[Home] [Help]
PACKAGE BODY: APPS.HZ_PREFERENCE_PUB
Source
1 PACKAGE BODY HZ_PREFERENCE_PUB AS
2 /*$Header: ARHPREFB.pls 120.9 2006/01/05 17:07:59 vravicha noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_PREFERENCE_PUB';
5 G_PPREF_ID NUMBER;
6
7 -- private procedure for common validations
8 PROCEDURE validate(
9 p_party_id IN NUMBER,
10 p_category IN VARCHAR2,
11 p_preference_code IN VARCHAR2,
12 x_return_status IN OUT NOCOPY VARCHAR2
13 );
14
15 -- private procedure to denormalize preferred contact method
16 PROCEDURE denorm_pref_contact_method (
17 p_party_id IN NUMBER,
18 p_value_varchar2 IN VARCHAR2
19 );
20
21 -- private function for checking value_varchar2 presence
22 FUNCTION Contains_Value(
23 p_party_id NUMBER
24 , p_category VARCHAR2
25 , p_preference_code VARCHAR2
26 , p_value_varchar2_o VARCHAR2 := FND_API.G_MISS_CHAR
27 ) RETURN VARCHAR2
28 AS
29 l_dummy NUMBER;
30 l_return_status VARCHAR2(1);
31 BEGIN
32
33 -- call validation of the info passed
34 validate(p_party_id
35 ,p_category
36 ,p_preference_code
37 ,l_return_status
38 );
39
40 -- if the validation failed at some point, raise exception
41 if l_return_status = FND_API.G_RET_STS_ERROR then
42 RAISE FND_API.G_EXC_ERROR;
43 end if;
44
45 SELECT 1
46 INTO l_dummy
47 FROM HZ_PARTY_PREFERENCES
48 WHERE CATEGORY = p_category
49 AND PREFERENCE_CODE = p_preference_code
50 AND PARTY_ID = p_party_id
51 AND VALUE_VARCHAR2 = p_value_varchar2_o
52 AND ROWNUM = 1;
53
54 RETURN 'Y';
55
56 EXCEPTION
57 WHEN NO_DATA_FOUND THEN
58 RETURN 'N';
59
60 WHEN FND_API.G_EXC_ERROR THEN
61 RETURN FND_API.G_RET_STS_ERROR;
62
63 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
64 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
65
66 WHEN OTHERS THEN
67 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
68 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
69 FND_MSG_PUB.ADD;
70 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
71
72 END Contains_Value;
73
74
75 FUNCTION Contains_Preference(
76 p_party_id NUMBER
77 , p_category VARCHAR2
78 , p_preference_code VARCHAR2
79 ) RETURN VARCHAR2 AS
80
81 l_dummy NUMBER;
82 l_return_status VARCHAR2(1);
83
84 BEGIN
85
86 -- call validation of the info passed
87 validate(p_party_id
88 ,p_category
89 ,p_preference_code
90 ,l_return_status
91 );
92
93 -- if the validation failed at some point, raise exception
94 if l_return_status = FND_API.G_RET_STS_ERROR then
95 RAISE FND_API.G_EXC_ERROR;
96 end if;
97
98 SELECT 1
99 INTO l_dummy
100 FROM HZ_PARTY_PREFERENCES
101 WHERE CATEGORY = p_category
102 AND PREFERENCE_CODE = p_preference_code
103 AND PARTY_ID = p_party_id
104 AND ROWNUM = 1;
105
106 RETURN 'Y';
107
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 RETURN 'N';
111
112 WHEN FND_API.G_EXC_ERROR THEN
113 RETURN FND_API.G_RET_STS_ERROR;
114
115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
117
118 WHEN OTHERS THEN
119 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
120 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
121 FND_MSG_PUB.ADD;
122 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
123
124 END Contains_Preference;
125
126
127 FUNCTION Contains_Value(
128 p_party_id NUMBER
129 , p_category VARCHAR2
130 , p_preference_code VARCHAR2
131 , p_value_varchar2 VARCHAR2 := FND_API.G_MISS_CHAR
132 , p_value_number NUMBER := FND_API.G_MISS_NUM
133 , p_value_date DATE := FND_API.G_MISS_DATE
134 ) RETURN VARCHAR2 AS
135
136 l_dummy NUMBER;
137 l_value_varchar2 VARCHAR2(50);
138 l_value_number NUMBER;
139 l_value_date DATE;
140 l_ret VARCHAR2(1);
141 l_return_status VARCHAR2(1);
142
143 BEGIN
144
145 -- do additional validations
146 -- if none of the preference values passed then it is an error
147 if (p_value_varchar2 = FND_API.G_MISS_CHAR OR
148 p_value_varchar2 IS NULL) and
149 (p_value_number = FND_API.G_MISS_NUM OR
150 p_value_number IS NULL) and
151 (p_value_date = FND_API.G_MISS_DATE OR
152 p_value_date IS NULL)
153 then
154 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_PREFERENCE');
155 FND_MSG_PUB.ADD;
156 RAISE FND_API.G_EXC_ERROR;
157 end if;
158
159 -- if more than one preference values passed, then it is an error
160 if (p_value_varchar2 <> FND_API.G_MISS_CHAR AND
161 p_value_varchar2 IS NOT NULL AND
162 p_value_number <> FND_API.G_MISS_NUM AND
163 p_value_number IS NOT NULL) OR
164 (p_value_number <> FND_API.G_MISS_NUM AND
165 p_value_number IS NOT NULL AND
166 p_value_date <> FND_API.G_MISS_DATE AND
167 p_value_date IS NOT NULL) OR
168 (p_value_date <> FND_API.G_MISS_DATE AND
169 p_value_date IS NOT NULL AND
170 p_value_varchar2 <> FND_API.G_MISS_CHAR AND
171 p_value_varchar2 IS NOT NULL)
172 then
173 FND_MESSAGE.SET_NAME('AR', 'HZ_MULTIPLE_PREFERENCES');
174 FND_MSG_PUB.ADD;
175 RAISE FND_API.G_EXC_ERROR;
176 end if;
177
178 if p_value_varchar2 <> FND_API.G_MISS_CHAR and
179 p_value_varchar2 is not null then
180 l_ret := Contains_Value(p_party_id, p_category, p_preference_code, p_value_varchar2_o => p_value_varchar2);
181 end if;
182
183 if p_value_number <> FND_API.G_MISS_NUM and
184 p_value_number is not null then
185 l_ret := Contains_Value(p_party_id, p_category, p_preference_code, p_value_number);
186 end if;
187
188 if p_value_date <> FND_API.G_MISS_DATE and
189 p_value_date is not null then
190 l_ret := Contains_Value(p_party_id, p_category, p_preference_code, p_value_date);
191 end if;
192
193 RETURN l_ret;
194
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 RETURN 'N';
198
199 WHEN FND_API.G_EXC_ERROR THEN
200 RETURN FND_API.G_RET_STS_ERROR;
201
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
203 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
204
205 WHEN OTHERS THEN
206 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
207 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
208 FND_MSG_PUB.ADD;
209 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
210
211 END Contains_Value;
212
213
214 PROCEDURE Add(
215 p_party_id NUMBER
216 , p_category VARCHAR2
217 , p_preference_code VARCHAR2
218 , p_value_varchar2 VARCHAR2 := FND_API.G_MISS_CHAR
219 , p_value_number NUMBER := FND_API.G_MISS_NUM
220 , p_value_date DATE := FND_API.G_MISS_DATE
221 , p_value_name VARCHAR2 := FND_API.G_MISS_CHAR
222 , p_module VARCHAR2 := FND_API.G_MISS_CHAR
223 , p_additional_value1 VARCHAR2 := FND_API.G_MISS_CHAR
224 , p_additional_value2 VARCHAR2 := FND_API.G_MISS_CHAR
225 , p_additional_value3 VARCHAR2 := FND_API.G_MISS_CHAR
226 , p_additional_value4 VARCHAR2 := FND_API.G_MISS_CHAR
227 , p_additional_value5 VARCHAR2 := FND_API.G_MISS_CHAR
228 , x_return_status OUT NOCOPY VARCHAR2
229 , x_msg_count OUT NOCOPY NUMBER
230 , x_msg_data OUT NOCOPY VARCHAR2
231 , x_object_version_number OUT NOCOPY NUMBER
232 ) AS
233
234 cursor c_tag is
235 select tag
236 from fnd_lookup_values
237 where lookup_type = 'HZ_PREFERENCE'
238 and lookup_code = p_preference_code;
239
240 l_party_preference_id NUMBER;
241 l_rowid ROWID;
242 l_return_status VARCHAR2(1);
243 l_multiple_value_flag VARCHAR2(1);
244 l_object_version_number NUMBER := 1;
245
246 BEGIN
247
248 -- standard start of API savepoint
249 SAVEPOINT add_preference;
250
251 -- call validation of the info passed
252 validate(p_party_id
253 ,p_category
254 ,p_preference_code
255 ,l_return_status
256 );
257
258 -- if the validation failed at some point, raise exception
259 if l_return_status = FND_API.G_RET_STS_ERROR then
260 RAISE FND_API.G_EXC_ERROR;
261 end if;
262
263 -- if none of the preference values passed then it is an error
264 if (p_value_varchar2 = FND_API.G_MISS_CHAR OR
265 p_value_varchar2 IS NULL) AND
266 (p_value_number = FND_API.G_MISS_NUM OR
267 p_value_number IS NULL) AND
268 (p_value_date = FND_API.G_MISS_DATE OR
269 p_value_date IS NULL)
270 then
271 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_PREFERENCE');
272 FND_MSG_PUB.ADD;
273 RAISE FND_API.G_EXC_ERROR;
274 end if;
275
276 -- if more than one preference values passed, then it is an error
277 if (p_value_varchar2 <> FND_API.G_MISS_CHAR AND
278 p_value_varchar2 IS NOT NULL AND
279 p_value_number <> FND_API.G_MISS_NUM AND
280 p_value_number IS NOT NULL) OR
281 (p_value_number <> FND_API.G_MISS_NUM AND
282 p_value_number IS NOT NULL AND
283 p_value_date <> FND_API.G_MISS_DATE AND
284 p_value_date IS NOT NULL) OR
285 (p_value_date <> FND_API.G_MISS_DATE AND
286 p_value_date IS NOT NULL AND
287 p_value_varchar2 <> FND_API.G_MISS_CHAR AND
288 p_value_varchar2 IS NOT NULL)
289 then
290 FND_MESSAGE.SET_NAME('AR', 'HZ_MULTIPLE_PREFERENCES');
291 FND_MSG_PUB.ADD;
292 RAISE FND_API.G_EXC_ERROR;
293 end if;
294
295 -- if there is already some value set for the preference
296 -- and the preference is single-value type, then error
297 open c_tag;
298 fetch c_tag into l_multiple_value_flag;
299 close c_tag;
300
301 if l_multiple_value_flag = 'N' then -- single-value preference
302 if Contains_Preference(p_party_id,
303 p_category,
304 p_preference_code) = 'Y' then -- already one preference value set
305 FND_MESSAGE.SET_NAME('AR', 'HZ_SINGLE_VALUE_PREFERENCE');
306 FND_MSG_PUB.ADD;
307 RAISE FND_API.G_EXC_ERROR;
308 end if;
309 end if;
310
311 IF (Contains_Value(
312 p_party_id
313 , p_category
314 , p_preference_code
315 , p_value_varchar2
316 , p_value_number
317 , p_value_date) = 'N')
318 THEN
319 -- the current preference value does not exist, so create
320 -- generate the party preference id from sequence
321 select hz_party_preferences_s.nextval into l_party_preference_id from dual;
322
323 -- record the party_preference_id for integration service to call populate function
324 G_PPREF_ID := l_party_preference_id;
325
326 -- call table handler to insert preference record
327 HZ_PARTY_PREFERENCES_PKG.insert_row
328 (X_ROWID => l_rowid,
329 X_PARTY_PREFERENCE_ID => l_party_preference_id,
330 X_PARTY_ID => p_party_id,
331 X_MODULE => p_module,
332 X_CATEGORY => p_category,
333 X_PREFERENCE_CODE => p_preference_code,
334 X_VALUE_VARCHAR2 => p_value_varchar2,
335 X_VALUE_NUMBER => p_value_number,
336 X_VALUE_DATE => p_value_date,
337 X_VALUE_NAME => p_value_name,
338 X_ADDITIONAL_VALUE1 => p_additional_value1,
339 X_ADDITIONAL_VALUE2 => p_additional_value2,
340 X_ADDITIONAL_VALUE3 => p_additional_value3,
341 X_ADDITIONAL_VALUE4 => p_additional_value4,
342 X_ADDITIONAL_VALUE5 => p_additional_value5,
343 X_OBJECT_VERSION_NUMBER => l_object_version_number,
344 X_CREATED_BY => hz_utility_pub.CREATED_BY,
345 X_CREATION_DATE => hz_utility_pub.CREATION_DATE,
346 X_LAST_UPDATED_BY => hz_utility_pub.LAST_UPDATED_BY,
347 X_LAST_UPDATE_DATE => hz_utility_pub.LAST_UPDATE_DATE,
348 X_LAST_UPDATE_LOGIN => hz_utility_pub.LAST_UPDATE_LOGIN
349 );
350
351 IF p_category = 'COMMUNICATION_PREFERENCE' AND
352 p_preference_code = 'PREFERRED_CONTACT_METHOD'
353 THEN
354 denorm_pref_contact_method(p_party_id, p_value_varchar2);
355 END IF;
356 END IF;
357
358 x_return_status := FND_API.G_RET_STS_SUCCESS;
359 x_object_version_number := l_object_version_number;
360
361 FND_MSG_PUB.Count_And_Get(
362 p_encoded => FND_API.G_FALSE,
363 p_count => x_msg_count,
364 p_data => x_msg_data);
365
366 EXCEPTION
367
368 WHEN FND_API.G_EXC_ERROR THEN
369 ROLLBACK TO add_preference;
370
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 FND_MSG_PUB.Count_And_Get(
373 p_encoded => FND_API.G_FALSE,
374 p_count => x_msg_count,
375 p_data => x_msg_data);
376
377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
378 ROLLBACK TO add_preference;
379
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.Count_And_Get(
382 p_encoded => FND_API.G_FALSE,
383 p_count => x_msg_count,
384 p_data => x_msg_data);
385
386 WHEN OTHERS THEN
387 ROLLBACK TO add_preference;
388
389 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
390 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
391 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
392 FND_MSG_PUB.ADD;
393
394 FND_MSG_PUB.Count_And_Get(
395 p_encoded => FND_API.G_FALSE,
396 p_count => x_msg_count,
397 p_data => x_msg_data);
398
399 END Add;
400
401 PROCEDURE Put(
402 p_party_id NUMBER
403 , p_category VARCHAR2
404 , p_preference_code VARCHAR2
405 , p_value_varchar2 VARCHAR2 := FND_API.G_MISS_CHAR
406 , p_value_number NUMBER := FND_API.G_MISS_NUM
407 , p_value_date DATE := FND_API.G_MISS_DATE
408 , p_value_name VARCHAR2 := FND_API.G_MISS_CHAR
409 , p_module VARCHAR2 := FND_API.G_MISS_CHAR
410 , p_additional_value1 VARCHAR2 := FND_API.G_MISS_CHAR
411 , p_additional_value2 VARCHAR2 := FND_API.G_MISS_CHAR
412 , p_additional_value3 VARCHAR2 := FND_API.G_MISS_CHAR
413 , p_additional_value4 VARCHAR2 := FND_API.G_MISS_CHAR
414 , p_additional_value5 VARCHAR2 := FND_API.G_MISS_CHAR
415 , p_object_version_number IN OUT NOCOPY NUMBER
416 , x_return_status OUT NOCOPY VARCHAR2
417 , x_msg_count OUT NOCOPY NUMBER
418 , x_msg_data OUT NOCOPY VARCHAR2
419 ) AS
420
421 cursor c_tag is
422 select tag
423 from fnd_lookup_values
424 where lookup_type = 'HZ_PREFERENCE'
425 and lookup_code = p_preference_code;
426
427 l_rowid ROWID;
428 l_return_status VARCHAR2(1);
429 l_exists VARCHAR2(1);
430 l_party_preference_id NUMBER;
431 l_object_version_number NUMBER := 1;
432 o_object_version_number NUMBER;
433 l_multiple_preference_flag VARCHAR2(1);
434 l_op VARCHAR2(1);
435 l_ppref_id NUMBER;
436 BEGIN
437
438 -- standard start of API savepoint
439 SAVEPOINT put_preference;
440
441 -- call validation of the info passed
442 validate(p_party_id
443 ,p_category
444 ,p_preference_code
445 ,l_return_status
446 );
447
448 -- if the validation failed at some point, raise exception
449 if l_return_status = FND_API.G_RET_STS_ERROR then
450 RAISE FND_API.G_EXC_ERROR;
451 end if;
452
453 -- if none of the preference values passed then it is an error
454 if (p_value_varchar2 = FND_API.G_MISS_CHAR OR
455 p_value_varchar2 IS NULL) AND
456 (p_value_number = FND_API.G_MISS_NUM OR
457 p_value_number IS NULL) AND
458 (p_value_date = FND_API.G_MISS_DATE OR
459 p_value_date IS NULL)
460 then
461 FND_MESSAGE.SET_NAME('AR', 'HZ_NO_PREFERENCE');
462 FND_MSG_PUB.ADD;
463 RAISE FND_API.G_EXC_ERROR;
464 end if;
465
466 -- if more than one preference values passed, then it is an error
467 if (p_value_varchar2 <> FND_API.G_MISS_CHAR AND
468 p_value_varchar2 IS NOT NULL AND
469 p_value_number <> FND_API.G_MISS_NUM AND
470 p_value_number IS NOT NULL) OR
471 (p_value_number <> FND_API.G_MISS_NUM AND
472 p_value_number IS NOT NULL AND
473 p_value_date <> FND_API.G_MISS_DATE AND
474 p_value_date IS NOT NULL) OR
475 (p_value_date <> FND_API.G_MISS_DATE AND
476 p_value_date IS NOT NULL AND
477 p_value_varchar2 <> FND_API.G_MISS_CHAR AND
478 p_value_varchar2 IS NOT NULL)
479 then
480 FND_MESSAGE.SET_NAME('AR', 'HZ_MULTIPLE_PREFERENCES');
481 FND_MSG_PUB.ADD;
482 RAISE FND_API.G_EXC_ERROR;
483 end if;
484
485 -- check if there is already an existing preference value
486 l_exists := Contains_Value(
487 p_party_id
488 , p_category
489 , p_preference_code
490 , p_value_varchar2
491 , p_value_number
492 , p_value_date);
493
494 -- if the preference value does not exist then create one
495 -- for multiple-value preference or update single-value
496 -- preference with the new value if preference exists
497 -- else update the existing preference
498 IF l_exists = 'N'
499 THEN
500
501 -- check the multiple_value_flag for the preference
502 open c_tag;
503 fetch c_tag into l_multiple_preference_flag;
504 close c_tag;
505
506 if l_multiple_preference_flag = 'Y' then
507 -- this is multiple value preference, so create the preference value
508 -- call Add api to create the preference
509 Add(
510 p_party_id => p_party_id
511 , p_category => p_category
512 , p_preference_code => p_preference_code
513 , p_value_varchar2 => p_value_varchar2
514 , p_value_number => p_value_number
515 , p_value_date => p_value_date
516 , p_value_name => p_value_name
517 , p_module => p_module
518 , p_additional_value1 => p_additional_value1
519 , p_additional_value2 => p_additional_value2
520 , p_additional_value3 => p_additional_value3
521 , p_additional_value4 => p_additional_value4
522 , p_additional_value5 => p_additional_value5
523 , x_return_status => x_return_status
524 , x_msg_count => x_msg_count
525 , x_msg_data => x_msg_data
526 , x_object_version_number => p_object_version_number
527 );
528 else
529 -- this is a single value preference, so update the value
530 -- if preference entry exists, otherwise add a record
531 if Contains_Preference(p_party_id,
532 p_category,
533 p_preference_code) = 'Y' then -- already one preference value set
534 -- first we have to identify which record should
535 -- be updated based on what value was passed.
536 select party_preference_id, object_version_number
537 into l_party_preference_id, o_object_version_number
538 from hz_party_preferences
539 where party_id = p_party_id
540 and category = p_category
541 and preference_code = p_preference_code
542 for update nowait;
543
544 -- check if the object_version_numbers match
545 if o_object_version_number <> p_object_version_number
546 then
547 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
548 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_PREFERENCES');
549 FND_MSG_PUB.ADD;
550 RAISE FND_API.G_EXC_ERROR;
551 end if;
552 l_object_version_number := o_object_version_number + 1;
553
554 -- now call the update table handler to
555 -- update the record
556 HZ_PARTY_PREFERENCES_PKG.update_row
557 (X_PARTY_PREFERENCE_ID => l_party_preference_id
558 ,X_PARTY_ID => p_party_id
559 ,X_MODULE => p_module
560 ,X_CATEGORY => p_category
561 ,X_PREFERENCE_CODE => p_preference_code
562 ,X_VALUE_VARCHAR2 => p_value_varchar2
563 ,X_VALUE_NUMBER => p_value_number
564 ,X_VALUE_DATE => p_value_date
565 ,X_VALUE_NAME => p_value_name
566 ,X_ADDITIONAL_VALUE1 => p_additional_value1
567 ,X_ADDITIONAL_VALUE2 => p_additional_value2
568 ,X_ADDITIONAL_VALUE3 => p_additional_value3
569 ,X_ADDITIONAL_VALUE4 => p_additional_value4
570 ,X_ADDITIONAL_VALUE5 => p_additional_value5
571 ,X_OBJECT_VERSION_NUMBER => l_object_version_number
572 ,X_LAST_UPDATED_BY => hz_utility_pub.last_updated_by
573 ,X_LAST_UPDATE_DATE => hz_utility_pub.last_update_date
574 ,X_LAST_UPDATE_LOGIN => hz_utility_pub.last_update_login
575 );
576
577 IF p_category = 'COMMUNICATION_PREFERENCE' AND
578 p_preference_code = 'PREFERRED_CONTACT_METHOD'
579 THEN
580 denorm_pref_contact_method(p_party_id, p_value_varchar2);
581 END IF;
582
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584 p_object_version_number := l_object_version_number;
585 else
586 Add(
587 p_party_id => p_party_id
588 , p_category => p_category
589 , p_preference_code => p_preference_code
590 , p_value_varchar2 => p_value_varchar2
591 , p_value_number => p_value_number
592 , p_value_date => p_value_date
593 , p_value_name => p_value_name
594 , p_module => p_module
595 , p_additional_value1 => p_additional_value1
596 , p_additional_value2 => p_additional_value2
597 , p_additional_value3 => p_additional_value3
598 , p_additional_value4 => p_additional_value4
599 , p_additional_value5 => p_additional_value5
600 , x_return_status => x_return_status
601 , x_msg_count => x_msg_count
602 , x_msg_data => x_msg_data
603 , x_object_version_number => p_object_version_number
604 );
605 x_return_status := FND_API.G_RET_STS_SUCCESS;
606 end if;
607 end if;
608 ELSIF l_exists = 'Y'
609 THEN
610 -- update the preference
611 -- first we have to identify which record should
612 -- be updated based on what value was passed.
613 if p_value_varchar2 <> FND_API.G_MISS_CHAR AND
614 p_value_varchar2 IS NOT NULL
615 then
616 select party_preference_id, object_version_number
617 into l_party_preference_id, o_object_version_number
618 from hz_party_preferences
619 where party_id = p_party_id
620 and category = p_category
621 and preference_code = p_preference_code
622 and value_varchar2 = p_value_varchar2
623 for update nowait;
624 elsif p_value_number <> FND_API.G_MISS_NUM AND
625 p_value_number IS NOT NULL
626 then
627 select party_preference_id, object_version_number
628 into l_party_preference_id, o_object_version_number
629 from hz_party_preferences
630 where party_id = p_party_id
631 and category = p_category
632 and preference_code = p_preference_code
633 and value_number = p_value_number
634 for update nowait;
635 elsif p_value_date <> FND_API.G_MISS_DATE AND
636 p_value_date IS NOT NULL
637 then
638 select party_preference_id, object_version_number
639 into l_party_preference_id, o_object_version_number
640 from hz_party_preferences
641 where party_id = p_party_id
642 and category = p_category
643 and preference_code = p_preference_code
644 and value_date = p_value_date
645 for update nowait;
646 end if;
647
648 -- check if the object_version_numbers match
649 if o_object_version_number <> p_object_version_number
650 then
651 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
652 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTY_PREFERENCES');
653 FND_MSG_PUB.ADD;
654 RAISE FND_API.G_EXC_ERROR;
655 end if;
656 l_object_version_number := o_object_version_number + 1;
657
658 -- now call the update table handler to
659 -- update the record
660 HZ_PARTY_PREFERENCES_PKG.update_row
661 (X_PARTY_PREFERENCE_ID => l_party_preference_id
662 ,X_PARTY_ID => p_party_id
663 ,X_MODULE => p_module
664 ,X_CATEGORY => p_category
665 ,X_PREFERENCE_CODE => p_preference_code
666 ,X_VALUE_VARCHAR2 => p_value_varchar2
667 ,X_VALUE_NUMBER => p_value_number
668 ,X_VALUE_DATE => p_value_date
669 ,X_VALUE_NAME => p_value_name
670 ,X_ADDITIONAL_VALUE1 => p_additional_value1
671 ,X_ADDITIONAL_VALUE2 => p_additional_value2
672 ,X_ADDITIONAL_VALUE3 => p_additional_value3
673 ,X_ADDITIONAL_VALUE4 => p_additional_value4
674 ,X_ADDITIONAL_VALUE5 => p_additional_value5
675 ,X_OBJECT_VERSION_NUMBER => l_object_version_number
676 ,X_LAST_UPDATED_BY => hz_utility_pub.last_updated_by
677 ,X_LAST_UPDATE_DATE => hz_utility_pub.last_update_date
678 ,X_LAST_UPDATE_LOGIN => hz_utility_pub.last_update_login
679 );
680
681 IF p_category = 'COMMUNICATION_PREFERENCE' AND
682 p_preference_code = 'PREFERRED_CONTACT_METHOD'
683 THEN
684 denorm_pref_contact_method(p_party_id, p_value_varchar2);
685 END IF;
686
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688 p_object_version_number := l_object_version_number;
689 END IF;
690
691 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
692 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('EVENTS_ENABLED', 'BO_EVENTS_ENABLED')) THEN
693 IF(l_exists = 'Y') THEN
694 l_op := 'U';
695 l_ppref_id := l_party_preference_id;
696 ELSE
697 l_op := 'I';
698 l_ppref_id := G_PPREF_ID;
699 END IF;
700 HZ_POPULATE_BOT_PKG.pop_hz_party_preferences(
701 p_operation => l_op,
702 p_party_preference_id => l_ppref_id);
703 END IF;
704 END IF;
705
706 FND_MSG_PUB.Count_And_Get(
707 p_encoded => FND_API.G_FALSE,
708 p_count => x_msg_count,
709 p_data => x_msg_data);
710
711 EXCEPTION
712
713 WHEN FND_API.G_EXC_ERROR THEN
714 ROLLBACK TO put_preference;
715
716 x_return_status := FND_API.G_RET_STS_ERROR;
717 FND_MSG_PUB.Count_And_Get(
718 p_encoded => FND_API.G_FALSE,
719 p_count => x_msg_count,
720 p_data => x_msg_data);
721
722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
723 ROLLBACK TO put_preference;
724
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 FND_MSG_PUB.Count_And_Get(
727 p_encoded => FND_API.G_FALSE,
728 p_count => x_msg_count,
729 p_data => x_msg_data);
730
731 WHEN OTHERS THEN
732 ROLLBACK TO put_preference;
733
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
736 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
737 FND_MSG_PUB.ADD;
738
739 FND_MSG_PUB.Count_And_Get(
740 p_encoded => FND_API.G_FALSE,
741 p_count => x_msg_count,
742 p_data => x_msg_data);
743
744 END Put;
745
746 PROCEDURE Remove(
747 p_party_id NUMBER
748 , p_category VARCHAR2
749 , p_preference_code VARCHAR2
750 , p_value_varchar2 VARCHAR2 := FND_API.G_MISS_CHAR
751 , p_value_number NUMBER := FND_API.G_MISS_NUM
752 , p_value_date DATE := FND_API.G_MISS_DATE
753 , p_object_version_number NUMBER
754 , x_return_status OUT NOCOPY VARCHAR2
755 , x_msg_count OUT NOCOPY NUMBER
756 , x_msg_data OUT NOCOPY VARCHAR2
757 ) AS
758
759 l_return_status VARCHAR2(1);
760
761 BEGIN
762
763 -- standard start of API savepoint
764 SAVEPOINT remove_preference;
765
766 -- call validation of the info passed
767 validate(p_party_id
768 ,p_category
769 ,p_preference_code
770 ,l_return_status
771 );
772
773 -- if the validation failed at some point, raise exception
774 if l_return_status = FND_API.G_RET_STS_ERROR then
775 RAISE FND_API.G_EXC_ERROR;
776 end if;
777
778 x_return_status := l_return_status;
779
780 -- if more than one preference values passed, then it is an error
781 if (p_value_varchar2 <> FND_API.G_MISS_CHAR AND
782 p_value_varchar2 IS NOT NULL AND
783 p_value_number <> FND_API.G_MISS_NUM AND
784 p_value_number IS NOT NULL) OR
785 (p_value_number <> FND_API.G_MISS_NUM AND
786 p_value_number IS NOT NULL AND
787 p_value_date <> FND_API.G_MISS_DATE AND
788 p_value_date IS NOT NULL) OR
789 (p_value_date <> FND_API.G_MISS_DATE AND
790 p_value_date IS NOT NULL AND
791 p_value_varchar2 <> FND_API.G_MISS_CHAR AND
792 p_value_varchar2 IS NOT NULL)
793 then
794 FND_MESSAGE.SET_NAME('AR', 'HZ_MULTIPLE_PREFERENCES');
795 FND_MSG_PUB.ADD;
796 RAISE FND_API.G_EXC_ERROR;
797 end if;
798
799 -- if none of the values is specified, delete all
800 -- preferences for given party, category, preference_code
801 if (p_value_varchar2 = FND_API.G_MISS_CHAR OR
802 p_value_varchar2 IS NULL) AND
803 (p_value_number = FND_API.G_MISS_NUM OR
804 p_value_number IS NULL) AND
805 (p_value_date = FND_API.G_MISS_DATE OR
806 p_value_date IS NULL)
807 then
808 DELETE FROM hz_party_preferences
809 WHERE party_id = p_party_id
810 AND category = p_category
811 AND preference_code = p_preference_code;
812
813 IF p_category = 'COMMUNICATION_PREFERENCE' AND
814 p_preference_code = 'PREFERRED_CONTACT_METHOD'
815 THEN
816 denorm_pref_contact_method(p_party_id, null);
817 END IF;
818
819 elsif (p_value_varchar2 <> FND_API.G_MISS_CHAR AND
820 p_value_varchar2 IS NOT NULL) AND
821 (p_value_number = FND_API.G_MISS_NUM OR
822 p_value_number IS NULL) AND
823 (p_value_date = FND_API.G_MISS_DATE OR
824 p_value_date IS NULL)
825 then
826 DELETE FROM hz_party_preferences
827 WHERE party_id = p_party_id
828 AND category = p_category
829 AND preference_code = p_preference_code
830 AND value_varchar2 = p_value_varchar2
831 AND object_version_number = p_object_version_number;
832
833 IF p_category = 'COMMUNICATION_PREFERENCE' AND
834 p_preference_code = 'PREFERRED_CONTACT_METHOD'
835 THEN
836 denorm_pref_contact_method(p_party_id, null);
837 END IF;
838
839 elsif (p_value_varchar2 = FND_API.G_MISS_CHAR OR
840 p_value_varchar2 IS NULL) AND
841 (p_value_number <> FND_API.G_MISS_NUM AND
842 p_value_number IS NOT NULL) AND
843 (p_value_date = FND_API.G_MISS_DATE OR
844 p_value_date IS NULL)
845 then
846 DELETE FROM hz_party_preferences
847 WHERE party_id = p_party_id
848 AND category = p_category
849 AND preference_code = p_preference_code
850 AND value_number = p_value_number
851 AND object_version_number = p_object_version_number;
852
853 elsif (p_value_varchar2 = FND_API.G_MISS_CHAR OR
854 p_value_varchar2 IS NULL) AND
855 (p_value_number = FND_API.G_MISS_NUM OR
856 p_value_number IS NULL) AND
857 (p_value_date <> FND_API.G_MISS_DATE AND
858 p_value_date IS NOT NULL)
859 then
860 DELETE FROM hz_party_preferences
861 WHERE party_id = p_party_id
862 AND category = p_category
863 AND preference_code = p_preference_code
864 AND value_date = p_value_date
865 AND object_version_number = p_object_version_number;
866
867 end if;
868
869 FND_MSG_PUB.Count_And_Get(
870 p_encoded => FND_API.G_FALSE,
871 p_count => x_msg_count,
872 p_data => x_msg_data);
873
874 EXCEPTION
875
876 WHEN FND_API.G_EXC_ERROR THEN
877 ROLLBACK TO remove_preference;
878
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 FND_MSG_PUB.Count_And_Get(
881 p_encoded => FND_API.G_FALSE,
882 p_count => x_msg_count,
883 p_data => x_msg_data);
884
885 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
886 ROLLBACK TO remove_preference;
887
888 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
889 FND_MSG_PUB.Count_And_Get(
890 p_encoded => FND_API.G_FALSE,
891 p_count => x_msg_count,
892 p_data => x_msg_data);
893
894 WHEN OTHERS THEN
895 ROLLBACK TO remove_preference;
896
897 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
898 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
899 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
900 FND_MSG_PUB.ADD;
901 FND_MSG_PUB.Count_And_Get(
902 p_encoded => FND_API.G_FALSE,
903 p_count => x_msg_count,
904 p_data => x_msg_data);
905
906 END Remove;
907
908
909 PROCEDURE Retrieve(
910 p_party_id NUMBER
911 , p_category VARCHAR2
912 , p_preference_code VARCHAR2
913 , x_preference_value OUT NOCOPY ref_cursor_typ
914 , x_return_status OUT NOCOPY VARCHAR2
915 , x_msg_count OUT NOCOPY NUMBER
916 , x_msg_data OUT NOCOPY VARCHAR2
917 ) AS
918
919 l_return_status VARCHAR2(1);
920
921 BEGIN
922
923 -- call validation of the info passed
924 validate(p_party_id
925 ,p_category
926 ,p_preference_code
927 ,l_return_status
928 );
929
930 -- if the validation failed at some point, raise exception
931 if l_return_status = FND_API.G_RET_STS_ERROR then
932 RAISE FND_API.G_EXC_ERROR;
933 end if;
934
935 x_return_status := l_return_status;
936
937 OPEN x_preference_value FOR
938 SELECT *
939 FROM HZ_PARTY_PREFERENCES
940 WHERE CATEGORY = p_category
941 AND PREFERENCE_CODE = p_preference_code
942 AND PARTY_ID = p_party_id;
943
944 x_return_status := FND_API.G_RET_STS_SUCCESS;
945
946 FND_MSG_PUB.Count_And_Get(
947 p_encoded => FND_API.G_FALSE,
948 p_count => x_msg_count,
949 p_data => x_msg_data);
950
951 EXCEPTION
952
953 WHEN FND_API.G_EXC_ERROR THEN
954 x_return_status := FND_API.G_RET_STS_ERROR;
955 FND_MSG_PUB.Count_And_Get(
956 p_encoded => FND_API.G_FALSE,
957 p_count => x_msg_count,
958 p_data => x_msg_data);
959
960 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 FND_MSG_PUB.Count_And_Get(
963 p_encoded => FND_API.G_FALSE,
964 p_count => x_msg_count,
965 p_data => x_msg_data);
966
967 WHEN OTHERS THEN
968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
969 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
970 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
971 FND_MSG_PUB.ADD;
972 FND_MSG_PUB.Count_And_Get(
973 p_encoded => FND_API.G_FALSE,
974 p_count => x_msg_count,
975 p_data => x_msg_data);
976
977 END Retrieve;
978
979
980 FUNCTION Value_Varchar2(
981 p_party_id NUMBER
982 , p_category VARCHAR2
983 , p_preference_code VARCHAR2
984 ) RETURN VARCHAR2
985 AS
986 CURSOR c_pref(pty NUMBER, cat VARCHAR2, pref VARCHAR2) IS
987 SELECT VALUE_VARCHAR2
988 FROM HZ_PARTY_PREFERENCES
989 WHERE CATEGORY = cat
990 AND PREFERENCE_CODE = pref
991 AND PARTY_ID = pty
992 AND VALUE_VARCHAR2 is not null;
993
994 l_vchar2 VARCHAR2(240);
995 l_return_status VARCHAR2(1);
996
997 BEGIN
998
999 -- call validation of the info passed
1000 validate(p_party_id
1001 ,p_category
1002 ,p_preference_code
1003 ,l_return_status
1004 );
1005
1006 -- if the validation failed at some point, raise exception
1007 if l_return_status = FND_API.G_RET_STS_ERROR then
1008 RAISE FND_API.G_EXC_ERROR;
1009 end if;
1010
1011 OPEN c_pref(p_party_id, p_category, p_preference_code);
1012 FETCH c_pref into l_vchar2;
1013 IF (c_pref%NOTFOUND) THEN
1014 RETURN NULL;
1015 END IF;
1016 CLOSE c_pref;
1017
1018 RETURN l_vchar2;
1019
1020 EXCEPTION
1021 WHEN NO_DATA_FOUND THEN
1022 RETURN NULL;
1023
1024 WHEN FND_API.G_EXC_ERROR THEN
1025 RETURN NULL;
1026
1027 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1028 RETURN NULL;
1029
1030 WHEN OTHERS THEN
1031 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1032 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1033 FND_MSG_PUB.ADD;
1034 RETURN NULL;
1035
1036 END;
1037
1038
1039 FUNCTION Value_Number(
1040 p_party_id NUMBER
1041 , p_category VARCHAR2
1042 , p_preference_code VARCHAR2
1043 ) RETURN NUMBER
1044 AS
1045 CURSOR c_pref(pty NUMBER, cat VARCHAR2, pref VARCHAR2) IS
1046 SELECT VALUE_NUMBER
1047 FROM HZ_PARTY_PREFERENCES
1048 WHERE CATEGORY = cat
1049 AND PREFERENCE_CODE = pref
1050 AND PARTY_ID = pty
1051 AND VALUE_NUMBER is not null;
1052
1053 l_num NUMBER;
1054 l_return_status VARCHAR2(1);
1055 BEGIN
1056
1057 -- call validation of the info passed
1058 validate(p_party_id
1059 ,p_category
1060 ,p_preference_code
1061 ,l_return_status
1062 );
1063
1064 -- if the validation failed at some point, raise exception
1065 if l_return_status = FND_API.G_RET_STS_ERROR then
1066 RAISE FND_API.G_EXC_ERROR;
1067 end if;
1068
1069 OPEN c_pref(p_party_id, p_category, p_preference_code);
1070 FETCH c_pref into l_num;
1071 IF (c_pref%NOTFOUND) THEN
1072 RETURN NULL;
1073 END IF;
1074 CLOSE c_pref;
1075
1076 RETURN l_num;
1077
1078 EXCEPTION
1079 WHEN NO_DATA_FOUND THEN
1080 RETURN NULL;
1081
1082 WHEN FND_API.G_EXC_ERROR THEN
1083 RETURN NULL;
1084
1085 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1086 RETURN NULL;
1087
1088 WHEN OTHERS THEN
1089 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1090 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1091 FND_MSG_PUB.ADD;
1092 RETURN NULL;
1093
1094 END;
1095
1096
1097 FUNCTION Value_Date(
1098 p_party_id NUMBER
1099 , p_category VARCHAR2
1100 , p_preference_code VARCHAR2
1101 ) RETURN DATE
1102 AS
1103 CURSOR c_pref(pty NUMBER, cat VARCHAR2, pref VARCHAR2) IS
1104 SELECT VALUE_DATE
1105 FROM HZ_PARTY_PREFERENCES
1106 WHERE CATEGORY = cat
1107 AND PREFERENCE_CODE = pref
1108 AND PARTY_ID = pty
1109 AND VALUE_DATE is not null;
1110
1111 l_date DATE;
1112 l_return_status VARCHAR2(1);
1113
1114 BEGIN
1115
1116 -- call validation of the info passed
1117 validate(p_party_id
1118 ,p_category
1119 ,p_preference_code
1120 ,l_return_status
1121 );
1122
1123 -- if the validation failed at some point, raise exception
1124 if l_return_status = FND_API.G_RET_STS_ERROR then
1125 RAISE FND_API.G_EXC_ERROR;
1126 end if;
1127
1128 OPEN c_pref(p_party_id, p_category, p_preference_code);
1129 FETCH c_pref into l_date;
1130 IF (c_pref%NOTFOUND) THEN
1131 RETURN NULL;
1132 END IF;
1133 CLOSE c_pref;
1134
1135 RETURN l_date;
1136
1137 EXCEPTION
1138 WHEN NO_DATA_FOUND THEN
1139 RETURN NULL;
1140
1141 WHEN FND_API.G_EXC_ERROR THEN
1142 RETURN NULL;
1143
1144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1145 RETURN NULL;
1146
1147 WHEN OTHERS THEN
1148 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1149 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1150 FND_MSG_PUB.ADD;
1151 RETURN NULL;
1152
1153 END;
1154
1155
1156
1157 FUNCTION Contains_Value(
1158 p_party_id NUMBER
1159 , p_category VARCHAR2
1160 , p_preference_code VARCHAR2
1161 , p_value_number NUMBER --:= FND_API.G_MISS_NUM
1162 ) RETURN VARCHAR2
1163 AS
1164 l_dummy NUMBER;
1165 l_return_status VARCHAR2(1);
1166 BEGIN
1167
1168 -- call validation of the info passed
1169 validate(p_party_id
1170 ,p_category
1171 ,p_preference_code
1172 ,l_return_status
1173 );
1174
1175 -- if the validation failed at some point, raise exception
1176 if l_return_status = FND_API.G_RET_STS_ERROR then
1177 RAISE FND_API.G_EXC_ERROR;
1178 end if;
1179
1180 SELECT 1
1181 INTO l_dummy
1182 FROM HZ_PARTY_PREFERENCES
1183 WHERE CATEGORY = p_category
1184 AND PREFERENCE_CODE = p_preference_code
1185 AND PARTY_ID = p_party_id
1186 AND VALUE_NUMBER = p_value_number
1187 AND ROWNUM = 1;
1188
1189 RETURN 'Y';
1190
1191 EXCEPTION
1192 WHEN NO_DATA_FOUND THEN
1193 RETURN 'N';
1194
1195 WHEN FND_API.G_EXC_ERROR THEN
1196 RETURN FND_API.G_RET_STS_ERROR;
1197
1198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1199 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
1200
1201 WHEN OTHERS THEN
1202 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1203 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1204 FND_MSG_PUB.ADD;
1205 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
1206
1207 END Contains_Value;
1208
1209
1210
1211 FUNCTION Contains_Value(
1212 p_party_id NUMBER
1213 , p_category VARCHAR2
1214 , p_preference_code VARCHAR2
1215 , p_value_date DATE --:= FND_API.G_MISS_DATE
1216 ) RETURN VARCHAR2
1217 AS
1218 l_dummy NUMBER;
1219 l_return_status VARCHAR2(1);
1220 BEGIN
1221
1222 -- call validation of the info passed
1223 validate(p_party_id
1224 ,p_category
1225 ,p_preference_code
1226 ,l_return_status
1227 );
1228
1229 -- if the validation failed at some point, raise exception
1230 if l_return_status = FND_API.G_RET_STS_ERROR then
1231 RAISE FND_API.G_EXC_ERROR;
1232 end if;
1233
1234 SELECT 1
1235 INTO l_dummy
1236 FROM HZ_PARTY_PREFERENCES
1237 WHERE CATEGORY = p_category
1238 AND PREFERENCE_CODE = p_preference_code
1239 AND PARTY_ID = p_party_id
1240 AND VALUE_DATE = p_value_date
1241 AND ROWNUM = 1;
1242
1243 RETURN 'Y';
1244
1245 EXCEPTION
1246 WHEN NO_DATA_FOUND THEN
1247 RETURN 'N';
1248
1249 WHEN FND_API.G_EXC_ERROR THEN
1250 RETURN FND_API.G_RET_STS_ERROR;
1251
1252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1253 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
1254
1255 WHEN OTHERS THEN
1256 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1257 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1258 FND_MSG_PUB.ADD;
1259 RETURN FND_API.G_RET_STS_UNEXP_ERROR;
1260
1261 END Contains_Value;
1262
1263
1264 procedure validate(
1265 p_party_id IN NUMBER,
1266 p_category IN VARCHAR2,
1267 p_preference_code IN VARCHAR2,
1268 x_return_status IN OUT NOCOPY VARCHAR2
1269 ) IS
1270 l_dummy VARCHAR2(1);
1271 BEGIN
1272
1273 x_return_status := FND_API.G_RET_STS_SUCCESS;
1274
1275 -- check whether party id has been passed in.
1276 IF p_party_id IS NULL OR
1277 p_party_id = FND_API.G_MISS_NUM THEN
1278 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1279 FND_MESSAGE.SET_TOKEN('COLUMN', 'party id');
1280 FND_MSG_PUB.ADD;
1281 RAISE FND_API.G_EXC_ERROR;
1282 END IF;
1283
1284 -- check whether category has been passed in.
1285 IF p_category IS NULL OR
1286 p_category = FND_API.G_MISS_CHAR THEN
1287 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1288 FND_MESSAGE.SET_TOKEN('COLUMN', 'category');
1289 FND_MSG_PUB.ADD;
1290 RAISE FND_API.G_EXC_ERROR;
1291 END IF;
1292
1293 -- check whether preference code has been passed in.
1294 IF p_preference_code IS NULL OR
1295 p_preference_code = FND_API.G_MISS_CHAR THEN
1296 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
1297 FND_MESSAGE.SET_TOKEN('COLUMN', 'preference code');
1298 FND_MSG_PUB.ADD;
1299 RAISE FND_API.G_EXC_ERROR;
1300 END IF;
1301
1302 -- check if valid party_id has been passed
1303 begin
1304 select 'Y' into l_dummy
1305 from hz_parties
1306 where party_id = p_party_id;
1307 exception
1308 when no_data_found then
1309 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
1310 FND_MESSAGE.SET_TOKEN('FK', 'party id');
1311 FND_MESSAGE.SET_TOKEN('COLUMN' ,'party_id');
1312 FND_MESSAGE.SET_TOKEN('TABLE' ,'HZ_PARTIES');
1313 FND_MSG_PUB.ADD;
1314 RAISE FND_API.G_EXC_ERROR;
1315 when others then
1316 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1317 end;
1318
1319 -- check if valid preference code has been passed
1320 hz_common_pub.validate_lookup(
1321 p_lookup_type => 'HZ_PREFERENCE',
1322 p_column => 'preference code',
1323 p_column_value => p_preference_code,
1324 x_return_status => x_return_status);
1325
1326 if x_return_status = FND_API.G_RET_STS_ERROR then
1327 RAISE FND_API.G_EXC_ERROR;
1328 end if;
1329
1330 EXCEPTION
1331
1332 WHEN FND_API.G_EXC_ERROR THEN
1333 x_return_status := FND_API.G_RET_STS_ERROR;
1334
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337
1338 WHEN OTHERS THEN
1339 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1340 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1341 FND_MSG_PUB.ADD;
1342
1343 END validate;
1344
1345 PROCEDURE denorm_pref_contact_method (
1346 p_party_id IN NUMBER,
1347 p_value_varchar2 IN VARCHAR2
1348 ) IS
1349
1350 CURSOR c_party IS
1351 SELECT 'Y'
1352 FROM hz_parties
1353 WHERE party_id = p_party_id
1354 FOR UPDATE NOWAIT;
1355
1356 l_exists VARCHAR2(1);
1357
1358 BEGIN
1359
1360 --check if party record is locked by any one else.
1361 BEGIN
1362 OPEN c_party;
1363 FETCH c_party INTO l_exists;
1364 CLOSE c_party;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
1368 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_PARTIES');
1369 FND_MSG_PUB.ADD;
1370 RAISE FND_API.G_EXC_ERROR;
1371 END;
1372
1373 UPDATE hz_parties
1374 SET preferred_contact_method = p_value_varchar2
1375 WHERE party_id = p_party_id;
1376
1377 END denorm_pref_contact_method;
1378
1379 END HZ_PREFERENCE_PUB;