Tuesday, June 14, 2011

HOW TO FIND THE ON HAND QUANTITY FOR A SPECIFIC DATE

select  ITEM_ID,
   sum(TARGET_QTY) quantity
   from (
     SELECT 
              moqd.inventory_item_id item_id,            
              SUM (primary_transaction_quantity) target_qty
       FROM   mtl_onhand_quantities_detail moqd
      WHERE   moqd.organization_id = organizations
              AND moqd.owning_tp_type = DECODE (2, 2, 2, moqd.owning_tp_type)
   GROUP BY  moqd.inventory_item_id
   UNION
     SELECT
              mmt.inventory_item_id item_id,
              -SUM (primary_quantity) target_qty
       FROM   mtl_material_transactions mmt,
              mtl_txn_source_types mtst,
              mtl_parameters mp
      WHERE   mmt.organization_id = organizations AND mp.organization_id = organizations
                                                                    AND transaction_date >= (start_date + 1)
              AND NVL (mmt.owning_tp_type, 2) =
                    DECODE (2, 2, 2, NVL (mmt.owning_tp_type, 2))
              AND mmt.transaction_source_type_id =
                    mtst.transaction_source_type_id
              AND NVL (mmt.logical_transaction, 2) <> 1
   GROUP BY  
              mmt.inventory_item_id
              )
              group by  ITEM_ID

No comments:

Post a Comment