34.2.1 Formula Expression block

This window allows you to define a Formula. This section has the list of all Financial Elements/Interest Rate Codes, Functions, and Operators, which you can use to define a Formula.

You can also use the custom functions in Formula Expression window. To use a custom function, type the name of function in Formula Expression window.

A custom function is defined using the PL/SQL that returns a number not exceeding the precision of NUMBER(15,2). Compile this function directly within the database schema. You can use any parameter as required and that will be captured as part of the Formula expression itself.

The following Utility functions/procedures within the package ALM_FORMULA_RESULT_HELPER can be accessed in the custom functions. Then, these can be used as per the requirements/objectives.

Table 34-2 List of Functions and Procedures used in Defining Custom Functions

Type	Name	Usage	Output Parameter(s)	Comments
Function	getBucket­ColumnName	getBucket­ColumnName (Lag NUMBER)	Column-Name – VARCHAR2	Returns the column-name in RES_DTL_xxxxx corre­sponding to the Lag speci­fied;
				The lag is offset against the current bucket against which the expression has been defined.
				Exception(s) thrown:
				INVALID_NEGATIVE_LAG if Current Bucket + Lag <= 0
				INVALID_POSITIVE_LAG if Current Bucket + Lag > 240
Procedure	LOGMESSAGE	LOGMESSAGE (Message VARCHAR2)	-N.A.-	Logs a message into the FSI_PROCESS_ERRORS table
				Note: Procedure does an autonomous_transaction based commit.
Function	getCoefficient	getCoefficient ( COEFF NUMBER, LAG NUMBER, FR_TYPE NUM­BER)	Coefficient value – NUMBER	Returns the coefficient cor­responding to the coeffi­cient code, Lag and Formula-type.
				Available Formula-types are
				FR_CASH_FLOW_TYPE = 1
				FR_REPRICING_Gap_TYPE = 2
				FR_LIQUIDITY_Gap_TYPE = 3
				FR_MARKET_VALUE_­TYPE = 4
				Exception(s) thrown:
				INVALID_NEGATIVE_LAG if Current Bucket + Lag <= 0
				INVALID_POSITIVE_LAG if Current Bucket + Lag > 240
				NO_DATA_FOUND excep­tion if no data found for the given inputs
Procedure	AssertCash­FlowFE	AssertCash­FlowFE(fe_num number)	-N.A.-	Asserts if the FE-Number is a cash-flow-FE.
				Exception(s) thrown
				NOT_A_CASH_FLOW_FE if FE_NUM is not a valid FE
Procedure	AssertStaticFE	AssertStat­icFE(fe_num number)	-N.A.-	Asserts if the FE-Number is a Static-FE.
				Exception(s) thrown
				NOT_A_STATIC_FE if FE_­NUM is not a valid FE
Function	getStaticCo­lumnName	getStaticColumn­Name(fe_num number)	Column-Name – VARCHAR2	Returns the column-name in FSI_O_RESULT_MAS­TER / FSI_O_CONSOLI­DATED_MASTER corresponding to the FE-Number specified
				Exception(s) thrown
				NOT_A_STATIC_FE if FE_­NUM is not a valid FE
Function	getFilter	getFilter()	WHERE_CLAUSE VARCHAR2	Returns the WHERE-CLAUSE that can be applied on RES_DTL/CONS_DTL and/or FSI_O_RESULT_MASTER/FSI_O_CONSOLIDATED_­MASTER.
				NOT_A_STATIC_FE if FE_­NUM is not a valid FE
				This clause contains filters applied on each of the rele­vant columns upon which the expression has been defined in Formula results.
Procedure	Evaluate	evalu­ate(pBatchRunId varchar2, pAsOf­Date varchar2, pProcessSysID number)	-N.A.-	Evaluate Formula Results assumption for given pro­cess-id.
				Useful if the CFE Process­ing has already been com­pleted and the need is to only evaluate the Formula Results assumption.
				Note: Do not invoke this procedure within a custom function that is embedded within an FR Expression.
				pBatchRunId is a key used to uniquely identify an exe­cution of a batch / job
				pAsOfDate should be passed in the format YYYYMMDD

The following Utility variables within the package ALM_FORMULA_RESULT_HELPER can be accessed in the custom functions. Then, these can be used as per the requirements/objectives.

List of Variables used in Defining Custom Functions

Variable Name	Data Type	Purpose
RESULT_TABLE	VARCHAR2	Holds the Result-detail table-name i.e. RES_DTL_XXXX
MASTER_TABLE	VARCHAR2	Holds the result-Master table-name i.e. FSI_O_RE­SULT_MASTER
CURRENT_ALM_PRO­CESS_SYS_ID	NUMBER	Holds the Process-Sys-Id of the current execution
CURRENT_AS_OF_DATE	DATE	AS-OF-DATE for which the Formula Result is being evaluated
ALM_PROC_EXEC_­DATE_YYYYMMDD	VARCHAR2	AS-OF-DATE for which the Formula Result is being evaluated in the format YYYYMMDD
CURRENT_SCENARIO	NUMBER	The current forecast-rate scenario number for which the expression is being eval­uated.
CURRENT_TIME_BUCKET	NUMBER	The Time Bucket SYS-ID for which the expression is being evaluated.
CURRENT_START_DATE_IN­DEX	NUMBER	The current Start-date-index number for which the expression is being evalu­ated.
CURRENT_BUCKET	NUMBER	The bucket-number for which the expression is being evaluated
CURRENT_PRODUCT	NUMBER	The Product-Id for which the expression is being evalu­ated
CURRENT_LEG_TYPE	NUMBER	The leg-type for which the expression is being evalu­ated
CURRENT_ORG_UNIT	NUMBER	The Org-Unit-Id for which the expression is being eval­uated
CURRENT_LEGAL_ENTITY	NUMBER	The Legal-Entity Id for which the expression is being evaluated
CURRENT_CURRENCY	VARCHAR2	The Currency for which the expression is being evalu­ated
ALM_PROCESS_REPORT­ING_CURRENCY	VARCHAR2	The reporting currency cur­rently set for the process-sys-id
ALM_PROCESS_EXECUT­ED_BY	VARCHAR2	The AAI user-id who last executed the process for the given as-of-date
ALM_BATCH_RUN_ID	VARCHAR2	The Batch Run-ID i.e. unique execution identifier for the current execution
PRODUCT_DIMENSION_­COLUMN_NAME	VARCHAR2	The active product dimen­sion in use for the current execution
ORG_UNIT_DIMENSION_­COLUMN_NAME	VARCHAR2	The active Org-Unit dimen­sion in use for the current execution