GMF GL Item Cost Detail

Description
Categories: BI Publisher
Imported from BI Publisher
Description: GL Item Cost Detail Report of OPM CM
Application: Process Manufacturing Financials
Source: GL Item Cost Detail Report (XML)
Short Name: CMRGLR_XML
DB package: GMF_CMRGLR_XMLP_PKG
Run GMF GL Item Cost Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT distinct 
    ic.item_number ,
    substr(ic.description,1, 30) item_desc1,
    mp.organization_code  orgn_code,
    nvl(cst.acctg_cost * :P_EXCHANGE_RATE,0) acctg_cost,
    ''||'/'||ic.primary_uom_code uom, 
    cst.fmeff_id frm_id, 
    /* Bug 14395061 In RTF template only four columns are displayed. As category_id is not shown, records are shown as duplicate entries*/
    cst.final_flag final_flag, 
    /*mic.category_id,*/
	/* Bug 7591198 */
	GMF_CMRGLR_XMLP_PKG.sum_formulacfformula(cst.fmeff_id) Sum_FormulaCF,
	GMF_CMRGLR_XMLP_PKG.Formula_noCP_p Formula_noCP,
	GMF_CMRGLR_XMLP_PKG.Formula_verCP_p Formula_verCP,
	GMF_CMRGLR_XMLP_PKG.Routing_noCP_p Routing_noCP,
	GMF_CMRGLR_XMLP_PKG.Routing_verCP_p Routing_verCP
FROM gl_item_cst cst, 
    mtl_item_flexfields ic, 
    mtl_parameters mp, 
    mtl_categories_kfv mckfv, 
    mtl_item_categories mic , 
    gmd_org_access_vw oa
	/* Bug 14395061 One category_id has multiple category_set_id.*/
--	mtl_default_category_sets mdcs,		-- 	Bug 22745020 - comment out.  mozheng 
--	mtl_category_sets mcs			-- 	Bug 22745020 - comment out.  mozheng 
WHERE cst.period_id = :period_id 
    AND cst.cost_type_id = :p_cost_type_id 
    AND cst.inventory_item_id = ic.inventory_item_id 
    AND ic.organization_id = mp.organization_id 
    AND cst.organization_id = ic.organization_id 
    AND ic.inventory_item_id = mic.inventory_item_id(+)
    AND ic.organization_id = mic.organization_id(+)
	/* Bug 14395061 One category_id has multiple category_set_id.*/
--	AND mdcs.functional_area_id = 19			-- 	Bug 22745020 - comment out.  mozheng 
--	AND mdcs.category_set_id = mcs.category_set_id(+)		-- 	Bug 22745020 - comment out.  mozheng 
	/* Bug 21020253  missed join condition */
--	AND mcs.category_set_id = mic.category_set_id		-- 	Bug 22745020 - comment out.  mozheng 
    AND mic.category_id = mckfv.category_id(+)
    and  mckfv.structure_id(+) = :p_category_structure_id
    AND cst.delete_mark = 0
    AND ic.organization_id = oa.organization_id
    &Report_TypeCP
    &CostCategoryCP
    &ItemCP
    &OrganizationCP
    &DatesCP
GROUP BY ic.item_number,
    ic.primary_uom_code , 
    ic.description, 
    mp.organization_code,
    cst.fmeff_id,
    cst.final_flag,
    mic.category_id,
	cst.acctg_cost
   /* Bug 7591198 */
ORDER BY 
&orderCP
Parameter Name SQL text Validation
Legal Entity
 
LOV Oracle
CALENDAR_CODE
 
LOV Oracle
Period Code
 
LOV Oracle
Cost Type
 
LOV Oracle
From Organization
 
LOV Oracle
To Organization
 
LOV Oracle
From Item
 
Char
To Item
 
Char
From Cost Category
 
Char
To Cost Category
 
Char
Report Type
 
LOV Oracle
Sort By
 
LOV Oracle
Report Currency
 
LOV Oracle
Exchange Rate
 
Number
Blitz Report™