7. Batch Upload Function
7.1 Introduction
Entering high volume funds transfer contracts can be laborious and time consuming. You can avoid entering such contracts by using the Batch Upload Function. The FT Batch Upload function is designed to accept raw data that can be processed into an FT contract in Oracle FLEXCUBE. This function when invoked, automatically reads the data that is resident in the gateway (upload) tables of Oracle FLEXCUBE and create contracts in the FT module of Oracle FLEXCUBE. FT contracts can come into the gateway (upload) tables from any external system or source depicted as the Outer World in the diagram. Contracts to be uploaded onto Oracle FLEXCUBE should match certain validations, which will be discussed in the course of this chapter.
FT contracts can come into Oracle FLEXCUBE from any source depicted as the ‘Outer World’ in the diagram. However, Oracle FLEXCUBE will pick up contracts for upload only from the upload tables of Oracle FLEXCUBE.
For FT contracts to be successfully uploaded on to Oracle FLEXCUBE, the details of such contracts resident in the upload tables should pass all validations checked by the system.
In the course of this chapter we will discuss:
- The maintenance that is required prior to uploading FT contracts in Oracle FLEXCUBE.
- The steps involved in the contract upload process, including handling of errors/ overrides.
- The structure of the gateway (upload) tables and the type of data that Oracle FLEXCUBE expects to be available in the upload tables prior to the upload process.
This chapter contains the following sections:
- Section 7.2, "Upload Sources"
- Section 7.3, "Amending Uploaded Contract"
- Section 7.4, "Reversing Uploaded Contract"
- Section 7.5, "Automatic Upload of MT 103 and MT 202 S.W.I.F.T Messages"
- Section 7.6, "Contracts through STP Function"
- Section 7.7, "Uploading Contracts through Upload Master Screen"
- Section 7.8, "Upload of Incoming SWIFT Payment Messages"
- Section 7.9, "Single Debit Multiple Credit (SDMC) for Bulk Payments"
7.2 Upload Sources
This section contains the following topics:
7.2.1 Maintaining Upload Sources
Oracle FLEXCUBE provides the facility of uploading funds transfer transactions on an online basis, from external upstream systems.
To recall, FT contracts can be uploaded onto Oracle FLEXCUBE from any external source, such as an upstream system. However, Oracle FLEXCUBE will pick up transactions for upload only from its FT upload (or gateway) tables.
For FT transactions to be successfully uploaded on to Oracle FLEXCUBE, the details of such transactions resident in the upload tables should pass all validations done by the system.
Oracle FLEXCUBE validates the uploaded transactions, and after successful validation, they are taken up for processing as FT contracts in the system. Any transactions that fail the validations are rejected, and the reason for rejection recorded.
For reporting purposes, before you actually begin to upload FT contracts onto Oracle FLEXCUBE, you should maintain details of the sources from which contracts can come into the upload tables. A source in Oracle FLEXCUBE is simply a collection of attributes for a batch of contracts coming in through the upload tables.
For a source, you can define the operations (post upload) that can be performed on contracts uploaded from a particular source, and also define the status that uploaded contracts should be marked with. You can also define the exception handling attributes at this level.
Upload sources can be maintained at the Head Office level and propagated to the branches of your bank.
The maintenance of upload sources helps to retrieve information for a given source.
The procedure for maintaining details of an upload source has been discussed under the head ‘Maintaining Upload Source Details’, and ‘Specifying Upload Source Preferences’ in the ‘External System Maintenance’ chapter of the ‘Gateway Services’ user manual.
7.2.2 Deleting Uploaded Contract
Deleting an uploaded FT contract (from the Oracle FLEXCUBE front end, post upload) may or may not be allowed. It is determined by the specifications you made in the ‘Delete Allowed’ field of the ‘Source Detail Maintenance’ screen.
To delete an uploaded FT contract, Select ‘Delete’ from the Actions menu in the Application tool bar or click delete icon, when you view the contract in the summary or detailed view.
You can delete an uploaded contract only if:
- the record is unauthorized
- you have allowed the delete operation for the record in the Source Detail Maintenance screen.
7.3 Amending Uploaded Contract
Amending an uploaded FT contract after upload, may or may not be allowed. It is determined by the specifications you have made in the ‘Amend Allowed’ field on the ‘Source Detail Maintenance’ screen. To amend an uploaded record, choose upload from the Actions Menu.
Amendments to an unauthorized uploaded contract
If the uploaded contract bears the status ‘unauthorized’, Oracle FLEXCUBE will allow you to amend only those fields that have been marked with ‘Amend allowed’.
Amendment to an authorized uploaded contract
If the uploaded contract bears the status ‘authorized’, you can amend only those fields that have been marked with ‘Amend allowed’ in authorized state.
Amending an uploaded contract placed on ‘hold’
If an FT Contract has been uploaded and placed on hold, you will be allowed to amend only those fields of the uploaded contract for which you had specified that amendment is allowed.
7.4 Reversing Uploaded Contract
Reversing an uploaded FT contract may or may not be allowed. It is determined by the specifications you have made in the ‘Reverse Allowed’ field on the ‘Source Detail Maintenance’ screen.
To reverse an uploaded contract select ‘Reverse’ from the Actions menu in the Application tool bar or click reverse icon. You can reverse an uploaded record if:
- the record is authorized
- you have allowed the reverse operation for the record in the ‘Source Detail Maintenance’ screen
7.5 Automatic Upload of MT 103 and MT 202 S.W.I.F.T Messages
The Message Upload facility of Oracle FLEXCUBE allows you to automatically process all incoming MT 100, MT 103 and MT 202 messages, which result in either incoming or outgoing Funds Transfers. The Message Upload facility is a part of the Straight Through Processing (STP) function in Oracle FLEXCUBE.
7.6 Contracts through STP Function
This section contains the following topics:
- Section 7.6.1, "Uploading Contracts through STP Function"
- Section 7.6.2, "FT Upload Tables (Gateway Tables)"
- Section 7.6.3, "Structure of FT Upload (Gateway) Tables"
7.6.1 Uploading Contracts through STP Function
One of the most important features of the Funds Transfer module is the facility of processing contracts without user intervention. Messages received from ordering customers are interpreted, resolving the details of the contract, which is then booked automatically in the system and then processed to closure. This kind of automatic processing is known as straight through processing or STP.
The Message Upload function, which is a part of STP, resolves incoming SWIFT messages and writes the interpreted details into the FT Upload tables.
When the FT upload function is invoked, it uploads contracts that have been written into the upload tables by the STP Message Upload function, in addition to contracts from external sources. It processes data resident in the FT Upload tables and creates FT contracts in the system, which is then processed normally, just as contracts booked in the normal way, through the FT Contract Online screen.
Note
The branch-level STP preferences are applicable for upload of contracts through the FT Upload process. For details about the branch-level STP preferences, refer the Straight Through Processing chapter in this user manual.
The FT upload can either be invoked independently as a process after populating the data in the FT upload tables (i.e., as described in the section titled ‘Starting the FT Upload function) or will be invoked automatically as a part of the overall STP process.
For details on the complete STP process (and more details on the message upload component of the process), refer to the Straight Through Processing chapter in the Funds Transfer user manual. Only the FT Upload function process, which is part of the overall STP process, is described here.
7.6.2 FT Upload Tables (Gateway Tables)
Transactions can only be uploaded into the gateway tables by an upstream system only when Oracle FLEXCUBE is in transaction input stage. No transactions can be uploaded after the End of Transaction Input (EOTI) stage is marked off in Oracle FLEXCUBE. Any transactions that are uploaded into the gateway tables by an upstream system are marked with the status ‘U’, denoting ‘unprocessed’, in the CSTBS_EXT_CONTRACT_STAT table, which is the control master table for all transactions uploaded into Oracle FLEXCUBE from external systems.
An Oracle background process (Oracle job) constantly checks the gateway tables during Oracle FLEXCUBE’s transaction input to see if any transactions have been uploaded into the tables by an external upstream system, that have not been processed, i.e., marked with the status ‘U’. All such transactions are identified by the Oracle background process and picked up for the purpose of validating the uploaded transaction information.
The FT Upload tables, which are populated with FT contracts from external sources and through the STP Message Upload process, will be examined in detail in this section. The upload tables are also called the gateway tables. The following are the upload tables that need to be populated before invoking the FT Upload function, either manually or automatically through the overall STP process.
| Table Name | Mandatory | Remarks | |||
|---|---|---|---|---|---|
| CSTBS_EXT_CONTRACT_STAT | Yes | Master table of all contract uploads | |||
| FTTBS_UPLOAD_MASTER | Yes | Funds Transfer Upload Master | |||
| ISTBS_UPLOAD_CONTRACTIS | No | Settlement Information for Customer Accounts / Nostro | |||
| CFTBS_UPLOAD_CHARGE | No | Charge details for the contract | |||
| TATBS_UPLOAD_RULE | No | Tax details for the contract | |||
| MITBS_CONTRACT_MAPPING_UPLOAD | No | MIS details for Funds Transfer contracts | |||
| CSTBS_UPLOAD_CONTRACT_UDF | No | User defined fields for Funds Transfer contracts | |||
| FTTBS_UPLOAD_EXCEPTION | No | Exception details in case of upload failure | |||
| FTTBS_UPLOAD_LOG | No | Mandatory download table populated by Oracle FLEXCUBE after successful / failed upload |
7.6.2.1 Upload into Gateway Tables
The contract details of all the contracts to be uploaded from external sources are populated into gateway tables (i.e., the upload tables) of Oracle FLEXCUBE initially, either from a front-office contract booking system or by the Message Upload function of Oracle FLEXCUBE (in the case of STP) .
Every contract that is uploaded is identified by a Source name (as maintained in the FT Upload Source maintenance) and a unique number called Source Reference Number (typically the reference number of the contract in the system in which it was first initiated, such as a front-office system). Once a contract is uploaded into the gateway tables, the Oracle FLEXCUBE system generates a unique Contract Reference Number for each uploaded contract. Subsequently, for all other operations that need to be performed, such as amendment, authorization, reversal and so on, this number identifies the contract.
The gateway tables for Charges, Management Information System (MIS), Tax, and User Defined Field (UDF) components are optional in nature. In the absence of entries in these tables, the system picks up the default details from the product or the customer involved in the contracts, as applicable.
Note
The source reference number also gets displayed in the field 21 of MT 202, as the related reference number. The number should not start or end with a slash ‘/’ and should not have two consecutive slashes ‘//’.
7.6.2.2 Processing and Validations
The Oracle FLEXCUBE FT Upload process can be configured to be invoked either manually or automatically by an Oracle process that continuously checks for newly uploaded contracts. This process picks up all contracts that have a status of ’U‘ in the CSTBS_EXT_CONTRACT_STAT table, and performs validations on the data populated in the upload tables.
All successfully validated contracts will result in creation of contracts in the FT module of Oracle FLEXCUBE and the import status is set to ’Y‘ (Processed) in the CSTBS_EXT_ CONTRACT_STAT table (which is the control table for uploads into Oracle FLEXCUBE).
The post import status of a contract can either be authorized or unauthorized based on the preferences set in the ‘Upload Sources Preferences Maintenance’ screen.
The status of the uploaded contract will be unauthorized if the contract amount is greater than or equal to the transaction limit maintained for the product.
The system converts the contract credit amount into the transaction limit currency using the standard mid rate if the contract currency is different from the transaction limit currency.
The contracts are uploaded as per the ‘Upload Sources Preferences Maintenance’ screen if the contract amount is less than the maintained transaction limit.
For the contracts that are auto authorized, the authorizer of the contract will be ‘SYSTEM’.
For contracts to be put on hold only basic validations are done and the import status and post import status are changed to “Y” (Processed) and “H” (Hold) respectively.
For the contracts that encountered errors and rejected, the import status is set to “E” (‘Error’ and ‘Rejected’).
The FT Upload process does not delete the exception records of an existing FT Upload Transaction that has been marked as ‘E’, i.e., ‘Error’ and ‘Rejected’.
The user ID in the contract information must be a valid Oracle FLEXCUBE user ID, and have appropriate permissions for the upload of FT contracts. The number of contracts uploaded using the user ID is also validated, to see that it does not exceed the maximum number of transactions allowed.
Debit or credit customer types are used based on the product type. Debit customer type is used for internal and outgoing payments. Credit customer type is used for incoming payments.
Any contracts rejected by Oracle FLEXCUBE (i.e. by the FT Upload function) should be corrected at source and re-sent with a different payment reference and status “U”.
Charges and Tax information
The charges and tax information is uploaded by the upstream system into the CFTBS_UPLOAD_CHARGE and TATBS_UPLOAD_RULE gateway tables respectively. The system checks to ensure the correct amount tags, components and tax rule to be used are provided for appropriate charges and taxes applicable to the contract (as maintained in the Oracle FLEXCUBE funds transfer product), in the uploaded transaction information.
Settlement Details
The settlement route for the payment in a transaction is uploaded by the upstream system into the ISTBS_UPLOAD_CONTRACTIS gateway table. If the settlement information is not uploaded into this table, the standard settlement route maintained in Oracle FLEXCUBE under standard settlement instructions maintenance is picked up.
Note
- For uploaded FT contracts, the System checks for the charge bearer for the contract. If found, and the specification is different from that defined for the FT product, an error is raised (if the Allow Change in Contract option has not been enabled). If not specified in the upload information, it is defaulted from the preferences for the product of the contract.
- Also, the Beneficiary Account would undergo IBAN validations if the Beneficiary IBAN Mandatory option has been enabled for the FT Product. A configurable override is logged if the IBAN validations fail.
- The message generated for an incoming transaction may at times contain incorrect beneficiary account number. Invalid account numbers and closed accounts are considered as incorrect for this purpose. In such cases, the system uploads the contract and makes credits to the DAO GL mentioned for the product associated with the contract. Further while liquidating the contract, the system automatically debits the DAO GL. You can specify the actual credit account (valid customer account). However, you will not be allowed to select a GL at this level.
7.6.2.3 Generation of Accounting Entries and Messages
Appropriate accounting entries and messages are passed for all successfully validated contracts except for those in “Hold” status based on the events defined for the product involving the contract.
7.6.2.4 Recording Exceptions and Errors
The Funds Transfer Upload process logs the errors encountered for each contract during the upload process. The error codes indicating the reasons for rejections are populated in an exception table, the FTTBS_UPLOAD_EXCEPTION table.
7.6.2.5 Maintenance Log
All contracts that are processed, irrespective of their status, are recorded in FTTBS_UPLOAD_LOG, which is the log table for all upload processing. If, for a Source Reference, a record already exists in the table, the data is overwritten by the latest values.
7.6.3 Structure of FT Upload (Gateway) Tables
Each of the upload tables is described in detail below:
7.6.3.1 FTTBS_UPLOAD_MASTER
This table consists of the funds transfer contracts information, which will be loaded to the Funds Transfer Contract Master table. This table must be compulsorily populated for the FT Upload function to be invoked.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SOURCE_CODE | Alphanumeric (15) | Yes | Valid Source Code – Primary Key | ||||||||||||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Reference Number of the Other System – Primary Key | ||||||||||||||||||
| FT_CONTRACT_REF | Alphanumeric (16) | No | Will be populated by Oracle FLEXCUBE. Oracle FLEXCUBE Reference Number for cross verification | ||||||||||||||||||
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch to which contract needs to Be uploaded. Valid branch in Oracle FLEXCUBE | ||||||||||||||||||
| USER_REF_NO | Alphanumeric (16) | Yes | IF NULL, Source Reference will be Copied by Oracle FLEXCUBE | User Reference Number for the transaction | |||||||||||||||||
| FT_TYPE | 1 Character | Yes | Type of fund transfer Transaction. | Should be the same as Product Maintenance. | I - Incoming Funds Transfer | O - Outgoing Funds Transfer | N – Internal Funds Transfer or Book Transfer | ||||||||||||||
| MSG_COVER | CHAR (1) | Yes | Default from Product | Cover Message Required | N-Cover not required | Y-Cover required | |||||||||||||||
| MSG_AS_OF | CHAR (1) | Yes | Default from Product | When Outgoing Message should be sent | B-Booking Date | S-Spot Date | V-Value Date | N-Not applicable | D-Debit Value Date | C-Credit Value Date | I-Instruction Date | ||||||||||
| RATE_AS_OF | CHAR (1) | Yes | For Cross Currency, Default from Product | For Non Cross Ccy, Default N | Date as of which the exchange rates must be picked up | B-Booking Date | S-Spot Date | V-Value Date | U-User Input | N-Not applicable | D-Debit Value Date | C-Credit Value Date | I-Instruction Date | For cross currency contracts, should be one of 'B','S','V','U' | |||||||
| AFTER_RATE_CHANGE | CHAR (1) | Yes | N | Pickup rate as of parameter specified or not | Y-Input | N-As per rate as of parameter | |||||||||||||||
| RATE_TYPE | Alphanumeric (8) | No | Valid Oracle FLEXCUBE Rate Type. Should be null for non cross currency contracts | ||||||||||||||||||
| SPREAD_CODE | 1 Character | Yes | Spread Code | 1 – 1 Spread | 2 – ½ Spread | 4 – ¼ Spread | 8 – 1/8 Spread | 9 – No Spread For non cross currency, should be 9. | |||||||||||||
| EXCHANGE_RATE | Number (14,7) | No | User Input Exchange Rates. Mandatory for cross currency user input rates | ||||||||||||||||||
| DR_BRANCH | Alphanumeric (3) | Yes | Debit Account Branch. Valid branch code to which Debit Account belongs | ||||||||||||||||||
| DR_ACCOUNT | Alphanumeric (20) | Yes | Debit Account. Valid Oracle FLEXCUBE account | ||||||||||||||||||
| DR_CCY | Alphanumeric (3) | Yes | Debit Currency. Valid Currency Code in Oracle FLEXCUBE | ||||||||||||||||||
| DR_AMOUNT | Number (22,3) | No | Debit Amount. For non cross currency, cannot be Null. | For Incoming FT cannot be null | Should Be same as CR_AMOUNT for non cross currency contracts | ||||||||||||||||
| DR_VALUE_Date | Date | Yes | Debit Value Date. Holiday Maintenance should exist for this value date | ||||||||||||||||||
| CR_BRANCH | Alphanumeric (3) | Yes | Credit Branch. Valid branch code to which Credit Account belongs | ||||||||||||||||||
| CR_ACCOUNT | Alphanumeric (20) | Yes | Credit Account. Valid Oracle FLEXCUBE account | ||||||||||||||||||
| CR_CCY | Alphanumeric (3) | Yes | Credit Currency. Valid Currency Code in Oracle FLEXCUBE | ||||||||||||||||||
| CR_AMOUNT | Number (22,3) | No | Credit Amount. For non cross currency cannot be Null. | For Outgoing FT cannot be null | Should be the same as CR_AMOUNT for non cross currency contracts | ||||||||||||||||
| CR_VALUE_Date | Date | Yes | Credit Value Date. Holiday Maintenance should exist for this value date | ||||||||||||||||||
| MCK_Number | Alphanumeric (16) | No | Manager’s Check Number. | If MCK Required is ‘Y’ at Product Level then this field is mandatory. Should be unique if supplied. Should not be present if Managers Check required is 'N' at Product Level | |||||||||||||||||
| CHECK_Number | Alphanumeric (16) | No | Check Number. Should not be present if DR Account is a GL. | ||||||||||||||||||
| BY_ORDER_OF1 | Alphanumeric (35) | No | By Order of. For Incoming FT, if Credit Account is A GL, one of the fields of By order Of is mandatory. For Outgoing/Internal FT, if Debit Account is a GL, one of the fields of By Order Of is mandatory | ||||||||||||||||||
| BY_ORDER_OF2 | Alphanumeric (35) | No | By Order of. For Incoming FT, if Credit Account is A GL, one of the fields of By Order Of is mandatory. For Outgoing/Internal FT, if Debit Account is a GL, one of the fields of By Order Of is mandatory | ||||||||||||||||||
| BY_ORDER_OF3 | Alphanumeric (35) | No | By Order Of. For Incoming FT, if Credit Account is A GL, one of the fields of By Order Of is mandatory. For Outgoing/Internal FT, if Debit Account is a GL, one of the fields of By Order Of is mandatory | ||||||||||||||||||
| BY_ORDER_OF4 | Alphanumeric (35) | No | By Order of. For Incoming FT, if Credit Account is A GL, one of the fields of By Order Of is mandatory. For Outgoing/Internal FT, if Debit Account is a GL, one of the fields of By Order Of is mandatory | ||||||||||||||||||
| ULTIMATE_BEN1 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. For Incoming/Internal FT, if Credit Account is a GL, one of the fields of Ultimate Beneficiary is mandatory. For Outgoing FT, if Credit Account is a Nostro Account and it is a Customer Transfer, one of the fields of Ultimate Beneficiary is mandatory | |||||||||||||||||
| ULTIMATE_BEN2 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. For Incoming/Internal FT, if Credit Account is a GL, one of the fields of Ultimate Beneficiary is mandatory. For Outgoing FT, if Credit Account is a Nostro Account and it is a Customer Transfer, one of the fields of Ultimate Beneficiary is mandatory | |||||||||||||||||
| ULTIMATE_BEN3 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. For Incoming/Internal FT, if Credit Account is a GL, one of the fields of Ultimate Beneficiary is mandatory. For Outgoing FT, if Credit Account is a Nostro Account and it is a Customer Transfer, one of the fields of Ultimate Beneficiary is mandatory | |||||||||||||||||
| ULTIMATE_BEN4 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. For Incoming/Internal FT, if Credit Account is a GL, one of the fields of Ultimate Beneficiary is mandatory. For Outgoing FT, if Credit Account is a Nostro Account and it is a Customer Transfer, one of the fields of Ultimate Beneficiary is mandatory | |||||||||||||||||
| ULTIMATE_BEN5 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. For Incoming/Internal FT, if Credit Account is a GL, one of the fields of Ultimate Beneficiary is mandatory. For Outgoing FT, if Credit Account is a Nostro Account and it is a Customer Transfer, one of the fields of Ultimate Beneficiary is mandatory | |||||||||||||||||
| INT_REIM_INST1 | Alphanumeric (35) | No | 55 | Reimbursement Institution | |||||||||||||||||
| INT_REIM_INST2 | Alphanumeric (35) | No | 55 | Reimbursement Institution | |||||||||||||||||
| INT_REIM_INST3 | Alphanumeric (35) | No | 55 | Reimbursement Institution | |||||||||||||||||
| INT_REIM_INST4 | Alphanumeric (35) | No | 55 | Reimbursement Institution | |||||||||||||||||
| INT_REIM_INST5 | Alphanumeric (35) | No | 55 | Reimbursement Institution | |||||||||||||||||
| INTERMEDIARY1 | Alphanumeric (35) | No | 56 | Intermediary | |||||||||||||||||
| INTERMEDIARY2 | Alphanumeric (35) | No | 56 | Intermediary | |||||||||||||||||
| INTERMEDIARY3 | Alphanumeric (35) | No | 56 | Intermediary | |||||||||||||||||
| INTERMEDIARY4 | Alphanumeric 35) | No | 56 | Intermediary | |||||||||||||||||
| INTERMEDIARY5 | Alphanumeric (35) | No | 56 | Intermediary | |||||||||||||||||
| RECVR_CORRES1 | Alphanumeric (35) | No | Receiver correspondent | ||||||||||||||||||
| RECVR_CORRES2 | Alphanumeric (35) | No | Receiver correspondent | ||||||||||||||||||
| RECVR_CORRES3 | Alphanumeric (35) | No | Receiver correspondent | ||||||||||||||||||
| RECVR_CORRES4 | Alphanumeric (35) | No | Receiver correspondent | ||||||||||||||||||
| RECVR_CORRES5 | Alphanumeric (35) | No | Receiver Correspondent | ||||||||||||||||||
| ACC_WITH_INST1 | Alphanumeric (35) | No | 57 | Account With Institution. Mandatory if cover is required | |||||||||||||||||
| ACC_WITH_INST2 | Alphanumeric (35) | No | 57 | Account With Institution. Mandatory if cover is required | |||||||||||||||||
| ACC_WITH_INST3 | Alphanumeric (35) | No | 57 | Account With Institution. Mandatory if cover is required | |||||||||||||||||
| ACC_WITH_INST4 | Alphanumeric (35) | No | 57 | Account With Institution. Mandatory if cover is required | |||||||||||||||||
| ACC_WITH_INST5 | Alphanumeric (35) | No | 57 | Account With Institution. Mandatory if cover is required | |||||||||||||||||
| SNDR_TO_RECVR_INFO1 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| SNDR_TO_RECVR_INFO2 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| SNDR_TO_RECVR_INFO3 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| SNDR_TO_RECVR_INFO4 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| SNDR_TO_RECVR_INFO5 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| SNDR_TO_RECVR_INFO6 | Alphanumeric (35) | No | 72 | Sender Receiver Information | |||||||||||||||||
| PAYMENT_DETAILS1 | Alphanumeric (35) | No | 70 | Payment Details | |||||||||||||||||
| PAYMENT_DETAILS2 | Alphanumeric (35) | No | 70 | Payment Details | |||||||||||||||||
| PAYMENT_DETAILS3 | Alphanumeric (35) | No | 70 | Payment Details | |||||||||||||||||
| PAYMENT_DETAILS4 | Alphanumeric (35) | No | 70 | Payment Details | |||||||||||||||||
| ORDERING_INST1 | Alphanumeric (35) | No | 52 | Ordering Institution | |||||||||||||||||
| ORDERING_INST2 | Alphanumeric (35) | No | 52 | Ordering Institution | |||||||||||||||||
| ORDERING_INST3 | Alphanumeric (35) | No | 52 | Ordering Institution | |||||||||||||||||
| ORDERING_INST4 | Alphanumeric (35) | No | 52 | Ordering Institution | |||||||||||||||||
| ORDERING_INST5 | Alphanumeric (35) | No | 52 | Ordering Institution | |||||||||||||||||
| BENEFICIARY_INST1 | Alphanumeric (35) | No | 58 | Beneficiary Institution | |||||||||||||||||
| BENEFICIARY_INST2 | Alphanumeric (35) | No | 58 | Beneficiary Institution | |||||||||||||||||
| BENEFICIARY_INST3 | Alphanumeric (35) | No | 58 | Beneficiary Institution | |||||||||||||||||
| BENEFICIARY_INST4 | Alphanumeric (35) | No | 58 | Beneficiary Institution | |||||||||||||||||
| BENEFICIARY_INST5 | Alphanumeric (35) | No | 58 | Beneficiary Institution | |||||||||||||||||
| UPLOAD_STATUS | 1 Character | Yes | U | Upload Status. Populate with Null at the time of Upload. | After Upload, H-Contract Put on Hold | U- Unauthorised A-Authorised | |||||||||||||||
| APPLY_ICCF | 1 Character | Yes | Y | ICCF Pickup Required | Y- ICCF Pickup Required | N-ICCF Pickup Not Required | Cannot be Y when PASS_ACC_ENTRIES is N or APPLY_SETTLEMENTS is N | ||||||||||||||
| CHARGE_WHOM | 1 Character | Yes | Whom to Charge | O- Rem - All Chgs | B- Ben - All Chgs | U- Rem - Our Chgs | |||||||||||||||
| APPLY_TAX | 1 Character | Yes | Tax Pickup Required | Y-Tax Pickup Required | N-Tax Pickup Not Required. Should be N when PASS_ACC_ENTRIES is N | ||||||||||||||||
| APPLY_SETTLEMENTS | 1 Character | Yes | Y | Settlement Pickup Required | U-Populate Beneficiary Institution | N-Settlement Pickup Not Required | D- Don’t Populate intermediary reimbursement institution, intermediary, receiver correspondent, sender receiver info, ordering institution, beneficiary institution | Y-Populate Settlement | |||||||||||||
| PASS_ACC_ENTRIES | 1 Character | Yes | Y | Pass Accounting Entries | Y-Pass Accounting Entries | N-Don’t Pass Accounting Entries | |||||||||||||||
| INTERNAL_REMARKS | Alphanumeric (255) | No | Remarks. | ||||||||||||||||||
| PRODUCT_CODE | Alphanumeric (4) | Yes | N | Valid Product Code | |||||||||||||||||
| LCY_EQUIVALENT | Number (22,3) | Yes | Y | Local Currency Equivalent | |||||||||||||||||
| ERI_CCY | Alphanumeric (3) | No | Y | Euro Re-denomination Currency | |||||||||||||||||
| ERI_AMOUNT | Number (22,3) | No | Y | Euro Re-denomination Amount | |||||||||||||||||
| RECEIVER | Alphanumeric (11) | Yes | Receiver for Settlements. | ||||||||||||||||||
| UPLOAD_MSG_TYPE | Alphanumeric (3) | No |
7.6.3.2 CSTBS_EXT_CONTRACT_STAT
This is the ‘status driving’ table into which details of uploaded contracts are written, for all modules of Oracle FLEXCUBE. Each single row represents an uploaded contract. The system will update certain columns such as the import status automatically, after the upload is completed.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | |||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Valid Branch Code | |||||||||
| SOURCE | Alphanumeric (15) | Yes | Source Code – Primary Key | |||||||||
| PRODUCT_CODE | Alphanumeric (4) | Yes | Product Code | |||||||||
| COUNTERPARTY | Alphanumeric (9) | Yes | Customer for the contract | |||||||||
| EXTERNAL_INIT_Date | Date | No | Oracle System Initiation Date | |||||||||
| MODULE | Alphanumeric (2) | Yes | Module Code. Should be FT | |||||||||
| EXTERNAL_REF_NO | Alphanumeric (16) | Yes | Source Reference. Should be equal to Source Ref on FTTB_UPLOAD_MASTER – Primary Key | |||||||||
| IMPORT_STATUS | 1 Character | Yes | U | Upload Status. Should be U. U-Unprocessed | Y-Processed. E – Error & Rejected | |||||||
| CONTRACT_REF_NO | Alphanumeric (16) | No | Oracle FLEXCUBE Will populate with Actual Contract Ref Number | |||||||||
| POST_IMPORT_STATUS | 1 Character | No | Status of contract after upload. H- Hold, U-Unauthorized, A-Authorized | |||||||||
| EXPORT_STATUS | 1 Character | No | ||||||||||
| USER_ID | Alphanumeric (12) | Yes | Valid Oracle FLEXCUBE User ID with sufficient permissions to upload |
7.6.3.3 ISTBS_UPLOAD_CONTRACTIS
This table contains details of settlement related information for each component (which is debited/ credited to a customer or nostro type of account) of each of the uploaded contracts
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code – Same as that of FTTB_UPLOAD_MASTER | ||||||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Same As that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||||
| AMOUNT_TAG | Alphanumeric (20) | Yes | Amount Tag Name – Name used to identify each customer account/nostro entry within the Funds transfer Transaction – Primary Key | ||||||||||||
| TAG_CCY | Alphanumeric (3) | Yes | Currency of Amount Tag. Should be a valid currency code and should relate to the component Definition. | ||||||||||||
| AMT_IN_TAG_CCY | Number(22,3) | Yes | Amount in Amount Tag currency | ||||||||||||
| ACC_BRANCH | Alphanumeric (3) | Yes | Branch to which the account belongs | ||||||||||||
| ACCOUNT | Alphanumeric (20) | Yes | Valid account to which accounting entry is to be posted. | ||||||||||||
| ACC_CCY | Alphanumeric (3) | Yes | Currency of the account | ||||||||||||
| AMT_IN_ACC_CCY | Number (22,3) | Yes | Amount in account currency | ||||||||||||
| EX_RATE | Number (14,7) | Yes | Exchange rate between tag currency and account currency | ||||||||||||
| PAYMENT_BY | 1 Character | Yes | Payment Method | M-Message | I-Instrument | C-Clearing | |||||||||
| INSTRUMENT_TYPE | Alphanumeric (15) | No | Instrument Type | MCK – Manager’s Check, | CHECK - Check, | DRAFT – Demand Draft | |||||||||
| INSTRUMENT_NO | Alphanumeric (16) | No | Instrument Number | ||||||||||||
| COVER_REQUIRED | 1 Character | Yes | N | Cover Required | Y- Cover Required | N- Cover Not Required | |||||||||
| VALUE_Date | Date | Yes | Value Date of the transaction | ||||||||||||
| CHARGES_DETAILS | 1 Character | No | Charges Details | ||||||||||||
| OUR_CORRESPONDENT | Alphanumeric (9) | No | Our Correspondent. Should be Null if payment is by ‘Instrument’ | ||||||||||||
| RECEIVER | Alphanumeric (11) | No | Should be a Valid Swift Code / Customer number, if cover is required and Receiver is NOT NULL | ||||||||||||
| INT_REIM_INST1 | Alphanumeric (35) | No | 55 | Intermediate Reimbursement Institution | |||||||||||
| INT_REIM_INST2 | Alphanumeric (35) | No | 55 | Intermediate Reimbursement Institution | |||||||||||
| INT_REIM_INST3 | Alphanumeric (35) | No | 55 | Intermediate Reimbursement Institution | |||||||||||
| INT_REIM_INST4 | Alphanumeric (35) | No | 55 | Intermediate Reimbursement Institution | |||||||||||
| INT_REIM_INST5 | Alphanumeric (35) | No | 55 | Intermediate Reimbursement Institution | |||||||||||
| RCVR_CORRESP1 | Alphanumeric (35) | No | 54 | Receiver Correspondent | |||||||||||
| RCVR_CORRESP2 | Alphanumeric (35) | No | 54 | Receiver Correspondent | |||||||||||
| RCVR_CORRESP3 | Alphanumeric (35) | No | 54 | Receiver Correspondent | |||||||||||
| RCVR_CORRESP4 | Alphanumeric (35) | No | 54 | Receiver Correspondent | |||||||||||
| RCVR_CORRESP5 | Alphanumeric (35) | No | 54 | Receiver Correspondent | |||||||||||
| INTERMEDIARY1 | Alphanumeric (35) | No | 56 | Intermediary. For Payment by = ‘I’ or Cover Required = Y should not be present. | |||||||||||
| INTERMEDIARY2 | Alphanumeric (35) | No | 56 | Intermediary. For Payment by = ‘I’ or Cover Required = Y should not be present. | |||||||||||
| INTERMEDIARY3 | Alphanumeric (35) | No | 56 | Intermediary. For Payment by = ‘I’ or Cover Required = Y should not be present. | |||||||||||
| INTERMEDIARY4 | Alphanumeric (35) | No | 56 | Intermediary. For Payment by = ‘I’ or Cover Required = Y should not be present. | |||||||||||
| INTERMEDIARY5 | Alphanumeric (35) | No | 56 | Intermediary. For Payment by = ‘I’ or Cover Required = Y should not be present. | |||||||||||
| ACC_WITH_INSTN1 | Alphanumeric (35) | No | 57 | Account With Institution. If Cover Required = Y at least one Acc With Institution should be present | |||||||||||
| ACC_WITH_INSTN2 | Alphanumeric (35) | No | 57 | Account With Institution. If Cover Required = Y at least one Acc With Institution should be present | |||||||||||
| ACC_WITH_INSTN3 | Alphanumeric (35) | No | 57 | Account With Institution. If Cover Required = Y at least one Acc With Institution should be present | |||||||||||
| ACC_WITH_INSTN4 | Alphanumeric (35) | No | 57 | Account With Institution. If Cover Required = Y at least one Acc With Institution should be present | |||||||||||
| ACC_WITH_INSTN5 | Alphanumeric (35) | No | 57 | Account With Institution. If Cover Required = Y at least one Acc With Institution should be present | |||||||||||
| PAYMENT_DETAILS1 | Alphanumeric (35) | No | 70 | Payment Details. Not required for FT. Always Null | |||||||||||
| PAYMENT_DETAILS2 | Alphanumeric (35) | No | 70 | Payment Details. Not required for FT. Always Null | |||||||||||
| PAYMENT_DETAILS3 | Alphanumeric (35) | No | 70 | Payment Details. Not required for FT. Always NULL | |||||||||||
| PAYMENT_DETAILS4 | Alphanumeric (35) | No | 70 | Payment Details. Not required for FT. Always NULL | |||||||||||
| SNDR_TO_RCVR_INFO1 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| SNDR_TO_RCVR_INFO2 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| SNDR_TO_RCVR_INFO3 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| SNDR_TO_RCVR_INFO4 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| SNDR_TO_RCVR_INFO5 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| SNDR_TO_RCVR_INFO6 | Alphanumeric (35) | No | 72 | Sender to Receiver Information. Should follow the SWIFT standards of Sender Receiver Information | |||||||||||
| ORDERING_INSTITUTION1 | Alphanumeric (35) | No | 52 | Ordering Institution. Should be present only for Customer Transfers | |||||||||||
| ORDERING_INSTITUTION2 | Alphanumeric (35) | No | 52 | Ordering Institution. Should be present only for Customer Transfers | |||||||||||
| ORDERING_INSTITUTION3 | Alphanumeric (35) | No | 52 | Ordering Institution. Should be present only for Customer Transfers | |||||||||||
| ORDERING_INSTITUTION4 | Alphanumeric (35) | No | 52 | Ordering Institution. Should be present only for Customer Transfers | |||||||||||
| ORDERING_INSTITUTION5 | Alphanumeric (35) | No | 52 | Ordering Institution. Should be present only for Customer Transfers | |||||||||||
| ORDERING_CUSTOMER1 | Alphanumeric (35) | No | 50 | Ordering Customer | |||||||||||
| ORDERING_CUSTOMER2 | Alphanumeric (35) | No | 50 | Ordering Customer | |||||||||||
| ORDERING_CUSTOMER3 | Alphanumeric (35) | No | 50 | Ordering Customer | |||||||||||
| ORDERING_CUSTOMER4 | Alphanumeric (35) | No | 50 | Ordering Customer | |||||||||||
| ORDERING_CUSTOMER5 | Alphanumeric (35) | No | 50 | Ordering Customer | |||||||||||
| BENEF_INSTITUTION1 | Alphanumeric (35) | No | 58 | Beneficiary Institution. Not allowed for Module FT. Should always be NULL. | |||||||||||
| BENEF_INSTITUTION2 | Alphanumeric (35) | No | 58 | Beneficiary Institution. Not allowed for Module FT. Should always be NULL. | |||||||||||
| BENEF_INSTITUTION3 | Alphanumeric (35) | No | 58 | Beneficiary Institution. Not allowed for Module FT. Should always be NULL. | |||||||||||
| BENEF_INSTITUTION4 | Alphanumeric (35) | No | 58 | Beneficiary Institution. Not allowed for Module FT. Should always be NULL. | |||||||||||
| BENEF_INSTITUTION5 | Alphanumeric (35) | No | 58 | Beneficiary Institution. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ULT_BENEFICIARY1 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ULT_BENEFICIARY2 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ULT_BENEFICIARY3 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ULT_BENEFICIARY4 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ULT_BENEFICIARY5 | Alphanumeric (35) | No | 59 | Ultimate Beneficiary. Not allowed for Module FT. Should always be NULL. | |||||||||||
| ERI_CCY | Alphanumeric (3) | No | Euro Re-denomination Currency | ||||||||||||
| ERI_AMOUNT | Number (22,3) | No | Amount in ERI Currency | ||||||||||||
7.6.3.4 CFTBS_UPLOAD_CHARGE
This table contains the details of charges applicable for funds transfer contracts. When the components in this table are validated, the amount of the charge and the waiver is updated. Through this table you can alter the default amount of charge or waive charges totally.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code – Same as that of FTTB_UPLOAD_MASTER | ||||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||
| COMPONENT | Alphanumeric (10) | Yes | Component Name or Charge Name should be defined at product level – Primary Key | ||||||||||
| WAIVER | 1 Character | Yes | N | Charge Waive | Y-Waived | N-Not Waived | |||||||
| AMOUNT | Number (22,3) | Yes | Amount in Tag Currency | ||||||||||
| ACC_AMT | Number (22,3) | No | |||||||||||
| ACC_CCY | Alphanumeric (3) | No |
7.6.3.5 TATBS_UPLOAD_RULE
This table contains the details of tax applicable for funds transfer contracts. Through this you can waive the default application of tax.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code – Same as that of FTTB_UPLOAD_MASTER | ||||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||||
| RULE | Alphanumeric (10) | Yes | Tax Rule – Should be defined at the product level - Primary Key | ||||||||||
| WAIVER | 1 Character | Yes | N | Tax Waive | Y-Waived | N-Not Waived |
7.6.3.6 MITBS_UPLOAD_CONTRACT_MAPPING
This table contains the MIS details for the uploaded FT contracts.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | |||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Should be the same as that of FTTB_UPLOAD_MASTER – Primary Key | |||||||||||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Should be same as that of FTTB_UPLOAD_MASTER – Primary Key | |||||||||||||||||
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code – Same as that of FTTB_UPLOAD_MASTER | |||||||||||||||||
| PRODUCT | Alphanumeric (4) | Yes | Product Code – Should be same as that of FTTB_UPLOAD_MASTER | |||||||||||||||||
| CUSTOMER | Alphanumeric (9) | Yes | Customer of the contract | |||||||||||||||||
| RELATED_ACCOUNT | Alphanumeric (20) | No | Oracle FLEXCUBE Account Number for MIS purposes | |||||||||||||||||
| RELATED_REF | Alphanumeric (16) | No | Oracle FLEXCUBE Transaction Reference Number for MIS Purposes | |||||||||||||||||
| CCY | Alphanumeric (3) | Yes | Default from Funds Transfer Contract | Currency Code of the contract | ||||||||||||||||
| MIS_HEAD | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Head | |||||||||||||||||
| POOL_CODE | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Pool Code | |||||||||||||||||
| RATE_FLAG | 1 Character | No | Rate Flag From Pool Code or Contract | P- Pool Code | R-Contract | |||||||||||||||
| REF_RATE | Number (14,7) | No | Oracle FLEXCUBE MIS Refinancing Rate | |||||||||||||||||
| CALC_METHOD | 1 Character | No | Calculation Methods: | 30(Euro)/360 | 30(US)/360 | Actual/360 | 30(Euro)/365 | 30(US)/365 | Actual/365 | 30(Euro)/Actual | 30(US)/Actual | Actual/Actual | ||||||||
| MIS_GROUP | Alphanumeric (12) | No | Oracle FLEXCUBE MIS Group Code | |||||||||||||||||
| MIS_GROUP_TXN | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Group Transaction | |||||||||||||||||
| MIS_GROUP_COMP | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Group Composite | |||||||||||||||||
| COMP_MIS_1 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 1 | |||||||||||||||||
| COMP_MIS_2 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 2 | |||||||||||||||||
| COMP_MIS_3 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 3 | |||||||||||||||||
| COMP_MIS_4 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 4 | |||||||||||||||||
| COMP_MIS_5 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 5 | |||||||||||||||||
| COMP_MIS_6 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 6 | |||||||||||||||||
| COMP_MIS_7 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 7 | |||||||||||||||||
| COMP_MIS_8 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 8 | |||||||||||||||||
| COMP_MIS_9 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 9 | |||||||||||||||||
| COMP_MIS_10 | Alphanumeric (9) | No | Oracle FLEXCUBE Composite MIS Code 10 | |||||||||||||||||
| TXN_MIS_1 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 1 | |||||||||||||||||
| TXN_MIS_2 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 2 | |||||||||||||||||
| TXN_MIS_3 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 3 | |||||||||||||||||
| TXN_MIS_4 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 4 | |||||||||||||||||
| TXN_MIS_5 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 5 | |||||||||||||||||
| TXN_MIS_6 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 6 | |||||||||||||||||
| TXN_MIS_7 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 7 | |||||||||||||||||
| TXN_MIS_8 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 8 | |||||||||||||||||
| TXN_MIS_9 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 9 | |||||||||||||||||
| TXN_MIS_10 | Alphanumeric (9) | No | Oracle FLEXCUBE Transaction MIS Code 10 | |||||||||||||||||
| COST_CODE1 | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Cost Code 1 | |||||||||||||||||
| COST_CODE2 | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Cost Code 2 | |||||||||||||||||
| COST_CODE3 | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Cost Code 3 | |||||||||||||||||
| COST_CODE4 | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Cost Code 4 | |||||||||||||||||
| COST_CODE5 | Alphanumeric (9) | No | Oracle FLEXCUBE MIS Cost Code 5 | |||||||||||||||||
| REF_SPREAD | Number(10,5) | No | Floating Rate Spread for MIS | |||||||||||||||||
| REF_RATE_TYPE | Alphanumeric (1) | No | Rate Type for MIS | X – Fixed Rate | L - Floating Rate | |||||||||||||||
| REF_RATE_CODE | Alphanumeric (10) | No | Floating Rate Code for MIS. Should be a valid Floating Rate Code |
7.6.3.7 CSTBS_UPLOAD_CONTRACT_UDF
This table contains the User Defined Field (UDF) details for the uploaded funds transfer contracts.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code – Same as that of FTTB_UPLOAD_MASTER | ||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Same as that of FTTB_UPLOAD_MASTER – Primary Key | ||||||||
| FIELD_NAME | Alphanumeric (105) | Yes | Valid Field Name Maintained for the Product – Primary Key | ||||||||
| FIELD_VALUE | Alphanumeric (150) | Yes | Value For User Defined Field. Mandatory if maintained as ‘Mandatory’. Unique if maintained as ‘Unique’. |
7.6.3.8 FTTBS_UPLOAD_EXCEPTION
This table is updated by Oracle FLEXCUBE in case of errors encountered in respect of uploaded contracts, during the upload. As soon as Oracle FLEXCUBE encounters the first error in respect of a record, it is logged in this table, and the Upload function proceeds with the next record.
Only one error is logged in the Exception Table in respect of a single record.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code | ||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Primary Key | ||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Primary Key | ||||||||
| SEQUENCE_NO | Alphanumeric (16) | Yes | Sequence Number of error within the source reference –Primary Key | ||||||||
| ERROR_CODE | Alphanumeric (11) | Yes | Oracle FLEXCUBE Error Code | ||||||||
| ERROR_CODE_PARAMS | Alphanumeric (128) | No | Parameters responsible for the error | ||||||||
| ERROR_MESSAGE | Alphanumeric (255) | Yes | Derived Oracle FLEXCUBE Error Message with parameters substituted. |
7.6.3.9 FTTBS_UPLOAD_LOG
This table is updated by Oracle FLEXCUBE after successful completion of the entire Upload process.
| Column Name | Data Type | Mandatory | SWIFT Field | Default Value | Description | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| BRANCH_CODE | Alphanumeric (3) | Yes | Branch Code | ||||||||||||
| SOURCE_REF | Alphanumeric (16) | Yes | Source Reference – Primary Key | ||||||||||||
| SOURCE_CODE | Alphanumeric (15) | Yes | Source Code – Primary Key | ||||||||||||
| STATUS_FLAG | Alphanumeric (1) | Yes | Status | E – Error Not Uploaded | H – Contract Put on Hold | U – Contract Unauthorized | A – Contract Authorized. | ||||||||
| STATUS_TIMESTAMP | Date | Yes | Oracle System Date and Time of Latest Upload | ||||||||||||
| CONTRACT_REF_NO | Alphanumeric (16) | Yes | Oracle FLEXCUBE Transaction Reference | ||||||||||||
| OST_ID | Number | Yes | Unique Sequence Number for OST reference | ||||||||||||
| EVENT_STATUS | Alphanumeric (1) | Yes | U | OST Status. Oracle FLEXCUBE should populate with ‘U’ | |||||||||||
| EVENT_ERROR_STRING | Alphanumeric (255) | Yes | Oracle FLEXCUBE should populate with NULL | ||||||||||||
| PROCESS_ID | Alphanumeric (255) | Yes | Oracle FLEXCUBE should populate with NULL |
7.7 Uploading Contracts through Upload Master Screen
Once data is uploaded in the Upload Tables using files like; excel file, CSV file etc, system uses Upload Master screen (CVDUPLOD) to process data available in the FT upload tables and then creates an FT contract. To upload this data, you need to select FTDTRONL function ID in the Upload Master screen.
7.8 Upload of Incoming SWIFT Payment Messages
Oracle FLEXCUBE interfaces with an external system for uploading incoming SWIFT payment messages 103, 200 and 202. The external system writes the incoming messages into the Oracle FLEXCUBE message upload table, marking the status of the messages as ‘unprocessed’.
Oracle FLEXCUBE processes the incoming messages in the following manner:
- The uploaded messages are picked up for processing from the message upload table. The system checks to see that any messages other than 103, 200 or 202 are marked as rejected, with an appropriate reason for rejection. Also, the system checks that the BIC of the Sender of the message is a valid BIC in Oracle FLEXCUBE. If not, the message is rejected and a rejection reason logged.
- After performing the validations mentioned above, the system loads the incoming messages into the incoming messages queue, and marks the same in the upload table as ‘processed’.
7.9 Single Debit Multiple Credit (SDMC) for Bulk Payments
This section contains the following topics:
- Section 7.9.1, "Processing Single Debit Multiple Credit (SDMC) for Bulk Payments"
- Section 7.9.2, "Upload Process"
- Section 7.9.3, "Liquidation Process"
- Section 7.9.4, "Reversing a Liquidated Contract"
7.9.1 Processing Single Debit Multiple Credit (SDMC) for Bulk Payments
The external system parses the bulk payment file and populates the file level information and detailed records into Common Payment Gateway Browser in Oracle FLEXCUBE. The order of population is based on CPG detail data and then CPG file level information.
Oracle FLEXCUBE identifies the SDMC cross border transactions from the CPG file level information and CPG detail data stores based on the status of the field ‘Customer Consolidation Required’ and ‘Service Type' parameters. For SDMC cross border transactions, the option ‘Customer Consolidation Required’ is checked (Yes) and the ‘Service Type’ is ‘FT’.
For SDMC cross border transactions, the system consolidates the following payment attributes,
- Debit Account
- Debit Currency
- Debit Value Date
- File Reference Number
All the above payment attributes must have the same value for them to be eligible for consolidation.
You can identify the corresponding payment record for a file in CPG based on the file reference number in the uploaded file. For each file reference number, there can be multiple transactions.
When the payment records is populated in CPG detail, the status of the file at CPG file level is set as ‘W’ (Waiting). Once the records are populated in CPG detail, the external system updates the file status at CPG file level to ‘C’, (Completed).
7.9.2 Upload Process
The CPG upload process (CPG_UPLOAD) picks up the unprocessed cross border transfer records from CPG file level which comes for customer debit consolidation. For these records, the option ‘Customer Consolidation Required’ must be set to ‘Yes’ and the value of ‘Service Type’ set to ‘FT’. The payment records are picked up form CPG details by matching file reference number.
The CPG upload process creates FT payment contracts for all the payment records that are picked up from CPG details under products that are derived from the STP rule maintenance. However, the contracts will not be liquidated at this stage.
The CPG upload process will then process the subsequent file reference numbers.
7.9.3 Liquidation Process
Once the contracts are created, the liquidation process (PR_PROCESS_FT_CONS) creates a consolidated FT contract for the total transfer amount that is derived from each individual payment records. The consolidated FT contract is created using the internal FT product which is identified in the ‘Customer Consolidation Debit Product’ field in ‘Funds Transfer Branch Parameters’ screen.
The system passes the following accounting entries.
| Accounting Role | Dr/Cr | Amount | Currency | ||||
|---|---|---|---|---|---|---|---|
| REMITTER | Dr | Total Transfer amount | Debit Account Currency | ||||
| INTMD_SUSPENSE | Cr | Total Transfer amount | Debit Account Currency |
Note
The general ledger for ‘INTMD_SUSPENSE’ role is picked up from the 'Outgoing General Ledger' field in the ‘Funds Transfer Branch parameters’ maintenance.
This process liquidates the individual FT payment contracts created earlier for the payment records. Each payment contract is liquidated with the respective transfer amount.
Bank can collect the charges either at consolidated FT contract level or at individual FT contracts level.
The liquidation job process can be either parallel or sequential based on the parameter liquidation mode (LIQD_MODE).
- Parallel - Liquidation happens for multiple payments in parallel. If the bank applies charges at consolidated FT contract level and there is no remitter charge at the individual FT contracts level, then the liquidation mode can be configured as ‘Parallel’.
- Sequential - Liquidation of payments happens sequentially one after the other. If the bank applies charge at individual FT contracts level and there is no remitter charge at consolidated FT contract level, then the liquidation mode can be configured as ‘Sequential’.
This is a onetime setup and you cannot change it. By default, the liquidation mode is set to ‘Sequential’.
For sequential liquidation mode, the following accounting are passed.
| Accounting Role | Dr/Cr | Amount | Currency | ||||
|---|---|---|---|---|---|---|---|
| INTMD_SUSPENSE | Dr | Transfer Amount | Debit Account Currency | ||||
| NOSTRO GL | Cr | Transfer Amount | Transfer Currency | ||||
| REMITTER | Dr | Charge Amount | Charge Currency | ||||
| Charge Income GL | Cr | Charge Amount | Charge Currency |
In the field ‘Debit Consolidated Reference Number’ of the individual payment contracts, the system defaults the contract reference number of consolidated FT contract. However, the details of the customer and customer account will remain the same as those received in the incoming file.
The system will process the liquidation of each individual FT contracts for all the file reference numbers.
7.9.4 Reversing a Liquidated Contract
In case you reverse a liquidated individual contract described above, the system will pass the reversal accounting entries for the single customer debit FT contract also. However, the charges collected are not reversed.
The following accounting are passed on the reversal of individual FT contract:
| Accounting Role | Dr/Cr | Amount | Currency | ||||
|---|---|---|---|---|---|---|---|
| INTMD_SUSPENSE | Dr | (-1) * Transfer Amount | Debit Account Currency | ||||
| NOSTRO GL | Cr | (-1) * Transfer Amount | Transfer Currency |
The following accounting would be passed for the consolidated FT contract.
| Accounting Role | Dr/Cr | Amount | Currency | ||||
|---|---|---|---|---|---|---|---|
| REMITTER | Dr | (-1) * Transfer Amount of Reversed individual FT contract | Debit Account Currency | ||||
| INTMD_SUSPENSE | Cr | (-1) * Transfer Amount of Reversed individual FT contract | Debit Account Currency |