ECC Discrete Manufacturing, Outside Processing
Description
Categories: Enterprise Command Center
Columns: Ecc Spec Id, Ecc Op Spec Id, Ecc Rsc Spec Id, Job, Job Name, Job Scheduled Start Date, Job Scheduled Completion Date, Job Actual Completion Date, Assembly, Assembly Description ...
Columns: Ecc Spec Id, Ecc Op Spec Id, Ecc Rsc Spec Id, Job, Job Name, Job Scheduled Start Date, Job Scheduled Completion Date, Job Actual Completion Date, Assembly, Assembly Description ...
Imported from Enterprise Command Center
Description: This data set holds information about the outside processing operations
Dataset Key: wip-ospdetails2
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 the outside processing operations
Dataset Key: wip-ospdetails2
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
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_JOB_STATUS','WIP_OPERATION_STATUS','WIP_PO_NOT_CREATED','SYS_YES_NO','BOM_RESOURCE_SCHEDULE_TYPE','BOM_RESOURCE_TYPE','CST_BASIS','BOM_AUTOCHARGE_TYPE') AND view_application_id = 700 AND security_group_id = 0 ) SELECT Ecc_Spec_Id, ecc_op_spec_id, Ecc_rsc_Spec_Id, job, job_name , job_scheduled_start_date, job_scheduled_completion_date, job_actual_completion_date, job_status, assembly , assembly_description, organization_id , organization_code , organization_name , operation_code, projected_available_quantity, Standard_rate, shortage_quantity, replacement_group_num, substitute_group_num, activity, operation_sequence_number, department_description , planned_start_date , Planned_completion_date , actual_start_date , actual_completion_date , quantity_in_queue , quantity_in_run , Quantity_in_to_move , Scrap_quantity , Rejected_Quantity , Completed_quantity , Operation_Description , Operation_progress , Department, resource_code , resource_description , resource_usage_rate , resource_uom , resource_required_units , resource_applied_units , clock_in_employee, language, resource_type , resource_basis_type, resource_open_qty , resource_assigned_units , resource_start_date , resource_completion_date , resource_alternate , resource_sequence_number , scheduled_sequence_number , Rsr_actual_start_date , Rsr_actual_completion_date , auto_charge_flag , phantom_flag , operation_resource_batch , scheduled_flag , charge_type , utilization , efficiency , supplier , supplier_tbl , purchase_order_status , purchase_order_number , requisition_number , line_number , cancel_flag , need_by_date , promise_date , delayed_receipts, pending_quantity , quantity_ordered, quantity_delivered, quantity_cancelled, po_line_uom , line_item, osp_operation, starting_today, Completing_Today, starting_tomorrow, Completing_Tomorrow, starting_by, finishing_by, CASE WHEN job_status_type IN (3,6) THEN operation_start_delay ELSE 0 END operation_start_delay, CASE WHEN job_status_type IN (3,6) THEN operation_completion_delay ELSE 0 END operation_completion_delay, CASE WHEN job_status_type IN (3,6) THEN operation_delay_duration ELSE 0 END operation_delay_duration, CASE WHEN job_status_type IN (3,6) THEN operation_delayed ELSE 0 END operation_delayed, operation_status, op_status_flag, CASE WHEN op_status_flag =1 AND job_status_type = 3 THEN 1 ELSE 0 END upcoming, CASE WHEN op_status_flag =1 AND job_status_type = 3 AND operation_start_delay > 0 THEN 1 ELSE 0 END Start_delay_flag, CASE WHEN op_status_flag =1 AND job_status_type = 3 AND operation_start_delay > 0 THEN 'YES' ELSE 'NO' END Start_delay, CASE WHEN (op_status_flag IN (2,3)) AND job_status_type = 3 AND operation_completion_delay > 0 THEN 1 ELSE 0 END completion_delay_flag, CASE WHEN (op_status_flag IN (2,3)) AND job_status_type = 3 AND operation_completion_delay > 0 THEN 1 ELSE 0 END Completion_delay, CASE WHEN op_status_flag = 2 THEN 'YES' ELSE 'NO' END Open, CASE WHEN op_status_flag =1 AND job_status_type = 3 THEN 'YES' ELSE 'NO' END upcoming_code, CASE WHEN delayed_receipts =1 THEN 'YES' ELSE 'NO' END po_receipt_delay, 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 FROM ( SELECT /*+ leading(we wdj mp haout) use_nl(we wip_osp wdj mp haout wo wor bd bso) */ TO_CHAR (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||br.resource_code ||'-' ||wor.replacement_group_num ||'-' ||wdj.organization_id ||'-' ||wip_osp.rec_key ||'-OSP') Ecc_Spec_Id, TO_CHAR (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||wdj.organization_id ||'-OP') ecc_op_spec_id, (wdj.wip_entity_id ||'-' ||wo.operation_seq_num ||'-' ||br.resource_code ||'-' ||wdj.organization_id ||'-RSC') Ecc_rsc_Spec_Id, wo.wip_entity_id job, we.wip_entity_name job_name , status_lkp.meaning job_status, wor.replacement_group_num, wdj.scheduled_start_date job_scheduled_start_date , wdj.scheduled_completion_date job_scheduled_completion_date , wdj.date_completed job_actual_completion_date , 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, wdj.organization_id organization_id , wdj.status_type job_status_type, mp.organization_code organization_code , yeslk.meaning yes_flag, nolk.meaning no_flag, haout.name organization_name , bso.operation_code, 0 projected_available_quantity, to_char(decode(wor.standard_rate_flag,1,yeslk.meaning,nolk.meaning)) Standard_rate, 0 shortage_quantity, wor.substitute_group_num substitute_group_num, ca.activity activity, wo.operation_seq_num operation_sequence_number, bd.description department_description , 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_in_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 , wo.progress_percentage Operation_progress , bd.department_code Department, br.resource_code resource_code , br.description resource_description , wor.usage_rate_or_amount resource_usage_rate , wor.uom_code resource_uom , ROUND(wor.usage_rate_or_amount*DECODE(wor.basis_type, 1,wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1),6) resource_required_units , wor.applied_resource_units resource_applied_units , (SELECT pap.full_name FROM per_all_people_f pap WHERE pap.person_id = wo.employee_id AND ROWNUM = 1 ) clock_in_employee, restype.language language, restype.meaning resource_type , resbasis.meaning resource_basis_type, ROUND((wor.usage_rate_or_amount *DECODE(wor.basis_type,1, wdj.start_quantity-NVL(wo.cumulative_scrap_quantity,0),1))-wor.applied_resource_units,6) resource_open_qty , ROUND(wor.assigned_units,6) resource_assigned_units , wor.start_date resource_start_date , wor.completion_date resource_completion_date , null resource_alternate , wor.resource_seq_num resource_sequence_number , wor.schedule_seq_num scheduled_sequence_number , wor.actual_start_date Rsr_actual_start_date , wor.actual_completion_date Rsr_actual_completion_date , TO_CHAR(DECODE(wor.autocharge_type,1,yeslk.meaning,nolk.meaning)) auto_charge_flag , TO_CHAR(DECODE(wor.phantom_flag,1,yeslk.meaning,nolk.meaning)) phantom_flag , wor.batch_id operation_resource_batch , ressch.meaning scheduled_flag , reschg.meaning charge_type , NULL utilization , NULL efficiency , NVL(wip_osp.vendor_name,nonelk.meaning) supplier , wip_osp.vendor_name supplier_tbl , wip_osp.approval_status_code purchase_order_status , wip_osp.po_number purchase_order_number , wip_osp.req_number requisition_number , wip_osp.line_number line_number , DECODE(wip_osp.cancel_flag,'Y',1,2) cancel_flag , wip_osp.old_need_by_date need_by_date , wip_osp.old_promised_date promise_date , CASE WHEN wip_osp.po_number IS NOT NULL THEN CASE WHEN (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE) < 0 AND (NVL(wip_osp.old_promised_date,wip_osp.old_need_by_date) - SYSDATE) < 0 AND ((wip_osp.CANCEL_FLAG_PL IS NULL OR wip_osp.CANCEL_FLAG_PL = 'N') AND (wip_osp.QUANTITY_RECEIVED <(wip_osp.QUANTITY-wip_osp.QUANTITY_CANCELLED_PL)) AND NVL(wip_osp.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED') THEN 1 ELSE 0 END ELSE 0 END delayed_receipts, wip_osp.assembly_qty_pending pending_quantity , wip_osp.quantity_ordered, wip_osp.quantity_delivered, wip_osp.quantity_cancelled, wip_osp.assembly_primary_uom po_line_uom , wip_osp.osp_res_po_line_item line_item, CASE WHEN wip_osp.po_number IS NULL AND wip_osp.req_number IS NULL THEN nolk.meaning ELSE yeslk.meaning END osp_operation, CASE WHEN wip_osp.po_number IS NULL AND wip_osp.req_number IS NULL THEN nolk.lookup_code ELSE yeslk.lookup_code END osp_operation_flag, CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END starting_today, CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE) THEN 1 ELSE 0 END Completing_Today, CASE WHEN TRUNC(wo.first_unit_start_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END starting_tomorrow, CASE WHEN TRUNC(wo.last_unit_completion_date) = TRUNC(SYSDATE+1) THEN 1 ELSE 0 END Completing_Tomorrow, (wo.first_unit_start_date -SYSDATE) starting_by, (wo.last_unit_completion_date - SYSDATE ) finishing_by, 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) 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_start_delay, 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 ELSE 0 END operation_completion_delay, 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 1 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 1 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 1 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 1 ELSE 0 END ELSE 0 END operation_delayed, 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 FROM wip_entities we, wip_discrete_jobs wdj, bom_resources br, wip_operations wo, wip_operation_resources wor, cst_activities ca, mtl_parameters mp, hr_all_organization_units_tl haout, ( SELECT ml1.meaning, ml1.lookup_code, ml1.language FROM lookup_details ml1 WHERE ml1.lookup_type = 'WIP_JOB_STATUS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 ) status_lkp, (SELECT restype.meaning, restype.language, restype.lookup_code FROM lookup_details restype WHERE restype.lookup_type = 'BOM_RESOURCE_TYPE' AND restype.view_application_id = 700 AND restype.security_group_id = 0 )restype, (SELECT resbasis.meaning, resbasis.language, resbasis.lookup_code FROM lookup_details resbasis WHERE resbasis.lookup_type = 'CST_BASIS' AND resbasis.view_application_id = 700 AND resbasis.security_group_id = 0 )resbasis, (SELECT reschg.meaning, reschg.language , reschg.lookup_code FROM lookup_details reschg WHERE reschg.lookup_type = 'BOM_AUTOCHARGE_TYPE' AND reschg.view_application_id = 700 AND reschg.security_group_id = 0 )reschg, (SELECT ressch.meaning, ressch.language, ressch.lookup_code FROM lookup_details ressch WHERE ressch.lookup_type = 'BOM_RESOURCE_SCHEDULE_TYPE' AND ressch.view_application_id = 700 AND ressch.security_group_id = 0 )ressch, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 1 ) operation_ns_lkp, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 2 ) operation_run_lkp, (SELECT f.meaning, f.lookup_code, f.language FROM lookup_details f WHERE f.lookup_type = 'WIP_OPERATION_STATUS' AND f.view_application_id = 700 AND f.security_group_id = 0 AND f.lookup_code = 3 ) operation_com_lkp, (SELECT wip_osp1.rec_key, wip_osp1.approval_status_code, wip_osp1.po_number, wip_osp1.req_number, wip_osp1.cancel_flag, wip_osp1.old_need_by_date, wip_osp1.old_promised_date, wip_osp1.CANCEL_FLAG_PL, wip_osp1.QUANTITY_RECEIVED, wip_osp1.QUANTITY, wip_osp1.QUANTITY_CANCELLED_PL, wip_osp1.CLOSED_CODE, wip_osp1.assembly_qty_pending, wip_osp1.quantity_ordered, wip_osp1.quantity_delivered, wip_osp1.quantity_cancelled, wip_osp1.assembly_primary_uom, wip_osp1.organization_id, wip_osp1.wip_entity_id, wip_osp1.operation_seq_num, wip_osp1.vendor_name, wip_osp1.po_item_number osp_res_po_line_item, wip_osp1.line_number, wip_osp1.resource_seq_num FROM (SELECT /*+ leading(pd) index(pd PO_DISTRIBUTIONS_N8) */ ph.segment1 po_number, ph.type_lookup_code po_req_type, ph.authorization_status approval_status_code, ps.need_by_date old_need_by_date, ps.promised_date old_promised_date, ps.CANCEL_FLAG CANCEL_FLAG_PL, ps.QUANTITY_RECEIVED QUANTITY_RECEIVED, ps.QUANTITY QUANTITY, ps.QUANTITY_CANCELLED QUANTITY_CANCELLED_PL, ps.CLOSED_CODE CLOSED_CODE, wip_osp.converttoprimarymoveqty(pl.item_id, pd.destination_organization_id, greatest(pd.quantity_ordered - NVL(pd.quantity_delivered, 0) - NVL(pd.quantity_cancelled, 0), 0), pl.unit_meas_lookup_code, WIP_ECC_UTIL_PVT.get_primary_uom_code(we.organization_id,we.primary_item_id), (SELECT wor.usage_rate_or_amount |
