1 PACKAGE BODY Igs_En_Gen_009 AS
2 /* $Header: IGSEN09B.pls 120.1 2005/09/30 02:59:37 appldev ship $ */
3
4 --Added refernces to column ORG_UNIT_CD incall to IGS_EN_SU_ATTEMPT TBH call as a part of bug 1964697
5 --sarakshi 16-Nov-2004 Enh#4000939, added column APPROVED_DATE,EFFECTIVE_TERM_CAL_TYPE,EFFECTIVE_TERM_SEQUENCE_NUM and
6 -- DISCONTINUE_SOURCE_FLAG in the insert row call of IGS_PS_STDNT_TRN_PKG in procedure Enrp_Ins_Sct_Trnsfr
7 -- pradhakr 16-Dec-2002 Changed the call to the update_row of igs_en_su_attempt
8 -- table to igs_en_sua_api.update_unit_attempt.
9 -- Changes wrt ENCR031 build. Bug#2643207
10 -- Aiyer 10-Oct-2001 Added the columns grading schema code and gs_version_number in all Tbh calls of IGS_EN_SU_ATTEMPT_PKG as a part of the bug 2037897.
11 -- kkillams 13-11-2001 Added the columns primary_program_type, primary_prog_type_source, catalog_cal_type, catalog_seq_num,key_program as part of the bug 2027984
12 -- Nalin Kumar 23-Nov-2001 Added enrp_ins_award_aim procedure as the part of
13 -- UK Award Aims DLD Bug ID: 1366899
14 -- pradhakr 06-Dec-2001 Added a column deg_aud_detail_id in the TBH call of IGS_EN_SU_ATTEMPT as part of
15 -- Degree Audit Interface build (Bug# 2033208)
16 -- svenkata 20-Dec-2001 Added columns student_career_transcript and Student_career_statistics as part of build Career
17 -- Impact Part2 . Bug #2158626
18 -- svenkata 7-JAN-2002 Bug No. 2172405 Standard Flex Field columns have been added
19 -- to table handler procedure calls as part of CCR - ENCR022.
20 --Nishikant 30-jan-2002 Added the column session_id in the Tbh calls of IGS_EN_SU_ATTEMPT_PKG
21 -- as a part of the bug 2172380.
22 -- svenkata 25-02-02 Removed the procedure ENRP_INS_ENRL_FORM as part of CCR
23 -- ENCR024 .Bug # 2239050
24 -- Nishikant 13- JUN-2002 The code got modified to set the out NOCOPY parameter p_message instead of 'IGS_EN_NOT_CUR_ENROL'. as per the bug#2413811.
25 -- pradhakr 23-Sep-2002 Added a new parameter p_units_indicator in the package spec and body
26 -- as part of Core Vs Optional DLD. Bug# 2581270
27 -- amuthu 10-JUN-2003 modified as per the UK Streaming and Repeat TD (bug 2829265)
28 -- kkillams 17-Jun-2003 Three New parameters are added to Enrp_Ins_Pre_Pos function
29 -- w.r.t. bug 3829270
30 -- ptandon 7-Oct-2003 Modified the existing function Enrp_Ins_Pre_Pos and Added a new Function Enrp_Check_Usec_Core as
31 -- part of Prevent Dropping Core Units build. Enh Bug#3052432.
32
33 -- svanukur 18-oct-2003 modified enrp_ins_pre_pos as part of placements build 3052438 to process superior units first.
34 -- rvangala 02-Dec-2003 Added 4 new parameters to enrp_ins_sca_hist
35 -- bdeviset 11-DEC-2004 Added extra parameters to Enrp_Ins_Sct_Trnsfr as UOOID_TO_TRANSFER,SUSA_TO_TRANSFER, TRANSFER_ADV_STAND_FLAG transfer table
36 -- amuthu 05-JAN-2005 Added new method for deriving the core indicator value for the destinations program attempt in a transfer enrp_chk_dest_usec_core
37 -- ckasu 29-SEP-2005 Modfied signature of enrp_chk_dest_usec_core inorder to include cooid as a part of bug #4278867
38
39 l_smil_id IGS_EN_MERGE_ID_LOG.SMIL_id%TYPE;
40
41 PROCEDURE Enrp_Ins_Dflt_Effect(
42 p_person_id IN NUMBER ,
43 p_encumbrance_type IN VARCHAR2 ,
44 p_start_dt IN DATE ,
45 p_expiry_dt IN DATE ,
46 p_course_cd IN VARCHAR2 ,
47 p_message_name OUT NOCOPY VARCHAR2,
48 p_message_string IN OUT NOCOPY VARCHAR2 )
49 AS
50
51
52 BEGIN -- enrp_ins_dflt_effect
53 -- Insert the default IGS_PE_PERSENC_EFFCT's for a IGS_PE_PERS_ENCUMB
54 -- base on the encumbrance_type and its associated encmb_type_dflt_effects.
55 -- This procedure should only be called on createion of a IGS_PE_PERS_ENCUMB
56 -- record
57 DECLARE
58 v_closed_ind VARCHAR2(1);
59 v_error_ind NUMBER(5) := 0;
60 v_apply_to_course_ind IGS_EN_ENCMB_EFCTTYP_V.apply_to_course_ind%TYPE;
61 v_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
62 v_pee_seq_num IGS_PE_PERSENC_EFFCT.sequence_number%TYPE;
63 v_message_string VARCHAR2(5512);
64 v_return_type VARCHAR2(1);
65 CURSOR c_s_encmb_effect_type (
66 cp_s_encmb_effect_type IGS_EN_ENCMB_EFCTTYP_V.s_encmb_effect_type%TYPE) IS
67 SELECT closed_ind
68 FROM IGS_EN_ENCMB_EFCTTYP_V
69 WHERE s_encmb_effect_type = cp_s_encmb_effect_type;
70 CURSOR c_encmb_type_dflt_effect IS
71 SELECT eft.*,
72 seft.description encmb_meaning
73 FROM IGS_FI_ENC_DFLT_EFT eft,
74 IGS_EN_ENCMB_EFCTTYP_V seft
75 WHERE eft.encumbrance_type = p_encumbrance_type AND
76 eft.s_encmb_effect_type = seft.s_encmb_effect_type;
77
78 CURSOR c_get_apply_to_crs_ind (cp_s_encmb_effect_type
79 IGS_FI_ENC_DFLT_EFT.s_encmb_effect_type%TYPE) IS
80 SELECT apply_to_course_ind
81 FROM IGS_EN_ENCMB_EFCTTYP_V
82 WHERE s_encmb_effect_type = cp_s_encmb_effect_type;
83 CURSOR c_student_course_attempt IS
84 SELECT course_cd
85 FROM IGS_EN_STDNT_PS_ATT
86 WHERE person_id = p_person_id AND
87 course_attempt_status NOT IN ('DISCONTIN', 'COMPLETED', 'DELETED', 'LAPSED',
88 'UNCONFIRM');
89
90 BEGIN
91 p_message_name := NULL;
92 -- Set message 'Default effect results : ';
93 fnd_message.set_name('IGS','IGS_EN_DFLT_EFFECT_RESULT');
94 IGS_GE_MSG_STACK.ADD;
95 fnd_message.set_name('IGS','IGS_EN_DFLT_EFFECT_RESULT');
96 -- validate the input parameters
97 IF (p_person_id IS NULL OR
98 p_encumbrance_type IS NULL OR
99 p_start_dt IS NULL) THEN
100 RETURN;
101 END IF;
102 FOR v_etde_rec IN c_encmb_type_dflt_effect LOOP
103 -- Check that the effect type is open
104 OPEN c_s_encmb_effect_type(v_etde_rec.s_encmb_effect_type);
105 FETCH c_s_encmb_effect_type INTO v_closed_ind;
106 IF (v_closed_ind = 'Y') THEN
107 fnd_message.set_name('IGS' , 'IGS_EN_CLOSE_NOT_CREATED');
108 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
109 v_etde_rec.encmb_meaning || ' ' ||
110 fnd_message.get;
111 -- ' - closed, not created ';
112 CLOSE c_s_encmb_effect_type;
113 GOTO CONTINUE;
114 END IF;
115 CLOSE c_s_encmb_effect_type;
116 -- This effect cannot be applied if a IGS_PE_PERSON is still enrolled in a IGS_PS_COURSE
117 IF (v_etde_rec.s_encmb_effect_type = 'RVK_SRVC') THEN
118 IF (IGS_EN_VAL_PEE.enrp_val_pee_sca(
119 p_person_id,
120 p_message_name) = FALSE) THEN
121 -- This IGS_PE_PERSON is currently enrolled in IGS_PS_COURSE(s),
122 -- effect type can not be created
123 v_error_ind := v_error_ind + 1;
124 -- The code modified by Nishikant - 13Jun2002 - as per bug#2413811
125 -- Below the code got modified to set the out NOCOPY parameter p_message of the above function call
126 -- instead of 'IGS_EN_NOT_CUR_ENROL'
127 fnd_message.set_name('IGS' , p_message_name);
128 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
129 v_etde_rec.encmb_meaning ||' ' ||
130 fnd_message.get;
131 -- 'RVK_SRVC - not created ' ||
132 -- 'due to current enrolment';
133 GOTO CONTINUE;
134 END IF;
135 END IF;
136 -- check if selected s_encmb_effect_type applies to a IGS_PS_COURSE
137 OPEN c_get_apply_to_crs_ind(v_etde_rec.s_encmb_effect_type);
138 FETCH c_get_apply_to_crs_ind INTO v_apply_to_course_ind;
139 CLOSE c_get_apply_to_crs_ind;
140 IF (v_apply_to_course_ind = 'N') THEN
141 -- no need to set the IGS_PS_COURSE
142 v_course_cd := NULL;
143 ELSIF (p_course_cd IS NOT NULL) THEN
144 -- Validate that IGS_PE_PERSON is enrolled in the IGS_PS_COURSE.
145 IF IGS_EN_VAL_PEE.enrp_val_pee_crs (
146 p_person_id,
147 p_course_cd,
148 p_message_name) = FALSE THEN
149 -- This IGS_PE_PERSON is not enrolled in IGS_PS_COURSE,
150 -- IGS_PS_COURSE cannot be set
151 v_course_cd := NULL;
152 ELSE
153 v_course_cd := p_course_cd;
154 END IF;
155 ELSE --(p_course_cd IS NULL)
156 -- find each IGS_PS_COURSE the IGS_PE_PERSON is currently enrolled in
157 OPEN c_student_course_attempt;
158 LOOP
159 FETCH c_student_course_attempt INTO v_course_cd;
160 EXIT WHEN c_student_course_attempt%NOTFOUND;
161 END LOOP;
162 IF (c_student_course_attempt%ROWCOUNT = 1) THEN
163 -- IGS_PE_PERSON is enrolled in one IGS_PS_COURSE
164 -- v_course_cd is assigned by c_student_course_attempt.course_cd
165 CLOSE c_student_course_attempt;
166 ELSIF (c_student_course_attempt%ROWCOUNT > 1) THEN
167 -- IGS_PE_PERSON is enrolled in more than one IGS_PS_COURSE
168 v_error_ind := v_error_ind + 1;
169 fnd_message.set_name('IGS' , 'IGS_EN_NC_MULTI_CUR_ENR');
170 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
171 v_etde_rec.encmb_meaning || ' ' ||
172 -- v_etde_rec.s_encmb_effect_type || ' ' ||
173 fnd_message.get;
174 -- 'not created due to multiple current enrolments';
175 CLOSE c_student_course_attempt;
176 GOTO CONTINUE;
177 ELSE
178 -- IGS_PE_PERSON is not enrolled
179 v_error_ind := v_error_ind + 1;
180 fnd_message.set_name('IGS' , 'IGS_EN_NC_NO_CUR_ENR');
181 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
182 -- v_etde_rec.s_encmb_effect_type || ' ' ||
183 v_etde_rec.encmb_meaning || ' ' ||
184 fnd_message.get;
185 -- 'not created due to no current enrolment';
186 CLOSE c_student_course_attempt;
187 GOTO CONTINUE;
188 END IF;
189 END IF;
190 -- Validate if IGS_PE_PERSON already has a restricted attendance type encumbrance
191 -- applied to the target IGS_PS_COURSE. If so, don't create another.
192 IF v_course_cd IS NOT NULL AND
193 v_etde_rec.s_encmb_effect_type = 'RSTR_AT_TY' THEN
194 IF IGS_EN_VAL_PEE.enrp_val_pee_crs_att(
195 p_person_id,
196 v_etde_rec.s_encmb_effect_type,
197 0,
198 v_course_cd,
199 p_message_name) = FALSE THEN
200 v_error_ind := v_error_ind + 1;
201 fnd_message.set_name('IGS' , 'IGS_EN_NC_EXIST_REC_CONFLICT');
202 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
203 -- v_etde_rec.s_encmb_effect_type || ' ' ||
204 v_etde_rec.encmb_meaning || ' ' ||
205 fnd_message.get;
206 -- 'not created due to existing record conflict';
207 GOTO CONTINUE;
208 END IF;
209 END IF;
210 -- Validate if IGS_PE_PERSON already has a restricted credit point encumbrance
211 -- applied to the target IGS_PS_COURSE. If so, don't create another.
212 IF v_course_cd IS NOT NULL AND
213 v_etde_rec.s_encmb_effect_type IN ('RSTR_LE_CP','RSTR_GE_CP') THEN
214 IF IGS_EN_VAL_PEE.enrp_val_pee_crs_cp(
215 p_person_id,
216 v_etde_rec.s_encmb_effect_type,
217 0,
218 v_course_cd,
219 p_message_name) = FALSE THEN
220 v_error_ind := v_error_ind + 1;
221 fnd_message.set_name('IGS' , 'IGS_EN_NC_EXIST_REC_CONFLICT');
222 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
223 -- v_etde_rec.s_encmb_effect_type || ' ' ||
224 v_etde_rec.encmb_meaning || ' ' ||
225 fnd_message.get;
226 -- 'not created due to existing record conflict';
227 GOTO CONTINUE;
228 END IF;
229 END IF;
230 SELECT IGS_PE_PERSENC_EFFCT_SEQ_NUM_S.NEXTVAL
231 INTO v_pee_seq_num
232 FROM DUAL;
233 -- Call Table Handler
234 DECLARE
235 l_rowid VARCHAR2(25);
236 BEGIN
237 IGS_PE_PERSENC_EFFCT_PKG.INSERT_ROW (
238 x_rowid => l_rowid,
239 x_person_id => p_person_id,
240 x_encumbrance_type => p_encumbrance_type,
241 x_pen_start_dt => p_start_dt,
242 x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
243 x_pee_start_dt => p_start_dt,
244 x_sequence_number => v_pee_seq_num,
245 x_expiry_dt => p_expiry_dt,
246 x_course_cd => v_course_cd,
247 X_RESTRICTED_ENROLMENT_CP => NULL,
248 X_RESTRICTED_ATTENDANCE_TYPE => NULL,
249 X_MODE => 'R'
250 );
251 END;
252 -- End of TBH
253 IF v_etde_rec.s_encmb_effect_type IN (
254 'RSTR_GE_CP','RSTR_LE_CP','RSTR_AT_TY') THEN
255 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXTRA_DTL_REQ');
256 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
257 v_etde_rec.encmb_meaning || ' ' ||
258 -- v_etde_rec.s_encmb_effect_type || ' ' ||
259 fnd_message.get;
260 -- 'created, extra detail required';
261 ELSIF v_etde_rec.s_encmb_effect_type IN (
262 'EXC_CRS_GP','EXC_CRS_U','RQRD_CRS_U') THEN
263 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXTRA_DTL_REQ');
264 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
265 -- v_etde_rec.s_encmb_effect_type || ' ' ||
266 v_etde_rec.encmb_meaning || ' ' ||
267 fnd_message.get;
268 -- 'created, extra detail required';
269 ELSIF v_etde_rec.s_encmb_effect_type NOT IN (
270 'EXC_COURSE','SUS_COURSE') THEN
271 fnd_message.set_name('IGS' , 'IGS_EN_CR_SUCCESS');
272 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
273 -- v_etde_rec.s_encmb_effect_type || ' ' ||
274 v_etde_rec.encmb_meaning || ' ' ||
275 fnd_message.get;
276 -- 'created successfully';
277 ELSE
278 NULL;
279 END IF;
280 IF (v_etde_rec.s_encmb_effect_type = 'EXC_COURSE' AND
281 p_course_cd IS NOT NULL) THEN
282 -- This effect cannot be applied if a IGS_PE_PERSON is still enrolled in the IGS_PS_COURSE
283 IF (IGS_EN_VAL_PCE.enrp_val_pce_crs(
284 p_person_id,
285 p_course_cd,
286 p_start_dt,
287 p_message_name,
288 v_return_type) = FALSE) THEN
289 -- This IGS_PE_PERSON is currently enrolled in the IGS_PS_COURSE,
290 -- effect type can not be created
291 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXTRA_DTL_REQ');
292 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
293 v_etde_rec.encmb_meaning ||
294 fnd_message.get ;
295 -- 'EXC_COURSE - created, ' ||
296 -- 'extra detail required';
297 ELSE
298 -- Call the Table Handler
299 DECLARE
300 l_rowid VARCHAR2(25);
301 BEGIN
302 IGS_PE_COURSE_EXCL_PKG.INSERT_ROW(
303 x_rowid => l_rowid,
304 x_person_id => p_person_id,
305 x_encumbrance_type => p_encumbrance_type,
306 x_pen_start_dt => p_start_dt,
307 x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
308 x_pee_start_dt => p_start_dt,
309 x_pee_sequence_number => v_pee_seq_num,
310 x_course_cd => p_course_cd,
311 x_pce_start_dt => p_start_dt,
312 x_expiry_dt => p_expiry_dt,
313 x_mode => 'R'
314 );
315 END;
316 fnd_message.set_name('IGS' , 'IGS_EN_CR_SUCCESS');
317 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
318 -- v_etde_rec.s_encmb_effect_type || ' ' ||
319 v_etde_rec.encmb_meaning || ' ' ||
320 fnd_message.get ;
321 -- 'created successfully';
322 END IF;
323 ELSE
324 IF (v_etde_rec.s_encmb_effect_type = 'SUS_COURSE' AND
325 p_course_cd IS NOT NULL) THEN
326 -- This effect cannot be applied if a IGS_PE_PERSON is still enrolled in the IGS_PS_COURSE
327 IF (IGS_EN_VAL_PCE.enrp_val_pce_crs(
328 p_person_id,
329 p_course_cd,
330 p_start_dt,
331 p_message_name,
332 v_return_type) = FALSE) THEN
333 -- This IGS_PE_PERSON is currently enrolled in the IGS_PS_COURSE,
334 -- effect type can not be created
335 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXTRA_DTL_REQ');
336 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
337 -- v_etde_rec.encmb_meaning ||
338 v_etde_rec.encmb_meaning || ' ' ||
339 fnd_message.get ;
340 -- 'SUS_COURSE - created, ' ||
341 -- 'extra detail required';
342 ELSE
343 --Call the Table Handler
344 DECLARE
345 l_rowid VARCHAR2(25);
346 BEGIN
347 IGS_PE_COURSE_EXCL_PKG.INSERT_ROW(
348 x_rowid => l_rowid,
349 x_person_id => p_person_id,
350 x_encumbrance_type => p_encumbrance_type,
351 x_pen_start_dt => p_start_dt,
352 x_s_encmb_effect_type => v_etde_rec.s_encmb_effect_type,
353 x_pee_start_dt => p_start_dt,
354 x_pee_sequence_number => v_pee_seq_num,
355 x_course_cd => p_course_cd,
356 x_pce_start_dt => p_start_dt,
357 x_expiry_dt => p_expiry_dt,
358 x_mode => 'R'
359 );
360 END;
361 IF p_expiry_dt IS NULL THEN
362 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXP_DT_REQ');
363 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
364 -- v_etde_rec.s_encmb_effect_type || ' ' ||
365 v_etde_rec.encmb_meaning || ' ' ||
366 fnd_message.get;
367 -- 'created, expiry date required';
368 ELSE
369 fnd_message.set_name('IGS' , 'IGS_EN_CR_SUCCESS');
370 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
371 -- v_etde_rec.s_encmb_effect_type || ' ' ||
372 v_etde_rec.encmb_meaning || ' ' ||
373 fnd_message.get;
374 -- 'created successfully';
375 END IF;
376 END IF;
377 ELSE
378 IF (v_etde_rec.s_encmb_effect_type IN ('EXC_COURSE', 'SUS_COURSE') AND
379 p_course_cd IS NULL) THEN
380 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXP_DT_REQ');
381 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
382 -- v_etde_rec.s_encmb_effect_type || ' ' ||
383 v_etde_rec.encmb_meaning || ' ' ||
384 fnd_message.get;
385 -- 'created, extra detail required';
386 ELSE
387 IF v_etde_rec.s_encmb_effect_type = 'SUS_COURSE' THEN
388 IF p_expiry_dt IS NULL THEN
389 fnd_message.set_name('IGS' , 'IGS_EN_CR_EXP_DT_REQ');
390 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
391 -- v_etde_rec.s_encmb_effect_type || ' ' ||
392 v_etde_rec.encmb_meaning || ' ' ||
393 fnd_message.get;
394 -- 'created, expiry date required';
395 ELSE
396 fnd_message.set_name('IGS' , 'IGS_EN_CR_SUCCESS');
397 v_message_string := v_message_string || FND_GLOBAL.LOCAL_CHR(10) ||
398 -- v_etde_rec.s_encmb_effect_type || ' ' ||
399 v_etde_rec.encmb_meaning || ' ' ||
400 fnd_message.get;
401 -- 'created successfully';
402 END IF;
403 END IF;
404 END IF;
405 END IF;
406 END IF;
407 <<CONTINUE>>
408 -- reset
409 v_course_cd := NULL;
410 END LOOP;
411 IF (v_error_ind > 0) THEN
412 -- There were records which could not be created
413 p_message_name := 'IGS_EN_MULT_DFLT_ENCUMB';
414 END IF;
415 fnd_message.set_name('IGS','IGS_EN_DFLT_EFFECT_RESULT');
416 fnd_message.set_token('PARAM1',v_message_string);
417 p_message_string := v_message_string;
418 --
419 -- Write the following code in the form that calls this program directly / indirectly for message display
420 --
421 -- if v_message_name is not null then
422 -- fnd_message.set_name('IGS',v_message_name);
423 -- fnd_message.show;
424 -- end if;
425 -- fnd_message.retrieve;
426 -- fnd_message.show;
427 --
428 END;
429 END enrp_ins_dflt_effect;
430
431 PROCEDURE enrp_ins_award_aim (
432 p_person_id IN NUMBER,
433 p_course_cd IN VARCHAR2,
434 p_version_number IN NUMBER,
435 p_start_dt IN DATE
436 ) AS
437 /*
438 || Created By : [email protected]
439 || Created On : 22-NOV-2001
440 || Purpose : Added this procedure as per the UK Award Aims DLD.
441 || This will get called from the igs_en_stdnt_ps_att_pkg.
442 || Change History :
443 || Who When What
444 || anilk 30-Sep-2003 Changed for Program Completion Validation build
445 || Prajeesh Chandran .K 11-Jun-2002
446 || Added a check saying if in insert row of student program attempt is called
447 || it insert the awards else if called in udpaterow it updates the date or inserts the new awards which is not
448 || already inserted.
449 || (reverse chronological order - newest change first) igs_en_spa_awd_aim
450 */
451 CURSOR cur_caw IS
452 SELECT caw.award_cd, awd.grading_schema_cd, awd.gs_version_number
453 FROM igs_ps_award caw, igs_ps_awd awd
454 WHERE caw.course_cd = p_course_cd AND
455 caw.version_number = p_version_number AND
456 caw.default_ind = 'Y' AND
457 caw.closed_ind = 'N' AND
458 caw.award_cd = awd.award_cd;
459
460 CURSOR cur_spaa_awd_cnt IS
461 SELECT count(*)
462 FROM igs_en_spa_awd_aim awd
463 WHERE person_id = p_person_id AND
464 course_cd = p_course_cd;
465
466 CURSOR cur_spaa_awd IS
467 SELECT awd.*, awd.rowid
468 FROM igs_en_spa_awd_aim awd
469 WHERE person_id = p_person_id AND
470 course_cd = p_course_cd;
471
472 CURSOR cur_course IS
473 SELECT commencement_dt, course_rqrmnt_complete_ind
474 FROM igs_en_stdnt_ps_att
475 WHERE person_id = p_person_id AND
476 course_cd = p_course_cd AND
477 version_number = p_version_number;
478
479
480 lv_rowid VARCHAR2(25) := NULL;
481 l_course_rec cur_course%ROWTYPE;
482 l_spaa_awd_cnt NUMBER(4);
483 l_end_date DATE := NULL;
484
485 BEGIN
486 OPEN cur_spaa_awd_cnt;
487 FETCH cur_spaa_awd_cnt INTO l_spaa_awd_cnt;
488 CLOSE cur_spaa_awd_cnt;
489
490 IF l_spaa_awd_cnt = 0 THEN
491 OPEN cur_course;
492 FETCH cur_course INTO l_course_rec;
493 CLOSE cur_course;
494 IF l_course_rec.course_rqrmnt_complete_ind = 'Y' THEN
495 l_end_date := SYSDATE;
496 ELSE
497 l_end_date := NULL;
498 END IF;
499 FOR v_cur_caw IN cur_caw LOOP
500 igs_en_spa_awd_aim_pkg.insert_row (
501 x_rowid => lv_rowid,
502 x_person_id => p_person_id,
503 x_course_cd => p_course_cd,
504 x_award_cd => v_cur_caw.award_cd,
505 x_start_dt => p_start_dt,
506 x_end_dt => l_end_date,
507 x_complete_ind => 'N',
508 x_conferral_date => NULL,
509 x_award_mark => NULL,
510 x_award_grade => NULL,
511 x_grading_schema_cd => v_cur_caw.grading_schema_cd,
512 x_gs_version_number => v_cur_caw.gs_version_number,
513 x_mode => 'R'
514 );
515 END LOOP;
516 ELSE -- l_spaa_awd_cnt > 0
517 FOR l_spaa_awd_rec IN cur_spaa_awd LOOP
518 IF p_start_dt IS NOT NULL AND
519 p_start_dt <> l_spaa_awd_rec.start_dt THEN
520 igs_en_spa_awd_aim_pkg.update_row (
521 x_rowid => l_spaa_awd_rec.rowid,
522 x_person_id => l_spaa_awd_rec.person_id,
523 x_course_cd => l_spaa_awd_rec.course_cd,
524 x_award_cd => l_spaa_awd_rec.award_cd,
525 x_start_dt => p_start_dt,
526 x_end_dt => l_spaa_awd_rec.end_dt,
527 x_complete_ind => l_spaa_awd_rec.complete_ind,
528 x_conferral_date => l_spaa_awd_rec.conferral_date,
529 x_award_mark => l_spaa_awd_rec.award_mark,
530 x_award_grade => l_spaa_awd_rec.award_grade,
531 x_grading_schema_cd => l_spaa_awd_rec.grading_schema_cd,
532 x_gs_version_number => l_spaa_awd_rec.gs_version_number,
533 x_mode => 'R');
534 END IF;
535 END LOOP;
536 END IF;
537 END enrp_ins_award_aim;
538
539
540
541 PROCEDURE Enrp_Ins_Merge_Log(
542 p_smir_id IN NUMBER )
543 AS
544
545 BEGIN
546 DECLARE
547 TYPE t_merge_type IS RECORD (
548 obsolete_person_id IGS_EN_MERGE_ID_ROWS.obsolete_person_id%TYPE,
549 obsolete_id_row_info IGS_EN_MERGE_ID_ROWS.obsolete_id_row_info%TYPE,
550 current_person_id IGS_EN_MERGE_ID_ROWS.current_person_id%TYPE,
551 current_id_row_info IGS_EN_MERGE_ID_ROWS.current_id_row_info%TYPE,
552 table_alias IGS_EN_MERGE_ID_ROWS.table_alias%TYPE,
553 action_id IGS_EN_MRG_ID_ACT_CH.action_id%TYPE,
554 perform_action_id IGS_EN_MRG_ID_ACT_CH.perform_action_ind%TYPE );
555 v_merge_record t_merge_type;
556 CURSOR c_get_merge_data IS
557 SELECT smir.obsolete_person_id,
558 smir.obsolete_id_row_info,
559 smir.current_person_id,
560 smir.current_id_row_info,
561 smir.table_alias,
562 smiac.action_id,
563 smiac.perform_action_ind
564 FROM IGS_EN_MERGE_ID_ROWS smir, IGS_EN_MRG_ID_ACT_CH smiac
565 WHERE smir.smir_id = p_smir_id AND
566 smir.smir_id = smiac.smir_id;
567 l_seqval NUMBER;
568
569 BEGIN
570 FOR v_merge_record IN c_get_merge_data LOOP
571
572
573 DECLARE
574 l_rowid VARCHAR2(25);
575 BEGIN
576
577 -- Call table Handler
578 IGS_EN_MERGE_ID_LOG_PKG.INSERT_ROW (
579 x_rowid => l_rowid,
580 x_obsolete_person_id => v_merge_record.obsolete_person_id,
581 x_obsolete_id_row_info => v_merge_record.obsolete_id_row_info,
582 x_current_person_id => v_merge_record.current_person_id,
583 x_current_id_row_info => v_merge_record.current_id_row_info,
584 x_table_alias => v_merge_record.table_alias,
585 x_action_id => v_merge_record.action_id,
586 x_perform_action_ind => v_merge_record.perform_action_ind,
587 x_SMIL_ID => l_SMIL_id );
588 END;
589
590 END LOOP;
591 END;
592 END enrp_ins_merge_log;
593
594 FUNCTION Enrp_Ins_Pre_Pos(
595 p_acad_cal_type IN VARCHAR2 ,
596 p_acad_sequence_number IN NUMBER ,
597 p_person_id IN NUMBER ,
598 p_course_cd IN VARCHAR2 ,
599 p_version_number IN NUMBER ,
600 p_location_cd IN VARCHAR2 ,
601 p_attendance_mode IN VARCHAR2 ,
602 p_attendance_type IN VARCHAR2 ,
603 p_unit_set_cd IN VARCHAR2 ,
604 p_adm_cal_type IN VARCHAR2 ,
605 p_admission_cat IN VARCHAR2 ,
606 p_log_creation_dt IN DATE ,
607 p_units_indicator IN VARCHAR2, -- Added this paramter as part of Core Vs Optional DLD.
608 p_warn_level IN OUT NOCOPY VARCHAR2 ,
609 p_message_name IN OUT NOCOPY VARCHAR2,
610 p_progress_stat IN VARCHAR2,
611 p_progress_outcome_type IN VARCHAR2,
612 p_enr_method IN VARCHAR2 ,
613 p_load_cal_type IN VARCHAR2,
614 p_load_ci_seq_num IN NUMBER)
615 RETURN BOOLEAN AS
616
617 /**********************************/
618 --Change History :-
619 --Who When What
620 --mesriniv 12-sep-2002 Added a new parameter waitlist_manual_ind in insert row of IGS_EN_SU_ATTEMPT
621 -- for Bug 2554109 MINI Waitlist Build for Jan 03 Release
622 --pradhakr 23-Sep-2002 Added a new parameter p_units_indicator in the package spec and body
623 -- as part of Core Vs Optional DLD. Bug# 2581270
624 --svanukur 09-jul-2003 Created the variable l_rec_exist to check if the cursor c_pos returns any
625 -- records into the vt_two_pos. Since this was causing an unhandled exception
626 -- when no pattern of study units have been associated with the program.
627 --ptandon 07-Oct-2003 Modified the cursor c_posu and added logic to derive value for
628 -- core indicator and pass in call to function enrp_vald_inst_sua as
629 -- part of Prevent Dropping Core Units. Enh Bug# 3052432.
630
631 /********************************/
632
633
634
635 BEGIN -- enrp_ins_pre_pos
636 -- Insert pattern of study IGS_PS_UNIT pre-enrolments for a student
637 -- IGS_PS_COURSE attempt within a nominated academic calendar instance.
638 -- This routine may be called with a p_log_creation_dt, in which
639 -- case it will log all errors and warnings to the log (of type PRE-ENROL).
640 DECLARE
641 -- p_warn_level types
642 cst_error CONSTANT VARCHAR2(5) := 'ERROR';
643 cst_minor CONSTANT VARCHAR2(5) := 'MINOR';
644 cst_major CONSTANT VARCHAR2(5) := 'MAJOR';
645 cst_pre_enrol CONSTANT VARCHAR2(10) := 'PRE-ENROL';
646 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
647 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
648 cst_false CONSTANT VARCHAR2(5) := 'FALSE';
649 CURSOR c_aci IS
650 SELECT aci.cal_type,
651 aci.sequence_number,
652 aci.start_dt,
653 aci.end_dt
654 FROM IGS_CA_INST aci
655 WHERE aci.cal_type = p_acad_cal_type AND
656 aci.sequence_number = p_acad_sequence_number;
657 v_aci_rec c_aci%ROWTYPE;
658 CURSOR c_pos ( cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE ) IS
659 SELECT pos.cal_type,
660 pos.sequence_number,
661 pos.always_pre_enrol_ind,
662 pos.number_of_periods,
663 pos.aprvd_ci_sequence_number ,
664 pos.acad_perd_unit_set
665 FROM IGS_PS_PAT_OF_STUDY pos
666 WHERE pos.course_cd = p_course_cd AND
667 pos.version_number = p_version_number AND
668 pos.cal_type = p_acad_cal_type AND
669 ((pos.location_cd IS NULL AND
670 pos.attendance_mode IS NULL AND
671 pos.attendance_type IS NULL AND
672 pos.unit_set_cd IS NULL AND
673 pos.admission_cal_type IS NULL AND
674 pos.admission_cat IS NULL) OR
675 IGS_EN_GEN_005.enrp_get_pos_links(
676 p_location_cd,
677 p_attendance_mode,
678 p_attendance_type,
679 cp_unit_set_cd,
680 p_adm_cal_type,
681 p_admission_cat,
682 pos.location_cd,
683 pos.attendance_mode,
684 pos.attendance_type,
685 pos.unit_set_cd,
686 pos.admission_cal_type,
687 pos.admission_cat) > 0)
688 ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
689 p_location_cd,
690 p_attendance_mode,
691 p_attendance_type,
692 cp_unit_set_cd,
693 p_adm_cal_type,
694 p_admission_cat,
695 pos.location_cd,
696 pos.attendance_mode,
697 pos.attendance_type,
698 pos.unit_set_cd,
699 pos.admission_cal_type,
700 pos.admission_cat) DESC;
701 v_pos_rec c_pos%ROWTYPE;
702 v_stream_pos_rec c_pos%ROWTYPE;
703
704 TYPE t_two_pos IS TABLE OF c_pos%ROWTYPE INDEX BY BINARY_INTEGER;
705 vt_two_pos t_two_pos;
706 v_index BINARY_INTEGER;
707
708 CURSOR c_acad_us (cp_admin_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE) IS
709 SELECT usm.stream_unit_set_Cd
710 FROM igs_en_unit_set_map usm,
711 igs_ps_us_prenr_cfg upc
712 WHERE upc.unit_set_cd = cp_admin_unit_set_cd
713 AND usm.mapping_set_cd = upc.mapping_set_cd
714 AND usm.sequence_no = upc.sequence_no;
715
716 CURSOR c_susa_exists (cp_stream_unit_Set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
717 cp_person_id IGS_AS_SU_SETATMPT.PERSON_ID%TYPE,
718 cp_course_cd IGS_AS_SU_SETATMPT.COURSE_CD%TYPE) IS
719 SELECT 'X'
720 FROM igs_as_su_setatmpt susa
721 WHERE susa.unit_set_cd = cp_stream_unit_set_cd
722 AND susa.person_id = cp_person_id
723 AND susa.course_cd = cp_course_cd
724 AND susa.end_dt IS NULL
725 AND susa.rqrmnts_complete_dt IS NULL;
726
727 v_dummy VARCHAR2(1);
728
729 CURSOR c_am IS
730 SELECT am.govt_attendance_mode
731 FROM IGS_EN_STDNT_PS_ATT sca,
732 IGS_EN_ATD_MODE am
733 WHERE sca.person_id = p_person_id AND
734 sca.course_cd = p_course_cd AND
735 am.attendance_mode = sca.attendance_mode;
736 v_am_rec c_am%ROWTYPE;
737 CURSOR c_posp (
738 cp_sequence_number IGS_PS_PAT_OF_STUDY.sequence_number%TYPE,
739 cp_number_of_periods IGS_PS_PAT_OF_STUDY.number_of_periods%TYPE,
740 cp_period_number NUMBER) IS
741 SELECT posp.acad_period_num,
742 posp.teach_cal_type,
743 posp.sequence_number
744 FROM IGS_PS_PAT_STUDY_PRD posp
745 WHERE posp.pos_sequence_number = cp_sequence_number AND
746 posp.acad_period_num >= cp_period_number AND
747 posp.acad_period_num < (cp_period_number
748 + cp_number_of_periods) AND
749 EXISTS (SELECT 'x'
750 FROM IGS_PS_PAT_STUDY_UNT posu
751 WHERE posp.sequence_number = posu.posp_sequence_number AND
752 posu.unit_cd IS NOT NULL)
753 ORDER BY posp.acad_period_num;
754 CURSOR c_ci (
755 cp_start_dt IGS_CA_INST.start_dt%TYPE) IS
756 SELECT aci.cal_type,
757 aci.sequence_number,
758 aci.start_dt,
759 aci.end_dt
760 FROM IGS_CA_INST aci,
761 IGS_CA_STAT cs
762 WHERE aci.cal_type = p_acad_cal_type AND
763 aci.start_dt > cp_start_dt AND
764 cs.cal_status = aci.cal_status AND
765 cs.s_cal_status = cst_active
766 ORDER BY aci.start_dt;
767 v_ci_rec c_ci%ROWTYPE;
768 CURSOR c_ci2 (
769 cp_cal_type IGS_CA_INST.cal_type%TYPE,
770 cp_sequence_number IGS_CA_INST.sequence_number%TYPE) IS
771 SELECT ci.start_dt
772 FROM IGS_CA_INST ci
773 WHERE ci.cal_type = cp_cal_type AND
774 ci.sequence_number = cp_sequence_number;
775 v_ci2_rec c_ci2%ROWTYPE;
776 CURSOR c_cir (
777 cp_acad_cal_type IGS_CA_INST.cal_type%TYPE,
778 cp_acad_sequence_number IGS_CA_INST.sequence_number%TYPE,
779 cp_teach_cal_type IGS_CA_INST.cal_type%TYPE) IS
780 SELECT tci.cal_type,
781 tci.sequence_number
782 FROM IGS_CA_INST_REL cir,
783 IGS_CA_INST tci,
784 IGS_CA_TYPE cat,
785 IGS_CA_STAT cs
786 WHERE cir.sup_cal_type = cp_acad_cal_type AND
787 sup_ci_sequence_number = cp_acad_sequence_number AND
788 sub_cal_type = cp_teach_cal_type AND
789 tci.cal_type = cir.sub_cal_type AND
790 sequence_number = cir.sub_ci_sequence_number AND
791 cat.cal_type = tci.cal_type AND
792 cat.s_cal_cat = 'TEACHING' AND
793 cs.cal_status = tci.cal_status AND
794 cs.s_cal_status = cst_active
795 ORDER BY tci.start_dt DESC;
796 v_cir_rec c_cir%ROWTYPE;
797
798 -- Modified the cursor c_posu as part Core Vs Optional DLD.
799 -- pradhakr; 23-Sep-2002; Bug# 2581270
800 CURSOR c_posu (
801 cp_sequence_number IGS_PS_PAT_STUDY_PRD.sequence_number%TYPE,
802 cp_core_only VARCHAR2) IS
803 SELECT posu.unit_cd,
804 posu.unit_location_cd,
805 posu.unit_class,
806 posu.core_ind
807
808 FROM IGS_PS_PAT_STUDY_UNT posu
809 WHERE posu.posp_sequence_number = cp_sequence_number AND
810 unit_cd IS NOT NULL
811 AND (
812 ( NVL (core_ind,'N') = cp_core_only
813 AND cp_core_only = 'Y'
814 )
815 OR
816 cp_core_only = 'N'
817 );
818
819
820 --smaddali added this new cursor for YOP-EN dld bug#2156956
821 -- get the number of academic periods within the unit set
822 CURSOR c_num_acad_perd IS
823 SELECT DISTINCT acad_perd
824 FROM igs_en_susa_year_v
825 WHERE person_id = p_person_id
826 AND course_cd = p_course_cd
827 AND unit_set_cd = p_unit_set_cd ;
828 -- end of changes by smaddali
829
830 CURSOR c_uoo_status(p_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
831 SELECT DECODE(sua.unit_attempt_status, 'UNCONFIRM', 'N', 'WAITLISTED', 'Y' , NULL)
832 FROM IGS_EN_SU_ATTEMPT sua
833 WHERE sua.person_id = p_person_id AND
834 sua.course_cd = p_course_cd AND
835 sua.uoo_id = p_uoo_id;
836
837 CURSOR c_rel_type(p_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE) IS
838 SELECT relation_type
839 FROM IGS_PS_UNIT_OFR_OPT
840 WHERE uoo_id = p_uoo_id;
841
842 v_posu_rec c_posu%ROWTYPE;
843 v_attendance_mode VARCHAR2(3) ;
844 v_period_number NUMBER(2) := 0;
845 v_warn_level VARCHAR2(5) ;
846 vp_warn_level VARCHAR2(5) ;
847 v_message_name VARCHAR2(2000) ;
848 v_uoo_id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE;
849 v_last_acad_start_dt IGS_CA_INST.start_dt%TYPE;
850 v_last_acad_period_num NUMBER(2) := 0;
851 v_ci_start_dt DATE;
852 v_ci_end_dt DATE;
853 v_core_only VARCHAR2(1);
854 cst_core_only VARCHAR2(10) := 'CORE_ONLY';
855 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
856 --smaddali added this cursor for YOP-EN dld
857 CURSOR c_sua (
858 cp_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) IS
859 SELECT 'x'
860 FROM IGS_EN_SU_ATTEMPT sua
861 WHERE person_id = p_person_id AND
862 course_cd = p_course_cd AND
863 uoo_id = cp_uoo_id ;
864 v_sua_rec VARCHAR2(1);
865
866 l_rec_exist BOOLEAN;
867 l_core_indicator_code IGS_EN_SU_ATTEMPT.core_indicator_code%TYPE;
868
869 l_waitlist_flag VARCHAR2(1) := NULL;
870 l_rel_type IGS_PS_UNIT_OFR_OPT.relation_type%TYPE;
871 l_enr_uoo_ids VARCHAR2(2000);
872 l_out_uoo_ids VARCHAR2(2000);
873 l_waitlist_uoo_ids VARCHAR2(2000);
874 l_failed_uoo_ids VARCHAR2(2000);
875 l_unit_cds VARCHAR2(2000);
876
877 TYPE l_params_rec IS RECORD (
878 uoo_id IGS_PS_UNIT_OFR_OPT.UOO_ID%TYPE,
879 core_ind IGS_PS_PAT_STUDY_UNT.CORE_IND%TYPE );
880
881 TYPE t_params_table IS TABLE OF l_params_rec INDEX BY BINARY_INTEGER;
882 t_sup_params t_params_table;
883 t_sub_params t_params_table;
884 t_ord_params t_params_table;
885 t_all_params t_params_table;
886 v_sup_index BINARY_INTEGER := 1;
887 v_sub_index BINARY_INTEGER := 1;
888 v_ord_index BINARY_INTEGER := 1;
889 v_all_index BINARY_INTEGER := 1;
890 l_cal_type IGS_PS_UNIT_OFR_OPT.cal_type%TYPE;
891 l_seq_num IGS_PS_UNIT_OFR_OPT.ci_sequence_number%TYPE;
892 l_uoo_Id IGS_PS_UNIT_OFR_OPT.uoo_id%TYPE;
893
894 CURSOR cur_teach_cal(p_uoo_Id igs_ps_unit_ofr_opt.uoo_Id%TYPE) IS
895 SELECT cal_type, ci_sequence_number
896 FROM igs_ps_unit_ofr_opt
897 WHERE uoo_id = p_uoo_id;
898
899
900 BEGIN
901
902 -- Set the default message number
903 p_message_name := NULL;
904 l_rec_exist :=FALSE;
905 SAVEPOINT sp_pos;
906 -- Get the start/end dates from the academic period ?
907 -- required by routine calls lower in the routine.
908 -- The academic period has already been validated.
909 OPEN c_aci;
910 FETCH c_aci INTO v_aci_rec;
911 CLOSE c_aci;
912 -- Determine the pattern of study which applies to the current student.
913 -- Use the first record found. ie. the record with the most links.
914 v_index := 0;
915 OPEN c_pos (p_unit_set_cd);
916 FETCH c_pos INTO vt_two_pos(v_index);
917 IF c_pos%FOUND THEN
918 l_rec_exist := TRUE;
919 END IF;
920 CLOSE c_pos;
921
922 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
923 FOR vc_acad_us_rec IN c_acad_us(p_unit_set_cd) LOOP
924 OPEN c_susa_exists(vc_acad_us_rec.stream_unit_set_cd, p_person_id, p_course_Cd);
925 FETCH c_susa_exists INTO v_dummy;
926 IF c_susa_exists%FOUND THEN
927 l_rec_exist := TRUE;
928 v_index := v_index + 1;
929 OPEN c_pos (vc_acad_us_rec.stream_unit_set_cd);
930 FETCH c_pos INTO vt_two_pos(v_index);
931 CLOSE c_pos;
932 END IF;
933 CLOSE c_susa_exists;
934 END LOOP;
935 END IF;
936
937 -- Get the relevant attendance mode
938 OPEN c_am;
939 FETCH c_am INTO v_am_rec;
940 CLOSE c_am;
941 IF v_am_rec.govt_attendance_mode = '1' THEN
942 v_attendance_mode := 'ON';
943 ELSIF v_am_rec.govt_attendance_mode = '2' THEN
944 v_attendance_mode := 'OFF';
945 ELSE
946 v_attendance_mode := '%';
947 END IF;
948 -- Determine the number of academic periods in which the
949 -- student has been enrolled.
950 --smaddali adding code for YOP-EN dld . bug#2156956
951 -- If year of program mode is enabled then check if 'academic period within unit sets' is checked
952 -- for the pattern of study, if so get number of periods form igs_en_susa_year_v else use original function
953 --l_rec_exist checks if vt_two_pos has any values in it.
954 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' AND l_rec_exist THEN
955 IF NVL(vt_two_pos(vt_two_pos.FIRST).acad_perd_unit_set,'N') = 'Y' AND
956 p_unit_set_cd IS NOT NULL THEN
957 OPEN c_num_acad_perd ;
958 FETCH c_num_acad_perd INTO v_period_number ;
959 CLOSE c_num_acad_perd ;
960
961 v_period_number := v_period_number + 1;
962 ELSE
963 v_period_number := IGS_EN_GEN_004.enrp_get_perd_num(
964 p_person_id,
965 p_course_cd,
966 p_acad_cal_type,
967 p_acad_sequence_number,
968 v_aci_rec.start_dt);
969 END IF;
970
971 ELSE
972
973 v_period_number := IGS_EN_GEN_004.enrp_get_perd_num(
974 p_person_id,
975 p_course_cd,
976 p_acad_cal_type,
977 p_acad_sequence_number,
978 v_aci_rec.start_dt);
979 END IF;
980
981 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y'
982 AND NVL(p_progress_outcome_type,'ADVANCE') = 'REPEATYR'
983 AND NVL(P_PROGRESS_STAT,'ADVANCE') IN ('BOTH','REPEATYR') THEN
984 v_period_number := v_period_number - 1;
985 END IF;
986
987 ---Do following validation only if any units got added to the table.
988
989 IF l_rec_exist THEN
990
991 FOR v_index IN vt_two_pos.FIRST..vt_two_pos.LAST LOOP
992 -- Check that the student is eligible to be pre-enrolled
993 -- in the relevant academic period.
994
995 IF IGS_EN_GEN_005.enrp_get_pos_elgbl(
996 p_acad_cal_type,
997 p_acad_sequence_number,
998 p_person_id,
999 p_course_cd,
1000 p_version_number,
1001 vt_two_pos(v_index).sequence_number,
1002 vt_two_pos(v_index).always_pre_enrol_ind,
1003 v_period_number,
1004 p_log_creation_dt,
1005 v_warn_level,
1006 v_message_name) = cst_false THEN
1007 IF v_message_name = 'IGS_EN_STUD_INELG_PROGRESSION'
1008 AND NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y'
1009 AND NVL(p_progress_outcome_type,'ADVANCE') = 'REPEATYR'
1010 AND NVL(P_PROGRESS_STAT,'ADVANCE') IN ('BOTH','REPEATYR')THEN
1011 NULL; -- do nothing
1012 ELSE
1013 p_warn_level := v_warn_level;
1014 p_message_name := v_message_name;
1015 RETURN FALSE;
1016 END IF;
1017 END IF;
1018 END LOOP;
1019
1020
1021 -- Loop through the relevant pattern of study periods;
1022 -- this is relative to the number of academic periods
1023 -- in which the student has been enrolled
1024 v_last_acad_start_dt := v_aci_rec.start_dt;
1025 v_last_acad_period_num := v_period_number;
1026
1027 FOR v_index IN vt_two_pos.FIRST..vt_two_pos.LAST LOOP
1028
1029 FOR v_posp_rec IN c_posp(
1030 vt_two_pos(v_index).sequence_number,
1031 vt_two_pos(v_index).number_of_periods,
1032 v_period_number) LOOP
1033 -- If the acad_period_num has been incremented,
1034 -- then move the pre-enrolment forward into the
1035 -- next instance of the academic calendar.
1036
1037 IF v_posp_rec.acad_period_num > v_last_acad_period_num THEN
1038 OPEN c_ci(
1039 v_last_acad_start_dt);
1040 FETCH c_ci INTO v_aci_rec;
1041 IF c_ci%NOTFOUND THEN
1042 CLOSE c_ci;
1043 ROLLBACK TO sp_pos;
1044 IF p_log_creation_dt IS NOT NULL THEN
1045 -- If the log creation date is set then log the HECS error
1046 -- This is if the pre-enrolment is being performed in batch.
1047 IGS_GE_GEN_003.genp_ins_log_entry(
1048 cst_pre_enrol,
1049 p_log_creation_dt,
1050 cst_major || ',' ||
1051 TO_CHAR(p_person_id) || ',' ||
1052 p_course_cd,
1053 'IGS_EN_UNABLE_FIND_ACADEMIC',
1054 v_posp_rec.acad_period_num || ',' ||
1055 v_posp_rec.teach_cal_type);
1056 END IF;
1057 p_warn_level := cst_major;
1058 v_message_name := 'IGS_EN_UNABLE_FIND_ACADEMIC';
1059 EXIT;
1060 ELSE
1061 CLOSE c_ci;
1062 -- Make the first record found current (ie. the next period)
1063 v_last_acad_start_dt := v_aci_rec.start_dt;
1064 v_last_acad_period_num := v_posp_rec.acad_period_num;
1065 END IF;
1066 END IF;
1067 -- Determine whether the pattern is eligible to be used in the
1068 -- period being pre-enrolled. This is determined by the
1069 -- aprvd_ci_sequence_number which indicates the latest
1070 -- academic period in which the pattern can be used.
1071 IF vt_two_pos(v_index).aprvd_ci_sequence_number IS NOT NULL THEN
1072 -- Select the start date from the admission calendar referred
1073 -- to by the sequence number ; if the start date is < the
1074 -- academic period being pre-enrolled then
1075 OPEN c_ci2(
1076 vt_two_pos(v_index).cal_type,
1077 vt_two_pos(v_index).aprvd_ci_sequence_number);
1078 FETCH c_ci2 INTO v_ci2_rec;
1079 CLOSE c_ci2;
1080 IF v_aci_rec.start_dt > v_ci2_rec.start_dt THEN
1081 IF p_log_creation_dt IS NOT NULL THEN
1082 -- If the log creation date is set then log the HECS error
1083 -- This is if the pre-enrolment is being performed in batch.
1084 IGS_GE_GEN_003.genp_ins_log_entry(
1085 cst_pre_enrol,
1086 p_log_creation_dt,
1087 cst_major || ',' ||
1088 TO_CHAR(p_person_id) || ',' ||
1089 p_course_cd,
1090 'IGS_EN_UNABLE_PRE_ENR_UA',
1091 v_posp_rec.acad_period_num || ',' ||
1092 v_posp_rec.teach_cal_type);
1093 END IF;
1094 p_warn_level := cst_major;
1095 v_message_name := 'IGS_EN_UNABLE_PRE_ENR_UA';
1096 EXIT;
1097 END IF;
1098 END IF;
1099 -- Select the teaching calendar instance matching the
1100 -- IGS_PS_PAT_STUDY_PRD within the relevant academic
1101 -- calendar instance.
1102 OPEN c_cir (v_aci_rec.cal_type,
1103 v_aci_rec.sequence_number,
1104 v_posp_rec.teach_cal_type);
1105 FETCH c_cir INTO v_cir_rec;
1106
1107 IF c_cir%NOTFOUND THEN
1108 CLOSE c_cir;
1109 ROLLBACK TO sp_pos;
1110 IF p_log_creation_dt IS NOT NULL THEN
1111 -- If the log creation date is set then log the HECS error
1112 -- This is if the pre-enrolment is being performed in batch.
1113 IGS_GE_GEN_003.genp_ins_log_entry(
1114 cst_pre_enrol,
1115 p_log_creation_dt,
1116 cst_major || ',' ||
1117 TO_CHAR(p_person_id) || ',' ||
1118 p_course_cd,
1119 'IGS_EN_UNABLE_LOCATE_TEACHCAL',
1120 v_posp_rec.acad_period_num || ',' ||
1121 v_posp_rec.teach_cal_type);
1122 END IF;
1123 p_warn_level := cst_major;
1124 v_message_name := 'IGS_EN_UNABLE_LOCATE_TEACHCAL';
1125 EXIT;
1126 END IF;
1127 CLOSE c_cir;
1128 -- Validate that the IGS_PS_UNIT attempt period is not prior to the commencement date
1129 -- of the student.
1130
1131 IF IGS_EN_VAL_SUA.enrp_val_sua_ci(p_person_id,
1132 p_course_cd,
1133 v_cir_rec.cal_type,
1134 v_cir_rec.sequence_number,
1135 'UNCONFIRM',
1136 NULL,
1137 'T',
1138 v_message_name) = FALSE THEN
1139 IF vp_warn_level IS NULL THEN
1140 vp_warn_level := cst_minor;
1141 p_message_name := 'IGS_EN_COULD_NOT_PREENR';
1142 END IF;
1143 ELSE
1144
1145 -- Check whether the user wants to enroll only core units or all the units.
1146 -- If the user wants to pick only the core units then set the value for the
1147 -- varible v_core_only to 'Y' and pass the value to the cursor c_posu,
1148 -- so that it picks only core units. else if all the units are required
1149 -- then set the value for the varible v_core_only to 'N'.
1150 -- Added the following IF condition as part of Core Vs Optional DLD
1151 -- pradhakr; 23-Sep-2002; Bug# 2581270
1152
1153 IF p_units_indicator = cst_core_only THEN
1154 v_core_only := 'Y';
1155 ELSIF p_units_indicator = 'Y' THEN
1156 v_core_only := 'N';
1157 ELSE
1158 v_core_only := 'X';
1159 END IF;
1160
1161 -- Loop through the units that need to be pre-enrolled.
1162 FOR v_posu_rec IN c_posu(
1163 v_posp_rec.sequence_number,
1164 v_core_only) LOOP
1165 -- Validate whether there is anything preventing it
1166 -- being pre-enrolled ; including encumbrances and
1167 -- advanced standing.
1168 -- Modified by : jbegum
1169 -- Modification:
1170 -- The function Enrp_Val_Sua_Pre was being called from the package IGS_EN_GEN_013.But the
1171 -- exact replica of it was found in the package IGS_EN_VAL_SUA.Hence this function was removed from
1172 -- the package IGS_EN_GEN_013 and instead its replica in the package IGS_EN_VAL_SUA is getting called.
1173 IF NOT IGS_EN_VAL_SUA.enrp_val_sua_pre(
1174 p_person_id,
1175 p_course_cd,
1176 v_posu_rec.unit_cd,
1177 p_log_creation_dt,
1178 v_warn_level,
1179 v_message_name) THEN
1180 IF vp_warn_level IS NULL OR
1181 (vp_warn_level = cst_minor AND
1182 v_warn_level IN (cst_major,
1183 cst_error)) OR
1184 (vp_warn_level = cst_major AND
1185 v_warn_level = cst_error) THEN
1186 vp_warn_level := v_warn_level;
1187 p_message_name := v_message_name;
1188 END IF;
1189 -- continue with the next record
1190 ELSE
1191 -- Call routine to get the applicable uoo in
1192 -- which to pre-enrol the student.
1193 IF NOT IGS_EN_GEN_005.enrp_get_pre_uoo(
1194 v_posu_rec.unit_cd,
1195 v_cir_rec.cal_type,
1196 v_cir_rec.sequence_number,
1197 v_posu_rec.unit_location_cd,
1198 v_posu_rec.UNIT_CLASS,
1199 v_attendance_mode,
1200 p_location_cd,
1201 v_uoo_id) THEN
1202 IF p_log_creation_dt IS NOT NULL THEN
1203 -- If the log creation date is set then log the HECS error
1204 -- This is if the pre-enrolment is being performed in batch.
1205 IGS_GE_GEN_003.genp_ins_log_entry(
1206 cst_pre_enrol,
1207 p_log_creation_dt,
1208 cst_minor || ',' ||
1209 TO_CHAR(p_person_id) || ',' ||
1210 p_course_cd,
1211 'IGS_EN_UNABLE_LOCATE_UOO',
1212 v_posp_rec.acad_period_num || ',' ||
1213 v_posp_rec.teach_cal_type || ',' ||
1214 v_posu_rec.unit_cd || ',' ||
1215 v_posu_rec.unit_location_cd || ',' ||
1216 v_posu_rec.UNIT_CLASS);
1217 END IF;
1218 IF vp_warn_level IS NULL THEN
1219 vp_warn_level := cst_minor;
1220 p_message_name := 'IGS_EN_UNABLE_LOCATE_UOO';
1221 END IF;
1222 ELSE
1223
1224 OPEN c_rel_type(v_uoo_id);
1225 FETCH c_rel_type INTO l_rel_type;
1226 CLOSE c_rel_type;
1227
1228 IF l_rel_type= 'SUPERIOR' THEN
1229 t_sup_params(v_sup_index).uoo_id := v_uoo_id;
1230 t_sup_params(v_sup_index).core_ind := v_posu_rec.core_ind;
1231 v_sup_index :=v_sup_index+1;
1232 ELSIF l_rel_type = 'SUBORDINATE' THEN
1233 t_sub_params(v_sub_index).uoo_id := v_uoo_id;
1234 t_sub_params(v_sub_index).core_ind := v_posu_rec.core_ind;
1235 v_sub_index := v_sub_index+1;
1236 ELSE
1237 t_ord_params(v_ord_index).uoo_id := v_uoo_id;
1238 t_ord_params(v_ord_index).core_ind := v_posu_rec.core_ind;
1239 v_ord_index := v_ord_index+1;
1240 END IF;
1241
1242 END IF;
1243 END IF;
1244 END LOOP; -- posu loop
1245 -- add all the uoo_ids to one pl/sql table, with superiors, first, subordinate next and the rest .
1246 --combine all of this in one pl/sql table
1247
1248 IF t_sup_params.count > 0 THEN
1249 FOR i in 1 .. t_sup_params.count LOOP
1250 t_all_params(v_all_index) := t_sup_params(i);
1251 v_all_index := v_all_index + 1;
1252 END LOOP;
1253 END IF;
1254 IF t_sub_params.count > 0 THEN
1255 FOR i in 1 .. t_sub_params.count LOOP
1256 t_all_params(v_all_index) := t_sub_params(i);
1257 v_all_index := v_all_index + 1;
1258 END LOOP;
1259 END IF;
1260 IF t_ord_params.count > 0 THEN
1261 FOR i in 1 .. t_ord_params.count LOOP
1262 t_all_params(v_all_index) := t_ord_params(i);
1263 v_all_index := v_all_index + 1;
1264 END LOOP;
1265 END IF;
1266
1267
1268 IF t_all_params.count > 0 THEN
1269 FOR i in 1.. t_all_params.count LOOP
1270 -- smaddali added this check to see if unit attempt already exists in YOP-EN dld
1271 -- bug#2156956 as this was throwing duplicate record exception
1272 -- Check if the IGS_PS_UNIT attempt already exists.
1273
1274 OPEN c_sua( t_all_params(i).uoo_id);
1275 FETCH c_sua INTO v_sua_rec;
1276 IF c_sua%NOTFOUND THEN
1277 CLOSE c_sua;
1278 IGS_CA_GEN_001.CALP_GET_CI_DATES(v_posp_rec.teach_cal_type,
1279 v_cir_rec.sequence_number,
1280 v_ci_start_dt,v_ci_end_dt);
1281 -- Check whether the profile is set or not
1282 IF NVL(fnd_profile.value('IGS_EN_CORE_VAL'),'N') = 'N' THEN
1283 l_core_indicator_code := NULL;
1284 ELSE
1285 -- If the profile is set, derive the value of core indicator based
1286 -- on the value of core unit indicator in pattern of study periods table.
1287 IF t_all_params(i).core_ind = 'Y' THEN
1288 l_core_indicator_code := 'CORE';
1289 ELSE
1290 l_core_indicator_code := 'OPTIONAL';
1291 END IF;
1292 END IF;
1293
1294
1295 -- Add the unconfirmed IGS_PS_UNIT attempt to the students record.
1296
1297 IF igs_en_gen_010.enrp_vald_inst_sua(p_person_id => p_person_id,
1298 p_course_cd => p_course_cd,
1299 p_unit_cd => NULL,
1300 p_version_number => NULL,
1301 p_teach_cal_type => NULL,
1302 p_teach_seq_num => NULL,
1303 p_load_cal_type => p_load_cal_type,
1304 p_load_seq_num => p_load_ci_seq_num,
1305 p_location_cd => NULL,
1306 p_unit_class => NULL,
1307 p_uoo_id => t_all_params(i).uoo_id,
1308 p_enr_method => p_enr_method,
1309 p_core_indicator_code => l_core_indicator_code,
1310 p_message => v_message_name) THEN
1311 IF v_message_name IS NOT NULL THEN
1312 p_warn_level := 'MINOR';
1313 p_message_name := v_message_name;
1314 END IF;
1315 --call enr_sub_units to enroll any subordinate units that are marked as default enroll
1316
1317 l_waitlist_flag := NULL;
1318 OPEN c_uoo_status(t_all_params(i).uoo_id);
1319 FETCH c_uoo_status INTO l_waitlist_flag;
1320 CLOSE c_uoo_status;
1321
1322 --fetch the teach cal type and teach seq number
1323 l_cal_type := NULL;
1324 l_seq_num := NULL;
1325 OPEN cur_teach_cal(t_all_params(i).uoo_id);
1326 FETCH cur_teach_cal INTO l_cal_type, l_seq_num;
1327 CLOSE cur_teach_cal;
1328
1329 l_enr_uoo_ids := NULL;
1330 IF i < t_all_params.count THEN
1331 FOR j in (i+1).. t_all_params.count LOOP
1332 l_enr_uoo_ids := to_char(t_all_params(i).uoo_id) || ',';
1333 END LOOP;
1334 IF l_enr_uoo_ids IS NOT NULL THEN
1335 l_enr_uoo_ids := substr(l_enr_uoo_ids,1, LENGTH(l_enr_uoo_ids) -1);
1336 END IF;
1337 END IF;
1338
1339
1340 igs_en_val_sua.enr_sub_units(
1341 p_person_id => p_person_id,
1342 p_course_cd => p_course_cd,
1343 p_uoo_id => t_all_params(i).uoo_id,
1344 p_waitlist_flag => l_waitlist_flag,
1345 p_load_cal_type => p_load_cal_type ,
1346 p_load_seq_num => p_load_ci_seq_num,
1347 p_enrollment_date => SYSDATE,
1348 p_enrollment_method =>p_enr_method,
1349 p_enr_uoo_ids => l_enr_uoo_ids,
1350 p_uoo_ids => l_out_uoo_ids,
1351 p_waitlist_uoo_ids => l_waitlist_uoo_ids,
1352 p_failed_uoo_ids => l_failed_uoo_ids);
1353
1354
1355 IF l_failed_uoo_ids IS NOT NULL THEN
1356 l_unit_cds := NULL;
1357 --following function returns a string of units codes for teh passed in string of uoo_ids
1358 l_unit_cds := igs_en_gen_018.enrp_get_unitcds(l_failed_uoo_ids);
1359 p_warn_level := cst_error;
1360 p_message_name := 'IGS_EN_BLK_SUB_FAILED'||'*'||l_unit_cds;
1361 END IF;
1362
1363
1364 ELSE --igs_en_gen_010.enrp_vald_inst_sua returned false.
1365 p_warn_level := cst_error;
1366 p_message_name := v_message_name;
1367
1368 RETURN FALSE;
1369 END IF;
1370 ELSE
1371 CLOSE c_sua ;
1372 END IF; -- If unit attempt already exists
1373
1374 END LOOP;
1375 END IF; -- IF t_all_params.count > 0 THEN
1376
1377 END IF;
1378 END LOOP; -- v_posp_rec IN c_posp
1379 END LOOP; -- vt_two_pos
1380 END IF; --l_record_exists
1381
1382 IF vp_warn_level IS NOT NULL THEN
1383 p_warn_level := vp_warn_level;
1384 END IF;
1385 IF v_message_name IS NOT NULL THEN
1386 p_message_name := v_message_name;
1387 RETURN FALSE;
1388 END IF;
1389 -- Return the default value
1390 RETURN TRUE;
1391 EXCEPTION
1392 WHEN OTHERS THEN
1393 IF c_cir%ISOPEN THEN
1394 CLOSE c_cir;
1395 END IF;
1396 IF c_aci%ISOPEN THEN
1397 CLOSE c_aci;
1398 END IF;
1399 IF c_ci%ISOPEN THEN
1400 CLOSE c_ci;
1401 END IF;
1402 IF c_ci2%ISOPEN THEN
1403 CLOSE c_ci2;
1404 END IF;
1405 IF c_posp%ISOPEN THEN
1406 CLOSE c_posp;
1407 END IF;
1408 IF c_posu%ISOPEN THEN
1409 CLOSE c_posu;
1410 END IF;
1411 IF c_am%ISOPEN THEN
1412 CLOSE c_am;
1413 END IF;
1414 IF c_pos%ISOPEN THEN
1415 CLOSE c_pos;
1416 END IF;
1417 RAISE;
1418 END;
1419 END enrp_ins_pre_pos;
1420
1421 FUNCTION Enrp_Ins_Scae_Trnsfr(
1422 p_person_id IN NUMBER ,
1423 p_course_cd IN VARCHAR2 ,
1424 p_cal_type IN VARCHAR2 ,
1425 p_ci_sequence_number IN NUMBER ,
1426 p_enrolment_cat IN VARCHAR2 ,
1427 p_message_name OUT NOCOPY VARCHAR2)
1428 RETURN BOOLEAN AS
1429
1430 BEGIN -- enrp_ins_scae_trnsfr
1431 -- Insert a record into the IGS_AS_SC_ATMPT_ENR table.
1432 DECLARE
1433 v_dummy VARCHAR2(1);
1434 CURSOR c_scae IS
1435 SELECT 'x'
1436 FROM IGS_AS_SC_ATMPT_ENR scae
1437 WHERE scae.person_id = p_person_id AND
1438 scae.course_cd = p_course_cd AND
1439 scae.cal_type = p_cal_type AND
1440 scae.ci_sequence_number = p_ci_sequence_number;
1441 BEGIN
1442 p_message_name := NULL;
1443 -- 1. Check parameters.
1444 IF (p_person_id IS NULL OR
1445 p_course_cd IS NULL OR
1446 p_cal_type IS NULL OR
1447 p_ci_sequence_number IS NULL) THEN
1448 RETURN TRUE;
1449 END IF;
1450 -- 2. Check that record does not already exist.
1451 OPEN c_scae;
1452 FETCH c_scae INTO v_dummy;
1453 IF (c_scae%FOUND) THEN
1454 CLOSE c_scae;
1455 p_message_name := 'IGS_GE_RECORD_ALREADY_EXISTS ';
1456 RETURN FALSE;
1457 ELSE
1458 -- 3. Insert the record.
1459 DECLARE
1460 l_rowid VARCHAR2(25);
1461 BEGIN
1462
1463 IGS_AS_SC_ATMPT_ENR_PKG.INSERT_ROW(
1464 x_rowid => l_rowid,
1465 x_person_id => p_person_id,
1466 x_course_cd => p_course_cd,
1467 x_cal_type => p_cal_type,
1468 x_ci_sequence_number => p_ci_sequence_number,
1469 x_enrolment_cat => p_enrolment_cat,
1470 X_ENROLLED_DT => NULL,
1471 X_ENR_FORM_DUE_DT => NULL,
1472 X_ENR_PCKG_PROD_DT => NULL,
1473 X_ENR_FORM_RECEIVED_DT => NULL );
1474 END;
1475 END IF;
1476 -- 4. Set p_message_name to (0)
1477 CLOSE c_scae;
1478 RETURN TRUE;
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 IF (c_scae%ISOPEN) THEN
1482 CLOSE c_scae;
1483 END IF;
1484 RAISE;
1485 END;
1486 END enrp_ins_scae_trnsfr;
1487
1488 FUNCTION Enrp_Ins_Sca_Cah(
1489 p_person_id IN NUMBER ,
1490 p_course_cd IN VARCHAR2 ,
1491 p_student_confirmed_ind IN VARCHAR2 ,
1492 p_commencement_dt IN DATE ,
1493 p_old_attendance_type IN VARCHAR2 ,
1494 p_message_name OUT NOCOPY VARCHAR2)
1495 RETURN BOOLEAN AS
1496
1497 BEGIN -- enrp_ins_sca_cah
1498 -- This modules inserts into IGS_RE_CDT_ATT_HIST when
1499 -- IGS_EN_STDNT_PS_ATT.attendance_type is changed.
1500 -- The following is validated:
1501 -- Research IGS_RE_CANDIDATURE exists.
1502 -- Research IGS_RE_CANDIDATURE requires attendance history details to be retained
1503 DECLARE
1504 v_sequence_number IGS_RE_CANDIDATURE.sequence_number%TYPE;
1505 v_attendance_percentage IGS_RE_CANDIDATURE.attendance_percentage%TYPE;
1506 v_hist_start_dt IGS_RE_CDT_ATT_HIST.hist_start_dt%TYPE;
1507 v_hist_end_dt IGS_RE_CDT_ATT_HIST.hist_end_dt%TYPE;
1508 v_attendance_percentage_new IGS_RE_CDT_ATT_HIST.attendance_percentage%TYPE;
1509 v_cah_sequence_number IGS_RE_CDT_ATT_HIST.sequence_number%TYPE;
1510
1511 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
1512
1513 CURSOR c_ca IS
1514 SELECT ca.sequence_number,
1515 ca.attendance_percentage
1516 FROM IGS_RE_CANDIDATURE ca
1517 WHERE ca.person_id = p_person_id AND
1518 ca.sca_course_cd = p_course_cd;
1519 CURSOR c_cah (
1520 cp_ca_sequence_number IGS_RE_CDT_ATT_HIST.ca_sequence_number%TYPE) IS
1521 SELECT cah.hist_end_dt
1522 FROM IGS_RE_CDT_ATT_HIST cah
1523 WHERE cah.person_id = p_person_id AND
1524 cah.ca_sequence_number = cp_ca_sequence_number
1525 ORDER BY cah.hist_end_dt DESC;
1526 CURSOR c_cah2 (
1527 cp_sequence_number IGS_RE_CDT_ATT_HIST.sequence_number%TYPE) IS
1528 SELECT NVL(MAX(cah2.sequence_number),0)+1
1529 FROM IGS_RE_CDT_ATT_HIST cah2
1530 WHERE cah2.person_id = p_person_id AND
1531 cah2.ca_sequence_number = cp_sequence_number;
1532 BEGIN
1533 -- set default value
1534 p_message_name := NULL;
1535 IF p_student_confirmed_ind = 'Y' THEN
1536 -- Determine if student IGS_PS_COURSE attempt has a research IGS_RE_CANDIDATURE
1537 OPEN c_ca;
1538 FETCH c_ca INTO v_sequence_number,
1539 v_attendance_percentage;
1540 IF c_ca%NOTFOUND THEN
1541 CLOSE c_ca;
1542 RETURN TRUE;
1543 END IF;
1544 CLOSE c_ca;
1545 OPEN c_cah (
1546 v_sequence_number);
1547 FETCH c_cah INTO v_hist_end_dt;
1548 IF c_cah%NOTFOUND THEN
1549 CLOSE c_cah;
1550 -- First history inserted,
1551 -- start date should be set the student IGS_PS_COURSE attempt Commencement date
1552 IF p_commencement_dt IS NULL THEN
1553 p_message_name := 'IGS_RE_FIRST_HIST_CANT_INSERT';
1554 RETURN FALSE;
1555 ELSE
1556 v_hist_start_dt := p_commencement_dt;
1557 END IF;
1558 ELSE
1559 CLOSE c_cah;
1560 -- History start date should be set to latest history end date plus a day
1561 v_hist_start_dt := v_hist_end_dt + 1;
1562 END IF;
1563 IF v_hist_start_dt >= TRUNC(SYSDATE) THEN
1564 -- Changes not required in history, more than one change in a day or
1565 -- Commencement date has not been reached
1566 RETURN TRUE;
1567 END IF;
1568 -- Determine attendance percentage
1569 v_attendance_percentage_new := IGS_RE_GEN_001.resp_get_ca_att(
1570 p_person_id,
1571 p_course_cd,
1572 TRUNC(SYSDATE),
1573 v_sequence_number,
1574 p_old_attendance_type,
1575 v_attendance_percentage);
1576 -- Get next sequence number
1577 OPEN c_cah2(
1578 v_sequence_number);
1579 FETCH c_cah2 INTO v_cah_sequence_number;
1580 CLOSE c_cah2;
1581 DECLARE
1582 l_rowid VARCHAR2(25);
1583 BEGIN
1584
1585 IGS_RE_CDT_ATT_HIST_PKG.INSERT_ROW(
1586 x_rowid => l_rowid,
1587 x_person_id => p_person_id,
1588 x_sequence_number => v_cah_sequence_number,
1589 x_ca_sequence_number => v_sequence_number,
1590 x_hist_start_dt => v_hist_start_dt,
1591 x_hist_end_dt => TRUNC(SYSDATE) - 1,
1592 x_attendance_type => p_old_attendance_type,
1593 x_attendance_percentage => v_attendance_percentage_new,
1594 x_mode => 'R',
1595 x_org_id => l_org_id);
1596 END;
1597 END IF;
1598 RETURN TRUE;
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 IF c_ca%ISOPEN THEN
1602 CLOSE c_ca;
1603 END IF;
1604 IF c_cah%ISOPEN THEN
1605 CLOSE c_cah;
1606 END IF;
1607 RAISE;
1608 END;
1609 END; --enrp_ins_sca_cah
1610
1611 -- Modified by : jbegum
1612 -- Added 4 new parameters p_new_last_date_of_attendance , p_old_last_date_of_attendance , p_new_dropped_by , p_old_dropped_by
1613 -- as part of Enhancement Bug # 1832130
1614
1615 -- Modified by : kkillams
1616 -- Added 10 new parameters p_new_primary_program_type,p_old_primary_program_type,p_new_primary_prog_type_source,p_old_primary_prog_type_source,
1617 -- p_new_catalog_cal_type,p_old_catalog_cal_type,p_new_catalog_seq_num,p_old_catalog_seq_num,p_new_key_program,p_old_key_program
1618 -- as part of Enhancement Bug # 2027984
1619
1620 -- Modified by pradhakr
1621 -- Added 4 new parametes p_new_override_cmpl_dt, p_old_override_cmpl_dt, p_new_manual_ovr_cmpl_dt_ind,
1622 -- p_old_manual_ovr_cmpl_dt_ind as part of the build ENCR015. Bug# 2158654
1623
1624 -- Modified by rvangala
1625 -- Added 4 new parameters p_new_coo_id,p_old_coo_id,p_new_igs_pr_class_std_id,p_old_igs_pr_class_std_id
1626 --Change History:
1627 --Who When What
1628 --stutta 11-Dec-2004 Replace insert_row of igs_as_sc_attempt_h_pkg with add_row. This is done
1629 -- avoid record already exists msg when more than one history record is created
1630 -- within the same second. Bug # 4061929
1631 PROCEDURE Enrp_Ins_Sca_Hist(
1632 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
1633 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
1634 p_new_version_number IN IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
1635 p_old_version_number IN IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
1636 p_new_cal_type IN IGS_EN_STDNT_PS_ATT_ALL.cal_type%TYPE ,
1637 p_old_cal_type IN IGS_EN_STDNT_PS_ATT_ALL.cal_type%TYPE ,
1638 p_new_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.location_cd%TYPE ,
1639 p_old_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.location_cd%TYPE ,
1640 p_new_attendance_mode IN IGS_EN_STDNT_PS_ATT_ALL.attendance_mode%TYPE ,
1641 p_old_attendance_mode IN IGS_EN_STDNT_PS_ATT_ALL.attendance_mode%TYPE ,
1642 p_new_attendance_type IN IGS_EN_STDNT_PS_ATT_ALL.attendance_type%TYPE ,
1643 p_old_attendance_type IN IGS_EN_STDNT_PS_ATT_ALL.attendance_type%TYPE ,
1644 p_new_student_confirmed_ind IN IGS_EN_STDNT_PS_ATT_ALL.student_confirmed_ind%TYPE ,
1645 p_old_student_confirmed_ind IN IGS_EN_STDNT_PS_ATT_ALL.student_confirmed_ind%TYPE ,
1646 p_new_commencement_dt IN IGS_EN_STDNT_PS_ATT_ALL.commencement_dt%TYPE ,
1647 p_old_commencement_dt IN IGS_EN_STDNT_PS_ATT_ALL.commencement_dt%TYPE ,
1648 p_new_course_attempt_status IN IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE ,
1649 p_old_course_attempt_status IN IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE ,
1650 p_new_progression_status IN VARCHAR2 ,
1651 p_old_progression_status IN VARCHAR2 ,
1652 p_new_derived_att_type IN IGS_EN_STDNT_PS_ATT_ALL.derived_att_type%TYPE ,
1653 p_old_derived_att_type IN IGS_EN_STDNT_PS_ATT_ALL.derived_att_type%TYPE ,
1654 p_new_derived_att_mode IN IGS_EN_STDNT_PS_ATT_ALL.derived_att_mode%TYPE ,
1655 p_old_derived_att_mode IN IGS_EN_STDNT_PS_ATT_ALL.derived_att_mode%TYPE ,
1656 p_new_provisional_ind IN IGS_EN_STDNT_PS_ATT_ALL.provisional_ind%TYPE ,
1657 p_old_provisional_ind IN IGS_EN_STDNT_PS_ATT_ALL.provisional_ind%TYPE ,
1658 p_new_discontinued_dt IN IGS_EN_STDNT_PS_ATT_ALL.discontinued_dt%TYPE ,
1659 p_old_discontinued_dt IN IGS_EN_STDNT_PS_ATT_ALL.discontinued_dt%TYPE ,
1660 p_new_dscntntn_reason_cd IN IGS_EN_STDNT_PS_ATT_ALL.discontinuation_reason_cd%TYPE ,
1661 p_old_dscntntn_reason_cd IN IGS_EN_STDNT_PS_ATT_ALL.discontinuation_reason_cd%TYPE ,
1662 p_new_lapsed_dt IN DATE ,
1663 p_old_lapsed_dt IN DATE ,
1664 p_new_funding_source IN IGS_EN_STDNT_PS_ATT_ALL.funding_source%TYPE ,
1665 p_old_funding_source IN IGS_EN_STDNT_PS_ATT_ALL.funding_source%TYPE ,
1666 p_new_exam_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.exam_location_cd%TYPE ,
1667 p_old_exam_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.exam_location_cd%TYPE ,
1668 p_new_derived_cmpltn_yr IN IGS_EN_STDNT_PS_ATT_ALL.derived_completion_yr%TYPE ,
1669 p_old_derived_cmpltn_yr IN IGS_EN_STDNT_PS_ATT_ALL.derived_completion_yr%TYPE ,
1670 p_new_derived_cmpltn_perd IN IGS_EN_STDNT_PS_ATT_ALL.derived_completion_perd%TYPE ,
1671 p_old_derived_cmpltn_perd IN IGS_EN_STDNT_PS_ATT_ALL.derived_completion_perd%TYPE ,
1672 p_new_nominated_cmpltn_yr IN IGS_EN_STDNT_PS_ATT_ALL.nominated_completion_yr%TYPE ,
1673 p_old_nominated_cmpltn_yr IN IGS_EN_STDNT_PS_ATT_ALL.nominated_completion_yr%TYPE ,
1674 p_new_nominated_cmpltn_perd IN IGS_EN_STDNT_PS_ATT_ALL.nominated_completion_perd%TYPE ,
1675 p_old_nominated_cmpltn_perd IN IGS_EN_STDNT_PS_ATT_ALL.nominated_completion_perd%TYPE ,
1676 p_new_rule_check_ind IN IGS_EN_STDNT_PS_ATT_ALL.rule_check_ind%TYPE ,
1677 p_old_rule_check_ind IN IGS_EN_STDNT_PS_ATT_ALL.rule_check_ind%TYPE ,
1678 p_new_waive_option_check_ind IN IGS_EN_STDNT_PS_ATT_ALL.waive_option_check_ind%TYPE ,
1679 p_old_waive_option_check_ind IN IGS_EN_STDNT_PS_ATT_ALL.waive_option_check_ind%TYPE ,
1680 p_new_last_rule_check_dt IN IGS_EN_STDNT_PS_ATT_ALL.last_rule_check_dt%TYPE ,
1681 p_old_last_rule_check_dt IN IGS_EN_STDNT_PS_ATT_ALL.last_rule_check_dt%TYPE ,
1682 p_new_publish_outcomes_ind IN IGS_EN_STDNT_PS_ATT_ALL.publish_outcomes_ind%TYPE ,
1683 p_old_publish_outcomes_ind IN IGS_EN_STDNT_PS_ATT_ALL.publish_outcomes_ind%TYPE ,
1684 p_new_crs_rqrmnt_complete_ind IN IGS_EN_STDNT_PS_ATT_ALL.course_rqrmnt_complete_ind%TYPE ,
1685 p_old_crs_rqrmnt_complete_ind IN IGS_EN_STDNT_PS_ATT_ALL.course_rqrmnt_complete_ind%TYPE ,
1686 p_new_crs_rqrmnts_complete_dt IN DATE ,
1687 p_old_crs_rqrmnts_complete_dt IN DATE ,
1688 p_new_s_completed_source_type IN VARCHAR2 ,
1689 p_old_s_completed_source_type IN VARCHAR2 ,
1690 p_new_override_time_limitation IN IGS_EN_STDNT_PS_ATT_ALL.override_time_limitation%TYPE ,
1691 p_old_override_time_limitation IN IGS_EN_STDNT_PS_ATT_ALL.override_time_limitation%TYPE ,
1692 p_new_advanced_standing_ind IN IGS_EN_STDNT_PS_ATT_ALL.advanced_standing_ind%TYPE ,
1693 p_old_advanced_standing_ind IN IGS_EN_STDNT_PS_ATT_ALL.advanced_standing_ind%TYPE ,
1694 p_new_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
1695 p_old_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
1696 p_new_self_help_group_ind IN VARCHAR2 ,
1697 p_old_self_help_group_ind IN VARCHAR2 ,
1698 p_new_correspondence_cat IN VARCHAR2 ,
1699 p_old_correspondence_cat IN IGS_EN_STDNT_PS_ATT_ALL.correspondence_cat%TYPE ,
1700 p_new_adm_adm_appl_number IN NUMBER ,
1701 p_old_adm_adm_appl_number IN NUMBER ,
1702 p_new_adm_nominated_course_cd IN VARCHAR2 ,
1703 p_old_adm_nominated_course_cd IN VARCHAR2 ,
1704 p_new_adm_sequence_number IN NUMBER ,
1705 p_old_adm_sequence_number IN NUMBER ,
1706 p_new_update_who IN IGS_EN_STDNT_PS_ATT_ALL.last_updated_by%TYPE ,
1707 p_old_update_who IN IGS_EN_STDNT_PS_ATT_ALL.last_updated_by%TYPE ,
1708 p_new_update_on IN IGS_EN_STDNT_PS_ATT_ALL.last_update_date%TYPE ,
1709 p_old_update_on IN IGS_EN_STDNT_PS_ATT_ALL.last_update_date%TYPE ,
1710 p_new_last_date_of_attendance IN IGS_EN_STDNT_PS_ATT_ALL.last_date_of_attendance%TYPE ,
1711 p_old_last_date_of_attendance IN IGS_EN_STDNT_PS_ATT_ALL.last_date_of_attendance%TYPE ,
1712 p_new_dropped_by IN IGS_EN_STDNT_PS_ATT_ALL.dropped_by%TYPE ,
1713 p_old_dropped_by IN IGS_EN_STDNT_PS_ATT_ALL.dropped_by%TYPE ,
1714 p_new_primary_program_type IN IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE ,
1715 p_old_primary_program_type IN IGS_EN_STDNT_PS_ATT_ALL.primary_program_type%TYPE ,
1716 p_new_primary_prog_type_source IN IGS_EN_STDNT_PS_ATT_ALL.primary_prog_type_source%TYPE,
1717 p_old_primary_prog_type_source IN IGS_EN_STDNT_PS_ATT_ALl.primary_prog_type_source%TYPE ,
1718 p_new_catalog_cal_type IN IGS_EN_STDNT_PS_ATT_ALl.catalog_cal_type%TYPE ,
1719 p_old_catalog_cal_type IN IGS_EN_STDNT_PS_ATT_ALl.catalog_cal_type%TYPE ,
1720 p_new_catalog_seq_num IN IGS_EN_STDNT_PS_ATT_ALl.catalog_seq_num%TYPE,
1721 p_old_catalog_seq_num IN IGS_EN_STDNT_PS_ATT_ALl.catalog_seq_num%TYPE ,
1722 p_new_key_program IN IGS_EN_STDNT_PS_ATT_ALl.key_program%TYPE ,
1723 p_old_key_program IN IGS_EN_STDNT_PS_ATT_ALl.key_program%TYPE ,
1724 p_new_override_cmpl_dt IN IGS_EN_STDNT_PS_ATT_ALL.override_cmpl_dt%TYPE ,
1725 p_old_override_cmpl_dt IN IGS_EN_STDNT_PS_ATT_ALL.override_cmpl_dt%TYPE ,
1726 p_new_manual_ovr_cmpl_dt_ind IN IGS_EN_STDNT_PS_ATT_ALL.manual_ovr_cmpl_dt_ind%TYPE ,
1727 p_old_manual_ovr_cmpl_dt_ind IN IGS_EN_STDNT_PS_ATT_ALL.manual_ovr_cmpl_dt_ind%TYPE,
1728 p_new_coo_id IN IGS_EN_STDNT_PS_ATT_ALL.coo_id%TYPE,
1729 p_old_coo_id IN IGS_EN_STDNT_PS_ATT_ALL.coo_id%TYPE,
1730 p_new_igs_pr_class_std_id IGS_EN_STDNT_PS_ATT_ALL.igs_pr_class_std_id%TYPE,
1731 p_old_igs_pr_class_std_id IGS_EN_STDNT_PS_ATT_ALL.igs_pr_class_std_id%TYPE
1732 )
1733 AS
1734
1735 BEGIN
1736 DECLARE
1737 r_scah IGS_AS_SC_ATTEMPT_H%ROWTYPE;
1738 v_create_history BOOLEAN :=FALSE;
1739 v_fs_description IGS_FI_FUND_SRC.description%TYPE;
1740 v_elo_description IGS_AD_LOCATION.description%TYPE;
1741 v_fc_description IGS_FI_FEE_CAT.description%TYPE;
1742 v_cc_description IGS_CO_CAT.description%TYPE;
1743
1744 CURSOR c_find_fs_desc IS
1745 SELECT description
1746 FROM IGS_FI_FUND_SRC
1747 WHERE funding_source = r_scah.funding_source;
1748 CURSOR c_find_elo_desc IS
1749 SELECT description
1750 FROM IGS_AD_LOCATION
1751 WHERE location_cd = r_scah.exam_location_cd;
1752 CURSOR c_find_fc_desc IS
1753 SELECT description
1754 FROM IGS_FI_FEE_CAT
1755 WHERE fee_cat = r_scah.fee_cat;
1756 CURSOR c_find_cc_desc IS
1757 SELECT description
1758 FROM IGS_CO_CAT
1759 WHERE correspondence_cat = r_scah.correspondence_cat;
1760 BEGIN
1761
1762 -- check if a history is required
1763 IF p_new_version_number <> p_old_version_number THEN
1764 r_scah.version_number := p_old_version_number;
1765 v_create_history := TRUE;
1766 END IF;
1767 IF p_new_cal_type <> p_old_cal_type THEN
1768 r_scah.cal_type := p_old_cal_type;
1769 v_create_history := TRUE;
1770 END IF;
1771 IF p_new_location_cd <> p_old_location_cd THEN
1772 r_scah.location_cd := p_old_location_cd;
1773 v_create_history := TRUE;
1774 END IF;
1775 IF p_new_attendance_mode <> p_old_attendance_mode THEN
1776 r_scah.attendance_mode := p_old_attendance_mode;
1777 v_create_history := TRUE;
1778 END IF;
1779 IF p_new_attendance_type <> p_old_attendance_type THEN
1780 r_scah.attendance_type := p_old_attendance_type;
1781 v_create_history := TRUE;
1782 END IF;
1783 IF p_new_student_confirmed_ind <> p_old_student_confirmed_ind THEN
1784 r_scah.student_confirmed_ind := p_old_student_confirmed_ind;
1785 v_create_history := TRUE;
1786 END IF;
1787 IF NVL(p_new_commencement_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
1788 NVL(p_old_commencement_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
1789 r_scah.commencement_dt := p_old_commencement_dt;
1790 v_create_history := TRUE;
1791 END IF;
1792 IF NVL(p_new_course_attempt_status, 'NULL') <>
1793 NVL(p_old_course_attempt_status, 'NULL') THEN
1794 r_scah.course_attempt_status := p_old_course_attempt_status;
1795 v_create_history := TRUE;
1796 END IF;
1797 IF NVL(p_new_progression_status, 'NULL') <>
1798 NVL(p_old_progression_status, 'NULL') THEN
1799 r_scah.progression_status := p_old_progression_status;
1800 v_create_history := TRUE;
1801 END IF;
1802 IF NVL(p_new_derived_att_type, 'NULL') <>
1803 NVL( p_old_derived_att_type, 'NULL') THEN
1804 r_scah.derived_att_type := p_old_derived_att_type;
1805 v_create_history := TRUE;
1806 END IF;
1807 IF NVL(p_new_derived_att_mode, 'NULL') <>
1808 NVL(p_old_derived_att_mode, 'NULL') THEN
1809 r_scah.derived_att_mode := p_old_derived_att_mode;
1810 v_create_history := TRUE;
1811 END IF;
1812 IF p_new_provisional_ind <> p_old_provisional_ind THEN
1813 r_scah.provisional_ind := p_old_provisional_ind;
1814 v_create_history := TRUE;
1815 END IF;
1816
1817 IF NVL(p_new_discontinued_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
1818 NVL(p_old_discontinued_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
1819 r_scah.discontinued_dt := p_old_discontinued_dt;
1820 v_create_history := TRUE;
1821 END IF;
1822 IF NVL(p_new_dscntntn_reason_cd, 'NULL') <>
1823 NVL(p_old_dscntntn_reason_cd, 'NULL') THEN
1824 r_scah.discontinuation_reason_cd := p_old_dscntntn_reason_cd;
1825 v_create_history := TRUE;
1826 END IF;
1827 IF NVL(p_new_lapsed_dt,TO_DATE('01/01/4000','DD/MM/YYYY')) <>
1828 NVL(p_old_lapsed_dt,TO_DATE('01/01/4000','DD/MM/YYYY')) THEN
1829 r_scah.lapsed_dt := p_old_lapsed_dt;
1830 v_create_history := TRUE;
1831 END IF;
1832 IF NVL(p_new_funding_source, 'NULL') <> NVL(p_old_funding_source, 'NULL') THEN
1833 r_scah.funding_source := p_old_funding_source;
1834 IF NVL(p_old_funding_source, 'NULL') <> 'NULL' THEN
1835 -- get the funding source description
1836 OPEN c_find_fs_desc;
1837 FETCH c_find_fs_desc INTO r_scah.fs_description;
1838 CLOSE c_find_fs_desc;
1839 END IF;
1840 v_create_history := TRUE;
1841 END IF;
1842 IF NVL(p_new_exam_location_cd, 'NULL') <>
1843 NVL(p_old_exam_location_cd, 'NULL') THEN
1844 r_scah.exam_location_cd := p_old_exam_location_cd;
1845 IF NVL(p_old_exam_location_cd, 'NULL') <> 'NULL' THEN
1846 -- get the exam IGS_AD_LOCATION description
1847 OPEN c_find_elo_desc;
1848 FETCH c_find_elo_desc INTO r_scah.elo_description;
1849 CLOSE c_find_elo_desc;
1850 END IF;
1851 v_create_history := TRUE;
1852 END IF;
1853 IF NVL(p_new_derived_cmpltn_yr, 0) <>
1854 NVL(p_old_derived_cmpltn_yr, 0) THEN
1855 r_scah.derived_completion_yr := p_old_derived_cmpltn_yr;
1856 v_create_history := TRUE;
1857 END IF;
1858
1859 IF NVL(p_new_derived_cmpltn_perd, 'NULL') <>
1860 NVL(p_old_derived_cmpltn_perd, 'NULL') THEN
1861 r_scah.derived_completion_perd := p_old_derived_cmpltn_perd;
1862 v_create_history := TRUE;
1863 END IF;
1864
1865 IF NVL(p_new_nominated_cmpltn_yr, 0) <>
1866 NVL(p_old_nominated_cmpltn_yr, 0) THEN
1867 r_scah.nominated_completion_yr := p_old_nominated_cmpltn_yr;
1868 v_create_history := TRUE;
1869 END IF;
1870 IF NVL(p_new_nominated_cmpltn_perd, 'NULL') <>
1871 NVL(p_old_nominated_cmpltn_perd, 'NULL') THEN
1872 r_scah.nominated_completion_perd := p_old_nominated_cmpltn_perd;
1873 v_create_history := TRUE;
1874 END IF;
1875 IF p_new_rule_check_ind <> p_old_rule_check_ind THEN
1876 r_scah.rule_check_ind := p_old_rule_check_ind;
1877 v_create_history := TRUE;
1878 END IF;
1879 IF p_new_waive_option_check_ind <> p_old_waive_option_check_ind THEN
1880 r_scah.waive_option_check_ind := p_old_waive_option_check_ind;
1881 v_create_history := TRUE;
1882 END IF;
1883 IF NVL(p_new_last_rule_check_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
1884 NVL(p_old_last_rule_check_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
1885 r_scah.last_rule_check_dt := p_old_last_rule_check_dt;
1886 v_create_history := TRUE;
1887 END IF;
1888 IF p_new_publish_outcomes_ind <> p_old_publish_outcomes_ind THEN
1889 r_scah.publish_outcomes_ind := p_old_publish_outcomes_ind;
1890 v_create_history := TRUE;
1891 END IF;
1892 IF p_new_crs_rqrmnt_complete_ind <> p_old_crs_rqrmnt_complete_ind THEN
1893 r_scah.course_rqrmnt_complete_ind := p_old_crs_rqrmnt_complete_ind;
1894 v_create_history := TRUE;
1895 END IF;
1896 IF NVL(p_new_crs_rqrmnts_complete_dt, TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
1897 NVL(p_old_crs_rqrmnts_complete_dt,
1898 TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
1899 r_scah.course_rqrmnts_complete_dt := p_old_crs_rqrmnts_complete_dt;
1900 v_create_history := TRUE;
1901 END IF;
1902 IF NVL(p_new_s_completed_source_type, 'NULL') <>
1903 NVL(p_old_s_completed_source_type, 'NULL') THEN
1904 r_scah.s_completed_source_type := p_old_s_completed_source_type;
1905 v_create_history := TRUE;
1906 END IF;
1907 IF NVL(p_new_override_time_limitation, 0) <>
1908 NVL(p_old_override_time_limitation, 0) THEN
1909 r_scah.override_time_limitation := p_old_override_time_limitation;
1910 v_create_history := TRUE;
1911 END IF;
1912 IF p_new_advanced_standing_ind <> p_old_advanced_standing_ind THEN
1913 r_scah.advanced_standing_ind := p_old_advanced_standing_ind;
1914 v_create_history := TRUE;
1915 END IF;
1916 IF NVL(p_new_fee_cat, 'NULL') <> NVL(p_old_fee_cat, 'NULL') THEN
1917 r_scah.fee_cat := p_old_fee_cat;
1918 IF NVL(p_old_fee_cat, 'NULL') <> 'NULL' THEN
1919 -- get the fee category description
1920 OPEN c_find_fc_desc;
1921 FETCH c_find_fc_desc INTO r_scah.fc_description;
1922 CLOSE c_find_fc_desc;
1923 END IF;
1924 v_create_history := TRUE;
1925 END IF;
1926 IF NVL(p_new_correspondence_cat, 'NULL') <>
1927 NVL(p_old_correspondence_cat, 'NULL') THEN
1928 r_scah.correspondence_cat := p_old_correspondence_cat;
1929 IF NVL(p_old_correspondence_cat, 'NULL') <> 'NULL' THEN
1930 -- get the correspondence category description
1931 OPEN c_find_cc_desc;
1932 FETCH c_find_cc_desc INTO r_scah.cc_description;
1933 CLOSE c_find_cc_desc;
1934 END IF;
1935 v_create_history := TRUE;
1936 END IF;
1937 IF p_new_self_help_group_ind <> p_old_self_help_group_ind THEN
1938 r_scah.self_help_group_ind := p_old_self_help_group_ind;
1939 v_create_history := TRUE;
1940 END IF;
1941 IF NVL(p_new_adm_adm_appl_number,-1) <>
1942 NVL(p_old_adm_adm_appl_number,-1) THEN
1943 r_scah.adm_admission_appl_number := p_old_adm_adm_appl_number;
1944 v_create_history := TRUE;
1945 END IF;
1946 IF NVL(p_new_adm_nominated_course_cd,'NULL') <>
1947 NVL(p_old_adm_nominated_course_cd,'NULL') THEN
1948 r_scah.adm_nominated_course_cd := p_old_adm_nominated_course_cd;
1949 v_create_history := TRUE;
1950 END IF;
1951 IF NVL(p_new_adm_sequence_number,-1) <>
1952 NVL(p_old_adm_sequence_number,-1) THEN
1953 r_scah.adm_sequence_number := p_old_adm_sequence_number;
1954 v_create_history := TRUE;
1955 END IF;
1956
1957 -- Modified by : jbegum
1958 -- Added this IF condition as part of Enhancement Bug # 1832130
1959
1960 IF NVL(p_new_last_date_of_attendance, TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
1961 NVL(p_old_last_date_of_attendance, TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
1962 r_scah.last_date_of_attendance := p_old_last_date_of_attendance;
1963 v_create_history := TRUE;
1964 END IF;
1965
1966 -- Modified by : jbegum
1967 -- Added this IF condition as part of Enhancement Bug # 1832130
1968
1969 IF NVL(p_new_dropped_by,'NULL') <>
1970 NVL(p_old_dropped_by,'NULL') THEN
1971 r_scah.dropped_by := p_old_dropped_by;
1972 v_create_history := TRUE;
1973 END IF;
1974
1975 -- Modified by : kkillams
1976 -- Added this IF condition(s) as part of Enhancement Bug # 2027984
1977
1978 IF NVL(p_new_primary_program_type,'NULL') <>
1979 NVL(p_old_primary_program_type,'NULL') THEN
1980 r_scah.primary_program_type :=p_old_primary_program_type;
1981 v_create_history := TRUE;
1982 END IF;
1983 IF NVL(p_new_primary_prog_type_source,'NULL') <>
1984 NVL( p_old_primary_prog_type_source ,'NULL') THEN
1985 r_scah.primary_prog_type_source := p_old_primary_prog_type_source ;
1986 v_create_history := TRUE;
1987 END IF;
1988 IF NVL(p_new_catalog_cal_type,'NULL') <>
1989 NVL(p_old_catalog_cal_type ,'NULL') THEN
1990 r_scah.catalog_cal_type := p_old_catalog_cal_type ;
1991 v_create_history := TRUE;
1992 END IF;
1993
1994 IF NVL(p_new_catalog_seq_num,-1) <>
1995 NVL(p_old_catalog_seq_num ,-1) THEN
1996 r_scah.catalog_seq_num := p_old_catalog_seq_num ;
1997 v_create_history := TRUE;
1998 END IF;
1999 IF NVL(p_new_key_program ,'NULL') <>
2000 NVL(p_old_key_program ,'NULL') THEN
2001 r_scah.key_program := p_old_key_program ;
2002 v_create_history := TRUE;
2003 END IF;
2004 IF NVL(p_new_override_cmpl_dt ,TO_DATE('01/01/1900', 'DD/MM/YYYY')) <>
2005 NVL(p_old_override_cmpl_dt ,TO_DATE('01/01/1900', 'DD/MM/YYYY')) THEN
2006 r_scah.override_cmpl_dt := p_old_override_cmpl_dt ;
2007 v_create_history := TRUE;
2008 END IF;
2009 IF NVL(p_new_manual_ovr_cmpl_dt_ind ,'NULL') <>
2010 NVL(p_old_manual_ovr_cmpl_dt_ind ,'NULL') THEN
2011 r_scah.manual_ovr_cmpl_dt_ind := p_old_manual_ovr_cmpl_dt_ind ;
2012 v_create_history := TRUE;
2013 END IF;
2014
2015 -- validate coo_id and igs_pr_class_std_id
2016 IF NVL(p_new_coo_id,-1) <> NVL(p_old_coo_id,-1) THEN
2017 r_scah.coo_id := p_old_coo_id;
2018 v_create_history := TRUE;
2019 END IF;
2020
2021 IF NVL(p_new_igs_pr_class_std_id,-1) <> NVL(p_old_igs_pr_class_std_id,-1) THEN
2022 r_scah.igs_pr_class_std_id := p_old_igs_pr_class_std_id;
2023 v_create_history := TRUE;
2024 END IF;
2025
2026
2027 -- create a history record if a column has changed value
2028 IF v_create_history = TRUE THEN
2029 r_scah.person_id := p_person_id;
2030 r_scah.course_cd := p_course_cd;
2031 r_scah.hist_start_dt := p_old_update_on;
2032 r_scah.hist_end_dt := p_new_update_on;
2033 r_scah.hist_who := p_old_update_who;
2034 -- remove one second from the hist_start_dt value
2035 -- when the hist_start_dt and hist_end_dt are the same
2036 -- to avoid a primary key constraint from occurring
2037 -- when saving the record
2038 IF (r_scah.hist_start_dt = r_scah.hist_end_dt) THEN
2039 r_scah.hist_end_dt := r_scah.hist_start_dt + 1 /
2040 (60*24*60);
2041 END IF;
2042
2043 DECLARE
2044 l_rowid VARCHAR2(25);
2045 l_org_id NUMBER := igs_ge_gen_003.get_org_id;
2046 BEGIN
2047 -- ADD_ROW is used instead of INSERT_ROW, so that the history record is updated in
2048 -- case more than one history record is created within a second. More than one record
2049 -- are being created while recursive update_row of ps att table is performed.
2050 IGS_AS_SC_ATTEMPT_H_PKG.ADD_ROW (
2051 x_rowid => l_rowid,
2052 x_person_id => r_scah.person_id,
2053 x_course_cd => r_scah.course_cd,
2054 x_hist_start_dt => r_scah.hist_start_dt,
2055 x_hist_end_dt => r_scah.hist_end_dt,
2056 x_hist_who => r_scah.hist_who,
2057 x_version_number => r_scah.version_number,
2058 x_cal_type => r_scah.cal_type,
2059 x_location_cd => r_scah.location_cd,
2060 x_attendance_mode => r_scah.attendance_mode,
2061 x_attendance_type => r_scah.attendance_type,
2062 x_student_confirmed_ind => r_scah.student_confirmed_ind,
2063 x_commencement_dt => r_scah.commencement_dt,
2064 x_course_attempt_status => r_scah.course_attempt_status,
2065 x_progression_status => r_scah.progression_status,
2066 x_derived_att_type => r_scah.derived_att_type,
2067 x_derived_att_mode => r_scah.derived_att_mode,
2068 x_provisional_ind => r_scah.provisional_ind,
2069 x_discontinued_dt => r_scah.discontinued_dt,
2070 x_discontinuation_reason_cd => r_scah.discontinuation_reason_cd,
2071 x_lapsed_dt => r_scah.lapsed_dt,
2072 x_funding_source => r_scah.funding_source,
2073 x_fs_description => r_scah.fs_description,
2074 x_exam_location_cd => r_scah.exam_location_cd,
2075 x_elo_description => r_scah.elo_description,
2076 x_derived_completion_yr => r_scah.derived_completion_yr,
2077 x_derived_completion_perd => r_scah.derived_completion_perd,
2078 x_nominated_completion_yr => r_scah.nominated_completion_yr,
2079 x_nominated_completion_perd => r_scah.nominated_completion_perd,
2080 x_rule_check_ind => r_scah.rule_check_ind,
2081 x_waive_option_check_ind => r_scah.waive_option_check_ind,
2082 x_last_rule_check_dt => r_scah.last_rule_check_dt,
2083 x_publish_outcomes_ind => r_scah.publish_outcomes_ind,
2084 x_course_rqrmnt_complete_ind => r_scah.course_rqrmnt_complete_ind,
2085 x_course_rqrmnts_complete_dt => r_scah.course_rqrmnts_complete_dt,
2086 x_s_completed_source_type => r_scah.s_completed_source_type,
2087 x_override_time_limitation => r_scah.override_time_limitation,
2088 x_advanced_standing_ind => r_scah.advanced_standing_ind,
2089 x_fee_cat => r_scah.fee_cat,
2090 x_fc_description => r_scah.fc_description,
2091 x_correspondence_cat => r_scah.correspondence_cat,
2092 x_cc_description => r_scah.cc_description,
2093 x_self_help_group_ind => r_scah.self_help_group_ind,
2094 x_adm_admission_appl_number => r_scah.adm_admission_appl_number,
2095 x_adm_nominated_course_cd => r_scah.adm_nominated_course_cd,
2096 x_adm_sequence_number => r_scah.adm_sequence_number,
2097 x_org_id => l_org_id,
2098 x_last_date_of_attendance => r_scah.last_date_of_attendance,
2099 x_dropped_by => r_scah.dropped_by,
2100 x_primary_program_type =>r_scah.primary_program_type,
2101 x_primary_prog_type_source =>r_scah.primary_prog_type_source,
2102 x_catalog_cal_type =>r_scah.catalog_cal_type,
2103 x_catalog_seq_num =>r_scah.catalog_seq_num,
2104 x_key_program =>r_scah.key_program,
2105 x_override_cmpl_dt => r_scah.override_cmpl_dt,
2106 x_manual_ovr_cmpl_dt_ind => r_scah.manual_ovr_cmpl_dt_ind,
2107 x_coo_id => r_scah.coo_id,
2108 x_igs_pr_class_std_id => r_scah.igs_pr_class_std_id
2109 );
2110 END;
2111 END IF;
2112 END;
2113
2114 END enrp_ins_sca_hist;
2115
2116 FUNCTION Enrp_Ins_Scho_Dflt(
2117 p_person_id IN NUMBER ,
2118 p_course_cd IN VARCHAR2 ,
2119 p_hecs_payment_option IN VARCHAR2 ,
2120 p_acad_cal_type IN VARCHAR2 ,
2121 p_acad_sequence_number IN NUMBER ,
2122 p_message_name OUT NOCOPY VARCHAR2)
2123 RETURN BOOLEAN AS
2124
2125 BEGIN -- enrp_ins_scho_dflt_temp
2126 -- This routine will attempt to insert a default IGS_EN_STDNTPSHECSOP
2127 -- record based on just the hecs payment option (which has typically be
2128 -- collected during admissions).
2129 -- This routine will only default EXEMPT HECS payment options. It is not
2130 -- logical to default anything but exempt, as a HECS payment options form must
2131 -- be submitted anyway.
2132 -- It is assumed that the exempt categories don?t have applicable visa flags,
2133 -- tax file number details or differential HECS details. These are all
2134 -- defaulted to 'N' or NULL.
2135 DECLARE
2136 e_resource_busy_exception EXCEPTION;
2137 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
2138 v_message_name VARCHAR2(30);
2139 v_govt_hecs_payment_option IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE;
2140 v_expire_aftr_acdmc_perd_ind
2141 IGS_FI_HECS_PAY_OPTN.expire_aftr_acdmc_perd_ind%TYPE;
2142 v_scho_rec IGS_EN_STDNTPSHECSOP%ROWTYPE;
2143 v_start_dt IGS_EN_STDNTPSHECSOP.start_dt%TYPE;
2144 v_end_dt IGS_EN_STDNTPSHECSOP.end_dt%TYPE;
2145 v_hecs_payment_option IGS_EN_STDNTPSHECSOP.hecs_payment_option%TYPE;
2146 v_outside_aus_res_ind IGS_EN_STDNTPSHECSOP.outside_aus_res_ind%TYPE;
2147 v_update_existing BOOLEAN;
2148
2149 CURSOR c_hpo IS
2150 SELECT hpo.govt_hecs_payment_option,
2151 hpo.expire_aftr_acdmc_perd_ind
2152 FROM IGS_FI_HECS_PAY_OPTN hpo
2153 WHERE hpo.hecs_payment_option = p_hecs_payment_option;
2154 CURSOR c_ghpo_exempt (
2155 cp_govt_hecs_payment_option
2156 IGS_FI_HECS_PAY_OPTN.govt_hecs_payment_option%TYPE) IS
2157 SELECT 'x'
2158 FROM IGS_FI_GOV_HEC_PA_OP ghpo
2159 WHERE ghpo.govt_hecs_payment_option = cp_govt_hecs_payment_option AND
2160 ghpo.s_hecs_payment_type = 'EXEMPT';
2161 v_is_exempt VARCHAR2(1);
2162 -- ensure that NULL end date is the max value.
2163 CURSOR c_scho_open_end IS
2164 SELECT ROWID,
2165 IGS_EN_STDNTPSHECSOP.*
2166 FROM IGS_EN_STDNTPSHECSOP
2167 WHERE person_id = p_person_id AND
2168 course_cd = p_course_cd AND
2169 end_dt IS NULL
2170 FOR UPDATE OF end_dt NOWAIT;
2171
2172 c_scho_open_end_rec c_scho_open_end%ROWTYPE;
2173
2174 CURSOR c_scho_max_enddt IS
2175 SELECT scho.end_dt,
2176 scho.start_dt,
2177 scho.hecs_payment_option
2178 FROM IGS_EN_STDNTPSHECSOP scho
2179 WHERE scho.person_id = p_person_id AND
2180 scho.course_cd = p_course_cd
2181 ORDER BY scho.end_dt DESC;
2182 CURSOR c_scho_update IS
2183 SELECT ROWID, IGS_EN_STDNTPSHECSOP.*
2184 FROM IGS_EN_STDNTPSHECSOP
2185 WHERE person_id = p_person_id AND
2186 course_cd = p_course_cd
2187 FOR UPDATE OF hecs_payment_option NOWAIT;
2188
2189 c_scho_update_rec c_scho_update%ROWTYPE;
2190
2191
2192 CURSOR c_ci IS
2193 SELECT ci.end_dt
2194 FROM IGS_CA_INST ci
2195 WHERE ci.cal_type = p_acad_cal_type AND
2196 ci.sequence_number = p_acad_sequence_number;
2197 v_ci_end_dt IGS_CA_INST.end_dt%TYPE;
2198 BEGIN
2199 -- Set the default message number
2200 p_message_name := NULL;
2201 -- Select the type of HECS option from the hecs_payment_option table
2202 OPEN c_hpo;
2203 FETCH c_hpo INTO v_govt_hecs_payment_option,
2204 v_expire_aftr_acdmc_perd_ind;
2205 IF c_hpo%NOTFOUND THEN
2206 -- Error will be picked up by calling routine
2207 CLOSE c_hpo;
2208 RETURN TRUE;
2209 ELSE
2210 CLOSE c_hpo;
2211 IF v_govt_hecs_payment_option IS NULL THEN
2212 -- The selected HPO must be mapped onto a govt payment option.
2213 p_message_name := 'IGS_EN_NOT_PREENR_HECS_NOTMAP';
2214 RETURN FALSE;
2215 END IF;
2216 OPEN c_ghpo_exempt(
2217 v_govt_hecs_payment_option);
2218 FETCH c_ghpo_exempt INTO v_is_exempt;
2219 -- Only exempt payment options can be defaulted through this process.
2220 -- Return an error if a non-exempt type has been passed.
2221 IF c_ghpo_exempt%NOTFOUND THEN
2222 CLOSE c_ghpo_exempt;
2223 p_message_name := 'IGS_EN_NOT_PREENR_HECS_EXEMPT';
2224 RETURN FALSE;
2225 END IF;
2226 CLOSE c_ghpo_exempt;
2227 END IF; -- c_hpo%NOTFOUND
2228 -- Default the fields which have no associated logic in the following sections.
2229 v_scho_rec.end_dt := NULL;
2230 v_scho_rec.differential_hecs_ind := 'N';
2231 v_scho_rec.diff_hecs_ind_update_who := NULL;
2232 v_scho_rec.diff_hecs_ind_update_on := NULL;
2233 v_scho_rec.diff_hecs_ind_update_comments := NULL;
2234 v_scho_rec.outside_aus_res_ind := 'N';
2235 v_scho_rec.nz_citizen_ind := 'N';
2236 v_scho_rec.nz_citizen_less2yr_ind := 'N';
2237 v_scho_rec.nz_citizen_not_res_ind := 'N';
2238 v_scho_rec.safety_net_ind := 'N';
2239 v_scho_rec.tax_file_number := NULL;
2240 v_scho_rec.tax_file_number_collected_dt := NULL;
2241 v_scho_rec.tax_file_invalid_dt := NULL;
2242 v_scho_rec.tax_file_certificate_number := NULL;
2243 -- Establish the start date for the HECS record.
2244 -- If required, end the previous record.
2245 v_update_existing := FALSE;
2246 OPEN c_scho_open_end;
2247
2248 FETCH c_scho_open_end INTO c_scho_open_end_rec;
2249
2250 -- IGS_EN_STDNTPSHECSOP.end_dt IS NOT NULL OR
2251 -- c_scho_open_end%NOTFOUND
2252 IF c_scho_open_end%NOTFOUND THEN
2253 CLOSE c_scho_open_end;
2254 OPEN c_scho_max_enddt;
2255 FETCH c_scho_max_enddt INTO v_start_dt,
2256 v_end_dt,
2257 v_hecs_payment_option;
2258 IF c_scho_max_enddt%NOTFOUND THEN
2259 CLOSE c_scho_max_enddt;
2260 v_start_dt := TRUNC(SYSDATE);
2261 v_scho_rec.end_dt := NULL;
2262 ELSE
2263 CLOSE c_scho_max_enddt;
2264 -- If a HECS option record already exists which is past
2265 -- the current date then a new one cannot be defaulted.
2266 IF (v_end_dt > SYSDATE OR
2267 v_start_dt > SYSDATE) THEN
2268 IF v_hecs_payment_option = p_hecs_payment_option THEN
2269 p_message_name := NULL;
2270 RETURN TRUE;
2271 ELSE
2272 p_message_name := 'IGS_EN_NOT_PREENR_HECS_OPTION'
2273 ;
2274 RETURN FALSE;
2275 END IF;
2276 ELSE -- end_dt <= SYSDATE
2277 v_start_dt := TRUNC(SYSDATE);
2278 v_scho_rec.end_dt := NULL;
2279 END IF;
2280 END IF;
2281 ELSE -- c_scho_open_end%FOUND. End_dt is null.
2282 -- If the details which are being defaulted are different then end the
2283 -- record. If not, leave the record intact and exit the routine.
2284 IF (v_hecs_payment_option = p_hecs_payment_option AND
2285 v_outside_aus_res_ind = v_scho_rec.outside_aus_res_ind) THEN
2286 RETURN TRUE;
2287 ELSIF v_start_dt = TRUNC(SYSDATE) THEN
2288 v_update_existing := TRUE;
2289 ELSE
2290 -- End the existing record prior to inserting a new one.
2291
2292 IGS_EN_STDNTPSHECSOP_PKG.UPDATE_ROW(
2293 X_ROWID => c_scho_open_end_rec.ROWID,
2294 X_PERSON_ID => c_scho_open_end_rec.PERSON_ID,
2295 X_COURSE_CD => c_scho_open_end_rec.COURSE_CD,
2296 X_START_DT => c_scho_open_end_rec.START_DT,
2297 X_END_DT => TRUNC(SYSDATE)-1,
2298 X_HECS_PAYMENT_OPTION => c_scho_open_end_rec.HECS_PAYMENT_OPTION,
2299 X_DIFFERENTIAL_HECS_IND => c_scho_open_end_rec.DIFFERENTIAL_HECS_IND,
2300 X_DIFF_HECS_IND_UPDATE_WHO => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_WHO,
2301 X_DIFF_HECS_IND_UPDATE_ON => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_ON ,
2302 X_OUTSIDE_AUS_RES_IND => c_scho_open_end_rec.OUTSIDE_AUS_RES_IND,
2303 X_NZ_CITIZEN_IND => c_scho_open_end_rec.NZ_CITIZEN_IND,
2304 X_NZ_CITIZEN_LESS2YR_IND => c_scho_open_end_rec.NZ_CITIZEN_LESS2YR_IND,
2305 X_NZ_CITIZEN_NOT_RES_IND => c_scho_open_end_rec.NZ_CITIZEN_NOT_RES_IND,
2306 X_SAFETY_NET_IND => c_scho_open_end_rec.SAFETY_NET_IND,
2307 X_TAX_FILE_NUMBER => c_scho_open_end_rec.TAX_FILE_NUMBER ,
2308 X_TAX_FILE_NUMBER_COLLECTED_DT => c_scho_open_end_rec.TAX_FILE_NUMBER_COLLECTED_DT,
2309 X_TAX_FILE_INVALID_DT => c_scho_open_end_rec.TAX_FILE_INVALID_DT,
2310 X_TAX_FILE_CERTIFICATE_NUMBER => c_scho_open_end_rec.TAX_FILE_CERTIFICATE_NUMBER,
2311 X_DIFF_HECS_IND_UPDATE_COMMENT => c_scho_open_end_rec.DIFF_HECS_IND_UPDATE_COMMENTS,
2312 X_MODE => 'R'
2313 );
2314
2315
2316 END IF;
2317 CLOSE c_scho_open_end;
2318 v_start_dt := TRUNC(SYSDATE);
2319 v_scho_rec.end_dt := NULL;
2320 END IF; -- c_scho_open_end%NOTFOUND
2321 -- Test whether the new option must expire at the end of the academic period.
2322 -- If so, set the end date accordingly.
2323 IF v_expire_aftr_acdmc_perd_ind = 'Y' THEN
2324 OPEN c_ci;
2325 FETCH c_ci INTO v_ci_end_dt;
2326 IF c_ci%FOUND THEN
2327 IF v_ci_end_dt >= TRUNC(SYSDATE) THEN
2328 v_scho_rec.end_dt := v_ci_end_dt;
2329 END IF;
2330 END IF;
2331 CLOSE c_ci;
2332 END IF;
2333 -- Call routine to validate the defaulted HECS option.
2334 -- If fails then defaulting is not possible.
2335 IF IGS_EN_VAL_SCHO.enrp_val_scho_all(
2336 p_person_id,
2337 p_course_cd,
2338 v_start_dt,
2339 v_scho_rec.end_dt,
2340 p_hecs_payment_option,
2341 v_scho_rec.differential_hecs_ind,
2342 v_scho_rec.diff_hecs_ind_update_who,
2343 v_scho_rec.diff_hecs_ind_update_on,
2344 v_scho_rec.diff_hecs_ind_update_comments,
2345 v_scho_rec.outside_aus_res_ind,
2346 v_scho_rec.nz_citizen_ind,
2347 v_scho_rec.nz_citizen_less2yr_ind,
2348 v_scho_rec.nz_citizen_not_res_ind,
2349 v_scho_rec.safety_net_ind,
2350 v_scho_rec.tax_file_number,
2351 v_scho_rec.tax_file_number_collected_dt,
2352 v_scho_rec.tax_file_invalid_dt,
2353 v_scho_rec.tax_file_certificate_number,
2354 v_message_name) = FALSE THEN
2355 p_message_name := v_message_name;
2356 RETURN FALSE;
2357 END IF;
2358 -- Insert default into scho
2359 v_scho_rec.person_id := p_person_id;
2360 v_scho_rec.course_cd := p_course_cd;
2361 v_scho_rec.hecs_payment_option := p_hecs_payment_option;
2362 IF v_update_existing = FALSE THEN
2363
2364 DECLARE
2365 l_rowid VARCHAR2(25);
2366 BEGIN
2367
2368 IGS_EN_STDNTPSHECSOP_PKG.INSERT_ROW(
2369 x_rowid => l_rowid,
2370 x_person_id => v_scho_rec.person_id,
2371 x_course_cd => v_scho_rec.course_cd,
2372 x_start_dt => v_start_dt,
2373 x_end_dt => v_scho_rec.end_dt,
2374 x_hecs_payment_option => v_scho_rec.hecs_payment_option,
2375 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
2376 x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
2377 x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
2378 x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
2379 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
2380 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
2381 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
2382 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
2383 x_safety_net_ind => v_scho_rec.safety_net_ind,
2384 x_tax_file_number => v_scho_rec.tax_file_number,
2385 x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
2386 x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
2387 x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number);
2388 END;
2389
2390 ELSE
2391 -- Get lock on record before updating. If lock couldn't be obtained,
2392 -- return FALSE with message number 4064
2393 OPEN c_scho_update;
2394
2395 FETCH c_scho_update INTO c_scho_update_rec;
2396
2397 IF c_scho_update%FOUND THEN
2398
2399
2400 IGS_EN_STDNTPSHECSOP_PKG.UPDATE_ROW(
2401 X_ROWID => c_scho_update_rec.ROWID,
2402 X_PERSON_ID => c_scho_update_rec.PERSON_ID,
2403 X_COURSE_CD => c_scho_update_rec.COURSE_CD,
2404 X_START_DT => c_scho_update_rec.START_DT,
2405 X_END_DT => v_scho_rec.end_dt,
2406 X_HECS_PAYMENT_OPTION => p_hecs_payment_option,
2407 X_DIFFERENTIAL_HECS_IND => v_scho_rec.differential_hecs_ind,
2408 X_DIFF_HECS_IND_UPDATE_WHO => v_scho_rec.DIFF_HECS_IND_UPDATE_WHO,
2409 X_DIFF_HECS_IND_UPDATE_ON => v_scho_rec.DIFF_HECS_IND_UPDATE_ON ,
2410 X_OUTSIDE_AUS_RES_IND => v_scho_rec.OUTSIDE_AUS_RES_IND,
2411 X_NZ_CITIZEN_IND => v_scho_rec.NZ_CITIZEN_IND,
2412 X_NZ_CITIZEN_LESS2YR_IND => v_scho_rec.NZ_CITIZEN_LESS2YR_IND,
2413 X_NZ_CITIZEN_NOT_RES_IND => v_scho_rec.NZ_CITIZEN_NOT_RES_IND,
2414 X_SAFETY_NET_IND => v_scho_rec.SAFETY_NET_IND,
2415 X_TAX_FILE_NUMBER => v_scho_rec.TAX_FILE_NUMBER ,
2416 X_TAX_FILE_NUMBER_COLLECTED_DT => v_scho_rec.TAX_FILE_NUMBER_COLLECTED_DT,
2417 X_TAX_FILE_INVALID_DT => v_scho_rec.TAX_FILE_INVALID_DT,
2418 X_TAX_FILE_CERTIFICATE_NUMBER => v_scho_rec.TAX_FILE_CERTIFICATE_NUMBER,
2419 X_DIFF_HECS_IND_UPDATE_COMMENT => v_scho_rec.DIFF_HECS_IND_UPDATE_COMMENTS,
2420 X_MODE => 'R'
2421 );
2422
2423
2424
2425 END IF;
2426 CLOSE c_scho_update;
2427 END IF;
2428 RETURN TRUE;
2429 EXCEPTION
2430 WHEN e_resource_busy_exception THEN
2431 IF c_hpo%ISOPEN THEN
2432 CLOSE c_hpo;
2433 END IF;
2434 IF c_ghpo_exempt%ISOPEN THEN
2435 CLOSE c_ghpo_exempt;
2436 END IF;
2437 IF c_scho_open_end%ISOPEN THEN
2438 CLOSE c_scho_open_end;
2439 END IF;
2440 IF c_scho_max_enddt%ISOPEN THEN
2441 CLOSE c_scho_max_enddt;
2442 END IF;
2443 IF c_scho_update%ISOPEN THEN
2444 CLOSE c_scho_update;
2445 END IF;
2446 p_message_name := 'IGS_EN_STUD_HECS_REC_LOCKED';
2447 RETURN FALSE;
2448 WHEN OTHERS THEN
2449 IF c_hpo%ISOPEN THEN
2450 CLOSE c_hpo;
2451 END IF;
2452 IF c_ghpo_exempt%ISOPEN THEN
2453 CLOSE c_ghpo_exempt;
2454 END IF;
2455 IF c_scho_open_end%ISOPEN THEN
2456 CLOSE c_scho_open_end;
2457 END IF;
2458 IF c_scho_max_enddt%ISOPEN THEN
2459 CLOSE c_scho_max_enddt;
2460 END IF;
2461 IF c_scho_update%ISOPEN THEN
2462 CLOSE c_scho_update;
2463 END IF;
2464 RAISE;
2465 END;
2466
2467 END enrp_ins_scho_dflt;
2468
2469 FUNCTION Enrp_Ins_Sct_Trnsfr(
2470 p_person_id IN NUMBER ,
2471 p_course_cd IN VARCHAR2 ,
2472 p_transfer_course_cd IN VARCHAR2 ,
2473 p_transfer_dt IN DATE ,
2474 p_message_name OUT NOCOPY VARCHAR2,
2475 p_trans_approved_dt IN DATE,
2476 p_term_cal_type IN VARCHAR2,
2477 p_term_seq_num IN NUMBER,
2478 p_discontinue_src_flag IN VARCHAR2,
2479 p_uooids_to_transfer IN VARCHAR2,
2480 p_susa_to_transfer IN VARCHAR2,
2481 p_transfer_adv_stand_flag IN VARCHAR2,
2482 p_status_date IN DATE,
2483 p_status_flag IN VARCHAR2
2484 )
2485 RETURN BOOLEAN AS
2486
2487 BEGIN -- enrp_ins_sct_trnsfr
2488 -- Insert a record into the IGS_PS_STDNT_TRN table.
2489 DECLARE
2490 BEGIN
2491 -- Set the default message number
2492 p_message_name := NULL;
2493 -- * Validate parameters.
2494 IF p_person_id IS NULL OR
2495 p_course_cd IS NULL OR
2496 p_transfer_course_cd IS NULL THEN
2497 RETURN TRUE;
2498 END IF;
2499 -- * Insert into IGS_PS_STDNT_TRN
2500 DECLARE
2501 l_rowid VARCHAR2(25);
2502 BEGIN
2503
2504 IGS_PS_STDNT_TRN_PKG.INSERT_ROW(
2505 x_rowid => l_rowid,
2506 x_person_id => p_person_id,
2507 x_course_cd => p_course_cd,
2508 x_transfer_course_cd => p_transfer_course_cd,
2509 x_TRANSFER_DT => NVL(p_transfer_dt,SYSDATE),
2510 x_COMMENTS => NULL,
2511 X_APPROVED_DATE => p_trans_approved_dt,
2512 X_EFFECTIVE_TERM_CAL_TYPE => p_term_cal_type,
2513 X_EFFECTIVE_TERM_SEQUENCE_NUM => p_term_seq_num,
2514 X_DISCONTINUE_SOURCE_FLAG => p_discontinue_src_flag,
2515 X_UOOIDS_TO_TRANSFER => p_uooids_to_transfer,
2516 X_SUSA_TO_TRANSFER => p_susa_to_transfer,
2517 X_TRANSFER_ADV_STAND_FLAG => p_transfer_adv_stand_flag,
2518 X_STATUS_DATE => p_status_date,
2519 X_STATUS_FLAG => p_status_flag
2520 );
2521 END;
2522
2523 RETURN TRUE;
2524 END;
2525 END enrp_ins_sct_trnsfr;
2526 FUNCTION enrp_check_usec_core(
2527 p_person_id IN NUMBER ,
2528 p_program_cd IN VARCHAR2 ,
2529 p_uoo_id IN NUMBER )
2530
2531 ------------------------------------------------------------------
2532 --Created by : Parul Tandon, Oracle IDC
2533 --Date created: 07-OCT-2003
2534 --
2535 --Purpose: This Function checks whether the given unit section is
2536 --a core unit or not in the current pattern of study for the given
2537 --student program attempt.
2538 --
2539 --Known limitations/enhancements and/or remarks:
2540 --
2541 --Change History:
2542 --Who When What
2543 -------------------------------------------------------------------
2544
2545 RETURN VARCHAR2
2546 IS
2547
2548
2549
2550 CURSOR c_ps_att_dtls
2551 IS
2552 SELECT location_cd,
2553 attendance_mode,
2554 attendance_type,
2555 version_number
2556 FROM igs_en_stdnt_ps_att
2557 WHERE person_id = p_person_id
2558 AND course_cd = p_program_cd;
2559
2560 l_ps_att_dtls_rec c_ps_att_dtls%ROWTYPE;
2561 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
2562 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
2563
2564 CURSOR c_pos ( cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE ) IS
2565 SELECT pos.cal_type,
2566 pos.sequence_number,
2567 pos.always_pre_enrol_ind,
2568 pos.number_of_periods,
2569 pos.aprvd_ci_sequence_number ,
2570 pos.acad_perd_unit_set
2571 FROM IGS_PS_PAT_OF_STUDY pos
2572 WHERE pos.course_cd = p_program_cd AND
2573 pos.version_number = l_ps_att_dtls_rec.version_number AND
2574 pos.cal_type = l_acad_cal_type AND
2575 ((pos.location_cd IS NULL AND
2576 pos.attendance_mode IS NULL AND
2577 pos.attendance_type IS NULL AND
2578 pos.unit_set_cd IS NULL AND
2579 pos.admission_cal_type IS NULL AND
2580 pos.admission_cat IS NULL) OR
2581 IGS_EN_GEN_005.enrp_get_pos_links(
2582 l_ps_att_dtls_rec.location_cd,
2583 l_ps_att_dtls_rec.attendance_mode,
2584 l_ps_att_dtls_rec.attendance_type,
2585 cp_unit_set_cd,
2586 NULL,
2587 NULL,
2588 pos.location_cd,
2589 pos.attendance_mode,
2590 pos.attendance_type,
2591 pos.unit_set_cd,
2592 pos.admission_cal_type,
2593 pos.admission_cat) > 0)
2594 ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
2595 l_ps_att_dtls_rec.location_cd,
2596 l_ps_att_dtls_rec.attendance_mode,
2597 l_ps_att_dtls_rec.attendance_type,
2598 cp_unit_set_cd,
2599 NULL,
2600 NULL,
2601 pos.location_cd,
2602 pos.attendance_mode,
2603 pos.attendance_type,
2604 pos.unit_set_cd,
2605 pos.admission_cal_type,
2606 pos.admission_cat) DESC;
2607 l_pos_rec c_pos%ROWTYPE;
2608
2609
2610 CURSOR c_last_unit_set IS
2611 SELECT susa.unit_set_cd
2612 FROM igs_as_su_setatmpt susa ,
2613 igs_en_unit_set us ,
2614 igs_en_unit_set_cat usc
2615 WHERE susa.person_id = p_person_id AND
2616 susa.course_cd = p_program_cd AND
2617 susa.rqrmnts_complete_dt IS NULL AND
2618 susa.student_confirmed_ind = 'Y' AND
2619 susa.end_dt IS NULL AND
2620 susa.unit_set_cd = us.unit_set_cd AND
2621 us.unit_set_cat = usc.unit_set_cat AND
2622 usc.s_unit_set_cat = 'PRENRL_YR';
2623 l_last_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE;
2624
2625 CURSOR c_map_unit_set( cp_person_id igs_as_su_setatmpt.person_id%TYPE,
2626 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE,
2627 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE) IS
2628 SELECT unit_set_cd
2629 FROM igs_as_su_setatmpt
2630 WHERE (
2631 (unit_set_cd = cp_unit_set_cd) OR
2632 (unit_set_cd IN (SELECT stream_unit_set_cd
2633 FROM igs_en_unit_set_map
2634 WHERE (mapping_set_cd,sequence_no) IN (SELECT mapping_set_cd,sequence_no
2635 FROM igs_ps_us_prenr_cfg
2636 WHERE unit_set_cd = cp_unit_set_cd)))
2637 )
2638 AND person_id = cp_person_id
2639 AND course_cd = cp_course_cd;
2640
2641 CURSOR c_susa IS
2642 SELECT susa.unit_set_cd
2643 FROM igs_as_su_setatmpt susa
2644 WHERE susa.person_id = p_person_id AND
2645 susa.course_cd = p_program_cd AND
2646 susa.student_confirmed_ind = 'Y' AND
2647 susa.rqrmnts_complete_dt IS NULL AND
2648 susa.end_dt IS NULL;
2649
2650 CURSOR c_pos_unit_sets( cp_version_number igs_ps_pat_of_study.version_number%TYPE) IS
2651 SELECT unit_set_cd
2652 FROM igs_ps_pat_of_study pos
2653 WHERE course_cd = p_program_cd AND
2654 version_number = cp_version_number AND
2655 cal_type = l_acad_cal_type AND
2656 unit_set_cd IN
2657 ( SELECT susa.unit_set_cd
2658 FROM igs_as_su_setatmpt susa
2659 WHERE susa.person_id = p_person_id AND
2660 susa.course_cd = pos.course_cd AND
2661 susa.student_confirmed_ind = 'Y' AND
2662 susa.end_dt IS NULL);
2663
2664 l_core_ind igs_ps_pat_study_unt.core_ind%TYPE;
2665 l_message VARCHAR2(100);
2666 l_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE;
2667 l_row_count NUMBER;
2668 l_pos_count NUMBER;
2669
2670 FUNCTION get_core_indicator (p_acad_perd_unit_set igs_ps_pat_of_study.acad_perd_unit_set%TYPE,
2671 p_sequence_number igs_ps_pat_of_study.sequence_number%TYPE,
2672 p_number_of_periods igs_ps_pat_of_study.number_of_periods%TYPE,
2673 p_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE)
2674 RETURN VARCHAR2
2675 IS
2676 CURSOR c_usec_details IS
2677 SELECT unit_cd,
2678 cal_type
2679 FROM igs_ps_unit_ofr_opt
2680 WHERE uoo_id = p_uoo_id;
2681 l_usec_details_rec c_usec_details%ROWTYPE;
2682
2683 CURSOR c_aci IS
2684 SELECT aci.cal_type,
2685 aci.sequence_number,
2686 aci.start_dt,
2687 aci.end_dt
2688 FROM igs_ca_inst aci
2689 WHERE aci.cal_type = l_acad_cal_type AND
2690 aci.sequence_number = l_acad_ci_sequence_number;
2691 l_aci_rec c_aci%ROWTYPE;
2692
2693 CURSOR c_acad_us (cp_admin_unit_Set_cd igs_as_su_setatmpt.unit_set_cd%TYPE) IS
2694 SELECT usm.stream_unit_set_Cd
2695 FROM igs_en_unit_set_map usm,
2696 igs_ps_us_prenr_cfg upc
2697 WHERE upc.unit_set_cd = cp_admin_unit_set_cd
2698 AND usm.mapping_set_cd = upc.mapping_set_cd
2699 AND usm.sequence_no = upc.sequence_no;
2700
2701 CURSOR c_susa_exists (cp_stream_unit_Set_cd igs_as_su_setatmpt.unit_set_cd%TYPE,
2702 cp_person_id igs_as_su_setatmpt.person_id%TYPE,
2703 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE) IS
2704 SELECT 'X'
2705 FROM igs_as_su_setatmpt susa
2706 WHERE susa.unit_set_cd = cp_stream_unit_set_cd
2707 AND susa.person_id = cp_person_id
2708 AND susa.course_cd = cp_course_cd
2709 AND susa.end_dt IS NULL
2710 AND susa.rqrmnts_complete_dt IS NULL;
2711
2712 CURSOR c_num_acad_perd IS
2713 SELECT DISTINCT acad_perd
2714 FROM igs_en_susa_year_v
2715 WHERE person_id = p_person_id
2716 AND course_cd = p_program_cd
2717 AND unit_set_cd = p_unit_set_cd ;
2718
2719 CURSOR c_posp (
2720 cp_sequence_number igs_ps_pat_of_study.sequence_number%TYPE,
2721 cp_number_of_periods igs_ps_pat_of_study.number_of_periods%TYPE,
2722 cp_period_number NUMBER) IS
2723 SELECT posp.acad_period_num,
2724 posp.teach_cal_type,
2725 posp.sequence_number
2726 FROM igs_ps_pat_study_prd posp
2727 WHERE posp.pos_sequence_number = cp_sequence_number AND
2728 posp.acad_period_num >= cp_period_number AND
2729 posp.acad_period_num < (cp_period_number
2730 + cp_number_of_periods) AND
2731 EXISTS (SELECT 'x'
2732 FROM igs_ps_pat_study_unt posu
2733 WHERE posp.sequence_number = posu.posp_sequence_number AND
2734 posu.unit_cd IS NOT NULL)
2735 ORDER BY posp.acad_period_num;
2736
2737 CURSOR c_posu (
2738 cp_sequence_number igs_ps_pat_study_prd.sequence_number%TYPE,
2739 cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE) IS
2740 SELECT core_ind
2741 FROM IGS_PS_PAT_STUDY_UNT posu
2742 WHERE posu.posp_sequence_number = cp_sequence_number
2743 AND posu.unit_cd = cp_unit_cd;
2744
2745 l_dummy VARCHAR2(1);
2746 l_rec_exist BOOLEAN;
2747 l_period_number NUMBER;
2748 l_core_ind igs_ps_pat_study_unt.core_ind%TYPE;
2749
2750 BEGIN
2751 -- Get the unit section details
2752
2753 OPEN c_usec_details;
2754 FETCH c_usec_details INTO l_usec_details_rec;
2755 CLOSE c_usec_details;
2756
2757 -- Get the start/end dates from the academic period
2758 -- required by routine calls lower in the routine.
2759
2760 OPEN c_aci;
2761 FETCH c_aci INTO l_aci_rec;
2762 CLOSE c_aci;
2763
2764 OPEN c_pos (p_unit_set_cd);
2765 IF c_pos%FOUND THEN
2766 l_rec_exist := TRUE;
2767 END IF;
2768 CLOSE c_pos;
2769
2770 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
2771
2772 FOR vc_acad_us_rec IN c_acad_us(p_unit_set_cd) LOOP
2773 OPEN c_susa_exists(vc_acad_us_rec.stream_unit_set_cd, p_person_id, p_program_cd);
2774 FETCH c_susa_exists INTO l_dummy;
2775 IF c_susa_exists%FOUND THEN
2776 l_rec_exist := TRUE;
2777 END IF;
2778 CLOSE c_susa_exists;
2779 END LOOP;
2780 END IF;
2781
2782 -- Determine the number of academic periods in which the
2783 -- student has been enrolled
2784
2785 -- If year of program mode is enabled
2786 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' AND l_rec_exist THEN
2787
2788 -- If 'Academic Period within Unit Sets' is checked for the pattern of study,
2789 -- Get number of periods from igs_en_susa_year_v else use original function
2790 -- igs_en_gen_004.enrp_get_perd_num.
2791 IF NVL(p_acad_perd_unit_set,'N') = 'Y' AND p_unit_set_cd IS NOT NULL THEN
2792 OPEN c_num_acad_perd;
2793 FETCH c_num_acad_perd INTO l_period_number;
2794 CLOSE c_num_acad_perd;
2795
2796 l_period_number := l_period_number + 1;
2797 ELSE
2798 l_period_number := igs_en_gen_004.enrp_get_perd_num(
2799 p_person_id,
2800 p_program_cd,
2801 l_acad_cal_type,
2802 l_acad_ci_sequence_number,
2803 l_aci_rec.start_dt);
2804 END IF;
2805 ELSE
2806 l_period_number := igs_en_gen_004.enrp_get_perd_num(
2807 p_person_id,
2808 p_program_cd,
2809 l_acad_cal_type,
2810 l_acad_ci_sequence_number,
2811 l_aci_rec.start_dt);
2812 END IF;
2813
2814 -- As the function igs_en_gen_004.enrp_get_perd_num returns incremented period number, so
2815 -- decrement by 1 to get the current period
2816 IF l_period_number > 1 THEN
2817 l_period_number := l_period_number - 1;
2818 END IF;
2819
2820 FOR l_posp_rec IN c_posp(p_sequence_number,
2821 p_number_of_periods,
2822 l_period_number)
2823 LOOP
2824 OPEN c_posu(l_posp_rec.sequence_number,l_usec_details_rec.unit_cd);
2825 FETCH c_posu INTO l_core_ind;
2826 IF c_posu%FOUND THEN
2827 RETURN l_core_ind;
2828 END IF;
2829 CLOSE c_posu;
2830 END LOOP;
2831 RETURN 'X';
2832 END get_core_indicator;
2833
2834 BEGIN
2835
2836 -- If the value of profile 'IGS_EN_CORE_VAL' if not set or set to No then Return NULL
2837 IF NVL(fnd_profile.value('IGS_EN_CORE_VAL'),'N') = 'N' THEN
2838
2839 RETURN NULL;
2840 END IF;
2841
2842 -- Get the Program Attempt Details
2843 OPEN c_ps_att_dtls;
2844 FETCH c_ps_att_dtls INTO l_ps_att_dtls_rec;
2845 CLOSE c_ps_att_dtls;
2846
2847 -- Get the academic calendar instance
2848 igs_en_gen_015.get_academic_cal
2849 (
2850 p_person_id,
2851 p_program_cd,
2852 l_acad_cal_type,
2853 l_acad_ci_sequence_number,
2854 l_message,
2855 SYSDATE
2856 );
2857
2858 -- If Pre_Enrollment Year profile option is set to Yes
2859 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
2860
2861 -- Get the current unit set attempt of type pre-enrollment
2862 OPEN c_last_unit_set;
2863 FETCH c_last_unit_set INTO l_last_unit_set_cd;
2864 CLOSE c_last_unit_set;
2865
2866 -- For the unit set fetched above and all the unit set attempts mapped to the unit set
2867 FOR l_map_unit_set_rec IN c_map_unit_set(p_person_id, p_program_cd, l_last_unit_set_cd)
2868 LOOP
2869 -- Check whether the given unit is defined in pattern of study as either core or optional
2870 OPEN c_pos(l_map_unit_set_rec.unit_set_cd);
2871 FETCH c_pos INTO l_pos_rec;
2872 CLOSE c_pos;
2873 -- Call local function to get the core indicator
2874 l_core_ind := get_core_indicator(l_pos_rec.acad_perd_unit_set, l_pos_rec.sequence_number, l_pos_rec.number_of_periods, l_map_unit_set_rec.unit_set_cd);
2875 IF l_core_ind IN('Y','N') THEN
2876 EXIT;
2877 END IF;
2878 END LOOP;
2879
2880 -- If the unit is defined then return appropriate value i.e. CORE or OPTIONAL
2881 -- If the unit is not defined then return ELECTIVE
2882 IF l_core_ind = 'Y' THEN
2883 RETURN 'CORE';
2884 ELSIF l_core_ind = 'N' THEN
2885 RETURN 'OPTIONAL';
2886 ELSIF l_core_ind = 'X' THEN
2887 RETURN 'ELECTIVE';
2888 ELSE
2889 RETURN NULL;
2890 END IF;
2891
2892 -- If the Pre_Enrollment Year profile option is not set or set to No
2893 ELSE
2894 -- Get the current unit set attempt
2895 FOR l_susa_rec IN c_susa LOOP
2896 l_row_count := 1;
2897 l_unit_set_cd := l_susa_rec.unit_set_cd;
2898 IF c_susa%ROWCOUNT > 1 THEN
2899 l_row_count := 2;
2900 EXIT;
2901 END IF;
2902 END LOOP;
2903 IF l_row_count > 1 THEN
2904 FOR l_pos_unit_sets_rec IN c_pos_unit_sets(l_ps_att_dtls_rec.version_number) LOOP
2905 l_pos_count := 1;
2906 l_unit_set_cd := l_pos_unit_sets_rec.unit_set_cd;
2907 IF c_pos_unit_sets%ROWCOUNT > 1 THEN
2908 l_pos_count := 2;
2909 EXIT;
2910 END IF;
2911 END LOOP;
2912 IF l_pos_count <> 1 THEN
2913 l_unit_set_cd := NULL;
2914 END IF;
2915 ELSIF l_row_count = 0 THEN
2916 l_unit_set_cd := NULL;
2917 END IF;
2918
2919 -- For this unit set check whether the given unit is defined in pattern of study as either core or optional
2920 OPEN c_pos(l_unit_set_cd);
2921 FETCH c_pos INTO l_pos_rec;
2922 CLOSE c_pos;
2923
2924 -- Call local function to get the core indicator
2925 l_core_ind := get_core_indicator(l_pos_rec.acad_perd_unit_set, l_pos_rec.sequence_number, l_pos_rec.number_of_periods, l_unit_set_cd);
2926
2927 -- If the unit is defined then return appropriate value i.e. CORE or OPTIONAL
2928 -- If the unit is not defined then return ELECTIVE
2929 IF l_core_ind = 'Y' THEN
2930 RETURN 'CORE';
2931 ELSIF l_core_ind = 'N' THEN
2932 RETURN 'OPTIONAL';
2933 ELSIF l_core_ind = 'X' THEN
2934 RETURN 'ELECTIVE';
2935 ELSE
2936 RETURN NULL;
2937 END IF;
2938
2939 END IF;
2940
2941 END enrp_check_usec_core;
2942
2943
2944
2945 FUNCTION enrp_chk_dest_usec_core(
2946 p_person_id IN NUMBER ,
2947 p_src_program_cd IN VARCHAR2 ,
2948 p_dest_program_cd IN VARCHAR2 ,
2949 p_unit_set_cd IN VARCHAR2,
2950 p_uoo_id IN NUMBER,
2951 p_coo_id IN NUMBER
2952 )
2953
2954 ------------------------------------------------------------------
2955 --Created by : Parul Tandon, Oracle IDC
2956 --Date created: 07-OCT-2003
2957 --
2958 --Purpose: This Function checks whether the given unit section is
2959 --a core unit or not in the current pattern of study for the given
2960 --student program attempt.
2961 --
2962 --Known limitations/enhancements and/or remarks:
2963 --
2964 --Change History:
2965 --Who When What
2966 --ckasu 29-SEP-2005 Modfied this procedure inorder to include cooid
2967 -- inorder to show unit as Core when POS is setup in destination for
2968 -- for unit as core a part of bug #4278867
2969 -------------------------------------------------------------------
2970
2971 RETURN VARCHAR2
2972 IS
2973
2974 CURSOR chk_dest_core_ind (cp_person_id IGS_EN_SU_ATTEMPT.PERSON_ID%TYPE,
2975 cp_course_cd IGS_EN_SU_ATTEMPT.COURSE_CD%TYPE,
2976 cp_uoo_id IGS_EN_SU_ATTEMPT.UOO_ID%TYPE) IS
2977 SELECT core_indicator_code
2978 FROM IGS_EN_SU_ATTEMPT
2979 WHERE person_id = cp_person_id
2980 AND course_cd = cp_course_cd
2981 AND uoo_id = cp_uoo_id;
2982
2983 -- modified this cursor as a part of bug #4278867
2984 CURSOR c_ps_att_dtls
2985 IS
2986 SELECT location_cd,
2987 attendance_mode,
2988 attendance_type,
2989 version_number
2990 FROM IGS_PS_OFR_OPT
2991 WHERE coo_id = p_coo_id;
2992
2993
2994 l_ps_att_dtls_rec c_ps_att_dtls%ROWTYPE;
2995 l_acad_cal_type igs_ca_inst.cal_type%TYPE;
2996 l_acad_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
2997
2998 CURSOR c_pos ( cp_unit_set_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE ) IS
2999 SELECT pos.cal_type,
3000 pos.sequence_number,
3001 pos.always_pre_enrol_ind,
3002 pos.number_of_periods,
3003 pos.aprvd_ci_sequence_number ,
3004 pos.acad_perd_unit_set
3005 FROM IGS_PS_PAT_OF_STUDY pos
3006 WHERE pos.course_cd = p_dest_program_cd AND
3007 pos.version_number = l_ps_att_dtls_rec.version_number AND
3008 pos.cal_type = l_acad_cal_type AND
3009 ((pos.location_cd IS NULL AND
3010 pos.attendance_mode IS NULL AND
3011 pos.attendance_type IS NULL AND
3012 pos.unit_set_cd IS NULL AND
3013 pos.admission_cal_type IS NULL AND
3014 pos.admission_cat IS NULL) OR
3015 IGS_EN_GEN_005.enrp_get_pos_links(
3016 l_ps_att_dtls_rec.location_cd,
3017 l_ps_att_dtls_rec.attendance_mode,
3018 l_ps_att_dtls_rec.attendance_type,
3019 cp_unit_set_cd,
3020 NULL,
3021 NULL,
3022 pos.location_cd,
3023 pos.attendance_mode,
3024 pos.attendance_type,
3025 pos.unit_set_cd,
3026 pos.admission_cal_type,
3027 pos.admission_cat) > 0)
3028 ORDER BY IGS_EN_GEN_005.enrp_get_pos_links(
3029 l_ps_att_dtls_rec.location_cd,
3030 l_ps_att_dtls_rec.attendance_mode,
3031 l_ps_att_dtls_rec.attendance_type,
3032 cp_unit_set_cd,
3033 NULL,
3034 NULL,
3035 pos.location_cd,
3036 pos.attendance_mode,
3037 pos.attendance_type,
3038 pos.unit_set_cd,
3039 pos.admission_cal_type,
3040 pos.admission_cat) DESC;
3041 l_pos_rec c_pos%ROWTYPE;
3042
3043
3044 CURSOR c_last_unit_set IS
3045 SELECT susa.unit_set_cd
3046 FROM igs_as_su_setatmpt susa ,
3047 igs_en_unit_set us ,
3048 igs_en_unit_set_cat usc
3049 WHERE susa.person_id = p_person_id AND
3050 susa.course_cd = p_src_program_cd AND
3051 susa.rqrmnts_complete_dt IS NULL AND
3052 susa.student_confirmed_ind = 'Y' AND
3053 susa.end_dt IS NULL AND
3054 susa.unit_set_cd = us.unit_set_cd AND
3055 us.unit_set_cat = usc.unit_set_cat AND
3056 usc.s_unit_set_cat = 'PRENRL_YR';
3057 l_last_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE;
3058
3059 CURSOR c_map_unit_set( cp_person_id igs_as_su_setatmpt.person_id%TYPE,
3060 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE,
3061 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE) IS
3062 SELECT unit_set_cd
3063 FROM igs_as_su_setatmpt
3064 WHERE (
3065 (unit_set_cd = cp_unit_set_cd) OR
3066 (unit_set_cd IN (SELECT stream_unit_set_cd
3067 FROM igs_en_unit_set_map
3068 WHERE (mapping_set_cd,sequence_no) IN (SELECT mapping_set_cd,sequence_no
3069 FROM igs_ps_us_prenr_cfg
3070 WHERE unit_set_cd = cp_unit_set_cd)))
3071 )
3072 AND person_id = cp_person_id
3073 AND course_cd = cp_course_cd;
3074
3075 CURSOR c_susa IS
3076 SELECT susa.unit_set_cd
3077 FROM igs_as_su_setatmpt susa
3078 WHERE susa.person_id = p_person_id AND
3079 susa.course_cd = p_src_program_cd AND
3080 susa.student_confirmed_ind = 'Y' AND
3081 susa.rqrmnts_complete_dt IS NULL AND
3082 susa.end_dt IS NULL;
3083
3084 CURSOR c_pos_unit_sets( cp_version_number igs_ps_pat_of_study.version_number%TYPE) IS
3085 SELECT unit_set_cd
3086 FROM igs_ps_pat_of_study pos
3087 WHERE course_cd = p_dest_program_cd AND
3088 version_number = cp_version_number AND
3089 cal_type = l_acad_cal_type AND
3090 unit_set_cd IN
3091 ( SELECT susa.unit_set_cd
3092 FROM igs_as_su_setatmpt susa
3093 WHERE susa.person_id = p_person_id AND
3094 susa.course_cd = p_src_program_cd AND
3095 susa.student_confirmed_ind = 'Y' AND
3096 susa.end_dt IS NULL);
3097
3098 l_core_ind igs_ps_pat_study_unt.core_ind%TYPE;
3099 l_dest_core_ind IGS_EN_SU_ATTEMPT.CORE_INDICATOR_CODE%TYPE;
3100 l_message VARCHAR2(100);
3101 l_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE;
3102 l_row_count NUMBER;
3103 l_pos_count NUMBER;
3104
3105 FUNCTION get_trn_core_indicator (p_acad_perd_unit_set igs_ps_pat_of_study.acad_perd_unit_set%TYPE,
3106 p_sequence_number igs_ps_pat_of_study.sequence_number%TYPE,
3107 p_number_of_periods igs_ps_pat_of_study.number_of_periods%TYPE,
3108 p_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE)
3109 RETURN VARCHAR2
3110 IS
3111 CURSOR c_usec_details IS
3112 SELECT unit_cd,
3113 cal_type
3114 FROM igs_ps_unit_ofr_opt
3115 WHERE uoo_id = p_uoo_id;
3116 l_usec_details_rec c_usec_details%ROWTYPE;
3117
3118 CURSOR c_aci IS
3119 SELECT aci.cal_type,
3120 aci.sequence_number,
3121 aci.start_dt,
3122 aci.end_dt
3123 FROM igs_ca_inst aci
3124 WHERE aci.cal_type = l_acad_cal_type AND
3125 aci.sequence_number = l_acad_ci_sequence_number;
3126 l_aci_rec c_aci%ROWTYPE;
3127
3128 CURSOR c_acad_us (cp_admin_unit_Set_cd igs_as_su_setatmpt.unit_set_cd%TYPE) IS
3129 SELECT usm.stream_unit_set_Cd
3130 FROM igs_en_unit_set_map usm,
3131 igs_ps_us_prenr_cfg upc
3132 WHERE upc.unit_set_cd = cp_admin_unit_set_cd
3133 AND usm.mapping_set_cd = upc.mapping_set_cd
3134 AND usm.sequence_no = upc.sequence_no;
3135
3136
3137 CURSOR c_num_acad_perd IS
3138 SELECT DISTINCT acad_perd
3139 FROM igs_en_susa_year_v
3140 WHERE person_id = p_person_id
3141 AND course_cd = p_src_program_cd
3142 AND unit_set_cd = p_unit_set_cd ;
3143
3144 CURSOR c_posp (
3145 cp_sequence_number igs_ps_pat_of_study.sequence_number%TYPE,
3146 cp_number_of_periods igs_ps_pat_of_study.number_of_periods%TYPE,
3147 cp_period_number NUMBER) IS
3148 SELECT posp.acad_period_num,
3149 posp.teach_cal_type,
3150 posp.sequence_number
3151 FROM igs_ps_pat_study_prd posp
3152 WHERE posp.pos_sequence_number = cp_sequence_number AND
3153 posp.acad_period_num >= cp_period_number AND
3154 posp.acad_period_num < (cp_period_number
3155 + cp_number_of_periods) AND
3156 EXISTS (SELECT 'x'
3157 FROM igs_ps_pat_study_unt posu
3158 WHERE posp.sequence_number = posu.posp_sequence_number AND
3159 posu.unit_cd IS NOT NULL)
3160 ORDER BY posp.acad_period_num;
3161
3162 CURSOR c_posu (
3163 cp_sequence_number igs_ps_pat_study_prd.sequence_number%TYPE,
3164 cp_unit_cd igs_ps_unit_ver.unit_cd%TYPE) IS
3165 SELECT core_ind
3166 FROM IGS_PS_PAT_STUDY_UNT posu
3167 WHERE posu.posp_sequence_number = cp_sequence_number
3168 AND posu.unit_cd = cp_unit_cd;
3169
3170 l_dummy VARCHAR2(1);
3171 l_rec_exist BOOLEAN;
3172 l_period_number NUMBER;
3173 l_core_ind igs_ps_pat_study_unt.core_ind%TYPE;
3174
3175 BEGIN
3176 -- Get the unit section details
3177
3178 OPEN c_usec_details;
3179 FETCH c_usec_details INTO l_usec_details_rec;
3180 CLOSE c_usec_details;
3181
3182 -- Get the start/end dates from the academic period
3183 -- required by routine calls lower in the routine.
3184
3185 OPEN c_aci;
3186 FETCH c_aci INTO l_aci_rec;
3187 CLOSE c_aci;
3188
3189 OPEN c_pos (p_unit_set_cd);
3190 IF c_pos%FOUND THEN
3191 l_rec_exist := TRUE;
3192 END IF;
3193 CLOSE c_pos;
3194
3195 -- Determine the number of academic periods in which the
3196 -- student has been enrolled
3197
3198 -- If year of program mode is enabled
3199 IF NVL(FND_PROFILE.VALUE('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
3200
3201 -- If 'Academic Period within Unit Sets' is checked for the pattern of study,
3202 -- Get number of periods from igs_en_susa_year_v else use original function
3203 -- igs_en_gen_004.enrp_get_perd_num.
3204 IF NVL(p_acad_perd_unit_set,'N') = 'Y' AND p_unit_set_cd IS NOT NULL THEN
3205 OPEN c_num_acad_perd;
3206 FETCH c_num_acad_perd INTO l_period_number;
3207 CLOSE c_num_acad_perd;
3208
3209 l_period_number := l_period_number + 1;
3210 ELSE
3211 l_period_number := igs_en_gen_004.enrp_get_perd_num(
3212 p_person_id,
3213 p_src_program_cd,
3214 l_acad_cal_type,
3215 l_acad_ci_sequence_number,
3216 l_aci_rec.start_dt);
3217 END IF;
3218 ELSE
3219 l_period_number := igs_en_gen_004.enrp_get_perd_num(
3220 p_person_id,
3221 p_src_program_cd,
3222 l_acad_cal_type,
3223 l_acad_ci_sequence_number,
3224 l_aci_rec.start_dt);
3225 END IF;
3226
3227 -- As the function igs_en_gen_004.enrp_get_perd_num returns incremented period number, so
3228 -- decrement by 1 to get the current period
3229 IF l_period_number > 1 THEN
3230 l_period_number := l_period_number - 1;
3231 END IF;
3232
3233 FOR l_posp_rec IN c_posp(p_sequence_number,
3234 p_number_of_periods,
3235 l_period_number)
3236 LOOP
3237 OPEN c_posu(l_posp_rec.sequence_number,l_usec_details_rec.unit_cd);
3238 FETCH c_posu INTO l_core_ind;
3239 IF c_posu%FOUND THEN
3240 RETURN l_core_ind;
3241 END IF;
3242 CLOSE c_posu;
3243 END LOOP;
3244 RETURN 'X';
3245 END get_trn_core_indicator;
3246
3247 BEGIN
3248
3249 -- If the value of profile 'IGS_EN_CORE_VAL' if not set or set to No then Return NULL
3250 IF NVL(fnd_profile.value('IGS_EN_CORE_VAL'),'N') = 'N' THEN
3251
3252 RETURN NULL;
3253 END IF;
3254
3255 -- if the destination unit attempt exists, then fetch the core indicator value
3256 -- from that record. This is mainly to show the correctly value when the
3257 -- program transfer completion messages is shown. In case unit attempt record
3258 -- already existts before the transfer is submitted then in the record
3259 -- will not be transferred as it already exists in the destination.
3260 OPEN chk_dest_core_ind(p_person_id, p_dest_program_cd, p_uoo_id);
3261 FETCH chk_dest_core_ind INTO l_dest_core_ind ;
3262 IF chk_dest_core_ind%FOUND THEN
3263 CLOSE chk_dest_core_ind;
3264 RETURN l_dest_core_ind;
3265 ELSE
3266 CLOSE chk_dest_core_ind;
3267 END IF;
3268
3269 -- Get the Program Attempt Details
3270 OPEN c_ps_att_dtls;
3271 FETCH c_ps_att_dtls INTO l_ps_att_dtls_rec;
3272 CLOSE c_ps_att_dtls;
3273
3274 -- Get the academic calendar instance
3275 igs_en_gen_015.get_academic_cal
3276 (
3277 p_person_id,
3278 p_src_program_cd,
3279 l_acad_cal_type,
3280 l_acad_ci_sequence_number,
3281 l_message,
3282 SYSDATE
3283 );
3284
3285 -- If Pre_Enrollment Year profile option is set to Yes
3286 IF NVL(fnd_profile.value('IGS_PS_PRENRL_YEAR_IND'),'N') = 'Y' THEN
3287
3288 -- Get the current unit set attempt of type pre-enrollment
3289 IF p_unit_set_cd IS NULL THEN
3290 OPEN c_last_unit_set;
3291 FETCH c_last_unit_set INTO l_last_unit_set_cd;
3292 CLOSE c_last_unit_set;
3293 -- For the unit set fetched above and all the unit set attempts mapped to the unit set
3294 FOR l_map_unit_set_rec IN c_map_unit_set(p_person_id, p_src_program_cd, l_last_unit_set_cd)
3295 LOOP
3296 -- Check whether the given unit is defined in pattern of study as either core or optional
3297 OPEN c_pos(l_map_unit_set_rec.unit_set_cd);
3298 FETCH c_pos INTO l_pos_rec;
3299 CLOSE c_pos;
3300 -- Call local function to get the core indicator
3301 l_core_ind := get_trn_core_indicator(l_pos_rec.acad_perd_unit_set, l_pos_rec.sequence_number, l_pos_rec.number_of_periods, l_map_unit_set_rec.unit_set_cd);
3302 IF l_core_ind IN('Y','N') THEN
3303 EXIT;
3304 END IF;
3305 END LOOP;
3306 ELSE
3307 OPEN c_pos(p_unit_set_cd);
3308 FETCH c_pos INTO l_pos_rec;
3309 CLOSE c_pos;
3310
3311 l_core_ind := get_trn_core_indicator(l_pos_rec.acad_perd_unit_set, l_pos_rec.sequence_number, l_pos_rec.number_of_periods, p_unit_set_cd);
3312 END IF;
3313
3314 -- If the unit is defined then return appropriate value i.e. CORE or OPTIONAL
3315 -- If the unit is not defined then return ELECTIVE
3316 IF l_core_ind = 'Y' THEN
3317 RETURN 'CORE';
3318 ELSIF l_core_ind = 'N' THEN
3319 RETURN 'OPTIONAL';
3320 ELSIF l_core_ind = 'X' THEN
3321 RETURN 'ELECTIVE';
3322 ELSE
3323 RETURN NULL;
3324 END IF;
3325
3326 -- If the Pre_Enrollment Year profile option is not set or set to No
3327 ELSE
3328 -- Get the current unit set attempt
3329 IF p_unit_set_cd IS NULL THEN
3330 FOR l_susa_rec IN c_susa LOOP
3331 l_row_count := 1;
3332 l_unit_set_cd := l_susa_rec.unit_set_cd;
3333 IF c_susa%ROWCOUNT > 1 THEN
3334 l_row_count := 2;
3335 EXIT;
3336 END IF;
3337 END LOOP;
3338 IF l_row_count > 1 THEN
3339 FOR l_pos_unit_sets_rec IN c_pos_unit_sets(l_ps_att_dtls_rec.version_number) LOOP
3340 l_pos_count := 1;
3341 l_unit_set_cd := l_pos_unit_sets_rec.unit_set_cd;
3342 IF c_pos_unit_sets%ROWCOUNT > 1 THEN
3343 l_pos_count := 2;
3344 EXIT;
3345 END IF;
3346 END LOOP;
3347 IF l_pos_count <> 1 THEN
3348 l_unit_set_cd := NULL;
3349 END IF;
3350 ELSIF l_row_count = 0 THEN
3351 l_unit_set_cd := NULL;
3352 END IF;
3353 ELSE
3354 l_unit_set_cd := p_unit_set_cd;
3355 END IF;
3356
3357 -- For this unit set check whether the given unit is defined in pattern of study as either core or optional
3358 OPEN c_pos(l_unit_set_cd);
3359 FETCH c_pos INTO l_pos_rec;
3360 CLOSE c_pos;
3361 -- Call local function to get the core indicator
3362 l_core_ind := get_trn_core_indicator(l_pos_rec.acad_perd_unit_set, l_pos_rec.sequence_number, l_pos_rec.number_of_periods, l_unit_set_cd);
3363
3364 -- If the unit is defined then return appropriate value i.e. CORE or OPTIONAL
3365 -- If the unit is not defined then return ELECTIVE
3366 IF l_core_ind = 'Y' THEN
3367 RETURN 'CORE';
3368 ELSIF l_core_ind = 'N' THEN
3369 RETURN 'OPTIONAL';
3370 ELSIF l_core_ind = 'X' THEN
3371 RETURN 'ELECTIVE';
3372 ELSE
3373 RETURN NULL;
3374 END IF;
3375
3376 END IF;
3377
3378 END enrp_chk_dest_usec_core;
3379
3380 END Igs_En_Gen_009;
[Home] [Help]