CST_STD_OVERHEAD_RATES_EFF

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
STD_OVERHEAD_RATE_ID SCENARIO_ID COST_ORG_ID COST_BOOK_ID INVENTORY_ORG_ID OVERHEAD_RATE_TYPE CATEGORY_SET_ID CATEGORY_ID INVENTORY_ITEM_ID WORK_CENTER_ID RESOURCE_TYPE EFFECTIVE_START_DATE EFFECTIVE_END_DATE TOTAL_RATE CURRENCY_CODE STATUS_CODE

Name

STD_OVERHEAD_RATE_ID

SCENARIO_ID

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ORG_ID

OVERHEAD_RATE_TYPE

CATEGORY_SET_ID

CATEGORY_ID

INVENTORY_ITEM_ID

WORK_CENTER_ID

RESOURCE_TYPE

EFFECTIVE_START_DATE

EFFECTIVE_END_DATE

TOTAL_RATE

CURRENCY_CODE

STATUS_CODE

Query

SQL_Statement
SELECT MAX(std_overhead_rate_id) KEEP(DENSE_RANK FIRST ORDER BY value_type) std_overhead_rate_id, scenario_id, cost_org_id, cost_book_id, inventory_org_id, overhead_rate_type, category_set_id, category_id, inventory_item_id, work_center_id, resource_type, MAX(effective_start_date) KEEP(DENSE_RANK FIRST ORDER BY value_type) effective_start_date, MAX(effective_end_date) KEEP(DENSE_RANK FIRST ORDER BY value_type) effective_end_date, MAX(total_rate) KEEP(DENSE_RANK FIRST ORDER BY value_type) total_rate, currency_code, MAX(status_code) KEEP(DENSE_RANK FIRST ORDER BY value_type) status_code FROM ( SELECT CAST('C' AS VARCHAR2(1)) AS value_type, csor.std_overhead_rate_id, csor.scenario_id, csor.cost_org_id, csor.cost_book_id, csor.inventory_org_id, csor.overhead_rate_type, csor.category_set_id, csor.category_id, csor.inventory_item_id, csor.work_center_id, csor.resource_type, csor.effective_start_date, csor.effective_end_date, csor.total_rate, csor.currency_code, csor.status_code FROM cst_std_overhead_rates csor UNION ALL SELECT CAST('P' AS VARCHAR2(1)) AS value_type, csor.std_overhead_rate_id, cs.scenario_id, csor.cost_org_id, csor.cost_book_id, csor.inventory_org_id, csor.overhead_rate_type, csor.category_set_id, csor.category_id, csor.inventory_item_id, csor.work_center_id, csor.resource_type, csor.effective_start_date, csor.effective_end_date, csor.total_rate, csor.currency_code, csor.status_code FROM cst_std_overhead_rates csor, cst_scenarios cs WHERE cs.cost_org_id = csor.cost_org_id AND cs.cost_book_id = csor.cost_book_id AND nvl(cs.supply_chain_rollup_flag, 'N') = 'N' AND cs.scenario_id <> csor.scenario_id AND cs.effective_start_date BETWEEN csor.effective_start_date AND csor.effective_end_date AND csor.status_code = 'PUBLISHED' UNION ALL SELECT CAST('P' AS VARCHAR2(1)) AS value_type, csor.std_overhead_rate_id, cs.scenario_id, csor.cost_org_id, csor.cost_book_id, csor.inventory_org_id, csor.overhead_rate_type, csor.category_set_id, csor.category_id, csor.inventory_item_id, csor.work_center_id, csor.resource_type, csor.effective_start_date, csor.effective_end_date, csor.total_rate, csor.currency_code, csor.status_code FROM cst_std_overhead_rates csor, cst_scenarios cs, ( SELECT DISTINCT scenario_id, cost_org_id, cost_book_id FROM cst_scenario_drivers ) drv WHERE drv.cost_org_id = csor.cost_org_id AND drv.cost_book_id = csor.cost_book_id AND cs.supply_chain_rollup_flag = 'Y' AND cs.scenario_id <> csor.scenario_id AND drv.scenario_id <> csor.scenario_id AND cs.scenario_id = drv.scenario_id AND cs.effective_start_date BETWEEN csor.effective_start_date AND csor.effective_end_date AND csor.status_code = 'PUBLISHED' ) GROUP BY scenario_id, cost_org_id, cost_book_id, inventory_org_id, overhead_rate_type, category_set_id, category_id, inventory_item_id, work_center_id, resource_type, currency_code

SQL_Statement

SELECT

MAX(std_overhead_rate_id) KEEP(DENSE_RANK FIRST ORDER BY value_type) std_overhead_rate_id,

scenario_id,

cost_org_id,

cost_book_id,

inventory_org_id,

overhead_rate_type,

category_set_id,

category_id,

inventory_item_id,

work_center_id,

resource_type,

MAX(effective_start_date) KEEP(DENSE_RANK FIRST ORDER BY value_type) effective_start_date,

MAX(effective_end_date) KEEP(DENSE_RANK FIRST ORDER BY value_type) effective_end_date,

MAX(total_rate) KEEP(DENSE_RANK FIRST ORDER BY value_type) total_rate,

currency_code,

MAX(status_code) KEEP(DENSE_RANK FIRST ORDER BY value_type) status_code

FROM

(

SELECT

CAST('C' AS VARCHAR2(1)) AS value_type,

csor.std_overhead_rate_id,

csor.scenario_id,

csor.cost_org_id,

csor.cost_book_id,

csor.inventory_org_id,

csor.overhead_rate_type,

csor.category_set_id,

csor.category_id,

csor.inventory_item_id,

csor.work_center_id,

csor.resource_type,

csor.effective_start_date,

csor.effective_end_date,

csor.total_rate,

csor.currency_code,

csor.status_code

FROM

cst_std_overhead_rates csor

UNION ALL

SELECT

CAST('P' AS VARCHAR2(1)) AS value_type,

csor.std_overhead_rate_id,

cs.scenario_id,

csor.cost_org_id,

csor.cost_book_id,

csor.inventory_org_id,

csor.overhead_rate_type,

csor.category_set_id,

csor.category_id,

csor.inventory_item_id,

csor.work_center_id,

csor.resource_type,

csor.effective_start_date,

csor.effective_end_date,

csor.total_rate,

csor.currency_code,

csor.status_code

FROM

cst_std_overhead_rates csor,

cst_scenarios cs

WHERE

cs.cost_org_id = csor.cost_org_id

AND cs.cost_book_id = csor.cost_book_id

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

AND cs.scenario_id <> csor.scenario_id

AND cs.effective_start_date BETWEEN csor.effective_start_date AND csor.effective_end_date

AND csor.status_code = 'PUBLISHED'

UNION ALL

SELECT

CAST('P' AS VARCHAR2(1)) AS value_type,

csor.std_overhead_rate_id,

cs.scenario_id,

csor.cost_org_id,

csor.cost_book_id,

csor.inventory_org_id,

csor.overhead_rate_type,

csor.category_set_id,

csor.category_id,

csor.inventory_item_id,

csor.work_center_id,

csor.resource_type,

csor.effective_start_date,

csor.effective_end_date,

csor.total_rate,

csor.currency_code,

csor.status_code

FROM

cst_std_overhead_rates csor,

cst_scenarios cs,

(

SELECT DISTINCT

scenario_id,

cost_org_id,

cost_book_id

FROM

cst_scenario_drivers

) drv

WHERE

drv.cost_org_id = csor.cost_org_id

AND drv.cost_book_id = csor.cost_book_id

AND cs.supply_chain_rollup_flag = 'Y'

AND cs.scenario_id <> csor.scenario_id

AND drv.scenario_id <> csor.scenario_id

AND cs.scenario_id = drv.scenario_id

AND cs.effective_start_date BETWEEN csor.effective_start_date AND csor.effective_end_date

AND csor.status_code = 'PUBLISHED'

)

GROUP BY

scenario_id,

cost_org_id,

cost_book_id,

inventory_org_id,

overhead_rate_type,

category_set_id,

category_id,

inventory_item_id,

work_center_id,

resource_type,

currency_code