[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_REINSTATE_PKG
Source
1 PACKAGE BODY IGS_EN_REINSTATE_PKG AS
2 /* $Header: IGSENB4B.pls 120.3 2006/02/21 00:55:56 svanukur noship $ */
3
4 PROCEDURE reinstate_stdnt_unit_attempt(
5 p_person_id IN NUMBER,
6 p_course_cd IN VARCHAR2,
7 p_course_version IN NUMBER,
8 p_load_cal_type IN VARCHAR2,
9 p_load_sequence_number IN VARCHAR2,
10 p_uoo_id IN NUMBER,
11 p_person_type IN VARCHAR2,
12 p_return_status OUT NOCOPY VARCHAR2,
13 p_message OUT NOCOPY VARCHAR2,
14 p_deny_warn OUT NOCOPY VARCHAR2)
15
16 ------------------------------------------------------------------
17 --Created by : Somasekar, Oracle IDC
18 --Date created: 11-AUG-2005
19 --
20 --Purpose: This procedure validates the unit attempt and program attempt
21 -- while renistating the discontinued unit.
22 --
23 --Known limitations/enhancements and/or remarks:
24 --
25 --Change History:
26 --Who When What
27 -------------------------------------------------------------------
28
29 AS
30 -- Cursor to find unit attempt status
31 CURSOR uoo_status IS
32 SELECT unit_section_status
33 FROM igs_ps_unit_ofr_opt
34 WHERE uoo_id = p_uoo_id;
35 --curosr to get student unit attempt status
36
37 CURSOR sua_status is
38 Select unit_Attempt_Status
39 FROM igs_En_su_Attempt
40 where person_Id = p_person_id
41 AND course_cd = p_course_cd
42 AND uoo_id = p_uoo_id;
43
44 -- Cursor to get the Unit Attempt Details .
45 CURSOR igs_en_su_attempt_cur IS
46 SELECT igs_en_su_attempt.*
47 FROM igs_en_su_attempt
48 WHERE person_id = p_person_id AND
49 course_cd = p_course_cd AND
50 uoo_id = p_uoo_id;
51
52 igs_en_su_attempt_rec igs_en_su_attempt_cur%ROWTYPE;
53 l_uoo_status uoo_status%ROWTYPE;
54 l_enr_meth_type igs_en_method_type.enr_method_type%TYPE;
55 l_enr_cal_type VARCHAR2(20);
56 l_enr_ci_seq NUMBER(20);
57 l_enr_cat VARCHAR2(20);
58 l_enr_comm VARCHAR2(2000);
59 l_enr_categories VARCHAR2(255);
60 l_acad_cal_type igs_ca_inst.cal_type%type;
61 l_acad_ci_sequence_number igs_ca_inst.sequence_number%type;
62 l_acad_start_dt igs_ca_inst.start_dt%type;
63 l_acad_end_dt igs_ca_inst.end_dt%type;
64 l_alternate_code igs_ca_inst.alternate_code%type;
65 l_acad_message varchar2(100);
66 l_message VARCHAR2(1200);
67 l_return_status VARCHAR2(10);
68 l_sua_Status igs_En_Su_Attempt.unit_Attempt_Status%TYPE;
69
70 BEGIN
71 --check if the unit is in discontinued state .
72 OPEN sua_status;
73 FETCH sua_status INTO l_sua_Status;
74 close sua_status;
75 IF l_sua_Status <> 'DISCONTIN'
76 THEN p_message := 'IGS_GE_RECORD_CHANGED';
77 p_return_status := 'FALSE';
78 p_deny_warn := 'DENY';
79 RETURN;
80 END IF;
81
82 OPEN uoo_status;
83 FETCH uoo_status INTO l_uoo_status;
84 CLOSE uoo_status;
85 IF l_uoo_status.unit_section_status IN ('CANCELLED', 'PLANNED', 'NOT_OFFERED') THEN
86 p_message := 'IGS_EN_SS_CANNOT_WAITLIST';
87 p_return_status := 'FALSE';
88 p_deny_warn := 'DENY';
89 RETURN;
90 END IF;
91
92 -- call igs_en_gen_017.enrp_get_enr_method to decide enrollment method type
93 igs_en_gen_017.enrp_get_enr_method(
94 p_enr_method_type => l_enr_meth_type,
95 p_error_message => p_message,
96 p_ret_status => p_return_status);
97
98 -- added below logic to get the Academic Calendar which is used by method enrp_get_enr_cat --
99 -- get the academic calendar of the given Load Calendar
100 --
101 l_alternate_code := Igs_En_Gen_002.Enrp_Get_Acad_Alt_Cd(
102 p_cal_type => p_load_cal_type,
103 p_ci_sequence_number => p_load_sequence_number,
104 p_acad_cal_type => l_acad_cal_type,
105 p_acad_ci_sequence_number => l_acad_ci_sequence_number,
106 p_acad_ci_start_dt => l_acad_start_dt,
107 p_acad_ci_end_dt => l_acad_end_dt,
108 p_message_name => l_acad_message );
109
110 IF l_acad_message IS NOT NULL THEN
111 p_message := l_acad_message;
112 p_return_status := 'FALSE';
113 p_deny_warn := 'DENY';
114 END IF;
115
116 l_enr_cat := igs_en_gen_003.enrp_get_enr_cat( p_person_id => p_person_id,
117 p_course_cd => p_course_cd ,
118 p_cal_type => l_acad_cal_type ,
119 p_ci_sequence_number => l_acad_ci_sequence_number,
120 p_session_enrolment_cat => NULL,
121 p_enrol_cal_type => l_enr_cal_type,
122 p_enrol_ci_sequence_number=> l_enr_ci_seq,
123 p_commencement_type => l_enr_comm,
124 p_enr_categories => l_enr_categories );
125
126 IF l_enr_comm = 'BOTH' THEN
127 l_enr_comm :='ALL';
128 END IF;
129
130 OPEN igs_en_su_attempt_cur;
131 FETCH igs_en_su_attempt_cur INTO igs_en_su_attempt_rec;
132 CLOSE igs_en_su_attempt_cur;
133
134 igs_en_sua_api.update_unit_attempt(
135 X_ROWID => igs_en_su_attempt_rec.row_id,
136 X_PERSON_ID => igs_en_su_attempt_rec.person_id,
137 X_COURSE_CD => igs_en_su_attempt_rec.course_cd,
138 X_UNIT_CD => igs_en_su_attempt_rec.unit_cd,
139 X_CAL_TYPE => igs_en_su_attempt_rec.cal_type,
140 X_CI_SEQUENCE_NUMBER => igs_en_su_attempt_rec.ci_sequence_number,
141 X_VERSION_NUMBER => igs_en_su_attempt_rec.version_number,
142 X_LOCATION_CD => igs_en_su_attempt_rec.location_cd,
143 X_UNIT_CLASS => igs_en_su_attempt_rec.unit_class,
144 X_CI_START_DT => igs_en_su_attempt_rec.ci_start_dt,
145 X_CI_END_DT => igs_en_su_attempt_rec.ci_end_dt,
146 X_UOO_ID => igs_en_su_attempt_rec.uoo_id,
147 X_ENROLLED_DT => igs_en_su_attempt_rec.enrolled_dt,
148 X_UNIT_ATTEMPT_STATUS => 'ENROLLED',
149 X_ADMINISTRATIVE_UNIT_STATUS => NULL,
150 X_ADMINISTRATIVE_PRIORITY => igs_en_su_attempt_rec.administrative_PRIORITY,
151 X_DISCONTINUED_DT => NULL,
152 X_DCNT_REASON_CD => NULL,
153 X_RULE_WAIVED_DT => igs_en_su_attempt_rec.rule_waived_dt,
154 X_RULE_WAIVED_PERSON_ID => igs_en_su_attempt_rec.rule_waived_person_id,
155 X_NO_ASSESSMENT_IND => igs_en_su_attempt_rec.no_assessment_ind,
156 X_SUP_UNIT_CD => igs_en_su_attempt_rec.sup_unit_cd,
157 X_SUP_VERSION_NUMBER => igs_en_su_attempt_rec.SUP_VERSION_NUMBER,
158 X_EXAM_LOCATION_CD => igs_en_su_attempt_rec.exam_location_cd,
159 X_ALTERNATIVE_TITLE => igs_en_su_attempt_rec.alternative_title,
160 X_OVERRIDE_ENROLLED_CP => igs_en_su_attempt_rec.OVERRIDE_ENROLLED_CP,
161 X_OVERRIDE_EFTSU => igs_en_su_attempt_rec.OVERRIDE_EFTSU,
162 X_OVERRIDE_ACHIEVABLE_CP => igs_en_su_attempt_rec.OVERRIDE_ACHIEVABLE_CP,
163 X_OVERRIDE_OUTCOME_DUE_DT => igs_en_su_attempt_rec.OVERRIDE_OUTCOME_DUE_DT,
164 X_OVERRIDE_CREDIT_REASON => igs_en_su_attempt_rec.OVERRIDE_CREDIT_REASON,
165 X_WAITLIST_DT => igs_en_su_attempt_rec.WAITLIST_DT,
166 X_MODE => 'R',
167 X_GS_VERSION_NUMBER => igs_en_su_attempt_rec.GS_VERSION_NUMBER,
168 X_ENR_METHOD_TYPE => igs_en_su_attempt_rec.ENR_METHOD_TYPE,
169 X_FAILED_UNIT_RULE => igs_en_su_attempt_rec.FAILED_UNIT_RULE,
170 X_CART => igs_en_su_attempt_rec.CART,
171 X_RSV_SEAT_EXT_ID => igs_en_su_attempt_rec.RSV_SEAT_EXT_ID,
172 X_ORG_UNIT_CD => igs_en_su_attempt_rec.ORG_UNIT_CD,
173 X_SESSION_ID => igs_en_su_attempt_rec.SESSION_ID,
174 X_GRADING_SCHEMA_CODE => igs_en_su_attempt_rec.GRADING_SCHEMA_CODE,
175 X_DEG_AUD_DETAIL_ID => igs_en_su_attempt_rec.DEG_AUD_DETAIL_ID,
176 X_SUBTITLE => igs_en_su_attempt_rec.subtitle,
177 X_STUDENT_CAREER_TRANSCRIPT => igs_en_su_attempt_rec.student_career_transcript,
178 X_STUDENT_CAREER_STATISTICS => igs_en_su_attempt_rec.student_career_statistics,
179 X_ATTRIBUTE_CATEGORY => igs_en_su_attempt_rec.attribute_category,
180 X_ATTRIBUTE1 => igs_en_su_attempt_rec.attribute1,
181 X_ATTRIBUTE2 => igs_en_su_attempt_rec.attribute2,
182 X_ATTRIBUTE3 => igs_en_su_attempt_rec.attribute3,
183 X_ATTRIBUTE4 => igs_en_su_attempt_rec.attribute4,
184 X_ATTRIBUTE5 => igs_en_su_attempt_rec.attribute5,
185 X_ATTRIBUTE6 => igs_en_su_attempt_rec.attribute6,
186 X_ATTRIBUTE7 => igs_en_su_attempt_rec.attribute7,
187 X_ATTRIBUTE8 => igs_en_su_attempt_rec.attribute8,
188 X_ATTRIBUTE9 => igs_en_su_attempt_rec.attribute9,
189 X_ATTRIBUTE10 => igs_en_su_attempt_rec.attribute10,
190 X_ATTRIBUTE11 => igs_en_su_attempt_rec.attribute11,
191 X_ATTRIBUTE12 => igs_en_su_attempt_rec.attribute12,
192 X_ATTRIBUTE13 => igs_en_su_attempt_rec.attribute13,
193 X_ATTRIBUTE14 => igs_en_su_attempt_rec.attribute14,
194 X_ATTRIBUTE15 => igs_en_su_attempt_rec.attribute15,
195 X_ATTRIBUTE16 => igs_en_su_attempt_rec.attribute16,
196 X_ATTRIBUTE17 => igs_en_su_attempt_rec.attribute17,
197 X_ATTRIBUTE18 => igs_en_su_attempt_rec.attribute18,
198 X_ATTRIBUTE19 => igs_en_su_attempt_rec.attribute19,
199 X_ATTRIBUTE20 => igs_en_su_attempt_rec.attribute20,
200 X_WAITLIST_MANUAL_IND => igs_en_su_attempt_rec.waitlist_manual_ind ,
201 X_WLST_PRIORITY_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_priority_weight_num,
202 X_WLST_PREFERENCE_WEIGHT_NUM => igs_en_su_attempt_rec.wlst_preference_weight_num,
203 X_CORE_INDICATOR_CODE => igs_en_su_attempt_rec.core_indicator_code );
204
205 IF NOT igs_en_enroll_wlst.validate_unit( p_unit_cd => igs_en_su_attempt_rec.unit_cd,
206 p_version_number => igs_en_su_attempt_rec.version_number,
207 p_cal_type => igs_en_su_attempt_rec.cal_type,
208 p_ci_sequence_number => igs_en_su_attempt_rec.ci_sequence_number,
209 p_location_cd => igs_en_su_attempt_rec.location_cd,
210 p_person_id => igs_en_su_attempt_rec.person_id,
211 p_unit_class => igs_en_su_attempt_rec.unit_class,
212 p_uoo_id => igs_en_su_attempt_rec.uoo_id,
213 p_message_name => p_message,
214 p_deny_warn => p_deny_warn,
215 p_course_cd => igs_en_su_attempt_rec.course_cd ) THEN
216 p_return_status := 'FALSE';
217 RETURN;
218 END IF; --END OF igs_en_enroll_wlst.validate_unit
219
220 IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps(
221 p_person_id => p_person_id,
222 p_person_type => p_person_type,
223 p_load_cal_type => p_load_cal_type,
224 p_load_sequence_number => p_load_sequence_number,
225 p_uoo_id => p_uoo_id ,
226 p_course_cd => p_course_cd,
227 p_course_version => p_course_version,
228 p_enrollment_category => l_enr_cat,
229 p_enr_method_type => l_enr_meth_type,
230 p_comm_type => l_enr_comm,
231 p_message => p_message,
232 p_deny_warn => p_deny_warn,
233 p_calling_obj => 'REINSTATE'
234 ) THEN
235 IF p_message IS NOT NULL THEN
236 p_return_status := 'FALSE';
237 RETURN;
238 END IF;
239 END IF; -- IF NOT IGS_EN_ELGBL_UNIT.eval_unit_steps
240
241 -- Evaluate program steps
242
243 IF NOT igs_en_elgbl_program.eval_program_steps(
244 p_person_id => p_person_id,
245 p_person_type => p_person_type,
246 p_load_calendar_type => p_load_cal_type,
247 p_load_cal_sequence_number => p_load_sequence_number,
248 p_uoo_id => p_uoo_id,
249 p_program_cd => p_course_cd,
250 p_program_version => p_course_version,
251 p_enrollment_category => l_enr_cat,
252 p_comm_type => l_enr_comm,
253 p_method_type => l_enr_meth_type,
254 p_message => p_message,
255 p_deny_warn => p_deny_warn,
256 p_calling_obj => 'REINSTATE') THEN
257 IF p_message IS NOT NULL THEN
258 p_return_status := 'FALSE';
259 RETURN;
260 END IF; --end of eval_program_steps
261
262 END IF;
263 -- check for Max cp by passing the 0.0 value to credit points
264 --since the cp has already been enrolled.
265 igs_en_enroll_wlst.ss_eval_min_or_max_cp( p_person_id => p_person_id,
266 p_load_cal_type => p_load_cal_type,
267 p_load_ci_sequence_number => p_load_sequence_number,
268 p_uoo_id => p_uoo_id,
269 p_program_cd => p_course_cd,
270 p_step_type => 'FMAX_CRDT',
271 p_credit_points => 0.0, --deliberately passing the value zero since the cp has already been enrolled.
272 p_message_name => p_message,
273 p_deny_warn => p_deny_warn,
274 p_return_status => p_return_status,
275 p_enr_method => l_enr_meth_type);
276 IF p_return_status = 'FALSE' OR p_deny_warn = 'DENY' THEN
277 p_return_status := 'FALSE';
278 ELSE
279 p_return_status := 'TRUE';
280 END IF; --end of ss_eval_min_or_max_cp
281
282 RETURN;
283 END reinstate_stdnt_unit_attempt;
284
285 END IGS_EN_REINSTATE_PKG;