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