CST_ROLLUP_OVERHEADS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
NODE_NAME ROLLUP_HEADER_KEY ROLLUP_HEADER_TYPE WORK_DEFINITION_NAME WORK_DEFINITION_ID PARENT_WD_ID PARENT_WO_ID PARENT_ITEM_ID WORK_CENTER_ID WORK_CENTER_CODE USE_WORK_CENTER_FLAG INV_ORG_ID SCENARIO_ID SCENARIO_EVENT_ID UNIT_COST EXTENDED_COST

Name

NODE_NAME

ROLLUP_HEADER_KEY

ROLLUP_HEADER_TYPE

WORK_DEFINITION_NAME

WORK_DEFINITION_ID

PARENT_WD_ID

PARENT_WO_ID

PARENT_ITEM_ID

WORK_CENTER_ID

WORK_CENTER_CODE

USE_WORK_CENTER_FLAG

INV_ORG_ID

SCENARIO_ID

SCENARIO_EVENT_ID

UNIT_COST

EXTENDED_COST

Query

SQL_Statement
select oh.node_name node_name, 0 ROLLUP_HEADER_KEY, oh.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE, oh.WORK_DEFINITION_NAME, oh.WORK_DEFINITION_ID WORK_DEFINITION_ID , oh.WORK_DEFINITION_ID PARENT_WD_ID, null PARENT_WO_ID, oh.PARENT_ITEM_ID, oh.WORK_CENTER_ID WORK_CENTER_ID, oh.WORK_CENTER_CODE WORK_CENTER_CODE, oh.USE_WORK_CENTER_FLAG, oh.INV_ORG_ID, oh.SCENARIO_ID, oh.SCENARIO_EVENT_ID, sum(oh.unit_cost) unit_cost, sum(oh.extended_cost) extended_cost from (select distinct 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY, 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID , wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, cwd.WD_OPERATION_ID, cwd.WORK_DEFINITION_ID PARENT_WD_ID, cwd.WD_OPERATION_ID PARENT_WO_ID, cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID, cwd.OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, 0 WORK_CENTER_ID, null WORK_CENTER_CODE, cwd.SCENARIO_ID, cost.SCENARIO_EVENT_ID, cost.unit_cost, cost.extended_cost from fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE, sum(csrd.UNIT_COST) UNIT_COST, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_OVERHEAD_RATES cor where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE ) cost where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY and cwd.SCENARIO_ID = cost.SCENARIO_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID union all select distinct 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY, 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID , wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, cwd.WD_OPERATION_ID, cwd.WORK_DEFINITION_ID PARENT_WD_ID, cwd.WD_OPERATION_ID PARENT_WO_ID, cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID, cwd.OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, cwd.WORK_CENTER_ID, (select WORK_CENTER_NAME from fusion.WIS_WORK_CENTERS_VL where WORK_CENTER_ID = cwd.WORK_CENTER_ID) as WORK_CENTER_CODE, cwd.SCENARIO_ID, cost.SCENARIO_EVENT_ID, cost.unit_cost, cost.extended_cost from fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE, sum(csrd.UNIT_COST) UNIT_COST, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_OVERHEAD_RATES cor where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE ) cost where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY and cwd.SCENARIO_ID = cost.SCENARIO_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID union all select distinct 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY, 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID , wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, cwd.WD_OPERATION_ID, cwd.WORK_DEFINITION_ID PARENT_WD_ID, cwd.WD_OPERATION_ID PARENT_WO_ID, cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID, cwd.OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, 0 WORK_CENTER_ID, null WORK_CENTER_CODE, cwd.SCENARIO_ID, cost.SCENARIO_EVENT_ID, cost.unit_cost, cost.extended_cost from fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE, sum(csrd.UNIT_COST) UNIT_COST, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_OVERHEAD_RATES cor where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'PLANT' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE ) cost where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY and cwd.SCENARIO_ID = cost.SCENARIO_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID union all select distinct 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY, 'OVERHEADS_'\|\|cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE, cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID , wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, cwd.WD_OPERATION_ID, cwd.WORK_DEFINITION_ID PARENT_WD_ID, cwd.WD_OPERATION_ID PARENT_WO_ID, cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID, cwd.OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, 0 WORK_CENTER_ID, null WORK_CENTER_CODE, cwd.SCENARIO_ID, cost.SCENARIO_EVENT_ID, cost.unit_cost, cost.extended_cost from fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE, sum(csrd.UNIT_COST) UNIT_COST, sum(csrd.EXTENDED_COST) EXTENDED_COST from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd, fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_OVERHEAD_RATES cor where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and csrd.OVERHEAD_FLAG='Y' and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID and cor.OVERHEAD_RATE_TYPE = 'PLANT' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE ) cost where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY and cwd.SCENARIO_ID = cost.SCENARIO_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) oh group by oh.node_name, 0, oh.ROLLUP_HEADER_TYPE, oh.WORK_DEFINITION_NAME, oh.WORK_DEFINITION_ID, oh.WORK_DEFINITION_ID, null, oh.WORK_CENTER_ID, oh.WORK_CENTER_CODE, oh.USE_WORK_CENTER_FLAG, oh.INV_ORG_ID, oh.SCENARIO_ID, oh.SCENARIO_EVENT_ID, oh.PARENT_ITEM_ID

SQL_Statement

select oh.node_name node_name,

0 ROLLUP_HEADER_KEY,

oh.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE,

oh.WORK_DEFINITION_NAME,

oh.WORK_DEFINITION_ID WORK_DEFINITION_ID ,

oh.WORK_DEFINITION_ID PARENT_WD_ID,

null PARENT_WO_ID,

oh.PARENT_ITEM_ID,

oh.WORK_CENTER_ID WORK_CENTER_ID,

oh.WORK_CENTER_CODE WORK_CENTER_CODE,

oh.USE_WORK_CENTER_FLAG,

oh.INV_ORG_ID,

oh.SCENARIO_ID,

oh.SCENARIO_EVENT_ID,

sum(oh.unit_cost) unit_cost,

sum(oh.extended_cost) extended_cost

from

(select distinct

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE node_name,

cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID,

cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY,

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID ,

wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME,

cwd.WD_OPERATION_ID,

cwd.WORK_DEFINITION_ID PARENT_WD_ID,

cwd.WD_OPERATION_ID PARENT_WO_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID,

cwd.OPERATION_SEQ_NUMBER,

'N' USE_WORK_CENTER_FLAG,

0 WORK_CENTER_ID,

null WORK_CENTER_CODE,

cwd.SCENARIO_ID,

cost.SCENARIO_EVENT_ID,

cost.unit_cost,

cost.extended_cost

from fusion.CST_WORK_DEFINITIONS cwd,

fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv,

(select csrh.ROLLUP_HEADER_KEY,

csrd.WD_OPERATION_ID,

csrh.SCENARIO_ID,

csrh.SCENARIO_EVENT_ID,

cor.OVERHEAD_RATE_TYPE,

sum(csrd.UNIT_COST) UNIT_COST,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_OVERHEAD_RATES cor

where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER'

group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE

) cost

where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID

and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY

and cwd.SCENARIO_ID = cost.SCENARIO_ID

and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

union all

select distinct

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE node_name,

cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID,

cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY,

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID ,

wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME,

cwd.WD_OPERATION_ID,

cwd.WORK_DEFINITION_ID PARENT_WD_ID,

cwd.WD_OPERATION_ID PARENT_WO_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID,

cwd.OPERATION_SEQ_NUMBER,

'Y' USE_WORK_CENTER_FLAG,

cwd.WORK_CENTER_ID,

(select WORK_CENTER_NAME from fusion.WIS_WORK_CENTERS_VL where WORK_CENTER_ID = cwd.WORK_CENTER_ID) as WORK_CENTER_CODE,

cwd.SCENARIO_ID,

cost.SCENARIO_EVENT_ID,

cost.unit_cost,

cost.extended_cost

from fusion.CST_WORK_DEFINITIONS cwd,

fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv,

(select csrh.ROLLUP_HEADER_KEY,

csrd.WD_OPERATION_ID,

csrh.SCENARIO_ID,

csrh.SCENARIO_EVENT_ID,

cor.OVERHEAD_RATE_TYPE,

sum(csrd.UNIT_COST) UNIT_COST,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_OVERHEAD_RATES cor

where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'WORKCENTER'

group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE

) cost

where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID

and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY

and cwd.SCENARIO_ID = cost.SCENARIO_ID

and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

union all

select distinct

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE node_name,

cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID,

cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY,

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID ,

wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME,

cwd.WD_OPERATION_ID,

cwd.WORK_DEFINITION_ID PARENT_WD_ID,

cwd.WD_OPERATION_ID PARENT_WO_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID,

cwd.OPERATION_SEQ_NUMBER,

'N' USE_WORK_CENTER_FLAG,

0 WORK_CENTER_ID,

null WORK_CENTER_CODE,

cwd.SCENARIO_ID,

cost.SCENARIO_EVENT_ID,

cost.unit_cost,

cost.extended_cost

from fusion.CST_WORK_DEFINITIONS cwd,

fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv,

(select csrh.ROLLUP_HEADER_KEY,

csrd.WD_OPERATION_ID,

csrh.SCENARIO_ID,

csrh.SCENARIO_EVENT_ID,

cor.OVERHEAD_RATE_TYPE,

sum(csrd.UNIT_COST) UNIT_COST,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_OVERHEAD_RATES cor

where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'PLANT'

group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE

) cost

where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID

and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY

and cwd.SCENARIO_ID = cost.SCENARIO_ID

and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

union all

select distinct

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE node_name,

cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID,

cwd.WD_OPERATION_ID ROLLUP_HEADER_KEY,

'OVERHEADS_'||cost.OVERHEAD_RATE_TYPE ROLLUP_HEADER_TYPE,

cwd.WORK_DEFINITION_ID WORK_DEFINITION_ID ,

wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME,

cwd.WD_OPERATION_ID,

cwd.WORK_DEFINITION_ID PARENT_WD_ID,

cwd.WD_OPERATION_ID PARENT_WO_ID,

cwd.OUTPUT_INVENTORY_ITEM_ID PARENT_ITEM_ID,

cwd.OPERATION_SEQ_NUMBER,

'Y' USE_WORK_CENTER_FLAG,

0 WORK_CENTER_ID,

null WORK_CENTER_CODE,

cwd.SCENARIO_ID,

cost.SCENARIO_EVENT_ID,

cost.unit_cost,

cost.extended_cost

from fusion.CST_WORK_DEFINITIONS cwd,

fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv,

(select csrh.ROLLUP_HEADER_KEY,

csrd.WD_OPERATION_ID,

csrh.SCENARIO_ID,

csrh.SCENARIO_EVENT_ID,

cor.OVERHEAD_RATE_TYPE,

sum(csrd.UNIT_COST) UNIT_COST,

sum(csrd.EXTENDED_COST) EXTENDED_COST

from fusion.CST_SCENARIO_ROLLUP_DETAILS csrd,

fusion.CST_SCENARIO_ROLLUP_HEADERS csrh,

fusion.CST_OVERHEAD_RATES cor

where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and csrd.OVERHEAD_FLAG='Y'

and cor.OVERHEAD_RATE_ID = csrd.OVERHEAD_RATE_ID

and cor.OVERHEAD_RATE_TYPE = 'PLANT'

group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, cor.OVERHEAD_RATE_TYPE

) cost

where cwd.WD_OPERATION_ID = cost.WD_OPERATION_ID

and cwd.WD_OPERATION_ID = cost.ROLLUP_HEADER_KEY

and cwd.SCENARIO_ID = cost.SCENARIO_ID

and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

) oh

group by oh.node_name, 0, oh.ROLLUP_HEADER_TYPE, oh.WORK_DEFINITION_NAME, oh.WORK_DEFINITION_ID,

oh.WORK_DEFINITION_ID, null, oh.WORK_CENTER_ID, oh.WORK_CENTER_CODE, oh.USE_WORK_CENTER_FLAG,

oh.INV_ORG_ID, oh.SCENARIO_ID, oh.SCENARIO_EVENT_ID, oh.PARENT_ITEM_ID