FFIEC009 consolidated Issues (Doc ID 2740310.1)

Last updated on JUNE 21, 2021

Applies to:

Goal

Issue 1-

The hierarchy HREG1179 :HIR - RR Reg Account Status is populated from COALESCE(DIM_REG_ACCT_STATUS.V_REG_ACCOUNT_STATUS_CD,'MSG')
This is populated from below join condition,
LEFT OUTER JOIN DIM_REG_ACCT_STATUS ON FCT_REG_FUND_LEND_DETAILS.N_REG_ACCT_STATUS_SKEY = DIM_REG_ACCT_STATUS.N_REG_ACCT_STATUS_SKEY

FCT_REG_FUND_LEND_DETAILS.N_REG_ACCT_STATUS_SKEY is populated from rule PRUS_FUND_LEND_ACC_REG_COLS_01

Then populated through RLUS_FRAS_REG_ACCT_STATUS_MAP which has below join condition,
INNER JOIN DIM_ACCT_STATUS ON DIM_ACCT_STATUS.N_ACCT_STATUS_SKEY = FCT_COMMON_ACCOUNT_SUMMARY.N_ACCT_STATUS_SKEY

And FCT_COMMON_ACCOUNT_SUMMARY.N_ACCT_STATUS_SKEY is populated as COALESCE(DIM_ACCT_STATUS.N_ACCT_STATUS_SKEY,NVL2(STG_LOAN_CONTRACTS.V_ACCT_STATUS_CODE,-1,0))
which is populated from below join condition,
LEFT OUTER JOIN DIM_ACCT_STATUS ON DIM_ACCT_STATUS.V_D_ACCOUNT_STATUS = STG_LOAN_CONTRACTS.V_ACCT_STATUS_CODE AND DIM_ACCT_STATUS.F_LATEST_RECORD_INDICATOR = 'Y'

Issue - The hierarchy expects value 'TRDIFN' but this V_D_ACCOUNT_STATUS is not available in DIM_ACCT_STATUS. Due to this hierarchy will never get populated.

-------------------------

Issue 2- The hierarchy HREG1338 HIR - RR Reg Reporting Line expects FFIEC009CL1,FFIEC009CL10,FFIEC009CL2,FFIEC009CL3,FFIEC009CL4,FFIEC009CL5,FFIEC009CL6,FFIEC009CL7,FFIEC009CL8,FFIEC009CL9 which utilizes multiple rules to populated the expected member code,
RLFD_FRFLRM_REG_RL_009C_01
RLFD_FRFLRM_REG_RL_009C_02
RLFD_FRFLRM_REG_RL_009C_03
RLFD_FRFLRM_REG_RL_009C_04
RLFD_FRFLRM_REG_RL_009C_05
RLFD_FRFLRM_REG_RL_009C_07
RLFD_FRFLRM_REG_RL_009C_08

Corresponding rules fail for few of the below mentioned issues,
Potential Issue 1 and 2:
• RLFD_FRFLRM_REG_RL_009C_02/ RLFD_FRFLRM_REG_RL_009C_05:
This rule uses the condition a_dim_boolean_rep_dom_acc_flag.v_flag_code = 'Y' which is populated from below join,
INNER JOIN dim_boolean_flags a_dim_boolean_rep_dom_acc_flag ON a_dim_boolean_rep_dom_acc_flag.v_flag_code = coalesce(fct_reg_fund_lend_details.f_domestic_customer_flag, N')
FCT_REG_FUND_LEND_DETAILS.F_DOMESTIC_CUSTOMER_FLAG populated from DECODE (DIM_ORG_STRUCTURE.V_COUNTRY_ID, DIM_PARTY.V_ISO_COUNTRY_CD, 'Y', 'N')
DIM_PARTY.V_ISO_COUNTRY_CD is completely NULL which is populated from STG_PARTY_MASTER.V_ISO_COUNTRY_CD which is completely NULL.
Due to this the whole rule fails to classify.

• RLFD_FRFLRM_REG_RL_009C_03:
This rule uses the condition a_dim_cust_lv_ownership_type.v_ownership_type = 'PSE' which is populated from below join,
INNER JOIN DIM_OWNERSHIP_TYPE A_DIM_CUST_LV_OWNERSHIP_TYPE ON FCT_REG_FUND_LEND_DETAILS.N_CUST_LV_OWNERSHIP_TYPE_SKEY = A_DIM_CUST_LV_OWNERSHIP_TYPE
.N_OWNERSHIP_TYPE_SKEY
FCT_REG_FUND_LEND_DETAILS.N_CUST_LV_OWNERSHIP_TYPE_SKEY is populated from below condition,
COALESCE(A_DIM_CUST_OWNERSHIP_TYPE.N_OWNERSHIP_TYPE_SKEY, NVL2(DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE, -1, 0))
Which has following join,
LEFT OUTER JOIN DIM_OWNERSHIP_TYPE A_DIM_CUST_OWNERSHIP_TYPE ON A_DIM_CUST_OWNERSHIP_TYPE.V_OWNERSHIP_TYPE = DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE AND DIM_DATES.D_CALENDAR_DATE BETWEEN A_DIM_CUST_OWNERSHIP_TYPE.D_RECORD_START_DATE AND A_DIM_CUST_OWNERSHIP_TYPE.D_RECORD_END_DATE
DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE is completely NULL. Due to this the rule fails to classify

• RLFD_FRFLRM_REG_RL_009C_07 and RLFD_FRFLRM_REG_RL_009C_08:
This rule uses a_dim_cust_lv_ownership_type.v_ownership_type which is populated from below join,
INNER JOIN DIM_OWNERSHIP_TYPE A_DIM_CUST_LV_OWNERSHIP_TYPE ON FCT_REG_FUND_LEND_DETAILS.N_CUST_LV_OWNERSHIP_TYPE_SKEY = A_DIM_CUST_LV_OWNERSHIP_TYPE
.N_OWNERSHIP_TYPE_SKEY
FCT_REG_FUND_LEND_DETAILS.N_CUST_LV_OWNERSHIP_TYPE_SKEY is populated from below condition,
COALESCE(A_DIM_CUST_OWNERSHIP_TYPE.N_OWNERSHIP_TYPE_SKEY, NVL2(DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE, -1, 0))
Which has following join,
LEFT OUTER JOIN DIM_OWNERSHIP_TYPE A_DIM_CUST_OWNERSHIP_TYPE ON A_DIM_CUST_OWNERSHIP_TYPE.V_OWNERSHIP_TYPE = DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE AND DIM_DATES.D_CALENDAR_DATE BETWEEN A_DIM_CUST_OWNERSHIP_TYPE.D_RECORD_START_DATE AND A_DIM_CUST_OWNERSHIP_TYPE.D_RECORD_END_DATE
DIM_CUSTOMER.V_LV_OWNERSHIP_TYPE is completely NULL. Due to this the rule fails to classify

This rule uses the condition a_dim_boolean_rep_dom_acc_flag.v_flag_code = 'Y' which is populated from below join,
INNER JOIN dim_boolean_flags a_dim_boolean_rep_dom_acc_flag ON a_dim_boolean_rep_dom_acc_flag.v_flag_code = coalesce(fct_reg_fund_lend_details.f_domestic_customer_flag, N')
FCT_REG_FUND_LEND_DETAILS.F_DOMESTIC_CUSTOMER_FLAG populated from DECODE (DIM_ORG_STRUCTURE.V_COUNTRY_ID, DIM_PARTY.V_ISO_COUNTRY_CD, 'Y', 'N')
DIM_PARTY.V_ISO_COUNTRY_CD is completely NULL which is populated from STG_PARTY_MASTER.V_ISO_COUNTRY_CD which is completely NULL.
Due to this the whole rule fails to classify.

Potential Issue 3:

• RLFD_FRFLRM_REG_RL_009C_05
Rule has filter condition as
AND ( ( a_dim_business_domain_cust.v_business_domain_code = 'FACTOR' )
OR ( a_dim_business_domain_cust.v_business_domain_code = 'OTHFINT' ) ) which is populated from STG_PARTY_MASTER.V_BUSINESS_DOMAIN and this only has INS,PEFUNF,REIT,MFUND,RBPF,BRDEL which do not include the required data which is FACTOR and OTHFINT. Need clarification on the same.

Solution

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