CST All Inventories Value (7)

➜ Back to Library
Description
Categories: BI Publisher
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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 Name SQL text Validation
Title 
 
 Char
Cost Type 
 
 LOV Oracle
Sort Option 
 
 LOV Oracle
Report Option 
 
 LOV Oracle
As of Date 
 
 DateTime
Item From 
 
 Char
Item To 
 
 Char
Category Set 
 
 LOV Oracle
Category From 
 
 Char
Category To 
 
 Char
Currency 
 
 LOV Oracle
Exchange Rate 
 
 LOV Oracle
Display Zero Costs Only 
 
 LOV Oracle
Include Expense Items 
 
 LOV Oracle
Include Expense Subinventories 
 
 LOV Oracle
Download
 
Related Blitz Reports
CST Inventory Value Report - by Cost Group CST All Inventories Value (8) CST All Inventories Value CST All Inventories Value Report - by Cost Group CST All Inventories Value (1) CST Elemental Inventory Value Report - by Subinventory CST All Inventories Value (3) CST Elemental Inventory Value Report - by Cost Group CST All Inventories Value (9)
Blitz Report™

Blitz Report™ provides multiple benefits: