1 PACKAGE BODY HZ_UTILITY_V2PUB AS
2 /*$Header: ARH2UTSB.pls 120.47.12020000.2 2012/12/03 12:16:11 rgokavar 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
281
278 -- Bug 5398089 : Added variables
279 l_string VARCHAR2(255);
280 l_cache BOOLEAN;
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 *
563 * 07-23-2001 Jianying Huang o Created.
564 *
565 */
566
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
702 * to p_old_column_value, return status will be
703 * set to error.
704 * IN/OUT:
705 * x_return_status Return status.
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
816 IF p_restricted = 'Y' THEN
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
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
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
854 * p_end_date New 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;
997 x_return_status := FND_API.G_RET_STS_ERROR;
998 END IF;
999
1000 END validate_cannot_update_to_null;
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
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;
1162 G_FILE_DEBUG := FALSE;
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;
1272 *
1269 * p_module_prefix Module prefix to store package name,form name.Default value is
1270 * HZ_Package.
1271 * p_module Module to store Procedure Name. Default value is HZ_Module.
1273 * NOTES
1274 *
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;
1455 END IF;
1456
1457 END program_application_id;
1458
1459 FUNCTION application_id RETURN NUMBER IS
1460 BEGIN
1461
1465 RETURN NULL;
1462 IF FND_GLOBAL.resp_appl_id = -1 OR
1463 FND_GLOBAL.resp_appl_id IS NULL
1464 THEN
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
1637 * IN:
1638 * p_lookup_table Table/view name you want to validate against to.
1639 * For now, we are supporting
1640 * AR_LOOKUPS
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
1652 * 03-01-2002 Jianying Huang o Created.
1649 *
1650 * MODIFICATION HISTORY
1651 *
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;
1768 END IF;
1769
1770 RETURN 'N';
1771
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
1804 RETURN 'Y';
1801 (p_api_version = 'V2' AND
1802 p_column_value IS NOT NULL))
1803 THEN
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 (
1905 p_party_site_id IN NUMBER)
1906 RETURN VARCHAR2 IS
1907
1908 l_site_use_purpose VARCHAR2(100);
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
1961 * will return all site use types
1958 *
1959 * DESCRIPTION
1960 * used by common party UI .
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 * 09-Sep-2010 Nishant Singhai o Bug 10040575: Modified get_acct_site_purposes function to
2026 * limit the return value to 1000 chars
2027 *
2028 **/
2029 function get_acct_site_purposes (
2030 p_acct_site_id IN NUMBER)
2031 RETURN VARCHAR2 IS
2032
2033 l_site_use_purpose VARCHAR2(100);
2034 l_all_site_use_purposes VARCHAR2(1000) := '';
2035
2036 cursor c_site_use_purposes (l_acct_site_id IN NUMBER) is
2037 select al.MEANING
2038 from hz_cust_acct_sites s,
2039 hz_cust_site_uses u,
2040 ar_lookups al
2041 where
2042 s.cust_acct_site_id = l_acct_site_id and
2043 u.cust_acct_site_id = s.cust_acct_site_id and
2044 u.status = 'A' and
2045 al.lookup_type = 'SITE_USE_CODE' and
2046 al.lookup_code = u.SITE_USE_CODE
2047 ORDER BY U.PRIMARY_FLAG DESC, u.creation_date, AL.MEANING;
2048
2049 BEGIN
2050
2051 OPEN c_site_use_purposes(p_acct_site_id);
2052 LOOP
2053 FETCH c_site_use_purposes INTO l_site_use_purpose;
2054
2055 IF c_site_use_purposes%NOTFOUND THEN
2056 EXIT;
2057 END IF;
2058
2059 -- Nishant Singhai (Bug 10040575 -- limit the fetch to 1000 chars)
2060 --(check random number 950 which is less than 1000 but should be sufficenit to accomodate
2061 -- a big site use value till 980 -990 char - assumption )
2062 IF length(l_all_site_use_purposes) > 950 THEN
2063 l_all_site_use_purposes := concat(l_all_site_use_purposes, ', ...');
2064 EXIT;
2065 END IF;
2066 -- End of fix for Bug 10040575
2067
2068 IF l_all_site_use_purposes is not null THEN
2069 l_all_site_use_purposes := concat(l_all_site_use_purposes, ', ');
2070 END IF;
2071
2072 l_all_site_use_purposes := concat(l_all_site_use_purposes, l_site_use_purpose);
2073
2074 END LOOP;
2075 CLOSE c_site_use_purposes;
2076
2077 RETURN l_all_site_use_purposes;
2078 END get_acct_site_purposes;
2079
2080
2081 /**
2082 * FUNCTION validate_flex_address
2083 *
2084 * DESCRIPTION
2085 * used by common party UI .
2086 * will validate the flex address
2087 * and return 'Y'/'N'
2088 * ARGUMENTS
2089 * IN:
2090 * p_context_value : context_value
2091 * p_address1 : address1
2092 * p_address2 : address2
2093 * p_address3 : address3
2094 * p_address4 : address4
2095 * p_address_lines_phonetic: address_lines_phonetic
2096 * p_city : city
2097 * p_county : county
2098 * p_postal_code : postal_code
2099 * p_province : province
2100 * p_state : state
2101 * p_attribute1 : attribute1
2102 * p_attribute2 : attribute2
2103 * p_attribute3 : attribute3
2104 * p_attribute4 : attribute4
2105 * p_attribute5 : attribute5
2106 * p_attribute6 : attribute6
2107 * p_attribute7 : attribute7
2108 * p_attribute8 : attribute8
2109 * p_attribute9 : attribute9
2110 * p_attribute10: attribute10
2111 * p_attribute11: attribute11
2112 * p_attribute12: attribute12
2113 * p_attribute13: attribute13
2114 * p_attribute14: attribute14
2115 * p_attribute15: attribute15
2116 * p_attribute16: attribute16
2117 * p_attribute17: attribute17
2118 * p_attribute18: attribute18
2119 * p_attribute19: attribute19
2120 * p_attribute20: attribute20
2121 * p_postal_plu4_code : postal_plu4_code --added against bug 7671107
2122 *
2126 FUNCTION validate_flex_address (
2123 * RETURNS : VARCHAR2
2124 *
2125 **/
2127 p_context_value IN VARCHAR2,
2128 p_address1 IN VARCHAR2,
2129 p_address2 IN VARCHAR2,
2130 p_address3 IN VARCHAR2,
2131 p_address4 IN VARCHAR2,
2132 p_address_lines_phonetic IN VARCHAR2,
2133 p_city IN VARCHAR2,
2134 p_county IN VARCHAR2,
2135 p_postal_code IN VARCHAR2,
2136 p_province IN VARCHAR2,
2137 p_state IN VARCHAR2,
2138 p_attribute1 IN VARCHAR2,
2139 p_attribute2 IN VARCHAR2,
2140 p_attribute3 IN VARCHAR2,
2141 p_attribute4 IN VARCHAR2,
2142 p_attribute5 IN VARCHAR2,
2143 p_attribute6 IN VARCHAR2,
2144 p_attribute7 IN VARCHAR2,
2145 p_attribute8 IN VARCHAR2,
2146 p_attribute9 IN VARCHAR2,
2147 p_attribute10 IN VARCHAR2,
2148 p_attribute11 IN VARCHAR2,
2149 p_attribute12 IN VARCHAR2,
2150 p_attribute13 IN VARCHAR2,
2151 p_attribute14 IN VARCHAR2,
2152 p_attribute15 IN VARCHAR2,
2153 p_attribute16 IN VARCHAR2,
2154 p_attribute17 IN VARCHAR2,
2155 p_attribute18 IN VARCHAR2,
2156 p_attribute19 IN VARCHAR2,
2157 p_attribute20 IN VARCHAR2,
2158 p_postal_plu4_code IN VARCHAR2 --added against bug 7671107
2159
2160 ) RETURN VARCHAR2 IS
2161
2162 l_return VARCHAR2(1) := 'N';
2163 appl_short_name varchar2(30) := 'AR';
2164 desc_flex_name varchar2(30) := 'Remit Address HZ';
2165 values_or_ids varchar2(10) := 'I'; --FND BUG 4220582
2166 validation_date DATE := SYSDATE;
2167 error_msg VARCHAR2(5000);
2168 errors_received EXCEPTION;
2169
2170 BEGIN
2171
2172 --*********************************************************
2173 --* set the context value *
2174 --*********************************************************
2175 FND_FLEX_DESCVAL.set_context_value(p_context_value);
2176 --*********************************************************
2177 --* set the address attributes value *
2178 --*********************************************************
2179 fnd_flex_descval.set_column_value('ADDRESS1', p_address1 );
2180 fnd_flex_descval.set_column_value('ADDRESS2', p_address2 );
2181 fnd_flex_descval.set_column_value('ADDRESS3', p_address3 );
2182 fnd_flex_descval.set_column_value('ADDRESS4', p_address4 );
2183 fnd_flex_descval.set_column_value('ADDRESS_LINES_PHONETIC', p_address_lines_phonetic );
2184 fnd_flex_descval.set_column_value('CITY', p_city );
2185 fnd_flex_descval.set_column_value('COUNTY', p_county );
2186 fnd_flex_descval.set_column_value('STATE', p_state );
2187 fnd_flex_descval.set_column_value('PROVINCE', p_province );
2188 fnd_flex_descval.set_column_value('POSTAL_CODE', p_postal_code);
2189 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1 );
2190 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2 );
2191 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3 );
2192 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4 );
2193 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5 );
2194 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6 );
2195 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7 );
2196 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8 );
2197 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9 );
2198 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10 );
2199 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11 );
2200 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12 );
2201 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13 );
2202 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14 );
2203 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15 );
2204 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attribute16 );
2205 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attribute17 );
2206 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attribute18 );
2207 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attribute19 );
2208 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attribute20 );
2209 fnd_flex_descval.set_column_value('POSTAL_PLUS4_CODE', p_postal_plu4_code); -- added against bug 7671107
2210
2211 IF FND_FLEX_DESCVAL.validate_desccols(
2212 appl_short_name,
2213 desc_flex_name,
2214 values_or_ids,
2215 validation_date)
2216 THEN
2217 l_return := 'Y';
2218 return l_return;
2219 ELSE
2220 RAISE errors_received;
2221 END IF;
2222
2223 EXCEPTION
2224 WHEN errors_received THEN
2225 error_msg := fnd_flex_descval.encoded_error_message;
2226 FND_MESSAGE.SET_ENCODED(error_msg);
2227 FND_MSG_PUB.Add;
2228 return l_return;
2229 WHEN others THEN
2230 return l_return;
2231
2232 END validate_flex_address;
2233
2234 /**
2238 * used by common party UI .
2235 * FUNCTION validate_desc_flex
2236 *
2237 * DESCRIPTION
2239 * will validate the descriptive flex
2240 * and return 'Y'/'N'
2241 * ARGUMENTS
2242 * IN:
2243 * p_appl_short_name:appl_short_name
2244 * p_desc_flex_name :desc_flex_name
2245 * p_context_value : context_value
2246 * p_attribute1 : attribute1
2247 * p_attribute2 : attribute2
2248 * p_attribute3 : attribute3
2249 * p_attribute4 : attribute4
2250 * p_attribute5 : attribute5
2251 * p_attribute6 : attribute6
2252 * p_attribute7 : attribute7
2253 * p_attribute8 : attribute8
2254 * p_attribute9 : attribute9
2255 * p_attribute10: attribute10
2256 * p_attribute11: attribute11
2257 * p_attribute12: attribute12
2258 * p_attribute13: attribute13
2259 * p_attribute14: attribute14
2260 * p_attribute15: attribute15
2261 * p_attribute16: attribute16
2262 * p_attribute17: attribute17
2263 * p_attribute18: attribute18
2264 * p_attribute19: attribute19
2265 * p_attribute20: attribute20
2266 * p_attribute21: attribute21
2267 * p_attribute22: attribute22
2268 * p_attribute23: attribute23
2269 * p_attribute24: attribute24
2270 * RETURNS : VARCHAR2
2271 *
2272 **/
2273 FUNCTION validate_desc_flex (
2274 p_appl_short_name IN VARCHAR2,
2275 p_desc_flex_name IN VARCHAR2,
2276 p_context_value IN VARCHAR2,
2277 p_attribute1 IN VARCHAR2,
2278 p_attribute2 IN VARCHAR2,
2279 p_attribute3 IN VARCHAR2,
2280 p_attribute4 IN VARCHAR2,
2281 p_attribute5 IN VARCHAR2,
2282 p_attribute6 IN VARCHAR2,
2283 p_attribute7 IN VARCHAR2,
2284 p_attribute8 IN VARCHAR2,
2285 p_attribute9 IN VARCHAR2,
2286 p_attribute10 IN VARCHAR2,
2287 p_attribute11 IN VARCHAR2,
2288 p_attribute12 IN VARCHAR2,
2289 p_attribute13 IN VARCHAR2,
2290 p_attribute14 IN VARCHAR2,
2291 p_attribute15 IN VARCHAR2,
2292 p_attribute16 IN VARCHAR2,
2293 p_attribute17 IN VARCHAR2,
2294 p_attribute18 IN VARCHAR2,
2295 p_attribute19 IN VARCHAR2,
2296 p_attribute20 IN VARCHAR2,
2297 p_attribute21 IN VARCHAR2,
2298 p_attribute22 IN VARCHAR2,
2299 p_attribute23 IN VARCHAR2,
2300 p_attribute24 IN VARCHAR2
2301 ) RETURN VARCHAR2 IS
2302
2303 l_return VARCHAR2(1) := 'N';
2304 appl_short_name varchar2(30) := p_appl_short_name;
2305 desc_flex_name varchar2(30) := p_desc_flex_name;
2306 values_or_ids varchar2(10) := 'I'; --Bug 5356950
2307 validation_date DATE := SYSDATE;
2308 error_msg VARCHAR2(5000);
2309 errors_received EXCEPTION;
2310
2311 BEGIN
2312
2313 --*********************************************************
2314 --* set the context value *
2315 --*********************************************************
2316 FND_FLEX_DESCVAL.set_context_value(p_context_value);
2317 --*********************************************************
2318 --* set the attributes value *
2319 --*********************************************************
2320 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_attribute1 );
2321 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_attribute2 );
2322 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_attribute3 );
2323 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_attribute4 );
2324 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_attribute5 );
2325 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_attribute6 );
2326 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_attribute7 );
2327 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_attribute8 );
2328 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_attribute9 );
2329 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_attribute10 );
2330 fnd_flex_descval.set_column_value('ATTRIBUTE11', p_attribute11 );
2331 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_attribute12 );
2332 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_attribute13 );
2333 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_attribute14 );
2334 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_attribute15 );
2335 fnd_flex_descval.set_column_value('ATTRIBUTE16', p_attribute16 );
2336 fnd_flex_descval.set_column_value('ATTRIBUTE17', p_attribute17 );
2337 fnd_flex_descval.set_column_value('ATTRIBUTE18', p_attribute18 );
2338 fnd_flex_descval.set_column_value('ATTRIBUTE19', p_attribute19 );
2339 fnd_flex_descval.set_column_value('ATTRIBUTE20', p_attribute20 );
2340 IF p_desc_flex_name <> 'HZ_PARTY_SITES' THEN
2341 fnd_flex_descval.set_column_value('ATTRIBUTE21', p_attribute21 );
2342 fnd_flex_descval.set_column_value('ATTRIBUTE22', p_attribute22 );
2343 fnd_flex_descval.set_column_value('ATTRIBUTE23', p_attribute23 );
2344 fnd_flex_descval.set_column_value('ATTRIBUTE24', p_attribute24 );
2345 END IF;
2346
2347 IF FND_FLEX_DESCVAL.validate_desccols(
2351 validation_date)
2348 appl_short_name,
2349 desc_flex_name,
2350 values_or_ids,
2352 THEN
2353 l_return := 'Y';
2354 return l_return;
2355 ELSE
2356 RAISE errors_received;
2357 END IF;
2358
2359 EXCEPTION
2360 WHEN errors_received THEN
2361 error_msg := fnd_flex_descval.encoded_error_message;
2362 FND_MESSAGE.SET_ENCODED(error_msg);
2363 FND_MSG_PUB.Add;
2364 return l_return;
2365 WHEN others THEN
2366 return l_return;
2367
2368 END validate_desc_flex;
2369
2370 /**
2371 * FUNCTION get_org_contact_role
2372 *
2373 * DESCRIPTION
2374 * used by common party UI .
2375 * added by albert (tsli)
2376 * will return the first three org contact roles
2377
2378 * ARGUMENTS
2379 * IN:
2380 * p_org_contact_id org contact id used to retrieve the org contact roles.
2381 *
2382 * RETURNS : VARCHAR2
2383 *
2384 **/
2385
2386 FUNCTION get_org_contact_role (
2387 p_org_contact_id IN NUMBER
2388 ) RETURN VARCHAR2 IS
2389
2390 TYPE varchar2_table IS TABLE OF VARCHAR2(80); --5960623
2391 org_contact_roles_tab varchar2_table;
2392
2393 l_top3_org_contact_roles VARCHAR2(100);
2394 l_count NUMBER;
2395
2396 CURSOR c_org_contact_roles IS
2397 SELECT lu.meaning
2398 FROM hz_org_contact_roles ocr,
2399 fnd_lookup_values lu
2400 WHERE ocr.org_contact_id = p_org_contact_id
2401 AND ocr.status = 'A'
2402 AND lu.view_application_id = 222
2403 AND lu.language = userenv('LANG')
2404 AND lu.lookup_type = 'CONTACT_ROLE_TYPE'
2405 AND lu.lookup_code = ocr.role_type;
2406
2407 BEGIN
2408
2409 OPEN c_org_contact_roles;
2410 FETCH c_org_contact_roles BULK COLLECT INTO org_contact_roles_tab;
2411
2412 l_top3_org_contact_roles := '';
2413
2414 IF org_contact_roles_tab.count > 0 THEN
2415 l_count := 1;
2416
2417 WHILE (l_count <= 3 AND l_count <= org_contact_roles_tab.count)
2418 LOOP
2419 IF l_count > 1 THEN
2420 l_top3_org_contact_roles := l_top3_org_contact_roles || ', ';
2421 END IF;
2422
2423 l_top3_org_contact_roles := l_top3_org_contact_roles ||
2424 org_contact_roles_tab(l_count);
2425
2426
2427 l_count := l_count + 1;
2428 END LOOP;
2429
2430 IF org_contact_roles_tab.count > 3 THEN
2431 l_top3_org_contact_roles := l_top3_org_contact_roles || ', ...';
2432 END IF;
2433 END IF;
2434 CLOSE c_org_contact_roles;
2435
2436 RETURN l_top3_org_contact_roles;
2437
2438 END get_org_contact_role;
2439
2440 /**
2441 * FUNCTION get_primary_phone
2442 *
2443 * DESCRIPTION
2444 * used by common party UI .
2445 * added by albert (tsli)
2446 * will return the primary phone
2447 * ARGUMENTS
2448 * IN:
2449 * p_party_id party id used to retrieve the primary phone
2450 *
2451 * RETURNS : VARCHAR2
2452 *
2453 **/
2454
2455 function get_primary_phone (
2456 p_party_id IN NUMBER,
2457 p_display_purpose IN VARCHAR2 := fnd_api.g_true)
2458 RETURN VARCHAR2 IS
2459 l_contact_point_id NUMBER;
2460 BEGIN
2461
2462 BEGIN
2463 select
2464 contact_point_id
2465 INTO l_contact_point_id
2466 from hz_contact_points CPPH
2467 where contact_point_type = 'PHONE'
2468 and primary_flag = 'Y'
2469 and status = 'A'
2470 and OWNER_TABLE_NAME = 'HZ_PARTIES'
2471 and OWNER_TABLE_ID = p_party_id
2472 and rownum = 1;
2473
2474 EXCEPTION
2475 WHEN NO_DATA_FOUND THEN
2476 RETURN NULL;
2477 END;
2478
2479 RETURN hz_format_phone_v2pub.get_formatted_phone(l_contact_point_id,p_display_purpose);
2480 END get_primary_phone;
2481
2482 /**
2483 * FUNCTION get_primary_email
2484 *
2485 * DESCRIPTION
2486 * used by common party UI .
2487 * added by albert (tsli)
2488 * will return the primary email
2489 * ARGUMENTS
2490 * IN:
2491 * p_party_id party id used to retrieve the primary email
2492 *
2493 * RETURNS : VARCHAR2
2494 *
2495 **/
2496 function get_primary_email (
2497 p_party_id IN NUMBER)
2498 RETURN VARCHAR2 IS
2499 l_primary_email VARCHAR2(2000);
2500 BEGIN
2501
2502 select email_address
2503 INTO l_primary_email
2504 from hz_contact_points
2505 where contact_point_type = 'EMAIL'
2506 and primary_flag = 'Y'
2507 and status = 'A'
2508 and OWNER_TABLE_NAME = 'HZ_PARTIES'
2509 and OWNER_TABLE_ID = p_party_id
2510 and rownum = 1;
2511
2512 RETURN l_primary_email;
2513 END get_primary_email;
2514
2515 PROCEDURE find_index_name(
2516 p_index_name OUT NOCOPY VARCHAR2) IS
2517 tmp_errm VARCHAR2(500);
2518 n NUMBER;
2519 m NUMBER;
2520 i NUMBER;
2521 BEGIN
2522 n := INSTR(sqlerrm, '(', 1,1) ;
2523 m := INSTR(sqlerrm, ')', 1,1) ;
2524 i := m -n;
2525 tmp_errm := SUBSTRB(sqlerrm, n, i);
2526 n := INSTR(tmp_errm, '.', 1,1) ;
2527 p_index_name := SUBSTRB(tmp_errm, n+1, i-1);
2528 END find_index_name;
2529
2530
2531 /**
2532 * FUNCTION GET_YAHOO_MAP_URL
2533 *
2534 * DESCRIPTION
2535 * function that would return a html link tag which
2536 * will contain the address formatted for Yahoo Maps.
2537 * ARGUMENTS
2538 * IN:
2539 * address1 IN VARCHAR2,
2540 * address2 IN VARCHAR2,
2541 * address3 IN VARCHAR2,
2542 * address4 IN VARCHAR2,
2543 * city IN VARCHAR2,
2544 * country IN VARCHAR2,
2545 * state IN VARCHAR2,
2546 * postal_code IN VARCHAR2
2547 *
2548 * RETURNS : VARCHAR2
2549 *
2550 **/
2551
2552 FUNCTION GET_YAHOO_MAP_URL(address1 IN VARCHAR2,
2553 address2 IN VARCHAR2,
2554 address3 IN VARCHAR2,
2555 address4 IN VARCHAR2,
2556 city IN VARCHAR2,
2557 country IN VARCHAR2,
2558 state IN VARCHAR2,
2559 postal_code IN VARCHAR2)
2560 RETURN VARCHAR2 AS
2561 url VARCHAR2(200);
2562 url2 VARCHAR2(700);
2563 country_code VARCHAR2(20);
2564 amp VARCHAR2(01) := '&';
2565 staticURL VARCHAR2(100) := 'http://maps.yahoo.com/py/maps.py?BFCat=' || amp || 'Pyt=Tmap' || amp || 'newFL=Use+Address+Below' || amp || 'Get Map=Get+Map';
2566
2567 BEGIN
2568
2569 -- Since TCA validates the country code of UK to GB and Yahoo Maps expects 'uk'
2570 -- we resort to this work around
2571 IF upper(rtrim(country)) = 'GB'
2572 THEN
2573 country_code := 'UK';
2574 ELSE
2575 country_code := rtrim(country);
2576 END IF;
2577
2578 URL2 := staticURL ||
2579 amp || 'addr=' || REPLACE(address1, ' ', '+') ||
2580 amp || 'csz=' || REPLACE(city, ' ', '+') || '+' || RTRIM(state) || '+' || RTRIM(postal_code) ||
2581 amp || 'country=' || country_code;
2582
2583 URL := substrb(URL2, 1, 200);
2584 RETURN url;
2585 END GET_YAHOO_MAP_URL;
2586
2587 /**
2588 * FUNCTION IS_PARTY_ID_IN_REQUEST_LOG
2589 *
2590 * DESCRIPTION
2591 * function that would return a 'Y' if this party_id exist in hz_dnb_request_log
2592 * return 'N' if not.
2593 * ARGUMENTS
2594 * party_id IN NUMBER
2595 *
2596 * RETURNS : VARCHAR2
2597 */
2598
2599 FUNCTION IS_PARTY_ID_IN_REQUEST_LOG(
2600 p_party_id IN NUMBER)
2601
2602 RETURN VARCHAR2 AS
2603
2604 l_exist varchar2(1) := 'N';
2605
2606 /* Bug 3301467 : Comment the cursor code
2607
2608 CURSOR c IS
2609 select 'N'
2610 from hz_dnb_request_log log, hz_organization_profiles org
2611 where log.party_id= p_party_id
2612 and org.party_id=log.party_id
2613 and org.actual_content_source='DNB'
2614 and org.effective_end_date is NULL
2615 and trunc(org.last_update_date) > trunc(log.last_update_date)
2616 and rownum =1
2617 UNION
2618 select 'Y'
2619 from hz_dnb_request_log log, hz_organization_profiles org
2620 where log.party_id= p_party_id
2621 and org.party_id=log.party_id
2622 and org.actual_content_source='DNB'
2623 and org.effective_end_date is NULL
2624 and trunc(org.last_update_date) = trunc(log.last_update_date)
2625 and rownum =1;
2626 */
2627
2628 BEGIN
2629
2630 select 'E' into l_exist
2631 from hz_dnb_request_log
2632 where party_id = p_party_id
2633 and rownum = 1;
2634
2635 if l_exist = 'E' then
2636 /*
2637 open c;
2638 fetch c into l_exist;
2639 close c;
2640 */
2641
2642 -- Bug 3301467 : Add below statement to check if the last purchased package for
2643 -- a party is through online mode or batch mode.
2644 -- If it is online mode, return 'Y' else 'N'
2645 Begin
2646 select 'Y' into l_exist from hz_organization_profiles org
2647 where org.party_id = p_party_id
2648 and org.actual_content_source='DNB'
2649 and org.effective_end_date is NULL
2650 and trunc(org.last_update_date) =
2651 (select trunc(max(log.last_update_date)) from hz_dnb_request_log log
2652 where log.party_id = p_party_id);
2653 Exception
2654 when no_data_found then
2655 l_exist := 'N';
2656 end;
2657 end if;
2658
2659 return l_exist;
2660
2661
2662 EXCEPTION
2663 WHEN NO_DATA_FOUND THEN
2664 return l_exist;
2665 END IS_PARTY_ID_IN_REQUEST_LOG;
2666
2667
2668 /**
2669 * FUNCTION get_message
2670 *
2671 * DESCRIPTION
2672 * returns the translated message
2673 * ARGUMENTS
2674 * message_name
2675 * token1_name, token1_value
2676 * token2_name, token2_value
2677 * token3_name, token3_value
2678 * token4_name, token4_value
2679 * token5_name, token5_value
2680 *
2681 * RETURNS : VARCHAR2: token sustituted, translated message
2682 */
2683 FUNCTION get_message(
2684 app_short_name IN VARCHAR2,
2685 message_name IN varchar2,
2686 token1_name IN VARCHAR2,
2687 token1_value IN VARCHAR2,
2688 token2_name IN VARCHAR2,
2689 token2_value IN VARCHAR2,
2690 token3_name IN VARCHAR2,
2691 token3_value IN VARCHAR2,
2692 token4_name IN VARCHAR2,
2693 token4_value IN VARCHAR2,
2694 token5_name IN VARCHAR2,
2695 token5_value IN VARCHAR2)
2696 RETURN VARCHAR2 AS
2697 BEGIN
2698 -- Flow
2699 -- 1. Set the Message Name
2700 -- 2. Set the token(s).
2701 -- 3. get translated message and return it.
2702
2703 FND_MESSAGE.SET_NAME(app_short_name,message_name);
2704 -- not using the CASE statement to make it backward compatible.
2705 IF token1_name IS NOT NULL THEN
2706 FND_MESSAGE.SET_TOKEN(token1_name,token1_value);
2707 END IF;
2708 IF token2_name IS NOT NULL THEN
2709 FND_MESSAGE.SET_TOKEN(token2_name,token2_value);
2710 END IF;
2711 IF token3_name IS NOT NULL THEN
2712 FND_MESSAGE.SET_TOKEN(token3_name,token3_value);
2713 END IF;
2714 IF token4_name IS NOT NULL THEN
2715 FND_MESSAGE.SET_TOKEN(token4_name,token4_value);
2716 END IF;
2717 IF token5_name IS NOT NULL THEN
2718 FND_MESSAGE.SET_TOKEN(token5_name,token5_value);
2719 END IF;
2720
2721 return FND_MESSAGE.get;
2722
2723 EXCEPTION
2724 WHEN NO_DATA_FOUND THEN
2725 return 'NO MESSAGE FOUND';
2726 WHEN OTHERS THEN
2727 return 'NO MESSAGE FOUND';
2728 END get_message;
2729
2730
2731 /**
2732 * FUNCTION is_restriction_exist
2733 *
2734 * DESCRIPTION
2735 * used by common party UI .
2736 * will return a flag to indicate if contact preference exist
2737 * ARGUMENTS
2738 * IN:
2739 * p_contact_level_table contact level table
2740 * p_contact_level_table_id contact level table id
2741 * p_preference_code preference code
2742 *
2743 * RETURNS : VARCHAR2
2744 *
2745 **/
2746
2747 FUNCTION is_restriction_exist (
2748 p_contact_level_table IN VARCHAR2,
2749 p_contact_level_table_id IN NUMBER,
2750 p_preference_code IN VARCHAR2
2751 ) RETURN VARCHAR2 IS
2752
2753 CURSOR c_restriction IS
2754 SELECT null
2755 FROM hz_contact_preferences
2756 WHERE contact_level_table = p_contact_level_table
2757 AND contact_level_table_id = p_contact_level_table_id
2758 AND preference_code = p_preference_code
2759 AND status = 'A'
2760 AND (preference_end_date IS NULL OR
2761 TRUNC(preference_end_date) >= TRUNC(sysdate))
2762 AND TRUNC(preference_start_date) <= TRUNC(sysdate)
2763 AND ROWNUM = 1;
2764
2765 l_dummy VARCHAR2(1);
2766 l_return VARCHAR2(1);
2767
2768 BEGIN
2769
2770 IF p_contact_level_table_id IS NULL THEN
2771 RETURN 'N';
2772 END IF;
2773
2774 OPEN c_restriction;
2775 FETCH c_restriction INTO l_dummy;
2776 IF c_restriction%NOTFOUND THEN
2777 l_return := 'N';
2778 ELSE
2779 l_return := 'Y';
2780 END IF;
2781 CLOSE c_restriction;
2782
2783 RETURN l_return;
2784
2785 END is_restriction_exist;
2786
2787 /**
2788 * FUNCTION is_purchased_content_source
2789 *
2790 * DESCRIPTION
2791 * This function will return 'Y' if the source system is a purchased one.
2792 * (i.e HZ_ORIG_SYSTEMS_B.orig_system_type = 'PURCHASED')
2793 *
2794 * ARGUMENTS
2795 * IN:
2796 * p_content_source
2797 *
2798 * RETURNS : VARCHAR2
2799 *
2800 * NOTES
2801 *
2802 * MODIFICATION HISTORY
2803 *
2804 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2805 *
2806 **/
2807 FUNCTION is_purchased_content_source (
2808 p_content_source IN VARCHAR2
2809 ) RETURN VARCHAR2 IS
2810
2811 CURSOR c_content_source IS
2812 SELECT 'Y'
2813 FROM HZ_ORIG_SYSTEMS_B
2814 WHERE orig_system = p_content_source
2815 AND orig_system_type = 'PURCHASED'
2816 -- AND status = 'A'
2817 AND rownum = 1;
2818
2819 l_return_value VARCHAR2(1);
2820
2821 BEGIN
2822
2823 OPEN c_content_source;
2827 END IF;
2824 FETCH c_content_source INTO l_return_value;
2825 IF c_content_source%NOTFOUND THEN
2826 l_return_value := 'N';
2828 CLOSE c_content_source;
2829
2830 RETURN l_return_value;
2831
2832 END is_purchased_content_source;
2833
2834 /**
2835 * FUNCTION get_lookupMeaning_lang
2836 *
2837 * DESCRIPTION
2838 * This function will return the meaning in FND_LOOKUP_VALUES for the given combination
2839 * of lookup_type, lookup_code and language.
2840 *
2841 * ARGUMENTS
2842 * IN:
2843 * p_lookup_type
2844 * p_lookup_code
2845 * p_language
2846 *
2847 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Meaning)
2848 *
2849 * NOTES
2850 *
2851 * MODIFICATION HISTORY
2852 *
2853 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2854 *
2855 **/
2856 FUNCTION get_lookupMeaning_lang (
2857 p_lookup_type IN VARCHAR2,
2858 p_lookup_code IN VARCHAR2,
2859 p_language IN VARCHAR2
2860 ) RETURN VARCHAR2 IS
2861
2862 CURSOR c_lookup_meaning_for_lang IS
2863 SELECT meaning
2864 FROM FND_LOOKUP_VALUES
2865 WHERE lookup_type = p_lookup_type
2866 AND lookup_code = p_lookup_code
2867 AND language = p_language
2868 AND enabled_flag = 'Y'
2869 AND (end_date_active IS NULL
2870 OR end_date_active >= sysdate);
2871
2872 l_return_value VARCHAR2(80);
2873
2874 BEGIN
2875
2876 OPEN c_lookup_meaning_for_lang;
2877 FETCH c_lookup_meaning_for_lang INTO l_return_value;
2878 IF c_lookup_meaning_for_lang%NOTFOUND THEN
2879 l_return_value := NULL ;
2880 END IF;
2881 CLOSE c_lookup_meaning_for_lang;
2882
2883 RETURN l_return_value;
2884
2885 END get_lookupMeaning_lang;
2886
2887 /**
2888 * FUNCTION get_lookupDesc_lang
2889 *
2890 * DESCRIPTION
2891 * This function will return the description in FND_LOOKUP_VALUES for the given combination
2892 * of lookup_type, lookup_code and language.
2893 *
2894 * ARGUMENTS
2895 * IN:
2896 * p_lookup_type
2897 * p_lookup_code
2898 * p_language
2899 *
2900 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Description)
2901 *
2902 * NOTES
2903 *
2904 * MODIFICATION HISTORY
2905 *
2906 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
2907 *
2908 **/
2909 FUNCTION get_lookupDesc_lang (
2910 p_lookup_type IN VARCHAR2,
2911 p_lookup_code IN VARCHAR2,
2912 p_language IN VARCHAR2
2913 ) RETURN VARCHAR2 IS
2914
2915 CURSOR c_lookup_desc_for_lang IS
2916 SELECT description
2917 FROM FND_LOOKUP_VALUES
2918 WHERE lookup_type = p_lookup_type
2919 AND lookup_code = p_lookup_code
2920 AND language = p_language
2921 AND enabled_flag = 'Y'
2922 AND (end_date_active IS NULL
2923 OR end_date_active >= sysdate);
2924
2925 l_return_value VARCHAR2(240);
2926
2927 BEGIN
2928
2929 OPEN c_lookup_desc_for_lang;
2930 FETCH c_lookup_desc_for_lang INTO l_return_value;
2931 IF c_lookup_desc_for_lang%NOTFOUND THEN
2932 l_return_value := NULL ;
2933 END IF;
2934 CLOSE c_lookup_desc_for_lang;
2935
2936 RETURN l_return_value;
2937
2938 END get_lookupDesc_lang;
2939
2940
2941
2942 /**
2943 * FUNCTION check_prim_bill_to_site
2944 *
2945 * DESCRIPTION
2946 * used by common party UI .
2947 * will return Y if the party site is the primary Bill_To site.
2948 * will return N in other cases
2949
2950 * ARGUMENTS
2951 * IN:
2952 * p_party_site_id party site id used to retrieve the site use purpose.
2953 *
2954 * RETURNS : VARCHAR2
2955 *
2956 **/
2957 function check_prim_bill_to_site (
2958 p_party_site_id IN NUMBER)
2959 RETURN VARCHAR2 IS
2960
2961
2962 l_bill_to_primary VARCHAR2(1000);
2963 l_primary VARCHAR2(10);
2964 cursor c_site_bill_to (l_party_site_id IN NUMBER) is
2965 select psu.primary_per_type
2966 from hz_party_site_uses psu
2967 where
2968 psu.party_site_id = l_party_site_id and
2969 psu.status = 'A' and
2970 psu.site_use_type = 'BILL_TO' and
2971 psu.primary_per_type = 'Y'
2972 order by primary_per_type DESC;
2973
2974 BEGIN
2975
2976 l_bill_to_primary := 'N';
2977 OPEN c_site_bill_to(p_party_site_id);
2978 LOOP
2979 FETCH c_site_bill_to INTO l_primary;
2980
2981 IF c_site_bill_to%NOTFOUND THEN
2982 EXIT;
2983 END IF;
2984
2985 IF l_primary = 'Y' THEN
2986 l_bill_to_primary := 'Y';
2987 exit;
2988 END IF;
2989
2990 END LOOP;
2991 CLOSE c_site_bill_to;
2992
2993 RETURN l_bill_to_primary;
2994 END check_prim_bill_to_site;
2995
2996
2997 /**
2998 * FUNCTION check_prim_ship_to_site
2999 *
3000 * DESCRIPTION
3001 * used by common party UI .
3002 * will return Y if the party site is the primary Ship_To site.
3003 * will return N in other cases
3007 * p_party_site_id party site id used to retrieve the site use purpose.
3004
3005 * ARGUMENTS
3006 * IN:
3008 *
3009 * RETURNS : VARCHAR2
3010 *
3011 **/
3012 function check_prim_ship_to_site (
3013 p_party_site_id IN NUMBER)
3014 RETURN VARCHAR2 IS
3015
3016
3017 l_ship_to_primary VARCHAR2(1000);
3018 l_primary VARCHAR2(10);
3019 cursor c_site_ship_to (l_party_site_id IN NUMBER) is
3020 select psu.primary_per_type
3021 from hz_party_site_uses psu
3022 where
3023 psu.party_site_id = l_party_site_id and
3024 psu.status = 'A' and
3025 psu.site_use_type = 'SHIP_TO' and
3026 psu.primary_per_type = 'Y'
3027 order by primary_per_type DESC;
3028
3029 BEGIN
3030
3031 l_ship_to_primary := 'N';
3032 OPEN c_site_ship_to(p_party_site_id);
3033 LOOP
3034 FETCH c_site_ship_to INTO l_primary;
3035
3036 IF c_site_ship_to%NOTFOUND THEN
3037 EXIT;
3038 END IF;
3039
3040 IF l_primary = 'Y' THEN
3041 l_ship_to_primary := 'Y';
3042 exit;
3043 END IF;
3044
3045 END LOOP;
3046 CLOSE c_site_ship_to;
3047
3048 RETURN l_ship_to_primary;
3049 END check_prim_ship_to_site;
3050
3051
3052 /**
3053 * PROCEDURE validate_created_by_module
3054 *
3055 * DESCRIPTION
3056 * validate created by module
3057 * ARGUMENTS
3058 * IN:
3059 * p_create_update_flag create update flag
3060 * p_created_by_module created by module
3061 * p_old_created_by_module old value of created by module
3062 * x_return_status return status
3063 */
3064
3065 PROCEDURE validate_created_by_module (
3066 p_create_update_flag IN VARCHAR2,
3067 p_created_by_module IN VARCHAR2,
3068 p_old_created_by_module IN VARCHAR2,
3069 x_return_status IN OUT NOCOPY VARCHAR2
3070 ) IS
3071
3072 BEGIN
3073
3074 -- skip mandatory and non-updateable check from logical API
3075 IF G_CALLING_API IS NULL THEN
3076 -- created_by_module is mandatory field
3077 -- Since created_by_module is non-updateable, we only need to check mandatory
3078 -- during creation.
3079
3080 IF p_create_update_flag = 'C' THEN
3081 validate_mandatory (
3082 p_create_update_flag => p_create_update_flag,
3083 p_column => 'created_by_module',
3084 p_column_value => p_created_by_module,
3085 x_return_status => x_return_status);
3086
3087 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3088 debug(
3089 p_prefix => '',
3090 p_message => 'created_by_module is mandatory. ' ||
3091 'x_return_status = ' || x_return_status,
3092 p_msg_level => fnd_log.level_statement);
3093 END IF;
3094 END IF;
3095
3096 -- created_by_module is non-updateable field. But it can be updated from
3097 -- NULL to some value.
3098
3099 IF p_create_update_flag = 'U' AND
3100 p_created_by_module IS NOT NULL
3101 THEN
3102 validate_nonupdateable (
3103 p_column => 'created_by_module',
3104 p_column_value => p_created_by_module,
3105 p_old_column_value => p_old_created_by_module,
3106 p_restricted => 'N',
3107 x_return_status => x_return_status);
3108
3109 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3110 debug(
3111 p_prefix => '',
3112 p_message => 'created_by_module is non-updateable. It can be updated from NULL to a value. ' ||
3113 'x_return_status = ' || x_return_status,
3114 p_msg_level => fnd_log.level_statement);
3115 END IF;
3116 END IF;
3117 END IF;
3118
3119 -- created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES
3120 IF p_created_by_module IS NOT NULL AND
3121 p_created_by_module <> fnd_api.g_miss_char AND
3122 (p_create_update_flag = 'C' OR
3123 (p_create_update_flag = 'U' AND
3124 (p_old_created_by_module IS NULL OR
3125 p_created_by_module <> p_old_created_by_module)))
3126 THEN
3127 validate_lookup (
3128 p_column => 'created_by_module',
3129 p_lookup_type => 'HZ_CREATED_BY_MODULES',
3130 p_column_value => p_created_by_module,
3131 x_return_status => x_return_status);
3132
3133 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3134 debug(
3135 p_prefix => '',
3136 p_message => 'created_by_module is lookup code in lookup type HZ_CREATED_BY_MODULES. ' ||
3137 'x_return_status = ' || x_return_status,
3138 p_msg_level => fnd_log.level_statement);
3139 END IF;
3140 END IF;
3141
3142 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3143 debug(
3144 p_prefix => '',
3145 p_message => 'after validate created_by_module ... ' ||
3146 'x_return_status = ' || x_return_status,
3147 p_msg_level => fnd_log.level_statement);
3148 END IF;
3149
3150 END validate_created_by_module;
3151
3152
3153 /**
3154 * PROCEDURE validate_application_id
3155 *
3156 * DESCRIPTION
3157 * validate application id
3158 * ARGUMENTS
3159 * IN:
3160 * p_create_update_flag create update flag
3161 * p_application_id application id
3162 * p_old_application_id old value of application id
3163 * x_return_status return status
3164 */
3165
3166 PROCEDURE validate_application_id (
3167 p_create_update_flag IN VARCHAR2,
3168 p_application_id IN NUMBER,
3169 p_old_application_id IN NUMBER,
3170 x_return_status IN OUT NOCOPY VARCHAR2
3171 ) IS
3172
3173 l_column CONSTANT VARCHAR2(30) := 'application_id';
3174
3175 BEGIN
3176
3177 -- skip non-updateable check from logical API
3178 IF G_CALLING_API IS NULL THEN
3179 -- application_id is non-updateable field. But it can be updated from NULL
3180 -- to some value.
3181
3182 IF p_create_update_flag = 'U' AND
3183 p_application_id IS NOT NULL
3184 THEN
3185 validate_nonupdateable (
3186 p_column => l_column,
3187 p_column_value => p_application_id,
3188 p_old_column_value => p_old_application_id,
3189 p_restricted => 'N',
3190 x_return_status => x_return_status);
3191
3192 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3193 debug(
3194 p_prefix => '',
3195 p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
3196 'x_return_status = ' || x_return_status,
3197 p_msg_level => fnd_log.level_statement);
3198 END IF;
3199 END IF;
3200 END IF;
3201
3202 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3203 debug(
3204 p_prefix => '',
3205 p_message => 'after validate ' || l_column || ' ... ' ||
3206 'x_return_status = ' || x_return_status,
3207 p_msg_level => fnd_log.level_statement);
3208 END IF;
3209
3210 END validate_application_id;
3211
3212
3213 /**
3214 * FUNCTION is_role_in_relationship_group
3215 *
3216 * DESCRIPTION
3217 * return if a role exists in a relationship group
3218 * ARGUMENTS
3219 * IN:
3220 * p_relationship_type_id relationship type id
3221 * p_relationship_group_code relationship group code
3222 */
3223
3224 FUNCTION is_role_in_relationship_group (
3225 p_relationship_type_id IN NUMBER,
3226 p_relationship_group_code IN VARCHAR2
3227 ) RETURN VARCHAR2 IS
3228
3229 CURSOR c_groups IS
3230 SELECT null
3231 FROM hz_code_assignments c
3232 WHERE c.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
3233 AND c.class_category = 'RELATIONSHIP_TYPE_GROUP'
3234 AND c.class_code = p_relationship_group_code
3235 AND sysdate between c.start_date_active and nvl(c.end_date_active, sysdate+1)
3236 AND c.status = 'A'
3237 AND c.owner_table_id = p_relationship_type_id
3238 AND ROWNUM = 1;
3239
3240 l_dummy VARCHAR2(1);
3241 l_return VARCHAR2(1);
3242
3243 BEGIN
3244
3245 OPEN c_groups;
3246 FETCH c_groups INTO l_dummy;
3247 IF c_groups%NOTFOUND THEN
3248 l_return := 'N';
3249 ELSE
3250 l_return := 'Y';
3251 END IF;
3252 CLOSE c_groups;
3253
3254 RETURN l_return;
3255
3256 END is_role_in_relationship_group;
3257
3258 --Bug15942984
3259 /**
3260 * FUNCTION G_EXECUTE_API_CALLOUTS
3261 *
3262 * DESCRIPTION
3263 * Return Profile 'HZ_EXECUTE_API_CALLOUTS' value.
3264 */
3265 FUNCTION g_execute_api_callouts RETURN VARCHAR2 IS
3266 BEGIN
3267
3268 RETURN FND_PROFILE.VALUE('HZ_EXECUTE_API_CALLOUTS');
3269
3270 END g_execute_api_callouts;
3271
3272
3273 END HZ_UTILITY_V2PUB;
[Home] [Help]