[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_CONT_POINT_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_CONT_POINT_BO_PVT AS
2 /*$Header: ARHECPVB.pls 120.5 2006/06/13 20:28:01 acng noship $ */
3 /*
4 * This package contains the private APIs for logical phone.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname phone
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf phone Get APIs
11 */
12
13
14 /*
15 The Get Contact Point API Procedures are retrieval services that return a full Contact Point business object of the type specified.
16 The user identifies a particular Contact Point business object using the TCA identifier and/or the objects Source System information.
17 Upon proper validation of the object, the full Contact Point business object is returned. The object consists of all data included
18 within the Contact Point business object, at all embedded levels. This includes the set of all data stored in the TCA tables for
19 each embedded entity.
20
21 To retrieve the appropriate embedded entities within the Contact Point business objects, the Get procedure returns all records for
22 the particular object from these TCA entity tables.
23
24 Embedded BO Mandatory Multiple Logical API Procedure Comments
25
26 Contact Point Y N HZ_CONTACT_POINTS
27 Contact Preference N Y HZ_CONTACT_PREFERENCES
28
29 */
30
31
32 --------------------------------------
33 --
34 -- PROCEDURE get_phone_bos
35 --
36 -- DESCRIPTION
37 -- Get logical phones.
38 --
39 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
40 --
41 -- ARGUMENTS
42 -- IN:
43 -- p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
44 -- p_phone_id phone ID. If this id is passed in, return only one obj.
45 --
46 -- p_parent_id parent_id
47 -- p_parent_table_name parent_table name
48 -- OUT:
49 -- x_phone_obj Logical phone record.
50 -- x_return_status Return status after the call. The status can
51 -- be fnd_api.g_ret_sts_success (success),
52 -- fnd_api.g_ret_sts_error (error),
53 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
54 -- x_msg_count Number of messages in message stack.
55 -- x_msg_data Message text if x_msg_count is 1.
56 --
57 -- NOTES
58 --
59 -- MODIFICATION HISTORY
60 --
61 --
62 -- 30-May-2005 AWU Created.
63 --
64
65
66 PROCEDURE get_phone_bos(
67 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
68 p_phone_id IN NUMBER,
69 p_parent_id IN NUMBER,
70 p_parent_table_name IN VARCHAR2,
71 p_action_type IN VARCHAR2 := NULL,
72 x_phone_objs OUT NOCOPY HZ_PHONE_CP_BO_TBL,
73 x_return_status OUT NOCOPY VARCHAR2,
74 x_msg_count OUT NOCOPY NUMBER,
75 x_msg_data OUT NOCOPY VARCHAR2
76 ) is
77
78
79 cursor c1 is
80 SELECT HZ_PHONE_CP_BO(
81 P_ACTION_TYPE,
82 NULL, -- COMMON_OBJ_ID
83 CONTACT_POINT_ID,
84 NULL, -- ORIG_SYSTEM,
85 NULL, -- ORIG_SYSTEM_REFERENCE,
86 STATUS,
87 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
88 OWNER_TABLE_ID,
89 PRIMARY_FLAG,
90 ATTRIBUTE_CATEGORY,
91 ATTRIBUTE1,
92 ATTRIBUTE2,
93 ATTRIBUTE3,
94 ATTRIBUTE4,
95 ATTRIBUTE5,
96 ATTRIBUTE6,
97 ATTRIBUTE7,
98 ATTRIBUTE8,
99 ATTRIBUTE9,
100 ATTRIBUTE10,
101 ATTRIBUTE11,
102 ATTRIBUTE12,
103 ATTRIBUTE13,
104 ATTRIBUTE14,
105 ATTRIBUTE15,
106 ATTRIBUTE16,
107 ATTRIBUTE17,
108 ATTRIBUTE18,
109 ATTRIBUTE19,
110 ATTRIBUTE20,
111 CONTACT_POINT_PURPOSE,
112 PRIMARY_BY_PURPOSE,
113 PROGRAM_UPDATE_DATE,
114 CREATED_BY_MODULE,
115 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
116 CREATION_DATE,
117 LAST_UPDATE_DATE,
118 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
119 ACTUAL_CONTENT_SOURCE,
120 PHONE_CALLING_CALENDAR,
121 LAST_CONTACT_DT_TIME,
122 TIMEZONE_ID,
123 PHONE_AREA_CODE,
124 PHONE_COUNTRY_CODE,
125 PHONE_NUMBER,
126 PHONE_EXTENSION,
127 PHONE_LINE_TYPE,
128 RAW_PHONE_NUMBER,
129 CAST(MULTISET (
130 SELECT HZ_ORIG_SYS_REF_OBJ(
131 NULL, --P_ACTION_TYPE,
132 ORIG_SYSTEM_REF_ID,
133 ORIG_SYSTEM,
134 ORIG_SYSTEM_REFERENCE,
135 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
136 OWNER_TABLE_ID,
137 STATUS,
138 REASON_CODE,
139 OLD_ORIG_SYSTEM_REFERENCE,
140 START_DATE_ACTIVE,
141 END_DATE_ACTIVE,
142 PROGRAM_UPDATE_DATE,
143 CREATED_BY_MODULE,
144 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
145 CREATION_DATE,
146 LAST_UPDATE_DATE,
147 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
148 ATTRIBUTE_CATEGORY,
149 ATTRIBUTE1,
150 ATTRIBUTE2,
151 ATTRIBUTE3,
152 ATTRIBUTE4,
153 ATTRIBUTE5,
154 ATTRIBUTE6,
155 ATTRIBUTE7,
156 ATTRIBUTE8,
157 ATTRIBUTE9,
158 ATTRIBUTE10,
159 ATTRIBUTE11,
160 ATTRIBUTE12,
161 ATTRIBUTE13,
162 ATTRIBUTE14,
163 ATTRIBUTE15,
164 ATTRIBUTE16,
165 ATTRIBUTE17,
166 ATTRIBUTE18,
167 ATTRIBUTE19,
168 ATTRIBUTE20)
169 FROM HZ_ORIG_SYS_REFERENCES OSR
170 WHERE
171 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
172 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
173 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
174 CAST(MULTISET (
175 SELECT HZ_CONTACT_PREF_OBJ(
176 P_ACTION_TYPE,
177 NULL, -- COMMON_OBJ_ID
178 CONTACT_PREFERENCE_ID,
179 'PHONE',
180 CONTACT_LEVEL_TABLE_ID,
181 CONTACT_TYPE,
182 PREFERENCE_CODE,
183 PREFERENCE_TOPIC_TYPE,
184 PREFERENCE_TOPIC_TYPE_ID,
185 PREFERENCE_TOPIC_TYPE_CODE,
186 PREFERENCE_START_DATE,
187 PREFERENCE_END_DATE,
188 PREFERENCE_START_TIME_HR,
189 PREFERENCE_END_TIME_HR,
190 PREFERENCE_START_TIME_MI,
191 PREFERENCE_END_TIME_MI,
192 MAX_NO_OF_INTERACTIONS,
193 MAX_NO_OF_INTERACT_UOM_CODE,
194 REQUESTED_BY,
195 REASON_CODE,
196 STATUS,
197 PROGRAM_UPDATE_DATE,
198 CREATED_BY_MODULE,
199 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
200 CREATION_DATE,
201 LAST_UPDATE_DATE,
202 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
203 FROM HZ_CONTACT_PREFERENCES CPREF
204 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
205 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
206 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'PHONE'
207 AND ((P_PHONE_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
208 AND OWNER_TABLE_ID = P_PARENT_ID)
209 OR (P_PHONE_ID IS NOT NULL AND CONTACT_POINT_ID = P_PHONE_ID));
210
211 l_debug_prefix VARCHAR2(30) := '';
212
213 BEGIN
214
215
216 -- initialize API return status to success.
217 x_return_status := FND_API.G_RET_STS_SUCCESS;
218
219 -- Initialize message list if p_init_msg_list is set to TRUE
220 IF FND_API.to_Boolean(p_init_msg_list) THEN
221 FND_MSG_PUB.initialize;
222 END IF;
223
224
225 -- Debug info.
226 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
227 hz_utility_v2pub.debug(p_message=>'get_phone_bos(+)',
228 p_prefix=>l_debug_prefix,
229 p_msg_level=>fnd_log.level_procedure);
230 END IF;
231
232 x_phone_objs := HZ_PHONE_CP_BO_TBL();
233 open c1;
234 fetch c1 BULK COLLECT into x_phone_objs;
235 close c1;
236
237 -- Debug info.
238 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
239 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
240 p_msg_data=>x_msg_data,
241 p_msg_type=>'WARNING',
242 p_msg_level=>fnd_log.level_exception);
243 END IF;
244
245 -- Debug info.
246 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
247 hz_utility_v2pub.debug(p_message=>'get_phone_bos (-)',
248 p_prefix=>l_debug_prefix,
249 p_msg_level=>fnd_log.level_procedure);
250 END IF;
251
252
253 EXCEPTION
254
255 WHEN fnd_api.g_exc_error THEN
256 x_return_status := fnd_api.g_ret_sts_error;
257
258 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
259 p_count => x_msg_count,
260 p_data => x_msg_data);
261
262 -- Debug info.
263 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
264 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
265 p_msg_data=>x_msg_data,
266 p_msg_type=>'ERROR',
267 p_msg_level=>fnd_log.level_error);
268 END IF;
269 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
270 hz_utility_v2pub.debug(p_message=>'get_phone_bos (-)',
271 p_prefix=>l_debug_prefix,
272 p_msg_level=>fnd_log.level_procedure);
273 END IF;
274 WHEN fnd_api.g_exc_unexpected_error THEN
275 x_return_status := fnd_api.g_ret_sts_unexp_error;
276
277 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
278 p_count => x_msg_count,
279 p_data => x_msg_data);
280
281 -- Debug info.
282 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
283 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
284 p_msg_data=>x_msg_data,
285 p_msg_type=>'UNEXPECTED ERROR',
286 p_msg_level=>fnd_log.level_error);
287 END IF;
288 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
289 hz_utility_v2pub.debug(p_message=>'get_phone_bos (-)',
290 p_prefix=>l_debug_prefix,
291 p_msg_level=>fnd_log.level_procedure);
292 END IF;
293 WHEN OTHERS THEN
294 x_return_status := fnd_api.g_ret_sts_unexp_error;
295
296 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
297 fnd_message.set_token('ERROR' ,SQLERRM);
298 fnd_msg_pub.add;
299
300 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
301 p_count => x_msg_count,
302 p_data => x_msg_data);
303
304 -- Debug info.
305 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
306 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
307 p_msg_data=>x_msg_data,
308 p_msg_type=>'SQL ERROR',
309 p_msg_level=>fnd_log.level_error);
310 END IF;
311 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
312 hz_utility_v2pub.debug(p_message=>'get_phone_bos (-)',
313 p_prefix=>l_debug_prefix,
314 p_msg_level=>fnd_log.level_procedure);
315 END IF;
316
317 end;
318
319
320
321
322 --------------------------------------
323 --
324 -- PROCEDURE get_telex_bos
325 --
326 -- DESCRIPTION
327 -- Get a logical telex.
328 --
329 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
330 --
331 -- ARGUMENTS
332 -- IN:
333 -- p_init_msg_list Initialize message stack if it is set to
334 -- p_telex_id telex ID. If this id is passed in, return only one obj.
335 -- p_parent_id parent_id
336 -- p_parent_table_name parent_table name
337
338 -- OUT:
339 -- x_telex_objs Logical telex record.
340 -- x_return_status Return status after the call. The status can
341 -- be fnd_api.g_ret_sts_success (success),
342 -- fnd_api.g_ret_sts_error (error),
343 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
344 -- x_msg_count Number of messages in message stack.
345 -- x_msg_data Message text if x_msg_count is 1.
346 --
347 -- NOTES
348 --
349 -- MODIFICATION HISTORY
350 --
351 --
352 -- 30-May-2005 AWU Created.
353 --
354
355
356
357 PROCEDURE get_telex_bos(
358 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
359 p_telex_id IN NUMBER,
360 p_parent_id IN NUMBER,
361 p_parent_table_name IN VARCHAR2,
362 p_action_type IN VARCHAR2 := NULL,
363 x_telex_objs OUT NOCOPY HZ_TELEX_CP_BO_TBL,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2
367 ) is
368
369 cursor c1 is
370 SELECT HZ_TELEX_CP_BO(
371 P_ACTION_TYPE,
372 NULL, -- COMMON_OBJ_ID
373 CONTACT_POINT_ID,
374 NULL, -- ORIG_SYSTEM,
375 NULL, -- ORIG_SYSTEM_REFERENCE,
376 STATUS,
377 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
378 OWNER_TABLE_ID,
379 PRIMARY_FLAG,
380 ATTRIBUTE_CATEGORY,
381 ATTRIBUTE1,
382 ATTRIBUTE2,
383 ATTRIBUTE3,
384 ATTRIBUTE4,
385 ATTRIBUTE5,
386 ATTRIBUTE6,
387 ATTRIBUTE7,
388 ATTRIBUTE8,
389 ATTRIBUTE9,
390 ATTRIBUTE10,
391 ATTRIBUTE11,
392 ATTRIBUTE12,
393 ATTRIBUTE13,
394 ATTRIBUTE14,
395 ATTRIBUTE15,
396 ATTRIBUTE16,
397 ATTRIBUTE17,
398 ATTRIBUTE18,
399 ATTRIBUTE19,
400 ATTRIBUTE20,
401 CONTACT_POINT_PURPOSE,
402 PRIMARY_BY_PURPOSE,
403 PROGRAM_UPDATE_DATE,
404 CREATED_BY_MODULE,
405 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
406 CREATION_DATE,
407 LAST_UPDATE_DATE,
408 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
409 ACTUAL_CONTENT_SOURCE,
410 TELEX_NUMBER,
411 CAST(MULTISET (
412 SELECT HZ_ORIG_SYS_REF_OBJ(
413 NULL, --P_ACTION_TYPE,
414 ORIG_SYSTEM_REF_ID,
415 ORIG_SYSTEM,
416 ORIG_SYSTEM_REFERENCE,
417 OWNER_TABLE_NAME,
418 OWNER_TABLE_ID,
419 STATUS,
420 REASON_CODE,
421 OLD_ORIG_SYSTEM_REFERENCE,
422 START_DATE_ACTIVE,
423 END_DATE_ACTIVE,
424 PROGRAM_UPDATE_DATE,
425 CREATED_BY_MODULE,
426 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
427 CREATION_DATE,
428 LAST_UPDATE_DATE,
429 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
430 ATTRIBUTE_CATEGORY,
431 ATTRIBUTE1,
432 ATTRIBUTE2,
433 ATTRIBUTE3,
434 ATTRIBUTE4,
435 ATTRIBUTE5,
436 ATTRIBUTE6,
437 ATTRIBUTE7,
438 ATTRIBUTE8,
439 ATTRIBUTE9,
440 ATTRIBUTE10,
441 ATTRIBUTE11,
442 ATTRIBUTE12,
443 ATTRIBUTE13,
444 ATTRIBUTE14,
445 ATTRIBUTE15,
446 ATTRIBUTE16,
447 ATTRIBUTE17,
448 ATTRIBUTE18,
449 ATTRIBUTE19,
450 ATTRIBUTE20)
451 FROM HZ_ORIG_SYS_REFERENCES OSR
452 WHERE
453 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
454 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
455 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
456 CAST(MULTISET (
457 SELECT HZ_CONTACT_PREF_OBJ(
458 P_ACTION_TYPE,
459 NULL, -- COMMON_OBJ_ID
460 CONTACT_PREFERENCE_ID,
461 'TLX',
462 CONTACT_LEVEL_TABLE_ID,
463 CONTACT_TYPE,
464 PREFERENCE_CODE,
465 PREFERENCE_TOPIC_TYPE,
466 PREFERENCE_TOPIC_TYPE_ID,
467 PREFERENCE_TOPIC_TYPE_CODE,
468 PREFERENCE_START_DATE,
469 PREFERENCE_END_DATE,
470 PREFERENCE_START_TIME_HR,
471 PREFERENCE_END_TIME_HR,
472 PREFERENCE_START_TIME_MI,
473 PREFERENCE_END_TIME_MI,
474 MAX_NO_OF_INTERACTIONS,
475 MAX_NO_OF_INTERACT_UOM_CODE,
476 REQUESTED_BY,
477 REASON_CODE,
478 STATUS,
479 PROGRAM_UPDATE_DATE,
480 CREATED_BY_MODULE,
481 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
482 CREATION_DATE,
483 LAST_UPDATE_DATE,
484 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
485 FROM HZ_CONTACT_PREFERENCES CPREF
486 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
487 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
488 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'TLX'
489 AND ((P_TELEX_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
490 AND OWNER_TABLE_ID = P_PARENT_ID)
491 OR (P_TELEX_ID IS NOT NULL AND CONTACT_POINT_ID = P_TELEX_ID));
492
493
494 l_debug_prefix VARCHAR2(30) := '';
495
496 begin
497
498 -- initialize API return status to success.
499 x_return_status := FND_API.G_RET_STS_SUCCESS;
500
501 -- Initialize message list if p_init_msg_list is set to TRUE
502 IF FND_API.to_Boolean(p_init_msg_list) THEN
503 FND_MSG_PUB.initialize;
504 END IF;
505
506 -- Debug info.
507 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
508 hz_utility_v2pub.debug(p_message=>'get_telex_bos(+)',
509 p_prefix=>l_debug_prefix,
510 p_msg_level=>fnd_log.level_procedure);
511 END IF;
512
513
514 x_telex_objs := HZ_TELEX_CP_BO_TBL();
515
516 open c1;
517 fetch c1 BULK COLLECT into x_telex_objs;
518 close c1;
519
520
521 -- Debug info.
522 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
523 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
524 p_msg_data=>x_msg_data,
525 p_msg_type=>'WARNING',
526 p_msg_level=>fnd_log.level_exception);
527 END IF;
528
529 -- Debug info.
530 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
531 hz_utility_v2pub.debug(p_message=>'get_telex_bos (-)',
532 p_prefix=>l_debug_prefix,
533 p_msg_level=>fnd_log.level_procedure);
534 END IF;
535
536 EXCEPTION
537
538 WHEN fnd_api.g_exc_error THEN
539 x_return_status := fnd_api.g_ret_sts_error;
540
541 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
542 p_count => x_msg_count,
543 p_data => x_msg_data);
544
545 -- Debug info.
546 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
547 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
548 p_msg_data=>x_msg_data,
549 p_msg_type=>'ERROR',
550 p_msg_level=>fnd_log.level_error);
551 END IF;
552 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
553 hz_utility_v2pub.debug(p_message=>'get_telex_bos(-)',
554 p_prefix=>l_debug_prefix,
555 p_msg_level=>fnd_log.level_procedure);
556 END IF;
557 WHEN fnd_api.g_exc_unexpected_error THEN
558 x_return_status := fnd_api.g_ret_sts_unexp_error;
559
560 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
561 p_count => x_msg_count,
562 p_data => x_msg_data);
563
564 -- Debug info.
565 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
566 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
567 p_msg_data=>x_msg_data,
568 p_msg_type=>'UNEXPECTED ERROR',
569 p_msg_level=>fnd_log.level_error);
570 END IF;
571 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
572 hz_utility_v2pub.debug(p_message=>'get_telex_bos (-)',
573 p_prefix=>l_debug_prefix,
574 p_msg_level=>fnd_log.level_procedure);
575 END IF;
576 WHEN OTHERS THEN
577 x_return_status := fnd_api.g_ret_sts_unexp_error;
578
579 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
580 fnd_message.set_token('ERROR' ,SQLERRM);
581 fnd_msg_pub.add;
582
583 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
584 p_count => x_msg_count,
585 p_data => x_msg_data);
586
587 -- Debug info.
588 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
589 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
590 p_msg_data=>x_msg_data,
591 p_msg_type=>'SQL ERROR',
592 p_msg_level=>fnd_log.level_error);
593 END IF;
594 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
595 hz_utility_v2pub.debug(p_message=>'get_telex_bos(-)',
596 p_prefix=>l_debug_prefix,
597 p_msg_level=>fnd_log.level_procedure);
598 END IF;
599
600 end;
601
602
603 --------------------------------------
604 --
605 -- PROCEDURE get_email_bos
606 --
607 -- DESCRIPTION
608 -- Get a logical email.
609 --
610 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
611 --
612 -- ARGUMENTS
613 -- IN:
614 -- p_init_msg_list Initialize message stack if it is set to
615 -- p_email_id email ID. If this id is passed in, return only one obj.
616 -- p_parent_id parent_id
617 -- p_parent_table_name parent_table name
618 -- OUT:
619 -- x_email_objs Logical email record.
620 -- x_return_status Return status after the call. The status can
621 -- be fnd_api.g_ret_sts_success (success),
622 -- fnd_api.g_ret_sts_error (error),
623 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
624 -- x_msg_count Number of messages in message stack.
625 -- x_msg_data Message text if x_msg_count is 1.
626 --
627 -- NOTES
628 --
629 -- MODIFICATION HISTORY
630 --
631 --
632 -- 30-May-2005 AWU Created.
633 --
634
635
636
637 PROCEDURE get_email_bos(
638 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
639 p_email_id IN NUMBER,
640 p_parent_id IN NUMBER,
641 p_parent_table_name IN VARCHAR2,
642 p_action_type IN VARCHAR2 := NULL,
643 x_email_objs OUT NOCOPY HZ_EMAIL_CP_BO_TBL,
644 x_return_status OUT NOCOPY VARCHAR2,
645 x_msg_count OUT NOCOPY NUMBER,
646 x_msg_data OUT NOCOPY VARCHAR2
647 ) is
648
649 cursor c1 is
650 SELECT HZ_EMAIL_CP_BO(
651 P_ACTION_TYPE,
652 NULL, -- COMMON_OBJ_ID
653 CONTACT_POINT_ID,
654 NULL, -- ORIG_SYSTEM,
655 NULL, -- ORIG_SYSTEM_REFERENCE,
656 STATUS,
657 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
658 OWNER_TABLE_ID,
659 PRIMARY_FLAG,
660 ATTRIBUTE_CATEGORY,
661 ATTRIBUTE1,
662 ATTRIBUTE2,
663 ATTRIBUTE3,
664 ATTRIBUTE4,
665 ATTRIBUTE5,
666 ATTRIBUTE6,
667 ATTRIBUTE7,
668 ATTRIBUTE8,
669 ATTRIBUTE9,
670 ATTRIBUTE10,
671 ATTRIBUTE11,
672 ATTRIBUTE12,
673 ATTRIBUTE13,
674 ATTRIBUTE14,
675 ATTRIBUTE15,
676 ATTRIBUTE16,
677 ATTRIBUTE17,
678 ATTRIBUTE18,
679 ATTRIBUTE19,
680 ATTRIBUTE20,
681 CONTACT_POINT_PURPOSE,
682 PRIMARY_BY_PURPOSE,
683 PROGRAM_UPDATE_DATE,
684 CREATED_BY_MODULE,
685 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
686 CREATION_DATE,
687 LAST_UPDATE_DATE,
688 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
689 ACTUAL_CONTENT_SOURCE,
690 EMAIL_FORMAT,
691 EMAIL_ADDRESS,
692 CAST(MULTISET (
693 SELECT HZ_ORIG_SYS_REF_OBJ(
694 NULL, --P_ACTION_TYPE,
695 ORIG_SYSTEM_REF_ID,
696 ORIG_SYSTEM,
697 ORIG_SYSTEM_REFERENCE,
698 OWNER_TABLE_NAME,
699 OWNER_TABLE_ID,
700 STATUS,
701 REASON_CODE,
702 OLD_ORIG_SYSTEM_REFERENCE,
703 START_DATE_ACTIVE,
704 END_DATE_ACTIVE,
705 PROGRAM_UPDATE_DATE,
706 CREATED_BY_MODULE,
707 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
708 CREATION_DATE,
709 LAST_UPDATE_DATE,
710 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
711 ATTRIBUTE_CATEGORY,
712 ATTRIBUTE1,
713 ATTRIBUTE2,
714 ATTRIBUTE3,
715 ATTRIBUTE4,
716 ATTRIBUTE5,
717 ATTRIBUTE6,
718 ATTRIBUTE7,
719 ATTRIBUTE8,
720 ATTRIBUTE9,
721 ATTRIBUTE10,
722 ATTRIBUTE11,
723 ATTRIBUTE12,
724 ATTRIBUTE13,
725 ATTRIBUTE14,
726 ATTRIBUTE15,
727 ATTRIBUTE16,
728 ATTRIBUTE17,
729 ATTRIBUTE18,
730 ATTRIBUTE19,
731 ATTRIBUTE20)
732 FROM HZ_ORIG_SYS_REFERENCES OSR
733 WHERE
734 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
735 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
736 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
737 CAST(MULTISET (
738 SELECT HZ_CONTACT_PREF_OBJ(
739 P_ACTION_TYPE,
740 NULL, -- COMMON_OBJ_ID
741 CONTACT_PREFERENCE_ID,
742 'EMAIL',
743 CONTACT_LEVEL_TABLE_ID,
744 CONTACT_TYPE,
745 PREFERENCE_CODE,
746 PREFERENCE_TOPIC_TYPE,
747 PREFERENCE_TOPIC_TYPE_ID,
748 PREFERENCE_TOPIC_TYPE_CODE,
749 PREFERENCE_START_DATE,
750 PREFERENCE_END_DATE,
751 PREFERENCE_START_TIME_HR,
752 PREFERENCE_END_TIME_HR,
753 PREFERENCE_START_TIME_MI,
754 PREFERENCE_END_TIME_MI,
755 MAX_NO_OF_INTERACTIONS,
756 MAX_NO_OF_INTERACT_UOM_CODE,
757 REQUESTED_BY,
758 REASON_CODE,
759 STATUS,
760 PROGRAM_UPDATE_DATE,
761 CREATED_BY_MODULE,
762 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
763 CREATION_DATE,
764 LAST_UPDATE_DATE,
765 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
766 FROM HZ_CONTACT_PREFERENCES CPREF
767 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
768 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
769 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EMAIL'
770 AND ((P_EMAIL_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
771 AND OWNER_TABLE_ID = P_PARENT_ID)
772 OR (P_EMAIL_ID IS NOT NULL AND CONTACT_POINT_ID = P_EMAIL_ID));
773
774
775 l_debug_prefix VARCHAR2(30) := '';
776
777 BEGIN
778
779 -- initialize API return status to success.
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781
782 -- Initialize message list if p_init_msg_list is set to TRUE
783 IF FND_API.to_Boolean(p_init_msg_list) THEN
784 FND_MSG_PUB.initialize;
785 END IF;
786
787 -- Debug info.
788 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
789 hz_utility_v2pub.debug(p_message=>'get_email_bos(+)',
790 p_prefix=>l_debug_prefix,
791 p_msg_level=>fnd_log.level_procedure);
792 END IF;
793
794
795
796 x_email_objs := HZ_EMAIL_CP_BO_TBL();
797 open c1;
798 fetch c1 BULK COLLECT into x_email_objs;
799 close c1;
800
801 -- Debug info.
802 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
803 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
804 p_msg_data=>x_msg_data,
805 p_msg_type=>'WARNING',
806 p_msg_level=>fnd_log.level_exception);
807 END IF;
808
809 -- Debug info.
810 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
811 hz_utility_v2pub.debug(p_message=>'get_email_bos (-)',
812 p_prefix=>l_debug_prefix,
813 p_msg_level=>fnd_log.level_procedure);
814 END IF;
815
816 EXCEPTION
817
818 WHEN fnd_api.g_exc_error THEN
819 x_return_status := fnd_api.g_ret_sts_error;
820
821 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
822 p_count => x_msg_count,
823 p_data => x_msg_data);
824
825 -- Debug info.
826 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
827 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
828 p_msg_data=>x_msg_data,
829 p_msg_type=>'ERROR',
830 p_msg_level=>fnd_log.level_error);
831 END IF;
832 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
833 hz_utility_v2pub.debug(p_message=>'get_email_bos (-)',
834 p_prefix=>l_debug_prefix,
835 p_msg_level=>fnd_log.level_procedure);
836 END IF;
837 WHEN fnd_api.g_exc_unexpected_error THEN
838 x_return_status := fnd_api.g_ret_sts_unexp_error;
839
840 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
841 p_count => x_msg_count,
842 p_data => x_msg_data);
843
844 -- Debug info.
845 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
846 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
847 p_msg_data=>x_msg_data,
848 p_msg_type=>'UNEXPECTED ERROR',
849 p_msg_level=>fnd_log.level_error);
850 END IF;
851 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
852 hz_utility_v2pub.debug(p_message=>'get_email_bos (-)',
853 p_prefix=>l_debug_prefix,
854 p_msg_level=>fnd_log.level_procedure);
855 END IF;
856 WHEN OTHERS THEN
857 x_return_status := fnd_api.g_ret_sts_unexp_error;
858
859 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
860 fnd_message.set_token('ERROR' ,SQLERRM);
861 fnd_msg_pub.add;
862
863 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
864 p_count => x_msg_count,
865 p_data => x_msg_data);
866
867 -- Debug info.
868 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
869 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
870 p_msg_data=>x_msg_data,
871 p_msg_type=>'SQL ERROR',
872 p_msg_level=>fnd_log.level_error);
873 END IF;
874 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
875 hz_utility_v2pub.debug(p_message=>'get_email_bos (-)',
876 p_prefix=>l_debug_prefix,
877 p_msg_level=>fnd_log.level_procedure);
878 END IF;
879
880
881 end;
882
883
884
885 --------------------------------------
886 --
887 -- PROCEDURE get_web_bos
888 --
889 -- DESCRIPTION
890 -- Get a logical web business object.
891 --
892 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
893 --
894 -- ARGUMENTS
895 -- IN:
896 -- p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
897 -- p_web_id web ID. If this id is passed in, return only one obj.
898 -- p_parent_id parent_id
899 -- p_parent_table_name parent_table name
900 --
901 -- OUT:
902 -- x_web_objs Logical web record.
903 -- x_return_status Return status after the call. The status can
904 -- be fnd_api.g_ret_sts_success (success),
905 -- fnd_api.g_ret_sts_error (error),
906 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
907 -- x_msg_count Number of messages in message stack.
908 -- x_msg_data Message text if x_msg_count is 1.
909 --
910 -- NOTES
911 --
912 -- MODIFICATION HISTORY
913 --
914 --
915 -- 30-May-2005 AWU Created.
916 --
917
918
919
920 PROCEDURE get_web_bos(
921 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
922 p_web_id IN NUMBER,
923 p_parent_id IN NUMBER,
924 p_parent_table_name IN VARCHAR2,
925 p_action_type IN VARCHAR2 := NULL,
926 x_web_objs OUT NOCOPY HZ_WEB_CP_BO_TBL,
927 x_return_status OUT NOCOPY VARCHAR2,
928 x_msg_count OUT NOCOPY NUMBER,
929 x_msg_data OUT NOCOPY VARCHAR2
930 ) is
931
932 cursor c1 is
933 SELECT HZ_WEB_CP_BO(
934 P_ACTION_TYPE,
935 NULL, -- COMMON_OBJ_ID
936 CONTACT_POINT_ID,
937 NULL, -- ORIG_SYSTEM,
938 NULL, -- ORIG_SYSTEM_REFERENCE,
939 STATUS,
940 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
941 OWNER_TABLE_ID,
942 PRIMARY_FLAG,
943 ATTRIBUTE_CATEGORY,
944 ATTRIBUTE1,
945 ATTRIBUTE2,
946 ATTRIBUTE3,
947 ATTRIBUTE4,
948 ATTRIBUTE5,
949 ATTRIBUTE6,
950 ATTRIBUTE7,
951 ATTRIBUTE8,
952 ATTRIBUTE9,
953 ATTRIBUTE10,
954 ATTRIBUTE11,
955 ATTRIBUTE12,
956 ATTRIBUTE13,
957 ATTRIBUTE14,
958 ATTRIBUTE15,
959 ATTRIBUTE16,
960 ATTRIBUTE17,
961 ATTRIBUTE18,
962 ATTRIBUTE19,
963 ATTRIBUTE20,
964 CONTACT_POINT_PURPOSE,
965 PRIMARY_BY_PURPOSE,
966 PROGRAM_UPDATE_DATE,
967 CREATED_BY_MODULE,
968 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
969 CREATION_DATE,
970 LAST_UPDATE_DATE,
971 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
972 ACTUAL_CONTENT_SOURCE,
973 WEB_TYPE,
974 URL,
975 CAST(MULTISET (
976 SELECT HZ_ORIG_SYS_REF_OBJ(
977 NULL, --P_ACTION_TYPE,
978 ORIG_SYSTEM_REF_ID,
979 ORIG_SYSTEM,
980 ORIG_SYSTEM_REFERENCE,
981 OWNER_TABLE_NAME,
982 OWNER_TABLE_ID,
983 STATUS,
984 REASON_CODE,
985 OLD_ORIG_SYSTEM_REFERENCE,
986 START_DATE_ACTIVE,
987 END_DATE_ACTIVE,
988 PROGRAM_UPDATE_DATE,
989 CREATED_BY_MODULE,
990 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
991 CREATION_DATE,
992 LAST_UPDATE_DATE,
993 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
994 ATTRIBUTE_CATEGORY,
995 ATTRIBUTE1,
996 ATTRIBUTE2,
997 ATTRIBUTE3,
998 ATTRIBUTE4,
999 ATTRIBUTE5,
1000 ATTRIBUTE6,
1001 ATTRIBUTE7,
1002 ATTRIBUTE8,
1003 ATTRIBUTE9,
1004 ATTRIBUTE10,
1005 ATTRIBUTE11,
1006 ATTRIBUTE12,
1007 ATTRIBUTE13,
1008 ATTRIBUTE14,
1009 ATTRIBUTE15,
1010 ATTRIBUTE16,
1011 ATTRIBUTE17,
1012 ATTRIBUTE18,
1013 ATTRIBUTE19,
1014 ATTRIBUTE20)
1015 FROM HZ_ORIG_SYS_REFERENCES OSR
1016 WHERE
1017 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
1018 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1019 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1020 CAST(MULTISET (
1021 SELECT HZ_CONTACT_PREF_OBJ(
1022 P_ACTION_TYPE,
1023 NULL, -- COMMON_OBJ_ID
1024 CONTACT_PREFERENCE_ID,
1025 'WEB',
1026 CONTACT_LEVEL_TABLE_ID,
1027 CONTACT_TYPE,
1028 PREFERENCE_CODE,
1029 PREFERENCE_TOPIC_TYPE,
1030 PREFERENCE_TOPIC_TYPE_ID,
1031 PREFERENCE_TOPIC_TYPE_CODE,
1032 PREFERENCE_START_DATE,
1033 PREFERENCE_END_DATE,
1034 PREFERENCE_START_TIME_HR,
1035 PREFERENCE_END_TIME_HR,
1036 PREFERENCE_START_TIME_MI,
1037 PREFERENCE_END_TIME_MI,
1038 MAX_NO_OF_INTERACTIONS,
1039 MAX_NO_OF_INTERACT_UOM_CODE,
1040 REQUESTED_BY,
1041 REASON_CODE,
1042 STATUS,
1043 PROGRAM_UPDATE_DATE,
1044 CREATED_BY_MODULE,
1045 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1046 CREATION_DATE,
1047 LAST_UPDATE_DATE,
1048 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1049 FROM HZ_CONTACT_PREFERENCES CPREF
1050 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
1051 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
1052 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'WEB'
1053 AND ((P_WEB_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
1054 AND OWNER_TABLE_ID = P_PARENT_ID)
1055 OR (P_WEB_ID IS NOT NULL AND CONTACT_POINT_ID = P_WEB_ID));
1056
1057
1058 l_debug_prefix VARCHAR2(30) := '';
1059
1060
1061 BEGIN
1062
1063 -- initialize API return status to success.
1064 x_return_status := FND_API.G_RET_STS_SUCCESS;
1065
1066 -- Initialize message list if p_init_msg_list is set to TRUE
1067 IF FND_API.to_Boolean(p_init_msg_list) THEN
1068 FND_MSG_PUB.initialize;
1069 END IF;
1070
1071 -- Debug info.
1072 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1073 hz_utility_v2pub.debug(p_message=>'get_web_bos(+)',
1074 p_prefix=>l_debug_prefix,
1075 p_msg_level=>fnd_log.level_procedure);
1076 END IF;
1077
1078
1079
1080
1081 x_web_objs := HZ_WEB_CP_BO_TBL();
1082 open c1;
1083 fetch c1 BULK COLLECT into x_web_objs;
1084 close c1;
1085
1086 -- Debug info.
1087 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1088 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1089 p_msg_data=>x_msg_data,
1090 p_msg_type=>'WARNING',
1091 p_msg_level=>fnd_log.level_exception);
1092 END IF;
1093
1094 -- Debug info.
1095 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1096 hz_utility_v2pub.debug(p_message=>'get_web_bos (-)',
1097 p_prefix=>l_debug_prefix,
1098 p_msg_level=>fnd_log.level_procedure);
1099 END IF;
1100
1101 EXCEPTION
1102
1103 WHEN fnd_api.g_exc_error THEN
1104 x_return_status := fnd_api.g_ret_sts_error;
1105
1106 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1107 p_count => x_msg_count,
1108 p_data => x_msg_data);
1109
1110 -- Debug info.
1111 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1112 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1113 p_msg_data=>x_msg_data,
1114 p_msg_type=>'ERROR',
1115 p_msg_level=>fnd_log.level_error);
1116 END IF;
1117 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1118 hz_utility_v2pub.debug(p_message=>'get_web_bos (-)',
1119 p_prefix=>l_debug_prefix,
1120 p_msg_level=>fnd_log.level_procedure);
1121 END IF;
1122 WHEN fnd_api.g_exc_unexpected_error THEN
1123 x_return_status := fnd_api.g_ret_sts_unexp_error;
1124
1125 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1126 p_count => x_msg_count,
1127 p_data => x_msg_data);
1128
1129 -- Debug info.
1130 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1131 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1132 p_msg_data=>x_msg_data,
1133 p_msg_type=>'UNEXPECTED ERROR',
1134 p_msg_level=>fnd_log.level_error);
1135 END IF;
1136 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1137 hz_utility_v2pub.debug(p_message=>'get_web_bos (-)',
1138 p_prefix=>l_debug_prefix,
1139 p_msg_level=>fnd_log.level_procedure);
1140 END IF;
1141 WHEN OTHERS THEN
1142 x_return_status := fnd_api.g_ret_sts_unexp_error;
1143
1144 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1145 fnd_message.set_token('ERROR' ,SQLERRM);
1146 fnd_msg_pub.add;
1147
1148 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1149 p_count => x_msg_count,
1150 p_data => x_msg_data);
1151
1152 -- Debug info.
1153 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1154 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1155 p_msg_data=>x_msg_data,
1156 p_msg_type=>'SQL ERROR',
1157 p_msg_level=>fnd_log.level_error);
1158 END IF;
1159 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1160 hz_utility_v2pub.debug(p_message=>'get_web_bos (-)',
1161 p_prefix=>l_debug_prefix,
1162 p_msg_level=>fnd_log.level_procedure);
1163 END IF;
1164
1165
1166 end;
1167
1168
1169
1170
1171 --------------------------------------
1172 --
1173 -- PROCEDURE get_edi_bos
1174 --
1175 -- DESCRIPTION
1176 -- Get a logical edi business object.
1177 --
1178 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1179 --
1180 -- ARGUMENTS
1181 -- IN:
1182 -- p_init_msg_list Initialize message stack if it is set to
1183 -- p_edi_id edi ID. If this id is passed in, return only one obj.
1184 -- p_parent_id parent_id
1185 -- p_parent_table_name parent_table name
1186
1187 -- OUT:
1188 -- x_edi_objs Logical edi record.
1189 -- x_return_status Return status after the call. The status can
1190 -- be fnd_api.g_ret_sts_success (success),
1191 -- fnd_api.g_ret_sts_error (error),
1192 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1193 -- x_msg_count Number of messages in message stack.
1194 -- x_msg_data Message text if x_msg_count is 1.
1195 --
1196 -- NOTES
1197 --
1198 -- MODIFICATION HISTORY
1199 --
1200 --
1201 -- 30-May-2005 AWU Created.
1202 --
1203
1204
1205
1206 PROCEDURE get_edi_bos(
1207 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1208 p_edi_id IN NUMBER,
1209 p_parent_id IN NUMBER,
1210 p_parent_table_name IN VARCHAR2,
1211 p_action_type IN VARCHAR2 := NULL,
1212 x_edi_objs OUT NOCOPY HZ_EDI_CP_BO_TBL,
1213 x_return_status OUT NOCOPY VARCHAR2,
1214 x_msg_count OUT NOCOPY NUMBER,
1215 x_msg_data OUT NOCOPY VARCHAR2
1216 ) is
1217
1218 cursor c1 is
1219 SELECT HZ_EDI_CP_BO(
1220 P_ACTION_TYPE,
1221 NULL, -- COMMON_OBJ_ID
1222 CONTACT_POINT_ID,
1223 NULL, -- ORIG_SYSTEM,
1224 NULL, -- ORIG_SYSTEM_REFERENCE,
1225 STATUS,
1226 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1227 OWNER_TABLE_ID,
1228 PRIMARY_FLAG,
1229 ATTRIBUTE_CATEGORY,
1230 ATTRIBUTE1,
1231 ATTRIBUTE2,
1232 ATTRIBUTE3,
1233 ATTRIBUTE4,
1234 ATTRIBUTE5,
1235 ATTRIBUTE6,
1236 ATTRIBUTE7,
1237 ATTRIBUTE8,
1238 ATTRIBUTE9,
1239 ATTRIBUTE10,
1240 ATTRIBUTE11,
1241 ATTRIBUTE12,
1242 ATTRIBUTE13,
1243 ATTRIBUTE14,
1244 ATTRIBUTE15,
1245 ATTRIBUTE16,
1246 ATTRIBUTE17,
1247 ATTRIBUTE18,
1248 ATTRIBUTE19,
1249 ATTRIBUTE20,
1250 CONTACT_POINT_PURPOSE,
1251 PRIMARY_BY_PURPOSE,
1252 PROGRAM_UPDATE_DATE,
1253 CREATED_BY_MODULE,
1254 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1255 CREATION_DATE,
1256 LAST_UPDATE_DATE,
1257 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1258 ACTUAL_CONTENT_SOURCE,
1259 EDI_TRANSACTION_HANDLING,
1260 EDI_ID_NUMBER,
1261 EDI_PAYMENT_METHOD,
1262 EDI_PAYMENT_FORMAT,
1263 EDI_REMITTANCE_METHOD,
1264 EDI_REMITTANCE_INSTRUCTION,
1265 EDI_TP_HEADER_ID,
1266 EDI_ECE_TP_LOCATION_CODE,
1267 CAST(MULTISET (
1268 SELECT HZ_ORIG_SYS_REF_OBJ(
1269 NULL, --P_ACTION_TYPE,
1270 ORIG_SYSTEM_REF_ID,
1271 ORIG_SYSTEM,
1272 ORIG_SYSTEM_REFERENCE,
1273 OWNER_TABLE_NAME,
1274 OWNER_TABLE_ID,
1275 STATUS,
1276 REASON_CODE,
1277 OLD_ORIG_SYSTEM_REFERENCE,
1278 START_DATE_ACTIVE,
1279 END_DATE_ACTIVE,
1280 PROGRAM_UPDATE_DATE,
1281 CREATED_BY_MODULE,
1282 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1283 CREATION_DATE,
1284 LAST_UPDATE_DATE,
1285 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1286 ATTRIBUTE_CATEGORY,
1287 ATTRIBUTE1,
1288 ATTRIBUTE2,
1289 ATTRIBUTE3,
1290 ATTRIBUTE4,
1291 ATTRIBUTE5,
1292 ATTRIBUTE6,
1293 ATTRIBUTE7,
1294 ATTRIBUTE8,
1295 ATTRIBUTE9,
1296 ATTRIBUTE10,
1297 ATTRIBUTE11,
1298 ATTRIBUTE12,
1299 ATTRIBUTE13,
1300 ATTRIBUTE14,
1301 ATTRIBUTE15,
1302 ATTRIBUTE16,
1303 ATTRIBUTE17,
1304 ATTRIBUTE18,
1305 ATTRIBUTE19,
1306 ATTRIBUTE20)
1307 FROM HZ_ORIG_SYS_REFERENCES OSR
1308 WHERE
1309 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
1310 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1311 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1312 CAST(MULTISET (
1313 SELECT HZ_CONTACT_PREF_OBJ(
1314 P_ACTION_TYPE,
1315 NULL, -- COMMON_OBJ_ID
1316 CONTACT_PREFERENCE_ID,
1317 'EDI',
1318 CONTACT_LEVEL_TABLE_ID,
1319 CONTACT_TYPE,
1320 PREFERENCE_CODE,
1321 PREFERENCE_TOPIC_TYPE,
1322 PREFERENCE_TOPIC_TYPE_ID,
1323 PREFERENCE_TOPIC_TYPE_CODE,
1324 PREFERENCE_START_DATE,
1325 PREFERENCE_END_DATE,
1326 PREFERENCE_START_TIME_HR,
1327 PREFERENCE_END_TIME_HR,
1328 PREFERENCE_START_TIME_MI,
1329 PREFERENCE_END_TIME_MI,
1330 MAX_NO_OF_INTERACTIONS,
1331 MAX_NO_OF_INTERACT_UOM_CODE,
1332 REQUESTED_BY,
1333 REASON_CODE,
1334 STATUS,
1335 PROGRAM_UPDATE_DATE,
1336 CREATED_BY_MODULE,
1337 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1338 CREATION_DATE,
1339 LAST_UPDATE_DATE,
1340 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1341 FROM HZ_CONTACT_PREFERENCES CPREF
1342 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
1343 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
1344 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EDI'
1345 AND ((P_EDI_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
1346 AND OWNER_TABLE_ID = P_PARENT_ID)
1347 OR (P_EDI_ID IS NOT NULL AND CONTACT_POINT_ID = P_EDI_ID));
1348
1349
1350 l_debug_prefix VARCHAR2(30) := '';
1351
1352 BEGIN
1353
1354 -- initialize API return status to success.
1355 x_return_status := FND_API.G_RET_STS_SUCCESS;
1356
1357 -- Initialize message list if p_init_msg_list is set to TRUE
1358 IF FND_API.to_Boolean(p_init_msg_list) THEN
1359 FND_MSG_PUB.initialize;
1360 END IF;
1361
1362 -- Debug info.
1363 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1364 hz_utility_v2pub.debug(p_message=>'get_edi_bos(+)',
1365 p_prefix=>l_debug_prefix,
1366 p_msg_level=>fnd_log.level_procedure);
1367 END IF;
1368
1369 x_edi_objs := HZ_EDI_CP_BO_TBL();
1370 open c1;
1371 fetch c1 BULK COLLECT into x_edi_objs;
1372 close c1;
1373
1374
1375 -- Debug info.
1376 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1377 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1378 p_msg_data=>x_msg_data,
1379 p_msg_type=>'WARNING',
1380 p_msg_level=>fnd_log.level_exception);
1381 END IF;
1382
1383 -- Debug info.
1384 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1385 hz_utility_v2pub.debug(p_message=>'get_edi_bos (-)',
1386 p_prefix=>l_debug_prefix,
1387 p_msg_level=>fnd_log.level_procedure);
1388 END IF;
1389
1390 EXCEPTION
1391
1392 WHEN fnd_api.g_exc_error THEN
1393 x_return_status := fnd_api.g_ret_sts_error;
1394
1395 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1396 p_count => x_msg_count,
1397 p_data => x_msg_data);
1398
1399 -- Debug info.
1400 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1401 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1402 p_msg_data=>x_msg_data,
1403 p_msg_type=>'ERROR',
1404 p_msg_level=>fnd_log.level_error);
1405 END IF;
1406 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1407 hz_utility_v2pub.debug(p_message=>'get_edi_bos (-)',
1408 p_prefix=>l_debug_prefix,
1409 p_msg_level=>fnd_log.level_procedure);
1410 END IF;
1411 WHEN fnd_api.g_exc_unexpected_error THEN
1412 x_return_status := fnd_api.g_ret_sts_unexp_error;
1413
1414 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1415 p_count => x_msg_count,
1416 p_data => x_msg_data);
1417
1418 -- Debug info.
1419 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1420 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1421 p_msg_data=>x_msg_data,
1422 p_msg_type=>'UNEXPECTED ERROR',
1423 p_msg_level=>fnd_log.level_error);
1424 END IF;
1425 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1426 hz_utility_v2pub.debug(p_message=>'get_edi_bos (-)',
1427 p_prefix=>l_debug_prefix,
1428 p_msg_level=>fnd_log.level_procedure);
1429 END IF;
1430 WHEN OTHERS THEN
1431 x_return_status := fnd_api.g_ret_sts_unexp_error;
1432
1433 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1434 fnd_message.set_token('ERROR' ,SQLERRM);
1435 fnd_msg_pub.add;
1436
1437 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1438 p_count => x_msg_count,
1439 p_data => x_msg_data);
1440
1441 -- Debug info.
1442 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1443 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1444 p_msg_data=>x_msg_data,
1445 p_msg_type=>'SQL ERROR',
1446 p_msg_level=>fnd_log.level_error);
1447 END IF;
1448 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1449 hz_utility_v2pub.debug(p_message=>'get_edi_bos (-)',
1450 p_prefix=>l_debug_prefix,
1451 p_msg_level=>fnd_log.level_procedure);
1452 END IF;
1453
1454
1455 end;
1456
1457
1458
1459 --------------------------------------
1460 --
1461 -- PROCEDURE get_eft_bos
1462 --
1463 -- DESCRIPTION
1464 -- Get a logical eft.
1465 --
1466 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1467 --
1468 -- ARGUMENTS
1469 -- IN:
1470 -- p_init_msg_list Initialize message stack if it is set to
1471 -- p_eft_id eft ID. If this id is passed in, return only one obj.
1472 -- p_parent_id parent_id
1473 -- p_parent_table_name parent_table name
1474
1475 -- OUT:
1476 -- x_eft_objs Logical eft record.
1477 -- x_return_status Return status after the call. The status can
1478 -- be fnd_api.g_ret_sts_success (success),
1479 -- fnd_api.g_ret_sts_error (error),
1480 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1481 -- x_msg_count Number of messages in message stack.
1482 -- x_msg_data Message text if x_msg_count is 1.
1483 --
1484 -- NOTES
1485 --
1486 -- MODIFICATION HISTORY
1487 --
1488 --
1489 -- 30-May-2005 AWU Created.
1490 --
1491
1492
1493
1494 PROCEDURE get_eft_bos(
1495 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1496 p_eft_id IN NUMBER,
1497 p_parent_id IN NUMBER,
1498 p_parent_table_name IN VARCHAR2,
1499 p_action_type IN VARCHAR2 := NULL,
1500 x_eft_objs OUT NOCOPY HZ_EFT_CP_BO_TBL,
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 x_msg_count OUT NOCOPY NUMBER,
1503 x_msg_data OUT NOCOPY VARCHAR2
1504 ) is
1505
1506 cursor c1 is
1507 SELECT HZ_EFT_CP_BO(
1508 P_ACTION_TYPE,
1509 NULL, -- COMMON_OBJ_ID
1510 CONTACT_POINT_ID,
1511 NULL, -- ORIG_SYSTEM,
1512 NULL, -- ORIG_SYSTEM_REFERENCE,
1513 STATUS,
1514 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1515 OWNER_TABLE_ID,
1516 PRIMARY_FLAG,
1517 ATTRIBUTE_CATEGORY,
1518 ATTRIBUTE1,
1519 ATTRIBUTE2,
1520 ATTRIBUTE3,
1521 ATTRIBUTE4,
1522 ATTRIBUTE5,
1523 ATTRIBUTE6,
1524 ATTRIBUTE7,
1525 ATTRIBUTE8,
1526 ATTRIBUTE9,
1527 ATTRIBUTE10,
1528 ATTRIBUTE11,
1529 ATTRIBUTE12,
1530 ATTRIBUTE13,
1531 ATTRIBUTE14,
1532 ATTRIBUTE15,
1533 ATTRIBUTE16,
1534 ATTRIBUTE17,
1535 ATTRIBUTE18,
1536 ATTRIBUTE19,
1537 ATTRIBUTE20,
1538 CONTACT_POINT_PURPOSE,
1539 PRIMARY_BY_PURPOSE,
1540 PROGRAM_UPDATE_DATE,
1541 CREATED_BY_MODULE,
1542 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1543 CREATION_DATE,
1544 LAST_UPDATE_DATE,
1545 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1546 ACTUAL_CONTENT_SOURCE,
1547 EFT_TRANSMISSION_PROGRAM_ID,
1548 EFT_PRINTING_PROGRAM_ID,
1549 EFT_USER_NUMBER,
1550 EFT_SWIFT_CODE,
1551 CAST(MULTISET (
1552 SELECT HZ_ORIG_SYS_REF_OBJ(
1553 NULL, --P_ACTION_TYPE,
1554 ORIG_SYSTEM_REF_ID,
1555 ORIG_SYSTEM,
1556 ORIG_SYSTEM_REFERENCE,
1557 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1558 OWNER_TABLE_ID,
1559 STATUS,
1560 REASON_CODE,
1561 OLD_ORIG_SYSTEM_REFERENCE,
1562 START_DATE_ACTIVE,
1563 END_DATE_ACTIVE,
1564 PROGRAM_UPDATE_DATE,
1565 CREATED_BY_MODULE,
1566 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1567 CREATION_DATE,
1568 LAST_UPDATE_DATE,
1569 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1570 ATTRIBUTE_CATEGORY,
1571 ATTRIBUTE1,
1572 ATTRIBUTE2,
1573 ATTRIBUTE3,
1574 ATTRIBUTE4,
1575 ATTRIBUTE5,
1576 ATTRIBUTE6,
1577 ATTRIBUTE7,
1578 ATTRIBUTE8,
1579 ATTRIBUTE9,
1580 ATTRIBUTE10,
1581 ATTRIBUTE11,
1582 ATTRIBUTE12,
1583 ATTRIBUTE13,
1584 ATTRIBUTE14,
1585 ATTRIBUTE15,
1586 ATTRIBUTE16,
1587 ATTRIBUTE17,
1588 ATTRIBUTE18,
1589 ATTRIBUTE19,
1590 ATTRIBUTE20)
1591 FROM HZ_ORIG_SYS_REFERENCES OSR
1592 WHERE
1593 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
1594 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1595 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1596 CAST(MULTISET (
1597 SELECT HZ_CONTACT_PREF_OBJ(
1598 P_ACTION_TYPE,
1599 NULL, -- COMMON_OBJ_ID
1600 CONTACT_PREFERENCE_ID,
1601 'EFT',
1602 CONTACT_LEVEL_TABLE_ID,
1603 CONTACT_TYPE,
1604 PREFERENCE_CODE,
1605 PREFERENCE_TOPIC_TYPE,
1606 PREFERENCE_TOPIC_TYPE_ID,
1607 PREFERENCE_TOPIC_TYPE_CODE,
1608 PREFERENCE_START_DATE,
1609 PREFERENCE_END_DATE,
1610 PREFERENCE_START_TIME_HR,
1611 PREFERENCE_END_TIME_HR,
1612 PREFERENCE_START_TIME_MI,
1613 PREFERENCE_END_TIME_MI,
1614 MAX_NO_OF_INTERACTIONS,
1615 MAX_NO_OF_INTERACT_UOM_CODE,
1616 REQUESTED_BY,
1617 REASON_CODE,
1618 STATUS,
1619 PROGRAM_UPDATE_DATE,
1620 CREATED_BY_MODULE,
1621 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1622 CREATION_DATE,
1623 LAST_UPDATE_DATE,
1624 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1625 FROM HZ_CONTACT_PREFERENCES CPREF
1626 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
1627 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
1628 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'EFT'
1629 AND ((P_EFT_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
1630 AND OWNER_TABLE_ID = P_PARENT_ID)
1631 OR (P_EFT_ID IS NOT NULL AND CONTACT_POINT_ID = P_EFT_ID));
1632
1633
1634 l_debug_prefix VARCHAR2(30) := '';
1635
1636 BEGIN
1637
1638 -- initialize API return status to success.
1639 x_return_status := FND_API.G_RET_STS_SUCCESS;
1640
1641 -- Initialize message list if p_init_msg_list is set to TRUE
1642 IF FND_API.to_Boolean(p_init_msg_list) THEN
1643 FND_MSG_PUB.initialize;
1644 END IF;
1645
1646 -- Debug info.
1647 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1648 hz_utility_v2pub.debug(p_message=>'get_eft_bos(+)',
1649 p_prefix=>l_debug_prefix,
1650 p_msg_level=>fnd_log.level_procedure);
1651 END IF;
1652
1653
1654 x_eft_objs := HZ_EFT_CP_BO_TBL();
1655 open c1;
1656 fetch c1 BULK COLLECT into x_eft_objs;
1657 close c1;
1658
1659 -- Debug info.
1660 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1661 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1662 p_msg_data=>x_msg_data,
1663 p_msg_type=>'WARNING',
1664 p_msg_level=>fnd_log.level_exception);
1665 END IF;
1666
1667 -- Debug info.
1668 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1669 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
1670 p_prefix=>l_debug_prefix,
1671 p_msg_level=>fnd_log.level_procedure);
1672 END IF;
1673
1674 EXCEPTION
1675
1676 WHEN fnd_api.g_exc_error THEN
1677 x_return_status := fnd_api.g_ret_sts_error;
1678
1679 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1680 p_count => x_msg_count,
1681 p_data => x_msg_data);
1682
1683 -- Debug info.
1684 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1685 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1686 p_msg_data=>x_msg_data,
1687 p_msg_type=>'ERROR',
1688 p_msg_level=>fnd_log.level_error);
1689 END IF;
1690 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1691 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
1692 p_prefix=>l_debug_prefix,
1693 p_msg_level=>fnd_log.level_procedure);
1694 END IF;
1695 WHEN fnd_api.g_exc_unexpected_error THEN
1696 x_return_status := fnd_api.g_ret_sts_unexp_error;
1697
1698 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1699 p_count => x_msg_count,
1700 p_data => x_msg_data);
1701
1702 -- Debug info.
1703 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1704 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1705 p_msg_data=>x_msg_data,
1706 p_msg_type=>'UNEXPECTED ERROR',
1707 p_msg_level=>fnd_log.level_error);
1708 END IF;
1709 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1710 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
1711 p_prefix=>l_debug_prefix,
1712 p_msg_level=>fnd_log.level_procedure);
1713 END IF;
1714 WHEN OTHERS THEN
1715 x_return_status := fnd_api.g_ret_sts_unexp_error;
1716
1717 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1718 fnd_message.set_token('ERROR' ,SQLERRM);
1719 fnd_msg_pub.add;
1720
1721 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1722 p_count => x_msg_count,
1723 p_data => x_msg_data);
1724
1725 -- Debug info.
1726 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1727 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1728 p_msg_data=>x_msg_data,
1729 p_msg_type=>'SQL ERROR',
1730 p_msg_level=>fnd_log.level_error);
1731 END IF;
1732 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1733 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
1734 p_prefix=>l_debug_prefix,
1735 p_msg_level=>fnd_log.level_procedure);
1736 END IF;
1737
1738
1739 end;
1740
1741
1742
1743 --------------------------------------
1744 --
1745 -- PROCEDURE get_sms_bos
1746 --
1747 -- DESCRIPTION
1748 -- Get a logical sms.
1749 --
1750 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1751 --
1752 -- ARGUMENTS
1753 -- IN:
1754 -- p_init_msg_list Initialize message stack if it is set to
1755 -- p_sms_id sms ID. If this id is passed in, return only one obj.
1756 -- p_parent_id parent_id
1757 -- p_parent_table_name parent_table name
1758
1759 -- OUT:
1760 -- x_sms_objs Logical sms record.
1761 -- x_return_status Return status after the call. The status can
1762 -- be fnd_api.g_ret_sts_success (success),
1763 -- fnd_api.g_ret_sts_error (error),
1764 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1765 -- x_msg_count Number of messages in message stack.
1766 -- x_msg_data Message text if x_msg_count is 1.
1767 --
1768 -- NOTES
1769 --
1770 -- MODIFICATION HISTORY
1771 --
1772 --
1773 -- 30-May-2005 AWU Created.
1774 --
1775
1776
1777
1778 PROCEDURE get_sms_bos(
1779 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1780 p_sms_id IN NUMBER,
1781 p_parent_id IN NUMBER,
1782 p_parent_table_name IN VARCHAR2,
1783 p_action_type IN VARCHAR2 := NULL,
1784 x_sms_objs OUT NOCOPY HZ_SMS_CP_BO_TBL,
1785 x_return_status OUT NOCOPY VARCHAR2,
1786 x_msg_count OUT NOCOPY NUMBER,
1787 x_msg_data OUT NOCOPY VARCHAR2
1788 ) is
1789 l_debug_prefix VARCHAR2(30) := '';
1790
1791 cursor c1 is
1792 SELECT HZ_SMS_CP_BO(
1793 P_ACTION_TYPE,
1794 NULL, -- COMMON_OBJ_ID
1795 CONTACT_POINT_ID,
1796 NULL, -- ORIG_SYSTEM,
1797 NULL, -- ORIG_SYSTEM_REFERENCE,
1798 STATUS,
1799 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
1800 OWNER_TABLE_ID,
1801 PRIMARY_FLAG,
1802 ATTRIBUTE_CATEGORY,
1803 ATTRIBUTE1,
1804 ATTRIBUTE2,
1805 ATTRIBUTE3,
1806 ATTRIBUTE4,
1807 ATTRIBUTE5,
1808 ATTRIBUTE6,
1809 ATTRIBUTE7,
1810 ATTRIBUTE8,
1811 ATTRIBUTE9,
1812 ATTRIBUTE10,
1813 ATTRIBUTE11,
1814 ATTRIBUTE12,
1815 ATTRIBUTE13,
1816 ATTRIBUTE14,
1817 ATTRIBUTE15,
1818 ATTRIBUTE16,
1819 ATTRIBUTE17,
1820 ATTRIBUTE18,
1821 ATTRIBUTE19,
1822 ATTRIBUTE20,
1823 CONTACT_POINT_PURPOSE,
1824 PRIMARY_BY_PURPOSE,
1825 PROGRAM_UPDATE_DATE,
1826 CREATED_BY_MODULE,
1827 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1828 CREATION_DATE,
1829 LAST_UPDATE_DATE,
1830 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1831 ACTUAL_CONTENT_SOURCE,
1832 PHONE_CALLING_CALENDAR,
1833 LAST_CONTACT_DT_TIME,
1834 TIMEZONE_ID,
1835 PHONE_AREA_CODE,
1836 PHONE_COUNTRY_CODE,
1837 PHONE_NUMBER,
1838 PHONE_EXTENSION,
1839 PHONE_LINE_TYPE,
1840 RAW_PHONE_NUMBER,
1841 CAST(MULTISET (
1842 SELECT HZ_ORIG_SYS_REF_OBJ(
1843 NULL, --P_ACTION_TYPE,
1844 ORIG_SYSTEM_REF_ID,
1845 ORIG_SYSTEM,
1846 ORIG_SYSTEM_REFERENCE,
1847 OWNER_TABLE_NAME,
1848 OWNER_TABLE_ID,
1849 STATUS,
1850 REASON_CODE,
1851 OLD_ORIG_SYSTEM_REFERENCE,
1852 START_DATE_ACTIVE,
1853 END_DATE_ACTIVE,
1854 PROGRAM_UPDATE_DATE,
1855 CREATED_BY_MODULE,
1856 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1857 CREATION_DATE,
1858 LAST_UPDATE_DATE,
1859 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
1860 ATTRIBUTE_CATEGORY,
1861 ATTRIBUTE1,
1862 ATTRIBUTE2,
1863 ATTRIBUTE3,
1864 ATTRIBUTE4,
1865 ATTRIBUTE5,
1866 ATTRIBUTE6,
1867 ATTRIBUTE7,
1868 ATTRIBUTE8,
1869 ATTRIBUTE9,
1870 ATTRIBUTE10,
1871 ATTRIBUTE11,
1872 ATTRIBUTE12,
1873 ATTRIBUTE13,
1874 ATTRIBUTE14,
1875 ATTRIBUTE15,
1876 ATTRIBUTE16,
1877 ATTRIBUTE17,
1878 ATTRIBUTE18,
1879 ATTRIBUTE19,
1880 ATTRIBUTE20)
1881 FROM HZ_ORIG_SYS_REFERENCES OSR
1882 WHERE
1883 OSR.OWNER_TABLE_ID = CP.CONTACT_POINT_ID
1884 AND OWNER_TABLE_NAME = 'HZ_CONTACT_POINTS'
1885 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
1886 CAST(MULTISET (
1887 SELECT HZ_CONTACT_PREF_OBJ(
1888 P_ACTION_TYPE,
1889 NULL, -- COMMON_OBJ_ID
1890 CONTACT_PREFERENCE_ID,
1891 'SMS',
1892 CONTACT_LEVEL_TABLE_ID,
1893 CONTACT_TYPE,
1894 PREFERENCE_CODE,
1895 PREFERENCE_TOPIC_TYPE,
1896 PREFERENCE_TOPIC_TYPE_ID,
1897 PREFERENCE_TOPIC_TYPE_CODE,
1898 PREFERENCE_START_DATE,
1899 PREFERENCE_END_DATE,
1900 PREFERENCE_START_TIME_HR,
1901 PREFERENCE_END_TIME_HR,
1902 PREFERENCE_START_TIME_MI,
1903 PREFERENCE_END_TIME_MI,
1904 MAX_NO_OF_INTERACTIONS,
1905 MAX_NO_OF_INTERACT_UOM_CODE,
1906 REQUESTED_BY,
1907 REASON_CODE,
1908 STATUS,
1909 PROGRAM_UPDATE_DATE,
1910 CREATED_BY_MODULE,
1911 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
1912 CREATION_DATE,
1913 LAST_UPDATE_DATE,
1914 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
1915 FROM HZ_CONTACT_PREFERENCES CPREF
1916 WHERE CONTACT_LEVEL_TABLE = 'HZ_CONTACT_POINTS'
1917 AND CONTACT_LEVEL_TABLE_ID = CP.CONTACT_POINT_ID) AS HZ_CONTACT_PREF_OBJ_TBL))
1918 FROM HZ_CONTACT_POINTS CP WHERE CONTACT_POINT_TYPE = 'SMS'
1919 AND ((P_SMS_ID IS NULL AND OWNER_TABLE_NAME = P_PARENT_TABLE_NAME
1920 AND OWNER_TABLE_ID = P_PARENT_ID)
1921 OR (P_SMS_ID IS NOT NULL AND CONTACT_POINT_ID = P_SMS_ID));
1922
1923
1924 BEGIN
1925
1926 -- initialize API return status to success.
1927 x_return_status := FND_API.G_RET_STS_SUCCESS;
1928
1929 -- Initialize message list if p_init_msg_list is set to TRUE
1930 IF FND_API.to_Boolean(p_init_msg_list) THEN
1931 FND_MSG_PUB.initialize;
1932 END IF;
1933
1934 -- Debug info.
1935 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1936 hz_utility_v2pub.debug(p_message=>'get_sms_bos(+)',
1937 p_prefix=>l_debug_prefix,
1938 p_msg_level=>fnd_log.level_procedure);
1939 END IF;
1940
1941
1942
1943 x_sms_objs := HZ_SMS_CP_BO_TBL();
1944 open c1;
1945 fetch c1 BULK COLLECT into x_sms_objs;
1946 close c1;
1947
1948 -- Debug info.
1949 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1950 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1951 p_msg_data=>x_msg_data,
1952 p_msg_type=>'WARNING',
1953 p_msg_level=>fnd_log.level_exception);
1954 END IF;
1955
1956 -- Debug info.
1957 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1958 hz_utility_v2pub.debug(p_message=>'get_sms_bos (-)',
1959 p_prefix=>l_debug_prefix,
1960 p_msg_level=>fnd_log.level_procedure);
1961 END IF;
1962
1963 EXCEPTION
1964
1965 WHEN fnd_api.g_exc_error THEN
1966 x_return_status := fnd_api.g_ret_sts_error;
1967
1968 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1969 p_count => x_msg_count,
1970 p_data => x_msg_data);
1971
1972 -- Debug info.
1973 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1974 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1975 p_msg_data=>x_msg_data,
1976 p_msg_type=>'ERROR',
1977 p_msg_level=>fnd_log.level_error);
1978 END IF;
1979 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1980 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
1981 p_prefix=>l_debug_prefix,
1982 p_msg_level=>fnd_log.level_procedure);
1983 END IF;
1984 WHEN fnd_api.g_exc_unexpected_error THEN
1985 x_return_status := fnd_api.g_ret_sts_unexp_error;
1986
1987 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1988 p_count => x_msg_count,
1989 p_data => x_msg_data);
1990
1991 -- Debug info.
1992 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1993 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1994 p_msg_data=>x_msg_data,
1995 p_msg_type=>'UNEXPECTED ERROR',
1996 p_msg_level=>fnd_log.level_error);
1997 END IF;
1998 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1999 hz_utility_v2pub.debug(p_message=>'get_sms_bos (-)',
2000 p_prefix=>l_debug_prefix,
2001 p_msg_level=>fnd_log.level_procedure);
2002 END IF;
2003 WHEN OTHERS THEN
2004 x_return_status := fnd_api.g_ret_sts_unexp_error;
2005
2006 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2007 fnd_message.set_token('ERROR' ,SQLERRM);
2008 fnd_msg_pub.add;
2009
2010 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2011 p_count => x_msg_count,
2012 p_data => x_msg_data);
2013
2014 -- Debug info.
2015 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2016 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2017 p_msg_data=>x_msg_data,
2018 p_msg_type=>'SQL ERROR',
2019 p_msg_level=>fnd_log.level_error);
2020 END IF;
2021 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2022 hz_utility_v2pub.debug(p_message=>'get_eft_bos (-)',
2023 p_prefix=>l_debug_prefix,
2024 p_msg_level=>fnd_log.level_procedure);
2025 END IF;
2026
2027
2028 end;
2029
2030 PROCEDURE get_cont_pref_objs(
2031 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2032 p_cont_level_table_id IN NUMBER,
2033 p_cont_level_table IN VARCHAR2,
2034 p_contact_type IN VARCHAR2,
2035 p_action_type IN VARCHAR2 := NULL,
2036 x_cont_pref_objs OUT NOCOPY HZ_CONTACT_PREF_OBJ_TBL,
2037 x_return_status OUT NOCOPY VARCHAR2,
2038 x_msg_count OUT NOCOPY NUMBER,
2039 x_msg_data OUT NOCOPY VARCHAR2
2040 ) is
2041 i BINARY_INTEGER := 1;
2042 l_debug_prefix VARCHAR2(30) := '';
2043
2044 cursor c1 is
2045 select HZ_CONTACT_PREF_OBJ(
2046 P_ACTION_TYPE,
2047 NULL, -- COMMON_OBJ_ID
2048 CONTACT_PREFERENCE_ID,
2049 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(CONTACT_LEVEL_TABLE,CONTACT_LEVEL_TABLE_ID),
2050 CONTACT_LEVEL_TABLE_ID,
2051 CONTACT_TYPE,
2052 PREFERENCE_CODE,
2053 PREFERENCE_TOPIC_TYPE,
2054 PREFERENCE_TOPIC_TYPE_ID,
2055 PREFERENCE_TOPIC_TYPE_CODE,
2056 PREFERENCE_START_DATE,
2057 PREFERENCE_END_DATE,
2058 PREFERENCE_START_TIME_HR,
2059 PREFERENCE_END_TIME_HR,
2060 PREFERENCE_START_TIME_MI,
2061 PREFERENCE_END_TIME_MI,
2062 MAX_NO_OF_INTERACTIONS,
2063 MAX_NO_OF_INTERACT_UOM_CODE,
2064 REQUESTED_BY,
2065 REASON_CODE,
2066 STATUS,
2067 PROGRAM_UPDATE_DATE,
2068 CREATED_BY_MODULE,
2069 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
2070 CREATION_DATE,
2071 LAST_UPDATE_DATE,
2072 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
2073 from hz_contact_preferences
2074 where contact_level_table = p_cont_level_table
2075 and contact_level_table_id = p_cont_level_table_id
2076 and contact_type = nvl(p_contact_type, contact_type);
2077
2078 begin
2079
2080 -- initialize API return status to success.
2081 x_return_status := FND_API.G_RET_STS_SUCCESS;
2082
2083 -- Initialize message list if p_init_msg_list is set to TRUE
2084 IF FND_API.to_Boolean(p_init_msg_list) THEN
2085 FND_MSG_PUB.initialize;
2086 END IF;
2087
2088 -- Debug info.
2089 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2090 hz_utility_v2pub.debug(p_message=>'get_cont_pref_objs(-)',
2091 p_prefix=>l_debug_prefix,
2092 p_msg_level=>fnd_log.level_procedure);
2093 END IF;
2094
2095 x_cont_pref_objs := HZ_CONTACT_PREF_OBJ_TBL();
2096
2097 open c1;
2098 fetch c1 BULK COLLECT into x_cont_pref_objs;
2099 close c1;
2100
2101 -- Debug info.
2102 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
2103 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2104 p_msg_data=>x_msg_data,
2105 p_msg_type=>'WARNING',
2106 p_msg_level=>fnd_log.level_exception);
2107 END IF;
2108
2109 -- Debug info.
2110 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2111 hz_utility_v2pub.debug(p_message=>'get_cont_pref_objs(-)',
2112 p_prefix=>l_debug_prefix,
2113 p_msg_level=>fnd_log.level_procedure);
2114 END IF;
2115
2116 EXCEPTION
2117
2118 WHEN OTHERS THEN
2119 x_return_status := fnd_api.g_ret_sts_unexp_error;
2120
2121 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
2122 fnd_message.set_token('ERROR' ,SQLERRM);
2123 fnd_msg_pub.add;
2124
2125 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2126 p_count => x_msg_count,
2127 p_data => x_msg_data);
2128
2129 -- Debug info.
2130 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
2131 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
2132 p_msg_data=>x_msg_data,
2133 p_msg_type=>'SQL ERROR',
2134 p_msg_level=>fnd_log.level_error);
2135 END IF;
2136 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
2137 hz_utility_v2pub.debug(p_message=>'get_cont_pref_objs(-)',
2138 p_prefix=>l_debug_prefix,
2139 p_msg_level=>fnd_log.level_procedure);
2140 END IF;
2141
2142 end;
2143
2144
2145 END HZ_EXTRACT_CONT_POINT_BO_PVT;