1 PACKAGE BODY IGS_EN_SUA_LGCY_PUB AS
2 /* $Header: IGSENA4B.pls 120.15 2006/08/11 09:35:31 smaddali ship $ */
3
4
5 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_EN_SUA_LGCY_PUB';
6 g_sua_status VARCHAR2(10) := 'VALID';
7 g_wlst_pri_pref_exists BOOLEAN :=FALSE;
8
9 PROCEDURE validate_parameters(p_sua_dtls_rec IN sua_dtls_rec_type) AS
10 /*------------------------------------------------------------------
11 Created By : SVENKATA
12 Date Created By : 12-NOV-02
13 Purpose : This routine validates the parameters that are being passed to the insert API.
14 It Checks for Mandatory Parameters and Check constraints. While making a call to the
15 Check_constraints routine of the TBH , it is possible to encounter an Exception if the
16 validation fails.So, calls to check_constraint routines are handled gracefully within
17 blocks.If an error is encountered , the Check_cons routine adds the generic message
18 IGS_GE_INVALID_VALUE to the stack , and then raises the Exception. So , in the Insert
19 API Exception section, generic message 'IGS_GE_INVALID_VALUE' is deleted from the Stack
20 and a more specific message is added , if required.
21 Known limitations,
22 enhancements,
23 remarks :
24 Change History
25 Who When What
26 ------------------------------------------------------------------*/
27
28 -- Cursor to fetch parameter for querying label GRADE
29 CURSOR get_grade IS
30 SELECT message_text
31 FROM fnd_new_messages
32 WHERE message_name = 'IGS_EN_GRADE' ;
33
34 -- Declare local variables and initialise parameters.
35 l_msg_count NUMBER ;
36 l_msg_data VARCHAR2(2000);
37 l_grade_msg FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
38
39 BEGIN
40
41 -- Person Number is Mandatory
42 IF p_sua_dtls_rec.person_number IS NULL THEN
43 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PER_NUM_NULL');
44 FND_MSG_PUB.Add;
45 g_sua_status := 'INVALID';
46 END IF;
47
48 -- Program Code is Mandatory
49 IF p_sua_dtls_rec.program_cd IS NULL THEN
50 FND_MESSAGE.SET_NAME('IGS','IGS_EN_PRGM_CD_NULL');
51 FND_MSG_PUB.Add;
52 g_sua_status := 'INVALID';
53 END IF;
54
55 -- Unit Code is Mandatory
56 IF p_sua_dtls_rec.unit_cd IS NULL THEN
57 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNITCD_SPECIFIED');
58 FND_MSG_PUB.Add;
59 g_sua_status := 'INVALID';
60 END IF;
61
62 -- Calendar Alternate Code is Mandatory
63 IF p_sua_dtls_rec.teach_calendar_alternate_code IS NULL THEN
64 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CAL_TYP_NULL');
65 FND_MSG_PUB.Add;
66 g_sua_status := 'INVALID';
67 END IF;
68
69 -- Location Code is Mandatory
70 IF p_sua_dtls_rec.location_cd IS NULL THEN
71 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LOC_CD_NULL');
72 FND_MSG_PUB.Add;
73 g_sua_status := 'INVALID';
74 END IF;
75
76 -- Unit Class is Mandatory
77 IF p_sua_dtls_rec.unit_class IS NULL THEN
78 FND_MESSAGE.SET_NAME('IGS','IGS_EN_UNT_CLS_NULL');
79 FND_MSG_PUB.Add;
80 g_sua_status := 'INVALID';
81 END IF;
82
83 -- added by vijrajag for bug # 4235458
84 -- cannot override credit points when unit is audited
85 IF p_sua_dtls_rec.no_assessment_ind = 'Y' THEN
86 IF p_sua_dtls_rec.override_enrolled_cp IS NOT NULL OR
87 p_sua_dtls_rec.override_achievable_cp IS NOT NULL THEN
88 FND_MESSAGE.SET_NAME('IGS','IGS_EN_AUDIT_NO_OVR_CP');
89 FND_MSG_PUB.ADD;
90 g_sua_status := 'INVALID';
91 END IF;
92 END IF;
93
94 -- If Program Code is specified , it should be in Upper Case.
95 IF p_sua_dtls_rec.program_cd IS NOT NULL THEN
96 BEGIN
97 igs_en_su_attempt_pkg.check_constraints(
98 column_name => 'COURSE_CD' ,
99 column_value => p_sua_dtls_rec.program_cd );
100
101 EXCEPTION
102 WHEN OTHERS THEN
103 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
104 p_data => l_msg_data);
105 FND_MSG_PUB.DELETE_MSG(l_msg_count);
106 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PRGM_CD_UCASE');
107 FND_MSG_PUB.ADD;
108 g_sua_status := 'INVALID';
109 END;
110 END IF;
111
112 -- If Unit Code is specified , it should be in Upper Case.
113 IF p_sua_dtls_rec.unit_cd IS NOT NULL THEN
114 BEGIN
115 igs_en_su_attempt_pkg.check_constraints(
116 column_name => 'UNIT_CD' ,
117 column_value => p_sua_dtls_rec.unit_cd );
118 EXCEPTION
119 WHEN OTHERS THEN
120 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
121 p_data => l_msg_data);
122 FND_MSG_PUB.DELETE_MSG(l_msg_count);
123 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_CD_UCASE');
124 FND_MSG_PUB.ADD;
125 g_sua_status := 'INVALID';
126 END;
127 END IF;
128
129 -- If Location Code is specified , it should be in Upper Case.
130 IF p_sua_dtls_rec.location_cd IS NOT NULL THEN
131 BEGIN
132 igs_en_su_attempt_pkg.check_constraints(
133 column_name => 'LOCATION_CD' ,
134 column_value => p_sua_dtls_rec.location_cd );
135 EXCEPTION
136 WHEN OTHERS THEN
137 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
138 p_data => l_msg_data);
139 FND_MSG_PUB.DELETE_MSG(l_msg_count);
140 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_LOC_CD_UCASE');
141 FND_MSG_PUB.ADD;
142 g_sua_status := 'INVALID';
143 END;
144 END IF;
145
146 -- If Unit class is specified , it should be in Upper Case.
147 IF p_sua_dtls_rec.unit_class IS NOT NULL THEN
148 BEGIN
149 igs_en_su_attempt_pkg.check_constraints(
150 column_name => 'UNIT_CLASS' ,
151 column_value => p_sua_dtls_rec.unit_class );
152 EXCEPTION
153 WHEN OTHERS THEN
154 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
155 p_data => l_msg_data);
156 FND_MSG_PUB.DELETE_MSG(l_msg_count);
157 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_CLS_UCASE');
158 FND_MSG_PUB.ADD;
159 g_sua_status := 'INVALID';
160 END;
161 END IF;
162
163 IF p_sua_dtls_rec.dropped_ind IS NOT NULL AND p_sua_dtls_rec.dropped_ind NOT IN ('Y' , 'N') THEN
164 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_DROP_IND_INV');
165 FND_MSG_PUB.ADD;
166 g_sua_status := 'INVALID';
167 END IF ;
168
169 -- If Discontinuation Reason Code is specified , it should be in Upper Case.
170 IF p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL THEN
171 BEGIN
172 igs_en_su_attempt_pkg.check_constraints(
173 column_name => 'DCNT_REASON_CD' ,
174 column_value => p_sua_dtls_rec.dcnt_reason_cd );
175 EXCEPTION
176 WHEN OTHERS THEN
177 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
178 p_data => l_msg_data);
179 FND_MSG_PUB.DELETE_MSG(l_msg_count);
180 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_DISC_RSN_INV');
181 FND_MSG_PUB.ADD;
182 g_sua_status := 'INVALID';
183 END;
184 END IF;
185
186 -- If Assesment Indicator is specified , Permitted values are 'Y' or 'N'.
187 IF p_sua_dtls_rec.no_assessment_ind IS NOT NULL THEN
188 BEGIN
189 igs_en_su_attempt_pkg.check_constraints(
190 column_name => 'NO_ASSESSMENT_IND' ,
191 column_value => p_sua_dtls_rec.no_assessment_ind );
192 EXCEPTION
193 WHEN OTHERS THEN
194 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
195 p_data => l_msg_data);
196 FND_MSG_PUB.DELETE_MSG(l_msg_count);
197 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_NO_ASSMNT_INV');
198 FND_MSG_PUB.ADD;
199 g_sua_status := 'INVALID';
200 END;
201 END IF;
202
203 -- If Override Enrolled Credit Points are specified, it must be between 0 and 999.999.
204 IF p_sua_dtls_rec.override_enrolled_cp IS NOT NULL THEN
205 BEGIN
206
207 igs_en_su_attempt_pkg.check_constraints(
208 column_name => 'OVERRIDE_ENROLLED_CP' ,
209 column_value => igs_ge_number.to_cann(p_sua_dtls_rec.override_enrolled_cp ));
210 EXCEPTION
211 WHEN OTHERS THEN
212 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
213 p_data => l_msg_data);
214 FND_MSG_PUB.DELETE_MSG(l_msg_count);
215 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OVR_ENR_CP_INV' );
216 FND_MSG_PUB.ADD;
217 g_sua_status := 'INVALID';
218 END;
219 END IF;
220
221 -- If Override Enrolled Achievable Points are specified, it must be between 0 and 999.999.
222 IF p_sua_dtls_rec.override_achievable_cp IS NOT NULL THEN
223 BEGIN
224 igs_en_su_attempt_pkg.check_constraints(
225 column_name => 'OVERRIDE_ACHIEVABLE_CP' ,
226 column_value => igs_ge_number.to_cann(p_sua_dtls_rec.override_achievable_cp ));
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
231 p_data => l_msg_data);
232 FND_MSG_PUB.DELETE_MSG(l_msg_count);
233 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OVR_ACH_CP_INV');
234 FND_MSG_PUB.ADD;
235 g_sua_status := 'INVALID';
236 END;
237 END IF;
238
239 -- If Grading Schema Code is specified , it should be in Upper Case.
240 IF p_sua_dtls_rec.grading_schema_code IS NOT NULL THEN
241 BEGIN
242
243 igs_as_su_stmptout_pkg.check_constraints(
244 column_name => 'GRADING_SCHEMA_CD' ,
245 column_value => p_sua_dtls_rec.grading_schema_code );
246 EXCEPTION
247 WHEN OTHERS THEN
248 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
249 p_data => l_msg_data);
250 FND_MSG_PUB.DELETE_MSG(l_msg_count);
251 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_GRD_SCH');
252 FND_MSG_PUB.ADD;
253 g_sua_status := 'INVALID';
254 END;
255 END IF;
256
257
258 -- If Student Career Transcript is specified , Permitted values are 'Y' or 'N'.
259 IF p_sua_dtls_rec.student_career_transcript IS NOT NULL AND p_sua_dtls_rec.student_career_transcript NOT IN ('Y' , 'N') THEN
260 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_CAR_TRNSCPT_INV');
261 FND_MSG_PUB.ADD;
262 g_sua_status := 'INVALID';
263 END IF ;
264
265 -- If Student Career statistics is specified , Permitted values are 'Y' or 'N'.
266 IF p_sua_dtls_rec.student_career_statistics IS NOT NULL AND p_sua_dtls_rec.student_career_statistics NOT IN ('Y' , 'N') THEN
267 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_CAR_STATS_INV');
268 FND_MSG_PUB.ADD;
269 g_sua_status := 'INVALID';
270 END IF ;
271
272 -- If Transfer Program Code is specified , it should be in Upper Case.
273 IF p_sua_dtls_rec.transfer_program_cd IS NOT NULL THEN
274 BEGIN
275 igs_ps_stdnt_trn_pkg.check_constraints(
276 column_name => 'TRANSFER_COURSE_CD' ,
277 column_value => p_sua_dtls_rec.transfer_program_cd);
278 EXCEPTION
279 WHEN OTHERS THEN
280 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
281 p_data => l_msg_data);
282 FND_MSG_PUB.DELETE_MSG(l_msg_count);
283 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_PRGM_CD_UCASE');
284 FND_MSG_PUB.ADD;
285 g_sua_status := 'INVALID';
286 END;
287 END IF;
288
289 -- If Marks is specified, it must be between 0 and 999.999.
290 IF p_sua_dtls_rec.mark IS NOT NULL THEN
291 BEGIN
292 igs_as_su_stmptout_pkg.check_constraints(
293 column_name => 'MARK' ,
294 column_value => igs_ge_number.to_cann(p_sua_dtls_rec.mark ));
295 EXCEPTION
296 WHEN OTHERS THEN
297 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
298 p_data => l_msg_data);
299 FND_MSG_PUB.DELETE_MSG(l_msg_count);
300 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_MARK_INV');
301 FND_MSG_PUB.ADD;
302 g_sua_status := 'INVALID';
303 END;
304 END IF;
305
306 -- If Grade is specified , it should be in Upper Case.
307 IF p_sua_dtls_rec.grade IS NOT NULL AND p_sua_dtls_rec.grade <> UPPER(p_sua_dtls_rec.grade) THEN
308 OPEN get_grade;
309 FETCH get_grade INTO l_grade_msg;
310 CLOSE get_grade ;
311
312 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_INV' );
313 FND_MESSAGE.SET_TOKEN('PARAM',l_grade_msg );
314 FND_MSG_PUB.ADD;
315 g_sua_status := 'INVALID';
316 END IF;
317
318 --
319 -- If any of the Descriptive Flex field columns have value , validate them .
320 IF (p_sua_dtls_rec.attribute_category IS NOT NULL OR p_sua_dtls_rec.attribute1 IS NOT NULL OR p_sua_dtls_rec.attribute2 IS NOT NULL OR
321 p_sua_dtls_rec.attribute3 IS NOT NULL OR p_sua_dtls_rec.attribute4 IS NOT NULL OR p_sua_dtls_rec.attribute5 IS NOT NULL OR
322 p_sua_dtls_rec.attribute6 IS NOT NULL OR p_sua_dtls_rec.attribute7 IS NOT NULL OR p_sua_dtls_rec.attribute8 IS NOT NULL OR
323 p_sua_dtls_rec.attribute9 IS NOT NULL OR p_sua_dtls_rec.attribute10 IS NOT NULL OR p_sua_dtls_rec.attribute11 IS NOT NULL OR
324 p_sua_dtls_rec.attribute12 IS NOT NULL OR p_sua_dtls_rec.attribute13 IS NOT NULL OR p_sua_dtls_rec.attribute14 IS NOT NULL OR
325 p_sua_dtls_rec.attribute15 IS NOT NULL OR p_sua_dtls_rec.attribute16 IS NOT NULL OR p_sua_dtls_rec.attribute17 IS NOT NULL OR
326 p_sua_dtls_rec.attribute18 IS NOT NULL OR p_sua_dtls_rec.attribute19 IS NOT NULL OR p_sua_dtls_rec.attribute20 IS NOT NULL )
327 THEN
328 IF NOT igs_ad_imp_018.validate_desc_flex (
329 p_attribute_category => p_sua_dtls_rec.attribute_category ,
330 p_attribute1 => p_sua_dtls_rec.attribute1 ,
331 p_attribute2 => p_sua_dtls_rec.attribute2 ,
332 p_attribute3 => p_sua_dtls_rec.attribute3 ,
333 p_attribute4 => p_sua_dtls_rec.attribute4 ,
334 p_attribute5 => p_sua_dtls_rec.attribute5 ,
335 p_attribute6 => p_sua_dtls_rec.attribute6 ,
336 p_attribute7 => p_sua_dtls_rec.attribute7 ,
337 p_attribute8 => p_sua_dtls_rec.attribute8 ,
338 p_attribute9 => p_sua_dtls_rec.attribute9 ,
339 p_attribute10 => p_sua_dtls_rec.attribute10 ,
340 p_attribute11 => p_sua_dtls_rec.attribute11 ,
341 p_attribute12 => p_sua_dtls_rec.attribute12 ,
342 p_attribute13 => p_sua_dtls_rec.attribute13 ,
343 p_attribute14 => p_sua_dtls_rec.attribute14 ,
344 p_attribute15 => p_sua_dtls_rec.attribute15 ,
345 p_attribute16 => p_sua_dtls_rec.attribute16 ,
346 p_attribute17 => p_sua_dtls_rec.attribute17 ,
347 p_attribute18 => p_sua_dtls_rec.attribute18 ,
348 p_attribute19 => p_sua_dtls_rec.attribute19 ,
349 p_attribute20 => p_sua_dtls_rec.attribute20 ,
350 p_desc_flex_name => 'IGS_EN_SU_ATMPT_FLEX'
351 ) THEN
352 FND_MESSAGE.SET_NAME( 'IGS','IGS_AD_INVALID_DESC_FLEX' );
353 FND_MSG_PUB.ADD;
354 g_sua_status := 'INVALID';
355 END IF ;
356 END IF;
357
358 -- If Outcome Grading Schema Code is specified , it should be in Upper Case.
359 IF p_sua_dtls_rec.outcome_grading_schema_code IS NOT NULL THEN
360 BEGIN
361 igs_as_su_stmptout_pkg.check_constraints(
362 column_name => 'GRADING_SCHEMA_CD' ,
363 column_value => p_sua_dtls_rec.outcome_grading_schema_code );
364 EXCEPTION
365 WHEN OTHERS THEN
366 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
367 p_data => l_msg_data);
368 FND_MSG_PUB.DELETE_MSG(l_msg_count);
369 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_GRD_SCH');
370 FND_MSG_PUB.ADD;
371 g_sua_status := 'INVALID';
372 END;
373 END IF;
374
375 RETURN ;
376
377 END validate_parameters;
378
379 PROCEDURE validate_db_cons( p_person_id IN NUMBER,
380 p_unit_version_number IN NUMBER,
381 p_uoo_id IN NUMBER ,
382 p_cal_type IN VARCHAR2 ,
383 p_ci_sequence_number IN NUMBER,
384 p_sua_dtls_rec IN sua_dtls_rec_type ) AS
385 /*-----------------------------------------------------------------------------
386 Created By : SVENKATA
387 Date Created By : 12-NOV-02
388 Purpose : This routine validates the DB constraints for the parameters. In cases where the parameters
389 are not Mandatory , a check is made to check if Atleast one column of the composite FK has
390 value. Incases when validations are done for Madatory columns , the above check is not done.
391 Failure of PK validation alone should return warning. In this case, processing should stop,
392 and a status of warning is return to the calling procedure. All other validations are done in
393 one shot.
394 Known limitations,
395 enhancements,
396 remarks :
397 Change History
398 Who When What
399 knaraset 19-Jun-2003 Added unique key validation igs_en_su_attempt_pkg.Get_Uk_For_Validation, as part of bug 2956146
400 kkillams 29-04-2003 Impacted object, due to change in the signature of the igs_en_su_attempt_pkg.get_pk_for_validation function
401 w.r.t. bug number 2829262
402 rvangala 02-OCT-2003 Added validation to check value of core_indicator, added as part of Prevent Dropping Core Units. Enh Bug# 3052432
403 amuthu 29-JUL-2004 Added validation for Administrative unit status, to call
404 the IGS_AD_ADM_UNIT_STAT_PKG.Get_PK_For_Validation, to
405 prevent invalid values from getting saved
406 -----------------------------------------------------------------------------*/
407 l_indicator BOOLEAN := false;
408 BEGIN
409 -- Primary Key validation
410 IF igs_en_su_attempt_pkg.get_pk_for_validation (
411 p_person_id ,
412 p_sua_dtls_rec.program_cd,
413 p_uoo_id) THEN
414 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_ATMPT_EXTS');
415 FND_MSG_PUB.ADD;
416 g_sua_status := 'WARNING';
417 RETURN;
418 END IF ;
419 -- Unique Key validation
420 IF igs_en_su_attempt_pkg.Get_Uk_For_Validation (
421 x_person_id => p_person_id ,
422 x_course_cd => p_sua_dtls_rec.program_cd,
423 x_unit_cd => p_sua_dtls_rec.unit_cd,
424 x_cal_type => p_cal_type,
425 x_ci_sequence_number => p_ci_sequence_number,
426 x_location_cd => p_sua_dtls_rec.location_cd,
427 x_unit_class => p_sua_dtls_rec.unit_class,
428 x_version_number => p_unit_version_number) THEN
429 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_UNT_ATMPT_EXTS');
430 FND_MSG_PUB.ADD;
431 g_sua_status := 'WARNING';
432 RETURN;
433 END IF ;
434
435 -- Foreign Key Validation - Administrative unit status
436 IF (p_sua_dtls_rec.administrative_unit_status IS NOT NULL) THEN
437 IF NOT IGS_AD_ADM_UNIT_STAT_PKG.Get_PK_For_Validation(
438 p_sua_dtls_rec.administrative_unit_status,NULL) THEN
439 Fnd_Message.Set_Name ('IGS', 'IGS_EN_AUS_INVALID');
440 FND_MSG_PUB.ADD;
441 g_sua_status := 'INVALID';
442 END IF;
443 END IF;
444
445
446 -- Foreign Key Validation - Grading Schema Code and version Number
447 IF (p_sua_dtls_rec.gs_version_number IS NOT NULL OR p_sua_dtls_rec.grading_schema_code IS NOT NULL) THEN
448 IF NOT igs_as_grd_schema_pkg.get_pk_for_validation (
449 x_grading_schema_cd => p_sua_dtls_rec.grading_schema_code,
450 x_version_number => p_sua_dtls_rec.gs_version_number) THEN
451
452 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_GRD_SCH' );
453 FND_MSG_PUB.ADD;
454 g_sua_status := 'INVALID';
455 END IF;
456 END IF;
457
458 -- Foreign Key Validation - Check if Location Code exists.
459 IF NOT igs_ad_location_pkg.get_pk_for_validation ( x_location_cd => p_sua_dtls_rec.location_cd) THEN
460 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_LOC_CD_INV' );
461 FND_MSG_PUB.ADD;
462 g_sua_status := 'INVALID';
463 END IF;
464
465 -- Foreign Key Validation - Check if Student Program Attempt exists.
466 IF NOT igs_en_stdnt_ps_att_pkg.get_pk_for_validation (
467 x_person_id => p_person_id,
468 x_course_cd => p_sua_dtls_rec.program_cd) THEN
469
470 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_FI_PRSNID_PRGCD_NOT_MATCH');
471 FND_MSG_PUB.ADD;
472 g_sua_status := 'INVALID';
473 END IF ;
474
475 -- Foreign Key Validation - Check if Unit Code / Version exists.
476 IF NOT igs_ps_unit_ver_pkg.get_pk_for_validation (
477 x_unit_cd => p_sua_dtls_rec.unit_cd,
478 x_version_number => NVL ( p_sua_dtls_rec.version_number,p_unit_version_number)) THEN
479
480 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_PS_UNITCODE_UNITVER_NE');
481 FND_MSG_PUB.ADD;
482 g_sua_status := 'INVALID';
483 END IF;
484
485 -- Foreign Key Validation - Check if Unit Code exists.
486 IF NOT igs_ps_unit_pkg.get_pk_for_validation ( x_unit_cd => p_sua_dtls_rec.unit_cd)THEN
487 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_IN_INVALID_UNIT_CODE');
488 FND_MSG_PUB.ADD;
489 g_sua_status := 'INVALID';
490 END IF;
491
492 -- Foreign Key Validation - Check if Unit is being Offered.
493 IF NOT igs_ps_unit_ofr_opt_pkg.get_pk_for_validation (
494 x_unit_cd => p_sua_dtls_rec.unit_cd,
495 x_version_number => NVL ( p_sua_dtls_rec.version_number,p_unit_version_number),
496 x_cal_type => p_cal_type,
497 x_ci_sequence_number => p_ci_sequence_number,
498 x_location_cd => p_sua_dtls_rec.location_cd,
499 x_unit_class => p_sua_dtls_rec.unit_class) THEN
500
501 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_AS_UNITCD_LOC_UC_INVALID');
502 FND_MSG_PUB.ADD;
503 g_sua_status := 'INVALID';
504 END IF;
505
506 --Foreign Key validation - Check if Discontinuation Reason Code exists.
507 IF p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL THEN
508 IF NOT igs_en_dcnt_reasoncd_pkg.get_pk_for_validation ( x_dcnt_reason_cd => p_sua_dtls_rec.dcnt_reason_cd )THEN
509 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_DISC_REASON_CD_INV' );
510 FND_MSG_PUB.ADD;
511 g_sua_status := 'INVALID';
512 END IF;
513 END IF;
514
515
516 -- core_indicator validation - Check if value of core indicator is valid, added by rvangala as part of Prevent Dropping Core Units. Enh Bug# 3052432
517 IF p_sua_dtls_rec.core_indicator IS NOT NULL THEN
518 l_indicator := IGS_LOOKUPS_VIEW_PKG.GET_PK_FOR_VALIDATION('IGS_PS_CORE_IND',p_sua_dtls_rec.core_indicator);
519 IF l_indicator=FALSE THEN
520 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_CORE_IND_INVALID' );
521 FND_MSG_PUB.ADD;
522 g_sua_status := 'INVALID';
523 END IF;
524 END IF;
525 END validate_db_cons;
526
527 PROCEDURE validate_pre_sua( p_person_id IN NUMBER,
528 p_sua_dtls_rec IN sua_dtls_rec_type ,
529 p_version_number IN NUMBER ,
530 p_cal_type IN VARCHAR2 ,
531 p_ci_sequence_number IN NUMBER,
532 p_uoo_id IN IGS_EN_SU_ATTEMPT.UOO_ID%TYPE
533 ) AS
534 /*------------------------------------------------------------------
535 Created By : SVENKATA
536 Date Created By : 12-NOV-02
537 Purpose : This routine is private API. The insert API makes a call to this routine to validate
538 SUA details regarding Unit and Program Transfer. If the validations are successful ,
539 SUA AND SPA Transfer Details are created in the respectively tables. NOte that SPA
540 transfer details can already exist in the table if a Transferred Unit Attempt has
541 already been import for another SUA.
542 Known limitations,
543 enhancements,
544 remarks :
545 Change History
546 Who When What
547 svenkata 2-JAN-03 The values of WHO columns was being set inside the block for Student Program Transfer.Moved the code
548 to the main block-Bug# 2732542
549 kkillams 29-04-2003 Modified the get_sua_trn cursor due to change in the pk of student unit attempt w.r.t. bug number 2829262
550 ------------------------------------------------------------------*/
551 --
552 -- cursor to determine if Student Program Attempt Transfer details already exist .
553 CURSOR get_sca_trn IS
554 SELECT 'x'
555 FROM igs_ps_stdnt_trn
556 WHERE person_id = p_person_id AND
557 course_cd= p_sua_dtls_rec.program_cd AND
558 transfer_course_cd = p_sua_dtls_rec.transfer_program_cd AND
559 TRUNC(transfer_dt) = TRUNC( p_sua_dtls_rec.transfer_dt);
560
561 --
562 -- CURSOR to check if the SUA Transfer record already exists
563 CURSOR get_sua_trn IS
564 SELECT 'x'
565 FROM igs_ps_stdnt_unt_trn
566 WHERE person_id = p_person_id AND
567 course_cd = p_sua_dtls_rec.program_cd AND
568 transfer_course_cd = p_sua_dtls_rec.transfer_program_cd AND
569 transfer_dt = p_sua_dtls_rec.transfer_dt AND
570 uoo_id = p_uoo_id;
571
572
573 l_creation_date igs_ps_stdnt_trn.creation_date%TYPE;
574 l_last_update_date igs_ps_stdnt_trn.last_update_date%TYPE;
575 l_created_by igs_ps_stdnt_trn.created_by%TYPE;
576 l_last_updated_by igs_ps_stdnt_trn.last_updated_by%TYPE;
577 l_last_update_login igs_ps_stdnt_trn.last_update_login%TYPE;
578 l_dummy VARCHAR2(1) := NULL;
579
580 BEGIN
581
582 -- Insert SPA Transfer details
583 OPEN get_sca_trn;
584 FETCH get_sca_trn INTO l_dummy;
585
586 l_creation_date := SYSDATE;
587 l_created_by := FND_GLOBAL.USER_ID;
588
589 l_last_update_date := SYSDATE;
590 l_last_updated_by := FND_GLOBAL.USER_ID;
591 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
592
593 IF l_created_by IS NULL THEN
594 l_created_by := -1;
595 END IF;
596
597 IF l_last_updated_by IS NULL THEN
598 l_last_updated_by := -1;
599 END IF;
600
601 IF l_last_update_login IS NULL THEN
602 l_last_update_login := -1;
603 END IF;
604
605 IF get_sca_trn%NOTFOUND THEN
606
607 BEGIN
608 INSERT INTO IGS_PS_STDNT_TRN (
609 PERSON_ID,
610 COURSE_CD,
611 TRANSFER_COURSE_CD,
612 TRANSFER_DT,
613 COMMENTS,
614 STATUS_DATE,
615 STATUS_FLAG,
616 CREATION_DATE,
617 CREATED_BY,
618 LAST_UPDATE_DATE,
619 LAST_UPDATED_BY,
620 LAST_UPDATE_LOGIN
621 ) VALUES (
622 p_person_id,
623 p_sua_dtls_rec.program_cd ,
624 p_sua_dtls_rec.transfer_program_cd,
625 p_sua_dtls_rec.transfer_dt,
626 NULL ,
627 p_sua_dtls_rec.transfer_dt,
628 'T',
629 l_last_update_date,
630 l_last_updated_by,
631 l_last_update_date,
632 l_last_updated_by,
633 l_last_update_login
634 );
635 EXCEPTION
636 WHEN OTHERS THEN
637 IF (get_sca_trn%ISOPEN) THEN
638 CLOSE get_sca_trn;
639 END IF;
640
641 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_STDNT_SPA_TRN_INCOMPL' );
642 FND_MSG_PUB.ADD;
643 g_sua_status := 'INVALID';
644 RAISE ;
645 END;
646 END IF ;
647 CLOSE get_sca_trn;
648
649 OPEN get_sua_trn;
650 FETCH get_sua_trn INTO l_dummy;
651 IF get_sua_trn%NOTFOUND THEN
652
653 BEGIN
654 INSERT INTO igs_ps_stdnt_unt_trn (
655 PERSON_ID,
656 COURSE_CD,
657 TRANSFER_COURSE_CD,
658 TRANSFER_DT,
659 UOO_ID,
660 UNIT_CD,
661 CAL_TYPE,
662 CI_SEQUENCE_NUMBER,
663 CREATION_DATE,
664 CREATED_BY,
665 LAST_UPDATE_DATE,
666 LAST_UPDATED_BY,
667 LAST_UPDATE_LOGIN
668 ) values (
669 p_person_id,
670 p_sua_dtls_rec.program_cd ,
671 p_sua_dtls_rec.transfer_program_cd,
672 p_sua_dtls_rec.transfer_dt,
673 p_uoo_id,
674 p_sua_dtls_rec.unit_cd,
675 p_cal_type ,
676 p_ci_Sequence_number,
677 l_last_update_date,
678 l_last_updated_by,
679 l_last_update_date,
680 l_last_updated_by,
681 l_last_update_login
682 );
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 IF (get_sua_trn%ISOPEN) THEN
687 CLOSE get_sua_trn;
688 END IF;
689
690 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_STDNT_SUA_TRN_INCOMPL' );
691 FND_MSG_PUB.ADD;
692 g_sua_status := 'INVALID';
693 RAISE ;
694 END;
695
696 ELSE
697 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_STDNT_SUA_TRN_INCOMPL' );
698 FND_MSG_PUB.ADD;
699 g_sua_status := 'INVALID';
700 END IF ;
701
702 CLOSE get_sua_trn;
703
704 END validate_pre_sua;
705
706 PROCEDURE validate_post_sua (p_person_id IN NUMBER,
707 p_version_number IN NUMBER,
708 p_sua_dtls_rec IN sua_dtls_rec_type,
709 p_cal_type IN VARCHAR2,
710 p_ci_sequence_number IN NUMBER,
711 p_ci_end_dt IN DATE,
712 p_ci_start_dt IN DATE,
713 p_unit_attempt_status IN VARCHAR2,
714 p_uoo_id IN NUMBER,
715 p_administrative_unit_status IN VARCHAR2,
716 p_career_centric IN VARCHAR2 ,
717 p_primary_program_type IN VARCHAR2 ,
718 p_administrative_pri IN NUMBER
719 ) AS
720 /*------------------------------------------------------------------
721 Created By : SVENKATA
722 Date Created By : 12-NOV-02
723 Purpose : This routine is private to the API. There is a call to this routine from the insert
724 API to carry out all validations after inserting Student Unit Attempt Details .This
725 routine does the following :
726 1. If DISCONTINUED_DT is not null then insert corresponding discontinuation grade
727 into IGS_AS_SU_STMPTOUT_ALL
728 2. Increment Unit section actual enrollment .
729 3. Insert Student Unit Attempt Outcome details
730 4. Re-derive the Program Attempt status. Update the SPA record with the new status.
731 Known limitations,
732 enhancements,
733 remarks :
734 Change History
735 Who When What
736 svenkata 18-Nov-2002 Bug# 2715256- Adminisntrative Unit Status was passed incorrectly to the routine
737 igs_en_gen_010.enrp_ins_suao_discon.Added NVL clause.
738 svenkata 18-Nov-2002 Bug# 2715240 - The value of the column MARK was not passed correctly to the API call
739 of Student Unit Attempt Outcome.
740 svenkata 30-Dec-2002 Mirroring of all secondary Program Attempts if status of Primary Program Attempt
741 | is changed.Bug# 2728047.
742 kkillams 27-Mar-03 Modified usec_cur Cursor, replaced * with enrollment_actual and waitlist_actual
743 w.r.t. bug 2749648
744 kkillams 25-04-2003 Impacted object, due to change in the signature of the igs_en_gen_010.enrp_ins_suao_discon function
745 w.r.t. bug number 2829262
746 rvivekan 11-July -2003 Added INVALID and UNCONFIRM to scenarios for enrollment_actual increment Bug 3036949
747 ptandon 23-Sep-2003 Passed the value of fields LOCATION_CD and UNIT_CLASS in l_suao_rec parameter in call to procedure
748 igs_as_suao_lgcy_pub.create_unit_outcome. Bug# 3149520.
749 ptandon 24-Sep-2003 In call to igs_en_gen_legacy.get_course_att_status, the value of parameter p_discontinued_dt
750 was being incorrectly passed as p_sua_dtls_rec.discontinued_dt. Replaced it by value of
751 discontinued_dt fetched from get_spa cursor. Bug# 3152211.
752 ptandon 02-Dec-2003 Inserted call to the Term Records Legacy API as per Term Records Fee Calc build. Bug# 2829263.
753 ------------------------------------------------------------------*/
754 --
755 -- Cursor to get the Enrollment Actual for a Unit section.
756 CURSOR usec_upd_enr_act IS
757 SELECT ROWID,uoo.enrollment_actual, uoo.waitlist_actual
758 FROM igs_ps_unit_ofr_opt uoo
759 WHERE uoo_id = p_uoo_id
760 FOR UPDATE NOWAIT;
761
762 --
763 -- Cursor to fetch Student Program Details .
764 CURSOR get_spa IS
765 SELECT ROWID , spa.*
766 FROM igs_en_stdnt_ps_att spa
767 WHERE spa.person_id = p_person_id AND
768 spa.course_cd = p_sua_dtls_rec.program_cd
769 FOR UPDATE NOWAIT;
770
771 --
772 -- Cursor to get the Course Type of the Primary Program
773 CURSOR get_course_type IS
774 SELECT sca.course_type
775 FROM igs_en_sca_v sca
776 WHERE sca.person_id = p_person_id AND
777 sca.course_cd = p_sua_dtls_rec.program_cd ;
778
779 --
780 -- Cursor to fetch all the secondary Programs in a career for the given Primary Program
781 CURSOR get_spa_sec_prgm (p_course_type IN VARCHAR2 ) IS
782 SELECT spa.ROWID
783 FROM igs_en_stdnt_ps_att spa , igs_en_sca_v sca
784 WHERE spa.person_id = p_person_id AND
785 spa.person_id = sca.person_id AND
786 sca.course_cd = spa.course_cd AND
787 sca.course_type = p_course_type AND
788 spa.primary_program_type = 'SECONDARY' AND
789 spa.course_attempt_status NOT IN ('UNCONFIRM' , 'DISCONTIN' , 'COMPLETED')
790 FOR UPDATE NOWAIT ;
791
792 l_suao_rec igs_as_suao_lgcy_pub.lgcy_suo_rec_type;
793 l_course_type igs_en_sca_v.course_type%TYPE DEFAULT NULL ;
794 l_spa_row get_spa%ROWTYPE;
795 l_spa_row_sec get_spa_sec_prgm %ROWTYPE;
796
797 l_usec_row usec_upd_enr_act%ROWTYPE;
798 l_message_name VARCHAR2(60) := NULL ;
799 l_waitlist_actual igs_ps_unit_ofr_opt.waitlist_actual%TYPE := 0 ;
800 l_enrollment_actual igs_ps_unit_ofr_opt.enrollment_actual%TYPE:= 0 ;
801
802 l_return_status VARCHAR2(1) := NULL ;
803 l_msg_count NUMBER := 0;
804 l_msg_data VARCHAR2(2000) := NULL ;
805 l_course_attempt_status_after igs_en_stdnt_ps_att.course_attempt_status%TYPE := NULL ;
806 l_last_dt_of_att igs_en_stdnt_ps_att.last_date_of_attendance%TYPE := NULL ;
807
808 BEGIN
809
810 IF p_unit_attempt_status ='WAITLISTED' THEN
811 IF g_wlst_pri_pref_exists=TRUE THEN
812 igs_en_wlst_gen_proc.enrp_wlst_assign_pos(p_person_id => p_person_id,
813 p_program_cd => p_sua_dtls_rec.program_cd,
814 p_uoo_id => p_uoo_id);
815
816 ELSE
817 igs_en_wlst_gen_proc.enrp_wlst_dt_reseq (p_person_id => p_person_id,
818 p_program_cd => p_sua_dtls_rec.program_cd,
819 p_uoo_id => p_uoo_id,
820 p_cur_position=> p_administrative_pri);
821 END IF;
822 END IF;
823 --
824 -- A. If discontinued date is set , corresponding discontinuation grade is inserted into IGS_AS_SU_STMPTOUT_ALL
825 IF p_sua_dtls_rec.discontinued_dt IS NOT NULL THEN
826
827 IF NOT igs_en_gen_010.enrp_ins_suao_discon(
828 p_person_id => p_person_id ,
829 p_course_cd => p_sua_dtls_rec.program_cd ,
830 p_unit_cd => p_sua_dtls_rec.unit_cd ,
831 p_cal_type => p_cal_type ,
832 p_ci_sequence_number => p_ci_sequence_number ,
833 p_ci_start_dt => p_ci_start_dt ,
834 p_ci_end_dt => p_ci_end_dt ,
835 p_discontinued_dt => p_sua_dtls_rec.discontinued_dt ,
836 p_administrative_unit_status => NVL( p_sua_dtls_rec.administrative_unit_status , p_administrative_unit_status),
837 p_message_name => l_message_name,
838 p_uoo_id => p_uoo_id) AND l_message_name IS NOT NULL THEN
839 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name );
840 FND_MSG_PUB.ADD;
841 g_sua_status := 'INVALID';
842
843 END IF;
844
845 END IF ;
846
847 --
848 -- B. Increment Unit section actual enrollment and waitlist actual.
849 OPEN usec_upd_enr_act ;
850 FETCH usec_upd_enr_act INTO l_usec_row;
851
852 IF usec_upd_enr_act%FOUND THEN
853 CLOSE usec_upd_enr_act;
854 IF p_unit_attempt_status IN ( 'ENROLLED' ,'COMPLETED','INVALID','UNCONFIRM') THEN
855
856 -- Increment the Total enrollment actual count by 1.
857 l_enrollment_actual := NVL(l_usec_row.ENROLLMENT_ACTUAL,0)+1;
858
859 BEGIN
860 UPDATE igs_ps_unit_ofr_opt_all SET enrollment_actual = l_enrollment_actual WHERE ROWID = l_usec_row.ROWID;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 IF (usec_upd_enr_act%ISOPEN) THEN
865 CLOSE usec_upd_enr_act;
866 END IF ;
867 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_ACTUAL_ENR_UD_ERR' );
868 FND_MSG_PUB.ADD;
869 g_sua_status := 'INVALID';
870 RAISE ;
871 END ;
872
873 ELSIF p_unit_attempt_status = 'WAITLISTED' THEN
874
875 -- Increment the Total waitlist actual count by 1.
876 l_waitlist_actual := NVL(l_usec_row.WAITLIST_ACTUAL,0)+1;
877
878 BEGIN
879 UPDATE igs_ps_unit_ofr_opt_all SET waitlist_actual = l_waitlist_actual WHERE ROWID = l_usec_row.ROWID ;
880
881 EXCEPTION
882 WHEN OTHERS THEN
883 IF (usec_upd_enr_act%ISOPEN) THEN
884 CLOSE usec_upd_enr_act;
885 END IF ;
886 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_WAITLIST_UPD_ERR' );
887 FND_MSG_PUB.ADD;
888 g_sua_status := 'INVALID';
889 RAISE ;
890 END ;
891 END IF ;
892 ELSE
893 CLOSE usec_upd_enr_act;
894 END IF ;
895 --
896 -- C.Insert Student Unit Attempt Outcome details
897 -- This has be done when SUAO API is complete , or atleast when signature is available.
898 --
899 IF p_sua_dtls_rec.outcome_dt IS NOT NULL THEN
900
901 l_suao_rec.person_number := p_sua_dtls_rec.person_number ;
902 l_suao_rec.program_cd := p_sua_dtls_rec.program_cd ;
903 l_suao_rec.unit_cd := p_sua_dtls_rec.unit_cd ;
904 l_suao_rec.teach_cal_alt_code := p_sua_dtls_rec.teach_calendar_alternate_code ;
905 l_suao_rec.outcome_dt := p_sua_dtls_rec.outcome_dt ;
906 l_suao_rec.grading_schema_cd := p_sua_dtls_rec.outcome_grading_schema_code ;
907 l_suao_rec.version_number := p_sua_dtls_rec.outcome_gs_version_number;
908 l_suao_rec.grading_period_cd := 'FINAL';
909 l_suao_rec.incomp_deadline_date := p_sua_dtls_rec.incomp_deadline_date ;
910 l_suao_rec.incomp_default_grade := p_sua_dtls_rec.incomp_default_grade ;
911 l_suao_rec.grade := p_sua_dtls_rec.grade ;
912 l_suao_rec.mark := p_sua_dtls_rec.mark ;
913 l_suao_rec.incomp_default_mark := p_sua_dtls_rec.incomp_default_mark ;
914 l_suao_rec.location_cd := p_sua_dtls_rec.location_cd ;
915 l_suao_rec.unit_class := p_sua_dtls_rec.unit_class ;
916
917 igs_as_suao_lgcy_pub.create_unit_outcome
918 (
919 p_api_version => 1,
920 p_init_msg_list => FND_API.G_FALSE ,
921 p_commit => FND_API.G_FALSE ,
922 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
923 p_lgcy_suo_rec => l_suao_rec ,
924 x_return_status => l_return_status ,
925 x_msg_count => l_msg_count ,
926 x_msg_data => l_msg_data );
927
928 IF l_return_status IN ('E' , 'U' , 'W' ) THEN
929 g_sua_status := 'INVALID';
930 END IF ;
931 END IF;
932
933 -- The the system is in Career Centric mode, get the course_type of the Program.
934 IF p_career_centric = 'Y' THEN
935 OPEN get_course_type ;
936 FETCH get_course_type INTO l_course_type;
937 CLOSE get_course_type;
938 END IF;
939
940 OPEN get_spa ;
941 FETCH get_spa INTO l_spa_row ;
942
943 -- D. Update Student Program Attempt
944 IF get_spa%FOUND THEN
945 CLOSE get_spa ;
946 IF p_sua_dtls_rec.discontinued_dt IS NOT NULL THEN
947 igs_en_gen_legacy.get_last_dt_of_att (
948 x_person_id => p_person_id ,
949 x_course_cd => p_sua_dtls_rec.program_cd ,
950 x_last_date_of_attendance => l_last_dt_of_att ) ;
951 END IF;
952
953
954 l_course_attempt_status_after := igs_en_gen_legacy.get_course_att_status(
955 p_person_id => p_person_id ,
956 p_course_cd => p_sua_dtls_rec.program_cd ,
957 p_student_confirmed_ind => l_spa_row.student_confirmed_ind ,
958 p_discontinued_dt => l_spa_row.discontinued_dt ,
959 p_lapsed_dt => l_spa_row.lapsed_dt ,
960 p_course_rqrmnt_complete_ind => l_spa_row.course_rqrmnt_complete_ind ,
961 p_primary_pg_type => p_primary_program_type ,
962 p_primary_prog_type_source => l_spa_row.primary_program_type ,
963 p_course_type => l_course_type ,
964 p_career_flag => p_career_centric ) ;
965
966
967 IF l_spa_row.course_attempt_status <> l_course_attempt_status_after THEN
968
969 -- If the course attempt status changes as a result of the Unit Import , Update the Course Attempt Status .
970 BEGIN
971 IF l_last_dt_of_att IS NOT NULL THEN
972 UPDATE IGS_EN_STDNT_PS_ATT_ALL SET COURSE_ATTEMPT_STATUS = l_course_attempt_status_after ,
973 LAST_DATE_OF_ATTENDANCE = l_last_dt_of_att WHERE ROWID = l_spa_row.ROWID;
974 ELSE
975 UPDATE IGS_EN_STDNT_PS_ATT_ALL SET COURSE_ATTEMPT_STATUS = l_course_attempt_status_after WHERE ROWID = l_spa_row.ROWID;
976 END IF;
977
978 -- If the Primary Program Status is updated, mirror all the secondary Programs with the same status as the primary program.
979 IF l_spa_row.primary_program_type = 'PRIMARY' THEN
980 FOR get_spa_sec_prgm_rec IN get_spa_sec_prgm(l_course_type)
981 LOOP
982 UPDATE IGS_EN_STDNT_PS_ATT_ALL SET COURSE_ATTEMPT_STATUS = l_course_attempt_status_after WHERE ROWID = get_spa_sec_prgm_rec.ROWID ;
983 END LOOP;
984 END IF;
985
986 EXCEPTION
987 WHEN OTHERS THEN
988 IF (get_spa%ISOPEN) THEN
989 CLOSE get_spa;
990 END IF;
991 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SPA_STAT_INCOMPL' );
992 FND_MSG_PUB.ADD;
993 g_sua_status := 'INVALID';
994 RAISE ;
995 END ;
996 END IF ;
997 ELSE
998 CLOSE get_spa ;
999 END IF ;
1000
1001 END validate_post_sua ;
1002
1003 PROCEDURE validate_sua(p_person_id IN NUMBER,
1004 p_version_number IN NUMBER,
1005 p_sua_dtls_rec IN sua_dtls_rec_type,
1006 p_cal_type IN VARCHAR2,
1007 p_ci_sequence_number IN NUMBER,
1008 p_ci_end_dt IN DATE,
1009 p_ci_start_dt IN DATE,
1010 p_unit_attempt_status IN VARCHAR2,
1011 p_uoo_id IN NUMBER ,
1012 p_career_model_enabled IN VARCHAR2 ,
1013 p_administrative_unit_status IN VARCHAR2,
1014 p_no_assessment_ind IN VARCHAR2 ,
1015 p_primary_program_type OUT NOCOPY VARCHAR2 ,
1016 p_sup_unit_cd OUT NOCOPY VARCHAR2 ,
1017 p_sup_unit_version_number OUT NOCOPY NUMBER
1018 ) AS
1019 /*------------------------------------------------------------------
1020 Created By : SVENKATA
1021 Date Created By : 12-NOV-02
1022 Purpose : This routine is private to the API. There is a call to this routine from the insert
1023 API to carry out all validations before inserting Student Unit Attempt Details .
1024 Known limitations,
1025 enhancements,
1026 remarks :
1027 Change History
1028 Who When What
1029 sarakshi 13-Jul-2004 Bug#3729462, Added predicate DELETE_FLAG='N' to the cursor c_get_wlst_alwd_oopt .
1030 svenkata 30-Dec-2002 Error message was displayed twice.The message_name returned was compared against NULL incorrectly
1031 after call to routine igs_en_val_sua.enrp_val_discont_aus. Bug#2727931
1032 pradhakr 20-Jan-2003 Added a parameter no_assessment_ind to the procedue call IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp
1033 as part of ENCR26 build.
1034 ptandon 17-Oct-2003 Added two OUT parameters p_sup_unit_cd and p_sup_unit_version_number and modified the code to add
1035 the validation for the superior-subordinate as part of Placements build. Enh Bug# 3052438.
1036 rvivekan 17-nov-2003 Bug3264064. Changed the datatype of variables holding the concatenated administrative unit status list
1037 to varchar(2000)
1038 vkarthik 10-dec-2003 Bug3140571. Added a cursor to pick up version for the given person and course and another to get
1039 max_wlst_per_stud given the course and version. Made use of these cursors to include program level
1040 EN waitlist
1041 bdeviset 27-oct-2004 Bug#3972537.The call igs_en_val_sua.enrp_val_sua_intrmt is not made if unit_attempt_status is
1042 either dropped or discontinued.
1043
1044 bdeviset 16-NOV-2004 Bug#4000939.Added a check to see if the load calendar (corresponding to the teaching calendar) end date
1045 is greater than the commencement date of the program attempt for ENROLLED,DISCONTIN,WAITLIST and INVALID.
1046 ckasu 30-DEC-2004 modified code inorder to consider Term Records while getting primary program type as a part of bug#4095276
1047 ckasu 20-JUL-2006 modified b=y ckasu as a part of bug #4642089 inorder to validate whethet Subtitle updation is allowed or not
1048 ------------------------------------------------------------------*/
1049 --
1050 -- Cursor to check if the auditable_ind is available at the unit section level.
1051 CURSOR get_audit_usec IS
1052 SELECT NVL(auditable_ind, 'N')
1053 FROM igs_ps_unit_ofr_opt
1054 WHERE uoo_id = p_uoo_id;
1055
1056 CURSOR c_get_wlst_alwd_usec (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1057 SELECT NVL(waitlist_allowed,'N') waitlist_allowed,NVL(max_students_per_waitlist,9999) max_students_per_waitlist
1058 FROM igs_ps_usec_lim_wlst
1059 WHERE cp_uoo_id=uoo_id;
1060
1061
1062 CURSOR c_get_wlst_alwd_oopt (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1063 SELECT NVL(uop.waitlist_allowed,'N') waitlist_allowed ,NVL(uop.max_students_per_waitlist,9999) max_students_per_waitlist
1064 FROM igs_ps_unit_ofr_pat uop,
1065 igs_ps_unit_ofr_opt uoo
1066 WHERE uop.unit_cd=uoo.unit_cd
1067 AND uop.version_number=uoo.version_number
1068 AND uop.cal_type=uoo.cal_type
1069 AND uop.ci_sequence_number=uoo.ci_sequence_number
1070 AND uoo.uoo_id=cp_uoo_id
1071 AND uop.delete_flag='N';
1072
1073
1074
1075 CURSOR c_get_wlst_actual_usec (cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1076 SELECT waitlist_actual
1077 FROM igs_ps_unit_ofr_opt
1078 WHERE uoo_id=p_uoo_id;
1079
1080 -- cursor to get version number for person and course
1081 CURSOR c_get_prog_ver(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
1082 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE) IS
1083 SELECT version_number
1084 FROM igs_en_stdnt_ps_att
1085 WHERE
1086 person_id = cp_person_id AND
1087 course_cd = cp_course_cd;
1088
1089 -- cursor to get program level max_wlst_per_stud for a course and version
1090 CURSOR c_max_wlst_stud_ps(cp_course_cd igs_ps_ver.course_cd%TYPE,
1091 cp_version_number igs_ps_ver.version_number%TYPE) IS
1092 SELECT max_wlst_per_stud
1093 FROM igs_ps_ver
1094 WHERE
1095 course_cd = cp_course_cd AND
1096 version_number = cp_version_number;
1097
1098 CURSOR c_get_max_wlst_per_stud IS
1099 SELECT NVL(max_waitlists_student_num,9999) max_waitlists
1100 FROM IGS_EN_INST_WL_STPS;
1101
1102 CURSOR c_get_wlst_suas (cp_person_id igs_en_su_attempt.person_id%TYPE,
1103 cp_load_cal_type igs_en_su_attempt.cal_type%TYPE,
1104 cp_load_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE) IS
1105 SELECT COUNT(ROWID) waitlists
1106 FROM igs_en_su_attempt
1107 WHERE person_id = cp_person_id AND
1108 unit_attempt_status ='WAITLISTED' AND
1109 (cal_type,ci_sequence_number) IN
1110 (SELECT teach_cal_type,teach_ci_sequence_number
1111 FROM igs_ca_load_to_teach_v
1112 WHERE load_cal_type = cp_load_cal_type AND
1113 load_ci_sequence_number = cp_load_ci_sequence_number);
1114
1115 --
1116 -- Cursor to get the Discontinued Date of the Program Attempt
1117 CURSOR get_prgm_discd_dt IS
1118 SELECT discontinued_dt
1119 FROM igs_en_Stdnt_ps_att
1120 WHERE course_cd = p_sua_dtls_rec.program_cd
1121 AND person_id = p_person_id;
1122
1123 --
1124 -- Cursor to determine if the unit section is superior or subordinate
1125 --
1126 CURSOR c_get_usec_relation(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1127 SELECT NVL(RELATION_TYPE,'NONE')
1128 FROM igs_ps_unit_ofr_opt
1129 WHERE uoo_id = cp_uoo_id;
1130
1131 --
1132 -- Cursor to get superior unit section details
1133 --
1134 CURSOR c_get_superior_usec(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
1135 SELECT unit_cd,version_number
1136 FROM igs_ps_unit_ofr_opt
1137 WHERE uoo_id = (SELECT sup_uoo_id
1138 FROM igs_ps_unit_ofr_opt
1139 WHERE uoo_id = cp_uoo_id);
1140
1141 -- Cursor to select the Term Calendar associated with a Teaching Calendar.
1142 CURSOR c_get_term_cal(cp_cal_type igs_ca_inst.cal_type%TYPE,
1143 cp_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1144 SELECT load_cal_type, load_ci_sequence_number,load_end_dt
1145 FROM IGS_CA_TEACH_TO_LOAD_V
1146 WHERE teach_cal_type = cp_cal_type
1147 AND teach_ci_sequence_number = cp_sequence_number
1148 ORDER BY LOAD_START_DT asc;
1149
1150 -- added by ckasu as a part of bug# 4642089
1151 CURSOR c_sua_chg_alwd IS
1152 SELECT NVL(subtitle_modifiable_flag,'N')
1153 FROM igs_ps_usec_ref
1154 WHERE uoo_id = p_uoo_id;
1155
1156 l_sua_chg_alwd igs_ps_usec_ref.subtitle_modifiable_flag%TYPE;
1157 l_auditable_ind igs_ps_unit_ver.auditable_ind%TYPE := NULL ;
1158 l_version_number igs_as_grd_sch_grade.version_number%TYPE := 0 ;
1159 l_program_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE ;
1160 l_prgm_discontinued_dt igs_en_stdnt_ps_att.discontinued_dt%TYPE := NULL ;
1161
1162 l_src_program_typ igs_en_sca_v.course_type%TYPE ;
1163 l_destn_program_typ igs_en_sca_v.course_type%TYPE ;
1164 l_person_id igs_pe_person.person_id%TYPE;
1165 l_discontin_dt igs_en_stdnt_ps_att.discontinued_dt%TYPE;
1166
1167 l_unit_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE := NULL ;
1168 l_count NUMBER := 0 ;
1169 l_message_name VARCHAR2(60) := NULL ;
1170 l_message_token VARCHAR2(2000) := NULL ;
1171
1172 l_boolean BOOLEAN := TRUE;
1173 l_legacy VARCHAR2(1) ;
1174 l_commencement_dt igs_en_stdnt_ps_att.commencement_dt%TYPE;
1175 l_prgm_ver igs_en_stdnt_ps_att.version_number%TYPE;
1176
1177 l_msg_count NUMBER ;
1178 l_msg_data VARCHAR2(2000);
1179 l_wlst_alwd c_get_wlst_alwd_usec%ROWTYPE;
1180 l_wlst_actual NUMBER;
1181 l_wlst_suas NUMBER;
1182 l_max_wlst_per_stud NUMBER;
1183 l_prog_version_spat igs_en_stdnt_ps_att.version_number%TYPE;
1184
1185 l_relation_type igs_ps_unit_ofr_opt.relation_type%TYPE;
1186 l_get_superior_usec_rec c_get_superior_usec%ROWTYPE;
1187 l_sup_sub_status igs_en_su_attempt.unit_attempt_status%TYPE;
1188 l_term_cal_dtls c_get_term_cal%ROWTYPE;
1189
1190
1191 BEGIN
1192 --added due to gscc warning
1193 l_legacy := 'Y';
1194
1195 -- get version number for the course of a given person
1196 OPEN c_get_prog_ver(p_person_id, p_sua_dtls_rec.program_cd);
1197 FETCH c_get_prog_ver INTO l_prog_version_spat;
1198 CLOSE c_get_prog_ver;
1199
1200 -- If imported sua is WAITLISTED, check whether waitlist is allowed and check that student is not crossing limit.
1201 IF p_unit_attempt_status='WAITLISTED' THEN
1202 OPEN c_get_wlst_alwd_usec(p_uoo_id);
1203 FETCH c_get_wlst_alwd_usec INTO l_wlst_alwd;
1204 IF c_get_wlst_alwd_usec%NOTFOUND THEN
1205 OPEN c_get_wlst_alwd_oopt(p_uoo_id);
1206 FETCH c_get_wlst_alwd_oopt INTO l_wlst_alwd;
1207 CLOSE c_get_wlst_alwd_oopt;
1208 END IF;
1209 CLOSE c_get_wlst_alwd_usec;
1210
1211 IF l_wlst_alwd.waitlist_allowed='N' THEN
1212 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_WLST_NOT_ALWD' );
1213 FND_MSG_PUB.ADD;
1214 g_sua_status := 'INVALID';
1215 END IF;
1216
1217 OPEN c_get_wlst_actual_usec(p_uoo_id);
1218 FETCH c_get_wlst_actual_usec INTO l_wlst_actual;
1219 CLOSE c_get_wlst_actual_usec;
1220
1221 IF l_wlst_actual>=l_wlst_alwd.max_students_per_waitlist THEN
1222 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_MAX_WAIT_REACH' );
1223 FND_MSG_PUB.ADD;
1224 g_sua_status := 'INVALID';
1225 END IF;
1226
1227 -- get program level max_wlst_per_stud for the program in context
1228 OPEN c_max_wlst_stud_ps(p_sua_dtls_rec.program_cd, l_prog_version_spat);
1229 FETCH c_max_wlst_stud_ps INTO l_max_wlst_per_stud;
1230 CLOSE c_max_wlst_stud_ps;
1231
1232 -- when program level max_wlst_per_stud is not defined, proceed to insitute level max_wlst_per_stud
1233 IF l_max_wlst_per_stud IS NULL THEN
1234 OPEN c_get_max_wlst_per_stud;
1235 FETCH c_get_max_wlst_per_stud INTO l_max_wlst_per_stud;
1236 CLOSE c_get_max_wlst_per_stud;
1237 END IF;
1238
1239 --check if the sua violates the maximum waitlists per student
1240 OPEN c_get_wlst_suas (p_person_id,p_cal_type, p_ci_sequence_number); --cursor returns 'Y' if validation succeeds, null otherwise
1241 FETCH c_get_wlst_suas INTO l_wlst_suas;
1242 CLOSE c_get_wlst_suas;
1243 IF l_max_wlst_per_stud<=NVL(l_wlst_suas,0) THEN
1244 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_MAX_WLST_STUD_RCH' );
1245 FND_MSG_PUB.ADD;
1246 g_sua_status := 'INVALID';
1247 END IF;
1248 END IF;
1249
1250 -- Get the term calendar associated with the teaching calendar.
1251 OPEN c_get_term_cal(p_cal_type,p_ci_sequence_number);
1252 FETCH c_get_term_cal INTO l_term_cal_dtls;
1253 CLOSE c_get_term_cal;
1254
1255
1256 -- 1. Derive Program Attempt Details
1257 l_program_attempt_status := igs_en_gen_legacy.validate_prgm_att_stat (
1258 p_person_id => p_person_id ,
1259 p_course_cd => p_sua_dtls_rec.program_cd ,
1260 p_discontin_dt => l_discontin_dt ,
1261 p_program_type => l_src_program_typ ,
1262 p_commencement_dt => l_commencement_dt ,
1263 p_version_number => l_prgm_ver ) ;
1264 p_primary_program_type := l_src_program_typ;
1265
1266 -- added by ckasu as a part of bug #4095276
1267 --
1268 l_src_program_typ := igs_en_spa_terms_api.get_spat_primary_prg(p_person_id,p_sua_dtls_rec.program_cd,l_term_cal_dtls.load_cal_type,l_term_cal_dtls.load_ci_sequence_number);
1269
1270 -- end of code added by ckasu as a part of bug #4095276
1271
1272 --
1273 -- 2. The Unit version that the student is trying to enroll in must not be planned.
1274 -- 3. Unit section must have offered flag set.
1275 l_boolean := igs_en_val_sua.enrp_val_sua_uoo(
1276 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1277 p_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1278 p_cal_type => p_cal_type ,
1279 p_ci_sequence_number => p_ci_sequence_number ,
1280 p_location_cd => p_sua_dtls_rec.location_cd ,
1281 p_unit_class => p_sua_dtls_rec.unit_class,
1282 p_message_name => l_message_name ,
1283 p_legacy => 'Y' ) ;
1284
1285 IF l_message_name IS NOT NULL THEN
1286 g_sua_status := 'INVALID';
1287 END IF ;
1288
1289 --
1290 -- Validate Program Attempt Status .
1291 -- 4. If enrolled date is set, program attempt must be confirmed. Value of NULL is being passed for Commencement Date
1292 -- as the validation pertaining to that should be skipped in the routine enrp_val_sua_enr_dt.
1293 IF NOT igs_en_val_sua.enrp_val_sua_enr_dt (
1294 p_person_id => p_person_id ,
1295 p_course_cd => p_sua_dtls_rec.program_cd,
1296 p_enrolled_dt => NVL( p_sua_dtls_rec.enrolled_dt , NULL) ,
1297 p_unit_attempt_status => p_unit_attempt_status ,
1298 p_ci_end_dt => p_ci_end_dt ,
1299 p_commencement_dt => NULL ,
1300 p_message_name => l_message_name ,
1301 p_legacy => 'Y' )
1302 THEN
1303
1304 g_sua_status := 'INVALID';
1305
1306 END IF;
1307
1308 --
1309 -- Validate Intermission Periods
1310 -- 5. Cannot enroll in teaching period within period of intermission (defined as intermission dates overlapping census date(s))
1311 BEGIN
1312
1313 IF p_unit_attempt_status <> 'DISCONTIN' AND p_unit_attempt_status <> 'DROPPED' THEN
1314
1315 IF NOT igs_en_val_sua.enrp_val_sua_intrmt (
1316 p_person_id => p_person_id ,
1317 p_course_cd => p_sua_dtls_rec.program_cd,
1318 p_cal_type => p_cal_type ,
1319 p_ci_sequence_number => p_ci_sequence_number ,
1320 p_message_name => l_message_name ) THEN
1321
1322 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name );
1323 FND_MSG_PUB.ADD;
1324 g_sua_status := 'INVALID';
1325 END IF ;
1326
1327 END IF;
1328
1329 EXCEPTION
1330 WHEN OTHERS THEN
1331 FND_MSG_PUB.COUNT_AND_GET ( p_count => l_msg_count ,
1332 p_data => l_msg_data);
1333 FND_MSG_PUB.DELETE_MSG(l_msg_count);
1334 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_ST_CANT_DETR_CENSUS_DA');
1335 FND_MSG_PUB.ADD;
1336 g_sua_status := 'INVALID';
1337 END ;
1338
1339 --
1340 -- Validate Unit version Repeatable
1341 -- 6. Unit version cannot be enrolled if unit version is not repeatable and advanced standing has been already granted for the unit.
1342 BEGIN
1343 IF NOT igs_en_val_sua.enrp_val_sua_advstnd (
1344 p_person_id => p_person_id ,
1345 p_course_cd => p_sua_dtls_rec.program_cd,
1346 p_crs_version_number => l_prgm_ver ,
1347 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1348 p_un_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1349 p_message_name => l_message_name ,
1350 p_legacy => 'Y' )
1351 THEN
1352
1353 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name );
1354 FND_MSG_PUB.ADD;
1355 g_sua_status := 'INVALID';
1356 END IF;
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_AV_MAPADV_SUA_CANNOT_DTRM' );
1360 FND_MSG_PUB.ADD;
1361 g_sua_status := 'INVALID';
1362 END ;
1363
1364 BEGIN
1365
1366 -- Determine if the unit section is superior or subordinate.
1367 OPEN c_get_usec_relation(p_uoo_id);
1368 FETCH c_get_usec_relation INTO l_relation_type;
1369 CLOSE c_get_usec_relation;
1370
1371 IF l_relation_type = 'SUBORDINATE' THEN
1372
1373 -- If the unit section is SUBORDINATE, check whether the superior unit attempt exists.
1374 IF igs_en_sua_api.enr_sua_sup_sub_val(p_person_id => p_person_id,
1375 p_course_cd => p_sua_dtls_rec.program_cd,
1376 p_uoo_id => p_uoo_id,
1377 p_unit_attempt_status => p_unit_attempt_status,
1378 p_sup_sub_status => l_sup_sub_status)
1379 THEN
1380
1381 -- If superior unit attempt exists, fetch the superior unit code and unit version number
1382 OPEN c_get_superior_usec(p_uoo_id);
1383 FETCH c_get_superior_usec INTO l_get_superior_usec_rec;
1384 CLOSE c_get_superior_usec;
1385
1386 p_sup_unit_cd := l_get_superior_usec_rec.unit_cd;
1387 p_sup_unit_version_number := l_get_superior_usec_rec.version_number;
1388
1389 ELSE
1390
1391 FND_MESSAGE.SET_NAME('IGS','IGS_EN_LGCY_NO_SUPER');
1392 FND_MSG_PUB.ADD;
1393 g_sua_status := 'INVALID';
1394
1395 END IF;
1396
1397 END IF;
1398
1399 EXCEPTION
1400 WHEN OTHERS THEN
1401 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1402 FND_MESSAGE.SET_TOKEN('NAME','IGS_EN_SUA_API.ENR_SUA_SUP_SUB_VAL');
1403 g_sua_status := 'INVALID';
1404
1405 END;
1406
1407 --
1408 -- Validate subtitle titles
1409 -- 7.Alternative title is only permitted if unit version title override indicator is set.
1410 -- modified by ckasu as a part of bug #4642089 inorder to validate whethet Subtitle updation
1411 -- is allowed or not
1412 IF p_sua_dtls_rec.subtitle IS NOT NULL THEN
1413
1414 OPEN c_sua_chg_alwd;
1415 FETCH c_sua_chg_alwd INTO l_sua_chg_alwd;
1416 CLOSE c_sua_chg_alwd;
1417
1418 IF l_sua_chg_alwd = 'N' THEN
1419 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_FAIL_SUA_SUBTIT_NOT_AWD' );
1420 FND_MSG_PUB.ADD;
1421 g_sua_status := 'INVALID';
1422 END IF;
1423
1424 END IF;
1425
1426 --
1427 -- Verify unit version points override indicator
1428 -- 8. Override achievable or enrolled credit points are only permitted if unit version points override indicator is set
1429 -- 9. Override achievable CP must be within limits set by unit version/section min, max and increment values
1430 -- 10. Override enrolled CP must be within limits set by unit version/section min, max and increment values
1431
1432 BEGIN
1433 IF NOT igs_en_val_sua.enrp_val_sua_ovrd_cp (
1434 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1435 p_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1436 p_override_enrolled_cp => p_sua_dtls_rec.override_enrolled_cp ,
1437 p_override_achievable_cp => p_sua_dtls_rec.override_achievable_cp ,
1438 p_override_eftsu => NULL ,
1439 p_message_name => l_message_name ,
1440 p_uoo_id => p_uoo_id,
1441 p_no_assessment_ind => p_no_assessment_ind
1442 )THEN
1443 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name );
1444 FND_MSG_PUB.ADD;
1445 g_sua_status := 'INVALID';
1446 END IF;
1447 EXCEPTION
1448 WHEN OTHERS THEN
1449 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
1450 FND_MESSAGE.SET_TOKEN('NAME', 'IGS_EN_VAL_SUA.enrp_val_sua_ovrd_cp');
1451 g_sua_status := 'INVALID';
1452 END;
1453
1454 --
1455 -- Validate Grading schema / version:
1456 -- 11. Grading Schema Code/Version must be valid within the enrolling unit section/version
1457 IF p_sua_dtls_rec.grading_schema_code IS NOT NULL AND p_sua_dtls_rec.gs_version_number IS NOT NULL THEN
1458
1459 IF NOT igs_en_gen_legacy.validate_grad_sch_cd_ver (
1460 p_uoo_id => p_uoo_id ,
1461 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1462 p_Version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1463 p_Grading_schema_code => p_sua_dtls_rec.grading_schema_code,
1464 p_Gs_Version_number => p_sua_dtls_rec.gs_version_number ,
1465 p_message_name => l_message_name ) THEN
1466
1467 FND_MESSAGE.SET_NAME( 'IGS' , l_message_name );
1468 FND_MSG_PUB.ADD;
1469 g_sua_status := 'INVALID';
1470 END IF;
1471
1472 END IF;
1473
1474 --
1475 -- Validate Program Attempt Status :
1476 -- 12. If enrolled date is set, then program attempt status cannot be Unconfirmed.
1477 IF p_sua_dtls_rec.enrolled_dt IS NOT NULL THEN
1478 IF l_program_attempt_status = 'UNCONFIRM' THEN
1479 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ENRDT_NOT_ENTERED_SPA' );
1480 FND_MSG_PUB.ADD;
1481 g_sua_status := 'INVALID';
1482 END IF ;
1483 END IF;
1484
1485 --
1486 -- 13. Enrolled unit attempts cannot be added within a discontinued or completed program.
1487 IF l_program_attempt_status in ('DISCONTIN', 'COMPLETED') and p_unit_attempt_status = 'ENROLLED' THEN
1488 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_SPA_MISMTCH');
1489 FND_MSG_PUB.ADD;
1490 g_sua_status := 'INVALID';
1491 END IF;
1492
1493 --
1494 -- Validate DISCONTINUED Program Attempt Status :
1495 -- 14. If program attempt is discontinued, then the teaching period start date cannot be after the discontinued date of the program.
1496 IF l_program_attempt_status = 'DISCONTIN' AND p_ci_start_dt > TRUNC(p_sua_dtls_rec.discontinued_dt) THEN
1497 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_TCH_PRD_AFT_DIS');
1498 FND_MSG_PUB.ADD;
1499 g_sua_status := 'INVALID';
1500 END IF;
1501
1502 --
1503 -- Validate Primary Program Type :
1504 -- 15. If career centric, and program is a secondary (ie. non-primary) program then Enrolled units cannot be attached.
1505 IF p_career_model_enabled = 'Y' AND l_src_program_typ = 'SECONDARY' AND p_unit_attempt_status = 'ENROLLED' THEN
1506 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ENR_UNT_SEC_PRGM');
1507 FND_MSG_PUB.ADD;
1508 g_sua_status := 'INVALID';
1509 END IF ;
1510
1511 --
1512 -- Validate Enrolled Date :
1513 -- 16. If unit attempt is not duplicate, then the enrolled date cannot be prior to the program commencement (start) date.
1514 IF p_unit_attempt_status <> 'DUPLICATE' AND p_sua_dtls_rec.enrolled_dt IS NOT NULL AND TRUNC(p_sua_dtls_rec.enrolled_dt) < l_commencement_dt THEN
1515 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_BF_STAMPT' );
1516 FND_MSG_PUB.ADD;
1517 g_sua_status := 'INVALID';
1518 END IF ;
1519
1520 -- check to see if the load calendar (corresponding to the teaching calendar) end date is greater than the
1521 -- commencement date of the program attempt for ENROLLED, DISCONTIN, WAITLISTED and INVALID.
1522 IF p_unit_attempt_status IN ('ENROLLED','DISCONTIN','WAITLISTED','INVALID') THEN
1523 IF l_term_cal_dtls.load_end_dt < l_commencement_dt THEN
1524 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_BF_STAMPT' );
1525 FND_MSG_PUB.ADD;
1526 g_sua_status := 'INVALID';
1527 END IF;
1528 END IF;
1529
1530
1531 --
1532 -- Validate Research Units
1533 -- 17. Research unit must have teaching period covered by research supervision.
1534 -- 18. Must have research candidature to enroll in research unit
1535 l_boolean := igs_en_val_sua.resp_val_sua_cnfrm (
1536 p_person_id => p_person_id ,
1537 p_course_cd => p_sua_dtls_rec.program_cd,
1538 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1539 p_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1540 p_cal_type => p_cal_type ,
1541 p_ci_sequence_number => p_ci_sequence_number,
1542 p_message_name => l_message_name ,
1543 p_legacy => 'Y' ) ;
1544 IF l_message_name IS NOT NULL THEN
1545 g_sua_status := 'INVALID';
1546 END IF ;
1547
1548 --
1549 -- Validations for Dropped / Discontinuation Units :
1550 IF (p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL OR p_sua_dtls_rec.discontinued_dt IS NOT NULL OR
1551 p_sua_dtls_rec.administrative_unit_status IS NOT NULL OR p_sua_dtls_rec.dropped_ind IS NOT NULL) THEN
1552
1553 -- Validate Dropped / Discontinuation Date
1554 -- 19. Discontinued date cannot be a future date.
1555 -- 20. Discontinued date cannot be prior to unit attempt enrolled date.
1556 -- 21. Discontinued date must be set if administrative unit status is set.
1557 l_boolean := igs_en_val_sua.enrp_val_sua_discont(
1558 p_person_id => p_person_id ,
1559 p_course_cd => p_sua_dtls_rec.program_cd,
1560 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1561 p_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) ,
1562 p_ci_start_dt => p_ci_start_dt ,
1563 p_enrolled_dt => p_sua_dtls_rec.enrolled_dt,
1564 p_administrative_unit_status => NVL( p_sua_dtls_rec.administrative_unit_status , p_administrative_unit_status),
1565 p_unit_attempt_status => p_unit_attempt_status ,
1566 p_discontinued_dt => p_sua_dtls_rec.discontinued_dt,
1567 p_message_name => l_message_name ,
1568 p_legacy => 'Y' ) ;
1569 IF l_message_name IS NOT NULL THEN
1570
1571 g_sua_status := 'INVALID';
1572 END IF;
1573
1574 --
1575 -- 22. Administrative unit status can only be set if discontinued date is set.
1576 -- 23. Administrative unit status must be set if discontinued date is set.
1577 -- 24. If administrative unit status is set, then must be able to determine applicable grade from setup.
1578 l_boolean := igs_en_val_sua.enrp_val_discont_aus(
1579 p_administrative_unit_status => NVL( p_sua_dtls_rec.administrative_unit_status , p_administrative_unit_status),
1580 p_discontinued_dt => p_sua_dtls_rec.discontinued_dt ,
1581 p_cal_type => p_cal_type ,
1582 p_ci_sequence_number => p_ci_sequence_number ,
1583 p_message_name => l_message_name ,
1584 p_uoo_id => p_uoo_id ,
1585 p_message_token => l_message_token ,
1586 p_legacy => 'Y' );
1587 IF l_message_name IS NOT NULL THEN
1588 g_sua_status := 'INVALID';
1589 END IF ;
1590
1591 -- Validate Discontinuation Reason Code :
1592 -- 25. Discontinuation reason code must match a row with the unit flag set
1593 IF p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL THEN
1594 IF NOT igs_en_gen_legacy.validate_disc_rsn_cd (p_discontinuation_reason_cd => p_sua_dtls_rec.dcnt_reason_cd) THEN
1595 g_sua_status := 'INVALID';
1596 END IF ;
1597 END IF;
1598
1599 --
1600 -- 26. Discontinuation reason code can only be set when discontinued date is set
1601 IF p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL AND p_sua_dtls_rec.discontinued_dt IS NULL THEN
1602 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_DISC_RSN_CD_INV');
1603 FND_MSG_PUB.ADD;
1604 g_sua_status := 'INVALID';
1605 END IF ;
1606
1607 -- Validate Dropped Indicator :
1608 -- 27. If dropped indicator is set then enrolled date must be set
1609 IF p_sua_dtls_rec.dropped_ind = 'Y' AND p_sua_dtls_rec.enrolled_dt IS NULL THEN
1610 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_ENR_DT_NT_SET');
1611 FND_MSG_PUB.ADD;
1612 g_sua_status := 'INVALID';
1613 END IF ;
1614
1615 -- 28. If dropped indicator is set then discontinued date must not be set
1616 IF p_sua_dtls_rec.dropped_ind = 'Y' AND p_sua_dtls_rec.discontinued_dt IS NOT NULL THEN
1617 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_SUA_DRP_DISN_INV');
1618 FND_MSG_PUB.ADD;
1619 g_sua_status := 'INVALID';
1620 END IF ;
1621
1622 END IF ;
1623
1624 -- Validate Transfer Program Code 29-30
1625 -- 29. Transfer program code cannot be the same as the program of the unit attempt being imported.
1626
1627 IF p_sua_dtls_rec.transfer_program_cd IS NOT NULL THEN
1628 IF p_sua_dtls_rec.transfer_program_cd = p_sua_dtls_rec.program_cd THEN
1629 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_TOPRG_TRNS_FROM_PRG');
1630 FND_MSG_PUB.ADD;
1631 g_sua_status := 'INVALID';
1632 END IF;
1633 END IF ;
1634
1635 -- 30. If Transfer Date is specified , Transfer Program Code should be specified and vice versa.
1636 IF (p_sua_dtls_rec.transfer_program_cd IS NOT NULL AND p_sua_dtls_rec.transfer_dt is NULL )
1637 OR (p_sua_dtls_rec.transfer_program_cd IS NULL AND p_sua_dtls_rec.transfer_dt IS NOT NULL ) THEN
1638 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_TRN_DTLS_INCOMPL');
1639 FND_MSG_PUB.ADD;
1640 g_sua_status := 'INVALID';
1641 END IF ;
1642
1643 -- Validate unit to be transferred
1644 -- 31. If any of the transfer columns are specified, then another unit attempt must exist with matching person id,
1645 -- transfer program code, unit code, teaching alternate code (resolved to calendar type and sequence number),
1646 -- location code and unit class.
1647
1648 IF (p_sua_dtls_rec.transfer_program_cd IS NOT NULL OR p_sua_dtls_rec.transfer_dt IS NOT NULL ) THEN
1649
1650 IF NOT igs_en_gen_legacy.validate_trn_unit (
1651 p_person_id => p_person_id ,
1652 p_program_cd => p_sua_dtls_rec.transfer_program_cd ,
1653 p_cal_type => p_cal_type ,
1654 p_ci_sequence_number => p_ci_sequence_number ,
1655 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1656 p_location_cd => p_sua_dtls_rec.location_cd ,
1657 P_unit_class => p_sua_dtls_rec.unit_class ,
1658 p_unit_attempt_status => l_unit_attempt_status ) THEN
1659 g_sua_status := 'INVALID';
1660 END IF;
1661
1662 --
1663 -- Validate Discontinuation reason code of Program
1664 -- If unit transfer, and source program attempt is discontinued then it must have a reason code of type 'TRANSFER'
1665
1666 IF NOT igs_en_gen_legacy.validate_transfer (
1667 p_person_id => p_person_id ,
1668 p_transfer_program_cd => p_sua_dtls_rec.transfer_program_cd ) THEN
1669 g_sua_status := 'INVALID';
1670 END IF;
1671
1672 END IF ;
1673
1674 -- Validate Transfer Date
1675 IF p_sua_dtls_rec.transfer_dt IS NOT NULL AND ( p_sua_dtls_rec.enrolled_dt IS NOT NULL OR p_sua_dtls_rec.discontinued_dt IS NOT NULL OR
1676 p_sua_dtls_rec.administrative_unit_status IS NOT NULL OR p_sua_dtls_rec.dcnt_reason_cd IS NOT NULL OR
1677 p_sua_dtls_rec.no_assessment_ind IS NOT NULL OR p_sua_dtls_rec.override_enrolled_cp IS NOT NULL OR
1678 p_sua_dtls_rec.override_achievable_cp IS NOT NULL OR p_sua_dtls_rec.grading_schema_code IS NOT NULL OR
1679 p_sua_dtls_rec.gs_version_number IS NOT NULL OR p_sua_dtls_rec.subtitle IS NOT NULL OR
1680 p_sua_dtls_rec.outcome_dt IS NOT NULL OR p_sua_dtls_rec.mark IS NOT NULL OR p_sua_dtls_rec.grade IS NOT NULL ) THEN
1681 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_TRNSFR_DT_SET');
1682 FND_MSG_PUB.ADD;
1683 g_sua_status := 'INVALID';
1684 END IF ;
1685
1686 -- Validate Grade /Mark
1687 --38. Grade can only be set when outcome date is set, and visa versa.
1688 IF p_sua_dtls_rec.outcome_dt IS NULL AND p_sua_dtls_rec.grade IS NOT NULL THEN
1689 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OTCME_DT_REQD');
1690 FND_MSG_PUB.ADD;
1691 g_sua_status := 'INVALID';
1692 END IF ;
1693
1694 IF p_sua_dtls_rec.outcome_dt IS NOT NULL AND p_sua_dtls_rec.grade IS NULL THEN
1695 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OTCME_DT_NT_SET');
1696 FND_MSG_PUB.ADD;
1697 g_sua_status := 'INVALID';
1698 END IF ;
1699
1700 -- 39. Mark can only be set when outcome date is set.
1701 IF p_sua_dtls_rec.outcome_dt IS NULL AND p_sua_dtls_rec.mark IS NOT NULL THEN
1702 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OTCME_DT_REQD');
1703 FND_MSG_PUB.ADD;
1704 g_sua_status := 'INVALID';
1705 END IF ;
1706
1707 -- 40.If grade set and outcome grading schema code and version are not set, then grade must exist in the unit section default grading schema.
1708 IF p_sua_dtls_rec.grade IS NOT NULL AND (p_sua_dtls_rec.outcome_grading_schema_code IS NULL
1709 AND p_sua_dtls_rec.outcome_gs_version_number IS NULL ) THEN
1710 IF NOT igs_en_gen_legacy.validate_grading_schm (
1711 p_grade => p_sua_dtls_rec.grade ,
1712 p_uoo_id => p_uoo_id ,
1713 p_unit_cd => p_sua_dtls_rec.unit_cd ,
1714 p_version_number => NVL( p_sua_dtls_rec.version_number , p_version_number ) )THEN
1715 g_sua_status := 'INVALID';
1716 END IF;
1717 END IF ;
1718
1719 -- Validate outcome Date
1720 -- 41. If outcome date set, then enrolled date must be set and discontinued must not be set.
1721 IF (p_sua_dtls_rec.outcome_dt IS NOT NULL AND p_sua_dtls_rec.enrolled_dt IS NULL ) OR
1722 (p_sua_dtls_rec.outcome_dt IS NOT NULL AND p_sua_dtls_rec.discontinued_dt IS NOT NULL) THEN
1723 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_OUTCME_DT_NREQD');
1724 FND_MSG_PUB.ADD;
1725 g_sua_status := 'INVALID';
1726 END IF ;
1727
1728 -- 42.If adding completed unit attempt to a discontinued program attempt, the outcome date cannot be after the discontinuation date.
1729 IF l_program_attempt_status = 'DISCONTIN' AND p_sua_dtls_rec.outcome_dt IS NOT NULL THEN
1730
1731 OPEN get_prgm_discd_dt;
1732 FETCH get_prgm_discd_dt INTO l_prgm_discontinued_dt;
1733 CLOSE get_prgm_discd_dt ;
1734
1735 IF p_sua_dtls_rec.outcome_dt > l_prgm_discontinued_dt THEN
1736 FND_MESSAGE.SET_NAME( 'IGS' ,'IGS_EN_TRNSFER_INV');
1737 FND_MSG_PUB.ADD;
1738 g_sua_status := 'INVALID';
1739 END IF ;
1740 END IF ;
1741
1742 -- Validate waitlist Date
1743 -- 43. If waitlist date is set, then enrolled date and discontinued date must not be set.
1744 IF p_sua_dtls_rec.waitlisted_dt IS NOT NULL AND ( p_sua_dtls_rec.discontinued_dt IS NOT NULL OR p_sua_dtls_rec.enrolled_dt IS NOT NULL ) THEN
1745 FND_MESSAGE.SET_NAME( 'IGS', 'IGS_EN_ENR_CNT_WAITLST' );
1746 FND_MSG_PUB.ADD;
1747 g_sua_status := 'INVALID';
1748 END IF ;
1749
1750 --
1751 -- Validate Auditable
1752 IF NVL( p_sua_dtls_rec.no_assessment_ind , p_no_assessment_ind ) = 'Y' THEN
1753 --check if auditable flag is set at Unit section level .
1754
1755 OPEN get_audit_usec;
1756 FETCH get_audit_usec INTO l_auditable_ind ;
1757 CLOSE get_audit_usec ;
1758
1759 IF l_auditable_ind <> 'Y' THEN
1760 --check if auditable flag is set at Unit level .
1761 FND_MESSAGE.SET_NAME( 'IGS', 'IGS_EN_NO_ASS_IND_INV');
1762 FND_MSG_PUB.ADD;
1763 g_sua_status := 'INVALID';
1764 END IF ;
1765 END IF ;
1766
1767 l_message_name := NULL;
1768 IF NOT igs_en_elgbl_unit.eval_award_prog_only(
1769 p_person_id => p_person_id,
1770 p_person_type => NULL,
1771 p_load_cal_type => l_term_cal_dtls.load_cal_type,
1772 p_load_sequence_number => l_term_cal_dtls.load_ci_sequence_number,
1773 p_uoo_id => p_uoo_id,
1774 p_course_cd => p_sua_dtls_rec.program_cd,
1775 p_course_version => l_prog_version_spat,
1776 p_message => l_message_name,
1777 p_calling_obj => 'JOB'
1778 ) THEN
1779
1780 IF l_message_name IS NOT NULL THEN
1781 FND_MESSAGE.SET_NAME( 'IGS', l_message_name);
1782 FND_MSG_PUB.ADD;
1783 g_sua_status := 'INVALID';
1784 END IF;
1785
1786 END IF;
1787
1788 END validate_sua ;
1789
1790 PROCEDURE create_sua ( p_api_version IN NUMBER,
1791 p_init_msg_list IN VARCHAR2 ,
1792 p_commit IN VARCHAR2 ,
1793 p_validation_level IN NUMBER ,
1794 p_sua_dtls_rec IN sua_dtls_rec_type ,
1795 x_return_status OUT NOCOPY VARCHAR2,
1796 x_msg_count OUT NOCOPY NUMBER,
1797 x_msg_data OUT NOCOPY VARCHAR2)
1798 AS
1799 /*------------------------------------------------------------------
1800 Created By : SVENKATA
1801 Date Created By : 12-NOV-02
1802 Purpose : This routine is public in the API. The user makes a call to this routine to import
1803 Legacy Data onto OSS Tables. The routine validates data passed to it and inserts them
1804 into the corresponding OSS tables.
1805 Known limitations,
1806 enhancements,
1807 remarks :
1808 Change History
1809 Who When What
1810 svenkata 16-dec-02 Bug # 2708674 - columns No assessment indicator , student career transcript and student
1811 career statistics not defaulted.
1812 svenkata 30-Dec-02 Derived the Status of the Unit as COMPLETED if Grade is mentioned.Bug# 2727922
1813 kkillams 29-04-2003 Impacted object, due to change in the signature of the igs_en_gen_007.enrp_get_sua_status function
1814 w.r.t. bug number 2829262
1815 rvangala 01-OCT-2003 Added core_indicator value in the INSERT statements, added as part of Prevent Dropping Core Units. Enh Bug# 3052432
1816 ptandon 17-OCT-2003 Modified call to validate_sua to take into consideration two new OUT parameters p_sup_unit_cd and
1817 p_sup_unit_version_number and pass these values in the insert statements for IGS_EN_SU_ATTEMPT_ALL as part
1818 of Placements build. Enh Bug# 3052438.
1819 ------------------------------------------------------------------*/
1820
1821 --
1822 -- Cursor to get transfer Details
1823 CURSOR get_sua_trn (p_uoo_id IN NUMBER , p_person_id IN NUMBER , p_transfer_program_cd IN VARCHAR2 , P_unit_cd IN VARCHAR2 ) IS
1824 SELECT enrolled_dt, discontinued_dt, administrative_unit_status, dcnt_reason_cd,
1825 no_assessment_ind, override_enrolled_cp, override_achievable_cp,
1826 grading_schema_code, gs_version_number, subtitle, attribute_category ,
1827 attribute1 , attribute2 , attribute3 , attribute4 , attribute5 , attribute6,
1828 attribute7 , attribute8 , attribute9 , attribute10 , attribute11 , attribute12 ,
1829 attribute13 , attribute14 , attribute15 , attribute16 , attribute17 , attribute18 ,
1830 attribute19 , attribute20,upd_audit_flag,ss_source_ind
1831 FROM igs_en_su_attempt sua
1832 WHERE sua.person_id = p_person_id AND
1833 sua.course_cd = p_transfer_program_cd AND
1834 sua.unit_cd = p_unit_cd AND
1835 sua.uoo_id = p_uoo_id;
1836
1837
1838 CURSOR c_lock_parent_usec (cp_uoo_id IN NUMBER) IS
1839 SELECT uoo_id
1840 FROM igs_ps_unit_ofr_opt
1841 WHERE uoo_id=cp_uoo_id
1842 FOR UPDATE;
1843
1844 CURSOR c_admin_pri (cp_uoo_id igs_en_su_attempt.uoo_id%TYPE,cp_waitlist_dt DATE) IS
1845 SELECT NVL(MAX(administrative_priority),0)+1
1846 FROM igs_en_su_attempt
1847 WHERE uoo_id=cp_uoo_id
1848 AND waitlist_dt<=cp_waitlist_dt
1849 AND unit_attempt_status='WAITLISTED';
1850
1851
1852 TYPE sua_trnsfr_rec_tbl IS TABLE OF get_sua_trn%ROWTYPE INDEX BY BINARY_INTEGER ;
1853 sua_trnsfr_rec sua_trnsfr_rec_tbl;
1854
1855 l_api_name CONSTANT VARCHAR2(30) := 'create_sua';
1856 l_api_version CONSTANT NUMBER := 1.0;
1857 l_insert_flag BOOLEAN := TRUE;
1858
1859 l_primary_program_type igs_en_sca_v.course_type%TYPE ;
1860 l_person_id igs_pe_person.person_id%TYPE;
1861 l_adm_unit_status_ret igs_en_su_attempt.administrative_unit_Status %TYPE;
1862 l_adm_unit_status VARCHAR2(2000);
1863 l_alias_val igs_ca_da_inst.absolute_val%TYPE ;
1864
1865 l_org_unit_cd igs_or_unit.org_unit_cd%TYPE;
1866 l_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE;
1867 l_cal_type igs_en_su_attempt.cal_type%TYPE;
1868 l_ci_sequence_number igs_en_su_attempt.ci_Sequence_number%TYPE;
1869 l_ci_start_dt igs_ca_inst.start_dt%TYPE;
1870
1871 l_ci_end_dt igs_ca_inst.end_dt%TYPE;
1872 l_version_number igs_en_su_attempt.version_number%TYPE;
1873 l_unit_attempt_status igs_en_su_attempt.unit_attempt_status%TYPE;
1874 l_program_attempt_status igs_en_Stdnt_ps_Att.course_attempt_status%TYPE;
1875 l_career_model_enabled VARCHAR2(1);
1876 l_return_status VARCHAR2(30);
1877
1878 l_cart_status VARCHAR2(1);
1879
1880 l_count NUMBER := 0;
1881 l_first_char NUMBER := 0;
1882 l_current_string VARCHAR2(300) := NULL;
1883 l_msg_count NUMBER ;
1884 l_msg_data VARCHAR2(2000);
1885
1886 l_creation_date igs_en_su_attempt.creation_date%TYPE;
1887 l_last_update_date igs_en_su_attempt.last_update_date%TYPE;
1888 l_created_by igs_en_su_attempt.created_by%TYPE;
1889 l_last_updated_by igs_en_su_attempt.last_updated_by%TYPE;
1890 l_last_update_login igs_en_su_attempt.last_update_login%TYPE;
1891 l_request_id igs_en_su_attempt.request_id%TYPE;
1892 l_program_id igs_en_su_attempt.program_id%TYPE;
1893 l_program_application_id igs_en_su_attempt.program_application_id%TYPE;
1894 l_program_update_date igs_en_su_attempt.program_update_date%TYPE;
1895 l_no_assessment_ind igs_en_su_attempt.no_assessment_ind%TYPE;
1896 l_student_career_transcript igs_en_su_attempt.student_career_transcript%TYPE;
1897 l_student_career_statistics igs_en_su_attempt.student_career_statistics%TYPE;
1898 l_pref_weight NUMBER;
1899 l_pri_weight NUMBER;
1900 l_wlst_position NUMBER;
1901
1902 l_sup_unit_cd igs_ps_unit_ofr_opt.unit_cd%TYPE;
1903 l_sup_unit_version_number igs_ps_unit_ofr_opt.version_number%TYPE;
1904 cst_duplicate CONSTANT IGS_EN_SU_ATTEMPT.unit_attempt_status%TYPE := 'DUPLICATE';
1905 l_stat_sua_status igs_en_su_attempt.unit_attempt_status%TYPE;
1906 l_override_enrollment_cp igs_en_su_attempt.override_enrolled_cp%TYPE;
1907 l_override_achievable_cp igs_en_su_attempt.override_achievable_cp%TYPE;
1908 --
1909 -- The architecture of the Insert API is briefly described below. The validatins are done in the order mentioned below.
1910 -- 1. The routine validate_parameters is invoked to validate all params for Mandatory/check constraints
1911 -- 2. All required parameters are derived .
1912 -- 3. The DB constraints are validated by making a call to validate_db_cons.
1913 -- 4. Validate all Business rules by making a call to validate_sua.
1914 -- 5. If transfer Details are applicable, validate and Insert Iransfer Details.
1915 -- 6. Insert Student Unit Attempt Details.If transfer is applicable, insert values derived from source Unit Attempt.
1916 -- 7. Call post validation routines.This routine takes care of the following :
1917 -- Increment Unit section actual enrollment .
1918 -- Insert Student Unit Attempt Outcome details
1919 -- Re-derive the Program Attempt status. Update the SPA record with the new status.
1920 -- NOte : If an exception is encountered during any operation , the database is rolled back to a consistent
1921 -- state. A status of Unexpected error is returned to the calling Program . The Exception is not propogated to
1922 -- the calling routine.
1923 --
1924
1925 BEGIN
1926
1927 -- Create a savepoint
1928 SAVEPOINT CREATE_SUA_PUB;
1929
1930 -- Check for the Compatible API call
1931 IF NOT FND_API.COMPATIBLE_API_CALL( l_api_version,
1932 p_api_version,
1933 l_api_name,
1934 g_pkg_name) THEN
1935
1936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1937 END IF;
1938
1939 -- If the calling program has passed the parameter for initializing the message list
1940 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
1941 FND_MSG_PUB.INITIALIZE;
1942 END IF;
1943
1944 -- Set the return status to success
1945 x_return_status := FND_API.G_RET_STS_SUCCESS;
1946 g_sua_status := 'VALID';
1947
1948 --
1949 -- Derive default values if value is NULL
1950 IF p_sua_dtls_rec.no_assessment_ind IS NULL THEN
1951 l_no_assessment_ind := 'N';
1952 ELSE
1953 l_no_assessment_ind := p_sua_dtls_rec.no_assessment_ind;
1954 END IF ;
1955
1956 IF p_sua_dtls_rec.student_career_transcript IS NULL THEN
1957 l_student_career_transcript := 'Y';
1958 END IF ;
1959
1960 IF p_sua_dtls_rec.student_career_statistics IS NULL THEN
1961 l_student_career_statistics := 'Y' ;
1962 END IF ;
1963
1964 -- added by vijrajag for bug # 4235458
1965 IF l_no_assessment_ind = 'Y' THEN
1966
1967 l_override_enrollment_cp := 0;
1968 l_override_achievable_cp := 0;
1969 ELSE
1970 l_override_enrollment_cp := NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(l_person_id,l_uoo_id),
1971 p_sua_dtls_rec.override_enrolled_cp);
1972 l_override_achievable_cp := NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(l_person_id,l_uoo_id),
1973 p_sua_dtls_rec.override_achievable_cp);
1974 END IF;
1975
1976 -- 1.Validate Parameters
1977 validate_parameters(p_sua_dtls_rec => p_sua_dtls_rec);
1978
1979 -- 2.Derive all required parameters
1980 IF g_sua_status <> 'INVALID' THEN
1981
1982 -- Derive Person ID
1983 l_person_id := igs_ge_gen_003.get_person_id ( p_person_number => p_sua_dtls_rec.person_number) ;
1984 IF l_person_id IS NULL THEN
1985 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_GE_INVALID_PERSON_NUMBER');
1986 FND_MSG_PUB.ADD;
1987 g_sua_status := 'INVALID';
1988 END IF;
1989
1990 -- Derive Calendar Details.
1991 igs_ge_gen_003.get_calendar_instance(
1992 p_alternate_cd => p_sua_dtls_rec.teach_calendar_alternate_code ,
1993 p_s_cal_category => '''TEACHING''',
1994 p_cal_type => l_cal_type ,
1995 p_ci_sequence_number => l_ci_sequence_number ,
1996 p_start_dt => l_ci_start_dt ,
1997 p_end_dt => l_ci_end_dt ,
1998 p_return_status => l_return_status );
1999
2000 IF l_return_status = 'INVALID' THEN
2001 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ALT_CD_NO_CAL_FND ');
2002 FND_MSG_PUB.ADD;
2003 g_sua_status := 'INVALID';
2004 ELSIF l_return_status = 'MULTIPLE' THEN
2005 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_MULTI_TCH_CAL_FND');
2006 FND_MSG_PUB.ADD;
2007 g_sua_status := 'INVALID';
2008 END IF ;
2009
2010 -- Derive Unit version if parameter is NULL.
2011 IF p_sua_dtls_rec.version_number IS NULL THEN
2012 IF NOT igs_en_gen_legacy.get_unit_ver (
2013 p_cal_type => l_cal_type ,
2014 p_ci_sequence_number => l_ci_sequence_number ,
2015 p_unit_cd => p_sua_dtls_rec.unit_cd ,
2016 p_location_cd => p_sua_dtls_rec.location_cd ,
2017 P_unit_class => p_sua_dtls_rec.unit_class ,
2018 p_version_number => l_version_number ) THEN
2019
2020 g_sua_status := 'INVALID';
2021 END IF;
2022
2023 END IF;
2024
2025 -- Derive uoo_id
2026 IF NOT igs_en_gen_legacy.get_uoo_id (
2027 p_cal_type => l_cal_type ,
2028 p_ci_sequence_number => l_ci_sequence_number ,
2029 p_unit_cd => p_sua_dtls_rec.unit_cd ,
2030 p_location_cd => p_sua_dtls_rec.location_cd ,
2031 P_unit_class => p_sua_dtls_rec.unit_class ,
2032 p_version_number => NVL ( p_sua_dtls_rec.version_number,l_version_number) ,
2033 p_uoo_id => l_uoo_id ,
2034 p_owner_org_unit_cd => l_org_unit_cd ) THEN
2035
2036 g_sua_status := 'INVALID';
2037 END IF ;
2038
2039
2040 --Derive Unit Attempt Status
2041 IF p_sua_dtls_rec.dropped_ind = 'Y' THEN
2042 l_unit_attempt_status := 'DROPPED';
2043 ELSIF p_sua_dtls_rec.grade IS NOT NULL THEN
2044 l_unit_attempt_status := 'COMPLETED';
2045 ELSE
2046 -- IF p_sua_dtls_rec.transfer_dt IS NOT NULL OR p_sua_dtls_rec.outcome_dt IS NOT NULL OR THEN
2047 -- The Unit Attempt Status is passed as 'LEGACY' to the routine 'cos if NULL is passed , it tries to query the
2048 -- Unit Attempt Status that exists in the system , which is not valid for Legacy.
2049 l_unit_attempt_status := igs_en_gen_007.enrp_get_sua_status(
2050 p_person_id => l_person_id ,
2051 p_course_cd => p_sua_dtls_rec.program_cd ,
2052 p_unit_cd => p_sua_dtls_rec.unit_cd,
2053 p_version_number => NVL(p_sua_dtls_rec.version_number,l_version_number) ,
2054 p_cal_type => l_cal_type,
2055 p_ci_sequence_number => l_ci_sequence_number ,
2056 p_unit_attempt_status => 'LEGACY',
2057 p_enrolled_dt => p_sua_dtls_rec.enrolled_dt,
2058 p_rule_waived_dt => NULL,
2059 p_discontinued_dt => p_sua_dtls_rec.discontinued_dt,
2060 p_waitlisted_dt => p_sua_dtls_rec.waitlisted_dt,
2061 p_uoo_id => l_uoo_id);
2062 END IF;
2063
2064 -- Derive Transfer Details of Transfer Date is mentioned
2065 IF p_sua_dtls_rec.transfer_dt IS NOT NULL THEN
2066
2067 OPEN get_sua_trn (l_uoo_id , l_person_id ,p_sua_dtls_rec.transfer_program_cd ,p_sua_dtls_rec.unit_cd ) ;
2068 FETCH get_sua_trn INTO sua_trnsfr_rec(1) ;
2069 IF get_sua_trn%NOTFOUND THEN
2070 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_TRNSFR_UNT_NT_FND');
2071 FND_MSG_PUB.ADD;
2072 g_sua_status := 'INVALID';
2073 END IF ;
2074 CLOSE get_sua_trn ;
2075
2076 END IF;
2077
2078 -- Derive Administrative Unit Status
2079 IF p_sua_dtls_rec.discontinued_dt IS NOT NULL AND p_sua_dtls_rec.administrative_unit_status IS NULL THEN
2080 l_adm_unit_status_ret := igs_en_gen_008.enrp_get_uddc_aus (
2081 p_discontinued_dt => p_sua_dtls_rec.discontinued_dt ,
2082 p_cal_type => l_cal_type,
2083 p_ci_sequence_number => l_ci_sequence_number,
2084 p_admin_unit_status_str => l_adm_unit_status,
2085 p_alias_val => l_alias_val,
2086 p_uoo_id => l_uoo_id);
2087
2088 --
2089 -- The logic below should be incorporated to ensure that an error message is returned when multiple administrative_unit_status
2090 -- is returned by the routine enrp_get_uddc_aus.
2091
2092 IF l_adm_unit_status_ret IS NULL THEN
2093
2094 l_adm_unit_status_ret := NULL;
2095 l_first_char := 1;
2096 LOOP
2097
2098 -- exit when the end of the string is reached
2099 EXIT WHEN l_first_char >= LENGTH(l_adm_unit_status);
2100
2101 -- put 10 characters at a a time into a string for comparison
2102 l_current_string := (SUBSTR(l_adm_unit_status, l_first_char, 10));
2103 --1.Don't do anything if the string is null
2104
2105 IF (l_current_string IS NULL) THEN
2106 EXIT;
2107 ELSE
2108 IF l_adm_unit_status_ret IS NULL THEN
2109 l_adm_unit_status_ret := RTRIM(RPAD(l_current_string,10,' '));
2110 ELSE
2111 l_adm_unit_status_ret := l_adm_unit_status_ret||','||RTRIM(RPAD(l_current_string,10,' '));
2112 END IF;
2113 l_first_char := l_first_char + 11;
2114 END IF;
2115
2116 END LOOP;
2117 FND_MESSAGE.SET_NAME( 'IGS' , 'IGS_EN_ADM_UNT_STAT_INV');
2118 FND_MSG_PUB.ADD;
2119 g_sua_status := 'INVALID';
2120
2121 END IF;
2122 END IF;
2123
2124 -- Derive the Value of Profile for Career model Enabled.
2125 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'Y' THEN
2126 l_career_model_enabled := 'Y';
2127 ELSE
2128 l_career_model_enabled := 'N';
2129 END IF;
2130
2131 END IF; -- End Derivations.
2132
2133 IF g_sua_status <> 'INVALID' THEN
2134
2135 -- 3. The DB constraints are validated by making a call to validate_db_cons.
2136 validate_db_cons ( p_person_id => l_person_id ,
2137 p_unit_version_number => l_version_number ,
2138 p_uoo_id => l_uoo_id ,
2139 p_cal_type => l_cal_type ,
2140 p_ci_sequence_number => l_ci_Sequence_number ,
2141 p_sua_dtls_rec => p_sua_dtls_rec ) ;
2142
2143 END IF;
2144
2145 IF g_sua_status NOT IN ( 'INVALID' , 'WARNING' ) THEN
2146
2147 -- 4. Validate all Business rules by making a call to validate_sua.
2148
2149 validate_sua( p_person_id => l_person_id ,
2150 p_version_number => l_version_number ,
2151 p_sua_dtls_rec => p_sua_dtls_rec ,
2152 p_cal_type => l_cal_type ,
2153 p_ci_sequence_number => l_ci_Sequence_number ,
2154 p_ci_end_dt => l_ci_end_dt ,
2155 p_ci_start_dt => l_ci_start_dt ,
2156 p_unit_attempt_status => l_unit_attempt_status ,
2157 p_uoo_id => l_uoo_id ,
2158 p_career_model_enabled => l_career_model_enabled ,
2159 p_administrative_unit_status => l_adm_unit_status_ret,
2160 p_no_assessment_ind => l_no_assessment_ind ,
2161 p_primary_program_type => l_primary_program_type ,
2162 p_sup_unit_cd => l_sup_unit_cd ,
2163 p_sup_unit_version_number => l_sup_unit_version_number
2164 ) ;
2165 END IF ;
2166
2167 IF g_sua_status NOT IN ( 'INVALID' , 'WARNING' ) AND p_sua_dtls_rec.transfer_dt IS NOT NULL THEN
2168 validate_pre_sua( p_person_id => l_person_id ,
2169 p_sua_dtls_rec => p_sua_dtls_rec ,
2170 p_version_number => l_version_number,
2171 p_cal_type => l_cal_type ,
2172 p_ci_sequence_number => l_ci_Sequence_number,
2173 p_uoo_id => l_uoo_id);
2174
2175 IF g_sua_status <> 'INVALID' THEN
2176 --
2177 -- Re-derive the Unit Attempt status
2178 -- The Unit Attempt Status is passed as 'LEGACY' to the routine 'cos if NULL is passed , it tries to query the
2179 -- Unit Attempt Status that exists in the system , which is not valid for Legacy.
2180 l_unit_attempt_status := igs_en_gen_007.enrp_get_sua_status(
2181 p_person_id => l_person_id ,
2182 p_course_cd => p_sua_dtls_rec.program_cd ,
2183 p_unit_cd => p_sua_dtls_rec.unit_cd,
2184 p_version_number => NVL ( p_sua_dtls_rec.version_number,l_version_number) ,
2185 p_cal_type => l_cal_type,
2186 p_ci_sequence_number => l_ci_sequence_number ,
2187 p_unit_attempt_status => 'LEGACY' ,
2188 p_enrolled_dt => sua_trnsfr_rec(1).enrolled_dt,
2189 p_rule_waived_dt => NULL,
2190 p_discontinued_dt => sua_trnsfr_rec(1).discontinued_dt,
2191 p_waitlisted_dt => p_sua_dtls_rec.waitlisted_dt,
2192 p_uoo_id => l_uoo_id) ;
2193 END IF ;
2194
2195 END IF;
2196
2197 IF g_sua_status NOT IN ( 'INVALID' , 'WARNING' ) THEN
2198
2199 l_creation_date := SYSDATE;
2200 l_created_by := FND_GLOBAL.USER_ID;
2201 l_last_update_date := SYSDATE;
2202 l_last_updated_by := FND_GLOBAL.USER_ID;
2203 l_last_update_login :=FND_GLOBAL.LOGIN_ID;
2204
2205 IF l_created_by IS NULL THEN
2206 l_created_by := -1;
2207 END IF;
2208
2209 IF l_last_updated_by IS NULL THEN
2210 l_last_updated_by := -1;
2211 END IF;
2212
2213 IF l_last_update_login IS NULL THEN
2214 l_last_update_login := -1;
2215 END IF;
2216
2217 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
2218 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
2219 l_program_application_id := FND_GLOBAL.PROG_APPL_ID;
2220
2221 IF (l_request_id = -1) THEN
2222 l_request_id := NULL;
2223 l_program_id := NULL;
2224 l_program_application_id := NULL;
2225 l_program_update_date := NULL;
2226 ELSE
2227 l_program_update_date := SYSDATE;
2228 END IF;
2229
2230
2231 l_pri_weight:=NULL;
2232 l_pref_weight:=NULL;
2233 l_wlst_position:=NULL;
2234 g_wlst_pri_pref_exists:=FALSE;
2235 IF l_unit_attempt_status='WAITLISTED' THEN
2236 igs_en_wlst_gen_proc.enrp_wlst_pri_pref_calc( p_person_id => l_person_id,
2237 p_program_cd => p_sua_dtls_rec.program_cd,
2238 p_uoo_id => l_uoo_id,
2239 p_priority_weight => l_pri_weight,
2240 p_preference_weight=> l_pref_weight);
2241
2242
2243
2244 IF l_pri_weight IS NOT NULL AND l_pref_weight IS NOT NULL THEN
2245 g_wlst_pri_pref_exists:=TRUE;
2246 ELSE
2247 OPEN c_lock_parent_usec(l_uoo_id);
2248 CLOSE c_lock_parent_usec;
2249 OPEN c_admin_pri (l_uoo_id,p_sua_dtls_rec.waitlisted_dt);
2250 FETCH c_admin_pri INTO l_wlst_position;
2251 CLOSE c_admin_pri;
2252 END IF;
2253 END IF;
2254
2255 IF l_unit_attempt_status = 'UNCONFIRM' then
2256 l_cart_status := 'J';
2257 ELSE
2258 l_cart_status := 'N' ;
2259
2260 END IF ;
2261
2262 --
2263 -- If transfer is not applicable, insert values passed to the API . derived from source Unit Attempt.
2264 IF p_sua_dtls_rec.transfer_dt IS NULL THEN
2265
2266 INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
2267 person_id,
2268 course_cd,
2269 unit_cd,
2270 version_number,
2271 cal_type,
2272 ci_sequence_number,
2273 location_cd,
2274 unit_class,
2275 ci_start_dt,
2276 ci_end_dt,
2277 uoo_id,
2278 enrolled_dt,
2279 unit_attempt_status,
2280 administrative_unit_status,
2281 discontinued_dt,
2282 rule_waived_dt,
2283 rule_waived_person_id,
2284 no_assessment_ind,
2285 sup_unit_cd,
2286 sup_version_number,
2287 exam_location_cd,
2288 alternative_title,
2289 override_enrolled_cp,
2290 override_eftsu,
2291 override_achievable_cp,
2292 override_outcome_due_dt,
2293 override_credit_reason,
2294 administrative_priority,
2295 waitlist_dt,
2296 dcnt_reason_cd,
2297 creation_date,
2298 created_by,
2299 last_update_date,
2300 last_updated_by,
2301 last_update_login,
2302 request_id,
2303 program_id,
2304 program_application_id,
2305 program_update_date,
2306 org_id,
2307 gs_version_number,
2308 enr_method_type ,
2309 failed_unit_rule ,
2310 cart ,
2311 rsv_seat_ext_id ,
2312 org_unit_cd ,
2313 grading_schema_code ,
2314 subtitle,
2315 session_id,
2316 deg_aud_detail_id,
2317 student_career_transcript,
2318 student_career_statistics,
2319 waitlist_manual_ind ,
2320 attribute_category,
2321 attribute1,
2322 attribute2,
2323 attribute3,
2324 attribute4,
2325 attribute5,
2326 attribute6,
2327 attribute7,
2328 attribute8,
2329 attribute9,
2330 attribute10,
2331 attribute11,
2332 attribute12,
2333 attribute13,
2334 attribute14,
2335 attribute15,
2336 attribute16,
2337 attribute17,
2338 attribute18,
2339 attribute19,
2340 attribute20,
2341 wlst_priority_weight_num,
2342 wlst_preference_weight_num,
2343 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
2344 core_indicator_code,
2345 upd_audit_flag,
2346 ss_source_ind)
2347 VALUES (
2348 l_person_id,
2349 p_sua_dtls_rec.program_cd,
2350 p_sua_dtls_rec.unit_cd,
2351 NVL ( p_sua_dtls_rec.version_number , l_version_number),
2352 l_cal_type,
2353 l_ci_sequence_number,
2354 p_sua_dtls_rec.location_cd,
2355 p_sua_dtls_rec.unit_class,
2356 l_ci_start_dt,
2357 l_ci_end_dt,
2358 l_uoo_id,
2359 p_sua_dtls_rec.enrolled_dt,
2360 l_unit_attempt_status,
2361 NVL ( p_sua_dtls_rec.administrative_unit_status , l_adm_unit_status_ret),
2362 p_sua_dtls_rec.discontinued_dt,
2363 NULL ,
2364 NULL ,
2365 NVL( p_sua_dtls_rec.no_assessment_ind , l_no_assessment_ind ),
2366 l_sup_unit_cd ,
2367 l_sup_unit_version_number ,
2368 NULL ,
2369 NULL ,
2370 l_override_enrollment_cp,
2371 NULL ,
2372 l_override_achievable_cp,
2373 NULL ,
2374 NULL ,
2375 l_wlst_position,
2376 p_sua_dtls_rec.waitlisted_dt,
2377 p_sua_dtls_rec.dcnt_reason_cd,
2378 l_last_update_date,
2379 l_last_updated_by,
2380 l_last_update_date,
2381 l_last_updated_by,
2382 l_last_update_login,
2383 l_request_id,
2384 l_program_id,
2385 l_program_application_id,
2386 l_program_update_date,
2387 NULL ,
2388 p_sua_dtls_rec.gs_version_number ,
2389 NULL ,
2390 NULL ,
2391 l_cart_status ,
2392 NULL ,
2393 l_org_unit_cd ,
2394 p_sua_dtls_rec.grading_schema_code,
2395 p_sua_dtls_rec.subtitle,
2396 NULL ,
2397 NULL ,
2398 NVL( p_sua_dtls_rec.student_career_transcript , l_student_career_transcript ),
2399 NVL( p_sua_dtls_rec.student_career_statistics, l_student_career_statistics ),
2400 NULL ,
2401 p_sua_dtls_rec.attribute_category,
2402 p_sua_dtls_rec.attribute1,
2403 p_sua_dtls_rec.attribute2,
2404 p_sua_dtls_rec.attribute3,
2405 p_sua_dtls_rec.attribute4,
2406 p_sua_dtls_rec.attribute5,
2407 p_sua_dtls_rec.attribute6,
2408 p_sua_dtls_rec.attribute7,
2409 p_sua_dtls_rec.attribute8,
2410 p_sua_dtls_rec.attribute9,
2411 p_sua_dtls_rec.attribute10,
2412 p_sua_dtls_rec.attribute11,
2413 p_sua_dtls_rec.attribute12,
2414 p_sua_dtls_rec.attribute13,
2415 p_sua_dtls_rec.attribute14,
2416 p_sua_dtls_rec.attribute15,
2417 p_sua_dtls_rec.attribute16,
2418 p_sua_dtls_rec.attribute17,
2419 p_sua_dtls_rec.attribute18,
2420 p_sua_dtls_rec.attribute19,
2421 p_sua_dtls_rec.attribute20,
2422 l_pri_weight,
2423 l_pref_weight,
2424 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
2425 p_sua_dtls_rec.core_indicator,
2426 'N',
2427 'A'
2428 );
2429 l_stat_sua_status := l_unit_attempt_status;
2430 ELSE
2431 -- If transfer is applicable, insert values derived from source Unit Attempt.
2432 INSERT INTO IGS_EN_SU_ATTEMPT_ALL (
2433 person_id,
2434 course_cd,
2435 unit_cd,
2436 version_number,
2437 cal_type,
2438 ci_sequence_number,
2439 location_cd,
2440 unit_class,
2441 ci_start_dt,
2442 ci_end_dt,
2443 uoo_id,
2444 enrolled_dt,
2445 unit_attempt_status,
2446 administrative_unit_status,
2447 discontinued_dt,
2448 rule_waived_dt,
2449 rule_waived_person_id,
2450 no_assessment_ind,
2451 sup_unit_cd,
2452 sup_version_number,
2453 exam_location_cd,
2454 alternative_title,
2455 override_enrolled_cp,
2456 override_eftsu,
2457 override_achievable_cp,
2458 override_outcome_due_dt,
2459 override_credit_reason,
2460 administrative_priority,
2461 waitlist_dt,
2462 dcnt_reason_cd,
2463 creation_date,
2464 created_by,
2465 last_update_date,
2466 last_updated_by,
2467 last_update_login,
2468 request_id,
2469 program_id,
2470 program_application_id,
2471 program_update_date,
2472 org_id,
2473 gs_version_number,
2474 enr_method_type ,
2475 failed_unit_rule ,
2476 cart ,
2477 rsv_seat_ext_id ,
2478 org_unit_cd ,
2479 grading_schema_code ,
2480 subtitle,
2481 session_id,
2482 deg_aud_detail_id,
2483 student_career_transcript,
2484 student_career_statistics,
2485 waitlist_manual_ind ,
2486 attribute_category,
2487 attribute1,
2488 attribute2,
2489 attribute3,
2490 attribute4,
2491 attribute5,
2492 attribute6,
2493 attribute7,
2494 attribute8,
2495 attribute9,
2496 attribute10,
2497 attribute11,
2498 attribute12,
2499 attribute13,
2500 attribute14,
2501 attribute15,
2502 attribute16,
2503 attribute17,
2504 attribute18,
2505 attribute19,
2506 attribute20,
2507 wlst_priority_weight_num,
2508 wlst_preference_weight_num,
2509 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
2510 core_indicator_code,
2511 upd_audit_flag,
2512 ss_source_ind)
2513 VALUES (
2514 l_person_id,
2515 p_sua_dtls_rec.program_cd,
2516 p_sua_dtls_rec.unit_cd,
2517 NVL ( p_sua_dtls_rec.version_number , l_version_number),
2518 l_cal_type,
2519 l_ci_sequence_number,
2520 p_sua_dtls_rec.location_cd,
2521 p_sua_dtls_rec.unit_class,
2522 l_ci_start_dt,
2523 l_ci_end_dt,
2524 l_uoo_id,
2525 sua_trnsfr_rec(1).enrolled_dt,
2526 cst_duplicate ,
2527 NULL ,
2528 NULL ,
2529 NULL ,
2530 NULL ,
2531 NVL( p_sua_dtls_rec.no_assessment_ind , l_no_assessment_ind ),
2532 l_sup_unit_cd ,
2533 l_sup_unit_version_number ,
2534 NULL ,
2535 NULL ,
2536 NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(l_person_id,l_uoo_id),sua_trnsfr_rec(1).override_enrolled_cp),
2537 NULL ,
2538 NVL(IGS_EN_GEN_015.enrp_get_appr_cr_pt(l_person_id,l_uoo_id),sua_trnsfr_rec(1).override_achievable_cp),
2539 NULL ,
2540 NULL ,
2541 l_wlst_position,
2542 p_sua_dtls_rec.waitlisted_dt,
2543 NULL ,
2544 l_last_update_date,
2545 l_last_updated_by,
2546 l_last_update_date,
2547 l_last_updated_by,
2548 l_last_update_login,
2549 l_request_id,
2550 l_program_id,
2551 l_program_application_id,
2552 l_program_update_date,
2553 NULL ,
2554 sua_trnsfr_rec(1).gs_version_number ,
2555 NULL ,
2556 NULL ,
2557 l_cart_status ,
2558 NULL ,
2559 l_org_unit_cd ,
2560 sua_trnsfr_rec(1).grading_schema_code,
2561 sua_trnsfr_rec(1).subtitle,
2562 NULL ,
2563 NULL ,
2564 NVL( p_sua_dtls_rec.student_career_transcript , l_student_career_transcript ),
2565 NVL( p_sua_dtls_rec.student_career_statistics, l_student_career_statistics ),
2566 NULL ,
2567 sua_trnsfr_rec(1).attribute_category,
2568 sua_trnsfr_rec(1).attribute1,
2569 sua_trnsfr_rec(1).attribute2,
2570 sua_trnsfr_rec(1).attribute3,
2571 sua_trnsfr_rec(1).attribute4,
2572 sua_trnsfr_rec(1).attribute5,
2573 sua_trnsfr_rec(1).attribute6,
2574 sua_trnsfr_rec(1).attribute7,
2575 sua_trnsfr_rec(1).attribute8,
2576 sua_trnsfr_rec(1).attribute9,
2577 sua_trnsfr_rec(1).attribute10,
2578 sua_trnsfr_rec(1).attribute11,
2579 sua_trnsfr_rec(1).attribute12,
2580 sua_trnsfr_rec(1).attribute13,
2581 sua_trnsfr_rec(1).attribute14,
2582 sua_trnsfr_rec(1).attribute15,
2583 sua_trnsfr_rec(1).attribute16,
2584 sua_trnsfr_rec(1).attribute17,
2585 sua_trnsfr_rec(1).attribute18,
2586 sua_trnsfr_rec(1).attribute19,
2587 sua_trnsfr_rec(1).attribute20,
2588 l_pri_weight,
2589 l_pref_weight,
2590 --added by rvangala 01-OCT-2003. Enh Bug# 3052432
2591 p_sua_dtls_rec.core_indicator,
2592 'N',
2593 'A');
2594
2595 l_stat_sua_status := cst_duplicate;
2596 END IF ;
2597
2598 igs_en_gen_003.UPD_MAT_MRADM_CAT_TERMS(
2599 p_person_id => l_person_id,
2600 p_program_cd => p_sua_dtls_rec.program_cd,
2601 p_unit_attempt_status => l_stat_sua_status,
2602 p_teach_cal_type => l_cal_type,
2603 p_teach_ci_seq_num => l_ci_sequence_number);
2604
2605 validate_post_sua (p_person_id => l_person_id ,
2606 p_version_number => NVL ( p_sua_dtls_rec.version_number , l_version_number),
2607 p_sua_dtls_rec => p_sua_dtls_rec ,
2608 p_cal_type => l_cal_type ,
2609 p_ci_sequence_number => l_ci_sequence_number ,
2610 p_ci_end_dt => l_ci_end_dt ,
2611 p_ci_start_dt => l_ci_start_dt ,
2612 p_unit_attempt_status => l_unit_attempt_status ,
2613 p_uoo_id => l_uoo_id ,
2614 p_administrative_unit_status => l_adm_unit_status_ret ,
2615 p_career_centric => l_career_model_enabled ,
2616 p_primary_program_type => l_primary_program_type ,
2617 p_administrative_pri => l_wlst_position
2618 ) ;
2619 ELSE
2620 ROLLBACK TO CREATE_SUA_PUB;
2621 END IF;
2622
2623 --
2624 -- If the calling program has passed the parameter for committing the data and there
2625 -- have been no errors in calling the balances process, then commit the work
2626 IF ( (FND_API.To_Boolean(p_commit)) AND (g_sua_status = 'VALID') ) THEN
2627 COMMIT WORK;
2628 END IF;
2629
2630 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
2631 p_data => x_msg_data);
2632
2633 --
2634 -- Retutn Status to the calling program
2635 IF g_sua_status = 'INVALID' THEN
2636 ROLLBACK TO CREATE_SUA_PUB;
2637 x_return_status := FND_API.G_RET_STS_ERROR;
2638 ELSIF g_sua_status = 'WARNING' THEN
2639 ROLLBACK TO CREATE_SUA_PUB;
2640 x_return_status := 'W';
2641 END IF ;
2642
2643 EXCEPTION
2644 WHEN FND_API.G_EXC_ERROR THEN
2645 ROLLBACK TO CREATE_SUA_PUB;
2646 x_return_status := FND_API.G_RET_STS_ERROR;
2647 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
2648 p_data => x_msg_data);
2649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2650 ROLLBACK TO CREATE_SUA_PUB;
2651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2652 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
2653 p_data => x_msg_data);
2654 WHEN OTHERS THEN
2655 ROLLBACK TO CREATE_SUA_PUB;
2656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2657 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2658 FND_MSG_PUB.Add_Exc_Msg(g_pkg_name,
2659 l_api_name);
2660 END IF;
2661 FND_MSG_PUB.COUNT_AND_GET( p_count => x_msg_count,
2662 p_data => x_msg_data);
2663
2664
2665 END create_sua ;
2666
2667 END igs_en_sua_lgcy_pub ;
[Home] [Help]