Milestone template for Order Release Event

[zonikee]

Hi,

I have a milestone template done for a Order Release Event. However, the alert gets delayed as it only gets triggered when the Event Occured Date expires in GMT timezone. But, the event that i am logging is in Asia/Jakarta timezone. Yet, when i checked in the Order Release Event History table, the timezone gid is always set to 'Local'.

Is there anywhere to get the milestone template to run the Event Occured Date in Asia/Jakarta timezone or is there any method to Add Order Release Event in Asia/Jakarta timezone?

Please note that the Order Release Event is logged through interface and not through XML integration.

Appreciate any feedback. Thanks!

[josephliang]

Re: Milestone template for Order Release Event

Not sure this is a bug or not.

Based on my experiences, the way that OTM stores event_date in status_history table is:
If time_zone_gid='Local', then it means date recorded is in local time zone. No time zone conversion when displaying on screen.
If time_zone_gid= any time zone gid, event_date is in UTC time zone. When the date is displaying on the screen, OTM will convert it based on time_zone_gid.

So, maybe Milestone treats all event_date in UTC regardless time_zone_gid.

There might be a workaround by saving Event Date in UTC as Refnum using a Agent listens to Status Received event.

select from_tz(cast(event_date as timestamp),
decode(time_zone_gid,'Local',time_zone_gid,'UTC')) at time zone 'UTC'
from order_release_status_history
where i_transaction_no=$i_transaction_no

Then use Saved Query as Based On in Milestone Link.

[zonikee]

Re: Milestone template for Order Release Event

Thanks Joseph! i will give it a shot.to see if it works

However, i still find it rather odd that OTM does not allow timezone as an input while logging Order Release Event and the milestone template always pick up dates in UTC.

[zonikee]

Re: Milestone template for Order Release Event

I have tried the suggested method.
And i got a weird error in my SQL, it should be a relatively easy to fix error.
But i still cant spot till now.

2009-10-01 10:28:18.270 817662 Error Exception select ORDER_RELEASE_REFNUM_VALUE from order_release_refnum where order_release_gid=$gid and
ORDER_RELEASE_REFNUM_QUAL_GID='T021.ETA GMT' [SCEM - 1]
2009-10-01 10:28:18.277 817662 Error Exception java.sql.SQLException: ORA-00911: invalid character

I have changed my query serveral times. But its still not working.
Can anyone spot anything?

Thanks!

[LourensGlog]

Re: Milestone template for Order Release Event

Try unchecking the Refesh Cache tickbox in your Direct SQL Statement of your agent.