Data mismatch

[nikesh mohanlal]

Hi All,

Urgency:Yes
Version:5.5 CU3

I have developed a finder screen for Order Release .I create a order release with twos cenarios:
1.Give early pickupdate as oct 10,2007,00:00 hrs and let the order release gid be assumed as 'X1'(after creation)
2.Give early pickupdate as oct 10,2007,07:00 hrs and let the order release gid be assumed as 'X2'(after creation)

But When I search with early pickupdate as Oct 9,2007 I get to see the 'X1' order release.And When I serach with early pickupdate as Oct10,2007,I see only 'X2' order release.The reason is because in DB,it stores for 'X1' order release,the early pickupdate as Oct 9,2007:22:00 hrs and for 'X2' order release,the early pickupdate as Oct 10,2007:05:00 hrs.That means what value I give,it subtracts 2 hrs less and stores in DB.So I get 'X1' order release record also when I search with early pickupdate as Oct 9,2007 .

Can anyone let me know how to make the time in DB and UI in synch.Also please let me know if my understanding is wrong and also the solution for the above problem.

I have not set any preference regarding time zone.The time dispalyed in UI is from CET standard.

Thanking and regards,
Nikesh.

[mldeuel]

Re: Data mismatch

Dates are stored in UTC/GMT in the database. The UI converts the dates to the "local" date/time based on the timezone for the location that date is associated to.

You really do not want the dates in the UI and database to be the same this would negatively impact a lot of things in OTM let alone the business....for example, when I tender is sent out to a carrier, OTM will convert the pickup date/time on the tender to the timezone for the location where the shipment is to be picked up....if you do away with this conversion (which I don't believe you can do) this would result in the carrier being notified of the wrong time to pick up the shipment which could result in additional charges for the carrier to come back at the correct time to pick up the shipment, etc.....

I believe this is explained in the documentation supplied w/ the install, or you can go to Oracle's site to get copies of the documentation (use the link supplied in OTM Help).

[chrisplough]

Re: Data mismatch

In addition, you can look at the following post, which gives a few more documentation links. As mldeuel noted, though - please be careful, and be sure to check the OTM online help - it's pretty good.

http://www.otmfaq.com/forums/f63/solved-br100-bfa-66/#post122

--Chris

[divya]

Re: Data mismatch

Could you please let me know how do we write a SQL Query to retrieve Order Releases for a particular date since the database and the UI times differ?