Product: | INV - Inventory |
---|---|
Description: | - Retrofitted |
Implementation/DBA Data: | APPS.INVFV_SERIAL_NUMBER_TXNS |
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