ORA- 00936 Errors When Running Target Only Static Table Driver With Management Ledger (Doc ID 2163251.1)

Last updated on JULY 20, 2016

Applies to:

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

Symptoms

On Oracle Financial Services Profitability Management (PFT) 8.0.1, management ledger static table driver allocations with Target Only fail with errors:

ERROR

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [0] - SQL function not found! Oracle Error:
ORA- 00936: missing expression Driver Function:
drv_oci::Execute() SQL Statement: MERGE INTO
FSI_D_MANAGEMENT_LEDGER TARGET USING (
SELECT *
FROM (
SELECT (SOURCE_AMOUNT * 1)ENTERED_BALANCE, (FUN_AMT_V *
1)FUNCTIONAL_BALANCE, SRC_IOVW.*
FROM (
SELECT SUM(IVWSRC_AMOUNT * DECODE(ISO_CURRENCY_CD, 'AED',
... 8946992, 'TRL', 0, 'USD', 2.652519894, 'YER', 1, 'BHD', 1,
1)) SOURCE_AMOUNT, SUM(IVWSRC_AMOUNT) FUN_AMT_V, ORG_UNIT_ID,
GL_ACCOUNT_ID, PRODUCT_ID, LEGAL_ENTITY_ID, ACCOUNT_OFFICER_ID,
BRANCH_ID, RM_COA_ID, COMMON_COA_ID, ISO_CURRENCY_CD
FROM (
SELECT SUM( src_amount * IVW_SOURCE2.AMOUNT ) IVWSRC_AMOUNT,
IVW_SOURCE2.LEAF_1 COMMON_COA_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
PRODUCT_ID, LEGAL_ENTITY_ID, ACCOUNT_OFFICER_ID, BRANCH_ID,
RM_COA_ID, ISO_CURRENCY_CD
FROM (
SELECT *
FROM (SELECT SUM(DECODE( m.FISCAL_YEAR, 2015.000000 ,
DECODE(m.FISCAL_MONTH, 10.000000 , m.ENTERED_BALANCE, 0), 0))
src_amount , FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, ACCOUNT_OFFICER_ID,
BRANCH_ID, RM_COA_ID, ISO_CURRENCY_CD
FROM (GLBL22895TMP) m
GROUP BY FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, LEGAL_ENTITY_ID, ACCOUNT_OFFICER_ID,
BRANCH_ID, RM_COA_ID, ISO_CURRENCY_CD)Src, (
SELECT *
FROM VW_TID_LS_22895
WHERE LEAF_TYPE = 'K' ) Drv
WHERE ) IVW_SOURCE1 , (
SELECT DISTINCT LEAF_1 , AMOUNT * 1 / SUM(AMOUNT) OVER (PARTITION BY
PARENT_ROW_NUM) AMOUNT, PARENT_ROW_NUM
FROM VW_TID_LS_22895
WHERE LEAF_TYPE = 'T' ) IVW_SOURCE2
WHERE src_amount <> 0
GROUP BY ORG_UNIT_ID, GL_ACCOUNT_ID, PRODUCT_ID, LEGAL_ENTITY_ID,
...  SOURCE.ORG_UNIT_ID, SOURCE.PRODUCT_ID, SOURCE.RM_COA_ID)

In order to further investigate this issue, execute an allocation on Management Ledger having the Static Table Driver with both Key and Target in order to check how the LOG is created, and it was the following:

SELECT *
FROM VW_TID_LS_958
WHERE LEAF_TYPE = 'K' ) Drv
WHERE Src.COMMON_COA_ID = Drv.LEAF_1 ) IVW_SOURCE1 , (
SELECT DISTINCT LEAF_1 , AMOUNT * 1 / SUM(AMOUNT) OVER (PARTITION BY
 PARENT_ROW_NUM) AMOUNT, PARENT_ROW_NUM
FROM VW_TID_LS_958
WHERE LEAF_TYPE = 'T' ) IVW_SOURCE2

In comparison, for the 266919 allocation the code with the blank space is the following:

SELECT *
FROM VW_TID_LS_22895
WHERE LEAF_TYPE = 'K' ---key) Drv
WHERE ) IVW_SOURCE1 , (
SELECT DISTINCT LEAF_1 , AMOUNT * 1 / SUM(AMOUNT) OVER (PARTITION BY
 PARENT_ROW_NUM) AMOUNT, PARENT_ROW_NUM
FROM VW_TID_LS_22895
WHERE LEAF_TYPE = 'T' ---target ) IVW_SOURCE2

And the code for the 266937 allocation which was built on Ledger Stat and was successful is the following:

SELECT DISTINCT LEAF_1 , AMOUNT * 1 / SUM(AMOUNT) OVER (PARTITION BY
 PARENT_ROW_NUM) AMOUNT, PARENT_ROW_NUM
FROM VW_TID_LS_16459
WHERE LEAF_TYPE = 'T' ---target
AND AMOUNT <> 0 ) IVW_SOURCE2

The issue can be reproduced at will with the following steps:
1) Sys ID 266937: allocates one amount from one Ledger Stat FE to a different Ledger Stat FE, using a Target only Static Table Driver on COA ID. The Source contains distinct COA IDs which are being allocated proportionally towards 2 other COA IDs.

Status: the allocation was executed successfully and the results were written correctly.
Please find attached the LOG file. You will notice that the SQL created for driver skipped over the KEY static table’s dimension as it was not defined.

2) Sys ID 266919: allocates one amount from one Management Ledger FE to a different Management Ledger FE, using the same Static Table Driver as described above. The Source contains distinct COA IDs which are being allocated proportionally towards 2 other COA IDs.

Status: the allocation is failed, and the FusionApps LOG file indicates that the SQL code is missing the expression for the KEY Driver Element; the issue is that even if the KEY was not defined, the code created contains a blank and therefore cannot be executed.

Changes

 

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