ECC Discrete Manufacturing, Move Transactions
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds scrap, reject, rework, and yield information.
Dataset Key: wip-opqualitydetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Description: This data set holds scrap, reject, rework, and yield information.
Dataset Key: wip-opqualitydetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Discrete Manufacturing, Move Transactions and other Oracle EBS reports with Blitz Report™ on our demo environment
select x.* from ( SELECT * FROM ( SELECT ecc_spec_id, job, job_name , assembly , assembly_description , organization_id, organization_currency, language, department_description , Department, standard_operation_code, standard_operation_code_tbl, operation_sequence_number , planned_start_date , Planned_completion_date , Actual_start_date , Actual_completion_date , quantity_in_queue , quantity_in_run , Quantity_to_move , op_Scrap_quantity , op_Rejected_Quantity , op_Completed_quantity , Operation_Description , osp_operation_flag, osp_operation, Operation_progress , scrap_reason, Reject_Reason, Scrap_reason_tbl, Reject_Reason_tbl, Scrap_quantity, Reject_quantity, scrap_flag, reject_flag, transaction_id, item_cost, Scrap_quantity * item_cost scrap_value, Reject_quantity * item_cost reject_value FROM ( SELECT TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||wo.operation_seq_num||'-'||wmt.transaction_id||'-TXN') ecc_spec_id, wo.wip_entity_id job, we.wip_entity_name job_name , 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 , wo.organization_id, WIP_ECC_UTIL_PVT.get_currency_code(wo.organization_id) organization_currency , yeslk.language language, bd.description department_description , bd.department_code Department, NVL(bso.operation_code,nonelk.meaning) standard_operation_code, bso.operation_code standard_operation_code_tbl, wo.operation_seq_num operation_sequence_number , 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)) op_Scrap_quantity , to_number(NVL(wo.quantity_rejected,0)) op_Rejected_Quantity , NVL(wo.quantity_completed,0) op_Completed_quantity , wo.description Operation_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, NVL((SELECT MAX(CASE WHEN autocharge_type IN(3,4) THEN yeslk.meaning ELSE nolk.meaning END) 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 ),nolk.meaning) osp_operation, wo.progress_percentage Operation_progress , TO_CHAR( CASE WHEN wmt.to_intraoperation_step_type = 5 or wmt.fm_intraoperation_step_type = 5 THEN NVL(mtr.description,nonelk.meaning) ELSE NULL END) Scrap_reason, TO_CHAR( CASE WHEN wmt.to_intraoperation_step_type = 4 or wmt.fm_intraoperation_step_type = 4 THEN NVL(mtr.description,nonelk.meaning) ELSE NULL END) Reject_Reason, TO_CHAR( CASE WHEN wmt.to_intraoperation_step_type = 5 or wmt.fm_intraoperation_step_type = 5 THEN mtr.description ELSE NULL END) Scrap_reason_tbl, TO_CHAR( CASE WHEN wmt.to_intraoperation_step_type = 4 or wmt.fm_intraoperation_step_type = 4 THEN mtr.description ELSE NULL END) Reject_Reason_tbl, wmt.to_intraoperation_step_type to_intraoperation_step_type, CASE WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type =5 THEN wmt.primary_quantity WHEN wmt.fm_operation_seq_num = wo.operation_seq_num and wmt.fm_intraoperation_step_type = 5 THEN -1*wmt.primary_quantity ELSE 0 END Scrap_quantity, CASE WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type =4 THEN wmt.primary_quantity WHEN wmt.fm_operation_seq_num = wo.operation_seq_num and wmt.fm_intraoperation_step_type =4 THEN -1*wmt.primary_quantity ELSE 0 END Reject_quantity, CASE WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type = 5 THEN 1 WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type <> 5 THEN 2 END scrap_flag, CASE WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type = 4 THEN 1 WHEN wmt.to_operation_seq_num = wo.operation_seq_num and wmt.to_intraoperation_step_type <> 4 THEN 2 END reject_flag, wmt.transaction_id transaction_id, WIP_ECC_UTIL_PVT.get_item_cost(wdj.primary_item_id,wdj.organization_id) item_cost FROM wip_discrete_jobs wdj, wip_entities we, bom_departments bd, bom_standard_operations bso, (SELECT nonelk.meaning, nonelk.lookup_code, nonelk.language FROM fnd_lookup_values 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 fnd_lookup_values 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, (SELECT yeslk.meaning, yeslk.lookup_code, yeslk.language FROM fnd_lookup_values yeslk WHERE yeslk.lookup_type = 'SYS_YES_NO' AND yeslk.view_application_id = 700 AND yeslk.lookup_code = '1' AND yeslk.security_group_id = 0 ) yeslk, wip_operations wo, wip_move_transactions wmt, mtl_transaction_reasons mtr WHERE we.wip_entity_id = wdj.wip_entity_id AND wo.wip_entity_id = wdj.wip_entity_id AND bd.department_id = wo.department_id AND wo.standard_operation_id = bso.standard_operation_id(+) AND NVL(bso.operation_type,1) = 1 AND bso.line_id IS NULL AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND we.entity_type IN (1,3) AND wo.organization_id = wmt.organization_id AND wo.wip_entity_id = wmt.wip_entity_id AND nonelk.language = yeslk.language AND nonelk.language = nolk.language AND (wmt.to_intraoperation_step_type in (4,5) OR wmt.fm_intraoperation_step_type in (4,5)) 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 wmt.reason_id = mtr.reason_id(+) 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 )) )WHERE language in ('US') ) PIVOT ( MAX(STANDARD_OPERATION_CODE) AS STANDARD_OPERATION_CODE , MAX(SCRAP_REASON) AS SCRAP_REASON ,MAX(OSP_OPERATION) AS OSP_OPERATION, MAX(REJECT_REASON) AS REJECT_REASON FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
