[Home] [Help]
PACKAGE BODY: APPS.HZ_EXTRACT_PERSON_BO_PVT
Source
1 PACKAGE BODY HZ_EXTRACT_PERSON_BO_PVT AS
2 /*$Header: ARHEPPVB.pls 120.11.12000000.2 2007/02/23 20:56:00 awu ship $ */
3 /*
4 * This package contains the private APIs for logical person.
5 * @rep:scope private
6 * @rep:product HZ
7 * @rep:displayname Person
8 * @rep:category BUSINESS_ENTITY HZ_PARTIES
9 * @rep:lifecycle active
10 * @rep:doccd 115hztig.pdf Person Get APIs
11 */
12
13 --------------------------------------
14 --
15 -- PROCEDURE get_person_bo
16 --
17 -- DESCRIPTION
18 -- Get a logical person.
19 --
20 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
21 --
22 -- ARGUMENTS
23 -- IN:
24 -- p_init_msg_list Initialize message stack if it is set to
25 -- p_person_id Person ID.
26 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
27 -- OUT:
28 -- x_person_obj Logical person record.
29 -- x_return_status Return status after the call. The status can
30 -- be fnd_api.g_ret_sts_success (success),
31 -- fnd_api.g_ret_sts_error (error),
32 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
33 -- x_msg_count Number of messages in message stack.
34 -- x_msg_data Message text if x_msg_count is 1.
35 --
36 -- NOTES
37 --
38 -- MODIFICATION HISTORY
39 --
40 --
41 -- 20-MAY-2005 AWU Created.
42 --
43
44 -- Private procedure get_employ_hist_bos
45
46 PROCEDURE get_employ_hist_bos(
47 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
48 p_person_id IN NUMBER,
49 p_action_type IN VARCHAR2 := NULL,
50 x_employ_hist_objs OUT NOCOPY HZ_EMPLOY_HIST_BO_TBL,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_msg_count OUT NOCOPY NUMBER,
53 x_msg_data OUT NOCOPY VARCHAR2
54 ) is
55
56 CURSOR C1 IS
57 SELECT HZ_EMPLOY_HIST_BO(
58 P_ACTION_TYPE,
59 NULL, -- COMMON_OBJ_ID
60 EMPLOYMENT_HISTORY_ID,
61 PARTY_ID,
62 BEGIN_DATE,
63 END_DATE,
64 EMPLOYMENT_TYPE_CODE,
65 EMPLOYED_AS_TITLE_CODE,
66 EMPLOYED_AS_TITLE,
67 EMPLOYED_BY_NAME_COMPANY,
68 EMPLOYED_BY_PARTY_ID,
69 EMPLOYED_BY_DIVISION_NAME,
70 SUPERVISOR_NAME,
71 BRANCH,
72 MILITARY_RANK,
73 SERVED,
74 STATION,
75 RESPONSIBILITY,
76 WEEKLY_WORK_HOURS,
77 REASON_FOR_LEAVING,
78 FACULTY_POSITION_FLAG,
79 TENURE_CODE,
80 FRACTION_OF_TENURE,
81 COMMENTS,
82 STATUS,
83 PROGRAM_UPDATE_DATE,
84 CREATED_BY_MODULE,
85 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
86 CREATION_DATE,
87 LAST_UPDATE_DATE,
88 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
89 CAST(MULTISET (
90 SELECT HZ_WORK_CLASS_OBJ(
91 P_ACTION_TYPE,
92 NULL, -- COMMON_OBJ_ID
93 WORK_CLASS_ID,
94 LEVEL_OF_EXPERIENCE,
95 WORK_CLASS_NAME,
96 EMPLOYMENT_HISTORY_ID,
97 STATUS,
98 PROGRAM_UPDATE_DATE,
99 CREATED_BY_MODULE,
100 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
101 CREATION_DATE,
102 LAST_UPDATE_DATE,
103 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
104 FROM HZ_WORK_CLASS
105 WHERE WORK_CLASS_ID = P_PERSON_ID
106 AND WORK_CLASS_NAME = 'HZ_PARTIES') AS HZ_WORK_CLASS_OBJ_TBL))
107 FROM HZ_EMPLOYMENT_HISTORY
108 WHERE PARTY_ID = P_PERSON_ID;
109
110 l_debug_prefix VARCHAR2(30) := '';
111
112 BEGIN
113
114
115 -- initialize API return status to success.
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117
118 -- Initialize message list if p_init_msg_list is set to TRUE
119 IF FND_API.to_Boolean(p_init_msg_list) THEN
120 FND_MSG_PUB.initialize;
121 END IF;
122
123
124 -- Debug info.
125 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
126 hz_utility_v2pub.debug(p_message=>'get_employ_hist_bos(+)',
127 p_prefix=>l_debug_prefix,
128 p_msg_level=>fnd_log.level_procedure);
129 END IF;
130
131 x_employ_hist_objs := HZ_EMPLOY_HIST_BO_TBL();
132 open c1;
133 fetch c1 BULK COLLECT into x_employ_hist_objs;
134 close c1;
135
136 -- Debug info.
137 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
138 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
139 p_msg_data=>x_msg_data,
140 p_msg_type=>'WARNING',
141 p_msg_level=>fnd_log.level_exception);
142 END IF;
143
144 -- Debug info.
145 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
146 hz_utility_v2pub.debug(p_message=>'get_employ_hist_bos (-)',
147 p_prefix=>l_debug_prefix,
148 p_msg_level=>fnd_log.level_procedure);
149 END IF;
150
151
152 EXCEPTION
153
154 WHEN fnd_api.g_exc_error THEN
155 x_return_status := fnd_api.g_ret_sts_error;
156
157 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
158 p_count => x_msg_count,
159 p_data => x_msg_data);
160
161 -- Debug info.
162 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
163 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
164 p_msg_data=>x_msg_data,
165 p_msg_type=>'ERROR',
166 p_msg_level=>fnd_log.level_error);
167 END IF;
168 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
169 hz_utility_v2pub.debug(p_message=>'get_employ_hist_bos (-)',
170 p_prefix=>l_debug_prefix,
171 p_msg_level=>fnd_log.level_procedure);
172 END IF;
173 WHEN fnd_api.g_exc_unexpected_error THEN
174 x_return_status := fnd_api.g_ret_sts_unexp_error;
175
176 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
177 p_count => x_msg_count,
178 p_data => x_msg_data);
179
180 -- Debug info.
181 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
182 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
183 p_msg_data=>x_msg_data,
184 p_msg_type=>'UNEXPECTED ERROR',
185 p_msg_level=>fnd_log.level_error);
186 END IF;
187 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
188 hz_utility_v2pub.debug(p_message=>'get_employ_hist_bos (-)',
189 p_prefix=>l_debug_prefix,
190 p_msg_level=>fnd_log.level_procedure);
191 END IF;
192 WHEN OTHERS THEN
193 x_return_status := fnd_api.g_ret_sts_unexp_error;
194
195 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
196 fnd_message.set_token('ERROR' ,SQLERRM);
197 fnd_msg_pub.add;
198
199 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
200 p_count => x_msg_count,
201 p_data => x_msg_data);
202
203 -- Debug info.
204 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
205 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
206 p_msg_data=>x_msg_data,
207 p_msg_type=>'SQL ERROR',
208 p_msg_level=>fnd_log.level_error);
209 END IF;
210 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
211 hz_utility_v2pub.debug(p_message=>'get_employ_hist_bos (-)',
212 p_prefix=>l_debug_prefix,
213 p_msg_level=>fnd_log.level_procedure);
214 END IF;
215
216 end;
217
218
219 /*
220 The Get Person API Procedure is a retrieval service that returns a full Person business object.
221 The user identifies a particular Person business object using the TCA identifier and/or
222 the object Source System information. Upon proper validation of the object,
223 the full Person business object is returned. The object consists of all data included within
224 the Person business object, at all embedded levels. This includes the set of all data stored
225 in the TCA tables for each embedded entity.
226
227 To retrieve the appropriate embedded business objects within the Person business object,
228 the Get procedure calls the equivalent procedure for the following embedded objects:
229
230 Embedded BO Mandatory Multiple Logical API Procedure Comments
231 Party Site N Y get_party_site_bo
232 Phone N Y get_phone_bo
233 Email N Y get_email_bo
234 Web N Y get_web_bo
235 SMS N Y get_sms_bo
236 Employment History N Y Business Structure. Included entities:HZ_EMPLOYMENT_HISTORY, HZ_WORK_CLASS
237
238
239 To retrieve the appropriate embedded entities within the Person business object,
240 the Get procedure returns all records for the particular person from these TCA entity tables:
241
242 Embedded TCA Entity Mandatory Multiple TCA Table Entities
243
244 Party,Person Profile Y N HZ_PARTIES, HZ_PERSON_PROFILES
245 Person Preference N Y HZ_PARTY_PREFERENCES
246 Relationship N Y HZ_RELATIONSHIPS
247 Classification N Y HZ_CODE_ASSIGNMENTS
248 Language N Y HZ_PERSON_LANGUAGE
249 Education N Y HZ_EDUCATION
250 Citizenship N Y HZ_CITIZENSHIP
251 Interest N Y HZ_PERSON_INTEREST
252 Certification N Y HZ_CERTIFICATIONS
253 Financial Profile N Y HZ_FINANCIAL_PROFILE
254 */
255
256
257
258 PROCEDURE get_person_bo(
259 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
260 p_person_id IN NUMBER,
261 p_action_type IN VARCHAR2 := NULL,
262 x_person_obj OUT NOCOPY HZ_PERSON_BO,
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2
266 ) is
267
268 CURSOR C1 IS
269 SELECT HZ_PERSON_BO(
270 P_ACTION_TYPE,
271 NULL, -- COMMON_OBJ_ID
272 P.PARTY_ID,
273 NULL, --ORIG_SYSTEM,
274 NULL, --ORIG_SYSTEM_REFERENCE,
275 P.PARTY_NUMBER,
276 P.VALIDATED_FLAG,
277 P.STATUS,
278 P.CATEGORY_CODE,
279 P.SALUTATION,
280 P.ATTRIBUTE_CATEGORY,
281 P.ATTRIBUTE1,
282 P.ATTRIBUTE2,
283 P.ATTRIBUTE3,
284 P.ATTRIBUTE4,
285 P.ATTRIBUTE5,
286 P.ATTRIBUTE6,
287 P.ATTRIBUTE7,
288 P.ATTRIBUTE8,
289 P.ATTRIBUTE9,
290 P.ATTRIBUTE10,
291 P.ATTRIBUTE11,
292 P.ATTRIBUTE12,
293 P.ATTRIBUTE13,
294 P.ATTRIBUTE14,
295 P.ATTRIBUTE15,
296 P.ATTRIBUTE16,
297 P.ATTRIBUTE17,
298 P.ATTRIBUTE18,
299 P.ATTRIBUTE19,
300 P.ATTRIBUTE20,
301 P.ATTRIBUTE21,
302 P.ATTRIBUTE22,
303 P.ATTRIBUTE23,
304 P.ATTRIBUTE24,
305 PRO.PERSON_PRE_NAME_ADJUNCT,
306 PRO.PERSON_FIRST_NAME,
307 PRO.PERSON_MIDDLE_NAME,
308 PRO.PERSON_LAST_NAME,
309 PRO.PERSON_NAME_SUFFIX,
310 PRO.PERSON_TITLE,
311 PRO.PERSON_ACADEMIC_TITLE,
312 PRO.PERSON_PREVIOUS_LAST_NAME,
313 PRO.PERSON_INITIALS,
314 PRO.KNOWN_AS,
315 PRO.KNOWN_AS2,
316 PRO.KNOWN_AS3,
317 PRO.KNOWN_AS4,
318 PRO.KNOWN_AS5,
319 PRO.PERSON_NAME_PHONETIC,
320 PRO.PERSON_FIRST_NAME_PHONETIC,
321 PRO.PERSON_LAST_NAME_PHONETIC,
322 PRO.MIDDLE_NAME_PHONETIC,
323 PRO.TAX_REFERENCE,
324 PRO.JGZZ_FISCAL_CODE,
325 PRO.PERSON_IDEN_TYPE,
326 PRO.PERSON_IDENTIFIER,
327 PRO.DATE_OF_BIRTH,
328 PRO.PLACE_OF_BIRTH,
329 PRO.DATE_OF_DEATH,
330 PRO.DECEASED_FLAG,
331 PRO.GENDER,
332 PRO.DECLARED_ETHNICITY,
333 PRO.MARITAL_STATUS,
334 MARITAL_STATUS_EFFECTIVE_DATE,
335 PRO.PERSONAL_INCOME,
336 PRO.HEAD_OF_HOUSEHOLD_FLAG,
337 PRO.HOUSEHOLD_INCOME,
338 PRO.HOUSEHOLD_SIZE,
339 PRO.RENT_OWN_IND,
340 PRO.LAST_KNOWN_GPS,
341 PRO.INTERNAL_FLAG,
342 PRO.PROGRAM_UPDATE_DATE,
343 PRO.CREATED_BY_MODULE,
344 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.CREATED_BY),
345 PRO.CREATION_DATE,
346 PRO.LAST_UPDATE_DATE,
347 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(PRO.LAST_UPDATED_BY),
348 PRO.ACTUAL_CONTENT_SOURCE,
349 HZ_ORIG_SYS_REF_OBJ_TBL(),
350 HZ_EXT_ATTRIBUTE_OBJ_TBL(),
351 HZ_PARTY_SITE_BO_TBL(),
352 CAST(MULTISET (
353 SELECT HZ_PARTY_PREF_OBJ(
354 P_ACTION_TYPE,
355 NULL, -- COMMON_OBJ_ID
356 PARTY_PREFERENCE_ID,
357 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
358 PARTY_ID,
359 CATEGORY,
360 PREFERENCE_CODE,
361 VALUE_VARCHAR2,
362 VALUE_NUMBER,
363 VALUE_DATE,
364 VALUE_NAME,
365 MODULE,
366 ADDITIONAL_VALUE1,
367 ADDITIONAL_VALUE2,
368 ADDITIONAL_VALUE3,
369 ADDITIONAL_VALUE4,
370 ADDITIONAL_VALUE5,
371 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
372 CREATION_DATE,
373 LAST_UPDATE_DATE,
374 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
375 FROM HZ_PARTY_PREFERENCES
376 WHERE PARTY_ID = P_PERSON_ID) AS HZ_PARTY_PREF_OBJ_TBL),
377 HZ_RELATIONSHIP_OBJ_TBL(),
378 HZ_PHONE_CP_BO_TBL(),
379 HZ_EMAIL_CP_BO_TBL(),
380 HZ_WEB_CP_BO_TBL(),
381 HZ_SMS_CP_BO_TBL(),
382 CAST(MULTISET (
383 SELECT HZ_CODE_ASSIGNMENT_OBJ(
384 P_ACTION_TYPE,
385 NULL, -- COMMON_OBJ_ID
386 CODE_ASSIGNMENT_ID,
387 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',OWNER_TABLE_ID),
388 OWNER_TABLE_ID,
389 CLASS_CATEGORY,
390 CLASS_CODE,
391 PRIMARY_FLAG,
392 ACTUAL_CONTENT_SOURCE,
393 START_DATE_ACTIVE,
394 END_DATE_ACTIVE,
395 STATUS,
396 PROGRAM_UPDATE_DATE,
397 CREATED_BY_MODULE,
398 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
399 CREATION_DATE,
400 LAST_UPDATE_DATE,
401 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY),
402 RANK)
403 FROM HZ_CODE_ASSIGNMENTS
404 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
405 AND OWNER_TABLE_ID = P_PERSON_ID) AS HZ_CODE_ASSIGNMENT_OBJ_TBL),
406 CAST(MULTISET (
407 SELECT HZ_PERSON_LANG_OBJ(
408 P_ACTION_TYPE,
409 NULL, -- COMMON_OBJ_ID
410 LANGUAGE_USE_REFERENCE_ID,
411 LANGUAGE_NAME,
412 PARTY_ID,
413 NATIVE_LANGUAGE,
414 PRIMARY_LANGUAGE_INDICATOR,
415 READS_LEVEL,
416 SPEAKS_LEVEL,
417 WRITES_LEVEL,
418 SPOKEN_COMPREHENSION_LEVEL,
419 STATUS,
420 PROGRAM_UPDATE_DATE,
421 CREATED_BY_MODULE,
422 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
423 CREATION_DATE,
424 LAST_UPDATE_DATE,
425 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
426 FROM HZ_PERSON_LANGUAGE
427 WHERE PARTY_ID = P_PERSON_ID) AS HZ_PERSON_LANG_OBJ_TBL),
428 CAST(MULTISET (
429 SELECT HZ_EDUCATION_OBJ(
430 P_ACTION_TYPE,
431 NULL, -- COMMON_OBJ_ID
432 EDUCATION_ID,
433 PARTY_ID,
434 COURSE_MAJOR,
435 DEGREE_RECEIVED,
436 START_DATE_ATTENDED,
437 LAST_DATE_ATTENDED,
438 SCHOOL_ATTENDED_NAME,
439 SCHOOL_PARTY_ID,
440 TYPE_OF_SCHOOL,
441 STATUS,
442 PROGRAM_UPDATE_DATE,
443 CREATED_BY_MODULE,
444 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
445 CREATION_DATE,
446 LAST_UPDATE_DATE,
447 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
448 FROM HZ_EDUCATION
449 WHERE PARTY_ID = P_PERSON_ID) AS HZ_EDUCATION_OBJ_TBL),
450 CAST(MULTISET (
451 SELECT HZ_CITIZENSHIP_OBJ(
452 P_ACTION_TYPE,
453 NULL, -- COMMON_OBJ_ID
454 CITIZENSHIP_ID,
455 PARTY_ID,
456 BIRTH_OR_SELECTED,
457 COUNTRY_CODE,
458 DATE_RECOGNIZED,
459 DATE_DISOWNED,
460 END_DATE,
461 DOCUMENT_TYPE,
462 DOCUMENT_REFERENCE,
463 STATUS,
464 PROGRAM_UPDATE_DATE,
465 CREATED_BY_MODULE,
466 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
467 CREATION_DATE,
468 LAST_UPDATE_DATE,
469 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
470 FROM HZ_CITIZENSHIP
471 WHERE PARTY_ID = P_PERSON_ID) AS HZ_CITIZENSHIP_OBJ_TBL),
472 HZ_EMPLOY_HIST_BO_TBL(),
473 CAST(MULTISET (
474 SELECT HZ_PERSON_INTEREST_OBJ(
475 P_ACTION_TYPE,
476 NULL, -- COMMON_OBJ_ID
477 PERSON_INTEREST_ID,
478 LEVEL_OF_INTEREST,
479 PARTY_ID,
480 LEVEL_OF_PARTICIPATION,
481 INTEREST_TYPE_CODE,
482 COMMENTS,
483 SPORT_INDICATOR,
484 SUB_INTEREST_TYPE_CODE,
485 INTEREST_NAME,
486 TEAM,
487 SINCE,
488 STATUS,
489 PROGRAM_UPDATE_DATE,
490 CREATED_BY_MODULE,
491 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
492 CREATION_DATE,
493 LAST_UPDATE_DATE,
494 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
495 FROM HZ_PERSON_INTEREST
496 WHERE PARTY_ID = P_PERSON_ID) AS HZ_PERSON_INTEREST_OBJ_TBL),
497 CAST(MULTISET (
498 SELECT HZ_CERTIFICATION_OBJ(
499 P_ACTION_TYPE,
500 NULL, -- COMMON_OBJ_ID
501 CERTIFICATION_ID,
502 CERTIFICATION_NAME,
503 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
504 PARTY_ID,
505 CURRENT_STATUS,
506 EXPIRES_ON_DATE,
507 GRADE,
508 ISSUED_BY_AUTHORITY,
509 ISSUED_ON_DATE,
510 --WH_UPDATE_DATE,
511 STATUS,
512 PROGRAM_UPDATE_DATE,
513 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
514 CREATION_DATE,
515 LAST_UPDATE_DATE,
516 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
517 FROM HZ_CERTIFICATIONS
518 WHERE PARTY_ID = P_PERSON_ID) AS HZ_CERTIFICATION_OBJ_TBL),
519 CAST(MULTISET (
520 SELECT HZ_FINANCIAL_PROF_OBJ(
521 P_ACTION_TYPE,
522 NULL, -- COMMON_OBJ_ID
523 FINANCIAL_PROFILE_ID,
524 ACCESS_AUTHORITY_DATE,
525 ACCESS_AUTHORITY_GRANTED,
526 BALANCE_AMOUNT,
527 BALANCE_VERIFIED_ON_DATE,
528 FINANCIAL_ACCOUNT_NUMBER,
529 FINANCIAL_ACCOUNT_TYPE,
530 FINANCIAL_ORG_TYPE,
531 FINANCIAL_ORGANIZATION_NAME,
532 HZ_EXTRACT_BO_UTIL_PVT.get_parent_object_type('HZ_PARTIES',PARTY_ID),
533 PARTY_ID,
534 --WH_UPDATE_DATE,
535 STATUS,
536 PROGRAM_UPDATE_DATE,
537 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(CREATED_BY),
538 CREATION_DATE,
539 LAST_UPDATE_DATE,
540 HZ_EXTRACT_BO_UTIL_PVT.GET_USER_NAME(LAST_UPDATED_BY))
541 FROM HZ_FINANCIAL_PROFILE
542 WHERE PARTY_ID = P_PERSON_ID) AS HZ_FINANCIAL_PROF_OBJ_TBL),
543 HZ_CONTACT_PREF_OBJ_TBL(),
544 HZ_PARTY_USAGE_OBJ_TBL())
545 FROM HZ_PERSON_PROFILES PRO, HZ_PARTIES P
546 WHERE PRO.PARTY_ID = P.PARTY_ID
547 AND PRO.PARTY_ID = P_PERSON_ID
548 AND SYSDATE BETWEEN EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE);
549
550 cursor get_profile_id_csr is
551 select person_profile_id
552 from HZ_PERSON_PROFILES
553 where party_id = p_person_id
554 AND sysdate between effective_start_date and nvl(effective_end_date,sysdate);
555 l_debug_prefix VARCHAR2(30) := '';
556 l_prof_id number;
557 BEGIN
558
559 -- initialize API return status to success.
560 x_return_status := FND_API.G_RET_STS_SUCCESS;
561
562 -- Initialize message list if p_init_msg_list is set to TRUE
563 IF FND_API.to_Boolean(p_init_msg_list) THEN
564 FND_MSG_PUB.initialize;
565 END IF;
566
567
568 -- Debug info.
569 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
570 hz_utility_v2pub.debug(p_message=>'get_person_bo(+)',
571 p_prefix=>l_debug_prefix,
572 p_msg_level=>fnd_log.level_procedure);
573 END IF;
574
575 open c1;
576 fetch c1 into x_person_obj;
577 close c1;
578
579 HZ_EXTRACT_ORIG_SYS_REF_BO_PVT.get_orig_sys_ref_bos
580 (p_init_msg_list => fnd_api.g_false,
581 p_owner_table_id => p_person_id,
582 p_owner_table_name => 'HZ_PARTIES',
583 p_action_type => NULL, --p_action_type,
584 x_orig_sys_ref_objs => x_person_obj.orig_sys_objs,
585 x_return_status => x_return_status,
586 x_msg_count => x_msg_count,
587 x_msg_data => x_msg_data);
588
589 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
590 RAISE FND_API.G_EXC_ERROR;
591 END IF;
592
593 HZ_EXTRACT_PARTY_USAGE_BO_PVT.get_party_usage_bos
594 (p_init_msg_list => fnd_api.g_false,
595 p_owner_table_id => p_person_id,
596 p_owner_table_name => 'HZ_PARTIES',
597 p_action_type => p_action_type,
598 x_party_usage_objs => x_person_obj.party_usage_objs,
599 x_return_status => x_return_status,
600 x_msg_count => x_msg_count,
601 x_msg_data => x_msg_data);
602
603 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
604 RAISE FND_API.G_EXC_ERROR;
605 END IF;
606
607 open get_profile_id_csr;
608 fetch get_profile_id_csr into l_prof_id;
609 close get_profile_id_csr;
610
611 hz_extract_ext_attri_bo_pvt.get_ext_attribute_bos
612 (p_init_msg_list => fnd_api.g_false,
613 p_ext_object_id => l_prof_id,
614 p_ext_object_name => 'HZ_PERSON_PROFILES',
615 p_action_type => p_action_type,
616 x_ext_attribute_objs => x_person_obj.ext_attributes_objs,
617 x_return_status => x_return_status,
618 x_msg_count => x_msg_count,
619 x_msg_data => x_msg_data);
620
621 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624
625
626
627 HZ_EXTRACT_PARTY_SITE_BO_PVT.get_party_site_bos
628 (p_init_msg_list => fnd_api.g_false,
629 p_party_id => p_person_id,
630 p_party_site_id => NULL,
631 p_action_type => p_action_type,
632 x_party_site_objs => x_person_obj.party_site_objs,
633 x_return_status => x_return_status,
634 x_msg_count => x_msg_count,
635 x_msg_data => x_msg_data);
636
637 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
638 RAISE FND_API.G_EXC_ERROR;
639 END IF;
640
641 HZ_EXTRACT_RELATIONSHIP_BO_PVT.get_relationship_bos
642 (p_init_msg_list => fnd_api.g_false,
643 p_subject_id => p_person_id,
644 p_action_type => p_action_type,
645 x_relationship_objs => x_person_obj.relationship_objs,
646 x_return_status => x_return_status,
647 x_msg_count => x_msg_count,
648 x_msg_data => x_msg_data);
649
650 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
651 RAISE FND_API.G_EXC_ERROR;
652 END IF;
653
654 hz_extract_cont_point_bo_pvt.get_phone_bos
655 (p_init_msg_list => fnd_api.g_false,
656 p_phone_id => null,
657 p_parent_id => p_person_id,
658 p_parent_table_name => 'HZ_PARTIES',
659 p_action_type => p_action_type,
660 x_phone_objs => x_person_obj.phone_objs,
661 x_return_status => x_return_status,
662 x_msg_count => x_msg_count,
663 x_msg_data => x_msg_data);
664
665 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
666 RAISE FND_API.G_EXC_ERROR;
667 END IF;
668
669 hz_extract_cont_point_bo_pvt.get_email_bos
670 (p_init_msg_list => fnd_api.g_false,
671 p_email_id => null,
672 p_parent_id => p_person_id,
673 p_parent_table_name => 'HZ_PARTIES',
674 p_action_type => p_action_type,
675 x_email_objs => x_person_obj.email_objs,
676 x_return_status => x_return_status,
677 x_msg_count => x_msg_count,
678 x_msg_data => x_msg_data);
679
680 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683
684
685 hz_extract_cont_point_bo_pvt.get_web_bos
686 (p_init_msg_list => fnd_api.g_false,
687 p_web_id => null,
688 p_parent_id => p_person_id,
689 p_parent_table_name => 'HZ_PARTIES',
690 p_action_type => p_action_type,
691 x_web_objs => x_person_obj.web_objs,
692 x_return_status => x_return_status,
693 x_msg_count => x_msg_count,
694 x_msg_data => x_msg_data);
695
696 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
697 RAISE FND_API.G_EXC_ERROR;
698 END IF;
699
700 hz_extract_cont_point_bo_pvt.get_sms_bos
701 (p_init_msg_list => fnd_api.g_false,
702 p_sms_id => null,
703 p_parent_id => p_person_id,
704 p_parent_table_name => 'HZ_PARTIES',
705 p_action_type => p_action_type,
706 x_sms_objs => x_person_obj.sms_objs,
707 x_return_status => x_return_status,
708 x_msg_count => x_msg_count,
709 x_msg_data => x_msg_data);
710
711 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
712 RAISE FND_API.G_EXC_ERROR;
713 END IF;
714
715 get_employ_hist_bos(p_init_msg_list => fnd_api.g_false,
716 p_person_id => p_person_id,
717 p_action_type => p_action_type,
718 x_employ_hist_objs => x_person_obj.employ_hist_objs,
719 x_return_status => x_return_status,
720 x_msg_count => x_msg_count,
721 x_msg_data => x_msg_data);
722
723
724 hz_extract_cont_point_bo_pvt.get_cont_pref_objs
725 (p_init_msg_list => fnd_api.g_false,
726 p_cont_level_table_id => p_person_id,
727 p_cont_level_table => 'HZ_PARTIES',
728 p_contact_type => NULL,
729 p_action_type => p_action_type,
730 x_cont_pref_objs => x_person_obj.contact_pref_objs,
731 x_return_status => x_return_status,
732 x_msg_count => x_msg_count,
733 x_msg_data => x_msg_data);
734
735 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
736 RAISE FND_API.G_EXC_ERROR;
737 END IF;
738
739
740 -- Debug info.
741 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
742 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
743 p_msg_data=>x_msg_data,
744 p_msg_type=>'WARNING',
745 p_msg_level=>fnd_log.level_exception);
746 END IF;
747
748 -- Debug info.
749 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
750 hz_utility_v2pub.debug(p_message=>'get_person_bo (-)',
751 p_prefix=>l_debug_prefix,
752 p_msg_level=>fnd_log.level_procedure);
753 END IF;
754
755
756 EXCEPTION
757
758 WHEN fnd_api.g_exc_error THEN
759 x_return_status := fnd_api.g_ret_sts_error;
760
761 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
762 p_count => x_msg_count,
763 p_data => x_msg_data);
764
765 -- Debug info.
766 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
767 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
768 p_msg_data=>x_msg_data,
769 p_msg_type=>'ERROR',
770 p_msg_level=>fnd_log.level_error);
771 END IF;
772 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
773 hz_utility_v2pub.debug(p_message=>'get_person_bo (-)',
774 p_prefix=>l_debug_prefix,
775 p_msg_level=>fnd_log.level_procedure);
776 END IF;
777 WHEN fnd_api.g_exc_unexpected_error THEN
778 x_return_status := fnd_api.g_ret_sts_unexp_error;
779
780 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
781 p_count => x_msg_count,
782 p_data => x_msg_data);
783
784 -- Debug info.
785 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
786 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
787 p_msg_data=>x_msg_data,
788 p_msg_type=>'UNEXPECTED ERROR',
789 p_msg_level=>fnd_log.level_error);
790 END IF;
791 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
792 hz_utility_v2pub.debug(p_message=>'get_person_bo (-)',
793 p_prefix=>l_debug_prefix,
794 p_msg_level=>fnd_log.level_procedure);
795 END IF;
796 WHEN OTHERS THEN
797 x_return_status := fnd_api.g_ret_sts_unexp_error;
798
799 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
800 fnd_message.set_token('ERROR' ,SQLERRM);
801 fnd_msg_pub.add;
802
803 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
804 p_count => x_msg_count,
805 p_data => x_msg_data);
806
807 -- Debug info.
808 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
809 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
810 p_msg_data=>x_msg_data,
811 p_msg_type=>'SQL ERROR',
812 p_msg_level=>fnd_log.level_error);
813 END IF;
814 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
815 hz_utility_v2pub.debug(p_message=>'get_person_bo (-)',
816 p_prefix=>l_debug_prefix,
817 p_msg_level=>fnd_log.level_procedure);
818 END IF;
819
820 end;
821
822
823
824 --------------------------------------
825 --
826 -- PROCEDURE get_persons_created
827 --
828 -- DESCRIPTION
829 --The caller provides an identifier for the Persons created business event and
830 --the procedure returns database objects of the type HZ_PERSON_BO for all of
831 --the Person business objects from the business event.
832
833 --
834 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
835 --
836 -- ARGUMENTS
837 -- IN:
838 -- p_init_msg_list Initialize message stack if it is set to
839 -- p_event_id BES Event identifier.
840 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
841 -- OUT:
842 -- x_person_objs One or more created logical person.
843 -- x_return_status Return status after the call. The status can
844 -- be fnd_api.g_ret_sts_success (success),
845 -- fnd_api.g_ret_sts_error (error),
846 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
847 -- x_msg_count Number of messages in message stack.
848 -- x_msg_data Message text if x_msg_count is 1.
849 --
850 -- NOTES
851 --
852 -- MODIFICATION HISTORY
853 --
854 -- 20-MAY-2005 AWU Created.
855 --
856
857
858
859 /*
860 The Get Persons Created procedure is a service to retrieve all of the Person business objects
861 whose creations have been captured by a logical business event. Each Persons Created
862 business event signifies that one or more Person business objects have been created.
863 The caller provides an identifier for the Persons Created business event and the procedure
864 returns all of the Person business objects from the business event. For each business object
865 creation captured in the business event, the procedure calls the generic Get operation:
866 HZ_PERSON_BO_PVT.get_person_bo
867
868 Gathering all of the returned business objects from those API calls, the procedure packages
869 them in a table structure and returns them to the caller.
870 */
871
872
873 PROCEDURE get_persons_created(
874 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
875 p_event_id IN NUMBER,
876 x_person_objs OUT NOCOPY HZ_PERSON_BO_TBL,
877 x_return_status OUT NOCOPY VARCHAR2,
878 x_msg_count OUT NOCOPY NUMBER,
879 x_msg_data OUT NOCOPY VARCHAR2
880 ) is
881
882 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
883 l_debug_prefix VARCHAR2(30) := '';
884 begin
885
886 -- initialize API return status to success.
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888
889 -- Initialize message list if p_init_msg_list is set to TRUE
890 IF FND_API.to_Boolean(p_init_msg_list) THEN
891 FND_MSG_PUB.initialize;
892 END IF;
893
894
895 -- Debug info.
896 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
897 hz_utility_v2pub.debug(p_message=>'get_person_created(+)',
898 p_prefix=>l_debug_prefix,
899 p_msg_level=>fnd_log.level_procedure);
900 END IF;
901
902
903 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
904 p_init_msg_list => fnd_api.g_false,
905 p_event_id => p_event_id,
906 x_obj_root_ids => l_obj_root_ids,
907 x_return_status => x_return_status,
908 x_msg_count => x_msg_count,
909 x_msg_data => x_msg_data);
910
911 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
912 RAISE FND_API.G_EXC_ERROR;
913 END IF;
914
915 x_person_objs := HZ_PERSON_BO_TBL();
916
917 for i in 1..l_obj_root_ids.count loop
918
919 x_person_objs.extend;
920 get_person_bo(
921 p_init_msg_list => fnd_api.g_false,
922 p_person_id => l_obj_root_ids(i),
923 p_action_type => 'CREATED',
924 x_person_obj => x_person_objs(i),
925 x_return_status => x_return_status,
926 x_msg_count => x_msg_count,
927 x_msg_data => x_msg_data);
928
929 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
930 RAISE FND_API.G_EXC_ERROR;
931 END IF;
932
933 end loop;
934
935 -- Debug info.
936 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
937 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
938 p_msg_data=>x_msg_data,
939 p_msg_type=>'WARNING',
940 p_msg_level=>fnd_log.level_exception);
941 END IF;
942
943 -- Debug info.
944 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
945 hz_utility_v2pub.debug(p_message=>'get_person_created (-)',
946 p_prefix=>l_debug_prefix,
947 p_msg_level=>fnd_log.level_procedure);
948 END IF;
949
950
951 EXCEPTION
952
953 WHEN fnd_api.g_exc_error THEN
954 x_return_status := fnd_api.g_ret_sts_error;
955
956 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
957 p_count => x_msg_count,
958 p_data => x_msg_data);
959
960 -- Debug info.
961 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
962 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
963 p_msg_data=>x_msg_data,
964 p_msg_type=>'ERROR',
965 p_msg_level=>fnd_log.level_error);
966 END IF;
967 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
968 hz_utility_v2pub.debug(p_message=>'get_person_created(-)',
969 p_prefix=>l_debug_prefix,
970 p_msg_level=>fnd_log.level_procedure);
971 END IF;
972 WHEN fnd_api.g_exc_unexpected_error THEN
973 x_return_status := fnd_api.g_ret_sts_unexp_error;
974
975 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
976 p_count => x_msg_count,
977 p_data => x_msg_data);
978
979 -- Debug info.
980 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
981 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
982 p_msg_data=>x_msg_data,
983 p_msg_type=>'UNEXPECTED ERROR',
984 p_msg_level=>fnd_log.level_error);
985 END IF;
986 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
987 hz_utility_v2pub.debug(p_message=>'get_person_created(-)',
988 p_prefix=>l_debug_prefix,
989 p_msg_level=>fnd_log.level_procedure);
990 END IF;
991 WHEN OTHERS THEN
992 x_return_status := fnd_api.g_ret_sts_unexp_error;
993
994 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
995 fnd_message.set_token('ERROR' ,SQLERRM);
996 fnd_msg_pub.add;
997
998 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
999 p_count => x_msg_count,
1000 p_data => x_msg_data);
1001
1002 -- Debug info.
1003 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1004 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1005 p_msg_data=>x_msg_data,
1006 p_msg_type=>'SQL ERROR',
1007 p_msg_level=>fnd_log.level_error);
1008 END IF;
1009 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1010 hz_utility_v2pub.debug(p_message=>'get_person_created(-)',
1011 p_prefix=>l_debug_prefix,
1012 p_msg_level=>fnd_log.level_procedure);
1013 END IF;
1014
1015 end;
1016
1017
1018
1019 --------------------------------------
1020 --
1021 -- PROCEDURE get_persons_updated
1022 --
1023 -- DESCRIPTION
1024 --The caller provides an identifier for the Persons update business event and
1025 --the procedure returns database objects of the type HZ_PERSON_BO for all of
1026 --the Person business objects from the business event.
1027
1028 --
1029 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1030 --
1031 -- ARGUMENTS
1032 -- IN:
1033 -- p_init_msg_list Initialize message stack if it is set to
1034 -- p_event_id BES Event identifier.
1035 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1036 -- OUT:
1037 -- x_person_objs One or more created logical person.
1038 -- x_return_status Return status after the call. The status can
1039 -- be fnd_api.g_ret_sts_success (success),
1040 -- fnd_api.g_ret_sts_error (error),
1041 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1042 -- x_msg_count Number of messages in message stack.
1043 -- x_msg_data Message text if x_msg_count is 1.
1044 --
1045 -- NOTES
1046 --
1047 -- MODIFICATION HISTORY
1048 --
1049 -- 20-MAY-2005 AWU Created.
1050 --
1051
1052
1053
1054 /*
1055 The Get Persons Updated procedure is a service to retrieve all of the Person business objects whose updates have been
1056 captured by the logical business event. Each Persons Updated business event signifies that one or more Person business
1057 objects have been updated.
1058 The caller provides an identifier for the Persons Update business event and the procedure returns database objects of
1059 the type HZ_PERSON_BO for all of the Person business objects from the business event.
1060 Gathering all of the returned database objects from those API calls, the procedure packages them in a table structure
1061 and returns them to the caller.
1062 */
1063
1064 PROCEDURE get_persons_updated(
1065 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1066 p_event_id IN NUMBER,
1067 x_person_objs OUT NOCOPY HZ_PERSON_BO_TBL,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2
1071 ) is
1072
1073 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1074 l_debug_prefix VARCHAR2(30) := '';
1075
1076 begin
1077
1078 -- initialize API return status to success.
1079 x_return_status := FND_API.G_RET_STS_SUCCESS;
1080
1081 -- Initialize message list if p_init_msg_list is set to TRUE
1082 IF FND_API.to_Boolean(p_init_msg_list) THEN
1083 FND_MSG_PUB.initialize;
1084 END IF;
1085
1086
1087 -- Debug info.
1088 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1089 hz_utility_v2pub.debug(p_message=>'get_persons_updated(+)',
1090 p_prefix=>l_debug_prefix,
1091 p_msg_level=>fnd_log.level_procedure);
1092 END IF;
1093
1094
1095 HZ_EXTRACT_BO_UTIL_PVT.get_bo_root_ids(
1096 p_init_msg_list => fnd_api.g_false,
1097 p_event_id => p_event_id,
1098 x_obj_root_ids => l_obj_root_ids,
1099 x_return_status => x_return_status,
1100 x_msg_count => x_msg_count,
1101 x_msg_data => x_msg_data);
1102
1103 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106
1107
1108 -- call event API get_organization_updated for each id.
1109
1110 x_person_objs := HZ_PERSON_BO_TBL();
1111
1112 for i in 1..l_obj_root_ids.count loop
1113
1114 x_person_objs.extend;
1115 get_person_updated(
1116 p_init_msg_list => fnd_api.g_false,
1117 p_event_id => p_event_id,
1118 p_person_id => l_obj_root_ids(i),
1119 x_person_obj => x_person_objs(i),
1120 x_return_status => x_return_status,
1121 x_msg_count => x_msg_count,
1122 x_msg_data => x_msg_data);
1123
1124 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1125 RAISE FND_API.G_EXC_ERROR;
1126
1127 END IF;
1128
1129 end loop;
1130
1131
1132
1133 -- Debug info.
1134 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1135 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1136 p_msg_data=>x_msg_data,
1137 p_msg_type=>'WARNING',
1138 p_msg_level=>fnd_log.level_exception);
1139 END IF;
1140
1141 -- Debug info.
1142 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1143 hz_utility_v2pub.debug(p_message=>'get_persons_updated (-)',
1144 p_prefix=>l_debug_prefix,
1145 p_msg_level=>fnd_log.level_procedure);
1146 END IF;
1147
1148
1149 EXCEPTION
1150
1151 WHEN fnd_api.g_exc_error THEN
1152 x_return_status := fnd_api.g_ret_sts_error;
1153
1154 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1155 p_count => x_msg_count,
1156 p_data => x_msg_data);
1157
1158 -- Debug info.
1159 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1160 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1161 p_msg_data=>x_msg_data,
1162 p_msg_type=>'ERROR',
1163 p_msg_level=>fnd_log.level_error);
1164 END IF;
1165 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1166 hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
1167 p_prefix=>l_debug_prefix,
1168 p_msg_level=>fnd_log.level_procedure);
1169 END IF;
1170 WHEN fnd_api.g_exc_unexpected_error THEN
1171 x_return_status := fnd_api.g_ret_sts_unexp_error;
1172
1173 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1174 p_count => x_msg_count,
1175 p_data => x_msg_data);
1176
1177 -- Debug info.
1178 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1179 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1180 p_msg_data=>x_msg_data,
1181 p_msg_type=>'UNEXPECTED ERROR',
1182 p_msg_level=>fnd_log.level_error);
1183 END IF;
1184 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1185 hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
1186 p_prefix=>l_debug_prefix,
1187 p_msg_level=>fnd_log.level_procedure);
1188 END IF;
1189 WHEN OTHERS THEN
1190 x_return_status := fnd_api.g_ret_sts_unexp_error;
1191
1192 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1193 fnd_message.set_token('ERROR' ,SQLERRM);
1194 fnd_msg_pub.add;
1195
1196 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1197 p_count => x_msg_count,
1198 p_data => x_msg_data);
1199
1200 -- Debug info.
1201 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1202 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1203 p_msg_data=>x_msg_data,
1204 p_msg_type=>'SQL ERROR',
1205 p_msg_level=>fnd_log.level_error);
1206 END IF;
1207 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1208 hz_utility_v2pub.debug(p_message=>'get_persons_updated(-)',
1209 p_prefix=>l_debug_prefix,
1210 p_msg_level=>fnd_log.level_procedure);
1211 END IF;
1212
1213 end;
1214
1215 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
1216 procedure set_person_bo_action_type(p_event_id IN NUMBER,
1217 p_root_id IN NUMBER,
1218 px_person_obj IN OUT NOCOPY HZ_PERSON_BO,
1219 x_return_status OUT NOCOPY VARCHAR2) is
1220 cursor c1 is
1221
1222 SELECT
1223 sys_connect_by_path(CHILD_BO_CODE, '/') node_path,
1224 CHILD_OPERATION_FLAG,
1225 CHILD_BO_CODE,
1226 CHILD_ENTITY_NAME,
1227 CHILD_ID,
1228 populated_flag
1229 FROM HZ_BUS_OBJ_TRACKING
1230 where event_id = p_event_id
1231 START WITH child_id = p_root_id
1232 AND child_entity_name = 'HZ_PARTIES'
1233 AND PARENT_BO_CODE IS NULL
1234 AND event_id = p_event_id
1235 AND CHILD_BO_CODE = 'PERSON' --(or ORG, PERSON_CUST, ORG_CUST).
1236 and event_id = p_event_id
1237 CONNECT BY PARENT_ENTITY_NAME = PRIOR CHILD_ENTITY_NAME
1238 AND PARENT_ID = PRIOR CHILD_ID
1239 AND parent_bo_code = PRIOR child_bo_code
1240 and event_id = PRIOR event_id;
1241
1242 cursor c2 is
1243 select CHILD_ENTITY_NAME
1244 FROM HZ_BUS_OBJ_TRACKING
1245 where event_id = p_event_id
1246 and populated_flag = 'N'
1247 and CHILD_ENTITY_NAME = 'HZ_PERSON_PROFILES';
1248
1249 L_CHILD_OPERATION_FLAG VARCHAR2(1);
1250 L_CHILD_BO_CODE VARCHAR2(30);
1251 L_CHILD_ENTITY_NAME VARCHAR2(30);
1252 L_CHILD_ID NUMBER;
1253 l_action_type varchar2(30);
1254 l_node_path varchar2(2000);
1255 l_populated_flag varchar2(1);
1256 l_child_upd_flag varchar2(1);
1257
1258 begin
1259 -- initialize API return status to success.
1260 x_return_status := FND_API.G_RET_STS_SUCCESS;
1261
1262 -- Solve populate_flag is not in sync in hz_parties and hz_org_profiles.
1263 -- c1 can't return profile entity.
1264
1265 open c2;
1266 fetch c2 into L_CHILD_ENTITY_NAME;
1267 close c2;
1268
1269 if l_child_entity_name = 'HZ_PERSON_PROFILES'
1270 then
1271 px_person_obj.action_type := 'UPDATED';
1272 else px_person_obj.action_type := 'CHILD_UPDATED';
1273 end if;
1274
1275 open c1;
1276 loop
1277 fetch c1 into L_NODE_PATH, L_CHILD_OPERATION_FLAG,L_CHILD_BO_CODE,
1278 L_CHILD_ENTITY_NAME, L_CHILD_ID, l_populated_flag;
1279 exit when c1%NOTFOUND;
1280 if l_populated_flag = 'N'
1281 then
1282 if L_CHILD_OPERATION_FLAG = 'I'
1283 then l_action_type := 'CREATED';
1284 elsif L_CHILD_OPERATION_FLAG = 'U'
1285 then l_action_type := 'UPDATED';
1286 end if;
1287
1288 -- check first level entity objects
1289
1290 if l_child_entity_name = 'HZ_EDUCATION' then
1291 for i in 1..PX_PERSON_OBJ.EDUCATION_OBJS .COUNT
1292 loop
1293 if PX_PERSON_OBJ.EDUCATION_OBJS(i).education_id = l_child_id
1294 then PX_PERSON_OBJ.EDUCATION_OBJS(i).action_type := l_action_type;
1295 l_child_upd_flag := 'Y';
1296 end if;
1297 end loop;
1298 elsif l_child_entity_name = 'HZ_PARTY_USG_ASSIGNMENTS' then
1299 for i in 1..PX_PERSON_OBJ.PARTY_USAGE_OBJS .COUNT
1300 loop
1301 if PX_PERSON_OBJ.PARTY_USAGE_OBJS(i).party_usg_assignment_id = l_child_id
1302 then PX_PERSON_OBJ.PARTY_USAGE_OBJS(i).action_type := l_action_type;
1303 l_child_upd_flag := 'Y';
1304 end if;
1305 end loop;
1306 elsif l_child_entity_name = 'HZ_CITIZENSHIP' then
1307 for i in 1..PX_PERSON_OBJ.CITIZENSHIP_OBJS .COUNT
1308 loop
1309 if PX_PERSON_OBJ.CITIZENSHIP_OBJS(i).citizenship_id = l_child_id
1310 then PX_PERSON_OBJ.CITIZENSHIP_OBJS(i).action_type := l_action_type;
1311 end if;
1312 end loop;
1313 elsif l_child_entity_name = 'HZ_RELATIONSHIPS' then
1314 for i in 1..PX_PERSON_OBJ.RELATIONSHIP_OBJS .COUNT
1315 loop
1316 if PX_PERSON_OBJ.RELATIONSHIP_OBJS(i).relationship_id = l_child_id
1317 then PX_PERSON_OBJ.RELATIONSHIP_OBJS(i).action_type := l_action_type;
1318 l_child_upd_flag := 'Y';
1319 end if;
1320 end loop;
1321 elsif l_child_entity_name = 'HZ_CERTIFICATIONS' then
1322 for i in 1..PX_PERSON_OBJ.CERTIFICATION_OBJS .COUNT
1323 loop
1324 if PX_PERSON_OBJ.CERTIFICATION_OBJS(i).certification_id = l_child_id
1325 then PX_PERSON_OBJ.CERTIFICATION_OBJS(i).action_type := l_action_type;
1326 l_child_upd_flag := 'Y';
1327 end if;
1328
1329 end loop;
1330 elsif l_child_entity_name = 'HZ_PERSON_INTEREST' then
1331 for i in 1..PX_PERSON_OBJ.INTEREST_OBJS .COUNT
1332 loop
1333 if PX_PERSON_OBJ.INTEREST_OBJS(i).person_interest_id = l_child_id
1334 then PX_PERSON_OBJ.INTEREST_OBJS(i).action_type := l_action_type;
1335 end if;
1336 l_child_upd_flag := 'Y';
1337 end loop;
1338 elsif l_child_entity_name = 'HZ_PERSON_LANGUAGE' then
1339 for i in 1..PX_PERSON_OBJ.LANGUAGE_OBJS .COUNT
1340 loop
1341 if PX_PERSON_OBJ.LANGUAGE_OBJS(i).LANGUAGE_USE_REFERENCE_ID = l_child_id
1342 then PX_PERSON_OBJ.LANGUAGE_OBJS(i).action_type := l_action_type;
1343 l_child_upd_flag := 'Y';
1344 end if;
1345 end loop;
1346 elsif l_child_entity_name = 'HZ_CODE_ASSIGNMENTS' then
1347 for i in 1..PX_PERSON_OBJ.CLASS_OBJS .COUNT
1348 loop
1349 if PX_PERSON_OBJ.CLASS_OBJS(i).code_assignment_id = l_child_id
1350 then PX_PERSON_OBJ.CLASS_OBJS(i).action_type := l_action_type;
1351 l_child_upd_flag := 'Y';
1352 end if;
1353 end loop;
1354 elsif l_child_entity_name = 'HZ_FINANCIAL_PROFILE' then
1355 for i in 1..PX_PERSON_OBJ.FINANCIAL_PROF_OBJS .COUNT
1356 loop
1357 if PX_PERSON_OBJ.FINANCIAL_PROF_OBJS(i).financial_profile_id = l_child_id
1358 then PX_PERSON_OBJ.FINANCIAL_PROF_OBJS(i).action_type := l_action_type;
1359 l_child_upd_flag := 'Y';
1360 end if;
1361 end loop;
1362 elsif l_child_entity_name = 'HZ_PARTY_PREFERENCES' then
1363 for i in 1..PX_PERSON_OBJ.PREFERENCE_OBJS .COUNT
1364 loop
1365 if PX_PERSON_OBJ.PREFERENCE_OBJS(i).party_preference_id = l_child_id
1366 then PX_PERSON_OBJ.PREFERENCE_OBJS(i).action_type := l_action_type;
1367 l_child_upd_flag := 'Y';
1368 end if;
1369 end loop;
1370 elsif l_child_entity_name = 'HZ_PER_PROFILES_EXT_VL' then
1371 for i in 1..PX_PERSON_OBJ.EXT_ATTRIBUTES_OBJS .COUNT
1372 loop
1373 if PX_PERSON_OBJ.EXT_ATTRIBUTES_OBJS(i).extension_id = l_child_id
1374 then PX_PERSON_OBJ.EXT_ATTRIBUTES_OBJS(i).action_type := l_action_type;
1375 l_child_upd_flag := 'Y';
1376 end if;
1377 end loop;
1378
1379 -- contact preference might have multiple parents, but one id will not belong to
1380 -- more than one parents
1381 elsif l_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1382
1383 for i in 1..PX_PERSON_OBJ.CONTACT_PREF_OBJS .COUNT
1384 loop
1385 if PX_PERSON_OBJ.CONTACT_PREF_OBJS(i).contact_preference_id = l_child_id
1386 then PX_PERSON_OBJ.CONTACT_PREF_OBJS(i).action_type := l_action_type;
1387 l_child_upd_flag := 'Y';
1388 end if;
1389 end loop;
1390
1391 if instr(l_node_path, 'PERSON/PHONE') > 0 then
1392 for i in 1..PX_PERSON_OBJ.PHONE_OBJS.COUNT
1393 loop
1394 for j in 1..PX_PERSON_OBJ.PHONE_OBJS(i).CONTACT_PREF_OBJS.count
1395 loop
1396 if px_person_obj.phone_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1397 then px_person_obj.phone_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1398 end if;
1399 end loop;
1400 end loop;
1401 end if;
1402 if instr(l_node_path, 'PERSON/EMAIL') > 0 then
1403 for i in 1..PX_PERSON_OBJ.EMAIL_OBJS.COUNT
1404 loop
1405 for j in 1..PX_PERSON_OBJ.EMAIL_OBJS(i).CONTACT_PREF_OBJS.count
1406 loop
1407 if px_person_obj.email_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1408 then px_person_obj.email_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1409
1410 end if;
1411 end loop;
1412 end loop;
1413 end if;
1414 if instr(l_node_path, 'PERSON/WEB') > 0 then
1415 for i in 1..PX_PERSON_OBJ.WEB_OBJS.COUNT
1416 loop
1417 for j in 1..PX_PERSON_OBJ.WEB_OBJS(i).CONTACT_PREF_OBJS.count
1418 loop
1419 if px_person_obj.web_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1420 then px_person_obj.web_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1421
1422 end if;
1423 end loop;
1424 end loop;
1425 end if;
1426 if instr(l_node_path, 'PERSON/SMS') > 0 then
1427 for i in 1..PX_PERSON_OBJ.SMS_OBJS.COUNT
1428 loop
1429 for j in 1..PX_PERSON_OBJ.SMS_OBJS(i).CONTACT_PREF_OBJS.count
1430 loop
1431 if px_person_obj.sms_objs(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1432 then px_person_obj.sms_objs(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1433 l_child_upd_flag := 'Y';
1434 end if;
1435 end loop;
1436 end loop;
1437 end if;
1438
1439 elsif l_child_entity_name = 'HZ_CONTACT_POINTS' then
1440 if l_child_bo_code = 'EMAIL'
1441 then
1442 for i in 1..PX_PERSON_OBJ.EMAIL_OBJS.COUNT
1443 loop
1444 if PX_PERSON_OBJ.EMAIL_OBJS(i).email_id = l_child_id
1445 then PX_PERSON_OBJ.EMAIL_OBJS(i).action_type := l_action_type;
1446 l_child_upd_flag := 'Y';
1447 end if;
1448 end loop;
1449 elsif l_child_bo_code = 'PHONE'
1450 then
1451 for i in 1..PX_PERSON_OBJ.PHONE_OBJS.COUNT
1452 loop
1453 if PX_PERSON_OBJ.PHONE_OBJS(i).phone_id = l_child_id
1454 then PX_PERSON_OBJ.PHONE_OBJS(i).action_type := l_action_type;
1455 l_child_upd_flag := 'Y';
1456 end if;
1457 end loop;
1458 elsif l_child_bo_code = 'WEB'
1459 then
1460 for i in 1..PX_PERSON_OBJ.WEB_OBJS.COUNT
1461 loop
1462 if PX_PERSON_OBJ.WEB_OBJS(i).web_id = l_child_id
1463 then PX_PERSON_OBJ.WEB_OBJS(i).action_type := l_action_type;
1464 l_child_upd_flag := 'Y';
1465 end if;
1466 end loop;
1467 elsif l_child_bo_code = 'SMS'
1468 then
1469 for i in 1..PX_PERSON_OBJ.SMS_OBJS.COUNT
1470 loop
1471 if PX_PERSON_OBJ.SMS_OBJS(i).sms_id = l_child_id
1472 then PX_PERSON_OBJ.SMS_OBJS(i).action_type := l_action_type;
1473 l_child_upd_flag := 'Y';
1474 end if;
1475 end loop;
1476 end if;
1477 end if;
1478
1479 -- check party site object
1480
1481 if instr(l_node_path, 'PERSON/PARTY_SITE') > 0
1482 then
1483 for i in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS.COUNT
1484 loop
1485 -- check root level
1486 if l_child_entity_name = 'HZ_PARTY_SITES'
1487 then
1488 if px_person_obj.party_site_objs(i).party_site_id = l_child_id
1489 then
1490 px_person_obj.party_site_objs(i).action_type := l_action_type;
1491 l_child_upd_flag := 'N';
1492 if l_action_type = 'CREATED'
1493 then
1494 px_person_obj.party_site_objs(i).location_obj.action_type :='CREATED';
1495 l_child_upd_flag := 'Y';
1496 end if;
1497
1498 end if;
1499 end if;
1500
1501 -- check second level
1502
1503 if l_child_entity_name = 'HZ_LOCATIONS'
1504 then
1505 if px_person_obj.party_site_objs(i).location_obj.location_id = l_child_id and l_action_type = 'UPDATED'
1506 then px_person_obj.party_site_objs(i).location_obj.action_type := l_action_type;
1507 l_child_upd_flag := 'Y';
1508 end if;
1509 end if;
1510 if l_child_entity_name = 'HZ_CONTACT_POINTS'
1511 then
1512 if l_child_bo_code = 'EMAIL'
1513 then
1514 for j in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS(i).EMAIL_OBJS.COUNT
1515 loop
1516 if PX_PERSON_OBJ.PARTY_SITE_OBJS(i).EMAIL_OBJS(j).email_id = l_child_id
1517 then PX_PERSON_OBJ.PARTY_SITE_OBJS(i).EMAIL_OBJS(j).action_type := l_action_type;
1518 l_child_upd_flag := 'Y';
1519 end if;
1520 end loop;
1521 elsif l_child_bo_code = 'PHONE'
1522 then
1523 for j in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS(i).PHONE_OBJS.COUNT
1524 loop
1525 if PX_PERSON_OBJ.PARTY_SITE_OBJS(i).PHONE_OBJS(j).phone_id = l_child_id
1526 then PX_PERSON_OBJ.PARTY_SITE_OBJS(i).PHONE_OBJS(j).action_type := l_action_type;
1527 l_child_upd_flag := 'Y';
1528 end if;
1529 end loop;
1530 elsif l_child_bo_code = 'WEB'
1531 then
1532 for j in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS(i).WEB_OBJS.COUNT
1533 loop
1534 if PX_PERSON_OBJ.PARTY_SITE_OBJS(i).WEB_OBJS(j).web_id = l_child_id
1535 then PX_PERSON_OBJ.PARTY_SITE_OBJS(i).WEB_OBJS(j).action_type := l_action_type;
1536 l_child_upd_flag := 'Y';
1537 end if;
1538 end loop;
1539 elsif l_child_bo_code = 'TLX'
1540 then
1541 for j in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS(i).TELEX_OBJS.COUNT
1542 loop
1543 if PX_PERSON_OBJ.PARTY_SITE_OBJS(i).TELEX_OBJS(j).telex_id = l_child_id
1544 then PX_PERSON_OBJ.PARTY_SITE_OBJS(i).TELEX_OBJS(j).action_type := l_action_type;
1545 l_child_upd_flag := 'Y';
1546 end if;
1547 end loop;
1548 end if;
1549 end if; --'HZ_CONTACT_POINTS'
1550 if l_child_entity_name = 'HZ_CONTACT_PREFERENCES' then
1551 for j in 1..PX_PERSON_OBJ.PARTY_SITE_OBJS(i).CONTACT_PREF_OBJS.count
1552 loop
1553 if PX_PERSON_OBJ.PARTY_SITE_OBJS(i).CONTACT_PREF_OBJS(j).contact_preference_id = l_child_id
1554 then PX_PERSON_OBJ.PARTY_SITE_OBJS(i).CONTACT_PREF_OBJS(j).action_type := l_action_type;
1555
1556 l_child_upd_flag := 'Y'; end if;
1557 end loop;
1558 end if;
1559
1560 if px_person_obj.party_site_objs(i).action_type = 'UNCHANGED'
1561 and l_child_upd_flag = 'Y'
1562 then
1563 px_person_obj.party_site_objs(i).action_type := 'CHILD_UPDATED';
1564 end if;
1565 end loop; -- party_site_obj
1566 end if; -- party_site_obj
1567
1568 -- check emp history object
1569
1570 if instr(l_node_path, 'PERSON/EMP_HIST') > 0
1571 then
1572 for i in 1..PX_PERSON_OBJ.EMPLOY_HIST_OBJS.COUNT
1573 loop
1574 -- check root level
1575 if l_child_entity_name = 'HZ_EMPLOYMENT_HISTORY'
1576 then
1577 if px_person_obj.EMPLOY_HIST_OBJS(i).EMPLOYMENT_HISTORY_ID = l_child_id
1578 then
1579 px_person_obj.EMPLOY_HIST_OBJS(i).action_type := l_action_type;
1580 l_child_upd_flag := 'N';
1581 end if;
1582 end if;
1583
1584 -- check second level
1585
1586 if l_child_entity_name = 'HZ_WORK_CLASS'
1587 then
1588 for j in 1..PX_PERSON_OBJ.EMPLOY_HIST_OBJS(i).WORK_CLASS_OBJS.COUNT
1589 loop
1590 if PX_PERSON_OBJ.EMPLOY_HIST_OBJS(i).WORK_CLASS_OBJS(j).work_class_id = l_child_id
1591 then PX_PERSON_OBJ.EMPLOY_HIST_OBJS(i).WORK_CLASS_OBJS(j).action_type := l_action_type;
1592 l_child_upd_flag := 'Y';
1593 end if;
1594 end loop;
1595 end if;
1596 if px_person_obj.employ_hist_objs(i).action_type = 'UNCHANGED'
1597 and l_child_upd_flag = 'Y'
1598 then
1599 px_person_obj.employ_hist_objs(i).action_type := 'CHILD_UPDATED';
1600 end if;
1601 end loop; -- emp history
1602 end if; -- emp history
1603 end if; -- populated_flag = 'N'
1604 end loop;
1605 close c1;
1606
1607 EXCEPTION
1608
1609
1610 WHEN fnd_api.g_exc_unexpected_error THEN
1611 x_return_status := fnd_api.g_ret_sts_unexp_error;
1612
1613
1614 WHEN OTHERS THEN
1615 x_return_status := fnd_api.g_ret_sts_unexp_error;
1616
1617 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1618 fnd_message.set_token('ERROR' ,SQLERRM);
1619 fnd_msg_pub.add;
1620
1621 end set_person_bo_action_type;
1622
1623 --------------------------------------
1624 --
1625 -- PROCEDURE get_person_updated
1626 --
1627 -- DESCRIPTION
1628 --The caller provides an identifier for the Persons update business event and person_id
1629 --the procedure returns one database object of the type HZ_PERSON_BO
1630
1631 --
1632 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1633 --
1634 -- ARGUMENTS
1635 -- IN:
1636 -- p_init_msg_list Initialize message stack if it is set to
1637 -- p_event_id BES Event identifier.
1638 -- p_person_id Person identifier.
1639 -- FND_API.G_TRUE. Default is FND_API.G_FALSE.
1640 -- OUT:
1641 -- x_person_objs One or more created logical person.
1642 -- x_return_status Return status after the call. The status can
1643 -- be fnd_api.g_ret_sts_success (success),
1644 -- fnd_api.g_ret_sts_error (error),
1645 -- FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
1646 -- x_msg_count Number of messages in message stack.
1647 -- x_msg_data Message text if x_msg_count is 1.
1648 --
1649 -- NOTES
1650 --
1651 -- MODIFICATION HISTORY
1652 --
1653 -- 10-JUN-2005 AWU Created.
1654 --
1655
1656
1657
1658 -- Get only one person object based on p_person_id and event_id
1659
1660 PROCEDURE get_person_updated(
1661 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1662 p_event_id IN NUMBER,
1663 p_person_id IN NUMBER,
1664 x_person_obj OUT NOCOPY HZ_PERSON_BO,
1665 x_return_status OUT NOCOPY VARCHAR2,
1666 x_msg_count OUT NOCOPY NUMBER,
1667 x_msg_data OUT NOCOPY VARCHAR2
1668 ) is
1669 l_obj_root_ids HZ_EXTRACT_BO_UTIL_PVT.BO_ID_TBL;
1670 l_debug_prefix VARCHAR2(30) := '';
1671 l_person_obj HZ_PERSON_BO;
1672
1673 begin
1674
1675 -- initialize API return status to success.
1676 x_return_status := FND_API.G_RET_STS_SUCCESS;
1677
1678 -- Initialize message list if p_init_msg_list is set to TRUE
1679 IF FND_API.to_Boolean(p_init_msg_list) THEN
1680 FND_MSG_PUB.initialize;
1681 END IF;
1682
1683
1684 -- Debug info.
1685 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1686 hz_utility_v2pub.debug(p_message=>'get_person_updated(+)',
1687 p_prefix=>l_debug_prefix,
1688 p_msg_level=>fnd_log.level_procedure);
1689 END IF;
1690 /* moved to public api
1691 HZ_EXTRACT_BO_UTIL_PVT.validate_event_id(p_event_id => p_event_id,
1692 p_party_id => p_person_id,
1693 x_return_status => x_return_status);
1694
1695 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1696 RAISE FND_API.G_EXC_ERROR;
1697 END IF;
1698 */
1699
1700 -- Set action type to 'UNCHANGED' by default
1701
1702 get_person_bo(
1703 p_init_msg_list => fnd_api.g_false,
1704 p_person_id => p_person_id,
1705 p_action_type => 'UNCHANGED',
1706 x_person_obj => x_person_obj,
1707 x_return_status => x_return_status,
1708 x_msg_count => x_msg_count,
1709 x_msg_data => x_msg_data);
1710
1711 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1712 RAISE FND_API.G_EXC_ERROR;
1713 END IF;
1714
1715 -- Based on BOT, for updated branch, set action_type = 'UPDATED'/'CREATED'
1716
1717
1718 l_person_obj := x_person_obj;
1719 set_person_bo_action_type(p_event_id => p_event_id,
1720 p_root_id => p_person_id,
1721 px_person_obj => l_person_obj,
1722 x_return_status => x_return_status
1723 );
1724 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1725 RAISE FND_API.G_EXC_ERROR;
1726 END IF;
1727
1728 x_person_obj := l_person_obj;
1729
1730
1731 -- Debug info.
1732 IF fnd_log.level_exception>=fnd_log.g_current_runtime_level THEN
1733 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1734 p_msg_data=>x_msg_data,
1735 p_msg_type=>'WARNING',
1736 p_msg_level=>fnd_log.level_exception);
1737 END IF;
1738
1739 -- Debug info.
1740 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1741 hz_utility_v2pub.debug(p_message=>'get_person_updated (-)',
1742 p_prefix=>l_debug_prefix,
1743 p_msg_level=>fnd_log.level_procedure);
1744 END IF;
1745
1746
1747 EXCEPTION
1748
1749 WHEN fnd_api.g_exc_error THEN
1750 x_return_status := fnd_api.g_ret_sts_error;
1751
1752 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1753 p_count => x_msg_count,
1754 p_data => x_msg_data);
1755
1756 -- Debug info.
1757 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1758 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1759 p_msg_data=>x_msg_data,
1760 p_msg_type=>'ERROR',
1761 p_msg_level=>fnd_log.level_error);
1762 END IF;
1763 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1764 hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
1765 p_prefix=>l_debug_prefix,
1766 p_msg_level=>fnd_log.level_procedure);
1767 END IF;
1768 WHEN fnd_api.g_exc_unexpected_error THEN
1769 x_return_status := fnd_api.g_ret_sts_unexp_error;
1770
1771 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1772 p_count => x_msg_count,
1773 p_data => x_msg_data);
1774
1775 -- Debug info.
1776 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1777 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1778 p_msg_data=>x_msg_data,
1779 p_msg_type=>'UNEXPECTED ERROR',
1780 p_msg_level=>fnd_log.level_error);
1781 END IF;
1782 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1783 hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
1784 p_prefix=>l_debug_prefix,
1785 p_msg_level=>fnd_log.level_procedure);
1786 END IF;
1787 WHEN OTHERS THEN
1788 x_return_status := fnd_api.g_ret_sts_unexp_error;
1789
1790 fnd_message.set_name('AR', 'HZ_API_OTHERS_EXCEP');
1791 fnd_message.set_token('ERROR' ,SQLERRM);
1792 fnd_msg_pub.add;
1793
1794 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1795 p_count => x_msg_count,
1796 p_data => x_msg_data);
1797
1798 -- Debug info.
1799 IF fnd_log.level_error>=fnd_log.g_current_runtime_level THEN
1800 hz_utility_v2pub.debug_return_messages(p_msg_count=>x_msg_count,
1801 p_msg_data=>x_msg_data,
1802 p_msg_type=>'SQL ERROR',
1803 p_msg_level=>fnd_log.level_error);
1804 END IF;
1805 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
1806 hz_utility_v2pub.debug(p_message=>'get_person_updated(-)',
1807 p_prefix=>l_debug_prefix,
1808 p_msg_level=>fnd_log.level_procedure);
1809 END IF;
1810
1811 end;
1812
1813
1814
1815
1816 END HZ_EXTRACT_PERSON_BO_PVT;