1. User Guide
  2. Data Loaders
  3. ALMBI Transformation

5.14 ALMBI Transformation

ALM_BI_TRANSFORMATION data definition transforms the Asset Liability Management (ALM) processing results of an executed ALM process to ALMBI fact tables.

You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from Batch Maintenance window within OFSAAI framework.

  1. To run the procedure from SQL*Plus, login to SQL*Plus as the Atomic Schema Owner. The syntax for calling the procedure is

set serveroutput on

DECLARE num number;

Begin num :=

FN_ALM_BI_TRANSFORMATION('<P_BATCH_RUN_ID>','<P_AS_OF_DATE>','<PID>','<P_RE_RUN_FLAG>','<B_LIMIT_FLAG>'); END;

FN_ALM_BI_TRANSFORMATION function requires below parameters:

P_BATCH_RUN_ID is the Batch Run ID typically in the format <INFODOM>_TRANSFORMALMRESULT_<ASOFDATE AS YYYYMMDD>_X.

P_AS_OF_DATE is the As of Date in the format YYYYMMDD. PID Pass the ALM Process Sys ID for which the transformation has to be done.

P_RE_RUN_FLAG is a re-run flag.

'Y': Yes (This means that the transformation was already done and the user is trying to redo the transformation once again for the ALM process).

'N': No (This means that the user is executing the transformation for the first time for the ALM process).

Run below query to check if Transformation has been run before.

BI_TRANSFORM_STATUS = 1 means it was run before, null means new run.

select * from fsi_process_run_history where PROCESS_SYS_ID = <PID>;

B_LIMIT_FLG is limit flag. Specify 'Y' or 'N'. The limit should be prepared only if the flag is 'Y'.

For example:

set serveroutput on DECLARE num number;

Begin num :=

FN_ALM_BI_TRANSFORMATION('OFSALMINFO_TRANSFORMALMRESULT_20230430_3','20230430','712902','Y','N'); END;

Note:

The values for parameters PID and p_re_run_flag has to be entered in the Parameter List during the batch definition.

Figure 5-13 Example


Example

  • If the user is trying to do transformation of ALM process 200009 for the first time, then the values that must be entered in the Parameter List are 200009, 'N'.
  • If the user is trying to do transformation of ALM process 200011, for which he had already done the transformation, then the values that must be entered in the Parameter List are 200011, 'Y'.