CST_STD_COSTS_EFF_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
STD_COST_ID SCENARIO_ID COST_ORG_ID COST_BOOK_ID INVENTORY_ITEM_ID INVENTORY_ORG_ID VAL_STRUCTURE_ID COST_PROFILE_ID VAL_UNIT_ID EFFECTIVE_START_DATE EFFECTIVE_END_DATE STATUS_CODE TOTAL_COST CURRENCY_CODE UOM_CODE LAST_USED_DATE

Query

SQL_Statement

SELECT MAX(std_cost_id) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) std_cost_id, scenario_id, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, val_structure_id, cost_profile_id, val_unit_id, MAX(effective_start_date) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) effective_start_date, MAX(effective_end_date) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) effective_end_date, MAX(status_code) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) status_code, MAX(total_cost) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) total_cost, currency_code, MAX(uom_code) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) uom_code, MAX(last_used_date) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) last_used_date FROM ( SELECT CAST('C' AS VARCHAR2(1)) AS value_type, std_cost_id, scenario_id, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, val_structure_id, val_unit_id, cost_profile_id, effective_start_date, effective_end_date, status_code, total_cost, currency_code, uom_code, last_used_date FROM cst_std_costs UNION ALL SELECT CAST('P' AS VARCHAR2(1)) AS value_type, csc.std_cost_id, cs.scenario_id, csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, csc.inventory_org_id, csc.val_structure_id, csc.val_unit_id, csc.cost_profile_id, csc.effective_start_date, csc.effective_end_date, csc.status_code, csc.total_cost, csc.currency_code, csc.uom_code, csc.last_used_date FROM cst_std_costs csc, cst_scenarios cs WHERE cs.cost_org_id = csc.cost_org_id AND cs.cost_book_id = csc.cost_book_id AND cs.scenario_id <> csc.scenario_id AND cs.effective_start_date BETWEEN csc.effective_start_date AND csc.effective_end_date AND csc.status_code = 'PUBLISHED' AND cs.scenario_type = 'STANDARD' ) GROUP BY scenario_id, cost_org_id, cost_book_id, inventory_item_id, inventory_org_id, val_structure_id, cost_profile_id, val_unit_id, currency_code UNION ALL SELECT csc.std_cost_id, cs.scenario_id, csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, csc.inventory_org_id, csc.val_structure_id, csc.val_unit_id, csc.cost_profile_id, csc.effective_start_date, csc.effective_end_date, csc.status_code, csc.total_cost, csc.currency_code, csc.uom_code, csc.last_used_date FROM cst_std_costs csc, cst_scenarios cs WHERE cs.cost_org_id = csc.cost_org_id AND cs.cost_book_id = csc.cost_book_id AND cs.scenario_id <> csc.scenario_id AND csc.status_code = 'PUBLISHED' AND cs.scenario_type = 'CONFIGURED_ITEMS' AND EXISTS ( SELECT 'Standard Costs valid on Purchase Order Date' FROM cst_scenario_purchase_orders cspo, cst_planning_purchase_orders cppo WHERE cspo.scenario_id = cs.scenario_id AND cppo.po_line_location_id = cspo.po_line_location_id AND TRUNC(cppo.approved_date) BETWEEN csc.effective_start_date AND csc.effective_end_date UNION ALL SELECT 'Standard Costs valid on Work Order Date' FROM cst_scenario_work_orders cswo, cst_planning_work_orders cpwo WHERE cswo.scenario_id = cs.scenario_id AND cpwo.work_order_id = cswo.work_order_id AND TRUNC(COALESCE(cpwo.work_definition_as_of_date, cpwo.planned_start_date, cpwo.actual_start_date, cpwo.released_date)) BETWEEN csc.effective_start_date AND csc.effective_end_date )