I.1.1 Description

This batch is used to load account-level attributes from individual Instrument tables to the FSI_D_INST_SUMMARY table.

The inputs to the batch are (in addition to Batch run ID and FIC MIS date, which are implicit parameters passed to all Batches by the Batch framework):

  1. P_FAIL_ON_ERROR: This is an optional parameter. The default value is Y. If the parameter value is Y, then the execution stops at that point where it encounters an error during the execution, and the Batch returns a failure status. On re-executing the batch with the same input parameter values, the DT skips the tables that have been successfully loaded and resumes loading the table that failed during the last execution.

    If the parameter value is passed as N, then the execution continues even if there is an error, and the Batch returns status as Success. Check Instrmnt_summary_load_status and fsi_message_log tables to see if all Instrument tables are loaded successfully or not. The instrmnt_summary_load_status.load_status_flag indicates whether the load was S (successful) or F (failed).

    On re-executing, the batch execution reloads all tables without skipping the tables that were loaded successfully in the previous execution.

  2. P_START_DATE and P_END_DATE: These two are optional parameters. The default value is NULL. If the value for these parameters is passed then the records from Instrument tables will be selected based on these dates. i.e., those records whose as_of_date falls between P_START_DATE and P_END_DATE will be selected (both inclusive).

    If these parameter values are NULL, then the records from Instrument tables will be selected based on the input FIC MIS date, i.e., those records whose as_of_date equals the input FIC MIS date.

  3. P_TARGET_TABLE: This is an optional parameter. The default value for this parameter is FSI_D_INST_SUMMARY. This is the target Instrument Summary table name which would be loaded from the individual Instrument tables.

Note:

Following is the sample request parameters, in case you don’t want to use defaults:
P_FAIL_ON_ERROR => 'N',
P_START_DATE => '20210101',
P_END_DATE => '20210131',
P_TARGET_TABLE => 'FSI_D_INST_SUMMARY'

The list of Instrument tables from which the attributes of the account will be loaded are:

  • FSI_D_ANNUITY_CONTRACTS
  • FSI_D_BORROWINGS
  • FSI_D_BREAK_FUNDING_CHARGES
  • FSI_D_CASA
  • FSI_D_CREDIT_CARDS
  • FSI_D_CREDIT_LINES
  • FSI_D_FUTURES
  • FSI_D_FX_CONTRACTS
  • FSI_D_GUARANTEES
  • FSI_D_INVESTMENTS
  • FSI_D_LEASES
  • FSI_D_LOAN_CONTRACTS
  • FSI_D_MERCHANT_CARDS
  • FSI_D_MM_CONTRACTS
  • FSI_D_MORTGAGES
  • FSI_D_MUTUAL_FUNDS
  • FSI_D_OTHER_SERVICES
  • FSI_D_RETIREMENT_ACCOUNTS
  • FSI_D_SWAPS
  • FSI_D_TERM_DEPOSITS
  • FSI_D_TRUSTS

The SETUP_PARAMETERS_MASTER table can be configured with the list of tables to be excluded from the load. For each table name to be excluded, create a row in the SETUP_PARAMETERS_MASTER table with param_name as EXCLUDE_FROM_INSTRMNT_SUMMARY_LOAD, and param_value as the Instrument table name to be excluded. The table names that are excluded from the load will be written to fsi_message_log.

The DT uses the following column UDPs for the purpose mentioned in the below table. Any user-defined column added in the instrument tables must also be added in FSI_D_INST_SUMMARY as well, along with assigning UDP_EXPORT_PFT_OUTPUT. Please refer DMU guide <section/page number> for additional details regarding the user-defined properties.

The following PL/SQL is an example for executing the Load Instrument procedure directly on the schema:

declare retVal number;
declare
retVal
          number; beginretVal := FN_LOAD_INSTRMNT_SUMMARY(P_BATCH_RUN_ID  
          => 'OFSPFTINFO_LOAD_INSTRMNT_SUMMARY_20210131_1',P_FIC_MIS_DATE  =>
        '20210131',
                P_FAIL_ON_ERROR => 'N',
                P_START_DATE    => '20210101',
                P_END_DATE      => '20210131',
                P_TARGET_TABLE  => 'FSI_D_INST_SUMMARY');
end;
/