CST Inventory Value Report - by Subinventory (Item Cost)
Description
Categories: BI Publisher, Enginatics
Repository: Github
Repository: Github
Imported Oracle standard inventory value subinventory report by item cost
Source: Inventory Value Report - by Subinventory (XML)
Short Name: CSTRINVR_XML
DB package: BOM_CSTRINVR_XMLP_PKG
Source: Inventory Value Report - by Subinventory (XML)
Short Name: CSTRINVR_XML
DB package: BOM_CSTRINVR_XMLP_PKG
select nvl(&ic_item_segment,&p_item_seg) item , msi.description item_description , nvl(&ic_category_segment,&p_cat_seg) category , 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 , ciqt.subinventory_code subinventory , decode(ciqt.subinventory_code,null,'Intransit','On-hand') type , decode(:p_item_revision, 1, ciqt.revision, null) revision , sec.description subinventory_desc , xxen_util.meaning(sec.asset_inventory,'SYS_YES_NO',700) asset_subinventory , round(sum(nvl(ciqt.rollback_qty,0)),:p_qty_precision) qty , decode(cict.cost_type_id, :p_cost_type_id, ' ', '*') defaulted , round(nvl(cict.item_cost,0)*:p_exchange_rate, :p_ext_precision) unit_cost -- changed to round by extended precision , round(sum(nvl(ciqt.rollback_qty,0)*decode(nvl(sec.asset_inventory,1), 1, nvl(cict.item_cost,0), 0)*:p_exchange_rate/:round_unit))*:round_unit total_cost , round(sum(nvl(ciqt.rollback_qty,0) * decode(nvl(sec.asset_inventory,1), 1, nvl(cict.item_cost,0), 0) * :p_exchange_rate)/round(sum(nvl(ciqt.rollback_qty,0)),:p_qty_precision), :p_ext_precision) unit_cost_1 -- changed to round by extended precision , decode(:p_sort_option,2,&ic_category_segment,&ic_item_segment) order_by from mtl_categories_b mc , mtl_system_items_vl msi , mtl_secondary_inventories sec , cst_inv_qty_temp ciqt , cst_inv_cost_temp cict , mtl_parameters mp , cst_item_costs cic where msi.organization_id = ciqt.organization_id and msi.inventory_item_id = ciqt.inventory_item_id and mp.organization_id = ciqt.organization_id and cict.organization_id = ciqt.organization_id and cict.inventory_item_id = ciqt.inventory_item_id and (cict.cost_group_id = ciqt.cost_group_id or mp.primary_cost_method = 1) and &p_subinv_where and mc.category_id = ciqt.category_id and cic.organization_id = ciqt.organization_id and cic.inventory_item_id = ciqt.inventory_item_id and cic.cost_type_id = cict.cost_type_id and :p_sort_option in (1,2) group by msi.description , msi.primary_uom_code , msi.inventory_item_status_code , cict.inventory_asset_flag , msi.planning_make_buy_code , decode(:p_sort_option,2,&ic_category_segment,&ic_item_segment) , nvl(&ic_item_pseg,&p_item_seg) , nvl(&ic_cat_pseg,&p_cat_seg) , nvl(&ic_item_segment,&p_item_seg) , nvl(&ic_category_segment,&p_cat_seg) , 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)) , ciqt.subinventory_code , decode(ciqt.subinventory_code,null,'Intransit','On-hand') , decode(:p_item_revision, 1, ciqt.revision, null) , sec.description , sec.asset_inventory , decode(cict.cost_type_id, :p_cost_type_id, ' ', '*') , 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 sum(ciqt.rollback_qty) <> 0 order by order_by,item,revision,sec.description |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Title |
|
||
Cost Type |
|
LOV Oracle | |
Sort Option |
|
LOV Oracle | |
Report Option |
|
LOV Oracle | |
As of Date |
|
DateTime | |
Item From |
|
||
Item To |
|
||
Category Set |
|
LOV Oracle | |
Category From |
|
||
Category To |
|
||
Subinventory From |
|
LOV Oracle | |
Subinventory To |
|
LOV Oracle | |
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 | |
Category Structure |
|
Number | |
Chart of Accounts Id |
|
Number | |
CST_SRS_INVERSE_RATE |
|
||
Organization Id |
|
Number | |
Quantity Precision |
|
Number | |
CST_SRS_RATE_TYPE |
|
Number | |
View Cost Privilege |
|
Number |