====== 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>
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
}
</code>