CREATE OR REPLACE VIEW QCC_COST_REVAL_V
(COST_ORG, ON_HAND_ORG, ITEM_NAME, ITEM_DESC, COST_CAT_1, 
 COST_CAT_2, COST_CAT_3, COST_CATEGORY, COST_TYPE_1, ITEM_COST_1, 
 MTL_COST_1, COST_TYPE_2, ITEM_COST_2, MTL_COST_2, ON_HAND_QTY, 
 MAKE_BUY_FLAG)
AS 
select
  cost_orgs.organization_code || ' - ' || cost_orgs.organization_name cost_org,
  on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name on_hand_org,
  msi.segment1 item_name,
  msi.description item_desc,
  mcb.segment1 cost_cat_1,
  mcb.segment2 cost_cat_2,
  mcb.segment3 cost_cat_3,
  mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3 cost_category,
  upper(ctf1.cost_type) cost_type_1,
  cicf1.item_cost item_cost_1,
  cicf1.material_cost mtl_cost_1,
  upper(ctf2.cost_type) cost_type_2,
  cicf2.item_cost item_cost_2,
  cicf2.material_cost mtl_cost_2,
  sum(moh.transaction_quantity) on_hand_qty,
  decode(msi.planning_make_buy_code,1,'M','B') make_buy_flag
from
--  mtl_parameters mp,
  cst_item_costs cicf1,
  cst_cost_types ctf1,
  cst_item_costs cicf2,
  cst_cost_types ctf2,
  mtl_onhand_quantities moh,
  inv.mtl_categories_b mcb,
  inv.mtl_item_categories mic,
--  inv.mtl_category_sets_tl mcst,
  org_organization_definitions cost_orgs,
  org_organization_definitions on_hand_orgs,
  inv.mtl_system_items_b msi
where 1=1
and cost_orgs.organization_id = msi.organization_id
and cicf1.organization_id = msi.organization_id
and cicf1.inventory_item_id = msi.inventory_item_id
and ctf1.cost_type_id = cicf1.cost_type_id --this should be set to frozen
and cicf2.organization_id = msi.organization_id  --cross-product between sets will be resolved by Discoverer parameters
and cicf2.inventory_item_id = msi.inventory_item_id
and ctf2.cost_type_id = cicf2.cost_type_id
and moh.organization_id = on_hand_orgs.ORGANIZATION_ID
and moh.inventory_item_id = msi.inventory_item_id
and mic.organization_id (+) = msi.organization_id
and mic.inventory_item_id (+) = msi.inventory_item_id
and mic.category_set_id (+) = 1100000006 --inventory cost category
and mcb.category_id (+)= mic.category_id -- the inventory cost category consisists of mcb. segment1,2 and 3
--and mcst.CATEGORY_SET_ID = mic.CATEGORY_SET_ID
--and mcst.CATEGORY_SET_NAME = 'QC INVENTORY CATEGORY'
--and msi.inventory_item_id = 512
--and cicf1.COST_TYPE_ID = 1
--and cicf2.COST_TYPE_ID = 1101
--and msi.ORGANIZATION_ID = 89
group by
  cost_orgs.organization_code || ' - ' || cost_orgs.organization_name,
  on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name,
  msi.segment1,
  msi.description,
  mcb.segment1,
  mcb.segment2,
  mcb.segment3,
  mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3,
  upper(ctf1.cost_type),
  cicf1.item_cost,
  cicf1.material_cost,
  upper(ctf2.cost_type),
  cicf2.item_cost,
  cicf2.material_cost,
  decode(msi.planning_make_buy_code,1,'M','B')
 --order by msi.SEGMENT1
/