====== pears.IQXNetTemplateGradeList ======
<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"."IQXNetTemplateGradeList" IS 
{create function IQXNetTemplateGradeList 
(in templateid char(20),in deptid char(20))
returns long varchar
begin
  declare rv long varchar;
  declare xtagid char(20);
  declare xchoiceid char(20);
  declare xlocation char(20);
  declare xgradetagid char(20);
  declare xgradeid char(20);
  select first trim(isnull(tagid,'')),trim(isnull(tagchoiceid,'')),trim(taglocation),trim(isnull(gradetagid,'')),trim(isnull(essentialskillgradeid,'')) into xtagid,
    xchoiceid,xlocation,xgradetagid,xgradeid from tempshifttemplate where tempshifttemplateid = templateid;
  if xlocation = 'A' then
    set xlocation=string('A',deptid)
  end if;
  if xgradetagid = '' then
    set xgradetagid=xtagid
  end if;
  set rv=null;
  if xgradetagid <> '' and xgradeid = '' then
    select list(string(c.tagchoiceid,']~[',c.description),']~[' order by isnull(c.value,c.sortorder) asc) into rv from tagchoice as c key join tag as t where
      c.taglocation = xlocation and c.tagid = xgradetagid and(c.subchoice = 1 or t.tagtype = 'M')
  end if;
  return rv
end
}
</code>