CST_ROLLUP_COSTS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

NODE_NAME INVENTORY_ITEM_ID INV_ORG_ID RESOURCE_ID ROLLUP_HEADER_KEY ROLLUP_HEADER_TYPE STATUS_CODE UPDATE_FLAG STATUS_MESSAGE SCENARIO_ID SCENARIO_EVENT_ID ROLLUP_HEADER_LEVEL WORK_DEFINITION_NAME YIELD_FACTOR UNIT_RATE QUANTITY_USAGE COSTING_BATCH_OUTPUT_SIZE QTY_UOM_CODE PARENT_WO_ID PARENT_WD_ID PARENT_ITEM_ID WORK_DEFINITION_ID OPERATION_SEQ_NUMBER RESOURCE_SEQ_NUMBER USE_WORK_CENTER_FLAG TOPITEM_SUCCESS_FLAG TOPITEM_FAILURE_FLAG WORK_CENTER_ID WORK_CENTER_CODE UNIT_COST EXTENDED_COST

Query

SQL_Statement

select distinct wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.MATERIAL_QUANTITY QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, cost.UNIT_COST UNIT_COST , cost.EXTENDED_COST EXTENDED_COST from (select (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, decode(csrh.STATUS_CODE,'SUCCESS',decode(csrh.SOURCE_TYPE,'CST_SCENARIO_ROLLUP_HEADERS','COPY',csrh.STATUS_CODE),csrh.STATUS_CODE) STATUS_CODE, decode(csrh.SOURCE_TYPE, null, decode(csrh.SOURCE_ID, null, 'Y', 'N'), 'N') UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, decode(csrh.STATUS_CODE,'SUCCESS','Y',null) as TOPITEM_SUCCESS_FLAG, decode(csrh.STATUS_CODE,'SUCCESS',null,decode(csrh.STATUS_CODE,'NOT_PROCESSED',null,'Y')) as TOPITEM_FAILURE_FLAG, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, csrh.ROLLUP_HEADER_LEVEL, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, null MATERIAL_QUANTITY, cwd.COSTING_BATCH_OUTPUT_SIZE, csrh.PRIMARY_UOM_CODE QTY_UOM_CODE, null parent_wo_id, null parent_wd_id, csrh.ROLLUP_HEADER_KEY parent_item_id, cwd.WORK_DEFINITION_ID, null OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_SCENARIOS cs, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 1 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 1 group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 1 union all select distinct wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.MATERIAL_QUANTITY QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, cost.UNIT_COST UNIT_COST , cost.EXTENDED_COST EXTENDED_COST from (select (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, decode(csrh.STATUS_CODE,'SUCCESS',decode(csrh.SOURCE_TYPE,'CST_SCENARIO_ROLLUP_HEADERS','COPY',csrh.STATUS_CODE),csrh.STATUS_CODE) STATUS_CODE, decode(csrh.SOURCE_TYPE, null, decode(csrh.SOURCE_ID, null, 'Y', 'N'), 'N') UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, decode(csrh.STATUS_CODE,'SUCCESS','Y',null) as TOPITEM_SUCCESS_FLAG, decode(csrh.STATUS_CODE,'SUCCESS',null,decode(csrh.STATUS_CODE,'NOT_PROCESSED',null,'Y')) as TOPITEM_FAILURE_FLAG, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, csrh.ROLLUP_HEADER_LEVEL, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, null MATERIAL_QUANTITY, cwd.COSTING_BATCH_OUTPUT_SIZE, csrh.PRIMARY_UOM_CODE QTY_UOM_CODE, null parent_wo_id, null parent_wd_id, csrh.ROLLUP_HEADER_KEY parent_item_id, cwd.WORK_DEFINITION_ID, null OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_SCENARIOS cs, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 1 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 1 group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 1 union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.MATERIAL_QUANTITY QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_COST * wd.qty_used_per_unit) UNIT_COST , (cost.UNIT_COST * wd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, decode(csrh.STATUS_CODE,'SUCCESS',decode(csrh.SOURCE_TYPE,'CST_SCENARIO_ROLLUP_HEADERS','COPY',csrh.STATUS_CODE),csrh.STATUS_CODE) STATUS_CODE, decode(csrh.SOURCE_TYPE, null, decode(csrh.SOURCE_ID, null, 'Y', 'N'), 'N') UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, decode(csrh.STATUS_CODE,'SUCCESS','Y',null) as TOPITEM_SUCCESS_FLAG, decode(csrh.STATUS_CODE,'SUCCESS',null,decode(csrh.STATUS_CODE,'NOT_PROCESSED',null,'Y')) as TOPITEM_FAILURE_FLAG, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, csrh.ROLLUP_HEADER_LEVEL, (select distinct wwdnv.WORK_DEF_NAME from fusion.CST_WORK_DEFINITIONS cwd1,fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where cwd1.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd1.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd1.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID and cwd1.SCENARIO_ID = cs.SCENARIO_ID ) as WORK_DEFINITION_NAME, cwd.MATERIAL_YIELD_FACTOR YIELD_FACTOR, cwd.MATERIAL_QUANTITY, cwd.MATERIAL_SCALED_QUANTITY, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, (select distinct cwd1.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd1 where cwd1.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd1.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd1.SCENARIO_ID = cs.SCENARIO_ID ) as WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_SCENARIOS cs where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL not in (1,0) and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL not in (1,0) group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL not in (1,0) union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_COST * wd.qty_used_per_unit) UNIT_COST , (cost.UNIT_COST * wd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, decode(csrh.STATUS_CODE,'SUCCESS',decode(csrh.SOURCE_TYPE,'CST_SCENARIO_ROLLUP_HEADERS','COPY',csrh.STATUS_CODE),csrh.STATUS_CODE) STATUS_CODE, decode(csrh.SOURCE_TYPE, null, decode(csrh.SOURCE_ID, null, 'Y', 'N'), 'N') UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, decode(csrh.STATUS_CODE,'SUCCESS','Y',null) as TOPITEM_SUCCESS_FLAG, decode(csrh.STATUS_CODE,'SUCCESS',null,decode(csrh.STATUS_CODE,'NOT_PROCESSED',null,'Y')) as TOPITEM_FAILURE_FLAG, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, csrh.ROLLUP_HEADER_LEVEL, (select distinct wwdnv.WORK_DEF_NAME from fusion.CST_WORK_DEFINITIONS cwd1,fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where cwd1.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd1.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd1.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID and cwd1.SCENARIO_ID = cs.SCENARIO_ID ) as WORK_DEFINITION_NAME, cwd.MATERIAL_YIELD_FACTOR YIELD_FACTOR, cwd.MATERIAL_QUANTITY, cwd.MATERIAL_SCALED_QUANTITY, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, (select distinct cwd1.WORK_DEFINITION_ID from fusion.CST_WORK_DEFINITIONS cwd1 where cwd1.OUTPUT_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd1.OUTPUT_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd1.SCENARIO_ID = cs.SCENARIO_ID ) as WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, cwd.WORK_CENTER_ID 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 from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_WORK_DEFINITIONS cwd, fusion.CST_SCENARIOS cs where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL not in (1,0) and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL not in (1,0) group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL not in (1,0) union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_COST * wd.qty_used_per_unit) UNIT_COST , (cost.UNIT_COST * wd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from fusion.CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL, csrh.CURRENCY_CONVERSION_RATE, csrh.SOURCE_TYPE, csrh.SOURCE_ID, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.MATERIAL_QUANTITY , cwd.MATERIAL_SCALED_QUANTITY, cwd.MATERIAL_YIELD_FACTOR, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_SCENARIOS cs, fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SOURCE_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 0 and wd.SOURCE_TYPE = 'CST_SCENARIO_ROLLUP_HEADERS' union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, null UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_COST * wd.qty_used_per_unit) UNIT_COST , (cost.UNIT_COST * wd.MATERIAL_SCALED_QUANTITY) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from fusion.CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL, csrh.CURRENCY_CONVERSION_RATE, csrh.SOURCE_TYPE, csrh.SOURCE_ID, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.MATERIAL_QUANTITY , cwd.MATERIAL_SCALED_QUANTITY, cwd.MATERIAL_YIELD_FACTOR, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, cwd.WORK_CENTER_ID 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 from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_SCENARIOS cs, fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select csrh.SCENARIO_ROLLUP_HEADER_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 = 'ITEM' group by csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SOURCE_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 0 and wd.SOURCE_TYPE = 'CST_SCENARIO_ROLLUP_HEADERS' union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, (cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1)) UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1) * wd.qty_used_per_unit) UNIT_COST , (wd.MATERIAL_SCALED_QUANTITY * cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1)) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL, csrh.CURRENCY_CONVERSION_RATE, csrh.SOURCE_TYPE, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.MATERIAL_QUANTITY , cwd.MATERIAL_SCALED_QUANTITY, cwd.MATERIAL_YIELD_FACTOR, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_SCENARIOS cs, fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select ccel.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ccel.INVENTORY_ORG_ID INV_ORG_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, sum(cced.ESTIMATED_UNIT_COST) ESTIMATED_UNIT_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 where ccel.INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and ccel.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID and ccea.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID and csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 AND ccea.COST_ESTIMATE_ASSIGN_ID = csrh.source_id and csrh.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS' group by ccel.INVENTORY_ITEM_ID, ccel.INVENTORY_ORG_ID, csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_KEY = cost.INVENTORY_ITEM_ID(+) and wd.INVENTORY_ITEM_ID = cost.INVENTORY_ITEM_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 0 AND wd.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS' union all select wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, (cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1)) UNIT_RATE, wd.MATERIAL_QUANTITY QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, null RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1)* wd.qty_used_per_unit) UNIT_COST , (wd.MATERIAL_SCALED_QUANTITY * cost.ESTIMATED_UNIT_COST * nvl(wd.CURRENCY_CONVERSION_RATE,1)) EXTENDED_COST from (select distinct (select item_number from egp_system_items where inventory_item_id = csrh.ROLLUP_HEADER_KEY and organization_id = cs.INV_ORG_ID) as node_name, csrh.ROLLUP_HEADER_KEY INVENTORY_ITEM_ID, cs.INV_ORG_ID INV_ORG_ID, null RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL, csrh.CURRENCY_CONVERSION_RATE, csrh.SOURCE_TYPE, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.MATERIAL_QUANTITY , cwd.MATERIAL_SCALED_QUANTITY, cwd.MATERIAL_YIELD_FACTOR, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.MATERIAL_SCALED_QUANTITY/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.MATERIAL_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.work_definition_id parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, cwd.WORK_CENTER_ID WORK_CENTER_ID, (select WORK_CENTER_NAME from WIS_WORK_CENTERS_VL where WORK_CENTER_ID = cwd.WORK_CENTER_ID) as WORK_CENTER_CODE from fusion.CST_SCENARIO_ROLLUP_HEADERS csrh, fusion.CST_SCENARIOS cs, fusion.CST_WORK_DEFINITIONS cwd, fusion.WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.SCENARIO_ID = cs.SCENARIO_ID and cwd.MATERIAL_INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and cwd.MATERIAL_INVENTORY_ORG_ID = cs.INV_ORG_ID and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID ) wd, (select ccel.INVENTORY_ITEM_ID INVENTORY_ITEM_ID, ccel.INVENTORY_ORG_ID INV_ORG_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, sum(cced.ESTIMATED_UNIT_COST) ESTIMATED_UNIT_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 where ccel.INVENTORY_ITEM_ID = csrh.ROLLUP_HEADER_KEY and ccel.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID and ccea.COST_ESTIMATE_LINE_ID = cced.COST_ESTIMATE_LINE_ID and csrh.ROLLUP_HEADER_TYPE = 'ITEM' and csrh.ROLLUP_HEADER_LEVEL = 0 AND ccea.COST_ESTIMATE_ASSIGN_ID = csrh.source_id and csrh.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS' group by ccel.INVENTORY_ITEM_ID, ccel.INVENTORY_ORG_ID, csrh.SCENARIO_ROLLUP_HEADER_ID ) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID(+) and wd.ROLLUP_HEADER_KEY = cost.INVENTORY_ITEM_ID(+) and wd.INVENTORY_ITEM_ID = cost.INVENTORY_ITEM_ID(+) and wd.ROLLUP_HEADER_TYPE = 'ITEM' and wd.ROLLUP_HEADER_LEVEL = 0 AND wd.SOURCE_TYPE = 'CST_COST_ESTIMATE_ASSIGNS' union all select distinct wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, (cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1))UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, wd.RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1)* wd.qty_used_per_unit ) UNIT_COST , (wd.RESOURCE_SCALED_USAGE_RATE * cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1)) EXTENDED_COST from (select distinct wrv.resource_name node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL , csrh.CURRENCY_CONVERSION_RATE, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.RESOURCE_SEQ_NUMBER, cwd.RESOURCE_ASSIGNED_UNITS, cwd.RESOURCE_USAGE_RATE, cwd.RESOURCE_SCALED_USAGE_RATE, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.RESOURCE_SCALED_USAGE_RATE/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.RESOURCE_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.WORK_DEFINITION_ID parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'N' USE_WORK_CENTER_FLAG, null WORK_CENTER_ID, null WORK_CENTER_CODE from CST_SCENARIO_ROLLUP_HEADERS csrh, CST_WORK_DEFINITIONS cwd, WIS_RESOURCES_VL wrv, WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.ROLLUP_HEADER_TYPE = 'RESOURCE' and cwd.RESOURCE_ID = csrh.ROLLUP_HEADER_KEY and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID and wrv.RESOURCE_ID = cwd.RESOURCE_ID) wd, (select crr.RESOURCE_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, sum(crrd.UNIT_RATE) UNIT_RATE from CST_RESOURCE_RATES crr, CST_RESOURCE_RATE_DETAILS crrd, CST_SCENARIO_ROLLUP_HEADERS csrh 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' group by crr.RESOURCE_ID, csrh.SCENARIO_ROLLUP_HEADER_ID) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID (+) and wd.ROLLUP_HEADER_KEY = cost.RESOURCE_ID(+) and wd.ROLLUP_HEADER_LEVEL = 0 and wd.ROLLUP_HEADER_TYPE = 'RESOURCE' union all select distinct wd.node_name, wd.INVENTORY_ITEM_ID, wd.INV_ORG_ID, wd.RESOURCE_ID, wd.ROLLUP_HEADER_KEY, wd.ROLLUP_HEADER_TYPE, wd.STATUS_CODE, wd.UPDATE_FLAG, wd.STATUS_MESSAGE, wd.SCENARIO_ID, wd.SCENARIO_EVENT_ID, wd.ROLLUP_HEADER_LEVEL , wd.WORK_DEFINITION_NAME, wd.YIELD_FACTOR, (cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1))UNIT_RATE, wd.qty_used_per_unit QUANTITY_USAGE, wd.COSTING_BATCH_OUTPUT_SIZE, wd.QTY_UOM_CODE, wd.parent_wo_id, wd.parent_wd_id, wd.parent_item_id, wd.WORK_DEFINITION_ID, wd.OPERATION_SEQ_NUMBER, wd.RESOURCE_SEQ_NUMBER, wd.USE_WORK_CENTER_FLAG, wd.TOPITEM_SUCCESS_FLAG, wd.TOPITEM_FAILURE_FLAG, wd.WORK_CENTER_ID, wd.WORK_CENTER_CODE, (cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1) * wd.qty_used_per_unit ) UNIT_COST , (wd.RESOURCE_SCALED_USAGE_RATE * cost.UNIT_RATE * nvl(wd.CURRENCY_CONVERSION_RATE,1)) EXTENDED_COST from (select distinct wrv.resource_name node_name, cwd.OUTPUT_INVENTORY_ITEM_ID INVENTORY_ITEM_ID, cwd.OUTPUT_INVENTORY_ORG_ID INV_ORG_ID, cwd.RESOURCE_ID, csrh.ROLLUP_HEADER_KEY, csrh.ROLLUP_HEADER_TYPE, csrh.STATUS_CODE, decode(csrh.STATUS_CODE, 'CHANGED', 'C', null) UPDATE_FLAG, (select message_text from CST_SCENARIO_EVENT_MESSAGES where message_name = csrh.status_code and rownum=1 and message_text is not null) as STATUS_MESSAGE, 'Y' TOPITEM_SUCCESS_FLAG, 'Y' TOPITEM_FAILURE_FLAG, csrh.SCENARIO_ROLLUP_HEADER_ID, cwd.SCENARIO_ID, csrh.SCENARIO_EVENT_ID, csrh.ROLLUP_HEADER_LEVEL , csrh.CURRENCY_CONVERSION_RATE, wwdnv.WORK_DEF_NAME WORK_DEFINITION_NAME, null YIELD_FACTOR, cwd.RESOURCE_SEQ_NUMBER, cwd.RESOURCE_ASSIGNED_UNITS, cwd.RESOURCE_USAGE_RATE, cwd.RESOURCE_SCALED_USAGE_RATE, cwd.COSTING_BATCH_OUTPUT_SIZE, cwd.RESOURCE_SCALED_USAGE_RATE/decode(cwd.COSTING_BATCH_OUTPUT_SIZE,null,1,0,1,cwd.COSTING_BATCH_OUTPUT_SIZE) qty_used_per_unit, cwd.RESOURCE_UOM_CODE QTY_UOM_CODE, cwd.WD_OPERATION_ID parent_wo_id, cwd.WORK_DEFINITION_ID parent_wd_id, cwd.output_inventory_item_id parent_item_id, null WORK_DEFINITION_ID, cwd.OPERATION_SEQ_NUMBER OPERATION_SEQ_NUMBER, 'Y' USE_WORK_CENTER_FLAG, cwd.WORK_CENTER_ID WORK_CENTER_ID, (select WORK_CENTER_NAME from WIS_WORK_CENTERS_VL where WORK_CENTER_ID = cwd.WORK_CENTER_ID) as WORK_CENTER_CODE from CST_SCENARIO_ROLLUP_HEADERS csrh, CST_WORK_DEFINITIONS cwd, WIS_RESOURCES_VL wrv, WIS_WORK_DEFINITION_NAMES_VL wwdnv where csrh.SCENARIO_ID = cwd.SCENARIO_ID and csrh.ROLLUP_HEADER_LEVEL = 0 and csrh.ROLLUP_HEADER_TYPE = 'RESOURCE' and cwd.RESOURCE_ID = csrh.ROLLUP_HEADER_KEY and cwd.WORK_DEFINITION_NAME_ID = wwdnv.WORK_DEFINITION_NAME_ID and wrv.RESOURCE_ID = cwd.RESOURCE_ID) wd, (select crr.RESOURCE_ID, csrh.SCENARIO_ROLLUP_HEADER_ID, sum(crrd.UNIT_RATE) UNIT_RATE from CST_RESOURCE_RATES crr, CST_RESOURCE_RATE_DETAILS crrd, CST_SCENARIO_ROLLUP_HEADERS csrh 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' group by crr.RESOURCE_ID, csrh.SCENARIO_ROLLUP_HEADER_ID) cost where wd.SCENARIO_ROLLUP_HEADER_ID = cost.SCENARIO_ROLLUP_HEADER_ID (+) and wd.ROLLUP_HEADER_KEY = cost.RESOURCE_ID(+) and wd.ROLLUP_HEADER_LEVEL = 0 and wd.ROLLUP_HEADER_TYPE = 'RESOURCE'