====== pears.NetOwnerCandidateSetBank ======
<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"."NetOwnerCandidateSetBank"( in "pWebUserID" char(20),in "ppersonid" char(20),in "pni" char(10) default 'NOT-SET',in "pBankName" char(30) default 'NOT-SET',in "pBankSortCode" char(8) default 'NOT-SET',in "pBankAcName" char(50) default 'NOT-SET',in "pBankAcNo" char(8) default 'NOT-SET',in "pUniqueTaxReference" char(20) default 'NOT-SET',in "poldni" char(10) default 'NOT-SET',in "poldBankName" char(30) default 'NOT-SET',in "poldBankSortCode" char(8) default 'NOT-SET',in "poldBankAcName" char(50) default 'NOT-SET',in "poldBankAcNo" char(8) default 'NOT-SET',in "poldUniqueTaxReference" char(20) default 'NOT-SET' ) 
result( "pResult" char(250) ) 
begin
  declare "ssql" char(255); -- sql string
  declare "errmsg" char(150);
  declare "DivID" char(20);
  declare "CurrentStates" char(30);
  set "ssql" = '';
  -- check that the record is owned by the logged in user
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  set "CurrentStates" = "string"('[',(select first "PersonCurrentStates" from "params"),']');
  if not "ppersonid" = any(select "person"."personid" from "person" where "person"."divisionid" = "DivID" and "person"."status" like "CurrentStates") then
    select '99:~Permission denied';
    return
  end if;
  if "left"("pBankAcNo",1) = '*' then
    select '2:~Check your bank details are correct';
    return
  end if;
  if "isnull"("pni",'') <> "isnull"("poldni",'') then -- National insurance number goes into person table. others go into pay_Employee table
    set "pni" = "ucase"("pni");
    if "trim"("isnull"("pni",'')) <> '' then
      set "errmsg" = "validateninumber"("pni");
      if "trim"("isnull"("errmsg",'')) <> '' then
        select '121:~'+"errmsg";
        return
      end if end if;
    set "ssql" = "ssql"+'ni=pni'
  end if;
  if "ssql" <> '' then
    execute immediate 'update person set '+"ssql"+' where personid=ppersonid'
  end if;
  -- check if record already present in Pay_Employee. If no, insert stub record first
  if not exists(select * from "Pay_Employee" where "Personid" = "ppersonid") then
    -- insert stub
    insert into "pay_employee"( "personid" ) values( "ppersonid" ) 
  end if;
  set "ssql" = ''; -- clear for second update to Pay_Employee table
  -- update
  if "isnull"("pBankName",'') <> "isnull"("poldBankName",'') then
    set "ssql" = "ssql"+',BankName=pBankName'
  end if;
  if "isnull"("pBankSortCode",'') <> "isnull"("poldBankSortCode",'') then
    set "ssql" = "ssql"+',BankSortCode=pBankSortCode'
  end if;
  if "isnull"("pBankAcName",'') <> "isnull"("poldBankAcName",'') then
    set "ssql" = "ssql"+',BankAcName=pBankAcName'
  end if;
  if "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') then
    set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
  end if;
  if "isnull"("pUniqueTaxReference",'') <> "isnull"("poldUniqueTaxReference",'') then
    set "ssql" = "ssql"+',UniqueTaxReference=pUniqueTaxReference'
  end if;
  if "ssql" <> '' then
    execute immediate 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=ppersonid'
  end if;
  call "personrecordupdated"("ppersonid");
  select '0:~Success'
end /* DOC
2018-05-07 PC IW-503 hide bank account number except for last two digits
2018-08-27 PC improve error handling

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerCandidateSetBank" IS 
{create PROCEDURE pears."NetOwnerCandidateSetBank"( in "pWebUserID" char(20),in "ppersonid" char(20),in "pni" char(10) default 'NOT-SET',in "pBankName" char(30) default 'NOT-SET',in "pBankSortCode" char(8) default 'NOT-SET',in "pBankAcName" char(50) default 'NOT-SET',in "pBankAcNo" char(8) default 'NOT-SET',in "pUniqueTaxReference" char(20) default 'NOT-SET',in "poldni" char(10) default 'NOT-SET',in "poldBankName" char(30) default 'NOT-SET',in "poldBankSortCode" char(8) default 'NOT-SET',in "poldBankAcName" char(50) default 'NOT-SET',in "poldBankAcNo" char(8) default 'NOT-SET',in "poldUniqueTaxReference" char(20) default 'NOT-SET' ) 
result( "pResult" char(250) ) 
begin
  declare "ssql" char(255); -- sql string
  declare "errmsg" char(150);
  declare "DivID" char(20);
  declare "CurrentStates" char(30);
  set "ssql" = '';
  -- check that the record is owned by the logged in user
  set "DivID" = (select first "staff"."divisionid" from "iqxnetuser" key join "staff" where "iqxnetuser"."iqxnetuserid" = "pWebUserID");
  set "CurrentStates" = "string"('[',(select first "PersonCurrentStates" from "params"),']');
  if not "ppersonid" = any(select "person"."personid" from "person" where "person"."divisionid" = "DivID" and "person"."status" like "CurrentStates") then
    select '99:~Permission denied';
    return
  end if;
  if left(pBankAcNo,1)='*' then
    select '2:~Check your bank details are correct';
    return; 
  end if;
  if "isnull"("pni",'') <> "isnull"("poldni",'') then -- National insurance number goes into person table. others go into pay_Employee table
    set "pni" = "ucase"("pni");
    if "trim"("isnull"("pni",'')) <> '' then
      set "errmsg" = "validateninumber"("pni");
      if "trim"("isnull"("errmsg",'')) <> '' then
        select '121:~'+"errmsg";
        return
      end if end if;
    set "ssql" = "ssql"+'ni=pni'
  end if;
  if "ssql" <> '' then
    execute immediate 'update person set '+"ssql"+' where personid=ppersonid'
  end if;
  -- check if record already present in Pay_Employee. If no, insert stub record first
  if not exists(select * from "Pay_Employee" where "Personid" = "ppersonid") then
    -- insert stub
    insert into "pay_employee"( "personid" ) values( "ppersonid" ) 
  end if;
  set "ssql" = ''; -- clear for second update to Pay_Employee table
  -- update
  if "isnull"("pBankName",'') <> "isnull"("poldBankName",'') then
    set "ssql" = "ssql"+',BankName=pBankName'
  end if;
  if "isnull"("pBankSortCode",'') <> "isnull"("poldBankSortCode",'') then
    set "ssql" = "ssql"+',BankSortCode=pBankSortCode'
  end if;
  if "isnull"("pBankAcName",'') <> "isnull"("poldBankAcName",'') then
    set "ssql" = "ssql"+',BankAcName=pBankAcName'
  end if;
  if "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') then
    set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
  end if;
  if "isnull"("pUniqueTaxReference",'') <> "isnull"("poldUniqueTaxReference",'') then
    set "ssql" = "ssql"+',UniqueTaxReference=pUniqueTaxReference'
  end if;
  if "ssql" <> '' then
    execute immediate 'update Pay_Employee set '+"stuff"("ssql",1,1,'')+' where personid=ppersonid'
  end if;
  call "personrecordupdated"("ppersonid");
  select '0:~Success'
end

/* DOC
2018-05-07 PC IW-503 hide bank account number except for last two digits
2018-08-27 PC improve error handling

*/
}
</code>