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