34.4.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 |
getBucketColumnName |
getBucketColumnName (Lag NUMBER) |
Column-Name – VARCHAR2 |
Returns the column-name in RES_DTL_xxxxx corresponding to the Lag specified; |
|
|
|
|
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 NUMBER) |
Coefficient value – NUMBER |
Returns the coefficient corresponding to the coefficient 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 exception if no data found for the given inputs |
Procedure |
AssertCashFlowFE |
AssertCashFlowFE(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 |
AssertStaticFE(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 |
getStaticColumnName |
getStaticColumnName(fe_num number) |
Column-Name – VARCHAR2 |
Returns the column-name in FSI_O_RESULT_MASTER / FSI_O_CONSOLIDATED_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 relevant columns upon which the expression has been defined in Formula results. |
Procedure |
Evaluate |
evaluate(pBatchRunId varchar2, pAsOfDate varchar2, pProcessSysID number) |
-N.A.- |
Evaluate Formula Results assumption for given process-id. |
|
|
|
|
Useful if the CFE Processing has already been completed 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 execution 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_RESULT_MASTER |
CURRENT_ALM_PROCESS_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 evaluated. |
CURRENT_TIME_BUCKET |
NUMBER |
The Time Bucket SYS-ID for which the expression is being evaluated. |
CURRENT_START_DATE_INDEX |
NUMBER |
The current Start-date-index number for which the expression is being evaluated. |
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 evaluated |
CURRENT_LEG_TYPE |
NUMBER |
The leg-type for which the expression is being evaluated |
CURRENT_ORG_UNIT |
NUMBER |
The Org-Unit-Id for which the expression is being evaluated |
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 evaluated |
ALM_PROCESS_REPORTING_CURRENCY |
VARCHAR2 |
The reporting currency currently set for the process-sys-id |
ALM_PROCESS_EXECUTED_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 dimension in use for the current execution |
ORG_UNIT_DIMENSION_COLUMN_NAME |
VARCHAR2 |
The active Org-Unit dimension in use for the current execution |