ECC Project Manufacturing, Exceptions and Overdues, SQL4

➜ Back to Library
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
Run ECC Project Manufacturing, Exceptions and Overdues, SQL4 and other Oracle EBS reports with Blitz Report™ on our demo environment
select
x.*
from
(
select * from ( SELECT /* s4 */  /*+ leading(p3 pp) index(pp PA_PROJECTS_U1) no_push_pred(p3) */    'PROJ_SO_OVERDUE' || '-' || OOL.PROJECT_ID || '-' || NVL( TO_CHAR(OOL.task_id), 'NO_TASK' ) || '-' ||  OOH.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( 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')) ) AS ECC_LAST_UPDATE_DATE, 'Overdue Sales Orders ' 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.LINE_ID AS SO_LINE_ID, OOH.HEADER_ID AS SO_HEADER_ID,  ooh.order_number AS SO_NUMBER, mif.segment1 AS ITEM_NUMBER, hou.name AS WAREHOUSE,  ool.schedule_status_code AS ORDER_LINE_SCHEDULE_STATUS ,  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,  NVL( ool.ordered_quantity, 0 ) AS QUANTITY, ool.schedule_ship_date AS SCHEDULE_DATE,  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 mif, pa_projects_all pp, PA_TASKS PT  ,(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.visible_demand_flag, 'N' ) = 'N'  AND NVL( OOL.CANCELLED_FLAG, 'N' ) != 'Y'  AND NVL( ool.open_flag, 'Y' ) != 'N'  AND ooh.header_id = ool.header_id  AND NVL( ool.source_type_code, 'NOT' ) = 'INTERNAL'  AND hou.organization_id = ool.ship_from_org_id  AND mif.organization_id = ool.ship_from_org_id  AND MIF.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID  AND sysdate > NVL( pt.start_date, NVL( pp.start_date, sysdate ) )  )  
) 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
Download
 
Related Blitz Reports
ECC Project Manufacturing, Sales Orders XXONT Orders and Lines ONT Orders and Lines PPF_WP3_OM_DETAILS Yanmar - Discount on order lines check ONT DIFOT ECC Project Manufacturing, Exceptions and Overdues, SQL3 ECC Discrete Manufacturing, Work Orders ECC Project Manufacturing Genealogy & Trace, Project Locators
Blitz Report™

Blitz Report™ provides multiple benefits: