CST_ROLLUP_DET_OTBI_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

SCENARIO_ROLLUP_DETAIL_ID SCENARIO_ROLLUP_HEADER_ID WD_OPERATION_ID RESOURCE_SEQ_NUMBER RESOURCE_ID MATERIAL_INVENTORY_ITEM_ID MATERIAL_INVENTORY_ORG_ID COST_COMPONENT_ID EXPENSE_POOL_ID COST_LEVEL OVERHEAD_FLAG OVERHEAD_RATE_ID COSTING_BATCH_OUTPUT_SIZE BASIS_TYPE QUANTITY_USAGE QTY_UOM_CODE COST_UOM_CODE EXTENDED_COST UNIT_COST YIELD_FACTOR CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN REQUEST_ID JOB_DEFINITION_NAME JOB_DEFINITION_PACKAGE COST_ELEMENT_ID CST_OPERATION_ID SOURCE_TYPE OUTPUT_INVENTORY_ITEM_ID OUTPUT_INVENTORY_ORG_ID CUMULATIVE_UNIT_COST CUMULATIVE_EXTENDED_COST ALLOCATED_UNIT_COST ALLOCATED_EXTENDED_COST UNALLOCATED_UNIT_COST UNALLOCATED_EXTENDED_COST RUNNING_UNIT_TOTAL RUNNING_EXTENDED_TOTAL COST_ALLOCATION_PERCENTAGE

Query

SQL_Statement

SELECT SRD.SCENARIO_ROLLUP_DETAIL_ID, SRD.SCENARIO_ROLLUP_HEADER_ID, DECODE (DET_ROW.DET_ROW_NUM, 1, NVL(SRD.WD_OPERATION_ID, -1), NULL) WD_OPERATION_ID, DECODE (DET_ROW.DET_ROW_NUM, 1, SRD.RESOURCE_SEQ_NUMBER, NULL) RESOURCE_SEQ_NUMBER, DECODE (DET_ROW.DET_ROW_NUM, 1, SRD.RESOURCE_ID, NULL) RESOURCE_ID, DECODE (DET_ROW.DET_ROW_NUM, 1, NVL(SRD.MATERIAL_INVENTORY_ITEM_ID, SRD.OUTPUT_INVENTORY_ITEM_ID), NULL) MATERIAL_INVENTORY_ITEM_ID, DECODE (DET_ROW.DET_ROW_NUM, 1, NVL(SRD.MATERIAL_INVENTORY_ORG_ID, SRD.OUTPUT_INVENTORY_ORG_ID), NULL) MATERIAL_INVENTORY_ORG_ID, SRD.COST_COMPONENT_ID, DECODE(CSRH.ROLLUP_ITEM_TYPE,'PO_CONFIGURED_ITEMS',SRD.EXPENSE_POOL_ID,DECODE(SRD.OVERHEAD_FLAG,'Y',SRD.EXPENSE_POOL_ID,NVL2(SRD.STD_TXFR_COST_RULE_ID,SRD.EXPENSE_POOL_ID,NULL))) EXPENSE_POOL_ID, SRD.COST_LEVEL, SRD.OVERHEAD_FLAG, DECODE (DET_ROW.DET_ROW_NUM, 1, SRD.OVERHEAD_RATE_ID, NULL) OVERHEAD_RATE_ID, SRD.COSTING_BATCH_OUTPUT_SIZE, SRD.BASIS_TYPE, SRD.QUANTITY_USAGE, SRD.QTY_UOM_CODE, SRD.COST_UOM_CODE, DECODE (DET_ROW.DET_ROW_NUM, 2, SRD.EXTENDED_COST, DECODE(SRD.source_type, NULL, SRD.EXTENDED_COST, SRD.OPERATION_EXTENDED_COST)) EXTENDED_COST, DECODE (DET_ROW.DET_ROW_NUM, 2, SRD.UNIT_COST, DECODE(SRD.source_type, NULL, SRD.UNIT_COST, SRD.OPERATION_UNIT_COST)) UNIT_COST, SRD.YIELD_FACTOR, SRD.CREATED_BY, SRD.CREATION_DATE, SRD.LAST_UPDATED_BY, SRD.LAST_UPDATE_DATE, SRD.LAST_UPDATE_LOGIN, SRD.REQUEST_ID, SRD.JOB_DEFINITION_NAME, SRD.JOB_DEFINITION_PACKAGE, SRD.COST_ELEMENT_ID, SRD.CST_OPERATION_ID, SRD.SOURCE_TYPE, SRD.OUTPUT_INVENTORY_ITEM_ID, SRD.OUTPUT_INVENTORY_ORG_ID, SRD.CUMULATIVE_UNIT_COST, SRD.CUMULATIVE_EXTENDED_COST, DECODE ( SRD.SOURCE_TYPE, 'OUTPUT_ALLOCATION', -SRD.ALLOCATED_UNIT_COST, 'RESIDUAL_ALLOCATION', -SRD.ALLOCATED_UNIT_COST, SRD.ALLOCATED_UNIT_COST) ALLOCATED_UNIT_COST, DECODE ( SRD.SOURCE_TYPE, 'OUTPUT_ALLOCATION', -SRD.ALLOCATED_EXTENDED_COST, 'RESIDUAL_ALLOCATION', -SRD.ALLOCATED_EXTENDED_COST, SRD.ALLOCATED_EXTENDED_COST) ALLOCATED_EXTENDED_COST, SRD.UNALLOCATED_UNIT_COST, SRD.UNALLOCATED_EXTENDED_COST, SRD.RUNNING_UNIT_TOTAL, SRD.RUNNING_EXTENDED_TOTAL, SRD.THIS_ALLOCATION_PERCENTAGE + SRD.OTHER_ALLOCATION_PERCENTAGE COST_ALLOCATION_PERCENTAGE FROM FUSION.CST_SCENARIO_ROLLUP_DETAILS SRD, FUSION.CST_STD_COSTS STD, FUSION.CST_SCENARIO_ROLLUP_HEADERS CSRH, (SELECT 1 DET_ROW_NUM FROM DUAL UNION SELECT 2 DET_ROW_NUM FROM DUAL ) DET_ROW WHERE SRD.SCENARIO_ROLLUP_HEADER_ID = STD.SCENARIO_ROLLUP_HEADER_ID AND STD.SCENARIO_ID = NVL(SYS_CONTEXT('CST_OTBI_PARAMS','p_scenario_id'), std.scenario_id) AND SRD.SCENARIO_ROLLUP_HEADER_ID = CSRH.SCENARIO_ROLLUP_HEADER_ID