CST_REVIEW_ROLLUP_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
NODE_NAME PARENT_WD_KEY PARENT_WO_KEY PARENT_WC_KEY PARENT_ITEM_KEY PARENT_TYPE CHILD_WD_KEY CHILD_WO_KEY CHILD_WC_KEY CHILD_ITEM_KEY CHILD_TYPE SCENARIO_VERSION EFFECTIVE_START_DATE ORGANIZATION_CODE INV_ORG_NAME INV_ORG_ID SCENARIO_ID SCENARIO_EVENT_ID ROLLUP_HEADER_LEVEL INVENTORY_ITEM_ID RESOURCE_ID ROLLUP_HEADER_KEY ROLLUP_HEADER_TYPE WORK_DEFINITION_NAME YIELD_FACTOR UNIT_RATE QUANTITY_USAGE COSTING_BATCH_OUTPUT_SIZE QTY_UOM_CODE PARENT_WO_ID PARENT_WD_ID WORK_DEFINITION_ID WD_OPERATION_ID OPERATION_SEQ_NUMBER RESOURCE_SEQ_NUMBER USE_WORK_CENTER_FLAG TOPITEM_SUCCESS_FLAG TOPITEM_FAILURE_FLAG WORK_CENTER_ID WORK_CENTER_CODE ROLLUP_STATUS_CODE UPDATE_FLAG STATUS_MESSAGE UNIT_COST EXTENDED_COST

Name

NODE_NAME

PARENT_WD_KEY

PARENT_WO_KEY

PARENT_WC_KEY

PARENT_ITEM_KEY

PARENT_TYPE

CHILD_WD_KEY

CHILD_WO_KEY

CHILD_WC_KEY

CHILD_ITEM_KEY

CHILD_TYPE

SCENARIO_VERSION

EFFECTIVE_START_DATE

ORGANIZATION_CODE

INV_ORG_NAME

INV_ORG_ID

SCENARIO_ID

SCENARIO_EVENT_ID

ROLLUP_HEADER_LEVEL

INVENTORY_ITEM_ID

RESOURCE_ID

ROLLUP_HEADER_KEY

ROLLUP_HEADER_TYPE

WORK_DEFINITION_NAME

YIELD_FACTOR

UNIT_RATE

QUANTITY_USAGE

COSTING_BATCH_OUTPUT_SIZE

QTY_UOM_CODE

PARENT_WO_ID

PARENT_WD_ID

WORK_DEFINITION_ID

WD_OPERATION_ID

OPERATION_SEQ_NUMBER

RESOURCE_SEQ_NUMBER

USE_WORK_CENTER_FLAG

TOPITEM_SUCCESS_FLAG

TOPITEM_FAILURE_FLAG

WORK_CENTER_ID

WORK_CENTER_CODE

ROLLUP_STATUS_CODE

UPDATE_FLAG

STATUS_MESSAGE

UNIT_COST

EXTENDED_COST

Query

SQL_Statement
SELECT distinct cs.SCENARIO_NUMBER node_name, to_number(0) parent_wd_key, to_number(0) parent_wo_key, to_number(0) parent_wc_key, to_number(0) parent_item_key, to_char(null) parent_type, cse.SCENARIO_ID child_wd_key, to_number(0) child_wo_key, to_number(0) child_wc_key, to_number(0) child_item_key, 'SCENARIO' child_type, 'Version:' \|\| TO_CHAR(cs.effective_start_date, 'DD-MON-YYYY') scenario_version, cs.effective_start_date effective_start_date, iop.organization_code organization_code, hr.name inv_org_name, cs.INV_ORG_ID INV_ORG_ID, cs.SCENARIO_ID SCENARIO_ID, cse.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, 0 ROLLUP_HEADER_LEVEL, to_number(null) INVENTORY_ITEM_ID, to_number(null) RESOURCE_ID, cs.SCENARIO_ID ROLLUP_HEADER_KEY, 'SCENARIO' ROLLUP_HEADER_TYPE, to_char(null) WORK_DEFINITION_NAME, to_number(null) YIELD_FACTOR, to_number(null) UNIT_RATE, to_number(null) QUANTITY_USAGE, to_number(null) COSTING_BATCH_OUTPUT_SIZE, to_char(null) QTY_UOM_CODE, to_number(null) parent_wo_id, to_number(null) parent_wd_id, to_number(null) WORK_DEFINITION_ID, to_number(null) WD_OPERATION_ID, to_number(null) OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, to_number(null) WORK_CENTER_ID, to_char(null) WORK_CENTER_CODE, to_char(null) ROLLUP_STATUS_CODE, to_char(null) UPDATE_FLAG, to_char(null) STATUS_MESSAGE, to_number(null) UNIT_COST, to_number(null) EXTENDED_COST FROM fusion.cst_scenarios cs, fusion.hr_organization_units_f_tl hr, fusion.inv_org_parameters iop, fusion.CST_SCENARIO_EVENTS cse WHERE cs.INV_ORG_ID = hr.ORGANIZATION_ID AND cs.INV_ORG_ID = iop.ORGANIZATION_ID AND cse.SCENARIO_ID = cs.SCENARIO_ID AND hr.LANGUAGE = USERENV('LANG') AND exists (select 1 from fusion.cst_scenario_rollup_headers csrh where csrh.SCENARIO_EVENT_ID = cse.SCENARIO_EVENT_ID) union all SELECT distinct cs.SCENARIO_NUMBER node_name, to_number(0) parent_wd_key, to_number(0) parent_wo_key, to_number(0) parent_wc_key, to_number(0) parent_item_key, to_char(null) parent_type, cse.SCENARIO_ID child_wd_key, to_number(0) child_wo_key, to_number(0) child_wc_key, to_number(0) child_item_key, 'SCENARIO' child_type, 'Version:' \|\| TO_CHAR(cs.effective_start_date, 'DD-MON-YYYY') scenario_version, cs.effective_start_date effective_start_date, iop.organization_code organization_code, hr.name inv_org_name, cs.INV_ORG_ID INV_ORG_ID, cs.SCENARIO_ID SCENARIO_ID, cse.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, 0 ROLLUP_HEADER_LEVEL, to_number(null) INVENTORY_ITEM_ID, to_number(null) RESOURCE_ID, cs.SCENARIO_ID ROLLUP_HEADER_KEY, 'SCENARIO' ROLLUP_HEADER_TYPE, to_char(null) WORK_DEFINITION_NAME, to_number(null) YIELD_FACTOR, to_number(null) UNIT_RATE, to_number(null) QUANTITY_USAGE, to_number(null) COSTING_BATCH_OUTPUT_SIZE, to_char(null) QTY_UOM_CODE, to_number(null) parent_wo_id, to_number(null) parent_wd_id, to_number(null) WORK_DEFINITION_ID, to_number(null) WD_OPERATION_ID, to_number(null) OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, to_number(null) WORK_CENTER_ID, to_char(null) WORK_CENTER_CODE, to_char(null) ROLLUP_STATUS_CODE, to_char(null) UPDATE_FLAG, to_char(null) STATUS_MESSAGE, to_number(null) UNIT_COST, to_number(null) EXTENDED_COST FROM fusion.cst_scenarios cs, fusion.hr_organization_units_f_tl hr, fusion.inv_org_parameters iop, fusion.CST_SCENARIO_EVENTS cse WHERE cs.INV_ORG_ID = hr.ORGANIZATION_ID AND cs.INV_ORG_ID = iop.ORGANIZATION_ID AND cse.SCENARIO_ID = cs.SCENARIO_ID AND hr.LANGUAGE = USERENV('LANG') AND exists (select 1 from fusion.cst_scenario_rollup_headers csrh where csrh.SCENARIO_EVENT_ID = cse.SCENARIO_EVENT_ID) union all select crcv.node_name, decode(crcv.ROLLUP_HEADER_LEVEL, 1, crcv.SCENARIO_ID, crcv.parent_wd_id) parent_wd_key, decode(crcv.ROLLUP_HEADER_LEVEL, 1, 0, crcv.parent_wo_id) parent_wo_key, decode(crcv.ROLLUP_HEADER_LEVEL, 1,0, decode(crcv.USE_WORK_CENTER_FLAG, 'Y', crcv.WORK_CENTER_ID,0)) parent_wc_key, decode(crcv.ROLLUP_HEADER_LEVEL, 1, 0, crcv.parent_item_id) parent_item_key, decode(crcv.ROLLUP_HEADER_LEVEL, 1, 'SCENARIO', 'OPERATIONS') parent_type, to_number(decode(crcv.ROLLUP_HEADER_LEVEL, 0, crcv.ROLLUP_HEADER_KEY, crcv.WORK_DEFINITION_ID)) child_wd_key, to_number(0) child_wo_key, to_number(0) child_wc_key, crcv.inventory_item_id child_item_key, decode(crcv.ROLLUP_HEADER_LEVEL, 0, crcv.ROLLUP_HEADER_TYPE, decode(crcv.USE_WORK_CENTER_FLAG, 'Y', 'WORKCENTER','DEFINITION')) child_type, to_char(null) scenario_version, to_date(null) effective_start_date, to_char(null) organization_code, to_char(null) inv_org_name, crcv.INV_ORG_ID, crcv.SCENARIO_ID SCENARIO_ID, crcv.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, crcv.ROLLUP_HEADER_LEVEL ROLLUP_HEADER_LEVEL, crcv.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, crcv.RESOURCE_ID RESOURCE_ID, crcv.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, crcv.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE, crcv.WORK_DEFINITION_NAME WORK_DEFINITION_NAME, crcv.YIELD_FACTOR YIELD_FACTOR, crcv.UNIT_RATE UNIT_RATE, crcv.QUANTITY_USAGE QUANTITY_USAGE, crcv.COSTING_BATCH_OUTPUT_SIZE COSTING_BATCH_OUTPUT_SIZE, crcv.QTY_UOM_CODE QTY_UOM_CODE, crcv.parent_wo_id parent_wo_id, crcv.parent_wd_id parent_wd_id, crcv.WORK_DEFINITION_ID WORK_DEFINITION_ID, crcv.parent_wo_id WD_OPERATION_ID, crcv.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, crcv.RESOURCE_SEQ_NUMBER RESOURCE_SEQ_NUMBER, crcv.USE_WORK_CENTER_FLAG, crcv.TOPITEM_SUCCESS_FLAG, crcv.TOPITEM_FAILURE_FLAG, null WORK_CENTER_ID, crcv.WORK_CENTER_CODE, crcv.STATUS_CODE ROLLUP_STATUS_CODE, crcv.UPDATE_FLAG UPDATE_FLAG, crcv.STATUS_MESSAGE STATUS_MESSAGE, crcv.UNIT_COST UNIT_COST, crcv.EXTENDED_COST EXTENDED_COST from fusion.CST_ROLLUP_COSTS_V crcv union all select crov.node_name, crov.PARENT_WD_ID parent_wd_key, to_number(0) parent_wo_key, decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) parent_wc_key, crov.parent_item_id parent_item_key, 'DEFINITION' parent_type, crov.PARENT_WD_ID child_wd_key, crov.ROLLUP_HEADER_KEY child_wo_key, decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) child_wc_key, crov.inventory_item_id child_item_key, crov.ROLLUP_HEADER_TYPE child_type, to_char(null) scenario_version, to_date(null) effective_start_date, to_char(null) organization_code, to_char(null) inv_org_name, crov.INV_ORG_ID INV_ORG_ID, crov.SCENARIO_ID SCENARIO_ID, crov.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, to_number(null) ROLLUP_HEADER_LEVEL, crov.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, to_number(null) RESOURCE_ID, crov.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, crov.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE, crov.WORK_DEFINITION_NAME, to_number(null) YIELD_FACTOR, to_number(null) UNIT_RATE, to_number(null) QUANTITY_USAGE, to_number(null) COSTING_BATCH_OUTPUT_SIZE, to_char(null) QTY_UOM_CODE, crov.PARENT_WO_ID PARENT_WO_ID, crov.PARENT_WD_ID PARENT_WD_ID, crov.PARENT_WD_ID WORK_DEFINITION_ID, crov.WD_OPERATION_ID WD_OPERATION_ID, crov.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, crov.USE_WORK_CENTER_FLAG, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, crov.WORK_CENTER_ID, crov.WORK_CENTER_CODE, to_char(null) ROLLUP_STATUS_CODE, to_char(null) UPDATE_FLAG, to_char(null) STATUS_MESSAGE, crov.unit_cost unit_cost, crov.extended_cost extended_cost from fusion.CST_ROLLUP_OPERATIONS_V crov union all select crwv.node_name, crwv.PARENT_WD_ID parent_wd_key, to_number(0) parent_wo_key, to_number(0) parent_wc_key, crwv.parent_item_id parent_item_key, 'WORKCENTER' parent_type, crwv.PARENT_WD_ID child_wd_key, to_number(0) child_wo_key, crwv.WORK_CENTER_ID child_wc_key, crwv.inventory_item_id child_item_key, 'DEFINITION' child_type, to_char(null) scenario_version, to_date(null) effective_start_date, to_char(null) organization_code, to_char(null) inv_org_name, to_number(null) INV_ORG_ID, crwv.SCENARIO_ID SCENARIO_ID, crwv.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, to_number(null) ROLLUP_HEADER_LEVEL, to_number(null)INVENTORY_ITEM_ID, to_number(null) RESOURCE_ID, crwv.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, crwv.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE, to_char(crwv.WORK_DEFINITION_NAME), to_number(null) YIELD_FACTOR, to_number(null) UNIT_RATE, to_number(null) QUANTITY_USAGE, to_number(null) COSTING_BATCH_OUTPUT_SIZE, to_char(null) QTY_UOM_CODE, crwv.PARENT_WD_ID PARENT_WD_ID, crwv.PARENT_WO_ID PARENT_WO_ID, crwv.PARENT_WD_ID WORK_DEFINITION_ID, to_number(null) WD_OPERATION_ID, to_number(null) OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, crwv.WORK_CENTER_ID, crwv.WORK_CENTER_CODE, to_char(null) ROLLUP_STATUS_CODE, to_char(null) UPDATE_FLAG, to_char(null) STATUS_MESSAGE, crwv.unit_cost unit_cost, crwv.extended_cost extended_cost from fusion.CST_ROLLUP_WORKCENTERS_V crwv union all select crov.node_name, crov.PARENT_WD_ID parent_wd_key, to_number(0) parent_wo_key, decode(crov.ROLLUP_HEADER_TYPE,'OVERHEADS_PLANT',decode(crov.USE_WORK_CENTER_FLAG, 'Y',0, crov.WORK_CENTER_ID),decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0)) parent_wc_key, crov.parent_item_id parent_item_key, decode(crov.ROLLUP_HEADER_TYPE,'OVERHEADS_PLANT',decode(crov.USE_WORK_CENTER_FLAG, 'Y','WORKCENTER','DEFINITION'),'DEFINITION') parent_type, crov.PARENT_WD_ID child_wd_key, crov.ROLLUP_HEADER_KEY child_wo_key, decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) child_wc_key, to_number(0) child_item_key, crov.ROLLUP_HEADER_TYPE child_type, to_char(null) scenario_version, to_date(null) effective_start_date, to_char(null) organization_code, to_char(null) inv_org_name, crov.INV_ORG_ID INV_ORG_ID, crov.SCENARIO_ID SCENARIO_ID, crov.SCENARIO_EVENT_ID SCENARIO_EVENT_ID, to_number(0) ROLLUP_HEADER_LEVEL, to_number(null)INVENTORY_ITEM_ID, to_number(null) RESOURCE_ID, crov.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY, crov.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE, to_char(crov.WORK_DEFINITION_NAME), to_number(null) YIELD_FACTOR, to_number(null) UNIT_RATE, to_number(null) QUANTITY_USAGE, to_number(null) COSTING_BATCH_OUTPUT_SIZE, to_char(null) QTY_UOM_CODE, crov.PARENT_WD_ID PARENT_WD_ID, crov.PARENT_WO_ID PARENT_WO_ID, crov.PARENT_WD_ID WORK_DEFINITION_ID, to_number(null) WD_OPERATION_ID, to_number(null) OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, crov.USE_WORK_CENTER_FLAG USE_WORK_CENTER_FLAG, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, crov.WORK_CENTER_ID, crov.WORK_CENTER_CODE, to_char(null) ROLLUP_STATUS_CODE, to_char(null) UPDATE_FLAG, to_char(null) STATUS_MESSAGE, crov.unit_cost unit_cost, crov.extended_cost extended_cost from fusion.CST_ROLLUP_OVERHEADS_V crov

SQL_Statement

SELECT distinct

cs.SCENARIO_NUMBER node_name,

to_number(0) parent_wd_key,

to_number(0) parent_wo_key,

to_number(0) parent_wc_key,

to_number(0) parent_item_key,

to_char(null) parent_type,

cse.SCENARIO_ID child_wd_key,

to_number(0) child_wo_key,

to_number(0) child_wc_key,

to_number(0) child_item_key,

'SCENARIO' child_type,

'Version:' || TO_CHAR(cs.effective_start_date, 'DD-MON-YYYY') scenario_version,

cs.effective_start_date effective_start_date,

iop.organization_code organization_code,

hr.name inv_org_name,

cs.INV_ORG_ID INV_ORG_ID,

cs.SCENARIO_ID SCENARIO_ID,

cse.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

0 ROLLUP_HEADER_LEVEL,

to_number(null) INVENTORY_ITEM_ID,

to_number(null) RESOURCE_ID,

cs.SCENARIO_ID ROLLUP_HEADER_KEY,

'SCENARIO' ROLLUP_HEADER_TYPE,

to_char(null) WORK_DEFINITION_NAME,

to_number(null) YIELD_FACTOR,

to_number(null) UNIT_RATE,

to_number(null) QUANTITY_USAGE,

to_number(null) COSTING_BATCH_OUTPUT_SIZE,

to_char(null) QTY_UOM_CODE,

to_number(null) parent_wo_id,

to_number(null) parent_wd_id,

to_number(null) WORK_DEFINITION_ID,

to_number(null) WD_OPERATION_ID,

to_number(null) OPERATION_SEQ_NUMBER,

null RESOURCE_SEQ_NUMBER,

'N' USE_WORK_CENTER_FLAG,

'Y' TOPITEM_SUCCESS_FLAG,

'Y' TOPITEM_FAILURE_FLAG,

to_number(null) WORK_CENTER_ID,

to_char(null) WORK_CENTER_CODE,

to_char(null) ROLLUP_STATUS_CODE,

to_char(null) UPDATE_FLAG,

to_char(null) STATUS_MESSAGE,

to_number(null) UNIT_COST,

to_number(null) EXTENDED_COST

FROM fusion.cst_scenarios cs,

fusion.hr_organization_units_f_tl hr,

fusion.inv_org_parameters iop,

fusion.CST_SCENARIO_EVENTS cse

WHERE cs.INV_ORG_ID = hr.ORGANIZATION_ID

AND cs.INV_ORG_ID = iop.ORGANIZATION_ID

AND cse.SCENARIO_ID = cs.SCENARIO_ID

AND hr.LANGUAGE = USERENV('LANG')

AND exists (select 1 from fusion.cst_scenario_rollup_headers csrh where csrh.SCENARIO_EVENT_ID = cse.SCENARIO_EVENT_ID)

union all

SELECT distinct

cs.SCENARIO_NUMBER node_name,

to_number(0) parent_wd_key,

to_number(0) parent_wo_key,

to_number(0) parent_wc_key,

to_number(0) parent_item_key,

to_char(null) parent_type,

cse.SCENARIO_ID child_wd_key,

to_number(0) child_wo_key,

to_number(0) child_wc_key,

to_number(0) child_item_key,

'SCENARIO' child_type,

'Version:' || TO_CHAR(cs.effective_start_date, 'DD-MON-YYYY') scenario_version,

cs.effective_start_date effective_start_date,

iop.organization_code organization_code,

hr.name inv_org_name,

cs.INV_ORG_ID INV_ORG_ID,

cs.SCENARIO_ID SCENARIO_ID,

cse.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

0 ROLLUP_HEADER_LEVEL,

to_number(null) INVENTORY_ITEM_ID,

to_number(null) RESOURCE_ID,

cs.SCENARIO_ID ROLLUP_HEADER_KEY,

'SCENARIO' ROLLUP_HEADER_TYPE,

to_char(null) WORK_DEFINITION_NAME,

to_number(null) YIELD_FACTOR,

to_number(null) UNIT_RATE,

to_number(null) QUANTITY_USAGE,

to_number(null) COSTING_BATCH_OUTPUT_SIZE,

to_char(null) QTY_UOM_CODE,

to_number(null) parent_wo_id,

to_number(null) parent_wd_id,

to_number(null) WORK_DEFINITION_ID,

to_number(null) WD_OPERATION_ID,

to_number(null) OPERATION_SEQ_NUMBER,

null RESOURCE_SEQ_NUMBER,

'Y' USE_WORK_CENTER_FLAG,

'Y' TOPITEM_SUCCESS_FLAG,

'Y' TOPITEM_FAILURE_FLAG,

to_number(null) WORK_CENTER_ID,

to_char(null) WORK_CENTER_CODE,

to_char(null) ROLLUP_STATUS_CODE,

to_char(null) UPDATE_FLAG,

to_char(null) STATUS_MESSAGE,

to_number(null) UNIT_COST,

to_number(null) EXTENDED_COST

FROM fusion.cst_scenarios cs,

fusion.hr_organization_units_f_tl hr,

fusion.inv_org_parameters iop,

fusion.CST_SCENARIO_EVENTS cse

WHERE cs.INV_ORG_ID = hr.ORGANIZATION_ID

AND cs.INV_ORG_ID = iop.ORGANIZATION_ID

AND cse.SCENARIO_ID = cs.SCENARIO_ID

AND hr.LANGUAGE = USERENV('LANG')

AND exists (select 1 from fusion.cst_scenario_rollup_headers csrh where csrh.SCENARIO_EVENT_ID = cse.SCENARIO_EVENT_ID)

union all

select crcv.node_name,

decode(crcv.ROLLUP_HEADER_LEVEL, 1, crcv.SCENARIO_ID, crcv.parent_wd_id) parent_wd_key,

decode(crcv.ROLLUP_HEADER_LEVEL, 1, 0, crcv.parent_wo_id) parent_wo_key,

decode(crcv.ROLLUP_HEADER_LEVEL, 1,0, decode(crcv.USE_WORK_CENTER_FLAG, 'Y', crcv.WORK_CENTER_ID,0)) parent_wc_key,

decode(crcv.ROLLUP_HEADER_LEVEL, 1, 0, crcv.parent_item_id) parent_item_key,

decode(crcv.ROLLUP_HEADER_LEVEL, 1, 'SCENARIO', 'OPERATIONS') parent_type,

to_number(decode(crcv.ROLLUP_HEADER_LEVEL, 0, crcv.ROLLUP_HEADER_KEY, crcv.WORK_DEFINITION_ID)) child_wd_key,

to_number(0) child_wo_key,

to_number(0) child_wc_key,

crcv.inventory_item_id child_item_key,

decode(crcv.ROLLUP_HEADER_LEVEL, 0, crcv.ROLLUP_HEADER_TYPE, decode(crcv.USE_WORK_CENTER_FLAG, 'Y', 'WORKCENTER','DEFINITION')) child_type,

to_char(null) scenario_version,

to_date(null) effective_start_date,

to_char(null) organization_code,

to_char(null) inv_org_name,

crcv.INV_ORG_ID,

crcv.SCENARIO_ID SCENARIO_ID,

crcv.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

crcv.ROLLUP_HEADER_LEVEL ROLLUP_HEADER_LEVEL,

crcv.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

crcv.RESOURCE_ID RESOURCE_ID,

crcv.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

crcv.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE,

crcv.WORK_DEFINITION_NAME WORK_DEFINITION_NAME,

crcv.YIELD_FACTOR YIELD_FACTOR,

crcv.UNIT_RATE UNIT_RATE,

crcv.QUANTITY_USAGE QUANTITY_USAGE,

crcv.COSTING_BATCH_OUTPUT_SIZE COSTING_BATCH_OUTPUT_SIZE,

crcv.QTY_UOM_CODE QTY_UOM_CODE,

crcv.parent_wo_id parent_wo_id,

crcv.parent_wd_id parent_wd_id,

crcv.WORK_DEFINITION_ID WORK_DEFINITION_ID,

crcv.parent_wo_id WD_OPERATION_ID,

crcv.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER,

crcv.RESOURCE_SEQ_NUMBER RESOURCE_SEQ_NUMBER,

crcv.USE_WORK_CENTER_FLAG,

crcv.TOPITEM_SUCCESS_FLAG,

crcv.TOPITEM_FAILURE_FLAG,

null WORK_CENTER_ID,

crcv.WORK_CENTER_CODE,

crcv.STATUS_CODE ROLLUP_STATUS_CODE,

crcv.UPDATE_FLAG UPDATE_FLAG,

crcv.STATUS_MESSAGE STATUS_MESSAGE,

crcv.UNIT_COST UNIT_COST,

crcv.EXTENDED_COST EXTENDED_COST

from fusion.CST_ROLLUP_COSTS_V crcv

union all

select

crov.node_name,

crov.PARENT_WD_ID parent_wd_key,

to_number(0) parent_wo_key,

decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) parent_wc_key,

crov.parent_item_id parent_item_key,

'DEFINITION' parent_type,

crov.PARENT_WD_ID child_wd_key,

crov.ROLLUP_HEADER_KEY child_wo_key,

decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) child_wc_key,

crov.inventory_item_id child_item_key,

crov.ROLLUP_HEADER_TYPE child_type,

to_char(null) scenario_version,

to_date(null) effective_start_date,

to_char(null) organization_code,

to_char(null) inv_org_name,

crov.INV_ORG_ID INV_ORG_ID,

crov.SCENARIO_ID SCENARIO_ID,

crov.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

to_number(null) ROLLUP_HEADER_LEVEL,

crov.INVENTORY_ITEM_ID INVENTORY_ITEM_ID,

to_number(null) RESOURCE_ID,

crov.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

crov.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE,

crov.WORK_DEFINITION_NAME,

to_number(null) YIELD_FACTOR,

to_number(null) UNIT_RATE,

to_number(null) QUANTITY_USAGE,

to_number(null) COSTING_BATCH_OUTPUT_SIZE,

to_char(null) QTY_UOM_CODE,

crov.PARENT_WO_ID PARENT_WO_ID,

crov.PARENT_WD_ID PARENT_WD_ID,

crov.PARENT_WD_ID WORK_DEFINITION_ID,

crov.WD_OPERATION_ID WD_OPERATION_ID,

crov.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER,

null RESOURCE_SEQ_NUMBER,

crov.USE_WORK_CENTER_FLAG,

'Y' TOPITEM_SUCCESS_FLAG,

'Y' TOPITEM_FAILURE_FLAG,

crov.WORK_CENTER_ID,

crov.WORK_CENTER_CODE,

to_char(null) ROLLUP_STATUS_CODE,

to_char(null) UPDATE_FLAG,

to_char(null) STATUS_MESSAGE,

crov.unit_cost unit_cost,

crov.extended_cost extended_cost

from fusion.CST_ROLLUP_OPERATIONS_V crov

union all

select

crwv.node_name,

crwv.PARENT_WD_ID parent_wd_key,

to_number(0) parent_wo_key,

to_number(0) parent_wc_key,

crwv.parent_item_id parent_item_key,

'WORKCENTER' parent_type,

crwv.PARENT_WD_ID child_wd_key,

to_number(0) child_wo_key,

crwv.WORK_CENTER_ID child_wc_key,

crwv.inventory_item_id child_item_key,

'DEFINITION' child_type,

to_char(null) scenario_version,

to_date(null) effective_start_date,

to_char(null) organization_code,

to_char(null) inv_org_name,

to_number(null) INV_ORG_ID,

crwv.SCENARIO_ID SCENARIO_ID,

crwv.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

to_number(null) ROLLUP_HEADER_LEVEL,

to_number(null)INVENTORY_ITEM_ID,

to_number(null) RESOURCE_ID,

crwv.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

crwv.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE,

to_char(crwv.WORK_DEFINITION_NAME),

to_number(null) YIELD_FACTOR,

to_number(null) UNIT_RATE,

to_number(null) QUANTITY_USAGE,

to_number(null) COSTING_BATCH_OUTPUT_SIZE,

to_char(null) QTY_UOM_CODE,

crwv.PARENT_WD_ID PARENT_WD_ID,

crwv.PARENT_WO_ID PARENT_WO_ID,

crwv.PARENT_WD_ID WORK_DEFINITION_ID,

to_number(null) WD_OPERATION_ID,

to_number(null) OPERATION_SEQ_NUMBER,

null RESOURCE_SEQ_NUMBER,

'Y' USE_WORK_CENTER_FLAG,

'Y' TOPITEM_SUCCESS_FLAG,

'Y' TOPITEM_FAILURE_FLAG,

crwv.WORK_CENTER_ID,

crwv.WORK_CENTER_CODE,

to_char(null) ROLLUP_STATUS_CODE,

to_char(null) UPDATE_FLAG,

to_char(null) STATUS_MESSAGE,

crwv.unit_cost unit_cost,

crwv.extended_cost extended_cost

from fusion.CST_ROLLUP_WORKCENTERS_V crwv

union all

select

crov.node_name,

crov.PARENT_WD_ID parent_wd_key,

to_number(0) parent_wo_key,

decode(crov.ROLLUP_HEADER_TYPE,'OVERHEADS_PLANT',decode(crov.USE_WORK_CENTER_FLAG, 'Y',0, crov.WORK_CENTER_ID),decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0)) parent_wc_key,

crov.parent_item_id parent_item_key,

decode(crov.ROLLUP_HEADER_TYPE,'OVERHEADS_PLANT',decode(crov.USE_WORK_CENTER_FLAG, 'Y','WORKCENTER','DEFINITION'),'DEFINITION') parent_type,

crov.PARENT_WD_ID child_wd_key,

crov.ROLLUP_HEADER_KEY child_wo_key,

decode(crov.USE_WORK_CENTER_FLAG, 'Y', crov.WORK_CENTER_ID,0) child_wc_key,

to_number(0) child_item_key,

crov.ROLLUP_HEADER_TYPE child_type,

to_char(null) scenario_version,

to_date(null) effective_start_date,

to_char(null) organization_code,

to_char(null) inv_org_name,

crov.INV_ORG_ID INV_ORG_ID,

crov.SCENARIO_ID SCENARIO_ID,

crov.SCENARIO_EVENT_ID SCENARIO_EVENT_ID,

to_number(0) ROLLUP_HEADER_LEVEL,

to_number(null)INVENTORY_ITEM_ID,

to_number(null) RESOURCE_ID,

crov.ROLLUP_HEADER_KEY ROLLUP_HEADER_KEY,

crov.ROLLUP_HEADER_TYPE ROLLUP_HEADER_TYPE,

to_char(crov.WORK_DEFINITION_NAME),

to_number(null) YIELD_FACTOR,

to_number(null) UNIT_RATE,

to_number(null) QUANTITY_USAGE,

to_number(null) COSTING_BATCH_OUTPUT_SIZE,

to_char(null) QTY_UOM_CODE,

crov.PARENT_WD_ID PARENT_WD_ID,

crov.PARENT_WO_ID PARENT_WO_ID,

crov.PARENT_WD_ID WORK_DEFINITION_ID,

to_number(null) WD_OPERATION_ID,

to_number(null) OPERATION_SEQ_NUMBER,

null RESOURCE_SEQ_NUMBER,

crov.USE_WORK_CENTER_FLAG USE_WORK_CENTER_FLAG,

'Y' TOPITEM_SUCCESS_FLAG,

'Y' TOPITEM_FAILURE_FLAG,

crov.WORK_CENTER_ID,

crov.WORK_CENTER_CODE,

to_char(null) ROLLUP_STATUS_CODE,

to_char(null) UPDATE_FLAG,

to_char(null) STATUS_MESSAGE,

crov.unit_cost unit_cost,

crov.extended_cost extended_cost

from fusion.CST_ROLLUP_OVERHEADS_V crov