create or replace procedure V8_UPD_FIXXCMS_CONTACTPREF is
  cursor contactprefs is
    select xcms_id, xcms_contactpref from xcms where xcms_contactpref>0;
  cp contactprefs%rowtype;

  cursor linkedxcms(pxcmsid number) is
    select xcil_company_xcms_id  
    from   xcil, xcms
    where  xcil_individual_xcms_id=pxcmsid and 
           xcil_company_xcms_id=xcms_id ;
  lx linkedxcms%rowtype;         
  
  contact_xcmsid xcms.xcms_id%type;
  counter xcms.xcms_contactpref%type;
begin
  for cp in contactprefs loop
    counter := 1;
    for lx in linkedxcms(cp.xcms_id) loop
      if counter = cp.xcms_contactpref then 
        contact_xcmsid := lx.xcil_company_xcms_id;
      end if;   
      counter := counter + 1;
    end loop;
    
    if contact_xcmsid>0 then
      update xcms set xcms_contactpref = contact_xcmsid where xcms_id = cp.xcms_id;
    end if;        
  end loop;

  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_FIXXCMS_CONTACTPREF;
/
