15.4.1 Complete Snapshot Load Example
For example, if we have three Loan Contract accounts in a bank system which is supposed to be loaded into OFSAA Stage Loan Contracts using the Load Run ID = 1 for BASEL Standard Approach, the data after loading staging table will appear as below:
Table 30: Complete Snapshot Load example
| FIC_MIS_DATE | V_ACCOUNT_NUMBER | V_GAAP_CODE | N_LOAD_RUN_ID | N_EOP_BAL |
|---|---|---|---|---|
| 01-JAN-15 | LOAN1000 | USGAAP | 1 | 4066.213 |
| 01-JAN-15 | LOAN1001 | USGAAP | 1 | 34538.905 |
| 01-JAN-15 | LOAN1002 | USGAAP | 1 | 667.357 |
NOTE
After each load you need to run fn_register_load_details function mentioned in Post Stage Load Process and Updating Load as Completed which is explained in the following sections.
In order to enable downstream applications to consume only the latest set of record, you need to call another function named fn_pop_load_run_map. This is mandatory in case of incremental snapshot load scenario. This function populates a intermediate processing table that keep track of latest incoming record identifier.
Function - Populate Load Run Map
Parameters - Batch Id, MIS Date, Stage Table Name, Load Run Id, Load Run Name
Table 31: Populate Load Run Map Example
| 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 |
| Stage Table Name | Input from Customer | STG_CASA |
| Load Run ID | Input from Customer | 1 |
| Load Run Name, | Input from Customer | FSDF_Load |
Example:
Declare
Result number;
Begin
Result: = fn_pop_load_run_map('OFSBFNDINFO_20150101_1','20150101','STG_CASA',1,'FSDF
_LOAD');
END;
NOTE
For troubleshooting any errors while making the function calls , refer to fsi_message_log table for more details.
For the example mentioned above, records in FSI_ACCOUNT_LOAD_RUN_MAP table will appear as below:
Table 15-3 Records in the FSI_ACCOUNT_LOAD_RUN_MAP table
| FIC_MIS_DATE | V_ACCOUNT_NUMBER | V_GAAP_CODE | N_LOAD_RUN_ID | F_LATEST_LOAD_RUN_FLAG |
|---|---|---|---|---|
| 1-Jan-15 | LOAN1000 | USGAAP | 1 | N |
| 1-Jan-15 | LOAN1001 | USGAAP | 1 | N |
| 1-Jan-15 | LOAN1002 | USGAAP | 1 | N |
| 1-Jan-15 | LOAN1001 | USGAAP | 2 | Y |
| 1-Jan-15 | LOAN1002 | USGAAP | 2 | Y |
| 1-Jan-15 | LOAN1000 | USGAAP | 2 | Y |
There may be a requirement to reload a complete snapshot of data in the OFSAA staging again. This could either be to satisfy a intraday reporting requirement, or to load corrected source records in the OFSAA staging table. The earlier design forced users to truncate staging table to accommodate the new set of date. However with the introduction of Load Run identifier concept, you can retain both set of data in the staging area and allow the downstream application choose the correct set for processing. This will involve making another call to fn_register_load_run function.
For example in the below table, LOAN1001 and LOAN1002 have some changes since the previous load and will now need be loaded again staging with a different load run identifier . Additionally, strategy is to load the complete snapshot again to staging, all the records from the source such as, both changed and unchanged records will need to stamped with the new load run identifier.
Table 15-4 Table 33: The STG_LOAN_CONTRACTS table after the loading
| FIC_MIS_DATE | V_ACCOUNT_NUMBER | V_GAAP_CODE | N_LOAD_RUN_ID | N_EOP_BAL |
|---|---|---|---|---|
| 01-JAN-15 | LOAN1000 | USGAAP | 1 | 4066.213 |
| 01-JAN-15 | LOAN1001 | USGAAP | 1 | 34538.905 |
| 01-JAN-15 | LOAN1002 | USGAAP | 1 | 667.357 |
| 01-JAN-15 | LOAN1000 | USGAAP | 2 | 4066.213 |
| 01-JAN-15 | LOAN1001 | USGAAP | 2 | 34540.000 |
| 01-JAN-15 | LOAN1002 | USGAAP | 2 | 670.000 |
REV_LOAD_RUN_MASTER after second function call will appear as below:
Table 15-5 The REV_LOAD_RUN_MASTER table after the second function call
| LOAD_ RUN_ID | MIS_DAT E | LOAD_T YPE | LOAD_PURP OSE | START_DT_ TIME | LOAD_RUN_ NAME | BATCH_ID | LOAD_RUN _STATUS |
|---|---|---|---|---|---|---|---|
| 1 | 01-JAN-15 | B | BA |
01-JAN-15 13:00 PM |
FSDF_Load | OFSBFND- INFO_20150 101_1 | Complete |
| 2 | 01-JAN-15 | B | BA |
01-JAN-15 23:00 PM |
Loan Correc- tions | OFSBFND- INFO_20150 101_2 | In Progress |
Following tables require full snapshot mandatorily:
- STAGE ACCOUNT WRITE OFF DETAILS
- STAGE ACCOUNT RECOVERY DETAILS
- STAGE PARTY RATING DETAILS
- STAGE INSTRUMENT RATING DETAILS
- STAGE ACCOUNT RATING DETAILS
If the source system is unable to provide snapshots on a daily basis due to certain limitations, we can customize the T2Ts, and prepare the snapshot data out of incremental data. Data Loading scenarios are as follows to prepare the snapshot data set:
1. Day 1: Load full snapshot data as part of day 1 load to the Staging area and process the same to Results area.
2. Day 2: Receive incremental data in the Staging area and process the same to Results area.
3. Day 2: Create a customized T2T to copy the previous day data from results table excluding the records that are no more part of the load, for which you have received data as part of today’s incremental load, and reload the same to results table with Current Date Surrogate key and Run Surrogate key.
Day 2 steps should be followed for subsequent loads.