Oder Release Lead Time Formula Calculation Error in ETL (ODI) When There Is More Than 23 Hours Between the Dates/Times. (Doc ID 2142245.1)

Last updated on MAY 25, 2016

Applies to:

Oracle Fusion Transportation Intelligence - Version 6.3.5 to 6.4.2 [Release 6.3 to 6.4]
Information in this document applies to any platform.

Symptoms

There is a calculation error with a formula in ODI for the ETL from OTM to FTI. The calculation for order releases measuring the lead-time between when an order is entered and when it is available for pickup is not calculating correctly if there is more than 23 hours between these dates/times.

Current formula: TRUNC(MOD((ORDER_RELEASE.EARLY_PICKUP_DATE - ORDER_RELEASE.INSERT_DATE) * 24, 24))

What the formula should be: TRUNC((ORDER_RELEASE.EARLY_PICKUP_DATE - ORDER_RELEASE.INSERT_DATE) * 24, 24)

Within ODI...Interfaces...INCR_F_ORDER_RELEASE_DETAIL

Columns: INSERT_TO_EARLY_PICKUP_HRS, INSERT_TO_LATE_PICKUP_HRS

Example:
Insert Date 5/10/2016 08:00:00
Early Pickup Date: 5/11/2016 10:00:00

Current Result: 2 hours
Should be Results: 26 hours

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