1. Tables and Views for SCM
  2. CST_INDENTED_ROLLUP_DETAILS_V

CST_INDENTED_ROLLUP_DETAILS_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

ROLLUPKEY

ROOT_ASSEMBLY_ITEM_ID

ASSEMBLY_ITEM_ID

BOM_LEVEL

WORK_DEFINITION_ID

VERSION_NUMBER

ORGANIZATION_ID

OUTPUT_INVENTORY_ITEM_ID

WORK_DEFINITION_TYPE

PRODUCTION_PRIORITY

COSTING_PRIORITY

WD_COSTING_BATCH_OUTPUT_SIZE

WORK_DEFINITION_NAME_ID

BATCH_QUANTITY

BATCH_UOM_CODE

BATCH_PRIMARY_UOM_CODE

WORK_ORDER_ID

PO_LINE_LOCATION_ID

VAL_UNIT_ID

COST_ORG_ID

COST_BOOK_ID

EFFECTIVE_START_DATE

SCENARIO_ROLLUP_HEADER_ID

SCENARIO_ID

ROLLUP_HEADER_TYPE

ROLLUP_HEADER_KEY

ROLLUP_HEADER_LEVEL

STATUS_CODE

SOURCE_TYPE

SOURCE_ID

COST_EFFECTIVE_START_DATE

COST_EFFECTIVE_END_DATE

COST_UOM_CODE

PRIMARY_UOM_CODE

COST_UOM_CONVERSION_FACTOR

CURRENCY_CONVERSION_RATE

PUBLISHED_FLAG

SCENARIO_EVENT_ID

INV_ORG_ID

POSTED_FLAG

WD_OPERATION_ID

RESOURCE_SEQ_NUMBER

RESOURCE_ID

MATERIAL_INVENTORY_ITEM_ID

MATERIAL_INVENTORY_ORG_ID

OVERHEAD_RATE_ID

OVERHEAD_FLAG

COSTING_BATCH_OUTPUT_SIZE

YIELD_FACTOR

OP_YIELD_FACTOR

OP_REV_YIELD_FACTOR

BASIS_TYPE

QUANTITY_USAGE

QTY_UOM_CODE

COMP_COST_UOM_CODE

EXTENDED_COST

UNIT_COST

ROOT_ROW_NUM

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

OUTPUT_TYPE

ORIG_OUTPUT_TYPE

PRIMARY_FLAG

DETAIL_SOURCE_TYPE

WORK_METHOD_ID

WORK_METHOD_CODE

WORK_METHOD_NAME

OUTPUT_SEQ_NUMBER

OPERATION_SEQ_NUMBER

Query

SQL_Statement

SELECT

MIN(rollupkey) rollupkey,

root_assembly_item_id,

decode(root_total.root_row_num, 1, assembly_item_id, NULL) assembly_item_id,

decode(root_total.root_row_num, 1, bom_level, 0) bom_level,

work_definition_id,

version_number,

organization_id,

output_inventory_item_id,

work_definition_type,

production_priority,

costing_priority,

costing_batch_output_size wd_costing_batch_output_size,

work_definition_name_id,

batch_quantity,

batch_uom_code,

batch_primary_uom_code,

work_order_id,

po_line_location_id,

val_unit_id,

cost_org_id,

cost_book_id,

effective_start_date,

scenario_rollup_header_id,

scenario_id,

rollup_header_type,

rollup_header_key,

rollup_header_level,

status_code,

source_type,

source_id,

cost_effective_start_date,

cost_effective_end_date,

cost_uom_code,

primary_uom_code,

cost_uom_conversion_factor,

currency_conversion_rate,

published_flag,

scenario_event_id,

inv_org_id,

posted_flag,

decode(root_total.root_row_num, 1, nvl(wd_operation_id, - 1), NULL) wd_operation_id,

decode(root_total.root_row_num, 1, resource_seq_number, NULL) resource_seq_number,

decode(root_total.root_row_num, 1, resource_id, NULL) resource_id,

decode(root_total.root_row_num, 1, nvl(material_inventory_item_id, dtl_output_inventory_item_id), NULL) material_inventory_item_id,

decode(root_total.root_row_num, 1, nvl(material_inventory_org_id, dtl_output_inventory_org_id), NULL) material_inventory_org_id,

decode(root_total.root_row_num, 1, overhead_rate_id, NULL) overhead_rate_id,

decode(root_total.root_row_num, 1, overhead_flag, NULL) overhead_flag,

decode(root_total.root_row_num, 1, costing_batch_output_size, NULL) costing_batch_output_size,

decode(root_total.root_row_num, 1, decode(nvl(yield_factor, 1), 0, 1, nvl(yield_factor, 1)), NULL) yield_factor,

decode(root_total.root_row_num, 1, decode(nvl(op_yield_factor, 1), 0, 1, nvl(op_yield_factor, 1)), NULL) op_yield_factor,

decode(root_total.root_row_num, 1, decode(nvl(op_rev_yield_factor, 1), 0, 1, nvl(op_rev_yield_factor, 1)), NULL) op_rev_yield_factor,

decode(root_total.root_row_num, 1, basis_type, NULL) basis_type,

decode(root_total.root_row_num, 1, decode(nvl(quantity_usage, 0), 0, 0,(quantity_usage / decode(nvl(yield_factor, 1), 0, 1, nvl(yield_factor, 1)))), output_quantity) quantity_usage,

decode(root_total.root_row_num, 1, qty_uom_code, NULL) qty_uom_code,

cost_uom_code comp_cost_uom_code,

SUM(decode(root_total.root_row_num, 1, nvl(operation_extended_cost, extended_cost), extended_cost)) extended_cost,

SUM(decode(root_total.root_row_num, 1, operation_unit_cost, unit_cost)) unit_cost,

root_total.root_row_num,

SUM(cumulative_unit_cost * show_values) cumulative_unit_cost,

SUM(cumulative_extended_cost * show_values) cumulative_extended_cost,

SUM(decode(detail_source_type, 'OUTPUT_ALLOCATION', - allocated_unit_cost, 'RESIDUAL_ALLOCATION', - allocated_unit_cost, allocated_unit_cost) * show_values) allocated_unit_cost,

SUM(decode(detail_source_type, 'OUTPUT_ALLOCATION', - allocated_extended_cost, 'RESIDUAL_ALLOCATION', - allocated_unit_cost, allocated_extended_cost) * show_values) allocated_extended_cost,

SUM(unallocated_unit_cost * show_values) unallocated_unit_cost,

SUM(unallocated_extended_cost * show_values) unallocated_extended_cost,

SUM(running_unit_total * show_values) running_unit_total,

SUM(running_extended_total * show_values) running_extended_total,

cost_allocation_percentage * show_values cost_allocation_percentage,

decode(show_values, 1, output_type, NULL) output_type,

decode(show_values, 1, orig_output_type, NULL) orig_output_type,

decode(show_values, 1, primary_flag, NULL) primary_flag,

decode(show_values, 1, detail_source_type, NULL) detail_source_type,

work_method_id,

work_method_code,

work_method_name,

output_seq_number,

decode(root_total.root_row_num, 1, operation_seq_number, -1) operation_seq_number

FROM

(

SELECT

sys_connect_by_path(output_inventory_item_id

|| '.'

|| decode(detail_source_type, 'RESIDUAL_ALLOCATION', 1, 'OVERHEAD', 2,

0)

|| '.'

|| wd_operation_id

|| '.'

|| decode(detail_source_type, 'FIXED_ALLOCATION', 4, 'OUTPUT_ALLOCATION', 5,'CARRY_FORWARD', 6, 0)

|| '.'

|| resource_id

|| '.'

|| material_inventory_item_id

|| '.'

|| decode(detail_source_type, 'CST_STD_COSTS', 1, 'CST_STD_RESOURCE_RATES', 2, 'OP_YIELD_SCRAP_ALLOCATION', 3, 0),

'/') rollupkey,

CONNECT_BY_ROOT output_inventory_item_id root_assembly_item_id,

output_inventory_item_id assembly_item_id,

LEVEL bom_level,

work_definition_id,

version_number,

organization_id,

output_inventory_item_id,

work_definition_type,

production_priority,

costing_priority,

costing_batch_output_size wd_costing_batch_output_size,

work_definition_name_id,

batch_quantity,

batch_uom_code,

batch_primary_uom_code,

work_order_id,

po_line_location_id,

val_unit_id,

cost_org_id,

cost_book_id,

effective_start_date,

scenario_rollup_header_id,

scenario_id,

rollup_header_type,

rollup_header_key,

rollup_header_level,

status_code,

source_type,

source_id,

cost_effective_start_date,

cost_effective_end_date,

cost_uom_code,

primary_uom_code,

cost_uom_conversion_factor,

currency_conversion_rate,

published_flag,

scenario_event_id,

inv_org_id,

posted_flag,

wd_operation_id,

resource_seq_number,

resource_id,

material_inventory_item_id,

material_inventory_org_id,

dtl_output_inventory_item_id,

dtl_output_inventory_org_id,

overhead_rate_id,

overhead_flag,

costing_batch_output_size,

yield_factor,

op_yield_factor,

op_rev_yield_factor,

basis_type,

quantity_usage,

output_quantity,

qty_uom_code,

cost_uom_code comp_cost_uom_code,

extended_cost,

unit_cost,

operation_unit_cost,

operation_extended_cost,

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,

detail_source_type,

output_type,

orig_output_type,

primary_flag,

work_method_id,

work_method_code,

work_method_name,

output_seq_number,

operation_seq_number

FROM

(

SELECT

cwdv.work_definition_id,

cwdv.version_number,

cwdv.organization_id,

cwdv.output_inventory_item_id,

cwdv.work_definition_type,

cwdv.production_priority,

cwdv.costing_priority,

cwdv.costing_batch_output_size wd_costing_batch_output_size,

cwdv.work_definition_name_id,

cwdv.batch_quantity,

cwdv.batch_uom_code,

cwdv.batch_primary_uom_code,

cwdi.work_order_id,

cwdi.po_line_location_id,

sc.val_unit_id,

S.cost_org_id,

S.cost_book_id,

S.effective_start_date,

srh.scenario_rollup_header_id,

srh.scenario_id,

srh.rollup_header_type,

srh.rollup_header_key,

srh.rollup_header_level,

srh.status_code,

srh.source_type,

srh.source_id,

sc.effective_start_date cost_effective_start_date,

sc.effective_end_date cost_effective_end_date,

srh.cost_uom_code,

srh.primary_uom_code,

srh.cost_uom_conversion_factor,

srh.currency_conversion_rate,

srh.published_flag,

srh.scenario_event_id,

srh.inv_org_id,

srh.posted_flag,

srd.wd_operation_id,

srd.resource_seq_number,

srd.resource_id,

srd.material_inventory_item_id,

srd.material_inventory_org_id,

srd.dtl_output_inventory_item_id,

srd.dtl_output_inventory_org_id,

srd.overhead_rate_id,

srd.overhead_flag,

srd.costing_batch_output_size,

srd.yield_factor,

srd.op_yield_factor,

srd.op_rev_yield_factor,

srd.basis_type,

srd.quantity_usage,

nvl(cwoo.output_quantity, srd.output_quantity) output_quantity,

srd.qty_uom_code,

srd.cost_uom_code comp_cost_uom_code,

srd.extended_cost,

srd.unit_cost,

srd.operation_unit_cost,

srd.operation_extended_cost,

srd.cumulative_unit_cost,

srd.cumulative_extended_cost,

srd.allocated_unit_cost,

srd.allocated_extended_cost,

srd.unallocated_unit_cost,

srd.unallocated_extended_cost,

srd.running_unit_total,

srd.running_extended_total,

srd.cost_allocation_percentage,

srd.source_type detail_source_type,

(

CASE

WHEN cwoo.output_type = 'PRODUCT'

AND cwoo.primary_flag = 'Y' THEN

'PRIMARY_PRODUCT'

WHEN cwoo.output_type = 'PRODUCT'

AND cwoo.primary_flag = 'N' THEN

'PRODUCT'

WHEN cwoo.output_type = 'BY_PRODUCT' THEN

'BY_PRODUCT'

ELSE

'PRIMARY_PRODUCT'

END

) AS output_type,

cwoo.output_type AS orig_output_type,

cwoo.primary_flag,

cwdv.work_method_id,

cwdv.work_method_code,

cwdv.work_method_name,

cwoo.output_seq_number,

srd.operation_seq_number

FROM

(

SELECT DISTINCT

cwd.scenario_id,

cwd.work_definition_id,

cwd.version_number,

cwd.organization_id,

nvl(cwd.operation_output_item_id, cwd.output_inventory_item_id) output_inventory_item_id,

cwd.work_definition_type,

cwd.production_priority,

cwd.costing_priority,

cwd.costing_batch_output_size,

cwd.work_definition_name_id,

cwd.batch_quantity,

cwd.batch_uom_code,

cwd.batch_primary_uom_code,

cwd.work_method_id,

cwd.work_method_code,

cwd.work_method_name

FROM

cst_work_definitions_v cwd,

cst_wd_operation_outputs cwoo

WHERE

nvl(cwd.operation_output_item_id, cwd.output_inventory_item_id) IS NOT NULL

AND nvl(cwd.operation_output_item_id, cwd.output_inventory_item_id) = cwoo.inventory_item_id

AND ( cwoo.status_code = 'ROLLUP_AND_PUBLISH'

OR ( cwoo.status_code IN ( 'USER_ENTERED_COST' )

AND cost_allocation_percentage IS NULL ) )

AND cwoo.work_definition_item_id = cwd.work_definition_item_id

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

) cwdv,

cst_scenario_rollup_headers srh,

cst_work_definition_items cwdi,

(

SELECT DISTINCT

scenario_id,

organization_id,

inventory_item_id,

output_type,

primary_flag,

output_quantity,

work_definition_item_id,

output_seq_number

FROM

cst_wd_operation_outputs

WHERE

status_code != 'ROLLUP_DONT_PUBLISH'

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

) cwoo,

cst_std_costs sc,

cst_scenarios S,

(

SELECT

D.scenario_rollup_header_id,

decode(substr(D.source_type, 1, 8), 'OVERHEAD', 'OVERHEAD', D.source_type) source_type,

D.wd_operation_id,

D.resource_seq_number,

D.resource_id,

D.material_inventory_item_id,

D.material_inventory_org_id,

D.output_inventory_item_id dtl_output_inventory_item_id,

D.output_inventory_org_id dtl_output_inventory_org_id,

D.overhead_rate_id,

D.overhead_flag,

D.costing_batch_output_size,

D.yield_factor,

D.op_yield_factor,

D.op_rev_yield_factor,

D.basis_type,

decode(D.overhead_rate_id, NULL, D.quantity_usage,decode(D.output_quantity, 0, 1, NULL, D.costing_batch_output_size, D.output_quantity)) quantity_usage,

decode(D.output_quantity, NULL, decode(nvl(D.costing_batch_output_size, 1), 0, 1, D.costing_batch_output_size), 0, 1, D.output_quantity) output_quantity,

D.qty_uom_code,

D.cost_uom_code,

SUM(D.extended_cost) extended_cost,

SUM(D.unit_cost) unit_cost,

SUM(decode(D.source_type, NULL, D.unit_cost, D.operation_unit_cost)) operation_unit_cost,

SUM(decode(D.source_type, NULL, D.extended_cost, D.operation_extended_cost)) operation_extended_cost,

SUM(D.cumulative_unit_cost) cumulative_unit_cost,

SUM(D.cumulative_extended_cost) cumulative_extended_cost,

SUM(D.total_allocated_unit_cost) allocated_unit_cost,

SUM(D.total_allocated_extended_cost) allocated_extended_cost,

SUM(D.unallocated_unit_cost) unallocated_unit_cost,

SUM(D.unallocated_extended_cost) unallocated_extended_cost,

decode(D.source_type, 'OUTPUT_ALLOCATION', MIN(D.running_unit_total), 'RESIDUAL_ALLOCATION', MIN(D.running_unit_total),

SUM(D.running_unit_total)) running_unit_total,

decode(D.source_type, 'OUTPUT_ALLOCATION', MIN(D.running_extended_total), 'RESIDUAL_ALLOCATION', MIN(D.running_extended_total),

SUM(D.running_extended_total)) running_extended_total,

D.this_allocation_percentage + D.other_allocation_percentage cost_allocation_percentage,

D.operation_seq_number operation_seq_number

FROM

cst_scenario_rollup_details D,

cst_std_costs std

WHERE

D.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)

GROUP BY

D.scenario_rollup_header_id,

D.source_type,

D.wd_operation_id,

D.resource_seq_number,

D.resource_id,

D.material_inventory_item_id,

D.material_inventory_org_id,

D.output_inventory_item_id,

D.output_inventory_org_id,

D.overhead_rate_id,

D.overhead_flag,

D.costing_batch_output_size,

D.yield_factor,

D.op_yield_factor,

D.op_rev_yield_factor,

D.basis_type,

D.overhead_rate_id,

decode(D.overhead_rate_id, NULL, D.quantity_usage, decode(D.output_quantity, 0, 1, NULL, D.costing_batch_output_size, D.output_quantity)),

decode(D.output_quantity, NULL, decode(nvl(D.costing_batch_output_size, 1), 0, 1, D.costing_batch_output_size),0, 1, D.output_quantity),

D.qty_uom_code,

D.cost_uom_code,

D.this_allocation_percentage + D.other_allocation_percentage,

D.operation_seq_number

) srd

WHERE

cwdv.output_inventory_item_id = srh.rollup_header_key

AND cwdv.scenario_id = srh.scenario_id

AND S.scenario_id = srh.scenario_id

AND cwdv.organization_id = srh.inv_org_id

AND srh.scenario_rollup_header_id = srd.scenario_rollup_header_id (+)

AND decode(srh.rollup_header_type, 'ITEM', srh.source_id, - 1) = sc.std_cost_id (+)

AND cwdv.scenario_id = cwoo.scenario_id (+)

AND cwdv.organization_id = cwoo.organization_id (+)

AND cwdv.output_inventory_item_id = cwoo.inventory_item_id (+)

AND cwdi.work_definition_item_id (+) = cwoo.work_definition_item_id

AND srh.rollup_header_level != 0

AND nvl(s.supply_chain_rollup_flag, 'N') = 'N'

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

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

AND S.effective_start_date >= nvl(TO_DATE(sys_context('CST_OTBI_PARAMS', 'p_scenario_effective_from_date'), 'YYYY-MM-DD'), TO_DATE('01/01/1900', 'dd/mm/yyyy'))

)

CONNECT BY PRIOR material_inventory_item_id = rollup_header_key

AND PRIOR val_unit_id = val_unit_id

AND PRIOR scenario_id = scenario_id

) rollup_det,

(

SELECT

1 root_row_num,

1 show_values

FROM

dual

UNION

SELECT

2 root_row_num,

NULL show_values

FROM

dual

) root_total

WHERE

decode(rollup_det.root_assembly_item_id, rollup_det.assembly_item_id, 2, 1) >= root_total.root_row_num

AND root_assembly_item_id = nvl(sys_context('CST_OTBI_PARAMS', 'p_root_assembly_id'), root_assembly_item_id)

GROUP BY

root_assembly_item_id,

decode(root_total.root_row_num, 1, assembly_item_id, NULL),

decode(root_total.root_row_num, 1, bom_level, 0),

work_definition_id,

version_number,

organization_id,

output_inventory_item_id,

work_definition_type,

production_priority,

costing_priority,

costing_batch_output_size,

work_definition_name_id,

batch_quantity,

batch_uom_code,

batch_primary_uom_code,

work_order_id,

po_line_location_id,

val_unit_id,

cost_org_id,

cost_book_id,

effective_start_date,

scenario_rollup_header_id,

scenario_id,

rollup_header_type,

rollup_header_key,

rollup_header_level,

status_code,

source_type,

source_id,

cost_effective_start_date,

cost_effective_end_date,

cost_uom_code,

primary_uom_code,

cost_uom_conversion_factor,

currency_conversion_rate,

published_flag,

scenario_event_id,

inv_org_id,

posted_flag,

decode(root_total.root_row_num, 1, nvl(wd_operation_id, - 1), NULL),

decode(root_total.root_row_num, 1, resource_seq_number, NULL),

decode(root_total.root_row_num, 1, resource_id, NULL),

decode(root_total.root_row_num, 1, nvl(material_inventory_item_id, dtl_output_inventory_item_id), NULL),

decode(root_total.root_row_num, 1, nvl(material_inventory_org_id, dtl_output_inventory_org_id), NULL),

decode(root_total.root_row_num, 1, overhead_rate_id, NULL),

decode(root_total.root_row_num, 1, overhead_flag, NULL),

decode(root_total.root_row_num, 1, costing_batch_output_size, NULL),

decode(root_total.root_row_num, 1, decode(nvl(yield_factor, 1), 0, 1, nvl(yield_factor, 1)), NULL),

decode(root_total.root_row_num, 1, decode(nvl(op_yield_factor, 1), 0, 1, nvl(op_yield_factor, 1)), NULL),

decode(root_total.root_row_num, 1, decode(nvl(op_rev_yield_factor, 1), 0, 1, nvl(op_rev_yield_factor, 1)), NULL),

decode(root_total.root_row_num, 1, basis_type, NULL),

decode(root_total.root_row_num, 1, decode(nvl(quantity_usage, 0), 0, 0,(quantity_usage / decode(nvl(yield_factor, 1), 0, 1, nvl(yield_factor,

1)))), output_quantity),

decode(root_total.root_row_num, 1, qty_uom_code, NULL),

cost_uom_code,

cost_allocation_percentage * show_values,

decode(show_values, 1, output_type, NULL),

decode(show_values, 1, orig_output_type, NULL),

decode(show_values, 1, primary_flag, NULL),

decode(show_values, 1, detail_source_type, NULL),

work_method_id,

work_method_code,

work_method_name,

output_seq_number,

decode(root_total.root_row_num, 1, operation_seq_number, -1),

root_total.root_row_num