1 PACKAGE BODY igs_en_spa_lgcy_pub AS
2 /* $Header: IGSENA2B.pls 120.0 2005/06/03 15:54:41 appldev noship $ */
3 /*----------------------------------------------------------------------------
4 || Created By : kkillams
5 || Created On : 08-11-2002
6 || Purpose :
7 || Known limitations, enhancements or remarks :
8 || Change History :
9 || Who When What
10 || amuthu 07-JAN-03 Changed the validate_re_db_cons
11 || amuthu 07-JAN-03 Changed the validate_sca_db_cons
12 ------------------------------------------------------------------------------*/
13 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_SPA_LGCY_PUB';
14
15 FUNCTION validate_parameters(p_sca_re_rec IN SCA_RE_REC_TYPE,
16 p_career_flag IN VARCHAR2)
17 /*----------------------------------------------------------------------------
18 || Created By : kkillams
19 || Created On : 08-11-2002
20 || Purpose : Function validates the student program attempt legacy input parameters.
21 | On successful validation it returns true else false.
22 || Known limitations, enhancements or remarks :
23 || Change History :
24 || Who When What
25 ------------------------------------------------------------------------------*/
26 RETURN BOOLEAN AS
27 l_error_flag VARCHAR2(1);
28 l_message_count NUMBER(5);
29 BEGIN
30 --Set the error flag to N
31 l_error_flag := 'N';
32
33 --Validating the mandatory parameters.
34 IF p_sca_re_rec.person_number IS NULL THEN
35 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
36 FND_MSG_PUB.ADD;
37 l_error_flag := 'Y';
38 END IF;
39
40 IF p_sca_re_rec.program_cd IS NULL THEN
41 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
42 FND_MSG_PUB.ADD;
43 l_error_flag := 'Y';
44 END IF;
45
46 IF p_sca_re_rec.version_number IS NULL THEN
47 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_VER_NULL');
48 FND_MSG_PUB.ADD;
49 l_error_flag := 'Y';
50 END IF;
51
52 IF p_sca_re_rec.location_cd IS NULL THEN
53 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_LOC_NULL');
54 FND_MSG_PUB.ADD;
55 l_error_flag := 'Y';
56 END IF;
57
58 IF p_sca_re_rec.attendance_mode IS NULL THEN
59 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_MOD_NULL');
60 FND_MSG_PUB.ADD;
61 l_error_flag := 'Y';
62 END IF;
63
64 IF p_sca_re_rec.attendance_type IS NULL THEN
65 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_TYPE_NULL');
66 FND_MSG_PUB.ADD;
67 l_error_flag := 'Y';
68 END IF;
69
70 -- If any of the Descriptive Flex field columns have value , validate them .
71 IF (p_sca_re_rec.attribute_category IS NOT NULL OR p_sca_re_rec.attribute1 IS NOT NULL OR p_sca_re_rec.attribute2 IS NOT NULL OR
72 p_sca_re_rec.attribute3 IS NOT NULL OR p_sca_re_rec.attribute4 IS NOT NULL OR p_sca_re_rec.attribute5 IS NOT NULL OR
73 p_sca_re_rec.attribute6 IS NOT NULL OR p_sca_re_rec.attribute7 IS NOT NULL OR p_sca_re_rec.attribute8 IS NOT NULL OR
74 p_sca_re_rec.attribute9 IS NOT NULL OR p_sca_re_rec.attribute10 IS NOT NULL OR p_sca_re_rec.attribute11 IS NOT NULL OR
75 p_sca_re_rec.attribute12 IS NOT NULL OR p_sca_re_rec.attribute13 IS NOT NULL OR p_sca_re_rec.attribute14 IS NOT NULL OR
76 p_sca_re_rec.attribute15 IS NOT NULL OR p_sca_re_rec.attribute16 IS NOT NULL OR p_sca_re_rec.attribute17 IS NOT NULL OR
77 p_sca_re_rec.attribute18 IS NOT NULL OR p_sca_re_rec.attribute19 IS NOT NULL OR p_sca_re_rec.attribute20 IS NOT NULL )
78 THEN
79 IF NOT igs_ad_imp_018.validate_desc_flex (
80 p_attribute_category => p_sca_re_rec.attribute_category ,
81 p_attribute1 => p_sca_re_rec.attribute1 ,
82 p_attribute2 => p_sca_re_rec.attribute2 ,
83 p_attribute3 => p_sca_re_rec.attribute3 ,
84 p_attribute4 => p_sca_re_rec.attribute4 ,
85 p_attribute5 => p_sca_re_rec.attribute5 ,
86 p_attribute6 => p_sca_re_rec.attribute6 ,
87 p_attribute7 => p_sca_re_rec.attribute7 ,
88 p_attribute8 => p_sca_re_rec.attribute8 ,
89 p_attribute9 => p_sca_re_rec.attribute9 ,
90 p_attribute10 => p_sca_re_rec.attribute10 ,
91 p_attribute11 => p_sca_re_rec.attribute11 ,
92 p_attribute12 => p_sca_re_rec.attribute12 ,
93 p_attribute13 => p_sca_re_rec.attribute13 ,
94 p_attribute14 => p_sca_re_rec.attribute14 ,
95 p_attribute15 => p_sca_re_rec.attribute15 ,
96 p_attribute16 => p_sca_re_rec.attribute16 ,
97 p_attribute17 => p_sca_re_rec.attribute17 ,
98 p_attribute18 => p_sca_re_rec.attribute18 ,
99 p_attribute19 => p_sca_re_rec.attribute19 ,
100 p_attribute20 => p_sca_re_rec.attribute20 ,
101 p_desc_flex_name => 'IGS_EN_STDNT_ATT_FLEX'
102 ) THEN
103 FND_MESSAGE.SET_NAME( 'IGS','IGS_AD_INVALID_DESC_FLEX' );
104 FND_MSG_PUB.ADD;
105 l_error_flag := 'Y';
106 END IF ;
107 END IF;
108
109 --Do check constraints validation.
110 IF p_sca_re_rec.program_cd IS NOT NULL THEN
111 BEGIN
112 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'COURSE_CD',
113 column_value => p_sca_re_rec.program_cd);
114 EXCEPTION
115 WHEN OTHERS THEN
116 l_message_count := FND_MSG_PUB.COUNT_MSG;
117 FND_MSG_PUB.DELETE_MSG(l_message_count);
118 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_CD_UPPCASE');
119 FND_MSG_PUB.ADD;
120 l_error_flag := 'Y';
121 END;
122 END IF;
123
124 IF p_sca_re_rec.cal_type IS NOT NULL THEN
125 BEGIN
126 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'CAL_TYPE',
127 column_value => p_sca_re_rec.cal_type);
128 EXCEPTION
129 WHEN OTHERS THEN
130 l_message_count := FND_MSG_PUB.COUNT_MSG;
131 FND_MSG_PUB.DELETE_MSG(l_message_count);
132 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ACAD_CAL_UPPCASE');
133 FND_MSG_PUB.ADD;
134 l_error_flag := 'Y';
135 END;
136 END IF;
137
138 IF p_sca_re_rec.location_cd IS NOT NULL THEN
139 BEGIN
140 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'LOCATION_CD',
141 column_value => p_sca_re_rec.location_cd);
142 EXCEPTION
143 WHEN OTHERS THEN
144 l_message_count := FND_MSG_PUB.COUNT_MSG;
145 FND_MSG_PUB.DELETE_MSG(l_message_count);
146 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LOC_CD_UCASE');
147 FND_MSG_PUB.ADD;
148 l_error_flag := 'Y';
149 END;
150 END IF;
151
152 IF p_sca_re_rec.attendance_mode IS NOT NULL THEN
153 BEGIN
154 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'ATTENDANCE_MODE',
155 column_value => p_sca_re_rec.attendance_mode);
156 EXCEPTION
157 WHEN OTHERS THEN
158 l_message_count := FND_MSG_PUB.COUNT_MSG;
159 FND_MSG_PUB.DELETE_MSG(l_message_count);
160 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_MODE_UCASE');
161 FND_MSG_PUB.ADD;
162 l_error_flag := 'Y';
163 END;
164 END IF;
165
166 IF p_sca_re_rec.attendance_type IS NOT NULL THEN
167 BEGIN
168 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'ATTENDANCE_TYPE',
169 column_value => p_sca_re_rec.attendance_type);
170 EXCEPTION
171 WHEN OTHERS THEN
172 l_message_count := FND_MSG_PUB.COUNT_MSG;
173 FND_MSG_PUB.DELETE_MSG(l_message_count);
174 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ATT_TYPE_UCASE');
175 FND_MSG_PUB.ADD;
176 l_error_flag := 'Y';
177 END;
178 END IF;
179
180 IF p_sca_re_rec.student_confirmed_ind IS NOT NULL THEN
181 BEGIN
182 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'STUDENT_CONFIRMED_IND',
183 column_value => p_sca_re_rec.student_confirmed_ind);
184 EXCEPTION
185 WHEN OTHERS THEN
186 l_message_count := FND_MSG_PUB.COUNT_MSG;
187 FND_MSG_PUB.DELETE_MSG(l_message_count);
188 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_CONF_IND_INVALID');
189 FND_MSG_PUB.ADD;
190 l_error_flag := 'Y';
191 END;
192 END IF;
193
194 IF p_career_flag = 'Y' THEN
195 IF p_sca_re_rec.primary_program_type IS NOT NULL THEN
196 BEGIN
197 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'PRIMARY_PROGRAM_TYPE',
198 column_value => p_sca_re_rec.primary_program_type);
199 EXCEPTION
200 WHEN OTHERS THEN
201 l_message_count := FND_MSG_PUB.COUNT_MSG;
202 FND_MSG_PUB.DELETE_MSG(l_message_count);
203 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_PRI_PRG_INVALID');
204 FND_MSG_PUB.ADD;
205 l_error_flag := 'Y';
206 END;
207 END IF;
208 IF p_sca_re_rec.primary_prog_type_source IS NOT NULL THEN
209 BEGIN
210 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'PRIMARY_PROG_TYPE_SOURCE',
211 column_value => p_sca_re_rec.primary_prog_type_source);
212 EXCEPTION
213 WHEN OTHERS THEN
214 l_message_count := FND_MSG_PUB.COUNT_MSG;
215 FND_MSG_PUB.DELETE_MSG(l_message_count);
216 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_SRC_PRI_TYP_INVALID');
217 FND_MSG_PUB.ADD;
218 l_error_flag := 'Y';
219 END;
220 END IF;
221 END IF; --p_career_flag
222 IF p_sca_re_rec.key_program IS NOT NULL THEN
223 BEGIN
224 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'KEY_PROGRAM',
225 column_value => p_sca_re_rec.key_program);
226 EXCEPTION
227 WHEN OTHERS THEN
228 l_message_count := FND_MSG_PUB.COUNT_MSG;
229 FND_MSG_PUB.DELETE_MSG(l_message_count);
230 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_KEY_INVALID');
231 FND_MSG_PUB.ADD;
232 l_error_flag := 'Y';
233 END;
234 END IF;
235
236 IF p_sca_re_rec.provisional_ind IS NOT NULL THEN
237 BEGIN
238 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'PROVISIONAL_IND',
239 column_value => p_sca_re_rec.provisional_ind);
240 EXCEPTION
241 WHEN OTHERS THEN
242 l_message_count := FND_MSG_PUB.COUNT_MSG;
243 FND_MSG_PUB.DELETE_MSG(l_message_count);
244 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_PROV_IND_INVALID');
245 FND_MSG_PUB.ADD;
246 l_error_flag := 'Y';
247 END;
248 END IF;
249
250 IF p_sca_re_rec.discontinuation_reason_cd IS NOT NULL THEN
251 BEGIN
252 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'DISCONTINUATION_REASON_CD',
253 column_value => p_sca_re_rec.discontinuation_reason_cd);
254 EXCEPTION
255 WHEN OTHERS THEN
256 l_message_count := FND_MSG_PUB.COUNT_MSG;
257 FND_MSG_PUB.DELETE_MSG(l_message_count);
258 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_DIS_REAS_INVALID');
259 FND_MSG_PUB.ADD;
260 l_error_flag := 'Y';
261 END;
262 END IF;
263
264 IF p_sca_re_rec.funding_source IS NOT NULL THEN
265 BEGIN
266 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'FUNDING_SOURCE',
267 column_value => p_sca_re_rec.funding_source);
268 EXCEPTION
269 WHEN OTHERS THEN
270 l_message_count := FND_MSG_PUB.COUNT_MSG;
271 FND_MSG_PUB.DELETE_MSG(l_message_count);
272 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_FUND_UPPCASE');
273 FND_MSG_PUB.ADD;
274 l_error_flag := 'Y';
275 END;
276 END IF;
277
278 IF p_sca_re_rec.exam_location_cd IS NOT NULL THEN
279 BEGIN
280 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'EXAM_LOCATION_CD',
281 column_value => p_sca_re_rec.exam_location_cd);
282 EXCEPTION
283 WHEN OTHERS THEN
284 l_message_count := FND_MSG_PUB.COUNT_MSG;
285 FND_MSG_PUB.DELETE_MSG(l_message_count);
286 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_EXM_LOC_UPPCASE');
287 FND_MSG_PUB.ADD;
288 l_error_flag := 'Y';
289 END;
290 END IF;
291
292 IF p_sca_re_rec.nominated_completion_yr IS NOT NULL THEN
293 BEGIN
294 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'NOMINATED_COMPLETION_YR',
295 column_value => igs_ge_number.to_cann(p_sca_re_rec.nominated_completion_yr));
296 EXCEPTION
297 WHEN OTHERS THEN
298 l_message_count := FND_MSG_PUB.COUNT_MSG;
299 FND_MSG_PUB.DELETE_MSG(l_message_count);
300 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_NOM_YR_INVALID');
301 FND_MSG_PUB.ADD;
302 l_error_flag := 'Y';
303 END;
304 END IF;
305
306 IF p_sca_re_rec.nominated_completion_perd IS NOT NULL THEN
307 BEGIN
308 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'NOMINATED_COMPLETION_PERD',
309 column_value => p_sca_re_rec.nominated_completion_perd);
310 EXCEPTION
311 WHEN OTHERS THEN
312 l_message_count := FND_MSG_PUB.COUNT_MSG;
313 FND_MSG_PUB.DELETE_MSG(l_message_count);
314 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_NOM_PER_UPPCASE');
315 FND_MSG_PUB.ADD;
316 l_error_flag := 'Y';
317 END;
318 END IF;
319
320 IF p_sca_re_rec.rule_check_ind IS NOT NULL THEN
321 BEGIN
322 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'RULE_CHECK_IND',
323 column_value => p_sca_re_rec.rule_check_ind);
324 EXCEPTION
325 WHEN OTHERS THEN
326 l_message_count := FND_MSG_PUB.COUNT_MSG;
327 FND_MSG_PUB.DELETE_MSG(l_message_count);
328 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_RUL_IND_INVALID');
329 FND_MSG_PUB.ADD;
330 l_error_flag := 'Y';
331 END;
332 END IF;
333
334 IF p_sca_re_rec.waive_option_check_ind IS NOT NULL THEN
335 BEGIN
336 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'WAIVE_OPTION_CHECK_IND',
337 column_value => p_sca_re_rec.waive_option_check_ind);
338 EXCEPTION
339 WHEN OTHERS THEN
340 l_message_count := FND_MSG_PUB.COUNT_MSG;
341 FND_MSG_PUB.DELETE_MSG(l_message_count);
342 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_WAV_IND_INVALID');
343 FND_MSG_PUB.ADD;
344 l_error_flag := 'Y';
345 END;
346 END IF;
347
348 IF p_sca_re_rec.publish_outcomes_ind IS NOT NULL THEN
349 BEGIN
350 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'PUBLISH_OUTCOMES_IND',
351 column_value => p_sca_re_rec.publish_outcomes_ind);
352 EXCEPTION
353 WHEN OTHERS THEN
354 l_message_count := FND_MSG_PUB.COUNT_MSG;
355 FND_MSG_PUB.DELETE_MSG(l_message_count);
356 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_PUB_OUT_INVALID');
357 FND_MSG_PUB.ADD;
358 l_error_flag := 'Y';
359 END;
360 END IF;
361
362 IF p_sca_re_rec.course_rqrmnt_complete_ind IS NOT NULL THEN
363 BEGIN
364 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'COURSE_RQRMNT_COMPLETE_IND',
365 column_value => p_sca_re_rec.course_rqrmnt_complete_ind);
366 EXCEPTION
367 WHEN OTHERS THEN
368 l_message_count := FND_MSG_PUB.COUNT_MSG;
369 FND_MSG_PUB.DELETE_MSG(l_message_count);
370 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_CRS_RQR_CMP_IND_INVALID');
371 FND_MSG_PUB.ADD;
372 l_error_flag := 'Y';
373 END;
374 END IF;
375
376 IF p_sca_re_rec.s_completed_source_type IS NOT NULL THEN
377 BEGIN
378 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'S_COMPLETED_SOURCE_TYPE',
379 column_value => p_sca_re_rec.s_completed_source_type);
380 EXCEPTION
381 WHEN OTHERS THEN
382 l_message_count := FND_MSG_PUB.COUNT_MSG;
383 FND_MSG_PUB.DELETE_MSG(l_message_count);
384 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_SYS_COMP_INVALID');
385 FND_MSG_PUB.ADD;
386 l_error_flag := 'Y';
387 END;
388 END IF;
389
390 IF p_sca_re_rec.correspondence_cat IS NOT NULL THEN
391 BEGIN
392 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'CORRESPONDENCE_CAT',
393 column_value => p_sca_re_rec.correspondence_cat);
394 EXCEPTION
395 WHEN OTHERS THEN
396 l_message_count := FND_MSG_PUB.COUNT_MSG;
397 FND_MSG_PUB.DELETE_MSG(l_message_count);
398 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_CORR_CAT_INALID');
399 FND_MSG_PUB.ADD;
400 l_error_flag := 'Y';
401 END;
402 END IF;
403
404 IF p_sca_re_rec.self_help_group_ind IS NOT NULL THEN
405 BEGIN
406 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'SELF_HELP_GROUP_IND',
407 column_value => p_sca_re_rec.self_help_group_ind);
408 EXCEPTION
409 WHEN OTHERS THEN
410 l_message_count := FND_MSG_PUB.COUNT_MSG;
411 FND_MSG_PUB.DELETE_MSG(l_message_count);
412 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_HLP_GRP_IND_INVALID');
413 FND_MSG_PUB.ADD;
414 l_error_flag := 'Y';
415 END;
416 END IF;
417
418 IF p_sca_re_rec.adm_nominated_course_cd IS NOT NULL THEN
419 BEGIN
420 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'ADM_NOMINATED_COURSE_CD',
421 column_value => p_sca_re_rec.adm_nominated_course_cd);
422 EXCEPTION
423 WHEN OTHERS THEN
424 l_message_count := FND_MSG_PUB.COUNT_MSG;
425 FND_MSG_PUB.DELETE_MSG(l_message_count);
426 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_ADM_NOM_PG_INVALID');
427 FND_MSG_PUB.ADD;
428 l_error_flag := 'Y';
429 END;
430 END IF;
431
432 IF p_sca_re_rec.manual_ovr_cmpl_dt_ind IS NOT NULL THEN
433 BEGIN
434 igs_en_stdnt_ps_att_pkg.check_constraints(column_name => 'MANUAL_OVR_CMPL_DT_IND',
435 column_value => p_sca_re_rec.manual_ovr_cmpl_dt_ind);
436 EXCEPTION
437 WHEN OTHERS THEN
438 l_message_count := FND_MSG_PUB.COUNT_MSG;
439 FND_MSG_PUB.DELETE_MSG(l_message_count);
440 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRG_MAN_OVR_IND_INVALID');
441 FND_MSG_PUB.ADD;
442 l_error_flag := 'Y';
443 END;
444 END IF;
445
446 IF p_sca_re_rec.re_attendance_percentage IS NOT NULL THEN
447 BEGIN
448 igs_re_candidature_pkg.check_constraints(column_name => 'ATTENDANCE_PERCENTAGE',
449 column_value => igs_ge_number.to_cann(p_sca_re_rec.re_attendance_percentage));
450 EXCEPTION
451 WHEN OTHERS THEN
452 l_message_count := FND_MSG_PUB.COUNT_MSG;
453 FND_MSG_PUB.DELETE_MSG(l_message_count);
454 FND_MESSAGE.SET_NAME('IGS','IGS_RE_ATT_PER_INVALID');
455 FND_MSG_PUB.ADD;
456 l_error_flag := 'Y';
457 END;
458 END IF;
459
460 IF p_sca_re_rec.re_govt_type_of_activity_cd IS NOT NULL THEN
461 BEGIN
462 igs_re_candidature_pkg.check_constraints(column_name => 'GOVT_TYPE_OF_ACTIVITY_CD',
463 column_value => p_sca_re_rec.re_govt_type_of_activity_cd);
464 EXCEPTION
465 WHEN OTHERS THEN
466 l_message_count := FND_MSG_PUB.COUNT_MSG;
467 FND_MSG_PUB.DELETE_MSG(l_message_count);
468 FND_MESSAGE.SET_NAME('IGS','IGS_RE_GOVT_CD_UPPCASE');
469 FND_MSG_PUB.ADD;
470 l_error_flag := 'Y';
471 END;
472 END IF;
473
474 IF l_error_flag = 'Y' THEN
475 RETURN FALSE;
476 ELSE
477 RETURN TRUE;
478 END IF;
479 END validate_parameters;
480
481
482 FUNCTION validate_sca_db_cons(p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
483 p_sca_re_rec IN SCA_RE_REC_TYPE)
484 RETURN VARCHAR2 AS
485 /*----------------------------------------------------------------------------
486 || Created By : kkillams
487 || Created On : 08-11-2002
488 || Purpose : Function validates student program attempt table constraints.
489 || Known limitations, enhancements or remarks :
490 || Change History :
491 || Who When What
492 || amuthu 07-JAN-03 Added the validation for Nominated Completion Period
493 || and correspondence category
494 ------------------------------------------------------------------------------*/
495 l_result_flag VARCHAR2(1);
496 BEGIN --validate_sca_db_cons
497
498 l_result_flag := 'S';
499
500 --Check whether student program attemt record is already exist in the database or not.
501 --If exist set the warning message and terminate the function
502 IF igs_en_stdnt_ps_att_pkg.get_pk_for_validation(x_person_id =>p_person_id,
503 x_course_cd =>p_sca_re_rec.program_cd) THEN
504 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PS_ATT_EXISTS');
505 FND_MSG_PUB.ADD;
506 RETURN 'P';
507 ELSE
508 --Check whether parent record is exist for the relative child record in student program attempt
509 IF ( (p_sca_re_rec.adm_admission_appl_number IS NOT NULL) OR
510 (p_sca_re_rec.adm_nominated_course_cd IS NOT NULL ) OR
511 (p_sca_re_rec.adm_sequence_number IS NOT NULL)) THEN
512 IF NOT igs_ad_ps_appl_inst_pkg.get_pk_for_validation(x_person_id => p_person_id,
513 x_admission_appl_number => p_sca_re_rec.adm_admission_appl_number,
514 x_nominated_course_cd => p_sca_re_rec.adm_nominated_course_cd,
515 x_sequence_number => p_sca_re_rec.adm_sequence_number) THEN
516 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_APP_DET_NOT_FOUND');
517 FND_MSG_PUB.ADD;
518 l_result_flag := 'E';
519 END IF;
520 END IF;
521 IF p_sca_re_rec.discontinuation_reason_cd IS NOT NULL THEN
522 IF NOT igs_en_dcnt_reasoncd_pkg.get_pk_for_validation(x_dcnt_reason_cd => p_sca_re_rec.discontinuation_reason_cd) THEN
523 FND_MESSAGE.SET_NAME('IGS','IGS_EN_DCNT_REAS_NOT_FOUND');
524 FND_MSG_PUB.ADD;
525 l_result_flag := 'E';
526 END IF;
527 END IF;
528 IF p_sca_re_rec.fee_cat IS NOT NULL THEN
529 IF NOT igs_fi_fee_cat_pkg.get_pk_for_validation(x_fee_cat => p_sca_re_rec.fee_cat) THEN
530 FND_MESSAGE.SET_NAME('IGS','IGS_EN_FEE_CAT_NOT_FOUND');
531 FND_MSG_PUB.ADD;
532 l_result_flag := 'E';
533 END IF;
534 END IF;
535 IF p_sca_re_rec.funding_source IS NOT NULL THEN
536 IF NOT igs_fi_fund_src_pkg.get_pk_for_validation(x_funding_source => p_sca_re_rec.funding_source) THEN
537 FND_MESSAGE.SET_NAME('IGS','IGS_EN_FUND_SRC_NOT_FOUND');
538 FND_MSG_PUB.ADD;
539 l_result_flag := 'E';
540 END IF;
541 END IF;
542 IF p_sca_re_rec.exam_location_cd IS NOT NULL THEN
543 IF NOT igs_ad_location_pkg.get_pk_for_validation(x_location_cd => p_sca_re_rec.exam_location_cd) THEN
544 FND_MESSAGE.SET_NAME('IGS','IGS_EN_EXAM_LOC_NOT_FOUND');
545 FND_MSG_PUB.ADD;
546 l_result_flag := 'E';
547 END IF;
548 END IF;
549
550 IF p_sca_re_rec.NOMINATED_COMPLETION_PERD IS NOT NULL THEN
551 IF NOT igs_en_nom_cmpl_prd_pkg.get_pk_for_validation(x_completion_perd => p_sca_re_rec.NOMINATED_COMPLETION_PERD ) THEN
552 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NOM_CMPL_PRD_NOT_FOUND');
553 FND_MSG_PUB.ADD;
554 l_result_flag := 'E';
555 END IF;
556 END IF;
557
558 IF p_sca_re_rec.CORRESPONDENCE_CAT IS NOT NULL THEN
559 IF NOT IGS_CO_CAT_PKG.get_pk_for_validation(x_correspondence_cat => p_sca_re_rec.CORRESPONDENCE_CAT ) THEN
560 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CO_CAT_NOT_FOUND');
561 FND_MSG_PUB.ADD;
562 l_result_flag := 'E';
563 END IF;
564 END IF;
565
566 IF p_sca_re_rec.primary_prog_type_source IS NOT NULL THEN
567 IF NOT igs_lookups_view_pkg.get_pk_for_validation('IGS_EN_PP_SOURCE',p_sca_re_rec.primary_prog_type_source) THEN
568 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRI_PRG_TYP_NOT_FOUND');
569 FND_MSG_PUB.ADD;
570 l_result_flag := 'E';
571 END IF;
572 END IF;
573 RETURN l_result_flag;
574 END IF;
575 END validate_sca_db_cons;
576
577 FUNCTION validate_sca(p_person_id IN igs_pe_person.person_id%TYPE,
578 p_course_att_status IN igs_en_stdnt_ps_att_all.course_attempt_status%TYPE,
579 p_course_type IN igs_ps_ver_all.course_type%TYPE,
580 p_career_flag IN VARCHAR2,
581 p_sca_re_rec IN SCA_RE_REC_TYPE)
582 RETURN BOOLEAN AS
583 /*----------------------------------------------------------------------------
584 || Created By : kkillams
585 || Created On : 08-11-2002
586 || Purpose : Function validates Student program attempt's business validations
587 || Known limitations, enhancements or remarks :
588 || Change History :
589 || Who When What
590 || kkillams 31-12-2002 Bypass the val_sca_primary_pg function call if
591 || program attempt status is UNCONFIRM w.r.t. bug 2721076
592 ------------------------------------------------------------------------------*/
593 l_validation_success BOOLEAN;
594 l_message VARCHAR2(200);
595 l_boolean BOOLEAN;
596 l_start NUMBER(4);
597 l_end NUMBER(4);
598 BEGIN --validate_sca
599 l_validation_success := TRUE;
600
601 --Validates the person types for this student program attempt
602 IF NOT igs_en_gen_legacy.val_sca_per_type(p_person_id => p_person_id,
603 p_course_cd => p_sca_re_rec.program_cd,
604 p_course_attempt_status => p_course_att_status) THEN
605 l_validation_success := FALSE;
606 END IF;
607 IF NOT igs_en_gen_legacy.val_sca_start_dt(p_student_confirmed_ind => p_sca_re_rec.student_confirmed_ind,
608 p_commencement_dt => p_sca_re_rec.commencement_dt) THEN
609 l_validation_success := FALSE;
610 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SCA_COMM_DT_INVALID');
611 FND_MSG_PUB.ADD;
612 END IF;
613
614 --Validates the discontinuation of student program attempt.
615 IF (p_sca_re_rec.discontinued_dt IS NOT NULL OR p_sca_re_rec.discontinuation_reason_cd IS NOT NULL) THEN
616 l_message := NULL;
617 l_boolean := NULL;
618 l_boolean:=igs_en_val_sca.enrp_val_sca_discont(p_person_id => p_person_id,
619 p_course_cd => p_sca_re_rec.program_cd,
620 p_version_number => p_sca_re_rec.version_number,
621 p_course_attempt_status => p_course_att_status,
622 p_discontinuation_reason_cd => p_sca_re_rec.discontinuation_reason_cd,
623 p_discontinued_dt => p_sca_re_rec.discontinued_dt,
624 p_commencement_dt => p_sca_re_rec.commencement_dt,
625 p_message_name => l_message,
626 p_legacy => 'Y');
627 IF l_message IS NOT NULL THEN
628 l_validation_success := FALSE;
629 END IF;
630 END IF;
631
632 --Validating the lapse date aganist program attempt status.
633 IF p_sca_re_rec.lapsed_dt IS NOT NULL THEN
634 l_message := NULL;
635 l_boolean := NULL;
636 l_boolean:=igs_en_val_sca.enrp_val_sca_lapse (p_course_attempt_status => p_course_att_status,
637 p_lapse_dt => p_sca_re_rec.lapsed_dt,
638 p_message_name => l_message,
639 p_legacy => 'Y');
640
641 IF l_message IS NOT NULL THEN
642 l_validation_success := FALSE;
643 END IF;
644 END IF;
645 IF p_sca_re_rec.discontinued_dt IS NOT NULL THEN
646 IF NOT igs_en_gen_legacy.val_sca_disc_date(p_discontinued_dt => p_sca_re_rec.discontinued_dt) THEN
647 l_validation_success := FALSE;
648 FND_MESSAGE.SET_NAME('IGS','IGS_EN_SCA_DISC_DT_NOT_FUTURE');
649 FND_MSG_PUB.ADD;
650 END IF;
651 END IF;
652
653 IF NOT igs_en_gen_legacy.val_sca_comp_flag(p_course_attempt_status => p_course_att_status,
654 p_course_rqrmnt_complete_ind => p_sca_re_rec.course_rqrmnt_complete_ind) THEN
655 l_validation_success := FALSE;
656 FND_MESSAGE.SET_NAME('IGS','IGS_PR_CANNOT_SET_COMPL_IND');
657 FND_MSG_PUB.ADD;
658 END IF;
659
660 --Validating the Discountinue date aganist Requirement completion indicatory.
661 IF p_sca_re_rec.discontinued_dt IS NOT NULL AND p_sca_re_rec.course_rqrmnt_complete_ind IS NOT NULL THEN
662 IF p_sca_re_rec.course_rqrmnt_complete_ind ='Y' THEN
663 l_validation_success := FALSE;
664 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ONLY_SPA_ST_ENROLLED');
665 FND_MSG_PUB.ADD;
666 END IF;
667 END IF;
668
669 --Validating requirement completion date aganist commencement date.
670 IF p_sca_re_rec.course_rqrmnts_complete_dt IS NOT NULL THEN
671 l_message := NULL;
672 l_boolean := NULL;
673 l_boolean:=igs_pr_val_sca.prgp_val_sca_cmpl_dt(p_person_id => p_person_id,
674 p_course_cd => p_sca_re_rec.program_cd,
675 p_commencement_dt => p_sca_re_rec.commencement_dt,
676 p_course_rqrmnts_complete_dt => p_sca_re_rec.course_rqrmnts_complete_dt,
677 p_message_name => l_message,
678 p_legacy => 'Y');
679 IF l_message IS NOT NULL THEN
680 l_validation_success := FALSE;
681 IF INSTR(l_message,'*') = 0 THEN
682 FND_MESSAGE.SET_NAME('IGS',l_message);
683 FND_MSG_PUB.ADD;
684 ELSE
685 l_start := 1;
686 LOOP
687 l_end:= INSTR(l_message,'*',l_start);
688 IF l_end = 0 THEN
689 FND_MESSAGE.SET_NAME('IGS',SUBSTR(l_message,l_start,LENGTH(l_message)-l_start+1));
690 FND_MSG_PUB.ADD;
691 EXIT;
692 ELSE
693 FND_MESSAGE.SET_NAME('IGS',SUBSTR(l_message,l_start,l_end-l_start));
694 FND_MSG_PUB.ADD;
695 END IF;
696 l_start :=l_end +1;
697 END LOOP;
698 END IF;
699 END IF;
700 END IF;
701
702 l_message := NULL;
703 IF NOT igs_en_gen_legacy.val_sca_reqcmpl_dt(p_course_rqrmnt_comp_ind => p_sca_re_rec.course_rqrmnt_complete_ind,
704 p_course_rqrmnts_comp_dt => p_sca_re_rec.course_rqrmnts_complete_dt,
705 p_message_name => l_message) THEN
706 l_validation_success := FALSE;
707 FND_MESSAGE.SET_NAME('IGS',l_message);
708 FND_MSG_PUB.ADD;
709 END IF;
710
711 --In career mode, validating the primary program of the student program attempt.
712 IF (p_career_flag = 'Y') AND (p_course_att_status <> 'UNCONFIRM') THEN
713 IF NOT igs_en_gen_legacy.val_sca_primary_pg(p_person_id =>p_person_id,
714 p_primary_prog_type =>p_sca_re_rec.primary_program_type,
715 P_course_type =>p_course_type) THEN
716 l_validation_success := FALSE;
717 END IF;
718 END IF;
719 IF NOT igs_en_gen_legacy.val_sca_key_prg(p_person_id => p_person_id,
720 p_course_cd => p_sca_re_rec.program_cd,
721 p_key_program => p_sca_re_rec.key_program,
722 p_primary_prg_type => p_sca_re_rec.primary_program_type,
723 p_course_attempt_st => p_course_att_status,
724 p_career_flag => p_career_flag) THEN
725 l_validation_success := FALSE;
726 END IF;
727 RETURN l_validation_success;
728 END validate_sca;
729
730 FUNCTION validate_re_db_cons(p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE,
731 p_sca_re_rec IN SCA_RE_REC_TYPE)
732 RETURN VARCHAR2 AS
733 /*----------------------------------------------------------------------------
734 || Created By : kkillams
735 || Created On : 08-11-2002
736 || Purpose : Function validates Research candidature table constraints.
737 || Known limitations, enhancements or remarks :
738 || Change History :
739 || Who When What
740 || amuthu 07-JAN-03 Changed the message IGS_EN_DCNT_REAS_NOT_FOUND
741 || to IGS_RE_GOV_ACT_CD_NOT_FOUND govt activity
742 || code validation
743 ------------------------------------------------------------------------------*/
744 l_result_flag VARCHAR2(1);
745 l_count NUMBER(3);
746 BEGIN --validate_re_db_cons
747 l_result_flag := 'S';
748 --Check whether research candidature record is already exist in the database or not.
749 --If exist set the warning message and terminate the function
750 BEGIN
751 igs_re_candidature_pkg.get_fk_igs_en_stdnt_ps_att(x_person_id =>p_person_id,
752 x_course_cd =>p_sca_re_rec.program_cd);
753 EXCEPTION
754 WHEN OTHERS THEN
755 l_count :=FND_MSG_PUB.COUNT_MSG;
756 FND_MSG_PUB.DELETE_MSG(l_count);
757 FND_MESSAGE.SET_NAME('IGS','IGS_RE_CAND_EXIST');
758 FND_MSG_PUB.ADD;
759 RETURN 'P';
760 END;
761 --Check whether parent record is exist for the relative child record in student program attempt
762 IF ( (p_sca_re_rec.adm_admission_appl_number IS NOT NULL) OR
763 (p_sca_re_rec.adm_nominated_course_cd IS NOT NULL ) OR
764 (p_sca_re_rec.adm_sequence_number IS NOT NULL)) THEN
765 IF NOT igs_ad_ps_appl_inst_pkg.get_pk_for_validation(x_person_id => p_person_id,
766 x_admission_appl_number => p_sca_re_rec.adm_admission_appl_number,
767 x_nominated_course_cd => p_sca_re_rec.adm_nominated_course_cd,
768 x_sequence_number => p_sca_re_rec.adm_sequence_number) THEN
769 FND_MESSAGE.SET_NAME('IGS','IGS_EN_ADM_APP_DET_NOT_FOUND');
770 FND_MSG_PUB.ADD;
771 l_result_flag := 'E';
772 END IF;
773 END IF;
774 IF p_sca_re_rec.re_govt_type_of_activity_cd IS NOT NULL THEN
775 IF NOT igs_re_gv_toa_cls_cd_pkg.get_pk_for_validation(x_govt_toa_class_cd => p_sca_re_rec.re_govt_type_of_activity_cd) THEN
776 FND_MESSAGE.SET_NAME('IGS','IGS_RE_GOV_ACT_CD_NOT_FOUND');
777 FND_MSG_PUB.ADD;
778 l_result_flag := 'E';
779 END IF;
780 END IF;
781 RETURN l_result_flag;
782 END validate_re_db_cons;
783 FUNCTION validate_re (p_person_id IN igs_pe_person.person_id%TYPE,
784 p_sca_re_rec IN SCA_RE_REC_TYPE)
785 RETURN BOOLEAN AS
786 /*----------------------------------------------------------------------------
787 || Created By : kkillams
788 || Created On : 08-11-2002
789 || Purpose : Function validates research candidature business validations
790 || Known limitations, enhancements or remarks :
791 || Change History :
792 || Who When What
793 ------------------------------------------------------------------------------*/
794 l_validation_success BOOLEAN;
795 l_message_name VARCHAR2(100);
796 l_boolean BOOLEAN;
797 BEGIN --validate_re
798
799 l_validation_success := TRUE;
800
801 l_message_name := NULL;
802 l_boolean:= NULL;
803 l_boolean := igs_re_val_ca.resp_val_ca_minsbmsn(p_person_id => p_person_id,
804 p_sca_course_cd => p_sca_re_rec.program_cd,
805 p_acai_admission_appl_number => p_sca_re_rec.adm_admission_appl_number,
806 p_acai_nominated_course_cd => p_sca_re_rec.adm_nominated_course_cd,
807 p_acai_sequence_number => p_sca_re_rec.adm_sequence_number,
808 p_min_submission_dt => p_sca_re_rec.re_min_submission_dt,
809 p_max_submission_dt => p_sca_re_rec.re_max_submission_dt,
810 p_attendance_percentage => p_sca_re_rec.re_attendance_percentage,
811 p_commencement_dt => NULL,
812 p_message_name => l_message_name,
813 p_legacy => 'Y');
814 IF l_message_name IS NOT NULL THEN
815 l_validation_success := FALSE;
816 END IF;
817
818 l_message_name := NULL;
819 l_boolean:= NULL;
820 l_boolean := igs_re_val_ca.resp_val_ca_maxsbmsn(p_person_id => p_person_id,
821 p_sca_course_cd => p_sca_re_rec.program_cd,
822 p_acai_admission_appl_number => p_sca_re_rec.adm_admission_appl_number,
823 p_acai_nominated_course_cd => p_sca_re_rec.adm_nominated_course_cd,
824 p_acai_sequence_number => p_sca_re_rec.adm_sequence_number,
825 p_min_submission_dt => p_sca_re_rec.re_min_submission_dt,
826 p_max_submission_dt => p_sca_re_rec.re_max_submission_dt,
827 p_attendance_percentage => p_sca_re_rec.re_attendance_percentage,
828 p_commencement_dt => NULL,
829 p_message_name => l_message_name,
830 p_legacy => 'Y');
831 IF l_message_name IS NOT NULL THEN
832 l_validation_success := FALSE;
833 END IF;
834
835 l_message_name := NULL;
836 l_boolean:= NULL;
837 l_boolean := igs_re_val_ca.resp_val_ca_topic (p_person_id => p_person_id,
838 p_sca_course_cd => p_sca_re_rec.program_cd,
839 p_acai_admission_appl_number => p_sca_re_rec.adm_admission_appl_number,
840 p_acai_nominated_course_cd => p_sca_re_rec.adm_nominated_course_cd,
841 p_acai_sequence_number => p_sca_re_rec.adm_sequence_number,
842 p_research_topic => p_sca_re_rec.re_research_topic,
843 p_message_name => l_message_name,
844 p_legacy => 'Y');
845 IF l_message_name IS NOT NULL THEN
846 l_validation_success := FALSE;
847 END IF;
848
849 RETURN l_validation_success;
850 END validate_re;
851
852 PROCEDURE create_spa(
853 p_api_version IN NUMBER,
854 p_init_msg_list IN VARCHAR2,
855 p_commit IN VARCHAR2,
856 p_validation_level IN NUMBER,
857 p_sca_re_rec IN SCA_RE_REC_TYPE,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_count OUT NOCOPY NUMBER,
860 x_msg_data OUT NOCOPY VARCHAR2) AS
861 /*----------------------------------------------------------------------------
862 || Created By : kkillams
863 || Created On : 08-11-2002
864 || Purpose :
865 || Known limitations, enhancements or remarks :
866 || Change History :
867 || Who When What
868 || kkillams 13-12-2002 Added if clause to check catalog_cal_alternate_code value
869 || before calling the igs_ge_gen_003.get_calendar_instance procedure
870 || w.r.t. bug 2708628.
871 || kkillams 19-12-2002 Added new validation before processing the candidature record,
872 || which checks the context program is a research program or not and
873 || any research interface columns having values or not w.r.t. bug 2715207.
874 || kkillams 27-12-2002 Modified default setting of KEY PROGRAM attribute,
875 || if key program is null then set the key program to 'N'
876 || w.r.t. bug no: 2708532.
877 || bdeviset 16-DEC-2004 Passing the value of future dated transfer flag as 'N' when
878 || the IGS_EN_STDNT_PS_ATT.INSERT_ROW is called.Bug# 4071165
879 ------------------------------------------------------------------------------*/
880
881 l_api_name CONSTANT VARCHAR2(30) := 'create_spa';
882 l_api_version CONSTANT NUMBER := 1.0;
883
884 --Cursor to get the research details for a program type
885 CURSOR cur_re_prg(p_prg_type igs_ps_type.course_type%TYPE) IS
886 SELECT research_type_ind FROM igs_ps_type
887 WHERE course_type = p_prg_type;
888 --Cursor is to get the sequence number for the candidature.
889 CURSOR cur_re_seq IS SELECT igs_re_candidature_seq_num_s.NEXTVAL FROM DUAL;
890
891 --local variables for flags
892 l_err_derive_flag VARCHAR2(1);
893 l_execution_flag1 VARCHAR2(1);
894 l_execution_flag2 VARCHAR2(1);
895 l_err_flag VARCHAR2(1);
896
897 --local variable to hold derived values.
898 l_career_flag VARCHAR2(1);
899 l_person_id igs_pe_person.person_id%TYPE;
900 l_course_type igs_ps_ver_all.course_type%TYPE;
901 l_coo_id igs_ps_ofr_opt_all.coo_id%TYPE;
902 l_course_att_status igs_en_stdnt_ps_att_all.course_attempt_status%TYPE;
903 l_igs_pr_class_std_id igs_en_stdnt_ps_att_all.igs_pr_class_std_id%TYPE;
904 l_catalog_cal_type igs_en_stdnt_ps_att_all.catalog_cal_type%TYPE;
905 l_catalog_seq_num igs_en_stdnt_ps_att_all.catalog_seq_num%TYPE;
906 l_dropped_by igs_en_stdnt_ps_att_all.dropped_by%TYPE;
907 l_catalog_start_dt DATE;
908 l_catalog_end_dt DATE;
909 l_sequence_number igs_re_candidature_all.sequence_number%TYPE;
910 l_status VARCHAR2(20);
911 l_sca_re_rec SCA_RE_REC_TYPE;
912 l_request_id igs_en_stdnt_ps_att_all.request_id%TYPE;
913 l_program_id igs_en_stdnt_ps_att_all.program_id%TYPE;
914 l_program_application_id igs_en_stdnt_ps_att_all.program_application_id%TYPE;
915 l_program_update_date igs_en_stdnt_ps_att_all.program_update_date%TYPE;
916 l_research_type_ind igs_ps_type.research_type_ind%TYPE;
917
918 BEGIN --Begin of create_spa
919
920 --Save point for whole procedure.
921 SAVEPOINT create_spa;
922
923 -- Check for the Compatible API call
924 IF NOT FND_API.COMPATIBLE_API_CALL( l_api_version,
925 p_api_version,
926 l_api_name,
927 g_pkg_name) THEN
928
929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
930 END IF;
931
932 -- If the calling program has passed the parameter for initializing the message list
933 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
934 FND_MSG_PUB.Initialize;
935 END IF;
936
937 --Assign the parameter record type to local record type.
938 l_sca_re_rec := p_sca_re_rec;
939
940 -- Set the return status to success
941 x_return_status := FND_API.G_RET_STS_SUCCESS;
942
943 --Derive the concurrent program who column values.
944 l_request_id := fnd_global.conc_request_id;
945 l_program_id := fnd_global.conc_program_id;
946 l_program_application_id := fnd_global.prog_appl_id;
947 IF (l_request_id = -1) THEN
948 l_request_id := NULL;
949 l_program_id := NULL;
950 l_program_application_id := NULL;
951 l_program_update_date := NULL;
952 ELSE
953 l_PROGRAM_UPDATE_DATE := SYSDATE;
954 END IF;
955
956 --Set the default values if no value is passed.
957 IF l_sca_re_rec.student_confirmed_ind IS NULL THEN
958 l_sca_re_rec.student_confirmed_ind := 'Y';
959 END IF;
960 IF l_sca_re_rec.key_program IS NULL THEN
961 l_sca_re_rec.key_program:='N';
962 END IF;
963 IF l_sca_re_rec.provisional_ind IS NULL THEN
964 l_sca_re_rec.provisional_ind:='N';
965 END IF;
966 IF l_sca_re_rec.rule_check_ind IS NULL THEN
967 l_sca_re_rec.rule_check_ind:='Y';
968 END IF;
969 IF l_sca_re_rec.waive_option_check_ind IS NULL THEN
970 l_sca_re_rec.waive_option_check_ind := 'N';
971 END IF;
972 IF l_sca_re_rec.publish_outcomes_ind IS NULL THEN
973 l_sca_re_rec.publish_outcomes_ind:= 'Y';
974 END IF;
975 IF l_sca_re_rec.course_rqrmnt_complete_ind IS NULL THEN
976 l_sca_re_rec.course_rqrmnt_complete_ind:= 'N';
977 END IF;
978 IF l_sca_re_rec.advanced_standing_ind IS NULL THEN
979 l_sca_re_rec.advanced_standing_ind:= 'N';
980 END IF;
981 IF l_sca_re_rec.self_help_group_ind IS NULL THEN
982 l_sca_re_rec.self_help_group_ind := 'N';
983 END IF;
984 IF l_sca_re_rec.manual_ovr_cmpl_dt_ind IS NULL THEN
985 l_sca_re_rec.manual_ovr_cmpl_dt_ind:= 'N';
986 END IF;
987
988 --Check career model flag is checked or not.
989 l_career_flag:= NVL(fnd_profile.value('CAREER_MODEL_ENABLED'),'N');
990
991 --Set the error flag.
992 l_err_flag := 'N';
993
994 --Validate parameter function is to validate the function input parameters.
995 IF NOT validate_parameters(l_sca_re_rec,l_career_flag) THEN
996 l_err_flag := 'Y';
997 END IF;
998
999
1000 IF l_err_flag <> 'Y' THEN
1001 ---Derive the required values, to do futher validations.
1002 --deriving the person identifier value.
1003 l_person_id :=igs_ge_gen_003.get_person_id(l_sca_re_rec.person_number);
1004 IF l_person_id IS NULL THEN
1005 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_PERSON_NUMBER');
1006 FND_MSG_PUB.ADD;
1007 l_err_flag := 'Y';
1008 END IF;
1009
1010 --deriving the course type.
1011 l_course_type:=igs_en_gen_legacy.get_sca_prog_type(l_sca_re_rec.program_cd,l_sca_re_rec.version_number);
1012 IF l_course_type IS NULL THEN
1013 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PROG_TYPE_NOT_FOUND');
1014 FND_MSG_PUB.ADD;
1015 l_err_flag := 'Y';
1016 END IF;
1017
1018 --deriving the program offering option identifier.
1019 l_coo_id:=igs_en_gen_legacy.get_coo_id(p_course_cd =>l_sca_re_rec.program_cd,
1020 p_version_number =>l_sca_re_rec.version_number,
1021 p_cal_type =>l_sca_re_rec.cal_type,
1022 p_location_cd =>l_sca_re_rec.location_cd,
1023 p_attendance_mode =>l_sca_re_rec.attendance_mode,
1024 p_attendance_type =>l_sca_re_rec.attendance_type);
1025 IF l_coo_id IS NULL THEN
1026 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PS_OFR_OPT_NOT_FOUND');
1027 FND_MSG_PUB.ADD;
1028 l_err_flag := 'Y';
1029 END IF;
1030
1031 --deriving the program attempt status for the current student program attempt.
1032 l_course_att_status:=igs_en_gen_legacy.get_course_att_status(p_person_id =>l_person_id,
1033 p_course_cd =>l_sca_re_rec.program_cd,
1034 p_student_confirmed_ind =>l_sca_re_rec.student_confirmed_ind,
1035 p_discontinued_dt =>l_sca_re_rec.discontinued_dt,
1036 p_lapsed_dt =>l_sca_re_rec.lapsed_dt,
1037 p_course_rqrmnt_complete_ind =>l_sca_re_rec.course_rqrmnt_complete_ind,
1038 p_primary_pg_type =>l_sca_re_rec.primary_program_type,
1039 p_primary_prog_type_source =>l_sca_re_rec.primary_prog_type_source,
1040 p_course_type =>l_course_type,
1041 p_career_flag =>l_career_flag);
1042 IF l_course_att_status IS NULL THEN
1043 FND_MESSAGE.SET_NAME('IGS','IGS_EN_COURSE_ATT_NOT_FOUND');
1044 FND_MSG_PUB.ADD;
1045 l_err_flag := 'Y';
1046 END IF;
1047 --deriving the class standing identifier.
1048 IF l_sca_re_rec.class_standing_override IS NOT NULL THEN
1049 l_igs_pr_class_std_id:=igs_en_gen_legacy.get_class_std_id(p_class_standing =>l_sca_re_rec.class_standing_override );
1050 IF l_igs_pr_class_std_id IS NULL THEN
1051 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CLASS_STD_ID_NOT_FOUND');
1052 FND_MSG_PUB.ADD;
1053 l_err_flag := 'Y';
1054 END IF;
1055 END IF;
1056
1057 --deriving the staff person to populate the dropped column.
1058 l_dropped_by:=igs_en_gen_legacy.get_sca_dropped_by();
1059 IF l_dropped_by IS NULL THEN
1060 FND_MESSAGE.SET_NAME('IGS','IGS_EN_DROPPED_BY_NOT_FOUND');
1061 FND_MSG_PUB.ADD;
1062 END IF;
1063 IF l_sca_re_rec.catalog_cal_alternate_code IS NOT NULL THEN
1064 --deriving the catalog calendar details for a given catalog alternate code.
1065 igs_ge_gen_003.get_calendar_instance(p_alternate_cd =>l_sca_re_rec.catalog_cal_alternate_code,
1066 p_s_cal_category =>'''LOAD'',''ACADEMIC''',
1067 p_cal_type =>l_catalog_cal_type,
1068 p_ci_sequence_number =>l_catalog_seq_num,
1069 p_start_dt =>l_catalog_start_dt,
1070 p_end_dt =>l_catalog_end_dt,
1071 p_return_status =>l_status);
1072 IF l_status = 'MULTIPLE' THEN
1073 FND_MESSAGE.SET_NAME('IGS','IGS_EN_MORE_CAL_FOUND');
1074 FND_MSG_PUB.ADD;
1075 l_err_flag := 'Y';
1076 ELSIF l_status = 'INVALID' THEN
1077 FND_MESSAGE.SET_NAME('IGS','IGS_EN_NO_ACAD_TERM_CAL');
1078 FND_MSG_PUB.ADD;
1079 l_err_flag := 'Y';
1080 END IF;
1081 END IF;
1082 END IF; --l_err_flag
1083
1084 --If no error found in the above validation then only do further validation else terminate process.
1085 IF l_err_flag <> 'Y' THEN
1086 l_execution_flag1 := NULL;
1087 l_execution_flag2 := NULL;
1088 --Validates the student program attempt database constarits.
1089 --Function will returns S,E,P
1090 l_execution_flag1:=validate_sca_db_cons (p_person_id => l_person_id,
1091 p_sca_re_rec => l_sca_re_rec);
1092 --If all validation passed then call the validate_sca which will validates the all business rules
1093 IF l_execution_flag1 = 'S' THEN
1094 IF NOT validate_sca(p_person_id => l_person_id,
1095 p_course_att_status => l_course_att_status,
1096 p_course_type => l_course_type,
1097 p_career_flag => l_career_flag,
1098 p_sca_re_rec => l_sca_re_rec) THEN
1099 l_execution_flag1 := 'E';
1100 END IF;
1101 END IF;
1102
1103 --Inserting the Student program attempt
1104 IF l_execution_flag1 ='S' THEN
1105 --Note:Following columns are not populating as they are obsoleted in the data model
1106 -- derived_att_mode, derived_att_type, logical_delete_dt, derived_completion_yr,
1107 -- derived_completiod_perd, override_time_limitation
1108 -- progression_status and last_date_of_attendance column would be derived while improting the unit atttempt details.
1109 INSERT INTO igs_en_stdnt_ps_att_all(
1110 person_id,
1111 course_cd,
1112 version_number,
1113 cal_type,
1114 location_cd,
1115 attendance_mode,
1116 attendance_type,
1117 coo_id,
1118 student_confirmed_ind,
1119 commencement_dt,
1120 course_attempt_status,
1121 progression_status,
1122 derived_att_type,
1123 derived_att_mode,
1124 provisional_ind,
1125 discontinued_dt,
1126 discontinuation_reason_cd,
1127 lapsed_dt,
1128 funding_source,
1129 exam_location_cd,
1130 derived_completion_yr,
1131 derived_completion_perd,
1132 nominated_completion_yr,
1133 nominated_completion_perd,
1134 rule_check_ind,
1135 waive_option_check_ind,
1136 last_rule_check_dt,
1137 publish_outcomes_ind,
1138 course_rqrmnt_complete_ind,
1139 course_rqrmnts_complete_dt,
1140 s_completed_source_type,
1141 override_time_limitation,
1142 advanced_standing_ind,
1143 fee_cat,
1144 correspondence_cat,
1145 self_help_group_ind,
1146 logical_delete_dt,
1147 adm_admission_appl_number,
1148 adm_nominated_course_cd,
1149 adm_sequence_number,
1150 igs_pr_class_std_id,
1151 last_date_of_attendance,
1152 dropped_by,
1153 primary_program_type,
1154 primary_prog_type_source,
1155 catalog_cal_type,
1156 catalog_seq_num,
1157 key_program,
1158 override_cmpl_dt,
1159 manual_ovr_cmpl_dt_ind,
1160 attribute_category,
1161 attribute1,
1162 attribute2,
1163 attribute3,
1164 attribute4,
1165 attribute5,
1166 attribute6,
1167 attribute7,
1168 attribute8,
1169 attribute9,
1170 attribute10,
1171 attribute11,
1172 attribute12,
1173 attribute13,
1174 attribute14,
1175 attribute15,
1176 attribute16,
1177 attribute17,
1178 attribute18,
1179 attribute19,
1180 attribute20,
1181 created_by,
1182 creation_date,
1183 last_updated_by,
1184 last_update_date,
1185 last_update_login,
1186 request_id,
1187 program_application_id,
1188 program_id,
1189 program_update_date,
1190 org_id,
1191 future_dated_trans_flag)
1192 VALUES ( l_person_id,
1193 l_sca_re_rec.program_cd,
1194 l_sca_re_rec.version_number,
1195 l_sca_re_rec.cal_type,
1196 l_sca_re_rec.location_cd,
1197 l_sca_re_rec.attendance_mode,
1198 l_sca_re_rec.attendance_type,
1199 l_coo_id,
1200 l_sca_re_rec.student_confirmed_ind,
1201 l_sca_re_rec.commencement_dt,
1202 l_course_att_status,
1203 NULL,
1204 NULL,
1205 NULL,
1206 l_sca_re_rec.provisional_ind,
1207 l_sca_re_rec.discontinued_dt,
1208 l_sca_re_rec.discontinuation_reason_cd,
1209 l_sca_re_rec.lapsed_dt,
1210 l_sca_re_rec.funding_source,
1211 l_sca_re_rec.exam_location_cd,
1212 NULL,
1213 NULL,
1214 l_sca_re_rec.nominated_completion_yr,
1215 l_sca_re_rec.nominated_completion_perd,
1216 l_sca_re_rec.rule_check_ind,
1217 l_sca_re_rec.waive_option_check_ind,
1218 l_sca_re_rec.last_rule_check_dt,
1219 l_sca_re_rec.publish_outcomes_ind,
1220 l_sca_re_rec.course_rqrmnt_complete_ind,
1221 l_sca_re_rec.course_rqrmnts_complete_dt,
1222 l_sca_re_rec.s_completed_source_type,
1223 NULL,
1224 l_sca_re_rec.advanced_standing_ind,
1225 l_sca_re_rec.fee_cat,
1226 l_sca_re_rec.correspondence_cat,
1227 l_sca_re_rec.self_help_group_ind,
1228 NULL,
1229 l_sca_re_rec.adm_admission_appl_number,
1230 l_sca_re_rec.adm_nominated_course_cd,
1231 l_sca_re_rec.adm_sequence_number,
1232 l_igs_pr_class_std_id,
1233 NULL,
1234 l_dropped_by,
1235 l_sca_re_rec.primary_program_type,
1236 l_sca_re_rec.primary_prog_type_source,
1237 l_catalog_cal_type,
1238 l_catalog_seq_num,
1239 l_sca_re_rec.key_program,
1240 l_sca_re_rec.override_cmpl_dt,
1241 l_sca_re_rec.manual_ovr_cmpl_dt_ind,
1242 l_sca_re_rec.attribute_category,
1243 l_sca_re_rec.attribute1,
1244 l_sca_re_rec.attribute2,
1245 l_sca_re_rec.attribute3,
1246 l_sca_re_rec.attribute4,
1247 l_sca_re_rec.attribute5,
1248 l_sca_re_rec.attribute6,
1249 l_sca_re_rec.attribute7,
1250 l_sca_re_rec.attribute8,
1251 l_sca_re_rec.attribute9,
1252 l_sca_re_rec.attribute10,
1253 l_sca_re_rec.attribute11,
1254 l_sca_re_rec.attribute12,
1255 l_sca_re_rec.attribute13,
1256 l_sca_re_rec.attribute14,
1257 l_sca_re_rec.attribute15,
1258 l_sca_re_rec.attribute16,
1259 l_sca_re_rec.attribute17,
1260 l_sca_re_rec.attribute18,
1261 l_sca_re_rec.attribute19,
1262 l_sca_re_rec.attribute20,
1263 NVL(FND_GLOBAL.USER_ID,-1),
1264 SYSDATE,
1265 NVL(FND_GLOBAL.USER_ID,-1),
1266 SYSDATE,
1267 NVL(FND_GLOBAL.LOGIN_ID,-1),
1268 l_request_id,
1269 l_program_application_id,
1270 l_program_id,
1271 l_program_update_date,
1272 igs_ge_gen_003.get_org_id,
1273 'N');
1274 END IF; ---l_execution_flag1 ='S'
1275 --Check the context program is type research candidature.
1276 l_research_type_ind := 'N';
1277 OPEN cur_re_prg(l_course_type);
1278 FETCH cur_re_prg INTO l_research_type_ind;
1279 CLOSE cur_re_prg;
1280
1281 IF l_execution_flag1 IN ('S','P') AND
1282 ((l_sca_re_rec.re_attendance_percentage IS NOT NULL) OR (l_sca_re_rec.re_govt_type_of_activity_cd IS NOT NULL) OR
1283 (l_sca_re_rec.re_max_submission_dt IS NOT NULL) OR (l_sca_re_rec.re_min_submission_dt IS NOT NULL) OR
1284 (l_sca_re_rec.re_research_topic IS NOT NULL) OR (l_sca_re_rec.re_industry_links IS NOT NULL) OR (l_research_type_ind = 'Y')
1285 ) THEN
1286 --Validates the student program attempt database constarits.
1287 l_execution_flag2:=validate_re_db_cons (p_person_id => l_person_id,
1288 p_sca_re_rec => l_sca_re_rec);
1289 IF l_execution_flag2 = 'S' THEN
1290 IF NOT validate_re(p_person_id => l_person_id,
1291 p_sca_re_rec => l_sca_re_rec) THEN
1292 l_execution_flag2 := 'E';
1293 END IF;
1294 END IF; --l_execution_flag2 ='S'
1295 --Inserting the Research Candidature.
1296 IF l_execution_flag2 ='S' THEN
1297 --Get the sequence number for research candidature.
1298 l_sequence_number:= NULL;
1299 OPEN cur_re_seq;
1300 FETCH cur_re_seq INTO l_sequence_number;
1301 CLOSE cur_re_seq;
1302 INSERT INTO igs_re_candidature_all(person_id,
1303 sequence_number,
1304 sca_course_cd,
1305 acai_admission_appl_number,
1306 acai_nominated_course_cd,
1307 acai_sequence_number,
1308 attendance_percentage,
1309 govt_type_of_activity_cd,
1310 max_submission_dt,
1311 min_submission_dt,
1312 research_topic,
1313 industry_links,
1314 created_by,
1315 creation_date,
1316 last_updated_by,
1317 last_update_date,
1318 last_update_login,
1319 request_id,
1320 program_application_id,
1321 program_id,
1322 program_update_date,
1323 org_id) VALUES(
1324 l_person_id,
1325 l_sequence_number,
1326 l_sca_re_rec.program_cd,
1327 l_sca_re_rec.adm_admission_appl_number,
1328 l_sca_re_rec.adm_nominated_course_cd,
1329 l_sca_re_rec.adm_sequence_number,
1330 l_sca_re_rec.re_attendance_percentage,
1331 l_sca_re_rec.re_govt_type_of_activity_cd,
1332 l_sca_re_rec.re_max_submission_dt,
1333 l_sca_re_rec.re_min_submission_dt,
1334 l_sca_re_rec.re_research_topic,
1335 l_sca_re_rec.re_industry_links,
1336 NVL(FND_GLOBAL.USER_ID,-1),
1337 SYSDATE,
1338 NVL(FND_GLOBAL.USER_ID,-1),
1339 SYSDATE,
1340 NVL(FND_GLOBAL.LOGIN_ID,-1),
1341 l_request_id,
1342 l_program_application_id,
1343 l_program_id,
1344 l_program_update_date,
1345 igs_ge_gen_003.get_org_id);
1346 END IF; --l_execution_flag2 ='S'
1347 END IF; --l_execution_flag1 IN ('S','P')
1348 END IF; ---l_err_flag <> 'Y'
1349 IF ((l_err_flag ='Y') OR
1350 (l_execution_flag1 ='E') OR
1351 (l_execution_flag2 ='E')) THEN
1352 ROLLBACK TO create_spa;
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 ELSIF ((l_execution_flag1 = 'P') AND (l_execution_flag2 IS NULL)) OR
1355 ((l_execution_flag1 = 'P') AND (l_execution_flag2 ='P')) THEN
1356 x_return_status := 'W';
1357 ELSE
1358 IF FND_API.TO_BOOLEAN(p_commit) THEN
1359 COMMIT WORK;
1360 END IF;
1361 x_return_status := FND_API.G_RET_STS_SUCCESS;
1362 END IF;
1363 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1364 p_data => x_msg_data);
1365 EXCEPTION
1366 WHEN FND_API.G_EXC_ERROR THEN
1367 ROLLBACK TO create_spa;
1368 x_return_status := FND_API.G_RET_STS_ERROR;
1369 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1370 p_data => x_msg_data);
1371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1372 ROLLBACK TO create_spa;
1373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1374 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
1375 p_data => x_msg_data);
1376 WHEN OTHERS THEN
1377 ROLLBACK TO create_spa;
1378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1379 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1380 FND_MSG_PUB.ADD_Exc_Msg(g_pkg_name,
1381 l_api_name);
1382 END IF;
1383 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1384 p_data => x_msg_data);
1385 END create_spa;
1386 END igs_en_spa_lgcy_pub;
[Home] [Help]