add

About Me

My photo
Oracle Apps - Techno Functional consultant

Wednesday, July 23

Get Onhand Quantity from table and API

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
Also from GET ON-HAND QUANTITIES API

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

SELECT * FROM MTL_ONHAND_QUANTITIES;

No comments: