1 PACKAGE BODY flm_timezone AS
2 /* $Header: FLMTMZOB.pls 120.1 2011/11/28 10:54:34 sisankar ship $*/
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | FILE NAME : FLMTMZOB.pls |
9 | DESCRIPTION : This package contains functions used to provide timezone |
10 | support |
11 | MODIFICATION HISTORY: |
12 | Hadi Wenas 10/14/03 Created |
13 +===========================================================================*/
14
15 g_pkg_name CONSTANT VARCHAR2(30) := 'FLM_Timezone';
16
17 /*
18 Removed global variables:
19 - g_offset
20
21 Removed the following procedures:
22 - get_offset()
23 - calendar_to_client()
24 - client_to_calendar()
25 */
26 --end of fix bug#3827600
27
28 FUNCTION is_init RETURN BOOLEAN IS
29 BEGIN
30 return g_init;
31 END is_init;
32
33 /*
34 init_timezone has to be called before using the following functions:
35 - server_to_calendar
36 - calendar_to_server
37 */
38 PROCEDURE init_timezone(p_org_id NUMBER) IS
39 l_client_offset NUMBER;
40 l_server_offset NUMBER;
41 l_start_time NUMBER;
42 l_offset BOOLEAN;
43 l_temp_date DATE; --fix bug#3827600
44 BEGIN
45
46 IF g_enabled THEN
47
48 SELECT gmt_deviation_hours
49 INTO l_client_offset
50 FROM hz_timezones
51 WHERE timezone_id = g_client_id;
52
53 SELECT gmt_deviation_hours
54 INTO l_server_offset
55 FROM hz_timezones
56 WHERE timezone_id = g_server_id;
57
58 IF p_org_id IS NOT NULL THEN
59
60 SELECT start_time
61 INTO l_start_time
62 FROM wip_lines
63 WHERE organization_id = p_org_id
64 AND (disable_date IS NULL
65 OR disable_date > Sysdate)
66 AND ROWNUM=1
67 ORDER BY line_code;
68
69 --fix bug#3827600
70 --Init new global variables
71 g_org_id := p_org_id;
72 g_server_start_time := l_start_time;
73 l_temp_date := trunc(sysdate);
74 l_temp_date := l_temp_date + (l_start_time/86400);
75 l_temp_date := server_to_client(l_temp_date);
76 g_client_start_time := to_number(to_char(l_temp_date,'SSSSS'));
77 --end of fix bug#3827600
78
79 ELSE
80 --fix bug#3827600
81 --Init new global variables
82 g_org_id := NULL;
83 g_client_start_time := 0;
84 g_server_start_time := to_char(client_to_server(trunc(sysdate)),'SSSSS');
85 --end of fix bug#3827600
86 END IF;
87
88 END IF;
89
90 g_init := TRUE;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
95 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Init Timezone');
96 END IF;
97 END init_timezone;
98
99 /*fix bug#3827600
100 Modified the logic completely as follows:
101 1. Convert server date to client
102 2. Trunc it to get the 00:00:00 at client
103 3. Add the client line start time
104 4. Convert back to server
105 5. Trunc it to get the calendar
106 */
107 FUNCTION server_to_calendar(p_server_date IN DATE) RETURN DATE IS
108 l_server_time NUMBER;
109 l_server_date NUMBER;
110 l_calendar_date DATE;
111 e_not_init EXCEPTION;
112 BEGIN
113 IF p_server_date IS NULL THEN
114 RETURN NULL;
115 END IF;
116 -- Bug 9830320.
117 IF NOT g_init THEN
118 init_timezone(null);
119 END IF;
120
121 IF g_enabled THEN
122 l_calendar_date := server_to_client(p_server_date);
123 l_calendar_date := trunc(l_calendar_date) + (g_client_start_time/86400);
124 l_calendar_date := client_to_server(l_calendar_date);
125 l_calendar_date := trunc(l_calendar_date);
126
127 ELSE
128 l_calendar_date := trunc(p_server_date);
129 END IF;
130
131 RETURN l_calendar_date;
132 EXCEPTION
133 WHEN e_not_init THEN
134 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
135 fnd_msg_pub.add_exc_msg(g_pkg_name, 'FLM Timezone is not initialized');
136 END IF;
137 RETURN Trunc(p_server_date);
138 WHEN OTHERS THEN
139 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
140 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Convert server to CALENDAR Date');
141 END IF;
142 RETURN Trunc(p_server_date);
143 END server_to_calendar;
144
145
146 --fix bug#3840945: Reinserted function client_to_calendar() since wipsfcbb.pls is using it.
147 FUNCTION client_to_calendar(p_client_date IN DATE) RETURN DATE IS
148 l_server_date DATE;
149 e_not_init EXCEPTION;
150 BEGIN
151 IF p_client_date IS NULL THEN
152 RETURN NULL;
153 END IF;
154 -- Bug 9830320.
155 IF NOT g_init THEN
156 init_timezone(null);
157 END IF;
158
159 IF g_enabled THEN
160 l_server_date := client_to_server(p_client_date);
161 ELSE
162 l_server_date := p_client_date;
163 END IF;
164
165 RETURN server_to_calendar(l_server_date);
166 EXCEPTION
167 WHEN e_not_init THEN
168 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
169 fnd_msg_pub.add_exc_msg(g_pkg_name, 'FLM Timezone is not initialized');
170 END IF;
171 RETURN Trunc(p_client_date);
172 WHEN OTHERS THEN
173 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
174 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Convert client to CALENDAR date');
175 END IF;
176 RETURN Trunc(p_client_date);
177 END client_to_calendar;
178
179
180 /*fix bug#3827600
181 Modified the logic completely as follows:
182 1. If server_time parameter is null, default to client00_at_server
183 2. Convert (sysdate+server_time) from server to calendar
184 4. If the day shifts adjust (calendar_date+server_time) accordingly (-1 or +1)
185 */
186 FUNCTION calendar_to_server(p_calendar_date IN DATE, p_server_time IN NUMBER DEFAULT NULL) RETURN DATE IS
187 l_server_time NUMBER;
188 l_calendar_date DATE;
189 l_server_date DATE;
190 e_not_init EXCEPTION;
191 l_temp_num NUMBER;
192 l_temp_server DATE;
193 l_temp_calendar DATE;
194 BEGIN
195 IF p_calendar_date IS NULL THEN
196 RETURN NULL;
197 END IF;
198 -- Bug 9830320.
199 IF NOT g_init THEN
200 init_timezone(null);
201 END IF;
202
203 IF g_enabled THEN
204 IF (p_server_time IS NULL) THEN
205 l_server_time := to_char(client_to_server(trunc(sysdate)),'SSSSS');
206 ELSE
207 l_server_time := p_server_time;
208 END IF;
209
210 l_temp_server := trunc(sysdate)+(l_server_time/86400);
211 l_temp_calendar := server_to_calendar(l_temp_server);
212
213 IF (trunc(l_temp_server) = l_temp_calendar) THEN
214 l_server_date := p_calendar_date + (l_server_time/86400);
215 ELSIF (trunc(l_temp_server) > l_temp_calendar) THEN
216 l_server_date := p_calendar_date+1+(l_server_time/86400);
217 ELSE
218 l_server_date := p_calendar_date-1+(l_server_time/86400);
219 END IF;
220
221 ELSE
222 l_server_date := p_calendar_date;
223 END IF;
224
225 RETURN l_server_date;
226 EXCEPTION
227 WHEN e_not_init THEN
228 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
229 fnd_msg_pub.add_exc_msg(g_pkg_name, 'FLM Timezone is not initialized');
230 END IF;
231 RETURN Trunc(p_calendar_date);
232 WHEN OTHERS THEN
233 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
234 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Convert CALENDAR to server date');
235 END IF;
236 RETURN Trunc(p_calendar_date);
237 END calendar_to_server;
238
239
240 FUNCTION server_to_client(p_server_date IN DATE) RETURN DATE IS
241 l_client_date DATE;
242 BEGIN
243 IF p_server_date IS NULL THEN
244 RETURN NULL;
245 END IF;
246
247 IF g_enabled THEN
248 l_client_date := hz_timezone_pub.convert_datetime(g_server_id,
249 g_client_id,
250 p_server_date);
251 ELSE
252 l_client_date := p_server_date;
253 END IF;
254
255 RETURN l_client_date;
256 EXCEPTION
257 WHEN OTHERS THEN
258 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
259 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Convert server to client date');
260 END IF;
261 RETURN p_server_date;
262 END server_to_client;
263
264
265 FUNCTION client_to_server(p_client_date IN DATE) RETURN DATE IS
266 l_server_date DATE;
267 BEGIN
268 IF p_client_date IS NULL THEN
269 RETURN NULL;
270 END IF;
271
272 IF g_enabled THEN
273 l_server_date := hz_timezone_pub.convert_datetime(g_client_id,
274 g_server_id,
275 p_client_date);
276 ELSE
277 l_server_date := p_client_date;
278 END IF;
279
280 RETURN l_server_date;
281 EXCEPTION
282 WHEN OTHERS THEN
283 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
284 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Convert client to server');
285 END IF;
286 RETURN p_client_date;
287 END client_to_server;
288
289
290 FUNCTION client00_in_server(p_server_date IN DATE) RETURN DATE IS
291 l_return_date DATE;
292 BEGIN
293 IF p_server_date IS NULL THEN
294 RETURN NULL;
295 END IF;
296
297 IF g_enabled THEN
298 l_return_date := server_to_client(p_server_date);
299 l_return_date := client_to_server(trunc(l_return_date));
300
301 ELSE
302 l_return_date := Trunc(p_server_date);
303 END IF;
304
305 RETURN l_return_date;
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
309 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Get client00 in server');
310 END IF;
311 RETURN p_server_date;
312 END client00_in_server;
313
314
315 FUNCTION sysdate00_in_server RETURN DATE IS
316 l_return_date DATE;
317 BEGIN
318 IF g_enabled THEN
319 l_return_date := server_to_client(sysdate);
320 l_return_date := client_to_server(trunc(l_return_date));
321
322 ELSE
323 l_return_date := Trunc(Sysdate);
324 END IF;
325
326 RETURN l_return_date;
327 EXCEPTION
328 WHEN OTHERS THEN
329 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
330 fnd_msg_pub.add_exc_msg(g_pkg_name, 'Get sysdate00 in server');
331 END IF;
332 RETURN trunc(sysdate);
333 END sysdate00_in_server;
334
335
336 END flm_timezone;
[Home] [Help]