GMF Inventory Valuation
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Inventory Valuation Report
Application: Process Manufacturing Financials
Source: Inventory Valuation Report (XML)
Short Name: GMFINVAL_XML
DB package: GMF_GMFINVAL_XMLP_PKG
Description: Inventory Valuation Report
Application: Process Manufacturing Financials
Source: Inventory Valuation Report (XML)
Short Name: GMFINVAL_XML
DB package: GMF_GMFINVAL_XMLP_PKG
SELECT iim.item_number, iim.inventory_item_id, mp.organization_code, nvl(sum(moqd.primary_transaction_quantity), 0) primary_quantity, iim.primary_uom_code , nvl(sum(moqd.secondary_transaction_quantity),0) secondary_quantity, iim.secondary_uom_code , iim.description , sysdate transdate, haou.name organization_name, moqd.subinventory_code, msi.description subinv_description, mp.organization_id, iim.dual_uom_control, GMF_GMFINVAL_XMLP_PKG.unitcostcfformula(iim.inventory_item_id, mp.organization_id, sysdate) UNITCOSTCF, GMF_GMFINVAL_XMLP_PKG.TOTALCOSTCFFORMULA(nvl(sum(moqd.primary_transaction_quantity), 0)) TOTALCOSTCF, GMF_GMFINVAL_XMLP_PKG.TOTALCOSTCP_p TOTALCOSTCP, GMF_GMFINVAL_XMLP_PKG.UNITCOSTCP_p UNITCOSTCP FROM mtl_item_flexfields iim, mtl_parameters mp, mtl_onhand_quantities_detail moqd, hr_all_organization_units haou, hr_organization_information hoi, mtl_secondary_inventories msi, gmd_org_access_vw oa WHERE &Q1ORQ2 = '1' AND iim.inventory_item_id = moqd.inventory_item_id AND iim.organization_id = moqd.organization_id AND iim.organization_id = mp.organization_id AND hoi.org_information2 = TO_CHAR(:p_legal_entity_id) /* BUG 12970368 */ AND hoi.organization_id = mp.organization_id AND hoi.org_information_context = 'Accounting Information' AND haou.organization_id = hoi.organization_id AND haou.organization_id = mp.organization_id AND moqd.is_consigned = 2 AND msi.secondary_inventory_name = moqd.subinventory_code AND msi.organization_id = mp.organization_id AND iim.organization_id = oa.organization_id AND msi.asset_inventory = 1 /* B 8439947 Bug 12970368 */ &OrganizationCP &ItemCP GROUP BY iim.item_number, iim.inventory_item_id, mp.organization_code,iim.primary_uom_code, iim.secondary_uom_code,iim.description, sysdate, haou.name, moqd.subinventory_code, msi.description, mp.organization_id, iim.dual_uom_control UNION SELECT iim.item_number, iim.inventory_item_id, mp.organization_code, nvl(sum(gpb.primary_quantity), 0) primary_quantity, iim.primary_uom_code , nvl(sum(gpb.secondary_quantity),0) secondary_quantity, iim.secondary_uom_code , iim.description , /*Bug#12970368 oap.period_start_date transdate,*/ (oap.schedule_close_date + (86399/86400)) transdate, /* Bug#12970368*/ haou.name organization_name, gpb.subinventory_code, msi.description subinv_description, mp.organization_id, iim.dual_uom_control, GMF_GMFINVAL_XMLP_PKG.unitcostcfformula(iim.inventory_item_id, mp.organization_id, (oap.schedule_close_date + (86399/86400))) UNITCOSTCF, /*Bug#12970368*/ GMF_GMFINVAL_XMLP_PKG.TOTALCOSTCFFORMULA(nvl(sum(gpb.primary_quantity), 0)) TOTALCOSTCF, GMF_GMFINVAL_XMLP_PKG.TOTALCOSTCP_p TOTALCOSTCP, GMF_GMFINVAL_XMLP_PKG.UNITCOSTCP_p UNITCOSTCP FROM mtl_item_flexfields iim, mtl_parameters mp, gmf_period_balances gpb, org_acct_periods oap, hr_all_organization_units haou, hr_organization_information hoi, mtl_secondary_inventories msi, gmd_org_access_vw oa WHERE &Q1ORQ2 = '2' AND iim.inventory_item_id = gpb.inventory_item_id AND iim.organization_id = gpb.organization_id AND iim.organization_id = mp.organization_id AND mp.organization_id = gpb.organization_id AND hoi.org_information2 = TO_CHAR(:p_legal_entity_id) /* BUG 12970368 */ AND hoi.organization_id = mp.organization_id AND hoi.org_information_context = 'Accounting Information' AND haou.organization_id = hoi.organization_id AND haou.organization_id = mp.organization_id AND mp.organization_id = oap.organization_id AND gpb.acct_period_id = oap.acct_period_id AND oap.period_year = :p_period_year AND oap.period_num = :p_period_num AND gpb.subinventory_code (+)= msi.secondary_inventory_name AND mp.organization_id = msi.organization_id AND iim.organization_id = oa.organization_id AND msi.asset_inventory = 1 /* B 8439947 Bug 12970368 */ &OrganizationCP &ItemCP GROUP BY iim.item_number, iim.inventory_item_id, mp.organization_code, iim.primary_uom_code, iim.secondary_uom_code, iim.description, oap.schedule_close_date, haou.name, gpb.subinventory_code, msi.description, mp.organization_id, iim.dual_uom_control /* ORDER BY 3,1,11 ORDER BY 3 ASC,10 ASC,13 ASC,2 ASC,1 ASC,8 ASC,14 ASC,5 ASC,7 ASC , 3 , 1 , 11 bug #13638682 */ ORDER BY 3 ASC, 1 ASC, 11 ASC, 10 ASC,13 ASC,2 ASC,8 ASC,14 ASC,5 ASC,7 ASC |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Legal Entity |
|
LOV Oracle | |
From Organization |
|
LOV Oracle | |
To Organization |
|
LOV Oracle | |
From Item |
|
Char | |
To Item |
|
Char | |
Current Balance |
|
LOV Oracle | |
Period Year |
|
LOV Oracle | |
Period Number |
|
LOV Oracle | |
Report Currency |
|
LOV Oracle | |
Exchange Rate |
|
Number |