1 PACKAGE BODY igs_he_verify_return_pkg AS
2 /* $Header: IGSHE27B.pls 120.3 2006/06/21 03:00:01 jchin noship $*/
3
4
5 -- Global variables
6 g_he_submsn_return igs_he_submsn_return%ROWTYPE;
7 g_he_submsn_header igs_he_submsn_header%ROWTYPE;
8
9
10
11 PROCEDURE check_associations(p_user_return_subclass IN VARCHAR2) IS
12 /******************************************************************
13 Created By : Jonathan Baber
14 Date Created By : 23-Nov-05
15 Purpose : Checks field associations for given extract.
16 Makes sure every OSS code has corresponding HESA code.
17 Known limitations,enhancements,remarks:
18 Change History
19 Who When What
20 *******************************************************************/
21
22
23 TYPE cur_unmapped IS REF CURSOR;
24 l_unmapped cur_unmapped;
25
26 -- Gets all distinct association codes used in given extract
27 -- ordered by the first field they appear in
28 CURSOR c_assoc IS
29 SELECT MIN(ass.field_number) field, ass.association_code, ass.oss_seq, ass.hesa_seq
30 FROM igs_he_usr_rtn_clas urc,
31 igs_he_usr_rt_cl_fld fld,
32 igs_he_sys_rt_cl_ass ass
33 WHERE urc.user_return_subclass = p_user_return_subclass
34 AND fld.user_return_subclass = urc.user_return_subclass
35 AND ass.system_return_class_type = urc.system_return_class_type
36 AND fld.field_number = ass.field_number
37 AND fld.include_flag = 'Y'
38 AND ass.oss_seq IS NOT NULL
39 AND ass.hesa_seq IS NOT NULL
40 GROUP BY ass.association_code, ass.oss_seq, ass.hesa_seq
41 ORDER BY field;
42
43 -- Gets all the fields affected by a given association code
44 CURSOR c_fields(cp_association_code igs_he_sys_rt_cl_ass.association_code%TYPE) IS
45 SELECT ass.field_number
46 FROM igs_he_usr_rtn_clas urc,
47 igs_he_usr_rt_cl_fld fld,
48 igs_he_sys_rt_cl_ass ass
49 WHERE urc.user_return_subclass = p_user_return_subclass
50 AND fld.user_return_subclass = urc.user_return_subclass
51 AND ass.system_return_class_type = urc.system_return_class_type
52 AND fld.field_number = ass.field_number
53 AND fld.include_flag = 'Y'
54 AND ass.association_code = cp_association_code
55 ORDER BY ass.field_number;
56
57 -- Get the association type of the code (either CODE, DIRECT or INDIRECT)
58 CURSOR c_assoc_type(cp_association_code igs_he_code_ass_val.association_code%TYPE,
59 cp_sequence igs_he_code_ass_val.sequence%TYPE) IS
60 SELECT association_type, main_source, secondary_source, condition, display_title
61 FROM igs_he_code_ass_val
62 WHERE association_code = cp_association_code
63 AND sequence = cp_sequence;
64
65 l_unmapped_value igs_he_code_map_val.map2%TYPE;
66 l_assoc_type c_assoc_type%ROWTYPE;
67 l_where_stmt VARCHAR2(100);
68 l_stmt VARCHAR2(400);
69 l_affected_fields VARCHAR2(50);
70 l_count NUMBER := 0;
71
72 BEGIN
73
74 -- Log Header
75 fnd_message.set_name('IGS','IGS_HE_VERIFY_CHECK_CODES');
76 fnd_message.set_token('DATE',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
77 fnd_file.put_line(fnd_file.log,fnd_message.get());
78
79 -- Loop through all field associations of selected user defined return class
80 -- Need to determine every OSS value that doesn't have a corresponding HESA value
81 FOR l_assoc IN c_assoc LOOP
82
83 l_stmt := NULL;
84 l_where_stmt := NULL;
85 l_assoc_type := NULL;
86
87 -- Determine association type of the OSS mapping
88 OPEN c_assoc_type(l_assoc.association_code, l_assoc.oss_seq);
89 FETCH c_assoc_type INTO l_assoc_type;
90 CLOSE c_assoc_type;
91
92
93 -- Construct query
94 IF l_assoc_type.association_type = 'CODE' THEN
95 l_stmt := ' SELECT DISTINCT value' ||
96 ' FROM igs_he_code_values ' ||
97 ' WHERE value NOT IN ';
98 ELSE
99 l_stmt := ' SELECT DISTINCT ' || l_assoc_type.secondary_source ||
100 ' FROM ' || l_assoc_type.main_source ||
101 ' WHERE ' || l_assoc_type.secondary_source || ' NOT IN ';
102 END IF;
103
104 -- Construct where clause to get all OSS values which are not mapped
105 l_stmt := l_stmt || ' (SELECT map' || l_assoc.oss_seq ||
106 ' FROM igs_he_code_map_val ' ||
107 ' WHERE association_code = :ASS_CODE)';
108
109 -- Include any other where conditions
110 IF l_assoc_type.association_type = 'CODE' THEN
111 l_stmt := l_stmt || ' AND closed_ind = ''N'' AND code_type = :CODE_TYPE ';
112 ELSIF l_assoc_type.condition IS NOT NULL THEN
113 l_stmt := l_stmt || ' AND ' || l_assoc_type.condition;
114 END IF;
115
116
117 -- Open cursor with appropriate bind variables
118 IF l_assoc_type.association_type = 'CODE' THEN
119 OPEN l_unmapped FOR l_stmt USING l_assoc.association_code, l_assoc_type.main_source;
120 ELSE
121 OPEN l_unmapped FOR l_stmt USING l_assoc.association_code;
122 END IF;
123
124 -- Find all unmapped OSS values
125 FETCH l_unmapped INTO l_unmapped_value;
126
127 IF l_unmapped%FOUND THEN
128
129 -- Get all fields affected by missing association code mapping
130 FOR l_fields IN c_fields(l_assoc.association_code) LOOP
131 IF c_fields%ROWCOUNT = 1 THEN
132 l_affected_fields := l_fields.field_number;
133 ELSE
134 IF LENGTH(l_affected_fields || ',' || l_fields.field_number) < 22 THEN
135 l_affected_fields := l_affected_fields || ',' || l_fields.field_number;
136 ELSE
137 l_affected_fields := l_affected_fields || '...';
138 EXIT;
139 END IF;
140 END IF;
141 END LOOP; -- affected fields
142
143 -- Insert each unmapped value into temp table
144 LOOP
145
146 INSERT INTO IGS_HE_VERIFY_DATA_T (association_code, fields_affected, display_title, oss_value, creation_date, created_by, last_update_date, last_updated_by)
147 VALUES (l_assoc.association_code, l_affected_fields, l_assoc_type.display_title, l_unmapped_value, sysdate, -1, sysdate, -1);
148
149 --Increment Counter
150 l_count := l_count + 1;
151
152 FETCH l_unmapped INTO l_unmapped_value;
153 EXIT WHEN l_unmapped%NOTFOUND;
154
155 END LOOP;
156
157 END IF; -- unmapped found
158
159 CLOSE l_unmapped;
160
161 END LOOP; -- association codes
162
163 -- Log Summary
164 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_CODES');
165 fnd_message.set_token('MISSING',l_count);
166 fnd_file.put_line(fnd_file.log,fnd_message.get());
167
168 EXCEPTION
169 WHEN OTHERS THEN
170
171 IF l_unmapped%ISOPEN THEN
172 CLOSE l_unmapped;
173 END IF;
174
175 IF c_assoc%ISOPEN THEN
176 CLOSE c_assoc;
177 END IF;
178
179 IF c_assoc_type%ISOPEN THEN
180 CLOSE c_assoc_type;
181 END IF;
182
183 IF c_fields%ISOPEN THEN
184 CLOSE c_fields;
185 END IF;
186
187 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
188 Fnd_Message.Set_Token('NAME','igs_he_verify_setup_pkg.check_associations - ' || SQLERRM);
189 fnd_file.put_line(fnd_file.log, fnd_message.get);
190 App_Exception.Raise_Exception;
191
192 END check_associations;
193
194
195
196 PROCEDURE check_HESA_data(p_submission_name IN VARCHAR2,
197 p_user_return_subclass IN VARCHAR2,
198 p_return_name IN VARCHAR2) IS
199 /******************************************************************
200 Created By : Jonathan Baber
201 Date Created By : 23-Nov-05
202 Purpose : Makes sure HESA records exist at the correct levels
203 Known limitations,enhancements,remarks:
204 Change History
205 Who When What
206 jchin 21-Jun-06 Bug 5213152 Modified c_get_yop cursor to
207 conform with IGSHE9AB
208 *******************************************************************/
209
210
211 -- Cursor to determine if course has qual aim
212 CURSOR c_award_qualaim
213 (cp_course_cd igs_ps_award.course_cd%TYPE,
214 cp_version_number igs_ps_award.version_number%TYPE) IS
215 SELECT DECODE(count(award_cd), 0, 'N', 'Y')
216 FROM igs_ps_award
217 WHERE course_cd = cp_course_cd
218 AND version_number = cp_version_number
219 AND closed_ind = 'N';
220
221
222 -- Get Student Program Attempt Records
223 -- Similar cursor as IGSHE9AB but with outer join to igs_he_st_spa_all
224 CURSOR c_get_spa (cp_awd_conf_start_dt DATE,
225 cp_awd_conf_end_dt DATE) IS
226 SELECT DISTINCT sca.person_id,
227 pe.party_number person_number,
228 sca.course_cd,
229 sca.version_number,
230 sca.location_cd ,
231 sca.attendance_mode,
232 sca.attendance_type,
233 sca.cal_type sca_cal_type,
234 sca.commencement_dt ,
235 sca.discontinued_dt,
236 sca.course_rqrmnts_complete_dt,
237 sca.course_attempt_status,
238 hspa.student_inst_number,
239 DECODE(hspa.hesa_st_spa_id, NULL, 'N', 'Y') spa_flag,
240 DECODE(hspa.student_inst_number, NULL, 'N', 'Y') sin_flag,
241 DECODE(hspa.student_qual_aim, NULL, 'N', 'Y') spa_qualaim_flag,
242 enawd.complete_ind,
243 enawd.conferral_date
244 FROM igs_en_stdnt_ps_att_all sca,
245 igs_he_st_spa_all hspa,
246 igs_he_st_prog_all hprog,
247 igs_en_spa_awd_aim enawd,
248 hz_parties pe
249 WHERE sca.person_id = hspa.person_id (+)
250 AND sca.course_cd = hspa.course_cd (+)
251 AND sca.course_cd = hprog.course_cd (+)
252 AND sca.version_number = hprog.version_number (+)
253 AND NVL(hprog.exclude_flag, 'N') = 'N'
254 AND NVL(hspa.exclude_flag, 'N') = 'N'
255 AND NVL(sca.future_dated_trans_flag,'N') IN ('N','S')
256 AND sca.student_confirmed_ind = 'Y'
257 AND hspa.person_id = enawd.person_id(+)
258 AND hspa.course_cd = enawd.course_cd(+)
259 AND sca.person_id = pe.party_id
260 AND ( ( sca.commencement_dt <= g_he_submsn_header.enrolment_end_date
261 AND ( sca.discontinued_dt IS NULL OR sca.discontinued_dt >= g_he_submsn_header.enrolment_start_date )
262 AND (sca.course_rqrmnts_complete_dt IS NULL OR
263 sca.course_rqrmnts_complete_dt >= g_he_submsn_header.enrolment_start_date
264 )
265 )
266 OR -- Added for HE309
267 -- check whether award conferral dates are defined first at program level
268 -- or program type level, otherwise hesa submission reporting periods
269 enawd.complete_ind = 'Y' AND
270 (enawd.conferral_date BETWEEN cp_awd_conf_start_dt AND cp_awd_conf_end_dt)
271 )
272 ORDER BY sca.person_id, hspa.student_inst_number, discontinued_dt DESC,
273 course_rqrmnts_complete_dt DESC, sca.commencement_dt DESC ;
274
275
276 -- Get SUSA Records
277 -- Similar cursor as IGSHE9AB but with outer join to igs_he_en_susa
278 CURSOR c_get_yop (cp_person_id igs_he_st_spa.person_id%TYPE,
279 cp_course_cd igs_he_st_spa.course_cd%TYPE,
280 cp_enrl_start_dt DATE,
281 cp_enrl_end_dt DATE,
282 cp_awd_conf_start_dt DATE,
283 cp_awd_conf_end_dt DATE) IS
284 SELECT DISTINCT
285 susa.unit_set_cd,
286 susa.us_version_number,
287 DECODE(husa.hesa_en_susa_id, NULL, 'N','Y') susa_flag
288 FROM igs_as_su_setatmpt susa,
289 igs_he_en_susa husa,
290 igs_en_unit_set us,
291 igs_en_unit_set_cat susc,
292 igs_en_spa_awd_aim enawd,
293 igs_en_stdnt_ps_att_all sca
294 WHERE susa.person_id = sca.person_id
295 AND susa.course_cd = sca.course_cd
296 AND sca.person_id = enawd.person_id(+)
297 AND sca.course_cd = enawd.course_cd(+)
298 AND susa.unit_set_cd = husa.unit_set_cd(+)
299 AND susa.us_version_number = husa.us_version_number(+)
300 AND susa.person_id = husa.person_id(+)
301 AND susa.course_cd = husa.course_cd(+)
302 AND susa.sequence_number = husa.sequence_number(+)
303 AND susa.unit_set_cd = us.unit_set_cd
304 AND susa.us_version_number = us.version_number
305 AND us.unit_set_cat = susc.unit_set_cat
306 AND susa.person_id = cp_person_id
307 AND susa.course_cd = cp_course_cd
308 AND susc.s_unit_set_cat = 'PRENRL_YR'
309 -- the program attempt is overlapping with the submmission period and the yop is also overlapping with the submission period
310 AND ( ( sca.commencement_dt <= cp_enrl_end_dt AND
311 (sca.discontinued_dt IS NULL OR sca.discontinued_dt >= cp_enrl_start_dt ) AND
312 (sca.course_rqrmnts_complete_dt IS NULL OR sca.course_rqrmnts_complete_dt >= cp_enrl_start_dt ) AND
313 susa.selection_dt <= cp_enrl_end_dt AND
314 (susa.end_dt IS NULL OR susa.end_dt >= cp_enrl_start_dt ) AND
315 (susa.rqrmnts_complete_dt IS NULL OR susa.rqrmnts_complete_dt >= cp_enrl_start_dt)
316 )
317 OR
318 -- jchin bug 5213152
319 -- the program attempt is completed before the submmission period start and award is conferred in the submission period and
320 -- the yop is completed before the award conferral date
321 ( susa.rqrmnts_complete_dt < cp_enrl_start_dt AND
322 sca.course_rqrmnts_complete_dt <= cp_enrl_end_dt AND
323 enawd.complete_ind = 'Y' AND
324 enawd.conferral_date BETWEEN cp_awd_conf_start_dt AND cp_awd_conf_end_dt
325 )
326 ) ;
327
328 -- Does program have associated HESA record?
329 CURSOR c_get_prog(cp_course_cd igs_he_st_prog_all.course_cd%TYPE,
330 cp_version_number igs_he_st_prog_all.version_number%TYPE) IS
331 SELECT DECODE(count(course_cd), 0, 'N', 'Y') prog_flag
332 FROM igs_he_st_prog_all
333 WHERE course_cd = cp_course_cd
334 AND version_number = cp_version_number;
335
336
337 -- Does POOUS have associated HESA record?
338 CURSOR c_get_poous(cp_crv_version_number igs_he_poous_all.crv_version_number%TYPE,
339 cp_course_cd igs_he_poous_all.course_cd%TYPE,
340 cp_cal_type igs_he_poous_all.cal_type%TYPE,
341 cp_location_cd igs_he_poous_all.location_cd%TYPE,
342 cp_attendance_mode igs_he_poous_all.attendance_mode%TYPE,
343 cp_attendance_type igs_he_poous_all.attendance_type%TYPE,
344 cp_unit_set_cd igs_he_poous_all.unit_set_cd%TYPE,
345 cp_us_version_number igs_he_poous_all.us_version_number%TYPE) IS
346 SELECT DECODE(count(crv_version_number), 0, 'N', 'Y') poous_flag
347 FROM igs_he_poous_all
348 WHERE crv_version_number = cp_crv_version_number
349 AND course_cd = cp_course_cd
350 AND cal_type = cp_cal_type
351 AND location_cd = cp_location_cd
352 AND attendance_mode = cp_attendance_mode
353 AND attendance_type = cp_attendance_type
354 AND unit_set_cd = cp_unit_set_cd
355 AND us_version_number = cp_us_version_number;
356
357 -- Determines course type
358 CURSOR c_prog_type (cp_course_cd igs_ps_ver_all.course_cd%TYPE,
359 cp_version_number igs_ps_ver_all.version_number%TYPE) IS
360 SELECT course_type
361 FROM igs_ps_ver_all
362 WHERE course_cd = cp_course_cd
363 AND version_number = cp_version_number;
364
365 -- Alternate ID Cursor
366 CURSOR c_alternate_id (p_person_id igs_pe_person.person_id%TYPE,
367 cp_enrl_start_dt igs_he_submsn_header.enrolment_start_date%TYPE,
368 cp_enrl_end_dt igs_he_submsn_header.enrolment_end_date%TYPE) IS
369 SELECT api_person_id,person_id_type, LENGTH(api_person_id) api_length
370 FROM igs_pe_alt_pers_id
371 WHERE pe_person_id = p_person_id
372 AND person_id_type IN ('HUSID', 'UCASID', 'GTTRID', 'NMASID', 'SWASID')
373 AND Start_Dt <= cp_enrl_end_dt
374 AND (End_Dt IS NULL OR End_Dt >= cp_enrl_start_dt )
375 AND (End_Dt IS NULL OR Start_Dt <> End_Dt)
376 ORDER BY person_id_type, Start_Dt DESC ;
377
378 -- Unit Attempt enrollment
379 -- returns a row if a SPA has any unit attempts with a status of ENROLLED, COMPLETED, DISCONTIN
380 -- or DUPLICATE where the unit attempt enrollment date is less than or equal to the reporting
381 -- period end date.
382 CURSOR c_enr_su (p_person_id igs_en_stdnt_ps_att_all.person_id%TYPE,
383 p_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE,
384 p_enrolment_end_date igs_he_submsn_header.enrolment_end_date%TYPE) IS
385 SELECT 'X'
386 FROM igs_en_su_attempt_all
387 WHERE person_id = p_person_id
388 AND course_cd = p_course_cd
389 AND unit_attempt_status IN ('ENROLLED', 'COMPLETED','DISCONTIN','DUPLICATE')
390 AND enrolled_dt <= p_enrolment_end_date;
391
392
393
394 l_enrolled_su c_enr_su%ROWTYPE ;
395 l_prog_type igs_ps_ver_all.course_type%TYPE;
396 l_prev_pid_type igs_pe_alt_pers_id.person_id_type%TYPE := 'X' ;
397 l_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE;
398 l_id NUMBER;
399 l_awd_table igs_he_extract_fields_pkg.awd_table;
400 l_prog_rec_flag BOOLEAN := FALSE;
401 l_prog_type_rec_flag BOOLEAN := FALSE;
402 l_awd_conf_start_dt igs_he_submsn_awd.award_start_date%TYPE;
403 l_awd_conf_end_dt igs_he_submsn_awd.award_end_date%TYPE;
404 l_prev_person_id NUMBER := -1;
405 l_prev_student_inst_number VARCHAR2(100) := '-1';
406 l_valid BOOLEAN;
407 l_verify_data igs_he_verify_data_t%ROWTYPE := NULL;
408 l_count NUMBER;
409
410
411
412 BEGIN
413
414 -- Log Header
415 fnd_message.set_name('IGS','IGS_HE_VERIFY_CHECK_HESA');
416 fnd_message.set_token('DATE',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
417 fnd_file.put_line(fnd_file.log,fnd_message.get());
418
419 -- Get award conferral dates (to be used in c_get_spa)
420 igs_he_extract_fields_pkg.get_awd_dtls( p_submission_name, l_awd_table, l_prog_rec_flag, l_prog_type_rec_flag);
421 igs_he_extract_fields_pkg.get_min_max_awd_dates( p_submission_name, g_he_submsn_header.enrolment_start_date, g_he_submsn_header.enrolment_end_date, l_awd_conf_start_dt, l_awd_conf_end_dt);
422
423 -- Examine each spa record for eligibility
424 FOR l_std_inst IN c_get_spa(l_awd_conf_start_dt, l_awd_conf_end_dt) LOOP
425
426 IF (l_std_inst.person_id <> l_prev_person_id) OR (l_std_inst.student_inst_number <> l_prev_student_inst_number) THEN
427
428
429 l_verify_data := NULL;
430
431 l_verify_data.person_id := l_std_inst.person_id;
432 l_verify_data.course_cd := l_std_inst.course_cd;
433 l_verify_data.crv_version_number := l_std_inst.version_number;
434 l_verify_data.cal_type := l_std_inst.sca_cal_type;
435 l_verify_data.location_cd := l_std_inst.location_cd;
436 l_verify_data.attendance_mode := l_std_inst.attendance_mode;
437 l_verify_data.attendance_type := l_std_inst.attendance_type;
438
439
440 /********* Basic Validation *********/
441 -- similar to IGSHE9AB, but without 'Define Extract Criteria'
442 -- parameter validation
443
444 l_valid := TRUE;
445
446
447 -- Award conferral dates
448 IF l_valid THEN
449
450 l_awd_conf_start_dt := g_he_submsn_header.enrolment_start_date;
451 l_awd_conf_end_dt := g_he_submsn_header.enrolment_end_date;
452
453 IF NOT ( l_std_inst.commencement_dt <= g_he_submsn_header.enrolment_end_date
454 AND ( l_std_inst.discontinued_dt IS NULL OR l_std_inst.discontinued_dt >= g_he_submsn_header.enrolment_start_date )
455 AND (l_std_inst.course_rqrmnts_complete_dt IS NULL OR l_std_inst.course_rqrmnts_complete_dt >= g_he_submsn_header.enrolment_start_date)
456 ) THEN
457
458 -- If student has a conferral date
459 IF l_std_inst.complete_ind = 'Y' AND l_std_inst.conferral_date IS NOT NULL THEN
460
461 IF l_prog_type_rec_flag = TRUE
462 THEN
463
464 -- If there are award conferral dates specified at the program type
465 -- level only, then check if any relate to this particular student program attempt
466 OPEN c_prog_type(l_std_inst.course_cd, l_std_inst.version_number);
467 FETCH c_prog_type INTO l_prog_type;
468 CLOSE c_prog_type;
469
470
471 igs_he_extract_fields_pkg.get_awd_conferral_dates(l_awd_table,
472 p_submission_name,
473 l_prog_rec_flag,
474 l_prog_type_rec_flag,
475 l_std_inst.course_cd,
476 l_prog_type,
477 g_he_submsn_header.enrolment_start_date,
478 g_he_submsn_header.enrolment_end_date,
479 l_awd_conf_start_dt,
480 l_awd_conf_end_dt);
481
482 ELSE
483
484 -- If there are award conferral dates specified at the program level only,
485 -- then check if any relate to this particular student program attempt
486 igs_he_extract_fields_pkg.get_awd_conferral_dates(l_awd_table,
487 p_submission_name,
488 l_prog_rec_flag,
489 l_prog_type_rec_flag,
490 l_std_inst.course_cd,
491 NULL,
492 g_he_submsn_header.enrolment_start_date,
493 g_he_submsn_header.enrolment_end_date,
494 l_awd_conf_start_dt,
495 l_awd_conf_end_dt);
496
497 END IF; -- l_prog_type_rec_flag
498
499 IF NOT l_std_inst.conferral_date BETWEEN l_awd_conf_start_dt AND l_awd_conf_end_dt THEN
500 l_valid := FALSE;
501 END IF;
502
503 END IF;
504
505 END IF;
506
507 END IF; -- Award conferral dates
508
509
510 -- Alternate ID Check
511 IF l_valid THEN
512
513 l_prev_pid_type := 'X' ;
514 --TO check that the alternate person id's if present for the person is number (i.e it does not contains non-numeric character)
515 FOR alternate_id_rec IN c_alternate_id( l_std_inst.person_id,
516 g_he_submsn_header.enrolment_start_date,
517 g_he_submsn_header.enrolment_end_date) LOOP
518
519 IF (alternate_id_rec.person_id_type <> l_prev_pid_type) THEN
520
521 l_prev_pid_type := alternate_id_rec.person_id_type;
522 BEGIN
523
524 l_id := NULL;
525 l_api_person_id := NULL ;
526 l_api_person_id := alternate_id_rec.api_person_id;
527 IF l_api_person_id IS NOT NULL THEN
528 l_id := TO_NUMBER(l_api_person_id);
529 END IF;
530
531 EXCEPTION
532 WHEN value_error THEN
533
534 -- In case the alternate person id contains non-numeric characters
535 -- exclude this record from further processing
536 l_valid := FALSE ;
537 END;
538
539 IF alternate_id_rec.person_id_type <> 'HUSID' AND alternate_id_rec.api_length > 8 THEN
540 -- HUSID has more than 8 characters so exclude this record
541 l_valid := FALSE ;
542 END IF;
543
544 END IF; -- validate only latest Person id type record of each type
545
546 END LOOP;
547
548 END IF; -- Alternate ID Check
549
550
551 -- Check SPA is enrolled
552 IF l_valid THEN
553 -- if the current SPA is not enrolled, check associated unit attempts
554 IF NOT l_std_inst.course_attempt_status = 'ENROLLED' THEN
555 l_enrolled_su := NULL;
556 OPEN c_enr_su(l_std_inst.person_id,
557 l_std_inst.course_cd,
558 g_he_submsn_header.enrolment_end_date) ;
559 FETCH c_enr_su INTO l_enrolled_su ;
560 IF c_enr_su%NOTFOUND THEN
561 l_valid := FALSE;
562 END IF;
563 CLOSE c_enr_su;
564 END IF;
565 END IF; -- SPA is enrolled
566
567
568 -- Check offset days
569 IF l_valid THEN
570
571 IF g_he_submsn_header.offset_days IS NOT NULL
572 THEN
573 IF g_he_submsn_header.apply_to_atmpt_st_dt = 'Y'
574 AND l_std_inst.discontinued_dt < (l_std_inst.commencement_dt + g_he_submsn_header.offset_days)
575 THEN
576 -- Exclude this record
577 l_valid := FALSE;
578
579 END IF;
580 END IF;
581
582 END IF; -- Offset days
583
584
585 /********* Check SUSA Details *********/
586 IF l_valid THEN
587 OPEN c_get_yop(l_verify_data.person_id, l_verify_data.course_cd, g_he_submsn_header.enrolment_start_date, g_he_submsn_header.enrolment_end_date, l_awd_conf_start_dt, l_awd_conf_end_dt);
588 FETCH c_get_yop INTO l_verify_data.unit_set_cd,
589 l_verify_data.us_version_number,
590 l_verify_data.susa_flag;
591
592 -- If there is no SUSA record at all, then this SPA is invalid
593 IF c_get_yop%NOTFOUND THEN
594 l_valid := FALSE;
595 END IF;
596
597 CLOSE c_get_yop;
598
599 END IF;
600
601
602
603 IF l_valid THEN
604 -- If this is a module return this is as far as we need to go for the students.
605 -- We assume all these students *could* be in the return so we insert them into temp table
606 IF SUBSTR(g_he_submsn_return.record_id,4,2) = '13' THEN
607
608 INSERT INTO IGS_HE_VERIFY_DATA_T(person_id, creation_date, created_by, last_update_date, last_updated_by)
609 VALUES (l_std_inst.person_id, sysdate, 1, sysdate, 1);
610
611 ELSE
612
613 -- If this is a student or combined return, we now need to check existence of HESA records...
614
615
616 /********* Check SPA Details *********/
617 l_verify_data.spa_flag := l_std_inst.spa_flag;
618 l_verify_data.qualaim_flag := l_std_inst.spa_qualaim_flag;
619 l_verify_data.sin_flag := l_std_inst.sin_flag;
620
621 -- If spa_qualaim = 'N' at this point, check at the award level
622 IF l_std_inst.spa_qualaim_flag = 'N' THEN
623 OPEN c_award_qualaim(l_std_inst.course_cd, l_std_inst.version_number);
624 FETCH c_award_qualaim INTO l_verify_data.qualaim_flag;
625 CLOSE c_award_qualaim;
626 END IF;
627
628 /********* Check POOUS Details *********/
629 OPEN c_get_poous(l_verify_data.crv_version_number, l_verify_data.course_cd, l_verify_data.cal_type,
630 l_verify_data.location_cd, l_verify_data.attendance_mode, l_verify_data.attendance_type,
631 l_verify_data.unit_set_cd, l_verify_data.us_version_number);
632 FETCH c_get_poous INTO l_verify_data.poous_flag;
633 CLOSE c_get_poous;
634
635
636 /********* Check Program Details *********/
637 OPEN c_get_prog( l_verify_data.course_cd, l_verify_data.crv_version_number);
638 FETCH c_get_prog INTO l_verify_data.prog_flag;
639 CLOSE c_get_prog;
640
641 -- Insert complete record into temp table
642 INSERT INTO IGS_HE_VERIFY_DATA_T
643 (person_id,
644 course_cd,
645 crv_version_number,
646 cal_type,
647 location_cd,
648 attendance_mode,
649 attendance_type,
650 unit_set_cd,
651 us_version_number,
652 spa_flag,
653 qualaim_flag,
654 sin_flag,
655 susa_flag,
656 poous_flag,
657 prog_flag,
658 creation_date,
659 created_by,
660 last_update_date,
661 last_updated_by)
662 VALUES
663 (l_verify_data.person_id,
664 l_verify_data.course_cd,
665 l_verify_data.crv_version_number,
666 l_verify_data.cal_type,
667 l_verify_data.location_cd,
668 l_verify_data.attendance_mode,
669 l_verify_data.attendance_type,
670 l_verify_data.unit_set_cd,
671 l_verify_data.us_version_number,
672 l_verify_data.spa_flag,
673 l_verify_data.qualaim_flag,
674 l_verify_data.sin_flag,
675 l_verify_data.susa_flag,
676 l_verify_data.poous_flag,
677 l_verify_data.prog_flag,
678 sysdate,
679 1,
680 sysdate,
681 1);
682
683
684 END IF; -- MODULE or COMBINED/STUDENT
685
686 END IF;
687
688 -- Update previous Person ID and Student Instance Number
689 l_prev_person_id := l_std_inst.person_id;
690 l_prev_student_inst_number := l_std_inst.student_inst_number;
691
692 END IF; -- End of Duplicate HSPA record Check
693
694 END LOOP; -- For each Person ID and Student Instance Number
695
696
697
698
699 -- If this is a module return
700 IF SUBSTR(g_he_submsn_return.record_id,4,2) = '13' THEN
701
702 /********* Check Module Details *********/
703 -- Insert all units started or completed within the enrolment period
704 -- by the students identified above. This is *similar* to the query used in
705 -- IGSHE9CB, however the student_inst_number is NOT used so query may select
706 -- more units than will appear in the return.
707 INSERT INTO IGS_HE_VERIFY_DATA_T
708 (unit_cd, u_version_number, unit_flag, creation_date, created_by, last_update_date, last_updated_by)
709 (SELECT DISTINCT
710 ua.unit_cd,
711 ua.version_number,
712 DECODE(hunt.hesa_st_unt_vs_id, NULL, 'N', 'Y') unit_flag, sysdate, 1, sysdate, 1
713 FROM igs_en_su_attempt_all ua,
714 igs_he_verify_data_t t,
715 igs_he_st_unt_vs_all hunt
716 WHERE t.person_id = ua.person_id
717 AND ua.unit_cd = hunt.unit_cd (+)
718 AND ua.version_number = hunt.version_number(+)
719 AND ua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
720 AND ua.ci_start_dt BETWEEN g_he_submsn_header.enrolment_start_date AND g_he_submsn_header.enrolment_end_date
721 AND hunt.hesa_st_unt_vs_id IS NULL -- only get modules with no HESA details
722 UNION
723 SELECT DISTINCT
724 ua.unit_cd,
725 ua.version_number,
726 DECODE(hunt.hesa_st_unt_vs_id, NULL, 'N', 'Y') unit_flag, sysdate, 1, sysdate, 1
727 FROM igs_en_su_attempt_all ua,
728 igs_he_verify_data_t t,
729 igs_he_st_unt_vs_all hunt,
730 igs_as_su_stmptout_all uao
731 WHERE t.person_id = ua.person_id
732 AND ua.unit_cd = hunt.unit_cd (+)
733 AND ua.version_number = hunt.version_number(+)
734 AND uao.person_id = ua.person_id
735 AND uao.course_cd = ua.course_cd
736 AND uao.uoo_id = ua.uoo_id
737 AND uao.finalised_outcome_ind = 'Y'
738 AND ua.unit_attempt_status IN ('ENROLLED','DISCONTIN','COMPLETED')
739 AND uao.outcome_dt BETWEEN g_he_submsn_header.enrolment_start_date AND g_he_submsn_header.enrolment_end_date
740 AND hunt.hesa_st_unt_vs_id IS NULL -- only get modules with no HESA details
741 );
742
743 -- Delete the temporarily stored student records
744 DELETE FROM IGS_HE_VERIFY_DATA_T
745 WHERE person_id IS NOT NULL;
746
747 END IF; -- Module Return
748
749
750 /********* Log Summary of Missing HESA Records *********/
751 IF SUBSTR(g_he_submsn_return.record_id,4,2) = '13' THEN
752
753 -- Get count of missing Unit HESA Records
754 SELECT COUNT(DISTINCT unit_cd || u_version_number)
755 INTO l_count
756 FROM igs_he_verify_data_t
757 WHERE unit_flag = 'N';
758
759 -- Log Summary of Module Results
760 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_UNIT');
761 fnd_message.set_token('MISSING',l_count);
762 fnd_file.put_line(fnd_file.log,fnd_message.get());
763
764 ELSE
765
766
767 -- Get count of missing Program HESA Records
768 SELECT COUNT(DISTINCT course_cd || crv_version_number)
769 INTO l_count
770 FROM igs_he_verify_data_t
771 WHERE prog_flag = 'N';
772
773 -- Log Summary of Program Results
774 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_PROG');
775 fnd_message.set_token('MISSING',l_count);
776 fnd_file.put_line(fnd_file.log,fnd_message.get());
777
778 -- Get count of missing POOUS HESA Records
779 SELECT COUNT(DISTINCT course_cd || crv_version_number || cal_type || location_cd || attendance_mode || attendance_type || unit_set_cd || us_version_number)
780 INTO l_count
781 FROM igs_he_verify_data_t
782 WHERE poous_flag = 'N';
783
784 -- Log Summary of POOUS Results
785 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_POOUS');
786 fnd_message.set_token('MISSING',l_count);
787 fnd_file.put_line(fnd_file.log,fnd_message.get());
788
789 -- Get count of missing or incomplete SPA HESA Records
790 SELECT COUNT(DISTINCT person_id || course_cd)
791 INTO l_count
792 FROM igs_he_verify_data_t
793 WHERE spa_flag = 'N' OR qualaim_flag = 'N' OR sin_flag = 'N';
794
795 -- Log Summary of POOUS Results
796 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_SPA');
797 fnd_message.set_token('MISSING',l_count);
798 fnd_file.put_line(fnd_file.log,fnd_message.get());
799
800 -- Get count of missing or incomplete SPA HESA Records
801 SELECT COUNT(DISTINCT person_id || course_cd || unit_set_cd || us_version_number)
802 INTO l_count
803 FROM igs_he_verify_data_t
804 WHERE susa_flag = 'N';
805
806 -- Log Summary of POOUS Results
807 fnd_message.set_name('IGS','IGS_HE_VERIFY_MISS_SUSA');
808 fnd_message.set_token('MISSING',l_count);
809 fnd_file.put_line(fnd_file.log,fnd_message.get());
810
811 END IF;
812
813 EXCEPTION
814 WHEN OTHERS THEN
815
816 --Close any open cursors
817 IF c_award_qualaim%ISOPEN THEN
818 CLOSE c_award_qualaim;
819 END IF;
820
821 IF c_get_spa%ISOPEN THEN
822 CLOSE c_get_spa;
823 END IF;
824
825 IF c_get_yop%ISOPEN THEN
826 CLOSE c_get_yop;
827 END IF;
828
829 IF c_get_prog%ISOPEN THEN
830 CLOSE c_get_prog;
831 END IF;
832
833 IF c_get_poous%ISOPEN THEN
834 CLOSE c_get_poous;
835 END IF;
836
837 IF c_prog_type%ISOPEN THEN
838 CLOSE c_prog_type;
839 END IF;
840
841 IF c_alternate_id%ISOPEN THEN
842 CLOSE c_alternate_id;
843 END IF;
844
845 IF c_enr_su%ISOPEN THEN
846 CLOSE c_enr_su;
847 END IF;
848
849 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
850 Fnd_Message.Set_Token('NAME','igs_he_verify_setup_pkg.check_HESA_data - ' || SQLERRM);
851 fnd_file.put_line(fnd_file.log, fnd_message.get);
852 App_Exception.Raise_Exception;
853
854 END check_HESA_data;
855
856
857
858
859
860 PROCEDURE verify_return (errbuf OUT NOCOPY VARCHAR2,
861 retcode OUT NOCOPY NUMBER,
862 p_submission_name IN VARCHAR2,
863 p_sub_rtn_id IN NUMBER,
864 p_check_HESA_details IN VARCHAR2,
865 p_check_field_associations IN VARCHAR2) IS
866 /******************************************************************
867 Created By : Jonathan Baber
868 Date Created By : 23-Nov-05
869 Purpose : Main Function
870 Calls check_HESA_data and check_associations
871 depending on corresponding flags
872 Known limitations,enhancements,remarks:
873 Change History
874 Who When What
875 anwest 13-FEB-2006 Bug# 4950285 R12 Disable OSS Mandate
876 *******************************************************************/
877
878 -- Get extract run details
879 CURSOR c_extract_dtls IS
880 SELECT rtn.submission_name,
881 rtn.user_return_subclass,
882 rtn.return_name,
883 rtn.record_id,
884 shd.enrolment_start_date,
885 shd.enrolment_end_date,
886 shd.offset_days,
887 NVL(shd.apply_to_atmpt_st_dt,'N') apply_to_atmpt_st_dt
888 FROM igs_he_submsn_header shd,
889 igs_he_submsn_return rtn
890 WHERE rtn.sub_rtn_id = p_sub_rtn_id
891 AND rtn.submission_name = shd.submission_name;
892
893 l_request_id NUMBER;
894
895 BEGIN
896
897 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
898 IGS_GE_GEN_003.SET_ORG_ID;
899
900 -- Get extract details
901 -- Store in global variable
902 OPEN c_extract_dtls;
903 FETCH c_extract_dtls INTO g_he_submsn_return.submission_name,
904 g_he_submsn_return.user_return_subclass,
905 g_he_submsn_return.return_name,
906 g_he_submsn_return.record_id,
907 g_he_submsn_header.enrolment_start_date,
908 g_he_submsn_header.enrolment_end_date,
909 g_he_submsn_header.offset_days,
910 g_he_submsn_header.apply_to_atmpt_st_dt;
911 CLOSE c_extract_dtls;
912
913
914 -- Log Header
915 fnd_message.set_name('IGS','IGS_HE_PROC_SUBM');
916 fnd_message.set_token('SUBMISSION_NAME',g_he_submsn_return.submission_name);
917 fnd_message.set_token('USER_RETURN_SUBCLASS',g_he_submsn_return.user_return_subclass);
918 fnd_message.set_token('RETURN_NAME',g_he_submsn_return.return_name);
919 fnd_message.set_token('ENROLMENT_START_DATE',g_he_submsn_header.enrolment_start_date);
920 fnd_message.set_token('ENROLMENT_END_DATE',g_he_submsn_header.enrolment_end_date);
921 fnd_file.put_line(fnd_file.log,fnd_message.get());
922
923
924 -- Delete Temp Table
925 DELETE FROM IGS_HE_VERIFY_DATA_T;
926
927 -- Check HESA details of COMBINED/STUDENT or MODULE return
928 IF p_check_HESA_details = 'Y'
929 AND SUBSTR(g_he_submsn_return.record_id,4,2) <> '18' THEN
930 check_HESA_data(g_he_submsn_return.submission_name, g_he_submsn_return.user_return_subclass, g_he_submsn_return.return_name);
931 END IF;
932
933
934 -- Check field associations of given extract
935 IF p_check_field_associations = 'Y' THEN
936 check_associations(g_he_submsn_return.user_return_subclass);
937 END IF;
938
939
940 COMMIT;
941
942 -- Submit Report Log
943 fnd_message.set_name('IGS','IGS_HE_VERIFY_REPORT_SUB');
944 fnd_message.set_token('DATE',TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
945 fnd_file.put_line(fnd_file.log,fnd_message.get());
946
947
948 -- Submit the Report
949 l_request_id := NULL ;
950 l_request_id := Fnd_Request.Submit_Request
951 ( 'IGS',
952 'IGSHES03',
953 'Verify HESA Return Report',
954 NULL,
955 FALSE,
956 g_he_submsn_return.submission_name,
957 g_he_submsn_return.user_return_subclass,
958 g_he_submsn_return.return_name,
959 p_check_HESA_details,
960 p_check_field_associations);
961
962 EXCEPTION
963 WHEN OTHERS THEN
964
965 --Close any open cursors
966 IF c_extract_dtls%ISOPEN THEN
967 CLOSE c_extract_dtls;
968 END IF;
969
970 ROLLBACK;
971
972 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
973 Fnd_Message.Set_Token('NAME','igs_he_verify_setup_pkg.verify_setup - ' ||SQLERRM);
974 errbuf := fnd_message.get;
975 fnd_file.put_line(fnd_file.log, errbuf);
976 retcode := 2;
977 App_Exception.Raise_Exception;
978
979 END verify_return;
980
981
982 END igs_he_verify_return_pkg ;
[Home] [Help]