ECC Discrete Manufacturing, Outside Processing
Description
Categories: Enterprise Command Center
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
Run
ECC Discrete Manufacturing, Outside Processing 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_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_queue+quantity_in_run inprogress_quantity, 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, buyer_name FROM ( SELECT 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, wip_osp.buyer_name, 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, wip_osp1.buyer_name FROM (SELECT /*+ leading ( pd pl ) index ( pd PO_DISTRIBUTIONS_N8 ) use(pd pl) */ 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), |
