CST_ROLLUP_COST_DTL

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_DETAIL_ID ROLLUP_HEADER_TYPE ROLLUP_HEADER_KEY WORK_DEFINITION_ID WORK_CENTER_ID WD_OPERATION_ID OUTPUT_INVENTORY_ITEM_ID RESOURCE_SEQ_NUMBER COST_COMPONENT_ID EXPENSE_POOL_ID SCENARIO_ID UNIT_COST EXTENDED_COST

Name

COST_DETAIL_ID

ROLLUP_HEADER_TYPE

ROLLUP_HEADER_KEY

WORK_DEFINITION_ID

WORK_CENTER_ID

WD_OPERATION_ID

OUTPUT_INVENTORY_ITEM_ID

RESOURCE_SEQ_NUMBER

COST_COMPONENT_ID

EXPENSE_POOL_ID

SCENARIO_ID

UNIT_COST

EXTENDED_COST

Query

SQL_Statement
SELECT csrh.SCENARIO_ROLLUP_HEADER_ID COST_DETAIL_ID, csrh.ROLLUP_HEADER_TYPE, csrh.ROLLUP_HEADER_KEY, (select distinct cwd.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd where cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID ) as WORK_DEFINITION_ID, null WORK_CENTER_ID, null WD_OPERATION_ID, 0 OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, cost.COST_COMPONENT_ID, cost.EXPENSE_POOL_ID, csrh.SCENARIO_ID, cost.UNIT_COST, cost.EXTENDED_COST FROM fusion.cst_scenario_rollup_headers csrh, fusion.CST_SCENARIOS cs, (select csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, sum(csrd.unit_cost) unit_cost, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.cst_scenario_rollup_details csrd group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID ) cost WHERE csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_LEVEL = 1 And cs.SCENARIO_ID = csrh.SCENARIO_ID union all SELECT csrh.SCENARIO_ROLLUP_HEADER_ID COST_DETAIL_ID, csrh.ROLLUP_HEADER_TYPE, csrh.ROLLUP_HEADER_KEY, (select distinct cwd.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd where cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID ) as WORK_DEFINITION_ID, null WORK_CENTER_ID, cwd.WD_OPERATION_ID WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, cost.COST_COMPONENT_ID, cost.EXPENSE_POOL_ID, csrh.SCENARIO_ID, (cost.UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE)) UNIT_COST , (cost.UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST FROM fusion.cst_scenario_rollup_headers csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_SCENARIOS cs, (select csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, sum(csrd.unit_cost) unit_cost, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.cst_scenario_rollup_details csrd group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID ) cost WHERE csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_LEVEL not in(1, 0) And cs.SCENARIO_ID = csrh.SCENARIO_ID And cwd.SCENARIO_ID = csrh.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID union all SELECT csrd.SCENARIO_ROLLUP_DETAIL_ID, csrh.rollup_header_type, csrh.rollup_header_key rollup_header_key, (SELECT DISTINCT cwd1.work_definition_id FROM fusion.cst_work_definitions cwd1 WHERE cwd1.output_inventory_item_id = csrh2.rollup_header_key AND cwd1.scenario_id = csrh2.scenario_id ) work_definition_id, NULL work_center_id, cwd.wd_operation_id, cwd.output_inventory_item_id, NULL resource_seq_number, csrd.cost_component_id, csrd.expense_pool_id, csrh.scenario_id, (csrd.unit_cost * cwd.material_scaled_quantity/DECODE(cwd.costing_batch_output_size,NULL,1,0,1,cwd.costing_batch_output_size)) unit_cost, (cwd.material_scaled_quantity * csrd.unit_cost) extended_cost FROM fusion.cst_scenario_rollup_headers csrh2, fusion.cst_scenario_rollup_details csrd, fusion.cst_scenario_rollup_headers csrh, fusion.cst_work_definitions cwd WHERE csrh2.rollup_header_key = csrh.rollup_header_key AND csrh.rollup_header_type = 'ITEM' AND csrh.rollup_header_level = 0 AND csrd.scenario_rollup_header_id = csrh2.scenario_rollup_header_id AND csrh.source_id = csrh2.scenario_rollup_header_id AND csrh.source_type = 'CST_SCENARIO_ROLLUP_HEADERS' AND cwd.scenario_id = csrh.scenario_id AND cwd.material_inventory_item_id = csrh2.rollup_header_key union all select cced.COST_ESTIMATE_DETAIL_ID, csrh.ROLLUP_HEADER_TYPE, csrh.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, (select distinct cwd1.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd1 where cwd1.OUTPUT_INVENTORY_ITEM_ID = ccel.INVENTORY_ITEM_ID and cwd1.OUTPUT_INVENTORY_ORG_ID = ccel.INVENTORY_ORG_ID and cwd1.SCENARIO_ID = csrh.SCENARIO_ID ) as WORK_DEFINITION_ID, null WORK_CENTER_ID, cwd.WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, cced.COST_COMPONENT_ID, cced.EXPENSE_POOL_ID, csrh.SCENARIO_ID, (cced.ESTIMATED_UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE)) UNIT_COST, (cwd.MATERIAL_SCALED_QUANTITY * cced.ESTIMATED_UNIT_COST) EXTENDED_COST from fusion.CST_COST_ESTIMATE_LINES ccel, fusion.CST_COST_ESTIMATE_DETAILS cced, fusion.CST_COST_ESTIMATE_ASSIGNS ccea, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd where ccel.INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and ccel.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID and csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 and ccel.COST_ESTIMATE_LINE_ID = ccea.COST_ESTIMATE_LINE_ID and csrh.SOURCE_ID = ccea.COST_ESTIMATE_ASSIGN_ID and csrh.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS' and cwd.SCENARIO_ID = csrh.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = ccel.INVENTORY_ITEM_ID union all select distinct crrd.RESOURCE_RATE_DETAIL_ID, csrh.ROLLUP_HEADER_TYPE, csrh.ROLLUP_HEADER_KEY, null work_definition_id, null WORK_CENTER_ID, cwd.WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, cwd.RESOURCE_SEQ_NUMBER, crrd.COST_COMPONENT_ID, crrd.EXPENSE_POOL_ID, csrh.SCENARIO_ID, (crrd.UNIT_RATE * cwd.RESOURCE_SCALED_USAGE_RATE/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) ) UNIT_COST , (cwd.RESOURCE_SCALED_USAGE_RATE * crrd.UNIT_RATE) EXTENDED_COST from fusion.CST_RESOURCE_RATES crr, fusion.CST_RESOURCE_RATE_DETAILS crrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_RESOURCES_VL wrv where crr.RESOURCE_ID = csrh.ROLLUP_HEADER_KEY and crr.RESOURCE_RATE_ID = crrd.RESOURCE_RATE_ID and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.ROLLUP_HEADER_TYPE = 'RESOURCE' and csrh.SOURCE_ID = crr.RESOURCE_RATE_ID and csrh.SOURCE_TYPE = 'CST_RESOURCE_RATES' and cwd.RESOURCE_ID = crr.RESOURCE_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and wrv.RESOURCE_ID = cwd.RESOURCE_ID union all select distinct cost.SCENARIO_ROLLUP_HEADER_ID, 'OPERATIONS' ROLLUP_HEADER_TYPE, csrh.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, cwd.work_definition_id, cwd.WORK_CENTER_ID WORK_CENTER_ID, cwd.WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, cost.COST_COMPONENT_ID, cost.EXPENSE_POOL_ID, csrh.SCENARIO_ID, cost.UNIT_COST, cost.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, (select csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, sum(csrd.unit_cost) unit_cost, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.cst_scenario_rollup_details csrd where nvl(csrd.OVERHEAD_FLAG,'N')='N' group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID ) cost where csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrh.ROLLUP_HEADER_KEY and cwd.SCENARIO_ID = csrh.SCENARIO_ID union all select a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID , a.WD_OPERATION_ID, a.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, sum(a.UNIT_COST) UNIT_COST, sum(a.EXTENDED_COST) EXTENDED_COST from (select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID, 'WORKCENTER' ROLLUP_HEADER_TYPE, cwd.WORK_CENTER_ID ROLLUP_HEADER_KEY, cwd.OUTPUT_INVENTORY_ORG_ID, cwd.work_definition_id, cwd.WORK_CENTER_ID WORK_CENTER_ID, 0 WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, cwd.SCENARIO_ID, csrd.UNIT_COST, csrd.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and nvl(csrd.OVERHEAD_FLAG, 'N') = 'N' and csrd.OVERHEAD_RATE_ID is null union select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID, 'WORKCENTER' ROLLUP_HEADER_TYPE, cwd.WORK_CENTER_ID ROLLUP_HEADER_KEY, cwd.OUTPUT_INVENTORY_ORG_ID, cwd.work_definition_id, cwd.WORK_CENTER_ID WORK_CENTER_ID, 0 WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, cwd.SCENARIO_ID, csrd.UNIT_COST, csrd.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_OVERHEAD_RATES cor where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and nvl(csrd.OVERHEAD_FLAG, 'N') = 'Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER' )a group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID, a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null, a.OUTPUT_INVENTORY_ITEM_ID union all select a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID , a.WD_OPERATION_ID, a.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, sum(a.UNIT_COST) UNIT_COST, sum(a.EXTENDED_COST) EXTENDED_COST from (select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID, 'OVERHEADS_'\|\|cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, 0 ROLLUP_HEADER_KEY, cwd.OUTPUT_INVENTORY_ORG_ID, cwd.work_definition_id, 0 WORK_CENTER_ID, cor.OVERHEAD_RATE_TYPE, 0 WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, cwd.SCENARIO_ID, csrd.UNIT_COST, csrd.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_OVERHEAD_RATES cor where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'PLANT' )a group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID, a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null, a.OUTPUT_INVENTORY_ITEM_ID union all select a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID , a.WD_OPERATION_ID, a.OUTPUT_INVENTORY_ITEM_ID, null RESOURCE_SEQ_NUMBER, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, sum(a.UNIT_COST) UNIT_COST, sum(a.EXTENDED_COST) EXTENDED_COST from (select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID, 'OVERHEADS_'\|\|cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, 0 ROLLUP_HEADER_KEY, cwd.OUTPUT_INVENTORY_ORG_ID, cwd.work_definition_id, cwd.WORK_CENTER_ID WORK_CENTER_ID, cor.OVERHEAD_RATE_TYPE, 0 WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, cwd.SCENARIO_ID, csrd.UNIT_COST, csrd.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_OVERHEAD_RATES cor where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER' union all select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID, 'OVERHEADS_'\|\|cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, 0 ROLLUP_HEADER_KEY, cwd.OUTPUT_INVENTORY_ORG_ID, cwd.work_definition_id, 0 WORK_CENTER_ID, cor.OVERHEAD_RATE_TYPE, 0 WD_OPERATION_ID, cwd.OUTPUT_INVENTORY_ITEM_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID, cwd.SCENARIO_ID, csrd.UNIT_COST, csrd.EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_OVERHEAD_RATES cor where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID and cwd.SCENARIO_ID = csrh.SCENARIO_ID and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER' )a group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID, a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null, a.OUTPUT_INVENTORY_ITEM_ID

SQL_Statement

SELECT

csrh.SCENARIO_ROLLUP_HEADER_ID COST_DETAIL_ID,

csrh.ROLLUP_HEADER_TYPE,

csrh.ROLLUP_HEADER_KEY,

(select distinct cwd.WORK_DEFINITION_ID

from fusion.CST_WORK_DEFINITIONS cwd

where cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY

and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

) as WORK_DEFINITION_ID,

null WORK_CENTER_ID,

null WD_OPERATION_ID,

0 OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

cost.COST_COMPONENT_ID,

cost.EXPENSE_POOL_ID,

csrh.SCENARIO_ID,

cost.UNIT_COST,

cost.EXTENDED_COST

FROM fusion.cst_scenario_rollup_headers csrh,

fusion.CST_SCENARIOS cs,

(select csrd.SCENARIO_ROLLUP_HEADER_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

sum(csrd.unit_cost) unit_cost,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.cst_scenario_rollup_details csrd

group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID

) cost

WHERE csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_LEVEL = 1

And cs.SCENARIO_ID = csrh.SCENARIO_ID

union all

SELECT

csrh.SCENARIO_ROLLUP_HEADER_ID COST_DETAIL_ID,

csrh.ROLLUP_HEADER_TYPE,

csrh.ROLLUP_HEADER_KEY,

(select distinct cwd.WORK_DEFINITION_ID

from fusion.CST_WORK_DEFINITIONS cwd

where cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY

and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

) as WORK_DEFINITION_ID,

null WORK_CENTER_ID,

cwd.WD_OPERATION_ID WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

cost.COST_COMPONENT_ID,

cost.EXPENSE_POOL_ID,

csrh.SCENARIO_ID,

(cost.UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE)) UNIT_COST ,

(cost.UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST

FROM fusion.cst_scenario_rollup_headers csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.CST_SCENARIOS cs,

(select csrd.SCENARIO_ROLLUP_HEADER_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

sum(csrd.unit_cost) unit_cost,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.cst_scenario_rollup_details csrd

group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID

) cost

WHERE csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_LEVEL not in(1, 0)

And cs.SCENARIO_ID = csrh.SCENARIO_ID

And cwd.SCENARIO_ID = csrh.SCENARIO_ID

and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY

and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID

union all

SELECT csrd.SCENARIO_ROLLUP_DETAIL_ID,

csrh.rollup_header_type,

csrh.rollup_header_key rollup_header_key,

(SELECT DISTINCT cwd1.work_definition_id

FROM fusion.cst_work_definitions cwd1

WHERE cwd1.output_inventory_item_id = csrh2.rollup_header_key

AND cwd1.scenario_id = csrh2.scenario_id

) work_definition_id,

NULL work_center_id,

cwd.wd_operation_id,

cwd.output_inventory_item_id,

NULL resource_seq_number,

csrd.cost_component_id,

csrd.expense_pool_id,

csrh.scenario_id,

(csrd.unit_cost * cwd.material_scaled_quantity/DECODE(cwd.costing_batch_output_size,NULL,1,0,1,cwd.costing_batch_output_size)) unit_cost,

(cwd.material_scaled_quantity * csrd.unit_cost) extended_cost

FROM fusion.cst_scenario_rollup_headers csrh2,

fusion.cst_scenario_rollup_details csrd,

fusion.cst_scenario_rollup_headers csrh,

fusion.cst_work_definitions cwd

WHERE csrh2.rollup_header_key = csrh.rollup_header_key

AND csrh.rollup_header_type = 'ITEM'

AND csrh.rollup_header_level = 0

AND csrd.scenario_rollup_header_id = csrh2.scenario_rollup_header_id

AND csrh.source_id = csrh2.scenario_rollup_header_id

AND csrh.source_type = 'CST_SCENARIO_ROLLUP_HEADERS'

AND cwd.scenario_id = csrh.scenario_id

AND cwd.material_inventory_item_id = csrh2.rollup_header_key

union all

select cced.COST_ESTIMATE_DETAIL_ID,

csrh.ROLLUP_HEADER_TYPE,

csrh.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

(select distinct cwd1.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd1

where cwd1.OUTPUT_INVENTORY_ITEM_ID = ccel.INVENTORY_ITEM_ID

and cwd1.OUTPUT_INVENTORY_ORG_ID = ccel.INVENTORY_ORG_ID

and cwd1.SCENARIO_ID = csrh.SCENARIO_ID

) as WORK_DEFINITION_ID,

null WORK_CENTER_ID,

cwd.WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

cced.COST_COMPONENT_ID,

cced.EXPENSE_POOL_ID,

csrh.SCENARIO_ID,

(cced.ESTIMATED_UNIT_COST * cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE)) UNIT_COST,

(cwd.MATERIAL_SCALED_QUANTITY * cced.ESTIMATED_UNIT_COST) EXTENDED_COST

from fusion.CST_COST_ESTIMATE_LINES ccel,

fusion.CST_COST_ESTIMATE_DETAILS cced,

fusion.CST_COST_ESTIMATE_ASSIGNS ccea,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd

where ccel.INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY

and ccel.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID

and csrh.ROLLUP_HEADER_TYPE = 'ITEM'

and csrh.ROLLUP_HEADER_LEVEL = 0

and ccel.COST_ESTIMATE_LINE_ID = ccea.COST_ESTIMATE_LINE_ID

and csrh.SOURCE_ID = ccea.COST_ESTIMATE_ASSIGN_ID

and csrh.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS'

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and cwd.MATERIAL_INVENTORY_ITEM_ID = ccel.INVENTORY_ITEM_ID

union all

select distinct crrd.RESOURCE_RATE_DETAIL_ID,

csrh.ROLLUP_HEADER_TYPE,

csrh.ROLLUP_HEADER_KEY,

null work_definition_id,

null WORK_CENTER_ID,

cwd.WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

cwd.RESOURCE_SEQ_NUMBER,

crrd.COST_COMPONENT_ID,

crrd.EXPENSE_POOL_ID,

csrh.SCENARIO_ID,

(crrd.UNIT_RATE * cwd.RESOURCE_SCALED_USAGE_RATE/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) ) UNIT_COST ,

(cwd.RESOURCE_SCALED_USAGE_RATE * crrd.UNIT_RATE) EXTENDED_COST

from fusion.CST_RESOURCE_RATES crr,

fusion.CST_RESOURCE_RATE_DETAILS crrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.WIS_RESOURCES_VL wrv

where crr.RESOURCE_ID = csrh.ROLLUP_HEADER_KEY

and crr.RESOURCE_RATE_ID = crrd.RESOURCE_RATE_ID

and csrh.ROLLUP_HEADER_LEVEL = 0

and csrh.ROLLUP_HEADER_TYPE = 'RESOURCE'

and csrh.SOURCE_ID = crr.RESOURCE_RATE_ID

and csrh.SOURCE_TYPE = 'CST_RESOURCE_RATES'

and cwd.RESOURCE_ID = crr.RESOURCE_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and wrv.RESOURCE_ID = cwd.RESOURCE_ID

union all

select distinct cost.SCENARIO_ROLLUP_HEADER_ID,

'OPERATIONS' ROLLUP_HEADER_TYPE,

csrh.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

cwd.work_definition_id,

cwd.WORK_CENTER_ID WORK_CENTER_ID,

cwd.WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

cost.COST_COMPONENT_ID,

cost.EXPENSE_POOL_ID,

csrh.SCENARIO_ID,

cost.UNIT_COST,

cost.EXTENDED_COST

from

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

(select csrd.SCENARIO_ROLLUP_HEADER_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

sum(csrd.unit_cost) unit_cost,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.cst_scenario_rollup_details csrd

where nvl(csrd.OVERHEAD_FLAG,'N')='N'

group by csrd.SCENARIO_ROLLUP_HEADER_ID, csrd.COST_COMPONENT_ID, csrd.EXPENSE_POOL_ID

) cost

where csrh.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrh.ROLLUP_HEADER_KEY

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

union all

select a.OUTPUT_INVENTORY_ORG_ID,

a.ROLLUP_HEADER_TYPE,

a.ROLLUP_HEADER_KEY,

a.work_definition_id,

a.WORK_CENTER_ID ,

a.WD_OPERATION_ID,

a.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

a.COST_COMPONENT_ID,

a.EXPENSE_POOL_ID,

a.SCENARIO_ID,

sum(a.UNIT_COST) UNIT_COST,

sum(a.EXTENDED_COST) EXTENDED_COST

from

(select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID,

'WORKCENTER' ROLLUP_HEADER_TYPE,

cwd.WORK_CENTER_ID ROLLUP_HEADER_KEY,

cwd.OUTPUT_INVENTORY_ORG_ID,

cwd.work_definition_id,

cwd.WORK_CENTER_ID WORK_CENTER_ID,

0 WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

cwd.SCENARIO_ID,

csrd.UNIT_COST,

csrd.EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd

where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and nvl(csrd.OVERHEAD_FLAG, 'N') = 'N'

and csrd.OVERHEAD_RATE_ID is null

union

select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID,

'WORKCENTER' ROLLUP_HEADER_TYPE,

cwd.WORK_CENTER_ID ROLLUP_HEADER_KEY,

cwd.OUTPUT_INVENTORY_ORG_ID,

cwd.work_definition_id,

cwd.WORK_CENTER_ID WORK_CENTER_ID,

0 WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

cwd.SCENARIO_ID,

csrd.UNIT_COST,

csrd.EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.CST_OVERHEAD_RATES cor

where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and nvl(csrd.OVERHEAD_FLAG, 'N') = 'Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER'

)a

group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID,

a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null,

a.OUTPUT_INVENTORY_ITEM_ID

union all

select a.OUTPUT_INVENTORY_ORG_ID,

a.ROLLUP_HEADER_TYPE,

a.ROLLUP_HEADER_KEY,

a.work_definition_id,

a.WORK_CENTER_ID ,

a.WD_OPERATION_ID,

a.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

a.COST_COMPONENT_ID,

a.EXPENSE_POOL_ID,

a.SCENARIO_ID,

sum(a.UNIT_COST) UNIT_COST,

sum(a.EXTENDED_COST) EXTENDED_COST

from

(select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID,

'OVERHEADS_'||cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

0 ROLLUP_HEADER_KEY,

cwd.OUTPUT_INVENTORY_ORG_ID,

cwd.work_definition_id,

0 WORK_CENTER_ID,

cor.OVERHEAD_RATE_TYPE,

0 WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

cwd.SCENARIO_ID,

csrd.UNIT_COST,

csrd.EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.CST_OVERHEAD_RATES cor

where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'PLANT'

)a

group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID,

a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null,

a.OUTPUT_INVENTORY_ITEM_ID

union all

select a.OUTPUT_INVENTORY_ORG_ID,

a.ROLLUP_HEADER_TYPE,

a.ROLLUP_HEADER_KEY,

a.work_definition_id,

a.WORK_CENTER_ID ,

a.WD_OPERATION_ID,

a.OUTPUT_INVENTORY_ITEM_ID,

null RESOURCE_SEQ_NUMBER,

a.COST_COMPONENT_ID,

a.EXPENSE_POOL_ID,

a.SCENARIO_ID,

sum(a.UNIT_COST) UNIT_COST,

sum(a.EXTENDED_COST) EXTENDED_COST

from

(select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID,

'OVERHEADS_'||cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

0 ROLLUP_HEADER_KEY,

cwd.OUTPUT_INVENTORY_ORG_ID,

cwd.work_definition_id,

cwd.WORK_CENTER_ID WORK_CENTER_ID,

cor.OVERHEAD_RATE_TYPE,

0 WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

cwd.SCENARIO_ID,

csrd.UNIT_COST,

csrd.EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.CST_OVERHEAD_RATES cor

where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER'

union all

select distinct csrd.SCENARIO_ROLLUP_DETAIL_ID,

'OVERHEADS_'||cor.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

0 ROLLUP_HEADER_KEY,

cwd.OUTPUT_INVENTORY_ORG_ID,

cwd.work_definition_id,

0 WORK_CENTER_ID,

cor.OVERHEAD_RATE_TYPE,

0 WD_OPERATION_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID,

csrd.COST_COMPONENT_ID,

csrd.EXPENSE_POOL_ID,

cwd.SCENARIO_ID,

csrd.UNIT_COST,

csrd.EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_WORK_DEFINITIONS cwd,

fusion.CST_OVERHEAD_RATES cor

where csrd.SCENARIO_ROLLUP_HEADER_ID = csrh.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and cwd.WD_OPERATION_ID = csrd.WD_OPERATION_ID

and cwd.SCENARIO_ID = csrh.SCENARIO_ID

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER'

)a

group by a.OUTPUT_INVENTORY_ORG_ID, a.ROLLUP_HEADER_TYPE, a.ROLLUP_HEADER_KEY, a.work_definition_id, a.WORK_CENTER_ID,

a.WD_OPERATION_ID, a.COST_COMPONENT_ID, a.EXPENSE_POOL_ID, a.SCENARIO_ID, null,

a.OUTPUT_INVENTORY_ITEM_ID