CST Elemental Inventory Value Report - by Subinventory
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
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
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 |
|
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 |