[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHIES_PKG
Source
1 PACKAGE BODY HZ_GEOGRAPHIES_PKG AS
2 /*$Header: ARHGEOTB.pls 120.1 2005/10/25 22:33:22 nsinghai noship $ */
3
4 PROCEDURE Insert_Row (
5 x_rowid IN OUT NOCOPY VARCHAR2,
6 x_geography_id IN OUT NOCOPY NUMBER,
7 x_object_version_number IN NUMBER,
8 x_geography_type IN VARCHAR2,
9 x_geography_name IN VARCHAR2,
10 x_geography_use IN VARCHAR2,
11 x_geography_code IN VARCHAR2,
12 x_start_date IN DATE,
13 x_end_date IN DATE,
14 x_multiple_parent_flag IN VARCHAR2,
15 x_created_by_module IN VARCHAR2,
16 x_country_code IN VARCHAR2,
17 x_geography_element1 IN VARCHAR2,
18 x_geography_element1_id IN NUMBER,
19 x_geography_element1_code IN VARCHAR2,
20 x_geography_element2 IN VARCHAR2,
21 x_geography_element2_id IN NUMBER,
22 x_geography_element2_code IN VARCHAR2,
23 x_geography_element3 IN VARCHAR2,
24 x_geography_element3_id IN NUMBER,
25 x_geography_element3_code IN VARCHAR2,
26 x_geography_element4 IN VARCHAR2,
27 x_geography_element4_id IN NUMBER,
28 x_geography_element4_code IN VARCHAR2,
29 x_geography_element5 IN VARCHAR2,
30 x_geography_element5_id IN NUMBER,
31 x_geography_element5_code IN VARCHAR2,
32 x_geography_element6 IN VARCHAR2,
33 x_geography_element6_id IN NUMBER,
34 x_geography_element7 IN VARCHAR2,
35 x_geography_element7_id IN NUMBER,
36 x_geography_element8 IN VARCHAR2,
37 x_geography_element8_id IN NUMBER,
38 x_geography_element9 IN VARCHAR2,
39 x_geography_element9_id IN NUMBER,
40 x_geography_element10 IN VARCHAR2,
41 x_geography_element10_id IN NUMBER,
42 x_geometry IN MDSYS.SDO_GEOMETRY,
43 x_timezone_code IN VARCHAR2,
44 x_application_id IN NUMBER,
45 x_program_login_id IN NUMBER,
46 x_attribute_category IN VARCHAR2,
47 x_attribute1 IN VARCHAR2,
48 x_attribute2 IN VARCHAR2,
49 x_attribute3 IN VARCHAR2,
50 x_attribute4 IN VARCHAR2,
51 x_attribute5 IN VARCHAR2,
52 x_attribute6 IN VARCHAR2,
53 x_attribute7 IN VARCHAR2,
54 x_attribute8 IN VARCHAR2,
55 x_attribute9 IN VARCHAR2,
56 x_attribute10 IN VARCHAR2,
57 x_attribute11 IN VARCHAR2,
58 x_attribute12 IN VARCHAR2,
59 x_attribute13 IN VARCHAR2,
60 x_attribute14 IN VARCHAR2,
61 x_attribute15 IN VARCHAR2,
62 x_attribute16 IN VARCHAR2,
63 x_attribute17 IN VARCHAR2,
64 x_attribute18 IN VARCHAR2,
65 x_attribute19 IN VARCHAR2,
66 x_attribute20 IN VARCHAR2
67 ) IS
68
69 l_success VARCHAR2(1) := 'N';
70
71 BEGIN
72
73 --dbms_output.put_line.PUT_LINE('Before Insert');
74 WHILE l_success = 'N' LOOP
75 BEGIN
76 INSERT INTO HZ_GEOGRAPHIES (
77 geography_id,
78 object_version_number,
79 geography_type,
80 geography_name,
81 geography_use,
82 geography_code,
83 start_date,
84 end_date,
85 multiple_parent_flag,
86 created_by_module,
87 country_code,
88 geography_element1,
89 geography_element1_id,
90 geography_element1_code,
91 geography_element2,
92 geography_element2_id,
93 geography_element2_code,
94 geography_element3,
95 geography_element3_id,
96 geography_element3_code,
97 geography_element4,
98 geography_element4_id,
99 geography_element4_code,
100 geography_element5,
101 geography_element5_id,
102 geography_element5_code,
103 geography_element6,
104 geography_element6_id,
105 geography_element7,
106 geography_element7_id,
107 geography_element8,
108 geography_element8_id,
109 geography_element9,
110 geography_element9_id,
111 geography_element10,
112 geography_element10_id,
113 geometry,
114 timezone_code,
115 last_updated_by,
116 creation_date,
117 created_by,
118 last_update_date,
119 last_update_login,
120 application_id,
121 program_id,
122 program_login_id,
123 program_application_id,
124 request_id,
125 attribute_category,
126 attribute1,
127 attribute2,
128 attribute3,
129 attribute4,
130 attribute5,
131 attribute6,
132 attribute7,
133 attribute8,
134 attribute9,
135 attribute10,
136 attribute11,
137 attribute12,
138 attribute13,
139 attribute14,
140 attribute15,
141 attribute16,
142 attribute17,
143 attribute18,
144 attribute19,
145 attribute20
146 )
147 VALUES (
148 DECODE(x_geography_id,
149 FND_API.G_MISS_NUM, HZ_GEOGRAPHIES_S.NEXTVAL,
150 NULL, HZ_GEOGRAPHIES_S.NEXTVAL,
151 x_geography_id),
152 DECODE(x_object_version_number,
153 FND_API.G_MISS_NUM, NULL,
154 x_object_version_number),
155 DECODE(x_geography_type,
156 FND_API.G_MISS_CHAR, NULL,
157 x_geography_type),
158 DECODE(x_geography_name,
159 FND_API.G_MISS_CHAR, NULL,
160 x_geography_name),
161 DECODE(x_geography_use,
162 FND_API.G_MISS_CHAR, NULL,
163 x_geography_use),
164 DECODE(x_geography_code,
165 FND_API.G_MISS_CHAR, NULL,
166 x_geography_code),
167 DECODE(x_start_date,
168 FND_API.G_MISS_DATE, TO_DATE(NULL),
169 x_start_date),
170 DECODE(x_end_date,
171 FND_API.G_MISS_DATE, TO_DATE(NULL),
172 x_end_date),
173 DECODE(x_multiple_parent_flag,
174 FND_API.G_MISS_CHAR, NULL,
175 x_multiple_parent_flag),
176 DECODE(x_created_by_module,
177 FND_API.G_MISS_CHAR, NULL,
178 x_created_by_module),
179 DECODE(x_country_code,
180 FND_API.G_MISS_CHAR, NULL,
181 x_country_code),
182 DECODE(x_geography_element1,
183 FND_API.G_MISS_CHAR, NULL,
184 x_geography_element1),
185 DECODE(x_geography_element1_id,
186 FND_API.G_MISS_NUM, NULL,
187 x_geography_element1_id),
188 DECODE(x_geography_element1_code,
189 FND_API.G_MISS_CHAR, NULL,
190 x_geography_element1_code),
191 DECODE(x_geography_element2,
192 FND_API.G_MISS_CHAR, NULL,
193 x_geography_element2),
194 DECODE(x_geography_element2_id,
195 FND_API.G_MISS_NUM, NULL,
196 x_geography_element2_id),
197 DECODE(x_geography_element2_code,
198 FND_API.G_MISS_CHAR, NULL,
199 x_geography_element2_code),
200 DECODE(x_geography_element3,
201 FND_API.G_MISS_CHAR, NULL,
202 x_geography_element3),
203 DECODE(x_geography_element3_id,
204 FND_API.G_MISS_NUM, NULL,
205 x_geography_element3_id),
206 DECODE(x_geography_element3_code,
207 FND_API.G_MISS_CHAR, NULL,
208 x_geography_element3_code),
209 DECODE(x_geography_element4,
210 FND_API.G_MISS_CHAR, NULL,
211 x_geography_element4),
212 DECODE(x_geography_element4_id,
213 FND_API.G_MISS_NUM, NULL,
214 x_geography_element4_id),
215 DECODE(x_geography_element4_code,
216 FND_API.G_MISS_CHAR, NULL,
217 x_geography_element4_code),
218 DECODE(x_geography_element5,
219 FND_API.G_MISS_CHAR, NULL,
220 x_geography_element5),
221 DECODE(x_geography_element5_id,
222 FND_API.G_MISS_NUM, NULL,
223 x_geography_element5_id),
224 DECODE(x_geography_element5_code,
225 FND_API.G_MISS_CHAR, NULL,
226 x_geography_element5_code),
227 DECODE(x_geography_element6,
228 FND_API.G_MISS_CHAR, NULL,
229 x_geography_element6),
230 DECODE(x_geography_element6_id,
231 FND_API.G_MISS_NUM, NULL,
232 x_geography_element6_id),
233 DECODE(x_geography_element7,
234 FND_API.G_MISS_CHAR, NULL,
235 x_geography_element7),
236 DECODE(x_geography_element7_id,
237 FND_API.G_MISS_NUM, NULL,
238 x_geography_element7_id),
239 DECODE(x_geography_element8,
240 FND_API.G_MISS_CHAR, NULL,
241 x_geography_element8),
242 DECODE(x_geography_element8_id,
243 FND_API.G_MISS_NUM, NULL,
244 x_geography_element8_id),
245 DECODE(x_geography_element9,
246 FND_API.G_MISS_CHAR, NULL,
247 x_geography_element9),
248 DECODE(x_geography_element9_id,
249 FND_API.G_MISS_NUM, NULL,
250 x_geography_element9_id),
251 DECODE(x_geography_element10,
252 FND_API.G_MISS_CHAR, NULL,
253 x_geography_element10),
254 DECODE(x_geography_element10_id,
255 FND_API.G_MISS_NUM, NULL,
256 x_geography_element10_id),
257 x_geometry,
258 DECODE(x_timezone_code,
259 FND_API.G_MISS_CHAR, NULL,
260 x_timezone_code),
261 hz_utility_v2pub.last_updated_by,
262 hz_utility_v2pub.creation_date,
263 hz_utility_v2pub.created_by,
264 hz_utility_v2pub.last_update_date,
265 hz_utility_v2pub.last_update_login,
266 DECODE(x_application_id,
267 FND_API.G_MISS_NUM, NULL,
268 x_application_id),
269 hz_utility_v2pub.program_id,
270 DECODE(x_program_login_id,
271 FND_API.G_MISS_NUM, NULL,
272 x_program_login_id),
273 hz_utility_v2pub.program_application_id,
274 hz_utility_v2pub.request_id,
275 DECODE(x_attribute_category,
276 FND_API.G_MISS_CHAR, NULL,
277 x_attribute_category),
278 DECODE(x_attribute1,
279 FND_API.G_MISS_CHAR, NULL,
280 x_attribute1),
281 DECODE(x_attribute2,
282 FND_API.G_MISS_CHAR, NULL,
283 x_attribute2),
284 DECODE(x_attribute3,
285 FND_API.G_MISS_CHAR, NULL,
286 x_attribute3),
287 DECODE(x_attribute4,
288 FND_API.G_MISS_CHAR, NULL,
289 x_attribute4),
290 DECODE(x_attribute5,
291 FND_API.G_MISS_CHAR, NULL,
292 x_attribute5),
293 DECODE(x_attribute6,
294 FND_API.G_MISS_CHAR, NULL,
295 x_attribute6),
296 DECODE(x_attribute7,
297 FND_API.G_MISS_CHAR, NULL,
298 x_attribute7),
299 DECODE(x_attribute8,
300 FND_API.G_MISS_CHAR, NULL,
301 x_attribute8),
302 DECODE(x_attribute9,
303 FND_API.G_MISS_CHAR, NULL,
304 x_attribute9),
305 DECODE(x_attribute10,
306 FND_API.G_MISS_CHAR, NULL,
307 x_attribute10),
308 DECODE(x_attribute11,
309 FND_API.G_MISS_CHAR, NULL,
310 x_attribute11),
311 DECODE(x_attribute12,
312 FND_API.G_MISS_CHAR, NULL,
313 x_attribute12),
314 DECODE(x_attribute13,
315 FND_API.G_MISS_CHAR, NULL,
319 x_attribute14),
316 x_attribute13),
317 DECODE(x_attribute14,
318 FND_API.G_MISS_CHAR, NULL,
320 DECODE(x_attribute15,
321 FND_API.G_MISS_CHAR, NULL,
322 x_attribute15),
323 DECODE(x_attribute16,
324 FND_API.G_MISS_CHAR, NULL,
325 x_attribute16),
326 DECODE(x_attribute17,
327 FND_API.G_MISS_CHAR, NULL,
328 x_attribute17),
329 DECODE(x_attribute18,
330 FND_API.G_MISS_CHAR, NULL,
331 x_attribute18),
332 DECODE(x_attribute19,
333 FND_API.G_MISS_CHAR, NULL,
334 x_attribute19),
335 DECODE(x_attribute20,
336 FND_API.G_MISS_CHAR, NULL,
337 x_attribute20)
338 ) RETURNING
339 rowid,
340 geography_id
341 INTO
342 x_rowid,
343 x_geography_id;
344
345 l_success := 'Y';
346
347 --dbms_output.put_line.PUT_LINE('l_success in after insert '||l_success);
348
349 EXCEPTION
350 WHEN DUP_VAL_ON_INDEX THEN
351 IF INSTR(SQLERRM, 'HZ_GEOGRAPHIES_S') <> 0 THEN
352 DECLARE
353 l_count NUMBER;
354 l_dummy VARCHAR2(1);
355 BEGIN
356 l_count := 1;
357 WHILE l_count > 0 LOOP
358 SELECT HZ_GEOGRAPHIES_S.NEXTVAL
359 INTO x_geography_id FROM dual;
360 BEGIN
361 SELECT 'Y' INTO l_dummy
362 FROM HZ_GEOGRAPHIES
363 WHERE geography_id = x_geography_id;
364 l_count := 1;
365 EXCEPTION
366 WHEN NO_DATA_FOUND THEN
367 l_count := 0;
368 END;
369 END LOOP;
370 END;
371 END IF;
372
373 END;
374 END LOOP;
375
376 END Insert_Row;
377
378 PROCEDURE Update_Row (
379 x_rowid IN OUT NOCOPY VARCHAR2,
380 x_geography_id IN NUMBER,
381 x_object_version_number IN NUMBER,
382 x_geography_type IN VARCHAR2,
383 x_geography_name IN VARCHAR2,
384 x_geography_use IN VARCHAR2,
385 x_geography_code IN VARCHAR2,
386 x_start_date IN DATE,
387 x_end_date IN DATE,
388 x_multiple_parent_flag IN VARCHAR2,
389 x_created_by_module IN VARCHAR2,
390 x_country_code IN VARCHAR2,
391 x_geography_element1 IN VARCHAR2,
392 x_geography_element1_id IN NUMBER,
393 x_geography_element1_code IN VARCHAR2,
394 x_geography_element2 IN VARCHAR2,
395 x_geography_element2_id IN NUMBER,
396 x_geography_element2_code IN VARCHAR2,
397 x_geography_element3 IN VARCHAR2,
398 x_geography_element3_id IN NUMBER,
399 x_geography_element3_code IN VARCHAR2,
400 x_geography_element4 IN VARCHAR2,
401 x_geography_element4_id IN NUMBER,
402 x_geography_element4_code IN VARCHAR2,
403 x_geography_element5 IN VARCHAR2,
404 x_geography_element5_id IN NUMBER,
405 x_geography_element5_code IN VARCHAR2,
406 x_geography_element6 IN VARCHAR2,
407 x_geography_element6_id IN NUMBER,
408 x_geography_element7 IN VARCHAR2,
409 x_geography_element7_id IN NUMBER,
410 x_geography_element8 IN VARCHAR2,
411 x_geography_element8_id IN NUMBER,
412 x_geography_element9 IN VARCHAR2,
413 x_geography_element9_id IN NUMBER,
414 x_geography_element10 IN VARCHAR2,
415 x_geography_element10_id IN NUMBER,
416 x_geometry IN MDSYS.SDO_GEOMETRY,
417 x_timezone_code IN VARCHAR2,
418 x_application_id IN NUMBER,
419 x_program_login_id IN NUMBER,
420 x_attribute_category IN VARCHAR2,
421 x_attribute1 IN VARCHAR2,
422 x_attribute2 IN VARCHAR2,
423 x_attribute3 IN VARCHAR2,
424 x_attribute4 IN VARCHAR2,
425 x_attribute5 IN VARCHAR2,
426 x_attribute6 IN VARCHAR2,
427 x_attribute7 IN VARCHAR2,
428 x_attribute8 IN VARCHAR2,
429 x_attribute9 IN VARCHAR2,
430 x_attribute10 IN VARCHAR2,
431 x_attribute11 IN VARCHAR2,
432 x_attribute12 IN VARCHAR2,
433 x_attribute13 IN VARCHAR2,
437 x_attribute17 IN VARCHAR2,
434 x_attribute14 IN VARCHAR2,
435 x_attribute15 IN VARCHAR2,
436 x_attribute16 IN VARCHAR2,
438 x_attribute18 IN VARCHAR2,
439 x_attribute19 IN VARCHAR2,
440 x_attribute20 IN VARCHAR2
441 ) IS
442 BEGIN
443
444 UPDATE HZ_GEOGRAPHIES
445 SET
446 geography_id =
447 DECODE(x_geography_id,
448 NULL, geography_id,
449 FND_API.G_MISS_NUM, NULL,
450 x_geography_id),
451 object_version_number =
452 DECODE(x_object_version_number,
453 NULL, object_version_number,
454 FND_API.G_MISS_NUM, NULL,
455 x_object_version_number),
456 geography_type =
457 DECODE(x_geography_type,
458 NULL, geography_type,
459 FND_API.G_MISS_CHAR, NULL,
460 x_geography_type),
461 geography_name =
462 DECODE(x_geography_name,
463 NULL, geography_name,
464 FND_API.G_MISS_CHAR, NULL,
465 x_geography_name),
466 geography_use =
467 DECODE(x_geography_use,
468 NULL, geography_use,
469 FND_API.G_MISS_CHAR, NULL,
470 x_geography_use),
471 geography_code =
472 DECODE(x_geography_code,
473 NULL, geography_code,
474 FND_API.G_MISS_CHAR, NULL,
475 x_geography_code),
476 start_date =
477 DECODE(x_start_date,
478 NULL, start_date,
479 FND_API.G_MISS_DATE, NULL,
480 x_start_date),
481 end_date =
482 DECODE(x_end_date,
483 NULL, end_date,
484 FND_API.G_MISS_DATE, NULL,
485 x_end_date),
486 multiple_parent_flag =
487 DECODE(x_multiple_parent_flag,
488 NULL, multiple_parent_flag,
489 FND_API.G_MISS_CHAR, NULL,
490 x_multiple_parent_flag),
491 created_by_module =
492 DECODE(x_created_by_module,
493 NULL, created_by_module,
494 FND_API.G_MISS_CHAR, NULL,
495 x_created_by_module),
496 country_code =
497 DECODE(x_country_code,
498 NULL, country_code,
499 FND_API.G_MISS_CHAR, NULL,
500 x_country_code),
501 geography_element1 =
502 DECODE(x_geography_element1,
503 NULL, geography_element1,
504 FND_API.G_MISS_CHAR, NULL,
505 x_geography_element1),
506 geography_element1_id =
507 DECODE(x_geography_element1_id,
508 NULL, geography_element1_id,
509 FND_API.G_MISS_NUM, NULL,
510 x_geography_element1_id),
511 geography_element1_code =
512 DECODE(x_geography_element1_code,
513 NULL, geography_element1_code,
514 FND_API.G_MISS_CHAR, NULL,
515 x_geography_element1_code),
516 geography_element2 =
517 DECODE(x_geography_element2,
518 NULL, geography_element2,
519 FND_API.G_MISS_CHAR, NULL,
520 x_geography_element2),
521 geography_element2_id =
522 DECODE(x_geography_element2_id,
523 NULL, geography_element2_id,
524 FND_API.G_MISS_NUM, NULL,
525 x_geography_element2_id),
526 geography_element2_code =
527 DECODE(x_geography_element2_code,
528 NULL, geography_element2_code,
529 FND_API.G_MISS_CHAR, NULL,
530 x_geography_element2_code),
531 geography_element3 =
532 DECODE(x_geography_element3,
533 NULL, geography_element3,
534 FND_API.G_MISS_CHAR, NULL,
535 x_geography_element3),
536 geography_element3_id =
537 DECODE(x_geography_element3_id,
538 NULL, geography_element3_id,
539 FND_API.G_MISS_NUM, NULL,
540 x_geography_element3_id),
541 geography_element3_code =
542 DECODE(x_geography_element3_code,
543 NULL, geography_element3_code,
544 FND_API.G_MISS_CHAR, NULL,
545 x_geography_element3_code),
546 geography_element4 =
547 DECODE(x_geography_element4,
548 NULL, geography_element4,
549 FND_API.G_MISS_CHAR, NULL,
550 x_geography_element4),
551 geography_element4_id =
552 DECODE(x_geography_element4_id,
553 NULL, geography_element4_id,
554 FND_API.G_MISS_NUM, NULL,
555 x_geography_element4_id),
556 geography_element4_code =
557 DECODE(x_geography_element4_code,
558 NULL, geography_element4_code,
559 FND_API.G_MISS_CHAR, NULL,
560 x_geography_element4_code),
561 geography_element5 =
562 DECODE(x_geography_element5,
566 geography_element5_id =
563 NULL, geography_element5,
564 FND_API.G_MISS_CHAR, NULL,
565 x_geography_element5),
567 DECODE(x_geography_element5_id,
568 NULL, geography_element5_id,
569 FND_API.G_MISS_NUM, NULL,
570 x_geography_element5_id),
571 geography_element5_code =
572 DECODE(x_geography_element5_code,
573 NULL, geography_element5_code,
574 FND_API.G_MISS_CHAR, NULL,
575 x_geography_element5_code),
576 geography_element6 =
577 DECODE(x_geography_element6,
578 NULL, geography_element6,
579 FND_API.G_MISS_CHAR, NULL,
580 x_geography_element6),
581 geography_element6_id =
582 DECODE(x_geography_element6_id,
583 NULL, geography_element6_id,
584 FND_API.G_MISS_NUM, NULL,
585 x_geography_element6_id),
586 geography_element7 =
587 DECODE(x_geography_element7,
588 NULL, geography_element7,
589 FND_API.G_MISS_CHAR, NULL,
590 x_geography_element7),
591 geography_element7_id =
592 DECODE(x_geography_element7_id,
593 NULL, geography_element7_id,
594 FND_API.G_MISS_NUM, NULL,
595 x_geography_element7_id),
596 geography_element8 =
597 DECODE(x_geography_element8,
598 NULL, geography_element8,
599 FND_API.G_MISS_CHAR, NULL,
600 x_geography_element8),
601 geography_element8_id =
602 DECODE(x_geography_element8_id,
603 NULL, geography_element8_id,
604 FND_API.G_MISS_NUM, NULL,
605 x_geography_element8_id),
606 geography_element9 =
607 DECODE(x_geography_element9,
608 NULL, geography_element9,
609 FND_API.G_MISS_CHAR, NULL,
610 x_geography_element9),
611 geography_element9_id =
612 DECODE(x_geography_element9_id,
613 NULL, geography_element9_id,
614 FND_API.G_MISS_NUM, NULL,
615 x_geography_element9_id),
616 geography_element10 =
617 DECODE(x_geography_element10,
618 NULL, geography_element10,
619 FND_API.G_MISS_CHAR, NULL,
620 x_geography_element10),
621 geography_element10_id =
622 DECODE(x_geography_element10_id,
623 NULL, geography_element10_id,
624 FND_API.G_MISS_NUM, NULL,
625 x_geography_element10_id),
626 geometry = geometry,
627 timezone_code =
628 DECODE(x_timezone_code,
629 NULL, timezone_code,
630 FND_API.G_MISS_CHAR, NULL,
631 x_timezone_code),
632 last_updated_by = hz_utility_v2pub.last_updated_by,
633 creation_date = creation_date,
634 created_by = created_by,
635 last_update_date = hz_utility_v2pub.last_update_date,
636 last_update_login = hz_utility_v2pub.last_update_login,
637 application_id =
638 DECODE(x_application_id,
639 NULL, application_id,
640 FND_API.G_MISS_NUM, NULL,
641 x_application_id),
642 program_id = hz_utility_v2pub.program_id,
643 program_login_id =
644 DECODE(x_program_login_id,
645 NULL, program_login_id,
646 FND_API.G_MISS_NUM, NULL,
647 x_program_login_id),
648 program_application_id = hz_utility_v2pub.program_application_id,
649 request_id = hz_utility_v2pub.request_id,
650 attribute_category =
651 DECODE(x_attribute_category,
652 NULL, attribute_category,
653 FND_API.G_MISS_CHAR, NULL,
654 x_attribute_category),
655 attribute1 =
656 DECODE(x_attribute1,
657 NULL, attribute1,
658 FND_API.G_MISS_CHAR, NULL,
659 x_attribute1),
660 attribute2 =
661 DECODE(x_attribute2,
662 NULL, attribute2,
663 FND_API.G_MISS_CHAR, NULL,
664 x_attribute2),
665 attribute3 =
666 DECODE(x_attribute3,
667 NULL, attribute3,
668 FND_API.G_MISS_CHAR, NULL,
669 x_attribute3),
670 attribute4 =
671 DECODE(x_attribute4,
672 NULL, attribute4,
673 FND_API.G_MISS_CHAR, NULL,
674 x_attribute4),
675 attribute5 =
676 DECODE(x_attribute5,
677 NULL, attribute5,
678 FND_API.G_MISS_CHAR, NULL,
679 x_attribute5),
680 attribute6 =
681 DECODE(x_attribute6,
682 NULL, attribute6,
683 FND_API.G_MISS_CHAR, NULL,
684 x_attribute6),
685 attribute7 =
686 DECODE(x_attribute7,
687 NULL, attribute7,
688 FND_API.G_MISS_CHAR, NULL,
689 x_attribute7),
693 FND_API.G_MISS_CHAR, NULL,
690 attribute8 =
691 DECODE(x_attribute8,
692 NULL, attribute8,
694 x_attribute8),
695 attribute9 =
696 DECODE(x_attribute9,
697 NULL, attribute9,
698 FND_API.G_MISS_CHAR, NULL,
699 x_attribute9),
700 attribute10 =
701 DECODE(x_attribute10,
702 NULL, attribute10,
703 FND_API.G_MISS_CHAR, NULL,
704 x_attribute10),
705 attribute11 =
706 DECODE(x_attribute11,
707 NULL, attribute11,
708 FND_API.G_MISS_CHAR, NULL,
709 x_attribute11),
710 attribute12 =
711 DECODE(x_attribute12,
712 NULL, attribute12,
713 FND_API.G_MISS_CHAR, NULL,
714 x_attribute12),
715 attribute13 =
716 DECODE(x_attribute13,
717 NULL, attribute13,
718 FND_API.G_MISS_CHAR, NULL,
719 x_attribute13),
720 attribute14 =
721 DECODE(x_attribute14,
722 NULL, attribute14,
723 FND_API.G_MISS_CHAR, NULL,
724 x_attribute14),
725 attribute15 =
726 DECODE(x_attribute15,
727 NULL, attribute15,
728 FND_API.G_MISS_CHAR, NULL,
729 x_attribute15),
730 attribute16 =
731 DECODE(x_attribute16,
732 NULL, attribute16,
733 FND_API.G_MISS_CHAR, NULL,
734 x_attribute16),
735 attribute17 =
736 DECODE(x_attribute17,
737 NULL, attribute17,
738 FND_API.G_MISS_CHAR, NULL,
739 x_attribute17),
740 attribute18 =
741 DECODE(x_attribute18,
742 NULL, attribute18,
743 FND_API.G_MISS_CHAR, NULL,
744 x_attribute18),
745 attribute19 =
746 DECODE(x_attribute19,
747 NULL, attribute19,
748 FND_API.G_MISS_CHAR, NULL,
749 x_attribute19),
750 attribute20 =
751 DECODE(x_attribute20,
752 NULL, attribute20,
753 FND_API.G_MISS_CHAR, NULL,
754 x_attribute20)
755 WHERE rowid = x_rowid;
756
757 IF ( SQL%NOTFOUND ) THEN
758 RAISE NO_DATA_FOUND;
759 END IF;
760
761 END Update_Row;
762
763 /*PROCEDURE Lock_Row (
764 x_rowid IN OUT NOCOPY VARCHAR2,
765 x_geography_id IN NUMBER,
766 x_object_version_number IN NUMBER,
767 x_geography_type IN VARCHAR2,
768 x_geography_name IN VARCHAR2,
769 x_geography_use IN VARCHAR2,
770 x_geography_code IN VARCHAR2,
771 x_start_date IN DATE,
772 x_end_date IN DATE,
773 x_multiple_parent_flag IN VARCHAR2,
774 x_created_by_module IN VARCHAR2,
775 x_country_code IN VARCHAR2,
776 x_geography_element1 IN VARCHAR2,
777 x_geography_element1_id IN NUMBER,
778 x_geography_element1_code IN VARCHAR2,
779 x_geography_element2 IN VARCHAR2,
780 x_geography_element2_id IN NUMBER,
781 x_geography_element2_code IN VARCHAR2,
782 x_geography_element3 IN VARCHAR2,
783 x_geography_element3_id IN NUMBER,
784 x_geography_element3_code IN VARCHAR2,
785 x_geography_element4 IN VARCHAR2,
786 x_geography_element4_id IN NUMBER,
787 x_geography_element4_code IN VARCHAR2,
788 x_geography_element5 IN VARCHAR2,
789 x_geography_element5_id IN NUMBER,
790 x_geography_element5_code IN VARCHAR2,
791 x_geography_element6 IN VARCHAR2,
792 x_geography_element6_id IN NUMBER,
793 x_geography_element7 IN VARCHAR2,
794 x_geography_element7_id IN NUMBER,
795 x_geography_element8 IN VARCHAR2,
796 x_geography_element8_id IN NUMBER,
800 x_geography_element10_id IN NUMBER,
797 x_geography_element9 IN VARCHAR2,
798 x_geography_element9_id IN NUMBER,
799 x_geography_element10 IN VARCHAR2,
801 x_geometry IN MDSYS.SDO_GEOMETRY,
802 x_timezone_code IN VARCHAR2,
803 x_last_updated_by IN NUMBER,
804 x_creation_date IN DATE,
805 x_created_by IN NUMBER,
806 x_last_update_date IN DATE,
807 x_last_update_login IN NUMBER,
808 x_application_id IN NUMBER,
809 x_program_id IN NUMBER,
810 x_program_login_id IN NUMBER,
811 x_program_application_id IN NUMBER,
812 x_request_id IN NUMBER,
813 x_attribute_category IN VARCHAR2,
814 x_attribute1 IN VARCHAR2,
815 x_attribute2 IN VARCHAR2,
816 x_attribute3 IN VARCHAR2,
817 x_attribute4 IN VARCHAR2,
818 x_attribute5 IN VARCHAR2,
819 x_attribute6 IN VARCHAR2,
820 x_attribute7 IN VARCHAR2,
821 x_attribute8 IN VARCHAR2,
822 x_attribute9 IN VARCHAR2,
823 x_attribute10 IN VARCHAR2,
824 x_attribute11 IN VARCHAR2,
825 x_attribute12 IN VARCHAR2,
826 x_attribute13 IN VARCHAR2,
827 x_attribute14 IN VARCHAR2,
828 x_attribute15 IN VARCHAR2,
829 x_attribute16 IN VARCHAR2,
830 x_attribute17 IN VARCHAR2,
831 x_attribute18 IN VARCHAR2,
832 x_attribute19 IN VARCHAR2,
833 x_attribute20 IN VARCHAR2
834 ) IS
835
836 CURSOR c IS
837 SELECT * FROM hz_geographies
838 WHERE rowid = x_rowid
839 FOR UPDATE NOWAIT;
840 Recinfo c%ROWTYPE;
841
842 BEGIN
843
844 OPEN c;
845 FETCH c INTO Recinfo;
846 IF ( c%NOTFOUND ) THEN
847 CLOSE c;
848 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
849 APP_EXCEPTION.RAISE_EXCEPTION;
850 END IF;
851 CLOSE C;
852
853 IF (
854 ( ( Recinfo.geography_id = x_geography_id )
855 OR ( ( Recinfo.geography_id IS NULL )
856 AND ( x_geography_id IS NULL ) ) )
857 AND ( ( Recinfo.object_version_number = x_object_version_number )
858 OR ( ( Recinfo.object_version_number IS NULL )
859 AND ( x_object_version_number IS NULL ) ) )
860 AND ( ( Recinfo.geography_type = x_geography_type )
861 OR ( ( Recinfo.geography_type IS NULL )
862 AND ( x_geography_type IS NULL ) ) )
863 AND ( ( Recinfo.geography_name = x_geography_name )
864 OR ( ( Recinfo.geography_name IS NULL )
865 AND ( x_geography_name IS NULL ) ) )
866 AND ( ( Recinfo.geography_use = x_geography_use )
867 OR ( ( Recinfo.geography_use IS NULL )
868 AND ( x_geography_use IS NULL ) ) )
869 AND ( ( Recinfo.geography_code = x_geography_code )
870 OR ( ( Recinfo.geography_code IS NULL )
871 AND ( x_geography_code IS NULL ) ) )
872 AND ( ( Recinfo.start_date = x_start_date )
873 OR ( ( Recinfo.start_date IS NULL )
874 AND ( x_start_date IS NULL ) ) )
875 AND ( ( Recinfo.end_date = x_end_date )
876 OR ( ( Recinfo.end_date IS NULL )
877 AND ( x_end_date IS NULL ) ) )
878 AND ( ( Recinfo.multiple_parent_flag = x_multiple_parent_flag )
879 OR ( ( Recinfo.multiple_parent_flag IS NULL )
880 AND ( x_multiple_parent_flag IS NULL ) ) )
881 AND ( ( Recinfo.created_by_module = x_created_by_module )
882 OR ( ( Recinfo.created_by_module IS NULL )
883 AND ( x_created_by_module IS NULL ) ) )
884 AND ( ( Recinfo.country_code = x_country_code )
885 OR ( ( Recinfo.country_code IS NULL )
886 AND ( x_country_code IS NULL ) ) )
887 AND ( ( Recinfo.geography_element1 = x_geography_element1 )
888 OR ( ( Recinfo.geography_element1 IS NULL )
889 AND ( x_geography_element1 IS NULL ) ) )
890 AND ( ( Recinfo.geography_element1_id = x_geography_element1_id )
891 OR ( ( Recinfo.geography_element1_id IS NULL )
892 AND ( x_geography_element1_id IS NULL ) ) )
893 AND ( ( Recinfo.geography_element1_code = x_geography_element1_code )
894 OR ( ( Recinfo.geography_element1_code IS NULL )
895 AND ( x_geography_element1_code IS NULL ) ) )
896 AND ( ( Recinfo.geography_element2 = x_geography_element2 )
897 OR ( ( Recinfo.geography_element2 IS NULL )
898 AND ( x_geography_element2 IS NULL ) ) )
899 AND ( ( Recinfo.geography_element2_id = x_geography_element2_id )
900 OR ( ( Recinfo.geography_element2_id IS NULL )
901 AND ( x_geography_element2_id IS NULL ) ) )
905 AND ( ( Recinfo.geography_element3 = x_geography_element3 )
902 AND ( ( Recinfo.geography_element2_code = x_geography_element2_code )
903 OR ( ( Recinfo.geography_element2_code IS NULL )
904 AND ( x_geography_element2_code IS NULL ) ) )
906 OR ( ( Recinfo.geography_element3 IS NULL )
907 AND ( x_geography_element3 IS NULL ) ) )
908 AND ( ( Recinfo.geography_element3_id = x_geography_element3_id )
909 OR ( ( Recinfo.geography_element3_id IS NULL )
910 AND ( x_geography_element3_id IS NULL ) ) )
911 AND ( ( Recinfo.geography_element3_code = x_geography_element3_code )
912 OR ( ( Recinfo.geography_element3_code IS NULL )
913 AND ( x_geography_element3_code IS NULL ) ) )
914 AND ( ( Recinfo.geography_element4 = x_geography_element4 )
915 OR ( ( Recinfo.geography_element4 IS NULL )
916 AND ( x_geography_element4 IS NULL ) ) )
917 AND ( ( Recinfo.geography_element4_id = x_geography_element4_id )
918 OR ( ( Recinfo.geography_element4_id IS NULL )
919 AND ( x_geography_element4_id IS NULL ) ) )
920 AND ( ( Recinfo.geography_element4_code = x_geography_element4_code )
921 OR ( ( Recinfo.geography_element4_code IS NULL )
922 AND ( x_geography_element4_code IS NULL ) ) )
923 AND ( ( Recinfo.geography_element5 = x_geography_element5 )
924 OR ( ( Recinfo.geography_element5 IS NULL )
925 AND ( x_geography_element5 IS NULL ) ) )
926 AND ( ( Recinfo.geography_element5_id = x_geography_element5_id )
927 OR ( ( Recinfo.geography_element5_id IS NULL )
928 AND ( x_geography_element5_id IS NULL ) ) )
929 AND ( ( Recinfo.geography_element5_code = x_geography_element5_code )
930 OR ( ( Recinfo.geography_element5_code IS NULL )
931 AND ( x_geography_element5_code IS NULL ) ) )
932 AND ( ( Recinfo.geography_element6 = x_geography_element6 )
933 OR ( ( Recinfo.geography_element6 IS NULL )
934 AND ( x_geography_element6 IS NULL ) ) )
935 AND ( ( Recinfo.geography_element6_id = x_geography_element6_id )
936 OR ( ( Recinfo.geography_element6_id IS NULL )
937 AND ( x_geography_element6_id IS NULL ) ) )
938 AND ( ( Recinfo.geography_element7 = x_geography_element7 )
939 OR ( ( Recinfo.geography_element7 IS NULL )
940 AND ( x_geography_element7 IS NULL ) ) )
941 AND ( ( Recinfo.geography_element7_id = x_geography_element7_id )
942 OR ( ( Recinfo.geography_element7_id IS NULL )
943 AND ( x_geography_element7_id IS NULL ) ) )
944 AND ( ( Recinfo.geography_element8 = x_geography_element8 )
945 OR ( ( Recinfo.geography_element8 IS NULL )
946 AND ( x_geography_element8 IS NULL ) ) )
947 AND ( ( Recinfo.geography_element8_id = x_geography_element8_id )
948 OR ( ( Recinfo.geography_element8_id IS NULL )
949 AND ( x_geography_element8_id IS NULL ) ) )
950 AND ( ( Recinfo.geography_element9 = x_geography_element9 )
951 OR ( ( Recinfo.geography_element9 IS NULL )
952 AND ( x_geography_element9 IS NULL ) ) )
953 AND ( ( Recinfo.geography_element9_id = x_geography_element9_id )
954 OR ( ( Recinfo.geography_element9_id IS NULL )
955 AND ( x_geography_element9_id IS NULL ) ) )
956 AND ( ( Recinfo.geography_element10 = x_geography_element10 )
957 OR ( ( Recinfo.geography_element10 IS NULL )
958 AND ( x_geography_element10 IS NULL ) ) )
959 AND ( ( Recinfo.geography_element10_id = x_geography_element10_id )
960 OR ( ( Recinfo.geography_element10_id IS NULL )
961 AND ( x_geography_element10_id IS NULL ) ) )
962 AND ( ( Recinfo.geometry = x_geometry )
963 OR ( ( Recinfo.geometry IS NULL )
964 AND ( x_geometry IS NULL ) ) )
965 AND ( ( Recinfo.timezone_code = x_timezone_code )
966 OR ( ( Recinfo.timezone_code IS NULL )
967 AND ( x_timezone_code IS NULL ) ) )
968 AND ( ( Recinfo.last_updated_by = x_last_updated_by )
969 OR ( ( Recinfo.last_updated_by IS NULL )
970 AND ( x_last_updated_by IS NULL ) ) )
971 AND ( ( Recinfo.creation_date = x_creation_date )
972 OR ( ( Recinfo.creation_date IS NULL )
973 AND ( x_creation_date IS NULL ) ) )
974 AND ( ( Recinfo.created_by = x_created_by )
975 OR ( ( Recinfo.created_by IS NULL )
976 AND ( x_created_by IS NULL ) ) )
977 AND ( ( Recinfo.last_update_date = x_last_update_date )
978 OR ( ( Recinfo.last_update_date IS NULL )
979 AND ( x_last_update_date IS NULL ) ) )
980 AND ( ( Recinfo.last_update_login = x_last_update_login )
981 OR ( ( Recinfo.last_update_login IS NULL )
982 AND ( x_last_update_login IS NULL ) ) )
983 AND ( ( Recinfo.application_id = x_application_id )
984 OR ( ( Recinfo.application_id IS NULL )
985 AND ( x_application_id IS NULL ) ) )
986 AND ( ( Recinfo.program_id = x_program_id )
987 OR ( ( Recinfo.program_id IS NULL )
988 AND ( x_program_id IS NULL ) ) )
989 AND ( ( Recinfo.program_login_id = x_program_login_id )
990 OR ( ( Recinfo.program_login_id IS NULL )
991 AND ( x_program_login_id IS NULL ) ) )
992 AND ( ( Recinfo.program_application_id = x_program_application_id )
993 OR ( ( Recinfo.program_application_id IS NULL )
994 AND ( x_program_application_id IS NULL ) ) )
995 AND ( ( Recinfo.request_id = x_request_id )
999 OR ( ( Recinfo.attribute_category IS NULL )
996 OR ( ( Recinfo.request_id IS NULL )
997 AND ( x_request_id IS NULL ) ) )
998 AND ( ( Recinfo.attribute_category = x_attribute_category )
1000 AND ( x_attribute_category IS NULL ) ) )
1001 AND ( ( Recinfo.attribute1 = x_attribute1 )
1002 OR ( ( Recinfo.attribute1 IS NULL )
1003 AND ( x_attribute1 IS NULL ) ) )
1004 AND ( ( Recinfo.attribute2 = x_attribute2 )
1005 OR ( ( Recinfo.attribute2 IS NULL )
1006 AND ( x_attribute2 IS NULL ) ) )
1007 AND ( ( Recinfo.attribute3 = x_attribute3 )
1008 OR ( ( Recinfo.attribute3 IS NULL )
1009 AND ( x_attribute3 IS NULL ) ) )
1010 AND ( ( Recinfo.attribute4 = x_attribute4 )
1011 OR ( ( Recinfo.attribute4 IS NULL )
1012 AND ( x_attribute4 IS NULL ) ) )
1013 AND ( ( Recinfo.attribute5 = x_attribute5 )
1014 OR ( ( Recinfo.attribute5 IS NULL )
1015 AND ( x_attribute5 IS NULL ) ) )
1016 AND ( ( Recinfo.attribute6 = x_attribute6 )
1017 OR ( ( Recinfo.attribute6 IS NULL )
1018 AND ( x_attribute6 IS NULL ) ) )
1019 AND ( ( Recinfo.attribute7 = x_attribute7 )
1020 OR ( ( Recinfo.attribute7 IS NULL )
1021 AND ( x_attribute7 IS NULL ) ) )
1022 AND ( ( Recinfo.attribute8 = x_attribute8 )
1023 OR ( ( Recinfo.attribute8 IS NULL )
1024 AND ( x_attribute8 IS NULL ) ) )
1025 AND ( ( Recinfo.attribute9 = x_attribute9 )
1026 OR ( ( Recinfo.attribute9 IS NULL )
1027 AND ( x_attribute9 IS NULL ) ) )
1028 AND ( ( Recinfo.attribute10 = x_attribute10 )
1029 OR ( ( Recinfo.attribute10 IS NULL )
1030 AND ( x_attribute10 IS NULL ) ) )
1031 AND ( ( Recinfo.attribute11 = x_attribute11 )
1032 OR ( ( Recinfo.attribute11 IS NULL )
1033 AND ( x_attribute11 IS NULL ) ) )
1034 AND ( ( Recinfo.attribute12 = x_attribute12 )
1035 OR ( ( Recinfo.attribute12 IS NULL )
1036 AND ( x_attribute12 IS NULL ) ) )
1037 AND ( ( Recinfo.attribute13 = x_attribute13 )
1038 OR ( ( Recinfo.attribute13 IS NULL )
1039 AND ( x_attribute13 IS NULL ) ) )
1040 AND ( ( Recinfo.attribute14 = x_attribute14 )
1041 OR ( ( Recinfo.attribute14 IS NULL )
1042 AND ( x_attribute14 IS NULL ) ) )
1043 AND ( ( Recinfo.attribute15 = x_attribute15 )
1044 OR ( ( Recinfo.attribute15 IS NULL )
1045 AND ( x_attribute15 IS NULL ) ) )
1046 AND ( ( Recinfo.attribute16 = x_attribute16 )
1047 OR ( ( Recinfo.attribute16 IS NULL )
1048 AND ( x_attribute16 IS NULL ) ) )
1049 AND ( ( Recinfo.attribute17 = x_attribute17 )
1050 OR ( ( Recinfo.attribute17 IS NULL )
1051 AND ( x_attribute17 IS NULL ) ) )
1052 AND ( ( Recinfo.attribute18 = x_attribute18 )
1053 OR ( ( Recinfo.attribute18 IS NULL )
1054 AND ( x_attribute18 IS NULL ) ) )
1055 AND ( ( Recinfo.attribute19 = x_attribute19 )
1056 OR ( ( Recinfo.attribute19 IS NULL )
1057 AND ( x_attribute19 IS NULL ) ) )
1058 AND ( ( Recinfo.attribute20 = x_attribute20 )
1059 OR ( ( Recinfo.attribute20 IS NULL )
1060 AND ( x_attribute20 IS NULL ) ) )
1061 ) THEN
1062 RETURN;
1063 ELSE
1064 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
1065 APP_EXCEPTION.RAISE_EXCEPTION;
1066 END IF;
1067
1068 END Lock_Row; */
1069
1070 PROCEDURE Select_Row (
1071 x_geography_id IN OUT NOCOPY NUMBER,
1072 x_object_version_number OUT NOCOPY NUMBER,
1073 x_geography_type OUT NOCOPY VARCHAR2,
1074 x_geography_name OUT NOCOPY VARCHAR2,
1075 x_geography_use OUT NOCOPY VARCHAR2,
1076 x_geography_code OUT NOCOPY VARCHAR2,
1077 x_start_date OUT NOCOPY DATE,
1078 x_end_date OUT NOCOPY DATE,
1079 x_multiple_parent_flag OUT NOCOPY VARCHAR2,
1080 x_created_by_module OUT NOCOPY VARCHAR2,
1081 x_country_code OUT NOCOPY VARCHAR2,
1082 x_geography_element1 OUT NOCOPY VARCHAR2,
1083 x_geography_element1_id OUT NOCOPY NUMBER,
1084 x_geography_element1_code OUT NOCOPY VARCHAR2,
1085 x_geography_element2 OUT NOCOPY VARCHAR2,
1086 x_geography_element2_id OUT NOCOPY NUMBER,
1087 x_geography_element2_code OUT NOCOPY VARCHAR2,
1088 x_geography_element3 OUT NOCOPY VARCHAR2,
1089 x_geography_element3_id OUT NOCOPY NUMBER,
1090 x_geography_element3_code OUT NOCOPY VARCHAR2,
1091 x_geography_element4 OUT NOCOPY VARCHAR2,
1092 x_geography_element4_id OUT NOCOPY NUMBER,
1093 x_geography_element4_code OUT NOCOPY VARCHAR2,
1094 x_geography_element5 OUT NOCOPY VARCHAR2,
1095 x_geography_element5_id OUT NOCOPY NUMBER,
1096 x_geography_element5_code OUT NOCOPY VARCHAR2,
1097 x_geography_element6 OUT NOCOPY VARCHAR2,
1098 x_geography_element6_id OUT NOCOPY NUMBER,
1102 x_geography_element8_id OUT NOCOPY NUMBER,
1099 x_geography_element7 OUT NOCOPY VARCHAR2,
1100 x_geography_element7_id OUT NOCOPY NUMBER,
1101 x_geography_element8 OUT NOCOPY VARCHAR2,
1103 x_geography_element9 OUT NOCOPY VARCHAR2,
1104 x_geography_element9_id OUT NOCOPY NUMBER,
1105 x_geography_element10 OUT NOCOPY VARCHAR2,
1106 x_geography_element10_id OUT NOCOPY NUMBER,
1107 x_geometry OUT NOCOPY MDSYS.SDO_GEOMETRY,
1108 x_timezone_code OUT NOCOPY VARCHAR2,
1109 x_application_id OUT NOCOPY NUMBER,
1110 x_program_login_id OUT NOCOPY NUMBER,
1111 x_attribute_category OUT NOCOPY VARCHAR2,
1112 x_attribute1 OUT NOCOPY VARCHAR2,
1113 x_attribute2 OUT NOCOPY VARCHAR2,
1114 x_attribute3 OUT NOCOPY VARCHAR2,
1115 x_attribute4 OUT NOCOPY VARCHAR2,
1116 x_attribute5 OUT NOCOPY VARCHAR2,
1117 x_attribute6 OUT NOCOPY VARCHAR2,
1118 x_attribute7 OUT NOCOPY VARCHAR2,
1119 x_attribute8 OUT NOCOPY VARCHAR2,
1120 x_attribute9 OUT NOCOPY VARCHAR2,
1121 x_attribute10 OUT NOCOPY VARCHAR2,
1122 x_attribute11 OUT NOCOPY VARCHAR2,
1123 x_attribute12 OUT NOCOPY VARCHAR2,
1124 x_attribute13 OUT NOCOPY VARCHAR2,
1125 x_attribute14 OUT NOCOPY VARCHAR2,
1126 x_attribute15 OUT NOCOPY VARCHAR2,
1127 x_attribute16 OUT NOCOPY VARCHAR2,
1128 x_attribute17 OUT NOCOPY VARCHAR2,
1129 x_attribute18 OUT NOCOPY VARCHAR2,
1130 x_attribute19 OUT NOCOPY VARCHAR2,
1131 x_attribute20 OUT NOCOPY VARCHAR2
1132 ) IS
1133 BEGIN
1134
1135 SELECT
1136 NVL(geography_id, FND_API.G_MISS_NUM),
1137 NVL(geography_type, FND_API.G_MISS_CHAR),
1138 NVL(geography_name, FND_API.G_MISS_CHAR),
1139 NVL(geography_use, FND_API.G_MISS_CHAR),
1140 NVL(geography_code, FND_API.G_MISS_CHAR),
1141 NVL(start_date, FND_API.G_MISS_DATE),
1142 NVL(end_date, FND_API.G_MISS_DATE),
1143 NVL(multiple_parent_flag, FND_API.G_MISS_CHAR),
1144 NVL(created_by_module, FND_API.G_MISS_CHAR),
1145 NVL(country_code, FND_API.G_MISS_CHAR),
1146 NVL(geography_element1, FND_API.G_MISS_CHAR),
1147 NVL(geography_element1_id, FND_API.G_MISS_NUM),
1148 NVL(geography_element1_code, FND_API.G_MISS_CHAR),
1149 NVL(geography_element2, FND_API.G_MISS_CHAR),
1150 NVL(geography_element2_id, FND_API.G_MISS_NUM),
1151 NVL(geography_element2_code, FND_API.G_MISS_CHAR),
1152 NVL(geography_element3, FND_API.G_MISS_CHAR),
1153 NVL(geography_element3_id, FND_API.G_MISS_NUM),
1154 NVL(geography_element3_code, FND_API.G_MISS_CHAR),
1155 NVL(geography_element4, FND_API.G_MISS_CHAR),
1156 NVL(geography_element4_id, FND_API.G_MISS_NUM),
1157 NVL(geography_element4_code, FND_API.G_MISS_CHAR),
1158 NVL(geography_element5, FND_API.G_MISS_CHAR),
1159 NVL(geography_element5_id, FND_API.G_MISS_NUM),
1160 NVL(geography_element5_code, FND_API.G_MISS_CHAR),
1161 NVL(geography_element6, FND_API.G_MISS_CHAR),
1162 NVL(geography_element6_id, FND_API.G_MISS_NUM),
1163 NVL(geography_element7, FND_API.G_MISS_CHAR),
1164 NVL(geography_element7_id, FND_API.G_MISS_NUM),
1165 NVL(geography_element8, FND_API.G_MISS_CHAR),
1166 NVL(geography_element8_id, FND_API.G_MISS_NUM),
1167 NVL(geography_element9, FND_API.G_MISS_CHAR),
1168 NVL(geography_element9_id, FND_API.G_MISS_NUM),
1169 NVL(geography_element10, FND_API.G_MISS_CHAR),
1170 NVL(geography_element10_id, FND_API.G_MISS_NUM),
1171 geometry,
1172 NVL(timezone_code, FND_API.G_MISS_CHAR),
1173 NVL(application_id, FND_API.G_MISS_NUM),
1174 NVL(program_login_id, FND_API.G_MISS_NUM),
1175 NVL(attribute_category, FND_API.G_MISS_CHAR),
1176 NVL(attribute1, FND_API.G_MISS_CHAR),
1177 NVL(attribute2, FND_API.G_MISS_CHAR),
1178 NVL(attribute3, FND_API.G_MISS_CHAR),
1179 NVL(attribute4, FND_API.G_MISS_CHAR),
1180 NVL(attribute5, FND_API.G_MISS_CHAR),
1181 NVL(attribute6, FND_API.G_MISS_CHAR),
1182 NVL(attribute7, FND_API.G_MISS_CHAR),
1183 NVL(attribute8, FND_API.G_MISS_CHAR),
1184 NVL(attribute9, FND_API.G_MISS_CHAR),
1185 NVL(attribute10, FND_API.G_MISS_CHAR),
1186 NVL(attribute11, FND_API.G_MISS_CHAR),
1187 NVL(attribute12, FND_API.G_MISS_CHAR),
1188 NVL(attribute13, FND_API.G_MISS_CHAR),
1189 NVL(attribute14, FND_API.G_MISS_CHAR),
1190 NVL(attribute15, FND_API.G_MISS_CHAR),
1191 NVL(attribute16, FND_API.G_MISS_CHAR),
1192 NVL(attribute17, FND_API.G_MISS_CHAR),
1193 NVL(attribute18, FND_API.G_MISS_CHAR),
1194 NVL(attribute19, FND_API.G_MISS_CHAR),
1195 NVL(attribute20, FND_API.G_MISS_CHAR)
1196 INTO
1197 x_geography_id,
1201 x_geography_code,
1198 x_geography_type,
1199 x_geography_name,
1200 x_geography_use,
1202 x_start_date,
1203 x_end_date,
1204 x_multiple_parent_flag,
1205 x_created_by_module,
1206 x_country_code,
1207 x_geography_element1,
1208 x_geography_element1_id,
1209 x_geography_element1_code,
1210 x_geography_element2,
1211 x_geography_element2_id,
1212 x_geography_element2_code,
1213 x_geography_element3,
1214 x_geography_element3_id,
1215 x_geography_element3_code,
1216 x_geography_element4,
1217 x_geography_element4_id,
1218 x_geography_element4_code,
1219 x_geography_element5,
1220 x_geography_element5_id,
1221 x_geography_element5_code,
1222 x_geography_element6,
1223 x_geography_element6_id,
1224 x_geography_element7,
1225 x_geography_element7_id,
1226 x_geography_element8,
1227 x_geography_element8_id,
1228 x_geography_element9,
1229 x_geography_element9_id,
1230 x_geography_element10,
1231 x_geography_element10_id,
1232 x_geometry,
1236 x_attribute_category,
1233 x_timezone_code,
1234 x_application_id,
1235 x_program_login_id,
1237 x_attribute1,
1238 x_attribute2,
1239 x_attribute3,
1240 x_attribute4,
1241 x_attribute5,
1242 x_attribute6,
1243 x_attribute7,
1244 x_attribute8,
1245 x_attribute9,
1246 x_attribute10,
1247 x_attribute11,
1248 x_attribute12,
1249 x_attribute13,
1250 x_attribute14,
1251 x_attribute15,
1252 x_attribute16,
1253 x_attribute17,
1254 x_attribute18,
1255 x_attribute19,
1256 x_attribute20
1257 FROM HZ_GEOGRAPHIES
1258 WHERE geography_id = x_geography_id;
1259
1260 EXCEPTION
1261 WHEN NO_DATA_FOUND THEN
1262 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
1263 FND_MESSAGE.SET_TOKEN('RECORD', 'hz_geography_rec');
1264 FND_MESSAGE.SET_TOKEN('VALUE', TO_CHAR(x_geography_id));
1265 FND_MSG_PUB.ADD;
1266 RAISE FND_API.G_EXC_ERROR;
1267
1268 END Select_Row;
1269
1270 PROCEDURE Delete_Row (
1271 x_geography_id IN NUMBER
1272 ) IS
1273 BEGIN
1274
1275 DELETE FROM HZ_GEOGRAPHIES
1276 WHERE geography_id = x_geography_id;
1277
1278 IF ( SQL%NOTFOUND ) THEN
1279 RAISE NO_DATA_FOUND;
1280 END IF;
1281
1282 END Delete_Row;
1283
1284 /*
1285 Procedure update_geo_element_cp is concurrent program 'Update Geography Element Columns'
1286 (ARHGEOEU) which will update the HZ_GEOGRAPHIES name/code value for updated identifier
1287 values (if primary flag is Y).
1288 Created By Nishant Singhai 25-Oct-2005 For Bug 4578867
1289 */
1290 PROCEDURE update_geo_element_cp (
1291 errbuf OUT NOCOPY VARCHAR2,
1292 retcode OUT NOCOPY VARCHAR2,
1293 p_geography_id IN NUMBER,
1294 p_identifier_type IN VARCHAR2,
1295 p_identifier_value IN VARCHAR2
1296 ) IS
1297
1298 l_old_geo_name VARCHAR2(360);
1299 l_old_geo_code VARCHAR2(360);
1300 l_geography_type VARCHAR2(30);
1301 l_country_code VARCHAR2(30);
1302 l_err_message VARCHAR2(500);
1303 l_identifier_value VARCHAR2(360);
1304 l_geo_element_col VARCHAR2(60);
1305 l_geo_element_code VARCHAR2(60);
1306 l_geo_element_id VARCHAR2(60);
1307
1308 CURSOR c_get_geo_details (l_geo_id IN NUMBER) IS
1309 SELECT geography_name, geography_code, geography_type, country_code
1310 FROM hz_geographies
1311 WHERE geography_id = l_geo_id
1312 AND geography_use = 'MASTER_REF'
1313 ;
1314 BEGIN
1315 retcode := 0;
1316
1317 IF (p_identifier_type = 'CODE') THEN
1318 l_identifier_value := UPPER(p_identifier_value);
1319 ELSE
1320 l_identifier_value := p_identifier_value;
1321 END IF;
1322
1323 OPEN c_get_geo_details (p_geography_id);
1324 FETCH c_get_geo_details INTO l_old_geo_name, l_old_geo_code, l_geography_type, l_country_code;
1325 CLOSE c_get_geo_details;
1326
1327 IF (l_geography_type <> 'COUNTRY') THEN
1328 BEGIN
1329 --get geography_element_column from hz_geo_structure_levels for this geography_id
1330 SELECT geography_element_column
1331 INTO l_geo_element_col
1332 FROM HZ_GEO_STRUCTURE_LEVELS
1333 WHERE country_code = l_country_code
1334 AND geography_type = l_geography_type
1335 AND ROWNUM < 2;
1336
1337 l_geo_element_code := l_geo_element_col||'_CODE';
1338 l_geo_element_id := l_geo_element_col||'_ID';
1339 EXCEPTION
1340 WHEN NO_DATA_FOUND THEN
1341 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
1342 fnd_message.set_token('COLUMN', 'geography_element_column,country_code');
1343 l_err_message := FND_MESSAGE.GET;
1344 RAISE FND_API.G_EXC_ERROR;
1345 END;
1346
1347 ELSIF l_geography_type = 'COUNTRY' THEN
1348 l_geo_element_col:= 'GEOGRAPHY_ELEMENT1';
1349 l_geo_element_code := 'GEOGRAPHY_ELEMENT1_CODE';
1350 l_geo_element_id := 'GEOGRAPHY_ELEMENT1_ID';
1351 END IF;
1352
1353 -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME' and 'CODE'
1354 -- for this geography_id
1355
1356 IF p_identifier_type = 'CODE' THEN
1357 UPDATE HZ_GEOGRAPHIES
1358 SET geography_code = l_identifier_value
1359 WHERE geography_id = p_geography_id;
1360 END IF;
1361
1362 IF p_identifier_type = 'NAME' THEN
1363 UPDATE HZ_GEOGRAPHIES
1364 SET geography_name = l_identifier_value
1365 WHERE geography_id = p_geography_id;
1366 END IF;
1367
1368 -- denormalize the primary identifier in HZ_GEOGRAPHIES for identifier_type='NAME'/'CODE' in all the rows
1369 -- where this geography_id is de-normalized as a parent
1370
1371 IF (p_identifier_type='NAME' AND l_geo_element_col IS NOT NULL)THEN
1372
1373 EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_col||'= :l_identifier_value '||
1374 ' WHERE country_code = :l_country_code and '||
1375 l_geo_element_id||'= :l_geography_id '
1376 USING l_identifier_value, l_country_code, p_geography_id ;
1377
1378 END IF;
1379
1380 IF p_identifier_type='CODE' THEN
1381 IF l_geo_element_col IN ('GEOGRAPHY_ELEMENT1','GEOGRAPHY_ELEMENT2','GEOGRAPHY_ELEMENT3','GEOGRAPHY_ELEMENT4','GEOGRAPHY_ELEMENT5') THEN
1382 EXECUTE IMMEDIATE 'UPDATE HZ_GEOGRAPHIES SET '||l_geo_element_code||'= :l_identifier_value '||
1383 ' WHERE country_code = :l_country_code and '||
1384 l_geo_element_id||'= :l_geography_id '
1388 END IF;
1385 USING l_identifier_value, l_country_code, p_geography_id;
1386
1387 END IF;
1389
1390 EXCEPTION
1391 WHEN FND_API.G_EXC_ERROR THEN
1392 fnd_file.put_line(fnd_file.log,l_err_message);
1393 retcode := '2'; -- Error
1394 errbuf := l_err_message;
1395 WHEN OTHERS THEN
1396 fnd_file.put_line(fnd_file.log,SQLERRM);
1397 retcode := '2'; -- Error
1398 errbuf := SQLERRM;
1399 END update_geo_element_cp;
1400
1401 END HZ_GEOGRAPHIES_PKG;