ECC Discrete Manufacturing, Serial Numbers
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Description: This data set holds information about serial-level details for work orders
Dataset Key: wip-serialdetails
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 serial-level details for work orders
Dataset Key: wip-serialdetails
Query Procedure: WIP_ECC_UTIL_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetFilterAttributeValues
Run
ECC Discrete Manufacturing, Serial Numbers 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_SERIAL_STATUS','WIP_INTRAOPERATION_STEP','SERIAL_NUM_STATUS','WIP_SERIAL_YIELD') AND view_application_id = 700 AND security_group_id = 0 ) SELECT /*+ leading(we) */ TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id, wdj.wip_entity_id job, we.wip_entity_name job_name, status_lkp.meaning job_status, wdj.organization_id organization_id, mp.organization_code organization_code, CASE WHEN (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 ) > 0 THEN 1 ELSE 2 END osp_operation_flag, 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 , (SELECT acat.concatenated_segments FROM mtl_item_catalog_groups_b_kfv acat WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id) ) assembly_catalog_grp, (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 ) assembly_base_model, msn.serial_number , msn.wip_entity_id wip_entity_id , NULL parent_object_id, msn.operation_seq_num operation_seq_num , msn.intraoperation_step_type intraoperation_step_type , msn.cycles_since_mark , msn.cycles_since_new , msn.cycles_since_overhaul , msn.cycles_since_repair , msn.cycles_since_visit , msn.number_of_repairs , msn.territory_code , msn.time_since_mark , msn.time_since_new , msn.time_since_overhaul , msn.time_since_repair , msn.time_since_visit , wip_serial_status_code.meaning wip_serial_status, wip_serial_status_code.lookup_code wip_serial_status_flag, assembly_serial_op_step.meaning assembly_serial_op_step, inv_serial_status_code.meaning inventory_serial_status, inv_serial_status_code.lookup_code inv_serial_status_code, inv_serial_status_code.language, CASE WHEN intraoperation_step_type IN (5) THEN 'Scrap' WHEN intraoperation_step_type IN (4) THEN 'Reject' ELSE 'In progress' END serial_type, (SELECT SUM( CASE WHEN fm_operation_seq_num > to_operation_seq_num OR (fm_operation_seq_num = to_operation_seq_num AND to_intraoperation_Step_type IN (1,2) AND fm_intraoperation_Step_type IN (3,4,5)) THEN 1 ELSE 0 END ) FROM wip_move_transactions wmt, wip_serial_move_transactions wsmt WHERE wmt.organization_id = we.organization_id AND wmt.to_operation_seq_num = msn.operation_seq_num AND wmt.to_intraoperation_Step_type = msn.intraoperation_step_type AND wmt.to_intraoperation_Step_type IN (4,5) AND wsmt.transaction_id = wmt.transaction_id AND wsmt.assembly_serial_number = msn.serial_number AND wmt.wip_entity_id = msn.wip_entity_id ) rework_quantity, CASE WHEN intraoperation_step_type IN (5) THEN scrap_lkp.meaning WHEN intraoperation_step_type IN (4) THEN reject_lkp.meaning ELSE NULL END yield FROM mtl_serial_numbers msn, mtl_parameters mp, wip_entities we, wip_discrete_jobs wdj, (SELECT ml2.meaning, ml2.language, to_number(ml2.lookup_code) LOOKUP_CODE FROM lookup_details ml2 WHERE ml2.lookup_type = 'WIP_SERIAL_STATUS' AND ml2.view_application_id = 700 AND ml2.security_group_id = 0 ) wip_serial_status_code , (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 ml3.meaning, ml3.language, ml3.lookup_code FROM lookup_details ml3 WHERE ml3.lookup_type = 'WIP_INTRAOPERATION_STEP' AND ml3.view_application_id = 700 AND ml3.security_group_id = 0 ) assembly_serial_op_step , (SELECT ml4.lookup_code, ML4.MEANING, ml4. language FROM lookup_details ml4 WHERE ml4.lookup_type = 'SERIAL_NUM_STATUS' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 ) inv_serial_status_code , (SELECT ml4.lookup_code, ML4.MEANING, ml4. language FROM lookup_details ml4 WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 AND ml4.lookup_code =1 ) scrap_lkp, (SELECT ml4.lookup_code, ML4.MEANING, ml4. language FROM lookup_details ml4 WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 AND ml4.lookup_code =2 ) reject_lkp WHERE msn.wip_entity_id = we.wip_entity_id AND we.organization_id = mp.organization_id AND inv_serial_status_code.lookup_code(+) = msn.current_status AND assembly_serial_op_step.lookup_code(+) = msn.intraoperation_step_type AND wip_serial_status_code.lookup_code(+) = DECODE(msn.operation_seq_num, NULL, 1, 2) AND wdj.status_type = status_lkp.lookup_code (+) AND status_lkp.language = nvl(inv_serial_status_code.language, status_lkp.language) AND status_lkp.language = nvl(assembly_serial_op_step.language, status_lkp.language) AND status_lkp.language = nvl(wip_serial_status_code.language, status_lkp.language) AND status_lkp.language = nvl(scrap_lkp.language, status_lkp.language) AND status_lkp.language = nvl(reject_lkp.language, status_lkp.language) AND wdj.wip_entity_id = we.wip_entity_id AND we.primary_item_id = msn.inventory_item_id AND we.entity_type IN (1,3) 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 )) UNION ALL SELECT ecc_spec_id, job, job_name, job_status, organization_id, organization_code, osp_operation_flag, assembly , assembly_uom , assembly_description , assembly_catalog_grp, assembly_base_model, serial_number , wip_entity_id , parent_object_id, operation_seq_num operation_seq_num , intraoperation_step_type intraoperation_step_type , cycles_since_mark , cycles_since_new , cycles_since_overhaul , cycles_since_repair , cycles_since_visit , number_of_repairs , territory_code , time_since_mark , time_since_new , time_since_overhaul , time_since_repair , time_since_visit , wip_serial_status , wip_serial_status_flag , assembly_serial_op_step , inventory_serial_status , inv_serial_status_code, language, serial_type, rework_quantity, CASE WHEN rework_quantity > 0 THEN rework_flag ELSE accepted_flag END yield FROM (SELECT /*+ leading(we) */ TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id, wdj.wip_entity_id job, we.wip_entity_name job_name, wdj.organization_id organization_id, mp.organization_code organization_code, CASE WHEN (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 ) > 0 THEN 1 ELSE 2 END osp_operation_flag, 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 , (SELECT acat.concatenated_segments FROM mtl_item_catalog_groups_b_kfv acat WHERE acat.item_catalog_group_id = WIP_ECC_UTIL_PVT.get_item_catalog_group_id(wdj.organization_id,wdj.primary_item_id) ) assembly_catalog_grp, (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 ) assembly_base_model, msn.serial_number , we.wip_entity_id , mog.parent_object_id, msn.operation_seq_num operation_seq_num , msn.intraoperation_step_type intraoperation_step_type , msn.cycles_since_mark , msn.cycles_since_new , msn.cycles_since_overhaul , msn.cycles_since_repair , msn.cycles_since_visit , msn.number_of_repairs , msn.territory_code , msn.time_since_mark , msn.time_since_new , msn.time_since_overhaul , msn.time_since_repair , msn.time_since_visit , msn.gen_object_id serial_gen_obj_id, wip_serial_status.meaning wip_serial_status , 3 wip_serial_status_flag , wip_intraoperation_step.meaning assembly_serial_op_step , inv_serial_status.meaning inventory_serial_status , inv_serial_status.lookup_code inv_serial_status_code, inv_serial_status.language, 'Completed' serial_type, (SELECT SUM( CASE WHEN fm_operation_seq_num > to_operation_seq_num OR (fm_operation_seq_num = to_operation_seq_num AND to_intraoperation_Step_type IN (1,2) AND fm_intraoperation_Step_type IN (3,4,5)) THEN 1 ELSE 0 END ) FROM wip_move_transactions wmt, wip_serial_move_transactions wsmt WHERE wmt.organization_id = we.organization_id AND wmt.to_operation_seq_num = msn.operation_seq_num AND wmt.to_intraoperation_Step_type = msn.intraoperation_step_type AND wmt.to_intraoperation_Step_type IN (4,5) AND wsmt.transaction_id = wmt.transaction_id AND wsmt.assembly_serial_number = msn.serial_number AND wmt.wip_entity_id = msn.wip_entity_id ) rework_quantity, rework_lkp.meaning rework_flag, accepted_lkp.meaning accepted_flag, status_lkp.meaning job_status FROM mtl_serial_numbers msn, mtl_parameters mp , wip_entities we, mtl_system_items_vl msi8, mtl_object_genealogy mog, wip_discrete_jobs wdj, (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 ml4.lookup_code, ML4.MEANING, ml4. language FROM lookup_details ml4 WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 AND ml4.lookup_code =4 ) accepted_lkp, (SELECT ml4.lookup_code, ML4.MEANING, ml4. language FROM lookup_details ml4 WHERE ml4.lookup_type = 'WIP_SERIAL_YIELD' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 AND ml4.lookup_code =3 ) rework_lkp , (SELECT ml4.lookup_code, ML4.LANGUAGE, ml4.meaning FROM lookup_details ml4 WHERE ml4.lookup_type = 'SERIAL_NUM_STATUS' AND ml4.view_application_id = 700 AND ml4.security_group_id = 0 ) inv_serial_status, (SELECT ml8.meaning, ml8.lookup_code, ML8.LANGUAGE FROM lookup_details ml8 WHERE ml8.lookup_type = 'WIP_INTRAOPERATION_STEP' AND ml8.view_application_id = 700 AND ml8.security_group_id = 0 ) wip_intraoperation_step, (SELECT ml6.meaning, ml6.lookup_code, ML6.LANGUAGE FROM lookup_details ml6 WHERE ml6.lookup_type = 'WIP_SERIAL_STATUS' AND ml6.view_application_id = 700 AND ml6.lookup_code = '3' AND ml6.security_group_id = 0 ) wip_serial_status WHERE msn.gen_object_id = mog.parent_object_id AND we.gen_object_id = mog.object_id AND mog.object_type = 5 AND mog.parent_object_type = 2 AND mog.end_date_active IS NULL AND we.entity_type IN (1,3) AND wip_intraoperation_step.lookup_code(+) = msn.intraoperation_step_type AND inv_serial_status.lookup_code(+) = msn.current_status AND status_lkp.language = nvl(inv_serial_status.language, status_lkp.language) AND status_lkp.language = nvl(wip_intraoperation_step.language, status_lkp.language) AND status_lkp.language = nvl(wip_serial_status.language, status_lkp.language) AND status_lkp.language = nvl(accepted_lkp.language, status_lkp.language) AND status_lkp.language = nvl(rework_lkp.language, status_lkp.language) AND we.primary_item_id = msn.inventory_item_id AND wdj.status_type = status_lkp.lookup_code (+) AND wdj.wip_entity_id = we.wip_entity_id AND wdj.organization_id = msi8.organization_id(+) AND wdj.primary_item_id = msi8.inventory_item_id(+) AND wdj.organization_id = we.organization_id AND wdj.organization_id = mp.organization_id AND wdj.status_type IN (1,3,4,5,6,7,12,14,15) AND wdj.serialization_start_op IS NOT NULL 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 )) )src WHERE SRC.LANGUAGE IN ('US') ) PIVOT ( MAX(WIP_SERIAL_STATUS) AS WIP_SERIAL_STATUS , MAX(INVENTORY_SERIAL_STATUS) AS INVENTORY_SERIAL_STATUS , MAX(YIELD) AS YIELD , MAX ( ASSEMBLY_SERIAL_OP_STEP ) AS ASSEMBLY_SERIAL_OP_STEP, MAX ( JOB_STATUS ) AS JOB_STATUS FOR LANGUAGE IN ('US' "US")) ) x where 2=2 |
