1 PACKAGE BODY IGS_EN_GET_SCAEH_DTL AS
2 /* $Header: IGSEN15B.pls 115.5 2002/02/11 13:43:21 pkm ship $ */
3
4
5 -- Get student course attempt effective history column value
6 FUNCTION enrp_get_scaeh_col(
7 p_column_name IN VARCHAR2 ,
8 p_column_value IN VARCHAR2 ,
9 p_person_id IN IGS_AS_SC_ATTEMPT_H_ALL.person_id%TYPE ,
10 p_course_cd IGS_PS_COURSE.course_cd%TYPE ,
11 p_hist_start_dt IN DATE ,
12 p_course_attempt_status VARCHAR2 )
13 RETURN VARCHAR2 AS
14 gv_other_detail VARCHAR2(255);
15 BEGIN
16 DECLARE
17 -- cursor to get the current student course attempt status
18 CURSOR c_sca (
19 cp_column_name user_tab_columns.column_name%TYPE,
20 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
21 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
22 SELECT /*+ ROWID(IGS_EN_STDNT_PS_ATT) */
23 course_attempt_status,
24 commencement_dt,
25 DECODE(cp_column_name, 'VERSION_NUMBER', TO_CHAR(version_number),
26 'CAL_TYPE', cal_type,
27 'LOCATION_CD', location_cd,
28 'ATTENDANCE_MODE', attendance_mode,
29 'ATTENDANCE_TYPE', attendance_type,
30 'STUDENT_CONFIRMED_IND', student_confirmed_ind,
31 'COMMENCEMENT_DT', IGS_GE_DATE.IGSCHARDT(commencement_dt),
32 'COURSE_ATTEMPT_STATUS', course_attempt_status,
33 'DERIVED_ATT_TYPE', derived_att_type,
34 'DERIVED_ATT_MODE', derived_att_mode,
35 'PROVISIONAL_IND', provisional_ind,
36 'DISCONTINUED_DT', IGS_GE_DATE.IGSCHARDT(discontinued_dt),
37 'DISCONTINUATION_REASON_CD', discontinuation_reason_cd,
38 'FUNDING_SOURCE', funding_source,
39 'EXAM_LOCATION_CD', exam_location_cd,
40 'DERIVED_COMPLETION_YR', TO_CHAR(derived_completion_yr),
41 'DERIVED_COMPLETION_PERD', derived_completion_perd,
42 'NOMINATED_COMPLETION_YR', TO_CHAR(nominated_completion_yr),
43 'NOMINATED_COMPLETION_PERD', nominated_completion_perd,
44 'RULE_CHECK_IND', rule_check_ind,
45 'WAIVE_OPTION_CHECK_IND', waive_option_check_ind,
46 'LAST_RULE_CHECK_DT', IGS_GE_DATE.IGSCHARDT(last_rule_check_dt),
47 'PUBLISH_OUTCOMES_IND', publish_outcomes_ind,
48 'COURSE_RQRMNT_COMPLETE_IND', course_rqrmnt_complete_ind,
49 'OVERRIDE_TIME_LIMITATION', TO_CHAR(override_time_limitation),
50 'ADVANCED_STANDING_IND', advanced_standing_ind,
51 'FEE_CAT', fee_cat,
52 'CORRESPONDENCE_CAT', correspondence_cat,
53 'SELF_HELP_GROUP_IND', self_help_group_ind,
54 'PRIMARY_PROGRAM_TYPE', primary_program_type, --Bug 2162747 by vvutukur
55 'KEY_PROGRAM', key_program) --Bug 2162747 by vvutukur
56 FROM IGS_EN_STDNT_PS_ATT
57 WHERE person_id = cp_person_id AND
58 course_cd = cp_course_cd;
59 -- cursor to get the last enrolled history
60 CURSOR c_last_e_scah (
61 cp_column_name user_tab_columns.column_name%TYPE,
62 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
63 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE) IS
64 SELECT /*+ FIRST_ROWS */
65 hist_start_dt,
66 hist_end_dt,
67 DECODE(cp_column_name, 'VERSION_NUMBER', TO_CHAR(version_number),
68 'CAL_TYPE', cal_type,
69 'LOCATION_CD', location_cd,
70 'ATTENDANCE_MODE', attendance_mode,
71 'ATTENDANCE_TYPE', attendance_type,
72 'STUDENT_CONFIRMED_IND', student_confirmed_ind,
73 'COMMENCEMENT_DT', IGS_GE_DATE.IGSCHARDT(commencement_dt),
74 'COURSE_ATTEMPT_STATUS', course_attempt_status,
75 'DERIVED_ATT_TYPE', derived_att_type,
76 'DERIVED_ATT_MODE', derived_att_mode,
77 'PROVISIONAL_IND', provisional_ind,
78 'DISCONTINUED_DT', IGS_GE_DATE.IGSCHARDT(discontinued_dt),
79 'DISCONTINUATION_REASON_CD', discontinuation_reason_cd,
80 'FUNDING_SOURCE', funding_source,
81 'EXAM_LOCATION_CD', exam_location_cd,
82 'DERIVED_COMPLETION_YR', TO_CHAR(derived_completion_yr),
83 'DERIVED_COMPLETION_PERD', derived_completion_perd,
84 'NOMINATED_COMPLETION_YR', TO_CHAR(nominated_completion_yr),
85 'NOMINATED_COMPLETION_PERD', nominated_completion_perd,
86 'RULE_CHECK_IND', rule_check_ind,
87 'WAIVE_OPTION_CHECK_IND', waive_option_check_ind,
88 'LAST_RULE_CHECK_DT', IGS_GE_DATE.IGSCHARDT(last_rule_check_dt),
89 'PUBLISH_OUTCOMES_IND', publish_outcomes_ind,
90 'COURSE_RQRMNT_COMPLETE_IND', course_rqrmnt_complete_ind,
91 'OVERRIDE_TIME_LIMITATION', TO_CHAR(override_time_limitation),
92 'ADVANCED_STANDING_IND', advanced_standing_ind,
93 'FEE_CAT', fee_cat,
94 'CORRESPONDENCE_CAT', correspondence_cat,
95 'SELF_HELP_GROUP_IND', self_help_group_ind,
96 'PRIMARY_PROGRAM_TYPE', primary_program_type,
97 'KEY_PROGRAM', key_program)
98 FROM IGS_AS_SC_ATTEMPT_H
99 WHERE person_id = cp_person_id AND
100 course_cd = cp_course_cd AND
101 course_attempt_status = 'ENROLLED'
102 ORDER BY hist_start_dt DESC;
103 v_last_hist_start_dt IGS_AS_SC_ATTEMPT_H.hist_start_dt%TYPE;
104 v_last_hist_end_dt IGS_AS_SC_ATTEMPT_H.hist_end_dt%TYPE;
105 v_current_cas IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
106 v_current_c_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
107 v_current_col_value VARCHAR2(2000);
108 v_hist_col_value VARCHAR2(2000);
109 BEGIN
110 -- get the current course attempt status
111 OPEN c_sca( p_column_name,
112 p_person_id,
113 p_course_cd);
114 FETCH c_sca INTO v_current_cas,
115 v_current_c_dt,
116 v_current_col_value;
117 CLOSE c_sca;
118 -- The following assumptions have been made;
119 -- A student course attempt with course attempt status = 'ENROLLED' can be
120 -- changed to any other status
121 -- A student course attempt with course attempt status = 'DISCONTIN' can be
122 -- changed to 'ENROLLED' only
123 -- A student course attempt with course attempt status = 'UNCONFIRM' can be
124 -- changed to 'ENROLLED'
125 IF v_current_cas = 'DISCONTIN' THEN
126 IF p_course_attempt_status = 'DISCONTIN' THEN
127 -- All prior 'DISCONTINued histories are converted to match
128 -- the current student course attempt.
129 RETURN v_current_col_value;
130 ELSE -- history is not discontinued
131 -- Cannot be effectively discontinued before being
132 -- effectively enrolled.
133 -- All histories prior to discontinuation are considered
134 -- enrolled.
135 -- Find the last enrolled history entry
136 OPEN c_last_e_scah( p_column_name,
137 p_person_id,
138 p_course_cd);
139 FETCH c_last_e_scah INTO v_last_hist_start_dt,
140 v_last_hist_end_dt,
141 v_hist_col_value;
142 IF (c_last_e_scah%NOTFOUND) THEN
143 CLOSE c_last_e_scah;
144 RETURN p_column_value;
145 ELSE
146 CLOSE c_last_e_scah;
147 IF v_hist_col_value IS NULL THEN
148 -- get the value of the first history instance
149 -- with a value for the column
150 v_hist_col_value := IGS_EN_GEN_004.ENRP_GET_SCAH_COL(
151 p_column_name,
152 p_person_id,
153 p_course_cd,
154 v_last_hist_end_dt);
155 IF v_hist_col_value IS NULL AND
156 p_column_name <> 'DISCONTINUED_DT' AND
157 p_column_name <> 'DISCONTINUATION_REASON_CD'
158 THEN
159 v_hist_col_value := v_current_col_value;
160 END IF;
161 END IF;
162 RETURN v_hist_col_value;
163 END IF;
164 END IF;
165 ELSIF v_current_cas = 'ENROLLED' THEN
166 -- All histories converted to the ENROLLED definition.
167 RETURN v_current_col_value;
168 ELSIF v_current_cas = 'UNCONFIRM' THEN
169 -- All histories converted to the UNCONFIRMed definition.
170 RETURN v_current_col_value;
171 ELSE
172 IF v_current_c_dt IS NOT NULL THEN
173 -- attempt to find the last enrolled history entry
174 OPEN c_last_e_scah( p_column_name,
175 p_person_id,
176 p_course_cd);
177 FETCH c_last_e_scah INTO v_last_hist_start_dt,
178 v_last_hist_end_dt,
179 v_hist_col_value;
180 IF (c_last_e_scah%NOTFOUND) THEN
181 CLOSE c_last_e_scah;
182 RETURN p_column_value;
183 ELSE
184 CLOSE c_last_e_scah;
185 IF p_hist_start_dt <= v_last_hist_start_dt THEN
186 -- All histories prior to and including the
187 -- last ENROLLED history are converted to the
188 -- last ENROLLED definition.
189 IF v_hist_col_value IS NULL THEN
190 -- get the value of the first history
191 -- instance with a value for the column
192 v_hist_col_value := IGS_EN_GEN_004.ENRP_GET_SCAH_COL(
193 p_column_name,
194 p_person_id,
195 p_course_cd,
196 v_last_hist_end_dt);
197 IF v_hist_col_value IS NULL AND
198 p_column_name <> 'DISCONTINUED_DT' AND
199 p_column_name <> 'DISCONTINUATION_REASON_CD'
200 THEN
201 v_hist_col_value := v_current_col_value;
202 END IF;
203 END IF;
204 RETURN v_hist_col_value;
205 ELSE
206 RETURN p_column_value;
207 END IF;
208 END IF;
209 ELSE
210 RETURN p_column_value;
211 END IF;
212 END IF;
213 END;
214 EXCEPTION
215 WHEN OTHERS THEN
216 gv_other_detail := 'Parm: p_column_name - ' || p_column_name
217 || ' p_person_id - ' || TO_CHAR(p_person_id)
218 || ' p_course_cd - ' || p_course_cd
219 || ' p_course_attempt_status - ' || p_course_attempt_status
220 || ' p_hist_start_dt - ' || IGS_GE_DATE.IGSCHARDT(p_hist_start_dt);
221
222 RAISE;
223 END enrp_get_scaeh_col;
224 --
225 -- Routine to get the effective end date for a SCA history
226 FUNCTION enrp_get_scaeh_eff_end(
227 p_person_id IN IGS_AS_SC_ATTEMPT_H_ALL.person_id%TYPE ,
228 p_course_cd IN IGS_AS_SC_ATTEMPT_H_ALL.course_cd%TYPE ,
229 p_hist_end_dt IN DATE ,
230 p_course_attempt_status IN VARCHAR2 )
231 RETURN DATE AS
232 gv_other_detail VARCHAR2(255);
233 BEGIN
234 DECLARE
235 -- cursor to get the current student course attempt status
236 CURSOR c_sca (
237 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
238 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE) IS
239 SELECT /*+ ROWID(IGS_EN_STDNT_PS_ATT) */
240 course_attempt_status,
241 commencement_dt,
242 discontinued_dt
243 FROM IGS_EN_STDNT_PS_ATT
244 WHERE person_id = cp_person_id AND
245 course_cd = cp_course_cd;
246 -- cursor to get the last enrolled history
247 CURSOR c_last_e_scah (
248 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
249 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE) IS
250 SELECT MAX(hist_end_dt)
251 FROM IGS_AS_SC_ATTEMPT_H
252 WHERE person_id = cp_person_id AND
253 course_cd = cp_course_cd AND
254 course_attempt_status = 'ENROLLED';
255 v_current_cas IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
256 v_current_c_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
257 v_current_d_dt IGS_EN_STDNT_PS_ATT.discontinued_dt%TYPE;
258 v_last_hist_end_dt IGS_AS_SC_ATTEMPT_H.hist_end_dt%TYPE;
259 BEGIN -- enrp_get_scaeh_eff_end
260 -- get the current course attempt status
261 OPEN c_sca( p_person_id,
262 p_course_cd);
263 FETCH c_sca INTO v_current_cas,
264 v_current_c_dt,
265 v_current_d_dt;
266 CLOSE c_sca;
267 -- The following assumptions have been made;
268 -- A student course attempt with course attempt status = 'ENROLLED' can be
269 -- changed to any other status
270 -- A student course attempt with course attempt status = 'DISCONTIN' can be
271 -- changed to 'ENROLLED' only
272 -- A student course attempt with course attempt status = 'UNCONFIRM' can be
273 -- changed to 'ENROLLED'
274 IF v_current_cas = 'DISCONTIN' THEN
275 IF p_course_attempt_status = 'DISCONTIN' THEN
276 -- All prior 'DISCONTINued histories are converted to match
277 -- the current student course attempt.
278 -- End the history at the same time as the current
279 -- student course attempt ie. now
280 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
281 ELSE -- history is not discontinued
282 -- Cannot be effectively discontinued before being
283 -- effectively enrolled.
284 -- All histories prior to discontinuation are considered
285 -- enrolled and ended at the start of the discontinuation
286 RETURN v_current_d_dt;
287 END IF;
288 ELSIF v_current_cas = 'ENROLLED' THEN
289 -- All histories converted to the ENROLLED definition.
290 -- End the history at the same time as the current
291 -- student course attempt ie. now
292 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
293 ELSIF v_current_cas = 'UNCONFIRM' THEN
294 -- All histories converted to the UNCONFIRMed definition.
295 -- End the history at the same time as the current
296 -- student course attempt ie. now
297 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
298 ELSE
299 IF v_current_c_dt IS NOT NULL THEN
300 -- attempt to find the last enrolled history entry
301 OPEN c_last_e_scah(
302 p_person_id,
303 p_course_cd);
304 FETCH c_last_e_scah INTO v_last_hist_end_dt;
305 IF (c_last_e_scah%NOTFOUND) THEN
306 CLOSE c_last_e_scah;
307 RETURN p_hist_end_dt;
308 ELSE
309 CLOSE c_last_e_scah;
310 IF p_hist_end_dt <= v_last_hist_end_dt THEN
311 -- all histories prior to and including the
312 -- last ENROLLED history are converted to the
313 -- last ENROLLED definition
314 RETURN v_last_hist_end_dt;
315 ELSE
316 RETURN p_hist_end_dt;
317 END IF;
318 END IF;
319 ELSE
320 RETURN p_hist_end_dt;
321 END IF;
322 END IF;
323 END;
324 EXCEPTION
325 WHEN OTHERS THEN
326 gv_other_detail := 'Parm: p_person_id - '
327 || TO_CHAR(p_person_id)
328 || ' p_course_cd - '
329 || p_course_cd
330 || ' p_hist_end_dt - '
331 || IGS_GE_DATE.IGSCHAR(p_hist_end_dt)
332 || ' p_course_attempt_status - '
333 || p_course_attempt_status;
334
335 RAISE;
336 END enrp_get_scaeh_eff_end;
337 --
338 -- Routine to get the effective start date for a SCA history
339 FUNCTION enrp_get_scaeh_eff_st(
340 p_person_id IN IGS_AS_SC_ATTEMPT_H_ALL.person_id%TYPE ,
341 p_course_cd IN IGS_AS_SC_ATTEMPT_H_ALL.course_cd%TYPE ,
342 p_hist_start_dt IN DATE ,
343 p_course_attempt_status IN VARCHAR2 )
344 RETURN DATE AS
345 gv_other_detail VARCHAR2(255);
346 BEGIN
347 DECLARE
348 -- cursor to get the current student course attempt status
349 CURSOR c_sca (
350 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
351 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE) IS
352 SELECT /*+ ROWID(IGS_EN_STDNT_PS_ATT) */
353 course_attempt_status,
354 commencement_dt,
355 discontinued_dt
356 FROM IGS_EN_STDNT_PS_ATT
357 WHERE person_id = cp_person_id AND
358 course_cd = cp_course_cd;
359 CURSOR c_last_e_scah (
360 cp_person_id IGS_AS_SC_ATTEMPT_H.person_id%TYPE,
361 cp_course_cd IGS_AS_SC_ATTEMPT_H.course_cd%TYPE) IS
362 SELECT MAX(hist_start_dt)
363 FROM IGS_AS_SC_ATTEMPT_H
364 WHERE person_id = cp_person_id AND
365 course_cd = cp_course_cd AND
366 course_attempt_status = 'ENROLLED';
367 v_last_hist_start_dt IGS_AS_SC_ATTEMPT_H.hist_start_dt%TYPE;
368 v_current_cas IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
369 v_current_c_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
370 v_current_d_dt IGS_EN_STDNT_PS_ATT.discontinued_dt%TYPE;
371 BEGIN -- enrp_get_scaeh_eff_st
372 -- get the current course attempt status
373 OPEN c_sca( p_person_id,
374 p_course_cd);
375 FETCH c_sca INTO v_current_cas,
376 v_current_c_dt,
377 v_current_d_dt;
378 CLOSE c_sca;
379 -- The following assumptions have been made;
380 -- A student course attempt with course attempt status = 'ENROLLED' can be
381 -- changed to any other status
382 -- A student course attempt with course attempt status = 'DISCONTIN' can be
383 -- changed to 'ENROLLED' only
384 -- A student course attempt with course attempt status = 'UNCONFIRM' can be
385 -- changed to 'ENROLLED'
386 IF v_current_cas = 'DISCONTIN' THEN
387 IF p_course_attempt_status = 'DISCONTIN' THEN
388 -- All prior 'DISCONTINued histories are converted to match
389 -- the current student course attempt.
390 -- Start the history at the same effective time as the current
391 -- student course attempt ie. now
392 RETURN v_current_d_dt;
393 ELSE -- history is not discontinued
394 -- Cannot be effectively discontinued before being
395 -- effectively enrolled.
396 -- All histories prior to discontinuation are considered
397 -- enrolled and commenced at the start of the enrolment
398 IF TRUNC(v_current_c_dt) <= TRUNC(SYSDATE) THEN
399 RETURN v_current_c_dt;
400 ELSE -- commencing in the future
401 RETURN TRUNC(SYSDATE);
402 END IF;
403 END IF;
404 ELSIF v_current_cas = 'ENROLLED' THEN
405 -- All histories converted to the ENROLLED definition.
406 -- Start the history at the same time as the current
407 -- student course attempt ie. now
408 IF TRUNC(v_current_c_dt) <= TRUNC(SYSDATE) THEN
409 RETURN v_current_c_dt;
410 ELSE -- commencing in the future
411 RETURN TRUNC(SYSDATE);
412 END IF;
413 ELSIF v_current_cas = 'UNCONFIRM' THEN
414 -- All histories converted to the UNCONFIRMed definition.
415 -- Start the history at the same time as the current
416 -- student course attempt ie. now
417 RETURN TRUNC(SYSDATE);
418 ELSE
419 IF v_current_c_dt IS NOT NULL THEN
420 -- attempt to find the last enrolled history entry
421 OPEN c_last_e_scah(
422 p_person_id,
423 p_course_cd);
424 FETCH c_last_e_scah INTO v_last_hist_start_dt;
425 IF (c_last_e_scah%NOTFOUND) THEN
426 CLOSE c_last_e_scah;
427 RETURN p_hist_start_dt;
428 ELSE
429 CLOSE c_last_e_scah;
430 IF p_hist_start_dt <= v_last_hist_start_dt THEN
431 -- all histories prior to and including the
432 -- last ENROLLED history are converted to
433 -- the last ENROLLED definition using it's
434 -- commencement dt as the start
435 IF TRUNC(v_current_c_dt) <= TRUNC(SYSDATE) THEN
436 RETURN v_current_c_dt;
437 ELSE -- commencing in the future
438 RETURN TRUNC(SYSDATE);
439 END IF;
440 ELSE
441 RETURN p_hist_start_dt;
442 END IF;
443 END IF;
444 ELSE
445 RETURN p_hist_start_dt;
446 END IF;
447 END IF;
448 END;
449 EXCEPTION
450 WHEN OTHERS THEN
451 gv_other_detail := 'Parm: p_person_id - '
452 || TO_CHAR(p_person_id)
453 || ' p_course_cd - '
454 || p_course_cd
455 || ' p_hist_start_dt - '
456 || IGS_GE_DATE.IGSCHARDT(p_hist_start_dt)
457 || ' p_course_attempt_status - '
458 || p_course_attempt_status;
459
460 RAISE;
461 END enrp_get_scaeh_eff_st;
462 END IGS_EN_GET_SCAEH_DTL;
[Home] [Help]