pears.MergePeople [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.MergePeople ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."MergePeople"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "ssourceid" char(20),in "stargetid" char(20) ) 
begin
  declare "scvtext" long varchar;
  declare "snotes" long varchar;
  declare "tnotes" long varchar;
  declare "sxnotes" long varchar;
  declare "txnotes" long varchar;
  declare "salert" char(100);
  declare "talert" char(100);
  declare "sni" char(15);
  declare "tni" char(15);
  declare "sdob" date;
  declare "tdob" date;
  declare "ssex" char(1);
  declare "tsex" char(1);
  declare "smstatus" char(1);
  declare "tmstatus" char(1);
  declare "schildren" smallint;
  declare "tchildren" smallint;
  declare "sstaffid" char(20);
  declare "tstaffid" char(20);
  declare "scompliancestaffid" char(20);
  declare "tcompliancestaffid" char(20);
  declare "sappnumber" char(12);
  declare "tappnumber" char(12);
  declare "sregdate" date;
  declare "tregdate" date;
  declare "spaynumber" char(15);
  declare "tpaynumber" char(15);
  declare "schangedate" timestamp;
  declare "tchangedate" timestamp;
  declare "sstatus" char(1);
  declare "tstatus" char(1);
  declare "savailable" char(1);
  declare "savaildate" date;
  declare "snoticeperiod" smallint;
  declare "tavailable" char(1);
  declare "tavaildate" date;
  declare "tnoticeperiod" smallint;
  declare "saddr1" char(40);
  declare "saddr2" char(40);
  declare "saddr3" char(40);
  declare "taddr1" char(40);
  declare "taddr2" char(40);
  declare "taddr3" char(40);
  declare "stown" char(30);
  declare "ttown" char(30);
  declare "scounty" char(30);
  declare "tcounty" char(30);
  declare "scountry" char(30);
  declare "tcountry" char(30);
  declare "spostcode" char(30);
  declare "tpostcode" char(30);
  declare "sname" char(30);
  declare "tname" char(30);
  declare "idum" smallint;
  declare "payextnum" integer;
  declare "snetid" char(20);
  select "ni","dob","sex","maritalstatus","children","staffid","appnumber",
    "registrationdate","payrollnumber","changedate","available","availdate",
    "noticeperiod","status","alert","notes","othernotes",
    "addr1","addr2","addr3","town","county","country","postcode","cvtext","name","compliancestaffid" into "sni",
    "sdob","ssex","smstatus","schildren","sstaffid","sappnumber",
    "sregdate","spaynumber","schangedate","savailable","savaildate",
    "snoticeperiod","sstatus","salert","snotes",
    "sxnotes","saddr1","saddr2","saddr3","stown","scounty","scountry",
    "spostcode","scvtext","sname","scompliancestaffid" from "person" where "personid" = "ssourceid";
  select "ni","dob","sex","maritalstatus","children","staffid","appnumber",
    "registrationdate","payrollnumber","changedate","available","availdate",
    "noticeperiod","status","alert","notes","othernotes",
    "addr1","addr2","addr3","town","county","country","postcode","name","compliancestaffid" into "tni",
    "tdob","tsex","tmstatus","tchildren","tstaffid","tappnumber",
    "tregdate","tpaynumber","tchangedate","tavailable","tavaildate",
    "tnoticeperiod","tstatus","talert","tnotes",
    "txnotes","taddr1","taddr2","taddr3","ttown","tcounty","tcountry",
    "tpostcode","tname","tcompliancestaffid" from "person" where "personid" = "stargetid";
  call "AuditLog"('PERSON',
  "stargetid",
  "string"('Person ',"sname",' merged into ',"tname"),
  "string"('Appnumber: ',"sappnumber",', NI: ',"sni",', Payroll Number: ',"spaynumber",', DOB: ',"sdob",',Address: ',"saddr1",', ',"saddr2",', ',"saddr3",', ',"stown",', ',"scounty",', ',"spostcode",', ID: ',"ssourceid"),
  "string"('Appnumber: ',"tappnumber",', NI: ',"tni",', Payroll Number: ',"tpaynumber",', DOB: ',"tdob",',Address: ',"taddr1",', ',"taddr2",', ',"taddr3",', ',"ttown",', ',"tcounty",', ',"tpostcode",', ID: ',"stargetid"));
  if "trim"("snotes") <> '' then
    update "person" set "notes" = "string"("tnotes","char"(13),"snotes") where "personid" = "stargetid"
  end if;
  if "trim"("txnotes") <> '' then
    update "person" set "othernotes" = "string"("txnotes","char"(13),"sxnotes") where "personid" = "stargetid"
  end if;
  if "trim"("string"("taddr1","taddr2")) = '' then
    set "taddr1" = "saddr1";
    set "taddr2" = "saddr2";
    set "taddr3" = "saddr3";
    set "ttown" = "stown";
    set "tcounty" = "scounty";
    set "tcountry" = "scountry";
    set "tpostcode" = "spostcode"
  end if;
  if "trim"("isnull"("tni",'')) = '' then
    set "tni" = "sni"
  end if;
  if "tdob" is null then
    set "tdob" = "sdob"
  end if;
  if "trim"("isnull"("tsex",'')) = '' then
    set "tsex" = "ssex"
  end if;
  if "trim"("isnull"("tmstatus",'')) = '' then
    set "tmstatus" = "smstatus"
  end if;
  if "tchildren" is null then
    set "tchildren" = "schildren"
  end if;
  if "tstaffid" is null then
    set "tstaffid" = "sstaffid"
  end if;
  if "tcompliancestaffid" is null then
    set "tcompliancestaffid" = "scompliancestaffid"
  end if;
  if "trim"("isnull"("tappnumber",'')) = '' then
    set "tappnumber" = "sappnumber"
  end if;
  if "tregdate" is null then
    set "tregdate" = "sregdate"
  end if;
  if "trim"("isnull"("tpaynumber",'')) = '' then
    set "tpaynumber" = "spaynumber"
  end if;
  if "tchangedate" is null then
    set "tchangedate" = "schangedate"
  end if;
  if "tavailable" is null then
    set "tavailable" = "savailable";
    set "tavaildate" = "savaildate";
    set "tnoticeperiod" = "snoticeperiod"
  end if;
  if "trim"("salert") <> '' then
    set "talert" = "trim"("string"("talert",' ')) || "string"("salert")
  end if;
  if "sstatus" = 'C' then
    set "tstatus" = 'C'
  end if;
  update "person" set "ni" = "tni","dob" = "tdob","sex" = "tsex","maritalstatus" = "tmstatus",
    "children" = "tchildren","staffid" = "tstaffid","appnumber" = "tappnumber",
    "registrationdate" = "tregdate","payrollnumber" = "tpaynumber","changedate" = "tchangedate",
    "available" = "tavailable","availdate" = "tavaildate","noticeperiod" = "tnoticeperiod",
    "status" = "tstatus","alert" = "talert","addr1" = "taddr1","addr2" = "taddr2",
    "addr3" = "taddr3","town" = "ttown","county" = "tcounty","country" = "tcountry","postcode" = "tpostcode","compliancestaffid" = "tcompliancestaffid"
    where "personid" = "stargetid";
  begin
    if exists(select * from "availability" where "personid" = "stargetid") then
      delete from "availability" where "personid" = "ssourceid"
    else
      update "availability" set "personid" = "stargetid" where "personid" = "ssourceid"
    end if
  exception
    when others then set "idum" = 0
  end;
  update "TempShiftProgress" as "m" set "personid" = "stargetid" where "personid" = "ssourceid"
    and not exists(select * from "TempShiftProgress" where "personid" = "stargetid" and "TempShiftPlanID" = "m"."TempShiftPlanID");
  delete from "TempShiftProgress" where "personid" = "ssourceid";
  update "PersonInterest" as "m" set "personid" = "stargetid" where "personid" = "ssourceid"
    and not exists(select * from "PersonInterest" where "personid" = "stargetid" and "staffID" = "m"."staffID");
  delete from "PersonInterest" where "personid" = "ssourceid";
  update "offlimits" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "DocPackValidation" set "personid" = "stargetid" where "personid" = "ssourceid";
  begin
    update "psHealthPerson" set "personid" = "stargetid" where "personid" = "ssourceid"
  exception
    when others then set "idum" = 0
  end;
  update "MasterRosterShift" set "defaultpersonid" = "stargetid" where "defaultpersonid" = "ssourceid";
  update "MasterRosterPreferredPerson" as "m" set "personid" = "stargetid" where "personid" = "ssourceid"
    and not exists(select * from "MasterRosterPreferredPerson" where "personid" = "stargetid" and "MasterRosterID" = "m"."MasterRosterID");
  delete from "MasterRosterPreferredPerson" where "personid" = "ssourceid";
  update "MasterRosterFailLog" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "contactevent" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "broadbeancandidate" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "externalemployment" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "audittrail" set "recordid" = "stargetid" where "recordid" = "ssourceid" and("audittype" = 'PERSON' or "audittype" = 'DOCUMENT');
  update "diary" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "employment" set "personid" = "stargetid" where "personid" = "ssourceid";
  select "extnumber" into "payextnum" from "pay_employee" where "personid" = "ssourceid";
  if exists(select * from "withholds" where "personid" = "ssourceid") then
    update "withholds" as "w" set "personid" = "stargetid" where "personid" = "ssourceid"
      and not exists(select * from "withholds" where "personid" = "stargetid" and "companyid" = "w"."companyid");
    update "withholds" as "w" set "personid" = "stargetid" where "personid" = "ssourceid"
      and not exists(select * from "withholds" where "personid" = "stargetid" and "vacancyid" = "w"."vacancyid");
    delete from "withholds" where "personid" = "ssourceid"
  end if;
  if exists(select * from "pay_employee" where "personid" = "stargetid") then
    delete from "pay_employee" where "personid" = "ssourceid"
  else
    update "pay_employee" set "personid" = "stargetid" where "personid" = "ssourceid"
  end if;
  if "payextnum" is not null then
    update "pay_employee" set "extnumber" = "payextnum" where "personid" = "stargetid" and "isnull"("extnumber",0) = 0
  end if;
  update "pay_employment" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "progress" set "personid" = "stargetid" where "personid" = "ssourceid";
  delete from "sentcvs" as "s1" where "personid" = "ssourceid" and exists(select * from "sentcvs" as "s2" where "s1"."contacteventid" = "s2"."contacteventid" and "s2"."personid" = "stargetid");
  update "sentcvs" set "personid" = "stargetid" where "personid" = "ssourceid";
  begin
    update "pay_holiday" set "personid" = "stargetid" where "personid" = "ssourceid"
  exception
    when others then set "idum" = 0
  end;
  begin
    update "tempslot" set "personid" = "stargetid" where "personid" = "ssourceid"
  exception
    when others then set "idum" = 0
  end;
  update "tempshift" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "tempsubsidy" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "temptimesheet" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "tempprovtimesheet" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "temppoolmember" as "tx" set "personid" = "stargetid" where "personid" = "ssourceid"
    and not exists(select * from "temppoolmember" where "personid" = "stargetid"
      and "tempdeskid" = "tx"."tempdeskid");
  delete from "temppoolmember" where "personid" = "ssourceid";
  update "oledocument" set "ownerid" = "stargetid" where "ownertype" = 'P' and "ownerid" = "ssourceid";
  update "interpersonlink" set "person1id" = "stargetid" where "person1id" = "ssourceid";
  update "interpersonlink" set "person2id" = "stargetid" where "person2id" = "ssourceid";
  update "personincompatibility" set "person1id" = "stargetid" where "person1id" = "ssourceid";
  update "personincompatibility" set "person2id" = "stargetid" where "person2id" = "ssourceid";
  update "vacancy" set "recipientid" = "stargetid" where "recipientid" = "ssourceid";
  update "AWRWeeklyDetail" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "AWRJobMaster" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "EBtimesheet" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "TSQueryLog" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "CompanySDS" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "ReferenceRequest" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "CompliancePersonDomain" as "t" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "CompliancePersonStatus" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "ConsentPersonOption" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "ConsentPersonOptionHistory" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "DataManagementLog" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "IQXAccountsStatistics" set "personid" = "stargetid" where "personid" = "ssourceid";
  update "PersonWillingnessHistory" as "t" set "personid" = "stargetid" where "personid" = "ssourceid" and not exists(select * from "PersonWillingness" as "s" where "s"."personid" = "t"."personid");
  update "PersonWillingness" as "t" set "personid" = "stargetid" where "personid" = "ssourceid" and not exists(select * from "PersonWillingness" as "s" where "s"."personid" = "t"."personid");
  if exists(select * from "sys"."systable" where "table_name" = 'TSImage') then update "TSImage" set "personid" = "stargetid" where "personid" = "ssourceid" end if;
  call "mergephones"('P',"ssourceid","stargetid");
  call "mergequestionnaire"('P',"ssourceid","stargetid");
  call "mergequestionnaire"('A%',"ssourceid","stargetid");
  if exists(select "id" from "blobstore" where "id" = "stargetid" and "class" = 'V') then
    delete from "blobstore" where "id" = "ssourceid" and "class" = 'V'
  else
    update "blobstore" set "id" = "stargetid" where "id" = "ssourceid" and "class" = 'V';
    update "person" set "cvtext" = "scvtext" where "personid" = "stargetid"
  end if;
  if exists(select "id" from "blobstore" where "id" = "stargetid" and "class" = 'J') then
    delete from "blobstore" where "id" = "ssourceid" and "class" = 'J'
  else
    update "blobstore" set "id" = "stargetid" where "id" = "ssourceid" and "class" = 'J'
  end if;
  update "search" as "sx" set "personid" = "stargetid" where "personid" = "ssourceid"
    and not exists(select * from "search" where "personid" = "stargetid"
      and "departmentid" = "sx"."departmentid");
  delete from "search" where "personid" = "ssourceid";
  delete from "criterion" where "searchlocation" like 'P%' and "searchlocation" <> 'P' and "id" = "ssourceid";
  if exists(select * from "iqxnetuserlink" where "personid" = "stargetid") then
    select first "iqxnetuserid" into "snetid" from "iqxnetuserlink" where "personid" = "ssourceid";
    delete from "iqxnetuserlink" where "personid" = "ssourceid";
    delete from "iqxnetuser" where "iqxnetuserid" = "snetid" and not exists(select * from "iqxnetuserlink" where "iqxnetuserid" = "iqxnetuser"."iqxnetuserid")
  else
    update "iqxnetuserlink" set "personid" = "stargetid" where "personid" = "ssourceid"
  end if;
  delete from "person" where "personid" = "ssourceid"
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MergePeople" IS 
{create procedure MergePeople 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( 
  in ssourceid char(20),in stargetid char(20) ) 
begin
  declare scvtext long varchar;
  declare snotes long varchar;
  declare tnotes long varchar;
  declare sxnotes long varchar;
  declare txnotes long varchar;
  declare salert char(100);
  declare talert char(100);
  declare sni char(15);
  declare tni char(15);
  declare sdob date;
  declare tdob date;
  declare ssex char(1);
  declare tsex char(1);
  declare smstatus char(1);
  declare tmstatus char(1);
  declare schildren smallint;
  declare tchildren smallint;
  declare sstaffid char(20);
  declare tstaffid char(20);
  declare scompliancestaffid char(20);
  declare tcompliancestaffid char(20);
  declare sappnumber char(12);
  declare tappnumber char(12);
  declare sregdate date;
  declare tregdate date;
  declare spaynumber char(15);
  declare tpaynumber char(15);
  declare schangedate timestamp;
  declare tchangedate timestamp;
  declare sstatus char(1);
  declare tstatus char(1);
  declare savailable char(1);
  declare savaildate date;
  declare snoticeperiod smallint;
  declare tavailable char(1);
  declare tavaildate date;
  declare tnoticeperiod smallint;
  declare saddr1 char(40);
  declare saddr2 char(40);
  declare saddr3 char(40);
  declare taddr1 char(40);
  declare taddr2 char(40);
  declare taddr3 char(40);
  declare stown char(30);
  declare ttown char(30);
  declare scounty char(30);
  declare tcounty char(30);
  declare scountry char(30);
  declare tcountry char(30);
  declare spostcode char(30);
  declare tpostcode char(30);
  declare sname char(30);
  declare tname char(30);
  declare idum smallint;
  declare payextnum integer;
  declare snetid char(20);
  select ni,dob,sex,maritalstatus,children,staffid,appnumber,
    registrationdate,payrollnumber,changedate,available,availdate,
    noticeperiod,status,alert,notes,othernotes,
    addr1,addr2,addr3,town,county,country,postcode,cvtext,name, compliancestaffid into sni,
    sdob,ssex,smstatus,schildren,sstaffid,sappnumber,
    sregdate,spaynumber,schangedate,savailable,savaildate,
    snoticeperiod,sstatus,salert,snotes,
    sxnotes,saddr1,saddr2,saddr3,stown,scounty,scountry,
    spostcode,scvtext,sname,scompliancestaffid from person where personid = ssourceid;
  select ni,dob,sex,maritalstatus,children,staffid,appnumber,
    registrationdate,payrollnumber,changedate,available,availdate,
    noticeperiod,status,alert,notes,othernotes,
    addr1,addr2,addr3,town,county,country,postcode,name, compliancestaffid into tni,
    tdob,tsex,tmstatus,tchildren,tstaffid,tappnumber,
    tregdate,tpaynumber,tchangedate,tavailable,tavaildate,
    tnoticeperiod,tstatus,talert,tnotes,
    txnotes,taddr1,taddr2,taddr3,ttown,tcounty,tcountry,
    tpostcode,tname,tcompliancestaffid from person where personid = stargetid;
  call AuditLog('PERSON',
  stargetid,
  string('Person ',sname,' merged into ',tname),
  string('Appnumber: ',sappnumber,', NI: ',sni,', Payroll Number: ',spaynumber,', DOB: ',sdob,',Address: ',saddr1,', ',saddr2,', ',saddr3,', ',stown,', ',scounty,', ',spostcode,', ID: ',ssourceid),
  string('Appnumber: ',tappnumber,', NI: ',tni,', Payroll Number: ',tpaynumber,', DOB: ',tdob,',Address: ',taddr1,', ',taddr2,', ',taddr3,', ',ttown,', ',tcounty,', ',tpostcode,', ID: ',stargetid));
  if trim(snotes) <> '' then
    update person set notes = string(tnotes,"char"(13),snotes) where personid = stargetid
  end if;
  if trim(txnotes) <> '' then
    update person set othernotes = string(txnotes,"char"(13),sxnotes) where personid = stargetid
  end if;
  if trim(string(taddr1,taddr2)) = '' then
    set taddr1 = saddr1;
    set taddr2 = saddr2;
    set taddr3 = saddr3;
    set ttown = stown;
    set tcounty = scounty;
    set tcountry = scountry;
    set tpostcode = spostcode
  end if;
  if trim(isnull(tni,'')) = '' then
    set tni = sni
  end if;
  if tdob is null then
    set tdob = sdob
  end if;
  if trim(isnull(tsex,'')) = '' then
    set tsex = ssex
  end if;
  if trim(isnull(tmstatus,'')) = '' then
    set tmstatus = smstatus
  end if;
  if tchildren is null then
    set tchildren = schildren
  end if;
  if tstaffid is null then
    set tstaffid = sstaffid
  end if;
  if tcompliancestaffid is null then
    set tcompliancestaffid = scompliancestaffid
  end if;
  if trim(isnull(tappnumber,'')) = '' then
    set tappnumber = sappnumber
  end if;
  if tregdate is null then
    set tregdate = sregdate
  end if;
  if trim(isnull(tpaynumber,'')) = '' then
    set tpaynumber = spaynumber
  end if;
  if tchangedate is null then
    set tchangedate = schangedate
  end if;
  if tavailable is null then
    set tavailable = savailable;
    set tavaildate = savaildate;
    set tnoticeperiod = snoticeperiod
  end if;
  if trim(salert) <> '' then
    set talert = trim(string(talert,' ')) || string(salert)
  end if;
  if sstatus = 'C' then
    set tstatus = 'C'
  end if;
  update person set ni = tni,dob = tdob,sex = tsex,maritalstatus = tmstatus,
    children = tchildren,staffid = tstaffid,appnumber = tappnumber,
    registrationdate = tregdate,payrollnumber = tpaynumber,changedate = tchangedate,
    available = tavailable,availdate = tavaildate,noticeperiod = tnoticeperiod,
    status = tstatus,alert = talert,addr1 = taddr1,addr2 = taddr2,
    addr3 = taddr3,town = ttown,county = tcounty,country = tcountry,postcode = tpostcode, compliancestaffid = tcompliancestaffid
    where personid = stargetid;
  begin
    if exists(select * from availability where personid = stargetid) then
      delete from availability where personid = ssourceid
    else
      update availability set personid = stargetid where personid = ssourceid
    end if
  exception
    when others then set idum = 0
  end;
  update TempShiftProgress m set personid = stargetid where personid = ssourceid 
      and not exists(select * from TempShiftProgress where personid = stargetid and TempShiftPlanID = m.TempShiftPlanID);
  delete from TempShiftProgress where personid = ssourceid;
  update PersonInterest m set personid = stargetid where personid = ssourceid 
      and not exists(select * from PersonInterest where personid = stargetid and staffID = m.staffID);
  delete from PersonInterest where personid = ssourceid;
  update offlimits set personid = stargetid where personid = ssourceid; 
  update DocPackValidation set personid = stargetid where personid = ssourceid; 
  begin
    update psHealthPerson set personid = stargetid where personid = ssourceid
  exception
    when others then set idum = 0
  end;
  update MasterRosterShift set defaultpersonid = stargetid where defaultpersonid = ssourceid; 
  update MasterRosterPreferredPerson m set personid = stargetid where personid = ssourceid 
      and not exists(select * from MasterRosterPreferredPerson where personid = stargetid and MasterRosterID = m.MasterRosterID);
  delete from MasterRosterPreferredPerson where personid = ssourceid;
  update MasterRosterFailLog set personid = stargetid where personid = ssourceid;
  update contactevent set personid = stargetid where personid = ssourceid;
  update broadbeancandidate set personid = stargetid where personid = ssourceid;
  update externalemployment set personid = stargetid where personid = ssourceid;
  update audittrail set recordid = stargetid where recordid = ssourceid and (audittype = 'PERSON' or audittype = 'DOCUMENT');
  update diary set personid = stargetid where personid = ssourceid;
  update employment set personid = stargetid where personid = ssourceid;
  select extnumber into payextnum from pay_employee where personid = ssourceid;
  if exists(select * from withholds where personid = ssourceid) then
    update withholds as w set personid = stargetid where personid = ssourceid
      and not exists(select * from withholds where personid = stargetid and companyid = w.companyid);
    update withholds as w set personid = stargetid where personid = ssourceid
      and not exists(select * from withholds where personid = stargetid and vacancyid = w.vacancyid);
    delete from withholds where personid = ssourceid
  end if;
  if exists(select * from pay_employee where personid = stargetid) then
    delete from pay_employee where personid = ssourceid
  else
    update pay_employee set personid = stargetid where personid = ssourceid
  end if;
  if payextnum is not null then
    update pay_employee set extnumber = payextnum where personid = stargetid and isnull(extnumber,0) = 0
  end if;
  update pay_employment set personid = stargetid where personid = ssourceid;
  update progress set personid = stargetid where personid = ssourceid;
 delete from sentcvs s1 where personid = ssourceid and exists(select * from sentcvs s2 where s1.contacteventid  = s2.contacteventid and s2.personid = stargetid);  
  update sentcvs set personid = stargetid where personid = ssourceid;
  begin
    update pay_holiday set personid = stargetid where personid = ssourceid
  exception
    when others then set idum = 0
  end;
  begin
    update tempslot set personid = stargetid where personid = ssourceid
  exception
    when others then set idum = 0
  end;
  update tempshift set personid = stargetid where personid = ssourceid;
  update tempsubsidy set personid = stargetid where personid = ssourceid;
  update temptimesheet set personid = stargetid where personid = ssourceid;
  update tempprovtimesheet set personid = stargetid where personid = ssourceid;
  update temppoolmember as tx set personid = stargetid where personid = ssourceid
    and not exists(select * from temppoolmember where personid = stargetid
      and tempdeskid = tx.tempdeskid);
  delete from temppoolmember where personid = ssourceid;
  update oledocument set ownerid = stargetid where ownertype = 'P' and ownerid = ssourceid;
  update interpersonlink set person1id = stargetid where person1id = ssourceid;
  update interpersonlink set person2id = stargetid where person2id = ssourceid;
  update personincompatibility set person1id = stargetid where person1id = ssourceid;
  update personincompatibility set person2id = stargetid where person2id = ssourceid;
  update vacancy set recipientid = stargetid where recipientid = ssourceid;
  update AWRWeeklyDetail set personid = stargetid where personid = ssourceid;
  update AWRJobMaster set personid = stargetid where personid = ssourceid;
  update EBtimesheet set personid = stargetid where personid = ssourceid;
  update TSQueryLog set personid = stargetid where personid = ssourceid;
  update CompanySDS set personid = stargetid where personid = ssourceid;
  update ReferenceRequest set personid = stargetid where personid = ssourceid;
  update CompliancePersonDomain t set personid = stargetid where personid = ssourceid;
  update CompliancePersonStatus set personid = stargetid where personid = ssourceid;
  update ConsentPersonOption set personid = stargetid where personid = ssourceid;
  update ConsentPersonOptionHistory set personid = stargetid where personid = ssourceid;
  update DataManagementLog set personid = stargetid where personid = ssourceid;
  update IQXAccountsStatistics set personid = stargetid where personid = ssourceid;
  update PersonWillingnessHistory t set personid = stargetid where personid = ssourceid and not exists(select * from PersonWillingness s where s.personid  = t.personid);
  update PersonWillingness t set personid = stargetid where personid = ssourceid and not exists(select * from PersonWillingness s where s.personid  = t.personid);
  if exists(select * from sys.systable where table_name = 'TSImage') then update TSImage set personid = stargetid where personid = ssourceid end if;
  call mergephones('P',ssourceid,stargetid);
  call mergequestionnaire('P',ssourceid,stargetid);
  call mergequestionnaire('A%',ssourceid,stargetid);
  if exists(select id from blobstore where id = stargetid and class = 'V') then
    delete from blobstore where id = ssourceid and class = 'V'
  else
    update blobstore set id = stargetid where id = ssourceid and class = 'V';
    update person set cvtext = scvtext where personid = stargetid
  end if;
  if exists(select id from blobstore where id = stargetid and class = 'J') then
    delete from blobstore where id = ssourceid and class = 'J'
  else
    update blobstore set id = stargetid where id = ssourceid and class = 'J';
  end if;
  update search as sx set personid = stargetid where personid = ssourceid
    and not exists(select * from search where personid = stargetid
      and departmentid = sx.departmentid);
  delete from search where personid = ssourceid;
  delete from criterion where searchlocation like 'P%' and searchlocation <> 'P' and id = ssourceid;
  if exists(select * from iqxnetuserlink where personid = stargetid) then
    select first iqxnetuserid into snetid from iqxnetuserlink where personid = ssourceid;
    delete from iqxnetuserlink where personid = ssourceid;
    delete from iqxnetuser where iqxnetuserid = snetid and not exists(select * from iqxnetuserlink where iqxnetuserid = iqxnetuser.iqxnetuserid)
  else
    update iqxnetuserlink set personid = stargetid where personid = ssourceid
  end if;
  delete from person where personid = ssourceid
end
}
</code>