Total Pageviews

August 28, 2015

8/28/2015 05:54:00 AM


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;