GMF GL Item Cost Detail

Description
Categories: BI Publisher, Manufacturing
Application: Process Manufacturing Financials
Source: GL Item Cost Detail Report (XML)
Short Name: CMRGLR_XML
DB package: GMF_CMRGLR_XMLP_PKG
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
Parameter Name SQL text Validation
Non Block Sql
 
LOV Oracle
SY ALL
 
Dummy Structure Id
 
LOV Oracle
End Date
 
DateTime
Start Date
 
DateTime
Currency Code
 
Exchange Rate
 
Number
Report Currency
 
LOV Oracle
Sort By
 
LOV Oracle
Report Type
 
LOV Oracle
To Cost Category
 
From Cost Category
 
To Item
 
From Item
 
To Organization
 
LOV Oracle
From Organization
 
LOV Oracle
Cost Type
 
LOV Oracle
Period Code
 
LOV Oracle
CALENDAR_CODE
 
LOV Oracle
Legal Entity
 
LOV Oracle