View: MTL_TRANSACTION_DETAILS_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.MTL_TRANSACTION_DETAILS_V

View Text

SELECT MSN.GEN_OBJECT_ID
, 2
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, SUBSTR(DECODE( MMT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID ) )
, 1
, 30)
, SUBSTR(INV_OBJECT_GENEALOGY.GETTRADINGPARTNER ( MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSFER_ORGANIZATION_ID )
, 1
, 30)
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMT.TRANSACTION_UOM
, 1
, MMT.CREATED_BY
, MMT.SOURCE_CODE
, MMT.SOURCE_LINE_ID
, MMT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMT.ORIGINAL_TRANSACTION_TEMP_ID
, MMT.VENDOR_LOT_NUMBER
, NULL
, NULL
, NULL
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
WHERE MSN.SERIAL_NUMBER = MSNT.SERIAL_NUMBER
AND MSN.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND MSNT.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMT.TASK_ID = PTV.TASK_ID(+)
AND MMT.LPN_ID = WLPN1.LPN_ID(+)
AND MMT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMT.CONTENT_LPN_ID = WLPN3.LPN_ID(+) UNION ALL (SELECT MLN.GEN_OBJECT_ID
, 1
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, SUBSTR(DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID ) )
, 1
, 30)
, SUBSTR(INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSFER_ORGANIZATION_ID )
, 1
, 30)
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, MTLN.TRANSACTION_QUANTITY
, MMT.TRANSACTION_UOM
, MTLN.PRIMARY_QUANTITY
, MMT.CREATED_BY
, MMT.SOURCE_CODE
, MMT.SOURCE_LINE_ID
, MMT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMT.ORIGINAL_TRANSACTION_TEMP_ID
, NVL( MTLN.SUPPLIER_LOT_NUMBER
, MMT.VENDOR_LOT_NUMBER)
, MTLN.LOT_NUMBER
, MTLN.SECONDARY_TRANSACTION_QUANTITY
, MTLN.GRADE_CODE
FROM MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_LOT_NUMBERS MLN
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
WHERE MLN.LOT_NUMBER = MTLN.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = MTLN.INVENTORY_ITEM_ID
AND MTLN.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MTLN.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMT.TASK_ID = PTV.TASK_ID(+)
AND MMT.LPN_ID = WLPN1.LPN_ID(+)
AND MMT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMT.CONTENT_LPN_ID = WLPN3.LPN_ID(+)) UNION ALL (SELECT MSN.GEN_OBJECT_ID
, 2
, MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, SUBSTR(DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID ) )
, 1
, 30)
, SUBSTR(INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMT.ORGANIZATION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRX_SOURCE_LINE_ID
, MMT.TRANSFER_ORGANIZATION_ID )
, 1
, 30)
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMT.TRANSACTION_UOM
, 1
, MMT.CREATED_BY
, MMT.SOURCE_CODE
, MMT.SOURCE_LINE_ID
, MMT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMT.ORIGINAL_TRANSACTION_TEMP_ID
, NVL( MTLN.SUPPLIER_LOT_NUMBER
, MMT.VENDOR_LOT_NUMBER)
, MTLN.LOT_NUMBER
, NULL
, NULL
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TXN_SOURCE_TYPES MTST
, MTL_PARAMETERS MP
, PJM_TASKS_V PTV
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_TRANSACTION_LOT_NUMBERS MTLN
WHERE MSN.SERIAL_NUMBER = MSNT.SERIAL_NUMBER
AND MTLN.SERIAL_TRANSACTION_ID = MSNT.TRANSACTION_ID
AND MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID
AND MSN.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND MMT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMT.TASK_ID = PTV.TASK_ID(+)
AND MMT.LPN_ID = WLPN1.LPN_ID(+)
AND MMT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMT.CONTENT_LPN_ID = WLPN3.LPN_ID(+))

Columns