1 PACKAGE BODY igs_en_rpt_prc_uhk AS
2 /* $Header: IGSEN84B.pls 120.0 2005/06/01 20:21:22 appldev noship $ */
3
4
5 -- User Hook - which can be customisable by the customer.
6 --
7 -- This function returns the Derived Completion Date which in turn
8 -- is to be passed back to the calling program unit. This function
9 -- is to be called from the API IGS_EN_GEN_015.ENRP_DRV_CMPL_DT.
10 -- If the Derived Completion Date is not manually overridden in
11 -- Student Enrollment form (IGSEN022), then the API
12 -- IGS_EN_GEN_015.ENRP_DRV_CMPL_DT is called and the return value
13 -- is shown in the form to the user, but it is not stored anywhere in
14 -- the database. The User Hook should not be directly called from
15 -- any where in the code, instead the API should be used.
16 -- Who When What
17 -- bdeviset 04-AUG-2004 Removed function repeat_allowed and added function
18 -- repeat_reenroll_allowed as part of Bug 3620784
19
20
21 FUNCTION enrf_drv_cmpl_dt_uhk
22 (
23 p_person_id IN NUMBER,
24 p_course_cd IN VARCHAR2,
25 p_achieved_cp IN NUMBER,
26 p_attendance_type IN VARCHAR2,
27 p_load_cal_type IN VARCHAR2,
28 p_load_ci_seq_number IN NUMBER,
29 p_load_ci_alt_code IN VARCHAR2,
30 p_load_ci_start_dt IN DATE,
31 p_load_ci_end_dt IN DATE,
32 p_init_load_cal_type IN VARCHAR2,
33 p_init_load_ci_seq_num IN NUMBER,
34 p_init_load_ci_alt_code IN VARCHAR2,
35 p_init_load_ci_start_dt IN DATE,
36 p_init_load_ci_end_dt IN DATE
37 ) RETURN DATE IS
38 --
39 -- Parameters Description:
40 --
41 -- p_person_id -> Person Identifier
42 -- p_course_cd -> Program code
43 -- p_achieved_cp -> Credit point achieved
44 -- p_attendance_type -> Attendance Type
45 -- p_load_cal_type -> Term or Load Calendar Type
46 -- p_load_ci_seq_number -> Term or Load Calendar Type Sequence Number
47 -- p_load_ci_alt_code -> Load Calendar Alternate Code
48 -- p_load_ci_start_dt -> Load Calendar Start Date
49 -- p_load_ci_end_dt -> Load Calendar End Date
50 -- p_init_load_cal_type -> Initial Load Calendar
51 -- p_init_load_ci_seq_num -> Initial Load Sequence Number
52 -- p_init_load_ci_alt_code -> Initial Load Alternate Code
53 -- p_init_load_ci_start_dt -> Initial Load Start Date
54 -- p_init_load_ci_end_dt -> Initial Load End Date
55 --
56
57 BEGIN
58
59 -- PUT YOUR CODE HERE
60 RETURN NULL;
61
62 END enrf_drv_cmpl_dt_uhk;
63
64
65 FUNCTION repeat_reenroll_allowed (
66 p_person_id IN NUMBER,
67 p_program_cd IN VARCHAR2,
68 p_unit_cd IN VARCHAR2,
69 p_uoo_id IN NUMBER,
70 p_repeat_reenroll IN VARCHAR2,
71 p_load_cal_type IN VARCHAR2,
72 p_load_ci_seq_number IN NUMBER,
73 p_mus_ind IN VARCHAR2,
74 p_reenroll_max IN NUMBER,
75 p_reenroll_max_cp IN NUMBER,
76 p_repeat_max IN NUMBER,
77 p_repeat_funding IN NUMBER,
78 p_same_tch_reenroll_max IN NUMBER,
79 p_same_tch_reenroll_max_cp IN NUMBER,
80 p_message OUT NOCOPY VARCHAR2
81 ) RETURN BOOLEAN IS
82 --------------------------------------------------------------------------------
83 --Created by : bdeviset
84 --Date created: 04-AUG-2004
85 --
86 --Purpose: User Hook - which is customisable by the customer.
87 --
88 --
89 --Known limitations/enhancements and/or remarks:
90 --
91 --Change History:
92 --Who When What
93 --stutta 11-8-2004 Modifying user hook to return true for testing. Would
94 -- be reverting back to return null. bug #3826194
95 --------------------------------------------------------------------------------
96 -- p_repeat_reenroll -> is used to identify whether the user hook call is made to validate a repeatable unit
97 -- a reenrollable unit. p_repeat_reenroll is passed as 'REPEAT' for repeatable units and
98 -- 'REENROLL' for reenrollable units.
99 -- p_mus_ind -> indicates whether the unit section allows enrollment into multiple unit sections after
100 -- considering 'Exclude from Multiple Unit Section' indicator at unit section level.
101 -- p_mus_ind = 'Y' if Multiple Unit Section is checked at unit level and Exclude from
102 -- Multiple Unit section in not checked at unit section level.
103 -- p_mus_ind = 'N' if Multiple Unit Section is not set at unit level or Multiple Unit
104 -- section set a unit level, but excluded at unit section level.
105 -- All other parameter values are fetched from the repeat/reenroll setup done at unit level.
106 /* CURSOR cur_unit_details IS
107 SELECT unit_cd,
108 version_number,
109 cal_type,
110 ci_sequence_number
111 FROM igs_ps_unit_ofr_opt
112 WHERE uoo_id = p_uoo_id;
113
114 --
115 -- Cursor to select all the reenrolled Unit Attempts of the Student.
116 -- when the unit is added to the cart , the uncinfirmed unit attempt is created with cart = null.
117 -- hence when calculating re-enrollment/repeat limits we should ignore the current unit which has been added to the cart from the limits check.
118 -- the condition cart is not null does this .
119 CURSOR cur_student_attempts_reenroll (
120 cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE,
121 cp_version_number igs_ps_unit_ver.version_number%TYPE
122 ) IS
123 SELECT sua.unit_cd,
124 sua.version_number,
125 sua.cal_type,
126 sua.ci_sequence_number,
127 sua.uoo_id,
128 NVL(sua.override_enrolled_cp,NVL(cps.enrolled_credit_points,uv.enrolled_credit_points)) override_enrolled_cp ,
129 sua.course_cd
130 FROM igs_en_su_attempt sua,
131 igs_ps_unit_ver uv,
132 igs_ps_usec_cps cps
133 WHERE sua.person_id = p_person_id
134 AND sua.unit_cd = uv.unit_cd
135 AND sua.version_number = uv.version_number
136 AND sua.unit_cd = cp_unit_cd
137 AND sua.version_number = cp_version_number
138 AND sua.uoo_id = cps.uoo_id(+)
139 AND sua.unit_attempt_status IN ('ENROLLED', 'DISCONTIN','COMPLETED','INVALID','UNCONFIRM')
140 AND sua.cart IS NOT NULL;
141
142
143 rec_cur_unit_details cur_unit_details%ROWTYPE;
144 l_no_of_reenrollments NUMBER;
145 l_total_reenroll_credit_points NUMBER;
146 l_same_tp_cp NUMBER;
147 l_same_tp_reenrollments NUMBER; */
148 BEGIN
149 -- PUT YOUR CODE HERE
150 /* OPEN cur_unit_details;
151 FETCH cur_unit_details INTO rec_cur_unit_details;
152 CLOSE cur_unit_details;
153 -- This is sample code for REENROLL
154 IF (p_repeat_reenroll = 'REENROLL') THEN
155 l_no_of_reenrollments := 0;
156 l_total_reenroll_credit_points := 0;
157 l_same_tp_cp := 0;
158 l_same_tp_reenrollments := 0;
159 FOR rec_cur_student_attempts IN cur_student_attempts_reenroll(rec_cur_unit_details.unit_cd, rec_cur_unit_details.version_number) LOOP
160 l_total_reenroll_credit_points := l_total_reenroll_credit_points + rec_cur_student_attempts.override_enrolled_cp;
161 l_no_of_reenrollments := l_no_of_reenrollments + 1;
162 IF ((rec_cur_unit_details.cal_type = rec_cur_student_attempts.cal_type) AND
163 (rec_cur_unit_details.ci_sequence_number = rec_cur_student_attempts.ci_sequence_number)) THEN
164 l_same_tp_cp := l_same_tp_cp + rec_cur_student_attempts.override_enrolled_cp;
165 l_same_tp_reenrollments := l_same_tp_reenrollments + 1 ;
166 END IF;
167 END LOOP;
168 IF l_no_of_reenrollments > p_reenroll_max THEN
169 RETURN FALSE;
170 ELSIF l_same_tp_reenrollments > p_same_tch_reenroll_max THEN
171 RETURN FALSE;
172 ELSIF l_same_tp_cp > p_same_tch_reenroll_max_cp THEN
173 RETURN FALSE;
174 ELSIF l_total_reenroll_credit_points > p_reenroll_max_cp THEN
175 RETURN FALSE;
176 END IF;
177 RETURN TRUE;
178 ELSIF (p_repeat_reenroll = 'REPEAT') THEN
179 -- Similarly specific REPEAT processing can also be coded.
180 RETURN TRUE;
181 END IF; */
182
183 RETURN NULL;
184 END repeat_reenroll_allowed;
185
186
187 END igs_en_rpt_prc_uhk;