Skip to end of metadata
  • Created by Former user , last modified by Former user on 10.07.2014
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 116 Next »

Unknown macro: {table-plus}

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

 

 

 

 

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

  File Modified
No files shared here yet.

1 - Purpose of Document

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

2 - Introduction to Functionality

The objective of SEPA is to provide standards for Euro payments. All payments under this regulation are to be considered domestic transactions and enable users to make payment transactions in Euro from a single bank account within the SEPA area. SEPA offers a set of instruments to process these payment transactions (SEPA Credit Transfers and SEPA Direct Debits) in an easy and secure way.

3 - Architecture and Design

All the payment transactions are XML based. In order to support the XML file generation and XML processing (reading) two Engines will be implemented:

  • SEPA Generator: This engine will read the information from the SEPA Payment table and create the SEPA XML file depending on the payment type (Direct Debit or Credit Transfer).
  • SEPA Reader: This engine will read the incoming SEPA XML and update the corresponding SEPA tables. The two incoming files are: Cash Management (camt.054) and Process Incoming Payment Initiation (pain.002).

It is important to notice that the standard FTP Engine will transfer the xml files from the outgoing folders to the remote server and the incoming files from the remote server.

3.1 - Object Model

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

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

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:

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 / Debitor Info depending on the type of transaction.

SEPA_PAYMENT_TRANS_INFO

Contains information specific to the individual transactions.

3.2.1 - SEPA_PAYMENT Table

Name

Type

Description

Mandatory

ID

NUMBER

This field is used to set the message Id in the xml files.

  • DD xml: <GrpHdr><MsgId>
  • CT xml: <GrpHdr><MsgId>

Autogenerated

STATUS_ID

NUMBER

Status used by the engine to process the records.

YES 

FILE_NAME

VARCHAR (200CHAR)

The XML file name.

YES

DESCRIPTION

VARCHAR (200CHAR)

Field used to write information.

NO

COMMENTS

VARCHAR2(4000CHAR)

Contains information related to errors.

NO

TYPE

NUMBER

Indicates the type of payment (Direct Debit / Credit Transfer).

YES

CREATION_DATE

DATE

Creation date of the Sepa payment entry

NO (Updated by the SEPA Generator)

FILE_CREATION_DATEDATECreation date of the fileNO (updated by the file generation engine)

TRANSACTION_NUMBER

NUMBER

Number of transactions.

  • DD xml: <GrpHdr><NbOfTx>
  • CT xml: <GrpHdr><NbOfTxs>

YES

TOTAL_SUM

NUMBER

Total amount of all transactions.

  • DD xml: <GrpHdr><CtrlSum>
  • CT xml: <GrpHdr><CtrlSum>

YES

INITIAL_PARTY_NAME

VARCHAR(70CHAR)

The party that initiates the transaction. Max 70 characters.

  • DD xml: <GrpHdr><InitgPty><Nm>
  • CT xml: <GrpHdr><InitgPty><Nm>

YES

BRAND_ID

NUMBER

 

NO

CA_SUB_TYPE_IDNUMBERThe custom attribute SUB_TYPE_ID that for refunds holds the refund cycleNO

3.2.2 - SEPA_PAYMENT_INFO Table

Name

Type

Description

Mandatory

ID

NUMBER

  •  DD xml: <PmtInf><PmtInfId>
  •  CT xml: <PmtInf><PmtInfId>

Autogenerated

SEPA_PAYMENT_ID

NUMBER

Reference to the SEPA_PAYMENT table.

YES

BATCH_BOOKING

VARCHAR(6CHAR)

True/False. If not set the default value is true.

  • DD xml: <PmtInf><BtchBookg>
  • CT xml: <PmtInf><BtchBookg>

NO

TRANSACTION_NUMBER

NUMBER

Number of individual transactions contained in the Payment Information Block.

  • DD xml: <PmtInf><NbOfTxs>
  • CT xml: <PmtInf><NbOfTxs>

YES

CATEGORY_PURPOSE

VARCHAR(4CHAR)

Max 4 characters - Only codes from the ISO 20022 ExternalPurposeCode list are allowed.

  • DD xml: <PmtInf><PmtTpInf><CtgyPurp><Cd>
  • CT xml: <PmtInf><PmtTpInf><CtgyPurp><Cd>

NO

REQ_DATE

DATE

Requested date. It is the Requested Collection Date in case of Direct Debit and Requested Execution in case of Credit Transfer.

  • DD xml: <PmtInf><ReqdColltnDt>
  • CT xml: <PmtInf><ReqdExctnDt>

YES

FINANTIAL_INST_BIC

VARCHAR(11CHAR)

Finantial Institution BIC.

  • DD xml: <PmtInf><CdtrAgt><FinInstnId><BIC>
  • CT xml: <PmtInf><DbtrAgt><FinInstnId><BIC>

NO /YES (Mandatory for DD version Pain.008.002.02

CHARGE_BEARER

VARCHAR(5CHAR)

Only SLEV is allowed.

NO

LOCAL_INSTRUMENT_CODE

VARCHAR(10CHAR)

Possible values are COR1/CORE. This field is only used by DDT.
<PmtInf><PmtTpInf><LclInstrm><Cd>

YES (Please note that for the DD version Pain.008.002.02 the two possible values are: CORE and B2B)

SEQUENCE_TYPE

VARCHAR(6CHAR)

Possible values are FRST, RCUR, FNAL, OOF. This field is only used by DDT.
<PmtInf> < PmtTpInf> <SeqTp>

YES

CSI_ID

VARCHAR(35CHAR)

Creditor Scheme Identification. This field is only used by DDT. < PmtInf> <CdtrSchmeId><Id>

NO

CSI_NAME

VARCHAR(70CHAR)

This field is only used by DDT.

NO

CREDITOR_NAME

VARCHAR(70CHAR)

This field is only used by DDT. <PmtInf><Cdtr><Nm>

YES

CREDITOR_ADDRESS

VARCHAR(70CHAR)

This field is only used by DDT.  < PmtInf>< Cdtr> < PstlAdr><AdrLine>

NO

CREDITOR_COUNTRY

VARCHAR(10CHAR)

This field is only used by DDT.  < PmtInf>< Cdtr><PstlAdr><Ctry>

NO

CREDITOR_ACCOUNT_CURRENCY

VARCHAR(5CHAR)

This field is only used by DDT. < PmtInf> <CdtrAcct><Ccy>

NO

CREDITOR_IBAN

VARCHAR(34CHAR)

This field is only used by DDT.  < PmtInf> < CdtrAcct><Id><IBAN>

YES

INSTRUCTIOIN_PRIORITY

VARCHAR(7CHAR)

Possible values are HIGH and NORM. This field is only used by CTT. <PmtInf><PmtTpInf><InstrPrty>

NO

DEBTOR_NAME

VARCHAR(70CHAR)

This field is only used by CTT.<PmtInf><Dbtr><Nm>

YES

DEBTOR_ADDRESS

VARCHAR(70CHAR)

This field is only used by CTT. < PmtInf>< Dbtr><PstlAdr><AdrLine>

NO

DEBTOR_COUNTRY

VARCHAR(10CHAR)

This field is only used by CTT. < PmtInf>< Dbtr><PstlAdr><Ctry>

NO

DEBTOR_ACCOUNT_CURRENCY

VARCHAR(5CHAR)

This field is only used by CTT.<PmtInf><DbtrAcct><Ccy>

NO

DEBTOR_IBAN

VARCHAR(34CHAR)

This field is only used by CTT. <PmtInf><DbtrAcct><Id><IBAN>

YES

3.2.3 - SEPA_PAYMENT_TRANS_INFO Table

Name

Type

Description

Mandatory

ID

NUMBER

 

Autogenerated

PAYMENT_REFERENCE_ID

NUMBER

Contains the reference to the internal payment tables: ACCOUNT_PAYMENT, INVOICE or BILLING_GROUP_REFUND_REQUEST

YES

PAYMENT_REFERENCE_TABLE

VARCHAR(128CHAR)

Contains the name of the reference payment table: ACCOUNT_PAYMENT, INVOICE or BILLING_GROUP_REFUND_REQUEST

YES

PAYMENT_INFO_ID

NUMBER

Reference to the SEPA_PAYMENT_INFO table.

YES

INSTRUCTION_ID

NUMBER

Unique ID assigned by an instructing party.

  • CT xml: <PmtInf><CdtTrfTxInf><PmtId><InstrId>

NO

END_TO_END_ID

VARCHAR(50CHAR)

Unique ID assigned by the initiating party to unambiguously identify the transaction.

  • CT xml: <PmtInf><CdtTrfTxInf><PmtId><EndToEndId>

YES

INSTRUCTED_AMOUNT

NUMBER

The amount instructed by the ordering party.

  • CT xml: <PmtInf><CdtTrfTxInf><Amt><InstdAmt>

YES

REMITTANCE_INFO

VARCHAR(140CHAR)

Information that enables the matching, i.e. reconciliation, of a payment with the items that the payment is intended to settle, e.g. commercial invoices in an account receivable system.

  • CT xml: <PmtInf><CdtTrfTxInf><RmtInf><Ustrd>

NO

ULTIMATE_CREDITOR_NAME

VARCHAR(70CHAR)

Creditor name party. For information only.

  • CT xml: <PmtInf><CdtTrfTxInf><UltmtCdtr><Nm>

NO

ULTIMATE_DEBTOR_NAME

VARCHAR(70CHAR)

Debtor reference party. For information only.

  • CT xml: <PmtInf><CdtTrfTxInf><UltmtDbtr><Nm>

NO

PURPOSE

VARCHAR(4CHAR)

Only codes from the ISO 20022 ExternalPurposeCode list are allowed.

  • CT xml: <PmtInf><CdtTrfTxInf><Purp><Cd>

NO

DEBTOR_BIC

VARCHAR(11CHAR)

This field is only used by DDT.

NO / YES for the version Pain.008.002.02

DEBTOR_NAME

VARCHAR(70CHAR)

This field is only used by DDT.

YES

DEBTOR_ADDRESS

VARCHAR(70CHAR)

This field is only used by DDT.

NO

DEBTOR_COUNTRY

VARCHAR(10CHAR)

This field is only used by DDT.

NO

DEBTOR_IBAN

VARCHAR(34CHAR)

This field is only used by DDT.

YES

MANDATE_ID

VARCHAR(35CHAR)

This field is only used by DDT.

YES

MANDATE_SIGNATURE_DATE

DATE

This field is only used by DDT.

YES

AMENDMENT_INDICATOR

VARCHAR(6CHAR)

Possible values True/Fase. This field is only used by DDT.

NO

ELECTRONIC_SIGNATURE

VARCHAR(1025CHAR)

This field is only used by DDT.

NO

ORIG_MANDATE_ID

VARCHAR(35CHAR)

Original Mandate Id. This field is only used by DDT.

NO

ORIG_CREDITOR_NAME

VARCHAR(70CHAR)

This field is only used by DDT.

NO

ORIG_CREDITOR_ID

VARCHAR(35CHAR)

Original Creditor Identifier. This field is only used by DDT.

NO

ORIG_DEBTOR_IBAN

VARCHAR(34CHAR)

This field is only used by DDT.

NO

CREDITOR_SCHEME_ID

VARCHAR(35CHAR)

This field is only used by DDT.

NO

AMOUNT_CURRENCY

VARCHAR(5CHAR)

This field is only used by CTT. 

NO (Default is EUR)

CREDITOR_BIC

VARCHAR(11CHAR)

This field is only used by CTT. <PmtInf><CdtTrfTxInf><CdtrAgt><FinInstnId><BIC>

NO / YES for the version Pain.001.002.03

CREDITOR_NAME

VARCHAR(70CHAR)

This field is only used by CTT.<PmtInf><CdtTrfTxInf><Cdtr><Nm>

YES

CREDITOR_ADDRESS

VARCHAR(70CHAR)

This field is only used by CTT. <PmtInf><CdtTrfTxInf>< Cdtr><PstlAdr><AdrLine>

NO

CREDITOR_COUNTRY

VARCHAR(10CHAR)

This field is only used by CTT. <PmtInf><CdtTrfTxInf>< Cdtr><Ctry>

NO

CREDITOR_IBAN

VARCHAR(34CHAR)

This field is only used by CTT. <PmtInf><CdtTrfTxInf><CdtrAcct ><Id><IBAN>

YES

Note

The SEPA_PAYMENT_INFO table and SEPA_PAYMENT_TRANS_INFO contain all the necessary fields to support the Direct Debit and Credit Transfer transactions.
The fields for each one are indicated in different colors.

 

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:

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.

3.2.4 - SEPA_INCOMING_FILE Table

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

3.2.5 - SEPA_INCOMING_RECORD Table

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

3.2.6 - SEPA_PAYMENT_STATUS_INFO Table

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

Unknown macro: {table-plus}
  • No labels