ECC Project Manufacturing, Exceptions and Overdues, SQL9

select
x.*
from
(
select * from ( SELECT /* s55 */  /*+ no_push_pred(esed) no_push_pred(eeed) leading(p3 pp) index(pp PA_PROJECTS_U1) */    'PROJ_PA_SCHEDULE' || '-' || PP.PROJECT_ID || '-' || NVL( TO_CHAR(PT.TASK_ID), 'NO_TASK' ) || '-QUO-' ||  pl.po_header_id || '-' || 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, 'Procurement Activities 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, 'Quotation' AS ACTIVITY_TYPE, ph.segment1 AS ACTIVITY_NUMBER,  'Quotation '|| ph.segment1  AS ACTIVITY_TYPE_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,  NULL AS QUANTITY_ORDERED, NULL AS QUANTITY_DELIVERED, NULL AS UOM, pv1.vendor_name AS PO_SUPPLIER_NAME,  pvs.vendor_site_code AS PO_SUPPLIER_SITE_NAME, NULL AS PA_PROMISED_DATE, NULL AS PROMISED_EXCEPTION_DAYS,  NULL AS PA_NEED_BY_DATE, NULL AS NEED_BY_EXCEPTION_DAYS, NULL AS PA_DUE_DATE, NULL AS DUE_EXCEPTION_DAYS,  ph.start_date AS PA_EFFE_START_DATE, ESED.EXCEPTION_DAYS AS EFFE_START_EXC_DAYS, ph.end_date AS PA_EFFE_END_DATE,  EEED.EXCEPTION_DAYS AS EFFE_END_EXC_DAYS, NVL( PT.START_DATE, PP.START_DATE ) AS START_DATE, 'TRUE'  as SCHEDULE_EXCEPTION_FLAG_1 , NULL EXCEPTION_DAYS_1 ,NULL EXCEPTION_DAYS_2,'TRUE' SCHEDULE_EXCEPTION_FLAG_2,'TRUE' SCHEDULE_EXCEPTION_FLAG_3 , NULL EXCEPTION_DAYS_3 ,NULL EXCEPTION_DAYS_4,'TRUE' SCHEDULE_EXCEPTION_FLAG_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.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 /*+ leading(p31 pp) index(pp) */ pl.po_line_id, TRUNC(ph.start_date) - TRUNC(NVL( pt.completion_date, pp.completion_date )) AS EXCEPTION_DAYS  FROM po_lines_all pl, po_headers_all ph, pa_projects_all pp, pa_tasks pt  ,(select distinct project_id from pjm_project_parameters ) p31  WHERE  pl.po_header_id = ph.po_header_id  and pp.project_id = p31.project_id  AND pl.project_id = pp.project_id  AND pl.task_id = pt.task_id(+)  AND ph.start_date IS NOT NULL  AND TRUNC(ph.start_date) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, ph.start_date +1 ) )))ESED,  (  SELECT /*+ leading(p32 pp) index(pp) */ pl.po_line_id, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(ph.end_date) AS EXCEPTION_DAYS  FROM po_lines_all pl, po_headers_all ph, pa_projects_all pp, pa_tasks pt  ,(select distinct project_id from pjm_project_parameters ) p32  WHERE pl.po_header_id = ph.po_header_id  and pp.project_id = p32.project_id  AND pl.project_id = pp.project_id  AND pl.task_id = pt.task_id(+)  AND ph.start_date IS NOT NULL  AND TRUNC(ph.end_date) < TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, ph.end_date +1 ) )))EEED  ,(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 ESED.PO_LINE_ID(+) = pl.po_line_id  AND EEED.PO_LINE_ID(+) = pl.po_line_id  AND (ESED.EXCEPTION_DAYS IS NOT NULL OR EEED.EXCEPTION_DAYS IS NOT NULL)  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