pears.LSMLuserValue [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.LSMLuserValue ======
<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"."LSMLuserValue"( in "stype" char(1),in "sid" char(20) ) 
returns char(20)
begin
  declare "rv" char(20);
  declare "branch" char(20);
  declare "depart" char(20);
  declare "divis" char(20);
  declare "consult" char(20);
  declare "consultid" char(20);
  declare "desk" char(20);
  declare "lv" long varchar;
  declare "vatonmargin" char(1);
  declare "suspendcheck" char(1);
  declare "c" char(1);
  declare "i" integer;
  declare "x" char(20);
  declare "PayslipDist" char(1);
  declare "InvoiceDist" char(1);
  -- stype= company-C vacancy-V person-P Payband-R Timesheet-T TimesheetLine-L ProvTimesheet-E ProvTimesheetLine-F Expenses-X
  select first "lsmagencyid" into "rv" from "params";
  case
  when "stype" = 'C' then
    select "invoicefrequency","groupinvoice" into "c","i" from "companyaccount" where "companyid" = "sid";
    if "isnull"("i",0) = 2 then
      set "rv" = '1' -- Group by their ref
    else
      set "rv" = '0' -- Group by client or not at all - the sepinv field will be set appropriately
    end if;
    if "isnull"("c",' ') in( '4','M' ) then
      set "rv" = "rv"+'01'
    else
      if "isnull"("c",' ') = 'W' then
        set "rv" = "rv"+'02'
      else
        set "rv" = "rv"+'00'
      end if end if;
    -- Get Payslip Distribution Behaviour
    select "max"("tagchoiceid") into "PayslipDist" from "tagvalue" where "taglocation" = 'C' and "tagid" = 'INV' and "id" = "sid";
    if "isnull"("PayslipDist",'') = '' then
      set "PayslipDist" = '0'
    end if;
    set "rv" = "string"("rv","PayslipDist");
    return("padstr"("rv",20,'_'))
  when "stype" = 'T' or "stype" = 'E' then
    if "stype" = 'T' then
      select "d"."departmentid","d"."defanalysiscode","isnull"("placement"."staffid","person"."staffid"),
        "placementelementvalue"("t"."placementid",'VATONMARGIN'),
        (if exists(select * from "temptimesheetline" as "L" key join "temppayband" as "B" where "L"."temptimesheetid" = "t"."temptimesheetid" and "B"."payrollflag" like '_1%') then '1'
        else '0'
        endif) into "depart","desk","consultid","lv","suspendcheck" from "temptimesheet" as "t" key left outer join "tempdesk" as "d"
          ,"temptimesheet" as "t" key left outer join "placement"
          ,"temptimesheet" as "t" key join "person"
        where "t"."temptimesheetid" = "sid"
    else
      select "d"."departmentid","d"."defanalysiscode","isnull"("placement"."staffid","isnull"("vacancy"."staffid","person"."staffid")),
        "placementelementvalue"("t"."placementid",'VATONMARGIN'),
        (if exists(select * from "tempprovtimesheetline" as "L" key join "temppayband" as "B" where "L"."tempprovtimesheetid" = "t"."tempprovtimesheetid" and "B"."payrollflag" like '_1%') then '1'
        else '0'
        endif) into "depart","desk","consultid","lv","suspendcheck" from "tempprovtimesheet" as "t" key left outer join "tempdesk" as "d"
          ,"tempprovtimesheet" as "t" key left outer join "vacancy"
          ,"tempprovtimesheet" as "t" key left outer join "placement"
          ,"tempprovtimesheet" as "t" key join "person"
        where "t"."tempprovtimesheetid" = "sid"
    end if;
    select(select "analysis" from "agencydetails" where "agencyid" = "c"."agencyid"),
      (select "analysis" from "division" where "divisionid" = "c"."divisionid"),
      "c"."analysiscode" into "branch","divis","consult" from "staff" as "c" where "c"."staffid" = "consultid";
    set "vatonmargin" = "isnull"("left"("lv",1),'');
    if "vatonmargin" = 'Y' then
      set "vatonmargin" = '1'
    end if;
    if "vatonmargin" <> '1' then
      set "vatonmargin" = '0'
    end if;
    set "suspendcheck" = "isnull"("suspendcheck",'0');
    set "rv" = "padstr"("divis",2,'_')+"padstr"("branch",3,'_')+"padstr"("depart",2,'_')+"padstr"("desk",3,'_')+"padstr"("consult",5,'_')+"vatonmargin"+"suspendcheck";
    return("padstr"("rv",20,'_'))
  when "stype" = 'L' or "stype" = 'F' then
    if "stype" = 'L' then
      select "b"."payrollflag" into "x" from "temptimesheetline" as "l" key join "temppayband" as "b" where "l"."temptimesheetlineid" = "sid"
    else
      select "b"."payrollflag" into "x" from "tempprovtimesheetline" as "l" key join "temppayband" as "b" where "l"."tempprovtimesheetlineid" = "sid"
    end if;
    return("padstr"("x",20,'0'))
  when "stype" = 'P' then
    set "x" = null;
    select "max"("tagchoiceid") into "x" from "tagvalue" where "taglocation" = 'P' and "tagid" = 'INS' and "id" = "sid";
    if "isnull"("x",'') = 'Y' then
      set "x" = '1'
    else
      set "x" = '0'
    end if;
    select(select "analysis" from "agencydetails" where "agencyid" = "c"."agencyid"),
      (select "analysis" from "division" where "divisionid" = "person"."divisionid"),
      "c"."analysiscode" into "branch","divis","consult" from "staff" as "c" key join "person" where "person"."personid" = "sid";
    set "branch" = "padstr"("branch",3,'_');
    set "divis" = "padstr"("divis",2,'_');
    set "consult" = "padstr"("consult",5,'_');
    -- Get Invoice Distribution Behaviour
    select "max"("tagchoiceid") into "InvoiceDist" from "tagvalue" where "taglocation" = 'P' and "tagid" = 'INV' and "id" = "sid";
    if "isnull"("InvoiceDist",'') = '' then
      set "InvoiceDist" = '0'
    end if;
    return("padstr"("string"("x","divis","branch","consult","InvoiceDist"),20,'_'))
  end case;
  return("rv")
end
</code>