CST_WHERE_USED_ROLLUP_DTLS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
ROLLUPKEY ROOT_MATERIAL_ITEM_ID ROOT_RESOURCE_ID ASSEMBLY_ITEM_ID BOM_LEVEL TOP_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 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 BASIS_TYPE QUANTITY_USAGE QTY_UOM_CODE COMP_COST_UOM_CODE EXTENDED_COST UNIT_COST CUMULATIVE_UNIT_COST CUMULATIVE_EXTENDED_COST ALLOCATED_UNIT_COST ALLOCATED_EXTENDED_COST UNALLOCATED_UNIT_COST UNALLOCATED_EXTENDED_COST COST_ALLOCATION_PERCENTAGE OUTPUT_TYPE ORIG_OUTPUT_TYPE PRIMARY_FLAG DETAIL_SOURCE_TYPE BATCH_QUANTITY BATCH_UOM_CODE BATCH_PRIMARY_UOM_CODE WORK_METHOD_ID WORK_METHOD_CODE WORK_METHOD_NAME

Name

ROLLUPKEY

ROOT_MATERIAL_ITEM_ID

ROOT_RESOURCE_ID

ASSEMBLY_ITEM_ID

BOM_LEVEL

TOP_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

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

BASIS_TYPE

QUANTITY_USAGE

QTY_UOM_CODE

COMP_COST_UOM_CODE

EXTENDED_COST

UNIT_COST

CUMULATIVE_UNIT_COST

CUMULATIVE_EXTENDED_COST

ALLOCATED_UNIT_COST

ALLOCATED_EXTENDED_COST

UNALLOCATED_UNIT_COST

UNALLOCATED_EXTENDED_COST

COST_ALLOCATION_PERCENTAGE

OUTPUT_TYPE

ORIG_OUTPUT_TYPE

PRIMARY_FLAG

DETAIL_SOURCE_TYPE

BATCH_QUANTITY

BATCH_UOM_CODE

BATCH_PRIMARY_UOM_CODE

WORK_METHOD_ID

WORK_METHOD_CODE

WORK_METHOD_NAME

Query

SQL_Statement
SELECT sys_connect_by_path(output_inventory_item_id \|\|'.' \|\|wd_operation_id \|\|'.' \|\|material_inventory_item_id \|\|'.' \|\|resource_id, '/') rollupkey, CONNECT_BY_ROOT material_inventory_item_id root_material_item_id, CONNECT_BY_ROOT resource_id root_resource_id, output_inventory_item_id assembly_item_id, LEVEL bom_level, CONNECT_BY_ISLEAF top_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, 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, NVL(wd_operation_id, -1) 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, basis_type, quantity_usage, qty_uom_code, cost_uom_code comp_cost_uom_code, operation_extended_cost extended_cost, operation_unit_cost unit_cost, cumulative_unit_cost, cumulative_extended_cost, allocated_unit_cost, allocated_extended_cost, unallocated_unit_cost, unallocated_extended_cost, cost_allocation_percentage, output_type, orig_output_type, primary_flag, detail_source_type, batch_quantity, batch_uom_code, batch_primary_uom_code, work_method_id, work_method_code, work_method_name 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, cwdi.work_order_id, cwdi.po_line_location_id, cwdv.batch_quantity, cwdv.batch_uom_code, cwdv.batch_primary_uom_code, 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.overhead_rate_id, srd.overhead_flag, srd.costing_batch_output_size, srd.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.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 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 WHERE decode(cwd.batch_quantity, NULL, cwd.output_inventory_item_id, cwd.operation_output_item_id ) IS NOT NULL ) 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 from cst_wd_operation_outputs) cwoo, cst_std_costs sc, cst_scenarios s, ( SELECT scenario_rollup_header_id, source_type, 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, basis_type, CASE WHEN overhead_rate_id IS NOT NULL THEN costing_batch_output_size ELSE quantity_usage END quantity_usage, DECODE (output_quantity, NULL, DECODE(NVL(costing_batch_output_size, 1),0,1,costing_batch_output_size), 0, 1, output_quantity) output_quantity, qty_uom_code, cost_uom_code, SUM(extended_cost) extended_cost, SUM(unit_cost) unit_cost, SUM(DECODE(source_type, NULL, unit_cost, operation_unit_cost)) operation_unit_cost, SUM(DECODE(source_type, NULL, extended_cost, operation_extended_cost)) operation_extended_cost, SUM(cumulative_unit_cost) cumulative_unit_cost, SUM(cumulative_extended_cost) cumulative_extended_cost, SUM(allocated_unit_cost) allocated_unit_cost, SUM(allocated_extended_cost) allocated_extended_cost, SUM(unallocated_unit_cost) unallocated_unit_cost, SUM(unallocated_extended_cost) unallocated_extended_cost, cost_allocation_percentage FROM cst_scenario_rollup_details GROUP BY scenario_rollup_header_id, source_type, 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, basis_type, quantity_usage, DECODE (output_quantity, NULL, DECODE(NVL(costing_batch_output_size, 1),0,1,costing_batch_output_size), 0, 1, output_quantity), qty_uom_code, cost_uom_code, cost_allocation_percentage ) 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 ) CONNECT BY PRIOR rollup_header_key = material_inventory_item_id AND PRIOR val_unit_id = val_unit_id AND PRIOR scenario_id = scenario_id

SQL_Statement

SELECT

sys_connect_by_path(output_inventory_item_id

||'.'

||wd_operation_id

||'.'

||material_inventory_item_id

||'.'

||resource_id, '/') rollupkey,

CONNECT_BY_ROOT material_inventory_item_id root_material_item_id,

CONNECT_BY_ROOT resource_id root_resource_id,

output_inventory_item_id assembly_item_id,

LEVEL bom_level,

CONNECT_BY_ISLEAF top_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,

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,

NVL(wd_operation_id, -1) 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,

basis_type,

quantity_usage,

qty_uom_code,

cost_uom_code comp_cost_uom_code,

operation_extended_cost extended_cost,

operation_unit_cost unit_cost,

cumulative_unit_cost,

cumulative_extended_cost,

allocated_unit_cost,

allocated_extended_cost,

unallocated_unit_cost,

unallocated_extended_cost,

cost_allocation_percentage,

output_type,

orig_output_type,

primary_flag,

detail_source_type,

batch_quantity,

batch_uom_code,

batch_primary_uom_code,

work_method_id,

work_method_code,

work_method_name

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,

cwdi.work_order_id,

cwdi.po_line_location_id,

cwdv.batch_quantity,

cwdv.batch_uom_code,

cwdv.batch_primary_uom_code,

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.overhead_rate_id,

srd.overhead_flag,

srd.costing_batch_output_size,

srd.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.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

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

WHERE

decode(cwd.batch_quantity, NULL, cwd.output_inventory_item_id, cwd.operation_output_item_id ) IS NOT NULL

)

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

from cst_wd_operation_outputs) cwoo,

cst_std_costs sc,

cst_scenarios s,

(

SELECT

scenario_rollup_header_id,

source_type,

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,

basis_type,

CASE

WHEN overhead_rate_id IS NOT NULL

THEN costing_batch_output_size

ELSE quantity_usage

END quantity_usage,

DECODE (output_quantity, NULL, DECODE(NVL(costing_batch_output_size, 1),0,1,costing_batch_output_size), 0, 1, output_quantity) output_quantity,

qty_uom_code,

cost_uom_code,

SUM(extended_cost) extended_cost,

SUM(unit_cost) unit_cost,

SUM(DECODE(source_type, NULL, unit_cost, operation_unit_cost)) operation_unit_cost,

SUM(DECODE(source_type, NULL, extended_cost, operation_extended_cost)) operation_extended_cost,

SUM(cumulative_unit_cost) cumulative_unit_cost,

SUM(cumulative_extended_cost) cumulative_extended_cost,

SUM(allocated_unit_cost) allocated_unit_cost,

SUM(allocated_extended_cost) allocated_extended_cost,

SUM(unallocated_unit_cost) unallocated_unit_cost,

SUM(unallocated_extended_cost) unallocated_extended_cost,

cost_allocation_percentage

FROM

cst_scenario_rollup_details

GROUP BY

scenario_rollup_header_id,

source_type,

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,

basis_type,

quantity_usage,

DECODE (output_quantity, NULL, DECODE(NVL(costing_batch_output_size, 1),0,1,costing_batch_output_size), 0, 1, output_quantity),

qty_uom_code,

cost_uom_code,

cost_allocation_percentage

)

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

)

CONNECT BY PRIOR rollup_header_key = material_inventory_item_id

AND PRIOR val_unit_id = val_unit_id

AND PRIOR scenario_id = scenario_id