ECC Project Manufacturing Genealogy & Trace, Project Locators

select
x.*
from
(
SELECT * FROM (
          WITH pjm_projects AS (
            SELECT DISTINCT project_id FROM pjm_project_parameters
          ),
          inv_txn AS (
            SELECT mmt.transaction_id,
                   mmt.transaction_date,
                   mmt.source_project_id AS project_id,
                   mmt.source_task_id    AS task_id,
                   mmt.inventory_item_id,
                   mmt.organization_id,
                   mmt.subinventory_code,
                   mmt.locator_id,
                   NULL AS to_locator_id
            FROM mtl_material_transactions mmt,
                 pjm_projects
            WHERE mmt.transaction_source_type_id = 5
              AND mmt.transaction_action_id IN (1, 31)
              AND mmt.source_project_id = pjm_projects.project_id
            UNION ALL
            SELECT mmt.transaction_id,
                  mmt.transaction_date,
                  mmt.project_id,
                  mmt.task_id,
                  mmt.inventory_item_id,
                  mmt.organization_id,
                  mmt.subinventory_code,
                  mmt.locator_id,
                  NULL
            FROM mtl_material_transactions mmt,
                 pjm_projects
            WHERE mmt.transaction_source_type_id = 1
              AND mmt.transaction_action_id = 27
              AND mmt.project_id = pjm_projects.project_id
            UNION ALL
            SELECT mmt.transaction_id,
                   mmt.transaction_date,
                   ool.project_id,
                   ool.task_id,
                   mmt.inventory_item_id,
                   mmt.organization_id,
                   mmt.subinventory_code,
                   mmt.locator_id,
                   NULL
            FROM mtl_material_transactions mmt,
                 wsh_delivery_details wdd,
                 oe_order_lines_all ool,
                 pjm_projects
            WHERE mmt.transaction_id = wdd.transaction_id
              AND mmt.transaction_source_type_id IN (2, 8)
              AND mmt.transaction_action_id = 28
              AND wdd.source_code = 'OE'
              AND wdd.source_line_id = ool.line_id
              AND ool.project_id = pjm_projects.project_id
            UNION ALL
            SELECT mmt.transaction_id,
                   mmt.transaction_date,
                   oked.project_id,
                   oked.task_id,
                   mmt.inventory_item_id,
                   mmt.organization_id,
                   mmt.subinventory_code,
                   mmt.locator_id,
                   NULL
            FROM mtl_material_transactions mmt,
                 wsh_delivery_details wdd,
                 oke_k_deliverables_b oked,
                 pjm_projects
            WHERE mmt.transaction_source_id = wdd.source_header_id
              AND mmt.trx_source_line_id = wdd.source_line_id
              AND mmt.picking_line_id = wdd.delivery_detail_id
              AND mmt.transaction_source_type_id = 16
              AND mmt.transaction_action_id = 1
              AND wdd.source_code = 'OKE'
              AND wdd.source_header_id = oked.k_header_id
              AND wdd.source_line_id = oked.deliverable_id
              AND oked.project_id = pjm_projects.project_id
            UNION ALL
            SELECT mmt.transaction_id,
                   mmt.transaction_date,
                   mmt.project_id,
                   mmt.task_id,
                   mmt.inventory_item_id,
                   mmt.organization_id,
                   mmt.subinventory_code,
                   mmt.locator_id,
                   DECODE(SIGN(mmt.transaction_quantity), -1, mmt.transfer_locator_id)
            FROM mtl_material_transactions mmt,
                 pjm_projects
            WHERE mmt.transaction_source_type_id = 13
              AND mmt.transaction_action_id IN (2, 27)
              AND mmt.project_id = pjm_projects.project_id
          ),
          inv_onhand_quantity AS (
            SELECT organization_id,
                   subinventory_code,
                   locator_id,
                   inventory_item_id,
                   SUM(transaction_quantity) AS quantity
            FROM mtl_onhand_quantities_detail
            WHERE locator_id IS NOT NULL
            GROUP BY organization_id,
                     subinventory_code,
                     locator_id,
                     inventory_item_id
          )
          SELECT
            'PJM_GNT_LOCATOR' || '-' || inv_txn.locator_id
                                || '-' || inv_txn.transaction_id         AS ecc_spec_id,
            pp.org_id,
            pp.project_id                                                  AS txn_project_id,
            pp_loc.project_id,
            pp_loc.segment1                                                AS project_number,
            pp_loc.name                                                    AS project_name,
            pt_loc.task_id,
            pt_loc.task_number,
            pt_loc.task_name,
            inv_txn.transaction_id,
            inv_txn.transaction_date,
            inv_txn.organization_id                                        AS inv_org_id,
            mp.organization_code                                           AS inv_org_code,
            hou.name                                                       AS inv_org,
            inv_txn.subinventory_code                                      AS subinventory,
            inv_txn.locator_id,
            DECODE(mp.project_reference_enabled, 1,
                   mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' || pp_loc.segment1 || '.' || pt_loc.task_number,
                   mil.concatenated_segments)                              AS locator,
            flv.meaning                                                    AS type,
            mms.status_code                                                AS status,
            inv_txn.inventory_item_id                                      AS item_id,
            msi.concatenated_segments                                      AS item_number,
            msit.description                                               AS item_description,
            NVL(inv_onhand_quantity.quantity, 0)                           AS onhand_quantity,
            msi.primary_uom_code,
            inv_txn.to_locator_id,
            hou.language
          FROM pa_projects_all pp,
               pa_projects_all pp_loc,
               pa_tasks pt_loc,
               mtl_item_locations_kfv mil,
               mtl_system_items_kfv msi,
               mtl_system_items_tl msit,
               mtl_material_statuses_tl mms,
               mtl_parameters mp,
               hr_all_organization_units_tl hou,
               fnd_lookup_values flv,
               inv_txn,
               inv_onhand_quantity,
               pjm_projects
          WHERE pp.start_date >= NVL(TO_DATE('2021/12/12', 'YYYY/MM/DD'), pp.start_date)
            AND pp.project_type <> 'AWARD_PROJECT'
            AND pp.template_flag = 'N'
            AND pp.project_id = pjm_projects.project_id
            AND pp.project_id = inv_txn.project_id
            AND mil.organization_id = inv_txn.organization_id
            AND mil.inventory_location_id = inv_txn.locator_id
            AND pp_loc.project_id (+) = mil.project_id
            AND pt_loc.project_id (+) = mil.project_id
            AND pt_loc.task_id (+) = mil.task_id
            AND msi.organization_id = inv_txn.organization_id
            AND msi.inventory_item_id = inv_txn.inventory_item_id
            AND msit.organization_id = inv_txn.organization_id
            AND msit.inventory_item_id = inv_txn.inventory_item_id
            AND inv_onhand_quantity.organization_id (+) = inv_txn.organization_id
            AND inv_onhand_quantity.inventory_item_id (+) = inv_txn.inventory_item_id
            AND inv_onhand_quantity.subinventory_code (+) = inv_txn.subinventory_code
            AND inv_onhand_quantity.locator_id (+) = inv_txn.locator_id
            AND mp.organization_id = inv_txn.organization_id
            AND hou.organization_id = inv_txn.organization_id
            AND mms.status_id (+) = mil.status_id
            AND flv.view_application_id (+) = 700
            AND flv.lookup_type (+) = 'MTL_LOCATOR_TYPES'
            AND flv.lookup_code (+) = mil.inventory_location_type
            AND msit.language = hou.language
            AND NVL(mms.language, hou.language) = hou.language
            AND NVL(flv.language, hou.language) = hou.language
            AND hou.language IN ('US')) PIVOT (
          MAX(inv_org) AS inv_org,
          MAX(type) AS type,
          MAX(status) AS status,
          MAX(item_description) AS item_description FOR language IN ('US' "US"))
) x
where
2=2