pears.NetCandidateProfileSetBank

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original 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
*/
}