pears.EssentialSkillGradeDescription

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."EssentialSkillGradeDescription" IS 
{CREATE FUNCTION EssentialSkillGradeDescription 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( 
  IN essentialskill CHAR(15),IN essentialskillgradeid CHAR(4),IN departmentid CHAR(2) ) 
RETURNS CHAR(200)
BEGIN
  DECLARE rv CHAR(200);
  DECLARE tloc CHAR(3);
  DECLARE tid CHAR(3);
  DECLARE tgid CHAR(3);
  DECLARE issubchoice INTEGER;
  DECLARE i INTEGER;
  IF TRIM(isnull(essentialskillgradeid,'')) = '' THEN
    RETURN ''
  END IF;
  SET issubchoice = 1; -- fallback default is graded skill question
  SET i = charindex(';',essentialskill);
  IF i > 0 THEN
    SET tloc = "left"(essentialskill,i-1);
    SET essentialskill = "right"(essentialskill,LENGTH(essentialskill)-i);
    IF tloc = 'A' THEN
      SET tloc = string('A',departmentid)
    END IF END IF;
  SET i = charindex(';',essentialskill);
  IF i > 0 THEN
    SET tid = "left"(essentialskill,i-1);
    SET essentialskill = "right"(essentialskill,LENGTH(essentialskill)-i)
  END IF;
  SET i = charindex(';',essentialskill);
  IF i > 0 THEN
    -- Not interested in the skill choiceid
    SET essentialskill = "right"(essentialskill,LENGTH(essentialskill)-i);
    SET i = charindex(';',essentialskill);
    IF i > 0 THEN
      SET tgid = TRIM("left"(essentialskill,i-1))
    ELSE
      SET tgid = TRIM(essentialskill)
    END IF;
    IF tgid <> '' THEN
      SET tid = tgid;
      SET issubchoice = 0
    END IF
  END IF;
  SET rv = (SELECT FIRST description FROM tagchoice WHERE taglocation = tloc AND tagid = tid AND tagchoiceid = essentialskillgradeid AND isnull(subchoice,0) = issubchoice);
  RETURN isnull(rv,essentialskillgradeid) -- fall back on the literal grade
END
}