Final Payers SSIS

Pre-PTX, the Final Payments file (or 19 report) output from DMS is automatically picked up and loaded into Webseries via its 'DDMO jobs'.
 
With Webseries being replaced by PTX, this DDMO job had to be replaced by a new job that similarly monitors the destination folder for the Final Payment file(s) and load them into PTX via the API.

The solution will, on output of a Final Payment file from DMS, create a final payment transaction in PTX for the plans involved and expire the plans.

DMS user generates Final Payers file in CSV format, which is generated in folder \\VMWBSPRO1APP01\Webseries Live\StepChange\Final Payments\Import\

This is done on the day before the 1st DD claim is submitted.

SQL Agent job APP – Create Final Payments runs each day from the 23rd of the month on a fifteen-minute schedule between 0800 and 1800.

There are three new entries in the DMS database [system_codes] table to support this job.

Job checks the item in the DMS database [system_codes] table, type = CREATE_FINAL_PAYMENTS, code = CURRENT_FILE to see if there is a file listed

Assuming a new file...

File is checked to being with the string ‘Finalpayers’ and be of type ‘csv’.

 If not, paymentqueries@stepchange.org will receive an email stating file validation has failed, and the file is moved to \\VMWBSPRO1APP01\Webseries Live\StepChange\Final Payments\Import\Archive\

If the file name and type was valid, the file is moved to, \\VMWBSPRO1APP01\Webseries Live\StepChange\Final Payments\Import\Processing\. The name of the file is inserted into the [system_codes] table.

The reference, payment date and amount for each item in the file are copied to DMS database table [UK_SSIS_FinalPayments_Data].

At this point, the columns is_valid, file_item_checked, mandate_checked, transaction_requested and expiry_requested will be 0, and the exception_warning will be NULL.

These may change as each item is validated and the final payments attempted to be created in PTX, as the job progresses.

The file name and date of processing will be recorded against each entry.

Individual file items validated against following conditions:
 
- Client reference must be numeric
 - Amount must be numeric and positive
 - Date in valid format (DD/MM/YYYY)

Entries in the data table failing any of these checks are updated with a description in the exception_warning column, as an error record to output to the warning/completion email.

The is_valid column will be updated with a 1 or 0 depending on validity. The file_item_checked column will be set to 1.

Mandate details for clients from valid file items are requested from the broker using the RequestMandateDetails endpoint.

Warnings

- The DD amendment spans the month the final payment occurs in (i.e. next month, from time of processing)
 - The mandate start date is in the final month (i.e. next month, from time of processing)
 
Entries in the data table failing any of these checks are updated with a description in the exception_warning column, as an warning record to output to the warning/completion email.

The job checks mandates in batches of 200 – it was found the live PTX instance can struggle returning mandate details for volumes larger than this, and the job is prone to time out.

The job will proceed until each item in the data table has a mandate_checked value of 1.

A final transaction is created for clients who have passed all the above validation by sending the data to the broker’s CreateTransactions endpoint

Similarly to the mandate checks, the job submits transactions in batches of 200. The job will proceed until each item in the data table has a transaction_requested value of 1.

The plans of the client records passing all validations (including ones with warning records) are expired using the broker’s BatchUpdatePaymentPlanStatus endpoint

The warning/completion email is sent to paymentqueries@stepchange.org and includes the following information:
 
- Total number of records on input file
 - Total number of final payments loaded into PTX
 - Total number of errors found
 - Total number of warnings found
 - Location of archive folder

 

Job checks the item in the DMS database [system_codes] table, type = CREATE_FINAL_PAYMENTS, code = CURRENT_FILE to see if there is a file listed

Assuming file name is populated -
Possible failure

FILE_MOVED
->
FILE_ITEMS_INSERTED
->
FILE_ITEMS_VALIDATED
->
MANDATES_VALIDATED
->
TRANSACTIONS_CREATED