CST Elemental Inventory Value Report - by Subinventory

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Elemental Inventory Value Report
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 NameSQL textValidation
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
Subinventory From
 
LOV Oracle
Subinventory To
 
LOV Oracle
Currency
 
LOV Oracle
Exchange Rate
 
LOV Oracle
Negative Quantities Only
 
LOV Oracle
Display Zero Costs Only
 
LOV Oracle
Include Expense Items
 
LOV Oracle
Include Expense Subinventories
 
LOV Oracle
Include Zero Quantities
 
LOV Oracle
Include Unvalued Transactions
 
LOV Oracle
Include Intransit
 
LOV Oracle
Download
Related Blitz Reports
CST Inventory Value - Multi-Organization (Element Costs) 11i CST Inventory Value Report - by Cost Group (1) CST Inventory Value - Multi-Organization (Item Costs) CST Inventory Value Report - by Cost Group CST Elemental Inventory Value Report - by Cost Group XXFJ CST Inventory Value - Multi-Organization (Item Costs) CST Subinventory Account Value - Multi-Org CST Inventory Value Report - by Subinventory (Item Cost) CST All Inventories Value
Blitz Report™