View: CST_LAYER_ACTUAL_COST_DTLS_V

Product:	BOM - Bills of Material
Description:
Implementation/DBA Data:	APPS.CST_LAYER_ACTUAL_COST_DTLS_V

View Text

SELECT CLACD.TRANSACTION_ID
, CLACD.ORGANIZATION_ID
, CLACD.LAYER_ID
, CLACD.INV_LAYER_ID
, CLACD.LAYER_QUANTITY
, CLACD.TRANSACTION_SOURCE_TYPE_NAME
, CLACD.TRANSACTION_SOURCE_TYPE_ID
, CLACD.MATERIAL_COST
, CLACD.MATERIAL_OVERHEAD_COST
, CLACD.RESOURCE_COST
, CLACD.OUTSIDE_PROCESSING_COST
, CLACD.OVERHEAD_COST
, CLACD.TOTAL_UNIT_COST
, CLACD.TRANSACTION_SOURCE_ID
, CLACD.TRANSACTION_SOURCE_NAME
FROM ( SELECT MCLACD.TRANSACTION_ID
, MCLACD.ORGANIZATION_ID
, MCLACD.LAYER_ID
, MCLACD.INV_LAYER_ID
, MCLACD.LAYER_QUANTITY
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, CIL.TRANSACTION_SOURCE_TYPE_ID
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 1
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) MATERIAL_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 2
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) MATERIAL_OVERHEAD_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 3
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) RESOURCE_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 4
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) OUTSIDE_PROCESSING_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 5
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) OVERHEAD_COST
, SUM(NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)) TOTAL_UNIT_COST
, CIL.TRANSACTION_SOURCE_ID
, CIL.TRANSACTION_SOURCE TRANSACTION_SOURCE_NAME
, MMT.TRANSACTION_ACTION_ID
FROM MTL_CST_LAYER_ACT_COST_DETAILS MCLACD
, CST_INV_LAYERS CIL
, MTL_TXN_SOURCE_TYPES MTST
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE MCLACD.ORGANIZATION_ID = CIL.ORGANIZATION_ID
AND MCLACD.LAYER_ID = CIL.LAYER_ID
AND MCLACD.INV_LAYER_ID = CIL.INV_LAYER_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = CIL.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_ID = MCLACD.TRANSACTION_ID GROUP BY MCLACD.TRANSACTION_ID
, MCLACD.ORGANIZATION_ID
, MCLACD.LAYER_ID
, MCLACD.INV_LAYER_ID
, MCLACD.LAYER_QUANTITY
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, CIL.TRANSACTION_SOURCE_TYPE_ID
, CIL.TRANSACTION_SOURCE_ID
, CIL.TRANSACTION_SOURCE ) CLACD
WHERE CLACD.TRANSACTION_ACTION_ID IN (24
, 15
, 22) OR EXISTS ( SELECT 'X'
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = CLACD.TRANSACTION_ID
AND SIGN(MTA.PRIMARY_QUANTITY) = SIGN (CLACD.LAYER_QUANTITY)
AND MTA.ACCOUNTING_LINE_TYPE = 1 ) UNION ALL SELECT CLACD.TRANSACTION_ID
, CLACD.ORGANIZATION_ID
, CLACD.LAYER_ID
, CLACD.INV_LAYER_ID
, 0
, CLACD.TRANSACTION_SOURCE_TYPE_NAME
, CLACD.TRANSACTION_SOURCE_TYPE_ID
, CLACD.MATERIAL_COST
, CLACD.MATERIAL_OVERHEAD_COST
, CLACD.RESOURCE_COST
, CLACD.OUTSIDE_PROCESSING_COST
, CLACD.OVERHEAD_COST
, CLACD.TOTAL_UNIT_COST
, CLACD.TRANSACTION_SOURCE_ID
, CLACD.TRANSACTION_SOURCE_NAME
FROM ( SELECT MCLACD.TRANSACTION_ID
, MCLACD.ORGANIZATION_ID
, MCLACD.LAYER_ID
, MCLACD.INV_LAYER_ID
, MCLACD.LAYER_QUANTITY
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, CIL.TRANSACTION_SOURCE_TYPE_ID
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 1
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) MATERIAL_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 2
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) MATERIAL_OVERHEAD_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 3
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) RESOURCE_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 4
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) OUTSIDE_PROCESSING_COST
, SUM(DECODE(MCLACD.COST_ELEMENT_ID
, 5
, NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)
, 0)) OVERHEAD_COST
, SUM(NVL(DECODE(MMT.TRANSACTION_ACTION_ID
, 24
, MCLACD.ACTUAL_COST
, MCLACD.LAYER_COST)
, 0)) TOTAL_UNIT_COST
, CIL.TRANSACTION_SOURCE_ID
, CIL.TRANSACTION_SOURCE TRANSACTION_SOURCE_NAME
FROM MTL_CST_LAYER_ACT_COST_DETAILS MCLACD
, CST_INV_LAYERS CIL
, MTL_TXN_SOURCE_TYPES MTST
, MTL_MATERIAL_TRANSACTIONS MMT
WHERE MCLACD.ORGANIZATION_ID = CIL.ORGANIZATION_ID
AND MCLACD.LAYER_ID = CIL.LAYER_ID
AND MCLACD.INV_LAYER_ID = CIL.INV_LAYER_ID
AND MTST.TRANSACTION_SOURCE_TYPE_ID = CIL.TRANSACTION_SOURCE_TYPE_ID
AND MMT.TRANSACTION_ID = MCLACD.TRANSACTION_ID
AND MMT.TRANSACTION_ACTION_ID NOT IN (24
, 15
, 22) GROUP BY MCLACD.TRANSACTION_ID
, MCLACD.ORGANIZATION_ID
, MCLACD.LAYER_ID
, MCLACD.INV_LAYER_ID
, MCLACD.LAYER_QUANTITY
, MTST.TRANSACTION_SOURCE_TYPE_NAME
, CIL.TRANSACTION_SOURCE_TYPE_ID
, CIL.TRANSACTION_SOURCE_ID
, CIL.TRANSACTION_SOURCE ) CLACD
WHERE NOT EXISTS ( SELECT 'X'
FROM MTL_TRANSACTION_ACCOUNTS MTA
WHERE MTA.TRANSACTION_ID = CLACD.TRANSACTION_ID
AND SIGN(MTA.PRIMARY_QUANTITY) = SIGN (CLACD.LAYER_QUANTITY)
AND MTA.ACCOUNTING_LINE_TYPE = 1 )

Columns