[Home] [Help]
PACKAGE BODY: APPS.IGS_PR_UPLOAD_EXT_RESULTS
Source
1 PACKAGE BODY igs_pr_upload_ext_results AS
2 /* $Header: IGSPR38B.pls 120.1 2006/01/18 23:08:29 swaghmar noship $ */
3 /****************************************************************************************************************
4 || Created By : nmankodi
5 || Created On : 07-NOV-2002
6 || Purpose : This Job validates and uploads the Interface data for External Stats and Degree Completion
7 || This process can be called from the concurrent manager .
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 || smanglm 18-AUG-2003 Bug: 3102152 - Timeframe check for BOTH , CUMULATIVE and PERIOD
13 || kdande 10-Sep-2003 Bug: 3076139 - Enhanced the error logging behavior
14 || for the Degree Comnpletion Import.
15 ****************************************************************************************************************/
16
17 FUNCTION validate_record (
18 p_stu_acad_stat_int_rec IN igs_pr_stu_acad_stat_int%ROWTYPE,
19 p_error_code OUT NOCOPY igs_pr_stu_acad_stat_int.error_code%TYPE,
20 p_person_id OUT NOCOPY hz_parties.party_id%TYPE,
21 p_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
22 p_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE
23 )
24 RETURN BOOLEAN IS
25 -- This cursor fetches the interface table records
26
27 CURSOR cur_hz_parties (
28 cp_person_number igs_pr_stu_acad_stat_int.person_number%TYPE
29 ) IS
30 SELECT hz.party_id
31 FROM hz_parties hz
32 WHERE hz.party_number = cp_person_number;
33
34 CURSOR cur_spa_exists (
35 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
36 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
37 ) IS
38 SELECT 'X'
39 FROM igs_en_stdnt_ps_att spa
40 WHERE spa.person_id = cp_person_id
41 AND spa.course_cd = cp_course_cd;
42
43 CURSOR cur_ci_exists (
44 cp_alternate_code igs_ca_inst.alternate_code%TYPE
45 ) IS
46 SELECT ci.cal_type,
47 ci.sequence_number
48 FROM igs_ca_inst ci,
49 igs_ca_type ct
50 WHERE ci.alternate_code = cp_alternate_code
51 AND ci.cal_type = ct.cal_type
52 AND ct.s_cal_cat = 'LOAD';
53
54 CURSOR cur_org_stat_type (
55 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
56 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
57 cp_stat_type igs_pr_stat_type.stat_type%TYPE
58 ) IS
59 SELECT ost.timeframe,
60 stty.closed_ind,
61 stty.derivation
62 FROM igs_en_stdnt_ps_att spa,
63 igs_ps_ver pv,
64 igs_pr_org_stat ost,
65 igs_pr_stat_type stty
66 WHERE spa.person_id = cp_person_id
67 AND spa.course_cd = cp_course_cd
68 AND spa.course_cd = pv.course_cd
69 AND spa.version_number = pv.version_number
70 AND ost.org_unit_cd = pv.responsible_org_unit_cd
71 AND ost.stat_type = cp_stat_type
72 AND ost.stat_type = stty.stat_type;
73
74 CURSOR cur_inst_stat_type (
75 cp_stat_type igs_pr_stat_type.stat_type%TYPE
76 ) IS
77 SELECT ist.timeframe,
78 stty.closed_ind,
79 stty.derivation
80 FROM igs_pr_inst_stat ist,
81 igs_pr_stat_type stty
82 WHERE ist.stat_type = cp_stat_type
83 AND ist.stat_type = stty.stat_type;
84
85 CURSOR cur_stat_ele (
86 cp_stat_type igs_pr_stat_type.stat_type%TYPE,
87 cp_s_stat_element igs_pr_sta_type_ele.s_stat_element%TYPE
88 ) IS
89 SELECT 'X'
90 FROM igs_pr_sta_type_ele stte
91 WHERE stte.stat_type = cp_stat_type
92 AND stte.s_stat_element = cp_s_stat_element;
93
94 hz_parties_rec cur_hz_parties%ROWTYPE;
95 spa_exists_rec cur_spa_exists%ROWTYPE;
96 ci_exists_rec cur_ci_exists%ROWTYPE;
97 org_stat_type_rec cur_org_stat_type%ROWTYPE;
98 inst_stat_type_rec cur_inst_stat_type%ROWTYPE;
99 stat_ele_rec cur_stat_ele%ROWTYPE;
100 l_stat_closed_ind igs_pr_stat_type.closed_ind%TYPE;
101 l_stat_derivation igs_pr_stat_type.derivation%TYPE;
102 l_stat_timeframe igs_pr_org_stat.timeframe%TYPE;
103 BEGIN
104 IF (igs_ge_gen_004.genp_get_lookup (
105 'PR_EXTERNAL_STAT_SOURCE',
106 p_stu_acad_stat_int_rec.source_type
107 ) IS NULL
108 ) THEN
109 p_error_code := 'IGS_PR_INVALID_SOURCE';
110 RETURN FALSE;
111 END IF;
112
113 IF NVL (p_stu_acad_stat_int_rec.attempted_credit_points, 0) < 0
114 OR NVL (p_stu_acad_stat_int_rec.earned_credit_points, 0) < 0
115 OR NVL (p_stu_acad_stat_int_rec.gpa, 0) < 0
116 OR NVL (p_stu_acad_stat_int_rec.gpa_credit_points, 0) < 0
117 OR NVL (p_stu_acad_stat_int_rec.gpa_quality_points, 0) < 0 THEN
118 p_error_code := 'IGS_PR_NEGATIVE_STAT_VALUE';
119 RETURN FALSE;
120 END IF;
121
122 OPEN cur_hz_parties (p_stu_acad_stat_int_rec.person_number);
123 FETCH cur_hz_parties INTO hz_parties_rec;
124
125 IF (cur_hz_parties%NOTFOUND) THEN
126 p_error_code := 'IGS_PR_PERSON_NOT_FOUND';
127 CLOSE cur_hz_parties;
128 RETURN FALSE;
129 ELSE
130 p_person_id := hz_parties_rec.party_id;
131 CLOSE cur_hz_parties;
132 END IF;
133
134 OPEN cur_spa_exists (
135 hz_parties_rec.party_id,
136 p_stu_acad_stat_int_rec.course_cd
137 );
138 FETCH cur_spa_exists INTO spa_exists_rec;
139
140 IF (cur_spa_exists%NOTFOUND) THEN
141 p_error_code := 'IGS_PR_SPA_NOT_EXISTS';
142 CLOSE cur_spa_exists;
143 RETURN FALSE;
144 ELSE
145 CLOSE cur_spa_exists;
146 END IF;
147
148 OPEN cur_ci_exists (p_stu_acad_stat_int_rec.alternate_code);
149 FETCH cur_ci_exists INTO ci_exists_rec;
150
151 IF (cur_ci_exists%NOTFOUND) THEN
152 p_error_code := 'IGS_PR_CI_NOT_EXISTS';
153 CLOSE cur_ci_exists;
154 RETURN FALSE;
155 ELSE
156 p_cal_type := ci_exists_rec.cal_type;
157 p_ci_sequence_number := ci_exists_rec.sequence_number;
158 CLOSE cur_ci_exists;
159 END IF;
160
161 OPEN cur_org_stat_type (
162 hz_parties_rec.party_id,
163 p_stu_acad_stat_int_rec.course_cd,
164 p_stu_acad_stat_int_rec.stat_type
165 );
166 FETCH cur_org_stat_type INTO org_stat_type_rec;
167 OPEN cur_inst_stat_type (p_stu_acad_stat_int_rec.stat_type);
168 FETCH cur_inst_stat_type INTO inst_stat_type_rec;
169
170 IF (cur_org_stat_type%FOUND) THEN
171 l_stat_closed_ind := org_stat_type_rec.closed_ind;
172 l_stat_derivation := org_stat_type_rec.derivation;
173 l_stat_timeframe := org_stat_type_rec.timeframe;
174 CLOSE cur_org_stat_type;
175 ELSIF (cur_inst_stat_type%FOUND) THEN
176 l_stat_closed_ind := inst_stat_type_rec.closed_ind;
177 l_stat_derivation := inst_stat_type_rec.derivation;
178 l_stat_timeframe := inst_stat_type_rec.timeframe;
179 CLOSE cur_org_stat_type;
180 CLOSE cur_inst_stat_type;
181 ELSE
182 p_error_code := 'IGS_PR_STAT_NOT_DEF';
183 CLOSE cur_org_stat_type;
184 CLOSE cur_inst_stat_type;
185 RETURN FALSE;
186 END IF;
187
188 IF l_stat_closed_ind = 'Y' THEN
189 p_error_code := 'IGS_PR_STAT_CLOSED';
190 RETURN FALSE;
191 END IF;
192
193 IF l_stat_derivation = 'CALCULATED' THEN
194 p_error_code := 'IGS_PR_DERIV_CALC';
195 RETURN FALSE;
196 END IF;
197
198 -- code fix for bug 3102152 starts
199 IF UPPER (l_stat_timeframe) <> UPPER (p_stu_acad_stat_int_rec.timeframe) THEN
200 IF UPPER (l_stat_timeframe) = 'BOTH'
201 AND ( UPPER (p_stu_acad_stat_int_rec.timeframe) = 'CUMULATIVE'
202 OR UPPER (p_stu_acad_stat_int_rec.timeframe) = 'PERIOD'
203 ) THEN
204 NULL;
205 ELSE
206 p_error_code := 'IGS_PR_TIMEFRAME_INC';
207 RETURN FALSE;
208 END IF;
209 END IF;
210
211 -- code fix for bug 3102152 ends
212 OPEN cur_stat_ele (p_stu_acad_stat_int_rec.stat_type, 'CP_ATTEMPTED');
213 FETCH cur_stat_ele INTO stat_ele_rec;
214
215 IF (p_stu_acad_stat_int_rec.attempted_credit_points IS NULL
216 AND cur_stat_ele%FOUND
217 ) THEN
218 p_error_code := 'IGS_PR_ELE_CP_ATTEMPT_NULL';
219 CLOSE cur_stat_ele;
220 RETURN FALSE;
221 ELSIF (p_stu_acad_stat_int_rec.attempted_credit_points IS NOT NULL
222 AND cur_stat_ele%NOTFOUND
223 ) THEN
224 p_error_code := 'IGS_PR_ELE_CP_ATTEMPT_INCL';
225 CLOSE cur_stat_ele;
226 RETURN FALSE;
227 ELSE
228 CLOSE cur_stat_ele;
229 END IF;
230
231 OPEN cur_stat_ele (p_stu_acad_stat_int_rec.stat_type, 'CP_EARNED');
232 FETCH cur_stat_ele INTO stat_ele_rec;
233
234 IF (p_stu_acad_stat_int_rec.earned_credit_points IS NULL
235 AND cur_stat_ele%FOUND
236 ) THEN
237 p_error_code := 'IGS_PR_ELE_CP_EARN_NULL';
238 CLOSE cur_stat_ele;
239 RETURN FALSE;
240 ELSIF ( p_stu_acad_stat_int_rec.earned_credit_points IS NOT NULL
241 AND cur_stat_ele%NOTFOUND
242 ) THEN
243 p_error_code := 'IGS_PR_ELE_CP_EARN_INCL';
244 CLOSE cur_stat_ele;
245 RETURN FALSE;
246 ELSE
247 CLOSE cur_stat_ele;
248 END IF;
249
250 OPEN cur_stat_ele (p_stu_acad_stat_int_rec.stat_type, 'GPA');
251 FETCH cur_stat_ele INTO stat_ele_rec;
252
253 IF (p_stu_acad_stat_int_rec.gpa IS NULL
254 AND cur_stat_ele%FOUND
255 ) THEN
256 p_error_code := 'IGS_PR_ELE_GPA_NULL';
257 CLOSE cur_stat_ele;
258 RETURN FALSE;
259 ELSIF (p_stu_acad_stat_int_rec.gpa IS NOT NULL
260 AND cur_stat_ele%NOTFOUND
261 ) THEN
262 p_error_code := 'IGS_PR_ELE_GPA_INCL';
263 CLOSE cur_stat_ele;
264 RETURN FALSE;
265 ELSE
266 CLOSE cur_stat_ele;
267 END IF;
268
269 OPEN cur_stat_ele (p_stu_acad_stat_int_rec.stat_type, 'GPA CP');
270 FETCH cur_stat_ele INTO stat_ele_rec;
271
272 IF (p_stu_acad_stat_int_rec.gpa_credit_points IS NULL
273 AND cur_stat_ele%FOUND
274 ) THEN
275 p_error_code := 'IGS_PR_ELE_GPA_CP_NULL';
276 CLOSE cur_stat_ele;
277 RETURN FALSE;
278 ELSIF (p_stu_acad_stat_int_rec.gpa_credit_points IS NOT NULL
279 AND cur_stat_ele%NOTFOUND
280 ) THEN
281 p_error_code := 'IGS_PR_ELE_GPA_CP_INCL';
282 CLOSE cur_stat_ele;
283 RETURN FALSE;
284 ELSE
285 CLOSE cur_stat_ele;
286 END IF;
287
288 OPEN cur_stat_ele (p_stu_acad_stat_int_rec.stat_type, 'GPA QP');
289 FETCH cur_stat_ele INTO stat_ele_rec;
290
291 IF (p_stu_acad_stat_int_rec.gpa_quality_points IS NULL
292 AND cur_stat_ele%FOUND
293 ) THEN
294 p_error_code := 'IGS_PR_ELE_GPA_QP_NULL';
295 CLOSE cur_stat_ele;
296 RETURN FALSE;
297 ELSIF (p_stu_acad_stat_int_rec.gpa_quality_points IS NOT NULL
298 AND cur_stat_ele%NOTFOUND
299 ) THEN
300 p_error_code := 'IGS_PR_ELE_GPA_QP_INCL';
301 CLOSE cur_stat_ele;
302 RETURN FALSE;
303 ELSE
304 CLOSE cur_stat_ele;
305 END IF;
306
307 RETURN TRUE;
308 END validate_record;
309
310 PROCEDURE upload_external_stats (
311 errbuf OUT NOCOPY VARCHAR2, -- Standard Error Buffer Variable
312 retcode OUT NOCOPY NUMBER, -- Standard Concurrent Return code
313 p_batch_id IN NUMBER -- The batch id which needs to be uploaded
314 ) IS
315
316 /****************************************************************************************************************
317 || Created By : nmankodi
318 || Created On : 04-NOV-2002
319 || Purpose : This Job validates and uploads and then purges the Interface data for External Stats and Degree
320 Completion
321 || This process can be called from the concurrent manager .
322 || Known limitations, enhancements or remarks :
323 || Change History :
324 || Who When What
325 || (reverse chronological order - newest change first)
326 ||swaghmar 16-Jan-2006 Bug# 4951054 Added check for disabling UI's
327 ****************************************************************************************************************/
328
329 CURSOR cur_stu_acad_stat_int (
330 cp_batch_id igs_pr_stu_acad_stat_int.batch_id%TYPE
331 ) IS
332 SELECT sasi.*
333 FROM igs_pr_stu_acad_stat_int sasi
334 WHERE sasi.batch_id = cp_batch_id
335 FOR UPDATE;
336
337 stu_acad_stat_int_rec cur_stu_acad_stat_int%ROWTYPE;
338 l_rowid VARCHAR2 (4000) DEFAULT NULL;
339 l_valid_record BOOLEAN DEFAULT FALSE;
340 l_error_code igs_pr_stu_acad_stat_int.error_code%TYPE;
341 l_person_id hz_parties.party_id%TYPE;
342 l_cal_type igs_ca_inst.cal_type%TYPE;
343 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
344 invalid_parameter_combination EXCEPTION;
345 BEGIN
346 retcode := 0;
347 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
348
349 -- Fetching the records from the Interface Table and validating the data
350 IF (p_batch_id IS NOT NULL) THEN
351 FOR stu_acad_stat_int_rec IN cur_stu_acad_stat_int (p_batch_id) LOOP
352 l_valid_record := validate_record (
353 stu_acad_stat_int_rec,
354 l_error_code,
355 l_person_id,
356 l_cal_type,
357 l_ci_sequence_number
358 );
359
360 IF l_valid_record THEN
361 igs_pr_stu_acad_stat_pkg.add_row (
362 x_rowid => l_rowid,
363 x_person_id => l_person_id,
364 x_course_cd => stu_acad_stat_int_rec.course_cd,
365 x_cal_type => l_cal_type,
366 x_ci_sequence_number => l_ci_sequence_number,
367 x_stat_type => stu_acad_stat_int_rec.stat_type,
368 x_timeframe => UPPER (stu_acad_stat_int_rec.timeframe),
369 x_source_type => stu_acad_stat_int_rec.source_type,
370 x_source_reference => stu_acad_stat_int_rec.source_reference,
371 x_attempted_credit_points => stu_acad_stat_int_rec.attempted_credit_points,
372 x_earned_credit_points => stu_acad_stat_int_rec.earned_credit_points,
373 x_gpa => stu_acad_stat_int_rec.gpa,
374 x_gpa_credit_points => stu_acad_stat_int_rec.gpa_credit_points,
375 x_gpa_quality_points => stu_acad_stat_int_rec.gpa_quality_points,
376 x_mode => 'R'
377 );
378
379 IF l_rowid IS NOT NULL THEN
380 DELETE FROM igs_pr_stu_acad_stat_int
381 WHERE CURRENT OF cur_stu_acad_stat_int;
382 END IF;
383 ELSE
384 UPDATE igs_pr_stu_acad_stat_int
385 SET error_code = l_error_code
386 WHERE CURRENT OF cur_stu_acad_stat_int;
387 END IF;
388 END LOOP;
389 ELSIF (p_batch_id IS NULL) THEN
390
391 -- When the batch_id is passed as null there is no batch to process.
392 RAISE invalid_parameter_combination;
393 END IF;
394
395 COMMIT;
396 EXCEPTION
397 WHEN invalid_parameter_combination THEN
398 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
399 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
400 retcode := 2;
401 errbuf := fnd_message.get_string ('IGS', 'IGS_PR_RNK_INV_PRM');
402 igs_ge_msg_stack.conc_exception_hndl;
403 WHEN OTHERS THEN
404 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
405 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
406 retcode := 2;
407 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXP');
408 igs_ge_msg_stack.conc_exception_hndl;
409 END upload_external_stats;
410
411
412 -- =========================================================================================
413
414 FUNCTION validate_spa_record (
415 p_person_number IN igs_pr_spa_complete_int.person_number%TYPE,
416 p_course_cd IN igs_pr_spa_complete_int.course_cd%TYPE,
417 p_error_code OUT NOCOPY igs_pr_spa_complete_int.error_code%TYPE,
418 p_person_id OUT NOCOPY hz_parties.party_id%TYPE,
419 p_rowid OUT NOCOPY ROWID
420 )
421 RETURN BOOLEAN IS
422
423 /****************************************************************************************************************
424 || Created By : dlarsen
425 || Created On : 16-DEC-2002
426 || Purpose : This validate the Person Number and that Student Program Attempt record exists and
427 || is not already completed or ended.
428 ||
429 || This process can be called from upload_external_completion.
430 || Known limitations, enhancements or remarks :
431 || Change History :
432 || Who When What
433 || (reverse chronological order - newest change first)
434 ****************************************************************************************************************/
435
436 CURSOR c_parties (
437 cp_person_number igs_pr_spa_complete_int.person_number%TYPE
438 ) IS
439 SELECT hz.party_id
440 FROM hz_parties hz
441 WHERE hz.party_number = cp_person_number;
442
443 CURSOR c_spa (
444 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
445 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE
446 ) IS
447 SELECT spa.ROWID,
448 spa.course_attempt_status
449 FROM igs_en_stdnt_ps_att spa
450 WHERE spa.person_id = cp_person_id
451 AND spa.course_cd = cp_course_cd;
452
453 l_person_id hz_parties.party_id%TYPE;
454 l_rowid ROWID;
455 l_course_attempt_status igs_en_stdnt_ps_att.course_attempt_status%TYPE;
456 BEGIN
457 p_error_code := NULL;
458 -- Check the Person Number relates to a valid Person/Party ID
459 OPEN c_parties (p_person_number);
460 FETCH c_parties INTO l_person_id;
461
462 IF (c_parties%NOTFOUND) THEN
463 p_error_code := 'IGS_PR_PERSON_NOT_FOUND';
464 CLOSE c_parties;
465 RETURN FALSE;
466 ELSE
467 p_person_id := l_person_id;
468 CLOSE c_parties;
469 END IF;
470
471 -- Check if the Student Program Attempt exists and is of the correct Course Attempt Status
472 OPEN c_spa (l_person_id, p_course_cd);
473 FETCH c_spa INTO l_rowid,
474 l_course_attempt_status;
475
476 IF (c_spa%NOTFOUND) THEN
477 p_error_code := 'IGS_PR_SPA_NOT_EXISTS';
478 CLOSE c_spa;
479 RETURN FALSE;
480 ELSE
481 CLOSE c_spa;
482
483 -- Check if the Student Program Attempt has a valid status
484 IF l_course_attempt_status NOT IN ('ENROLLED', 'INACTIVE') THEN
485 p_error_code := 'IGS_PR_SPA_STATUS';
486 RETURN FALSE;
487 END IF;
488 END IF;
489
490 -- Return the rowid of the Student Program Attempt record
491 p_rowid := l_rowid;
492 RETURN TRUE;
493 EXCEPTION
494 WHEN OTHERS THEN
495 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
496 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
497 igs_ge_msg_stack.conc_exception_hndl;
498 END validate_spa_record;
499
500
501 -- =========================================================================================
502
503 FUNCTION validate_susa_record (
504 p_person_id IN hz_parties.party_id%TYPE,
505 p_course_cd IN igs_pr_susa_complete_int.course_cd%TYPE,
506 p_unit_set_cd IN igs_pr_susa_complete_int.unit_set_cd%TYPE,
507 p_error_code OUT NOCOPY igs_pr_susa_complete_int.error_code%TYPE,
508 p_rowid OUT NOCOPY ROWID
509 )
510 RETURN BOOLEAN IS
511
512 /****************************************************************************************************************
513 || Created By : dlarsen
514 || Created On : 16-DEC-2002
515 || Purpose : This validate the Student Unit Set Attempt record exists and is not already completed or ended.
516 ||
517 || This process can be called from upload_external_completion.
518 || Known limitations, enhancements or remarks :
519 || Change History :
520 || Who When What
521 || (reverse chronological order - newest change first)
522 ****************************************************************************************************************/
523
524 CURSOR c_susa (
525 cp_person_id igs_as_su_setatmpt.person_id%TYPE,
526 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE,
527 cp_unit_set_cd igs_as_su_setatmpt.unit_set_cd%TYPE
528 ) IS
529 SELECT susa.ROWID,
530 susa.end_dt,
531 susa.rqrmnts_complete_ind
532 FROM igs_as_su_setatmpt susa
533 WHERE susa.person_id = cp_person_id
534 AND susa.course_cd = cp_course_cd
535 AND susa.unit_set_cd = cp_unit_set_cd;
536
537 l_rowid ROWID;
538 l_end_dt igs_as_su_setatmpt.end_dt%TYPE;
539 l_rqrmnts_complete_ind igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE;
540 BEGIN
541 p_error_code := NULL;
542 -- Check if the Student Unit Set Attempt exists and is not ended or completed
543 OPEN c_susa (p_person_id, p_course_cd, p_unit_set_cd);
544 FETCH c_susa INTO l_rowid,
545 l_end_dt,
546 l_rqrmnts_complete_ind;
547
548 IF (c_susa%NOTFOUND) THEN
549 p_error_code := 'IGS_PR_SUSA_NOT_EXISTS';
550 CLOSE c_susa;
551 RETURN FALSE;
552 ELSE
553 CLOSE c_susa;
554
555 -- Check if the Student Unit Set Attempt is already completed
556 IF l_rqrmnts_complete_ind = 'Y' THEN
557 p_error_code := 'IGS_PR_SUSA_COMPLETE';
558 RETURN FALSE;
559 END IF;
560
561 -- Check if the Student Unit Set Attempt is already ended
562 IF l_end_dt IS NOT NULL THEN
563 p_error_code := 'IGS_PR_SUSA_ENDED';
564 RETURN FALSE;
565 END IF;
566 END IF;
567
568 -- Return the rowid of the Student Unit Set Attempt record
569 p_rowid := l_rowid;
570 RETURN TRUE;
571 EXCEPTION
572 WHEN OTHERS THEN
573 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
574 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
575 igs_ge_msg_stack.conc_exception_hndl;
576 END validate_susa_record;
577
578
579 -- =========================================================================================
580
581 PROCEDURE update_susa (
582 errbuf OUT NOCOPY VARCHAR2,
583 retcode OUT NOCOPY NUMBER,
584 p_rowid IN ROWID,
585 p_end_dt IN igs_as_su_setatmpt.end_dt%TYPE,
586 p_voluntary_end_ind IN igs_as_su_setatmpt.voluntary_end_ind%TYPE,
587 p_rqrmnts_complete_ind IN igs_as_su_setatmpt.rqrmnts_complete_ind%TYPE,
588 p_rqrmnts_complete_dt IN igs_as_su_setatmpt.rqrmnts_complete_dt%TYPE,
589 p_s_completed_source_type IN igs_as_su_setatmpt.s_completed_source_type%TYPE
590 ) IS
591
592 /****************************************************************************************************************
593 || Created By : dlarsen
594 || Created On : 16-DEC-2002
595 || Purpose : This updates the Student Unit Set Attempt record with the completion or ending details.
596 ||
597 || This process can be called from upload_external_completion.
598 || Known limitations, enhancements or remarks :
599 || Change History :
600 || Who When What
601 || (reverse chronological order - newest change first)
602 ****************************************************************************************************************/
603
604 CURSOR c_susa (
605 cp_rowid ROWID
606 ) IS
607 SELECT susa.*
608 FROM igs_as_su_setatmpt susa
609 WHERE susa.ROWID = cp_rowid
610 FOR UPDATE NOWAIT;
611 BEGIN
612 retcode := 0;
613
614 FOR v_susa_rec IN c_susa (p_rowid) LOOP
615 igs_as_su_setatmpt_pkg.update_row (
616 x_mode => 'R',
617 x_rowid => p_rowid,
618 x_person_id => v_susa_rec.person_id,
619 x_course_cd => v_susa_rec.course_cd,
620 x_unit_set_cd => v_susa_rec.unit_set_cd,
621 x_us_version_number => v_susa_rec.us_version_number,
622 x_sequence_number => v_susa_rec.sequence_number,
623 x_selection_dt => v_susa_rec.selection_dt,
624 x_student_confirmed_ind => v_susa_rec.student_confirmed_ind,
625 x_end_dt => p_end_dt,
626 x_parent_unit_set_cd => v_susa_rec.parent_unit_set_cd,
627 x_parent_sequence_number => v_susa_rec.parent_sequence_number,
628 x_primary_set_ind => v_susa_rec.primary_set_ind,
629 x_voluntary_end_ind => p_voluntary_end_ind,
630 x_authorised_person_id => v_susa_rec.authorised_person_id,
631 x_authorised_on => v_susa_rec.authorised_on,
632 x_override_title => v_susa_rec.override_title,
633 x_rqrmnts_complete_ind => p_rqrmnts_complete_ind,
634 x_rqrmnts_complete_dt => p_rqrmnts_complete_dt,
635 x_s_completed_source_type => p_s_completed_source_type,
636 x_catalog_cal_type => v_susa_rec.catalog_cal_type,
637 x_catalog_seq_num => v_susa_rec.catalog_seq_num,
638 x_attribute_category => v_susa_rec.attribute_category,
639 x_attribute1 => v_susa_rec.attribute1,
640 x_attribute2 => v_susa_rec.attribute2,
641 x_attribute3 => v_susa_rec.attribute3,
642 x_attribute4 => v_susa_rec.attribute4,
643 x_attribute5 => v_susa_rec.attribute5,
644 x_attribute6 => v_susa_rec.attribute6,
645 x_attribute7 => v_susa_rec.attribute7,
646 x_attribute8 => v_susa_rec.attribute8,
647 x_attribute9 => v_susa_rec.attribute9,
648 x_attribute10 => v_susa_rec.attribute10,
649 x_attribute11 => v_susa_rec.attribute11,
650 x_attribute12 => v_susa_rec.attribute12,
651 x_attribute13 => v_susa_rec.attribute13,
652 x_attribute14 => v_susa_rec.attribute14,
653 x_attribute15 => v_susa_rec.attribute15,
654 x_attribute16 => v_susa_rec.attribute16,
655 x_attribute17 => v_susa_rec.attribute17,
656 x_attribute18 => v_susa_rec.attribute18,
657 x_attribute19 => v_susa_rec.attribute19,
658 x_attribute20 => v_susa_rec.attribute20
659 );
660 END LOOP;
661 EXCEPTION
662 WHEN OTHERS THEN
663 DECLARE
664 app_short_name VARCHAR2 (10);
665 message_name VARCHAR2 (100);
666 BEGIN
667 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
668 fnd_message.parse_encoded (
669 fnd_message.get_encoded,
670 app_short_name,
671 message_name
672 );
673 retcode := 2;
674 errbuf := message_name;
675 END;
676 END update_susa;
677
678
679 -- =========================================================================================
680
681 PROCEDURE update_spa (
682 errbuf OUT NOCOPY VARCHAR2,
683 retcode OUT NOCOPY NUMBER,
684 p_rowid IN ROWID,
685 p_course_rqrmnts_complete_dt IN igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE
686 ) IS
687
688 /****************************************************************************************************************
689 || Created By : dlarsen
690 || Created On : 16-DEC-2002
691 || Purpose : This updates the Student Program Attempt record with the completion details.
692 ||
693 || This process can be called from upload_external_completion.
694 || Known limitations, enhancements or remarks :
695 || Change History :
696 || Who When What
697 || sarakshi 16-Nov-2004 Enh#4000939, added column FUTURE_DATED_TRANS_FLAG in the update row call of IGS_EN_STDNT_PS_ATT_PKG
698 || (reverse chronological order - newest change first)
699 ****************************************************************************************************************/
700
701 CURSOR c_spa (
702 cp_rowid ROWID
703 ) IS
704 SELECT spa.*
705 FROM igs_en_stdnt_ps_att spa
706 WHERE spa.ROWID = cp_rowid
707 FOR UPDATE NOWAIT;
708 BEGIN
709 retcode := 0;
710
711 FOR v_spa_rec IN c_spa (p_rowid) LOOP
712 igs_en_stdnt_ps_att_pkg.update_row (
713 x_mode => 'R',
714 x_rowid => p_rowid,
715 x_person_id => v_spa_rec.person_id,
716 x_course_cd => v_spa_rec.course_cd,
717 x_version_number => v_spa_rec.version_number,
718 x_cal_type => v_spa_rec.cal_type,
719 x_location_cd => v_spa_rec.location_cd,
720 x_attendance_mode => v_spa_rec.attendance_mode,
721 x_attendance_type => v_spa_rec.attendance_type,
722 x_coo_id => v_spa_rec.coo_id,
723 x_student_confirmed_ind => v_spa_rec.student_confirmed_ind,
724 x_commencement_dt => v_spa_rec.commencement_dt,
725 x_course_attempt_status => v_spa_rec.course_attempt_status,
726 x_progression_status => v_spa_rec.progression_status,
727 x_derived_att_type => v_spa_rec.derived_att_type,
728 x_derived_att_mode => v_spa_rec.derived_att_mode,
729 x_provisional_ind => v_spa_rec.provisional_ind,
730 x_discontinued_dt => v_spa_rec.discontinued_dt,
731 x_discontinuation_reason_cd => v_spa_rec.discontinuation_reason_cd,
732 x_lapsed_dt => v_spa_rec.lapsed_dt,
733 x_funding_source => v_spa_rec.funding_source,
734 x_exam_location_cd => v_spa_rec.exam_location_cd,
735 x_derived_completion_yr => v_spa_rec.derived_completion_yr,
736 x_derived_completion_perd => v_spa_rec.derived_completion_perd,
737 x_nominated_completion_yr => v_spa_rec.nominated_completion_yr,
738 x_nominated_completion_perd => v_spa_rec.nominated_completion_perd,
739 x_rule_check_ind => v_spa_rec.rule_check_ind,
740 x_waive_option_check_ind => v_spa_rec.waive_option_check_ind,
741 x_last_rule_check_dt => v_spa_rec.last_rule_check_dt,
742 x_publish_outcomes_ind => v_spa_rec.publish_outcomes_ind,
743 x_course_rqrmnt_complete_ind => 'Y',
744 x_course_rqrmnts_complete_dt => p_course_rqrmnts_complete_dt,
745 x_s_completed_source_type => 'SYSTEM',
746 x_override_time_limitation => v_spa_rec.override_time_limitation,
747 x_advanced_standing_ind => v_spa_rec.advanced_standing_ind,
748 x_fee_cat => v_spa_rec.fee_cat,
749 x_correspondence_cat => v_spa_rec.correspondence_cat,
750 x_self_help_group_ind => v_spa_rec.self_help_group_ind,
751 x_logical_delete_dt => v_spa_rec.logical_delete_dt,
752 x_adm_admission_appl_number => v_spa_rec.adm_admission_appl_number,
753 x_adm_nominated_course_cd => v_spa_rec.adm_nominated_course_cd,
754 x_adm_sequence_number => v_spa_rec.adm_sequence_number,
755 x_last_date_of_attendance => v_spa_rec.last_date_of_attendance,
756 x_dropped_by => v_spa_rec.dropped_by,
757 x_igs_pr_class_std_id => v_spa_rec.igs_pr_class_std_id,
758 x_primary_program_type => v_spa_rec.primary_program_type,
759 x_primary_prog_type_source => v_spa_rec.primary_prog_type_source,
760 x_catalog_cal_type => v_spa_rec.catalog_cal_type,
761 x_catalog_seq_num => v_spa_rec.catalog_seq_num,
762 x_key_program => v_spa_rec.key_program,
763 x_override_cmpl_dt => v_spa_rec.override_cmpl_dt,
764 x_manual_ovr_cmpl_dt_ind => v_spa_rec.manual_ovr_cmpl_dt_ind,
765 x_attribute_category => v_spa_rec.attribute_category,
766 x_attribute1 => v_spa_rec.attribute1,
767 x_attribute2 => v_spa_rec.attribute2,
768 x_attribute3 => v_spa_rec.attribute3,
769 x_attribute4 => v_spa_rec.attribute4,
770 x_attribute5 => v_spa_rec.attribute5,
771 x_attribute6 => v_spa_rec.attribute6,
772 x_attribute7 => v_spa_rec.attribute7,
773 x_attribute8 => v_spa_rec.attribute8,
774 x_attribute9 => v_spa_rec.attribute9,
775 x_attribute10 => v_spa_rec.attribute10,
776 x_attribute11 => v_spa_rec.attribute11,
777 x_attribute12 => v_spa_rec.attribute12,
778 x_attribute13 => v_spa_rec.attribute13,
779 x_attribute14 => v_spa_rec.attribute14,
780 x_attribute15 => v_spa_rec.attribute15,
781 x_attribute16 => v_spa_rec.attribute16,
782 x_attribute17 => v_spa_rec.attribute17,
783 x_attribute18 => v_spa_rec.attribute18,
784 x_attribute19 => v_spa_rec.attribute19,
785 x_attribute20 => v_spa_rec.attribute20,
786 x_future_dated_trans_flag => v_spa_rec.future_dated_trans_flag
787 );
788 END LOOP;
789 EXCEPTION
790 WHEN OTHERS THEN
791 DECLARE
792 app_short_name VARCHAR2 (10);
793 message_name VARCHAR2 (100);
794 BEGIN
795 fnd_file.put_line (fnd_file.LOG, 'SQL Error Message :' || SQLERRM);
796 fnd_message.parse_encoded (
797 fnd_message.get_encoded,
798 app_short_name,
799 message_name
800 );
801 retcode := 2;
802 errbuf := message_name;
803 END;
804 END update_spa;
805
806
807 -- =========================================================================================
808
809
810 PROCEDURE upload_external_completion (
811 errbuf OUT NOCOPY VARCHAR2,
812 retcode OUT NOCOPY NUMBER,
813 p_batch_id IN NUMBER,
814 p_unit_set_method IN VARCHAR2
815 ) IS
816
817 /****************************************************************************************************************
818 || Created By : dlarsen
819 || Created On : 16-DEC-2002
820 || Purpose : This Job validates, uploads and then purges the Interface data for Student Unit Set Attempt
821 || and Student Program Attempt Completion
822 ||
823 || This process can be called from the concurrent manager .
824 || Known limitations, enhancements or remarks :
825 || Change History :
826 || Who When What
827 || (reverse chronological order - newest change first)
828 ||swaghmar 16-Jan-2006 Bug# 4951054 Added check for disabling UI's
829 ****************************************************************************************************************/
830
831 CURSOR c_susaci (
832 cp_batch_id igs_pr_susa_complete_int.batch_id%TYPE
833 ) IS
834 SELECT susaci.ROWID,
835 susaci.*
836 FROM igs_pr_susa_complete_int susaci
837 WHERE susaci.batch_id = cp_batch_id
838 FOR UPDATE;
839
840 CURSOR c_spaci (
841 cp_batch_id igs_pr_spa_complete_int.batch_id%TYPE
842 ) IS
843 SELECT spaci.ROWID,
844 spaci.*
845 FROM igs_pr_spa_complete_int spaci
846 WHERE spaci.batch_id = cp_batch_id
847 FOR UPDATE;
848
849 CURSOR c_susa (
850 cp_person_id igs_as_su_setatmpt.person_id%TYPE,
851 cp_course_cd igs_as_su_setatmpt.course_cd%TYPE
852 ) IS
853 SELECT susa.ROWID
854 FROM igs_as_su_setatmpt susa
855 WHERE susa.person_id = cp_person_id
856 AND susa.course_cd = cp_course_cd
857 AND susa.end_dt IS NULL
858 AND susa.rqrmnts_complete_ind = 'N';
859
860 l_susa_rowid ROWID;
861 l_spa_rowid ROWID;
862 l_susa_error_code igs_pr_susa_complete_int.error_code%TYPE;
863 l_spa_error_code igs_pr_spa_complete_int.error_code%TYPE;
864 l_person_id hz_parties.party_id%TYPE;
865 l_errbuf VARCHAR2 (1000);
866 l_retcode NUMBER (1);
867 l_message VARCHAR2 (2000);
868 invalid_parameter_combination EXCEPTION;
869 BEGIN
870 retcode := 0;
871 IGS_GE_GEN_003.SET_ORG_ID(); -- swaghmar, bug# 4951054
872
873 -- Validate the paramters
874 IF p_batch_id IS NOT NULL
875 AND p_unit_set_method IN ('COMPLETED', 'ENDED') THEN
876 -- Loop through Student Unit Set Attempt completion records
877 FOR v_susaci_rec IN c_susaci (p_batch_id) LOOP
878 -- Validate the Student Program Attempt before validating the Student Unit Set Attempt
879
880 IF validate_spa_record (
881 v_susaci_rec.person_number,
882 v_susaci_rec.course_cd,
883 l_spa_error_code,
884 l_person_id,
885 l_spa_rowid
886 ) THEN
887 -- Validate the Student Unit Set Attempt
888 IF validate_susa_record (
889 l_person_id,
890 v_susaci_rec.course_cd,
891 v_susaci_rec.unit_set_cd,
892 l_susa_error_code,
893 l_susa_rowid
894 ) THEN
895 -- Update the Student Unit Set Attempt record with the completion details
896 IF p_unit_set_method = 'COMPLETED' THEN
897 -- Update the Student Unit Set Attempt record with the completion details
898 update_susa (
899 l_errbuf,
900 l_retcode,
901 l_susa_rowid,
902 NULL,
903 'N',
904 'Y',
905 v_susaci_rec.complete_dt,
906 'SYSTEM'
907 );
908 ELSE -- p_unit_set_method = 'ENDED'
909 -- Update the Student Unit Set Attempt record with the ended details
910 update_susa (
911 l_errbuf,
912 l_retcode,
913 l_susa_rowid,
914 v_susaci_rec.complete_dt,
915 'Y',
916 'N',
917 NULL,
918 NULL
919 );
920 END IF;
921
922 IF (l_retcode <> 0) THEN
923 UPDATE igs_pr_susa_complete_int
924 SET error_code = l_errbuf
925 WHERE CURRENT OF c_susaci;
926 ELSE
927 -- If there is no error delete the record from the interface table
928 DELETE FROM igs_pr_susa_complete_int
929 WHERE CURRENT OF c_susaci;
930 END IF;
931 ELSE
932 -- Otherwise update the inteface record with the error code
933 UPDATE igs_pr_susa_complete_int
934 SET error_code = l_susa_error_code
935 WHERE CURRENT OF c_susaci;
936 END IF;
937 ELSE
938 -- Otherwise update the inteface record with the error code
939 UPDATE igs_pr_susa_complete_int
940 SET error_code = l_spa_error_code
941 WHERE CURRENT OF c_susaci;
942 END IF;
943 END LOOP;
944
945 -- Loop through Student Program Attempt completion records
946 FOR v_spaci_rec IN c_spaci (p_batch_id) LOOP
947 -- Validate the Student Program Attempt
948 IF validate_spa_record (
949 v_spaci_rec.person_number,
950 v_spaci_rec.course_cd,
951 l_spa_error_code,
952 l_person_id,
953 l_spa_rowid
954 ) THEN
955 -- Find any Student Unit Set Attempt records which are not complete or ended.
956 FOR v_susa_rec IN c_susa (l_person_id, v_spaci_rec.course_cd) LOOP
957 IF p_unit_set_method = 'COMPLETED' THEN
958 -- Update the Student Unit Set Attempt record with the completion details
959 update_susa (
960 l_errbuf,
961 l_retcode,
962 v_susa_rec.ROWID,
963 NULL,
964 'N',
965 'Y',
966 v_spaci_rec.complete_dt,
967 'SYSTEM'
968 );
969 ELSE -- p_unit_set_method = 'ENDED'
970 -- Update the Student Unit Set Attempt record with the ended details
971 update_susa (
972 l_errbuf,
973 l_retcode,
974 v_susa_rec.ROWID,
975 v_spaci_rec.complete_dt,
976 'Y',
977 'N',
978 NULL,
979 NULL
980 );
981 END IF;
982 END LOOP;
983
984 -- Update the Student Program Attempt record with the completion details
985 update_spa (
986 l_errbuf,
987 l_retcode,
988 l_spa_rowid,
989 v_spaci_rec.complete_dt
990 );
991
992 -- If there is no error delete the record from the interface table
993 IF (l_retcode <> 0) THEN
994 UPDATE igs_pr_spa_complete_int
995 SET error_code = l_errbuf
996 WHERE CURRENT OF c_spaci;
997 ELSE
998 DELETE FROM igs_pr_spa_complete_int
999 WHERE CURRENT OF c_spaci;
1000 END IF;
1001 ELSE
1002 -- Otherwise update the inteface record with the error code
1003 UPDATE igs_pr_spa_complete_int
1004 SET error_code = l_spa_error_code
1005 WHERE CURRENT OF c_spaci;
1006 END IF;
1007 END LOOP;
1008 ELSE
1009 -- When the batch_id is passed as null there is no batch to process.
1010 RAISE invalid_parameter_combination;
1011 END IF;
1012
1013 COMMIT;
1014 EXCEPTION
1015 WHEN invalid_parameter_combination THEN
1016 fnd_file.put_line (
1017 fnd_file.LOG,
1018 'SQL Error Message :' || SUBSTR (SQLERRM, 1, 200)
1019 );
1020 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1021 retcode := 2;
1022 errbuf := fnd_message.get_string ('IGS', 'IGS_PR_RNK_INV_PRM');
1023 igs_ge_msg_stack.conc_exception_hndl;
1024 WHEN OTHERS THEN
1025 fnd_file.put_line (
1026 fnd_file.LOG,
1027 'SQL Error Message :' || SUBSTR (SQLERRM, 1, 200)
1028 );
1029 fnd_file.put_line (fnd_file.LOG, fnd_message.get);
1030 retcode := 2;
1031 errbuf := fnd_message.get_string ('IGS', 'IGS_GE_UNHANDLED_EXP');
1032 igs_ge_msg_stack.conc_exception_hndl;
1033 END upload_external_completion;
1034 END igs_pr_upload_ext_results;