1 package body HZ_TIMEZONE_PKG as
2 /*$Header: ARHTZTHB.pls 115.4 2003/09/05 22:14:21 awu ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out nocopy VARCHAR2,
5 X_TIMEZONE_ID in NUMBER,
6 X_GLOBAL_TIMEZONE_NAME in VARCHAR2,
7 X_STANDARD_TIME_SHORT_CODE in VARCHAR2,
8 X_DAYLIGHT_SAVINGS_SHORT_CODE in VARCHAR2,
9 X_GMT_DEVIATION_HOURS in NUMBER,
10 X_DAYLIGHT_SAVINGS_TIME_FLAG in VARCHAR2,
11 X_BEGIN_DST_MONTH in VARCHAR2,
12 X_BEGIN_DST_DAY in NUMBER,
13 X_BEGIN_DST_WEEK_OF_MONTH in NUMBER,
14 X_BEGIN_DST_DAY_OF_WEEK in NUMBER,
15 X_BEGIN_DST_HOUR in NUMBER,
16 X_END_DST_MONTH in VARCHAR2,
17 X_END_DST_DAY in NUMBER,
18 X_END_DST_WEEK_OF_MONTH in NUMBER,
19 X_END_DST_DAY_OF_WEEK in NUMBER,
20 X_END_DST_HOUR in NUMBER,
21 X_PRIMARY_ZONE_FLAG in VARCHAR2,
22 X_NAME in VARCHAR2,
23 X_DESCRIPTION in VARCHAR2,
24 X_CREATION_DATE in DATE,
25 X_CREATED_BY in NUMBER,
26 X_LAST_UPDATE_DATE in DATE,
27 X_LAST_UPDATED_BY in NUMBER,
28 X_LAST_UPDATE_LOGIN in NUMBER
29 ) is
30 cursor C is select ROWID from HZ_TIMEZONES
31 where TIMEZONE_ID = X_TIMEZONE_ID
32 ;
33 begin
34 insert into HZ_TIMEZONES (
35 TIMEZONE_ID,
36 GLOBAL_TIMEZONE_NAME,
37 STANDARD_TIME_SHORT_CODE,
38 DAYLIGHT_SAVINGS_SHORT_CODE,
39 GMT_DEVIATION_HOURS,
40 DAYLIGHT_SAVINGS_TIME_FLAG,
41 BEGIN_DST_MONTH,
42 BEGIN_DST_DAY,
43 BEGIN_DST_WEEK_OF_MONTH,
44 BEGIN_DST_DAY_OF_WEEK,
45 BEGIN_DST_HOUR,
46 END_DST_MONTH,
47 END_DST_DAY,
48 END_DST_WEEK_OF_MONTH,
49 END_DST_DAY_OF_WEEK,
50 END_DST_HOUR,
51 PRIMARY_ZONE_FLAG,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_DATE,
55 LAST_UPDATED_BY,
56 LAST_UPDATE_LOGIN
57 ) values (
58 X_TIMEZONE_ID,
59 X_GLOBAL_TIMEZONE_NAME,
60 X_STANDARD_TIME_SHORT_CODE,
61 X_DAYLIGHT_SAVINGS_SHORT_CODE,
62 X_GMT_DEVIATION_HOURS,
63 X_DAYLIGHT_SAVINGS_TIME_FLAG,
64 X_BEGIN_DST_MONTH,
65 X_BEGIN_DST_DAY,
66 X_BEGIN_DST_WEEK_OF_MONTH,
67 X_BEGIN_DST_DAY_OF_WEEK,
68 X_BEGIN_DST_HOUR,
69 X_END_DST_MONTH,
70 X_END_DST_DAY,
71 X_END_DST_WEEK_OF_MONTH,
72 X_END_DST_DAY_OF_WEEK,
73 X_END_DST_HOUR,
74 X_PRIMARY_ZONE_FLAG,
75 X_CREATION_DATE,
76 X_CREATED_BY,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_LAST_UPDATE_LOGIN
80 );
81
82 insert into HZ_TIMEZONES_TL (
83 CREATION_DATE,
84 CREATED_BY,
85 LAST_UPDATE_DATE,
86 LAST_UPDATED_BY,
87 LAST_UPDATE_LOGIN,
88 TIMEZONE_ID,
89 NAME,
90 DESCRIPTION,
91 LANGUAGE,
92 SOURCE_LANG
93 ) select
94 X_CREATION_DATE,
95 X_CREATED_BY,
96 X_LAST_UPDATE_DATE,
97 X_LAST_UPDATED_BY,
98 X_LAST_UPDATE_LOGIN,
99 X_TIMEZONE_ID,
100 X_NAME,
101 X_DESCRIPTION,
102 L.LANGUAGE_CODE,
103 userenv('LANG')
104 from FND_LANGUAGES L
105 where L.INSTALLED_FLAG in ('I', 'B')
106 and not exists
107 (select NULL
108 from HZ_TIMEZONES_TL T
109 where T.TIMEZONE_ID = X_TIMEZONE_ID
110 and T.LANGUAGE = L.LANGUAGE_CODE);
111
112 open c;
113 fetch c into X_ROWID;
114 if (c%notfound) then
115 close c;
116 raise no_data_found;
117 end if;
118 close c;
119
120 end INSERT_ROW;
121
122 procedure LOCK_ROW (
123 X_TIMEZONE_ID in NUMBER,
124 X_GLOBAL_TIMEZONE_NAME in VARCHAR2,
125 X_STANDARD_TIME_SHORT_CODE in VARCHAR2,
126 X_DAYLIGHT_SAVINGS_SHORT_CODE in VARCHAR2,
127 X_GMT_DEVIATION_HOURS in NUMBER,
128 X_DAYLIGHT_SAVINGS_TIME_FLAG in VARCHAR2,
129 X_BEGIN_DST_MONTH in VARCHAR2,
130 X_BEGIN_DST_DAY in NUMBER,
131 X_BEGIN_DST_WEEK_OF_MONTH in NUMBER,
132 X_BEGIN_DST_DAY_OF_WEEK in NUMBER,
133 X_BEGIN_DST_HOUR in NUMBER,
134 X_END_DST_MONTH in VARCHAR2,
135 X_END_DST_DAY in NUMBER,
136 X_END_DST_WEEK_OF_MONTH in NUMBER,
137 X_END_DST_DAY_OF_WEEK in NUMBER,
138 X_END_DST_HOUR in NUMBER,
139 X_PRIMARY_ZONE_FLAG in VARCHAR2,
140 X_NAME in VARCHAR2,
141 X_DESCRIPTION in VARCHAR2
142 ) is
143 cursor c is select
144 GLOBAL_TIMEZONE_NAME,
145 STANDARD_TIME_SHORT_CODE,
146 DAYLIGHT_SAVINGS_SHORT_CODE,
147 GMT_DEVIATION_HOURS,
148 DAYLIGHT_SAVINGS_TIME_FLAG,
149 BEGIN_DST_MONTH,
150 BEGIN_DST_DAY,
151 BEGIN_DST_WEEK_OF_MONTH,
152 BEGIN_DST_DAY_OF_WEEK,
153 BEGIN_DST_HOUR,
154 END_DST_MONTH,
155 END_DST_DAY,
156 END_DST_WEEK_OF_MONTH,
157 END_DST_DAY_OF_WEEK,
158 END_DST_HOUR,
159 PRIMARY_ZONE_FLAG
160 from HZ_TIMEZONES
161 where TIMEZONE_ID = X_TIMEZONE_ID
162 for update of TIMEZONE_ID nowait;
163 recinfo c%rowtype;
164
165 cursor c1 is select
166 NAME,
167 DESCRIPTION,
168 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
169 from HZ_TIMEZONES_TL
170 where TIMEZONE_ID = X_TIMEZONE_ID
171 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
172 for update of TIMEZONE_ID nowait;
173 begin
174 open c;
175 fetch c into recinfo;
176 if (c%notfound) then
177 close c;
178 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
179 app_exception.raise_exception;
180 end if;
181 close c;
182 if ( (recinfo.GLOBAL_TIMEZONE_NAME = X_GLOBAL_TIMEZONE_NAME)
183 AND (recinfo.STANDARD_TIME_SHORT_CODE = X_STANDARD_TIME_SHORT_CODE)
184 AND ((recinfo.DAYLIGHT_SAVINGS_SHORT_CODE = X_DAYLIGHT_SAVINGS_SHORT_CODE)
185 OR ((recinfo.DAYLIGHT_SAVINGS_SHORT_CODE is null) AND (X_DAYLIGHT_SAVINGS_SHORT_CODE is null)))
186 AND (recinfo.DAYLIGHT_SAVINGS_TIME_FLAG = X_DAYLIGHT_SAVINGS_TIME_FLAG)
187 AND ((recinfo.BEGIN_DST_MONTH = X_BEGIN_DST_MONTH)
188 OR ((recinfo.BEGIN_DST_MONTH is null) AND (X_BEGIN_DST_MONTH is null)))
189 AND ((recinfo.BEGIN_DST_DAY = X_BEGIN_DST_DAY)
190 OR ((recinfo.BEGIN_DST_DAY is null) AND (X_BEGIN_DST_DAY is null)))
191 AND ((recinfo.BEGIN_DST_WEEK_OF_MONTH = X_BEGIN_DST_WEEK_OF_MONTH)
192 OR ((recinfo.BEGIN_DST_WEEK_OF_MONTH is null) AND (X_BEGIN_DST_WEEK_OF_MONTH is null)))
193 AND ((recinfo.BEGIN_DST_DAY_OF_WEEK = X_BEGIN_DST_DAY_OF_WEEK)
194 OR ((recinfo.BEGIN_DST_DAY_OF_WEEK is null) AND (X_BEGIN_DST_DAY_OF_WEEK is null)))
195 AND ((recinfo.BEGIN_DST_HOUR = X_BEGIN_DST_HOUR)
196 OR ((recinfo.BEGIN_DST_HOUR is null) AND (X_BEGIN_DST_HOUR is null)))
197 AND ((recinfo.END_DST_MONTH = X_END_DST_MONTH)
198 OR ((recinfo.END_DST_MONTH is null) AND (X_END_DST_MONTH is null)))
199 AND ((recinfo.END_DST_DAY = X_END_DST_DAY)
200 OR ((recinfo.END_DST_DAY is null) AND (X_END_DST_DAY is null)))
201 AND ((recinfo.END_DST_WEEK_OF_MONTH = X_END_DST_WEEK_OF_MONTH)
202 OR ((recinfo.END_DST_WEEK_OF_MONTH is null) AND (X_END_DST_WEEK_OF_MONTH is null)))
203 AND ((recinfo.END_DST_DAY_OF_WEEK = X_END_DST_DAY_OF_WEEK)
204 OR ((recinfo.END_DST_DAY_OF_WEEK is null) AND (X_END_DST_DAY_OF_WEEK is null)))
205 AND ((recinfo.END_DST_HOUR = X_END_DST_HOUR)
206 OR ((recinfo.END_DST_HOUR is null) AND (X_END_DST_HOUR is null)))
207 AND ((recinfo.PRIMARY_ZONE_FLAG = X_PRIMARY_ZONE_FLAG)
208 OR ((recinfo.PRIMARY_ZONE_FLAG is null) AND (X_PRIMARY_ZONE_FLAG is null)))
209 ) then
210 null;
211 else
212 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
213 app_exception.raise_exception;
214 end if;
215
216 for tlinfo in c1 loop
217 if (tlinfo.BASELANG = 'Y') then
218 if ( (tlinfo.NAME = X_NAME)
219 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
220 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
221 ) then
222 null;
223 else
224 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
225 app_exception.raise_exception;
226 end if;
227 end if;
228 end loop;
229 return;
230 end LOCK_ROW;
231
232 procedure UPDATE_ROW (
233 X_TIMEZONE_ID in NUMBER,
234 X_GLOBAL_TIMEZONE_NAME in VARCHAR2,
235 X_STANDARD_TIME_SHORT_CODE in VARCHAR2,
236 X_DAYLIGHT_SAVINGS_SHORT_CODE in VARCHAR2,
237 X_GMT_DEVIATION_HOURS in NUMBER,
238 X_DAYLIGHT_SAVINGS_TIME_FLAG in VARCHAR2,
239 X_BEGIN_DST_MONTH in VARCHAR2,
240 X_BEGIN_DST_DAY in NUMBER,
241 X_BEGIN_DST_WEEK_OF_MONTH in NUMBER,
242 X_BEGIN_DST_DAY_OF_WEEK in NUMBER,
243 X_BEGIN_DST_HOUR in NUMBER,
244 X_END_DST_MONTH in VARCHAR2,
245 X_END_DST_DAY in NUMBER,
246 X_END_DST_WEEK_OF_MONTH in NUMBER,
247 X_END_DST_DAY_OF_WEEK in NUMBER,
248 X_END_DST_HOUR in NUMBER,
249 X_PRIMARY_ZONE_FLAG in VARCHAR2,
250 X_NAME in VARCHAR2,
251 X_DESCRIPTION in VARCHAR2,
252 X_LAST_UPDATE_DATE in DATE,
253 X_LAST_UPDATED_BY in NUMBER,
254 X_LAST_UPDATE_LOGIN in NUMBER
255 ) is
256 begin
257 update HZ_TIMEZONES set
258 GLOBAL_TIMEZONE_NAME = X_GLOBAL_TIMEZONE_NAME,
259 STANDARD_TIME_SHORT_CODE = X_STANDARD_TIME_SHORT_CODE,
260 DAYLIGHT_SAVINGS_SHORT_CODE = X_DAYLIGHT_SAVINGS_SHORT_CODE,
261 GMT_DEVIATION_HOURS = X_GMT_DEVIATION_HOURS,
262 DAYLIGHT_SAVINGS_TIME_FLAG = X_DAYLIGHT_SAVINGS_TIME_FLAG,
263 BEGIN_DST_MONTH = X_BEGIN_DST_MONTH,
264 BEGIN_DST_DAY = X_BEGIN_DST_DAY,
265 BEGIN_DST_WEEK_OF_MONTH = X_BEGIN_DST_WEEK_OF_MONTH,
266 BEGIN_DST_DAY_OF_WEEK = X_BEGIN_DST_DAY_OF_WEEK,
267 BEGIN_DST_HOUR = X_BEGIN_DST_HOUR,
268 END_DST_MONTH = X_END_DST_MONTH,
269 END_DST_DAY = X_END_DST_DAY,
270 END_DST_WEEK_OF_MONTH = X_END_DST_WEEK_OF_MONTH,
271 END_DST_DAY_OF_WEEK = X_END_DST_DAY_OF_WEEK,
272 END_DST_HOUR = X_END_DST_HOUR,
273 PRIMARY_ZONE_FLAG = X_PRIMARY_ZONE_FLAG,
274 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
275 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
276 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
277 where TIMEZONE_ID = X_TIMEZONE_ID;
278
279 if (sql%notfound) then
280 raise no_data_found;
281 end if;
282
283 update HZ_TIMEZONES_TL set
284 NAME = X_NAME,
285 DESCRIPTION = X_DESCRIPTION,
286 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
287 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
288 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
289 SOURCE_LANG = userenv('LANG')
290 where TIMEZONE_ID = X_TIMEZONE_ID
291 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
292
293 if (sql%notfound) then
294 raise no_data_found;
295 end if;
296 end UPDATE_ROW;
297
298 procedure DELETE_ROW (
299 X_TIMEZONE_ID in NUMBER
300 ) is
301 begin
302 delete from HZ_TIMEZONES_TL
303 where TIMEZONE_ID = X_TIMEZONE_ID;
304
305 if (sql%notfound) then
306 raise no_data_found;
307 end if;
308
309 delete from HZ_TIMEZONES
310 where TIMEZONE_ID = X_TIMEZONE_ID;
311
312 if (sql%notfound) then
313 raise no_data_found;
314 end if;
315 end DELETE_ROW;
316
317 procedure ADD_LANGUAGE
318 is
319 begin
320 delete from HZ_TIMEZONES_TL T
321 where not exists
322 (select NULL
323 from HZ_TIMEZONES B
324 where B.TIMEZONE_ID = T.TIMEZONE_ID
325 );
326
327 update HZ_TIMEZONES_TL T set (
328 NAME,
329 DESCRIPTION
330 ) = (select
331 B.NAME,
332 B.DESCRIPTION
333 from HZ_TIMEZONES_TL B
334 where B.TIMEZONE_ID = T.TIMEZONE_ID
335 and B.LANGUAGE = T.SOURCE_LANG)
336 where (
337 T.TIMEZONE_ID,
338 T.LANGUAGE
339 ) in (select
340 SUBT.TIMEZONE_ID,
341 SUBT.LANGUAGE
342 from HZ_TIMEZONES_TL SUBB, HZ_TIMEZONES_TL SUBT
343 where SUBB.TIMEZONE_ID = SUBT.TIMEZONE_ID
344 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
345 and (SUBB.NAME <> SUBT.NAME
346 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
347 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
348 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
349 ));
350
351 insert into HZ_TIMEZONES_TL (
352 CREATION_DATE,
353 CREATED_BY,
354 LAST_UPDATE_DATE,
355 LAST_UPDATED_BY,
356 LAST_UPDATE_LOGIN,
357 TIMEZONE_ID,
358 NAME,
359 DESCRIPTION,
360 LANGUAGE,
361 SOURCE_LANG
362 ) select
363 B.CREATION_DATE,
364 B.CREATED_BY,
365 B.LAST_UPDATE_DATE,
366 B.LAST_UPDATED_BY,
367 B.LAST_UPDATE_LOGIN,
368 B.TIMEZONE_ID,
369 B.NAME,
370 B.DESCRIPTION,
371 L.LANGUAGE_CODE,
372 B.SOURCE_LANG
373 from HZ_TIMEZONES_TL B, FND_LANGUAGES L
374 where L.INSTALLED_FLAG in ('I', 'B')
375 and B.LANGUAGE = userenv('LANG')
376 and not exists
377 (select NULL
378 from HZ_TIMEZONES_TL T
379 where T.TIMEZONE_ID = B.TIMEZONE_ID
380 and T.LANGUAGE = L.LANGUAGE_CODE);
381 end ADD_LANGUAGE;
382
383 end HZ_TIMEZONE_PKG;
[Home] [Help]