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

Last updated on OCTOBER 04, 2016

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.ACCIDENT_HEALTH_PREMIUM, x.ACCOUNT_CLOSE_DATE,
x.ACCOUNT_CONTRIB, x.ACCOUNT_CONTRIB_AFTER_TAX,
x.ACCOUNT_GROUP_CD, x.ACCOUNT_NUMBER, x.ACCOUNT_OFFICER_CD,
x.ACCRUAL_BASIS_CD, x.ACCRUED_INTEREST, x.ADJUSTABLE_TYPE_CD,
x.ADJ_EFFECTIVE_DATE, x.AFTER_PAYMENT_BALANCE, x.ALL_IN_TP_RATE,
x.ALLOCATED_EQUITY, x.ALLOC_LLR, x.AMRT_TERM, x.AMRT_TERM_MULT,
x.AMRT_TYPE_CD, x.AMORT_DISC_PREM_CHG_CR, x.AS_OF_DATE,
x.AUTOPAY_ACCT_NUMBER, x.AUTOPAY_BANK_TRANSIT_NBR, x.AUTOPAY_FLG,
x.AUTOPAY_INSTR_TYPE_CD, x.AVG_BOOK_BAL, x.AVERAGE_LIFE_C,
x.AVG_NET_BOOK_BAL_C, x.BACKUP_LIQUID_COST, x.BANK_CD,
x.BASIS_RISK_CHARGE_CREDIT, x.BASIS_RISK_COST_AMT,
x.BASIS_RISK_COST_RATE, x.BEHAVIOUR_SUB_TYPE_CD,
x.BEHAVIOUR_TYPE_CD, x.BRANCH_CD, x.BRANCH_PLATFORM_EXP,
x.BRANCH_TELLER_EXP, x.BREAKAGE_AMOUNT,
x.BREAKAGE_CUMULATIVE_AMT, x.BREAK_TOLERANCE_EXPIRY_DATE,
x.BREAKAGE_FLG, x.BREAK_TOLERANCE_AMT, x.BREAKAGE_TYPE_CD,
x.CIF_KEY, x.CUSIP_NUMBER, x.CALL_CENTER_EXP, x.CALL_OPTION_DATE,
x.CALL_PRICE, x.MISC_ASSET_CHG, x.COLLATERAL_CD,
x.COLLATERAL_MKT_VALUE, x.COLLATERAL_NOMINAL_VALUE,
x.COMMITMENT_BAL, x.COMMITMENT_NBR, x.COMMITMENT_TYPE_CD,
x.COMMIT_UTIL_PCT_C, x.COMMON_COA_ID, x.COMPOUND_BASIS_CD,
x.CONTRACT_AMT, x.CONTRIB_AFTER_CAPITAL_CHG, x.CONVEXITY_C,
x.CREDIT_LIFE_INS_CO_CD, x.CREDIT_LIFE_INS_FLG,
x.CREDIT_LIFE_INS_PREM, x.CREDIT_RATING_CD,
x.CREDIT_RISK_CAPITAL, x.CREDIT_SCORE, x.CREDIT_SCORE_DATE,
x.CREDIT_STATUS_CD, x.ALLOC_LLR_CR, x.MISC_LIABILITY_CR,
x.CURRENT_FEES, x.CUR_BOOK_BAL, x.CUR_PAR_BAL, x.CUR_GROSS_RATE,
x.CUR_YIELD, x.CUR_NET_BOOK_BAL_C, x.CUR_NET_PAR_BAL_C,
x.CUR_NET_RATE, x.CUR_OAS, x.CUR_OAS_ALT, x.CUR_PAYMENT,
x.CUR_STATIC_SPREAD, x.CUR_STATIC_SPREAD_ALT, x.CUR_TP_PER_ADB,
x.CUSTOMER_ID, x.CUSTOMER_NAME, x.DATA_PROCESSING_EXP,
x.DATA_SOURCE_CD, x.DEALER_NBR, x.DEALER_RES_ORG,
x.DEALER_RES_UNEARN, x.AMORT_METH_PDFC_CD, x.DEFERRED_CUR_BAL,
x.DEFERRED_ORG_BAL, x.DEL_CUR_DAYS, x.DEL_LIFE_TIMES,
x.DEL_YEAR_TIMES, x.DEVOLVEMENT_STATUS_CD, x.DIRECT_ACCT_EXP,
x.DIRECT_IND_CD, x.DIST_FR_LIFE_CAP_C, x.DISTRIBUTION_CHANNEL_CD,
x.DRAWN_AMT, x.DURATION_C, x.EFF_INTEREST_RATE_C,
x.ELECTRONIC_BANKING_EXP, x.EMBEDDED_OPTIONS_FLG,
x.EQUITY_CREDIT, x.EXIST_BORROWER_CD, x.EXPECTED_GRAD_DATE,
x.FEES_EIR, x.FIRST_DISBURS_DATE, x.GL_ACCOUNT_ID,
x.GEOGRAPHIC_LOC_CD, x.GROSS_FEE_INCOME, x.HEDGE_PORTFOLIO_SET,
x.HISTORIC_OAS, x.HISTORIC_OAS_ALT, x.HISTORIC_STATIC_SPREAD,
x.HISTORIC_STATIC_SPREAD_ALT, x.HOLIDAY_ROLLING_CONVENTION_CD,
x.ID_NUMBER, x.ISO_CURRENCY_CD, x.IDENTITY_CODE,
x.IDENTITY_CODE_CHG, x.NOTCH1_DOWNGRADE_CF_IMPACT,
x.NOTCH10_DOWNGRADE_CF_IMPACT, x.NOTCH2_DOWNGRADE_CF_IMPACT,
x.NOTCH3_DOWNGRADE_CF_IMPACT, x.NOTCH4_DOWNGRADE_CF_IMPACT,
x.NOTCH5_DOWNGRADE_CF_IMPACT, x.NOTCH6_DOWNGRADE_CF_IMPACT,
x.NOTCH7_DOWNGRADE_CF_IMPACT, x.NOTCH8_DOWNGRADE_CF_IMPACT,
x.NOTCH9_DOWNGRADE_CF_IMPACT, x.INDIRECT_NIR,
x.INITIAL_DIRCT_COST, x.INSTRUMENT_TYPE_CD,
x.INTEREST_CHARGE_CREDIT, x.INTEREST_INC_EXP, x.INTEREST_OVERDUE,
x.INTEREST_RATE_CD, x.IRR_CAPITAL, x.INT_TYPE, x.INVESTOR_NBR,
x.ISSUE_DATE, x.ITEM_PROCESSING_EXP, x.JOINT_ACCOUNT_FLG,
x.LRD_BALANCE, x.LAST_PAYMENT_DATE, x.LAST_REPRICE_DATE,
x.LAST_UPDATE_DATE_C, x.RESIDUAL_AMOUNT, x.LIQUIDITY_PREMIUM_AMT,
x.LIQUIDITY_PREM_CHARGE_CREDIT, x.LIQUIDITY_PREMIUM_RATE,
x.LIQ_RISK_CAPITAL, x.LOAN_APPROVE_DATE, x.LOAN_LOSS_PROVISION,
x.LOAN_LOSS_RESERVE, x.EXPECTED_BAL_GROWTH_PCT, x.EXPECTED_BAL,
x.MINIMUM_BALANCE, x.OFFSET_PERCENT, x.MARGIN, x.MARGIN_GROSS,
x.MARGIN_T_RATE, x.MARKET_RISK_CAPITAL, x.MARKET_SEGMENT_CD,
x.MARKET_VALUE_CLEAN_C, x.MARKET_VALUE_C, x.MATCHED_SPREAD_C,
x.MATCHED_SPREAD_ALT, x.MATURITY_AMOUNT, x.MATURITY_DATE,
x.MISC_ALLOC_ASSETS, x.MISC_ALLOC_LIABILITIES,
x.MODIFIED_DURATION_C, x.NEG_AMRT_AMT, x.NEG_AMRT_EQ_DATE,
x.NEG_AMRT_EQ_MULT, x.NEG_AMRT_EQ_FREQ, x.NEG_AMRT_LIMIT,
x.NET_FEE_INCOME, x.NET_INT_MARGIN, x.NET_MARGIN_CD,
x.NEXT_FINANCE_DATE, x.NEXT_PAYMENT_DATE, x.NEXT_REPRICE_DATE,
x.NOTE_NUMBER, x.RETURN_ITEMS, x.OPEN_ACCOUNT_FLG,
x.OP_RISK_CAPITAL, x.OPTION_COST, x.CHARGE_CREDIT_OCOST,
x.CHARGE_CREDIT_OCOST_REM_TERM, x.CHARGE_CREDIT_TRATE,
x.CHARGE_CREDIT_TRATE_REM_TERM, x.ORG_UNIT_ID, x.ORG_BOOK_BAL,
x.ORG_PAR_BAL, x.ORG_INTEREST_AMT, x.ORG_LOAN_TO_VALUE,
x.ORG_MARKET_VALUE, x.ORG_NET_BOOK_BAL_C, x.ORG_NET_PAR_BAL_C,
x.ORG_PAYMENT_AMT, x.ORG_PAYMENT_DATE, x.ORG_RATE,
x.ORG_TERM_MULT, x.ORG_TERM, x.ORIGINATION_DATE,
x.OTHER_ACCOUNT_EXP, x.OTHER_ADJUSTMENTS_AMT,
x.OTHER_ADJ_AMOUNT_ALT, x.OTHER_ADJ_CHARGE_CREDIT,
x.OTHER_ADJUSTMENTS_RATE, x.OTHER_ADJ_RATE_ALT, x.OTHER_CAPITAL,
x.OTHER_PROCESSING_EXP, x.PARTICIPATION_AMT_SOLD,
x.PARTICIPATION_FLG, x.PMT_ADJUST_DATE, x.PMT_CHG_FREQ,
x.PMT_CHG_FREQ_MULT, x.PMT_DECR_CYCLE, x.PMT_DECR_LIFE,
x.PMT_FREQ, x.PMT_FREQ_MULT, x.PMT_INCR_CYCLE, x.PMT_INCR_LIFE,
x.PMT_TYPE_CD, x.PERCENT_SOLD, x.PLEDGED_STATUS_CD,
x.POWER_OF_ATTORNEY_FLG, x.PRICING_INCENTIVE_AMT,
x.PRICING_INC_CHARGE_CREDIT, x.PRICING_INCENTIVE_RATE,
x.PRIME_RATE, x.PRIOR_TP_PER_ADB, x.PRODUCT_ID,
x.PRODUCT_TYPE_CD, x.PURPOSE_CD, x.PUT_CALL_CD,
x.PUT_EXPIRE_DATE, x.PUT_OPTION_DATE, x.PUT_OPTION_MULT_C,
x.PUT_OPTION_TERM_C, x.RATE_CAP_LIFE, x.RATE_CHG_MIN,
x.RATE_CHG_RND_CD, x.RATE_CHG_RND_FAC, x.RATE_DECR_CYCLE,
x.RATE_DECR_YEAR, x.RATE_FLOOR_LIFE, x.RATE_INCR_CYCLE,
x.RATE_INCR_YEAR, x.RATE_SET_LAG, x.RATE_SET_LAG_MULT,
x.REASON_CLOSED_CD, x.RECORD_COUNT, x.REMAIN_NO_PMTS_C,
x.REMAIN_TERM_C, x.REMAIN_TERM_MULT_C, x.TRAN_RATE_REM_TERM,
x.TRAN_RATE_REM_TERM_ALT, x.REPRICE_FREQ, x.REPRICE_FREQ_MULT,
x.RESERVE_CHARGE_CREDIT, x.RETAIL_EXP, x.RETURN_ON_EQUITY,
x.RISK_ADJ_AVG_BAL, x.SIC_CD, x.SALES_CHANNEL,
x.SANCTIONED_LIMIT, x.SCHOOL_ID_CD, x.SECURED_FLAG,
x.SETTLEMENT_DATE, x.STUDENT_STATUS_FLG,
x.STUDENT_YR_IN_SCHOOL_CD, x.TP_EFFECTIVE_DATE, x.TAX_EXEMPT_PCT,
x.TAX_EXP, x.TEASER_END_DATE, x.TOTAL_ACCOUNT_EXP,
x.TOTAL_DISTRIBUTION_EXP, x.TOTAL_FEES, x.TOTAL_FEES_AT_ORG,
x.TOTAL_PROCESS_EXP, x.TOTAL_TAX_EXP, x.TOTAL_TRANSACTIONS,
x.TP_AVERAGE_LIFE, x.TP_DURATION, x.TRANSFER_RATE,
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

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