ALL INVENTORY ORGANIZATION REPORT

SELECT   "item code",
         item_desc,
         orgn,
         subinventory,
         transaction_uom,
         "PRIMARY_QUANTITY ",
         cmpnt_cost,
         "PRIMARY_QUANTITY " * cmpnt_cost AS "TOTAL COST"
  FROM   (  SELECT   DISTINCT
                     mtlb.inventory_item_id,
                     mtlb.segment1 "item code",
                     mtlb.description item_desc,
                     organization_name orgn,
                     MMT.subinventory_code subinventory,
                     mtlb.primary_uom_code transaction_uom,
                     (SELECT   on_hand_qty
                        FROM   (  SELECT   SUM (target_qty) on_hand_qty,
                                           inventory_item_id,
                                           subinventory_code
                                    FROM   (  SELECT   moqv.subinventory_code,
                                                       moqv.inventory_item_id,
                                                       SUM (transaction_quantity)
                                                          target_qty
                                                FROM   mtl_onhand_qty_cost_v moqv
                                               WHERE   moqv.organization_id IN
                                                             (SELECT   organization_id
                                                                FROM   org_organization_definitions
                                                               WHERE   organization_code =
                                                                          NVL (
                                                                             :p_orgn,
                                                                             organization_code
                                                                          ))
                                                       --:org_id
                                                       AND moqv.subinventory_code =
                                                             NVL (
                                                                :p_from_whse,
                                                                moqv.subinventory_code
                                                             )
                                            GROUP BY   moqv.subinventory_code,
                                                       moqv.inventory_item_id,
                                                       moqv.item_cost
                                            UNION
                                              SELECT   mt.subinventory_code subinv,
                                                       mt.inventory_item_id item_id,
                                                       -SUM (primary_quantity)
                                                          target_qty
                                                FROM   mtl_material_transactions mt,
                                                       mtl_txn_source_types mtst
                                               WHERE   mt.organization_id IN
                                                             (SELECT   organization_id
                                                                FROM   org_organization_definitions
                                                               WHERE   organization_code =
                                                                          NVL (
                                                                             :p_orgn,
                                                                             organization_code
                                                                          ))
                                                       AND transaction_date >=
                                                             NVL (
                                                                TO_DATE (
                                                                   :p_end_date
                                                                ),
                                                                TRUNC (SYSDATE)
                                                             )
                                                             + 1
                                                       AND mt.transaction_source_type_id =
                                                             mtst.transaction_source_type_id
                                                       AND mt.subinventory_code =
                                                             NVL (
                                                                :p_from_whse,
                                                                mt.subinventory_code
                                                             )
                                            GROUP BY   mt.subinventory_code,
                                                       mt.inventory_item_id) oq
                                GROUP BY   oq.inventory_item_id,
                                           subinventory_code) a
                       WHERE   a.inventory_item_id = mmt.inventory_item_id
                               AND a.subinventory_code = mmt.subinventory_code)
                        "PRIMARY_QUANTITY ",
                     ROUND (ccd.cmpnt_cost, 5) cmpnt_cost
              /*  ROUND (
                   ( (SELECT   SUM (primary_quantity)
                        FROM   mtl_material_transactions
                       WHERE   inventory_item_id = mmt.inventory_item_id
                               AND organization_id = mmt.organization_id
                               AND subinventory_code =
                                     mmt.subinventory_code
                               AND primary_quantity > 0
                               AND TRUNC (transaction_date) <=
                                     (:p_end_date))
                    * ccd.cmpnt_cost),
                   5
                )
                   "TOTAL COST"*/
              FROM   org_organization_definitions org,
                     mtl_material_transactions mmt,
                     mtl_system_items_b mtlb,
                     cm_cmpt_dtl ccd,
                     gmf_period_statuses gps,
                     --gmf_calendar_assignments gcass,
                     gmf_fiscal_policies gfp
             WHERE       org.organization_id = mmt.organization_id
                     AND mmt.organization_id = mtlb.organization_id
                     AND mtlb.inventory_item_id = mmt.inventory_item_id
                     AND ccd.inventory_item_id = mmt.inventory_item_id
                     AND TRUNC (transaction_date) <= (:p_end_date)
                     AND mmt.inventory_item_id IN
                              (SELECT   inventory_item_id
                                 FROM   mtl_system_items_b
                                WHERE   segment1 BETWEEN NVL (:p_from_item,
                                                              segment1)
                                                     AND  NVL (:p_to_item,
                                                               segment1))
                     AND org.organization_id IN
                              (SELECT   organization_id
                                 FROM   org_organization_definitions
                                WHERE   organization_code =
                                           NVL (:p_orgn, organization_code))
                     AND org.operating_unit IN
                              (SELECT   organization_id
                                 FROM   hr_operating_units
                                WHERE   organization_id =
                                           NVL (:p_co_code, organization_id))
                     /*  AND ccd.period_id IN
                                (SELECT   ju.period_id
                                   FROM   gmf_fiscal_policies a,
                                          hr_operating_units b,
                                          gmf_calendar_assignments h,
                                          gmf_period_statuses ju
                                  WHERE   a.legal_entity_id =
                                             b.default_legal_context_id
                                          AND a.legal_entity_id =
                                                h.legal_entity_id
                                          AND a.cost_type_id = h.cost_type_id
                                          AND ju.legal_entity_id =
                                                h.legal_entity_id
                                          AND ju.cost_type_id = h.cost_type_id
                                          AND b.organization_id =
                                                NVL (:p_co_code,
                                                     b.organization_id)
                                )
                       AND ccd.cost_type_id IN
                                (SELECT   h.cost_type_id
                                   FROM   gmf_fiscal_policies a,
                                          hr_operating_units b,
                                          gmf_calendar_assignments h,
                                          gmf_period_statuses ju
                                  WHERE   a.legal_entity_id =
                                             b.default_legal_context_id
                                          AND a.legal_entity_id =
                                                h.legal_entity_id
                                          AND a.cost_type_id = h.cost_type_id
                                          AND ju.legal_entity_id =
                                                h.legal_entity_id
                                          AND ju.cost_type_id = h.cost_type_id
                                          AND b.organization_id =
                                                NVL (:p_co_code,
                                                     b.organization_id)
                                )*/
                     AND mmt.subinventory_code =
                           NVL (:p_from_whse, mmt.subinventory_code)
                     --  AND gps.calendar_code = gcass.calendar_code
                     AND gps.legal_entity_id = org.legal_entity
                     --  AND gps.legal_entity_id = gcass.legal_entity_id
                     --AND gfp.legal_entity_id = gps.legal_entity_id
                     -- AND gcass.legal_entity_id = gfp.legal_entity_id
                     -- AND gps.cost_type_id = gfp.cost_type_id
                     AND:p_end_date BETWEEN gps.start_date AND gps.end_date
                     AND mtlb.process_execution_enabled_flag = 'Y'
                     AND mtlb.purchasing_enabled_flag = 'Y'
                     AND ccd.cost_type_id = gps.cost_type_id
                     AND mmt.TRANSACTION_QUANTITY NOT LIKE '%-%'
                     AND ccd.period_id = gps.period_id
                     AND gfp.legal_entity_id = gps.legal_entity_id
                     AND gfp.MTL_CMPNTCLS_ID = ccd.COST_CMPNTCLS_ID
          GROUP BY   organization_name,
                     mtlb.segment1,
                     mtlb.inventory_item_id,
                     subinventory_code,
                     mmt.transaction_uom,
                     ccd.cmpnt_cost,
                     mmt.inventory_item_id,
                     mmt.organization_id,
                     mtlb.description,
                     gps.cost_type_id,
                     mtlb.primary_uom_code,
                     ccd.PERIOD_ID
          ORDER BY   segment1)                            --subinventory_code)
 WHERE   cmpnt_cost > 0
-- &CP_1