CST Elemental Inventory Value Report - by Subinventory
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
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 |
|
