ECC Project Manufacturing Genealogy & Trace, Work Orders

select
x.*
from
(
SELECT * FROM (
          WITH pjm_projects AS (
            SELECT DISTINCT project_id FROM pjm_project_parameters
          ),
          wip_rejected AS (
            SELECT wdj.organization_id,
                   wdj.wip_entity_id,
                   SUM(wo.quantity_rejected) AS quantity
            FROM wip_operations wo,
                 wip_discrete_jobs wdj,
                 pjm_projects
            WHERE wo.wip_entity_id = wdj.wip_entity_id
              AND wo.organization_id = wdj.organization_id
              AND wdj.project_id = pjm_projects.project_id
            GROUP BY wdj.organization_id,
                     wdj.wip_entity_id
          )
          SELECT
            'PJM_GNT_WO' || '-' || pp.project_id
                           || '-' || NVL(TO_CHAR(pt.task_id), 'NO_TASK')
                           || '-' || we.wip_entity_id
                           || '-' || wdj.organization_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,
            pp.segment1                                                                               AS project_number,
            pp.name                                                                                   AS project_name,
            pt.task_id,
            pt.task_number,
            pt.task_name,
            we.wip_entity_id                                                                          AS job_id,
            we.wip_entity_name                                                                        AS job_name,
            wdj.description                                                                           AS job_description,
            wdj.class_code                                                                            AS job_class,
            flv.meaning                                                                               AS job_status,
            wdj.bom_revision,
            wdj.scheduled_start_date                                                                  AS job_start_date,
            wdj.scheduled_completion_date                                                             AS job_completion_date,
            wdj.organization_id                                                                       AS inv_org_id,
            mp.organization_code                                                                      AS inv_org_code,
            hou.name                                                                                  AS inv_org,
            wdj.primary_item_id                                                                       AS assembly_item_id,
            msi.concatenated_segments                                                                 AS assembly,
            msit.description                                                                          AS assembly_description,
            msi.primary_uom_code                                                                      AS assembly_primary_uom,
            msi.serial_number_control_code,
            wdj.end_item_unit_number,
            wdj.start_quantity,
            wdj.quantity_completed,
            wdj.quantity_scrapped,
            wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped                       AS quantity_remaining,
            wip_rejected.quantity                                                                     AS quantity_rejected,
            msn.serial_number,
            mmt.locator_id                                                                            AS txn_locator_id,
            CASE
              WHEN we.entity_type IN (1, 3)
               AND wdj.status_type = 6 THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS hold_flag,
            CASE
              WHEN we.entity_type IN (1, 3)
               AND wdj.quantity_scrapped > 0 THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS scrap_flag,
            CASE
              WHEN we.entity_type IN (1, 3)
               AND wip_rejected.quantity > 0 THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS reject_flag,
            CASE
              WHEN wdj.status_type NOT IN (4, 5, 7, 12)
               AND TRUNC(wdj.scheduled_start_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, wdj.scheduled_start_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS schedule_exception_flag_1,
            CASE
              WHEN wdj.status_type NOT IN (4, 5, 7, 12)
               AND TRUNC(wdj.scheduled_start_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, wdj.scheduled_start_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS schedule_exception_flag_2,
            CASE
              WHEN wdj.status_type NOT IN (4, 5, 7, 12)
               AND TRUNC(wdj.scheduled_completion_date) < TRUNC(NVL(pt.start_date, NVL(pp.start_date, wdj.scheduled_completion_date - 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS schedule_exception_flag_3,
            CASE
              WHEN wdj.status_type NOT IN (4, 5, 7, 12)
               AND TRUNC(wdj.scheduled_completion_date) > TRUNC(NVL(pt.completion_date, NVL(pp.completion_date, wdj.scheduled_completion_date + 1))) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS schedule_exception_flag_4,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(wdj.scheduled_start_date)                AS exception_days_1,
            TRUNC(wdj.scheduled_start_date) - TRUNC(NVL(pt.completion_date, pp.completion_date))      AS exception_days_2,
            TRUNC(NVL(pt.start_date, pp.start_date)) - TRUNC(wdj.scheduled_completion_date)           AS exception_days_3,
            TRUNC(wdj.scheduled_completion_date) - TRUNC(NVL(pt.completion_date, pp.completion_date)) AS exception_days_4,
            CASE
              WHEN wdj.status_type NOT IN (4, 5, 7, 12)
               AND wdj.start_quantity > wdj.quantity_completed
               AND SYSDATE > NVL(pt.completion_date, pp.completion_date) THEN
                'FALSE'
              ELSE
                'TRUE'
            END                                                                                       AS overdue_flag,
            hou.language
          FROM pa_projects_all pp,
               pa_tasks pt,
               wip_entities we,
               wip_discrete_jobs wdj,
               mtl_system_items_kfv msi,
               mtl_system_items_tl msit,
               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,
               wip_rejected,
               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 wip_rejected.organization_id (+) = wdj.organization_id
            AND wip_rejected.wip_entity_id (+) = wdj.wip_entity_id
            AND msi.organization_id (+) = wdj.organization_id
            AND msi.inventory_item_id (+) = wdj.primary_item_id
            AND msit.organization_id (+) = msi.organization_id
            AND msit.inventory_item_id (+) = msi.inventory_item_id
            AND mmt.organization_id (+) = wdj.organization_id
            AND mmt.inventory_item_id (+) = wdj.primary_item_id
            AND mmt.transaction_source_id (+) = wdj.wip_entity_id
            AND mmt.transaction_source_type_id (+) = 5
            AND mmt.transaction_action_id (+) = 31
            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_JOB_STATUS'
            AND flv.lookup_code = wdj.status_type
            AND NVL(msit.language, hou.language) = hou.language
            AND flv.language = hou.language
            AND hou.language IN ('US')) PIVOT (
          MAX(job_status) AS job_status,
          MAX(inv_org) AS inv_org,
          MAX(assembly_description) AS assembly_description FOR language IN ('US' "US"))
) x
where
2=2