Case Study on how to record user specific times through P42101 (Sales Order Entry)

Purpose of Document

Purpose of this document is to guide possible implementation on times per user time zone which are available EnterpriseOne release 8.9 and above as below,

• RSDT (Promised Delivery Time)
• DRQT (Requested Delivery Time)
• PMDT (Scheduled Shipment Time)
• RLTM (Time - Release)
• ADTM (Actual Shipment Time)
• OPTT (Time - Original Promised Delivery)
• PDTT (Time - Scheduled Pick)
• PSTM (Time - Future Time 2)

Note that above columns are available in P42101 but not in P4210.

Before continue to review, refer to <Document 626596.1> - E1: DD: Data Item Class JDEUTIME:
"Prior to EnterpriseOne 8.9, the software presumed that all users of a system were located in the same time zone. While this is not a problem for localized enterprises dealing with only one time zone, it becomes a significant issue for enterprises whose business processes span several time zones, particularly when the duration of these processes is relatively short. Time zone variations make timestamps inaccurate, and the potential errors caused by these variations can make the calculated and reported dates in applications almost meaningless. In the current e-business environment, where multi-zone, short duration business processes are typical, finding a solution to this problem is critical."

Table of Contents

• Limitation
• UTC (Coordinated Universal Time)
• Goal
•     Business Scenario
•     Representation of data type for date (JDEDATE) and datetime (JDEUTIME)
•     Example of implementation:
•     Problem
• Solution

Limitation

Since columns listed above do not belong to JDEUTime class of data dictionary item, Oracle do not support any trigger in DD and Table level. So information in body is example on possible implementation to make use of the concept of JDEUTime. Full list of table column combination can be found here.

UTC (Coordinated Universal Time)

Coordinated Universal Time (UTC) is the primary time standard by which the world regulates clocks and time. It is one of several closely related successors to Greenwich Mean Time (GMT). For most purposes, UTC is synonymous with GMT, but GMT is no longer precisely defined by the scientific community.

Goal

As above reference- E1: DD: Data Item Class JDEUTIME reads your business may require you to implement JDEUTime for Sales Order Module. This document is to guide possible implementation and considerations on it. If possible, create a brand new table to hold these information rather than modify existing routines.

For this example, you are going to create custom NER (Named Event Rule) to show/save based on the timezone you are on.

Business Scenario

• Supposed that you (user A) are sitting in UAT +08:00 (GMT + 08:00, Singapore) and you promised to deliver a certain part by 11:00:00 a.m.
• When a user (User B) from Abudabi (UAT +04:00) sees it as 07:00:00 and ship to location has changed from Singapore to Abudabi
• So User B wants to change it the earliest working hour 09:00:00.

So,

• User A enter 110000 which is to be stores as 030000 in the database
• User B views it as 070000
• User B updates it with 090000
• So database stores it as 050000

Representation of data type for date (JDEDATE) and datetime (JDEUTIME)

A. Julian Date

• 113001 <> 1/1/2013 113365 <> 12/31/2013
• 114001 <> 1/1/2014 114365 <> 12/31/2013

B. timedate format data (example of F03012.aidtee)
- Representation through JDE tools (e.g., UTB or application): 8282012 05:57:55(UTC) /* Description 2 of UDC H91/TZ (Time Zone) for Code 26 */
: Date is parsed based on Date Format from User Profile which is coming from UDC 00/DF (Date Format).
- Representation through JDE tools: 8282012 13:57:55(UTC+08:00)
: when UDC H91/TZ is 31 (UAT +08:00) same value is to be appeared,

C. Value through Query Tools (example of Oracle 10g)
sql> select aidtee from proddta.f03012 where aian8 = 4242;

To describe it with date and time
sql> SELECT TO_CHAR(aidtee, 'DD-MON-YYYY HH24:MI:SS') AS test FROM proddta.f03012 WHERE an8 = 4242;

Example of implementation:

Requirement: 2 columns (RSDJ (Date - Promised Delivery) and RSDT (Promised Delivery Time)) from F4211 (Sales Order Detail File) are to be adjusted based on user timezone defined.

Steps:

1. Create data structure with minimum parameters. For this example, jdPromisedDeliveryDate, mnPromisedDeliveryTime, cMode and cTimeFormat

• cMode: this is need to determine From Time and To Time. For example,
• - Write/Update: From Time (User Time) to To Time (Server Time or UTC - 0)
• - View: From Time (Server Time) to To Time (User Time)
• cTimeFormat: This parameter is required because time format can be HHMM without seconds. So depends on requirement, if needed additional '00' is to be appended by multiplying HHMM with 100

2. Define necessary variable in reading data

3. Check whether input RSDJ is NULL but RSDT is not zero then set RSDJ = Date Today

4. Retrieve Time Zone and Daylight Savings Rule for the user. This can be through BSFN or simple Table I/O as below,

5. (If needed, you may implement server timezone which is different from UTC - 0, or GMT) For this example, for simple implementation we simply sets UTC - 0 timezone

6. Set FROM and TO Time Zone values based on MODE flag

7. Retrieve Daylight Savings Flag

8. (Only when jdPromisedDeliveryDate is not NULL Date) If the time format is '1' then the time is in HHMM format. If no value is passed the time is in HHMMSS format

9. Convert User Date/Time to UTC format

10. Seconds are not considered by B9800009 and need to be appended to the converted value

11. Convert UTC Time to the requested time zone

11'. If the time format is '1' convert the time to HHMM format

12. Save it and compile

13. Table Trigger design for table to update

Implement above at,

Issue is coming in updating exiting order so call custom NER at the event of,

• Before Record Is Inserted
• Before Record Is Updated
• After Record Is Fetched

PROBLEM:

In updating Sales Order Detail lines, if the time gap for column RSDT is same with gap between two timezone (From and To) the value stored in server gets wrong. For example, User (UTC - 1) enters order as below,

(Note that there is 1 hour gap between two order lines)

Quantity for both lines are updated by same user, which updates

(Note that 2nd row is updated with 1st row column value)

Same issue occurs,

• User is sitting in (UTC - 2)
• Time gap between two lines are 2 hour (e.g., 08:00:00 and 10:00:00)

Or,

• User timezone is (UTC + 4)
• Time gap between two lines are -4 hour (e.g., 10:00:00 and 6:00:00)

What is possible cause of this and how to fix it?

SOLUTION

Issue is coming in updating exiting order so call custom NER at the event of,
- Before Record Is Inserted
- After Record Is Inserted

- Before Record Is Updated
- After Record is Updated
And,
- After Record Is Fetched

: this is based on existing routine for F4211_CURRENCY() and JDEUTime (database level update) which adjust display whenever it got accessed. So if you implement table trigger, your custom NER has to be called from all table events.