1. Tables and Views for SCM
  2. CST_ROLLUP_DET_OTBI_V

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

cst_scenario_rollup_details srd,

cst_std_costs std,

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 = csrh.scenario_rollup_header_id

AND csrh.scenario_id = nvl(sys_context('CST_OTBI_PARAMS', 'p_scenario_id'), csrh.scenario_id)

AND csrh.source_id = std.std_cost_id

AND csrh.source_type = 'CST_STD_COSTS'