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