====== pears.InsertDiary ======
<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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."InsertDiary" IS 
{create function InsertDiary 

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

(in @StaffID char(20),in @DiaryFrom timestamp,in @DurationType char(1),in @Duration double,in @PopupMinutes smallint default
null,in @WhoEntered char(20),in @PersonID char(20) default
null,in @VacancyID char(20) default
null,in @EmploymentID char(20) default
null,in @ProgressID char(20) default
null,in @PlacementID char(20) default
null,in @Description char(100),in @Notes varchar(8000) default
null)
returns char(250)
begin
  -- for immediate pop up specify @PopupMinutes=0, for no pop up leave as null
  declare @NewDiaryID char(20);
  declare @DiaryTo timestamp;
  -- Check missing ID''s
  if @Employmentid is null and(@VacancyID is not null or @Progressid is not null or @Placementid is not null) then
    return('2:If a diary entry is to be linked to a vacancy, Progress item or Placement, then the Progress or Placement and Vacancy and EmploymentIDs must be specified.')
  end if;
  if(@Progressid is not null and @VacancyID is null) then
    set @VacancyID=(select VacancyID from Progress where Progress.ProgressID = @ProgressID)
  end if;
  if(@Placementid is not null and @VacancyID is null) then
    set @VacancyID=(select VacancyID from Placement where Placement.Placementid = @Placementid)
  end if;
  -- Check Duration and Popup not negative
  if @Duration < 0 or @PopupMinutes < 0 then return('4:Neither @Duration nor @PopupMinutes can be negative')
  end if;
  -- Check Duration Type & calculate @DiaryTo
  case @DurationType when 'R' then
    set @DiaryTo=@DiaryFrom when 'M' then
    set @DiaryTo=dateadd(minute,@Duration,@DiaryFrom) when 'H' then
    set @DiaryTo=dateadd(hour,@Duration,@DiaryFrom) when 'D' then
    set @DiaryTo=dateadd(day,@Duration,@DiaryFrom) when 'W' then
    set @DiaryTo=dateadd(week,@Duration,@DiaryFrom) else
    return('3:Duration Type must be one of R(eminder), M(inutes), H(ours), D(ays), W(eeks)')
  end case
  ; -- Create new ID
  set @NewDiaryID=uniquekey(1);
  -- Insert record
  insert into Diary( diaryid,staffid,personid,vacancyid,employmentid,progressid,placementid,diaryfrom,diaryto,description,notes,DurationType,Duration,PopupMinutes,WhoEntered) values( 
    @NewDiaryID,@StaffID,@PersonID,@VacancyID,@EmploymentID,@ProgressID,@PlacementID,@DiaryFrom,@DiaryTo,@Description,@Notes,@DurationType,@Duration,@PopupMinutes,@WhoEntered) ;
  if @@rowcount <> 1 then return('1:Error inserting diary record')
  end if;
  return(string('0:Diary record inserted~',@NewDiaryID))
exception
  when others then return('1:Error inserting diary record')
end
}
</code>