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
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 ( 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 ), wdj AS (SELECT /*+ materialize leading(wdj) */ wdj.*, we.wip_entity_name, we.entity_type FROM wip_discrete_jobs wdj , wip_entities we WHERE wdj.wip_entity_id = we.wip_entity_id AND we.entity_type IN ( 1, 3 ) AND wdj.status_type IN (1,3,4,5,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 )) ) SELECT /*+ leading(wdj msn) no_merge(wdj) */ TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id, wdj.wip_entity_id job, wdj.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 , to_char(msn.serial_number || ' (' || wip_ecc_util_pvt.get_concatenated_segments(wdj.organization_id, wdj.primary_item_id) || ', ' || mp.organization_code || ')') SERIAL_ITEM_ORG, 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 , msn.gen_object_id GEN_OBJECT_ID, 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 = wdj.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, wdj 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 = wdj.wip_entity_id AND wdj.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.primary_item_id = msn.inventory_item_id 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 , to_char(serial_number || ' (' || assembly || ', ' || organization_code || ')') SERIAL_ITEM_ORG, 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 , GEN_OBJECT_ID, 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(wdj mmt) no_merge(wdj) index(mmt MTL_MATERIAL_TRANSACTIONS_N2) */ TO_CHAR (wdj.wip_entity_id ||'-'||wdj.organization_id ||'-'||msn.serial_number||'-SER') ecc_spec_id, wdj.wip_entity_id job, wdj.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 , wdj.wip_entity_id , msn.gen_object_id 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 GEN_OBJECT_ID, wip_serial_status.meaning wip_serial_status , 3 wip_serial_status_flag , null 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 = wdj.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 , mtl_system_items_vl msi8, /*mtl_object_genealogy mog,*/ mtl_material_transactions mmt, mtl_unit_transactions mut, wdj 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 */ mut.transaction_id = decode (msi8.lot_control_code,1, mmt.transaction_id, ( SELECT mtln.serial_transaction_id from mtl_transaction_lot_numbers mtln where mtln.transaction_id = mmt.transaction_id) ) AND mmt.transaction_source_type_id = 5 AND mmt.organization_id = wdj.organization_id AND mmt.transaction_source_id = wdj.wip_entity_id AND mmt.transaction_action_id =31 AND mut.serial_number = msn.serial_number AND mut.inventory_item_id = msn.inventory_item_id AND wdj.wip_entity_id <> nvl(msn.wip_entity_id,-1) AND wdj.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 wdj.primary_item_id = msn.inventory_item_id AND wdj.status_type = status_lkp.lookup_code (+) AND wdj.organization_id = msi8.organization_id(+) AND wdj.primary_item_id = msi8.inventory_item_id(+) AND wdj.organization_id = mp.organization_id /* AND wdj.serialization_start_op IS NOT NULL */ )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 |
