ECC Project Manufacturing, Exceptions and Overdues, SQL2

select
x.*
from
(
select * from (SELECT /* s2 */  /*+ leading(p3 pp) index(pp PA_PROJECTS_U1) no_push_pred(p3) */    'PROJ_WIP_OVERDUE' || '-' ||  WDJ.PROJECT_ID || '-' || NVL( TO_CHAR(WDJ.TASK_ID), 'NO_TASK' ) || '-' || TO_CHAR(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, 'Overdue Work Orders' 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,  	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, HR_ALL_ORGANIZATION_UNITS_TL HOU1, HR_ALL_ORGANIZATION_UNITS_TL HOU2,  	PA_TASKS PT, MTL_PARAMETERS MP, MTL_SYSTEM_ITEMS_B MIF  	,(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_TYPE != 'AWARD_PROJECT'  AND PP.TEMPLATE_FLAG = 'N'  and pp.project_id = p3.project_id  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 NVL( WDJ.START_QUANTITY, 0 ) > NVL( TO_NUMBER(WDJ.QUANTITY_COMPLETED), 0 )  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 SYSDATE > NVL( PT.COMPLETION_DATE, NVL( PP.COMPLETION_DATE, SYSDATE ) )  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