1 PACKAGE BODY FND_PII_UTILITY_PVT AS
2 /* $Header: fndpiutb.pls 120.1 2005/07/02 03:35:25 appldev noship $ */
3
4 -- Global constants
5
6 -- Pre-defined validation levels
7 --
8 G_PKG_NAME VARCHAR2(100) := 'FND_PII_UTILITY_PVT';
9
10 -- get the option for the party for a given business purpose code and attribute
11 FUNCTION get_purpose_attr_option
12 ( p_purpose_code IN VARCHAR2 ,
13 p_privacy_attribute_code IN VARCHAR2 ,
14 p_party_id IN NUMBER
15 ) RETURN VARCHAR2
16
17 IS
18
19 l_api_name VARCHAR2(100);
20 x_return_status VARCHAR2(1000);
21 x_msg_count NUMBER;
22 x_msg_data VARCHAR2(1000);
23
24 l_ret_value varchar2(30000);
25
26 cursor c_check_attr(l_privacy_attribute_code in varchar2,
27 l_purpose_code in varchar2)
28 is
29 select purpose_attribute_id
30 from fnd_purpose_attributes
31 where purpose_code = l_purpose_code
32 and privacy_attribute_code = l_privacy_attribute_code;
33
34 r_check_attr c_check_attr%rowtype;
35
36 cursor c_pref(l_purpose_code in varchar2,
37 l_party_id in varchar2)
38 is
39 select contact_preference_id,
40 preference_code
41 FROM HZ_CONTACT_PREFERENCES pref
42 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
43 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
44 AND pref.preference_topic_type_code = l_purpose_code -- this will be l_purpose_code
45 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B' -- this will be FND_BUSINESS_PURPOSES
46 AND pref.contact_type = 'PRIV_PREF'
47 AND status = 'A';
48
49 r_pref c_pref%rowtype;
50
51 cursor c_default(l_purpose_code in varchar2)
52 is
53 select decode(purpose_default_code, 'N', 'I', purpose_default_code) purpose_default_code
54 from fnd_business_purposes_b
55 where purpose_code = l_purpose_code;
56
57 r_default c_default%rowtype;
58
59 BEGIN
60 x_return_status := fnd_api.g_ret_sts_success;
61 l_ret_value := 'I';
62 l_api_name := 'GET_PURPOSE_OPTION';
63
64 if(p_privacy_attribute_code is not null and p_party_id is not null and p_purpose_code is not null)
65 then
66 -- check if the privacy_attribute_code passed in is used for the business_purpose
67 open c_check_attr(p_privacy_attribute_code, p_purpose_code);
68 fetch c_check_attr into r_check_attr;
69 if(c_check_attr%found)
70 then
71 l_ret_value := 'O';
72 else
73 l_ret_value := 'I';
74 end if; -- end of c_check_attr
75 close c_check_attr;
76
77 -- attribute found mapped to business purpose then determine the option for the party for this business purpose
78 if(l_ret_value = 'I')
79 then
80 null;
81 else
82 open c_pref(p_purpose_code,
83 p_party_id);
84 fetch c_pref into r_pref;
85 if(c_pref%found)
86 then
87 if(r_pref.preference_code = 'DO_NOT')
88 then
89 l_ret_value := 'O';
90 else
91 l_ret_value := 'I';
92 end if;
93 else
94 -- find the default option for this business purpose
95 open c_default(p_purpose_code);
96 fetch c_default into r_default;
97 l_ret_value := r_default.purpose_default_code;
98 close c_default;
99
100
101 end if; -- end of c_pref
102 close c_pref;
103 end if; -- end of l_ret_value check
104 end if; -- end of null check for party id, privacy attribute code and purpose code
105
106 return l_ret_value;
107
108 EXCEPTION
109 WHEN FND_API.G_EXC_ERROR THEN
110 x_return_status := FND_API.G_RET_STS_ERROR ;
111 FND_MSG_PUB.Count_And_Get
112 (
113 p_count => x_msg_count,
114 p_data => x_msg_data
115 );
116
117 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
119 FND_MSG_PUB.Count_And_Get
120 (
121 p_count => x_msg_count,
122 p_data => x_msg_data
123 );
124
125 WHEN OTHERS THEN
126 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
127 fnd_message.set_token('P_SQLCODE',SQLCODE);
128 fnd_message.set_token('P_SQLERRM',SQLERRM);
129 fnd_message.set_token('P_API_NAME',l_api_name);
130 FND_MSG_PUB.add;
131
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
133 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
134 FND_MSG_PUB.Add_Exc_Msg
135 (
136 G_PKG_NAME,
137 l_api_name
138 );
139 END IF;
140
141 FND_MSG_PUB.Count_And_Get
142 (
143 p_count => x_msg_count,
144 p_data => x_msg_data
145 );
146 END get_purpose_attr_option;
147
148
149 -- get the option for the party for a given attribute . if person is opted out of any one purpose
150 -- using the attribute then the return value is 'O' which is opted out.
151 FUNCTION get_attribute_option
152 ( p_privacy_attribute_code IN VARCHAR2 ,
153 p_party_id IN NUMBER
154 ) RETURN VARCHAR2
155 IS
156 l_api_name VARCHAR2(100) ;
157 x_return_status VARCHAR2(1000);
158 x_msg_count NUMBER;
159 x_msg_data VARCHAR2(1000);
160
161 cursor c_purp(l_privacy_attribute_code in varchar2)
162 is
163 select attr.purpose_attribute_id,
164 attr.purpose_code,
165 purp.purpose_default_code
166 from fnd_purpose_attributes attr,
167 fnd_business_purposes_b purp
168 where attr.privacy_attribute_code = l_privacy_attribute_code
169 and attr.purpose_code = purp.purpose_code;
170
171 r_purp c_purp%rowtype;
172
173
174 cursor c_pref(l_purpose_code in varchar2,
175 l_party_id in number)
176 is
177 select pref.contact_preference_id,
178 pref.preference_code
179 FROM HZ_CONTACT_PREFERENCES pref
180 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
181 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
182 AND pref.preference_topic_type_code = l_purpose_code -- this will be l_purpose_code
183 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B' -- this will be FND_BUSINESS_PURPOSES
184 AND pref.contact_type = 'PRIV_PREF'
185 AND status = 'A' ;
186
187
188
189 r_pref c_pref%rowtype;
190
191
192 l_ret_value varchar2(10) ;
193
194
195
196 BEGIN
197 x_return_status := fnd_api.g_ret_sts_success;
198 l_ret_value := 'I';
199 l_api_name := 'GET_ATTRIBUTES';
200
201 if(p_privacy_attribute_code is not null and p_party_id is not null)
202 then
203 -- get the business purposes that use this attribute
204 for r_purp in c_purp(p_privacy_attribute_code)
205 loop
206 -- if the defualt code is 'I' then check if there are any 'O' records in cont pref.
207 -- if found then set ret value to 'O' . exit with the value
208 -- if default value is 'O' then check if there are any 'I' records cont pref
209 -- if found then do nothing. else set ret value to 'O' and exit. If exit condition does
210 -- not occur then look at next purpose
211 open c_pref(r_purp.purpose_code, p_party_id);
212 fetch c_pref into r_pref;
213 close c_pref;
214 IF(r_purp.purpose_default_code = 'I')
215 THEN
216 if(r_pref.preference_code = 'DO_NOT')
217 then
218 l_ret_value := 'O';
219 exit;
220 end if;
221 ELSIF(r_purp.purpose_default_code = 'O')
222 THEN
223 if(r_pref.preference_code = 'DO')
224 then
225 null;
226 else
227 l_ret_value := 'O';
228 exit;
229 end if;
230 END IF; -- end of r_purp.purpose_defualt_code check
231
232 end loop; -- end of r_purp loop
233 end if; -- end of p_privacy_attribute_code and p_party_id null check
234
235 return l_ret_value;
236
237 EXCEPTION
238 WHEN FND_API.G_EXC_ERROR THEN
239 x_return_status := FND_API.G_RET_STS_ERROR ;
240 FND_MSG_PUB.Count_And_Get
241 (
242 p_count => x_msg_count,
243 p_data => x_msg_data
244 );
245
246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
247 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
248 FND_MSG_PUB.Count_And_Get
249 (
250 p_count => x_msg_count,
251 p_data => x_msg_data
252 );
253
254 WHEN OTHERS THEN
255 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
256 fnd_message.set_token('P_SQLCODE',SQLCODE);
257 fnd_message.set_token('P_SQLERRM',SQLERRM);
258 fnd_message.set_token('P_API_NAME',l_api_name);
259 FND_MSG_PUB.add;
260
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
262 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
263 FND_MSG_PUB.Add_Exc_Msg
264 (
265 G_PKG_NAME,
266 l_api_name
267 );
268 END IF;
269
270 FND_MSG_PUB.Count_And_Get
271 (
272 p_count => x_msg_count,
273 p_data => x_msg_data
274 );
275 END get_attribute_option;
276
277 -- get the option for the party for a given business purpose code
278 FUNCTION get_purpose_option
279 ( p_purpose_code IN VARCHAR2 ,
280 p_party_id IN NUMBER
281 ) RETURN VARCHAR2
282
283 IS
284
285 l_api_name VARCHAR2(100);
286 x_return_status VARCHAR2(1000);
287 x_msg_count NUMBER;
288 x_msg_data VARCHAR2(1000);
289
290 l_ret_value varchar2(30000);
291
292 cursor c_pref(l_purpose_code in varchar2,
293 l_party_id in varchar2)
294 is
295 select contact_preference_id,
296 preference_code
297 FROM HZ_CONTACT_PREFERENCES pref
298 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
299 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
300 AND pref.preference_topic_type_code = l_purpose_code -- this will be l_purpose_code
301 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B' -- this will be FND_BUSINESS_PURPOSES
302 AND pref.contact_type = 'PRIV_PREF'
303 AND status = 'A';
304
305 r_pref c_pref%rowtype;
306
307 cursor c_default(l_purpose_code in varchar2)
308 is
309 select purpose_default_code
310 from fnd_business_purposes_b
311 where purpose_code = l_purpose_code;
312
313 r_default c_default%rowtype;
314
315 BEGIN
316 x_return_status := fnd_api.g_ret_sts_success;
317 l_ret_value := 'I';
318 l_api_name := 'GET_PURPOSE__ATTR_OPTION';
319
320 if(p_purpose_code is not null and p_party_id is not null)
321 then
322 -- check if party has a option set for the purpose. else return defualt option
323 open c_pref(p_purpose_code,
324 p_party_id);
325 fetch c_pref into r_pref;
326 if(c_pref%found)
327 then
328 if(r_pref.preference_code = 'DO_NOT')
329 then
330 l_ret_value := 'O';
331 else
332 l_ret_value := 'I';
333 end if;
334 else
335 -- find the default option for this business purpose
336 open c_default(p_purpose_code);
337 fetch c_default into r_default;
338 l_ret_value := r_default.purpose_default_code;
339 close c_default;
340 end if; -- end of c_pref
341 close c_pref;
342 end if; -- end of null check for party id and purpose code
343
344 return l_ret_value;
345
346 EXCEPTION
347 WHEN FND_API.G_EXC_ERROR THEN
348 x_return_status := FND_API.G_RET_STS_ERROR ;
349 FND_MSG_PUB.Count_And_Get
350 (
351 p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354
355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
357 FND_MSG_PUB.Count_And_Get
358 (
359 p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362
363 WHEN OTHERS THEN
364 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
365 fnd_message.set_token('P_SQLCODE',SQLCODE);
366 fnd_message.set_token('P_SQLERRM',SQLERRM);
367 fnd_message.set_token('P_API_NAME',l_api_name);
368 FND_MSG_PUB.add;
369
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
371 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
372 FND_MSG_PUB.Add_Exc_Msg
373 (
374 G_PKG_NAME,
375 l_api_name
376 );
377 END IF;
378
379 FND_MSG_PUB.Count_And_Get
380 (
381 p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384 END get_purpose_option;
385
386 END FND_PII_UTILITY_PVT;
[Home] [Help]