====== pears.pay_usercheckemployment ======
<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"."pay_usercheckemployment"( 
  in "pid" char(20),in "pstartdate" date ) 
begin
  declare "pempid" char(20);
  declare "pempstartdate" date;
  declare "oldtax" integer;
  declare "oldregno" char(20);
  declare "newtax" integer;
  declare "newregno" char(20);
  select first "pay_employmentid","startdate","companyregno","taxmethod" into "pempid","pempstartdate","oldregno","oldtax"
    from "pay_employment" where "personid" = "pid" and "enddate" is null order by "startdate" desc;
  update "pay_employment" set "enddate" = "pstartdate" where "personid" = "pid" and "pay_employmentid" <> "pempid" and "enddate" is null;
  if "pempid" is null then
    insert into "pay_employment"( "pay_employmentid","personid","startdate",
      "CompanyName","CompanyRegNo","HMRCEngagementDetail","PayrollAddr1","PayrollAddr2","PayrollAddr3","PayrollAddr4",
      "PayrollFullAddr","payrollnumber","PayrollPostcode","TaxMethod","UTR","VATNumber",
      "VATRegistered" ) select "uniquekey"("pid"),"pid","pstartdate",
        "e"."CompanyName","e"."RegNo","e"."HMRCEngagementDetail","e"."PayrollAddr1","e"."PayrollAddr2","e"."PayrollAddr3","e"."PayrollAddr4",
        "p"."extranotes","p"."payrollnumber","e"."PayrollPostcode","e"."TaxMethod","e"."UniqueTaxReference","e"."VATNumber","e"."VATRegistered"
        from "person" as "p" left outer join "pay_employee" as "e" where "p"."personid" = "pid";
    update "pay_employee" set "p45requested" = 0 where "personid" = "pid"
  else
    select "regno","taxmethod" into "newregno","newtax" from "pay_employee" where "personid" = "pid";
    if "isnull"("oldtax",'') = '' then
      update "pay_employment" set "payrollnumber" = "p"."payrollnumber","taxmethod" = "e"."taxmethod","PayrollFullAddr" = "p"."extranotes",
        "CompanyName" = "e"."CompanyName","CompanyRegNo" = "e"."RegNo","HMRCEngagementDetail" = "e"."HMRCEngagementDetail",
        "PayrollAddr1" = "e"."PayrollAddr1","PayrollAddr2" = "e"."PayrollAddr2","PayrollAddr3" = "e"."PayrollAddr3","PayrollAddr4" = "e"."PayrollAddr4",
        "PayrollPostcode" = "e"."PayrollPostcode","UTR" = "e"."UniqueTaxReference","VATNumber" = "e"."VATNumber","VATRegistered" = "e"."VATRegistered" from
        "pay_employee" as "e" key join "person" as "p" key join "pay_employment" where "pay_employmentid" = "pempid"
    else
      if(("isnull"("oldtax",'') <> "isnull"("newtax",''))
        or(("isnull"("oldregno",'') <> "isnull"("newregno",'')) and "isnull"("newtax",'') in( 2,3 ) )) then
        update "pay_employment" set "enddate" = "dateadd"("day",-1,"pstartdate") where "pay_employmentid" = "pempid";
        insert into "pay_employment"( "pay_employmentid","personid","startdate",
          "CompanyName","CompanyRegNo","HMRCEngagementDetail","PayrollAddr1","PayrollAddr2","PayrollAddr3","PayrollAddr4",
          "PayrollFullAddr","payrollnumber","PayrollPostcode","TaxMethod","UTR","VATNumber",
          "VATRegistered" ) select "uniquekey"("pid"),"pid","pstartdate",
            "e"."CompanyName","e"."RegNo","e"."HMRCEngagementDetail","e"."PayrollAddr1","e"."PayrollAddr2","e"."PayrollAddr3","e"."PayrollAddr4",
            "p"."extranotes","p"."payrollnumber","e"."PayrollPostcode","e"."TaxMethod","e"."UniqueTaxReference","e"."VATNumber","e"."VATRegistered"
            from "person" as "p" left outer join "pay_employee" as "e" where "p"."personid" = "pid"
      else
        if "pempstartdate" > "pstartdate" then
          update "pay_employment" set "startdate" = "pstartdate" where "pay_employmentid" = "pempid"
        end if
      end if
    end if
  end if
end
</code>