CST Inventory Value - Multi-Organization (Item Costs)

select
gsob.name ledger,
haou.name operating_unit,
mp.organization_code,
msi.concatenated_segments item,
msi.description item_description,
mc.concatenated_segments category,
xxen_util.meaning(msi.item_type,'ITEM_TYPE',3) user_item_type,
msi.primary_uom_code uom,
msi.inventory_item_status_code item_status,
xxen_util.meaning(cict.inventory_asset_flag,'SYS_YES_NO',700) inventory_asset,
xxen_util.meaning(msi.planning_make_buy_code,'MTL_PLANNING_MAKE_BUY',700) make_buy,
decode(msi.inventory_planning_code,6, xxen_util.meaning(nvl(msi.mrp_planning_code,6),'MRP_PLANNING_CODE',700), xxen_util.meaning(nvl(msi.inventory_planning_code,6),'MTL_MATERIAL_PLANNING',700)) planning_method,
decode(:p_item_revision, 1, ciqt.revision, null) revision,
ciqt.subinventory_code subinventory,
sec.description subinventory_desc,
xxen_util.meaning(sec.asset_inventory,'SYS_YES_NO',700) asset_subinventory,
ccg.cost_group,
case
when ciqt.qty_source in (3,4,5) then 'Onhand'
when ciqt.qty_source in (6,7,8) then 'Intransit'
else 'Receiving'
end quantity_type,
round(sum(nvl(ciqt.rollback_qty,0)),:p_qty_precision) total_qty,
--
round(nvl(cict.item_cost,0)*:p_exchange_rate, :p_ext_precision) item_unit_cost,
--
sum(ciqt.rollback_qty * nvl(cict.item_cost,0)               * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) total_cost,
sum(ciqt.rollback_qty * nvl(cict.material_cost,0)           * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) material_cost,
sum(ciqt.rollback_qty * nvl(cict.material_overhead_cost,0)  * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) material_overhead_cost,
sum(ciqt.rollback_qty * nvl(cict.resource_cost,0)           * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) resource_cost,
sum(ciqt.rollback_qty * nvl(cict.outside_processing_cost,0) * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) outside_processing_cost,
sum(ciqt.rollback_qty * nvl(cict.overhead_cost,0)           * case when ciqt.qty_source in (3,4,5) then decode(sec.asset_inventory,2,0,1) else 1 end * :p_exchange_rate) overhead_cost,
--
round(sum(case when ciqt.qty_source in (3,4,5) then nvl(ciqt.rollback_qty,0) else null end),:p_qty_precision) onhand_qty,
sum(case when ciqt.qty_source in (3,4,5) then nvl(ciqt.rollback_qty,0) else null end * nvl(cict.item_cost,0) * decode(sec.asset_inventory,2,0,1) * :p_exchange_rate) onhand_cost,
round(sum(case when ciqt.qty_source in (6,7,8) then nvl(ciqt.rollback_qty,0) else null end),:p_qty_precision) intransit_qty,
sum(case when ciqt.qty_source in (6,7,8) then nvl(ciqt.rollback_qty,0) else null end * nvl(cict.item_cost,0) * :p_exchange_rate) intransit_cost,
round(sum(case when ciqt.qty_source in (9,10) then nvl(ciqt.rollback_qty,0) else null end),:p_qty_precision)  receiving_qty,
sum(case when ciqt.qty_source in (9,10) then nvl(ciqt.rollback_qty,0) else null end * nvl(cict.item_cost,0) * :p_exchange_rate) receiving_cost,
--
msi.concatenated_segments || ' - ' || msi.description item_label,
ciqt.subinventory_code || ' - ' || sec.description subinventory_label,
mp.organization_code || ' - ' || ood.organization_name organization_label
from
mtl_categories_b_kfv mc,
mtl_system_items_vl msi,
mtl_secondary_inventories sec,
cst_inv_qty_temp ciqt,
cst_inv_cost_temp cict,
mtl_parameters mp,
org_organization_definitions ood,
hr_all_organization_units haou,
gl_sets_of_books gsob,
cst_cost_groups ccg
where
1=1 and
ciqt.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
msi.organization_id = ciqt.organization_id and
msi.inventory_item_id = ciqt.inventory_item_id and
mp.organization_id = ciqt.organization_id and
ood.organization_id = ciqt.organization_id and
haou.organization_id = ood.operating_unit and
gsob.set_of_books_id = ood.set_of_books_id and
cict.organization_id = ciqt.organization_id and
cict.inventory_item_id = ciqt.inventory_item_id and
sec.organization_id(+) = ciqt.organization_id and
sec.secondary_inventory_name(+) = ciqt.subinventory_code and
mc.category_id = ciqt.category_id and
ccg.cost_group_id (+) = coalesce(ciqt.cost_group_id,cict.cost_group_id,mp.default_cost_group_id) and
(
 ciqt.qty_source in (9,10) or
 cict.cost_group_id = ciqt.cost_group_id or
 mp.primary_cost_method = 1
) and
( ciqt.qty_source not in (9,10) or
  ciqt.rcv_transaction_id = cict.rcv_transaction_id
) and
(
 (ciqt.qty_source in (3,4,5) and cict.cost_source in (1,2)) or
 (ciqt.qty_source in (6,7,8) and cict.cost_source in (1,2)) or
 (ciqt.qty_source in (9,10) and cict.cost_source in (3,4))
)
group by
gsob.name,
haou.name,
mp.organization_code,
ood.organization_name,
msi.concatenated_segments,
msi.description,
mc.concatenated_segments,
xxen_util.meaning(msi.item_type,'ITEM_TYPE',3),
msi.primary_uom_code,
msi.inventory_item_status_code,
cict.inventory_asset_flag,
msi.planning_make_buy_code,
decode(msi.inventory_planning_code,6, xxen_util.meaning(nvl(msi.mrp_planning_code,6),'MRP_PLANNING_CODE',700), xxen_util.meaning(nvl(msi.inventory_planning_code,6),'MTL_MATERIAL_PLANNING',700)),
decode(:p_item_revision, 1, ciqt.revision, null),
ciqt.subinventory_code,
ccg.cost_group,
case
when ciqt.qty_source in (3,4,5) then 'Onhand'
when ciqt.qty_source in (6,7,8) then 'Intransit'
else 'Receiving'
end,
sec.description,
sec.asset_inventory,
round(nvl(cict.item_cost,0) * :p_exchange_rate, :p_ext_precision)
having
2=2 and
decode(:p_neg_qty,1,1,2) = decode(:p_neg_qty,1,decode(sign(sum(ciqt.rollback_qty)),'-1',1,2),2) and
decode(:p_zero_qty,2,round(sum(nvl(ciqt.rollback_qty,0)),:p_qty_precision),1) <> 0
order by
mp.organization_code,
ciqt.subinventory_code,
msi.concatenated_segments,
revision