2. Oracle FCUBS – FCPB Interface

Oracle FLEXCUBE Private Banking (FCPB) helps banks, financial institutions and wealth management service providers deliver advisory-driven solutions. The services include portfolio management, management of multiple asset-classes and advisory services.

The interface between FCUBS and FCPB enables the exchange of information related to day-to-day transactions and maintenances. FCUBS generates the required data for exchange in ASCII format and places the files in a predefined directory in the database server. This directory is accessible by FCPB.

The data types permitted in a handoff file are given below:

Data Type Description
Text Text fields can have alphanumeric characters as per the length specified for the field.
Numeric Numeric fields contain the following information: Amounts – This type of fields contain digits (0-9) and a decimal separator (.). The number of deci­mal digits is driven by the currency maintenance of FCUBS. Numbers without decimals – This type of fields contain digits (0-9) only. Numbers with decimals – This type of fields contain digits (0-9) and a decimal sep­arator (.). However, these digits do not represent amounts. For example, exchange rates of currencies may contain decimal.
Date Dates are represented in YYYYMMDD format. For instance, March 30, 2012 is represented as 20120330.

This chapter contains the following sections:

2.1 Details Handed off from FCUBS to Oracle FCPB

Oracle FLEXCUBE hands off the following details to Oracle FCPB:

Entity Action Frequency Core/Security
TD Account Creation Add On-line Core
TD Premature Withdrawal Add On-line Core
CASA Account Creation Add On-line Core
CIF Authorization/Modification/Closure Add On-line Core
Broker Data Add On-line Core
CASA Account Balances Add On-line Core
Segmentation Information Add On-line Core
TD Maturity with Interest Liquidation Add Batch Core
Daily Accrued Interest on TD Add Batch Core
Penalty on TD Add Batch Core
Loan and Liability Information Add Batch Core
Segmentation Status Information Add Batch Core

2.2 Interface Definition

This section contains the following topic:

2.2.1 Defining Interface Details for Oracle FCPB

In order to enable the interface, you need to define the interface details for Oracle FCPB in FCUBS. You can define the format details and properties of interface files using ‘Interface Definition’ screen. To invoke this screen, type ‘GIDIFTDF’ in the field at the top right corner of the Application tool bar and click the adjoining arrow button.

You need to define the interface details.

External System

Specify the code that identifies the external system. The interface details defined on this screen are applicable to the interface between FCUBS and the external system selected here.

For FCPB interface, you need to specify ‘FCPB’ as the external system.

Interface Code

Specify a unique code that identifies the interface between the above external system and FCUBS.

Interface Type

Select the interface type from the following options:

File Mask

Specify the file mask for the outgoing interface file. The system decides the name of the handoff file based on the file mask defined here.

Format Type

Select the message format type. Choose one of the following format types based on the data length:

If you select ‘Delimited’, the system enables the field ‘Delimiting Character’.

File Path

Specify the directory in which the interface file is stored.

For further details on the fields on ‘Interface Definition’ screen, refer to the section ‘Specifying Interface Definition Details’ under chapter ‘Generic Interface’ of Generic Interface user manual.

2.2.1.1 Defining Component Details

As part of interface definition, you need to capture specific component details for both handoff and upload. Click ‘Component Details’ button on the ‘Interface Definition’ screen.

You can capture the details of the header, body and footer under ‘Component Linkage’ section. Below that, under ‘Component Field Linkage’ section, you can define the details pertaining to table columns, field type, field length, field name and other relevant details.

For more details of the fields on this screen, refer the chapter ‘Generic Interface’ in the Generic Interface User Manual.

2.3 FATCA Interface between Oracle FCUBS and FCPB

As part of the GIDPRSIF batch, Oracle FCUBS hands off FATCA (Foreign Account Tax Compliance Act) related data to FCPB. This batch is part of the end of day operations. Once a customer gets upgraded as per the segmentation rules, FCUBS generates a handoff file to FCPB. Subsequently, the customers will receive an online notification.

The system generates the following handoff files during EOD operations:

The Customer Master File

This file contains the following information related to FATCA.

The interface code for Customer Master File (UBS_SEG_CUSTOMER.CSV) is IFCUSSEG

Directors File

Any field information related to 'Director' will form a part of the 'Directors' hand-off file. This file supports one-to-many relationships between customer and directors.

The interface code for Directors (UBS_SEG_AUTH_SIGN.CSV) is IFCUSCOR

Customer FATCA Classification File

This file contains FATCA classification details of the customers.

The interface code for Customer FATCA Classification file (UBS_SEG_CUST_FATCA.CSV) is IFFATCA

2.4 Online Notifications

FCUBS sends an online notification to Oracle FCPB at the following occasions:

Note

Online Notification for TD redemption through 1317 redemption screen is not supported.

Note

Notification will be fired only for segmented customer. That is when the segment status and segment code for the customer are not null.

Customer status cannot be modified to downgrade on the same day of upgrade.

The details of online notification processed from FCUBS are given below:

Instance Notification Code Operation Service XSD Name
TD account creation NOTIF_TD_T­DACC_NEW QuerycustAc­countDetails FCUBSAccSer­vice TD-NewAc­count-Notify-MSG.xsd
TD Account modification NOTIF_TD­MOD_PB QuerycustAc­countDetails FCUBSAccSer­vice TD-NewAc­count-Notify-MSG.xsd
TD prema­ture with­drawal NOTIF_I­CREDM QueryTDRe­demption FCUBSAccSer­vice IC-QueryTDRe­dem-Res-Full-MSG.xsd  ,IC-QueryTDRe­dem-Req-IO-MSG.xsd
TD rollover NOTIF_T­D_ROLLOVER QueryTDRollover FCUBSTDSer­vice ST-QueryT­DRollover-Req-IO-MSG.xsd, ST-QueryT­DRollover-Res-Full-MSG.xsd
CASA account creation NOTIF_­CA_CUSTAC­C_NEW QuerycustAc­countDetails FCUBSAccSer­vice CA-NewCus­tomerAccount-Notify-MSG.xsd
CIF authori­zation/ modifica­tion/ Clo­sure NOTIF_CO_CUSTOMER_­MOD QueryCustomer FCUBSCus­tomerService ST-ModifyCus­tomer-Notify-MSG.xsd
Broker cre­ation NOTIF_CO_CUSTOM­ER_NEW QueryCustomer FCUBSCus­tomerService ST-NewCus­tomer-Notify-MSG.xsd
CASA account balance change NOTI­FY_AC_BAL QuerycustAc­countDetails FCUBSAccSer­vice TD-AccStat-Notif.xsd
Customer creation NOTIF_P­B_CUSTOMER Customer crea­tion under ‘Bro­ker’ category    
Segmenta­tion code NOTIF_P­B_CUSTOMER     CUSTSEGCD
Net worth Amount NOTIF_P­B_CUSTOMER     NETWORTH
Net worth Currency NOTIF_P­B_CUSTOMER     NET­WORTHCCY
customer currency NOTIF_P­B_CUSTOMER     CUSTCCY
Segmenta­tion status NOTIF_P­B_CUSTOMER     CUSTSEGSTA­TUS

New tags CUSTSEGCD, CUSTSEGSTATUS, NETWORTH, NETWORTHCCY and CUSTCCY will be available in the notification XSD.

2.5 Interface Details for Data Handoff

FCUBS hands off the details of maintenances and transactions to Oracle FCPB for processing. You need to maintain separate interface details to enable handoff of the following details:

The maintenances required for enabling the data upload are discussed under the following headings.

This section contains the following topics:

2.5.1 Term Deposit Maturity with Interest Liquidation

On the maturity date of a term deposit, FCUBS hands off the term deposit maturity details to Oracle FCPB. In addition to that, the system also hands off the interest details after interest liquidation.

This handoff happens only for the customers whose segment code and segment status are defined in the system.

To enable the data handoff from FCUBS, you need to define the interface details as given in the following table:

Field Value
Interface Code STOIMDTL
Interface Type Outgoing
From System FCUBS
To System FCPB
Dateformat YYYYMMDD
File Mask /U/B/S/_/T/D/M/A/T$D$M$Y$h$m$s/E/N/D/./C/S/V
Whentorun Beginning of day
Frequency Daily
Character Set ASCII
Format Delimited
Delimiter ^
File Header record length 3 , Default value HDR
Body Record Length 36
File Footer record length 3, default value TLR
Numeric padding Left padding with Zeros
Text padding Left padding with spaces
Datalog Required Yes
CRC Required No
Triggering System

In ‘Component Details’ sub-screen, you need to maintain the following details:

Field Name Table Name Column Name Data Type Field Length Position
HEADER     VARCHAR2 3 1
AC_REF_NO TDVWS_TD_MA­TURITY_DETAILS AC_EN­TRY_SR_NO NUMBER 10 1
CUST_NO TDVWS_TD_MA­TURITY_DETAILS CUSTOM­ER_NO VARCHAR2 9 11
CUST_NAME TDVWS_TD_MA­TURITY_DETAILS CUSTOM­ER_NAME1 VARCHAR2 105 20
TD_ACC TDVWS_TD_MA­TURITY_DETAILS ACC VARCHAR2 20 125
ACC_­CLASS TDVWS_TD_MA­TURITY_DETAILS ACCOUNT_­CLASS VARCHAR2 6 145
TRN_DT TDVWS_TD_MA­TURITY_DETAILS TRN_DT DATE 10 151
LCY_AMOUNT TDVWS_TD_MA­TURITY_DETAILS AMOUNT NUMBER 22 161
DR_CR_ACC TDVWS_TD_MA­TURITY_DETAILS AC_NO VARCHAR2 20 183
AC_CCY TDVWS_TD_MA­TURITY_DETAILS AC_CCY VARCHAR2 3 203
REC_STAT TDVWS_TD_MA­TURITY_DETAILS RECORD_STAT VARCHAR2 1 206
TXN_­TYPE TDVWS_TD_MA­TURITY_DETAILS TRANSAC­TION_TYPE VARCHAR2 9 207
TRAILER     VARCHAR2 3 1

2.5.2 Daily Accrued Interest on Term Deposits

FCUBS hands off the details of the accrued interest on the term deposits to Oracle FCPB on a daily basis. This handoff happens only for the customers whose segment code and segment status are defined in the system.

Note

Discounted TD’s accrual record will not be sent to Oracle FCPB. Interest Liquidation for Discounted TD’s will be sent upfront in TD Maturity with interest liquidation file.

To enable handoff of this data from FCUBS, you need to define the interface details as given in the following table:

Field Value
Interface Code DLACCRNT
Interface Type Outgoing
From System FCUBS
To System FCPB
Dateformat YYYYMMDD
File Mask /M/K$D$M$Y$h$m$s/E/N/D
Whentorun End of Transaction input
Frequency Daily
Character Set ASCII
Format Delimited
Delimiter ^
File Header record length 3 , Default value HDR
Body Record Length 36
File Footer record length 3, default value TLR
Numeric padding Left padding with Zeros
Text padding Left padding with spaces
Datalog Required Yes
CRC Required No
Triggering System

In ‘Component Details’ sub-screen, you need to maintain the following details:

Field Name Table Name Column Name Data Type Field Length Position
CUSTNO ICVW_ACCRU­AL_DETAILS CUST_NO VARCHAR2 9 1
SHRTNM ICVW_ACCRU­AL_DETAILS SHORT_NAME VARCHAR2 20 10
ACC ICVW_ACCRU­AL_DETAILS ACC VARCHAR2 20 30
ACCL ICVW_ACCRU­AL_DETAILS ACCOUNT_­CLASS VARCHAR2 6 50
ACRAMT ICVW_ACCRU­AL_DETAILS ACCRUED_AMT NUMBER 22 56
ENTDT ICVW_ACCRU­AL_DETAILS ENT_DT VARCHAR2 10 78
CCY ICVW_ACCRU­AL_DETAILS CCY VARCHAR2 3 88
PROD ICVW_ACCRU­AL_DETAILS PROD VARCHAR2 4 91
HEADER     VARCHAR2 3 1
TRAILER     VARCHAR2 3 1

2.5.3 Penalty on Term Deposits

FCUBS hands off to Oracle FCPB the details of penalties applied on term deposits. This handoff happens only for the customers whose segment code and segment status are defined in the system.

To enable handoff of this data from FCUBS, you need to define the interface details as given in the following table:

Field Value
Interface Code STOIMDTL
Interface Type Outgoing
From System FCUBS
To System FCPB
Dateformat YYYYMMDD
File Mask /U/B/S/_/T/D/M/A/T$D$M$Y$h$m$s/E/N/D/./C/S/V
Whentorun Beginning of day
Frequency Daily
Character Set ASCII
Format Delimited
Delimiter ^
File Header record length 3 , Default value HDR
Body Record Length 36
File Footer record length 3, default value TLR
Numeric padding Left padding with Zeros
Text padding Left padding with spaces
Datalog Required Yes
CRC Required No
Triggering System

In ‘Component Details’ sub-screen, you need to maintain the following details:

Field Name Table Name Column Name Data Type Field Length Position
HEADER     VARCHAR2 3 1
AC_REF_NO TDVW_T­D_DE­TAILS AC_ENTRY_SR_NO NUMBER 10 1
CUST_NO TDVW_T­D_DE­TAILS CUSTOMER_NO VARCHAR2 9 11
CUST_NAME TDVW_T­D_DE­TAILS CUSTOM­ER_NAME1 VARCHAR2 105 20
TD_ACC TDVW_T­D_DE­TAILS ACC VARCHAR2 20 125
ACC_­CLASS TDVW_T­D_DE­TAILS ACCOUNT_CLASS VARCHAR2 6 145
TRN_DT TDVW_T­D_DE­TAILS TRN_DT DATE 10 151
LCY_AMOUNT TDVW_T­D_DE­TAILS AMOUNT NUMBER 22 161
DR_CR_ACC TDVW_T­D_DE­TAILS AC_NO VARCHAR2 20 183
AC_CCY TDVW_T­D_DE­TAILS AC_CCY VARCHAR2 3 203
REC_STAT TDVW_T­D_DE­TAILS RECORD_STAT VARCHAR2 1 206
TXN_TYPE TDVW_T­D_DE­TAILS TRANSACTION_­TYPE VARCHAR2 9 207
TRAILER     VARCHAR2 3 1

2.5.4 Loan and Liability Information

FCUBS hands off the details of loan accounts to Oracle FCPB. This handoff happens only for the customers whose segment code and segment status are defined in the system.

The handoff file contains the following details of the loan accounts maintained in FCUBS:

To enable handoff of this data from FCUBS, you need to define the interface details as given in the following table:

Field Value
Interface Code CLOLNDTL
Interface Type Outgoing
From System FCUBS
To System FCPB
Dateformat YYYYMMDD
File Mask /U/B/S/_/P/R/O/D/B/L$D$M$Y$h$m$s/E/N/D
Whentorun End of Transaction input
Frequency Daily
Character Set ASCII
Format Delimited
Delimiter ^
File Header record length 3 , Default value HDR
Body Record Length 36
File Footer record length 3, default value TLR
Numeric padding Left padding with Zeros
Text padding Left padding with spaces
Datalog Required Yes
CRC Required No
Triggering System

In ‘Component Details’ sub-screen, you need to maintain the following details:

Field Name Table Name Column Name Data Type Field Length Position
HEADER     VARCHAR2 3 1
CUSTID CLVW_LOAN_INT_DETAILS CUSTOMER_ID VARCHAR2 35 1
ACCNO CLVW_LOAN_INT_DETAILS ACCOUNT_NUM­BER VARCHAR2 35 2
PRODAC CLVW_LOAN_INT_DETAILS DR_PROD_AC VARCHAR2 20 36
PROD CLVW_LOAN_INT_DETAILS PRO­DUCT_CODE VARCHAR2 4 56
BOOKDT CLVW_LOAN_INT_DETAILS BOOK_DATE VARCHAR2 10 60
VALDT CLVW_LOAN_INT_DETAILS VALUE_DATE VARCHAR2 10 70
PRNOUT­AMT CLVW_LOAN_INT_DETAILS PRNOUTSTAND­ING NUMBER 22 80
CCY CLVW_LOAN_INT_DETAILS CURRENCY VARCHAR2 3 102
MATDT CLVW_LOAN_INT_DETAILS MATURITY_­DATE VARCHAR2 10 105
ACCSTAT CLVW_LOAN_INT_DETAILS ACCOUNT_STA­TUS VARCHAR2 1 115
ACSUB­TYP CLVW_LOAN_INT_DETAILS ACCT_SUB_­TYPE VARCHAR2 10 116
ACCBRN CLVW_LOAN_INT_DETAILS DR_ACC_BRN VARCHAR2 3 126
MICR CLVW_LOAN_INT_DETAILS MICR VARCHAR2 10 129
RSLDVAL CLVW_LOAN_INT_DETAILS RESOLVED_VALUE NUMBER 22 139
TENOR CLVW_LOAN_INT_DETAILS TENOR NUMBER 22 161
ACCRAMT CLVW_LOAN_INT_DETAILS ACCRAMT NUMBER 22 183
LTINACDT CLVW_LOAN_INT_DETAILS LAST_INT_AC­CR_DATE VARCHAR2 10 205
AMTSTLD CLVW_LOAN_INT_DETAILS AMTSTLD NUMBER 22 215
LTPMTDT CLVW_LOAN_INT_DETAILS LAST_PMT_DT VARCHAR2 10 237
ACTADD CLVW_LOAN_INT_DETAILS ACCT_ADDRESS VARCHAR2 10 247
HLDAMT CLVW_LOAN_INT_DETAILS HOLD_AMOUNT NUMBER 22 257
TRAILER     VARCHAR2 3 1

2.5.5 Term Deposit Rollover

FCUBS hands off the details of manual and automatic rollovers of term deposits to Oracle FCPB. This handoff happens only for the customers whose segment code and segment status are defined in the system.

The Notification contains the following details of the TD accounts maintained in FCUBS:

2.5.6 Segmentation Status Information

When a customer segmentation status is updated to ‘Upgraded’ as per the segmentation rules, FCUBS generates an End Of Day (EOD) and Beginning Of Day (BOD) files handoff for the corresponding customers and triggers the handoffs to FCPB. As a part of this interface, the following files will be generated whenever the customer segmentation status is upgraded:

FCUBS will generate outgoing files for the upgraded customers and the account details of the corresponding primary account holder will be handed off to FCPB.

FCUBS will generate outgoing files for the CASA accounts of the upgraded customers and the corresponding open accounts will be handed off to FCPB.

FCUBS will generate outgoing files for the CASA accounts and the balance of the upgraded customers. The balance details of the corresponding open accounts will be handed off to FCPB.

FCUBS will generate outgoing files for the Loan accounts and the balance of the upgraded customers. The active loans of the corresponding customers will be handed off to FCPB.

FCUBS will generate outgoing files for the TD accounts which underwent the following changes as of segmentation date:

The following details are sent to the FCPB:

FCUBS will generate outgoing files at BOD, for the TD accounts undergoing the following changes as of segmentation date:

The following TD details are sent to FCPB:

2.5.6.1 Segment Upgrade - File Formats

The formats of the above files are given below.

Customer File Format

The format of the customer file is given below.

FIELD NAME DESCRIPTION MANDATORY FCUBS COLUMN NAME TABLE NAME LENGTH
EXTER­NAL_ID External client id of customer YES EXT_REF_NO STTM_CUS­TOMER 20
FCPB CLI­ENT_ID FCPB Client ID of customer     NULL 10
SALUTA­TION Salutation YES CUSTOM­ER_PRE­FIX STT­M_CUST_PER­SONAL 30
FIRST_NAME First Name YES FIRST_NAME STT­M_CUST_PER­SONAL 105
MID­DLE_NAME Middle Name NO MID­DLE_NAME STT­M_CUST_PER­SONAL 105
LAST_NAME Last Name NO LAST_NAME STT­M_CUST_PER­SONAL 105
PHONE_RESI_COUN­TRY_CODE Telephone international country code NO TEL_IS­D_NO STT­M_CUST_PER­SONAL 10
PHONE_HOME Home Phone NO TELE­PHONE STT­M_CUST_PER­SONAL 105
MOBILE_ADDN_COUNTRY_CODE Mobile interna­tional country code NO MOB_IS­D_NO STT­M_CUST_PER­SONAL 10
PHONE_­MOBILE Mobile Phone NO MOBILE_­NUMBER STT­M_CUST_PER­SONAL 22
PHONE_OFC_COUN­TRY_CODE Office Tele­phone interna­tional country code NO   NULL 3
PHONE_WORK Work Phone NO E_TELE­PHONE STT­M_CUST_PRO­FESSIONAL 10
EMAIL Email NO E_MAIL STT­M_CUST_PER­SONAL 255
GENDER Gender:(M) Male, (F) Female YES SEX STT­M_CUST_PER­SONAL 1
DOB Date OF Birth NO DATE_OF_BIRTH STT­M_CUST_PER­SONAL 10
TAX_ID Income Tax ID YES TAX_ID STTM_CORP_DI­RECTORS 105
CLI­ENT_SEG Client Segment YES CUST_SEGMENT_CODE STTM_CUS­TOMER  
OCCUPA­TION Occupation YES DESIGNA­TION STT­MS_CUST_PRO­FESSIONAL 105
CLIENT_­TYPE Client IT type YES CUSTOM­ER_TYPE STTM_CUS­TOMER 1
HOME_BRANCH Branch code of the customer YES LOCAL_BRANCH STTM_CUS­TOMER 3
ACQ_DATE Acquisition Date NO   NULL  
BANKER Banker Code YES BANK_CODE STTM_BANK 4
ADDR_LINE1 Mailing Address 1 NO ADDRESS1 MST­M_CUST_AD­DRESS 105
ADDR_LINE2 Mailing Address 2 NO ADDRESS2 MST­M_CUST_AD­DRESS 105
ADDR_LINE3 Mailing Address 3 NO ADDRESS3 MST­M_CUST_AD­DRESS 105
STATE_­MAILING State of the Mailing address NO   NULL  
COUN­TRY_MAIL­ING Country of the mailing address NO COUNTRY MST­M_CUST_AD­DRESS 3
ZIP_MAIL­ING Zip code of the mailing address NO   NULL  
ADDR_LINE1 Home Address 1 YES ADDRESS_LINE1 STTM_CUS­TOMER 105
ADDR_LINE2 Home Address 2 YES ADDRESS_LINE3 STTM_CUS­TOMER 105
ADDR_LINE3 Home Address 3 YES ADDRESS_LINE2 STTM_CUS­TOMER 105
STATE_HOME State of the Home address NO   NULL  
COUN­TRY_HOME Country of the Home address YES COUNTRY STTM_CUS­TOMER 3
ZIP_HOME Zip code of the Home address NO   NULL  
ADDR_LINE1 Work Address 1 NO E_AD­DRESS1 STT­MS_CUST_PRO­FESSIONAL 105
ADDR_LINE2 Work Address 2 NO E_AD­DRESS2 STT­MS_CUST_PRO­FESSIONAL 105
ADDR_LINE3 Work Address 3 NO E_AD­DRESS3 STT­MS_CUST_PRO­FESSIONAL 105
STATE_WORK State of the work address NO   NULL  
COUN­TRY_WORK Country of the work address NO   NULL  
ZIP_WORK Zip code of the work address NO   NULL  
FAX­_HOME_COUNTRY_CODE FAX interna­tional country code NO FAX_IS­D_NO STT­M_CUST_PER­SONAL 10
FAX Fax Number NO FAX_NUM­BER STTM_CUS­TOMER 105
NATIONAL­ITY Nationality - If Customer type is "I" (Individ­ual) Nationality required to be provided If Customer type is "C" (Corporate) Country of Incorporation to be provided YES NATIONAL­ITY/INCORP_COUNTRY STTM_CUS­TOMER/STT­MS_CUST_CORPORATE 3
LANGUAGE Preferred lan­guage of the customer YES LAN­GUAGE STTM_CUS­TOMER 3
CCY_ID Customer Cur­rency YES PORTFO­LIO_C­CY_CODE SETM_PORTFO­LIO_MASTER 3
PREF_COMM_MODE Preferable communication mode YES DEFAULT_­MEDIA STTM_CUS­TOMER 12
CLIENT_­CATEGORY Client category YES CUSTOM­ER_CATE­GORY STTM_CUS­TOMER 10
CLIENT_­CLASSIFI­CATION Client Classifi­cation YES CUST_­CLASSIFI­CATION STTM_CUS­TOMER 20
MINOR_­FLAG Flag indicating if minor YES MINOR STT­M_CUST_PER­SONAL 1
BIRTH_PLACE Place of Birth NO PLACE_OF_BIRTH STT­M_CUST_PER­SONAL 100
COB_COUNTRY_ID Country of birth NO BIRTH_COUNTRY BIRTH_COUN­TRY 3
DOMICILE Domicile Coun­try details NO D_COUN­TRY STTM_CUS­TOMER 3
SIG­NIF_STY_IN_USA_YN Visited US in last 3 years   VST_US_PREV STT­M_CUST_PER­SONAL 1
POA FLAG Power of Attor­ney flag, If POA flag is "Y" FCPB should consider the holder details and update Sig­natory type in CRM_AU­THOR­IZED_SIGNATORY table as "PA" NO PA_IS­SUED STT­M_CUST_PER­SONAL 1
AUTH_SIGN_FNAME First name of the Power of Attorney Holder NO PA_HOLD­ER_NAME STT­M_CUST_PER­SONAL 105
AUTH_SIGN_LNAME Last name of the Power of Attorney Holder NO   NULL  
AUTH_SIGN_DOB Date of birth of the Power of Attorney Holder NO   NULL  
AUTH_SIGN_MOBILE Mobile interna­tional country code of the guardian/Power of Attorney Holder NO   NULL  
AUTH_SIGN_HOME Mobile number of the Power of Attorney Holder NO   NULL  
PH_HOME_COUN­TRY_CODE Telephone Code of home telephone num­ber of the Power of Attor­ney holder   PA_HOLD­ER_TEL_ISD STT­M_CUST_PER­SONAL 10
AUTH_SIGN_HOME Telephone number of the Power of Attor­ney holder NO PA_HOLD­ER_TEL_NO STT­M_CUST_PER­SONAL 20
AUTH_SIGN_TAX_ID Tax identifier of the Power of Attorney Holder NO   NULL 15
AUTH_SIGN_REL Relationship of the Power of Attorney Holder NO   NULL 5
AUTH_SIGN_TYPE Type of the Power of Attor­ney Holder NO   NULL 3
AUTH_SIGN_DOMI­CILE Country of the Power of Attor­ney Holder NO   NULL 20
NATIONALI­TY_COUN­TRY_CODE Nationality Country ID of the Power of Attorney holder   PA_HOLD­ER_NA­TIONALTY STT­M_CUST_PER­SONAL 3
AUTH_SIGN_AD­DRESS Address of the Power of Attor­ney holder   PA_HOLD­ER_ADDR STT­M_CUST_PER­SONAL 105
AUTH_SIGN_COUN­TRY Address Coun­try ID of the Power of Attor­ney holder   PA_HOLD­ER_AD­DR_COUNTRY STT­M_CUST_PER­SONAL 3
LOCATION Location code/details of cus­tomer NO   NULL  
EXT_PORT­FOLIO_ID UBS Invest­ment portfolio ID NO PORTFO­LIO_ID SETM_PORTFO­LIO_MASTER 16
REG_COUNTRY_ID Registered country for the corporate NO R_COUN­TRY STT­MS_CUST_COR­PORATE 3
INCORP_COUN­TRY_ID Incorporated country for the corporate NO INCORP_COUNTRY STT­MS_CUST_COR­PORATE 3
CUSTOM­ER_STA­TUS Status of the customer NO CIF_STA­TUS STTM_CUS­TOMER 20
UDF_NUM_1 UDF NUM 1 NO FIELD_VAL_1 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_NUM_2 UDF NUM 2 NO FIELD_VAL_2 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_NUM_3 UDF NUM 3 NO FIELD_VAL_3 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_NUM_4 UDF NUM 4 NO FIELD_VAL_4 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_NUM_5 UDF NUM 5 NO FIELD_VAL_5 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_TXT_1 Guardian name - It is manda­tory if minor flag is ‘Y’ NO LEGAL_GUARDIAN STT­M_CUST_PER­SONAL 105
UDF_TXT_2 UDF TXT 2 NO FIELD_VAL_7 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_TXT_3 UDF TXT 3 NO FIELD_VAL_8 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_TXT_4 UDF TXT 4 NO FIELD_VAL_9 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_TXT_5 UDF TXT 5 NO FIELD_VAL_10 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_­DATE_1   NO FIELD_VAL_11 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_­DATE_2   NO FIELD_VAL_12 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_­DATE_3   NO FIELD_VAL_13 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_­DATE_4   NO FIELD_VAL_14 CSTM_FUNC­TION_USER­DEF_FIELDS 150
UDF_­DATE_5   NO FIELD_VAL_15 CSTM_FUNC­TION_USER­DEF_FIELDS 150

Customer Account File Format

The format of the customer account file is given below:

FIELD NAME DESCRIPTION LENGTH MANDATORY FCC_COLUMNS LENGTH TABLE NAME Fcubs Remarks
T_EXT_­CLI­ENT_ID External client id 10 YES CUST_NO 9 STT­M_CUST_ACCOUNT  
T_BANK_ACCT_ID Bank account id 16 YES CUST_AC_NO 20 STT­M_CUST_ACCOUNT  
T_ACCT_STATUS Account status 1 YES ACC_STA­TUS 4 STT­M_CUST_ACCOUNT  
T_ACCT_SUB_­TYPE Account sub type 5 YES ACCOUNT CALSS 6 STT­M_CUST_ACCOUNT  
T_CURR Currency 3 YES CCY 3 STT­M_CUST_ACCOUNT  
T_ACCT_BRANCH Account branch 50 YES BRANCH_CODE 3 STT­M_CUST_ACCOUNT  
T_MICR MICR 24 NO NULL      
T_ACCT_­TYP Account type 5 YES ACCOUNT_TYPE 1 STT­M_CUST_ACCOUNT S- Sav­ings, U - Current
T_SALES_REP_ID Sale rep­resenta­tive id 50 NO NULL      
T_OP­PORTUNI­TY_ID Sale oppurtin­ity id 50 NO NULL      
OPEN­ING_DATE Opening date   YES AC_OPEN_DATE 8 STT­M_CUST_ACCOUNT  
CLOS­ING_DATE Closing date   NO AC_SET_­CLOSE_­DATE 8 STT­M_CUST_ACCOUNT  
T_ACCT_ADDRESS Address 200 YES ADDRESS1 105 STT­M_CUST_ACCOUNT  
T_ACCT_ADDRESS1 Address line 1 200 NO ADDRESS2 105 STT­M_CUST_ACCOUNT  
T_ACCT_ADDRESS2 Address line 1 200 NO ADDRESS3 105 STT­M_CUST_ACCOUNT  
T_ACCT_CITY Account city 100 NO LOCA­TION 15 STT­M_CUST_ACCOUNT  
T_ACCT_COUN­TRY_ID Account country id 3 NO NULL      
T_HLD_ACCT_YN Held away accoutn YN 1 YES "N" 1   DEFAULT VALUE "N"
T_ACCT_­DESCRIP­TION Accound descrip­tion 20 NO AC_DESC   STT­M_CUST_ACCOUNT  

Customer Balance – CASA File Format

The format of the customer balance file is given below:

FIELD NAME DESCRIPTION TYPE LENGTH MANDATORY FCC_COLUMN LENGTH FCC Table name FCC Remarks
CLI­ENT_ID Client id VAR­CHAR 10 YES CUST_NO 9 STT­M_CUST_AC­COUNT  
BANK_ACCT_ID Bank account id /loan account id VAR­CHAR 16 YES CUST_AC_NO 20 STT­M_CUST_AC­COUNT  
PROD_ID product id VAR­CHAR 5 YES PROD 4 ICT­B_EN­TRIES Interest Product associ­ated.One account can have multiple interest product
BOOK­ING_­DATE booking date DATE   YES AC_OPEN_­DATE   STT­M_CUST_AC­COUNT  
VAL­UE_­DATE value date DATE   YES   8   Default Value Applica­tion date
AMOUNT amount NUM­BER 24,6 YES ACY_AVL_BAL 22 STT­M_CUST_AC­COUNT  
CCY_ID Currency VAR­CHAR 3 YES CCY 3 STT­M_CUST_AC­COUNT  
MATU­RITY_­DATE Maturity date DATE   NO NULL      
ACCT_SUB_­TYPE Account sub type VAR­CHAR 5 YES ACCOUNT_­CLASS 6 STT­M_CUST_AC­COUNT  
INT_RATE interest rate NUM­BER 24,6 NO AMT/RATE 22 ICT­B_UDE­VALS  
TENOR tenor NUM­BER 10 YES NULL      
ACCR_INT Accrued interest NUM­BER 24,6 NO AMT 22 ICT­B_EN­TRIES This will be sum of total credit interest accrued for that product includes all credit formula associ­ated to it
LAST_INT_AC­CR_­DATE Last inter­est paid DATE   NO LAST_ACCR_DT 8 ICT­B_AC­C_PR  
INTER­EST_PAID interest paid NUM­BER 24,6 NO AMT 22 ICT­B_EN­TRIES This will be sum of total credit interest liqui­dated for the prod­uct includes all credit formula associ­ated to it
LAST_INT_­DATE Last inter­est date DATE   NO LAST_ACCR_DT 8 ICT­B_AC­C_PR  
HOLD_AMOUNT Hold amount NUM­BER 24,6 NO ACY_­BLOCKED_AMOUNT 22 STT­M_CUST_AC­COUNT  

Customer Balance – Loan File Format

The format of the customer loan balance file is given below:

FIELD NAME DESCRIPTION TYPE LENGTH MANDATORY FCC_COLUMN LENGTH FCC Table name FCC Remarks
CLI­ENT_ID Client id VARCHAR 10 YES CUS­TOMER_ID 9 CLTB_AC­COUNT_MASTER  
BANK_ACCT_ID Bank account id /loan account id VARCHAR 16 YES CUST_AC_NO 20 STT­M_CUST_ACCOUNT  
PROD_ID product id VARCHAR 5 YES PRO­DUCT_CODE 4 CLTB_AC­COUNT_MASTER  
BOOK­ING_­DATE booking date DATE   YES BOOK_DATE 8 CLTB_AC­COUNT_MASTER YYYYM­MDD for­mat
VAL­UE_­DATE value date DATE   YES VAL­UE_­DATE 8 CLTB_AC­COUNT_MASTER YYYYM­MDD for­mat
AMOUNT amount NUMBER 24,6 YES AMOUNT_FINANCED   CLTB_AC­COUNT_MASTER  
CCY_ID Cur­rency VARCHAR 3 YES CUR­RENCY 3 CLTB_AC­COUNT_MASTER  
MATU­RITY_­DATE Maturity date DATE   NO MATURITY_DATE 8 CLTB_AC­COUNT_MASTER YYYYM­MDD for­mat
ACCT_SUB_­TYPE Account sub type VARCHAR 5 YES "L" 1   Default Value "L" - Loan
INT_RATE interest rate NUMBER 24,6 NO UDE_VALUE   CLTB_AC­COUNT_UDE_VALUES  
TENOR tenor NUMBER 10 YES NULL   CLTB_AC­COUNT_MASTER  
ACCR_INT Accrued interest NUMBER 24,6 NO ACCRUED_AMOUNT   CLTB_AC­COUNT_SCHEDULES  
LAST_INT_AC­CR_­DATE Last interest paid DATE   NO EVENT_DATE 8 CLTB_AC­COUNT_EVENTST_DI­ARY Last accrual event hap­pened date
INTER­EST_PAID interest paid NUMBER 24,6 NO AMOUNT_SET­TLED 22 CLTB_AC­COUNT_SCHEDULES  
LAST_INT_­DATE Last interest date DATE   NO EVENT_DATE 8 CLTB_AC­COUNT_EVENTST_DI­ARY Last liqui­dation event hap­pened date
HOLD_AMOUNT Hold amount NUMBER 24,6 NO AMOUNT_FINANCED MINUS AMOUNT_DIS­BURSED 22 CLTB_AC­COUNT_MASTER This will be derived from
AMOUNT_FI­NANCED - AMOUNT_DIS­BURSED

Customer TD Booked File Format

The format of the customer TD booked file is given below:

FIELD NAME DESCRIPTION TYPE LENGTH MANDATORY REMARKS SAMPLE VALUES
CLI­ENT_ID External customer ID Varchar 10 Yes    
TRAN_TYPE Tran type Varchar 15 Yes   Could be NEW for Booking, PAR for Partial Uplift or RNW for Rollover
ACCOUNT_NO Acount no Varchar 16 Yes    
TRAN_DATE Transac­tion date Date   Yes    
AMOUNT Amount Number 24,6 Yes Could be BookingAMt or PARAMt (For PAR TranType) or Rollover­AMt (for RNW tranType)
CUR­RENCY Currency Varchar 3 Yes    
MAT_­DATE Maturity date Date   Yes Could be MatDt for Booking OR New MatDt for Rollovers
TENOR Tenor Number 10 Yes    
INT_RATE Interest rate Number 23,6 NO Has been made non-mandatory as FCUBS request; but Should be mandatory for Booking IntRate or Rollover IntRate (for RNW tranType)
EXTERNAL_TRAN_ID external tran id Varchar 40 No Since FCUBS does not have any separate TransactionId for TD and only uses TD Ref Number, this should be sent as NULL/BLANK
TRAN_STA­TUS Tran Sta­tus Varchar 1 Yes No need to send CLOSED TDs..only Active TDs to be sent
SPREAD Spread in BPS Number 24,6 Yes Only for Floating we have Spread; Fixed Deposit only has Customer Level Spread -Bala to get back
ACCOUNT_­CLASS Product alt id Varchar 5 Yes    
ROLL­OVER_TYPE Rollover Type Varchar 1 Yes Mandatory for rollover Trans­actionType oNLY Can be P or 'P+I' as Rollo­ver Type
INTEREST_AMOUNT Interest Amt for Rollover 'P' type Number 24,6 No   This indicates the Interest to be paid to cus­tomer on the RolloverDate for a 'P' type of Rollover

Customer TD Interest Liquidation, Penalty File Format

The format of the customer TD interest liquidation, penalty file is given below:

FIELD NAME DESCRIPTION MANDATORY REMARKS SAMPLE VALUES
TRAN REF NO Can be stored as Ext_Tran_Idn or Ext_Tran_Ref Yes    
CUSTOM­ER_NO Client_id Yes    
CUSTOM­ER_NAME1 ignored NO    
ACC Sub_portfolio_id Yes    
ACCOUNT_­CLASS Based on Account class(whether it is TD or call deposit, instrument id and instrument type would be populated)- details in FCPB_FS_­CASA_Includeinhold­ings_Rel2.1- section 8) Yes    
         
TRN_DT Tran_Date Yes Interest Liq­uidation Date, Pen­alty Charg­ing Date  
LCY_AMOUNT Amount Yes Interest, Penalty  
AC_NO Indicates Dr/Cr AcctNum­ber n ignored NO    
AC_CCY Amt_ccy YES    
RECORD_STAT ignored NO    
TRANSAC­TION_TYPE Tran_Type Yes   Interest, Penalty

Corporate Directors File Format

The format of the corporate directors file is given below:

FIELD NAME DESCRIPTION MANDATORY FCUBS Column Name FCUBS Table Name
LINKED_CLI­ENT_ID External client id of cus­tomer YES CUSTOM­ER_NO STTM_CUS­TOMER
FCPB CLI­ENT_ID FCPB Client Id of cus­tomer     NULL
AUTH_SIGN_F­NAME First Name of the author­ized signatory NO DIREC­TOR_NAME STT­M_CORP_­DIRECTORS
AUTH_SIGN_L­NAME Last Name of the author­ized signatory NO   NULL
AUTH_SIGN_DOB Date of birth of the authorized signatory NO   NULL
MOBILE_COUN­TRY_CODE Mobile international country code of the authorized signatory NO MOB_IS­D_NO STT­M_CORP_­DIRECTORS
AUTH_SIGN_­MOBILE Mobile number of the authorized signatory NO MOBILE_­NUMBER  
PH_HOME_COUNTRY_CODE Telephone international country code of the authorized signatory NO TEL_IS­D_NO STT­M_CORP_­DIRECTORS
AUTH_SIGN_HOME Home number of the authorized signatory NO TELE­PHONE STT­M_CORP_­DIRECTORS
AUTH_SIGN_TAX_ID Tax identified of the authorized signatory NO TAX_ID STT­M_CORP_­DIRECTORS
AUTH_SIGN_REL Relationship of the authorized signatory NO    
AUTH_SIGN_­TYPE Type of the authorized signatory NO    
NATIONALI­TY_COUN­TRY_CODE Nationality of the author­ized signatory NO NATIONAL­ITY STT­M_CORP_­DIRECTORS
AUTH_SIGN_ADDRESS Address of the author­ized signatory NO ADDRESS_LINE1 STT­M_CORP_­DIRECTORS
AUTH_SIGN_COUNTRY Country-Address of the authorized signatory NO ADDR_COUNTRY STT­M_CORP_­DIRECTORS
AUTH_SIGN_DOMICILE Country-Address of the authorized signatory NO   NULL

Customer FATCA File Format

The format of the customer FATCA handoff file is given below:

FIELD NAME DESCRIPTION MANDATORY FCUBS Column Name FCUBS Table Name
LINKED_CLI­ENT_ID External client id of cus­tomer YES CUSTOM­ER_NO STTM_CUS­TOMER
FCPB CLI­ENT_ID FCPB Client Id of cus­tomer. In case if external ID is does not provided in this file, then process cus­tomer upload based on FCPB_CLIENT_ID     NULL
US_INDICIA US INDICIA PRESENT NO US_INDI­CIA STT­M_CUST_­FATCA
FATCA_ID FATCACLASSIFICA­TION (to be arrived based on values provided by FCUBS - consider as it is assuming that there would be synch between both the systems) NO FATCA_­CLASFCN STT­M_CUST_­FATCA
RECALCI­TRANT Recalcitrant NO RECALCI­TRANT STT­M_CUST_­FATCA
EIN EIN NO CUST_EIN STT­M_CUST_­FATCA
DATE_OF_IS­SUE ISSDATE NO EIN_IS­SUE_DT STT­M_CUST_­FATCA
DATE_OF_EX­PIRY EXPDATE NO EIN_EXPI­RY_DT STT­M_CUST_­FATCA
TIN TIN NO CUST_TIN STT­M_CUST_­FATCA
TIN_DATE_OF_­EXPIRY TIN EXPIRY DATE NO TIN_EXPI­RY_DT STT­M_CUST_­FATCA
GIIN GIIN NO TIN_EXPI­RY_DT STT­M_CUST_­FATCA
COMMENTS User Comments NO REMARKS STT­M_CUST_­FATCA
EFFECTIVE DATE FCPB: Effective date of FATCA classification (should get auto-popu­lated as current business date in FCPB).NOT EXPECTED FROM FCUBS,SHOULD BE BLANK     NULL