1 PACKAGE BODY igs_pe_fund_excl_pkg AS
2 /* $Header: IGSNI98B.pls 115.4 2002/11/29 01:37:42 nsidana noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references igs_pe_fund_excl%ROWTYPE;
6 new_references igs_pe_fund_excl%ROWTYPE;
7
8 PROCEDURE set_column_values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2,
11 x_fund_excl_id IN NUMBER,
12 x_person_id IN NUMBER,
13 x_encumbrance_type IN VARCHAR2,
14 x_pen_start_dt IN DATE,
15 x_s_encmb_effect_type IN VARCHAR2,
16 x_pee_start_dt IN DATE,
17 x_pee_sequence_number IN NUMBER,
18 x_fund_code IN VARCHAR2,
19 x_pfe_start_dt IN DATE,
20 x_expiry_dt IN DATE,
21 x_creation_date IN DATE,
22 x_created_by IN NUMBER,
23 x_last_update_date IN DATE,
24 x_last_updated_by IN NUMBER,
25 x_last_update_login IN NUMBER
26 ) AS
27 /*
28 || Created By : prabhat.patel
29 || Created On : 09-OCT-2002
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_fund_excl
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.fund_excl_id := x_fund_excl_id;
61 new_references.person_id := x_person_id;
62 new_references.encumbrance_type := x_encumbrance_type;
63 new_references.pen_start_dt := x_pen_start_dt;
64 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
65 new_references.pee_start_dt := x_pee_start_dt;
66 new_references.pee_sequence_number := x_pee_sequence_number;
67 new_references.fund_code := x_fund_code;
68 new_references.pfe_start_dt := x_pfe_start_dt;
69 new_references.expiry_dt := x_expiry_dt;
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 : prabhat.patel
89 || Created On : 09-OCT-2002
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.encumbrance_type,
101 new_references.pen_start_dt,
102 new_references.s_encmb_effect_type,
103 new_references.pee_start_dt,
104 new_references.pee_sequence_number,
105 new_references.fund_code,
106 new_references.pfe_start_dt
107 )
108 ) THEN
109 fnd_message.set_name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
110 igs_ge_msg_stack.add;
111 app_exception.raise_exception;
112 END IF;
113
114 END check_uniqueness;
115
116
117 PROCEDURE check_parent_existance AS
118 /*
119 || Created By : prabhat.patel
120 || Created On : 09-OCT-2002
121 || Purpose : Checks for the existance of Parent records.
122 || Known limitations, enhancements or remarks :
123 || Change History :
124 || Who When What
125 || (reverse chronological order - newest change first)
126 */
127 BEGIN
128
129 IF (((old_references.fund_code = new_references.fund_code)) OR
130 ((new_references.fund_code IS NULL))) THEN
131 NULL;
132 ELSIF NOT igf_aw_fund_cat_pkg.get_uk_For_validation (
133 new_references.fund_code
134 ) THEN
135 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
136 igs_ge_msg_stack.add;
137 app_exception.raise_exception;
138 END IF;
139
140 IF (((old_references.person_id = new_references.person_id) AND
141 (old_references.encumbrance_type = new_references.encumbrance_type) AND
142 (old_references.pen_start_dt = new_references.pen_start_dt) AND
143 (old_references.s_encmb_effect_type = new_references.s_encmb_effect_type) AND
144 (old_references.pee_start_dt = new_references.pee_start_dt) AND
145 (old_references.pee_sequence_number = new_references.pee_sequence_number)) OR
146 ((new_references.person_id IS NULL) OR
147 (new_references.encumbrance_type IS NULL) OR
148 (new_references.pen_start_dt IS NULL) OR
149 (new_references.s_encmb_effect_type IS NULL) OR
150 (new_references.pee_start_dt IS NULL) OR
151 (new_references.pee_sequence_number IS NULL))) THEN
152 NULL;
153 ELSIF NOT igs_pe_persenc_effct_pkg.get_pk_for_validation (
154 new_references.person_id,
155 new_references.encumbrance_type,
156 new_references.pen_start_dt,
157 new_references.s_encmb_effect_type,
158 new_references.pee_start_dt,
159 new_references.pee_sequence_number
160 ) THEN
161 fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
162 igs_ge_msg_stack.add;
163 app_exception.raise_exception;
164 END IF;
165
166 END check_parent_existance;
167
168
169 FUNCTION get_pk_for_validation (
170 x_fund_excl_id IN NUMBER
171 ) RETURN BOOLEAN AS
172 /*
173 || Created By : prabhat.patel
174 || Created On : 09-OCT-2002
175 || Purpose : Validates the Primary Key of the table.
176 || Known limitations, enhancements or remarks :
177 || Change History :
178 || Who When What
179 || (reverse chronological order - newest change first)
180 */
181 CURSOR cur_rowid IS
182 SELECT rowid
183 FROM igs_pe_fund_excl
184 WHERE fund_excl_id = x_fund_excl_id
185 FOR UPDATE NOWAIT;
186
187 lv_rowid cur_rowid%RowType;
188
189 BEGIN
190
191 OPEN cur_rowid;
192 FETCH cur_rowid INTO lv_rowid;
193 IF (cur_rowid%FOUND) THEN
194 CLOSE cur_rowid;
195 RETURN(TRUE);
196 ELSE
197 CLOSE cur_rowid;
198 RETURN(FALSE);
199 END IF;
200
201 END get_pk_for_validation;
202
203
204 FUNCTION get_uk_for_validation (
205 x_person_id IN NUMBER,
206 x_encumbrance_type IN VARCHAR2,
207 x_pen_start_dt IN DATE,
208 x_s_encmb_effect_type IN VARCHAR2,
209 x_pee_start_dt IN DATE,
210 x_pee_sequence_number IN NUMBER,
211 x_fund_code IN VARCHAR2,
212 x_pfe_start_dt IN DATE
213 ) RETURN BOOLEAN AS
214 /*
215 || Created By : prabhat.patel
216 || Created On : 09-OCT-2002
217 || Purpose : Validates the Unique Keys of the table.
218 || Known limitations, enhancements or remarks :
219 || Change History :
220 || Who When What
221 || (reverse chronological order - newest change first)
222 */
223 CURSOR cur_rowid IS
224 SELECT rowid
225 FROM igs_pe_fund_excl
226 WHERE person_id = x_person_id
227 AND encumbrance_type = x_encumbrance_type
228 AND pen_start_dt = x_pen_start_dt
229 AND s_encmb_effect_type = x_s_encmb_effect_type
230 AND pee_start_dt = x_pee_start_dt
231 AND pee_sequence_number = x_pee_sequence_number
232 AND fund_code = x_fund_code
233 AND pfe_start_dt = x_pfe_start_dt
234 AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
235
236 lv_rowid cur_rowid%RowType;
237
238 BEGIN
239
240 OPEN cur_rowid;
241 FETCH cur_rowid INTO lv_rowid;
242 IF (cur_rowid%FOUND) THEN
243 CLOSE cur_rowid;
244 RETURN (true);
245 ELSE
246 CLOSE cur_rowid;
247 RETURN(FALSE);
248 END IF;
249
250 END get_uk_for_validation ;
251
252
253 PROCEDURE get_ufk_igf_aw_fund_cat (
254 x_fund_code IN VARCHAR2
255 ) AS
256 /*
257 || Created By : prabhat.patel
258 || Created On : 09-OCT-2002
259 || Purpose : Validates the Foreign Keys for the table.
260 || Known limitations, enhancements or remarks :
261 || Change History :
262 || Who When What
263 || (reverse chronological order - newest change first)
264 */
265 CURSOR cur_rowid IS
266 SELECT rowid
267 FROM igs_pe_fund_excl
268 WHERE ((fund_code = x_fund_code));
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 OPEN cur_rowid;
275 FETCH cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 CLOSE cur_rowid;
278 fnd_message.set_name ('IGS', 'IGS_PE_FCAT_PEE_FK');
279 igs_ge_msg_stack.add;
280 app_exception.raise_exception;
281 RETURN;
282 END IF;
283 CLOSE cur_rowid;
284
285 END get_ufk_igf_aw_fund_cat;
286
287
288 PROCEDURE get_fk_igs_pe_persenc_effct (
289 x_person_id IN NUMBER,
290 x_encumbrance_type IN VARCHAR2,
291 x_pen_start_dt IN DATE,
292 x_s_encmb_effect_type IN VARCHAR2,
293 x_pee_start_dt IN DATE,
294 x_sequence_number IN NUMBER
295 ) AS
296 /*
297 || Created By : prabhat.patel
298 || Created On : 09-OCT-2002
299 || Purpose : Validates the Foreign Keys for the table.
300 || Known limitations, enhancements or remarks :
301 || Change History :
302 || Who When What
303 || (reverse chronological order - newest change first)
304 */
305 CURSOR cur_rowid IS
306 SELECT rowid
307 FROM igs_pe_fund_excl
308 WHERE ((encumbrance_type = x_encumbrance_type) AND
309 (pee_sequence_number = x_sequence_number) AND
310 (pee_start_dt = x_pee_start_dt) AND
311 (pen_start_dt = x_pen_start_dt) AND
312 (person_id = x_person_id) AND
313 (s_encmb_effect_type = x_s_encmb_effect_type));
314
315 lv_rowid cur_rowid%RowType;
316
317 BEGIN
318
319 OPEN cur_rowid;
320 FETCH cur_rowid INTO lv_rowid;
321 IF (cur_rowid%FOUND) THEN
322 CLOSE cur_rowid;
323 fnd_message.set_name ('IGS', 'IGS_PE_PFE_PEE_FK');
324 igs_ge_msg_stack.add;
325 app_exception.raise_exception;
326 RETURN;
327 END IF;
328 CLOSE cur_rowid;
329
330 END get_fk_igs_pe_persenc_effct;
331
332 PROCEDURE BeforeRowInsertUpdate(
333 p_inserting IN BOOLEAN,
334 p_updating IN BOOLEAN,
335 p_deleting IN BOOLEAN
336 ) AS
337
338 l_message_name VARCHAR2(30);
339
340 BEGIN
341 -- Validate that start date is not less than the current date.
342 IF (new_references.pfe_start_dt IS NOT NULL) AND
343 (p_inserting OR (p_updating AND
344 (old_references.pfe_start_dt <> new_references.pfe_start_dt)))
345 THEN
346 IF igs_en_val_pce.enrp_val_encmb_dt (
347 new_references.pfe_start_dt,
348 l_message_name) = FALSE THEN
349 FND_MESSAGE.SET_NAME('IGS', l_message_name);
350 IGS_GE_MSG_STACK.ADD;
351 APP_EXCEPTION.RAISE_EXCEPTION;
352 END IF;
353 END IF;
354
355 -- Validate that start date is not less than the parent IGS_PE_PERSON
356 -- Encumbrance Effect start date.
357 IF p_inserting THEN
358 IF igs_en_val_pce.enrp_val_encmb_dts (
359 new_references.pee_start_dt,
360 new_references.pfe_start_dt,
361 l_message_name) = FALSE THEN
362 FND_MESSAGE.SET_NAME('IGS', l_message_name);
363 IGS_GE_MSG_STACK.ADD;
364 APP_EXCEPTION.RAISE_EXCEPTION;
365 END IF;
366 END IF;
367
368 -- Validate that if expiry date is specified, then expiry date is not
369 -- less than the start date.
370 IF (new_references.expiry_dt IS NOT NULL) AND
371 (p_inserting OR (p_updating AND
372 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
373 <> new_references.expiry_dt)))
374 THEN
375 IF igs_en_val_pce.enrp_val_strt_exp_dt (
376 new_references.pfe_start_dt,
377 new_references.expiry_dt,
378 l_message_name) = FALSE THEN
379 FND_MESSAGE.SET_NAME('IGS', l_message_name);
380 IGS_GE_MSG_STACK.ADD;
381 APP_EXCEPTION.RAISE_EXCEPTION;
382 END IF;
383
384 IF igs_en_val_pce.enrp_val_encmb_dt (
385 new_references.expiry_dt,
386 l_message_name) = FALSE THEN
387 FND_MESSAGE.SET_NAME('IGS', l_message_name);
388 IGS_GE_MSG_STACK.ADD;
389 APP_EXCEPTION.RAISE_EXCEPTION;
390 END IF;
391 END IF;
392
393
394 -- Validate that records for this table can be created for the encumbrance
395 -- effect type.
396 IF p_inserting THEN
397 IF new_references.s_encmb_effect_type NOT IN ('EX_AWD','EX_DISB','EX_SP_AWD','EX_SP_DISB') THEN
398 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_CANT_CREATE_REC_ENCUMB');
399 IGS_GE_MSG_STACK.ADD;
400 APP_EXCEPTION.RAISE_EXCEPTION;
401 END IF;
402 END IF;
403
404 END BeforeRowInsertUpdate;
405
406 PROCEDURE AfterRowInsertUpdateDelete(
407 p_inserting IN BOOLEAN,
408 p_updating IN BOOLEAN,
409 p_deleting IN BOOLEAN
410 ) IS
411 l_check VARCHAR2(1);
412
413 CURSOR fund_exclusion_cur IS
414 SELECT 'X'
415 FROM igs_pe_fund_excl
416 WHERE person_id = new_references.person_id AND
417 encumbrance_type = new_references.encumbrance_type AND
418 pen_start_dt = new_references.pen_start_dt AND
419 s_encmb_effect_type = new_references.s_encmb_effect_type AND
420 pee_start_dt = new_references.pee_start_dt AND
421 fund_code = new_references.fund_code AND
422 expiry_dt IS NULL AND
423 pfe_start_dt <> new_references.pfe_start_dt;
424
425 BEGIN
426
427 OPEN fund_exclusion_cur;
428 FETCH fund_exclusion_cur INTO l_check;
429 IF fund_exclusion_cur%FOUND THEN
430 CLOSE fund_exclusion_cur;
431 FND_MESSAGE.SET_NAME('IGS', 'IGS_PE_FUND_EXCL_OPEN');
432 IGS_GE_MSG_STACK.ADD;
433 APP_EXCEPTION.RAISE_EXCEPTION;
434 END IF;
435 CLOSE fund_exclusion_cur;
436
437 END AfterRowInsertUpdateDelete;
438
439 PROCEDURE before_dml (
440 p_action IN VARCHAR2,
441 x_rowid IN VARCHAR2,
442 x_fund_excl_id IN NUMBER,
443 x_person_id IN NUMBER,
444 x_encumbrance_type IN VARCHAR2,
445 x_pen_start_dt IN DATE,
446 x_s_encmb_effect_type IN VARCHAR2,
447 x_pee_start_dt IN DATE,
448 x_pee_sequence_number IN NUMBER,
449 x_fund_code IN VARCHAR2,
450 x_pfe_start_dt IN DATE,
451 x_expiry_dt IN DATE,
452 x_creation_date IN DATE,
453 x_created_by IN NUMBER,
454 x_last_update_date IN DATE,
455 x_last_updated_by IN NUMBER,
456 x_last_update_login IN NUMBER
457 ) AS
458 /*
459 || Created By : prabhat.patel
460 || Created On : 09-OCT-2002
461 || Purpose : Initialises the columns, Checks Constraints, Calls the
462 || Trigger Handlers for the table, before any DML operation.
463 || Known limitations, enhancements or remarks :
464 || Change History :
465 || Who When What
466 || (reverse chronological order - newest change first)
467 */
468 BEGIN
469
470 set_column_values (
471 p_action,
472 x_rowid,
473 x_fund_excl_id,
474 x_person_id,
475 x_encumbrance_type,
476 x_pen_start_dt,
477 x_s_encmb_effect_type,
478 x_pee_start_dt,
479 x_pee_sequence_number,
480 x_fund_code,
481 x_pfe_start_dt,
482 x_expiry_dt,
483 x_creation_date,
484 x_created_by,
485 x_last_update_date,
486 x_last_updated_by,
487 x_last_update_login
488 );
489
490 IF (p_action = 'INSERT') THEN
491 -- Call all the procedures related to Before Insert.
492
493 beforerowinsertupdate(
494 p_inserting => TRUE,
495 p_updating => FALSE,
496 p_deleting => FALSE);
497
498 IF ( get_pk_for_validation(
499 new_references.fund_excl_id
500 )
501 ) THEN
502 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
503 igs_ge_msg_stack.add;
504 app_exception.raise_exception;
505 END IF;
506 check_uniqueness;
507 check_parent_existance;
508 ELSIF (p_action = 'UPDATE') THEN
509 -- Call all the procedures related to Before Update.
510 beforerowinsertupdate(
511 p_inserting => FALSE,
512 p_updating => TRUE,
513 p_deleting => FALSE);
514
515 check_uniqueness;
516 check_parent_existance;
517 ELSIF (p_action = 'VALIDATE_INSERT') THEN
518 -- Call all the procedures related to Before Insert.
519 beforerowinsertupdate(
520 p_inserting => TRUE,
521 p_updating => FALSE,
522 p_deleting => FALSE);
523
524 IF ( get_pk_for_validation (
525 new_references.fund_excl_id
526 )
527 ) THEN
528 fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
529 igs_ge_msg_stack.add;
530 app_exception.raise_exception;
531 END IF;
532 check_uniqueness;
533 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
534
535 beforerowinsertupdate(
536 p_inserting => FALSE,
537 p_updating => TRUE,
538 p_deleting => FALSE);
539
540 check_uniqueness;
541 END IF;
542
543 END before_dml;
544
545
546 PROCEDURE After_DML (
547 p_action IN VARCHAR2
548 ) AS
549
550 BEGIN
551
552 IF (p_action = 'INSERT') THEN
553 -- Call all the procedures related to After Insert.
554 AfterRowInsertUpdateDelete
555 ( p_inserting => TRUE,
556 p_updating => FALSE,
557 p_deleting => FALSE);
558
559 ELSIF (p_action = 'UPDATE') THEN
560 -- Call all the procedures related to After Update.
561 AfterRowInsertUpdateDelete
562 ( p_inserting => FALSE,
563 p_updating => TRUE,
564 p_deleting => FALSE);
565
566 ELSIF (p_action = 'DELETE') THEN
567 -- Call all the procedures related to After Delete.
568 NULL;
569 END IF;
570
571 END After_DML;
572
573 PROCEDURE insert_row (
574 x_rowid IN OUT NOCOPY VARCHAR2,
575 x_fund_excl_id IN OUT NOCOPY NUMBER,
576 x_person_id IN NUMBER,
577 x_encumbrance_type IN VARCHAR2,
578 x_pen_start_dt IN DATE,
579 x_s_encmb_effect_type IN VARCHAR2,
580 x_pee_start_dt IN DATE,
581 x_pee_sequence_number IN NUMBER,
582 x_fund_code IN VARCHAR2,
583 x_pfe_start_dt IN DATE,
584 x_expiry_dt IN DATE,
585 x_mode IN VARCHAR2
586 ) AS
587 /*
588 || Created By : prabhat.patel
589 || Created On : 09-OCT-2002
590 || Purpose : Handles the INSERT DML logic for the table.
591 || Known limitations, enhancements or remarks :
592 || Change History :
593 || Who When What
594 || (reverse chronological order - newest change first)
595 */
596
597 x_last_update_date DATE;
598 x_last_updated_by NUMBER;
599 x_last_update_login NUMBER;
600
601 BEGIN
602
603 x_last_update_date := SYSDATE;
604 IF (x_mode = 'I') THEN
605 x_last_updated_by := 1;
606 x_last_update_login := 0;
607 ELSIF (x_mode = 'R') THEN
608 x_last_updated_by := fnd_global.user_id;
609 IF (x_last_updated_by IS NULL) THEN
610 x_last_updated_by := -1;
611 END IF;
612 x_last_update_login := fnd_global.login_id;
613 IF (x_last_update_login IS NULL) THEN
614 x_last_update_login := -1;
615 END IF;
616 ELSE
617 fnd_message.set_name ('FND', 'SYSTEM-INVALID ARGS');
618 igs_ge_msg_stack.add;
619 app_exception.raise_exception;
620 END IF;
621
622 before_dml(
623 p_action => 'INSERT',
624 x_rowid => x_rowid,
625 x_fund_excl_id => x_fund_excl_id,
626 x_person_id => x_person_id,
627 x_encumbrance_type => x_encumbrance_type,
628 x_pen_start_dt => x_pen_start_dt,
629 x_s_encmb_effect_type => x_s_encmb_effect_type,
630 x_pee_start_dt => x_pee_start_dt,
631 x_pee_sequence_number => x_pee_sequence_number,
632 x_fund_code => x_fund_code,
633 x_pfe_start_dt => x_pfe_start_dt,
634 x_expiry_dt => x_expiry_dt,
635 x_creation_date => x_last_update_date,
636 x_created_by => x_last_updated_by,
637 x_last_update_date => x_last_update_date,
638 x_last_updated_by => x_last_updated_by,
639 x_last_update_login => x_last_update_login
640 );
641
642 INSERT INTO igs_pe_fund_excl (
643 fund_excl_id,
644 person_id,
645 encumbrance_type,
646 pen_start_dt,
647 s_encmb_effect_type,
648 pee_start_dt,
649 pee_sequence_number,
650 fund_code,
651 pfe_start_dt,
652 expiry_dt,
653 creation_date,
654 created_by,
655 last_update_date,
656 last_updated_by,
657 last_update_login
658 ) VALUES (
659 igs_pe_fund_excl_s.NEXTVAL,
660 new_references.person_id,
661 new_references.encumbrance_type,
662 new_references.pen_start_dt,
663 new_references.s_encmb_effect_type,
664 new_references.pee_start_dt,
665 new_references.pee_sequence_number,
666 new_references.fund_code,
667 new_references.pfe_start_dt,
668 new_references.expiry_dt,
669 x_last_update_date,
670 x_last_updated_by,
671 x_last_update_date,
672 x_last_updated_by,
673 x_last_update_login
674 ) RETURNING ROWID, fund_excl_id INTO x_rowid, x_fund_excl_id;
675
676 IF x_rowid IS NULL THEN
677 RAISE NO_DATA_FOUND;
678 END IF;
679
680 After_DML(
681 p_action => 'INSERT'
682 );
683
684 END insert_row;
685
686
687 PROCEDURE lock_row (
688 x_rowid IN VARCHAR2,
689 x_fund_excl_id IN NUMBER,
690 x_person_id IN NUMBER,
691 x_encumbrance_type IN VARCHAR2,
692 x_pen_start_dt IN DATE,
693 x_s_encmb_effect_type IN VARCHAR2,
694 x_pee_start_dt IN DATE,
695 x_pee_sequence_number IN NUMBER,
696 x_fund_code IN VARCHAR2,
697 x_pfe_start_dt IN DATE,
698 x_expiry_dt IN DATE
699 ) AS
700 /*
701 || Created By : prabhat.patel
702 || Created On : 09-OCT-2002
703 || Purpose : Handles the LOCK mechanism for the table.
704 || Known limitations, enhancements or remarks :
705 || Change History :
706 || Who When What
707 || (reverse chronological order - newest change first)
708 */
709 CURSOR c1 IS
710 SELECT
711 person_id,
712 encumbrance_type,
713 pen_start_dt,
714 s_encmb_effect_type,
715 pee_start_dt,
716 pee_sequence_number,
717 fund_code,
718 pfe_start_dt,
719 expiry_dt
720 FROM igs_pe_fund_excl
721 WHERE rowid = x_rowid
722 FOR UPDATE NOWAIT;
723
724 tlinfo c1%ROWTYPE;
725
726 BEGIN
727
728 OPEN c1;
729 FETCH c1 INTO tlinfo;
730 IF (c1%notfound) THEN
731 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
732 igs_ge_msg_stack.add;
733 CLOSE c1;
734 app_exception.raise_exception;
735 RETURN;
736 END IF;
737 CLOSE c1;
738
739 IF (
740 (tlinfo.person_id = x_person_id)
741 AND (tlinfo.encumbrance_type = x_encumbrance_type)
742 AND (tlinfo.pen_start_dt = x_pen_start_dt)
743 AND (tlinfo.s_encmb_effect_type = x_s_encmb_effect_type)
744 AND (tlinfo.pee_start_dt = x_pee_start_dt)
745 AND (tlinfo.pee_sequence_number = x_pee_sequence_number)
746 AND (tlinfo.fund_code = x_fund_code)
747 AND (tlinfo.pfe_start_dt = x_pfe_start_dt)
748 AND ((tlinfo.expiry_dt = x_expiry_dt) OR ((tlinfo.expiry_dt IS NULL) AND (X_expiry_dt IS NULL)))
749 ) THEN
750 NULL;
751 ELSE
752 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
753 igs_ge_msg_stack.add;
754 app_exception.raise_exception;
755 END IF;
756
757 RETURN;
758
759 END lock_row;
760
761
762 PROCEDURE update_row (
763 x_rowid IN VARCHAR2,
764 x_fund_excl_id IN NUMBER,
765 x_person_id IN NUMBER,
766 x_encumbrance_type IN VARCHAR2,
767 x_pen_start_dt IN DATE,
768 x_s_encmb_effect_type IN VARCHAR2,
769 x_pee_start_dt IN DATE,
770 x_pee_sequence_number IN NUMBER,
771 x_fund_code IN VARCHAR2,
772 x_pfe_start_dt IN DATE,
773 x_expiry_dt IN DATE,
774 x_mode IN VARCHAR2
775 ) AS
776 /*
777 || Created By : prabhat.patel
778 || Created On : 09-OCT-2002
779 || Purpose : Handles the UPDATE DML logic for the table.
780 || Known limitations, enhancements or remarks :
781 || Change History :
782 || Who When What
783 || (reverse chronological order - newest change first)
784 */
785 x_last_update_date DATE ;
786 x_last_updated_by NUMBER;
787 x_last_update_login NUMBER;
788
789 BEGIN
790
791 x_last_update_date := SYSDATE;
792 IF (X_MODE = 'I') THEN
793 x_last_updated_by := 1;
794 x_last_update_login := 0;
795 ELSIF (x_mode = 'R') THEN
796 x_last_updated_by := fnd_global.user_id;
797 IF x_last_updated_by IS NULL THEN
798 x_last_updated_by := -1;
799 END IF;
800 x_last_update_login := fnd_global.login_id;
801 IF (x_last_update_login IS NULL) THEN
802 x_last_update_login := -1;
803 END IF;
804 ELSE
805 fnd_message.set_name( 'FND', 'SYSTEM-INVALID ARGS');
806 igs_ge_msg_stack.add;
807 app_exception.raise_exception;
808 END IF;
809
810 before_dml(
811 p_action => 'UPDATE',
812 x_rowid => x_rowid,
813 x_fund_excl_id => x_fund_excl_id,
814 x_person_id => x_person_id,
815 x_encumbrance_type => x_encumbrance_type,
816 x_pen_start_dt => x_pen_start_dt,
817 x_s_encmb_effect_type => x_s_encmb_effect_type,
818 x_pee_start_dt => x_pee_start_dt,
819 x_pee_sequence_number => x_pee_sequence_number,
820 x_fund_code => x_fund_code,
821 x_pfe_start_dt => x_pfe_start_dt,
822 x_expiry_dt => x_expiry_dt,
823 x_creation_date => x_last_update_date,
824 x_created_by => x_last_updated_by,
825 x_last_update_date => x_last_update_date,
826 x_last_updated_by => x_last_updated_by,
827 x_last_update_login => x_last_update_login
828 );
829
830 UPDATE igs_pe_fund_excl
831 SET
832 person_id = new_references.person_id,
833 encumbrance_type = new_references.encumbrance_type,
834 pen_start_dt = new_references.pen_start_dt,
835 s_encmb_effect_type = new_references.s_encmb_effect_type,
836 pee_start_dt = new_references.pee_start_dt,
837 pee_sequence_number = new_references.pee_sequence_number,
838 fund_code = new_references.fund_code,
839 pfe_start_dt = new_references.pfe_start_dt,
840 expiry_dt = new_references.expiry_dt,
841 last_update_date = x_last_update_date,
842 last_updated_by = x_last_updated_by,
843 last_update_login = x_last_update_login
844 WHERE rowid = x_rowid;
845
846 IF (SQL%NOTFOUND) THEN
847 RAISE NO_DATA_FOUND;
848 END IF;
849
850 After_DML(
851 p_action => 'UPDATE'
852 );
853
854 END update_row;
855
856
857 PROCEDURE add_row (
858 x_rowid IN OUT NOCOPY VARCHAR2,
859 x_fund_excl_id IN OUT NOCOPY NUMBER,
860 x_person_id IN NUMBER,
861 x_encumbrance_type IN VARCHAR2,
862 x_pen_start_dt IN DATE,
863 x_s_encmb_effect_type IN VARCHAR2,
864 x_pee_start_dt IN DATE,
865 x_pee_sequence_number IN NUMBER,
866 x_fund_code IN VARCHAR2,
867 x_pfe_start_dt IN DATE,
868 x_expiry_dt IN DATE,
869 x_mode IN VARCHAR2
870 ) AS
871 /*
872 || Created By : prabhat.patel
873 || Created On : 09-OCT-2002
874 || Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
875 || Known limitations, enhancements or remarks :
876 || Change History :
877 || Who When What
878 || (reverse chronological order - newest change first)
879 */
880 CURSOR c1 IS
881 SELECT rowid
882 FROM igs_pe_fund_excl
883 WHERE fund_excl_id = x_fund_excl_id;
884
885 BEGIN
886
887 OPEN c1;
888 FETCH c1 INTO x_rowid;
889 IF (c1%NOTFOUND) THEN
890 CLOSE c1;
891
892 insert_row (
893 x_rowid,
894 x_fund_excl_id,
895 x_person_id,
896 x_encumbrance_type,
897 x_pen_start_dt,
898 x_s_encmb_effect_type,
899 x_pee_start_dt,
900 x_pee_sequence_number,
901 x_fund_code,
902 x_pfe_start_dt,
903 x_expiry_dt,
904 x_mode
905 );
906 RETURN;
907 END IF;
908 CLOSE c1;
909
910 update_row (
911 x_rowid,
912 x_fund_excl_id,
913 x_person_id,
914 x_encumbrance_type,
915 x_pen_start_dt,
916 x_s_encmb_effect_type,
917 x_pee_start_dt,
918 x_pee_sequence_number,
919 x_fund_code,
920 x_pfe_start_dt,
921 x_expiry_dt,
922 x_mode
923 );
924
925 END add_row;
926
927
928 PROCEDURE delete_row (
929 x_rowid IN VARCHAR2
930 ) AS
931 /*
932 || Created By : prabhat.patel
933 || Created On : 09-OCT-2002
934 || Purpose : Handles the DELETE DML logic for the table.
935 || Known limitations, enhancements or remarks :
936 || Change History :
937 || Who When What
938 || (reverse chronological order - newest change first)
939 */
940 BEGIN
941
942 before_dml (
943 p_action => 'DELETE',
944 x_rowid => x_rowid
945 );
946
947 DELETE FROM igs_pe_fund_excl
948 WHERE rowid = x_rowid;
949
950 IF (SQL%NOTFOUND) THEN
951 RAISE NO_DATA_FOUND;
952 END IF;
953
954 END delete_row;
955
956
957 END igs_pe_fund_excl_pkg;
[Home] [Help]