1 PACKAGE BODY IGS_HE_EXTRACT_PKG AS
2 /* $Header: IGSHE9AB.pls 120.8 2006/05/02 22:39:01 jtmathew ship $ */
3
4 -- Variables that will be accessed by any or all the procedures
5 -- Extract related records
6 g_he_ext_run_dtls igs_he_ext_run_dtls%ROWTYPE;
7 g_he_ext_run_except igs_he_ext_run_excp%ROWTYPE;
8 g_he_submsn_return igs_he_submsn_return%ROWTYPE;
9 g_he_submsn_header igs_he_submsn_header%ROWTYPE;
10
11 -- Student / Module related records.
12 g_en_stdnt_ps_att igs_en_stdnt_ps_att%ROWTYPE;
13 g_he_st_spa igs_he_st_spa%ROWTYPE;
14 g_as_su_setatmpt igs_as_su_setatmpt%ROWTYPE;
15 g_he_en_susa igs_he_en_susa%ROWTYPE;
16 g_he_st_prog igs_he_st_prog%ROWTYPE;
17 g_ps_ver igs_ps_ver%ROWTYPE;
18 g_he_poous igs_he_poous%ROWTYPE;
19 g_pe_person igs_pe_person%ROWTYPE;
20 g_he_ad_dtl igs_he_ad_dtl%ROWTYPE;
21
22 g_records_found BOOLEAN := FALSE;
23
24 g_prog_rec_flag BOOLEAN := FALSE;
25 g_prog_type_rec_flag BOOLEAN := FALSE;
26
27 g_awd_table igs_he_extract_fields_pkg.awd_table;
28
29 /*----------------------------------------------------------------------
30 This procedures writes onto the log file
31 ----------------------------------------------------------------------*/
32 PROCEDURE write_to_log(p_message IN VARCHAR2)
33 IS
34 BEGIN
35
36 Fnd_File.Put_Line(Fnd_File.Log, p_message);
37
38 END write_to_log;
39
40 /*----------------------------------------------------------------------
41 This procedure is called to insert errors into the exception run
42 table. The Exception Run Report is run after the Generate Extract
43 process completes which reads the data from this table and prints the
44 report
45 The processing should not stop if any error is encountered unless it
46 is fatal.
47
48 Parameters :
49 p_he_ext_run_exceptions IN Record which contains the values that
50 need to be inserted into the exception
51 table.
52 The field Exception_Reason should
53 contain the message text not the
54 message code.
55 ----------------------------------------------------------------------*/
56 PROCEDURE log_error
57 (p_he_ext_run_exceptions IN OUT NOCOPY igs_he_ext_run_excp%ROWTYPE)
58 IS
59 PRAGMA AUTONOMOUS_TRANSACTION;
60
61 l_rowid VARCHAR2(30) := NULL;
62
63 BEGIN
64
65 Igs_He_Ext_Run_Excp_Pkg.Insert_Row
66 (X_Rowid => l_rowid,
67 X_Ext_Exception_Id => p_he_ext_run_exceptions.ext_exception_id,
68 X_Extract_Run_Id => p_he_ext_run_exceptions.Extract_Run_Id,
69 X_Person_Id => p_he_ext_run_exceptions.Person_Id,
70 X_Person_Number => p_he_ext_run_exceptions.Person_Number,
71 X_Course_Cd => p_he_ext_run_exceptions.Course_Cd,
72 X_Crv_Version_Number => p_he_ext_run_exceptions.Crv_Version_Number,
73 X_Unit_Cd => p_he_ext_run_exceptions.Unit_Cd,
74 X_Uv_Version_Number => p_he_ext_run_exceptions.Uv_Version_Number,
75 X_Line_Number => p_he_ext_run_exceptions.Line_Number,
76 X_Field_Number => p_he_ext_run_exceptions.Field_Number,
77 X_Exception_Reason => p_he_ext_run_exceptions.Exception_Reason);
78
79 -- Commit this insert. Since its an autonomous transaction
80 -- it will not affect the main transaction.
81 COMMIT;
82
83 EXCEPTION
84 WHEN OTHERS
85 THEN
86 write_to_log(SQLERRM);
87 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
88 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.log_error');
89 IGS_GE_MSG_STACK.ADD;
90 App_Exception.Raise_Exception;
91
92 END log_error;
93
94 -- created by jtmathew
95 -- used to filter out students whose awards are not conferred
96 -- between the correct award conferral dates.
97 FUNCTION validate_award_conferral_dates (p_std_awd_cmp_ind IN igs_en_spa_awd_aim.complete_ind%TYPE,
98 p_std_awd_conf_dt IN igs_en_spa_awd_aim.conferral_date%TYPE,
99 p_awd_conf_start_dt OUT NOCOPY igs_he_submsn_awd.award_start_date%TYPE,
100 p_awd_conf_end_dt OUT NOCOPY igs_he_submsn_awd.award_end_date%TYPE)
101 RETURN BOOLEAN
102 IS
103
104 -- Only to be run if prog type award conferral dates exist for submission
105 -- i.e. g_prog_type_rec_flag is TRUE
106 CURSOR c_prog_type IS
107 SELECT course_type
108 FROM igs_ps_ver_all
109 WHERE course_cd = g_en_stdnt_ps_att.course_cd
110 AND version_number = g_en_stdnt_ps_att.version_number;
111
112 l_prog_type igs_ps_ver_all.course_type%TYPE;
113 l_awd_conf_start_dt igs_he_submsn_awd.award_start_date%TYPE;
114 l_awd_conf_end_dt igs_he_submsn_awd.award_end_date%TYPE;
115 l_valid BOOLEAN;
116
117 BEGIN
118
119 p_awd_conf_start_dt := g_he_submsn_header.enrolment_start_date;
120 p_awd_conf_end_dt := g_he_submsn_header.enrolment_end_date;
121 l_valid := FALSE;
122
123 IF ( g_en_stdnt_ps_att.commencement_dt <= g_he_submsn_header.enrolment_end_date
124 AND ( g_en_stdnt_ps_att.discontinued_dt IS NULL OR g_en_stdnt_ps_att.discontinued_dt >= g_he_submsn_header.enrolment_start_date )
125 AND (g_en_stdnt_ps_att.course_rqrmnts_complete_dt IS NULL OR g_en_stdnt_ps_att.course_rqrmnts_complete_dt >= g_he_submsn_header.enrolment_start_date)
126 ) THEN
127 l_valid := TRUE;
128 END IF;
129
130
131 IF NOT l_valid
132 THEN
133
134 -- If student has a conferral date
135 IF p_std_awd_cmp_ind = 'Y' AND p_std_awd_conf_dt IS NOT NULL THEN
136
137
138 IF g_prog_type_rec_flag = TRUE
139 THEN
140 -- If there are award conferral dates specified at the program type
141 -- level only, then check if any relate to this particular student program attempt
142 OPEN c_prog_type;
143 FETCH c_prog_type INTO l_prog_type;
144 CLOSE c_prog_type;
145
146 igs_he_extract_fields_pkg.get_awd_conferral_dates(g_awd_table,
147 g_he_ext_run_dtls.submission_name,
148 g_prog_rec_flag,
149 g_prog_type_rec_flag,
150 g_en_stdnt_ps_att.course_cd,
151 l_prog_type,
152 g_he_submsn_header.enrolment_start_date,
153 g_he_submsn_header.enrolment_end_date,
154 p_awd_conf_start_dt,
155 p_awd_conf_end_dt);
156
157 ELSE
158 -- If there are award conferral dates specified at the program level only,
159 -- then check if any relate to this particular student program attempt
160 igs_he_extract_fields_pkg.get_awd_conferral_dates(g_awd_table,
161 g_he_ext_run_dtls.submission_name,
162 g_prog_rec_flag,
163 g_prog_type_rec_flag,
164 g_en_stdnt_ps_att.course_cd,
165 NULL,
166 g_he_submsn_header.enrolment_start_date,
167 g_he_submsn_header.enrolment_end_date,
168 p_awd_conf_start_dt,
169 p_awd_conf_end_dt);
170 END IF;
171
172 IF p_std_awd_conf_dt BETWEEN p_awd_conf_start_dt AND p_awd_conf_end_dt THEN
173 l_valid := TRUE;
174 ELSE
175 l_valid := FALSE;
176 END IF;
177
178 END IF;
179 END IF;
180
181 RETURN l_valid;
182
183 EXCEPTION
184 WHEN OTHERS
185 THEN
186 write_to_log(SQLERRM);
187
188 -- Close Cursors
189 IF c_prog_type%ISOPEN
190 THEN
191 CLOSE c_prog_type;
192 END IF;
193
194 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
195 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_award_conferral_dates');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198
199
200 END validate_award_conferral_dates;
201
202 -- created by jbaber
203 -- used when recalculating
204 FUNCTION validate_recalc_params (p_extract_run_id IN NUMBER,
205 p_person_id IN igs_en_stdnt_ps_att.person_id%TYPE,
206 p_course_cd IN igs_en_stdnt_ps_att.course_cd%TYPE)
207 RETURN BOOLEAN
208 IS
209
210 CURSOR c_person(cp_person_id IN igs_en_stdnt_ps_att.person_id%TYPE) IS
211 SELECT 'X'
212 FROM igs_he_ext_run_prms
213 WHERE extract_run_id = p_extract_run_id
214 AND only = cp_person_id
215 AND param_type = 'RECALC-PERSON';
216
217 CURSOR c_program(cp_person_id IN igs_en_stdnt_ps_att.course_cd%TYPE) IS
218 SELECT 'X'
219 FROM igs_he_ext_run_prms
220 WHERE extract_run_id = p_extract_run_id
221 AND only = cp_person_id
222 AND param_type = 'RECALC-PROGRAM';
223
224 l_result VARCHAR2(1) := NULL;
225
226 BEGIN
227
228
229 -- Check if this person ID should be recalculated
230 OPEN c_person(p_person_id);
231 FETCH c_person INTO l_result;
232 CLOSE c_person;
233
234 -- If so then return true
235 IF l_result IS NOT NULL THEN
236 RETURN TRUE;
237 END IF;
238
239 -- return false if course_cd is NULL
240 -- possible for DLHE recalculation
241 IF p_course_cd IS NULL THEN
242 RETURN FALSE;
243 END IF;
244
245 -- Check if this course cd should be recalculated
246 OPEN c_program(p_course_cd);
247 FETCH c_program INTO l_result;
248 CLOSE c_program;
249
250 -- If so then return true
251 IF l_result IS NOT NULL THEN
252 RETURN TRUE;
253 END IF;
254
255
256 -- This SPA record doesn't meet the criteria of the recalculate form so exclude
257 RETURN FALSE;
258
259 EXCEPTION
260 WHEN OTHERS
261 THEN
262 write_to_log(SQLERRM);
263
264 -- Close Cursors
265 IF c_person%ISOPEN
266 THEN
267 CLOSE c_person;
268 END IF;
269
270 IF c_program%ISOPEN
271 THEN
272 CLOSE c_program;
273 END IF;
274
275
276 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
277 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_relalc_params');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception;
280
281 END validate_recalc_params;
282
283
284
285 --smaddali created split this procedure into 2 more procedures for bug 2350730
286 --smaddali 11-dec-03 Modified for bug#3235753 , to replace system date comparision with hesa submission period
287 --jbaber 19-Jan-06 Support for dynamic person ID groups for bug 3693367
288 FUNCTION validate_params (p_extract_run_id IN NUMBER)
289 RETURN BOOLEAN
290 IS
291 -- Changed the cursor to remove DECODE for bug,3179585
292 CURSOR c_prm IS
293 SELECT param_type,
294 exclude,
295 only
296 FROM igs_he_ext_run_prms
297 WHERE extract_run_id = p_extract_run_id
298 AND (exclude IS NOT NULL
299 OR only IS NOT NULL)
300 AND param_type IN ('PSN_IDENT_GROUP', 'PSN_ID')
301 ORDER BY param_type;
302
303 -- Changed the cursor to replace the multi org view with igs_pe_prsid_grp_mem_all for bug,3179585
304 -- smaddali modified this cursor to select group_cd instead of group_id for bug2391473
305 --smaddali modified this cursor to add new parameter p_group_cd for bug 2436567
306 -- smaddali modified this cursor to get records which are effective in the HESA submission period, bug#3235753
307 CURSOR c_psn_grp
308 (p_person_id NUMBER ,
309 p_group_cd VARCHAR2,
310 cp_enrl_start_dt igs_he_submsn_header.enrolment_start_date%TYPE,
311 cp_enrl_end_dt igs_he_submsn_header.enrolment_end_date%TYPE) IS
312 SELECT a.group_cd
313 FROM igs_pe_persid_group a ,
314 igs_pe_prsid_grp_mem_all b
315 WHERE b.person_id = p_person_id
316 AND a.group_cd = p_group_cd
317 AND ( b.Start_Date IS NULL OR b.Start_Date <= cp_enrl_end_dt)
318 AND ( b.End_Date IS NULL OR b.End_Date >= cp_enrl_start_dt )
319 AND a.group_id = b.group_id AND a.closed_ind = 'N' ;
320
321 -- Determine type (static or dynamic) of persion id group
322 CURSOR c_group_type (p_group_cd VARCHAR2) IS
323 SELECT group_id, group_type
324 FROM igs_pe_persid_group_v
325 WHERE group_cd = p_group_cd;
326
327 l_person_id NUMBER;
328 l_group_id NUMBER;
329 l_group_type igs_pe_persid_group_v.group_type%TYPE;
330 l_psn_group_cd igs_pe_persid_group.group_cd%TYPE := NULL;
331
332 BEGIN
333
334 FOR l_prm IN c_prm
335 LOOP
336 IF l_prm.param_type = 'PSN_IDENT_GROUP'
337 THEN
338
339 -- Determine type (static or dynamic) of person id group
340 OPEN c_group_type(NVL(l_prm.exclude ,l_prm.only));
341 FETCH c_group_type INTO l_group_id, l_group_type;
342 CLOSE c_group_type;
343
344 IF l_group_type = 'STATIC' THEN
345
346 -- Person Identity Group
347 --smaddali added new parameter p_group_cd to this cursor for bug 2436567
348 l_psn_group_cd := NULL;
349 OPEN c_psn_grp(g_pe_person.person_id , NVL(l_prm.exclude ,l_prm.only),
350 g_he_submsn_header.enrolment_start_date,
351 g_he_submsn_header.enrolment_end_date );
352 FETCH c_psn_grp INTO l_psn_group_cd;
353 CLOSE c_psn_grp;
354
355 IF l_psn_group_cd IS NOT NULL
356 AND l_prm.exclude IS NOT NULL
357 THEN
358 -- User does not want this Person Group
359 RETURN FALSE;
360
361 ELSIF l_prm.only IS NOT NULL
362 AND l_psn_group_cd IS NULL
363 THEN
364 -- User want only this Person Group
365 RETURN FALSE;
366 END IF;
367
368 ELSE
369
370
371 -- Is student in dynamic group?
372 l_person_id := IGS_PE_DYNAMIC_PERSID_GROUP.DYN_PIG_MEMBER(l_group_id,g_pe_person.person_id);
373
374 IF l_person_id IS NOT NULL
375 AND l_prm.exclude IS NOT NULL
376 THEN
377 --User does not want this Person Group
378 RETURN FALSE;
379 ELSIF l_prm.only IS NOT NULL
380 AND l_person_id IS NULL
381 THEN
382 --User want only this Person Group
383 RETURN FALSE;
384 END IF;
385
386
387 END IF;
388
389 ELSIF l_prm.param_type = 'PSN_ID'
390 THEN
391 -- Person Id
392 IF l_prm.exclude IS NOT NULL
393 AND l_prm.exclude = g_pe_person.person_id
394 THEN
395 -- User does not want this Person Id
396 RETURN FALSE;
397
398 ELSIF l_prm.only IS NOT NULL
399 AND l_prm.only <> g_pe_person.person_id
400 THEN
401 -- User wants only this Person Id
402 RETURN FALSE;
403 END IF;
404
405 END IF; -- Parameter Type
406
407 END LOOP;
408
409 -- All ok, pass back TRUE
410 RETURN TRUE;
411
412 EXCEPTION
413 WHEN OTHERS
414 THEN
415 write_to_log(SQLERRM);
416
417 -- Close Cursors
418 IF c_psn_grp%ISOPEN
419 THEN
420 CLOSE c_psn_grp;
421 END IF;
422
423 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
424 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_params');
425 IGS_GE_MSG_STACK.ADD;
426 App_Exception.Raise_Exception;
427
428 END validate_params;
429
430
431 --smaddali created this new procedure by splitting original procedure validate_params
432 -- into 3 procedures for bug 2350730
433 -- smaddali modified logic for checking PROGRAM_CATEGORY,PROGRAM_TYPE parameters for bug#3166126
434 -- AYEDUBAT 29-04-04 Changed the cursor, c_drm to add a new condition to check
435 -- for approved intermissions, if approval is required for Bug, 3494224
436 -- jbaber 30-11-04 Removed c_drm, using isDormant function instead for bug# 4037237
437
438 FUNCTION validate_params1 (p_extract_run_id IN NUMBER)
439 RETURN BOOLEAN
440 IS
441 CURSOR c_prm IS
442 SELECT param_type,
443 DECODE(exclude, 'YES', 'Y',
444 'NO', 'N',
445 exclude) exclude,
446 DECODE(only, 'YES', 'Y',
447 'NO', 'N',
448 only) only
449 FROM igs_he_ext_run_prms
450 WHERE extract_run_id = p_extract_run_id
451 AND (exclude IS NOT NULL
452 OR only IS NOT NULL)
453 AND param_type IN ('PROGRAM' ,'DORMANT','VISIT_EXCHANGE','POST_CODE' )
454 ORDER BY param_type;
455
456 CURSOR c_specst
457 (p_person_id igs_he_ad_dtl.person_id%TYPE,
458 p_admission_appl_number igs_he_ad_dtl.admission_appl_number%TYPE,
459 p_nominated_course_cd igs_he_ad_dtl.nominated_course_cd%TYPE ,
460 p_sequence_number igs_he_ad_dtl.sequence_number%TYPE) IS
461 SELECT special_student_cd
462 FROM igs_he_ad_dtl_all
463 WHERE person_id = p_person_id
464 AND admission_appl_number = p_admission_appl_number
465 AND nominated_course_cd = p_nominated_course_cd
466 AND sequence_number = p_sequence_number;
467
468
469 l_course_cat igs_ps_categorise.course_cat%TYPE ;
470 l_course_group_cd igs_ps_grp_mbr.course_group_cd%TYPE ;
471 l_hesa_special_student igs_he_code_map_val.map1%TYPE ;
472 l_ad_special_student igs_he_code_map_val.map1%TYPE ;
473 l_dummy VARCHAR2(50);
474 l_dormant BOOLEAN := FALSE;
475
476 --smaddali added these cursors for bug#3166126
477 -- check if exclude / only parameters are setup for Prog cat
478 CURSOR c_prg_cat_exst IS
479 SELECT exclude, only
480 FROM igs_he_ext_run_prms
481 WHERE extract_run_id = p_extract_run_id
482 AND param_type = 'PROGRAM_CATEGORY' ;
483 c_prg_cat_exst_rec c_prg_cat_exst%ROWTYPE ;
484
485 -- check if exclude / only parameters are setup for Prog group
486 CURSOR c_prg_grp_exst IS
487 SELECT exclude, only
488 FROM igs_he_ext_run_prms
489 WHERE extract_run_id = p_extract_run_id
490 AND param_type = 'PROGRAM_GROUP' ;
491 c_prg_grp_exst_rec c_prg_grp_exst%ROWTYPE ;
492
493 -- Check if the passed program belongs to an excluded Program category
494 CURSOR c_prg_cat_excl
495 (p_course_cd VARCHAR2,
496 p_version_number NUMBER ) IS
497 SELECT 'X'
498 FROM igs_ps_categorise_all
499 WHERE course_cd = p_course_cd
500 AND version_number = p_version_number
501 AND course_cat IN ( SELECT exclude FROM igs_he_ext_run_prms
502 WHERE extract_run_id = p_extract_run_id
503 AND exclude IS NOT NULL
504 AND param_type ='PROGRAM_CATEGORY') ;
505 -- Check if the passed program belongs to any ONLY Program category
506 CURSOR c_prg_cat_only
507 (p_course_cd VARCHAR2,
508 p_version_number NUMBER ) IS
509 SELECT 'X'
510 FROM igs_ps_categorise_all
511 WHERE course_cd = p_course_cd
512 AND version_number = p_version_number
513 AND course_cat IN ( SELECT only FROM igs_he_ext_run_prms
514 WHERE extract_run_id = p_extract_run_id
515 AND only IS NOT NULL
516 AND param_type ='PROGRAM_CATEGORY') ;
517
518 -- Check if the passed program belongs to an excluded Program group
519 CURSOR c_prg_grp_excl
520 (p_course_cd VARCHAR2,
521 p_version_number NUMBER ) IS
522 SELECT 'X'
523 FROM igs_ps_grp_mbr
524 WHERE course_cd = p_course_cd
525 AND version_number = p_version_number
526 AND course_group_cd IN ( SELECT exclude FROM igs_he_ext_run_prms
527 WHERE extract_run_id = p_extract_run_id
528 AND exclude IS NOT NULL
529 AND param_type ='PROGRAM_GROUP') ;
530 -- Check if the passed program belongs to any ONLY Program group
531 CURSOR c_prg_grp_only
532 (p_course_cd VARCHAR2,
533 p_version_number NUMBER ) IS
534 SELECT 'X'
535 FROM igs_ps_grp_mbr
536 WHERE course_cd = p_course_cd
537 AND version_number = p_version_number
538 AND course_group_cd IN ( SELECT only FROM igs_he_ext_run_prms
539 WHERE extract_run_id = p_extract_run_id
540 AND only IS NOT NULL
541 AND param_type ='PROGRAM_GROUP') ;
542 -- end bug#3166126
543
544 BEGIN
545
546 FOR l_prm IN c_prm
547 LOOP
548 IF l_prm.param_type = 'PROGRAM'
549 THEN
550 -- Program
551 IF l_prm.exclude IS NOT NULL
552 AND l_prm.exclude = g_en_stdnt_ps_att.course_cd
553 THEN
554 -- User does not want this Course Code
555 RETURN FALSE;
556
557 ELSIF l_prm.only IS NOT NULL
558 AND l_prm.only <> g_en_stdnt_ps_att.course_cd
559 THEN
560 -- User wants only this Course Code
561 RETURN FALSE;
562 END IF;
563
564 ELSIF l_prm.param_type = 'DORMANT'
565 THEN
566 l_dormant := FALSE;
567
568 -- Dormant
569 l_dormant := igs_he_extract_fields_pkg.isDormant
570 (p_person_id => g_en_stdnt_ps_att.person_id,
571 p_course_cd => g_en_stdnt_ps_att.course_cd,
572 p_version_number => g_en_stdnt_ps_att.version_number,
573 p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
574 p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
575
576 IF l_prm.exclude = 'Y'
577 AND l_dormant
578 THEN
579 -- User does not want Dormant Students
580 RETURN FALSE;
581
582 ELSIF l_prm.only = 'Y'
583 AND NOT l_dormant
584 THEN
585 -- User wants only Dormant Student
586 RETURN FALSE;
587
588 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '4'
589 AND NOT l_dormant
590 THEN
591 -- User wants only Dormant students
592 RETURN FALSE;
593 END IF;
594
595
596 ELSIF l_prm.param_type = 'VISIT_EXCHANGE'
597 THEN
598 l_ad_special_student := NULL;
599 l_hesa_special_student := NULL;
600 OPEN c_specst ( g_en_stdnt_ps_att.person_id,
601 g_en_stdnt_ps_att.adm_admission_appl_number,
602 g_en_stdnt_ps_att.adm_nominated_course_cd,
603 g_en_stdnt_ps_att.adm_sequence_number);
604 FETCH c_specst INTO l_ad_special_student;
605 CLOSE c_specst;
606
607 -- Visiting Exchange
608
609 igs_he_extract_fields_pkg.get_special_student
610 (p_ad_special_student => l_ad_special_student,
611 p_spa_special_student => g_he_st_spa.special_student,
612 p_oss_special_student => l_dummy,
613 p_hesa_special_student => l_hesa_special_student);
614
615 IF l_prm.exclude = 'Y'
616 AND l_hesa_special_student IN ('3','4','5','6','7','8')
617 THEN
618 -- User does not want Visiting / Exchange students
619 RETURN FALSE;
620
621 ELSIF l_prm.only = 'Y'
622 AND (l_hesa_special_student NOT IN ('3','4','5','6','7','8')
623 OR l_hesa_special_student IS NULL)
624 THEN
625 -- User wants only Visiting / Exchange students
626 RETURN FALSE;
627
628 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '3'
629 AND (l_hesa_special_student NOT IN ('3','4','5','6','7','8')
630 OR l_hesa_special_student IS NULL)
631 THEN
632 -- User wants only Visiting / Exchange students
633 RETURN FALSE;
634
635 END IF;
636
637
638 ELSIF l_prm.param_type = 'POST_CODE'
639 THEN
640 -- Postcode
641 IF l_prm.exclude IS NOT NULL
642 AND l_prm.exclude = g_he_st_spa.postcode
643 THEN
644 -- User does not want this postcode
645 RETURN FALSE;
646
647 ELSIF l_prm.only IS NOT NULL
648 AND (g_he_st_spa.postcode IS NULL
649 OR l_prm.only <> g_he_st_spa.postcode)
650 THEN
651 -- User wants only this postcode
652 RETURN FALSE;
653 END IF;
654 END IF; -- Parameter Type
655
656 END LOOP;
657
658 --smaddali moved group parameters program_group and Program_category check out of the LOOP for bug 3166126
659 -- PROGRAM CATEGORY CHECK
660 c_prg_cat_exst_rec := NULL ;
661 l_course_cat := NULL ;
662
663 OPEN c_prg_cat_exst ;
664 FETCH c_prg_cat_exst INTO c_prg_cat_exst_rec ;
665 CLOSE c_prg_cat_exst;
666 -- check if exclude Program category parameters are setup for this extract
667 IF c_prg_cat_exst_rec.exclude IS NOT NULL THEN
668 -- If the passed program belongs to an EXCLUDE Program category then exclude this SPA record
669 OPEN c_prg_cat_excl (g_en_stdnt_ps_att.course_cd,
670 g_en_stdnt_ps_att.version_number );
671 FETCH c_prg_cat_excl INTO l_course_cat;
672 CLOSE c_prg_cat_excl;
673
674 IF l_course_cat IS NOT NULL
675 THEN
676 -- User does not want this course category
677 RETURN FALSE;
678 END IF ;
679 -- check if only Program category parameters are setup for this extract
680 ELSIF c_prg_cat_exst_rec.only IS NOT NULL THEN
681 -- If the passed program does not belong to any ONLY Program category then exclude this SPA record
682 OPEN c_prg_cat_only (g_en_stdnt_ps_att.course_cd,
683 g_en_stdnt_ps_att.version_number );
684 FETCH c_prg_cat_only INTO l_course_cat;
685 CLOSE c_prg_cat_only;
686
687 IF l_course_cat IS NULL
688 THEN
689 -- User does not want this course category
690 RETURN FALSE;
691 END IF ;
692 END IF ;
693 -- PROGRAM CATEGORY CHECK
694
695 -- PROGRAM GROUP
696 c_prg_grp_exst_rec := NULL ;
697 l_course_group_cd := NULL ;
698 OPEN c_prg_grp_exst ;
699 FETCH c_prg_grp_exst INTO c_prg_grp_exst_rec ;
700 CLOSE c_prg_grp_exst;
701
702 -- check if 'exclude' Program group parameters are setup for this extract
703 IF c_prg_grp_exst_rec.exclude IS NOT NULL THEN
704 -- If the passed program belongs to an EXCLUDE Program group then exclude this SPA record
705 OPEN c_prg_grp_excl (g_en_stdnt_ps_att.course_cd,
706 g_en_stdnt_ps_att.version_number );
707 FETCH c_prg_grp_excl INTO l_course_group_cd;
708 CLOSE c_prg_grp_excl ;
709 IF l_course_group_cd IS NOT NULL
710 THEN
711 -- User does not want this course Group
712 RETURN FALSE;
713 END IF ;
714 -- check if 'only' Program group parameters are setup for this extract
715 ELSIF c_prg_grp_exst_rec.only IS NOT NULL THEN
716 -- If the passed program does not belong to any ONLY Program group then exclude this SPA record
717 OPEN c_prg_grp_only (g_en_stdnt_ps_att.course_cd,
718 g_en_stdnt_ps_att.version_number );
719 FETCH c_prg_grp_only INTO l_course_group_cd;
720 CLOSE c_prg_grp_only ;
721 IF l_course_group_cd IS NULL
722 THEN
723 -- User does not want this course Group
724 RETURN FALSE;
725 END IF ;
726 END IF ;
727 -- PROGRAM GROUP CHECK
728
729 -- All ok, pass back TRUE
730 RETURN TRUE;
731
732 EXCEPTION
733 WHEN OTHERS
734 THEN
735 write_to_log(SQLERRM);
736
737 -- Close Cursors
738 IF c_prg_grp_excl%ISOPEN
739 THEN
740 CLOSE c_prg_grp_excl;
741 END IF;
742
743 IF c_prg_grp_only%ISOPEN
744 THEN
745 CLOSE c_prg_grp_only;
746 END IF;
747
748 IF c_prg_cat_only%ISOPEN
749 THEN
750 CLOSE c_prg_cat_only;
751 END IF;
752
753 IF c_prg_cat_excl%ISOPEN
754 THEN
755 CLOSE c_prg_cat_excl;
756 END IF;
757
758
759
760 IF c_specst%ISOPEN
761 THEN
762 CLOSE c_specst;
763 END IF;
764
765 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
766 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_params1');
767 IGS_GE_MSG_STACK.ADD;
768 App_Exception.Raise_Exception;
769
770 END validate_params1;
771
772 --smaddali created this new procedure by splitting original procedure validate_params
773 -- into 3 procedures for bug 2350730
774 -- smaddali modified logic for checking ORG-UNIT parameter for bug#3166126
775 -- jtmathew modified call to get_funding_src to add spa parameter for bug#3962575
776 FUNCTION validate_params2 (p_extract_run_id IN NUMBER)
777 RETURN BOOLEAN
778 IS
779 CURSOR c_prm IS
780 SELECT param_type,
781 DECODE(exclude, 'YES', 'Y',
782 'NO', 'N',
783 exclude) exclude,
784 DECODE(only, 'YES', 'Y',
785 'NO', 'N',
786 only) only
787 FROM igs_he_ext_run_prms
788 WHERE extract_run_id = p_extract_run_id
789 AND (exclude IS NOT NULL
790 OR only IS NOT NULL)
791 AND param_type IN ( 'ORG_UNIT' ,'PROGRAM_YEAR','OUTSIDE_UK','FE')
792 ORDER BY param_type;
793
794 l_hesa_study_location igs_he_code_map_val.map1%TYPE ;
795 l_dummy VARCHAR2(50);
796
797 --smaddali added these variable declarations for bug 2483523
798 l_oss_value_64 igs_he_ex_rn_dat_fd.value%TYPE ;
799 l_hesa_value_64 igs_he_ex_rn_dat_fd.value%TYPE ;
800 l_oss_value_65 igs_he_ex_rn_dat_fd.value%TYPE ;
801 l_hesa_value_65 igs_he_ex_rn_dat_fd.value%TYPE ;
802 l_oss_value_6 igs_he_ex_rn_dat_fd.value%TYPE ;
803 l_hesa_value_6 igs_he_ex_rn_dat_fd.value%TYPE ;
804 -- smaddali added these parameters for bug#3166126
805 l_only_exists BOOLEAN;
806 l_only_matches BOOLEAN ;
807
808 BEGIN
809 -- smaddali added initialisation of variables for bug#3166126
810 l_only_exists := FALSE;
811 l_only_matches := FALSE ;
812
813 FOR l_prm IN c_prm
814 LOOP
815
816 IF l_prm.param_type = 'ORG_UNIT'
817 THEN
818 -- Organization Unit
819 IF l_prm.exclude IS NOT NULL
820 AND l_prm.exclude = g_ps_ver.responsible_org_unit_cd
821 THEN
822 -- User does not want this Organisation Unit
823 RETURN FALSE;
824
825 ELSIF l_prm.only IS NOT NULL THEN
826 l_only_exists := TRUE ;
827 IF l_prm.only = g_ps_ver.responsible_org_unit_cd
828 THEN
829 -- If this program's Org unit matches with an only Organisation Unit then select this person
830 l_only_matches := TRUE ;
831 END IF;
832 END IF;
833
834
835 ELSIF l_prm.param_type = 'PROGRAM_YEAR'
836 THEN
837 -- Program Year
838 IF l_prm.exclude IS NOT NULL
839 AND l_prm.exclude = g_as_su_setatmpt.unit_set_cd
840 THEN
841 -- User does not want this Program Year
842 RETURN FALSE;
843
844 ELSIF l_prm.only IS NOT NULL
845 AND l_prm.only <> g_as_su_setatmpt.unit_set_cd
846 THEN
847 -- User wants only this Program Year
848 RETURN FALSE;
849 END IF;
850
851 ELSIF l_prm.param_type = 'OUTSIDE_UK'
852 THEN
853 -- Outside UK
854 l_hesa_study_location := NULL;
855 igs_he_extract_fields_pkg.get_study_location
856 (p_susa_study_location => g_he_en_susa.study_location,
857 p_poous_study_location => g_he_poous.location_of_study,
858 p_prg_study_location => g_he_st_prog.location_of_study,
859 p_oss_study_location => l_dummy,
860 p_hesa_study_location => l_hesa_study_location);
861
862 IF l_prm.exclude = 'Y'
863 AND l_hesa_study_location = '7'
864 THEN
865 -- User does not want students outside UK
866 RETURN FALSE;
867
868 ELSIF l_prm.only = 'Y'
869 AND (l_hesa_study_location <> '7'
870 OR l_hesa_study_location IS NULL)
871 THEN
872 -- User wants only students outside UK
873 RETURN FALSE;
874 END IF;
875
876
877 ELSIF l_prm.param_type = 'FE'
878 THEN
879 -- smaddali added this code to calculate field 6 , bug 2483523 ,
880 -- if the value of field 6 is '2' then student is not an FE student
881 -- FE Student Marker
882 -- First get the Funding Source
883 -- smaddali Modifed call to add new parameter for hefd208 build , bug#2717751
884 l_oss_value_64 := NULL;
885 l_hesa_value_64 := NULL;
886 l_oss_value_65 := NULL;
887 l_hesa_value_65 := NULL;
888 l_oss_value_6 := NULL;
889 l_hesa_value_6 := NULL;
890 igs_he_extract_fields_pkg.get_funding_src
891 (p_course_cd => g_en_stdnt_ps_att.course_cd ,
892 p_version_number => g_en_stdnt_ps_att.version_number,
893 p_spa_fund_src => g_en_stdnt_ps_att.funding_source,
894 p_poous_fund_src => g_he_poous.funding_source,
895 p_oss_fund_src => l_oss_value_64,
896 p_hesa_fund_src => l_hesa_value_64 );
897
898 -- Next get the Fundability Code
899 -- smaddali 11-dec-03 Modified for bug#3235753 , added 2 new parameters
900 igs_he_extract_fields_pkg.get_fundability_cd
901 (p_person_id => g_en_stdnt_ps_att.person_id,
902 p_susa_fund_cd => g_he_en_susa.fundability_code,
903 p_spa_funding_source => g_en_stdnt_ps_att.funding_source,
904 p_poous_fund_cd => g_he_poous.fundability_cd,
905 p_prg_fund_cd => g_he_st_prog.fundability,
906 p_prg_funding_source => l_oss_value_64,
907 p_oss_fund_cd => l_oss_value_65,
908 p_hesa_fund_cd => l_hesa_value_65 ,
909 p_enrl_start_dt => g_he_submsn_header.enrolment_start_date,
910 p_enrl_end_dt => g_he_submsn_header.enrolment_end_date);
911
912 -- Now get the FE Student Marker
913 igs_he_extract_fields_pkg.get_fe_stdnt_mrker
914 (p_spa_fe_stdnt_mrker => g_he_st_spa.fe_student_marker,
915 p_fe_program_marker => g_he_st_prog.fe_program_marker,
916 p_funding_src => l_oss_value_64,
917 p_fundability_cd => l_oss_value_65,
918 p_oss_fe_stdnt_mrker => l_oss_value_6,
919 p_hesa_fe_stdnt_mrker => l_hesa_value_6 );
920
921 -- Further Education
922 -- smaddali modified code to use l_hesa_value_6 instead of g_he_st_spa.fe_student_marker
923 -- or g_he_st_prog.fe_program_marker to determine if the student is an fe student ,for bug 2483523
924 IF l_prm.exclude = 'Y'
925 AND l_hesa_value_6 <> '2'
926 THEN
927 -- User does not want Visiting / Exchange students
928 RETURN FALSE;
929
930 ELSIF l_prm.only = 'Y'
931 AND NVL(l_hesa_value_6,'2') = '2'
932 THEN
933 -- User wants only Visiting / Exchange students
934 RETURN FALSE;
935
936 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '2'
937 AND NVL(l_hesa_value_6,'2') = '2'
938 THEN
939 -- User wants only Visiting / Exchange students
940 RETURN FALSE;
941
942 END IF;
943
944 END IF; -- Parameter Type
945
946 END LOOP;
947
948 -- All ok, pass back TRUE
949 -- If only parameters were setup but the current person's dlhe record status doesnot match any of them
950 -- then exclude this person else include this person in this return
951 IF l_only_exists AND NOT l_only_matches THEN
952 RETURN FALSE ;
953 ELSE
954 RETURN TRUE;
955 END IF;
956
957 EXCEPTION
958 WHEN OTHERS
959 THEN
960 write_to_log(SQLERRM);
961
962 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
963 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_params2');
964 IGS_GE_MSG_STACK.ADD;
965 App_Exception.Raise_Exception;
966
967 END validate_params2;
968
969
970
971 /*----------------------------------------------------------------------
972 This procedure deletes rows from tables before starting the
973 process
974
975 Because it uses table handlers, there might be a server
976 performance issue as the number of rows being processed
977 would be huge
978
979 Parameters :
980 p_extract_run_id IN The Extract Run Id
981 p_line_number IN Line Number
982 p_table_name IN Table Name
983 Values : INTERIM - Igs_He_Ext_Run_Interim
984 LINE - Igs_He_Ex_Rn_Dat_Ln
985 FIELD - Igs_He_Ex_Rn_Dat_Fd
986 ERROR - Igs_He_Ext_Run_Excp
987 WHO: AYEDUBAT
988 WHAT: Removed the 'PROCEDURE delete_rows' as all the call to this procedure are placed
989 with direct DMLS for bug,3179585
990 ----------------------------------------------------------------------*/
991
992
993 /*----------------------------------------------------------------------
994 This procedure fetches the modules that need to be processed
995 and inserts them into the temporary procesing table
996 Parameters :
997 p_extract_run_id IN The Extract Run Id
998 --smaddali seperated the extract parameters validation into 3 different groups :person,
999 -- program attempt and program,program year parameters for bug 2350730
1000 -- hence the procedure validate_params has been split into 3 procedures
1001 -- namely validate_params , validate_params1,validate_params2
1002 -- and calls to these procedures have been added in this procedure at appropriate places
1003
1004 --Done as a part of HEFD101(2636897)
1005 --Bayadav Included in the WHERE clause the graduated student also but who have not awarded ans the conferral_td is set
1006 --Outer join is to consider the graduation.conferraldt condition only in case the student have graduation rec .
1007 --The other students(not graduated) should also be selected
1008 --smvk 03-Jun-2003 Bug # 2858436.Modified the cursor c_quaim to select open program awards only.
1009 16-DEC-02 Bayadav Included the conditions in the WHERE clause to consider the students who have been awarded in HESA period but must have comepleted the course earlier as a part of bug 2702117
1010 20-JAN-2003 Bayadav Included the validations to check if the alternate person id does not contains non-numeric characters for the person in context as a part of 2744808
1011 03-MAR-2003 bayadav Included check in c_encp cursor to cehck for the Units
1012 sarakshi 26-Jun-2003 Enh#2930935,modified cursor c_encp to include unit section level
1013 enrolled_credit_points if exists else unit level credit points
1014 dsridhar 04-Jul-03 Bug No:3079731. Changed the order of setting the tokens for the message IGS_HE_INVALID_PER_ID.
1015 smaddali 20-Oct-03 Modified procedure for bug#3172980 , skip students whose api person id> 8 digits
1016 ayedubat 14-Nov-03 Modified the procedure to improve the performance for Bug, 3179585
1017 smaddali 05-Dec-03 Modified cursors c_get_yop, c_get_spa to add condition complete_ind=Y , for HECR210 build, bug#2874542
1018 smaddali 10-Dec-03 Modified logic to get Term record details for HECR214 - Term based fees enhancement, bug#3291656
1019 smaddali 14-Jan-04 Modified cursor c_qulaim for bug#3360646
1020 ayedubat 09-Mar-04 Modified logic to check the condition, l_std_inst.person_id <> l_prev_person_id only
1021 when logging the error message in the log file for Bug, 3491096
1022 jbaber 04-Nov-04 Modified c_get_spa for HE354 - Program Transfer
1023 Replace c_inact_st with c_enr_su for bug 3810280
1024 slaport 31-Jan-05 Modified cursor c_alternate_id for HE358 to ignore logically deleted records.
1025 jbaber 15-Apr-05 Modified c_get_spa cursor to include records where future_date_trans_flag = N or S as per bug #4179106
1026 jtmathew 27-Jan-06 Modified c_get_spa cursor to include award conferral date parameters
1027 jbaber 15-Mar-06 Added p_recalculate parameter for HE365 - Extract Rerun
1028 ----------------------------------------------------------------------*/
1029
1030 PROCEDURE get_students (p_extract_run_id IN NUMBER, p_recalculate IN BOOLEAN) IS
1031
1032 --smaddali modified where clause for comparing the enrolment dates for bug 2415632
1033 --dsridhar modified the table form igs_pe_person to igs_pe_person_base_v for the bug 2911738
1034 --Removed the cursor, c_get_stins for Bug, 3179585
1035 --smaddali modified where clause for comparing the enrolment dates for bug 2415632
1036 --smaddali added field hspa.fe_student_marker for bug 2452834
1037 --Removed the cursor to remove person_id and person_number parameters for Bug, 3179585
1038 --jbaber added check for exclude flag and removed calendar types for HE305
1039 CURSOR c_get_spa (
1040 p_submission_name igs_he_submsn_header.submission_name%TYPE,
1041 p_return_name igs_he_submsn_return.return_name%TYPE,
1042 p_user_return_subclass igs_he_submsn_return.user_return_subclass%TYPE,
1043 p_enrl_start_dt DATE,
1044 p_enrl_end_dt DATE,
1045 p_awd_conf_start_dt DATE,
1046 p_awd_conf_end_dt DATE) IS
1047 SELECT DISTINCT sca.person_id,
1048 pe.party_number person_number,
1049 sca.course_cd,
1050 sca.version_number,
1051 sca.location_cd ,
1052 sca.attendance_mode,
1053 sca.attendance_type,
1054 sca.cal_type sca_cal_type,
1055 sca.commencement_dt ,
1056 sca.discontinued_dt,
1057 sca.course_rqrmnt_complete_ind,
1058 sca.course_rqrmnts_complete_dt,
1059 sca.adm_admission_appl_number,
1060 sca.adm_nominated_course_cd,
1061 sca.adm_sequence_number,
1062 sca.course_attempt_status,
1063 sca.funding_source,
1064 hspa.student_inst_number,
1065 hspa.student_qual_aim,
1066 hspa.return_type,
1067 hspa.postcode,
1068 hspa.special_student,
1069 hspa.fe_student_marker ,
1070 enawd.complete_ind,
1071 enawd.conferral_date
1072 FROM igs_en_stdnt_ps_att_all sca,
1073 igs_he_st_spa_all hspa,
1074 igs_he_st_prog_all hprog,
1075 igs_en_spa_awd_aim enawd,
1076 hz_parties pe
1077 WHERE sca.person_id = hspa.person_id
1078 AND sca.course_cd = hspa.course_cd
1079 AND sca.course_cd = hprog.course_cd (+)
1080 AND sca.version_number = hprog.version_number (+)
1081 AND NVL(hprog.exclude_flag, 'N') = 'N'
1082 AND NVL(hspa.exclude_flag, 'N') = 'N'
1083 AND NVL(sca.future_dated_trans_flag,'N') IN ('N','S')
1084 AND sca.student_confirmed_ind = 'Y'
1085 AND hspa.person_id = enawd.person_id(+)
1086 AND hspa.course_cd = enawd.course_cd(+)
1087 AND sca.person_id = pe.party_id
1088 AND ( ( sca.commencement_dt <= p_enrl_end_dt
1089 AND ( sca.discontinued_dt IS NULL OR sca.discontinued_dt >= p_enrl_start_dt )
1090 AND (sca.course_rqrmnts_complete_dt IS NULL OR
1091 sca.course_rqrmnts_complete_dt >= p_enrl_start_dt
1092 )
1093 )
1094 OR
1095 (
1096 enawd.complete_ind = 'Y' AND
1097 (enawd.conferral_date BETWEEN p_awd_conf_start_dt AND p_awd_conf_end_dt))
1098 )
1099 ORDER BY sca.person_id, hspa.student_inst_number, discontinued_dt DESC,
1100 course_rqrmnts_complete_dt DESC, sca.commencement_dt DESC ;
1101
1102 -- smaddali modified cursor for bug#3360646, to remove the check for default award
1103 CURSOR c_quaim
1104 (p_course_cd igs_he_st_spa.course_cd%TYPE,
1105 p_version_number igs_he_st_spa.version_number%TYPE)
1106 IS
1107 SELECT award_cd
1108 FROM igs_ps_award
1109 WHERE course_cd = p_course_cd
1110 AND version_number = p_version_number
1111 AND closed_ind = 'N' ;
1112
1113 --smaddali modified where clause for comparing the enrolment dates for bug 2415632
1114 -- smaddali 27-desc-2002 modified cursor to check for conferral date , bug 2702100
1115 CURSOR c_get_yop
1116 (p_person_id igs_he_st_spa.person_id%TYPE,
1117 p_course_cd igs_he_st_spa.course_cd%TYPE,
1118 p_enrl_start_dt DATE,
1119 p_enrl_end_dt DATE,
1120 p_awd_conf_start_dt DATE,
1121 p_awd_conf_end_dt DATE)
1122 IS
1123 SELECT DISTINCT susa.unit_set_cd,
1124 susa.us_version_number,
1125 susa.sequence_number,
1126 susa.selection_dt,
1127 susa.end_dt,
1128 susa.rqrmnts_complete_ind,
1129 susa.rqrmnts_complete_dt,
1130 husa.study_location ,
1131 husa.fte_perc_override,
1132 husa.credit_value_yop1
1133 FROM igs_as_su_setatmpt susa,
1134 igs_he_en_susa husa,
1135 igs_en_unit_set us,
1136 igs_en_unit_set_cat susc,
1137 igs_en_spa_awd_aim enawd,
1138 igs_en_stdnt_ps_att_all sca
1139 WHERE susa.person_id = sca.person_id
1140 AND susa.course_cd = sca.course_cd
1141 AND sca.person_id = enawd.person_id(+)
1142 AND sca.course_cd = enawd.course_cd(+)
1143 AND susa.unit_set_cd = husa.unit_set_cd
1144 AND susa.us_version_number = husa.us_version_number
1145 AND susa.person_id = husa.person_id
1146 AND susa.course_cd = husa.course_cd
1147 AND susa.sequence_number = husa.sequence_number
1148 AND susa.unit_set_cd = us.unit_set_cd
1149 AND susa.us_version_number = us.version_number
1150 AND us.unit_set_cat = susc.unit_set_cat
1151 AND susa.person_id = p_person_id
1152 AND susa.course_cd = p_course_cd
1153 AND susc.s_unit_set_cat = 'PRENRL_YR'
1154 -- the program attempt is overlapping with the submission period and the yop is also overlapping with the submission period
1155 AND ( ( sca.commencement_dt <= p_enrl_end_dt AND
1156 (sca.discontinued_dt IS NULL OR sca.discontinued_dt >= p_enrl_start_dt ) AND
1157 (sca.course_rqrmnts_complete_dt IS NULL OR sca.course_rqrmnts_complete_dt >= p_enrl_start_dt ) AND
1158 susa.selection_dt <= p_enrl_end_dt AND
1159 (susa.end_dt IS NULL OR susa.end_dt >= p_enrl_start_dt ) AND
1160 (susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= p_enrl_start_dt)
1161 )
1162 OR
1163 -- the yop has completed before the start of the submission period
1164 -- AND the program attempt has completed before the end of the submission period
1165 -- AND an award has been conferred between the NVL(award conferral dates, submission period)
1166 ( susa.rqrmnts_complete_dt < p_enrl_start_dt AND
1167 sca.course_rqrmnts_complete_dt <= p_enrl_end_dt AND
1168 enawd.complete_ind = 'Y' AND
1169 enawd.conferral_date BETWEEN p_awd_conf_start_dt AND p_awd_conf_end_dt
1170 )
1171 )
1172 ORDER BY susa.rqrmnts_complete_dt DESC, susa.end_dt DESC, susa.selection_dt DESC;
1173
1174 -- smaddali Modifed cursor to fetch funding_source for hefd208 build , bug#2717751
1175 CURSOR c_get_crse
1176 (p_course_cd igs_he_st_spa.course_cd%TYPE,
1177 p_crv_version_number igs_he_st_spa.version_number%TYPE,
1178 p_cal_type igs_ps_ofr_opt.cal_type%TYPE,
1179 p_attendance_mode igs_ps_ofr_opt.attendance_mode%TYPE,
1180 p_attendance_type igs_ps_ofr_opt.attendance_type%TYPE,
1181 p_location_cd igs_ps_ofr_opt.location_cd%TYPE,
1182 p_unit_set_cd igs_he_poous_all.unit_set_cd%TYPE,
1183 p_us_version_number igs_he_poous_all.us_version_number%TYPE)
1184 IS
1185 SELECT crv.title,
1186 crv.std_annual_load,
1187 crv.contact_hours,
1188 crv.govt_special_course_type,
1189 crv.responsible_org_unit_cd,
1190 hpr.location_of_study ,
1191 hpr.return_type,
1192 hpr.default_award,
1193 Nvl(hpr.program_calc,'N') ,
1194 hpr.fe_program_marker,
1195 hpud.location_of_study,
1196 hpud.credit_value_yop1,
1197 hpud.fte_intensity ,
1198 hpud.funding_source
1199 FROM igs_ps_ver crv,
1200 igs_he_st_prog hpr,
1201 igs_he_poous hpud
1202 WHERE crv.course_cd = hpr.course_cd
1203 AND crv.version_number = hpr.version_number
1204 AND crv.course_cd = p_course_cd
1205 AND crv.version_number = p_crv_version_number
1206 AND hpud.course_cd = crv.course_cd
1207 AND hpud.crv_version_number = crv.version_number
1208 AND hpud.cal_type = p_cal_type
1209 AND hpud.attendance_mode = p_attendance_mode
1210 AND hpud.attendance_type = p_attendance_type
1211 AND hpud.location_cd = p_location_cd
1212 AND hpud.unit_set_cd = p_unit_set_cd
1213 AND hpud.us_version_number = p_us_version_number;
1214
1215
1216 -- jbaber created this cursor for bug 3810280
1217 -- returns a row if a SPA has any unit attempts with a status of ENROLLED, COMPLETED, DISCONTIN
1218 -- or DUPLICATE where the unit attempt enrollment date is less than or equal to the reporting
1219 -- period end date.
1220 CURSOR c_enr_su (p_person_id igs_en_stdnt_ps_att_all.person_id%TYPE,
1221 p_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE,
1222 p_enrolment_end_date igs_he_submsn_header.enrolment_end_date%TYPE) IS
1223 SELECT 'X'
1224 FROM igs_en_su_attempt_all
1225 WHERE person_id = p_person_id
1226 AND course_cd = p_course_cd
1227 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','DISCONTIN','DUPLICATE')
1228 AND TRUNC(enrolled_dt) <= p_enrolment_end_date;
1229
1230 -- Changed the cursor to remove the parameter, p_id_type and replacing with the hard coded values
1231 -- like HUSID, UCASID,'GTTRID', 'NMASID' and 'SWASID' for bug,315
1232 -- smaddali modified cursor to select length for bug 3172980
1233 -- smaddali modified this cursor to get records which are effective in the HESA submission period, bug#3235753
1234 CURSOR c_alternate_id
1235 ( p_person_id IN igs_pe_person.person_id%TYPE,
1236 cp_enrl_start_dt igs_he_submsn_header.enrolment_start_date%TYPE,
1237 cp_enrl_end_dt igs_he_submsn_header.enrolment_end_date%TYPE) IS
1238 SELECT api_person_id,person_id_type, LENGTH(api_person_id) api_length
1239 FROM igs_pe_alt_pers_id
1240 WHERE pe_person_id = p_person_id
1241 AND person_id_type IN ('HUSID', 'UCASID', 'GTTRID', 'NMASID', 'SWASID')
1242 AND Start_Dt <= cp_enrl_end_dt
1243 AND ( End_Dt IS NULL OR End_Dt >= cp_enrl_start_dt )
1244 AND (End_Dt IS NULL OR Start_Dt <> End_Dt)
1245 ORDER BY person_id_type, Start_Dt DESC ;
1246 l_prev_pid_type igs_pe_alt_pers_id.person_id_type%TYPE := 'X' ;
1247
1248 l_awd_min_dt DATE;
1249 l_awd_max_dt DATE;
1250 l_awd_conf_start_dt DATE;
1251 l_awd_conf_end_dt DATE;
1252
1253 l_enrolled_su c_enr_su%ROWTYPE ;
1254 l_valid BOOLEAN := TRUE;
1255 l_rowid VARCHAR2(50);
1256 l_ext_interim_id NUMBER;
1257 l_award_cd igs_ps_award.award_cd%TYPE;
1258 l_rec_count NUMBER := 0;
1259 l_message VARCHAR2(2000);
1260 l_return_type VARCHAR2(3);
1261 l_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE;
1262 l_id NUMBER;
1263 l_prev_person_id NUMBER := -1;
1264 l_prev_student_inst_number VARCHAR2(100) := '-1';
1265
1266 -- smaddali added following cursors for HECR214 - term based fees enhancement build, bug#3291656
1267
1268 -- Get the latest Term record for the Leavers,where the student left date lies between term start and end dates
1269 CURSOR c_term1_lev( cp_person_id igs_en_spa_terms.person_id%TYPE,
1270 cp_course_cd igs_en_spa_terms.program_cd%TYPE,
1271 cp_lev_dt DATE ) IS
1272 SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
1273 FROM igs_en_spa_terms tr , igs_ca_inst_all ca
1274 WHERE tr.term_cal_type = ca.cal_type AND
1275 tr.term_sequence_number = ca.sequence_number AND
1276 tr.person_id = cp_person_id AND
1277 tr.program_cd = cp_course_cd AND
1278 cp_lev_dt BETWEEN ca.start_dt AND ca.end_dt
1279 ORDER BY ca.start_dt DESC;
1280 c_term1_lev_rec c_term1_lev%ROWTYPE ;
1281
1282 -- Get the latest Term record for the Leavers just before the student left
1283 CURSOR c_term2_lev( cp_person_id igs_en_spa_terms.person_id%TYPE,
1284 cp_course_cd igs_en_spa_terms.program_cd%TYPE,
1285 cp_lev_dt DATE ,
1286 cp_enrl_start_dt igs_he_submsn_header.enrolment_start_date%TYPE,
1287 cp_enrl_end_dt igs_he_submsn_header.enrolment_end_date%TYPE) IS
1288 SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
1289 FROM igs_en_spa_terms tr , igs_ca_inst_all ca
1290 WHERE tr.term_cal_type = ca.cal_type AND
1291 tr.term_sequence_number = ca.sequence_number AND
1292 tr.person_id = cp_person_id AND
1293 tr.program_cd = cp_course_cd AND
1294 cp_lev_dt > ca.start_dt AND
1295 ca.start_dt BETWEEN cp_enrl_start_dt AND cp_enrl_end_dt
1296 ORDER BY ca.start_dt DESC;
1297 c_term2_lev_rec c_term2_lev%ROWTYPE ;
1298
1299 -- Get the latest term record for the Continuing students, where the term start date lies in the HESA submission period
1300 CURSOR c_term_con ( cp_person_id igs_en_spa_terms.person_id%TYPE,
1301 cp_course_cd igs_en_spa_terms.program_cd%TYPE ,
1302 cp_enrl_start_dt igs_he_submsn_header.enrolment_start_date%TYPE,
1303 cp_enrl_end_dt igs_he_submsn_header.enrolment_end_date%TYPE) IS
1304 SELECT tr.program_version , tr.acad_cal_type, tr.location_cd, tr.attendance_mode, tr.attendance_type
1305 FROM igs_en_spa_terms tr , igs_ca_inst_all ca
1306 WHERE tr.term_cal_type = ca.cal_type AND
1307 tr.term_sequence_number = ca.sequence_number AND
1308 tr.person_id = cp_person_id AND
1309 tr.program_cd = cp_course_cd AND
1310 ca.start_dt BETWEEN cp_enrl_start_dt AND cp_enrl_end_dt
1311 ORDER BY ca.start_dt DESC;
1312 c_term_con_rec c_term_con%ROWTYPE ;
1313 l_lev_dt igs_en_stdnt_ps_att_all.discontinued_dt%TYPE ;
1314
1315 BEGIN
1316
1317 -- printing datetimestamp for monitoring performance
1318 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
1319 fnd_message.set_token('PROCEDURE', 'GET_STUDENTS');
1320 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1321 fnd_file.put_line(fnd_file.log, fnd_message.get);
1322
1323 fnd_message.set_name('IGS','IGS_HE_STD_RETURN');
1324 fnd_file.put_line(fnd_file.log,fnd_message.get());
1325
1326 l_awd_min_dt := NULL;
1327 l_awd_max_dt := NULL;
1328
1329 -- get the award conferral details
1330 igs_he_extract_fields_pkg.get_awd_dtls(g_he_ext_run_dtls.submission_name,
1331 g_awd_table,
1332 g_prog_rec_flag, g_prog_type_rec_flag);
1333
1334 -- get the minimum award conferral start date and maximum award conferral end date
1335 igs_he_extract_fields_pkg.get_min_max_awd_dates(g_he_ext_run_dtls.submission_name,
1336 g_he_submsn_header.enrolment_start_date,
1337 g_he_submsn_header.enrolment_end_date,
1338 l_awd_min_dt,
1339 l_awd_max_dt);
1340
1341 FOR l_std_inst IN c_get_spa( g_he_ext_run_dtls.submission_name, g_he_ext_run_dtls.return_name,
1342 g_he_ext_run_dtls.user_return_subclass, g_he_submsn_header.enrolment_start_date,
1343 g_he_submsn_header.enrolment_end_date,
1344 l_awd_min_dt, l_awd_max_dt) LOOP
1345
1346 IF (l_std_inst.person_id <> l_prev_person_id) OR (l_std_inst.student_inst_number <> l_prev_student_inst_number) THEN
1347
1348 -- smaddali added initialisation of loop varables , bug#3166126
1349 g_en_stdnt_ps_att := NULL;
1350 g_he_st_spa := NULL;
1351 g_as_su_setatmpt := NULL;
1352 g_he_en_susa := NULL;
1353 g_he_st_prog := NULL;
1354 g_ps_ver := NULL;
1355 g_he_poous := NULL;
1356 g_pe_person := NULL;
1357 g_he_ad_dtl := NULL;
1358 l_awd_conf_start_dt := NULL;
1359 l_awd_conf_end_dt := NULL;
1360
1361 g_en_stdnt_ps_att.person_id := l_std_inst.person_id;
1362 g_en_stdnt_ps_att.course_cd := l_std_inst.course_cd;
1363 g_en_stdnt_ps_att.version_number := l_std_inst.version_number;
1364 g_en_stdnt_ps_att.location_cd := l_std_inst.location_cd ;
1365 g_en_stdnt_ps_att.attendance_mode := l_std_inst.attendance_mode;
1366 g_en_stdnt_ps_att.attendance_type := l_std_inst.attendance_type;
1367 g_en_stdnt_ps_att.cal_type := l_std_inst.sca_cal_type;
1368 g_en_stdnt_ps_att.commencement_dt := l_std_inst.commencement_dt ;
1369 g_en_stdnt_ps_att.discontinued_dt := l_std_inst.discontinued_dt;
1370 g_en_stdnt_ps_att.course_rqrmnt_complete_ind := l_std_inst.course_rqrmnt_complete_ind;
1371 g_en_stdnt_ps_att.course_rqrmnts_complete_dt := l_std_inst.course_rqrmnts_complete_dt;
1372 g_en_stdnt_ps_att.adm_admission_appl_number := l_std_inst.adm_admission_appl_number;
1373 g_en_stdnt_ps_att.adm_nominated_course_cd := l_std_inst.adm_nominated_course_cd;
1374 g_en_stdnt_ps_att.adm_sequence_number := l_std_inst.adm_sequence_number;
1375 g_en_stdnt_ps_att.course_attempt_status := l_std_inst.course_attempt_status;
1376 g_en_stdnt_ps_att.funding_source := l_std_inst.funding_source;
1377 g_he_st_spa.student_inst_number := l_std_inst.student_inst_number;
1378 g_he_st_spa.student_qual_aim := l_std_inst.student_qual_aim;
1379 g_he_st_spa.return_type := l_std_inst.return_type;
1380 g_he_st_spa.postcode := l_std_inst.postcode;
1381 g_he_st_spa.special_student := l_std_inst.special_student;
1382 g_he_st_spa.fe_student_marker := l_std_inst.fe_student_marker ;
1383
1384 g_pe_person.person_number := l_std_inst.person_number;
1385 g_pe_person.person_id := l_std_inst.person_id ;
1386
1387 -- Flag to keep track of whether a SPA record has passed all validations
1388 -- if not, processing should continue with the next SPA record.
1389 l_valid := TRUE;
1390
1391
1392
1393 IF NOT validate_award_conferral_dates (l_std_inst.complete_ind,
1394 l_std_inst.conferral_date,
1395 l_awd_conf_start_dt,
1396 l_awd_conf_end_dt) THEN
1397 l_valid := FALSE;
1398 END IF;
1399
1400 -- Validate record with recalculate parameters
1401 IF l_valid AND p_recalculate THEN
1402
1403 IF NOT validate_recalc_params(p_extract_run_id, g_en_stdnt_ps_att.person_id, g_en_stdnt_ps_att.course_cd) THEN
1404 -- exclude this record
1405 l_valid := FALSE;
1406 END IF;
1407
1408 END IF;
1409
1410
1411 IF l_valid THEN
1412
1413 -- validate person , person id group parameters
1414 --these validations have been seperated from the other validations
1415 -- by smaddali for bug 2350730
1416 IF NOT validate_params( p_extract_run_id) THEN
1417 -- exclude this record
1418 l_valid := FALSE ;
1419 END IF;
1420
1421 END IF;
1422
1423 -- for doing the following validations only once for a Person and not for every program attempt of the person
1424 IF l_valid THEN
1425
1426 l_prev_pid_type := 'X' ;
1427 --TO check that the alternate person id's if present for the person is number (i.e it does not contains non-numeric character)
1428 -- Changed the logic to replace individual calls with in the loop for bug,315
1429 FOR alternate_id_rec IN c_alternate_id( l_std_inst.person_id,
1430 g_he_submsn_header.enrolment_start_date,
1431 g_he_submsn_header.enrolment_end_date) LOOP
1432 -- smaddali added this check for bug#3235753 , because the cursor will bring more than one
1433 -- alternate personid record for each person id type, the first record being the valid record.
1434 -- so we need to skip this validation from the 2nd record of each person_id_type
1435 IF (alternate_id_rec.person_id_type <> l_prev_pid_type) THEN
1436
1437 l_prev_pid_type := alternate_id_rec.person_id_type;
1438 BEGIN
1439
1440 l_id := NULL;
1441 l_api_person_id := NULL ;
1442 l_api_person_id := alternate_id_rec.api_person_id;
1443 IF l_api_person_id IS NOT NULL THEN
1444 l_id := TO_NUMBER(l_api_person_id);
1445 END IF;
1446
1447 EXCEPTION
1448 WHEN value_error THEN
1449 -- added the check l_std_inst.person_id <> l_prev_person_id for Bug, 3491096
1450 IF l_std_inst.person_id <> l_prev_person_id THEN
1451 fnd_message.set_name('IGS','IGS_HE_INVALID_PER_ID');
1452 fnd_Message.Set_Token('PERSON_ID_TYPE',alternate_id_rec.person_id_type);
1453 fnd_Message.Set_Token('PERSON',l_std_inst.person_number);
1454 fnd_file.put_line(fnd_file.log,fnd_message.get());
1455 END IF;
1456
1457 --In case the alternate person id contains non-numeric characters ,then log the message and
1458 -- exclude this record from furtehr processing
1459 l_valid := FALSE ;
1460 END;
1461
1462 -- smaddali 20-oct-03 added code to log error when alternate person id > 8 digits. for bug#3172980
1463 -- Modified this validation to exclude the HUSID Type for bug, 3296711
1464 IF alternate_id_rec.person_id_type <> 'HUSID' AND alternate_id_rec.api_length > 8 THEN
1465 -- added the check l_std_inst.person_id <> l_prev_person_id for Bug, 3491096
1466 IF l_std_inst.person_id <> l_prev_person_id THEN
1467 fnd_message.set_name('IGS','IGS_HE_PERSID_MORE_DIGITS');
1468 fnd_Message.Set_Token('PIDTYPE',alternate_id_rec.person_id_type);
1469 fnd_Message.Set_Token('PERSON',l_std_inst.person_number);
1470 fnd_file.put_line(fnd_file.log,fnd_message.get());
1471 END IF;
1472 --In case the alternate person id contains more than 8 digits ,then log the message and
1473 -- exclude this record from furtehr processing
1474 l_valid := FALSE ;
1475 END IF;
1476
1477 END IF; -- validate only latest Person id type record of each type
1478 END LOOP;
1479
1480 END IF; -- if valid
1481
1482 -- smaddali moved the initialisation if these variables here because l_prev_person_id is being used by the
1483 -- alternate person id check also.
1484 l_prev_person_id := l_std_inst.person_id;
1485 l_prev_student_inst_number := l_std_inst.student_inst_number;
1486
1487 --smaddali seperated validations for program attempt parameters ,for bug 2350730
1488 -- Use the Extract Run Parameters to check if the
1489 -- record satisfies the program_group,program_category,program,dormant parameter criteria
1490 IF l_valid
1491 THEN
1492
1493 -- smaddali added following code for HECR214 - term based fees enhancement build , Bug#3291656
1494 -- to get version_number,cal_type,location_cd, attendance_type and mode from the Term record
1495 -- Get the Leaving date for the student
1496 l_lev_dt := NULL;
1497 l_lev_dt := NVL(g_en_stdnt_ps_att.course_rqrmnts_complete_dt,g_en_stdnt_ps_att.discontinued_dt) ;
1498
1499 -- If the student is a leaver(i.e leaving date falls within the HESA Submission period)
1500 -- then get the latest term rec where the leaving date falls within the term calendar start and end dates
1501 IF l_lev_dt BETWEEN g_he_submsn_header.enrolment_start_date AND g_he_submsn_header.enrolment_end_date THEN
1502 -- get the latest term record within which the Leaving date falls
1503 c_term1_lev_rec := NULL ;
1504 OPEN c_term1_lev (g_en_stdnt_ps_att.person_id, g_en_stdnt_ps_att.course_cd, l_lev_dt );
1505 FETCH c_term1_lev INTO c_term1_lev_rec ;
1506 IF c_term1_lev%NOTFOUND THEN
1507 -- Get the latest term record just before the Leaving date
1508 c_term2_lev_rec := NULL ;
1509 OPEN c_term2_lev(g_en_stdnt_ps_att.person_id,
1510 g_en_stdnt_ps_att.course_cd,
1511 l_lev_dt,
1512 g_he_submsn_header.enrolment_start_date,
1513 g_he_submsn_header.enrolment_end_date ) ;
1514 FETCH c_term2_lev INTO c_term2_lev_rec ;
1515 IF c_term2_lev%FOUND THEN
1516 -- Override the location_cd,cal_type,version_number,attendance_type,attendance_mode
1517 -- in the SCA record with the term record values
1518 g_en_stdnt_ps_att.version_number := c_term2_lev_rec.program_version ;
1519 g_en_stdnt_ps_att.cal_type := c_term2_lev_rec.acad_cal_type ;
1520 g_en_stdnt_ps_att.location_cd := c_term2_lev_rec.location_cd ;
1521 g_en_stdnt_ps_att.attendance_mode := c_term2_lev_rec.attendance_mode ;
1522 g_en_stdnt_ps_att.attendance_type := c_term2_lev_rec.attendance_type ;
1523 END IF ;
1524 CLOSE c_term2_lev ;
1525 ELSE
1526 -- Override the location_cd,cal_type,version_number,attendance_type,attendance_mode
1527 -- in the SCA record with the term record values
1528 g_en_stdnt_ps_att.version_number := c_term1_lev_rec.program_version ;
1529 g_en_stdnt_ps_att.cal_type := c_term1_lev_rec.acad_cal_type ;
1530 g_en_stdnt_ps_att.location_cd := c_term1_lev_rec.location_cd ;
1531 g_en_stdnt_ps_att.attendance_mode := c_term1_lev_rec.attendance_mode ;
1532 g_en_stdnt_ps_att.attendance_type := c_term1_lev_rec.attendance_type ;
1533 END IF ;
1534 CLOSE c_term1_lev ;
1535
1536 -- Else the student is continuing student then get the latest term rec
1537 -- where the Term start date falls within the HESA Submission start and end dates
1538 ELSE
1539 -- Get the latest term record which falls within the FTE period and term start date > commencement dt
1540 c_term_con_rec := NULL ;
1541 OPEN c_term_con(g_en_stdnt_ps_att.person_id,
1542 g_en_stdnt_ps_att.course_cd,
1543 g_he_submsn_header.enrolment_start_date,
1544 g_he_submsn_header.enrolment_end_date );
1545 FETCH c_term_con INTO c_term_con_rec ;
1546 IF c_term_con%FOUND THEN
1547 -- Override the location_cd,cal_type,version_number,attendance_type,attendance_mode
1548 -- in the SCA record with the term record values
1549 g_en_stdnt_ps_att.version_number := c_term_con_rec.program_version ;
1550 g_en_stdnt_ps_att.cal_type := c_term_con_rec.acad_cal_type ;
1551 g_en_stdnt_ps_att.location_cd := c_term_con_rec.location_cd ;
1552 g_en_stdnt_ps_att.attendance_mode := c_term_con_rec.attendance_mode ;
1553 g_en_stdnt_ps_att.attendance_type := c_term_con_rec.attendance_type ;
1554 END IF ;
1555 CLOSE c_term_con ;
1556 END IF ; -- if student is leaving / continuing
1557
1558 IF NOT validate_params1 (p_extract_run_id)
1559 THEN
1560 -- Exclude this record
1561 l_valid := FALSE;
1562 END IF;
1563
1564 END IF; -- Record is still valid
1565
1566 -- jbaber added this validation for bug 3810280
1567 -- Make sure current SPA is enrolled
1568 IF l_valid THEN
1569 -- if the current SPA is not enrolled, check associated unit attempts
1570 IF NOT g_en_stdnt_ps_att.course_attempt_status = 'ENROLLED' THEN
1571 l_enrolled_su := NULL;
1572 OPEN c_enr_su(g_en_stdnt_ps_att.person_id,
1573 g_en_stdnt_ps_att.course_cd,
1574 g_he_submsn_header.enrolment_end_date) ;
1575 FETCH c_enr_su INTO l_enrolled_su ;
1576 IF c_enr_su%NOTFOUND THEN
1577 l_valid := FALSE;
1578 END IF;
1579 CLOSE c_enr_su;
1580 END IF;
1581 END IF;
1582
1583 -- Do all the checks that can be done using the
1584 -- information got so far.
1585 IF l_valid
1586 THEN
1587
1588 -- Check offset days
1589 IF g_he_submsn_header.offset_days IS NOT NULL
1590 THEN
1591 -- smaddali modified for bug 2394560 , to apply the offset to spa start date instead of the hesa submission start date
1592 IF g_he_submsn_header.apply_to_atmpt_st_dt = 'Y'
1593 AND g_en_stdnt_ps_att.discontinued_dt < (g_en_stdnt_ps_att.commencement_dt + g_he_submsn_header.offset_days)
1594 THEN
1595 -- Exclude this record
1596 l_valid := FALSE;
1597
1598 END IF;
1599 END IF; -- Offset days entered as parameter
1600
1601 END IF; -- Record is still valid
1602
1603 IF l_valid
1604 THEN
1605 -- Check that the course has qualification aim
1606 IF g_he_st_spa.student_qual_aim IS NULL
1607 THEN
1608 l_award_cd := NULL ;
1609 OPEN c_quaim (g_en_stdnt_ps_att.course_cd,
1610 g_en_stdnt_ps_att.version_number);
1611 FETCH c_quaim INTO l_award_cd;
1612 CLOSE c_quaim;
1613
1614 IF l_award_cd IS NULL
1615 THEN
1616 -- Exclude this record
1617 l_valid := FALSE;
1618 END IF;
1619 END IF; -- Qual Aim check
1620
1621 END IF; -- Record is still valid
1622
1623 -- For the next set of checks we need the Year of Program
1624 -- details
1625 IF l_valid
1626 THEN
1627 -- Get Year of Program details
1628 OPEN c_get_yop
1629 (g_en_stdnt_ps_att.person_id,
1630 g_en_stdnt_ps_att.course_cd,
1631 g_he_submsn_header.enrolment_start_date,
1632 g_he_submsn_header.enrolment_end_date,
1633 l_awd_conf_start_dt,
1634 l_awd_conf_end_dt);
1635
1636 FETCH c_get_yop INTO g_as_su_setatmpt.unit_set_cd,
1637 g_as_su_setatmpt.us_version_number,
1638 g_as_su_setatmpt.sequence_number,
1639 g_as_su_setatmpt.selection_dt,
1640 g_as_su_setatmpt.end_dt,
1641 g_as_su_setatmpt.rqrmnts_complete_ind,
1642 g_as_su_setatmpt.rqrmnts_complete_dt,
1643 g_he_en_susa.study_location ,
1644 g_he_en_susa.fte_perc_override,
1645 g_he_en_susa.credit_value_yop1;
1646
1647 IF c_get_yop%NOTFOUND
1648 THEN
1649 -- If Year of Program details were not found, then log error
1650 l_valid := FALSE;
1651
1652 Fnd_Message.Set_Name('IGS', 'IGS_HE_EXT_YOP_NOT_FOUND');
1653 l_message := Fnd_Message.Get;
1654
1655 -- Initialize Record to Null.
1656 g_he_ext_run_except := NULL;
1657
1658 -- Populate the required fields.
1659 g_he_ext_run_except.extract_run_id := p_extract_run_id;
1660 g_he_ext_run_except.exception_reason := l_message;
1661 g_he_ext_run_except.person_id :=
1662 g_en_stdnt_ps_att.person_id;
1663 g_he_ext_run_except.course_cd :=
1664 g_en_stdnt_ps_att.course_cd;
1665 -- smaddali modified this call to pass l_std_inst.version_number instead of g_en_stdnt_ps_att.version_number
1666 -- as part of HECR214 build
1667 g_he_ext_run_except.crv_version_number :=
1668 l_std_inst.version_number;
1669 g_he_ext_run_except.person_number :=
1670 g_pe_person.person_number;
1671
1672 -- Call procedure to log error
1673 log_error (g_he_ext_run_except);
1674
1675 END IF; -- YOP record not found
1676
1677 CLOSE c_get_yop;
1678
1679 END IF; -- Record is still valid
1680
1681 IF l_valid
1682 THEN
1683 -- Get the course details
1684 OPEN c_get_crse
1685 (g_en_stdnt_ps_att.course_cd,
1686 g_en_stdnt_ps_att.version_number,
1687 g_en_stdnt_ps_att.cal_type,
1688 g_en_stdnt_ps_att.attendance_mode,
1689 g_en_stdnt_ps_att.attendance_type,
1690 g_en_stdnt_ps_att.location_cd,
1691 g_as_su_setatmpt.unit_set_cd,
1692 g_as_su_setatmpt.us_version_number );
1693 -- smaddali Modifed cursor to fetch funding_source for hefd208 build , bug#2717751
1694 FETCH c_get_crse INTO
1695 g_ps_ver.title,
1696 g_ps_ver.std_annual_load,
1697 g_ps_ver.contact_hours,
1698 g_ps_ver.govt_special_course_type,
1699 g_ps_ver.responsible_org_unit_cd,
1700 g_he_st_prog.location_of_study ,
1701 g_he_st_prog.return_type,
1702 g_he_st_prog.default_award,
1703 g_he_st_prog.program_calc ,
1704 g_he_st_prog.fe_program_marker,
1705 g_he_poous.location_of_study,
1706 g_he_poous.credit_value_yop1,
1707 g_he_poous.fte_intensity,
1708 g_he_poous.funding_source ;
1709
1710 IF c_get_crse%NOTFOUND
1711 THEN
1712 -- If Course details were not found, then log error
1713 l_valid := FALSE;
1714
1715 Fnd_Message.Set_Name('IGS', 'IGS_HE_EXT_CRSE_DTL_NOT_FOUND');
1716 l_message := Fnd_Message.Get;
1717
1718 -- Initialize Record to Null.
1719 g_he_ext_run_except := NULL;
1720
1721 -- Populate the required fields.
1722 g_he_ext_run_except.extract_run_id := p_extract_run_id;
1723 g_he_ext_run_except.exception_reason := l_message;
1724 g_he_ext_run_except.person_id :=
1725 g_en_stdnt_ps_att.person_id;
1726 g_he_ext_run_except.course_cd :=
1727 g_en_stdnt_ps_att.course_cd;
1728 -- smaddali modified this call to pass l_std_inst.version_number instead of g_en_stdnt_ps_att.version_number
1729 -- as part of HECR214 build
1730 g_he_ext_run_except.crv_version_number :=
1731 l_std_inst.version_number;
1732 g_he_ext_run_except.person_number :=
1733 g_pe_person.person_number;
1734
1735 -- Call procedure to log error
1736 log_error (g_he_ext_run_except);
1737
1738 END IF; -- Crse record not found
1739
1740 CLOSE c_get_crse;
1741
1742 END IF; -- Record is still valid
1743
1744 -- Do the Reduced Return Type Checks..
1745 IF l_valid
1746 THEN
1747 l_return_type := NULL ;
1748 l_return_type := Nvl(Nvl(g_he_st_spa.return_type,
1749 g_he_st_prog.return_type),'0');
1750
1751 IF SUBSTR(g_he_submsn_return.record_id,3,1) = '6'
1752 AND l_return_type <> '6'
1753 THEN
1754 -- User wants only Welsh for adults
1755 -- Exclude this record
1756 l_valid := FALSE;
1757
1758 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '1'
1759 AND l_return_type <> '1'
1760 THEN
1761 -- User wants only Low Credit Bearing Courses
1762 -- Exclude this record
1763 l_valid := FALSE;
1764
1765 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '5'
1766 THEN
1767 -- User wants only Late Return
1768 IF l_return_type = '5'
1769 OR g_en_stdnt_ps_att.course_rqrmnts_complete_dt
1770 BETWEEN g_he_submsn_return.lrr_start_date
1771 AND g_he_submsn_return.lrr_end_date
1772 THEN
1773 -- Nothing, we need this record
1774 NULL;
1775 ELSE
1776 -- Exclude this record
1777 l_valid := FALSE;
1778 END IF;
1779
1780 ELSIF SUBSTR(g_he_submsn_return.record_id,3,1) = '0'
1781 THEN
1782 -- Main Record
1783 IF l_return_type <> '0'
1784 THEN
1785 -- This record will be returned in a reduce return
1786 -- separtely.
1787 -- Since its return_type is initialized to 0
1788 -- only those that the user has specifically marked
1789 -- will get excluded.
1790 -- Exclude this record
1791 l_valid := FALSE;
1792 END IF;
1793 END IF;
1794 END IF; -- Record is still valid
1795
1796 -- validate the program_year,FE,outside_uk,org_unit Extract Run Parameters to check if the
1797 -- record satisfies the criteria
1798 --smaddali modified this call to call validate_params2 instead of validate_params for bug 2350730
1799 IF l_valid
1800 THEN
1801 IF NOT validate_params2 (p_extract_run_id)
1802 THEN
1803 -- Exclude this record
1804 l_valid := FALSE;
1805 END IF;
1806
1807 END IF; -- Record is still valid
1808
1809 -- This spa record has passed all validation checks
1810 -- Therefore it needs to be processed further
1811 -- Insert it into the temporary processing table
1812 IF l_valid
1813 THEN
1814 l_rowid := NULL;
1815 l_ext_interim_id := NULL ;
1816 -- smaddali modified this call to pass l_std_inst.version_number instead of g_en_stdnt_ps_att.version_number
1817 -- as part of HECR214 build
1818 igs_he_ext_run_interim_pkg.insert_row
1819 (X_rowid => l_rowid,
1820 X_ext_interim_id => l_ext_interim_id,
1821 X_extract_run_id => p_extract_run_id,
1822 X_person_id => g_en_stdnt_ps_att.person_id,
1823 X_course_cd => g_en_stdnt_ps_att.course_cd,
1824 X_crv_version_number => l_std_inst.version_number,
1825 X_unit_cd => NULL,
1826 X_uv_version_number => NULL,
1827 X_student_inst_number => g_he_st_spa.student_inst_number,
1828 X_line_number => NULL);
1829
1830 g_records_found := TRUE;
1831
1832 END IF;
1833
1834 END IF ; -- End of Duplicate HSPA record Check
1835
1836 END LOOP; -- For Each Person Id and Student Instance Number
1837
1838 EXCEPTION
1839 WHEN OTHERS
1840 THEN
1841 write_to_log(SQLERRM);
1842
1843 -- Close open cursors
1844 IF c_quaim%ISOPEN
1845 THEN
1846 CLOSE c_quaim;
1847 END IF;
1848
1849 IF c_get_yop%ISOPEN
1850 THEN
1851 CLOSE c_get_yop;
1852 END IF;
1853
1854 IF c_get_crse%ISOPEN
1855 THEN
1856 CLOSE c_get_crse;
1857 END IF;
1858
1859 IF c_alternate_id%ISOPEN
1860 THEN
1861 CLOSE c_alternate_id;
1862 END IF;
1863
1864 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1865 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.get_students');
1866 IGS_GE_MSG_STACK.ADD;
1867 App_Exception.Raise_Exception;
1868
1869 END get_students;
1870
1871 /*----------------------------------------------------------------------
1872 This procedure fetches the modules that need to be processed
1873 and inserts them into the temporary procesing table
1874 Parameters :
1875 p_extract_run_id IN The Extract Run Id
1876
1877 ----------------------------------------------------------------------*/
1878 PROCEDURE get_modules (p_extract_run_id IN NUMBER)
1879 IS
1880
1881 --smaddali modified this cursor to add NVL 'N' to program_calc, also added ckeck that module_id is not null for bug 2425932
1882 CURSOR c_get_mod (p_stdnt_extract_run_id NUMBER)
1883 IS
1884 SELECT DISTINCT Nvl(a.override_value, a.value) module_id
1885 FROM igs_he_ex_rn_dat_fd a,
1886 igs_he_ex_rn_dat_ln b,
1887 igs_he_st_prog c
1888 WHERE a.extract_run_id = b.extract_run_id
1889 AND b.extract_run_id = p_stdnt_extract_run_id
1890 AND b.course_cd = c.course_cd
1891 AND b.crv_version_number = c.version_number
1892 AND NVL(c.program_calc,'N') = 'N'
1893 AND a.field_number BETWEEN 85 AND 100
1894 AND NVL(a.override_value,a.value) IS NOT NULL ;
1895
1896 CURSOR c_get_exclude_flag(cp_unit_cd igs_he_st_unt_vs_all.unit_cd%TYPE,
1897 cp_version_number igs_he_st_unt_vs_all.version_number%TYPE) IS
1898 SELECT NVL(exclude_flag, 'N') exclude_flag
1899 FROM igs_he_st_unt_vs_all
1900 WHERE unit_cd = cp_unit_cd
1901 AND version_number = cp_version_number;
1902
1903 l_exclude igs_he_st_unt_vs_all.exclude_flag%TYPE;
1904 l_he_ext_run_interim igs_he_ext_run_interim%ROWTYPE;
1905 l_rowid VARCHAR2(50);
1906 l_dot_position NUMBER;
1907
1908 BEGIN
1909 -- printing datetimestamp for monitoring performance
1910 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
1911 fnd_message.set_token('PROCEDURE', 'GET_MODULES');
1912 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
1913 fnd_file.put_line(fnd_file.log, fnd_message.get);
1914
1915 fnd_message.set_name('IGS','IGS_HE_MOD_RETURN');
1916 fnd_file.put_line(fnd_file.log,fnd_message.get());
1917
1918 FOR l_mod IN c_get_mod (g_he_ext_run_dtls.student_ext_run_id)
1919 LOOP
1920 g_records_found := TRUE;
1921 l_he_ext_run_interim := NULL ;
1922 l_dot_position := NULL ;
1923 l_rowid := NULL ;
1924
1925 -- Extract the Unit Cd and Version Number
1926 -- Module Id would be in format 'UNITABC.1'
1927 l_dot_position := INSTR(l_mod.module_id,'.') ;
1928 IF l_dot_position > 0
1929 THEN
1930 l_he_ext_run_interim.unit_cd := SUBSTR(l_mod.module_id, 1 ,
1931 l_dot_position - 1);
1932 l_he_ext_run_interim.uv_version_number := SUBSTR(l_mod.module_id,
1933 l_dot_position + 1);
1934 ELSE
1935 l_he_ext_run_interim.unit_cd := l_mod.module_id;
1936 l_he_ext_run_interim.uv_version_number := 1;
1937 END IF;
1938
1939 OPEN c_get_exclude_flag(l_he_ext_run_interim.unit_cd, l_he_ext_run_interim.uv_version_number);
1940 FETCH c_get_exclude_flag INTO l_exclude;
1941 CLOSE c_get_exclude_flag;
1942
1943 IF l_exclude = 'N' THEN
1944
1945 l_he_ext_run_interim.extract_run_id := p_extract_run_id;
1946 l_he_ext_run_interim.line_number := NULL;
1947
1948 igs_he_ext_run_interim_pkg.insert_row
1949 (X_rowid => l_rowid,
1950 X_ext_interim_id => l_he_ext_run_interim.ext_interim_id,
1951 X_extract_run_id => l_he_ext_run_interim.extract_run_id,
1952 X_person_id => NULL,
1953 X_course_cd => NULL,
1954 X_crv_version_number => NULL,
1955 X_unit_cd => l_he_ext_run_interim.unit_cd,
1956 X_uv_version_number => l_he_ext_run_interim.uv_version_number,
1957 X_student_inst_number => NULL,
1958 X_line_number => l_he_ext_run_interim.line_number);
1959
1960 END IF;
1961
1962 END LOOP; -- c_get_mod
1963
1964 EXCEPTION
1965 WHEN OTHERS
1966 THEN
1967 write_to_log(SQLERRM);
1968 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1969 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.get_modules');
1970 IGS_GE_MSG_STACK.ADD;
1971 App_Exception.Raise_Exception;
1972
1973 END get_modules;
1974
1975
1976
1977 --smaddali created this procedure for validating dlhe record status , HEFD203 build, bug#2717745
1978 -- smaddali 16-oct-03 Modified the processing of dlhe_status parameters as part of bug#3166126
1979 FUNCTION validate_dlhe_status (p_extract_run_id IN NUMBER,
1980 p_dlhe_record_status igs_he_stdnt_dlhe.dlhe_record_status%TYPE,
1981 p_popdlhe_flag igs_he_stdnt_dlhe.popdlhe_flag%TYPE)
1982 RETURN BOOLEAN
1983 IS
1984 /***************************************************************
1985 Created By : smaddali
1986 Date Created By : 9-apr-03
1987 Purpose : This procedure validates dlhe record status
1988 Known Limitations,Enhancements or Remarks:
1989 Change History :
1990 Who When What
1991 ***************************************************************/
1992
1993 -- Get all the DLHE parameters setup for this extract run id
1994 CURSOR c_dlhe_prm IS
1995 SELECT param_type,
1996 exclude,
1997 only
1998 FROM igs_he_ext_run_prms
1999 WHERE extract_run_id = p_extract_run_id
2000 AND (exclude IS NOT NULL
2001 OR only IS NOT NULL)
2002 AND param_type IN ('DLHE','POPDLHE');
2003
2004 -- smaddali added these parameters for bug#3166126
2005 l_only_exists BOOLEAN;
2006 l_only_matches BOOLEAN ;
2007
2008 BEGIN
2009 -- smaddali added initialisation of variables for bug#3166126
2010 l_only_exists := FALSE;
2011 l_only_matches := FALSE ;
2012
2013 FOR l_dlhe_prm IN c_dlhe_prm
2014 LOOP
2015
2016 IF l_dlhe_prm.param_type = 'DLHE' THEN
2017
2018 IF l_dlhe_prm.exclude IS NOT NULL AND p_dlhe_record_status = l_dlhe_prm.exclude
2019 THEN
2020 -- User does not want this dlhe_record_status
2021 RETURN FALSE;
2022
2023 -- smaddali modified logic for bug#3166126
2024 ELSIF l_dlhe_prm.only IS NOT NULL THEN
2025 -- if atleast 1 only parameter has been setup then set the respective flag
2026 l_only_exists := TRUE;
2027 IF p_dlhe_record_status = l_dlhe_prm.only THEN
2028 -- If current person's dlhe record status is equal to one of the
2029 -- Only parameters then this person should be included in the return
2030 l_only_matches := TRUE;
2031 END IF ;
2032 END IF;
2033
2034 ELSIF l_dlhe_prm.param_type = 'POPDLHE' THEN
2035
2036 IF l_dlhe_prm.exclude IS NOT NULL
2037 AND UPPER(SUBSTR(l_dlhe_prm.exclude,1,1)) = UPPER(p_popdlhe_flag) THEN
2038 -- user does not want this record
2039 RETURN FALSE;
2040
2041 ELSIF l_dlhe_prm.only IS NOT NULL
2042 AND UPPER(SUBSTR(l_dlhe_prm.only,1,1)) <> UPPER(p_popdlhe_flag) THEN
2043 -- user does not want this record
2044 RETURN FALSE;
2045
2046 END IF;
2047
2048 END IF; -- parameter type
2049
2050
2051 END LOOP;
2052
2053 -- All ok, pass back TRUE
2054 -- If only parameters were setup but the current person's dlhe record status doesnot match any of them
2055 -- then exclude this person else include this person in this return
2056 IF l_only_exists AND NOT l_only_matches THEN
2057 RETURN FALSE;
2058 ELSE
2059 RETURN TRUE;
2060 END IF;
2061
2062 EXCEPTION
2063 WHEN OTHERS
2064 THEN
2065 write_to_log(SQLERRM);
2066
2067 -- Close Cursors
2068 IF c_dlhe_prm%ISOPEN
2069 THEN
2070 CLOSE c_dlhe_prm;
2071 END IF;
2072
2073 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2074 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.validate_dlhe_status');
2075 IGS_GE_MSG_STACK.ADD;
2076 App_Exception.Raise_Exception;
2077 END validate_dlhe_status;
2078
2079
2080
2081 PROCEDURE get_dlhe (p_extract_run_id IN NUMBER, p_recalculate IN BOOLEAN)
2082 IS
2083 /***************************************************************
2084 Created By : smaddali
2085 Date Created By : 9-apr-03
2086 Purpose : This procedure fetches the dlhe records that need to be processed
2087 and inserts them into the temporary procesing table
2088 Known Limitations,Enhancements or Remarks:
2089 Change History :
2090 Who When What
2091 jchakrab 20-Feb-2006 Modified for 4256498 - removed ORDER BY from c_get_dlhe query
2092 jbaber 15-Mar-2006 Added p_recalculate_flag for HE365 - Extract Rerun
2093 ***************************************************************/
2094
2095 -- Get all the dlhe records belonging to all the qualifying periods in this submission return
2096 CURSOR c_get_dlhe ( p_submission_name igs_he_submsn_header.submission_name%TYPE,
2097 p_return_name igs_he_submsn_return.return_name%TYPE,
2098 p_user_return_subclass igs_he_submsn_return.user_return_subclass%TYPE )
2099 IS
2100 SELECT dlhe.person_id , dlhe.dlhe_record_status, dlhe.popdlhe_flag
2101 FROM igs_he_stdnt_dlhe dlhe,
2102 igs_he_sub_rtn_qual qual
2103 WHERE qual.submission_name = dlhe.submission_name
2104 AND qual.return_name = dlhe.return_name
2105 AND qual.user_return_subclass = dlhe.user_return_subclass
2106 AND qual.qual_period_code = dlhe.qual_period_code
2107 AND qual.submission_name = p_submission_name
2108 AND qual.return_name = p_return_name
2109 AND qual.user_return_subclass = p_user_return_subclass
2110 AND qual.closed_ind = 'N';
2111
2112 l_rowid VARCHAR2(50);
2113 l_ext_interim_id NUMBER;
2114
2115 BEGIN
2116 -- printing datetimestamp for monitoring performance
2117 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
2118 fnd_message.set_token('PROCEDURE', 'GET_DLHE');
2119 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2120 fnd_file.put_line(fnd_file.log, fnd_message.get);
2121
2122 fnd_message.set_name('IGS','IGS_HE_DLHE_RETURN');
2123 fnd_file.put_line(fnd_file.log,fnd_message.get());
2124
2125 -- loop thru each dlhe record and validate dlhe_record_status
2126 FOR g_he_stdnt_dlhe IN c_get_dlhe ( g_he_ext_run_dtls.submission_name, g_he_ext_run_dtls.return_name,
2127 g_he_ext_run_dtls.user_return_subclass )
2128 LOOP
2129 g_pe_person.person_id := g_he_stdnt_dlhe.person_id ;
2130 -- if dlhe_record_status is valid and person_id and person_id_group paramaters are validated
2131 -- then create an interim record
2132 IF validate_dlhe_status( p_extract_run_id,g_he_stdnt_dlhe.dlhe_record_status, g_he_stdnt_dlhe.popdlhe_flag)
2133 AND validate_params( p_extract_run_id )
2134 -- jbaber added validation for recalculated extracts for HE365
2135 AND (NOT p_recalculate OR (p_recalculate AND validate_recalc_params(p_extract_run_id, g_he_stdnt_dlhe.person_id , NULL)))
2136 THEN
2137
2138 l_ext_interim_id := NULL;
2139 l_rowid := NULL ;
2140 igs_he_ext_run_interim_pkg.insert_row
2141 (X_rowid => l_rowid,
2142 X_ext_interim_id => l_ext_interim_id ,
2143 X_extract_run_id => p_extract_run_id ,
2144 X_person_id => g_he_stdnt_dlhe.person_id,
2145 X_course_cd => NULL,
2146 X_crv_version_number => NULL,
2147 X_unit_cd => NULL,
2148 X_uv_version_number => NULL,
2149 X_student_inst_number => NULL,
2150 X_line_number => NULL);
2151 g_records_found := TRUE;
2152
2153 END IF;
2154
2155 END LOOP; -- c_get_dlhe
2156
2157 EXCEPTION
2158 WHEN OTHERS
2159 THEN
2160 write_to_log(SQLERRM);
2161 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2162 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.get_dlhe');
2163 IGS_GE_MSG_STACK.ADD;
2164 App_Exception.Raise_Exception;
2165
2166 END get_dlhe;
2167
2168
2169
2170 /*----------------------------------------------------------------------
2171 This procedure processes the records that have been processed
2172 before but have been marked as requiring recalculation
2173
2174 Parameters :
2175 p_extract_run_id IN The Extract Run Id
2176 ----------------------------------------------------------------------*/
2177 PROCEDURE get_marked_rows
2178 (p_extract_run_id IN igs_he_ext_run_dtls.extract_run_id%TYPE)
2179
2180 IS
2181 /***************************************************************
2182 Created By : Bidisha S
2183 Date Created By : 28-Jan-02
2184 Purpose : This procedure processes the records that have been processed
2185 before but have been marked as requiring recalculation
2186 Known Limitations,Enhancements or Remarks:
2187 Change History :
2188 Who When What
2189 smaddali 09-Apr-03 Modified for adding dlhe processing , HEFD203 build , bug#2717745
2190 jbaber 15-Mar-05 Do NOT delete recalculated fields for HE365
2191 ***************************************************************/
2192
2193 CURSOR c_recalc_criteria IS
2194 SELECT 'X'
2195 FROM igs_he_ext_run_prms
2196 WHERE extract_run_id = p_extract_run_id
2197 AND param_type IN ('RECALC-PERSON', 'RECALC-PROGRAM');
2198
2199 CURSOR c_mrk_row IS
2200 SELECT record_id,
2201 line_number,
2202 person_id,
2203 course_cd,
2204 manually_inserted,
2205 exclude_from_file,
2206 student_inst_number,
2207 crv_version_number,
2208 unit_cd,
2209 uv_version_number,
2210 recalculate_flag
2211 FROM igs_he_ex_rn_dat_ln
2212 WHERE extract_run_id = p_extract_run_id
2213 AND manually_inserted = 'N'
2214 AND (recalculate_flag = 'Y'
2215 OR person_id IN (SELECT only from igs_he_ext_run_prms WHERE param_type = 'RECALC-PERSON' AND extract_run_id = p_extract_run_id)
2216 OR course_cd IN (SELECT only from igs_he_ext_run_prms WHERE param_type = 'RECALC-PROGRAM'AND extract_run_id = p_extract_run_id));
2217
2218 l_he_ext_run_interim igs_he_ext_run_interim%ROWTYPE;
2219 l_rowid VARCHAR2(50);
2220 l_temp VARCHAR2(3);
2221
2222 BEGIN
2223 -- printing datetimestamp for monitoring performance
2224 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
2225 fnd_message.set_token('PROCEDURE', 'GET_MARKED_ROWS');
2226 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2227 fnd_file.put_line(fnd_file.log, fnd_message.get);
2228
2229 fnd_message.set_name('IGS','IGS_HE_REC_RECAL');
2230 fnd_file.put_line(fnd_file.log,fnd_message.get());
2231
2232
2233 -- Delete from igs_he_ext_run_interim, if rows exist
2234 -- Replaced the procedure call which calls the TBH for deletion with direct DML for bug,3179585
2235 DELETE FROM igs_he_ext_run_interim WHERE extract_run_id = p_extract_run_id;
2236
2237
2238 FOR l_c_mrk_row IN c_mrk_row
2239 LOOP
2240 g_records_found := TRUE;
2241 l_he_ext_run_interim := NULL;
2242
2243 -- Insert into temporary table to be processed
2244 -- later
2245 IF Substr(g_he_submsn_return.record_id,4,2) = '11'
2246 OR Substr(g_he_submsn_return.record_id,4,2) = '12'
2247 THEN
2248 -- Student or Combined Return
2249 l_he_ext_run_interim.person_id := l_c_mrk_row.person_id;
2250 l_he_ext_run_interim.course_cd := l_c_mrk_row.course_cd;
2251 l_he_ext_run_interim.crv_version_number := l_c_mrk_row.crv_version_number;
2252 l_he_ext_run_interim.student_inst_number := l_c_mrk_row.student_inst_number ;
2253
2254 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '13' THEN
2255 -- Module Return
2256 l_he_ext_run_interim.unit_cd := l_c_mrk_row.unit_cd;
2257 l_he_ext_run_interim.uv_version_number := l_c_mrk_row.uv_version_number;
2258 -- smaddali added code for dlhe return , build HEFD203 bug#2717745
2259 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '18' THEN
2260 -- dlhe Return
2261 l_he_ext_run_interim.person_id := l_c_mrk_row.person_id;
2262 END IF;
2263
2264 l_he_ext_run_interim.extract_run_id := p_extract_run_id;
2265 l_he_ext_run_interim.line_number := l_c_mrk_row.line_number;
2266
2267 igs_he_ext_run_interim_pkg.insert_row
2268 (X_rowid => l_rowid,
2269 X_ext_interim_id => l_he_ext_run_interim.ext_interim_id,
2270 X_extract_run_id => l_he_ext_run_interim.extract_run_id,
2271 X_person_id => l_he_ext_run_interim.person_id,
2272 X_course_cd => l_he_ext_run_interim.course_cd,
2273 X_crv_version_number => l_he_ext_run_interim.crv_version_number,
2274 X_unit_cd => l_he_ext_run_interim.unit_cd,
2275 X_uv_version_number => l_he_ext_run_interim.uv_version_number,
2276 X_student_inst_number => l_he_ext_run_interim.student_inst_number,
2277 X_line_number => l_he_ext_run_interim.line_number);
2278
2279 END LOOP;
2280
2281 -- Check if person or prorgram criteria.
2282 OPEN c_recalc_criteria;
2283 FETCH c_recalc_criteria INTO l_temp;
2284 CLOSE c_recalc_criteria;
2285
2286 -- If criteria does exist for this return,
2287 -- then append lines as appropriate to the return.
2288 IF l_temp IS NOT NULL THEN
2289
2290 -- Call the appropriate function (get_students or get_dlhe)
2291 IF Substr(g_he_submsn_return.record_id,4,2) IN ('11', '12') THEN
2292 get_students(p_extract_run_id, TRUE);
2293 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '18' THEN
2294 get_dlhe(p_extract_run_id, TRUE);
2295 END IF;
2296
2297 -- Delete any appended lines that are already marked for recalculation
2298 DELETE FROM igs_he_ext_run_interim
2299 WHERE ext_interim_id IN
2300 (SELECT MAX(ext_interim_id)
2301 FROM igs_he_ext_run_interim a
2302 GROUP BY extract_run_id, person_id, course_cd, crv_version_number, unit_cd, uv_version_number, student_inst_number
2303 HAVING COUNT(ext_interim_id) > 1)
2304 AND line_number IS NULL;
2305
2306 END IF;
2307
2308
2309 EXCEPTION
2310 WHEN OTHERS
2311 THEN
2312 write_to_log(SQLERRM);
2313 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
2314 Fnd_Message.Set_Token('NAME','IGS_HE_EXTRACT_PKG.get_marked_rows');
2315 IGS_GE_MSG_STACK.ADD;
2316 App_Exception.Raise_Exception;
2317
2318 END get_marked_rows;
2319
2320
2321 /*----------------------------------------------------------------------
2322 This procedure processes the records that have been inserted into
2323 the temporary run table.
2324 For each student / module, it will derive each of the fields and insert
2325 the rows into the extarct run data tables.
2326
2327 Parameters :
2328 p_extract_run_id IN The Extract Run Id
2329 p_module_called_from IN Module this process was called from
2330 Values : 'IGSHE007' and 'IGSHE008'
2331 p_new_run_flag IN Indicates whether this is a fresh run
2332 Values : 'Y', 'N'
2333 retcode OUT NOCOPY Return status of the concurrent program
2334 Values : 0 - Success
2335 1 - Warning
2336 2 - Error
2337 errbuf OUT NOCOPY Error Buffer
2338 ----------------------------------------------------------------------*/
2339 PROCEDURE extract_main
2340 (errbuf IN OUT NOCOPY VARCHAR2,
2341 retcode IN OUT NOCOPY NUMBER,
2342 p_extract_run_id IN igs_he_ext_run_dtls.extract_run_id%TYPE,
2343 p_module_called_from IN VARCHAR2,
2344 p_new_run_flag IN VARCHAR2)
2345 IS
2346 /***************************************************************
2347 Created By : Bidisha S
2348 Date Created By : 28-Jan-02
2349 Purpose : This procedure processes the records that have been inserted into
2350 the temporary run table. For each student / module, it will derive each of the fields and insert
2351 the rows into the extarct run data tables.
2352 Known Limitations,Enhancements or Remarks:
2353 Change History :
2354 Who When What
2355 smaddali 9-apr-03 modified for adding dlhe processing , HEFD203 build , bug#2717745
2356 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
2357 ***************************************************************/
2358
2359 CURSOR c_ext_dtl IS
2360 SELECT a.rowid,
2361 a.submission_name,
2362 a.user_return_subclass,
2363 a.return_name ,
2364 a.extract_phase,
2365 a.student_ext_run_id,
2366 a.conc_request_id,
2367 a.conc_request_status,
2368 a.extract_run_date,
2369 a.file_name ,
2370 a.file_location ,
2371 a.date_file_sent ,
2372 a.extract_override,
2373 a.validation_kit_result,
2374 a.hesa_validation_result ,
2375 b.lrr_start_date,
2376 b.lrr_end_date,
2377 b.record_id,
2378 c.enrolment_start_date,
2379 c.enrolment_end_date,
2380 c.offset_days ,
2381 c.validation_country ,
2382 Nvl(c.apply_to_atmpt_st_dt,'N') apply_to_atmpt_st_dt,
2383 Nvl(c.apply_to_inst_st_dt,'N') apply_to_inst_st_dt
2384 FROM igs_he_ext_run_dtls a,
2385 igs_he_submsn_return b,
2386 igs_he_submsn_header c
2387 WHERE a.extract_run_id = p_extract_run_id
2388 AND a.submission_name = b.submission_name
2389 AND a.return_name = b.return_name
2390 AND a.User_Return_Subclass = b.user_return_subclass
2391 AND a.submission_name = c.submission_name;
2392
2393 -- Changed the cursor to COUNT(*) with 1 for bug, 3179585
2394 CURSOR c_interim_cnt IS
2395 SELECT 1
2396 FROM igs_he_ext_run_interim
2397 WHERE extract_run_id = p_extract_run_id;
2398
2399 l_message VARCHAR2(2000);
2400 l_msg_code VARCHAR2(30);
2401 l_ext_run_dtl_rowid VARCHAR2(50);
2402 l_request_id NUMBER;
2403 l_count NUMBER := 0;
2404
2405 IGS_HESA_NOT_ENABLED_EXCEP EXCEPTION;
2406
2407 BEGIN
2408
2409 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
2410 IGS_GE_GEN_003.SET_ORG_ID;
2411
2412 -- printing datetimestamp for monitoring performance
2413 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
2414 fnd_message.set_token('PROCEDURE', 'EXTRACT_MAIN');
2415 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2416 fnd_file.put_line(fnd_file.log, fnd_message.get);
2417
2418 -- Check if UCAS and HESA are enabled, ie country = UK
2419 IF NOT Igs_Uc_Utils.is_ucas_hesa_enabled
2420 THEN
2421
2422 RAISE IGS_HESA_NOT_ENABLED_EXCEP;
2423
2424 END IF;
2425
2426 -- smaddali added initialisations
2427 g_he_ext_run_dtls := NULL;
2428 g_he_submsn_return := NULL;
2429 g_he_submsn_header := NULL;
2430
2431 -- Get the HESA Extract Details
2432 l_ext_run_dtl_rowid := NULL ;
2433 OPEN c_ext_dtl;
2434 FETCH c_ext_dtl INTO l_ext_run_dtl_rowid,
2435 g_he_ext_run_dtls.submission_name,
2436 g_he_ext_run_dtls.user_return_subclass,
2437 g_he_ext_run_dtls.return_name ,
2438 g_he_ext_run_dtls.extract_phase,
2439 g_he_ext_run_dtls.student_ext_run_id,
2440 g_he_ext_run_dtls.conc_request_id,
2441 g_he_ext_run_dtls.conc_request_status,
2442 g_he_ext_run_dtls.extract_run_date,
2443 g_he_ext_run_dtls.file_name ,
2444 g_he_ext_run_dtls.file_location ,
2445 g_he_ext_run_dtls.date_file_sent ,
2446 g_he_ext_run_dtls.extract_override,
2447 g_he_ext_run_dtls.validation_kit_result,
2448 g_he_ext_run_dtls.hesa_validation_result ,
2449 g_he_submsn_return.lrr_start_date,
2450 g_he_submsn_return.lrr_end_date,
2451 g_he_submsn_return.record_id,
2452 g_he_submsn_header.enrolment_start_date,
2453 g_he_submsn_header.enrolment_end_date,
2454 g_he_submsn_header.offset_days ,
2455 g_he_submsn_header.validation_country ,
2456 g_he_submsn_header.apply_to_atmpt_st_dt,
2457 g_he_submsn_header.apply_to_inst_st_dt;
2458 IF c_ext_dtl%NOTFOUND
2459 THEN
2460 CLOSE c_ext_dtl;
2461 l_message := NULL ;
2462 Fnd_Message.Set_Name('IGS', 'IGS_HE_EXT_DTL_NOT_FOUND');
2463 l_message := Fnd_Message.Get;
2464
2465 -- Initialize Record to Null.
2466 g_he_ext_run_except := NULL;
2467
2468 -- Populate the required fields.
2469 g_he_ext_run_except.extract_run_id := p_extract_run_id;
2470 g_he_ext_run_except.exception_reason := l_message;
2471
2472 -- Call procedure to log error
2473 log_error (g_he_ext_run_except);
2474 App_Exception.Raise_Exception;
2475
2476 END IF;
2477
2478 CLOSE c_ext_dtl;
2479
2480
2481 fnd_message.set_name('IGS','IGS_HE_PROC_SUBM');
2482 fnd_message.set_token('submission_name',g_he_ext_run_dtls.submission_name);
2483 fnd_message.set_token('user_return_subclass',g_he_ext_run_dtls.user_return_subclass);
2484 fnd_message.set_token('return_name',g_he_ext_run_dtls.return_name);
2485 fnd_message.set_token('enrolment_start_date',g_he_submsn_header.enrolment_start_date);
2486 fnd_message.set_token('enrolment_end_date',g_he_submsn_header.enrolment_end_date);
2487 fnd_file.put_line(fnd_file.log,fnd_message.get());
2488
2489
2490 IF p_module_called_from = 'IGSHE008'
2491 THEN
2492 -- Called from 'Maintain Extract'
2493 -- Need to process only those rows which are
2494 -- marked as requiring recalculation
2495 get_marked_rows (p_extract_run_id);
2496
2497 -- Delete marked rows exceptions for bug 3166186
2498 DELETE FROM igs_he_ext_run_excp excp
2499 WHERE excp.extract_run_id = p_extract_run_id
2500 AND excp.line_number IN
2501 (SELECT line_number
2502 FROM igs_he_ext_run_interim
2503 WHERE extract_run_id = excp.extract_run_id);
2504
2505
2506 ELSIF p_module_called_from = 'IGSHE007'
2507 THEN
2508 -- Called from 'Define Extract'
2509 -- Check if earlier process needs to be restarted or
2510 -- if its a new run
2511 IF p_new_run_flag = 'Y'
2512 THEN
2513 -- Do a fresh run.
2514 -- Therefore, delete all data that was created previously
2515 -- for the same run.
2516 -- Delete from igs_he_ext_run_interim
2517 fnd_message.set_name('IGS','IGS_HE_DELETE_REC');
2518 fnd_file.put_line(fnd_file.log,fnd_message.get());
2519
2520 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
2521 fnd_message.set_token('PROCEDURE', 'START_DELETE_ROWS');
2522 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2523 fnd_file.put_line(fnd_file.log, fnd_message.get);
2524
2525 -- Changed the logic to replace the call to procedure, delete_rows with the
2526 -- Direct DMLs to improve performance for bug,3179585
2527
2528 -- Delete from igs_he_ext_run_interim
2529 DELETE FROM igs_he_ext_run_interim WHERE extract_run_id = p_extract_run_id;
2530
2531 -- Delete from igs_he_ex_rn_dat_fd
2532 DELETE FROM igs_he_ex_rn_dat_fd WHERE extract_run_id = p_extract_run_id;
2533
2534 -- Delete from igs_he_ex_rn_dat_ln
2535 DELETE FROM igs_he_ex_rn_dat_ln WHERE extract_run_id = p_extract_run_id;
2536
2537 -- Delete from igs_he_ext_run_excp
2538 DELETE FROM igs_he_ext_run_excp WHERE extract_run_id = p_extract_run_id;
2539
2540 fnd_message.set_name('IGS','IGS_HE_ST_PROC_TIME');
2541 fnd_message.set_token('PROCEDURE', 'END_DELETE_ROWS');
2542 fnd_message.set_token('TIMESTAMP',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2543 fnd_file.put_line(fnd_file.log, fnd_message.get);
2544
2545 IF Substr(g_he_submsn_return.record_id,4,2) = '11'
2546 OR Substr(g_he_submsn_return.record_id,4,2) = '12'
2547 THEN
2548 -- Student or Combined Return
2549 get_students (p_extract_run_id, FALSE);
2550 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '13' THEN
2551 -- Module Return
2552 get_modules (p_extract_run_id);
2553 -- smaddali added processing for DLHE return as part of HEFD203 build , bug#2717745
2554 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '18' THEN
2555 -- DLHE Return
2556 get_dlhe(p_extract_run_id, FALSE);
2557 END IF;
2558
2559 ELSE
2560 -- Restart from where the process
2561 -- stopped last time.i.e process
2562 -- unprocessed rows.
2563 -- Check if there are rows to process.
2564 l_count := 0;
2565 OPEN c_interim_cnt ;
2566 FETCH c_interim_cnt INTO l_count ;
2567 CLOSE c_interim_cnt;
2568 IF l_count > 0
2569 THEN
2570 g_records_found := TRUE;
2571 END IF;
2572 END IF; -- check new_run_flag
2573
2574 ELSE
2575 -- Unknown p_module_called_from
2576 Fnd_Message.Set_Name('IGS','IGS_HE_EXT_INV_MOD');
2577 IGS_GE_MSG_STACK.ADD;
2578
2579 l_message := Fnd_message.Get_string('IGS','IGS_HE_EXT_INV_MOD');
2580 write_to_log (l_message);
2581
2582 App_Exception.Raise_Exception;
2583 END IF; -- Module called from check
2584
2585 -- Commit all the rows inserted into the Interim run table
2586 COMMIT;
2587
2588 IF g_records_found
2589 THEN
2590 fnd_message.set_name('IGS','IGS_HE_VALID_STUD');
2591 fnd_file.put_line(fnd_file.log,fnd_message.get()|| ' - ' ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2592
2593 fnd_message.set_name('IGS','IGS_HE_TEMP_REC');
2594 fnd_file.put_line(fnd_file.log,fnd_message.get());
2595
2596 -- If records were found that need processing,
2597 -- call the next procedure to process these
2598 -- Transaction handling is done within this procedure
2599 igs_he_extract2_pkg.process_temp_table
2600 (p_extract_run_id => p_extract_run_id,
2601 p_module_called_from => p_module_called_from,
2602 p_new_run_flag => p_new_run_flag);
2603 ELSE
2604 IF Substr(g_he_submsn_return.record_id,4,2) = '11'
2605 OR Substr(g_he_submsn_return.record_id,4,2) = '12'
2606 THEN
2607 Fnd_Message.Set_Name('IGS','IGS_HE_EXT_SPA_NOT_FOUND');
2608 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '13' THEN
2609 Fnd_Message.Set_Name('IGS','IGS_HE_EXT_MOD_NOT_FOUND');
2610 Fnd_Message.Set_Token('ST_RUN_ID',g_he_ext_run_dtls.student_ext_run_id,
2611 TRUE);
2612 -- smaddali added processing for DLHE return as part of HEFD203 build , bug#2717745
2613 ELSIF Substr(g_he_submsn_return.record_id,4,2) = '18' THEN
2614 Fnd_Message.Set_Name('IGS','IGS_HE_EXT_DLHE_NOT_FOUND');
2615 END IF;
2616
2617 IGS_GE_MSG_STACK.ADD;
2618
2619 l_message := Fnd_message.Get;
2620 write_to_log (l_message);
2621
2622 END IF; -- records found check
2623
2624 -- Mark Process as completed
2625 -- smaddali populating conc_request_id and date for bug 2483376
2626 igs_he_ext_run_dtls_pkg.update_row
2627 (X_rowid => l_ext_run_dtl_rowid,
2628 X_extract_run_id => p_extract_run_id,
2629 X_submission_name => g_he_ext_run_dtls.submission_name,
2630 X_user_return_subclass => g_he_ext_run_dtls.user_return_subclass,
2631 X_return_name => g_he_ext_run_dtls.return_name,
2632 X_extract_phase => g_he_ext_run_dtls.extract_phase ,
2633 X_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
2634 X_conc_request_status => 'COMPLETE',
2635 X_extract_run_date => TRUNC(SYSDATE),
2636 X_file_name => g_he_ext_run_dtls.file_name ,
2637 X_file_location => g_he_ext_run_dtls.file_location,
2638 X_date_file_sent => g_he_ext_run_dtls.date_file_sent,
2639 X_extract_override => g_he_ext_run_dtls.extract_override,
2640 X_validation_kit_result => g_he_ext_run_dtls.validation_kit_result,
2641 X_hesa_validation_result => g_he_ext_run_dtls.hesa_validation_result,
2642 X_student_ext_run_id => g_he_ext_run_dtls.student_ext_run_id );
2643
2644 -- Commit Transaction
2645 COMMIT;
2646
2647 fnd_message.set_name('IGS','IGS_HE_PROC_COMP');
2648 fnd_file.put_line(fnd_file.log,fnd_message.get()|| ' - ' ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2649
2650 -- Submit the Extract Run Exception report.
2651 l_request_id := NULL ;
2652 l_request_id := Fnd_Request.Submit_Request
2653 ( 'IGS',
2654 'IGSHES01',
2655 'Extract Run Exception Report',
2656 NULL,
2657 FALSE,
2658 p_extract_run_id,
2659 'LINE');
2660
2661
2662 fnd_message.set_name('IGS','IGS_HE_REP_SUBM');
2663 fnd_file.put_line(fnd_file.log,fnd_message.get()|| ' - ' ||TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
2664
2665 EXCEPTION
2666 WHEN IGS_HESA_NOT_ENABLED_EXCEP
2667 THEN
2668 Errbuf := Fnd_message.Get_string('IGS','IGS_UC_HE_NOT_ENABLED');
2669 retcode := 2;
2670 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2671
2672 WHEN OTHERS
2673 THEN
2674 write_to_log(SQLERRM);
2675 errbuf := SQLERRM;
2676 retcode := 2;
2677 ROLLBACK;
2678
2679 -- Close Cursors
2680 IF c_ext_dtl%ISOPEN
2681 THEN
2682 CLOSE c_ext_dtl;
2683 END IF;
2684
2685 -- Mark Process as Errored
2686 -- smaddali populating conc_request_id and date for bug 2483376
2687 igs_he_ext_run_dtls_pkg.update_row
2688 (X_rowid => l_ext_run_dtl_rowid,
2689 X_extract_run_id => p_extract_run_id,
2690 X_submission_name => g_he_ext_run_dtls.submission_name,
2691 X_user_return_subclass => g_he_ext_run_dtls.user_return_subclass,
2692 X_return_name => g_he_ext_run_dtls.return_name,
2693 X_extract_phase => g_he_ext_run_dtls.extract_phase ,
2694 X_conc_request_id => FND_GLOBAL.CONC_REQUEST_ID,
2695 X_conc_request_status => 'ERROR',
2696 X_extract_run_date => TRUNC(SYSDATE),
2697 X_file_name => g_he_ext_run_dtls.file_name ,
2698 X_file_location => g_he_ext_run_dtls.file_location,
2699 X_date_file_sent => g_he_ext_run_dtls.date_file_sent,
2700 X_extract_override => g_he_ext_run_dtls.extract_override,
2701 X_validation_kit_result => g_he_ext_run_dtls.validation_kit_result,
2702 X_hesa_validation_result => g_he_ext_run_dtls.hesa_validation_result,
2703 X_student_ext_run_id => g_he_ext_run_dtls.student_ext_run_id );
2704
2705 -- Commit Transaction
2706 COMMIT;
2707
2708 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
2709
2710 END extract_main;
2711
2712 END IGS_HE_EXTRACT_PKG;
[Home] [Help]