moj noms apvs 0676 gbs direct payments

#0676 - GBS Direct Payments

Author(s) - Tom Swann

Introduction

Technical Spike to understand the Gov Banking Services (GBS) integration required to make direct bank payments to successful claimants.

Requirements

The APVS service has a requirement to pay out to claimants where their claim has been successfully approved.

This includes an option to pay individuals who have a bank account via direct bank transfer.

The service is mandated to use the GBS direct payments service operated by SSCL (Shared Services Connected Limited) for this method of payment and this technical spike describes our understanding of the requirements and the design to implement the solution.

Claimant Requirements

Claimants who wish to have their claims paid directly to their bank account will be able to select this payment method on the external web application during the claim screen flow for both first time and repeat claims.

The screen will capture the following required information:

• Bank Account number
• Sort Code

This information will be used to populate the data file which is generated by the APVS solution and transfered to GBS.

Note that validation at the point of entry will be covered by separate stories and associated spikes, this may include Bank Account Validation #321

When a claimant has their claim approved, they will expect be notified that this has taken place and the payment will be made into their bank account.

A claimant should expect the payment into the account to be identified on their account statement using a reference generated by the system.

SSCL Requirements

SSCL operate the direct bank payments service and will be responsible for retrieving the files generated by the APVS service.

Data files

SSCL require two data files to be generated as described below.

AccessPay CSV file

This file is a comma-separated file containing records for each of the Payments to be generated.

The following row format, however note that the file itself does not include this information in the form of a header row:

• sort code
• account number
• name (full name of the claimant)
• amount
• reference (APVS payment reference code to appear on customer statement, should include ClaimId for traceability)

An example of the file contents:

010203,12345678,Tom Swann,123.99,99887766

Overall file size for this file is expected to be single digit KBs uncompressed.

ADI Journal Excel file

The second file will be an Excel file used by SSCL to upload totals from the AccessPay transactions into their finance system.

This file is in the form of a pre-existing template which the APVS system will populate a single entry calculated from the corresponding AccessPay file from that day:

1. Overall total of transactions included in the AccessPay file

The template also includes macros which are used by SSCL and the finance system.

TBC: Awaiting template from SSCL

An existing implementation which populates the ADI Journal for Money To Prisoners currently exists and can be found here:

MtP ADI Python implementation

Note that this implementations contains code which writes to an existing version of the ADI template which will be replaced during the timescales for this project. Therefore this will ultimately need to be re-written for both MtP and APVS use.

File size of the existing template is approx 200 KB populated and uncompressed.

Bank Admin UI

SSCL require an Bank Admin page to be provided as part of the internal web application from which they can download the data files for the previous 24 hour period.

Once downloaded the SSCL user will be able to use these files to upload them to the AccessPay service (used to pay out to customers) and their internal finance system.

Any errors encountered during the upstream process by SSCL will be fed back to APVU outside of this integration - this will be covered by an additional user story for the internal web application detailing how pending claims with unsuccessful payments will be updated and the claimants notified.

This Bank Admin UI concept is based upon the existing mechanism used to deliver this data to SSCL on the Money To Prisoners service.

See MtP Bank Admin on GitHub

Note, that this service generates additional data beyond the AccessPay and ADI Journal files as they also must capture information about payments in as well as payments out.

Design

The diagram above illustrates the overall sequence of events for the direct payments solution, from the capture and approval of claim information to the generation of the payment files for the outbound interface with GBS.

File Generation Process

The solution will use a scheduled async worker task to generate the required payment files for the preceding run period.

Individual runs will be scheduled as follows and a 'run period' will be defined as containing all payment transactions since the previous successful scheduled run.

1. Mon, Tue, Wed, Thu at 12AM UTC
2. Sun at 12AM UTC

A single run period should produce the pair of output files for that set of payment records:

1. The AccessPay CSV file containing the list of payment transactions
2. The ADI Journal containing the totals for the set of associated transactions

The criteria that payment records will meet to be included in the file contents for a given run period will be as follows:

1. Claim Status is Approved
2. Claim has associated Bank Details (payment method is direct bank payment)
3. Claim Payment Status is Pending

Once the files have been successfully generated from the input payment records they will be output to Azure File Storage.

If this operation succeeds then the metadata relating to those files will be written to the APVS database.

Reconciling failed payments

SSCL will email a file containing payments which failed to APVU (due to incorrect account details etc.). The file will contain the line items from the AccessPay file which failed, including the reference number/claimId. Using the reference number a caseworker will identify the associated claims and re-open the approved claim using the "Request Information" flow to notify the claimant of the issue and try to correct.

ADI Journal Generation

We will use a Python script for the code required to generate the ADI Journal file. There are several reasons for this choice:

• The implementation is proven in Python on the existing Money To Prisoners project
• The most mature Node library for generating Excel content does not support writes of XLSM (macro enabled workbooks)
• Python aligns with existing MoJ skills (both Digi Studio and MtP)

openpyxl is the library for Excel workbook read, manipulation and writing in Python.

The script will be called by the Async worker task after the CSV file of payment records has been generated.

The file requires only one parameter - the monetary total which is the sum of the payments being processed in the accompanying AccessPay file and a single line with this total should be written to the ADI journal.

The APVS technical prototype includes an example implementation of calling a simple python script from the Node async worker, reading the Excel template and updating a value.

Python coding guidelines have been added to the Code Quality page.

Links

• Money To Prisoners Bank Admin Repository