1 PACKAGE BODY FND_UPDATE_USER_PREF_PUB AS
2 /* $Header: fndpiprb.pls 120.1 2005/07/02 03:35:01 appldev noship $ */
3
4 -- Global constants
5 G_PKG_NAME VARCHAR2(100) := 'FND_UPDATE_USER_PREF_PUB';
6 -- Pre-defined validation levels
7 --
8
9 PROCEDURE set_donotuse_preference
10 ( p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 ,
12 p_commit IN VARCHAR2 ,
13 p_user_id IN NUMBER ,
14 p_party_id IN NUMBER ,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2
18 )
19 IS
20
21 l_api_name VARCHAR2(100);
22 l_api_name_1 VARCHAR2(100);
23 l_api_version number;
24
25 l_sysdate Date ;
26
27 Cursor c_bus_purp
28 IS
29 select purpose_code,
30 purpose_default_code
31 from fnd_business_purposes_b
32 where purpose_code <> 'ALL';
33
34
35 CURSOR c_cont_pref(l_party_id in number,
36 l_purpose_code in varchar2)
37 IS
38 select contact_preference_id,
39 object_version_number,
40 contact_type
41 contact_level_table,
42 contact_level_table_id ,
43 preference_code ,
44 preference_topic_type ,
45 preference_topic_type_id ,
46 preference_topic_type_code ,
47 preference_start_date ,
48 preference_end_date ,
49 requested_by ,
50 reason_code ,
51 status ,
52 created_by_module ,
53 contact_type
54 FROM HZ_CONTACT_PREFERENCES pref
55 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
56 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
57 AND pref.preference_topic_type_code = l_purpose_code -- this will be l_purpose_code
58 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B' -- this will be FND_BUSINESS_PURPOSES
59 AND pref.contact_type = 'PRIV_PREF'
60 -- AND sysdate between pref.preference_start_date and nvl(pref.preference_end_date, sysdate +1)
61 AND status = 'A';
62
63 r_cont_pref c_cont_pref%rowtype;
64
65 i number ;
66 k number ;
67
68 cursor c_party(l_user_id number)
69 is
70 select person_party_id
71 from fnd_user
72 where user_id = l_user_id;
73
74 l_party_id number ;
75
76 l_contact_preference_record hz_contact_preference_v2pub.contact_preference_rec_type;
77
78 l_object_version_number number;
79 l_return_status varchar2(20);
80 l_msg_count number;
81 l_msg_data varchar2(2000);
82 l_contact_preference_id number;
83 l_requested_by varchar2(30);
84
85 BEGIN
86
87 -- initialize variables
88 l_api_name := 'SET_DONOTUSE_PREFERENCE';
89 l_api_name_1 := 'SET_DONOTUSE_PREFERENCE';
90 l_api_version := 1;
91 l_sysdate := sysdate;
92 i := 0;
93 k := 0;
94 x_return_status := fnd_api.g_ret_sts_success;
95 l_party_id := p_party_id;
96
97 /* requested_by is validated against lookup_code for lookup_type = 'REQUESTED_BY'. valid values are INTERNAL/PARTY */
98 l_requested_by := 'INTERNAL';
99
100 /* Standard call to check for call compatibility */
101 /*
102 IF NOT fnd_api.compatible_api_call (l_api_version,
103 p_api_version,
104 l_api_name,
105 g_pkg_name) THEN
106 RAISE fnd_api.g_exc_unexpected_error;
107 END IF;
108 */
109
110 /* Initialize message list if p_init_msg_list is set to TRUE */
111
112 IF fnd_api.to_boolean (p_init_msg_list) THEN
113 fnd_msg_pub.initialize;
114 END IF;
115
116 /*If party id has not been passed then get the party id from fnd_user */
117 if(p_party_id is null)
118 then
119 open c_party(p_user_id);
120 fetch c_party into l_party_id;
121 close c_party;
122 end if;
123
124
125 IF (l_party_id is not null)
126 THEN
127
128 -- check to see if the 'ALL' Business Purpose is already opted out
129 open c_cont_pref(l_party_id,
130 'ALL');
131 fetch c_cont_pref into r_cont_pref;
132 close c_cont_pref;
133 -- if opted out record already exists then no inserts required for ALL
134 -- else insert the ALL opt-out record. This is to hold state of the Opt-out of All Purposes button in UI
135 -- for the party
136 IF(r_cont_pref.preference_code = 'DO_NOT')
137 THEN
138 null;
139 ELSE
140 -- create the record
141 --l_contact_preference_record.contact_preference_id := r_cont_pref.contact_preference_id;
142 l_contact_preference_record.contact_level_table := 'HZ_PARTIES';
143 l_contact_preference_record.contact_level_table_id := l_party_id;
144 l_contact_preference_record.contact_type := 'PRIV_PREF';
145 l_contact_preference_record.preference_code := 'DO_NOT';
146 l_contact_preference_record.preference_topic_type := 'FND_BUSINESS_PURPOSES_B';
147 --l_contact_preference_record.preference_topic_type_id := 'ALL';
148 l_contact_preference_record.preference_topic_type_code := 'ALL';
149 l_contact_preference_record.preference_start_date := trunc(sysdate);
150 l_contact_preference_record.preference_end_date := null ;
151 l_contact_preference_record.requested_by := l_requested_by;
152 l_contact_preference_record.status := 'A' ;
153 l_contact_preference_record.created_by_module := 'FND Data Privacy' ;
154 l_contact_preference_record.application_id := 0 ;
155
156
157 -- change the API Name temporarily so that in case of unexpected error
158 -- it is properly caught
159 l_api_name := l_api_name||'-CREATE_CONTACT_PREFERENCE';
160
161 hz_contact_preference_v2pub.create_contact_preference (
162 p_init_msg_list => FND_API.G_FALSE,
163 p_contact_preference_rec => l_contact_preference_record,
164 x_contact_preference_id => l_contact_preference_id,
165 x_return_status => l_return_status,
166 x_msg_count => l_msg_count,
167 x_msg_data => l_msg_data
168 );
169
170 -- set back the API name to original name
171 l_api_name := l_api_name_1;
172
173 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
174 -- Unexpected Execution Error from call to Get_contracts_resources
175 fnd_message.set_name('FND', 'FND_PII_GENERIC_API_ERROR');
176 fnd_message.set_token('P_PROC_NAME','CREATE_CONTACT_PREFERENCE');
177 fnd_message.set_token('P_API_NAME', l_api_name);
178 fnd_msg_pub.add;
179 RAISE fnd_api.g_exc_error;
180 END IF; -- end of x_return_status check
181
182 END IF; -- end of DO check
183
184 FOR r_bus_purp in c_bus_purp
185 LOOP
186 IF(r_bus_purp.purpose_default_code = 'N')
187 THEN
188 -- if the default option is No choice then there will not be any records in HZ_CONTACT_PREFERENCES
189 null;
190 ELSIF(r_bus_purp.purpose_default_code = 'O')
191 THEN
192 -- if the default option is Opt Out, and a party opt in record exists in HZ_CONTACT_PREFERENCES
193 -- the record has to be deleted
194 open c_cont_pref(l_party_id,
195 r_bus_purp.purpose_code);
196 fetch c_cont_pref into r_cont_pref;
197 close c_cont_pref;
198
199 -- if a opt in record exists then that has to be deleted
200 IF(r_cont_pref.preference_code = 'DO')
201 THEN
202 delete hz_contact_preferences
203 where contact_preference_id = r_cont_pref.contact_preference_id;
204 END IF; -- end of DO check
205
206 ELSIF(r_bus_purp.purpose_default_code = 'I')
207 THEN
208 -- if the default option is opt in, and a party opt out record DOES NOT exist in HZ_CONTACT_PREFERENCES
209 -- then create an opt-out record for the Business Purpose and Party
210 open c_cont_pref(l_party_id,
211 r_bus_purp.purpose_code);
212 fetch c_cont_pref into r_cont_pref;
213 --close c_cont_pref;
214
215 -- if opt-out record does not exist then create it
216 IF(r_cont_pref.preference_code = 'DO_NOT')
217 THEN
218 null;
219 ELSE
220 -- create the record
221 --l_contact_preference_record.contact_preference_id := r_cont_pref.contact_preference_id;
222 l_contact_preference_record.contact_level_table := 'HZ_PARTIES';
223 l_contact_preference_record.contact_level_table_id := l_party_id;
224 l_contact_preference_record.contact_type := 'PRIV_PREF';
225 l_contact_preference_record.preference_code := 'DO_NOT';
226 l_contact_preference_record.preference_topic_type := 'FND_BUSINESS_PURPOSES_B';
227 --l_contact_preference_record.preference_topic_type_id := r_bus_purp.purpose_code;
228 l_contact_preference_record.preference_topic_type_code := r_bus_purp.purpose_code;
229 l_contact_preference_record.preference_start_date := trunc(sysdate);
230 l_contact_preference_record.preference_end_date := null ;
231 l_contact_preference_record.requested_by := l_requested_by;
232 l_contact_preference_record.status := 'A' ;
233 l_contact_preference_record.created_by_module := 'FND Data Privacy' ;
234 l_contact_preference_record.application_id := 0; --r_cont_pref.application_id ;
235
236
237 -- change the API Name temporarily so that in case of unexpected error
238 -- it is properly caught
239 l_api_name := l_api_name||'-CREATE_CONTACT_PREFERENCE';
240
241 hz_contact_preference_v2pub.create_contact_preference (
242 p_init_msg_list => FND_API.G_FALSE,
243 p_contact_preference_rec => l_contact_preference_record,
244 x_contact_preference_id => l_contact_preference_id,
245 x_return_status => l_return_status,
246 x_msg_count => l_msg_count,
247 x_msg_data => l_msg_data
248 );
249
250 -- set back the API name to original name
251 l_api_name := l_api_name_1;
252
253 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
254 -- Unexpected Execution Error from call to Get_contracts_resources
255 fnd_message.set_name('FND', 'FND_PII_GENERIC_API_ERROR');
256 fnd_message.set_token('P_PROC_NAME','CREATE_CONTACT_PREFERENCE');
257 fnd_message.set_token('P_API_NAME', l_api_name);
258 fnd_msg_pub.add;
259 RAISE fnd_api.g_exc_error;
260 END IF; -- end of x_return_status check
261
262 END IF; -- end of DO check
263
264 close c_cont_pref;
265 END IF;
266 END LOOP; -- end of c_bus_purp
267
268
269 END IF; -- end of p_user_id check
270
271 IF (p_commit = FND_API.G_TRUE)
272 THEN
273 COMMIT;
274 END IF;
275
276 -- add confirmation message
277 fnd_message.set_name ('FND', 'FND_PII_CONFIRM_SAVE');
278 FND_MSG_PUB.add;
279 FND_MSG_PUB.Count_And_Get
280 (
281 p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284
285 EXCEPTION
286 WHEN FND_API.G_EXC_ERROR THEN
287 x_return_status := FND_API.G_RET_STS_ERROR ;
288 FND_MSG_PUB.Count_And_Get
289 (
290 p_count => x_msg_count,
291 p_data => x_msg_data
292 );
293
294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
296 FND_MSG_PUB.Count_And_Get
297 (
298 p_count => x_msg_count,
299 p_data => x_msg_data
300 );
301
302 WHEN OTHERS THEN
303 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
304 fnd_message.set_token('P_SQLCODE',SQLCODE);
305 fnd_message.set_token('P_SQLERRM',SQLERRM);
306 fnd_message.set_token('P_API_NAME',l_api_name);
307 FND_MSG_PUB.add;
308
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
310 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
311 FND_MSG_PUB.Add_Exc_Msg
312 (
313 G_PKG_NAME,
314 l_api_name
315 );
316 END IF;
317
318 FND_MSG_PUB.Count_And_Get
319 (
320 p_count => x_msg_count,
321 p_data => x_msg_data
322 );
323
324 END set_donotuse_preference;
325
326 PROCEDURE set_default_preference
327 ( p_api_version IN NUMBER,
328 p_init_msg_list IN VARCHAR2 ,
329 p_commit IN VARCHAR2 ,
330 p_user_id IN NUMBER ,
331 p_party_id IN NUMBER ,
332 x_return_status OUT NOCOPY VARCHAR2,
333 x_msg_count OUT NOCOPY NUMBER,
334 x_msg_data OUT NOCOPY VARCHAR2
335 )
336 IS
337
338 l_api_name VARCHAR2(100) ;
339 l_api_name_1 VARCHAR2(100) ;
340 l_api_version number ;
341
342 l_sysdate Date ;
343
344 Cursor c_purp_attr
345 IS
346 select purpose_attribute_id,
347 attribute_default_code
348 from fnd_purpose_attributes;
349
350 --r_purp_attr c_purp_attr%rowtype;
351
352
353 cursor c_party(l_user_id number)
354 is
355 select customer_id
356 from fnd_user
357 where user_id = l_user_id;
358
359 l_party_id number;
360
361
362 l_return_status varchar2(20);
363 l_msg_count number;
364 l_msg_data varchar2(2000);
365 l_contact_preference_id number;
366 l_requested_by varchar2(30);
367
368 BEGIN
369
370 -- initialize variables
371
372 -- initialize variables
373 l_api_name := 'SET_DEFAULT_PREFERENCE';
374 l_api_name_1 := 'SET_DEFAULT_PREFERENCE';
375 l_api_version := 1;
376 l_sysdate := sysdate;
377 x_return_status := fnd_api.g_ret_sts_success;
378
379 l_party_id := p_party_id;
380 l_requested_by := 'INTERNAL';
381 l_party_id := p_party_id;
382
383
384 IF fnd_api.to_boolean (p_init_msg_list) THEN
385 fnd_msg_pub.initialize;
386 END IF;
387
388 /*If party id has not been passed then get the party id from fnd_user */
389 if(p_party_id is null)
390 then
391 open c_party(p_user_id);
392 fetch c_party into l_party_id;
393 close c_party;
394 end if;
395
396
397 IF (l_party_id is not null)
398 THEN
399 /* as the default options are to be set for the party, so all opt-in/opt-out records from contact
400 preferences will have to be removed */
401 delete HZ_CONTACT_PREFERENCES pref
402 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
403 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
404 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B'
405 AND pref.contact_type = 'PRIV_PREF';
406
407 END IF; -- end of p_party_id check
408
409 fnd_message.set_name ('FND', 'FND_PII_CONFIRM_SAVE');
410 FND_MSG_PUB.add;
411 FND_MSG_PUB.Count_And_Get
412 (
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416
417 EXCEPTION
418 WHEN FND_API.G_EXC_ERROR THEN
419 x_return_status := FND_API.G_RET_STS_ERROR ;
420 FND_MSG_PUB.Count_And_Get
421 (
422 p_count => x_msg_count,
423 p_data => x_msg_data
424 );
425
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
428 FND_MSG_PUB.Count_And_Get
429 (
430 p_count => x_msg_count,
431 p_data => x_msg_data
432 );
433
434 WHEN OTHERS THEN
435 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
436 fnd_message.set_token('P_SQLCODE',SQLCODE);
437 fnd_message.set_token('P_SQLERRM',SQLERRM);
438 fnd_message.set_token('P_API_NAME',l_api_name);
439 FND_MSG_PUB.add;
440
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
442 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
443 FND_MSG_PUB.Add_Exc_Msg
444 (
445 G_PKG_NAME,
446 l_api_name
447 );
448 END IF;
449
450 FND_MSG_PUB.Count_And_Get
451 (
452 p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455
456 END set_default_preference;
457
458
459 PROCEDURE set_purpose_option
460 ( p_api_version IN NUMBER,
461 p_init_msg_list IN VARCHAR2 ,
462 p_commit IN VARCHAR2 ,
463 p_user_id IN NUMBER ,
464 p_party_id IN NUMBER ,
465 p_option IN preference_tbl ,
466 x_return_status OUT NOCOPY VARCHAR2,
467 x_msg_count OUT NOCOPY NUMBER,
468 x_msg_data OUT NOCOPY VARCHAR2
469 )
470 IS
471 l_api_name VARCHAR2(100) ;
472 l_api_name_1 VARCHAR2(100) ;
473 l_api_version number ;
474
475 l_sysdate Date ;
476
477 Cursor c_purp_attr
478 IS
479 select purpose_attribute_id,
480 attribute_default_code
481 from fnd_purpose_attributes;
482
483 --r_purp_attr c_purp_attr%rowtype;
484
485 CURSOR c_cont_pref(l_party_id in number,
486 l_purpose_code in varchar2)
487 IS
488 select contact_preference_id,
489 preference_code
490 FROM HZ_CONTACT_PREFERENCES pref
491 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
492 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
493 --AND pref.preference_topic_type_id = l_purpose_code
494 AND pref.preference_topic_type_code = l_purpose_code
495 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B'
496 AND pref.contact_type = 'PRIV_PREF'
497 AND status = 'A';
498
499 r_cont_pref c_cont_pref%rowtype;
500
501 i number ;
502 k number ;
503
504 cursor c_party(l_user_id number)
505 is
506 select customer_id
507 from fnd_user
508 where user_id = l_user_id;
509
510 l_party_id number ;
511
512 l_contact_preference_record hz_contact_preference_v2pub.contact_preference_rec_type;
513
514 l_object_version_number number;
515 l_return_status varchar2(20);
516 l_msg_count number;
517 l_msg_data varchar2(2000);
518 l_contact_preference_id number;
519 l_requested_by varchar2(30) ;
520 l_all_flag varchar2(1);
521
522 BEGIN
523
524 -- initialize variables
525 l_api_name := 'SET_PURPOSE_OPTION';
526 l_api_name_1 := 'SET_PURPOSE_OPTION';
527 l_api_version := 1;
528 l_sysdate := sysdate;
529 i := 0;
530 k := 0;
531 x_return_status := fnd_api.g_ret_sts_success;
532
533 l_party_id := p_party_id;
534 l_requested_by := 'INTERNAL';
535 l_all_flag := 'N';
536
537
538 IF fnd_api.to_boolean (p_init_msg_list) THEN
539 fnd_msg_pub.initialize;
540 END IF;
541
542 IF fnd_api.to_boolean (p_init_msg_list) THEN
543 fnd_msg_pub.initialize;
544 END IF;
545
546 /*If party id has not been passed then get the party id from fnd_user */
547 if(p_party_id is null)
548 then
549 open c_party(p_user_id);
550 fetch c_party into l_party_id;
551 close c_party;
552 end if;
553
554
555 IF((l_party_id is not null) and ( p_option.count > 0))
556 THEN
557 FOR i in p_option.first..p_option.last
558 LOOP
559 l_contact_preference_record := null;
560 IF(p_option(i).purpose_default_code = 'N')
561 THEN
562 -- do nothing
563 null;
564 ELSIF(p_option(i).purpose_default_code in ('I', 'O'))
565 THEN
566 IF(p_option(i).user_option = p_option(i).purpose_default_code )
567 THEN
568 -- if user option is same as default code then delete any opt-in/opt-out from hz_contact_preferences table
569 -- check to see if any record already exists .
570 r_cont_pref := null;
571 open c_cont_pref(l_party_id, p_option(i).purpose_code);
572 fetch c_cont_pref into r_cont_pref;
573 if(c_cont_pref%FOUND)
574 THEN
575 l_all_flag := 'Y';
576
577 delete HZ_CONTACT_PREFERENCES pref
578 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
579 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
580 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B'
581 AND pref.contact_type = 'PRIV_PREF'
582 AND pref.preference_topic_type_code = p_option(i).purpose_code;
583
584 END IF;
585 close c_cont_pref;
586
587
588 ELSIF(p_option(i).user_option = 'I')
589 THEN
590 -- check to see if a opt-in record already exists . if not insert the record
591 r_cont_pref := null;
592 open c_cont_pref(l_party_id, p_option(i).purpose_code);
593 fetch c_cont_pref into r_cont_pref;
594 close c_cont_pref;
595
596 IF(r_cont_pref.preference_code = 'DO')
597 THEN
598 -- do nothing as OPT-IN record already exists
599 null;
600 ELSIF(r_cont_pref.preference_code = 'DO_NOT')
601 THEN
602 l_all_flag := 'Y';
603 -- delete the OPT-OUT record , in the unlikely case that it exisrs
604 delete HZ_CONTACT_PREFERENCES pref
605 where pref.contact_preference_id = r_cont_pref.contact_preference_id;
606 ELSE
607 l_all_flag := 'Y';
608 -- insert the OPT-IN record
609 --l_contact_preference_record.contact_preference_id := r_cont_pref.contact_preference_id;
610 l_contact_preference_record.contact_level_table := 'HZ_PARTIES';
611 l_contact_preference_record.contact_level_table_id := l_party_id;
612 l_contact_preference_record.contact_type := 'PRIV_PREF';
613 l_contact_preference_record.preference_code := 'DO';
614 l_contact_preference_record.preference_topic_type := 'FND_BUSINESS_PURPOSES_B';
615 --l_contact_preference_record.preference_topic_type_id := p_option(i).purpose_code;
616 l_contact_preference_record.preference_topic_type_code := p_option(i).purpose_code;
617 l_contact_preference_record.preference_start_date := trunc(sysdate);
618 l_contact_preference_record.preference_end_date := null ;
619 l_contact_preference_record.requested_by := l_requested_by;
620 l_contact_preference_record.status := 'A' ;
621 l_contact_preference_record.created_by_module := 'FND Data Privacy' ;
622 l_contact_preference_record.application_id := 0; --r_cont_pref.application_id ;
623
624
625 -- change the API Name temporarily so that in case of unexpected error
626 -- it is properly caught
627 l_api_name := l_api_name||'-CREATE_CONTACT_PREFERENCE';
628
629 hz_contact_preference_v2pub.create_contact_preference (
630 p_init_msg_list => FND_API.G_FALSE,
631 p_contact_preference_rec => l_contact_preference_record,
632 x_contact_preference_id => l_contact_preference_id,
633 x_return_status => l_return_status,
634 x_msg_count => l_msg_count,
635 x_msg_data => l_msg_data
636 );
637
638 -- set back the API name to original name
639 l_api_name := l_api_name_1;
640
641 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
642 -- Unexpected Execution Error from call to Get_contracts_resources
643 fnd_message.set_name('FND', 'FND_PII_GENERIC_API_ERROR');
644 fnd_message.set_token('P_PROC_NAME','CREATE_CONTACT_PREFERENCE');
645 fnd_message.set_token('P_API_NAME', l_api_name);
646 fnd_msg_pub.add;
647 RAISE fnd_api.g_exc_error;
648 END IF; -- end of x_return_status check
649 END IF; -- end of check of r_cont_pref record
650
651 ELSIF(p_option(i).user_option = 'O')
652 THEN
653 -- check to see if a opt-out record already exists . if not insert the record
654 r_cont_pref := null;
655 open c_cont_pref(l_party_id, p_option(i).purpose_code);
656 fetch c_cont_pref into r_cont_pref;
657 close c_cont_pref;
658
659 IF(r_cont_pref.preference_code = 'DO_NOT')
660 THEN
661 -- do nothing as OPT-IN record already exists
662 null;
663 ELSIF(r_cont_pref.preference_code = 'DO')
664 THEN
665 l_all_flag := 'Y';
666 -- delete the OPT-OUT record , in the unlikely case that it exisrs
667 delete HZ_CONTACT_PREFERENCES pref
668 where pref.contact_preference_id = r_cont_pref.contact_preference_id;
669 ELSE
670 l_all_flag := 'Y';
671 -- insert the OPT-IN record
672 --l_contact_preference_record.contact_preference_id := r_cont_pref.contact_preference_id;
673 l_contact_preference_record.contact_level_table := 'HZ_PARTIES';
674 l_contact_preference_record.contact_level_table_id := l_party_id;
675 l_contact_preference_record.contact_type := 'PRIV_PREF';
676 l_contact_preference_record.preference_code := 'DO_NOT';
677 l_contact_preference_record.preference_topic_type := 'FND_BUSINESS_PURPOSES_B';
678 --l_contact_preference_record.preference_topic_type_id := p_option(i).purpose_code;
679 l_contact_preference_record.preference_topic_type_code := p_option(i).purpose_code;
680 l_contact_preference_record.preference_start_date := trunc(sysdate);
681 l_contact_preference_record.preference_end_date := null ;
682 l_contact_preference_record.requested_by := l_requested_by;
683 l_contact_preference_record.status := 'A' ;
684 l_contact_preference_record.created_by_module := 'FND Data Privacy' ;
685 l_contact_preference_record.application_id := 0; --r_cont_pref.application_id ;
686
687
688 -- change the API Name temporarily so that in case of unexpected error
689 -- it is properly caught
690 l_api_name := l_api_name||'-CREATE_CONTACT_PREFERENCE';
691
692 hz_contact_preference_v2pub.create_contact_preference (
693 p_init_msg_list => FND_API.G_FALSE,
694 p_contact_preference_rec => l_contact_preference_record,
695 x_contact_preference_id => l_contact_preference_id,
696 x_return_status => l_return_status,
697 x_msg_count => l_msg_count,
698 x_msg_data => l_msg_data
699 );
700
701 -- set back the API name to original name
702 l_api_name := l_api_name_1;
703
704 IF NOT (l_return_status = fnd_api.g_ret_sts_success) THEN
705 -- Unexpected Execution Error from call to Get_contracts_resources
706 fnd_message.set_name('FND', 'FND_PII_GENERIC_API_ERROR');
707 fnd_message.set_token('P_PROC_NAME','CREATE_CONTACT_PREFERENCE');
708 fnd_message.set_token('P_API_NAME', l_api_name);
709 fnd_msg_pub.add;
710 RAISE fnd_api.g_exc_error;
711 END IF; -- end of x_return_status check
712 END IF; -- end of check of r_cont_pref record
713
714 END IF; -- end of p_option.user_option check
715
716 END IF; -- end of purpose_default_code check
717 -- end if;
718 END LOOP; -- end of loop for i from p_option.first to p_option.last
719
720 -- check to see the l_all_flag. If value is Y then delete the record for opt-out-of-all-purposes button switch
721 -- this flag is used to keep track that some change has been made to the user option for privacy preferences
722 IF(l_all_flag = 'Y')
723 THEN
724 delete HZ_CONTACT_PREFERENCES pref
725 WHERE pref.CONTACT_LEVEL_TABLE_ID = l_party_id
726 AND pref.CONTACT_LEVEL_TABLE = 'HZ_PARTIES'
727 AND pref.preference_topic_type = 'FND_BUSINESS_PURPOSES_B'
728 AND pref.contact_type = 'PRIV_PREF'
729 AND pref.preference_topic_type_code = 'ALL';
730
731 END IF;
732
733 END IF; -- end of l_party_id check
734
735 fnd_message.set_name ('FND', 'FND_PII_CONFIRM_SAVE');
736 FND_MSG_PUB.add;
737 FND_MSG_PUB.Count_And_Get
738 (
739 p_count => x_msg_count,
740 p_data => x_msg_data
741 );
742
743 EXCEPTION
744 WHEN FND_API.G_EXC_ERROR THEN
745 x_return_status := FND_API.G_RET_STS_ERROR ;
746 FND_MSG_PUB.Count_And_Get
747 (
748 p_count => x_msg_count,
749 p_data => x_msg_data
750 );
751
752 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
754 FND_MSG_PUB.Count_And_Get
755 (
756 p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759
760 WHEN OTHERS THEN
761 fnd_message.set_name ('FND', 'FND_PII_UNEXP_ERROR');
762 fnd_message.set_token('P_SQLCODE',SQLCODE);
763 fnd_message.set_token('P_SQLERRM',SQLERRM);
764 fnd_message.set_token('P_API_NAME',l_api_name);
765 FND_MSG_PUB.add;
766
767 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
768 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )THEN
769 FND_MSG_PUB.Add_Exc_Msg
770 (
771 G_PKG_NAME,
772 l_api_name
773 );
774 END IF;
775
776 FND_MSG_PUB.Count_And_Get
777 (
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781
782
783 END set_purpose_option;
784
785
786 END FND_UPDATE_USER_PREF_PUB;
[Home] [Help]