View: CST_OPI_MMT_MTA_V

Product:	BOM - Bills of Material
Description:	This view is used by Inventory Intellegence team for calculating transfer cost for FOB.
Implementation/DBA Data:	APPS.CST_OPI_MMT_MTA_V

View Text

SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, SUM(MTA.BASE_TRANSACTION_VALUE) ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.ACCOUNTING_LINE_TYPE = 1
AND ( MMT.PRIMARY_QUANTITY = NVL(MTA.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY) OR MMT.TRANSACTION_ACTION_ID IN (1
, 24))
AND MMT.TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_TYPE_ID <> 25 GROUP BY MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST UNION ALL SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, SUM(MTA.BASE_TRANSACTION_VALUE) ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.ACCOUNTING_LINE_TYPE = 1
AND MMT.PRIMARY_QUANTITY = MTA.PRIMARY_QUANTITY
AND MMT.TRANSFER_TRANSACTION_ID = MTA.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID IN (2
, 3
, 5
, 28
, 55) GROUP BY MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MTA.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST UNION ALL SELECT MMT.TRANSACTION_ID
, MMT.TRANSACTION_DATE
, MMT.ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE
, MMT.INVENTORY_ITEM_ID
, MMT.LOCATOR_ID
, MMT.TRANSACTION_TYPE_ID
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSACTION_SOURCE_ID
, MMT.TRANSACTION_SOURCE_NAME
, MMT.REASON_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.TRANSFER_SUBINVENTORY
, MMT.TRANSFER_LOCATOR_ID
, MMT.FOB_POINT
, MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_COST
, NULL ACTUAL_COST
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_PARAMETERS PAR
WHERE MMT.ORGANIZATION_ID=PAR.ORGANIZATION_ID
AND PAR.PROCESS_ENABLED_FLAG = 'N'
AND MMT.COSTED_FLAG IS NULL
AND MMT.TRANSACTION_TYPE_ID NOT IN (73
, 25
, 26
, 90
, 91
, 92
, 55
, 56
, 57
, 58
, 87
, 88
, 89)
AND MMT.ORGANIZATION_ID = NVL(MMT.OWNING_ORGANIZATION_ID
, MMT.ORGANIZATION_ID)
AND NVL(MMT.OWNING_TP_TYPE
, 2) = 2
AND NOT EXISTS (SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE = 1 UNION ALL SELECT 1
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = MMT.TRANSFER_TRANSACTION_ID
AND MTA.ACCOUNTING_LINE_TYPE = 1)

Columns