pears.SetPhone

CREATE PROCEDURE "pears"."SetPhone"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "cpe" CHAR(2),IN "ptype" CHAR(25),IN "recid" CHAR(20),IN "snumber" CHAR(250) ) 
BEGIN
  DECLARE "i" INTEGER;
  DECLARE "s" CHAR(200);
  DECLARE "sname" CHAR(200);
  DECLARE "sid" CHAR(20);
  DECLARE "stype" CHAR(12);
  IF "cpe" = 'E' THEN
    SET "cpe" = 'CP'
  END IF;
  -- audit functionality
  IF EXISTS(SELECT * FROM "phonetype" WHERE "auditflag" = 1 AND "name" = "ptype" AND "cpe" = (CASE WHEN "company" = 1 THEN 'C' WHEN "contact" = 1 THEN 'CP' WHEN "applicant" = 1 THEN 'P' END)) THEN
    IF "cpe" = 'C' THEN
      SET "stype" = 'COMPANY';
      SET "sid" = "recid";
      SELECT "name" INTO "sname" FROM "company" WHERE "companyid" = "recid"
    END IF;
    IF "cpe" = 'P' THEN
      SET "stype" = 'PERSON';
      SET "sid" = "recid";
      SELECT "name" INTO "sname" FROM "person" WHERE "personid" = "recid"
    END IF;
    IF "cpe" = 'CP' THEN
      SET "stype" = 'COMPANY';
      SELECT "company"."companyid","string"("person"."name",', ',"company"."name") INTO "sid","sname" FROM "employment" KEY JOIN("person","company") WHERE "employment"."employmentid" = "recid"
    END IF;
    CALL "AuditLog"("stype","sid","string"("ptype",' Updated - ',"sname"),"getphone"("cpe","ptype","recid"),"snumber")
  END IF;
  --delete from phone from phone key join phonetype where phone.whoid = recid and phone.who = cpe and phonetype.name = ptype;
  DELETE FROM "phone" WHERE "whoid" = "recid" AND "who" = "cpe" AND "phonetypeid" = any(SELECT "phonetypeid" FROM "phonetype" WHERE "name" = "ptype");
  while "trim"("snumber") <> '' loop
    SET "i" = "patindex"('%[,;]%',"snumber");
    IF "i" > 0 THEN
      SET "s" = "left"("snumber","i"-1);
      SET "snumber" = "stuff"("snumber" || ' ',1,"i",NULL) -- stuff operation must not result in empty string or server bombs
    ELSE
      SET "s" = "snumber";
      SET "snumber" = ''
    END IF;
    CALL "addphone"("cpe","ptype","recid","trim"("s"))
  END loop;
  IF "ptype" = 'E-mail' THEN
    CALL "SetNetEmail"("cpe","ptype","recid","snumber")
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."SetPhone" IS 
{CREATE PROCEDURE SetPhone 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN cpe CHAR(2),IN ptype CHAR(25),IN recid CHAR(20),IN snumber CHAR(250) ) 
BEGIN
  DECLARE i INTEGER;
  DECLARE s CHAR(200);
  DECLARE sname CHAR(200);
  DECLARE sid CHAR(20);
  DECLARE stype CHAR(12);
  IF cpe = 'E' THEN
    SET cpe = 'CP'
  END IF;
  -- audit functionality
  IF EXISTS(SELECT * FROM phonetype WHERE auditflag = 1 AND name = ptype AND cpe = (CASE WHEN company = 1 THEN 'C' WHEN contact = 1 THEN 'CP' WHEN applicant = 1 THEN 'P' END)) THEN
    IF cpe = 'C' THEN
      SET stype = 'COMPANY';
      SET sid = recid;
      SELECT name INTO sname FROM company WHERE companyid = recid
    END IF;
    IF cpe = 'P' THEN
      SET stype = 'PERSON';
      SET sid = recid;
      SELECT name INTO sname FROM person WHERE personid = recid
    END IF;
    IF cpe = 'CP' THEN
      SET stype = 'COMPANY';
      SELECT company.companyid,string(person.name,', ',company.name) INTO sid,sname FROM employment KEY JOIN(person,company) WHERE employment.employmentid = recid
    END IF;
    CALL AuditLog(stype,sid,string(ptype,' Updated - ',sname),getphone(cpe,ptype,recid),snumber)
  END IF;
  --delete from phone from phone key join phonetype where phone.whoid = recid and phone.who = cpe and phonetype.name = ptype;
  DELETE FROM phone WHERE whoid = recid AND who = cpe AND phonetypeid = any(SELECT phonetypeid FROM phonetype WHERE name = ptype);
  while TRIM(snumber) <> '' loop
    SET i = patindex('%[,;]%',snumber);
    IF i > 0 THEN
      SET s = "left"(snumber,i-1);
      SET snumber = stuff(snumber || ' ',1,i,NULL) -- stuff operation must not result in empty string or server bombs
    ELSE
      SET s = snumber;
      SET snumber = ''
    END IF;
    CALL addphone(cpe,ptype,recid,TRIM(s))
  END loop;
  IF ptype = 'E-mail' THEN
    CALL SetNetEmail(cpe,ptype,recid,snumber)
  END IF
END
}