CST All Inventories Value Report - by Cost Group

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: All Inventories Value Report - by Cost Group (XML)
Short Name: CSTRAIVA_XML
DB package: BOM_CSTRAIVA_XMLP_PKG
		 SELECT cost_group,
       item, 
       category,
       description,
       uom,
       SUM(STKQTY) STKQTY,
       SUM(INTQTY) INTQTY,
       SUM(RCVQTY) RCVQTY,
       SUM(STKVAL) STKVAL,
       SUM(INTVAL) INTVAL, 
       SUM(RCVVAL) RCVVAL, 
	BOM_CSTRAIVA_XMLP_PKG.cf_orderformula(:category) CF_ORDER, 
	 CF_CATEGORY, 
	 CF_Item, 
	BOM_CSTRAIVA_XMLP_PKG.cf_totqtyformula(SUM (STKQTY), SUM (RCVQTY), SUM (INTQTY)) TOTQTY, 
	BOM_CSTRAIVA_XMLP_PKG.cf_totvalformula(SUM(STKVAL), SUM(RCVVAL), SUM(INTVAL)) TOTVAL
FROM(         
SELECT CCG.cost_group COST_GROUP,
       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') 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') CATEGORY,
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) STKQTY,
       0 INTQTY,
       0 RCVQTY,
       ROUND(
         SUM(CIQT.rollback_qty) * 
		 DECODE(SUB.asset_inventory,1,CICT.item_cost,0) * :pv_exchange_rate / :pv_round_unit) * :pv_round_unit STKVAL,
       0 INTVAL,
       0 RCVVAL,
	    fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CF_CATEGORY , 
	  nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),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')) CF_Item 
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,
       cst_cost_groups CCG
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) -- should we split? 
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
AND    CCG.cost_group_id = NVL(CIQT.cost_group_id,MP.default_cost_group_id) 
GROUP
BY     CCG.cost_group,
       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(SUB.asset_inventory,1,CICT.item_cost,0),
	    fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')  , 
	  fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  
HAVING SUM(CIQT.rollback_qty) <> 0
UNION ALL
SELECT CCG.cost_group COST_GROUP,
       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') 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')  CATEGORY,
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM,
       0 STKQTY,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) INTQTY,
       0 RCVQTY,
      0 STKVAL,
       ROUND(SUM(CIQT.rollback_qty)*CICT.item_cost * 
	   :pv_exchange_rate / :pv_round_unit) * :pv_round_unit INTVAL,
       0 RCVVAL,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CF_CATEGORY , 
	  nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),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')) CF_Item 
FROM   cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_parameters MP,
       mtl_system_items_vl MSI,
       mtl_categories MC,
       cst_cost_groups CCG       
WHERE  CIQT.qty_source in (6,7,8)
AND    CIQT.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
AND    CCG.cost_group_id = NVL(CIQT.cost_group_id,MP.default_cost_group_id) 
GROUP
BY     CCG.cost_group,
       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,
       CICT.item_cost,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')  , 
	  fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  
HAVING SUM(CIQT.rollback_qty) <> 0
UNION ALL
SELECT CCG.cost_group COST_GROUP,
      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') 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') CATEGORY,
       MSI.description DESCRIPTION,
       MSI.primary_uom_code UOM,
       0 STKQTY,
       0 INTQTY,
       ROUND(SUM(CIQT.rollback_qty),:P_Qty_Precision) RCVQTY,
       0 STKVAL,
       0 INTVAL,
	   ROUND(SUM(CIQT.rollback_qty)*CICT.item_cost * :pv_exchange_rate / :pv_round_unit) *:pv_round_unit RCVVAL,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CF_CATEGORY , 
	  nvl(fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),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')) CF_Item 
FROM   cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_system_items_vl MSI,
       mtl_categories MC,
       mtl_parameters MP,
       cst_cost_groups CCG
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
AND    MP.organization_id = CIQT.organization_id
AND    CCG.cost_group_id = NVL(CIQT.cost_group_id,MP.default_cost_group_id) 
GROUP
BY     CCG.cost_group,
       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,
       CICT.item_cost,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_category', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')  , 
	  fnd_flex_xml_publisher_apis.process_kff_combination_1('cf_item', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')  
	  HAVING SUM(CIQT.rollback_qty) <> 0
) TEMP
GROUP BY cost_group,
       item,
       category,
       description,
       uom,CF_CATEGORY,CF_Item
&p_order_by
Parameter Name SQL text Validation
View Cost Privilege
 
Number
Quantity Precision
 
Number
CST_SRS_RATE_TYPE
 
Number
CST_SRS_INVERSE_RATE
 
Category Structure
 
Number
CST_SRS_COST_GROUP_DUMMY
 
Number
Chart of Accounts Id
 
Number
Organization Id
 
Number
Include Expense Subinventories
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Display Zero Costs Only
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Currency
 
LOV Oracle
Category To
 
Category From
 
Category Set
 
LOV Oracle
Item To
 
Item From
 
As of Date
 
DateTime
Sort Option
 
LOV Oracle
Specific Cost Group
 
LOV Oracle
Cost Group Option
 
LOV Oracle
Cost Type
 
LOV Oracle
Title