Timezone Support in AIA PIPs involving Oracle EBS as an edge application
By ankit.goel on Jul 30, 2009
Conversion between timezones of the participating edge applications is required when the integration specific logic or the data being exchanged involves date with timestamp attributes.
In Oracle EBS, DATE fields do not store timezone information and are supposed to be in EBS server timezone. In all UI transactions, it is assumed that the EBS User Interface will convert from User (client) TZ to EBS server TZ while pushing data into db, and from EBS server TZ to user TZ while pulling out data from db on to the UI.
The same principle can be applied for AIA based interface with EBS. In AIA Provider ABCS, AIA layer (client for EBS) should first query the EBS server TZ or have it as a configuration property (there already exists a "Ebiz" module property EBIZ_01.SERVER_TIMEZONE in AIAConfigurationProperties). It should then convert the incoming xsd:dateTime value in the EBM to EBS server TZ, and then pass the converted date value to EBS APIs as a DATE field.
To convert between timezones, EBS Integrations has developed and made available a generic custom XPath function ebi:getConvertedDate() which has been used in a couple of PIPs; similar methodology can be adopted in other PIPs. To get TZ from xsd:dateTime field, a bpel function can be used - timezone-from-dateTime(). In order to get EBS server TZ, following query can be used:
select timezone_code from fnd_timezones_b
where upgrade_tz_id = fnd_profile.value(’SERVER_TIMEZONE_ID’);
[While calling EBS APIs using Oracle Apps Adapter, currently EBS doesn't support timestamp with timezone type in PL/SQL API signatures. So the dateTime field (including TZ) in Enterprise Business Message (EBM) cannot be directly mapped and passed to EBS API. Even if one tries passing a date with timezone to a DATE field in PL/SQL, Oracle Apps Adapter does an automatic timezone conversion to the timezone where the JVM is running which can lead to incorrect results.]
Similarly, in Requestor ABCS, while retrieving data from EBS, AIA layer should query the EBS server TZ, and then convert the retrieved DATE from EBS server TZ to EBM/AIA TZ. The EBS wrapper API can be made to return in GMT always, or it can be made to return in a specific TZ(configurable at AIA layer) passed into it as input parameter. In case of direct query from db, the SQL can be written to return the date value in GMT or in the configured TZ.