Cumulative Inventory Stock Position

This query fetches cumulative inventory stock position for a particular period.

select :p_from_date fromdate,:p_to_date todate,
msi.inventory_item_id, msi.segment1,
msi.primary_uom_code uom,
      (select (sum(mmt.primary_quantity))
          from mtl_material_transactions mmt
            where inventory_item_id = msi.inventory_item_id
            and organization_id = msi.organization_id
            and trunc (mmt.transaction_date) <= trunc (to_date (:p_from_date)

      )  closing_qty,     
            (select (sum(mmt.primary_quantity))
                 * (cst_cost_api.get_item_cost (1,
                                              msi.inventory_item_id,
                                              msi.organization_id,
                                              NULL,
                                              NULL
                                             )
                 )
          from mtl_material_transactions mmt
            where inventory_item_id = msi.inventory_item_id
            and organization_id = msi.organization_id
            and trunc (mmt.transaction_date) <= trunc (to_date (:p_from_date))

      ) closing_val,
       (SELECT SUM (primary_quantity) int_rec_qty
          --,organization_id,inventory_item_id
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Intransit Receipt'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                  int_rec_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Intransit Receipt'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                  int_rec_val,
       (SELECT SUM (primary_quantity) int_ship_qty
          --,organization_id,inventory_item_id
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Intransit Shipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 int_ship_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Intransit Shipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 int_ship_val,
       (SELECT SUM (primary_quantity) int_ship_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Misc Receipt(Cash Pur. < 5000)'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 Misc_Rec_Pur5000_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Misc Receipt(Cash Pur. < 5000)'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                and :p_to_date)
                                                                 Misc_Rec_Pur5000_val,
       (SELECT SUM (primary_quantity) int_ship_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Miscellaneous receipt of FIM'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 Misc_Rec_FIM_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Miscellaneous receipt of FIM'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                and :p_to_date)
                                                                 Misc_Rec_FIM_val,       
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'PO Receipt'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                   po_rec_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'PO Receipt'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                   po_rec_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return to Vendor'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           and  mmt.transaction_date between :p_from_date
                                                AND :p_to_date)
                                                            return_vendor_qty,
       (SELECT  SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return to Vendor'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                            return_vendor_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'WIP Issue'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 wip_comp_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'WIP Issue'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 wip_comp_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                    'Move Order Issue - Contractor Chargeable'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                  mo_cont_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                    'Move Order Issue - Contractor Chargeable'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                  mo_cont_val,
       (SELECT SUM (primary_quantity)
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                          'Move Order Issue - Contractor Free'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_free_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                          'Move Order Issue - Contractor Free'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_free_val,
       (SELECT SUM (primary_quantity)
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Equipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_equi_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Equipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_equi_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Project'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_proj_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
          FROM mtl_material_transactions mmt, mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Project'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                             mo_cont_proj_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Scrap'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 mo_scrap_qty,
       (SELECT   SUM (primary_quantity*actual_cost)po_rec_val
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Move Order Issue - Scrap'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                                 mo_scrap_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                         'Return From Contractor - Chargeable'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                              return_cont_qty,
       (SELECT   SUM (primary_quantity*actual_cost)po_rec_val
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name =
                                         'Return From Contractor - Chargeable'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                              return_cont_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Contractor - Free'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                         return_cont_free_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Contractor - Free'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN  :p_from_date
                                                AND :p_to_date)
                                                         return_cont_free_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Equipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND  :p_to_date)
                                                              return_equi_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Equipment'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                              return_equi_val,
       (SELECT SUM (primary_quantity) po_rec_qty
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Project'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           AND mmt.transaction_date BETWEEN :p_from_date
                                                AND :p_to_date)
                                                              return_proj_qty,
       (SELECT   SUM (primary_quantity*actual_cost)
        FROM   mtl_material_transactions mmt,
               mtl_transaction_types mtt
         WHERE mmt.transaction_type_id = mtt.transaction_type_id
           AND mtt.transaction_type_name = 'Return From Project'
           AND inventory_item_id = msi.inventory_item_id
           AND organization_id = msi.organization_id
           and mmt.transaction_date between :p_from_date
                                                AND :p_to_date)
                                                              return_proj_val,
          (select (sum(mmt.primary_quantity))
          from mtl_material_transactions mmt
            where inventory_item_id = msi.inventory_item_id
            and organization_id = msi.organization_id
            and trunc (mmt.transaction_date) <= trunc (to_date (:p_to_date))

      ) closing_qty_as,
     
            (select (sum(mmt.primary_quantity))
                 * (cst_cost_api.get_item_cost (1,
                                              msi.inventory_item_id,
                                              msi.organization_id,
                                              NULL,
                                              NULL
                                             )
                 )
          from mtl_material_transactions mmt
            where inventory_item_id = msi.inventory_item_id
            and organization_id = msi.organization_id
            and trunc (mmt.transaction_date) <= trunc (to_date (:p_to_date))

      ) closing_val_as
                                                             
  FROM mtl_system_items_b msi
 where 1 = 1
   AND msi.organization_id = :P_ORG_ID
   AND msi.segment1 between NVL(:P_ITEM_FROM,msi.segment1) and NVL(:P_ITEM_TO,msi.segment1)
   Order by msi.segment1;