GMF Inventory Valuation

Description
Categories: BI Publisher, Manufacturing
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
Organization Context
 
Number
SY_ALL
 
Non Block Sql
 
LOV Oracle
P_LEDGER
 
Number
Exchange Rate
 
Number
Report Currency
 
LOV Oracle
Period Number
 
LOV Oracle
Period Year
 
LOV Oracle
Current Balance
 
LOV Oracle
To Item
 
From Item
 
To Organization
 
LOV Oracle
From Organization
 
LOV Oracle
Legal Entity
 
LOV Oracle
Ask a question