Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Document Logs
Change log:

...

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

 

4.- The SEPA Process

Wiki Markup
{table-plus}{table-plus}