This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve
You can also get the On-hand quantities from the table mtl_onhand_quantities
GET ON-HAND QUANTITIES API
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
DECLARE x_return_status VARCHAR2 (50); x_msg_count VARCHAR2 (50); x_msg_data VARCHAR2 (50); v_item_id NUMBER; v_org_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 — Set the variable values v_item_id := ‘6566’; v_org_id := 61; v_qoh := NULL; v_rqoh := NULL; v_atr := NULL; v_lot_control_code := FALSE; v_serial_control_code := FALSE; — Set the org context fnd_client_info.set_org_context (1); — Call API 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_org_id, p_inventory_item_id => v_item_id, p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode, — or 3 p_is_revision_control => FALSE, p_is_lot_control => v_lot_control_code, — is_lot_control, 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_cost_group_id => NULL, — cg_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; |
GET ON-HAND QUANTITIES FROM TABLE
|
1 |
SELECT * FROM MTL_ONHAND_QUANTITIES; |