CST Elemental Inventory Value Report - by Cost Group

Description
Categories: BI Publisher, Financials, Manufacturing
Application: Bills of Material
Source: Elemental Inventory Value Report - by Cost Group (XML)
Short Name: CSTREIVW_XML
DB package: BOM_CSTREIVW_XMLP_PKG
SELECT null IC_ITEM_NUMBER
,      null IC_CATEGORY
,      MSI.description IC_DESCRIPTION
,      MSI.primary_uom_code IC_UOM
,      CCG.cost_group IC_COSTGROUP
,      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_NEW
,      ROUND(CIQT.rollback_qty,:P_qty_precision) IC_QTY
,      DECODE(CICT.cost_type_id, :P_COST_TYPE_ID, ' ', '*') IC_DEFAULTED
,      ROUND(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(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(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(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(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(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,
	BOM_CSTREIVW_XMLP_PKG.ic_orderformula(MSI.description || MSI.description || MSI.description || MSI.segment1 || MSI.segment2 || MSI.segment3, MC.attribute1 || MC.attribute2 || MC.attribute3 || MC.attribute4 || MC.attribute5 || MC.description || MC.segment1, :IC_ITEM_SEGMENT, :IC_CATEGORY_SEGMENT, :IC_ITEM_PSEG, :IC_CAT_PSEG) IC_ORDER,
	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') IC_ITEM_PSEG,
	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_cat_pseg', 'INV', 'MCAT', MC.STRUCTURE_ID, NULL, MC.CATEGORY_ID, 'ALL', 'Y', 'PADDED_VALUE') IC_CAT_PSEG,
	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,
	BOM_CSTREIVW_XMLP_PKG.ic_rowcountformula(:IC_QUANTITY) IC_ROWCOUNT,
	BOM_CSTREIVW_XMLP_PKG.ic_quantity1formula(:IC_QUANTITY) IC_QUANTITY1,
	BOM_CSTREIVW_XMLP_PKG.ic_matl1formula(:IC_QUANTITY, :IC_MATL) IC_MATL1,
	BOM_CSTREIVW_XMLP_PKG.ic_movh1formula(:IC_QUANTITY, :IC_MOVH) IC_MOVH1,
	BOM_CSTREIVW_XMLP_PKG.ic_res1formula(:IC_QUANTITY, :IC_RES) IC_RES1,
	BOM_CSTREIVW_XMLP_PKG.ic_osp1formula(:IC_QUANTITY, :IC_OSP) IC_OSP1,
	BOM_CSTREIVW_XMLP_PKG.ic_ovhd1formula(:IC_QUANTITY, :IC_OVHD) IC_OVHD1,
	BOM_CSTREIVW_XMLP_PKG.ic_total1formula(:IC_QUANTITY, :IC_TOTAL) IC_TOTAL1
FROM   mtl_categories_b MC,
       mtl_system_items_vl MSI,
       cst_cost_groups CCG,
       mtl_secondary_inventories SEC,
       cst_inv_qty_temp CIQT,
       cst_inv_cost_temp CICT,
       mtl_parameters MP
WHERE  SEC.organization_id (+) = CIQT.organization_id
AND    SEC.secondary_inventory_name (+) = CIQT.subinventory_code
AND    CCG.cost_group_id = CIQT.cost_group_id
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
ORDER BY 6, 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') ,3 ,4 ,8 ,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
Cost Group To
 
LOV Oracle
Cost Group 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