====== pears.CollectionItemDescription ======
<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"."CollectionItemDescription"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "CollectionID" char(20),in "ItemType" char(2),in "ItemID" char(20) ) 
returns varchar(250)
begin
  declare "rv" varchar(250);
  --  DECLARE CNote LONG VARCHAR;
  /* Item Types P -person, C -company, E -employment, L -placement, R -progress, T -timesheet (full), I -Invoice, */
  /*            V -vacancy, Q -timesheet query, 0 -contact event, S - Shift, Z- Provisional Timesheet */
  /* can use collectionid to get type */
  case "ItemType"
  when 'O' then set "rv" = 'Contact Event'
  when 'H' then select "name" into "rv" from "collection" where "collectionID" = "ItemID"
  when 'E' then select "string"("person"."name",', ',"company"."name") into "rv" from "employment" key join("company","person") where "employmentid" = "ItemID"
  when 'P' then select "name" into "rv" from "person" where "personid" = "ItemID"
  when 'Q' then select "name" into "rv" from "person" key join "tsquerylog" where "tsquerylogid" = "ItemID"
  when 'T' then select "string"("serialnumber",' - ',"name") into "rv" from "person" key join "temptimesheet" where "temptimesheetid" = "ItemID"
  when 'S' then select "ShiftSerialNumber" into "rv" from "tempshift" key join "tempshiftplan" where "tempshiftid" = "ItemID"
  when 'Z' then select "string"("serialnumber",' - ',"name") into "rv" from "person" key join "tempprovtimesheet" where "tempprovtimesheetid" = "ItemID"
  when 'R' then select "string"("p"."name",' - ',"c"."name") into "rv" from "person" key join "progress" key join "vacancy" key join "employment" key join "company" where "progressid" = "ItemID"
  when 'C' then select "string"("name",', ',"addr1",', ',"postcode") into "rv" from "company" where "companyid" = "ItemID"
  when 'I' then select "string"("ourref",' - ',(select "name" from "company" where "companyid" = "GetInvoiceCompanyID"("AccountCode"))) into "rv" from "IQacDocument" where "DocumentID" = "ItemID"
  when 'V' then select "string"("v"."Position",' - ',"c"."Name") into "rv" from "Company" as "c" key join "Employment" as "e" key join "Vacancy" as "v" where "v"."VacancyID" = "ItemID"
  when 'L' then select "string"("Person"."Name",' as ',"Employment"."Position",' at ',"Company"."Name") into "rv" from "Placement"
        key join("Employment" key join "Person","Vacancy" key join "Employment" as "ce" key join "Company") where "Placement"."PlacementID" = "ItemID"
  end case;
  /*if ItemType <> 'H' then 
select trim(substr(notes,1,35)) into CNote from collection where ID = ItemID;
if CNote > '' then set rv = string(rv,' - ',CNote)
end if
end if;*/
  return "rv"
end
</code>