Cannot See Hierarchy Loaded with Lowercase Folder Name in Dimension Management > Hierarchies (Doc ID 1357452.1)

Last updated on JUNE 25, 2016

Applies to:

Oracle Financial Services Analytical Applications Infrastructure - Version 7.2 to 7.2.9 [Release 7]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Analytical Applications (OFSAA) Infrastructure 7.2.9, if you load a hierarchy into OFSAA using the fn_drmDataLoader program and the hierarchy has a lowercase or mixed case folder name in STG_HIERARCHIES_INTF (ex. 'ofsaaseg'), the hierarchy does not appear in the Dimension Management > Hierarchies screen.

The following SQL is listed in the FUSIONSUBSYSTEMSService.log in the web server directory:

[FUSIONSUBSYSTEMS] Aug 19,11 17:20:09 : [HIERARCHYHANDLER]Count Query to get
List of Hierarchies for the dimension : 2 : select count(1) from (SELECT
rev_hier.rowid hierarchyrowid, rev_hier.hierarchy_id, fmodtl.short_desc,
rev_hier.init_display_level, rev_hier.created_by,
to_char(rev_hier.creation_date, 'DS HH24:MI:SS') creation_date,
to_char(rev_hier.last_modified_date, 'DS HH24:MI:SS') last_modified_date,
fmodb.access_cd, fmodtl.long_desc, rev_hier.creation_date sort_creation_date,
rev_hier.last_modified_date sort_last_modified_date FROM rev_hierarchies
rev_hier, fsi_m_object_definition_b fmodb, fsi_m_object_definition_tl fmodtl
WHERE rev_hier.hierarchy_id = fmodb.object_definition_id AND
fmodb.object_definition_id = fmodtl.object_definition_id AND
rev_hier.dimension_id = ? AND fmodtl.lang_cd = ? AND fmodb.folder_name IN (?)
AND fmodb.folder_name = COALESCE(?, fmodb.folder_name) AND
UPPER(fmodtl.short_desc) like UPPER(COALESCE(?, fmodtl.short_desc)) ORDER BY
UPPER(fmodtl.short_desc) ASC)
[FUSIONSUBSYSTEMS] Aug 19,11 17:20:09 : [HIERARCHYHANDLER]Hierarchy Grid Bean
: null
[FUSIONSUBSYSTEMS] Aug 19,11 17:22:46 : [AMHMSERVLET]paramName:folderId --
paramValue:OFSAASEG

You can see rows associated with the hierarchy in the target hierarchy tables (REV_HIERARCHIES, DIM_<dimension>_HIER, etc.).

This issue only occurs for hierarchies loaded into OFSAA.  You can see hierarchies created using the interface.

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