[Home] [Help]
PACKAGE BODY: APPS.IGS_DA_XML_PKG
Source
1 PACKAGE BODY IGS_DA_XML_PKG AS
2 /* $Header: IGSDA10B.pls 120.3 2006/02/09 01:30:24 ijeddy ship $ */
3 /***************************************************************************************************************
4
5 Created By: Steven Hogan, Nimit Mankodi
6
7 Date Created By: March-04-2001
8
9 Purpose:
10
11 Known limitations,enhancements,remarks:
12
13 Change History
14
15 Who When What
16 shogan May 8 2003 l_event_key - in Submit_Event Procedure
17 rvivekan 09-sep-2003 Modified the behaviour of repeatable_ind column in igs_ps_unit_ver table
18 in function get_unit_repeatable . PSP integration build #3052433
19 smanglm 12-sep-2003 bug 3093223 - added pre_submit_event to write error report and lauch a workflow
20 shogan 20-feb-2004 Bug #3438386/3472644 - update added loop to procedure Submit_Event
21 - update added table updates with error_code.
22 - New procedure added update_stdnts_err - write error code
23 and report message.
24 - Alt. update_request_status (reduced parameters)
25 - remove call from workflow process. igsdaxmlgen.wft
26 nmankodi 03-Nov-2004 Bug 3936708 - Changed the udpate_request_status procedure.
27 - Changed the update_req_students spec and body, added p_error_code
28 jhanda 17-Jan-2005 Bug 4114100 - Changed code added cursor c_ftr_val to check for sending a single
29 large xml containing information for all students
30 bradhakr 01-mar-2005 bug -4210676 - Modify the procedure call igs_pr_cp_gpa.get_sua_all to include uoo_id
31
32 or multiple small xml's 1 for each student.
33 | nmankodi 11-Apr-2005 fnd_user.customer_id column has been changed to
34 | fnd_user.person_party_id as an ebizsuite wide TCA mandate.
35 swaghmar 15-Sep-2005 bug# 4491456
36 nmankodi 16-Sep-2005 Bug# 4613611
37 ijeddy 05-Dec-2006 Bug 4755785 - Modified the update_req_students procedure and added the IF conditions to
38 check the variable if not null then do the string operation
39
40 ****************************************************************************************************************** */
41
42 --
43 --
44 --
45 g_pkg_name CONSTANT VARCHAR2(30) := 'IGS_DA_XML_PKG';
46 PROCEDURE populate_sua_table
47 (
48 p_person_id IN igs_en_su_attempt.person_id%TYPE,
49 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
50 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
51 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
52 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
53 ) AS
54 --
55 v_gpa NUMBER;
56 v_gpa_cp NUMBER;
57 v_gpa_qp NUMBER;
58 v_earned_cp NUMBER;
59 v_attempted_cp NUMBER;
60 v_init_msg_list VARCHAR2(20):= FND_API.G_TRUE;
61 v_return_status VARCHAR2(1);
62 v_msg_count NUMBER;
63 v_msg_data VARCHAR2(2000);
64 v_s_result_type igs_as_grd_sch_grade.s_result_type%TYPE;
65 v_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE;
66 v_gs_version_number igs_as_grd_sch_grade.version_number%TYPE;
67 v_grade igs_as_grd_sch_grade.grade%TYPE;
68 v_mark igs_as_su_stmptout.mark%TYPE;
69 v_outcome_dt igs_as_su_stmptout.outcome_dt%TYPE;
70 v_origin_course_cd igs_as_su_stmptout.course_cd%TYPE;
71 v_unit_cd igs_en_su_attempt.unit_cd%TYPE;
72 v_unit_version_number igs_en_su_attempt.version_number%TYPE;
73 v_teach_cal_type igs_en_su_attempt.cal_type%TYPE;
74 v_teach_ci_sequence_number igs_en_su_attempt.ci_sequence_number%TYPE;
75 v_dummy VARCHAR2(1);
76 --
77 CURSOR c_sua IS
78 SELECT sua.unit_cd,
79 sua.version_number,
80 sua.cal_type,
81 sua.ci_sequence_number
82 FROM igs_en_su_attempt sua
83 WHERE sua.person_id = p_person_id
84 AND sua.course_cd = p_course_cd
85 AND sua.uoo_id = p_uoo_id;
86 --
87 CURSOR c_gsg (
88 cp_grading_schema_cd igs_as_grd_sch_grade.grading_schema_cd%TYPE,
89 cp_version_number igs_as_grd_sch_grade.version_number%TYPE,
90 cp_grade igs_as_grd_sch_grade.grade%TYPE
91 ) IS
92 SELECT 'X'
93 FROM igs_as_grd_sch_grade gsg
94 WHERE cp_grading_schema_cd = gsg.grading_schema_cd
95 AND cp_version_number = gsg.version_number
96 AND cp_grade = gsg.grade
97 AND gsg.show_on_official_ntfctn_ind = 'Y';
98 --
99 BEGIN
100 --
101 OPEN c_sua;
102 FETCH c_sua INTO v_unit_cd,
103 v_unit_version_number,
104 v_teach_cal_type,
105 v_teach_ci_sequence_number;
106 CLOSE c_sua;
107 --
108 v_s_result_type := igs_as_gen_003.assp_get_sua_outcome (
109 p_person_id,
110 p_course_cd,
111 v_unit_cd,
112 v_teach_cal_type,
113 v_teach_ci_sequence_number,
114 p_unit_attempt_status,
115 'Y',
116 v_outcome_dt,
117 v_grading_schema_cd,
118 v_gs_version_number,
119 v_grade,
120 v_mark,
121 v_origin_course_cd,
122 p_uoo_id,
123 --added by LKAKI---
124 'N');
125 --
126 OPEN c_gsg (v_grading_schema_cd,
127 v_gs_version_number,
128 v_grade);
129 FETCH c_gsg INTO v_dummy;
130 IF c_gsg%NOTFOUND THEN
131 v_grade := NULL;
132 v_mark := NULL;
133 END IF;
134 CLOSE c_gsg;
135 --
136 -- Get the Student Unit Attempt CP and GPA values
137 --
138 igs_pr_cp_gpa.get_sua_all (
139 p_person_id => p_person_id,
140 p_course_cd => p_course_cd,
141 p_unit_cd => v_unit_cd,
142 p_unit_version_number => v_unit_version_number,
143 p_teach_cal_type => v_teach_cal_type,
144 p_teach_ci_sequence_number => v_teach_ci_sequence_number,
145 p_stat_type => p_stat_type,
146 p_system_stat => NULL,
147 p_earned_cp => v_earned_cp,
148 p_attempted_cp => v_attempted_cp,
149 p_gpa_value => v_gpa,
150 p_gpa_cp => v_gpa_cp,
151 p_gpa_quality_points => v_gpa_qp,
152 p_init_msg_list => v_init_msg_list,
153 p_return_status => v_return_status,
154 p_msg_count => v_msg_count,
155 p_msg_data => v_msg_data,
156 p_uoo_id => p_uoo_id
157 );
158
159 --
160 -- Store all of the values in the SUA temp table
161 --
162 sua_table(1).person_id := p_person_id;
163 sua_table(1).course_cd := p_course_cd;
164 sua_table(1).uoo_id := p_uoo_id;
165 sua_table(1).grade := v_grade;
166 sua_table(1).mark := v_mark;
167 sua_table(1).s_result_type := v_s_result_type;
168 sua_table(1).gpa := v_gpa;
169 sua_table(1).gpa_cp := v_gpa_cp;
170 sua_table(1).gpa_qp := v_gpa_qp;
171 sua_table(1).earned_cp := v_earned_cp;
172 sua_table(1).attempted_cp := v_attempted_cp;
173 --
174 END populate_sua_table;
175
176 --
177 --swaghmar 15-Sep-2005 Bug# 4491456
178 --
179 PROCEDURE populate_load_table
180 (
181 p_person_id IN igs_en_su_attempt.person_id%TYPE,
182 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
183 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
184 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
185 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
186 ) AS
187 --
188 v_cum_gpa NUMBER;
189 v_cum_gpa_cp NUMBER;
190 v_cum_gpa_qp NUMBER;
191 v_cum_earned_cp NUMBER;
192 v_cum_attempted_cp NUMBER;
193 v_init_msg_list VARCHAR2(20) := FND_API.G_TRUE;
194 v_return_status VARCHAR2(1);
195 v_msg_count NUMBER;
196 v_msg_data VARCHAR2(2000);
197 --
198 BEGIN
199 --
200 -- Get the Cum Student Program Attempt CP values
201 -- and Student Program Attempt GPA values
202 --
203 igs_pr_cp_gpa.get_all_stats(
204 p_person_id => p_person_id,
205 p_course_cd => p_course_cd,
206 p_stat_type => p_stat_type,
207 p_load_cal_type => p_load_cal_type,
208 p_load_ci_sequence_number => p_load_ci_sequence_number,
209 p_system_stat => NULL,
210 p_cumulative_ind => 'Y',
211 p_earned_cp => v_cum_earned_cp,
212 p_attempted_cp => v_cum_attempted_cp,
213 p_gpa_value => v_cum_gpa,
214 p_gpa_cp => v_cum_gpa_cp,
215 p_gpa_quality_points => v_cum_gpa_qp,
216 p_init_msg_list => v_init_msg_list,
217 p_return_status => v_return_status,
218 p_msg_count => v_msg_count,
219 p_msg_data => v_msg_data
220 );
221
222 --
223 -- Store all of the values in the SUA temp table
224 --
225 load_table(1).person_id := p_person_id;
226 load_table(1).course_cd := p_course_cd;
227 load_table(1).load_cal_type := p_load_cal_type;
228 load_table(1).load_ci_sequence_number := p_load_ci_sequence_number;
229 load_table(1).cum_gpa := v_cum_gpa;
230 load_table(1).cum_gpa_cp := v_cum_gpa_cp;
231 load_table(1).cum_gpa_qp := v_cum_gpa_qp;
232 load_table(1).cum_earned_cp := v_cum_earned_cp;
233 load_table(1).cum_attempted_cp := v_cum_attempted_cp;
234 --
235 END populate_load_table;
236 --
237
238 --
239 --
240 --
241 FUNCTION get_sua_gpa_cp
242 (
243 p_person_id IN igs_en_su_attempt.person_id%TYPE,
244 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
245 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
246 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
247 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
248 ) RETURN NUMBER AS
249 --
250 BEGIN
251 --
252 IF sua_table.EXISTS(1) AND
253 sua_table(1).person_id = p_person_id AND
254 sua_table(1).course_cd = p_course_cd AND
255 sua_table(1).uoo_id = p_uoo_id THEN
256 --
257 RETURN sua_table(1).gpa_cp;
258 --
259 ELSE
260 --
261 populate_sua_table (
262 p_person_id,
263 p_course_cd,
264 p_uoo_id,
265 p_unit_attempt_status,
266 p_stat_type
267 );
268 --
269 RETURN sua_table(1).gpa_cp;
270 --
271 END IF;
272 --
273 END get_sua_gpa_cp;
274 --
275 --
276 --
277 FUNCTION get_sua_gpa_qp
278 (
279 p_person_id IN igs_en_su_attempt.person_id%TYPE,
280 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
281 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
282 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
283 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
284 ) RETURN NUMBER AS
285 --
286 BEGIN
287 --
288 IF sua_table.EXISTS(1) AND
289 sua_table(1).person_id = p_person_id AND
290 sua_table(1).course_cd = p_course_cd AND
291 sua_table(1).uoo_id = p_uoo_id THEN
292 --
293 RETURN sua_table(1).gpa_qp;
294 --
295 ELSE
296 --
297 populate_sua_table (
298 p_person_id,
299 p_course_cd,
300 p_uoo_id,
301 p_unit_attempt_status,
302 p_stat_type
303 );
304 --
305 RETURN sua_table(1).gpa_qp;
306 --
307 END IF;
308 --
309 END get_sua_gpa_qp;
310 --
311 --
312 --
313 FUNCTION get_sua_earned_cp
314 (
315 p_person_id IN igs_en_su_attempt.person_id%TYPE,
316 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
317 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
318 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
319 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
320 ) RETURN NUMBER AS
321 --
322 BEGIN
323 --
324 IF sua_table.EXISTS(1) AND
325 sua_table(1).person_id = p_person_id AND
326 sua_table(1).course_cd = p_course_cd AND
327 sua_table(1).uoo_id = p_uoo_id THEN
328 --
329 RETURN sua_table(1).earned_cp;
330 --
331 ELSE
332 --
333 populate_sua_table (
334 p_person_id,
335 p_course_cd,
336 p_uoo_id,
337 p_unit_attempt_status,
338 p_stat_type
339 );
340 --
341 RETURN sua_table(1).earned_cp;
342 --
343 END IF;
344 --
345 END get_sua_earned_cp;
346 --
347 --
348 --
349 FUNCTION get_sua_attempted_cp
350 (
351 p_person_id IN igs_en_su_attempt.person_id%TYPE,
352 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
353 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
354 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
355 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
356 ) RETURN NUMBER AS
357 --
358 BEGIN
359 --
360 IF sua_table.EXISTS(1) AND
361 sua_table(1).person_id = p_person_id AND
362 sua_table(1).course_cd = p_course_cd AND
363 sua_table(1).uoo_id = p_uoo_id THEN
364 --
365 RETURN sua_table(1).attempted_cp;
366 --
367 ELSE
368 --
369 populate_sua_table (
370 p_person_id,
371 p_course_cd,
372 p_uoo_id,
373 p_unit_attempt_status,
374 p_stat_type
375 );
376 --
377 RETURN sua_table(1).attempted_cp;
378 --
379 END IF;
380 --
381 END get_sua_attempted_cp;
382 --
383 --
384 --
385 FUNCTION get_sua_grade
386 (
387 p_person_id IN igs_en_su_attempt.person_id%TYPE,
388 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
389 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
390 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
391 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
392 ) RETURN VARCHAR2 AS
393 --
394 BEGIN
395 --
396 IF sua_table.EXISTS(1) AND
397 sua_table(1).person_id = p_person_id AND
398 sua_table(1).course_cd = p_course_cd AND
399 sua_table(1).uoo_id = p_uoo_id THEN
400 --
401 RETURN sua_table(1).grade;
402 --
403 ELSE
404 --
405 populate_sua_table (
406 p_person_id,
407 p_course_cd,
408 p_uoo_id,
409 p_unit_attempt_status,
410 p_stat_type
411 );
412 --
413 RETURN sua_table(1).grade;
414 --
415 END IF;
416 --
417 END get_sua_grade;
418 --
419 --
420 --
421 FUNCTION get_sua_mark
422 (
423 p_person_id IN igs_en_su_attempt.person_id%TYPE,
424 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
425 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
426 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
427 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
428 ) RETURN NUMBER AS
429 --
430 BEGIN
431 --
432 IF sua_table.EXISTS(1) AND
433 sua_table(1).person_id = p_person_id AND
434 sua_table(1).course_cd = p_course_cd AND
435 sua_table(1).uoo_id = p_uoo_id THEN
436 --
437 RETURN sua_table(1).mark;
438 --
439 ELSE
440 --
441 populate_sua_table (
442 p_person_id,
443 p_course_cd,
444 p_uoo_id,
445 p_unit_attempt_status,
446 p_stat_type
447 );
448 --
449 RETURN sua_table(1).mark;
450 --
451 END IF;
452 --
453 END get_sua_mark;
454 --
455 --
456 --
457 FUNCTION get_sua_result_type
458 (
459 p_person_id IN igs_en_su_attempt.person_id%TYPE,
460 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
461 p_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
462 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
463 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
464 ) RETURN VARCHAR2 AS
465 --
466 BEGIN
467 --
468 IF sua_table.EXISTS(1) AND
469 sua_table(1).person_id = p_person_id AND
470 sua_table(1).course_cd = p_course_cd AND
471 sua_table(1).uoo_id = p_uoo_id THEN
472 --
473 RETURN sua_table(1).mark;
474 --
475 ELSE
476 --
477 populate_sua_table (
478 p_person_id,
479 p_course_cd,
480 p_uoo_id,
481 p_unit_attempt_status,
482 p_stat_type
483 );
484 --
485 RETURN sua_table(1).s_result_type;
486 --
487 END IF;
488 --
489 END get_sua_result_type;
490 --
491 --
492 --
493 FUNCTION get_cum_gpa
494 (
495 p_person_id IN igs_en_su_attempt.person_id%TYPE,
496 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
497 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
498 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
499 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
500 ) RETURN NUMBER AS
501 --
502 BEGIN
503 --
504 IF load_table.EXISTS(1) AND
505 load_table(1).person_id = p_person_id AND
506 load_table(1).course_cd = p_course_cd AND
507 load_table(1).load_cal_type = p_load_cal_type AND
508 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
509 --
510 RETURN load_table(1).cum_gpa;
511 ELSE
512 --
513 populate_load_table (
514 p_person_id,
515 p_course_cd,
516 p_load_cal_type,
517 p_load_ci_sequence_number,
518 p_stat_type
519 );
520 --
521 RETURN load_table(1).cum_gpa;
522 --
523 END IF;
524 --
525 END get_cum_gpa;
526 --
527 --
528 --
529 FUNCTION get_cum_gpa_cp
530 (
531 p_person_id IN igs_en_su_attempt.person_id%TYPE,
532 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
533 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
534 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
535 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
536 ) RETURN NUMBER AS
537 --
538 BEGIN
539 --
540 IF load_table.EXISTS(1) AND
541 load_table(1).person_id = p_person_id AND
542 load_table(1).course_cd = p_course_cd AND
543 load_table(1).load_cal_type = p_load_cal_type AND
544 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
545 --
546 RETURN load_table(1).cum_gpa_cp;
547 ELSE
548 --
549 populate_load_table (
550 p_person_id,
551 p_course_cd,
552 p_load_cal_type,
553 p_load_ci_sequence_number,
554 p_stat_type
555 );
556 --
557 RETURN load_table(1).cum_gpa_cp;
558 --
559 END IF;
560 --
561 END get_cum_gpa_cp;
562 --
563 --
564 --
565 FUNCTION get_cum_gpa_qp
566 (
567 p_person_id IN igs_en_su_attempt.person_id%TYPE,
568 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
569 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
570 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
571 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
572 ) RETURN NUMBER AS
573 --
574 BEGIN
575 --
576 IF load_table.EXISTS(1) AND
577 load_table(1).person_id = p_person_id AND
578 load_table(1).course_cd = p_course_cd AND
579 load_table(1).load_cal_type = p_load_cal_type AND
580 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
581 --
582 RETURN load_table(1).cum_gpa_qp;
583 ELSE
584 --
585 populate_load_table (
586 p_person_id,
587 p_course_cd,
588 p_load_cal_type,
589 p_load_ci_sequence_number,
590 p_stat_type
591 );
592 --
593 RETURN load_table(1).cum_gpa_qp;
594 --
595 END IF;
596 --
597 END get_cum_gpa_qp;
598 --
599 --
600 --
601 FUNCTION get_cum_earned_cp
602 (
603 p_person_id IN igs_en_su_attempt.person_id%TYPE,
604 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
605 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
606 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
607 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
608 ) RETURN NUMBER AS
609 --
610 BEGIN
611 --
612 IF load_table.EXISTS(1) AND
613 load_table(1).person_id = p_person_id AND
614 load_table(1).course_cd = p_course_cd AND
615 load_table(1).load_cal_type = p_load_cal_type AND
616 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
617 --
618 RETURN load_table(1).cum_earned_cp;
619 ELSE
620 --
621 populate_load_table (
622 p_person_id,
623 p_course_cd,
624 p_load_cal_type,
625 p_load_ci_sequence_number,
626 p_stat_type
627 );
628 --
629 RETURN load_table(1).cum_earned_cp;
630 --
631 END IF;
632 --
633 END get_cum_earned_cp;
634 --
635 --
636 --
637 FUNCTION get_cum_attempted_cp
638 (
639 p_person_id IN igs_en_su_attempt.person_id%TYPE,
640 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
641 p_load_cal_type IN igs_en_su_attempt.cal_type%TYPE,
642 p_load_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
643 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
644 ) RETURN NUMBER AS
645 --
646 BEGIN
647 --
648 IF load_table.EXISTS(1) AND
649 load_table(1).person_id = p_person_id AND
650 load_table(1).course_cd = p_course_cd AND
651 load_table(1).load_cal_type = p_load_cal_type AND
652 load_table(1).load_ci_sequence_number = p_load_ci_sequence_number THEN
653 --
654 RETURN load_table(1).cum_attempted_cp;
655 ELSE
656 --
657 populate_load_table (
658 p_person_id,
659 p_course_cd,
660 p_load_cal_type,
661 p_load_ci_sequence_number,
662 p_stat_type
663 );
664 --
665 RETURN load_table(1).cum_attempted_cp;
666 --
667 END IF;
668 --
669 END get_cum_attempted_cp;
670
671
672 -- ============================================================================
673
674 --
675 --
676
677 FUNCTION get_course_abbr_num
678 (
679 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
680 p_evaluation_type IN CHAR DEFAULT 'A'
681 ) RETURN VARCHAR2 AS
682 --
683 l_count NUMBER := 0;
684 l_str_length NUMBER := 0;
685 --
686 BEGIN
687 --
688 l_count := 1;
689 l_str_length:= LENGTH(p_unit_cd);
690 --
691 IF (l_count <= l_str_length) THEN
692 LOOP
693 --
694 IF SUBSTR (p_unit_cd, l_count, l_count) NOT BETWEEN 'A' and 'Z'
695 THEN
696 --
697 IF (p_evaluation_type = 'A') THEN
698 RETURN SUBSTR (p_unit_cd, 0, l_count-1);
699 ELSE
700 RETURN SUBSTR (p_unit_cd, l_count, LENGTH(p_unit_cd));
701 END IF;
702 --
703 ELSE
704 --
705 IF (l_count <= l_str_length) THEN
706 l_count:= l_count + 1;
707 ELSE
708 --
709 IF (p_evaluation_type = 'A') THEN
710 RETURN p_unit_cd;
711 ELSE
712 RETURN ' ';
713 END IF;
714 --
715 END IF;
716 --
717 END IF;
718 --
719 END LOOP;
720 ELSE
721 --
722 IF (p_evaluation_type = 'A') THEN
723 RETURN p_unit_cd;
724 ELSE
725 RETURN ' ';
726 END IF;
727 --
728 END IF;
729 --
730 END get_course_abbr_num;
731 --
732 --
733 -- student_type_list combines the student type into a single list
734 -- of student type i.e. Student, Instructor, etc...
735 -- Default return is not student type...
736 FUNCTION student_type_list
737 (p_person_id IN igs_en_su_attempt.person_id%TYPE
738 ) RETURN VARCHAR2 AS
739
740 l_count NUMBER := 0;
741 l_counter NUMBER := 0;
742 l_str_length NUMBER := 0;
743 l_student_type VARCHAR2(100):= '';
744
745 CURSOR c_student_type_data IS
746 SELECT person_type_code
747 FROM igs_pe_typ_instances_all
748 WHERE end_date IS NULL
749 AND person_id = p_person_id;
750
751 l_student_type_rec c_student_type_data%ROWTYPE;
752
753 TYPE r_student_type_rec IS RECORD (
754 person_type_code igs_pe_typ_instances_all.person_type_code%TYPE);
755 TYPE t_student_type_table IS TABLE OF r_student_type_rec INDEX BY BINARY_INTEGER;
756 student_type_table t_student_type_table;
757
758 BEGIN
759
760 OPEN c_student_type_data;
761 LOOP
762 FETCH c_student_type_data INTO l_student_type_rec;
763 student_type_table(l_count).person_type_code := l_student_type_rec.person_type_code;
764 l_count := c_student_type_data%ROWCOUNT;
765 EXIT WHEN c_student_type_data%NOTFOUND;
766 END LOOP;
767 CLOSE c_student_type_data;
768 IF (l_count = 0) THEN
769 RETURN student_type_table(0).person_type_code;
770 END IF;
771 IF (l_count >= 1) THEN
772 l_student_type:= student_type_table(l_counter).person_type_code;
773 l_counter:= l_counter + 1;
774 LOOP
775 l_student_type:= l_student_type || ', ' || student_type_table(l_counter).person_type_code;
776 l_counter := l_counter + 1;
777 IF (l_counter >= l_count) THEN
778 RETURN l_student_type;
779 END IF;
780 END LOOP;
781 RETURN l_student_type;
782 END IF;
783 RETURN l_count;
784 END student_type_list;
785 --
786 --
787 --
788 --
789 FUNCTION get_unit_repeatable
790 (
791 p_person_id IN igs_en_su_attempt.person_id%TYPE,
792 p_course_cd IN igs_en_su_attempt.course_cd%TYPE,
793 p_unit_cd IN igs_en_su_attempt.unit_cd%TYPE,
794 p_version_number IN igs_en_su_attempt.version_number%TYPE,
795 p_teach_cal_type IN igs_en_su_attempt.cal_type%TYPE,
796 p_teach_ci_sequence_number IN igs_en_su_attempt.ci_sequence_number%TYPE,
797 p_unit_attempt_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
798 p_stat_type IN igs_pr_org_stat.stat_type%TYPE
799 ) RETURN VARCHAR2 AS
800 --
801 CURSOR suao_gsg_cur is
802 SELECT suao.grade, gsg.grade valid_grade, gsg.repeat_grade
803 FROM igs_as_grd_sch_grade gsg, igs_as_su_stmptout_all suao
804 WHERE
805 suao.unit_cd = p_unit_cd
806 AND suao.finalised_outcome_ind = 'Y'
807 AND suao.grade = gsg.grade
808 AND suao.grading_schema_cd = gsg.grading_schema_cd
809 AND suao.person_id = p_person_id
810 AND suao.unit_cd = p_unit_cd
811 AND suao.course_cd = p_course_cd
812 AND suao.version_number = p_version_number
813 AND suao.cal_type = p_teach_cal_type
814 AND suao.ci_sequence_number = p_teach_ci_sequence_number;
815
816 l_suao_gsg_rec suao_gsg_cur%ROWTYPE;
817 TYPE r_suao_gsg_rec IS RECORD (
818 grade igs_as_su_stmptout_all.grade%TYPE,
819 valid_grade igs_as_grd_sch_grade.grade%TYPE,
820 repeat_grade igs_as_grd_sch_grade.repeat_grade%TYPE
821 );
822 TYPE t_suao_gsg_table IS TABLE OF r_suao_gsg_rec INDEX BY BINARY_INTEGER;
823 suao_gsg_table t_suao_gsg_table;
824 --
825 CURSOR repeatable_cur is
826 SELECT uv.repeatable_ind
827 FROM hz_parties p, igs_en_stdnt_ps_att spa, igs_ps_ver crv, igs_ca_teach_to_load_v ttl,
828 igs_en_su_attempt sua, igs_ps_unit_ver uv, igs_ps_unit_ofr_opt uoo, IGS_PS_PRG_UNIT_REL pur,
829 igs_as_su_atmptout_h_all atm
830 WHERE p.party_id = sua.person_id
831 AND sua.person_id = p_person_id
832 AND sua.unit_cd = p_unit_cd
833 AND sua.course_cd = p_course_cd
834 AND sua.version_number = p_version_number
835 AND sua.cal_type = p_teach_cal_type
836 AND sua.ci_sequence_number = p_teach_ci_sequence_number
837 AND sua.unit_attempt_status = p_unit_attempt_status
838 AND sua.person_id = spa.person_id
839 AND sua.course_cd = spa.course_cd
840 AND sua.person_id = atm.person_id
841 AND sua.course_cd = atm.course_cd
842 AND atm.unit_cd = sua.unit_cd
843 AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED','DUPLICATE','DISCONTIN')
844 AND uv.unit_cd = sua.unit_cd
845 AND uv.version_number = sua.version_number
846 AND sua.uoo_id = uoo.uoo_id
847 AND spa.course_cd = crv.course_cd
848 AND spa.version_number = crv.version_number
849 AND sua.cal_type = ttl.teach_cal_type
850 AND sua.ci_sequence_number = ttl.teach_ci_sequence_number
851 AND (sua.student_career_transcript = 'Y'
852 OR (sua.student_career_transcript IS NULL
853 AND pur.unit_type_id = uv.unit_type_id
854 AND pur.student_career_level = crv.course_type
855 AND pur.student_career_transcript = 'Y'));
856
857 l_repeatable_rec repeatable_cur%ROWTYPE;
858
859 TYPE r_repeatable_rec IS RECORD (
860 repeatable_ind igs_ps_unit_ver.repeatable_ind%TYPE
861 );
862 TYPE t_repeat_table IS TABLE OF r_repeatable_rec INDEX BY BINARY_INTEGER;
863 repeat_table t_repeat_table;
864
865 l_count NUMBER := 0;
866 l_str_length NUMBER := 0;
867
868 BEGIN
869 OPEN repeatable_cur;
870 LOOP
871 FETCH repeatable_cur INTO l_repeatable_rec;
872 repeat_table(l_count).repeatable_ind := l_repeatable_rec.repeatable_ind;
873 EXIT WHEN repeatable_cur%ROWCOUNT > 2 OR repeatable_cur%NOTFOUND;
874 -- Check to see if unit is repeatable
875 IF (repeat_table(l_count).repeatable_ind = 'N') THEN
876 l_count := repeatable_cur%ROWCOUNT;
877 END IF; -- end check of repeat indicator
878 END LOOP;
879 CLOSE repeatable_cur;
880 -- Is there more than one attempt on the unit?
881 IF (l_count > 1) THEN
882 l_count := 0;
883 OPEN suao_gsg_cur;
884 LOOP
885 FETCH suao_gsg_cur INTO l_suao_gsg_rec;
886 suao_gsg_table(l_count).grade := l_suao_gsg_rec.grade;
887 suao_gsg_table(l_count).valid_grade := l_suao_gsg_rec.grade;
888 suao_gsg_table(l_count).repeat_grade := l_suao_gsg_rec.repeat_grade;
889
890 IF (l_count-1 > 0) THEN
891 IF ((suao_gsg_table(l_count).grade = suao_gsg_table(l_count -1).repeat_grade
892 AND suao_gsg_table(l_count -1).repeat_grade <> NULL)
893 OR suao_gsg_table(l_count).grade <> NULL ) THEN
894 RETURN 'true';
895 ELSE
896 RETURN 'false';
897 END IF;
898 ELSE
899 RETURN 'false';
900 END IF;
901 l_count := l_count + 1;
902 END LOOP;
903 CLOSE suao_gsg_cur;
904 END IF;
905 RETURN 'false';
906 --
907 --
908 --
909 END get_unit_repeatable;
910
911
912 PROCEDURE get_person_details
913 (
914 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
915 p_person_id_code_type IN igs_pe_alt_pers_id.api_person_id%TYPE,
916 p_person_id OUT NOCOPY hz_parties.party_id%TYPE,
917 p_person_number OUT NOCOPY hz_parties.party_number%TYPE) IS
918
919 CURSOR c1 IS
920 SELECT hp.party_id
921 FROM hz_parties hp
922 WHERE hp.party_number = p_person_id_code;
923
924 CURSOR c2 IS
925 SELECT hp.party_id, hp.party_number
926 FROM hz_parties hp,
927 igs_pe_alt_pers_id api,
928 igs_da_setup ds
929 WHERE ds.s_control_num = 1
930 AND api.api_person_id = p_person_id_code
931 AND api.person_id_type = ds.default_student_id_type
932 AND api.pe_person_id = hp.party_id
933 AND (api.end_dt >= SYSDATE or api.end_dt is null);
934
935
936 BEGIN
937
938 IF p_person_id_code_type = 'OSS' THEN
939 FOR v_dummy IN c1 LOOP
940 p_person_id := v_dummy.party_id;
941 END LOOP;
942 p_person_number := p_person_id_code;
943
944 ELSE
945 FOR v_dummy IN c2 LOOP
946 p_person_id := v_dummy.party_id;
947 p_person_number := v_dummy.party_number;
948 END LOOP;
949 END IF;
950 RETURN;
951
952 END;
953
954 PROCEDURE update_stdnts_err (
955 p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
956 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
957 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
958 p_report_text IN igs_da_req_stdnts.report_text%TYPE,
959 p_error_code IN igs_da_req_stdnts.error_code%TYPE,
960 x_return_status OUT NOCOPY VARCHAR2
961 ) IS
962 l_err_msg_pos1 VARCHAR2(30);
963 l_person_id hz_parties.party_id%TYPE;
964 l_person_number hz_parties.party_number%TYPE;
965
966
967 CURSOR c1 IS
968 SELECT ROWID,drs.*
969 FROM igs_da_req_stdnts drs
970 WHERE drs.batch_id = p_batch_id
971 AND drs.person_id = l_person_id;
972
973 l_found BOOLEAN := FALSE;
974
975 BEGIN
976 ecx_debug.push('IGS_DA_XML_PKG.UPDATE_STDNTS_ERR');
977 x_return_status := 'S';
978
979 get_person_details (p_person_id_code,p_person_id_code_type,l_person_id,l_person_number);
980
981 l_err_msg_pos1 := 'IGS_PROGRAM_ERROR C1';
982 FOR v_dummy IN c1 LOOP
983 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
984 (v_dummy.ROWID,
985 v_dummy.batch_id,
986 v_dummy.igs_da_req_stdnts_id,
987 v_dummy.person_id,
988 v_dummy.program_code,
989 v_dummy.wif_program_code,
990 v_dummy.special_program_code,
991 v_dummy.major_unit_set_cd,
992 v_dummy.program_major_code,
993 p_report_text,
994 v_dummy.wif_id,
995 'R',
996 p_error_code);
997 l_found := TRUE;
998 END LOOP;
999
1000 --Update the request status bug #3438386
1001 IF (l_found) THEN
1002 update_request_status(p_batch_id);
1003 END IF;
1004
1005 IF NOT (l_found) THEN
1006 ecx_debug.pl (0, 'IGS', 'IGS_PROCEDURE_EXECUTION', 'PROCEDURE_NAME',g_pkg_name);
1007 ecx_debug.pl (0, 'IGS', 'IGS_ERROR_MSG'||'Error Code =' || p_error_code ,'E: Unable to Update');
1008 x_return_status := 'E: Unable to Update';
1009 END IF;
1010
1011 ecx_debug.pop('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1012 -- Check if the error code updated successfully , else return status error.
1013 EXCEPTION
1014 WHEN NO_DATA_FOUND THEN
1015 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1016 ecx_debug.pl(0,'IGS','IGS_ERROR_MSG - NO DATA','ERROR_MESSAGE',SQLERRM);
1017 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1018 x_return_status := 'E: UPDATE FAILED';
1019 WHEN OTHERS THEN
1020 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1021 ecx_debug.pl(0,'IGS','IGS_ERROR_MSG - OTHERS','ERROR_MESSAGE',SQLERRM);
1022 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1023 x_return_status := 'E: Other Exception';
1024
1025 END;
1026
1027
1028 PROCEDURE update_req_students (
1029 p_batch_id IN igs_da_req_stdnts.batch_id%TYPE,
1030 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
1031 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
1032 p_report_text IN igs_da_req_stdnts.report_text%TYPE,
1033 p_academicsubprogram_codes IN VARCHAR2,
1034 p_program_code IN igs_da_req_stdnts.program_code%TYPE,
1035 p_error_code IN igs_da_req_stdnts.error_code%TYPE,
1036 x_return_status OUT NOCOPY VARCHAR2
1037 ) IS
1038
1039 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REQ_STUDENTS';
1040 l_error_code VARCHAR2(30);
1041 l_person_id hz_parties.party_id%TYPE;
1042 l_person_number hz_parties.party_number%TYPE;
1043 l_progmajor_ind igs_da_setup.program_definition_ind%TYPE;
1044 l_prog_comparison_type igs_da_rqst.program_comparison_type%TYPE;
1045 pos1 NUMBER ;
1046 pos2 NUMBER ;
1047 pos3 NUMBER ;
1048 l_academicsubprogramcode1 igs_en_unit_set.unit_set_cd%TYPE;
1049 l_academicsubprogramcode2 igs_en_unit_set.unit_set_cd%TYPE;
1050 l_academicsubprogramcode3 igs_en_unit_set.unit_set_cd%TYPE;
1051
1052 CURSOR c_prog_comp IS
1053 SELECT dr.program_comparison_type
1054 FROM igs_da_rqst dr
1055 WHERE dr.batch_id = RTRIM(LTRIM(p_batch_id)) ;
1056
1057 CURSOR c_prog_def IS
1058 SELECT ds.program_definition_ind
1059 FROM igs_da_setup ds
1060 WHERE ds.s_control_num = 1;
1061
1062 CURSOR c1 IS
1063 SELECT ROWID,drs.*
1064 FROM igs_da_req_stdnts drs
1065 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1066 AND drs.person_id = l_person_id
1067 AND drs.program_major_code = RTRIM(LTRIM(p_program_code));
1068
1069 CURSOR c2 IS
1070 SELECT ROWID,drs.*
1071 FROM igs_da_req_stdnts drs
1072 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1073 AND drs.person_id = l_person_id
1074 AND drs.program_major_code = RTRIM(LTRIM(p_program_code))
1075 AND EXISTS (SELECT 'X'
1076 FROM igs_da_req_wif drw
1077 WHERE drw.batch_id = drs.batch_id
1078 AND drw.wif_id = drs.wif_id
1079 AND drw.program_code = drs.wif_program_code
1080 AND drw.major_unit_set_cd1 = drs.major_unit_set_cd
1081 AND (drw.minor_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode1))
1082 AND EXISTS (SELECT 'X'
1083 FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
1084 WHERE eus.unit_set_status=euss.unit_set_status
1085 AND euss.s_unit_set_status = 'ACTIVE'
1086 AND eus.unit_set_cd = drw.minor_unit_set_cd1
1087 AND eus.unit_set_cat = ds.wif_minor_unit_set_cat
1088 AND ds.s_control_num =1)
1089 OR drw.minor_unit_set_cd1 IS NULL)
1090 AND (drw.track_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode2))
1091 AND EXISTS (SELECT 'X'
1092 FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
1093 WHERE eus.unit_set_status=euss.unit_set_status
1094 AND euss.s_unit_set_status = 'ACTIVE'
1095 AND eus.unit_set_cd = drw.track_unit_set_cd1
1096 AND eus.unit_set_cat = ds.wif_track_unit_set_cat
1097 AND ds.s_control_num =1)
1098 OR drw.track_unit_set_cd1 IS NULL));
1099
1100 CURSOR c3 IS
1101 SELECT ROWID,drs.*
1102 FROM igs_da_req_stdnts drs
1103 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1104 AND drs.person_id = l_person_id
1105 AND drs.program_code = RTRIM(LTRIM(p_program_code));
1106
1107 CURSOR c4 IS
1108 SELECT ROWID,drs.*
1109 FROM igs_da_req_stdnts drs
1110 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1111 AND drs.person_id = l_person_id
1112 AND drs.special_program_code = RTRIM(LTRIM(p_program_code));
1113
1114 CURSOR c5 IS
1115 SELECT ROWID,drs.*
1116 FROM igs_da_req_stdnts drs
1117 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1118 AND drs.person_id = l_person_id
1119 AND drs.wif_program_code = RTRIM(LTRIM(p_program_code))
1120 AND exists (SELECT 'X'
1121 FROM igs_da_req_wif drw
1122 WHERE drw.batch_id = drs.batch_id
1123 AND drw.wif_id = drs.wif_id
1124 AND drw.program_code = drs.wif_program_code
1125 AND (drw.major_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode1))
1126 AND EXISTS (SELECT 'X'
1127 FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
1128 WHERE eus.unit_set_status=euss.unit_set_status
1129 AND euss.s_unit_set_status = 'ACTIVE'
1130 AND eus.unit_set_cd = drw.major_unit_set_cd1
1131 AND eus.unit_set_cat = ds.wif_major_unit_set_cat
1132 AND ds.s_control_num =1)
1133 OR drw.major_unit_set_cd1 IS NULL)
1134 AND (drw.minor_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode2))
1135 AND EXISTS (SELECT 'X'
1136 FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
1137 WHERE eus.unit_set_status=euss.unit_set_status
1138 AND euss.s_unit_set_status = 'ACTIVE'
1139 AND eus.unit_set_cd = drw.minor_unit_set_cd1
1140 AND eus.unit_set_cat = ds.wif_minor_unit_set_cat
1141 AND ds.s_control_num =1)
1142 OR drw.minor_unit_set_cd1 IS NULL)
1143 AND (drw.track_unit_set_cd1 = RTRIM(LTRIM(l_academicsubprogramcode3))
1144 AND EXISTS (SELECT 'X'
1145 FROM igs_en_unit_set eus,igs_en_unit_set_stat euss, igs_da_setup ds
1146 WHERE eus.unit_set_status=euss.unit_set_status
1147 AND euss.s_unit_set_status = 'ACTIVE'
1148 AND eus.unit_set_cd = drw.track_unit_set_cd1
1149 AND eus.unit_set_cat = ds.wif_track_unit_set_cat
1150 AND ds.s_control_num =1)
1151 OR drw.track_unit_set_cd1 IS NULL));
1152
1153 l_found BOOLEAN := FALSE;
1154
1155 BEGIN
1156
1157 -- need to be changed for NLS
1158 -- need to add 'REPLY_ERROR' to IGS_LOOKUP_VALUES.
1159 -- Check if the <ErrorCode> element is present , if yes then update all the request student rows.
1160 IF p_error_code IS NOT NULL THEN
1161 l_error_code := 'REPLY_ERROR';
1162 END IF;
1163
1164 ecx_debug.push('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1165 x_return_status := 'S';
1166
1167 get_person_details ( RTRIM(LTRIM(p_person_id_code)), RTRIM(LTRIM(p_person_id_code_type)),l_person_id,l_person_number);
1168
1169 FOR v_dummy IN c_prog_comp LOOP
1170 l_prog_comparison_type := v_dummy.program_comparison_type;
1171 END LOOP;
1172
1173 FOR v_dummy IN c_prog_def LOOP
1174 l_progmajor_ind := v_dummy.program_definition_ind;
1175 END LOOP;
1176 -- Bug 4961469 Added the IF condition to check if the values are not null then do the string operation.
1177 pos1 := instr( RTRIM(LTRIM(p_academicsubprogram_codes)),',',1,1);
1178 IF (pos1 <> NULL) THEN
1179 l_academicsubprogramcode1 := substr( RTRIM(LTRIM(p_academicsubprogram_codes)),1,pos1-1);
1180 END IF;
1181 pos2 := instr(p_academicsubprogram_codes,',',1,2);
1182 IF (pos2 <> NULL) THEN
1183 l_academicsubprogramcode2 := substr( RTRIM(LTRIM(p_academicsubprogram_codes)),pos1+1,pos2-pos1-1);
1184 END IF;
1185 pos3 := instr(p_academicsubprogram_codes,',',1,3);
1186 IF (pos3 <> NULL) THEN
1187 l_academicsubprogramcode3 := substr( RTRIM(LTRIM(p_academicsubprogram_codes)),pos2+1,pos3-pos2-1);
1188 END IF;
1189
1190 IF (l_progmajor_ind = 'Y' ) THEN
1191 IF ((l_prog_comparison_type ='DP') OR (l_prog_comparison_type ='SP')) THEN
1192 FOR v_dummy IN c1 LOOP
1193 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
1194 (v_dummy.ROWID,
1195 v_dummy.batch_id,
1196 v_dummy.igs_da_req_stdnts_id,
1197 v_dummy.person_id,
1198 v_dummy.program_code,
1199 v_dummy.wif_program_code,
1200 v_dummy.special_program_code,
1201 v_dummy.major_unit_set_cd,
1202 v_dummy.program_major_code,
1203 p_report_text,
1204 v_dummy.wif_id,
1205 'R',
1206 l_error_code);
1207 l_found := TRUE;
1208 END LOOP;
1209 ELSIF (l_prog_comparison_type ='WIF') THEN
1210 FOR v_dummy IN c2 LOOP
1211 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
1212 (v_dummy.ROWID,
1213 v_dummy.batch_id,
1214 v_dummy.igs_da_req_stdnts_id,
1215 v_dummy.person_id,
1216 v_dummy.program_code,
1217 v_dummy.wif_program_code,
1218 v_dummy.special_program_code,
1219 v_dummy.major_unit_set_cd,
1220 v_dummy.program_major_code,
1221 p_report_text,
1222 v_dummy.wif_id,
1223 'R',
1224 l_error_code);
1225 l_found := TRUE;
1226 END LOOP;
1227 END IF;
1228 ELSE
1229 IF (l_prog_comparison_type ='DP') THEN
1230 FOR v_dummy IN c3 LOOP
1231 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
1232 (v_dummy.ROWID,
1233 v_dummy.batch_id,
1234 v_dummy.igs_da_req_stdnts_id,
1235 v_dummy.person_id,
1236 v_dummy.program_code,
1237 v_dummy.wif_program_code,
1238 v_dummy.special_program_code,
1239 v_dummy.major_unit_set_cd,
1240 v_dummy.program_major_code,
1241 p_report_text,
1242 v_dummy.wif_id,
1243 'R',
1244 l_error_code);
1245 l_found := TRUE;
1246 END LOOP;
1247 ELSIF (l_prog_comparison_type ='SP') THEN
1248 FOR v_dummy IN c4 LOOP
1249 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
1250 (v_dummy.ROWID,
1251 v_dummy.batch_id,
1252 v_dummy.igs_da_req_stdnts_id,
1253 v_dummy.person_id,
1254 v_dummy.program_code,
1255 v_dummy.wif_program_code,
1256 v_dummy.special_program_code,
1257 v_dummy.major_unit_set_cd,
1258 v_dummy.program_major_code,
1259 p_report_text,
1260 v_dummy.wif_id,
1261 'R',
1262 l_error_code);
1263 l_found := TRUE;
1264 END LOOP;
1265 ELSIF (l_prog_comparison_type ='WIF') THEN
1266 FOR v_dummy IN c5 LOOP
1267 IGS_DA_REQ_STDNTS_PKG.UPDATE_ROW
1268 (v_dummy.ROWID,
1269 v_dummy.batch_id,
1270 v_dummy.igs_da_req_stdnts_id,
1271 v_dummy.person_id,
1272 v_dummy.program_code,
1273 v_dummy.wif_program_code,
1274 v_dummy.special_program_code,
1275 v_dummy.major_unit_set_cd,
1276 v_dummy.program_major_code,
1277 p_report_text,
1278 v_dummy.wif_id,
1279 'R',
1280 l_error_code);
1281 l_found := TRUE;
1282 END LOOP;
1283 END IF;
1284 END IF;
1285
1286 --Update the request status bug #3438386
1287 IF (l_found )THEN
1288 update_request_status(p_batch_id);
1289 END IF;
1290
1291 IF NOT (l_found) THEN
1292 ecx_debug.pl (0, 'IGS', 'IGS_PROCEDURE_EXECUTION', 'PROCEDURE_NAME',g_pkg_name);
1293 ecx_debug.pl (0, 'IGS', 'IGS_REPORT_UPDATE_FAILED','E: Unable to Update');
1294 x_return_status := 'E: Unable to Update';
1295 END IF;
1296
1297 ecx_debug.pop('IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1298 -- Check if the update is successfull , else return status error.
1299 EXCEPTION
1300 WHEN NO_DATA_FOUND THEN
1301 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1302 ecx_debug.pl(0,'IGS','IGS_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1303 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1304 x_return_status := 'E: UPDATE FAILED';
1305 WHEN OTHERS THEN
1306 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1307 ecx_debug.pl(0,'IGS','IGS_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1308 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.UPDATE_REQ_STUDENTS');
1309 x_return_status := 'E: Other Exception';
1310
1311 END;
1312
1313 PROCEDURE insert_gpa
1314 (
1315 p_batch_id IN igs_pr_stu_acad_stat_int.batch_id%TYPE,
1316 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
1317 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
1318 p_program_code IN igs_da_req_stdnts.program_code%TYPE,
1319 p_alternate_code IN igs_pr_stu_acad_stat_int.alternate_code%TYPE,
1320 p_stat_type IN igs_pr_stu_acad_stat_int.stat_type%TYPE,
1321 p_timeframe IN igs_pr_stu_acad_stat_int.timeframe%TYPE,
1322 p_attempted_credit_points IN igs_pr_stu_acad_stat_int.attempted_credit_points%TYPE,
1323 p_earned_credit_points IN igs_pr_stu_acad_stat_int.earned_credit_points%TYPE,
1324 p_gpa IN igs_pr_stu_acad_stat_int.gpa%TYPE,
1325 p_gpa_credit_points IN igs_pr_stu_acad_stat_int.gpa_credit_points%TYPE,
1326 p_gpa_quality_points IN igs_pr_stu_acad_stat_int.gpa_quality_points%TYPE,
1327 x_return_status OUT NOCOPY VARCHAR2
1328 ) IS
1329
1330 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GPA';
1331 l_person_id hz_parties.party_id%TYPE;
1332 l_person_number hz_parties.party_number%TYPE;
1333 l_program_code igs_pr_stu_acad_stat_int.course_cd%TYPE;
1334
1335 CURSOR c1 IS
1336 SELECT drs.program_code
1337 FROM igs_da_req_stdnts drs
1338 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1339 AND drs.person_id = l_person_id
1340 AND ((drs.program_major_code = RTRIM(LTRIM(p_program_code))) OR (drs.program_code = RTRIM(LTRIM(p_program_code))));
1341
1342 BEGIN
1343 ecx_debug.push('IGS_DA_XML_PKG.INSERT_GPA');
1344 x_return_status := 'S';
1345
1346 get_person_details ( RTRIM(LTRIM(p_person_id_code)), RTRIM(LTRIM(p_person_id_code_type)),l_person_id,l_person_number);
1347
1348 FOR v_dummy IN c1 LOOP
1349 l_program_code := v_dummy.program_code;
1350 END LOOP;
1351
1352 INSERT INTO igs_pr_stu_acad_stat_int
1353 (BATCH_ID,
1354 COURSE_CD,
1355 PERSON_NUMBER,
1356 ALTERNATE_CODE,
1357 STAT_TYPE,
1358 TIMEFRAME,
1359 SOURCE_TYPE,
1360 SOURCE_REFERENCE,
1361 ATTEMPTED_CREDIT_POINTS,
1362 EARNED_CREDIT_POINTS,
1363 GPA,
1364 GPA_CREDIT_POINTS,
1365 GPA_QUALITY_POINTS,
1366 ERROR_CODE,
1367 CREATED_BY,
1368 CREATION_DATE,
1369 LAST_UPDATED_BY,
1370 LAST_UPDATE_DATE,
1371 LAST_UPDATE_LOGIN
1372 )
1373 VALUES
1374 (
1375 RTRIM(LTRIM(p_batch_id)),
1376 l_program_code,
1377 l_person_number,
1378 RTRIM(LTRIM(p_alternate_code)),
1379 RTRIM(LTRIM(p_stat_type)),
1380 RTRIM(LTRIM(p_timeframe)),
1381 'DEGREE AUDIT',
1382 'DEGREE AUDIT',
1383 RTRIM(LTRIM(p_attempted_credit_points)),
1384 RTRIM(LTRIM(p_earned_credit_points)),
1385 RTRIM(LTRIM(p_gpa)),
1386 RTRIM(LTRIM(p_gpa_credit_points)),
1387 RTRIM(LTRIM(p_gpa_quality_points)),
1388 NULL,
1389 1,
1390 SYSDATE,
1391 1,
1392 SYSDATE,
1393 NULL);
1394
1395 ecx_debug.pop('IGS_DA_XML_PKG.INSERT_GPA');
1396 EXCEPTION
1397 WHEN OTHERS THEN
1398 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.INSERT_GPA');
1399 ecx_debug.pl(0,'IGS','IGS_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1400 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.INSERT_GPA');
1401 x_return_status := 'E: Other Exception';
1402 END;
1403
1404 PROCEDURE insert_program_completion
1405 (
1406 p_batch_id IN igs_pr_spa_complete_int.batch_id%TYPE,
1407 p_person_id_code IN igs_pe_alt_pers_id.api_person_id%TYPE,
1408 p_person_id_code_type IN igs_pe_alt_pers_id.person_id_type%TYPE,
1409 p_program_code IN igs_da_req_stdnts.program_code%TYPE,
1410 p_program_complete IN VARCHAR2,
1411 p_program_complete_date IN VARCHAR2,
1412 x_return_status OUT NOCOPY VARCHAR2
1413 ) IS
1414
1415 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_PROGRAM_COMPLETION';
1416 l_person_id hz_parties.party_id%TYPE;
1417 l_person_number hz_parties.party_number%TYPE;
1418 l_program_code igs_pr_spa_complete_int.course_cd%TYPE;
1419
1420 CURSOR c1 IS
1421 SELECT drs.program_code
1422 FROM igs_da_req_stdnts drs
1423 WHERE drs.batch_id = RTRIM(LTRIM(p_batch_id))
1424 AND drs.person_id = l_person_id
1425 AND ((drs.program_major_code = RTRIM(LTRIM(p_program_code))) OR (drs.program_code = RTRIM(LTRIM(p_program_code))));
1426
1427 BEGIN
1428 ecx_debug.push('IGS_DA_XML_PKG.INSERT_DEGREE_COMPLETION');
1429 x_return_status := 'S';
1430
1431 get_person_details ( RTRIM(LTRIM(p_person_id_code)), RTRIM(LTRIM(p_person_id_code_type)),l_person_id,l_person_number);
1432
1433 FOR v_dummy IN c1 LOOP
1434 l_program_code := v_dummy.program_code;
1435 END LOOP;
1436
1437 IF ( RTRIM(LTRIM(p_program_complete)) ='true') THEN
1438 INSERT INTO igs_pr_spa_complete_int
1439 (BATCH_ID,
1440 PERSON_NUMBER,
1441 COURSE_CD,
1442 COMPLETE_DT,
1443 ERROR_CODE,
1444 CREATED_BY,
1445 CREATION_DATE,
1446 LAST_UPDATED_BY,
1447 LAST_UPDATE_DATE,
1448 LAST_UPDATE_LOGIN
1449 )
1450 VALUES
1451 ( RTRIM(LTRIM(p_batch_id)),
1452 l_person_number,
1453 l_program_code,
1454 -- Modified by nmankodi, Bug # 4613611
1455 NVL(to_date(substr(RTRIM(LTRIM(p_program_complete_date)),1,10),'YYYY-MM-DD'),SYSDATE),
1456 NULL,
1457 1,
1458 SYSDATE,
1459 1,
1460 SYSDATE,
1461 NULL);
1462 END IF;
1463
1464 ecx_debug.pop('IGS_DA_XML_PKG.INSERT_DEGREE_COMPLETION');
1465
1466 EXCEPTION
1467 WHEN OTHERS THEN
1468 ecx_debug.pl(0,'IGS','IGS_PROGRAM_ERROR','PROGRESS_LEVEL','IGS_DA_XML_PKG.INSERT_PROGRAM_COMPLETION');
1469 ecx_debug.pl(0,'IGS','IGS_ERROR_MESSAGE','ERROR_MESSAGE',SQLERRM);
1470 ecx_debug.setErrorInfo(2,30,SQLERRM||' -IGS_DA_XML_PKG.INSERT_PROGRAM_COMPLETION');
1471 x_return_status := 'E: Other Exception';
1472 END;
1473
1474 /*****************************************************************/
1475 PROCEDURE Submit_Event (
1476 p_batch_id IN IGS_DA_REQ_STDNTS.BATCH_ID%TYPE
1477 )
1478 IS
1479
1480 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1481 l_event_name VARCHAR2(255);
1482 l_event_key VARCHAR2(255);
1483 l_party_id HZ_PARTY_SITES.PARTY_ID%TYPE;
1484 l_party_site_id HZ_PARTY_SITES.PARTY_SITE_ID%TYPE;
1485 l_trans_type VARCHAR2(30) :='DA';
1486 l_trans_subtype VARCHAR2(30) ;
1487 l_party_type VARCHAR2(30) :='C';
1488 l_debug_level NUMBER := 0;
1489 l_wif NUMBER := 0;
1490 l_count NUMBER := 0;
1491 l_doc_id IGS_DA_REQ_STDNTS.BATCH_ID%TYPE;
1492 l_batch_profile VARCHAR2(1) := 'N';
1493
1494 CURSOR c_student IS
1495 SELECT drs.person_id
1496 FROM igs_da_req_stdnts drs
1497 WHERE drs.batch_id = p_batch_id;
1498
1499
1500 CURSOR c_party_data IS
1501 SELECT party_id,
1502 party_site_id
1503 FROM ecx_tp_headers
1504 WHERE tp_header_id IN
1505 ( SELECT tp_header_id
1506 FROM ecx_tp_details
1507 WHERE ext_process_id IN
1508 ( SELECT ext_process_id
1509 FROM ecx_ext_processes
1510 WHERE direction = 'OUT'
1511 AND transaction_id IN
1512 (SELECT et.transaction_id
1513 FROM ecx_transactions et, ecx_ext_processes ep
1514 WHERE transaction_type='DA'
1515 and ep.transaction_id = et.transaction_id
1516 and ep.ext_type =
1517 (select dcry.request_type
1518 from igs_da_cnfg_req_typ dcry, igs_da_rqst dr
1519 where dr.batch_id = p_batch_id
1520 and dcry.request_type_id = dr.request_type_id )
1521 and ep.direction = 'OUT')
1522 )
1523 );
1524
1525 CURSOR c_transaction_data IS
1526 SELECT et.transaction_subtype
1527 FROM ecx_transactions et, ecx_ext_processes ep
1528 WHERE transaction_type='DA'
1529 and ep.transaction_id = et.transaction_id
1530 and ep.direction = 'OUT'
1531 and ep.ext_type = (select dcry.request_type
1532 from igs_da_cnfg_req_typ dcry, igs_da_rqst dr
1533 where dr.batch_id = p_batch_id
1534 and dcry.request_type_id = dr.request_type_id );
1535
1536 /******************************************************************************/
1537 /***** wif -- What-If -- */
1538 /*Degree Audit process needs to be able to distinguish between Standard Degree*/
1539 /*Audits and What-If Degree Audits - Request Types. */
1540 /* For added processing by the Trading Partners */
1541 /******************************************************************************/
1542
1543 CURSOR c_wif IS
1544 SELECT count(drs.wif_program_code)
1545 FROM igs_da_req_stdnts drs
1546 WHERE drs.batch_id = p_batch_id
1547 AND wif_program_code is not null;
1548
1549 CURSOR c_transaction_wif_data IS
1550 SELECT et.transaction_subtype
1551 FROM ecx_transactions et, ecx_ext_processes ep
1552 WHERE transaction_type='DA'
1553 and ep.transaction_id = et.transaction_id
1554 and ep.direction = 'OUT'
1555 and ep.ext_type = 'WF';
1556 /* Cursor to determine if single large XML is to be generated or multiple small xml's */
1557
1558 CURSOR c_ftr_val IS
1559 SELECT feature_value
1560 FROM igs_da_req_ftrs
1561 WHERE batch_id = p_batch_id AND feature_code = 'SNG' ;
1562
1563 /******************************************************************************/
1564
1565
1566 BEGIN
1567
1568 l_event_name := 'oracle.apps.igs.da.xml.reqsubm';
1569
1570 OPEN c_transaction_data;
1571 FETCH c_transaction_data INTO l_trans_subtype;
1572 CLOSE c_transaction_data;
1573
1574 OPEN c_party_data;
1575 FETCH c_party_data INTO l_party_id, l_party_site_id;
1576 CLOSE c_party_data;
1577
1578 /******************************************************************************/
1579 /***** wif
1580 /******************************************************************************/
1581 OPEN c_wif;
1582 FETCH c_wif INTO l_wif;
1583 CLOSE c_wif;
1584
1585 IF l_wif <> 0 THEN
1586 OPEN c_transaction_wif_data;
1587 FETCH c_transaction_wif_data INTO l_trans_subtype;
1588 CLOSE c_transaction_wif_data;
1589 END IF;
1590 /******************************************************************************/
1591
1592 IF l_party_id IS NULL THEN
1593 FND_MESSAGE.SET_NAME('IGS', 'IGS_SV_PRTNR_STP_ERR'); -- No trading partner setup found
1594 FND_MSG_PUB.Add;
1595 RAISE FND_API.G_EXC_ERROR;
1596 END IF;
1597
1598
1599 OPEN c_ftr_val ;
1600 FETCH c_ftr_val INTO l_batch_profile ;
1601 IF c_ftr_val%NOTFOUND THEN
1602 l_batch_profile := 'N';
1603 END IF;
1604 CLOSE c_ftr_val;
1605
1606 -- if single large xml document needs to be generated...
1607 IF l_batch_profile = 'Y' THEN
1608
1609 -- Use person_id and batch_id for event key.
1610 l_event_key := p_batch_id || l_count+1;
1611 -- Create a new document ID for each student XML document created.
1612 l_doc_id := p_batch_id || l_count ;
1613
1614 wf_event.AddParameterToList(p_name=>'ECX_DOCUMENT_ID',p_value=>l_doc_id
1615 ,p_parameterlist=>l_parameter_list);
1616 wf_event.AddParameterToList(p_name=>'PARAMETER5',p_value=>p_batch_id
1617 ,p_parameterlist=>l_parameter_list);
1618 wf_event.AddParameterToList(p_name=>'PARAMETER4',p_value=>NULL
1619 ,p_parameterlist=>l_parameter_list);
1620 wf_event.AddParameterToList(p_name=>'ECX_PARTY_ID',p_value=>l_party_id
1621 ,p_parameterlist=>l_parameter_list);
1622 wf_event.AddParameterToList(p_name=>'ECX_PARTY_SITE_ID',p_value=>l_party_site_id
1623 ,p_parameterlist=>l_parameter_list);
1624 wf_event.AddParameterToList(p_name=>'ECX_TRANSACTION_TYPE',p_value=>l_trans_type
1625 ,p_parameterlist=>l_parameter_list);
1626 wf_event.AddParameterToList(p_name=>'ECX_TRANSACTION_SUBTYPE',p_value=>l_trans_subtype
1627 ,p_parameterlist=>l_parameter_list);
1628 wf_event.AddParameterToList(p_name=>'ECX_PARTY_TYPE',p_value=>l_party_type
1629 ,p_parameterlist=>l_parameter_list);
1630 wf_event.AddParameterToList(p_name=>'ECX_DEBUG_LEVEL',p_value=>l_debug_level
1631 ,p_parameterlist=>l_parameter_list);
1632 -- Raise the Event without the message
1633 -- The Generate Function Callback will create the XML Document
1634 -- Also possible that an API might be called from here to
1635 -- to generate the XML document
1636 wf_event.raise( p_event_name => l_event_name,
1637 p_event_key => l_event_key,
1638 p_parameters => l_parameter_list);
1639
1640 l_parameter_list.DELETE;
1641
1642
1643 ELSE
1644 --Loop through all the students in the batch request.
1645 --And generate a workflow request for each student.
1646 FOR v_dummy IN c_student LOOP
1647
1648 -- Use person_id and batch_id for event key.
1649 l_event_key := v_dummy.person_id || p_batch_id ;
1650 -- Create a new document ID for each student XML document created.
1651 l_doc_id := p_batch_id || l_count ;
1652
1653 wf_event.AddParameterToList(p_name=>'ECX_DOCUMENT_ID',p_value=>l_doc_id
1654 ,p_parameterlist=>l_parameter_list);
1655 wf_event.AddParameterToList(p_name=>'PARAMETER5',p_value=>p_batch_id
1656 ,p_parameterlist=>l_parameter_list);
1657 wf_event.AddParameterToList(p_name=>'PARAMETER4',p_value=>v_dummy.person_id
1658 ,p_parameterlist=>l_parameter_list);
1659 wf_event.AddParameterToList(p_name=>'ECX_PARTY_ID',p_value=>l_party_id
1660 ,p_parameterlist=>l_parameter_list);
1661 wf_event.AddParameterToList(p_name=>'ECX_PARTY_SITE_ID',p_value=>l_party_site_id
1662 ,p_parameterlist=>l_parameter_list);
1663 wf_event.AddParameterToList(p_name=>'ECX_TRANSACTION_TYPE',p_value=>l_trans_type
1664 ,p_parameterlist=>l_parameter_list);
1665 wf_event.AddParameterToList(p_name=>'ECX_TRANSACTION_SUBTYPE',p_value=>l_trans_subtype
1666 ,p_parameterlist=>l_parameter_list);
1667 wf_event.AddParameterToList(p_name=>'ECX_PARTY_TYPE',p_value=>l_party_type
1668 ,p_parameterlist=>l_parameter_list);
1669 wf_event.AddParameterToList(p_name=>'ECX_DEBUG_LEVEL',p_value=>l_debug_level
1670 ,p_parameterlist=>l_parameter_list);
1671 -- Raise the Event without the message
1672 -- The Generate Function Callback will create the XML Document
1673 -- Also possible that an API might be called from here to
1674 -- to generate the XML document
1675 wf_event.raise( p_event_name => l_event_name,
1676 p_event_key => l_event_key,
1677 p_parameters => l_parameter_list);
1678
1679 l_parameter_list.DELETE;
1680 l_count := l_count + 1;
1681
1682 END LOOP;
1683 END IF;
1684 END;
1685 /*****************************************************************************************/
1686
1687 PROCEDURE process_reply_failure
1688 (p_batch_id IN igs_da_req_stdnts.batch_id%TYPE
1689 ) IS
1690 l_da_wf_admin_id fnd_profile_options.profile_option_name%TYPE := FND_PROFILE.VALUE('IGS_DA_WF_ADMIN');
1691 CURSOR c_request_status IS
1692 SELECT 'X'
1693 FROM igs_da_rqst dr
1694 WHERE dr.batch_id = p_batch_id
1695 FOR UPDATE OF dr.request_status NOWAIT;
1696
1697 CURSOR c_requestor IS
1698 SELECT dr.requestor_id,fdu.user_name
1699 FROM igs_da_rqst dr, fnd_user fdu
1700 WHERE dr.batch_id = p_batch_id
1701 AND dr.requestor_id = fdu.person_party_id;
1702 CURSOR c_da_wf_admin IS
1703 SELECT fdu.person_party_id,fdu.user_name
1704 FROM fnd_user fdu
1705 WHERE fdu.person_party_id = l_da_wf_admin_id;
1706
1707
1708 l_da_wf_admin_name fnd_user.user_name%TYPE;
1709 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
1710 l_event_name VARCHAR2(255);
1711 l_event_key VARCHAR2(255);
1712 l_debug_level NUMBER := 3;
1713 l_requester fnd_user.user_name%TYPE;
1714 l_error_type VARCHAR2(2) := '25';
1715 l_error_code VARCHAR2(2) := '25';
1716 l_error_message VARCHAR2(20);
1717
1718 BEGIN
1719 SAVEPOINT PROCESS_REPLY_FAILURE;
1720 ecx_debug.push('IGS_DA_XML_PKG.PROCESS_REPLY_FAILURE');
1721 FOR v_dummy IN c_request_status LOOP
1722 UPDATE igs_da_rqst
1723 SET request_status = 'ERROR'
1724 WHERE CURRENT OF c_request_status;
1725 END LOOP;
1726
1727 IF (l_da_wf_admin_id IS NOT NULL) THEN
1728 FOR v_dummy IN c_requestor LOOP
1729 l_requester := v_dummy.user_name;
1730 END LOOP;
1731 FOR v_dummy IN c_da_wf_admin LOOP
1732 l_da_wf_admin_name := v_dummy.user_name;
1733 END LOOP;
1734
1735 l_event_name := 'oracle.apps.igs.da.xml.rcverr';
1736 l_event_key := p_batch_id;
1737 wf_event.AddParameterToList(p_name=>'TO_USERNAME',p_value=>l_da_wf_admin_name
1738 ,p_parameterlist=>l_parameter_list);
1739 wf_event.AddParameterToList(p_name=>'BATCH_ID',p_value=>p_batch_id
1740 ,p_parameterlist=>l_parameter_list);
1741 wf_event.AddParameterToList(p_name=>'REQ_USERNAME',p_value=>l_requester
1742 ,p_parameterlist=>l_parameter_list);
1743
1744 wf_event.raise( p_event_name => l_event_name,
1745 p_event_key => l_event_key,
1746 p_parameters => l_parameter_list);
1747
1748 l_parameter_list.DELETE;
1749
1750
1751 ELSE
1752 ECX_ACTIONS.SET_ERROR_EXIT_PROGRAM(l_error_type,l_error_code,l_error_message);
1753 END IF;
1754 ecx_debug.pop('IGS_DA_XML_PKG.PROCESS_REPLY_FAILURE');
1755 END;
1756
1757 PROCEDURE update_request_status
1758 (p_batch_id IN igs_da_req_stdnts.batch_id%TYPE
1759 ) IS
1760 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_REQUEST_STATUS' ;
1761 l_request_status VARCHAR2(30) := 'SUBMITTED';
1762 l_RETURN_STATUS VARCHAR2(10);
1763 l_err_count NUMBER(10):= 0;
1764 l_noClob_count NUMBER(10):= 0;
1765 l_clob_count NUMBER(10):= 0;
1766 l_reply_err_count NUMBER(10):= 0;
1767 l_request_err_count NUMBER(10):= 0;
1768
1769 CURSOR c_request_status IS
1770 SELECT ROWID,dr.*
1771 FROM igs_da_rqst dr
1772 WHERE dr.batch_id = p_batch_id;
1773
1774 BEGIN
1775
1776 SAVEPOINT UPDATE_REQUEST_STATUS;
1777
1778 -- Get the current error count of students that failed in the pre-processing and could not make it in the Request XML Document
1779 -- of the selected batch_id
1780 select count(*)
1781 into l_request_err_count
1782 from igs_da_req_stdnts
1783 where batch_id = p_batch_id
1784 and error_code ='PRE- SUBMISSION FAILURE';
1785
1786 -- Get the current error count of students that failed with the a <Error Code> element in the ReplyXML
1787 -- of the selected batch_id
1788 -- need to be changed for NLS
1789 -- need to add 'REPLY_ERROR' to IGS_LOOKUP_VALUES.
1790 select count(*)
1791 into l_reply_err_count
1792 from igs_da_req_stdnts
1793 where batch_id = p_batch_id
1794 and error_code ='REPLY_ERROR';
1795
1796 -- Get the current count of students not containing reports
1797 -- in the selected batch_id
1798 select count(*)
1799 into l_noClob_count
1800 from igs_da_req_stdnts
1801 where batch_id = p_batch_id
1802 and report_text is null;
1803
1804 -- Get the current count of students containing reports
1805 -- in the selected batch_id
1806 select count(*)
1807 into l_clob_count
1808 from igs_da_req_stdnts
1809 where batch_id = p_batch_id
1810 and report_text is not null;
1811
1812 --Set the current Status of the batch requst.
1813 l_err_count := l_request_err_count + l_reply_err_count;
1814
1815
1816 IF l_err_count = 0 THEN
1817 IF (l_noClob_count = 0) THEN l_request_status := 'COMPLETED'; END IF;
1818 IF(l_noClob_count > 0 AND l_Clob_count > 0) THEN l_request_status := 'COMPLETE_ERROR' ; END IF;
1819 IF (l_Clob_count = 0) THEN l_request_status := 'SUBMITTED'; END IF;
1820 ELSE
1821 IF ( l_reply_err_count >0 ) THEN l_request_status := 'COMPLETE_ERROR'; END IF;
1822 IF (l_request_err_count > 0 AND l_Clob_count > l_request_err_count) THEN l_request_status := 'COMPLETE_ERROR'; END IF;
1823 IF (l_request_err_count > 0 AND l_Clob_count = l_request_err_count) THEN l_request_status := 'SUBMIT_ERROR'; END IF;
1824 END IF;
1825
1826 UPDATE igs_da_rqst
1827 SET REQUEST_STATUS = l_request_status
1828 WHERE batch_id = p_batch_id;
1829
1830
1831 EXCEPTION
1832 WHEN FND_API.G_EXC_ERROR THEN
1833 --If execution error, rollback all database changes, generate message text
1834 --and return failure status to the WF
1835 ROLLBACK TO UPDATE_REQUEST_STATUS;
1836
1837 return;
1838
1839 WHEN OTHERS THEN
1840 RAISE ;
1841
1842 END;
1843
1844 PROCEDURE launch_notify_err_wf (p_batch_id igs_da_req_stdnts.batch_id%TYPE) IS
1845 l_item_key VARCHAR2(100);
1846 -- cursor to find the requestor
1847 CURSOR c_req (cp_batch_id igs_da_rqst.batch_id%TYPE) IS
1848 SELECT user_name
1849 FROM fnd_user
1850 WHERE person_party_id IN (SELECT requestor_id
1851 FROM igs_da_rqst
1852 WHERE batch_id = cp_batch_id);
1853 l_req VARCHAR2(2000);
1854 BEGIN
1855 ECX_DEBUG.PUSH('IGS_DA_XML_PKG.LAUNCH_NOTIFY_ERR_WF');
1856 l_item_key := 'DANTFERR'||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISSSSS');
1857 OPEN c_req(p_batch_id);
1858 FETCH c_req INTO l_req;
1859 CLOSE c_req;
1860 IF l_req IS NULL THEN
1861 l_req := 'SYSADMIN';
1862 END IF;
1863 WF_ENGINE.CreateProcess (ItemType => 'DANTFERR',
1864 Itemkey => l_item_key,
1865 process => 'NTFREQST');
1866 WF_ENGINE.SetItemAttrText(ItemType => 'DANTFERR',
1867 ItemKey => l_item_key,
1868 aname => 'BATCH_ID',
1869 avalue => to_char(p_batch_id));
1870 WF_ENGINE.SetItemAttrText(ItemType => 'DANTFERR',
1871 ItemKey => l_item_key,
1872 aname => 'REQUESTOR',
1873 avalue => l_req);
1874
1875 WF_ENGINE.StartProcess (ItemType => 'DANTFERR',
1876 ItemKey => l_item_key);
1877 ECX_DEBUG.POP('IGS_DA_XML_PKG.LAUNCH_NOTIFY_ERR_WF');
1878 EXCEPTION
1879 WHEN OTHERS THEN
1880 WF_CORE.Context('IGS_DA_XML_PKG','launch_notify_err_wf',l_item_key);
1881 RAISE;
1882 END launch_notify_err_wf;
1883
1884 PROCEDURE pre_submit_event (p_batch_id IN igs_da_req_stdnts.batch_id%TYPE) IS
1885 /*
1886 This procedure filters out the erroroneous record from the batch and show
1887 the error message through the html report. It can happen that in a batch id,
1888 which is having more than 1 students and 1 student's record fails to meet the
1889 mandatory criteria. Also if it happens that the complete batch id is failing,
1890 no call to submit_event, which generates the XML Message will be made.
1891 */
1892
1893 -- cursor to fetch the list of person_id in the given batch_id
1894 CURSOR c_list_person_id (cp_batch_id igs_da_req_stdnts.batch_id%TYPE,
1895 cp_igs_da_req_stdnts_id igs_da_req_stdnts.igs_da_req_stdnts_id%TYPE) IS
1896 SELECT person_id, igs_da_req_stdnts_id
1897 FROM igs_da_req_stdnts
1898 WHERE batch_id = cp_batch_id
1899 AND igs_da_req_stdnts_id=cp_igs_da_req_stdnts_id;
1900
1901 -- curosr to count the person_id in the batch_id
1902 CURSOR c_count_person_id (cp_batch_id igs_da_req_stdnts.batch_id%TYPE) IS
1903 SELECT count(*)
1904 FROM igs_da_req_stdnts
1905 WHERE batch_id = cp_batch_id;
1906
1907 l_count_person_id NUMBER;
1908
1909 -- cursor to validate the record from the view IGS_DA_XML_PERSON_V
1910 CURSOR c_person_v (cp_batch_id igs_da_req_stdnts.batch_id%TYPE,
1911 cp_person_id igs_da_req_stdnts.person_id%TYPE) IS
1912 SELECT person_code_qualifier,
1913 person_id_code,
1914 name_type,
1915 name_first,
1916 name_last
1917 FROM igs_da_xml_person_v
1918 WHERE batch_id = cp_batch_id
1919 AND person_id = cp_person_id;
1920
1921 -- cursor to validate the record from the view IGS_DA_XML_DEGREEPROGRAM_V
1922 /* removing this chech as igs_da_xml_degreeprogram_v has three cols only and
1923 all of them are not null
1924
1925 CURSOR c_degprg_v (cp_batch_id igs_da_req_stdnts.batch_id%TYPE,
1926 cp_person_id igs_da_req_stdnts.person_id%TYPE) IS
1927 SELECT institution_cd
1928 FROM igs_da_xml_degreeprogram_v
1929 WHERE batch_id = cp_batch_id
1930 AND person_id = cp_person_id;
1931 */
1932 -- cursor to validate the record from the view IGS_DA_XML_ACADEMICPROGRAM_V
1933 CURSOR c_acadprg_v (cp_batch_id igs_da_req_stdnts.batch_id%TYPE,
1934 cp_person_id igs_da_req_stdnts.person_id%TYPE) IS
1935 SELECT program_type,
1936 program_code,
1937 program_catalog_year
1938 FROM igs_da_xml_academicprogram_v
1939 WHERE batch_id = cp_batch_id
1940 AND person_id = cp_person_id;
1941
1942 -- cursor to update igs_da_req_stdnts
1943 CURSOR c_req_stdnts (cp_batch_id igs_da_req_stdnts.batch_id%TYPE,
1944 cp_person_id igs_da_req_stdnts.person_id%TYPE,
1945 cp_igs_da_req_stdnts_id igs_da_req_stdnts.igs_da_req_stdnts_id%TYPE) IS
1946 SELECT rowid, a.*
1947 FROM igs_da_req_stdnts a
1948 WHERE batch_id = cp_batch_id
1949 AND person_id = cp_person_id
1950 AND igs_da_req_stdnts_id = cp_igs_da_req_stdnts_id;
1951
1952 -- cursor to run for each req_stdnts_id
1953 CURSOR c_req_stdnts_id (cp_batch_id igs_da_req_stdnts.batch_id%TYPE) IS
1954 SELECT igs_da_req_stdnts_id
1955 FROM igs_da_req_stdnts
1956 WHERE batch_id = cp_batch_id;
1957 ctr_tbl NUMBER;
1958 pid_exists VARCHAR2(1);
1959 v_report_text VARCHAR2(4000);
1960 l_pcode_qual_exists VARCHAR2(1);
1961 l_pid_code_exists VARCHAR2(1);
1962 l_name_type_exists VARCHAR2(1);
1963 l_name_first_exists VARCHAR2(1);
1964 l_name_last_exists VARCHAR2(1);
1965 l_prog_type_exists VARCHAR2(1);
1966 l_prog_code_exists VARCHAR2(1);
1967 l_prog_cat_exists VARCHAR2(1);
1968 -- need to be changed for NLS
1969 -- needt to add 'PRE-SUBMISSION FAILURE' to IGS_LOOKUP_VALUES.
1970 l_error_code VARCHAR2(30) := 'PRE- SUBMISSION FAILURE';
1971
1972 FUNCTION get_person (pp_person_id igs_da_req_stdnts.person_id%TYPE) RETURN VARCHAR2 IS
1973 CURSOR c_get_pers (cp_person_id igs_da_req_stdnts.person_id%TYPE) IS
1974 SELECT party_name||' ('||party_number||') '
1975 FROM hz_parties
1976 WHERE party_id = cp_person_id;
1977 v_get_pers VARCHAR2(4000);
1978 BEGIN
1979 OPEN c_get_pers (pp_person_id);
1980 FETCH c_get_pers INTO v_get_pers;
1981 CLOSE c_get_pers;
1982 RETURN v_get_pers;
1983 END get_person;
1984
1985 BEGIN
1986 ECX_DEBUG.PUSH('IGS_DA_XML_PKG.PRE_SUBMIT_EVENT');
1987 -- initialize the variable
1988 ctr_tbl:=0;
1989 pid_exists:='N';
1990 l_pcode_qual_exists:='N';
1991 l_pid_code_exists :='N';
1992 l_name_type_exists :='N';
1993 l_name_first_exists:='N';
1994 l_name_last_exists :='N';
1995 l_prog_type_exists :='N';
1996 l_prog_code_exists :='N';
1997 l_prog_cat_exists :='N';
1998
1999 -- find the count of person in the given batch id
2000 OPEN c_count_person_id(p_batch_id);
2001 FETCH c_count_person_id INTO l_count_person_id;
2002 CLOSE c_count_person_id;
2003 -- loop through the list of person id in the given batch
2004 -- to identify the missing attribute
2005 FOR rec_req_stdnts_id IN c_req_stdnts_id(p_batch_id)
2006 LOOP
2007 FOR rec_list_person_id IN c_list_person_id(p_batch_id,rec_req_stdnts_id.igs_da_req_stdnts_id)
2008 LOOP
2009 -- check whether PersonIdCodeQualifier, PersonIdCode
2010 -- NameType, NameFirst, NameLast exist for the person id or not
2011 FOR rec_person_v IN c_person_v (p_batch_id,rec_list_person_id.person_id)
2012 LOOP
2013 IF rec_person_v.person_code_qualifier IS NULL OR
2014 rec_person_v.person_id_code IS NULL OR
2015 rec_person_v.name_type IS NULL OR
2016 rec_person_v.name_first IS NULL OR
2017 rec_person_v.name_last IS NULL THEN
2018 ctr_tbl := ctr_tbl+1;
2019 pid_exists := 'Y';
2020 v_report_text := v_report_text ||' '|| get_person(rec_list_person_id.person_id) || ' does not have: <BR> ';
2021 END IF;
2022 IF rec_person_v.person_code_qualifier IS NULL AND l_pcode_qual_exists = 'N' THEN
2023 l_pcode_qual_exists:='Y';
2024 v_report_text := v_report_text ||' '|| ' Person Code Qualifier. <BR>';
2025 END IF;
2026 IF rec_person_v.person_id_code IS NULL AND l_pid_code_exists = 'N' THEN
2027 l_pid_code_exists := 'Y';
2028 v_report_text := v_report_text ||' '|| ' Person Id Code. <BR>';
2029 END IF;
2030 IF rec_person_v.name_type IS NULL AND l_name_type_exists = 'N' THEN
2031 l_name_type_exists := 'Y';
2032 v_report_text := v_report_text ||' '|| ' Name Type. <BR>';
2033 END IF;
2034 IF rec_person_v.name_first IS NULL AND l_name_first_exists = 'N' THEN
2035 l_name_first_exists := 'Y';
2036 v_report_text := v_report_text ||' '|| ' Name First. <BR>';
2037 END IF;
2038 IF rec_person_v.name_last IS NULL AND l_name_last_exists = 'N' THEN
2039 l_name_last_exists:='Y';
2040 v_report_text := v_report_text ||' '|| ' Name Last. <BR>';
2041 END IF;
2042 END LOOP; --FOR rec_person_v IN c_person_v (p_batch_id,rec_list_person_id.person_id)
2043
2044 -- check that the student has local institution defined in IGS_DA_XML_DEGREEPROGRAM_V
2045 /* removing this chech as igs_da_xml_degreeprogram_v has three cols only and
2046 all of them are not null
2047 FOR rec_degprg_v IN c_degprg_v (p_batch_id,rec_list_person_id.person_id)
2048 LOOP
2049 IF rec_degprg_v.institution_cd IS NULL THEN
2050 IF pid_exists = 'N' THEN
2051 ctr_tbl := ctr_tbl+1;
2052 v_report_text := v_report_text ||' '|| get_person(rec_list_person_id.person_id) ||' '|| ' does not have: <BR> ';
2053 END IF;
2054 v_report_text := v_report_text ||' '|| ' Local Institution. <BR>';
2055 END IF;
2056 END LOOP; --FOR rec_degprg_v IN c_degprg_v (p_batch_id,rec_list_person_id.person_id)
2057 */
2058 -- check that the student has valid record in IGS_DA_XML_ACADEMICPROGRAM_V
2059 FOR rec_acadprg_v IN c_acadprg_v (p_batch_id,rec_list_person_id.person_id)
2060 LOOP
2061 IF rec_acadprg_v.program_type IS NULL OR
2062 rec_acadprg_v.program_code IS NULL OR
2063 rec_acadprg_v.program_catalog_year IS NULL THEN
2064 IF pid_exists = 'N' THEN
2065 ctr_tbl := ctr_tbl + 1;
2066 pid_exists := 'Y';
2067 v_report_text := v_report_text ||' '||get_person(rec_list_person_id.person_id) ||' does not have: <BR> ';
2068 END IF;
2069 END IF;
2070 IF rec_acadprg_v.program_type IS NULL AND l_prog_type_exists = 'N' THEN
2071 l_prog_type_exists := 'Y';
2072 v_report_text := v_report_text ||' '|| ' Program Type. <BR>';
2073 END IF;
2074 IF rec_acadprg_v.program_code IS NULL AND l_prog_code_exists = 'N' THEN
2075 l_prog_code_exists := 'Y';
2076 v_report_text := v_report_text ||' '|| ' Program Code. <BR>';
2077 END IF;
2078 IF rec_acadprg_v.program_catalog_year IS NULL AND l_prog_cat_exists = 'N' THEN
2079 l_prog_cat_exists := 'Y';
2080 v_report_text := v_report_text ||' '|| ' Program Catalog Year. <BR>';
2081 END IF;
2082 END LOOP; -- FOR rec_acadprg_v IN c_acadprg_v (p_batch_id,rec_list_person_id.person_id)
2083 -- now update the req_stdnts table
2084 IF v_report_text IS NOT NULL THEN
2085 v_report_text := ' <HTML> <BODY> Error Report <BR> <BR> '||v_report_text||' '|| ' </BODY> </HTML> ';
2086 END IF;
2087 IF v_report_text IS NOT NULL THEN
2088 UPDATE igs_da_req_stdnts
2089 SET report_text = v_report_text,
2090 -- bug fix 3438386 - update error code for internal request failues.
2091 error_code = l_error_code
2092 WHERE batch_id = p_batch_id
2093 AND person_id = rec_list_person_id.person_id
2094 AND igs_da_req_stdnts_id = rec_req_stdnts_id.igs_da_req_stdnts_id;
2095 END IF;
2096 /*
2097 igs_da_req_stdnts_pkg.update_row (
2098 X_ROWID => rec_req_stdnts.ROWID,
2099 X_BATCH_ID => rec_req_stdnts.batch_id,
2100 X_IGS_DA_REQ_STDNTS_ID => rec_req_stdnts.igs_da_req_stdnts_id,
2101 X_PERSON_ID => rec_req_stdnts.person_id,
2102 X_PROGRAM_CODE => rec_req_stdnts.program_code,
2103 X_WIF_PROGRAM_CODE => rec_req_stdnts.wif_program_code,
2104 X_SPECIAL_PROGRAM_CODE => rec_req_stdnts.special_program_code,
2105 X_MAJOR_UNIT_SET_CD => rec_req_stdnts.major_unit_set_cd,
2106 X_PROGRAM_MAJOR_CODE => rec_req_stdnts.program_major_code,
2107 X_REPORT_TEXT => v_report_text,
2108 X_WIF_ID => rec_req_stdnts.wif_id,
2109 X_MODE => 'R'
2110 );
2111 */
2112 v_report_text := NULL;
2113 pid_exists := 'N';
2114 l_pcode_qual_exists:='N';
2115 l_pid_code_exists :='N';
2116 l_name_type_exists :='N';
2117 l_name_first_exists:='N';
2118 l_name_last_exists :='N';
2119 l_prog_type_exists :='N';
2120 l_prog_code_exists :='N';
2121 l_prog_cat_exists :='N';
2122 END LOOP; -- FOR rec_list_person_id IN c_list_person_id(p_batch_id,rec_req_stdnts_id.igs_da_req_stdnts_id)
2123 END LOOP; -- FOR rec_req_stdnts_id IN c_req_stdnts_id(p_batch_id)
2124 /*
2125 now, check the count and directly call the
2126 submit_event else first launch the workflow to notify the error
2127 */
2128 ECX_DEBUG.PUSH('BEFORE CALL TO SUBMIT_EVENT');
2129 IF ctr_tbl = 0 THEN
2130 submit_event(p_batch_id);
2131 ELSE
2132 launch_notify_err_wf(p_batch_id);
2133 IF l_count_person_id <> ctr_tbl THEN
2134 submit_event(p_batch_id);
2135 END IF;
2136 END IF; -- IF v_person_tbl.COUNT = 0 THEN
2137 ECX_DEBUG.POP('BEFORE CALL TO SUBMIT_EVENT');
2138 ECX_DEBUG.POP('IGS_DA_XML_PKG.PRE_SUBMIT_EVENT');
2139 END pre_submit_event;
2140
2141 END IGS_DA_XML_PKG;