Ledger Stat Dynamic And Static Driver Allocation ISO Currency Code Invalid Identifier (Doc ID 1607581.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Financial Services Profitability Management - Version 6.1.0.1 and later
Information in this document applies to any platform.

Symptoms

When attempting to run Allocation Rules using static and dynamic driver they fail due to the following error:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] -
oexec(),
oexn() SQL Function: [189] - SQL function not found! Oracle
Error: ORA- 00904: "ISO_CURRENCY_CD": invalid identifier Driver
Function: drv_oci::Execute()
Module Logging OFS errors: (908001) Fatal error during processing.

Allocation history page status is showing started and sometimes not
completed.

Problem is caused by the "DECODE" in the following merge statement:

MERGE INTO LEDGER_STAT_TEST TARGET USING (SELECT *
FROM (SELECT (SUM (IVW_SOURCE.IVWSRC_AMOUNT) * 1 * DECODE
(ISO_CURRENCY_CD, 'AUD', 1, 1) ) BALANCE_AMOUNT, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, TRADING_CENTRE_ID,
@ TRADING_PARTNER_ID, DAY_ID, FTP_ID, LEGAL_ENTITY_ID, BRANCH_ID,
ACTIVITY_ID, COSTING_ID, BUSINESS_UNIT_ID
FROM (SELECT (Src.src_amount * Drv.drv_factor) IVWSRC_AMOUNT,
Src.ORG_UNIT_ID, Src.GL_ACCOUNT_ID, Src.COMMON_COA_ID,
Src.PRODUCT_ID, Src.TRADING_CENTRE_ID, Src.TRADING_PARTNER_ID,
@ Src.DAY_ID, Src.FTP_ID, Src.LEGAL_ENTITY_ID, Src.BRANCH_ID,
Drv.ACTIVITY_ID, Src.COSTING_ID, Src.BUSINESS_UNIT_ID
FROM (SELECT SUM(DECODE(a.year_s, 2012, a.month_01, 0) )
src_amount , a.GL_ACCOUNT_ID, a.COMMON_COA_ID, a.PRODUCT_ID,
@ a.TRADING_CENTRE_ID, a.TRADING_PARTNER_ID, a.DAY_ID, a.FTP_ID,
a.LEGAL_ENTITY_ID, a.BRANCH_ID, a.COSTING_ID, a.BUSINESS_UNIT_ID,
a.ORG_UNIT_ID
FROM (GLBL15078TMP)a
GROUP BY a.GL_ACCOUNT_ID, a.COMMON_COA_ID, a.PRODUCT_ID,
@ a.TRADING_CENTRE_ID, a.TRADING_PARTNER_ID, a.DAY_ID, a.FTP_ID,
a.LEGAL_ENTITY_ID, a.BRANCH_ID, a.COSTING_ID, a.BUSINESS_UNIT_ID,
a.ORG_UNIT_ID) Src JOIN (SELECT (ColVal/ mTotal) Drv_factor,
ACTIVITY_ID, ORG_UNIT_ID
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY ACTIVITY_ID,
ORG_UNIT_ID
ORDER BY ACTIVITY_ID, ORG_UNIT_ID) rw, (SUM(DECODE(a.year_s,
2012, a.month_01, 0)) over (PARTITION BY ACTIVITY_ID, ORG_UNIT_ID
))ColVal, (SUM(DECODE(a.year_s, 2012, a.month_01, 0))
over(PARTITION BY ORG_UNIT_ID ))mTotal, ACTIVITY_ID, ORG_UNIT_ID
from LEDGER_STAT a
where a.FINANCIAL_ELEM_ID=31000
and a.ORG_UNIT_ID=8216
and a.GL_ACCOUNT_ID=4101
and a.year_s IN (2012)
and a.iso_currency_cd = 'AUD'
and a.CONSOLIDATION_CD = 100 AND(ORG_UNIT_ID) IN (SELECT
ORG_UNIT_ID
FROM (GLBL15078TMP)a
GROUP BY a.GL_ACCOUNT_ID, a.COMMON_COA_ID, a.PRODUCT_ID,
@ a.TRADING_CENTRE_ID, a.TRADING_PARTNER_ID, a.DAY_ID, a.FTP_ID,
a.LEGAL_ENTITY_ID, a.BRANCH_ID, a.COSTING_ID, a.BUSINESS_UNIT_ID,
a.ORG_UNIT_ID ))
WHERE RW = 1
AND mTotal <> 0
AND ColVal <> 0) Drv ON (Src.ORG_UNIT_ID = Drv.ORG_UNIT_ID) )
IVW_SOURCE
where IVWSRC_AMOUNT <> 0
GROUP BY ORG_UNIT_ID, GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID,
@ TRADING_CENTRE_ID, TRADING_PARTNER_ID, DAY_ID, FTP_ID,
LEGAL_ENTITY_ID, BRANCH_ID, ACTIVITY_ID, COSTING_ID,
BUSINESS_UNIT_ID )
WHERE BALANCE_AMOUNT IS NOT NULL
and round ( BALANCE_AMOUNT, 3) <> 0 ) SOURCE ON
(TARGET.FINANCIAL_ELEM_ID = 31000.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = SOURCE.GL_ACCOUNT_ID
AND TARGET.COMMON_COA_ID = SOURCE.COMMON_COA_ID
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.TRADING_CENTRE_ID = SOURCE.TRADING_CENTRE_ID
AND TARGET.TRADING_PARTNER_ID = SOURCE.TRADING_PARTNER_ID
AND TARGET.DAY_ID = SOURCE.DAY_ID
@ AND TARGET.FTP_ID = SOURCE.FTP_ID
AND TARGET.LEGAL_ENTITY_ID = SOURCE.LEGAL_ENTITY_ID
AND TARGET.BRANCH_ID = SOURCE.BRANCH_ID
AND TARGET.ACTIVITY_ID = SOURCE.ACTIVITY_ID
AND TARGET.COSTING_ID = SOURCE.COSTING_ID
AND TARGET.BUSINESS_UNIT_ID = SOURCE.BUSINESS_UNIT_ID
AND TARGET.YEAR_S = 2012
AND TARGET.IDENTITY_CODE = 3247.000000
AND TARGET.ISO_CURRENCY_CD = 'AUD'
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D'
AND TARGET.BALANCE_TYPE_CD = 101 ) WHEN MATCHED THEN
UPDATE
... <>

2) Steps to Reproduce Problem
-----------------------------
Problem replicates in release 6.1.0.1, but it could not be replicated in 6.1.0.2

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