OBIA 11g: The Out Of The Box ETL logic for W_SALES_PICK_LINE_F.SHIPPED_ONTIME_IND is Not Correct

(Doc ID 2010146.1)

Last updated on AUGUST 09, 2016

Applies to:

Business Intelligence Applications Consumer - Version and later
Information in this document applies to any platform.


The Out Of The Box ETL logic for W_SALES_PICK_LINE_F.SHIPPED_ONTIME_IND doesn't appear to be correct for the following reason:-

The following is the OOTB code for the interface.

If the value of the RELEASED_STATUS is equal to 'Y' then the SHIPPED_ONTIME_INDICATOR is set to null, otherwise the GET_ONTIME_IND method is invoked which places a value of 1, 0, or -1 into the SHIPPED_ONTIME_INDICATOR and those values determine whether a shipped order is late, on time or early. Since the value of 'Y' equates to Staged/Picked Confirmed, it is not shipped. This is correct and we agree with that. However, the value of 'B' which is Backordered is also not shipped. 'R' is Ready for Release which is also not shipped but ready for it. 'S' is a Release to Warehouse which also means that the order is not shipped. 'D' is Cancelled so the order will not ship either. Therefore, the values of 'B', 'R', 'Y'. 'S', and 'D' are all RELEASE_STATUS values that equate to an order NOT SHIPPING.

It is our contention that the THEN and ELSE portions of the OOTB code are correct but that the condition or the IIF statement is not correct. If the value of RELEASED_STATUS is any other value than 'C' which is Shipped and/or Interfaced, then it did not ship and the SHIPPED_ONTIME_INDICATOR should be set to null. Only when the RELEASED_STATUS = 'C' should it invoke the GET_ONTIME_IND method in the ELSE portion of the IIF statement.

It is clear from the RELEASED_STATUS values  contain only one value, 'C', that indicates a shipped order. The only time the SHIPPED_ONTIME_INDICATOR should have a value is when the order is shipped. Otherwise it did not ship and cannot be late, on time or early which is what the indicator flags.

OOTB logic:


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