====== 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>
create function "pears"."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>