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.

VIEW: APPS.CST_LAYER_ACTUAL_COST_DTLS_V

Source

View Text - Preformatted


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 ,MMT.TRANSACTION_ACTION_ID ) 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 )

View Text - HTML Formatted

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
, MMT.TRANSACTION_ACTION_ID ) 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 )