ECC Project Manufacturing Genealogy & Trace, Project Contracts

select
x.*
from
(
SELECT * FROM (
          WITH oke_customers AS(
            SELECT dnz_chr_id,
                   LISTAGG(party_name, ',') WITHIN GROUP (ORDER BY party_name) AS name
            FROM (
              SELECT DISTINCT
                     okcpr.dnz_chr_id,
                     hzp.party_name
              FROM hz_cust_accounts hzca,
                   hz_parties hzp,
                   okc_k_party_roles_b okcpr
              WHERE hzca.party_id = hzp.party_id
                AND hzca.cust_account_id = okcpr.object1_id1
                AND okcpr.jtot_object1_code = 'OKE_CUSTACCT'
                AND okcpr.rle_code = 'K_CUSTOMER'
            )
            GROUP BY dnz_chr_id
          ),
          pjm_projects AS (
            SELECT DISTINCT project_id FROM pjm_project_parameters
          )
          SELECT 'PJM_GNT_CONTRACT' || '-' || pp_d.project_id
                                      || '-' || NVL(TO_CHAR(pt_d.task_id), 'NO_TASK')
                                      || '-' || okeh.k_header_id
                                      || '-' || oked.deliverable_id
                                      || '-' || NVL(TO_CHAR(mmt.transaction_id), 'NO_TXN')
                                      || '-' || NVL(TO_CHAR(msn.gen_object_id), 'NO_SERIAL') AS ecc_spec_id,
                 pp_d.org_id,
                 okeh.k_header_id             AS header_id,
                 okett.k_type_name            AS contract_type,
                 okch.contract_number,
                 okep.program_number          AS program,
                 flv_pl.meaning               AS product_line,
                 okcst_h.meaning              AS header_status,
                 pp_h.segment1                AS header_project_number,
                 okch.currency_code,
                 okch.estimated_amount        AS contract_value,
                 okch.start_date              AS header_start_date,
                 okch.end_date                AS header_end_date,
                 oke_customers.name           AS customer,
                 okcht.short_description,
                 okel.k_line_id               AS line_id,
                 okcl.line_number,
                 oked.deliverable_id,
                 oked.deliverable_num,
                 oked.item_id                 AS deliverable_item_id,
                 msi_d.concatenated_segments  AS deliverable_item_number,
                 msit_d.description           AS deliverable_item_desc,
                 oked.quantity                AS deliverable_quantity,
                 oked.uom_code                AS deliverable_uom_code,
                 oked.unit_number             AS deliverable_unit_number,
                 pp_d.project_id              AS deliverable_project_id,
                 pp_d.segment1                AS deliverable_project_number,
                 pp_d.name                    AS deliverable_project_name,
                 pt_d.task_id                 AS deliverable_task_id,
                 pt_d.task_number             AS deliverable_task_number,
                 pt_d.task_name               AS deliverable_task_name,
                 oked.delivery_date           AS delivery_date,
                 oked.expected_shipment_date  AS expected_shipment_date,
                 oked.promised_shipment_date  AS promised_shipment_date,
                 CASE
                   WHEN wnd.status_code IN ('CL', 'CO', 'IT') THEN
                     CASE
                       WHEN oked.shipping_request_id IS NOT NULL THEN
                         inv_convert.inv_um_convert(oked.item_id,
                                                    5,
                                                    NVL(wdd.shipped_quantity, 0),
                                                    wdd.requested_quantity_uom,
                                                    oked.uom_code,
                                                    NULL,
                                                    NULL)
                       ELSE
                         wdd.shipped_quantity
                     END
                   ELSE
                     NULL
                 END                          AS deliverable_shipped_quantity,
                 msn.serial_number,
                 mmt.locator_id               AS txn_locator_id,
                 okcht.language
          FROM pa_projects_all pp_h,
               pa_projects_all pp_d,
               pa_tasks pt_d,
               oke_k_headers okeh,
               oke_k_lines okel,
               oke_k_deliverables_b oked,
               oke_k_types_tl okett,
               oke_programs okep,
               okc_k_headers_all_b okch,
               okc_k_headers_tl okcht,
               okc_k_lines_b okcl,
               okc_statuses_tl okcst_h,
               mtl_system_items_kfv msi_d,
               mtl_system_items_tl msit_d,
               mtl_material_transactions mmt,
               mtl_unit_transactions mut,
               mtl_serial_numbers msn,
               wsh_delivery_details wdd,
               wsh_delivery_assignments wda,
               wsh_new_deliveries wnd,
               fnd_lookup_values flv_pl,
               oke_customers,
               pjm_projects
          WHERE pp_d.start_date >= NVL(TO_DATE('2021/12/12', 'YYYY/MM/DD'), pp_d.start_date)
            AND pp_d.project_type <> 'AWARD_PROJECT'
            AND pp_d.template_flag = 'N'
            AND pp_d.project_id = pjm_projects.project_id
            AND pp_d.project_id = oked.project_id
            AND pt_d.project_id (+) = oked.project_id
            AND pt_d.task_id (+) = oked.task_id
            AND oked.direction = 'OUT'
            AND okel.k_line_id = oked.k_line_id
            AND okcl.id = okel.k_line_id
            AND okeh.k_header_id = oked.k_header_id
            AND okch.id = okeh.k_header_id
            AND okch.buy_or_sell = 'S'
            AND okcht.id = okch.id
            AND okett.k_type_code = okeh.k_type_code
            AND okep.program_id (+) = okeh.program_id
            AND oke_customers.dnz_chr_id (+) = okeh.k_header_id
            AND okcst_h.code = okch.sts_code
            AND pp_h.project_id = okeh.project_id
            AND msi_d.inventory_item_id = oked.item_id
            AND msi_d.organization_id = oked.inventory_org_id
            AND msit_d.inventory_item_id = oked.item_id
            AND msit_d.organization_id = oked.inventory_org_id
            AND flv_pl.view_application_id (+) = 777
            AND flv_pl.lookup_type (+) = 'PRODUCT_LINE'
            AND flv_pl.lookup_code (+) = okeh.product_line_code
            AND wdd.source_code (+) = 'OKE'
            AND wdd.source_header_id (+) = oked.k_header_id
            AND wdd.source_line_id (+) = oked.deliverable_id
            AND wda.delivery_detail_id (+) = wdd.delivery_detail_id
            AND wnd.delivery_id (+) = wda.delivery_id
            AND 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 mut.transaction_id (+) = mmt.transaction_id
            AND msn.inventory_item_id (+) = mut.inventory_item_id
            AND msn.serial_number (+) = mut.serial_number
            AND okett.language = okcht.language
            AND okcst_h.language = okcht.language
            AND msit_d.language = okcht.language
            AND NVL(flv_pl.language, okcht.language) = okcht.language
            AND okcht.language IN ('US')) PIVOT (
          MAX(contract_type) AS contract_type,
          MAX(product_line) AS product_line,
          MAX(header_status) AS header_status,
          MAX(short_description) AS short_description,
          MAX(deliverable_item_desc) AS deliverable_item_desc FOR language IN ('US' "US"))
) x
where
2=2