1 PACKAGE BODY igs_as_suao_lgcy_pub AS
2 /* $Header: IGSPAS1B.pls 120.0 2005/07/05 12:03:21 appldev noship $ */
3
4 /***********************************************************************************************
5 ||
6 ||Created By: Arun Iyer
7 ||
8 ||Date Created By: 20-11-2002
9 ||
10 ||Purpose: This package creates a student Assessment unit outcome record.
11 ||
12 ||
13 ||Known limitations,enhancements,remarks:
14 ||
15 ||Change History
16 ||
17 ||Who When What
18 ||knaraset 14-May-2003 Modified the context of sua and suao to have location_cd and Unit_class or uoo_id,
19 || as part of MUS build bug 2829262
20 ||Aiyer 09-Jan-2003 Modified the function Validate_Unit_Outcome for the fix of
21 || the bug 2741946.
22 ||Aiyer 02-Jan-2003 Modified Function Validte_Parameters for the bug #2732559.
23 ||ijeddy 12-Apr-2005 Bug 4079384. set release_date to outcome_dt if finalised_outcome_ind = 'Y'
24 || and outcome_dt is not null.
25 ************************************************************************************************/
26
27
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGS_AS_SUAO_LGCY_PUB';
29
30 -- forward declaration of procedure/function used in this package
31
32 /*
33 validate_parameters function checks all the mandatory parameters
34 for the passed record type are not null
35 */
36
37 FUNCTION validate_parameters ( p_lgcy_suo_rec IN LGCY_SUO_REC_TYPE )
38 RETURN BOOLEAN ;
39
40
41
42 /*
43 Function Derive_unit_outcome_data - Derives the value for the following parameters: -
44 1. Person_id from person_number
45 2. Calendar type, calendar sequence number, calendar Start and End date from calendar alternate code
46 3. Derive the unit attempt status for the student unit attempt
47 4. Translated Grading schema code, Translated version number, Translated grade and Translated date
48 5. Derive the grading schems code and version number from the grade.
49 6. Derive the incomplete grading schema code and incomplete version number if they have not been specified.
50 */
51
52 FUNCTION derive_unit_outcome_data (
53 p_lgcy_suo_rec IN OUT NOCOPY LGCY_SUO_REC_TYPE ,
54 p_person_id IN OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE ,
55 p_cal_type IN OUT NOCOPY IGS_CA_INST.CAL_TYPE%TYPE ,
56 p_sequence_number IN OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
57 p_start_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.CI_START_DT%TYPE ,
58 p_end_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.CI_END_DT%TYPE ,
59 p_unit_attempt_status OUT NOCOPY IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ,
60 p_translated_grading_schema_cd OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADING_SCHEMA_CD%TYPE ,
61 p_translated_version_number OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_VERSION_NUMBER%TYPE ,
62 p_translated_grade OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADE%TYPE ,
63 p_translated_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_DT%TYPE ,
64 p_number_of_times OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.NUMBER_TIMES_KEYED%TYPE,
65 p_uoo_id OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.UOO_ID%TYPE,
66 p_release_date OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.RELEASE_DATE%TYPE
67 )
68 RETURN BOOLEAN;
69
70
71
72 /*
73 Function validate_suao_db_cons -
74 Performs all data integrity validations on the table igs_as_su_stmptout_all
75 It is called from the procedure create_unit_outcome.
76 */
77 FUNCTION validate_suao_db_cons (
78 p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE ,
79 p_lgcy_suo_rec IN LGCY_SUO_REC_TYPE ,
80 p_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE ,
81 p_sequence_number IN IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
82 p_translated_version_number IN IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_VERSION_NUMBER%TYPE ,
83 x_return_status OUT NOCOPY VARCHAR2,
84 p_uoo_id IN IGS_AS_SU_STMPTOUT_ALL.UOO_ID%TYPE
85 )
86 RETURN BOOLEAN;
87
88
89 /*
90 Procedure validate_unit_outcome -
91 Validates all the business validations before importing a record in the table IGS_AS_SU_STMPT_OUT_ALL.
92 Called from the procedure create_unit_outcome
93 */
94
95 FUNCTION validate_unit_outcome (
96 p_lgcy_suo_rec LGCY_SUO_REC_TYPE ,
97 p_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE
98 )
99
100 RETURN BOOLEAN ;
101
102
103
104 /*
105 Procedure create_post_unit_outcome -
106 Performs all the post insert operations on the table IGS_AS_SU_STMPTOUT_ALL.
107 Called from the procedure create_unit_outcome
108 */
109
110 PROCEDURE create_post_unit_outcome (
111 p_person_id IGS_PE_PERSON.PERSON_ID%TYPE ,
112 p_cal_type IGS_CA_INST.CAL_TYPE%TYPE ,
113 p_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
114 p_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ,
115 p_lgcy_suo_rec LGCY_SUO_REC_TYPE
116 );
117
118
119
120 PROCEDURE gen_log_info (p_msg IN VARCHAR2)
121 AS
122 /***********************************************************************************************
123 Created By: Arun Iyer
124
125 Date Created By: 19-11-2002
126
127 Purpose: This procedure is mainly used for debugging purposes.
128 Debug messages can be put in the code at various places to check the various
129 loops in the code.
130
131 Known limitations,enhancements,remarks:
132
133 Change History
134
135 Who When What
136 ********************************************************************************************** */
137
138 -- Variable Declarations.
139
140 BEGIN
141 null;
142 END gen_log_info;
143
144
145 PROCEDURE initialise ( p_lgcy_suo_rec IN OUT NOCOPY LGCY_SUO_REC_TYPE )
146 /************************************************************************************************************************
147 ||Created By : Aiyer
148 ||Date Created on : 2002/11/26
149 ||Purpose : This procedure initialises the record type fields to null
150 ||
151 ||
152 ||Know limitations, enhancements or remarks
153 ||Change History
154 ||Who When What
155 ||(reverse chronological order - newest change first)
156 *************************************************************************************************************************/
157 IS
158 BEGIN
159
160 p_lgcy_suo_rec.person_number := NULL;
161 p_lgcy_suo_rec.program_cd := NULL;
162 p_lgcy_suo_rec.unit_cd := NULL;
163 p_lgcy_suo_rec.teach_cal_alt_code := NULL;
164 p_lgcy_suo_rec.outcome_dt := NULL;
165 p_lgcy_suo_rec.grading_schema_cd := NULL;
166 p_lgcy_suo_rec.version_number := NULL;
167 p_lgcy_suo_rec.grade := NULL;
168 p_lgcy_suo_rec.s_grade_creation_method_type := NULL;
169 p_lgcy_suo_rec.finalised_outcome_ind := NULL;
170 p_lgcy_suo_rec.mark := NULL;
171 p_lgcy_suo_rec.incomp_deadline_date := NULL;
172 p_lgcy_suo_rec.incomp_grading_schema_cd := NULL;
173 p_lgcy_suo_rec.incomp_version_number := NULL;
174 p_lgcy_suo_rec.incomp_default_grade := NULL;
175 p_lgcy_suo_rec.incomp_default_mark := NULL;
176 p_lgcy_suo_rec.comments := NULL;
177 p_lgcy_suo_rec.grading_period_cd := NULL;
178 p_lgcy_suo_rec.attribute_category := NULL;
179 p_lgcy_suo_rec.attribute1 := NULL;
180 p_lgcy_suo_rec.attribute2 := NULL;
181 p_lgcy_suo_rec.attribute3 := NULL;
182 p_lgcy_suo_rec.attribute4 := NULL;
183 p_lgcy_suo_rec.attribute5 := NULL;
184 p_lgcy_suo_rec.attribute6 := NULL;
185 p_lgcy_suo_rec.attribute7 := NULL;
186 p_lgcy_suo_rec.attribute8 := NULL;
187 p_lgcy_suo_rec.attribute9 := NULL;
188 p_lgcy_suo_rec.attribute10 := NULL;
189 p_lgcy_suo_rec.attribute11 := NULL;
190 p_lgcy_suo_rec.attribute12 := NULL;
191 p_lgcy_suo_rec.attribute13 := NULL;
192 p_lgcy_suo_rec.attribute14 := NULL;
193 p_lgcy_suo_rec.attribute15 := NULL;
194 p_lgcy_suo_rec.attribute16 := NULL;
195 p_lgcy_suo_rec.attribute17 := NULL;
196 p_lgcy_suo_rec.attribute18 := NULL;
197 p_lgcy_suo_rec.attribute19 := NULL;
198 p_lgcy_suo_rec.attribute20 := NULL;
199 p_lgcy_suo_rec.location_cd := NULL;
200 p_lgcy_suo_rec.unit_class := NULL;
201
202 END initialise;
203
204
205
206 PROCEDURE create_unit_outcome
207 (p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
209 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
210 p_validation_level IN VARCHAR2 DEFAULT FND_API.G_VALID_LEVEL_FULL,
211 p_lgcy_suo_rec IN LGCY_SUO_REC_TYPE,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2
215 )
216 /************************************************************************************************************************
217 ||Created By : Aiyer
218 ||Date Created on : 2002/11/26
219 ||Purpose : The procedure create_unit_outcome creates a student unit outcome record in the table IGS_AS_SU_STMPT_OUT_ALL
220 || after doing the following validations :-
221 || 1. Validate the parameters passed to the api -- call function validate_parameters
222 || 2. Derive all the necessary values from the values passed -- call function derive_unit_outcome_data
223 || 3. Validate all the database constraints -- call function validate_suao_db_cons
224 || 4. Validate all the business validations -- call function validate_unit_outcome
225 || 5. Insert data into table IGS_AS_SU_STMPTOUT_ALL -- direct DML operation in this procedure
226 || 6. Perform post insert operations on table IGS_AS_SU_STMPTOUT_ALL -- call procedure create_post_unit_outcome
227 ||Know limitations, enhancements or remarks
228 ||Change History
229 ||Who When What
230 ||(reverse chronological order - newest change first)
231 *************************************************************************************************************************/
232 IS
233 l_api_name CONSTANT VARCHAR2(30) := 'create_unit_outcome' ;
234 l_api_version CONSTANT NUMBER := 1.0 ;
235 l_person_id IGS_PE_PERSON.PERSON_ID%TYPE ;
236 l_cal_type IGS_CA_INST.CAL_TYPE%TYPE ;
237 l_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE ;
238 l_start_dt IGS_AS_SU_STMPTOUT_ALL.CI_START_DT%TYPE ;
239 l_end_dt IGS_AS_SU_STMPTOUT_ALL.CI_END_DT%TYPE ;
240 l_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ;
241 l_translated_grading_schema_cd IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADING_SCHEMA_CD%TYPE ;
242 l_translated_version_number IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_VERSION_NUMBER%TYPE ;
243 l_translated_grade IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADE%TYPE ;
244 l_translated_dt IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_DT%TYPE ;
245 l_number_of_times IGS_AS_SU_STMPTOUT_ALL.NUMBER_TIMES_KEYED%TYPE ;
246 l_lgcy_suo_rec LGCY_SUO_REC_TYPE ;
247 l_uoo_id IGS_AS_SU_STMPTOUT_ALL.UOO_ID%TYPE;
248 l_release_date IGS_AS_SU_STMPTOUT_ALL.RELEASE_DATE%TYPE;
249 duplicate_record_exists EXCEPTION;
250 BEGIN
251 --Standard start of API savepoint
252 SAVEPOINT create_unit_outcome;
253
254 --Standard call to check for call compatibility.
255 IF NOT FND_API.Compatible_API_Call(
256 l_api_version,
257 p_api_version,
258 l_api_name,
259 G_PKG_NAME)
260 THEN
261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262 END IF;
263
264 --Initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean(p_init_msg_list) THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 --Initialize API return status to success.
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 /*
273 Assigning values to the the record type variable l_lgcy_suo_rec_type from p_lgcy_suo_rec_type
274 */
275
276 l_lgcy_suo_rec.person_number := p_lgcy_suo_rec.person_number ;
277 l_lgcy_suo_rec.program_cd := UPPER(p_lgcy_suo_rec.program_cd) ;
278 l_lgcy_suo_rec.unit_cd := UPPER(p_lgcy_suo_rec.unit_cd) ;
279 l_lgcy_suo_rec.teach_cal_alt_code := UPPER(p_lgcy_suo_rec.teach_cal_alt_code) ;
280 l_lgcy_suo_rec.outcome_dt := p_lgcy_suo_rec.outcome_dt ;
281 l_lgcy_suo_rec.grading_schema_cd := UPPER(p_lgcy_suo_rec.grading_schema_cd) ;
282 l_lgcy_suo_rec.version_number := p_lgcy_suo_rec.version_number ;
283 l_lgcy_suo_rec.grade := UPPER(p_lgcy_suo_rec.grade) ;
284 l_lgcy_suo_rec.s_grade_creation_method_type := UPPER(p_lgcy_suo_rec.s_grade_creation_method_type) ;
285 l_lgcy_suo_rec.finalised_outcome_ind := UPPER(p_lgcy_suo_rec.finalised_outcome_ind) ;
286 l_lgcy_suo_rec.mark := p_lgcy_suo_rec.mark ;
287 l_lgcy_suo_rec.incomp_deadline_date := p_lgcy_suo_rec.incomp_deadline_date ;
288 l_lgcy_suo_rec.incomp_grading_schema_cd := UPPER(p_lgcy_suo_rec.incomp_grading_schema_cd) ;
289 l_lgcy_suo_rec.incomp_version_number := p_lgcy_suo_rec.incomp_version_number ;
290 l_lgcy_suo_rec.incomp_default_grade := UPPER(p_lgcy_suo_rec.incomp_default_grade) ;
291 l_lgcy_suo_rec.incomp_default_mark := p_lgcy_suo_rec.incomp_default_mark ;
292 l_lgcy_suo_rec.comments := p_lgcy_suo_rec.comments ;
293 l_lgcy_suo_rec.grading_period_cd := UPPER(p_lgcy_suo_rec.grading_period_cd) ;
294 l_lgcy_suo_rec.attribute_category := p_lgcy_suo_rec.attribute_category ;
295 l_lgcy_suo_rec.attribute1 := p_lgcy_suo_rec.attribute1 ;
296 l_lgcy_suo_rec.attribute2 := p_lgcy_suo_rec.attribute2 ;
297 l_lgcy_suo_rec.attribute3 := p_lgcy_suo_rec.attribute3 ;
298 l_lgcy_suo_rec.attribute4 := p_lgcy_suo_rec.attribute4 ;
299 l_lgcy_suo_rec.attribute5 := p_lgcy_suo_rec.attribute5 ;
300 l_lgcy_suo_rec.attribute6 := p_lgcy_suo_rec.attribute6 ;
301 l_lgcy_suo_rec.attribute7 := p_lgcy_suo_rec.attribute7 ;
302 l_lgcy_suo_rec.attribute8 := p_lgcy_suo_rec.attribute8 ;
303 l_lgcy_suo_rec.attribute9 := p_lgcy_suo_rec.attribute9 ;
304 l_lgcy_suo_rec.attribute10 := p_lgcy_suo_rec.attribute10 ;
305 l_lgcy_suo_rec.attribute11 := p_lgcy_suo_rec.attribute11 ;
306 l_lgcy_suo_rec.attribute12 := p_lgcy_suo_rec.attribute12 ;
307 l_lgcy_suo_rec.attribute13 := p_lgcy_suo_rec.attribute13 ;
308 l_lgcy_suo_rec.attribute14 := p_lgcy_suo_rec.attribute14 ;
309 l_lgcy_suo_rec.attribute15 := p_lgcy_suo_rec.attribute15 ;
310 l_lgcy_suo_rec.attribute16 := p_lgcy_suo_rec.attribute16 ;
311 l_lgcy_suo_rec.attribute17 := p_lgcy_suo_rec.attribute17 ;
312 l_lgcy_suo_rec.attribute18 := p_lgcy_suo_rec.attribute18 ;
313 l_lgcy_suo_rec.attribute19 := p_lgcy_suo_rec.attribute19 ;
314 l_lgcy_suo_rec.attribute20 := p_lgcy_suo_rec.attribute20 ;
315 l_lgcy_suo_rec.location_cd := p_lgcy_suo_rec.location_cd ;
316 l_lgcy_suo_rec.unit_class := p_lgcy_suo_rec.unit_class ;
317
318 gen_log_info ('START OF FUNCTION CREATE UNIT OUTCOME');
319
320 /*************************************** Validation 1 ******************************************/
321
322 /*
323 Validate all the paramters passed to this api
324 */
325
326 gen_log_info('Start of create_unit_outcome.validation 1');
327
328 IF NOT VALIDATE_PARAMETERS( p_lgcy_suo_rec => l_lgcy_suo_rec ) THEN
329 RAISE FND_API.G_EXC_ERROR;
330 END IF;
331
332 gen_log_info('End of create_unit_outcome.validation 1');
333
334 /*************************************** Validation 2 ******************************************/
335
336 /*
337 Derive all the necessary columns eruiqred while importing a ercord in the table igs_as_su_stmptout_all
338 */
339 gen_log_info('Start of create_unit_outcome.validation 2');
340
341 IF NOT derive_unit_outcome_data (
342 p_lgcy_suo_rec => l_lgcy_suo_rec ,
343 p_person_id => l_person_id ,
344 p_cal_type => l_cal_type ,
345 p_sequence_number => l_sequence_number ,
346 p_start_dt => l_start_dt ,
347 p_end_dt => l_end_dt ,
348 p_unit_attempt_status => l_unit_attempt_status ,
349 p_translated_grading_schema_cd => l_translated_grading_schema_cd ,
350 p_translated_version_number => l_translated_version_number ,
351 p_translated_grade => l_translated_grade ,
352 p_translated_dt => l_translated_dt ,
353 p_number_of_times => l_number_of_times,
354 p_uoo_id => l_uoo_id,
355 p_release_date => l_release_date
356 )
357 THEN
358 RAISE FND_API.G_EXC_ERROR;
359 END IF;
360
361 gen_log_info('End of create_unit_outcome.validation 2');
362
363 /*************************************** Validation 3 ******************************************/
364 /*
365 Validate the data integrity rules on the table igs_as_stmptout_all before importing records.
366 */
367
368 gen_log_info('Start of create_unit_outcome.validation 3');
369
370 IF NOT validate_suao_db_cons (
371 p_person_id => l_person_id ,
372 p_lgcy_suo_rec => l_lgcy_suo_rec ,
373 p_cal_type => l_cal_type ,
374 p_sequence_number => l_sequence_number ,
375 p_translated_version_number => l_translated_version_number ,
376 x_return_status => x_return_status,
377 p_uoo_id => l_uoo_id
378 )
379 THEN
380 IF x_return_status = 'W' THEN
381 RAISE duplicate_record_exists;
382 ELSE
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385 END IF;
386
387 gen_log_info('End of create_unit_outcome.validation 3');
388
389 /*************************************** Validation 4 ******************************************/
390 /*
391 Validate the business rules on the table igs_as_stmptout_all before inporting records
392 */
393
394 gen_log_info('Start of create_unit_outcome.validation 4');
395
396 IF NOT validate_unit_outcome (
397 p_lgcy_suo_rec => l_lgcy_suo_rec ,
398 p_unit_attempt_status => l_unit_attempt_status
399 ) THEN
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402 gen_log_info('End of create_unit_outcome.validation 4');
403
404 /*************************************** Validation 5 ******************************************/
405 /*
406
407 Insert a record in the table igs_as_su_stmptout_all
408
409 */
410
411 gen_log_info('Start of create_unit_outcome.validation 5');
412
413 INSERT INTO IGS_AS_SU_STMPTOUT_ALL
414 (
415 PERSON_ID ,
416 COURSE_CD ,
417 UNIT_CD ,
418 CAL_TYPE ,
419 CI_SEQUENCE_NUMBER ,
420 CI_START_DT ,
421 CI_END_DT ,
422 OUTCOME_DT ,
423 GRADING_SCHEMA_CD ,
424 VERSION_NUMBER ,
425 GRADE ,
426 S_GRADE_CREATION_METHOD_TYPE ,
427 FINALISED_OUTCOME_IND ,
428 MARK ,
429 NUMBER_TIMES_KEYED ,
430 TRANSLATED_GRADING_SCHEMA_CD ,
431 TRANSLATED_VERSION_NUMBER ,
432 TRANSLATED_GRADE ,
433 TRANSLATED_DT ,
434 CREATED_BY ,
435 CREATION_DATE ,
436 LAST_UPDATED_BY ,
437 LAST_UPDATE_DATE ,
438 LAST_UPDATE_LOGIN ,
439 REQUEST_ID ,
440 PROGRAM_APPLICATION_ID ,
441 PROGRAM_ID ,
442 PROGRAM_UPDATE_DATE ,
443 ORG_ID ,
444 GRADING_PERIOD_CD ,
445 ATTRIBUTE_CATEGORY ,
446 ATTRIBUTE1 ,
447 ATTRIBUTE2 ,
448 ATTRIBUTE3 ,
449 ATTRIBUTE4 ,
450 ATTRIBUTE5 ,
451 ATTRIBUTE6 ,
452 ATTRIBUTE7 ,
453 ATTRIBUTE8 ,
454 ATTRIBUTE9 ,
455 ATTRIBUTE10 ,
456 ATTRIBUTE11 ,
457 ATTRIBUTE12 ,
458 ATTRIBUTE13 ,
459 ATTRIBUTE14 ,
460 ATTRIBUTE15 ,
461 ATTRIBUTE16 ,
462 ATTRIBUTE17 ,
463 ATTRIBUTE18 ,
464 ATTRIBUTE19 ,
465 ATTRIBUTE20 ,
466 INCOMP_DEADLINE_DATE ,
467 INCOMP_GRADING_SCHEMA_CD ,
468 INCOMP_VERSION_NUMBER ,
469 INCOMP_DEFAULT_GRADE ,
470 INCOMP_DEFAULT_MARK ,
471 COMMENTS,
472 UOO_ID,
473 RELEASE_DATE
474 )
475 VALUES
476 (
477 l_person_id ,
478 l_lgcy_suo_rec.program_cd ,
479 l_lgcy_suo_rec.unit_cd ,
480 l_cal_type ,
481 l_sequence_number ,
482 l_start_dt ,
483 l_end_dt ,
484 l_lgcy_suo_rec.outcome_dt ,
485 l_lgcy_suo_rec.grading_schema_cd ,
486 l_lgcy_suo_rec.version_number ,
487 l_lgcy_suo_rec.grade ,
488 l_lgcy_suo_rec.s_grade_creation_method_type ,
489 l_lgcy_suo_rec.finalised_outcome_ind ,
490 l_lgcy_suo_rec.mark ,
491 l_number_of_times ,
492 l_translated_grading_schema_cd ,
493 l_translated_version_number ,
494 l_translated_grade ,
495 l_translated_dt ,
496 nvl(fnd_global.user_id,-1) ,
497 SYSDATE ,
498 NVL(fnd_global.user_id,-1) ,
499 sysdate ,
500 NVL(fnd_global.login_id,-1) ,
501 DECODE(fnd_global.conc_request_id,-1,null,fnd_global.conc_request_id) ,
502 DECODE(fnd_global.conc_request_id,-1,null,fnd_global.prog_appl_id) ,
503 DECODE(fnd_global.conc_request_id,-1,null,fnd_global.conc_program_id) ,
504 DECODE(fnd_global.conc_request_id,-1,null,sysdate) ,
505 igs_ge_gen_003.get_org_id ,
506 l_lgcy_suo_rec.grading_period_cd ,
507 l_lgcy_suo_rec.attribute_category ,
508 l_lgcy_suo_rec.attribute1 ,
509 l_lgcy_suo_rec.attribute2 ,
510 l_lgcy_suo_rec.attribute3 ,
511 l_lgcy_suo_rec.attribute4 ,
512 l_lgcy_suo_rec.attribute5 ,
513 l_lgcy_suo_rec.attribute6 ,
514 l_lgcy_suo_rec.attribute7 ,
515 l_lgcy_suo_rec.attribute8 ,
516 l_lgcy_suo_rec.attribute9 ,
517 l_lgcy_suo_rec.attribute10 ,
518 l_lgcy_suo_rec.attribute11 ,
519 l_lgcy_suo_rec.attribute12 ,
520 l_lgcy_suo_rec.attribute13 ,
521 l_lgcy_suo_rec.attribute14 ,
522 l_lgcy_suo_rec.attribute15 ,
523 l_lgcy_suo_rec.attribute16 ,
524 l_lgcy_suo_rec.attribute17 ,
525 l_lgcy_suo_rec.attribute18 ,
526 l_lgcy_suo_rec.attribute19 ,
527 l_lgcy_suo_rec.attribute20 ,
528 l_lgcy_suo_rec.incomp_deadline_date ,
529 l_lgcy_suo_rec.incomp_grading_schema_cd ,
530 l_lgcy_suo_rec.incomp_version_number ,
531 l_lgcy_suo_rec.incomp_default_grade ,
532 l_lgcy_suo_rec.incomp_default_mark ,
533 l_lgcy_suo_rec.comments,
534 l_uoo_id,
535 l_release_date
536 );
537
538 gen_log_info('End of create_unit_outcome.validation 5');
539
540 /*************************************** Validation 6 ******************************************/
541
542 /*
543 Perform Post insert activities on the table igs_as_su_stmptout_all
544 */
545 gen_log_info('Start of create_unit_outcome.validation 6');
546
547 create_post_unit_outcome (
548 p_person_id => l_person_id ,
549 p_cal_type => l_cal_type ,
550 p_sequence_number => l_sequence_number ,
551 p_unit_attempt_status => l_unit_attempt_status ,
552 p_lgcy_suo_rec => l_lgcy_suo_rec
553 );
554
555 gen_log_info('End of create_unit_outcome.validation 6');
556
557
558 /*==================== End Of Code ===================================*/
559
560 --Standard check of p_commit.
561 IF FND_API.to_Boolean(p_commit) AND x_return_status = FND_API.G_RET_STS_SUCCESS THEN
562 commit;
563 END IF;
564
565 gen_log_info('END OF FUNCTION CREATE_UNIT_OUTCOME.VALIDATION');
566
567 RETURN ;
568
569 EXCEPTION
570 WHEN FND_API.G_EXC_ERROR THEN
571 ROLLBACK TO create_unit_outcome;
572 x_return_status := FND_API.G_RET_STS_ERROR;
573 FND_MSG_PUB.Count_And_Get(
574 p_count => x_msg_count,
575 p_data => x_msg_data);
576
577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578 ROLLBACK TO create_unit_outcome;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
580 FND_MSG_PUB.Count_And_Get(
581 p_count => x_msg_count,
582 p_data => x_msg_data
583 );
584 WHEN DUPLICATE_RECORD_EXISTS THEN
585 ROLLBACK TO create_unit_outcome;
586 x_return_status := 'W';
587 FND_MSG_PUB.Count_And_Get(
588 p_count => x_msg_count,
589 p_data => x_msg_data
590 );
591
592 WHEN OTHERS THEN
593 ROLLBACK TO create_unit_outcome;
594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
595 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_UNHANDLED_ERROR');
596 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
597 FND_MSG_PUB.ADD;
598
599 FND_MSG_PUB.Count_And_Get(
600 p_count => x_msg_count,
601 p_data => x_msg_data);
602
603 END create_unit_outcome;
604
605 FUNCTION validate_parameters ( p_lgcy_suo_rec IN LGCY_SUO_REC_TYPE )
606 RETURN BOOLEAN
607
608 /************************************************************************************************************************
609 ||Created By : Aiyer
610 ||Date Created on : 2002/11/26
611 ||
612 ||Purpose : The function validate_parameters validates the parameters passed to the api.
613 || It is called by the procedure create_unit_outcome.
614 ||
615 ||Know limitations, enhancements or remarks
616 ||Change History
617 ||Who When What
618 ||(reverse chronological order - newest change first)
619 ||Aiyer 02-Jan-2003 Validation 10 modified for the bug #2732559.
620 || Modified where clause to remove check for check for result_type = 'INCOMP'
621 *************************************************************************************************************************/
622
623 IS
624 l_return_status BOOLEAN := TRUE;
625 BEGIN
626
627 gen_log_info('START OF FUNCTION VALIDATE_PARAMETERS');
628
629 /*************************************** Validation 1 ******************************************/
630
631 gen_log_info('Start of validate_parameters.Validation 1');
632
633 IF p_lgcy_suo_rec.person_number IS NULL THEN
634 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PER_NUM_NULL');
635 FND_MSG_PUB.ADD;
636 l_return_status := FALSE;
637 END IF;
638
639 gen_log_info('End of validate_parameters.Validation 1');
640
641 /*************************************** Validation 2 ******************************************/
642
643 gen_log_info('Start of validate_parameters.Validation 2');
644
645 IF p_lgcy_suo_rec.program_cd IS NULL THEN
646 FND_MESSAGE.SET_NAME('IGS', 'IGS_EN_PRGM_CD_NULL');
647 FND_MSG_PUB.ADD;
648 l_return_status := FALSE;
649 END IF;
650
651 gen_log_info('End of validate_parameters.Validation 2');
652
653 /*************************************** Validation 3 ******************************************/
654 gen_log_info('Start of validate_parameters.Validation 3');
655 IF p_lgcy_suo_rec.unit_cd IS NULL THEN
656 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_UNIT_CD_NULL');
657 FND_MSG_PUB.ADD;
658 l_return_status := FALSE;
659 END IF;
660 gen_log_info('End of validate_parameters.Validation 3');
661 /*************************************** Validation 4 ******************************************/
662 gen_log_info('Start of validate_parameters.Validation 4');
663 IF p_lgcy_suo_rec.teach_cal_alt_code IS NULL THEN
664 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_TCAL_ALTCD_NULL');
665 FND_MSG_PUB.ADD;
666 l_return_status := FALSE;
667 END IF;
668 gen_log_info('End of validate_parameters.Validation 4');
669 /*************************************** Validation 5 ******************************************/
670 gen_log_info('Start of validate_parameters.Validation 5');
671 IF p_lgcy_suo_rec.outcome_dt IS NULL THEN
672 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_OTCM_DT_NULL');
673 FND_MSG_PUB.ADD;
674 l_return_status := FALSE;
675 END IF;
676 gen_log_info('End of validate_parameters.Validation 5');
677
678 /*************************************** Validation 6 ******************************************/
679
680 gen_log_info('Start of validate_parameters.Validation 6');
681 IF p_lgcy_suo_rec.grade IS NULL THEN
682 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_GRADE_NULL');
683 FND_MSG_PUB.ADD;
684 l_return_status := FALSE;
685 END IF;
686 gen_log_info('End of validate_parameters.Validation 6');
687
688 /*************************************** Validation 7 ******************************************/
689
690 gen_log_info('Start of validate_parameters.Validation 7');
691 IF p_lgcy_suo_rec.grading_period_cd IS NULL THEN
692 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_GRAD_PRD_NULL');
693 FND_MSG_PUB.ADD;
694 l_return_status := FALSE;
695 END IF;
696 gen_log_info('End of validate_parameters.Validation 7');
697
698 /*************************************** Validation 8 ******************************************/
699
700 /*
701 A not null value for both grading schema and version number should exist or both should be null.
702 */
703 gen_log_info('Start of validate_parameters.Validation 8');
704
705 IF p_lgcy_suo_rec.grading_schema_cd IS NOT NULL AND p_lgcy_suo_rec.version_number IS NULL THEN
706 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_GRDVER_NULL_NOTNULL');
707 FND_MSG_PUB.ADD;
708 l_return_status := FALSE;
709 ELSIF p_lgcy_suo_rec.grading_schema_cd IS NULL AND p_lgcy_suo_rec.version_number IS NOT NULL THEN
710 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_GRDVER_NULL_NOTNULL');
711 FND_MSG_PUB.ADD;
712 l_return_status := FALSE;
713 END IF;
714 gen_log_info('End of validate_parameters.Validation 7');
715
716 /*************************************** Validation 9 ******************************************/
717
718 gen_log_info('Start of validate_parameters.Validation 9');
719
720 /*
721 1. If an incomplete grading schema code is not null then the incomplete grading schema version number should be between
722 1 and 999 and should have a valid setup in table igs_as_grd_schema
723 2. ELSE if a incomp version number is not null then the incomp grading schema also needs to be not null
724 */
725
726 DECLARE
727 -- Check that a valid value for incomplete_grading_schema_cd and incomp_version_number already exists
728 CURSOR cur_incomp_grd_sch_exists
729 IS
730 SELECT
731 'X'
732 FROM
733 igs_as_grd_schema
734 WHERE
735 grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
736 version_number = p_lgcy_suo_rec.incomp_version_number;
737
738 l_exists VARCHAR2(1);
739
740 BEGIN
741 IF p_lgcy_suo_rec.incomp_grading_schema_cd IS NOT NULL THEN
742
743 IF NVL(p_lgcy_suo_rec.incomp_version_number,0) NOT BETWEEN 1 and 999 THEN
744 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INCMP_VERNUM_BET_0_999');
745 FND_MSG_PUB.ADD;
746 l_return_status := FALSE;
747 END IF;
748
749 /*
750 check if a valid value for both incomplete grading schema and incomplete grading schema version number exists in the set up
751 table igs_as_grd_schema.
752 IF not then raise an error message.
753 */
754 OPEN cur_incomp_grd_sch_exists;
755 FETCH cur_incomp_grd_sch_exists INTO l_exists;
756 IF cur_incomp_grd_sch_exists%NOTFOUND THEN
757 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INCMP_GRDVER_BOTNULL');
758 FND_MSG_PUB.ADD;
759 l_return_status := FALSE;
760 END IF;
761 CLOSE cur_incomp_grd_sch_exists;
762
763
764 ELSIF p_lgcy_suo_rec.incomp_grading_schema_cd IS NULL AND p_lgcy_suo_rec.incomp_version_number IS NOT NULL THEN
765 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INCMP_GRDVER_BOTNULL');
766 FND_MSG_PUB.ADD;
767 l_return_status := FALSE;
768 END IF;
769 END;
770
771 gen_log_info('End of validate_parameters.Validation 9');
772 /*************************************** Validation 10 ******************************************/
773
774 gen_log_info('Start of validate_parameters.Validation 10');
775
776 /*
777 1. If the incomplete default grade is not null then the incomp_grading_schema_cd and incomp_version_number
778 also needs to be not null.
779 2. If the incomplete default grade is not null then it should be defined within the incomplete
780 grading schema cd/version number
781 */
782
783 IF p_lgcy_suo_rec.incomp_default_grade IS NOT NULL THEN
784 -- Incomplete default grade is not null
785 IF p_lgcy_suo_rec.incomp_grading_schema_cd IS NULL OR p_lgcy_suo_rec.incomp_version_number IS NULL THEN
786 -- incomplete grading schema code or incomplete version has be sent to the API as null.
787 -- set an error message to stack
788 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INCMP_GRADE_NOT_EXIST');
789 FND_MSG_PUB.ADD;
790 l_return_status := FALSE;
791 ELSE
792 -- incomplete grading schema code and incomplete version both are not nulls
793 DECLARE
794 -- check whether the incomplete default grade lies within an incomplete grading schema /incomplete version number
795 /* Code modified for the bug 2732559. The Default grade need not be of Result Type 'Incomplete' */
796 CURSOR cur_incomp_grade_exists
797 IS
798 SELECT
799 'X'
800 FROM
801 igs_as_grd_sch_grade ggs
802 WHERE
803 grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
804 version_number = p_lgcy_suo_rec.incomp_version_number AND
805 grade = p_lgcy_suo_rec.incomp_default_grade;
806
807 l_exists VARCHAR2(1);
808 BEGIN
809 OPEN cur_incomp_grade_exists;
810 FETCH cur_incomp_grade_exists INTO l_exists;
811 IF cur_incomp_grade_exists%NOTFOUND THEN
812
813 /* set an error message as the incomplete default grade has not been defined for the incomplete
814 grading schema code/incomplete version number combination
815 */
816
817 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INVALID_INCMP_GRADE');
818 FND_MSG_PUB.ADD;
819 l_return_status := FALSE;
820 END IF;
821 CLOSE cur_incomp_grade_exists;
822 END;
823 END IF;
824 END IF;
825
826 gen_log_info('End of validate_parameters.Validation 10');
827
828 /*************************************** Validation 11 ******************************************/
829
830 /*
831 1. Incomplete grading schema and incomplete default grade cannot be null when a incomplete default mark
832 has been specified.
833 2. Incomplete default mark should lie within the range of marks for the given incomplete grading schemas code ,
834 incomplete_version number and incomplete default grade.
835 */
836
837 gen_log_info('Start of validate_parameters.Validation 11');
838
839 IF p_lgcy_suo_rec.incomp_default_mark IS NOT NULL THEN
840 -- Incomplete defalut mark is not null
841 IF p_lgcy_suo_rec.incomp_grading_schema_cd IS NULL OR p_lgcy_suo_rec.incomp_default_grade IS NULL THEN
842 -- incomplete grading schema code or incomplete default grade has been sent to the APi as null.
843 -- set an error message to stack
844 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INCMP_MARK_NOT_EXIST');
845 FND_MSG_PUB.ADD;
846 l_return_status := FALSE;
847 ELSE
848 -- incomplete grading schema code and incomplete default grade both are not nulls
849 DECLARE
850 -- check whether the incomplete default mark lies between the lower and upper mark ranges for a
851 -- incomplete grading schema ,incomplete version number and incomplete default grade.
852 CURSOR cur_chk_mark_range_valid
853 IS
854 SELECT
855 'X'
856 FROM
857 igs_as_grd_sch_grade ggs
858 WHERE
859 grading_schema_cd = p_lgcy_suo_rec.incomp_grading_schema_cd AND
860 version_number = p_lgcy_suo_rec.incomp_version_number AND
861 grade = p_lgcy_suo_rec.incomp_default_grade AND
862 p_lgcy_suo_rec.incomp_default_mark BETWEEN
863 NVL(LOWER_MARK_RANGE,p_lgcy_suo_rec.incomp_default_mark) AND
864 NVL(UPPER_MARK_RANGE,p_lgcy_suo_rec.incomp_default_mark) ;
865
866 l_exists VARCHAR2(1);
867 BEGIN
868 OPEN cur_chk_mark_range_valid;
869 FETCH cur_chk_mark_range_valid INTO l_exists;
870 IF cur_chk_mark_range_valid%NOTFOUND THEN
871
872 /* set an error message as the incomplete default mark does not lie within the lower and upper mark ranges for
873 the passed incomplete grading schema code, incomplete version number and incomplete default grade combination.
874 */
875
876 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_INVALID_INCMP_MARK');
877 FND_MSG_PUB.ADD;
878 l_return_status := FALSE;
879
880 END IF;
881 CLOSE cur_chk_mark_range_valid;
882 END;
883 END IF;
884 END IF;
885
886
887 gen_log_info('End of validate_parameters.Validation 11');
888
889 /*************************************** Validation 11 ******************************************/
890
891 gen_log_info('Start of validate_parameters.Validation 12');
892
893 /*
894 Validate that parameter p_lgcy_suo_rec.grading_period_cd cannot have any other values except 'MIDTERM' or 'FINAL'
895 */
896 IF p_lgcy_suo_rec.grading_period_cd <> 'MIDTERM' AND p_lgcy_suo_rec.grading_period_cd <> 'FINAL' THEN
897 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_GRD_PRD_INVALID');
898 FND_MSG_PUB.ADD;
899 l_return_status := FALSE;
900 END IF;
901
902 gen_log_info('End of validate_parameters.Validation 12');
903
904 /*************************************** Validation 13 ******************************************/
905
906 gen_log_info('Start of validate_parameters.Validation 13');
907 /*
908 Validate the flex definition
909 */
910
911 --
912 -- If any of the Descriptive Flex field columns have value, validate them .
913 -- Added to fix Bug#
914 IF (p_lgcy_suo_rec.attribute_category IS NOT NULL OR p_lgcy_suo_rec.attribute1 IS NOT NULL OR p_lgcy_suo_rec.attribute2 IS NOT NULL OR
915 p_lgcy_suo_rec.attribute3 IS NOT NULL OR p_lgcy_suo_rec.attribute4 IS NOT NULL OR p_lgcy_suo_rec.attribute5 IS NOT NULL OR
916 p_lgcy_suo_rec.attribute6 IS NOT NULL OR p_lgcy_suo_rec.attribute7 IS NOT NULL OR p_lgcy_suo_rec.attribute8 IS NOT NULL OR
917 p_lgcy_suo_rec.attribute9 IS NOT NULL OR p_lgcy_suo_rec.attribute10 IS NOT NULL OR p_lgcy_suo_rec.attribute11 IS NOT NULL OR
918 p_lgcy_suo_rec.attribute12 IS NOT NULL OR p_lgcy_suo_rec.attribute13 IS NOT NULL OR p_lgcy_suo_rec.attribute14 IS NOT NULL OR
919 p_lgcy_suo_rec.attribute15 IS NOT NULL OR p_lgcy_suo_rec.attribute16 IS NOT NULL OR p_lgcy_suo_rec.attribute17 IS NOT NULL OR
920 p_lgcy_suo_rec.attribute18 IS NOT NULL OR p_lgcy_suo_rec.attribute19 IS NOT NULL OR p_lgcy_suo_rec.attribute20 IS NOT NULL ) THEN
921 IF NOT IGS_AD_IMP_018.validate_desc_flex(
922 p_attribute_category => p_lgcy_suo_rec.attribute_category,
923 p_attribute1 => p_lgcy_suo_rec.attribute1 ,
924 p_attribute2 => p_lgcy_suo_rec.attribute2 ,
925 p_attribute3 => p_lgcy_suo_rec.attribute3 ,
926 p_attribute4 => p_lgcy_suo_rec.attribute4 ,
927 p_attribute5 => p_lgcy_suo_rec.attribute5 ,
928 p_attribute6 => p_lgcy_suo_rec.attribute6 ,
929 p_attribute7 => p_lgcy_suo_rec.attribute7 ,
930 p_attribute8 => p_lgcy_suo_rec.attribute8 ,
931 p_attribute9 => p_lgcy_suo_rec.attribute9 ,
932 p_attribute10 => p_lgcy_suo_rec.attribute10 ,
933 p_attribute11 => p_lgcy_suo_rec.attribute11 ,
934 p_attribute12 => p_lgcy_suo_rec.attribute12 ,
935 p_attribute13 => p_lgcy_suo_rec.attribute13 ,
936 p_attribute14 => p_lgcy_suo_rec.attribute14 ,
937 p_attribute15 => p_lgcy_suo_rec.attribute15 ,
938 p_attribute16 => p_lgcy_suo_rec.attribute16 ,
939 p_attribute17 => p_lgcy_suo_rec.attribute17 ,
940 p_attribute18 => p_lgcy_suo_rec.attribute18 ,
941 p_attribute19 => p_lgcy_suo_rec.attribute19 ,
942 p_attribute20 => p_lgcy_suo_rec.attribute20 ,
943 p_desc_flex_name => 'IGS_AS_SU_STMPTOUT_FLEX') THEN
944 l_return_status := FALSE;
945 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_INVALID_DESC_FLEX');
946 FND_MSG_PUB.ADD;
947 END IF;
948 END IF;
949
950 gen_log_info('End of validate_parameters.Validation 13');
951 gen_log_info('END OF FUNCTION VALIDATE_PARAMETERS');
952 return (l_return_status) ;
953 END validate_parameters;
954
955
956 FUNCTION derive_unit_outcome_data (
957 p_lgcy_suo_rec IN OUT NOCOPY LGCY_SUO_REC_TYPE ,
958 p_person_id IN OUT NOCOPY IGS_PE_PERSON.PERSON_ID%TYPE ,
959 p_cal_type IN OUT NOCOPY IGS_CA_INST.CAL_TYPE%TYPE ,
960 p_sequence_number IN OUT NOCOPY IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
961 p_start_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.CI_START_DT%TYPE ,
962 p_end_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.CI_END_DT%TYPE ,
963 p_unit_attempt_status OUT NOCOPY IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ,
964 p_translated_grading_schema_cd OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADING_SCHEMA_CD%TYPE ,
965 p_translated_version_number OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_VERSION_NUMBER%TYPE ,
966 p_translated_grade OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_GRADE%TYPE ,
967 p_translated_dt OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_DT%TYPE ,
968 p_number_of_times OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.NUMBER_TIMES_KEYED%TYPE,
969 p_uoo_id OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.UOO_ID%TYPE,
970 p_release_date OUT NOCOPY IGS_AS_SU_STMPTOUT_ALL.RELEASE_DATE%TYPE
971 )
972 RETURN BOOLEAN
973 /*************************************************************************************************************************
974 ||Created By : Aiyer
975 || Date Created on : 2002/11/20
976 || Purpose : The function derive_unit_outcome_data derives the value for the following parameters: -
977 || 1. Person_id from person_number
978 || 2. Calendar type, calendar sequence number, calendar Start and End date from calendar alternate code
979 || 3. Derive the unit attempt status for the student unit attempt
980 || 4. Translated Grading schema code, Translated version number, Translated grade and Translated date
981 || 5. Derive the grading schems code and version number from the grade.
982 || 6. Derive the incomplete grading schema code and incomplete version number if they have not been specified.
983 || 7. Derive the release_date: If finalised_outcome_ind and outcome_dt is not null then release_date = outcome_dt
984 || else its NULL
985 || It is called by the procedure create_unit_outcome
986 || Know limitations, enhancements or remarks
987 || Change History
988 || Who When What
989 || (reverse chronological order - newest change first)
990 ****************************************************************************************************************************/
991
992 IS
993 l_return_status BOOLEAN DEFAULT TRUE;
994 l_temp_status VARCHAR2(20);
995 BEGIN
996
997 gen_log_info('START OF FUNCTION DERIVE_UNIT_OUTCOME_DATA');
998
999 /*************************************** Validation 1 ******************************************/
1000 gen_log_info('Start of Validation 1');
1001
1002 /*
1003 Derive the person_id out the person_number
1004 */
1005
1006 p_person_id := IGS_GE_GEN_003.GET_PERSON_ID(p_lgcy_suo_rec.person_number);
1007 gen_log_info ('person_id : '||p_person_id);
1008 IF p_person_id IS NULL THEN
1009 FND_MESSAGE.SET_NAME('IGS', 'IGS_GE_INVALID_PERSON_NUMBER');
1010 FND_MSG_PUB.ADD;
1011 l_return_status := FALSE;
1012 END IF;
1013
1014 gen_log_info('End of Validation 1');
1015
1016 /************************************** Validation 2 *******************************************/
1017 gen_log_info('Start of Validation 2');
1018
1019 /*
1020 Derive the cal_type and sequence_number, start_dt and end_dt from the alternate code
1021 */
1022
1023 IGS_GE_GEN_003.GET_CALENDAR_INSTANCE
1024 (
1025 p_alternate_cd => p_lgcy_suo_rec.teach_cal_alt_code,
1026 p_s_cal_category => '''TEACHING''',
1027 p_cal_type => p_cal_type,
1028 p_ci_sequence_number => p_sequence_number,
1029 p_start_dt => p_start_dt,
1030 p_end_dt => p_end_dt,
1031 p_return_status => l_temp_status
1032 );
1033
1034 gen_log_info('cal type, sequence number,start_dt,end dt : '||p_cal_type||p_sequence_number||p_start_dt||p_end_dt);
1035
1036 IF p_cal_type IS NULL OR p_sequence_number IS NULL OR p_start_dt IS NULL THEN
1037 FND_MESSAGE.SET_NAME('IGS', 'IGS_AV_INVALID_CAL_ALT_CODE');
1038 FND_MSG_PUB.ADD;
1039 l_return_status := FALSE;
1040 END IF;
1041
1042 gen_log_info('End of Validation 2');
1043
1044 /************************************** Validation 3 *******************************************/
1045 gen_log_info('Start of Validation 3');
1046
1047 /*
1048 Derive the value for the out parameter p_unit_attempt_status
1049 */
1050
1051 DECLARE
1052 CURSOR cur_get_unit_attempt_status
1053 IS
1054 SELECT
1055 unit_attempt_status,
1056 uoo_id
1057 FROM
1058 igs_en_su_attempt_all su
1059 WHERE
1060 su.person_id = p_person_id
1061 AND su.course_cd = p_lgcy_suo_rec.program_cd
1062 AND su.unit_cd = p_lgcy_suo_rec.unit_cd
1063 AND su.cal_type = p_cal_type
1064 AND su.ci_sequence_number = p_sequence_number
1065 AND su.location_cd = p_lgcy_suo_rec.location_cd
1066 AND su.unit_class = p_lgcy_suo_rec.unit_class;
1067
1068 BEGIN
1069 OPEN cur_get_unit_attempt_status;
1070 FETCH cur_get_unit_attempt_status INTO p_unit_attempt_status,p_uoo_id;
1071 CLOSE cur_get_unit_attempt_status;
1072 END ;
1073
1074 gen_log_info('End of Validation 3');
1075
1076
1077 /************************************** Validation 4 *******************************************/
1078 gen_log_info('Start of Validation 4');
1079
1080 /*
1081 Derive the grading schema code and version number if these have been passed to this API as null as follows:
1082 1. IF a record with a default grading schema code exists at the unit section level then: -
1083 Validate that the grade passed to this API falls under this default grading schema (set up at the unit section level).
1084 IF such a record exists then default the value for the grading schema code and grading schema version number
1085 in the record type to this grading schema code and version number and go to validation 4.
1086 ELSE the situation indicates an invalid legacy grade entry,
1087 set the error message IGS_AS_INVALID_GRADE to stack and go to validation 4,
1088
1089 2. ELSIF a record with a default grading schema is set up at the unit level then: -
1090 Validate that the grade passed to this API falls under this default grading schema (set up at the unit level).
1091 IF such a record exists then default the value for the grading schema code and grading schema version number
1092 in the record type to this grading schema code and version number and go to validation 4.
1093 ELSE the situation indicates an invalid legacy grade entry,
1094 set the error message IGS_AS_INVALID_GRADE to stack and go to validation 4,
1095
1096 3. ELSE no set up for grading schema code has been done at both the unit and unit section level then set the error
1097 message IGS_AS_GRDSCH_SETUP_NOT_EXIST to stack and go to validation 4.
1098 */
1099
1100 IF p_lgcy_suo_rec.grading_schema_cd IS NULL OR p_lgcy_suo_rec.version_number IS NULL THEN
1101
1102 DECLARE
1103 -- get the default grading schema cd and version number from the unit section level and the corresponding grade
1104 CURSOR cur_get_grd_schm_usec_lvl
1105 IS
1106 SELECT
1107 ugs.grading_schema_code ,
1108 ugs.grd_schm_version_number
1109 FROM igs_ps_usec_grd_schm ugs ,
1110 igs_en_su_attempt_all en
1111 WHERE
1112 ugs.default_flag = 'Y' AND
1113 ugs.uoo_id = en.uoo_id AND
1114 en.person_id = p_person_id AND
1115 en.course_cd = p_lgcy_suo_rec.program_cd AND
1116 en.unit_cd = p_lgcy_suo_rec.unit_cd AND
1117 en.cal_type = p_cal_type AND
1118 en.ci_sequence_number = p_sequence_number AND
1119 en.location_cd = p_lgcy_suo_rec.location_cd AND
1120 en.unit_class = p_lgcy_suo_rec.unit_class;
1121
1122
1123 -- get the default grading schema cd and version number from the unit level and the corresponding grade
1124 CURSOR cur_get_grd_schm_unit_lvl
1125 IS
1126 SELECT
1127 ugs.grading_schema_code ,
1128 ugs.grd_schm_version_number
1129 FROM
1130 igs_ps_unit_grd_schm ugs ,
1131 igs_en_su_attempt_all en
1132 WHERE
1133 ugs.default_flag = 'Y' AND
1134 ugs.unit_code = en.unit_cd AND
1135 ugs.unit_version_number = en.version_number AND
1136 en.person_id = p_person_id AND
1137 en.course_cd = p_lgcy_suo_rec.program_cd AND
1138 en.unit_cd = p_lgcy_suo_rec.unit_cd AND
1139 en.cal_type = p_cal_type AND
1140 en.ci_sequence_number = p_sequence_number AND
1141 en.location_cd = p_lgcy_suo_rec.location_cd AND
1142 en.unit_class = p_lgcy_suo_rec.unit_class;
1143
1144
1145 -- get the grade for the corresponding grading schem /version number at unitsection/unit level
1146 CURSOR cur_get_grade (
1147 cp_grading_schema_cd IGS_AS_GRD_SCH_GRADE.GRADING_SCHEMA_CD%TYPE ,
1148 cp_version_number IGS_AS_GRD_SCH_GRADE.VERSION_NUMBER%TYPE ,
1149 cp_grade IGS_AS_GRD_SCH_GRADE.GRADE%TYPE
1150 )
1151 IS
1152 SELECT
1153 'X'
1154 FROM
1155 igs_as_grd_sch_grade
1156 WHERE
1157 grading_schema_cd = cp_grading_schema_cd AND
1158 version_number = cp_version_number AND
1159 grade = cp_grade;
1160
1161 rec_get_grd_schm_usec_lvl CUR_GET_GRD_SCHM_USEC_LVL%ROWTYPE;
1162 rec_get_grd_schm_unit_lvl CUR_GET_GRD_SCHM_UNIT_LVL%ROWTYPE;
1163 l_exists VARCHAR2(1) ;
1164
1165 BEGIN
1166 OPEN cur_get_grd_schm_usec_lvl;
1167 FETCH cur_get_grd_schm_usec_lvl INTO rec_get_grd_schm_usec_lvl;
1168
1169 IF cur_get_grd_schm_usec_lvl%FOUND THEN
1170 --A record with a default grading schema code exists at the unit section level
1171
1172
1173 -- check whether the grade passed to this API matches with the grade for the default grading schema
1174 -- set up at the unit section level.
1175 OPEN cur_get_grade ( cp_grading_schema_cd => rec_get_grd_schm_usec_lvl.grading_schema_code ,
1176 cp_version_number => rec_get_grd_schm_usec_lvl.grd_schm_version_number ,
1177 cp_grade => p_lgcy_suo_rec.grade
1178 );
1179 FETCH cur_get_grade INTO l_exists;
1180
1181 IF cur_get_grade%NOTFOUND THEN
1182 -- Situation indicates an invalid legacy grade entry
1183 -- set the error message IGS_AS_INVALID_GRADE to stack and go to validation 4
1184 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_INVALID_GRADE');
1185 FND_MSG_PUB.ADD;
1186 l_return_status := FALSE;
1187 ELSE
1188 -- both the grades are equal.
1189 -- Default the record type grading schema code and version number to the grading schema and version number
1190 -- at the unit section level
1191 p_lgcy_suo_rec.grading_schema_cd := rec_get_grd_schm_usec_lvl.grading_schema_code ;
1192 p_lgcy_suo_rec.version_number := rec_get_grd_schm_usec_lvl.grd_schm_version_number ;
1193 END IF;
1194
1195 CLOSE cur_get_grade;
1196
1197 ELSE
1198 -- A record with a default grading schema does not exist at the unit Section level
1199 OPEN cur_get_grd_schm_unit_lvl;
1200 FETCH cur_get_grd_schm_unit_lvl INTO rec_get_grd_schm_unit_lvl;
1201
1202 --check whether such a setup exists at the unit level.
1203 IF cur_get_grd_schm_unit_lvl%FOUND THEN
1204 -- A record with a default grading schema code exists at the unit level
1205 -- check whether the grade passed to this API matches with the grade for the default grading schema
1206 -- set up at the unit level.
1207 OPEN cur_get_grade (
1208 cp_grading_schema_cd => rec_get_grd_schm_unit_lvl.grading_schema_code ,
1209 cp_version_number => rec_get_grd_schm_unit_lvl.grd_schm_version_number ,
1210 cp_grade => p_lgcy_suo_rec.grade
1211 );
1212 FETCH cur_get_grade INTO l_exists;
1213
1214 IF cur_get_grade%NOTFOUND THEN
1215 -- Situation indicates an invalid legacy grade entry
1216 -- set the error message IGS_AS_INVALID_GRADE to stack and go to validation 4
1217 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_INVALID_GRADE');
1218 FND_MSG_PUB.ADD;
1219 l_return_status := FALSE;
1220 ELSE
1221 -- both the grades are equal.
1222 -- Default the record type grading schema code and version number to the grading schema and version number
1223 -- at the unit section level
1224 p_lgcy_suo_rec.grading_schema_cd := rec_get_grd_schm_unit_lvl.grading_schema_code ;
1225 p_lgcy_suo_rec.version_number := rec_get_grd_schm_unit_lvl.grd_schm_version_number ;
1226 END IF;
1227
1228 CLOSE cur_get_grade;
1229 ELSE
1230 --A Grading schema setup does not exist at the unit section level and the unit level
1231 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_GRDSCH_SETUP_NOT_EXIST');
1232 FND_MSG_PUB.ADD;
1233 l_return_status := FALSE;
1234 END IF;
1235 CLOSE cur_get_grd_schm_unit_lvl;
1236 END IF;
1237 CLOSE cur_get_grd_schm_usec_lvl;
1238
1239 END; -- end of local block.
1240
1241 END IF;
1242
1243 gen_log_info('End of Validation 4');
1244
1245 /************************************** Validation 4 *******************************************/
1246 gen_log_info('Start of Validation 5');
1247
1248 IF p_lgcy_suo_rec.s_grade_creation_method_type IS NULL THEN
1249 -- set the value of grade creation method type to 'CONVERSION' in case it is passed as null to create_unit_outcome
1250 p_lgcy_suo_rec.s_grade_creation_method_type := 'CONVERSION';
1251 END IF;
1252 gen_log_info('End of Validation 5');
1253
1254 /************************************** Validation 6 *******************************************/
1255 gen_log_info('Start of Validation 6');
1256
1257 IF p_lgcy_suo_rec.finalised_outcome_ind IS NULL THEN
1258 -- set the value of finalised outcome Indicator to 'Y' in case it is passed as null to create_unit_outcome
1259 p_lgcy_suo_rec.finalised_outcome_ind := 'Y';
1260 END IF;
1261
1262 gen_log_info('End of Validation 6');
1263
1264 /************************************** Validation 7*******************************************/
1265 gen_log_info('Start of Validation 7');
1266
1267 /*
1268 The incomp_grading schema/version only need to be defaulted ( if these are passed with null values to the api )
1269 if a grade with a result type of incomplete grade has been specified, for the grading_schema and version
1270 number otherwise these would remain null.
1271 */
1272 IF p_lgcy_suo_rec.incomp_grading_schema_cd IS NULL OR p_lgcy_suo_rec.incomp_version_number IS NULL THEN
1273
1274 DECLARE
1275 CURSOR cur_incomp_grade_exists
1276 IS
1277 SELECT
1278 'X'
1279 FROM
1280 igs_as_grd_sch_grade ggs
1281 WHERE
1282 grading_schema_cd = p_lgcy_suo_rec.grading_schema_cd AND
1283 version_number = p_lgcy_suo_rec.version_number AND
1284 s_result_type = 'INCOMP';
1285
1286 lv_exists VARCHAR2(1);
1287 BEGIN
1288 OPEN cur_incomp_grade_exists;
1289 FETCH cur_incomp_grade_exists INTO lv_exists;
1290 IF cur_incomp_grade_exists%FOUND THEN
1291 -- If an incomplete grade exists then default the incomplete grading schema code and version number
1292 -- to the grading schema code and version number of the record type passed
1293 -- ELSE they would remain as NULLS
1294 p_lgcy_suo_rec.incomp_grading_schema_cd := p_lgcy_suo_rec.grading_schema_cd ;
1295 p_lgcy_suo_rec.incomp_version_number := p_lgcy_suo_rec.version_number ;
1296 END IF;
1297 CLOSE cur_incomp_grade_exists;
1298
1299 END;
1300
1301 END IF;
1302
1303 gen_log_info('End of Validation 7');
1304
1305 /************************************** Validation 8*******************************************/
1306 gen_log_info('Start of Validation 8');
1307
1308 /*
1309 Set the value for the out parameter p_number_of_times to 1.
1310 */
1311
1312 p_number_of_times := 1;
1313
1314 gen_log_info('End of Validation 8');
1315 /************************************** Validation 9*******************************************/
1316 gen_log_info('Start of Validation 9');
1317
1318 /*
1319 If the record parameter Finalised_Outcome_Ind is set to 'Y' then derive the value for p_translated_grading_schema_cd,
1320 p_translated_version_number, p_translated_grade, p_translated_dt from the fields person_id, program_cd,
1321 unit_cd, cal_type, sequence_number, grading_schema_cd, version_number, grade and mark.
1322 */
1323
1324 IF p_lgcy_suo_rec.finalised_outcome_ind = 'Y' THEN
1325 DECLARE
1326 -- If the grading schema precedence number is set to N at the unit section level then
1327 -- retrieve the grading schema code and version number set at the program offering pattern level
1328 -- for the corresponding student unit attempt.
1329 CURSOR cur_cop (
1330 cp_acad_cal_type IGS_PS_OFR_PAT.CAL_TYPE%TYPE ,
1331 cp_acad_ci_seq_num IGS_PS_OFR_PAT.CI_SEQUENCE_NUMBER%TYPE
1332 )
1333 IS
1334 SELECT
1335 cop.grading_schema_cd,
1336 cop.gs_version_number
1337 FROM
1338 igs_en_su_attempt sua,
1339 igs_en_stdnt_ps_att sca,
1340 igs_ps_unit_ofr_opt uoo,
1341 igs_ps_ofr_pat cop
1342 WHERE
1343 sua.person_id = p_person_id
1344 AND sua.course_cd = p_lgcy_suo_rec.program_cd
1345 AND sua.unit_cd = p_lgcy_suo_rec.unit_cd
1346 AND sua.cal_type = p_cal_type
1347 AND sua.ci_sequence_number = p_sequence_number
1348 AND sua.location_cd = p_lgcy_suo_rec.location_cd
1349 AND sua.unit_class = p_lgcy_suo_rec.unit_class
1350 AND uoo.uoo_id = sua.uoo_id
1351 AND uoo.grading_schema_prcdnce_ind = 'N'
1352 AND sca.person_id = sua.person_id
1353 AND sca.course_cd = sua.course_cd
1354 AND cop.coo_id = sca.coo_id
1355 AND cop.cal_type = cp_acad_cal_type
1356 AND cop.ci_sequence_number = cp_acad_ci_seq_num
1357 AND cop.grading_schema_cd is not null
1358 AND cop.gs_version_number is not null;
1359
1360 -- get the to grade for the grading schema and to grading schema (set at the unit section/unit
1361 -- level and the program offering pattern level.
1362 CURSOR cur_gsgt(
1363 cp_grading_schema_cd IGS_PS_OFR_PAT.GRADING_SCHEMA_CD%TYPE,
1364 cp_gs_ver_num IGS_PS_OFR_PAT.GS_VERSION_NUMBER%TYPE
1365 )
1366 IS
1367 SELECT
1368 gsgt.to_grade
1369 FROM
1370 igs_as_grd_sch_trn gsgt
1371 WHERE
1372 gsgt.grading_schema_cd = p_lgcy_suo_rec.grading_schema_cd
1373 AND gsgt.version_number = p_lgcy_suo_rec.version_number
1374 AND gsgt.grade = p_lgcy_suo_rec.grade
1375 AND gsgt.to_grading_schema_cd = cp_grading_schema_cd
1376 AND gsgt.to_version_number = cp_gs_ver_num;
1377
1378 rec_cur_cop CUR_COP%ROWTYPE ;
1379 l_grade IGS_AS_GRD_SCH_TRN.TO_GRADE%TYPE ;
1380 l_alt_code IGS_CA_INST.ALTERNATE_CODE%TYPE ;
1381 l_acad_cal_type IGS_CA_INST.CAL_TYPE%TYPE ;
1382 l_acad_ci_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE ;
1383 l_acad_ci_start_dt IGS_CA_INST.START_DT%TYPE ;
1384 l_acad_ci_end_dt IGS_CA_INST.END_DT%TYPE ;
1385 l_message_name VARCHAR2(1000) ;
1386
1387 BEGIN
1388
1389 l_alt_code := IGS_EN_GEN_002.enrp_get_acad_alt_cd (
1390 P_CAL_TYPE => p_cal_type ,
1391 P_CI_SEQUENCE_NUMBER => p_sequence_number ,
1392 P_ACAD_CAL_TYPE => l_acad_cal_type ,
1393 P_ACAD_CI_SEQUENCE_NUMBER => l_acad_ci_sequence_number ,
1394 P_ACAD_CI_START_DT => l_acad_ci_start_dt ,
1395 P_ACAD_CI_END_DT => l_acad_ci_end_dt ,
1396 P_MESSAGE_NAME => l_message_name
1397 );
1398
1399 IF l_message_name IS NOT NULL THEN
1400 FND_MESSAGE.SET_NAME('IGS', l_message_name);
1401 FND_MSG_PUB.ADD;
1402 l_return_status := FALSE;
1403 ELSE
1404 OPEN cur_cop(
1405 l_acad_cal_type ,
1406 l_acad_ci_sequence_number
1407 );
1408 FETCH cur_cop INTO rec_cur_cop;
1409
1410 IF cur_cop%FOUND THEN
1411 --Validate that their exists a grade mapping.
1412 OPEN cur_gsgt(
1413 rec_cur_cop.grading_schema_cd,
1414 rec_cur_cop.gs_version_number
1415 );
1416 FETCH cur_gsgt INTO l_grade;
1417 IF cur_gsgt%FOUND THEN
1418 -- Set the fields to the new translation.
1419 p_translated_grading_schema_cd := rec_cur_cop.grading_schema_cd ;
1420 p_translated_version_number := rec_cur_cop.gs_version_number ;
1421 p_translated_grade := l_grade ;
1422 p_translated_dt := SYSDATE ;
1423 END IF; -- end of innermost if
1424 CLOSE cur_gsgt;
1425 END IF; -- end of outer if
1426 CLOSE cur_cop;
1427 END IF; -- end of outermost if
1428 END ;
1429 END IF;
1430 gen_log_info('End of Validation 9');
1431 /************************************** Validation 10*******************************************/
1432 gen_log_info('Start of Validation 10');
1433 IF p_lgcy_suo_rec.finalised_outcome_ind = 'Y' AND p_lgcy_suo_rec.outcome_dt IS NOT NULL THEN
1434 p_release_date := p_lgcy_suo_rec.outcome_dt;
1435 ELSE
1436 p_release_date := NULL;
1437 END IF;
1438 gen_log_info('End of Validation 10');
1439
1440 return l_return_status;
1441
1442 gen_log_info('END OF FUNCTION DERIVE_UNIT_OUTCOME_DATA');
1443
1444 END derive_unit_outcome_data;
1445
1446 FUNCTION validate_suao_db_cons (
1447 p_person_id IN IGS_PE_PERSON.PERSON_ID%TYPE ,
1448 p_lgcy_suo_rec IN LGCY_SUO_REC_TYPE ,
1449 p_cal_type IN IGS_CA_INST.CAL_TYPE%TYPE ,
1450 p_sequence_number IN IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
1451 p_translated_version_number IN IGS_AS_SU_STMPTOUT_ALL.TRANSLATED_VERSION_NUMBER%TYPE ,
1452 x_return_status OUT NOCOPY VARCHAR2,
1453 p_uoo_id IN IGS_AS_SU_STMPTOUT_ALL.UOO_ID%TYPE
1454 )
1455 RETURN BOOLEAN
1456
1457 /******************************************************************************************************
1458 ||Created By : Aiyer
1459 ||Date Created on : 2002/11/20
1460 ||Purpose : This function performs all data integrity validations on the table igs_as_su_stmptout_all
1461 || It is called from the procedure create_unit_outcome.
1462 ||Know limitations, enhancements or remarks
1463 ||Change History
1464 ||Who When What
1465 ||(reverse chronological order - newest change first)
1466 *******************************************************************************************************/
1467
1468 IS
1469 l_return_status BOOLEAN DEFAULT TRUE;
1470
1471 BEGIN
1472
1473 -- Initialise X_return_status to 'S' i.e success.
1474 x_return_status := 'S';
1475
1476 /*************************************** Validation 1 ******************************************/
1477 gen_log_info('Start of Validation 1');
1478 /*
1479 Validate that the primary key does not already exist.
1480 if exists then give warning and return
1481 */
1482 IF Igs_as_su_stmptout_pkg.get_pk_for_validation (
1483 X_PERSON_ID => p_person_id ,
1484 X_COURSE_CD => p_lgcy_suo_rec.program_cd ,
1485 X_UOO_ID => p_uoo_id ,
1486 X_OUTCOME_DT => p_lgcy_suo_rec.outcome_dt ,
1487 X_GRADING_PERIOD_CD => p_lgcy_suo_rec.grading_period_cd
1488 ) THEN
1489 /*
1490 Primary key already exists.
1491 Set an warning message to stack and set x_return_status to 'W'
1492 and return FALSE
1493 */
1494
1495 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_UTOTCM_ALREADY_EXISTS');
1496 FND_MSG_PUB.ADD;
1497 x_return_status := 'W';
1498 l_return_status := FALSE;
1499 RETURN l_return_status;
1500 END IF;
1501
1502 gen_log_info('End of Validation 1');
1503
1504
1505 /*************************************** Validation 2 ******************************************/
1506 gen_log_info('Start of Validation 2');
1507 /*
1508 Check whether foreign key with table igs_en_su_attempt_all exists.
1509 */
1510
1511 IF NOT igs_en_su_attempt_pkg.get_pk_for_validation (
1512 X_PERSON_ID => p_person_id ,
1513 X_COURSE_CD => p_lgcy_suo_rec.program_cd ,
1514 X_UOO_ID => p_uoo_id
1515 )
1516 THEN
1517 /*
1518 Foreign key with the table igs_en_su_attempt_all does not exists.
1519 Set an error message to stack and set x_return_status to 'E' and proceed
1520 to the next step
1521 */
1522
1523 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_STD_ENRL_NOT_EXISTS');
1524 FND_MSG_PUB.ADD;
1525 x_return_status := 'E';
1526 l_return_status := FALSE;
1527 END IF;
1528
1529 gen_log_info('End of Validation 2');
1530
1531 /*************************************** Validation 3 ******************************************/
1532 gen_log_info('Start of Validation 3');
1533
1534 /*
1535 Check whether foreign key with table igs_as_grd_sch_grade exists.
1536 */
1537
1538 IF NOT igs_as_grd_sch_grade_pkg.get_pk_for_validation (
1539 X_GRADING_SCHEMA_CD => p_lgcy_suo_rec.grading_schema_cd ,
1540 X_VERSION_NUMBER => p_lgcy_suo_rec.version_number ,
1541 X_GRADE => p_lgcy_suo_rec.grade
1542 )
1543 THEN
1544 /*
1545 Foreign key with the table igs_as_grd_sch_grade does not exists.
1546 Set an error message to stack and set x_return_status to 'E' and proceed
1547 to the next step
1548 */
1549
1550 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_GRD_SCH_NOT_EXISTS');
1551 FND_MSG_PUB.ADD;
1552 x_return_status := 'E';
1553 l_return_status := FALSE;
1554
1555 END IF;
1556
1557 gen_log_info('End of Validation 3');
1558
1559 /*************************************** Validation 4 ******************************************/
1560
1561 gen_log_info('Start of Validation 4');
1562 /*
1563 Validate that the parameter s_grade_creation_method_type should have one of the following values as
1564 CONVERSION', 'KEYED','UPLOAD'.
1565 */
1566 IF p_lgcy_suo_rec.s_grade_creation_method_type <> 'CONVERSION' AND
1567 p_lgcy_suo_rec.s_grade_creation_method_type <> 'KEYED' AND
1568 p_lgcy_suo_rec.s_grade_creation_method_type <> 'UPLOAD'
1569 THEN
1570 FND_MESSAGE.SET_NAME ('IGS','IGS_AS_GRD_CRTMTH_INVALID');
1571 FND_MSG_PUB.ADD;
1572 x_return_status := 'E';
1573 l_return_status := FALSE;
1574 END IF;
1575
1576 gen_log_info('End of Validation 4');
1577
1578 /*************************************** Validation 5 ******************************************/
1579
1580 gen_log_info('Start of Validation 5');
1581 /*
1582 Check that Version Number should be between 0 and 999.
1583 */
1584 DECLARE
1585 l_msg_count NUMBER ;
1586
1587 BEGIN
1588 /*
1589 Call the function igs_as_su_stmptout_pkg.check_constraint to check for the version number between 0 and 999
1590 this function raises an exception if it finds an invalid value.
1591 trap this exception in the when others block of the exception handler , remove the invalid_value message added by this function
1592 and add the customised message.
1593 */
1594
1595 igs_as_su_stmptout_pkg.check_constraints ( column_name => 'VERSION_NUMBER' ,
1596 column_value => p_lgcy_suo_rec.version_number
1597 );
1598
1599 EXCEPTION
1600 WHEN OTHERS THEN
1601 l_msg_count := FND_MSG_PUB.COUNT_MSG;
1602 -- Delete the message 'IGS_GE_INVALID_VALUE'
1603 FND_MSG_PUB.DELETE_MSG (l_msg_count);
1604 -- set the customized message
1605 FND_MESSAGE.Set_Name('IGS','IGS_AS_GRD_VERSION_BET_0_999');
1606 FND_MSG_PUB.Add;
1607 x_return_status := 'E';
1608 l_return_status := FALSE;
1609 END ;
1610 gen_log_info('End of Validation 5');
1611
1612 /*************************************** Validation 6 ******************************************/
1613 gen_log_info('Start of Validation 6');
1614 /*
1615 Check Mark between 0 to 999
1616 */
1617 DECLARE
1618 l_msg_count NUMBER ;
1619
1620 BEGIN
1621 /*
1622 Call the function igs_as_su_stmptout_pkg.check_constraint to check for the mark is between 0 and 999.
1623 This function raises an exception if it finds an invalid value.
1624 Trap this exception in the when others block of the exception handler , remove the invalid_value message added by this function
1625 and add the customised message.
1626 */
1627
1628 igs_as_su_stmptout_pkg.check_constraints ( column_name => 'MARK' ,
1629 column_value => p_lgcy_suo_rec.mark
1630 );
1631
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 l_msg_count := FND_MSG_PUB.COUNT_MSG;
1635 -- Delete the message 'IGS_GE_INVALID_VALUE'
1636 FND_MSG_PUB.DELETE_MSG (l_msg_count);
1637 -- set the customized message
1638 FND_MESSAGE.Set_Name('IGS','IGS_EN_MARK_INV');
1639 FND_MSG_PUB.Add;
1640 x_return_status := 'E';
1641 l_return_status := FALSE;
1642 END ;
1643
1644 gen_log_info('End of Validation 6');
1645
1646 /*************************************** Validation 7 ******************************************/
1647 gen_log_info('Start of Validation 7');
1648
1649 /*
1650 Check that FINALISED_OUTCOME_IND can have values only as 'Y' or 'N'.
1651 */
1652 DECLARE
1653 l_msg_count NUMBER ;
1654
1655 BEGIN
1656 /*
1657 Call the function igs_as_su_stmptout_pkg.check_constraint to check that the FINALISED_OUTCOME_IND can have values only as
1658 'Y' or 'N'.
1659 This function raises an exception if it finds an invalid value.
1660 Trap this exception in the when others block of the exception handler , remove the invalid_value message added by this function
1661 and add the customised message.
1662 */
1663
1664 igs_as_su_stmptout_pkg.check_constraints ( column_name => 'FINALISED_OUTCOME_IND' ,
1665 column_value => p_lgcy_suo_rec.finalised_outcome_ind
1666 );
1667
1668 EXCEPTION
1669 WHEN OTHERS THEN
1670 l_msg_count := FND_MSG_PUB.COUNT_MSG;
1671 -- Delete the message 'IGS_GE_INVALID_VALUE'
1672 FND_MSG_PUB.DELETE_MSG (l_msg_count);
1673 -- set the customized message
1674 FND_MESSAGE.Set_Name('IGS','IGS_AS_FNL_OTCN_IND_Y_N');
1675 FND_MSG_PUB.Add;
1676 x_return_status := 'E';
1677 l_return_status := FALSE;
1678 END ;
1679
1680 gen_log_info('End of Validation 7');
1681
1682 /*************************************** Validation 8 ******************************************/
1683 gen_log_info('Start of Validation 8');
1684 /*
1685 Check that ci_sequence_number can have values only between 0 and 999999
1686 */
1687 DECLARE
1688 l_msg_count NUMBER ;
1689
1690 BEGIN
1691 /*
1692 Call the function igs_as_su_stmptout_pkg.check_constraint to check that ci_sequence_number
1693 can have values only between 0 and 999999.
1694 This function raises an exception if it finds an invalid value.
1695 Trap this exception in the when others block of the exception handler , remove the invalid_value message added by this function
1696 and add the customised message.
1697 */
1698
1699 igs_as_su_stmptout_pkg.check_constraints ( column_name => 'CI_SEQUENCE_NUMBER' ,
1700 column_value => p_sequence_number
1701 );
1702
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 l_msg_count := FND_MSG_PUB.COUNT_MSG;
1706 -- Delete the message 'IGS_GE_INVALID_VALUE'
1707 FND_MSG_PUB.DELETE_MSG (l_msg_count);
1708 -- set the customized message
1709 FND_MESSAGE.Set_Name('IGS','IGS_AS_SEQ_NUM_BET_0_999999');
1710 FND_MSG_PUB.Add;
1711 x_return_status := 'E';
1712 l_return_status := FALSE;
1713 END ;
1714
1715 gen_log_info('End of Validation 8');
1716
1717 /*************************************** Validation 9 ******************************************/
1718 gen_log_info('Start of Validation 9');
1719 /*
1720 Check that the translated_version_number can have values only between 0 and 999
1721 */
1722 DECLARE
1723 l_msg_count NUMBER ;
1724
1725 BEGIN
1726 /*
1727 Call the function igs_as_su_stmptout_pkg.check_constraint to check that the translated_version_number can
1728 have values only between 0 and 999.
1729 This function raises an exception if it finds an invalid value.
1730 Trap this exception in the when others block of the exception handler , remove the invalid_value message added by this function
1731 and add the customised message.
1732 */
1733
1734 igs_as_su_stmptout_pkg.check_constraints ( column_name => 'TRANSLATED_VERSION_NUMBER' ,
1735 column_value => p_translated_version_number
1736 );
1737
1738 EXCEPTION
1739 WHEN OTHERS THEN
1740 l_msg_count := FND_MSG_PUB.COUNT_MSG;
1741 -- Delete the message 'IGS_GE_INVALID_VALUE'
1742 FND_MSG_PUB.DELETE_MSG (l_msg_count);
1743
1744 -- set the customized message
1745 FND_MESSAGE.Set_Name('IGS','IGS_AS_TRN_VERSION_BET_0_999');
1746 FND_MSG_PUB.Add;
1747 x_return_status := 'E';
1748 l_return_status := FALSE;
1749 END ;
1750
1751 gen_log_info('End of Validation 9');
1752
1753 gen_log_info('End of function validate_suao_db_cons');
1754
1755 return ( l_return_status );
1756
1757 END validate_suao_db_cons;
1758
1759 FUNCTION validate_unit_outcome (
1760 p_lgcy_suo_rec LGCY_SUO_REC_TYPE ,
1761 p_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE
1762 )
1763
1764 /************************************************************************************************************************
1765 ||Created By : Aiyer
1766 ||Date Created on : 2002/11/26
1767 ||Purpose : The function validate_unit_outcome validates all the business validations before entering a record in the
1768 || table IGS_AS_SU_STMPT_OUT_ALL.
1769 || Called from the procedure create_unit_outcome
1770 ||Know limitations, enhancements or remarks
1771 ||Change History
1772 ||Who When What
1773 ||(reverse chronological order - newest change first)
1774 ||Aiyer 09-Jan-2003 Code fix for the bug 2741946.
1775 || Removed the check to validate mark against grade and grading schema.
1776 *************************************************************************************************************************/
1777 RETURN BOOLEAN
1778
1779 IS
1780 l_return_status BOOLEAN DEFAULT TRUE;
1781 BEGIN
1782
1783 gen_log_info (p_msg => ' Start of function validate_unit_outcome function ');
1784
1785 /************************* Validation 1 ********************************/
1786 /*
1787 IF unit attempt_status (derived in derive_unit_outcome_data function) is niether 'ENROLLED' nor 'COMPLETED'
1788 then do not allow student unit attempt outcome to be graded.
1789 set an error message to stack
1790 */
1791
1792 gen_log_info (p_msg => ' Start of validation 1 ');
1793
1794 -- Check whether unit_attempt_status is niether in ENROLLED nor COMPLETED.
1795
1796 IF p_unit_attempt_status <> 'ENROLLED' AND p_unit_attempt_status <> 'COMPLETED' THEN
1797 -- Validation unsuccessful as the unit_attempt_status is not in ENROLLED or COMPLETED.
1798 -- set an error message to stack and return FALSE.
1799 FND_MESSAGE.SET_NAME('IGS', 'IGS_AS_CANT_GRD_UNT_INV_STAT');
1800 FND_MSG_PUB.ADD;
1801 l_return_status := FALSE;
1802 END IF;
1803
1804 gen_log_info (p_msg => ' End of validation 1 ');
1805
1806 -- Return TRUE , Validation successful
1807
1808 gen_log_info (p_msg => ' End of function validate_unit_outcome function ');
1809
1810 return l_return_status;
1811
1812 END validate_unit_outcome;
1813
1814 PROCEDURE create_post_unit_outcome (
1815 p_person_id IGS_PE_PERSON.PERSON_ID%TYPE ,
1816 p_cal_type IGS_CA_INST.CAL_TYPE%TYPE ,
1817 p_sequence_number IGS_CA_INST.SEQUENCE_NUMBER%TYPE ,
1818 p_unit_attempt_status IGS_EN_SU_ATTEMPT_ALL.UNIT_ATTEMPT_STATUS%TYPE ,
1819 p_lgcy_suo_rec LGCY_SUO_REC_TYPE
1820 )
1821 /************************************************************************************************************************
1822 ||Created By : Aiyer
1823 ||Date Created on : 2002/11/26
1824 ||Purpose : The procedure create_post_unit_outcome performs post insert operations on the table IGS_AS_SU_STMPTOUT_ALL
1825 || Called from the procedure create_unit_outcome
1826 ||
1827 ||Know limitations, enhancements or remarks
1828 ||Change History
1829 ||Who When What
1830 ||(reverse chronological order - newest change first)
1831 *************************************************************************************************************************/
1832
1833 IS
1834
1835 BEGIN
1836
1837 gen_log_info('Start of PROCEDURE CREATE_POST_UNIT_OUTCOME');
1838
1839 /*************************************** Validation 1 ******************************************/
1840
1841 gen_log_info('Start of Create_post_unit_outcome.Validation 1');
1842
1843 IF p_lgcy_suo_rec.finalised_outcome_ind = 'Y' AND p_unit_attempt_status = 'ENROLLED' THEN
1844
1845 -- update the table igs_en_su_attempt_all to the unit_attempt_status of 'COMPLETED'
1846 UPDATE
1847 IGS_EN_SU_ATTEMPT_ALL su
1848 SET
1849 unit_attempt_status = 'COMPLETED'
1850 WHERE
1851 su.person_id = p_person_id
1852 AND su.course_cd = p_lgcy_suo_rec.program_cd
1853 AND su.unit_cd = p_lgcy_suo_rec.unit_cd
1854 AND su.cal_type = p_cal_type
1855 AND su.ci_sequence_number = p_sequence_number
1856 AND su.location_cd = p_lgcy_suo_rec.location_cd
1857 AND su.unit_class = p_lgcy_suo_rec.unit_class;
1858
1859 END IF;
1860
1861 gen_log_info('End of create_post_unit_outcome.validation 1');
1862
1863 gen_log_info('END of PROCEDURE CREATE_POST_UNIT_OUTCOME');
1864
1865 return;
1866
1867 END create_post_unit_outcome;
1868
1869
1870 END igs_as_suao_lgcy_pub;
[Home] [Help]