pears.TempDeskCustomColumn

CREATE FUNCTION "pears"."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