My Oracle Support Banner

OFSAA PFT Lookup Table Driver Rule Fails With ORA-00979 Error (Doc ID 1680958.1)

Last updated on AUGUST 07, 2018

Applies to:

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

Symptoms

On PFT 6.0.3, a lookup table driver allocation rule fails with errors. The allocation fails with the following error:

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- 00979: not a
GROUP BY expression Driver Function: drv_oci::Execute()


Based on the generated PFT debug logs, the column 'iso_currency_cd' is not included in the GROUP BY clause of the merge statement:

MERGE INTO LEDGER_STAT TARGET USING (SELECT *
FROM (
SELECT 100 CONSOLIDATION_CD, 'D' ACCUM_TYPE_CD, 10943.000000
IDENTITY_CODE, 2013 YEAR_S, 101 BALANCE_TYPE_CD, ISO_CURRENCY_CD,
1111111.000000 FINANCIAL_ELEM_ID, ORG_UNIT_ID, COMMON_COA_ID,
PRODUCT_ID, (SUM(NVL(ATM_EXP, 0) + NVL(pg1lkup2, 0) ) ) * 1
BALANCE_AMOUNT
from (
SELECT RISK_MATRIX.RETURN_VALUE pg1lkup2, x.ATM_EXP,
x.ACCID_HEALTH_INS_CO_CD, x.ACC_HEALTH_INS_FLG,

...

x.TRANSFER_RATE_ALT, x.T_RATE_INT_RATE_CD, x.UNDRAWN_AMT,
x.VIP_ACCOUNT_FLG, x.WAIVED_FEES
FROM FSI_D_LOAN_CONTRACTS x, RISK_MATRIX
WHERE x.PRODUCT_ID=RISK_MATRIX.PRODUCT_ID) a
where a.as_of_date='02/ 28/ 2013'
GROUP BY ORG_UNIT_ID, COMMON_COA_ID, PRODUCT_ID ) /* missing ISO_CURRENCY_CD column*/
WHERE BALANCE_AMOUNT IS NOT NULL ) SOURCE ON
(TARGET.FINANCIAL_ELEM_ID = 1111111.000000
AND TARGET.ORG_UNIT_ID = SOURCE.ORG_UNIT_ID
AND TARGET.GL_ACCOUNT_ID = 500001.000000
AND TARGET.COMMON_COA_ID = SOURCE.COMMON_COA_ID
AND TARGET.PRODUCT_ID = SOURCE.PRODUCT_ID
AND TARGET.TRANSACTION_ID = 0.000000
AND TARGET.YEAR_S = 2013
AND TARGET.IDENTITY_CODE = 10943.000000
AND TARGET.ISO_CURRENCY_CD = SOURCE.ISO_CURRENCY_CD
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D'
AND TARGET.BALANCE_TYPE_CD = 101 ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_02 = TARGET.MONTH_02 + SOURCE.BALANCE_AMOUNT,
ytd_02 = ytd_01 + SOURCE.BALANCE_AMOUNT , ytd_03 = ( ytd_01 +
SOURCE.BALANCE_AMOUNT) + month_03 , ytd_04 = (( ytd_01 +
SOURCE.BALANCE_AMOUNT) + month_03) + month_04 , ytd_05 = (((
ytd_01 + SOURCE.BALANCE_AMOUNT) + month_03) + month_04) +
month_05 , ytd_06 = (((( ytd_01 + SOURCE.BALANCE_AMOUNT) +
month_03) + month_04) + month_05) + month_06 , ytd_07 = (((((
ytd_01 + SOURCE.BALANCE_AMOUNT) + month_03) + month_04) +
month_05) + month_06) + month_07 , ytd_08 = (((((( ytd_01 +
SOURCE.BALANCE_AMOUNT) + month_03) + month_04) + month_05) +
month_06) + month_07) + month_08 , ytd_09 = ((((((( ytd_01 +
SOURCE.BALANCE_AMOUNT) + month_03) + month_04) + month_05) +
month_06) + month_07) + month_08) + month_09 , ytd_10 = ((((((((
ytd_01 + SOURCE.BALANCE_AMOUNT) + month_03) + month_04) +
month_05) + month_06) + month_07) + month_08) + month_09) +
month_10 , ytd_11 = ((((((((( ytd_01 + SOURCE.BALANCE_AMOUNT) +
month_03) + month_04) + month_05) + month_06) + month_07) +
month_08) + month_09) + month_10) + month_11 , ytd_12 =
(((((((((( ytd_01 + SOURCE.BALANCE_AMOUNT) + month_03) +
month_04) + month_05) + month_06) + month_07) + month_08) +
month_09) + month_10) + month_11) + month_12 WHEN NOT MATCHED
THEN
INSERT (FINANCIAL_ELEM_ID, ORG_UNIT_ID, GL_ACCOUNT_ID,
COMMON_COA_ID, PRODUCT_ID, TRANSACTION_ID, ACCUM_TYPE_CD,
BALANCE_TYPE_CD, CONSOLIDATION_CD, CURRENCY_TYPE_CD,
IDENTITY_CODE, ISO_CURRENCY_CD, MONTH_01, MONTH_02, MONTH_03,
MONTH_04, MONTH_05, MONTH_06, MONTH_07, MONTH_08, MONTH_09,
MONTH_10, MONTH_11, MONTH_12, YEAR_S, YTD_01, YTD_02, YTD_03,
YTD_04, YTD_05, YTD_06, YTD_07, YTD_08, YTD_09, YTD_10, YTD_11,
YTD_12 )
VALUES ( 1111111.000000, SOURCE.ORG_UNIT_ID, 500001.000000,
SOURCE.COMMON_COA_ID, SOURCE.PRODUCT_ID, 0.000000, 'D' , 101 ,
100 , 0 , 10943.000000 , SOURCE.ISO_CURRENCY_CD, 0.0,
SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0,
0.0, 0.0, 2013, 0, SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT
, SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT , SOURCE.BALANCE_AMOUNT ,
SOURCE.BALANCE_AMOUNT )

Multi currency is enabled in this instance.

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!


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