ORA- 00947: not enough values Errors Running Allocations After Making Updates to Data Model to Implement EFPA (Doc ID 1951155.1)

Last updated on DECEMBER 04, 2014

Applies to:

Oracle Financial Services Profitability Management - Version 6.1 and later
Oracle Financial Services Enterprise Financial Performance Analytics - Version 6.1 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Enterprise Financial Performance Analytics (EFPA / PFTBI) previously known as Oracle Financial Services Profitability Analytics

Symptoms

On Oracle Financial Services Profitability Management (PFT) 6.1.0.3 when trying to run a constant allocation but after it executes it fails caused by "Insuficient Values". Checking the log it seems that we have less values than columns on the insert the values.

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- 00947: not enough
values Driver Function: drv_oci::Execute() SQL Statement: MERGE
INTO LEDGER_STAT TARGET USING (SELECT *
FROM (
SELECT 10053.000000 identity_code, 2014 year_s , 'D'
accum_type_cd , 100 consolidation_cd , 101 balance_type_cd, 'USD'
ISO_CURRENCY_CD , SOURCE_CONSTANT * 1/ 2.935000 BALANCE_AMOUNT
from FSI_M_ALLOC_DETAILS
where ALLOC_ELEMENT_SYS_ID in (
select SOURCE_SYS_ID
from FSI_M_ALLOCATION_RULE
where ALLOCATION_SYS_ID = 200524.000000) )
WHERE BALANCE_AMOUNT IS NOT NULL
and round ( BALANCE_AMOUNT, 4) <> 0 ) SOURCE ON
(TARGET.TIPO_TRAN_ID = 1001.000000
AND TARGET.FINANCIAL_ELEM_ID = 30040.000000
AND TARGET.ORG_UNIT_ID = 0.000000
AND TARGET.GL_ACCOUNT_ID = 1111010101.000000
AND TARGET.COMMON_COA_ID = - 1.000000
AND TARGET.PRODUCT_ID = 10.000000
AND TARGET.CANAL_ID = 1001.000000
AND TARGET.YEAR_S = 2014
AND TARGET.IDENTITY_CODE = 10053.000000
AND TARGET.ISO_CURRENCY_CD = 'USD'
AND TARGET.CONSOLIDATION_CD = 100
AND TARGET.ACCUM_TYPE_CD = 'D'
AND TARGET.BALANCE_TYPE_CD = 101 ) WHEN MATCHED THEN
UPDATE
SET TARGET.MONTH_01 = TARGET.MONTH_01 + SOURCE.BALANCE_AMOUNT,
ytd_01 = SOURCE.BALANCE_AMOUNT , ytd_02 = ytd_01 + month_02 ,
ytd_03 = ( ytd_01 + month_02) + month_03 , ytd_04 = (( ytd_01 +
month_02) + month_03) + month_04 , ytd_05 = ((( ytd_01 +
month_02) + month_03) + month_04) + month_05 , ytd_06 = ((((
ytd_01 + month_02) + month_03) + month_04) + month_05) + month_06
, ytd_07 = ((((( ytd_01 + month_02) + month_03) + month_04) +
month_05) + month_06) + month_07 , ytd_08 = (((((( ytd_01 +
month_02) + month_03) + month_04) + month_05) + month_06) +
month_07) + month_08 , ytd_09 = ((((((( ytd_01 + month_02) +
month_03) + month_04) + month_05) + month_06) + month_07) +
month_08) + month_09 , ytd_10 = (((((((( ytd_01 + month_02) +
month_03) + month_04) + month_05) + month_06) + month_07) +
month_08) + month_09) + month_10 , ytd_11 = ((((((((( ytd_01 +
month_02) + month_03) + month_04) + month_05) + month_06) +
month_07) + month_08) + month_09) + month_10) + month_11 , ytd_12
= (((((((((( ytd_01 + month_02) + 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 (TIPO_TRAN_ID, FINANCIAL_ELEM_ID, ORG_UNIT_ID,
GL_ACCOUNT_ID, COMMON_COA_ID, PRODUCT_ID, CANAL_ID,
ACCUM_TYPE_CD, BALANCE_TYPE_CD, CONSOLIDATION_CD,
CURRENCY_TYPE_CD, ENTITY_ID, IDENTITY_CODE, ISO_CURRENCY_CD,
LOB_ID, 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 ( 1001.000000, 30040.000000, 0.000000, 1111010101.000000,
- 1.000000, 10.000000, 1001.000000, 'D' , 101 , 100 , 0 ,
10053.000000 , 'USD' , SOURCE.BALANCE_AMOUNT, 0.0, 0.0, 0.0, 0.0,
0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 2014, 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 ,
SOURCE.BALANCE_AMOUNT )

Error appeared after completing data model merge with PFTBI. For PFTBI, the "ledger leaf columns" show two additional columns (LOB_ID and Entity_id) and are not marked as processing key,

The issue can be reproduced at will with the following steps:

1. Merge data model PFT and PFTBI
2. Run model upload
3. Attempt to rerun constant allocation

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