pears.NetCandidateProfileBank

CREATE PROCEDURE "pears"."NetCandidateProfileBank"( IN "pWebUserID" CHAR(20),IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "PersonID" CHAR(20),"ni" CHAR(10),"BankName" CHAR(30),"BankSortCode" CHAR(8),"BankAcName" CHAR(50),"BankAcNo" CHAR(8),"UniqueTaxReference" CHAR(20) ) 
BEGIN
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  SELECT "person"."personid",
    "person"."ni",
    "Pay_Employee"."BankName",
    "Pay_Employee"."BankSortCode",
    "Pay_Employee"."BankAcName",
    "Pay_Employee"."BankAcNo",
    "string"("replicate"('*',"length"("Pay_Employee"."BankAcNo")-2),"right"("Pay_Employee"."BankAcNo",2)),
    "Pay_Employee"."UniqueTaxReference"
    FROM "person"
      KEY LEFT OUTER JOIN "Pay_Employee"
    WHERE "person"."personid" = "pPersonID"
END /* DOC
2018-05-07 PC IW-503 replace bank account details with asterisks
2018-10-22 MHS IW-456 multi-cand fix
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateProfileBank" IS 
{CREATE PROCEDURE pears."NetCandidateProfileBank"( IN "pWebUserID" CHAR(20), IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( "PersonID" CHAR(20),"ni" CHAR(10),"BankName" CHAR(30),"BankSortCode" CHAR(8),"BankAcName" CHAR(50),"BankAcNo" CHAR(8),"UniqueTaxReference" CHAR(20) ) 
BEGIN
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    SELECT 
        "person"."personid",
        "person"."ni",
        "Pay_Employee"."BankName",
        "Pay_Employee"."BankSortCode",
        "Pay_Employee"."BankAcName",
        "Pay_Employee"."BankAcNo",
        string(replicate('*',LENGTH("Pay_Employee"."BankAcNo")-2),RIGHT("Pay_Employee"."BankAcNo",2)),
        "Pay_Employee"."UniqueTaxReference"
    FROM 
        "person" 
            KEY LEFT OUTER JOIN "Pay_Employee"
    WHERE 
        person.personid=pPersonID
END
 
/* DOC
2018-05-07 PC IW-503 replace bank account details with asterisks
2018-10-22 MHS IW-456 multi-cand fix
*/
}