1 PACKAGE BODY HZ_UTILITY_V2PUB AS
2 /*$Header: ARH2UTSB.pls 120.45 2008/02/05 20:02:33 awu ship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 G_SPECIAL_STRING CONSTANT VARCHAR2(4):= '%#@*';
9 G_LENGTH CONSTANT NUMBER := LENGTHB( G_SPECIAL_STRING );
10
11 TYPE VAL_TAB_TYPE IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
12
13 -- file handler we will use for log file.
14 G_FILE UTL_FILE.FILE_TYPE;
15
16 -- running in file debug mode.
17 G_FILE_DEBUG BOOLEAN := FALSE;
18 G_FILE_NAME VARCHAR2(100);
19 G_FILE_PATH VARCHAR2(200);
20
21 -- running in normal debug mode by calling dbms_output.
22 G_DBMS_DEBUG BOOLEAN := FALSE;
23
24 -- buffer size used by dbms_output.debug
25 G_BUFFER_SIZE CONSTANT NUMBER := 1000000;
26 G_MAX_LINE_SIZE_OF_FILE CONSTANT NUMBER := 1023;
27 G_MAX_LINE_SIZE_OF_DBMS CONSTANT NUMBER := 255;
28
29 -- level of debug has been called.
30 G_COUNT NUMBER := 0;
31
32 --------------------------------------
33 -- define the internal table that will cache values
34 --------------------------------------
35
36 VAL_TAB VAL_TAB_TYPE; -- the table of values
37 TABLE_SIZE BINARY_INTEGER := 2048; -- the size of above tables
38 LOOKUP_MEANING_TAB VAL_TAB_TYPE;
39
40 --------------------------------------
41 -- declaration of private procedures and functions
42 --------------------------------------
43
44 FUNCTION get_index (
45 p_val IN VARCHAR2
46 ) RETURN BINARY_INTEGER;
47
48 FUNCTION put (
49 p_val IN VARCHAR2
50 ) RETURN BINARY_INTEGER;
51
52 FUNCTION search (
53 p_val IN VARCHAR2,
54 p_category IN VARCHAR2
55 ) RETURN BOOLEAN;
56
57 FUNCTION search (
58 p_val IN VARCHAR2,
59 p_category IN VARCHAR2,
60 x_lookup_meaning OUT NOCOPY VARCHAR2,
61 p_calling_proc IN VARCHAR2
62 ) RETURN BOOLEAN;
63
64 PROCEDURE enable_file_debug;
65
66 --------------------------------------
67 -- private procedures and functions
68 --------------------------------------
69
70 /**
71 * PRIVATE FUNCTION get_index
72 *
73 * DESCRIPTION
74 * Gets index in caching table for a specified value.
75 *
76 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
77 *
78 * ARGUMENTS
79 * IN:
80 * p_val Specified value.
81 *
82 * NOTES
83 *
84 * MODIFICATION HISTORY
85 *
86 * 07-23-2001 Jianying Huang o Created.
87 *
88 */
89
90 FUNCTION get_index (
91 p_val IN VARCHAR2
92 ) RETURN BINARY_INTEGER IS
93
94 l_table_index BINARY_INTEGER;
95 l_found BOOLEAN := FALSE;
96 l_hash_value NUMBER;
97
98 BEGIN
99
100 l_table_index := DBMS_UTILITY.get_hash_value( p_val, 1, TABLE_SIZE );
101
102 IF VAL_TAB.EXISTS(l_table_index) THEN
103 IF VAL_TAB(l_table_index) = p_val THEN
104 RETURN l_table_index;
105 ELSE
106 l_hash_value := l_table_index;
107 l_table_index := l_table_index + 1;
108 l_found := FALSE;
109
110 WHILE ( l_table_index < TABLE_SIZE ) AND ( NOT l_found ) LOOP
111 IF VAL_TAB.EXISTS(l_table_index) THEN
112 IF VAL_TAB(l_table_index) = p_val THEN
113 l_found := TRUE;
114 ELSE
115 l_table_index := l_table_index + 1;
116 END IF;
117 ELSE
118 RETURN TABLE_SIZE + 1;
119 END IF;
120 END LOOP;
121
122 IF NOT l_found THEN -- Didn't find any till the end
123 l_table_index := 1; -- Start from the beginning
124
125 WHILE ( l_table_index < l_hash_value ) AND ( NOT l_found ) LOOP
126 IF VAL_TAB.EXISTS(l_table_index) THEN
127 IF VAL_TAB(l_table_index) = p_val THEN
128 l_found := TRUE;
129 ELSE
130 l_table_index := l_table_index + 1;
131 END IF;
132 ELSE
133 RETURN TABLE_SIZE + 1;
134 END IF;
135 END LOOP;
136 END IF;
137
138 IF NOT l_found THEN
139 RETURN TABLE_SIZE + 1; -- Return a higher value
140 END IF;
141 END IF;
142 ELSE
143 RETURN TABLE_SIZE + 1;
144 END IF;
145
146 RETURN l_table_index;
147
148 EXCEPTION
149 WHEN OTHERS THEN -- The entry doesn't exists
150 RETURN TABLE_SIZE + 1;
151
152 END get_index;
153
154 /**
155 * PRIVATE FUNCTION put
156 *
157 * DESCRIPTION
158 * Put value in caching table and return table index.
159 *
160 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
161 *
162 * ARGUMENTS
163 * IN:
164 * p_val Specified value.
165 *
166 * NOTES
167 *
168 * MODIFICATION HISTORY
169 *
170 * 07-23-2001 Jianying Huang o Created.
171 * 03-04-2002 Jianying Huang o Modified the procedure to be a function
172 * to return table index.
173 *
174 */
175
176 FUNCTION put (
177 p_val IN VARCHAR2
178 ) RETURN BINARY_INTEGER IS
179
180 l_table_index BINARY_INTEGER;
181 l_stored BOOLEAN := FALSE;
182 l_hash_value NUMBER;
183
184 BEGIN
185
186 l_table_index := DBMS_UTILITY.get_hash_value( p_val, 1, TABLE_SIZE );
187
188 IF VAL_TAB.EXISTS(l_table_index) THEN
189 IF VAL_TAB(l_table_index) <> p_val THEN --Collision
190 l_hash_value := l_table_index;
191 l_table_index := l_table_index + 1;
192
193 WHILE (l_table_index < TABLE_SIZE) AND (NOT l_stored) LOOP
194 IF VAL_TAB.EXISTS(l_table_index) THEN
195 IF VAL_TAB(l_table_index) <> p_val THEN
196 l_table_index := l_table_index + 1;
197 END IF;
198 ELSE
199 VAL_TAB(l_table_index) := p_val;
200 l_stored := TRUE;
201 END IF;
202 END LOOP;
203
204 IF NOT l_stored THEN --Didn't find any free bucket till the end
205 l_table_index := 1;
206
207 WHILE (l_table_index < l_hash_value) AND (NOT l_stored) LOOP
208 IF VAL_TAB.EXISTS(l_table_index) THEN
209 IF VAL_TAB(l_table_index) <> p_val THEN
210 l_table_index := l_table_index + 1;
211 END IF;
212 ELSE
213 VAL_TAB(l_table_index) := p_val;
214 l_stored := TRUE;
215 END IF;
216 END LOOP;
217 END IF;
218
219 END IF;
220 ELSE
221 VAL_TAB(l_table_index) := p_val;
222 END IF;
223
224 RETURN l_table_index;
225 EXCEPTION
226 WHEN OTHERS THEN
227 NULL;
228
229 END put;
230
231 /**
232 * PRIVATE FUNCTION search
233 *
234 * DESCRIPTION
235 * Find value with a specified category, for instance, lookup.
236 *
237 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
238 *
239 * ARGUMENTS
240 * IN:
241 * p_val Specified value.
242 * p_category Value category. We only support
243 * category LOOKUP for now.
244 * OUT:
245 * x_lookup_meaning Lookup Meaning
246 *
247 * NOTES
248 *
249 * MODIFICATION HISTORY
250 *
251 * 07-23-2001 Jianying Huang o Created.
252 * 03-04-2002 Jianying Huang o Added new parameter to return
253 * lookup meaning.
254 *
255 */
256
257 FUNCTION search (
258 p_val IN VARCHAR2,
259 p_category IN VARCHAR2,
260 x_lookup_meaning OUT NOCOPY VARCHAR2,
261 p_calling_proc IN VARCHAR2
262 ) RETURN BOOLEAN IS
263
264 l_table_index BINARY_INTEGER;
265 l_return BOOLEAN;
266
267 l_dummy VARCHAR2(1);
268 l_position1 NUMBER;
269 l_position2 NUMBER;
270
271 l_lookup_table VARCHAR2(30);
272 l_lookup_type AR_LOOKUPS.lookup_type%TYPE;
273 l_lookup_code AR_LOOKUPS.lookup_code%TYPE;
274
275 l_relationship_type VARCHAR2(30);
276 l_incl_unrelated_entities VARCHAR2(1);
277
278 -- Bug 5398089 : Added variables
279 l_string VARCHAR2(255);
280 l_cache BOOLEAN;
281
282 BEGIN
283
284 -- search for the value
285 l_table_index := get_index( p_val || G_SPECIAL_STRING || p_category );
286 l_cache := TRUE;
287
288 IF l_table_index < table_size THEN
289 IF p_calling_proc <> 'Get_LookupMeaning' THEN
290 RETURN TRUE;
291 ELSE
292 -- Bug 5398089 : conisder language for lookup meaning
293 IF p_category = 'LOOKUP' THEN
294 l_string := LOOKUP_MEANING_TAB(l_table_index);
295 l_position1 := INSTRB( l_string, G_SPECIAL_STRING, 1, 1 );
296 IF SUBSTRB( l_string, l_position1 + G_LENGTH ) = userenv('LANG') THEN
297 -- This stores the meaning
298 x_lookup_meaning := SUBSTRB( l_string, 1, l_position1 - 1 );
299 RETURN TRUE;
300 END IF;
301 l_cache := FALSE;
302 END IF;
303 END IF;
304 END IF;
305 --Can't find the value in the table; look in the database
306 IF p_category = 'LOOKUP' THEN
307
308 l_position1 := INSTRB( p_val, G_SPECIAL_STRING, 1, 1 );
309 l_lookup_table := SUBSTRB( p_val, 1, l_position1 - 1 );
310 l_position2 := INSTRB( p_val, G_SPECIAL_STRING, 1, 2 );
311 l_lookup_type := SUBSTRB( p_val, l_position1 + G_LENGTH,
312 l_position2 - l_position1 - G_LENGTH );
313 l_lookup_code := SUBSTRB( p_val, l_position2 + G_LENGTH );
314
315 IF UPPER( l_lookup_table ) = 'AR_LOOKUPS' THEN
316 BEGIN
317 SELECT meaning INTO x_lookup_meaning
318 FROM AR_LOOKUPS
319 WHERE LOOKUP_TYPE = l_lookup_type
320 AND LOOKUP_CODE = l_lookup_code
321 AND ( ENABLED_FLAG = 'Y' AND
322 TRUNC( SYSDATE ) BETWEEN
323 TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
324 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
325
326 l_return := TRUE;
327 EXCEPTION
328 WHEN NO_DATA_FOUND THEN
329 l_return := FALSE;
330 END;
331 ELSIF UPPER( l_lookup_table ) = 'SO_LOOKUPS' THEN
332 BEGIN
333 SELECT meaning INTO x_lookup_meaning
334 FROM SO_LOOKUPS
335 WHERE LOOKUP_TYPE = l_lookup_type
336 AND LOOKUP_CODE = l_lookup_code
337 AND ( ENABLED_FLAG = 'Y' AND
338 TRUNC( SYSDATE ) BETWEEN
339 TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
340 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) );
341
342 l_return := TRUE;
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 l_return := FALSE;
346 END;
347 ELSIF UPPER( l_lookup_table ) = 'OE_SHIP_METHODS_V' THEN
348 BEGIN
349 SELECT meaning INTO x_lookup_meaning
350 FROM OE_SHIP_METHODS_V
351 WHERE LOOKUP_TYPE = l_lookup_type
352 AND LOOKUP_CODE = l_lookup_code
353 AND ( ENABLED_FLAG = 'Y' AND
354 TRUNC( SYSDATE ) BETWEEN
355 TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
356 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
357 AND ROWNUM = 1;
358
359 l_return := TRUE;
360 EXCEPTION
361 WHEN NO_DATA_FOUND THEN
362 l_return := FALSE;
363 END;
364 ELSIF UPPER( l_lookup_table ) = 'FND_LOOKUP_VALUES' THEN
365 BEGIN
366 SELECT meaning INTO x_lookup_meaning
367 FROM FND_LOOKUP_VALUES
368 WHERE LOOKUP_TYPE = l_lookup_type
369 AND LOOKUP_CODE = l_lookup_code
370 AND ( ENABLED_FLAG = 'Y' AND
371 TRUNC( SYSDATE ) BETWEEN
372 TRUNC(NVL( START_DATE_ACTIVE,SYSDATE ) ) AND
373 TRUNC(NVL( END_DATE_ACTIVE,SYSDATE ) ) )
374 -- Bug 4112157
375 --AND ROWNUM = 1;
376 AND LANGUAGE= userenv('LANG')
377 AND VIEW_APPLICATION_ID=222
378 AND SECURITY_GROUP_ID=0;
379
380 l_return := TRUE;
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 l_return := FALSE;
384 END;
385 ELSIF UPPER( l_lookup_table ) = 'FND_LANGUAGES' THEN
386 BEGIN
387 SELECT nls_language INTO x_lookup_meaning
388 FROM FND_LANGUAGES
389 WHERE LANGUAGE_CODE = l_lookup_code;
390
391 l_return := TRUE;
392 EXCEPTION
393 WHEN NO_DATA_FOUND THEN
394 l_return := FALSE;
395 END;
396
397 ELSE
398 l_return := FALSE;
399 END IF;
400
401 -- added the following section for caching of incl_unrelated_entities
402 -- column value for hz_relationship_types records.
403 ELSIF p_category = 'RELATIONSHIP_TYPE' THEN
404
405 l_position1 := INSTRB( p_val, G_SPECIAL_STRING, 1, 1 );
406 l_relationship_type := SUBSTRB( p_val, 1, l_position1 - 1 );
407
408 BEGIN
409 SELECT INCL_UNRELATED_ENTITIES INTO l_dummy
410 FROM HZ_RELATIONSHIP_TYPES
411 WHERE RELATIONSHIP_TYPE = l_relationship_type
412 AND ROWNUM = 1;
413
414 IF l_dummy = 'Y' THEN
415 l_return := TRUE;
416 ELSE
417 l_return := FALSE;
418 END IF;
419
420 EXCEPTION
421 WHEN NO_DATA_FOUND THEN
422 l_return := FALSE;
423 END;
424
425 END IF;
426
427 --Cache the value
428 IF l_return THEN
429 IF l_cache THEN
430 l_table_index := put( p_val || G_SPECIAL_STRING || p_category );
431 END IF;
432 IF p_category = 'LOOKUP' THEN
433 -- Bug 5398089 : concat meanign with langauge
434 LOOKUP_MEANING_TAB(l_table_index) := x_lookup_meaning || G_SPECIAL_STRING || userenv('LANG');
435 END IF;
436 END IF;
437
438 RETURN l_return;
439
440 END search;
441
442 /**
443 * PRIVATE FUNCTION search
444 *
445 * DESCRIPTION
446 * Find value with a specified category, for instance, lookup.
447 *
448 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
449 *
450 * ARGUMENTS
451 * IN:
452 * p_val Specified value.
453 * p_category Value category. We only support
454 * category LOOKUP for now.
455 * NOTES
456 *
457 * MODIFICATION HISTORY
458 *
459 * 03-04-2002 Jianying Huang o Added new parameter to return
460 * lookup meaning.
461 *
462 */
463
464 FUNCTION search (
465 p_val IN VARCHAR2,
466 p_category IN VARCHAR2
467 ) RETURN BOOLEAN IS
468
469 l_lookup_meaning VARCHAR2(100);
470
471 BEGIN
472 RETURN search(p_val, p_category, l_lookup_meaning, 'search');
473 END search;
474
475 /**
476 * PRIVATE PROCEDURE enable_file_debug
477 *
478 * DESCRIPTION
479 * Enable file debug.
480 *
481 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
482 *
483 * ARGUMENTS
484 *
485 * NOTES
486 *
487 * MODIFICATION HISTORY
488 *
489 * 07-23-2001 Jianying Huang o Created.
490 *
491 */
492
493 PROCEDURE enable_file_debug IS
494
495 BEGIN
496
497 -- Open log file in 'append' mode.
498 IF NOT UTL_FILE.is_open( G_FILE ) THEN
499 G_FILE := UTL_FILE.fopen( G_FILE_PATH, G_FILE_NAME, 'a' );
500 END IF;
501
502 G_FILE_DEBUG := TRUE;
503
504 EXCEPTION
505 WHEN UTL_FILE.INVALID_PATH THEN
506 FND_MESSAGE.SET_NAME( 'FND', 'CONC-TEMPFILE_INVALID_PATH' );
507 FND_MESSAGE.SET_TOKEN( 'FILE_DIR', G_FILE_PATH );
508 FND_MSG_PUB.ADD;
509 G_FILE_DEBUG := FALSE;
510 G_COUNT := 0;
511 RAISE FND_API.G_EXC_ERROR;
512
513 WHEN UTL_FILE.INVALID_MODE THEN
514 FND_MESSAGE.SET_NAME( 'FND', 'CONC-TEMPFILE_INVALID_MODE' );
515 FND_MESSAGE.SET_TOKEN( 'TEMP_FILE', G_FILE_NAME );
516 FND_MESSAGE.SET_TOKEN( 'FILE_MODE', 'w' );
517 FND_MSG_PUB.ADD;
518 G_FILE_DEBUG := FALSE;
519 G_COUNT := 0;
520 RAISE FND_API.G_EXC_ERROR;
521
522 WHEN UTL_FILE.INVALID_OPERATION THEN
523 FND_MESSAGE.SET_NAME( 'FND', 'CONC-TEMPFILE_INVALID_OPERATN' );
524 FND_MESSAGE.SET_TOKEN( 'TEMP_FILE', G_FILE_NAME );
525 FND_MESSAGE.SET_TOKEN( 'TEMP_DIR', G_FILE_PATH );
526 FND_MSG_PUB.ADD;
527 G_FILE_DEBUG := FALSE;
528 G_COUNT := 0;
529 RAISE FND_API.G_EXC_ERROR;
530
531 END enable_file_debug;
532
533 --------------------------------------
534 -- public procedures and functions
535 --------------------------------------
536
537 /**
538 * PROCEDURE validate_mandatory
539 *
540 * DESCRIPTION
541 * Validate mandatory field.
542 *
543 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
544 *
545 * ARGUMENTS
546 * IN:
547 * p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
548 * p_column Column name you want to validate.
549 * p_column_value Column value
550 * p_restriced If set to 'Y', p_column_value should be passed
551 * in with some value in both create and update
552 * mode. If set to 'N', p_column_value can be
553 * NULL in update mode. Default is 'N'.
554 * IN/OUT:
555 * x_return_status Return status.
556 *
557 * NOTES
558 * The procedure is overloaded for different column type, i.e. VARCHAR2,
559 * NUMBER, and DATE.
560 *
561 * MODIFICATION HISTORY
562 *
566
563 * 07-23-2001 Jianying Huang o Created.
564 *
565 */
567 PROCEDURE validate_mandatory (
568 p_create_update_flag IN VARCHAR2,
569 p_column IN VARCHAR2,
570 p_column_value IN VARCHAR2,
571 p_restricted IN VARCHAR2,
572 x_return_status IN OUT NOCOPY VARCHAR2
573 ) IS
574
575 l_error BOOLEAN := FALSE;
576
577 BEGIN
578
579 IF p_restricted = 'N' THEN
580 IF ( p_create_update_flag = 'C' AND
581 ( p_column_value IS NULL OR
582 p_column_value = FND_API.G_MISS_CHAR ) ) OR
583 ( p_create_update_flag = 'U' AND
584 p_column_value = FND_API.G_MISS_CHAR )
585 THEN
586 l_error := TRUE;
587 END IF;
588 ELSE
589 IF ( p_column_value IS NULL OR
590 p_column_value = FND_API.G_MISS_CHAR )
591 THEN
592 l_error := TRUE;
593 END IF;
594 END IF;
595
596 IF l_error THEN
597 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
598 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
599 FND_MSG_PUB.ADD;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 END IF;
602
603 END validate_mandatory;
604
605 PROCEDURE validate_mandatory (
606 p_create_update_flag IN VARCHAR2,
607 p_column IN VARCHAR2,
608 p_column_value IN NUMBER,
609 p_restricted IN VARCHAR2,
610 x_return_status IN OUT NOCOPY VARCHAR2
611 ) IS
612
613 l_error BOOLEAN := FALSE;
614
615 BEGIN
616
617 IF p_restricted = 'N' THEN
618 IF ( p_create_update_flag = 'C' AND
619 ( p_column_value IS NULL OR
620 p_column_value = FND_API.G_MISS_NUM ) ) OR
621 ( p_create_update_flag = 'U' AND
622 p_column_value = FND_API.G_MISS_NUM )
623 THEN
624 l_error := TRUE;
625 END IF;
626 ELSE
627 IF ( p_column_value IS NULL OR
628 p_column_value = FND_API.G_MISS_NUM )
629 THEN
630 l_error := TRUE;
631 END IF;
632 END IF;
633
634 IF l_error THEN
635 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
636 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
637 FND_MSG_PUB.ADD;
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 END IF;
640
641 END validate_mandatory;
642
643 PROCEDURE validate_mandatory (
644 p_create_update_flag IN VARCHAR2,
645 p_column IN VARCHAR2,
646 p_column_value IN DATE,
647 p_restricted IN VARCHAR2,
648 x_return_status IN OUT NOCOPY VARCHAR2
649 ) IS
650
651 l_error BOOLEAN := FALSE;
652
653 BEGIN
654
655 IF p_restricted = 'N' THEN
656 IF ( p_create_update_flag = 'C' AND
657 ( p_column_value IS NULL OR
658 p_column_value = FND_API.G_MISS_DATE ) ) OR
659 ( p_create_update_flag = 'U' AND
660 p_column_value = FND_API.G_MISS_DATE )
661 THEN
662 l_error := TRUE;
663 END IF;
664 ELSE
665 IF ( p_column_value IS NULL OR
666 p_column_value = FND_API.G_MISS_DATE )
667 THEN
668 l_error := TRUE;
669 END IF;
670 END IF;
671
672 IF l_error THEN
673 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MISSING_COLUMN' );
674 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
675 FND_MSG_PUB.ADD;
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 END IF;
678
679 END validate_mandatory;
680
681 /**
682 * PROCEDURE validate_nonupdateable
683 *
684 * DESCRIPTION
685 * Validate nonupdateable field.
686 *
687 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
688 *
689 * ARGUMENTS
690 * IN:
691 * p_column Column name you want to validate.
692 * p_column_value Column value
693 * p_old_column_value Current database column value
694 * p_restriced If set to 'Y', column can not be updated
695 * even the database value is null. This is
696 * default value and as long as p_column_value
697 * is not equal to p_old_column_error, return
698 * status will be set to error.
699 * If set to 'N', if database value is null,
700 * we can update it to a value. If database value
701 * is not null and if p_column_value is not equal
705 * x_return_status Return status.
702 * to p_old_column_value, return status will be
703 * set to error.
704 * IN/OUT:
706 *
707 * NOTES
708 * The procedure is overloaded for different column type, i.e. VARCHAR2,
709 * NUMBER, and DATE. The procedure should be called in update mode.
710 *
711 * For example:
712 * IF p_create_update_flag = 'U' THEN
713 * validate_nonupdateable( ... );
714 * END IF;
715 *
716 * MODIFICATION HISTORY
717 *
718 * 07-23-2001 Jianying Huang o Created.
719 *
720 */
721
722 PROCEDURE validate_nonupdateable (
723 p_column IN VARCHAR2,
724 p_column_value IN VARCHAR2,
725 p_old_column_value IN VARCHAR2,
726 p_restricted IN VARCHAR2,
727 x_return_status IN OUT NOCOPY VARCHAR2
728 ) IS
729
730 l_old_column_value VARCHAR2(2000);
731 l_error BOOLEAN := FALSE;
732
733 BEGIN
734
735 IF p_column_value IS NOT NULL THEN
736 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_CHAR);
737
738 IF p_restricted = 'Y' THEN
739 IF p_column_value <> l_old_column_value THEN
740 l_error := TRUE;
741 END IF;
742 ELSE
743 IF l_old_column_value <> FND_API.G_MISS_CHAR AND
744 (p_column_value = FND_API.G_MISS_CHAR OR
745 p_column_value <> l_old_column_value)
746 THEN
747 l_error := TRUE;
748 END IF;
749 END IF;
750 END IF;
751
752 IF l_error THEN
753 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
754 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
755 FND_MSG_PUB.ADD;
756 x_return_status := FND_API.G_RET_STS_ERROR;
757 END IF;
758
759 END validate_nonupdateable;
760
761 PROCEDURE validate_nonupdateable (
762 p_column IN VARCHAR2,
763 p_column_value IN NUMBER,
764 p_old_column_value IN NUMBER,
765 p_restricted IN VARCHAR2,
766 x_return_status IN OUT NOCOPY VARCHAR2
767 ) IS
768
769 l_old_column_value NUMBER;
770 l_error BOOLEAN := FALSE;
771
772 BEGIN
773
774 IF p_column_value IS NOT NULL THEN
775 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_NUM);
776
777 IF p_restricted = 'Y' THEN
778 IF p_column_value <> l_old_column_value THEN
779 l_error := TRUE;
780 END IF;
781 ELSE
782 IF l_old_column_value <> FND_API.G_MISS_NUM AND
783 (p_column_value = FND_API.G_MISS_NUM OR
784 p_column_value <> l_old_column_value)
785 THEN
786 l_error := TRUE;
787 END IF;
788 END IF;
789 END IF;
790
791 IF l_error THEN
792 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
793 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
794 FND_MSG_PUB.ADD;
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 END IF;
797
798 END validate_nonupdateable;
799
800 PROCEDURE validate_nonupdateable (
801 p_column IN VARCHAR2,
802 p_column_value IN DATE,
803 p_old_column_value IN DATE,
804 p_restricted IN VARCHAR2,
805 x_return_status IN OUT NOCOPY VARCHAR2
806 ) IS
807
808 l_old_column_value DATE;
809 l_error BOOLEAN := FALSE;
810
811 BEGIN
812
813 IF p_column_value IS NOT NULL THEN
814 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_DATE);
815
816 IF p_restricted = 'Y' THEN
817 IF p_column_value <> l_old_column_value THEN
818 l_error := TRUE;
819 END IF;
820 ELSE
821 IF l_old_column_value <> FND_API.G_MISS_DATE AND
822 (p_column_value = FND_API.G_MISS_DATE OR
823 p_column_value <> l_old_column_value)
824 THEN
825 l_error := TRUE;
826 END IF;
827 END IF;
828 END IF;
829
830 IF l_error THEN
831 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_COLUMN' );
832 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
833 FND_MSG_PUB.ADD;
834 x_return_status := FND_API.G_RET_STS_ERROR;
835 END IF;
836
837 END validate_nonupdateable;
838
839 /**
840 * PROCEDURE validate_start_end_date
841 *
842 * DESCRIPTION
843 * Validate start data can not be earlier than end date.
844 *
845 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
846 *
847 * ARGUMENTS
848 * IN:
849 * p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
850 * p_start_date_column_name Column name of start date
854 * p_end_date New end date
851 * p_start_date New start date
852 * p_old_start_date Database start date in update mode
853 * p_end_date_column_name Column name of end date
855 * p_old_end_date Database end date in update mode
856 * IN/OUT:
857 * x_return_status Return status.
858 *
859 * NOTES
860 *
861 * MODIFICATION HISTORY
862 *
863 * 07-23-2001 Jianying Huang o Created.
864 *
865 */
866
867 PROCEDURE validate_start_end_date (
868 p_create_update_flag IN VARCHAR2,
869 p_start_date_column_name IN VARCHAR2,
870 p_start_date IN DATE,
871 p_old_start_date IN DATE,
872 p_end_date_column_name IN VARCHAR2,
873 p_end_date IN DATE,
874 p_old_end_date IN DATE,
875 x_return_status IN OUT NOCOPY VARCHAR2
876 ) IS
877
878 l_start_date DATE := p_old_start_date;
879 l_end_date DATE := p_old_end_date;
880
881 BEGIN
882
883 IF p_create_update_flag = 'C' THEN
884 l_start_date := p_start_date;
885 l_end_date := p_end_date;
886 ELSIF p_create_update_flag = 'U' THEN
887 IF p_start_date IS NOT NULL
888 THEN
889 IF p_start_date = FND_API.G_MISS_DATE THEN
890 l_start_date := NULL;
891 ELSE
892 l_start_date := p_start_date;
893 END IF;
894 END IF;
895
896 IF p_end_date IS NOT NULL
897 THEN
898 IF p_end_date = FND_API.G_MISS_DATE THEN
899 l_end_date := NULL;
900 ELSE
901 l_end_date := p_end_date;
902 END IF;
903 END IF;
904 END IF;
905
906 IF l_end_date IS NOT NULL AND
907 l_end_date <> FND_API.G_MISS_DATE AND
908 ( l_start_date IS NULL OR
909 l_start_date = FND_API.G_MISS_DATE OR
910 l_start_date > l_end_date )
911 THEN
912 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_DATE_GREATER' );
913 FND_MESSAGE.SET_TOKEN( 'DATE2', p_end_date_column_name );
914 FND_MESSAGE.SET_TOKEN( 'DATE1', p_start_date_column_name );
915 FND_MSG_PUB.ADD;
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 END IF;
918
919 END validate_start_end_date;
920
921 /**
922 * PROCEDURE validate_cannot_update_to_null
923 *
924 * DESCRIPTION
925 * Validate column cannot be updated to null.
926 *
927 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
928 *
929 * ARGUMENTS
930 * IN:
931 * p_column Column name you want to validate.
932 * p_column_value Column value
933 * IN/OUT:
934 * x_return_status Return status.
935 *
936 * NOTES
937 * The procedure is overloaded for different column type, i.e. VARCHAR2,
938 * NUMBER, and DATE. The procedure should be called in update mode.
939 *
940 * For example:
941 * IF p_create_update_flag = 'U' THEN
942 * validate_cannot_update_to_null( ... );
943 * END IF;
944 *
945 * MODIFICATION HISTORY
946 *
947 * 07-23-2001 Jianying Huang o Created.
948 *
949 */
950
951 PROCEDURE validate_cannot_update_to_null (
952 p_column IN VARCHAR2,
953 p_column_value IN VARCHAR2,
954 x_return_status IN OUT NOCOPY VARCHAR2
955 ) IS
956
957 BEGIN
958
959 IF p_column_value = FND_API.G_MISS_CHAR THEN
960 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
961 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
962 FND_MSG_PUB.ADD;
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 END IF;
965
966 END validate_cannot_update_to_null;
967
968 PROCEDURE validate_cannot_update_to_null (
969 p_column IN VARCHAR2,
970 p_column_value IN NUMBER,
971 x_return_status IN OUT NOCOPY VARCHAR2
972 ) IS
973
974 BEGIN
975
976 IF p_column_value = FND_API.G_MISS_NUM THEN
977 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
978 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
979 FND_MSG_PUB.ADD;
980 x_return_status := FND_API.G_RET_STS_ERROR;
981 END IF;
982
983 END validate_cannot_update_to_null;
984
985 PROCEDURE validate_cannot_update_to_null (
986 p_column IN VARCHAR2,
987 p_column_value IN DATE,
988 x_return_status IN OUT NOCOPY VARCHAR2
989 ) IS
990
991 BEGIN
992
993 IF p_column_value = FND_API.G_MISS_DATE THEN
994 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NONUPDATEABLE_TO_NULL' );
995 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
996 FND_MSG_PUB.ADD;
1000 END validate_cannot_update_to_null;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 END IF;
999
1001
1002 /**
1003 * PROCEDURE validate_cannot_update_to_null
1004 *
1005 * DESCRIPTION
1006 * Validate column cannot be updated to null.
1007 *
1008 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1009 *
1010 * ARGUMENTS
1011 * IN:
1012 * p_column Column name you want to validate.
1013 * p_lookup_table Table/view name you want to validate against to.
1014 * For now, we are supporting
1015 * AR_LOOKUPS
1016 * SO_LOOKUPS
1017 * OE_SHIP_METHODS_V
1018 * FND_LOOKUP_VALUES
1019 * Default value is AR_LOOKUPS
1020 * p_lookup_type FND lookup type
1021 * p_column_value Column value
1022 * IN/OUT:
1023 * x_return_status Return status.
1024 *
1025 * NOTES
1026 * The procedure is using cache strategy for performance improvement.
1027 *
1028 * MODIFICATION HISTORY
1029 *
1030 * 07-23-2001 Jianying Huang o Created.
1031 *
1032 */
1033
1034 PROCEDURE validate_lookup (
1035 p_column IN VARCHAR2,
1036 p_lookup_table IN VARCHAR2,
1037 p_lookup_type IN VARCHAR2,
1038 p_column_value IN VARCHAR2,
1039 x_return_status IN OUT NOCOPY VARCHAR2
1040 ) IS
1041
1042 l_error BOOLEAN := FALSE;
1043
1044 BEGIN
1045
1046 IF p_column_value IS NOT NULL AND
1047 p_column_value <> FND_API.G_MISS_CHAR THEN
1048
1049 IF p_lookup_type = 'YES/NO' THEN
1050 IF p_column_value NOT IN ('Y', 'N') THEN
1051 l_error := TRUE;
1052 END IF;
1053 ELSE
1054 IF NOT search(p_lookup_table || G_SPECIAL_STRING ||
1055 p_lookup_type || G_SPECIAL_STRING || p_column_value,
1056 'LOOKUP')
1057 THEN
1058 l_error := TRUE;
1059 END IF;
1060 END IF;
1061
1062 IF l_error THEN
1063 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_LOOKUP' );
1064 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
1065 FND_MESSAGE.SET_TOKEN( 'LOOKUP_TYPE', p_lookup_type );
1066 FND_MSG_PUB.ADD;
1067 x_return_status := FND_API.G_RET_STS_ERROR;
1068 END IF;
1069 END IF;
1070
1071 END validate_lookup;
1072
1073 /**
1074 * PROCEDURE enable_debug
1075 *
1076 * DESCRIPTION
1077 * Enable file or dbms debug based on profile options.
1078 * HZ_API_FILE_DEBUG_ON : Turn on/off file debug, i.e. debug message
1079 * will be written to a user specified file.
1080 * The file name and file path is stored in
1081 * profiles HZ_API_DEBUG_FILE_PATH and
1082 * HZ_API_DEBUG_FILE_NAME. File path must be
1083 * database writable.
1084 * HZ_API_DBMS_DEBUG_ON : Turn on/off dbms debug.
1085 *
1086 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1087 *
1088 * ARGUMENTS
1089 *
1090 * NOTES
1091 *
1092 * MODIFICATION HISTORY
1093 *
1094 * 07-23-2001 Jianying Huang o Created.
1095 *
1096 */
1097
1098 PROCEDURE enable_debug IS
1099
1100 BEGIN
1101
1102 G_COUNT := G_COUNT + 1;
1103
1104 IF G_COUNT > 1 THEN
1105 RETURN;
1106 END IF;
1107
1108 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' THEN
1109
1110 G_FILE_NAME := FND_PROFILE.value( 'HZ_API_DEBUG_FILE_NAME' );
1111 G_FILE_PATH := FND_PROFILE.value( 'HZ_API_DEBUG_FILE_PATH' );
1112 enable_file_debug;
1113 /*
1114 ELSIF FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y' THEN
1115
1116 -- Enable calls to dbms_output.
1117 DBMS_OUTPUT.enable( G_BUFFER_SIZE );
1118 G_DBMS_DEBUG := TRUE;
1119 */
1120 END IF;
1121
1122 END enable_debug;
1123
1124 /**
1125 * PROCEDURE disable_debug
1126 *
1127 * DESCRIPTION
1128 * Disable file or dbms debug.
1129 *
1130 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1131 *
1132 * ARGUMENTS
1133 *
1134 * NOTES
1135 *
1136 * MODIFICATION HISTORY
1137 *
1138 * 07-23-2001 Jianying Huang o Created.
1139 *
1140 */
1141
1142 PROCEDURE disable_debug IS
1143
1144 BEGIN
1145
1146 G_COUNT := G_COUNT - 1;
1147
1148 IF G_COUNT > 0 THEN
1149 RETURN;
1150 END IF;
1151
1152 IF G_FILE_DEBUG THEN
1153 IF UTL_FILE.is_open( G_FILE ) THEN
1154 BEGIN
1155 UTL_FILE.fclose( G_FILE );
1156 G_FILE_DEBUG := FALSE;
1157 EXCEPTION
1158 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
1162 G_FILE_DEBUG := FALSE;
1159 FND_MESSAGE.SET_NAME( 'FND', 'CONC-TEMPFILE_INVALID_HANDLE' );
1160 FND_MESSAGE.SET_TOKEN( 'TEMP_FILE', G_FILE_NAME );
1161 FND_MSG_PUB.ADD;
1163 G_COUNT := 0;
1164 RAISE FND_API.G_EXC_ERROR;
1165
1166 WHEN UTL_FILE.WRITE_ERROR THEN
1167 FND_MESSAGE.SET_NAME( 'FND', 'CONC-TEMPFILE_WRITE_ERROR' );
1168 FND_MESSAGE.SET_TOKEN( 'TEMP_FILE', G_FILE_NAME );
1169 FND_MSG_PUB.ADD;
1170 G_FILE_DEBUG := FALSE;
1171 G_COUNT := 0;
1172 RAISE FND_API.G_EXC_ERROR;
1173 END;
1174 END IF;
1175 /*
1176 ELSIF G_DBMS_DEBUG THEN
1177 G_DBMS_DEBUG := FALSE;
1178 */
1179 END IF;
1180
1181 END disable_debug;
1182
1183 /**
1184 * PROCEDURE debug
1185 *
1186 * DESCRIPTION
1187 * Put debug message.
1188 *
1189 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1190 *
1191 * ARGUMENTS
1192 * IN:
1193 * p_message Message you want to put in log.
1194 * p_prefix Prefix of the message. Default value is
1195 * DEBUG.
1196 * p_msg_level Message Level.Default value is 1 and the value should be between
1197 * 1 and 6 corresponding to FND_LOG's
1198 * LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
1199 * LEVEL_ERROR CONSTANT NUMBER := 5;
1200 * LEVEL_EXCEPTION CONSTANT NUMBER := 4;
1201 * LEVEL_EVENT CONSTANT NUMBER := 3;
1202 * LEVEL_PROCEDURE CONSTANT NUMBER := 2;
1203 * LEVEL_STATEMENT CONSTANT NUMBER := 1;
1204 * p_module_prefix Module prefix to store package name,form name.Default value is
1205 * HZ_Package.
1206 * p_module Module to store Procedure Name. Default value is HZ_Module.
1207 * NOTES
1208 *
1209 * MODIFICATION HISTORY
1210 *
1211 * 07-23-2001 Jianying Huang o Created.
1212 * 09-10-2001 Jianying Huang o Bug 1986499: Modified 'debug' procedure to take care of
1213 * p_message is passed as NULL: Initilized l_len, l_times to
1214 * 0 and added NVL around l_len.
1215 * 10-Dec-2003 Ramesh Ch Added p_msg_level,p_module_prefix,p_module parameters
1216 * with default values as part of Common Logging Infrastrycture Uptake.
1217 * Also modified the logic to call FND_LOG.STRING procedure to store the
1218 * messages in FND_LOG_MESSAGES.
1219 */
1220
1221 PROCEDURE debug (
1222 p_message IN VARCHAR2,
1223 p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
1224 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1225 p_module_prefix IN VARCHAR2 DEFAULT 'HZ_Package',
1226 p_module IN VARCHAR2 DEFAULT 'HZ_Module'
1227 ) IS
1228
1229 l_message VARCHAR2(4000);
1230 l_module VARCHAR2(255);
1231
1232 BEGIN
1233
1234 l_module :=SUBSTRB('ar.hz.plsql.'||p_module_prefix||'.'||p_module,1,255);
1235
1236 IF p_prefix IS NOT NULL THEN
1237 l_message :=SUBSTRB(p_prefix||'-'||p_message,1,4000);
1238 ELSE
1239 l_message :=SUBSTRB(p_message,1,4000);
1240 END IF;
1241
1242 if( p_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1243 FND_LOG.STRING(p_msg_level,l_module,l_message);
1244 end if;
1245
1246 END debug;
1247
1248 /**
1249 * PROCEDURE debug_return_messages
1250 *
1251 * DESCRIPTION
1252 * Put debug messages based on message count in message stack.
1253 *
1254 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1255 *
1256 * ARGUMENTS
1257 * IN:
1258 * p_msg_count Message count in message stack.
1259 * p_msg_data Message data if message count is 1.
1260 * p_msg_type Message type used as prefix of the message.
1261 * p_msg_level Message Level.Default value is 1 and the value should be between
1262 * 1 and 6 corresponding to FND_LOG's
1263 * LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
1264 * LEVEL_ERROR CONSTANT NUMBER := 5;
1265 * LEVEL_EXCEPTION CONSTANT NUMBER := 4;
1266 * LEVEL_EVENT CONSTANT NUMBER := 3;
1267 * LEVEL_PROCEDURE CONSTANT NUMBER := 2;
1268 * LEVEL_STATEMENT CONSTANT NUMBER := 1;
1269 * p_module_prefix Module prefix to store package name,form name.Default value is
1270 * HZ_Package.
1274 *
1271 * p_module Module to store Procedure Name. Default value is HZ_Module.
1272 *
1273 * NOTES
1275 * MODIFICATION HISTORY
1276 *
1277 * 07-23-2001 Jianying Huang o Created.
1278 * 10-Dec-2003 Ramesh Ch Added p_msg_level,p_module_prefix,p_module parameters
1279 * with default values as part of Common Logging Infrastrycture Uptake.
1280 * Also passed in the additional parameters when calling debug procedure.
1281 *
1282 *
1283 */
1284
1285 PROCEDURE debug_return_messages (
1286 p_msg_count IN NUMBER,
1287 p_msg_data IN VARCHAR2,
1288 p_msg_type IN VARCHAR2 DEFAULT 'ERROR',
1289 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
1290 p_module_prefix IN VARCHAR2 DEFAULT 'HZ_Package',
1291 p_module IN VARCHAR2 DEFAULT 'HZ_Module'
1292 ) IS
1293
1294 i NUMBER;
1295
1296 BEGIN
1297
1298 IF (p_msg_count <= 0) or p_msg_count is null THEN
1299 RETURN;
1300 END IF;
1301
1302 IF p_msg_count = 1 THEN
1303 debug( p_msg_data, p_msg_type,p_msg_level,p_module_prefix,p_module);
1304 ELSE
1305 FOR i IN 1..p_msg_count LOOP
1306 debug( FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE ), p_msg_type,p_msg_level,p_module_prefix,p_module);
1307 END LOOP;
1308 END IF;
1309
1310 END debug_return_messages;
1311
1312 /**
1313 * FUNCTION get_session_process_id
1314 *
1315 * DESCRIPTION
1316 * Return OS process id of current session.
1317 *
1318 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1319 *
1320 * ARGUMENTS
1321 * IN:
1322 *
1323 * NOTES
1324 *
1325 * MODIFICATION HISTORY
1326 *
1327 * 07-23-2001 Jianying Huang o Created.
1328 *
1329 */
1330
1331 FUNCTION get_session_process_id RETURN VARCHAR2 IS
1332
1333 l_spid V$PROCESS.spid%TYPE;
1334
1335 BEGIN
1336
1337 SELECT SPID INTO l_spid
1338 FROM V$PROCESS
1339 WHERE ADDR = (
1340 SELECT PADDR
1341 FROM V$SESSION
1342 WHERE AUDSID = USERENV('SESSIONID') );
1343
1344 RETURN ( l_spid );
1345
1346 END get_session_process_id;
1347
1348 /**
1349 * FUNCTION
1350 * created_by
1351 * creation_date
1352 * last_updated_by
1353 * last_update_date
1354 * last_update_login
1355 * request_id
1356 * program_id
1357 * program_application_id
1358 * program_update_date
1359 * user_id
1360 * application_id
1361 *
1362 * DESCRIPTION
1363 * Return standard who value.
1364 *
1365 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1366 *
1367 * ARGUMENTS
1368 * IN:
1369 *
1370 * NOTES
1371 *
1372 * MODIFICATION HISTORY
1373 *
1374 * 07-23-2001 Jianying Huang o Created.
1375 * 01-27-2003 Sreedhar Mohan o Added application_id.
1376 *
1377 */
1378
1379 FUNCTION created_by RETURN NUMBER IS
1380 BEGIN
1381
1382 RETURN NVL(FND_GLOBAL.user_id,-1);
1383
1384 END created_by;
1385
1386 FUNCTION creation_date RETURN DATE IS
1387 BEGIN
1388
1389 RETURN SYSDATE;
1390
1391 END creation_date;
1392
1393 FUNCTION last_updated_by RETURN NUMBER IS
1394 BEGIN
1395
1396 RETURN NVL(FND_GLOBAL.user_id,-1);
1397
1398 END last_updated_by;
1399
1400 FUNCTION last_update_date RETURN DATE IS
1401 BEGIN
1402
1403 RETURN SYSDATE;
1404
1405 END last_update_date;
1406
1407 FUNCTION last_update_login RETURN NUMBER IS
1408 BEGIN
1409
1410 IF FND_GLOBAL.conc_login_id = -1 OR
1411 FND_GLOBAL.conc_login_id IS NULL
1412 THEN
1413 RETURN FND_GLOBAL.login_id;
1414 ELSE
1415 RETURN FND_GLOBAL.conc_login_id;
1416 END IF;
1417
1418 END last_update_login;
1419
1420 FUNCTION request_id RETURN NUMBER IS
1421 BEGIN
1422
1423 IF FND_GLOBAL.conc_request_id = -1 OR
1424 FND_GLOBAL.conc_request_id IS NULL
1425 THEN
1426 RETURN NULL;
1427 ELSE
1428 RETURN FND_GLOBAL.conc_request_id;
1429 END IF;
1430
1431 END request_id;
1432
1433 FUNCTION program_id RETURN NUMBER IS
1434 BEGIN
1435
1436 IF FND_GLOBAL.conc_program_id = -1 OR
1437 FND_GLOBAL.conc_program_id IS NULL
1438 THEN
1439 RETURN NULL;
1440 ELSE
1441 RETURN FND_GLOBAL.conc_program_id;
1442 END IF;
1443
1444 END program_id;
1445
1446 FUNCTION program_application_id RETURN NUMBER IS
1447 BEGIN
1448
1449 IF FND_GLOBAL.prog_appl_id = -1 OR
1450 FND_GLOBAL.prog_appl_id IS NULL
1451 THEN
1452 RETURN NULL;
1453 ELSE
1454 RETURN FND_GLOBAL.prog_appl_id;
1458
1455 END IF;
1456
1457 END program_application_id;
1459 FUNCTION application_id RETURN NUMBER IS
1460 BEGIN
1461
1462 IF FND_GLOBAL.resp_appl_id = -1 OR
1463 FND_GLOBAL.resp_appl_id IS NULL
1464 THEN
1465 RETURN NULL;
1466 ELSE
1467 RETURN FND_GLOBAL.resp_appl_id;
1468 END IF;
1469
1470 END application_id;
1471
1472 FUNCTION program_update_date RETURN DATE IS
1473 BEGIN
1474
1475 IF program_id IS NULL THEN
1476 RETURN NULL;
1477 ELSE
1478 RETURN SYSDATE;
1479 END IF;
1480
1481 END program_update_date;
1482
1483 FUNCTION user_id RETURN NUMBER IS
1484 BEGIN
1485
1486 RETURN NVL(FND_GLOBAL.user_id,-1);
1487
1488 END user_id;
1489
1490 /**
1491 * FUNCTION incl_unrelated_entities
1492 *
1493 * DESCRIPTION
1494 * Function to check the value of incl_unrelated_entities flag
1495 * for a relationship type. the procedure has been put here to
1496 * cache the values so that program does not hit database if the
1497 * same relationship type has already been read.
1498 *
1499 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1500 *
1501 * ARGUMENTS
1502 * IN:
1503 * p_relationship_type Relationship type.
1504 *
1505 * NOTES
1506 *
1507 * MODIFICATION HISTORY
1508 *
1509 * 02-02-2002 Indrajit Sen o Created.
1510 *
1511 */
1512
1513 FUNCTION incl_unrelated_entities (
1514 p_relationship_type IN VARCHAR2
1515 ) RETURN VARCHAR2 IS
1516
1517 l_error BOOLEAN := FALSE;
1518
1519 BEGIN
1520
1521 IF p_relationship_type IS NOT NULL AND
1522 p_relationship_type <> FND_API.G_MISS_CHAR THEN
1523
1524 IF NOT search(p_relationship_type || G_SPECIAL_STRING,
1525 'RELATIONSHIP_TYPE')
1526 THEN
1527 l_error := TRUE;
1528 END IF;
1529 END IF;
1530
1531 IF l_error THEN
1532 RETURN 'N';
1533 ELSE
1534 RETURN 'Y';
1535 END IF;
1536
1537 END incl_unrelated_entities;
1538
1539 /**
1540 * FUNCTION Get_SchemaName
1541 *
1542 * DESCRIPTION
1543 * Return Schema's Name By Given The Application's Short Name.
1544 * The function will raise fnd_api.g_exc_unexpected_error if
1545 * the short name can not be found in installation and put a
1546 * message '<p_app_short_name> is not a valid oracle schema name.'
1547 * in the message stack.
1548 *
1549 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1550 *
1551 * ARGUMENTS
1552 * IN:
1553 * p_app_short_name Application short name.
1554 *
1555 * NOTES
1556 *
1557 * MODIFICATION HISTORY
1558 *
1559 * 03-01-2002 Jianying Huang o Created.
1560 *
1561 */
1562
1563 FUNCTION Get_SchemaName (
1564 p_app_short_name IN VARCHAR2
1565 ) RETURN VARCHAR2 IS
1566
1567 l_status VARCHAR2(30);
1568 l_industry VARCHAR2(30);
1569 l_schema_name VARCHAR2(30);
1570 l_return_value BOOLEAN;
1571
1572 BEGIN
1573
1574 l_return_value := fnd_installation.get_app_info(
1575 p_app_short_name, l_status, l_industry, l_schema_name);
1576
1577 IF l_schema_name IS NULL THEN
1578 fnd_message.set_name('FND','FND_NO_SCHEMA_NAME');
1579 fnd_message.set_token('SCHEMA_NAME',p_app_short_name);
1580 fnd_msg_pub.add;
1581 RAISE fnd_api.g_exc_unexpected_error;
1582 ELSE
1583 RETURN l_schema_name;
1584 END IF;
1585
1586 END Get_SchemaName;
1587
1588 /**
1589 * FUNCTION Get_AppsSchemaName
1590 *
1591 * DESCRIPTION
1592 * Return APPS Schema's Name
1593 * The function will raise fnd_api.g_exc_unexpected_error if
1594 * the 'FND' as a short name can not be found in installation.
1595 * and put a message 'FND is not a valid oracle schema name.'
1596 * in the message stack.
1597 *
1598 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1599 *
1600 * ARGUMENTS
1601 * IN:
1602 *
1603 * NOTES
1604 *
1605 * MODIFICATION HISTORY
1606 *
1607 * 03-01-2002 Jianying Huang o Created.
1608 *
1609 */
1610
1611 FUNCTION Get_AppsSchemaName RETURN VARCHAR2 IS
1612
1613 l_aol_schema VARCHAR2(30);
1614 l_apps_schema VARCHAR2(30);
1615 l_apps_mls_schema VARCHAR2(30);
1616
1617 BEGIN
1618
1619 l_aol_schema := Get_SchemaName('FND');
1620 system.ad_apps_private.get_apps_schema_name(
1621 1, l_aol_schema, l_apps_schema, l_apps_mls_schema);
1622
1623 RETURN l_apps_schema;
1624
1625 END Get_AppsSchemaName;
1626
1627 /**
1628 * FUNCTION Get_LookupMeaning
1629 *
1630 * DESCRIPTION
1631 * Get lookup meaning. Return NULL if lookup code does
1632 * not exist.
1633 *
1634 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1635 *
1636 * ARGUMENTS
1640 * AR_LOOKUPS
1637 * IN:
1638 * p_lookup_table Table/view name you want to validate against to.
1639 * For now, we are supporting
1641 * SO_LOOKUPS
1642 * OE_SHIP_METHODS_V
1643 * FND_LOOKUP_VALUES
1644 * Default value is AR_LOOKUPS
1645 * p_lookup_type FND lookup type
1646 * p_lookup_code FND lookup code
1647 *
1648 * NOTES
1649 *
1650 * MODIFICATION HISTORY
1651 *
1652 * 03-01-2002 Jianying Huang o Created.
1653 *
1654 */
1655
1656 FUNCTION Get_LookupMeaning (
1657 p_lookup_table IN VARCHAR2,
1658 p_lookup_type IN VARCHAR2,
1659 p_lookup_code IN VARCHAR2
1660 ) RETURN VARCHAR2 IS
1661
1662 l_return BOOLEAN;
1663 l_lookup_meaning VARCHAR2(100);
1664
1665 BEGIN
1666 l_return := search(p_lookup_table || G_SPECIAL_STRING ||
1667 p_lookup_type || G_SPECIAL_STRING || p_lookup_code,
1668 'LOOKUP', l_lookup_meaning, 'Get_LookupMeaning');
1669 RETURN l_lookup_meaning;
1670
1671 END Get_LookupMeaning;
1672
1673 /**
1674 * FUNCTION isColumnHasValue
1675 *
1676 * DESCRIPTION
1677 * Return 'Y' if user populates the column with some value.
1678 * Return 'N' if user does not.
1679 * The function supports both V1 and V2 column style. It is
1680 * helpful when obsolete a column and raise exception in
1681 * development site based on if the column is populated.
1682 *
1683 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1684 *
1685 * ARGUMENTS
1686 * IN:
1687 * p_api_version 'V1' is for V1 API. 'V2' is for V2 API.
1688 * p_create_update_flag 'C' is for create. 'U' is for update.
1689 * p_column_value Value of the column.
1690 * p_default_value Default value of the column. Please note,
1691 * for V1 API, most columns are defaulted to
1692 * FND_API.G_MISS_XXX and for V2 API, we do
1693 * not have default value for most columns.
1694 * p_old_column_value Database value of the column. Only used
1695 * in update mode.
1696 *
1697 * NOTES
1698 * I am not making the function as public for now because it is used only by
1699 * obsoleting content_source_type.
1700 *
1701 * MODIFICATION HISTORY
1702 *
1703 * 03-01-2002 Jianying Huang o Created.
1704 */
1705
1706 FUNCTION isColumnHasValue (
1707 p_api_version IN VARCHAR2,
1708 p_create_update_flag IN VARCHAR2,
1709 p_column_value IN VARCHAR2,
1710 p_default_value IN VARCHAR2,
1711 p_old_column_value IN VARCHAR2
1712 ) RETURN VARCHAR2 IS
1713
1714 l_column_value VARCHAR2(2000);
1715 l_old_column_value VARCHAR2(2000);
1716
1717 BEGIN
1718
1719 l_column_value := NVL(p_column_value, FND_API.G_MISS_CHAR);
1720
1721 IF p_create_update_flag = 'C' AND
1722 l_column_value <> FND_API.G_MISS_CHAR AND
1723 l_column_value <> NVL(p_default_value, FND_API.G_MISS_CHAR)
1724 THEN
1725 RETURN 'Y';
1726 ELSE
1727 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_CHAR);
1728
1729 IF l_column_value <> l_old_column_value AND
1730 l_column_value <> NVL(p_default_value, FND_API.G_MISS_CHAR)
1731 THEN
1732 RETURN 'Y';
1733 END IF;
1734 END IF;
1735
1736 RETURN 'N';
1737
1738 END isColumnHasValue;
1739
1740 FUNCTION isColumnHasValue (
1741 p_api_version IN VARCHAR2,
1742 p_create_update_flag IN VARCHAR2,
1743 p_column_value IN NUMBER,
1744 p_default_value IN NUMBER,
1745 p_old_column_value IN NUMBER
1746 ) RETURN VARCHAR2 IS
1747
1748 l_column_value NUMBER;
1749 l_old_column_value NUMBER;
1750
1751 BEGIN
1752
1753 l_column_value := NVL(p_column_value, FND_API.G_MISS_NUM);
1754
1755 IF p_create_update_flag = 'C' AND
1756 l_column_value <> FND_API.G_MISS_NUM AND
1757 l_column_value <> NVL(p_default_value, FND_API.G_MISS_NUM)
1758 THEN
1759 RETURN 'Y';
1760 ELSE
1761 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_NUM);
1762
1763 IF l_column_value <> l_old_column_value AND
1764 l_column_value <> NVL(p_default_value, FND_API.G_MISS_NUM)
1765 THEN
1766 RETURN 'Y';
1767 END IF;
1771
1768 END IF;
1769
1770 RETURN 'N';
1772 END isColumnHasValue;
1773
1774 FUNCTION isColumnHasValue (
1775 p_api_version IN VARCHAR2,
1776 p_create_update_flag IN VARCHAR2,
1777 p_column_value IN DATE,
1778 p_default_value IN DATE,
1779 p_old_column_value IN DATE
1780 ) RETURN VARCHAR2 IS
1781
1782 l_column_value DATE;
1783 l_old_column_value DATE;
1784
1785 BEGIN
1786
1787 l_column_value := NVL(p_column_value, FND_API.G_MISS_DATE);
1788
1789 IF p_create_update_flag = 'C' AND
1790 l_column_value <> FND_API.G_MISS_DATE AND
1791 l_column_value <> NVL(p_default_value, FND_API.G_MISS_DATE)
1792 THEN
1793 RETURN 'Y';
1794 ELSE
1795 l_old_column_value := NVL(p_old_column_value, FND_API.G_MISS_DATE);
1796
1797 IF l_column_value <> l_old_column_value AND
1798 l_column_value <> NVL(p_default_value, FND_API.G_MISS_DATE) AND
1799 ((p_api_version = 'V1' AND
1800 (p_column_value IS NULL)) OR
1801 (p_api_version = 'V2' AND
1802 p_column_value IS NOT NULL))
1803 THEN
1804 RETURN 'Y';
1805 END IF;
1806 END IF;
1807
1808 RETURN 'N';
1809
1810 END isColumnHasValue;
1811
1812 /**
1813 * FUNCTION Check_ObsoleteColumn
1814 *
1815 * DESCRIPTION
1816 * Internal use only!!
1817 * Set x_return_status to FND_API.G_RET_STS_ERROR when
1818 * user is trying to pass value into an obsolete column
1819 * in development site.
1820 *
1821 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1822 *
1823 * ARGUMENTS
1824 * IN:
1825 * p_api_version 'V1' is for V1 API. 'V2' is for V2 API.
1826 * p_create_update_flag 'C' is for create. 'U' is for update.
1827 * p_column Column name.
1828 * p_column_value Value of the column.
1829 * p_default_value Default value of the column. Please note,
1830 * for V1 API, most columns are defaulted to
1831 * FND_API.G_MISS_XXX and for V2 API, we do
1832 * not have default value for most columns.
1833 * p_old_column_value Database value of the column. Only used
1834 * in update mode.
1835 * OUT:
1836 * x_return_status Return FND_API.G_RET_STS_ERROR if user
1837 * is trying to pass value into an obsolete
1838 * column in development site.
1839 *
1840 * NOTES
1841 * I am not making the function as public for now because it is used only by
1842 * obsoleting content_source_type. It is worth to call this function only when
1843 * you obsolete one column. If you are obsoleting more than one columns, it
1844 * is better to cancat them and then decide if need to raise exception. For
1845 * this limitation, it is not worth to provide the function for NUMBER and
1846 * DATE type of column.
1847 *
1848 * MODIFICATION HISTORY
1849 *
1850 * 03-01-2002 Jianying Huang o Created.
1851 */
1852
1853 PROCEDURE Check_ObsoleteColumn (
1854 p_api_version IN VARCHAR2,
1855 p_create_update_flag IN VARCHAR2,
1856 p_column IN VARCHAR2,
1857 p_column_value IN VARCHAR2,
1858 p_default_value IN VARCHAR2,
1859 p_old_column_value IN VARCHAR2,
1860 x_return_status IN OUT NOCOPY VARCHAR2
1861 ) IS
1862 BEGIN
1863
1864 IF FND_PROFILE.value('HZ_API_ERR_ON_OBSOLETE_COLUMN') = 'Y' AND
1865 isColumnHasValue (
1866 p_api_version, p_create_update_flag,
1867 p_column_value, p_default_value, p_old_column_value) = 'Y'
1868 THEN
1869 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_OBSOLETE_COLUMN' );
1870 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
1871 FND_MSG_PUB.ADD;
1872 x_return_status := FND_API.G_RET_STS_ERROR;
1873 END IF;
1874
1875 END Check_ObsoleteColumn;
1876
1877 FUNCTION is_active (
1878 start_date_active IN date,
1879 end_date_active IN date)
1880 RETURN VARCHAR2 IS
1881
1882 active varchar2(10) := 'Yes';
1883 BEGIN
1884 if ((start_date_active > sysdate) OR (sysdate > end_date_active)) then
1885 active := 'No';
1886 end if;
1887 return active;
1888 END;
1889
1890 /**
1891 * FUNCTION get_site_use_purpose
1892 *
1893 * DESCRIPTION
1894 * used by common party UI .
1895 * will return the first three site use type
1896
1897 * ARGUMENTS
1898 * IN:
1899 * p_party_site_id party site id used to retrieve the site use purpose.
1900 *
1901 * RETURNS : VARCHAR2
1902 *
1903 **/
1904 function get_site_use_purpose (
1908 l_site_use_purpose VARCHAR2(100);
1905 p_party_site_id IN NUMBER)
1906 RETURN VARCHAR2 IS
1907
1909 l_top3_site_use_purposes VARCHAR2(100) := '';
1910 l_count NUMBER := 0;
1911
1912 cursor c_site_use_purposes (l_party_site_id IN NUMBER) is
1913 select al.MEANING
1914 -- psu.site_use_type,
1915 -- psu.primary_per_type
1916 from hz_party_sites ps,
1917 hz_party_site_uses psu,
1918 ar_lookups al
1919 where
1920 ps.party_site_id = l_party_site_id and
1921 psu.party_site_id = ps.party_site_id and
1922 psu.status = 'A' and
1923 al.lookup_type = 'PARTY_SITE_USE_CODE' and
1924 al.lookup_code = psu.SITE_USE_TYPE
1925 order by primary_per_type DESC;
1926
1927 BEGIN
1928
1929 OPEN c_site_use_purposes(p_party_site_id);
1930 LOOP
1931 FETCH c_site_use_purposes INTO l_site_use_purpose;
1932
1933 IF c_site_use_purposes%NOTFOUND THEN
1934 EXIT;
1935 END IF;
1936
1937
1938 IF l_count = 3 THEN
1939 l_top3_site_use_purposes := concat(l_top3_site_use_purposes, ', ...');
1940 EXIT;
1941 END IF;
1942
1943 IF l_top3_site_use_purposes is not null THEN
1944 l_top3_site_use_purposes := concat(l_top3_site_use_purposes, ', ');
1945 END IF;
1946
1947 l_top3_site_use_purposes := concat(l_top3_site_use_purposes, l_site_use_purpose);
1948
1949 l_count := l_count + 1;
1950 END LOOP;
1951 CLOSE c_site_use_purposes;
1952
1953 RETURN l_top3_site_use_purposes;
1954 END get_site_use_purpose;
1955
1956 /**
1957 * FUNCTION get_all_purposes
1958 *
1959 * DESCRIPTION
1960 * used by common party UI .
1961 * will return all site use types
1962
1963 * ARGUMENTS
1964 * IN:
1965 * p_party_site_id party site id used to retrieve the site use purpose.
1966 *
1967 * RETURNS : VARCHAR2
1968 *
1969 **/
1970 function get_all_purposes (
1971 p_party_site_id IN NUMBER)
1972 RETURN VARCHAR2 IS
1973
1974 l_site_use_purpose VARCHAR2(100);
1975 l_all_site_use_purposes VARCHAR2(1000) := '';
1976
1977 cursor c_site_use_purposes (l_party_site_id IN NUMBER) is
1978 select al.MEANING
1979 from hz_party_sites ps,
1980 hz_party_site_uses psu,
1981 ar_lookups al
1982 where
1983 ps.party_site_id = l_party_site_id and
1984 psu.party_site_id = ps.party_site_id and
1985 psu.status = 'A' and
1986 al.lookup_type = 'PARTY_SITE_USE_CODE' and
1987 al.lookup_code = psu.SITE_USE_TYPE
1988 order by primary_per_type DESC;
1989
1990 BEGIN
1991
1992 OPEN c_site_use_purposes(p_party_site_id);
1993 LOOP
1994 FETCH c_site_use_purposes INTO l_site_use_purpose;
1995
1996 IF c_site_use_purposes%NOTFOUND THEN
1997 EXIT;
1998 END IF;
1999
2000 IF l_all_site_use_purposes is not null THEN
2001 l_all_site_use_purposes := concat(l_all_site_use_purposes, ', ');
2002 END IF;
2003
2004 l_all_site_use_purposes := concat(l_all_site_use_purposes, l_site_use_purpose);
2005
2006 END LOOP;
2007 CLOSE c_site_use_purposes;
2008
2009 RETURN l_all_site_use_purposes;
2010 END get_all_purposes;
2011
2012 /**
2013 * FUNCTION get_acct_site_purposes
2014 *
2015 * DESCRIPTION
2016 * used by common party UI .
2017 * will return all acct site uses
2018
2019 * ARGUMENTS
2020 * IN:
2021 * p_acct_site_id acct site id used to retrieve the site use purpose.
2022 *
2023 * RETURNS : VARCHAR2
2024 *
2025 **/
2026 function get_acct_site_purposes (
2027 p_acct_site_id IN NUMBER)
2028 RETURN VARCHAR2 IS
2029
2030 l_site_use_purpose VARCHAR2(100);
2031 l_all_site_use_purposes VARCHAR2(1000) := '';
2032
2033 cursor c_site_use_purposes (l_acct_site_id IN NUMBER) is
2034 select al.MEANING
2035 from hz_cust_acct_sites s,
2036 hz_cust_site_uses u,
2037 ar_lookups al
2038 where
2039 s.cust_acct_site_id = l_acct_site_id and
2040 u.cust_acct_site_id = s.cust_acct_site_id and
2041 u.status = 'A' and
2042 al.lookup_type = 'SITE_USE_CODE' and
2043 al.lookup_code = u.SITE_USE_CODE
2044 order by al.MEANING;
2045
2046 BEGIN
2047
2048 OPEN c_site_use_purposes(p_acct_site_id);
2049 LOOP
2050 FETCH c_site_use_purposes INTO l_site_use_purpose;
2051
2052 IF c_site_use_purposes%NOTFOUND THEN
2053 EXIT;
2054 END IF;
2055
2056 IF l_all_site_use_purposes is not null THEN
2057 l_all_site_use_purposes := concat(l_all_site_use_purposes, ', ');
2058 END IF;
2059
2060 l_all_site_use_purposes := concat(l_all_site_use_purposes, l_site_use_purpose);
2061
2062 END LOOP;
2063 CLOSE c_site_use_purposes;
2064
2065 RETURN l_all_site_use_purposes;
2066 END get_acct_site_purposes;
2067
2068
2069 /**
2073 * used by common party UI .
2070 * FUNCTION validate_flex_address
2071 *
2072 * DESCRIPTION
2074 * will validate the flex address
2075 * and return 'Y'/'N'
2076 * ARGUMENTS
2077 * IN:
2078 * p_context_value : context_value
2079 * p_address1 : address1
2080 * p_address2 : address2
2081 * p_address3 : address3
2082 * p_address4 : address4
2083 * p_address_lines_phonetic: address_lines_phonetic
2084 * p_city : city
2085 * p_county : county
2086 * p_postal_code : postal_code
2087 * p_province : province
2088 * p_state : state
2089 * p_attribute1 : attribute1
2090 * p_attribute2 : attribute2
2091 * p_attribute3 : attribute3
2092 * p_attribute4 : attribute4
2093 * p_attribute5 : attribute5
2094 * p_attribute6 : attribute6
2095 * p_attribute7 : attribute7
2096 * p_attribute8 : attribute8
2097 * p_attribute9 : attribute9
2098 * p_attribute10: attribute10
2099 * p_attribute11: attribute11
2100 * p_attribute12: attribute12
2101 * p_attribute13: attribute13
2102 * p_attribute14: attribute14
2103 * p_attribute15: attribute15
2104 * p_attribute16: attribute16
2105 * p_attribute17: attribute17
2106 * p_attribute18: attribute18
2107 * p_attribute19: attribute19
2108 * p_attribute20: attribute20
2109 *
2110 * RETURNS : VARCHAR2
2111 *
2112 **/
2113 FUNCTION validate_flex_address (
2114 p_context_value IN VARCHAR2,
2115 p_address1 IN VARCHAR2,
2116 p_address2 IN VARCHAR2,
2117 p_address3 IN VARCHAR2,
2118 p_address4 IN VARCHAR2,
2119 p_address_lines_phonetic IN VARCHAR2,
2120 p_city IN VARCHAR2,
2121 p_county IN VARCHAR2,
2122 p_postal_code IN VARCHAR2,
2123 p_province IN VARCHAR2,
2124 p_state IN VARCHAR2,
2125 p_attribute1 IN VARCHAR2,
2126 p_attribute2 IN VARCHAR2,
2127 p_attribute3 IN VARCHAR2,
2128 p_attribute4 IN VARCHAR2,
2129 p_attribute5 IN VARCHAR2,
2130 p_attribute6 IN VARCHAR2,
2131 p_attribute7 IN VARCHAR2,
2132 p_attribute8 IN VARCHAR2,
2133 p_attribute9 IN VARCHAR2,
2134 p_attribute10 IN VARCHAR2,
2135 p_attribute11 IN VARCHAR2,
2136 p_attribute12 IN VARCHAR2,
2137 p_attribute13 IN VARCHAR2,
2138 p_attribute14 IN VARCHAR2,
2139 p_attribute15 IN VARCHAR2,
2140 p_attribute16 IN VARCHAR2,
2141 p_attribute17 IN VARCHAR2,
2142 p_attribute18 IN VARCHAR2,
2143 p_attribute19 IN VARCHAR2,
2144 p_attribute20 IN VARCHAR2
2145 ) RETURN VARCHAR2 IS
2146
2147 l_return VARCHAR2(1) := 'N';
2148 appl_short_name varchar2(30) := 'AR';
2149 desc_flex_name varchar2(30) := 'Remit Address HZ';
2150 values_or_ids varchar2(10) := 'I'; --FND BUG 4220582
2151 validation_date DATE := SYSDATE;
2152 error_msg VARCHAR2(5000);
2153 errors_received EXCEPTION;
2154
2155 BEGIN
2156
2157 --*********************************************************
2158 --* set the context value *
2159 --*********************************************************
2160 FND_FLEX_DESCVAL.set_context_value(p_context_value);
2161 --*********************************************************
2162 --* set the address attributes value *
2163 --*********************************************************
2164 fnd_flex_descval.set_column_value('ADDRESS1', p_address1 );
2165 fnd_flex_descval.set_column_value('ADDRESS2', p_address2 );
2166 fnd_flex_descval.set_column_value('ADDRESS3', p_address3 );
2167 fnd_flex_descval.set_column_value('ADDRESS4', p_address4 );
2168 fnd_flex_descval.set_column_value('ADDRESS_LINES_PHONETIC', p_address_lines_phonetic );
2169 fnd_flex_descval.set_column_value('CITY', p_city );
2170 fnd_flex_descval.set_column_value('COUNTY', p_county );
2171 fnd_flex_descval.set_column_value('STATE', p_state );
2172 fnd_flex_descval.set_column_value('PROVINCE', p_province );
2173 fnd_flex_descval.set_column_value('POSTAL_CODE', p_postal_code);
2174 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1 );
2175 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2 );
2176 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3 );
2177 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4 );
2178 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5 );
2179 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6 );
2180 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7 );
2181 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8 );
2182 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9 );
2183 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10 );
2184 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11 );
2185 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12 );
2186 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13 );
2187 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14 );
2188 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15 );
2189 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attribute16 );
2190 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attribute17 );
2191 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attribute18 );
2192 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attribute19 );
2193 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attribute20 );
2194
2195 IF FND_FLEX_DESCVAL.validate_desccols(
2196 appl_short_name,
2197 desc_flex_name,
2198 values_or_ids,
2199 validation_date)
2200 THEN
2201 l_return := 'Y';
2202 return l_return;
2203 ELSE
2204 RAISE errors_received;
2205 END IF;
2206
2207 EXCEPTION
2208 WHEN errors_received THEN
2209 error_msg := fnd_flex_descval.encoded_error_message;
2210 FND_MESSAGE.SET_ENCODED(error_msg);
2211 FND_MSG_PUB.Add;
2212 return l_return;
2213 WHEN others THEN
2214 return l_return;
2215
2216 END validate_flex_address;
2217
2218 /**
2219 * FUNCTION validate_desc_flex
2220 *
2221 * DESCRIPTION
2222 * used by common party UI .
2223 * will validate the descriptive flex
2224 * and return 'Y'/'N'
2225 * ARGUMENTS
2226 * IN:
2227 * p_appl_short_name:appl_short_name
2228 * p_desc_flex_name :desc_flex_name
2229 * p_context_value : context_value
2230 * p_attribute1 : attribute1
2231 * p_attribute2 : attribute2
2232 * p_attribute3 : attribute3
2233 * p_attribute4 : attribute4
2234 * p_attribute5 : attribute5
2235 * p_attribute6 : attribute6
2236 * p_attribute7 : attribute7
2237 * p_attribute8 : attribute8
2238 * p_attribute9 : attribute9
2239 * p_attribute10: attribute10
2240 * p_attribute11: attribute11
2241 * p_attribute12: attribute12
2242 * p_attribute13: attribute13
2243 * p_attribute14: attribute14
2244 * p_attribute15: attribute15
2245 * p_attribute16: attribute16
2246 * p_attribute17: attribute17
2247 * p_attribute18: attribute18
2248 * p_attribute19: attribute19
2249 * p_attribute20: attribute20
2250 * p_attribute21: attribute21
2251 * p_attribute22: attribute22
2252 * p_attribute23: attribute23
2253 * p_attribute24: attribute24
2254 * RETURNS : VARCHAR2
2255 *
2256 **/
2257 FUNCTION validate_desc_flex (
2258 p_appl_short_name IN VARCHAR2,
2259 p_desc_flex_name IN VARCHAR2,
2260 p_context_value IN VARCHAR2,
2261 p_attribute1 IN VARCHAR2,
2262 p_attribute2 IN VARCHAR2,
2263 p_attribute3 IN VARCHAR2,
2264 p_attribute4 IN VARCHAR2,
2265 p_attribute5 IN VARCHAR2,
2266 p_attribute6 IN VARCHAR2,
2267 p_attribute7 IN VARCHAR2,
2268 p_attribute8 IN VARCHAR2,
2269 p_attribute9 IN VARCHAR2,
2270 p_attribute10 IN VARCHAR2,
2271 p_attribute11 IN VARCHAR2,
2275 p_attribute15 IN VARCHAR2,
2272 p_attribute12 IN VARCHAR2,
2273 p_attribute13 IN VARCHAR2,
2274 p_attribute14 IN VARCHAR2,
2276 p_attribute16 IN VARCHAR2,
2277 p_attribute17 IN VARCHAR2,
2278 p_attribute18 IN VARCHAR2,
2279 p_attribute19 IN VARCHAR2,
2280 p_attribute20 IN VARCHAR2,
2281 p_attribute21 IN VARCHAR2,
2282 p_attribute22 IN VARCHAR2,
2283 p_attribute23 IN VARCHAR2,
2284 p_attribute24 IN VARCHAR2
2285 ) RETURN VARCHAR2 IS
2286
2287 l_return VARCHAR2(1) := 'N';
2288 appl_short_name varchar2(30) := p_appl_short_name;
2289 desc_flex_name varchar2(30) := p_desc_flex_name;
2290 values_or_ids varchar2(10) := 'I'; --Bug 5356950
2291 validation_date DATE := SYSDATE;
2292 error_msg VARCHAR2(5000);
2293 errors_received EXCEPTION;
2294
2295 BEGIN
2296
2297 --*********************************************************
2298 --* set the context value *
2299 --*********************************************************
2300 FND_FLEX_DESCVAL.set_context_value(p_context_value);
2301 --*********************************************************
2302 --* set the attributes value *
2303 --*********************************************************
2304 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1 );
2305 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2 );
2306 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3 );
2307 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4 );
2308 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5 );
2309 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6 );
2310 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7 );
2311 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8 );
2312 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9 );
2313 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10 );
2314 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11 );
2315 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12 );
2316 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13 );
2317 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14 );
2318 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15 );
2319 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attribute16 );
2320 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attribute17 );
2321 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attribute18 );
2322 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attribute19 );
2323 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attribute20 );
2324 IF p_desc_flex_name <> 'HZ_PARTY_SITES' THEN
2325 fnd_flex_descval.set_column_value('ATTRIBUTE21', p_attribute21 );
2326 fnd_flex_descval.set_column_value('ATTRIBUTE22', p_attribute22 );
2327 fnd_flex_descval.set_column_value('ATTRIBUTE23', p_attribute23 );
2328 fnd_flex_descval.set_column_value('ATTRIBUTE24', p_attribute24 );
2329 END IF;
2330
2331 IF FND_FLEX_DESCVAL.validate_desccols(
2332 appl_short_name,
2333 desc_flex_name,
2334 values_or_ids,
2335 validation_date)
2336 THEN
2337 l_return := 'Y';
2338 return l_return;
2339 ELSE
2340 RAISE errors_received;
2341 END IF;
2342
2343 EXCEPTION
2344 WHEN errors_received THEN
2345 error_msg := fnd_flex_descval.encoded_error_message;
2346 FND_MESSAGE.SET_ENCODED(error_msg);
2347 FND_MSG_PUB.Add;
2348 return l_return;
2349 WHEN others THEN
2350 return l_return;
2351
2352 END validate_desc_flex;
2353
2354 /**
2355 * FUNCTION get_org_contact_role
2356 *
2357 * DESCRIPTION
2358 * used by common party UI .
2359 * added by albert (tsli)
2360 * will return the first three org contact roles
2361
2362 * ARGUMENTS
2363 * IN:
2364 * p_org_contact_id org contact id used to retrieve the org contact roles.
2365 *
2366 * RETURNS : VARCHAR2
2367 *
2368 **/
2369
2370 FUNCTION get_org_contact_role (
2371 p_org_contact_id IN NUMBER
2372 ) RETURN VARCHAR2 IS
2373
2374 TYPE varchar2_table IS TABLE OF VARCHAR2(80); --5960623
2375 org_contact_roles_tab varchar2_table;
2376
2377 l_top3_org_contact_roles VARCHAR2(100);
2378 l_count NUMBER;
2379
2380 CURSOR c_org_contact_roles IS
2381 SELECT lu.meaning
2382 FROM hz_org_contact_roles ocr,
2383 fnd_lookup_values lu
2384 WHERE ocr.org_contact_id = p_org_contact_id
2385 AND ocr.status = 'A'
2386 AND lu.view_application_id = 222
2387 AND lu.language = userenv('LANG')
2388 AND lu.lookup_type = 'CONTACT_ROLE_TYPE'
2389 AND lu.lookup_code = ocr.role_type;
2390
2391 BEGIN
2392
2393 OPEN c_org_contact_roles;
2397
2394 FETCH c_org_contact_roles BULK COLLECT INTO org_contact_roles_tab;
2395
2396 l_top3_org_contact_roles := '';
2398 IF org_contact_roles_tab.count > 0 THEN
2399 l_count := 1;
2400
2401 WHILE (l_count <= 3 AND l_count <= org_contact_roles_tab.count)
2402 LOOP
2403 IF l_count > 1 THEN
2404 l_top3_org_contact_roles := l_top3_org_contact_roles || ', ';
2405 END IF;
2406
2407 l_top3_org_contact_roles := l_top3_org_contact_roles ||
2408 org_contact_roles_tab(l_count);
2409
2410
2411 l_count := l_count + 1;
2412 END LOOP;
2413
2414 IF org_contact_roles_tab.count > 3 THEN
2415 l_top3_org_contact_roles := l_top3_org_contact_roles || ', ...';
2416 END IF;
2417 END IF;
2418 CLOSE c_org_contact_roles;
2419
2420 RETURN l_top3_org_contact_roles;
2421
2422 END get_org_contact_role;
2423
2424 /**
2425 * FUNCTION get_primary_phone
2426 *
2427 * DESCRIPTION
2428 * used by common party UI .
2429 * added by albert (tsli)
2430 * will return the primary phone
2431 * ARGUMENTS
2432 * IN:
2433 * p_party_id party id used to retrieve the primary phone
2434 *
2435 * RETURNS : VARCHAR2
2436 *
2437 **/
2438
2439 function get_primary_phone (
2440 p_party_id IN NUMBER,
2441 p_display_purpose IN VARCHAR2 := fnd_api.g_true)
2442 RETURN VARCHAR2 IS
2443 l_contact_point_id NUMBER;
2444 BEGIN
2445
2446 BEGIN
2447 select
2448 contact_point_id
2449 INTO l_contact_point_id
2450 from hz_contact_points CPPH
2451 where contact_point_type = 'PHONE'
2452 and primary_flag = 'Y'
2453 and status = 'A'
2454 and OWNER_TABLE_NAME = 'HZ_PARTIES'
2455 and OWNER_TABLE_ID = p_party_id
2456 and rownum = 1;
2457
2458 EXCEPTION
2459 WHEN NO_DATA_FOUND THEN
2460 RETURN NULL;
2461 END;
2462
2463 RETURN hz_format_phone_v2pub.get_formatted_phone(l_contact_point_id,p_display_purpose);
2464 END get_primary_phone;
2465
2466 /**
2467 * FUNCTION get_primary_email
2468 *
2469 * DESCRIPTION
2470 * used by common party UI .
2471 * added by albert (tsli)
2472 * will return the primary email
2473 * ARGUMENTS
2474 * IN:
2475 * p_party_id party id used to retrieve the primary email
2476 *
2477 * RETURNS : VARCHAR2
2478 *
2479 **/
2480 function get_primary_email (
2481 p_party_id IN NUMBER)
2482 RETURN VARCHAR2 IS
2483 l_primary_email VARCHAR2(2000);
2484 BEGIN
2485
2486 select email_address
2487 INTO l_primary_email
2488 from hz_contact_points
2489 where contact_point_type = 'EMAIL'
2490 and primary_flag = 'Y'
2491 and status = 'A'
2492 and OWNER_TABLE_NAME = 'HZ_PARTIES'
2493 and OWNER_TABLE_ID = p_party_id
2494 and rownum = 1;
2495
2496 RETURN l_primary_email;
2497 END get_primary_email;
2498
2499 PROCEDURE find_index_name(
2500 p_index_name OUT NOCOPY VARCHAR2) IS
2501 tmp_errm VARCHAR2(500);
2502 n NUMBER;
2503 m NUMBER;
2504 i NUMBER;
2505 BEGIN
2506 n := INSTR(sqlerrm, '(', 1,1) ;
2507 m := INSTR(sqlerrm, ')', 1,1) ;
2508 i := m -n;
2509 tmp_errm := SUBSTRB(sqlerrm, n, i);
2510 n := INSTR(tmp_errm, '.', 1,1) ;
2511 p_index_name := SUBSTRB(tmp_errm, n+1, i-1);
2512 END find_index_name;
2513
2514
2515 /**
2516 * FUNCTION GET_YAHOO_MAP_URL
2517 *
2518 * DESCRIPTION
2519 * function that would return a html link tag which
2520 * will contain the address formatted for Yahoo Maps.
2521 * ARGUMENTS
2522 * IN:
2523 * address1 IN VARCHAR2,
2524 * address2 IN VARCHAR2,
2525 * address3 IN VARCHAR2,
2526 * address4 IN VARCHAR2,
2527 * city IN VARCHAR2,
2528 * country IN VARCHAR2,
2529 * state IN VARCHAR2,
2530 * postal_code IN VARCHAR2
2531 *
2532 * RETURNS : VARCHAR2
2533 *
2534 **/
2535
2536 FUNCTION GET_YAHOO_MAP_URL(address1 IN VARCHAR2,
2537 address2 IN VARCHAR2,
2538 address3 IN VARCHAR2,
2539 address4 IN VARCHAR2,
2540 city IN VARCHAR2,
2541 country IN VARCHAR2,
2542 state IN VARCHAR2,
2543 postal_code IN VARCHAR2)
2544 RETURN VARCHAR2 AS
2545 url VARCHAR2(200);
2546 url2 VARCHAR2(700);
2547 country_code VARCHAR2(20);
2548 amp VARCHAR2(01) := '&';
2549 staticURL VARCHAR2(100) := 'http://maps.yahoo.com/py/maps.py?BFCat=' || amp || 'Pyt=Tmap' || amp || 'newFL=Use+Address+Below' || amp || 'Get Map=Get+Map';
2550
2551 BEGIN
2552
2556 THEN
2553 -- Since TCA validates the country code of UK to GB and Yahoo Maps expects 'uk'
2554 -- we resort to this work around
2555 IF upper(rtrim(country)) = 'GB'
2557 country_code := 'UK';
2558 ELSE
2559 country_code := rtrim(country);
2560 END IF;
2561
2562 URL2 := staticURL ||
2563 amp || 'addr=' || REPLACE(address1, ' ', '+') ||
2564 amp || 'csz=' || REPLACE(city, ' ', '+') || '+' || RTRIM(state) || '+' || RTRIM(postal_code) ||
2565 amp || 'country=' || country_code;
2566
2567 URL := substrb(URL2, 1, 200);
2568 RETURN url;
2569 END GET_YAHOO_MAP_URL;
2570
2571 /**
2572 * FUNCTION IS_PARTY_ID_IN_REQUEST_LOG
2573 *
2574 * DESCRIPTION
2575 * function that would return a 'Y' if this party_id exist in hz_dnb_request_log
2576 * return 'N' if not.
2577 * ARGUMENTS
2578 * party_id IN NUMBER
2579 *
2580 * RETURNS : VARCHAR2
2581 */
2582
2583 FUNCTION IS_PARTY_ID_IN_REQUEST_LOG(
2584 p_party_id IN NUMBER)
2585
2586 RETURN VARCHAR2 AS
2587
2588 l_exist varchar2(1) := 'N';
2589
2590 /* Bug 3301467 : Comment the cursor code
2591
2592 CURSOR c IS
2593 select 'N'
2594 from hz_dnb_request_log log, hz_organization_profiles org
2595 where log.party_id= p_party_id
2596 and org.party_id=log.party_id
2597 and org.actual_content_source='DNB'
2598 and org.effective_end_date is NULL
2599 and trunc(org.last_update_date) > trunc(log.last_update_date)
2600 and rownum =1
2601 UNION
2602 select 'Y'
2603 from hz_dnb_request_log log, hz_organization_profiles org
2604 where log.party_id= p_party_id
2605 and org.party_id=log.party_id
2606 and org.actual_content_source='DNB'
2607 and org.effective_end_date is NULL
2608 and trunc(org.last_update_date) = trunc(log.last_update_date)
2609 and rownum =1;
2610 */
2611
2612 BEGIN
2613
2614 select 'E' into l_exist
2615 from hz_dnb_request_log
2616 where party_id = p_party_id
2617 and rownum = 1;
2618
2619 if l_exist = 'E' then
2620 /*
2621 open c;
2622 fetch c into l_exist;
2623 close c;
2624 */
2625
2626 -- Bug 3301467 : Add below statement to check if the last purchased package for
2627 -- a party is through online mode or batch mode.
2628 -- If it is online mode, return 'Y' else 'N'
2629 Begin
2630 select 'Y' into l_exist from hz_organization_profiles org
2631 where org.party_id = p_party_id
2632 and org.actual_content_source='DNB'
2633 and org.effective_end_date is NULL
2634 and trunc(org.last_update_date) =
2635 (select trunc(max(log.last_update_date)) from hz_dnb_request_log log
2636 where log.party_id = p_party_id);
2637 Exception
2638 when no_data_found then
2639 l_exist := 'N';
2640 end;
2641 end if;
2642
2643 return l_exist;
2644
2645
2646 EXCEPTION
2647 WHEN NO_DATA_FOUND THEN
2648 return l_exist;
2649 END IS_PARTY_ID_IN_REQUEST_LOG;
2650
2651
2652 /**
2653 * FUNCTION get_message
2654 *
2655 * DESCRIPTION
2656 * returns the translated message
2657 * ARGUMENTS
2658 * message_name
2659 * token1_name, token1_value
2660 * token2_name, token2_value
2661 * token3_name, token3_value
2662 * token4_name, token4_value
2663 * token5_name, token5_value
2664 *
2665 * RETURNS : VARCHAR2: token sustituted, translated message
2666 */
2667 FUNCTION get_message(
2668 app_short_name IN VARCHAR2,
2669 message_name IN varchar2,
2670 token1_name IN VARCHAR2,
2671 token1_value IN VARCHAR2,
2672 token2_name IN VARCHAR2,
2673 token2_value IN VARCHAR2,
2674 token3_name IN VARCHAR2,
2675 token3_value IN VARCHAR2,
2676 token4_name IN VARCHAR2,
2677 token4_value IN VARCHAR2,
2678 token5_name IN VARCHAR2,
2679 token5_value IN VARCHAR2)
2680 RETURN VARCHAR2 AS
2681 BEGIN
2682 -- Flow
2683 -- 1. Set the Message Name
2684 -- 2. Set the token(s).
2685 -- 3. get translated message and return it.
2686
2687 FND_MESSAGE.SET_NAME(app_short_name,message_name);
2688 -- not using the CASE statement to make it backward compatible.
2689 IF token1_name IS NOT NULL THEN
2690 FND_MESSAGE.SET_TOKEN(token1_name,token1_value);
2691 END IF;
2692 IF token2_name IS NOT NULL THEN
2693 FND_MESSAGE.SET_TOKEN(token2_name,token2_value);
2694 END IF;
2695 IF token3_name IS NOT NULL THEN
2696 FND_MESSAGE.SET_TOKEN(token3_name,token3_value);
2697 END IF;
2698 IF token4_name IS NOT NULL THEN
2699 FND_MESSAGE.SET_TOKEN(token4_name,token4_value);
2700 END IF;
2701 IF token5_name IS NOT NULL THEN
2702 FND_MESSAGE.SET_TOKEN(token5_name,token5_value);
2703 END IF;
2704
2705 return FND_MESSAGE.get;
2706
2710 WHEN OTHERS THEN
2707 EXCEPTION
2708 WHEN NO_DATA_FOUND THEN
2709 return 'NO MESSAGE FOUND';
2711 return 'NO MESSAGE FOUND';
2712 END get_message;
2713
2714
2715 /**
2716 * FUNCTION is_restriction_exist
2717 *
2718 * DESCRIPTION
2719 * used by common party UI .
2720 * will return a flag to indicate if contact preference exist
2721 * ARGUMENTS
2722 * IN:
2723 * p_contact_level_table contact level table
2724 * p_contact_level_table_id contact level table id
2725 * p_preference_code preference code
2726 *
2727 * RETURNS : VARCHAR2
2728 *
2729 **/
2730
2731 FUNCTION is_restriction_exist (
2732 p_contact_level_table IN VARCHAR2,
2733 p_contact_level_table_id IN NUMBER,
2734 p_preference_code IN VARCHAR2
2735 ) RETURN VARCHAR2 IS
2736
2737 CURSOR c_restriction IS
2738 SELECT null
2739 FROM hz_contact_preferences
2740 WHERE contact_level_table = p_contact_level_table
2741 AND contact_level_table_id = p_contact_level_table_id
2742 AND preference_code = p_preference_code
2743 AND status = 'A'
2744 AND (preference_end_date IS NULL OR
2745 TRUNC(preference_end_date) >= TRUNC(sysdate))
2746 AND TRUNC(preference_start_date) <= TRUNC(sysdate)
2747 AND ROWNUM = 1;
2748
2749 l_dummy VARCHAR2(1);
2750 l_return VARCHAR2(1);
2751
2752 BEGIN
2753
2754 IF p_contact_level_table_id IS NULL THEN
2755 RETURN 'N';
2756 END IF;
2757
2758 OPEN c_restriction;
2759 FETCH c_restriction INTO l_dummy;
2760 IF c_restriction%NOTFOUND THEN
2761 l_return := 'N';
2762 ELSE
2763 l_return := 'Y';
2764 END IF;
2765 CLOSE c_restriction;
2766
2767 RETURN l_return;
2768
2769 END is_restriction_exist;
2770
2771 /**
2772 * FUNCTION is_purchased_content_source
2773 *
2774 * DESCRIPTION
2775 * This function will return 'Y' if the source system is a purchased one.
2776 * (i.e HZ_ORIG_SYSTEMS_B.orig_system_type = 'PURCHASED')
2777 *
2778 * ARGUMENTS
2779 * IN:
2780 * p_content_source
2781 *
2782 * RETURNS : VARCHAR2
2783 *
2784 * NOTES
2785 *
2786 * MODIFICATION HISTORY
2787 *
2788 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2789 *
2790 **/
2791 FUNCTION is_purchased_content_source (
2792 p_content_source IN VARCHAR2
2793 ) RETURN VARCHAR2 IS
2794
2795 CURSOR c_content_source IS
2796 SELECT 'Y'
2797 FROM HZ_ORIG_SYSTEMS_B
2798 WHERE orig_system = p_content_source
2799 AND orig_system_type = 'PURCHASED'
2800 -- AND status = 'A'
2801 AND rownum = 1;
2802
2803 l_return_value VARCHAR2(1);
2804
2805 BEGIN
2806
2807 OPEN c_content_source;
2808 FETCH c_content_source INTO l_return_value;
2809 IF c_content_source%NOTFOUND THEN
2810 l_return_value := 'N';
2811 END IF;
2812 CLOSE c_content_source;
2813
2814 RETURN l_return_value;
2815
2816 END is_purchased_content_source;
2817
2818 /**
2819 * FUNCTION get_lookupMeaning_lang
2820 *
2821 * DESCRIPTION
2822 * This function will return the meaning in FND_LOOKUP_VALUES for the given combination
2823 * of lookup_type, lookup_code and language.
2824 *
2825 * ARGUMENTS
2826 * IN:
2827 * p_lookup_type
2828 * p_lookup_code
2829 * p_language
2830 *
2831 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Meaning)
2832 *
2833 * NOTES
2834 *
2835 * MODIFICATION HISTORY
2836 *
2837 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2838 *
2839 **/
2840 FUNCTION get_lookupMeaning_lang (
2841 p_lookup_type IN VARCHAR2,
2842 p_lookup_code IN VARCHAR2,
2843 p_language IN VARCHAR2
2844 ) RETURN VARCHAR2 IS
2845
2846 CURSOR c_lookup_meaning_for_lang IS
2847 SELECT meaning
2848 FROM FND_LOOKUP_VALUES
2849 WHERE lookup_type = p_lookup_type
2850 AND lookup_code = p_lookup_code
2851 AND language = p_language
2852 AND enabled_flag = 'Y'
2853 AND (end_date_active IS NULL
2854 OR end_date_active >= sysdate);
2855
2856 l_return_value VARCHAR2(80);
2857
2858 BEGIN
2859
2860 OPEN c_lookup_meaning_for_lang;
2861 FETCH c_lookup_meaning_for_lang INTO l_return_value;
2862 IF c_lookup_meaning_for_lang%NOTFOUND THEN
2863 l_return_value := NULL ;
2864 END IF;
2865 CLOSE c_lookup_meaning_for_lang;
2866
2867 RETURN l_return_value;
2868
2869 END get_lookupMeaning_lang;
2870
2871 /**
2872 * FUNCTION get_lookupDesc_lang
2873 *
2874 * DESCRIPTION
2875 * This function will return the description in FND_LOOKUP_VALUES for the given combination
2876 * of lookup_type, lookup_code and language.
2877 *
2878 * ARGUMENTS
2882 * p_language
2879 * IN:
2880 * p_lookup_type
2881 * p_lookup_code
2883 *
2884 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Description)
2885 *
2886 * NOTES
2887 *
2888 * MODIFICATION HISTORY
2889 *
2890 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2891 *
2892 **/
2893 FUNCTION get_lookupDesc_lang (
2894 p_lookup_type IN VARCHAR2,
2895 p_lookup_code IN VARCHAR2,
2896 p_language IN VARCHAR2
2897 ) RETURN VARCHAR2 IS
2898
2899 CURSOR c_lookup_desc_for_lang IS
2900 SELECT description
2901 FROM FND_LOOKUP_VALUES
2902 WHERE lookup_type = p_lookup_type
2903 AND lookup_code = p_lookup_code
2904 AND language = p_language
2905 AND enabled_flag = 'Y'
2906 AND (end_date_active IS NULL
2907 OR end_date_active >= sysdate);
2908
2909 l_return_value VARCHAR2(240);
2910
2911 BEGIN
2912
2913 OPEN c_lookup_desc_for_lang;
2914 FETCH c_lookup_desc_for_lang INTO l_return_value;
2915 IF c_lookup_desc_for_lang%NOTFOUND THEN
2916 l_return_value := NULL ;
2917 END IF;
2918 CLOSE c_lookup_desc_for_lang;
2919
2920 RETURN l_return_value;
2921
2922 END get_lookupDesc_lang;
2923
2924
2925
2926 /**
2927 * FUNCTION check_prim_bill_to_site
2928 *
2929 * DESCRIPTION
2930 * used by common party UI .
2931 * will return Y if the party site is the primary Bill_To site.
2932 * will return N in other cases
2933
2934 * ARGUMENTS
2935 * IN:
2936 * p_party_site_id party site id used to retrieve the site use purpose.
2937 *
2938 * RETURNS : VARCHAR2
2939 *
2940 **/
2941 function check_prim_bill_to_site (
2942 p_party_site_id IN NUMBER)
2943 RETURN VARCHAR2 IS
2944
2945
2946 l_bill_to_primary VARCHAR2(1000);
2947 l_primary VARCHAR2(10);
2948 cursor c_site_bill_to (l_party_site_id IN NUMBER) is
2949 select psu.primary_per_type
2950 from hz_party_site_uses psu
2951 where
2952 psu.party_site_id = l_party_site_id and
2953 psu.status = 'A' and
2954 psu.site_use_type = 'BILL_TO' and
2955 psu.primary_per_type = 'Y'
2956 order by primary_per_type DESC;
2957
2958 BEGIN
2959
2960 l_bill_to_primary := 'N';
2961 OPEN c_site_bill_to(p_party_site_id);
2962 LOOP
2963 FETCH c_site_bill_to INTO l_primary;
2964
2965 IF c_site_bill_to%NOTFOUND THEN
2966 EXIT;
2967 END IF;
2968
2969 IF l_primary = 'Y' THEN
2970 l_bill_to_primary := 'Y';
2971 exit;
2972 END IF;
2973
2974 END LOOP;
2975 CLOSE c_site_bill_to;
2976
2977 RETURN l_bill_to_primary;
2978 END check_prim_bill_to_site;
2979
2980
2981 /**
2982 * FUNCTION check_prim_ship_to_site
2983 *
2984 * DESCRIPTION
2985 * used by common party UI .
2986 * will return Y if the party site is the primary Ship_To site.
2987 * will return N in other cases
2988
2989 * ARGUMENTS
2990 * IN:
2991 * p_party_site_id party site id used to retrieve the site use purpose.
2992 *
2993 * RETURNS : VARCHAR2
2994 *
2995 **/
2996 function check_prim_ship_to_site (
2997 p_party_site_id IN NUMBER)
2998 RETURN VARCHAR2 IS
2999
3000
3001 l_ship_to_primary VARCHAR2(1000);
3002 l_primary VARCHAR2(10);
3003 cursor c_site_ship_to (l_party_site_id IN NUMBER) is
3004 select psu.primary_per_type
3005 from hz_party_site_uses psu
3006 where
3007 psu.party_site_id = l_party_site_id and
3008 psu.status = 'A' and
3009 psu.site_use_type = 'SHIP_TO' and
3010 psu.primary_per_type = 'Y'
3011 order by primary_per_type DESC;
3012
3013 BEGIN
3014
3015 l_ship_to_primary := 'N';
3016 OPEN c_site_ship_to(p_party_site_id);
3017 LOOP
3018 FETCH c_site_ship_to INTO l_primary;
3019
3020 IF c_site_ship_to%NOTFOUND THEN
3021 EXIT;
3022 END IF;
3023
3024 IF l_primary = 'Y' THEN
3025 l_ship_to_primary := 'Y';
3026 exit;
3027 END IF;
3028
3029 END LOOP;
3030 CLOSE c_site_ship_to;
3031
3032 RETURN l_ship_to_primary;
3033 END check_prim_ship_to_site;
3034
3035
3036 /**
3037 * PROCEDURE validate_created_by_module
3038 *
3039 * DESCRIPTION
3040 * validate created by module
3041 * ARGUMENTS
3042 * IN:
3043 * p_create_update_flag create update flag
3044 * p_created_by_module created by module
3045 * p_old_created_by_module old value of created by module
3046 * x_return_status return status
3047 */
3048
3049 PROCEDURE validate_created_by_module (
3050 p_create_update_flag IN VARCHAR2,
3051 p_created_by_module IN VARCHAR2,
3055
3052 p_old_created_by_module IN VARCHAR2,
3053 x_return_status IN OUT NOCOPY VARCHAR2
3054 ) IS
3056 BEGIN
3057
3058 -- skip mandatory and non-updateable check from logical API
3059 IF G_CALLING_API IS NULL THEN
3060 -- created_by_module is mandatory field
3061 -- Since created_by_module is non-updateable, we only need to check mandatory
3062 -- during creation.
3063
3064 IF p_create_update_flag = 'C' THEN
3065 validate_mandatory (
3066 p_create_update_flag => p_create_update_flag,
3067 p_column => 'created_by_module',
3068 p_column_value => p_created_by_module,
3069 x_return_status => x_return_status);
3070
3071 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3072 debug(
3073 p_prefix => '',
3074 p_message => 'created_by_module is mandatory. ' ||
3075 'x_return_status = ' || x_return_status,
3076 p_msg_level => fnd_log.level_statement);
3077 END IF;
3078 END IF;
3079
3080 -- created_by_module is non-updateable field. But it can be updated from
3081 -- NULL to some value.
3082
3083 IF p_create_update_flag = 'U' AND
3084 p_created_by_module IS NOT NULL
3085 THEN
3086 validate_nonupdateable (
3087 p_column => 'created_by_module',
3088 p_column_value => p_created_by_module,
3089 p_old_column_value => p_old_created_by_module,
3090 p_restricted => 'N',
3091 x_return_status => x_return_status);
3092
3093 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3094 debug(
3095 p_prefix => '',
3096 p_message => 'created_by_module is non-updateable. It can be updated from NULL to a value. ' ||
3097 'x_return_status = ' || x_return_status,
3098 p_msg_level => fnd_log.level_statement);
3099 END IF;
3100 END IF;
3101 END IF;
3102
3103 -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
3104 IF p_created_by_module IS NOT NULL AND
3105 p_created_by_module <> fnd_api.g_miss_char AND
3106 (p_create_update_flag = 'C' OR
3107 (p_create_update_flag = 'U' AND
3108 (p_old_created_by_module IS NULL OR
3109 p_created_by_module <> p_old_created_by_module)))
3110 THEN
3111 validate_lookup (
3112 p_column => 'created_by_module',
3113 p_lookup_type => 'HZ_CREATED_BY_MODULES',
3114 p_column_value => p_created_by_module,
3115 x_return_status => x_return_status);
3116
3117 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3118 debug(
3119 p_prefix => '',
3120 p_message => 'created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES. ' ||
3121 'x_return_status = ' || x_return_status,
3122 p_msg_level => fnd_log.level_statement);
3123 END IF;
3124 END IF;
3125
3126 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3127 debug(
3128 p_prefix => '',
3129 p_message => 'after validate created_by_module ... ' ||
3130 'x_return_status = ' || x_return_status,
3131 p_msg_level => fnd_log.level_statement);
3132 END IF;
3133
3134 END validate_created_by_module;
3135
3136
3137 /**
3138 * PROCEDURE validate_application_id
3139 *
3140 * DESCRIPTION
3141 * validate application id
3142 * ARGUMENTS
3143 * IN:
3144 * p_create_update_flag create update flag
3145 * p_application_id application id
3146 * p_old_application_id old value of application id
3147 * x_return_status return status
3148 */
3149
3150 PROCEDURE validate_application_id (
3151 p_create_update_flag IN VARCHAR2,
3152 p_application_id IN NUMBER,
3153 p_old_application_id IN NUMBER,
3154 x_return_status IN OUT NOCOPY VARCHAR2
3155 ) IS
3156
3157 l_column CONSTANT VARCHAR2(30) := 'application_id';
3158
3159 BEGIN
3160
3161 -- skip non-updateable check from logical API
3162 IF G_CALLING_API IS NULL THEN
3163 -- application_id is non-updateable field. But it can be updated from NULL
3164 -- to some value.
3165
3166 IF p_create_update_flag = 'U' AND
3167 p_application_id IS NOT NULL
3168 THEN
3169 validate_nonupdateable (
3170 p_column => l_column,
3171 p_column_value => p_application_id,
3172 p_old_column_value => p_old_application_id,
3173 p_restricted => 'N',
3174 x_return_status => x_return_status);
3175
3176 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3177 debug(
3178 p_prefix => '',
3179 p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
3180 'x_return_status = ' || x_return_status,
3181 p_msg_level => fnd_log.level_statement);
3182 END IF;
3183 END IF;
3184 END IF;
3185
3186 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3187 debug(
3188 p_prefix => '',
3189 p_message => 'after validate ' || l_column || ' ... ' ||
3190 'x_return_status = ' || x_return_status,
3191 p_msg_level => fnd_log.level_statement);
3192 END IF;
3193
3194 END validate_application_id;
3195
3196
3197 /**
3198 * FUNCTION is_role_in_relationship_group
3199 *
3200 * DESCRIPTION
3201 * return if a role exists in a relationship group
3202 * ARGUMENTS
3203 * IN:
3204 * p_relationship_type_id relationship type id
3205 * p_relationship_group_code relationship group code
3206 */
3207
3208 FUNCTION is_role_in_relationship_group (
3209 p_relationship_type_id IN NUMBER,
3210 p_relationship_group_code IN VARCHAR2
3211 ) RETURN VARCHAR2 IS
3212
3213 CURSOR c_groups IS
3214 SELECT null
3215 FROM hz_code_assignments c
3216 WHERE c.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
3217 AND c.class_category = 'RELATIONSHIP_TYPE_GROUP'
3218 AND c.class_code = p_relationship_group_code
3219 AND sysdate between c.start_date_active and nvl(c.end_date_active, sysdate+1)
3220 AND c.status = 'A'
3221 AND c.owner_table_id = p_relationship_type_id
3222 AND ROWNUM = 1;
3223
3224 l_dummy VARCHAR2(1);
3225 l_return VARCHAR2(1);
3226
3227 BEGIN
3228
3229 OPEN c_groups;
3230 FETCH c_groups INTO l_dummy;
3231 IF c_groups%NOTFOUND THEN
3232 l_return := 'N';
3233 ELSE
3234 l_return := 'Y';
3235 END IF;
3236 CLOSE c_groups;
3237
3238 RETURN l_return;
3239
3240 END is_role_in_relationship_group;
3241
3242
3243 END HZ_UTILITY_V2PUB;
[Home] [Help]