ECC Genealogy and Trace, Work Orders
Description
Categories: Enterprise Command Center
Imported from Enterprise Command Center
Dataset Key: gnt-wodetails
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetGntFilterAttributeValues
Dataset Key: gnt-wodetails
Query Procedure: WIP_ECC_GNT_PVT.GET_ECC_DATA_LOAD_INFO
Security Procedure: wip_ecc_datasecurity_pkg_pub.GetGntFilterAttributeValues
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 (SELECT wo.*, CASE WHEN nvl(wo.reject_qty,0) >0 THEN 'FALSE' ELSE 'TRUE' END reject_flag FROM ( SELECT /*+ leading(wo_txns) no_merge(wo_txns) use_nl(status_lkp) use_nl(nodetype_lkp) */ to_char(wo_txns.type||'-'||wo_txns.transaction_id||'-'||wo_txns.gen_object_id||'-'|| wo_txns.lot_number) ecc_spec_id, mp.organization_code, mp.organization_id, WIP_ECC_GNT_PVT.get_orgname(mp.organization_id) org_name, wo_txns.wip_entity_name, wo_txns.wip_entity_id, wo_txns.primary_item_id inventory_item_id, status_lkp.meaning job_status, nodetype_lkp.meaning node_type, msiv.concatenated_segments assembly, msiv.description, msiv.primary_uom_code, wo_txns.transaction_id, wo_txns.transaction_source_id, decode(wo_txns.type, 1, wo_txns.gen_object_id) lot_gen_obj_id, decode(wo_txns.type, 2, wo_txns.gen_object_id, 4, wo_txns.gen_object_id) ser_gen_obj_id, decode(wo_txns.type, 3, wo_txns.gen_object_id) res_wo_id, wo_txns.transaction_date, wo_txns.transaction_quantity, wo_txns.transaction_uom, wo_txns.revision, mtt.transaction_type_name, status_lkp.language language, decode(wo_txns.entity_type,3,1,wo_txns.entity_type) entity_type, /* action_lkp.meaning */ 'QA' WO_QA_RESULTS, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.start_quantity WHEN wo_txns.entity_type = 4 THEN wfs.planned_quantity ELSE wrs.daily_production_rate * wrs.processing_work_days END start_quantity, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.quantity_completed WHEN wo_txns.entity_type = 4 THEN wfs.quantity_completed ELSE wrs.quantity_completed END quantity_completed, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.scheduled_start_date WHEN wo_txns.entity_type = 4 THEN wfs.scheduled_start_date ELSE wrs.first_unit_start_date END scheduled_start_date, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.scheduled_completion_date WHEN wo_txns.entity_type = 4 THEN wfs.scheduled_completion_date ELSE wrs.last_unit_completion_date END scheduled_completion_date, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.bom_revision WHEN wo_txns.entity_type = 4 THEN wfs.bom_revision ELSE wrs.bom_revision END bom_revision, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.routing_revision WHEN wo_txns.entity_type = 4 THEN wfs.routing_revision ELSE wrs.routing_revision END routing_revision, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.bom_revision_date WHEN wo_txns.entity_type = 4 THEN wfs.bom_revision_date ELSE wrs.bom_revision_date END bom_revision_date, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.routing_revision_date WHEN wo_txns.entity_type = 4 THEN wfs.routing_revision_date ELSE wrs.routing_revision_date END routing_revision_date, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.alternate_bom_designator WHEN wo_txns.entity_type = 4 THEN wfs.alternate_bom_designator ELSE wrs.alternate_bom_designator END alternate_bom_designator, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.alternate_routing_designator WHEN wo_txns.entity_type = 4 THEN wfs.alternate_routing_designator ELSE wrs.alternate_routing_designator END alternate_routing_designator, (SELECT wl.line_code FROM wip_lines wl WHERE wl.line_id = CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.line_id WHEN wo_txns.entity_type = 4 THEN wfs.line_id ELSE wrs.line_id END ) line_code, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.status_type WHEN wo_txns.entity_type = 4 THEN decode(wfs.status, 2, 12, 3) ELSE wrs.status_type END status_type, CASE WHEN wo_txns.entity_type IN (1,3,5,8) AND wdj.status_type =6 THEN 'FALSE' WHEN wo_txns.entity_type = 4 THEN 'TRUE' WHEN wo_txns.entity_type = 2 AND wrs.status_type =6 THEN 'FALSE' ELSE 'TRUE' END hold_flag, CASE WHEN wo_txns.entity_type IN (1,3,5,8) AND wdj.quantity_scrapped >0 THEN 'FALSE' WHEN wo_txns.entity_type = 4 AND wfs.quantity_scrapped>0 THEN 'FALSE' WHEN wo_txns.entity_type = 2 THEN 'TRUE' ELSE 'TRUE' END scrap_flag, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN wdj.quantity_scrapped WHEN wo_txns.entity_type = 4 THEN wfs.quantity_scrapped WHEN wo_txns.entity_type = 2 THEN null ELSE null END scrap_qty, CASE WHEN wo_txns.entity_type IN (1,3,5,8) THEN (select sum(nvl(wo.quantity_rejected,0)) from wip_operations wo where wo.wip_entity_id =wdj.wip_entity_id and wo.organization_id = wdj.organization_id) WHEN wo_txns.entity_type = 4 THEN null WHEN wo_txns.entity_type = 2 THEN null ELSE null END reject_qty, wo_txns.inventory_item_id MMT_TXN_ITEM_ID, wo_txns.lot_number MMT_LOT_NUMBER, wo_txns.subinventory_code MMT_SUBINVENTORY_CODE, wo_txns.locator_id MMT_LOCATOR_ID, WIP_ECC_GNT_PVT.get_locator(wo_txns.organization_id, wo_txns.subinventory_code, wo_txns.locator_id) MMT_LOCATOR_SEGMENTS, wo_txns.transfer_subinventory MMT_TRANSFER_SUBINVENTORY, nvl(wo_txns.transfer_locator_id,- 999) MMT_TRANSFER_LOCATOR_ID, WIP_ECC_GNT_PVT.get_locator(wo_txns.organization_id, wo_txns.transfer_subinventory, wo_txns.transfer_locator_id) MMT_TRANSFER_LOCATOR_SEGMENTS, WIP_ECC_GNT_PVT.get_username(wo_txns.created_by) MMT_CREATED_BY, wo_txns.transaction_type_id, msiv2.concatenated_segments mmt_item FROM ( SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mmt.transaction_source_id, (select mln.gen_object_id from mtl_lot_numbers mln where mln.lot_number = mtln.lot_number and mln.inventory_item_id = mtln.inventory_item_id and mln.organization_id = mtln.organization_id) gen_object_id, 1 type, mtln.transaction_date transaction_date, abs(mtln.transaction_quantity) transaction_quantity, mtln.transaction_id, mmt.transaction_uom, mmt.transaction_action_id, mmt.transaction_type_id, mtln.serial_transaction_id, we.entity_type, we.organization_id, we.wip_entity_name, we.wip_entity_id, mtln.inventory_item_id, mmt.revision, we.primary_item_id, mtln.lot_number, mmt.subinventory_code, nvl(mmt.locator_id,-999) locator_id, mmt.transfer_subinventory, nvl(mmt.transfer_locator_id,- 999) transfer_locator_id, mmt.created_by FROM mtl_material_transactions mmt, mtl_transaction_lot_numbers mtln, wip_entities we WHERE mmt.transaction_id = mtln.transaction_id AND mmt.transaction_source_id = we.wip_entity_id AND mmt.organization_id = we.organization_id AND we.entity_type IN (1,2,3,4,5,8) AND mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id IN (30,31,32,40,41,42,43) AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mmt.transaction_source_id, (select msn.gen_object_id from mtl_serial_numbers msn where msn.serial_number = mut.serial_number and msn.inventory_item_id = mut.inventory_item_id) gen_object_id, 2, mut.transaction_date transaction_date, 1 transaction_quantity, mut.transaction_id, mmt.transaction_uom, mmt.transaction_action_id, mmt.transaction_type_id, NULL, we.entity_type, we.organization_id, we.wip_entity_name, we.wip_entity_id, mut.inventory_item_id, mmt.revision, we.primary_item_id, '####' lot_number, mmt.subinventory_code, nvl(mmt.locator_id,-999) locator_id, mmt.transfer_subinventory, nvl(mmt.transfer_locator_id,-999) transfer_locator_id, mmt.created_by FROM mtl_material_transactions mmt, mtl_unit_transactions mut, wip_entities we WHERE mmt.transaction_id = mut.transaction_id AND mmt.transaction_source_id = we.wip_entity_id AND mmt.organization_id = we.organization_id AND we.entity_type IN (1,2,3,4,5,8) AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id IN (30,31,32,40,41,42,43) AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(wmt) INDEX(wmt WIP_MOVE_TRANSACTIONS_N2) */ we.wip_entity_id, msn.gen_object_id, 4, wmt.transaction_date transaction_date, 1 transaction_quantity, wmt.transaction_id, wmt.primary_uom, decode(msn.intraoperation_step_type,5,30,-1), decode(msn.intraoperation_step_type,5,90,-1), NULL, we.entity_type, we.organization_id, we.wip_entity_name, we.wip_entity_id, msn.inventory_item_id, null, we.primary_item_id, '####' lot_number, NULL subinventory_code, -999 locator_id, NULL transfer_subinventory, -999 transfer_locator_id, NULL created_by FROM wip_move_transactions wmt, wip_serial_move_transactions wsmt, wip_entities we, mtl_serial_numbers msn WHERE wmt.transaction_id = wsmt.transaction_id AND wmt.wip_entity_id = we.wip_entity_id AND wmt.organization_id = we.organization_id AND we.entity_type IN (1,3,5,8) AND wmt.primary_item_id =msn.inventory_item_id AND msn.serial_number = wsmt.assembly_serial_number AND wmt.to_operation_seq_num = msn.operation_seq_num AND wmt.to_intraoperation_step_type = msn.intraoperation_step_type AND msn.intraoperation_step_type IN (4,5) AND wmt.organization_id = nvl('',wmt.organization_id) AND wmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND wmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mmt.transaction_source_id , NULL GEN_OBJECT_ID , 0 TYPE , mmt.TRANSACTION_DATE , mmt.TRANSACTION_QUANTITY , mmt.transaction_id , mmt.TRANSACTION_UOM , mmt.TRANSACTION_ACTION_ID , mmt.transaction_type_id , mmt.TRANSACTION_TYPE_ID , we.entity_type , we.organization_id , we.wip_entity_name , we.wip_entity_id , mmt.inventory_item_id , mmt.revision , we.primary_item_id , '####' , mmt.subinventory_code , nvl(mmt.locator_id,-999) locator_id , mmt.transfer_subinventory , nvl(mmt.transfer_locator_id,-999) transfer_locator_id , mmt.created_by FROM mtl_material_transactions mmt, wip_entities we, mtl_system_items msib WHERE mmt.transaction_source_id = we.wip_entity_id AND mmt.organization_id = we.organization_id AND mmt.organization_id = msib.organization_id AND mmt.inventory_item_id = msib.inventory_item_id AND msib.lot_control_code =1 AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id NOT IN (30,31,32,40,41,42,43) AND we.entity_type IN (1,2,3,4,5,8) AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(mmt) INDEX(mmt MTL_MATERIAL_TRANSACTIONS_N5) */ mmt.transaction_source_id , NULL GEN_OBJECT_ID , 0 TYPE , mmt.TRANSACTION_DATE , mmt.TRANSACTION_QUANTITY , mmt.transaction_id , mmt.TRANSACTION_UOM , mmt.TRANSACTION_ACTION_ID , mmt.transaction_type_id , mmt.TRANSACTION_TYPE_ID , we.entity_type , we.organization_id , we.wip_entity_name , we.wip_entity_id , mmt.inventory_item_id , mmt.revision , we.primary_item_id , mtln.lot_number , mmt.subinventory_code , nvl (mmt.locator_id,- 999) locator_id , mmt.transfer_subinventory , nvl (mmt.transfer_locator_id,- 999) transfer_locator_id , mmt.created_by FROM mtl_material_transactions mmt , wip_entities we , mtl_transaction_lot_numbers mtln WHERE mmt.transaction_source_id = we.wip_entity_id AND mmt.organization_id = we.organization_id AND mmt.transaction_id = mtln.transaction_id AND mmt.organization_id = nvl('',mmt.organization_id) AND mmt.transaction_source_type_id = 5 AND mmt.transaction_action_id NOT IN (30,31,32,40,41,42,43) AND we.entity_type IN (1,2,3,4,5,8) AND mmt.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND mmt.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') UNION ALL SELECT /*+ leading(txn) INDEX(txn WSM_SPLIT_MERGE_TXNS_ECC_N1) */ st.wip_entity_id, rs.wip_entity_id, 3, txn.transaction_date, decode(txn.transaction_type_id, 1, rs.start_quantity, st.available_quantity), txn.transaction_id, msi.primary_uom_code, decode(txn.transaction_type_id, 1, 40, 41), decode(txn.transaction_type_id, 1, 55, 56), NULL, we.entity_type, we.organization_id, we.wip_entity_name, we.wip_entity_id, st.primary_item_id, st.bom_revision, we.primary_item_id, '####' lot_number, NULL subinventory_code, - 999 locator_id, NULL transfer_subinventory, - 999 transfer_locator_id, NULL created_by FROM wsm_sm_starting_jobs st, wsm_sm_resulting_jobs rs, wsm_split_merge_transactions txn, mtl_system_items msi, wip_entities we WHERE txn.transaction_id = st.transaction_id AND txn.transaction_id = rs.transaction_id AND st.wip_entity_id = we.wip_entity_id AND txn.organization_id = we.organization_id AND we.entity_type IN (5,8) AND txn.transaction_type_id IN (1,2) AND txn.transaction_date >= TO_DATE('12-DEC-21 10:09:50','DD-MON-RR HH24:MI:SS') AND txn.transaction_date <= TO_DATE('12-DEC-22 10:09:50','DD-MON-RR HH24:MI:SS') AND rs.primary_item_id = msi.inventory_item_id AND txn.organization_id = msi.organization_id AND txn.organization_id = nvl('',txn.organization_id) ) wo_txns, (SELECT ml1.meaning,ml1.lookup_code,ml1.language FROM fnd_lookup_values ml1 WHERE ml1.lookup_type = 'WIP_JOB_STATUS' AND ml1.view_application_id = 700 AND ml1.security_group_id = 0 AND ml1.language in ('US') ) status_lkp, (select meaning , language , lookup_code FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ECC_NW_NODETYPE' AND language in ('US') ) nodetype_lkp , /* (select meaning , language FROM fnd_lookup_values WHERE lookup_type = 'WIP_GNT_ACTION_LINK_TYPE' AND lookup_code = 4 )action_lkp , */ wip_discrete_jobs wdj, wip_flow_schedules wfs, wip_repetitive_schedules wrs, mtl_system_items_vl msiv, mtl_system_items_vl msiv2, mtl_parameters mp, mtl_transaction_types mtt WHERE wo_txns.transaction_source_id = wdj.wip_entity_id (+) AND wo_txns.organization_id = wdj.organization_id (+) AND wo_txns.transaction_source_id = wfs.wip_entity_id (+) AND wo_txns.organization_id = wfs.organization_id (+) AND wo_txns.transaction_source_id = wrs.wip_entity_id (+) AND wo_txns.organization_id = wrs.organization_id (+) AND wo_txns.organization_id = msiv.organization_id (+) AND wo_txns.primary_item_id = msiv.inventory_item_id(+) AND wo_txns.organization_id = msiv2.organization_id AND wo_txns.inventory_item_id = msiv2.inventory_item_id AND wo_txns.organization_id = mp.organization_id AND wo_txns.transaction_type_id = mtt.transaction_type_id(+) AND status_lkp.lookup_code = to_char(decode(wo_txns.entity_type,2,wrs.status_type,4,decode(wfs.status,2,12,3),wdj.status_type)) AND nodetype_lkp.lookup_code = to_char(decode(wo_txns.entity_type,1,5,3,5,4,4,5,6,8,6,2,3,5)) AND nodetype_lkp.language = status_lkp.language /* AND action_lkp.language = status_lkp.language */ AND status_lkp.language IN ('US') ) wo) PIVOT ( MAX ( job_status ) AS job_status , MAX( node_type) AS node_type /* , MAX(WO_QA_RESULTS) AS WO_QA_RESULTS */ FOR language IN ('US' "US")) ) x where 2=2 |
