[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERSON_SS_PKG
Source
1 PACKAGE BODY IGS_PE_PERSON_SS_PKG AS
2 /* $Header: IGSPE10B.pls 120.24 2006/06/29 13:51:38 prbhardw ship $ */
3
4 /* +=======================================================================+
5 | Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | NAME |
9 | IGSVTFPB.pls |
10 | |
11 | DESCRIPTION |
12 | This package provides service functions and procedures to |
13 | print hte text file from the EDS system definition . |
14 | |
15 | NOTES |
16 | |
17 | DEPENDENCIES |
18 | |
19 | USAGE |
20 | |
21 | HISTORY |
22 | 04-APR-2001 A Tereshenkov Created
23 | 10dec-2002 ssawhney - removed the call to hz TBH and called HZ_PARTY_PUB instead.
24 | kumma 27-JUN-2003 2902713, Modified the update_privacy to not |
25 | to call the delete if the row_id is null |
26 | gmaheswa 10-Nov-2003 Bug 3223043 HZ.K Impact changes
27 | Signature of Update_employment is changed, three
28 | new columns are added.
29 | asbala 12-nov-03 3227107: address changes - signature of igs_pe_person_addr_pkg.insert_row and update_row changed
30 | signature of igs_pe_person_ss_pkg.update_address now includes 3 new parameters
31 | ssaleem 11-dec-03 3311720: Problem with error message display in SS Pages
32 | FND_MSG_PUB.initialize called in every procedure
33 | pkpatel 18-JUL-2005 Bug 4327807 (Person SS Enhancement)
34 | Added new procedures and modify existing procedures as per the new enhancement
35 | gamheswa 24-Aug-05 Bug 4327807 (Person SS Enhancement) modified CREATEUPDATE_RELATIONSHIP
36 | pkpatel 9-sep-2005 Bug 4327807 (Person SS Enhancement)
37 | Passed UPPER for Alternate ID.
38 | skpandey 21-SEP-2005 Bug: 3663505
39 | Description: Added ATTRIBUTES 21 TO 24 TO STORE ADDITIONAL INFORMATION
40 | vredkar 03-OCT-2005 Added a cursor dup_addrs_cur in PROCEDURE Update_Usage
41 | vredkar 26-OCT-2005 Bug# 4692461. In Update_Person and createupdate_pers_altid
42 | if error message is IGS_PE_UNIQUE_PID then display IGS_PE_UNIQUE_PID_SS
43 | Added a cursor c_alt_id_desc in the procedure createupdate_pers_altid to
44 | retrieve value of alternate Id description for th given alternate Id type.
45 | pkpatel 12-Jan-2006 Bug 4937960
46 | Modified cursors referring igs_pe_person and igs_pe_stat_v to refer base tables in
47 | Update_Person, Createupdate_Relationship and update_biographic procedures
48 | pkpatel 8-Feb-2006 Bug 4869740 (Changed level to lvl in update_privacy)
49 +=======================================================================+ */
50
51 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_PE_PERSON_SS_PKG';
52 g_prod VARCHAR2(3) := 'IGS';
53 g_debug_mode BOOLEAN := FALSE;
54
55 apps_exception EXCEPTION ;
56 PRAGMA EXCEPTION_INIT(apps_exception, -20001);
57
58 PROCEDURE Update_Privacy(
59 p_api_version IN NUMBER,
60 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
61 p_commit IN VARCHAR2 := FND_API.G_FALSE,
62 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 x_privacy_level_id OUT NOCOPY NUMBER,
67 p_mode IN VARCHAR2,
68 p_person_id IN NUMBER,
69 p_privacy_level_id IN NUMBER,
70 p_data_group_id IN NUMBER,
71 p_data_group IN VARCHAR2,
72 p_lvl IN VARCHAR2,
73 p_action IN VARCHAR2,
74 p_whom IN VARCHAR2,
75 p_start_date IN DATE,
76 p_end_date IN DATE
77 )
78 IS
79
80 l_api_name CONSTANT VARCHAR2(30) := 'Update_Privacy';
81 l_api_version CONSTANT NUMBER := 1.0;
82 l_rowid VARCHAR2(255);
83
84 CURSOR c_rowid (cp_privacy_level_id NUMBER) IS
85 SELECT rowid, ref_notes_id
86 FROM IGS_PE_PRIV_LEVEL
87 WHERE privacy_level_id = cp_privacy_level_id;
88
89 CURSOR data_group_cur (cp_data_group_id NUMBER) IS
90 SELECT data_group, lvl
91 FROM igs_pe_data_groups
92 WHERE data_group_id = p_data_group_id;
93
94 l_data_group IGS_PE_DATA_GROUPS.data_group%TYPE;
95 l_lvl IGS_PE_DATA_GROUPS.lvl%TYPE;
96 l_ref_notes_id igs_pe_priv_level.ref_notes_id%TYPE;
97 BEGIN
98 -- Standard Start of API savepoint
99 SAVEPOINT Update_Privacy;
100 -- Standard call to check for call compatibility.
101 IF NOT FND_API.Compatible_API_Call (l_api_version,
102 p_api_version,
103 l_api_name,
104 G_PKG_NAME)
105 THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108 -- Initialize message list if p_init_msg_list is set to TRUE.
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 IF FND_API.to_Boolean (p_init_msg_list) THEN
111 FND_MSG_PUB.initialize;
112 END IF;
113 -- Bug # 3311720
114 FND_MSG_PUB.initialize;
115
116 -- API body
117 OPEN data_group_cur(p_data_group_id);
118 FETCH data_group_cur INTO l_data_group,l_lvl;
119 CLOSE data_group_cur;
120
121 IF p_mode='UPDATE' THEN
122 OPEN c_rowid (p_privacy_level_id);
123 FETCH c_rowid INTO l_rowid, l_ref_notes_id;
124 CLOSE c_rowid;
125 igs_pe_priv_level_pkg.update_row(
126 X_ROWID => l_rowid,
127 X_PRIVACY_LEVEL_ID => p_privacy_level_id,
128 X_PERSON_ID => p_person_id ,
129 X_DATA_GROUP => l_data_group ,
130 X_DATA_GROUP_ID => p_data_group_id ,
131 X_LVL => l_lvl ,
132 X_ACTION => p_action,
133 X_WHOM => p_whom ,
134 X_REF_NOTES_ID => l_ref_notes_id,
135 X_START_DATE => p_start_date ,
136 X_END_DATE => p_end_date );
137 ELSIF p_mode='INSERT' THEN
138 igs_pe_priv_level_pkg.insert_row(
139 X_ROWID => l_rowid,
140 X_PRIVACY_LEVEL_ID => x_privacy_level_id,
141 X_PERSON_ID => p_person_id ,
142 X_DATA_GROUP => l_data_group ,
143 X_DATA_GROUP_ID => p_data_group_id ,
144 X_LVL => l_lvl ,
145 X_ACTION => p_action,
146 X_WHOM => p_whom ,
147 X_REF_NOTES_ID => null,
148 X_START_DATE => p_start_date ,
149 X_END_DATE => p_end_date );
150 ELSIF p_mode='DELETE' THEN
151 OPEN c_rowid(p_privacy_level_id);
152 FETCH c_rowid INTO l_rowid, l_ref_notes_id;
153 CLOSE c_rowid;
154 --kumma, 2902713, Added the following IF clause , as row id could be null
155 IF l_rowid IS NOT NULL THEN
156 igs_pe_priv_level_pkg.delete_row(
157 X_ROWID => l_rowid);
158 END IF;
159
160 ELSIF p_mode='LOCK' THEN
161 OPEN c_rowid(p_privacy_level_id);
162 FETCH c_rowid INTO l_rowid, l_ref_notes_id;
163 CLOSE c_rowid;
164
165 Igs_Pe_Priv_Level_Pkg.Lock_Row (
166 X_RowId => l_rowid,
167 X_Privacy_Level_Id => p_privacy_level_id,
168 X_Person_Id => p_person_id,
169 X_Data_Group => l_data_group,
170 X_Data_Group_Id => p_data_group_id,
171 X_Lvl => l_lvl,
172 X_Action => p_action,
173 X_Whom => p_whom,
174 X_Start_Date => p_start_date,
175 X_End_Date => p_end_date,
176 X_Ref_Notes_Id => l_ref_notes_id
177 );
178 END IF;
179 -- End of API body.
180 -- Standard check of p_commit.
181 IF FND_API.To_Boolean( p_commit ) THEN
182 COMMIT WORK;
183 END IF;
184 -- Standard call to get message count and if count is 1, get message info.
185 FND_MSG_PUB.Count_And_Get
186 ( p_count => x_msg_count ,
187 p_data => x_msg_data
188 );
189 EXCEPTION
190 WHEN apps_exception THEN
191 ROLLBACK TO Update_Privacy;
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
194 p_data => x_msg_data );
195 WHEN FND_API.G_EXC_ERROR THEN
196 ROLLBACK TO Update_Privacy;
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
199 p_data => x_msg_data );
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 ROLLBACK TO Update_Privacy;
202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
203 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
204 p_data => x_msg_data );
205 WHEN OTHERS THEN
206 ROLLBACK TO Update_Privacy;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
208 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
209 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
210 END IF;
211 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
212 p_data => x_msg_data );
213 END Update_Privacy;
214
215 PROCEDURE Update_Person(
216 x_return_status OUT NOCOPY VARCHAR2,
217 x_msg_count OUT NOCOPY NUMBER,
218 x_msg_data OUT NOCOPY VARCHAR2,
219 p_person_id IN NUMBER,
220 p_suffix IN VARCHAR2,
221 p_middle_name IN VARCHAR2,
222 p_pre_name_adjunct IN VARCHAR2,
223 p_sex IN VARCHAR2,
224 p_title IN VARCHAR2,
225 p_birth_dt IN DATE,
226 p_preferred_name IN VARCHAR2,
227 p_api_person_id IN VARCHAR2,
228 p_hz_parties_ovn IN OUT NOCOPY NUMBER,
229 p_attribute_category IN VARCHAR2,
230 p_attribute1 IN VARCHAR2,
231 p_attribute2 IN VARCHAR2,
232 p_attribute3 IN VARCHAR2,
233 p_attribute4 IN VARCHAR2,
234 p_attribute5 IN VARCHAR2,
235 p_attribute6 IN VARCHAR2,
236 p_attribute7 IN VARCHAR2,
237 p_attribute8 IN VARCHAR2,
238 p_attribute9 IN VARCHAR2,
239 p_attribute10 IN VARCHAR2,
240 p_attribute11 IN VARCHAR2,
241 p_attribute12 IN VARCHAR2,
242 p_attribute13 IN VARCHAR2,
243 p_attribute14 IN VARCHAR2,
244 p_attribute15 IN VARCHAR2,
245 p_attribute16 IN VARCHAR2,
246 p_attribute17 IN VARCHAR2,
247 p_attribute18 IN VARCHAR2,
248 p_attribute19 IN VARCHAR2,
249 p_attribute20 IN VARCHAR2,
250 p_attribute21 IN VARCHAR2,
251 p_attribute22 IN VARCHAR2,
252 p_attribute23 IN VARCHAR2,
253 p_attribute24 IN VARCHAR2
254 )
255 IS
256 l_message_name VARCHAR2(30);
257 l_app VARCHAR2(50);
258
259 CURSOR pref_id_cur IS
260 SELECT person_id_type, description, format_mask
261 FROM igs_pe_person_id_typ
262 WHERE preferred_ind ='Y';
263
264 pref_id_rec pref_id_cur%ROWTYPE;
265
266 CURSOR c_person(cp_person_id hz_parties.party_id%TYPE) IS
267 SELECT p.rowid row_id,
268 p.party_number person_number,
269 p.person_last_name surname ,
270 p.person_first_name given_names ,
271 NULL staff_member_ind ,
272 DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_ind,
273 pd.archive_exclusion_ind ,
274 pd.archive_dt ,
275 pd.purge_exclusion_ind ,
276 pd.purge_dt ,
277 pp.date_of_death deceased_date ,
278 pd.proof_of_ins ,
279 pd.proof_of_immu ,
280 p.salutation ,
281 pd.oracle_username ,
282 NULL email_addr ,
283 pd.level_of_qual level_of_qual_id ,
284 pd.military_service_reg ,
285 pd.veteran ,
286 p.object_version_number
287 FROM hz_parties p,
288 igs_pe_hz_parties pd,
289 hz_person_profiles pp
290 WHERE p.party_id = cp_person_id
291 AND p.party_id = pd.party_id (+)
292 AND p.party_id = pp.party_id
293 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
294
295 l_return_status VARCHAR2(1);
296 l_msg_count NUMBER;
297 l_msg_data VARCHAR(2000);
298 l_date DATE := sysdate;
299
300 BEGIN
301 -- Standard Start of API savepoint
302 SAVEPOINT Update_Person;
303
304 -- Initialize message list if p_init_msg_list is set to TRUE.
305 x_return_status := FND_API.G_RET_STS_SUCCESS;
306
307 FND_MSG_PUB.initialize;
308 -- API body
309 /* 1. Get the Preferred Person Id Type
310 2. Retrieve current row values to be passed to the procedure.
311 3. Call IGS_PE_PERSON_PKG.UPDATE_ROW procedure
312 */
313
314 FOR c_person_rec IN c_person(p_person_id) LOOP
315
316 OPEN pref_id_cur;
317 FETCH pref_id_cur INTO pref_id_rec;
318 CLOSE pref_id_cur;
319
320 igs_pe_person_pkg.update_row(
321 x_last_update_date => l_date,
322 x_return_status => l_return_status,
323 x_msg_count => l_msg_count,
324 x_msg_data => l_msg_data,
325 x_rowid => c_person_rec.row_id,
326 x_person_id => p_person_id,
327 x_person_number => c_person_rec.person_number,
328 x_surname => c_person_rec.surname,
329 x_middle_name => p_middle_name,
330 x_given_names => c_person_rec.given_names,
331 x_sex => p_sex,
332 x_title => p_title,
333 x_staff_member_ind => c_person_rec.staff_member_ind,
334 x_deceased_ind => c_person_rec.deceased_ind,
335 x_suffix => p_suffix,
336 x_pre_name_adjunct => p_pre_name_adjunct,
337 x_archive_exclusion_ind => c_person_rec.archive_exclusion_ind,
338 x_archive_dt => c_person_rec.archive_dt,
339 x_purge_exclusion_ind => c_person_rec.purge_exclusion_ind,
340 x_purge_dt => c_person_rec.purge_dt,
341 x_deceased_date => c_person_rec.deceased_date,
342 x_proof_of_ins => c_person_rec.proof_of_ins,
343 x_proof_of_immu => c_person_rec.proof_of_immu,
344 x_birth_dt => p_birth_dt,
345 x_salutation => c_person_rec.salutation,
346 x_oracle_username => c_person_rec.oracle_username,
347 x_preferred_given_name => p_preferred_name,
348 x_email_addr => c_person_rec.email_addr,
349 x_level_of_qual_id => c_person_rec.level_of_qual_id,
350 x_military_service_reg => c_person_rec.military_service_reg,
351 x_veteran => c_person_rec.veteran,
352 x_hz_parties_ovn => c_person_rec.object_version_number,
353 x_attribute_category => p_attribute_category,
354 x_attribute1 => p_attribute1,
355 x_attribute2 => p_attribute2,
356 x_attribute3 => p_attribute3,
357 x_attribute4 => p_attribute4,
358 x_attribute5 => p_attribute5,
359 x_attribute6 => p_attribute6,
360 x_attribute7 => p_attribute7,
361 x_attribute8 => p_attribute8,
362 x_attribute9 => p_attribute9,
363 x_attribute10 => p_attribute10,
364 x_attribute11 => p_attribute11,
365 x_attribute12 => p_attribute12,
366 x_attribute13 => p_attribute13,
367 x_attribute14 => p_attribute14,
368 x_attribute15 => p_attribute15,
369 x_attribute16 => p_attribute16,
370 x_attribute17 => p_attribute17,
371 x_attribute18 => p_attribute18,
372 x_attribute19 => p_attribute19,
373 x_attribute20 => p_attribute20,
374 x_person_id_type => pref_id_rec.person_id_type,
375 x_api_person_id => UPPER(p_api_person_id),
376 x_attribute21 => p_attribute21,
377 x_attribute22 => p_attribute22,
378 x_attribute23 => p_attribute23,
379 x_attribute24 => p_attribute24
380 );
381 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
382 raise FND_API.G_EXC_ERROR;
383 END IF;
384 END LOOP;
385 -- End of API body.
386
387 -- Standard call to get message count and if count is 1, get message info.
388 FND_MSG_PUB.Count_And_Get
389 ( p_count => x_msg_count ,
390 p_data => x_msg_data
391 );
392 EXCEPTION
393 WHEN apps_exception THEN
394 ROLLBACK TO Update_Person;
395
396 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
397
398 IF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
399 FND_MSG_PUB.initialize ;
400 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_UNIQUE_PID_SS');
401 FND_MESSAGE.SET_TOKEN('ALT_ID_DESC1', pref_id_rec.description);
402 FND_MESSAGE.SET_TOKEN('ALT_ID_DESC2', pref_id_rec.description);
403 IGS_GE_MSG_STACK.ADD;
404 END IF;
405
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
408 p_data => x_msg_data );
409
410 WHEN FND_API.G_EXC_ERROR THEN
411 ROLLBACK TO Update_Person;
412 x_return_status := FND_API.G_RET_STS_ERROR;
413 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
414 p_data => x_msg_data );
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO Update_Person;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
418 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
419 p_data => x_msg_data );
420 WHEN OTHERS THEN
421 ROLLBACK TO Update_Person;
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
424 p_data => x_msg_data );
425 END Update_Person;
426
427 PROCEDURE Update_Contact(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
430 p_commit IN VARCHAR2 := FND_API.G_FALSE,
431 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2,
435 x_id OUT NOCOPY NUMBER,
436 p_mode IN VARCHAR2,
437 p_person_id IN NUMBER,
438 p_contact_point_id IN NUMBER,
439 p_contact_point_ovn IN OUT NOCOPY NUMBER,
440 p_status IN VARCHAR2,
441 p_primary_flag IN VARCHAR2,
442 p_phone_area_code IN VARCHAR2,
443 p_phone_country_code IN VARCHAR2,
444 p_phone_number IN VARCHAR2,
445 p_phone_extension IN VARCHAR2,
446 p_phone_line_type IN VARCHAR2,
447 p_email_format IN VARCHAR2,
448 p_email_address IN VARCHAR2
449 )
450 IS
451 l_api_name CONSTANT VARCHAR2(30) := 'Update_Phone';
452 l_api_version CONSTANT NUMBER := 1.0;
453 l_rowid VARCHAR2(255);
454 CURSOR c_contact(cp_contact_point_id NUMBER) IS
455 SELECT rowid ,
456 attribute_category,
457 last_update_date,
458 attribute1 ,
459 attribute2 ,
460 attribute3 ,
461 attribute4 ,
462 attribute5 ,
463 attribute6 ,
464 attribute7 ,
465 attribute8 ,
466 attribute9 ,
467 attribute10,
468 attribute11,
469 attribute12,
470 attribute13,
471 attribute14,
472 attribute15,
473 attribute16,
474 attribute17,
475 attribute18,
476 attribute19,
477 attribute20
478 FROM hz_contact_points
479 WHERE contact_point_id = cp_contact_point_id;
480
481 l_contact_point_id hz_contact_points.contact_point_id%TYPE;
482 l_object_version_number hz_contact_points.object_version_number%TYPE;
483 l_date DATE;
484 l_return_status VARCHAR2(1);
485 l_msg_count NUMBER;
486 l_msg_data VARCHAR(2000);
487 l_phone_number VARCHAR(2000);
488 l_mode VARCHAR2(10);
489
490 CURSOR dup_email_cur(cp_person_id hz_contact_points.owner_table_id%TYPE,
491 cp_contact_point_type hz_contact_points.contact_point_type%TYPE,
492 cp_owner_table_name hz_contact_points.owner_table_name%TYPE,
493 cp_status hz_contact_points.status%TYPE,
494 cp_email_address hz_contact_points.email_address%TYPE) IS
495 SELECT contact_point_id, object_version_number
496 FROM hz_contact_points
497 WHERE owner_table_id = cp_person_id AND
498 contact_point_type = cp_contact_point_type AND
499 owner_table_name = cp_owner_table_name AND
500 status = cp_status AND
501 UPPER(EMAIL_ADDRESS) = UPPER(cp_email_address);
502
503 dup_email_rec dup_email_cur%ROWTYPE;
504
505 CURSOR dup_phone_cur(cp_person_id hz_contact_points.owner_table_id%TYPE,
506 cp_contact_point_type hz_contact_points.contact_point_type%TYPE,
507 cp_owner_table_name hz_contact_points.owner_table_name%TYPE,
508 cp_status hz_contact_points.status%TYPE,
509 cp_phone_number VARCHAR2) IS
510 SELECT contact_point_id, object_version_number
511 FROM hz_contact_points
512 WHERE owner_table_id = cp_person_id AND
513 contact_point_type = cp_contact_point_type AND
514 owner_table_name = cp_owner_table_name AND
515 status = cp_status AND
516 UPPER(phone_country_code||'-'||phone_area_code||'-'||phone_number||'-'||phone_extension) = UPPER(cp_phone_number);
517
518 dup_phone_rec dup_phone_cur%ROWTYPE;
519 BEGIN
520 -- Standard Start of API savepoint
521 SAVEPOINT Update_Phone;
522 -- Standard call to check for call compatibility.
523 IF NOT FND_API.Compatible_API_Call (l_api_version,
524 p_api_version,
525 l_api_name,
526 G_PKG_NAME)
527 THEN
528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
529 END IF;
530 -- Initialize message list if p_init_msg_list is set to TRUE.
531 x_return_status := FND_API.G_RET_STS_SUCCESS;
532 IF FND_API.to_Boolean (p_init_msg_list) THEN
533 FND_MSG_PUB.initialize;
534 END IF;
535
536 FND_MSG_PUB.initialize;
537
538 -- API body
539 -- Retrive existing information to be passed to the API.
540 -- Call IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP procedure
541 l_date := sysdate;
542 l_mode := p_mode;
543 l_contact_point_id := p_contact_point_id;
544 l_object_version_number := p_contact_point_ovn;
545
546 /*
547 While inserting a new record check whether there is already an Inactive record witht the same details.
548 If present then make that record Active. Else create a new record.
549 */
550 IF p_mode='INSERT' THEN
551 IF (p_email_format IS NULL) THEN
552
553 l_phone_number := p_phone_country_code||'-'||p_phone_area_code||'-'||p_phone_number||'-'||p_phone_extension;
554
555 OPEN dup_phone_cur(p_person_id,'PHONE','HZ_PARTIES','I',l_phone_number);
556 FETCH dup_phone_cur INTO dup_phone_rec;
557 CLOSE dup_phone_cur;
558
559 IF dup_phone_rec.contact_point_id IS NOT NULL THEN
560 l_mode := 'UPDATE';
561 l_contact_point_id := dup_phone_rec.contact_point_id;
562 l_object_version_number := dup_phone_rec.object_version_number;
563 END IF;
564
565 ELSE
566 OPEN dup_email_cur(p_person_id,'EMAIL','HZ_PARTIES','I',p_email_address);
567 FETCH dup_email_cur INTO dup_email_rec;
568 CLOSE dup_email_cur;
569
570 IF dup_email_rec.contact_point_id IS NOT NULL THEN
571 l_mode := 'UPDATE';
572 l_contact_point_id := dup_email_rec.contact_point_id;
573 l_object_version_number := dup_email_rec.object_version_number;
574 END IF;
575 END IF;
576 END IF;
577
578
579 IF l_mode ='UPDATE' THEN
580 FOR c_contacs_rec IN c_contact(l_contact_point_id) LOOP
581 l_rowid := c_contacs_rec.rowid;
582 l_date := c_contacs_rec.last_update_date;
583 IF (p_email_format IS NULL) THEN -- Insert phone
584 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP(
585 p_action => 'UPDATE',
586 p_rowid => l_rowid,
587 p_status => p_status,
588 p_owner_table_name => 'HZ_PARTIES',
589 p_owner_table_id => p_person_id,
590 P_primary_flag => p_primary_flag,
591 p_phone_country_code => p_phone_country_code,
592 p_phone_area_code => p_phone_area_code,
593 p_phone_number => p_phone_number,
594 p_phone_extension => p_phone_extension,
595 p_phone_line_type => p_phone_line_type,
596 p_return_status => l_return_status,
597 p_msg_data => l_msg_data,
598 p_last_update_date => l_date,
599 p_contact_point_id => l_contact_point_id,
600 p_contact_point_ovn => l_object_version_number,
601 p_attribute_category => c_contacs_rec.attribute_category,
602 p_attribute1 => c_contacs_rec.attribute1,
603 p_attribute2 => c_contacs_rec.attribute2,
604 p_attribute3 => c_contacs_rec.attribute3,
605 p_attribute4 => c_contacs_rec.attribute4,
606 p_attribute5 => c_contacs_rec.attribute5,
607 p_attribute6 => c_contacs_rec.attribute6,
608 p_attribute7 => c_contacs_rec.attribute7,
609 p_attribute8 => c_contacs_rec.attribute8,
610 p_attribute9 => c_contacs_rec.attribute9,
611 p_attribute10 => c_contacs_rec.attribute10,
612 p_attribute11 => c_contacs_rec.attribute11,
613 p_attribute12 => c_contacs_rec.attribute12,
614 p_attribute13 => c_contacs_rec.attribute13,
615 p_attribute14 => c_contacs_rec.attribute14,
616 p_attribute15 => c_contacs_rec.attribute15,
617 p_attribute16 => c_contacs_rec.attribute16,
618 p_attribute17 => c_contacs_rec.attribute17,
619 p_attribute18 => c_contacs_rec.attribute18,
620 p_attribute19 => c_contacs_rec.attribute19,
621 p_attribute20 => c_contacs_rec.attribute20
622 ) ;
623 ELSE
624 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
625 p_action => 'UPDATE',
626 p_rowid => l_rowid,
627 p_status => p_status,
628 p_owner_table_name => 'HZ_PARTIES',
629 p_owner_table_id => p_person_id,
630 P_primary_flag => p_primary_flag,
631 p_email_format => p_email_format,
632 p_email_address => p_email_address,
633 p_return_status => l_return_status,
634 p_msg_data => l_msg_data,
635 p_last_update_date => l_date,
636 p_contact_point_id => l_contact_point_id,
637 p_contact_point_ovn => l_object_version_number,
638 p_attribute_category => c_contacs_rec.attribute_category,
639 p_attribute1 => c_contacs_rec.attribute1,
640 p_attribute2 => c_contacs_rec.attribute2,
641 p_attribute3 => c_contacs_rec.attribute3,
642 p_attribute4 => c_contacs_rec.attribute4,
643 p_attribute5 => c_contacs_rec.attribute5,
644 p_attribute6 => c_contacs_rec.attribute6,
645 p_attribute7 => c_contacs_rec.attribute7,
646 p_attribute8 => c_contacs_rec.attribute8,
647 p_attribute9 => c_contacs_rec.attribute9,
648 p_attribute10 => c_contacs_rec.attribute10,
649 p_attribute11 => c_contacs_rec.attribute11,
650 p_attribute12 => c_contacs_rec.attribute12,
651 p_attribute13 => c_contacs_rec.attribute13,
652 p_attribute14 => c_contacs_rec.attribute14,
653 p_attribute15 => c_contacs_rec.attribute15,
654 p_attribute16 => c_contacs_rec.attribute16,
655 p_attribute17 => c_contacs_rec.attribute17,
656 p_attribute18 => c_contacs_rec.attribute18,
657 p_attribute19 => c_contacs_rec.attribute19,
658 p_attribute20 => c_contacs_rec.attribute20
659 ) ;
660 END IF;
661 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
662 raise FND_API.G_EXC_ERROR;
663 END IF;
664 END LOOP;
665 ELSIF l_mode ='INSERT' THEN
666 IF (p_email_format IS NULL) THEN -- Insert phone
667 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKP(
668 p_action => 'INSERT',
669 p_rowid => l_rowid ,
670 p_status => p_status,
671 p_owner_table_name => 'HZ_PARTIES',
672 p_owner_table_id => p_person_id,
673 P_primary_flag => p_primary_flag,
674 p_phone_country_code => p_phone_country_code,
675 p_phone_area_code => p_phone_area_code,
676 p_phone_number => p_phone_number,
677 p_phone_extension => p_phone_extension,
678 p_phone_line_type => p_phone_line_type,
679 p_return_status => l_return_status,
680 p_msg_data => l_msg_data,
681 p_last_update_date => l_date,
682 p_contact_point_id => l_contact_point_id,
683 p_contact_point_ovn => p_contact_point_ovn
684 ) ;
685 ELSE
686 IGS_PE_CONTACT_POINT_PKG.HZ_CONTACT_POINTS_AKE(
687 p_action => 'INSERT',
688 p_rowid => l_rowid ,
689 p_status => p_status,
690 p_owner_table_name => 'HZ_PARTIES',
691 p_owner_table_id => p_person_id,
692 P_primary_flag => p_primary_flag,
693 p_email_format => p_email_format,
694 p_email_address => p_email_address,
695 p_return_status => l_return_status,
696 p_msg_data => l_msg_data,
697 p_last_update_date => l_date,
698 p_contact_point_id => l_contact_point_id,
699 p_contact_point_ovn => p_contact_point_ovn
700 ) ;
701 END IF;
702 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
703 raise FND_API.G_EXC_ERROR;
704 END IF;
705 END IF;
706 x_id := l_contact_point_id;
707 -- End of API body.
708 -- Standard check of p_commit.
709 IF FND_API.To_Boolean( p_commit ) THEN
710 COMMIT WORK;
711 END IF;
712 -- Standard call to get message count and if count is 1, get message info.
713 FND_MSG_PUB.Count_And_Get
714 ( p_count => x_msg_count ,
715 p_data => x_msg_data
716 );
717 EXCEPTION
718 WHEN apps_exception THEN
719 ROLLBACK TO Update_Phone;
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
722 p_data => x_msg_data );
723 WHEN FND_API.G_EXC_ERROR THEN
724 ROLLBACK TO Update_Phone;
725 x_return_status := FND_API.G_RET_STS_ERROR;
726 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
727 p_data => x_msg_data );
728 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
729 ROLLBACK TO Update_Person;
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
732 p_data => x_msg_data );
733 WHEN OTHERS THEN
734 ROLLBACK TO Update_Phone;
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
736 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
737 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
738 END IF;
739 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
740 p_data => x_msg_data );
741 END Update_Contact;
742
743 PROCEDURE Update_Address(
744 p_api_version IN NUMBER,
745 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
746 p_commit IN VARCHAR2 := FND_API.G_FALSE,
747 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_count OUT NOCOPY NUMBER,
750 x_msg_data OUT NOCOPY VARCHAR2,
751 x_id OUT NOCOPY NUMBER,
752 p_mode IN VARCHAR2,
753 p_person_id IN NUMBER,
754 p_location_id IN NUMBER,
755 p_start_dt IN DATE,
756 p_end_dt IN DATE,
757 p_party_site_id IN NUMBER,
758 p_addr_line_1 IN VARCHAR2,
759 p_addr_line_2 IN VARCHAR2,
760 p_addr_line_3 IN VARCHAR2,
761 p_addr_line_4 IN VARCHAR2,
762 p_city IN VARCHAR2,
763 p_state IN VARCHAR2,
764 p_province IN VARCHAR2,
765 p_county IN VARCHAR2,
766 p_country IN VARCHAR2,
767 p_country_cd IN VARCHAR2,
768 p_postal_code IN VARCHAR2,
769 p_ident_addr_flag IN VARCHAR2,
770 p_location_ovn IN OUT NOCOPY hz_locations.object_version_number%TYPE,
771 p_party_site_ovn IN OUT NOCOPY hz_party_sites.object_version_number%TYPE,
772 p_status IN hz_party_sites.status%TYPE
773 )
774 IS
775
776 BEGIN
777 -- Stubbed the procedure since we will be using the CPUI.
778 NULL;
779 END Update_Address;
780
781 PROCEDURE Update_Usage(
782 p_api_version IN NUMBER,
783 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
784 p_commit IN VARCHAR2 := FND_API.G_FALSE,
785 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
786 x_return_status OUT NOCOPY VARCHAR2,
787 x_msg_count OUT NOCOPY NUMBER,
788 x_msg_data OUT NOCOPY VARCHAR2,
789 x_id OUT NOCOPY NUMBER,
790 p_mode IN VARCHAR2,
791 p_party_site_use_id IN NUMBER,
792 p_party_site_id IN NUMBER,
793 p_site_use_type IN VARCHAR2,
794 p_location IN VARCHAR2,
795 p_site_use_id IN NUMBER,
796 p_active IN VARCHAR2,
797 p_hz_party_site_use_ovn IN OUT NOCOPY NUMBER
798 )
799 /*
800 Created By : mesriniv
801 Date Created By : 2001/07/12
802 Change History :
803 WHO WHEN WHAT
804 pkpatel 15-MAR-2002 Bug no.2238946 :Added the parameter p_status in the call to igs_pe_party_site_use_pkg
805 */
806 IS
807 l_api_name CONSTANT VARCHAR2(30) := 'Update_Usage';
808 l_api_version CONSTANT NUMBER := 1.0;
809 l_rowid VARCHAR2(255);
810 l_party_site_use_id hz_party_site_uses.party_site_use_id%TYPE := p_party_site_use_id;
811 l_party_site_id hz_party_site_uses.party_site_id%TYPE := p_party_site_id;
812 l_site_use_id igs_pe_partysiteuse_v.site_use_id%TYPE := p_site_use_id;
813 l_date DATE :=sysdate;
814 l_return_status VARCHAR2(1);
815 l_msg_count NUMBER;
816 l_msg_data VARCHAR(2000);
817 l_mode VARCHAR(10);
818
819 CURSOR dup_addrs_cur(cp_site_use_type hz_party_site_uses.site_use_type%TYPE,
820 cp_party_site_id hz_party_site_uses.party_site_id%TYPE,
821 cp_status hz_party_site_uses.status%TYPE
822 )IS
823
824 SELECT party_site_use_id, object_version_number
825 FROM HZ_PARTY_SITE_USES
826 WHERE site_use_type = cp_site_use_type AND
827 party_site_id = cp_party_site_id AND
828 status = cp_status;
829
830 dup_addrs_rec dup_addrs_cur%ROWTYPE;
831
832
833 BEGIN
834 -- Standard Start of API savepoint
835 SAVEPOINT Update_Usage;
836 -- Standard call to check for call compatibility.
837 IF NOT FND_API.Compatible_API_Call (l_api_version,
838 p_api_version,
839 l_api_name,
840 G_PKG_NAME)
841 THEN
842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
843 END IF;
844 -- Initialize message list if p_init_msg_list is set to TRUE.
845 x_return_status := FND_API.G_RET_STS_SUCCESS;
846 IF FND_API.to_Boolean (p_init_msg_list) THEN
847 FND_MSG_PUB.initialize;
848 END IF;
849
850 FND_MSG_PUB.initialize;
851
852 -- API body
853 -- Retrieve current row values to be passed to the procedure.
854 -- Call IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK procedure
855
856 l_mode:= p_mode;
857
858 IF p_mode='INSERT' THEN
859 OPEN dup_addrs_cur(p_site_use_type,p_party_site_id,'I');
860 FETCH dup_addrs_cur INTO dup_addrs_rec;
861 CLOSE dup_addrs_cur;
862
863
864 IF dup_addrs_rec.party_site_use_id IS NOT NULL THEN
865 l_mode := 'UPDATE';
866 l_party_site_use_id := dup_addrs_rec.party_site_use_id;
867 p_hz_party_site_use_ovn := dup_addrs_rec.object_version_number;
868 END IF;
869 END IF;
870
871 igs_pe_party_site_use_pkg.hz_party_site_uses_ak(
872 p_action => l_mode,
873 p_rowid => l_rowid,
874 p_party_site_use_id => l_party_site_use_id,
875 p_party_site_id => l_party_site_id,
876 p_site_use_type => p_site_use_type,
877 p_return_status => l_return_status,
878 p_msg_data => l_msg_data,
879 p_last_update_date => l_date,
880 p_site_use_last_update_date => l_date,
881 p_profile_last_update_date => l_date,
882 p_status => p_active,
883 p_hz_party_site_use_ovn => p_hz_party_site_use_ovn
884 );
885 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
886 raise FND_API.G_EXC_ERROR;
887 END IF;
888 x_id := l_party_site_use_id;
889 -- End of API body.
890 -- Standard check of p_commit.
891 IF FND_API.To_Boolean( p_commit ) THEN
892 COMMIT WORK;
893 END IF;
894 -- Standard call to get message count and if count is 1, get message info.
895 FND_MSG_PUB.Count_And_Get
896 ( p_count => x_msg_count ,
897 p_data => x_msg_data
898 );
899 EXCEPTION
900 WHEN apps_exception THEN
901 ROLLBACK TO Update_Usage;
902 x_return_status := FND_API.G_RET_STS_ERROR;
903 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
904 p_data => x_msg_data );
905 WHEN FND_API.G_EXC_ERROR THEN
906 ROLLBACK TO Update_Usage;
907 x_return_status := FND_API.G_RET_STS_ERROR;
908 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
909 p_data => x_msg_data );
910 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
911 ROLLBACK TO Update_Usage;
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
914 p_data => x_msg_data );
915 WHEN OTHERS THEN
916 ROLLBACK TO Update_Usage;
917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
918 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
919 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
920 END IF;
921 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
922 p_data => x_msg_data );
923 END Update_Usage;
924
925 PROCEDURE Update_Employment(
926 p_api_version IN NUMBER,
927 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
928 p_commit IN VARCHAR2 := FND_API.G_FALSE,
929 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
930 x_return_status OUT NOCOPY VARCHAR2,
931 x_msg_count OUT NOCOPY NUMBER,
932 x_msg_data OUT NOCOPY VARCHAR2,
933 x_id OUT NOCOPY NUMBER,
934 p_mode IN VARCHAR2,
935 p_person_id IN NUMBER,
936 p_employment_history_id IN NUMBER,
937 p_start_dt IN DATE,
938 p_end_dt IN DATE,
939 p_position IN VARCHAR2,
940 p_weekly_work_hours IN NUMBER,
941 p_comments IN VARCHAR2,
942 p_employer IN VARCHAR2,
943 p_employed_by_division_name IN VARCHAR2,
944 p_object_version_number IN OUT NOCOPY NUMBER,
945 p_employed_by_party_id IN NUMBER,
946 p_reason_for_leaving IN VARCHAR2,
947 p_type_of_employment IN VARCHAR2,
948 p_tenure_of_employment IN VARCHAR2
949 )
950 IS
951 l_api_name CONSTANT VARCHAR2(30) := 'Update_Employment';
952 l_api_version CONSTANT NUMBER := 1.0;
953 l_rowid VARCHAR2(255);
954 l_employment_history_id igs_ad_hz_emp_dtl.employment_history_id%TYPE := p_employment_history_id;
955 l_return_status VARCHAR2(1);
956 l_msg_count NUMBER;
957 l_msg_data VARCHAR(2000);
958 CURSOR c_rowid IS
959 SELECT row_id ,
960 contact,
961 fraction_of_employment ,
962 occupational_title_code,
963 occupational_title ,
964 branch ,
965 military_rank,
966 served ,
967 station,
968 reason_for_leaving,
969 employed_by_party_id
970 FROM igs_ad_emp_dtl
971 WHERE employment_history_id = p_employment_history_id;
972 BEGIN
973 -- Standard Start of API savepoint
974 SAVEPOINT Update_Employment;
975 -- Standard call to check for call compatibility.
976 IF NOT FND_API.Compatible_API_Call (l_api_version,
977 p_api_version,
978 l_api_name,
979 G_PKG_NAME)
980 THEN
981 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
982 END IF;
983 -- Initialize message list if p_init_msg_list is set to TRUE.
984 x_return_status := FND_API.G_RET_STS_SUCCESS;
985 IF FND_API.to_Boolean (p_init_msg_list) THEN
986 FND_MSG_PUB.initialize;
987 END IF;
988
989 FND_MSG_PUB.initialize;
990
991 -- API body
992 -- If mode='INSERT' call igs_ad_emp_dtl_pkg.INSERT_ROW
993 -- If mode='UPDATE': retrieve current row values to be passed to the procedure and call igs_ad_emp_dtl_pkg.UPDATE_ROW
994 -- If mode='DELETE' call igs_ad_emp_dtl_pkg.DELETE_ROW
995
996 IF p_start_dt IS NOT NULL AND p_end_dt IS NOT NULL THEN
997 IF p_start_dt > p_end_dt THEN
998 FND_MESSAGE.SET_NAME ('IGS','IGS_GE_INVALID_DATE');
999 IGS_GE_MSG_STACK.ADD;
1000 APP_EXCEPTION.RAISE_EXCEPTION;
1001 END IF;
1002 END IF;
1003
1004 IF p_mode='UPDATE' THEN
1005 FOR c_rowid_rec IN c_rowid LOOP
1006 l_rowid := c_rowid_rec.row_id;
1007 igs_ad_emp_dtl_pkg.update_row(
1008 x_rowid => l_rowid,
1009 x_employment_history_id => l_employment_history_id,
1010 x_person_id => p_person_id,
1011 x_start_dt => p_start_dt,
1012 x_end_dt => p_end_dt,
1013 x_type_of_employment => p_type_of_employment,
1014 x_fraction_of_employment => c_rowid_rec.fraction_of_employment,
1015 x_tenure_of_employment => p_tenure_of_employment,
1016 x_position => p_position,
1017 x_occupational_title_code => c_rowid_rec.occupational_title_code,
1018 x_occupational_title => c_rowid_rec.occupational_title,
1019 x_weekly_work_hours => p_weekly_work_hours,
1020 x_comments => p_comments,
1021 x_employer => p_employer,
1022 x_employed_by_division_name => p_employed_by_division_name,
1023 x_branch => c_rowid_rec.branch,
1024 x_military_rank => c_rowid_rec.military_rank,
1025 x_served => c_rowid_rec.served,
1026 x_station => c_rowid_rec.station,
1027 x_contact => c_rowid_rec.contact,
1028 x_return_status => l_return_status,
1029 x_object_version_number => p_object_version_number,
1030 x_employed_by_party_id => c_rowid_rec.employed_by_party_id,
1031 x_reason_for_leaving => c_rowid_rec.reason_for_leaving,
1032 x_msg_data => l_msg_data );
1033
1034 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1035 raise FND_API.G_EXC_ERROR;
1036 END IF;
1037 END LOOP;
1038 ELSIF p_mode='INSERT' THEN
1039 igs_ad_emp_dtl_pkg.insert_row(
1040 x_rowid => l_rowid,
1041 x_employment_history_id => l_employment_history_id,
1042 x_person_id => p_person_id,
1043 x_start_dt => p_start_dt,
1044 x_end_dt => p_end_dt,
1045 x_type_of_employment => p_type_of_employment,
1046 x_fraction_of_employment => '',
1047 x_tenure_of_employment => p_tenure_of_employment,
1048 x_position => p_position,
1049 x_occupational_title_code => '',
1050 x_occupational_title => '',
1051 x_weekly_work_hours => p_weekly_work_hours,
1052 x_comments => p_comments,
1053 x_employer => p_employer,
1054 x_employed_by_division_name => p_employed_by_division_name,
1055 x_branch => '',
1056 x_military_rank => '',
1057 x_served => '',
1058 x_station => '',
1059 x_contact => '',
1060 x_return_status => l_return_status,
1061 x_msg_data => l_msg_data,
1062 x_object_version_number => p_object_version_number,
1063 x_employed_by_party_id => p_employed_by_party_id,
1064 x_reason_for_leaving => p_reason_for_leaving
1065 );
1066 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1067 raise FND_API.G_EXC_ERROR;
1068 END IF;
1069 ELSIF p_mode='DELETE' THEN
1070 FOR c_rowid_rec IN c_rowid LOOP
1071 l_rowid := c_rowid_rec.row_id;
1072 igs_ad_emp_dtl_pkg.delete_row(
1073 x_rowid => l_rowid);
1074 END LOOP;
1075 END IF;
1076 x_id := l_employment_history_id;
1077 -- End of API body.
1078 -- Standard check of p_commit.
1079 IF FND_API.To_Boolean( p_commit ) THEN
1080 COMMIT WORK;
1081 END IF;
1082 -- Standard call to get message count and if count is 1, get message info.
1083 FND_MSG_PUB.Count_And_Get
1084 ( p_count => x_msg_count ,
1085 p_data => x_msg_data
1086 );
1087 EXCEPTION
1088 WHEN apps_exception THEN
1089 ROLLBACK TO Update_Employment;
1090 x_return_status := FND_API.G_RET_STS_ERROR;
1091 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1092 p_data => x_msg_data );
1093 WHEN FND_API.G_EXC_ERROR THEN
1094 ROLLBACK TO Update_Employment;
1095 x_return_status := FND_API.G_RET_STS_ERROR;
1096 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1097 p_data => x_msg_data );
1098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1099 ROLLBACK TO Update_Employment;
1100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1101 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1102 p_data => x_msg_data );
1103 WHEN OTHERS THEN
1104 ROLLBACK TO Update_Employment;
1105 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1106 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1107 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1108 END IF;
1109 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1110 p_data => x_msg_data );
1111 END Update_Employment;
1112
1113 PROCEDURE Update_Emergency(
1114 p_api_version IN NUMBER,
1115 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1116 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1117 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1118 x_return_status OUT NOCOPY VARCHAR2,
1119 x_msg_count OUT NOCOPY NUMBER,
1120 x_msg_data OUT NOCOPY VARCHAR2,
1121 x_id OUT NOCOPY NUMBER,
1122 p_mode IN VARCHAR2,
1123 p_em_person_id IN NUMBER,
1124 p_person_id IN NUMBER,
1125 p_given_name IN VARCHAR2,
1126 p_surname IN VARCHAR2,
1127 p_middle_name IN VARCHAR2,
1128 p_preferred_name IN VARCHAR2,
1129 p_birthdate IN DATE,
1130 p_pre_name_adjunct IN VARCHAR2,
1131 p_suffix IN VARCHAR2,
1132 p_title IN VARCHAR2,
1133 p_rel_end IN VARCHAR2,
1134 p_hz_parties_ovn IN OUT NOCOPY NUMBER,
1135 p_hz_rel_ovn IN OUT NOCOPY NUMBER
1136 )
1137 IS
1138 BEGIN
1139 NULL;
1140 -- Stubbed the procedure since its no longer used
1141 END Update_Emergency;
1142
1143 PROCEDURE Update_Dates(
1144 p_api_version IN NUMBER,
1145 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1146 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1147 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
1148 x_return_status OUT NOCOPY VARCHAR2,
1149 x_msg_count OUT NOCOPY NUMBER,
1150 x_msg_data OUT NOCOPY VARCHAR2,
1151 p_person_id IN NUMBER,
1152 p_course_cd IN VARCHAR2,
1153 p_version_number IN NUMBER,
1154 p_nom_year IN VARCHAR2,
1155 p_nom_period IN VARCHAR2,
1156 p_action IN VARCHAR2
1157 )
1158 IS
1159 l_api_name CONSTANT VARCHAR2(30) := 'Update_Dates';
1160 l_api_version CONSTANT NUMBER := 1.0;
1161 l_rowid VARCHAR2(255);
1162
1163 CURSOR c_get_stdnt_ps_att_dtls(cp_person_id igs_en_stdnt_ps_att_all.person_id%TYPE,
1164 cp_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE) IS
1165 SELECT *
1166 FROM igs_en_stdnt_ps_att
1167 WHERE person_id = cp_person_id AND
1168 course_cd = cp_course_cd;
1169
1170 -- l_stdnt_ps_attempt_dtls_rec c_get_stdnt_ps_att_dtls%ROWTYPE;
1171
1172 BEGIN
1173 -- Standard Start of API savepoint
1174 SAVEPOINT Update_Dates;
1175 -- Standard call to check for call compatibility.
1176 IF NOT FND_API.Compatible_API_Call (l_api_version,
1177 p_api_version,
1178 l_api_name,
1179 G_PKG_NAME)
1180 THEN
1181 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1182 END IF;
1183 -- Initialize message list if p_init_msg_list is set to TRUE.
1184 x_return_status := FND_API.G_RET_STS_SUCCESS;
1185 FND_MSG_PUB.initialize;
1186
1187 IF P_ACTION = 'LOCK' THEN
1188 FOR l_stdnt_ps_attempt_dtls_rec IN c_get_stdnt_ps_att_dtls(p_person_id, p_course_cd) LOOP
1189 IGS_EN_STDNT_PS_ATT_PKG.LOCK_ROW(
1190 X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
1191 X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
1192 X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
1193 X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
1194 X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
1195 X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
1196 X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
1197 X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
1198 X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
1199 X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
1200 X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
1201 X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
1202 X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
1203 X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
1204 X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
1205 X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
1206 X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
1207 X_STUDENT_CONFIRMED_IND => l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind,
1208 X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
1209 X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
1210 X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
1211 X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
1212 X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
1213 X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
1214 X_DISCONTINUED_DT => l_stdnt_ps_attempt_dtls_rec.DISCONTINUED_DT,
1215 X_DISCONTINUATION_REASON_CD => l_stdnt_ps_attempt_dtls_rec.DISCONTINUATION_REASON_CD,
1216 X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
1217 X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
1218 X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
1219 X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
1220 X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
1221 X_NOMINATED_COMPLETION_YR => p_nom_year,
1222 X_NOMINATED_COMPLETION_PERD => p_nom_period,
1223 X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
1224 X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
1225 X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
1226 X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
1227 X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
1228 X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
1229 X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
1230 X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
1231 x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
1232 x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
1233 X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
1234 x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
1235 x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
1236 x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
1237 x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
1238 x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
1239 x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
1240 x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
1241 X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
1242 X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
1243 X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
1244 X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
1245 X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
1246 X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
1247 X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
1248 X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
1249 X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
1250 X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
1251 X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
1252 X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
1253 X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
1254 X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
1255 X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
1256 X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
1257 X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
1258 X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
1259 X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
1260 X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
1261 X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
1262 X_FUTURE_DATED_TRANS_FLAG => l_stdnt_ps_attempt_dtls_rec.future_dated_trans_flag
1263 );
1264 END LOOP;
1265 ELSE
1266 -- API body
1267 --Update IGS_EN_STDNT_PS_ATT_ALL table.
1268 FOR l_stdnt_ps_attempt_dtls_rec IN c_get_stdnt_ps_att_dtls(p_person_id, p_course_cd) LOOP
1269
1270 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
1271 X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
1272 X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
1273 X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
1274 X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
1275 X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
1276 X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
1277 X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
1278 X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
1279 X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
1280 X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
1281 X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
1282 X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
1283 X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
1284 X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
1285 X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
1286 X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
1287 X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
1288 X_STUDENT_CONFIRMED_IND => l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind,
1289 X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
1290 X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
1291 X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
1292 X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
1293 X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
1294 X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
1295 X_DISCONTINUED_DT => l_stdnt_ps_attempt_dtls_rec.DISCONTINUED_DT,
1296 X_DISCONTINUATION_REASON_CD => l_stdnt_ps_attempt_dtls_rec.DISCONTINUATION_REASON_CD,
1297 X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
1298 X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
1299 X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
1300 X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
1301 X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
1302 X_NOMINATED_COMPLETION_YR => p_nom_year,
1303 X_NOMINATED_COMPLETION_PERD => p_nom_period,
1304 X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
1305 X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
1306 X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
1307 X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
1308 X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
1309 X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
1310 X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
1311 X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
1312 x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
1313 x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
1314 X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
1315 x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
1316 x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
1317 x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
1318 x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
1319 x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
1320 x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
1321 x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
1322 X_MODE => 'R',
1323 X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
1324 X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
1325 X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
1326 X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
1327 X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
1328 X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
1329 X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
1330 X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
1331 X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
1332 X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
1333 X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
1334 X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
1335 X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
1336 X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
1337 X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
1338 X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
1339 X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
1340 X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
1341 X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
1342 X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
1343 X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
1344 X_FUTURE_DATED_TRANS_FLAG => l_stdnt_ps_attempt_dtls_rec.future_dated_trans_flag
1345 );
1346 END LOOP;
1347 END IF;
1348 -- End of API body.
1349 -- Standard check of p_commit.
1350 IF FND_API.To_Boolean( p_commit ) THEN
1351 COMMIT WORK;
1352 END IF;
1353 -- Standard call to get message count and if count is 1, get message info.
1354 FND_MSG_PUB.Count_And_Get
1355 ( p_count => x_msg_count ,
1356 p_data => x_msg_data
1357 );
1358 EXCEPTION
1359 WHEN apps_exception THEN
1360 ROLLBACK TO Update_Dates;
1361 x_return_status := FND_API.G_RET_STS_ERROR;
1362 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1363 p_data => x_msg_data );
1364 WHEN FND_API.G_EXC_ERROR THEN
1365 ROLLBACK TO Update_Dates;
1366 x_return_status := FND_API.G_RET_STS_ERROR;
1367 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,
1368 p_data => x_msg_data );
1369 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1370 ROLLBACK TO Update_Dates;
1371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1372 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1373 p_data => x_msg_data );
1374 WHEN OTHERS THEN
1375 ROLLBACK TO Update_Dates;
1376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1377 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1378 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1379 END IF;
1380 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1381 p_data => x_msg_data );
1382 END Update_Dates;
1383
1384 FUNCTION Get_Relationship_type RETURN VARCHAR2 IS
1385 BEGIN
1386 return 'CONTACT_OF';
1387 END Get_Relationship_type;
1388
1389 PROCEDURE Update_Biographic (
1390 P_PERSON_ID IN NUMBER,
1391 P_ETHNICITY IN VARCHAR2,
1392 P_MARITAL_STATUS IN VARCHAR2,
1393 P_MARITAL_STATUS_DATE IN DATE,
1394 P_BIRTH_CITY IN VARCHAR2,
1395 P_BIRTH_COUNTRY IN VARCHAR2,
1396 P_VETERAN IN VARCHAR2,
1397 P_RELIGION_CD IN VARCHAR2,
1398 P_HZ_OVN IN NUMBER,
1399 P_RETURN_STATUS OUT NOCOPY VARCHAR2,
1400 P_MSG_COUNT OUT NOCOPY NUMBER,
1401 P_MSG_DATA OUT NOCOPY VARCHAR2,
1402 p_caller in varchar2
1403 ) IS
1404
1405 l_person_rec_type Hz_Party_V2Pub.PERSON_REC_TYPE;
1406 l_party_rec_type Hz_Party_V2Pub.PARTY_REC_TYPE;
1407 v_party_last_update_date hz_person_profiles.last_update_date%TYPE;
1408 lv_perosn_profile_id hz_person_profiles.person_profile_id%TYPE;
1409
1410 CURSOR pehz_cur (cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
1411 SELECT pehz.ROWID, pehz.*
1412 FROM IGS_PE_HZ_PARTIES pehz
1413 WHERE party_id = cp_person_id;
1414
1415 tlinfo2 pehz_cur%ROWTYPE;
1416
1417 CURSOR c_pe_stat (cp_person_id igs_pe_hz_parties.party_id%TYPE) IS
1418 SELECT
1419 p.rowid row_id,
1420 pp.person_profile_id,
1421 p.party_id person_id,
1422 p.party_number person_number,
1423 pp.effective_start_date,
1424 pp.effective_end_date,
1425 pp.declared_ethnicity ethnic_origin_id,
1426 pp.marital_status,
1427 pp.marital_status_effective_date,
1428 pp.internal_flag,
1429 sd.religion_cd religion,
1430 sd.next_to_kin,
1431 pp.place_of_birth,
1432 sd.socio_eco_cd socio_eco_status,
1433 sd.further_education_cd further_education,
1434 pp.household_size number_in_family,
1435 pp.household_income ann_family_income,
1436 sd.in_state_tuition,
1437 sd.tuition_st_date,
1438 sd.tuition_end_date,
1439 sd.matr_cal_type,
1440 sd.matr_sequence_number,
1441 sd.init_cal_type,
1442 sd.init_sequence_number,
1443 sd.recent_cal_type,
1444 sd.recent_sequence_number,
1445 sd.catalog_cal_type,
1446 sd.catalog_sequence_number,
1447 sd.attribute_category attribute_category,
1448 sd.attribute1 attribute1,
1449 sd.attribute2 attribute2,
1450 sd.attribute3 attribute3,
1451 sd.attribute4 attribute4,
1452 sd.attribute5 attribute5,
1453 sd.attribute6 attribute6,
1454 sd.attribute7 attribute7,
1455 sd.attribute8 attribute8,
1456 sd.attribute9 attribute9,
1457 sd.attribute10 attribute10,
1458 sd.attribute11 attribute11,
1459 sd.attribute12 attribute12,
1460 sd.attribute13 attribute13,
1461 sd.attribute14 attribute14,
1462 sd.attribute15 attribute15,
1463 sd.attribute16 attribute16,
1464 sd.attribute17 attribute17,
1465 sd.attribute18 attribute18,
1466 sd.attribute19 attribute19,
1467 sd.attribute20 attribute20,
1468 pp.global_attribute_category,
1469 pp.global_attribute1,
1470 pp.global_attribute2,
1471 pp.global_attribute3,
1472 pp.global_attribute4,
1473 pp.global_attribute5,
1474 pp.global_attribute6,
1475 pp.global_attribute7,
1476 pp.global_attribute8,
1477 pp.global_attribute9,
1478 pp.global_attribute10,
1479 pp.global_attribute11,
1480 pp.global_attribute12,
1481 pp.global_attribute13,
1482 pp.global_attribute14,
1483 pp.global_attribute15,
1484 pp.global_attribute16,
1485 pp.global_attribute17,
1486 pp.global_attribute18,
1487 pp.global_attribute19,
1488 pp.global_attribute20,
1489 pp.person_initials,
1490 pp.primary_contact_id,
1491 pp.personal_income,
1492 pp.head_of_household_flag,
1493 pp.content_source_type,
1494 pp.content_source_number,
1495 p.object_version_number object_version_number,
1496 sd.birth_cntry_resn_code
1497 FROM
1498 hz_person_profiles pp,
1499 igs_pe_stat_details sd,
1500 hz_parties p
1501 WHERE
1502 sd.person_id(+) = p.party_id AND
1503 pp.party_id = p.party_id AND
1504 SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date, SYSDATE) AND
1505 p.party_id = cp_person_id;
1506
1507 cv_pe_stat c_pe_stat%ROWTYPE;
1508
1509 CURSOR get_dob_dt_cur(cp_person_id igs_pe_passport.person_id%TYPE)
1510 IS
1511 SELECT birth_date
1512 FROM igs_pe_person_base_v
1513 WHERE person_id = cp_person_id;
1514
1515 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
1516 v_mar_dt VARCHAR2(100);
1517 v_other_dt VARCHAR2(100);
1518 l_marital_date DATE;
1519 l_ethnicity igs_pe_stat_v.ethnic_origin%TYPE;
1520 l_religion igs_pe_stat_v.religion%TYPE;
1521 BEGIN
1522 SAVEPOINT Update_Biographic;
1523 -- Initialize message list if p_init_msg_list is set to TRUE.
1524 P_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1525 FND_MSG_PUB.initialize;
1526
1527 -- API body
1528
1529 IF p_marital_status_date IS NOT NULL THEN
1530 IF p_marital_status_date > TRUNC(SYSDATE) THEN
1531 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_MAR_DT');
1532 v_mar_dt := FND_MESSAGE.GET;
1533 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_CURR_DT');
1534 v_other_dt := FND_MESSAGE.GET;
1535
1536 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_DT_VAL_FAIL');
1537 FND_MESSAGE.SET_TOKEN ('DATE1',v_other_dt);
1538 FND_MESSAGE.SET_TOKEN ('DATE2',v_mar_dt);
1539 IGS_GE_MSG_STACK.ADD;
1540 APP_EXCEPTION.RAISE_EXCEPTION;
1541 END IF;
1542 END IF;
1543
1544 OPEN get_dob_dt_cur(p_person_id);
1545 FETCH get_dob_dt_cur INTO l_birth_dt;
1546 CLOSE get_dob_dt_cur;
1547
1548 IF l_birth_dt IS NOT NULL AND p_marital_status_date IS NOT NULL THEN
1549 IF l_birth_dt > p_marital_status_date THEN
1550 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_MAR_DT');
1551 v_mar_dt := FND_MESSAGE.GET;
1552 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_BIRTH_DT');
1553 v_other_dt := FND_MESSAGE.GET;
1554
1555 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_DT_VAL_FAIL');
1556 FND_MESSAGE.SET_TOKEN ('DATE1',v_mar_dt);
1557 FND_MESSAGE.SET_TOKEN ('DATE2',v_other_dt);
1558 IGS_GE_MSG_STACK.ADD;
1559 APP_EXCEPTION.RAISE_EXCEPTION;
1560 END IF;
1561 END IF;
1562
1563 OPEN c_pe_stat(P_PERSON_ID);
1564 FETCH c_pe_stat INTO cv_pe_stat;
1565 CLOSE c_pe_stat;
1566
1567
1568 IF (p_caller = 'RELATIONSHIP_SS' AND NVL(cv_pe_stat.marital_status,'**') <> NVL(P_MARITAL_STATUS,'**')) OR
1569 (p_caller IS NULL) THEN
1570
1571 IF cv_pe_stat.PERSON_ID IS NOT NULL THEN
1572
1573 IF (p_caller = 'RELATIONSHIP_SS') THEN
1574 l_marital_date := cv_pe_stat.marital_status_effective_date;
1575 l_ethnicity := cv_pe_stat.ethnic_origin_id;
1576 l_religion := cv_pe_stat.religion;
1577 ELSE
1578 l_marital_date := P_MARITAL_STATUS_DATE;
1579 l_ethnicity := P_ETHNICITY;
1580 l_religion := P_RELIGION_CD;
1581 END IF;
1582 igs_pe_stat_pkg.update_row(
1583 x_action => 'UPDATE',
1584 x_rowid => cv_pe_stat.row_id,
1585 x_person_id => cv_pe_stat.person_id,
1586 x_ethnic_origin_id => l_ethnicity,
1587 x_marital_status => P_MARITAL_STATUS,
1588 x_marital_stat_effect_dt => l_marital_date,
1589 x_ann_family_income => cv_pe_stat.ann_family_income,
1590 x_number_in_family => cv_pe_stat.number_in_family,
1591 x_content_source_type => cv_pe_stat.content_source_type,
1592 x_internal_flag => cv_pe_stat.internal_flag,
1593 x_person_number => cv_pe_stat.person_number,
1594 x_effective_start_date => cv_pe_stat.effective_start_date,
1595 x_effective_end_date => cv_pe_stat.effective_end_date,
1596 x_ethnic_origin => NULL,
1597 x_religion => l_religion,
1598 x_next_to_kin => cv_pe_stat.next_to_kin,
1599 x_next_to_kin_meaning => NULL,
1600 x_place_of_birth => cv_pe_stat.place_of_birth,
1601 x_socio_eco_status => cv_pe_stat.socio_eco_status,
1602 x_socio_eco_status_desc => NULL,
1603 x_further_education => cv_pe_stat.further_education,
1604 x_further_education_desc => NULL,
1605 x_in_state_tuition => cv_pe_stat.in_state_tuition,
1606 x_tuition_st_date => cv_pe_stat.tuition_st_date,
1607 x_tuition_end_date => cv_pe_stat.tuition_end_date,
1608 x_person_initials => cv_pe_stat.person_initials,
1609 x_primary_contact_id => cv_pe_stat.primary_contact_id,
1610 x_personal_income => cv_pe_stat.personal_income,
1611 x_head_of_household_flag => cv_pe_stat.head_of_household_flag,
1612 x_content_source_number => cv_pe_stat.content_source_number,
1613 x_hz_parties_ovn => cv_pe_stat.object_version_number,
1614 x_attribute_category => cv_pe_stat.attribute_category,
1615 x_attribute1 => cv_pe_stat.attribute1,
1616 x_attribute2 => cv_pe_stat.attribute2,
1617 x_attribute3 => cv_pe_stat.attribute3,
1618 x_attribute4 => cv_pe_stat.attribute4,
1619 x_attribute5 => cv_pe_stat.attribute5,
1620 x_attribute6 => cv_pe_stat.attribute6,
1621 x_attribute7 => cv_pe_stat.attribute7,
1622 x_attribute8 => cv_pe_stat.attribute8,
1623 x_attribute9 => cv_pe_stat.attribute9,
1624 x_attribute10 => cv_pe_stat.attribute10,
1625 x_attribute11 => cv_pe_stat.attribute11,
1626 x_attribute12 => cv_pe_stat.attribute12,
1627 x_attribute13 => cv_pe_stat.attribute13,
1628 x_attribute14 => cv_pe_stat.attribute14,
1629 x_attribute15 => cv_pe_stat.attribute15,
1630 x_attribute16 => cv_pe_stat.attribute16,
1631 x_attribute17 => cv_pe_stat.attribute17,
1632 x_attribute18 => cv_pe_stat.attribute18,
1633 x_attribute19 => cv_pe_stat.attribute19,
1634 x_attribute20 => cv_pe_stat.attribute20,
1635 x_global_attribute_category => cv_pe_stat.global_attribute_category,
1636 x_global_attribute1 => cv_pe_stat.global_attribute1,
1637 x_global_attribute2 => cv_pe_stat.global_attribute2,
1638 x_global_attribute3 => cv_pe_stat.global_attribute3,
1639 x_global_attribute4 => cv_pe_stat.global_attribute4,
1640 x_global_attribute5 => cv_pe_stat.global_attribute5,
1641 x_global_attribute6 => cv_pe_stat.global_attribute6,
1642 x_global_attribute7 => cv_pe_stat.global_attribute7,
1643 x_global_attribute8 => cv_pe_stat.global_attribute8,
1644 x_global_attribute9 => cv_pe_stat.global_attribute9,
1645 x_global_attribute10=> cv_pe_stat.global_attribute10,
1646 x_global_attribute11 => cv_pe_stat.global_attribute11,
1647 x_global_attribute12 => cv_pe_stat.global_attribute12,
1648 x_global_attribute13 => cv_pe_stat.global_attribute13,
1649 x_global_attribute14 => cv_pe_stat.global_attribute14,
1650 x_global_attribute15 => cv_pe_stat.global_attribute15,
1651 x_global_attribute16 => cv_pe_stat.global_attribute16,
1652 x_global_attribute17 => cv_pe_stat.global_attribute17,
1653 x_global_attribute18 => cv_pe_stat.global_attribute18,
1654 x_global_attribute19 => cv_pe_stat.global_attribute19,
1655 x_global_attribute20 => cv_pe_stat.global_attribute20,
1656 x_party_last_update_date => v_party_last_update_date,
1657 x_person_profile_id => lv_perosn_profile_id,
1658 x_matr_cal_type => cv_pe_stat.matr_cal_type,
1659 x_matr_sequence_number => cv_pe_stat.matr_sequence_number,
1660 x_init_cal_type => cv_pe_stat.init_cal_type,
1661 x_init_sequence_number => cv_pe_stat.init_sequence_number,
1662 x_recent_cal_type => cv_pe_stat.recent_cal_type,
1663 x_recent_sequence_number => cv_pe_stat.recent_sequence_number,
1664 x_catalog_cal_type => cv_pe_stat.catalog_cal_type,
1665 x_catalog_sequence_number => cv_pe_stat.catalog_sequence_number,
1666 z_return_status => p_return_status,
1667 z_msg_count => p_msg_count,
1668 z_msg_data => p_msg_data,
1669 x_birth_cntry_resn_code => cv_pe_stat.birth_cntry_resn_code --- prbhardw
1670 );
1671
1672 END IF;
1673 END IF;
1674
1675 IF (p_caller IS NULL) THEN
1676 IF p_return_status = 'S' THEN
1677 OPEN pehz_cur(p_person_id);
1678 FETCH pehz_cur INTO tlinfo2;
1679
1680 -- PRBHARDW replaced update_row with add_row as part of BUG 5248350
1681
1682 IGS_PE_HZ_PARTIES_PKG.ADD_ROW(
1683 X_ROWID => tlinfo2.ROWID,
1684 X_PARTY_ID => P_PERSON_ID,
1685 X_DECEASED_IND => tlinfo2.deceased_ind,
1686 X_ARCHIVE_EXCLUSION_IND => tlinfo2.archive_exclusion_ind,
1687 X_ARCHIVE_DT => tlinfo2.archive_dt,
1688 X_PURGE_EXCLUSION_IND => tlinfo2.purge_exclusion_ind,
1689 X_PURGE_DT => tlinfo2.purge_dt,
1690 X_ORACLE_USERNAME => tlinfo2.oracle_username,
1691 X_PROOF_OF_INS => tlinfo2.proof_of_ins,
1692 X_PROOF_OF_IMMU => tlinfo2.proof_of_immu,
1693 X_LEVEL_OF_QUAL => tlinfo2.level_of_qual,
1694 X_MILITARY_SERVICE_REG => tlinfo2.military_service_reg,
1695 X_VETERAN => P_VETERAN,
1696 X_INSTITUTION_CD => tlinfo2.INSTITUTION_CD,
1697 X_OI_LOCAL_INSTITUTION_IND => tlinfo2.OI_LOCAL_INSTITUTION_IND,
1698 X_OI_OS_IND => tlinfo2.OI_OS_IND,
1699 X_OI_GOVT_INSTITUTION_CD => tlinfo2.OI_GOVT_INSTITUTION_CD,
1700 X_OI_INST_CONTROL_TYPE => tlinfo2.OI_INST_CONTROL_TYPE,
1701 X_OI_INSTITUTION_TYPE => tlinfo2.OI_INSTITUTION_TYPE,
1702 X_OI_INSTITUTION_STATUS => tlinfo2.OI_INSTITUTION_STATUS,
1703 X_OU_START_DT => tlinfo2.OU_START_DT,
1704 X_OU_END_DT => tlinfo2.OU_END_DT,
1705 X_OU_MEMBER_TYPE => tlinfo2.OU_MEMBER_TYPE,
1706 X_OU_ORG_STATUS => tlinfo2.OU_ORG_STATUS,
1707 X_OU_ORG_TYPE => tlinfo2.OU_ORG_TYPE,
1708 X_INST_ORG_IND => tlinfo2.INST_ORG_IND,
1709 X_FUND_AUTHORIZATION => tlinfo2.FUND_AUTHORIZATION,
1710 X_PE_INFO_VERIFY_TIME => tlinfo2.PE_INFO_VERIFY_TIME,
1711 X_birth_city => p_birth_city,
1712 X_birth_country => p_birth_country,
1713 X_MODE => 'R'
1714 );
1715
1716 CLOSE pehz_cur;
1717 END IF;
1718 end if;
1719 -- End of API body.
1720
1721 -- Standard call to get message count and if count is 1, get message info.
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 ROLLBACK TO Update_Biographic;
1726 p_return_status := FND_API.G_RET_STS_ERROR;
1727 p_msg_data := SQLERRM;
1728 END Update_Biographic;
1729
1730 PROCEDURE CREATEUPDATE_PERS_ALTID (
1731 P_ACTION IN VARCHAR2,
1732 P_PE_PERSON_ID IN NUMBER,
1733 P_API_PERSON_ID IN VARCHAR2,
1734 P_PERSON_ID_TYPE IN VARCHAR2,
1735 P_START_DT IN DATE,
1736 P_END_DT IN DATE,
1737 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
1738 P_ATTRIBUTE1 IN VARCHAR2,
1739 P_ATTRIBUTE2 IN VARCHAR2,
1740 P_ATTRIBUTE3 IN VARCHAR2,
1741 P_ATTRIBUTE4 IN VARCHAR2,
1742 P_ATTRIBUTE5 IN VARCHAR2,
1743 P_ATTRIBUTE6 IN VARCHAR2,
1744 P_ATTRIBUTE7 IN VARCHAR2,
1745 P_ATTRIBUTE8 IN VARCHAR2,
1746 P_ATTRIBUTE9 IN VARCHAR2,
1747 P_ATTRIBUTE10 IN VARCHAR2,
1748 P_ATTRIBUTE11 IN VARCHAR2,
1749 P_ATTRIBUTE12 IN VARCHAR2,
1750 P_ATTRIBUTE13 IN VARCHAR2,
1751 P_ATTRIBUTE14 IN VARCHAR2,
1752 P_ATTRIBUTE15 IN VARCHAR2,
1753 P_ATTRIBUTE16 IN VARCHAR2,
1754 P_ATTRIBUTE17 IN VARCHAR2,
1755 P_ATTRIBUTE18 IN VARCHAR2,
1756 P_ATTRIBUTE19 IN VARCHAR2,
1757 P_ATTRIBUTE20 IN VARCHAR2,
1758 P_REGION_CD IN VARCHAR2,
1759 P_RETURN_STATUS OUT NOCOPY VARCHAR2,
1760 P_MSG_COUNT OUT NOCOPY NUMBER,
1761 P_MSG_DATA OUT NOCOPY VARCHAR2
1762 ) IS
1763 l_message_name VARCHAR2(30);
1764 l_app VARCHAR2(50);
1765 CURSOR c_row_id(cp_pe_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
1766 cp_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE,
1767 cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE,
1768 cp_start_dt igs_pe_alt_pers_id.start_dt%TYPE) IS
1769 SELECT rowid, api_person_id_uf
1770 FROM igs_pe_alt_pers_id
1771 WHERE pe_person_id = cp_pe_person_id AND
1772 api_person_id = cp_api_person_id AND
1773 person_id_type = cp_person_id_type AND
1774 start_dt = cp_start_dt;
1775
1776 CURSOR c_ins_row_id(cp_pe_person_id igs_pe_alt_pers_id.pe_person_id%TYPE,
1777 cp_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE,
1778 cp_person_id_type igs_pe_alt_pers_id.person_id_type%TYPE,
1779 cp_start_dt igs_pe_alt_pers_id.start_dt%TYPE) IS
1780 SELECT rowid
1781 FROM IGS_PE_ALT_PERS_ID
1782 where PE_PERSON_ID = cp_pe_person_id
1783 and API_PERSON_ID = cp_api_person_id
1784 and PERSON_ID_TYPE = cp_person_id_type
1785 and start_dt = cp_start_dt
1786 and start_dt = end_dt;
1787
1788 l_rowid VARCHAR2(25) := NULL;
1789 l_Api_Person_Id_uf igs_pe_alt_pers_id.api_person_id_uf%TYPE;
1790 l_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE;
1791
1792 CURSOR c_alt_id_desc(cp_alt_id_type in VARCHAR) IS
1793 SELECT description
1794 FROM igs_pe_person_id_typ
1795 WHERE person_id_type= cp_alt_id_type;
1796
1797 l_alt_id_desc igs_pe_person_id_typ.description%TYPE;
1798
1799 BEGIN
1800
1801 -- Initialize message list if p_init_msg_list is set to TRUE.
1802 P_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1803 FND_MSG_PUB.initialize;
1804
1805 l_api_person_id := UPPER(p_api_person_id);
1806
1807 IF P_ACTION = 'INSERT' THEN
1808 /*
1809 Check whether there are any record with the same values entered and are logically deleted (start date = end date).
1810 If there is any record then insted of creating a new duplicate record, delete the existing record and create
1811 the new record.
1812 */
1813
1814 OPEN c_ins_row_id(p_pe_person_id, l_api_person_id, p_person_id_type, p_start_dt);
1815 FETCH c_ins_row_id INTO l_rowid;
1816 CLOSE c_ins_row_id;
1817
1818 IF l_rowid IS NOT NULL THEN
1819 igs_pe_alt_pers_id_pkg.delete_row(l_rowid,'R');
1820 END IF;
1821
1822 l_rowid := NULL;
1823
1824 igs_pe_alt_pers_id_pkg.insert_row(
1825 X_ROWID => l_rowid,
1826 X_PE_PERSON_ID => P_PE_PERSON_ID,
1827 X_API_PERSON_ID => l_api_person_id,
1828 X_API_PERSON_ID_UF => NULL,
1829 X_PERSON_ID_TYPE => P_PERSON_ID_TYPE,
1830 X_START_DT => P_START_DT,
1831 X_END_DT => P_END_DT,
1832 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1833 X_ATTRIBUTE1 => P_ATTRIBUTE1,
1834 X_ATTRIBUTE2 => P_ATTRIBUTE2,
1835 X_ATTRIBUTE3 => P_ATTRIBUTE3,
1836 X_ATTRIBUTE4 => P_ATTRIBUTE4,
1837 X_ATTRIBUTE5 => P_ATTRIBUTE5,
1838 X_ATTRIBUTE6 => P_ATTRIBUTE6,
1839 X_ATTRIBUTE7 => P_ATTRIBUTE7,
1840 X_ATTRIBUTE8 => P_ATTRIBUTE8,
1841 X_ATTRIBUTE9 => P_ATTRIBUTE9,
1842 X_ATTRIBUTE10 => P_ATTRIBUTE10,
1843 X_ATTRIBUTE11 => P_ATTRIBUTE11,
1844 X_ATTRIBUTE12 => P_ATTRIBUTE12,
1845 X_ATTRIBUTE13 => P_ATTRIBUTE13,
1846 X_ATTRIBUTE14 => P_ATTRIBUTE14,
1847 X_ATTRIBUTE15 => P_ATTRIBUTE15,
1848 X_ATTRIBUTE16 => P_ATTRIBUTE16,
1849 X_ATTRIBUTE17 => P_ATTRIBUTE17,
1850 X_ATTRIBUTE18 => P_ATTRIBUTE18,
1851 X_ATTRIBUTE19 => P_ATTRIBUTE19,
1852 X_ATTRIBUTE20 => P_ATTRIBUTE20,
1853 X_REGION_CD => P_REGION_CD,
1854 X_MODE => 'R'
1855 );
1856
1857 ELSIF P_ACTION='UPDATE' THEN
1858 OPEN c_row_id(p_pe_person_id, l_api_person_id, p_person_id_type, p_start_dt);
1859 FETCH c_row_id INTO l_rowid, l_Api_Person_Id_uf;
1860 CLOSE c_row_id;
1861
1862 IGS_PE_ALT_PERS_ID_PKG.UPDATE_ROW(
1863 X_ROWID => l_rowid,
1864 X_PE_PERSON_ID => P_PE_PERSON_ID,
1865 X_API_PERSON_ID => l_api_person_id,
1866 X_API_PERSON_ID_UF => NULL,
1867 X_PERSON_ID_TYPE => P_PERSON_ID_TYPE,
1868 X_START_DT => P_START_DT,
1869 X_END_DT => P_END_DT,
1870 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1871 X_ATTRIBUTE1 => P_ATTRIBUTE1,
1872 X_ATTRIBUTE2 => P_ATTRIBUTE2,
1873 X_ATTRIBUTE3 => P_ATTRIBUTE3,
1874 X_ATTRIBUTE4 => P_ATTRIBUTE4,
1875 X_ATTRIBUTE5 => P_ATTRIBUTE5,
1876 X_ATTRIBUTE6 => P_ATTRIBUTE6,
1877 X_ATTRIBUTE7 => P_ATTRIBUTE7,
1878 X_ATTRIBUTE8 => P_ATTRIBUTE8,
1879 X_ATTRIBUTE9 => P_ATTRIBUTE9,
1880 X_ATTRIBUTE10 => P_ATTRIBUTE10,
1881 X_ATTRIBUTE11 => P_ATTRIBUTE11,
1882 X_ATTRIBUTE12 => P_ATTRIBUTE12,
1883 X_ATTRIBUTE13 => P_ATTRIBUTE13,
1884 X_ATTRIBUTE14 => P_ATTRIBUTE14,
1885 X_ATTRIBUTE15 => P_ATTRIBUTE15,
1886 X_ATTRIBUTE16 => P_ATTRIBUTE16,
1887 X_ATTRIBUTE17 => P_ATTRIBUTE17,
1888 X_ATTRIBUTE18 => P_ATTRIBUTE18,
1889 X_ATTRIBUTE19 => P_ATTRIBUTE19,
1890 X_ATTRIBUTE20 => P_ATTRIBUTE20,
1891 X_REGION_CD => P_REGION_CD,
1892 X_MODE => 'R'
1893 );
1894
1895 ELSIF P_ACTION='LOCK' THEN
1896 OPEN c_row_id(p_pe_person_id, l_api_person_id, p_person_id_type, p_start_dt);
1897 FETCH c_row_id INTO l_rowid, l_Api_Person_Id_uf;
1898 CLOSE c_row_id;
1899
1900 IGS_PE_ALT_PERS_ID_Pkg.Lock_Row (
1901 X_RowId => l_rowid,
1902 X_Pe_Person_Id => p_pe_person_id,
1903 X_Api_Person_Id => l_api_person_id,
1904 X_Api_Person_Id_uf => l_Api_Person_Id_uf,
1905 X_Person_Id_Type => p_person_id_type,
1906 X_Start_Dt => p_start_dt,
1907 X_End_Dt => p_end_dt,
1908 X_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
1909 X_ATTRIBUTE1 => P_ATTRIBUTE1,
1910 X_ATTRIBUTE2 => P_ATTRIBUTE2,
1911 X_ATTRIBUTE3 => P_ATTRIBUTE3,
1912 X_ATTRIBUTE4 => P_ATTRIBUTE4,
1913 X_ATTRIBUTE5 => P_ATTRIBUTE5,
1914 X_ATTRIBUTE6 => P_ATTRIBUTE6,
1915 X_ATTRIBUTE7 => P_ATTRIBUTE7,
1916 X_ATTRIBUTE8 => P_ATTRIBUTE8,
1917 X_ATTRIBUTE9 => P_ATTRIBUTE9,
1918 X_ATTRIBUTE10 => P_ATTRIBUTE10,
1919 X_ATTRIBUTE11 => P_ATTRIBUTE11,
1920 X_ATTRIBUTE12 => P_ATTRIBUTE12,
1921 X_ATTRIBUTE13 => P_ATTRIBUTE13,
1922 X_ATTRIBUTE14 => P_ATTRIBUTE14,
1923 X_ATTRIBUTE15 => P_ATTRIBUTE15,
1924 X_ATTRIBUTE16 => P_ATTRIBUTE16,
1925 X_ATTRIBUTE17 => P_ATTRIBUTE17,
1926 X_ATTRIBUTE18 => P_ATTRIBUTE18,
1927 X_ATTRIBUTE19 => P_ATTRIBUTE19,
1928 X_ATTRIBUTE20 => P_ATTRIBUTE20,
1929 X_REGION_CD => P_REGION_CD
1930 );
1931
1932 END IF;
1933
1934 EXCEPTION
1935 WHEN OTHERS THEN
1936 -- To find the message name raised from the TBH
1937 FND_MESSAGE.PARSE_ENCODED(FND_MESSAGE.GET_ENCODED, l_app, l_message_name);
1938
1939 IF l_message_name = 'IGS_PE_UNIQUE_PID' THEN
1940 OPEN c_alt_id_desc(P_PERSON_ID_TYPE);
1941 FETCH c_alt_id_desc INTO l_alt_id_desc;
1942 CLOSE c_alt_id_desc;
1943
1944 FND_MSG_PUB.initialize ;
1945 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_UNIQUE_PID_SS');
1946 FND_MESSAGE.SET_TOKEN('ALT_ID_DESC1', l_alt_id_desc);
1947 FND_MESSAGE.SET_TOKEN('ALT_ID_DESC2', l_alt_id_desc);
1948 IGS_GE_MSG_STACK.ADD;
1949 END IF;
1950 p_return_status := FND_API.G_RET_STS_ERROR;
1951 p_msg_data := SQLERRM;
1952
1953 END createupdate_pers_altid;
1954
1955 PROCEDURE UPDATE_TEST_RESULT_DETAILS (
1956 P_TEST_SEGMENT_ID IN NUMBER,
1957 P_TEST_RESULT_ID IN NUMBER,
1958 P_TEST_SCORE IN NUMBER,
1959 P_RETURN_STATUS OUT NOCOPY VARCHAR2,
1960 P_MSG_COUNT OUT NOCOPY NUMBER,
1961 P_MSG_DATA OUT NOCOPY VARCHAR2
1962 )
1963 IS
1964 l_rowid ROWID;
1965 l_tst_rslt_dtls_id igs_ad_tst_rslt_dtls.tst_rslt_dtls_id%TYPE;
1966 l_action VARCHAR2(30);
1967
1968 CURSOR record_info_cur (cp_test_results_id igs_ad_tst_rslt_dtls.test_results_id%TYPE,
1969 cp_test_segment_id igs_ad_tst_rslt_dtls.test_segment_id%TYPE)
1970 IS
1971 SELECT rowid, rslt_dtl.*
1972 FROM igs_ad_tst_rslt_dtls rslt_dtl
1973 WHERE rslt_dtl.test_results_id = cp_test_results_id AND
1974 rslt_dtl.test_segment_id = cp_test_segment_id;
1975
1976 CURSOR parent_cur(cp_test_results_id igs_ad_tst_rslt_dtls.test_results_id%TYPE)
1977 IS
1978 SELECT test_date
1979 FROM igs_ad_test_results
1980 WHERE test_results_id = cp_test_results_id;
1981
1982 record_info_rec record_info_cur%ROWTYPE;
1983 l_test_date DATE;
1984 BEGIN
1985 P_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
1986 FND_MSG_PUB.initialize;
1987
1988 /*
1989 IF for the P_TEST_RESULT_ID and P_TEST_SEGMENT_ID combination there are no records,
1990 then if Test score is entered, INSERT should happen.
1991 If there is already a record and
1992 Test Score is passed NULL, then DELETE should happen.
1993 Test score is passed NOT NULL and its NOT equal to the database value then UPDATE should happen.
1994 */
1995
1996 OPEN record_info_cur(P_TEST_RESULT_ID, P_TEST_SEGMENT_ID);
1997 FETCH record_info_cur INTO record_info_rec;
1998 IF record_info_cur%NOTFOUND THEN
1999 IF P_TEST_SCORE IS NOT NULL THEN
2000 l_action := 'INSERT';
2001 END IF;
2002 ELSE
2003 IF P_TEST_SCORE IS NULL THEN
2004 l_action := 'DELETE';
2005 ELSE
2006 IF record_info_rec.test_score IS NULL OR (P_TEST_SCORE <> record_info_rec.test_score) THEN
2007 l_action := 'UPDATE';
2008 END IF;
2009 END IF;
2010
2011 END IF;
2012 CLOSE record_info_cur;
2013 /*
2014 igs_pe_elearning_pkg.debug('P_TEST_SEGMENT_ID :'||P_TEST_SEGMENT_ID);
2015 igs_pe_elearning_pkg.debug('P_TEST_RESULT_ID :'||P_TEST_RESULT_ID);
2016 igs_pe_elearning_pkg.debug('P_TEST_SCORE :'||P_TEST_SCORE);
2017 igs_pe_elearning_pkg.debug('l_action :'||l_action);
2018 */
2019 IF l_action = 'INSERT' THEN
2020 OPEN parent_cur(p_test_result_id);
2021 FETCH parent_cur INTO l_test_date;
2022 CLOSE parent_cur;
2023
2024 IF l_test_date > TRUNC(SYSDATE) THEN
2025 Fnd_Message.Set_Name ('IGS', 'IGS_SS_AD_SEG_NOT_IN_FUTURE');
2026 IGS_GE_MSG_STACK.ADD;
2027 App_Exception.Raise_Exception;
2028 END IF;
2029
2030 igs_ad_tst_rslt_dtls_pkg.insert_row(
2031 X_ROWID => l_rowid,
2032 X_TST_RSLT_DTLS_ID => l_tst_rslt_dtls_id,
2033 X_TEST_RESULTS_ID => P_TEST_RESULT_ID,
2034 X_TEST_SEGMENT_ID => P_TEST_SEGMENT_ID,
2035 X_TEST_SCORE => P_TEST_SCORE,
2036 X_PERCENTILE => NULL,
2037 X_NATIONAL_PERCENTILE => NULL,
2038 X_STATE_PERCENTILE => NULL,
2039 X_PERCENTILE_YEAR_RANK => NULL,
2040 X_SCORE_BAND_LOWER => NULL,
2041 X_SCORE_BAND_UPPER => NULL,
2042 X_IRREGULARITY_CODE_ID => NULL,
2043 X_ATTRIBUTE_CATEGORY => NULL,
2044 X_ATTRIBUTE1 => NULL,
2045 X_ATTRIBUTE2 => NULL,
2046 X_ATTRIBUTE3 => NULL,
2047 X_ATTRIBUTE4 => NULL,
2048 X_ATTRIBUTE5 => NULL,
2049 X_ATTRIBUTE6 => NULL,
2050 X_ATTRIBUTE7 => NULL,
2051 X_ATTRIBUTE8 => NULL,
2052 X_ATTRIBUTE9 => NULL,
2053 X_ATTRIBUTE10 => NULL,
2054 X_ATTRIBUTE11 => NULL,
2055 X_ATTRIBUTE12 => NULL,
2056 X_ATTRIBUTE13 => NULL,
2057 X_ATTRIBUTE14 => NULL,
2058 X_ATTRIBUTE15 => NULL,
2059 X_ATTRIBUTE16 => NULL,
2060 X_ATTRIBUTE17 => NULL,
2061 X_ATTRIBUTE18 => NULL,
2062 X_ATTRIBUTE19 => NULL,
2063 X_ATTRIBUTE20 => NULL
2064 );
2065
2066 ELSIF l_action = 'UPDATE' THEN
2067
2068 igs_ad_tst_rslt_dtls_pkg.update_row(
2069 X_ROWID => record_info_rec.ROWID,
2070 X_TST_RSLT_DTLS_ID => record_info_rec.TST_RSLT_DTLS_ID,
2071 X_TEST_RESULTS_ID => record_info_rec.TEST_RESULTS_ID,
2072 X_TEST_SEGMENT_ID => record_info_rec.TEST_SEGMENT_ID,
2073 X_TEST_SCORE => P_TEST_SCORE,
2074 X_PERCENTILE => record_info_rec.PERCENTILE,
2075 X_NATIONAL_PERCENTILE => record_info_rec.NATIONAL_PERCENTILE,
2076 X_STATE_PERCENTILE => record_info_rec.STATE_PERCENTILE,
2077 X_PERCENTILE_YEAR_RANK => record_info_rec.PERCENTILE_YEAR_RANK,
2078 X_SCORE_BAND_LOWER => record_info_rec.SCORE_BAND_LOWER,
2079 X_SCORE_BAND_UPPER => record_info_rec.SCORE_BAND_UPPER,
2080 X_IRREGULARITY_CODE_ID => record_info_rec.IRREGULARITY_CODE_ID,
2081 X_ATTRIBUTE_CATEGORY => record_info_rec.ATTRIBUTE_CATEGORY,
2082 X_ATTRIBUTE1 => record_info_rec.ATTRIBUTE1,
2083 X_ATTRIBUTE2 => record_info_rec.ATTRIBUTE2,
2084 X_ATTRIBUTE3 => record_info_rec.ATTRIBUTE3,
2085 X_ATTRIBUTE4 => record_info_rec.ATTRIBUTE4,
2086 X_ATTRIBUTE5 => record_info_rec.ATTRIBUTE5,
2087 X_ATTRIBUTE6 => record_info_rec.ATTRIBUTE6,
2088 X_ATTRIBUTE7 => record_info_rec.ATTRIBUTE7,
2089 X_ATTRIBUTE8 => record_info_rec.ATTRIBUTE8,
2090 X_ATTRIBUTE9 => record_info_rec.ATTRIBUTE9,
2091 X_ATTRIBUTE10 => record_info_rec.ATTRIBUTE10,
2092 X_ATTRIBUTE11 => record_info_rec.ATTRIBUTE11,
2093 X_ATTRIBUTE12 => record_info_rec.ATTRIBUTE12,
2094 X_ATTRIBUTE13 => record_info_rec.ATTRIBUTE13,
2095 X_ATTRIBUTE14 => record_info_rec.ATTRIBUTE14,
2096 X_ATTRIBUTE15 => record_info_rec.ATTRIBUTE15,
2097 X_ATTRIBUTE16 => record_info_rec.ATTRIBUTE16,
2098 X_ATTRIBUTE17 => record_info_rec.ATTRIBUTE17,
2099 X_ATTRIBUTE18 => record_info_rec.ATTRIBUTE18,
2100 X_ATTRIBUTE19 => record_info_rec.ATTRIBUTE19,
2101 X_ATTRIBUTE20 => record_info_rec.ATTRIBUTE20
2102 );
2103
2104 ELSIF l_action = 'DELETE' THEN
2105
2106 igs_ad_tst_rslt_dtls_pkg.delete_row(
2107 x_rowid => record_info_rec.ROWID
2108 );
2109 END IF;
2110
2111 EXCEPTION
2112 WHEN OTHERS THEN
2113 p_return_status := FND_API.G_RET_STS_ERROR;
2114 p_msg_data := SQLERRM;
2115 END UPDATE_TEST_RESULT_DETAILS;
2116
2117 FUNCTION CHECK_DUPLICATE_LOC(
2118 p_country IN VARCHAR2,
2119 p_addr_line_1 IN VARCHAR2 ,
2120 p_addr_line_2 IN VARCHAR2 ,
2121 p_addr_line_3 IN VARCHAR2 ,
2122 p_addr_line_4 IN VARCHAR2 ,
2123 p_city IN VARCHAR2 ,
2124 p_state IN VARCHAR2 ,
2125 p_province IN VARCHAR2 ,
2126 p_county IN VARCHAR2 ,
2127 p_postal_code IN VARCHAR2 ,
2128 p_object_id IN NUMBER
2129 ) RETURN BOOLEAN IS
2130
2131 CURSOR c_dup_loc(cp_address VARCHAR2) IS
2132 SELECT 1
2133 FROM hz_locations hl, hz_party_sites hps
2134 WHERE hl.COUNTRY = p_country AND
2135 UPPER(hl.address1||'-'||hl.address2||'-'||hl.address3||'-'||hl.address4||'-'||hl.city||'-'||hl.state||'-'||
2136 hl.province||'-'||hl.county||'-'||hl.postal_code) = cp_address AND
2137 hl.location_id = hps.location_id and
2138 hps.party_id = p_object_id;
2139
2140 loc_count NUMBER;
2141 l_concat_address VARCHAR2(4000);
2142 BEGIN
2143 l_concat_address := UPPER(p_addr_line_1||'-'||p_addr_line_2||'-'||p_addr_line_3||'-'||p_addr_line_4||'-'||
2144 p_city||'-'||p_state||'-'||p_province||'-'||p_county||'-'||p_postal_code);
2145
2146 OPEN c_dup_loc(l_concat_address);
2147 FETCH c_dup_loc INTO loc_count;
2148 CLOSE c_dup_loc;
2149
2150 IF loc_count = 1 THEN
2151 RETURN TRUE;
2152 ELSE
2153 RETURN FALSE;
2154 END IF;
2155 END check_duplicate_loc;
2156
2157 PROCEDURE CREATEUPDATE_RELATIONSHIP (
2158 P_MODE IN VARCHAR2,
2159 P_RETURN_STATUS OUT NOCOPY VARCHAR2,
2160 P_MSG_COUNT OUT NOCOPY NUMBER,
2161 P_MSG_DATA OUT NOCOPY VARCHAR2,
2162 P_RELATIONSHIP_ID IN OUT NOCOPY NUMBER,
2163 P_DIRECTIONAL_FLAG IN VARCHAR2,
2164 P_SUBJECT_ID IN NUMBER,
2165 P_OBJECT_ID IN OUT NOCOPY NUMBER,
2166 P_FIRST_NAME IN VARCHAR2,
2167 P_LAST_NAME IN VARCHAR2,
2168 P_MIDDLE_NAME IN VARCHAR2,
2169 P_PREFERRED_NAME IN VARCHAR2,
2170 P_BIRTHDATE IN DATE,
2171 P_PRE_NAME_ADJUNCT IN VARCHAR2,
2172 P_SUFFIX IN VARCHAR2,
2173 P_TITLE IN VARCHAR2,
2174 P_HZ_PARTIES_OVN IN OUT NOCOPY NUMBER,
2175 P_HZ_REL_OVN IN OUT NOCOPY NUMBER,
2176 P_JOINT_MAILING IN VARCHAR2,
2177 P_NEXT_OF_KIN IN VARCHAR2,
2178 P_EMERGENCY_CONTACT IN VARCHAR2,
2179 P_DECEASED IN VARCHAR2,
2180 P_GENDER IN VARCHAR2,
2181 P_MARITAL_STATUS IN VARCHAR2,
2182 P_REP_FACULTY IN VARCHAR2,
2183 P_REP_STAFF IN VARCHAR2,
2184 P_REP_STUDENT IN VARCHAR2,
2185 P_REP_ALUMNI IN VARCHAR2,
2186 P_REL_START_DATE IN DATE,
2187 P_REL_END_DATE IN DATE,
2188 P_REL_CODE IN VARCHAR2,
2189 P_COPY_PRIMARY_ADDR IN VARCHAR2
2190 ) IS
2191 CURSOR c_existing_rel(cp_first_name VARCHAR2, cp_last_name VARCHAR2,cp_subject_id NUMBER,cp_rel_code VARCHAR2)IS
2192 SELECT hr.object_id, hr.relationship_id
2193 FROM hz_parties hp, hz_relationships hr
2194 WHERE UPPER(hp.person_first_name) = UPPER(cp_first_name)
2195 AND UPPER(hp.person_last_name) = UPPER(cp_last_name)
2196 AND hp.party_id = hr.object_id
2197 AND hr.subject_id = cp_subject_id
2198 AND hr.relationship_code = cp_rel_code
2199 AND SYSDATE NOT BETWEEN hr.start_date AND NVL(hr.end_date,SYSDATE);
2200
2201 CURSOR c_person (cp_object_id NUMBER)IS
2202 SELECT
2203 p.rowid row_id,
2204 p.party_id person_id,
2205 p.party_number person_number,
2206 p.party_name person_name,
2207 NULL staff_member_ind,
2208 p.person_last_name surname,
2209 p.person_first_name given_names,
2210 p.person_middle_name middle_name,
2211 p.person_name_suffix suffix,
2212 p.person_pre_name_adjunct pre_name_adjunct,
2213 p.person_title title,
2214 p.email_address email_addr,
2215 p.salutation,
2216 p.known_as preferred_given_name,
2217 pd.proof_of_ins,
2218 pd.proof_of_immu,
2219 pd.level_of_qual level_of_qual_id,
2220 pd.military_service_reg,
2221 pd.veteran,
2222 DECODE(pp.date_of_death,NULL,NVL(pd.deceased_ind,'N'),'Y') deceased_ind,
2223 pp.gender sex,
2224 pp.date_of_death deceased_date,
2225 pp.date_of_birth birth_dt,
2226 pd.archive_exclusion_ind,
2227 pd.archive_dt,
2228 pd.purge_exclusion_ind,
2229 pd.purge_dt,
2230 pd.fund_authorization,
2231 p.attribute_category,
2232 p.attribute1,
2233 p.attribute2,
2234 p.attribute3,
2235 p.attribute4,
2236 p.attribute5,
2237 p.attribute6,
2238 p.attribute7,
2239 p.attribute8,
2240 p.attribute9,
2241 p.attribute10,
2242 p.attribute11,
2243 p.attribute12,
2244 p.attribute13,
2245 p.attribute14,
2246 p.attribute15,
2247 p.attribute16,
2248 p.attribute17,
2249 p.attribute18,
2250 p.attribute19,
2251 p.attribute20,
2252 p.attribute21,
2253 p.attribute22,
2254 p.attribute23,
2255 p.attribute24,
2256 pd.oracle_username ,
2257 pd.birth_city,
2258 pd.birth_country,
2259 p.object_version_number,
2260 p.status,
2261 pd.felony_convicted_flag,
2262 p.last_update_date
2263 FROM
2264 hz_parties p,
2265 igs_pe_hz_parties pd,
2266 hz_person_profiles pp
2267 WHERE p.party_id = cp_object_id
2268 AND p.party_id = pd.party_id (+)
2269 AND p.party_id = pp.party_id
2270 AND SYSDATE BETWEEN pp.effective_start_date AND NVL(pp.effective_end_date,SYSDATE);
2271
2272 CURSOR c_relationship(cp_relationship_id NUMBER, cp_directional_flag VARCHAR2) IS
2273 SELECT
2274 relationship_id ,
2275 subject_id ,
2276 subject_type ,
2277 subject_table_name ,
2278 object_id ,
2279 object_type ,
2280 object_table_name ,
2281 party_id ,
2282 relationship_code ,
2283 directional_flag ,
2284 comments ,
2285 start_date ,
2286 end_date ,
2287 status ,
2288 created_by ,
2289 creation_date ,
2290 last_updated_by ,
2291 last_update_date ,
2292 last_update_login ,
2293 content_source_type ,
2294 relationship_type ,
2295 object_version_number ,
2296 direction_code ,
2297 percentage_ownership ,
2298 actual_content_source
2299 FROM hz_relationships
2300 WHERE relationship_id = cp_relationship_id
2301 AND directional_flag = cp_directional_flag;
2302
2303 CURSOR c_rel_code(cp_rel_code VARCHAR2) IS
2304 SELECT relationship_type
2305 FROM hz_relationship_types
2306 WHERE forward_rel_code = cp_rel_code;
2307
2308 CURSOR c_primary(cp_subject_id NUMBER) IS
2309 SELECT 'X' ,prel.LAST_UPDATE_DATE
2310 FROM hz_relationships hrel,igs_pe_hz_rel prel
2311 WHERE hrel.relationship_id = prel.relationship_id
2312 AND hrel.directional_flag = prel.directional_flag
2313 AND hrel.subject_id = cp_subject_id
2314 AND prel.primary = 'Y';
2315
2316 CURSOR c_secondary(cp_subject_id NUMBER) IS
2317 SELECT 'X' ,prel.LAST_UPDATE_DATE
2318 FROM hz_relationships hrel,igs_pe_hz_rel prel
2319 WHERE hrel.relationship_id = prel.relationship_id
2320 AND hrel.directional_flag = prel.directional_flag
2321 AND hrel.subject_id = cp_subject_id
2322 AND prel.secondary = 'Y';
2323
2324 CURSOR c_location(cp_subject_id NUMBER) IS
2325 SELECT
2326 ihps.start_date start_dt,
2327 hps.party_site_id,
2328 ihps.end_date end_dt,
2329 hl.rowid,
2330 hl.location_id,
2331 hl.country country_cd,
2332 hl.address_style,
2333 hl.address1 addr_line_1,
2334 hl.address2 addr_line_2,
2335 hl.address3 addr_line_3,
2336 hl.address4 addr_line_4,
2337 hps.identifying_address_flag correspondence,
2338 hl.city,
2339 hl.state,
2340 hl.province,
2341 hl.county,
2342 hl.postal_code,
2343 hl.address_lines_phonetic,
2344 hl.delivery_point_code,
2345 hps.status
2346 FROM
2347 hz_locations hl,
2348 hz_party_sites hps,
2349 igs_pe_hz_pty_sites ihps
2350 WHERE hl.location_id = hps.location_id
2351 AND hps.party_id = cp_subject_id
2352 AND hps.identifying_address_flag = 'Y'
2353 AND hps.party_site_id = ihps.party_site_id(+);
2354
2355 CURSOR c_site_detail (cp_party_site_id NUMBER) IS
2356 SELECT site_use_type
2357 FROM hz_party_site_uses hps
2358 WHERE hps.party_site_id = cp_party_site_id;
2359
2360 CURSOR c_subject_party_name (cp_subject_id NUMBER) IS
2361 SELECT party_name
2362 FROM hz_parties
2363 WHERE party_id = cp_subject_id;
2364
2365 rec_site_detail c_site_detail%ROWTYPE;
2366 rec_relationship c_relationship%ROWTYPE;
2367
2368 l_object_id NUMBER := -1;
2369 l_prim_flag VARCHAR2(1):= NULL;
2370 l_sec_flag VARCHAR2(1) := NULL;
2371 l_prim_last_updt_dt DATE;
2372 l_sec_last_updt_dt DATE;
2373 l_return_status VARCHAR2(1);
2374 l_msg_count NUMBER;
2375 l_msg_data VARCHAR(2000);
2376 l_row_id VARCHAR2(255);
2377 l_person_id NUMBER;
2378 l_person_number VARCHAR2(100);
2379 l_person c_person%ROWTYPE;
2380 l_rel_end_dt DATE;
2381 l_rel_type VARCHAR2(100);
2382 l_party_rel_id NUMBER;
2383 l_party_id NUMBER;
2384 l_party_number VARCHAR2(100);
2385 l_primary VARCHAR2(1) := 'N';
2386 l_secondary VARCHAR2(1) := 'N';
2387 l_last_update DATE := TRUNC(SYSDATE);
2388 l_location_id NUMBER;
2389 l_loc_rowid VARCHAR2(25);
2390 rec_student_addr c_location%ROWTYPE;
2391 l_party_site_ovn hz_party_sites.object_version_number%TYPE;
2392 l_location_ovn hz_locations.object_version_number%TYPE;
2393 l_hz_rel_ovn hz_relationships.object_version_number%TYPE;
2394 l_party_site_id NUMBER;
2395 l_last_update_date DATE;
2396 l_party_site_use_rowid VARCHAR2(25);
2397 l_party_site_use_id NUMBER;
2398 l_site_use_id NUMBER;
2399 l_site_last_update_date DATE;
2400 l_profile_last_update_date DATE;
2401 l_object_version_number NUMBER;
2402 l_loc_exists BOOLEAN;
2403 L_SUB_PARTY_NAME hz_parties.party_name%TYPE;
2404
2405 err_msg_data varchar2(200);
2406
2407 CURSOR IS_HR_PERSON(CP_PARTY_ID NUMBER) IS
2408 SELECT 'X'
2409 FROM PER_ALL_PEOPLE_F
2410 WHERE PARTY_ID = CP_PARTY_ID;
2411 L_FOUND VARCHAR2(1);
2412
2413 l_relationship_id NUMBER;
2414 BEGIN
2415 FND_MSG_PUB.initialize;
2416 SAVEPOINT CreateUpdate_Relationship;
2417 P_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
2418
2419 OPEN c_existing_rel(p_first_name,p_last_name,p_subject_id,p_rel_code);
2420 FETCH c_existing_rel INTO l_object_id, l_relationship_id;
2421 CLOSE c_existing_rel;
2422
2423 IF l_object_id IS NULL THEN
2424 l_object_id := -1;
2425 END IF;
2426
2427
2428 IF P_MODE = 'INSERT' AND l_object_id = -1 THEN
2429
2430 OPEN c_rel_code(p_rel_code);
2431 FETCH c_rel_code INTO l_rel_type;
2432 CLOSE c_rel_code;
2433
2434 IGS_PE_PERSON_PKG.INSERT_ROW (
2435 X_MSG_COUNT => P_MSG_COUNT,
2436 X_MSG_DATA => p_msg_data,
2437 X_RETURN_STATUS => l_return_status,
2438 X_ROWID => l_row_id,
2439 X_PERSON_ID => P_OBJECT_ID,
2440 X_PERSON_NUMBER => l_person_number,
2441 X_SURNAME => P_LAST_NAME,
2442 X_MIDDLE_NAME => P_MIDDLE_NAME,
2443 X_GIVEN_NAMES => P_FIRST_NAME,
2444 X_SEX => P_GENDER,
2445 X_TITLE => P_TITLE,
2446 X_STAFF_MEMBER_IND => 'N',
2447 X_DECEASED_IND => P_DECEASED,
2448 X_SUFFIX => P_SUFFIX,
2449 X_PRE_NAME_ADJUNCT => P_PRE_NAME_ADJUNCT,
2450 X_ARCHIVE_EXCLUSION_IND => NULL,
2451 X_ARCHIVE_DT => NULL,
2452 X_PURGE_EXCLUSION_IND => NULL,
2453 X_PURGE_DT => NULL,
2454 X_DECEASED_DATE => NULL,
2455 X_PROOF_OF_INS => NULL,
2456 X_PROOF_OF_IMMU => NULL,
2457 X_BIRTH_DT => P_BIRTHDATE,
2458 X_SALUTATION => P_JOINT_MAILING,
2459 X_ORACLE_USERNAME => NULL,
2460 X_PREFERRED_GIVEN_NAME => P_PREFERRED_NAME,
2461 X_EMAIL_ADDR => NULL,
2462 X_LEVEL_OF_QUAL_ID => NULL,
2463 X_MILITARY_SERVICE_REG => NULL,
2464 X_VETERAN => NULL,
2465 X_HZ_PARTIES_OVN => P_HZ_PARTIES_OVN,
2466 X_ATTRIBUTE_CATEGORY => NULL,
2467 X_ATTRIBUTE1 => NULL,
2468 X_ATTRIBUTE2 => NULL,
2469 X_ATTRIBUTE3 => NULL,
2470 X_ATTRIBUTE4 => NULL,
2471 X_ATTRIBUTE5 => NULL,
2472 X_ATTRIBUTE6 => NULL,
2473 X_ATTRIBUTE7 => NULL,
2474 X_ATTRIBUTE8 => NULL,
2475 X_ATTRIBUTE9 => NULL,
2476 X_ATTRIBUTE10 => NULL,
2477 X_ATTRIBUTE11 => NULL,
2478 X_ATTRIBUTE12 => NULL,
2479 X_ATTRIBUTE13 => NULL,
2480 X_ATTRIBUTE14 => NULL,
2481 X_ATTRIBUTE15 => NULL,
2482 X_ATTRIBUTE16 => NULL,
2483 X_ATTRIBUTE17 => NULL,
2484 X_ATTRIBUTE18 => NULL,
2485 X_ATTRIBUTE19 => NULL,
2486 X_ATTRIBUTE20 => NULL,
2487 X_PERSON_ID_TYPE => NULL,
2488 X_API_PERSON_ID => NULL,
2489 X_STATUS => 'A',
2490 X_ATTRIBUTE21 => NULL,
2491 X_ATTRIBUTE22 => NULL,
2492 X_ATTRIBUTE23 => NULL,
2493 X_ATTRIBUTE24 => NULL
2494 );
2495
2496 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2497 RAISE FND_API.G_EXC_ERROR;
2498 END IF;
2499
2500 UPDATE_BIOGRAPHIC(
2501 P_PERSON_ID => P_OBJECT_ID,
2502 P_ETHNICITY => NULL,
2503 P_MARITAL_STATUS => P_MARITAL_STATUS,
2504 P_MARITAL_STATUS_DATE => NULL,
2505 P_BIRTH_CITY => NULL,
2506 P_BIRTH_COUNTRY => NULL,
2507 P_VETERAN => NULL,
2508 P_RELIGION_CD => NULL,
2509 P_HZ_OVN => P_HZ_PARTIES_OVN,
2510 P_RETURN_STATUS => l_return_status,
2511 P_MSG_COUNT => P_MSG_COUNT,
2512 P_MSG_DATA => l_msg_data,
2513 P_CALLER => 'RELATIONSHIP_SS'
2514 );
2515 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2516 RAISE FND_API.G_EXC_ERROR;
2517 END IF;
2518
2519 IF P_JOINT_MAILING = 'Y' THEN
2520 l_primary := 'N';
2521 l_secondary := 'N';
2522 OPEN c_primary(p_subject_id);
2523 FETCH c_primary INTO l_prim_flag,l_prim_last_updt_dt;
2524 CLOSE c_primary;
2525 IF l_prim_flag IS NULL THEN
2526 l_primary := 'Y';
2527 ELSE
2528 OPEN c_secondary(p_subject_id);
2529 FETCH c_secondary INTO l_sec_flag,l_sec_last_updt_dt;
2530 CLOSE c_secondary;
2531
2532 IF l_sec_flag IS NULL THEN
2533 l_secondary := 'Y';
2534 ELSE -- Primary,Secondary Relationships exists
2535 IF l_prim_last_updt_dt >= l_sec_last_updt_dt THEN
2536 l_secondary := 'Y';
2537 ELSE
2538 l_primary := 'Y';
2539 END IF;
2540 END IF;
2541 END IF;
2542 END IF;
2543
2544 IF P_COPY_PRIMARY_ADDR = 'Y' THEN
2545 OPEN c_location(p_subject_id);
2546 FETCH c_location INTO rec_student_addr;
2547 IF c_location%FOUND THEN
2548 CLOSE c_location;
2549 IGS_PE_PERSON_ADDR_PKG.INSERT_ROW(
2550 p_action =>'INSERT',
2551 p_rowid => l_loc_rowid,
2552 p_location_id => l_location_id,
2553 p_start_dt => rec_student_addr.START_DT,
2554 p_end_dt => rec_student_addr.END_DT,
2555 p_country => rec_student_addr.COUNTRY_CD,
2556 p_address_style => rec_student_addr.ADDRESS_STYLE,
2557 p_addr_line_1 => rec_student_addr.ADDR_LINE_1,
2558 p_addr_line_2 => rec_student_addr.ADDR_LINE_2,
2559 p_addr_line_3 => rec_student_addr.ADDR_LINE_3,
2560 p_addr_line_4 => rec_student_addr.ADDR_LINE_4,
2561 p_date_last_verified => NULL,
2562 p_correspondence => rec_student_addr.CORRESPONDENCE,
2563 p_city => rec_student_addr.CITY,
2564 p_state => rec_student_addr.STATE,
2565 p_province => rec_student_addr.PROVINCE,
2566 p_county => rec_student_addr.COUNTY,
2567 p_postal_code => rec_student_addr.POSTAL_CODE,
2568 p_address_lines_phonetic => rec_student_addr.address_lines_phonetic,
2569 p_delivery_point_code => rec_student_addr.delivery_point_code,
2570 p_other_details_1 => NULL,
2571 p_other_details_2 => NULL,
2572 p_other_details_3 => NULL,
2573 l_return_status => l_return_status ,
2574 l_msg_data => p_msg_data,
2575 p_party_id => p_object_id,
2576 p_party_site_id => l_party_site_id,
2577 p_party_type => 'PERSON',
2578 p_last_update_date => l_last_update_date ,
2579 p_party_site_ovn => l_party_site_ovn,
2580 p_location_ovn => l_location_ovn,
2581 p_status => rec_student_addr.status
2582 );
2583 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2584 RAISE FND_API.G_EXC_ERROR;
2585 END IF;
2586
2587 FOR rec_site_detail IN c_site_detail(rec_student_addr.party_site_id)
2588 LOOP
2589 l_party_site_use_id := NULL;
2590 IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK(
2591 p_action => 'INSERT',
2592 p_rowid => l_party_site_use_rowid,
2593 p_party_site_use_id => l_party_site_use_id,
2594 p_party_site_id => l_party_site_id,
2595 p_site_use_type => rec_site_detail.site_use_type,
2596 p_return_status => l_return_status,
2597 p_msg_data => p_msg_data,
2598 p_last_update_date => l_last_update_date,
2599 p_site_use_last_update_date => l_site_last_update_date,
2600 p_profile_last_update_date => l_profile_last_update_date,
2601 p_status => 'A',
2602 P_HZ_PARTY_SITE_USE_OVN => l_object_version_number
2603 );
2604 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2605 RAISE FND_API.G_EXC_ERROR;
2606 END IF;
2607 END LOOP;
2608 ELSE
2609 CLOSE c_location;
2610 -- THROW a error saying that there is no primary address defined
2611 OPEN c_subject_party_name(p_subject_id);
2612 FETCH c_subject_party_name INTO l_sub_party_name;
2613 CLOSE c_subject_party_name;
2614
2615 FND_MESSAGE.SET_NAME('IGS','IGS_PE_SS_NO_PRIM_ADDR');
2616 FND_MESSAGE.SET_TOKEN('SUBJECT_NAME',l_sub_party_name);
2617 IGS_GE_MSG_STACK.ADD;
2618 App_Exception.Raise_Exception;
2619 END IF;
2620 END IF; --P_COPY_PRIMARY_ADDR = 'Y'
2621
2622 IGS_PE_RELATIONSHIPS_PKG.CREATUPDATE_PARTY_RELATIONSHIP(
2623 P_ACTION => 'INSERT',
2624 P_SUBJECT_ID => P_SUBJECT_ID,
2625 P_OBJECT_ID => P_OBJECT_ID,
2626 P_PARTY_RELATIONSHIP_TYPE => l_rel_type,
2627 P_RELATIONSHIP_CODE => P_REL_CODE,
2628 P_COMMENTS => NULL,
2629 P_START_DATE => NVL(P_REL_START_DATE,TRUNC(SYSDATE)),
2630 P_END_DATE => P_REL_END_DATE,
2631 P_LAST_UPDATE_DATE => l_last_update,
2632 P_RETURN_STATUS => l_return_status,
2633 P_MSG_COUNT => p_msg_count,
2634 P_MSG_DATA => p_msg_data,
2635 P_PARTY_RELATIONSHIP_ID => P_RELATIONSHIP_ID,
2636 P_PARTY_ID => l_party_id,
2637 P_PARTY_NUMBER => l_party_number,
2638 P_CALLER => 'NOT_FAMILY',
2639 P_OBJECT_VERSION_NUMBER => P_HZ_REL_OVN,
2640 P_PRIMARY => l_primary,
2641 P_SECONDARY => l_secondary,
2642 P_JOINT_SALUTATION => P_JOINT_MAILING,
2643 P_NEXT_TO_KIN => P_NEXT_OF_KIN,
2644 P_REP_FACULTY => P_REP_FACULTY,
2645 P_REP_STAFF => P_REP_STAFF,
2646 P_REP_STUDENT => P_REP_STUDENT,
2647 P_REP_ALUMNI => P_REP_ALUMNI,
2648 P_DIRECTIONAL_FLAG => P_DIRECTIONAL_FLAG,
2649 P_EMERGENCY_CONTACT_FLAG => P_EMERGENCY_CONTACT
2650 );
2651 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2652 RAISE FND_API.G_EXC_ERROR;
2653 END IF;
2654
2655 ELSIF (P_MODE = 'UPDATE') OR (P_MODE = 'INSERT' AND l_object_id <> -1) THEN
2656 --update mode called.
2657
2658 IF P_MODE = 'UPDATE' THEN
2659 l_rel_end_dt := P_REL_END_DATE;
2660 l_relationship_id := P_RELATIONSHIP_ID;
2661 END IF;
2662
2663 IF P_MODE = 'INSERT' THEN
2664 l_rel_end_dt := TO_DATE('4712/12/31','YYYY/MM/DD');
2665 p_object_id := l_object_id;
2666 END IF;
2667
2668 OPEN c_person(p_object_id);
2669 FETCH c_person INTO l_person;
2670 CLOSE c_person;
2671
2672 p_hz_parties_ovn := l_person.object_version_number;
2673
2674 IF (P_MODE = 'INSERT') THEN
2675 OPEN IS_HR_PERSON(p_object_id);
2676 FETCH IS_HR_PERSON INTO L_FOUND;
2677 CLOSE IS_HR_PERSON;
2678 END IF;
2679 IF L_FOUND IS NULL THEN
2680 IGS_PE_PERSON_PKG.UPDATE_ROW (
2681 X_LAST_UPDATE_DATE => l_person.LAST_UPDATE_DATE,
2682 X_MSG_COUNT => P_MSG_COUNT,
2683 X_MSG_DATA => p_msg_data,
2684 X_RETURN_STATUS => l_return_status,
2685 X_ROWID => l_person.row_id,
2686 X_PERSON_ID => p_object_id,
2687 X_PERSON_NUMBER => l_person.person_number,
2688 X_SURNAME => P_LAST_NAME,
2689 X_MIDDLE_NAME => l_person.middle_name,
2690 X_GIVEN_NAMES => P_FIRST_NAME,
2691 X_SEX => P_GENDER,
2692 X_TITLE => P_TITLE,
2693 X_STAFF_MEMBER_IND => l_person.STAFF_MEMBER_IND,
2694 X_DECEASED_IND => P_DECEASED,
2695 X_SUFFIX => P_SUFFIX,
2696 X_PRE_NAME_ADJUNCT => P_PRE_NAME_ADJUNCT,
2697 X_ARCHIVE_EXCLUSION_IND => l_person.ARCHIVE_EXCLUSION_IND,
2698 X_ARCHIVE_DT => l_person.ARCHIVE_DT,
2699 X_PURGE_EXCLUSION_IND => l_person.PURGE_EXCLUSION_IND,
2700 X_PURGE_DT => l_person.PURGE_DT,
2701 X_DECEASED_DATE => l_person.DECEASED_DATE,
2702 X_PROOF_OF_INS => l_person.PROOF_OF_INS,
2703 X_PROOF_OF_IMMU => l_person.PROOF_OF_IMMU,
2704 X_BIRTH_DT => P_BIRTHDATE,
2705 X_SALUTATION => l_person.salutation,
2706 X_ORACLE_USERNAME => l_person.ORACLE_USERNAME,
2707 X_PREFERRED_GIVEN_NAME => P_PREFERRED_NAME,
2708 X_EMAIL_ADDR => l_person.EMAIL_ADDR,
2709 X_LEVEL_OF_QUAL_ID => l_person.LEVEL_OF_QUAL_ID,
2710 X_MILITARY_SERVICE_REG => l_person.MILITARY_SERVICE_REG,
2711 X_VETERAN => l_person.VETERAN,
2712 X_HZ_PARTIES_OVN => P_HZ_PARTIES_OVN,
2713 X_ATTRIBUTE_CATEGORY => l_person.ATTRIBUTE_CATEGORY,
2714 X_ATTRIBUTE1 => l_person.ATTRIBUTE1,
2715 X_ATTRIBUTE2 => l_person.ATTRIBUTE2,
2716 X_ATTRIBUTE3 => l_person.ATTRIBUTE3,
2717 X_ATTRIBUTE4 => l_person.ATTRIBUTE4,
2718 X_ATTRIBUTE5 => l_person.ATTRIBUTE5,
2719 X_ATTRIBUTE6 => l_person.ATTRIBUTE6,
2720 X_ATTRIBUTE7 => l_person.ATTRIBUTE7,
2721 X_ATTRIBUTE8 => l_person.ATTRIBUTE8,
2722 X_ATTRIBUTE9 => l_person.ATTRIBUTE9,
2723 X_ATTRIBUTE10 => l_person.ATTRIBUTE10,
2724 X_ATTRIBUTE11 => l_person.ATTRIBUTE11,
2725 X_ATTRIBUTE12 => l_person.ATTRIBUTE12,
2726 X_ATTRIBUTE13 => l_person.ATTRIBUTE13,
2727 X_ATTRIBUTE14 => l_person.ATTRIBUTE14,
2728 X_ATTRIBUTE15 => l_person.ATTRIBUTE15,
2729 X_ATTRIBUTE16 => l_person.ATTRIBUTE16,
2730 X_ATTRIBUTE17 => l_person.ATTRIBUTE17,
2731 X_ATTRIBUTE18 => l_person.ATTRIBUTE18,
2732 X_ATTRIBUTE19 => l_person.ATTRIBUTE19,
2733 X_ATTRIBUTE20 => l_person.ATTRIBUTE20,
2734 X_PERSON_ID_TYPE => NULL,
2735 X_API_PERSON_ID => NULL,
2736 X_STATUS => l_person.STATUS,
2737 X_ATTRIBUTE21 => l_person.ATTRIBUTE21,
2738 X_ATTRIBUTE22 => l_person.ATTRIBUTE22,
2739 X_ATTRIBUTE23 => l_person.ATTRIBUTE23,
2740 X_ATTRIBUTE24 => l_person.ATTRIBUTE24
2741 );
2742 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2743 RAISE FND_API.G_EXC_ERROR;
2744 END IF;
2745
2746 UPDATE_BIOGRAPHIC(
2747 P_PERSON_ID => P_OBJECT_ID,
2748 P_ETHNICITY => NULL,
2749 P_MARITAL_STATUS => P_MARITAL_STATUS,
2750 P_MARITAL_STATUS_DATE => NULL,
2751 P_BIRTH_CITY => NULL,
2752 P_BIRTH_COUNTRY => NULL,
2753 P_VETERAN => NULL,
2754 P_RELIGION_CD => NULL,
2755 P_HZ_OVN => P_HZ_PARTIES_OVN,
2756 P_RETURN_STATUS => l_return_status,
2757 P_MSG_COUNT => P_MSG_COUNT,
2758 P_MSG_DATA => p_msg_data,
2759 P_CALLER => 'RELATIONSHIP_SS'
2760 );
2761 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2762 RAISE FND_API.G_EXC_ERROR;
2763 END IF;
2764 ELSE
2765 err_msg_data :='IGS_PE_HR_PERS_RELATION';
2766 END IF;
2767
2768 OPEN c_relationship(l_relationship_id,p_directional_flag);
2769 FETCH c_relationship INTO rec_relationship;
2770 CLOSE c_relationship;
2771
2772 l_last_update := TRUNC(SYSDATE);
2773 l_hz_rel_ovn := rec_relationship.OBJECT_VERSION_NUMBER;
2774
2775 IF P_JOINT_MAILING = 'Y' THEN
2776 OPEN c_primary(p_subject_id);
2777 FETCH c_primary INTO l_prim_flag,l_prim_last_updt_dt;
2778 CLOSE c_primary;
2779 IF l_prim_flag IS NULL THEN -- Primary Relationship does not exists
2780 l_primary := 'Y';
2781 ELSE -- Primary Relationship exists
2782 OPEN c_secondary(p_subject_id);
2783 FETCH c_secondary INTO l_sec_flag,l_sec_last_updt_dt;
2784 CLOSE c_secondary;
2785 IF l_sec_flag IS NULL THEN -- Primary Relationship does not exists
2786 l_secondary := 'Y';
2787 ELSE -- Primary,Secondary Relationships exists
2788 IF l_prim_last_updt_dt >= l_sec_last_updt_dt THEN
2789 l_secondary := 'Y';
2790 ELSE
2791 l_primary := 'Y';
2792 END IF;
2793 END IF;
2794 END IF;
2795 END IF;
2796
2797 IF P_MODE = 'INSERT' THEN
2798 P_RELATIONSHIP_ID := rec_relationship.RELATIONSHIP_ID;
2799 P_HZ_REL_OVN := l_hz_rel_ovn;
2800 END IF;
2801 IGS_PE_RELATIONSHIPS_PKG.CREATUPDATE_PARTY_RELATIONSHIP(
2802 P_ACTION => 'UPDATE',
2803 P_SUBJECT_ID => P_SUBJECT_ID,
2804 P_OBJECT_ID => p_object_id,
2805 P_PARTY_RELATIONSHIP_TYPE => rec_relationship.RELATIONSHIP_TYPE,
2806 P_RELATIONSHIP_CODE => rec_relationship.RELATIONSHIP_CODE,
2807 P_COMMENTS => rec_relationship.COMMENTS,
2808 P_START_DATE => rec_relationship.START_DATE,
2809 P_END_DATE => l_rel_end_dt,
2810 P_LAST_UPDATE_DATE => l_last_update,
2811 P_RETURN_STATUS => l_return_status,
2812 P_MSG_COUNT => p_msg_count,
2813 P_MSG_DATA => p_msg_data,
2814 P_PARTY_RELATIONSHIP_ID => P_RELATIONSHIP_ID,
2815 P_PARTY_ID => rec_relationship.PARTY_ID,
2816 P_PARTY_NUMBER => l_party_number,
2817 P_CALLER => 'NOT_FAMILY',
2818 P_OBJECT_VERSION_NUMBER => l_hz_rel_ovn,
2819 P_PRIMARY => l_primary,
2820 P_SECONDARY => l_secondary,
2821 P_JOINT_SALUTATION => P_JOINT_MAILING,
2822 P_NEXT_TO_KIN => P_NEXT_OF_KIN,
2823 P_REP_FACULTY => P_REP_FACULTY,
2824 P_REP_STAFF => P_REP_STAFF,
2825 P_REP_STUDENT => P_REP_STUDENT,
2826 P_REP_ALUMNI => P_REP_ALUMNI,
2827 P_DIRECTIONAL_FLAG => P_DIRECTIONAL_FLAG,
2828 P_EMERGENCY_CONTACT_FLAG => P_EMERGENCY_CONTACT
2829 );
2830
2831 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2832 RAISE FND_API.G_EXC_ERROR;
2833 END IF;
2834
2835 IF P_COPY_PRIMARY_ADDR = 'Y' THEN
2836
2837 OPEN c_location(p_subject_id);
2838 FETCH c_location INTO rec_student_addr;
2839 IF C_LOCATION%NOTFOUND THEN
2840 CLOSE c_location;
2841 -- THROW a error saying that there is no primary address defined
2842 OPEN c_subject_party_name(p_subject_id);
2843 FETCH c_subject_party_name INTO l_sub_party_name;
2844 CLOSE c_subject_party_name;
2845
2846 FND_MESSAGE.SET_NAME('IGS','IGS_PE_SS_NO_PRIM_ADDR');
2847 FND_MESSAGE.SET_TOKEN('SUBJECT_NAME',l_sub_party_name);
2848 IGS_GE_MSG_STACK.ADD;
2849 App_Exception.Raise_Exception;
2850 ELSE
2851 CLOSE c_location;
2852 l_loc_exists := CHECK_DUPLICATE_LOC(
2853 p_country => rec_student_addr.COUNTRY_CD,
2854 p_addr_line_1 => rec_student_addr.ADDR_LINE_1,
2855 p_addr_line_2 => rec_student_addr.ADDR_LINE_2,
2856 p_addr_line_3 => rec_student_addr.ADDR_LINE_3,
2857 p_addr_line_4 => rec_student_addr.ADDR_LINE_4,
2858 p_city => rec_student_addr.CITY,
2859 p_state => rec_student_addr.STATE,
2860 p_province => rec_student_addr.PROVINCE,
2861 p_county => rec_student_addr.COUNTY,
2862 p_postal_code => rec_student_addr.POSTAL_CODE,
2863 p_object_id => p_object_id
2864 );
2865
2866 IF (NOT l_loc_exists) THEN
2867 IGS_PE_PERSON_ADDR_PKG.INSERT_ROW(
2868 p_action =>'INSERT',
2869 p_rowid => l_row_id,
2870 p_location_id => l_location_id,
2871 p_start_dt => rec_student_addr.START_DT,
2872 p_end_dt => rec_student_addr.END_DT,
2873 p_country => rec_student_addr.COUNTRY_CD,
2874 p_address_style => rec_student_addr.ADDRESS_STYLE,
2875 p_addr_line_1 => rec_student_addr.ADDR_LINE_1,
2876 p_addr_line_2 => rec_student_addr.ADDR_LINE_2,
2877 p_addr_line_3 => rec_student_addr.ADDR_LINE_3,
2878 p_addr_line_4 => rec_student_addr.ADDR_LINE_4,
2879 p_date_last_verified => NULL,
2880 p_correspondence => rec_student_addr.CORRESPONDENCE,
2881 p_city => rec_student_addr.CITY,
2882 p_state => rec_student_addr.STATE,
2883 p_province => rec_student_addr.PROVINCE,
2884 p_county => rec_student_addr.COUNTY,
2885 p_postal_code => rec_student_addr.POSTAL_CODE,
2886 p_address_lines_phonetic => rec_student_addr.address_lines_phonetic,
2887 p_delivery_point_code => rec_student_addr.delivery_point_code,
2888 p_other_details_1 => NULL,
2889 p_other_details_2 => NULL,
2890 p_other_details_3 => NULL,
2891 l_return_status => l_return_status ,
2892 l_msg_data => p_msg_data,
2893 p_party_id => p_object_id,
2894 p_party_site_id => l_party_site_id,
2895 p_party_type => 'PERSON',
2896 p_last_update_date => l_last_update_date ,
2897 p_party_site_ovn => l_party_site_ovn,
2898 p_location_ovn => l_location_ovn,
2899 p_status => rec_student_addr.status
2900 );
2901 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2902 RAISE FND_API.G_EXC_ERROR;
2903 END IF;
2904
2905 FOR rec_site_detail IN c_site_detail(rec_student_addr.party_site_id)
2906 LOOP
2907 l_party_site_use_id := NULL;
2908 IGS_PE_PARTY_SITE_USE_PKG.HZ_PARTY_SITE_USES_AK(
2909 p_action => 'INSERT',
2910 p_rowid => l_party_site_use_rowid,
2911 p_party_site_use_id => l_party_site_use_id,
2912 p_party_site_id => l_party_site_id,
2913 p_site_use_type => rec_site_detail.site_use_type,
2914 p_return_status => l_return_status,
2915 p_msg_data => p_msg_data,
2916 p_last_update_date => l_last_update_date,
2917 p_site_use_last_update_date => l_site_last_update_date,
2918 p_profile_last_update_date => l_profile_last_update_date,
2919 p_status => 'A',
2920 P_HZ_PARTY_SITE_USE_OVN => l_object_version_number
2921 );
2922 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2923 RAISE FND_API.G_EXC_ERROR;
2924 END IF;
2925 END LOOP;
2926 END IF;
2927 END IF;
2928 END IF; --P_COPY_PRIMARY_ADDR = 'Y'
2929 --Delete Functionality Starts
2930 ELSIF P_MODE = 'DELETE' THEN
2931 OPEN c_relationship(p_relationship_id,p_directional_flag);
2932 FETCH c_relationship INTO rec_relationship;
2933 CLOSE c_relationship;
2934 l_last_update := TRUNC(SYSDATE);
2935 l_hz_rel_ovn := rec_relationship.OBJECT_VERSION_NUMBER;
2936
2937 IGS_PE_RELATIONSHIPS_PKG.CREATUPDATE_PARTY_RELATIONSHIP(
2938 P_ACTION => 'UPDATE',
2939 P_SUBJECT_ID => P_SUBJECT_ID,
2940 P_OBJECT_ID => P_OBJECT_ID,
2941 P_PARTY_RELATIONSHIP_TYPE => rec_relationship.RELATIONSHIP_TYPE,
2942 P_RELATIONSHIP_CODE => rec_relationship.RELATIONSHIP_CODE,
2943 P_COMMENTS => rec_relationship.COMMENTS,
2944 P_START_DATE => rec_relationship.START_DATE,
2945 P_END_DATE => TRUNC(SYSDATE),
2946 P_LAST_UPDATE_DATE => l_last_update,
2947 P_RETURN_STATUS => l_return_status,
2948 P_MSG_COUNT => p_msg_count,
2949 P_MSG_DATA => p_msg_data,
2950 P_PARTY_RELATIONSHIP_ID => rec_relationship.RELATIONSHIP_ID,
2951 P_PARTY_ID => rec_relationship.PARTY_ID,
2952 P_PARTY_NUMBER => l_party_number,
2953 P_CALLER => 'NOT_FAMILY',
2954 P_OBJECT_VERSION_NUMBER => l_hz_rel_ovn,
2955 P_PRIMARY => 'N',
2956 P_SECONDARY => 'N',
2957 P_JOINT_SALUTATION => P_JOINT_MAILING,
2958 P_NEXT_TO_KIN => P_NEXT_OF_KIN,
2959 P_REP_FACULTY => P_REP_FACULTY,
2960 P_REP_STAFF => P_REP_STAFF,
2961 P_REP_STUDENT => P_REP_STUDENT,
2962 P_REP_ALUMNI => P_REP_ALUMNI,
2963 P_DIRECTIONAL_FLAG => P_DIRECTIONAL_FLAG
2964 );
2965 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2966 RAISE FND_API.G_EXC_ERROR;
2967 END IF;
2968 -- Delete functionality ends
2969 END IF; --(P_MODE = 'UPDATE') or (P_MODE = 'INSERT' and l_object_id <> -1)
2970
2971 p_msg_data := err_msg_data;
2972 EXCEPTION
2973 WHEN OTHERS THEN
2974 ROLLBACK TO CreateUpdate_Relationship;
2975 p_return_status := FND_API.G_RET_STS_ERROR;
2976 p_msg_data := SQLERRM;
2977 END createupdate_relationship;
2978
2979 END IGS_PE_PERSON_SS_PKG;