The total number of stock-keeping units (SKUs) that are physically located in the warehouse location at the current time. This includes items that are already allocated to fulfilling production needs or sales orders. So, this number may differ from the quantity available total.
On-hand quantity is what you actually have right now for transactions.
Available quantity is the total quantity.
i.e. Availability = On-hand qty - Reservations - Pending transactions (Intransit)
On-hand quantity
Interface tables: MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.
Base tables: MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS MTL_SERIAL_NUMBERS
Concurrent program:
Validations: validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.
Below is the PL/SQL, which gives you the On-hand Quantity details as per oracle form.
Oracle form show details like On-Hand Quantity, Available to reserve, Quantity Reserved,Quantity Suggested, Available to Transact and Available to Reserve.
All These details can be fetched using API => inv_quantity_tree_pub.query_quantities
DECLARE
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
v_item_id NUMBER;
v_organization_id NUMBER;
v_qoh NUMBER;
v_rqoh NUMBER;
v_atr NUMBER;
v_att NUMBER;
v_qr NUMBER;
v_qs NUMBER;
v_lot_control_code BOOLEAN;
v_serial_control_code BOOLEAN;
BEGIN
SELECT inventory_item_id, mp.organization_id
INTO v_item_id, v_organization_id
FROM mtl_system_items_b msib, mtl_parameters mp
WHERE segment1 = :item_number
AND msib.organization_id = mp.organization_id
AND mp.organization_code = :organization_code;
v_qoh := NULL;
v_rqoh := NULL;
v_atr := NULL;
v_lot_control_code := FALSE;
v_serial_control_code := FALSE;
fnd_client_info.set_org_context (1);
inv_quantity_tree_pub.query_quantities (
p_api_version_number => 1.0,
p_init_msg_lst => 'F',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_organization_id => v_organization_id,
p_inventory_item_id => v_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_is_revision_control => FALSE,
p_is_lot_control => v_lot_control_code,
p_is_serial_control => v_serial_control_code,
p_revision => NULL, -- p_revision,
p_lot_number => NULL, -- p_lot_number,
p_lot_expiration_date => SYSDATE,
p_subinventory_code => NULL, -- p_subinventory_code,
p_locator_id => NULL, -- p_locator_id,
p_onhand_source => 3,
x_qoh => v_qoh, -- Quantity on-hand
x_rqoh => v_rqoh, --reservable quantity on-hand
x_qr => v_qr,
x_qs => v_qs,
x_att => v_att, -- available to transact
x_atr => v_atr -- available to reserve
);
DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);
DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);
DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);
DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);
DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);
DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
END;
