VIEW: APPS.MTL_TRANSACTIONS_TEMP_ALL_V

Source

View Text - Preformatted

SELECT MMTT.ROWID ROW_ID , MMTT.TRANSACTION_HEADER_ID , MMTT.TRANSACTION_TEMP_ID , MMTT.SOURCE_CODE , MMTT.SOURCE_LINE_ID , MMTT.TRANSACTION_MODE , LKUP1.MEANING TRANSACTION_MODE_M , MMTT.LOCK_FLAG , DECODE(MMTT.LOCK_FLAG, 'N', 2, 1) DECODED_LOCK_FLAG , MMTT.LAST_UPDATE_DATE , MMTT.LAST_UPDATED_BY , MMTT.CREATION_DATE , MMTT.CREATED_BY , MMTT.LAST_UPDATE_LOGIN , MMTT.REQUEST_ID , MMTT.PROGRAM_APPLICATION_ID , MMTT.PROGRAM_ID , MMTT.PROGRAM_UPDATE_DATE , MMTT.INVENTORY_ITEM_ID , MSIK.DESCRIPTION ITEM_DESC , MSIK.SERIAL_NUMBER_CONTROL_CODE , MSIK.LOT_CONTROL_CODE , MSIK.PRIMARY_UOM_CODE , MMTT.REVISION , MMTT.ORGANIZATION_ID , MP1.ORGANIZATION_CODE , HRO1.NAME ORGANIZATION_NAME , MMTT.SUBINVENTORY_CODE , MMTT.LOCATOR_ID , MMTT.TRANSACTION_QUANTITY , MMTT.PRIMARY_QUANTITY , MMTT.TRANSACTION_UOM , MMTT.TRANSACTION_COST , MMTT.TRANSACTION_TYPE_ID , MTT.TRANSACTION_TYPE_NAME , MMTT.TRANSACTION_ACTION_ID , LKUP2.MEANING TRANSACTION_ACTION_NAME , MMTT.TRANSACTION_SOURCE_TYPE_ID , MTST.TRANSACTION_SOURCE_TYPE_NAME , MMTT.TRANSACTION_SOURCE_ID , MMTT.TRANSACTION_SOURCE_NAME , MMTT.TRANSACTION_DATE , MMTT.ACCT_PERIOD_ID , MMTT.DISTRIBUTION_ACCOUNT_ID , MMTT.TRANSACTION_REFERENCE , MMTT.REASON_ID , MTR.REASON_NAME , MMTT.LOT_NUMBER , MMTT.LOT_EXPIRATION_DATE , MMTT.SERIAL_NUMBER , MMTT.RECEIVING_DOCUMENT , MMTT.RCV_TRANSACTION_ID , MMTT.MOVE_TRANSACTION_ID , MMTT.COMPLETION_TRANSACTION_ID , MMTT.WIP_ENTITY_TYPE , MMTT.SCHEDULE_ID , WL.LINE_CODE , MMTT.EMPLOYEE_CODE , MMTT.SCHEDULE_UPDATE_CODE , MMTT.SETUP_TEARDOWN_CODE , MMTT.OPERATION_SEQ_NUM , MMTT.PICKING_LINE_ID , MMTT.TRX_SOURCE_LINE_ID , MMTT.TRX_SOURCE_DELIVERY_ID , MMTT.PHYSICAL_ADJUSTMENT_ID , MMTT.CYCLE_COUNT_ID , MMTT.RMA_LINE_ID , MMTT.CUSTOMER_SHIP_ID , MMTT.CURRENCY_CODE , MMTT.CURRENCY_CONVERSION_RATE , MMTT.CURRENCY_CONVERSION_TYPE , MMTT.CURRENCY_CONVERSION_DATE , MMTT.USSGL_TRANSACTION_CODE , MMTT.VENDOR_LOT_NUMBER , MMTT.ENCUMBRANCE_ACCOUNT , MMTT.ENCUMBRANCE_AMOUNT , HLT.LOCATION_CODE , MMTT.SHIPMENT_NUMBER , MMTT.TRANSFER_COST , MMTT.TRANSPORTATION_COST , MMTT.TRANSPORTATION_ACCOUNT , MMTT.FREIGHT_CODE , MMTT.CONTAINERS , MMTT.WAYBILL_AIRBILL , MMTT.EXPECTED_ARRIVAL_DATE , MMTT.TRANSFER_SUBINVENTORY , MMTT.TRANSFER_ORGANIZATION , MP2.ORGANIZATION_CODE TRANSFER_ORG_CODE , HROT2.NAME TRANSFER_ORG_NAME , MMTT.TRANSFER_TO_LOCATION , MMTT.NEW_AVERAGE_COST , MMTT.VALUE_CHANGE , MMTT.PERCENTAGE_CHANGE , MMTT.MATERIAL_ALLOCATION_TEMP_ID , MMTT.DEMAND_SOURCE_HEADER_ID , MMTT.DEMAND_SOURCE_LINE , MMTT.DEMAND_SOURCE_DELIVERY , MMTT.WIP_SUPPLY_TYPE , MMTT.POSTING_FLAG , DECODE(MMTT.POSTING_FLAG, 'N', 2, 1) DECODED_POSTING_FLAG , MMTT.PROCESS_FLAG , DECODE(MMTT.PROCESS_FLAG, 'N', 2, 'E', 3, 1) DECODED_PROCESS_FLAG , MMTT.ERROR_CODE , MMTT.PRIMARY_SWITCH , BD.DEPARTMENT_CODE , MMTT.ERROR_EXPLANATION , MMTT.DEMAND_ID , MMTT.REQUISITION_LINE_ID , MMTT.REQUISITION_DISTRIBUTION_ID , MMTT.MOVEMENT_ID , MMTT.ATTRIBUTE_CATEGORY , MMTT.ATTRIBUTE1 , MMTT.ATTRIBUTE2 , MMTT.ATTRIBUTE3 , MMTT.ATTRIBUTE4 , MMTT.ATTRIBUTE5 , MMTT.ATTRIBUTE6 , MMTT.ATTRIBUTE7 , MMTT.ATTRIBUTE8 , MMTT.ATTRIBUTE9 , MMTT.ATTRIBUTE10 , MMTT.ATTRIBUTE11 , MMTT.ATTRIBUTE12 , MMTT.ATTRIBUTE13 , MMTT.ATTRIBUTE14 , MMTT.ATTRIBUTE15 , MMTT.SOURCE_PROJECT_ID , MMTT.SOURCE_TASK_ID , MMTT.PROJECT_ID , MMTT.TO_PROJECT_ID , MMTT.TASK_ID , MMTT.TO_TASK_ID , MMTT.EXPENDITURE_TYPE , MMTT.PA_EXPENDITURE_ORG_ID , MMTT.COST_GROUP_ID , MMTT.OVERCOMPLETION_TRANSACTION_QTY , DECODE(NVL(MMTT.TRANSACTION_STATUS, 1), 2, 2, 1) TRANSACTION_STATUS , LKUP3.MEANING TRANSACTION_STATUS_M , MMTT.TRANSFER_COST_GROUP_ID , MMTT.LPN_ID , MMTT.TRANSFER_LPN_ID , LPN1.LICENSE_PLATE_NUMBER LPN , LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN , CG.COST_GROUP COST_GROUP_NAME , CG2.COST_GROUP TRANSFER_COST_GROUP_NAME , MMTT.PUT_AWAY_STRATEGY_ID , MMTT.PUT_AWAY_RULE_ID , MMTT.PICK_STRATEGY_ID , MMTT.PICK_RULE_ID , MMTT.MOVE_ORDER_LINE_ID , MMTT.CONTENT_LPN_ID , LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN , LPN4.LICENSE_PLATE_NUMBER CARTONIZED_LPN , MMTT.OWNING_ORGANIZATION_ID , MMTT.OWNING_TP_TYPE , MMTT.PLANNING_ORGANIZATION_ID , MMTT.PLANNING_TP_TYPE , MMTT.SECONDARY_TRANSACTION_QUANTITY, MSIK.SECONDARY_UOM_CODE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT, MTL_SYSTEM_ITEMS_VL MSIK, MFG_LOOKUPS LKUP1, MFG_LOOKUPS LKUP2, MFG_LOOKUPS LKUP3, MTL_TRANSACTION_TYPES MTT, MTL_TXN_SOURCE_TYPES MTST, MTL_PARAMETERS MP1, MTL_PARAMETERS MP2, HR_ORGANIZATION_UNITS HRO1, HR_ORG_UNITS_NO_JOIN HROC2, HR_ALL_ORGANIZATION_UNITS_TL HROT2, MTL_TRANSACTION_REASONS MTR, WIP_LINES WL, BOM_DEPARTMENTS BD, HR_LOCATIONS_ALL_TL HLT, HR_LOCATIONS_NO_JOIN HLC, CST_COST_GROUPS CG, CST_COST_GROUPS CG2, WMS_LICENSE_PLATE_NUMBERS LPN1, WMS_LICENSE_PLATE_NUMBERS LPN2, WMS_LICENSE_PLATE_NUMBERS LPN3, WMS_LICENSE_PLATE_NUMBERS LPN4 WHERE MMTT.ORGANIZATION_ID = MP1.ORGANIZATION_ID AND MMTT.ORGANIZATION_ID = MSIK.ORGANIZATION_ID AND MMTT.INVENTORY_ITEM_ID + 0 = MSIK.INVENTORY_ITEM_ID AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID AND MMTT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID AND NVL(TRANSACTION_MODE,1) = LKUP1.LOOKUP_CODE AND LKUP1.LOOKUP_TYPE = 'MTL_TRANSACTION_MODE' AND MMTT.TRANSACTION_ACTION_ID + 0 = LKUP2.LOOKUP_CODE AND LKUP2.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION' AND LKUP3.LOOKUP_TYPE = 'MTL_INV_TRANSACTION_STATUS' AND DECODE(NVL(MMTT.TRANSACTION_STATUS,1),2,2,1) = LKUP3.LOOKUP_CODE AND MMTT.REASON_ID = MTR.REASON_ID (+) AND MMTT.REPETITIVE_LINE_ID = WL.LINE_ID (+) AND MMTT.ORGANIZATION_ID = WL.ORGANIZATION_ID (+) AND MMTT.DEPARTMENT_ID = BD.DEPARTMENT_ID (+) AND MMTT.SHIP_TO_LOCATION = HLC.LOCATION_ID (+) AND HLC.LOCATION_ID = HLT.LOCATION_ID (+) AND DECODE(HLT.LOCATION_ID, NULL, '1', HLT.LANGUAGE) = DECODE(HLT.LOCATION_ID, NULL, '1', USERENV('LANG')) AND MMTT.ORGANIZATION_ID = HRO1.ORGANIZATION_ID AND MMTT.TRANSFER_ORGANIZATION = MP2.ORGANIZATION_ID (+) AND MMTT.TRANSFER_ORGANIZATION = HROC2.ORGANIZATION_ID (+) AND HROC2.ORGANIZATION_ID = HROT2.ORGANIZATION_ID (+) AND DECODE(HROT2.ORGANIZATION_ID, NULL, '1', HROT2.LANGUAGE) = DECODE(HROT2.ORGANIZATION_ID, NULL, '1', USERENV('LANG')) AND MMTT.COST_GROUP_ID = CG.COST_GROUP_ID(+) AND MMTT.TRANSFER_COST_GROUP_ID = CG2.COST_GROUP_ID(+) AND MMTT.LPN_ID = LPN1.LPN_ID(+) AND MMTT.TRANSFER_LPN_ID = LPN2.LPN_ID(+) AND MMTT.CONTENT_LPN_ID = LPN3.LPN_ID(+) AND MMTT.CARTONIZATION_ID = LPN4.LPN_ID(+) UNION ALL SELECT MMTT.ROWID ROW_ID , MMTT.TRANSACTION_HEADER_ID , MMTT.TRANSACTION_TEMP_ID , MMTT.SOURCE_CODE , MMTT.SOURCE_LINE_ID , MMTT.TRANSACTION_MODE , LKUP1.MEANING TRANSACTION_MODE_M , MMTT.LOCK_FLAG , DECODE(MMTT.LOCK_FLAG , 'N' , 2 , 1) DECODED_LOCK_FLAG , MMTT.LAST_UPDATE_DATE , MMTT.LAST_UPDATED_BY , MMTT.CREATION_DATE , MMTT.CREATED_BY , MMTT.LAST_UPDATE_LOGIN , MMTT.REQUEST_ID , MMTT.PROGRAM_APPLICATION_ID , MMTT.PROGRAM_ID , MMTT.PROGRAM_UPDATE_DATE , MMTT.INVENTORY_ITEM_ID , NULL , NULL , NULL , MMTT.ITEM_PRIMARY_UOM_CODE , MMTT.REVISION , MMTT.ORGANIZATION_ID , MP1.ORGANIZATION_CODE , HRO1.NAME ORGANIZATION_NAME , MMTT.SUBINVENTORY_CODE , MMTT.LOCATOR_ID , MMTT.TRANSACTION_QUANTITY , MMTT.PRIMARY_QUANTITY , MMTT.TRANSACTION_UOM , MMTT.TRANSACTION_COST , MMTT.TRANSACTION_TYPE_ID , MTT.TRANSACTION_TYPE_NAME , MMTT.TRANSACTION_ACTION_ID , LKUP2.MEANING TRANSACTION_ACTION_NAME , MMTT.TRANSACTION_SOURCE_TYPE_ID , MTST.TRANSACTION_SOURCE_TYPE_NAME , MMTT.TRANSACTION_SOURCE_ID , MMTT.TRANSACTION_SOURCE_NAME , MMTT.TRANSACTION_DATE , MMTT.ACCT_PERIOD_ID , MMTT.DISTRIBUTION_ACCOUNT_ID , MMTT.TRANSACTION_REFERENCE , MMTT.REASON_ID , MTR.REASON_NAME , MMTT.LOT_NUMBER , MMTT.LOT_EXPIRATION_DATE , MMTT.SERIAL_NUMBER , MMTT.RECEIVING_DOCUMENT , MMTT.RCV_TRANSACTION_ID , MMTT.MOVE_TRANSACTION_ID , MMTT.COMPLETION_TRANSACTION_ID , MMTT.WIP_ENTITY_TYPE , MMTT.SCHEDULE_ID , WL.LINE_CODE , MMTT.EMPLOYEE_CODE , MMTT.SCHEDULE_UPDATE_CODE , MMTT.SETUP_TEARDOWN_CODE , MMTT.OPERATION_SEQ_NUM , MMTT.PICKING_LINE_ID , MMTT.TRX_SOURCE_LINE_ID , MMTT.TRX_SOURCE_DELIVERY_ID , MMTT.PHYSICAL_ADJUSTMENT_ID , MMTT.CYCLE_COUNT_ID , MMTT.RMA_LINE_ID , MMTT.CUSTOMER_SHIP_ID , MMTT.CURRENCY_CODE , MMTT.CURRENCY_CONVERSION_RATE , MMTT.CURRENCY_CONVERSION_TYPE , MMTT.CURRENCY_CONVERSION_DATE , MMTT.USSGL_TRANSACTION_CODE , MMTT.VENDOR_LOT_NUMBER , MMTT.ENCUMBRANCE_ACCOUNT , MMTT.ENCUMBRANCE_AMOUNT , HLT.LOCATION_CODE , MMTT.SHIPMENT_NUMBER , MMTT.TRANSFER_COST , MMTT.TRANSPORTATION_COST , MMTT.TRANSPORTATION_ACCOUNT , MMTT.FREIGHT_CODE , MMTT.CONTAINERS , MMTT.WAYBILL_AIRBILL , MMTT.EXPECTED_ARRIVAL_DATE , MMTT.TRANSFER_SUBINVENTORY , MMTT.TRANSFER_ORGANIZATION , MP2.ORGANIZATION_CODE TRANSFER_ORG_CODE , HROT2.NAME TRANSFER_ORG_NAME , MMTT.TRANSFER_TO_LOCATION , MMTT.NEW_AVERAGE_COST , MMTT.VALUE_CHANGE , MMTT.PERCENTAGE_CHANGE , MMTT.MATERIAL_ALLOCATION_TEMP_ID , MMTT.DEMAND_SOURCE_HEADER_ID , MMTT.DEMAND_SOURCE_LINE , MMTT.DEMAND_SOURCE_DELIVERY , MMTT.WIP_SUPPLY_TYPE , MMTT.POSTING_FLAG , DECODE(MMTT.POSTING_FLAG , 'N' , 2 , 1) DECODED_POSTING_FLAG , MMTT.PROCESS_FLAG , DECODE(MMTT.PROCESS_FLAG , 'N' , 2 , 'E' , 3 , 1) DECODED_PROCESS_FLAG , MMTT.ERROR_CODE , MMTT.PRIMARY_SWITCH , BD.DEPARTMENT_CODE , MMTT.ERROR_EXPLANATION , MMTT.DEMAND_ID , MMTT.REQUISITION_LINE_ID , MMTT.REQUISITION_DISTRIBUTION_ID , MMTT.MOVEMENT_ID , MMTT.ATTRIBUTE_CATEGORY , MMTT.ATTRIBUTE1 , MMTT.ATTRIBUTE2 , MMTT.ATTRIBUTE3 , MMTT.ATTRIBUTE4 , MMTT.ATTRIBUTE5 , MMTT.ATTRIBUTE6 , MMTT.ATTRIBUTE7 , MMTT.ATTRIBUTE8 , MMTT.ATTRIBUTE9 , MMTT.ATTRIBUTE10 , MMTT.ATTRIBUTE11 , MMTT.ATTRIBUTE12 , MMTT.ATTRIBUTE13 , MMTT.ATTRIBUTE14 , MMTT.ATTRIBUTE15 , MMTT.SOURCE_PROJECT_ID , MMTT.SOURCE_TASK_ID , MMTT.PROJECT_ID , MMTT.TO_PROJECT_ID , MMTT.TASK_ID , MMTT.TO_TASK_ID , MMTT.EXPENDITURE_TYPE , MMTT.PA_EXPENDITURE_ORG_ID , MMTT.COST_GROUP_ID , MMTT.OVERCOMPLETION_TRANSACTION_QTY , DECODE(NVL(MMTT.TRANSACTION_STATUS , 1) , 2 , 2 , 1) TRANSACTION_STATUS , LKUP3.MEANING TRANSACTION_STATUS_M , MMTT.TRANSFER_COST_GROUP_ID , MMTT.LPN_ID , MMTT.TRANSFER_LPN_ID , LPN1.LICENSE_PLATE_NUMBER LPN , LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN , CG.COST_GROUP COST_GROUP_NAME , CG2.COST_GROUP TRANSFER_COST_GROUP_NAME , MMTT.PUT_AWAY_STRATEGY_ID , MMTT.PUT_AWAY_RULE_ID , MMTT.PICK_STRATEGY_ID , MMTT.PICK_RULE_ID , MMTT.MOVE_ORDER_LINE_ID , MMTT.CONTENT_LPN_ID , LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN , LPN4.LICENSE_PLATE_NUMBER CARTONIZED_LPN , MMTT.OWNING_ORGANIZATION_ID , MMTT.OWNING_TP_TYPE , MMTT.PLANNING_ORGANIZATION_ID , MMTT.PLANNING_TP_TYPE , MMTT.SECONDARY_TRANSACTION_QUANTITY , MMTT.SECONDARY_UOM_CODE FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT , MFG_LOOKUPS LKUP1 , MFG_LOOKUPS LKUP2 , MFG_LOOKUPS LKUP3 , MTL_TRANSACTION_TYPES MTT , MTL_TXN_SOURCE_TYPES MTST , MTL_PARAMETERS MP1 , MTL_PARAMETERS MP2 , HR_ORGANIZATION_UNITS HRO1 , HR_ORG_UNITS_NO_JOIN HROC2 , HR_ALL_ORGANIZATION_UNITS_TL HROT2 , MTL_TRANSACTION_REASONS MTR , WIP_LINES WL , BOM_DEPARTMENTS BD , HR_LOCATIONS_ALL_TL HLT , HR_LOCATIONS_NO_JOIN HLC , CST_COST_GROUPS CG , CST_COST_GROUPS CG2 , WMS_LICENSE_PLATE_NUMBERS LPN1 , WMS_LICENSE_PLATE_NUMBERS LPN2 , WMS_LICENSE_PLATE_NUMBERS LPN3 , WMS_LICENSE_PLATE_NUMBERS LPN4 WHERE MMTT .ORGANIZATION_ID = MP1.ORGANIZATION_ID AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID AND MMTT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID AND NVL(TRANSACTION_MODE , 1) = LKUP1.LOOKUP_CODE AND LKUP1.LOOKUP_TYPE = 'MTL_TRANSACTION_MODE' AND MMTT.TRANSACTION_ACTION_ID + 0 = LKUP2.LOOKUP_CODE AND LKUP2.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION' AND LKUP3.LOOKUP_TYPE = 'MTL_INV_TRANSACTION_STATUS' AND DECODE(NVL(MMTT.TRANSACTION_STATUS , 1) , 2 , 2 , 1) = LKUP3.LOOKUP_CODE AND MMTT.REASON_ID = MTR.REASON_ID (+) AND MMTT.REPETITIVE_LINE_ID = WL.LINE_ID (+) AND MMTT.ORGANIZATION_ID = WL.ORGANIZATION_ID (+) AND MMTT.DEPARTMENT_ID = BD.DEPARTMENT_ID (+) AND MMTT.SHIP_TO_LOCATION = HLC.LOCATION_ID (+) AND HLC.LOCATION_ID = HLT.LOCATION_ID (+) AND DECODE(HLT.LOCATION_ID , NULL , '1' , HLT.LANGUAGE) = DECODE(HLT.LOCATION_ID , NULL , '1' , USERENV('LANG')) AND MMTT.ORGANIZATION_ID = HRO1.ORGANIZATION_ID AND MMTT.TRANSFER_ORGANIZATION = MP2.ORGANIZATION_ID (+) AND MMTT.TRANSFER_ORGANIZATION = HROC2.ORGANIZATION_ID (+) AND HROC2.ORGANIZATION_ID = HROT2.ORGANIZATION_ID (+) AND DECODE(HROT2.ORGANIZATION_ID , NULL , '1' , HROT2.LANGUAGE) = DECODE(HROT2.ORGANIZATION_ID , NULL , '1' , USERENV('LANG')) AND MMTT.COST_GROUP_ID = CG.COST_GROUP_ID(+) AND MMTT.TRANSFER_COST_GROUP_ID = CG2.COST_GROUP_ID(+) AND MMTT.LPN_ID = LPN1.LPN_ID(+) AND MMTT.TRANSFER_LPN_ID = LPN2.LPN_ID(+) AND MMTT.CONTENT_LPN_ID = LPN3.LPN_ID(+) AND MMTT.CARTONIZATION_ID = LPN4.LPN_ID(+) AND MP1.wms_enabled_flag = 'Y' AND MMTT.INVENTORY_ITEM_ID = -1 AND MMTT.CONTENT_LPN_ID IS NOT NULL

View Text - HTML Formatted

SELECT MMTT.ROWID ROW_ID
, MMTT.TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_MODE
, LKUP1.MEANING TRANSACTION_MODE_M
, MMTT.LOCK_FLAG
, DECODE(MMTT.LOCK_FLAG
, 'N'
, 2
, 1) DECODED_LOCK_FLAG
, MMTT.LAST_UPDATE_DATE
, MMTT.LAST_UPDATED_BY
, MMTT.CREATION_DATE
, MMTT.CREATED_BY
, MMTT.LAST_UPDATE_LOGIN
, MMTT.REQUEST_ID
, MMTT.PROGRAM_APPLICATION_ID
, MMTT.PROGRAM_ID
, MMTT.PROGRAM_UPDATE_DATE
, MMTT.INVENTORY_ITEM_ID
, MSIK.DESCRIPTION ITEM_DESC
, MSIK.SERIAL_NUMBER_CONTROL_CODE
, MSIK.LOT_CONTROL_CODE
, MSIK.PRIMARY_UOM_CODE
, MMTT.REVISION
, MMTT.ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HRO1.NAME ORGANIZATION_NAME
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MMTT.TRANSACTION_QUANTITY
, MMTT.PRIMARY_QUANTITY
, MMTT.TRANSACTION_UOM
, MMTT.TRANSACTION_COST
, MMTT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, MMTT.TRANSACTION_ACTION_ID
, LKUP2.MEANING TRANSACTION_ACTION_NAME
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSACTION_DATE
, MMTT.ACCT_PERIOD_ID
, MMTT.DISTRIBUTION_ACCOUNT_ID
, MMTT.TRANSACTION_REFERENCE
, MMTT.REASON_ID
, MTR.REASON_NAME
, MMTT.LOT_NUMBER
, MMTT.LOT_EXPIRATION_DATE
, MMTT.SERIAL_NUMBER
, MMTT.RECEIVING_DOCUMENT
, MMTT.RCV_TRANSACTION_ID
, MMTT.MOVE_TRANSACTION_ID
, MMTT.COMPLETION_TRANSACTION_ID
, MMTT.WIP_ENTITY_TYPE
, MMTT.SCHEDULE_ID
, WL.LINE_CODE
, MMTT.EMPLOYEE_CODE
, MMTT.SCHEDULE_UPDATE_CODE
, MMTT.SETUP_TEARDOWN_CODE
, MMTT.OPERATION_SEQ_NUM
, MMTT.PICKING_LINE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRX_SOURCE_DELIVERY_ID
, MMTT.PHYSICAL_ADJUSTMENT_ID
, MMTT.CYCLE_COUNT_ID
, MMTT.RMA_LINE_ID
, MMTT.CUSTOMER_SHIP_ID
, MMTT.CURRENCY_CODE
, MMTT.CURRENCY_CONVERSION_RATE
, MMTT.CURRENCY_CONVERSION_TYPE
, MMTT.CURRENCY_CONVERSION_DATE
, MMTT.USSGL_TRANSACTION_CODE
, MMTT.VENDOR_LOT_NUMBER
, MMTT.ENCUMBRANCE_ACCOUNT
, MMTT.ENCUMBRANCE_AMOUNT
, HLT.LOCATION_CODE
, MMTT.SHIPMENT_NUMBER
, MMTT.TRANSFER_COST
, MMTT.TRANSPORTATION_COST
, MMTT.TRANSPORTATION_ACCOUNT
, MMTT.FREIGHT_CODE
, MMTT.CONTAINERS
, MMTT.WAYBILL_AIRBILL
, MMTT.EXPECTED_ARRIVAL_DATE
, MMTT.TRANSFER_SUBINVENTORY
, MMTT.TRANSFER_ORGANIZATION
, MP2.ORGANIZATION_CODE TRANSFER_ORG_CODE
, HROT2.NAME TRANSFER_ORG_NAME
, MMTT.TRANSFER_TO_LOCATION
, MMTT.NEW_AVERAGE_COST
, MMTT.VALUE_CHANGE
, MMTT.PERCENTAGE_CHANGE
, MMTT.MATERIAL_ALLOCATION_TEMP_ID
, MMTT.DEMAND_SOURCE_HEADER_ID
, MMTT.DEMAND_SOURCE_LINE
, MMTT.DEMAND_SOURCE_DELIVERY
, MMTT.WIP_SUPPLY_TYPE
, MMTT.POSTING_FLAG
, DECODE(MMTT.POSTING_FLAG
, 'N'
, 2
, 1) DECODED_POSTING_FLAG
, MMTT.PROCESS_FLAG
, DECODE(MMTT.PROCESS_FLAG
, 'N'
, 2
, 'E'
, 3
, 1) DECODED_PROCESS_FLAG
, MMTT.ERROR_CODE
, MMTT.PRIMARY_SWITCH
, BD.DEPARTMENT_CODE
, MMTT.ERROR_EXPLANATION
, MMTT.DEMAND_ID
, MMTT.REQUISITION_LINE_ID
, MMTT.REQUISITION_DISTRIBUTION_ID
, MMTT.MOVEMENT_ID
, MMTT.ATTRIBUTE_CATEGORY
, MMTT.ATTRIBUTE1
, MMTT.ATTRIBUTE2
, MMTT.ATTRIBUTE3
, MMTT.ATTRIBUTE4
, MMTT.ATTRIBUTE5
, MMTT.ATTRIBUTE6
, MMTT.ATTRIBUTE7
, MMTT.ATTRIBUTE8
, MMTT.ATTRIBUTE9
, MMTT.ATTRIBUTE10
, MMTT.ATTRIBUTE11
, MMTT.ATTRIBUTE12
, MMTT.ATTRIBUTE13
, MMTT.ATTRIBUTE14
, MMTT.ATTRIBUTE15
, MMTT.SOURCE_PROJECT_ID
, MMTT.SOURCE_TASK_ID
, MMTT.PROJECT_ID
, MMTT.TO_PROJECT_ID
, MMTT.TASK_ID
, MMTT.TO_TASK_ID
, MMTT.EXPENDITURE_TYPE
, MMTT.PA_EXPENDITURE_ORG_ID
, MMTT.COST_GROUP_ID
, MMTT.OVERCOMPLETION_TRANSACTION_QTY
, DECODE(NVL(MMTT.TRANSACTION_STATUS
, 1)
, 2
, 2
, 1) TRANSACTION_STATUS
, LKUP3.MEANING TRANSACTION_STATUS_M
, MMTT.TRANSFER_COST_GROUP_ID
, MMTT.LPN_ID
, MMTT.TRANSFER_LPN_ID
, LPN1.LICENSE_PLATE_NUMBER LPN
, LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, CG.COST_GROUP COST_GROUP_NAME
, CG2.COST_GROUP TRANSFER_COST_GROUP_NAME
, MMTT.PUT_AWAY_STRATEGY_ID
, MMTT.PUT_AWAY_RULE_ID
, MMTT.PICK_STRATEGY_ID
, MMTT.PICK_RULE_ID
, MMTT.MOVE_ORDER_LINE_ID
, MMTT.CONTENT_LPN_ID
, LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN
, LPN4.LICENSE_PLATE_NUMBER CARTONIZED_LPN
, MMTT.OWNING_ORGANIZATION_ID
, MMTT.OWNING_TP_TYPE
, MMTT.PLANNING_ORGANIZATION_ID
, MMTT.PLANNING_TP_TYPE
, MMTT.SECONDARY_TRANSACTION_QUANTITY
, MSIK.SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_SYSTEM_ITEMS_VL MSIK
, MFG_LOOKUPS LKUP1
, MFG_LOOKUPS LKUP2
, MFG_LOOKUPS LKUP3
, MTL_TRANSACTION_TYPES MTT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, HR_ORGANIZATION_UNITS HRO1
, HR_ORG_UNITS_NO_JOIN HROC2
, HR_ALL_ORGANIZATION_UNITS_TL HROT2
, MTL_TRANSACTION_REASONS MTR
, WIP_LINES WL
, BOM_DEPARTMENTS BD
, HR_LOCATIONS_ALL_TL HLT
, HR_LOCATIONS_NO_JOIN HLC
, CST_COST_GROUPS CG
, CST_COST_GROUPS CG2
, WMS_LICENSE_PLATE_NUMBERS LPN1
, WMS_LICENSE_PLATE_NUMBERS LPN2
, WMS_LICENSE_PLATE_NUMBERS LPN3
, WMS_LICENSE_PLATE_NUMBERS LPN4
WHERE MMTT.ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND MMTT.ORGANIZATION_ID = MSIK.ORGANIZATION_ID
AND MMTT.INVENTORY_ITEM_ID + 0 = MSIK.INVENTORY_ITEM_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND NVL(TRANSACTION_MODE
, 1) = LKUP1.LOOKUP_CODE
AND LKUP1.LOOKUP_TYPE = 'MTL_TRANSACTION_MODE'
AND MMTT.TRANSACTION_ACTION_ID + 0 = LKUP2.LOOKUP_CODE
AND LKUP2.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND LKUP3.LOOKUP_TYPE = 'MTL_INV_TRANSACTION_STATUS'
AND DECODE(NVL(MMTT.TRANSACTION_STATUS
, 1)
, 2
, 2
, 1) = LKUP3.LOOKUP_CODE
AND MMTT.REASON_ID = MTR.REASON_ID (+)
AND MMTT.REPETITIVE_LINE_ID = WL.LINE_ID (+)
AND MMTT.ORGANIZATION_ID = WL.ORGANIZATION_ID (+)
AND MMTT.DEPARTMENT_ID = BD.DEPARTMENT_ID (+)
AND MMTT.SHIP_TO_LOCATION = HLC.LOCATION_ID (+)
AND HLC.LOCATION_ID = HLT.LOCATION_ID (+)
AND DECODE(HLT.LOCATION_ID
, NULL
, '1'
, HLT.LANGUAGE) = DECODE(HLT.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND MMTT.ORGANIZATION_ID = HRO1.ORGANIZATION_ID
AND MMTT.TRANSFER_ORGANIZATION = MP2.ORGANIZATION_ID (+)
AND MMTT.TRANSFER_ORGANIZATION = HROC2.ORGANIZATION_ID (+)
AND HROC2.ORGANIZATION_ID = HROT2.ORGANIZATION_ID (+)
AND DECODE(HROT2.ORGANIZATION_ID
, NULL
, '1'
, HROT2.LANGUAGE) = DECODE(HROT2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND MMTT.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND MMTT.TRANSFER_COST_GROUP_ID = CG2.COST_GROUP_ID(+)
AND MMTT.LPN_ID = LPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = LPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = LPN3.LPN_ID(+)
AND MMTT.CARTONIZATION_ID = LPN4.LPN_ID(+) UNION ALL SELECT MMTT.ROWID ROW_ID
, MMTT.TRANSACTION_HEADER_ID
, MMTT.TRANSACTION_TEMP_ID
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_MODE
, LKUP1.MEANING TRANSACTION_MODE_M
, MMTT.LOCK_FLAG
, DECODE(MMTT.LOCK_FLAG
, 'N'
, 2
, 1) DECODED_LOCK_FLAG
, MMTT.LAST_UPDATE_DATE
, MMTT.LAST_UPDATED_BY
, MMTT.CREATION_DATE
, MMTT.CREATED_BY
, MMTT.LAST_UPDATE_LOGIN
, MMTT.REQUEST_ID
, MMTT.PROGRAM_APPLICATION_ID
, MMTT.PROGRAM_ID
, MMTT.PROGRAM_UPDATE_DATE
, MMTT.INVENTORY_ITEM_ID
, NULL
, NULL
, NULL
, MMTT.ITEM_PRIMARY_UOM_CODE
, MMTT.REVISION
, MMTT.ORGANIZATION_ID
, MP1.ORGANIZATION_CODE
, HRO1.NAME ORGANIZATION_NAME
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MMTT.TRANSACTION_QUANTITY
, MMTT.PRIMARY_QUANTITY
, MMTT.TRANSACTION_UOM
, MMTT.TRANSACTION_COST
, MMTT.TRANSACTION_TYPE_ID
, MTT.TRANSACTION_TYPE_NAME
, MMTT.TRANSACTION_ACTION_ID
, LKUP2.MEANING TRANSACTION_ACTION_NAME
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRANSACTION_SOURCE_NAME
, MMTT.TRANSACTION_DATE
, MMTT.ACCT_PERIOD_ID
, MMTT.DISTRIBUTION_ACCOUNT_ID
, MMTT.TRANSACTION_REFERENCE
, MMTT.REASON_ID
, MTR.REASON_NAME
, MMTT.LOT_NUMBER
, MMTT.LOT_EXPIRATION_DATE
, MMTT.SERIAL_NUMBER
, MMTT.RECEIVING_DOCUMENT
, MMTT.RCV_TRANSACTION_ID
, MMTT.MOVE_TRANSACTION_ID
, MMTT.COMPLETION_TRANSACTION_ID
, MMTT.WIP_ENTITY_TYPE
, MMTT.SCHEDULE_ID
, WL.LINE_CODE
, MMTT.EMPLOYEE_CODE
, MMTT.SCHEDULE_UPDATE_CODE
, MMTT.SETUP_TEARDOWN_CODE
, MMTT.OPERATION_SEQ_NUM
, MMTT.PICKING_LINE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRX_SOURCE_DELIVERY_ID
, MMTT.PHYSICAL_ADJUSTMENT_ID
, MMTT.CYCLE_COUNT_ID
, MMTT.RMA_LINE_ID
, MMTT.CUSTOMER_SHIP_ID
, MMTT.CURRENCY_CODE
, MMTT.CURRENCY_CONVERSION_RATE
, MMTT.CURRENCY_CONVERSION_TYPE
, MMTT.CURRENCY_CONVERSION_DATE
, MMTT.USSGL_TRANSACTION_CODE
, MMTT.VENDOR_LOT_NUMBER
, MMTT.ENCUMBRANCE_ACCOUNT
, MMTT.ENCUMBRANCE_AMOUNT
, HLT.LOCATION_CODE
, MMTT.SHIPMENT_NUMBER
, MMTT.TRANSFER_COST
, MMTT.TRANSPORTATION_COST
, MMTT.TRANSPORTATION_ACCOUNT
, MMTT.FREIGHT_CODE
, MMTT.CONTAINERS
, MMTT.WAYBILL_AIRBILL
, MMTT.EXPECTED_ARRIVAL_DATE
, MMTT.TRANSFER_SUBINVENTORY
, MMTT.TRANSFER_ORGANIZATION
, MP2.ORGANIZATION_CODE TRANSFER_ORG_CODE
, HROT2.NAME TRANSFER_ORG_NAME
, MMTT.TRANSFER_TO_LOCATION
, MMTT.NEW_AVERAGE_COST
, MMTT.VALUE_CHANGE
, MMTT.PERCENTAGE_CHANGE
, MMTT.MATERIAL_ALLOCATION_TEMP_ID
, MMTT.DEMAND_SOURCE_HEADER_ID
, MMTT.DEMAND_SOURCE_LINE
, MMTT.DEMAND_SOURCE_DELIVERY
, MMTT.WIP_SUPPLY_TYPE
, MMTT.POSTING_FLAG
, DECODE(MMTT.POSTING_FLAG
, 'N'
, 2
, 1) DECODED_POSTING_FLAG
, MMTT.PROCESS_FLAG
, DECODE(MMTT.PROCESS_FLAG
, 'N'
, 2
, 'E'
, 3
, 1) DECODED_PROCESS_FLAG
, MMTT.ERROR_CODE
, MMTT.PRIMARY_SWITCH
, BD.DEPARTMENT_CODE
, MMTT.ERROR_EXPLANATION
, MMTT.DEMAND_ID
, MMTT.REQUISITION_LINE_ID
, MMTT.REQUISITION_DISTRIBUTION_ID
, MMTT.MOVEMENT_ID
, MMTT.ATTRIBUTE_CATEGORY
, MMTT.ATTRIBUTE1
, MMTT.ATTRIBUTE2
, MMTT.ATTRIBUTE3
, MMTT.ATTRIBUTE4
, MMTT.ATTRIBUTE5
, MMTT.ATTRIBUTE6
, MMTT.ATTRIBUTE7
, MMTT.ATTRIBUTE8
, MMTT.ATTRIBUTE9
, MMTT.ATTRIBUTE10
, MMTT.ATTRIBUTE11
, MMTT.ATTRIBUTE12
, MMTT.ATTRIBUTE13
, MMTT.ATTRIBUTE14
, MMTT.ATTRIBUTE15
, MMTT.SOURCE_PROJECT_ID
, MMTT.SOURCE_TASK_ID
, MMTT.PROJECT_ID
, MMTT.TO_PROJECT_ID
, MMTT.TASK_ID
, MMTT.TO_TASK_ID
, MMTT.EXPENDITURE_TYPE
, MMTT.PA_EXPENDITURE_ORG_ID
, MMTT.COST_GROUP_ID
, MMTT.OVERCOMPLETION_TRANSACTION_QTY
, DECODE(NVL(MMTT.TRANSACTION_STATUS
, 1)
, 2
, 2
, 1) TRANSACTION_STATUS
, LKUP3.MEANING TRANSACTION_STATUS_M
, MMTT.TRANSFER_COST_GROUP_ID
, MMTT.LPN_ID
, MMTT.TRANSFER_LPN_ID
, LPN1.LICENSE_PLATE_NUMBER LPN
, LPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, CG.COST_GROUP COST_GROUP_NAME
, CG2.COST_GROUP TRANSFER_COST_GROUP_NAME
, MMTT.PUT_AWAY_STRATEGY_ID
, MMTT.PUT_AWAY_RULE_ID
, MMTT.PICK_STRATEGY_ID
, MMTT.PICK_RULE_ID
, MMTT.MOVE_ORDER_LINE_ID
, MMTT.CONTENT_LPN_ID
, LPN3.LICENSE_PLATE_NUMBER CONTENT_LPN
, LPN4.LICENSE_PLATE_NUMBER CARTONIZED_LPN
, MMTT.OWNING_ORGANIZATION_ID
, MMTT.OWNING_TP_TYPE
, MMTT.PLANNING_ORGANIZATION_ID
, MMTT.PLANNING_TP_TYPE
, MMTT.SECONDARY_TRANSACTION_QUANTITY
, MMTT.SECONDARY_UOM_CODE
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MFG_LOOKUPS LKUP1
, MFG_LOOKUPS LKUP2
, MFG_LOOKUPS LKUP3
, MTL_TRANSACTION_TYPES MTT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, HR_ORGANIZATION_UNITS HRO1
, HR_ORG_UNITS_NO_JOIN HROC2
, HR_ALL_ORGANIZATION_UNITS_TL HROT2
, MTL_TRANSACTION_REASONS MTR
, WIP_LINES WL
, BOM_DEPARTMENTS BD
, HR_LOCATIONS_ALL_TL HLT
, HR_LOCATIONS_NO_JOIN HLC
, CST_COST_GROUPS CG
, CST_COST_GROUPS CG2
, WMS_LICENSE_PLATE_NUMBERS LPN1
, WMS_LICENSE_PLATE_NUMBERS LPN2
, WMS_LICENSE_PLATE_NUMBERS LPN3
, WMS_LICENSE_PLATE_NUMBERS LPN4
WHERE MMTT .ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
AND NVL(TRANSACTION_MODE
, 1) = LKUP1.LOOKUP_CODE
AND LKUP1.LOOKUP_TYPE = 'MTL_TRANSACTION_MODE'
AND MMTT.TRANSACTION_ACTION_ID + 0 = LKUP2.LOOKUP_CODE
AND LKUP2.LOOKUP_TYPE = 'MTL_TRANSACTION_ACTION'
AND LKUP3.LOOKUP_TYPE = 'MTL_INV_TRANSACTION_STATUS'
AND DECODE(NVL(MMTT.TRANSACTION_STATUS
, 1)
, 2
, 2
, 1) = LKUP3.LOOKUP_CODE
AND MMTT.REASON_ID = MTR.REASON_ID (+)
AND MMTT.REPETITIVE_LINE_ID = WL.LINE_ID (+)
AND MMTT.ORGANIZATION_ID = WL.ORGANIZATION_ID (+)
AND MMTT.DEPARTMENT_ID = BD.DEPARTMENT_ID (+)
AND MMTT.SHIP_TO_LOCATION = HLC.LOCATION_ID (+)
AND HLC.LOCATION_ID = HLT.LOCATION_ID (+)
AND DECODE(HLT.LOCATION_ID
, NULL
, '1'
, HLT.LANGUAGE) = DECODE(HLT.LOCATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND MMTT.ORGANIZATION_ID = HRO1.ORGANIZATION_ID
AND MMTT.TRANSFER_ORGANIZATION = MP2.ORGANIZATION_ID (+)
AND MMTT.TRANSFER_ORGANIZATION = HROC2.ORGANIZATION_ID (+)
AND HROC2.ORGANIZATION_ID = HROT2.ORGANIZATION_ID (+)
AND DECODE(HROT2.ORGANIZATION_ID
, NULL
, '1'
, HROT2.LANGUAGE) = DECODE(HROT2.ORGANIZATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND MMTT.COST_GROUP_ID = CG.COST_GROUP_ID(+)
AND MMTT.TRANSFER_COST_GROUP_ID = CG2.COST_GROUP_ID(+)
AND MMTT.LPN_ID = LPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = LPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = LPN3.LPN_ID(+)
AND MMTT.CARTONIZATION_ID = LPN4.LPN_ID(+)
AND MP1.WMS_ENABLED_FLAG = 'Y'
AND MMTT.INVENTORY_ITEM_ID = -1
AND MMTT.CONTENT_LPN_ID IS NOT NULL