XXCustom all Inventories Value report

➜ Back to Library
Description
Categories: BI Publisher
Columns: Item Number, Category, Decode(:P Sort Option,1,Fnd Flex Xml Publisher Apis.Process Kff Combination 1('Item Segment','Inv','Mstk',101,Msi.Organization Id,Msi.Inventory Item Id,'All','Y','Value'),Fnd Flex Xml Publisher Apis.Process Kff Combination 1('Category Segment','Inv',', Description, UOM Code, Stk Qty, Int Qty, Rcv Qty, Stk Value, Int Value ...
Imported from BI Publisher
Description: All Inventories Value Report
Application: Bills of Material
Source: All Inventories Value Report (XML)
Short Name: CSTRAIVR_XML
DB package: BOM_CSTRAIVR_XMLP_PKG
SELECT fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_NUMBER,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY,
       decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')),
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM_CODE,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) STK_QTY,
       0 INT_QTY,
       0 RCV_QTY,
       ROUND(
         SUM(CIQT.rollback_qty) *
         DECODE(SUB.asset_inventory,1,CICT.item_cost,0) *
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit STK_VALUE,
       0 INT_VALUE,
       0 RCV_VALUE,
       ROUND(
         SUM(CIQT.rollback_qty) *
         DECODE(SUB.asset_inventory,1,CICT.item_cost,0) *
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit IT_VALUE,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) IT_QTY
FROM   cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_parameters MP,
       mtl_system_items_vl MSI,
       mtl_categories MC,
       mtl_secondary_inventories SUB
WHERE  CIQT.qty_source in (3,4,5)
AND    CICT.cost_source in (1,2)
AND    CICT.organization_id = CIQT.organization_id
AND    CICT.inventory_item_id = CIQT.inventory_item_id
AND    (   MP.primary_cost_method = 1 
       OR  CICT.cost_group_id = CIQT.cost_group_id) 
AND    MP.organization_id = CIQT.organization_id
AND    SUB.organization_id = CIQT.organization_id
AND    SUB.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
GROUP
BY     fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
       MSI.description,
       MSI.primary_uom_code,
       DECODE(SUB.asset_inventory,1,CICT.item_cost,0)
HAVING SUM(CIQT.rollback_qty)  <> 0
UNION ALL
SELECT fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_NUMBER,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY,
       decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')),
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM_CODE,
       0 STK_QTY,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) INT_QTY,
       0 RCV_QTY,
       0 STK_VALUE,
       ROUND(
         SUM(CIQT.rollback_qty) *
         CICT.item_cost *
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit INT_VALUE,
       0 RCV_VALUE,   
       ROUND(
         SUM(CIQT.rollback_qty) *
         CICT.item_cost *
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit IT_VALUE,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) IT_QTY
FROM   cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_parameters MP,
       mtl_system_items_vl MSI,
       mtl_categories MC
WHERE  CIQT.qty_source in (6,7,8)
AND    CICT.organization_id = :P_ORG_ID
AND    CICT.cost_source in (1,2)
AND    CICT.organization_id = CIQT.organization_id
AND    CICT.inventory_item_id = CIQT.inventory_item_id
AND    (   MP.primary_cost_method = 1 
       OR  CICT.cost_group_id = CIQT.cost_group_id) 
AND    MP.organization_id = CIQT.organization_id
AND    MSI.organization_id = CIQT.organization_id
AND    MSI.inventory_item_id = CIQT.inventory_item_id
AND    MC.category_id  = CIQT.category_id
GROUP
BY     fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
       MSI.description,
       MSI.primary_uom_code,
       CICT.item_cost
HAVING SUM(CIQT.rollback_qty) <> 0
UNION ALL
SELECT fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') ITEM_NUMBER,
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CATEGORY,
       decode(:P_SORT_OPTION,1,fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')),
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM_CODE,
       0 STK_QTY,
       0 INT_QTY,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) RCV_QTY,
       0 STK_VALUE,
       0 INT_VALUE,
       ROUND(
         SUM(CIQT.rollback_qty)*
         CICT.item_cost * 
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit RCV_VALUE, 		
       ROUND(
         SUM(CIQT.rollback_qty)*
         CICT.item_cost * 
         :P_Exchange_Rate /
         :Round_Unit
       ) *
       :Round_Unit IT_VALUE,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) IT_QTY
FROM    cst_inv_qty_temp CIQT,
        cst_inv_cost_temp CICT,
        mtl_system_items_vl MSI,
        mtl_categories MC
WHERE   CIQT.qty_source in (9,10)
AND     CICT.cost_source in (3,4)
AND     CICT.organization_id = CIQT.organization_id
AND     CICT.inventory_item_id = CIQT.inventory_item_id
AND     CICT.rcv_transaction_id = CIQT.rcv_transaction_id
AND     MSI.organization_id = CIQT.organization_id
AND     MSI.inventory_item_id = CIQT.inventory_item_id
AND     MC.category_id  = CIQT.category_id
GROUP
BY     fnd_flex_xml_publisher_apis.process_kff_combination_1('item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
       fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
       MSI.description,
       MSI.primary_uom_code,
       CICT.item_cost
HAVING SUM(CIQT.rollback_qty) <> 0
order by 3,1
Parameter NameSQL textValidation
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
 
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Display Zero Costs Only
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Include Expense Subinventories
 
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
Download
Related Blitz Reports
CST Inventory Value Report - by Subinventory (Item Cost) CST All Inventories Value (3) CST All Inventories Value Report - by Cost Group CST All Inventories Value (2) CST All Inventories Value CST Elemental Inventory Value Report - by Cost Group CST Inventory Value Report - by Subinventory (Subinventory) CST Elemental Inventory Value Report - by Subinventory CST Inventory Value Report - by Cost Group
Blitz Report In Action
Blitz Report™