upload_mt940

MT940 is a structured account statement. The sponsor bank will send this statement (for the benefit program funding account) everyday.

The exact mechanics of how this statement will be delivered to the government department will vary across implementations, viz. FTP, SMTP, API invoked by the bank etc.

Depending on the physical delivery mechanism, the implementation can create an integration layer and use this API to upload the MT940 message.

API Attributes
Direction	Inward
Invoked by	Sponsor bank, Implementation Integration work
Mode	Synchronous
Tables	benefit_program_account_statement account_statement_lob

Object design

account_statement

Attribute	Description
statement_id	Unique ID
statement_date	Tag :60F: of MT940 - Header Section
account_number	Tag :25: of MT940 - Header Section
reference_number	Tag :20: of MT940 - Header Section
statement_number	Tag :28C: of MT940 - Header Section
sequence_number	Tag :28C: of MT940 - Header Section
statement_upload_timestamp
statement_process_status	Enum PENDING PROCESSED
statement_process_timestamp
statement_process_error_code
statement_process_attempts

account_statement_lob

Attribute	Description
statement_id
statement_lob	TEXT type Stores the MT940 Statement as TEXT

Business logic

1. Persist the Account Statement in the two tables - account_statement & account_statement_lob

2. In the table - account_statement, only the following columns are populated - statement_id, statement_upload_timestamp and statment_process_status = PENDING

3. In the table - account_statement_lob, the entire text is persisted with the statement_id

mt940_processor_beat_producer

frequency	hourly (specified by configuration yml)
attempts	yes. subject to a configurable limit specified by configuration yml
driving table	account_statement
eligible envelopes	statement_process_status = 'PENDING'

1. Picks up all eligible account_statement_records

2. For each account statement, delegates a task to mt940_processor_worker

3. Payload -- statement_id

mt940 - statement format

MT940 - Account Statement - Detailed - Structure

mt940_processor_worker

1. Payload -- statement_id

2. Picks up the record from account_statement

3. Picks up the lob from account_statement_lob

4. Parse the mt940 - header and trailer and retrieve the following

   1. sponsor bank account number - Tag :25: of MT940 - Header Section

      E.g. - :25:032000136465

      Validate the Bank Account - against "benefit_program_configuration". This account number should exist If the account number does not exist, mark the MT940 Statement as "ERROR" - No further processing

   2. reference_number - Tag :20: of MT940 - Header Section

      E.g. - :20:CSCT032000136465

   3. statement_number - Tag :28C: of MT940 - Header Section

      E.g. - :28C:00001/001 (section before slash "/" is statement number)

   4. sequence_number - Tag :28C: of MT940 - Header Section

      E.g. - :28C:00001/001 (section after slash "/" is sequence number)

5. Update these attributes in the table - account_statement

6. Now loop through the transaction section of the MT940

7. Each Transaction consists of two lines (tags) - :61: & :86: (Statement and Narrative)

8. :61: is known as the Statement line and has the following structure

   :61:1507020702D115945,00F014NARRATIVE//0207150143062089CRLF1234567890

   1. 150702 -- 6 digits -- Transaction Value Date in YYMMDD format

   2. 0702 -- 4 digits -- Transaction Booking Date in MMDD format

   3. C/D/RC/RD -- 2 digits -- Credit, Debit, Reversal-Credit, Reversal-Debit

   4. 115945,00 -- Transaction Amount -- Maximum 19 characters

   5. F014 -- Transaction Code -- Should be a standard transaction code - 1 for Credit and another 1 for Debit

   6. NARRATIVE -- Transaction Narrative -- Should be the Beneficiary Name -- This should be as sent by the g2p-bridge to the Sponsor Bank

   7. // Reference Separator

   8. 0207150143062089CRLF1234567890 -- Transaction Reference Number issued by the Bank for this transaction

9. :86: is known as the Narrative Line - It can have 6 lines of 65 characters each. g2p-bridge should send as many details about the Benefit Program and Beneficiary in the Disbursement payload to ensure that the narrative text is as rich as possible

10. For each MT940 Transaction - retrieve the "disbursement_id" from the transaction

11. The "disbursement_id" should ideally be the Customer Reference Number. However, this depends on the Sponsor Bank banking platform

12. The Sponsor Bank may send the "disbursement_id" in any of these fields - customer_reference, bank_reference (unlikely, but possible) and one of the six narrative lines (tag 86)

13. So extracting the "disbursement_id" from the mt940 transaction - should be abstracted out to an interface - with an adapter implementation for different sponsor banks.

14. For "D" transactions, insert a new record for the "disbursement_id" in the table - disbursement_recon_from_bank

15. For "RD" transactions, updated the existing record in - disbursement_recon_from_bank

16. Log errors into disbursement_error_recons. The following error conditions are logged into this table

    1. I get a "D" transaction, but I don't get a valid "disbursement_id" - INVALID_DISBURSEMENT

    2. I get a "D" transaction, but the "disbursement_id" is already present in the table - disbursement_recon_from_bank - DUPLICATE_DISBURSEMENT

    3. I get a "RD" transaction, but the "disbursement_id" is not present in the table - disbursement_recon_from_bank - INVALID_REVERSAL

disbursement_recon

Attribute	Description
bank_disbursement_batch_id
disbursement_id	Unique Index
disbursement_envelope_id	Indexed
recon_statement_id	This is the Unique ID that is given to each MT940 that is uploaded into the platform
recon_statement_number	This is the Statement Number that is found in the MT940 header - field 28C
recon_statement_sequence
recon_entry_sequence	This is the sequence number of the entry in this statement - the entry that corresponds to this disbursement. This entry will be reflected as a "Debit" in the Program Account with the Sponsor Bank.
bank_reference_number	Bank's unique reference number for the transaction. Every disbursement will have a unique reference assigned by the bank.
reversal_found
reversal_statement_id
reversal_statement_number
reversal_statement_sequence
reversal_entry_sequence
reversal_reason	As found in MT940 statement. This reason may be found in any of the six lines of Narrative. Implementation will differ across sponsor banks Will depend on an Bank specific adapter implementation to extract the "reversal_reason" from the Narratives

disbursement_error_recon

Attribute	Description
bank_disbursement_batch_id
recon_statement_id	This is the Unique ID that is given to each MT940 that is uploaded into the platform
recon_statement_number	This is the Statement Number that is found in the MT940 header - field 28C
recon_statement_sequence
recon_entry_sequence	This is the sequence number of the entry in this statement - the entry that corresponds to this disbursement. This entry will be reflected as a "Debit" in the Program Account with the Sponsor Bank.
bank_reference_number	Bank's unique reference number for the transaction. Every disbursement will have a unique reference assigned by the bank.
disbursement_id
error_reason	INVALID_DISBURSEMENT DUPLICATE_DISBURSEMENT INVALID_REVERSAL