Document Logs
Change log:
...
The purpose of this document is to provide an overview of the functionality and implementation of the SEPA Payment Engines.
...
Engines.
1.1 - SEPA Process
| Drawio | ||||
|---|---|---|---|---|
|
2 - Introduction to Functionality
...
The following diagram presents the main classes of the SEPA Generator:
| Drawio | ||||
|---|---|---|---|---|
|
The following diagram represents the main classes of the SEPA Reader:
| Drawio | ||||
|---|---|---|---|---|
|
3.2 - Data Model
The SEPA Engines use specific tables to retrieve and store information. The following ER diagram shows the relationship between the tables of the SEPA Generator Engine:
| Drawio | ||||
|---|---|---|---|---|
|
Table Name | Description |
|---|---|
SEPA_PAYMENT | This is the table used by the SEPA Generator Engine to create the SEPA XML files. Contains information for the XML header section like Create date, number of transactions, etc. |
SEPA_PAYMENT_INFO | Contains information related to the payment: Creditor Info / Debtor Info depending on the type of transaction. |
SEPA_PAYMENT_TRANS_INFO | Contains information specific to the individual transactions. |
...
The SEPA Reader Engine use specific tables to store information. The following ER diagram shows the relationship between the tables of the SEPA Reader Engine:
| Drawio | ||||
|---|---|---|---|---|
|
Table Name | Descrption |
|---|---|
SEPA_INCOMING_FILE | This table is used by the SEPA Reader Engine to store information related to the incoming file like file name, load date, creation date, etc. |
SEPA_INCOMING_RECORD | Contains information related to a single transaction. |
SEPA_PAYMENT_STATUS_INFO | This table contains information related to each single transaction from the Process Incoming Payment Initiation (pain.002) files. This information is related to the rejection codes provided in the incoming file. |
...
Name | Type | Description | Mandatory |
|---|---|---|---|
ID | NUMBER |
| Autogenerated |
TYPE | NUMBER | Indicates the type of incoming file. The possible values are: 2 = SEPA_PAIN and 3 = SEPA_CASH | YES |
LOADED_DATE | DATE | The date when the file was loaded. | YES |
ORIG_MSG_ID | NUMBER | The original ID of the SEPA_PAYMENT table provided by the pain.002 files. This information is taken from the element: <OrgnlGrpInfAndSts><OrgnlMsgId> | NO |
FILE_NAME | VARCHAR(200) | The incoming file name. A database constraint ensures that this name is unique so the same file cannot be read 2 times. | YES |
RCPT_NAME | VARCHAR(140) | The recipient name. This field is only used by camt.054 files. The information is taken from the element: <GrpHdr><MsgRcpt><Nm> | NO |
CREATION_DATE | DATE | Date when the file was created. This field is only used by camt.054 files. The information is taken from the element: < GrpHdr> <CreDtTm> | NO |
MSG_ID | VARCHAR(35) | The id generated for the incoming file. This information taken from the element: <GrpHdr><MsgId>. Applies to both camt.054 and pain.002. A database constraint ensures that the MSG_ID is unique so the same file cannot be read 2 times. | YES |
| TRANSACTION_NUMBER | NUMBER | Sum of the number of elements found in <Document><BkToCstmrDbtCdtNtfctn><Ntfctn><Ntry><NtryDtls><Btch><NbOfTxs> | NO |
| TOTAL_SUM | NUMBER | Sum of the total amounts found in <Document><BkToCstmrDbtCdtNtfctn><Ntfctn><Ntry><NtryDtls><Btch><TtlAmt> | NO |
...
Name | Type | Description | Mandatory |
|---|---|---|---|
ID | NUMBER |
| Autogenerated |
SEPA_INCOMING_FILE_ID | NUMBER | Reference to the SEPA_INCOMING_FILE table. | YES |
TYPE | NUMBER | The type of record. Several scenarios determinte the type: 1 = Direct Debit Rejection, 2 = Manual Payment, 3 = Direct Debit Confirmation, | NO |
STATUS_ID | NUMBER | The stauts of the incoming record. The following values are allowed: | YES |
STATUS_COMMENT | VARCHAR(4000) | Field to insert comments. | NO |
END_TO_END_ID | VARCHAR(50) | The End to End Id provided in the file. This field might match the END_TO_END_ID from the SEPA_PAYMENT_TRANS_INFO table.
| NO |
MANDATE_ID | VARCHAR(35) | The mandate Id.
| NO |
ACCOUNT_ID | VARCHAR(34) | Account ID provided by the camt.054 file. This field might be the IBAN or other value. The information is taken either from element: <Ntfctn><Acct><Id><IBAN> or <Ntfctn><Acct><Id><Othr><Id> | NO |
FINANTIAL_INST_BIC | VARCHAR(11) | The Finantial Institution BIC. This field is only used by the camt.054 files. The information is taken from the element: < Ntfctn>< Acct><Svcr><FinInstnId><BICFI> | NO |
AMOUT | NUMBER | The transaction amount.
| NO |
TRANSACTION_DATE | DATE | The transaction date.* pain.002 (credit transfer):<OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><ReqdExctnDt>
| NO |
PYMT_RETURN_ORGTR | VARCHAR(140) | This field is present in case of rejections.
| NO |
PYMT_RETURN_RSN_CODE | VARCHAR(4) | Payment return reason code. This field is present in case of rejections and only codes of the external ISO 20022 code list are permitted.
| NO |
PYMT_RETURN_PRTRY | VARCHAR(35) | Payment return propietary. This field is only used by the camt.054 files. The information is taken from the element: < Ntfctn><Ntry><NtryDtls><TxDtls><RtrInf><OrgnlBkTxCd><Prtry><Cd> | NO |
DEBTOR_NAME | VARCHAR(140) | The debtor name.
| NO |
DEBTOR_BIC | VARCHAR(11) | The debtor BIC.
| NO |
DEBTOR_IBAN | VARCHAR(34) | The debtor IBAN.
| NO |
CREDITOR_NAME | VARCHAR(140) | The creditor name.
| NO |
CREDITOR_BIC | VARCHAR(11) | The creditor BIC.
| NO |
CREDITOR_IBAN | VARCHAR(34) | The creditor IBAN.
| NO |
BRAND_ID | NUMBER | The brand id. | NO |
REFERENCE | VARCHAR(4000) | This field might contain information related to the transaction. In case of manual payment this can be used as a reference.
| NO |
| EXTERNAL_TX_ID | VARCHAR2(50) | Taken from the camt.054 file: <Document><BkToCstmrDbtCdtNtfctn><Ntfctn><Ntry><NtryDtls><TxDtls><Refs><TxId> | NO |
...