CST_STD_RESOURCE_RATES_EFF_V

Details

• Schema: FUSION

• Object owner: CST

• Object type: VIEW

Columns

Name
STD_RESOURCE_RATE_ID SCENARIO_ID COST_ORG_ID COST_BOOK_ID INV_ORG_ID RESOURCE_ID UOM_CODE EFFECTIVE_START_DATE EFFECTIVE_END_DATE CURRENCY_CODE TOTAL_RATE STATUS_CODE

Query

SQL_Statement

SELECT MAX(std_resource_rate_id) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) std_resource_rate_id, scenario_id, cost_org_id, cost_book_id, inv_org_id, resource_id, MAX(uom_code) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) uom_code, 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, currency_code, MAX(total_rate) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) total_rate, MAX(status_code) KEEP (DENSE_RANK FIRST ORDER BY value_type, effective_start_date DESC) status_code FROM ( SELECT CAST('C' AS VARCHAR2(1)) AS value_type, std_resource_rate_id, scenario_id, cost_org_id, cost_book_id, inv_org_id, resource_id, uom_code, effective_start_date, effective_end_date, currency_code, total_rate, status_code FROM cst_std_resource_rates UNION ALL SELECT CAST('P' AS VARCHAR2(1)) AS value_type, csrr.std_resource_rate_id, cs.scenario_id, csrr.cost_org_id, csrr.cost_book_id, csrr.inv_org_id, csrr.resource_id, csrr.uom_code, csrr.effective_start_date, csrr.effective_end_date, csrr.currency_code, csrr.total_rate, csrr.status_code FROM cst_std_resource_rates csrr, cst_scenarios cs WHERE cs.cost_org_id = csrr.cost_org_id AND cs.cost_book_id = csrr.cost_book_id AND cs.scenario_id <> csrr.scenario_id AND nvl(cs.supply_chain_rollup_flag,'N') = 'N' AND cs.effective_start_date BETWEEN csrr.effective_start_date AND csrr.effective_end_date AND csrr.status_code = 'PUBLISHED' AND cs.scenario_type = 'STANDARD' UNION ALL SELECT CAST('P' AS VARCHAR2(1)) AS value_type, csrr.std_resource_rate_id, cs.scenario_id, csrr.cost_org_id, csrr.cost_book_id, csrr.inv_org_id, csrr.resource_id, csrr.uom_code, csrr.effective_start_date, csrr.effective_end_date, csrr.currency_code, csrr.total_rate, csrr.status_code FROM cst_std_resource_rates csrr, cst_scenarios cs, (select distinct scenario_id,cost_org_id,cost_book_id from cst_scenario_drivers) drv WHERE drv.cost_org_id = csrr.cost_org_id AND drv.cost_book_id = csrr.cost_book_id AND cs.scenario_id <> csrr.scenario_id AND drv.scenario_id <> csrr.scenario_id AND cs.scenario_id = drv.scenario_id AND cs.supply_chain_rollup_flag = 'Y' AND cs.effective_start_date BETWEEN csrr.effective_start_date AND csrr.effective_end_date AND csrr.status_code = 'PUBLISHED' AND cs.scenario_type = 'STANDARD' ) GROUP BY scenario_id, cost_org_id, cost_book_id, inv_org_id, resource_id, currency_code UNION ALL SELECT csrr.std_resource_rate_id, cs.scenario_id, csrr.cost_org_id, csrr.cost_book_id, csrr.inv_org_id, csrr.resource_id, csrr.uom_code, csrr.effective_start_date, csrr.effective_end_date, csrr.currency_code, csrr.total_rate, csrr.status_code FROM cst_std_resource_rates csrr, cst_scenarios cs WHERE cs.cost_org_id = csrr.cost_org_id AND cs.cost_book_id = csrr.cost_book_id AND cs.scenario_id <> csrr.scenario_id AND csrr.status_code = 'PUBLISHED' AND cs.scenario_type = 'CONFIGURED_ITEMS' AND EXISTS ( SELECT 'Resources Rates 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 csrr.effective_start_date AND csrr.effective_end_date UNION ALL SELECT 'Resource Rates 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 csrr.effective_start_date AND csrr.effective_end_date )