3. Alerts

The bank customers may require critical information on their accounts or transactions to help them take appropriate actions. The bank staff may also require certain information on the customer accounts and transactions. A business alert is a message that conveys such information by email, sms or as a message on the user dashboard.

Oracle FLEXCUBE allows you to define business alerts for the bank staff as well as bank customers. The alerts can be generated to the customers and staff at a regular interval defined in the system.

This chapter contains the following sections:

3.1 Alert Selection Criteria

This section contains the following topics:

3.1.1 Defining Alert Selection Criteria

You can define alert selection criteria in Oracle FLEXCUBE. The system identifies the set of customers who receive the alerts based on the selection criteria.

The system generates the alerts based on the selection criteria defined in ‘Alert Selection Criteria’ screen. To invoke this screen, type ‘ITDACMNT’ in the field at the top right corner of the Application toolbar and click the adjoining arrow button.

Specify the following details:

Criteria Code

Specify a unique code that identifies the criterion that you are defining. This code will be the unique identifier of the criterion based on which alert is generated.

The system will not allow you to modify the criteria code after saving it.

Description

Specify a brief description of the selection criterion that you are defining.

Select

Specify the ‘Select’ part of the selection criterion.

The criterion must be defined as an SQL statement with aliases. However, you need not enter the keyword ‘SELECT’.

The alias naming convention is ‘ColX’. Here, ‘X’ refers to the sequential number of the column in the ‘Select’ clause.

Examples are s1.cust_ac_no Col1 ,s1.cust_no Col2 ,s1.ccy Col3 ,s1.account_class Col4 ,s1.ACY_OPENING_BAL Col5, s2.customer_name1 Col6, s2.language Col7, s2.default_media Col8

Based on the columns defined under ‘Select’ and ‘From and Where Clause’, the system forms the final SQL statement. You need to ensure that the final SQL statement is a syntactically correct SQL statement.

From and Where Clause

Specify the remaining part of the selection criterion. You need not enter the keyword ‘From’.

Based on the columns defined under ‘Select’ and ‘From and Where Clause’, the system forms the final SQL statement. You need to ensure that the final SQL statement is a syntactically correct SQL statement.

Final SQL Statement

The system concatenates the values defined under ‘Select’ and ‘From and Where Clause’ and displays the final SQL statement. This must be a valid SQL statement.

The system generates the alert messages based on this final SQL statement.

Once you have captured the details, save the record.

To define the criteria as SQL statement, you need to have sufficient understanding about the data model of Oracle FLEXCUBE and SQL programming language.

3.1.2 Viewing Alert Criteria Definition Summary

You can view a summary of the alert criteria maintained in Oracle FLEXCUBE using ‘Alert Selection Criteria Summary’ screen. To invoke this screen, type ‘ITSACMNT’ in the field at the top right corner of the Application toolbar and click the adjoining arrow button.

You can search for the records based on one or more of the following parameters:

Once you have set the search parameters, click the ‘Search’ button. The system displays the records that match the search criteria. Double-click a record to view the details.

3.2 Alerts

This section contains the following topics:

3.2.1 Defining Alerts

You can define specific alerts to be sent to the bank staff and customers. You need to define the alert codes and map the alert code to the customer or bank user using ‘Alert Code Definition’ screen. To invoke this screen, type ‘ITDADMNT’ in the field at the top right corner of the Application toolbar and click the adjoining arrow button.

Specify the following details:

Alert Code

Specify the alert code to be mapped to the customer or bank.

Description

System displays the description of the alert based on the alert code specified.

Criteria Code

Specify the selection criteria code to be used for the alert. The option list displays all valid selection criteria codes maintained in the system. Choose the appropriate one.

Final SQL Statement

Based on the criteria code selected, the system displays the final SQL statement.

3.2.2 Preferences Tab

You can set the preferences for alert message generation under ‘Preferences’ tab. Click ‘Preferences’ tab on ‘Alert Definition’ screen:

Under this tab, you can set the following preferences for the alert generation.

Effective From

Specify the start date of the alert message generation. Click the date button to choose a date from the calendar.

The system will generate the alerts for the users/customers from this date.

End Date

Specify the end date of the alert message generation. Click the date button to choose a date from the calendar.

The system will generate the alerts for the users/customers until this date.

Frequency

Specify the frequency of the alert message generation. Depending on the requirement, you can choose one of the following frequencies:

View Days

Specify the number of days you wish to keep the alert message in the dashboard or the portal. The user or customer can view the alert message in the dashboard or portal for the number view days maintained here. After that, the message will disappear from the dashboard or portal.

Actionable

Check this box to indicate that the alert is an actionable alert. You can check this box only if the Target Type is selected as ‘Bank User’.

Note

3.2.3 Target Tab

You can capture the details of the customer and bank users for who will receive the alert message. Click ‘Target’ tab.

Specify the following details:

Target Type

The target type indicates the receiver of the alert message. The drop-down list displays the following target types:

You can choose the appropriate one.

Customer/Both

Specify the intended target. The drop-down list displays the following options:

You can select one of the above options only for the target types ‘Customer’ and ‘Both’.

Customer No Column Position

Specify the position of the customer number column in the SQL statement. If you have selected the target type as ‘Customer’ or ‘Both’, it is mandatory to specify the customer number column position.

Bank User Type

Specify the bank user type to receive the alert message. The drop-down list displays the following user types:

Choose the appropriate one.

This is applicable only if the target type is ‘Bank User’.

Target ID

Specify the target ID. If the Bank User Type is ‘Role’, you need to specify the particular user role. If the Bank User Type is ‘User ID’, you need to specify the respective user ID.

You can choose the appropriate target ID from the option list. The alert message will be generated to the target IDs selected here.

Description

The system displays the description of the selected target ID.

You can add more bank user types by clicking the add button. You can also delete a bank user type using delete button.

3.2.4 Message Tab

You can capture the details of the message to be sent to the customers and users under ‘Message’ tab. Click ‘Message’ tab.

You can capture the following details under this tab:

Channel

Specify the mode of message delivery. The drop-down list displays the following channels:

Choose the appropriate one. The alert message is delivered to the customer/bank user by the mode specified here.

Language

Specify the language of the alert message. The option list displays all valid languages that are applicable. Choose the appropriate one.

Subject

Specify a subject that is significant to the alert message to be generated.

Customer Message

Enter the message to be generated for the customer. The message may contain two types of text, viz. static and variables.

The static text will be generated as they are defined here. The variable text will vary based on the intended customer and the nature of the message.

This field is enabled only if the alert message is generated for the customer.

User Message

Enter the message to be generated for the bank user or RM. The message may contain two types of text, viz. static and variables.

The static text will be generated as they are defined here. The variable text will vary based on the intended user and the nature of the message.

This field is enabled only if the alert message is generated for the bank user.

3.2.4.1 Using Variables in Messages

You can use certain variables in the alert messages generated for customers and users. Few of the variables are given below:

Variable

Description

$USERID

While generating the alert message, the system will replace this varia­ble with the corresponding user ID from ‘Alert Definition’.

$USERNAME

While generating the alert message, the system will replace this attrib­ute with the user name of the corresponding user ID defined in the ‘Alert Definition’.

$TODAY

While generating the alert message, the system will replace this attrib­ute with the current system date.

You can consider the following examples.

1. Alert for the bank user about the customers who have more than three loans with statuses other than ‘NORM’ with an adversity level greater than one.

In order to create this alert, you need to maintain the following selection criterion in ‘Alert Selection Criteria’ screen.

SELECT

cust.customer_name1 col1, cust.customer_no col2, a.user_defined_status col3, dt.today col4,COUNT(1) col5

FROM cltb_account_master a,

sttm_customer cust,

sttm_dates dt

WHERE cust.customer_no = a.customer_id

AND dt.branch_code = a.branch_code

AND a.user_defined_status IN

(SELECT b.status_code

FROM CLTM_PRODUCT_STATUS_LEVEL b

WHERE b.product_code = a.product_code

AND b.adversity_level in

(SELECT c.adversity_level

FROM CLTM_PRODUCT_STATUS_LEVEL c

WHERE c.product_code = a.product_code

AND c.status_code <> 'NORM'

and c.adversity_level > 1))

Group By cust.customer_name1, cust.customer_no , a.user_defined_status, dt.today

Having count(1) > 3

Further, in ‘Alert Definition’ screen under ‘Message’ tab, you need to create an alert message as follows:

Dear $USERNAME ($USERID),

Kindly note that as on today ($TODAY), the number of loans for the customer $1 (ID $2) with status $3 or worse is $5.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

Notice that three variables viz. $USERNAME, $USERID and $TODAY are used in the above message. While generating the actual message for a user, the system replaces these variables with the bank user name, user ID and the current application date.

The actual message will be generated as follows:

Dear Smith (SM000123),

Kindly note that as on today (01st Mar, 2012), the number of loans for the customer Elizabeth (ID C038756) with status NPA or worse is 5.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

2. Alert for fixed depositors’ maturity date intimation, where the target type is ‘Customer’ and ‘Customer/Both’ is ‘Customer Only’.

In order to create this alert, you need to maintain the following selection criterion in ‘Alert Selection Criteria’ screen.

SELECT s1.cust_ac_no col1, s1.cust_no col2, s1.ccy col3, s1.account_class col4,  s1.ACY_OPENING_BAL col5,  

s2.customer_name1 col6, s2.language col7, s2.default_media col8  

FROM  fcubs12r1.sttm_cust_account s1, fcubs12r1.sttm_customer s2,fcubs12r1.ictm_acc s3

WHERE    account_class = 'TD01' 

AND s1.cust_no = s2.customer_no   and s1.cust_ac_no = s3.acc

Further, in ‘Alert Definition’ screen under ‘Message’ tab, you need to create an alert message as follows:

Dear $6 (FD a/c No $1),

Kindly note that your fixed deposit, bearing account no. $1 (with an initial deposit of $3 $5) is due for maturity on $9. The maturity balance for the deposit is $3 $5.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

The actual message will be generated as follows:

Dear John Mathew (FD a/c No FD00003456),

Kindly note that your fixed deposit, bearing account no. FD00003456 (with an initial deposit of USD 1,000.00) is due for maturity on 07-Mar-2012.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

3. Alert for fixed depositors’ maturity date intimation, where the target type is ‘Customer’ and ‘Customer/Both’ is ‘RM Direct Only’.

In order to create this alert, you need to maintain the following selection criterion in ‘Alert Selection Criteria’ screen.

SELECT s1.cust_ac_no col1, s1.cust_no col2, s1.ccy col3, s1.account_class col4,  s1.ACY_OPENING_BAL col5,  

s2.customer_name1 col6, s2.language col7, s2.default_media col8  

FROM  fcubs12r1.sttm_cust_account s1, fcubs12r1.sttm_customer s2,fcubs12r1.ictm_acc s3

WHERE    account_class = 'TD01' 

AND s1.cust_no = s2.customer_no   and s1.cust_ac_no = s3.acc

Further, in ‘Alert Definition’ screen under ‘Message’ tab, you need to create an alert message as follows:

Dear $RM ($RMUID),

Kindly note that your customer $2 (customer id $6) has a fixed deposit (no. $1), which is maturing on $9. The maturity balance for the deposit is $3 $5.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

The actual message will be generated as follows:

Dear Joseph (RM0875344),

Kindly note that your customer John Mathew (customer ID CUST23456) has a fixed deposit (no. FD00003456), which is maturing on 07th Mar, 2012. The maturity bal­ance for the deposit is USD 12,000.

Regards,

<Bank Name>

This is an auto-generated message and does not need any signatures.

3.2.5 Viewing Alert Definition Summary

You can view a summary of the alerts defined in Oracle FLEXCUBE using ‘Alert Definition Summary’ screen. To invoke this screen, type ‘ITSADMNT’ in the field at the top right corner of the Application toolbar and click the adjoining arrow button.

You can search for the records based on one or more of the following parameters:

Once you have set the search parameters, click ‘Search’ button. The system displays the records that match the search criteria. Double-click a record to view the detailed screen of the record.

3.2.6 Viewing Alert Dashboard

Oracle FLEXCUBE generates and displays the user alerts defined for your user profile. You can view the user alerts on ‘Alerts’ screen in your Dashboard. You can also view the ‘Alerts’ screen by typing ‘ITDALEDB’ in the field at the top right corner of the Application toolbar and clicking the adjoining arrow button.

This screen displays five latest alert messages that require your attention. You can click the link ‘more…’ to view the remaining alert messages in your Dashboard. The messages are arranged in descending order of the effective date.

You can view the following details of the alert messages:

For further details on User Dashboards, refer to the User Dashboards user manual.