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
opening_balance	Tag :60F: of MT940 - Header Section Positive Number -- Stands for Credit Balance Negative Number -- Stands for Debit Balance
opening_balance_date	Tag :60F: of MT940 - Header Section
closing_balance	Tag :62F: of MT940 - Trailer Section Positive Number -- Stands for Credit Balance Negative Number -- Stands for Debit Balance
closing_balance_date	Tag :62F: of MT940 - Trailer Section
closing_available_balance	Tag :64: of MT940 - Trailer Section Positive Number -- Stands for Credit Balance Negative Number -- Stands for Debit Balance
closing_available_balance_date	Tag :64: of MT940 - Trailer 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 = UNPROCESSED

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

   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. opening_balance - Tag :60F: of MT940 - Header Section

      E.g. - :60F:C171120AUD98838,27

      C or D -- stands for Credit Balance or Debit Balance

      171120 -- 6 characters - Statement Date in YYMMDD format

      AUD -- Australian Dollar - Currency of the Account

      98838,27 -- Ninety Eight Thousand Eight Hundred Thirty Eight AUD and Twenty Seven Cents - The comma is to be treated as decimal

   6. closing_balance - Tag :62F: of MT940 - Trailer Section

      Similar to Tag 60F

   7. closing_available_balance - Tag :64: of MT940 - Trailer Section

      Similar to Tag 60F and 62F

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 -- 1 digit -- Credit or Debit indicator

   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. Create a List of Transactions for a Statement based on the Statement lines. The Statement Pydantic model should contain the following attributes

    1. Transaction Value Date

    2. Transaction Book Date

    3. Transaction Amount

    4. Debit-Credit Indicator

    5. Beneficiary Name -- NARRATIVE field (after the Reference Separator //)

    6. XX

11. Loop through the transactions. For each transaction

    1. Based on the Transaction Book Date -- go back 2 days (book date minus 2)

    2. This 2 (can be a configuration)

    3. get the benefit program based on the sponsor bank account number (benefit_program_configuration)

    4. for this benefit program, get a. list of all disbursements from Book Date minus 2

    5. the idea is that - based on an SLA -- the book date represents when the bank would have processed the disbursements after g2p-bridge dispatched them to the bank

    6. every disbursement should ideally have a corresponding Debit entry in the account statement

    7. match the transaction amount and the beneficiary details and the program details (how the narrative is formed has to be discussed)

    8. update the table - disbursement_

disbursement_recon_from_bank

Attribute	Description
bank_disbursement_batch_id
disbursement_id	Unique Index
recon_statement_id	This is the Unique ID that is given to each MT940 that is uploaded into the platform
bank_statement_number	This is the Statement Number that is found in the MT940 header - field 28C
corresponding_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_bank_statement_number
reversal_entry_sequence
reversal_reason

The sponsor bank invokes this API after it gets back the status of the disbursement from the destination bank through the national clearing / payment switch.

This API (as the name suggests) updates the status of the disbursment.

There are two possibilities - the disbursement either is successful or is a failure.

SUCCESS

Update the following attributes in disbursement_batch_status

Attribute	Datatype
reply_status_from_bank	SUCCESS
reply_from_bank_time_stamp	Time stamp of receipt of reply from Sponsor bank
reply_success_fsp_code	If the disbursement is a success, the fsp (the financial service provider / destination bank) code - where the account was credited
reply_success_fa	The full Financial Address (including account number, branch code / mobile number) where the disbursement was credited

FAILURE

Update the following attributes in disbursement_batch_status

Attribute	Datatype
reply_status_from_bank	FAILURE
reply_from_bank_time_stamp	Time stamp of receipt of reply from Sponsor bank
reply_failure_error_code	Error code from the downstream G2P chain participants (Sponsor bank, Payment switch, Destination banks) in case of a FAILURE (reply_status_from_bank)
reply_failure_error_message	Error message describing the failure_error_code