ONHAND Quantity, RESERVE qty , INTRANSIT qty

 

==========

Indexes

==========

SELECT b.uniqueness, a.index_name, a.table_name, a.column_name ,b.tablespace_name,b.status

  FROM all_ind_columns a, all_indexes b

 WHERE a.index_name=b.index_name 

   AND a.table_name = upper('XXCUST_LABEL_ATTR_RULES_DTL')

 ORDER BY a.table_name, a.index_name, a.column_position;

============

Inventory

============

--onhand

(SELECT NVL

        (SUM (moq.transaction_quantity),

         0

         ) on_hand_qty,

  moq.inventory_item_id

  FROM mtl_onhand_quantities_detail moq

 WHERE moq.organization_id = p_inv_org_id

 GROUP BY moq.inventory_item_id) onhand

--reserve qty

(SELECT organization_id, inventory_item_id,

        NVL (SUM (transaction_quantity),0) res_qty

   FROM mtl_onhand_quantities

  WHERE organization_id = p_inv_org_id

    AND subinventory_code IN (SELECT secondary_inventory_name

                                FROM mtl_secondary_inventories

                               WHERE organization_id = p_inv_org_id

                                 AND reservable_type = '2')

                               GROUP BY organization_id, inventory_item_id) reserve

--intransit_qty

(select  sum(quantity_shipped-quantity_received) qty, l.item_id

   from rcv_shipment_headers h,

rcv_shipment_lines   l

  where l.shipment_header_id = h.shipment_header_id

    and h.receipt_source_code = 'INTERNAL ORDER'

-- and item_id = 1292747

    and to_organization_id = p_inv_org_id

    and quantity_received != quantity_shipped

  group by to_organization_id, to_subinventory, item_id) intransit_qty