pears.NetQuestionOwnerID

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
}