Hierarchy Flattening is Failing in OFSAA When "ampersand" ("&") is Used in the Member Name
(Doc ID 2392116.1)
Last updated on MAY 11, 2020
Applies to:Oracle Financial Services Enterprise Financial Performance Analytics - Version 8.0.5 to 8.0.6 [Release 8]
Oracle Financial Services Asset Liability Management and Funds Transfer Pricing Analytics - Version 8.0.5 to 8.0.6 [Release 8]
Oracle Financial Services Analytical Applications Infrastructure - Version 8.0.5 to 8.0.6 [Release 8]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Enterprise Performance Management (EPM)
Oracle Business Intelligence Enterprise Edition (OBIEE)
Oracle Financial Services Asset Liability Management Analytics (ALMBI)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA/PFTBI) previously known as Oracle Financial Services Profitability Analytics
During hierarchy flattening, it has been observed that the Org Units as well as another custom dimension were failing whenever there was a presence of "&" in the respective _TL tables (e.g: dim_org_unit_tl).
Below entries were found in FSI_MESSAGE_LOG:
1465 20 Mode and Hier Id :2--123456
1465 20 Deleting Hier Id :123456
1465 20 vSQL: select rev_hier.hierarchy_id from rev_hierarchies rev_hier, rev_hier_definitions rev_hier_def where rev_hier.hierarchy_id =rev_hier_def.hierarchy_id and rev_hier_def.flattened_rows_completion_code='PENDING' and
rev_hier.dimension_Id = 1 and rev_hier.hierarchy_id = 123456
1465 20 Updating Hier Id :123456
1465 20 Batch Hierarchy Load Overload
1465 20 Successfully transformed hierarchy 123456
REV_HIER_TRANSFORMATON_BIAPPS.getTokenValueAt(NODE_NAME_PATH,3,'&') LEVEL_18_NAME, REV_HIER_TRANSFORMATON_BIAPPS.getTokenValueAt(NODE_PATH,2,'&') LEVEL_19_CODE, REV_HIER_TRANSFORMATON_BIAPPS.getTokenValueAt(NODE_NAME_PATH,2,'&') LEVEL_19_NAME, REV_HIER_TRANSFORMATON_BIAPPS.getTokenValueAt(NODE_PATH,1,'&') LEVEL_20_CODE, REV_HIER_TRANSFORMATON_BIAPPS.getTokenValueAt(NODE_NAME_PATH,1,'&') LEVEL_20_NAME
Error while executing hierachy Query
Oracle Error User-Defined Exception
STEPS TO REPRODUCE
The issue can be reproduced at will with the following steps:
1. create Organizational Unit member
2. Edit ORG_UNIT member to contain "&":
3. Create batch for running hierarchy flattening.
4. Execute batch as showed in UI screenshots.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document