[Home] [Help]
PACKAGE BODY: APPS.FND_TIMEZONES_PKG
Source
1 package body FND_TIMEZONES_PKG as
2 /* $Header: AFTZTBB.pls 120.2 2005/10/21 07:32:04 dbowles ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_TIMEZONE_CODE in VARCHAR2,
6 X_ENABLED_FLAG in VARCHAR2,
7 X_GMT_OFFSET in NUMBER,
8 X_DAYLIGHT_SAVINGS_FLAG in VARCHAR2,
9 X_ACTIVE_TIMEZONE_CODE in VARCHAR2,
10 X_UPGRADE_TZ_ID in NUMBER,
11 X_NAME in VARCHAR2,
12 X_CREATION_DATE in DATE,
13 X_CREATED_BY in NUMBER,
14 X_LAST_UPDATE_DATE in DATE,
15 X_LAST_UPDATED_BY in NUMBER,
16 X_LAST_UPDATE_LOGIN in NUMBER
17 ) is
18 cursor C is select ROWID from FND_TIMEZONES_B
19 where TIMEZONE_CODE = X_TIMEZONE_CODE
20 ;
21 begin
22 insert into FND_TIMEZONES_B (
23 TIMEZONE_CODE,
24 ENABLED_FLAG,
25 GMT_OFFSET,
26 DAYLIGHT_SAVINGS_FLAG,
27 ACTIVE_TIMEZONE_CODE,
28 UPGRADE_TZ_ID,
29 CREATION_DATE,
30 CREATED_BY,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 LAST_UPDATE_LOGIN
34 ) values (
35 X_TIMEZONE_CODE,
36 X_ENABLED_FLAG,
37 X_GMT_OFFSET,
38 X_DAYLIGHT_SAVINGS_FLAG,
39 X_ACTIVE_TIMEZONE_CODE,
40 X_UPGRADE_TZ_ID,
41 X_CREATION_DATE,
42 X_CREATED_BY,
43 X_LAST_UPDATE_DATE,
44 X_LAST_UPDATED_BY,
45 X_LAST_UPDATE_LOGIN
46 );
47
48 insert into FND_TIMEZONES_TL (
49 TIMEZONE_CODE,
50 NAME,
51 CREATION_DATE,
52 CREATED_BY,
53 LAST_UPDATE_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_LOGIN,
56 LANGUAGE,
57 SOURCE_LANG
58 ) select
59 X_TIMEZONE_CODE,
60 X_NAME,
61 X_CREATION_DATE,
62 X_CREATED_BY,
63 X_LAST_UPDATE_DATE,
64 X_LAST_UPDATED_BY,
65 X_LAST_UPDATE_LOGIN,
66 L.LANGUAGE_CODE,
67 userenv('LANG')
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from FND_TIMEZONES_TL T
73 where T.TIMEZONE_CODE = X_TIMEZONE_CODE
74 and T.LANGUAGE = L.LANGUAGE_CODE);
75
76 open c;
77 fetch c into X_ROWID;
78 if (c%notfound) then
79 close c;
80 raise no_data_found;
81 end if;
82 close c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_TIMEZONE_CODE in VARCHAR2,
88 X_ENABLED_FLAG in VARCHAR2,
89 X_GMT_OFFSET in NUMBER,
90 X_DAYLIGHT_SAVINGS_FLAG in VARCHAR2,
91 X_ACTIVE_TIMEZONE_CODE in VARCHAR2,
92 X_UPGRADE_TZ_ID in NUMBER,
93 X_NAME in VARCHAR2
94 ) is
95 cursor c is select
96 ENABLED_FLAG,
97 GMT_OFFSET,
98 DAYLIGHT_SAVINGS_FLAG,
99 ACTIVE_TIMEZONE_CODE,
100 UPGRADE_TZ_ID
101 from FND_TIMEZONES_B
102 where TIMEZONE_CODE = X_TIMEZONE_CODE
103 for update of TIMEZONE_CODE nowait;
104 recinfo c%rowtype;
105
106 cursor c1 is select
107 NAME,
108 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
109 from FND_TIMEZONES_TL
110 where TIMEZONE_CODE = X_TIMEZONE_CODE
111 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
112 for update of TIMEZONE_CODE nowait;
113 begin
114 open c;
115 fetch c into recinfo;
116 if (c%notfound) then
117 close c;
118 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
119 app_exception.raise_exception;
120 end if;
121 close c;
122 if ( (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
123 AND (recinfo.GMT_OFFSET = X_GMT_OFFSET)
124 AND (recinfo.DAYLIGHT_SAVINGS_FLAG = X_DAYLIGHT_SAVINGS_FLAG)
125 AND ((recinfo.ACTIVE_TIMEZONE_CODE = X_ACTIVE_TIMEZONE_CODE)
126 OR ((recinfo.ACTIVE_TIMEZONE_CODE is null) AND (X_ACTIVE_TIMEZONE_CODE is null)))
127 AND ((recinfo.UPGRADE_TZ_ID = X_UPGRADE_TZ_ID)
128 OR ((recinfo.UPGRADE_TZ_ID is null) AND (X_UPGRADE_TZ_ID is null)))
129 ) then
130 null;
131 else
132 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
133 app_exception.raise_exception;
134 end if;
135
136 for tlinfo in c1 loop
137 if (tlinfo.BASELANG = 'Y') then
138 if ( (tlinfo.NAME = X_NAME)
139 ) then
140 null;
141 else
142 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
143 app_exception.raise_exception;
144 end if;
145 end if;
146 end loop;
147 return;
148 end LOCK_ROW;
149
150 procedure UPDATE_ROW (
151 X_TIMEZONE_CODE in VARCHAR2,
152 X_ENABLED_FLAG in VARCHAR2,
153 X_GMT_OFFSET in NUMBER,
154 X_DAYLIGHT_SAVINGS_FLAG in VARCHAR2,
155 X_ACTIVE_TIMEZONE_CODE in VARCHAR2,
156 X_UPGRADE_TZ_ID in NUMBER,
157 X_NAME in VARCHAR2,
158 X_LAST_UPDATE_DATE in DATE,
159 X_LAST_UPDATED_BY in NUMBER,
160 X_LAST_UPDATE_LOGIN in NUMBER
161 ) is
162 begin
163 update FND_TIMEZONES_B set
164 ENABLED_FLAG = X_ENABLED_FLAG,
165 GMT_OFFSET = X_GMT_OFFSET,
166 DAYLIGHT_SAVINGS_FLAG = X_DAYLIGHT_SAVINGS_FLAG,
167 ACTIVE_TIMEZONE_CODE = X_ACTIVE_TIMEZONE_CODE,
168 UPGRADE_TZ_ID = X_UPGRADE_TZ_ID,
169 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
170 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
171 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
172 where TIMEZONE_CODE = X_TIMEZONE_CODE;
173
174 if (sql%notfound) then
175 raise no_data_found;
176 end if;
177
178 update FND_TIMEZONES_TL set
179 NAME = X_NAME,
180 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
181 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
182 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
183 SOURCE_LANG = userenv('LANG')
184 where TIMEZONE_CODE = X_TIMEZONE_CODE
185 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
186
187 if (sql%notfound) then
188 raise no_data_found;
189 end if;
190 end UPDATE_ROW;
191
192 procedure DELETE_ROW (
193 X_TIMEZONE_CODE in VARCHAR2
194 ) is
195 begin
196 delete from FND_TIMEZONES_TL
197 where TIMEZONE_CODE = X_TIMEZONE_CODE;
198
199 if (sql%notfound) then
200 raise no_data_found;
201 end if;
202
203 delete from FND_TIMEZONES_B
204 where TIMEZONE_CODE = X_TIMEZONE_CODE;
205
206 if (sql%notfound) then
207 raise no_data_found;
208 end if;
209 end DELETE_ROW;
210
211 procedure ADD_LANGUAGE
212 is
213 begin
214 /* Mar/19/03 requested by Ric Ginsberg */
215 /* The following delete and update statements are commented out */
216 /* as a quick workaround to fix the time-consuming table handler issue */
217 /* Eventually we'll need to turn them into a separate fix_language procedure */
218 /*
219
220 delete from FND_TIMEZONES_TL T
221 where not exists
222 (select NULL
223 from FND_TIMEZONES_B B
224 where B.TIMEZONE_CODE = T.TIMEZONE_CODE
225 );
226
227 update FND_TIMEZONES_TL T set (
228 NAME
229 ) = (select
230 B.NAME
231 from FND_TIMEZONES_TL B
232 where B.TIMEZONE_CODE = T.TIMEZONE_CODE
233 and B.LANGUAGE = T.SOURCE_LANG)
234 where (
235 T.TIMEZONE_CODE,
236 T.LANGUAGE
237 ) in (select
238 SUBT.TIMEZONE_CODE,
239 SUBT.LANGUAGE
240 from FND_TIMEZONES_TL SUBB, FND_TIMEZONES_TL SUBT
241 where SUBB.TIMEZONE_CODE = SUBT.TIMEZONE_CODE
242 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
243 and (SUBB.NAME <> SUBT.NAME
244 ));
245 */
246
247 insert into FND_TIMEZONES_TL (
248 TIMEZONE_CODE,
249 NAME,
250 CREATION_DATE,
251 CREATED_BY,
252 LAST_UPDATE_DATE,
253 LAST_UPDATED_BY,
254 LAST_UPDATE_LOGIN,
255 LANGUAGE,
256 SOURCE_LANG
257 ) select
258 B.TIMEZONE_CODE,
259 B.NAME,
260 B.CREATION_DATE,
261 B.CREATED_BY,
262 B.LAST_UPDATE_DATE,
263 B.LAST_UPDATED_BY,
264 B.LAST_UPDATE_LOGIN,
265 L.LANGUAGE_CODE,
266 B.SOURCE_LANG
267 from FND_TIMEZONES_TL B, FND_LANGUAGES L
268 where L.INSTALLED_FLAG in ('I', 'B')
269 and B.LANGUAGE = userenv('LANG')
270 and not exists
271 (select NULL
272 from FND_TIMEZONES_TL T
273 where T.TIMEZONE_CODE = B.TIMEZONE_CODE
274 and T.LANGUAGE = L.LANGUAGE_CODE);
275 end ADD_LANGUAGE;
276
277 procedure TRANSLATE_ROW(
278 X_TIMEZONE_CODE in VARCHAR2,
279 X_NAME in VARCHAR2,
280 X_OWNER in VARCHAR2,
281 X_LAST_UPDATE_DATE in VARCHAR2,
282 X_CUSTOM_MODE in VARCHAR2
283 ) is
284 f_luby number; -- entity owner in file
285 f_ludate date; -- entity update date in file
286 db_luby number; -- entity owner in db
287 db_ludate date; -- entity update date in db
288
289 begin
290
291 -- Translate owner to file_last_updated_by
292 f_luby := fnd_load_util.owner_id(x_owner);
293
294 -- Translate char last_update_date to date
295 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
296
297 begin
298
299 select LAST_UPDATED_BY, LAST_UPDATE_DATE
300 into db_luby, db_ludate
301 from FND_TIMEZONES_TL
302 where TIMEZONE_CODE = x_timezone_code
303 and LANGUAGE = userenv('LANG');
304
305 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
306 db_ludate, X_CUSTOM_MODE)) then
307 update FND_TIMEZONES_TL set
308 NAME = X_NAME,
309 LAST_UPDATE_DATE = f_ludate,
310 LAST_UPDATED_BY = f_luby,
311 LAST_UPDATE_LOGIN = 0,
312 SOURCE_LANG = userenv('LANG')
313 where userenv('LANG') in (LANGUAGE, SOURCE_LANG)
314 and TIMEZONE_CODE = x_TIMEZONE_CODE;
315 end if;
316 exception
317 when no_data_found then
318 null;
319 end;
320 end TRANSLATE_ROW;
321
322 procedure LOAD_ROW (
323 X_TIMEZONE_CODE in VARCHAR2,
324 X_ENABLED_FLAG in VARCHAR2,
325 X_GMT_OFFSET in NUMBER,
326 X_DAYLIGHT_SAVINGS_FLAG in VARCHAR2,
327 X_ACTIVE_TIMEZONE_CODE in VARCHAR2,
328 X_UPGRADE_TZ_ID in NUMBER,
329 X_NAME in VARCHAR2,
330 X_LAST_UPDATE_DATE in VARCHAR2,
331 X_OWNER in VARCHAR2,
332 X_CUSTOM_MODE in VARCHAR2
333 ) is
334 user_id NUMBER;
335 x_rowid VARCHAR2(64);
336 f_luby number; -- entity owner in file
337 f_ludate date; -- entity update date in file
338 db_luby number; -- entity owner in db
339 db_ludate date; -- entity update date in db
340
341 begin
342
343 -- Translate owner to file_last_updated_by
344 f_luby := fnd_load_util.owner_id(x_owner);
345
346 -- Translate char last_update_date to date
347 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
348
349 begin
350
351 select LAST_UPDATED_BY, LAST_UPDATE_DATE
352 into db_luby, db_ludate
353 from FND_TIMEZONES_B
354 where TIMEZONE_CODE = X_TIMEZONE_CODE;
355
356 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
357 db_ludate, X_CUSTOM_MODE)) then
358 FND_TIMEZONES_PKG.UPDATE_ROW(
359 x_timezone_code => X_TIMEZONE_CODE,
360 x_enabled_flag => X_ENABLED_FLAG,
361 x_gmt_offset => X_GMT_OFFSET,
362 x_daylight_savings_flag => X_DAYLIGHT_SAVINGS_FLAG,
363 x_active_timezone_code => X_ACTIVE_TIMEZONE_CODE,
364 x_upgrade_tz_id => X_UPGRADE_TZ_ID,
365 x_name => X_NAME,
366 x_last_update_date => f_ludate,
367 x_last_updated_by => f_luby,
368 x_last_update_login => 0 );
369 end if;
370 exception
371 when no_data_found then
372 -- Record doesn't exist - insert in all cases
373 Fnd_Timezones_Pkg.Insert_Row(
374 x_rowid => x_rowid,
375 x_timezone_code => X_TIMEZONE_CODE,
376 x_enabled_flag => X_ENABLED_FLAG,
377 x_gmt_offset => X_GMT_OFFSET,
378 x_daylight_savings_flag => X_DAYLIGHT_SAVINGS_FLAG,
379 x_active_timezone_code => X_ACTIVE_TIMEZONE_CODE,
380 x_upgrade_tz_id => X_UPGRADE_TZ_ID,
381 x_name => X_NAME,
382 x_creation_date => f_ludate,
383 x_created_by => f_luby,
384 x_last_update_date => f_ludate,
385 x_last_updated_by => f_luby,
386 x_last_update_login => 0 );
387 end;
388
389 end LOAD_ROW;
390
391 end FND_TIMEZONES_PKG;