Versions Compared

Old Version 119

changes.mady.by.user Søren Duusgaard

Saved on

compared with

New Version Current

changes.mady.by.user Mohamad Zind

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

Document Logs
Change log:

Date:

Author:

Version:

Changes:

Completed

Ext.

Int.

Is in Core

0.1

Doc. created

SI

 

 

 

17 July 2013

Anders Jørgensen

0.2

Update allowed values of the STATUS_ID column in SEPA_INCOMING_RECORD table

 

 

 

 

23 July 2013

Esau Castillo

0.3

Updated the Diagrams due to engine refactor

 

 

 

 

05 September 2013

Esau Castillo

0.4

Updated the datatype of the END_TO_END_ID column

 

 

 

 

09 February 2016Esau Castillo0.5Added a diagram to explain the SEPA Process    

Terms and definitions:

Terms/definitions:

Meaning:

TBD

To be defined

N/A

Not applicable

CSI

Creditor Scheme Identification

DDT

Direct Debit Transaction

CTT

Credit Transfer Transaction

SEPA

Single Euro Payment Area

pain.002

Process Incoming Payment Initiation file

camt.054

The Cash Management file

...

The purpose of this document is to provide an overview of the functionality and implementation of the SEPA Payment Engines.

1.1 - SEPA Process

Drawio
diagramNameSEPA Process.drawio
revision1

2 - Introduction to Functionality

...

It is important to notice that the standard FTP Engine /wiki/spaces/engines/pages/115671594 will transfer the xml files from the outgoing folders to the remote server and the incoming files from the remote server.

...

The following diagram presents the main classes of the SEPA Generator:SEPA Generator:

Drawio
diagramNameUML SEPA Generator.drawio
revision1

The following diagram represents the main classes of the SEPA Reader:

Drawio
diagramNameUML SEPA Reader.drawio
revision1

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
diagramNameER SEPA Generator Tables.drawio
revision1

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
diagramNameER SEPA Reader Tables.drawio
revision1

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_NUMBERNUMBERSum of the number of elements found in <Document><BkToCstmrDbtCdtNtfctn><Ntfctn><Ntry><NtryDtls><Btch><NbOfTxs>NO
TOTAL_SUMNUMBERSum 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,
4 = Refund Rejection, 5 = Refund Confirmation.

NO

STATUS_ID

NUMBER

The stauts of the incoming record. The following values are allowed:
0 = New
1 = Processing
2 = Completed
3 = Error
4 = Error Duplicate Rejection
5 = Fatal Error
6 = Manual Handling
7 = Resend Payment
8 = Reprocessing Completed

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.

  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlEndToEndId>
  • camt.054: <Ntry><NtryDtls><TxDtls><Refs><EndToEndId>

NO

MANDATE_ID

VARCHAR(35)

The mandate Id.

  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><MndtRltdInf><MndtId>
  • camt.054: <Ntry><NtryDtls><TxDtls><MndtId>

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.

  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><Amt><InstdAmt>
  • camt.054: <Ntry><NtryDtls><TxDtls><Amt>

NO

TRANSACTION_DATE

DATE

The transaction date.*  pain.002 (credit transfer):<OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><ReqdExctnDt>

  •  pain.002 (direct debit): <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><ReqdColltnDt>
  •  camt.054: <Ntry><NtryDtls><TxDtls><RltdDts><TxDtTm>
  •  camt.054(Credit Transfer): <Ntry><BookgDt>

NO

PYMT_RETURN_ORGTR

VARCHAR(140)

This field is present in case of rejections.

  •  camt.054: <Ntfctn><Ntry><NtryDtls><TxDtls><RtrInf><Orgtr><Nm>
  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><StsRsnInf><Orgtr><Nm>

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.

  •  camt.054: <Ntfctn><Ntry><NtryDtls><TxDtls><RtrInf><Rsn><Cd>.
  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><StsRsnInf><Rsn><Cd>

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.

  •  pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><Dbtr><Nm>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdPties><Dbtr><Nm>

NO

DEBTOR_BIC

VARCHAR(11)

The debtor BIC.

  •  pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><DbtrAgt><FinInstnId><BIC>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdAgts><DbtrAgt><FinInstnId><BIC>

NO

DEBTOR_IBAN

VARCHAR(34)

The debtor IBAN.

  •  pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><DbtrAcct><Id><IBAN>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdPties><DbtrAcct><Id><IBAN>

NO

CREDITOR_NAME

VARCHAR(140)

The creditor name.

  •  pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><Cdtr><Nm>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdPties><Cdtr><Nm>

NO

CREDITOR_BIC

VARCHAR(11)

The creditor BIC.

  •  pain.002: OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><CdtrAgt><FinInstnId><BIC>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdAgts><CdtrAgt><FinInstnId><BIC>

NO

CREDITOR_IBAN

VARCHAR(34)

The creditor IBAN. 

  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><CdtrAcct><Id><IBAN>
  • camt.054: <Ntry><NtryDtls><TxDtls><RltdPties><CdtrAcct><Id><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.

  • pain.002: <OrgnlPmtInfAndSts><TxInfAndSts><OrgnlTxRef><RmtInf><Ustrd>
  • camt.054: <Ntry><NtryDtls><TxDtls><RmtInf><Ustrd>; if empty then the value is taken from <Ntry><NtryDtls><TxDtls><RmtInf><Strd><CdtrRefInf><Ref>

NO

EXTERNAL_TX_ID

VARCHAR2(50)

Taken from the camt.054 file: <Document><BkToCstmrDbtCdtNtfctn><Ntfctn><Ntry><NtryDtls><TxDtls><Refs><TxId>NO

...

Name

Type

Description

Mandatory

ID

NUMBER

 

Autogenerated

REFERENCE_ID

NUMBER

Contains the reference of  the SEPA_INCOMING_RECORD

YES

ORG_NAME

VARCHAR(70)

Name of the return originator. Party issuing the return.

NO

ORG_BIC

VARCHAR(11)

BIC of the return originator.

NO

CODE

VARCHAR(4)

Only codes from the ISO 20022 are allowed.

NO

...