ORA-00979 on Allocation with Expression and Aggregate to Ledger Option (Doc ID 1465211.1)

Last updated on JUNE 06, 2012

Applies to:

Oracle Financial Services Profitability Management - Version 5.6 to 6.0 [Release 5 to 6]
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)

Symptoms

When attempting to run a Field Allocation on an instrument table using Expression in the Source, the allocation does not finish and the debug log is showing the following error.

ERROR
-----------------------
ofspa.log debug file shows:

Module Logging OFS errors: (203105) Oracle drv_oci error: OCI Function: [4] - oexec(),
oexn() SQL Function: [0] - SQL function not found! Oracle Error:
ORA- 00979: not a
GROUP BY expression Driver Function: drv_oci::Execute() SQL
Statement: MERGE / * + APPEND* / INTO LEDGER_STAT TARGET USING
(SELECT SUM(src_amount * 1 ) BALANCE_AMOUNT, DAY_ID,
BUSINESS_UNIT_ID, CHANNEL_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID
FROM (SELECT / * + PARALLEL* / (NVL(a.CUR_PAR_BAL_TRY, 0)*
NVL(a.CCF, 0)) / 100 * NVL(EXP_LOSS_PCT, 0) src_amount ,
a.DAY_ID, a.BUSINESS_UNIT_ID, a.CHANNEL_ID, a.ORG_UNIT_ID,
a.GL_ACCOUNT_ID, a.COMMON_COA_ID, a.PRODUCT_ID
from FSI_D_GUARANTEES a
where a.as_of_date='12/ 19/ 2011'
GROUP BY a.DAY_ID, a.BUSINESS_UNIT_ID, a.CHANNEL_ID,
a.ORG_UNIT_ID, a.GL_ACCOUNT_ID, a.COMMON_COA_ID, a.PRODUCT_ID)
where src_amount <> 0
GROUP BY DAY_ID, BUSINESS_UNIT_ID, CHANNEL_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID ) SOURCE ON
(TARGET.DAY_ID = SOURCE.DAY_ID
AND TARGET.BUSINESS_UNIT_ID = SOURCE.BUSINESS_UNIT_ID
AND TARGET.CHANNEL_ID = SOURCE.CHANNEL_ID
AND TARGET.FINANCIAL_ELEM_ID = 10800.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.YEAR_S = 2011
AND TARGET.IDENTITY_CODE = 26147.000000
AND TARGET.ISO_CURRENCY_CD = 'TRY'
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D' ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_12 = TARGET.MONTH_12 + SOURCE.BALANCE_AMOUNT,
BALANCE_TYPE_CD = 101, ytd_12 = ytd_11 + SOURCE.BALANCE_AMOUNT
WHEN NOT MATCHED THEN
INSERT (DAY_ID, BUSINESS_UNIT_ID, CHANNEL_ID, FINANCIAL_ELEM_ID,
ORG_UNIT_ID, GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID,
ACCUM_TYPE_CD, BALANCE_TYPE_CD, CONSOLIDATION_CD,
CURRENCY_TYPE_CD, ISO_CURRENCY_CD, IDENTITY_CODE, MONTH_01,
MONTH_02, MONTH_03, MONTH_04, MONTH_05, MONTH_06, MONTH_07,
MONTH_08, MONTH_09, MONTH_10, MONTH_11, MONTH_12, YTD_01, YTD_02,
YTD_03, YTD_04, YTD_05, YTD_06, YTD_07, YTD_08, YTD_09, YTD_10,
YTD_11, YTD_12, YEAR_S )
VALUES ( SOURCE.DAY_ID, SOURCE.BUSINESS_UNIT_ID,
SOURCE.CHANNEL_ID, 10800.000000, SOURCE.ORG_UNIT_ID,
SOURCE.GL_ACCOUNT_ID, SOURCE.COMMON_COA_ID, SOURCE.PRODUCT_ID,
'D' , 101 , 100 , 0 , 'TRY' , 26147.000000 , 0.0, 0.0, 0.0, 0.0,
0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, SOURCE.BALANCE_AMOUNT, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0, 0, SOURCE.BALANCE_AMOUNT , 2011)


STEPS
-----------------------
1. Create a Field Allocation on an instrument table.
2. Use Expression on Source and Divide the Source.
3. Aggregate Output to Ledger.


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