15.3 Implementation
Before loading data into the staging table, generate a Load Run Identifier to stamp the records from the source. These records can be a complete snapshot or can be partial or incremental data too. This load run identifier can be generated by calling the function in the OFSAA atomic schema named fn_register_load_run. The function expects some input parameters and returns a unique load run identifier back to the calling program.
Figure 15-1 Generate the Load Run Identifier
Note:
Column n_load_run_id should always be populated only by the value returned by fn_register_load_run.Function - Register Load Run (fn_register_load_run)
Parameters - Batch ID, MIS-Date, Load Run Name, Load Run Purpose, Load Run Type
Table 15-1 Parameters of FN_REGISTER_LOAD_RUN
| Parameters | Source Of Values | Example Values |
|---|---|---|
| Batch ID | Auto generated if you are using OFSAA Framework | OFSBFNDIN- FO_20150101_1 |
| MIS-Date | Input from Customer | 01/01/2015 |
| Load Run Name | Input from Customer | Daily EOD Load |
| Load Run Purpose | Input from Customer | BA/BS (BASEL Advanced Approach, BASEL Standard) |
| Load Run Type | Input from Customer | B - Base, A - Adjustments, P- Backdated Adjustments |
Example:
Declare
Result number;
Begin
Result: = fn_register_load_run ('OFSBFNDINFO_20150101_1','20150101','FSDF_LOAD','BA', 'A');
End;
The function registers the request in the table name rev_load_run_master and marks load as "In progress". You can use columns LOAD_RUN_NAME and LOAD_PURPOSE as per the requirement.
Column Load Type must have only the permissible value such as:
- "B - Base
- "A - Adjustments
- "P- Backdated Adjustments
Table 15-2 Column values for LOAD_RUN_NAME and LOAD_PURPOSE
| LOAD_R UN_I D | MIS_DA TE | LOAD_T YPE | LOAD_PUR POSE | START_DT_ TIME | LOAD_RUN_ NAME | BATCH_ID | LOAD_RUN_ STATUS |
|---|---|---|---|---|---|---|---|
| 1 | 01-JAN-15 | A | BA | 01-JAN-15 | FSDF_Load |
OFSBFNDIN FO_201501 01 _1 |
In Progress |
Note:
Multiple calls to the procedure can be made to the function for given FIC_MIS_DATE. Each call will return a number which will be unique across the FIC_MIS_DATE or Extraction date. You can use this load identifier to load either one or more staging tables.