[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_STRUCTURE_PUB
Source
1 PACKAGE BODY HZ_GEOGRAPHY_STRUCTURE_PUB AS
2 /*$Header: ARHGSTSB.pls 120.21 2006/03/23 15:03:02 idali noship $ */
3
4 -- Type declarations
5
6 ------------------------------------
7 -- declaration of private procedures
8 ------------------------------------
9
10 PROCEDURE do_create_geography_type(
11 p_geography_type_rec IN GEOGRAPHY_TYPE_REC_TYPE,
12 x_return_status IN OUT NOCOPY VARCHAR2
13 );
14
15 PROCEDURE do_create_geo_rel_type(
16 p_geo_rel_type_rec IN GEO_REL_TYPE_REC_TYPE,
17 x_relationship_type_id OUT NOCOPY NUMBER,
18 x_return_status IN OUT NOCOPY VARCHAR2
19 );
20
21 PROCEDURE do_update_geo_rel_type(
22 p_relationship_type_id IN NUMBER,
23 p_status IN VARCHAR2,
24 p_object_version_number IN OUT NOCOPY NUMBER,
25 x_return_status IN OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE do_create_geo_structure(
29 p_geo_structure_rec IN GEO_STRUCTURE_REC_TYPE,
30 x_return_status IN OUT NOCOPY VARCHAR2
31 );
32
33 PROCEDURE validate_geo_element_col(
34 p_geography_type IN VARCHAR2,
35 p_geography_id IN NUMBER,
36 p_geo_element_column IN VARCHAR2,
37 x_return_status IN OUT NOCOPY VARCHAR2
38 );
39
40 /*
41 PROCEDURE update_geo_rel_type(
42 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
43 p_relationship_type_id IN NUMBER,
44 p_status IN VARCHAR2,
45 p_object_version_number IN OUT NOCOPY NUMBER,
46 x_return_status OUT NOCOPY VARCHAR2,
47 x_msg_count OUT NOCOPY NUMBER,
48 x_msg_data OUT NOCOPY VARCHAR2
49 );
50 */
51
52 -------------------------------
53 -- body of private procedures
54 -------------------------------
55
56 -- check if the geography_type has multiple parents and geography_type has same value for
57 -- geography_column_element for all these rows.
58 PROCEDURE validate_geo_element_col(
59 p_geography_type IN VARCHAR2,
60 p_geography_id IN NUMBER,
61 p_geo_element_column IN VARCHAR2,
62 x_return_status IN OUT NOCOPY VARCHAR2
63 )IS
64
65 CURSOR c_geo_parent IS
66 SELECT parent_geography_type,geography_element_column
67 FROM HZ_GEO_STRUCTURE_LEVELS
68 WHERE geography_type = p_geography_type
69 AND geography_id = p_geography_id;
70
71 l_geo_parent c_geo_parent%ROWTYPE;
72
73 BEGIN
74
75 OPEN c_geo_parent;
76 LOOP
77 FETCH c_geo_parent INTO l_geo_parent;
78 EXIT WHEN c_geo_parent%NOTFOUND;
79 IF l_geo_parent.geography_element_column <> p_geo_element_column
80 THEN
81 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_MULTIPLE_GEO_COL');
82 FND_MESSAGE.SET_TOKEN('GEO_ID', p_geography_id);
83 FND_MESSAGE.SET_TOKEN('GEO_TYPE', p_geography_type);
84 FND_MSG_PUB.ADD;
85 x_return_status := fnd_api.g_ret_sts_error;
86 END IF;
87 END LOOP;
88 CLOSE c_geo_parent;
89
90 END validate_geo_element_col;
91
92 PROCEDURE do_create_geography_type(
93 p_geography_type_rec IN GEOGRAPHY_TYPE_REC_TYPE,
94 x_return_status IN OUT NOCOPY VARCHAR2
95 ) IS
96
97 --l_geography_type_rec GEOGRAPHY_TYPE_REC_TYPE := p_geography_type_rec;
98 l_geography_type hz_geography_types_b.GEOGRAPHY_TYPE%TYPE := UPPER(p_geography_type_rec.geography_type);
99 l_count NUMBER;
100 l_rowid VARCHAR2(64);
101 l_object_id NUMBER;
102 --l_dummy VARCHAR2(1);
103 l_instance_set_id NUMBER;
104 l_predicate VARCHAR2(4000);
105 l_geography_type_name VARCHAR2(80);
106
107 BEGIN
108
109 l_geography_type_name := p_geography_type_rec.geography_type_name;
110
111 -- replaced by find_index_name
112 /* -- If primary_key is passed, check for uniqueness
113 IF l_geography_type <> FND_API.G_MISS_CHAR
114 AND
115 l_geography_type IS NOT NULL
116 THEN
117 BEGIN
118
119 -- check for geography type uniqueness
120 SELECT 1
121 INTO l_count
122 FROM hz_geography_types_b
123 WHERE GEOGRAPHY_TYPE = l_geography_type;
124
125 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
126 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_type');
127 FND_MSG_PUB.ADD;
128 RAISE FND_API.G_EXC_ERROR;
129
130 EXCEPTION
131 WHEN NO_DATA_FOUND THEN
132 NULL;
133 END;
134
135 END IF;*/
136
137 IF p_geography_type_rec.geography_type_name IS NULL THEN
138 l_geography_type_name := initcap(p_geography_type_rec.geography_type);
139 END IF;
140
141 -- call the table handler to create geography_type
142 hz_geography_types_PKG.Insert_Row (
143 x_rowid => l_rowid,
144 x_geography_type => UPPER(l_geography_type),
145 x_geography_type_name => l_geography_type_name,
146 x_object_version_number => 1,
147 x_geography_use => 'MASTER_REF',
148 x_postal_code_range_flag => 'N',
149 x_limited_by_geography_id => NULL,
150 x_created_by_module => p_geography_type_rec.created_by_module,
151 x_application_id => p_geography_type_rec.application_id,
152 x_program_login_id => NULL
153 );
154
155
156 -- initialize the variables for creating fnd_object_instance_sets
157
158
159 SELECT object_id into l_object_id
160 FROM FND_OBJECTS
161 WHERE obj_name='HZ_GEOGRAPHIES';
162
163 -- Replace the ' in predicate with ''
164 l_predicate := 'GEOGRAPHY_TYPE='||''''||replace(l_geography_type,'''','''''')||'''';
165
166 BEGIN
167 SELECT count(*)
168 INTO l_count
169 FROM FND_OBJECT_INSTANCE_SETS
170 WHERE INSTANCE_SET_NAME = l_geography_type;
171
172 IF l_count = 0 THEN
173
174 SELECT FND_OBJECT_INSTANCE_SETS_S.nextval INTO l_instance_set_id FROM dual;
175 l_rowid := NULL;
176
177 -- call the table handler to create fnd_object_instance_sets
178 FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
179 X_ROWID => l_rowid,
180 X_INSTANCE_SET_ID => l_instance_set_id,
181 X_INSTANCE_SET_NAME => l_geography_type,
182 X_OBJECT_ID => l_object_id,
183 X_PREDICATE => l_predicate,
184 X_DISPLAY_NAME => l_geography_type,
185 X_DESCRIPTION => l_geography_type,
186 X_CREATION_DATE => HZ_UTILITY_V2PUB.creation_date,
187 X_CREATED_BY => HZ_UTILITY_V2PUB.created_by,
188 X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.last_update_date,
189 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.last_updated_by,
190 X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.last_update_login
191 ) ;
192
193
194 END IF;
195
196 END;
197
198 END do_create_geography_type;
199
200 -- create Geo Relationship Type
201
202 PROCEDURE do_create_geo_rel_type(
203 p_geo_rel_type_rec IN geo_rel_type_REC_TYPE,
204 x_relationship_type_id OUT NOCOPY NUMBER,
205 x_return_status IN OUT NOCOPY VARCHAR2
206 ) IS
207
208 l_relationship_type_rec HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE;
209 l_geography_type hz_geography_types_b.GEOGRAPHY_TYPE%TYPE :=p_geo_rel_type_rec.geography_type;
210 l_parent_geography_type hz_geography_types_b.GEOGRAPHY_TYPE%TYPE :=p_geo_rel_type_rec.parent_geography_type;
211 l_geography_use hz_geography_types_b.GEOGRAPHY_USE%TYPE;
212 l_count number;
213 x_msg_count NUMBER;
214 x_msg_data VARCHAR2(2000);
215
216 BEGIN
217
218 -- validate geography relationship type
219 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geo_rel_type(
220 p_create_update_flag => 'C',
221 p_geo_rel_type_rec => p_geo_rel_type_rec,
222 x_return_status => x_return_status
223 );
224
225
226 --if validation failed at any point, then raise an exception to stop processing
227 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
228 RAISE FND_API.G_EXC_ERROR;
229 END IF;
230 --dbms_output.put_line('After validate in geo_rel_type');
231
232 BEGIN
233 -- derive geography use of parent geography type which will be the relationship type
234 SELECT GEOGRAPHY_USE
235 INTO l_geography_use
236 FROM hz_geography_types_b
237 WHERE GEOGRAPHY_TYPE = l_parent_geography_type;
238
239 EXCEPTION
240 WHEN NO_DATA_FOUND THEN
241 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_VALUE');
242 FND_MESSAGE.SET_TOKEN('VALUE', l_parent_geography_type);
243 FND_MESSAGE.SET_TOKEN('COLUMN', 'parent_geography_type');
244 FND_MSG_PUB.ADD;
245 RAISE FND_API.G_EXC_ERROR;
246 END;
247 -- check if geography_type is already 'PARENT_OF' parent_geography_type (bug fix 2838632)
248 SELECT count(*) INTO l_count FROM hz_relationship_types
249 WHERE relationship_type=l_geography_use
250 AND subject_type=l_geography_type
251 AND object_type=l_parent_geography_type
252 AND status = 'A'
253 AND forward_rel_code='PARENT_OF'
254 AND backward_rel_code = 'CHILD_OF';
255 IF l_count > 0 THEN
256 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_INVALID_REL_TYPE');
257 FND_MESSAGE.SET_TOKEN('CHILD', l_geography_type);
258 FND_MESSAGE.SET_TOKEN('PARENT', l_parent_geography_type);
259 FND_MSG_PUB.ADD;
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262
263 --l_relationship_type_rec.relationship_type_id :=NULL;
264 l_relationship_type_rec.relationship_type :=l_geography_use;
265 l_relationship_type_rec.forward_rel_code :='PARENT_OF';
266 l_relationship_type_rec.backward_rel_code :='CHILD_OF';
267 l_relationship_type_rec.direction_code :='P';
268
269 IF l_geography_use = 'MASTER_REF' THEN
270 l_relationship_type_rec.hierarchical_flag :='Y';
271 ELSE
272 l_relationship_type_rec.hierarchical_flag :='N';
273 END IF;
274
275 l_relationship_type_rec.create_party_flag :='N';
276 l_relationship_type_rec.allow_relate_to_self_flag :='N';
277 l_relationship_type_rec.allow_circular_relationships :='N';
278 l_relationship_type_rec.subject_type :=l_parent_geography_type;
279 l_relationship_type_rec.object_type :=l_geography_type;
280 l_relationship_type_rec.status :=p_geo_rel_type_rec.status;
281 l_relationship_type_rec.created_by_module :=p_geo_rel_type_rec.created_by_module;
282 l_relationship_type_rec.application_id :=p_geo_rel_type_rec.application_id;
283 l_relationship_type_rec.multiple_parent_allowed :='Y';
284 l_relationship_type_rec.incl_unrelated_entities :=NULL;
285 l_relationship_type_rec.forward_role :=NULL;
286 l_relationship_type_rec.backward_role :=NULL;
287
288
289 HZ_RELATIONSHIP_TYPE_V2PUB.create_relationship_type (
290 p_init_msg_list => 'F',
291 p_relationship_type_rec => l_relationship_type_rec,
292 x_relationship_type_id => x_relationship_type_id,
293 x_return_status => x_return_status,
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data
296 );
297
298
299 --if validation failed at any point, then raise an exception to stop processing
300 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 END do_create_geo_rel_type;
305
306 -- update geography Relationship Type
307 PROCEDURE do_update_geo_rel_type(
308 p_relationship_type_id IN NUMBER,
309 p_status IN VARCHAR2,
310 p_object_version_number IN OUT NOCOPY NUMBER,
311 x_return_status IN OUT NOCOPY VARCHAR2
312 ) IS
313
314 l_relationship_type_rec HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE;
315 --l_relationship_type_id hz_geography_types_b.GEOGRAPHY_TYPE%TYPE :=p_relationship_type_id;
316 --x_return_status VARCHAR2(2000);
317 x_msg_count NUMBER;
318 x_msg_data VARCHAR2(2000);
319 l_count NUMBER;
320
321 BEGIN
322
323 -- validate relationship_type_id
324 BEGIN
325 select 1 into l_count from
326 hz_relationship_types
327 where relationship_type_id=p_relationship_type_id;
328 EXCEPTION WHEN NO_DATA_FOUND THEN
329 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
330 FND_MESSAGE.SET_TOKEN('TOKEN1', 'Relationship Type');
331 FND_MESSAGE.SET_TOKEN('TOKEN2', 'relationship_type_id '||p_relationship_type_id);
332 FND_MSG_PUB.ADD;
333 RAISE FND_API.G_EXC_ERROR;
334 END;
335
336 hz_utility_v2pub.validate_mandatory(
337 p_create_update_flag => 'U',
338 p_column => 'object_version_number',
339 p_column_value => p_object_version_number,
340 x_return_status => x_return_status
341 );
342
343 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
344 RAISE FND_API.G_EXC_ERROR;
345 END IF;
346
347 --construct relationship type rec for update
348
349 l_relationship_type_rec.relationship_type_id := p_relationship_type_id;
350 l_relationship_type_rec.status := p_status;
351 l_relationship_type_rec.subject_type := NULL;
352 l_relationship_type_rec.object_type := NULL;
353 l_relationship_type_rec.created_by_module := NULL;
354 l_relationship_type_rec.application_id := NULL;
355
356 -- update geography relationship type
357 HZ_RELATIONSHIP_TYPE_V2PUB.update_relationship_type(
358 p_init_msg_list =>'F',
359 p_relationship_type_rec =>l_relationship_type_rec,
360 p_object_version_number =>p_object_version_number,
361 x_return_status =>x_return_status,
362 x_msg_count =>x_msg_count,
363 x_msg_data =>x_msg_data
364 );
365
366 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
367 RAISE FND_API.G_EXC_ERROR;
371
368 END IF;
369
370 END do_update_geo_rel_type;
372
373 -- create Geography Structure
374 PROCEDURE do_create_geo_structure(
375 p_geo_structure_rec IN GEO_STRUCTURE_REC_TYPE,
376 x_return_status IN OUT NOCOPY VARCHAR2
377 )IS
378
379 l_relationship_type_id NUMBER;
380 l_object_version_number NUMBER := 1;
381 l_rowid VARCHAR2(64);
382 l_country_code VARCHAR2(30);
383 l_geo_rel_type_rec GEO_REL_TYPE_REC_TYPE;
384 x_relationship_type_id NUMBER;
385 x_msg_count NUMBER;
386 x_msg_data VARCHAR2(2000);
387 l_count NUMBER;
388 l_status VARCHAR2(1);
389 l_geo_element_col VARCHAR2(30);
390
391 BEGIN
392
393 -- validate geography structure record
394 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geo_structure(
395 p_create_update_flag => 'C',
396 p_geo_structure_rec => p_geo_structure_rec,
397 x_return_status => x_return_status
398 );
399
400 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
401 RAISE FND_API.G_EXC_ERROR;
402 END IF;
403
404 --dbms_output.put_line('After validation '|| x_return_status);
405
406 -- get country code
407 -- SELECT geography_code
408 -- Bug 4591502 : ISSUE # 15
409 -- Use country_code and not geography_code
410 SELECT country_code
411 INTO l_country_code
412 FROM HZ_GEOGRAPHIES
413 WHERE geography_id = p_geo_structure_rec.geography_id;
414
415 ----dbms_output.put_line('Country_code is '||l_country_code);
416
417 IF l_country_code IS NULL THEN
418 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
419 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'country_code' );
420 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'geography_id '||p_geo_structure_rec.geography_id );
421 FND_MSG_PUB.ADD;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF;
424
425 -- see whether this geography_element_column to be created is already being used with in this country
426 -- for another geography_type. if yes, do not create
427
428 /* commented the validation per bug :2911108
429 SELECT count(*) INTO l_count FROM hz_geo_structure_levels
430 WHERE geography_element_column = p_geo_structure_rec.geography_element_column
431 AND geography_type <> p_geo_structure_rec.geography_type
432 AND geography_id = p_geo_structure_rec.geography_id
433 AND rownum < 2;
434
435 IF l_count > 0 THEN
436 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
437 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'geography_element_column');
438 FND_MSG_PUB.ADD;
439 RAISE FND_API.G_EXC_ERROR;
440 END IF;*/
441
442 BEGIN
443
444 SELECT geography_element_column into l_geo_element_col FROM hz_geo_structure_levels
445 WHERE geography_id=p_geo_structure_rec.geography_id
446 AND geography_type=p_geo_structure_rec.geography_type
447 AND rownum < 2;
448
449 EXCEPTION WHEN no_data_found THEN
450 BEGIN
451 SELECT 'GEOGRAPHY_ELEMENT'||NVL(max(substr(geography_element_column,18))+1,2) geo_element_col
452 into l_geo_element_col
453 FROM hz_geo_structure_levels
454 WHERE geography_id = p_geo_structure_rec.geography_id;
455
456 EXCEPTION WHEN no_data_found THEN
457 l_geo_element_col:='GEOGRAPHY_ELEMENT2';
458 END;
459 END;
460
461 BEGIN
462
463 -- Get the relationship_type_id if there exists one for this geography_type and parent_geography_type
464 SELECT relationship_type_id,object_version_number,status
465 INTO l_relationship_type_id,l_object_version_number,l_status
466 FROM HZ_RELATIONSHIP_TYPES
467 WHERE subject_type = p_geo_structure_rec.parent_geography_type
468 AND object_type= p_geo_structure_rec.geography_type
469 AND forward_rel_code = 'PARENT_OF'
470 AND backward_rel_code = 'CHILD_OF'
471 AND relationship_type='MASTER_REF';
472
473 -- Activate the relationship_type if it was Inactive
474 IF l_status = 'I' THEN
475 update_geo_rel_type(
476 p_init_msg_list => 'F',
477 p_relationship_type_id => l_relationship_type_id,
478 p_status => 'A',
479 p_object_version_number => l_object_version_number,
480 x_return_status => x_return_status,
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data
483 );
484
485 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
486 RAISE FND_API.G_EXC_ERROR;
487 END IF;
488
489 END IF;
490
491 -- validate geo_element_column before inserting the row
492
493 /* commented as per bug : 2911108
494 validate_geo_element_col(
495 p_geography_type => p_geo_structure_rec.geography_type,
496 p_geography_id => p_geo_structure_rec.geography_id,
497 p_geo_element_column => p_geo_structure_rec.geography_element_column,
498 x_return_status => x_return_status
499 );
500
501 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;*/
504
505
509 x_geography_id => p_geo_structure_rec.geography_id,
506 -- call table handler to insert a row in HZ_GEO_STRUCTURE_LEVELS
507 HZ_GEO_STRUCTURE_LEVELS_PKG.Insert_Row (
508 x_rowid => l_rowid,
510 x_geography_type => p_geo_structure_rec.geography_type,
511 x_parent_geography_type => p_geo_structure_rec.parent_geography_type,
512 x_object_version_number => 1,
513 x_relationship_type_id => l_relationship_type_id,
514 x_country_code => l_country_code,
515 x_geography_element_column => l_geo_element_col,
516 x_created_by_module => p_geo_structure_rec.created_by_module,
517 x_application_id => p_geo_structure_rec.application_id,
518 x_program_login_id => NULL,
519 x_addr_val_level => p_geo_structure_rec.addr_val_level
520 );
521
522 EXCEPTION
523 WHEN NO_DATA_FOUND THEN
524
525 BEGIN
526
527 -- create a relationship type with this geography type and parent geography type
528
529 -- construct geo_rel_type_rec record
530 l_geo_rel_type_rec.geography_type := p_geo_structure_rec.geography_type;
531 l_geo_rel_type_rec.parent_geography_type := p_geo_structure_rec.parent_geography_type;
532 l_geo_rel_type_rec.status := 'A';
533 l_geo_rel_type_rec.created_by_module := p_geo_structure_rec.created_by_module;
534 l_geo_rel_type_rec.application_id := p_geo_structure_rec.application_id;
535
536 --dbms_output.put_line('Before create_geo_rel_type');
537 -- call create_geo_rel_type
538 create_geo_rel_type(
539 p_init_msg_list => 'F',
540 p_geo_rel_type_rec => l_geo_rel_type_rec,
541 x_relationship_type_id => x_relationship_type_id,
542 x_return_status => x_return_status,
543 x_msg_count => x_msg_count,
544 x_msg_data => x_msg_data
545 );
546
547
548 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
549 RAISE FND_API.G_EXC_ERROR;
550 END IF;
551
552 -- validate geo_element_column before inserting the row
553 /* commented per bug: 2911108
554 validate_geo_element_col(
555 p_geography_type => p_geo_structure_rec.geography_type,
556 p_geography_id => p_geo_structure_rec.geography_id,
557 p_geo_element_column => p_geo_structure_rec.geography_element_column,
558 x_return_status => x_return_status
559 );
560
561 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
562 RAISE FND_API.G_EXC_ERROR;
563 END IF;*/
564
565 -- insert a row in hz_geo_structure_levels with new relationship_type_id
566 HZ_GEO_STRUCTURE_LEVELS_PKG.Insert_Row (
567 x_rowid => l_rowid,
568 x_geography_id => p_geo_structure_rec.geography_id,
569 x_geography_type => p_geo_structure_rec.geography_type,
570 x_parent_geography_type => p_geo_structure_rec.parent_geography_type,
571 x_object_version_number => 1,
572 x_relationship_type_id => x_relationship_type_id,
573 x_country_code => l_country_code,
574 x_geography_element_column => l_geo_element_col,
575 x_created_by_module => p_geo_structure_rec.created_by_module,
576 x_application_id => p_geo_structure_rec.application_id,
577 x_program_login_id => NULL,
578 x_addr_val_level => p_geo_structure_rec.addr_val_level
579 );
580
581 END;
582 END;
583
584 END do_create_geo_structure;
585
586 /* Obsoleting as it is no more needed ( bug 2911108)
587 -- update geography structure
588 PROCEDURE do_update_geo_structure(
589 p_geography_id IN NUMBER,
590 p_geography_type IN VARCHAR2,
591 p_parent_geography_type IN VARCHAR2,
592 p_geography_element_column IN VARCHAR2,
593 p_object_version_number IN OUT NOCOPY NUMBER,
594 x_return_status IN OUT NOCOPY VARCHAR2
595 ) IS
596
597 l_relation_count NUMBER;
598 l_rowid ROWID;
599 l_object_type VARCHAR2(30);
600 l_country_code VARCHAR2(2);
601 l_stmnt VARCHAR2(1000);
602 l_geo_element_col VARCHAR2(30);
603 l_count NUMBER;
604 l_geo_structure_rec GEO_STRUCTURE_REC_TYPE;
605 l_object_version_number NUMBER;
606 CURSOR c_geo_structure_map IS
607 SELECT distinct map.map_id
608 FROM hz_geo_struct_map map, hz_geo_struct_map_dtl dtl
609 WHERE map.country_code = l_country_code
610 AND map.map_id = dtl.map_id;
611 l_geo_structure_map c_geo_structure_map%ROWTYPE;
612
613 BEGIN
614
615 l_geo_structure_rec.geography_id := p_geography_id;
616 l_geo_structure_rec.geography_type := p_geography_type;
617 l_geo_structure_rec.parent_geography_type := p_parent_geography_type;
618 l_geo_structure_rec.geography_element_column := p_geography_element_column;
619
620 --dbms_output.put_line('before validate');
621
625 p_geo_structure_rec => l_geo_structure_rec,
622 -- validate record for update
623 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geo_structure(
624 p_create_update_flag => 'U',
626 x_return_status => x_return_status
627 );
628
629 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
630 RAISE FND_API.G_EXC_ERROR;
631 END IF;
632
633 hz_utility_v2pub.validate_mandatory(
634 p_create_update_flag => 'U',
635 p_column => 'object_version_number',
636 p_column_value => p_object_version_number,
637 x_return_status => x_return_status
638 );
639
640 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 --dbms_output.put_line('after validate');
645
646 -- see whether this geography_element_column to be updated is already being used with in this country for
647 -- another geography_type . if yes, do not update
648
649 SELECT count(*) INTO l_count FROM hz_geo_structure_levels
650 WHERE geography_element_column = p_geography_element_column
651 AND geography_type <> p_geography_type
652 AND geography_id = p_geography_id
653 AND rownum < 2;
654
655 IF l_count > 0 THEN
656 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DUPLICATE_COLUMN' );
657 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'geography_element_column for this geography_id' );
658 FND_MSG_PUB.ADD;
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 BEGIN
663 SELECT country_code,geography_element_column,rowid,object_version_number
664 INTO l_country_code,l_geo_element_col,l_rowid,l_object_version_number
665 FROM hz_geo_structure_levels
666 WHERE geography_id = p_geography_id
667 AND geography_type = p_geography_type
668 AND parent_geography_type = p_parent_geography_type;
669
670 IF p_object_version_number <> l_object_version_number THEN
671 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
672 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geo_structure_levels');
673 FND_MSG_PUB.ADD;
674 RAISE FND_API.G_EXC_ERROR;
675 ELSE
676 p_object_version_number := l_object_version_number+1;
677 END IF;
678 EXCEPTION WHEN NO_DATA_FOUND THEN
679 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
680 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'country_code , geography_element_column' );
681 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'geography_id '||p_geography_id||', geography_type '||p_geography_type||', parent_geography_type '||p_parent_geography_type );
682 FND_MSG_PUB.ADD;
683 RAISE FND_API.G_EXC_ERROR;
684 END;
685
686 BEGIN
687 --dbms_output.put_line('l_geography_element '||l_geo_element_col);
688 --dbms_output.put_line('l_country_code '||''''||l_country_code||'''');
689 --dbms_output.put_line('before execute');
690 --see if there exists any geography in hz_geographies where this geography_element_column being update
691 -- is denormalized. If yes, do not update.
692
693 BEGIN
694
695 /* EXECUTE IMMEDIATE 'SELECT 1 FROM hz_geographies WHERE '||l_geo_element_col||' IS NOT NULL AND country_code='||''''||l_country_code||''''||
696 ' AND rownum <2'; */
697
698 /* SELECT 1 into l_count from
699 hz_relationships hrl
700 WHERE
701 hrl.relationship_type='MASTER_REF'
702 AND hrl.subject_type= p_parent_geography_type
703 AND hrl.object_type=p_geography_type
704 AND hrl.relationship_code='PARENT_OF'
705 AND hrl.status = 'A'
706 AND hrl.subject_id in ( SELECT geography_id from hz_geographies where
707 country_code = l_country_code )
708 AND rownum <2;
709 --dbms_output.put_line('after execute');
710 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_ELEMENT_NONUPDATEABLE' );
711 --FND_MESSAGE.SET_TOKEN( 'COLUMN', 'geography_element_column' );
712 FND_MSG_PUB.ADD;
713 --x_return_status := FND_API.G_RET_STS_ERROR;
714 RAISE FND_API.G_EXC_ERROR;
715
716 --dbms_output.put_line('l_count is '|| to_char(l_count));
717 EXCEPTION WHEN NO_DATA_FOUND THEN
718 --dbms_output.put_line('before update');
719 --call table handler to update row
720 HZ_GEO_STRUCTURE_LEVELS_PKG.Update_Row (
721 x_rowid => l_rowid,
722 x_geography_id => p_geography_id,
723 x_geography_type => p_geography_type,
724 x_parent_geography_type => p_parent_geography_type,
725 x_object_version_number => p_object_version_number,
726 x_relationship_type_id => NULL,
727 x_country_code => NULL,
728 x_geography_element_column => p_geography_element_column,
729 x_created_by_module => NULL,
730 x_application_id => NULL,
731 x_program_login_id => NULL
732 );
733 end;
734
735 -- update hz_geo_structure_map table
736 OPEN c_geo_structure_map;
737 LOOP
738 FETCH c_geo_structure_map INTO l_geo_structure_map;
739 EXIT WHEN c_geo_structure_map%NOTFOUND;
740 UPDATE hz_geo_struct_map_dtl
741 SET geo_element_col=p_geography_element_column
742 WHERE geography_type= p_geography_type
746 END;
743 AND map_id = l_geo_structure_map.map_id;
744 END LOOP;
745 CLOSE c_geo_structure_map;
747
748 END do_update_geo_structure;
749 */
750
751
752 --delete geography structure
753 PROCEDURE do_delete_geo_structure(
754 p_geography_id IN NUMBER,
755 p_geography_type IN VARCHAR2,
756 p_parent_geography_type IN VARCHAR2,
757 x_return_status IN OUT NOCOPY VARCHAR2
758 )IS
759
760 l_column VARCHAR2(30);
761 l_error BOOLEAN := FALSE;
762 l_relationship_type_id NUMBER;
763 l_country_code VARCHAR2(2);
764 l_geo_rel_type_rec GEO_REL_TYPE_REC_TYPE;
765 x_msg_count NUMBER;
766 x_msg_data VARCHAR2(2000);
767 l_count NUMBER;
768 l_object_version_number NUMBER;
769 l_child_geography_type VARCHAR2(30);
770 l_new_relationship_type_id NUMBER;
771 l_status VARCHAR2(1);
772 l_map_id NUMBER;
773 l_geography_id NUMBER;
774 l_usage_id NUMBER;
775 l_location_id NUMBER;
776 l_loc_tbl_name VARCHAR2(50);
777 l_zone_type VARCHAR2(50);
778 l_zone_id NUMBER;
779 l_start_date VARCHAR2(30);
780 l_master_ref_geography_id NUMBER;
781 l_geography_from VARCHAR2(30);
782 l_location_table_name VARCHAR2(30);
783 l_country VARCHAR2(2);
784 l_address_style VARCHAR2(30);
785 l_geo_struct_map_dtl_tbl HZ_GEO_STRUCT_MAP_PUB.geo_struct_map_dtl_tbl_type;
786
787
788 CURSOR c_get_geo_map is
789 Select map_id
790 from hz_geo_struct_map
791 where country_code = l_country_code;
792
793 CURSOR c_get_addr_usg is
794 SELECT usage_id
795 FROM hz_address_usages
796 WHERE map_id = l_map_id;
797
798 CURSOR c_get_geographies is
799 SELECT geography_id
800 FROM hz_geographies
801 WHERE geography_type = p_geography_type
802 AND country_code = l_country_code;
803
804 CURSOR c_get_geo_name_ref is
805 SELECT location_id,Location_table_name
806 FROM hz_geo_name_references
807 WHERE geography_id = l_geography_id;
808
809 CURSOR c_get_ranges is
810 SELECT geography_id,master_ref_geography_id,geography_from,
811 start_date
812 FROM hz_geography_ranges hgr
813 WHERE (SELECT country_code
814 FROM hz_geographies hg
815 WHERE hg.geography_id = hgr.master_ref_geography_id ) = l_country_code;
816
817 CURSOR c_get_zone_types IS
818 SELECT geography_type
819 FROM hz_geography_types_b
820 WHERE limited_by_geography_id = l_geography_id
821 AND geography_use = 'TAX';
822
823 BEGIN
824
825
826 -- check for mandatory columns
827
828 IF p_geography_id IS NULL THEN
829 l_error := TRUE;
830 l_column := 'geography_id';
831 ELSIF p_geography_type IS NULL THEN
832 l_error := TRUE;
833 l_column := 'geography_type';
834 ELSIF p_parent_geography_type IS NULL THEN
835 l_error := TRUE;
836 l_column := 'parent_geography_type';
837 END IF;
838
839 IF l_error = TRUE THEN
840 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
841 FND_MESSAGE.SET_TOKEN( 'COLUMN', l_column );
842 FND_MSG_PUB.ADD;
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845
846
847 -- get the relationship_type_id for this row
848 BEGIN
849
850 SELECT relationship_type_id,country_code INTO l_relationship_type_id,l_country_code
851 FROM hz_geo_structure_levels
852 WHERE geography_id = p_geography_id
853 AND geography_type = p_geography_type
854 AND parent_geography_type = p_parent_geography_type;
855
856 EXCEPTION WHEN NO_DATA_FOUND THEN
857 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_NO_RECORD' );
858 FND_MESSAGE.SET_TOKEN( 'TOKEN1', 'relationship_type_id,country_code' );
859 FND_MESSAGE.SET_TOKEN( 'TOKEN2', 'geography_id '||p_geography_id||', geography_type '||p_geography_type||', parent_geography_type '||p_parent_geography_type );
860 FND_MSG_PUB.ADD;
861 RAISE FND_API.G_EXC_ERROR;
862 END;
863 -- code added for bug 4730508
864 BEGIN
865
866 SELECT geography_type into l_child_geography_type
867 FROM hz_geo_structure_levels
868 WHERE geography_id = p_geography_id
869 AND parent_geography_type = p_geography_type;
870
871 BEGIN
872 -- Get the relationship_type_id if there exists one for this geography_type and parent_geography_type
873 SELECT relationship_type_id,object_version_number,status
874 INTO l_new_relationship_type_id,l_object_version_number,l_status
875 FROM HZ_RELATIONSHIP_TYPES
876 WHERE subject_type = p_parent_geography_type
877 AND object_type= l_child_geography_type
878 AND forward_rel_code = 'PARENT_OF'
879 AND backward_rel_code = 'CHILD_OF'
880 AND relationship_type='MASTER_REF';
881
882 -- Activate the relationship_type if it was Inactive
883 IF l_status = 'I' THEN
884 update_geo_rel_type(
885 p_init_msg_list => 'F',
886 p_relationship_type_id => l_new_relationship_type_id,
887 p_status => 'A',
888 p_object_version_number => l_object_version_number,
889 x_return_status => x_return_status,
890 x_msg_count => x_msg_count,
894 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
891 x_msg_data => x_msg_data
892 );
893
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897
898 END IF;
899
900 -- Bug 4543926 : update parent_geography_type for a child level
901 -- before deleting a level to its parent_geography_type
902 UPDATE hz_geo_structure_levels
903 set parent_geography_type = p_parent_geography_type,
904 relationship_type_id = l_new_relationship_type_id
905 where geography_id = p_geography_id
906 and parent_geography_type = p_geography_type;
907
908
909 EXCEPTION
910 WHEN NO_DATA_FOUND THEN
911
912 BEGIN
913
914 -- create a relationship type with this geography type and parent geography type
915
916 -- construct geo_rel_type_rec record
917 l_geo_rel_type_rec.geography_type := l_child_geography_type;
918 l_geo_rel_type_rec.parent_geography_type := p_parent_geography_type;
919 l_geo_rel_type_rec.status := 'A';
920 l_geo_rel_type_rec.created_by_module := 'HZ_GEO_HIERARCHY';
921 l_geo_rel_type_rec.application_id := 222;
922
923 --dbms_output.put_line('Before create_geo_rel_type');
924 -- call create_geo_rel_type
925 create_geo_rel_type(
926 p_init_msg_list => 'F',
927 p_geo_rel_type_rec => l_geo_rel_type_rec,
928 x_relationship_type_id => l_new_relationship_type_id,
929 x_return_status => x_return_status,
930 x_msg_count => x_msg_count,
931 x_msg_data => x_msg_data
932 );
933
934
935 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
936 RAISE FND_API.G_EXC_ERROR;
937 END IF;
938
939 -- Bug 4543926 : update parent_geography_type for a child level
940 -- before deleting a level to its parent_geography_type
941 UPDATE hz_geo_structure_levels
942 set parent_geography_type = p_parent_geography_type,
943 relationship_type_id = l_new_relationship_type_id
944 where geography_id = p_geography_id
945 and parent_geography_type = p_geography_type;
946
947 END;
948 END;
949 EXCEPTION
950 WHEN NO_DATA_FOUND THEN
951 null;
952 END;
953
954
955 -- call table handler to delete the row
956 HZ_GEO_STRUCTURE_LEVELS_PKG.Delete_Row (
957 x_geography_id => p_geography_id,
958 x_geography_type => p_geography_type,
959 x_parent_geography_type => p_parent_geography_type
960 );
961
962 -- disable the relationship_type if it is not used by any other structure
963 SELECT count(*) INTO l_count
964 FROM hz_geo_structure_levels
965 WHERE country_code <> l_country_code
966 AND relationship_type_id=l_relationship_type_id
967 AND rownum <3;
968
969 IF l_count = 0 THEN
970 -- call API to disable the relationship_type
971 /* l_geo_rel_type_rec.relationship_type_id := l_relationship_type_id;
972 l_geo_rel_type_rec.status := 'I';
973 l_geo_rel_type_rec.parent_geography_type := NULL;
974 l_geo_rel_type_rec.geography_type := NULL;
975 l_geo_rel_type_rec.created_by_module := NULL;
976 l_geo_rel_type_rec.application_id := NULL;*/
977
978 SELECT object_version_number into l_object_version_number
979 FROM hz_relationship_types
980 WHERE relationship_type_id = l_relationship_type_id;
981
982 update_geo_rel_type(
983 p_init_msg_list => 'F',
984 p_relationship_type_id => l_relationship_type_id,
985 p_status => 'I',
986 p_object_version_number => l_object_version_number,
987 x_return_status => x_return_status,
988 x_msg_count => x_msg_count,
989 x_msg_data => x_msg_data
990 );
991
992 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
993 RAISE FND_API.G_EXC_ERROR;
994 END IF;
995
996 -- disable the backward relationship_type too
997 SELECT object_version_number,relationship_type_id into l_object_version_number,l_relationship_type_id
998 FROM hz_relationship_types
999 WHERE subject_type=p_geography_type
1000 AND object_type=p_parent_geography_type
1001 AND relationship_type='MASTER_REF'
1002 AND forward_rel_code = 'CHILD_OF'
1003 AND backward_rel_code = 'PARENT_OF';
1004
1005 update_geo_rel_type(
1006 p_init_msg_list => 'F',
1007 p_relationship_type_id => l_relationship_type_id,
1008 p_status => 'I',
1009 p_object_version_number => l_object_version_number,
1010 x_return_status => x_return_status,
1011 x_msg_count => x_msg_count,
1012 x_msg_data => x_msg_data
1013 );
1014
1015 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1016 RAISE FND_API.G_EXC_ERROR;
1017 END IF;
1018
1019 END IF;
1020
1021 -- Delete when geography type is checked as included geo type in a tax zone type
1022 delete hz_relationship_types
1026 hz_geography_types_b hgt,hz_geographies hg
1023 where relationship_type = 'TAX'
1024 and ((object_type = p_geography_type
1025 and subject_type in (select hgt.geography_type from
1027 where hgt.limited_by_geography_id = hg.geography_id
1028 and hgt.geography_use = 'TAX'
1029 and hg.country_code = l_country_code ))
1030 or (subject_type = p_geography_type
1031 and object_type in (select hgt.geography_type from
1032 hz_geography_types_b hgt,hz_geographies hg
1033 where hgt.limited_by_geography_id = hg.geography_id
1034 and hgt.geography_use = 'TAX'
1035 and hg.country_code = l_country_code )));
1036
1037 -- Delete geographies
1038 open c_get_geographies;
1039 loop
1040 fetch c_get_geographies into l_geography_id;
1041 exit when c_get_geographies%NOTFOUND;
1042
1043 delete hz_geographies
1044 where geography_id = l_geography_id;
1045
1046 -- Delete geography identifiers
1047 delete hz_geography_identifiers
1048 where geography_id = l_geography_id;
1049
1050 -- delete geo name reference and geo name ref log
1051 open c_get_geo_name_ref;
1052 loop
1053 fetch c_get_geo_name_ref into l_location_id,l_loc_tbl_name;
1054 exit when c_get_geo_name_ref%NOTFOUND;
1055
1056 delete hz_geo_name_references
1057 where location_id = l_location_id
1058 and location_table_name = l_loc_tbl_name;
1059
1060 delete hz_geo_name_reference_log
1061 where location_id = l_location_id
1062 and location_table_name = l_loc_tbl_name;
1063 end loop;
1064 CLOSE c_get_geo_name_ref;
1065 -- if postal_code is deleted from structure delete all ranges record
1066 -- for this country
1067 if p_geography_type = 'POSTAL_CODE' then
1068 open c_get_ranges;
1069 loop
1070 fetch c_get_ranges into l_zone_id,l_master_ref_geography_id,l_geography_from,l_start_date;
1071 exit when c_get_ranges%NOTFOUND;
1072
1073 delete hz_geography_ranges hgr
1074 where geography_id = l_zone_id
1075 and geography_from = l_geography_from
1076 and start_date = l_start_date;
1077
1078 -- delete master_ref_geogrpahy for postal code range
1079 delete hz_relationships
1080 where subject_id = l_zone_id
1081 and object_id = l_master_ref_geography_id
1082 and subject_table_name = 'HZ_GEOGRAPHIES'
1083 and object_table_name = 'HZ_GEOGRAPHIES'
1084 and directional_flag = 'F'
1085 and relationship_type = 'TAX' ;
1086
1087 delete hz_relationships
1088 where subject_id = l_master_ref_geography_id
1089 and object_id = l_zone_id
1090 and subject_table_name = 'HZ_GEOGRAPHIES'
1091 and object_table_name = 'HZ_GEOGRAPHIES'
1092 and directional_flag = 'B'
1093 and relationship_type = 'TAX';
1094
1095 end loop;
1096 CLOSE c_get_ranges;
1097
1098 update hz_geography_types_b hgt
1099 set postal_code_range_flag = 'N'
1100 where geography_use = 'TAX'
1101 and limited_by_geography_id is not null
1102 and (select country_code
1103 from hz_geographies
1104 where geography_id = hgt.limited_by_geography_id)
1105 = l_country_code;
1106 end if;
1107 -- delete geogrpahy ranges if any master_ref_geo is deleted
1108 delete hz_geography_ranges
1109 where master_ref_geography_id = l_geography_id;
1110
1111 -- delete relationships record both for tax and master_ref relationship_type
1112 delete hz_relationships
1113 where (object_id = l_geography_id
1114 or subject_id = l_geography_id )
1115 and subject_table_name = 'HZ_GEOGRAPHIES'
1116 and object_table_name = 'HZ_GEOGRAPHIES'
1117 and (relationship_type = 'TAX'
1118 or relationship_type = 'MASTER_REF');
1119
1120 -- delete hierarchy nodes record for master_ref geos
1121 delete hz_hierarchy_nodes
1122 where (parent_id = l_geography_id
1123 or child_id = l_geography_id)
1124 and parent_table_name = 'HZ_GEOGRAPHIES'
1125 and child_table_name = 'HZ_GEOGRAPHIES'
1126 and hierarchy_type = 'MASTER_REF';
1127
1128 -- delete tax zone type whose limited by geo id is deleted
1129 OPEN c_get_zone_types;
1130 LOOP
1131 FETCH c_get_zone_types INTO l_zone_type;
1132 EXIT WHEN c_get_zone_types%NOTFOUND ;
1133
1134 DELETE hz_geography_types_b
1135 WHERE geography_type = l_zone_type
1136 AND geography_use = 'TAX';
1137
1138 -- delete identifiers and relationships for tax zones to be deleted
1139
1140 DELETE hz_geography_identifiers
1141 WHERE geography_id IN (SELECT geography_id
1142 FROM hz_geographies
1143 WHERE geography_type = l_zone_type
1144 AND geography_use = 'TAX');
1145
1146
1147 DELETE hz_relationships
1148 WHERE subject_id in (SELECT geography_id
1149 FROM hz_geographies
1150 WHERE geography_type = l_zone_type
1151 AND geography_use = 'TAX')
1152 AND subject_type = l_zone_type
1153 AND subject_table_name = 'HZ_GEOGRAPHIES'
1154 AND relationship_type = 'TAX';
1155
1156
1157 DELETE hz_relationships
1158 WHERE object_id in (SELECT geography_id
1159 FROM hz_geographies
1160 WHERE geography_type = l_zone_type
1161 AND geography_use = 'TAX')
1162 AND object_type = l_zone_type
1166 -- delete tax zone assosiated with this tax zone type
1163 AND object_table_name = 'HZ_GEOGRAPHIES'
1164 AND relationship_type = 'TAX';
1165
1167
1168 DELETE hz_geographies
1169 WHERE geography_type = l_zone_type
1170 AND geography_use = 'TAX' ;
1171 END LOOP;
1172 CLOSE c_get_zone_types;
1173 END LOOP;
1174 CLOSE c_get_geographies;
1175
1176
1177 -- Delete mapping and address usages for geography type
1178 open c_get_geo_map;
1179 loop
1180 fetch c_get_geo_map into l_map_id;
1181 exit when c_get_geo_map%NOTFOUND ;
1182
1183 select map_id,loc_tbl_name,country_code,
1184 address_style
1185 into l_map_id,l_location_table_name,l_country,
1186 l_address_style
1187 from hz_geo_struct_map
1188 where map_id = l_map_id;
1189 if l_map_id is not null then
1190 select loc_seq_num,loc_component,geography_type
1191 bulk collect into l_geo_struct_map_dtl_tbl
1192 from hz_geo_struct_map_dtl
1193 where map_id = l_map_id
1194 and geography_type = p_geography_type;
1195
1196 if l_geo_struct_map_dtl_tbl.COUNT > 0 then
1197
1198 HZ_GEO_STRUCT_MAP_PUB.delete_geo_struct_mapping(l_map_id,
1199 l_location_table_name,
1200 l_country,
1201 l_address_style,
1202 l_geo_struct_map_dtl_tbl,
1203 FND_API.G_FALSE,
1204 x_return_status,
1205 x_msg_count,
1206 x_msg_data);
1207 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1208 RAISE FND_API.G_EXC_ERROR;
1209 END IF;
1210 end if;
1211 end if;
1212 /*
1213 delete hz_geo_struct_map_dtl
1214 where map_id = l_map_id
1215 and geography_type = p_geography_type;
1216
1217 open c_get_addr_usg;
1218 LOOP
1219 fetch c_get_addr_usg into l_usage_id;
1220 exit when c_get_addr_usg%NOTFOUND;
1221
1222 delete hz_address_usage_dtls
1223 where usage_id = l_usage_id
1224 and geography_type = p_geography_type;
1225
1226 end loop;
1227 CLOSE c_get_addr_usg;
1228 */
1229 end loop;
1230 CLOSE c_get_geo_map;
1231
1232
1233 END do_delete_geo_structure;
1234
1235
1236 -- create zone type
1237 PROCEDURE do_create_zone_type(
1238 p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
1239 x_return_status IN OUT NOCOPY VARCHAR2
1240 ) IS
1241
1242 l_object_id NUMBER;
1243 l_predicate VARCHAR2(1000);
1244 l_rowid VARCHAR2(64);
1245 l_count NUMBER;
1246 l_geo_rel_type_rec GEO_REL_TYPE_REC_TYPE;
1247 x_relationship_type_id NUMBER;
1248 l_instance_set_id NUMBER;
1249 x_msg_count NUMBER;
1250 x_msg_data VARCHAR2(2000);
1251 l_geography_type_name VARCHAR2(80);
1252
1253 -- Added ro ER 4232852
1254 l_rel_status VARCHAR2(1);
1255 l_object_version_number NUMBER;
1256
1257 l_limited_by_geo_type VARCHAR2(30);
1258 l_country_code VARCHAR2(30);
1259 l_valid_geo_type VARCHAR2(30);
1260
1261 BEGIN
1262
1263 l_geography_type_name := p_zone_type_rec.geography_type_name;
1264
1265 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_zone_type (
1266 p_zone_type_rec => p_zone_type_rec,
1267 p_create_update_flag => 'C',
1268 x_return_status => x_return_status
1269 );
1270
1271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1272 RAISE FND_API.G_EXC_ERROR;
1273 END IF;
1274 IF p_zone_type_rec.geography_type_name IS NULL THEN
1275 l_geography_type_name := initcap(p_zone_type_rec.geography_type);
1276 END IF;
1277
1278 --dbms_output.put_line('l_geography_type_name is '||l_geography_type_name);
1279 -- call the table handler to create geography_type
1280 hz_geography_types_PKG.Insert_Row (
1281 x_rowid => l_rowid,
1282 x_geography_type => p_zone_type_rec.geography_type,
1283 x_geography_type_name => l_geography_type_name,
1284 x_object_version_number => 1,
1285 x_geography_use => p_zone_type_rec.geography_use,
1286 x_postal_code_range_flag => p_zone_type_rec.postal_code_range_flag,
1287 x_limited_by_geography_id => p_zone_type_rec.limited_by_geography_id,
1288 x_created_by_module => p_zone_type_rec.created_by_module,
1289 x_application_id => p_zone_type_rec.application_id,
1290 x_program_login_id => NULL
1291 );
1292
1293 --dbms_output.put_line('After geography_type insert');
1294
1295 -- initialize the variables for creating fnd_object_instance_sets
1296
1297
1298 SELECT object_id into l_object_id
1299 FROM FND_OBJECTS
1300 WHERE obj_name='HZ_GEOGRAPHIES';
1301
1302 -- Replace the ' in predicate with ''
1303 l_predicate := 'GEOGRAPHY_TYPE='||''''||replace(p_zone_type_rec.geography_type,'''','''''')||'''';
1304
1305 BEGIN
1306 SELECT count(*)
1307 INTO l_count
1308 FROM FND_OBJECT_INSTANCE_SETS
1309 WHERE INSTANCE_SET_NAME = p_zone_type_rec.geography_type;
1310
1311 IF l_count = 0 THEN
1312
1313 SELECT FND_OBJECT_INSTANCE_SETS_S.nextval INTO l_instance_set_id FROM dual;
1314 l_rowid := NULL;
1315
1316 -- call the table handler to create fnd_object_instance_sets
1317 FND_OBJECT_INSTANCE_SETS_PKG.INSERT_ROW (
1321 X_OBJECT_ID => l_object_id,
1318 X_ROWID => l_rowid,
1319 X_INSTANCE_SET_ID => l_instance_set_id,
1320 X_INSTANCE_SET_NAME => p_zone_type_rec.geography_type,
1322 X_PREDICATE => l_predicate,
1323 X_DISPLAY_NAME => p_zone_type_rec.geography_type,
1324 X_DESCRIPTION => p_zone_type_rec.geography_type,
1325 X_CREATION_DATE => HZ_UTILITY_V2PUB.creation_date,
1326 X_CREATED_BY => HZ_UTILITY_V2PUB.created_by,
1327 X_LAST_UPDATE_DATE => HZ_UTILITY_V2PUB.last_update_date,
1328 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.last_updated_by,
1329 X_LAST_UPDATE_LOGIN => HZ_UTILITY_V2PUB.last_update_login
1330 ) ;
1331
1332 END IF;
1333 END;
1334
1335
1336 -- create relationship_types between geography_type and included_geography_type
1337 IF p_zone_type_rec.included_geography_type.count > 0 THEN
1338
1339 l_geo_rel_type_rec.parent_geography_type := p_zone_type_rec.geography_type;
1340 l_geo_rel_type_rec.status := 'A';
1341 l_geo_rel_type_rec.created_by_module := p_zone_type_rec.created_by_module;
1342 l_geo_rel_type_rec.application_id := p_zone_type_rec.application_id;
1343
1344 FOR i in 1 .. p_zone_type_rec.included_geography_type.count LOOP
1345
1346 -- check if included_geography_type is COUNTRY and if yes, check for limited_by_geography_id to be null
1347 IF p_zone_type_rec.included_geography_type(i) = 'COUNTRY' THEN
1348 IF p_zone_type_rec.limited_by_geography_id IS NOT NULL THEN
1349 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_LIMITED_GEOGRAPHY');
1350 FND_MSG_PUB.ADD;
1351 RAISE FND_API.G_EXC_ERROR;
1352 END IF;
1353 END IF;
1354
1355
1356 -- Below check added for bug # 3116311, 3170024
1357 -- We should chk to make sure the included_geo_types are the types for children under limited_geo_id.
1358 IF (p_zone_type_rec.limited_by_geography_id IS NOT NULL AND p_zone_type_rec.limited_by_geography_id <> fnd_api.g_miss_num) THEN
1359 BEGIN
1360 SELECT geography_type, country_code
1361 INTO l_limited_by_geo_type, l_country_code
1362 FROM hz_geographies
1363 WHERE geography_id = p_zone_type_rec.limited_by_geography_id;
1364
1365 SELECT geography_type
1366 INTO l_valid_geo_type
1367 FROM hz_geo_structure_levels
1368 WHERE country_code = l_country_code
1369 AND geography_type = p_zone_type_rec.included_geography_type(i)
1370 START WITH parent_geography_type = l_limited_by_geo_type
1371 AND country_code = l_country_code
1372 CONNECT BY PRIOR geography_type = parent_geography_type
1373 AND country_code = l_country_code;
1374
1375 EXCEPTION WHEN NO_DATA_FOUND THEN
1376 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_INVALID_INC_GEO_TYPE');
1377 FND_MESSAGE.SET_TOKEN('P_LIMITED_BY_GEOGRAPHY', l_limited_by_geo_type);
1378 FND_MSG_PUB.ADD;
1379 RAISE FND_API.G_EXC_ERROR;
1380 END;
1381 END IF;
1382
1383 -- check if there exists a relationship_type for this geography_type and included_geography_type
1384 Begin
1385 SELECT status, relationship_type_id, object_version_number
1386 INTO l_rel_status, x_relationship_type_id, l_object_version_number
1387 FROM HZ_RELATIONSHIP_TYPES
1388 WHERE subject_type = p_zone_type_rec.geography_type
1389 AND object_type= p_zone_type_rec.included_geography_type(i)
1390 AND forward_rel_code = 'PARENT_OF'
1391 AND backward_rel_code = 'CHILD_OF'
1392 AND relationship_type = p_zone_type_rec.geography_use;
1393
1394 IF l_rel_status = 'I' THEN
1395 HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
1396 p_init_msg_list => 'F',
1397 p_relationship_type_id => x_relationship_type_id,
1398 p_status => 'A',
1399 p_object_version_number => l_object_version_number,
1400 x_return_status => x_return_status,
1401 x_msg_count => x_msg_count,
1402 x_msg_data => x_msg_data
1403 );
1404 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1405 RAISE FND_API.G_EXC_ERROR;
1406 END IF;
1407 END IF;
1408 EXCEPTION
1409 WHEN NO_DATA_FOUND THEN
1410 -- create a relationship type with this geography type and included geography type
1411 l_geo_rel_type_rec.geography_type := p_zone_type_rec.included_geography_type(i);
1412 x_relationship_type_id := NULL;
1413
1414 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
1415 p_init_msg_list => 'F',
1416 p_geo_rel_type_rec => l_geo_rel_type_rec,
1417 x_relationship_type_id => x_relationship_type_id,
1418 x_return_status => x_return_status,
1419 x_msg_count => x_msg_count,
1420 x_msg_data => x_msg_data
1421 );
1422
1423 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1424 RAISE FND_API.G_EXC_ERROR;
1425 END IF;
1426 END;
1427 END LOOP;
1428 END IF;
1429 END do_create_zone_type;
1430
1431
1432 -- update zone Type
1433
1434 PROCEDURE do_update_zone_type(
1435 p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
1436 p_object_version_number IN OUT NOCOPY NUMBER,
1437 x_return_status IN OUT NOCOPY VARCHAR2,
1438 x_msg_count OUT NOCOPY NUMBER,
1442 l_postal_code_range_flag VARCHAR2(1);
1439 x_msg_data OUT NOCOPY VARCHAR2
1440 ) IS
1441
1443 l_limited_by_geo_id NUMBER;
1444 l_count NUMBER;
1445 l_rowid ROWID;
1446 l_object_version_number NUMBER;
1447 l_geography_type VARCHAR2(30);
1448
1449 p_geography_type VARCHAR2(30) := p_zone_type_rec.geography_type;
1450 p_limited_by_geography_id NUMBER := p_zone_type_rec.limited_by_geography_id;
1451 p_postal_code_range_flag VARCHAR2(1) := p_zone_type_rec.postal_code_range_flag;
1452 l_rel_status VARCHAR2(1);
1453 x_relationship_type_id NUMBER;
1454 l_geo_rel_type_rec GEO_REL_TYPE_REC_TYPE;
1455 l_object_type VARCHAR2(30);
1456 removed boolean := TRUE;
1457
1458 cursor included_geo_type is
1459 select relationship_type_id, object_version_number, object_type
1460 from HZ_RELATIONSHIP_TYPES
1461 where subject_type = p_zone_type_rec.geography_type
1462 and forward_rel_code = 'PARENT_OF'
1463 and backward_rel_code = 'CHILD_OF'
1464 and relationship_type = 'TAX'
1465 and status = 'A';
1466
1467
1468 BEGIN
1469
1470 hz_utility_v2pub.validate_mandatory(
1471 p_create_update_flag => 'U',
1472 p_column => 'object_version_number',
1473 p_column_value => p_object_version_number,
1474 x_return_status => x_return_status
1475 );
1476
1477 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480
1481 -- validate for zone type update
1482
1483 BEGIN
1484
1485 SELECT OBJECT_VERSION_NUMBER,
1486 ROWID,
1487 GEOGRAPHY_TYPE,
1488 POSTAL_CODE_RANGE_FLAG,
1489 LIMITED_BY_GEOGRAPHY_ID
1490 INTO l_object_version_number,
1491 l_rowid,
1492 l_geography_type,
1493 l_postal_code_range_flag,
1494 l_limited_by_geo_id
1495 FROM HZ_GEOGRAPHY_TYPES_B
1496 WHERE GEOGRAPHY_TYPE = p_GEOGRAPHY_TYPE
1497 FOR UPDATE OF GEOGRAPHY_TYPE NOWAIT;
1498
1499
1500 --validate object_version_number
1501 IF l_object_version_number <> p_object_version_number THEN
1502 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1503 FND_MESSAGE.SET_TOKEN('TABLE', 'hz_geography_types_b');
1504 FND_MSG_PUB.ADD;
1505 RAISE FND_API.G_EXC_ERROR;
1506 ELSE
1507 p_object_version_number := l_object_version_number + 1;
1508 END IF;
1509
1510 EXCEPTION WHEN NO_DATA_FOUND THEN
1511 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
1512 FND_MESSAGE.SET_TOKEN('TOKEN1','zone_type');
1513 FND_MESSAGE.SET_TOKEN('TOKEN2', 'zone_type '||p_geography_type);
1514 FND_MSG_PUB.ADD;
1515 RAISE FND_API.G_EXC_ERROR;
1516
1517 END;
1518
1519 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_zone_type(
1520 p_zone_type_rec => p_zone_type_rec,
1521 p_create_update_flag => 'U',
1522 x_return_status => x_return_status
1523 );
1524
1525
1526 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1527 RAISE FND_API.G_EXC_ERROR;
1528 END IF;
1529
1530 --dbms_output.put_line('After validate');
1531
1532 IF (p_postal_code_range_flag = 'Y' and l_postal_code_range_flag = 'N') THEN
1533
1534 -- check if there exists any rows in hz_geography_ranges table for this zone_type
1535 -- if yes, then do not allow update
1536
1537 SELECT count(*) INTO l_count
1538 FROM hz_geography_ranges
1539 WHERE geography_id in (SELECT geography_id from hz_geographies
1540 WHERE geography_type=p_geography_type
1541 AND end_date > sysdate)
1542 AND rownum <2;
1543
1544 --dbms_output.put_line('l_count is '||to_char(l_count));
1545 IF l_count >0 THEN
1546 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
1547 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'postal_code_range_flag from Y to N as there exists rows in hz_geography_ranges');
1548 FND_MSG_PUB.ADD;
1549 -- x_return_status := FND_API.G_RET_STS_ERROR;
1550 RAISE FND_API.G_EXC_ERROR;
1551 END IF;
1552 END IF;
1553
1554 IF p_limited_by_geography_id <> l_limited_by_geo_id THEN
1555 --this can be updated only if there are no zones created for this geography_type
1556 SELECT count(*) INTO l_count
1557 FROM hz_geographies
1558 WHERE geography_type = p_geography_type
1559 AND end_date > SYSDATE
1560 AND rownum <3;
1561
1562 --dbms_output.put_line('l_count for limited_id '||to_char(l_count));
1563
1564 IF l_count > 0 THEN
1565 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
1566 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'limited_by_geography_id as there exists zones for this geography_type');
1567 FND_MSG_PUB.ADD;
1568 -- x_return_status := FND_API.G_RET_STS_ERROR;
1569 RAISE FND_API.G_EXC_ERROR;
1570 END IF;
1571 END IF;
1572 --dbms_output.put_line('l_rowid is '|| l_rowid);
1573 --dbms_output.put_line('geography_type_name is '||p_geography_type_name);
1574 -- call table handler to update zone type
1575 hz_geography_types_PKG.update_row(
1579 x_object_version_number => p_object_version_number,
1576 x_rowid => l_rowid,
1577 x_geography_type => p_geography_type,
1578 x_geography_type_name => NULL,
1580 x_geography_use => NULL,
1581 x_postal_code_range_flag => p_postal_code_range_flag,
1582 x_limited_by_geography_id => p_limited_by_geography_id,
1583 x_created_by_module => NULL,
1584 x_application_id => NULL,
1585 x_program_login_id => NULL
1586 );
1587 --dbms_output.put_line('After insert');
1588
1589 -- create relationship_types between geography_type and included_geography_type
1590 IF p_zone_type_rec.included_geography_type.count > 0 THEN
1591
1592 l_geo_rel_type_rec.parent_geography_type := p_zone_type_rec.geography_type;
1593 l_geo_rel_type_rec.status := 'A';
1594 l_geo_rel_type_rec.created_by_module := p_zone_type_rec.created_by_module;
1595 l_geo_rel_type_rec.application_id := p_zone_type_rec.application_id;
1596
1597 FOR i in 1 .. p_zone_type_rec.included_geography_type.count LOOP
1598
1599 -- check if included_geography_type is COUNTRY and if yes, check for limited_by_geography_id to be null
1600 IF p_zone_type_rec.included_geography_type(i) = 'COUNTRY' THEN
1601 IF p_zone_type_rec.limited_by_geography_id IS NOT NULL THEN
1602 FND_MESSAGE.SET_NAME( 'AR', 'HZ_GEO_LIMITED_GEOGRAPHY');
1603 FND_MSG_PUB.ADD;
1604 RAISE FND_API.G_EXC_ERROR;
1605 END IF;
1606 END IF;
1607 Begin
1608 SELECT status, relationship_type_id, object_version_number
1609 INTO l_rel_status, x_relationship_type_id, l_object_version_number
1610 FROM HZ_RELATIONSHIP_TYPES
1611 WHERE subject_type = p_zone_type_rec.geography_type
1612 AND object_type= p_zone_type_rec.included_geography_type(i)
1613 AND forward_rel_code = 'PARENT_OF'
1614 AND backward_rel_code = 'CHILD_OF'
1615 AND relationship_type = p_zone_type_rec.geography_use;
1616
1617 IF l_rel_status = 'I' THEN
1618 HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
1619 p_init_msg_list => 'F',
1620 p_relationship_type_id => x_relationship_type_id,
1621 p_status => 'A',
1622 p_object_version_number => l_object_version_number,
1623 x_return_status => x_return_status,
1624 x_msg_count => x_msg_count,
1625 x_msg_data => x_msg_data
1626 );
1627 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1628 RAISE FND_API.G_EXC_ERROR;
1629 END IF;
1630 END IF;
1631 EXCEPTION
1632 WHEN NO_DATA_FOUND THEN
1633 -- create a relationship type with this geography type and included geography type
1634 l_geo_rel_type_rec.geography_type := p_zone_type_rec.included_geography_type(i);
1635 x_relationship_type_id := NULL;
1636 l_geo_rel_type_rec.status:= 'A';
1637
1638 HZ_GEOGRAPHY_STRUCTURE_PUB.create_geo_rel_type(
1639 p_init_msg_list => 'F',
1640 p_geo_rel_type_rec => l_geo_rel_type_rec,
1641 x_relationship_type_id => x_relationship_type_id,
1642 x_return_status => x_return_status,
1643 x_msg_count => x_msg_count,
1644 x_msg_data => x_msg_data
1645 );
1646
1647 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1648 RAISE FND_API.G_EXC_ERROR;
1649 END IF;
1650 END;
1651
1652 END LOOP;
1653 END IF;
1654
1655 open included_geo_type;
1656 loop
1657 fetch included_geo_type into
1658 x_relationship_type_id, l_object_version_number, l_object_type;
1659 exit when included_geo_type%NOTFOUND;
1660 removed := TRUE;
1661 FOR j in 1 .. p_zone_type_rec.included_geography_type.count LOOP
1662 if(l_object_type = p_zone_type_rec.included_geography_type(j)) then
1663 removed := FALSE;
1664 exit;
1665 end if;
1666 END LOOP;
1667 if(removed) then
1668 HZ_GEOGRAPHY_STRUCTURE_PUB.update_geo_rel_type(
1669 p_init_msg_list => 'F',
1670 p_relationship_type_id => x_relationship_type_id,
1671 p_status => 'I',
1672 p_object_version_number => l_object_version_number,
1673 x_return_status => x_return_status,
1674 x_msg_count => x_msg_count,
1675 x_msg_data => x_msg_data
1676 );
1677 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1678 RAISE FND_API.G_EXC_ERROR;
1679 END IF;
1680 end if;
1681 end loop;
1682 close included_geo_type;
1683
1684 END do_update_zone_type;
1685
1686
1687 ----------------------------
1688 -- body of public procedures
1689 ----------------------------
1690
1691 /**
1692 * PROCEDURE create_geography_type
1693 *
1694 * DESCRIPTION
1695 * Creates Geography type.
1696 *
1697 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1698 *
1699 * ARGUMENTS
1700 * IN:
1701 * p_init_msg_list Initialize message stack if it is set to
1705 * OUT:
1702 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1703 * p_geography_type_rec Geography type record.
1704 * IN/OUT:
1706 * x_return_status Return status after the call. The status can
1707 * be FND_API.G_RET_STS_SUCCESS (success),
1708 * FND_API.G_RET_STS_ERROR (error),
1709 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1710 * x_msg_count Number of messages in message stack.
1711 * x_msg_data Message text if x_msg_count is 1.
1712 *
1713 * NOTES
1714 *
1715 * MODIFICATION HISTORY
1716 *
1717 * 11-04-2002 Rekha Nalluri o Created.
1718 *
1719 */
1720
1721 PROCEDURE create_geography_type (
1722 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1723 p_geography_type_rec IN GEOGRAPHY_TYPE_REC_TYPE,
1724 x_return_status OUT NOCOPY VARCHAR2,
1725 x_msg_count OUT NOCOPY NUMBER,
1726 x_msg_data OUT NOCOPY VARCHAR2
1727 ) IS
1728
1729 --l_geography_type_rec GEOGRAPHY_TYPE_REC_TYPE := p_geography_type_rec;
1730 p_index_name VARCHAR2(30);
1731
1732 BEGIN
1733 -- Standard start of API savepoint
1734 SAVEPOINT create_geography_type;
1735
1736 -- Initialize message list if p_init_msg_list is set to TRUE.
1737 IF FND_API.to_Boolean(p_init_msg_list) THEN
1738 FND_MSG_PUB.initialize;
1739 END IF;
1740
1741 -- Initialize API return status to success.
1742 x_return_status := FND_API.G_RET_STS_SUCCESS;
1743
1744 -- Call to business logic.
1745 do_create_geography_type(
1746 p_geography_type_rec => p_geography_type_rec,
1747 x_return_status => x_return_status
1748 );
1749
1750 --if validation failed at any point, then raise an exception to stop processing
1751 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1752 RAISE FND_API.G_EXC_ERROR;
1753 END IF;
1754
1755 -- Standard call to get message count and if count is 1, get message info.
1756 FND_MSG_PUB.Count_And_Get(
1757 p_encoded => FND_API.G_FALSE,
1758 p_count => x_msg_count,
1759 p_data => x_msg_data);
1760
1761 EXCEPTION
1762 WHEN FND_API.G_EXC_ERROR THEN
1763 ROLLBACK TO create_geography_type;
1764 x_return_status := FND_API.G_RET_STS_ERROR;
1765 FND_MSG_PUB.Count_And_Get(
1766 p_encoded => FND_API.G_FALSE,
1767 p_count => x_msg_count,
1768 p_data => x_msg_data);
1769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1770 ROLLBACK TO create_geography_type;
1771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1772 FND_MSG_PUB.Count_And_Get(
1773 p_encoded => FND_API.G_FALSE,
1774 p_count => x_msg_count,
1775 p_data => x_msg_data);
1776
1777 WHEN DUP_VAL_ON_INDEX THEN
1778 ROLLBACK TO create_geography_type;
1779 x_return_status := FND_API.G_RET_STS_ERROR;
1780 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
1781 IF p_index_name = 'HZ_GEOGRAPHY_TYPES_B_U1' THEN
1782 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
1783 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_type');
1784 FND_MSG_PUB.ADD;
1785 END IF;
1786 FND_MSG_PUB.Count_And_Get(
1787 p_encoded => FND_API.G_FALSE,
1788 p_count => x_msg_count,
1789 p_data => x_msg_data);
1790
1791 WHEN OTHERS THEN
1792 ROLLBACK TO create_geography_type;
1793 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1794 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1795 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1796 FND_MSG_PUB.ADD;
1797 FND_MSG_PUB.Count_And_Get(
1798 p_encoded => FND_API.G_FALSE,
1799 p_count => x_msg_count,
1800 p_data => x_msg_data);
1801
1802 END create_geography_type;
1803
1804
1805 /**
1806 * PROCEDURE create_geography_rel_type
1807 *
1808 * DESCRIPTION
1809 * Creates Geography Relationship type.
1810 *
1811 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1812 *
1813 * ARGUMENTS
1814 * IN:
1815 * p_init_msg_list Initialize message stack if it is set to
1816 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1817 * p_geography_rel_type_rec Geography Relationship type record.
1818 * IN/OUT:
1819 * OUT:
1820 * x_return_status Return status after the call. The status can
1821 * be FND_API.G_RET_STS_SUCCESS (success),
1822 * FND_API.G_RET_STS_ERROR (error),
1823 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1824 * x_msg_count Number of messages in message stack.
1825 * x_msg_data Message text if x_msg_count is 1.
1826 *
1827 * NOTES
1828 *
1829 * MODIFICATION HISTORY
1830 *
1831 * 11-11-2002 Rekha Nalluri o Created.
1832 *
1833 */
1834
1835 PROCEDURE create_geo_rel_type(
1836 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1837 p_geo_rel_type_rec IN GEO_REL_TYPE_REC_TYPE,
1841 x_msg_data OUT NOCOPY VARCHAR2
1838 x_relationship_type_id OUT NOCOPY NUMBER,
1839 x_return_status OUT NOCOPY VARCHAR2,
1840 x_msg_count OUT NOCOPY NUMBER,
1842 ) IS
1843
1844 l_geo_rel_type_rec geo_rel_type_REC_TYPE := p_geo_rel_type_rec;
1845
1846 BEGIN
1847
1848 -- Standard start of API savepoint
1849 SAVEPOINT create_geo_rel_type;
1850
1851 -- Initialize message list if p_init_msg_list is set to TRUE.
1852 IF FND_API.to_Boolean(p_init_msg_list) THEN
1853 FND_MSG_PUB.initialize;
1854 END IF;
1855
1856 -- Initialize API return status to success.
1857 x_return_status := FND_API.G_RET_STS_SUCCESS;
1858
1859 -- Call to business logic.
1860 do_create_geo_rel_type(
1861 p_geo_rel_type_rec => l_geo_rel_type_rec,
1862 x_relationship_type_id => x_relationship_type_id,
1863 x_return_status => x_return_status
1864 );
1865
1866 --if validation failed at any point, then raise an exception to stop processing
1867 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1868 RAISE FND_API.G_EXC_ERROR;
1869 END IF;
1870
1871 -- Standard call to get message count and if count is 1, get message info.
1872 FND_MSG_PUB.Count_And_Get(
1873 p_encoded => FND_API.G_FALSE,
1874 p_count => x_msg_count,
1875 p_data => x_msg_data);
1876
1877 EXCEPTION
1878 WHEN FND_API.G_EXC_ERROR THEN
1879 ROLLBACK TO create_geo_rel_type;
1880 x_return_status := FND_API.G_RET_STS_ERROR;
1881 FND_MSG_PUB.Count_And_Get(
1882 p_encoded => FND_API.G_FALSE,
1883 p_count => x_msg_count,
1884 p_data => x_msg_data);
1885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1886 ROLLBACK TO create_geo_rel_type;
1887 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1888 FND_MSG_PUB.Count_And_Get(
1889 p_encoded => FND_API.G_FALSE,
1890 p_count => x_msg_count,
1891 p_data => x_msg_data);
1892
1893 WHEN OTHERS THEN
1894 ROLLBACK TO create_geo_rel_type;
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1897 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1898 FND_MSG_PUB.ADD;
1899 FND_MSG_PUB.Count_And_Get(
1900 p_encoded => FND_API.G_FALSE,
1901 p_count => x_msg_count,
1902 p_data => x_msg_data);
1903
1904 END CREATE_GEO_REL_TYPE;
1905
1906 /**
1907 * PROCEDURE update_geography_rel_type
1908 *
1909 * DESCRIPTION
1910 * Updates only Status of geography relationship type.
1911 *
1912 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1913 *
1914 * ARGUMENTS
1915 * IN:
1916 * p_init_msg_list Initialize message stack if it is set to
1917 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
1918 * p_geography_rel_type_rec Geography Relationship type record.
1919 * IN/OUT:
1920 * p_object_version_number object version number of the row being updated
1921 * OUT:
1922 * x_return_status Return status after the call. The status can
1923 * be FND_API.G_RET_STS_SUCCESS (success),
1924 * FND_API.G_RET_STS_ERROR (error),
1925 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1926 * x_msg_count Number of messages in message stack.
1927 * x_msg_data Message text if x_msg_count is 1.
1928 *
1929 * NOTES
1930 *
1931 * MODIFICATION HISTORY
1932 *
1933 * 11-13-2002 Rekha Nalluri o Created.
1934 *
1935 */
1936
1937 PROCEDURE update_geo_rel_type(
1938 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1939 p_relationship_type_id IN NUMBER,
1940 p_status IN VARCHAR2,
1941 p_object_version_number IN OUT NOCOPY NUMBER,
1942 x_return_status OUT NOCOPY VARCHAR2,
1943 x_msg_count OUT NOCOPY NUMBER,
1944 x_msg_data OUT NOCOPY VARCHAR2
1945 )IS
1946
1947 --l_geo_rel_type_rec geo_rel_type_REC_TYPE := p_geo_rel_type_rec;
1948
1949 BEGIN
1950
1951 -- Standard start of API savepoint
1952 SAVEPOINT update_geo_rel_type;
1953
1954 -- Initialize message list if p_init_msg_list is set to TRUE.
1955 IF FND_API.to_Boolean(p_init_msg_list) THEN
1956 FND_MSG_PUB.initialize;
1957 END IF;
1958
1959 -- Initialize API return status to success.
1960 x_return_status := FND_API.G_RET_STS_SUCCESS;
1961
1962 -- Call to business logic.
1963 do_update_geo_rel_type(
1964 p_relationship_type_id => p_relationship_type_id,
1965 p_status => p_status,
1966 p_object_version_number => p_object_version_number,
1967 x_return_status => x_return_status
1968 );
1969
1970 --if validation failed at any point, then raise an exception to stop processing
1971 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1972 RAISE FND_API.G_EXC_ERROR;
1973 END IF;
1974
1975 -- Standard call to get message count and if count is 1, get message info.
1976 FND_MSG_PUB.Count_And_Get(
1980
1977 p_encoded => FND_API.G_FALSE,
1978 p_count => x_msg_count,
1979 p_data => x_msg_data);
1981 EXCEPTION
1982 WHEN FND_API.G_EXC_ERROR THEN
1983 ROLLBACK TO update_geo_rel_type;
1984 x_return_status := FND_API.G_RET_STS_ERROR;
1985 FND_MSG_PUB.Count_And_Get(
1986 p_encoded => FND_API.G_FALSE,
1987 p_count => x_msg_count,
1988 p_data => x_msg_data);
1989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1990 ROLLBACK TO update_geo_rel_type;
1991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1992 FND_MSG_PUB.Count_And_Get(
1993 p_encoded => FND_API.G_FALSE,
1994 p_count => x_msg_count,
1995 p_data => x_msg_data);
1996
1997 WHEN OTHERS THEN
1998 ROLLBACK TO update_geo_rel_type;
1999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2000 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2001 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2002 FND_MSG_PUB.ADD;
2003 FND_MSG_PUB.Count_And_Get(
2004 p_encoded => FND_API.G_FALSE,
2005 p_count => x_msg_count,
2006 p_data => x_msg_data);
2007
2008 END UPDATE_GEO_REL_TYPE;
2009
2010 /**
2011 * PROCEDURE create_geo_structure
2012 *
2013 * DESCRIPTION
2014 * Creates Geography Structure.
2015 *
2016 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2017 *
2018 * ARGUMENTS
2019 * IN:
2020 * p_init_msg_list Initialize message stack if it is set to
2021 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2022 * p_geo_structure_rec Geography structure type record.
2023
2024 * IN/OUT:
2025 * OUT:
2026 * x_return_status Return status after the call. The status can
2027 * be FND_API.G_RET_STS_SUCCESS (success),
2028 * FND_API.G_RET_STS_ERROR (error),
2029 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2030 * x_msg_count Number of messages in message stack.
2031 * x_msg_data Message text if x_msg_count is 1.
2032 *
2033 * NOTES
2034 *
2035 * MODIFICATION HISTORY
2036 *
2037 * 11-18-2002 Rekha Nalluri o Created.
2038 *
2039 */
2040
2041
2042 PROCEDURE create_geo_structure(
2043 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2044 p_geo_structure_rec IN GEO_STRUCTURE_REC_TYPE,
2045 x_return_status OUT NOCOPY VARCHAR2,
2046 x_msg_count OUT NOCOPY NUMBER,
2047 x_msg_data OUT NOCOPY VARCHAR2
2048 ) IS
2049
2050 p_index_name VARCHAR2(30);
2051
2052 BEGIN
2053
2054 -- Standard start of API savepoint
2055 SAVEPOINT create_geo_structure;
2056
2057 -- Initialize message list if p_init_msg_list is set to TRUE.
2058 IF FND_API.to_Boolean(p_init_msg_list) THEN
2059 FND_MSG_PUB.initialize;
2060 END IF;
2061
2062 -- Initialize API return status to success.
2063 x_return_status := FND_API.G_RET_STS_SUCCESS;
2064
2065 -- Call to business logic.
2066 do_create_geo_structure(
2067 p_geo_structure_rec => p_geo_structure_rec,
2068 x_return_status => x_return_status
2069 );
2070
2071 --if validation failed at any point, then raise an exception to stop processing
2072 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2073 RAISE FND_API.G_EXC_ERROR;
2074 END IF;
2075
2076 -- Standard call to get message count and if count is 1, get message info.
2077 FND_MSG_PUB.Count_And_Get(
2078 p_encoded => FND_API.G_FALSE,
2079 p_count => x_msg_count,
2080 p_data => x_msg_data);
2081
2082 EXCEPTION
2083 WHEN FND_API.G_EXC_ERROR THEN
2084 ROLLBACK TO create_geo_structure;
2085 x_return_status := FND_API.G_RET_STS_ERROR;
2086 FND_MSG_PUB.Count_And_Get(
2087 p_encoded => FND_API.G_FALSE,
2088 p_count => x_msg_count,
2089 p_data => x_msg_data);
2090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2091 ROLLBACK TO create_geo_structure;
2092 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2093 FND_MSG_PUB.Count_And_Get(
2094 p_encoded => FND_API.G_FALSE,
2095 p_count => x_msg_count,
2096 p_data => x_msg_data);
2097
2098 WHEN DUP_VAL_ON_INDEX THEN
2099 ROLLBACK TO create_geo_structure;
2100 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
2101 IF p_index_name = 'HZ_GEO_STRUCTURE_LEVELS_U1' THEN
2102 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2103 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_id,geography_type and parent_geography_type');
2104 FND_MSG_PUB.ADD;
2105 END IF;
2106 x_return_status := FND_API.G_RET_STS_ERROR;
2107 FND_MSG_PUB.Count_And_Get(
2108 p_encoded => FND_API.G_FALSE,
2109 p_count => x_msg_count,
2110 p_data => x_msg_data);
2111
2112 WHEN OTHERS THEN
2113 ROLLBACK TO create_geo_structure;
2114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2118 FND_MSG_PUB.Count_And_Get(
2115 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2116 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2117 FND_MSG_PUB.ADD;
2119 p_encoded => FND_API.G_FALSE,
2120 p_count => x_msg_count,
2121 p_data => x_msg_data);
2122
2123 END create_geo_structure;
2124
2125
2126 /**
2127 * PROCEDURE update_geo_structure
2128 *
2129 * DESCRIPTION
2130 * Updates geography_element_column in a Geography Structure - geography_element_column can be updated for
2131 * a geography_id and relationship_type_id only when there exists no geographies that have used this
2132 * structure.
2133 *
2134 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2135 *
2136 * ARGUMENTS
2137 * IN:
2138 * p_init_msg_list Initialize message stack if it is set to
2139 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2140 * p_geo_structure_rec Geography structure type record.
2141
2142 * IN/OUT:
2143 * p_object_version_number object version number of the row being updated
2144 * OUT:
2145 * x_return_status Return status after the call. The status can
2146 * be FND_API.G_RET_STS_SUCCESS (success),
2147 * FND_API.G_RET_STS_ERROR (error),
2148 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2149 * x_msg_count Number of messages in message stack.
2150 * x_msg_data Message text if x_msg_count is 1.
2151 *
2152 * NOTES
2153 *
2154 * MODIFICATION HISTORY
2155 *
2156 * 12-18-2002 Rekha Nalluri o Created.
2157 *
2158 */
2159
2160 /* Obsoleting as it is no more needed ( bug 2911108)
2161 PROCEDURE update_geo_structure(
2162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2163 p_geography_id IN NUMBER,
2164 p_geography_type IN VARCHAR2,
2165 p_parent_geography_type IN VARCHAR2,
2166 p_geography_element_column IN VARCHAR2,
2167 p_object_version_number IN OUT NOCOPY NUMBER,
2168 x_return_status OUT NOCOPY VARCHAR2,
2169 x_msg_count OUT NOCOPY NUMBER,
2170 x_msg_data OUT NOCOPY VARCHAR2
2171 ) IS
2172
2173 BEGIN
2174
2175 -- Standard start of API savepoint
2176 SAVEPOINT update_geo_structure;
2177
2178 -- Initialize message list if p_init_msg_list is set to TRUE.
2179 IF FND_API.to_Boolean(p_init_msg_list) THEN
2180 FND_MSG_PUB.initialize;
2181 END IF;
2182
2183 -- Initialize API return status to success.
2184 x_return_status := FND_API.G_RET_STS_SUCCESS;
2185
2186 -- Call to business logic.
2187 do_update_geo_structure(
2188 p_geography_id =>p_geography_id,
2189 p_geography_type =>p_geography_type,
2190 p_parent_geography_type => p_parent_geography_type,
2191 p_geography_element_column =>p_geography_element_column,
2192 p_object_version_number => p_object_version_number,
2193 x_return_status => x_return_status
2194 );
2195
2196 --if validation failed at any point, then raise an exception to stop processing
2197 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2198 RAISE FND_API.G_EXC_ERROR;
2199 END IF;
2200
2201 -- Standard call to get message count and if count is 1, get message info.
2202 FND_MSG_PUB.Count_And_Get(
2203 p_encoded => FND_API.G_FALSE,
2204 p_count => x_msg_count,
2205 p_data => x_msg_data);
2206
2207 EXCEPTION
2208 WHEN FND_API.G_EXC_ERROR THEN
2209 ROLLBACK TO update_geo_structure;
2210 x_return_status := FND_API.G_RET_STS_ERROR;
2211 FND_MSG_PUB.Count_And_Get(
2212 p_encoded => FND_API.G_FALSE,
2213 p_count => x_msg_count,
2214 p_data => x_msg_data);
2215 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2216 ROLLBACK TO update_geo_structure;
2217 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2218 FND_MSG_PUB.Count_And_Get(
2219 p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222
2223 WHEN OTHERS THEN
2224 ROLLBACK TO update_geo_structure;
2225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2226 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2227 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2228 FND_MSG_PUB.ADD;
2229 FND_MSG_PUB.Count_And_Get(
2230 p_encoded => FND_API.G_FALSE,
2231 p_count => x_msg_count,
2232 p_data => x_msg_data);
2233
2234 END update_geo_structure;
2235 */
2236
2237 /**
2238 * PROCEDURE delete_geo_structure
2239 *
2240 * DESCRIPTION
2241 * Deletes the row in the structure. Disables the relationship_type if it is not used by any other structure.
2242 *
2243 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2244 *
2245 * ARGUMENTS
2246 * IN:
2247 * p_init_msg_list Initialize message stack if it is set to
2248 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2249 * p_geo_structure_rec Geography structure type record.
2250
2251 * IN/OUT:
2255 * be FND_API.G_RET_STS_SUCCESS (success),
2252 *
2253 * OUT:
2254 * x_return_status Return status after the call. The status can
2256 * FND_API.G_RET_STS_ERROR (error),
2257 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2258 * x_msg_count Number of messages in message stack.
2259 * x_msg_data Message text if x_msg_count is 1.
2260 *
2261 * NOTES
2262 *
2263 * MODIFICATION HISTORY
2264 *
2265 * 12-19-2002 Rekha Nalluri o Created.
2266 *
2267 */
2268
2269 PROCEDURE delete_geo_structure(
2270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2271 p_geography_id IN NUMBER,
2272 p_geography_type IN VARCHAR2,
2273 p_parent_geography_type IN VARCHAR2,
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2
2277 )IS
2278
2279
2280 BEGIN
2281
2282 -- Standard start of API savepoint
2283 SAVEPOINT delete_geo_structure;
2284
2285 -- Initialize message list if p_init_msg_list is set to TRUE.
2286 IF FND_API.to_Boolean(p_init_msg_list) THEN
2287 FND_MSG_PUB.initialize;
2288 END IF;
2289
2290 -- Initialize API return status to success.
2291 x_return_status := FND_API.G_RET_STS_SUCCESS;
2292
2293 -- Call to business logic.
2294 do_delete_geo_structure(
2295 p_geography_id => p_geography_id,
2296 p_geography_type => p_geography_type,
2297 p_parent_geography_type => p_parent_geography_type,
2298 x_return_status => x_return_status
2299 );
2300
2301 --if validation failed at any point, then raise an exception to stop processing
2302 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2303 RAISE FND_API.G_EXC_ERROR;
2304 END IF;
2305
2306 -- Standard call to get message count and if count is 1, get message info.
2307 FND_MSG_PUB.Count_And_Get(
2308 p_encoded => FND_API.G_FALSE,
2309 p_count => x_msg_count,
2310 p_data => x_msg_data);
2311
2312 EXCEPTION
2313 WHEN FND_API.G_EXC_ERROR THEN
2314 ROLLBACK TO delete_geo_structure;
2315 x_return_status := FND_API.G_RET_STS_ERROR;
2316 FND_MSG_PUB.Count_And_Get(
2317 p_encoded => FND_API.G_FALSE,
2318 p_count => x_msg_count,
2319 p_data => x_msg_data);
2320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2321 ROLLBACK TO delete_geo_structure;
2322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2323 FND_MSG_PUB.Count_And_Get(
2324 p_encoded => FND_API.G_FALSE,
2325 p_count => x_msg_count,
2326 p_data => x_msg_data);
2327
2328 WHEN OTHERS THEN
2329 ROLLBACK TO delete_geo_structure;
2330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2331 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2332 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2333 FND_MSG_PUB.ADD;
2334 FND_MSG_PUB.Count_And_Get(
2335 p_encoded => FND_API.G_FALSE,
2336 p_count => x_msg_count,
2337 p_data => x_msg_data);
2338
2339 END delete_geo_structure;
2340
2341 /**
2342 * PROCEDURE create_zone_type
2343 *
2344 * DESCRIPTION
2345 * Creates Zone Type.
2346 *
2347 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2348 *
2349 * ARGUMENTS
2350 * IN:
2351 * p_init_msg_list Initialize message stack if it is set to
2352 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2353 * p_zone_type_rec Zone_type type record.
2354 * IN/OUT:
2355 * OUT:
2356 *
2357 * x_return_status Return status after the call. The status can
2358 * be FND_API.G_RET_STS_SUCCESS (success),
2359 * FND_API.G_RET_STS_ERROR (error),
2360 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2361 * x_msg_count Number of messages in message stack.
2362 * x_msg_data Message text if x_msg_count is 1.
2363 *
2364 * NOTES
2365 *
2366 * MODIFICATION HISTORY
2367 * 01-09-2003 Rekha Nalluri o Created.
2368 *
2369 */
2370
2371 PROCEDURE create_zone_type(
2372 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2373 p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
2374 x_return_status OUT NOCOPY VARCHAR2,
2375 x_msg_count OUT NOCOPY NUMBER,
2376 x_msg_data OUT NOCOPY VARCHAR2
2377 ) IS
2378
2379 p_index_name VARCHAR2(30);
2380
2381 BEGIN
2382 -- Standard start of API savepoint
2383 SAVEPOINT create_zone_type;
2384 --dbms_output.put_line('In the beginning of create_master_geography');
2385
2386 -- Initialize message list if p_init_msg_list is set to TRUE.
2387 IF FND_API.to_Boolean(p_init_msg_list) THEN
2388 FND_MSG_PUB.initialize;
2389 END IF;
2390
2391 -- Initialize API return status to success.
2392 x_return_status := FND_API.G_RET_STS_SUCCESS;
2393
2397 p_zone_type_rec => p_zone_type_rec,
2394 --dbms_output.put_line('before call to do_create_zone_type');
2395 -- Call to business logic.
2396 do_create_zone_type(
2398 x_return_status => x_return_status
2399 );
2400
2401 --if validation failed at any point, then raise an exception to stop processing
2402 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2403 RAISE FND_API.G_EXC_ERROR;
2404 END IF;
2405
2406 -- Standard call to get message count and if count is 1, get message info.
2407 FND_MSG_PUB.Count_And_Get(
2408 p_encoded => FND_API.G_FALSE,
2409 p_count => x_msg_count,
2410 p_data => x_msg_data);
2411
2412 EXCEPTION
2413 WHEN FND_API.G_EXC_ERROR THEN
2414 ROLLBACK TO create_zone_type;
2415 x_return_status := FND_API.G_RET_STS_ERROR;
2416 FND_MSG_PUB.Count_And_Get(
2417 p_encoded => FND_API.G_FALSE,
2418 p_count => x_msg_count,
2419 p_data => x_msg_data);
2420 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2421 ROLLBACK TO create_zone_type;
2422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2423 FND_MSG_PUB.Count_And_Get(
2424 p_encoded => FND_API.G_FALSE,
2425 p_count => x_msg_count,
2426 p_data => x_msg_data);
2427
2428 WHEN DUP_VAL_ON_INDEX THEN
2429 ROLLBACK TO create_zone_type;
2430 x_return_status := FND_API.G_RET_STS_ERROR;
2431 HZ_UTILITY_V2PUB.find_index_name(p_index_name);
2432 IF p_index_name = 'HZ_GEOGRAPHY_TYPES_B_U1' THEN
2433 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
2434 FND_MESSAGE.SET_TOKEN('COLUMN', 'geography_type');
2435 FND_MSG_PUB.ADD;
2436 END IF;
2437 FND_MSG_PUB.Count_And_Get(
2438 p_encoded => FND_API.G_FALSE,
2439 p_count => x_msg_count,
2440 p_data => x_msg_data);
2441
2442 WHEN OTHERS THEN
2443 ROLLBACK TO create_zone_type;
2444 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2445 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2446 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2447 FND_MSG_PUB.ADD;
2448 FND_MSG_PUB.Count_And_Get(
2449 p_encoded => FND_API.G_FALSE,
2450 p_count => x_msg_count,
2451 p_data => x_msg_data);
2452
2453 END create_zone_type;
2454
2455 /**
2456 * PROCEDURE update_zone_type
2457 *
2458 * DESCRIPTION
2459 * Updates zone type.
2460 *
2461 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
2462 *
2463 * ARGUMENTS
2464 * IN:
2465 * p_init_msg_list Initialize message stack if it is set to
2466 * FND_API.G_TRUE. Default is FND_API.G_FALSE.
2467 * p_geographytype Geography type.
2468 * p_limited_by_geography_id
2469 * p_postal_code_range_flag
2470 * IN/OUT:
2471 * p_object_version_number object version number of the row being updated
2472 * OUT:
2473 * x_return_status Return status after the call. The status can
2474 * be FND_API.G_RET_STS_SUCCESS (success),
2475 * FND_API.G_RET_STS_ERROR (error),
2476 * FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
2477 * x_msg_count Number of messages in message stack.
2478 * x_msg_data Message text if x_msg_count is 1.
2479 *
2480 * NOTES
2481 *
2482 * MODIFICATION HISTORY
2483 *
2484 * 01-13-2003 Rekha Nalluri o Created.
2485 *
2486 */
2487
2488 PROCEDURE update_zone_type(
2489 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2490 p_zone_type_rec IN ZONE_TYPE_REC_TYPE,
2491 p_object_version_number IN OUT NOCOPY NUMBER,
2492 x_return_status OUT NOCOPY VARCHAR2,
2493 x_msg_count OUT NOCOPY NUMBER,
2494 x_msg_data OUT NOCOPY VARCHAR2
2495 ) IS
2496
2497 BEGIN
2498
2499 -- Standard start of API savepoint
2500 SAVEPOINT update_zone_type;
2501
2502 -- Initialize message list if p_init_msg_list is set to TRUE.
2503 IF FND_API.to_Boolean(p_init_msg_list) THEN
2504 FND_MSG_PUB.initialize;
2505 END IF;
2506
2507 -- Initialize API return status to success.
2508 x_return_status := FND_API.G_RET_STS_SUCCESS;
2509 -- Call to business logic.
2510 do_update_zone_type(
2511 p_zone_type_rec => p_zone_type_rec,
2512 p_object_version_number => p_object_version_number,
2513 x_return_status => x_return_status,
2514 x_msg_count => x_msg_count,
2515 x_msg_data => x_msg_data
2516 );
2517
2518 --if validation failed at any point, then raise an exception to stop processing
2519 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2520 RAISE FND_API.G_EXC_ERROR;
2521 END IF;
2522
2523 -- Standard call to get message count and if count is 1, get message info.
2524 FND_MSG_PUB.Count_And_Get(
2525 p_encoded => FND_API.G_FALSE,
2526 p_count => x_msg_count,
2527 p_data => x_msg_data);
2528
2529 EXCEPTION
2530 WHEN FND_API.G_EXC_ERROR THEN
2531 ROLLBACK TO update_zone_type;
2532 x_return_status := FND_API.G_RET_STS_ERROR;
2533 FND_MSG_PUB.Count_And_Get(
2534 p_encoded => FND_API.G_FALSE,
2535 p_count => x_msg_count,
2536 p_data => x_msg_data);
2537 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2538 ROLLBACK TO update_zone_type;
2539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2540 FND_MSG_PUB.Count_And_Get(
2541 p_encoded => FND_API.G_FALSE,
2542 p_count => x_msg_count,
2543 p_data => x_msg_data);
2544
2545 WHEN OTHERS THEN
2546 ROLLBACK TO update_zone_type;
2547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2548 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2549 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2550 FND_MSG_PUB.ADD;
2551 FND_MSG_PUB.Count_And_Get(
2552 p_encoded => FND_API.G_FALSE,
2553 p_count => x_msg_count,
2554 p_data => x_msg_data);
2555
2556 END update_zone_type;
2557
2558 END HZ_GEOGRAPHY_STRUCTURE_PUB;