ECC Project Manufacturing Genealogy & Trace, Purchase Orders

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.organization_id,
                   mmt.inventory_item_id,
                   mmt.locator_id,
                   pd.po_distribution_id
            FROM mtl_material_transactions mmt,
                 rcv_transactions rt,
                 po_distributions_all pd
            WHERE mmt.rcv_transaction_id = rt.transaction_id
              AND mmt.organization_id = rt.organization_id
              AND mmt.transaction_source_type_id = 1
              AND mmt.transaction_action_id = 27
              AND pd.po_distribution_id = rt.po_distribution_id
              AND pd.destination_organization_id = rt.organization_id
              AND pd.project_id IN (SELECT DISTINCT project_id FROM pjm_project_parameters)
          )
          SELECT /*+ merge(pjm_projects) cardinality(pjm_projects 1)
                         leading(pjm_projects pp pd pll pl ph h0u1 h0u2)
                         use_nl(pjm_projects pp pd pll pl ph h0u1 h0u2) */
            'PJM_GNT_PO' || '-' || pp.project_id
                           || '-' || NVL(TO_CHAR(pt.task_id), 'NO_TASK')
                           || '-' || ph.po_header_id
                           || '-' || pd.po_distribution_id
                           || '-' || NVL(TO_CHAR(inv_txn.transaction_id), 'NO_TXN')
                           || '-' || NVL(TO_CHAR(msn.gen_object_id), 'NO_SERIAL')     AS ecc_spec_id,
            pp.org_id,
            pp.project_id,
            pp.segment1                                                                   AS project_number,
            pp.name                                                                       AS project_name,
            pt.task_id,
            pt.task_number,
            pt.task_name,
            ph.po_header_id,
            ph.segment1                                                                   AS po_number,
            ph.revision_num,
            ph.creation_date,
            hou1.name                                                                     AS operating_unit,
            pdt.type_name                                                                 AS document_type,
            aps.vendor_id                                                                 AS supplier_id,
            aps.vendor_name                                                               AS supplier_name,
            apss.vendor_site_id                                                           AS supplier_site_id,
            apss.vendor_site_code                                                         AS supplier_site_name,
            CASE
              WHEN ph.cancel_flag = 'Y'
                OR ph.frozen_flag = 'Y'
                OR ph.user_hold_flag = 'Y'
                OR NVL(ph.closed_code, 'OPEN') <> 'OPEN' THEN
                flv1.meaning || ', ' || flv2.meaning
              ELSE
                flv1.meaning
            END                                                                           AS status,
            pap.full_name                                                                 AS agent_name,
            ph.currency_code                                                              AS currency,
            pl.po_line_id,
            pl.line_num                                                                   AS po_line_number,
            msi.inventory_item_id                                                         AS item_id,
            msi.concatenated_segments                                                     AS item_number,
            NVL(msit.description, pl.item_description)                                    AS item_description,
            msi.primary_uom_code,
            msi.serial_number_control_code,
            pll.line_location_id                                                          AS po_shipment_id,
            pll.shipment_num                                                              AS po_shipment_number,
            pll.promised_date,
            pll.need_by_date,
            pd.po_distribution_id,
            pd.distribution_num                                                           AS po_distribution_number,
            pd.destination_organization_id                                                AS dist_destination_org_id,
            hou2.name                                                                     AS dist_destination_org,
            pd.quantity_ordered                                                           AS dist_quantity_ordered,
            pd.quantity_delivered                                                         AS dist_quantity_delivered,
            CASE pl.matching_basis
              WHEN 'QUANTITY' THEN
                (pd.quantity_ordered - pd.quantity_cancelled) * pll.price_override
              WHEN 'AMOUNT' THEN
                pd.amount_ordered - pd.amount_cancelled
            END                                                                           AS dist_amount,
            pd.end_item_unit_number,
            msn.serial_number,
            inv_txn.locator_id                                                            AS txn_locator_id,
            DECODE(ph.user_hold_flag, 'Y', 'FALSE', 'TRUE')                         AS hold_flag,
            CASE
              WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%'
               AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED')
               AND TRUNC(pll.promised_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, pll.promised_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                           AS schedule_exception_flag_1,
            CASE
              WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%'
               AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED')
               AND TRUNC(pll.promised_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, pll.promised_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                           AS schedule_exception_flag_2,
            CASE
              WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%'
               AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED')
               AND TRUNC(pll.need_by_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, pll.need_by_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                           AS schedule_exception_flag_3,
            CASE
              WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%'
               AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED')
               AND TRUNC(pll.need_by_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, pll.need_by_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                           AS schedule_exception_flag_4,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(pll.promised_date)           AS exception_days_1,
            TRUNC(pll.promised_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_2,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(pll.need_by_date)            AS exception_days_3,
            TRUNC(pll.need_by_date) - TRUNC(NVL(pt.completion_date, pp.completion_date))  AS exception_days_4,
            CASE
              WHEN NVL(ph.closed_code, 'OPEN') NOT LIKE '%CLOSED%'
               AND NVL(ph.authorization_status, 'N') NOT IN ('CANCELLED', 'REJECTED')
               AND NVL(pd.quantity_delivered, 0) + NVL(pd.quantity_cancelled, 0) < NVL(pd.quantity_ordered, 0)
               AND SYSDATE > NVL(pt.completion_date, pp.completion_date) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                           AS overdue_flag,
            hou1.language
          FROM pa_projects_all pp,
               pa_tasks pt,
               po_headers_all ph,
               po_lines_all pl,
               po_line_locations_all pll,
               po_distributions_all pd,
               po_document_types_all_tl pdt,
               ap_suppliers aps,
               ap_supplier_sites_all apss,
               per_all_people_f pap,
               mtl_system_items_kfv msi,
               mtl_system_items_tl msit,
               mtl_unit_transactions mut,
               mtl_serial_numbers msn,
               hr_all_organization_units_tl hou1,
               hr_all_organization_units_tl hou2,
               fnd_lookup_values flv1,
               fnd_lookup_values flv2,
               inv_txn,
               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 = pd.project_id
            AND pt.project_id (+) = pd.project_id
            AND pt.task_id (+) = pd.task_id
            AND pll.line_location_id = pd.line_location_id
            AND pl.po_line_id = pd.po_line_id
            AND pl.po_line_id = pll.po_line_id
            AND ph.po_header_id = pl.po_header_id
            AND ph.type_lookup_code = 'STANDARD'
            AND aps.vendor_id = ph.vendor_id
            AND apss.vendor_id = ph.vendor_id
            AND apss.vendor_site_id = ph.vendor_site_id
            AND pdt.document_type_code IN ('PO', 'PA')
            AND pdt.document_subtype = 'STANDARD'
            AND pdt.org_id = ph.org_id
            AND pap.person_id = ph.agent_id
            AND (TRUNC(SYSDATE) BETWEEN pap.effective_start_date AND pap.effective_end_date)
            AND NVL(msi.organization_id, pd.destination_organization_id) = pd.destination_organization_id
            AND msi.inventory_item_id (+) = pl.item_id
            AND msit.organization_id (+) = msi.organization_id
            AND msit.inventory_item_id (+) = msi.inventory_item_id
            AND inv_txn.organization_id (+) = pd.destination_organization_id
            AND inv_txn.po_distribution_id (+) = pd.po_distribution_id
            AND mut.transaction_id (+) = inv_txn.transaction_id
            AND msn.inventory_item_id (+) = mut.inventory_item_id
            AND msn.serial_number (+) = mut.serial_number
            AND hou1.organization_id = ph.org_id
            AND hou2.organization_id = pd.destination_organization_id
            AND flv1.view_application_id = 201
            AND flv1.lookup_code = DECODE(ph.approved_flag, 'R', ph.approved_flag, NVL(ph.authorization_status, 'INCOMPLETE'))
            AND flv1.lookup_type IN ('PO APPROVAL', 'DOCUMENT STATE')
            AND flv2.view_application_id = 201
            AND flv2.lookup_type = 'DOCUMENT STATE'
            AND flv2.lookup_code = CASE
                                     WHEN ph.cancel_flag = 'Y' THEN
                                       'CANCELLED'
                                     WHEN ph.frozen_flag = 'Y' THEN
                                       'FROZEN'
                                     WHEN ph.user_hold_flag = 'Y' THEN
                                       'ON HOLD'
                                     ELSE
                                       NVL(ph.closed_code, 'OPEN')
                                   END
            AND pdt.language = hou1.language
            AND NVL(msit.language, hou1.language) = hou1.language
            AND hou2.language = hou1.language
            AND flv1.language = hou1.language
            AND flv2.language = hou1.language
            AND hou1.language IN ('US')) PIVOT (
          MAX(operating_unit) AS operating_unit,
          MAX(document_type) AS document_type,
          MAX(status) AS status,
          MAX(item_description) AS item_description,
          MAX(dist_destination_org) AS dist_destination_org FOR language IN ('US' "US"))
) x
where
2=2