1 PACKAGE BODY IGS_EN_VAL_PCGE AS
2 /* $Header: IGSEN51B.pls 115.6 2003/05/21 10:10:50 ptandon ship $ */
3 --
4 -- Validate that IGS_PE_PERSON doesn't already have an open crs grp exclusion.
5 /*---------------------------------------------------------------------------------------------------------------------------------------
6 --Change History:
7 --Who When What
8 --ptandon 21-MAY-2003 Replaced usage of Message IGS_EN_COURSE_GRP_CLOSED with IGS_PS_PRGGRP_CODE_CLOSED. Bug#2755657
9 -----------------------------------------------------------------------------------------------------------------------------------------*/
10
11 FUNCTION enrp_val_pcge_open(
12 p_person_id IN NUMBER ,
13 p_encumbrance_type IN VARCHAR2 ,
14 p_pen_start_dt IN DATE ,
15 p_s_encmb_effect_type IN VARCHAR2 ,
16 p_pee_start_dt IN DATE ,
17 p_course_group_cd IN VARCHAR2 ,
18 p_pcge_start_dt IN DATE ,
19 p_message_name OUT NOCOPY VARCHAR2)
20 RETURN BOOLEAN AS
21
22 BEGIN -- enrp_val_pcge_open
23 -- Validate that there are no other "open ended" pcge records
24 -- for the nominated encumbrance effect type
25 DECLARE
26 v_check VARCHAR2(1);
27 v_ret_val BOOLEAN DEFAULT TRUE;
28 CURSOR c_person_crs_grp_exclusion IS
29 SELECT 'x'
30 FROM IGS_PE_CRS_GRP_EXCL
31 WHERE
32 person_id = p_person_id AND
33 encumbrance_type = p_encumbrance_type AND
34 pen_start_dt = p_pen_start_dt AND
35 s_encmb_effect_type = p_s_encmb_effect_type AND
36 pee_start_dt = p_pee_start_dt AND
37 course_group_cd = p_course_group_cd AND
38 pcge_start_dt <> p_pcge_start_dt;
39 BEGIN
40 p_message_name := null;
41 OPEN c_person_crs_grp_exclusion;
42 FETCH c_person_crs_grp_exclusion INTO v_check;
43 IF (c_person_crs_grp_exclusion%FOUND) THEN
44 -- open record already exists
45 p_message_name := 'IGS_EN_PRSN_PRGGRP_EXCLUSION';
46 v_ret_val := FALSE;
47 END IF;
48 CLOSE c_person_crs_grp_exclusion;
49 RETURN v_ret_val;
50 END;
51 EXCEPTION
52 WHEN OTHERS THEN
53 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
54 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCGE.enrp_val_pcge_open');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57
58
59 END enrp_val_pcge_open;
60
61 -- bug id : 1956374
62 -- sjadhav,28-aug-2001
63 -- removed FUNCTION enrp_val_encmb_dt
64 -- removed FUNCTION enrp_val_encmb_dts
65 -- removed enrp_val_crs_exclsn
66 --
67 --
68 -- Validate the IGS_PS_COURSE group closed indicator.
69 FUNCTION enrp_val_crs_gp_clsd(
70 p_course_group_cd IN VARCHAR2 ,
71 p_message_name OUT NOCOPY VARCHAR2)
72 RETURN BOOLEAN AS
73 BEGIN
74 DECLARE
75
76 v_closed_ind VARCHAR2(1);
77 CURSOR c_course_group_cd IS
78 SELECT closed_ind
79 FROM IGS_PS_GRP
80 WHERE course_group_cd = p_course_group_cd;
81 BEGIN
82 -- Check if the IGS_PS_COURSE group code is closed
83 p_message_name := null;
84 OPEN c_course_group_cd;
85 FETCH c_course_group_cd INTO v_closed_ind;
86 IF (c_course_group_cd%NOTFOUND) THEN
87 CLOSE c_course_group_cd;
88 RETURN TRUE;
89 END IF;
90 IF (v_closed_ind = 'Y') THEN
91 p_message_name := 'IGS_PS_PRGGRP_CODE_CLOSED';
92 CLOSE c_course_group_cd;
93 RETURN FALSE;
94 END IF;
95 -- record is not closed
96 CLOSE c_course_group_cd;
97 RETURN TRUE;
98 EXCEPTION
99 WHEN OTHERS THEN
100 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
101 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCGE.enrp_val_crs_gp_clsd');
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104
105
106 END;
107 END enrp_val_crs_gp_clsd;
108 --
109 -- Validate the IGS_PS_COURSE group on the IGS_PE_PERSON IGS_PS_COURSE group exclusion table.
110 FUNCTION enrp_val_pcge_crs_gp(
111 p_person_id IN NUMBER ,
112 p_course_group_cd IN VARCHAR2 ,
113 p_exclusion_start_dt IN DATE ,
114 p_message_name OUT NOCOPY VARCHAR2,
115 p_return_type OUT NOCOPY VARCHAR2 )
116 RETURN BOOLEAN AS
117
118 BEGIN -- enrp_val_pcge_crs_gp
119 -- Validate whether or not a IGS_PE_PERSON is enrolled in a IGS_PS_COURSE within the
120 -- specified IGS_PS_COURSE group and whether or not the IGS_PS_COURSE must be
121 -- discontinued before a IGS_PS_COURSE exclusion can be applied.
122 DECLARE
123 v_person_enrolled BOOLEAN DEFAULT FALSE;
124 v_validate_failed BOOLEAN DEFAULT FALSE;
125 CURSOR c_get_course_cd IS
126 SELECT sca.course_cd
127 FROM IGS_EN_STDNT_PS_ATT sca,
128 IGS_PS_GRP_MBR cgm
129 WHERE sca.person_id = p_person_id AND
130 sca.course_attempt_status IN
131 ('ENROLLED', 'INACTIVE', 'INTERMIT') AND
132 sca.course_cd = cgm.course_cd AND
133 cgm.course_group_cd = p_course_group_cd;
134 BEGIN
135 p_message_name := null;
136 -- Validate input parameters
137 IF (p_person_id IS NULL OR
138 p_course_group_cd IS NULL OR
139 p_exclusion_start_dt IS NULL) THEN
140 RETURN TRUE;
141 END IF;
142 -- Check if the IGS_PE_PERSON is enrolled in a IGS_PS_COURSE within the
143 -- specified IGS_PS_COURSE group
144 FOR v_rec IN c_get_course_cd LOOP
145 v_person_enrolled := TRUE;
146 -- Validate if the IGS_PS_COURSE must be discontinued before a IGS_PS_COURSE
147 -- group exclusion can be applied
148 IF (IGS_EN_VAL_PCE.enrp_val_crs_exclsn(
149 p_person_id,
150 v_rec.course_cd,
151 p_exclusion_start_dt,
152 p_message_name,
153 p_return_type) = FALSE) THEN
154 v_validate_failed := TRUE;
155 EXIT;
156 END IF;
157 END LOOP;
158 IF (v_person_enrolled = FALSE) THEN
159 RETURN TRUE;
160 END IF;
161 IF (v_validate_failed = TRUE) THEN
162 RETURN FALSE;
163 END IF;
164 RETURN TRUE;
165 END;
166 EXCEPTION
167 WHEN OTHERS THEN
168 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
169 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_PCGE.enrp_val_pcge_crs_gp');
170 IGS_GE_MSG_STACK.ADD;
171 App_Exception.Raise_Exception;
172
173
174 END enrp_val_pcge_crs_gp;
175 --
176 -- bug id : 1956374
177 -- sjadhav , 28-aug-2001
178 -- removed enrp_val_crs_exclsn
179 --
180 --
181 -- bug id : 1956374
182 -- removed FUNCTION enrp_val_encmb_dts
183 --
184 END IGS_EN_VAL_PCGE;
[Home] [Help]