Incorrect Translation of PFT Expressions Using IF Function with '<>' Operator (Doc ID 1640433.1)

Last updated on OCTOBER 04, 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 Profitability Management (PFT)

Symptoms

In PFT, when using an expression containing IF and '<>' operator as source for an allocation, the allocation returns only the value typed in the else clause.

Expression example:

IF(FSI_D_LOAN_CONTRACTS.CUR_NET_RATE <>0, (FSI_D_LOAN_CONTRACTS.CUR_BOOK_BAL/FSI_D_LOAN_CONTRACTS.CUR_NET_RATE+FSI_D_LOAN_CONTRACTS.LOAN_LOSS_PROVISION), 0)

The allocation engine incorrectly translates the IF clause in an incorrect DECODE statement, which always choses the ELSE clause:

SELECT IDENTITY_CODE, ID_NUMBER, (DECODE((NVL(a.CUR_NET_RATE, 0)
- 0), 0, 0, - ABS(NVL(a.CUR_NET_RATE, 0) - 0),
(NVL(a.CUR_BOOK_BAL, 0)/ NVL(a.CUR_NET_RATE, 0)+
NVL(a.LOAN_LOSS_PROVISION, 0)), 0)) * 0.08 / - 100
* NVL(TRANSFER_RATE, 0) TARGET_AMOUNT
from FSI_D_LOAN_CONTRACTS a

The issue is in the -ABS(NVL(a.CUR_NET_RATE, 0) - 0) condition, as the condition to be fulfilled is not a negative number. This is the reason why the DECODE will always return the ELSE clause, which is 0.

The source is well interpreted by the engine when changing the IF expression to match 0:
IF(FSI_D_LOAN_CONTRACTS.CUR_NET_RATE=0, 0, (FSI_D_LOAN_CONTRACTS.CUR_BOOK_BAL/FSI_D_LOAN_CONTRACTS.CUR_NET_RATE+FSI_D_LOAN_CONTRACTS.LOAN_LOSS_PROVISION))

SELECT IDENTITY_CODE, ID_NUMBER, (DECODE(NVL(a.CUR_NET_RATE, 0),
0, 0, (NVL(a.CUR_BOOK_BAL, 0)/ NVL(a.CUR_NET_RATE, 0)+
NVL(a.LOAN_LOSS_PROVISION, 0)))) * 0.08 / - 100 *
NVL(TRANSFER_RATE, 0) TARGET_AMOUNT
from FSI_D_LOAN_CONTRACTS a

Steps to reproduce

The issue can be reproduced at will with the following steps:
1. Create an allocation having a filter expression using an IF statement with <> condition
2. Execute the allocation and check the 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