pears.CollectionItemDescription

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