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.
Direction
Inward
Invoked by
Sponsor bank, Implementation Integration work
Mode
Synchronous
Tables
benefit_program_account_statement account_statement_lob
Object design
account_statement
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
statement_id
statement_lob
TEXT type Stores the MT940 Statement as TEXT
Business logic
Persist the Account Statement in the two tables - account_statement & account_statement_lob
In the table - account_statement, only the following columns are populated - statement_id, statement_upload_timestamp and statment_process_status = PENDING
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'
Picks up all eligible account_statement_records
For each account statement, delegates a task to mt940_processor_worker
Payload -- statement_id
mt940 - statement format
mt940_processor_worker
Payload -- statement_id
Picks up the record from account_statement
Picks up the lob from account_statement_lob
Parse the mt940 - header and trailer and retrieve the following
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
reference_number - Tag :20: of MT940 - Header Section
E.g. - :20:CSCT032000136465
statement_number - Tag :28C: of MT940 - Header Section
E.g. - :28C:00001/001 (section before slash "/" is statement number)
sequence_number - Tag :28C: of MT940 - Header Section
E.g. - :28C:00001/001 (section after slash "/" is sequence number)
Update these attributes in the table - account_statement
Now loop through the transaction section of the MT940
Each Transaction consists of two lines (tags) - :61: & :86: (Statement and Narrative)
:61: is known as the Statement line and has the following structure
:61:1507020702D115945,00F014NARRATIVE//0207150143062089CRLF1234567890
150702 -- 6 digits -- Transaction Value Date in YYMMDD format
0702 -- 4 digits -- Transaction Booking Date in MMDD format
C/D/RC/RD -- 2 digits -- Credit, Debit, Reversal-Credit, Reversal-Debit
115945,00 -- Transaction Amount -- Maximum 19 characters
F014 -- Transaction Code -- Should be a standard transaction code - 1 for Credit and another 1 for Debit
NARRATIVE -- Transaction Narrative -- Should be the Beneficiary Name -- This should be as sent by the g2p-bridge to the Sponsor Bank
// Reference Separator
0207150143062089CRLF1234567890 -- Transaction Reference Number issued by the Bank for this transaction
: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
For each MT940 Transaction - retrieve the "disbursement_id" from the transaction
The "disbursement_id" should ideally be the Customer Reference Number. However, this depends on the Sponsor Bank banking platform
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)
So extracting the "disbursement_id" from the mt940 transaction - should be abstracted out to an interface - with an adapter implementation for different sponsor banks.
For "D" transactions, insert a new record for the "disbursement_id" in the table - disbursement_recon_from_bank
For "RD" transactions, updated the existing record in - disbursement_recon_from_bank
Log errors into disbursement_error_recons. The following error conditions are logged into this table
I get a "D" transaction, but I don't get a valid "disbursement_id" - INVALID_DISBURSEMENT
I get a "D" transaction, but the "disbursement_id" is already present in the table - disbursement_recon_from_bank - DUPLICATE_DISBURSEMENT
I get a "RD" transaction, but the "disbursement_id" is not present in the table - disbursement_recon_from_bank - INVALID_REVERSAL
disbursement_recon
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
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
Last updated
