ECC Discrete Manufacturing, Operations
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds information about work order operations.
Dataset Key: wip-opdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: This data set holds information about work order operations.
Dataset Key: wip-opdetails
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 ( SELECT ecc_spec_id , wo_op_seq, row_id, job , job_name , job_scheduled_start_date, job_scheduled_completion_date, job_actual_completion_date, planned_start_date scheduled_start_date_t, planned_completion_date SCHEDULE_COMPLET_DATE_T, job_status, organization_id , organization_currency, organization_code, organization_name, language, operation_sequence_number, department , department_description , planned_start_date , planned_completion_date , Actual_start_date , Actual_completion_date , Actual_start_date Actual_start_date_t , Actual_completion_date Actual_completion_date_t, quantity_in_queue , quantity_in_run , quantity_to_move , scrap_quantity , rejected_quantity , completed_quantity , operation_description , progress_percentage , CASE WHEN osp_operation_flag = 1 THEN yes_flag ELSE no_flag END osp_operation, osp_operation_flag, count_point, autocharge, backflush, clock_in_employee, serialized_link, Job_Description, operation_progress actual_operation_progress, CASE WHEN TO_NUMBER(SUBSTR(operation_progress,1,INSTR(operation_progress,'-A') -1)) > 100 THEN 100 ELSE NVL(TO_NUMBER(SUBSTR(operation_progress,1,INSTR(operation_progress,'-A') -1)),0) END operation_progress, TO_NUMBER(SUBSTR(operation_progress,INSTR(operation_progress,'-A') +2,INSTR(operation_progress,'-B',-1)-INSTR(operation_progress,'-A')-2))elapsed_time, TO_NUMBER(SUBSTR(operation_progress,INSTR(operation_progress,'-B') +2,LENGTH(operation_progress)))lead_time, operation_delay_duration , 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, CASE WHEN operation_delay_duration > 0 and job_status_type in (3,6) THEN yes_flag ELSE no_flag END Operation_delayed, CASE WHEN operation_delay_duration > 0 and job_status_type in (3,6) THEN 1 ELSE 0 END Operation_delayed_flag, CASE WHEN job_status_type IN (3,6) AND 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 operation_delay_duration > 0 THEN no_flag WHEN job_status_type NOT IN (3,6) THEN no_flag ELSE yes_flag END ontrack, operation_status, op_status_flag, op_status_flag OP_STATUS_CODE, CASE WHEN op_status_flag =1 AND job_status_type = 3 THEN 1 ELSE 0 END upcoming, shop_floor_status_code, Shop_Floor_Status_Step, Move_status, Status_Move_Flag Move_status_flag, Rework_quantity, CASE WHEN op_status_flag in (2,3) THEN CASE WHEN completed_quantity = 0 OR completed_quantity IS NULL THEN NULL WHEN (scrap_quantity + rejected_quantity + Rework_quantity) > completed_quantity THEN 0 WHEN (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity))/ completed_quantity > 1 THEN 100 ELSE 100 * (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity))/ completed_quantity END END yield, CASE WHEN completed_quantity = 0 OR completed_quantity IS NULL THEN NULL WHEN (scrap_quantity + rejected_quantity + Rework_quantity) > completed_quantity THEN 0 ELSE (completed_quantity - (scrap_quantity + rejected_quantity + Rework_quantity)) END tot_scrap_qty, Standard_operation_code, Standard_operation_code_tbl, Assembly , assembly_description, pending_material_issue, item_cost * scrap_quantity Scrap_value, CASE WHEN (NVL(scrap_quantity,0) + NVL(completed_quantity,0) + NVL(rejected_quantity,0)) =0 THEN 1 ELSE (NVL(scrap_quantity,0) + NVL(completed_quantity,0) + NVL(rejected_quantity,0)) END total_quantity, scheduled_quantity FROM (WITH wdj AS (SELECT /*+ materialize */ 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) 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) ) ) , 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_INTRAOPERATION_STEP','WIP_OPERATION_STATUS','WIP_SHOPFLOOR_MOVE_STATUS','WIP_JOB_STATUS','SYS_YES_NO','WIP_PURGE_REPORT_TYPE') AND view_application_id = 700 AND security_group_id = 0 ) SELECT TO_CHAR (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||wdj.organization_id ||'-OP') Ecc_Spec_Id, TO_CHAR (wdj.Job_name ||':' ||wo.operation_seq_num ) wo_op_seq, wdj.row_id, yeslk.meaning yes_flag, nolk.meaning no_flag, nonelk.meaning none_flag, nonelk.language language, wo.wip_entity_id Job, wdj.Job_name , status_lkp.meaning job_status, WIP_ECC_UTIL_PVT.get_item_cost(wdj.primary_item_id,wdj.organization_id) item_cost, wdj.scheduled_start_date job_scheduled_start_date , wdj.scheduled_completion_date job_scheduled_completion_date , wdj.date_completed job_actual_completion_date , wdj.organization_id organization_id , WIP_ECC_UTIL_PVT.get_currency_code(wdj.organization_id) organization_currency , wdj.organization_code organization_code, wdj.NAME organization_name , wdj.date_completed job_completion_date, wo.operation_seq_num operation_sequence_number , wo.scheduled_quantity scheduled_quantity, bd.description department_description , NVL((SELECT MIN( yeslk.lookup_code) FROM wip_operation_resources wor WHERE wo.wip_entity_id = wor.wip_entity_id AND wo.operation_seq_num = wor.operation_seq_num AND wo.organization_id = wor.organization_id AND wor.autocharge_type IN(3,4) ),nolk.lookup_code) osp_operation_flag, wo.first_unit_start_date planned_start_date , wo.last_unit_completion_date Planned_completion_date , wo.actual_start_date Actual_start_date , wo.actual_completion_date Actual_completion_date , NVL(wo.quantity_in_queue,0) quantity_in_queue , NVL(wo.quantity_running,0) quantity_in_run , NVL(wo.quantity_waiting_to_move,0) Quantity_to_move , to_number(NVL(wo.quantity_scrapped,0)) Scrap_quantity , to_number(NVL(wo.quantity_rejected,0)) Rejected_Quantity , NVL(wo.quantity_completed,0) Completed_quantity , wo.description Operation_Description , wdj.status_type job_status_type, to_char(decode(wo.count_point_type,3,nolk.meaning,yeslk.meaning)) autocharge, to_char(decode(wo.count_point_type,1,yeslk.meaning,nolk.meaning)) count_point, to_char(decode(wo.backflush_flag,1,yeslk.meaning,nolk.meaning)) backflush, wo.progress_percentage progress_percentage , wdj.job_description, (select pap.full_name from per_all_people_f pap where pap.person_id = wo.employee_id and rownum = 1) clock_in_employee, to_char(case when wdj.serialization_start_op is not null and wdj.serialization_start_op <= wo.operation_seq_num and (to_number(nvl(wo.quantity_in_queue,0)) + to_number(nvl(wo.quantity_running,0)) + to_number(nvl(wo.quantity_waiting_to_move,0)) + to_number(nvl(wo.quantity_rejected,0)) + to_number(nvl(wo.quantity_scrapped,0))) <> 0 then yeslk.meaning else null end) serialized_link, TO_CHAR( (SELECT (SUM(SUM(( CASE WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999) THEN 0 ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.applied_resource_units END))) ) /(SUM(MAX(( CASE WHEN (CASE WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999) THEN 0 ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.usage_rate_or_amount END) = 0 THEN NULL ELSE (CASE WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999) THEN 0 ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)* wor.usage_rate_or_amount END) END )* CASE WHEN least(wor.assigned_units,bdr.capacity_units) =0 OR bdr.utilization = 0 OR bdr.efficiency =0 OR wo.scheduled_quantity =0 THEN NULL ELSE ( DECODE(wor.basis_type,2,1,wo.scheduled_quantity) /least(wor.assigned_units,bdr.capacity_units)/(NVL( bdr.utilization,1))/(NVL(bdr.efficiency,1))) END ))) ||'-A' ||(SUM(SUM(( CASE WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999) THEN 0 ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)* wor.applied_resource_units END))) ) ||'-B' ||(SUM(MAX(( CASE WHEN (WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code) = -99999) THEN 0 ELSE WIP_ECC_UTIL_PVT.get_inv_conv(wor.uom_code)*wor.usage_rate_or_amount END) * CASE WHEN least(wor.assigned_units,bdr.capacity_units) =0 OR bdr.utilization = 0 OR bdr.efficiency =0 OR wo.scheduled_quantity =0 THEN NULL ELSE (DECODE(wor.basis_type,2,1,wo.scheduled_quantity) /least(wor.assigned_units,bdr.capacity_units)/(NVL( bdr.utilization,1))/(NVL(bdr.efficiency,1))) END ))) FROM wip_operation_resources wor, bom_department_resources bdr WHERE wor.wip_entity_id = wo.wip_entity_id AND wo.operation_seq_num = wor.operation_seq_num AND wo.department_id = bdr.department_id AND wor.resource_id = bdr.resource_id AND wor.scheduled_flag <>2 GROUP BY TO_CHAR(NVL(TO_CHAR(wor.schedule_seq_num),rowidtochar(wor.rowid))) )) operation_progress, bd.department_code Department, 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 )) 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 )) 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 )) 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 )) ELSE 0 END ELSE 0 END operation_delay_duration, 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 END completion_delay_duration, CASE 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 END start_delay_duration, CASE WHEN ( wo.quantity_completed >= ( wdj.start_quantity - wo.cumulative_scrap_quantity ) ) OR wdj.status_type IN ( 4,5,7,12) THEN operation_com_lkp.meaning WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null) OR ( wo.quantity_completed > 0 ) ) and wdj.status_type in (3,6) THEN operation_run_lkp.meaning ELSE operation_ns_lkp.meaning END operation_status, CASE WHEN ( wo.quantity_completed >= ( wdj.start_quantity - wo.cumulative_scrap_quantity ) ) OR wdj.status_type IN (4,5,7,12) THEN operation_com_lkp.lookup_code WHEN ((Wo.quantity_in_queue + wo.quantity_running > 0) OR (wo.date_last_moved is not null) OR ( wo.quantity_completed > 0 ) ) and wdj.status_type in (3,6) THEN operation_run_lkp.lookup_code ELSE operation_ns_lkp.lookup_code END op_status_flag, shop_floor_status_step.meaning shop_floor_status_step, wsfs.shop_floor_status_code shop_floor_status_code, wsfs.Status_Move_Flag, CASE WHEN wdj.status_type in (3,6) THEN CASE WHEN wsfs.Status_Move_Flag =1 THEN move_status_lkp.meaning WHEN wsfs.Status_Move_Flag =2 THEN nomove_status_lkp.meaning ELSE NULL END ELSE NULL END Move_status, ( SELECT 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 ) FROM wip_move_transactions WMT 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 ) rework_quantity, NVL(bso.operation_code,nonelk.meaning) Standard_operation_code, bso.operation_code Standard_operation_code_tbl, WIP_ECC_UTIL_PVT.get_concatenated_segments(wdj.organization_id,wdj.primary_item_id) Assembly, WIP_ECC_UTIL_PVT.get_description(wdj.organization_id,wdj.primary_item_id) assembly_description, NULL pending_material_issue FROM wdj wdj, wip_operations wo, bom_departments bd, bom_standard_operations bso, (SELECT wsfs.intraoperation_step_type , wsfs.organization_id, wsfs.wip_entity_id, wsfs.operation_seq_num, wsfs.shop_floor_status_code, Wsfsc.Status_Move_Flag FROM wip_shop_floor_statuses wsfs, wip_shop_floor_status_codes wsfsc WHERE NVL(NVL(wsfs.shop_floor_status_code(+),'-1'),NVL(wsfsc.shop_floor_status_code,'-1')) = NVL(wsfsc.shop_floor_status_code,'-1') )wsfs, (SELECT nonelk.meaning, nonelk.lookup_code, nonelk.language FROM lookup_details nonelk WHERE nonelk.lookup_type = 'WIP_PURGE_REPORT_TYPE' AND nonelk.view_application_id = 700 AND nonelk.lookup_code = '4' AND nonelk.security_group_id = 0 ) nonelk, (SELECT nolk.meaning, nolk.lookup_code, nolk.language FROM lookup_details nolk WHERE nolk.lookup_type = 'SYS_YES_NO' AND nolk.view_application_id = 700 AND nolk.lookup_code = '2' AND nolk.security_group_id = 0 ) nolk, |
