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)
Interface tables: MTL_TRANSACTIONS_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
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
x_return_status VARCHAR2 (50);
x_msg_count VARCHAR2 (50);
x_msg_data VARCHAR2 (50);
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;
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);
DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);