ORA-01400: Cannot Insert NULL into ("FEM"."FEM_BALANCES"."CURRENCY_TYPE_CODE") with Merge Result Buffer (Doc ID 2069478.1)

Last updated on OCTOBER 26, 2015

Applies to:

Oracle Profitability Manager - Version 12.1.3 and later
Oracle Enterprise Performance Foundation - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

In Profitability Manager (PFT) 12.1.3, in an environment upgraded from 11.5.10.2, when you set the Mapping Rule Tuning Option to use "Output Method" = Merge Result Buffer, the Mapping Rule ends in a Warning and fails to output results.  The following error exists in the Output log:

Cause: drv_upi failed due to ORA-01400: cannot insert NULL into ("FEM"."FEM_BALANCES"."CURRENCY_TYPE_CODE").

This error does not occur if you change "Output Method" = Classic Result Buffer.  The Mapping Rule runs successfully with the Classic option.

CURRENCY_TYPE_CODE does exist in the Mapping Input and Mapping Output column properties.

Looking at the Output log, it appears CURRENCY_TYPE_CODE is not included in the mapping rule code:

APP-FND-01564: ORACLE error 1400 in drv_upi

Cause: drv_upi failed due to ORA-01400: cannot insert NULL into
("FEM"."FEM_BALANCES"."CURRENCY_TYPE_CODE").

The SQL statement being executed at the time of the error was: MERGE INTO
FEM_BALANCES USING DUAL ON ( CAL_PERIOD_ID = :CAL_PERIOD_ID and
COMPANY_COST_CENTER_ORG_ID = :COMPANY_COST_CENTER_ORG_ID and CURRENCY_CODE =
:CURRENCY_CODE and DATASET_CODE = :DATASET_CODE and FINANCIAL_ELEM_ID =
:FINANCIAL_ELEM_ID and LEDGER_ID = :LEDGER_ID and LINE_ITEM_ID =
:LINE_ITEM_ID and NATURAL_ACCOUNT_ID = :NATURAL_ACCOUNT_ID and
CREATED_BY_OBJECT_ID = :CREATED_BY_OBJECT_ID and PRODUCT_ID = :PRODUCT_ID and
PROJECT_ID = :PROJECT_ID and SOURCE_SYSTEM_CODE = :SOURCE_SYSTEM_CODE and
USER_DIM1_ID = :USER_DIM1_ID and USER_DIM2_ID = :USER_DIM2_ID and TASK_ID =
:TASK_ID and INTERCOMPANY_ID = :INTERCOMPANY_ID and ENTITY_ID = :ENTITY_ID )  
WHEN MATCHED THEN UPDATE SET XTD_BALANCE_E = XTD_BALANCE_E + :XTD_BALANCE_E,
XTD_BALANCE_F = XTD_BALANCE_F + :XTD_BALANCE_F WHEN NOT MATCHED THEN INSERT
(CAL_PERIOD_ID, COMPANY_COST_CENTER_ORG_ID, CURRENCY_CODE, DATASET_CODE,
FINANCIAL_ELEM_ID, LEDGER_ID, LINE_ITEM_ID, NATURAL_ACCOUNT_ID,
CREATED_BY_OBJECT_ID, PRODUCT_ID, PROJECT_ID, SOURCE_SYSTEM_CODE,
USER_DIM1_ID, USER_DIM2_ID, TASK_ID, INTERCOMPANY_ID, ENTITY_ID,
CREATION_ROW_SEQUENCE, CREATED_BY_REQUEST_ID, LAST_UPDATED_BY_REQUEST_ID,
LAST_UPDATED_BY_OBJECT_ID, XTD_BALANCE_E, XTD_BALANCE_F ) Values (
:CAL_PERIOD_ID,  :COMPANY_COST_CENTER_ORG_ID,  :CURRENCY_CODE,  
:DATASET_CODE,  :FINANCIAL_ELEM_ID,  :LEDGER_ID,  :LINE_ITEM_ID,  
:NATURAL_ACCOUNT_ID,  :CREATED_BY_OBJECT_ID,  :PRODUCT_ID,  :PROJECT_ID,  
:SOURCE_SYSTEM_CODE,  :USER_DIM1_ID,  :USER_DIM2_ID,  :TASK_ID,  
:INTERCOMPANY_ID,  :ENTITY_ID,  :CREATION_ROW_SEQUENCE,  
:CREATED_BY_REQUEST_ID,  :LAST_UPDATED_BY_REQUEST_
APP-FEM-416622: Bound SQL Parameters:
:CREATION_ROW_SEQUENCE = 1
:LAST_UPDATED_BY_REQUEST_ID = 50991881
:LAST_UPDATED_BY_OBJECT_ID = 13339
:XTD_BALANCE_E = 5.31978643148824e+15
:XTD_BALANCE_F = 5.31978643148824e+15
:CREATED_BY_REQUEST_ID = 50991881
:CAL_PERIOD_ID = 24515440000000000000121000100140
:COMPANY_COST_CENTER_ORG_ID = 10005
:CURRENCY_CODE = 'USD'
:DATASET_CODE = 10002
:FINANCIAL_ELEM_ID = 1101
:LEDGER_ID = 671
:LINE_ITEM_ID = 1050609
:NATURAL_ACCOUNT_ID = 1049563
:CREATED_BY_OBJECT_ID = 13339
:PRODUCT_ID = 1049564
:PROJECT_ID = 0
:SOURCE_SYSTEM_CODE = 40
:USER_DIM1_ID = 1049572
:USER_DIM2_ID = 1049574
:TASK_ID = 1049571
:INTERCOMPANY_ID = 0
:ENTITY_ID = 0


As a result, the Mapping Rule fails to run.

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