1 PACKAGE BODY IGS_AD_VAL_CRS_ADMPERD AS
2 /* $Header: IGSAD51B.pls 115.4 2003/01/03 06:57:13 rghosh ship $ */
3 -- Validate the admission application course version.
4 FUNCTION admp_val_coo_crv(
5 p_course_cd IN VARCHAR2 ,
6 p_version_number IN NUMBER ,
7 p_s_admission_process_type IN VARCHAR2 ,
8 p_offer_ind IN VARCHAR2 DEFAULT 'N',
9 p_message_name OUT NOCOPY VARCHAR2)
10 RETURN BOOLEAN IS
11 BEGIN -- admp_val_coo_crv
12 -- Validate the admission application course version.
13 -- Validations are -
14 -- * If the course version is nominated then the course status must be
15 -- Active,
16 -- however, if the course version is offered the course status must be Active
17 -- * For all admission process types, with the exception of Re-Admission,
18 -- the expiry date of the course version must not be set.
19 -- * If the admission process type is Non-Award then the course version must be
20 -- a non-award course.
21 -- * If the admission process type is Transfer then the course version must not
22 -- be a generic course.
23 DECLARE
24 CURSOR c_cvcsct (
25 cp_course_cd IGS_PS_VER.course_cd%TYPE,
26 cp_version_number IGS_PS_VER.version_number%TYPE) IS
27 SELECT cs.s_course_status,
28 cv.expiry_dt,
29 cv.generic_course_ind,
30 ct.govt_course_type
31 FROM IGS_PS_VER cv,
32 IGS_PS_STAT cs,
33 IGS_PS_TYPE ct
34 WHERE course_cd = cp_course_cd AND
35 version_number = cp_version_number AND
36 cv.course_status = cs.course_status AND
37 cv.course_type = ct.course_type;
38 v_cvcsct_rec c_cvcsct%ROWTYPE;
39 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
40
41 BEGIN
42 -- Set the default message number
43 p_message_name := null;
44 -- Retrieve the course version data
45 OPEN c_cvcsct(
46 p_course_cd,
47 p_version_number);
48 FETCH c_cvcsct INTO v_cvcsct_rec;
49 IF c_cvcsct%NOTFOUND THEN
50 CLOSE c_cvcsct;
51 RETURN TRUE;
52 END IF;
53 CLOSE c_cvcsct;
54 -- Validate the course status
55 IF p_offer_ind = 'Y' THEN
56 IF v_cvcsct_rec.s_course_status <> cst_active THEN
57 p_message_name := 'IGS_AD_OFFER_PRG_MUSTBEACTIVE';
58 RETURN FALSE;
59 END IF;
60 ELSE
61 IF v_cvcsct_rec.s_course_status <> cst_active THEN
62 p_message_name := 'IGS_AD_NOMINATED_PRG_ACTVPLAN'; --removed the planned status as per bug#2722785 --rghosh
63 RETURN FALSE;
64 END IF;
65 END IF;
66 -- Validate the course version against the admission process type
67 IF p_s_admission_process_type <> 'RE-ADMIT' AND
68 v_cvcsct_rec.expiry_dt IS NOT NULL THEN
69 p_message_name := 'IGS_AD_PRCTYPE_PRG_EXPDT_SET';
70 RETURN FALSE;
71 END IF;
72 IF p_s_admission_process_type = 'TRANSFER' AND
73 v_cvcsct_rec.generic_course_ind = 'Y' THEN
74 p_message_name := 'IGS_AD_PRG_TRANSFERED_CANNOT';
75 RETURN FALSE;
76 END IF;
77 IF p_s_admission_process_type = 'NON-AWARD' AND
78 v_cvcsct_rec.govt_course_type <> 50 THEN
79 p_message_name := 'IGS_AD_PRCTYPE_NONAWARD';
80 RETURN FALSE;
81 END IF;
82 -- Return the default value
83 RETURN TRUE;
84 END;
85 END admp_val_coo_crv;
86 --
87 -- Validate the course offering option against the admission cat.
88 FUNCTION admp_val_coo_adm_cat(
89 p_course_cd IN VARCHAR2 ,
90 p_version_number IN NUMBER ,
91 p_cal_type IN VARCHAR2 ,
92 p_location_cd IN VARCHAR2 ,
93 p_attendance_mode IN VARCHAR2 ,
94 p_attendance_type IN VARCHAR2 ,
95 p_admission_cat IN VARCHAR2 ,
96 p_message_name OUT NOCOPY VARCHAR2)
97 RETURN BOOLEAN IS
98 BEGIN -- admp_val_coo_adm_cat
99 -- Validate that the course offering option of the admission application
100 -- against the admission category.
101 DECLARE
102 CURSOR c_cooac IS
103 SELECT location_cd,
104 attendance_mode,
105 attendance_type
106 FROM IGS_PS_OF_OPT_AD_CAT
107 WHERE course_cd = p_course_cd AND
108 version_number = p_version_number AND
109 cal_type = p_cal_type AND
110 admission_cat = p_admission_cat;
111 v_adm_cat_match BOOLEAN DEFAULT FALSE;
112 BEGIN
113 -- Set the default message number
114 p_message_name := null;
115 -- Course offering component of course offering option and admission
116 -- category must be set.
117 IF p_course_cd IS NULL OR
118 p_version_number IS NULL OR
119 p_cal_type IS NULL OR
120 p_admission_cat IS NULL THEN
121 p_message_name := 'IGS_AD_DETERMINE_VALID_PRG';
122 RETURN FALSE;
123 END IF;
124 -- Validate the course offering option is mapped to the admission category
125 FOR v_cooac_rec IN c_cooac LOOP
126 -- Restrict the course offering option admission category records
127 -- to match on optional input parameters.
128 IF (p_location_cd IS NULL OR
129 v_cooac_rec.location_cd = p_location_cd) AND
130 (p_attendance_mode IS NULL OR
131 v_cooac_rec.attendance_mode = p_attendance_mode) AND
132 (p_attendance_type IS NULL OR
133 v_cooac_rec.attendance_type = p_attendance_type) THEN
134 -- There is an admission category match for the course offering option.
135 v_adm_cat_match := TRUE;
136 END IF;
137 END LOOP;
138 IF v_adm_cat_match THEN
139 RETURN TRUE;
140 END IF;
141 -- If this point is reached, there must have been no admission
142 -- category match for the course offering option.
143 p_message_name := 'IGS_AD_PRGOFR_OPTION_NOTVALID';
144 RETURN FALSE;
145 END;
146 END admp_val_coo_adm_cat;
147 --
148 -- Validate course application to an admission period.
149 FUNCTION admp_val_coo_admperd(
150 p_adm_cal_type IN VARCHAR2 ,
151 p_adm_ci_sequence_number IN NUMBER ,
152 p_admission_cat IN VARCHAR2 ,
153 p_s_admission_process_type IN VARCHAR2 ,
154 p_course_cd IN VARCHAR2 ,
155 p_version_number IN NUMBER ,
156 p_acad_cal_type IN VARCHAR2 ,
157 p_location_cd IN VARCHAR2 ,
158 p_attendance_mode IN VARCHAR2 ,
159 p_attendance_type IN VARCHAR2 ,
160 p_message_name OUT NOCOPY VARCHAR2)
161 RETURN BOOLEAN IS
162 BEGIN -- admp_val_coo_admperd
163 -- Validate that the course offering option is available for entry in
164 -- the admission period
165 DECLARE
166 v_record_found BOOLEAN DEFAULT FALSE;
167 CURSOR c_apcoo_adm (
168 cp_adm_cal_type IGS_AD_PRD_PS_OF_OPT.adm_cal_type%TYPE,
169 cp_adm_ci_sequence_number
170 IGS_AD_PRD_PS_OF_OPT.adm_ci_sequence_number%TYPE,
171 cp_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE,
172 cp_s_admission_process_type
173 IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE) IS
174 SELECT 'x'
175 FROM IGS_AD_PRD_PS_OF_OPT apcoo
176 WHERE apcoo.adm_cal_type = cp_adm_cal_type AND
177 apcoo.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
178 apcoo.admission_cat = cp_admission_cat AND
179 apcoo.s_admission_process_type = cp_s_admission_process_type;
180 CURSOR c_adm_perd_course_off_opt (
181 cp_adm_cal_type IGS_AD_PRD_PS_OF_OPT.adm_cal_type%TYPE,
182 cp_adm_ci_sequence_number
183 IGS_AD_PRD_PS_OF_OPT.adm_ci_sequence_number%TYPE,
184 cp_admission_cat IGS_AD_PRD_PS_OF_OPT.admission_cat%TYPE,
185 cp_s_admission_process_type
186 IGS_AD_PRD_PS_OF_OPT.s_admission_process_type%TYPE,
187 cp_course_cd IGS_PS_OFR_OPT.course_cd%TYPE,
188 cp_version_number IGS_PS_OFR_OPT.version_number%TYPE,
189 cp_acad_cal_type IGS_PS_OFR_OPT.cal_type%TYPE) IS
190 SELECT apcoo.location_cd,
191 apcoo.attendance_mode,
192 apcoo.attendance_type
193 FROM IGS_AD_PRD_PS_OF_OPT apcoo
194 WHERE apcoo.adm_cal_type = cp_adm_cal_type AND
195 apcoo.adm_ci_sequence_number = cp_adm_ci_sequence_number AND
196 apcoo.admission_cat = cp_admission_cat AND
197 apcoo.s_admission_process_type = cp_s_admission_process_type AND
198 apcoo.course_cd = cp_course_cd AND
199 apcoo.version_number = cp_version_number AND
200 apcoo.acad_cal_type = cp_acad_cal_type;
201 BEGIN
202 p_message_name := null;
203 -- If there are no adm_perd_course_off_option for the admission period
204 -- then return TRUE
205 FOR v_apcoo_adm_rec IN c_apcoo_adm(
206 p_adm_cal_type,
207 p_adm_ci_sequence_number,
208 p_admission_cat,
209 p_s_admission_process_type) LOOP
210 v_record_found := TRUE;
211 END LOOP;
212 IF(v_record_found = FALSE) THEN
213 p_message_name := null;
214 RETURN TRUE;
215 ELSE
216 v_record_found := FALSE;
217 END IF;
218 -- Get course offering options for the admission period and course
219 FOR v_adm_perd_course_off_opt_rec IN c_adm_perd_course_off_opt(
220 p_adm_cal_type,
221 p_adm_ci_sequence_number,
222 p_admission_cat,
223 p_s_admission_process_type,
224 p_course_cd,
225 p_version_number,
226 p_acad_cal_type) LOOP
227 v_record_found := TRUE;
228 IF(v_adm_perd_course_off_opt_rec.location_cd IS NULL) THEN
229 IF(v_adm_perd_course_off_opt_rec.attendance_mode IS NULL) THEN
230 IF(v_adm_perd_course_off_opt_rec.attendance_type IS NULL OR
231 (v_adm_perd_course_off_opt_rec.attendance_type = p_attendance_type)) THEN
232 -- Valid match
233 RETURN TRUE;
234 END IF;
235 ELSE
236 IF(v_adm_perd_course_off_opt_rec.attendance_mode = p_attendance_mode AND
237 (v_adm_perd_course_off_opt_rec.attendance_type IS NULL OR
238 v_adm_perd_course_off_opt_rec.attendance_type = p_attendance_type)) THEN
239 -- Valid match
240 RETURN TRUE;
241 END IF;
242 END IF;
243 ELSE
244 IF((v_adm_perd_course_off_opt_rec.location_cd = p_location_cd) AND
245 (v_adm_perd_course_off_opt_rec.attendance_mode IS NULL OR
246 v_adm_perd_course_off_opt_rec.attendance_mode = p_attendance_mode) AND
247 (v_adm_perd_course_off_opt_rec.attendance_type IS NULL OR
248 v_adm_perd_course_off_opt_rec.attendance_type = p_attendance_type)) THEN
249 -- Valid match
250 RETURN TRUE;
251 END IF;
252 END IF;
253 END LOOP;
254 IF(v_record_found = FALSE) THEN
255 -- This course must not be valid
256 p_message_name := 'IGS_AD_PRGOFR_NO_ENTRY_POINT';
257 RETURN FALSE;
258 END IF;
259 -- No match
260 p_message_name := 'IGS_AD_PRGOFR_NO_ENTRY_POINT';
261 RETURN FALSE;
262 END;
263 END admp_val_coo_admperd;
264 END IGS_AD_VAL_CRS_ADMPERD;
[Home] [Help]