[Home] [Help]
1 package body PV_SQL_UTILITY as
2 /* $Header: pvsqlutb.pls 120.3 2005/12/19 16:19:51 pklin ship $*/
3
4 --=============================================================================+
5 --| Public Procedure |
6 --| pv_lookup |
7 --| |
8 --| Parameters |
9 --| IN |
10 --| OUT |
11 --| |
12 --| |
13 --| NOTES |
14 --| |
15 --| HISTORY |
16 --| |
17 --==============================================================================
18 FUNCTION pv_lookup (
19 p_lookup_code IN VARCHAR2,
20 p_lookup_type IN VARCHAR2
21 )
22 RETURN VARCHAR2
23 IS
24 l_meaning VARCHAR2(80);
25
26 BEGIN
27 FOR x IN (SELECT meaning
28 FROM pv_lookups
29 WHERE lookup_code = p_lookup_code AND
30 lookup_type = p_lookup_type)
31 LOOP
32 l_meaning := x.meaning;
33 END LOOP;
34
35 RETURN l_meaning;
36
37 END pv_lookup;
38
39
40 --=============================================================================+
41 --| Public Procedure |
42 --| as_lookup |
43 --| |
44 --| Parameters |
45 --| IN |
46 --| OUT |
47 --| |
48 --| |
49 --| NOTES |
50 --| |
51 --| HISTORY |
52 --| |
53 --==============================================================================
54 FUNCTION as_lookup (
55 p_lookup_code IN VARCHAR2,
56 p_lookup_type IN VARCHAR2
57 )
58 RETURN VARCHAR2
59 IS
60 l_meaning VARCHAR2(80);
61
62 BEGIN
63 FOR x IN (SELECT meaning
64 FROM as_lookups
65 WHERE lookup_code = p_lookup_code AND
66 lookup_type = p_lookup_type)
67 LOOP
68 l_meaning := x.meaning;
69 END LOOP;
70
71 RETURN l_meaning;
72
73 END as_lookup;
74
75
76 --=============================================================================+
77 --| Public Procedure |
78 --| ar_lookup |
79 --| |
80 --| Parameters |
81 --| IN |
82 --| OUT |
83 --| |
84 --| |
85 --| NOTES |
86 --| |
87 --| HISTORY |
88 --| |
89 --==============================================================================
90 FUNCTION ar_lookup (
91 p_lookup_code IN VARCHAR2,
92 p_lookup_type IN VARCHAR2
93 )
94 RETURN VARCHAR2
95 IS
96 l_meaning VARCHAR2(80);
97
98 BEGIN
99 FOR x IN (SELECT meaning
100 FROM ar_lookups
101 WHERE lookup_code = p_lookup_code AND
102 lookup_type = p_lookup_type)
103 LOOP
104 l_meaning := x.meaning;
105 END LOOP;
106
107 RETURN l_meaning;
108
109 END ar_lookup;
110
111
112 --=============================================================================+
113 --| Public Procedure |
114 --| fnd_lookup_values |
115 --| |
116 --| Parameters |
117 --| IN |
118 --| OUT |
119 --| |
120 --| |
121 --| NOTES |
122 --| |
123 --| HISTORY |
124 --| |
125 --==============================================================================
126 FUNCTION fnd_lookup_values (
127 p_lookup_code IN VARCHAR2,
128 p_lookup_type IN VARCHAR2
129 )
130 RETURN VARCHAR2
131 IS
132 l_meaning VARCHAR2(80);
133
134 BEGIN
135 FOR x IN (SELECT meaning
136 FROM fnd_lookup_values
137 WHERE lookup_code = p_lookup_code AND
138 lookup_type = p_lookup_type AND
139 LANGUAGE(+) = USERENV('LANG'))
140 LOOP
141 l_meaning := x.meaning;
142 END LOOP;
143
144 RETURN l_meaning;
145
146 END fnd_lookup_values;
147
148
149 --=============================================================================+
150 --| Public Procedure |
151 --| as_status |
152 --| |
153 --| Parameters |
154 --| IN |
155 --| OUT |
156 --| |
157 --| |
158 --| NOTES |
159 --| |
160 --| HISTORY |
161 --| |
162 --==============================================================================
163 FUNCTION as_status (
164 p_status_code IN VARCHAR2
165 )
166 RETURN VARCHAR2
167 IS
168 l_meaning VARCHAR2(240);
169
170 BEGIN
171 IF (p_status_code IS NULL) THEN
172 RETURN NULL;
173 END IF;
174
175 FOR x IN (SELECT meaning
176 FROM as_statuses_tl
177 WHERE status_code = p_status_code AND
178 language = USERENV('LANG'))
179 LOOP
180 l_meaning := x.meaning;
181 END LOOP;
182
183 RETURN l_meaning;
184
185 END as_status;
186
187
188 --=============================================================================+
189 --| Public Procedure |
190 --| aso_i_sales_channels |
191 --| |
192 --| Parameters |
193 --| IN |
194 --| OUT |
195 --| |
196 --| |
197 --| NOTES |
198 --| |
199 --| HISTORY |
200 --| |
201 --==============================================================================
202 FUNCTION aso_i_sales_channels (
203 p_sales_channel_code IN VARCHAR2
204 )
205 RETURN VARCHAR2
206 IS
207 l_meaning VARCHAR2(80);
208
209 BEGIN
210 IF (p_sales_channel_code IS NULL) THEN
211 RETURN NULL;
212 END IF;
213
214 FOR x IN (SELECT sales_channel meaning
215 FROM aso_i_sales_channels_v
216 WHERE sales_channel_code = p_sales_channel_code)
217 LOOP
218 l_meaning := x.meaning;
219 END LOOP;
220
221 RETURN l_meaning;
222
223 END aso_i_sales_channels;
224
225
226 --=============================================================================+
227 --| Public Procedure |
228 --| as_sales_methodology |
229 --| |
230 --| Parameters |
231 --| IN |
232 --| OUT |
233 --| |
234 --| |
235 --| NOTES |
236 --| |
237 --| HISTORY |
238 --| |
239 --==============================================================================
240 FUNCTION as_sales_methodology (
241 p_sales_methodology_id IN NUMBER
242 )
243 RETURN VARCHAR2
244 IS
245 l_meaning VARCHAR2(80);
246
247 BEGIN
248 IF (p_sales_methodology_id IS NULL) THEN
249 RETURN NULL;
250 END IF;
251
252 FOR x IN (SELECT name meaning
253 FROM as_sales_methodology_vl
254 WHERE sales_methodology_id = p_sales_methodology_id)
255 LOOP
256 l_meaning := x.meaning;
257 END LOOP;
258
259 RETURN l_meaning;
260
261 END as_sales_methodology;
262
263
264 --=============================================================================+
265 --| Public Procedure |
266 --| as_sales_stages_all |
267 --| |
268 --| Parameters |
269 --| IN |
270 --| OUT |
271 --| |
272 --| |
273 --| NOTES |
274 --| |
275 --| HISTORY |
276 --| |
277 --==============================================================================
278 FUNCTION as_sales_stages_all (
279 p_sales_stage_id IN NUMBER
280 )
281 RETURN VARCHAR2
282 IS
283 l_meaning VARCHAR2(80);
284
285 BEGIN
286 IF (p_sales_stage_id IS NULL) THEN
287 RETURN NULL;
288 END IF;
289
290 FOR x IN (SELECT name meaning
291 FROM as_sales_stages_all_vl
292 WHERE sales_stage_id = p_sales_stage_id)
293 LOOP
294 l_meaning := x.meaning;
295 END LOOP;
296
297 RETURN l_meaning;
298
299 END as_sales_stages_all;
300
301
302 --=============================================================================+
303 --| Public Procedure |
304 --| as_sales_lead_ranks |
305 --| |
306 --| Parameters |
307 --| IN |
308 --| OUT |
309 --| |
310 --| |
311 --| NOTES |
312 --| |
313 --| HISTORY |
314 --| |
315 --==============================================================================
316 FUNCTION as_sales_lead_ranks (
317 p_rank_id IN NUMBER
318 )
319 RETURN VARCHAR2
320 IS
321 l_meaning VARCHAR2(240);
322
323 BEGIN
324 IF (p_rank_id IS NULL) THEN
325 RETURN NULL;
326 END IF;
327
328 FOR x IN (SELECT meaning
329 FROM as_sales_lead_ranks_vl
330 WHERE rank_id = p_rank_id)
331 LOOP
332 l_meaning := x.meaning;
333 END LOOP;
334
335 RETURN l_meaning;
336
337 END as_sales_lead_ranks;
338
339
340 --=============================================================================+
341 --| Public Procedure |
342 --| fnd_territories |
343 --| |
344 --| Parameters |
345 --| IN |
346 --| OUT |
347 --| |
348 --| |
349 --| NOTES |
350 --| |
351 --| HISTORY |
352 --| |
353 --==============================================================================
354 FUNCTION fnd_territories (
355 p_territory_code IN VARCHAR2
356 )
357 RETURN VARCHAR2
358 IS
359 l_meaning VARCHAR2(80);
360
361 BEGIN
362 IF (p_territory_code IS NULL) THEN
363 RETURN NULL;
364 END IF;
365
366 FOR x IN (SELECT territory_short_name meaning
367 FROM fnd_territories_tl
368 WHERE territory_code = p_territory_code AND
369 language = USERENV('LANG'))
370 LOOP
371 l_meaning := x.meaning;
372 END LOOP;
373
374 RETURN l_meaning;
375
376 END fnd_territories;
377
378
379
380 --=============================================================================+
381 --| Public Procedure |
382 --| hz_location_country |
383 --| |
384 --| Parameters |
385 --| IN |
386 --| OUT |
387 --| |
388 --| |
389 --| NOTES |
390 --| |
391 --| HISTORY |
392 --| |
393 --==============================================================================
394 FUNCTION hz_location_country (
395 p_party_site_id IN NUMBER
396 )
397 RETURN VARCHAR2
398 IS
399 l_meaning VARCHAR2(60);
400
401 BEGIN
402 IF (p_party_site_id IS NULL) THEN
403 RETURN NULL;
404 END IF;
405
406 FOR x IN (SELECT country meaning
407 FROM hz_locations a,
408 hz_party_sites b
409 WHERE b.party_site_id = p_party_site_id AND
410 a.location_id = b.location_id)
411 LOOP
412 l_meaning := x.meaning;
413 END LOOP;
414
415 RETURN l_meaning;
416
417 END hz_location_country;
418
419
420 --=============================================================================+
421 --| Public Procedure |
422 --| customer_contact_name |
423 --| |
424 --| Parameters |
425 --| IN |
426 --| OUT |
427 --| |
428 --| |
429 --| NOTES |
430 --| |
431 --| HISTORY |
432 --| |
433 --==============================================================================
434 FUNCTION customer_contact_name (
435 p_party_id IN NUMBER
436 )
437 RETURN VARCHAR2
438 IS
439 l_meaning VARCHAR2(302);
440
441 BEGIN
442 IF (p_party_id IS NULL) THEN
443 RETURN NULL;
444 END IF;
445
446 FOR x IN (SELECT PERSON_LAST_NAME || ' ' || PERSON_FIRST_NAME meaning
447 FROM hz_relationships a,
448 hz_parties b
449 WHERE a.party_id = p_party_id AND
450 a.subject_type = 'PERSON' AND
451 a.subject_id = b.party_id)
452 LOOP
453 l_meaning := x.meaning;
454 END LOOP;
455
456 RETURN l_meaning;
457
458 END customer_contact_name;
459
460 --=============================================================================+
461 --| Public Procedure |
462 --| customer_contact_email |
463 --| |
464 --| Parameters |
465 --| IN |
466 --| OUT |
467 --| |
468 --| |
469 --| NOTES |
470 --| |
471 --| HISTORY |
472 --| |
473 --==============================================================================
474 FUNCTION customer_contact_email (
475 p_party_id IN NUMBER
476 )
477 RETURN VARCHAR2
478 IS
479 l_meaning VARCHAR2(2000);
480
481 BEGIN
482 IF (p_party_id IS NULL) THEN
483 RETURN NULL;
484 END IF;
485
486 FOR x IN (SELECT email_address meaning
487 FROM hz_relationships a,
488 hz_parties b
489 WHERE a.party_id = p_party_id AND
490 a.subject_type = 'PERSON' AND
491 a.subject_id = b.party_id)
492 LOOP
493 l_meaning := x.meaning;
494 END LOOP;
495
496 RETURN l_meaning;
497
498 END customer_contact_email;
499
500
501 --=============================================================================+
502 --| Public Procedure |
503 --| customer_contact_phone |
504 --| |
505 --| Parameters |
506 --| IN |
507 --| OUT |
508 --| |
509 --| |
510 --| NOTES |
511 --| |
512 --| HISTORY |
513 --| |
514 --==============================================================================
515 FUNCTION customer_contact_phone (
516 p_party_id IN NUMBER
517 )
518 RETURN VARCHAR2
519 IS
520 l_meaning VARCHAR2(80);
521
522 BEGIN
523 IF (p_party_id IS NULL) THEN
524 RETURN NULL;
525 END IF;
526
527 FOR x IN (SELECT PHONE_COUNTRY_CODE || PHONE_AREA_CODE || PHONE_NUMBER ||
528 PHONE_EXTENSION meaning
529 FROM hz_contact_points
530 WHERE owner_table_name = 'HZ_PARTIES' AND
531 owner_table_id = p_party_id AND
532 contact_point_type = 'PHONE' AND
533 primary_flag = 'Y' AND
534 status = 'A')
535 LOOP
536 l_meaning := x.meaning;
537 END LOOP;
538
539 RETURN l_meaning;
540
541 END customer_contact_phone;
542
543
544 --=============================================================================+
545 --| Public Procedure |
546 --| customer_contact_name2 |
547 --| |
548 --| Parameters |
549 --| IN |
550 --| OUT |
551 --| |
552 --| |
553 --| NOTES |
554 --| |
555 --| HISTORY |
556 --| |
557 --==============================================================================
558 FUNCTION customer_contact_name2 (
559 p_lead_id IN NUMBER
560 )
561 RETURN VARCHAR2
562 IS
563 l_meaning VARCHAR2(302);
564
565 BEGIN
566
567 IF (p_lead_id IS NULL) THEN
568 RETURN NULL;
569 END IF;
570
571 FOR x IN (SELECT PERSON_LAST_NAME || ' ' || PERSON_FIRST_NAME meaning
572 FROM hz_relationships a,
573 hz_parties b,
574 as_lead_contacts_all aslc
575 WHERE aslc.lead_id = p_lead_id AND
576 aslc.primary_contact_flag = 'Y' AND
577 a.party_id = aslc.contact_party_id AND
578 a.subject_type = 'PERSON' AND
579 a.subject_id = b.party_id)
580 LOOP
581 l_meaning := x.meaning;
582 END LOOP;
583
584 RETURN l_meaning;
585
586 END customer_contact_name2;
587
588
589 --=============================================================================+
590 --| Public Procedure |
591 --| customer_contact_email2 |
592 --| |
593 --| Parameters |
594 --| IN |
595 --| OUT |
596 --| |
597 --| |
598 --| NOTES |
599 --| |
600 --| HISTORY |
601 --| |
602 --==============================================================================
603 FUNCTION customer_contact_email2 (
604 p_lead_id IN NUMBER
605 )
606 RETURN VARCHAR2
607 IS
608 l_meaning VARCHAR2(2000);
609
610 BEGIN
611 IF (p_lead_id IS NULL) THEN
612 RETURN NULL;
613 END IF;
614
615 FOR x IN (SELECT email_address meaning
616 FROM hz_relationships a,
617 hz_parties b,
618 as_lead_contacts_all aslc
619 WHERE aslc.lead_id = p_lead_id AND
620 aslc.primary_contact_flag = 'Y' AND
621 a.party_id = aslc.contact_party_id AND
622 a.subject_type = 'PERSON' AND
623 a.subject_id = b.party_id)
624 LOOP
625 l_meaning := x.meaning;
626 END LOOP;
627
628 RETURN l_meaning;
629
630 END customer_contact_email2;
631
632
633 --=============================================================================+
634 --| Public Procedure |
635 --| customer_contact_phone2 |
636 --| |
637 --| Parameters |
638 --| IN |
639 --| OUT |
640 --| |
641 --| |
642 --| NOTES |
643 --| |
644 --| HISTORY |
645 --| |
646 --==============================================================================
647 FUNCTION customer_contact_phone2 (
648 p_lead_id IN NUMBER
649 )
650 RETURN VARCHAR2
651 IS
652 l_meaning VARCHAR2(80);
653
654 BEGIN
655 IF (p_lead_id IS NULL) THEN
656 RETURN NULL;
657 END IF;
658
659 FOR x IN (SELECT PHONE_COUNTRY_CODE || PHONE_AREA_CODE || PHONE_NUMBER ||
660 PHONE_EXTENSION meaning
661 FROM hz_contact_points,
662 as_lead_contacts_all aslc
663 WHERE aslc.lead_id = p_lead_id AND
664 aslc.primary_contact_flag = 'Y' AND
665 owner_table_name = 'HZ_PARTIES' AND
666 owner_table_id = aslc.contact_party_id AND
667 contact_point_type = 'PHONE')
668 LOOP
669 l_meaning := x.meaning;
670 END LOOP;
671
672 RETURN l_meaning;
673
674 END customer_contact_phone2;
675
676
677 --=============================================================================+
678 --| Public Procedure |
679 --| referral_customer_address |
680 --| |
681 --| Parameters |
682 --| IN |
683 --| OUT |
684 --| |
685 --| |
686 --| NOTES |
687 --| |
688 --| HISTORY |
689 --| |
690 --==============================================================================
691 FUNCTION referral_customer_address (
692 p_referral_id IN NUMBER
693 )
694 RETURN VARCHAR2
695 IS
696 l_formatted_address VARCHAR2(32000);
697
698 BEGIN
699 FOR x IN (SELECT ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(
700 Null,
701 CUSTOMER_ADDRESS1,
702 CUSTOMER_ADDRESS2,
703 CUSTOMER_ADDRESS3,
704 CUSTOMER_ADDRESS4,
705 CUSTOMER_CITY,
706 CUSTOMER_COUNTY,
707 CUSTOMER_STATE,
708 CUSTOMER_PROVINCE,
709 CUSTOMER_POSTAL_CODE,
710 null,
711 CUSTOMER_COUNTRY,
712 Null,
713 Null,
714 Null,
715 Null,
716 Null,
717 NULL,
718 NULL,
719 NULL,
720 2000,
721 1,
722 1) ADDRESS
723 FROM pv_referrals_b
724 WHERE referral_id = p_referral_id)
725 LOOP
726 l_formatted_address := x.address;
727 END LOOP;
728
729 RETURN l_formatted_address;
730
731 END referral_customer_address;
732
733
734 --=============================================================================+
735 --| Public Procedure |
736 --| party_address |
737 --| |
738 --| Parameters |
739 --| IN |
740 --| OUT |
741 --| |
742 --| |
743 --| NOTES |
744 --| |
745 --| HISTORY |
746 --| |
747 --==============================================================================
748 FUNCTION party_address (
749 p_party_site_id IN NUMBER
750 )
751 RETURN VARCHAR2
752 IS
753 l_formatted_address VARCHAR2(32000);
754
755 BEGIN
756 FOR x IN (SELECT ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(
757 Null,
758 a.ADDRESS1,
759 a.ADDRESS2,
760 a.ADDRESS3,
761 a.ADDRESS4,
762 a.CITY,
763 a.COUNTY,
764 a.STATE,
765 a.PROVINCE,
766 a.POSTAL_CODE,
767 null,
768 a.COUNTRY,
769 Null,
770 Null,
771 Null,
772 Null,
773 Null,
774 NULL,
775 NULL,
776 NULL,
777 2000,
778 1,
779 1) ADDRESS
780 FROM hz_locations a,
781 hz_party_sites b
782 WHERE b.party_site_id = p_party_site_id AND
783 a.location_id = b.location_id AND
784 b.status = 'A')
785 LOOP
786 l_formatted_address := x.address;
787 END LOOP;
788
789 RETURN l_formatted_address;
790
791 END party_address;
792
793
794 --=============================================================================+
795 --| Public Procedure |
796 --| party_address2 |
797 --| |
798 --| Parameters |
799 --| IN |
800 --| OUT |
801 --| |
802 --| |
803 --| NOTES |
804 --| |
805 --| HISTORY |
806 --| |
807 --==============================================================================
808 FUNCTION party_address2 (
809 p_location_id IN NUMBER
810 )
811 RETURN VARCHAR2
812 IS
813 l_formatted_address VARCHAR2(32000);
814
815 BEGIN
816 FOR x IN (SELECT ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS_LABEL(
817 Null,
818 ADDRESS1,
819 ADDRESS2,
820 ADDRESS3,
821 ADDRESS4,
822 CITY,
823 COUNTY,
824 STATE,
825 PROVINCE,
826 POSTAL_CODE,
827 null,
828 COUNTRY,
829 Null,
830 Null,
831 Null,
832 Null,
833 Null,
834 NULL,
835 NULL,
836 NULL,
837 2000,
838 1,
839 1) ADDRESS
840 FROM hz_locations
841 WHERE location_id = p_location_id)
842 LOOP
843 l_formatted_address := x.address;
844 END LOOP;
845
846 RETURN l_formatted_address;
847
848 END party_address2;
849
850
851 --=============================================================================+
852 --| Public Procedure |
853 --| jtf_resource |
854 --| |
855 --| Parameters |
856 --| IN |
857 --| OUT |
858 --| |
859 --| |
860 --| NOTES |
861 --| |
862 --| HISTORY |
863 --| |
864 --==============================================================================
865 FUNCTION jtf_resource (
866 p_resource_id IN NUMBER
867 )
868 RETURN VARCHAR2
869 IS
870 l_source_name VARCHAR2(360);
871
872 BEGIN
873 FOR x IN (SELECT source_name
874 FROM jtf_rs_resource_extns
875 WHERE resource_id = p_resource_id)
876 LOOP
877 l_source_name := x.source_name;
878 END LOOP;
879
880 RETURN l_source_name;
881
882 END jtf_resource;
883
884
885 --=============================================================================+
886 --| Public Procedure |
887 --| user_has_permission |
888 --| |
889 --| Parameters |
890 --| IN |
891 --| OUT |
892 --| |
893 --| |
894 --| NOTES |
895 --| |
896 --| HISTORY |
897 --| |
898 --==============================================================================
899 function user_has_permission(p_contact_rel_party_id number, p_permission varchar2)
900 return number is
901 l_exists_flag number := 0;
902 begin
903 for x in ( select 1 exist_flag
904 from jtf_auth_principal_maps jtfpm,
905 jtf_auth_principals_b jtfp1,
906 jtf_auth_domains_b jtfd,
907 jtf_auth_principals_b jtfp2,
908 jtf_auth_role_perms jtfrp,
909 jtf_auth_permissions_b jtfperm,
910 fnd_user FU
911 where jtfp1.principal_name = FU.USER_NAME and
912 FU.CUSTOMER_ID = p_contact_rel_party_id and
913 jtfp1.is_user_flag=1
914 and jtfp1.jtf_auth_principal_id=jtfpm.jtf_auth_principal_id
915 and jtfpm.jtf_auth_parent_principal_id = jtfp2.jtf_auth_principal_id
916 and jtfp2.is_user_flag=0
917 and jtfp2.jtf_auth_principal_id = jtfrp.jtf_auth_principal_id
918 and jtfrp.positive_flag = 1
919 and jtfrp.jtf_auth_permission_id = jtfperm.jtf_auth_permission_id
920 and jtfperm.permission_name = p_permission
921 and jtfd.jtf_auth_domain_id=jtfpm.jtf_auth_domain_id
922 and jtfd.domain_name='CRM_DOMAIN')
923 loop
924 l_exists_flag := x.exist_flag;
925 end loop;
926 return l_exists_flag;
927 end user_has_permission;
928
929 END PV_SQL_UTILITY;
|
|
|
|