Skip Headers

Oracle Treasury User Guide
Release 12.1
Part Number E13426-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Open Interface Tables

Treasury Open Interface Tables

Open interface tables store information that is loaded into Treasury from external sources using Oracle SQL*Loader. You can use concurrent programs to import data such as bank balances, current market data (such as interest rates, foreign exchange rates, bond prices, and so on), deal details, and external exposure details from the open interface tables into Treasury.

Understanding Open Interface Tables

The Treasury open interface tables act as a staging area between your third-party application data and your Treasury data. You can use SQL*Loader to load third-party application data into Treasury. Once the data is loaded into the open interface tables, you can use concurrent programs to import the data from the open interface tables into Treasury tables. For a complete technical definition of all Treasury tables, see the eTRM.

Using SQL*Loader to Populate the Treasury Open Interface Tables

Use the Bank Statement Import or the Exposure Import windows, or SQL*Loader to load information into the Treasury open interface tables.

NOT NULL Columns

You must enter values for all NOT NULL columns in the interface tables to successfully save your data. If you do not enter a value in a NOT NULL column, then you cannot save the record in the interface table.

Required Columns

If you do not enter a valid value in a Required field, then the record will be rejected during import.

NULL Columns

Leave these columns null or the import process will fail. For example, during import the concurrent process updates the values of the columns to create a bank statement. Treasury does not support importing any data from these columns and you should leave these columns null for the records you load into the table.

Conditionally Required Columns

You have to enter values in a conditionally required column only if you have entered a value in another column that the conditionally required column is dependant on.

Optional Columns

You can use some columns in the interface tables to import additional information. Treasury imports the data that you load into these columns provided that the information passes the validation checks during the import process.

Internal ID Columns

These columns contain values that Treasury uses internally and that the user never sees. You can obtain these values only by looking in the Treasury tables. These ID columns correspond to other columns in the interface table. You do not need to enter a value for any of these ID columns if you enter a value in the corresponding column. For example, if you enter a BANK_NAME, you do not need to enter a BANK_ID.

If you enter values for both columns and the values do not match, the record will be rejected during import.

Reviewing and Correcting Import Errors

If you receive any errors when you import data from the open interface tables to Treasury, those errors are listed in the Deal Interface Summary window. You can use the Deal Interface Summary window to review and correct those errors. See: Deal Interface Summary.

Table Descriptions

The following section lists the columns in the Treasury Open Interface tables and indicates which columns require values to import information into Treasury.

Although columns are validated against columns in other tables, the tables have no foreign key relationships.

XTR_MARKET_DATA_INTERFACE

Use the XTR_MARKET_DATE_INTERFACE table to import market rates, such as interest rates, foreign exchange rates, and bond prices, from external sources into the XTR_MARKET_PRICES table. Rates are imported as current or historical rates based on their time stamps. There are several sample programs that are provided with Treasury that you can use as a basis for transferring data from your external sources into the XTR_MARKET_DATA_INTERFACE table, see: Data Exchange Programs.

Once market rates are imported into Treasury they can be viewed in the Current System Rates window, see: Current System Rates.

SOURCE

The source of the market data. If you have not already defined the market data source in the Current System Rates window, set it up before import.

Validation: The source must be an existing, authorized import source in the Current System Rates window.

Destination: XTR_MARKET_PRICES

EXTERNAL_REF_CODE

The code used to identify the source of the market data.

Validation: The code must be an existing, authorized code in the Current System Rates window.

Destination: XTR_MARKET_PRICES

DATETIME

The date and time that the rate was last updated.

Validation: The date and time must be in valid date and time formats.

Destination: XTR_MARKET_PRICES

BID, ASK, MID, and SPREAD

The bid, ask, mid, and spread quotes for the rate.

Validation: None.

Destination: XTR_MARKET_PRICES

RETURN_CODE

The return code for the transfer.

Validation: None.

Destination: XTR_MARKET_PRICES

REF_CODE

The reference code for the market rate.

Validation: The code must be an existing, authorized rate in the Current System Rates window.

Destination: XTR_MARKET_PRICES

CREATED_BY

The user who loaded the market rate into the interface table.

Validation: None.

Destination: XTR_MARKET_PRICES

CREATION_DATE

The date the market rate was first entered into the interface table.

Validation: None.

Destination: XTR_MARKET_PRICES

LAST_UPDATED_BY

The user who last updated the market rates.

Validation: None.

Destination: XTR_MARKET_PRICES

LAST_UPDATED_DATE

The date the market rates were last updated.

Validation: None.

Destination: XTR_MARKET_PRICES

LAST_UPDATED_LOGIN

Standard Who Column

Validation: None.

Destination: XTR_MARKET_PRICES

XTR_DEALS_INTERFACE

The XTR_DEALS_INTERFACE table is used to import deals from external sources into Treasury. The XTR_DEALS_INTERFACE table is capable of holding data for many different deal types; however, since each deal type uses a different set of values (for example, foreign exchange deals use two currencies, whereas intercompany funding deals use one), each deal type is imported into Treasury using a different deal transfer package.

The basic definition, validation, and destination for each column is documented in the following section.

For additional definition, validation, and destination information for a specific deal type, see the individual deal transfer packages:

ACCOUNT_NO_A

The first account number of the deal.

Validation: The account number must be a defined, authorized account for a company or counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ACCOUNT_NO_B

The second account number of the deal.

Validation: The account number must be a defined, authorized account for a company or counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ACTION_CODE

The action code for the deal.

Validation: The action code must be a valid action code for the deal type.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

AMOUNT_A

The first amount for the deal

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

AMOUNT_B

The second amount for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

AMOUNT_C

The second amount for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

AMOUNT_TYPE

The amount type for the deal.

Validation: The amount type must be a valid amount type for the deal type.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

AMOUNT_HCE

The deal amount in the reporting currency.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE_CATEGORY

The category for the descriptive flexfield attached to the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE1

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE2

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE3

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE4

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE5

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE6

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE7

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE8

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE9

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE10

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE11

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE12

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE13

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE14

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

ATTRIBUTE15

Flexfield attribute.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

BANK_CODE_FROM

The bank account number that the amounts are being transferred from.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information

BANK_CODE_TO

The bank account number that the amounts are being transferred to.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

BROKERAGE_CODE

The brokerage party of the deal.

Validation: The brokerage party must be a counterparty that is authorized for the deal type and currency of the deal.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

BROKERAGE_CURRENCY

The currency code for the brokerage amount of the deal.

Validation: The brokerage currency must be an authorized currency.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CLIENT_CODE

The client code for the deal.

Validation: The client code must be an authorized counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

COMMENTS

The internal comments for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

COMPANY_CODE

The company code for the deal.

Validation: The company code must be an authorized company.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CPARTY_ACCOUNT_NO

The counterparty account number.

Validation: The counterparty account number must be a defined, authorized account for a company or a counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CPARTY_CODE

The counterparty for the deal.

Validation: The counterparty code must be an authorized counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CPARTY_REF

The counterparty account reference code.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CREATED_BY

Standard who column.

Validation: None.

Destination: no destination.

CREATION_DATE

Standard who column.

Validation: None.

Destination: no destination.

CURRENCY_A

The first currency for the deal.

Validation: The currency must be an authorized currency.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

CURRENCY_B

The second currency for the deal.

Validation: The currency must be an authorized currency.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DATE_A

The first date for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DATE_B

The second date for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DEAL_LINKING_CODE

The deal linking code for the deal.

Validation: The deal linking code must be a defined deal linking code.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DEAL_TYPE

The Treasury deal type (for example, FXO) of the deal being imported. The values for many other columns in the XTR_DEALS_INTERFACE table are dependant on the value in this field.

Validation: The deal type must be an authorized, defined deal type in the Deal/Product Type window.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DEAL_SUBTYPE

The deal subtype for the deal.

Validation: The deal subtype must be an authorized subtype of the deal type for the deal.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DEALER_CODE

The dealer code for the deal.

Validation: The dealer code must be an authorized counterparty.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DUAL_AUTHORIZATION_BY

The vasodilator who must provide dual authorization for the deal.

Validation: The vasodilator must be a valid Treasury user.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

DUAL_AUTHORIZATION_ON

The date that the deal was authorized.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

EXPOSURE_TYPE

The exposure type for the deal.

Validation: The exposure type must be a defined, authorized exposure type.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

EXTERNAL_DEAL_ID

The deal ID from the external application.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

EXTERNAL_COMMENTS

The external comments for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

EXTERNAL_CPARTY_NO

The external counterparty for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

INTERNAL_TICKET_NO

The internal reference number for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

LAST_UPDATED_BY

Standard who column.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

LAST_UPDATED_DATE

Standard who column.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

LAST_UPDATE_LOGIN

Standard who column.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

LOAD_STATUS_CODE

The status of the deal being uploaded. For more information, see: Deal Import Statuses

Note: Note that if the status column is blank, it is treated as if the status is NEW.

Validation: None.

Destination: no destination.

LIMIT_CODE

The first limit that is applied to the deal.

Validation: The limit must be a defined, authorized limit.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

LIMIT_CODE_B

The first limit that is applied to the deal.

Validation: The limit must be a defined, authorized limit.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

MARKET_DATA_SET

The market data set used to revalue the deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_DEAL

Flag that indicates whether the deal is the mirror side of an intercompany funding deal (Y) or not (N).

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_DEAL_NUMBER

The deal ID for one side of a company to company intercompany funding deal. Combined with the MIRROR_TRANSACTION_NUMBER, this column forms a unique ID for each side of a mirror deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_TRANSACTION_NUMBER

The transaction number for one side of a company to company intercompany funding deal. Combined with the MIRROR_DEAL_NUMBER, this column forms a unique ID for each side of a mirror deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_LIMIT_CODE_FUND

The fund type limit code for the mirror side of an intercompany funding deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_LIMIT_CODE_INVEST

The invest type limit code for the mirror side of an intercompany funding deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_PORTFOLIO_CODE

The fund type limit code for the mirror side of an intercompany funding deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

MIRROR_PRODUCT_TYPE

The product type for the mirror side of an intercompany funding deal.

Validation: None.

Destination: XTR_INTERGROUP_TRANSFERS

NO_OF_DAYS

The number of days per year used to calculate interest on the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

OVERRIDE_LIMIT

A flag indicating whether you want to override the limits that are applied to the deal (Y), or not (N).

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

PORTFOLIO_CODE

The date the market rates were last updated.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

PRICING_MODEL

The pricing model for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

PRODUCT_TYPE

The product type for the deal.

Validation: The product type must be a defined, authorized product type for the deal type.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

RATE_A

The first rate for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

RATE_B

The second rate for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

RATE_C

The third rate for the deal.

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

SETTLE_ACTION_REQD

A flag indicating whether you want to settle the transaction in Treasury (YES), or not (NO).

Validation: None.

Destination: For most deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

STATUS_CODE

The status for the deal. The default status is CURRENT.

Validation: The status must be a valid status type for the deal type.

Destination: For all deals, XTR_DEALS. See individual deal transfer package descriptions for more information.

FX Deal Transfer Package

When you import foreign exchange spot and forward deals, the FX Deal Transfer package requires certain unique values.

The destination column is listed for some of those values. If no destination column is listed, then the destination column has the same name as the column in the XTR_DEALS_INTERFACE table. The unique values required for FX deals are as follows:

IG Deal Transfer Package

When you import intercompany transfer deals, the IG Deal Transfer package requires certain unique values.

The destination column is listed for some of those values. If no destination column is listed, then the destination column has the same name as the column in the XTR_DEALS_INTERFACE table. The unique values required for IG deals are as follows:

EXP Deal Transfer Package

When you import exposure deals, the EXP Deal Transfer package requires certain unique values.

The destination column is listed for some of those values. If no destination column is listed, then the destination column has the same name as the column in the XTR_EXPOSURE_TRANSACTIONS table. The unique values required for EXP deals are as follows:

NI Deal Transfer Package

When you import exposure deals, the NI Deal Transfer package requires certain unique values.

The destination column is listed for some of those values. If no destination column is listed, then the destination column has the same name as the column in the XTR_DEALS_INTERFACE table. The unique values required for NI deals are as follows:

XTR_TRANSACTION_INTERFACE

This table is designed to hold common transaction information and is a temporary staging area for deal data awaiting transfer to the actual transaction tables.

This table is used to store NI data.

AMOUNT_A

The size of the parcel.

Validation: The value must be greater than zero.

Destination: No mapping, gives number of parcels. The number here equals the number of times the row information is duplicated in the XTR_ROLLOVER_TRANSACTIONS table.

AMOUNT_B

The face value.

Validation: See table below.

Destination: For NI deals, the BALANCE_OUT column of XTR_ROLLOVER_TRANSACTIONS.

AMOUNT_C

The consideration.

Validation: See table below.

Destination: For NI deals, the INITIAL_FAIR_VALUE column of XTR_ROLLOVER_TRANSACTIONS.

AMOUNT_D

The interest.

Validation: See table below.

Destination: For NI deals, the INTEREST column of XTR_ROLLOVER_TRANSACTIONS.

DEAL_TYPE

The deal or instrument type.

Validation: The deal type must be an authorized, defined deal type in the Deal/Product Type window.

Destination: For NI deals, XTR_ROLLOVER_TRANSACTIONS.

EXTERNAL_DEAL_ID

Stores the external unique primary identifier for each deal.

Validation: None

Destination: For NI deals, XTR_ROLLOVER_TRANSACTIONS.

OPTION_A

Used for NI to indicate if a parcel is available for resale.

Validation: None

Destination: For NI deals, XTR_PARCEL_SPLITS.AVAILABLE_FOR_RESALE column of XTR_ROLLOVER_TRANSACTIONS.

TRANSACTION_NUMBER

The transaction number.

Validation: None

Destination: For NI deals, XTR_ROLLOVER_TRANSACTIONS.

VALUE_A

The serial number for NI.

Validation: Only relevant for Issue subtypes.

Destination: For NI deals, XTR_PARCEL_SPLITS.SERIAL_NUMBER column of XTR_ROLLOVER_TRANSACTIONS.

Validation

The system performs validation on the values in the XTR_TRANSACTION_INTERFACE table. The rules to decide which values are calculated and which values are imported are explained below.

If an interest value is specified, the interest rate must fall inside established tolerances defined by the interest override feature and the interest value is imported. If the interest value is not specified, then treasury calculates it.

If the Discount/Yield basis is Discount, then you must provide a face value.

If the Discount/Yield basis is Yield, then you must provide either face value or consideration. If one of these values is specified, then the other is calculated. If both these values are specified, the system produces an XTR_INV_CONSIDERATION error message.

The table below lists the results when each of the specific values are provided.

Face Value Consideration Interest Discount (D) or Yield (Y) Result
         
X X X Y Invalid
X X N/A Y Invalid
X N/A X Y If interest falls inside tolerance, face value and interest are imported and consideration is calculated.
X N/A N/A Y Face value is imported, consideration and interest are calculated.
N/A X X Y If interest falls inside tolerance, consideration and interest are imported and face value is calculated.
N/A X N/A Y Consideration is imported, face value and interest are calculated.
N/A N/A X Y Invalid
N/A N/A N/A Y Invalid
X X X D Invalid
X X N/A D Invalid
X N/A X D If interest falls inside tolerance, face value and interest are imported and consideration is calculated.
X N/A N/A D Face value is imported, consideration and interest are calculated.
N/A X X D Invalid
N/A X N/A D Invalid
N/A N/A X D Invalid
N/A N/A N/A D Invalid