pears.MergeEmployments

CREATE PROCEDURE "pears"."MergeEmployments"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "ssourceid" CHAR(20),IN "stargetid" CHAR(20) ) 
BEGIN
  DECLARE "snote" CHAR(100);
  DECLARE "tnote" CHAR(100);
  DECLARE "idum" SMALLINT;
  DECLARE "snetid" CHAR(20);
  SELECT "note" INTO "snote" FROM "employment" WHERE "employmentid" = "ssourceid";
  SELECT "note" INTO "tnote" FROM "employment" WHERE "employmentid" = "stargetid";
  IF "trim"("snote") <> '' THEN
    UPDATE "employment" SET "note" = "trim"("string"("tnote",' ')) || "string"("snote") WHERE "employmentid" = "stargetid"
  END IF;
  UPDATE "contactevent" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "diary" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "placement" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  BEGIN
    UPDATE "tempslot" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid"
  exception
    WHEN others THEN SET "idum" = 0
  END;
  UPDATE "vacancy" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "etip" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "companyaccount" SET "accountscontact" = "stargetid" WHERE "accountscontact" = "ssourceid";
  UPDATE "companyaccount" SET "timesheetcontact" = "stargetid" WHERE "accountscontact" = "ssourceid";
  UPDATE "CompanySDS" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "ReferenceRequest" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  UPDATE "VacancyRoleAllocation" AS "t" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid";
  CALL "mergephones"('CP',"ssourceid","stargetid");
  CALL "mergequestionnaire"('E',"ssourceid","stargetid");
  IF EXISTS(SELECT * FROM "iqxnetuserlink" WHERE "employmentid" = "stargetid") THEN
    SELECT FIRST "iqxnetuserid" INTO "snetid" FROM "iqxnetuserlink" WHERE "employmentid" = "ssourceid";
    DELETE FROM "iqxnetuserlink" WHERE "employmentid" = "ssourceid";
    DELETE FROM "iqxnetuser" WHERE "iqxnetuserid" = "snetid" AND NOT EXISTS(SELECT * FROM "iqxnetuserlink" WHERE "iqxnetuserid" = "iqxnetuser"."iqxnetuserid")
  ELSE
    UPDATE "iqxnetuserlink" SET "employmentid" = "stargetid" WHERE "employmentid" = "ssourceid"
  END IF;
  DELETE FROM "employment" WHERE "employmentid" = "ssourceid"
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."MergeEmployments" IS 
{CREATE PROCEDURE MergeEmployments 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN ssourceid CHAR(20),IN stargetid CHAR(20) ) 
BEGIN
  DECLARE snote CHAR(100);
  DECLARE tnote CHAR(100);
  DECLARE idum SMALLINT;
  DECLARE snetid CHAR(20);
  SELECT note INTO snote FROM employment WHERE employmentid = ssourceid;
  SELECT note INTO tnote FROM employment WHERE employmentid = stargetid;
  IF TRIM(snote) <> '' THEN
    UPDATE employment SET note = TRIM(string(tnote,' ')) || string(snote) WHERE employmentid = stargetid
  END IF;
  UPDATE contactevent SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE diary SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE placement SET employmentid = stargetid WHERE employmentid = ssourceid;
  BEGIN
    UPDATE tempslot SET employmentid = stargetid WHERE employmentid = ssourceid
  exception
    WHEN others THEN SET idum = 0
  END;
  UPDATE vacancy SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE etip SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE companyaccount SET accountscontact = stargetid WHERE accountscontact = ssourceid;
  UPDATE companyaccount SET timesheetcontact = stargetid WHERE accountscontact = ssourceid;
  UPDATE CompanySDS SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE ReferenceRequest SET employmentid = stargetid WHERE employmentid = ssourceid;
  UPDATE VacancyRoleAllocation t SET employmentid = stargetid WHERE employmentid = ssourceid ;
  CALL mergephones('CP',ssourceid,stargetid);
  CALL mergequestionnaire('E',ssourceid,stargetid);
  IF EXISTS(SELECT * FROM iqxnetuserlink WHERE employmentid = stargetid) THEN
    SELECT FIRST iqxnetuserid INTO snetid FROM iqxnetuserlink WHERE employmentid = ssourceid;
    DELETE FROM iqxnetuserlink WHERE employmentid = ssourceid;
    DELETE FROM iqxnetuser WHERE iqxnetuserid = snetid AND NOT EXISTS(SELECT * FROM iqxnetuserlink WHERE iqxnetuserid = iqxnetuser.iqxnetuserid)
  ELSE
    UPDATE iqxnetuserlink SET employmentid = stargetid WHERE employmentid = ssourceid
  END IF;
  DELETE FROM employment WHERE employmentid = ssourceid
END
}