ECC Project Manufacturing, Exceptions and Overdues, SQL7

Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Record Type, Ecc Last Update Date, Exception Type, Project Id, Project Number, Org Id, Task Id, Task Number, Activity Type ...
Imported from Enterprise Command Center
Description: Project Manufacturing Exceptions and Overdues
Dataset Key: pjm-exceptions-overdues
Query Procedure: PJM_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: PJM_ECC_DATASECURITY_PKG_PUB.GetFilterAttributeValues
select
x.*
from
(
select * from ( SELECT /* s53 */  /*+ 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' ) || '-REQ-' ||  prl.REQUISITION_HEADER_ID || '-' || 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, '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, 'Requisition' AS ACTIVITY_TYPE, prh.segment1 AS ACTIVITY_NUMBER,  TO_CHAR(prh.requisition_header_id) AS ACTIVITY_ID,  NVL( prh.closed_code, prh.authorization_status ) AS PO_STATUS, mif.SEGMENT1 AS ITEM_NUMBER,  hou.name AS PO_SHIP_TO_LOCATION_NAME, prd.req_line_quantity AS QUANTITY_ORDERED, NULL AS QUANTITY_DELIVERED,  prl.unit_meas_lookup_code AS UOM,  pv.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, prl.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,  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_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,  ( SELECT /*+ leading(p31 pp) index(pp) */ prl.requisition_line_id, TRUNC(NVL( pt.start_date, pp.start_date )) - TRUNC(prl.need_by_date) AS EXCEPTION_DAYS  FROM po_requisition_lines_all prl, po_req_distributions_all prd, pa_projects_all pp, pa_tasks pt    ,( select distinct project_id from pjm_project_parameters ) p31  WHERE pp.project_id = p31.project_id  and prd.project_id = pp.project_id  AND prd.task_id = pt.task_id(+)  AND prd.requisition_line_id = prl.requisition_line_id  AND prl.need_by_date IS NOT NULL  AND TRUNC(prl.need_by_date) < TRUNC(NVL( pt.start_date, NVL( pp.start_date, prl.need_by_date -1 ) ))  UNION ALL  SELECT /*+ leading(p32 pp) index(pp) */ prl.requisition_line_id, TRUNC(NVL( pt.completion_date, pp.completion_date )) - TRUNC(prl.need_by_date)  FROM po_requisition_lines_all prl, po_req_distributions_all prd, pa_projects_all pp, pa_tasks pt    ,( select distinct project_id from pjm_project_parameters ) p32  WHERE pp.project_id = p32.project_id  and prd.project_id = pp.project_id  AND prd.task_id = pt.task_id(+)  AND prd.requisition_line_id = prl.requisition_line_id  AND prl.need_by_date IS NOT NULL  AND TRUNC(prl.need_by_date) > TRUNC(NVL( pt.completion_date, NVL( pp.completion_date, prl.need_by_date +1 ) )))NDED  ,( select distinct project_id from pjm_project_parameters ) p3     WHERE pp.start_date >= NVL(to_date('','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 NDED.requisition_line_id = prl.requisition_line_id  AND HOU.LANGUAGE IN ('US') )  PIVOT( MAX(PO_SHIP_TO_LOCATION_NAME) AS PO_SHIP_TO_LOCATION_NAME FOR LANGUAGE IN ('US' "US"))
) x
where
2=2
Parameter Name SQL text Validation
Operating Unit
x.org_id in (select haouv.organization_id from hr_all_organization_units_vl haouv where haouv.name=:operating_unit)
LOV