Shipments with Status Updated To SHIPMENT_READY_TO_LOAD During ETL Load Process Are Rejected (Doc ID 2199183.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

ACTUAL BEHAVIOR
---------------
Shipments were created in OTM shortly before the ETL load process kicked off (ex: 4:00). The status of these shipments for shipment hd load status was not set to SHIPMENT_READY_TO_LOAD in time so they were not initially picked up to be loaded in the dimension tables. At some point during the ETL load (and after the D_SHIPMENT table was already loaded), the status of these shipments changed to SHIPMENT_READY_TO_LOAD and the data for these shipments was attempted to be loaded for the fact tables (ex. after 4:00). Thus all depending tables on these shipments failed with the join exceptions as they were missing from D_SHIPMENT. This results in multiple join exceptions in e_load_rejects but a successful run in e_load_log for the D_SHIPMENT table.

EXPECTED BEHAVIOR
-----------------------
It is expected that the ETL load process not attempt to load shipment data for shipments that changed status to SHIPMENT_READY_TO_LOAD after the ETL load process has already initiated.

STEPS
-----------------------
1. Create a shipment in OTM but do not change the status to SHIPMENT_READY_TO_LOAD
2. Kick off the ETL load process
3. Change the status of the shipment created in step one to SHIPMENT_READY_TO_LOAD (ensure the D_SHIPMENT table has already been loaded)
4. There will be multiple join exceptions on related child tables for shipments

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.