ECC Discrete Manufacturing, Work Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds the information for the work order details.
Dataset Key: wip-wodetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: This data set holds the information for the work order details.
Dataset Key: wip-wodetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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_VIEW_QUALITY_NC','WIP_VIEW_ITEM','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 job_name, we.description job_description, haout.language,wdj.rowid row_id 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) ) SELECT ecc_spec_id , organization_id , organization_code , organization_name , organization_currency , loaded_date , job , job_name , job_description , assembly , assembly_uom , assembly_description , planner, job_status , status_code, language , job_status_type , job_start_quantity , scrap_quantity , completed_quantity , release_date , actual_completion_date , actual_completion_date actual_completion_date_t , job_creation_date , open_quantity , Reject_quantity, job_type , job_class , net_quantity , close_date , build_sequence , expedited_flag , base_assembly , assembly_fixed_lead_time , assembly_var_lead_time , assembly_total_lead_time , repair_order_flag , repair_order, repair_line_id , repair_number , job_assembly_description , schedule_group_name , started_today_flag , started_late_flag , release_delay_flag , completed_today_flag , completed_yesterday_flag , starting_tomorrow_flag , completing_tomorrow_flag , on_hold_flag , started_today, started_late , release_delay , completed_today , completed_yesterday , starting_tomorrow , completing_tomorrow , on_hold , job_start_delay , job_finish_delay , operation_delay_duration , pending_material_issue, scheduled_start_date , scheduled_completion_date , scheduled_start_date scheduled_start_date_t , scheduled_completion_date scheduled_completion_date_t , bill_reference , alternate_bill , supply_type , bill_revision , bill_revision_date , bill_description, routing_description, routing_reference , routing_revision , routing_revision_date , alternate_routing, 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 , Kanban, scheduleing_priority , sales_order , customer , scheduled_ship_date , actual_ship_date , sales_order_line , sales_order_id , ship_date_approach_risk_flag, ship_date_approach_risk_days, ship_date_passed_flag, ship_date_passed_days, job_note, demand_class , sales_order_line_uom , primary_quantity_reserved , quantity_reserved , sales_order_line_status , delayed_sales_order , sales_order_overdue , assembly_category , production_line , operation_shop_floor_status , start_delay_flag, finish_delay_flag , Start_delay , Completion_delay finish_delay, operation_delay_flag , operation_delay, ontrack_flag, ontrack, delayed, delayed_flag, inprogress, 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, release_delay_duation release_delay_duration, cal_rework_quantity rework_quantity, osp_operation, osp_operation_flag, link, item_link, 'fwkhp_sswafunc.gif' as wo_qa_link, 'fwkhp_sswafunc.gif' as assembly_qa_link, 'fwkhp_sswafunc.gif' as project_link, production_line_tbl, schedule_group_name_tbl, project_name_tbl, task_name_tbl, customer_tbl, CASE WHEN pending_material_issue =1 THEN 'YES' ELSE 'NO' END unissued, CASE WHEN delayed_flag =1 THEN 'YES' ELSE 'NO' END delayed_code, CASE WHEN job_status_type = 1 THEN 'UNRELEASED' WHEN job_status_type = 3 THEN 'RELEASED' WHEN job_status_type = 4 THEN 'COMPLETE' WHEN job_status_type = 5 THEN 'COMPLETE - NO CHARGES ALLOWED' WHEN job_status_type = 6 THEN 'HOLD' WHEN job_status_type = 7 THEN 'CANCELED' WHEN job_status_type = 12 THEN 'CLOSED' WHEN job_status_type = 14 THEN 'PENDING CLOSE' WHEN job_status_type = 15 THEN 'FAILED CLOSE' END job_status_code, CASE WHEN inprogress =1 THEN 'YES' ELSE 'NO' END open, CASE WHEN release_date IS NULL THEN NULL ELSE CASE WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0 THEN 0 WHEN ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity) > 1 THEN 100 ELSE 100 * ((cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity))/cal_completed_quantity) END END yield, cal_completed_quantity , dfv.*, CASE WHEN release_date IS NULL THEN NULL ELSE CASE WHEN (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity) > cal_completed_quantity OR cal_completed_quantity = 0 THEN 0 ELSE (cal_completed_quantity - (cal_scrap_quantity+cal_rejected_quantity+cal_rework_quantity)) END END tot_scrap_qty FROM (SELECT src.*, CASE WHEN osp_operation_cnt > 0 THEN yes_flag ELSE no_flag END OSP_operation, CASE WHEN osp_operation_cnt > 0 THEN 1 ELSE 2 END OSP_operation_flag, 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 Completion_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 1 ELSE 0 END delayed_flag, CASE WHEN job_status_type IN (3,6) THEN 1 ELSE 0 END inprogress, CASE WHEN NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) > job_start_quantity THEN job_start_quantity ELSE NVL(TO_NUMBER(SUBSTR(rework_quantity,1,INSTR(rework_quantity,'-A')-1)),0) END cal_rework_quantity, NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-A') +2,INSTR(rework_quantity,'-B',-1)-INSTR(rework_quantity,'-A')-2)),0)cal_scrap_quantity, NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-B') +2,INSTR(rework_quantity,'-C',-1)-INSTR(rework_quantity,'-B')-2)),0)cal_rejected_quantity, /* -- NVL(TO_NUMBER(SUBSTR(rework_quantity,INSTR(rework_quantity,'-C') +2,LENGTH(rework_quantity))),0)cal_completed_quantity */ completed_quantity cal_completed_quantity FROM (SELECT 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.row_id, yeslk.meaning yes_flag, nolk.meaning no_flag, wdj.organization_code organization_code , wdj.NAME organization_name , WIP_ECC_UTIL_PVT.get_currency_code(wdj.organization_id) organization_currency , SYSDATE loaded_date , wdj.wip_entity_id job , (SELECT SUM(CASE WHEN autocharge_type IN(3,4) THEN 1 ELSE 0 END) FROM wip_operation_resources wor WHERE wdj.wip_entity_id = wor.wip_entity_id AND wdj.organization_id = wor.organization_id ) osp_operation_cnt, wdj.job_name job_name , wdj.job_description job_description , WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) assembly , WIP_ECC_UTIL_PVT.get_primary_uom_code(wdj.organization_id,wdj.primary_item_id) assembly_uom , WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description , WIP_ECC_UTIL_PVT.get_planner(wdj.organization_id,wdj.primary_item_id) Planner, status_lkp.meaning job_status, status_lkp.lookup_code status_code, status_lkp.language language, 'fwkhp_sswafunc.gif' link , /* view_quality_lkp.meaning link,*/ 'fwkhp_sswafunc.gif' item_link , /*view_item_lkp.meaning item_link,*/ wdj.status_type job_status_type , wdj.start_quantity job_start_quantity , wdj.quantity_scrapped scrap_quantity , wdj.quantity_completed completed_quantity , wdj.date_released release_date , wdj.date_completed actual_completion_date , wdj.creation_date job_creation_date , (wdj.start_quantity - NVL(wdj.quantity_completed,0) ) open_quantity , (SELECT SUM(quantity_rejected) FROM wip_operations wo WHERE wo.wip_entity_id = wdj.wip_entity_id) Reject_quantity, type_lkp.meaning job_type , wdj.class_code job_class , wdj.net_quantity net_quantity , wdj.date_closed close_date , wdj.build_sequence build_sequence , DECODE(wdj.expedited,'Y',yeslk.meaning,nolk.meaning) expedited_flag, (SELECT msi6.concatenated_segments FROM mtl_system_items_vl msi6 WHERE msi6.inventory_item_id = WIP_ECC_UTIL_PVT.get_base_item_id(wdj.organization_id,wdj.primary_item_id) AND msi6.organization_id = wdj.organization_id ) base_assembly, DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id) ,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_fixed_lead_time, DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)) assembly_var_lead_time, NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_fixed_lead_time(wdj.organization_id,wdj.routing_reference_id)),0) + (NVL(DECODE(wdj.routing_reference_id,NULL,WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.primary_item_id),WIP_ECC_UTIL_PVT.get_variable_lead_time(wdj.organization_id,wdj.routing_reference_id)),0)*wdj.start_quantity) assembly_total_lead_time, DECODE(wdj.wip_entity_id,xref.wip_entity_id,1,2) repair_order_flag , DECODE(wdj.wip_entity_id,xref.wip_entity_id,yeslk.meaning,nolk.meaning) repair_order, xref.repair_line_id repair_line_id , rep.repair_number repair_number, WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) job_assembly_description , NVL( (SELECT schedule_group_name FROM wip_schedule_groups WHERE schedule_group_id= wdj.schedule_group_id ) ,nonelk.meaning ) schedule_group_name , (SELECT schedule_group_name FROM wip_schedule_groups WHERE schedule_group_id= wdj.schedule_group_id ) schedule_group_name_tbl , (SELECT MAX(SUM( CASE WHEN wmt.fm_operation_seq_num = wmt.to_operation_seq_num AND wmt.to_intraoperation_step_type < wmt.fm_intraoperation_step_type AND wmt.to_intraoperation_step_type IN (1,2) THEN wmt.primary_quantity WHEN wmt.fm_operation_seq_num <> wmt.to_operation_seq_num AND wmt.to_operation_seq_num < wmt.fm_operation_seq_num THEN CASE WHEN wo.operation_seq_num = wmt.to_operation_seq_num AND wmt.to_intraoperation_step_type IN (1,2) THEN wmt.primary_quantity WHEN wo.operation_seq_num = wmt.fm_operation_seq_num AND wmt.fm_intraoperation_step_type <> 1 THEN wmt.primary_quantity WHEN wo.operation_seq_num <> wmt.fm_operation_seq_num OR wo.operation_seq_num <> wmt.to_operation_seq_num THEN wmt.primary_quantity ELSE 0 END ELSE 0 END)) ||'-A' ||SUM(MAX(wo.quantity_scrapped)) ||'-B' || SUM(MAX(wo.quantity_rejected)) ||'-C' || MAX(MAX(wo.quantity_completed)) FROM wip_move_transactions WMT, Wip_Operations Wo WHERE 1 =1 AND wo.organization_id = wmt.organization_id AND wo.wip_entity_id = wmt.wip_entity_id AND ((wo.operation_seq_num BETWEEN wmt.fm_operation_seq_num AND wmt.to_operation_seq_num) OR ( wo.operation_seq_num BETWEEN wmt.to_operation_seq_num AND wmt.fm_operation_seq_num) ) AND wo.wip_entity_id = wdj.wip_entity_id GROUP BY wo.wip_entity_id, wo.organization_id, wo.operation_seq_num ) rework_quantity, (SELECT SU |
