CST_ROLLED_UP_COST_JUNITS_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name

SCENARIO_ID OUTPUT_INVENTORY_ITEM_ID DISPLAY RESOURCE_SEQ_NUMBER ROLLUP_HEADER_KEY ROLLUP_HEADER_TYPE PARENT_ROLLUP_HEADER_KEY PARENT_ROLLUP_HEADER_TYPE ITEM_DESCRIPTION WORK_DEFINITION_NAME OPERATION_SEQ_NUMBER OPERATION_TYPE QUANTITY UNIT_OF_MEASURE BASIS_TYPE MATERIAL_COST RESOURCE_COST OVERHEAD_COST UNIT_COST EXTENDED_COST INVENTORY_ITEM_ID ORGANIZATION_ID SOURCE_TYPE STATUS_CODE REFERENCE_TYPE ITEM_STRUCTURE_OR_FORMULA_NAME PLANT_ORG_NAME ABSORPTION_TYPE OVERHEAD_RATE OVERHEAD_RATE_DETAILS WORK_METHOD_CODE WORK_METHOD_NAME OUTPUT_TYPE WORK_DEFINITION_HEADER_NAME OUTPUT_ITEM BATCH_QUANTITY BATCH_QTY_UOM_CODE COSTING_BATCH_OUTPUT_SIZE OPERATION_UNIT_COST OPERATION_EXTENDED_COST RUNNING_TOTAL COST_ALLOCATION_PERCENTAGE COST_ALLOCATION_FIXED OUTPUT_COST COST_ALLOCATION_SEQ OUTPUT_SEQ_NUMBER DISPLAY_SEQ POSTED_FLAG WD_OPERATION_ID CURRENCY_CODE CURR_PRECISION CURR_EXTENDED_PRECISION CURRENCY_NAME CST_MSC_SRC_RULE_ID SOURCING_RULE_NAME RULE_RANK RULE_TYPE SUPPLIER SUPPLIER_SITE SOURCE_INV_ORG_ID SOURCE_ORG ALLOCATION STD_TXFR_COST_RULE_ID TRANSFER_CHARGE_RULE_NAME ALLOCATED_MATERIAL_COST ALLOCATED_RESOURCE_COST ALLOCATED_OVERHEAD_COST ALLOCATED_EXTENDED_COST ENTERED_UNIT_COST COST_ORG_LEVEL_COST COSTING_QUANTITY COST_UOM_CODE COST_UNIT_OF_MEASURE COST_UOM_CONVERSION_FACTOR OP_YIELD_FACTOR OP_REV_YIELD_FACTOR OP_CUM_YIELD_FACTOR SCENARIO_NUMBER PRODUCTION_LINE_NAME SOURCING_RULE_COUNT

Query

SQL_Statement

SELECT scenario_id, output_inventory_item_id, display, resource_seq_number, rollup_header_key, rollup_header_type, parent_rollup_header_key, parent_rollup_header_type, item_description, work_definition_name, operation_seq_number, operation_type, quantity, unit_of_measure, basis_type, material_cost, resource_cost, overhead_cost, unit_cost, extended_cost, inventory_item_id, organization_id, source_type, status_code, reference_type, item_structure_or_formula_name, plant_org_name, absorption_type, overhead_rate, overhead_rate_details, work_method_code, work_method_name, output_type, work_definition_header_name, output_item, batch_quantity, batch_qty_uom_code, costing_batch_output_size, operation_unit_cost, operation_extended_cost, running_total, cost_allocation_percentage, cost_allocation_fixed, output_cost, cost_allocation_seq, output_seq_number, display_seq, posted_flag, wd_operation_id, currency_code, curr_precision, curr_extended_precision, currency_name, cst_msc_src_rule_id, sourcing_rule_name, rule_rank, rule_type, supplier, supplier_site, source_inv_org_id, source_org, allocation, std_txfr_cost_rule_id, transfer_charge_rule_name, allocated_material_cost, allocated_resource_cost, allocated_overhead_cost, allocated_extended_cost, entered_unit_cost, cost_org_level_cost, costing_quantity, cost_uom_code, cost_unit_of_measure, cost_uom_conversion_factor, op_yield_factor, op_rev_yield_factor, op_cum_yield_factor, scenario_number, production_line_name, sourcing_rule_count FROM ( WITH cs AS ( SELECT cs.scenario_id, cs.scenario_number FROM cst_scenarios cs WHERE scenario_number LIKE 'JUNIT%' ) SELECT costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_op display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_op rollup_header_key, CAST('ITEM' AS VARCHAR2(30)) rollup_header_type, NULL parent_rollup_header_key, NULL parent_rollup_header_type, costs.item_description_op item_description, costs.work_definition_name work_definition_name, NULL operation_seq_number, NULL operation_type, nvl(costs.output_quantity, costs.costing_batch_output_size) quantity, costs.output_item_unit_of_measure unit_of_measure, NULL basis_type, to_char(costs.material_cost_op, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_op, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_op, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, to_char(costs.unit_cost_op, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) unit_cost, to_char(costs.extended_cost_op, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, costs.output_inventory_item_id inventory_item_id, costs.output_inventory_org_id organization_id, costs.source_type_op source_type, CASE WHEN costs.header_status_code != 'SUCCESS' THEN 'ERROR' ELSE 'SUCCESS' END status_code, ( CASE WHEN costs.po_line_location_id IS NOT NULL THEN 'PURCHASE_ORDER' WHEN costs.work_order_id IS NOT NULL THEN 'WORK_ORDER' WHEN costs.bill_sequence_id IS NOT NULL AND nvl(costs.work_definition_id, -1) = - 1 THEN 'ITEM_STRUCTURE' ELSE 'WORK_DEFINITION' END ) reference_type, costs.alternate_bom_designator item_structure_or_formula_name, costs.plant_org_name plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code work_method_code, costs.work_method_name work_method_name, nvl(costs.output_type, 'PRIMARY_PRODUCT') AS output_type, costs.work_definition_header_name work_definition_header_name, costs.output_item output_item, costs.batch_quantity batch_quantity, costs.batch_qty_uom batch_qty_uom_code, costs.costing_batch_output_size costing_batch_output_size, costs.operation_unit_cost_op operation_unit_cost, costs.operation_extended_cost_op operation_extended_cost, NULL running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '0' display_seq, costs.posted_flag_op posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, costs.cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, to_char(costs.cost_org_level_cost, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) cost_org_level_cost, costs.costing_quantity, costs.cost_uom_code, costs.cost_unit_of_measure, costs.cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, cs.scenario_number, costs.production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE output_item_rank = 1 AND costs.scenario_id = cs.scenario_id AND ( costs.output_status_code IS NULL OR costs.output_status_code NOT IN ( 'USER_ENTERED_COST', 'FIXED_COST_ALLOCATION' ) ) AND costs.overhead_rate_id IS NULL AND costs.operation_rank <> - 1 AND costs.currency_code = curr.currency_code UNION ALL SELECT /* Sourcing Rule Records */ costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_sr display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_sr rollup_header_key, CAST('SOURCING_RULE' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_op parent_rollup_header_key, CAST('ITEM' AS VARCHAR2(30)) parent_rollup_header_type, CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, NULL operation_seq_number, NULL operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, to_char(costs.material_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, decode(costs.sourcing_rule_type, 3, NULL, to_char(costs.unit_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0'))) unit_cost, to_char(costs.extended_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, CASE WHEN costs.sourcing_rule_type IN ( '1', '2' ) THEN 'CST_SCENARIO_ROLLUP_HEADERS' ELSE 'CST_STD_COSTS' END source_type, costs.status_code_sr status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_sr operation_unit_cost, costs.operation_extended_cost_sr operation_extended_cost, NULL running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '1' display_seq, 'X' posted_flag, CAST(- 1 AS NUMBER(18)) wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, costs.cst_msc_src_rule_id, costs.sourcing_rule_name sourcing_rule_name, costs.rule_rank rule_rank, costs.rule_type rule_type, costs.partner_name supplier, costs.partner_site_name supplier_site, costs.source_inv_org_id source_inv_org_id, costs.source_org source_org, costs.allocation_percent allocation, costs.std_txfr_cost_rule_id std_txfr_cost_rule_id, costs.transfer_charge_rule_name transfer_charge_rule_name, to_char(costs.allocated_material_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) allocated_material_cost, to_char(costs.allocated_resource_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) allocated_resource_cost, to_char(costs.allocated_overhead_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) allocated_overhead_cost, to_char(costs.allocated_extended_cost_sr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) allocated_extended_cost, decode(costs.sourcing_rule_type, 3, to_char(costs.entered_unit_cost, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')), NULL) entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, decode(costs.sourcing_rule_type, 3, costs.cost_uom_code, NULL) cost_uom_code, decode(costs.sourcing_rule_type, 3, costs.cost_unit_of_measure, NULL) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE costs.sourcing_rule_rank = 1 AND costs.scenario_id = cs.scenario_id AND costs.currency_code = curr.currency_code AND costs.cst_msc_src_rule_id IS NOT NULL UNION ALL SELECT /* SCCR Operation Records */ costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_opr display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_opr rollup_header_key, CAST('OPERATION' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_sr parent_rollup_header_key, CAST('SOURCING_RULE' AS VARCHAR2(30)) parent_rollup_header_type, CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, costs.operation_seq_number, costs.operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, to_char(costs.material_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, NULL unit_cost, to_char(costs.extended_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, 'CST_SCENARIO_ROLLUP_HEADERS' source_type, costs.status_code_opr status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_opr operation_unit_cost, costs.operation_extended_cost_opr operation_extended_cost, to_char( round(costs.running_total_opr, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '1' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, costs.op_yield_factor, costs.op_rev_yield_factor, costs.op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE nvl(costs.supply_chain_rollup_flag, 'N') = 'Y' AND costs.operation_rank = 1 AND costs.scenario_id = cs.scenario_id AND costs.currency_code = curr.currency_code AND costs.sourcing_rule_type = '2' AND sign(nvl(costs.wd_operation_id, 0)) = 1 UNION ALL SELECT /* Non-SCCR Operation Records */ costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_opr display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_opr rollup_header_key, CAST('OPERATION' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_op parent_rollup_header_key, CAST('ITEM' AS VARCHAR2(30)) parent_rollup_header_type, CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, costs.operation_seq_number, costs.operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, to_char(costs.material_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, NULL unit_cost, to_char(costs.extended_cost_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, 'CST_SCENARIO_ROLLUP_HEADERS' source_type, costs.status_code_opr status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_opr operation_unit_cost, costs.operation_extended_cost_opr operation_extended_cost, to_char( round(costs.running_total_opr, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '1' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, costs.op_yield_factor, costs.op_rev_yield_factor, costs.op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE costs.operation_rank = 1 AND costs.scenario_id = cs.scenario_id AND costs.cst_msc_src_rule_id IS NULL AND display_opr IS NOT NULL AND costs.currency_code = curr.currency_code UNION ALL SELECT /* Non-SCCR Overhead Records */ costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_overhead display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_overhead rollup_header_key, CAST('OVERHEAD' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_op parent_rollup_header_key, CAST('ITEM' AS VARCHAR2(30)) parent_rollup_header_type, CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, NULL operation_seq_number, NULL operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, to_char(costs.material_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, to_char(costs.unit_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) unit_cost, to_char(costs.extended_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, costs.source_type_overhead source_type, costs.status_code_overhead status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, costs.absorption_type AS absorption_type, costs.overhead_rate AS overhead_rate, costs.overhead_rate_details AS overhead_rate_details, costs.work_method_code work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, NULL operation_unit_cost, NULL operation_extended_cost, NULL running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '99' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND costs.status_code_overhead = 'SUCCESS' AND costs.currency_code = curr.currency_code AND costs.cst_msc_src_rule_id IS NULL AND costs.overhead_rate_id IS NOT NULL UNION ALL SELECT /* SCCR Overhead Records */ costs.scenario_id scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_overhead display, CAST(NULL AS NUMBER) resource_seq_number, costs.rollup_header_key_overhead rollup_header_key, CAST('OVERHEAD' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_sr parent_rollup_header_key, CAST('SOURCING_RULE' AS VARCHAR2(30)) parent_rollup_header_type, CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, NULL operation_seq_number, NULL operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, to_char(costs.material_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, to_char(costs.unit_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) unit_cost, to_char(costs.extended_cost_overhead, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, costs.source_type_overhead source_type, costs.status_code_overhead status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, costs.absorption_type AS absorption_type, costs.overhead_rate AS overhead_rate, costs.overhead_rate_details AS overhead_rate_details, costs.work_method_code work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, NULL operation_unit_cost, NULL operation_extended_cost, NULL running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '99' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND costs.status_code_overhead = 'SUCCESS' AND costs.currency_code = curr.currency_code AND costs.sourcing_rule_type = '2' AND costs.cst_msc_src_rule_id IS NOT NULL AND costs.overhead_rate_id IS NOT NULL UNION ALL SELECT /* Input Material Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id, costs.display_ip || CASE WHEN costs.scenario_id <> cs.scenario_id THEN ' (' || cs.scenario_number || ')' ELSE '' END display, costs.resource_seq_number resource_seq_number, costs.rollup_header_key_ip rollup_header_key, CAST( CASE WHEN(cc.sr_children_make_at = 0 AND cc.sr_children != 0) OR csc.scenario_rollup_header_id IS NULL THEN 'CST_STD_COSTS' WHEN cc.sr_children_make_at > 0 OR(csrh.rollup_header_level > 0 OR csc.scenario_rollup_header_id IS NOT NULL) THEN 'LOWER_LEVEL' WHEN costs.output_item_rank = - 2 THEN 'OP_YIELD_SCRAP_ALLOCATION' ELSE 'LOWER_LEVEL' END AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_opr parent_rollup_header_key, CAST('OPERATION' AS VARCHAR2(30)) parent_rollup_header_type, costs.item_description_ip item_description, NULL work_definition_name, costs.operation_seq_number, NULL operation_type, costs.quantity quantity, costs.unit_of_measure, costs.basis_type, to_char(costs.material_cost_ip, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_ip, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_ip, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, CASE WHEN nvl(costs.entered_unit_cost, 0) = 0 THEN to_char(costs.unit_cost_ip, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) ELSE NULL END unit_cost, to_char(costs.extended_cost_ip, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, costs.material_inventory_item_id inventory_item_id, costs.material_inventory_org_id organization_id, 'CST_STD_COSTS' source_type, costs.status_code_ip status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code, NULL work_method_name, NULL output_type, costs.work_definition_header_name, costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_ip operation_unit_cost, costs.operation_extended_cost_ip operation_extended_cost, to_char( round(costs.running_total_ip, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, NULL cost_allocation_percentage, NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '3' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, to_char(costs.entered_unit_cost, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, costs.cost_uom_code_ip cost_uom_code, costs.cost_unit_of_measure_ip cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, decode(costs.output_item_rank, -2, costs.op_yield_factor, NULL) op_yield_factor, decode(costs.output_item_rank, -2, costs.op_rev_yield_factor, NULL) op_rev_yield_factor, decode(costs.output_item_rank, -2, costs.op_cum_yield_factor, NULL) op_cum_yield_factor, cs.scenario_number, NULL production_line_name, cc.sr_children sourcing_rule_count FROM cst_scenario_rollup_costs costs, fnd_currencies_vl curr, cst_std_costs csc, cs, cst_scenario_rollup_headers csrh, LATERAL ( SELECT SUM(decode(sourcing_rule_type, 2, 1, 0)) AS sr_children_make_at, COUNT(*) AS sr_children FROM cst_scenario_rollup_costs WHERE sourcing_rule_rank = 1 AND rollup_header_key_op = costs.rollup_header_key_ip AND scenario_id = costs.scenario_id ) cc WHERE costs.input_item_rank = 1 AND costs.scenario_id = cs.scenario_id AND costs.source_id_ip = csc.std_cost_id (+) AND cs.scenario_id (+) = csc.scenario_id AND csrh.scenario_id (+) = costs.scenario_id AND csrh.rollup_header_type (+) = 'ITEM' AND csrh.inv_org_id (+) = costs.material_inventory_org_id AND csrh.rollup_header_key (+) = costs.material_inventory_item_id AND costs.material_inventory_item_id IS NOT NULL AND costs.currency_code = curr.currency_code UNION ALL SELECT /* Resource Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id , costs.display_rs display, costs.resource_seq_number, costs.rollup_header_key_rs rollup_header_key, CAST(decode(output_item_rank, -2, 'SCRAP_ALLOCATION', 'CST_STD_RESOURCE_RATES') AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_opr parent_rollup_header_key , CAST('OPERATION' AS VARCHAR2(30)) parent_rollup_header_type , CAST(NULL AS VARCHAR2(240)) item_description, NULL work_definition_name, costs.operation_seq_number, NULL operation_type, costs.quantity quantity, costs.unit_of_measure, costs.basis_type, to_char(costs.material_cost_rs, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) material_cost, to_char(costs.resource_cost_rs, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) resource_cost, to_char(costs.overhead_cost_rs, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) overhead_cost, NULL unit_cost, to_char(costs.extended_cost_rs, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) extended_cost, NULL inventory_item_id, NULL organization_id, 'CST_STD_RESOURCE_RATES' source_type, costs.status_code_rs status_code, NULL reference_type, NULL item_structure_or_formula_name , NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details , costs.work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name , costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size , costs.operation_unit_cost_rs operation_unit_cost, costs.operation_extended_cost_rs operation_extended_cost, to_char( round(costs.running_total_rs, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, NULL cost_allocation_percentage , NULL cost_allocation_fixed, NULL output_cost, NULL cost_allocation_seq, NULL output_seq_number, '4' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name , NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, to_char(costs.unit_cost_rs, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) entered_unit_cost , NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, NULL cost_uom_code, NULL cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor , decode(costs.output_item_rank, -2, costs.op_yield_factor, NULL) op_yield_factor, decode(costs.output_item_rank, -2, costs.op_rev_yield_factor, NULL) op_rev_yield_factor, decode(costs.output_item_rank, -2, costs.op_cum_yield_factor, NULL) op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, fnd_currencies_vl curr WHERE costs.resource_rank = 1 AND costs.scenario_id = cs.scenario_id AND costs.resource_id IS NOT NULL AND costs.currency_code = curr.currency_code UNION ALL SELECT /* Non-SCCR Output Item Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id, esi.item_number || CASE WHEN ( costs.work_method_code = 'PROCESS_MANUFACTURING' OR cwoo.primary_flag = 'N' ) AND costs.cost_alloc_percentage_op_opr > 0 THEN ' @ ' || ltrim(to_char(costs.cost_alloc_percentage_op_opr, '99990.99')) || ' %' WHEN costs.entered_unit_cost <> 0 THEN ' @ ' || ltrim(to_char(costs.entered_unit_cost, '9999999999990.99')) || ' ' || costs.currency_code END display, NULL resource_seq_number, CAST(- 1 AS VARCHAR2(120)) rollup_header_key, CAST('OUTPUT_ALLOCATION' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_opr parent_rollup_header_key, CAST('OPERATION' AS VARCHAR2(30)) parent_rollup_header_type, esi.description item_description, NULL work_definition_name, costs.operation_seq_number, NULL operation_type, costs.quantity_op_opr quantity, iuom.unit_of_measure unit_of_measure, NULL basis_type, NULL material_cost, NULL resource_cost, NULL overhead_cost, NULL unit_cost, NULL extended_cost, NULL inventory_item_id, costs.output_inventory_org_id organization_id, 'CST_STD_COSTS' source_type, costs.status_code_op_opr status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_op_opr operation_unit_cost, costs.operation_extended_cost_op_opr operation_extended_cost, to_char( round(costs.running_total_op_opr, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, costs.cost_alloc_percentage_op_opr cost_allocation_percentage, to_char(costs.cost_allocation_fixed_op_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) cost_allocation_fixed, to_char(costs.output_cost_op_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) output_cost, costs.cost_allocation_seq_op_opr cost_allocation_seq, cwoo.output_seq_number, '6' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, to_char(costs.entered_unit_cost, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, costs.cost_uom_code cost_uom_code, costs.cost_unit_of_measure cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cst_work_definition_items cwdi, egp_system_items_vl esi, cst_wd_operation_outputs cwoo, inv_units_of_measure iuom, fnd_currencies_vl curr, cs WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND cwoo.scenario_id = costs.scenario_id AND ( costs.work_method_code = 'PROCESS_MANUFACTURING' OR nvl(cwdi.wd_material_count, 1) > 1 ) AND cwoo.work_definition_item_id = costs.work_definition_item_id AND cwdi.work_definition_item_id = costs.work_definition_item_id AND cwoo.cst_operation_id = costs.cst_operation_id AND esi.inventory_item_id = cwoo.inventory_item_id AND esi.organization_id = cwoo.organization_id AND iuom.uom_code = cwoo.uom_code AND costs.output_inv_item_id_opr = cwoo.inventory_item_id AND costs.output_inv_org_id_opr = cwoo.organization_id AND costs.material_inventory_item_id IS NULL AND costs.currency_code = curr.currency_code AND costs.cst_msc_src_rule_id IS NULL UNION ALL SELECT /* SCCR Output Item Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id, esi.item_number || CASE WHEN ( costs.work_method_code = 'PROCESS_MANUFACTURING' OR cwoo.primary_flag = 'N' ) AND costs.cost_alloc_percentage_op_opr <> 0 THEN ' @ ' || ltrim(to_char(costs.cost_alloc_percentage_op_opr, '99990.99')) || ' %' WHEN costs.entered_unit_cost <> 0 THEN ' @ ' || ltrim(to_char(costs.entered_unit_cost, '9999999999990.99')) || ' ' || costs.currency_code END display, NULL resource_seq_number, CAST(- 1 AS VARCHAR2(120)) rollup_header_key, CAST('OUTPUT_ALLOCATION' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_opr parent_rollup_header_key, CAST('OPERATION' AS VARCHAR2(30)) parent_rollup_header_type, esi.description item_description, NULL work_definition_name, costs.operation_seq_number, NULL operation_type, costs.quantity_op_opr quantity, iuom.unit_of_measure unit_of_measure, NULL basis_type, NULL material_cost, NULL resource_cost, NULL overhead_cost, NULL unit_cost, NULL extended_cost, NULL inventory_item_id, costs.output_inventory_org_id organization_id, 'CST_STD_COSTS' source_type, costs.status_code_op_opr status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_op_opr operation_unit_cost, costs.operation_extended_cost_op_opr operation_extended_cost, to_char( round(costs.running_total_op_opr, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, costs.cost_alloc_percentage_op_opr cost_allocation_percentage, to_char(costs.cost_allocation_fixed_op_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) cost_allocation_fixed, to_char(costs.output_cost_op_opr, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) output_cost, costs.cost_allocation_seq_op_opr cost_allocation_seq, cwoo.output_seq_number, '6' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, to_char(costs.entered_unit_cost, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, costs.cost_uom_code cost_uom_code, costs.cost_unit_of_measure cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, cst_work_definition_items cwdi, egp_system_items_vl esi, cst_wd_operation_outputs cwoo, inv_units_of_measure iuom, fnd_currencies_vl curr WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND cwoo.scenario_id = costs.scenario_id AND ( costs.work_method_code = 'PROCESS_MANUFACTURING' OR nvl(cwdi.wd_material_count, 1) > 1 ) /* Bug#38231445 - As per original design before Multiple outputs, Outputs are not displayed under Operations */ AND cwoo.work_definition_item_id = costs.work_definition_item_id AND cwdi.work_definition_item_id = costs.work_definition_item_id AND cwoo.cst_operation_id = costs.cst_operation_id AND esi.inventory_item_id = cwoo.inventory_item_id AND esi.organization_id = cwoo.organization_id AND iuom.uom_code = cwoo.uom_code AND costs.output_inv_item_id_opr = cwoo.inventory_item_id AND costs.output_inv_org_id_opr = cwoo.organization_id AND costs.material_inventory_item_id IS NULL AND costs.currency_code = curr.currency_code AND costs.cst_msc_src_rule_id IS NOT NULL AND costs.sourcing_rule_type = '2' UNION ALL SELECT /* Non-SCCR Process Mfg Residual Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id, esi.item_number || '@ 100.00 %' display, NULL resource_seq_number, CAST(- 1 AS VARCHAR2(120)) rollup_header_key, CAST('RESIDUAL_ALLOCATION' AS VARCHAR2(30)) rollup_header_type, costs.rollup_header_key_op parent_rollup_header_key, CAST('ITEM' AS VARCHAR2(30)) parent_rollup_header_type, esi.description item_description, NULL work_definition_name, NULL operation_seq_number, NULL operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, NULL material_cost, NULL resource_cost, NULL overhead_cost, NULL unit_cost, NULL extended_cost, NULL inventory_item_id, NULL organization_id, NULL source_type, costs.status_code_rsdl status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_rsdl operation_unit_cost, costs.operation_extended_cost_rsdl operation_extended_cost, to_char( round(costs.running_total_rsdl, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, CAST(100 AS NUMBER) cost_allocation_percentage, NULL cost_allocation_fixed, to_char(costs.output_cost_rsdl, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) output_cost, NULL cost_allocation_seq, NULL output_seq_number, '98' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, egp_system_items_vl esi, fnd_currencies_vl curr WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND costs.work_method_code = 'PROCESS_MANUFACTURING' AND esi.inventory_item_id = costs.output_inv_item_id_opr AND esi.organization_id = costs.output_inv_org_id_opr AND costs.material_inventory_item_id IS NULL AND costs.overhead_rate_id IS NULL AND costs.cst_msc_src_rule_id IS NULL AND costs.status_code_rsdl = 'SUCCESS' AND costs.currency_code = curr.currency_code UNION ALL SELECT /* SCCR Process Mfg Residual Records */ costs.scenario_id, costs.output_inventory_item_id output_inventory_item_id, esi.item_number || ' @ 100.00 %' display, NULL resource_seq_number, CAST(- 1 AS VARCHAR2(120)) rollup_header_key, CAST('RESIDUAL_ALLOCATION' AS VARCHAR2(30)) rollup_header_type, rollup_header_key_sr parent_rollup_header_key, CAST('SOURCING_RULE' AS VARCHAR2(30)) parent_rollup_header_type, esi.description item_description, NULL work_definition_name, NULL operation_seq_number, NULL operation_type, NULL quantity, NULL unit_of_measure, NULL basis_type, NULL material_cost, NULL resource_cost, NULL overhead_cost, NULL unit_cost, NULL extended_cost, NULL inventory_item_id, NULL organization_id, NULL source_type, costs.status_code_rsdl status_code, NULL reference_type, NULL item_structure_or_formula_name, NULL AS plant_org_name, NULL AS absorption_type, NULL AS overhead_rate, NULL AS overhead_rate_details, costs.work_method_code, NULL work_method_name, NULL output_type, NULL work_definition_header_name, costs.output_item, NULL batch_quantity, NULL batch_qty_uom_code, NULL costing_batch_output_size, costs.operation_unit_cost_rsdl operation_unit_cost, costs.operation_extended_cost_rsdl operation_extended_cost, to_char( round(costs.running_total_rsdl, curr.extended_precision), 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0') ) running_total, CAST(100 AS NUMBER) cost_allocation_percentage, NULL cost_allocation_fixed, to_char(costs.output_cost_rsdl, 'FM999G999G999G990D' || rpad('0', curr.extended_precision, '0')) output_cost, NULL cost_allocation_seq, NULL output_seq_number, '98' display_seq, 'X' posted_flag, costs.wd_operation_id, costs.currency_code currency_code, curr.precision curr_precision, curr.extended_precision curr_extended_precision, curr.name currency_name, NULL cst_msc_src_rule_id, NULL sourcing_rule_name, NULL rule_rank, NULL rule_type, NULL supplier, NULL supplier_site, NULL source_inv_org_id, NULL source_org, NULL allocation, NULL std_txfr_cost_rule_id, NULL transfer_charge_rule_name, NULL allocated_material_cost, NULL allocated_resource_cost, NULL allocated_overhead_cost, NULL allocated_extended_cost, NULL entered_unit_cost, NULL cost_org_level_cost, CAST(NULL AS NUMBER) costing_quantity, CAST(NULL AS VARCHAR2(3)) cost_uom_code, CAST(NULL AS VARCHAR2(25)) cost_unit_of_measure, CAST(NULL AS NUMBER) cost_uom_conversion_factor, NULL op_yield_factor, NULL op_rev_yield_factor, NULL op_cum_yield_factor, NULL scenario_number, NULL production_line_name, NULL sourcing_rule_count FROM cst_scenario_rollup_costs costs, cs, egp_system_items_vl esi, fnd_currencies_vl curr WHERE costs.output_item_rank = - 1 AND costs.scenario_id = cs.scenario_id AND costs.work_method_code = 'PROCESS_MANUFACTURING' AND esi.inventory_item_id = costs.output_inv_item_id_opr AND esi.organization_id = costs.output_inv_org_id_opr AND costs.material_inventory_item_id IS NULL AND costs.overhead_rate_id IS NULL AND costs.cst_msc_src_rule_id IS NOT NULL AND costs.status_code_rsdl = 'SUCCESS' AND costs.currency_code = curr.currency_code ) ORDER BY scenario_id, display_seq, output_inventory_item_id, operation_seq_number, rollup_header_key