5.12.1 Cash Flow Loader Table

• STG_ACCOUNT_CASH_FLOW: This table is used to store the cash flow generated by the different sources for loading purpose. This is a staging table where ALM expected cashflows data to be loaded from back office/bank. There is no dependency of FSI_D tables to load this table.

Data is expected as per following details:

Column name	Data expectation
Extraction Date (fic_mis_date)	MIS date for which the given data is valid, also called As of Date
Cash Flow Date (d_cash_flow_date)	Calendar date on which cash flow or other event occurs
Id Number (n_account_id)	This is equivalent of ID_NUMBER in EPM processing tables (ex: FSI_D_LOAN_CONTRACTS) and is used to map cash flows with their corresponding Instrument record. If aggregated cash flows are loaded then this column can be defaulted to -1
Identity Code (n_acct_data_identity_cd)	This is equivalent of IDENTITY_CODE in EPM processing tables (ex: FSI_D_LOAN_CONTRACTS) and stores as of date in number (YYYYMMDD) format
Cash Flow Amount (n_cash_flow_amount)	This column stores the cash flow or other amount, depending on financial element, on event date
Cash Flow Sequence (n_cash_flow_sequence)	Sequence in which event occurs is mentioned here. It can be a running number and is used to identify order in which an event occurs if there are multiple events on same date.
Currency Type Code (n_currency_type_cd)	This column decides whether the given cash flows are for Base (Natural) currency or consolidated (Reporting) currency. Based on it loader will move to either RES_DTL_XXX or CONS_DTL_XXX table. Expected values are: ‘1’ for base/natural (also called entered) currency and ‘2’ for consolidated/reporting (also called functional) currency. Corresponding reference tables are FSI_CURRENCY_TYPE_CD and FSI_CURRENCY_TYPE_MLS
	· If you have selected Consolidate to Reporting Currency in ALM Process, then following cases are possible with respect to Consolidation Flag, n_currency_type_cd =1, and n_currency_type_cd = 2.
	· Case 1: If Consolidation Flag is OFF and N_Currency_Type_Cd = 1, then process will execute successfully and only RES_DTL_XX will get populated.
	· Case 2: If Consolidation Flag is OFF, N_Currency_Type_Cd = 1, and N_Currency_Type_Cd = 2, then process will execute successfully and only RES_DTL_XX will populate for records N_Currency_Type_Cd=1 and ignore records of type N_Currency_Type_Cd=2.
	· Case 3: If Consolidation Flag is ON and N_Currency_Type_Cd = 1, then Loader would fail as it expects reocrds for N_Currency_Type_Cd=2 when CONSOLIDATED_OUTPUT_FLG is ON.
	· Case 4: If Consolidation Flag is ON, N_Currency_Type_Cd 1, and N_Currency_Type_Cd = 2, then it logs as No Data in the instrument table for the given FIC MIS DATE.Loading data to fsi_o_consolidated_master Failed error in FSI_MESSAGE_LOG. You need to load data if Consolidation Flag is ON and N_Currency_Type_Cd = 2. Here, N_Currency_Type_Cd = 2 signifies that the records are of consolidation type and meant for CONT_DTL once processing them. Similarly, N_Currency_Type_Cd = 1 indicates that non-consolidated records in stage and meant for RES_DLT processing.
	· Note: If there are records for n_currency_type_cd = 1 and n_currency_type_cd =2 does not records, then Cash Flow Loader cannot use the same set of records loaded for n_currency_type_cd = 1 and convert it for consolidation.
Instrument Type Code (n_instrument_type_cd)	This identifies the type of instrument that is, loan, deposit and so on. for which data is being loaded. Corresponding reference tables are FSI_INSTRUMENT_TYPE_CD and FSI_INSTRUMENT_TYPE_MLS
Scenario Number (n_scenario_no)	An ALM process can have multiple forecast rate scenario. This column indicates the scenario for which data has been loaded. It is used by loader to map data to corresponding scenario of ALM process. Reference tables are FSI_CASH_FLOW_LOADER_SETUP (scenario_num) and DIM_FCST_RATES_SCENARIO (n_scenario_num). Loader takes ALM Process Id as input and then checks corresponding scenario numbers in ALM metadata tables for validation
Account / Contract Code (v_account_number)	This column stores the alpha-numeric unique account or contract number for which data is being loaded. This is generally the unique identifier from operational source systems. Corresponding reference table in DIM_ACCOUNT (v_account_number). If aggregated cash flows are loaded then this column can be defaulted to -1
Cash Flow Type (v_cash_flow_type)	Indicates whether the cash flow is Inflow or Outflow. Values expected are 'I' for inflow and 'O' for outflow. Note that, this column is not used by cash flow loader, hence does not impact any result in ALM. It is used by Liquidity Risk Management (LRM) application and is a mandatory column in table.
Currency Code (v_ccy_code)	Three letter ISO currency code in which the cash flow amount is denominated must be given in this column.
Common Coa Code (v_common_coa_code)	Common Chart of Account code of the account number for which data is loaded must be given here. Corresponding reference table is DIM_COMMON_COA_B (common_coa_code)
Data Origin (v_data_origin)	Code of the source system from where data is obtained is expected here. Corresponding reference table is DIM_DATA_ORIGIN (v_data_source_code)
Financial Element Code (v_financial_element_code)	This indicates the financial element that is, nature of amount loaded. Corresponding reference table is DIM_FINANCIAL_ELEMENTS_B (financial_elem_code)
Gl Account Code (v_gl_account_code)	General Ledger Account code of the account number for which data is loaded must be given here. Corresponding reference table is DIM_GENERAL_LEDGER_B (gl_account_code)
Lv Code (v_lv_code)	Legal Entity code of the account number for which data is loaded must be given here. Corresponding reference table is DIM_LEGAL_ENTITY_B (legal_entity_code)
Organization Unit Code (v_org_unit_code)	Organisation or Business Unit code of the account number for which data is loaded must be given here. Corresponding reference table is DIM_ORG_UNIT_B (org_unit_code)
Product Code (v_prod_code)	Product code of the account number for which data is loaded must be given here. Corresponding reference table is DIM_PRODUCTS_B (product_code)

• Output tables: Aggregated Cash Flows will be populated in the following output tables
  • RES_DTL_XX
  • CONS_DTL_XX
  • FSI_O_RESULT_MASTER
  • FSI_O_CONSOLIDATED_MASTER

  XX denotes the Process ID.

• SETUP_MASTER: This table will be used in the case of instrument cash flows. For Instrument cash flows, an entry against V_COMPONENT_VALUE of the SETUP_MASTER table should have values either 0 or 1 which indicate if id numbers or account numbers are provided, respectively.
• FSI_ALM_DETERMINISTIC_PROCESS: This table will be used for loading cash flows in CONSOLIDATED tables. To populate consolidated tables, the CONSOLIDATED_OUTPUT_FLG should be 1 in the fsi_alm_deterministic_process table against the cash flow process ids.
• FSI_CASH_FLOW_LOADER_SETUP: This table will have all the process ids for cash flow loader. Only those processes will be executed which have status 'N' in FSI_CASH_FLOW_LOADER_SETUP table. In such case, those processes will be already existing into the system.
• FSI_M_USER_ACTIVE_TIME_BUCKETS: For cash flow loader, user should be mapped to an active time bucket in the FSI_M_USER_ACTIVE_TIME_BUCKETS table.
• TIME BUCKETS: The following tables will store the time bucket details:
  • FSI_TIME_BUCKET_MASTER
  • FSI_M_LR_IRR_BUCKETS
  • FSI_LR_IRR_BUCKETS_AUX
  • FSI_TIME_BKT_ISB
  • FSI_TIME_BKT_LR_LRR_DATES