View: MTL_PENDING_TXN_DETAILS_V

Product:	INV - Inventory
Description:	Pending Transactions View in Serial Number Form & Genealogy Form
Implementation/DBA Data:	APPS.MTL_PENDING_TXN_DETAILS_V

View Text

SELECT MSN.GEN_OBJECT_ID
, 2
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE( MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMTT.TRANSACTION_UOM
, 1
, NULL
, NULL
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, NULL
, NULL
, MMTT.TRANSACTION_STATUS
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, 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 = MMTT.TRANSACTION_TEMP_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+) UNION ALL (SELECT MLN.GEN_OBJECT_ID
, 1
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE(MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, MTLT.TRANSACTION_QUANTITY
, MMTT.TRANSACTION_UOM
, MTLT.PRIMARY_QUANTITY
, MTLT.SECONDARY_QUANTITY
, MTLT.GRADE_CODE
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, MMTT.VENDOR_LOT_NUMBER
, MTLT.LOT_NUMBER
, MMTT.TRANSACTION_STATUS
FROM MTL_TRANSACTION_LOTS_TEMP MTLT
, MTL_LOT_NUMBERS MLN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, 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 = MTLT.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID = MMTT.INVENTORY_ITEM_ID
AND MTLT.TRANSACTION_TEMP_ID = MMTT.TRANSACTION_TEMP_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+)) UNION ALL (SELECT MSN.GEN_OBJECT_ID
, 2
, MMTT.TRANSACTION_TEMP_ID
, MMTT.TRANSACTION_DATE
, MP.ORGANIZATION_CODE
, MMTT.ORGANIZATION_ID
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, DECODE(MMTT.TRANSACTION_SOURCE_TYPE_ID
, 13
, MMTT.TRANSACTION_SOURCE_NAME
, INV_OBJECT_GENEALOGY.GETSOURCE ( MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID ) )
, INV_OBJECT_GENEALOGY.GETTRADINGPARTNER (MMTT.ORGANIZATION_ID
, MMTT.TRANSACTION_SOURCE_TYPE_ID
, MMTT.TRANSACTION_SOURCE_ID
, MMTT.TRX_SOURCE_LINE_ID
, MMTT.TRANSFER_ORGANIZATION )
, SUBSTR(PTV.PROJECT_NAME
, 1
, 30)
, PTV.TASK_NAME
, 1
, MMTT.TRANSACTION_UOM
, 1
, NULL
, NULL
, MMTT.CREATED_BY
, MMTT.SOURCE_CODE
, MMTT.SOURCE_LINE_ID
, MMTT.TRANSACTION_TYPE_ID
, WLPN1.LPN_ID
, WLPN2.LPN_ID
, WLPN3.LPN_ID
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER
, WLPN3.LICENSE_PLATE_NUMBER
, MMTT.TRANSACTION_TEMP_ID
, MMTT.VENDOR_LOT_NUMBER
, MTLT.LOT_NUMBER
, MMTT.TRANSACTION_STATUS
FROM MTL_UNIT_TRANSACTIONS MSNT
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, 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_LOTS_TEMP MTLT
WHERE MSN.SERIAL_NUMBER = MSNT.SERIAL_NUMBER
AND MTLT.SERIAL_TRANSACTION_TEMP_ID = MSNT.TRANSACTION_ID
AND MMTT.TRANSACTION_TEMP_ID = MTLT.TRANSACTION_TEMP_ID
AND MSN.INVENTORY_ITEM_ID = MSNT.INVENTORY_ITEM_ID
AND MMTT.TRANSACTION_SOURCE_TYPE_ID = MTST.TRANSACTION_SOURCE_TYPE_ID
AND MMTT.TRANSACTION_ACTION_ID NOT IN (24
, 30)
AND MMTT.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MMTT.PROJECT_ID = PTV.PROJECT_ID(+)
AND MMTT.TASK_ID = PTV.TASK_ID(+)
AND MMTT.LPN_ID = WLPN1.LPN_ID(+)
AND MMTT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND MMTT.CONTENT_LPN_ID = WLPN3.LPN_ID(+))

Columns