[Home] [Help]
PACKAGE BODY: APPS.HZ_CONTACT_PREFERENCE_BO_PVT
Source
1 PACKAGE BODY hz_contact_preference_bo_pvt AS
2 /*$Header: ARHBCTVB.pls 120.6 2006/05/18 22:25:22 acng noship $ */
3
4 -- PRIVATE PROCEDURE assign_contact_pref_rec
5 --
6 -- DESCRIPTION
7 -- Assign attributes from contact preference object to plsql record.
8 --
9 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
10 --
11 -- ARGUMENTS
12 -- IN:
13 -- p_contact_pref_obj Contact preference object.
14 -- p_contact_level_table_id Contact level table Id.
15 -- p_contact_level_table Contact level table.
16 -- IN/OUT:
17 -- px_contact_pref_rec Contact preference plsql record.
18 --
19 -- NOTES
20 --
21 -- MODIFICATION HISTORY
22 --
23 -- 14-DEC-2004 Arnold Ng Created.
24 --
25
26 PROCEDURE assign_contact_pref_rec(
27 p_contact_pref_obj IN HZ_CONTACT_PREF_OBJ,
28 p_contact_level_table_id IN NUMBER,
29 p_contact_level_table IN VARCHAR2,
30 px_contact_pref_rec IN OUT NOCOPY HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE
31 );
32
33 -- PROCEDURE create_contact_preferences
34 --
35 -- DESCRIPTION
36 -- Create contact preferences.
37 --
38 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
39 --
40 -- ARGUMENTS
41 -- IN:
42 -- p_cp_pref_objs List of contact preference objects.
43 -- p_contact_level_table_id Contact level table Id.
44 -- p_contact_level_table Contact level table.
45 -- OUT:
46 -- x_return_status Return status after the call. The status can
47 -- be fnd_api.g_ret_sts_success (success),
48 -- fnd_api.g_ret_sts_error (error),
49 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
50 -- x_msg_count Number of messages in message stack.
51 -- x_msg_data Message text if x_msg_count is 1.
52 --
53 -- NOTES
54 --
55 -- MODIFICATION HISTORY
56 --
57 -- 14-DEC-2004 Arnold Ng Created.
58 --
59
60 PROCEDURE create_contact_preferences(
61 p_cp_pref_objs IN OUT NOCOPY HZ_CONTACT_PREF_OBJ_TBL,
62 p_contact_level_table_id IN NUMBER,
63 p_contact_level_table IN VARCHAR2,
64 x_return_status OUT NOCOPY VARCHAR2,
65 x_msg_count OUT NOCOPY NUMBER,
66 x_msg_data OUT NOCOPY VARCHAR2
67 ) IS
68 l_debug_prefix VARCHAR2(30);
69 l_contact_pref_id NUMBER;
70 l_contact_pref_rec HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE;
71 BEGIN
72 -- Standard start of API savepoint
73 SAVEPOINT create_contact_preferences_pvt;
74
75 -- initialize API return status to success.
76 x_return_status := FND_API.G_RET_STS_SUCCESS;
77
78 -- Debug info.
79 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
80 hz_utility_v2pub.debug(p_message=>'create_contact_preferences(+)',
81 p_prefix=>l_debug_prefix,
82 p_msg_level=>fnd_log.level_procedure);
83 END IF;
84
85 -- Create contact preferences
86 FOR i IN 1..p_cp_pref_objs.COUNT LOOP
87 assign_contact_pref_rec(
88 p_contact_pref_obj => p_cp_pref_objs(i),
89 p_contact_level_table_id => p_contact_level_table_id,
90 p_contact_level_table => p_contact_level_table,
91 px_contact_pref_rec => l_contact_pref_rec
92 );
93
94 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
95 p_contact_preference_rec => l_contact_pref_rec,
96 x_contact_preference_id => l_contact_pref_id,
97 x_return_status => x_return_status,
98 x_msg_count => x_msg_count,
99 x_msg_data => x_msg_data
100 );
101
102 -- If error happen, push message into stack, raise exception out of the loop.
103 -- Reason is that we want to capture as manay as we can.
104 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
105 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
106 hz_utility_v2pub.debug(p_message=>'Error occurred at hz_contact_preference_bo_pvt.create_contact_preferences: contact level table and id: '||p_contact_level_table||' '||p_contact_level_table_id,
107 p_prefix=>l_debug_prefix,
108 p_msg_level=>fnd_log.level_procedure);
109 END IF;
110 RAISE FND_API.G_EXC_ERROR;
111 END IF;
112
113 -- assign contact_preference_id
114 p_cp_pref_objs(i).contact_preference_id := l_contact_pref_id;
115 END LOOP;
116
117 -- Debug info.
118 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
119 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
120 p_msg_data=>x_msg_data,
121 p_msg_type=>'WARNING',
122 p_msg_level=>fnd_log.level_exception);
123 END IF;
124 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
125 hz_utility_v2pub.debug(p_message=>'create_contact_preferences(-)',
126 p_prefix=>l_debug_prefix,
127 p_msg_level=>fnd_log.level_procedure);
128 END IF;
129 EXCEPTION
130 WHEN fnd_api.g_exc_error THEN
131 ROLLBACK TO create_contact_preferences_pvt;
132 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
133 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
134 FND_MSG_PUB.ADD;
135
136 x_return_status := fnd_api.g_ret_sts_error;
137
138 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
139 p_count => x_msg_count,
140 p_data => x_msg_data);
141
142 -- Debug info.
143 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
144 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
145 p_msg_data=>x_msg_data,
146 p_msg_type=>'ERROR',
147 p_msg_level=>fnd_log.level_error);
148 END IF;
149 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
150 hz_utility_v2pub.debug(p_message=>'create_contact_preferences(-)',
151 p_prefix=>l_debug_prefix,
152 p_msg_level=>fnd_log.level_procedure);
153 END IF;
154
155 WHEN fnd_api.g_exc_unexpected_error THEN
156 ROLLBACK TO create_contact_preferences_pvt;
157 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
158 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
159 FND_MSG_PUB.ADD;
160
161 x_return_status := fnd_api.g_ret_sts_unexp_error;
162
163 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
164 p_count => x_msg_count,
165 p_data => x_msg_data);
166
167 -- Debug info.
168 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
169 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
170 p_msg_data=>x_msg_data,
171 p_msg_type=>'UNEXPECTED ERROR',
172 p_msg_level=>fnd_log.level_error);
173 END IF;
174 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
175 hz_utility_v2pub.debug(p_message=>'create_contact_preferences(-)',
176 p_prefix=>l_debug_prefix,
177 p_msg_level=>fnd_log.level_procedure);
178 END IF;
179
180 WHEN OTHERS THEN
181 ROLLBACK TO create_contact_preferences_pvt;
182 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
183 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
184 FND_MSG_PUB.ADD;
185
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187
188 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
189 fnd_message.set_token('ERROR' ,SQLERRM);
190 fnd_msg_pub.add;
191
192 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
193 p_count => x_msg_count,
194 p_data => x_msg_data);
195
196 -- Debug info.
197 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
198 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
199 p_msg_data=>x_msg_data,
200 p_msg_type=>'SQL ERROR',
201 p_msg_level=>fnd_log.level_error);
202 END IF;
203 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
204 hz_utility_v2pub.debug(p_message=>'create_contact_preferences(-)',
205 p_prefix=>l_debug_prefix,
206 p_msg_level=>fnd_log.level_procedure);
207 END IF;
208 END create_contact_preferences;
209
210 -- PROCEDURE save_contact_preferences
211 --
212 -- DESCRIPTION
213 -- Create or update contact preferences.
214 --
215 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
216 --
217 -- ARGUMENTS
218 -- IN:
219 -- p_cp_pref_objs List of contact preference objects.
220 -- p_contact_level_table_id Contact level table Id.
221 -- p_contact_level_table Contact level table.
222 -- OUT:
223 -- x_return_status Return status after the call. The status can
224 -- be fnd_api.g_ret_sts_success (success),
225 -- fnd_api.g_ret_sts_error (error),
226 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
227 -- x_msg_count Number of messages in message stack.
228 -- x_msg_data Message text if x_msg_count is 1.
229 --
230 -- NOTES
231 --
232 -- MODIFICATION HISTORY
233 --
234 -- 14-DEC-2004 Arnold Ng Created.
235 --
236
237 PROCEDURE save_contact_preferences(
238 p_cp_pref_objs IN OUT NOCOPY HZ_CONTACT_PREF_OBJ_TBL,
239 p_contact_level_table_id IN NUMBER,
240 p_contact_level_table IN VARCHAR2,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_msg_count OUT NOCOPY NUMBER,
243 x_msg_data OUT NOCOPY VARCHAR2
244 ) IS
245 CURSOR get_contact_pref_id(l_contact_point_id NUMBER, l_contact_type VARCHAR2,
246 l_preference_code VARCHAR2, l_preference_start_date DATE,
247 l_preference_end_date DATE)IS
248 SELECT contact_preference_id
249 FROM HZ_CONTACT_PREFERENCES
250 WHERE contact_level_table_id = l_contact_point_id
251 AND contact_type = l_contact_type
252 AND preference_code = l_preference_code
253 AND trunc(preference_start_date) = trunc(l_preference_start_date)
254 AND trunc(nvl(preference_start_date,sysdate)) = trunc(nvl(l_preference_start_date,sysdate))
255 AND rownum = 1;
256
257 l_debug_prefix VARCHAR2(30);
258 l_contact_pref_id NUMBER;
259 l_contact_pref_rec HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE;
260 l_ovn NUMBER := NULL;
261 BEGIN
262 -- Standard start of API savepoint
263 SAVEPOINT save_contact_preferences_pvt;
264
265 -- initialize API return status to success.
266 x_return_status := FND_API.G_RET_STS_SUCCESS;
267
268 -- Debug info.
269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
270 hz_utility_v2pub.debug(p_message=>'save_contact_preferences(+)',
271 p_prefix=>l_debug_prefix,
272 p_msg_level=>fnd_log.level_procedure);
273 END IF;
274
275 -- Create/Update contact preferences
276 FOR i IN 1..p_cp_pref_objs.COUNT LOOP
277 assign_contact_pref_rec(
278 p_contact_pref_obj => p_cp_pref_objs(i),
279 p_contact_level_table_id => p_contact_level_table_id,
280 p_contact_level_table => p_contact_level_table,
281 px_contact_pref_rec => l_contact_pref_rec
282 );
283
284 -- check if the contact pref record is create or update
285 hz_registry_validate_bo_pvt.check_contact_pref_op(
286 p_contact_level_table_id => p_contact_level_table_id,
287 p_contact_level_table => p_contact_level_table,
288 px_contact_pref_id => l_contact_pref_rec.contact_preference_id,
289 p_contact_type => l_contact_pref_rec.contact_type,
290 p_preference_code => l_contact_pref_rec.preference_code,
291 p_preference_start_date => l_contact_pref_rec.preference_start_date,
292 p_preference_end_date => l_contact_pref_rec.preference_end_date,
293 x_object_version_number => l_ovn
294 );
295
296 IF(l_ovn = -1) THEN
297 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
298 hz_utility_v2pub.debug(p_message=>'Error occurred at hz_contact_preference_bo_pvt.check_contact_pref_op: contact level table and id: '||p_contact_level_table||' '||p_contact_level_table_id,
299 p_prefix=>l_debug_prefix,
300 p_msg_level=>fnd_log.level_procedure);
301 END IF;
302 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_ID');
303 FND_MSG_PUB.ADD;
304 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
305 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
306 FND_MSG_PUB.ADD;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309
310 IF(l_ovn IS NULL) THEN
311 HZ_CONTACT_PREFERENCE_V2PUB.create_contact_preference(
312 p_contact_preference_rec => l_contact_pref_rec,
313 x_contact_preference_id => l_contact_pref_id,
314 x_return_status => x_return_status,
315 x_msg_count => x_msg_count,
316 x_msg_data => x_msg_data
317 );
318
319 -- assign contact_preference_id
320 p_cp_pref_objs(i).contact_preference_id := l_contact_pref_id;
321 ELSE
322 -- clean up created_by_module during update
323 l_contact_pref_rec.created_by_module := NULL;
324 HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference(
325 p_contact_preference_rec => l_contact_pref_rec,
326 p_object_version_number => l_ovn,
327 x_return_status => x_return_status,
328 x_msg_count => x_msg_count,
329 x_msg_data => x_msg_data
330 );
331
332 -- assign contact_preference_id
333 p_cp_pref_objs(i).contact_preference_id := l_contact_pref_rec.contact_preference_id;
334 END IF;
335
336 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
337 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
338 hz_utility_v2pub.debug(p_message=>'Error occurred at hz_contact_preference_bo_pvt.save_contact_preferences: contact level table and id: '||p_contact_level_table||' '||p_contact_level_table_id,
339 p_prefix=>l_debug_prefix,
340 p_msg_level=>fnd_log.level_procedure);
341 END IF;
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344 END LOOP;
345
346 -- Debug info.
347 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
348 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
349 p_msg_data=>x_msg_data,
350 p_msg_type=>'WARNING',
351 p_msg_level=>fnd_log.level_exception);
352 END IF;
353 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
357 END IF;
354 hz_utility_v2pub.debug(p_message=>'save_contact_preferences(-)',
355 p_prefix=>l_debug_prefix,
356 p_msg_level=>fnd_log.level_procedure);
358 EXCEPTION
359 WHEN fnd_api.g_exc_error THEN
360 ROLLBACK TO save_contact_preferences_pvt;
361 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
362 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
363 FND_MSG_PUB.ADD;
364
365 x_return_status := fnd_api.g_ret_sts_error;
366
367 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
368 p_count => x_msg_count,
369 p_data => x_msg_data);
370
371 -- Debug info.
372 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
373 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
374 p_msg_data=>x_msg_data,
375 p_msg_type=>'ERROR',
376 p_msg_level=>fnd_log.level_error);
377 END IF;
378 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
379 hz_utility_v2pub.debug(p_message=>'save_contact_preferences(-)',
380 p_prefix=>l_debug_prefix,
381 p_msg_level=>fnd_log.level_procedure);
382 END IF;
383
384 WHEN fnd_api.g_exc_unexpected_error THEN
385 ROLLBACK TO save_contact_preferences_pvt;
386 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
387 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
388 FND_MSG_PUB.ADD;
389
390 x_return_status := fnd_api.g_ret_sts_unexp_error;
391
392 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
393 p_count => x_msg_count,
394 p_data => x_msg_data);
395
396 -- Debug info.
397 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
398 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
399 p_msg_data=>x_msg_data,
400 p_msg_type=>'UNEXPECTED ERROR',
401 p_msg_level=>fnd_log.level_error);
402 END IF;
403 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
404 hz_utility_v2pub.debug(p_message=>'save_contact_preferences(-)',
405 p_prefix=>l_debug_prefix,
406 p_msg_level=>fnd_log.level_procedure);
407 END IF;
408
409 WHEN OTHERS THEN
410 ROLLBACK TO save_contact_preferences_pvt;
411 FND_MESSAGE.SET_NAME('AR', 'HZ_API_PROPAGATE_ENTITY_ERROR');
412 FND_MESSAGE.SET_TOKEN('ENTITY', 'HZ_CONTACT_PREFERENCES');
413 FND_MSG_PUB.ADD;
414
415 x_return_status := fnd_api.g_ret_sts_unexp_error;
416
417 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
418 fnd_message.set_token('ERROR' ,SQLERRM);
419 fnd_msg_pub.add;
420
421 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
422 p_count => x_msg_count,
423 p_data => x_msg_data);
424
425 -- Debug info.
426 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
427 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
428 p_msg_data=>x_msg_data,
429 p_msg_type=>'SQL ERROR',
430 p_msg_level=>fnd_log.level_error);
431 END IF;
432 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
433 hz_utility_v2pub.debug(p_message=>'save_contact_preferences(-)',
434 p_prefix=>l_debug_prefix,
435 p_msg_level=>fnd_log.level_procedure);
436 END IF;
437 END save_contact_preferences;
438
439 -- PRIVATE PROCEDURE assign_contact_pref_rec
440 --
441 -- DESCRIPTION
442 -- Assign attributes from contact preference object to plsql record.
443 --
444 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
445 --
446 -- ARGUMENTS
447 -- IN:
448 -- p_contact_pref_obj Contact preference object.
449 -- p_contact_level_table_id Contact level table Id.
450 -- p_contact_level_table Contact level table.
451 -- IN/OUT:
452 -- px_contact_pref_rec Contact preference plsql record.
453 --
454 -- NOTES
455 --
456 -- MODIFICATION HISTORY
457 --
458 -- 14-DEC-2004 Arnold Ng Created.
459 --
460
461 PROCEDURE assign_contact_pref_rec(
462 p_contact_pref_obj IN HZ_CONTACT_PREF_OBJ,
463 p_contact_level_table_id IN NUMBER,
464 p_contact_level_table IN VARCHAR2,
465 px_contact_pref_rec IN OUT NOCOPY HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE
466 ) IS
467 BEGIN
468 px_contact_pref_rec.contact_preference_id := p_contact_pref_obj.contact_preference_id;
469 px_contact_pref_rec.contact_level_table := p_contact_level_table;
470 px_contact_pref_rec.contact_level_table_id := p_contact_level_table_id;
471 px_contact_pref_rec.contact_type := p_contact_pref_obj.contact_type;
472 px_contact_pref_rec.preference_code := p_contact_pref_obj.preference_code;
473 px_contact_pref_rec.preference_topic_type := p_contact_pref_obj.preference_topic_type;
474 px_contact_pref_rec.preference_topic_type_id := p_contact_pref_obj.preference_topic_type_id;
475 px_contact_pref_rec.preference_topic_type_code := p_contact_pref_obj.preference_topic_type_code;
476 px_contact_pref_rec.preference_start_date := p_contact_pref_obj.preference_start_date;
477 px_contact_pref_rec.preference_end_date := p_contact_pref_obj.preference_end_date;
481 px_contact_pref_rec.preference_end_time_mi := p_contact_pref_obj.preference_end_time_mi;
478 px_contact_pref_rec.preference_start_time_hr := p_contact_pref_obj.preference_start_time_hr;
479 px_contact_pref_rec.preference_end_time_hr := p_contact_pref_obj.preference_end_time_hr;
480 px_contact_pref_rec.preference_start_time_mi := p_contact_pref_obj.preference_start_time_mi;
482 px_contact_pref_rec.max_no_of_interactions := p_contact_pref_obj.max_no_of_interactions;
483 px_contact_pref_rec.max_no_of_interact_uom_code := p_contact_pref_obj.max_no_of_interact_uom_code;
484 px_contact_pref_rec.requested_by := p_contact_pref_obj.requested_by;
485 px_contact_pref_rec.reason_code := p_contact_pref_obj.reason_code;
486 IF(p_contact_pref_obj.status in ('A','I')) THEN
487 px_contact_pref_rec.status := p_contact_pref_obj.status;
488 ELSE
489 px_contact_pref_rec.status := null;
490 END IF;
491 px_contact_pref_rec.created_by_module := HZ_UTILITY_V2PUB.G_CREATED_BY_MODULE;
492 END assign_contact_pref_rec;
493
494 END hz_contact_preference_bo_pvt;