DGS VA UAT2 - Summary V/s Drill Down Mismatch - Case Condition Logic (Doc ID 2824600.1)

Last updated on DECEMBER 05, 2021

Applies to:

Goal

Customer is facing issues with these BHCKK039 and BHCKK041 MDRM which has the Derived entity- DEFD1702. This derived entity is created with CASE WHEN EXPR1 THEN RES1 WHEN EXPR2 THEN RES2 ELSE RES3 END

Below is the snippet from the derived entity -DEFD1702
CASE
  WHEN SUM(COALESCE(DERG1000.BPRG1011, 0) + COALESCE(DERG1000.BPRG1012, 0) + COALESCE(DERG1000.BPRG1013, 0)) > 0
  AND SUM(COALESCE(FCT_REG_ACCOUNT_SUMMARY.N_SOV_GUARANTEED_AMT_RCY, 0)) > 0 THEN
  LEAST(SUM(COALESCE(DERG1000.BPRG1011, 0) + COALESCE(DERG1000.BPRG1012, 0) + COALESCE(DERG1000.BPRG1013, 0)), SUM
  (COALESCE(FCT_REG_ACCOUNT_SUMMARY.N_SOV_GUARANTEED_AMT_RCY, 0)))

This is created by group by columns and DGS has account level information to be included and the result is getting varied due to the above expression. Also customer observed that the upcoming release 80971 has removed the case when then concept and updated as below:
SUM(GREATEST(LEAST(COALESCE(FCT_REG_ACCOUNT_SUMMARY.N_SOV_GUARANTEED_AMT_RCY, 0), COALESCE(FCT_REG_ACCOUNT_SUMMARY.N_EOP_BAL_RCY , 0)), 0))
 

Solution

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