pears.RequestLinkedEmailStaffNotification

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'