CST Inventory Value Report - by Cost Group
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Inventory Value Report - by Cost Group
Application: Bills of Material
Source: Inventory Value Report - by Cost Group (XML)
Short Name: CSTRIRVW_XML
DB package: BOM_CSTRIRVW_XMLP_PKG
Description: Inventory Value Report - by Cost Group
Application: Bills of Material
Source: Inventory Value Report - by Cost Group (XML)
Short Name: CSTRIRVW_XML
DB package: BOM_CSTRIRVW_XMLP_PKG
Run
CST Inventory Value Report - by Cost Group and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT CCG.cost_group CG_QOH_CG , CCG.DESCRIPTION CG_DESCRIPTION , MSI.description CG_ITEM_DESC , MSI.primary_uom_code CG_UOM , fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') CG_ITEM_PSEG , fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') CG_ITEM_SEGMENT , fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_cat_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') CG_CAT_SEGMENT , DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL) CG_REVISION , MSI.inventory_item_status_code CG_ITEM_STATUS , LU3.meaning CG_INV_ASSET , LU1.meaning CG_MAKE_BUY , SEC.asset_inventory , DECODE(MSI.inventory_planning_code, 6, LU4.meaning, LU2.meaning) CG_PLANNING_METHOD , ROUND(sum(CIQT.rollback_qty),:P_QTY_PRECISION) CG_QTY , ROUND((NVL(CICT.item_cost,0) * :P_EXCHANGE_RATE), 5) CG_UNIT_COST , DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') CG_DEFAULTED , Round(Sum(CIQT.rollback_qty) * NVL(CICT.material_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_MATL_COST , Round(Sum(CIQT.rollback_qty) * NVL(CICT.material_overhead_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_MOVH_COST , Round(Sum(CIQT.rollback_qty) * NVL(CICT.resource_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_RES_COST , Round(Sum(CIQT.rollback_qty) * NVL(CICT.outside_processing_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_OSP_COST , Round(Sum(CIQT.rollback_qty) * NVL(CICT.overhead_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_OVHD_COST , Round(sum(CIQT.rollback_qty) * NVL(CICT.item_cost,0) * DECODE(NVL(SEC.asset_inventory,1), 1, 1, 0) * :P_EXCHANGE_RATE / :ROUND_UNIT) * :ROUND_UNIT CG_TOTAL_COST FROM mtl_secondary_inventories SEC , mtl_categories_b MC , mtl_system_items_vl MSI , cst_cost_groups CCG , mfg_lookups LU4 , mfg_lookups LU3 , mfg_lookups LU2 , mfg_lookups LU1 , cst_inv_qty_temp CIQT , cst_inv_cost_temp CICT , mtl_parameters MP WHERE CCG.cost_group_id = CIQT.cost_group_id AND 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 (CICT.cost_group_id = CIQT.cost_group_id OR MP.primary_cost_method = 1) AND MC.category_id = CIQT.category_id AND LU1.lookup_type(+) = 'MTL_PLANNING_MAKE_BUY' AND LU1.lookup_code(+) = MSI.planning_make_buy_code AND LU2.lookup_type = 'MTL_MATERIAL_PLANNING' AND LU2.lookup_code = NVL(MSI.inventory_planning_code,6) AND LU3.lookup_type = 'SYS_YES_NO' AND LU3.lookup_code = CICT.inventory_asset_flag AND LU4.lookup_type = 'MRP_PLANNING_CODE' AND LU4.lookup_code = NVL(MSI.mrp_planning_code,6) AND :P_SORT_OPTION = 8 GROUP BY CCG.cost_group , MSI.description , MSI.primary_uom_code , fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_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('cg_item_segment', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') , fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_cat_segment', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'VALUE') , DECODE(:P_ITEM_REVISION, 1, CIQT.revision, NULL) , MSI.inventory_item_status_code , LU3.meaning , LU1.meaning , DECODE(MSI.inventory_planning_code, 6, LU4.meaning, LU2.meaning) , DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') , SEC.asset_inventory , CCG.DESCRIPTION , NVL(CICT.item_cost,0) , NVL(CICT.overhead_cost,0) , NVL(CICT.outside_processing_cost,0) , NVL(CICT.resource_cost,0) , NVL(CICT.material_overhead_cost,0) , NVL(CICT.material_cost,0) HAVING Decode(:P_NEG_QTY,1,1,2) = Decode(:P_NEG_QTY,1,Decode(sign(Sum(CIQT.rollback_qty)),'-1',1, 2),2) AND ROUND(sum(NVL(CIQT.rollback_qty,0)),:P_qty_precision) <> 0 order by CCG.cost_group,fnd_flex_xml_publisher_apis.process_kff_combination_1('cg_item_pseg', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'PADDED_VALUE') |
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 | |
Cost Group From |
|
LOV Oracle | |
Cost Group To |
|
LOV Oracle | |
Currency |
|
LOV Oracle | |
Exchange Rate |
|
LOV Oracle | |
Quantities By Revision |
|
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 | |
Project Id |
|
LOV Oracle |