OUTER_PACK_UOM                                 outer
OUTER_PACK_CONVERSION_FACTOR                   outer_inter 
OUTER_PACK_UNIT_OF_USE_QTY                     outer_uou 
OUTER_SUBPACK_QUANTITY                         outer_sub
INTERMEDIATE_PACK_UOM                          inter 
INTERMEDIATE_PACK_CONV_FACTOR                  inter_uou
INTERMEDIATE_SUBPACK_QUANTITY                  inter_sub
UNIT_OF_USE                                    uou
UNIT_OF_USE_CONVERSION_FACTOR                  uou_sub
SUBPACK_UOM                                    sub



declare 

cursor c_rec is
select * 
from xxnfma_pps_packaging;

cursor con_uom(prd_key in  number, 
               sub_prd in varchar2 ) is 
select 1 
  from vha_int_price 
 where product_key = prd_key 
   and uom_key     = sub_prd ;

sub_exists number := 0;

begin 

   for c1 in c_rec loop 

       sub_exists := 0;
       open con_uom(c1.ocs_product_key , c1.SUBPACK_UOM);
       fetch con_uom into sub_exists;
       close con_uom;

       if sub_exists = 1 then 

CASE 1
----------------
Level from_uom   converion_factor    to_uom 
1     outer      1                   outer  
2     uou        outer_uou           outer
3     sub        uou_sub             uou 

else

CASE 2
----------------
Level from     conv        to 
1     outer    1           outer  
2     inter    outer_inter outer
3     uou      inter_uou   inter


end if;

   end loop;
end ;
/