ECC Project Manufacturing, Exceptions and Overdues, SQL14

select
x.*
from
(
select * from ( SELECT /* s65 */  /*+ leading(p3 pp) index(pp PA_PROJECTS_U1) no_push_pred(p3) */    'PROJ_PA_OVERDUE' || '-' ||  PP.PROJECT_ID || '-' || NVL( TO_CHAR(PT.TASK_ID), 'NO_TASK' ) || '-QT_' || TO_CHAR(PL.PO_LINE_ID) AS ECC_SPEC_ID, 'PROJ_EXC' AS RECORD_TYPE,  GREATEST( NVL( PL.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( PH.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( PV1.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( PVS.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( HLOC.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( 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( FSP.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ) AS ECC_LAST_UPDATE_DATE, 'Overdue Procurement Activities' 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, 'Quotation' AS ACTIVITY_TYPE, PH.SEGMENT1 AS ACTIVITY_NUMBER,  TO_CHAR(PH.PO_HEADER_ID) AS ACTIVITY_ID,  quotestatus_lkup.meaning AS po_status,  MIF.SEGMENT1 AS ITEM_NUMBER, HLOT2.LOCATION_CODE AS PO_SHIP_TO_LOCATION_NAME,  PV1.VENDOR_NAME AS PO_SUPPLIER_NAME, PVS.VENDOR_SITE_CODE AS PO_SUPPLIER_SITE_NAME, NULL AS QUANTITY_ORDERED,  NULL AS QUANTITY_DELIVERED, NULL AS QUANTITY_CANCELLED, NULL AS UOM, NULL AS PA_PROMISED_DATE, NULL AS PA_NEED_BY_DATE,  NULL AS PA_DUE_DATE, PH.START_DATE AS PA_EFFE_START_DATE, PH.END_DATE AS PA_EFFE_END_DATE,  NVL( PT.START_DATE, PP.START_DATE ) AS START_DATE, NVL( PT.COMPLETION_DATE, PP.COMPLETION_DATE ) AS END_DATE,  HLOT2.LANGUAGE AS LANGUAGE  FROM PO_LINES_ALL PL, PO_HEADERS_ALL PH, PO_VENDORS PV1, PO_VENDOR_SITES_ALL PVS,  fnd_lookup_values quotestatus_lkup,  HR_LOCATIONS_ALL HLOC, HR_LOCATIONS_ALL_TL HLOT, HR_LOCATIONS_ALL_TL HLOT2,  MTL_SYSTEM_ITEMS_B MIF, PA_PROJECTS_ALL PP, PA_TASKS PT, FINANCIALS_SYSTEM_PARAMS_ALL FSP  ,(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 = PL.PROJECT_ID  AND PT.TASK_ID(+) = PL.TASK_ID  AND PH.PO_HEADER_ID = PL.PO_HEADER_ID  AND PH.TYPE_LOOKUP_CODE = 'QUOTATION'  AND PV1.VENDOR_ID(+) = PH.VENDOR_ID  AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID  AND PH.STATUS_LOOKUP_CODE < > 'C'  AND NVL( HLOC.BUSINESS_GROUP_ID, NVL( HR_GENERAL.GET_BUSINESS_GROUP_ID, -99 ) ) = NVL( HR_GENERAL.GET_BUSINESS_GROUP_ID, NVL( HLOC.BUSINESS_GROUP_ID, -99 ) )  AND HLOC.LOCATION_ID = HLOT.LOCATION_ID  AND HLOC.SHIP_TO_LOCATION_ID(+) = PH.SHIP_TO_LOCATION_ID  AND HLOC.SHIP_TO_LOCATION_ID = HLOT2.LOCATION_ID  AND HLOT.LANGUAGE = HLOT2.LANGUAGE  AND MIF.ORGANIZATION_ID = FSP.INVENTORY_ORGANIZATION_ID  AND FSP.ORG_ID = PH.ORG_ID  AND MIF.INVENTORY_ITEM_ID(+) = PL.ITEM_ID  AND SYSDATE > NVL( PT.COMPLETION_DATE, NVL( PP.COMPLETION_DATE, SYSDATE ) )  AND quotestatus_lkup.lookup_type = 'RFQ/QUOTE STATUS'  AND quotestatus_lkup.lookup_code = ph.status_lookup_code  AND quotestatus_lkup.view_application_id = 201  AND quotestatus_lkup.security_group_id = 0  AND quotestatus_lkup.language = hlot2.language  AND HLOT2.LANGUAGE in ('US')  AND quotestatus_lkup.language IN ('US') )  PIVOT ( MAX(PO_SHIP_TO_LOCATION_NAME) AS PO_SHIP_TO_LOCATION_NAME,  MAX (po_status) AS po_status FOR LANGUAGE IN ('US' "US"))
) x
where
2=2