View: CST_PERIOD_BALANCES_VIEW

Product:	BOM - Bills of Material
Description:	Cost summary by cost element for job or repetitive schedule : SINGLE-ORG view
Implementation/DBA Data:	APPS.CST_PERIOD_BALANCES_VIEW

View Text

SELECT WPB.ACCT_PERIOD_ID
, WPB.WIP_ENTITY_ID
, WRS.LINE_ID
, WPB.ORGANIZATION_ID
, CCE.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, DECODE(CCE.COST_ELEMENT_ID
, 1
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.MATERIAL_ACCOUNT
, WRS.MATERIAL_ACCOUNT)
, 2
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WRS.MATERIAL_OVERHEAD_ACCOUNT)
, 3
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.RESOURCE_ACCOUNT
, WRS.RESOURCE_ACCOUNT)
, 4
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WRS.OUTSIDE_PROCESSING_ACCOUNT)
, 5
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.OVERHEAD_ACCOUNT
, WRS.OVERHEAD_ACCOUNT)
, NULL)
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, 0
, 2
, 0
, 3
, NVL(WPB.TL_RESOURCE_IN
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_IN
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.TL_MATERIAL_OUT
, 0)
, 2
, NVL(WPB.TL_MATERIAL_OVERHEAD_OUT
, 0)
, 3
, NVL(WPB.TL_RESOURCE_OUT
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_OUT
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_OUT
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.PL_MATERIAL_IN
, 0)
, 2
, NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0)
, 3
, NVL(WPB.PL_RESOURCE_IN
, 0)
, 4
, NVL(WPB.PL_OUTSIDE_PROCESSING_IN
, 0)
, 5
, NVL(WPB.PL_OVERHEAD_IN
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.PL_MATERIAL_OUT
, 0)
, 2
, NVL(WPB.PL_MATERIAL_OVERHEAD_OUT
, 0)
, 3
, NVL(WPB.PL_RESOURCE_OUT
, 0)
, 4
, NVL(WPB.PL_OUTSIDE_PROCESSING_OUT
, 0)
, 5
, NVL(WPB.PL_OVERHEAD_OUT
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, 0 + NVL(WPB.PL_MATERIAL_IN
, 0)
, 2
, 0 + NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0)
, 3
, NVL(WPB.TL_RESOURCE_IN
, 0) + NVL(PL_RESOURCE_IN
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0)+NVL(PL_OUTSIDE_PROCESSING_IN
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_IN
, 0) + NVL(PL_OVERHEAD_IN
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.TL_MATERIAL_OUT
, 0) + NVL(WPB.PL_MATERIAL_OUT
, 0)
, 2
, NVL(WPB.TL_MATERIAL_OVERHEAD_OUT
, 0)+NVL(WPB.PL_MATERIAL_OVERHEAD_OUT
, 0)
, 3
, NVL(WPB.TL_RESOURCE_OUT
, 0) + NVL(PL_RESOURCE_OUT
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_OUT
, 0)+NVL(PL_OUTSIDE_PROCESSING_OUT
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_OUT
, 0) + NVL(PL_OVERHEAD_OUT
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.TL_MATERIAL_VAR
, 0) + NVL(WPB.PL_MATERIAL_VAR
, 0)
, 2
, NVL(WPB.TL_MATERIAL_OVERHEAD_VAR
, 0)+NVL(WPB.PL_MATERIAL_OVERHEAD_VAR
, 0)
, 3
, NVL(WPB.TL_RESOURCE_VAR
, 0) + NVL(PL_RESOURCE_VAR
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_VAR
, 0)+NVL(PL_OUTSIDE_PROCESSING_VAR
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_VAR
, 0) + NVL(PL_OVERHEAD_VAR
, 0)
, 0))
, SUM( DECODE(CCE.COST_ELEMENT_ID
, 1
, 0 + NVL(WPB.PL_MATERIAL_IN
, 0)
, 2
, 0 + NVL(WPB.PL_MATERIAL_OVERHEAD_IN
, 0)
, 3
, NVL(WPB.TL_RESOURCE_IN
, 0) + NVL(PL_RESOURCE_IN
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_IN
, 0)+NVL(PL_OUTSIDE_PROCESSING_IN
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_IN
, 0) + NVL(PL_OVERHEAD_IN
, 0)
, 0) - DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.TL_MATERIAL_OUT
, 0) + NVL(WPB.PL_MATERIAL_OUT
, 0)
, 2
, NVL(WPB.TL_MATERIAL_OVERHEAD_OUT
, 0)+NVL(WPB.PL_MATERIAL_OVERHEAD_OUT
, 0)
, 3
, NVL(WPB.TL_RESOURCE_OUT
, 0) + NVL(PL_RESOURCE_OUT
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_OUT
, 0) + NVL(PL_OUTSIDE_PROCESSING_OUT
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_OUT
, 0) + NVL(PL_OVERHEAD_OUT
, 0)
, 0) - DECODE(CCE.COST_ELEMENT_ID
, 1
, NVL(WPB.TL_MATERIAL_VAR
, 0) + NVL(WPB.PL_MATERIAL_VAR
, 0)
, 2
, NVL(WPB.TL_MATERIAL_OVERHEAD_VAR
, 0)+NVL(WPB.PL_MATERIAL_OVERHEAD_VAR
, 0)
, 3
, NVL(WPB.TL_RESOURCE_VAR
, 0) + NVL(PL_RESOURCE_VAR
, 0)
, 4
, NVL(WPB.TL_OUTSIDE_PROCESSING_VAR
, 0) + NVL(PL_OUTSIDE_PROCESSING_VAR
, 0)
, 5
, NVL(WPB.TL_OVERHEAD_VAR
, 0) + NVL(PL_OVERHEAD_VAR
, 0)
, 0))
FROM WIP_PERIOD_BALANCES WPB
, WIP_DISCRETE_JOBS WDJ
, WIP_REPETITIVE_SCHEDULES WRS
, CST_COST_ELEMENTS CCE
WHERE WDJ.WIP_ENTITY_ID (+) = WPB.WIP_ENTITY_ID
AND WDJ.ORGANIZATION_ID (+) = WPB.ORGANIZATION_ID
AND WRS.REPETITIVE_SCHEDULE_ID (+) = WPB.REPETITIVE_SCHEDULE_ID
AND WRS.ORGANIZATION_ID (+) = WPB.ORGANIZATION_ID GROUP BY WPB.ACCT_PERIOD_ID
, WPB.WIP_ENTITY_ID
, WRS.LINE_ID
, WPB.ORGANIZATION_ID
, CCE.COST_ELEMENT_ID
, CCE.COST_ELEMENT
, DECODE(CCE.COST_ELEMENT_ID
, 1
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.MATERIAL_ACCOUNT
, WRS.MATERIAL_ACCOUNT)
, 2
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.MATERIAL_OVERHEAD_ACCOUNT
, WRS.MATERIAL_OVERHEAD_ACCOUNT)
, 3
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.RESOURCE_ACCOUNT
, WRS.RESOURCE_ACCOUNT)
, 4
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.OUTSIDE_PROCESSING_ACCOUNT
, WRS.OUTSIDE_PROCESSING_ACCOUNT)
, 5
, DECODE(WPB.REPETITIVE_SCHEDULE_ID
, NULL
, WDJ.OVERHEAD_ACCOUNT
, WRS.OVERHEAD_ACCOUNT)
, NULL)

Columns