[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_HZ_REL_PKG
Source
1 PACKAGE BODY igs_pe_hz_rel_pkg AS
2 /* $Header: IGSNIB1B.pls 120.2 2005/07/08 01:28:18 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_hz_rel%ROWTYPE;
6 new_references igs_pe_hz_rel%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_relationship_id IN NUMBER,
12 x_directional_flag IN VARCHAR2,
13 x_primary IN VARCHAR2,
14 x_secondary IN VARCHAR2,
15 x_joint_salutation IN VARCHAR2,
16 x_next_to_kin IN VARCHAR2,
17 x_rep_faculty IN VARCHAR2,
18 x_rep_staff IN VARCHAR2,
19 x_rep_student IN VARCHAR2,
20 x_rep_alumni IN VARCHAR2,
21 x_emergency_contact_flag IN VARCHAR2,
22 x_creation_date IN DATE,
23 x_created_by IN NUMBER,
24 x_last_update_date IN DATE,
25 x_last_updated_by IN NUMBER,
26 x_last_update_login IN NUMBER
27 ) AS
28 /*
29 || Created By :
30 || Created On : 28-APR-2003
31 || Purpose : Initialises the Old and New references for the columns of the table.
32 || Known limitations, enhancements or remarks :
33 || Change History :
34 || Who When What
35 || (reverse chronological order - newest change first)
36 */
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM igs_pe_hz_rel
41 WHERE rowid = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 OPEN cur_old_ref_values;
50 FETCH cur_old_ref_values INTO old_references;
51 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
52 CLOSE cur_old_ref_values;
53 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
54 igs_ge_msg_stack.add;
55 app_exception.raise_exception;
56 RETURN;
57 END IF;
58 CLOSE cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.relationship_id := x_relationship_id;
62 new_references.directional_flag := x_directional_flag;
63 new_references.primary := x_primary;
64 new_references.secondary := x_secondary;
65 new_references.joint_salutation := x_joint_salutation;
66 new_references.next_to_kin := x_next_to_kin;
67 new_references.rep_faculty := x_rep_faculty;
68 new_references.rep_staff := x_rep_staff;
69 new_references.rep_student := x_rep_student;
70 new_references.rep_alumni := x_rep_alumni;
71 new_references.emergency_contact_flag := x_emergency_contact_flag;
72 IF (p_action = 'UPDATE') THEN
73 new_references.creation_date := old_references.creation_date;
74 new_references.created_by := old_references.created_by;
75 ELSE
76 new_references.creation_date := x_creation_date;
77 new_references.created_by := x_created_by;
78 END IF;
79
80 new_references.last_update_date := x_last_update_date;
81 new_references.last_updated_by := x_last_updated_by;
82 new_references.last_update_login := x_last_update_login;
83
84 END set_column_values;
85
86 PROCEDURE AfterRowInsertUpdate(
87 p_rowid IN ROWID,
88 p_inserting IN BOOLEAN,
89 p_updating IN BOOLEAN,
90 p_deleting IN BOOLEAN
91 ) as
92 ------------------------------------------------------------------------------------------
93 --Created by : kpadiyar
94 --Date created: 10-JAN-2003
95 --
96 --Purpose: To form the Joint Salutation.
97 --
98 --Known limitations/enhancements and/or remarks:
99 --
100 --Change History:
101 --Who When What
102 --asbala 15-JAN-2004 3349171: Incorrect usage of fnd_lookup_values view
103 ----------------------------------------------------------------------------------------------
104 --
105 -- cursor to get the surname, prefix, given_names of member
106 --
107 CURSOR check_update IS
108 SELECT d.subject_id
109 FROM igs_pe_hz_rel a , hz_relationships d
110 WHERE a.rowid = p_rowid
111 AND (NVL(a.primary,'N') = 'Y' OR NVL(a.secondary,'N') = 'Y')
112 AND d.relationship_id = a.relationship_id
113 AND a.directional_flag = d.directional_flag;
114
115
116 CURSOR c_member_detail1(p_person_id NUMBER) IS
117 SELECT a.rowid,
118 b.person_last_name surname,
119 b.person_first_name given_names,
120 b.person_pre_name_adjunct prefix
121 FROM igs_pe_hz_rel a ,hz_parties b , hz_relationships c
122 WHERE a.relationship_id = c.relationship_id
123 AND c.subject_id = p_person_id
124 AND c.object_id = b.party_id
125 AND c.directional_flag = a.directional_flag
126 AND NVL(a.primary,'N') = 'Y'
127 AND rownum = 1;
128
129 CURSOR c_member_detail2(p_person_id NUMBER) IS
130 SELECT a.rowid,
131 b.person_last_name surname,
132 b.person_first_name given_names,
133 b.person_pre_name_adjunct prefix
134 FROM igs_pe_hz_rel a ,hz_parties b , hz_relationships c
135 WHERE a.relationship_id = c.relationship_id
136 AND c.subject_id = p_person_id
137 AND c.object_id = b.party_id
138 AND c.directional_flag = a.directional_flag
139 AND NVL(a.secondary,'N') = 'Y'
140 AND rownum = 1;
141
142 CURSOR c_prefix_desc (p_prefix VARCHAR2,
143 p_lookup_type fnd_lookup_values.lookup_type%TYPE,
144 p_view_application_id fnd_lookup_values.view_application_id%TYPE,
145 p_security_group_id fnd_lookup_values.security_group_id%TYPE) IS
146 SELECT meaning
147 FROM fnd_lookup_values
148 WHERE lookup_type = p_lookup_type
149 AND view_application_id = p_view_application_id
150 AND language = USERENV('LANG')
151 AND security_group_id = p_security_group_id
152 AND lookup_code = p_prefix
153 AND enabled_flag = 'Y';
154
155 l_member_id1 hz_relationships.object_id%TYPE;
156 l_member_id2 hz_relationships.object_id%TYPE;
157 rec_prime_member_detail c_member_detail1%ROWTYPE;
158 rec_sec_member_detail c_member_detail2%ROWTYPE;
159 lv_joint_salutation VARCHAR2(750);
160 lv_update VARCHAR2(1);
161 lv_update_joint_sal VARCHAR2(1);
162 l_check_update check_update%ROWTYPE;
163 BEGIN
164 lv_update := 'N';
165 lv_update_joint_sal := 'N';
166 IF p_inserting OR p_updating THEN
167 IF p_updating THEN
168 OPEN check_update;
169 FETCH check_update INTO l_check_update;
170 IF check_update%FOUND THEN
171
172 IF new_references.primary = 'Y' THEN
173 IF ( NVL(old_references.joint_salutation,'N') <> NVL(new_references.joint_salutation,'N')) THEN
174 --
175 -- fetch secondary member details
176 --
177 OPEN c_member_detail2 (l_check_update.subject_id);
178 FETCH c_member_detail2 INTO rec_sec_member_detail;
179 IF rec_sec_member_detail.prefix IS NOT NULL THEN
180 OPEN c_prefix_desc (rec_sec_member_detail.prefix, 'CONTACT_TITLE',222,0);
181 FETCH c_prefix_desc INTO rec_sec_member_detail.prefix;
182 CLOSE c_prefix_desc;
183 END IF;
184 CLOSE c_member_detail2;
185
186 UPDATE igs_pe_hz_rel
187 SET
188 joint_salutation = new_references.joint_salutation
189 WHERE rowid =rec_sec_member_detail.rowid;
190
191
192 END IF;
193 ELSIF new_references.secondary = 'Y' THEN
194 IF ( NVL(old_references.joint_salutation,'N') <> NVL(new_references.joint_salutation,'N')) THEN
195 -- fetch primary member details
196 --
197 OPEN c_member_detail1 (l_check_update.subject_id);
198 FETCH c_member_detail1 INTO rec_prime_member_detail;
199 IF rec_prime_member_detail.prefix IS NOT NULL THEN
200 OPEN c_prefix_desc (rec_prime_member_detail.prefix, 'CONTACT_TITLE',222,0);
201 FETCH c_prefix_desc INTO rec_prime_member_detail.prefix;
202 CLOSE c_prefix_desc;
203 END IF;
204 CLOSE c_member_detail1;
205
206 UPDATE igs_pe_hz_rel
207 SET
208 joint_salutation = new_references.joint_salutation
209 WHERE rowid = rec_prime_member_detail.rowid;
210
211 END IF;
212 END IF;
213 END IF;
214 CLOSE check_update;
215 END IF;
216 IF p_updating THEN
217
218 /* Set the joint salutation to null if the member is changed from not being a primary or secondary */
219 IF ( NVL(new_references.primary,'N') = 'N' and (NVL(new_references.primary,'N') <> NVL(old_references.primary,'N')) ) OR
220 ( NVL(new_references.secondary,'N') = 'N' and (NVL(new_references.secondary,'N') <> NVL(old_references.secondary,'N')) ) THEN
221 UPDATE igs_pe_hz_rel
222 SET joint_salutation = NULL
223 WHERE rowid = p_rowid;
224
225 END IF;
226
227 OPEN check_update;
228 FETCH check_update INTO l_check_update;
229 IF check_update%FOUND THEN
230 IF
231 ( NVL(old_references.primary,'N') <> NVL(new_references.primary,'N')) OR
232 ( NVL(old_references.secondary,'N') <> NVL(new_references.secondary,'N'))
233 THEN
234
235 lv_update := 'Y';
236
237 END IF;
238 ELSE
239 lv_update := 'N';
240
241 END IF;
242 CLOSE check_update;
243 ELSE
244 OPEN check_update;
245 FETCH check_update INTO l_check_update;
246 IF check_update%FOUND THEN
247 lv_update := 'Y';
248
249 ELSE
250 lv_update := 'N';
251
252 END IF;
253 CLOSE check_update;
254 END IF;
255 IF lv_update = 'Y' THEN
256
257 -- fetch primary member details
258 --
259 OPEN c_member_detail1 (l_check_update.subject_id);
260 FETCH c_member_detail1 INTO rec_prime_member_detail;
261 IF rec_prime_member_detail.prefix IS NOT NULL THEN
262 OPEN c_prefix_desc (rec_prime_member_detail.prefix, 'CONTACT_TITLE',222,0);
263 FETCH c_prefix_desc INTO rec_prime_member_detail.prefix;
264 CLOSE c_prefix_desc;
265 END IF;
266 CLOSE c_member_detail1;
267 --
268 -- fetch secondary member details
269 --
270 OPEN c_member_detail2 (l_check_update.subject_id);
271 FETCH c_member_detail2 INTO rec_sec_member_detail;
272 IF rec_sec_member_detail.prefix IS NOT NULL THEN
273 OPEN c_prefix_desc (rec_sec_member_detail.prefix, 'CONTACT_TITLE',222,0);
274 FETCH c_prefix_desc INTO rec_sec_member_detail.prefix;
275 CLOSE c_prefix_desc;
276 END IF;
277 CLOSE c_member_detail2;
278
279 --
280 -- prepare the joint salutation
281 --
282 FND_MESSAGE.SET_NAME('IGS', 'IGS_AD_AND');
283
284 IF rec_prime_member_detail.surname = rec_sec_member_detail.surname THEN
285 IF rec_prime_member_detail.prefix IS NULL AND rec_sec_member_detail.prefix IS NOT NULL THEN
286 lv_joint_salutation := rec_sec_member_detail.prefix||' '||rec_prime_member_detail.given_names||' '||rec_prime_member_detail.surname;
287
288 ELSIF rec_prime_member_detail.prefix IS NULL AND rec_sec_member_detail.prefix IS NULL THEN
289 lv_joint_salutation := rec_prime_member_detail.given_names||' '||rec_prime_member_detail.surname;
290
291 ELSIF rec_prime_member_detail.prefix IS NOT NULL AND rec_sec_member_detail.prefix IS NULL THEN
292 lv_joint_salutation := rec_prime_member_detail.prefix||' '||rec_prime_member_detail.given_names||' '||rec_prime_member_detail.surname;
293
294 ELSE
295 lv_joint_salutation := rec_prime_member_detail.prefix||' '||FND_MESSAGE.GET||' '||rec_sec_member_detail.prefix||' '||rec_prime_member_detail.given_names||' '||rec_prime_member_detail.surname;
296 END IF;
297 ELSE
298 IF ( (rec_prime_member_detail.prefix IS NULL) AND (rec_prime_member_detail.given_names IS NULL) AND (rec_prime_member_detail.surname IS NULL) ) THEN
299 lv_joint_salutation := rec_sec_member_detail.prefix||' '||rec_sec_member_detail.given_names||' '||rec_sec_member_detail.surname;
300 ELSE
301 lv_joint_salutation := rec_prime_member_detail.prefix||' '||rec_prime_member_detail.given_names||' '||
302 rec_prime_member_detail.surname||' '||FND_MESSAGE.GET||' '||rec_sec_member_detail.prefix||' '||
303 rec_sec_member_detail.given_names||' '||rec_sec_member_detail.surname;
304 END IF;
305 END IF;
306
307 UPDATE igs_pe_hz_rel
308 SET
309 joint_salutation = trim(lv_joint_salutation)
310 WHERE rowid in (rec_prime_member_detail.rowid,rec_sec_member_detail.rowid);
311
312 END IF;
313 END IF;
314
315 END AfterRowInsertUpdate;
316
317 PROCEDURE before_row_insert_update AS
318 /*
319 || Created By :
320 || Created On : 28-APR-2003
321 || Purpose : Checks for the existance of Parent records.
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 */
327 BEGIN
328 IF new_references.primary = 'Y' AND new_references.secondary = 'Y' THEN
329 fnd_message.set_name('IGS', 'IGS_AD_NOT_BOTH_PRIM_SEC');
330 igs_ge_msg_stack.add;
331 app_exception.raise_exception;
332 END IF;
333 END before_row_insert_update;
334
335 PROCEDURE check_parent_existance AS
336 /*
337 || Created By :
338 || Created On : 28-APR-2003
339 || Purpose : Checks for the existance of Parent records.
340 || Known limitations, enhancements or remarks :
341 || Change History :
342 || Who When What
343 || (reverse chronological order - newest change first)
344 */
345 CURSOR rel_pk IS
346 SELECT 'Y' FROM HZ_RELATIONSHIPS
347 WHERE relationship_id = new_references.relationship_id AND
348 directional_flag = new_references.directional_flag;
349
350 l_var VARCHAR2(1);
351 BEGIN
352
353 IF (((old_references.relationship_id = new_references.relationship_id) AND
354 (old_references.directional_flag = new_references.directional_flag)) OR
355 ((new_references.relationship_id IS NULL) OR
356 (new_references.directional_flag IS NULL))) THEN
357 NULL;
358 ELSE
359 OPEN rel_pk;
360 FETCH rel_pk INTO l_var;
361 IF rel_pk%NOTFOUND THEN
362 CLOSE rel_pk;
363 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
364 igs_ge_msg_stack.add;
365 app_exception.raise_exception;
366 END IF;
367 CLOSE rel_pk;
368 END IF;
369
370 END check_parent_existance;
371
372
373 FUNCTION get_pk_for_validation (
374 x_relationship_id IN NUMBER,
375 x_directional_flag IN VARCHAR2
376 ) RETURN BOOLEAN AS
377 /*
378 || Created By :
379 || Created On : 28-APR-2003
380 || Purpose : Validates the Primary Key of the table.
381 || Known limitations, enhancements or remarks :
382 || Change History :
383 || Who When What
384 || (reverse chronological order - newest change first)
385 */
386 CURSOR cur_rowid IS
387 SELECT rowid
388 FROM igs_pe_hz_rel
389 WHERE relationship_id = x_relationship_id
390 AND directional_flag = x_directional_flag
391 FOR UPDATE NOWAIT;
392
393 lv_rowid cur_rowid%RowType;
394
395 BEGIN
396
397 OPEN cur_rowid;
398 FETCH cur_rowid INTO lv_rowid;
399 IF (cur_rowid%FOUND) THEN
400 CLOSE cur_rowid;
401 RETURN(TRUE);
402 ELSE
403 CLOSE cur_rowid;
404 RETURN(FALSE);
405 END IF;
406
407 END get_pk_for_validation;
408
409 PROCEDURE before_dml (
410 p_action IN VARCHAR2,
411 x_rowid IN VARCHAR2,
412 x_relationship_id IN NUMBER,
413 x_directional_flag IN VARCHAR2,
414 x_primary IN VARCHAR2,
415 x_secondary IN VARCHAR2,
416 x_joint_salutation IN VARCHAR2,
417 x_next_to_kin IN VARCHAR2,
418 x_rep_faculty IN VARCHAR2,
419 x_rep_staff IN VARCHAR2,
420 x_rep_student IN VARCHAR2,
421 x_rep_alumni IN VARCHAR2,
422 x_emergency_contact_flag IN VARCHAR2,
423 x_creation_date IN DATE,
424 x_created_by IN NUMBER,
425 x_last_update_date IN DATE,
426 x_last_updated_by IN NUMBER,
427 x_last_update_login IN NUMBER
428 ) AS
429 /*
430 || Created By :
431 || Created On : 28-APR-2003
432 || Purpose : Initialises the columns, Checks Constraints, Calls the
433 || Trigger Handlers for the table, before any DML operation.
434 || Known limitations, enhancements or remarks :
435 || Change History :
436 || Who When What
437 || (reverse chronological order - newest change first)
438 */
439 BEGIN
440
441 set_column_values (
442 p_action,
443 x_rowid,
444 x_relationship_id,
445 x_directional_flag,
446 x_primary,
447 x_secondary,
448 x_joint_salutation,
449 x_next_to_kin,
450 x_rep_faculty,
451 x_rep_staff,
452 x_rep_student,
453 x_rep_alumni,
454 x_emergency_contact_flag,
455 x_creation_date,
456 x_created_by,
457 x_last_update_date,
458 x_last_updated_by,
459 x_last_update_login
460 );
461
462 IF (p_action = 'INSERT') THEN
463 -- Call all the procedures related to Before Insert.
464 IF ( get_pk_for_validation(
465 new_references.relationship_id,
466 new_references.directional_flag
467 )
468 ) THEN
469 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
470 igs_ge_msg_stack.add;
471 app_exception.raise_exception;
472 END IF;
473 check_parent_existance;
474 before_row_insert_update;
475 ELSIF (p_action = 'UPDATE') THEN
476 -- Call all the procedures related to Before Update.
477 check_parent_existance;
478 before_row_insert_update;
479 ELSIF (p_action = 'VALIDATE_INSERT') THEN
480 -- Call all the procedures related to Before Insert.
481 IF ( get_pk_for_validation (
482 new_references.relationship_id,
483 new_references.directional_flag
484 )
485 ) THEN
486 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
487 igs_ge_msg_stack.add;
488 app_exception.raise_exception;
489 END IF;
490 before_row_insert_update;
491 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
492 before_row_insert_update;
493 END IF;
494
495 END before_dml;
496
497 PROCEDURE after_dml(p_action IN VARCHAR2,
498 x_rowid IN VARCHAR2
499 ) AS
500 /*
501 || Created By :
502 || Created On : 28-APR-2003
503 || Purpose : Initialises the columns, Checks Constraints, Calls the
504 || Trigger Handlers for the table, before any DML operation.
505 || Known limitations, enhancements or remarks :
506 || Change History :
507 || Who When What
508 || (reverse chronological order - newest change first)
509 */
510
511 BEGIN
512
513 l_rowid := x_rowid;
514
515 IF (p_action = 'INSERT') THEN
516 -- Call all the procedures related to After Insert.
517 AfterRowInsertUpdate(
518 p_rowid => l_rowid,
519 p_inserting => TRUE,
520 p_updating => FALSE,
521 p_deleting => FALSE
522 );
523 ELSIF (p_action = 'UPDATE') THEN
524 -- Call all the procedures related to After Update.
525 AfterRowInsertUpdate(
526 p_rowid => l_rowid,
527 p_inserting => FALSE,
528 p_updating => TRUE,
529 p_deleting => FALSE
530 );
531 END IF;
532
533 END after_dml;
534
535
536 PROCEDURE insert_row (
537 x_rowid IN OUT NOCOPY VARCHAR2,
538 x_relationship_id IN NUMBER,
539 x_directional_flag IN VARCHAR2,
540 x_primary IN VARCHAR2,
541 x_secondary IN VARCHAR2,
542 x_joint_salutation IN VARCHAR2,
543 x_next_to_kin IN VARCHAR2,
544 x_rep_faculty IN VARCHAR2,
545 x_rep_staff IN VARCHAR2,
546 x_rep_student IN VARCHAR2,
547 x_rep_alumni IN VARCHAR2,
548 x_emergency_contact_flag IN VARCHAR2,
549 x_mode IN VARCHAR2
550 ) AS
551 /*
552 || Created By :
553 || Created On : 28-APR-2003
554 || Purpose : Handles the INSERT DML logic for the table.
555 || Known limitations, enhancements or remarks :
556 || Change History :
557 || Who When What
558 || (reverse chronological order - newest change first)
559 */
560
561 x_last_update_date DATE;
562 x_last_updated_by NUMBER;
563 x_last_update_login NUMBER;
564 l_directional_flag igs_pe_hz_rel.directional_flag%TYPE;
565
566 BEGIN
567
568 x_last_update_date := SYSDATE;
569 IF (x_mode = 'I') THEN
570 x_last_updated_by := 1;
571 x_last_update_login := 0;
572 ELSIF (X_MODE IN ('R', 'S')) THEN
573 x_last_updated_by := fnd_global.user_id;
574 IF (x_last_updated_by IS NULL) THEN
575 x_last_updated_by := -1;
576 END IF;
577 x_last_update_login := fnd_global.login_id;
578 IF (x_last_update_login IS NULL) THEN
579 x_last_update_login := -1;
580 END IF;
581 ELSE
582 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
583 fnd_message.set_token ('ROUTINE', 'IGS_PE_HZ_REL_PKG.INSERT_ROW');
584 igs_ge_msg_stack.add;
585 app_exception.raise_exception;
586 END IF;
587
588 before_dml(
589 p_action => 'INSERT',
590 x_rowid => x_rowid,
591 x_relationship_id => x_relationship_id,
592 x_directional_flag => x_directional_flag,
593 x_primary => x_primary,
594 x_secondary => x_secondary,
595 x_joint_salutation => x_joint_salutation,
596 x_next_to_kin => x_next_to_kin,
597 x_rep_faculty => x_rep_faculty,
598 x_rep_staff => x_rep_staff,
599 x_rep_student => x_rep_student,
600 x_rep_alumni => x_rep_alumni,
601 x_emergency_contact_flag => x_emergency_contact_flag,
602 x_creation_date => x_last_update_date,
603 x_created_by => x_last_updated_by,
604 x_last_update_date => x_last_update_date,
605 x_last_updated_by => x_last_updated_by,
606 x_last_update_login => x_last_update_login
607 );
608
609 IF (x_mode = 'S') THEN
610 igs_sc_gen_001.set_ctx('R');
611 END IF;
612 INSERT INTO igs_pe_hz_rel (
613 relationship_id,
614 directional_flag,
615 primary,
616 secondary,
617 joint_salutation,
618 next_to_kin,
619 rep_faculty,
620 rep_staff,
621 rep_student,
622 rep_alumni,
623 emergency_contact_flag,
624 creation_date,
625 created_by,
626 last_update_date,
627 last_updated_by,
628 last_update_login
629 ) VALUES (
630 new_references.relationship_id,
631 new_references.directional_flag,
632 new_references.primary,
633 new_references.secondary,
634 new_references.joint_salutation,
635 new_references.next_to_kin,
636 new_references.rep_faculty,
637 new_references.rep_staff,
638 new_references.rep_student,
639 new_references.rep_alumni,
640 new_references.emergency_contact_flag,
641 x_last_update_date,
642 x_last_updated_by,
643 x_last_update_date,
644 x_last_updated_by,
645 x_last_update_login
646 ) RETURNING ROWID INTO x_rowid;
647 IF (x_mode = 'S') THEN
648 igs_sc_gen_001.unset_ctx('R');
649 END IF;
650
651
652 IF new_references.directional_flag = 'F' THEN
653 l_directional_flag := 'B';
654 ELSIF new_references.directional_flag = 'B' THEN
655 l_directional_flag := 'F';
656 END IF;
657
658 INSERT INTO igs_pe_hz_rel (
659 relationship_id,
660 directional_flag,
661 primary,
662 secondary,
663 next_to_kin,
664 rep_faculty,
665 rep_staff,
666 rep_student,
667 rep_alumni,
668 creation_date,
669 created_by,
670 last_update_date,
671 last_updated_by,
672 last_update_login
673 ) VALUES (
674 new_references.relationship_id,
675 l_directional_flag,
676 'N',
677 'N',
678 'N',
679 'N',
680 'N',
681 'N',
682 'N',
683 x_last_update_date,
684 x_last_updated_by,
685 x_last_update_date,
686 x_last_updated_by,
687 x_last_update_login
688 );
689 IF (x_mode = 'S') THEN
690 igs_sc_gen_001.unset_ctx('R');
691 END IF;
692
693
694 after_dml(p_action => 'INSERT',x_rowid => x_rowid);
695
696
697 EXCEPTION
698 WHEN OTHERS THEN
699 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
700 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
701 fnd_message.set_token ('ERR_CD', SQLCODE);
702 igs_ge_msg_stack.add;
703 igs_sc_gen_001.unset_ctx('R');
704 app_exception.raise_exception;
705 ELSE
706 igs_sc_gen_001.unset_ctx('R');
707 RAISE;
708 END IF;
709 END insert_row;
710
711
712 PROCEDURE lock_row (
713 x_rowid IN VARCHAR2,
714 x_relationship_id IN NUMBER,
715 x_directional_flag IN VARCHAR2,
716 x_primary IN VARCHAR2,
717 x_secondary IN VARCHAR2,
718 x_joint_salutation IN VARCHAR2,
719 x_next_to_kin IN VARCHAR2,
720 x_rep_faculty IN VARCHAR2,
721 x_rep_staff IN VARCHAR2,
722 x_rep_student IN VARCHAR2,
723 x_rep_alumni IN VARCHAR2,
724 x_emergency_contact_flag IN VARCHAR2
725 ) AS
726 /*
727 || Created By :
728 || Created On : 28-APR-2003
729 || Purpose : Handles the LOCK mechanism for the table.
730 || Known limitations, enhancements or remarks :
731 || Change History :
732 || Who When What
733 || (reverse chronological order - newest change first)
734 */
735 CURSOR c1 IS
736 SELECT
737 primary,
738 secondary,
739 joint_salutation,
740 next_to_kin,
741 rep_faculty,
742 rep_staff,
743 rep_student,
744 rep_alumni,
745 emergency_contact_flag
746 FROM igs_pe_hz_rel
747 WHERE rowid = x_rowid
748 FOR UPDATE NOWAIT;
749
750 tlinfo c1%ROWTYPE;
751
752 BEGIN
753
754 OPEN c1;
755 FETCH c1 INTO tlinfo;
756 IF (c1%notfound) THEN
757 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
758 igs_ge_msg_stack.add;
759 CLOSE c1;
760 app_exception.raise_exception;
761 RETURN;
762 END IF;
763 CLOSE c1;
764
765 IF (
766 ((tlinfo.primary = x_primary) OR ((tlinfo.primary IS NULL) AND (X_primary IS NULL)))
767 AND ((tlinfo.secondary = x_secondary) OR ((tlinfo.secondary IS NULL) AND (X_secondary IS NULL)))
768 AND ((tlinfo.joint_salutation = x_joint_salutation) OR ((tlinfo.joint_salutation IS NULL) AND (X_joint_salutation IS NULL)))
769 AND ((tlinfo.next_to_kin = x_next_to_kin) OR ((tlinfo.next_to_kin IS NULL) AND (X_next_to_kin IS NULL)))
770 AND ((tlinfo.rep_faculty = x_rep_faculty) OR ((tlinfo.rep_faculty IS NULL) AND (X_rep_faculty IS NULL)))
771 AND ((tlinfo.rep_staff = x_rep_staff) OR ((tlinfo.rep_staff IS NULL) AND (X_rep_staff IS NULL)))
772 AND ((tlinfo.rep_student = x_rep_student) OR ((tlinfo.rep_student IS NULL) AND (X_rep_student IS NULL)))
773 AND ((tlinfo.rep_alumni = x_rep_alumni) OR ((tlinfo.rep_alumni IS NULL) AND (X_rep_alumni IS NULL)))
774 AND ((tlinfo.emergency_contact_flag = x_emergency_contact_flag) OR ((tlinfo.emergency_contact_flag IS NULL) AND (X_emergency_contact_flag IS NULL)))
775 ) THEN
776 NULL;
777 ELSE
778 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
779 igs_ge_msg_stack.add;
780 app_exception.raise_exception;
781 END IF;
782
783 RETURN;
784
785 END lock_row;
786
787
788 PROCEDURE update_row (
789 x_rowid IN VARCHAR2,
790 x_relationship_id IN NUMBER,
791 x_directional_flag IN VARCHAR2,
792 x_primary IN VARCHAR2,
793 x_secondary IN VARCHAR2,
794 x_joint_salutation IN VARCHAR2,
795 x_next_to_kin IN VARCHAR2,
796 x_rep_faculty IN VARCHAR2,
797 x_rep_staff IN VARCHAR2,
798 x_rep_student IN VARCHAR2,
799 x_rep_alumni IN VARCHAR2,
800 x_emergency_contact_flag IN VARCHAR2,
801 x_mode IN VARCHAR2
802 ) AS
803 /*
804 || Created By :
805 || Created On : 28-APR-2003
806 || Purpose : Handles the UPDATE DML logic for the table.
807 || Known limitations, enhancements or remarks :
808 || Change History :
809 || Who When What
810 || (reverse chronological order - newest change first)
811 */
812 x_last_update_date DATE ;
813 x_last_updated_by NUMBER;
814 x_last_update_login NUMBER;
815
816 BEGIN
817
818 x_last_update_date := SYSDATE;
819 IF (X_MODE = 'I') THEN
820 x_last_updated_by := 1;
821 x_last_update_login := 0;
822 ELSIF (X_MODE IN ('R', 'S')) THEN
823 x_last_updated_by := fnd_global.user_id;
824 IF x_last_updated_by IS NULL THEN
825 x_last_updated_by := -1;
826 END IF;
827 x_last_update_login := fnd_global.login_id;
828 IF (x_last_update_login IS NULL) THEN
829 x_last_update_login := -1;
830 END IF;
831 ELSE
832 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
833 fnd_message.set_token ('ROUTINE', 'IGS_PE_HZ_REL_PKG.UPDATE_ROW');
834 igs_ge_msg_stack.add;
835 app_exception.raise_exception;
836 END IF;
837
838 before_dml(
839 p_action => 'UPDATE',
840 x_rowid => x_rowid,
841 x_relationship_id => x_relationship_id,
842 x_directional_flag => x_directional_flag,
843 x_primary => x_primary,
844 x_secondary => x_secondary,
845 x_joint_salutation => x_joint_salutation,
846 x_next_to_kin => x_next_to_kin,
847 x_rep_faculty => x_rep_faculty,
848 x_rep_staff => x_rep_staff,
849 x_rep_student => x_rep_student,
850 x_rep_alumni => x_rep_alumni,
851 x_emergency_contact_flag => x_emergency_contact_flag,
852 x_creation_date => x_last_update_date,
853 x_created_by => x_last_updated_by,
854 x_last_update_date => x_last_update_date,
855 x_last_updated_by => x_last_updated_by,
856 x_last_update_login => x_last_update_login
857 );
858
859
860 IF (x_mode = 'S') THEN
861 igs_sc_gen_001.set_ctx('R');
862 END IF;
863 --if the passed emergency contact flag is not null then update the database value. Else don't update emergency Contact value.
864 IF new_references.emergency_contact_flag is NOT NULL THEN
865 UPDATE igs_pe_hz_rel
866 SET
867 primary = new_references.primary,
868 secondary = new_references.secondary,
869 joint_salutation = new_references.joint_salutation,
870 next_to_kin = new_references.next_to_kin,
871 rep_faculty = new_references.rep_faculty,
872 rep_staff = new_references.rep_staff,
873 rep_student = new_references.rep_student,
874 rep_alumni = new_references.rep_alumni,
875 emergency_contact_flag = new_references.emergency_contact_flag,
876 last_update_date = x_last_update_date,
877 last_updated_by = x_last_updated_by,
878 last_update_login = x_last_update_login
879 WHERE rowid = x_rowid;
880 ELSE
881 UPDATE igs_pe_hz_rel
882 SET
883 primary = new_references.primary,
884 secondary = new_references.secondary,
885 joint_salutation = new_references.joint_salutation,
886 next_to_kin = new_references.next_to_kin,
887 rep_faculty = new_references.rep_faculty,
888 rep_staff = new_references.rep_staff,
889 rep_student = new_references.rep_student,
890 rep_alumni = new_references.rep_alumni,
891 emergency_contact_flag = old_references.emergency_contact_flag,
892 last_update_date = x_last_update_date,
893 last_updated_by = x_last_updated_by,
894 last_update_login = x_last_update_login
895 WHERE rowid = x_rowid;
896 END IF;
897
898 IF (SQL%NOTFOUND) THEN
899 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
900 igs_ge_msg_stack.add;
901 igs_sc_gen_001.unset_ctx('R');
902 app_exception.raise_exception;
903 END IF;
904 IF (x_mode = 'S') THEN
905 igs_sc_gen_001.unset_ctx('R');
906 END IF;
907
908
909 after_dml(p_action => 'UPDATE', x_rowid => x_rowid);
910
911
912 EXCEPTION
913 WHEN OTHERS THEN
914 IF (SQLCODE = (-28115)) THEN
915 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
916 fnd_message.set_token ('ERR_CD', SQLCODE);
917 igs_ge_msg_stack.add;
918 igs_sc_gen_001.unset_ctx('R');
919 app_exception.raise_exception;
920 ELSE
921 igs_sc_gen_001.unset_ctx('R');
922 RAISE;
923 END IF;
924 END update_row;
925
926
927 PROCEDURE add_row (
928 x_rowid IN OUT NOCOPY VARCHAR2,
929 x_relationship_id IN NUMBER,
930 x_directional_flag IN VARCHAR2,
931 x_primary IN VARCHAR2,
932 x_secondary IN VARCHAR2,
933 x_joint_salutation IN VARCHAR2,
934 x_next_to_kin IN VARCHAR2,
935 x_rep_faculty IN VARCHAR2,
936 x_rep_staff IN VARCHAR2,
937 x_rep_student IN VARCHAR2,
938 x_rep_alumni IN VARCHAR2,
939 x_emergency_contact_flag IN VARCHAR2,
940 x_mode IN VARCHAR2
941 ) AS
942 /*
943 || Created By :
944 || Created On : 28-APR-2003
945 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
946 || Known limitations, enhancements or remarks :
947 || Change History :
948 || Who When What
949 || (reverse chronological order - newest change first)
950 */
951 CURSOR c1 IS
952 SELECT rowid
953 FROM igs_pe_hz_rel
954 WHERE relationship_id = x_relationship_id
955 AND directional_flag = x_directional_flag;
956
957 BEGIN
958
959 OPEN c1;
960 FETCH c1 INTO x_rowid;
961 IF (c1%NOTFOUND) THEN
962 CLOSE c1;
963
964 insert_row (
965 x_rowid,
966 x_relationship_id,
967 x_directional_flag,
968 x_primary,
969 x_secondary,
970 x_joint_salutation,
971 x_next_to_kin,
972 x_rep_faculty,
973 x_rep_staff,
974 x_rep_student,
975 x_rep_alumni,
976 x_emergency_contact_flag,
977 x_mode
978 );
979 RETURN;
980 END IF;
981 CLOSE c1;
982
983 update_row (
984 x_rowid,
985 x_relationship_id,
986 x_directional_flag,
987 x_primary,
988 x_secondary,
989 x_joint_salutation,
990 x_next_to_kin,
991 x_rep_faculty,
992 x_rep_staff,
993 x_rep_student,
994 x_rep_alumni,
995 x_emergency_contact_flag,
996 x_mode
997 );
998
999 END add_row;
1000
1001
1002 PROCEDURE delete_row (
1003 x_rowid IN VARCHAR2,
1004 x_mode IN VARCHAR2
1005 ) AS
1006 /*
1007 || Created By :
1008 || Created On : 28-APR-2003
1009 || Purpose : Handles the DELETE DML logic for the table.
1010 || Known limitations, enhancements or remarks :
1011 || Change History :
1012 || Who When What
1013 || (reverse chronological order - newest change first)
1014 */
1015 BEGIN
1016
1017 before_dml (
1018 p_action => 'DELETE',
1019 x_rowid => x_rowid
1020 );
1021
1022 IF (x_mode = 'S') THEN
1023 igs_sc_gen_001.set_ctx('R');
1024 END IF;
1025 DELETE FROM igs_pe_hz_rel
1026 WHERE rowid = x_rowid;
1027
1028 IF (SQL%NOTFOUND) THEN
1029 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1030 igs_ge_msg_stack.add;
1031 igs_sc_gen_001.unset_ctx('R');
1032 app_exception.raise_exception;
1033 END IF;
1034 IF (x_mode = 'S') THEN
1035 igs_sc_gen_001.unset_ctx('R');
1036 END IF;
1037
1038
1039 END delete_row;
1040
1041
1042 END igs_pe_hz_rel_pkg;