API approach to find On Hand Quantity of an Item

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

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 := &item_id;
   v_org_id := &org_id;
   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;

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 := &item_id; v_org_id := &org_id; 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;