pears.CollectionItemDescription

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."CollectionItemDescription" IS 
{CREATE FUNCTION 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
}