ORA-01400: cannot insert NULL into ("FSI_M_PPMT_CALC_PARAMS"."PRODUCT_ID") Error Running T2T_PPMT_CALC_PARAMS

(Doc ID 2423013.1)

Last updated on JULY 13, 2018

Applies to:

Oracle Financial Services Funds Transfer Pricing - Version 8.0.5 and later
Information in this document applies to any platform.
Oracle Financial Services Analytical Applications (OFSAA)
Oracle Financial Services Analytical Applications Infrastructure (OFSAAI)
Oracle Financial Services Funds Transfer Pricing (FTP)
Oracle Financial Services Asset Liability Management (ALM)
Data Mapping
Table to Table (T2T)

Symptoms

On FTP 8.0.5 running the T2T_PPMT_CALC_PARAMS produces no output. The Out-of-the-box (OOTB) T2T generates a NULL value error on the derived PRODUCT_ID column. From the generated FSI_M_PPMT_CALC_PARAMS$ table the following error is thrown:

ERROR
ORA-01400: cannot insert NULL into ("atomic"."FSI_M_PPMT_CALC_PARAMS"."PRODUCT_ID")

The STG_PPMT_CALC_PARAMS.V_PRODUCT_CODE is populated with matching dimension member values in DIM_PRODUCTS_TL.PRODUCT_NAME.

Check of the log shows a missing section in the Expression for Product ID:

Log Message here 1: Tue 15 May 2018 10:38:57 AM CDT||DEBUG||ClsUMappingParser::parseMapping, INFO, SQL Transformation = CASE PPMT_OBJECT_DEFN.LEAF_NUM_ID WHEN 0 THEN (SELECT DIM_0.FINANCIAL_ELEM_ID FROM DIM_FINANCIAL_ELEMENTS_TL DIM_0 WHERE DIM_0.FINANCIAL_ELEM_NAME = STG_PPMT_CALC_PARAMS.V_PRODUCT_CODE) WHEN 1 THEN (SELECT DIM_1.ORG_UNIT_ID FROM DIM_ORG_UNIT_TL DIM_1 WHERE DIM_1.ORG_UNIT_NAME = STG_PPMT_CALC_PARAMS.V_PRODUCT_CODE) WHEN 2 THEN (SELECT DIM_2.GL_ACCOUNT_ID FROM DIM_GENERAL_LEDGER_TL DIM_2 WHERE DIM_2.GL_ACCOUNT_NAME = STG_PPMT_CALC_PARAMS.V_PRODUCT_CODE) WHEN 3 THEN (SELECT DIM_3.COMMON_COA_ID FROM DIM_COMMON_COA_TL DIM_3 WHERE DIM_3.COMMON_COA_NAME = STG_PPMT_CALC_PARAMS.V_PRODUCT_CODE) END AS PRODUCT_ID

It appears to be missing the critical section for Dimension 4 (standard Product ID dimension):


The issue can be reproduced at will with the following steps:
1. Run the T2T_PPMT_CALC_PARAMS
2. Check the generated FSI_M_PPMT_CALC_PARAMS$ for errors after finding no rows loaded to FSI_M_PPMT_CALC_PARAMS.

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