Incorrect Merge SQL Generated When Using an Expression in an Allocation (Doc ID 1907151.1)

Last updated on MAY 05, 2016

Applies to:

Oracle Financial Services Profitability Management - Version 6.1.0.3 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

On Oracle Financial Services Profitability Management (PFT) 6.1.0.3, when running a Dynamic Driver percent distribution allocation using an Expression which sums two columns for the Driver, an incorrect merge SQL is generated for the driver which leads to incorrect results.

ACTUAL BEHAVIOR

Merge SQL OFSAA is doing this:
(…)
SELECT a.IDENTITY_CODE, a.ID_NUMBER, (
  SUM(NVL(a.MISC_ALLOC_ASSETS, 0)+ NVL(a.MISC_ALLOC_LIABILITIES,
  0)) OVER() ) ColVal , ( SUM(NVL(a.MISC_ALLOC_ASSETS, 0)+
  NVL(a.MISC_ALLOC_LIABILITIES, 0)) OVER(PARTITION BY
  COMMON_COA_ID, ISO_CURRENCY_CD)) mTotal , COMMON_COA_ID,
  ISO_CURRENCY_CD
from FSI_D_LOAN_CONTRACTS a

(…)

EXPECTED BEHAVIOR

Ex. (with no sum)
SELECT a.IDENTITY_CODE, a.ID_NUMBER, ( NVL(a.MISC_ALLOC_ASSETS, 0)+ NVL(a.MISC_ALLOC_LIABILITIES, 0)) ColVal ,
( SUM(NVL(a.MISC_ALLOC_ASSETS, 0)+
  NVL(a.MISC_ALLOC_LIABILITIES, 0)) OVER(PARTITION BY
  COMMON_COA_ID, ISO_CURRENCY_CD)) mTotal , COMMON_COA_ID,
  ISO_CURRENCY_CD
from FSI_D_LOAN_CONTRACTS a


OR

Ex. (with sum, but defined partition)
SELECT a.IDENTITY_CODE, a.ID_NUMBER, (
  SUM(NVL(a.MISC_ALLOC_ASSETS, 0)+ NVL(a.MISC_ALLOC_LIABILITIES,
  0)) OVER(PARTITION BY ID_NUMBER) ) ColVal , ( SUM(NVL(a.MISC_ALLOC_ASSETS, 0)+
  NVL(a.MISC_ALLOC_LIABILITIES, 0)) OVER(PARTITION BY
  COMMON_COA_ID, ISO_CURRENCY_CD)) mTotal , COMMON_COA_ID,
  ISO_CURRENCY_CD
from FSI_D_LOAN_CONTRACTS a


IMHO, it should work similar to normal dynamic driver allocation with single column as a driver. In this allocation this part of sql looks like this:
(…)
SELECT a.IDENTITY_CODE, a.ID_NUMBER, (MISC_ALLOC_ASSETS)
  ColVal, (SUM (MISC_ALLOC_ASSETS) OVER(PARTITION BY COMMON_COA_ID,
  ISO_CURRENCY_CD)) mTotal , COMMON_COA_ID, ISO_CURRENCY_CD
from FSI_D_LOAN_CONTRACTS a

(…)

Steps to reproduce

1. Create Expression which adds two columns on instrument table
2. Create allocation with source and Ledger_Stat, driver using Expression and output to Instrument table
3. Check results

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms