CST_ROLLUP_OPERATIONS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

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

Name

NODE_NAME

INVENTORY_ITEM_ID

INV_ORG_ID

ROLLUP_HEADER_KEY

ROLLUP_HEADER_TYPE

WORK_DEFINITION_ID

WORK_DEFINITION_NAME

WD_OPERATION_ID

PARENT_WD_ID

PARENT_WO_ID

PARENT_ITEM_ID

OPERATION_SEQ_NUMBER

USE_WORK_CENTER_FLAG

WORK_CENTER_ID

WORK_CENTER_CODE

SCENARIO_ID

SCENARIO_EVENT_ID

UNIT_COST

EXTENDED_COST

Query

SQL_Statement
select distinct wwov.OPERATION_NAME 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, 'OPERATIONS' 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, null 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_WD_OPERATIONS_V wwov, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, 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 where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and nvl(csrd.OVERHEAD_FLAG, 'N') <> 'Y' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID) 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 wwov.WD_OPERATION_ID = cwd.WD_OPERATION_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID union all select distinct wwov.OPERATION_NAME 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, 'OPERATIONS' 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 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_WD_OPERATIONS_V wwov, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv, (select csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, 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 where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID and csrh.ROLLUP_HEADER_TYPE = 'OPERATION' and nvl(csrd.OVERHEAD_FLAG, 'N') <> 'Y' group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID) 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 wwov.WD_OPERATION_ID = cwd.WD_OPERATION_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

SQL_Statement

select distinct

wwov.OPERATION_NAME 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,

'OPERATIONS' 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,

null 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_WD_OPERATIONS_V wwov,

fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv,

(select csrh.ROLLUP_HEADER_KEY,

csrd.WD_OPERATION_ID,

csrh.SCENARIO_ID,

csrh.SCENARIO_EVENT_ID,

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

where csrh.SCENARIO_ROLLUP_HEADER_ID = csrd.SCENARIO_ROLLUP_HEADER_ID

and csrh.ROLLUP_HEADER_TYPE = 'OPERATION'

and nvl(csrd.OVERHEAD_FLAG, 'N') <> 'Y'

group by csrh.ROLLUP_HEADER_KEY, csrd.WD_OPERATION_ID, csrh.SCENARIO_ID, csrh.SCENARIO_EVENT_ID) 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 wwov.WD_OPERATION_ID = cwd.WD_OPERATION_ID

and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID

union all