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
Run
ECC Genealogy and Trace, Work Orders and other Oracle EBS reports with Blitz Report™ on our demo environment
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 |