Cannot Run Allocation ID with Large Tree Filter ID (Doc ID 178065.1)

Last updated on DECEMBER 23, 2011

Applies to:

Oracle Profitability Manager - Version: 4.5 to 4.5.39 - Release: 4.5 to 4.5
Information in this document applies to any platform.
Oracle Financial Services Applications (OFSA) 4.5
Oracle Performance Analzyer - Version: 4.5 to 4.5.39

Checked for relevance on 23-Dec-2011.

Symptoms

In Oracle Performance Analyzer (PA) 4.5, when running an Allocation ID that uses a very large Tree Filter ID, the Allocation fails with one of the following errors:

ORA-00920 Invalid Relational Operator error message
or
ORA-00936: missing expression
or
ORA- 00907: missing right parenthesis

The Allocation ID runs successfully after removing the Tree Filter ID or when using a smaller Tree Filter ID.

If you turn on the server-side SQL debug (<Note:197903.1>) and review the large SQL in the ofspa.xxxxx.log file generated by the Tree Filter ID, the SQL is incomplete.  For example:

select SUM(DECODE(a.year_s, 2007, a.month_08, 0) ),
a.ORG_UNIT_ID, a.COMMON_COA_ID
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=100
and a.ORG_UNIT_ID IN (SELECT leaf_node
FROM ofsa_idt_rollup
WHERE ofsa_idt_rollup.sys_id_num = 100038
and (ofsa_idt_rollup.leaf_node = 1000
or ofsa_idt_rollup.leaf_node = 1002
or ofsa_idt_rollup.leaf_node = 1003
.
.
.
or ofsa_idt_rollup.leaf_node = 9001
or ofsa_idt_rollup.leaf_node =)
and a.year_s IN (2007)
group by a.ORG_UNIT_ID, a.COMMON_COA_ID

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