CST Elemental Inventory Value Report - by Subinventory
Description
Categories: BI Publisher
Columns: Ic Description, Ic Uom, Ic Subinv, Ic Order, Ic Item Segment, Ic Category Segment, Ic Type, Ic Defaulted, Ic Qty, Ic Matl Cost ...
Columns: Ic Description, Ic Uom, Ic Subinv, Ic Order, Ic Item Segment, Ic Category Segment, Ic Type, Ic Defaulted, Ic Qty, Ic Matl Cost ...
Application: Bills of Material
Source: Elemental Inventory Value Report - by Subinventory (XML)
Short Name: CSTREIVR_XML
DB package: BOM_CSTREIVR_XMLP_PKG
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 | |
---|---|---|---|
Title | |||
Cost Type | LOV Oracle | ||
Sort Option | LOV Oracle | ||
Report Option | LOV Oracle | ||
As of Date | DateTime | ||
Item From | |||
Item To | |||
Category Set | LOV Oracle | ||
Category From | |||
Category To | |||
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 | ||
Organization Id | Number | ||
Chart of Accounts Id | Number | ||
Category Structure | Number | ||
CST_SRS_INVERSE_RATE | |||
CST_SRS_RATE_TYPE | Number | ||
Quantity Precision | Number | ||
View Cost Privilege | Number |