pears.MergeEmployments [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.MergeEmployments ======
<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"."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
}
</code>