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