====== pears.NetQuestionOwnerID ======
<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>
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
</code>