[Home] [Help]
7:
8: PROCEDURE FINP_GET_SUAEH(
9: p_person_id IN NUMBER ,
10: p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
11: p_unit_cd IN IGS_PS_UNIT.unit_cd%TYPE ,
12: p_effective_dt IN DATE ,
13: p_table_index IN OUT NOCOPY BINARY_INTEGER ,
14: p_suaeh_table IN OUT NOCOPY IGS_FI_GET_SUAEH.t_suaeh_dtl)
15: AS
16: gv_other_detail VARCHAR2(255);
17: lv_param_values VARCHAR2(1080);
18: BEGIN
19: DECLARE
20: -- cursor to get the current student IGS_PS_UNIT attempt status
21: CURSOR c_sua IS
22: SELECT *
23: FROM IGS_EN_SU_ATTEMPT sua
24: WHERE sua.person_id = p_person_id AND
26: sua.course_cd = p_course_cd) AND
27: (p_unit_cd IS NULL OR
28: sua.unit_cd = p_unit_cd);
29: CURSOR c_suah ( cp_course_cd IGS_PS_COURSE.course_cd%TYPE,
30: cp_unit_cd IGS_PS_UNIT.unit_cd%TYPE,
31: cp_effective_dt DATE) IS
32: SELECT PERSON_ID ,
33: COURSE_CD ,
34: UNIT_CD ,
391: BEGIN
392: DECLARE
393: CURSOR c_suahv_last_enr (
394: cp_course_cd IGS_PS_COURSE.course_cd%TYPE,
395: cp_unit_cd IGS_PS_UNIT.unit_cd%TYPE,
396: cp_effective_dt DATE) IS
397: SELECT *
398: FROM IGS_AS_SUA_H_V suahv
399: WHERE suahv.person_id = p_person_id AND
419: finpl_ins_suahv_rec(
420: p_sua_rec.enrolled_dt,
421: r_suahv);
422: ELSE
423: -- save the current student IGS_PS_UNIT attempt data
424: finpl_ins_sua_rec(
425: r_suahv.hist_end_dt,
426: SYSDATE,
427: p_sua_rec);
428: END IF;
429: ELSE -- missing enrolment history
430: CLOSE c_suahv_last_enr;
431: -- check if the effective date falls within the period
432: -- of the current student IGS_PS_UNIT attempt values
433: IF TRUNC(gv_effective_dt) >= TRUNC(p_sua_rec.LAST_UPDATE_DATE) THEN
434: -- save the current sua data
435: finpl_ins_sua_rec(
436: p_sua_rec.LAST_UPDATE_DATE,
461: END finpl_prc_sua_enrhist;
462: -------------------------------------------------------------------------------
463: BEGIN -- finp_get_suaeh
464: -- effective history logic is based upon the following assumptions -
465: -- the transitions between IGS_PS_UNIT status's are;
466: -- UNCONFIRM -> ENROLLED
467: --
468: -- ENROLLED -> UNCONFIRM
469: -- -> DISCONTIN
517: gv_person_id := p_person_id;
518: gv_course_cd := p_course_cd;
519: gv_unit_cd := p_unit_cd;
520: gv_effective_dt := igs_ge_date.igsdate(igs_ge_date.igschar(p_effective_dt)|| '23:59:59');
521: -- get the current student IGS_PS_UNIT attempt detail
522: FOR v_sua_rec IN c_sua
523: LOOP
524: r_sua := v_sua_rec;
525: -- check if effective date is set today or into the future
529: 'ENROLLED',
530: 'INVALID') THEN
531: -- check if commencing on or before the effective date
532: IF TRUNC(r_sua.enrolled_dt) <= TRUNC(gv_effective_dt) THEN
533: -- save the current student IGS_PS_UNIT attempt data
534: finpl_ins_sua_rec(
535: r_sua.enrolled_dt,
536: gv_effective_dt,
537: r_sua);
535: r_sua.enrolled_dt,
536: gv_effective_dt,
537: r_sua);
538: ELSE
539: -- save the current student IGS_PS_UNIT attempt data as an
540: -- unconfirmed history
541: r_sua.unit_attempt_status := 'UNCONFIRM';
542: finpl_ins_sua_rec(
543: gv_effective_dt,
544: r_sua.enrolled_dt,
545: r_sua);
546: END IF;
547: ELSIF r_sua.unit_attempt_status = 'DISCONTIN' THEN
548: -- save the current student IGS_PS_UNIT attempt data
549: finpl_ins_sua_rec(
550: r_sua.discontinued_dt,
551: gv_effective_dt,
552: r_sua);
550: r_sua.discontinued_dt,
551: gv_effective_dt,
552: r_sua);
553: ELSIF r_sua.unit_attempt_status = 'UNCONFIRM' THEN
554: -- save the current student IGS_PS_UNIT attempt data
555: finpl_ins_sua_rec(
556: r_sua.LAST_UPDATE_DATE,
557: gv_effective_dt,
558: r_sua);
603: finpl_ins_sua_rec(r_sua.enrolled_dt,
604: SYSDATE,
605: r_sua);
606: END IF;
607: ELSE -- prior to student IGS_PS_UNIT attempt enrolment
608: IF v_suah_found = TRUE THEN
609: IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
610: -- save the sua history data
611: finpl_ins_suah_rec(
639: -- assume ENROLLED -> COMPLETED
640: -- use the last enrolled history
641: finpl_prc_sua_enrhist(r_sua);
642: END IF;
643: ELSE -- prior to student IGS_PS_UNIT attempt enrolment
644: IF v_suah_found = TRUE THEN
645: IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
646: -- save the sua history data
647: finpl_ins_suah_rec(
675: finpl_ins_sua_rec(r_sua.discontinued_dt,
676: SYSDATE,
677: r_sua);
678: END IF;
679: ELSE -- prior to student IGS_PS_UNIT attempt discontinuation
680: IF gv_effective_dt >= TRUNC(r_sua.enrolled_dt) THEN
681: -- within the enrolled period
682: IF v_suah_found = TRUE THEN
683: IF r_suah.unit_attempt_status IN (
696: -- assume ENROLLED -> DISCONTIN
697: -- use the last enrolled history
698: finpl_prc_sua_enrhist(r_sua);
699: END IF;
700: ELSE -- prior to student IGS_PS_UNIT attempt enrolment
701: IF v_suah_found = TRUE THEN
702: IF r_suah.unit_attempt_status IN (
703: 'UNCONFIRM') THEN
704: -- save the SUA history data
735: finpl_ins_sua_rec(
736: r_sua.LAST_UPDATE_DATE,
737: SYSDATE,
738: r_sua);
739: ELSE -- prior to student IGS_PS_UNIT attempt record creation
740: RETURN;
741: END IF;
742: END IF;
743: ELSIF r_sua.unit_attempt_status = 'INVALID' THEN
761: -- assume ENROLLED -> INVALID
762: -- use the last enrolled history
763: finpl_prc_sua_enrhist(r_sua);
764: END IF;
765: ELSE -- prior to student IGS_PS_UNIT attempt enrolment
766: IF v_suah_found = TRUE THEN
767: IF r_suah.unit_attempt_status IN ('UNCONFIRM') THEN
768: -- save the SUA history data
769: finpl_ins_suah_rec(
797: NVL(r_sua.discontinued_dt, r_sua.enrolled_dt),
798: SYSDATE,
799: r_sua);
800: END IF;
801: ELSE -- prior to original student IGS_PS_UNIT attempt
802: -- dicontinuation/enrolment
803: RETURN; -- no history
804: END IF;
805: ELSE -- unrecognised status