CST Inventory Value - Multi-Organization (Item Costs)
Description
Categories: Enginatics
Repository: Github
Repository: Github
Report: CST Inventory Value - Multi-Organization (Item Costs)
Description:
The Inventory Value Report can be used to report Inventory Value by
- Elemental Item Cost level
- Quantity Type (Onhand, Intransit, Receiving)
The report can be used to analyze inventory value by Ledger, Operating Unit, Organization, Subinventory, Item Category, Cost Group
The report corres ... more
Description:
The Inventory Value Report can be used to report Inventory Value by
- Elemental Item Cost level
- Quantity Type (Onhand, Intransit, Receiving)
The report can be used to analyze inventory value by Ledger, Operating Unit, Organization, Subinventory, Item Category, Cost Group
The report corres ... more
select gsob.name ledger, haou.name operating_unit, x.organization_code, x.item, x.item_description, x.category, x.user_item_type, x.uom, x.item_status, x.inventory_asset, x.make_buy, x.planning_method, x.revision, x.subinventory, x.subinventory_desc, x.asset_subinventory, ccg.cost_group, x.quantity_type, x.total_qty, -- x.item_unit_cost, -- x.total_cost, x.material_cost, x.material_overhead_cost, x.resource_cost, x.outside_processing_cost, x.overhead_cost, -- x.onhand_qty, x.onhand_cost, x.intransit_qty, x.intransit_cost, x.receiving_qty, x.receiving_cost, -- -- x.item|| ' - ' || x.item_description item_label, x.subinventory || ' - ' || x.subinventory_desc subinventory_label, ood.organization_code || ' - ' || ood.organization_name organization_label from ( select mp.organization_id, 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, coalesce(ciqt.cost_group_id,cict.cost_group_id,mp.default_cost_group_id) cost_group_id, 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 from cst_inv_qty_temp ciqt, cst_inv_cost_temp cict, mtl_parameters mp, mtl_secondary_inventories sec, mtl_system_items_vl msi, mtl_categories_b_kfv mc where 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 -- cict.organization_id = ciqt.organization_id and cict.inventory_item_id = ciqt.inventory_item_id and -- mp.organization_id = ciqt.organization_id and -- sec.organization_id(+) = ciqt.organization_id and sec.secondary_inventory_name(+) = ciqt.subinventory_code and -- msi.organization_id = ciqt.organization_id and msi.inventory_item_id = ciqt.inventory_item_id and -- mc.category_id = ciqt.category_id and -- ( -- Q1 ( ciqt.qty_source in (3,4,5) and cict.cost_source in (1,2) and ( mp.primary_cost_method = 1 or cict.cost_group_id = ciqt.cost_group_id) ) or -- Q2/Q3 ( ciqt.qty_source in (6,7,8) and cict.cost_source in (1,2) and ( (mp.primary_cost_method <> 1 and cict.cost_group_id = ciqt.cost_group_id) or mp.primary_cost_method = 1 ) ) or -- Q4 ( ciqt.qty_source in (9,10) and cict.cost_source in (3,4) and cict.rcv_transaction_id = ciqt.rcv_transaction_id ) ) group by mp.organization_id, mp.organization_code, 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, coalesce(ciqt.cost_group_id,cict.cost_group_id,mp.default_cost_group_id), 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 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 ) x, org_organization_definitions ood, hr_all_organization_units haou, gl_sets_of_books gsob, cst_cost_groups ccg where 1=1 and x.organization_id = ood.organization_id and ood.operating_unit = haou.organization_id and ood.set_of_books_id = gsob.set_of_books_id and x.cost_group_id = ccg.cost_group_id (+) order by x.organization_code, x.subinventory, x.item, x.revision |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Quantity Type | LOV | ||
Ledger |
| LOV | |
Operating Unit |
| LOV | |
Organization Code |
| LOV | |
Cost Type | LOV | ||
As of Date | DateTime | ||
Item From | Char | ||
Item To | Char | ||
Category Set | LOV Oracle | ||
Category From | Char | ||
Category To | Char | ||
Subinventory From | LOV | ||
Subinventory To | LOV | ||
Currency | LOV Oracle | ||
Exchange Rate | LOV Oracle | ||
Quantities By Revision | LOV Oracle | ||
Negative Quantities only | LOV Oracle | ||
Display Zero Costs only | LOV Oracle | ||
Include Expense Items | LOV Oracle | ||
Include Expense Subinventories | LOV Oracle | ||
Include Zero Quantities | LOV Oracle | ||
Include Unvalued Transactions | LOV Oracle |