My Oracle Support Banner

14Q A2: Calculation Of Percent Loss Severity (3 Month) (Doc ID 2605973.1)

Last updated on NOVEMBER 06, 2019

Applies to:

Oracle Financial Services - Regulatory Reporting for US Federal Reserve - Lombard Risk Integration Pack - Version 8.0.8 and later
Information in this document applies to any platform.

Goal

There are 2 issues here:

Percent Loss Severity (3 month Lagged), PERCENT_LOSS_SEVERITY:

1) Line item is using BP: 'bppls302' which internally uses 'BP: bpfd4572'[ SUM(DEFD4571.MSRECO02 + DEFD4571.MSRECO01 + DEFD4571.MSRECO03))]. These 3 components - N_CHARGE_RECOVERY_AMT, N_INT_RECOVERY_AMT, N_PRIN_RECOVERY_AMT are being sourced from STG_ACCT_RECOVERY_DETAILS table along with N_EOP_CURR_PRIN_BAL from respective PP table (ex:STG_LOAN_CONTRACTS).

Apart from the above, configuration has a nested derived entity (DEFD4570) with the below query to get the min write_off date. however, there is no STG_ACCT_WRITE_OFF_DETAILS.D_WRITE_OFF_DATE in the lineage which is required

SELECT
  fct_acct_write_off_details.n_acct_skey AS hiqain06,
  fct_acct_write_off_details.n_acct_skey AS pk_hiqain06,
  dim_run.v_run_id AS hiqrtr06,
  dim_run.n_run_skey AS pk_hiqrtr06,
  MIN(dim_dates.d_fiscal_month_end_date) AS bpfd4570
FROM
  fct_acct_write_off_details
  INNER JOIN dim_dates ON fct_acct_write_off_details.n_write_off_date_skey = dim_dates.n_date_skey
  INNER JOIN dim_run ON dim_run.n_run_skey = fct_acct_write_off_details.n_run_skey

2. Formula used is: CASE WHEN SUM(Outstanding Principal Balance) > 0 THEN (Outstanding Principal Balance - SUM(Interest Recovery Amount + Principal recovery Amount + Charge Recovery Amount)) / Outstanding Principal Balance)) ELSE 0 END
Instruction says "Report the total loss net of all recoveries as a percent of the unpaid principal balance (UPB) for all accounts", Filter to capture Losses alone is not present in the current configuration

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.