====== pears.MergeCompanies ======
<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"."MergeCompanies"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "ssourceid" char(20),in "stargetid" char(20) ) 
begin
  declare "snotes" long varchar;
  declare "tnotes" long varchar;
  declare "salert" char(100);
  declare "talert" char(100);
  declare "sinvaddr" smallint;
  declare "sclicode" char(12);
  declare "tclicode" char(12);
  declare "ssalesstatus" char(20);
  declare "tsalesstatus" char(20);
  declare "sname" char(60);
  declare "tname" char(60);
  declare "idum" smallint;
  declare "comextnum" integer;
  select "notes","alert","trim"("isnull"("clientcode",'')),"invoiceaddress","name","companysalesstatusid" into "snotes","salert","sclicode","sinvaddr","sname","ssalesstatus" from "company" where "companyid" = "ssourceid";
  select "notes","alert","trim"("isnull"("clientcode",'')),"name","companysalesstatusid" into "tnotes","talert","tclicode","tname","tsalesstatus" from "company" where "companyid" = "stargetid";
  call "AuditLog"('COMPANY',
  "stargetid",
  "string"('Company ',"sname",' merged into ',"tname"),
  "string"('Client Code: ',"sclicode",', ID: ',"ssourceid"),
  "string"('Client Code: ',"tclicode",', ID: ',"stargetid"));
  if "trim"("snotes") <> '' then
    update "company" set "notes" = "string"("tnotes","char"(13),"snotes") where "companyid" = "stargetid"
  end if;
  if "trim"("salert") <> '' then
    update "company" set "alert" = "trim"("string"("talert",' ')) || "string"("salert") where "companyid" = "stargetid"
  end if;
  if "sclicode" <> '' and "tclicode" = '' then
    update "company" set "clientcode" = "sclicode" where "companyid" = "stargetid"
  end if;
  if "ssalesstatus" <> '' and "tsalesstatus" = '' then
    update "company" set "companysalesstatusid" = "ssalesstatus" where "companyid" = "stargetid"
  end if;
  if "sinvaddr" = 1 and "sclicode" <> '' and "tclicode" <> '' then
    update "company" set "clientcode" = "tclicode" where "clientcode" = "sclicode"
  end if;
  if "sinvaddr" = 1 and "sclicode" <> '' then
    update "company" set "invoiceaddress" = 1 where "companyid" = "stargetid"
  end if;
  update "employment" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "oledocument" set "ownerid" = "stargetid" where "ownertype" = 'C' and "ownerid" = "ssourceid";
  call "mergephones"('C',"ssourceid","stargetid");
  call "mergequestionnaire"('C',"ssourceid","stargetid");
  select "extnumber" into "comextnum" from "companyaccount" where "companyid" = "ssourceid";
  if exists(select "companyid" from "companyaccount" where "companyid" = "stargetid") then
    delete from "companyaccount" where "companyid" = "ssourceid"
  else
    update "companyaccount" set "companyid" = "stargetid" where "companyid" = "ssourceid"
  end if;
  if "comextnum" is not null then
    update "companyaccount" set "extnumber" = "comextnum" where "companyid" = "stargetid" and "isnull"("extnumber",0) = 0
  end if;
  begin
    if(select first "cashfriday" from "params") = 1 then
      update "cashfriday_clientcode" set "companyid" = "stargetid" where "companyid" = "ssourceid"
    end if
  exception
    when others then set "idum" = 0
  end;
  update "AWRWeeklyDetail" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "pay_employee" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "TempJobType" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "TempTimeSheet" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "EBTimeSheet" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "CascadedShift" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "CascadedVacancy" set "SecondaryAgencyID" = "stargetid" where "SecondaryAgencyID" = "ssourceid";
  update "CompanySDS" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "CompanyDeptDocType" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "IQXAccountsStatistics" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "TempDeskAgencyPoolMember" as "t" set "companyid" = "stargetid" where "companyid" = "ssourceid" and not exists(select * from "TempDeskAgencyPoolMember" as "s" where "s"."companyid" = "t"."companyid" and "s"."tempdeskid" = "t"."tempdeskid");
  update "offlimits" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "IQXNetMessageCompanyRecipient" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  update "withholds" set "companyid" = "stargetid" where "companyid" = "ssourceid";
  delete from "company" where "companyid" = "ssourceid"
end
go

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

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

(in ssourceid char(20),in stargetid char(20))
begin
  declare snotes long varchar;
  declare tnotes long varchar;
  declare salert char(100);
  declare talert char(100);
  declare sinvaddr smallint;
  declare sclicode char(12);
  declare tclicode char(12);
  declare ssalesstatus char(20);
  declare tsalesstatus char(20);
  declare sname char(60);
  declare tname char(60);
  declare idum smallint;
  declare comextnum integer;
  select notes,alert,trim(isnull(clientcode,'')),invoiceaddress,name,companysalesstatusid into snotes,salert,sclicode,sinvaddr,sname,ssalesstatus from company where companyid = ssourceid;
  select notes,alert,trim(isnull(clientcode,'')),name,companysalesstatusid into tnotes,talert,tclicode,tname,tsalesstatus from company where companyid = stargetid;
  call AuditLog('COMPANY',
  stargetid,
  string('Company ',sname,' merged into ',tname),
  string('Client Code: ',sclicode,', ID: ',ssourceid),
  string('Client Code: ',tclicode,', ID: ',stargetid));
  if trim(snotes) <> '' then
    update company set notes = string(tnotes,"char"(13),snotes) where companyid = stargetid
  end if;
  if trim(salert) <> '' then
    update company set alert = trim(string(talert,' ')) || string(salert) where companyid = stargetid
  end if;
  if sclicode <> '' and tclicode = '' then
    update company set clientcode = sclicode where companyid = stargetid
  end if;
  if ssalesstatus <> '' and tsalesstatus = '' then
    update company set companysalesstatusid = ssalesstatus where companyid = stargetid
  end if;
  if sinvaddr = 1 and sclicode <> '' and tclicode <> '' then
    update company set clientcode = tclicode where clientcode = sclicode
  end if;
  if sinvaddr = 1 and sclicode <> '' then
    update company set invoiceaddress = 1 where companyid = stargetid
  end if;
  update employment set companyid = stargetid where companyid = ssourceid;
  update oledocument set ownerid = stargetid where ownertype = 'C' and ownerid = ssourceid;
  call mergephones('C',ssourceid,stargetid);
  call mergequestionnaire('C',ssourceid,stargetid);
  select extnumber into comextnum from companyaccount where companyid = ssourceid;
  if exists(select companyid from companyaccount where companyid = stargetid) then
    delete from companyaccount where companyid = ssourceid
  else
    update companyaccount set companyid = stargetid where companyid = ssourceid
  end if;
  if comextnum is not null then
    update companyaccount set extnumber = comextnum where companyid = stargetid and isnull(extnumber,0) = 0
  end if;
  begin
    if(select first cashfriday from params) = 1 then
      update cashfriday_clientcode set companyid = stargetid where companyid = ssourceid
    end if
  exception
    when others then set idum=0
  end;
  update AWRWeeklyDetail set companyid = stargetid where companyid = ssourceid;
  update pay_employee set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update TempJobType set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update TempTimeSheet set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update EBTimeSheet set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update CascadedShift set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update CascadedVacancy set SecondaryAgencyID = stargetid where SecondaryAgencyID = ssourceid;
  update CompanySDS set companyid = stargetid where companyid = ssourceid;
  update CompanyDeptDocType set companyid = stargetid where companyid = ssourceid;
  update IQXAccountsStatistics set companyid = stargetid where companyid = ssourceid;
  update TempDeskAgencyPoolMember t set companyid = stargetid where companyid = ssourceid and   not exists(select * from TempDeskAgencyPoolMember s where s.companyid = t.companyid and s.tempdeskid = t.tempdeskid);
  update offlimits set companyid = stargetid where companyid = ssourceid;
  update IQXNetMessageCompanyRecipient set companyid = stargetid where companyid = ssourceid;
  update withholds set companyid = stargetid where companyid = ssourceid;
  delete from company where companyid = ssourceid
end
}
</code>