pears.NetQuestionOwnerID

CREATE FUNCTION "pears"."NetQuestionOwnerID"( IN "pBaseTagLocation" CHAR(3),IN "pBaseID" CHAR(20),IN "pRequiredTagLocation" CHAR(3) ) 
RETURNS CHAR(20)
deterministic
// IQXWeb
BEGIN
  DECLARE "placid" CHAR(20);
  DECLARE "persid" CHAR(20);
  DECLARE "vacid" CHAR(20);
  DECLARE "empid" CHAR(20);
  DECLARE "compid" CHAR(20);
  DECLARE "origRequiredLocation" CHAR(3);
  SET "origRequiredLocation" = "pRequiredTagLocation";
  SET "pBaseTagLocation" = "left"("pBaseTagLocation",1); // Departmental portion can be ignored
  IF "pBaseTagLocation" = 'A' THEN // BOTH USE personid
    SET "pBaseTagLocation" = 'P'
  END IF;
  SET "pRequiredTagLocation" = "left"("pRequiredTagLocation",1);
  IF "pRequiredTagLocation" = 'A' THEN
    SET "pRequiredTagLocation" = 'P'
  END IF;
  IF "pBaseTagLocation" = "pRequiredTagLocation" THEN // Simplest CASE
    RETURN "isnull"("pBaseID","origRequiredLocation") // IF pBaseID IS NULL we are interested IN whether the tag should be available
  END IF;
  IF "pBaseID" IS NULL THEN
    CASE "pRequiredTagLocation" // RETURN NULL ON any invalid path FROM Base TO Required
    WHEN 'C' THEN IF "locate"('EVSLT',"pBaseTagLocation") = 0 THEN RETURN NULL
      END IF WHEN 'E' THEN IF "locate"('VSLT',"pBaseTagLocation") = 0 THEN RETURN NULL
      END IF WHEN 'V' THEN IF "locate"('SLT',"pBaseTagLocation") = 0 THEN RETURN NULL
      END IF WHEN 'L' THEN IF "locate"('T',"pBaseTagLocation") = 0 THEN RETURN NULL
      END IF WHEN 'P' THEN IF "locate"('LT',"pBaseTagLocation") = 0 THEN RETURN NULL
      END IF WHEN 'T' THEN RETURN NULL
    WHEN 'S' THEN RETURN NULL
    END CASE;
    RETURN "origRequiredLocation" // Valid
  END IF;
  CASE "pBaseTagLocation"
  WHEN 'T' THEN
    SELECT "t"."placementid","t"."personid","t"."vacancyid","e"."employmentid","e"."companyid" INTO "placid","persid","vacid","empid","compid"
      FROM "tempprovtimesheet" AS "t" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e"
      WHERE "t"."tempprovtimesheetid" = "pBaseID";
    IF "persid" IS NULL THEN
      SELECT "t"."placementid","t"."personid","v"."vacancyid","e"."employmentid","e"."companyid" INTO "placid","persid","vacid","empid","compid"
        FROM "temptimesheet" AS "t" KEY LEFT OUTER JOIN("placement" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e")
        WHERE "t"."temptimesheetid" = "pBaseID"
    END IF WHEN 'L' THEN
    SELECT "ep"."personid","v"."vacancyid","e"."employmentid","e"."companyid" INTO "persid","vacid","empid","compid"
      FROM "placement" KEY JOIN "employment" AS "ep"
        ,"placement" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e"
      WHERE "placement"."placementid" = "pBaseID"
  WHEN 'S' THEN
    SELECT "v"."vacancyid","e"."employmentid","e"."companyid" INTO "vacid","empid","compid"
      FROM "tempshiftplan" AS "s" KEY JOIN "vacancy" AS "v" KEY JOIN "employment" AS "e"
      WHERE "s"."tempshiftplanid" = "pBaseID"
  WHEN 'V' THEN
    SELECT "e"."employmentid","e"."companyid" INTO "empid","compid"
      FROM "vacancy" AS "v" KEY JOIN "employment" AS "e"
      WHERE "v"."vacancyid" = "pBaseID"
  WHEN 'E' THEN
    SELECT "e"."companyid"
      INTO "compid"
      FROM "employment" AS "e"
      WHERE "e"."employmentid" = "pBaseID"
  END CASE;
  CASE "pRequiredTagLocation"
  WHEN 'L' THEN
    RETURN "placid"
  WHEN 'P' THEN
    RETURN "persid"
  WHEN 'V' THEN
    RETURN "vacid"
  WHEN 'E' THEN
    RETURN "empid"
  WHEN 'C' THEN
    RETURN "compid"
  END CASE;
  RETURN NULL
END