====== pears.SetQuestAnswer ======
<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 procedure "pears"."SetQuestAnswer"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  in "tlocation" char(3),in "tid" char(3),in "tchoiceid" char(4),in "parentid" char(20),in "tvalue" long varchar ) 
begin
  declare "ttype" char(1);
  declare "nvalue" double;
  declare "mstep" double;
  declare "svalue" long varchar;
  declare "baudit" smallint;
  declare "sname" char(200);
  declare "sid" char(20);
  declare "stype" char(12);
  declare "oval" long varchar;
  set "ttype" = null;
  set "svalue" = null;
  if "tchoiceid" = '' then
    set "tchoiceid" = null
  end if;
  if "tvalue" = 'Y' then
    set "nvalue" = 1
  else
    begin
      set "nvalue" = cast("tvalue" as double)
    exception
      when others then
        set "nvalue" = 0
    end end if;
  select "tagtype","minstep","auditflag" into "ttype","mstep","baudit" from "tag" where "taglocation" = "tlocation" and "tagid" = "tid";
  if "tlocation" like 'V%' then
    update "vacancy" set "QuestionnaireUpdated" = current timestamp where "vacancyid" = "parentid"
  end if;
  if "baudit" = 1 then
    if "tlocation" like 'C%' then
      set "stype" = 'COMPANY';
      set "sid" = "parentid";
      select "name" into "sname" from "company" where "companyid" = "parentid"
    else
      if "tlocation" = 'P' or "tlocation" like 'A%' then
        set "stype" = 'PERSON';
        set "sid" = "parentid";
        select "name" into "sname" from "person" where "personid" = "parentid"
      else if "tlocation" = 'E' then
          set "stype" = 'COMPANY';
          select "company"."companyid","string"("person"."name",', ',"company"."name") into "sid","sname" from "employment" key join("person","company") where "employment"."employmentid" = "parentid"
        else if "tlocation" like 'V%' then
            set "stype" = 'VACANCY';
            set "sid" = "parentid";
            select "string"("Company"."Name",' - ',"vacancy"."position",'(',"vacancy"."RefCode",')') into "sname" from "vacancy" key join "employment" key join "Company" where "vacancy"."vacancyid" = "parentid"
          else if "tlocation" like 'L%' then
              set "stype" = 'PLACEMENT';
              set "sid" = "parentid";
              select "string"("Company"."Name",' - ',"employment"."position") into "sname" from "placement" key join "employment" key join "Company" where "placement"."placementid" = "parentid"
            else if "tlocation" like 'S%' then
                set "stype" = 'SHIFTPLAN';
                set "sid" = "parentid" --;
              else --select string(Company.Name,' - ',employment.position) into sname from placement key join employment key join Company where tempshift.tempshiftplanid = parentid
                if "tlocation" like 'T%' then
                  set "stype" = 'TIMESHEET';
                  set "sid" = "parentid";
                  select
                    "isnull"(
                    (select first if "c"."Name" is not null then
                        "string"("t"."SerialNumber",': ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name")
                      else "string"("t"."SerialNumber",': ',"p"."Name",' - Internal ')
                      endif from "TempTimeSheet" as "t" key join "Person" as "p","TempTimeSheet" as "t" key left outer join("Placement" key join "Vacancy" as "v" key join "Employment" key join "Company" as "c") where "t"."TempTimeSheetID" = "parentid"),
                    (select first "string"("t"."SerialNumber",'(P): ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name") from "TempProvTimeSheet" as "t" key left outer join "Person" as "p","TempProvTimeSheet" as "t"
                        key left outer join("Vacancy" as "v" key join "Employment" key join "Company" as "c") where "t"."TempProvTimeSheetID" = "parentid"))
                    into "sname"
                else if "tlocation" like 'N%' then
                    set "stype" = 'CEVENT';
                    set "sid" = "parentid"
                  else if "tlocation" = 'U' then
                      set "stype" = 'USER';
                      set "sid" = "parentid"
                    else if "tlocation" like 'R%' then
                        set "stype" = 'REFER';
                        set "sid" = "parentid"
                      else if "tlocation" = 'I' then
                          set "stype" = 'PURCH';
                          set "sid" = "parentid"
                        else if "tlocation" = 'X' then
                            set "stype" = 'AGENCY';
                            set "sid" = "parentid"
                          else if "tlocation" like 'J%' then
                              set "stype" = 'COLLECT';
                              set "sid" = "parentid"
                            else
                              set "stype" = "string"('TAG ',"tlocation");
                              set "sid" = "parentid";
                              set "sname" = ''
                            end if
                          end if
                        end if
                      end if
                    end if
                  end if
                end if
              end if
            end if
          end if end if end if end if;
    if("length"("tchoiceid") > 0) and("ttype" <> 'M') then
      set "sname" = "string"((select "string"("tag"."description",': ',"tagchoice"."description") from "tagchoice" key join "tag"
          where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoice"."tagchoiceid" = "tchoiceid"),' Updated - ',"sname")
    else
      set "sname" = "string"((select "description" from "tag" where "taglocation" = "tlocation" and "tagid" = "tid"),' Updated - ',"sname")
    end if;
    if "tlocation" like 'U%' then
      set "sname" = "string"("sname",' - ',(select "userid" from "staff" where "staffid" = "parentid"))
    end if;
    if "length"("tlocation") > 1 then
      set "sname" = "string"((select "name" from "department" where "departmentid" = "substring"("tlocation",2)),' ',"sname")
    end if;
    if "ttype" in( 'T','U' ) then
      select first "textvalue" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
    else if "ttype" = 'D' then
        select first "dateformat"("dateadd"("day","value","date"('1899-12-30')),'yyyy-mm-dd') into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
      else if "ttype" = 'N' then
          select first "value" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
        else if "ttype" = 'M' then
            select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "oval" from "tagchoice" key join "tagvalue" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "id" = "parentid";
            if "oval" is null then select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "oval" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoiceid" = '_'
            end if
          else select first "value" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "tagchoiceid" = "tchoiceid" and "id" = "parentid"
          end if
        end if end if end if end if;
  if "isnull"("mstep",0) = 0 then
    set "mstep" = 1
  end if;
  case "ttype"
  when null then
    return
  when 'M' then if "tchoiceid" = '_' or "tchoiceid" = '' or "tchoiceid" is null then
      set "nvalue" = 0;
      set "tchoiceid" = null
    else if "nvalue" <> 0 then
        set "nvalue" = 1
      end if end if when 'T' then set "tchoiceid" = null;
    if "trim"("tvalue") <> '' then
      set "svalue" = "tvalue";
      set "nvalue" = 1
    else
      set "nvalue" = 0
    end if when 'U' then set "tchoiceid" = null;
    if "trim"("tvalue") <> '' then
      set "svalue" = "ucase"("tvalue");
      set "nvalue" = 1
    else
      set "nvalue" = 0
    end if when 'N' then set "tchoiceid" = null;
    if "nvalue" = 1 then
      set "nvalue" = "mstep"
    end if when 'D' then set "tchoiceid" = null;
    begin
      set "nvalue" = "date"("trim"("tvalue"))-"date"('1899-12-30')
    exception
      when others then
        set "nvalue" = 0
    end when 'L' then if "nvalue" <> 0 and "tchoiceid" <> '*' then
      set "nvalue" = 1
    end if when 'S' then if "nvalue" = 1 then
      set "nvalue" = "mstep"
    end if when 'G' then if "nvalue" = 1 then
      select "min"("value") into "nvalue" from "tagchoice" where "taglocation" = "tlocation" and "tagid" = "tid" and "subchoice" = 1
    end if when 'Q' then if "nvalue" <> 1 then
      set "nvalue" = 1
    end if else
    return
  end case;
  if "tchoiceid" is not null then
    if exists(select "id" from "tagvalue" where "tagchoiceid" = "tchoiceid" and "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid") then
      if((("ttype" = 'L' and "tchoiceid" <> '*') or "ttype" = 'M') and "nvalue" <> 0) or "tvalue" = 'Y' then
        return
      end if;
      delete from "tagvalue" where "tagchoiceid" = "tchoiceid" and "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid"
    end if end if;
  if "tchoiceid" is null or "ttype" = 'M' then
    if exists(select "id" from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid") then
      delete from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid"
    end if end if;
  if "ttype" = 'Q' then
    delete from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid" and "tagchoiceid"
       = any(select "tagchoiceid" from "tagchoice" where "tagid" = "tid" and "taglocation" = "tlocation" and "tagchoiceparentid" = "tvalue");
    set "svalue" = null;
    if "isnull"("tchoiceid",'0') = '0' then
      set "nvalue" = 0
    else set "nvalue" = 1
    end if end if;
  if "nvalue" <> 0 then
    insert into "tagvalue"( "id","tagchoiceid","tagid","taglocation","value","textvalue" ) values
      ( "parentid","tchoiceid","tid","tlocation","nvalue","svalue" ) 
  end if;
  if "baudit" = 1 then
    if "ttype" = 'M' then
      select "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "tvalue" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoice"."tagchoiceid" = "tchoiceid";
      if("tvalue" is null) or("tvalue" = '1') then
        select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "tvalue" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoiceid" = '_'
      end if;
      if "tvalue" = '1' then
        set "tvalue" = null
      end if end if;
    if "ttype" = 'L' then
      if "tvalue" = '1' then
        set "tvalue" = 'Added';
        set "oval" = ''
      else
        set "tvalue" = 'Removed';
        set "oval" = ''
      end if end if;
    call "AuditLog"("stype","sid","sname","isnull"("oval",''),"isnull"("tvalue",''))
  end if
exception
  when others then return
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."SetQuestAnswer" IS 
{create procedure SetQuestAnswer 

/* Application Maintained Function / Procedure - DO NOT EDIT*/

( 
  in "tlocation" char(3),in "tid" char(3),in "tchoiceid" char(4),in "parentid" char(20),in "tvalue" long varchar ) 
begin
  declare "ttype" char(1);
  declare "nvalue" double;
  declare "mstep" double;
  declare "svalue" long varchar;
  declare "baudit" smallint;
  declare "sname" char(200);
  declare "sid" char(20);
  declare "stype" char(12);
  declare "oval" long varchar;
  set "ttype" = null;
  set "svalue" = null;
  if "tchoiceid" = '' then
    set "tchoiceid" = null
  end if;
  if "tvalue" = 'Y' then
    set "nvalue" = 1
  else
    begin
      set "nvalue" = cast("tvalue" as double)
    exception
      when others then
        set "nvalue" = 0
    end end if;
  select "tagtype","minstep","auditflag" into "ttype","mstep","baudit" from "tag" where "taglocation" = "tlocation" and "tagid" = "tid";
  if "tlocation" like 'V%' then
    update "vacancy" set "QuestionnaireUpdated" = current timestamp where "vacancyid" = "parentid"
  end if;
  if "baudit" = 1 then
    if "tlocation" like 'C%' then
      set "stype" = 'COMPANY';
      set "sid" = "parentid";
      select "name" into "sname" from "company" where "companyid" = "parentid"
    else
      if "tlocation" = 'P' or "tlocation" like 'A%' then
        set "stype" = 'PERSON';
        set "sid" = "parentid";
        select "name" into "sname" from "person" where "personid" = "parentid"
      else if "tlocation" = 'E' then
          set "stype" = 'COMPANY';
          select "company"."companyid","string"("person"."name",', ',"company"."name") into "sid","sname" from "employment" key join("person","company") where "employment"."employmentid" = "parentid"
        else if "tlocation" like 'V%' then
            set "stype" = 'VACANCY';
            set "sid" = "parentid";
            select "string"("Company"."Name",' - ',"vacancy"."position",'(',"vacancy"."RefCode",')') into "sname" from "vacancy" key join "employment" key join "Company" where "vacancy"."vacancyid" = "parentid"
          else if "tlocation" like 'L%' then
              set "stype" = 'PLACEMENT';
              set "sid" = "parentid";
              select "string"("Company"."Name",' - ',"employment"."position") into "sname" from "placement" key join "employment" key join "Company" where "placement"."placementid" = "parentid"
            else if "tlocation" like 'S%' then
                set "stype" = 'SHIFTPLAN';
                set "sid" = "parentid" --;
              else --select string(Company.Name,' - ',employment.position) into sname from placement key join employment key join Company where tempshift.tempshiftplanid = parentid
                if "tlocation" like 'T%' then
                  set "stype" = 'TIMESHEET';
                  set "sid" = "parentid";
                  select
                    "isnull"(
                    (select first if "c"."Name" is not null then
                        "string"("t"."SerialNumber",': ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name")
                      else "string"("t"."SerialNumber",': ',"p"."Name",' - Internal ')
                      endif from "TempTimeSheet" as "t" key join "Person" as "p","TempTimeSheet" as "t" key left outer join("Placement" key join "Vacancy" as "v" key join "Employment" key join "Company" as "c") where "t"."TempTimeSheetID" = "parentid"),
                    (select first "string"("t"."SerialNumber",'(P): ',"p"."Name",' as ',"v"."Position",' at ',"c"."Name") from "TempProvTimeSheet" as "t" key left outer join "Person" as "p","TempProvTimeSheet" as "t"
                        key left outer join("Vacancy" as "v" key join "Employment" key join "Company" as "c") where "t"."TempProvTimeSheetID" = "parentid"))
                    into "sname"
                else if "tlocation" like 'N%' then
                    set "stype" = 'CEVENT';
                    set "sid" = "parentid"
                  else if "tlocation" = 'U' then
                      set "stype" = 'USER';
                      set "sid" = "parentid"
                    else if "tlocation" like 'R%' then
                        set "stype" = 'REFER';
                        set "sid" = "parentid"
                      else if "tlocation" = 'I' then
                          set "stype" = 'PURCH';
                          set "sid" = "parentid"
                        else if "tlocation" = 'X' then
                            set "stype" = 'AGENCY';
                            set "sid" = "parentid"
                          else if "tlocation" like 'J%' then
                              set "stype" = 'COLLECT';
                              set "sid" = "parentid"
                            else
                              set "stype" = "string"('TAG ',"tlocation");
                              set "sid" = "parentid";
                              set "sname" = ''
                            end if
                          end if
                        end if
                      end if
                    end if
                  end if
                end if
              end if
            end if
          end if
        end if end if end if;
    if("length"("tchoiceid") > 0) and("ttype" <> 'M') then
      set "sname" = "string"((select "string"(tag.description,': ',"tagchoice"."description") from "tagchoice" key join "tag" 
        where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoice"."tagchoiceid" = "tchoiceid"),' Updated - ',"sname")
    else
      set "sname" = "string"((select "description" from "tag" where "taglocation" = "tlocation" and "tagid" = "tid"),' Updated - ',"sname")
    end if;
    if "tlocation" like 'U%' then
      set "sname" = "string"("sname",' - ',(select "userid" from "staff" where "staffid" = "parentid"))
    end if;
    if "length"("tlocation") > 1 then
      set "sname" = "string"((select "name" from "department" where "departmentid" = "substring"("tlocation",2)),' ',"sname")
    end if;
    if "ttype" in( 'T','U' ) then
      select first "textvalue" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
    else if "ttype" = 'D' then
        select first "dateformat"("dateadd"("day","value","date"('1899-12-30')),'yyyy-mm-dd') into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
      else if "ttype" = 'N' then
          select first "value" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "id" = "parentid"
        else if "ttype" = 'M' then
            select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "oval" from "tagchoice" key join "tagvalue" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "id" = "parentid";
            if "oval" is null then select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "oval" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoiceid" = '_'
            end if
          else select first "value" into "oval" from "tagvalue" where "taglocation" = "tlocation" and "tagid" = "tid" and "tagchoiceid" = "tchoiceid" and "id" = "parentid"
          end if
        end if
      end if end if end if;
  if "isnull"("mstep",0) = 0 then
    set "mstep" = 1
  end if;
  case "ttype"
  when null then
    return
  when 'M' then if "tchoiceid" = '_' or "tchoiceid" = '' or "tchoiceid" is null then
      set "nvalue" = 0;
      set "tchoiceid" = null
    else if "nvalue" <> 0 then
        set "nvalue" = 1
      end if end if when 'T' then set "tchoiceid" = null;
    if "trim"("tvalue") <> '' then
      set "svalue" = "tvalue";
      set "nvalue" = 1
    else
      set "nvalue" = 0
    end if when 'U' then set "tchoiceid" = null;
    if "trim"("tvalue") <> '' then
      set "svalue" = "ucase"("tvalue");
      set "nvalue" = 1
    else
      set "nvalue" = 0
    end if when 'N' then set "tchoiceid" = null;
    if "nvalue" = 1 then
      set "nvalue" = "mstep"
    end if when 'D' then set "tchoiceid" = null;
    begin
      set "nvalue" = "date"("trim"("tvalue"))-"date"('1899-12-30')
    exception
      when others then
        set "nvalue" = 0
    end when 'L' then if "nvalue" <> 0 and "tchoiceid" <> '*' then
      set "nvalue" = 1
    end if when 'S' then if "nvalue" = 1 then
      set "nvalue" = "mstep"
    end if when 'G' then if "nvalue" = 1 then
      select "min"("value") into "nvalue" from "tagchoice" where "taglocation" = "tlocation" and "tagid" = "tid" and "subchoice" = 1
    end if when 'Q' then if "nvalue" <> 1 then
      set "nvalue" = 1
    end if else
    return
  end case;
  if "tchoiceid" is not null then
    if exists(select "id" from "tagvalue" where "tagchoiceid" = "tchoiceid" and "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid") then
      if((("ttype" = 'L' and "tchoiceid" <> '*') or "ttype" = 'M') and "nvalue" <> 0) or "tvalue" = 'Y' then
        return
      end if;
      delete from "tagvalue" where "tagchoiceid" = "tchoiceid" and "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid"
    end if end if;
  if "tchoiceid" is null or "ttype" = 'M' then
    if exists(select "id" from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid") then
      delete from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid"
    end if end if;
  if "ttype" = 'Q' then
    delete from "tagvalue" where "tagid" = "tid" and "taglocation" = "tlocation" and "id" = "parentid" and "tagchoiceid"
       = any(select "tagchoiceid" from "tagchoice" where "tagid" = "tid" and "taglocation" = "tlocation" and "tagchoiceparentid" = "tvalue");
    set "svalue" = null;
    if "isnull"("tchoiceid",'0') = '0' then
      set "nvalue" = 0
    else set "nvalue" = 1
    end if end if;
  if "nvalue" <> 0 then
    insert into "tagvalue"( "id","tagchoiceid","tagid","taglocation","value","textvalue" ) values
      ( "parentid","tchoiceid","tid","tlocation","nvalue","svalue" ) 
  end if;
  if "baudit" = 1 then
    if "ttype" = 'M' then
      select "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "tvalue" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoice"."tagchoiceid" = "tchoiceid";
      if("tvalue" is null) or("tvalue" = '1') then
        select first "string"("tagchoice"."tagchoiceid",': ',"tagchoice"."description") into "tvalue" from "tagchoice" where "tagchoice"."taglocation" = "tlocation" and "tagchoice"."tagid" = "tid" and "tagchoiceid" = '_'
      end if;
      if "tvalue" = '1' then
        set "tvalue" = null
      end if end if;
    if "ttype" = 'L' then
      if "tvalue" = '1' then
        set "tvalue" = 'Added';
        set "oval" = ''
      else
        set "tvalue" = 'Removed';
        set "oval" = ''
      end if end if;
    call "AuditLog"("stype","sid","sname","isnull"("oval",''),"isnull"("tvalue",''))
  end if
exception
  when others then return
end
}
</code>