ECC Project Manufacturing, Exceptions and Overdues, SQL6
Description
Categories: Enterprise Command Center
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
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
Run
ECC Project Manufacturing, Exceptions and Overdues, SQL6 and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( select * from ( SELECT /* s52 */ /*+ 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' ) || '-BR-' || 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( PR.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, 'Blanket Release' AS ACTIVITY_TYPE, ph.segment1 || ',' || TO_CHAR(pr.release_num) AS ACTIVITY_NUMBER, 'Blanket Release '|| ph.segment1 || ',' || TO_CHAR(pr.release_num) AS ACTIVITY_TYPE_NUMBER, TO_CHAR(ph.po_header_id) || ',' || TO_CHAR(pr.po_release_id) AS ACTIVITY_ID, brstatus_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_releases_all pr, po_line_locations_all pll, po_lines_all pl, po_headers_all ph, MTL_SYSTEM_ITEMS_B mif, pa_projects_all pp, po_vendors pv, po_vendor_sites_all pvs, pa_tasks pt, HR_ALL_ORGANIZATION_UNITS_TL hou, fnd_lookup_values brstatus_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 NOT NULL AND pr.po_release_id = pd.po_release_id 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 NVL( pr.closed_code, 'OPEN' ) NOT LIKE '%CLOSED%' AND NVL( pr.authorization_status, 'N' ) NOT IN ('CANCELLED', 'REJECTED') AND NVL( pr.cancel_flag, 'N' ) <> 'Y' AND MIF.ORGANIZATION_ID = PD.DESTINATION_ORGANIZATION_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 brstatus_lkup.lookup_type = 'DOCUMENT STATE' AND ((ph.authorization_status IS NOT NULL AND brstatus_lkup.lookup_code = ph.authorization_status) OR (ph.authorization_status IS NULL AND brstatus_lkup.lookup_code = 'INCOMPLETE')) AND brstatus_lkup.view_application_id = 201 AND brstatus_lkup.security_group_id = 0 AND brstatus_lkup.language = hou.language AND HOU.LANGUAGE IN ('US') AND brstatus_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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Operating Unit |
|
LOV |