1 PACKAGE BODY IGS_PR_VAL_SCA AS
2 /* $Header: IGSPR08B.pls 115.5 2002/11/29 02:45:39 nsidana ship $ */
3 /*
4 || Bug ID 1956374 - Removal of Duplicate Program Units from OSS.
5 || Removed program unit (PRGP_VAL_SCA_CMPLT) - from the spec and body. -- kdande
6 */
7 --
8 -- Validate the Student IGS_PS_UNIT Set Attempts.
9 FUNCTION prgp_val_susa_cmplt(
10 p_person_id IN NUMBER ,
11 p_course_cd IN VARCHAR2 ,
12 p_message_name OUT NOCOPY VARCHAR2 )
13 RETURN BOOLEAN AS
14 gv_other_detail VARCHAR2(255);
15 BEGIN -- prgp_val_susa_cmplt
16 -- Validate IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind
17 -- and the IGS_PS_STDNT_APV_ALT.rqrmnts_complete_ind
18 -- * Cannot be set if any IGS_AS_SU_SETATMPT records
19 -- are incomplete or unended for the IGS_PS_COURSE attempt.
20 DECLARE
21 v_susa_rec_found VARCHAR2(1);
22 CURSOR c_susa IS
23 SELECT 'x'
24 FROM IGS_AS_SU_SETATMPT susa
25 WHERE susa.person_id = p_person_id AND
26 susa.course_cd = p_course_cd AND
27 susa.student_confirmed_ind = 'Y' AND
28 susa.rqrmnts_complete_ind = 'N' AND
29 susa.end_dt IS NULL;
30 BEGIN
31 p_message_name := null;
32 -- Check parameters.
33 IF p_person_id IS NULL OR
34 p_course_cd IS NULL THEN
35 RETURN TRUE;
36 END IF;
37 -- Check if any IGS_AS_SU_SETATMPT records are incomplete
38 -- or unended.
39 OPEN c_susa;
40 FETCH c_susa INTO v_susa_rec_found;
41 IF c_susa%FOUND THEN
42 CLOSE c_susa;
43 p_message_name := 'IGS_PR_CANT_SET_COMPL_IND';
44 RETURN FALSE;
45 END IF;
46 CLOSE c_susa;
47 -- Return no error.
48 RETURN TRUE;
49 EXCEPTION
50 WHEN OTHERS THEN
51 IF c_susa%ISOPEN THEN
52 CLOSE c_susa;
53 END IF;
54 RAISE;
55 END;
56 EXCEPTION
57 WHEN OTHERS THEN
58 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
59 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_SUSA_CMPLT');
60 --IGS_GE_MSG_STACK.ADD;
61
62 END prgp_val_susa_cmplt;
63 --
64 -- Validate the Student IGS_PS_COURSE Attempt Status.
65 FUNCTION prgp_val_sca_status(
66 p_person_id IN NUMBER ,
67 p_course_cd IN VARCHAR2 ,
68 p_message_name OUT NOCOPY VARCHAR2 )
69 RETURN BOOLEAN AS
70 gv_other_detail VARCHAR2(255);
71 BEGIN -- prgp_val_sca_status
72 -- Validate IGS_EN_STDNT_PS_ATT.course_attempt_status when setting the
73 --student_course_attepmt.course_rqrmnt_complete_ind or the
74 --IGS_PS_STDNT_APV_ALT.rqrmnts_complete_ind.
75 --? Cannot be set if course_attempt_status is 'COMPLETED' or 'UNCONFIRM'.
76 --
77 -- Modified 28/01/99 to only test for UNCONFIRM (Greg White).
78 DECLARE
79 cst_unconfirm CONSTANT
80 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE := 'UNCONFIRM';
81 v_sca_course_attempt_status
82 IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
83 CURSOR c_sca IS
84 SELECT sca.course_attempt_status
85 FROM IGS_EN_STDNT_PS_ATT sca
86 WHERE sca.person_id = p_person_id And
87 sca.course_cd = p_course_cd And
88 sca.course_attempt_status = cst_unconfirm;
89 BEGIN
90 -- Set the default message number
91 p_message_name := null;
92 --1. Check parameters :
93 IF p_person_id IS NULL OR
94 p_course_cd IS NULL THEN
95 RETURN TRUE;
96 END IF;
97 --2. Get the IGS_PS_COURSE attempt status.
98 OPEN c_sca;
99 FETCH c_sca INTO v_sca_course_attempt_status;
100 IF c_sca%FOUND THEN
101 CLOSE c_sca;
102 p_message_name := 'IGS_PR_CANNOT_SET_COMPL_IND';
103 RETURN FALSE;
104 END IF;
105 CLOSE c_sca;
106 -- Return the default value
107 RETURN TRUE;
108 EXCEPTION
109 WHEN OTHERS THEN
110 IF c_sca%ISOPEN THEN
111 CLOSE c_sca;
112 END IF;
113 RAISE;
114 END;
115 EXCEPTION
116 WHEN OTHERS THEN
117 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
118 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_SCA_STATUS');
119 --IGS_GE_MSG_STACK.ADD;
120
121 END prgp_val_sca_status;
122 --
123 -- Validate the Student IGS_PS_COURSE complete indicator.
124 FUNCTION prgp_val_undo_cmpltn(
125 p_person_id IN NUMBER ,
126 p_course_cd IN VARCHAR2 ,
127 p_version_number IN NUMBER ,
128 p_exit_course_cd IN VARCHAR2 ,
129 p_exit_version_number IN NUMBER ,
130 p_message_name OUT NOCOPY VARCHAR2 )
131 RETURN BOOLEAN AS
132 gv_other_detail VARCHAR2(255);
133 BEGIN -- prgp_val_undo_cmpltn
134 DECLARE
135 v_gr_graduand_status IGS_GR_GRADUAND.graduand_status%TYPE;
136 cst_graduated CONSTANT IGS_GR_STAT.s_graduand_status%TYPE := 'GRADUATED';
137 cst_surrender CONSTANT IGS_GR_STAT.s_graduand_status%TYPE := 'SURRENDER';
138 CURSOR c_gr IS
139 SELECT gr.graduand_status
140 FROM IGS_GR_GRADUAND gr,
141 IGS_GR_STAT gst
142 WHERE gr.person_id = p_person_id AND
143 gr.course_cd = p_course_cd AND
144 gr.award_course_cd = p_course_cd AND
145 gr.award_crs_version_number = p_version_number AND
146 gr.graduand_status = gst.graduand_status AND
147 gst.s_graduand_status IN (
148 cst_graduated,
149 cst_surrender);
150 CURSOR c_gr1 IS
151 SELECT gr.graduand_status
152 FROM IGS_GR_GRADUAND gr,
153 IGS_GR_STAT gst
154 WHERE gr.person_id = p_person_id AND
155 gr.course_cd = p_course_cd AND
156 gr.award_course_cd = p_exit_course_cd AND
157 gr.award_crs_version_number = p_exit_version_number AND
158 gr.graduand_status = gst.graduand_status AND
159 gst.s_graduand_status IN (
160 cst_graduated,
161 cst_surrender);
162 BEGIN
163 -- Set the default message number
164 p_message_name := null;
165 -- 1. Check mandatory parameters :
166 IF p_person_id IS NULL OR
167 p_course_cd IS NULL OR
168 p_version_number IS NULL THEN
169 RETURN TRUE;
170 END IF;
171 IF p_exit_course_cd IS NULL THEN
172 OPEN c_gr;
173 FETCH c_gr INTO v_gr_graduand_status;
174 IF c_gr%FOUND THEN
175 CLOSE c_gr;
176 p_message_name := 'IGS_PR_CANNOT_CLEA_COMPL_IND';
177 RETURN FALSE;
178 END IF;
179 CLOSE c_gr;
180 END IF;
181 IF p_exit_course_cd IS NOT NULL THEN
182 OPEN c_gr1;
183 FETCH c_gr1 INTO v_gr_graduand_status;
184 IF c_gr1%FOUND THEN
185 CLOSE c_gr1;
186 p_message_name := 'IGS_PR_CANNOT_CLEA_COMPL_IND';
187 RETURN FALSE;
188 END IF;
189 CLOSE c_gr1;
190 END IF;
191 -- Return the default value
192 RETURN TRUE;
193 EXCEPTION
194 WHEN OTHERS THEN
195 IF c_gr%ISOPEN THEN
196 CLOSE c_gr;
197 END IF;
198 IF c_gr1%ISOPEN THEN
199 CLOSE c_gr1;
200 END IF;
201 RAISE;
202 END;
203 EXCEPTION
204 WHEN OTHERS THEN
205 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
206 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_UNDO_CMPLTN');
207 --IGS_GE_MSG_STACK.ADD;
208
209 END prgp_val_undo_cmpltn;
210 --
211 -- Validate the Student IGS_PS_COURSE complete indicator.
212 FUNCTION prgp_val_cmplt_ind(
213 p_person_id IN NUMBER ,
214 p_course_cd IN VARCHAR2 ,
215 p_course_complete_ind IN VARCHAR2 ,
216 p_exit_course_complete_ind IN VARCHAR2,
217 p_call_type IN VARCHAR2 ,
218 p_message_name OUT NOCOPY VARCHAR2 )
219 RETURN BOOLEAN AS
220 gv_other_detail VARCHAR2(255);
221 BEGIN -- prgp_val_cmplt_ind
222 -- Validate IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind and the
223 -- IGS_PS_STDNT_APV_ALT.rqrmnts_complete_ind.
224 --* Both cannot be set for the same IGS_PS_COURSE attempt
225 DECLARE
226 cst_course CONSTANT VARCHAR2(11) := 'COURSE';
227 cst_exit_course CONSTANT VARCHAR2(11) := 'EXIT_COURSE';
228 v_scaae_rec IGS_PS_STDNT_APV_ALT.rqrmnts_complete_ind%TYPE;
229 v_sca_rec IGS_EN_STDNT_PS_ATT.course_rqrmnt_complete_ind%TYPE;
230 CURSOR c_scaae IS
231 SELECT 'X'
232 FROM IGS_PS_STDNT_APV_ALT scaae
233 WHERE scaae.person_id = p_person_id AND
234 scaae.course_cd = p_course_cd AND
235 scaae.rqrmnts_complete_ind = 'Y';
236 CURSOR c_sca IS
237 SELECT 'X'
238 FROM IGS_EN_STDNT_PS_ATT sca
239 WHERE sca.person_id = p_person_id AND
240 sca.course_cd = p_course_cd AND
241 sca.course_rqrmnt_complete_ind = 'Y';
242 BEGIN
243 -- Set the default message number
244 p_message_name := null;
245 --1. Check parameters :
246 IF p_person_id IS NULL OR
247 p_course_cd IS NULL OR
248 p_call_type IS NULL THEN
249 RETURN TRUE;
250 END IF;
251 IF p_call_type = cst_course THEN
252 IF p_course_complete_ind = 'Y' THEN
253 OPEN c_scaae;
254 FETCH c_scaae INTO v_scaae_rec;
255 IF (c_scaae%FOUND) THEN
256 CLOSE c_scaae;
257 p_message_name := 'IGS_PR_COMPL_IND_SET_ALT_EXIT';
258 RETURN FALSE;
259 END IF;
260 CLOSE c_scaae;
261 END IF;
262 END IF;
263 IF p_call_type = cst_exit_course THEN
264 IF p_exit_course_complete_ind = 'Y' THEN
265 OPEN c_sca;
266 FETCH c_sca INTO v_sca_rec;
267 IF (c_sca%FOUND) THEN
268 CLOSE c_sca;
269 p_message_name := 'IGS_PR_COMPL_IND_SET_COUR_AT';
270 RETURN FALSE;
271 END IF;
272 CLOSE c_sca;
273 END IF;
274 END IF;
275 RETURN TRUE;
276 EXCEPTION
277 WHEN OTHERS THEN
278 IF (c_scaae%ISOPEN) THEN
279 CLOSE c_scaae;
280 END IF;
281 IF (c_sca%ISOPEN) THEN
282 CLOSE c_sca;
283 END IF;
284 RAISE;
285 END;
286 EXCEPTION
287 WHEN OTHERS THEN
288 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
289 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_CMPLT_IND');
290 --IGS_GE_MSG_STACK.ADD;
291
292 END prgp_val_cmplt_ind;
293 --
294 -- Validate that rqrmnts complete dt and source set if IGS_PS_COURSE complete.
295 FUNCTION prgp_val_sca_crcd(
296 p_course_rqrmnt_complete_ind IN VARCHAR2,
297 p_course_rqrmnts_complete_dt IN DATE ,
298 p_s_completed_source_type IN VARCHAR2 ,
299 p_message_name OUT NOCOPY VARCHAR2 )
300 RETURN BOOLEAN AS
301 gv_other_detail VARCHAR2(255);
302 BEGIN -- prgp_val_sca_crcd
303 -- This module validates that if the indicator is set the IGS_PS_COURSE
304 -- requirements complete date and completion source are set.
305 DECLARE
306 BEGIN
307 IF p_course_rqrmnt_complete_ind = 'Y' THEN
308 IF p_course_rqrmnts_complete_dt IS NULL OR
309 p_s_completed_source_type IS NULL THEN
310 p_message_name := 'IGS_PR_SET_CRS_REQ_COMPL_SORC';
311 RETURN FALSE;
312 END IF;
313 ELSE
314 IF p_course_rqrmnts_complete_dt IS NOT NULL OR
315 p_s_completed_source_type IS NOT NULL THEN
316 p_message_name := 'IGS_PR_CANT_SET_CRS_REQ_SORCE';
317 RETURN FALSE;
318 END IF;
319 END IF;
320 p_message_name := null;
324 WHEN OTHERS THEN
321 RETURN TRUE;
322 END;
323 EXCEPTION
325 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
326 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_SCA_CRCD');
327 --IGS_GE_MSG_STACK.ADD;
328
329 END prgp_val_sca_crcd;
330 --
331 -- To validate the IGS_EN_STDNT_PS_ATT.course_rqrmnts_complete_dt
332 FUNCTION prgp_val_sca_cmpl_dt(
333 p_person_id IN NUMBER,
334 p_course_cd IN VARCHAR2,
335 p_commencement_dt IN DATE,
336 p_course_rqrmnts_complete_dt IN DATE,
337 p_message_name OUT NOCOPY VARCHAR2,
338 p_legacy IN VARCHAR2 )
339 RETURN BOOLEAN AS
340 /*----------------------------------------------------------------------------
341 || Created By :
342 || Created On :
343 || Purpose : Validate the IGS_EN_STDNT_PS_ATT.course_rqrmnts_complete_dt,
344 || checking for:
345 || cannot be a future date
346 || cannot be a future date
347 || cannot pre-date the IGS_EN_STDNT_PS_ATT.commencement_dt
348 || warn if pre-dates the outcome date of the students last enteredn outcome
349 || Known limitations, enhancements or remarks :
350 || Change History :
351 || Who When What
352 || kkillams 11-11-2002 Modified function logic due to addition of new parameter p_legacy
353 || if p_legacy set to 'Y' then error message should be concatenated instead of
354 || return the function in the normal way. Else function should behave in
355 || normal way.Legacy Build Bug no: 2661533
356 ------------------------------------------------------------------------------*/
357 gv_other_detail VARCHAR2(255);
358 BEGIN
359 DECLARE
360 v_max_outcome_dt IGS_AS_SUAO_V.outcome_dt%TYPE;
361 CURSOR c_suaov IS
362 SELECT MAX(suaov.outcome_dt)
363 FROM IGS_AS_SUAO_V suaov
364 WHERE suaov.person_id = p_person_id AND
365 suaov.course_cd = p_course_cd AND
366 suaov.finalised_outcome_ind = 'Y';
367 BEGIN
368 p_message_name := null;
369 IF p_course_rqrmnts_complete_dt IS NOT NULL THEN
370 IF p_course_rqrmnts_complete_dt > TRUNC(SYSDATE) THEN
371 IF p_legacy <> 'Y' THEN
372 p_message_name := 'IGS_PR_CHK_COMPL_DATE';
373 RETURN FALSE;
374 ELSE
375 p_message_name := 'IGS_PR_CHK_COMPL_DATE';
376 END IF;
377 END IF;
378 IF p_course_rqrmnts_complete_dt < p_commencement_dt THEN
379 IF p_legacy <> 'Y' THEN
380 p_message_name := 'IGS_PR_CHK_COURS_COMPL_DT';
381 RETURN FALSE;
382 ELSE
383 IF p_message_name IS NULL THEN
384 p_message_name := 'IGS_PR_CHK_COURS_COMPL_DT';
385 ELSE
386 p_message_name := p_message_name||'*'||'IGS_PR_CHK_COURS_COMPL_DT';
387 END IF;
388 END IF;
389 END IF;
390 IF p_legacy <> 'Y' THEN
391 OPEN c_suaov;
392 FETCH c_suaov INTO v_max_outcome_dt;
393 CLOSE c_suaov;
394 IF p_course_rqrmnts_complete_dt < v_max_outcome_dt THEN
395 -- Warning only
396 p_message_name := 'IGS_PR_CHECK_CRS_COMPL_DT';
397 END IF;
398 END IF;
399 END IF;
400 RETURN TRUE;
401 EXCEPTION
402 WHEN OTHERS THEN
403 IF (c_suaov%ISOPEN) THEN
404 CLOSE c_suaov;
405 END IF;
406 RAISE;
407 END;
408 EXCEPTION
409 WHEN OTHERS THEN
410 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
411 FND_MESSAGE.SET_TOKEN ('NAME', 'IGS_PR_VAL_SCA.PRGP_VAL_SCA_CMPL_DT');
412 --IGS_GE_MSG_STACK.ADD;
413 END prgp_val_sca_cmpl_dt;
414 END IGS_PR_VAL_SCA;
[Home] [Help]