====== pears.TempDeskCustomColumn ======
<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"."TempDeskCustomColumn" IS 
{create function TempDeskCustomColumn(in DeskID char(20),in RecordType char(2),in RecordID char(20), in ColNo TinyInt, in DeptID char(2) default null) 
returns char(100)
begin
  -- DeskID will be % if Temp Desk selection = All
  -- RecordType P=Person V=Vacancy L=Placement T=Timesheet TP=Provisional Timesheet
  -- RecordID=null means return column heading for RecordType P, V, L or T, or null if custom column not wanted
  -- RecordID=personid, vacancyid, placementid, temptimesheetid or tempprovtimesheetid according to RecordType
  declare "rv" char(100);
  set "rv" = null;
  if "recordid" is null then
    if "recordtype" = 'V' then
      case "colno"
      when 1 then set "rv" = ' PO Ref '
      end case
    end if;
    if "recordtype" = 'L' then
      case "colno"
      when 1 then set "rv" = ' PO Ref '
      end case
    end if;
    if "recordtype" = 'T' then
      case "colno"
      when 1 then set "rv" = '  Pay Details  '
      when 2 then set "rv" = '  Processing Details  '
      end case
    end if;
    return "rv"
  end if;
  if "recordtype" = 'V' then
    case "colno"
    when 1 then select first "TheirRef" into "rv" from "vacancy" where "vacancyid" = "recordid"
    end case
  end if;
  if "recordtype" = 'L' then
    case "colno"
    when 1 then select first "TheirRef" into "rv" from "Placement" where "placementid" = "recordid"
    end case
  end if;
  if "recordtype" = 'TP' then
    case "colno"
    when 1 then select top 1
        if "taxmethod" > 1 then "isnull"((select "companyname" from "pay_employee" key join "Person" key join "tempprovtimesheet" where "tempprovtimesheetid" = "recordid"),'CHECK PAYROLL RE TAX') else 'PAYE' endif into "rv" from "pay_employee" key join "person" key join "tempprovtimesheet" where "tempprovtimesheetid" = "recordid" order by 1 desc
    when 2 then(select first "TheirRef" into "rv" from "tempprovtimesheet" where "tempprovtimesheetid" = "recordid")
    end case
  end if;
  if "recordtype" = 'T' then
    case "colno"
    when 1 then select top 1
        if "temptimesheet"."taxmethod" > 1 then "isnull"((select "companyname" from "pay_employee" key join "Person" key join "temptimesheet" where "temptimesheetid" = "recordid"),'CHECK PAYROLL RE TAX') else 'PAYE' endif into "rv" from "pay_employee" key join "person" key join "temptimesheet" where "temptimesheetid" = "recordid" order by 1 desc
    when 2 then set "rv" = "string"('Tr: ',(select first "isnull"("TransferBatch",0) from "temptimesheet" where "temptimesheetid" = "recordid"),
        '  Inv: ',(select first "OurRef" into "rv" from "iqacdocument" key join "iqacjournal" as "j" join "temptimesheetline" as "TL" on "j"."xref" = 'T' and "j"."xrefid" = "TL"."temptimesheetlineid" where "TL"."temptimesheetid" = "recordid"))
    end case
  end if;
  return "rv"
end
}
</code>