[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_EIT_PKG
Source
1 PACKAGE BODY igs_pe_eit_pkg AS
2 /* $Header: IGSNI87B.pls 120.4 2005/10/17 02:21:58 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_eit%ROWTYPE;
6 new_references igs_pe_eit%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_pe_eit_id IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_information_type IN VARCHAR2 DEFAULT NULL,
14 x_pei_information1 IN VARCHAR2 DEFAULT NULL,
15 x_pei_information2 IN VARCHAR2 DEFAULT NULL,
16 x_pei_information3 IN VARCHAR2 DEFAULT NULL,
17 x_pei_information4 IN VARCHAR2 DEFAULT NULL,
18 x_pei_information5 IN VARCHAR2 DEFAULT NULL,
19 x_start_date IN DATE DEFAULT NULL,
20 x_end_date IN DATE DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL
26 ) AS
27 /*
28 || Created By : cdcruz
29 || Created On : 21-SEP-2001
30 || Purpose : Initialises the Old and New references for the columns of the table.
31 || Known limitations, enhancements or remarks :
32 || Change History :
33 || Who When What
34 || (reverse chronological order - newest change first)
35 */
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM igs_pe_eit
40 WHERE rowid = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF ((cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT'))) THEN
51 CLOSE cur_old_ref_values;
52 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
53 igs_ge_msg_stack.add;
54 app_exception.raise_exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.pe_eit_id := x_pe_eit_id;
61 new_references.person_id := x_person_id;
62 new_references.information_type := x_information_type;
63 new_references.pei_information1 := x_pei_information1;
64 new_references.pei_information2 := x_pei_information2;
65 new_references.pei_information3 := x_pei_information3;
66 new_references.pei_information4 := x_pei_information4;
67 new_references.pei_information5 := x_pei_information5;
68 new_references.start_date := x_start_date;
69 new_references.end_date := x_end_date;
70
71 IF (p_action = 'UPDATE') THEN
72 new_references.creation_date := old_references.creation_date;
73 new_references.created_by := old_references.created_by;
74 ELSE
75 new_references.creation_date := x_creation_date;
76 new_references.created_by := x_created_by;
77 END IF;
78
79 new_references.last_update_date := x_last_update_date;
80 new_references.last_updated_by := x_last_updated_by;
81 new_references.last_update_login := x_last_update_login;
82
83 END set_column_values;
84
85
86 PROCEDURE check_uniqueness AS
87 /*
88 || Created By : cdcruz
89 || Created On : 21-SEP-2001
90 || Purpose : Handles the Unique Constraint logic defined for the columns.
91 || Known limitations, enhancements or remarks :
92 || Change History :
93 || Who When What
94 || (reverse chronological order - newest change first)
95 */
96 BEGIN
97
98 IF ( get_uk_for_validation (
99 new_references.person_id,
100 new_references.information_type,
101 new_references.start_date
102 )
103 ) THEN
104 IF new_references.information_type = 'PE_STAT_RES_STATE' THEN
105 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_STATE_DUP_EXISTS');
106 igs_ge_msg_stack.add;
107 app_exception.raise_exception;
108 ELSIF new_references.information_type = 'PE_STAT_RES_COUNTRY' THEN
109 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_COUNTRY_DUP_EXISTS');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 ELSE
113 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
114 igs_ge_msg_stack.add;
115 app_exception.raise_exception;
116 END IF;
117 END IF;
118
119 END check_uniqueness;
120
121
122 PROCEDURE check_parent_existance AS
123 /*
124 || Created By : cdcruz
125 || Created On : 21-SEP-2001
126 || Purpose : Checks for the existance of Parent records.
127 || Known limitations, enhancements or remarks :
128 || Change History :
129 || Who When What
130 || (reverse chronological order - newest change first)
131 */
132 BEGIN
133
134 IF (((old_references.person_id = new_references.person_id)) OR
135 ((new_references.person_id IS NULL))) THEN
136 NULL;
137 ELSIF NOT igs_pe_person_pkg.get_pk_for_validation (
138 new_references.person_id
139 ) THEN
140 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
141 igs_ge_msg_stack.add;
142 app_exception.raise_exception;
143 END IF;
144
145 END check_parent_existance;
146
147
148 FUNCTION get_pk_for_validation (
149 x_pe_eit_id IN NUMBER
150 ) RETURN BOOLEAN AS
151 /*
152 || Created By : cdcruz
153 || Created On : 21-SEP-2001
154 || Purpose : Validates the Primary Key of the table.
155 || Known limitations, enhancements or remarks :
156 || Change History :
157 || Who When What
158 || (reverse chronological order - newest change first)
159 */
160 CURSOR cur_rowid IS
161 SELECT rowid
162 FROM igs_pe_eit
163 WHERE pe_eit_id = x_pe_eit_id
164 FOR UPDATE NOWAIT;
165
166 lv_rowid cur_rowid%RowType;
167
168 BEGIN
169
170 OPEN cur_rowid;
171 FETCH cur_rowid INTO lv_rowid;
172 IF (cur_rowid%FOUND) THEN
173 CLOSE cur_rowid;
174 RETURN(TRUE);
175 ELSE
176 CLOSE cur_rowid;
177 RETURN(FALSE);
178 END IF;
179
180 END get_pk_for_validation;
181
182
183 FUNCTION get_uk_for_validation (
184 x_person_id IN NUMBER,
185 x_information_type IN VARCHAR2,
186 x_start_date IN DATE
187 ) RETURN BOOLEAN AS
188 /*
189 || Created By : cdcruz
190 || Created On : 21-SEP-2001
191 || Purpose : Validates the Unique Keys of the table.
192 || Known limitations, enhancements or remarks :
193 || Change History :
194 || Who When What
195 || (reverse chronological order - newest change first)
196 */
197 CURSOR cur_rowid IS
198 SELECT rowid
199 FROM igs_pe_eit
200 WHERE person_id = x_person_id
201 AND information_type = x_information_type
202 AND start_date = x_start_date
203 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
204
205 lv_rowid cur_rowid%RowType;
206
207 BEGIN
208
209 OPEN cur_rowid;
210 FETCH cur_rowid INTO lv_rowid;
211 IF (cur_rowid%FOUND) THEN
212 CLOSE cur_rowid;
213 RETURN (true);
214 ELSE
215 CLOSE cur_rowid;
216 RETURN(FALSE);
217 END IF;
218
219 END get_uk_for_validation ;
220
221
222 PROCEDURE get_fk_hz_parties (
223 x_party_id IN NUMBER
224 ) AS
225 /*
226 || Created By : cdcruz
227 || Created On : 21-SEP-2001
228 || Purpose : Validates the Foreign Keys for the table.
229 || Known limitations, enhancements or remarks :
230 || Change History :
231 || Who When What
232 || (reverse chronological order - newest change first)
233 */
234 CURSOR cur_rowid IS
235 SELECT rowid
236 FROM igs_pe_eit
237 WHERE ((person_id = x_party_id));
238
239 lv_rowid cur_rowid%RowType;
240
241 BEGIN
242
243 OPEN cur_rowid;
244 FETCH cur_rowid INTO lv_rowid;
245 IF (cur_rowid%FOUND) THEN
246 CLOSE cur_rowid;
247 fnd_message.set_name ('IGS', 'IGS_PE_PEIT_HZ_FK');
248 igs_ge_msg_stack.add;
249 app_exception.raise_exception;
250 RETURN;
251 END IF;
252 CLOSE cur_rowid;
253
254 END get_fk_hz_parties;
255
256
257
258 PROCEDURE BeforeRowInsertUpdate(
259 p_inserting IN BOOLEAN,
260 p_updating IN BOOLEAN,
261 p_deleting IN BOOLEAN
262 ) as
263 ------------------------------------------------------------------------------------------
264 --Created by : vredkar
265 --Date created: 18-JUL-2005
266 --
267 --Purpose:
268 --Known limitations/enhancements and/or remarks:
269 --
270 --Change History:
271 --Who When What
272 ----------------------------------------------------------------------------------------------
273
274 l_bth_dt IGS_PE_PERSON_BASE_V.birth_date%TYPE;
275 l_default_date DATE := TO_DATE('4712/12/31','YYYY/MM/DD');
276 v_st_res VARCHAR2(60) ;
277
278 CURSOR validate_brth_dt(cp_person_id NUMBER) IS
279 SELECT birth_date
280 FROM IGS_PE_PERSON_BASE_V
281 WHERE person_id = cp_person_id ;
282
283 CURSOR validate_dt_overlap(cp_person_id NUMBER, cp_pe_eit_id NUMBER, cp_information_type VARCHAR2,
284 cp_start_date DATE, cp_end_date DATE) IS
285 SELECT 'X'
286 FROM igs_pe_eit
287 WHERE person_id= cp_person_id
288 AND information_type = cp_information_type
289 AND cp_pe_eit_id <> pe_eit_id
290 AND (cp_start_date between start_date AND NVL(end_date,l_default_date)
291 OR cp_end_date between start_date AND NVL(end_date,l_default_date)
292 OR (cp_start_date <= START_DATE
293 AND NVL(cp_end_date,l_default_date) >= NVL(END_DATE,l_default_date)) );
294
295 l_Overlap_check VARCHAR2(1);
296 l_inf_type VARCHAR2(100);
297 BEGIN
298
299
300 IF p_inserting OR p_updating THEN
301 OPEN validate_brth_dt(new_references.person_id);
302 FETCH validate_brth_dt INTO l_bth_dt;
303 CLOSE validate_brth_dt;
304
305 IF new_references.END_DATE IS NOT NULL AND new_references.END_DATE < new_references.START_DATE THEN
306 FND_MESSAGE.SET_NAME('IGS','IGS_FI_ST_DT_LE_END_DT');
307 IGS_GE_MSG_STACK.ADD;
308 APP_EXCEPTION.RAISE_EXCEPTION;
309
310 ELSIF l_bth_dt IS NOT NULL AND l_bth_dt > new_references.START_DATE THEN
311 FND_MESSAGE.SET_NAME('IGS','IGS_PE_DREC_GT_BTDT');
312 IGS_GE_MSG_STACK.ADD;
313 APP_EXCEPTION.RAISE_EXCEPTION;
314 END IF;
315
316
317 OPEN validate_dt_overlap(new_references.person_id, new_references.pe_eit_id, new_references.information_type,
318 new_references.start_date, new_references.end_date);
319 FETCH validate_dt_overlap INTO l_Overlap_check;
320 IF (validate_dt_overlap%FOUND) THEN
321 CLOSE validate_dt_overlap;
322 IF new_references.information_type = 'PE_STAT_RES_STATE' THEN
323 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_ST_RES');
324 l_inf_type := FND_MESSAGE.GET;
325 ELSIF new_references.information_type = 'PE_STAT_RES_COUNTRY' THEN
326 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_CON_PHY');
327 l_inf_type := FND_MESSAGE.GET;
328 ELSIF new_references.information_type = 'PE_INT_PERM_RES' THEN
329 l_inf_type := NULL;
330 ELSIF new_references.information_type = 'PE_STAT_RES_STATUS' THEN
331 FND_MESSAGE.SET_NAME ('IGS','IGS_PE_CIT');
332 l_inf_type := FND_MESSAGE.GET;
333 END IF;
334
335 IF l_inf_type IS NOT NULL THEN
336 FND_MESSAGE.SET_NAME('IGS','IGS_PE_DT_RANGE_OVLP');
337 FND_MESSAGE.SET_TOKEN('TOKEN',l_inf_type);
338 ELSE
339 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PRDS_OVERLAP');
340 END IF;
341
342 IGS_GE_MSG_STACK.ADD;
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 END IF;
345 CLOSE validate_dt_overlap;
346 END IF;
347
348 END BeforeRowInsertUpdate;
349
350 PROCEDURE before_dml (
351 p_action IN VARCHAR2,
352 x_rowid IN VARCHAR2 DEFAULT NULL,
353 x_pe_eit_id IN NUMBER DEFAULT NULL,
354 x_person_id IN NUMBER DEFAULT NULL,
355 x_information_type IN VARCHAR2 DEFAULT NULL,
356 x_pei_information1 IN VARCHAR2 DEFAULT NULL,
357 x_pei_information2 IN VARCHAR2 DEFAULT NULL,
358 x_pei_information3 IN VARCHAR2 DEFAULT NULL,
359 x_pei_information4 IN VARCHAR2 DEFAULT NULL,
360 x_pei_information5 IN VARCHAR2 DEFAULT NULL,
361 x_start_date IN DATE DEFAULT NULL,
362 x_end_date IN DATE DEFAULT NULL,
363 x_creation_date IN DATE DEFAULT NULL,
364 x_created_by IN NUMBER DEFAULT NULL,
365 x_last_update_date IN DATE DEFAULT NULL,
366 x_last_updated_by IN NUMBER DEFAULT NULL,
367 x_last_update_login IN NUMBER DEFAULT NULL
368 ) AS
369 /*
370 || Created By : cdcruz
371 || Created On : 21-SEP-2001
372 || Purpose : Initialises the columns, Checks Constraints, Calls the
373 || Trigger Handlers for the table, before any DML operation.
374 || Known limitations, enhancements or remarks :
375 || Change History :
376 || Who When What
377 || (reverse chronological order - newest change first)
378 */
379 BEGIN
380
381 set_column_values (
382 p_action,
383 x_rowid,
384 x_pe_eit_id,
385 x_person_id,
386 x_information_type,
387 x_pei_information1,
388 x_pei_information2,
389 x_pei_information3,
390 x_pei_information4,
391 x_pei_information5,
392 x_start_date,
393 x_end_date,
394 x_creation_date,
395 x_created_by,
396 x_last_update_date,
397 x_last_updated_by,
398 x_last_update_login
399 );
400
401 IF (p_action = 'INSERT') THEN
402 -- Call all the procedures related to Before Insert.
403 BeforeRowInsertUpdate( TRUE, FALSE,FALSE );
404 IF ( get_pk_for_validation(
405 new_references.pe_eit_id
406 )
407 ) THEN
408 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
409 igs_ge_msg_stack.add;
410 app_exception.raise_exception;
411 END IF;
412 check_uniqueness;
413 check_parent_existance;
414 ELSIF (p_action = 'UPDATE') THEN
415 -- Call all the procedures related to Before Update.
416 BeforeRowInsertUpdate( FALSE,TRUE,FALSE );
417 check_uniqueness;
418 check_parent_existance;
419 ELSIF (p_action = 'VALIDATE_INSERT') THEN
420 -- Call all the procedures related to Before Insert.
421 IF ( get_pk_for_validation (
422 new_references.pe_eit_id
423 )
424 ) THEN
425 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
426 igs_ge_msg_stack.add;
427 app_exception.raise_exception;
428 END IF;
429 check_uniqueness;
430 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
431 check_uniqueness;
432 END IF;
433
434 END before_dml;
435
436
437 PROCEDURE insert_row (
438 x_rowid IN OUT NOCOPY VARCHAR2,
439 x_pe_eit_id IN OUT NOCOPY NUMBER,
440 x_person_id IN NUMBER,
441 x_information_type IN VARCHAR2,
442 x_pei_information1 IN VARCHAR2,
443 x_pei_information2 IN VARCHAR2,
444 x_pei_information3 IN VARCHAR2,
445 x_pei_information4 IN VARCHAR2,
446 x_pei_information5 IN VARCHAR2,
447 x_start_date IN DATE,
448 x_end_date IN DATE,
449 x_mode IN VARCHAR2 DEFAULT 'R'
450 ) AS
451 /*
452 || Created By : cdcruz
453 || Created On : 21-SEP-2001
454 || Purpose : Handles the INSERT DML logic for the table.
455 || Known limitations, enhancements or remarks :
456 || Change History :
457 || Who When What
458 || (reverse chronological order - newest change first)
459 */
460 CURSOR c IS
461 SELECT rowid
462 FROM igs_pe_eit
463 WHERE pe_eit_id = x_pe_eit_id;
464
465 x_last_update_date DATE;
466 x_last_updated_by NUMBER;
467 x_last_update_login NUMBER;
468
469 BEGIN
470
471 x_last_update_date := SYSDATE;
472 IF (x_mode = 'I') THEN
473 x_last_updated_by := 1;
474 x_last_update_login := 0;
475 ELSIF (X_MODE IN ('R', 'S')) THEN
476 x_last_updated_by := fnd_global.user_id;
477 IF (x_last_updated_by IS NULL) THEN
478 x_last_updated_by := -1;
479 END IF;
480 x_last_update_login := fnd_global.login_id;
481 IF (x_last_update_login IS NULL) THEN
482 x_last_update_login := -1;
483 END IF;
484 ELSE
485 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
486 igs_ge_msg_stack.add;
487 app_exception.raise_exception;
488 END IF;
489
490 SELECT igs_pe_eit_s.NEXTVAL
491 INTO x_pe_eit_id
492 FROM dual;
493
494 before_dml(
495 p_action => 'INSERT',
496 x_rowid => x_rowid,
497 x_pe_eit_id => x_pe_eit_id,
498 x_person_id => x_person_id,
499 x_information_type => x_information_type,
500 x_pei_information1 => x_pei_information1,
501 x_pei_information2 => x_pei_information2,
502 x_pei_information3 => x_pei_information3,
503 x_pei_information4 => x_pei_information4,
504 x_pei_information5 => x_pei_information5,
505 x_start_date => x_start_date,
506 x_end_date => x_end_date,
507 x_creation_date => x_last_update_date,
508 x_created_by => x_last_updated_by,
509 x_last_update_date => x_last_update_date,
510 x_last_updated_by => x_last_updated_by,
511 x_last_update_login => x_last_update_login
512 );
513
514 IF (x_mode = 'S') THEN
515 igs_sc_gen_001.set_ctx('R');
516 END IF;
517 INSERT INTO igs_pe_eit (
518 pe_eit_id,
519 person_id,
520 information_type,
521 pei_information1,
522 pei_information2,
523 pei_information3,
524 pei_information4,
525 pei_information5,
526 start_date,
527 end_date,
528 creation_date,
529 created_by,
530 last_update_date,
531 last_updated_by,
532 last_update_login
533 ) VALUES (
534 new_references.pe_eit_id,
535 new_references.person_id,
536 new_references.information_type,
537 new_references.pei_information1,
538 new_references.pei_information2,
539 new_references.pei_information3,
540 new_references.pei_information4,
541 new_references.pei_information5,
542 new_references.start_date,
543 new_references.end_date,
544 x_last_update_date,
545 x_last_updated_by,
546 x_last_update_date,
547 x_last_updated_by,
548 x_last_update_login
549 );
550 IF (x_mode = 'S') THEN
551 igs_sc_gen_001.unset_ctx('R');
552 END IF;
553
554
555 OPEN c;
556 FETCH c INTO x_rowid;
557 IF (c%NOTFOUND) THEN
558 CLOSE c;
559 RAISE NO_DATA_FOUND;
560 END IF;
561 CLOSE c;
562
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
567 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
568 fnd_message.set_token ('ERR_CD', SQLCODE);
569 igs_ge_msg_stack.add;
570 igs_sc_gen_001.unset_ctx('R');
571 app_exception.raise_exception;
572 ELSE
573 igs_sc_gen_001.unset_ctx('R');
574 RAISE;
575 END IF;
576 END insert_row;
577
578
579 PROCEDURE lock_row (
580 x_rowid IN VARCHAR2,
581 x_pe_eit_id IN NUMBER,
582 x_person_id IN NUMBER,
583 x_information_type IN VARCHAR2,
584 x_pei_information1 IN VARCHAR2,
585 x_pei_information2 IN VARCHAR2,
586 x_pei_information3 IN VARCHAR2,
587 x_pei_information4 IN VARCHAR2,
588 x_pei_information5 IN VARCHAR2,
589 x_start_date IN DATE,
590 x_end_date IN DATE
591 ) AS
592 /*
593 || Created By : cdcruz
594 || Created On : 21-SEP-2001
595 || Purpose : Handles the LOCK mechanism for the table.
596 || Known limitations, enhancements or remarks :
597 || Change History :
598 || Who When What
599 || (reverse chronological order - newest change first)
600 */
601 CURSOR c1 IS
602 SELECT
603 person_id,
604 information_type,
605 pei_information1,
606 pei_information2,
607 pei_information3,
608 pei_information4,
609 pei_information5,
610 start_date,
611 end_date
612 FROM igs_pe_eit
613 WHERE rowid = x_rowid
614 FOR UPDATE NOWAIT;
615
616 tlinfo c1%ROWTYPE;
617
618 BEGIN
619
620 OPEN c1;
621 FETCH c1 INTO tlinfo;
622 IF (c1%notfound) THEN
623 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
624 igs_ge_msg_stack.add;
625 CLOSE c1;
626 app_exception.raise_exception;
627 RETURN;
628 END IF;
629 CLOSE c1;
630
631 IF (
632 (tlinfo.person_id = x_person_id)
633 AND (tlinfo.information_type = x_information_type)
634 AND ((tlinfo.pei_information1 = x_pei_information1) OR ((tlinfo.pei_information1 IS NULL) AND (X_pei_information1 IS NULL)))
635 AND ((tlinfo.pei_information2 = x_pei_information2) OR ((tlinfo.pei_information2 IS NULL) AND (X_pei_information2 IS NULL)))
636 AND ((tlinfo.pei_information3 = x_pei_information3) OR ((tlinfo.pei_information3 IS NULL) AND (X_pei_information3 IS NULL)))
637 AND ((tlinfo.pei_information4 = x_pei_information4) OR ((tlinfo.pei_information4 IS NULL) AND (X_pei_information4 IS NULL)))
638 AND ((tlinfo.pei_information5 = x_pei_information5) OR ((tlinfo.pei_information5 IS NULL) AND (X_pei_information5 IS NULL)))
639 AND (tlinfo.start_date = x_start_date)
640 AND ((tlinfo.end_date = x_end_date) OR ((tlinfo.end_date IS NULL) AND (X_end_date IS NULL)))
641 ) THEN
642 NULL;
643 ELSE
644 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
645 igs_ge_msg_stack.add;
646 app_exception.raise_exception;
647 END IF;
648
649 RETURN;
650
651 END lock_row;
652
653
654 PROCEDURE update_row (
655 x_rowid IN VARCHAR2,
656 x_pe_eit_id IN NUMBER,
657 x_person_id IN NUMBER,
658 x_information_type IN VARCHAR2,
659 x_pei_information1 IN VARCHAR2,
660 x_pei_information2 IN VARCHAR2,
661 x_pei_information3 IN VARCHAR2,
662 x_pei_information4 IN VARCHAR2,
663 x_pei_information5 IN VARCHAR2,
664 x_start_date IN DATE,
665 x_end_date IN DATE,
666 x_mode IN VARCHAR2 DEFAULT 'R'
667 ) AS
668 /*
669 || Created By : cdcruz
670 || Created On : 21-SEP-2001
671 || Purpose : Handles the UPDATE DML logic for the table.
672 || Known limitations, enhancements or remarks :
673 || Change History :
674 || Who When What
675 || (reverse chronological order - newest change first)
676 */
677 x_last_update_date DATE ;
678 x_last_updated_by NUMBER;
679 x_last_update_login NUMBER;
680
681 BEGIN
682
683 x_last_update_date := SYSDATE;
684 IF (X_MODE = 'I') THEN
685 x_last_updated_by := 1;
686 x_last_update_login := 0;
687 ELSIF (X_MODE IN ('R', 'S')) THEN
688 x_last_updated_by := fnd_global.user_id;
689 IF x_last_updated_by IS NULL THEN
690 x_last_updated_by := -1;
691 END IF;
692 x_last_update_login := fnd_global.login_id;
693 IF (x_last_update_login IS NULL) THEN
694 x_last_update_login := -1;
695 END IF;
696 ELSE
697 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
698 igs_ge_msg_stack.add;
699 app_exception.raise_exception;
700 END IF;
701
702 before_dml(
703 p_action => 'UPDATE',
704 x_rowid => x_rowid,
705 x_pe_eit_id => x_pe_eit_id,
706 x_person_id => x_person_id,
707 x_information_type => x_information_type,
708 x_pei_information1 => x_pei_information1,
709 x_pei_information2 => x_pei_information2,
710 x_pei_information3 => x_pei_information3,
711 x_pei_information4 => x_pei_information4,
712 x_pei_information5 => x_pei_information5,
713 x_start_date => x_start_date,
714 x_end_date => x_end_date,
715 x_creation_date => x_last_update_date,
716 x_created_by => x_last_updated_by,
717 x_last_update_date => x_last_update_date,
718 x_last_updated_by => x_last_updated_by,
719 x_last_update_login => x_last_update_login
720 );
721
722 IF (x_mode = 'S') THEN
723 igs_sc_gen_001.set_ctx('R');
724 END IF;
725 UPDATE igs_pe_eit
726 SET
727 person_id = new_references.person_id,
728 information_type = new_references.information_type,
729 pei_information1 = new_references.pei_information1,
730 pei_information2 = new_references.pei_information2,
731 pei_information3 = new_references.pei_information3,
732 pei_information4 = new_references.pei_information4,
733 pei_information5 = new_references.pei_information5,
734 start_date = new_references.start_date,
735 end_date = new_references.end_date,
736 last_update_date = x_last_update_date,
737 last_updated_by = x_last_updated_by,
738 last_update_login = x_last_update_login
739 WHERE rowid = x_rowid;
740
741 IF (SQL%NOTFOUND) THEN
742 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
743 igs_ge_msg_stack.add;
744 igs_sc_gen_001.unset_ctx('R');
745 app_exception.raise_exception;
746 END IF;
747 IF (x_mode = 'S') THEN
748 igs_sc_gen_001.unset_ctx('R');
749 END IF;
750
751
752
753 EXCEPTION
754 WHEN OTHERS THEN
755 IF (SQLCODE = (-28115)) THEN
756 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
757 fnd_message.set_token ('ERR_CD', SQLCODE);
758 igs_ge_msg_stack.add;
759 igs_sc_gen_001.unset_ctx('R');
760 app_exception.raise_exception;
761 ELSE
762 igs_sc_gen_001.unset_ctx('R');
763 RAISE;
764 END IF;
765 END update_row;
766
767
768 PROCEDURE add_row (
769 x_rowid IN OUT NOCOPY VARCHAR2,
770 x_pe_eit_id IN OUT NOCOPY NUMBER,
771 x_person_id IN NUMBER,
772 x_information_type IN VARCHAR2,
773 x_pei_information1 IN VARCHAR2,
774 x_pei_information2 IN VARCHAR2,
775 x_pei_information3 IN VARCHAR2,
776 x_pei_information4 IN VARCHAR2,
777 x_pei_information5 IN VARCHAR2,
778 x_start_date IN DATE,
779 x_end_date IN DATE,
780 x_mode IN VARCHAR2 DEFAULT 'R'
781 ) AS
782 /*
783 || Created By : cdcruz
784 || Created On : 21-SEP-2001
785 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
786 || Known limitations, enhancements or remarks :
787 || Change History :
788 || Who When What
789 || (reverse chronological order - newest change first)
790 */
791 CURSOR c1 IS
792 SELECT rowid
793 FROM igs_pe_eit
794 WHERE pe_eit_id = x_pe_eit_id;
795
796 BEGIN
797
798 OPEN c1;
799 FETCH c1 INTO x_rowid;
800 IF (c1%NOTFOUND) THEN
801 CLOSE c1;
802
803 insert_row (
804 x_rowid,
805 x_pe_eit_id,
806 x_person_id,
807 x_information_type,
808 x_pei_information1,
809 x_pei_information2,
810 x_pei_information3,
811 x_pei_information4,
812 x_pei_information5,
813 x_start_date,
814 x_end_date,
815 x_mode
816 );
817 RETURN;
818 END IF;
819 CLOSE c1;
820
821 update_row (
822 x_rowid,
823 x_pe_eit_id,
824 x_person_id,
825 x_information_type,
826 x_pei_information1,
827 x_pei_information2,
828 x_pei_information3,
829 x_pei_information4,
830 x_pei_information5,
831 x_start_date,
832 x_end_date,
833 x_mode
834 );
835
836 END add_row;
837
838
839 PROCEDURE delete_row (
840 x_rowid IN VARCHAR2,
841 x_mode IN VARCHAR2
842 ) AS
843 /*
844 || Created By : cdcruz
845 || Created On : 21-SEP-2001
846 || Purpose : Handles the DELETE DML logic for the table.
847 || Known limitations, enhancements or remarks :
848 || Change History :
849 || Who When What
850 || (reverse chronological order - newest change first)
851 */
852 BEGIN
853
854 before_dml (
855 p_action => 'DELETE',
856 x_rowid => x_rowid
857 );
858
859 IF (x_mode = 'S') THEN
860 igs_sc_gen_001.set_ctx('R');
861 END IF;
862 DELETE FROM igs_pe_eit
863 WHERE rowid = x_rowid;
864
865 IF (SQL%NOTFOUND) THEN
866 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
867 igs_ge_msg_stack.add;
868 igs_sc_gen_001.unset_ctx('R');
869 app_exception.raise_exception;
870 END IF;
871 IF (x_mode = 'S') THEN
872 igs_sc_gen_001.unset_ctx('R');
873 END IF;
874
875
876 END delete_row;
877
878
879 END igs_pe_eit_pkg;