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

Last updated on APRIL 28, 2023

Applies to:

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

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