ECC Project Manufacturing, Exceptions and Overdues, SQL3

select
x.*
from
(
select * from ( SELECT /* s3 */  /*+ leading(p3 pp) index(pp PA_PROJECTS_U1) no_push_pred(p3) no_push_pred(rded) no_push_pred(pded) */    'PROJ_SO_SCHEDULE' || '-' || OOL.PROJECT_ID || '-' || NVL( TO_CHAR(OOL.TASK_ID), 'NO_TASK' ) || '-' ||  OOL.HEADER_ID || '-' || OOL.LINE_ID AS ECC_SPEC_ID, 'PROJ_EXC' AS RECORD_TYPE,  GREATEST( NVL( OOL.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( OOH.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) ,  NVL( HOU.last_update_date, to_date('01-01-1990', 'DD-MM-YYYY')) , NVL( MSI.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')) ) AS ECC_LAST_UPDATE_DATE, 'Sales Order 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, OOL.HEADER_ID AS SO_HEADER_ID, OOL.LINE_ID AS SO_LINE_ID, ooh.order_number AS SO_NUMBER,   ool.schedule_status_code AS ORDER_LINE_SCHEDULE_STATUS,   ool.schedule_ship_date AS SCHEDULE_DATE,    OE_FLEX_UTIL.GET_CONCAT_VALUE(OOL.LINE_NUMBER, OOL.SHIPMENT_NUMBER, OOL.OPTION_NUMBER, OOL.COMPONENT_NUMBER, OOL.SERVICE_NUMBER) AS SO_LINE_NUMBER,  msi.segment1 AS ITEM_NUMBER, hou.name AS WAREHOUSE, NVL( ool.ordered_quantity, 0 ) AS QUANTITY, ool.request_date AS REQUEST_DATE,  RDED.exception_days AS request_date_exception_days, ool.promise_date AS PROMISE_DATE, PDED.exception_days AS promise_date_exception_days, CASE
when RDED.exception_days <0
THEN
'TRUE'
when RDED.exception_days >0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_1 , abs(RDED.exception_days) EXCEPTION_DAYS_1 ,CASE when RDED.exception_days >0
THEN
'TRUE'
when RDED.exception_days <0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_2 , abs(RDED.exception_days) EXCEPTION_DAYS_2 ,CASE when PDED.exception_days <0
THEN
'TRUE'
when PDED.exception_days >0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_3 , abs(PDED.exception_days) EXCEPTION_DAYS_3 ,CASE when PDED.exception_days >0
THEN
'TRUE'
when PDED.exception_days <0
THEN
'FALSE'
end  as SCHEDULE_EXCEPTION_FLAG_4 , abs(PDED.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, NVL( pt.start_date, pp.start_date ) AS START_DATE, NVL( pt.completion_date, pp.completion_date ) AS END_DATE  FROM oe_order_lines_all ool, hr_organization_units hou, oe_order_headers_all ooh, MTL_SYSTEM_ITEMS_B msi, pa_projects_all pp, pa_tasks pt,  ( SELECT /*+ leading(p31 pp) index(pp) */ ool.line_id, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(ool.request_date) AS EXCEPTION_DAYS  FROM oe_order_lines_all ool, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p31  WHERE ool.project_id = pp.project_id   and pp.project_id = p31.project_id  	AND ool.task_id = pt.task_id(+)  	AND ool.request_date IS NOT NULL  	AND TRUNC(ool.request_date) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, ool.request_date -1 ) ))  UNION ALL  SELECT /*+ leading(p32 pp) index(pp) */ ool.line_id, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(ool.request_date)  FROM oe_order_lines_all ool, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p32  WHERE ool.project_id = pp.project_id   and pp.project_id = p32.project_id  	AND ool.task_id = pt.task_id(+)  	AND ool.request_date IS NOT NULL  	AND TRUNC(ool.request_date) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, ool.request_date +1 ) )))RDED,  ( SELECT /*+ leading(p33 pp) index(pp) */ ool.line_id, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(ool.promise_date) AS EXCEPTION_DAYS  FROM oe_order_lines_all ool, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p33  WHERE ool.project_id = pp.project_id   and pp.project_id = p33.project_id  	AND ool.task_id = pt.task_id(+)  	AND ool.promise_date IS NOT NULL  	AND TRUNC(ool.promise_date) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, ool.promise_date -1 ) ))  UNION ALL  SELECT /*+ leading(p34 pp) index(pp) */ ool.line_id, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(ool.promise_date)  FROM oe_order_lines_all ool, pa_projects_all pp, pa_tasks pt   ,(select distinct project_id from pjm_project_parameters ) p34  WHERE ool.project_id = pp.project_id   and pp.project_id = p34.project_id  	AND ool.task_id = pt.task_id(+)  	AND ool.promise_date IS NOT NULL  	AND TRUNC(ool.promise_date) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, ool.promise_date +1 ) )))PDED  ,(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 = OOL.PROJECT_ID  AND PT.TASK_ID(+) = OOL.TASK_ID  AND NVL( OOL.CANCELLED_FLAG, 'N' ) != 'Y'  AND NVL( ool.open_flag, 'Y' ) != 'N'  AND ooh.header_id = ool.header_id  AND hou.organization_id = ool.ship_from_org_id  AND msi.organization_id = ool.ship_from_org_id  AND msi.inventory_item_id = ool.inventory_item_id  AND ool.line_id = RDED.line_id(+)  AND ool.line_id = PDED.line_id(+)  AND (RDED.EXCEPTION_DAYS IS NOT NULL  OR PDED.exception_days IS NOT NULL)  )  
) x
where
2=2