My Oracle Support Banner

Allocation Fails With 'ORA- 00936: missing expression' on Hierarchy Filter SQL (Doc ID 2800210.1)

Last updated on AUGUST 31, 2021

Applies to:

Oracle Financial Services Profitability Management - Version 8.1.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Profitability Management (PFT)

Symptoms

When executing an Allocation with a Hierarchy Filter, the Allocation fails.  The following ORA-00936 error is output to the Allocation Engine log:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Error: 936 ORA- 00936: missing
expression SQL:
CREATE GLOBAL TEMPORARY TABLE GLBL32291TMP ON COMMIT PRESERVE
       ROWS AS
SELECT *
FROM (SELECT mv.AS_OF_DATE, mv.BALANCE_TYPE_CD, mv.COMMON_COA_ID,
       mv.CONSOLIDATION_CD, DECODE(FISCAL_YEAR, 1999,
       DECODE(mv.ISO_CURRENCY_CD, '', mv.ENTERED_BALANCE,
       mv.ENTERED_BALANCE), ENTERED_BALANCE) ENTERED_BALANCE,
       mv.FINANCIAL_ELEM_ID, mv.FISCAL_MONTH, mv.FISCAL_YEAR,
       mv.FUNCTIONAL_BALANCE, mv.GL_ACCOUNT_ID, mv.IDENTITY_CODE,
       mv.ISO_CURRENCY_CD, mv.LEGAL_ENTITY_ID, mv.LOB_ID,
       mv.ORG_UNIT_ID, mv.PRODUCT_ID, mv.STRATEGY_ID
FROM FSI_D_MANAGEMENT_LEDGER mv) m
WHERE PRODUCT_ID IN (
SELECT LEAF_NODE
FROM OFSA_IDT_ROLLUP
WHERE OFSA_IDT_ROLLUP.SYS_ID_NUM = 200040
AND ())
AND FISCAL_YEAR = 1999.000000
AND FISCAL_MONTH = 12.000000
AND CONSOLIDATION_CD = 100.000000
AND FINANCIAL_ELEM_ID = 100.000000


When the same hierarchy filter is used in FTP processing, the process ends without any error.

Cause

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
Symptoms
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.