ECC Project Manufacturing, Exceptions and Overdues, SQL1

select
x.*
from
(
select * from ( SELECT /* s1 */  /*+ leading(p3 pp wdj) index(pp PA_PROJECTS_U1) no_push_pred(p3) no_push_pred(sded) no_push_pred(cded) */    'PROJ_WIP_SCHEDULE' || '-' || WDJ.PROJECT_ID || '-' || NVL( TO_CHAR(WDJ.TASK_ID), 'NO_TASK' ) || '-' ||  WDJ.WIP_ENTITY_ID AS ECC_SPEC_ID, 'PROJ_EXC' AS RECORD_TYPE,  GREATEST(   NVL( WDJ.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( WE.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( ML1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( ML2.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( PP.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( PT.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( MP.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( MIF.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( HOU1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS ECC_LAST_UPDATE_DATE, 'Work Order Schedule Exceptions' AS EXCEPTION_TYPE,  PP.PROJECT_ID AS PROJECT_ID, PP.SEGMENT1 AS PROJECT_NUMBER, PP.ORG_ID AS ORG_ID,  PT.TASK_ID AS TASK_ID, PT.TASK_NUMBER AS TASK_NUMBER, HOU2.NAME AS JOB_ORGANIZATION,  WE.WIP_ENTITY_NAME AS JOB_NAME, MIF.SEGMENT1 AS ASSEMBLY, ML1.MEANING AS JOB_STATUS, ML2.MEANING AS JOB_TYPE,  WDJ.START_QUANTITY AS JOB_START_QUANTITY, WDJ.QUANTITY_COMPLETED AS JOB_QUANTITY_COMPLETED,  WDJ.SCHEDULED_START_DATE AS JOB_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE AS JOB_COMPLETION_DATE,  SDED.EXCEPTION_DAYS AS START_DATE_EXCEPTION_DAYS, CDED.EXCEPTION_DAYS AS COMPLETION_DATE_EXCEPTION_DAYS,  WDJ.SCHEDULED_START_DATE AS WO_START_DATE, WDJ.SCHEDULED_COMPLETION_DATE AS WO_COMPLETION_DATE, CASE when sded.exception_days <0
THEN
'TRUE'
when sded.exception_days >0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_1 , abs(sded.exception_days) EXCEPTION_DAYS_1 ,CASE when sded.exception_days >0
THEN
'TRUE'
when sded.exception_days <0
THEN
'FALSE'
end  as schedule_exception_flag_2 , abs(sded.exception_days) exception_days_2 ,CASE when CDED.exception_days <0
THEN
'TRUE'
when CDED.exception_days >0
THEN
'FALSE'
end  as schedule_exception_flag_3 , abs(CDED.exception_days) exception_days_3 ,CASE when CDED.exception_days >0
THEN
'TRUE'
when CDED.exception_days <0
THEN
'FALSE'
end  as schedule_exception_flag_4 , abs(CDED.exception_days) exception_days_4 , NVL( pt.start_date, pp.start_date ) AS PROJ_TASK_START_DATE,  NVL( pt.completion_date, pp.completion_date ) AS PROJ_TASK_END_DATE, pt.task_name as task_name, NVL( pt.start_date, pp.start_date ) AS START_DATE,  NVL( pt.completion_date, pp.completion_date ) AS END_DATE,  HOU1.LANGUAGE AS LANGUAGE  FROM wip_discrete_jobs wdj, wip_entities we, fnd_lookup_values ml1, fnd_lookup_values ML2, pa_projects_all pp, pa_tasks pt,  mtl_parameters mp, MTL_SYSTEM_ITEMS_B mif, HR_ALL_ORGANIZATION_UNITS_TL HOU1, HR_ALL_ORGANIZATION_UNITS_TL HOU2,  ( SELECT /*+ leading(p31 pp) index(pp) */ WDJ.WIP_ENTITY_ID, TRUNC( NVL( pt.start_date, pp.start_date )) - TRUNC(WDJ.SCHEDULED_START_DATE) AS EXCEPTION_DAYS  FROM wip_discrete_jobs wdj, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p31  WHERE wdj.project_id = pp.project_id  and pp.project_id = p31.project_id  AND wdj.task_id = pt.task_id(+)  AND wdj.SCHEDULED_START_DATE IS NOT NULL  AND TRUNC(WDJ.SCHEDULED_START_DATE) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, WDJ.SCHEDULED_START_DATE -1 ) ))  UNION ALL  SELECT /*+ leading(p32 pp) index(pp) */ WDJ.WIP_ENTITY_ID, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(WDJ.SCHEDULED_START_DATE)  FROM wip_discrete_jobs wdj, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p32  WHERE wdj.project_id = pp.project_id  and pp.project_id = p32.project_id  AND wdj.task_id = pt.task_id(+)  AND wdj.SCHEDULED_START_DATE IS NOT NULL  AND TRUNC(WDJ.SCHEDULED_START_DATE) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, WDJ.SCHEDULED_START_DATE +1 ) )))SDED,  ( SELECT /*+ leading(p33 pp) index(pp) */ WDJ.WIP_ENTITY_ID, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(wdj.scheduled_COMPLETION_date) AS EXCEPTION_DAYS  FROM wip_discrete_jobs wdj, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p33  WHERE wdj.project_id = pp.project_id  and pp.project_id = p33.project_id  AND wdj.task_id = pt.task_id(+)  AND wdj.scheduled_COMPLETION_date IS NOT NULL  AND TRUNC(wdj.scheduled_COMPLETION_date) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, wdj.scheduled_COMPLETION_date -1 ) ))  UNION ALL  SELECT /*+ leading(p34 pp) index(pp) */ WDJ.WIP_ENTITY_ID, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(wdj.scheduled_COMPLETION_date)  FROM wip_discrete_jobs wdj, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p34  WHERE wdj.project_id = pp.project_id  and pp.project_id = p34.project_id  AND wdj.task_id = pt.task_id(+)  AND wdj.scheduled_COMPLETION_date IS NOT NULL  AND TRUNC(wdj.scheduled_COMPLETION_date) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, wdj.scheduled_COMPLETION_date +1 ) )))CDED  ,(select distinct project_id from pjm_project_parameters ) p3       WHERE pp.start_date >= NVL(to_date('2021/12/12','RRRR/MM/DD HH24:MI:SS'), pp.start_date) and pp.project_id = p3.project_id  and PP.PROJECT_TYPE != 'AWARD_PROJECT'  AND PP.TEMPLATE_FLAG = 'N'  AND PP.PROJECT_ID = WDJ.PROJECT_ID  AND PT.TASK_ID(+) = WDJ.TASK_ID  AND wdj.status_type NOT  IN ( 4, 5, 7, 12 )  AND we.wip_entity_id = wdj.wip_entity_id  AND ml1.lookup_type = 'WIP_JOB_STATUS'  AND ml1.lookup_code = wdj.status_type  AND ml2.lookup_type = 'WIP_DISCRETE_JOB'  AND ml2.lookup_code = wdj.job_type  and ml1.language = ml2.language     and ml1.language = hou1.language	  AND HOU1.ORGANIZATION_ID = PP.CARRYING_OUT_ORGANIZATION_ID  AND HOU2.ORGANIZATION_ID = WDJ.ORGANIZATION_ID  AND HOU1.LANGUAGE = HOU2.LANGUAGE  AND MP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID  AND MIF.ORGANIZATION_ID(+) = WDJ.ORGANIZATION_ID  AND MIF.INVENTORY_ITEM_ID(+) = WDJ.PRIMARY_ITEM_ID  AND SDED.WIP_ENTITY_ID(+) = WDJ.WIP_ENTITY_ID  AND CDED.WIP_ENTITY_ID(+) = WDJ.WIP_ENTITY_ID  AND (SDED.EXCEPTION_DAYS IS NOT NULL OR CDED.EXCEPTION_DAYS IS NOT NULL)  AND hou1.LANGUAGE IN ('US') ) PIVOT( MAX(JOB_STATUS) AS JOB_STATUS, MAX(JOB_TYPE) AS JOB_TYPE , MAX(JOB_ORGANIZATION) AS JOB_ORGANIZATION FOR LANGUAGE IN ('US' "US"))
) x
where
2=2