'ORA-00904: "OFSA_IDT_ROLLUP"."NODE_0": Invalid Identifier' on Allocation with Large Hierarchy Filter

(Doc ID 2187854.1)

Last updated on MAY 01, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 8.0.0 to 8.0.2 [Release 8]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

In Oracle Financial Services Profitability Management (PFT) 8.0, Allocation rules that use a large hierarchy filter to select source data are failing.  The following ORA-00904 error exists in the ofspa.xxxxx.log in $FIC_DB_HOME/log/FusionApps:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [0] - SQL function not found! Oracle Error:
ORA- 00904: "OFSA_IDT_ROLLUP"."NODE_0": invalid identifier Driver
Function: drv_oci::Execute() SQL Statement:
CREATE GLOBAL TEMPORARY TABLE GLBL24212TMP ON COMMIT PRESERVE ....


In the hierarchy filter code, not all node values have a correct label:

WHERE    NAT_ACCT_ID IN (
SELECT   LEAF_NODE
FROM     OFSA_IDT_ROLLUP
WHERE    OFSA_IDT_ROLLUP.SYS_ID_NUM = 205226
AND      (OFSA_IDT_ROLLUP.NODE_10 IN (
SELECT   REV_HIERARCHY_FILTER.LEAF_NODE
FROM     REV_HIERARCHY_FILTER
WHERE    REV_HIERARCHY_FILTER.SYS_ID_NUM = 205700
AND      REV_HIERARCHY_FILTER.HIERARCHY_LEVEL = 3 )
OR       OFSA_IDT_ROLLUP.NODE_09 IN (
SELECT   REV_HIERARCHY_FILTER.LEAF_NODE
FROM     REV_HIERARCHY_FILTER
WHERE    REV_HIERARCHY_FILTER.SYS_ID_NUM = 205700
AND      REV_HIERARCHY_FILTER.HIERARCHY_LEVEL = 4 )
OR       OFSA_IDT_ROLLUP.NODE_08 IN (
SELECT   REV_HIERARCHY_FILTER.LEAF_NODE
FROM     REV_HIERARCHY_FILTER
WHERE    REV_HIERARCHY_FILTER.SYS_ID_NUM = 205700
AND      REV_HIERARCHY_FILTER.HIERARCHY_LEVEL = 5 )
OR       OFSA_IDT_ROLLUP.NODE_07 IN (
SELECT   REV_HIERARCHY_FILTER.LEAF_NODE
FROM     REV_HIERARCHY_FILTER
WHERE    REV_HIERARCHY_FILTER.SYS_ID_NUM = 205700
AND      REV_HIERARCHY_FILTER.HIERARCHY_LEVEL = 6 )
OR       OFSA_IDT_ROLLUP.NODE_0- 44792763 IN (
SELECT   REV_HIERARCHY_FILTER.LEAF_NODE
FROM     REV_HIERARCHY_FILTER
WHERE    REV_HIERARCHY_FILTER.SYS_ID_NUM = 205700
AND      REV_HIERARCHY_FILTER.HIERARCHY_LEVEL = 44792776 )
etc.


As a result of the bad code for the NODE name, the error is triggered and the Allocation fails.

You already ran the Reverse Population program to refresh the data in OFSA_IDT_ROLLUP.  The error still occurs.

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