CST_SCENARIO_COMPARE_COSTS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
INVENTORY_ITEM_ID INVENTORY_ORG_ID VAL_UNIT_ID UOM_CODE GROUPING_LEVEL ONHAND_QTY COST_ELEMENT_TYPE COST_ELEMENT_CODE SET1_COST SET2_COST NO_COST_DIFFERENCE_FLAG

Name

INVENTORY_ITEM_ID

INVENTORY_ORG_ID

VAL_UNIT_ID

UOM_CODE

GROUPING_LEVEL

ONHAND_QTY

COST_ELEMENT_TYPE

COST_ELEMENT_CODE

SET1_COST

SET2_COST

NO_COST_DIFFERENCE_FLAG

Query

SQL_Statement
SELECT csc.inventory_item_id, csc.inventory_org_id, csc.val_unit_id, csc.uom_code, CASE WHEN grouping_id(csc.cost_element_type) = 0 AND grouping_id(csc.cost_element_code) = 0 THEN 'COST_ELEMENT' WHEN grouping_id(csc.cost_element_type) = 0 AND grouping_id(csc.cost_element_code) = 1 THEN 'COST_ELEMENT_TYPE' WHEN grouping_id(csc.cost_element_type) = 1 AND grouping_id(csc.cost_element_code) = 1 THEN 'ITEM' END grouping_level , MAX( (SELECT NVL(MAX(x.quantity_onhand) KEEP (DENSE_RANK LAST ORDER BY x.cost_date, x.dep_trxn_id, x.transaction_id, x.rec_trxn_id),0) onhand_qty FROM cst_transaction_layers x WHERE x.posted_flag NOT IN ('N','X','W') AND x.cost_org_id = csc.cost_org_id AND x.cost_book_id = csc.cost_book_id AND x.inventory_item_id = csc.inventory_item_id AND x.val_unit_id = csc.val_unit_id )) onhand_qty, csc.cost_element_type, csc.cost_element_code, SUM( CASE WHEN csc.set_code = 1 THEN csc.unit_cost ELSE null END) set1_cost, SUM( CASE WHEN csc.set_code = 2 THEN csc.unit_cost ELSE null END) set2_cost, csc.no_cost_difference_flag FROM (SELECT csccg.set_code, csc.cost_org_id, csc.cost_book_id, csc.inventory_item_id, csc.inventory_org_id, csc.val_unit_id, csc.uom_code, cce.cost_element_type, cce.cost_element_code, cscd.unit_cost, csccg.no_cost_difference_flag FROM (SELECT set_code, scenario_id, category_id, inventory_item_id, no_cost_difference_flag, COUNT(set_code) OVER () compare_count FROM cst_scenario_compare_costs_gt ) csccg, cst_std_costs_eff_v csc, cst_std_cost_details cscd, cst_cost_elements_vl cce WHERE csc.scenario_id = csccg.scenario_id AND csc.std_cost_id = cscd.std_cost_id AND cce.cost_element_id = cscd.cost_element_id AND csc.inventory_item_id = NVL(csccg.inventory_item_id, csc.inventory_item_id) AND (csccg.category_id IS NULL OR (csccg.category_id IS NOT NULL AND EXISTS (SELECT 'X' FROM egp_item_categories eic, egp_default_category_sets edcs WHERE edcs.functional_area_id = 5 AND edcs.category_set_id = eic.category_set_id AND eic.inventory_item_id = csc.inventory_item_id AND eic.organization_id = csc.inventory_org_id AND eic.category_id = csccg.category_id ))) AND EXISTS ( SELECT 'Item exists in Scenario' FROM cst_std_costs x, cst_scenario_compare_costs_gt y WHERE y.scenario_id = x.scenario_id AND x.inventory_item_id = csc.inventory_item_id AND x.val_unit_id = csc.val_unit_id ) UNION ALL SELECT x.set_code, x.cost_org_id, x.cost_book_id, x.inventory_item_id, x.inventory_org_id, x.val_unit_id, x.uom_code, cce.cost_element_type, cce.cost_element_code, cscd.unit_cost, x.no_cost_difference_flag FROM ( SELECT 2 set_code, csc2.cost_org_id, csc2.cost_book_id, csc2.inventory_item_id, csc2.inventory_org_id, csc2.val_unit_id, csc2.uom_code, MAX(csc2.std_cost_id) KEEP (DENSE_RANK FIRST ORDER BY csc2.effective_start_date DESC) std_cost_id, csccg.no_cost_difference_flag FROM (SELECT set_code, scenario_id, category_id, inventory_item_id, no_cost_difference_flag, COUNT(set_code) OVER () compare_count FROM cst_scenario_compare_costs_gt ) csccg, cst_std_costs_eff_v csc, cst_std_costs_eff_v csc2 WHERE csccg.compare_count = 1 AND csc.scenario_id = csccg.scenario_id AND csc.cost_org_id = csc2.cost_org_id AND csc.cost_book_id = csc2.cost_book_id AND csc.inventory_item_id = csc2.inventory_item_id AND csc.inventory_org_id = csc2.inventory_org_id AND csc.val_unit_id = csc2.val_unit_id AND to_date(sysdate) >= csc2.effective_start_date AND csc2.status_code = 'PUBLISHED' AND csc.inventory_item_id = NVL(csccg.inventory_item_id, csc.inventory_item_id) AND (csccg.category_id IS NULL OR (csccg.category_id IS NOT NULL AND EXISTS (SELECT 'X' FROM egp_item_categories eic, egp_default_category_sets edcs WHERE edcs.functional_area_id = 5 AND edcs.category_set_id = eic.category_set_id AND eic.inventory_item_id = csc.inventory_item_id AND eic.organization_id = csc.inventory_org_id AND eic.category_id = csccg.category_id ))) AND EXISTS ( SELECT 'Item exists in Scenario' FROM cst_std_costs x, cst_scenario_compare_costs_gt y WHERE y.scenario_id = x.scenario_id AND x.inventory_item_id = csc.inventory_item_id AND x.val_unit_id = csc.val_unit_id ) GROUP BY csccg.set_code, csc2.cost_org_id, csc2.cost_book_id, csc2.inventory_item_id, csc2.inventory_org_id, csc2.val_unit_id, csc2.uom_code, csccg.no_cost_difference_flag ) x, cst_std_cost_details cscd, cst_cost_elements_vl cce WHERE x.std_cost_id = cscd.std_cost_id AND cce.cost_element_id = cscd.cost_element_id ) csc GROUP BY inventory_item_id, inventory_org_id, val_unit_id, uom_code, no_cost_difference_flag, ROLLUP(cost_element_type, cost_element_code)

SQL_Statement

SELECT csc.inventory_item_id,

csc.inventory_org_id,

csc.val_unit_id,

csc.uom_code,

CASE

WHEN grouping_id(csc.cost_element_type) = 0

AND grouping_id(csc.cost_element_code) = 0

THEN 'COST_ELEMENT'

WHEN grouping_id(csc.cost_element_type) = 0

AND grouping_id(csc.cost_element_code) = 1

THEN 'COST_ELEMENT_TYPE'

WHEN grouping_id(csc.cost_element_type) = 1

AND grouping_id(csc.cost_element_code) = 1

THEN 'ITEM'

END grouping_level ,

MAX(

(SELECT NVL(MAX(x.quantity_onhand) KEEP (DENSE_RANK LAST

ORDER BY x.cost_date, x.dep_trxn_id, x.transaction_id, x.rec_trxn_id),0) onhand_qty

FROM cst_transaction_layers x

WHERE x.posted_flag NOT IN ('N','X','W')

AND x.cost_org_id = csc.cost_org_id

AND x.cost_book_id = csc.cost_book_id

AND x.inventory_item_id = csc.inventory_item_id

AND x.val_unit_id = csc.val_unit_id

)) onhand_qty,

csc.cost_element_type,

csc.cost_element_code,

SUM(

CASE

WHEN csc.set_code = 1

THEN csc.unit_cost

ELSE null

END) set1_cost,

SUM(

CASE

WHEN csc.set_code = 2

THEN csc.unit_cost

ELSE null

END) set2_cost,

csc.no_cost_difference_flag

FROM

(SELECT csccg.set_code,

csc.cost_org_id,

csc.cost_book_id,

csc.inventory_item_id,

csc.inventory_org_id,

csc.val_unit_id,

csc.uom_code,

cce.cost_element_type,

cce.cost_element_code,

cscd.unit_cost,

csccg.no_cost_difference_flag

FROM

(SELECT set_code,

scenario_id,

category_id,

inventory_item_id,

no_cost_difference_flag,

COUNT(set_code) OVER () compare_count

FROM cst_scenario_compare_costs_gt

) csccg,

cst_std_costs_eff_v csc,

cst_std_cost_details cscd,

cst_cost_elements_vl cce

WHERE csc.scenario_id = csccg.scenario_id

AND csc.std_cost_id = cscd.std_cost_id

AND cce.cost_element_id = cscd.cost_element_id

AND csc.inventory_item_id = NVL(csccg.inventory_item_id, csc.inventory_item_id)

AND (csccg.category_id IS NULL

OR (csccg.category_id IS NOT NULL

AND EXISTS

(SELECT 'X'

FROM egp_item_categories eic,

egp_default_category_sets edcs

WHERE edcs.functional_area_id = 5

AND edcs.category_set_id = eic.category_set_id

AND eic.inventory_item_id = csc.inventory_item_id

AND eic.organization_id = csc.inventory_org_id

AND eic.category_id = csccg.category_id

)))

AND EXISTS

(

SELECT

'Item exists in Scenario'

FROM

cst_std_costs x,

cst_scenario_compare_costs_gt y

WHERE

y.scenario_id = x.scenario_id

AND x.inventory_item_id = csc.inventory_item_id

AND x.val_unit_id = csc.val_unit_id

)

UNION ALL

SELECT x.set_code,

x.cost_org_id,

x.cost_book_id,

x.inventory_item_id,

x.inventory_org_id,

x.val_unit_id,

x.uom_code,

cce.cost_element_type,

cce.cost_element_code,

cscd.unit_cost,

x.no_cost_difference_flag

FROM

( SELECT 2 set_code,

csc2.cost_org_id,

csc2.cost_book_id,

csc2.inventory_item_id,

csc2.inventory_org_id,

csc2.val_unit_id,

csc2.uom_code,

MAX(csc2.std_cost_id) KEEP (DENSE_RANK FIRST

ORDER BY csc2.effective_start_date DESC) std_cost_id,

csccg.no_cost_difference_flag

FROM

(SELECT set_code,

scenario_id,

category_id,

inventory_item_id,

no_cost_difference_flag,

COUNT(set_code) OVER () compare_count

FROM cst_scenario_compare_costs_gt

) csccg,

cst_std_costs_eff_v csc,

cst_std_costs_eff_v csc2

WHERE csccg.compare_count = 1

AND csc.scenario_id = csccg.scenario_id

AND csc.cost_org_id = csc2.cost_org_id

AND csc.cost_book_id = csc2.cost_book_id

AND csc.inventory_item_id = csc2.inventory_item_id

AND csc.inventory_org_id = csc2.inventory_org_id

AND csc.val_unit_id = csc2.val_unit_id

AND to_date(sysdate) >= csc2.effective_start_date

AND csc2.status_code = 'PUBLISHED'

AND csc.inventory_item_id = NVL(csccg.inventory_item_id, csc.inventory_item_id)

AND (csccg.category_id IS NULL

OR (csccg.category_id IS NOT NULL

AND EXISTS

(SELECT 'X'

FROM egp_item_categories eic,

egp_default_category_sets edcs

WHERE edcs.functional_area_id = 5

AND edcs.category_set_id = eic.category_set_id

AND eic.inventory_item_id = csc.inventory_item_id

AND eic.organization_id = csc.inventory_org_id

AND eic.category_id = csccg.category_id

)))

AND EXISTS

(

SELECT

'Item exists in Scenario'

FROM

cst_std_costs x,

cst_scenario_compare_costs_gt y

WHERE

y.scenario_id = x.scenario_id

AND x.inventory_item_id = csc.inventory_item_id

AND x.val_unit_id = csc.val_unit_id

)

GROUP BY csccg.set_code,

csc2.cost_org_id,

csc2.cost_book_id,

csc2.inventory_item_id,

csc2.inventory_org_id,

csc2.val_unit_id,

csc2.uom_code,

csccg.no_cost_difference_flag

) x,

cst_std_cost_details cscd,

cst_cost_elements_vl cce

WHERE x.std_cost_id = cscd.std_cost_id

AND cce.cost_element_id = cscd.cost_element_id

) csc

GROUP BY inventory_item_id,

inventory_org_id,

val_unit_id,

uom_code,

no_cost_difference_flag,

ROLLUP(cost_element_type, cost_element_code)