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 decimal 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 dec­imal separator (.). 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:

• Section 2.1, "Details Handed off from FCUBS to Oracle FCPB"
• Section 2.2, "Interface Definition"
• Section 2.3, "FATCA Interface between Oracle FCUBS and FCPB"
• Section 2.4, "Online Notifications"
• Section 2.5, "Interface Details for Data Handoff"

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:

• Section 2.2.1, "Defining Interface Details for Oracle FCPB"

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:

• Incoming - Select this for interfaces that upload data into FCUBS
• Outgoing - Select this for interfaces that facilitate handoff of data from FCUBS

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:

• Fixed - Select this format type if the interface data is within fixed width.
• Delimited - Select this format type if the interface data is in delimited format.

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.

• Information available in the FATCA related fields
• Any field information related to 'Director' in the existing customer master file will be removed as part of the batch. Such details will form a part of the 'Directors' hand-off file.

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:

• Creation of term deposit account in FCUBS
• Premature withdrawal (redemption before maturity date) of the term deposit amount
• Creation of customer accounts in FCUBS
• Change in the balance in the customer accounts
• Creation, modification or closure of customer information
• Creation of Brokers (customer under the customer category ‘Broker’) in FCUBS
• Modification of term deposit account in FCUBS
• Premature withdrawal (redemption before maturity date) of the term deposit amount

Note

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

• Rollover of Term deposit account in FCUBS
• Creation of Recurring deposit account in FCUBS
• Customer creation under ‘BROKER’ category
• Amendment of the segmentation code, net worth amount, net worth currency and customer currency of customer Segmentation
• Amendment of the segmentation status from Upgraded to Downgraded and Re-Upgraded to Downgraded

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:

• TD maturity with interest liquidation
• Daily accrued interest on TD
• Penalty on TD
• Loan and Liability Information

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

This section contains the following topics:

• Section 2.5.1, "Term Deposit Maturity with Interest Liquidation"
• Section 2.5.2, "Daily Accrued Interest on Term Deposits"
• Section 2.5.3, "Penalty on Term Deposits "
• Section 2.5.4, "Loan and Liability Information"
• Section 2.5.5, "Term Deposit Rollover"
• Section 2.5.6, "Segmentation Status Information"

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:

• Principal outstanding amount
• Maturity date
• Interest rate
• Tenor of the loan
• Accrued amount
• Amount paid
• Last paid date

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:

• Branch
• Deposit account number
• Old maturity date
• New maturity date
• Renewal date
• Rollover type
• Total interest on the TD
• Principal
• Rollover amount
• Balance interest paid out during rollover
• Interest rate
• Tenor
• Customer number
• Currency

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:

• Customer File

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.

• Customer Account File

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

• Customer Balances – CASA File

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.

• Customer Balances – Loan File

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.

• Customer TD Booked & Rollover File

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

• Booked
• Rollover
• Partial Redemption

The following details are sent to the FCPB:

• Details of all open TD accounts
• Partial redeemed TD details
• TD renewal details

• Customer TD Interest Liquidation, Penalty File - Segment Upgrade Details

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

• Interest liquidation
• Penalty

The following TD details are sent to FCPB:

• TD with interest liquidation
• TD with penalty entries for partial redemption

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