1 PACKAGE BODY IGS_EN_TRANSFER_APIS AS
2 /* $Header: IGSEN82B.pls 120.18 2006/09/15 09:00:19 bdeviset noship $ */
3 /*-------------------------------------------------------------------------------------------
4 Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
5
6 --Change History:
7 --Who When What
8 --ckasu 20-Nov-2004 modifed cleanup_job by changing order of parameters passed.
9 --ckasu 02-DEC-2004 created as a part of Bug #4044329
10 --bdeviset 02-DEC-2004 Bug# 4044319.Modified validate_src_prgm_unt_attempts.
11 --stutta 10-DEC-2004 Bug#4046782. Removed procedure chk_set_prm_prg_for_src_career
12 --ckasu 11-Dec-2004 modified file as a part of bug# 4061818,4061914
13 --ckasu 20-Dec-2004 modified file as a part of bug# 4063726
14 --bdeviset 22-Dec-2004 Modifed program_transfer_api as part Bug#4083015.
15 --smaddali 21-dec-04 Modified procedure validate_src_prgm_unt_attempts for bug#4083358
16 --amuthu 23-DEC-2004 Modified the program_transfer_api, to add two validation in career mode
17 -- smaddali 5-jan-2005 Modified procedure program_transfer_api for bug#4103437
18 -- ckasu 07-JAN-2005 Modified code inorder to include person holds and person step
19 -- validations as a part of bug# 4083552
20 -- bdeviset 21-Mar-2005 Modified update_destination_program procedure for Bug# 4248338, 4248367
21 -- sgurusam 17-Jun-2005 Modified validate_person_steps procedure for EN317FD
22 -- bdeviset 20-OCT-2005 Modified validate_src_prgm_unt_attempts procedure for bug# 4691498
23 -- stutta 26-Nov-2005 Created procedure val_unchk_sub_units and added call. Bug #4763202
24 -- ckasu 08-DEC-2005 passed SYSDATE for update_source instead of p_actual_date param
25 -- as part of bug#4869869
26 -- ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) in CLEANUP_JOB procedure
27 -- as a part of bug#4958173
28 -- ckasu 06-MAR-2006 added new cursor c_get_enr_method_type as a part of bug#5070732 in
29 -- validate_prgm_attend_type_step Procedure
30
31 -------------------------------------------------------------------------------------------*/
32
33
34 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_TRANSFER_APIS';
35 g_debug_level CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
36 g_transfer_dt DATE;
37 FUNCTION is_career_model_enabled RETURN BOOLEAN AS
38 -------------------------------------------------------------------------------------------
39 --Created by : Chandrasekhar Kasu, Oracle IDC
40 --Date created: 20-Nov-2004
41 -- Purpose : returns True when Career model is enabled else False.
42 --Change History:
43 --Who When What
44
45 -------------------------------------------------------------------------------------------
46 BEGIN
47
48 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
49 RETURN TRUE;
50 ELSE
51 RETURN FALSE;
52 END IF;
53
54 END is_career_model_enabled;
55
56 PROCEDURE parse_messages( p_message_names IN VARCHAR2) AS
57
58 l_strtpoint_msg NUMBER;
59 l_endpoint_msg NUMBER;
60 l_cindex_msg NUMBER;
61 l_pre_cindex_msg NUMBER;
62 l_nth_occurence_msg NUMBER;
63 l_messages_str VARCHAR2(2000);
64 l_message_name VARCHAR2(100);
65 l_messg_and_unitcd_sep NUMBER;
66 l_unit_cd VARCHAR2(100);
67
68 BEGIN
69
70 l_strtpoint_msg := 0;
71 l_pre_cindex_msg := 0;
72 l_nth_occurence_msg := 1;
73 l_messages_str := p_message_names||';';
74 l_cindex_msg := INSTR(l_messages_str,';',1,l_nth_occurence_msg);
75
76 WHILE (l_cindex_msg <> 0 ) LOOP
77
78 l_strtpoint_msg := l_pre_cindex_msg + 1;
79 l_endpoint_msg := l_cindex_msg - l_strtpoint_msg;
80 l_pre_cindex_msg := l_cindex_msg;
81 l_message_name := substr(l_messages_str,l_strtpoint_msg,l_endpoint_msg);
82 l_messg_and_unitcd_sep := INSTR(l_message_name,'*',1,1);
83
84 IF l_messg_and_unitcd_sep <> 0 THEN
85 l_unit_cd := substr(l_message_name,l_messg_and_unitcd_sep + 1,(l_cindex_msg-l_messg_and_unitcd_sep-1));
86 l_message_name := substr(l_message_name,1,l_messg_and_unitcd_sep - 1);
87 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
88 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_cd);
89 FND_MSG_PUB.ADD;
90 ELSE
91 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
92 FND_MSG_PUB.ADD;
93 END IF;
94
95 l_nth_occurence_msg := l_nth_occurence_msg + 1;
96 l_cindex_msg := INSTR(l_messages_str,';',1,l_nth_occurence_msg);
97
98 END LOOP; -- end of l_cindex_msg <> 0 LOOP
99
100 END parse_messages;
101
102 PROCEDURE is_destn_prgm_att_discon(
103 p_person_id IN NUMBER,
104 p_dest_program_cd IN VARCHAR2,
105 p_dest_prog_ver IN NUMBER,
106 p_status OUT NOCOPY BOOLEAN
107 ) AS
108
109 -------------------------------------------------------------------------------------------
110 --Created by : Chandrasekhar Kasu, Oracle IDC
111 --Date created: 20-Nov-2004
112 -- Purpose : Returns True when destination Program attempt is discontinued else False
113 --Change History:
114 --Who When What
115
116 -------------------------------------------------------------------------------------------
117
118 l_val VARCHAR2(10);
119 CURSOR c_get_prgm_att_status IS
120 Select 'Y'
121 From IGS_EN_STDNT_PS_ATT_ALL
122 Where person_id = p_person_id and
123 course_cd = p_dest_program_cd and
124 version_number = p_dest_prog_ver and
125 course_attempt_status = 'DISCONTIN';
126
127 BEGIN
128 OPEN c_get_prgm_att_status;
129 FETCH c_get_prgm_att_status INTO l_val;
130 IF (c_get_prgm_att_status%FOUND) THEN
131 CLOSE c_get_prgm_att_status;
132 p_status := TRUE;
133 ELSE
134 CLOSE c_get_prgm_att_status;
135 p_status := FALSE;
136 END IF;
137
138 END is_destn_prgm_att_discon;
139
140
141 PROCEDURE update_destination_prgm(
142 p_person_id IN NUMBER,
143 p_src_course_cd IN VARCHAR2,
144 p_course_cd IN VARCHAR2,
145 p_new_dest_key_flag IN OUT NOCOPY VARCHAR2,
146 p_stdnt_confrm_ind IN VARCHAR2,
147 p_dest_fut_dt_trans_flag IN VARCHAR2,
148 p_dest_commence_dt IN DATE,
149 p_tran_across_careers IN BOOLEAN,
150 p_term_cal_type IN VARCHAR2,
151 p_term_seq_num IN NUMBER
152 ) AS
153 -------------------------------------------------------------------------------------------
154 --Created by : Chandrasekhar Kasu, Oracle IDC
155 --Date created: 20-Nov-2004
156 -- Purpose : This Procedure is used to update the destination program during transfer
157 --Change History:
158 --Who When What
159 --stutta 10-DEC-2004 Removed setting/unsetting of global. It is set in program_trasfer
160 -- _api, before the call to this procedure. Calculating the program
161 -- attempt status and sending it in all ps_att update row call. Bug #4046782
162 -- bdeviset 21-Mar-2005 Modified update_destination_program procedure for Bug# 4248338, 4248367.
163 -- As start date is disabled in transfer page assinging the dest program
164 -- comm dt to src program comm dt so that the user encounters no errors
165 -- while transferring units from src to dest.
166 -- stutta 26-Sep-2005 Added call to create_update_term_rec for bug 4588264
167 --------------------------------------------------------------------------------------------
168 CURSOR c_get_stdnt_ps_att_dtls IS
169 SELECT *
170 FROM IGS_EN_STDNT_PS_ATT
171 WHERE person_id = p_person_id AND
172 course_cd = p_course_cd;
173
174 CURSOR c_get_discont_reason IS
175 SELECT discontinuation_reason_cd
176 FROM IGS_EN_DCNT_REASONCD
177 WHERE dcnt_program_ind = 'Y' AND
178 closed_ind = 'N' AND
179 sys_dflt_ind = 'Y' AND
180 s_discontinuation_reason_type = 'TRANSFER';
181
182 CURSOR c_get_comm_dt_of_src_prg IS
183 SELECT commencement_dt
184 FROM IGS_EN_STDNT_PS_ATT
185 WHERE person_id = p_person_id AND
186 course_cd = p_src_course_cd;
187
188 l_src_commence_dt IGS_EN_STDNT_PS_ATT.COMMENCEMENT_DT%TYPE;
189 l_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
190 l_discont_reason_code IGS_EN_DCNT_REASONCD.discontinuation_reason_cd%TYPE;
191 l_stdnt_ps_attempt_dtls_rec c_get_stdnt_ps_att_dtls%ROWTYPE;
192 l_key_program_flag igs_en_spa_terms.key_program_flag%TYPE := FND_API.G_MISS_CHAR;
193 l_message_name VARCHAR2(2000);
194 BEGIN
195 OPEN c_get_stdnt_ps_att_dtls;
196 FETCH c_get_stdnt_ps_att_dtls INTO l_stdnt_ps_attempt_dtls_rec;
197 IF (c_get_stdnt_ps_att_dtls%FOUND) THEN
198 IF (p_new_dest_key_flag <> l_stdnt_ps_attempt_dtls_rec.key_program AND p_new_dest_key_flag = 'Y') THEN
199 l_key_program_flag := p_new_dest_key_flag;
200 END IF;
201 IF p_dest_fut_dt_trans_flag = 'S' THEN
202 -- if it is an immediate transfer then make the dest as primary and key if source is key
203 -- else if source is non key p_new_dest_key_flag contains the old value of destination program
204 l_stdnt_ps_attempt_dtls_rec.key_program := p_new_dest_key_flag;
205 IF is_career_model_enabled THEN
206 l_stdnt_ps_attempt_dtls_rec.primary_program_type := 'PRIMARY';
207 END IF;
208 ELSIF (p_stdnt_confrm_ind = 'Y' AND l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind = 'N' AND is_career_model_enabled) THEN
209 -- this is to avoid a null value for primary program type when a unconfirmed program attempt
210 -- is the destination program in future dated transfer
211 l_stdnt_ps_attempt_dtls_rec.primary_program_type := 'SECONDARY';
212 END IF;
213
214 IF p_stdnt_confrm_ind = 'Y' AND l_stdnt_ps_attempt_dtls_rec.commencement_dt IS NULL THEN
215 OPEN c_get_comm_dt_of_src_prg;
216 FETCH c_get_comm_dt_of_src_prg INTO l_src_commence_dt;
217 CLOSE c_get_comm_dt_of_src_prg;
218 l_stdnt_ps_attempt_dtls_rec.commencement_dt := NVL(p_dest_commence_dt,l_src_commence_dt);
219 END IF;
220
221 l_stdnt_ps_attempt_dtls_rec.course_attempt_status :=
222 igs_en_gen_006.Enrp_Get_Sca_Status(
223 p_person_id => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
224 p_course_cd => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
225 p_course_attempt_status => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
226 p_student_confirmed_ind => p_stdnt_confrm_ind,
227 p_discontinued_dt => NULL,
228 p_lapsed_dt => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
229 p_course_rqrmnt_complete_ind => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
230 p_logical_delete_dt => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt );
231 igs_en_spa_terms_api.set_spa_term_cal_type(P_TERM_CAL_TYPE);
232 igs_en_spa_terms_api.set_spa_term_sequence_number (p_term_seq_num);
233 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
234 X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
235 X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
236 X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
237 X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
238 X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
239 X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
240 X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
241 X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
242 X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
243 X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
244 X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
245 X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
246 X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
247 X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
248 X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
249 X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
250 X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
251 X_STUDENT_CONFIRMED_IND => p_stdnt_confrm_ind,
252 X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
253 X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
254 X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
255 X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
256 X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
257 X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
258 X_DISCONTINUED_DT => NULL,
259 X_DISCONTINUATION_REASON_CD => NULL,
260 X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
261 X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
262 X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
263 X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
264 X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
265 X_NOMINATED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.nominated_completion_yr,
266 X_NOMINATED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.NOMINATED_COMPLETION_PERD,
267 X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
268 X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
269 X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
270 X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
271 X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
272 X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
273 X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
274 X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
275 x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
276 x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
277 X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
278 x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
279 x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
280 x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
281 x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
282 x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
283 x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
284 x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
285 X_MODE => 'R',
286 X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
287 X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
288 X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
289 X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
290 X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
291 X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
292 X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
293 X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
294 X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
295 X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
296 X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
297 X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
298 X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
299 X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
300 X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
301 X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
302 X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
303 X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
304 X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
305 X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
306 X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
307 X_FUTURE_DATED_TRANS_FLAG => p_dest_fut_dt_trans_flag);
308 igs_en_spa_terms_api.set_spa_term_cal_type(NULL);
309 igs_en_spa_terms_api.set_spa_term_sequence_number (NULL);
310 IF p_dest_fut_dt_trans_flag = 'S' THEN
311 igs_en_spa_terms_api.create_update_term_rec(p_person_id => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
312 p_program_cd => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
313 p_term_cal_type => P_TERM_CAL_TYPE,
314 p_term_sequence_number => p_term_seq_num,
315 p_key_program_flag => l_key_program_flag,
316 p_program_changed => TRUE,
317 p_ripple_frwrd => TRUE,
318 p_message_name => l_message_name,
319 p_update_rec => TRUE);
320 END IF;
321
322
323 END IF; -- end of c_get_stdnt_ps_att_dtls%FOUND)
324
325 CLOSE c_get_stdnt_ps_att_dtls;
326
327 EXCEPTION
328
329 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
330 --allow calls before dml and after dml to this package from igs_en_stdnt_ps_att_pkg to fire
331 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
332 igs_en_spa_terms_api.set_spa_term_cal_type(NULL);
333 igs_en_spa_terms_api.set_spa_term_sequence_number (NULL);
334 RAISE;
335 WHEN FND_API.G_EXC_ERROR THEN
336 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
337 igs_en_spa_terms_api.set_spa_term_cal_type(NULL);
338 igs_en_spa_terms_api.set_spa_term_sequence_number (NULL);
339
340 RAISE;
341 WHEN OTHERS THEN
342 --allow calls before dml and after dml to this package from igs_en_stdnt_ps_att_pkg to fire
343 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
344 igs_en_spa_terms_api.set_spa_term_cal_type(NULL);
345 igs_en_spa_terms_api.set_spa_term_sequence_number (NULL);
346 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
347 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.update_destination_prgm');
348 IGS_GE_MSG_STACK.ADD;
349 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
350 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.update_destination_prgm :',SQLERRM);
351 END IF;
352 App_Exception.Raise_Exception;
353
354 END update_destination_prgm;
355
356
357 PROCEDURE set_dest_prgm_att_params(
358 p_person_id IN NUMBER,
359 p_src_program_cd IN VARCHAR2,
360 p_term_cal_type IN VARCHAR2,
361 p_term_seq_num IN NUMBER,
362 p_dest_primary_prg_type IN OUT NOCOPY VARCHAR2,
363 p_dest_key_prgm_flag IN OUT NOCOPY VARCHAR2,
364 p_dest_commence_dt IN OUT NOCOPY DATE,
365 p_dest_fut_dt_trans_flag IN OUT NOCOPY VARCHAR2,
366 p_transfer_re IN VARCHAR2
367
368 )AS
369 -------------------------------------------------------------------------------------------
370 --Created by : Chandrasekhar Kasu, Oracle IDC
371 --Date created: 20-Nov-2004
372 -- Purpose : This procedure is used to set the destination program attempt key program
373 -- parameter and future dated transfer flag respectively.
374 --Change History:
375 --Who When What
376 --somasekar 13-apr-2005 bug# 4179106 modified to set the future date
377 -- transfer sucessful to 'S'
378 -------------------------------------------------------------------------------------------
379
380 /* This function sets the Destination Program Key Program value, future date
381 transfer flag and destination Primary program Type.
382 In Career mode destination Primary program Type = 'Y' and key program = Y
383 when source key program = 'Y' else set destination program key program = Y
384
385 */
386
387 l_begin_trans_dt_alias IGS_EN_CAL_CONF.BEGIN_TRANS_DT_ALIAS%TYPE;
388 l_begin_trans_dt_alias_val DATE;
389 l_src_key_prgm IGS_EN_STDNT_PS_ATT.KEY_PROGRAM%TYPE;
390 l_src_commence_dt IGS_EN_STDNT_PS_ATT.COMMENCEMENT_DT%TYPE;
391 CURSOR c_get_key_val_frm_src_prg IS
392 SELECT key_program,commencement_dt
393 FROM IGS_EN_STDNT_PS_ATT
394 WHERE person_id = p_person_id AND
395 course_cd = p_src_program_cd;
396 CURSOR c_get_begin_trans_dt_alias IS
397 SELECT begin_trans_dt_alias
398 FROM IGS_EN_CAL_CONF
399 WHERE s_control_num = 1;
400 CURSOR c_get_begin_trans_dt_alias_val(c_begin_dt_alias IGS_EN_CAL_CONF.BEGIN_TRANS_DT_ALIAS%TYPE) IS
401 SELECT ALIAS_VAL
402 FROM IGS_CA_DA_INST_V
403 WHERE dt_alias = c_begin_dt_alias AND
404 cal_type = p_term_cal_type AND
405 ci_sequence_number = p_term_seq_num;
406
407
408 BEGIN
409
410 IF (is_career_model_enabled) THEN
411 p_dest_primary_prg_type := 'PRIMARY';
412 END IF;
413 OPEN c_get_key_val_frm_src_prg;
414 FETCH c_get_key_val_frm_src_prg INTO l_src_key_prgm,l_src_commence_dt;
415 CLOSE c_get_key_val_frm_src_prg;
416
417 IF l_src_key_prgm = 'Y' THEN
418 p_dest_key_prgm_flag := 'Y';
419 END IF;
420
421 IF p_transfer_re = 'Y' THEN
422 IF p_dest_commence_dt <> l_src_commence_dt THEN
423 p_dest_commence_dt := l_src_commence_dt;
424 END IF;
425 END IF;-- end of p_transfer_re = Y IF THEN
426
427 OPEN c_get_begin_trans_dt_alias;
428 FETCH c_get_begin_trans_dt_alias INTO l_begin_trans_dt_alias;
429 IF c_get_begin_trans_dt_alias%FOUND AND l_begin_trans_dt_alias IS NOT NULL THEN
430 CLOSE c_get_begin_trans_dt_alias;
431 OPEN c_get_begin_trans_dt_alias_val(l_begin_trans_dt_alias);
432 FETCH c_get_begin_trans_dt_alias_val INTO l_begin_trans_dt_alias_val;
433 CLOSE c_get_begin_trans_dt_alias_val;
434 IF l_begin_trans_dt_alias_val > TRUNC(SYSDATE) THEN
435 p_dest_fut_dt_trans_flag := 'Y';
436 ELSIF l_begin_trans_dt_alias_val <= TRUNC(SYSDATE) THEN
437 p_dest_fut_dt_trans_flag := 'S';
438 ELSIF l_begin_trans_dt_alias_val IS NULL THEN
439 p_dest_fut_dt_trans_flag := 'S';
440 END IF;
441 RETURN;
442 ELSE
443 CLOSE c_get_begin_trans_dt_alias;
444 p_dest_fut_dt_trans_flag := 'S';
445 RETURN;
446 END IF;
447
448 EXCEPTION
449 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
450 RAISE;
451 WHEN FND_API.G_EXC_ERROR THEN
452 RAISE;
453 WHEN OTHERS THEN
454 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
455 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.set_dest_prgm_att_params');
456 IGS_GE_MSG_STACK.ADD;
457 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
458 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.set_dest_prgm_att_params :',SQLERRM);
459 END IF;
460 App_Exception.Raise_Exception;
461
462 END set_dest_prgm_att_params;
463
464 PROCEDURE create_prgm_admin_record (
465 p_person_id IN NUMBER,
466 p_src_program_cd IN VARCHAR2,
467 p_dest_program_cd IN VARCHAR2,
468 p_acad_cal_type IN VARCHAR2,
469 p_acad_seq_num IN NUMBER
470 ) AS
471 -------------------------------------------------------------------------------------------
472 --Created by : Chandrasekhar Kasu, Oracle IDC
473 --Date created: 20-Nov-2004
474 -- Purpose : This procedure creates program administration record
475 --Change History:
476 --Who When What
477
478 -------------------------------------------------------------------------------------------
479
480 l_enr_cal_type IGS_AS_SC_ATMPT_ENR.CAL_TYPE%TYPE;
481 l_enr_seq_num IGS_AS_SC_ATMPT_ENR.CI_SEQUENCE_NUMBER%TYPE;
482 l_enr_category IGS_AS_SC_ATMPT_ENR.ENROLMENT_CAT%TYPE;
483 l_sub_ci_seq_num IGS_CA_INST_REL.SUB_CI_SEQUENCE_NUMBER%TYPE;
484 l_return_value BOOLEAN;
485 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
486
487 CURSOR c_get_enrolment_cal_type IS
488 SELECT scae.cal_type, scae.ci_sequence_number, scae.enrolment_cat
489 FROM IGS_AS_SC_ATMPT_ENR scae, IGS_CA_INST ci
490 WHERE scae.person_id = p_person_id AND
491 scae.course_cd = p_src_program_cd AND
492 ci.cal_type = scae.cal_type AND
493 ci.sequence_number = scae.ci_sequence_number
494 ORDER BY ci.start_dt DESC;
495
496 CURSOR c_get_cal_rel_dtl_for_enr_cal (c_enr_cal_type IGS_CA_INST_REL.sub_cal_type%TYPE) IS
497 SELECT cir.sub_ci_sequence_number
498 FROM IGS_CA_INST_REL cir
499 WHERE cir.sup_cal_type = p_acad_cal_type AND
500 cir.sup_ci_sequence_number = p_acad_seq_num AND
501 cir.sub_cal_type = c_enr_cal_type;
502
503 BEGIN
504
505 OPEN c_get_enrolment_cal_type;
506 FETCH c_get_enrolment_cal_type INTO l_enr_cal_type,l_enr_seq_num,l_enr_category;
507
508 IF (c_get_enrolment_cal_type%FOUND) THEN
509 CLOSE c_get_enrolment_cal_type;
510 OPEN c_get_cal_rel_dtl_for_enr_cal(l_enr_cal_type);
511 FETCH c_get_cal_rel_dtl_for_enr_cal INTO l_sub_ci_seq_num;
512
513 IF (c_get_cal_rel_dtl_for_enr_cal%FOUND) THEN
514 CLOSE c_get_cal_rel_dtl_for_enr_cal;
515 l_return_value := IGS_EN_GEN_009.ENRP_INS_SCAE_TRNSFR(p_person_id,
516 p_dest_program_cd,
517 l_enr_cal_type,
518 l_sub_ci_seq_num,
519 l_enr_category,
520 l_message_name);
521 ELSE
522 CLOSE c_get_cal_rel_dtl_for_enr_cal;
523 END IF;
524 ELSE
525 CLOSE c_get_enrolment_cal_type;
526 END IF; -- end of c_get_enrolment_cal_type%FOUND IF THEN
527
528 EXCEPTION
529 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
530 RAISE;
531 WHEN FND_API.G_EXC_ERROR THEN
532 RAISE;
533 WHEN OTHERS THEN
534 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
535 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_src_prgm_unt_set_att');
536 IGS_GE_MSG_STACK.ADD;
537 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
538 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.create_prgm_admin_record :',SQLERRM);
539 END IF;
540 App_Exception.Raise_Exception;
541
542 END create_prgm_admin_record;
543
544
545 PROCEDURE check_is_dest_prgm_actv_confrm (
546 p_person_id IN NUMBER,
547 p_source_program_cd IN VARCHAR2,
548 p_acad_cal_type IN VARCHAR2,
549 p_acad_seq_num IN NUMBER,
550 p_dest_program_cd IN VARCHAR2,
551 p_show_warning IN VARCHAR2,
552 p_dest_confirmed_ind IN OUT NOCOPY VARCHAR2
553 ) AS
554 -------------------------------------------------------------------------------------------
555 --Created by : Chandrasekhar Kasu, Oracle IDC
556 --Date created: 20-Nov-2004
557 -- Purpose : This program check whether destination program student confirm indicator is
558 -- active or not and confirms it if it passed validations.
559 --Change History:
560 --Who When What
561
562 -------------------------------------------------------------------------------------------
563
564
565 CURSOR c_get_dest_prg_dtls(c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE) IS
566 SELECT *
567 FROM IGS_EN_STDNT_PS_ATT
568 WHERE person_id = c_person_id AND
569 course_cd = c_program_cd;
570 l_val_sca_confrm_status BOOLEAN;
571 l_val_sca_elgbl_status BOOLEAN;
572 l_message_name FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
573 l_message_name1 FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
574 l_dest_sca_rec c_get_dest_prg_dtls%ROWTYPE;
575
576 BEGIN
577
578 OPEN c_get_dest_prg_dtls(p_person_id,p_dest_program_cd);
579 FETCH c_get_dest_prg_dtls INTO l_dest_sca_rec;
580 CLOSE c_get_dest_prg_dtls;
581 l_val_sca_confrm_status := IGS_EN_VAL_SCA.enrp_val_sca_confirm(p_person_id,
582 p_dest_program_cd,
583 l_dest_sca_rec.adm_admission_appl_number,
584 l_dest_sca_rec.adm_nominated_course_cd,
585 l_dest_sca_rec.adm_sequence_number,
586 l_dest_sca_rec.student_confirmed_ind,
587 l_dest_sca_rec.course_attempt_status,
588 l_message_name);
589 IF l_val_sca_confrm_status = FALSE THEN
590 IF l_message_name IN ('IGS_EN_ASSOCIATE_ADMPRG_APPL') AND p_show_warning = 'Y' THEN
591 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
592 FND_MSG_PUB.ADD;
593 END IF;
594 IF l_message_name IN ('IGS_EN_PRG_ATT_CONF_ENR','IGS_EN_CONF_IND_ONLY_BE_CHANG') THEN
595 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
596 FND_MSG_PUB.ADD;
597 RAISE FND_API.G_EXC_ERROR;
598 END IF;
599 END IF;-- end of l_val_sca_confrm_status = FALSE IF THEN
600
601 l_val_sca_elgbl_status := IGS_EN_GEN_006.ENRP_GET_SCA_ELGBL(p_person_id,
602 p_dest_program_cd,
603 'RETURN',
604 p_acad_cal_type,
605 p_acad_seq_num,
606 'Y',
607 l_message_name1);
608
609 IF l_val_sca_elgbl_status = FALSE THEN
610 IF l_message_name1 NOT IN ('IGS_EN_STUD_INELIG_TO_RE_ENR','IGS_EN_INELIGBLE_DUE_TO_LAPSE',
611 'IGS_EN_STUD_INELIGIBLE_RE_ENR','IGS_EN_STUD_NOT_HAVE_CURR_AFF',
612 'IGS_EN_INTERM_DOES_NOT_END', 'IGS_RE_SUPERV_%_MUST_TOT_100') THEN
613 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name1);
614 FND_MSG_PUB.ADD;
615 RAISE FND_API.G_EXC_ERROR;
616 END IF;
617 END IF;-- end of l_val_sca_elgbl_status = FALSE IF THEN
618
619 p_dest_confirmed_ind := 'Y';
620
621 EXCEPTION
622
623 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
624 RAISE;
625 WHEN FND_API.G_EXC_ERROR THEN
626 RAISE;
627 WHEN OTHERS THEN
628 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
629 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.check_is_dest_prgm_actv_confrm');
630 IGS_GE_MSG_STACK.ADD;
631 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
632 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.check_is_dest_prgm_actv_confrm :',SQLERRM);
633 END IF;
634 App_Exception.Raise_Exception;
635
636 END check_is_dest_prgm_actv_confrm;
637
638
639 PROCEDURE check_for_debt(
640 p_person_id IN NUMBER,
641 p_source_program_cd IN VARCHAR2,
642 p_message_name OUT NOCOPY VARCHAR2
643 ) AS
644 -------------------------------------------------------------------------------------------
645 --Created by : Chandrasekhar Kasu, Oracle IDC
646 --Date created: 20-Nov-2004
647 -- Purpose : This procedure checks for the debt
648 --Change History:
649 --Who When What
650 --bdeviset 08-APR-2005 Changed cursors c_get_debt_for_src_prgm_att and c_get_debt_for_src_prgm_att
651 -- tp access data from igs_fi_inv_int instead of IGS_FI_FEE_AS for bug#4177199
652 -------------------------------------------------------------------------------------------
653 l_debt_exists IGS_FI_FEE_AS.person_id%TYPE;
654 CURSOR c_get_debt_for_src_prgm_att IS
655 SELECT person_id
656 FROM igs_fi_inv_int
657 WHERE person_id = p_person_id
658 AND course_cd = p_source_program_cd
659 AND transaction_type IN ('ASSESSMENT', 'RETENTION', 'SPECIAL')
660 AND invoice_amount_due > 0;
661
662 CURSOR c_getdebt_for_all_prgms IS
663 SELECT person_id
664 FROM igs_fi_inv_int
665 WHERE person_id = p_person_id
666 AND course_cd IS NULL
667 AND invoice_amount_due > 0;
668
669 BEGIN
670 OPEN c_get_debt_for_src_prgm_att;
671 FETCH c_get_debt_for_src_prgm_att INTO l_debt_exists;
672 IF ( c_get_debt_for_src_prgm_att%FOUND ) THEN
673 p_message_name := 'IGS_EN_STUD_EXIST_DEBT_PRG';
674 CLOSE c_get_debt_for_src_prgm_att;
675 RETURN;
676 ELSE
677 CLOSE c_get_debt_for_src_prgm_att;
678 END IF;
679
680 OPEN c_getdebt_for_all_prgms;
681 FETCH c_getdebt_for_all_prgms INTO l_debt_exists;
682 IF ( c_getdebt_for_all_prgms%FOUND ) THEN
683 p_message_name := 'IGS_EN_PRSN_EXISTING_DEBT';
684 CLOSE c_getdebt_for_all_prgms;
685 RETURN;
686 ELSE
687 CLOSE c_getdebt_for_all_prgms;
688 END IF;
689
690 EXCEPTION
691 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
692 RAISE;
693 WHEN FND_API.G_EXC_ERROR THEN
694 RAISE;
695 WHEN OTHERS THEN
696 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
697 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.check_for_debt');
698 IGS_GE_MSG_STACK.ADD;
699 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
700 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.check_for_debt :',SQLERRM);
701 END IF;
702 App_Exception.Raise_Exception;
703
704 END check_for_debt;
705
706 PROCEDURE check_for_holds(
707 p_person_id IN NUMBER,
708 p_dest_program_cd IN VARCHAR2,
709 p_term_cal_type IN VARCHAR2,
710 p_term_seq_num IN NUMBER,
711 p_person_type IN VARCHAR2,
712 p_return_status IN OUT NOCOPY VARCHAR2,
713 p_show_warning IN VARCHAR2
714 )AS
715 -------------------------------------------------------------------------------------------
716 --Created by : Chandrasekhar Kasu, Oracle IDC
717 --Date created: 20-Nov-2004
718 -- Purpose : This procedure checks for the holds
719 --Change History:
720 --Who When What
721 -- ckasu 07-JAN-2005 Modified code inorder to include check for deny all enrollment
722 -- activity as a part of bug# 4083552
723
724 -------------------------------------------------------------------------------------------
725 l_deny_warn VARCHAR2(100);
726 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
727 l_hold_status BOOLEAN;
728
729 v_message_name varchar2(30);
730 v_message_name2 varchar2(30);
731 v_first_message_name varchar2(30);
732 v_first_message_name2 varchar2(30);
733 v_return_type VARCHAR2(1);
734 v_attendance_types VARCHAR2(100);
735 v_encmb_fail BOOLEAN;
736
737
738 BEGIN
739
740 -- checks whether Student is excluded from the Admission or Enrolment of destination course or not
741
742 l_hold_status := IGS_EN_VAL_ENCMB.enrp_val_excld_crs(p_person_id,
743 p_dest_program_cd,
744 SYSDATE,
745 l_message_name
746 );
747 IF l_hold_status = FALSE THEN
748
749 IF p_show_warning = 'Y' THEN
750
751 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_NOTTRNS_NOMINATED_PRG');
752 FND_MSG_PUB.ADD;
753 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PERS_ENCUMB_SYS');
754 FND_MSG_PUB.ADD;
755 END IF;
756
757 END IF;
758
759
760 IF IGS_EN_VAL_ENCMB.enrp_val_enr_encmb( -- -- calling with census date
761 p_person_id,
762 p_dest_program_cd,
763 p_term_cal_type,
764 p_term_seq_num,
765 v_message_name,
766 v_message_name2,
767 v_return_type,
768 SYSDATE
769 ) = FALSE THEN
770
771 IF p_show_warning = 'Y' AND (l_message_name <> NVL(v_message_name,v_message_name2)) THEN
772
773 -- log the warning message here
774
775 FND_MESSAGE.SET_NAME( 'IGS' ,NVL(v_message_name,v_message_name2));
776 FND_MSG_PUB.ADD;
777
778 FND_MESSAGE.SET_NAME( 'IGS' ,'IGS_EN_PERS_ENCUMB_SYS');
779 FND_MSG_PUB.ADD;
780
781 END IF;
782
783 END IF; -- end of IF enrp_val_enr_encmb_pt(
784
785
786 -- check whether a hold with Deny All Enrollment acticity effect
787 -- exists for the student or not
788
789 igs_en_elgbl_person.eval_ss_deny_all_hold (
790 p_person_id => p_person_id,
791 p_person_type => p_person_type,
792 p_course_cd => p_dest_program_cd,
793 p_load_calendar_type => p_term_cal_type,
794 p_load_cal_sequence_number => p_term_seq_num,
795 p_status => l_deny_warn,
796 p_message => l_message_name);
797
798 IF l_deny_warn = 'E' THEN
799 IF l_message_name IS NOT NULL THEN
800 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
801 FND_MSG_PUB.ADD;
802 p_return_status := FND_API.G_RET_STS_ERROR;
803 RAISE FND_API.G_EXC_ERROR;
804 END IF;
805 END IF;-- end of l_deny_warn = 'E' IF THEN
806
807
808
809 EXCEPTION
810 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
811 RAISE;
812 WHEN FND_API.G_EXC_ERROR THEN
813 RAISE;
814 WHEN OTHERS THEN
815 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
816 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.check_for_holds');
817 IGS_GE_MSG_STACK.ADD;
818 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
819 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.check_for_holds :',SQLERRM);
820 END IF;
821 App_Exception.Raise_Exception;
822
823 END check_for_holds;
824
825 FUNCTION enrp_val_excld_unit_pt(
826 p_person_id IN NUMBER ,
827 p_course_cd IN VARCHAR2 ,
828 p_unit_cd IN VARCHAR2 ,
829 p_effective_dt IN DATE ,
830 p_message_name OUT NOCOPY VARCHAR2)
831 RETURN BOOLEAN AS
832
833 BEGIN
834 DECLARE
835 v_pue_start_dt IGS_PE_PERS_UNT_EXCL.pue_start_dt%TYPE;
836 v_expiry_dt IGS_PE_PERS_UNT_EXCL.expiry_dt%TYPE;
837 CURSOR c_psd_ed IS
838 SELECT pue.pue_start_dt,
839 pue.expiry_dt
840 FROM IGS_PE_PERSENC_EFFCT pee,
841 IGS_PE_PERS_UNT_EXCL pue
842 WHERE pee.person_id = p_person_id AND
843 pee.s_encmb_effect_type = 'EXC_CRS_U' AND
844 pee.course_cd = p_course_cd AND
845 pue.person_id = pee.person_id AND
846 pue.encumbrance_type = pee.encumbrance_type AND
847 pue.pen_start_dt = pee.pen_start_dt AND
848 pue.s_encmb_effect_type = pee.s_encmb_effect_type AND
849 pue.pee_start_dt = pee.pee_start_dt AND
850 pue.pee_sequence_number = pee.sequence_number AND
851 pue.unit_cd = p_unit_cd;
852 BEGIN
853 -- This function validates whether or not a IGS_PE_PERSON is
854 -- excluded from admission or enrolment in a specific IGS_PS_UNIT.
855 p_message_name := null;
856 -- Validate the input parameters
857 IF p_person_id IS NULL OR
858 p_course_cd IS NULL OR
859 p_unit_cd IS NULL OR
860 p_effective_dt IS NULL THEN
861 p_message_name := null;
862 RETURN TRUE;
863 END IF;
864
865 --Validate for an exclusion from a specific IGS_PS_UNIT.
866 OPEN c_psd_ed;
867 LOOP
868 FETCH c_psd_ed INTO v_pue_start_dt,
869 v_expiry_dt;
870 EXIT WHEN c_psd_ed%NOTFOUND;
871 --Validate if the dates of a returned record overlap with the effective date.
872 IF v_expiry_dt IS NULL THEN
873 IF v_pue_start_dt <= p_effective_dt THEN
874 CLOSE c_psd_ed;
875 p_message_name := 'IGS_EN_PRSN_ENCUMB_EXC_ENR';
876 RETURN FALSE;
877 END IF;
878 ELSE
879 IF p_effective_dt BETWEEN v_pue_start_dt AND (v_expiry_dt - 1) THEN
880 CLOSE c_psd_ed;
881 p_message_name := 'IGS_EN_PRSN_ENCUMB_EXC_ENR';
882 RETURN FALSE;
883 END IF;
884 END IF;
885 END LOOP;
886 CLOSE c_psd_ed;
887 --- Return the default value
888 p_message_name := null;
889 RETURN TRUE;
890 END;
891 EXCEPTION
892 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
893 RAISE;
894 WHEN FND_API.G_EXC_ERROR THEN
895 RAISE;
896 WHEN OTHERS THEN
897 Fnd_Message.Set_Name('IGS', 'IGS_GE_UNHANDLED_EXP');
898 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.enrp_val_excld_unit_pt');
899 IGS_GE_MSG_STACK.ADD;
900 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
901 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.enrp_val_excld_unit_pt :',SQLERRM);
902 END IF;
903 App_Exception.Raise_Exception;
904
905 END enrp_val_excld_unit_pt;
906
907
908 PROCEDURE upd_or_create_dest_term_rec(
909 p_person_id IN NUMBER,
910 p_dest_program_cd IN VARCHAR2,
911 p_term_cal_type IN VARCHAR2,
912 p_term_seq_num IN NUMBER,
913 p_key_program_flag IN VARCHAR2,
914 p_message_name OUT NOCOPY VARCHAR2,
915 p_dest_fut_dt_trans_flag IN VARCHAR2
916
917 ) AS
918
919 -------------------------------------------------------------------------------------------
920 --Created by : Chandrasekhar Kasu, Oracle IDC
921 --Date created: 20-Nov-2004
922 -- Purpose : this function is used to create or update destination program attempt
923 --Change History:
924 --Who When What
925 -- stutta 26-Sep-2005 Added call to create_update_term_rec for bug 4588264
926 -------------------------------------------------------------------------------------------
927
928 l_key_program_flag igs_en_spa_terms.key_program_flag%TYPE;
929 BEGIN
930
931 IF p_dest_fut_dt_trans_flag = 'Y' THEN
932 -- delete the global table before populating it
933
934 l_key_program_flag := FND_API.G_MISS_CHAR;
935 IF p_key_program_flag = 'Y' THEN
936 l_key_program_flag := p_key_program_flag;
937 END IF;
938 igs_en_spa_terms_api.create_update_term_rec(p_person_id => p_person_id,
939 p_program_cd => p_dest_program_cd,
940 p_term_cal_type => p_term_cal_type,
941 p_term_sequence_NUMBER => p_term_seq_num,
942 p_key_program_flag => l_key_program_flag,
943 p_ripple_frwrd => TRUE,
944 p_program_changed => TRUE,
945 p_message_name => p_message_name,
946 p_update_rec => TRUE);
947
948
949 END IF;
950
951 EXCEPTION
952 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
953 RAISE;
954 WHEN FND_API.G_EXC_ERROR THEN
955 RAISE;
956 WHEN OTHERS THEN
957 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
958 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.upd_or_create_dest_term_rec');
959 IGS_GE_MSG_STACK.ADD;
960 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
961 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.upd_or_create_dest_term_rec :',SQLERRM);
962 END IF;
963 App_Exception.Raise_Exception;
964
965 END upd_or_create_dest_term_rec;
966
967 FUNCTION is_tranfer_across_careers(
968 p_src_program_cd IN VARCHAR2,
969 p_src_progam_ver IN NUMBER,
970 p_dest_program_cd IN VARCHAR2,
971 p_dest_prog_ver IN NUMBER,
972 p_src_career_type OUT NOCOPY VARCHAR2
973 ) RETURN BOOLEAN AS
974
975 -------------------------------------------------------------------------------------------
976 --Created by : Chandrasekhar Kasu, Oracle IDC
977 --Date created: 20-Nov-2004
978 -- Purpose : This function returns when Transfer is across careers and
979 -- false when transfer is with in the careers
980 --Change History:
981 --Who When What
982
983 -------------------------------------------------------------------------------------------
984
985 CURSOR c_get_career_type(c_program_cd IGS_PS_VER.course_cd%TYPE,c_program_ver IGS_PS_VER.version_number%TYPE) IS
986 SELECT course_type
987 FROM IGS_PS_VER
988 WHERE course_cd = c_program_cd AND
989 version_number = c_program_ver;
990 l_src_prgm_career IGS_PS_VER.COURSE_TYPE%TYPE;
991 l_dest_prgm_career IGS_PS_VER.COURSE_TYPE%TYPE;
992
993 BEGIN
994
995 OPEN c_get_career_type(p_src_program_cd,p_src_progam_ver);
996 FETCH c_get_career_type INTO l_src_prgm_career;
997 CLOSE c_get_career_type;
998 OPEN c_get_career_type(p_dest_program_cd,p_dest_prog_ver);
999 FETCH c_get_career_type INTO l_dest_prgm_career;
1000 CLOSE c_get_career_type;
1001 IF (l_src_prgm_career <> l_dest_prgm_career) THEN
1002 p_src_career_type := l_src_prgm_career;
1003 RETURN TRUE;
1004 ELSE
1005 RETURN FALSE;
1006 END IF;
1007
1008 END is_tranfer_across_careers;
1009
1010
1011 PROCEDURE getunits_in_src_notin_dest_prg(
1012 p_person_id IN NUMBER,
1013 p_source_program_cd IN VARCHAR2,
1014 p_dest_program_cd IN VARCHAR2,
1015 p_uoo_ids_transfered IN VARCHAR2,
1016 p_drop IN BOOLEAN,
1017 p_show_warning IN VARCHAR2
1018 ) AS
1019 -------------------------------------------------------------------------------------------
1020 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
1021 -- Purpose : returns all units in sorce that are not in destination
1022 --Change History:
1023 --Who When What
1024
1025 --------------------------------------------------------------------------------------------
1026 l_unit_not_in_dest_prgm BOOLEAN;
1027 l_uooid NUMBER;
1028 l_unchk_units_in_src VARCHAR2(2000);
1029
1030 CURSOR c_get_all_enr_waitlstd_units IS
1031 SELECT unit_cd,uoo_id
1032 FROM IGS_EN_SU_ATTEMPT
1033 WHERE person_id = p_person_id AND
1034 course_cd = p_source_program_cd AND
1035 unit_attempt_status IN ('ENROLLED','WAITLISTED','INVALID');
1036 l_cindex NUMBER;
1037 l_temp_uoo_ids VARCHAR2(1000);
1038
1039 BEGIN
1040
1041 l_temp_uoo_ids := ',' || p_uoo_ids_transfered || ',' ;
1042 -- getting all unchecked/unselected units in Source whose status is Enrolled or Waitlisted
1043 -- or Invalid during Transfer.
1044
1045 FOR l_all_units_in_src_prgm_rec IN c_get_all_enr_waitlstd_units LOOP
1046
1047 l_cindex := INSTR(l_temp_uoo_ids,','||l_all_units_in_src_prgm_rec.uoo_id||',',1,1);
1048 IF l_cindex = 0 THEN
1049 IF l_unchk_units_in_src IS NULL THEN
1050 l_unchk_units_in_src := l_all_units_in_src_prgm_rec.unit_cd ;
1051 ELSE
1052 l_unchk_units_in_src := l_unchk_units_in_src || ' , '|| l_all_units_in_src_prgm_rec.unit_cd ;
1053 END IF;
1054 END IF;-- end of l_cindex <> 0 IF THEN
1055 END LOOP;--end of units in src Prgm Loop
1056
1057 -- when these units are being dropped in the immediate transfer then show the message
1058 IF p_show_warning = 'Y' AND l_unchk_units_in_src IS NOT NULL AND p_drop THEN
1059 FND_MESSAGE.SET_NAME('IGS','IGS_EN_TRN_EN_WL_U_NO_SEL');
1060 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unchk_units_in_src);
1061 FND_MSG_PUB.ADD;
1062 END IF;
1063
1064 EXCEPTION
1065 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1066 RAISE;
1067 WHEN FND_API.G_EXC_ERROR THEN
1068 RAISE;
1069 WHEN OTHERS THEN
1070 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1071 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.getunits_in_src_notin_dest_prg');
1072 IGS_GE_MSG_STACK.ADD;
1073 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1074 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.getunits_in_src_notin_dest_prg :',SQLERRM);
1075 END IF;
1076 App_Exception.Raise_Exception;
1077
1078 END getunits_in_src_notin_dest_prg;
1079
1080
1081 PROCEDURE val_unchk_sub_units(
1082 p_person_id IN NUMBER,
1083 p_source_program_cd IN VARCHAR2,
1084 p_uoo_ids_transfered IN OUT NOCOPY VARCHAR2,
1085 p_drop IN BOOLEAN,
1086 p_show_warning IN VARCHAR2
1087
1088 ) AS
1089 -------------------------------------------------------------------------------------------
1090 -- Created by : Susmitha Tutta, Oracle Student Systems Oracle IDC
1091 -- Purpose : Throws warning if a superior is checked and subordinate unchecked for transfer
1092 -- and unchecked source units are not to be dropped.
1093 --Change History:
1094 --Who When What
1095
1096 --------------------------------------------------------------------------------------------
1097 l_unit_not_in_dest_prgm BOOLEAN;
1098 l_uooid NUMBER;
1099 l_unchk_units_in_src VARCHAR2(2000);
1100
1101 CURSOR c_get_all_src_units IS
1102 SELECT sua.unit_cd,sua.uoo_id, uoo.sup_uoo_id
1103 FROM IGS_EN_SU_ATTEMPT sua, IGS_PS_UNIT_OFR_OPT uoo
1104 WHERE person_id = p_person_id AND
1105 course_cd = p_source_program_cd AND
1106 uoo.uoo_id = sua.uoo_id AND
1107 uoo.sup_uoo_id IS NOT NULL AND
1108 sua.unit_attempt_status <> 'DROPPED';
1109 l_cindex NUMBER;
1110 l_temp_uoo_ids VARCHAR2(1000);
1111 l_sub_not_selected BOOLEAN := FALSE;
1112 BEGIN
1113
1114 l_temp_uoo_ids := ',' || p_uoo_ids_transfered || ',' ;
1115
1116 FOR l_all_src_units_rec IN c_get_all_src_units LOOP
1117
1118 IF (INSTR(l_temp_uoo_ids,','||l_all_src_units_rec.sup_uoo_id||',',1,1) <>0
1119 AND INSTR (l_temp_uoo_ids,','||l_all_src_units_rec.uoo_id||',',1,1) = 0) THEN
1120 p_uoo_ids_transfered := p_uoo_ids_transfered||','||l_all_src_units_rec.uoo_id;
1121 l_sub_not_selected := TRUE;
1122 END IF;
1123 END LOOP;
1124
1125 -- when these units are being dropped in the immediate transfer then show the message
1126 IF p_show_warning = 'Y' AND l_sub_not_selected AND NOT p_drop THEN
1127 FND_MESSAGE.SET_NAME('IGS','IGS_EN_TRN_SUB_NO_SEL_DROP'); -- subordinates units of all superiors unit selected for transfer will be dropped.
1128 FND_MSG_PUB.ADD;
1129 END IF;
1130
1131 EXCEPTION
1132 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1133 RAISE;
1134 WHEN FND_API.G_EXC_ERROR THEN
1135 RAISE;
1136 WHEN OTHERS THEN
1137 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1138 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.val_unchk_sub_units');
1139 IGS_GE_MSG_STACK.ADD;
1140 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1141 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.val_unchk_sub_units :',SQLERRM);
1142 END IF;
1143 App_Exception.Raise_Exception;
1144
1145 END val_unchk_sub_units;
1146
1147
1148 PROCEDURE validate_person_steps(
1149 p_person_id IN NUMBER,
1150 p_dest_program_cd IN VARCHAR2,
1151 p_dest_prog_ver IN NUMBER,
1152 p_term_cal_type IN VARCHAR2,
1153 p_term_seq_num IN NUMBER,
1154 p_acad_cal_type IN VARCHAR2,
1155 p_acad_seq_num IN NUMBER,
1156 p_person_type IN VARCHAR2,
1157 p_show_warning IN VARCHAR2,
1158 p_return_status IN OUT NOCOPY VARCHAR2
1159 ) AS
1160
1161 -------------------------------------------------------------------------------------------
1162 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
1163 -- Purpose : this procedure validate all Person steps during program transfer
1164 --Change History:
1165 --Who When What
1166
1167 --------------------------------------------------------------------------------------------
1168
1169 l_deny_warn VARCHAR2(100);
1170 l_message_name VARCHAR2(4000);
1171 l_enrolment_cat IGS_PS_TYPE.ENROLMENT_CAT%TYPE;
1172 l_en_cal_type IGS_CA_INST.CAL_TYPE%TYPE;
1173 l_en_ci_seq_num IGS_CA_INST.SEQUENCE_NUMBER%TYPE;
1174 l_commencement_type VARCHAR2(20);
1175 l_dummy VARCHAR2(100);
1176 l_enroll_mtd_type IGS_EN_METHOD_TYPE.ENR_METHOD_TYPE%TYPE;
1177 l_personsteps_vald_status BOOLEAN;
1178
1179 CURSOR c_get_enr_method_type IS
1180 SELECT enr_method_type
1181 FROM IGS_EN_METHOD_TYPE
1182 WHERE transfer_flag = 'Y' AND
1183 closed_ind ='N';
1184
1185 BEGIN
1186
1187 l_commencement_type := NULL;
1188
1189 l_enrolment_cat := IGS_EN_GEN_003.Enrp_Get_Enr_Cat(p_person_id =>p_person_id,
1190 p_course_cd =>p_dest_program_cd,
1191 p_cal_type =>p_acad_cal_type,
1192 p_ci_sequence_number =>p_acad_seq_num,
1193 p_session_enrolment_cat =>NULL,
1194 p_enrol_cal_type =>l_en_cal_type,
1195 p_enrol_ci_sequence_number =>l_en_ci_seq_num,
1196 p_commencement_type =>l_commencement_type,
1197 p_enr_categories =>l_dummy);
1198
1199 OPEN c_get_enr_method_type;
1200 FETCH c_get_enr_method_type INTO l_enroll_mtd_type;
1201 CLOSE c_get_enr_method_type;
1202
1203
1204 l_personsteps_vald_status := igs_en_elgbl_person.eval_person_steps(
1205 p_person_id =>p_person_id,
1206 p_person_type =>p_person_type,
1207 p_load_calendar_type =>p_term_cal_type,
1208 p_load_cal_sequence_number =>p_term_seq_num,
1209 p_program_cd =>p_dest_program_cd,
1210 p_program_version =>p_dest_prog_ver,
1211 p_enrollment_category =>l_enrolment_cat,
1212 p_comm_type =>l_commencement_type,
1213 p_enrl_method =>l_enroll_mtd_type,
1214 p_message =>l_message_name,
1215 p_deny_warn =>l_deny_warn,
1216 p_calling_obj =>'JOB',
1217 p_create_warning =>'N'
1218 );
1219
1220
1221 IF l_personsteps_vald_status AND l_deny_warn = 'WARN' THEN
1222
1223 IF l_message_name IS NOT NULL AND p_show_warning = 'Y' THEN
1224 parse_messages( l_message_name);
1225 END IF;
1226
1227 END IF; -- end of l_personsteps_vald_status = 'FALSE' AND l_deny_warn = 'WARN' IF THEN
1228
1229 IF NOT l_personsteps_vald_status AND l_deny_warn = 'DENY' THEN
1230
1231 IF l_message_name IS NOT NULL AND p_show_warning = 'Y' THEN
1232 parse_messages( l_message_name );
1233 p_return_status := FND_API.G_RET_STS_ERROR;
1234 END IF;
1235
1236 IF l_message_name IS NOT NULL AND p_show_warning = 'N' THEN
1237 parse_messages( l_message_name );
1238 p_return_status := FND_API.G_RET_STS_ERROR;
1239 RAISE FND_API.G_EXC_ERROR;
1240 END IF;
1241
1242 END IF; -- end of l_personsteps_vald_status = 'FALSE' AND l_deny_warn = 'DENY' IF THEN
1243
1244
1245 EXCEPTION
1246 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1247 RAISE;
1248 WHEN FND_API.G_EXC_ERROR THEN
1249 RAISE;
1250 WHEN OTHERS THEN
1251 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1252 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_person_steps');
1253 IGS_GE_MSG_STACK.ADD;
1254 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1255 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_person_steps :',SQLERRM);
1256 END IF;
1257 App_Exception.Raise_Exception;
1258
1259 END validate_person_steps;
1260
1261
1262 PROCEDURE validate_candidacy_tran_dtls(
1263 p_person_id IN NUMBER,
1264 p_source_program_cd IN VARCHAR2,
1265 p_source_prog_ver IN NUMBER,
1266 p_dest_program_cd IN VARCHAR2,
1267 p_dest_prog_ver IN NUMBER,
1268 p_show_warning IN VARCHAR2,
1269 p_return_status IN OUT NOCOPY VARCHAR2
1270 ) AS
1271
1272 -------------------------------------------------------------------------------------------
1273 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
1274 -- Purpose : This procedure validates Candidacy transfer details
1275 --Change History:
1276 --Who When What
1277 --stutta 13-DEC-2004 Donot validate research candidacy before updating destination program
1278 -- if the destination program attempt is discontinued, validate it after
1279 -- updating destination program. Bug #4048290
1280 --------------------------------------------------------------------------------------------
1281
1282 l_candidacy_tran_status BOOLEAN;
1283 l_res_elgbl_status BOOLEAN;
1284 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1285 l_elgbl_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1286
1287 BEGIN
1288
1289 l_candidacy_tran_status := IGS_RE_VAL_CA.resp_val_ca_sca(p_person_id,
1290 NULL, -- sequence number not known
1291 NULL, -- old course code does not apply
1292 p_dest_program_cd,
1293 NULL,
1294 NULL,
1295 NULL, -- admission details does not apply
1296 l_message_name);
1297
1298 IF (l_candidacy_tran_status = FALSE AND p_show_warning = 'Y') THEN
1299 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1300 FND_MSG_PUB.ADD;
1301 p_return_status := FND_API.G_RET_STS_ERROR;
1302 END IF;
1303
1304 IF (l_candidacy_tran_status = FALSE AND p_show_warning = 'N') THEN
1305 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1306 FND_MSG_PUB.ADD;
1307 RAISE FND_API.G_EXC_ERROR;
1308 END IF;
1309
1310 l_candidacy_tran_status := IGS_EN_INS_CA_TRNSFR.enrp_ins_ca_trnsfr(p_person_id,
1311 p_dest_program_cd,
1312 NULL,
1313 NULL,
1314 NULL,
1315 p_source_program_cd,
1316 'SCA',
1317 l_message_name);
1318
1319 IF (l_candidacy_tran_status = FALSE AND p_show_warning = 'Y') THEN
1320 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1321 FND_MSG_PUB.ADD;
1322 p_return_status := FND_API.G_RET_STS_ERROR;
1323 END IF;
1324
1325 IF (l_candidacy_tran_status = FALSE AND p_show_warning = 'N') THEN
1326 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1327 FND_MSG_PUB.ADD;
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330
1331 l_res_elgbl_status := IGS_EN_VAL_SCA.enrp_val_res_elgbl(p_person_id,
1332 p_dest_program_cd,
1333 p_dest_prog_ver,
1334 l_elgbl_message_name);
1335
1336 IF (l_elgbl_message_name IS NOT NULL AND p_show_warning = 'Y') THEN
1337 IF l_elgbl_message_name NOT IN ('IGS_RE_SUPERV_%_MUST_TOT_100') THEN
1338 FND_MESSAGE.SET_NAME( 'IGS' , l_elgbl_message_name);
1339 FND_MSG_PUB.ADD;
1340 p_return_status := FND_API.G_RET_STS_ERROR;
1341 END IF;
1342 END IF;
1343
1344
1345 IF (l_elgbl_message_name IS NOT NULL AND p_show_warning = 'N') THEN
1346 IF l_elgbl_message_name NOT IN ('IGS_RE_SUPERV_%_MUST_TOT_100') THEN
1347 FND_MESSAGE.SET_NAME( 'IGS' , l_elgbl_message_name);
1348 FND_MSG_PUB.ADD;
1349 RAISE FND_API.G_EXC_ERROR;
1350 END IF;
1351 END IF;
1352
1353 EXCEPTION
1354 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1355 RAISE;
1356 WHEN FND_API.G_EXC_ERROR THEN
1357 RAISE;
1358 WHEN OTHERS THEN
1359 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1360 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_candidacy_tran_dtls');
1361 IGS_GE_MSG_STACK.ADD;
1362 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1363 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_candidacy_tran_dtls :',SQLERRM);
1364 END IF;
1365 App_Exception.Raise_Exception;
1366
1367 END validate_candidacy_tran_dtls;
1368
1369
1370 PROCEDURE validate_advance_st_tran_dtls(
1371 p_person_id IN NUMBER,
1372 p_source_program_cd IN VARCHAR2,
1373 p_source_prog_ver IN NUMBER,
1374 p_dest_program_cd IN VARCHAR2,
1375 p_dest_prog_ver IN NUMBER,
1376 p_show_warning IN VARCHAR2
1377 ) AS
1378
1379 -------------------------------------------------------------------------------------------
1380 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
1381 -- Purpose : This procedure validates Advance Standing details that are
1382 -- to be transfered is exists for source program.
1383 --Change History:
1384 --Who When What
1385 --bdeviset 06-SEP-2006 Bug# 5525374.The message retunred from IGS_EN_GEN_010.adv_stand_trans (IGS_EN_STDNT_ADV_STND_EXIST)
1386 -- is a warning message and should be added to the stack only when p_show_warning is passed as 'Y'
1387 -------------------------------------------------------------------------------------------
1388
1389 l_val VARCHAR2(1);
1390 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1391
1392 CURSOR c_is_adv_st_exists(c_person_id IGS_AV_ADV_STANDING.PERSON_ID%TYPE,
1393 c_source_program_cd IGS_AV_ADV_STANDING.COURSE_CD%TYPE,
1394 c_source_prog_ver IGS_AV_ADV_STANDING.VERSION_NUMBER%TYPE )IS
1395 SELECT 'x'
1396 FROM IGS_AV_ADV_STANDING
1397 WHERE person_id = c_person_id AND
1398 course_cd = c_source_program_cd AND
1399 version_number = c_source_prog_ver;
1400
1401
1402 BEGIN
1403
1404 OPEN c_is_adv_st_exists(p_person_id,p_source_program_cd,p_source_prog_ver);
1405 FETCH c_is_adv_st_exists INTO l_val;
1406 IF (c_is_adv_st_exists%FOUND) THEN
1407 CLOSE c_is_adv_st_exists;
1408 IGS_EN_GEN_010.adv_stand_trans(p_person_id => p_person_id,
1409 p_course_cd => p_source_program_cd,
1410 p_version_number => p_source_prog_ver,
1411 p_course_cd_new => p_dest_program_cd,
1412 p_version_number_new => p_dest_prog_ver,
1413 p_message_name => l_message_name);
1414 ELSE
1415 CLOSE c_is_adv_st_exists;
1416 END IF; -- end of IF THEN ELSE
1417
1418 IF p_show_warning = 'Y' AND l_message_name IS NOT NULL THEN
1419 FND_MESSAGE.SET_NAME( 'IGS' ,l_message_name);
1420 FND_MSG_PUB.ADD;
1421 END IF;-- end of l_message_name IF THEN
1422
1423 EXCEPTION
1424 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
1425 RAISE;
1426 WHEN FND_API.G_EXC_ERROR THEN
1427 RAISE;
1428 WHEN OTHERS THEN
1429 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1430 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_advance_st_tran_dtls');
1431 IGS_GE_MSG_STACK.ADD;
1432 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
1433 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_advance_st_tran_dtls :',SQLERRM);
1434 END IF;
1435 App_Exception.Raise_Exception;
1436
1437 END validate_advance_st_tran_dtls;
1438
1439 FUNCTION is_sua_enroll_eff_fut_term( p_person_id IN NUMBER,
1440 p_dest_program_cd IN VARCHAR2,
1441 p_term_cal_type IN VARCHAR2,
1442 p_term_seq_num IN NUMBER)
1443 RETURN BOOLEAN AS
1444
1445 -------------------------------------------------------------------------------------------
1446 -- Created by : bdeviset, Oracle Student Systems Oracle IDC
1447 -- Purpose : Checks if there are any enrolled/waitlisted/invalid unit attempts in the
1448 -- effective and future terms.If so returns true else false.
1449 --Change History:
1450 --Who When What
1451
1452 -------------------------------------------------------------------------------------------
1453
1454 -- cursor to get all the enrolled unit attempts uooid against destination program
1455 CURSOR c_enroll_sua (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1456 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
1457
1458 SELECT uoo_id
1459 FROM IGS_EN_SU_ATTEMPT sua
1460 WHERE sua.person_id = cp_person_id
1461 AND sua.course_cd = cp_course_cd
1462 AND sua.unit_attempt_status IN ('ENROLLED','WAITLISTED','INVALID');
1463
1464 l_unit_exists BOOLEAN;
1465
1466 BEGIN
1467
1468 l_unit_exists := FALSE;
1469 FOR c_enroll_sua_rec IN c_enroll_sua(p_person_id,p_dest_program_cd) LOOP
1470
1471 -- For each enrolled/waitlisted/invalid unit ateempt check whether it is in
1472 -- effective and future terms
1473 -- if so set the flag to true and exit
1474 IF IGS_EN_GEN_010.unit_effect_or_future_term(
1475 p_person_id => p_person_id,
1476 p_dest_course_cd => p_dest_program_cd,
1477 p_uoo_id => c_enroll_sua_rec.uoo_id,
1478 p_term_cal_type => p_term_cal_type ,
1479 p_term_seq_num => p_term_seq_num) THEN
1480
1481
1482 l_unit_exists := TRUE;
1483 EXIT;
1484
1485 END IF;
1486
1487 END LOOP;
1488
1489 RETURN l_unit_exists;
1490
1491
1492 END is_sua_enroll_eff_fut_term;
1493
1494 FUNCTION is_unit_rel_dest_acad_cal (p_person_id IN NUMBER,
1495 p_source_program_cd IN VARCHAR2,
1496 p_dest_program_cd IN VARCHAR2,
1497 p_uoo_id IN NUMBER,
1498 p_message_name OUT NOCOPY VARCHAR2)
1499 RETURN BOOLEAN AS
1500 CURSOR c_sca_detls (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1501 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
1502 SELECT cal_type
1503 FROM igs_en_stdnt_ps_att
1504 WHERE person_id = cp_person_id AND
1505 course_cd = cp_course_cd ;
1506 l_old_cal_type igs_en_stdnt_ps_att.cal_type%TYPE;
1507 l_new_cal_type igs_en_stdnt_ps_att.cal_type%TYPE;
1508 BEGIN
1509 -- get source acad cal type
1510 OPEN c_sca_detls(p_person_id, p_source_program_cd );
1511 FETCH c_sca_detls INTO l_old_cal_type;
1512 CLOSE c_sca_detls;
1513 -- get dest cal type
1514 OPEN c_sca_detls(p_person_id, p_dest_program_cd );
1515 FETCH c_sca_detls INTO l_new_cal_type;
1516 CLOSE c_sca_detls;
1517
1518 IF l_old_cal_type <> l_new_cal_type THEN
1519 IF IGS_EN_VAL_SCT.enrp_val_sua_acad (p_person_id,
1520 p_source_program_cd,
1521 p_uoo_id,
1522 l_new_cal_type,
1523 p_message_name) = FALSE THEN
1524 RETURN FALSE;
1525 END IF;
1526 END IF;
1527
1528 RETURN TRUE;
1529
1530 END is_unit_rel_dest_acad_cal;
1531
1532
1533 PROCEDURE validate_src_prgm_unt_attempts(
1534 p_person_id IN NUMBER,
1535 p_source_program_cd IN VARCHAR2,
1536 p_source_prog_ver IN NUMBER,
1537 p_term_cal_type IN VARCHAR2,
1538 p_term_seq_num IN NUMBER,
1539 p_acad_cal_type IN VARCHAR2,
1540 p_acad_seq_num IN NUMBER,
1541 p_trans_approval_dt IN DATE,
1542 p_trans_actual_dt IN DATE,
1543 p_dest_program_cd IN VARCHAR2,
1544 p_dest_prog_ver IN NUMBER,
1545 p_dest_coo_id IN NUMBER,
1546 p_uoo_ids_to_transfer IN VARCHAR2, -- concatenation of selected uoo_id,core_ind; coming from page
1547 p_uoo_ids_passed_transfer OUT NOCOPY VARCHAR2, -- units which were successfully transfered among the selected ones from the page
1548 p_uoo_ids_having_errors OUT NOCOPY VARCHAR2,
1549 p_uooids_str OUT NOCOPY VARCHAR2, -- all selected uoo_ids passed from the page in format uoo_id1,uoo_id2
1550 p_dest_fut_dt_trans_flag IN VARCHAR2,
1551 p_show_warning IN VARCHAR2,
1552 p_drop IN BOOLEAN,
1553 p_return_status IN OUT NOCOPY VARCHAR2
1554 ) AS
1555
1556 -------------------------------------------------------------------------------------------
1557 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
1558 -- Purpose : This procedure validates and transfers unit attempts in source program to the
1559 -- destination program.
1560 --Change History:
1561 --Who When What
1562 --ckasu 02-DEC-2004 modified as a part of Bug#4044329
1563 -- smaddali 16-dec-04 Modified for bug#4063726
1564 -- bdeviset 21-Mar-2006 After calling enrp_val_sua_cnfrm_p,while setting message
1565 -- used nvl(l_message_name1,l_message_name2) instead of l_message_name1
1566 -- as we need to consider l_message_name2 if l_message_name1 is null
1567 -- Bug# 5070403
1568 -------------------------------------------------------------------------------------------
1569
1570 l_strtpoint NUMBER;
1571 l_endpoint NUMBER;
1572 l_cindex NUMBER;
1573 l_pre_cindex NUMBER;
1574 l_nth_occurence NUMBER;
1575 l_uooid_coreind_sep_index NUMBER;
1576 l_uooid_and_coreind VARCHAR2(300);
1577 l_coreind VARCHAR2(30);
1578 l_uooid NUMBER;
1579 l_uoo_ids_to_transfer VARCHAR2(3000);
1580 l_enrp_sua_trans_status BOOLEAN;
1581 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1582 l_unit_outcome IGS_AS_GRD_SCH_GRADE.s_result_type%TYPE;
1583 l_is_val_sua_trans BOOLEAN;
1584 l_temp VARCHAR2(1);
1585 l_unt_att_status IGS_EN_SU_ATTEMPT.UNIT_ATTEMPT_STATUS%TYPE;
1586 l_proceed_aftr_res_val BOOLEAN;
1587 l_sut_status BOOLEAN;
1588 l_sua_trans_status BOOLEAN;
1589 l_return_type VARCHAR2(200);
1590 l_sua_confrm_status BOOLEAN;
1591 l_fail_type VARCHAR2(100);
1592 l_message_name1 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1593 l_message_name2 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1594 l_return_message FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1595 l_del_sua_tran_status BOOLEAN;
1596 l_del_all_sua_status BOOLEAN;
1597 l_teach_cal_desc IGS_CA_INST.description%TYPE;
1598 l_uooids_str VARCHAR2(1000);
1599 l_message_name3 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1600 l_error_occured BOOLEAN;
1601
1602
1603 CURSOR c_get_unit_dtls(c_uoo_id IGS_EN_SU_ATTEMPT.uoo_id%TYPE) Is
1604 SELECT *
1605 FROM IGS_EN_SU_ATTEMPT
1606 WHERE person_id = p_person_id AND
1607 course_cd = p_source_program_cd AND
1608 uoo_id = c_uoo_id ;
1609
1610 CURSOR c_get_research_dtls(c_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,c_unit_version_number IGS_EN_SU_ATTEMPT.version_number%TYPE) IS
1611 SELECT 'x'
1612 FROM IGS_PS_UNIT_VER uv
1613 WHERE uv.unit_cd = c_unit_cd AND
1614 uv.version_number = c_unit_version_number AND
1615 uv.research_unit_ind = 'Y';
1616
1617 CURSOR c_get_teach_cal_dec(c_teach_cal_type IGS_CA_INST.cal_type%TYPE,c_teach_cal_seq_num IGS_CA_INST.sequence_number%TYPE) IS
1618 SELECT description
1619 FROM igs_ca_inst
1620 WHERE cal_type = c_teach_cal_type AND
1621 sequence_number = c_teach_cal_seq_num ;
1622 l_unit_dtls_rec c_get_unit_dtls%ROWTYPE;
1623
1624 cst_enrolled CONSTANT VARCHAR2(10) := 'ENROLLED';
1625 cst_waitlist CONSTANT VARCHAR2(10) := 'WAITLISTED';
1626 cst_invalid CONSTANT VARCHAR2(10) := 'INVALID';
1627 l_src_crs_type igs_ps_ver.course_type%TYPE;
1628 BEGIN
1629
1630 l_strtpoint := 0;
1631 l_pre_cindex := 0;
1632 l_nth_occurence := 1;
1633 l_uoo_ids_to_transfer := p_uoo_ids_to_transfer ;
1634 l_cindex := INSTR(l_uoo_ids_to_transfer,';',1,l_nth_occurence);
1635 p_uoo_ids_having_errors := null;
1636 l_uooids_str := null;
1637
1638 WHILE (l_cindex <> 0 ) LOOP
1639
1640 SAVEPOINT SP_TRANSFER_SUA;
1641 l_error_occured := FALSE;
1642 l_strtpoint := l_pre_cindex + 1;
1643 l_endpoint := l_cindex - l_strtpoint;
1644 l_pre_cindex := l_cindex;
1645 l_uooid_and_coreind := substr(l_uoo_ids_to_transfer,l_strtpoint,l_endpoint);
1646 l_uooid_coreind_sep_index := INSTR(l_uooid_and_coreind,',',1);
1647 l_uooid := TO_NUMBER(SUBSTR(l_uooid_and_coreind,1,l_uooid_coreind_sep_index - 1));
1648 l_coreind := SUBSTR(l_uooid_and_coreind,l_uooid_coreind_sep_index + 1);
1649
1650 OPEN c_get_unit_dtls(l_uooid);
1651 FETCH c_get_unit_dtls INTO l_unit_dtls_rec ;
1652 CLOSE c_get_unit_dtls;
1653
1654
1655 IF l_uooids_str IS NULL THEN
1656 l_uooids_str := l_uooid;
1657 ELSE
1658 l_uooids_str := l_uooids_str||','||l_uooid;
1659 END IF;
1660
1661 l_is_val_sua_trans := TRUE;
1662 l_proceed_aftr_res_val := TRUE;
1663
1664
1665 OPEN c_get_teach_cal_dec(l_unit_dtls_rec.cal_type,l_unit_dtls_rec.ci_sequence_number);
1666 FETCH c_get_teach_cal_dec INTO l_teach_cal_desc;
1667 CLOSE c_get_teach_cal_dec;
1668
1669 -- If we are transfering from one academic calendar to different academic calendar then
1670 -- Units cannot be transfered if no academic calendar link exists.
1671 IF NOT is_unit_rel_dest_acad_cal(p_person_id,p_source_program_cd,p_dest_program_cd,l_uooid,l_return_message) THEN
1672 -- if transfer of unit failed then show warning and skip current unit attempt
1673 IF p_show_warning = 'Y' THEN
1674 IF NOT l_error_occured THEN
1675 l_error_occured := TRUE;
1676 IF p_uoo_ids_having_errors IS NULL THEN
1677 p_uoo_ids_having_errors := l_uooid;
1678 ELSE
1679 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1680 END IF;
1681 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1682 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1683 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1684 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1685 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1686 FND_MSG_PUB.ADD;
1687 END IF;-- end of NOT l_error_occured IF THEN
1688 FND_MESSAGE.SET_NAME( 'IGS' , l_return_message);
1689 FND_MSG_PUB.ADD;
1690 p_return_status := FND_API.G_RET_STS_ERROR;
1691 END IF;
1692 IF p_show_warning = 'N' THEN
1693 IF NOT l_error_occured THEN
1694 l_error_occured := TRUE;
1695 IF p_uoo_ids_having_errors IS NULL THEN
1696 p_uoo_ids_having_errors := l_uooid;
1697 ELSE
1698 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1699 END IF;
1700 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1701 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1702 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1703 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1704 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1705 FND_MSG_PUB.ADD;
1706 END IF;-- end of NOT l_error_occured IF THEN
1707 FND_MESSAGE.SET_NAME( 'IGS' , l_return_message);
1708 FND_MSG_PUB.ADD;
1709 RAISE FND_API.G_EXC_ERROR;
1710 END IF;
1711
1712 ELSE
1713
1714 l_enrp_sua_trans_status := IGS_EN_VAL_SCT.enrp_val_sua_trnsfr(p_person_id,
1715 p_source_program_cd,
1716 l_unit_dtls_rec.unit_cd ,
1717 l_unit_dtls_rec.cal_type,
1718 l_unit_dtls_rec.ci_sequence_number,
1719 l_unit_dtls_rec.unit_attempt_status ,
1720 l_message_name,
1721 l_uooid,
1722 l_unit_outcome );
1723 -- if above validation failed then log error message and skip this unit attempt
1724 IF NOT l_enrp_sua_trans_status THEN
1725 IF NOT l_error_occured THEN
1726 l_error_occured := TRUE;
1727 IF p_uoo_ids_having_errors IS NULL THEN
1728 p_uoo_ids_having_errors := l_uooid;
1729 ELSE
1730 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1731 END IF;
1732 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_UNIT_TRN');
1733 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1734 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1735 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1736 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1737 FND_MSG_PUB.ADD;
1738 END IF;-- end of NOT l_error_occured IF THEN
1739 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1740 FND_MSG_PUB.ADD;
1741
1742 -- if IGS_EN_VAL_SCT.enrp_val_sua_trnsfr validation passed then proceed
1743 ELSE
1744 -- for completed and discontinued unit attempts perform the 2 validations
1745 IF l_unit_dtls_rec.unit_attempt_status IN ('COMPLETED','DISCONTIN') THEN
1746 -- if unit attempt is failed then show a warning to the user and proceed
1747 IF l_unit_outcome = 'FAIL' AND p_show_warning = 'Y' THEN
1748 IF NOT l_error_occured THEN
1749 l_error_occured := TRUE;
1750 IF p_uoo_ids_having_errors IS NULL THEN
1751 p_uoo_ids_having_errors := l_uooid;
1752 ELSE
1753 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1754 END IF;
1755 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_UNIT_TRN');
1756 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1757 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1758 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1759 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1760 FND_MSG_PUB.ADD;
1761 END IF;-- end of NOT l_error_occured IF THEN
1762 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNATT_PROCEED_TRN_UN');
1763 FND_MSG_PUB.ADD;
1764 END IF;
1765
1766 END IF; -- end of ('COMPLETED','DISCONTIN')
1767
1768
1769 IF l_proceed_aftr_res_val THEN
1770
1771 --- continue Logic
1772 -- if enrolled unit attempt belongs to a past term then show warning that it will not be transfered
1773 -- but will be dropped from source.
1774 -- donot transfer enrolled/invalid/waitlisted unit attempts belonging to a past term
1775 -- compared to the effective term
1776 IF l_unit_dtls_rec.unit_attempt_status IN ( cst_enrolled,cst_invalid,cst_waitlist ) AND
1777 NOT igs_en_gen_010.unit_effect_or_future_term(p_person_id,p_dest_program_cd,l_unit_dtls_rec.uoo_id,p_term_cal_type,p_term_seq_num) THEN
1778 -- skip current unit attempt from transfer
1779 -- show warning when unit attempt is not transfered but will be dropped. i.e in intra career transfer
1780 IF p_show_warning = 'Y' THEN
1781 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_UNIT_TRN');
1782 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1783 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1784 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1785 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1786 FND_MSG_PUB.ADD;
1787 -- if intra career transfer, warn that unit will not be transfered and will be dropped from source
1788 IF is_career_model_enabled AND
1789 NOT is_tranfer_across_careers(p_source_program_cd,p_source_prog_ver,p_dest_program_cd,p_dest_prog_ver,l_src_crs_type) THEN
1790 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PAST_UNT_NOTTRN_DRP');
1791 FND_MSG_PUB.ADD;
1792 -- if program mode or inter career transfer then warn that unit will not be transfered
1793 ELSE
1794 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PAST_UNT_NOTTRN');
1795 FND_MSG_PUB.ADD;
1796 END IF;
1797 END IF;
1798 ELSE
1799 -- create unit transfer record with the same transfer date as the parent program transfer record
1800 -- smaddali modified the logic to create unit transfer records for bug#4063726
1801 l_sut_status := IGS_EN_GEN_010.ENRP_INS_SUT_TRNSFR(p_person_id,
1802 p_dest_program_cd,
1803 p_source_program_cd,
1804 p_trans_actual_dt,
1805 l_unit_dtls_rec.unit_cd ,
1806 l_unit_dtls_rec.cal_type,
1807 l_unit_dtls_rec.ci_sequence_number,
1808 l_message_name,
1809 l_uooid);
1810
1811 -- these uoo_id string will be used only in validate_prgm_attend_type_step procedure and not anywhere else
1812 -- this is to not validate program steps for units which were selected but not tranfered successfully
1813 IF p_uoo_ids_passed_transfer IS NULL THEN
1814 p_uoo_ids_passed_transfer := l_uooid;
1815 ELSE
1816 p_uoo_ids_passed_transfer := p_uoo_ids_passed_transfer ||','|| l_uooid ;
1817 END IF;
1818
1819 -- checking for unit exclusions before transferring.
1820 IF enrp_val_excld_unit_pt (
1821 p_person_id,
1822 p_dest_program_cd,
1823 l_unit_dtls_rec.unit_cd,
1824 SYSDATE,
1825 l_message_name) = FALSE THEN
1826 IF p_show_warning = 'Y' THEN
1827 IF NOT l_error_occured THEN
1828 l_error_occured := TRUE;
1829 IF p_uoo_ids_having_errors IS NULL THEN
1830 p_uoo_ids_having_errors := l_uooid;
1831 ELSE
1832 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1833 END IF;
1834 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_UNIT_TRN');
1835 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1836 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1837 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1838 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1839 FND_MSG_PUB.ADD;
1840 END IF;-- end of NOT l_error_occured IF THEN
1841 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1842 FND_MSG_PUB.ADD;
1843 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PERS_ENCUMB_SYS');
1844 FND_MSG_PUB.ADD;
1845
1846 END IF;
1847
1848 END IF;
1849
1850
1851 -- calling API to Perform actual unit of Transfer from src to dest Prgm
1852 l_sua_trans_status := igs_en_gen_010.enrp_Ins_Sua_Trnsfr(p_person_id,
1853 p_source_program_cd,
1854 p_dest_program_cd,
1855 p_dest_coo_id,
1856 l_unit_dtls_rec.unit_cd,
1857 l_unit_dtls_rec.version_number,
1858 l_unit_dtls_rec.cal_type,
1859 l_unit_dtls_rec.ci_sequence_number,
1860 l_return_type,
1861 l_message_name,
1862 l_uooid,
1863 l_coreind,
1864 p_term_cal_type,
1865 p_term_seq_num);
1866
1867 IF NOT l_sua_trans_status THEN
1868 -- if transfer of unit failed then show warning and skip current unit attempt
1869 IF p_show_warning = 'Y' THEN
1870 IF NOT l_error_occured THEN
1871 l_error_occured := TRUE;
1872 IF p_uoo_ids_having_errors IS NULL THEN
1873 p_uoo_ids_having_errors := l_uooid;
1874 ELSE
1875 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1876 END IF;
1877 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1878 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1879 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1880 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1881 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1882 FND_MSG_PUB.ADD;
1883 END IF;-- end of NOT l_error_occured IF THEN
1884 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1885 FND_MSG_PUB.ADD;
1886 p_return_status := FND_API.G_RET_STS_ERROR;
1887 END IF;
1888 IF p_show_warning = 'N' THEN
1889 IF NOT l_error_occured THEN
1890 l_error_occured := TRUE;
1891 IF p_uoo_ids_having_errors IS NULL THEN
1892 p_uoo_ids_having_errors := l_uooid;
1893 ELSE
1894 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1895 END IF;
1896 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1897 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1898 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1899 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1900 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1901 FND_MSG_PUB.ADD;
1902 END IF;-- end of NOT l_error_occured IF THEN
1903 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
1904 FND_MSG_PUB.ADD;
1905 RAISE FND_API.G_EXC_ERROR;
1906 END IF;
1907
1908 ELSE -- if l_sua_trans_status THEN
1909
1910
1911 l_sua_confrm_status := IGS_EN_VAL_SUA.enrp_val_sua_cnfrm_p (p_person_id,
1912 p_dest_program_cd,
1913 p_dest_prog_ver,
1914 p_dest_coo_id,
1915 p_acad_cal_type,
1916 p_acad_seq_num,
1917 l_uooid,
1918 l_fail_type,
1919 l_message_name1,
1920 l_message_name2);
1921
1922 IF NOT l_sua_confrm_status THEN
1923
1924 ROLLBACK TO SP_TRANSFER_SUA;
1925 IF p_show_warning = 'Y' THEN
1926 IF NOT l_error_occured THEN
1927 l_error_occured := TRUE;
1928 IF p_uoo_ids_having_errors IS NULL THEN
1929 p_uoo_ids_having_errors := l_uooid;
1930 ELSE
1931 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1932 END IF;
1933 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1934 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1935 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1936 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1937 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1938 FND_MSG_PUB.ADD;
1939 END IF;-- end of NOT l_error_occured IF THEN
1940 FND_MESSAGE.SET_NAME( 'IGS' ,nvl(l_message_name1,l_message_name2));
1941 FND_MSG_PUB.ADD;
1942 p_return_status := FND_API.G_RET_STS_ERROR;
1943 ELSIF p_show_warning = 'N' THEN
1944 IF NOT l_error_occured THEN
1945 l_error_occured := TRUE;
1946 IF p_uoo_ids_having_errors IS NULL THEN
1947 p_uoo_ids_having_errors := l_uooid;
1948 ELSE
1949 p_uoo_ids_having_errors := p_uoo_ids_having_errors ||','||l_uooid;
1950 END IF;
1951 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_UNIT_TRN');
1952 FND_MESSAGE.SET_TOKEN('UNIT_CD',l_unit_dtls_rec.unit_cd);
1953 FND_MESSAGE.SET_TOKEN('LOCATION_CD',l_unit_dtls_rec.location_cd);
1954 FND_MESSAGE.SET_TOKEN('SECTION',l_unit_dtls_rec.unit_class);
1955 FND_MESSAGE.SET_TOKEN('TEACH_CAL_DESC',l_teach_cal_desc);
1956 FND_MSG_PUB.ADD;
1957 END IF;-- end of NOT l_error_occured IF THEN
1958 FND_MESSAGE.SET_NAME( 'IGS' ,nvl(l_message_name1,l_message_name2));
1959 FND_MSG_PUB.ADD;
1960 RAISE FND_API.G_EXC_ERROR;
1961 END IF; -- end of p_show_warning = 'Y'
1962
1963 END IF; -- end of NOT l_sua_confrm_status IF THEN
1964
1965 END IF; --end of l_sua_trans_status IF THEN
1966 END IF; -- end of enrolled unit in past term
1967
1968 END IF; -- end of l_proceed_aftr_res_val IF THEN
1969
1970 END IF; -- end of l_is_val_sua_trans IF THEN
1971
1972 END IF ; -- end of NOT is_unit_rel_dest_acad_cal
1973 l_nth_occurence := l_nth_occurence + 1;
1974 l_cindex := INSTR(l_uoo_ids_to_transfer,';',1,l_nth_occurence);
1975
1976 END LOOP;-- end of WHILE LOOP
1977
1978 -- p_uoo_ids_string consists of concatenated uoo_ids seperated by comma
1979 p_uooids_str := l_uooids_str;
1980
1981 -- Listing all Enrolled/Waitlisted units that need to be droped that are present in Source but not
1982 -- in Destination
1983 getunits_in_src_notin_dest_prg(p_person_id,
1984 p_source_program_cd,
1985 p_dest_program_cd,
1986 l_uooids_str,
1987 p_drop,
1988 p_show_warning);
1989 val_unchk_sub_units(p_person_id,
1990 p_source_program_cd,
1991 l_uooids_str, -- add subordinate units also when superior is selected.
1992 p_drop,
1993 p_show_warning);
1994
1995 l_del_all_sua_status := IGS_EN_GEN_010.Enrp_del_all_Sua_Trnsfr(p_person_id,
1996 p_source_program_cd,
1997 p_dest_program_cd,
1998 l_uooids_str,
1999 p_term_cal_type,
2000 p_term_seq_num,
2001 p_drop,
2002 l_message_name3);
2003
2004 IF l_del_all_sua_status = FALSE THEN
2005 IF p_show_warning = 'Y' THEN
2006 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name3);
2007 FND_MSG_PUB.ADD;
2008 p_return_status := FND_API.G_RET_STS_ERROR;
2009 END IF;
2010 IF p_show_warning = 'N' THEN
2011 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name3);
2012 FND_MSG_PUB.ADD;
2013 RAISE FND_API.G_EXC_ERROR;
2014 END IF;
2015
2016 END IF; -- end of l_del_all_sua_status = FALSE
2017
2018
2019
2020 EXCEPTION
2021 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2022 RAISE;
2023 WHEN FND_API.G_EXC_ERROR THEN
2024 RAISE;
2025 WHEN OTHERS THEN
2026 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2027 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_src_prgm_unt_attempts');
2028 IGS_GE_MSG_STACK.ADD;
2029 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2030 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_src_prgm_unt_attempts :',SQLERRM);
2031 END IF;
2032 App_Exception.Raise_Exception;
2033
2034 END validate_src_prgm_unt_attempts;
2035
2036
2037 PROCEDURE validate_prgm_attend_type_step(
2038 p_person_id IN NUMBER,
2039 p_dest_program_cd IN VARCHAR2,
2040 p_uooids_str IN VARCHAR2,
2041 p_show_warning IN VARCHAR2,
2042 p_return_status IN OUT NOCOPY VARCHAR2
2043 ) AS
2044 -------------------------------------------------------------------------------------------
2045 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
2046 -- Purpose : This procedure validates attendance Type validation for the destination program.
2047 --Change History:
2048 --Who When What
2049 -- ckasu 06-MAR-2006 added new cursor c_get_enr_method_type as a part of bug#5070732
2050 -- ckasu created this procedure for bug#4063726
2051 -------------------------------------------------------------------------------------------
2052
2053 CURSOR c_get_unit_dtls(cp_uoo_id IGS_EN_SU_ATTEMPT.UOO_ID%TYPE) IS
2054 SELECT cal_type,ci_sequence_number
2055 FROM IGS_PS_UNIT_OFR_OPT
2056 WHERE uoo_id = cp_uoo_id;
2057
2058 CURSOR c_get_earliest_load_cal_dtls(cp_teach_cal_type IGS_CA_TEACH_TO_LOAD_V.TEACH_CAL_TYPE%TYPE,
2059 cp_teach_cal_seq_no IGS_CA_TEACH_TO_LOAD_V.TEACH_CI_SEQUENCE_NUMBER%TYPE) IS
2060
2061 SELECT load_cal_type,load_ci_sequence_number
2062 FROM IGS_CA_TEACH_TO_LOAD_V
2063 WHERE teach_cal_type = cp_teach_cal_type AND
2064 teach_ci_sequence_number = cp_teach_cal_seq_no
2065 ORDER BY LOAD_START_DT;
2066
2067 -- added by ckasu as a part of bug#5070732
2068 CURSOR c_get_enr_method_type IS
2069 SELECT enr_method_type
2070 FROM IGS_EN_METHOD_TYPE
2071 WHERE transfer_flag = 'Y'
2072 AND closed_ind ='N';
2073
2074
2075 l_strtpoint NUMBER;
2076 l_endpoint NUMBER;
2077 l_cindex NUMBER;
2078 l_pre_cindex NUMBER;
2079 l_nth_occurence NUMBER;
2080 l_uooid NUMBER;
2081 l_uooids_str VARCHAR2(1000);
2082 l_cal_type IGS_PS_UNIT_OFR_OPT.CAL_TYPE%TYPE;
2083 l_ci_sequence_number IGS_PS_UNIT_OFR_OPT.CI_SEQUENCE_NUMBER%TYPE;
2084 l_load_cal_type IGS_CA_TEACH_TO_LOAD_V.LOAD_CAL_TYPE%TYPE;
2085 l_load_ci_sequence_number IGS_CA_TEACH_TO_LOAD_V.LOAD_CI_SEQUENCE_NUMBER%TYPE;
2086
2087 l_enr_method igs_en_cat_prc_dtl.enr_method_type%TYPE;
2088 l_message_name1 VARCHAR2(4000);
2089 l_message_name2 VARCHAR2(4000);
2090 l_deny_warn VARCHAR2(10);
2091 l_return_status VARCHAR2(30);
2092
2093
2094
2095
2096 BEGIN
2097
2098 l_strtpoint := 0;
2099 l_pre_cindex := 0;
2100 l_nth_occurence := 1;
2101 l_uooids_str := p_uooids_str||',';
2102 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2103
2104 OPEN c_get_enr_method_type;
2105 FETCH c_get_enr_method_type INTO l_enr_method;
2106 CLOSE c_get_enr_method_type;
2107
2108
2109 WHILE (l_cindex <> 0 ) LOOP
2110
2111 l_strtpoint := l_pre_cindex + 1;
2112 l_endpoint := l_cindex - l_strtpoint;
2113 l_pre_cindex := l_cindex;
2114 l_uooid := TO_NUMBER(substr(l_uooids_str,l_strtpoint,l_endpoint));
2115 l_message_name2 := NULL;
2116 l_deny_warn := NULL;
2117 l_return_status := NULL;
2118 l_cal_type := NULL;
2119 l_ci_sequence_number := NULL;
2120 l_load_cal_type := NULL;
2121 l_load_ci_sequence_number := NULL;
2122
2123 OPEN c_get_unit_dtls(l_uooid);
2124 FETCH c_get_unit_dtls INTO l_cal_type,l_ci_sequence_number;
2125 CLOSE c_get_unit_dtls;
2126
2127 OPEN c_get_earliest_load_cal_dtls(l_cal_type,l_ci_sequence_number);
2128 FETCH c_get_earliest_load_cal_dtls INTO l_load_cal_type,l_load_ci_sequence_number;
2129 CLOSE c_get_earliest_load_cal_dtls;
2130 -- check for Max cp by passing the null value to credit points
2131 --so that the default calculation are done for the CP of this unit section
2132 igs_en_enroll_wlst.ss_eval_min_or_max_cp(p_person_id => p_person_id,
2133 p_load_cal_type => l_load_cal_type,
2134 p_load_ci_sequence_number => l_load_ci_sequence_number,
2135 p_uoo_id => l_uooid,
2136 p_program_cd => p_dest_program_cd,
2137 p_step_type => 'FMAX_CRDT',
2138 p_credit_points => NULL, -- deliberately passing null, this value will be internally calculated
2139 p_message_name => l_message_name2,
2140 p_deny_warn => l_deny_warn,
2141 p_return_status => l_return_status,
2142 p_enr_method => l_enr_method);
2143
2144
2145 IF l_return_status = 'FALSE' AND l_deny_warn = 'WARN' THEN
2146
2147 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2148 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2149 FND_MSG_PUB.ADD;
2150 END IF;
2151
2152 END IF; -- show warning message when rule failed in warn
2153
2154
2155 IF l_return_status = 'FALSE' AND l_deny_warn = 'DENY' THEN
2156
2157 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2158 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2159 FND_MSG_PUB.ADD;
2160 p_return_status := FND_API.G_RET_STS_ERROR;
2161 END IF;
2162
2163 IF l_message_name2 IS NOT NULL AND p_show_warning = 'N' THEN
2164 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2165 FND_MSG_PUB.ADD;
2166 p_return_status := FND_API.G_RET_STS_ERROR;
2167 RAISE FND_API.G_EXC_ERROR;
2168 END IF;
2169
2170 END IF; -- show warning/error message when rule failed in deny
2171
2172 l_nth_occurence := l_nth_occurence + 1;
2173 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2174
2175 END LOOP;-- end of WHILE LOOP for Max Cp Validation
2176
2177 -- Validation for Min CP
2178 l_strtpoint := 0;
2179 l_pre_cindex := 0;
2180 l_nth_occurence := 1;
2181 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2182
2183 WHILE (l_cindex <> 0 ) LOOP
2184
2185 l_strtpoint := l_pre_cindex + 1;
2186 l_endpoint := l_cindex - l_strtpoint;
2187 l_pre_cindex := l_cindex;
2188 l_uooid := TO_NUMBER(substr(l_uooids_str,l_strtpoint,l_endpoint));
2189 l_message_name2 := NULL;
2190 l_deny_warn := NULL;
2191 l_return_status := NULL;
2192 l_cal_type := NULL;
2193 l_ci_sequence_number := NULL;
2194 l_load_cal_type := NULL;
2195 l_load_ci_sequence_number := NULL;
2196
2197 OPEN c_get_unit_dtls(l_uooid);
2198 FETCH c_get_unit_dtls INTO l_cal_type,l_ci_sequence_number;
2199 CLOSE c_get_unit_dtls;
2200
2201 OPEN c_get_earliest_load_cal_dtls(l_cal_type,l_ci_sequence_number);
2202 FETCH c_get_earliest_load_cal_dtls INTO l_load_cal_type,l_load_ci_sequence_number;
2203 CLOSE c_get_earliest_load_cal_dtls;
2204 -- call the procedure to evaluate the Min CP by passing ZERO to the
2205 -- credit points parameter
2206 igs_en_enroll_wlst.ss_eval_min_or_max_cp(p_person_id => p_person_id,
2207 p_load_cal_type => l_load_cal_type,
2208 p_load_ci_sequence_number => l_load_ci_sequence_number,
2209 p_uoo_id => l_uooid,
2210 p_program_cd => p_dest_program_cd,
2211 p_step_type => 'FMIN_CRDT',
2212 p_credit_points => 0.0, -- deliberately passing null, this value will be internally calculated
2213 p_message_name => l_message_name2,
2214 p_deny_warn => l_deny_warn,
2215 p_return_status => l_return_status,
2216 p_enr_method => l_enr_method);
2217
2218
2219 IF l_return_status = 'FALSE' AND l_deny_warn = 'WARN' THEN
2220
2221 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2222 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2223 FND_MSG_PUB.ADD;
2224 END IF;
2225
2226 END IF; -- end of l_return_status = 'FALSE' AND l_deny_warn = 'WARN' IF THEN
2227
2228 IF l_return_status = 'FALSE' AND l_deny_warn = 'DENY' THEN
2229
2230 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2231 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2232 FND_MSG_PUB.ADD;
2233 p_return_status := FND_API.G_RET_STS_ERROR;
2234 END IF;
2235
2236 IF l_message_name2 IS NOT NULL AND p_show_warning = 'N' THEN
2237 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2238 FND_MSG_PUB.ADD;
2239 p_return_status := FND_API.G_RET_STS_ERROR;
2240 RAISE FND_API.G_EXC_ERROR;
2241 END IF;
2242
2243 END IF; -- end of l_return_status = 'FALSE' AND l_deny_warn = 'DENY' IF THEN
2244
2245
2246 l_nth_occurence := l_nth_occurence + 1;
2247 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2248
2249 END LOOP;-- end of WHILE LOOP for Min Cp Validation
2250
2251 -- Evaluating all person steps except Min cp and Max cp
2252 l_strtpoint := 0;
2253 l_pre_cindex := 0;
2254 l_nth_occurence := 1;
2255 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2256
2257 WHILE (l_cindex <> 0 ) LOOP
2258
2259 l_strtpoint := l_pre_cindex + 1;
2260 l_endpoint := l_cindex - l_strtpoint;
2261 l_pre_cindex := l_cindex;
2262 l_uooid := TO_NUMBER(substr(l_uooids_str,l_strtpoint,l_endpoint));
2263 l_message_name2 := NULL;
2264 l_deny_warn := NULL;
2265 l_return_status := NULL;
2266 l_cal_type := NULL;
2267 l_ci_sequence_number := NULL;
2268 l_load_cal_type := NULL;
2269 l_load_ci_sequence_number := NULL;
2270
2271
2272 OPEN c_get_unit_dtls(l_uooid);
2273 FETCH c_get_unit_dtls INTO l_cal_type,l_ci_sequence_number;
2274 CLOSE c_get_unit_dtls;
2275
2276 OPEN c_get_earliest_load_cal_dtls(l_cal_type,l_ci_sequence_number);
2277 FETCH c_get_earliest_load_cal_dtls INTO l_load_cal_type,l_load_ci_sequence_number;
2278 CLOSE c_get_earliest_load_cal_dtls;
2279 -- call the procedure to evaluate the Min CP by passing ZERO to the
2280 -- credit points parameter
2281 IF igs_en_enroll_wlst.validate_prog (
2282 p_person_id => p_person_id,
2283 p_cal_type => l_load_cal_type,
2284 p_ci_sequence_number => l_load_ci_sequence_number,
2285 p_uoo_id => l_uooid,
2286 p_course_cd => p_dest_program_cd,
2287 p_enr_method_type => l_enr_method,
2288 p_message_name => l_message_name2,
2289 p_deny_warn => l_deny_warn) THEN
2290 l_return_status := 'TRUE';
2291 ELSE
2292 l_return_status := 'FALSE';
2293 END IF;
2294
2295 IF l_return_status = 'FALSE' AND l_deny_warn = 'WARN' THEN
2296
2297 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2298 -- l_message_name2 contains appended message names, hence parse and add to fnd_message pub
2299 parse_messages( l_message_name2);
2300 END IF;
2301
2302 END IF; -- end of l_return_status = 'FALSE' AND l_deny_warn = 'WARN' IF THEN
2303
2304
2305
2306 IF l_return_status = 'FALSE' AND l_deny_warn = 'DENY' THEN
2307
2308 IF l_message_name2 IS NOT NULL AND p_show_warning = 'Y' THEN
2309 -- l_message_name2 contains appended message names, hence parse and add to fnd_message pub
2310 parse_messages( l_message_name2);
2311 p_return_status := FND_API.G_RET_STS_ERROR;
2312 END IF;
2313
2314 IF l_message_name2 IS NOT NULL AND p_show_warning = 'N' THEN
2315 -- l_message_name2 contains appended message names, hence parse and add to fnd_message pub
2316 parse_messages( l_message_name2);
2317 p_return_status := FND_API.G_RET_STS_ERROR;
2318 RAISE FND_API.G_EXC_ERROR;
2319 END IF;
2320
2321 END IF; -- end of l_return_status = 'FALSE' AND l_deny_warn = 'DENY' IF THEN
2322
2323 l_nth_occurence := l_nth_occurence + 1;
2324 l_cindex := INSTR(l_uooids_str,',',1,l_nth_occurence);
2325
2326 END LOOP;-- end of WHILE LOOP for Min Cp Validation
2327
2328
2329
2330 EXCEPTION
2331 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2332 RAISE;
2333 WHEN FND_API.G_EXC_ERROR THEN
2334 RAISE;
2335 WHEN OTHERS THEN
2336 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2337 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_prgm_attend_type_step');
2338 IGS_GE_MSG_STACK.ADD;
2339 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2340 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_prgm_attend_type_step :',SQLERRM);
2341 END IF;
2342 App_Exception.Raise_Exception;
2343 END validate_prgm_attend_type_step;
2344
2345 PROCEDURE validate_src_prgm_unt_set_att(
2346 p_person_id IN NUMBER,
2347 p_source_program_cd IN VARCHAR2,
2348 p_source_prog_ver IN NUMBER,
2349 p_term_cal_type IN VARCHAR2,
2350 p_term_seq_num IN NUMBER,
2351 p_acad_cal_type IN VARCHAR2,
2352 p_acad_seq_num IN NUMBER,
2353 p_trans_approval_dt IN DATE,
2354 p_dest_program_cd IN VARCHAR2,
2355 p_dest_prog_ver IN NUMBER,
2356 p_dest_coo_id IN NUMBER,
2357 p_unit_sets_to_transfer IN VARCHAR2,
2358 p_unit_sets_not_selected IN VARCHAR2,
2359 p_unit_sets_having_errors OUT NOCOPY VARCHAR2,
2360 p_show_warning IN VARCHAR2,
2361 p_return_status IN OUT NOCOPY VARCHAR2
2362 ) AS
2363 -------------------------------------------------------------------------------------------
2364 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
2365 -- Purpose : This procedure validates and transfers the unitsets againts the source program
2366 -- to the destination program.
2367 --Change History:
2368 --Who When What
2369
2370 -------------------------------------------------------------------------------------------
2371
2372 l_strtpoint NUMBER;
2373 l_endpoint NUMBER;
2374 l_cindex NUMBER;
2375 l_pre_cindex NUMBER;
2376 l_nth_occurence NUMBER;
2377 l_unitset_seqno_sep_index NUMBER;
2378 l_seqno_prmind_sep_index NUMBER;
2379 l_unitset_seqno_and_prmind VARCHAR2(300);
2380 l_unitset VARCHAR2(50);
2381 l_seqno NUMBER;
2382 l_prmind VARCHAR2(3);
2383 l_unitsets_to_transfer VARCHAR2(3000);
2384 l_unitset_append_count NUMBER;
2385
2386 l_unitset_att_exists BOOLEAN;
2387 l_val_susa_status BOOLEAN;
2388 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2389 l_message_name1 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2390 l_message_name2 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2391 l_message_name3 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2392 l_message_name4 FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2393 l_message_text VARCHAR2(1000);
2394 l_val_susa_tran BOOLEAN;
2395 l_susa_cousr_status BOOLEAN;
2396 l_status BOOLEAN;
2397 l_status1 BOOLEAN;
2398 l_status2 BOOLEAN;
2399 l_error_occured BOOLEAN;
2400 CURSOR c_get_dest_unitset_att (c_person_id IN IGS_AS_SU_SETATMPT.person_id%TYPE,
2401 c_course_cd IN IGS_AS_SU_SETATMPT.course_cd%TYPE,
2402 c_unit_set_cd IN IGS_AS_SU_SETATMPT.unit_set_cd%TYPE) IS
2403 SELECT *
2404 FROM IGS_AS_SU_SETATMPT
2405 WHERE person_id = c_person_id AND
2406 course_cd = c_course_cd AND
2407 unit_set_cd = c_unit_set_cd;
2408 CURSOR c_get_unitset_att ( c_person_id IN IGS_AS_SU_SETATMPT.person_id%TYPE,
2409 c_course_cd IN IGS_AS_SU_SETATMPT.course_cd%TYPE,
2410 c_unit_set_cd IN IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
2411 c_sequence_number IN IGS_AS_SU_SETATMPT.sequence_number%TYPE) IS
2412 SELECT *
2413 FROM IGS_AS_SU_SETATMPT
2414 WHERE person_id = c_person_id AND
2415 course_cd = c_course_cd AND
2416 unit_set_cd = c_unit_set_cd AND
2417 sequence_number = c_sequence_number ;
2418 l_dest_unitset_att_rec c_get_dest_unitset_att%ROWTYPE;
2419 l_unitset_att_rec c_get_unitset_att%ROWTYPE;
2420
2421 BEGIN
2422
2423 l_strtpoint := 0;
2424 l_pre_cindex := 0;
2425 l_nth_occurence := 1;
2426 l_unitset_append_count := 1;
2427 l_unitsets_to_transfer := p_unit_sets_to_transfer ;
2428 l_cindex := INSTR(l_unitsets_to_transfer,';',1,l_nth_occurence);
2429 p_unit_sets_having_errors := null;
2430
2431 WHILE (l_cindex <> 0 ) LOOP
2432 l_error_occured := FALSE;
2433 l_strtpoint := l_pre_cindex + 1;
2434 l_endpoint := l_cindex - l_strtpoint;
2435 l_pre_cindex := l_cindex;
2436 l_unitset_seqno_and_prmind := substr(l_unitsets_to_transfer,l_strtpoint,l_endpoint);
2437 l_unitset_seqno_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1);
2438 l_seqno_prmind_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1,2);
2439 l_unitset := SUBSTR(l_unitset_seqno_and_prmind,1,l_unitset_seqno_sep_index - 1);
2440 l_seqno := TO_NUMBER(SUBSTR(l_unitset_seqno_and_prmind,l_unitset_seqno_sep_index+1,l_seqno_prmind_sep_index -(l_unitset_seqno_sep_index+1)));
2441 l_prmind := SUBSTR(l_unitset_seqno_and_prmind,l_seqno_prmind_sep_index + 1);
2442 l_unitset_att_exists := FALSE;
2443 l_val_susa_tran := TRUE;
2444
2445 OPEN c_get_dest_unitset_att(p_person_id,p_dest_program_cd,l_unitset);
2446 FETCH c_get_dest_unitset_att INTO l_dest_unitset_att_rec;
2447 IF (c_get_dest_unitset_att%FOUND) THEN
2448 CLOSE c_get_dest_unitset_att;
2449 l_unitset_att_exists := TRUE;
2450 ELSE
2451 CLOSE c_get_dest_unitset_att;
2452 END IF;
2453
2454 IF NOT l_unitset_att_exists THEN
2455 OPEN c_get_unitset_att(p_person_id,p_source_program_cd,l_unitset,l_seqno);
2456 FETCH c_get_unitset_att INTO l_unitset_att_rec;
2457 CLOSE c_get_unitset_att;
2458 l_val_susa_status := IGS_EN_VAL_SUSA.ENRP_VAL_SUSA( p_person_id,
2459 p_dest_program_cd ,
2460 l_unitset,
2461 l_unitset_att_rec.sequence_number ,
2462 l_unitset_att_rec.us_version_number ,
2463 l_unitset_att_rec.selection_dt ,
2464 l_unitset_att_rec.student_confirmed_ind ,
2465 l_unitset_att_rec.end_dt ,
2466 l_unitset_att_rec.parent_unit_set_cd ,
2467 l_unitset_att_rec.parent_sequence_number ,
2468 l_unitset_att_rec.primary_set_ind ,
2469 l_unitset_att_rec.voluntary_end_ind,
2470 l_unitset_att_rec.authorised_person_id ,
2471 l_unitset_att_rec.authorised_on,
2472 l_unitset_att_rec.override_title,
2473 l_unitset_att_rec.rqrmnts_complete_ind,
2474 l_unitset_att_rec.rqrmnts_complete_dt,
2475 l_unitset_att_rec.s_completed_source_type,
2476 'INSERT',
2477 l_message_name,
2478 l_message_text);
2479
2480 IF ( l_val_susa_status = FALSE AND l_message_name IS NOT NULL ) THEN
2481
2482 IF l_message_name IN ('IGS_EN_UNIT_SET_PARENT_UNITSE',
2483 'IGS_EN_UNIT_SET_RELATIONSHIP','IGS_EN_UNITSET_HAVE_ONE_PAREN',
2484 'IGS_EN_UNIT_SET_NOTBE_PARENT','IGS_EN_UNIT_SET_NOT_ENDDT',
2485 'IGS_EN_INVALID_RELATIONSHIP','IGS_EN_UNIT_SET_PARENTSET_CON') THEN
2486 null;
2487 ELSIF (p_show_warning = 'N' ) THEN
2488 IF l_unitset_append_count = 1 THEN
2489 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2490 ELSE
2491 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2492 END IF;
2493 l_unitset_append_count := l_unitset_append_count + 1;
2494 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_US_TRN');
2495 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2496 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2497 FND_MSG_PUB.ADD;
2498 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
2499 FND_MSG_PUB.ADD;
2500 RAISE FND_API.G_EXC_ERROR;
2501 ELSIF (p_show_warning = 'Y' ) THEN
2502 -- if error occured for the first time for this unit set then log the heading and
2503 -- increment the count and append to out param. Ignore this logic when further errors occur
2504 IF NOT l_error_occured THEN
2505 l_error_occured := TRUE;
2506 IF l_unitset_append_count = 1 THEN
2507 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2508 ELSE
2509 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2510 END IF;
2511 l_unitset_append_count := l_unitset_append_count + 1;
2512 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_US_TRN');
2513 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2514 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2515 FND_MSG_PUB.ADD;
2516 END IF;
2517 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
2518 FND_MSG_PUB.ADD;
2519 p_return_status := FND_API.G_RET_STS_ERROR;
2520 l_val_susa_tran := FALSE;
2521
2522 END IF;-- end of l_message_name IN IF THEN
2523
2524 END IF;--end of l_val_susa_status = FALSE
2525
2526 IF l_val_susa_tran THEN
2527 IF IGS_EN_GEN_010.ENRP_INS_SUSA_TRNSFR (p_person_id,
2528 p_source_program_cd,
2529 p_dest_program_cd,
2530 l_unitset,
2531 l_unitset_att_rec.us_version_number,
2532 l_unitset_att_rec.sequence_number,
2533 l_unitset_att_rec.primary_set_ind ,
2534 l_message_name) = FALSE THEN
2535 IF l_message_name IN ('IGS_EN_UNITSET_REQ_AUTHORISAT',
2536 'IGS_EN_UNITSET_HAVE_ONE_PAREN') THEN
2537 null;
2538 ELSIF l_message_name IN ('IGS_EN_SUA_SET_ATT_TRNS_EXIST','IGS_GE_INVALID_VALUE','IGS_EN_PRIMARY_INDICATOR_NOT',
2539 'IGS_EN_PRIMARY_IND_NOT_SET','IGS_EN_UNIT_SET_SPA_ENR_INACT','IGS_EN_UNIT_SET_UNCONF_REQ',
2540 'IGS_EN_UNIT_SET_UNCONF_ENDDT','IGS_EN_UNIT_SET_PARENTSET_CON','IGS_EN_NOTDEL_UNITSET_COMPL',
2541 'IGS_EN_UNITSET_REQ_ENDED','IGS_EN_UNIT_SET_NO_OPEN','IGS_EN_UNIT_SET_EXISTS',
2542 'IGS_EN_INVALID_RELATIONSHIP', 'IGS_EN_UNIT_SET_NOT_ENDDT','IGS_EN_UNIT_SET_NOT_PARENT_EX',
2543 'IGS_EN_UNIT_SET_NOTBE_PARENT', 'IGS_EN_UNIT_SET_PARENT_UNITSE','IGS_EN_UNIT_SET_RELATIONSHIP',
2544 'IGS_EN_UNIT_SETST_ACTIVE', 'IGS_EN_UNIT_SET_EXPDT_NOTSET','IGS_EN_UNIT_SET_REQ_AUTHORISA',
2545 'IGS_EN_NOTDEL_UNITSET_COND', 'IGS_EN_NOTDEL_UNITSET_PARENT','IGS_EN_NOTDEL_UNITSET_ENDED',
2546 'IGS_EN_VOLUNTARY_END_INDICATO','IGS_EN_UNIT_SET_UNCONF_SETDT','IGS_EN_UNIT_SET_UNCONF_NOTSET',
2547 'IGS_EN_ENDDT_NOTBE_EARLIER_DT','IGS_EN_ENDDT_COMPLDT_NOTSET','IGS_EN_SELDT_LE_CURR_DT',
2548 'IGS_EN_ENDDT_LE_CURR_DT','IGS_EN_COMPLDT_LE_CURR_DT','IGS_EN_COMPLDT_GE_CURR_DT',
2549 'IGS_EN_SYS_COMPL_SRCTYPE_SET','IGS_EN_COMPL_DT_SET_COMPL_IND','IGS_EN_COMPLDT_NOTBE_SET_COMP',
2550 'IGS_EN_SU_SET_MUSTBE_CONFIRME','IGS_EN_AUTHORISED_PRSN_NOT','IGS_EN_AUTHDT_AUTHPRSN_SET',
2551 'IGS_EN_AUTHDT_NOTBE_AUTHPRSN','IGS_EN_AUTHDT_MUSTBE_SET','IGS_EN_UNIT_SETNOT_PERMITTED',
2552 'IGS_EN_STUD_COMPL_UNITSET','IGS_EN_SUA_NOT_CREATED','IGS_EN_PERS_EXL_ENRL_UNT_SET'
2553 ,'IGS_EN_PRSN_ENCUMB_REVOKING','IGS_EN_PERS_HAS_ENCUMB') THEN
2554 IF (p_show_warning = 'N' ) THEN
2555 IF l_unitset_append_count = 1 THEN
2556 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2557 ELSE
2558 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2559 END IF;
2560 l_unitset_append_count := l_unitset_append_count + 1;
2561 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_US_TRN');
2562 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2563 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2564 FND_MSG_PUB.ADD;
2565 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
2566 FND_MSG_PUB.ADD;
2567 RAISE FND_API.G_EXC_ERROR;
2568 ELSIF (p_show_warning = 'Y' ) THEN
2569 -- if error occured for the first time for this unit set then log the heading and
2570 -- increment the count and append to out param. Ignore this logic when further errors occur
2571 IF NOT l_error_occured THEN
2572 l_error_occured := TRUE;
2573 IF l_unitset_append_count = 1 THEN
2574 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2575 ELSE
2576 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2577 END IF;
2578 l_unitset_append_count := l_unitset_append_count + 1;
2579 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_US_TRN');
2580 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2581 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2582 FND_MSG_PUB.ADD;
2583 END IF;
2584 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
2585 FND_MSG_PUB.ADD;
2586 p_return_status := FND_API.G_RET_STS_ERROR;
2587 END IF;-- end of p_show_warning = 'N' IF THEN
2588
2589 END IF;-- END OF l_message_name
2590
2591
2592 END IF; -- END OF IGS_EN_GEN_010.ENRP_INS_SUSA_TRNSFR IF THEN
2593
2594 OPEN c_get_dest_unitset_att(p_person_id,p_dest_program_cd,l_unitset);
2595 FETCH c_get_dest_unitset_att INTO l_dest_unitset_att_rec;
2596 CLOSE c_get_dest_unitset_att;
2597 IF (l_dest_unitset_att_rec.primary_set_ind = 'Y') THEN
2598 l_susa_cousr_status := IGS_EN_VAL_SUSA.enrp_val_susa_cousr(p_person_id,
2599 p_dest_program_cd,
2600 l_unitset,
2601 l_dest_unitset_att_rec.us_version_number,
2602 l_dest_unitset_att_rec.parent_unit_set_cd,
2603 l_dest_unitset_att_rec.parent_sequence_number,
2604 'E',
2605 l_message_name1,
2606 'Y');
2607 IF (l_susa_cousr_status = FALSE ) THEN
2608 l_status := IGS_EN_GEN_001.ENRP_DEL_SUSA_TRNSFR(p_person_id,
2609 p_dest_program_cd,
2610 l_unitset,
2611 l_dest_unitset_att_rec.us_version_number,
2612 l_message_name2);
2613 IF (p_show_warning = 'N' ) THEN
2614 IF l_unitset_append_count = 1 THEN
2615 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2616 ELSE
2617 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2618 END IF;
2619 l_unitset_append_count := l_unitset_append_count + 1;
2620 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_US_TRN');
2621 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2622 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2623 FND_MSG_PUB.ADD;
2624 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name1);
2625 FND_MSG_PUB.ADD;
2626 IF l_message_name2 IS NOT NULL THEN
2627 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2628 FND_MSG_PUB.ADD;
2629 END IF;
2630 RAISE FND_API.G_EXC_ERROR;
2631 ELSIF (p_show_warning = 'Y' ) THEN
2632 -- if error occured for the first time for this unit set then log the heading and
2633 -- increment the count and append to out param. Ignore this logic when further errors occur
2634 IF NOT l_error_occured THEN
2635 l_error_occured := TRUE;
2636 IF l_unitset_append_count = 1 THEN
2637 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2638 ELSE
2639 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2640 END IF;
2641 l_unitset_append_count := l_unitset_append_count + 1;
2642 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_US_TRN');
2643 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2644 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2645 FND_MSG_PUB.ADD;
2646 END IF;
2647 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name1);
2648 FND_MSG_PUB.ADD;
2649 IF l_message_name2 IS NOT NULL THEN
2650 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name2);
2651 FND_MSG_PUB.ADD;
2652 END IF;
2653 p_return_status := FND_API.G_RET_STS_ERROR;
2654 END IF;-- end of p_show_warning = 'N' IF THEN
2655
2656 ELSIF (l_susa_cousr_status = TRUE ) THEN
2657 l_status := IGS_EN_VAL_SUSA.enrp_val_susa_parent(p_person_id,
2658 p_dest_program_cd,
2659 l_unitset,
2660 l_dest_unitset_att_rec.sequence_number,
2661 l_dest_unitset_att_rec.parent_unit_set_cd,
2662 l_dest_unitset_att_rec.parent_sequence_number,
2663 l_dest_unitset_att_rec.student_confirmed_ind,
2664 l_message_name3,
2665 'N');
2666 IF (l_status = FALSE) THEN
2667 l_status1 := IGS_EN_GEN_001.ENRP_DEL_SUSA_TRNSFR(p_person_id,
2668 p_dest_program_cd,
2669 l_unitset,
2670 l_dest_unitset_att_rec.us_version_number,
2671 l_message_name4);
2672
2673 IF (p_show_warning = 'N' ) THEN
2674 IF l_unitset_append_count = 1 THEN
2675 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2676 ELSE
2677 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2678 END IF;
2679 l_unitset_append_count := l_unitset_append_count + 1;
2680 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ERR_US_TRN');
2681 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2682 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2683 FND_MSG_PUB.ADD;
2684 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name3);
2685 FND_MSG_PUB.ADD;
2686 IF l_message_name2 IS NOT NULL THEN
2687 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name4);
2688 FND_MSG_PUB.ADD;
2689 END IF;
2690 RAISE FND_API.G_EXC_ERROR;
2691 ELSIF (p_show_warning = 'Y' ) THEN
2692 -- if error occured for the first time for this unit set then log the heading and
2693 -- increment the count and append to out param. Ignore this logic when further errors occur
2694 IF NOT l_error_occured THEN
2695 l_error_occured := TRUE;
2696
2697 IF l_unitset_append_count = 1 THEN
2698 p_unit_sets_having_errors := l_unitset_seqno_and_prmind || ';' ;
2699 ELSE
2700 p_unit_sets_having_errors := p_unit_sets_having_errors || l_unitset_seqno_and_prmind || ';' ;
2701 END IF;
2702 l_unitset_append_count := l_unitset_append_count + 1;
2703 FND_MESSAGE.SET_NAME('IGS','IGS_EN_WARN_US_TRN');
2704 FND_MESSAGE.SET_TOKEN('UNIT_SET_CD',l_unitset);
2705 FND_MESSAGE.SET_TOKEN('US_VERSION_NUMBER',l_unitset_att_rec.us_version_number);
2706 FND_MSG_PUB.ADD;
2707 END IF;
2708 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name3);
2709 FND_MSG_PUB.ADD;
2710 IF l_message_name2 IS NOT NULL THEN
2711 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name4);
2712 FND_MSG_PUB.ADD;
2713 END IF;
2714 p_return_status := FND_API.G_RET_STS_ERROR;
2715 END IF;-- end of p_show_warning = 'Y' IF THEN
2716
2717 END IF;-- end of l_status = FALSE
2718
2719 END IF;-- end of l_susa_cousr_status = FALSE IF THEN
2720
2721 END IF;--end of l_dest_unitset_att_rec.primary_set_ind = 'Y' IF THEN
2722
2723 END IF;--end of l_val_susa_tran IF THEN
2724
2725 END IF;-- end of l_unitset_att_exists IF THEN
2726
2727 l_nth_occurence := l_nth_occurence + 1;
2728 l_cindex := INSTR(l_unitsets_to_transfer,';',1,l_nth_occurence);
2729
2730 END LOOP;-- end of WHILE LOOP
2731
2732 EXCEPTION
2733 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2734 RAISE;
2735 WHEN FND_API.G_EXC_ERROR THEN
2736 RAISE;
2737 WHEN OTHERS THEN
2738 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2739 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.validate_src_prgm_unt_set_att');
2740 IGS_GE_MSG_STACK.ADD;
2741 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2742 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.validate_src_prgm_unt_set_att :',SQLERRM);
2743 END IF;
2744 App_Exception.Raise_Exception;
2745
2746 END validate_src_prgm_unt_set_att;
2747
2748 PROCEDURE update_source_prgm (
2749 p_person_id IN NUMBER,
2750 p_source_program_cd IN VARCHAR2,
2751 p_source_prog_ver IN NUMBER,
2752 p_dest_program_cd IN VARCHAR2,
2753 p_trans_approval_dt IN DATE,
2754 p_trans_actual_dt IN DATE,
2755 p_dest_fut_dt_trans_flag IN VARCHAR2,
2756 p_discontinue_source IN VARCHAR2,
2757 p_tran_across_careers IN BOOLEAN,
2758 p_src_career_type IN VARCHAR2
2759 ) AS
2760
2761 -------------------------------------------------------------------------------------------
2762 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
2763 -- Purpose : This procedure updated the source program when it passes all validations
2764 --Change History:
2765 --Who When What
2766 --stutta 10-DEC-2004 Unsetting skip_before_after_dml( allowing recurrsion) if discontinue_source
2767 -- is Y for a immediate transfer. This is the only case when skip_before_after
2768 -- _dml is unset during the entire process of program transfer. Calculate
2769 -- program attempt status depending on whether source is becoming PRIMARY/SECONDARY.
2770 -- Pass the program attempt status to update row call. Bug#4046782
2771 --somasekar 13-apr-2005 bug# 4179106 modified to check the transfer status with 'S'
2772 -- instead of 'N'
2773 -------------------------------------------------------------------------------------------
2774
2775
2776 l_course_attempt_status IGS_EN_STDNT_PS_ATT.course_attempt_status%TYPE;
2777 l_commencement_dt IGS_EN_STDNT_PS_ATT.commencement_dt%TYPE;
2778 l_discont_reason_code IGS_EN_DCNT_REASONCD.discontinuation_reason_cd%TYPE;
2779 l_status BOOLEAN;
2780 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
2781 CURSOR c_course_type IS
2782 SELECT course_type
2783 FROM IGS_PS_VER
2784 WHERE COURSE_CD = p_source_program_cd
2785 AND VERSION_NUMBER = p_source_prog_ver;
2786 CURSOR c_get_stdnt_ps_att_dtls IS
2787 SELECT *
2788 FROM IGS_EN_STDNT_PS_ATT
2789 WHERE person_id = p_person_id AND
2790 course_cd = p_source_program_cd;
2791 l_stdnt_ps_attempt_dtls_rec c_get_stdnt_ps_att_dtls%ROWTYPE;
2792
2793 CURSOR c_get_discont_reason IS
2794 SELECT discontinuation_reason_cd
2795 FROM IGS_EN_DCNT_REASONCD
2796 WHERE dcnt_program_ind = 'Y' AND
2797 closed_ind = 'N' AND
2798 sys_dflt_ind = 'Y' AND
2799 s_discontinuation_reason_type = 'TRANSFER';
2800 -- check if any other active program exists in the source career other than the source program
2801 CURSOR c_act_src_prg_exists_as_prmy IS
2802 SELECT 'x'
2803 FROM IGS_EN_STDNT_PS_ATT sca,
2804 IGS_PS_VER pv
2805 WHERE sca.person_id = p_person_id AND
2806 sca.course_cd = pv.course_cd AND
2807 sca.version_number = pv.version_number AND
2808 pv.course_type = p_src_career_type AND
2809 sca.course_cd <> p_source_program_cd AND
2810 sca.course_attempt_status IN ('ENROLLED','INACTIVE','LAPSED','INTERMIT');
2811 l_act_src_exist_across_career c_act_src_prg_exists_as_prmy%ROWTYPE;
2812 l_course_type igs_ps_ver.course_type%TYPE;
2813 BEGIN
2814
2815 OPEN c_get_stdnt_ps_att_dtls;
2816 FETCH c_get_stdnt_ps_att_dtls INTO l_stdnt_ps_attempt_dtls_rec;
2817 CLOSE c_get_stdnt_ps_att_dtls;
2818 IF p_discontinue_source = 'Y' AND p_dest_fut_dt_trans_flag = 'S' THEN
2819 -- discontinue source and any enrolled unit attempts left in it
2820 -- this procedure will automatically make the source secondary and make some other program in that career primary
2821 OPEN c_get_discont_reason;
2822 FETCH c_get_discont_reason INTO l_discont_reason_code;
2823 CLOSE c_get_discont_reason;
2824 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
2825 -- discontinue the source program attempt on the same date as the program ransfer record was created
2826 l_status := IGS_EN_GEN_012.ENRP_UPD_SCA_DISCONT (
2827 p_person_id,
2828 p_source_program_cd,
2829 p_source_prog_ver,
2830 l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
2831 l_stdnt_ps_attempt_dtls_rec.commencement_dt,
2832 p_trans_actual_dt,
2833 l_discont_reason_code,
2834 l_message_name,
2835 'PROGRAM_TRANSFER' ,
2836 p_dest_program_cd
2837 );
2838
2839 IF l_status = FALSE THEN
2840 FND_MESSAGE.SET_NAME('IGS',l_message_name);
2841 FND_MSG_PUB.ADD;
2842 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_UNABLE_DISCONT_FROM');
2843 FND_MSG_PUB.ADD;
2844 RAISE FND_API.G_EXC_ERROR;
2845 END IF;
2846 ELSIF p_discontinue_source = 'N' AND p_dest_fut_dt_trans_flag = 'S' THEN
2847 -- if source is key then make it non key because destination is already being made key in this case
2848 IF l_stdnt_ps_attempt_dtls_rec.key_program = 'Y' THEN
2849 l_stdnt_ps_attempt_dtls_rec.key_program := 'N';
2850 END IF;
2851
2852
2853 -- set the primary program type of the source as appropriate
2854 IF NOT p_tran_across_careers AND is_career_model_enabled THEN
2855 -- for transfers within career immediate transfers always make source secondary even when source is not discontinued
2856 l_stdnt_ps_attempt_dtls_rec.primary_program_type := 'SECONDARY';
2857 END IF;
2858
2859 -- DERIVE THE STATUS OF THE SOURCE PROGRAM ATTEMPT
2860 IF l_stdnt_ps_attempt_dtls_rec.primary_program_type = 'PRIMARY' OR NOT is_career_model_enabled THEN
2861 -- the source program is a primary or this is program mode, so derive proper status
2862 l_stdnt_ps_attempt_dtls_rec.course_attempt_status :=
2863 igs_en_gen_006.Enrp_Get_Sca_Status(
2864 p_person_id => l_stdnt_ps_attempt_dtls_rec.person_id,
2865 p_course_cd => l_stdnt_ps_attempt_dtls_rec.course_cd,
2866 p_course_attempt_status => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
2867 p_student_confirmed_ind => l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind,
2868 p_discontinued_dt => NULL,
2869 p_lapsed_dt => l_stdnt_ps_attempt_dtls_rec.lapsed_dt,
2870 p_course_rqrmnt_complete_ind => l_stdnt_ps_attempt_dtls_rec.course_rqrmnt_complete_ind,
2871 p_logical_delete_dt => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt );
2872 ELSE
2873 OPEN c_course_type;
2874 FETCH c_course_type INTO l_course_type;
2875 CLOSE c_course_type;
2876 -- the source program is a secondary, so derive proper status
2877 l_stdnt_ps_attempt_dtls_rec.course_attempt_status :=
2878 igs_en_career_model.enrp_get_sec_sca_status( l_stdnt_ps_attempt_dtls_rec.person_id ,
2879 l_stdnt_ps_attempt_dtls_rec.course_cd ,
2880 l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
2881 l_stdnt_ps_attempt_dtls_rec.primary_program_type ,
2882 l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
2883 l_course_type ,
2884 p_dest_program_cd);
2885 END IF;
2886
2887 IGS_EN_STDNT_PS_ATT_PKG.UPDATE_ROW(
2888 X_ROWID => l_stdnt_ps_attempt_dtls_rec.row_id,
2889 X_PERSON_ID => l_stdnt_ps_attempt_dtls_rec.PERSON_ID,
2890 X_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.COURSE_CD,
2891 X_ADVANCED_STANDING_IND => l_stdnt_ps_attempt_dtls_rec.ADVANCED_STANDING_IND,
2892 X_FEE_CAT => l_stdnt_ps_attempt_dtls_rec.fee_cat,
2893 X_CORRESPONDENCE_CAT => l_stdnt_ps_attempt_dtls_rec.correspondence_cat,
2894 X_SELF_HELP_GROUP_IND => l_stdnt_ps_attempt_dtls_rec.SELF_HELP_GROUP_IND,
2895 X_LOGICAL_DELETE_DT => l_stdnt_ps_attempt_dtls_rec.logical_delete_dt,
2896 X_ADM_ADMISSION_APPL_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_admission_appl_number,
2897 X_ADM_NOMINATED_COURSE_CD => l_stdnt_ps_attempt_dtls_rec.adm_nominated_course_cd,
2898 X_ADM_SEQUENCE_NUMBER => l_stdnt_ps_attempt_dtls_rec.adm_sequence_number,
2899 X_VERSION_NUMBER => l_stdnt_ps_attempt_dtls_rec.version_number,
2900 X_CAL_TYPE => l_stdnt_ps_attempt_dtls_rec.cal_type,
2901 X_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.location_cd,
2902 X_ATTENDANCE_MODE => l_stdnt_ps_attempt_dtls_rec.attendance_mode,
2903 X_ATTENDANCE_TYPE => l_stdnt_ps_attempt_dtls_rec.attendance_type,
2904 X_COO_ID => l_stdnt_ps_attempt_dtls_rec.coo_id,
2905 X_STUDENT_CONFIRMED_IND => l_stdnt_ps_attempt_dtls_rec.student_confirmed_ind,
2906 X_COMMENCEMENT_DT => l_stdnt_ps_attempt_dtls_rec.commencement_dt,
2907 X_COURSE_ATTEMPT_STATUS => l_stdnt_ps_attempt_dtls_rec.course_attempt_status,
2908 X_PROGRESSION_STATUS => l_stdnt_ps_attempt_dtls_rec.PROGRESSION_STATUS,
2909 X_DERIVED_ATT_TYPE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_TYPE,
2910 X_DERIVED_ATT_MODE => l_stdnt_ps_attempt_dtls_rec.DERIVED_ATT_MODE,
2911 X_PROVISIONAL_IND => l_stdnt_ps_attempt_dtls_rec.provisional_ind,
2912 X_DISCONTINUED_DT => l_stdnt_ps_attempt_dtls_rec.discontinued_dt,
2913 X_DISCONTINUATION_REASON_CD => l_stdnt_ps_attempt_dtls_rec.discontinuation_reason_cd,
2914 X_LAPSED_DT => l_stdnt_ps_attempt_dtls_rec.LAPSED_DT,
2915 X_FUNDING_SOURCE => l_stdnt_ps_attempt_dtls_rec.funding_source,
2916 X_EXAM_LOCATION_CD => l_stdnt_ps_attempt_dtls_rec.EXAM_LOCATION_CD,
2917 X_DERIVED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_YR,
2918 X_DERIVED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.DERIVED_COMPLETION_PERD,
2919 X_NOMINATED_COMPLETION_YR => l_stdnt_ps_attempt_dtls_rec.nominated_completion_yr,
2920 X_NOMINATED_COMPLETION_PERD => l_stdnt_ps_attempt_dtls_rec.NOMINATED_COMPLETION_PERD,
2921 X_RULE_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.RULE_CHECK_IND,
2922 X_WAIVE_OPTION_CHECK_IND => l_stdnt_ps_attempt_dtls_rec.WAIVE_OPTION_CHECK_IND,
2923 X_LAST_RULE_CHECK_DT => l_stdnt_ps_attempt_dtls_rec.LAST_RULE_CHECK_DT,
2924 X_PUBLISH_OUTCOMES_IND => l_stdnt_ps_attempt_dtls_rec.PUBLISH_OUTCOMES_IND,
2925 X_COURSE_RQRMNT_COMPLETE_IND => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNT_COMPLETE_IND,
2926 X_COURSE_RQRMNTS_COMPLETE_DT => l_stdnt_ps_attempt_dtls_rec.COURSE_RQRMNTS_COMPLETE_DT,
2927 X_S_COMPLETED_SOURCE_TYPE => l_stdnt_ps_attempt_dtls_rec.S_COMPLETED_SOURCE_TYPE,
2928 X_OVERRIDE_TIME_LIMITATION => l_stdnt_ps_attempt_dtls_rec.OVERRIDE_TIME_LIMITATION,
2929 x_last_date_of_attendance => l_stdnt_ps_attempt_dtls_rec.last_date_of_attendance,
2930 x_dropped_by => l_stdnt_ps_attempt_dtls_rec.dropped_by,
2931 X_IGS_PR_CLASS_STD_ID => l_stdnt_ps_attempt_dtls_rec.igs_pr_class_std_id,
2932 x_primary_program_type => l_stdnt_ps_attempt_dtls_rec.primary_program_type,
2933 x_primary_prog_type_source => l_stdnt_ps_attempt_dtls_rec.primary_prog_type_source,
2934 x_catalog_cal_type => l_stdnt_ps_attempt_dtls_rec.catalog_cal_type,
2935 x_catalog_seq_num => l_stdnt_ps_attempt_dtls_rec.catalog_seq_num,
2936 x_key_program => l_stdnt_ps_attempt_dtls_rec.key_program,
2937 x_override_cmpl_dt => l_stdnt_ps_attempt_dtls_rec.override_cmpl_dt,
2938 x_manual_ovr_cmpl_dt_ind => l_stdnt_ps_attempt_dtls_rec.manual_ovr_cmpl_dt_ind,
2939 X_MODE => 'R',
2940 X_ATTRIBUTE_CATEGORY => l_stdnt_ps_attempt_dtls_rec.attribute_category,
2941 X_ATTRIBUTE1 => l_stdnt_ps_attempt_dtls_rec.attribute1,
2942 X_ATTRIBUTE2 => l_stdnt_ps_attempt_dtls_rec.attribute2,
2943 X_ATTRIBUTE3 => l_stdnt_ps_attempt_dtls_rec.attribute3,
2944 X_ATTRIBUTE4 => l_stdnt_ps_attempt_dtls_rec.attribute4,
2945 X_ATTRIBUTE5 => l_stdnt_ps_attempt_dtls_rec.attribute5,
2946 X_ATTRIBUTE6 => l_stdnt_ps_attempt_dtls_rec.attribute6,
2947 X_ATTRIBUTE7 => l_stdnt_ps_attempt_dtls_rec.attribute7,
2948 X_ATTRIBUTE8 => l_stdnt_ps_attempt_dtls_rec.attribute8,
2949 X_ATTRIBUTE9 => l_stdnt_ps_attempt_dtls_rec.attribute9,
2950 X_ATTRIBUTE10 => l_stdnt_ps_attempt_dtls_rec.attribute10,
2951 X_ATTRIBUTE11 => l_stdnt_ps_attempt_dtls_rec.attribute11,
2952 X_ATTRIBUTE12 => l_stdnt_ps_attempt_dtls_rec.attribute12,
2953 X_ATTRIBUTE13 => l_stdnt_ps_attempt_dtls_rec.attribute13,
2954 X_ATTRIBUTE14 => l_stdnt_ps_attempt_dtls_rec.attribute14,
2955 X_ATTRIBUTE15 => l_stdnt_ps_attempt_dtls_rec.attribute15,
2956 X_ATTRIBUTE16 => l_stdnt_ps_attempt_dtls_rec.attribute16,
2957 X_ATTRIBUTE17 => l_stdnt_ps_attempt_dtls_rec.attribute17,
2958 X_ATTRIBUTE18 => l_stdnt_ps_attempt_dtls_rec.attribute18,
2959 X_ATTRIBUTE19 => l_stdnt_ps_attempt_dtls_rec.attribute19,
2960 X_ATTRIBUTE20 => l_stdnt_ps_attempt_dtls_rec.attribute20,
2961 X_FUTURE_DATED_TRANS_FLAG => l_stdnt_ps_attempt_dtls_rec.future_dated_trans_flag);
2962
2963
2964
2965
2966 END IF;-- end of p_discontinue_source = 'Y' AND p_dest_fut_dt_trans_flag = 'N' IF THEN
2967
2968 EXCEPTION
2969 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
2970 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
2971 RAISE;
2972 WHEN FND_API.G_EXC_ERROR THEN
2973 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
2974 RAISE;
2975 WHEN OTHERS THEN
2976 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE ;
2977 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2978 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_TRANSFER_APIS.update_source_prgm');
2979 IGS_GE_MSG_STACK.ADD;
2980 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
2981 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.update_source_prgm :',SQLERRM);
2982 END IF;
2983 App_Exception.Raise_Exception;
2984
2985 END update_source_prgm;
2986
2987
2988 PROCEDURE program_transfer_api(
2989 p_person_id IN NUMBER,
2990 p_source_program_cd IN VARCHAR2,
2991 p_source_prog_ver IN NUMBER,
2992 p_term_cal_type IN VARCHAR2,
2993 p_term_seq_num IN NUMBER,
2994 p_acad_cal_type IN VARCHAR2,
2995 p_acad_seq_num IN NUMBER,
2996 p_trans_approval_dt IN DATE,
2997 p_trans_actual_dt IN DATE,
2998 p_dest_program_cd IN VARCHAR2,
2999 p_dest_prog_ver IN NUMBER,
3000 p_dest_coo_id IN NUMBER,
3001 p_uoo_ids_to_transfer IN VARCHAR2,
3002 p_uoo_ids_not_selected IN VARCHAR2,
3003 p_uoo_ids_having_errors OUT NOCOPY VARCHAR2,
3004 p_unit_sets_to_transfer IN VARCHAR2,
3005 p_unit_sets_not_selected IN VARCHAR2,
3006 p_unit_sets_having_errors OUT NOCOPY VARCHAR2,
3007 p_transfer_av IN VARCHAR2,
3008 p_transfer_re IN VARCHAR2 ,
3009 p_discontinue_source IN VARCHAR2 ,
3010 p_show_warning IN VARCHAR2,
3011 p_call_from IN VARCHAR2,
3012 p_process_mode IN VARCHAR2,
3013 p_return_status OUT NOCOPY VARCHAR2,
3014 p_msg_data OUT NOCOPY VARCHAR2,
3015 p_msg_count OUT NOCOPY NUMBER
3016 ) AS
3017
3018 -- NOTE: Parameters p_unit_sets_not_selected, p_uoo_ids_not_selected are not being used at the moment.
3019 -- they had been introduced earlier but are not being removed in anticipation of future need.
3020
3021 -------------------------------------------------------------------------------------------
3022 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
3023 -- Purpose : This procedure validates holds,debt ,unit attempt transfer,unit set transfer by
3024 -- invoking appropriate procedures and updates the source and destination program
3025 -- respectively
3026 --Change History:
3027 --Who When What
3028 --ckasu 02-DEC-2004 modified as a part of Bug#4044329
3029 --stutta 10-DEC-2004 Setting global skip_before_after_dml to TRUE before update
3030 -- update destination, and unsetting it after update source. Bug #4046782
3031 -- smaddali 16-dec-04 modified for bug#4063726
3032 --bdeviset 22-Dec-2004 Modifed so as to update the transfer record when the transfer deatils
3033 -- are modified and added extra params status_date and status_flag for inserting
3034 -- transfer record as part Bug#4083015.
3035 --amuthu 23-DEC-2004 In the career mode if the source program of a transfer is secondary
3036 -- then an error message would be shown to the user.
3037 -- If the source program is primary and unconfrimed then it can be transfered
3038 -- only within the career, otherwise an error message would be shown.
3039 --bdeviset 31-DEC-2004 Bug# 4097481.Added a call to is_sua_enroll_eff_fut_term.
3040 --smaddali 5-jan-05 Bug#4103437 , modified logic for updating program transfer record
3041 --somasekar 13-apr-2005 bug# 4179106 modified to check the transfer status with 'S'
3042 -- instead of 'N'
3043 -- ckasu 08-DEC-2005 passed SYSDATE for update_source instead of p_actual_date param
3044 -- as part of bug#4869869
3045
3046 -------------------------------------------------------------------------------------------
3047
3048 l_api_name CONSTANT VARCHAR2(30) := 'PROGRAM_TRANSFER_APIS';
3049 l_discon_reason_code IGS_EN_STDNT_PS_ATT.DISCONTINUED_DT%TYPE ;
3050 l_discon_dt IGS_EN_STDNT_PS_ATT.DISCONTINUATION_REASON_CD%TYPE ;
3051 l_status BOOLEAN;
3052 l_return_value BOOLEAN;
3053 l_career_model_enabled BOOLEAN;
3054 l_tran_across_careers BOOLEAN;
3055 l_drop BOOLEAN;
3056 l_sct_tran_status BOOLEAN;
3057 l_src_career_type IGS_PS_VER.COURSE_TYPE%TYPE;
3058 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
3059 l_debt_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
3060 l_hold_status BOOLEAN;
3061
3062 l_person_type IGS_PE_PERSON_TYPES.person_type_code%TYPE;
3063 l_src_primary_prg_type IGS_EN_STDNT_PS_ATT.PRIMARY_PROGRAM_TYPE%TYPE;
3064 l_src_key_prgm IGS_EN_STDNT_PS_ATT.KEY_PROGRAM%TYPE;
3065 l_src_std_confrm_ind IGS_EN_STDNT_PS_ATT.STUDENT_CONFIRMED_IND%TYPE;
3066 l_src_prg_reasearch_ind IGS_PS_TYPE.research_type_ind%TYPE;
3067
3068 l_dest_primary_prg_type IGS_EN_STDNT_PS_ATT.PRIMARY_PROGRAM_TYPE%TYPE;
3069 l_old_dest_key_prgm_flag IGS_EN_STDNT_PS_ATT.KEY_PROGRAM%TYPE;
3070 l_new_dest_key_prgm_flag IGS_EN_STDNT_PS_ATT.KEY_PROGRAM%TYPE;
3071 l_dest_commence_dt IGS_EN_STDNT_PS_ATT.COMMENCEMENT_DT%TYPE;
3072 l_dest_prg_reasearch_ind IGS_PS_TYPE.research_type_ind%TYPE;
3073 l_dest_fut_dt_trans_flag IGS_EN_STDNT_PS_ATT.FUTURE_DATED_TRANS_FLAG%TYPE;
3074 l_dest_std_confrm_ind IGS_EN_STDNT_PS_ATT.STUDENT_CONFIRMED_IND%TYPE;
3075 l_course_attempt_status IGS_EN_STDNT_PS_ATT.COURSE_ATTEMPT_STATUS%TYPE;
3076 l_status_date CONSTANT DATE := SYSDATE;
3077 l_trans_rowid VARCHAR2(25);
3078 l_trans_comments igs_ps_stdnt_trn.comments%TYPE;
3079 l_trans_date igs_ps_stdnt_trn.transfer_dt%TYPE;
3080 l_trans_status igs_ps_stdnt_trn.status_flag%TYPE;
3081 l_unit_sets_to_transfer VARCHAR2(4000);
3082
3083 CURSOR c_get_key_val_frm_prg(c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE) IS
3084 SELECT key_program ,student_confirmed_ind,commencement_dt, course_attempt_status
3085 FROM IGS_EN_STDNT_PS_ATT
3086 WHERE person_id = c_person_id and
3087 course_cd = c_program_cd;
3088
3089 CURSOR c_get_std_course_ind_of_src (c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE) IS
3090 SELECT student_confirmed_ind, primary_program_type
3091 FROM IGS_EN_STDNT_PS_ATT
3092 WHERE person_id = c_person_id and
3093 course_cd = c_program_cd;
3094 CURSOR c_get_progam_type(c_course_cd IGS_PS_VER.course_cd%TYPE,c_course_ver IGS_PS_VER.version_number%TYPE) IS
3095 SELECT cty.research_type_ind
3096 FROM IGS_PS_VER crv,
3097 IGS_PS_TYPE cty
3098 WHERE crv.course_cd = c_course_cd AND
3099 crv.version_number = c_course_ver AND
3100 crv.course_type = cty.course_type ;
3101
3102 -- select any UNPROCESSED transfer record for the student with the same source and destination combo
3103 CURSOR c_chk_trans_rec (c_person_id igs_ps_stdnt_trn.person_id%TYPE,
3104 c_course_cd igs_ps_stdnt_trn.course_cd%TYPE,
3105 c_transfer_course_cd igs_ps_stdnt_trn.transfer_course_cd%TYPE) IS
3106 SELECT rowid, transfer_dt, comments
3107 FROM igs_ps_stdnt_trn
3108 WHERE person_id = c_person_id
3109 AND course_cd = c_course_cd
3110 AND transfer_course_cd = c_transfer_course_cd
3111 AND STATUS_FLAG = 'U';
3112
3113 l_uooids_str VARCHAR2(2000) ;
3114 l_uoo_ids_passed_transfer VARCHAR2(4000);
3115
3116
3117 -- smaddali added the following cursors for bug#4085979
3118 -- Fetch the relevant admissions course transfer record.
3119 CURSOR c_act ( cp_person_id IN IGS_AD_PS_APPL.person_id%TYPE,
3120 cp_course_cd IN IGS_AD_PS_APPL.nominated_course_cd%TYPE,
3121 cp_transfer_course_cd IN IGS_AD_PS_APPL.transfer_course_cd%TYPE) IS
3122 SELECT transfer_course_cd
3123 FROM IGS_AD_PS_APPL
3124 WHERE person_id = cp_person_id
3125 AND nominated_course_cd = cp_course_cd
3126 AND transfer_course_cd = cp_transfer_course_cd;
3127
3128 -- check if a transfer record exists for the passed source and destination or not
3129 CURSOR c_trans_exists (c_person_id igs_ps_stdnt_trn.person_id%TYPE,
3130 c_course_cd igs_ps_stdnt_trn.course_cd%TYPE,
3131 c_transfer_course_cd igs_ps_stdnt_trn.transfer_course_cd%TYPE) IS
3132 SELECT transfer_dt
3133 FROM igs_ps_stdnt_trn
3134 WHERE person_id = c_person_id
3135 AND course_cd = c_course_cd
3136 AND transfer_course_cd = c_transfer_course_cd
3137 AND status_flag <> 'C';
3138 l_trans_exists_rec c_trans_exists%ROWTYPE;
3139
3140 l_adm_course_cd IGS_AD_PS_APPL.transfer_course_cd%TYPE;
3141 l_adm_transfer BOOLEAN;
3142 l_key_program IGS_EN_SPA_TERMS.key_program_flag%TYPE;
3143 BEGIN
3144 SAVEPOINT TRANSFER_PRGM;
3145 FND_MSG_PUB.INITIALIZE;
3146 IF is_career_model_enabled THEN
3147 l_career_model_enabled := TRUE;
3148 ELSE
3149 l_career_model_enabled := FALSE;
3150 END IF;
3151 p_return_status := FND_API.G_RET_STS_SUCCESS;
3152 -- getting student confirmed indicator for source program
3153 OPEN c_get_std_course_ind_of_src(p_person_id,p_source_program_cd);
3154 FETCH c_get_std_course_ind_of_src INTO l_src_std_confrm_ind, l_src_primary_prg_type;
3155 CLOSE c_get_std_course_ind_of_src;
3156
3157 IF l_career_model_enabled AND l_src_primary_prg_type = 'SECONDARY' THEN
3158 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_SEC_CANT_BE_SRC');
3159 FND_MSG_PUB.ADD;
3160 RAISE FND_API.G_EXC_ERROR;
3161 END IF;
3162
3163 -- getting destination program key flag ,student confirmed
3164 -- indicator and destination commencement date values
3165 OPEN c_get_key_val_frm_prg(p_person_id,p_dest_program_cd);
3166 FETCH c_get_key_val_frm_prg INTO l_old_dest_key_prgm_flag,l_dest_std_confrm_ind,l_dest_commence_dt, l_course_attempt_status;
3167 CLOSE c_get_key_val_frm_prg;
3168
3169 -- l_tran_across_careers is assigned with TRUE when transfer is across career else FALSE.
3170 IF l_career_model_enabled THEN
3171 l_tran_across_careers := is_tranfer_across_careers(p_source_program_cd,
3172 p_source_prog_ver,
3173 p_dest_program_cd,
3174 p_dest_prog_ver,
3175 l_src_career_type);
3176 IF l_src_std_confrm_ind = 'N' AND l_tran_across_careers THEN
3177 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_UN_SRC_ONLY_INTRA');
3178 FND_MSG_PUB.ADD;
3179 RAISE FND_API.G_EXC_ERROR;
3180 END IF;
3181 END IF;
3182
3183 l_new_dest_key_prgm_flag := l_old_dest_key_prgm_flag;
3184 l_uooids_str := NULL;
3185 l_adm_transfer := FALSE;
3186
3187 l_person_type := Igs_En_Gen_008.enrp_get_person_type(p_course_cd =>NULL);
3188
3189 -- this procedure validates the Holds
3190 check_for_holds(p_person_id,
3191 p_dest_program_cd,
3192 p_term_cal_type,
3193 p_term_seq_num,
3194 l_person_type,
3195 p_return_status,
3196 p_show_warning);
3197
3198 -- this procedure validates the person steps
3199 validate_person_steps(p_person_id,
3200 p_dest_program_cd,
3201 p_dest_prog_ver,
3202 p_term_cal_type,
3203 p_term_seq_num,
3204 p_acad_cal_type,
3205 p_acad_seq_num,
3206 l_person_type,
3207 p_show_warning,
3208 p_return_status);
3209
3210 -- added for bug #4747585 by chanchal
3211 IF p_unit_sets_to_transfer IS NOT NULL THEN
3212
3213 l_unit_sets_to_transfer := arrange_selected_unitsets(p_person_id,
3214 p_source_program_cd,
3215 p_unit_sets_to_transfer);
3216
3217 END IF;
3218
3219
3220 -- This part need not be processed in case the stored future dated transfer records are being procesed for transfer
3221 -- since the research candidacy records were copied to the destination when the future dated transfer was created
3222 -- also the destination program attempt status has already been validated in the process wrapped. hence that too
3223 -- can be skipped.
3224 IF p_call_from <> 'PROCESS' THEN
3225
3226 -- validating and transfering research candidacy of source program that need to be transfered to destination
3227 IF p_transfer_re = 'Y' THEN
3228 -- smaddali Modified for bug#4063726, to disable transfers between research and non research programs
3229 -- getting research type indicator value for source program
3230 OPEN c_get_progam_type(p_source_program_cd,p_source_prog_ver);
3231 FETCH c_get_progam_type INTO l_src_prg_reasearch_ind;
3232 CLOSE c_get_progam_type;
3233
3234 -- getting research type indicator value for destination program
3235 OPEN c_get_progam_type(p_dest_program_cd,p_dest_prog_ver);
3236 FETCH c_get_progam_type INTO l_dest_prg_reasearch_ind;
3237 CLOSE c_get_progam_type;
3238
3239 -- cheking whether destination is an reasearch program or not when source program
3240 -- is an research program.
3241 IF l_src_prg_reasearch_ind = 'N' OR l_dest_prg_reasearch_ind = 'N' THEN
3242
3243 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_NOT_TRN_RESCAND');
3244 FND_MSG_PUB.ADD;
3245 RAISE FND_API.G_EXC_ERROR;
3246
3247 END IF;-- end of l_src_prg_reasearch_ind = 'Y' IF THEN
3248
3249 -- validating and transfering candidacy details
3250 validate_candidacy_tran_dtls(p_person_id,
3251 p_source_program_cd,
3252 p_source_prog_ver,
3253 p_dest_program_cd,
3254 p_dest_prog_ver,
3255 p_show_warning,
3256 p_return_status);
3257 END IF;-- end of p_transfer_re IF THEN
3258
3259 -- when destination program is not confirmed then validating inorder to make it confirm
3260 IF l_dest_std_confrm_ind = 'N' THEN
3261 check_is_dest_prgm_actv_confrm (p_person_id,
3262 p_source_program_cd,
3263 p_acad_cal_type,
3264 p_acad_seq_num,
3265 p_dest_program_cd,
3266 p_show_warning,
3267 l_dest_std_confrm_ind);
3268
3269 -- smaddali added logic for bug# 4085979
3270 -- to create the program transfer record before destination is confirmed.
3271 -- if the program is created thru an admission transfer application then
3272 -- program transfer record should be created before confirming the destination
3273 -- because the program attempt cannot be confirmed without a transfer record in this case
3274
3275 -- check if this program is created thru an admission program transfer application
3276 OPEN c_act (p_person_id, p_dest_program_cd, p_source_program_cd);
3277 FETCH c_act INTO l_adm_course_cd;
3278 IF c_act%FOUND THEN
3279 -- check if there is no existing trasnfer record from this source to this destination
3280 l_trans_exists_rec := NULL ;
3281 OPEN c_trans_exists (p_person_id, p_dest_program_cd, p_source_program_cd);
3282 FETCH c_trans_exists INTO l_trans_exists_rec;
3283 IF c_trans_exists%NOTFOUND THEN
3284 -- set the flag to indicate that program transfer record should be created before
3285 -- confirming the destination program attempt
3286 l_adm_transfer := TRUE;
3287 END IF;
3288 CLOSE c_trans_exists;
3289 END IF;
3290 CLOSE c_act;
3291
3292 END IF; -- end of l_dest_std_confrm_ind = 'N'
3293
3294 -- this checks whether destination program is discontinued or not.
3295 is_destn_prgm_att_discon(p_person_id,p_dest_program_cd,p_dest_prog_ver,l_status);
3296
3297 -- validation to check when destination program is discontinued status.
3298 IF l_status THEN
3299 l_return_value := IGS_EN_GEN_006.ENRP_GET_SCA_ELGBL(p_person_id,
3300 p_dest_program_cd,
3301 'RETURN',
3302 p_acad_cal_type,
3303 p_acad_seq_num,
3304 'Y',
3305 l_message_name);
3306 IF l_return_value = FALSE AND l_message_name NOT IN ('IGS_EN_STUD_INELIG_TO_RE_ENR','IGS_EN_INELIGBLE_DUE_TO_LAPSE',
3307 'IGS_EN_STUD_INELIGIBLE_RE_ENR','IGS_EN_STUD_NOT_HAVE_CURR_AFF',
3308 'IGS_EN_INTERM_DOES_NOT_END') THEN
3309 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name);
3310 FND_MSG_PUB.ADD;
3311 RAISE FND_API.G_EXC_ERROR;
3312 END IF;
3313 l_discon_dt := NULL;
3314 l_discon_reason_code := NULL;
3315 END IF;
3316 END IF;
3317 -- p_call_from=PROCESS
3318
3319 -- set the values of parameters to be used for further processing
3320 set_dest_prgm_att_params(p_person_id,
3321 p_source_program_cd,
3322 p_term_cal_type,
3323 p_term_seq_num,
3324 l_dest_primary_prg_type,
3325 l_new_dest_key_prgm_flag,
3326 l_dest_commence_dt,
3327 l_dest_fut_dt_trans_flag,
3328 p_transfer_re);
3329
3330 -- smaddali added logic for bug# 4085979
3331 -- if the destination is unconfirmed and is created from admission transfer application then
3332 -- create the program transfer record before confirming the destination
3333 IF l_adm_transfer THEN
3334 -- derive the transfer status flag value
3335 IF l_dest_fut_dt_trans_flag = 'S' THEN
3336 l_trans_status := 'T';
3337 ELSE
3338 l_trans_status := 'U';
3339 END IF;
3340 -- insert the transfer record.
3341 l_sct_tran_status := IGS_EN_GEN_009.Enrp_Ins_Sct_Trnsfr(p_person_id ,
3342 p_dest_program_cd ,
3343 p_source_program_cd ,
3344 p_trans_actual_dt ,
3345 l_message_name ,
3346 p_trans_approval_dt,
3347 p_term_cal_type ,
3348 p_term_seq_num,
3349 p_discontinue_source,
3350 p_uoo_ids_to_transfer,
3351 l_unit_sets_to_transfer,
3352 p_transfer_av,
3353 l_status_date,
3354 l_trans_status
3355 );
3356 END IF ; -- end of l_adm_transfer
3357
3358
3359 -- Updating destination program attempt during transfer.
3360 -- in this case the setting global variable to skip auto calculations of primary/secondary
3361 -- since the rank calculations need not be done. The destination will always be primary.
3362 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := TRUE ;
3363 update_destination_prgm(p_person_id,
3364 p_source_program_cd,
3365 p_dest_program_cd,
3366 l_new_dest_key_prgm_flag,
3367 l_dest_std_confrm_ind,
3368 l_dest_fut_dt_trans_flag,
3369 l_dest_commence_dt,
3370 l_tran_across_careers,
3371 p_term_cal_type ,
3372 p_term_seq_num);
3373
3374 -- Creating Transfer Record
3375 -- store the transfer date so that the same date can be used for creating unit transer record
3376 -- and discontinue source program attempt
3377 -- Transfer records are created when either its immediate trasnfer or when the future dates transfer is stored
3378 -- they will exist when a future dated trasnfer is actually processed.
3379
3380 IF l_dest_fut_dt_trans_flag = 'S' THEN
3381 l_trans_status := 'T';
3382 ELSE
3383 l_trans_status := 'U';
3384 END IF;
3385
3386 -- get the row in case an UNPROCESSED transfer record exists for
3387 -- the same pair of SPAs.
3388 OPEN c_chk_trans_rec (p_person_id, p_dest_program_cd, p_source_program_cd);
3389 FETCH c_chk_trans_rec INTO l_trans_rowid,l_trans_date, l_trans_comments;
3390 CLOSE c_chk_trans_rec;
3391 -- added extra check for unconfirmed admission applications, buig#4103437
3392 IF l_trans_rowid IS NOT NULL AND NOT l_adm_transfer THEN
3393 -- If an UNPROCESSED trasnfer record exists for the same pair of program codes
3394 -- then this record needs to be updated with the new information
3395 -- NOTE: The transfer term calendar information should be same as the old
3396 -- else the logic would need to change to delete the old term calendars
3397 -- and create new ones.
3398
3399 igs_ps_stdnt_trn_pkg.update_row(
3400 x_rowid => l_trans_rowid,
3401 x_person_id => p_person_id,
3402 x_course_cd => p_dest_program_cd,
3403 x_transfer_course_cd => p_source_program_cd,
3404 x_TRANSFER_DT => l_trans_date,
3405 x_COMMENTS => l_trans_comments,
3406 X_APPROVED_DATE => p_trans_approval_dt,
3407 X_EFFECTIVE_TERM_CAL_TYPE => p_term_cal_type,
3408 X_EFFECTIVE_TERM_SEQUENCE_NUM => p_term_seq_num,
3409 X_DISCONTINUE_SOURCE_FLAG => p_discontinue_source,
3410 X_UOOIDS_TO_TRANSFER => p_uoo_ids_to_transfer,
3411 X_SUSA_TO_TRANSFER => l_unit_sets_to_transfer,
3412 X_TRANSFER_ADV_STAND_FLAG => p_transfer_av,
3413 X_STATUS_DATE => l_status_date,
3414 X_STATUS_FLAG => l_trans_status
3415 );
3416 ELSE
3417
3418 -- when a future dated transfer is created If their is a enrolled/waitlisted/invalid unit attempt
3419 -- in the effective and future terms against destination program then future dated transfer cannot be stored
3420 IF p_call_from <> 'PROCESS' AND l_dest_fut_dt_trans_flag = 'Y' THEN
3421
3422 IF is_sua_enroll_eff_fut_term( p_person_id,
3423 p_dest_program_cd,
3424 p_term_cal_type,
3425 p_term_seq_num ) THEN
3426
3427 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ENR_SUA_CUR_FUT_TERM');
3428 FND_MSG_PUB.ADD;
3429 RAISE FND_API.G_EXC_ERROR;
3430
3431 END IF;
3432
3433 END IF;
3434
3435 -- smaddali added condition to create program transfer record if it hasn't been created before confirming destination
3436 -- due to l_adm_transfer flag for bug# 4085979
3437 IF NOT l_adm_transfer THEN
3438 -- insert the transfer record.
3439 l_sct_tran_status := IGS_EN_GEN_009.Enrp_Ins_Sct_Trnsfr(p_person_id ,
3440 p_dest_program_cd ,
3441 p_source_program_cd ,
3442 p_trans_actual_dt ,
3443 l_message_name ,
3444 p_trans_approval_dt,
3445 p_term_cal_type ,
3446 p_term_seq_num,
3447 p_discontinue_source,
3448 p_uoo_ids_to_transfer,
3449 l_unit_sets_to_transfer,
3450 p_transfer_av,
3451 l_status_date,
3452 l_trans_status
3453 );
3454 END IF;
3455
3456 -- Creating Program Administration Record
3457 create_prgm_admin_record (p_person_id ,
3458 p_source_program_cd,
3459 p_dest_program_cd,
3460 p_acad_cal_type,
3461 p_acad_seq_num );
3462
3463 -- creating term record for destination program
3464 l_key_program := FND_API.G_MISS_CHAR;
3465 IF (l_old_dest_key_prgm_flag <> l_new_dest_key_prgm_flag AND l_new_dest_key_prgm_flag = 'Y') THEN
3466 -- term api expects key_program_flag values only when key is changing to Y.
3467 l_key_program := 'Y';
3468 END IF;
3469 upd_or_create_dest_term_rec(p_person_id,
3470 p_dest_program_cd,
3471 p_term_cal_type,
3472 p_term_seq_num,
3473 l_key_program,
3474 l_message_name,
3475 l_dest_fut_dt_trans_flag);
3476 END IF; -- END of IF l_trans_rowid IS NOT NULL
3477
3478
3479 -- checking for debt when show warning is Y
3480 IF p_show_warning = 'Y' THEN
3481 check_for_debt(p_person_id ,
3482 p_source_program_cd,
3483 l_debt_message_name);
3484 IF l_debt_message_name IS NOT NULL THEN
3485 FND_MESSAGE.SET_NAME( 'IGS' , l_debt_message_name);
3486 FND_MSG_PUB.ADD;
3487 END IF;
3488 END IF;
3489
3490
3491 IF l_dest_fut_dt_trans_flag = 'S' THEN
3492
3493 -- Either if the source SPA is discontinued or the job is run in drop mode
3494 -- all the unit attempts have to be dropped
3495 IF p_discontinue_source = 'Y' OR p_process_mode = 'DROP' OR ( is_career_model_enabled AND l_tran_across_careers = FALSE ) THEN
3496 l_drop := TRUE;
3497 ELSE
3498 l_drop := FALSE;
3499 END IF;
3500
3501 -- validating and transfering units of source program that need to be transfered to destination
3502 validate_src_prgm_unt_attempts(p_person_id,
3503 p_source_program_cd,
3504 p_source_prog_ver,
3505 p_term_cal_type,
3506 p_term_seq_num ,
3507 p_acad_cal_type ,
3508 p_acad_seq_num ,
3509 p_trans_approval_dt,
3510 p_trans_actual_dt,
3511 p_dest_program_cd,
3512 p_dest_prog_ver,
3513 p_dest_coo_id ,
3514 p_uoo_ids_to_transfer,
3515 l_uoo_ids_passed_transfer,
3516 p_uoo_ids_having_errors,
3517 l_uooids_str,
3518 l_dest_fut_dt_trans_flag,
3519 p_show_warning,
3520 l_drop,
3521 p_return_status
3522 );
3523 -- validate the program step validations for destination program attempt if any of the unit attempts are being transfered
3524 -- added this validation as part of bug#4063726
3525 IF l_uoo_ids_passed_transfer IS NOT NULL THEN
3526 validate_prgm_attend_type_step(p_person_id,
3527 p_dest_program_cd,
3528 l_uoo_ids_passed_transfer,
3529 p_show_warning,
3530 p_return_status);
3531 END IF;
3532
3533 -- validating and transfering unit sets of source program that need to be transfered to destination
3534 validate_src_prgm_unt_set_att(p_person_id,
3535 p_source_program_cd,
3536 p_source_prog_ver,
3537 p_term_cal_type,
3538 p_term_seq_num,
3539 p_acad_cal_type,
3540 p_acad_seq_num,
3541 p_trans_approval_dt,
3542 p_dest_program_cd,
3543 p_dest_prog_ver,
3544 p_dest_coo_id,
3545 l_unit_sets_to_transfer ,
3546 p_unit_sets_not_selected,
3547 p_unit_sets_having_errors,
3548 p_show_warning,
3549 p_return_status);
3550
3551
3552 -- validating and transfering Advance standing of source program that need to be transfered to destination
3553 IF p_transfer_av = 'Y' THEN
3554 validate_advance_st_tran_dtls(p_person_id,
3555 p_source_program_cd,
3556 p_source_prog_ver,
3557 p_dest_program_cd,
3558 p_dest_prog_ver,
3559 p_show_warning);
3560 END IF; -- end of p_transfer_av = 'Y' IF THEN
3561
3562 -- updating source program attempt status and other values during transfer
3563 update_source_prgm (p_person_id,
3564 p_source_program_cd,
3565 p_source_prog_ver,
3566 p_dest_program_cd,
3567 p_trans_approval_dt,
3568 l_status_date,
3569 -- passing l_status_date = SYSDATE to p_trans_actual_dt parameter.
3570 -- This parameter is used while discontinuing the source program and has be >= Transfer date
3571 l_dest_fut_dt_trans_flag,
3572 p_discontinue_source,
3573 l_tran_across_careers,
3574 l_src_career_type);
3575 END IF;
3576 -- its an immediate transfer.
3577
3578 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
3579 FND_MSG_PUB.COUNT_AND_GET( p_count => p_msg_count,
3580 p_data => p_msg_data);
3581
3582 IF p_show_warning = 'Y' AND p_msg_count > 0 THEN
3583 ROLLBACK TO TRANSFER_PRGM;
3584 END IF;
3585
3586 EXCEPTION
3587 WHEN APP_EXCEPTION.APPLICATION_EXCEPTION THEN
3588 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
3589 p_return_status := FND_API.G_RET_STS_ERROR;
3590 FND_MESSAGE.SET_ENCODED(FND_MESSAGE.GET_ENCODED());
3591 FND_MSG_PUB.ADD;
3592 FND_MSG_PUB.COUNT_AND_GET( p_count => p_msg_count,
3593 p_data => p_msg_data);
3594 ROLLBACK TO TRANSFER_PRGM;
3595 WHEN FND_API.G_EXC_ERROR THEN
3596 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
3597 p_return_status := FND_API.G_RET_STS_ERROR;
3598 FND_MSG_PUB.COUNT_AND_GET( p_count => p_msg_count,
3599 p_data => p_msg_data);
3600 ROLLBACK TO TRANSFER_PRGM;
3601 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3602 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
3603 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3604 FND_MSG_PUB.COUNT_AND_GET( p_count => p_msg_count,
3605 p_data => p_msg_data);
3606 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
3607 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.program_transfer_api :',SQLERRM);
3608 END IF;
3609 ROLLBACK TO TRANSFER_PRGM;
3610 WHEN OTHERS THEN
3611 IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml := FALSE;
3612 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3613 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3614 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
3615 l_api_name);
3616 END IF;
3617 FND_MSG_PUB.COUNT_AND_GET( p_count => p_msg_count,
3618 p_data => p_msg_data);
3619 IF (FND_LOG.LEVEL_UNEXPECTED >= g_debug_level ) THEN
3620 FND_LOG.STRING(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_en_program_transfer_apis.program_transfer_api :',SQLERRM);
3621 END IF;
3622 ROLLBACK TO TRANSFER_PRGM;
3623
3624 END PROGRAM_TRANSFER_API;
3625
3626 PROCEDURE log_err_messages(
3627 p_msg_count IN NUMBER,
3628 p_msg_data IN VARCHAR2,
3629 p_warn_and_err_msg OUT NOCOPY VARCHAR2
3630 ) AS
3631 -------------------------------------------------------------------------------------------
3632 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
3633 -- purpose : this methos concatenates al the warning and error messages delimited by '<br>'
3634 -- that were recieved during program transfer.
3635 --Change History:
3636 --Who When What
3637
3638 --------------------------------------------------------------------------------------------
3639
3640
3641 l_msg_count NUMBER(4);
3642 l_msg_data VARCHAR2(4000);
3643 l_enc_msg VARCHAR2(2000);
3644 l_msg_index NUMBER(4);
3645 l_msg_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
3646 l_warn_and_err_msg VARCHAR2(5000);
3647
3648 BEGIN
3649
3650 l_msg_count := p_msg_count;
3651 l_msg_data := p_msg_data;
3652 l_warn_and_err_msg := null;
3653
3654 IF l_msg_count =1 THEN
3655 FND_MESSAGE.SET_ENCODED(l_msg_data);
3656 l_msg_text := FND_MESSAGE.GET;
3657 l_warn_and_err_msg := l_msg_text || '<br>';
3658 ELSIF l_msg_count > 1 THEN
3659 FOR l_index IN 1..NVL(l_msg_count,0)
3660 LOOP
3661 FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST,
3662 FND_API.G_TRUE,
3663 l_enc_msg,
3664 l_msg_index);
3665 FND_MESSAGE.SET_ENCODED(l_enc_msg);
3666 l_msg_text := FND_MESSAGE.GET;
3667 l_warn_and_err_msg := l_warn_and_err_msg||l_msg_text || '<br>';
3668
3669 FND_MSG_PUB.DELETE_MSG(l_msg_index);
3670
3671 END LOOP;
3672 END IF;
3673 p_warn_and_err_msg := l_warn_and_err_msg;
3674
3675 END log_err_messages;
3676
3677
3678 PROCEDURE cleanup_job(
3679 errbuf OUT NOCOPY VARCHAR2,
3680 retcode OUT NOCOPY NUMBER,
3681 p_term_cal_comb IN VARCHAR2,
3682 p_mode IN VARCHAR2,
3683 p_ignore_warnings IN VARCHAR2,
3684 p_drop_enrolled IN VARCHAR2
3685 ) AS
3686 -------------------------------------------------------------------------------------------
3687 -- Created by : Chandrasekhar Kasu, Oracle Student Systems Oracle IDC
3688 --Change History:
3689 --Who When What
3690 --ckasu 20-Nov-2004 changed the order of parameters passed
3691 --ckasu 17-JAN-2006 Added igs_ge_gen_003.set_org_id(NULL) as a part of bug#4958173.
3692 --------------------------------------------------------------------------------------------
3693
3694 BEGIN
3695
3696 igs_ge_gen_003.set_org_id(NULL);
3697 IGS_EN_FUTURE_DT_TRANS.process_fut_dt_trans(errbuf,
3698 retcode,
3699 p_term_cal_comb,
3700 p_mode,
3701 p_ignore_warnings,
3702 p_drop_enrolled);
3703
3704 END CLEANUP_JOB;
3705
3706 FUNCTION arrange_selected_unitsets(
3707 p_person_id IN NUMBER,
3708 p_program_cd IN VARCHAR2,
3709 p_unit_sets_to_transfer IN VARCHAR2
3710 ) RETURN VARCHAR2 IS
3711 -------------------------------------------------------------------------------------------
3712 -- Created by : chanchal tyagi, Oracle Student Systems Oracle IDC
3713 --Change History:
3714 --Who When What
3715 --ctyagi 25-Nov-2005 changed the order of unitset_attempt string
3716 -------------------------------------------------------------------------------------------
3717
3718 CURSOR c_parent_exist (c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,
3719 c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE,
3720 c_unitset_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
3721 c_seq_no IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE)
3722 IS
3723 SELECT 'x' FROM igs_as_su_setatmpt
3724 WHERE person_id = c_person_id
3725 AND COURSE_CD = c_program_cd
3726 AND UNIT_SET_CD = c_unitset_cd
3727 AND SEQUENCE_NUMBER= c_seq_no
3728 AND PARENT_UNIT_SET_CD IS NOT NULL;
3729
3730 CURSOR c_get_child (c_person_id IGS_EN_STDNT_PS_ATT.PERSON_ID%TYPE,
3731 c_program_cd IGS_EN_STDNT_PS_ATT.COURSE_CD%TYPE,
3732 c_unitset_cd IGS_AS_SU_SETATMPT.UNIT_SET_CD%TYPE,
3733 c_seq_no IGS_AS_SU_SETATMPT.SEQUENCE_NUMBER%TYPE)
3734 IS
3735 select susa.unit_set_cd || ',' || susa.sequence_number AS unitcd_seqno
3736 from igs_as_su_setatmpt susa
3737 where susa.person_id = c_person_id
3738 AND SUSA.COURSE_CD= c_program_cd
3739 AND level >= 2
3740 START WITH
3741 susa.person_id = c_person_id AND
3742 susa.course_cd = c_program_cd AND
3743 susa.unit_set_cd = c_unitset_cd AND
3744 susa.sequence_number = c_seq_no
3745 CONNECT BY
3746 PRIOR susa.person_id = susa.person_id AND
3747 PRIOR susa.course_cd = susa.course_cd AND
3748 PRIOR susa.unit_set_cd = susa.parent_unit_set_cd AND
3749 PRIOR susa.sequence_number = susa.parent_sequence_number
3750 ORDER BY level;
3751
3752 l_strtpoint NUMBER;
3753 l_endpoint NUMBER;
3754 l_cindex NUMBER;
3755 l_pre_cindex NUMBER;
3756 l_nth_occurence NUMBER;
3757 l_unitset_seqno_sep_index NUMBER;
3758 l_seqno_prmind_sep_index NUMBER;
3759 l_unitset_seqno_and_prmind VARCHAR2(4000);
3760 l_unitset VARCHAR2(4000);
3761 l_seqno NUMBER;
3762 l_prmind VARCHAR2(4000);
3763 l_unitsets_to_transfer VARCHAR2(4000);
3764
3765
3766 l_parent_unit_cd VARCHAR2(4000);
3767
3768 l_sub_token1 NUMBER;
3769 l_sub_token2 NUMBER;
3770 l_count_token NUMBER;
3771
3772 l_token_str VARCHAR2(4000);
3773
3774 l_dummy c_parent_exist%ROWTYPE;
3775 l_dummy_unitcd_seqno c_get_child%ROWTYPE;
3776
3777 l_final_selected_unitset VARCHAR2(4000);
3778
3779
3780 BEGIN
3781
3782 IF p_unit_sets_to_transfer IS NOT NULL THEN
3783
3784 l_unitsets_to_transfer := p_unit_sets_to_transfer;
3785 l_count_token := 1;
3786 l_strtpoint := 0;
3787 l_pre_cindex := 0;
3788 l_nth_occurence := 1;
3789 l_cindex := INSTR(l_unitsets_to_transfer,';',1,l_nth_occurence);
3790
3791 --single unit set attempt selected
3792 IF l_cindex = length(p_unit_sets_to_transfer) THEN
3793 return p_unit_sets_to_transfer ;
3794 END IF;
3795
3796 -- loop to get uniset wihtout any parent unitset
3797 WHILE (l_cindex <> 0) LOOP
3798 l_strtpoint := l_pre_cindex + 1;
3799 l_endpoint := l_cindex - l_strtpoint;
3800 l_pre_cindex := l_cindex;
3801 l_unitset_seqno_and_prmind := substr(l_unitsets_to_transfer,l_strtpoint,l_endpoint);
3802 l_unitset_seqno_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1);
3803 l_seqno_prmind_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1,2);
3804 l_unitset := SUBSTR(l_unitset_seqno_and_prmind,1,l_unitset_seqno_sep_index - 1);
3805 l_seqno := TO_NUMBER(SUBSTR(l_unitset_seqno_and_prmind,l_unitset_seqno_sep_index+1,l_seqno_prmind_sep_index -(l_unitset_seqno_sep_index+1)));
3806 l_prmind := SUBSTR(l_unitset_seqno_and_prmind,l_seqno_prmind_sep_index + 1);
3807
3808
3809
3810 OPEN c_parent_exist(p_person_id,p_program_cd,l_unitset,l_seqno);
3811 FETCH c_parent_exist INTO l_dummy;
3812 IF c_parent_exist%NOTFOUND THEN
3813 CLOSE c_parent_exist;
3814 l_final_selected_unitset := l_final_selected_unitset || l_unitset_seqno_and_prmind || ';' ;
3815
3816 l_count_token := l_count_token+1;
3817 l_nth_occurence := l_nth_occurence + 1;
3818 l_cindex := INSTR(l_unitsets_to_transfer,';',1,l_nth_occurence);
3819 ELSE
3820 CLOSE c_parent_exist;
3821 EXIT ;
3822 END IF;
3823
3824 END LOOP;
3825
3826
3827
3828 IF l_cindex < NVL(Length(l_unitsets_to_transfer),0) THEN
3829
3830
3831 l_strtpoint := 0;
3832 l_pre_cindex := 0;
3833 l_nth_occurence := 1;
3834
3835 -- loop for all top parent and add the child record
3836 WHILE (l_count_token > 1 ) LOOP
3837 l_cindex := INSTR(l_final_selected_unitset,';',1,l_nth_occurence);
3838 l_strtpoint := l_pre_cindex + 1;
3839 l_endpoint := l_cindex - l_strtpoint;
3840 l_pre_cindex := l_cindex;
3841 l_unitset_seqno_and_prmind := substr(l_unitsets_to_transfer,l_strtpoint,l_endpoint);
3842 l_unitset_seqno_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1);
3843 l_seqno_prmind_sep_index := INSTR(l_unitset_seqno_and_prmind,',',1,2);
3844 l_unitset := SUBSTR(l_unitset_seqno_and_prmind,1,l_unitset_seqno_sep_index - 1);
3845 l_seqno := TO_NUMBER(SUBSTR(l_unitset_seqno_and_prmind,l_unitset_seqno_sep_index+1,l_seqno_prmind_sep_index -(l_unitset_seqno_sep_index+1)));
3846 l_prmind := SUBSTR(l_unitset_seqno_and_prmind,l_seqno_prmind_sep_index + 1);
3847
3848 FOR l_dummy_unitcd_seqno IN c_get_child(p_person_id,p_program_cd,l_unitset,l_seqno) LOOP
3849
3850 l_sub_token1 := InStr(p_unit_sets_to_transfer,l_dummy_unitcd_seqno.unitcd_seqno,1,1);
3851
3852 IF l_sub_token1 <> 0 THEN
3853 l_sub_token2 := InStr(p_unit_sets_to_transfer,';',l_sub_token1,1);
3854
3855 l_token_str := SubStr(p_unit_sets_to_transfer,l_sub_token1,l_sub_token2-l_sub_token1);
3856
3857 l_final_selected_unitset := l_final_selected_unitset || l_token_str || ';';
3858
3859 END IF;
3860 END LOOP;
3861
3862 l_nth_occurence := l_nth_occurence + 1;
3863
3864 l_count_token := l_count_token -1 ;
3865 END LOOP;
3866
3867 END IF;
3868
3869 -- check if more unitset attempt string are peresent than processed till now
3870 IF NVL (LENGTH(l_final_selected_unitset),0) < LENGTH(p_unit_sets_to_transfer) THEN
3871 l_strtpoint := 0;
3872 l_pre_cindex := 0;
3873 l_nth_occurence := 1;
3874
3875 l_cindex := INSTR(p_unit_sets_to_transfer,';',1,l_nth_occurence);
3876
3877 WHILE (l_cindex <> 0) LOOP
3878 l_strtpoint := l_pre_cindex + 1;
3879 l_endpoint := l_cindex - l_strtpoint;
3880 l_pre_cindex := l_cindex;
3881 l_unitset_seqno_and_prmind := substr(p_unit_sets_to_transfer,l_strtpoint,l_endpoint);
3882
3883 l_sub_token1 := InStr(l_final_selected_unitset,l_unitset_seqno_and_prmind,1,1);
3884
3885 IF NVL(l_sub_token1,0) = 0 THEN
3886 l_final_selected_unitset := l_final_selected_unitset || l_unitset_seqno_and_prmind || ';' ;
3887 END IF;
3888
3889 IF NVL (LENGTH(l_final_selected_unitset),0) = LENGTH(p_unit_sets_to_transfer) THEN
3890 exit ;
3891 END IF;
3892 l_nth_occurence := l_nth_occurence + 1;
3893 l_cindex := INSTR(p_unit_sets_to_transfer,';',1,l_nth_occurence);
3894 END LOOP;
3895
3896
3897 END IF;
3898
3899 ELSE
3900 return p_unit_sets_to_transfer;
3901 END IF;
3902
3903 return l_final_selected_unitset;
3904
3905 END arrange_selected_unitsets;
3906
3907
3908
3909 END IGS_EN_TRANSFER_APIS;
[Home] [Help]