Dimension Loader Fn_stgdimdataloader Fails With "ORA-01400: cannot insert NULL" Errors (Doc ID 2965458.1)

Last updated on AUGUST 16, 2023

Applies to:

Symptoms

On OFSAA, trying to load the Legal entity Dimensions by manually calling the function fn_stgdimdataloader.   However, the DIM_LEGAL_ENTITY_B.LEGAL_ENTITY_ID column's value is always coming as 0. Other columns look fine.

The FSI_MESSAGE_LOG shows the following error:

ERROR

" MERGE INTO DIM_LEGAL_ENTITY_TL TARGET_TABLE
using ( select 'US' langauge ,
aa.LEGAL_ENTITY_ID id_column ,
bb.V_ENTITY_NAME name_column ,
bb.V_ENTITY_NAME description_column,
-2 created_by ,
sysdate created_date ,
-2 modified_by,
sysdate modified_date
from DIM_LEGAL_ENTITY_B aa , stg_org_structure_master bb
where fic_mis_date = to_date('20200331','RRRRMMDD') and
aa.LEGAL_ENTITY_CODE = bb.V_ENTITY_CODE
)
merge_query on
(target_table.language = merge_query.langauge and
target_table.LEGAL_ENTITY_ID = merge_query.id_column )
when matched then
update set
target_table.LEGAL_ENTITY_NAME = name_column,
target_table.description = description_column
when not matched then
insert (TARGET_TABLE.LANGUAGE,
TARGET_TABLE.LEGAL_ENTITY_ID,
TARGET_TABLE.LEGAL_ENTITY_NAME,
TARGET_TABLE.DESCRIPTION,
TARGET_TABLE.CREATED_BY,
TARGET_TABLE.CREATION_DATE,
TARGET_TABLE.LAST_MODIFIED_BY,
TARGET_TABLE.LAST_MODIFIED_DATE
) values (
merge_query.langauge,
merge_query.id_column,
merge_query.name_column,
merge_query.description_column,
merge_query.created_by,
merge_query.created_date,
merge_query.modified_by,
merge_query.modified_date
)"
Error .ORA-01400: cannot insert NULL into ("<ATOMIC>"."DIM_LEGAL_ENTITY_TL"."LEGAL_ENTITY_NAME")

Changes

Cause

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