1 PACKAGE BODY IGS_EN_GET_SUAEH_DTL AS
2 /* $Header: IGSEN16B.pls 115.6 2003/06/23 10:36:29 rvivekan ship $ */
3
4 -- Get student unit attempt effective history column value
5 FUNCTION enrp_get_suaeh_col(
6 p_column_name IN VARCHAR2 ,
7 p_column_value IN VARCHAR2 ,
8 p_person_id IN hz_parties.party_id%TYPE ,
9 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
10 p_unit_cd IN IGS_PS_UNIT.unit_cd%TYPE ,
11 p_cal_type IN IGS_CA_TYPE.cal_type%TYPE ,
12 p_ci_seq_num IN IGS_CA_INST_ALL.sequence_number%TYPE ,
13 p_hist_start_dt IN DATE ,
14 p_unit_attempt_status IN VARCHAR2,
15 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE)
16 -------------------------------------------------------------------------------------------
17 --Change History:
18 --Who When What
19 --kkillams 28-04-2003 New parameter p_uoo_id is added to this function and
20 -- c_sua and c_last_e_suah got modified due to change of pk of
21 -- student unit attempt w.r.t. bug number 2829262
22 -------------------------------------------------------------------------------------------
23 RETURN VARCHAR2 AS
24 gv_other_detail VARCHAR2(255);
25 BEGIN
26 DECLARE
27 -- cursor to get the current student unit attempt status
28 CURSOR c_sua ( cp_column_name user_tab_columns.column_name%TYPE,
29 cp_person_id igs_en_su_attempt.person_id%TYPE,
30 cp_course_cd igs_en_su_attempt.course_cd%TYPE,
31 cp_uoo_id igs_en_su_attempt.uoo_id%TYPE) IS
32 SELECT /*+ ROWID(IGS_EN_SU_ATTEMPT) */
33 unit_attempt_status,
34 enrolled_dt,
35 DECODE (cp_column_name,
36 'VERSION_NUMBER', TO_CHAR(version_number),
37 'LOCATION_CD', location_cd,
38 'UNIT_CLASS', unit_class,
39 'ENROLLED_DT', IGS_GE_DATE.IGSCHARDT(enrolled_dt),
40 'UNIT_ATTEMPT_STATUS', unit_attempt_status,
41 'ADMINISTRATIVE_UNIT_STATUS', administrative_unit_status,
42 'DISCONTINUED_DT', IGS_GE_DATE.IGSCHARDT(discontinued_dt),
43 'RULE_WAIVED_DT', IGS_GE_DATE.IGSCHARDT(rule_waived_dt),
44 'RULE_WAIVED_PERSON_ID', TO_CHAR(rule_waived_person_id),
45 'NO_ASSESSMENT_IND', no_assessment_ind,
46 'EXAM_LOCATION_CD', exam_location_cd,
47 'SUP_VERSION_NUMBER', TO_CHAR(sup_version_number),
48 'ALTERNATIVE_TITLE', alternative_title,
49 'OVERRIDE_ENROLLED_CP', TO_CHAR(override_enrolled_cp),
50 'OVERRIDE_EFTSU', TO_CHAR(override_eftsu),
51 'OVERRIDE_ACHIEVABLE_CP', TO_CHAR(override_achievable_cp),
52 'OVERRIDE_OUTCOME_DUE_DT', IGS_GE_DATE.IGSCHARDT(override_outcome_due_dt),
53 'OVERRIDE_CREDIT_REASON', override_credit_reason)
54 FROM IGS_EN_SU_ATTEMPT
55 WHERE person_id = cp_person_id AND
56 course_cd = cp_course_cd AND
57 uoo_id = cp_uoo_id;
58 -- cursor to get the last enrolled history
59 CURSOR c_last_e_suah (
60 cp_column_name user_tab_columns.column_name%TYPE,
61 cp_person_id igs_en_su_attempt_h.person_id%TYPE,
62 cp_course_cd igs_en_su_attempt_h.course_cd%TYPE,
63 cp_uoo_id igs_en_su_attempt_h.uoo_id%TYPE) IS
64 SELECT /*+ FIRST_ROWS */
65 hist_start_dt,
66 hist_end_dt,
67 DECODE (cp_column_name,
68 'VERSION_NUMBER', TO_CHAR(version_number),
69 'LOCATION_CD', location_cd,
70 'UNIT_CLASS', unit_class,
71 'ENROLLED_DT', IGS_GE_DATE.IGSCHARDT(enrolled_dt),
72 'UNIT_ATTEMPT_STATUS', unit_attempt_status,
73 'ADMINISTRATIVE_UNIT_STATUS', administrative_unit_status,
74 'DISCONTINUED_DT', IGS_GE_DATE.IGSCHARDT(discontinued_dt),
75 'RULE_WAIVED_DT', IGS_GE_DATE.IGSCHARDT(rule_waived_dt),
76 'RULE_WAIVED_PERSON_ID', TO_CHAR(rule_waived_person_id),
77 'NO_ASSESSMENT_IND', no_assessment_ind,
78 'EXAM_LOCATION_CD', exam_location_cd,
79 'SUP_VERSION_NUMBER', TO_CHAR(sup_version_number),
80 'ALTERNATIVE_TITLE', alternative_title,
81 'OVERRIDE_ENROLLED_CP', TO_CHAR(override_enrolled_cp),
82 'OVERRIDE_EFTSU', TO_CHAR(override_eftsu),
83 'OVERRIDE_ACHIEVABLE_CP', TO_CHAR(override_achievable_cp),
84 'OVERRIDE_OUTCOME_DUE_DT', IGS_GE_DATE.IGSCHARDT(override_outcome_due_dt),
85 'OVERRIDE_CREDIT_REASON', override_credit_reason)
86 FROM IGS_EN_SU_ATTEMPT_H
87 WHERE person_id = cp_person_id AND
88 course_cd = cp_course_cd AND
89 uoo_id = cp_uoo_id AND
90 unit_attempt_status = 'ENROLLED'
91 ORDER BY hist_start_dt DESC;
92 v_last_hist_start_dt IGS_EN_SU_ATTEMPT_H.hist_start_dt%TYPE;
93 v_last_hist_end_dt IGS_EN_SU_ATTEMPT_H.hist_end_dt%TYPE;
94 v_current_cas IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
95 v_current_e_dt IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE;
96 v_current_col_value VARCHAR2(2000);
97 v_hist_col_value VARCHAR2(2000);
98 BEGIN
99 -- get the current unit attempt status
100 OPEN c_sua( p_column_name,
101 p_person_id,
102 p_course_cd,
103 p_uoo_id);
104 FETCH c_sua INTO v_current_cas,
105 v_current_e_dt,
106 v_current_col_value;
107 CLOSE c_sua;
108 -- The following assumptions have been made;
109 -- A student unit attempt with unit attempt status = 'ENROLLED' can be
110 -- changed to any other status
111 -- A student unit attempt with unit attempt status = 'DISCONTIN' can be
112 -- changed to 'ENROLLED' only
113 -- A student unit attempt with unit attempt status = 'UNCONFIRM' can be
114 -- changed to 'ENROLLED'
115 IF v_current_cas = 'DISCONTIN' THEN
116 IF p_unit_attempt_status = 'DISCONTIN' THEN
117 -- All prior 'DISCONTINued histories are converted to match
118 -- the current student unit attempt.
119 RETURN v_current_col_value;
120 ELSE -- history is not discontinued
121 -- Cannot be effectively discontinued before being
122 -- effectively enrolled.
123 -- All histories prior to discontinuation are considered
124 -- enrolled.
125 -- Find the last enrolled history entry
126 OPEN c_last_e_suah( p_column_name,
127 p_person_id,
128 p_course_cd,
129 p_uoo_id);
130 FETCH c_last_e_suah INTO v_last_hist_start_dt,
131 v_last_hist_end_dt,
132 v_hist_col_value;
133 IF (c_last_e_suah%NOTFOUND) THEN
134 CLOSE c_last_e_suah;
135 RETURN p_column_value;
136 ELSE
137 CLOSE c_last_e_suah;
138 IF v_hist_col_value IS NULL THEN
139 -- get the value of the first history instance
140 -- with a value for the column
141 v_hist_col_value := IGS_EN_GEN_007.ENRP_GET_SUAH_COL(
142 p_column_name,
143 p_person_id,
144 p_course_cd,
145 p_unit_cd,
146 p_cal_type,
147 p_ci_seq_num,
148 v_last_hist_end_dt,
149 p_uoo_id);
150 IF v_hist_col_value IS NULL AND
151 p_column_name <> 'ADMINISTRATIVE_UNIT_STATUS' AND
152 p_column_name <> 'DISCONTINUED_DT'
153 THEN
154 v_hist_col_value := v_current_col_value;
155 END IF;
156 END IF;
157 RETURN v_hist_col_value;
158 END IF;
159 END IF;
160 ELSIF v_current_cas = 'ENROLLED' THEN
161 -- All histories converted to the ENROLLED definition.
162 RETURN v_current_col_value;
163 ELSIF v_current_cas = 'UNCONFIRM' THEN
164 -- All histories converted to the UNCONFIRMed definition.
165 RETURN v_current_col_value;
166 ELSE
167 IF v_current_e_dt IS NOT NULL THEN
168 -- attempt to find the last enrolled history entry
169 OPEN c_last_e_suah( p_column_name,
170 p_person_id,
171 p_course_cd,
172 p_uoo_id);
173 FETCH c_last_e_suah INTO v_last_hist_start_dt,
174 v_last_hist_end_dt,
175 v_hist_col_value;
176 IF (c_last_e_suah%NOTFOUND) THEN
177 CLOSE c_last_e_suah;
178 RETURN p_column_value;
179 ELSE
180 CLOSE c_last_e_suah;
181 IF p_hist_start_dt <= v_last_hist_start_dt THEN
182 -- All histories prior to and including the
183 -- last ENROLLED history are converted to the
184 -- last ENROLLED definition.
185 IF v_hist_col_value IS NULL THEN
186 -- get the value of the first history
187 -- instance with a value for the column
188 v_hist_col_value := IGS_EN_GEN_007.ENRP_GET_SUAH_COL(
189 p_column_name,
190 p_person_id,
191 p_course_cd,
192 p_unit_cd,
193 p_cal_type,
194 p_ci_seq_num,
195 v_last_hist_end_dt,
196 p_uoo_id);
197 IF v_hist_col_value IS NULL AND
198 p_column_name <> 'ADMINISTRATIVE_UNIT_STATUS' AND
199 p_column_name <> 'DISCONTINUED_DT'
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_unit_cd - ' || p_unit_cd
220 || ' p_cal_type - ' || p_cal_type
221 || ' p_ci_seq_num - ' || TO_CHAR(p_ci_seq_num)
222 || ' p_unit_attempt_status - ' || p_unit_attempt_status
223 || ' p_hist_start_dt - ' || IGS_GE_DATE.IGSCHARDT(p_hist_start_dt)
224 || ' p_uoo_id - '||TO_CHAR(p_uoo_id);
225 RAISE;
226 END enrp_get_suaeh_col;
227 --
228 -- Routine to get the effective end date for a SUA history
229 FUNCTION enrp_get_suaeh_eff_end(
230 p_person_id IN IGS_EN_SU_ATTEMPT_H_ALL.person_id%TYPE ,
231 p_course_cd IN IGS_EN_SU_ATTEMPT_H_ALL.course_cd%TYPE ,
232 p_unit_cd IN IGS_EN_SU_ATTEMPT_H_ALL.unit_cd%TYPE ,
233 p_cal_type IN IGS_EN_SU_ATTEMPT_H_ALL.cal_type%TYPE ,
234 p_ci_sequence_num IN IGS_EN_SU_ATTEMPT_H_ALL.ci_sequence_number%TYPE ,
235 p_hist_end_dt IN IGS_EN_SU_ATTEMPT_H_ALL.hist_end_dt%TYPE ,
236 p_unit_attempt_status IN IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE,
237 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE)
238 -------------------------------------------------------------------------------------------
239 --Change History:
240 --Who When What
241 --rvivekan 23-Jun-2003 modified cursor c_last_e_suah as it was giving rise to
242 -- perfomance issues. (Bug#2879291)
243 --kkillams 28-04-2003 New parameter p_uoo_id is added to this function and
244 -- c_sua and c_last_e_suah got modified due to change of pk of
245 -- student unit attempt w.r.t. bug number 2829262
246 -------------------------------------------------------------------------------------------
247 RETURN DATE AS
248 gv_other_detail VARCHAR2(255);
249 BEGIN
250 DECLARE
251 -- cursor to get the current student unit attempt status
252 CURSOR c_sua (
253 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
254 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
255 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
256 SELECT /*+ ROWID(IGS_EN_SU_ATTEMPT) */
257 unit_attempt_status,
258 enrolled_dt,
259 discontinued_dt
260 FROM IGS_EN_SU_ATTEMPT
261 WHERE person_id = cp_person_id AND
262 course_cd = cp_course_cd AND
263 uoo_id = cp_uoo_id;
264 -- cursor to get the last enrolled history..modified cursor due perfomance issues
265 CURSOR c_last_e_suah (
266 cp_person_id IGS_EN_SU_ATTEMPT_H.person_id%TYPE,
267 cp_course_cd IGS_EN_SU_ATTEMPT_H.course_cd%TYPE,
268 cp_uoo_id IGS_EN_SU_ATTEMPT_H.uoo_id%TYPE) IS
269 SELECT MAX(SUAH1.hist_end_dt)
270 FROM IGS_EN_SU_ATTEMPT_H suah1, IGS_EN_SU_ATTEMPT sua1
271 WHERE SUA1.person_id = SUAH1.person_id AND SUA1.course_cd = SUAH1.course_cd AND SUA1.uoo_id = SUAH1.uoo_id
272 AND SUAH1.person_id = cp_person_id AND SUAH1.course_cd = cp_course_cd AND SUAH1.uoo_id = cp_uoo_id
273 AND SUBSTR(NVL(SUAH1.unit_attempt_status, NVL(Igs_Au_Gen_003.audp_get_suah_col('UNIT_ATTEMPT_STATUS', SUAH1.person_id, SUAH1.course_cd, SUAH1.hist_end_dt, SUAH1.uoo_id ), SUA1.unit_attempt_status)),1,10)='ENROLLED' ;
274
275 v_current_uas IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
276 v_current_e_dt IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE;
277 v_current_d_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
278 v_last_hist_end_dt IGS_EN_SU_ATTEMPT_H.hist_end_dt%TYPE;
279 BEGIN -- enrp_get_scahl_eff_end
280 -- get the current unit attempt status
281 OPEN c_sua( p_person_id,
282 p_course_cd,
283 p_uoo_id);
284 FETCH c_sua INTO v_current_uas,
285 v_current_e_dt,
286 v_current_d_dt;
287 CLOSE c_sua;
288 -- The following assumptions have been made;
289 -- A student unit attempt with unit attempt status = 'ENROLLED' can be
290 -- changed to any other status
291 -- A student unit attempt with unit attempt status = 'DISCONTIN' can be
292 -- changed to 'ENROLLED' only
293 -- A student unit attempt with unit attempt status = 'UNCONFIRM' can be
294 -- changed to 'ENROLLED'
295 IF v_current_uas = 'DISCONTIN' THEN
296 IF p_unit_attempt_status = 'DISCONTIN' THEN
297 -- All prior 'DISCONTINued histories are converted to match
298 -- the current student unit attempt.
299 -- End the history at the same time as the current
300 -- student unit attempt ie. now
301 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
302 ELSE -- history is not discontinued
303 -- Cannot be effectively discontinued before being
304 -- effectively enrolled.
305 -- All histories prior to discontinuation are considered
306 -- enrolled and ended at the start of the discontinuation
307 RETURN v_current_d_dt;
308 END IF;
309 ELSIF v_current_uas = 'ENROLLED' THEN
310 -- All histories converted to the ENROLLED definition.
311 -- End the history at the same time as the current
312 -- student unit attempt ie. now
313 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
314 ELSIF v_current_uas = 'UNCONFIRM' THEN
315 -- All histories converted to the UNCONFIRMed definition.
316 -- End the history at the same time as the current
317 -- student unit attempt ie. now
318 RETURN IGS_GE_DATE.IGSDATE(IGS_GE_DATE.IGSCHAR(SYSDATE)||' 23:59:59');
319 ELSE
320 IF v_current_e_dt IS NOT NULL THEN
321 -- attempt to find the last enrolled history entry
322 OPEN c_last_e_suah(
323 p_person_id,
324 p_course_cd,
325 p_uoo_id);
326 FETCH c_last_e_suah INTO v_last_hist_end_dt;
327 IF (c_last_e_suah%NOTFOUND) THEN
328 CLOSE c_last_e_suah;
329 RETURN p_hist_end_dt;
330 ELSE
331 CLOSE c_last_e_suah;
332 IF p_hist_end_dt <= v_last_hist_end_dt THEN
333 -- all histories prior to and including the
334 -- last ENROLLED history are converted to the
335 -- last ENROLLED definition
336 RETURN v_last_hist_end_dt;
337 ELSE
338 RETURN p_hist_end_dt;
339 END IF;
340 END IF;
341 ELSE
342 RETURN p_hist_end_dt;
343 END IF;
344 END IF;
345 END;
346 EXCEPTION
347 WHEN OTHERS THEN
348 gv_other_detail := 'Parm: p_person_id - '|| TO_CHAR(p_person_id)
349 || ' p_course_cd - '|| p_course_cd
350 || ' p_unit_cd - '|| p_unit_cd
351 || ' p_cal_type - '|| p_cal_type
352 || ' p_ci_sequence_num - '|| TO_CHAR(p_ci_sequence_num)
353 || ' p_hist_end_dt - '|| IGS_GE_DATE.IGSCHAR(p_hist_end_dt)
354 || ' p_unit_attempt_status - '|| p_unit_attempt_status
355 || ' p_uoo_id - '||TO_CHAR(p_uoo_id);
356 RAISE;
357 END enrp_get_suaeh_eff_end;
358 --
359 -- Routine to get the effective start date for a SUA history
360 FUNCTION enrp_get_suaeh_eff_st(
361 p_person_id IN IGS_EN_SU_ATTEMPT_H_ALL.person_id%TYPE ,
362 p_course_cd IN IGS_EN_SU_ATTEMPT_H_ALL.course_cd%TYPE ,
363 p_unit_cd IN IGS_EN_SU_ATTEMPT_H_ALL.unit_cd%TYPE ,
364 p_cal_type IN IGS_EN_SU_ATTEMPT_H_ALL.cal_type%TYPE ,
365 p_ci_sequence_num IN IGS_EN_SU_ATTEMPT_H_ALL.ci_sequence_number%TYPE ,
366 p_hist_start_dt IN IGS_EN_SU_ATTEMPT_H_ALL.hist_start_dt%TYPE ,
367 p_unit_attempt_status IN IGS_EN_SU_ATTEMPT_ALL.unit_attempt_status%TYPE,
368 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE)
369 -------------------------------------------------------------------------------------------
370 --Change History:
371 --Who When What
372 --kkillams 28-04-2003 New parameter p_uoo_id is added to this function and
373 -- c_sua and c_last_e_suah got modified due to change of pk of
374 -- student unit attempt w.r.t. bug number 2829262
375 -------------------------------------------------------------------------------------------
376 RETURN DATE AS
377 gv_other_detail VARCHAR2(255);
378 BEGIN
379 DECLARE
380 -- cursor to get the current student unit attempt status
381 CURSOR c_sua (
382 cp_person_id IGS_EN_SU_ATTEMPT.person_id%TYPE,
383 cp_course_cd IGS_EN_SU_ATTEMPT.course_cd%TYPE,
384 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
385 SELECT unit_attempt_status,
386 enrolled_dt,
387 discontinued_dt
388 FROM IGS_EN_SU_ATTEMPT
389 WHERE person_id = cp_person_id AND
390 course_cd = cp_course_cd AND
391 uoo_id = cp_uoo_id;
392 CURSOR c_last_e_suah (
393 cp_person_id IGS_EN_SU_ATTEMPT_H.person_id%TYPE,
394 cp_course_cd IGS_EN_SU_ATTEMPT_H.course_cd%TYPE,
395 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
396 SELECT MAX(hist_start_dt)
397 FROM IGS_EN_SU_ATTEMPT_H
398 WHERE person_id = cp_person_id AND
399 course_cd = cp_course_cd AND
400 uoo_id = cp_uoo_id AND
401 unit_attempt_status = 'ENROLLED';
402 v_last_hist_start_dt IGS_EN_SU_ATTEMPT_H.hist_start_dt%TYPE;
403 v_current_uas IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE;
404 v_current_e_dt IGS_EN_SU_ATTEMPT.enrolled_dt%TYPE;
405 v_current_d_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
406 BEGIN -- enrp_get_scahl_eff_st
407 -- get the current unit attempt status
408 OPEN c_sua( p_person_id,
409 p_course_cd,
410 p_uoo_id);
411 FETCH c_sua INTO v_current_uas,
412 v_current_e_dt,
413 v_current_d_dt;
414 CLOSE c_sua;
415 -- The following assumptions have been made;
416 -- A student unit attempt with unit attempt status = 'ENROLLED' can be
417 -- changed to any other status
418 -- A student unit attempt with unit attempt status = 'DISCONTIN' can be
419 -- changed to 'ENROLLED' only
420 -- A student unit attempt with unit attempt status = 'UNCONFIRM' can be
421 -- changed to 'ENROLLED'
422 IF v_current_uas = 'DISCONTIN' THEN
423 IF p_unit_attempt_status = 'DISCONTIN' THEN
424 -- All prior 'DISCONTINued histories are converted to match
425 -- the current student unit attempt.
426 -- Start the history at the same effective time as the current
427 -- student unit attempt ie. now
428 RETURN v_current_d_dt;
429 ELSE -- history is not discontinued
430 -- Cannot be effectively discontinued before being
431 -- effectively enrolled.
432 -- All histories prior to discontinuation are considered
433 -- enrolled and commenced at the start of the enrolment
434 RETURN v_current_e_dt;
435 END IF;
436 ELSIF v_current_uas = 'ENROLLED' THEN
437 -- All histories converted to the ENROLLED definition.
438 -- Start the history at the same time as the current
439 -- student unit attempt ie. now
440 RETURN v_current_e_dt;
441 ELSIF v_current_uas = 'UNCONFIRM' THEN
442 -- All histories converted to the UNCONFIRMed definition.
443 -- Start the history at the same time as the current
444 -- student unit attempt ie. now
445 RETURN TRUNC(SYSDATE);
446 ELSE
447 IF v_current_e_dt IS NOT NULL THEN
448 -- attempt to find the last enrolled history entry
449 OPEN c_last_e_suah(
450 p_person_id,
451 p_course_cd,
452 p_uoo_id);
453 FETCH c_last_e_suah INTO v_last_hist_start_dt;
454 IF (c_last_e_suah%NOTFOUND) THEN
455 CLOSE c_last_e_suah;
456 RETURN p_hist_start_dt;
457 ELSE
458 CLOSE c_last_e_suah;
459 IF p_hist_start_dt <= v_last_hist_start_dt THEN
460 -- all histories prior to and including the
461 -- last ENROLLED history are converted to
462 -- the last ENROLLED definition using it's
463 -- commencement dt as the start
464 RETURN v_current_e_dt;
465 ELSE
466 RETURN p_hist_start_dt;
467 END IF;
468 END IF;
469 ELSE
470 RETURN p_hist_start_dt;
471 END IF;
472 END IF;
473 END;
474 EXCEPTION
475 WHEN OTHERS THEN
476 gv_other_detail := 'Parm: p_person_id - '|| TO_CHAR(p_person_id)
477 || ' p_course_cd - '|| p_course_cd
478 || ' p_unit_cd - '|| p_unit_cd
479 || ' p_cal_type - '|| p_cal_type
480 || ' p_ci_sequence_num - '|| TO_CHAR(p_ci_sequence_num)
481 || ' p_hist_start_dt - '|| IGS_GE_DATE.IGSCHAR(p_hist_start_dt)
482 || ' p_unit_attempt_status - '|| p_unit_attempt_status
483 || ' p_uoo_id - '||TO_CHAR(p_uoo_id);
484 RAISE;
485 END enrp_get_suaeh_eff_st;
486 END IGS_EN_GET_SUAEH_DTL;
[Home] [Help]