[Home] [Help]
PACKAGE BODY: APPS.IGS_PE_PERSENC_EFFCT_PKG
Source
1 package body IGS_PE_PERSENC_EFFCT_PKG AS
2 /* $Header: IGSNI16B.pls 115.9 2003/02/25 05:14:20 npalanis ship $ */
3
4 l_rowid VARCHAR2(25);
5
6 old_references IGS_PE_PERSENC_EFFCT%RowType;
7
8 new_references IGS_PE_PERSENC_EFFCT%RowType;
9
10
11
12 PROCEDURE Set_Column_Values (
13
14 p_action IN VARCHAR2,
15
16 x_rowid IN VARCHAR2,
17
18 x_person_id IN NUMBER,
19
20 x_encumbrance_type IN VARCHAR2,
21
22 x_pen_start_dt IN DATE,
23
24 x_s_encmb_effect_type IN VARCHAR2,
25
26 x_pee_start_dt IN DATE,
27
28 x_sequence_number IN NUMBER,
29
30 x_expiry_dt IN DATE,
31
32 x_course_cd IN VARCHAR2,
33
34 x_restricted_enrolment_cp IN NUMBER,
35
36 x_restricted_attendance_type IN VARCHAR2,
37
38 x_creation_date IN DATE,
39
40 x_created_by IN NUMBER,
41
42 x_last_update_date IN DATE,
43
44 x_last_updated_by IN NUMBER,
45
46 x_last_update_login IN NUMBER
47
48 ) AS
49
50
51
52 CURSOR cur_old_ref_values IS
53
54 SELECT *
55
56 FROM IGS_PE_PERSENC_EFFCT
57
58 WHERE rowid = x_rowid;
59
60
61
62 BEGIN
63
64
65
66 l_rowid := x_rowid;
67
68
69
70 -- Code for setting the Old and New Reference Values.
71
72 -- Populate Old Values.
73
74 Open cur_old_ref_values;
75
76 Fetch cur_old_ref_values INTO old_references;
77
78 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
79
80 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
81 IGS_GE_MSG_STACK.ADD;
82
83
84 Close cur_old_ref_values;
85 App_Exception.Raise_Exception;
86 Return;
87
88 END IF;
89
90 Close cur_old_ref_values;
91
92
93
94 -- Populate New Values.
95
96 new_references.person_id := x_person_id;
97
98 new_references.encumbrance_type := x_encumbrance_type;
99
100 new_references.pen_start_dt := x_pen_start_dt;
101
102 new_references.s_encmb_effect_type := x_s_encmb_effect_type;
103
104 new_references.pee_start_dt := x_pee_start_dt;
105
106 new_references.sequence_number := x_sequence_number;
107
108 new_references.expiry_dt := x_expiry_dt;
109
110 new_references.course_cd := x_course_cd;
111
112 new_references.restricted_enrolment_cp := x_restricted_enrolment_cp;
113
114 new_references.restricted_attendance_type := x_restricted_attendance_type;
115
116 IF (p_action = 'UPDATE') THEN
117
118 new_references.creation_date := old_references.creation_date;
119
120 new_references.created_by := old_references.created_by;
121
122 ELSE
123
124 new_references.creation_date := x_creation_date;
125
126 new_references.created_by := x_created_by;
127
128 END IF;
129
130 new_references.last_update_date := x_last_update_date;
131
132 new_references.last_updated_by := x_last_updated_by;
133
134 new_references.last_update_login := x_last_update_login;
135
136
137
138 END Set_Column_Values;
139
140
141
142 PROCEDURE BeforeRowInsertUpdate1(
143
144 p_inserting IN BOOLEAN,
145
146 p_updating IN BOOLEAN,
147
148 p_deleting IN BOOLEAN
149
150 ) AS
151
152 CURSOR cur_hold_ovr IS
153 SELECT HOLD_OLD_END_DT
154 FROM igs_pe_hold_rel_ovr HOVR, IGS_EN_ELGB_OVR_ALL OVR
155 WHERE OVR.ELGB_OVERRIDE_ID =HOVR.ELGB_OVERRIDE_ID AND
156 OVR.PERSON_ID = new_references.PERSON_ID AND new_references.pee_start_dt = HOVR.START_DATE
157 AND new_references.encumbrance_typE = HOVR.HOLD_TYPE;
158
159 l_hold_old_end_date DATE;
160 v_message_name varchar2(30);
161
162 BEGIN
163
164 -- Validate ENCUMBRANCE EFFECT TYPE.
165
166 -- Closed indicator.
167
168 IF p_inserting OR (p_updating AND (old_references.s_encmb_effect_type <> new_references.s_encmb_effect_type)) THEN
169
170 IF IGS_EN_VAL_ETDE.enrp_val_seet_closed (
171
172 new_references.s_encmb_effect_type,
173
174 v_message_name ) = FALSE THEN
175
176 Fnd_Message.Set_Name('IGS', v_message_name);
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179
180 END IF;
181
182 END IF;
183
184 -- Validate that start date is not less than the current date.
185
186 IF (new_references.pee_start_dt IS NOT NULL) THEN
187
188 IF p_inserting OR (p_updating AND
189
190 (NVL(old_references.pee_start_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
191
192 <> new_references.pee_start_dt)) THEN
193
194 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
195
196 new_references.pee_start_dt,
197
198 v_message_name ) = FALSE THEN
199
200 Fnd_Message.Set_Name('IGS', v_message_name);
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203
204 END IF;
205
206 END IF;
207
208 END IF;
209
210 -- Validate that start date is not less than the parent IGS_PE_PERSON
211
212 -- Encumbrance start date.
213
214 IF p_inserting THEN
215
216 IF IGS_EN_VAL_PCE.enrp_val_encmb_dts (
217
218 new_references.pen_start_dt,
219
220 new_references.pee_start_dt,
221
222 v_message_name ) = FALSE THEN
223
224 Fnd_Message.Set_Name('IGS', v_message_name);
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227
228 END IF;
229
230 END IF;
231
232 -- Validate that if expiry date is specified, then expiry date is not
233
234 -- less than the start date.
235
236 OPEN cur_hold_ovr;
237 FETCH cur_hold_ovr INTO l_hold_old_end_date;
238 IF cur_hold_ovr%NOTFOUND THEN
239 l_hold_old_end_date := new_references.expiry_dt+1;
240 END IF;
241 CLOSE cur_hold_ovr;
242
243 IF new_references.expiry_dt <> l_hold_old_end_date THEN
244 IF (new_references.expiry_dt IS NOT NULL) THEN
245
246 IF p_inserting OR (p_updating AND
247
248 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
249
250 <> new_references.expiry_dt)) THEN
251
252 IF IGS_EN_VAL_PCE.enrp_val_strt_exp_dt (
253
254 new_references.pee_start_dt,
255
256 new_references.expiry_dt,
257
258 v_message_name ) = FALSE THEN
259
260 Fnd_Message.Set_Name('IGS', v_message_name);
261 IGS_GE_MSG_STACK.ADD;
262 App_Exception.Raise_Exception;
263
264 END IF;
265 IF IGS_EN_VAL_PCE.enrp_val_encmb_dt (
266
267 new_references.expiry_dt,
268
269 v_message_name ) = FALSE THEN
270
271 Fnd_Message.Set_Name('IGS', v_message_name);
272 IGS_GE_MSG_STACK.ADD;
273 App_Exception.Raise_Exception;
274 END IF;
275
276 END IF;
277 END IF;
278 END IF;
279
280 -- Validate that if the encumbrance effect type applies to a IGS_PS_COURSE, that the
281
282 -- IGS_PE_PERSON is enrolled in a IGS_PS_COURSE.
283
284 IF p_inserting THEN
285
286 IF IGS_EN_VAL_PEE.enrp_val_pee_enrol (
287
288 new_references.person_id,
289
290 new_references.s_encmb_effect_type,
291
292 v_message_name ) = FALSE THEN
293
294 Fnd_Message.Set_Name('IGS', v_message_name);
295 IGS_GE_MSG_STACK.ADD;
296 App_Exception.Raise_Exception;
297
298 END IF;
299
300 END IF;
301
302 -- Validate IGS_PE_PERSON has an enrolment in the nominated IGS_PS_COURSE code.
303
304 IF (p_inserting OR p_updating) AND
305
306 (new_references.course_cd IS NOT NULL) AND
307
308 (NVL(old_references.course_cd, 'NULL') <> new_references.course_cd) THEN
309
310 IF IGS_EN_VAL_PEE.enrp_val_pee_crs (
311
312 new_references.person_id,
313
314 new_references.course_cd,
315
316 v_message_name ) = FALSE THEN
317
318 Fnd_Message.Set_Name('IGS', 'IGS_EN_CAN_SPEC_RESTR_ATT');
319 IGS_GE_MSG_STACK.ADD;
320 App_Exception.Raise_Exception;
321
322 END IF;
323
324 END IF;
325
326 -- Validate that restricted attendance type can be specified for the
327
328 -- encumbrance effect type.
329
330 IF (p_inserting OR p_updating) AND
331
332 (new_references.restricted_attendance_type IS NOT NULL) AND
333
334 (new_references.s_encmb_effect_type <> 'RSTR_AT_TY') THEN
335
336 Fnd_Message.Set_Name('IGS', v_message_name);
337 IGS_GE_MSG_STACK.ADD;
338 App_Exception.Raise_Exception;
339 END IF;
340
341 -- Validate that restricted attendance type is not closed.
342
343 IF new_references.restricted_attendance_type IS NOT NULL AND
344
345 (NVL(old_references.restricted_attendance_type, 'NULL') <>
346
347 new_references.restricted_attendance_type) THEN
348
349 IF IGS_EN_VAL_PEE.enrp_val_att_closed (
350
351 new_references.restricted_attendance_type,
352
353 v_message_name ) = FALSE THEN
354
355 Fnd_Message.Set_Name('IGS', v_message_name);
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 END IF;
359
360 END IF;
361
362 -- Validate IGS_PE_PERSON does not already have an attendance type restriction on the
363
364 -- IGS_PS_COURSE.
365
366 IF (p_inserting OR p_updating) AND
367
368 (new_references.course_cd IS NOT NULL) AND
369
370 (NVL(old_references.course_cd, 'NULL') <> new_references.course_cd) AND
371
372 (new_references.s_encmb_effect_type = 'RSTR_AT_TY') THEN
373
374 IF IGS_EN_VAL_PEE.enrp_val_pee_crs_att (
375
376 new_references.person_id,
377
378 new_references.s_encmb_effect_type,
379
380 nvl(new_references.sequence_number,0),
381
382 new_references.course_cd,
383
384 v_message_name ) = FALSE THEN
385
386 Fnd_Message.Set_Name('IGS', v_message_name);
387 IGS_GE_MSG_STACK.ADD;
388 App_Exception.Raise_Exception;
389
390 END IF;
391
392 END IF;
393
394 -- Validate that restricted enrolment load can be specified for the encumbrance
395
396 -- effect type.
397
398 IF (p_inserting OR p_updating) AND
399
400 (new_references.restricted_enrolment_cp > 0) AND
401
402 (new_references.s_encmb_effect_type <> 'RSTR_GE_CP' AND
403
404 new_references.s_encmb_effect_type <> 'RSTR_LE_CP') THEN
405
406 Fnd_Message.Set_Name('IGS','IGS_EN_CANT_SPEC_ENRL_CRDT');
407 IGS_GE_MSG_STACK.ADD;
408 App_Exception.Raise_Exception;
409
410 END IF;
411
412 -- Validate IGS_PE_PERSON does not already have a credit point restriction on the
413
414 -- IGS_PS_COURSE.
415
416 IF (p_inserting OR p_updating) AND
417
418 (new_references.course_cd IS NOT NULL) AND
419
420 (NVL(old_references.course_cd, 'NULL') <> new_references.course_cd) AND
421
422 (new_references.s_encmb_effect_type IN ('RSTR_LE_CP','RSTR_GE_CP')) THEN
423
424 IF IGS_EN_VAL_PEE.enrp_val_pee_crs_cp (
425
426 new_references.person_id,
427
428 new_references.s_encmb_effect_type,
429
430 nvl(new_references.sequence_number,0),
431
432 new_references.course_cd,
433
434 v_message_name ) = FALSE THEN
435
436 Fnd_Message.Set_Name('IGS', v_message_name);
437 IGS_GE_MSG_STACK.ADD;
438 App_Exception.Raise_Exception;
439
440 END IF;
441
442 END IF;
443
444
445
446
447
448 END BeforeRowInsertUpdate1;
449
450
451
452 -- Trigger description :-
453
454 -- "OSS_TST".trg_pee_ar_iu
455
456 -- AFTER INSERT OR UPDATE
457
458 -- ON IGS_PE_PERSENC_EFFCT
459
460 -- FOR EACH ROW
461
462
463
464 PROCEDURE AfterRowInsertUpdate2(
465
466 p_inserting IN BOOLEAN,
467
468 p_updating IN BOOLEAN,
469
470 p_deleting IN BOOLEAN
471
472 ) AS
473
474 v_message_name varchar2(30);
475
476 v_rowid_saved BOOLEAN := FALSE;
477
478 BEGIN
479
480 -- Validate for open ended IGS_PE_PERSON encumbrance effect records.
481
482 IF new_references.expiry_dt IS NULL THEN
483
484 -- Save the rowid of the current row.
485
486 v_rowid_saved := TRUE;
487
488 -- Cannot call enrp_val_pee_open because trigger will be mutating.
489
490 END IF;
491
492 IF p_updating AND
493
494 (NVL(old_references.expiry_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
495
496 NVL(new_references.expiry_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
497
498 -- Cannot call IGS_EN_GEN_011.ENRP_SET_PEN_EXPRY because trigger will be mutating.
499
500 -- Save the rowid of the current row.
501
502 IF v_rowid_saved = FALSE THEN
503
504 v_rowid_saved := TRUE;
505
506 END IF;
507
508 END IF;
509
510 IF v_rowid_saved = TRUE THEN
511
512 -- Based on this flag we need to do validation
513
514 -- Validate for open ended IGS_PE_PERS_ENCUMB effect records.
515
516 IF new_references.expiry_dt IS NULL THEN
517
518 IF IGS_EN_VAL_PEE.enrp_val_pee_open (
519
520 new_references.person_id,
521
522 new_references.encumbrance_type,
523
524 new_references.pen_start_dt,
525
526 new_references.s_encmb_effect_type,
527
528 new_references.sequence_number,
529
530 new_references.course_cd,
531
532 v_message_name) = FALSE THEN
533
534 Fnd_Message.Set_Name('IGS', v_message_name);
535 IGS_GE_MSG_STACK.ADD;
536 App_Exception.Raise_Exception;
537
538 END IF;
539
540 END IF;
541
542 -- Set the expiry date of the child records if the expiry date of the
543
544 -- effect has been updated.
545
546 IF new_references.expiry_dt IS NOT NULL THEN
547
548 IGS_EN_GEN_011.ENRP_SET_PEE_EXPRY (new_references.person_id,
549
550 new_references.encumbrance_type,
551
552 new_references.pen_start_dt,
553
554 new_references.s_encmb_effect_type,
555
556 new_references.pee_start_dt,
557
558 new_references.sequence_number,
559
560 new_references.expiry_dt,
561
562 v_message_name);
563
564 IF v_message_name IS NOT NULL THEN
565
566 Fnd_Message.Set_Name('IGS', v_message_name);
567 IGS_GE_MSG_STACK.ADD;
568 App_Exception.Raise_Exception;
569 END IF;
570
571 END IF;
572
573 -- Set the expiry date of the parent person encumbrance record if the expiry
574
575 -- date of the effect has been updated and no other active effect records
576
577 -- remain.
578
579 IF new_references.expiry_dt IS NOT NULL AND igs_pe_pers_encumb_pkg.initialised IS NULL THEN
580
581 IGS_EN_GEN_011.ENRP_SET_PEN_EXPRY (new_references.person_id,
582
583 new_references.encumbrance_type,
584
585 new_references.pen_start_dt,
586
587 new_references.sequence_number,
588
589 new_references.expiry_dt,
590
591 v_message_name);
592
593 --IF v_message_name <> 0 THEN
594 IF v_message_name IS NOT NULL THEN
595
596 Fnd_Message.Set_Name('IGS', v_message_name);
597 IGS_GE_MSG_STACK.ADD;
598 App_Exception.Raise_Exception;
599
600 END IF;
601
602 END IF;
603
604 END IF;
605
606
607
608 END AfterRowInsertUpdate2;
609
610
611
612 -- Trigger description :-
613
614 -- "OSS_TST".trg_pee_as_iu
615
616 -- AFTER INSERT OR UPDATE
617
618 -- ON IGS_PE_PERSENC_EFFCT
619
620
621
622
623
624
625 PROCEDURE Check_Constraints (
626 Column_Name IN VARCHAR2,
627 Column_Value IN VARCHAR2
628 )
629 AS
630 BEGIN
631 IF column_name is null then
632 NULL;
633 ELSIF upper(Column_name) = 'COURSE_CD' then
634 new_references.course_cd:= column_value;
635 ELSIF upper(Column_name) = 'ENCUMBRANCE_TYPE' then
636 new_references.encumbrance_type:= column_value;
637 ELSIF upper(Column_name) = 'RESTRICTED_ATTENDANCE_TYPE' then
638 new_references.restricted_attendance_type:= column_value;
639 ELSIF upper(Column_name) = 'S_ENCMB_EFFECT_TYPE' then
640 new_references.s_encmb_effect_type:= column_value;
641 ELSIF upper(Column_name) = 'RESTRICTED_ENROLMENT_CP' then
642 new_references.restricted_enrolment_cp := IGS_GE_NUMBER.to_num(column_value);
643 END IF;
644
645 IF upper(column_name) = 'COURSE_CD' OR
646 column_name is null Then
647 IF new_references.course_cd <> UPPER(new_references.course_cd) Then
648 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
649 IGS_GE_MSG_STACK.ADD;
650 App_Exception.Raise_Exception;
651 END IF;
652 END IF;
653
654 IF upper(column_name) = 'ENCUMBRANCE_TYPE' OR
655 column_name is null Then
656 IF new_references.encumbrance_type <>
657 UPPER(new_references.encumbrance_type) Then
658 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
659 IGS_GE_MSG_STACK.ADD;
660 App_Exception.Raise_Exception;
661 END IF;
662 END IF;
663 IF upper(column_name) = 'RESTRICTED_ATTENDANCE_TYPE' OR
664 column_name is null Then
665 IF new_references.restricted_attendance_type <>
666 UPPER(new_references.restricted_attendance_type) Then
667 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
668 IGS_GE_MSG_STACK.ADD;
669 App_Exception.Raise_Exception;
670 END IF;
671 END IF;
672 IF upper(column_name) = 'S_ENCMB_EFFECT_TYPE' OR
673 column_name is null Then
674 IF new_references.s_encmb_effect_type<>
675 UPPER(new_references.s_encmb_effect_type) Then
676 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
677 IGS_GE_MSG_STACK.ADD;
678 App_Exception.Raise_Exception;
679 END IF;
680 END IF;
681 IF upper(column_name) = 'RESTRICTED_ENROLMENT_CP' OR
682 column_name is null Then
683 IF new_references.restricted_enrolment_cp < 0 OR
684 new_references.restricted_enrolment_cp > 999.999 Then
685 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
686 IGS_GE_MSG_STACK.ADD;
687 App_Exception.Raise_Exception;
688 END IF;
689 END IF;
690
691
692 END Check_Constraints;
693
694
695
696
697
698 PROCEDURE Check_Parent_Existance AS
699
700 BEGIN
701
702
703
704 IF (((old_references.restricted_attendance_type = new_references.restricted_attendance_type)) OR
705
706 ((new_references.restricted_attendance_type IS NULL))) THEN
707
708 NULL;
709
710 ELSE
711 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
712 new_references.restricted_attendance_type
713 ) THEN
714 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
715 IGS_GE_MSG_STACK.ADD;
716 App_Exception.Raise_Exception;
717 END IF;
718
719
720 END IF;
721
722
723
724 IF (((old_references.person_id = new_references.person_id) AND
725
726 (old_references.encumbrance_type = new_references.encumbrance_type) AND
727
728 (old_references.pen_start_dt = new_references.pen_start_dt)) OR
729
730 ((new_references.person_id IS NULL) OR
731
732 (new_references.encumbrance_type IS NULL) OR
733
734 (new_references.pen_start_dt IS NULL))) THEN
735
736 NULL;
737
738 ELSE
739
740
741 IF NOT IGS_PE_PERS_ENCUMB_PKG.Get_PK_For_Validation (
742 new_references.person_id,
743 new_references.encumbrance_type,
744 new_references.pen_start_dt ) THEN
745 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
746 IGS_GE_MSG_STACK.ADD;
747 App_Exception.Raise_Exception;
748 END IF;
749
750 END IF;
751
752
753
754 IF (((old_references.s_encmb_effect_type = new_references.s_encmb_effect_type)) OR
755
756 ((new_references.s_encmb_effect_type IS NULL))) THEN
757
758 NULL;
759
760 ELSE
761
762
763 IF NOT IGS_EN_ENCMB_EFCTTYP_Pkg.Get_PK_For_Validation (
764 new_references.s_encmb_effect_type ) THEN
765 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
766 IGS_GE_MSG_STACK.ADD;
767 App_Exception.Raise_Exception;
768 END IF;
769
770 END IF;
771
772
773
774 END Check_Parent_Existance;
775
776
777
778 PROCEDURE Check_Child_Existance AS
779 /*
780 || Created By : prchandr
781 || Created On : 04-APR-2001
782 || Purpose : Checks for the existance of Child records.
783 || Known limitations, enhancements or remarks :
784 || Change History :
785 || Who When What
786 || pkpatel 04-OCT-2002 Bug NO: 2600842
787 || Added the call igs_pe_fund_excl_pkg.get_fk_igs_pe_persenc_effct
788 || (reverse chronological order - newest change first)
789 */
790 BEGIN
791
792
793
794 IGS_PE_COURSE_EXCL_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
795
796 old_references.person_id,
797
798 old_references.encumbrance_type,
799
800 old_references.pen_start_dt,
801
802 old_references.s_encmb_effect_type,
803
804 old_references.pee_start_dt,
805
806 old_references.sequence_number
807
808 );
809
810
811
812 IGS_PE_CRS_GRP_EXCL_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
813
814 old_references.person_id,
815
816 old_references.encumbrance_type,
817
818 old_references.pen_start_dt,
819
820 old_references.s_encmb_effect_type,
821
822 old_references.pee_start_dt,
823
824 old_references.sequence_number
825
826 );
827
828
829
830 IGS_PE_PERS_UNT_EXCL_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
831
832 old_references.person_id,
833
834 old_references.encumbrance_type,
835
836 old_references.pen_start_dt,
837
838 old_references.s_encmb_effect_type,
839
840 old_references.pee_start_dt,
841
842 old_references.sequence_number
843
844 );
845
846
847
848 IGS_PE_UNT_REQUIRMNT_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
849
850 old_references.person_id,
851
852 old_references.encumbrance_type,
853
854 old_references.pen_start_dt,
855
856 old_references.s_encmb_effect_type,
857
858 old_references.pee_start_dt,
859
860 old_references.sequence_number
861
862 );
863
864
865
866 IGS_PE_UNT_SET_EXCL_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
867
868 old_references.person_id,
869
870 old_references.encumbrance_type,
871
872 old_references.pen_start_dt,
873
874 old_references.s_encmb_effect_type,
875
876 old_references.pee_start_dt,
877
878 old_references.sequence_number
879
880 );
881
882 IGS_PE_FUND_EXCL_PKG.GET_FK_IGS_PE_PERSENC_EFFCT (
883
884 old_references.person_id,
885
886 old_references.encumbrance_type,
887
888 old_references.pen_start_dt,
889
890 old_references.s_encmb_effect_type,
891
892 old_references.pee_start_dt,
893
894 old_references.sequence_number
895
896 );
897
898 END Check_Child_Existance;
899
900
901
902 FUNCTION Get_PK_For_Validation (
903
904 x_person_id IN NUMBER,
905
906 x_encumbrance_type IN VARCHAR2,
907
908 x_pen_start_dt IN DATE,
909
910 x_s_encmb_effect_type IN VARCHAR2,
911
912 x_pee_start_dt IN DATE,
913
914 x_sequence_number IN NUMBER
915
916 ) RETURN BOOLEAN AS
917
918
919
920 CURSOR cur_rowid IS
921
922 SELECT rowid
923
924 FROM IGS_PE_PERSENC_EFFCT
925
926 WHERE person_id = x_person_id
927
928 AND encumbrance_type = x_encumbrance_type
929
930 AND pen_start_dt = x_pen_start_dt
931
932 AND s_encmb_effect_type = x_s_encmb_effect_type
933
934 AND pee_start_dt = x_pee_start_dt
935
936 AND sequence_number = x_sequence_number
937
938 FOR UPDATE NOWAIT;
939
940
941
942 lv_rowid cur_rowid%RowType;
943
944
945
946 BEGIN
947 Open cur_rowid;
948 Fetch cur_rowid INTO lv_rowid;
949 IF (cur_rowid%FOUND) THEN
950 Close cur_rowid;
951 Return (TRUE);
952 ELSE
953 Close cur_rowid;
954 Return (FALSE);
955 END IF;
956 END Get_PK_For_Validation;
957
958
959
960 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
961
962 x_attendance_type IN VARCHAR2
963
964 ) AS
965
966
967
968 CURSOR cur_rowid IS
969
970 SELECT rowid
971
972 FROM IGS_PE_PERSENC_EFFCT
973
974 WHERE restricted_attendance_type = x_attendance_type ;
975
976
977
978 lv_rowid cur_rowid%RowType;
979
980
981
982 BEGIN
983
984
985
986 Open cur_rowid;
987
988 Fetch cur_rowid INTO lv_rowid;
989
990 IF (cur_rowid%FOUND) THEN
991
992 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEE_ATT_FK');
993
994 IGS_GE_MSG_STACK.ADD;
995
996 Close cur_rowid;
997 App_Exception.Raise_Exception;
998 Return;
999
1000 END IF;
1001
1002 Close cur_rowid;
1003
1004
1005
1006 END GET_FK_IGS_EN_ATD_TYPE;
1007
1008
1009
1010 PROCEDURE GET_FK_IGS_PE_PERS_ENCUMB (
1011
1012 x_person_id IN NUMBER,
1013
1014 x_encumbrance_type IN VARCHAR2,
1015
1016 x_start_dt IN DATE
1017 ) AS
1018
1019
1020
1021 CURSOR cur_rowid IS
1022
1023 SELECT rowid
1024
1025 FROM IGS_PE_PERSENC_EFFCT
1026
1027 WHERE person_id = x_person_id
1028
1029 AND encumbrance_type = x_encumbrance_type
1030
1031 AND pen_start_dt = x_start_dt ;
1032
1033
1034
1035 lv_rowid cur_rowid%RowType;
1036
1037
1038
1039 BEGIN
1040
1041
1042
1043 Open cur_rowid;
1044
1045 Fetch cur_rowid INTO lv_rowid;
1046
1047 IF (cur_rowid%FOUND) THEN
1048
1049 Fnd_Message.Set_Name ('IGS', 'IGS_PE_PEE_PEN_FK');
1050
1051 IGS_GE_MSG_STACK.ADD;
1052
1053 Close cur_rowid;
1054 App_Exception.Raise_Exception;
1055 Return;
1056
1057 END IF;
1058
1059 Close cur_rowid;
1060
1061
1062
1063 END GET_FK_IGS_PE_PERS_ENCUMB;
1064
1065
1066 PROCEDURE Before_DML (
1067
1068 p_action IN VARCHAR2,
1069
1070 x_rowid IN VARCHAR2,
1071
1072 x_person_id IN NUMBER,
1073
1074 x_encumbrance_type IN VARCHAR2,
1075
1076 x_pen_start_dt IN DATE,
1077
1078 x_s_encmb_effect_type IN VARCHAR2,
1079
1080 x_pee_start_dt IN DATE,
1081
1082 x_sequence_number IN NUMBER,
1083
1084 x_expiry_dt IN DATE,
1085
1086 x_course_cd IN VARCHAR2,
1087
1088 x_restricted_enrolment_cp IN NUMBER,
1089
1090 x_restricted_attendance_type IN VARCHAR2,
1091
1092 x_creation_date IN DATE,
1093
1094 x_created_by IN NUMBER,
1095
1096 x_last_update_date IN DATE,
1097
1098 x_last_updated_by IN NUMBER,
1099
1100 x_last_update_login IN NUMBER
1101
1102 ) AS
1103
1104 BEGIN
1105
1106
1107
1108 Set_Column_Values (
1109
1110 p_action,
1111
1112 x_rowid,
1113
1114 x_person_id,
1115
1116 x_encumbrance_type,
1117
1118 x_pen_start_dt,
1119
1120 x_s_encmb_effect_type,
1121
1122 x_pee_start_dt,
1123
1124 x_sequence_number,
1125
1126 x_expiry_dt,
1127
1128 x_course_cd,
1129
1130 x_restricted_enrolment_cp,
1131
1132 x_restricted_attendance_type,
1133
1134 x_creation_date,
1135
1136 x_created_by,
1137
1138 x_last_update_date,
1139
1140 x_last_updated_by,
1141
1142 x_last_update_login
1143
1144 );
1145
1146
1147
1148 IF (p_action = 'INSERT') THEN
1149 -- Call all the procedures related to Before Insert.
1150 BeforeRowInsertUpdate1 (
1151 p_inserting => TRUE,
1152 p_updating => FALSE,
1153 p_deleting => FALSE);
1154
1155 IF Get_PK_For_Validation (
1156 new_references.person_id ,
1157 new_references.encumbrance_type ,
1158 new_references.pen_start_dt ,
1159 new_references.s_encmb_effect_type,
1160 new_references.pee_start_dt,
1161 new_references.sequence_number) THEN
1162 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1163 IGS_GE_MSG_STACK.ADD;
1164 App_Exception.Raise_Exception;
1165 END IF;
1166
1167 Check_Constraints; -- if procedure present
1168 Check_Parent_Existance; -- if procedure present
1169 ELSIF (p_action = 'UPDATE') THEN
1170 -- Call all the procedures related to Before Update.
1171
1172 BeforeRowInsertUpdate1 (
1173 p_inserting => FALSE,
1174 p_updating => TRUE,
1175 p_deleting => FALSE);
1176
1177 Check_Constraints; -- if procedure present
1178 Check_Parent_Existance; -- if procedure present
1179
1180 ELSIF (p_action = 'DELETE') THEN
1181 -- Call all the procedures related to Before Delete.
1182
1183 Check_Child_Existance; -- if procedure present
1184 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1185 IF Get_PK_For_Validation (
1186 new_references.person_id ,
1187 new_references.encumbrance_type ,
1188 new_references.pen_start_dt ,
1189 new_references.s_encmb_effect_type,
1190 new_references.pee_start_dt,
1191 new_references.sequence_number) THEN
1192 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
1193 IGS_GE_MSG_STACK.ADD;
1194 App_Exception.Raise_Exception;
1195 END IF;
1196 Check_Constraints; -- if procedure present
1197 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1198
1199 Check_Constraints; -- if procedure present
1200 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1201 Check_Child_Existance; -- if procedure present
1202 END IF;
1203
1204
1205 END Before_DML;
1206
1207
1208
1209 PROCEDURE After_DML (
1210
1211 p_action IN VARCHAR2,
1212
1213 x_rowid IN VARCHAR2
1214
1215 ) AS
1216
1217 BEGIN
1218
1219
1220
1221 l_rowid := x_rowid;
1222
1223
1224
1225 IF (p_action = 'INSERT') THEN
1226
1227 -- Call all the procedures related to After Insert.
1228
1229 AfterRowInsertUpdate2 (
1230 p_inserting => TRUE,
1231 p_updating => FALSE,
1232 p_deleting => FALSE);
1233
1234 --AfterStmtInsertUpdate3 ( p_inserting => TRUE );
1235
1236 ELSIF (p_action = 'UPDATE') THEN
1237
1238 -- Call all the procedures related to After Update.
1239
1240 AfterRowInsertUpdate2 (
1241 p_inserting => FALSE,
1242 p_updating => TRUE,
1243 p_deleting => FALSE);
1244
1245 ELSIF (p_action = 'DELETE') THEN
1246
1247 -- Call all the procedures related to After Delete.
1248
1249 Null;
1250
1251 END IF;
1252
1253
1254
1255 END After_DML;
1256
1257
1258 procedure INSERT_ROW (
1259 X_ROWID in out NOCOPY VARCHAR2,
1260 X_PERSON_ID in NUMBER,
1261 X_ENCUMBRANCE_TYPE in VARCHAR2,
1262 X_PEN_START_DT in DATE,
1263 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
1264 X_PEE_START_DT in DATE,
1265 X_SEQUENCE_NUMBER in NUMBER,
1266 X_EXPIRY_DT in DATE,
1267 X_COURSE_CD in VARCHAR2,
1268 X_RESTRICTED_ENROLMENT_CP in NUMBER,
1269 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
1270 X_MODE in VARCHAR2
1271 ) AS
1272 cursor C is select ROWID from IGS_PE_PERSENC_EFFCT
1273 where PERSON_ID = X_PERSON_ID
1274 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
1275 and PEN_START_DT = X_PEN_START_DT
1276 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
1277 and PEE_START_DT = X_PEE_START_DT
1278 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
1279 X_LAST_UPDATE_DATE DATE;
1280 X_LAST_UPDATED_BY NUMBER;
1281 X_LAST_UPDATE_LOGIN NUMBER;
1282 begin
1283 X_LAST_UPDATE_DATE := SYSDATE;
1284 if(X_MODE = 'I') then
1285 X_LAST_UPDATED_BY := 1;
1286 X_LAST_UPDATE_LOGIN := 0;
1287 elsif (X_MODE = 'R') then
1288 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1289 if X_LAST_UPDATED_BY is NULL then
1290 X_LAST_UPDATED_BY := -1;
1291 end if;
1292 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1293 if X_LAST_UPDATE_LOGIN is NULL then
1294 X_LAST_UPDATE_LOGIN := -1;
1295 end if;
1296 else
1297 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1298 IGS_GE_MSG_STACK.ADD;
1299 app_exception.raise_exception;
1300 end if;
1301
1302
1303
1304 Before_DML(
1305
1306 p_action=>'INSERT',
1307
1308 x_rowid=>X_ROWID,
1309
1310 x_course_cd=>X_COURSE_CD,
1311
1312 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
1313
1314 x_expiry_dt=>X_EXPIRY_DT,
1315
1316 x_pee_start_dt=>X_PEE_START_DT,
1317
1318 x_pen_start_dt=>X_PEN_START_DT,
1319
1320 x_person_id=>X_PERSON_ID,
1321
1322 x_restricted_attendance_type=>X_RESTRICTED_ATTENDANCE_TYPE,
1323
1324 x_restricted_enrolment_cp=> NVL(X_RESTRICTED_ENROLMENT_CP,0),
1325
1326 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
1327
1328 x_sequence_number=>X_SEQUENCE_NUMBER,
1329
1330 x_creation_date=>X_LAST_UPDATE_DATE,
1331
1332 x_created_by=>X_LAST_UPDATED_BY,
1333
1334 x_last_update_date=>X_LAST_UPDATE_DATE,
1335
1336 x_last_updated_by=>X_LAST_UPDATED_BY,
1337
1338 x_last_update_login=>X_LAST_UPDATE_LOGIN
1339
1340 );
1341
1342
1343 insert into IGS_PE_PERSENC_EFFCT (
1344 PERSON_ID,
1345 ENCUMBRANCE_TYPE,
1346 PEN_START_DT,
1347 S_ENCMB_EFFECT_TYPE,
1348 PEE_START_DT,
1349 SEQUENCE_NUMBER,
1350 EXPIRY_DT,
1351 COURSE_CD,
1352 RESTRICTED_ENROLMENT_CP,
1353 RESTRICTED_ATTENDANCE_TYPE,
1354 CREATION_DATE,
1355 CREATED_BY,
1356 LAST_UPDATE_DATE,
1357 LAST_UPDATED_BY,
1358 LAST_UPDATE_LOGIN
1359 ) values (
1360 NEW_REFERENCES.PERSON_ID,
1361 NEW_REFERENCES.ENCUMBRANCE_TYPE,
1362 NEW_REFERENCES.PEN_START_DT,
1363 NEW_REFERENCES.S_ENCMB_EFFECT_TYPE,
1364 NEW_REFERENCES.PEE_START_DT,
1365 NEW_REFERENCES.SEQUENCE_NUMBER,
1366 NEW_REFERENCES.EXPIRY_DT,
1367 NEW_REFERENCES.COURSE_CD,
1368 NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
1369 NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
1370 X_LAST_UPDATE_DATE,
1371 X_LAST_UPDATED_BY,
1372 X_LAST_UPDATE_DATE,
1373 X_LAST_UPDATED_BY,
1374 X_LAST_UPDATE_LOGIN
1375 );
1376
1377 open c;
1378 fetch c into X_ROWID;
1379 if (c%notfound) then
1380 close c;
1381 raise no_data_found;
1382 end if;
1383 close c;
1384
1385
1386 After_DML(
1387
1388 p_action => 'INSERT',
1389
1390 x_rowid => X_ROWID
1391
1392 );
1393 end INSERT_ROW;
1394
1395 procedure LOCK_ROW (
1396
1397 X_ROWID in VARCHAR2,
1398 X_PERSON_ID in NUMBER,
1399 X_ENCUMBRANCE_TYPE in VARCHAR2,
1400 X_PEN_START_DT in DATE,
1401 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
1402 X_PEE_START_DT in DATE,
1403 X_SEQUENCE_NUMBER in NUMBER,
1404 X_EXPIRY_DT in DATE,
1405 X_COURSE_CD in VARCHAR2,
1406 X_RESTRICTED_ENROLMENT_CP in NUMBER,
1407 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2
1408 ) AS
1409 cursor c1 is select
1410 EXPIRY_DT,
1411 COURSE_CD,
1412 RESTRICTED_ENROLMENT_CP,
1413 RESTRICTED_ATTENDANCE_TYPE
1414 from IGS_PE_PERSENC_EFFCT
1415 where ROWID = X_ROWID
1416 for update nowait;
1417 tlinfo c1%rowtype;
1418
1419 begin
1420 open c1;
1421 fetch c1 into tlinfo;
1422 if (c1%notfound) then
1423 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1424
1425 close c1;
1426 App_Exception.Raise_Exception;
1427 return;
1428 end if;
1429 close c1;
1430
1431 if ( ((tlinfo.EXPIRY_DT = X_EXPIRY_DT)
1432 OR ((tlinfo.EXPIRY_DT is null)
1433 AND (X_EXPIRY_DT is null)))
1434 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
1435 OR ((tlinfo.COURSE_CD is null)
1436 AND (X_COURSE_CD is null)))
1437 AND ((tlinfo.RESTRICTED_ENROLMENT_CP = X_RESTRICTED_ENROLMENT_CP)
1438 OR ((tlinfo.RESTRICTED_ENROLMENT_CP is null)
1439 AND (X_RESTRICTED_ENROLMENT_CP is null)))
1440 AND ((tlinfo.RESTRICTED_ATTENDANCE_TYPE = X_RESTRICTED_ATTENDANCE_TYPE)
1441 OR ((tlinfo.RESTRICTED_ATTENDANCE_TYPE is null)
1442 AND (X_RESTRICTED_ATTENDANCE_TYPE is null)))
1443 ) then
1444 null;
1445 else
1446 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1447 app_exception.raise_exception;
1448 end if;
1449 return;
1450 end LOCK_ROW;
1451
1452 procedure UPDATE_ROW (
1453
1454 X_ROWID in VARCHAR2,
1455 X_PERSON_ID in NUMBER,
1456 X_ENCUMBRANCE_TYPE in VARCHAR2,
1457 X_PEN_START_DT in DATE,
1458 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
1459 X_PEE_START_DT in DATE,
1460 X_SEQUENCE_NUMBER in NUMBER,
1461 X_EXPIRY_DT in DATE,
1462 X_COURSE_CD in VARCHAR2,
1463 X_RESTRICTED_ENROLMENT_CP in NUMBER,
1464 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
1465 X_MODE in VARCHAR2
1466 ) AS
1467 X_LAST_UPDATE_DATE DATE;
1468 X_LAST_UPDATED_BY NUMBER;
1469 X_LAST_UPDATE_LOGIN NUMBER;
1470 begin
1471 X_LAST_UPDATE_DATE := SYSDATE;
1472 if(X_MODE = 'I') then
1473 X_LAST_UPDATED_BY := 1;
1474 X_LAST_UPDATE_LOGIN := 0;
1475 elsif (X_MODE = 'R') then
1476 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1477 if X_LAST_UPDATED_BY is NULL then
1478 X_LAST_UPDATED_BY := -1;
1479 end if;
1480 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1481 if X_LAST_UPDATE_LOGIN is NULL then
1482 X_LAST_UPDATE_LOGIN := -1;
1483 end if;
1484 else
1485 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1486 IGS_GE_MSG_STACK.ADD;
1487 app_exception.raise_exception;
1488 end if;
1489
1490
1491
1492 Before_DML(
1493
1494 p_action=>'UPDATE',
1495
1496 x_rowid=>X_ROWID,
1497
1498 x_course_cd=>X_COURSE_CD,
1499
1500 x_encumbrance_type=>X_ENCUMBRANCE_TYPE,
1501
1502 x_expiry_dt=>X_EXPIRY_DT,
1503
1504 x_pee_start_dt=>X_PEE_START_DT,
1505
1506 x_pen_start_dt=>X_PEN_START_DT,
1507
1508 x_person_id=>X_PERSON_ID,
1509
1510 x_restricted_attendance_type=>X_RESTRICTED_ATTENDANCE_TYPE,
1511
1512 x_restricted_enrolment_cp=>X_RESTRICTED_ENROLMENT_CP,
1513
1514 x_s_encmb_effect_type=>X_S_ENCMB_EFFECT_TYPE,
1515
1516 x_sequence_number=>X_SEQUENCE_NUMBER,
1517
1518 x_creation_date=>X_LAST_UPDATE_DATE,
1519
1520 x_created_by=>X_LAST_UPDATED_BY,
1521
1522 x_last_update_date=>X_LAST_UPDATE_DATE,
1523
1524 x_last_updated_by=>X_LAST_UPDATED_BY,
1525
1526 x_last_update_login=>X_LAST_UPDATE_LOGIN
1527
1528 );
1529
1530
1531 update IGS_PE_PERSENC_EFFCT set
1532 EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
1533 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1534 RESTRICTED_ENROLMENT_CP = NEW_REFERENCES.RESTRICTED_ENROLMENT_CP,
1535 RESTRICTED_ATTENDANCE_TYPE = NEW_REFERENCES.RESTRICTED_ATTENDANCE_TYPE,
1536 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1537 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1538 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
1539 where ROWID = X_ROWID
1540 ;
1541 if (sql%notfound) then
1542 raise no_data_found;
1543 end if;
1544
1545
1546
1547 After_DML(
1548
1549 p_action => 'UPDATE',
1550
1551 x_rowid => X_ROWID
1552
1553 );
1554 end UPDATE_ROW;
1555
1556 procedure ADD_ROW (
1557 X_ROWID in out NOCOPY VARCHAR2,
1558 X_PERSON_ID in NUMBER,
1559 X_ENCUMBRANCE_TYPE in VARCHAR2,
1560 X_PEN_START_DT in DATE,
1561 X_S_ENCMB_EFFECT_TYPE in VARCHAR2,
1562 X_PEE_START_DT in DATE,
1563 X_SEQUENCE_NUMBER in NUMBER,
1564 X_EXPIRY_DT in DATE,
1565 X_COURSE_CD in VARCHAR2,
1566 X_RESTRICTED_ENROLMENT_CP in NUMBER,
1567 X_RESTRICTED_ATTENDANCE_TYPE in VARCHAR2,
1568 X_MODE in VARCHAR2
1569 ) AS
1570 cursor c1 is select rowid from IGS_PE_PERSENC_EFFCT
1571 where PERSON_ID = X_PERSON_ID
1572 and ENCUMBRANCE_TYPE = X_ENCUMBRANCE_TYPE
1573 and PEN_START_DT = X_PEN_START_DT
1574 and S_ENCMB_EFFECT_TYPE = X_S_ENCMB_EFFECT_TYPE
1575 and PEE_START_DT = X_PEE_START_DT
1576 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1577 ;
1578
1579 begin
1580 open c1;
1581 fetch c1 into X_ROWID;
1582 if (c1%notfound) then
1583 close c1;
1584 INSERT_ROW (
1585 X_ROWID,
1586 X_PERSON_ID,
1587 X_ENCUMBRANCE_TYPE,
1588 X_PEN_START_DT,
1589 X_S_ENCMB_EFFECT_TYPE,
1590 X_PEE_START_DT,
1591 X_SEQUENCE_NUMBER,
1592 X_EXPIRY_DT,
1593 X_COURSE_CD,
1594 X_RESTRICTED_ENROLMENT_CP,
1595 X_RESTRICTED_ATTENDANCE_TYPE,
1596 X_MODE);
1597 return;
1598 end if;
1599 close c1;
1600 UPDATE_ROW (
1601
1602 X_ROWID,
1603 X_PERSON_ID,
1604 X_ENCUMBRANCE_TYPE,
1605 X_PEN_START_DT,
1606 X_S_ENCMB_EFFECT_TYPE,
1607 X_PEE_START_DT,
1608 X_SEQUENCE_NUMBER,
1609 X_EXPIRY_DT,
1610 X_COURSE_CD,
1611 X_RESTRICTED_ENROLMENT_CP,
1612 X_RESTRICTED_ATTENDANCE_TYPE,
1613 X_MODE);
1614 end ADD_ROW;
1615
1616 procedure DELETE_ROW (
1617 X_ROWID in VARCHAR2
1618 ) AS
1619 begin
1620
1621 Before_DML(
1622
1623 p_action => 'DELETE',
1624
1625 x_rowid => X_ROWID
1626
1627 );
1628 delete from IGS_PE_PERSENC_EFFCT
1629 where ROWID = X_ROWID;
1630 if (sql%notfound) then
1631 raise no_data_found;
1632 end if;
1633
1634 After_DML(
1635
1636 p_action => 'DELETE',
1637
1638 x_rowid => X_ROWID
1639
1640 );
1641 end DELETE_ROW;
1642
1643 end IGS_PE_PERSENC_EFFCT_PKG;