Cannot Create Data Filter on Some Simple Dimension Columns in OFSAAI 7.3.3

Last updated on JANUARY 17, 2014

Applies to:

Symptoms

In Oracle Financial Services Analytical Applications (OFSAA) 7.3.3, you cannot create a Data Filter on certain Simple Dimensions (ex. Account Officer).  When you select the Simple Dimension in the "Data Element Selection" list, the following message appears on the screen:

No Data Found

Example:

When you check the FUSIONSUBSYSTEMSService.log in the Web Server's OFSAA log directory, you find an error like the following:

[FUSIONSUBSYSTEMS] Nov 22,13 09:41:47 : [COMMONFUNCTIONS]Query to get the code values.. select b.ACCOUNT_OFFICER_CD,tl.ACCOUNT_OFFICER from FSI_ACCOUNT_OFFICER_CD b, FSI_ACCOUNT_OFFICER_MLS tl where b.ACCOUNT_OFFICER_CD = tl.ACCOUNT_OFFICER_CD AND (UPPER(tl.ACCOUNT_OFFICER) like UPPER(coalesce(?,tl.ACCOUNT_OFFICER)) OR ( b.ACCOUNT_OFFICER_CD IN (''))) AND TL.MLS_CD = ? order by tl.ACCOUNT_OFFICER
[FUSIONSUBSYSTEMS] Nov 22,13 09:41:47 : [COMMONFUNCTIONS]SQL Exception occured while trying to retrieve code values.
[FUSIONSUBSYSTEMS] Nov 22,13 09:41:47 : [COMMONFUNCTIONS]
java.sql.SQLSyntaxErrorException: ORA-00904: "TL"."MLS_CD": invalid identifier

The error appears to occur because the Simple Dimension's "_MLS" table does not have an MLS_CD column.  However, according to the Oracle Financial Services Data Model Utility Guide, a simple dimension's "_MLS" table does not require an MLS_CD column.

Steps to Reproduce:

1. Go to Financial Services Applications
2. Go to Master Maintenance > Filters
3. Create a new Data Element Filter
4. In Data Element Selection, select a simple dimension column (ex. Account Officer Code)

After selecting the column, the "No Data Found" message appears.

Cause