Allocation With Expression on User Defined Portfolio Column Either Fails with ORA-00904 or Gives Incorrect Results (Doc ID 2158141.1)

Last updated on JULY 06, 2016

Applies to:

Oracle Financial Services Profitability Management - Version 8.0.0 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)
User Defined Field
Custom Column

Symptoms

On PFT 8.0.2, an allocation using a Portfolio with Expression fails with ORA-00904 errors:

ERROR
Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(), oexn() SQL Function: [189] - SQL function not found! Oracle Error: ORA- 00904: "A"."XX_COM_REC_OTHER_COMMISSIONS": invalid identifier Driver Function: drv_oci::Execute()"
and

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [189] - SQL function not found! Oracle
Error: ORA- 00904: "A"."XX_DAILY_ACCRUAL_BASIS": invalid
identifier Driver Function: drv_oci::Execute()

OR

"Oracle Error: ORA-00942: table or view does not exist" errors

Have created a Static Driver allocation on Portfolio Table having as source an Expression: TRANSFER_RATE * CUR_PAR_BAL * XX_DAILY_ACCRUAL_BASIS.  The XX_DAILY_ACCRUAL_BASIS is a custom column.

ADDITIONAL INFORMATION

1. When creating a similar expression but only with the standard columns (TRANSFER_RATE * CUR_PAR_BAL) the allocation is successful.
2. When using the original expression in an allocation having as source an individual instrument table (FSI_D_TERM_DEPOSITS) the allocation is successful.

In addition, an allocation with an Expression on Portfolio field does not fail, but gives incorrect results.  In this case, the currency conversion is not correct.

The issue can be reproduced at will with the following steps:
1. Create Static Driver allocation on Portfolio Table having as source an Expression

OR

1. Create a Portfolio expression like CUR_PAR_BAL * TRANSFER_RATE * XX_DAILY_ACCRUAL_BASIS_CD
2. Create an allocation using the same expression with Source Table = Portfolio
==> Check the results and PFT debug log file for errors.

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