1 PACKAGE HZ_UTILITY_V2PUB AS
2 /*$Header: ARH2UTSS.pls 120.26 2005/12/27 19:10:36 acng ship $ */
3
4 --------------------------------------
5 -- public procedures and functions
6 --------------------------------------
7
8 /**
9 * PROCEDURE validate_mandatory
10 *
11 * DESCRIPTION
12 * Validate mandatory field.
13 *
14 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
15 *
16 * ARGUMENTS
17 * IN:
18 * p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
19 * p_column Column name you want to validate.
20 * p_column_value Column value
21 * p_restriced If set to 'Y', p_column_value should be passed
22 * in with some value in both create and update
23 * mode. If set to 'N', p_column_value can be
24 * NULL in update mode. Default is 'N'.
25 * IN/OUT:
26 * x_return_status Return status.
27 *
28 * NOTES
29 * The procedure is overloaded for different column type, i.e. VARCHAR2,
30 * NUMBER, and DATE.
31 *
32 * MODIFICATION HISTORY
33 *
34 * 07-23-2001 Jianying Huang o Created.
35 *
36 */
37
38 G_CREATED_BY_MODULE VARCHAR2(30);
39 G_CALLING_API VARCHAR2(10);
40 G_EXECUTE_API_CALLOUTS CONSTANT VARCHAR2(255) := FND_PROFILE.VALUE('HZ_EXECUTE_API_CALLOUTS');
41 -- Bug 4693719
42 G_UPDATE_ACS VARCHAR2(1);
43
44 PROCEDURE validate_mandatory (
45 p_create_update_flag IN VARCHAR2,
46 p_column IN VARCHAR2,
47 p_column_value IN VARCHAR2,
48 p_restricted IN VARCHAR2 DEFAULT 'N',
49 x_return_status IN OUT NOCOPY VARCHAR2
50 );
51
52 PROCEDURE validate_mandatory (
53 p_create_update_flag IN VARCHAR2,
54 p_column IN VARCHAR2,
55 p_column_value IN NUMBER,
56 p_restricted IN VARCHAR2 DEFAULT 'N',
57 x_return_status IN OUT NOCOPY VARCHAR2
58 );
59
60 PROCEDURE validate_mandatory (
61 p_create_update_flag IN VARCHAR2,
62 p_column IN VARCHAR2,
63 p_column_value IN DATE,
64 p_restricted IN VARCHAR2 DEFAULT 'N',
65 x_return_status IN OUT NOCOPY VARCHAR2
66 );
67
68 /**
69 * PROCEDURE validate_nonupdateable
70 *
71 * DESCRIPTION
72 * Validate nonupdateable field.
73 *
74 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
75 *
76 * ARGUMENTS
77 * IN:
78 * p_column Column name you want to validate.
79 * p_column_value Column value
80 * p_old_column_value Current database column value
81 * p_restriced If set to 'Y', column can not be updated
82 * even the database value is null. This is
83 * default value and as long as p_column_value
84 * is not equal to p_old_column_error, return
85 * status will be set to error.
86 * If set to 'N', if database value is null,
87 * we can update it to a value. If database value
88 * is not null and if p_column_value is not equal
89 * to p_old_column_value, return status will be
90 * set to error.
91 * IN/OUT:
92 * x_return_status Return status.
93 *
94 * NOTES
95 * The procedure is overloaded for different column type, i.e. VARCHAR2,
96 * NUMBER, and DATE. The procedure should be called in update mode.
97 *
98 * For example:
99 * IF p_create_update_flag = 'U' THEN
100 * validate_nonupdateable( ... );
101 * END IF;
102 *
103 * MODIFICATION HISTORY
104 *
105 * 07-23-2001 Jianying Huang o Created.
106 *
107 */
108
109 PROCEDURE validate_nonupdateable (
110 p_column IN VARCHAR2,
111 p_column_value IN VARCHAR2,
112 p_old_column_value IN VARCHAR2,
113 p_restricted IN VARCHAR2 DEFAULT 'Y',
114 x_return_status IN OUT NOCOPY VARCHAR2
115 );
116
117 PROCEDURE validate_nonupdateable (
118 p_column IN VARCHAR2,
119 p_column_value IN NUMBER,
120 p_old_column_value IN NUMBER,
121 p_restricted IN VARCHAR2 DEFAULT 'Y',
122 x_return_status IN OUT NOCOPY VARCHAR2
123 );
124
125 PROCEDURE validate_nonupdateable (
126 p_column IN VARCHAR2,
127 p_column_value IN DATE,
128 p_old_column_value IN DATE,
129 p_restricted IN VARCHAR2 DEFAULT 'Y',
130 x_return_status IN OUT NOCOPY VARCHAR2
131 );
132
133 /**
134 * PROCEDURE validate_start_end_date
135 *
136 * DESCRIPTION
137 * Validate start data can not be earlier than end date.
138 *
139 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
140 *
141 * ARGUMENTS
142 * IN:
143 * p_create_update_flag 'C' ( create mode ), 'U' ( update mode )
144 * p_start_date_column_name Column name of start date
145 * p_start_date New start date
146 * p_old_start_date Database start date in update mode
147 * p_end_date_column_name Column name of end date
148 * p_end_date New end date
149 * p_old_end_date Database end date in update mode
150 * IN/OUT:
151 * x_return_status Return status.
152 *
153 * NOTES
154 *
155 * MODIFICATION HISTORY
156 *
157 * 07-23-2001 Jianying Huang o Created.
158 *
159 */
160
161 PROCEDURE validate_start_end_date (
162 p_create_update_flag IN VARCHAR2,
163 p_start_date_column_name IN VARCHAR2,
164 p_start_date IN DATE,
165 p_old_start_date IN DATE,
166 p_end_date_column_name IN VARCHAR2,
167 p_end_date IN DATE,
168 p_old_end_date IN DATE,
169 x_return_status IN OUT NOCOPY VARCHAR2
170 );
171
172 /**
173 * PROCEDURE validate_cannot_update_to_null
174 *
175 * DESCRIPTION
176 * Validate column cannot be updated to null.
177 *
178 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
179 *
180 * ARGUMENTS
181 * IN:
182 * p_column Column name you want to validate.
183 * p_column_value Column value
184 * IN/OUT:
185 * x_return_status Return status.
186 *
187 * NOTES
188 * The procedure is overloaded for different column type, i.e. VARCHAR2,
189 * NUMBER, and DATE. The procedure should be called in update mode.
190 *
191 * For example:
192 * IF p_create_update_flag = 'U' THEN
193 * validate_cannot_update_to_null( ... );
194 * END IF;
195 *
196 * MODIFICATION HISTORY
197 *
198 * 07-23-2001 Jianying Huang o Created.
199 *
200 */
201
202 PROCEDURE validate_cannot_update_to_null (
203 p_column IN VARCHAR2,
204 p_column_value IN VARCHAR2,
205 x_return_status IN OUT NOCOPY VARCHAR2
206 );
207
208 PROCEDURE validate_cannot_update_to_null (
209 p_column IN VARCHAR2,
210 p_column_value IN NUMBER,
211 x_return_status IN OUT NOCOPY VARCHAR2
212 );
213
214 PROCEDURE validate_cannot_update_to_null (
215 p_column IN VARCHAR2,
216 p_column_value IN DATE,
217 x_return_status IN OUT NOCOPY VARCHAR2
218 );
219
220 /**
221 * PROCEDURE validate_cannot_update_to_null
222 *
223 * DESCRIPTION
224 * Validate column cannot be updated to null.
225 *
226 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
227 *
228 * ARGUMENTS
229 * IN:
230 * p_column Column name you want to validate.
231 * p_lookup_table Table/view name you want to validate against to.
232 * For now, we are supporting
233 * AR_LOOKUPS
234 * SO_LOOKUPS
235 * OE_SHIP_METHODS_V
236 * FND_LOOKUP_VALUES
237 * Default value is AR_LOOKUPS
238 * p_lookup_type Fnd lookup type
239 * p_column_value Column value
240 * IN/OUT:
241 * x_return_status Return status.
242 *
243 * NOTES
244 * The procedure is using cache strategy for performance improvement.
245 *
246 * MODIFICATION HISTORY
247 *
248 * 07-23-2001 Jianying Huang o Created.
249 *
250 */
251
252 PROCEDURE validate_lookup (
253 p_column IN VARCHAR2,
254 p_lookup_table IN VARCHAR2 DEFAULT 'AR_LOOKUPS',
255 p_lookup_type IN VARCHAR2,
256 p_column_value IN VARCHAR2,
257 x_return_status IN OUT NOCOPY VARCHAR2
258 );
259
260 /**
261 * PROCEDURE enable_debug
262 *
263 * DESCRIPTION
264 * Enable file or dbms debug based on profile options.
265 * HZ_API_FILE_DEBUG_ON : Turn on/off file debug, i.e. debug message
266 * will be written to a user specified file.
267 * The file name and file path is stored in
268 * profiles HZ_API_DEBUG_FILE_PATH and
269 * HZ_API_DEBUG_FILE_NAME. File path must be
270 * database writable.
271 * HZ_API_DBMS_DEBUG_ON : Turn on/off dbms debug.
272 *
273 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
274 *
275 * ARGUMENTS
276 *
277 * NOTES
278 *
279 * MODIFICATION HISTORY
280 *
281 * 07-23-2001 Jianying Huang o Created.
282 *
283 */
284
285 PROCEDURE enable_debug;
286
287 /**
288 * PROCEDURE disable_debug
289 *
290 * DESCRIPTION
291 * Disable file or dbms debug.
292 *
293 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
294 *
295 * ARGUMENTS
296 *
297 * NOTES
298 *
299 * MODIFICATION HISTORY
300 *
301 * 07-23-2001 Jianying Huang o Created.
302 *
303 */
304
305 PROCEDURE disable_debug;
306
307 /**
308 * PROCEDURE debug
309 *
310 * DESCRIPTION
311 * Put debug message.
312 *
313 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
314 *
315 * ARGUMENTS
316 * IN:
317 * p_message Message you want to put in log.
318 * p_prefix Prefix of the message. Default value is
319 * DEBUG.
320 * p_msg_level Message Level.Default value is 1 and the value should be between
321 * 1 and 6 corresponding to FND_LOG's
322 * LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
323 * LEVEL_ERROR CONSTANT NUMBER := 5;
324 * LEVEL_EXCEPTION CONSTANT NUMBER := 4;
325 * LEVEL_EVENT CONSTANT NUMBER := 3;
326 * LEVEL_PROCEDURE CONSTANT NUMBER := 2;
327 * LEVEL_STATEMENT CONSTANT NUMBER := 1;
328 * p_module_prefix Module prefix to store package name,form name.Default value is
329 * HZ_Package.
330 * p_module Module to store Procedure Name. Default value is HZ_Module.
331 *
332 * NOTES
333 *
334 * MODIFICATION HISTORY
335 *
336 * 07-23-2001 Jianying Huang o Created.
337 * 01-Dec-2003 Ramesh Ch Added p_msg_level,p_module_prefix,p_module parameters
338 * with default values as part of Common Logging Infrastrycture Uptake.
339 */
340
341 PROCEDURE debug (
342 p_message IN VARCHAR2,
343 p_prefix IN VARCHAR2 DEFAULT 'DEBUG',
344 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
345 p_module_prefix IN VARCHAR2 DEFAULT 'HZ_Package',
346 p_module IN VARCHAR2 DEFAULT 'HZ_Module'
347 );
348
349 /**
350 * PROCEDURE debug_return_messages
351 *
352 * DESCRIPTION
353 * Put debug messages based on message count in message stack.
354 *
355 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
356 *
357 * ARGUMENTS
358 * IN:
359 * p_msg_count Message count in message stack.
360 * p_msg_data Message data if message count is 1.
361 * p_msg_type Message type used as prefix of the message.
362 *
363 * p_msg_level Message Level.Default value is 1 and the value should be between
364 * 1 and 6 corresponding to FND_LOG's
365 * LEVEL_UNEXPECTED CONSTANT NUMBER := 6;
366 * LEVEL_ERROR CONSTANT NUMBER := 5;
367 * LEVEL_EXCEPTION CONSTANT NUMBER := 4;
368 * LEVEL_EVENT CONSTANT NUMBER := 3;
369 * LEVEL_PROCEDURE CONSTANT NUMBER := 2;
370 * LEVEL_STATEMENT CONSTANT NUMBER := 1;
371 * p_module_prefix Module prefix to store package name,form name.Default value is
372 * HZ_Package.
373 * p_module Module to store Procedure Name. Default value is HZ_Module.
374 *
375 * NOTES
376 *
377 * MODIFICATION HISTORY
378 *
379 * 07-23-2001 Jianying Huang o Created.
380 *
381 * 01-Dec-2003 Ramesh Ch Added p_msg_level,p_module_prefix,p_module parameters
382 * with default values as part of Common Logging Infrastrycture Uptake.
383 */
384
385 PROCEDURE debug_return_messages (
386 p_msg_count IN NUMBER,
387 p_msg_data IN VARCHAR2,
388 p_msg_type IN VARCHAR2 DEFAULT 'ERROR',
389 p_msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT,
390 p_module_prefix IN VARCHAR2 DEFAULT 'HZ_Package',
391 p_module IN VARCHAR2 DEFAULT 'HZ_Module'
392 );
393
394
395 /**
396 * FUNCTION get_session_process_id
397 *
398 * DESCRIPTION
399 * Return OS process id of current session.
400 *
401 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
402 *
403 * ARGUMENTS
404 * IN:
405 *
406 * NOTES
407 *
408 * MODIFICATION HISTORY
409 *
410 * 07-23-2001 Jianying Huang o Created.
411 *
412 */
413
414 FUNCTION get_session_process_id RETURN VARCHAR2;
415 PRAGMA RESTRICT_REFERENCES ( get_session_process_id, WNDS, WNPS, RNPS );
416
417 /**
418 * FUNCTION
419 * created_by
420 * creation_date
421 * last_updated_by
422 * last_update_date
423 * last_update_login
424 * request_id
425 * program_id
426 * program_application_id
427 * program_update_date
428 * user_id
429 *
430 * DESCRIPTION
431 * Return standard who value.
432 *
436 * IN:
433 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
434 *
435 * ARGUMENTS
437 *
438 * NOTES
439 *
440 * MODIFICATION HISTORY
441 *
442 * 07-23-2001 Jianying Huang o Created.
443 *
444 */
445
446 FUNCTION created_by RETURN NUMBER;
447
448 FUNCTION creation_date RETURN DATE;
449
450 FUNCTION last_updated_by RETURN NUMBER;
451
452 FUNCTION last_update_date RETURN DATE;
453
454 FUNCTION last_update_login RETURN NUMBER;
455
456 FUNCTION request_id RETURN NUMBER;
457
458 FUNCTION program_id RETURN NUMBER;
459
460 FUNCTION program_application_id RETURN NUMBER;
461
462 FUNCTION application_id RETURN NUMBER;
463
464 FUNCTION program_update_date RETURN DATE;
465
466 FUNCTION user_id RETURN NUMBER;
467
468 /**
469 * FUNCTION incl_unrelated_entities
470 *
471 * DESCRIPTION
472 * Function to check the value of incl_unrelated_entities flag
473 * for a relationship type. the procedure has been put here to
474 * cache the values so that program does not hit database if the
475 * same relationship type has already been read.
476 *
477 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
478 *
479 * ARGUMENTS
480 * IN:
481 * p_relationship_type Relationship type.
482 *
483 * NOTES
484 *
485 * MODIFICATION HISTORY
486 *
487 * 02-02-2002 Indrajit Sen o Created.
488 *
489 */
490
491 FUNCTION incl_unrelated_entities (
492 p_relationship_type IN VARCHAR2
493 ) RETURN VARCHAR2;
494
495 /**
496 * FUNCTION Get_SchemaName
497 *
498 * DESCRIPTION
499 * Return Schema's Name By Given The Application's Short Name
500 * The function will raise fnd_api.g_exc_unexpected_error if
501 * the short name can not be found in installation and put a
502 * message '<p_app_short_name> is not a valid oracle schema name.'
503 * in the message stack.
504 *
505 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
506 *
507 * ARGUMENTS
508 * IN:
509 * p_app_short_name
510 * NOTES
511 *
512 * MODIFICATION HISTORY
513 *
514 * 03-01-2002 Jianying Huang o Created.
515 *
516 */
517
518 FUNCTION Get_SchemaName (
519 p_app_short_name IN VARCHAR2
520 ) RETURN VARCHAR2;
521
522 /**
523 * FUNCTION Get_AppsSchemaName
524 *
525 * DESCRIPTION
526 * Return APPS Schema's Name
527 * The function will raise fnd_api.g_exc_unexpected_error if
528 * the 'FND' as a short name can not be found in installation.
529 * and put a message 'FND is not a valid oracle schema name.'
530 * in the message stack.
531 *
532 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
533 *
534 * ARGUMENTS
535 * IN:
536 *
537 * NOTES
538 *
539 * MODIFICATION HISTORY
540 *
541 * 03-01-2002 Jianying Huang o Created.
542 *
543 */
544
545 FUNCTION Get_AppsSchemaName RETURN VARCHAR2;
546
547 /**
548 * FUNCTION Get_LookupMeaning
549 *
550 * DESCRIPTION
551 * Get lookup meaning. Return NULL if lookup code does
552 * not exist.
553 *
554 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
555 *
556 * ARGUMENTS
557 * IN:
558 * p_lookup_table
559 * p_lookup_type
560 * p_lookup_code
561 *
562 * NOTES
563 *
564 * MODIFICATION HISTORY
565 *
566 * 03-01-2002 Jianying Huang o Created.
567 *
568 */
569
570 FUNCTION Get_LookupMeaning (
571 p_lookup_table IN VARCHAR2 DEFAULT 'AR_LOOKUPS',
572 p_lookup_type IN VARCHAR2,
573 p_lookup_code IN VARCHAR2
574 ) RETURN VARCHAR2;
575
576 /**
577 * FUNCTION Check_ObsoleteColumn
578 *
579 * DESCRIPTION
580 * Internal use only!!
581 * Set x_return_status to FND_API.G_RET_STS_ERROR when
582 * user is trying to pass value into an obsolete column
583 * in development site.
584 *
585 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
586 *
587 * ARGUMENTS
588 * IN:
589 * p_api_version 'V1' is for V1 API. 'V2' is for V2 API.
590 * p_create_update_flag 'C' is for create. 'U' is for update.
591 * p_column Column name.
592 * p_column_value Value of the column.
593 * p_default_value Default value of the column. Please note,
594 * for V1 API, most columns are defaulted to
595 * FND_API.G_MISS_XXX and for V2 API, we do
596 * not have default value for most columns.
597 * p_old_column_value Database value of the column. Only used
598 * in update mode.
599 * OUT:
600 * x_return_status Return FND_API.G_RET_STS_ERROR if user
604 * NOTES
601 * is trying to pass value into an obsolete
602 * column in development site.
603 *
605 * I am not making the function as public for now because it is used only by
606 * obsoleting content_source_type. It is worth to call this function only when
607 * you obsolete one column. If you are obsoleting more than one columns, it
608 * is better to cancat them and then decide if need to raise exception. For
609 * this limitation, it is not worth to provide the function for NUMBER and
610 * DATE type of column.
611 *
612 * MODIFICATION HISTORY
613 *
614 * 03-01-2002 Jianying Huang o Created.
615 */
616
617 PROCEDURE Check_ObsoleteColumn (
618 p_api_version IN VARCHAR2,
619 p_create_update_flag IN VARCHAR2,
620 p_column IN VARCHAR2,
621 p_column_value IN VARCHAR2,
622 p_default_value IN VARCHAR2 := NULL,
623 p_old_column_value IN VARCHAR2 := NULL,
624 x_return_status IN OUT NOCOPY VARCHAR2
625 );
626
627
628 /**
629 * FUNCTION get_site_use_purpose
630 *
631 * DESCRIPTION
632 * used by common party UI .
633 * will return the first three site use type
634
635 * ARGUMENTS
636 * IN:
637 * p_party_site_id party site id used to retrieve the site use purpose.
638 *
639 * RETURNS : VARCHAR2
640 *
641 **/
642 function get_site_use_purpose (
643 p_party_site_id IN NUMBER)
644 RETURN VARCHAR2;
645
646 /**
647 * FUNCTION get_all_purposes
648 *
649 * DESCRIPTION
650 * used by common party UI .
651 * will return all site use types
652
653 * ARGUMENTS
654 * IN:
655 * p_party_site_id party site id used to retrieve the site use purpose.
656 *
657 * RETURNS : VARCHAR2
658 *
659 **/
660 function get_all_purposes (
661 p_party_site_id IN NUMBER)
662 RETURN VARCHAR2;
663
664 /**
665 * FUNCTION get_acct_site_purposes
666 *
667 * DESCRIPTION
668 * used by common party UI .
669 * will return acct site uses
670
671 * ARGUMENTS
672 * IN:
673 * p_acct_site_id acct site id used to retrieve the acct site use purpose.
674 *
675 * RETURNS : VARCHAR2
676 *
677 **/
678 function get_acct_site_purposes (
679 p_acct_site_id IN NUMBER)
680 RETURN VARCHAR2;
681
682 /**
683 * FUNCTION validate_flex_address
684 *
685 * DESCRIPTION
686 * used by common party UI .
687 * will validate the flex address
688 * and return 'Y'/'N'
689 * ARGUMENTS
690 * IN:
691 * p_context_value : context_value
692 * p_address1 : address1
693 * p_address2 : address2
694 * p_address3 : address3
695 * p_address4 : address4
696 * p_address_lines_phonetic: address_lines_phonetic
697 * p_city : city
698 * p_county : county
699 * p_postal_code : postal_code
700 * p_province : province
701 * p_state : state
702 * p_attribute1 : attribute1
703 * p_attribute2 : attribute2
704 * p_attribute3 : attribute3
705 * p_attribute4 : attribute4
706 * p_attribute5 : attribute5
707 * p_attribute6 : attribute6
708 * p_attribute7 : attribute7
709 * p_attribute8 : attribute8
710 * p_attribute9 : attribute9
711 * p_attribute10: attribute10
712 * p_attribute11: attribute11
713 * p_attribute12: attribute12
714 * p_attribute13: attribute13
715 * p_attribute14: attribute14
716 * p_attribute15: attribute15
717 * p_attribute16: attribute16
718 * p_attribute17: attribute17
719 * p_attribute18: attribute18
720 * p_attribute19: attribute19
721 * p_attribute20: attribute20
722 *
723 * RETURNS : VARCHAR2
724 *
725 **/
726 FUNCTION validate_flex_address (
727 p_context_value IN VARCHAR2,
728 p_address1 IN VARCHAR2,
729 p_address2 IN VARCHAR2,
730 p_address3 IN VARCHAR2,
731 p_address4 IN VARCHAR2,
732 p_address_lines_phonetic IN VARCHAR2,
733 p_city IN VARCHAR2,
734 p_county IN VARCHAR2,
735 p_postal_code IN VARCHAR2,
736 p_province IN VARCHAR2,
737 p_state IN VARCHAR2,
738 p_attribute1 IN VARCHAR2,
739 p_attribute2 IN VARCHAR2,
740 p_attribute3 IN VARCHAR2,
741 p_attribute4 IN VARCHAR2,
742 p_attribute5 IN VARCHAR2,
743 p_attribute6 IN VARCHAR2,
744 p_attribute7 IN VARCHAR2,
745 p_attribute8 IN VARCHAR2,
746 p_attribute9 IN VARCHAR2,
747 p_attribute10 IN VARCHAR2,
748 p_attribute11 IN VARCHAR2,
749 p_attribute12 IN VARCHAR2,
750 p_attribute13 IN VARCHAR2,
751 p_attribute14 IN VARCHAR2,
752 p_attribute15 IN VARCHAR2,
753 p_attribute16 IN VARCHAR2,
754 p_attribute17 IN VARCHAR2,
755 p_attribute18 IN VARCHAR2,
756 p_attribute19 IN VARCHAR2,
757 p_attribute20 IN VARCHAR2
758 ) RETURN VARCHAR2;
759
760 /**
761 * FUNCTION validate_desc_flex
762 *
763 * DESCRIPTION
764 * used by common party UI .
765 * will validate the descriptive flex
766 * and return 'Y'/'N'
767 * ARGUMENTS
768 * IN:
769 * p_appl_short_name:appl_short_name
770 * p_desc_flex_name :desc_flex_name
771 * p_context_value : context_value
772 * p_attribute1 : attribute1
773 * p_attribute2 : attribute2
774 * p_attribute3 : attribute3
775 * p_attribute4 : attribute4
776 * p_attribute5 : attribute5
777 * p_attribute6 : attribute6
778 * p_attribute7 : attribute7
779 * p_attribute8 : attribute8
780 * p_attribute9 : attribute9
781 * p_attribute10: attribute10
782 * p_attribute11: attribute11
783 * p_attribute12: attribute12
784 * p_attribute13: attribute13
788 * p_attribute17: attribute17
785 * p_attribute14: attribute14
786 * p_attribute15: attribute15
787 * p_attribute16: attribute16
789 * p_attribute18: attribute18
790 * p_attribute19: attribute19
791 * p_attribute20: attribute20
792 * p_attribute21: attribute21
793 * p_attribute22: attribute22
794 * p_attribute23: attribute23
795 * p_attribute24: attribute24
796 * RETURNS : VARCHAR2
797 *
798 **/
799 FUNCTION validate_desc_flex (
800 p_appl_short_name IN VARCHAR2,
801 p_desc_flex_name IN VARCHAR2,
802 p_context_value IN VARCHAR2,
803 p_attribute1 IN VARCHAR2,
804 p_attribute2 IN VARCHAR2,
805 p_attribute3 IN VARCHAR2,
806 p_attribute4 IN VARCHAR2,
807 p_attribute5 IN VARCHAR2,
808 p_attribute6 IN VARCHAR2,
809 p_attribute7 IN VARCHAR2,
810 p_attribute8 IN VARCHAR2,
811 p_attribute9 IN VARCHAR2,
812 p_attribute10 IN VARCHAR2,
813 p_attribute11 IN VARCHAR2,
814 p_attribute12 IN VARCHAR2,
815 p_attribute13 IN VARCHAR2,
816 p_attribute14 IN VARCHAR2,
817 p_attribute15 IN VARCHAR2,
818 p_attribute16 IN VARCHAR2,
819 p_attribute17 IN VARCHAR2,
820 p_attribute18 IN VARCHAR2,
821 p_attribute19 IN VARCHAR2,
822 p_attribute20 IN VARCHAR2,
823 p_attribute21 IN VARCHAR2 DEFAULT NULL,
824 p_attribute22 IN VARCHAR2 DEFAULT NULL,
825 p_attribute23 IN VARCHAR2 DEFAULT NULL,
826 p_attribute24 IN VARCHAR2 DEFAULT NULL
827 ) RETURN VARCHAR2;
828
829
830 /**
831 * FUNCTION get_primary_email
832 *
833 * DESCRIPTION
834 * used by common party UI .
835 * added by albert (tsli)
836 * will return the primary email
837 * ARGUMENTS
838 * IN:
839 * p_party_id party id used to retrieve the primary email
840 *
841 * RETURNS : VARCHAR2
842 *
843 **/
844
845 function get_primary_email (
846 p_party_id IN NUMBER)
847 RETURN VARCHAR2;
848
849 /**
850 * FUNCTION get_primary_phone
851 *
852 * DESCRIPTION
853 * used by common party UI .
854 * added by albert (tsli)
855 * will return the primary phone
856 * ARGUMENTS
857 * IN:
858 * p_party_id party id used to retrieve the primary phone
859 *
860 * RETURNS : VARCHAR2
861 *
862 **/
863
864 function get_primary_phone (
865 p_party_id IN NUMBER,
866 p_display_purpose IN VARCHAR2 := fnd_api.g_true)
867 RETURN VARCHAR2;
868
869
870 /**
871 * FUNCTION get_org_contact_role
872 *
873 * DESCRIPTION
874 * used by common party UI .
875 * added by albert (tsli)
876 * will return the first three org contact roles
877
878 * ARGUMENTS
879 * IN:
880 * p_org_contact_id org contact id used to retrieve the org contact roles.
881 *
882 * RETURNS : VARCHAR2
883 *
884 **/
885
886 function get_org_contact_role (
887 p_org_contact_id IN NUMBER)
888 RETURN VARCHAR2;
889
890 PROCEDURE find_index_name(
891 p_index_name OUT NOCOPY VARCHAR2);
892
893 /**
894 * FUNCTION GET_YAHOO_MAP_URL
895 *
896 * DESCRIPTION
897 * function that would return a html link tag which
898 * will contain the address formatted for Yahoo Maps.
899 * ARGUMENTS
900 * IN:
901 * address1 IN VARCHAR2,
902 * address2 IN VARCHAR2,
903 * address3 IN VARCHAR2,
904 * address4 IN VARCHAR2,
905 * city IN VARCHAR2,
906 * country IN VARCHAR2,
907 * state IN VARCHAR2,
908 * postal_code IN VARCHAR2
909 *
910 * RETURNS : VARCHAR2
911 *
912 **/
913 FUNCTION GET_YAHOO_MAP_URL(address1 IN VARCHAR2,
914 address2 IN VARCHAR2,
915 address3 IN VARCHAR2,
916 address4 IN VARCHAR2,
917 city IN VARCHAR2,
918 country IN VARCHAR2,
919 state IN VARCHAR2,
920 postal_code IN VARCHAR2)
921 RETURN VARCHAR2;
922
923 /**
924 * FUNCTION IS_PARTY_ID_IN_REQUEST_LOG
925 *
926 * DESCRIPTION
927 * function that would return a 'Y' if this party_id exist in hz_dnb_request_log
928 * return 'N' if not.
929 * ARGUMENTS
930 * party_id IN NUMBER
931 *
932 * RETURNS : VARCHAR2
933 */
934 FUNCTION IS_PARTY_ID_IN_REQUEST_LOG(
935 p_party_id IN NUMBER)
936
937 RETURN VARCHAR2;
938
939 /**
940 * FUNCTION get_message
941 *
942 * DESCRIPTION
943 * returns the translated message
944 * ARGUMENTS
945 * app_short_name -- applicatio short name
946 * message_name
947 * token1_name, token1_value
948 * token2_name, token2_value
949 * token3_name, token3_value
950 * token4_name, token4_value
951 * token5_name, token5_value
952 *
953 * RETURNS : VARCHAR2: token sustituted, translated message
954 */
955 FUNCTION get_message(
956 app_short_name IN VARCHAR2,
957 message_name IN varchar2,
958 token1_name IN VARCHAR2,
959 token1_value IN VARCHAR2,
960 token2_name IN VARCHAR2,
961 token2_value IN VARCHAR2,
962 token3_name IN VARCHAR2,
963 token3_value IN VARCHAR2,
964 token4_name IN VARCHAR2,
965 token4_value IN VARCHAR2,
966 token5_name IN VARCHAR2,
967 token5_value IN VARCHAR2)
968 RETURN VARCHAR2;
969
970
971 /**
972 * FUNCTION is_restriction_exist
973 *
974 * DESCRIPTION
975 * used by common party UI .
976 * will return a flag to indicate if contact preference exist
977 * ARGUMENTS
978 * IN:
979 * p_contact_level_table contact level table
980 * p_contact_level_table_id contact level table id
981 * p_preference_code preference code
982 *
983 * RETURNS : VARCHAR2
984 *
985 **/
986
987 FUNCTION is_restriction_exist (
988 p_contact_level_table IN VARCHAR2,
989 p_contact_level_table_id IN NUMBER,
990 p_preference_code IN VARCHAR2
991 ) RETURN VARCHAR2;
992
993 /**
994 * FUNCTION is_purchased_content_source
995 *
996 * DESCRIPTION
997 * This function will return 'Y' if the source system is a purchased one.
998 * (i.e HZ_ORIG_SYSTEMS_B.orig_system_type = 'PURCHASED')
999 *
1000 * ARGUMENTS
1001 * IN:
1002 * p_content_source
1003 *
1004 * RETURNS : VARCHAR2
1005 *
1006 * NOTES
1007 *
1008 * MODIFICATION HISTORY
1009 *
1010 * 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
1011 *
1012 **/
1013 FUNCTION is_purchased_content_source(
1017 /**
1014 p_content_source IN VARCHAR2
1015 ) RETURN VARCHAR2;
1016
1018 * FUNCTION get_lookupMeaning_lang
1019 *
1020 * DESCRIPTION
1021 * This function will return the meaning in FND_LOOKUP_VALUES for the given combination
1022 * of lookup_type, lookup_code and language.
1023 *
1024 * ARGUMENTS
1025 * IN:
1026 * p_lookup_type
1027 * p_lookup_code
1028 * p_language
1029 *
1030 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Meaning)
1031 *
1032 * NOTES
1033 *
1034 * MODIFICATION HISTORY
1035 *
1036 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
1037 *
1038 **/
1039 FUNCTION get_lookupMeaning_lang (
1040 p_lookup_type IN VARCHAR2,
1041 p_lookup_code IN VARCHAR2,
1042 p_language IN VARCHAR2
1043 ) RETURN VARCHAR2;
1044
1045 /**
1046 * FUNCTION get_lookupDesc_lang
1047 *
1048 * DESCRIPTION
1049 * This function will return the description in FND_LOOKUP_VALUES for the given combination
1050 * of lookup_type, lookup_code and language.
1051 *
1052 * ARGUMENTS
1053 * IN:
1054 * p_lookup_type
1055 * p_lookup_code
1056 * p_language
1057 *
1058 * RETURNS : VARCHAR2 (FND_LOOKUP_VALUES.Description)
1059 *
1060 * NOTES
1061 *
1062 * MODIFICATION HISTORY
1063 *
1064 * 09-Jan-2005 Rajib Ranjan Borah o SSM SST Integration and Extension. Created.
1065 *
1066 **/
1067 FUNCTION get_lookupDesc_lang (
1068 p_lookup_type IN VARCHAR2,
1069 p_lookup_code IN VARCHAR2,
1070 p_language IN VARCHAR2
1071 ) RETURN VARCHAR2;
1072
1073
1074 /**
1075 * FUNCTION check_prim_bill_to_site
1076 *
1077 * DESCRIPTION
1078 * used by common party UI .
1079 * will return Y if the party site is the primary Bill_To site.
1080 * will return N in other cases
1081
1082 * ARGUMENTS
1083 * IN:
1084 * p_party_site_id party site id used to retrieve the site use purpose.
1085 *
1086 * RETURNS : VARCHAR2
1087 *
1088 **/
1089 function check_prim_bill_to_site (
1090 p_party_site_id IN NUMBER)
1091 RETURN VARCHAR2;
1092
1093
1094 /**
1095 * FUNCTION check_prim_ship_to_site
1096 *
1097 * DESCRIPTION
1098 * used by common party UI .
1099 * will return Y if the party site is the primary Ship_To site.
1100 * will return N in other cases
1101
1102 * ARGUMENTS
1106 * RETURNS : VARCHAR2
1103 * IN:
1104 * p_party_site_id party site id used to retrieve the site use purpose.
1105 *
1107 *
1108 **/
1109 function check_prim_ship_to_site (
1110 p_party_site_id IN NUMBER)
1111 RETURN VARCHAR2;
1112
1113
1114 /**
1115 * PROCEDURE validate_created_by_module
1116 *
1117 * DESCRIPTION
1118 * validate created by module
1119 * ARGUMENTS
1120 * IN:
1121 * p_create_update_flag create update flag
1122 * p_created_by_module created by module
1123 * p_old_created_by_module old value of created by module
1124 * x_return_status return status
1125 */
1126
1127 PROCEDURE validate_created_by_module (
1128 p_create_update_flag IN VARCHAR2,
1129 p_created_by_module IN VARCHAR2,
1130 p_old_created_by_module IN VARCHAR2,
1131 x_return_status IN OUT NOCOPY VARCHAR2
1132 );
1133
1134
1135 /**
1136 * PROCEDURE validate_application_id
1137 *
1138 * DESCRIPTION
1139 * validate application id
1140 * ARGUMENTS
1141 * IN:
1142 * p_create_update_flag create update flag
1143 * p_application_id application id
1144 * p_old_application_id old value of application id
1145 * x_return_status return status
1146 */
1147
1148 PROCEDURE validate_application_id (
1149 p_create_update_flag IN VARCHAR2,
1150 p_application_id IN NUMBER,
1151 p_old_application_id IN NUMBER,
1152 x_return_status IN OUT NOCOPY VARCHAR2
1153 );
1154
1155
1156 /**
1157 * FUNCTION is_role_in_relationship_group
1158 *
1159 * DESCRIPTION
1160 * return if a role exists in a relationship group
1161 * ARGUMENTS
1162 * IN:
1163 * p_relationship_type_id relationship type id
1164 * p_relationship_group_code relationship group code
1165 */
1166
1167 FUNCTION is_role_in_relationship_group (
1168 p_relationship_type_id IN NUMBER,
1169 p_relationship_group_code IN VARCHAR2
1170 ) RETURN VARCHAR2;
1171
1172 END HZ_UTILITY_V2PUB;
[Home] [Help]