CST Inventory Value Report - by Subinventory

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Inventory Value Report - by Subinventory (XML)
Short Name: CSTRINVR_XML
DB package: BOM_CSTRINVR_XMLP_PKG
SELECT 
	   CIQT.subinventory_code SI_SUBINV
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  SI_ITEM
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('p_cat_seg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')   SI_CATEGORY
,      SEC.DESCRIPTION                  SI_DESCRIPTION
,      SEC.ASSET_INVENTORY              SI_ASSET_INV
,      MSI.description SI_ITEM_DESC
,      MSI.primary_uom_code SI_UOM
,      DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL) SI_REVISION
,      MSI.inventory_item_status_code SI_ITEM_STATUS
,      LU3.meaning SI_ASSET_INV_DSP
,      LU1.meaning SI_MAKE_BUY
,      DECODE(MSI.inventory_planning_code,
              6, LU4.meaning, LU2.meaning) SI_PLANNING_METHOD
,      ROUND(sum(CIQT.rollback_qty),:P_QTY_PRECISION) SI_QTY
,      ROUND((sum(NVL(CICT.item_cost,0)) * :P_EXCHANGE_RATE), :P_EXT_PRECISION)    SI_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) 
       / decode(:P_ZERO_QTY,2,ROUND(sum(NVL(CIQT.rollback_qty,0)),:P_qty_precision),1), :P_EXT_PRECISION)     SI_UNIT_COST_1   -- calculated as value/qty
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*')   SI_DEFAULTED
,      round(sum(CIQT.rollback_qty * 
         NVL(CICT.material_cost,0) *
         DECODE(SEC.asset_inventory, 2, 0, 1) *
         :P_EXCHANGE_RATE) / :ROUND_UNIT) * :ROUND_UNIT   SI_MATL_COST
,      round(sum(CIQT.rollback_qty *
         NVL(CICT.material_overhead_cost,0) *
         DECODE(SEC.asset_inventory, 2, 0, 1) *
         :P_EXCHANGE_RATE) / :ROUND_UNIT) * :ROUND_UNIT   SI_MOVH_COST
,      round(sum(CIQT.rollback_qty *
         NVL(CICT.resource_cost,0) *
         DECODE(SEC.asset_inventory, 2, 0, 1) *
         :P_EXCHANGE_RATE) / :ROUND_UNIT) * :ROUND_UNIT   SI_RES_COST
,      round(sum(CIQT.rollback_qty *
         NVL(CICT.outside_processing_cost,0) *
         DECODE(SEC.asset_inventory, 2, 0, 1) *
         :P_EXCHANGE_RATE) / :ROUND_UNIT) * :ROUND_UNIT   SI_OSP_COST
,      round(sum(CIQT.rollback_qty *
         NVL(CICT.overhead_cost,0) *
         DECODE(SEC.asset_inventory, 2, 0, 1) *
         :P_EXCHANGE_RATE) / :ROUND_UNIT) * :ROUND_UNIT   SI_OVHD_COST
,      round(sum(NVL(CIQT.rollback_qty,0) *
       NVL(CICT.item_cost,0) * DECODE(SEC.asset_inventory,2,0,1) *
       :P_EXCHANGE_RATE) / :ROUND_UNIT)*:ROUND_UNIT      SI_TOTAL_COST
FROM    mfg_lookups LU1
,       mfg_lookups LU2
,       mfg_lookups LU3
,       mfg_lookups LU4
,       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   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     MP.organization_id = CIQT.organization_id
AND     MC.category_id = CIQT.category_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     LU1.lookup_type(+) = 'MTL_PLANNING_MAKE_BUY'
AND     LU1.lookup_code(+) = MSI.planning_make_buy_code
AND     LU2.lookup_type = 'MTL_MATERIAL_PLANNING'
AND     LU2.lookup_code = NVL(MSI.inventory_planning_code,6)
AND     LU3.lookup_type = 'SYS_YES_NO'
AND     LU3.lookup_code = CICT.inventory_asset_flag
AND     LU4.lookup_type = 'MRP_PLANNING_CODE'
AND     LU4.lookup_code = NVL(MSI.mrp_planning_code,6)
AND     CIC.organization_id = MP.cost_organization_id
AND     CIC.inventory_item_id = CIQT.inventory_item_id
AND     CIC.cost_type_id   = CICT.cost_type_id 
AND     :P_SORT_OPTION  = 3
group by CIQT.subinventory_code
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('p_item_seg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')
,      fnd_flex_xml_publisher_apis.process_kff_combination_1('p_cat_seg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
,      MSI.description
,      MSI.primary_uom_code
,      DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL)
,      MSI.inventory_item_status_code
,      LU3.meaning
,      LU1.meaning
,      DECODE(MSI.inventory_planning_code,
              6, LU4.meaning, LU2.meaning)
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*')
,      SEC.DESCRIPTION   
,      SEC.ASSET_INVENTORY 
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
ORDER BY SI_SUBINV, SI_ITEM
Parameter Name SQL text Validation
View Cost Privilege
 
Number
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
Chart of Accounts Id
 
Number
Organization Id
 
Number
Include Unvalued Transactions
 
LOV Oracle
Include Zero Quantities
 
LOV Oracle
Include Expense Subinventories
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Display Zero Costs Only
 
LOV Oracle
Negative Quantities Only
 
LOV Oracle
Quantities By Revision
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Subinventory To
 
LOV Oracle
Subinventory From
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From
 
As of Date
 
DateTime
Report Option
 
LOV Oracle
Sort Option
 
LOV Oracle
Cost Type
 
LOV Oracle
Title
 
Ask a question