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
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