ECC Project Manufacturing, Exceptions and Overdues, SQL12

select
x.*
from
(
select * from ( SELECT /* s63 */  /*+ 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' ) || '-REQ_' ||  TO_CHAR(PRL.REQUISITION_LINE_ID) AS ECC_SPEC_ID, 'PROJ_EXC' AS RECORD_TYPE,  GREATEST( NVL( PRL.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( PRD.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( PRH.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( HOU.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, 'Requisition' AS ACTIVITY_TYPE, PRH.SEGMENT1 AS ACTIVITY_NUMBER,  TO_CHAR(PRH.REQUISITION_HEADER_ID) AS ACTIVITY_ID,  reqstatus_lkup.meaning AS po_status,  MIF.SEGMENT1 AS ITEM_NUMBER,  HOU.NAME AS PO_SHIP_TO_LOCATION_NAME,  pv.vendor_name       AS po_supplier_name,       pvs.vendor_site_code AS po_supplier_site_name,  PRD.REQ_LINE_QUANTITY AS QUANTITY_ORDERED, NULL AS QUANTITY_DELIVERED, NULL AS QUANTITY_CANCELLED,  PRL.UNIT_MEAS_LOOKUP_CODE AS UOM, NULL AS PA_PROMISED_DATE, PRL.NEED_BY_DATE AS PA_NEED_BY_DATE, NULL AS PA_DUE_DATE,  NULL AS PA_EFFE_START_DATE, NULL 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, HOU.LANGUAGE AS LANGUAGE  FROM PO_REQUISITION_LINES_ALL PRL, PO_REQ_DISTRIBUTIONS_ALL PRD, PO_REQUISITION_HEADERS_ALL PRH,  po_vendors           pv,   po_vendor_sites_all  pvs,  MTL_SYSTEM_ITEMS_B MIF, PA_PROJECTS_ALL PP, PA_TASKS PT, HR_ALL_ORGANIZATION_UNITS_TL HOU,  fnd_lookup_values reqstatus_lkup  ,(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 = PRD.PROJECT_ID  AND PT.TASK_ID(+) = PRD.TASK_ID  AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID  AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID  AND prl.vendor_id = pvs.vendor_id(+)            AND prl.vendor_site_id = pvs.vendor_site_id(+)  AND pvs.vendor_id = pv.vendor_id(+)             AND NVL( NVL( PRH.CLOSED_CODE, PRH.AUTHORIZATION_STATUS ) , 'OPEN' ) NOT LIKE '%CLOSED%'  AND NVL( PRH.AUTHORIZATION_STATUS, 'NOT' ) NOT IN ('CANCELLED', 'REJECTED', 'RETURNED')  AND MIF.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID  AND MIF.INVENTORY_ITEM_ID(+) = PRL.ITEM_ID  AND HOU.ORGANIZATION_ID = PRL.DESTINATION_ORGANIZATION_ID  AND SYSDATE > NVL( PT.COMPLETION_DATE, NVL( PP.COMPLETION_DATE, SYSDATE ) )  AND reqstatus_lkup.lookup_type IN ('PO APPROVAL', 'DOCUMENT STATE')  AND reqstatus_lkup.lookup_code = NVL(prh.closed_code, prh.authorization_status)  AND reqstatus_lkup.view_application_id = 201  AND reqstatus_lkup.security_group_id = 0  AND reqstatus_lkup.language = hou.language  AND HOU.LANGUAGE in ('US')  AND reqstatus_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