Page Comparison

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

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

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

VARCHAR2(50)