ETL Does Not Load Any Data into The F_ORDER_RELEASE_DETAIL Table (Doc ID 1633000.1)

Last updated on SEPTEMBER 08, 2016

Applies to:

Oracle Fusion Transportation Intelligence - Version 6.0 and later
Information in this document applies to any platform.

Symptoms

When attempting to run ETL, the following error occurs and ETL Does Not Load Any Data Into the F_ORDER_RELEASE_DETAIL table
Error:
ODI-15065: Join error (FK_FORD_D_SERVPROV) between the table F_ORDER_RELEASE_DETAIL and the table D_SERVPROV.
ODI-15065: Join error (FK_FORD_D_LANE) between the table F_ORDER_RELEASE_DETAIL and the table D_LANE.
ODI-15065: Join error (FK_FORD_D_TRANSPORT_MODE) between the table F_ORDER_RELEASE_DETAIL and the table D_TRANSPORT_MODE.


When checking the E_LOAD_REJECTS table to check for ETL errors, filtering on the F_ORDER_RELEASE_DETAIL table, found errors with 3 foreign keys.

They are:SERVPROV_GID, LANE_GID, TRANSPORT_MODE_GID.

Together, the failing of the ETL to match these keys to the primary keys in the D_SERVPROV, D_LANE, and D_TRANSPORT_MODE dimension tables causes the F_ORDER_RELEASE_DETAIL to not populate. We have verified that the 4 OTM sources tables feeding this table are all populated with data and that the filters in the accompanying interface are also not affecting what should be loaded into the F_ORDER_RELEASE_DETAIL table.

The issue can be reproduced at will with the following steps:
1. Make sure there are Order Release records in the OTM database that are with the status of Ready to Load
2. Run ETL by logging into DBA.ADMIN
3. There are many records in the E_LOAD_REJECTS table with the above error messages.

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