pears.MergeCompanies

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
}