GMF Inventory Valuation (2)

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