1 PACKAGE BODY igs_pe_sn_contact_pkg AS
2 /* $Header: IGSNI88B.pls 120.1 2005/06/28 06:14:48 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_sn_contact%ROWTYPE;
6 new_references igs_pe_sn_contact%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_sn_contact_id IN NUMBER DEFAULT NULL,
12 x_disability_id IN NUMBER DEFAULT NULL,
13 x_contact_name IN VARCHAR2 DEFAULT NULL,
14 x_contact_date IN DATE DEFAULT NULL,
15 x_comments IN VARCHAR2 DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22 /*
23 || Created By : cdcruz
24 || Created On : 21-SEP-2001
25 || Purpose : Initialises the Old and New references for the columns of the table.
26 || Known limitations, enhancements or remarks :
27 || Change History :
28 || Who When What
29 || (reverse chronological order - newest change first)
30 */
31
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM igs_pe_sn_contact
35 WHERE rowid = x_rowid;
36
37 BEGIN
38
39 l_rowid := x_rowid;
40
41 -- Code for setting the Old and New Reference Values.
42 -- Populate Old Values.
43 OPEN cur_old_ref_values;
44 FETCH cur_old_ref_values INTO old_references;
45 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
46 CLOSE cur_old_ref_values;
47 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
48 igs_ge_msg_stack.add;
49 app_exception.raise_exception;
50 RETURN;
51 END IF;
52 CLOSE cur_old_ref_values;
53
54 -- Populate New Values.
55 new_references.sn_contact_id := x_sn_contact_id;
56 new_references.disability_id := x_disability_id;
57 new_references.contact_name := x_contact_name;
58 new_references.contact_date := x_contact_date;
59 new_references.comments := x_comments;
60
61 IF (p_action = 'UPDATE') THEN
62 new_references.creation_date := old_references.creation_date;
63 new_references.created_by := old_references.created_by;
64 ELSE
65 new_references.creation_date := x_creation_date;
66 new_references.created_by := x_created_by;
67 END IF;
68
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END set_column_values;
74
75 PROCEDURE check_uniqueness AS
76 /*
77 || Created By : cdcruz
78 || Created On : 21-SEP-2001
79 || Purpose : Handles the Unique Constraint logic defined for the columns.
80 || Known limitations, enhancements or remarks :
81 || Change History :
82 || Who When What
83 || (reverse chronological order - newest change first)
84 */
85 BEGIN
86
87 IF ( get_uk_for_validation (
88 new_references.disability_id,
89 new_references.contact_date,
90 new_references.contact_name
91 )
92 ) THEN
93 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
94 igs_ge_msg_stack.add;
95 app_exception.raise_exception;
96 END IF;
97
98 END check_uniqueness;
99
100
101 PROCEDURE check_parent_existance AS
102 /*
103 || Created By : cdcruz
104 || Created On : 21-SEP-2001
105 || Purpose : Checks for the existance of Parent records.
106 || Known limitations, enhancements or remarks :
107 || Change History :
108 || Who When What
109 || (reverse chronological order - newest change first)
110 */
111 BEGIN
112
113 IF (((old_references.disability_id = new_references.disability_id)) OR
114 ((new_references.disability_id IS NULL))) THEN
115 NULL;
116 ELSIF NOT igs_pe_pers_disablty_pkg.get_pk_for_validation (
117 new_references.disability_id
118 ) THEN
119 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
120 igs_ge_msg_stack.add;
121 app_exception.raise_exception;
122 END IF;
123
124 END check_parent_existance;
125
126
127 FUNCTION get_pk_for_validation (
128 x_sn_contact_id IN NUMBER
129 ) RETURN BOOLEAN AS
130 /*
131 || Created By : cdcruz
132 || Created On : 21-SEP-2001
133 || Purpose : Validates the Primary Key of the table.
134 || Known limitations, enhancements or remarks :
135 || Change History :
136 || Who When What
137 || (reverse chronological order - newest change first)
138 */
139 CURSOR cur_rowid IS
140 SELECT rowid
141 FROM igs_pe_sn_contact
142 WHERE sn_contact_id = x_sn_contact_id
143 FOR UPDATE NOWAIT;
144
145 lv_rowid cur_rowid%RowType;
146
147 BEGIN
148
149 OPEN cur_rowid;
150 FETCH cur_rowid INTO lv_rowid;
151 IF (cur_rowid%FOUND) THEN
152 CLOSE cur_rowid;
153 RETURN(TRUE);
154 ELSE
155 CLOSE cur_rowid;
156 RETURN(FALSE);
157 END IF;
158
159 END get_pk_for_validation;
160
161 FUNCTION get_uk_for_validation (
162 x_disability_id IN NUMBER,
163 x_contact_date IN DATE,
164 x_contact_name IN VARCHAR2
165 ) RETURN BOOLEAN AS
166 /*
167 || Created By : cdcruz
168 || Created On : 21-SEP-2001
169 || Purpose : Validates the Unique Keys of the table.
170 || Known limitations, enhancements or remarks :
171 || Change History :
172 || Who When What
173 || (reverse chronological order - newest change first)
174 */
175 CURSOR cur_rowid IS
176 SELECT rowid
177 FROM igs_pe_sn_contact
178 WHERE disability_id = x_disability_id
179 AND ( (contact_date = x_contact_date) OR (contact_date IS NULL and x_contact_date IS NULL ))
180 AND ( (UPPER(contact_name) = UPPER(x_contact_name)) OR (contact_name IS NULL and x_contact_name IS NULL ))
181 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
182
183 lv_rowid cur_rowid%RowType;
184
185 BEGIN
186
187 OPEN cur_rowid;
188 FETCH cur_rowid INTO lv_rowid;
189 IF (cur_rowid%FOUND) THEN
190 CLOSE cur_rowid;
191 RETURN (true);
192 ELSE
193 CLOSE cur_rowid;
194 RETURN(FALSE);
195 END IF;
196
197 END get_uk_for_validation ;
198
199
200
201 PROCEDURE get_fk_igs_pe_pers_disablty (
202 x_igs_pe_pers_disablty_id IN NUMBER
203 ) AS
204 /*
205 || Created By : cdcruz
206 || Created On : 21-SEP-2001
207 || Purpose : Validates the Foreign Keys for the table.
208 || Known limitations, enhancements or remarks :
209 || Change History :
210 || Who When What
211 || (reverse chronological order - newest change first)
212 */
213 CURSOR cur_rowid IS
214 SELECT rowid
215 FROM igs_pe_sn_contact
216 WHERE ((disability_id = x_igs_pe_pers_disablty_id));
217
218 lv_rowid cur_rowid%RowType;
219
220 BEGIN
221
222 OPEN cur_rowid;
223 FETCH cur_rowid INTO lv_rowid;
224 IF (cur_rowid%FOUND) THEN
225 CLOSE cur_rowid;
226 fnd_message.set_name ('IGS', 'IGS_PE_SNCT_PD_FK');
227 igs_ge_msg_stack.add;
228 app_exception.raise_exception;
229 RETURN;
230 END IF;
231 CLOSE cur_rowid;
232
233 END get_fk_igs_pe_pers_disablty;
234
235
236 PROCEDURE before_dml (
237 p_action IN VARCHAR2,
238 x_rowid IN VARCHAR2 DEFAULT NULL,
239 x_sn_contact_id IN NUMBER DEFAULT NULL,
240 x_disability_id IN NUMBER DEFAULT NULL,
241 x_contact_name IN VARCHAR2 DEFAULT NULL,
242 x_contact_date IN DATE DEFAULT NULL,
243 x_comments IN VARCHAR2 DEFAULT NULL,
244 x_creation_date IN DATE DEFAULT NULL,
245 x_created_by IN NUMBER DEFAULT NULL,
246 x_last_update_date IN DATE DEFAULT NULL,
247 x_last_updated_by IN NUMBER DEFAULT NULL,
248 x_last_update_login IN NUMBER DEFAULT NULL
249 ) AS
250 /*
251 || Created By : cdcruz
252 || Created On : 21-SEP-2001
253 || Purpose : Initialises the columns, Checks Constraints, Calls the
254 || Trigger Handlers for the table, before any DML operation.
255 || Known limitations, enhancements or remarks :
256 || Change History :
257 || Who When What
258 || (reverse chronological order - newest change first)
259 */
260 BEGIN
261
262 set_column_values (
263 p_action,
264 x_rowid,
265 x_sn_contact_id,
266 x_disability_id,
267 x_contact_name,
268 x_contact_date,
269 x_comments,
270 x_creation_date,
271 x_created_by,
272 x_last_update_date,
273 x_last_updated_by,
274 x_last_update_login
275 );
276
277 IF (p_action = 'INSERT') THEN
278 -- Call all the procedures related to Before Insert.
279 IF ( get_pk_for_validation(
280 new_references.sn_contact_id
281 )
282 ) THEN
283 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
284 igs_ge_msg_stack.add;
285 app_exception.raise_exception;
286 END IF;
287 check_uniqueness;
288 check_parent_existance;
289 ELSIF (p_action = 'UPDATE') THEN
290 -- Call all the procedures related to Before Update.
291 check_uniqueness;
292 check_parent_existance;
293 ELSIF (p_action = 'VALIDATE_INSERT') THEN
294 -- Call all the procedures related to Before Insert.
295 IF ( get_pk_for_validation (
296 new_references.sn_contact_id
297 )
298 ) THEN
299 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
300 igs_ge_msg_stack.add;
301 app_exception.raise_exception;
302 END IF;
303 check_uniqueness;
304
305 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
306 check_uniqueness ;
307 END IF;
308
309 END before_dml;
310
311
312 PROCEDURE insert_row (
313 x_rowid IN OUT NOCOPY VARCHAR2,
314 x_sn_contact_id IN OUT NOCOPY NUMBER,
315 x_disability_id IN NUMBER,
316 x_contact_name IN VARCHAR2,
317 x_contact_date IN DATE,
318 x_comments IN VARCHAR2,
319 x_mode IN VARCHAR2 DEFAULT 'R'
320 ) AS
321 /*
322 || Created By : cdcruz
323 || Created On : 21-SEP-2001
324 || Purpose : Handles the INSERT DML logic for the table.
325 || Known limitations, enhancements or remarks :
326 || Change History :
327 || Who When What
328 || (reverse chronological order - newest change first)
329 */
330 CURSOR c IS
331 SELECT rowid
332 FROM igs_pe_sn_contact
333 WHERE sn_contact_id = x_sn_contact_id;
334
335 x_last_update_date DATE;
336 x_last_updated_by NUMBER;
337 x_last_update_login NUMBER;
338
339 BEGIN
340
341 x_last_update_date := SYSDATE;
342 IF (x_mode = 'I') THEN
343 x_last_updated_by := 1;
344 x_last_update_login := 0;
345 ELSIF (X_MODE IN ('R', 'S')) THEN
346 x_last_updated_by := fnd_global.user_id;
347 IF (x_last_updated_by IS NULL) THEN
348 x_last_updated_by := -1;
349 END IF;
350 x_last_update_login := fnd_global.login_id;
351 IF (x_last_update_login IS NULL) THEN
352 x_last_update_login := -1;
353 END IF;
354 ELSE
355 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
356 igs_ge_msg_stack.add;
357 app_exception.raise_exception;
358 END IF;
359
360 SELECT igs_pe_sn_contact_s.NEXTVAL
361 INTO x_sn_contact_id
362 FROM dual;
363
364 before_dml(
365 p_action => 'INSERT',
366 x_rowid => x_rowid,
367 x_sn_contact_id => x_sn_contact_id,
368 x_disability_id => x_disability_id,
369 x_contact_name => x_contact_name,
370 x_contact_date => x_contact_date,
371 x_comments => x_comments,
372 x_creation_date => x_last_update_date,
373 x_created_by => x_last_updated_by,
374 x_last_update_date => x_last_update_date,
375 x_last_updated_by => x_last_updated_by,
376 x_last_update_login => x_last_update_login
377 );
378
379 IF (x_mode = 'S') THEN
380 igs_sc_gen_001.set_ctx('R');
381 END IF;
382 INSERT INTO igs_pe_sn_contact (
383 sn_contact_id,
384 disability_id,
385 contact_name,
386 contact_date,
387 comments,
388 creation_date,
389 created_by,
390 last_update_date,
391 last_updated_by,
392 last_update_login
393 ) VALUES (
394 new_references.sn_contact_id,
395 new_references.disability_id,
396 new_references.contact_name,
397 new_references.contact_date,
398 new_references.comments,
399 x_last_update_date,
400 x_last_updated_by,
401 x_last_update_date,
402 x_last_updated_by,
403 x_last_update_login
404 );
405 IF (x_mode = 'S') THEN
406 igs_sc_gen_001.unset_ctx('R');
407 END IF;
408
409
410 OPEN c;
411 FETCH c INTO x_rowid;
412 IF (c%NOTFOUND) THEN
413 CLOSE c;
414 RAISE NO_DATA_FOUND;
415 END IF;
416 CLOSE c;
417
418
419 EXCEPTION
420 WHEN OTHERS THEN
421 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
422 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
423 fnd_message.set_token ('ERR_CD', SQLCODE);
424 igs_ge_msg_stack.add;
425 igs_sc_gen_001.unset_ctx('R');
426 app_exception.raise_exception;
427 ELSE
428 igs_sc_gen_001.unset_ctx('R');
429 RAISE;
430 END IF;
431 END insert_row;
432
433
434 PROCEDURE lock_row (
435 x_rowid IN VARCHAR2,
436 x_sn_contact_id IN NUMBER,
437 x_disability_id IN NUMBER,
438 x_contact_name IN VARCHAR2,
439 x_contact_date IN DATE,
440 x_comments IN VARCHAR2
441 ) AS
442 /*
443 || Created By : cdcruz
444 || Created On : 21-SEP-2001
445 || Purpose : Handles the LOCK mechanism for the table.
446 || Known limitations, enhancements or remarks :
447 || Change History :
448 || Who When What
449 || (reverse chronological order - newest change first)
450 */
451 CURSOR c1 IS
452 SELECT
453 disability_id,
454 contact_name,
455 contact_date,
456 comments
457 FROM igs_pe_sn_contact
458 WHERE rowid = x_rowid
459 FOR UPDATE NOWAIT;
460
461 tlinfo c1%ROWTYPE;
462
463 BEGIN
464
465 OPEN c1;
466 FETCH c1 INTO tlinfo;
467 IF (c1%notfound) THEN
468 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
469 igs_ge_msg_stack.add;
470 CLOSE c1;
471 app_exception.raise_exception;
472 RETURN;
473 END IF;
474 CLOSE c1;
475
476 IF (
477 (tlinfo.disability_id = x_disability_id)
478 AND ((tlinfo.contact_name = x_contact_name) OR ((tlinfo.contact_name IS NULL) AND (X_contact_name IS NULL)))
479 AND ((tlinfo.contact_date = x_contact_date) OR ((tlinfo.contact_date IS NULL) AND (X_contact_date IS NULL)))
480 AND ((tlinfo.comments = x_comments) OR ((tlinfo.comments IS NULL) AND (X_comments IS NULL)))
481 ) THEN
482 NULL;
483 ELSE
484 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
485 igs_ge_msg_stack.add;
486 app_exception.raise_exception;
487 END IF;
488
489 RETURN;
490
491 END lock_row;
492
493
494 PROCEDURE update_row (
495 x_rowid IN VARCHAR2,
496 x_sn_contact_id IN NUMBER,
497 x_disability_id IN NUMBER,
498 x_contact_name IN VARCHAR2,
499 x_contact_date IN DATE,
500 x_comments IN VARCHAR2,
501 x_mode IN VARCHAR2 DEFAULT 'R'
502 ) AS
503 /*
504 || Created By : cdcruz
505 || Created On : 21-SEP-2001
506 || Purpose : Handles the UPDATE DML logic for the table.
507 || Known limitations, enhancements or remarks :
508 || Change History :
509 || Who When What
510 || (reverse chronological order - newest change first)
511 */
512 x_last_update_date DATE ;
513 x_last_updated_by NUMBER;
514 x_last_update_login NUMBER;
515
516 BEGIN
517
518 x_last_update_date := SYSDATE;
519 IF (X_MODE = 'I') THEN
520 x_last_updated_by := 1;
521 x_last_update_login := 0;
522 ELSIF (X_MODE IN ('R', 'S')) THEN
523 x_last_updated_by := fnd_global.user_id;
524 IF x_last_updated_by IS NULL THEN
525 x_last_updated_by := -1;
526 END IF;
527 x_last_update_login := fnd_global.login_id;
528 IF (x_last_update_login IS NULL) THEN
529 x_last_update_login := -1;
530 END IF;
531 ELSE
532 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
533 igs_ge_msg_stack.add;
534 app_exception.raise_exception;
535 END IF;
536
537 before_dml(
538 p_action => 'UPDATE',
539 x_rowid => x_rowid,
540 x_sn_contact_id => x_sn_contact_id,
541 x_disability_id => x_disability_id,
542 x_contact_name => x_contact_name,
543 x_contact_date => x_contact_date,
544 x_comments => x_comments,
545 x_creation_date => x_last_update_date,
546 x_created_by => x_last_updated_by,
547 x_last_update_date => x_last_update_date,
548 x_last_updated_by => x_last_updated_by,
549 x_last_update_login => x_last_update_login
550 );
551
552 IF (x_mode = 'S') THEN
553 igs_sc_gen_001.set_ctx('R');
554 END IF;
555 UPDATE igs_pe_sn_contact
556 SET
557 disability_id = new_references.disability_id,
558 contact_name = new_references.contact_name,
559 contact_date = new_references.contact_date,
560 comments = new_references.comments,
561 last_update_date = x_last_update_date,
562 last_updated_by = x_last_updated_by,
563 last_update_login = x_last_update_login
564 WHERE rowid = x_rowid;
565
566 IF (SQL%NOTFOUND) THEN
567 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
568 igs_ge_msg_stack.add;
569 igs_sc_gen_001.unset_ctx('R');
570 app_exception.raise_exception;
571 END IF;
572 IF (x_mode = 'S') THEN
573 igs_sc_gen_001.unset_ctx('R');
574 END IF;
575
576
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 IF (SQLCODE = (-28115)) THEN
581 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
582 fnd_message.set_token ('ERR_CD', SQLCODE);
583 igs_ge_msg_stack.add;
584 igs_sc_gen_001.unset_ctx('R');
585 app_exception.raise_exception;
586 ELSE
587 igs_sc_gen_001.unset_ctx('R');
588 RAISE;
589 END IF;
590 END update_row;
591
592
593 PROCEDURE add_row (
594 x_rowid IN OUT NOCOPY VARCHAR2,
595 x_sn_contact_id IN OUT NOCOPY NUMBER,
596 x_disability_id IN NUMBER,
597 x_contact_name IN VARCHAR2,
598 x_contact_date IN DATE,
599 x_comments IN VARCHAR2,
600 x_mode IN VARCHAR2 DEFAULT 'R'
601 ) AS
602 /*
603 || Created By : cdcruz
604 || Created On : 21-SEP-2001
605 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
606 || Known limitations, enhancements or remarks :
607 || Change History :
608 || Who When What
609 || (reverse chronological order - newest change first)
610 */
611 CURSOR c1 IS
612 SELECT rowid
613 FROM igs_pe_sn_contact
614 WHERE sn_contact_id = x_sn_contact_id;
615
616 BEGIN
617
618 OPEN c1;
619 FETCH c1 INTO x_rowid;
620 IF (c1%NOTFOUND) THEN
621 CLOSE c1;
622
623 insert_row (
624 x_rowid,
625 x_sn_contact_id,
626 x_disability_id,
627 x_contact_name,
628 x_contact_date,
629 x_comments,
630 x_mode
631 );
632 RETURN;
633 END IF;
634 CLOSE c1;
635
636 update_row (
637 x_rowid,
638 x_sn_contact_id,
639 x_disability_id,
640 x_contact_name,
641 x_contact_date,
642 x_comments,
643 x_mode
644 );
645
646 END add_row;
647
648
649 PROCEDURE delete_row (
650 x_rowid IN VARCHAR2,
651 x_mode IN VARCHAR2
652 ) AS
653 /*
654 || Created By : cdcruz
655 || Created On : 21-SEP-2001
656 || Purpose : Handles the DELETE DML logic for the table.
657 || Known limitations, enhancements or remarks :
658 || Change History :
659 || Who When What
660 || (reverse chronological order - newest change first)
661 */
662 BEGIN
663
664 before_dml (
665 p_action => 'DELETE',
666 x_rowid => x_rowid
667 );
668
669 IF (x_mode = 'S') THEN
670 igs_sc_gen_001.set_ctx('R');
671 END IF;
672 DELETE FROM igs_pe_sn_contact
673 WHERE rowid = x_rowid;
674
675 IF (SQL%NOTFOUND) THEN
676 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
677 igs_ge_msg_stack.add;
678 igs_sc_gen_001.unset_ctx('R');
679 app_exception.raise_exception;
680 END IF;
681 IF (x_mode = 'S') THEN
682 igs_sc_gen_001.unset_ctx('R');
683 END IF;
684
685
686 END delete_row;
687
688
689 END igs_pe_sn_contact_pkg;
[Home] [Help]