ECC Project Manufacturing Genealogy & Trace, Sales Orders

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) */
            'PJM_GNT_SO' || '-' || pp.project_id
                           || '-' || NVL(TO_CHAR(pt.task_id), 'NO_TASK')
                           || '-' || ooh.header_id
                           || '-' || ool.line_id
                           || '-' || NVL(TO_CHAR(mr.reservation_id), 'NO_RESERVATION')
                           || '-' || 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,
            pp.segment1                                                                  AS project_number,
            pp.name                                                                      AS project_name,
            pt.task_id,
            pt.task_number,
            pt.task_name,
            ooh.header_id,
            ooh.order_number,
            ooh.ordered_date,
            ott.name                                                                     AS order_type,
            hca.cust_account_id                                                          AS customer_id,
            hca.account_number                                                           AS customer_number,
            hp.party_name                                                                AS customer_name,
            hcsu_ship.location                                                           AS ship_to_location,
            ooh.transactional_curr_code                                                  AS currency_code,
            hou1.name                                                                    AS operating_unit,
            ool.line_id,
            RTRIM(ool.line_number || '.' ||
                  ool.shipment_number || '.' ||
                  ool.option_number || '.' ||
                  ool.component_number || '.' ||
                  ool.service_number, '.')                                             AS line_number,
            msi.inventory_item_id                                                        AS item_id,
            msi.concatenated_segments                                                    AS item_number,
            msit.description                                                             AS item_description,
            ool.ordered_quantity,
            msi.primary_uom_code,
            msi.serial_number_control_code,
            DECODE(ool.line_category_code, 'RETURN', -1, 1) *
              (NVL(ool.ordered_quantity, 0) - NVL(ool.cancelled_quantity, 0)) *
              NVL(ool.unit_selling_price, 0)                                             AS line_amount,
            ool.ship_from_org_id,
            hou2.name                                                                    AS ship_from_organization,
            ool.schedule_ship_date,
            ool.request_date,
            ool.promise_date,
            ool.end_item_unit_number                                                     AS end_item_unit_number,
            msn.serial_number,
            mr.reservation_id,
            CASE
              WHEN mr.supply_source_type_id = 5 THEN
                mr.supply_source_header_id
              WHEN mr.orig_supply_source_type_id = 5 THEN
                mr.orig_supply_source_header_id
            END                                                                          AS job_id,
            mmt.locator_id                                                               AS txn_locator_id,
            CASE
              (
                SELECT COUNT(1)
                FROM oe_order_holds_all
                WHERE header_id = ooh.header_id
                  AND released_flag = 'N'
                  AND line_id IS NULL
              )
              WHEN 0 THEN
                'TRUE'
              ELSE
                'FALSE'
            END                                                                          AS hold_flag,
            CASE
              WHEN ool.cancelled_flag <> 'Y'
               AND ool.open_flag <> 'N'
               AND TRUNC(ool.request_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, ool.request_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                          AS schedule_exception_flag_1,
            CASE
              WHEN ool.cancelled_flag <> 'Y'
               AND ool.open_flag <> 'N'
               AND TRUNC(ool.request_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, ool.request_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                          AS schedule_exception_flag_2,
            CASE
              WHEN ool.cancelled_flag <> 'Y'
               AND ool.open_flag <> 'N'
               AND TRUNC(ool.promise_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, ool.promise_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                          AS schedule_exception_flag_3,
            CASE
              WHEN ool.cancelled_flag <> 'Y'
               AND ool.open_flag <> 'N'
               AND TRUNC(ool.promise_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, ool.promise_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                          AS schedule_exception_flag_4,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(ool.request_date)           AS exception_days_1,
            TRUNC(ool.request_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_2,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(ool.promise_date)           AS exception_days_3,
            TRUNC(ool.promise_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_4,
            CASE
              WHEN NVL(ool.visible_demand_flag, 'N') = 'N'
               AND ool.cancelled_flag <> 'Y'
               AND ool.open_flag <> 'N'
               AND ool.source_type_code = 'INTERNAL'
               AND SYSDATE > NVL(pt.start_date, pp.start_date) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                          AS overdue_flag,
            hou1.language
          FROM pa_projects_all pp,
               pa_tasks pt,
               oe_order_headers_all ooh,
               oe_order_lines_all ool,
               oe_transaction_types_tl ott,
               hz_cust_accounts hca,
               hz_parties hp,
               hz_cust_site_uses_all hcsu_ship,
               hr_all_organization_units_tl hou1,
               hr_all_organization_units_tl hou2,
               mtl_system_items_kfv msi,
               mtl_system_items_tl msit,
               mtl_reservations mr,
               mtl_material_transactions mmt,
               mtl_unit_transactions mut,
               mtl_serial_numbers msn,
               wsh_delivery_details wdd,
               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 = ool.project_id
            AND pt.project_id (+) = ool.project_id
            AND pt.task_id (+) = ool.task_id
            AND ooh.header_id = ool.header_id
            AND ott.transaction_type_id = ooh.order_type_id
            AND hca.cust_account_id (+) = ooh.sold_to_org_id
            AND hp.party_id (+) = hca.party_id
            AND hcsu_ship.site_use_id (+) = ooh.ship_to_org_id
            AND msi.organization_id = ool.ship_from_org_id
            AND msi.inventory_item_id = ool.inventory_item_id
            AND msit.organization_id = msi.organization_id
            AND msit.inventory_item_id = msi.inventory_item_id
            AND mr.organization_id (+) = ool.ship_from_org_id
            AND mr.inventory_item_id (+) = ool.inventory_item_id
            AND mr.demand_source_type_id (+) = 2
            AND mr.demand_source_line_id (+) = ool.line_id
            AND (mr.supply_source_type_id (+) = 5 OR mr.orig_supply_source_type_id (+) = 5)
            AND wdd.source_code (+) = 'OE'
            AND wdd.source_line_id (+) = ool.line_id
            AND mmt.transaction_id (+) = wdd.transaction_id
            AND mmt.trx_source_line_id (+) = wdd.source_line_id
            AND mmt.transaction_source_type_id (+) IN (2, 8)
            AND mmt.transaction_action_id (+) = 28
            AND mut.transaction_id (+) = mmt.transaction_id
            AND msn.inventory_item_id (+) = mut.inventory_item_id
            AND msn.serial_number (+) = mut.serial_number
            AND hou1.organization_id = ool.org_id
            AND hou2.organization_id = ool.ship_from_org_id
            AND ott.language = hou1.language
            AND msit.language = hou1.language
            AND hou2.language = hou1.language
            AND hou1.language IN ('US')) PIVOT (
          MAX(order_type) AS order_type,
          MAX(operating_unit) AS operating_unit,
          MAX(item_description) AS item_description,
          MAX(ship_from_organization) AS ship_from_organization FOR language IN ('US' "US"))
) x
where
2=2