eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: CSTBV_ITEM_COST_TXN_HISTORY

Product:	BOM - Bills of Material
Description:	- Retrofitted
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT MCACD.TRANSACTION_ID TRANSACTION_ID
, MTT.TRANSACTION_TYPE_NAME TRANSACTION_TYPE
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, MMT.PRIOR_COSTED_QUANTITY
, 24
, MMT.PRIOR_COSTED_QUANTITY
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, ((((-1 * MMT.PRIMARY_QUANTITY) * SUM(MCACD.ACTUAL_COST)) - (SUM(MCACD.NEW_COST) * (-1 * MMT.PRIMARY_QUANTITY)))/ (SUM(MCACD.NEW_COST) - SUM(MCACD.PRIOR_COST)))
, MMT.PRIOR_COSTED_QUANTITY)
, MMT.PRIOR_COSTED_QUANTITY)
, 21
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, 12
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, MMT.PRIOR_COSTED_QUANTITY) PRIOR_COSTED_QUANTITY
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, TO_NUMBER(NULL)
, 24
, TO_NUMBER(NULL)
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, -1 * MMT.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY)
, MMT.PRIMARY_QUANTITY)
, 21
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIMARY_QUANTITY
, -1 * MMT.PRIMARY_QUANTITY)
, 12
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIMARY_QUANTITY
, -1 * MMT.PRIMARY_QUANTITY)
, -1 * MMT.PRIMARY_QUANTITY)
, MMT.PRIMARY_QUANTITY) PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, MMT.PRIOR_COSTED_QUANTITY
, 24
, MMT.PRIOR_COSTED_QUANTITY
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, ((((-1 * MMT.PRIMARY_QUANTITY) * SUM(MCACD.ACTUAL_COST)) - (SUM(MCACD.NEW_COST) * (-1 * MMT.PRIMARY_QUANTITY)))/ (SUM(MCACD.NEW_COST) - SUM(MCACD.PRIOR_COST))) + (-1 * MMT.PRIMARY_QUANTITY)
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, 21
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, 12
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY)) NEW_QUANTITY
, SUM(MCACD.ACTUAL_COST) ACTUAL_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.ACTUAL_COST
, 0)) ACTUAL_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OVERHEAD
, SUM(MCACD.PRIOR_COST) PRIOR_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.PRIOR_COST
, 0)) PRIOR_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.PRIOR_COST
, 0)) PRIOR_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.PRIOR_COST
, 0)) PRIOR_OVERHEAD
, SUM(MCACD.NEW_COST) NEW_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.NEW_COST
, 0)) NEW_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.NEW_COST
, 0)) NEW_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.NEW_COST
, 0)) NEW_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.NEW_COST
, 0)) NEW_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.NEW_COST
, 0)) NEW_OVERHEAD
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MCACD.ORGANIZATION_ID ORGANIZATION_ID
, CQL.COST_GROUP_ID COST_GROUP_ID
, MCACD.LAYER_ID LAYER_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_SECONDARY_INVENTORIES MSI
, MTL_SYSTEM_ITEMS MSITEM
, CST_QUANTITY_LAYERS CQL
, MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE ( (((MMT.TRANSACTION_ACTION_ID!=12)
AND (MMT.TRANSACTION_ACTION_ID!= 21))
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1) OR( ((MMT.TRANSACTION_ACTION_ID=12) OR (MMT.TRANSACTION_ACTION_ID= 21))
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID ) )
AND ( MMT.INVENTORY_ITEM_ID = MSITEM.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSITEM.ORGANIZATION_ID
AND MSITEM.INVENTORY_ASSET_FLAG = 'Y' )
AND ( ( MMT.TRANSACTION_ACTION_ID = 21
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND EXISTS ( SELECT 'X'
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MIP.FOB_POINT = 1 )
AND ((MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID
AND MMT.COST_GROUP_ID = CQL.COST_GROUP_ID
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1) OR (MMT.TRANSFER_ORGANIZATION_ID = MCACD.ORGANIZATION_ID
AND MMT.TRANSFER_COST_GROUP_ID = CQL.COST_GROUP_ID) ) ) OR ( MMT.TRANSACTION_ACTION_ID = 12
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND EXISTS ( SELECT 'X'
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.FROM_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MIP.FOB_POINT = 1 )
AND ( (MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID)
AND (((MMT.COST_GROUP_ID=CQL.COST_GROUP_ID)
AND (MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 2)) OR (((MMT.TRANSFER_COST_GROUP_ID = CQL.COST_GROUP_ID) OR (MMT.COST_GROUP_ID = CQL.COST_GROUP_ID))
AND ((MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND (MMT.COST_GROUP_ID!=MMT.TRANSFER_COST_GROUP_ID)) ) ) ) ) )
AND MCACD.LAYER_ID = CQL.LAYER_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND (( ( ((MMT.TRANSACTION_ACTION_ID != 1) OR (MMT.TRANSACTION_SOURCE_TYPE_ID != 5))
AND ((MMT.TRANSACTION_ACTION_ID!=12)
AND (MMT.TRANSACTION_ACTION_ID!=21)) )
AND MMT.COST_GROUP_ID = CQL.COST_GROUP_ID) OR (((MMT.TRANSACTION_ACTION_ID = 1)
AND (MMT.TRANSACTION_SOURCE_TYPE_ID = 5))) OR (MMT.TRANSACTION_ACTION_ID=12 OR MMT.TRANSACTION_ACTION_ID=21) )
AND ( (MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.COST_GROUP_ID = 1
AND MMT.TRANSFER_COST_GROUP_ID != 1
AND MCACD.TRANSACTION_ACTION_ID = 2) OR ( MMT.TRANSACTION_ACTION_ID = MCACD.TRANSACTION_ACTION_ID ) )
AND '_SEC:MCACD:ORGANIZATION_ID' IS NOT NULL GROUP BY MMT.INVENTORY_ITEM_ID
, MCACD.TRANSACTION_ID
, MCACD.ORGANIZATION_ID
, MCACD.LAYER_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY
, MMT.PRIMARY_QUANTITY
, MMT.QUANTITY_ADJUSTED
, CQL.COST_GROUP_ID
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MSI.ASSET_INVENTORY
, MMT.COST_GROUP_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY UNION SELECT MCACD.TRANSACTION_ID TRANSACTION_ID
, MTT.TRANSACTION_TYPE_NAME TRANSACTION_TYPE
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, MMT.PRIOR_COSTED_QUANTITY
, 24
, MMT.PRIOR_COSTED_QUANTITY
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, ((((-1 * MMT.PRIMARY_QUANTITY) * SUM(MCACD.ACTUAL_COST)) - (SUM(MCACD.NEW_COST) * (-1 * MMT.PRIMARY_QUANTITY)))/ (SUM(MCACD.NEW_COST) - SUM(MCACD.PRIOR_COST)))
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, 21
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, 12
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY)
, (MMT.PRIOR_COSTED_QUANTITY)) PRIOR_COSTED_QUANTITY
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, TO_NUMBER(NULL)
, 24
, TO_NUMBER(NULL)
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, -1 * MMT.PRIMARY_QUANTITY
, MMT.PRIMARY_QUANTITY)
, MMT.PRIMARY_QUANTITY)
, 21
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIMARY_QUANTITY
, -1 * MMT.PRIMARY_QUANTITY)
, -1 * MMT.PRIMARY_QUANTITY)
, 12
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIMARY_QUANTITY
, -1 * MMT.PRIMARY_QUANTITY)
, MMT.PRIMARY_QUANTITY) PRIMARY_QUANTITY
, DECODE(MMT.TRANSACTION_ACTION_ID
, 30
, MMT.PRIOR_COSTED_QUANTITY
, 24
, MMT.PRIOR_COSTED_QUANTITY
, 1
, DECODE(MMT.TRANSACTION_SOURCE_TYPE_ID
, 5
, DECODE(CQL.COST_GROUP_ID
, MMT.TRANSFER_COST_GROUP_ID
, ((((-1 * MMT.PRIMARY_QUANTITY) * SUM(MCACD.ACTUAL_COST)) - (SUM(MCACD.NEW_COST) * (-1 * MMT.PRIMARY_QUANTITY)))/ (SUM(MCACD.NEW_COST) - SUM(MCACD.PRIOR_COST))) + (-1 * MMT.PRIMARY_QUANTITY)
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY))
, 21
, DECODE(MSI.ASSET_INVENTORY
, 1
, DECODE(CQL.COST_GROUP_ID
, MMT.COST_GROUP_ID
, MMT.PRIOR_COSTED_QUANTITY + MMT.PRIMARY_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, 12
, DECODE(MCACD.ORGANIZATION_ID
, MMT.ORGANIZATION_ID
, MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY - MMT.PRIMARY_QUANTITY)
, (MMT.PRIMARY_QUANTITY + MMT.PRIOR_COSTED_QUANTITY)) NEW_QUANTITY
, SUM(MCACD.ACTUAL_COST) ACTUAL_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.ACTUAL_COST
, 0)) ACTUAL_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OVERHEAD
, SUM(MCACD.PRIOR_COST) PRIOR_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.PRIOR_COST
, 0)) PRIOR_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.PRIOR_COST
, 0)) PRIOR_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.PRIOR_COST
, 0)) PRIOR_OVERHEAD
, SUM(MCACD.NEW_COST) NEW_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.NEW_COST
, 0)) NEW_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.NEW_COST
, 0)) NEW_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.NEW_COST
, 0)) NEW_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.NEW_COST
, 0)) NEW_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.NEW_COST
, 0)) NEW_OVERHEAD
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MCACD.ORGANIZATION_ID ORGANIZATION_ID
, CQL.COST_GROUP_ID COST_GROUP_ID
, MCACD.LAYER_ID LAYER_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, MTL_SECONDARY_INVENTORIES MSI
, MTL_SYSTEM_ITEMS MSITEM
, CST_QUANTITY_LAYERS CQL
, MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE ( (((MMT.TRANSACTION_ACTION_ID!=12)
AND (MMT.TRANSACTION_ACTION_ID!= 21))
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1) OR( ((MMT.TRANSACTION_ACTION_ID=12) OR (MMT.TRANSACTION_ACTION_ID= 21))
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID ) )
AND ( MMT.INVENTORY_ITEM_ID = MSITEM.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSITEM.ORGANIZATION_ID
AND MSITEM.INVENTORY_ASSET_FLAG = 'Y' )
AND ( ((MMT.TRANSACTION_ACTION_ID = 2
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 13)
AND MMT.PRIMARY_QUANTITY < 0
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND ( ((MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT1
, MTL_SECONDARY_INVENTORIES MSI1
WHERE (MMT1.TRANSFER_SUBINVENTORY = MSI1.SECONDARY_INVENTORY_NAME
AND MMT1.ORGANIZATION_ID = MSI1.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1
AND MMT.TRANSACTION_ID = MMT1.TRANSACTION_ID) )
AND (MMT.COST_GROUP_ID != MMT.TRANSFER_COST_GROUP_ID)) OR ( (MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT2
, MTL_SECONDARY_INVENTORIES MSI2
WHERE (MMT2.TRANSFER_SUBINVENTORY = MSI2.SECONDARY_INVENTORY_NAME
AND MMT2.ORGANIZATION_ID = MSI2.ORGANIZATION_ID
AND MSI2.ASSET_INVENTORY = 2
AND MMT2.TRANSACTION_ID = MMT.TRANSACTION_ID) ) ) ) ) OR ((MMT.TRANSACTION_ACTION_ID = 2
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 13)
AND MMT.PRIMARY_QUANTITY > 0
AND MMT.TRANSFER_TRANSACTION_ID = MCACD.TRANSACTION_ID
AND ( ((MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT3
, MTL_SECONDARY_INVENTORIES MSI3
WHERE (MMT3.TRANSFER_SUBINVENTORY = MSI3.SECONDARY_INVENTORY_NAME
AND MMT3.ORGANIZATION_ID = MSI3.ORGANIZATION_ID
AND MSI3.ASSET_INVENTORY = 1
AND MMT.TRANSACTION_ID = MMT3.TRANSACTION_ID) )
AND (MMT.COST_GROUP_ID != MMT.TRANSFER_COST_GROUP_ID)) OR ((MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND EXISTS (SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS MMT4
, MTL_SECONDARY_INVENTORIES MSI4
WHERE (MMT4.TRANSFER_SUBINVENTORY = MSI4.SECONDARY_INVENTORY_NAME
AND MMT4.ORGANIZATION_ID = MSI4.ORGANIZATION_ID
AND MSI4.ASSET_INVENTORY = 2
AND MMT4.TRANSACTION_ID = MMT.TRANSACTION_ID) ) ) ) ) OR ( MMT.TRANSACTION_ACTION_ID = 21
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND EXISTS ( SELECT 'X'
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.FROM_ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MIP.FOB_POINT = 2 )
AND ( (MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID)
AND ( (((MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 1)
AND (MMT.COST_GROUP_ID != MMT.TRANSFER_COST_GROUP_ID))
AND ((MMT.COST_GROUP_ID=CQL.COST_GROUP_ID) OR (MMT.TRANSFER_COST_GROUP_ID = CQL.COST_GROUP_ID))) OR ((MMT.TRANSFER_COST_GROUP_ID=CQL.COST_GROUP_ID)
AND (MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MSI.ASSET_INVENTORY = 2)) ) ) ) OR ( MMT.TRANSACTION_ACTION_ID = 12
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND EXISTS ( SELECT 'X'
FROM MTL_INTERORG_PARAMETERS MIP
WHERE MIP.FROM_ORGANIZATION_ID = MMT.TRANSFER_ORGANIZATION_ID
AND MIP.TO_ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MIP.FOB_POINT = 2 )
AND ((MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID
AND MMT.SUBINVENTORY_CODE = MSI.SECONDARY_INVENTORY_NAME
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MMT.COST_GROUP_ID = CQL.COST_GROUP_ID
AND MSI.ASSET_INVENTORY = 1) OR (MMT.TRANSFER_ORGANIZATION_ID = MCACD.ORGANIZATION_ID
AND MMT.TRANSFER_COST_GROUP_ID = CQL.COST_GROUP_ID) ) ) OR ((MMT.TRANSACTION_ACTION_ID != 2 OR MMT.TRANSACTION_SOURCE_TYPE_ID != 13)
AND (MMT.TRANSACTION_ACTION_ID != 12
AND MMT.TRANSACTION_ACTION_ID != 21)
AND MMT.TRANSACTION_ACTION_ID != 24
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID ) )
AND MCACD.LAYER_ID = CQL.LAYER_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND (( ( ((MMT.TRANSACTION_ACTION_ID != 1) OR (MMT.TRANSACTION_SOURCE_TYPE_ID != 5))
AND ((MMT.TRANSACTION_ACTION_ID!=12)
AND (MMT.TRANSACTION_ID!=21 )) )
AND MMT.COST_GROUP_ID = CQL.COST_GROUP_ID) OR (((MMT.TRANSACTION_ACTION_ID = 1)
AND (MMT.TRANSACTION_SOURCE_TYPE_ID = 5))) OR (MMT.TRANSACTION_ACTION_ID=12 OR MMT.TRANSACTION_ACTION_ID=21) )
AND ( (MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.COST_GROUP_ID = 1
AND MMT.TRANSFER_COST_GROUP_ID != 1
AND MCACD.TRANSACTION_ACTION_ID = 2) OR ( MMT.TRANSACTION_ACTION_ID = MCACD.TRANSACTION_ACTION_ID ) )
AND '_SEC:MCACD:ORGANIZATION_ID' IS NOT NULL GROUP BY MMT.INVENTORY_ITEM_ID
, MCACD.TRANSACTION_ID
, MCACD.ORGANIZATION_ID
, MCACD.LAYER_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY
, MMT.PRIMARY_QUANTITY
, MMT.QUANTITY_ADJUSTED
, CQL.COST_GROUP_ID
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MSI.ASSET_INVENTORY
, MMT.COST_GROUP_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY UNION SELECT MCACD.TRANSACTION_ID TRANSACTION_ID
, MTT.TRANSACTION_TYPE_NAME TRANSACTION_TYPE
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, MMT.PRIOR_COSTED_QUANTITY PRIOR_COSTED_QUANTITY
, TO_NUMBER(NULL) PRIMARY_QUANTITY
, MMT.PRIOR_COSTED_QUANTITY NEW_QUANTITY
, SUM(MCACD.ACTUAL_COST) ACTUAL_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.ACTUAL_COST
, 0)) ACTUAL_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.ACTUAL_COST
, 0)) ACTUAL_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.ACTUAL_COST
, 0)) ACTUAL_OVERHEAD
, SUM(MCACD.PRIOR_COST) PRIOR_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.PRIOR_COST
, 0)) PRIOR_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.PRIOR_COST
, 0)) PRIOR_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.PRIOR_COST
, 0)) PRIOR_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.PRIOR_COST
, 0)) PRIOR_OVERHEAD
, SUM(MCACD.NEW_COST) NEW_COST
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 1
, MCACD.NEW_COST
, 0)) NEW_MATERIAL
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 2
, MCACD.NEW_COST
, 0)) NEW_MATERIAL_OVERHEAD
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 3
, MCACD.NEW_COST
, 0)) NEW_RESOURCE
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 4
, MCACD.NEW_COST
, 0)) NEW_OUTSIDE_PROCESSING
, SUM(DECODE(MCACD.COST_ELEMENT_ID
, 5
, MCACD.NEW_COST
, 0)) NEW_OVERHEAD
, MMT.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MCACD.ORGANIZATION_ID ORGANIZATION_ID
, CQL.COST_GROUP_ID COST_GROUP_ID
, MCACD.LAYER_ID LAYER_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_TYPES MTT
, CST_QUANTITY_LAYERS CQL
, MTL_CST_ACTUAL_COST_DETAILS MCACD
WHERE ( ( MMT.TRANSACTION_ACTION_ID = 24
AND MMT.TRANSACTION_ID = MCACD.TRANSACTION_ID
AND MMT.ORGANIZATION_ID = MCACD.ORGANIZATION_ID ) )
AND MCACD.LAYER_ID = CQL.LAYER_ID
AND MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID
AND (( ( ((MMT.TRANSACTION_ACTION_ID != 1) OR (MMT.TRANSACTION_SOURCE_TYPE_ID != 5))
AND ((MMT.TRANSACTION_ACTION_ID!=12)
AND (MMT.TRANSACTION_ID!=21 )) )
AND MMT.COST_GROUP_ID = CQL.COST_GROUP_ID) OR (((MMT.TRANSACTION_ACTION_ID = 1)
AND (MMT.TRANSACTION_SOURCE_TYPE_ID = 5))) OR (MMT.TRANSACTION_ACTION_ID=12 OR MMT.TRANSACTION_ACTION_ID=21) )
AND ( (MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 5
AND MMT.COST_GROUP_ID = 1
AND MMT.TRANSFER_COST_GROUP_ID != 1
AND MCACD.TRANSACTION_ACTION_ID = 2) OR ( MMT.TRANSACTION_ACTION_ID = MCACD.TRANSACTION_ACTION_ID ) )
AND '_SEC:MCACD:ORGANIZATION_ID' IS NOT NULL GROUP BY MMT.INVENTORY_ITEM_ID
, MCACD.TRANSACTION_ID
, MCACD.ORGANIZATION_ID
, MCACD.LAYER_ID
, MMT.PRIOR_COSTED_QUANTITY
, MMT.TRANSFER_PRIOR_COSTED_QUANTITY
, MMT.PRIMARY_QUANTITY
, MMT.QUANTITY_ADJUSTED
, CQL.COST_GROUP_ID
, MCACD.TRANSACTION_COSTED_DATE
, MMT.TRANSACTION_DATE
, MTT.TRANSACTION_TYPE_NAME
, MMT.TRANSACTION_ACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID
, MMT.TRANSFER_COST_GROUP_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.COST_GROUP_ID
, MMT.CREATION_DATE
, MMT.CREATED_BY
, MMT.LAST_UPDATE_DATE
, MMT.LAST_UPDATED_BY WITH READ ONLY

Columns

Name
TRANSACTION_ID
TRANSACTION_TYPE
TRANSACTION_COSTED_DATE
TRANSACTION_DATE
PRIOR_COSTED_QUANTITY
TRANSACTION_QUANTITY
NEW_QUANTITY
TRANSACTION_COST
TXN_MATERIAL_COST
TXN_MATERIAL_OVHD_COST
TXN_RESOURCE_COST
TXN_OUTSIDE_PROCESSING_COST
TXN_OVERHEAD_COST
PRIOR_COST
PRIOR_MATERIAL_COST
PRIOR_MATERIAL_OVERHEAD_COST
PRIOR_RESOURCE_COST
PRIOR_OUTSIDE_PROCESSING_COST
PRIOR_OVERHEAD_COST
NEW_COST
NEW_MATERIAL_COST
NEW_MATERIAL_OVERHEAD_COST
NEW_RESOURCE_COST
NEW_OUTSIDE_PROCESSING_COST
NEW_OVERHEAD_COST
INVENTORY_ITEM_ID
ORGANIZATION_ID
COST_GROUP_ID
LAYER_ID
CREATED_DATE
CREATED_BY
UPDATED_DATE
UPDATED_BY