====== pears.RequestLinkedEmailStaffNotification ======
<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"."RequestLinkedEmailStaffNotification"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "pTopic" char(50),in "pPersonID" char(20) default null,in "pCompanyID" char(20) default null,in "pVacancyID" char(20) default null,in "pDepartmentID" char(2) default null,in "pDivisionID" char(20) default null ) 
begin
  declare "abody" long varchar;
  select first "body" into "abody" from "EmailStaffNotificationAddress" where "topic" = "ptopic" and "body" is not null
    and("departmentid" = "pdepartmentid" or "departmentid" is null) and("divisionid" = "pdivisionid" or "divisionid" is null);
  if "trim"("isnull"("abody",'')) <> '' then
    set "abody" = "replace"("abody",'{personid}',"isnull"("pPersonID",''));
    set "abody" = "replace"("abody",'{companyid}',"isnull"("pCompanyID",''));
    set "abody" = "replace"("abody",'{vacancyid}',"isnull"("pVacancyID",''));
    set "abody" = "replace"("abody",'{loadform}','<a href=iqxw://iqx/loadform/');
    set "abody" = "replace"("abody",'{/loadform}','>Open in IQX</a>');
    insert into "EmailStaffNotification"( "id","topic","body","departmentid","divisionid","requested" ) values
      ( "uniquekey"('x'),"pTopic","aBody","pDepartmentID","pDivisionID",current timestamp ) 
  end if
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."RequestLinkedEmailStaffNotification" IS 
'create procedure RequestLinkedEmailStaffNotification 

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

( in pTopic char(50),in pPersonID char(20) default null,in pCompanyID char(20) default null,in pVacancyID char(20) default null,in pDepartmentID char(2) default null,in pDivisionID char(20) default null ) 
begin
  declare abody long varchar;
  select first body into abody from EmailStaffNotificationAddress where topic = ptopic and body is not null
    and(departmentid = pdepartmentid or departmentid is null) and(divisionid = pdivisionid or divisionid is null);
  if trim(isnull(abody,'''')) <> '''' then
    set abody = replace(abody,''{personid}'',isnull(pPersonID,''''));
    set abody = replace(abody,''{companyid}'',isnull(pCompanyID,''''));
    set abody = replace(abody,''{vacancyid}'',isnull(pVacancyID,''''));
    set abody = replace(abody,''{loadform}'',''<a href=iqxw://iqx/loadform/'');
    set abody = replace(abody,''{/loadform}'',''>Open in IQX</a>'');
    insert into EmailStaffNotification( id,topic,body,departmentid,divisionid,requested ) values
      ( uniquekey(''x''),pTopic,aBody,pDepartmentID,pDivisionID,current timestamp ) 
  end if
end'
</code>