====== pears.NetCandidateProfileSetBank ======
<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"."NetCandidateProfileSetBank"( 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);
  set "ssql" = '';
  -- check that the record is owned by the logged in user
  if not "ppersonid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid") then
    select '99:~Permission denied';
    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");
    set "errmsg" = "validateninumber"("pni");
    if "trim"("isnull"("errmsg",'')) <> '' then
      select '121:~'+"errmsg";
      return
    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 "left"("pBankAcNo",1) <> '*' then
    if "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') then
      set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
    end if end if;
  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"("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-07-24 PC include improved error handler
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateProfileSetBank" IS 
{create PROCEDURE pears."NetCandidateProfileSetBank"( 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);
  set "ssql" = '';
  -- check that the record is owned by the logged in user
  if not "ppersonid" = any(select "personid" from "iqxnetuserlink" where "iqxnetuserid" = "pwebuserid") then
    select '99:~Permission denied';
    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");
    set "errmsg" = "validateninumber"("pni");
    if "trim"("isnull"("errmsg",'')) <> '' then
      select '121:~'+"errmsg";
      return
    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 left(pBankAcNo,1)!='*' then
    if "isnull"("pBankAcNo",'') <> "isnull"("poldBankAcNo",'') then
      set "ssql" = "ssql"+',BankAcNo=pBankAcNo'
    end if;
  end if;
  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"("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-07-24 PC include improved error handler
*/
}
</code>