1 PACKAGE BODY CS_Parties_PKG AS
2 /* $Header: csxptsb.pls 115.7 2002/09/13 17:06:07 epajaril ship $ */
3 FUNCTION Get_Party_Phone
4 (
5 p_party_id IN NUMBER,
6 p_telephone_type IN VARCHAR2
7 ) RETURN VARCHAR2 IS
8
9 CURSOR cur_party_phone IS
10 select
11 decode(phone_country_code,null,null,phone_country_code||'-')||
12 decode(phone_area_code,null,'( )','('||phone_Area_code||')')||
13 phone_number||decode(phone_extension,null,null,' x'||phone_extension) phone_number
14 from
15 hz_contact_points
16 where owner_table_name = 'HZ_PARTIES' and
17 owner_table_id = p_party_id and
18 contact_point_type = 'PHONE' and
19 phone_line_type = p_telephone_type
20 order by 1;
21
22
23 CURSOR cur_party_phone_count IS
24 select
25 count(*)
26 from
27 hz_contact_points
28 where owner_table_name = 'HZ_PARTIES' and
29 owner_table_id = p_party_id and
30 contact_point_type = 'PHONE' and
31 phone_line_type = p_telephone_type
32 order by 1;
33
34 CURSOR cur_primary_party_phone IS
35
36 select
37 decode(phone_country_code,null,null,phone_country_code||'-')||
38 decode(phone_area_code,null,'( )','('||phone_Area_code||')')||
39 phone_number||decode(phone_extension,null,null,' x'||phone_extension) phone_number
40 from
41 hz_contact_points
42 where owner_table_name = 'HZ_PARTIES' and
43 owner_table_id = p_party_id and
44 contact_point_type = 'PHONE' and
45 phone_line_type = p_telephone_type and
46 primary_flag = 'Y' order by 1;
47 l_primary_phone_number varchar2(50);
48
49 l_phone_number varchar2(60);
50 l_phone_number_cnt varchar2(5);
51 BEGIN
52 open cur_party_phone_count;
53 fetch cur_party_phone_count into l_phone_number_cnt;
54 if cur_party_phone_count%NOTFOUND then
55 return(null);
56 end if;
57 close cur_party_phone_count;
58
59
60 open cur_party_phone;
61 loop
62
63 fetch cur_party_phone into l_phone_number;
64 if cur_party_phone%NOTFOUND then
65 RETURN(null);
66 end if;
67 if l_phone_number_cnt > 1 then
68 if cur_party_phone%ROWCOUNT > 1 then
69 BEGIN
70 open cur_primary_party_phone;
71 loop
72 fetch cur_primary_party_phone into l_primary_phone_number;
73 if cur_primary_party_phone%NOTFOUND then
74 RETURN(l_phone_number);
75 end if;
76
77 if cur_primary_party_phone%FOUND then
78 RETURN(l_primary_phone_number);
79 end if;
80 end loop;
81 close cur_primary_party_phone;
82 END;
83 end if;
84 else
85 RETURN(l_phone_number);
86 end if;
87 end loop;
88 RETURN(l_phone_number);
89 close cur_party_phone;
90
91 END;
92
93 FUNCTION Get_Party_Email
94 (
95 p_party_id IN NUMBER
96 )
97 RETURN VARCHAR2 IS
98 l_email varchar2(50);
99 CURSOR cur_email IS
100 select email_Address from hz_contact_points
101 where
102 owner_table_name = 'HZ_PARTIES' and
103 owner_table_id = p_party_id and contact_point_type = 'EMAIL'
104
105 order by 1;
106 Begin
107 open cur_email;
108 fetch cur_email into l_email;
109 if cur_email%NOTFOUND then
110 RETURN(NULL);
111 end if;
112 close cur_email;
113 RETURN(l_email);
114 End;
115
116 FUNCTION Get_Party_Fax
117 (
118
119 p_party_id IN NUMBER
120 ) RETURN VARCHAR2 IS
121 CURSOR cur_party_fax IS
122 select
123 decode(phone_country_code,null,null,phone_country_code||'-')||
124 decode(phone_area_code,null,'( )','('||phone_Area_code||')')||phone_number||
125 decode(phone_extension,null,null,' x'||phone_extension) phone_number
126 from
127 hz_contact_points
128 where owner_table_name = 'HZ_PARTIES' and
129 owner_table_id = p_party_id and
130 contact_point_type = 'PHONE'
131 and phone_line_type = 'FAX'
132 order by 1;
133
134 l_fax_number varchar2(60);
135 BEGIN
136 open cur_party_fax;
137 fetch cur_party_fax into l_fax_number;
138 if cur_party_fax%NOTFOUND then
139 RETURN(null);
140 end if;
141 close cur_party_fax;
142 RETURN(l_fax_number);
143 END;
144 END CS_Parties_PKG;
[Home] [Help]