====== pears.CreateICSFile ======
<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 function "pears"."CreateICSFile"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "puid" char(20),in "pLocation" char(200),in "pnotes" long varchar,in "pStartTime" timestamp,
  in "pEndTime" timestamp,in "pdescription" char(200),in "ppriority" smallint,
  in "precurrance" smallint,in "ppopup" smallint,in "ppopuptype" char(1) ) 
returns long varchar
begin
  declare "rv" long varchar;
  declare @createdstr char(20);
  declare @startstr char(20);
  declare @endstr char(20);
  declare @uid char(20);
  declare "crlf" char(2);
  set "rv" = '';
  set "crlf" = "char"(13)+"char"(10);
  if "pdescription" = '' then
    return
  end if;
  if "pstarttime" is null then
    return
  end if;
  if "isnull"("puid",'') = '' then
    set @uid = "uniquekey"('x')
  else set @uid = "puid"
  end if;
  set "rv" = 'BEGIN:VCALENDAR'+"crlf";
  set "rv" = "rv"+'PRODID:-//IQX//EN'+"crlf";
  set "rv" = "rv"+'VERSION:2.0'+"crlf";
  set "rv" = "rv"+'BEGIN:VEVENT'+"crlf";
  set "rv" = "rv"+'UID:'+@uid+"crlf";
  select "dateformat"(current utc timestamp,'yyyymmddThhnn00'),"dateformat"("pstarttime",'yyyymmddThhnn00'),
    "dateformat"("isnull"("pendtime","pstarttime"),'yyyymmddThhnn00')
    into @createdstr,@startstr,@endstr;
  set "rv" = "rv"+'CREATED:'+@createdstr+"crlf";
  if "trim"("isnull"("pnotes",'')) <> '' then
    set "rv" = "rv"+'DESCRIPTION:'+"pnotes"+"crlf"
  end if;
  set "rv" = "rv"+'DTSTAMP:'+@createdstr+"crlf";
  set "rv" = "rv"+'DTSTART:'+@startstr+"crlf";
  set "rv" = "rv"+'DTEND:'+@endstr+"crlf";
  if "isnull"("plocation",'') <> '' then
    set "rv" = "rv"+'LOCATION:'+"plocation"+"crlf"
  end if;
  if "isnull"("ppriority",0) > 0 then
    set "rv" = "rv"+'PRIORITY:'+"string"("ppriority")+"crlf"
  end if;
  if "isnull"("precurrance",0) > 0 then
    set "rv" = "rv"+'RRULE:FREQ=DAILY;COUNT='+"string"("precurrance")+';BYDAY=MO'+"crlf"
  end if;
  set "rv" = "rv"+'SEQUENCE:0'+"crlf";
  set "rv" = "rv"+'SUMMARY:'+"pdescription"+"crlf";
  if("isnull"("ppopup",0) > 0) and("isnull"("ppopuptype",'') in( 'D','M' ) ) then
    set "rv" = "rv"+'BEGIN:VALARM'+"crlf";
    set "rv" = "rv"+'TRIGGER:-PT'+"string"("ppopup")+"ppopuptype"+"crlf";
    set "rv" = "rv"+'DESCRIPTION:Reminder'+"crlf";
    set "rv" = "rv"+'END:VALARM'+"crlf"
  end if;
  set "rv" = "rv"+'END:VEVENT'+"crlf";
  set "rv" = "rv"+'END:VCALENDAR';
  return "rv"
end
</code>