[Home] [Help]
PACKAGE BODY: APPS.HZ_GEOGRAPHY_VALIDATE_PVT
Source
1 PACKAGE BODY HZ_GEOGRAPHY_VALIDATE_PVT AS
2 /*$Header: ARHGEOVB.pls 120.15 2006/01/23 07:07:00 idali noship $ */
3
4
5 -----------------------------------------
6 -- declaration of private global varibles
7 -----------------------------------------
8
9 --------------------------------------------------
10 -- declaration of private procedures and functions
11 --------------------------------------------------
12
13 -------------------------------
14 -- body of private procedures
15 -------------------------------
16
17
18
19 --------------------------------
20 -- body of public procedures
21 --------------------------------
22 /**
23 * PROCEDURE validate_structure
24 *
25 * DESCRIPTION
26 * Validates whether geography_type and parent_geography_type are as per the structure defined
27 * for that country
28 *
29 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
30 *
31 * ARGUMENTS
32 * IN:
33 * p_start_date start date
34 * p_end_date end date
35 *
36 * IN/OUT:
37 * x_return_status Return status.
38 *
39 * NOTES
40 *
41 *
42 * MODIFICATION HISTORY
43 *
44 * 11-22-2002 Rekha Nalluri o Created.
45 *
46 */
47 PROCEDURE validate_structure(
48 p_geography_type IN VARCHAR2,
49 p_parent_geography_type IN VARCHAR2,
50 p_country_code IN VARCHAR2,
51 x_return_status IN OUT NOCOPY VARCHAR2
52 ) IS
53
54 l_count NUMBER;
55 l_geography_id NUMBER;
56 l_country_id NUMBER;
57
58 BEGIN
59
60 -- check whether the country structure is defined
61 SELECT geography_id INTO l_geography_id
62 FROM hz_geographies
63 WHERE country_code = p_country_code
64 AND geography_type = 'COUNTRY';
65
66 BEGIN
67
68 SELECT 1 into l_count
69 FROM hz_geo_structure_levels
70 WHERE geography_id = l_geography_id
71 AND rownum <2;
72
73 IF l_count = 0 THEN
74 fnd_message.set_name('AR', 'HZ_GEO_STRUCT_UNDEFINED');
75 fnd_message.set_token('GEO_ID', l_geography_id);
76 fnd_msg_pub.add;
77 x_return_status := fnd_api.g_ret_sts_error;
78 END IF;
79
80 -- check whether geography_type and parent_geography_type are as per the country structure
81 /*SELECT 1 into l_count
82 FROM dual
83 WHERE p_geography_type in (SELECT geography_type
84 FROM HZ_GEO_STRUCTURE_LEVELS
85 WHERE country_code = p_country_code
86 CONNECT BY PRIOR geography_type=parent_geography_type
87 START WITH parent_geography_type = p_parent_geography_type); */
88
89 -- changing the above validation as fix for 2917924
90
91 SELECT 1 into l_count
92 FROM hz_geo_structure_levels
93 WHERE geography_id=l_geography_id
94 and geography_type=p_geography_type
95 and parent_geography_type=p_parent_geography_type;
96
97 EXCEPTION
98 WHEN NO_DATA_FOUND THEN
99 fnd_message.set_name('AR', 'HZ_GEO_INVALID_COMBINATION');
100 fnd_message.set_token('COUNTRY', p_country_code);
101 fnd_message.set_token('PARENT_GEO', p_parent_geography_type);
102 fnd_message.set_token('CHILD_GEO', p_geography_type);
103 fnd_msg_pub.add;
104 x_return_status := fnd_api.g_ret_sts_error;
105 END;
106 END validate_structure;
107
108 /**
109 * PROCEDURE get_geography_type
110 *
111 * DESCRIPTION
112 * Gets the geography type based on the geography_id passed.
113 *
114 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
115 *
116 * ARGUMENTS
117 * IN:
118 * p_geography_id Geography ID
119 * x_geography_type Geography Type
120 *
121 *
122 * NOTES
123 *
124 *
125 * MODIFICATION HISTORY
126 *
127 * 11-22-2002 Rekha Nalluri o Created.
128 *
129 */
130 FUNCTION get_geography_type(
131 p_geography_id IN NUMBER,
132 x_return_status IN OUT NOCOPY VARCHAR2
133 ) RETURN VARCHAR2 IS
134
135 l_geography_type VARCHAR2(30);
136
137 BEGIN
138
139 SELECT geography_type
140 INTO l_geography_type
141 FROM HZ_GEOGRAPHIES
142 WHERE geography_id = p_geography_id;
143
144 RETURN l_geography_type;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
149 fnd_message.set_token('FK','geography_id');
150 fnd_message.set_token('COLUMN', 'geography_id');
151 fnd_message.set_token('TABLE','HZ_GEOGRAPHIES');
152 fnd_msg_pub.add;
153 x_return_status := fnd_api.g_ret_sts_error;
154 END get_geography_type;
155
156
157 /**
158 * PROCEDURE validate_master_relation
159 *
160 * DESCRIPTION
161 * Validate the relationship record.
162 *
163 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
164 *
165 * ARGUMENTS
166 * IN:
167 * p_master_relation_rec Master relationship record
168 * p_create_update_flag Flag that indicates 'C' for create
169 * and 'U' for update
170 *
171 * IN/OUT:
172 * x_return_status Return status.
173 *
174 * NOTES
175 *
176 *
177 * MODIFICATION HISTORY
178 *
179 * 11-22-2002 Rekha Nalluri o Created.
180 *
181 */
182 PROCEDURE validate_master_relation (
183 p_master_relation_rec IN HZ_GEOGRAPHY_PUB.master_relation_rec_type,
184 p_create_update_flag IN VARCHAR2,
185 x_return_status IN OUT NOCOPY VARCHAR2
186 )IS
187
188 l_geography_type VARCHAR2(30);
189 l_parent_geography_type VARCHAR2(30);
190 l_count NUMBER;
191 l_start_date DATE;
192 l_country_code VARCHAR2(2);
193 l_end_date DATE;
194
195 BEGIN
196
197 -- Initialize start_date and end_date
198 l_start_date :=NULL;
199 l_end_date:=NULL;
200
201 IF p_create_update_flag = 'C' THEN
202 -- validate start_date and end_date
203 HZ_UTILITY_V2PUB.validate_start_end_date(
204 p_create_update_flag => p_create_update_flag,
205 p_start_date_column_name => 'start_date',
206 p_start_date => p_master_relation_rec.start_date,
207 p_old_start_date => l_start_date,
208 p_end_date_column_name => 'end_date',
209 p_end_date => p_master_relation_rec.end_date,
210 p_old_end_date => l_end_date,
211 x_return_status => x_return_status
212 );
213
214 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
215 RAISE FND_API.G_EXC_ERROR;
216 END IF;
217
218 END IF;
219
220 --dbms_output.put_line('In validate relatio after date validate');
221
222 -- validate geography_id and parent_geography_id
223 IF p_create_update_flag = 'C' THEN
224 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_id(
225 p_geography_id => p_master_relation_rec.geography_id,
226 p_master_ref_flag => 'Y',
227 x_return_status => x_return_status
228 );
229
230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
231 RAISE FND_API.G_EXC_ERROR;
232 END IF;
233
234 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_id(
235 p_geography_id => p_master_relation_rec.parent_geography_id,
236 p_master_ref_flag => 'Y',
237 x_return_status => x_return_status
238 );
239
240 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
241 RAISE FND_API.G_EXC_ERROR;
242 END IF;
243
244 END IF;
245
246 --dbms_output.put_line('In validate relation after geography_id validate');
247
248 IF p_create_update_flag = 'C' THEN
249 -- get geography_type and parent_geography_type
250 l_geography_type := get_geography_type(p_geography_id => p_master_relation_rec.geography_id,
251 x_return_status => x_return_status);
252 l_parent_geography_type := get_geography_type(p_geography_id => p_master_relation_rec.parent_geography_id,
253 x_return_status => x_return_status);
254
255 --dbms_output.put_line('In validate relation after get geography type');
256 -- get country code for geography_id
257
258 SELECT country_code INTO l_country_code
259 FROM HZ_GEOGRAPHIES
260 WHERE geography_id=p_master_relation_rec.parent_geography_id;
261 IF l_country_code IS NULL THEN
262 fnd_message.set_name('AR', 'HZ_GEO_NO_RECORD');
263 fnd_message.set_token('TOKEN1', 'country_code');
264 fnd_message.set_token('TOKEN2', 'geography_id '||p_master_relation_rec.parent_geography_id);
265 fnd_msg_pub.add;
266 x_return_status := fnd_api.g_ret_sts_error;
267
268 END IF;
269
270 -- validate whether geography_type is at lower level to parent_geography_type per that country structure
271 -- --dbms_output.put_line('before validate_structure');
272 validate_structure(
273 p_geography_type => l_geography_type,
274 p_parent_geography_type => l_parent_geography_type,
275 p_country_code => l_country_code,
276 x_return_status => x_return_status
277 );
278
279 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
280 ----dbms_output.put_line('x_return_status is '||x_return_status);
281 RAISE FND_API.G_EXC_ERROR;
282 END IF;
283
284 ----dbms_output.put_line('after validate_structure');
285 END IF;
286
287 --dbms_output.put_line('In validate relatio after structure validation');
288
289 IF p_create_update_flag = 'C' THEN
290
291 -- check whether geography_id is unique within parent_geography_id
292 SELECT count(*) INTO l_count
293 FROM HZ_RELATIONSHIPS
294 WHERE subject_id=p_master_relation_rec.parent_geography_id
295 AND object_id = p_master_relation_rec.geography_id
296 AND subject_type = l_parent_geography_type
297 AND object_type = l_geography_type
298 AND relationship_type='MASTER_REF'
299 AND status = 'A';
300
301 IF l_count > 0 THEN
302 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
303 fnd_message.set_token('COLUMN', 'geography_id');
304 fnd_msg_pub.add;
305 x_return_status := fnd_api.g_ret_sts_error;
306 END IF;
307 END IF;
308
309 END validate_master_relation;
310
311 /**
312 * PROCEDURE validate_geo_identifier
313 *
314 * DESCRIPTION
315 * Validate the geography identifier record.
316 *
317 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
318 *
319 * ARGUMENTS
320 * IN:
321 * p_geo_identifier_rec Geography Identifier record
322 * p_create_update_flag Flag that indicates 'C' for create
323 * and 'U' for update
324 *
325 * IN/OUT:
326 * x_return_status Return status.
327 *
328 * NOTES
329 *
330 *
331 * MODIFICATION HISTORY
332 *
333 * 12-03-2002 Rekha Nalluri o Created.
334 * 08-25-2005 Nishant Singhai o Modified for Bug 4549821. Added
335 * identifier_type check in WHERE clause to
336 * to verify if identifier value already exists
337 * in case of 'C'.
338 * 10-25-2005 Nishant Singhai Modified for Bug 4578867 (for NAME, if anything other than
339 * STANDARD_NAME is used raise error)
340 *
341 */
342
343 PROCEDURE validate_geo_identifier (
344 p_geo_identifier_rec IN HZ_GEOGRAPHY_PUB.geo_identifier_rec_type,
345 p_create_update_flag IN VARCHAR2,
346 x_return_status IN OUT NOCOPY VARCHAR2
347 )IS
348
349 l_count NUMBER;
350
351 BEGIN
352
353 IF p_create_update_flag = 'C' THEN
354 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_id(
355 p_geography_id => p_geo_identifier_rec.geography_id,
356 p_master_ref_flag => 'N',
357 x_return_status => x_return_status
358 );
359
360 -- validate identifier_subtype,identifier_type and geo_data_provider lookups
361
362 IF p_geo_identifier_rec.identifier_type='CODE' THEN
363
364 HZ_UTILITY_V2PUB.validate_lookup(
365 p_column => 'geography_code_type',
366 p_lookup_type => 'HZ_GEO_IDENTIFIER_SUBTYPE',
367 p_column_value => p_geo_identifier_rec.identifier_subtype,
368 x_return_status => x_return_status
369 );
370
371 -- Added by Nishant on 25-Oct-2005 for Bug 4578867 (Since STANDARD_NAME lookup
372 -- is being end-dated, for identifier type =NAME, there will be only 1
373 -- identifier_subtype, which is STANDARD_NAME. So, for NAME, if anything other than
374 -- STANDARD_NAME is used raise error.
375 ELSIF p_geo_identifier_rec.identifier_type='NAME' THEN
376 IF (p_geo_identifier_rec.identifier_subtype <> 'STANDARD_NAME') THEN
377 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
378 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'identifier_subtype' );
379 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', 'HZ_GEO_IDENTIFIER_SUBTYPE' );
380 FND_MSG_PUB.ADD;
381 x_return_status := FND_API.G_RET_STS_ERROR;
382 END IF;
383 -- this will not be called as only NAME and CODE are valid identifier type
384 -- but keeping it as 'catch all' condition to validate what is passed in
385 ELSE
386 HZ_UTILITY_V2PUB.validate_lookup(
387 p_column => 'identifier_subtype',
388 p_lookup_type => 'HZ_GEO_IDENTIFIER_SUBTYPE',
389 p_column_value => p_geo_identifier_rec.identifier_subtype,
390 x_return_status => x_return_status
391 );
392 END IF;
393
394 HZ_UTILITY_V2PUB.validate_lookup(
395 p_column => 'identifier_type',
396 p_lookup_type => 'HZ_GEO_IDENTIFIER_TYPE',
397 p_column_value => p_geo_identifier_rec.identifier_type,
398 x_return_status => x_return_status
399 );
400
401
402 -- language_code must be FK to fnd_languages
403 IF p_geo_identifier_rec.language_code IS NOT NULL THEN
404 SELECT count(*) INTO l_count
405 FROM fnd_languages
406 WHERE language_code = p_geo_identifier_rec.language_code
407 AND rownum <2;
408
409 IF l_count = 0 THEN
410 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
414 fnd_msg_pub.add;
411 fnd_message.set_token('FK','language_code');
412 fnd_message.set_token('COLUMN', 'language_code');
413 fnd_message.set_token('TABLE','FND_LANGUAGES');
415 x_return_status := fnd_api.g_ret_sts_error;
416 END IF;
417 END IF;
418 END IF;
419
420 /* If p_create_update_flag = 'C' THEN
421 -- check the uniqueness for the combination of geography_id,identifier_type,
422 -- identifier_subtype,identifier_value and language_code
423 SELECT count(*) INTO l_count
424 FROM HZ_GEOGRAPHY_IDENTIFIERS
425 WHERE geography_id=p_geo_identifier_rec.geography_id
426 AND identifier_type=p_geo_identifier_rec.identifier_type
427 AND identifier_subtype=p_geo_identifier_rec.identifier_subtype
428 AND identifier_value=p_geo_identifier_rec.identifier_value
429 AND language_code = p_geo_identifier_rec.language_code;
430
431 IF l_count > 0 THEN
432 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
433 fnd_message.set_token('COLUMN', 'geography_id,identifier_type,identifier_subtype,identifier_value,language_code');
434 fnd_msg_pub.add;
435 x_return_status := fnd_api.g_ret_sts_error;
436 END IF;
437 END IF; */
438
439 HZ_UTILITY_V2PUB.validate_lookup(
440 p_column => 'geo_data_provider',
441 p_lookup_type => 'HZ_GEO_DATA_PROVIDER',
442 p_column_value => p_geo_identifier_rec.geo_data_provider,
443 x_return_status => x_return_status
444 );
445
446 IF p_create_update_flag = 'C' THEN
447 -- Bug 4591502 : ISSUE # 16 : validate only in create
448
449 hz_utility_v2pub.validate_created_by_module(
450 p_create_update_flag => 'C',
451 p_created_by_module => p_geo_identifier_rec.created_by_module,
452 p_old_created_by_module => null,
453 x_return_status => x_return_status);
454
455 IF p_geo_identifier_rec.identifier_type='NAME' THEN
456 -- check if name is unique for a geography_id and identifier type, with in that language_code
457 -- identifier type check added in WHERE clause by NSINGHAI on 25-Aug-2005 for Bug 4549821
458 SELECT count(*) INTO l_count
459 FROM HZ_GEOGRAPHY_IDENTIFIERS
460 WHERE geography_id=p_geo_identifier_rec.geography_id
461 AND language_code = UPPER(p_geo_identifier_rec.language_code)
462 AND UPPER(identifier_value) = UPPER(p_geo_identifier_rec.identifier_value)
463 AND identifier_type = p_geo_identifier_rec.identifier_type
464 AND rownum <2;
465
466 IF l_count > 0 THEN
467 fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
468 fnd_message.set_token('COLUMN', 'identifier_value within the identifier_type NAME and language code '||p_geo_identifier_rec.language_code);
469 fnd_msg_pub.add;
470 x_return_status := fnd_api.g_ret_sts_error;
471 END IF;
472 END IF;
473 END IF;
474
475 IF p_create_update_flag = 'U' THEN
476 --check if the row exists
477 SELECT count(*) INTO l_count
478 FROM hz_geography_identifiers
479 WHERE geography_id = p_geo_identifier_rec.geography_id
480 AND identifier_type = p_geo_identifier_rec.identifier_type
481 AND identifier_subtype = p_geo_identifier_rec.identifier_subtype
482 AND identifier_value = p_geo_identifier_rec.identifier_value
483 AND language_code = p_geo_identifier_rec.language_code
484 ;
485
486 IF l_count = 0 THEN
487 FND_MESSAGE.SET_NAME('AR', 'HZ_GEO_NO_RECORD');
488 FND_MESSAGE.SET_TOKEN('TOKEN1', 'geography_identifier');
489 FND_MESSAGE.SET_TOKEN('TOKEN2', 'geography_id '||p_geo_identifier_rec.geography_id||',identifier_type '||p_geo_identifier_rec.identifier_type||
490 ', identifier_subtype '||p_geo_identifier_rec.identifier_subtype||', identifier_value '||p_geo_identifier_rec.identifier_value||
491 ', language_code '||p_geo_identifier_rec.language_code);
492 FND_MSG_PUB.ADD;
493 x_return_status := fnd_api.g_ret_sts_error;
494 END IF;
495 END IF;
496
497
498 END validate_geo_identifier;
499
500 /**
501 * PROCEDURE validate_master_geography
502 *
503 * DESCRIPTION
504 * Validate the master geography record.
505 *
506 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
507 *
508 * ARGUMENTS
509 * IN:
510 * p_master_geography_rec Master Geography record
511 * p_create_update_flag Flag that indicates 'C' for create
512 * and 'U' for update
513 *
514 * IN/OUT:
515 * x_return_status Return status.
516 *
517 * NOTES
518 *
519 *
520 * MODIFICATION HISTORY
521 *
522 * 12-09-2002 Rekha Nalluri o Created.
523 *
524 */
525
526 PROCEDURE validate_master_geography (
527 p_master_geography_rec IN HZ_GEOGRAPHY_PUB.master_geography_rec_type,
528 p_create_update_flag IN VARCHAR2,
529 x_return_status IN OUT NOCOPY VARCHAR2
533 l_parent_geography_tbl HZ_GEOGRAPHY_PUB.parent_geography_tbl_type;
530 ) IS
531
532 l_count NUMBER;
534 l_start_date DATE;
535 l_end_date DATE;
536 l_last NUMBER;
537 --l_geography_type VARCHAR2(30);
538
539
540 BEGIN
541
542 l_parent_geography_tbl := p_master_geography_rec.parent_geography_id;
543
544 -- Initialize start_date and end_date
545 l_start_date :=NULL;
546 l_end_date:=NULL;
547
548
549 -- check whether end_date >= start_date
550 HZ_UTILITY_V2PUB.validate_start_end_date(
551 p_create_update_flag => p_create_update_flag,
552 p_start_date_column_name => 'start_date',
553 p_start_date => p_master_geography_rec.start_date,
554 p_old_start_date => l_start_date,
555 p_end_date_column_name => 'end_date',
556 p_end_date => p_master_geography_rec.end_date,
557 p_old_end_date => l_end_date,
558 x_return_status => x_return_status
559 );
560
561 --dbms_output.put_line('In validate, after date validation '||x_return_status);
562 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
563 RAISE FND_API.G_EXC_ERROR;
564 END IF;
565
566
567 IF p_create_update_flag = 'C' THEN
568 -- validate geography_type
569 HZ_GEO_STRUCTURE_VALIDATE_PVT.validate_geography_type(
570 p_geography_type => p_master_geography_rec.geography_type,
571 p_master_ref_flag => 'Y',
572 x_return_status => x_return_status
573 );
574
575 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
576 RAISE FND_API.G_EXC_ERROR;
577 END IF;
578 -- validate geography name for mandatory
579 HZ_UTILITY_V2PUB.validate_mandatory (
580 p_create_update_flag =>'C',
581 p_column => 'geography_name',
582 p_column_value => p_master_geography_rec.geography_name,
583 p_restricted => 'N',
584 x_return_status => x_return_status
585 );
586
587 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590
591 hz_utility_v2pub.validate_created_by_module(
592 p_create_update_flag => 'C',
593 p_created_by_module => p_master_geography_rec.created_by_module,
594 p_old_created_by_module => null,
595 x_return_status => x_return_status);
596
597 END IF;
598
599 -- geography_code_type is mandatory if geography_code is NOT NULL
600 IF (p_master_geography_rec.geography_code IS NOT NULL AND p_master_geography_rec.geography_code_type IS NULL) THEN
601 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
602 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'geography_code_type' );
603 FND_MSG_PUB.ADD;
604 x_return_status := FND_API.G_RET_STS_ERROR;
605 END IF;
606
607
608 --dbms_output.put_line('In valiadte, after geography_code_type validation');
609
610 -- validate timezone_code for FK to FND_TIMEZONES
611 IF p_master_geography_rec.timezone_code IS NOT NULL THEN
612
613 SELECT count(*) INTO l_count
614 FROM FND_TIMEZONES_B
615 WHERE timezone_code = p_master_geography_rec.timezone_code;
616
617 IF l_count = 0 THEN
618 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
619 fnd_message.set_token('FK', 'timezone_code');
620 fnd_message.set_token('COLUMN','timezone_code');
621 fnd_message.set_token('TABLE','FND_TIMEZONES_B');
622 fnd_msg_pub.add;
623 x_return_status := fnd_api.g_ret_sts_error;
624 END IF;
625 END IF;
626
627 -- language_code must be FK to fnd_languages
628 IF p_master_geography_rec.language_code IS NOT NULL THEN
629 SELECT count(*) INTO l_count
630 FROM fnd_languages
631 WHERE language_code = UPPER(p_master_geography_rec.language_code)
632 AND rownum <2;
633 IF l_count = 0 THEN
634 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
635 fnd_message.set_token('FK', 'language_code');
636 fnd_message.set_token('COLUMN','language_code');
637 fnd_message.set_token('TABLE','FND_LANGUAGES');
638 fnd_msg_pub.add;
639 x_return_status := fnd_api.g_ret_sts_error;
640 END IF;
641 END IF;
642
646 FROM hz_geographies
643 --validate for duplicate country
644 IF p_master_geography_rec.geography_type = 'COUNTRY' THEN
645 SELECT count(*) INTO l_count
647 WHERE geography_code=p_master_geography_rec.geography_code
648 AND geography_type='COUNTRY';
649
650 IF l_count > 0 THEN
651 fnd_message.set_name('AR', 'HZ_GEO_DUPLICATE_GEOG_CODE');
652 fnd_message.set_token('COUNTRY_CODE', p_master_geography_rec.geography_code);
653 fnd_msg_pub.add;
654 x_return_status := fnd_api.g_ret_sts_error;
655 END IF;
656 END IF;
657
658
659 -- validate parent_geography_id
660 l_last := l_parent_geography_tbl.last;
661 IF l_last > 0 THEN
662 FOR i in 1 .. l_last loop
663 IF l_parent_geography_tbl.exists(i)= TRUE THEN
664 hz_geo_structure_validate_pvt.validate_geography_id (
665 p_geography_id => l_parent_geography_tbl(i),
666 p_master_ref_flag => 'Y',
667 x_return_status => x_return_status);
668 END IF;
669 END LOOP;
670 END IF;
671 -- validate geography_code for FK to FND_TERRITORIES if geography_type is 'COUNTRY'
672 IF p_master_geography_rec.geography_type = 'COUNTRY' THEN
673 SELECT count(*) INTO l_count
674 FROM FND_TERRITORIES
675 WHERE territory_code = UPPER(p_master_geography_rec.geography_code);
676 IF l_count = 0 THEN
677 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
678 fnd_message.set_token('FK','territory_code');
679 fnd_message.set_token('COLUMN', 'geography_code');
680 fnd_message.set_token('TABLE','FND_TERRITORIES');
681 fnd_msg_pub.add;
682 x_return_status := fnd_api.g_ret_sts_error;
683 END IF;
684 END IF;
685
686 -- if geography_type <> 'COUNTRY' then atleast one parent should be passed.
687 IF p_master_geography_rec.geography_type <> 'COUNTRY' THEN
688 --dbms_output.put_line('parent count is '||to_char(l_parent_geography_tbl.count));
689
690 IF l_parent_geography_tbl.count = 0 THEN
691 fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
692 fnd_message.set_token('COLUMN', 'parent_geography_id');
693 fnd_msg_pub.add;
694 x_return_status := fnd_api.g_ret_sts_error;
695 END IF;
696 END IF;
697
698 END validate_master_geography;
699
700 /**
701 * PROCEDURE validate_geography_range
702 *
703 * DESCRIPTION
704 * Validates the geography range record.
705 *
706 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
707 *
708 * ARGUMENTS
709 * IN:
710 * p_geography_range_rec Geography range record
711 * p_create_update_flag Flag that indicates 'C' for create
712 * and 'U' for update
713 *
714 * IN/OUT:
715 * x_return_status Return status.
716 *
717 * NOTES
718 *
719 *
720 * MODIFICATION HISTORY
721 *
722 * 01-20-2003 Rekha Nalluri o Created.
723 *
724 */
725
726 PROCEDURE validate_geography_range (
727 p_geography_range_rec IN HZ_GEOGRAPHY_PUB.geography_range_rec_type,
728 p_create_update_flag IN VARCHAR2,
729 x_return_status IN OUT NOCOPY VARCHAR2
730 ) IS
731
735 BEGIN
732 l_count NUMBER;
733
734
736
737 -- validate for mandatory columns
738 HZ_UTILITY_V2PUB.validate_mandatory (
739 p_create_update_flag =>p_create_update_flag,
740 p_column => 'zone_id',
741 p_column_value => p_geography_range_rec.zone_id,
742 p_restricted => 'N',
743 x_return_status => x_return_status
744 );
745
746
747 HZ_UTILITY_V2PUB.validate_mandatory (
748 p_create_update_flag =>p_create_update_flag,
749 p_column => 'geography_from',
750 p_column_value => p_geography_range_rec.geography_from,
751 p_restricted => 'N',
752 x_return_status => x_return_status
753 );
754
755 IF p_create_update_flag = 'C' THEN
756 HZ_UTILITY_V2PUB.validate_mandatory (
757 p_create_update_flag =>p_create_update_flag,
758 p_column => 'master_ref_geography_id',
759 p_column_value => p_geography_range_rec.master_ref_geography_id,
760 p_restricted => 'N',
761 x_return_status => x_return_status
762 );
763
764 HZ_UTILITY_V2PUB.validate_mandatory (
765 p_create_update_flag =>p_create_update_flag,
766 p_column => 'geography_to',
767 p_column_value => p_geography_range_rec.geography_to,
768 p_restricted => 'N',
769 x_return_status => x_return_status
770 );
771
772 HZ_UTILITY_V2PUB.validate_mandatory (
773 p_create_update_flag =>p_create_update_flag,
774 p_column => 'identifier_type',
775 p_column_value => p_geography_range_rec.identifier_type,
776 p_restricted => 'N',
777 x_return_status => x_return_status
778 );
779
780 hz_utility_v2pub.validate_created_by_module(
781 p_create_update_flag => 'C',
782 p_created_by_module => p_geography_range_rec.created_by_module,
783 p_old_created_by_module => null,
784 x_return_status => x_return_status);
785
786 END IF;
787
788 IF p_create_update_flag = 'U' THEN
789
790 HZ_UTILITY_V2PUB.validate_mandatory (
791 p_create_update_flag =>'U',
792 p_column => 'start_date',
793 p_column_value => p_geography_range_rec.start_date,
794 p_restricted => 'N',
795 x_return_status => x_return_status
796 );
797
798 HZ_UTILITY_V2PUB.validate_mandatory (
799 p_create_update_flag =>'U',
800 p_column => 'end_date',
801 p_column_value => p_geography_range_rec.end_date,
802 p_restricted => 'N',
803 x_return_status => x_return_status
804 );
805
806 END IF;
807
808
809 IF p_create_update_flag = 'C' THEN
810 -- validate for start_date and end_date
811 HZ_UTILITY_V2PUB.validate_start_end_date(
812 p_create_update_flag => p_create_update_flag,
813 p_start_date_column_name => 'start_date',
814 p_start_date => p_geography_range_rec.start_date,
815 p_old_start_date => NULL,
816 p_end_date_column_name => 'end_date',
817 p_end_date => p_geography_range_rec.end_date,
818 p_old_end_date => NULL,
819 x_return_status => x_return_status
820 );
821
822 END IF;
823
824 -- Added the below begin and exception to fix the bug # 4670425
825 -- If geography_from and geography_to are both numbers then it will execute the first part.
826 -- If geography_from and geography_to are alpha numeric, it will execute the exception part.
827 BEGIN
828 -- geography_to must be greater than or equal to geography_from
829 IF to_number(p_geography_range_rec.geography_from) > to_number(p_geography_range_rec.geography_to) THEN
830 fnd_message.set_name('AR', 'HZ_GEO_INVALID_RANGE');
831 fnd_msg_pub.add;
832 x_return_status := fnd_api.g_ret_sts_error;
833 END IF;
834 EXCEPTION WHEN VALUE_ERROR THEN
835 -- geography_to must be greater than or equal to geography_from
836 IF p_geography_range_rec.geography_from > p_geography_range_rec.geography_to THEN
837 fnd_message.set_name('AR', 'HZ_GEO_INVALID_RANGE');
838 fnd_msg_pub.add;
839 x_return_status := fnd_api.g_ret_sts_error;
840 END IF;
841 END;
842
843 -- validate zone_id
844 BEGIN
845 SELECT 1 INTO l_count
846 FROM hz_geographies
847 WHERE geography_id = p_geography_range_rec.zone_id
848 AND geography_use <> 'MASTER_REF';
849
850
851 EXCEPTION WHEN NO_DATA_FOUND THEN
852 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
853 fnd_message.set_token('FK','geography_id');
854 fnd_message.set_token('COLUMN', 'zone_id');
855 fnd_message.set_token('TABLE','HZ_GEOGRAPHIES');
856 fnd_msg_pub.add;
857 x_return_status := fnd_api.g_ret_sts_error;
858 END;
859
860 --validate master_ref_geography_id
861 --master_ref_geography_id is mandatory only in create
862 IF p_create_update_flag = 'C' THEN
863 BEGIN
864
865 SELECT 1 INTO l_count
866 FROM hz_geographies
867 WHERE geography_id = p_geography_range_rec.master_ref_geography_id
868 AND geography_use = 'MASTER_REF';
869
870
871 EXCEPTION WHEN NO_DATA_FOUND THEN
872 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
873 fnd_message.set_token('FK','geography_id');
874 fnd_message.set_token('COLUMN', 'master_ref_geography_id');
875 fnd_message.set_token('TABLE','HZ_GEOGRAPHIES');
876 fnd_msg_pub.add;
877 x_return_status := fnd_api.g_ret_sts_error;
878 END;
879 END IF;
880
881 END validate_geography_range;
882
883
884 /**
885 * PROCEDURE validate_zone_relation
886 *
887 * DESCRIPTION
888 * Validates the zone relation record.
889 *
890 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
891 *
892 * ARGUMENTS
893 * IN:i
894 * p_geography_id Geography id
895 * p_zone_relation_tbl Zone relation table of records
896 * p_create_update_flag Flag that indicates 'C' for create
897 * and 'U' for update
898 *
899 * IN/OUT:
900 * x_return_status Return status.
901 *
902 * NOTES
903 *
904 *
905 * MODIFICATION HISTORY
906 *
907 * 01-24-2003 Rekha Nalluri o Created.
908 *
909 */
910
911 PROCEDURE validate_zone_relation (
912 p_zone_relation_rec IN ZONE_RELATION_REC_TYPE,
913 p_create_update_flag IN VARCHAR2,
914 x_return_status IN OUT NOCOPY VARCHAR2
915 ) IS
916
917 l_count NUMBER;
918
919 BEGIN
920
921
922 -- validate start_date and end_date
923 HZ_UTILITY_V2PUB.validate_start_end_date(
924 p_create_update_flag => p_create_update_flag,
925 p_start_date_column_name => 'start_date',
926 p_start_date => p_zone_relation_rec.start_date,
927 p_old_start_date => NULL,
928 p_end_date_column_name => 'end_date',
929 p_end_date => p_zone_relation_rec.end_date,
930 p_old_end_date => NULL,
931 x_return_status => x_return_status
932 );
933
934
935 -- validate geography_id
936 SELECT count(*) INTO l_count
937 FROM hz_geographies
938 WHERE geography_id = p_zone_relation_rec.geography_id;
939
940 IF l_count = 0 THEN
941 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
942 fnd_message.set_token('FK', 'geography_id');
943 fnd_message.set_token('COLUMN','geography_id');
944 fnd_message.set_token('TABLE','HZ_GEOGRAPHIES');
945 fnd_msg_pub.add;
946 x_return_status := fnd_api.g_ret_sts_error;
947 END IF;
948
949 --validate included_geography_id
950 SELECT count(*) INTO l_count
951 FROM hz_geographies
952 WHERE geography_id = p_zone_relation_rec.included_geography_id;
953
954 IF l_count = 0 THEN
955 fnd_message.set_name('AR', 'HZ_GEO_INVALID_VALUE');
956 fnd_message.set_token('VALUE',p_zone_relation_rec.included_geography_id);
957 fnd_message.set_token('COLUMN', 'included_geography_id');
958 fnd_msg_pub.add;
959 x_return_status := fnd_api.g_ret_sts_error;
960 END IF;
961 END validate_zone_relation;
962
963
964 END HZ_GEOGRAPHY_VALIDATE_PVT;