[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_ORG_CONT_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_ORG_CONT_BO_PVT AS
2 /*$Header: ARHEOCVB.pls 120.6 2006/10/24 23:38:31 awu noship $ */
3 /*
4 * This package contains the private APIs for org contact information.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:display name org contact
8 * @rep:category BUSINESS_ENTITY HZ_ORG_CONTACTS
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf Get APIs
11 */
12
13 -- Private procedure get_cont_per_profile_bo
14
15 function get_id(p_org_cont_id in number, p_type in varchar2 ) return number is
16
17 cursor get_cont_info_csr is
18 select r.subject_id, r.party_id
19 from hz_relationships r, hz_org_contacts oc
20 where r.relationship_id = oc.party_relationship_id
21 and oc.org_contact_id = p_org_cont_id
22 and r.subject_type = 'PERSON';
23
24 l_person_id number;
25 l_rel_party_id number;
26 begin
27 open get_cont_info_csr;
28 fetch get_cont_info_csr into l_person_id, l_rel_party_id;
29 close get_cont_info_csr;
30
31 if p_type = 'PERSON'
32 then
33 return l_person_id;
34 elsif p_type = 'REL_PARTY'
35 then
36 return l_rel_party_id;
37 end if;
38 end get_id;
39
40 PROCEDURE get_cont_per_profile_bo(
41 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
42 p_person_id IN NUMBER,
43 p_action_type IN VARCHAR2 := NULL,
44 x_person_profile_obj OUT NOCOPY HZ_PERSON_PROFILE_OBJ,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2
48 ) is
49
50 CURSOR C1 IS
51 SELECT HZ_PERSON_PROFILE_OBJ(
52 P_ACTION_TYPE,
53 NULL, -- COMMON_OBJ_ID
54 P.PARTY_ID,
55 NULL, --ORIG_SYSTEM,
56 NULL, --ORIG_SYSTEM_REFERENCE,
57 P.PARTY_NUMBER,
58 P.VALIDATED_FLAG,
59 P.STATUS,
60 P.CATEGORY_CODE,
61 P.SALUTATION,
62 P.ATTRIBUTE_CATEGORY,
63 P.ATTRIBUTE1,
64 P.ATTRIBUTE2,
65 P.ATTRIBUTE3,
66 P.ATTRIBUTE4,
67 P.ATTRIBUTE5,
68 P.ATTRIBUTE6,
69 P.ATTRIBUTE7,
70 P.ATTRIBUTE8,
71 P.ATTRIBUTE9,
72 P.ATTRIBUTE10,
73 P.ATTRIBUTE11,
74 P.ATTRIBUTE12,
75 P.ATTRIBUTE13,
76 P.ATTRIBUTE14,
77 P.ATTRIBUTE15,
78 P.ATTRIBUTE16,
79 P.ATTRIBUTE17,
80 P.ATTRIBUTE18,
81 P.ATTRIBUTE19,
82 P.ATTRIBUTE20,
83 P.ATTRIBUTE21,
84 P.ATTRIBUTE22,
85 P.ATTRIBUTE23,
86 P.ATTRIBUTE24,
87 PRO.PERSON_PRE_NAME_ADJUNCT,
88 PRO.PERSON_FIRST_NAME,
89 PRO.PERSON_MIDDLE_NAME,
90 PRO.PERSON_LAST_NAME,
91 PRO.PERSON_NAME_SUFFIX,
92 PRO.PERSON_TITLE,
93 PRO.PERSON_ACADEMIC_TITLE,
94 PRO.PERSON_PREVIOUS_LAST_NAME,
95 PRO.PERSON_INITIALS,
96 PRO.KNOWN_AS,
97 PRO.KNOWN_AS2,
98 PRO.KNOWN_AS3,
99 PRO.KNOWN_AS4,
100 PRO.KNOWN_AS5,
101 PRO.PERSON_NAME_PHONETIC,
102 PRO.PERSON_FIRST_NAME_PHONETIC,
103 PRO.PERSON_LAST_NAME_PHONETIC,
104 PRO.MIDDLE_NAME_PHONETIC,
105 PRO.TAX_REFERENCE,
106 PRO.JGZZ_FISCAL_CODE,
107 PRO.PERSON_IDEN_TYPE,
108 PRO.PERSON_IDENTIFIER,
109 PRO.DATE_OF_BIRTH,
110 PRO.PLACE_OF_BIRTH,
111 PRO.DATE_OF_DEATH,
112 PRO.DECEASED_FLAG,
113 PRO.GENDER,
114 PRO.DECLARED_ETHNICITY,
115 PRO.MARITAL_STATUS,
116 MARITAL_STATUS_EFFECTIVE_DATE,
117 PRO.PERSONAL_INCOME,
118 PRO.HEAD_OF_HOUSEHOLD_FLAG,
119 PRO.HOUSEHOLD_INCOME,
120 PRO.HOUSEHOLD_SIZE,
121 PRO.RENT_OWN_IND,
122 PRO.LAST_KNOWN_GPS,
123 PRO.INTERNAL_FLAG,
124 PRO.PROGRAM_UPDATE_DATE,
125 PRO.CREATED_BY_MODULE,
126 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.CREATED_BY),
127 PRO.CREATION_DATE,
128 PRO.LAST_UPDATE_DATE,
129 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.LAST_UPDATED_BY),
130 PRO.ACTUAL_CONTENT_SOURCE,
131 CAST(MULTISET (
132 SELECT HZ_ORIG_SYS_REF_OBJ(
133 NULL, --P_ACTION_TYPE,
134 ORIG_SYSTEM_REF_ID,
135 ORIG_SYSTEM,
136 ORIG_SYSTEM_REFERENCE,
137 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type(OWNER_TABLE_NAME,OWNER_TABLE_ID),
138 OWNER_TABLE_ID,
139 STATUS,
140 REASON_CODE,
141 OLD_ORIG_SYSTEM_REFERENCE,
142 START_DATE_ACTIVE,
143 END_DATE_ACTIVE,
144 PROGRAM_UPDATE_DATE,
145 CREATED_BY_MODULE,
146 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
147 CREATION_DATE,
148 LAST_UPDATE_DATE,
149 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
150 ATTRIBUTE_CATEGORY,
151 ATTRIBUTE1,
152 ATTRIBUTE2,
153 ATTRIBUTE3,
154 ATTRIBUTE4,
155 ATTRIBUTE5,
156 ATTRIBUTE6,
157 ATTRIBUTE7,
158 ATTRIBUTE8,
159 ATTRIBUTE9,
160 ATTRIBUTE10,
161 ATTRIBUTE11,
162 ATTRIBUTE12,
163 ATTRIBUTE13,
164 ATTRIBUTE14,
165 ATTRIBUTE15,
166 ATTRIBUTE16,
167 ATTRIBUTE17,
168 ATTRIBUTE18,
169 ATTRIBUTE19,
170 ATTRIBUTE20)
171 FROM HZ_ORIG_SYS_REFERENCES OSR
172 WHERE
173 OSR.OWNER_TABLE_ID = PRO.PARTY_ID
174 AND OWNER_TABLE_NAME = 'HZ_PARTIES'
175 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL),
176 HZ_EXT_ATTRIBUTE_OBJ_TBL())
177 FROM HZ_PERSON_PROFILES PRO, HZ_PARTIES P
178 WHERE PRO.PARTY_ID = P.PARTY_ID AND P.PARTY_ID = P_PERSON_ID
179 AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE);
180
181 l_debug_prefix VARCHAR2(30) := '';
182
183 BEGIN
184
185
186 -- initialize API return status to success.
187 x_return_status := FND_API.G_RET_STS_SUCCESS;
188
189 -- Initialize message list if p_init_msg_list is set to TRUE
190 IF FND_API.to_Boolean(p_init_msg_list) THEN
191 FND_MSG_PUB.initialize;
192 END IF;
193
194
195 -- Debug info.
196 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
197 hz_utility_v2pub.debug(p_message=>'get_cont_per_profile_bo(+)',
198 p_prefix=>l_debug_prefix,
199 p_msg_level=>fnd_log.level_procedure);
200 END IF;
201
202 open c1;
203 fetch c1 into x_person_profile_obj;
204 close c1;
205
206 -- Debug info.
207 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
208 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
209 p_msg_data=>x_msg_data,
210 p_msg_type=>'WARNING',
211 p_msg_level=>fnd_log.level_exception);
212 END IF;
213
214 -- Debug info.
215 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
216 hz_utility_v2pub.debug(p_message=>'get_cont_per_profile_bo (-)',
217 p_prefix=>l_debug_prefix,
218 p_msg_level=>fnd_log.level_procedure);
219 END IF;
220
221
222 EXCEPTION
223
224 WHEN fnd_api.g_exc_error THEN
225 x_return_status := fnd_api.g_ret_sts_error;
226
227 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
228 p_count => x_msg_count,
229 p_data => x_msg_data);
230
231 -- Debug info.
232 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
233 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
234 p_msg_data=>x_msg_data,
235 p_msg_type=>'ERROR',
236 p_msg_level=>fnd_log.level_error);
237 END IF;
238 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
239 hz_utility_v2pub.debug(p_message=>'get_cont_per_profile_bo (-)',
240 p_prefix=>l_debug_prefix,
241 p_msg_level=>fnd_log.level_procedure);
242 END IF;
243 WHEN fnd_api.g_exc_unexpected_error THEN
244 x_return_status := fnd_api.g_ret_sts_unexp_error;
245
246 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
247 p_count => x_msg_count,
248 p_data => x_msg_data);
249
250 -- Debug info.
251 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
252 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
253 p_msg_data=>x_msg_data,
254 p_msg_type=>'UNEXPECTED ERROR',
255 p_msg_level=>fnd_log.level_error);
256 END IF;
257 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
258 hz_utility_v2pub.debug(p_message=>'get_cont_per_profile_bo (-)',
259 p_prefix=>l_debug_prefix,
260 p_msg_level=>fnd_log.level_procedure);
261 END IF;
262 WHEN OTHERS THEN
263 x_return_status := fnd_api.g_ret_sts_unexp_error;
264
265 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
266 fnd_message.set_token('ERROR' ,SQLERRM);
267 fnd_msg_pub.add;
268
269 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
270 p_count => x_msg_count,
271 p_data => x_msg_data);
272
273 -- Debug info.
274 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
275 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
276 p_msg_data=>x_msg_data,
277 p_msg_type=>'SQL ERROR',
278 p_msg_level=>fnd_log.level_error);
279 END IF;
280 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
281 hz_utility_v2pub.debug(p_message=>'get_cont_per_profile_bo (-)',
282 p_prefix=>l_debug_prefix,
283 p_msg_level=>fnd_log.level_procedure);
284 END IF;
285
286 end;
287
288 PROCEDURE get_org_contact_role_bos(
289 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
290 p_org_cont_id IN NUMBER,
291 p_action_type IN VARCHAR2 := NULL,
292 x_org_cont_role_objs OUT NOCOPY HZ_ORG_CONTACT_ROLE_OBJ_TBL,
293 x_return_status OUT NOCOPY VARCHAR2,
294 x_msg_count OUT NOCOPY NUMBER,
295 x_msg_data OUT NOCOPY VARCHAR2
296 ) is
297
298 CURSOR C1 IS
299 SELECT HZ_ORG_CONTACT_ROLE_OBJ(
300 P_ACTION_TYPE,
301 NULL, -- COMMON_OBJ_ID
302 ORG_CONTACT_ROLE_ID,
303 NULL, --ORIG_SYSTEM,
304 NULL, --ORIG_SYSTEM_REFERENCE,
305 ROLE_TYPE,
306 PRIMARY_FLAG,
307 ORG_CONTACT_ID,
308 ROLE_LEVEL,
309 PRIMARY_CONTACT_PER_ROLE_TYPE,
310 STATUS,
311 PROGRAM_UPDATE_DATE,
312 CREATED_BY_MODULE,
313 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
314 CREATION_DATE,
315 LAST_UPDATE_DATE,
316 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
317 CAST(MULTISET (
318 SELECT HZ_ORIG_SYS_REF_OBJ(
319 NULL, --P_ACTION_TYPE,
320 ORIG_SYSTEM_REF_ID,
321 ORIG_SYSTEM,
322 ORIG_SYSTEM_REFERENCE,
323 'ORG_CONTACT', -- parent_object_type
324 OWNER_TABLE_ID,
325 STATUS,
326 REASON_CODE,
327 OLD_ORIG_SYSTEM_REFERENCE,
328 START_DATE_ACTIVE,
329 END_DATE_ACTIVE,
330 PROGRAM_UPDATE_DATE,
331 CREATED_BY_MODULE,
332 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
333 CREATION_DATE,
334 LAST_UPDATE_DATE,
335 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
336 ATTRIBUTE_CATEGORY,
337 ATTRIBUTE1,
338 ATTRIBUTE2,
339 ATTRIBUTE3,
340 ATTRIBUTE4,
341 ATTRIBUTE5,
342 ATTRIBUTE6,
343 ATTRIBUTE7,
344 ATTRIBUTE8,
345 ATTRIBUTE9,
346 ATTRIBUTE10,
347 ATTRIBUTE11,
348 ATTRIBUTE12,
349 ATTRIBUTE13,
350 ATTRIBUTE14,
351 ATTRIBUTE15,
352 ATTRIBUTE16,
353 ATTRIBUTE17,
354 ATTRIBUTE18,
355 ATTRIBUTE19,
356 ATTRIBUTE20)
357 FROM HZ_ORIG_SYS_REFERENCES OSR
358 WHERE
359 OSR.OWNER_TABLE_ID = OCR.ORG_CONTACT_ROLE_ID
360 AND OWNER_TABLE_NAME = 'HZ_ORG_CONTACT_ROLES'
361 AND STATUS = 'A') AS HZ_ORIG_SYS_REF_OBJ_TBL))
362 FROM HZ_ORG_CONTACT_ROLES OCR
363 WHERE ORG_CONTACT_ID = P_ORG_CONT_ID;
364
365 l_debug_prefix VARCHAR2(30) := '';
366
367 BEGIN
368
369
370 -- initialize API return status to success.
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 -- Initialize message list if p_init_msg_list is set to TRUE
374 IF FND_API.to_Boolean(p_init_msg_list) THEN
375 FND_MSG_PUB.initialize;
376 END IF;
377
378
379 -- Debug info.
380 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
381 hz_utility_v2pub.debug(p_message=>'get_org_contact_role_bos(+)',
382 p_prefix=>l_debug_prefix,
383 p_msg_level=>fnd_log.level_procedure);
384 END IF;
385
386 x_org_cont_role_objs := HZ_ORG_CONTACT_ROLE_OBJ_TBL();
387 open c1;
388 fetch c1 BULK COLLECT into x_org_cont_role_objs;
389 close c1;
390
391 -- Debug info.
392 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
393 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
394 p_msg_data=>x_msg_data,
395 p_msg_type=>'WARNING',
396 p_msg_level=>fnd_log.level_exception);
397 END IF;
398
399 -- Debug info.
400 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
401 hz_utility_v2pub.debug(p_message=>'get_org_contact_role_bos (-)',
402 p_prefix=>l_debug_prefix,
403 p_msg_level=>fnd_log.level_procedure);
404 END IF;
405
406
407 EXCEPTION
408
409 WHEN fnd_api.g_exc_error THEN
410 x_return_status := fnd_api.g_ret_sts_error;
411
412 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
413 p_count => x_msg_count,
414 p_data => x_msg_data);
415
416 -- Debug info.
417 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
418 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
419 p_msg_data=>x_msg_data,
420 p_msg_type=>'ERROR',
421 p_msg_level=>fnd_log.level_error);
422 END IF;
423 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
424 hz_utility_v2pub.debug(p_message=>'get_org_contact_role_bos (-)',
425 p_prefix=>l_debug_prefix,
426 p_msg_level=>fnd_log.level_procedure);
427 END IF;
428 WHEN fnd_api.g_exc_unexpected_error THEN
429 x_return_status := fnd_api.g_ret_sts_unexp_error;
430
431 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
432 p_count => x_msg_count,
433 p_data => x_msg_data);
434
435 -- Debug info.
436 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
437 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
438 p_msg_data=>x_msg_data,
439 p_msg_type=>'UNEXPECTED ERROR',
440 p_msg_level=>fnd_log.level_error);
441 END IF;
442 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
443 hz_utility_v2pub.debug(p_message=>'get_org_contact_role_bos (-)',
444 p_prefix=>l_debug_prefix,
445 p_msg_level=>fnd_log.level_procedure);
446 END IF;
447 WHEN OTHERS THEN
448 x_return_status := fnd_api.g_ret_sts_unexp_error;
449
450 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
451 fnd_message.set_token('ERROR' ,SQLERRM);
452 fnd_msg_pub.add;
453
454 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
455 p_count => x_msg_count,
456 p_data => x_msg_data);
457
458 -- Debug info.
459 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
460 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
461 p_msg_data=>x_msg_data,
462 p_msg_type=>'SQL ERROR',
463 p_msg_level=>fnd_log.level_error);
464 END IF;
465 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
466 hz_utility_v2pub.debug(p_message=>'get_org_contact_role_bos (-)',
467 p_prefix=>l_debug_prefix,
468 p_msg_level=>fnd_log.level_procedure);
469 END IF;
470
471 end;
472
473
474
475 --------------------------------------
476 --
477 -- PROCEDURE get_org_contact_bos
478 --
479 -- DESCRIPTION
480 -- Get org contact information.
481 --
482 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
483 --
484 -- ARGUMENTS
485 -- IN:
486 -- p_init_msg_list Initialize message stack if it is set to FND_API.G_TRUE. Default is FND_API.G_FALSE.
487 -- p_organization_id Org Contact Org id.
488 --
489 -- OUT:
490 -- x_org contact_objs Table of org contact objects.
491 -- x_return_status Return status after the call. The status can
492 -- be fnd_api.g_ret_sts_success (success),
493 -- fnd_api.g_ret_sts_error (error),
494 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
495 -- x_msg_count Number of messages in message stack.
496 -- x_msg_data Message text if x_msg_count is 1.
497 --
498 -- NOTES
499 --
500 -- MODIFICATION HISTORY
501 --
502 --
503 -- 15-June-2005 AWU Created.
504 --
505
506
507
508 PROCEDURE get_org_contact_bos(
509 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
510 p_organization_id IN NUMBER,
511 p_org_contact_id IN NUMBER := NULL,
512 p_action_type IN VARCHAR2 := NULL,
513 x_org_contact_objs OUT NOCOPY HZ_ORG_CONTACT_BO_TBL,
514 x_return_status OUT NOCOPY VARCHAR2,
515 x_msg_count OUT NOCOPY NUMBER,
516 x_msg_data OUT NOCOPY VARCHAR2
517 ) is
518
519 CURSOR C1 IS
520 SELECT HZ_ORG_CONTACT_BO(
521 P_ACTION_TYPE,
522 NULL, -- COMMON_OBJ_ID
523 OC.ORG_CONTACT_ID,
524 P.PARTY_ID,
525 NULL, --ORIG_SYSTEM,
526 NULL, --ORIG_SYSTEM_REFERENCE,
527 OC.COMMENTS,
528 OC.CONTACT_NUMBER,
529 OC.DEPARTMENT_CODE,
530 OC.DEPARTMENT,
531 OC.TITLE,
532 OC.JOB_TITLE,
533 OC.DECISION_MAKER_FLAG,
534 OC.JOB_TITLE_CODE,
535 OC.REFERENCE_USE_FLAG,
536 OC.RANK,
537 OC.PARTY_SITE_ID,
538 OC.ATTRIBUTE_CATEGORY,
539 OC.ATTRIBUTE1,
540 OC.ATTRIBUTE2,
541 OC.ATTRIBUTE3,
542 OC.ATTRIBUTE4,
543 OC.ATTRIBUTE5,
544 OC.ATTRIBUTE6,
545 OC.ATTRIBUTE7,
546 OC.ATTRIBUTE8,
547 OC.ATTRIBUTE9,
548 OC.ATTRIBUTE10,
549 OC.ATTRIBUTE11,
550 OC.ATTRIBUTE12,
551 OC.ATTRIBUTE13,
552 OC.ATTRIBUTE14,
553 OC.ATTRIBUTE15,
554 OC.ATTRIBUTE16,
555 OC.ATTRIBUTE17,
556 OC.ATTRIBUTE18,
557 OC.ATTRIBUTE19,
558 OC.ATTRIBUTE20,
559 OC.ATTRIBUTE21,
560 OC.ATTRIBUTE22,
561 OC.ATTRIBUTE23,
562 OC.ATTRIBUTE24,
563 OC.PROGRAM_UPDATE_DATE,
564 OC.CREATED_BY_MODULE,
565 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OC.CREATED_BY),
566 OC.CREATION_DATE,
567 OC.LAST_UPDATE_DATE,
568 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(OC.LAST_UPDATED_BY),
569 R.RELATIONSHIP_CODE,
570 R.RELATIONSHIP_TYPE,
571 R.COMMENTS,
572 R.START_DATE,
573 R.END_DATE,
574 R.STATUS,
575 HZ_ORIG_SYS_REF_OBJ_TBL(),
576 NULL, --PERSON_PROFILE_OBJ,
577 HZ_ORG_CONTACT_ROLE_OBJ_TBL(),
578 HZ_PARTY_SITE_BO_TBL(),
579 HZ_PHONE_CP_BO_TBL(),
580 HZ_TELEX_CP_BO_TBL(),
581 HZ_EMAIL_CP_BO_TBL(),
582 HZ_WEB_CP_BO_TBL(),
583 HZ_SMS_CP_BO_TBL(),
584 HZ_CONTACT_PREF_OBJ_TBL())
585 FROM HZ_ORG_CONTACTS OC, HZ_PARTIES P, HZ_RELATIONSHIPS R
586 WHERE OC.PARTY_RELATIONSHIP_ID = R.RELATIONSHIP_ID
587 AND R.SUBJECT_ID = P.PARTY_ID
588 AND R.OBJECT_TYPE = 'PERSON'
589 AND ((P_ORG_CONTACT_ID IS NULL AND P.PARTY_ID = P_ORGANIZATION_ID)
590 OR (P_ORG_CONTACT_ID IS NOT NULL AND OC.ORG_CONTACT_ID = P_ORG_CONTACT_ID));
591
592 l_debug_prefix VARCHAR2(30) := '';
593 l_rel_party_id number;
594
595 BEGIN
596
597
598 -- initialize API return status to success.
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600
601 -- Initialize message list if p_init_msg_list is set to TRUE
602 IF FND_API.to_Boolean(p_init_msg_list) THEN
603 FND_MSG_PUB.initialize;
604 END IF;
605
606
607 -- Debug info.
608 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
609 hz_utility_v2pub.debug(p_message=>'get_org_contact_bos(+)',
610 p_prefix=>l_debug_prefix,
611 p_msg_level=>fnd_log.level_procedure);
612 END IF;
613
614 x_org_contact_objs := HZ_ORG_CONTACT_BO_TBL();
615 open c1;
616 fetch c1 BULK COLLECT into x_org_contact_objs;
617 close c1;
618
619
620 for i in 1..x_org_contact_objs.count loop
621
622 get_cont_per_profile_bo(
623 p_init_msg_list => fnd_api.g_false,
624 p_person_id => get_id(x_org_contact_objs(i).org_contact_id,'PERSON'),
625 p_action_type => p_action_type,
626 x_person_profile_obj => x_org_contact_objs(i).person_profile_obj,
627 x_return_status => x_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data);
630
631 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
632 RAISE FND_API.G_EXC_ERROR;
633 END IF;
634
635 get_org_contact_role_bos(
636 p_init_msg_list => fnd_api.g_false,
637 p_org_cont_id => x_org_contact_objs(i).org_contact_id,
638 p_action_type => p_action_type,
639 x_org_cont_role_objs => x_org_contact_objs(i).org_contact_role_objs,
640 x_return_status => x_return_status,
641 x_msg_count => x_msg_count,
642 x_msg_data => x_msg_data);
643
644 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
645 RAISE FND_API.G_EXC_ERROR;
646 END IF;
647
648 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
649 (p_init_msg_list => fnd_api.g_false,
650 p_owner_table_id => x_org_contact_objs(i).org_contact_id,
651 p_owner_table_name => 'HZ_ORG_CONTACTS',
652 p_action_type => NULL, --p_action_type,
653 x_orig_sys_ref_objs => x_org_contact_objs(i).orig_sys_objs,
654 x_return_status => x_return_status,
655 x_msg_count => x_msg_count,
656 x_msg_data => x_msg_data);
657
658 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
659 RAISE FND_API.G_EXC_ERROR;
660 END IF;
661
662 l_rel_party_id := get_id(x_org_contact_objs(i).org_contact_id,'REL_PARTY');
663
664 HZ_EXTRACT_PARTY_SITE_BO_PVT.get_party_site_bos
665 (p_init_msg_list => fnd_api.g_false,
666 p_party_id => l_rel_party_id,
667 p_party_site_id => NULL,
668 p_action_type => p_action_type,
669 x_party_site_objs => x_org_contact_objs(i).party_site_objs,
670 x_return_status => x_return_status,
671 x_msg_count => x_msg_count,
672 x_msg_data => x_msg_data);
673
674 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
675 RAISE FND_API.G_EXC_ERROR;
676 END IF;
677
678
679 hz_extract_cont_point_bo_pvt.get_phone_bos
680 (p_init_msg_list => fnd_api.g_false,
681 p_phone_id => null,
682 p_parent_id => l_rel_party_id,
683 p_parent_table_name => 'HZ_PARTIES',
684 p_action_type => p_action_type,
685 x_phone_objs => x_org_contact_objs(i).phone_objs,
686 x_return_status => x_return_status,
687 x_msg_count => x_msg_count,
688 x_msg_data => x_msg_data);
689
690 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693
694
695 hz_extract_cont_point_bo_pvt.get_email_bos
696 (p_init_msg_list => fnd_api.g_false,
697 p_email_id => null,
698 p_parent_id => l_rel_party_id,
699 p_parent_table_name => 'HZ_PARTIES',
700 p_action_type => p_action_type,
701 x_email_objs => x_org_contact_objs(i).email_objs,
702 x_return_status => x_return_status,
703 x_msg_count => x_msg_count,
704 x_msg_data => x_msg_data);
705
706 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709
710
711 hz_extract_cont_point_bo_pvt.get_telex_bos
712 (p_init_msg_list => fnd_api.g_false,
713 p_telex_id => null,
714 p_parent_id => l_rel_party_id,
715 p_parent_table_name => 'HZ_PARTIES',
716 x_telex_objs => x_org_contact_objs(i).telex_objs,
717 x_return_status => x_return_status,
718 x_msg_count => x_msg_count,
719 x_msg_data => x_msg_data);
720
721 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
722 RAISE FND_API.G_EXC_ERROR;
723 END IF;
724
725
726 hz_extract_cont_point_bo_pvt.get_web_bos
727 (p_init_msg_list => fnd_api.g_false,
728 p_web_id => null,
729 p_parent_id => l_rel_party_id,
730 p_parent_table_name => 'HZ_PARTIES',
731 p_action_type => p_action_type,
732 x_web_objs => x_org_contact_objs(i).web_objs,
733 x_return_status => x_return_status,
734 x_msg_count => x_msg_count,
735 x_msg_data => x_msg_data);
736
737 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
738 RAISE FND_API.G_EXC_ERROR;
739 END IF;
740
741
742 hz_extract_cont_point_bo_pvt.get_sms_bos
743 (p_init_msg_list => fnd_api.g_false,
744 p_sms_id => null,
745 p_parent_id => l_rel_party_id,
746 p_parent_table_name => 'HZ_PARTIES',
747 p_action_type => p_action_type,
748 x_sms_objs => x_org_contact_objs(i).sms_objs,
749 x_return_status => x_return_status,
750 x_msg_count => x_msg_count,
751 x_msg_data => x_msg_data);
752
753 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
754 RAISE FND_API.G_EXC_ERROR;
755 END IF;
756
757
758 hz_extract_cont_point_bo_pvt.get_cont_pref_objs
759 (p_init_msg_list => fnd_api.g_false,
760 p_cont_level_table_id => l_rel_party_id,
761 p_cont_level_table => 'HZ_PARTIES',
762 p_contact_type => NULL,
763 p_action_type => p_action_type,
764 x_cont_pref_objs => x_org_contact_objs(i).contact_pref_objs,
765 x_return_status => x_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data);
768
769 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
770 RAISE FND_API.G_EXC_ERROR;
771 END IF;
772
773
774 end loop;
775
776 -- Debug info.
777 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
778 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
779 p_msg_data=>x_msg_data,
780 p_msg_type=>'WARNING',
781 p_msg_level=>fnd_log.level_exception);
782 END IF;
783
784 -- Debug info.
785 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
786 hz_utility_v2pub.debug(p_message=>'get_org_contact_bos (-)',
787 p_prefix=>l_debug_prefix,
788 p_msg_level=>fnd_log.level_procedure);
789 END IF;
790
791
792 EXCEPTION
793
794 WHEN fnd_api.g_exc_error THEN
795 x_return_status := fnd_api.g_ret_sts_error;
796
797 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
798 p_count => x_msg_count,
799 p_data => x_msg_data);
800
801 -- Debug info.
802 IF fnd_log.level_error>=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=>'ERROR',
806 p_msg_level=>fnd_log.level_error);
807 END IF;
808 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
809 hz_utility_v2pub.debug(p_message=>'get_org_contact_bos (-)',
810 p_prefix=>l_debug_prefix,
811 p_msg_level=>fnd_log.level_procedure);
812 END IF;
813 WHEN fnd_api.g_exc_unexpected_error THEN
814 x_return_status := fnd_api.g_ret_sts_unexp_error;
815
816 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
817 p_count => x_msg_count,
818 p_data => x_msg_data);
819
820 -- Debug info.
821 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
822 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
823 p_msg_data=>x_msg_data,
824 p_msg_type=>'UNEXPECTED ERROR',
825 p_msg_level=>fnd_log.level_error);
826 END IF;
827 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
828 hz_utility_v2pub.debug(p_message=>'get_org_contact_bos (-)',
829 p_prefix=>l_debug_prefix,
830 p_msg_level=>fnd_log.level_procedure);
831 END IF;
832 WHEN OTHERS THEN
833 x_return_status := fnd_api.g_ret_sts_unexp_error;
834
835 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
836 fnd_message.set_token('ERROR' ,SQLERRM);
837 fnd_msg_pub.add;
838
839 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
840 p_count => x_msg_count,
841 p_data => x_msg_data);
842
843 -- Debug info.
844 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
845 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
846 p_msg_data=>x_msg_data,
847 p_msg_type=>'SQL ERROR',
848 p_msg_level=>fnd_log.level_error);
849 END IF;
850 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
851 hz_utility_v2pub.debug(p_message=>'get_org_contact_bos (-)',
852 p_prefix=>l_debug_prefix,
853 p_msg_level=>fnd_log.level_procedure);
854 END IF;
855
856 end;
857
858
859
860
861 END HZ_EXTRACT_ORG_CONT_BO_PVT;