ECC Project Manufacturing, Exceptions and Overdues, SQL5

select
x.*
from
(
select * from ( SELECT /* s51 */  /*+ no_push_pred(pded) no_push_pred(nded) leading(p3 pp) index(pp PA_PROJECTS_U1) */    'PROJ_PA_SCHEDULE' || '-' || PP.PROJECT_ID || '-' || NVL( TO_CHAR(PT.TASK_ID), 'NO_TASK' ) || '-PO-' ||  PL.PO_HEADER_ID || '-' || PL.PO_LINE_ID AS ECC_SPEC_ID, 'PROJ_EXC' AS RECORD_TYPE,  GREATEST( NVL( PD.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( PLL.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  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( 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, '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, 'Purchase Order' AS ACTIVITY_TYPE, PH.SEGMENT1 AS ACTIVITY_NUMBER,  'Purchase Order '|| PH.SEGMENT1  ACTIVITY_TYPE_NUMBER, TO_CHAR(PH.PO_HEADER_ID) AS ACTIVITY_ID,  postatus_lkup.meaning   AS po_status,  MIF.SEGMENT1 AS ITEM_NUMBER,  HOU.NAME AS PO_SHIP_TO_LOCATION_NAME, PD.QUANTITY_ORDERED AS QUANTITY_ORDERED, PD.QUANTITY_DELIVERED AS QUANTITY_DELIVERED,  NULL AS UOM,  pv.vendor_name  AS po_supplier_name,  pvs.vendor_site_code AS po_supplier_site_name,  PLL.PROMISED_DATE AS PA_PROMISED_DATE,  PDED.EXCEPTION_DAYS AS PROMISED_EXCEPTION_DAYS, PLL.NEED_BY_DATE AS PA_NEED_BY_DATE,  NDED.EXCEPTION_DAYS AS NEED_BY_EXCEPTION_DAYS, NULL AS PA_DUE_DATE, NULL AS DUE_EXCEPTION_DAYS, CASE
when PDED.exception_days <0
THEN
'TRUE'
when PDED.exception_days >0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_1 , abs(PDED.exception_days) EXCEPTION_DAYS_1 ,CASE when PDED.exception_days >0
THEN
'TRUE'
when PDED.exception_days <0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_2 , abs(PDED.exception_days) EXCEPTION_DAYS_2 ,CASE when NDED.exception_days <0
THEN
'TRUE'
when NDED.exception_days >0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_3 , abs(NDED.exception_days) EXCEPTION_DAYS_3 ,CASE when NDED.exception_days >0
THEN
'TRUE'
when NDED.exception_days <0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_4 , abs(NDED.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, NULL AS PA_EFFE_START_DATE, NULL AS EFFE_START_EXC_DAYS, NULL AS PA_EFFE_END_DATE, NULL AS EFFE_END_EXC_DAYS,  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_DISTRIBUTIONS_ALL PD, PO_LINE_LOCATIONS_ALL PLL, PO_LINES_ALL PL, PO_HEADERS_ALL PH,  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 postatus_lkup, ( SELECT /*+ leading(p31 pp) index(pp) */ PLL.PO_LINE_ID, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(PLL.PROMISED_DATE) AS EXCEPTION_DAYS  FROM PO_LINE_LOCATIONS_ALL PLL, PO_DISTRIBUTIONS_ALL PD, pa_projects_all pp, pa_tasks pt  , (select distinct project_id from pjm_project_parameters ) p31  WHERE pp.project_id = p31.project_id  AND pd.project_id = pp.project_id  AND pd.task_id = pt.task_id(+)  AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID  AND PLL.PROMISED_DATE IS NOT NULL  AND TRUNC(PLL.PROMISED_DATE) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, PLL.PROMISED_DATE -1 ) ))  UNION ALL  SELECT /*+ leading(p32 pp) index(pp) */ PLL.PO_LINE_ID, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(PLL.PROMISED_DATE)  FROM PO_LINE_LOCATIONS_ALL PLL, PO_DISTRIBUTIONS_ALL PD, pa_projects_all pp, pa_tasks pt  , (select distinct project_id from pjm_project_parameters ) p32  WHERE pp.project_id = p32.project_id  AND pd.project_id = pp.project_id  AND pd.task_id = pt.task_id(+)  AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID  AND PLL.PROMISED_DATE IS NOT NULL  AND TRUNC(PLL.PROMISED_DATE) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, PLL.PROMISED_DATE +1 ) )))PDED, ( SELECT /*+ leading(p33 pp) index(pp) */ PLL.PO_LINE_ID, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(PLL.NEED_BY_DATE) AS EXCEPTION_DAYS  FROM PO_LINE_LOCATIONS_ALL PLL, PO_DISTRIBUTIONS_ALL PD, pa_projects_all pp, pa_tasks pt  , (select distinct project_id from pjm_project_parameters ) p33  WHERE pp.project_id = p33.project_id  AND pd.project_id = pp.project_id  AND pd.task_id = pt.task_id(+)  AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID  AND PLL.NEED_BY_DATE IS NOT NULL  AND TRUNC(PLL.NEED_BY_DATE) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, PLL.NEED_BY_DATE -1 ) ))  UNION ALL  SELECT /*+ leading(p34 pp) index(pp) */ PLL.PO_LINE_ID, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(PLL.NEED_BY_DATE)  FROM PO_LINE_LOCATIONS_ALL PLL, PO_DISTRIBUTIONS_ALL PD, pa_projects_all pp, pa_tasks pt  , (select distinct project_id from pjm_project_parameters ) p34  WHERE pp.project_id = p34.project_id  AND pd.project_id = pp.project_id  AND pd.task_id = pt.task_id(+)  AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID  AND PLL.NEED_BY_DATE IS NOT NULL  AND TRUNC(PLL.NEED_BY_DATE) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, PLL.NEED_BY_DATE +1 ) )))NDED  , (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 = PD.PROJECT_ID  AND PT.TASK_ID(+) = PD.TASK_ID  AND PD.PO_RELEASE_ID IS NULL  AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID  AND PL.PO_LINE_ID = PLL.PO_LINE_ID  AND PH.PO_HEADER_ID = PL.PO_HEADER_ID  AND NVL( PH.CLOSED_CODE, 'OPEN' ) NOT LIKE '%CLOSED%'  AND NVL( PH.AUTHORIZATION_STATUS, 'N' ) NOT IN ('CANCELLED', 'REJECTED')  AND ph.approved_flag IN ('Y')  AND MIF.ORGANIZATION_ID = PL.ORG_ID  AND MIF.INVENTORY_ITEM_ID = PL.ITEM_ID  AND HOU.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_ID  AND pvs.vendor_site_id(+) = ph.vendor_site_id  AND pvs.vendor_id(+) = ph.vendor_id  AND pvs.vendor_id = pv.vendor_id(+)  AND PDED.PO_LINE_ID(+) = PLL.PO_LINE_ID  AND NDED.PO_LINE_ID(+) = PLL.PO_LINE_ID  AND (PDED.EXCEPTION_DAYS IS NOT NULL  OR NDED.EXCEPTION_DAYS IS NOT NULL)  AND postatus_lkup.lookup_type = 'DOCUMENT STATE'   AND ((ph.authorization_status IS NOT NULL AND postatus_lkup.lookup_code = ph.authorization_status)          OR (ph.authorization_status IS NULL AND postatus_lkup.lookup_code = 'INCOMPLETE'))  AND postatus_lkup.view_application_id = 201 AND postatus_lkup.security_group_id = 0 AND postatus_lkup.language = hou.language  AND hou.LANGUAGE IN ('US')  AND postatus_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