GMF GL Item Cost Detail

SELECT  distinct
    ic.item_number ,
    substr(ic.description,1, 30) item_desc1,
    mp.organization_code  orgn_code,
    nvl(sum(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
   /* Bug 7591198 */
ORDER BY 
&orderCP

SELECT distinct ic.item_number , substr(ic.description,1, 30) item_desc1, mp.organization_code orgn_code, nvl(sum(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 /* Bug 7591198 */ ORDER BY &orderCP