pears.MergePeople

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
}