CST Elemental Inventory Value Report - by Subinventory

➜ Back to Library
Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Elemental Inventory Value Report - by Subinventory (XML)
Short Name: CSTREIVR_XML
DB package: BOM_CSTREIVR_XMLP_PKG 
SELECT 
       MSI.description IC_DESCRIPTION
,       MSI.primary_uom_code IC_UOM
,       CIQT.subinventory_code IC_SUBINV
,       decode(:P_SORT_OPTION,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')) IC_ORDER
,	    fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') IC_ITEM_SEGMENT
,	    fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') IC_CATEGORY_SEGMENT
,       decode(CIQT.subinventory_code,NULL,'Intransit','On-hand') IC_TYPE
,       DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') IC_DEFAULTED
,       ROUND(sum(nvl(CIQT.rollback_qty,0)),:P_qty_precision) IC_QTY
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.material_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_MATL_COST
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.material_overhead_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_MOVH_COST
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.resource_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_RES_COST
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.outside_processing_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_OSP_COST
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.overhead_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_OVHD_COST
,       ROUND(sum(CIQT.rollback_qty * 
              DECODE(nvl(SEC.asset_inventory,1),1,1,0) *
              NVL(CICT.item_cost,0) * :P_EXCHANGE_RATE / :ROUND_UNIT))*:ROUND_UNIT IC_TOTAL_COST 
FROM   mtl_categories_b MC,
       mtl_system_items_vl MSI,
       mtl_secondary_inventories SEC,
       mtl_parameters MP,
       cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT
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    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    MC.category_id = CIQT.category_id
AND     :P_SORT_OPTION IN (1,2)
group by MSI.description,
MSI.primary_uom_code,
 CIQT.subinventory_code,
 decode(CIQT.subinventory_code,NULL,'Intransit','On-hand'),
 DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*'),
 decode(:P_SORT_OPTION,2,fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE')),
 fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE'),
 fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE'),
 fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE'),
 fnd_flex_xml_publisher_apis.process_kff_combination_1('ic_category_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE')
 HAVING Decode(:P_NEG_QTY,1,1,2) =  Decode(:P_NEG_QTY,1,Decode(sign(Sum(CIQT.rollback_qty)),'-1',1,
                                                                                  2),2)
 and sum(CIQT.rollback_qty) <> 0
Order by 4,5
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 Intransit 
 
 LOV Oracle
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
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 
 
Download
 
Related Blitz Reports
CST Inventory Value Report - by Subinventory
CST Elemental Inventory Value Report - by Cost Group
CST All Inventories Value Report - by Cost Group
CST All Inventories Value
INV Transaction historical summary
INV Inventory Aging
CST Intransit Value
INV Transaction register
CST Transaction Value Historical Summary - Average/FIFO/LIFO Costing