ECC Discrete Manufacturing, Work Order Delays
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds the delay information for the work order details.
Dataset Key: wip-wodelaydetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
Description: This data set holds the delay information for the work order details.
Dataset Key: wip-wodelaydetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure:
Run
ECC Discrete Manufacturing, Work Order Delays and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( WITH lookup_details as (SELECT /*+ materialize */ meaning, lookup_code, language, lookup_type, security_group_id, view_application_id FROM fnd_lookup_values WHERE lookup_type IN ('WIP_ECC_WEEK_BINS','WIP_JOB_STATUS','WIP_DISCRETE_JOB','WIP_SUPPLY','WIP_TOLERANCE_TYPE','SYS_YES_NO','WIP_PURGE_REPORT_TYPE') AND view_application_id = 700 AND security_group_id = 0 ), wdj AS (SELECT /*+ materialize use_nl(mp haout)*/ wdj.*, mp.organization_code , haout.NAME , we.wip_entity_name , we.description job_description, haout.language FROM wip_entities we, mtl_parameters mp, hr_all_organization_units_tl haout, wip_discrete_jobs wdj WHERE we.wip_entity_id = wdj.wip_entity_id AND we.entity_type IN (1,3) AND mp.organization_id = we.organization_id AND haout.organization_id = mp.organization_id AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND ( nvl(wdj.date_completed, wdj.scheduled_completion_date) >= SYSDATE - 30 OR wdj.status_type IN (3,6) OR ( wdj.status_type = 1 AND wdj.scheduled_start_date <= SYSDATE + 7 ) ) ) SELECT STARTED_TODAY_FLAG ,STARTED_LATE_FLAG ,RELEASE_DELAY_FLAG ,COMPLETED_TODAY_FLAG ,COMPLETED_YESTERDAY_FLAG ,STARTING_TOMORROW_FLAG ,COMPLETING_TOMORROW_FLAG ,ON_HOLD_FLAG ,JOB_START_DELAY ,JOB_FINISH_DELAY ,RELEASE_DELAY_DUATION ,OPERATION_DELAY_DURATION ,PENDING_MATERIAL_ISSUE ,SCHEDULED_START_DATE ,SCHEDULED_COMPLETION_DATE ,ALTERNATE_BILL ,BILL_DESCRIPTION ,SUPPLY_TYPE ,BILL_REVISION ,BILL_REVISION_DATE ,ROUTING_REFERENCE ,ROUTING_REVISION ,ALTERNATE_ROUTING ,ROUTING_REVISION_DATE ,REQUESTED_DUE_DATE ,COMPLETION_SUBINVENTORY ,SERIALIZATION_START_OPERATION ,COMPLETION_LOCATOR ,PROJECT_NAME ,PROJECT_NUMBER ,UNIT_NUMBER ,TASK_NAME ,TASK_NUMBER ,WO_DEMAND_CLASS ,LOT_NUMBER ,SALES_ORDER ,CUSTOMER ,DEMAND_CLASS ,PRODUCTION_LINE ,STARTED_TODAY ,STARTED_LATE ,RELEASE_DELAY ,COMPLETED_TODAY ,COMPLETED_YESTERDAY ,STARTING_TOMORROW ,COMPLETING_TOMORROW ,ON_HOLD ,START_DELAY_DURATION ,case when START_DELAY_DURATION <> 0 then round(START_DELAY_DURATION/24,6) else 0 end START_DELAY_DURATION_DAYS ,case when START_DELAY_DURATION <> 0 then round(START_DELAY_DURATION*60,6) else 0 end START_DELAY_DURATION_MIN ,COMPLETION_DELAY_DURATION ,case when COMPLETION_DELAY_DURATION <> 0 then round(COMPLETION_DELAY_DURATION/24,6) else 0 end COMPLETION_DELAY_DURATION_DAYS ,case when COMPLETION_DELAY_DURATION <> 0 then round(COMPLETION_DELAY_DURATION*60,6) else 0 end COMPLETION_DELAY_DURATION_MIN ,START_DELAY_FLAG ,FINISH_DELAY_FLAG ,START_DELAY ,FINISH_DELAY ,OPERATION_DELAY_FLAG ,OPERATION_DELAY ,ONTRACK_FLAG ,ONTRACK ,DELAYED ,DELAYED_FILTER ,DELAYED_FLAG ,INPROGRESS ,ECC_SPEC_ID ,ORGANIZATION_ID ,ORGANIZATION_CODE ,YES_FLAG ,NO_FLAG ,ORGANIZATION_NAME ,LOADED_DATE ,JOB ,JOB_NAME ,ASSEMBLY ,JOB_STATUS ,STATUS_CODE ,LANGUAGE ,JOB_STATUS_TYPE ,RELEASE_DATE ,ACTUAL_COMPLETION_DATE ,JOB_CREATION_DATE ,JOB_TYPE ,JOB_CLASS ,CLOSE_DATE ,BUILD_SEQUENCE ,SCHEDULE_GROUP_NAME ,CASE WHEN pending_material_issue =1 THEN 'YES' ELSE 'NO' END UNISSUED FROM (SELECT src.*, CASE WHEN started_today_flag = 1 THEN yes_flag ELSE no_flag END started_today, CASE WHEN started_late_flag = 1 THEN yes_flag ELSE no_flag END started_late , CASE WHEN release_delay_flag = 1 THEN yes_flag ELSE no_flag END release_delay , CASE WHEN completed_today_flag = 1 THEN yes_flag ELSE no_flag END completed_today , CASE WHEN completed_yesterday_flag = 1 THEN yes_flag ELSE no_flag END completed_yesterday , CASE WHEN starting_tomorrow_flag = 1 THEN yes_flag ELSE no_flag END starting_tomorrow , CASE WHEN completing_tomorrow_flag = 1 THEN yes_flag ELSE no_flag END completing_tomorrow , CASE WHEN on_hold_flag = 1 THEN yes_flag ELSE no_flag END on_hold , CASE WHEN job_status_type IN (1) THEN job_start_delay ELSE 0 END start_delay_duration, CASE WHEN job_status_type IN (3,6) THEN job_finish_delay ELSE 0 END Completion_delay_duration, CASE WHEN job_start_delay > 0 AND job_status_type IN (1) THEN 1 ELSE 0 END start_delay_flag, CASE WHEN job_finish_delay > 0 AND job_status_type IN (3,6) THEN 1 ELSE 0 END finish_delay_flag , CASE WHEN job_start_delay > 0 AND job_status_type IN (1) THEN yes_flag ELSE no_flag END Start_delay , CASE WHEN job_finish_delay > 0 AND job_status_type IN (3,6) THEN yes_flag ELSE no_flag END finish_delay , CASE WHEN operation_delay_duration > 0 AND job_status_type IN (3,6) THEN 1 ELSE 0 END operation_delay_flag , CASE WHEN operation_delay_duration > 0 AND job_status_type IN (3,6) THEN yes_flag ELSE no_flag END operation_delay, CASE WHEN job_status_type IN (3,6) AND( job_start_delay > 0 OR job_finish_delay > 0 OR operation_delay_duration > 0) THEN 0 WHEN job_status_type NOT IN (3,6) THEN 0 ELSE 1 END ontrack_flag, CASE WHEN job_status_type IN (3,6) AND( job_start_delay > 0 OR job_finish_delay > 0 OR operation_delay_duration > 0) THEN no_flag WHEN job_status_type NOT IN (3,6) THEN no_flag ELSE yes_flag END ontrack, CASE WHEN (job_start_delay > 0 AND job_status_type IN (1)) OR (job_finish_delay > 0 AND job_status_type IN (3,6)) OR (operation_delay_duration > 0 AND job_status_type IN (3,6)) THEN yes_flag ELSE no_flag END delayed, CASE WHEN (job_start_delay > 0 AND job_status_type IN (1)) OR (job_finish_delay > 0 AND job_status_type IN (3,6)) OR (operation_delay_duration > 0 AND job_status_type IN (3,6)) THEN 'YES' ELSE 'NO' END delayed_filter, CASE WHEN (job_start_delay > 0 AND job_status_type IN (1)) OR (job_finish_delay > 0 AND job_status_type IN (3,6)) OR (operation_delay_duration > 0 AND job_status_type IN (3,6)) THEN 1 ELSE 0 END delayed_flag, CASE WHEN job_status_type IN (3,6) THEN 1 ELSE 0 END inprogress FROM (SELECT /*+ PUSH_PRED(MSI4) PUSH_PRED(MSI8) */ TO_CHAR (wdj.wip_entity_id ||'-' ||wdj.organization_id ||'-' ||mr.supply_source_header_id ||'-' ||ool1.line_id ||'-JOB') ecc_spec_id, wdj.organization_id organization_id , wdj.organization_code organization_code , yeslk.meaning yes_flag, nolk.meaning no_flag, wdj.NAME organization_name , SYSDATE loaded_date , wdj.wip_entity_id job , wdj.wip_entity_name job_name , WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly , status_lkp.meaning job_status, status_lkp.lookup_code status_code, status_lkp.language language, wdj.status_type job_status_type , wdj.date_released release_date , wdj.date_completed actual_completion_date , wdj.creation_date job_creation_date , type_lkp.meaning job_type , wdj.class_code job_class , wdj.date_closed close_date , wdj.build_sequence build_sequence , NVL( (SELECT schedule_group_name FROM wip_schedule_groups WHERE schedule_group_id= wdj.schedule_group_id ) ,nonelk.meaning ) schedule_group_name , CASE WHEN NVL(TRUNC(wdj.actual_start_date),TRUNC(wdj.date_released)) = TRUNC(SYSDATE) AND wdj.status_type <> 1 THEN 1 ELSE 0 END started_today_flag, CASE WHEN wdj.scheduled_start_date < SYSDATE AND (NVL(wdj.actual_start_date,wdj.date_released) > wdj.scheduled_start_date) THEN 1 ELSE 0 END started_late_flag, CASE WHEN wdj.status_type = 3 AND wdj.date_released > wdj.scheduled_start_date THEN 1 ELSE 0 END release_delay_flag, CASE WHEN TRUNC(wdj.date_completed) = TRUNC(SYSDATE) THEN 1 ELSE 0 END completed_today_flag, CASE WHEN TRUNC(wdj.date_completed) = TRUNC(SYSDATE-1) THEN 1 ELSE 0 END completed_yesterday_flag, CASE WHEN TRUNC(wdj.scheduled_start_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END starting_tomorrow_flag, CASE WHEN TRUNC(wdj.scheduled_completion_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END completing_tomorrow_flag, CASE WHEN wdj.status_type = 6 THEN 1 ELSE 0 END on_hold_flag, CASE WHEN wdj.status_type IN( 1,3) AND wdj.scheduled_start_date < SYSDATE THEN CASE WHEN (SYSDATE - (wdj.scheduled_start_date )) > 0 THEN (SYSDATE - (wdj.scheduled_start_date ))* 24 ELSE 0 END END job_start_delay, CASE WHEN wdj.date_completed IS NOT NULL AND wdj.date_completed > wdj.scheduled_completion_date THEN CASE WHEN (wdj.date_completed - (wdj.scheduled_completion_date )) > 0 THEN (wdj.date_completed - (wdj.scheduled_completion_date ))* 24 ELSE 0 END END job_finish_delay, CASE WHEN wdj.status_type = 3 AND wdj.date_released > wdj.scheduled_start_date THEN CASE WHEN (wdj.date_released - (wdj.scheduled_start_date)) > 0 THEN (wdj.date_released - (wdj.scheduled_start_date))* 24 ELSE 0 END END release_delay_duation, NVL( (SELECT MAX( CASE WHEN wo.actual_completion_date IS NOT NULL AND wo.actual_completion_date > (wo.last_unit_completion_date ) THEN CASE WHEN (wo.actual_completion_date - (wo.last_unit_completion_date )) > 0 THEN (wo.actual_completion_date - (wo.last_unit_completion_date ))* 24 ELSE 0 END WHEN wo.actual_completion_date IS NULL AND (wo.last_unit_completion_date ) < sysdate AND (wo.quantity_in_queue > 0 OR wo.quantity_running > 0) THEN CASE WHEN (sysdate- (wo.last_unit_completion_date )) > 0 THEN (sysdate- (wo.last_unit_completion_date ))* 24 ELSE 0 END WHEN wo.actual_start_date IS NOT NULL AND wo.actual_start_date > (wo.first_unit_start_date ) THEN CASE WHEN (wo.actual_start_date-(wo.first_unit_start_date )) > 0 THEN (wo.actual_start_date-(wo.first_unit_start_date ))* 24 ELSE 0 END WHEN wo.actual_start_date IS NULL AND (wo.first_unit_start_date ) < sysdate AND NVL(wo.quantity_in_queue,0) = 0 AND NVL(wo.quantity_running,0) = 0 AND NVL(wo.quantity_completed,0) = 0 THEN CASE WHEN (sysdate-(wo.first_unit_start_date )) > 0 THEN (sysdate-(wo.first_unit_start_date ))* 24 ELSE 0 END ELSE 0 END) FROM wip_operations wo WHERE wo.organization_id = wdj.organization_id AND wo.wip_entity_id = wdj.wip_entity_id AND (( wo.quantity_in_queue > 0 OR wo.quantity_running > 0 ) OR ((wdj.start_quantity - wo.quantity_completed - wo.cumulative_scrap_quantity) > 0 )) ), 0) operation_delay_duration, (SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM wip_operations wo, wip_requirement_operations wro WHERE wo.organization_id = wdj.organization_id AND wo.wip_entity_id = wdj.wip_entity_id AND wdj.status_type IN (3,4,6) AND wro.wip_entity_id = wdj.wip_entity_id AND wro.quantity_per_assembly >= 0 AND wro.organization_id = wdj.organization_id AND wro.operation_seq_num = wo.operation_seq_num(+) AND ( (wro.quantity_per_assembly * (DECODE(wro.basis_type,2,1,NVL((wo.quantity_completed ), wdj.quantity_completed))) > wro.quantity_issu |