No Hierarchies Found Searching for Business Hierarchies When Hierarchy Sub Type is Selected (Doc ID 1369844.1)

Last updated on OCTOBER 28, 2011

Applies to:

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

Symptoms

In Oracle Financial Services Analytical Applications (OFSAA) infrastructure, when you go to Business Metadata Management > Business Hierarchy and do a Search where you use Hierarchy Type = 'REGULAR' and Hierarchy Sub Type = 'Non Business Intelligence Enabled', the search returns no rows.  The same happens if you use a Sub Type of Business Intelligence Enabled or Parent Child.

If you change the Sub Type to blank and search, rows are returned.  There are BI Enabled rows and Non BI rows.  The search is not finding these rows when a Sub Type is selected.

If you check the UMMService.log in $FIC_APP_HOME/common/FICServer/logs, the following query exists:

SELECT MM.METADATA_CODE, SD.LOCALE_DESCRIPTION AS SHORTDESC, LD.LOCALE_DESCRIPTION AS LONGDESC, MM.METADATA_SUB_TYPE AS HIERTYPE, ENT.V_ELEMENT_VALUE AS ENTITY, ATTR.V_ELEMENT_VALUE AS ATTRIBUTE, PC.V_ELEMENT_VALUE AS PARENTCHILD, BE.V_ELEMENT_VALUE AS BIENABLED FROM METADATA_MASTER MM LEFT JOIN METADATA_LOCALE_MASTER LD ON LD.METADATA_KEY = MM.METADATA_CODE || ':LD' AND LD.METADATA_INFODOM = MM.DSN_ID AND LD.VERSION_NO = MM.METADATA_VERSION AND LD.METADATA_LOCALE = MM.METADATA_LOCALE, METADATA_LOCALE_MASTER SD, METADATA_ELEMENT_MASTER ENT, METADATA_ELEMENT_MASTER ATTR, METADATA_ELEMENT_MASTER PC, METADATA_ELEMENT_MASTER BE WHERE SD.METADATA_KEY = MM.METADATA_CODE || ':SD' AND SD.METADATA_INFODOM = MM.DSN_ID AND SD.VERSION_NO = MM.METADATA_VERSION AND SD.METADATA_LOCALE = MM.METADATA_LOCALE AND ENT.V_METADATA_CODE = MM.METADATA_CODE AND ENT.V_ELEMENT_CODE = 'ENTITY' AND ENT.V_ELEMENT_PARENT_CODE = 'HIERARCHY' AND ENT.N_METADATA_VERSION = MM.METADATA_VERSION AND ENT.V_METADATA_INFODOM = MM.DSN_ID AND ATTR.V_METADATA_CODE = MM.METADATA_CODE AND ATTR.V_ELEMENT_CODE = 'ATTRIBUTE' AND ATTR.V_ELEMENT_PARENT_CODE = 'HIERARCHY' AND ATTR.N_METADATA_VERSION = MM.METADATA_VERSION AND ATTR.V_METADATA_INFODOM = MM.DSN_ID AND PC.V_METADATA_CODE = MM.METADATA_CODE AND PC.V_ELEMENT_CODE = 'PARENTCHILD' AND PC.V_ELEMENT_PARENT_CODE = 'HIERARCHY' AND PC.N_METADATA_VERSION = MM.METADATA_VERSION AND PC.V_METADATA_INFODOM = MM.DSN_ID AND BE.V_METADATA_CODE = MM.METADATA_CODE AND BE.V_ELEMENT_CODE = 'BIENABLED' AND BE.V_ELEMENT_PARENT_CODE = 'HIERARCHY' AND BE.N_METADATA_VERSION = MM.METADATA_VERSION AND BE.V_METADATA_INFODOM = MM.DSN_ID AND MM.DSN_ID = 'OFSAA52INFOD' AND MM.METADATA_TYPE = 3 AND MM.IS_DELETED = 'N' AND MM.METADATA_VERSION = 0 AND UPPER(MM.METADATA_LOCALE) = UPPER('en_US') AND UPPER(MM.METADATA_CODE) LIKE UPPER('%%') AND UPPER(SD.LOCALE_DESCRIPTION) LIKE UPPER('%%') ORDER BY UPPER(METADATA_CODE) asc

If you try to run the query in SQL*Plus, the following error occurs:

ORA-00997: illegal use of LONG datatype

Steps to Reproduce:
  1. Go to Unified Metadata Manager
  2. Go to Business Metadata Management > Business Hierarchy
  3. In the Search area, select Hierarchy Type = 'Regular' and Hierarchy Sub Type = 'Non Business Intelligence Enabled'.
  4. Click the Search button
No data found.

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