ECC Project Manufacturing Genealogy & Trace, Work Order Components

select
x.*
from
(
SELECT * FROM (
          WITH pjm_projects AS (
            SELECT DISTINCT project_id FROM pjm_project_parameters
          )
          SELECT /*+ merge(pjm_projects) cardinality(pjm_projects 1) index(mmt MTL_MATERIAL_TRANSACTIONS_N2)
                       leading(pjm_projects pp wdj we hou mp pt wo bso wro bd mmt mut msn)
                       use_nl(pjm_projects pp wdj we hou mp pt wo bso wro bd mmt mut msn) */
            'PJM_GNT_WOCOMP' || '-' || pp.project_id
                               || '-' || NVL(TO_CHAR(pt.task_id), 'NO_TASK')
                               || '-' || we.wip_entity_id
                               || '-' || wdj.organization_id
                               || '-' || wo.operation_seq_num
                               || '-' || wro.inventory_item_id
                               || '-' || NVL(TO_CHAR(mmt.transaction_id), 'NO_TXN')
                               || '-' || NVL(TO_CHAR(msn.gen_object_id), 'NO_SERIAL')     AS ecc_spec_id,
            pp.org_id,
            pp.project_id,
            pt.task_id,
            wdj.wip_entity_id                                                                 AS job_id,
            we.wip_entity_name                                                                AS job_name,
            wdj.organization_id                                                               AS inv_org_id,
            mp.organization_code                                                              AS inv_org_code,
            hou.name                                                                          AS inv_org,
            wo.operation_seq_num,
            bso.operation_code,
            NVL2(bso.operation_code,
                 wo.operation_seq_num || ' - ' || bso.operation_code, wo.operation_seq_num) AS operation,
            bd.department_code,
            wro.inventory_item_id                                                             AS item_id,
            msi.concatenated_segments                                                         AS item_number,
            msit.description                                                                  AS item_description,
            msi.primary_uom_code,
            msi.serial_number_control_code,
            flv.meaning                                                                       AS supply_type,
            wro.supply_subinventory                                                           AS supply_subinventory,
            DECODE(mp.project_reference_enabled, 1,
                   NVL2(mil.inventory_location_id,
                        mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' || pp_loc.segment1 || '.' || pt_loc.task_number,
                        NULL),
                   mil.concatenated_segments)                                                 AS supply_locator,
            wro.quantity_per_assembly,
            wro.required_quantity                                                             AS quantity_required,
            wro.quantity_issued                                                               AS quantity_issued,
            wro.required_quantity - wro.quantity_issued                                       AS quantity_open,
            wro.comments,
            msn.serial_number,
            mmt.locator_id                                                                    AS txn_locator_id,
            CASE
              WHEN wro.quantity_issued < wro.required_quantity THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                               AS quantity_flag,
            hou.language
          FROM pa_projects_all pp,
               pa_tasks pt,
               pa_projects_all pp_loc,
               pa_tasks pt_loc,
               wip_entities we,
               wip_discrete_jobs wdj,
               wip_operations wo,
               wip_requirement_operations wro,
               bom_standard_operations bso,
               bom_departments bd,
               mtl_system_items_kfv msi,
               mtl_system_items_tl msit,
               mtl_item_locations_kfv mil,
               mtl_material_transactions mmt,
               mtl_unit_transactions mut,
               mtl_serial_numbers msn,
               mtl_parameters mp,
               hr_all_organization_units_tl hou,
               fnd_lookup_values flv,
               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 = wdj.project_id
            AND pt.project_id (+) = wdj.project_id
            AND pt.task_id (+) = wdj.task_id
            AND we.organization_id = wdj.organization_id
            AND we.wip_entity_id = wdj.wip_entity_id
            AND wo.organization_id = wdj.organization_id
            AND wo.wip_entity_id = wdj.wip_entity_id
            AND wro.organization_id = wo.organization_id
            AND wro.wip_entity_id = wo.wip_entity_id
            AND wro.operation_seq_num = wo.operation_seq_num
            AND bso.organization_id (+) = wo.organization_id
            AND bso.standard_operation_id (+) = wo.standard_operation_id
            AND bd.department_id (+) = wro.department_id
            AND msi.organization_id = wro.organization_id
            AND msi.inventory_item_id = wro.inventory_item_id
            AND msit.organization_id = msi.organization_id
            AND msit.inventory_item_id = msi.inventory_item_id
            AND mil.organization_id (+) = wro.organization_id
            AND mil.inventory_location_id (+) = wro.supply_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 mmt.organization_id (+) = wro.organization_id
            AND mmt.inventory_item_id (+) = wro.inventory_item_id
            AND mmt.transaction_source_id (+) = wro.wip_entity_id
            AND mmt.operation_seq_num (+) = wro.operation_seq_num
            AND mmt.transaction_source_type_id (+) = 5
            AND mmt.transaction_action_id (+) = 1
            AND mut.transaction_id (+) = mmt.transaction_id
            AND msn.inventory_item_id (+) = mut.inventory_item_id
            AND msn.serial_number (+) = mut.serial_number
            AND mp.organization_id = wdj.organization_id
            AND hou.organization_id = wdj.organization_id
            AND flv.view_application_id = 700
            AND flv.lookup_type = 'WIP_SUPPLY'
            AND flv.lookup_code = wro.wip_supply_type
            AND msit.language = hou.language
            AND flv.language = hou.language
            AND hou.language IN ('US')) PIVOT (
          MAX(inv_org) AS inv_org,
          MAX(item_description) AS item_description,
          MAX(supply_type) AS supply_type FOR language IN ('US' "US"))
) x
where
2=2