[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_TYPES_PKG
Source
1 PACKAGE BODY HZ_GEOGRAPHY_TYPES_PKG AS
2 /*$Header: ARHGTPTB.pls 120.6.12000000.2 2007/05/07 20:45:47 nsinghai ship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_geography_type IN VARCHAR2,
7 x_geography_type_name IN VARCHAR2,
8 x_object_version_number IN NUMBER,
9 x_geography_use IN VARCHAR2,
10 x_postal_code_range_flag IN VARCHAR2,
11 x_limited_by_geography_id IN NUMBER,
12 x_created_by_module IN VARCHAR2,
13 x_application_id IN NUMBER,
14 x_program_login_id IN NUMBER
15 ) IS
16
17
18 BEGIN
19
20 INSERT INTO HZ_GEOGRAPHY_TYPES_B (
21 geography_type,
22 object_version_number,
23 geography_use,
24 postal_code_range_flag,
25 limited_by_geography_id,
26 created_by_module,
27 last_updated_by,
28 creation_date,
29 created_by,
30 last_update_date,
31 last_update_login,
32 application_id,
33 program_id,
34 program_login_id,
35 program_application_id,
36 request_id
37 )
38 VALUES (
39 DECODE(x_geography_type,
40 FND_API.G_MISS_CHAR, NULL,
41 x_geography_type),
42 DECODE(x_object_version_number,
43 FND_API.G_MISS_NUM, NULL,
44 x_object_version_number),
45 DECODE(x_geography_use,
46 FND_API.G_MISS_CHAR, NULL,
47 x_geography_use),
48 DECODE(x_postal_code_range_flag,
49 FND_API.G_MISS_CHAR, NULL,
50 x_postal_code_range_flag),
51 DECODE(x_limited_by_geography_id,
52 FND_API.G_MISS_NUM, NULL,
53 x_limited_by_geography_id),
54 DECODE(x_created_by_module,
55 FND_API.G_MISS_CHAR, NULL,
56 x_created_by_module),
57 hz_utility_v2pub.last_updated_by,
58 hz_utility_v2pub.creation_date,
59 hz_utility_v2pub.created_by,
60 hz_utility_v2pub.last_update_date,
61 hz_utility_v2pub.last_update_login,
62 DECODE(x_application_id,
63 FND_API.G_MISS_NUM, NULL,
64 x_application_id),
65 hz_utility_v2pub.program_id,
66 DECODE(x_program_login_id,
67 FND_API.G_MISS_NUM, NULL,
68 x_program_login_id),
69 hz_utility_v2pub.program_application_id,
70 hz_utility_v2pub.request_id
71 ) RETURNING
72 rowid
73 INTO
74 x_rowid;
75
76 insert into HZ_GEOGRAPHY_TYPES_TL (
77 GEOGRAPHY_TYPE,
78 GEOGRAPHY_TYPE_NAME,
79 CREATED_BY_MODULE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATE_LOGIN,
85 APPLICATION_ID,
86 PROGRAM_ID,
87 PROGRAM_LOGIN_ID,
88 PROGRAM_APPLICATION_ID,
89 REQUEST_ID,
90 LANGUAGE,
91 SOURCE_LANG
92 ) select
93 DECODE(x_geography_type,
94 FND_API.G_MISS_CHAR, NULL,
95 x_geography_type),
96 DECODE(x_geography_type_name,
97 FND_API.G_MISS_CHAR, NULL,
98 x_geography_type_name),
99 DECODE(x_created_by_module,
100 FND_API.G_MISS_CHAR, NULL,
101 x_created_by_module),
102 hz_utility_v2pub.last_updated_by,
103 hz_utility_v2pub.creation_date,
104 hz_utility_v2pub.created_by,
105 hz_utility_v2pub.last_update_date,
106 hz_utility_v2pub.last_update_login,
107 DECODE(x_application_id,
108 FND_API.G_MISS_NUM, NULL,
109 x_application_id),
110 hz_utility_v2pub.program_id,
111 DECODE(x_program_login_id,
112 FND_API.G_MISS_NUM, NULL,
113 x_program_login_id),
114 hz_utility_v2pub.program_application_id,
115 hz_utility_v2pub.request_id,
116 L.LANGUAGE_CODE,
117 userenv('LANG')
118 from FND_LANGUAGES L
119 where L.INSTALLED_FLAG in ('I', 'B')
120 and not exists
121 (select NULL
122 from HZ_GEOGRAPHY_TYPES_TL T
123 where T.GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE
124 and T.LANGUAGE = L.LANGUAGE_CODE);
125
126 END Insert_Row;
127
128 PROCEDURE Update_Row (
129 x_rowid IN OUT NOCOPY VARCHAR2,
130 x_geography_type IN VARCHAR2,
131 x_geography_type_name IN VARCHAR2,
132 x_object_version_number IN NUMBER,
133 x_geography_use IN VARCHAR2,
134 x_postal_code_range_flag IN VARCHAR2,
135 x_limited_by_geography_id IN NUMBER,
136 x_created_by_module IN VARCHAR2,
137 x_application_id IN NUMBER,
138 x_program_login_id IN NUMBER
139 ) IS
140 BEGIN
141
142 --dbms_output.put_line.PUT_LINE('in tblhandler geography_type_name is '||x_geography_type_name);
143
144 UPDATE HZ_GEOGRAPHY_TYPES_B
145 SET
146 geography_type =
147 DECODE(x_geography_type,
148 NULL, geography_type,
149 FND_API.G_MISS_CHAR, NULL,
150 x_geography_type),
151 object_version_number =
152 DECODE(x_object_version_number,
153 NULL, object_version_number,
154 FND_API.G_MISS_NUM, NULL,
155 x_object_version_number),
156 geography_use =
157 DECODE(x_geography_use,
158 NULL, geography_use,
159 FND_API.G_MISS_CHAR, NULL,
160 x_geography_use),
161 postal_code_range_flag =
162 DECODE(x_postal_code_range_flag,
163 NULL, postal_code_range_flag,
164 FND_API.G_MISS_CHAR, NULL,
165 x_postal_code_range_flag),
166 limited_by_geography_id =
167 DECODE(x_limited_by_geography_id,
168 NULL, limited_by_geography_id,
169 FND_API.G_MISS_NUM, NULL,
170 x_limited_by_geography_id),
171 created_by_module =
172 DECODE(x_created_by_module,
173 NULL, created_by_module,
174 FND_API.G_MISS_CHAR, NULL,
175 x_created_by_module),
176 last_updated_by = hz_utility_v2pub.last_updated_by,
177 creation_date = creation_date,
178 created_by = created_by,
179 last_update_date = hz_utility_v2pub.last_update_date,
180 last_update_login = hz_utility_v2pub.last_update_login,
181 application_id =
182 DECODE(x_application_id,
183 NULL, application_id,
184 FND_API.G_MISS_NUM, NULL,
185 x_application_id),
186 program_id = hz_utility_v2pub.program_id,
187 program_login_id =
188 DECODE(x_program_login_id,
189 NULL, program_login_id,
190 FND_API.G_MISS_NUM, NULL,
191 x_program_login_id),
192 program_application_id = hz_utility_v2pub.program_application_id,
193 request_id = hz_utility_v2pub.request_id
194 WHERE rowid = x_rowid;
195
196 IF ( SQL%NOTFOUND ) THEN
197 RAISE NO_DATA_FOUND;
198 END IF;
199
200 update HZ_GEOGRAPHY_TYPES_TL set
201 GEOGRAPHY_TYPE_NAME = decode(X_GEOGRAPHY_TYPE_NAME,
202 NULL, GEOGRAPHY_TYPE_NAME,
203 FND_API.G_MISS_NUM, NULL,
204 X_GEOGRAPHY_TYPE_NAME),
205 LAST_UPDATE_DATE = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATE_DATE,
206 FND_API.G_MISS_NUM, NULL,
207 hz_utility_v2pub.last_update_date),
208 LAST_UPDATED_BY = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATED_BY,
209 FND_API.G_MISS_NUM, NULL,
210 hz_utility_v2pub.LAST_UPDATED_BY),
211 LAST_UPDATE_LOGIN = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATE_LOGIN,
212 FND_API.G_MISS_NUM, NULL,
213 hz_utility_v2pub.LAST_UPDATE_LOGIN),
214 SOURCE_LANG = userenv('LANG')
215 where GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE
216 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
217
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221
222 END Update_Row;
223
224 PROCEDURE Lock_Row (
225 x_rowid IN OUT NOCOPY VARCHAR2,
226 x_geography_type IN VARCHAR2,
227 x_geography_type_name IN VARCHAR2,
228 x_object_version_number IN NUMBER,
229 x_geography_use IN VARCHAR2,
230 x_postal_code_range_flag IN VARCHAR2,
231 x_limited_by_geography_id IN NUMBER,
232 x_created_by_module IN VARCHAR2,
233 x_last_updated_by IN NUMBER,
234 x_creation_date IN DATE,
235 x_created_by IN NUMBER,
236 x_last_update_date IN DATE,
237 x_last_update_login IN NUMBER,
238 x_application_id IN NUMBER,
239 x_program_id IN NUMBER,
240 x_program_login_id IN NUMBER,
241 x_program_application_id IN NUMBER,
242 x_request_id IN NUMBER
243 ) IS
244
245 CURSOR c IS
246 SELECT * FROM hz_geography_types_b
247 WHERE rowid = x_rowid
248 FOR UPDATE NOWAIT;
249 Recinfo c%ROWTYPE;
250
251 cursor c1 is select
252 GEOGRAPHY_TYPE_NAME,
253 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
254 from HZ_GEOGRAPHY_TYPES_TL
255 where GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE
256 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
257 for update of GEOGRAPHY_TYPE nowait;
258 BEGIN
259
260 OPEN c;
261 FETCH c INTO Recinfo;
262 IF ( c%NOTFOUND ) THEN
263 CLOSE c;
264 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
265 APP_EXCEPTION.RAISE_EXCEPTION;
266 END IF;
267 CLOSE C;
268
269 IF (
270 ( ( Recinfo.geography_type = x_geography_type )
271 OR ( ( Recinfo.geography_type IS NULL )
272 AND ( x_geography_type IS NULL ) ) )
273 AND ( ( Recinfo.object_version_number = x_object_version_number )
274 OR ( ( Recinfo.object_version_number IS NULL )
275 AND ( x_object_version_number IS NULL ) ) )
276 AND ( ( Recinfo.geography_use = x_geography_use )
277 OR ( ( Recinfo.geography_use IS NULL )
278 AND ( x_geography_use IS NULL ) ) )
279 AND ( ( Recinfo.postal_code_range_flag = x_postal_code_range_flag )
280 OR ( ( Recinfo.postal_code_range_flag IS NULL )
281 AND ( x_postal_code_range_flag IS NULL ) ) )
282 AND ( ( Recinfo.limited_by_geography_id = x_limited_by_geography_id )
283 OR ( ( Recinfo.limited_by_geography_id IS NULL )
284 AND ( x_limited_by_geography_id IS NULL ) ) )
285 AND ( ( Recinfo.created_by_module = x_created_by_module )
286 OR ( ( Recinfo.created_by_module IS NULL )
287 AND ( x_created_by_module IS NULL ) ) )
288 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
289 OR ( ( Recinfo.last_updated_by IS NULL )
290 AND ( x_last_updated_by IS NULL ) ) )
291 AND ( ( Recinfo.creation_date = x_creation_date )
292 OR ( ( Recinfo.creation_date IS NULL )
293 AND ( x_creation_date IS NULL ) ) )
294 AND ( ( Recinfo.created_by = x_created_by )
295 OR ( ( Recinfo.created_by IS NULL )
296 AND ( x_created_by IS NULL ) ) )
297 AND ( ( Recinfo.last_update_date = x_last_update_date )
298 OR ( ( Recinfo.last_update_date IS NULL )
299 AND ( x_last_update_date IS NULL ) ) )
300 AND ( ( Recinfo.last_update_login = x_last_update_login )
301 OR ( ( Recinfo.last_update_login IS NULL )
302 AND ( x_last_update_login IS NULL ) ) )
303 AND ( ( Recinfo.application_id = x_application_id )
304 OR ( ( Recinfo.application_id IS NULL )
305 AND ( x_application_id IS NULL ) ) )
306 AND ( ( Recinfo.program_id = x_program_id )
307 OR ( ( Recinfo.program_id IS NULL )
308 AND ( x_program_id IS NULL ) ) )
309 AND ( ( Recinfo.program_login_id = x_program_login_id )
310 OR ( ( Recinfo.program_login_id IS NULL )
311 AND ( x_program_login_id IS NULL ) ) )
312 AND ( ( Recinfo.program_application_id = x_program_application_id )
313 OR ( ( Recinfo.program_application_id IS NULL )
314 AND ( x_program_application_id IS NULL ) ) )
315 AND ( ( Recinfo.request_id = x_request_id )
316 OR ( ( Recinfo.request_id IS NULL )
317 AND ( x_request_id IS NULL ) ) )
318 ) THEN
319 RETURN;
320 ELSE
321 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_CHANGED');
322 APP_EXCEPTION.RAISE_EXCEPTION;
323 END IF;
324
325 for tlinfo in c1 loop
326 if (tlinfo.BASELANG = 'Y') then
327 if ( ((tlinfo.GEOGRAPHY_TYPE_NAME = X_GEOGRAPHY_TYPE_NAME)
328 OR ((tlinfo.GEOGRAPHY_TYPE_NAME is null) AND (X_GEOGRAPHY_TYPE_NAME is null)))
329 ) then
330 null;
331 else
332 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
333 app_exception.raise_exception;
334 end if;
335 end if;
336 end loop;
337 return;
338 END Lock_Row;
339
340 PROCEDURE Select_Row (
341 x_geography_type IN OUT NOCOPY VARCHAR2,
342 x_object_version_number OUT NOCOPY NUMBER,
343 x_geography_use OUT NOCOPY VARCHAR2,
344 x_postal_code_range_flag OUT NOCOPY VARCHAR2,
345 x_limited_by_geography_id OUT NOCOPY NUMBER,
346 x_created_by_module OUT NOCOPY VARCHAR2,
347 x_application_id OUT NOCOPY NUMBER,
348 x_program_login_id OUT NOCOPY NUMBER
349 ) IS
350 BEGIN
351
352 SELECT
353 NVL(geography_type, FND_API.G_MISS_CHAR),
354 NVL(geography_use, FND_API.G_MISS_CHAR),
355 NVL(postal_code_range_flag, FND_API.G_MISS_CHAR),
356 NVL(limited_by_geography_id, FND_API.G_MISS_NUM),
357 NVL(created_by_module, FND_API.G_MISS_CHAR),
358 NVL(application_id, FND_API.G_MISS_NUM),
359 NVL(program_login_id, FND_API.G_MISS_NUM)
360 INTO
361 x_geography_type,
362 x_geography_use,
363 x_postal_code_range_flag,
364 x_limited_by_geography_id,
365 x_created_by_module,
366 x_application_id,
367 x_program_login_id
368 FROM HZ_GEOGRAPHY_TYPES_B
369 WHERE geography_type = x_geography_type;
370
371 EXCEPTION
372 WHEN NO_DATA_FOUND THEN
373 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
374 FND_MESSAGE.SET_TOKEN('RECORD', 'geography');
375 FND_MESSAGE.SET_TOKEN('VALUE', x_geography_type);
376 FND_MSG_PUB.ADD;
377 RAISE FND_API.G_EXC_ERROR;
378
379 END Select_Row;
380
381 PROCEDURE Delete_Row (
382 x_geography_type IN VARCHAR2
383 ) IS
384 BEGIN
385
386 delete from HZ_GEOGRAPHY_TYPES_TL
387 where GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE;
388
389 if (sql%notfound) then
390 raise no_data_found;
391 end if;
392
393 delete from HZ_GEOGRAPHY_TYPES_B
394 where GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE;
395
399 END Delete_Row;
396 if (sql%notfound) then
397 raise no_data_found;
398 end if;
400
401 procedure ADD_LANGUAGE
402 is
403 begin
404 delete from HZ_GEOGRAPHY_TYPES_TL T
405 where not exists
406 (select NULL
407 from HZ_GEOGRAPHY_TYPES_B B
408 where B.GEOGRAPHY_TYPE = T.GEOGRAPHY_TYPE
409 );
410
411 update HZ_GEOGRAPHY_TYPES_TL T set (
412 GEOGRAPHY_TYPE_NAME
413 ) = (select
414 B.GEOGRAPHY_TYPE_NAME
415 from HZ_GEOGRAPHY_TYPES_TL B
416 where B.GEOGRAPHY_TYPE = T.GEOGRAPHY_TYPE
417 and B.LANGUAGE = T.SOURCE_LANG)
418 where (
419 T.GEOGRAPHY_TYPE,
420 T.LANGUAGE
421 ) in (select
422 SUBT.GEOGRAPHY_TYPE,
423 SUBT.LANGUAGE
424 from HZ_GEOGRAPHY_TYPES_TL SUBB, HZ_GEOGRAPHY_TYPES_TL SUBT
425 where SUBB.GEOGRAPHY_TYPE = SUBT.GEOGRAPHY_TYPE
426 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
427 and (SUBB.GEOGRAPHY_TYPE_NAME <> SUBT.GEOGRAPHY_TYPE_NAME
428 or (SUBB.GEOGRAPHY_TYPE_NAME is null and SUBT.GEOGRAPHY_TYPE_NAME is not null)
429 or (SUBB.GEOGRAPHY_TYPE_NAME is not null and SUBT.GEOGRAPHY_TYPE_NAME is null)
430 ));
431
432 insert into HZ_GEOGRAPHY_TYPES_TL (
433 GEOGRAPHY_TYPE,
434 GEOGRAPHY_TYPE_NAME,
435 LAST_UPDATED_BY,
436 CREATION_DATE,
437 CREATED_BY,
438 LAST_UPDATE_DATE,
439 LAST_UPDATE_LOGIN,
440 LANGUAGE,
441 SOURCE_LANG,
442 CREATED_BY_MODULE
443 ) select
444 B.GEOGRAPHY_TYPE,
445 B.GEOGRAPHY_TYPE_NAME,
446 B.LAST_UPDATED_BY,
447 B.CREATION_DATE,
448 B.CREATED_BY,
449 B.LAST_UPDATE_DATE,
450 B.LAST_UPDATE_LOGIN,
451 L.LANGUAGE_CODE,
452 B.SOURCE_LANG,
453 B.CREATED_BY_MODULE
454 from HZ_GEOGRAPHY_TYPES_TL B, FND_LANGUAGES L
455 where L.INSTALLED_FLAG in ('I', 'B')
456 and B.LANGUAGE = userenv('LANG')
457 and not exists
458 (select NULL
459 from HZ_GEOGRAPHY_TYPES_TL T
460 where T.GEOGRAPHY_TYPE = B.GEOGRAPHY_TYPE
461 and T.LANGUAGE = L.LANGUAGE_CODE);
462 end ADD_LANGUAGE;
463
464 PROCEDURE translate_row (
465 x_geography_type IN VARCHAR2,
466 x_geography_type_name IN VARCHAR2,
467 x_owner IN VARCHAR2) IS
468
469 BEGIN
470 UPDATE HZ_GEOGRAPHY_TYPES_TL
471 SET geography_type_name = x_geography_type_name,
472 source_lang = userenv('LANG'),
473 last_update_date = sysdate,
474 last_updated_by = DECODE(x_owner, 'SEED', 1, 0),
475 last_update_login = 0
476 WHERE geography_type = x_geography_type
477 AND userenv('LANG') IN (language, source_lang);
478
479 END translate_row;
480
481 PROCEDURE LOAD_ROW (
482 x_geography_type IN VARCHAR2,
483 x_geography_type_name IN VARCHAR2,
484 x_object_version_number IN NUMBER,
485 x_geography_use IN VARCHAR2,
486 x_postal_code_range_flag IN VARCHAR2,
487 x_limited_by_geography_id IN NUMBER,
488 x_created_by_module IN VARCHAR2,
489 x_application_id IN NUMBER,
490 x_program_login_id IN NUMBER,
491 X_OWNER in VARCHAR2
492 ) IS
493
494 l_user_id NUMBER;
495 l_row_id ROWID; --varchar2(64);
496
497 BEGIN
498
499 l_user_id := NVL(fnd_load_util.owner_id(X_OWNER),FND_GLOBAL.USER_ID);
500
501 BEGIN
502
503 -- check for existance of data
504 SELECT rowid
505 INTO l_row_id
506 FROM hz_geography_types_b
507 WHERE geography_type = x_geography_type;
508
509 -- data exists in hz_geography_types_b table. Now Update it.
510 -- cannot use update_row package directly because it has last_updated_by as
511 -- hz_utility_v2pub.last_updated_by which cannot be used for seed data loading
512 UPDATE HZ_GEOGRAPHY_TYPES_B
513 SET
514 object_version_number =
515 DECODE(x_object_version_number,
516 NULL, object_version_number,
517 x_object_version_number),
518 geography_use =
519 DECODE(x_geography_use,
520 NULL, geography_use,
521 x_geography_use),
522 postal_code_range_flag =
523 DECODE(x_postal_code_range_flag,
524 NULL, postal_code_range_flag,
525 x_postal_code_range_flag),
526 limited_by_geography_id =
527 DECODE(x_limited_by_geography_id,
528 NULL, limited_by_geography_id,
529 FND_API.G_MISS_NUM, NULL,
530 x_limited_by_geography_id),
531 last_updated_by = l_user_id,
532 last_update_date = hz_utility_v2pub.last_update_date,
533 last_update_login = hz_utility_v2pub.last_update_login,
534 application_id =
535 DECODE(x_application_id,
536 NULL, application_id,
537 FND_API.G_MISS_NUM, NULL,
538 x_application_id),
539 program_login_id =
540 DECODE(x_program_login_id,
544 WHERE rowid = l_row_id;
541 NULL, program_login_id,
542 FND_API.G_MISS_NUM, NULL,
543 x_program_login_id)
545
546 -- so far hz_geography_types_b table has data. Now update tl table
547
548 UPDATE HZ_GEOGRAPHY_TYPES_TL SET
549 GEOGRAPHY_TYPE_NAME = decode(X_GEOGRAPHY_TYPE_NAME,
550 NULL, GEOGRAPHY_TYPE_NAME,
551 FND_API.G_MISS_NUM, NULL,
552 X_GEOGRAPHY_TYPE_NAME),
553 LAST_UPDATE_DATE = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATE_DATE,
554 hz_utility_v2pub.last_update_date),
555 LAST_UPDATED_BY = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATED_BY,
556 l_user_id),
557 LAST_UPDATE_LOGIN = decode(X_GEOGRAPHY_TYPE_NAME, NULL, LAST_UPDATE_LOGIN,
558 FND_API.G_MISS_NUM, NULL,
559 l_user_id),
560 SOURCE_LANG = USERENV('LANG')
561 WHERE GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE
562 AND USERENV('LANG') in (LANGUAGE, SOURCE_LANG);
563
564 IF (SQL%NOTFOUND) THEN
565 -- data exist in hz_geography_types_b table but not in hz_geography_types_tl
566 RAISE no_data_found;
567 END IF;
568
569 EXCEPTION WHEN NO_DATA_FOUND THEN -- insert data
570 BEGIN
571 -- check if we need to insert data in hz_geography_types_b
572 -- if l_row_id is NULL and no_data_found is raised, it means we have
573 -- to insert in hz_geography_types_b table.
574 IF (l_row_id IS NULL) THEN
575 -- We cannot use insert_row procedure because it puts user_ids of logged in user
576 INSERT INTO HZ_GEOGRAPHY_TYPES_B (
577 geography_type,
578 object_version_number,
579 geography_use,
580 postal_code_range_flag,
581 limited_by_geography_id,
582 created_by_module,
583 last_updated_by,
584 creation_date,
585 created_by,
586 last_update_date,
587 last_update_login,
588 application_id,
589 program_id,
590 program_login_id,
591 program_application_id,
592 request_id
593 )
594 VALUES (x_geography_type,
595 1,
596 x_geography_use,
597 x_postal_code_range_flag,
598 DECODE(x_limited_by_geography_id,
599 FND_API.G_MISS_NUM, NULL,
600 x_limited_by_geography_id),
601 x_created_by_module,
602 l_user_id,
603 hz_utility_v2pub.creation_date,
604 l_user_id,
605 hz_utility_v2pub.last_update_date,
606 hz_utility_v2pub.last_update_login,
607 DECODE(x_application_id,
608 FND_API.G_MISS_NUM, NULL,
609 x_application_id),
610 hz_utility_v2pub.program_id,
611 DECODE(x_program_login_id,
612 FND_API.G_MISS_NUM, NULL,
613 x_program_login_id),
614 hz_utility_v2pub.program_application_id,
615 hz_utility_v2pub.request_id
616 ) ;
617 END IF;
618
619 INSERT INTO HZ_GEOGRAPHY_TYPES_TL (
620 GEOGRAPHY_TYPE,
621 GEOGRAPHY_TYPE_NAME,
622 CREATED_BY_MODULE,
623 LAST_UPDATED_BY,
624 CREATION_DATE,
625 CREATED_BY,
626 LAST_UPDATE_DATE,
627 LAST_UPDATE_LOGIN,
628 APPLICATION_ID,
629 PROGRAM_ID,
630 PROGRAM_LOGIN_ID,
631 PROGRAM_APPLICATION_ID,
632 REQUEST_ID,
633 LANGUAGE,
634 SOURCE_LANG
635 ) SELECT
636 x_geography_type,
637 DECODE(x_geography_type_name,
638 FND_API.G_MISS_CHAR, NULL,
639 x_geography_type_name),
640 x_created_by_module,
641 l_user_id,
642 hz_utility_v2pub.creation_date,
643 l_user_id,
644 hz_utility_v2pub.last_update_date,
645 hz_utility_v2pub.last_update_login,
646 DECODE(x_application_id,
647 FND_API.G_MISS_NUM, NULL,
648 x_application_id),
649 hz_utility_v2pub.program_id,
650 DECODE(x_program_login_id,
651 FND_API.G_MISS_NUM, NULL,
652 x_program_login_id),
653 hz_utility_v2pub.program_application_id,
654 hz_utility_v2pub.request_id,
655 L.LANGUAGE_CODE,
656 USERENV('LANG')
657 FROM FND_LANGUAGES L
658 WHERE L.INSTALLED_FLAG in ('I', 'B')
659 AND NOT EXISTS (SELECT NULL
660 FROM HZ_GEOGRAPHY_TYPES_TL T
661 WHERE T.GEOGRAPHY_TYPE = X_GEOGRAPHY_TYPE
662 AND T.LANGUAGE = L.LANGUAGE_CODE);
663
664 EXCEPTION WHEN DUP_VAL_ON_INDEX THEN
665 NULL;
666 END;
667
668 END; -- for main table
669
670 END LOAD_ROW;
671
672 END HZ_GEOGRAPHY_TYPES_PKG;