pears.SetNetEmail [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.SetNetEmail ======
<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"."SetNetEmail"( 
  /* 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 "xEmail" char(250);
  if not "ptype" = 'E-mail' then
    return
  end if;
  select first "phone"."number" into "xEmail" from "phone" key join "phonetype" where "phone"."whoid" = "recid" and "phone"."who" = "cpe"
    and "phonetype"."name" = 'E-mail' order by 1 asc;
  if "xEmail" is null then
    return
  end if;
  set "xEmail" = "replace"("xEmail",'*','');
  if "cpe" = 'P' then
    update "iqxnetuserlink" key join "iqxnetuser" set "iqxnetuser"."EmailAddress" = "xEmail" where "iqxnetuserlink"."personid" = "recid"
  else
    update "iqxnetuserlink" key join "iqxnetuser" set "iqxnetuser"."EmailAddress" = "xEmail" where "iqxnetuserlink"."employmentid" = "recid"
  end if
exception -- Ignore duplicate email address error
  when others then
end
go

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

/* 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 xEmail char(250);
  if not ptype = 'E-mail' then
    return
  end if;
  select first phone.number into xEmail from phone key join phonetype where phone.whoid = recid and phone.who = cpe
    and phonetype.name = 'E-mail' order by 1 asc;
  if xEmail is null then
    return
  end if;
  set xEmail = replace(xEmail,'*','');
  if cpe = 'P' then
    update iqxnetuserlink key join iqxnetuser set iqxnetuser.EmailAddress = xEmail where iqxnetuserlink.personid = recid
  else
    update iqxnetuserlink key join iqxnetuser set iqxnetuser.EmailAddress = xEmail where iqxnetuserlink.employmentid = recid
  end if
exception
  when others then -- Ignore duplicate email address error
end
}
</code>