CST All Inventories Value

➜ Back to Library

Excel example

XML import file

Description

Categories: BI Publisher
Application: Bills of Material
Source: All Inventories Value Report (XML)
Short Name: CSTRAIVR_XML
DB package: BOM_CSTRAIVR_XMLP_PKG

Imported from BI Publisher
All Inventories Value Report

SQL 145 lines

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

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

Parameters 15

Parameter	Type	List of values SQL
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

Description

SQL 145 lines

Parameters 15

Find out more

Features

Download

FAQ