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
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