====== 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>
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetQuestionOwnerID" IS 
{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 t key join vacancy v key join employment 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 t key left outer join (placement key join vacancy v key join employment 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 ep, 
        placement key join vacancy v key join employment e
      where placement.placementid=pBaseID;
    when 'S' then
      select v.vacancyid,e.employmentid,e.companyid into vacid,empid,compid
      from 
        tempshiftplan s key join vacancy v key join employment e
      where s.tempshiftplanid=pBaseID;
    when 'V' then
      select e.employmentid,e.companyid into empid,compid
      from 
        vacancy v key join employment e
      where v.vacancyid=pBaseID;
    when 'E' then
      select e.companyid into compid
      from 
        employment 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>