Posting date issue due to time zones and the use of SystemDateGet()

Question

Summary

We are experiencing a problem where the systemDateGet() function is returning the AOS wher the local date is required when determining the date for posting purposes.

Details

• We have branches around the world, both sides of the international date line, with users connecting to local terminal servers with the appropriate time zone settings for the user's branch.

• Each branch is associated with a different company, which has been configured with the correct time zone.

• We run a single AOS with the time zone setting set for the head office. There is not an option to introduce additional AOS's.

• The SystemDateGet() function is returning the AOS date as the user is not changing their Axapta session date.

• A number of system fields in the database related to posting dates are DATE based and not UTCDATETIME based.

• We are running AX2009 SP1 RU7.

  • Kernel version 5.0.1500.4570
  • Application version 5.0.1500.4570
  • Localization version: Brazil, China, Japan, Thailand, India

• I am aware that the SystemDateGet() function was designed to return the AOS date unless the user changes their session date in which case that date is returned.

• Each user has the appropriate time zone setting in there user profile.

Problem

One example of the problem is when a user attempts to post a journal involving financial transactions, where the ledger period is checked to see if it is open. For example, the user is in England attempting to post a journal at 3:00pm on the 30st of November, local time, the standard Axapta code uses the systemDateGet() function to determine the date to use in the validation (determining if the period is open). In our case, the AOS is based in Brisbane Australia and the systemDateGet() function is returning the 1st of December (local time 1:00am on the 1st of December).

Another example of the problem is where an invoice is posted in the United States on a Friday and the day of the week where the AOS is situated is a Saturday. We need the system to record the local date.

Question

Besides rewriting all system code involving systemDateGet(), over 2000 entities, is there any other options that can be used to get around the problem of getting the correct local date?

Solution limitations.

• In the example given above of the ledger period being closed, it is not possible from a business practices standpoint to open the next period before end of month processing has been completed.

• There is currently no option for the purchase of additional AOS's.

Answer 1

Here is a quick update. Let me know if there are any issues or situations that need to be addressed.

USING LOCAL TIME

Introduction:
The following is a work in progress update, as the solution has yet to be fully tested in all situations.

Problem:
If the user has a different time zone to the AOS, the timeNow() and systemDateGet() functions are returning the AOS details when the local date time is required.

Clarifiers:
When the local date time is changed within Axapta, the systemDateGet() function will return the local date, however the timeNow() function still returns the AOS time.

Coding changes:
A number of coding changes have been made to handle a number of different situations. Comments will be added where an explanation may be required.

The brackets were changed to { and } to allow this to be posted.

CLASS:GLOBAL

One requirement I was given was to allow the system to handle multiple sites within a company that may have different time zones. Presently this functionality is not required.

static server void setSessionDateTime(
    inventSiteId    inventSiteId = '', 
    utcDateTime     reference = dateTimeUtil::utcNow())
{
    str                             sql;
    sqlStatementExecutePermission   perm;
    connection                      conn        = new UserConnection();
    timeZone                        timeZone;
    int                             ret;
    ;

    if (inventSiteId)
    {
        timeZone = inventSite::find(inventSiteId).Timezone;
    }
    else
    {
        timeZone = dateTimeUtil::getCompanyTimeZone();
    }

    //This is to get around the kernel validation of changing timezones
    //when the user has more than one session open.

    sql     = strfmt("Update userInfo set preferredTimeZone = %1 where userInfo.id = '%2'", enum2int(timeZone), curUserId());
    perm    = new SQLStatementExecutePermission(sql);
    perm.assert();

    ret = conn.createStatement().executeUpdate(sql);

    dateTimeUtil::setUserPreferredTimeZone(timeZone);
    dateTimeUtil::setSystemDateTime(reference);

    CodeAccessPermission::revertAssert();
}

static int localTime()
{
    utcDateTime tmp;
    ;
    setSessionDateTime();

    tmp = dateTimeUtil::applyTimeZoneOffset( dateTimeUtil::utcNow(), dateTimeUtil::getCompanyTimeZone());
    return dateTimeUtil::time(tmp);
}

The following method was implemented as a cross check to ensure that systemDateGet() returns the expected value.

static date localDate()
{
    utcDateTime tmp;
    ;
    setSessionDateTime();

    tmp = dateTimeUtil::applyTimeZoneOffset( dateTimeUtil::utcNow(), dateTimeUtil::getCompanyTimeZone());
    return dateTimeUtil::date(tmp);
}

CLASS:APPLICATION

Modify the method setDefaultCompany. Add the line setSessionDateTime(); directly after the super call. This is to allow the time to change when the user changes company (another requirement I was given).

CLASS:INFO

So that the system uses the correct date/time from the start of the session.

void startupPost()
{
    ;
    setSessionDateTime();
}

Modify the method canViewAlertInbox() adding the line setSessionDateTime(); as the first line. This is to handle if the user has multiple forms open for different companies.

Localization dependent changes:

Depending on your service pack and localizations, you will need to change a function of objects to use the localTime() function, replacing timeNow(). IMPORTANT NOTE: Do not change the class BatchRun to use the new localTime function as this will stop it working correctly.

In our system there were around 260 instances that could be changed. If you do not use all modules and localizations the actual number of lines you need to change will be less.

Final note:

There are a number of today() calls in the code. I have not yet gone through each line to ensure it is coded correctly, i.e. using today() instead of systemDateGet().

Known issues:

I have come across a situation where the timezone change function did not work completely as expected. This was when one session was doing a database synchronisation and another session was opened in a different company. As normal users will never be able to do this, I have not spent much time on its solution at this stage. It is something I do intend to resolve.

Answer 2

Create a function in the Global class:

static date systemDateGetLocal()
{
    return DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::utcNow(), DateTimeUtil::getUserPreferredTimeZone()));
}

Then in Info.watchDog() do a:

systemDateSet(systemDateGetLocal());

This may only be a partial solution, the watchDog is executed on the client side only.