VIEW: APPS.INVFV_SERIAL_NUMBER_TXNS

Source

View Text - Preformatted

SELECT MLN.TRANSACTION_ID , MLN.SERIAL_TRANSACTION_ID , MUT.SERIAL_NUMBER , MUT.INVENTORY_ITEM_ID ,'_KF:INV:MSTK:MSI' , MUT.ORGANIZATION_ID , HOU.NAME , MP.ORGANIZATION_CODE , MUT.SUBINVENTORY_CODE , MUT.LOCATOR_ID , MLN.LOT_NUMBER , MUT.TRANSACTION_DATE , MUT.TRANSACTION_SOURCE_ID , MUT.TRANSACTION_SOURCE_TYPE_ID , MTS.TRANSACTION_SOURCE_TYPE_NAME , MUT.TRANSACTION_SOURCE_NAME , MUT.RECEIPT_ISSUE_TYPE , MSI.PRIMARY_UOM_CODE , MUT.CREATED_BY , MUT.CREATION_DATE , MUT.LAST_UPDATED_BY , MUT.LAST_UPDATE_DATE FROM MTL_SYSTEM_ITEMS MSI, MTL_TRANSACTION_LOT_NUMBERS MLN, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_TXN_SOURCE_TYPES MTS, MTL_UNIT_TRANSACTIONS MUT WHERE MTS.TRANSACTION_SOURCE_TYPE_ID = MUT.TRANSACTION_SOURCE_TYPE_ID AND HOU.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID AND MSI.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MUT.INVENTORY_ITEM_ID AND '_SEC:MUT.ORGANIZATION_ID' IS NOT NULL UNION ALL /* If the item is NOT UNDER LOT control then join to MTL_MATERIAL_TRANSACTIONS directly as the MTL_UNIT_TRANSACTIONS.TRANSACTION_ID is actually MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID*/ SELECT MUT.TRANSACTION_ID, TO_NUMBER (NULL), MUT.SERIAL_NUMBER, MUT.INVENTORY_ITEM_ID, '_KF:INV:MSTK:MSI', MUT.ORGANIZATION_ID, HOU.NAME, MP.ORGANIZATION_CODE, MUT.SUBINVENTORY_CODE, MUT.LOCATOR_ID, NULL, MUT.TRANSACTION_DATE, MUT.TRANSACTION_SOURCE_ID, MUT.TRANSACTION_SOURCE_TYPE_ID, MTS.TRANSACTION_SOURCE_TYPE_NAME, MUT.TRANSACTION_SOURCE_NAME, MUT.RECEIPT_ISSUE_TYPE, MSI.PRIMARY_UOM_CODE, MUT.CREATED_BY, MUT.CREATION_DATE, MUT.LAST_UPDATED_BY, MUT.LAST_UPDATE_DATE FROM MTL_SYSTEM_ITEMS MSI, MTL_MATERIAL_TRANSACTIONS MMT, HR_ALL_ORGANIZATION_UNITS HOU, MTL_PARAMETERS MP, MTL_TXN_SOURCE_TYPES MTS, MTL_UNIT_TRANSACTIONS MUT WHERE MTS.TRANSACTION_SOURCE_TYPE_ID = MUT.TRANSACTION_SOURCE_TYPE_ID AND HOU.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MP.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MMT.TRANSACTION_ID = MUT.TRANSACTION_ID AND MSI.ORGANIZATION_ID = MUT.ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID = MUT.INVENTORY_ITEM_ID AND '_SEC:MUT.ORGANIZATION_ID' IS NOT NULL AND MMT.ORGANIZATION_ID = NVL (MMT.OWNING_ORGANIZATION_ID, MMT.ORGANIZATION_ID) AND NVL(MMT.OWNING_TP_TYPE, 2) = 2 AND NVL(MMT.LOGICAL_TRANSACTION,-99) <> 1 WITH READ ONLY

View Text - HTML Formatted

SELECT MLN.TRANSACTION_ID
, MLN.SERIAL_TRANSACTION_ID
, MUT.SERIAL_NUMBER
, MUT.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, MUT.ORGANIZATION_ID
, HOU.NAME
, MP.ORGANIZATION_CODE
, MUT.SUBINVENTORY_CODE
, MUT.LOCATOR_ID
, MLN.LOT_NUMBER
, MUT.TRANSACTION_DATE
, MUT.TRANSACTION_SOURCE_ID
, MUT.TRANSACTION_SOURCE_TYPE_ID
, MTS.TRANSACTION_SOURCE_TYPE_NAME
, MUT.TRANSACTION_SOURCE_NAME
, MUT.RECEIPT_ISSUE_TYPE
, MSI.PRIMARY_UOM_CODE
, MUT.CREATED_BY
, MUT.CREATION_DATE
, MUT.LAST_UPDATED_BY
, MUT.LAST_UPDATE_DATE
FROM MTL_SYSTEM_ITEMS MSI
, MTL_TRANSACTION_LOT_NUMBERS MLN
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTS
, MTL_UNIT_TRANSACTIONS MUT
WHERE MTS.TRANSACTION_SOURCE_TYPE_ID = MUT.TRANSACTION_SOURCE_TYPE_ID
AND HOU.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID
AND MSI.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MUT.INVENTORY_ITEM_ID
AND '_SEC:MUT.ORGANIZATION_ID' IS NOT NULL UNION ALL /* IF THE ITEM IS NOT UNDER LOT CONTROL THEN JOIN TO MTL_MATERIAL_TRANSACTIONS DIRECTLY AS THE MTL_UNIT_TRANSACTIONS.TRANSACTION_ID IS ACTUALLY MTL_MATERIAL_TRANSACTIONS.TRANSACTION_ID*/ SELECT MUT.TRANSACTION_ID
, TO_NUMBER (NULL)
, MUT.SERIAL_NUMBER
, MUT.INVENTORY_ITEM_ID
, '_KF:INV:MSTK:MSI'
, MUT.ORGANIZATION_ID
, HOU.NAME
, MP.ORGANIZATION_CODE
, MUT.SUBINVENTORY_CODE
, MUT.LOCATOR_ID
, NULL
, MUT.TRANSACTION_DATE
, MUT.TRANSACTION_SOURCE_ID
, MUT.TRANSACTION_SOURCE_TYPE_ID
, MTS.TRANSACTION_SOURCE_TYPE_NAME
, MUT.TRANSACTION_SOURCE_NAME
, MUT.RECEIPT_ISSUE_TYPE
, MSI.PRIMARY_UOM_CODE
, MUT.CREATED_BY
, MUT.CREATION_DATE
, MUT.LAST_UPDATED_BY
, MUT.LAST_UPDATE_DATE
FROM MTL_SYSTEM_ITEMS MSI
, MTL_MATERIAL_TRANSACTIONS MMT
, HR_ALL_ORGANIZATION_UNITS HOU
, MTL_PARAMETERS MP
, MTL_TXN_SOURCE_TYPES MTS
, MTL_UNIT_TRANSACTIONS MUT
WHERE MTS.TRANSACTION_SOURCE_TYPE_ID = MUT.TRANSACTION_SOURCE_TYPE_ID
AND HOU.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MMT.TRANSACTION_ID = MUT.TRANSACTION_ID
AND MSI.ORGANIZATION_ID = MUT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MUT.INVENTORY_ITEM_ID
AND '_SEC:MUT.ORGANIZATION_ID' IS NOT NULL
AND MMT.ORGANIZATION_ID = NVL (MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND NVL(MMT.LOGICAL_TRANSACTION
, -99) <> 1 WITH READ ONLY