[Home] [Help]
PACKAGE BODY: APPS.HZ_POPULATE_TIMEZONE_PKG
Source
1 PACKAGE BODY HZ_POPULATE_TIMEZONE_PKG AS
2 /* $Header: ARHTZCPB.pls 115.4 2004/06/09 19:50:22 awu noship $ */
3
4 PROCEDURE Debug_Message(
5 p_msg_level IN NUMBER,
6 -- p_app_name IN VARCHAR2 := 'AR',
7 p_msg IN VARCHAR2)
8 IS
9 l_length NUMBER;
10 l_start NUMBER := 1;
11 l_substring VARCHAR2(50);
12 BEGIN
13 IF FND_MSG_PUB.Check_Msg_Level(p_msg_level)
14 THEN
15 /*
16 l_length := lengthb(p_msg);
17
18 -- FND_MESSAGE doesn't allow message name to be over 30 chars
19 -- chop message name if length > 30
20 WHILE l_length > 30 LOOP
21 l_substring := substrb(p_msg, l_start, 30);
22
23 FND_MESSAGE.Set_Name('AR', l_substring);
24 -- FND_MESSAGE.Set_Name(p_app_name, l_substring);
25 l_start := l_start + 30;
26 l_length := l_length - 30;
27 FND_MSG_PUB.Add;
28 END LOOP;
29
30 l_substring := substrb(p_msg, l_start);
31 FND_MESSAGE.Set_Name('AR', l_substring);
32 -- dbms_output.put_line('l_substring: ' || l_substring);
33 -- FND_MESSAGE.Set_Name(p_app_name, p_msg);
34 FND_MSG_PUB.Add;
35 */
36 l_length := lengthb(p_msg);
37
38 -- FND_MESSAGE doesn't allow application name to be over 30 chars
39 -- chop message name if length > 30
40 IF l_length > 30
41 THEN
42 l_substring := substrb(p_msg, l_start, 30);
43 FND_MESSAGE.Set_Name('AR', l_substring);
44 -- FND_MESSAGE.Set_Name(l_substring, '');
45 ELSE
46 FND_MESSAGE.Set_Name('AR', p_msg);
47 -- FND_MESSAGE.Set_Name(p_msg, '');
48 END IF;
49
50 FND_MSG_PUB.Add;
51 END IF;
52 END Debug_Message;
53
54
55 PROCEDURE write_log(p_debug_source NUMBER, p_fpt number, p_mssg varchar2) IS
56 BEGIN
57 IF p_debug_source = G_DEBUG_CONCURRENT THEN
58 -- p_fpt (1,2)?(log : output)
59 FND_FILE.put(p_fpt, p_mssg);
60 FND_FILE.NEW_LINE(p_fpt, 1);
61 -- If p_fpt == 2 and debug flag then also write to log file
62 IF p_fpt = 2 And G_Debug THEN
63 FND_FILE.put(1, p_mssg);
64 FND_FILE.NEW_LINE(1, 1);
65 END IF;
66 END IF;
67
68 IF G_Debug AND p_debug_source = G_DEBUG_TRIGGER THEN
69 -- Write debug message to message stack
70 Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
71 END IF; -- G_Debug
72
73 EXCEPTION
74 WHEN OTHERS THEN
75 NULL;
76 END Write_Log;
77
78 PROCEDURE log(
79 message IN VARCHAR2,
80 newline IN BOOLEAN DEFAULT TRUE) IS
81 BEGIN
82
83 IF message = 'NEWLINE' THEN
84 FND_FILE.NEW_LINE(FND_FILE.LOG, 1);
85 ELSIF (newline) THEN
86 FND_FILE.put_line(fnd_file.log,message);
87 ELSE
88 FND_FILE.put(fnd_file.log,message);
89 END IF;
90 END log;
91
92
93 -- private get_phone_timezone_id. No error message raised, only set timezone_id
94 -- to null if error for bug 3639702
95 Procedure Get_Phone_Timezone_ID (
96 p_api_version in number,
97 p_init_msg_list in varchar2,
98 p_phone_country_code in varchar2,
99 p_area_code in varchar2,
100 p_phone_prefix in varchar2,
101 p_country_code in varchar2,
102 x_timezone_id out nocopy number,
103 x_return_status out nocopy varchar2,
104 x_msg_count out nocopy number,
105 x_msg_data out nocopy varchar2
106 ) IS
107 l_api_name CONSTANT VARCHAR2(30) := 'Get_Phone_Timezone_ID';
108 l_api_version CONSTANT NUMBER := 1.0;
109
110 cursor get_tz_by_pcc_csr is
111 select timezone_id
112 from hz_phone_country_codes
113 where phone_country_code = p_phone_country_code;
114
115 cursor get_tz_by_pcc_cc_csr is
116 select timezone_id
117 from hz_phone_country_codes
118 where territory_code = p_country_code
119 and phone_country_code = p_phone_country_code;
120
121 cursor get_country_tz_count_csr is
122 select count(*)
123 from hz_phone_country_codes
124 where phone_country_code = p_phone_country_code;
125
126 cursor get_area_code_tzone_csr is
127 select timezone_id
128 from hz_phone_area_codes
129 where phone_country_code = p_phone_country_code
130 and area_code = p_area_code;
131
132 cursor get_area_code_count_csr is
133 select count(*)
134 from hz_phone_area_codes
135 where phone_country_code = p_phone_country_code
136 and area_code = p_area_code;
137
138 cursor get_area_code_tz_csr is
139 select timezone_id
140 from hz_phone_area_codes
141 where phone_country_code = p_phone_country_code
142 and area_code = p_area_code
143 and territory_code = p_country_code;
144
145 l_count number := 0;
146 l_tz_count number := 0;
147
148 BEGIN
149
150 --Initialize API return status to success.
151 x_return_status := FND_API.G_RET_STS_SUCCESS;
152 x_timezone_id := null;
153
154 if p_phone_country_code is null
155 then
156 x_timezone_id := null;
157 x_return_status := FND_API.G_RET_STS_ERROR;
158 return;
159 else -- phone_country_code is not null
160 if p_area_code is not null
161 then
162 if p_country_code is not null
163 then
164 open get_area_code_tz_csr;
165 fetch get_area_code_tz_csr into x_timezone_id;
166 close get_area_code_tz_csr;
167
168 else -- p_country_code is null
169 open get_area_code_count_csr;
170 fetch get_area_code_count_csr into l_tz_count;
171 close get_area_code_count_csr;
172
173 if l_tz_count >1 -- need country code to be passed in
174 then
175 x_timezone_id := null;
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 return;
178 elsif l_tz_count = 1
179 then
180 open get_area_code_tzone_csr;
181 fetch get_area_code_tzone_csr into x_timezone_id;
182 close get_area_code_tzone_csr;
183 end if;
184 end if; -- country code is not null
185 end if; -- p_area_code is not null
186
187 -- other case such as l_tz_count = 0 or area_code not passed in, then logic below
188
189 if x_timezone_id is null
190 then
191 open get_country_tz_count_csr;
192 fetch get_country_tz_count_csr into l_count;
193 close get_country_tz_count_csr;
194 if l_count = 1
195 then
196 open get_tz_by_pcc_csr;
197 fetch get_tz_by_pcc_csr into x_timezone_id;
198 close get_tz_by_pcc_csr;
199 elsif l_count > 1
200 then
201 if p_country_code is not null
202 then
203 open get_tz_by_pcc_cc_csr;
204 fetch get_tz_by_pcc_cc_csr into x_timezone_id;
205 close get_tz_by_pcc_cc_csr;
206 if x_timezone_id is null
207 then
208 x_timezone_id := null;
209 x_return_status := FND_API.G_RET_STS_ERROR;
210 return;
211 end if; -- x_timezone_id is null
212
213 else
214 x_timezone_id := null;
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 return;
217 end if;
218 elsif l_count = 0
219 then
220 x_timezone_id := null;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 return;
223 end if; -- l_count = 1
224 end if; -- if timezone_id is null
225 end if;
226
227 EXCEPTION
228
229 WHEN OTHERS THEN
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231
232 /* FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
233 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
234 FND_MSG_PUB.ADD; */
235 log('Unexpected Error: '||SQLERRM);
236
237 end get_phone_timezone_id;
238
239
240 PROCEDURE PHONE_TIMEZONE(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY
241 VARCHAR2, p_overwrite_flag IN varchar2) is
242
243 cursor phone_csr is
244 SELECT contact_point_id, phone_country_code, phone_area_code
245 FROM hz_contact_points cp
246 WHERE contact_point_type = 'PHONE';
247
248 cursor phone_tz_csr is
249 SELECT contact_point_id, phone_country_code, phone_area_code
250 FROM hz_contact_points cp
251 WHERE contact_point_type = 'PHONE'
252 and timezone_id is null;
253
254
255 TYPE PHONE_COUNTRY_CODEList IS TABLE OF HZ_CONTACT_POINTS.PHONE_COUNTRY_CODE%TYPE;
256 TYPE PHONE_AREA_CODEList IS TABLE OF HZ_CONTACT_POINTS.PHONE_AREA_CODE%TYPE;
257 TYPE CONTACT_POINT_IDList IS TABLE OF HZ_CONTACT_POINTS.CONTACT_POINT_ID%TYPE;
258
259 I_PHONE_COUNTRY_CODE PHONE_COUNTRY_CODEList;
260 I_PHONE_AREA_CODE PHONE_AREA_CODEList;
261 I_CONTACT_POINT_ID CONTACT_POINT_IDList;
262
263 i NUMBER;
264 rows NUMBER := 1000;
265 i_commit NUMBER;
266 commit_counter NUMBER;
267 l_last_fetch BOOLEAN;
268 l_timezone_id number;
269 l_return_status VARCHAR2(1);
270 l_msg_count NUMBER;
271 l_msg_data VARCHAR2(2000);
272 l_status varchar2(255);
273
274 BEGIN
275
276 log('Process began @: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
277
278 retcode:=0;
279 i_commit := 0;
280 commit_counter := 1000;
281 l_last_fetch:=false;
282
283 if p_overwrite_flag = 'Y'
284 then
285 /* for each phone we selected */
286 OPEN phone_csr;
287 LOOP
288 FETCH phone_csr BULK COLLECT INTO
289 I_CONTACT_POINT_ID, I_PHONE_COUNTRY_CODE, I_PHONE_AREA_CODE LIMIT rows;
290
291 IF phone_csr%NOTFOUND THEN
292 l_last_fetch := TRUE;
293 END IF;
294 IF I_CONTACT_POINT_ID.COUNT = 0 AND l_last_fetch THEN
295 EXIT;
296 END IF;
297
298 FOR i IN I_CONTACT_POINT_ID.FIRST..I_CONTACT_POINT_ID.LAST
299 LOOP
300 get_phone_timezone_id(
301 p_api_version => 1.0,
302 p_init_msg_list => FND_API.G_TRUE,
303 p_phone_country_code => I_PHONE_COUNTRY_CODE(i),
304 p_area_code => I_PHONE_AREA_CODE(i),
305 p_phone_prefix => null,
306 p_country_code => null,-- don't need to pass in this
307 x_timezone_id => l_timezone_id,
308 x_return_status => l_return_status ,
309 x_msg_count =>l_msg_count ,
310 x_msg_data => l_msg_data);
311 if l_return_status <> fnd_api.g_ret_sts_success
312 then -- we don't raise error
313 l_timezone_id := null;
314 end if;
315
316 UPDATE hz_contact_points
317 SET timezone_id = l_timezone_id
318 WHERE contact_point_id = I_CONTACT_POINT_ID(i);
319
320 END LOOP;
321
322 i_commit := i_commit + rows;
323 IF i_commit = commit_counter THEN
324 COMMIT;
325 i_commit := 0;
326 END IF;
327
328 IF l_last_fetch = TRUE THEN
329 EXIT;
330 END IF;
331
332 END LOOP;
333 CLOSE phone_csr;
334
335 else -- do not overwrite existing timezone_id
336 OPEN phone_tz_csr;
337 LOOP
338 FETCH phone_tz_csr BULK COLLECT INTO
339 I_CONTACT_POINT_ID, I_PHONE_COUNTRY_CODE, I_PHONE_AREA_CODE LIMIT rows;
340
341 IF phone_tz_csr%NOTFOUND THEN
342 l_last_fetch := TRUE;
343 END IF;
344 IF I_CONTACT_POINT_ID.COUNT = 0 AND l_last_fetch THEN
345 EXIT;
346 END IF;
347
348 FOR i IN I_CONTACT_POINT_ID.FIRST..I_CONTACT_POINT_ID.LAST
349 LOOP
350 get_phone_timezone_id(
351 p_api_version => 1.0,
352 p_init_msg_list => FND_API.G_TRUE,
353 p_phone_country_code => I_PHONE_COUNTRY_CODE(i),
354 p_area_code => I_PHONE_AREA_CODE(i),
355 p_phone_prefix => null,
356 p_country_code => null,-- don't need to pass in this
357 x_timezone_id => l_timezone_id,
358 x_return_status => l_return_status ,
359 x_msg_count =>l_msg_count ,
360 x_msg_data => l_msg_data);
361 if l_return_status <> fnd_api.g_ret_sts_success
362 then -- we don't raise error
363 l_timezone_id := null;
364 end if;
365
366 UPDATE hz_contact_points
367 SET timezone_id = l_timezone_id
368 WHERE contact_point_id = I_CONTACT_POINT_ID(i);
369
370 END LOOP;
371
372 i_commit := i_commit + rows;
373 IF i_commit = commit_counter THEN
374 COMMIT;
375 i_commit := 0;
376 END IF;
377
378 IF l_last_fetch = TRUE THEN
379 EXIT;
380 END IF;
381
382 END LOOP;
383 CLOSE phone_tz_csr;
384 end if;
385
386 log('Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
387
388 EXCEPTION
389 WHEN OTHERS THEN
390 ERRBUF := ERRBUF||'Error in HZ_POPULATE_TIMEZONE_PKG.PHONE_TIMEZONE:'||to_char(sqlcode)||sqlerrm;
391 RETCODE := '2';
392 log('Error in HZ_POPULATE_TIMEZONE_PKG.PHONE_TIMEZONE:'||sqlerrm);
393
394 end PHONE_TIMEZONE;
395
396 PROCEDURE LOCATION_TIMEZONE(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY
397 VARCHAR2, p_overwrite_flag IN varchar2) is
398 cursor location_csr is
399 select location_id, country, state, city, postal_code
400 from hz_locations;
401
402 cursor location_tz_csr is
403 select location_id, country, state, city, postal_code
404 from hz_locations
405 where timezone_id is null;
406
407 TYPE COUNTRYList IS TABLE OF HZ_LOCATIONS.COUNTRY%TYPE;
408 TYPE CITYList IS TABLE OF HZ_LOCATIONS.CITY%TYPE;
409 TYPE POSTAL_CODEList IS TABLE OF HZ_LOCATIONS.POSTAL_CODE%TYPE;
410 TYPE STATEList IS TABLE OF HZ_LOCATIONS.STATE%TYPE;
411 TYPE LOCATION_IDList IS TABLE OF HZ_LOCATIONS.LOCATION_ID%TYPE;
412
413 I_COUNTRY COUNTRYList;
414 I_CITY CITYList;
415 I_POSTAL_CODE POSTAL_CODEList;
416 I_STATE STATEList;
417 I_LOCATION_ID LOCATION_IDList;
418
419 i NUMBER;
420 rows NUMBER := 1000;
421 i_commit NUMBER;
422 commit_counter NUMBER;
423 l_last_fetch BOOLEAN;
424 l_timezone_id number;
425 l_return_status VARCHAR2(1);
426 l_msg_count NUMBER;
427 l_msg_data VARCHAR2(2000);
428 l_status varchar2(255);
429 begin
430
431 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process began @: ' || to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
432
433 retcode:=0;
434 i_commit := 0;
435 commit_counter := 1000;
436 l_last_fetch:=false;
437
438 if p_overwrite_flag = 'Y'
439 then
440 /* for each location we selected */
441 OPEN location_csr;
442 LOOP
443 FETCH location_csr BULK COLLECT INTO
444 I_LOCATION_ID, I_COUNTRY, I_STATE, I_CITY, I_POSTAL_CODE LIMIT rows;
445
446 IF location_csr%NOTFOUND THEN
447 l_last_fetch := TRUE;
448 END IF;
449 IF I_LOCATION_ID.COUNT = 0 AND l_last_fetch THEN
450 EXIT;
451 END IF;
452
453 FOR i IN I_LOCATION_ID.FIRST..I_LOCATION_ID.LAST
454 LOOP
455 hz_timezone_pub.get_timezone_id(
456 p_api_version => 1.0,
457 p_init_msg_list => FND_API.G_TRUE,
458 p_postal_code => I_POSTAL_CODE(i),
459 p_city => I_CITY(i),
460 p_state => I_STATE(i),
461 p_country => I_COUNTRY(i),
462 x_timezone_id => l_timezone_id,
463 x_return_status => l_return_status ,
464 x_msg_count =>l_msg_count ,
465 x_msg_data => l_msg_data);
466 if l_return_status <> fnd_api.g_ret_sts_success
467 then -- we don't raise error
468 l_timezone_id := null;
469 end if;
470
471 UPDATE hz_locations
472 SET timezone_id = l_timezone_id
473 WHERE location_id = I_LOCATION_ID(i);
474
475 END LOOP;
476
477 i_commit := i_commit + rows;
478 IF i_commit = commit_counter THEN
479 COMMIT;
480 i_commit := 0;
481 END IF;
482
483 IF l_last_fetch = TRUE THEN
484 EXIT;
485 END IF;
486
487 END LOOP;
488 CLOSE location_csr;
489
490 else -- overwrite existing timezone
491 OPEN location_tz_csr;
492 LOOP
493 FETCH location_tz_csr BULK COLLECT INTO
494 I_LOCATION_ID, I_COUNTRY, I_STATE, I_CITY, I_POSTAL_CODE LIMIT rows;
495
496 IF location_tz_csr%NOTFOUND THEN
497 l_last_fetch := TRUE;
498 END IF;
499 IF I_LOCATION_ID.COUNT = 0 AND l_last_fetch THEN
500 EXIT;
501 END IF;
502
503 FOR i IN I_LOCATION_ID.FIRST..I_LOCATION_ID.LAST
504 LOOP
505 hz_timezone_pub.get_timezone_id(
506 p_api_version => 1.0,
507 p_init_msg_list => FND_API.G_TRUE,
508 p_postal_code => I_POSTAL_CODE(i),
509 p_city => I_CITY(i),
510 p_state => I_STATE(i),
511 p_country => I_COUNTRY(i),
512 x_timezone_id => l_timezone_id,
513 x_return_status => l_return_status ,
514 x_msg_count =>l_msg_count ,
515 x_msg_data => l_msg_data);
516 if l_return_status <> fnd_api.g_ret_sts_success
517 then -- we don't raise error
518 l_timezone_id := null;
519 end if;
520
521 UPDATE hz_locations
522 SET timezone_id = l_timezone_id
523 WHERE location_id = I_LOCATION_ID(i);
524
525 END LOOP;
526
527 i_commit := i_commit + rows;
528 IF i_commit = commit_counter THEN
529 COMMIT;
530 i_commit := 0;
531 END IF;
532
533 IF l_last_fetch = TRUE THEN
534 EXIT;
535 END IF;
536
537 END LOOP;
538 CLOSE location_tz_csr;
539 end if;
540 Write_Log(G_DEBUG_CONCURRENT, 1, 'Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
541
542 EXCEPTION
543 WHEN OTHERS THEN
544 ERRBUF := ERRBUF||'Error in HZ_POPULATE_TIMEZONE_PKG.PHONE_TIMEZONE:'||to_char(sqlcode)||sqlerrm;
545 RETCODE := '2';
546 Write_Log(G_DEBUG_CONCURRENT, 1,'Error in HZ_POPULATE_TIMEZONE_PKG.PHONE_TIMEZONE:');
547 Write_Log(G_DEBUG_CONCURRENT, 1,sqlerrm);
548 --l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
549 --IF l_status = TRUE THEN
550 -- Write_Log(G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
551 --END IF;
552
553 end location_timezone;
554
555 END HZ_POPULATE_TIMEZONE_PKG;