[Home] [Help]
PACKAGE BODY: APPS.IGS_HE_IMPORT_POPDLHE
Source
1 PACKAGE BODY igs_he_import_popdlhe AS
2 /* $Header: IGSHE26B.pls 120.1 2006/02/08 20:03:59 anwest noship $ */
3
4 TYPE popdlhe_dtls IS RECORD (
5 popdlhe_id igs_he_popdlhe_ints.popdlhe_id%TYPE,
6 husid igs_he_popdlhe_ints.husid%TYPE,
7 ownstu igs_he_popdlhe_ints.ownstu%TYPE,
8 xqmode01 igs_he_popdlhe_ints.xqmode01%TYPE,
9 ttcid igs_he_popdlhe_ints.ttcid%TYPE);
10
11 -- Function Declarations
12 FUNCTION validate_popdlhe(p_popdlhe_dtls IN popdlhe_dtls) RETURN BOOLEAN;
13
14 FUNCTION get_person_id(p_popdlhe_dtls IN popdlhe_dtls) RETURN NUMBER;
15
16 PROCEDURE log_results(p_return_name IN igs_he_sub_rtn_qual.return_name%TYPE,
17 p_qual_period IN igs_he_sub_rtn_qual.qual_period_code%TYPE,
18 p_total_dlhe_cnt IN NUMBER,
19 p_new_dlhe_cnt IN NUMBER,
20 p_upd_dlhe_cnt IN NUMBER,
21 p_fail_dlhe_cnt IN NUMBER,
22 p_not_mod_dlhe_cnt IN NUMBER);
23
24
25 PROCEDURE import_popdlhe_to_oss (errbuf OUT NOCOPY VARCHAR2,
26 retcode OUT NOCOPY NUMBER,
27 p_submission_name IN igs_he_sub_rtn_qual.submission_name%TYPE,
28 p_return_name IN igs_he_sub_rtn_qual.return_name%TYPE,
29 p_qual_period IN igs_he_sub_rtn_qual.qual_period_code%TYPE,
30 p_census_date IN VARCHAR2 ) IS
31 /******************************************************************
32 Created By : Jonathan Baber
33 Date Created By : 24-Aug-05
34 Purpose : Processes records from IGS_HE_POPDLHE_INTS interface table and
35 updates or inserts corresponding records in igs_he_stdnt_dlhe table
36 Known limitations,enhancements,remarks:
37 Change History
38 Who When What
39 anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
40 *******************************************************************/
41
42
43 -- Type defs
44 TYPE c_popdlhe_typ IS REF CURSOR;
45 c_popdlhe c_popdlhe_typ;
46
47
48 -- Cursor Defs
49 -- cursor to retrieve student dlhe rec infor
50 CURSOR c_dlhe_rec(cp_person_id igs_he_stdnt_dlhe.person_id%TYPE) IS
51 SELECT dlhe.rowid, dlhe.*
52 FROM igs_he_stdnt_dlhe dlhe
53 WHERE person_id = cp_person_id
54 AND submission_name = p_submission_name
55 AND return_name = p_return_name;
56
57 -- cursor to retrieve qualification period details
58 CURSOR c_qual_dets (cp_submission_name igs_he_sub_rtn_qual.submission_name%TYPE,
59 cp_return_name igs_he_sub_rtn_qual.return_name%TYPE,
60 cp_qual_period igs_he_sub_rtn_qual.qual_period_code%TYPE) IS
61 SELECT qual_period_type, qual_period_desc, user_return_subclass, closed_ind
62 FROM igs_he_sub_rtn_qual qual
63 WHERE qual.submission_name = cp_submission_name
64 AND qual.return_name = cp_return_name
65 AND qual.qual_period_code = cp_qual_period;
66
67
68 -- Variable Defs
69 l_qualified_teacher igs_he_stdnt_dlhe.qualified_teacher%TYPE;
70 l_pt_study igs_he_stdnt_dlhe.pt_study%TYPE;
71 l_qual_period igs_he_stdnt_dlhe.qual_period_code%TYPE;
72 l_person_id igs_he_stdnt_dlhe.person_id%TYPE;
73 l_qual_dets c_qual_dets%ROWTYPE;
74 l_upd_qual_dets c_qual_dets%ROWTYPE;
75 l_dlhe_rec c_dlhe_rec%ROWTYPE;
76 l_sql_stmt VARCHAR2(1000);
77 l_rowid VARCHAR2(30);
78 l_popdlhe_dtls popdlhe_dtls;
79 l_include BOOLEAN;
80
81 -- Counters for logging
82 l_total_dlhe_cnt NUMBER := 0;
83 l_new_dlhe_cnt NUMBER := 0;
84 l_upd_dlhe_cnt NUMBER := 0;
85 l_fail_dlhe_cnt NUMBER := 0;
86 l_not_mod_dlhe_cnt NUMBER := 0;
87
88 BEGIN
89
90 --anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
91 IGS_GE_GEN_003.SET_ORG_ID;
92
93 -- Get Qualifying Period
94 OPEN c_qual_dets(p_submission_name, p_return_name, p_qual_period);
95 FETCH c_qual_dets INTO l_qual_dets;
96 CLOSE c_qual_dets;
97
98
99 -- Report the Qualifying period details in the log file
100 fnd_message.set_name('IGS','IGS_HE_DLHE_QUAL_PERIOD');
101 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
102 fnd_message.set_token('DESC',l_qual_dets.qual_period_desc);
103 fnd_message.set_token('TYPE',l_qual_dets.qual_period_type);
104 fnd_file.put_line(fnd_file.log,fnd_message.get);
105
106
107 --Validate Parameters
108 IF l_qual_dets.qual_period_type = 'R' AND p_census_date IS NOT NULL THEN
109
110 -- Set warning for 'R' qual type and census date
111 fnd_message.set_name('IGS','IGS_HE_DLHE_CENSUS');
112 fnd_file.put_line(fnd_file.log, fnd_message.get);
113
114 ELSIF l_qual_dets.qual_period_type = 'L' AND p_census_date IS NULL THEN
115
116 -- Set error for 'L' qual type and census date and exit
117 fnd_message.set_name('IGS','IGS_HE_DLHE_NO_CENSUS');
118 fnd_file.put_line(fnd_file.log, fnd_message.get);
119 errbuf := fnd_message.get ;
120 retcode := 2;
121 RETURN;
122
123 END IF;
124
125
126 -- Construct and open POPDLHE query depending on qualification type
127 IF l_qual_dets.qual_period_type = 'L' THEN
128
129 l_sql_stmt := 'SELECT popdlhe_id, husid, ownstu, xqmode01, ttcid ' ||
130 'FROM igs_he_popdlhe_ints ' ||
131 'WHERE popdlhe = ''1'' ' ||
132 'AND census = :1' ;
133 OPEN c_popdlhe FOR l_sql_stmt USING p_census_date;
134
135 ELSIF l_qual_dets.qual_period_type = 'R' THEN
136
137 l_sql_stmt := 'SELECT popdlhe_id, husid, ownstu, xqmode01, ttcid ' ||
138 'FROM igs_he_popdlhe_ints ' ||
139 'WHERE rcident IN (''1'',''2'',''3'',''4'',''5'',''6'',''7'') ';
140 OPEN c_popdlhe FOR l_sql_stmt;
141
142 END IF;
143
144
145 -- Loop through all popdlhe records
146 LOOP
147 FETCH c_popdlhe INTO l_popdlhe_dtls;
148 EXIT WHEN c_popdlhe%NOTFOUND;
149
150 l_include := TRUE;
151
152 -- Increment total dlhe record count
153 l_total_dlhe_cnt := l_total_dlhe_cnt + 1;
154
155
156 -- Validate POPDLHE Interface Record
157 IF NOT validate_popdlhe(l_popdlhe_dtls) THEN
158 l_include := FALSE;
159 l_fail_dlhe_cnt := l_fail_dlhe_cnt + 1;
160 END IF;
161
162
163 -- Get PersonID from POPDLHE record
164 IF l_include THEN
165 l_person_id := get_person_id(l_popdlhe_dtls);
166
167 IF l_person_id IS NULL THEN
168 l_include := FALSE;
169 l_fail_dlhe_cnt := l_fail_dlhe_cnt + 1;
170 END IF;
171 END IF;
172
173
174 IF l_include THEN
175
176 -- Check if Stdnt DLHE record exists
177 OPEN c_dlhe_rec(l_person_id);
178 FETCH c_dlhe_rec INTO l_dlhe_rec;
179
180 IF c_dlhe_rec%FOUND THEN
181 -- Record already exists so may need updating
182
183 -- Get qual details for existing record
184 OPEN c_qual_dets (p_submission_name,
185 p_return_name,
186 l_dlhe_rec.qual_period_code);
187 FETCH c_qual_dets INTO l_upd_qual_dets;
188 CLOSE c_qual_dets;
189
190 -- Need to update record if popdlhe = 'N' or it exists in closed qualifying period
191 IF l_dlhe_rec.popdlhe_flag = 'N' OR l_upd_qual_dets.closed_ind = 'Y' THEN
192
193 -- If record in closed qualifying period use user selected qualifying period
194 IF l_upd_qual_dets.closed_ind = 'Y' THEN
195 l_qual_period := p_qual_period;
196 ELSE
197 l_qual_period := l_dlhe_rec.qual_period_code;
198 END IF;
199
200 -- Update record
201 igs_he_stdnt_dlhe_pkg.update_row(
202 x_rowid => l_dlhe_rec.rowid,
203 x_person_id => l_dlhe_rec.person_id,
204 x_submission_name => l_dlhe_rec.submission_name,
205 x_user_return_subclass => l_dlhe_rec.user_return_subclass ,
206 x_return_name => l_dlhe_rec.return_name,
207 x_qual_period_code => l_qual_period,
208 x_dlhe_record_status => l_dlhe_rec.dlhe_record_status,
209 x_participant_source => l_dlhe_rec.participant_source,
210 x_date_status_changed => l_dlhe_rec.date_status_changed,
211 x_validation_status => l_dlhe_rec.validation_status,
212 x_admin_coding => l_dlhe_rec.admin_coding,
213 x_survey_method => l_dlhe_rec.survey_method,
214 x_employment => l_dlhe_rec.employment,
215 x_further_study => l_dlhe_rec.further_study,
216 x_qualified_teacher => l_dlhe_rec.qualified_teacher,
217 x_pt_study => l_dlhe_rec.pt_study,
218 x_employer_business => l_dlhe_rec.employer_business,
219 x_employer_name => l_dlhe_rec.employer_name,
220 x_employer_classification => l_dlhe_rec.employer_classification,
221 x_employer_location => l_dlhe_rec.employer_location,
222 x_employer_postcode => l_dlhe_rec.employer_postcode,
223 x_employer_country => l_dlhe_rec.employer_country,
224 x_job_title => l_dlhe_rec.job_title,
225 x_job_duties => l_dlhe_rec.job_duties,
226 x_job_classification => l_dlhe_rec.job_classification,
227 x_employer_size => l_dlhe_rec.employer_size,
228 x_job_duration => l_dlhe_rec.job_duration,
229 x_job_salary => l_dlhe_rec.job_salary,
230 x_salary_refused => l_dlhe_rec.salary_refused,
231 x_qualification_requirement => l_dlhe_rec.qualification_requirement,
232 x_qualification_importance => l_dlhe_rec.qualification_importance,
233 x_job_reason1 => l_dlhe_rec.job_reason1,
234 x_job_reason2 => l_dlhe_rec.job_reason2,
235 x_job_reason3 => l_dlhe_rec.job_reason3,
236 x_job_reason4 => l_dlhe_rec.job_reason4,
237 x_job_reason5 => l_dlhe_rec.job_reason5,
238 x_job_reason6 => l_dlhe_rec.job_reason6,
239 x_job_reason7 => l_dlhe_rec.job_reason7,
240 x_job_reason8 => l_dlhe_rec.job_reason8,
241 x_other_job_reason => l_dlhe_rec.other_job_reason,
242 x_no_other_job_reason => l_dlhe_rec.no_other_job_reason,
243 x_job_source => l_dlhe_rec.job_source,
244 x_other_job_source => l_dlhe_rec.other_job_source,
245 x_no_other_job_source => l_dlhe_rec.no_other_job_source,
246 x_previous_job => l_dlhe_rec.previous_job,
247 x_previous_jobtype1 => l_dlhe_rec.previous_jobtype1,
248 x_previous_jobtype2 => l_dlhe_rec.previous_jobtype2,
249 x_previous_jobtype3 => l_dlhe_rec.previous_jobtype3,
250 x_previous_jobtype4 => l_dlhe_rec.previous_jobtype4,
251 x_previous_jobtype5 => l_dlhe_rec.previous_jobtype5,
252 x_previous_jobtype6 => l_dlhe_rec.previous_jobtype6,
253 x_further_study_type => l_dlhe_rec.further_study_type,
254 x_course_name => l_dlhe_rec.course_name,
255 x_course_training_subject => l_dlhe_rec.course_training_subject,
256 x_research_subject => l_dlhe_rec.research_subject,
257 x_research_training_subject => l_dlhe_rec.research_training_subject,
258 x_further_study_provider => l_dlhe_rec.further_study_provider,
259 x_further_study_qualaim => l_dlhe_rec.further_study_qualaim,
260 x_professional_qualification=> l_dlhe_rec.professional_qualification,
261 x_study_reason1 => l_dlhe_rec.study_reason1,
262 x_study_reason2 => l_dlhe_rec.study_reason2,
263 x_study_reason3 => l_dlhe_rec.study_reason3,
264 x_study_reason4 => l_dlhe_rec.study_reason4,
265 x_study_reason5 => l_dlhe_rec.study_reason5,
266 x_study_reason6 => l_dlhe_rec.study_reason6,
267 x_study_reason7 => l_dlhe_rec.study_reason7,
268 x_other_study_reason => l_dlhe_rec.other_study_reason,
269 x_no_other_study_reason => l_dlhe_rec.no_other_study_reason,
270 x_employer_sponsored => l_dlhe_rec.employer_sponsored,
271 x_funding_source => l_dlhe_rec.funding_source,
272 x_teacher_teaching => l_dlhe_rec.teacher_teaching,
273 x_teacher_seeking => l_dlhe_rec.teacher_seeking,
274 x_teaching_sector => l_dlhe_rec.teaching_sector,
275 x_teaching_level => l_dlhe_rec.teaching_level,
276 x_reason_for_ptcourse => l_dlhe_rec.reason_for_ptcourse,
277 x_job_while_studying => l_dlhe_rec.job_while_studying,
278 x_employer_support1 => l_dlhe_rec.employer_support1,
279 x_employer_support2 => l_dlhe_rec.employer_support2,
280 x_employer_support3 => l_dlhe_rec.employer_support3,
281 x_employer_support4 => l_dlhe_rec.employer_support4,
282 x_employer_support5 => l_dlhe_rec.employer_support5,
283 x_popdlhe_flag => 'Y'
284 );
285
286 -- Increment update count
287 l_upd_dlhe_cnt := l_upd_dlhe_cnt + 1;
288
289 ELSE
290 -- If no need to update, then update not mod counter
291 l_not_mod_dlhe_cnt := l_not_mod_dlhe_cnt + 1;
292 END IF;
293
294 ELSE
295 -- Record is new so insert
296
297 -- Determine qualified teacher status from ttcid field
298 IF l_popdlhe_dtls.ttcid = 0 THEN
299 l_qualified_teacher := 'N';
300 ELSE
301 l_qualified_teacher := 'Y';
302 END IF;
303
304 -- Determine part time study status from xqmode01 field
305 IF l_popdlhe_dtls.xqmode01 = 2 THEN
306 l_pt_study := 'Y';
307 ELSE
308 l_pt_study := 'N';
309 END IF;
310
311 igs_he_stdnt_dlhe_pkg.insert_row(
312 x_rowid => l_rowid,
313 x_person_id => l_person_id,
314 x_submission_name => p_submission_name,
315 x_user_return_subclass => l_qual_dets.user_return_subclass,
316 x_return_name => p_return_name,
317 x_qual_period_code => p_qual_period,
318 x_dlhe_record_status => 'NST',
319 x_participant_source => 'P',
320 x_date_status_changed => NULL,
321 x_validation_status => NULL,
322 x_admin_coding => NULL,
323 x_survey_method => NULL,
324 x_employment => NULL,
325 x_further_study => NULL,
326 x_qualified_teacher => l_qualified_teacher,
327 x_pt_study => l_pt_study,
328 x_employer_business => NULL,
329 x_employer_name => NULL,
330 x_employer_classification => NULL,
331 x_employer_location => NULL,
332 x_employer_postcode => NULL,
333 x_employer_country => NULL,
334 x_job_title => NULL,
335 x_job_duties => NULL,
336 x_job_classification => NULL,
337 x_employer_size => NULL,
338 x_job_duration => NULL,
339 x_job_salary => NULL,
340 x_salary_refused => 'N',
341 x_qualification_requirement => NULL,
342 x_qualification_importance => NULL,
343 x_job_reason1 => 'N',
344 x_job_reason2 => 'N',
345 x_job_reason3 => 'N',
346 x_job_reason4 => 'N',
347 x_job_reason5 => 'N',
348 x_job_reason6 => 'N',
349 x_job_reason7 => 'N',
350 x_job_reason8 => 'N',
351 x_other_job_reason => NULL,
352 x_no_other_job_reason => 'N',
353 x_job_source => NULL,
354 x_other_job_source => NULL,
355 x_no_other_job_source => 'N',
356 x_previous_job => NULL,
357 x_previous_jobtype1 => 'N',
358 x_previous_jobtype2 => 'N',
359 x_previous_jobtype3 => 'N',
360 x_previous_jobtype4 => 'N',
361 x_previous_jobtype5 => 'N',
362 x_previous_jobtype6 => 'N',
363 x_further_study_type => NULL,
364 x_course_name => NULL,
365 x_course_training_subject => NULL,
366 x_research_subject => NULL,
367 x_research_training_subject => NULL,
368 x_further_study_provider => NULL,
369 x_further_study_qualaim => NULL,
370 x_professional_qualification => NULL,
371 x_study_reason1 => NULL,
372 x_study_reason2 => 'N',
373 x_study_reason3 => 'N',
374 x_study_reason4 => 'N',
375 x_study_reason5 => 'N',
376 x_study_reason6 => 'N',
377 x_study_reason7 => 'N',
378 x_other_study_reason => NULL,
379 x_no_other_study_reason => 'N',
380 x_employer_sponsored => 'N',
381 x_funding_source => NULL,
382 x_teacher_teaching => 'N',
383 x_teacher_seeking => 'N',
384 x_teaching_sector => NULL,
385 x_teaching_level => NULL,
386 x_reason_for_ptcourse => NULL,
387 x_job_while_studying => 'N',
388 x_employer_support1 => 'N',
389 x_employer_support2 => 'N',
390 x_employer_support3 => 'N',
391 x_employer_support4 => 'N',
392 x_employer_support5 => 'N',
393 x_popdlhe_flag => 'Y'
394 );
395
396 -- update new counter
397 l_new_dlhe_cnt := l_new_dlhe_cnt+1;
398
399 END IF; -- c_dlhe_rec%FOUND
400
401 CLOSE c_dlhe_rec;
402
403 END IF;
404
405 END LOOP;
406
407 CLOSE c_popdlhe;
408
409
410 -- Log counters
411 log_results(p_return_name, p_qual_period, l_total_dlhe_cnt, l_new_dlhe_cnt, l_upd_dlhe_cnt, l_fail_dlhe_cnt, l_not_mod_dlhe_cnt);
412
413
414 EXCEPTION
415 WHEN OTHERS THEN
416
417 -- close any open cursors
418 IF c_popdlhe%ISOPEN THEN
419 CLOSE c_popdlhe;
420 END IF;
421
422 IF c_dlhe_rec%ISOPEN THEN
423 CLOSE c_dlhe_rec;
424 END IF;
425
426 ROLLBACK;
427 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
428 Fnd_Message.Set_Token('NAME','igs_he_import_popdlhe.import_popdlhe_to_oss - ' || SQLERRM);
429 fnd_file.put_line(fnd_file.log, fnd_message.get);
430 errbuf := fnd_message.get ;
431 retcode := 2;
432
433 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
434
435 END import_popdlhe_to_oss;
436
437
438 FUNCTION validate_popdlhe(p_popdlhe_dtls IN popdlhe_dtls) RETURN BOOLEAN IS
439 /******************************************************************
440 Created By : Jonathan Baber
441 Date Created By : 24-Aug-05
442 Purpose : Validates POPDLHE Interface table records
443 Known limitations,enhancements,remarks:
444 Change History
445 Who When What
446 *******************************************************************/
447
448 l_error_message VARCHAR2(100);
449 l_error_husid BOOLEAN := FALSE;
450 l_error_xqmode01 BOOLEAN := FALSE;
451 l_error_ttcid BOOLEAN := FALSE;
452 l_error_cnt NUMBER := 0;
453 l_temp NUMBER;
454 BEGIN
455
456
457 -- Check HUSID
458 -- 1) Should not be null
459 -- 2) Should be numeric
460 -- 3) Should be 13 characters in length
461 BEGIN
462 l_temp := TO_NUMBER(p_popdlhe_dtls.husid);
463 IF p_popdlhe_dtls.husid IS NULL OR LENGTH(p_popdlhe_dtls.husid) <> 13 THEN
464 l_error_husid := TRUE;
465 END IF;
466 EXCEPTION
467 WHEN VALUE_ERROR THEN
468 l_error_husid := TRUE;
469 END;
470
471 -- Check XQMode01
472 -- 1) Should not be null
473 -- 2) Should be numeric
474 -- 3) Should be either 1 or 2
475 BEGIN
476 IF p_popdlhe_dtls.xqmode01 IS NULL OR TO_NUMBER(p_popdlhe_dtls.xqmode01) NOT IN (1,2) THEN
477 l_error_xqmode01 := TRUE;
478 END IF;
479 EXCEPTION
480 WHEN VALUE_ERROR THEN
481 l_error_xqmode01 := TRUE;
482 END;
483
484 -- Check TTCID
485 -- 1) Should not be null
486 -- 2) Should be numeric
487 -- 3) Should be between 0 and 7
488 BEGIN
489 IF p_popdlhe_dtls.ttcid IS NULL OR TO_NUMBER(p_popdlhe_dtls.ttcid) NOT BETWEEN 0 AND 7 THEN
490 l_error_ttcid := TRUE;
491 END IF;
492 EXCEPTION
493 WHEN VALUE_ERROR THEN
494 l_error_ttcid := TRUE;
495 END;
496
497 IF NOT l_error_husid AND NOT l_error_xqmode01 AND NOT l_error_ttcid THEN
498 -- If no errors return TRUE
499 RETURN TRUE;
500 ELSE
501 -- If errors, log error message and return FALSE
502 IF l_error_husid THEN
503 l_error_message := 'HUSID';
504 l_error_cnt := l_error_cnt + 1;
505 END IF;
506
507 IF l_error_xqmode01 THEN
508 IF l_error_cnt = 0 THEN
509 l_error_message := 'XQMode01';
510 ELSIF l_error_cnt = 1 THEN
511 l_error_message := 'XQMode01 and ' || l_error_message;
512 ELSE
513 l_error_message := 'XQMode01, ' || l_error_message;
514 END IF;
515 l_error_cnt := l_error_cnt + 1;
516 END IF;
517
518 IF l_error_ttcid THEN
519 IF l_error_cnt = 0 THEN
520 l_error_message := 'TTCID';
521 ELSIF l_error_cnt = 1 THEN
522 l_error_message := 'TTCID and ' || l_error_message;
523 ELSE
524 l_error_message := 'TTCID, ' || l_error_message;
525 END IF;
526 l_error_cnt := l_error_cnt + 1;
527 END IF;
528
529 Fnd_Message.Set_Name('IGS','IGS_HE_DLHE_FAIL_FIELD_DERIVE');
530 Fnd_Message.Set_Token('FIELD',l_error_message);
531 Fnd_Message.Set_Token('POPDLHE_ID',p_popdlhe_dtls.popdlhe_id);
532 Fnd_Message.Set_Token('HUSID',p_popdlhe_dtls.husid);
533 fnd_file.put_line(fnd_file.log, fnd_message.get);
534
535 RETURN FALSE;
536 END IF;
537
538 EXCEPTION
539 WHEN OTHERS THEN
540 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
541 Fnd_Message.Set_Token('NAME','igs_he_import_popdlhe.validate_popdlhe - ' ||SQLERRM);
542 fnd_file.put_line(fnd_file.log, fnd_message.get);
543 App_Exception.Raise_Exception;
544
545 END validate_popdlhe;
546
547
548 FUNCTION get_person_id(p_popdlhe_dtls IN popdlhe_dtls) RETURN NUMBER IS
549 /******************************************************************
550 Created By : Jonathan Baber
551 Date Created By : 24-Aug-05
552 Purpose : Retrieves PersonID from either ownstu or husid
553 Known limitations,enhancements,remarks:
554 Change History
555 Who When What
556 *******************************************************************/
557 -- Determines PersonID from ownstu (party number)
558 CURSOR c_ownstu (cp_ownstu igs_he_popdlhe_ints.ownstu%TYPE) IS
559 SELECT party_id
560 FROM hz_parties
561 WHERE party_number = cp_ownstu;
562
563 -- Determines PersonID from alternateID table using HUSID
564 CURSOR c_husid (cp_husid igs_he_popdlhe_ints.husid%TYPE) IS
565 SELECT pe_person_id
566 FROM igs_pe_alt_pers_id
567 WHERE api_person_id = cp_husid
568 AND person_id_type= 'HUSID'
569 AND (end_dt IS NULL OR start_dt <> end_dt)
570 ORDER BY start_dt DESC;
571
572 l_person_id NUMBER;
573
574 BEGIN
575
576 -- try using owntsu to lookup hz_parties first
577 IF p_popdlhe_dtls.ownstu IS NOT NULL THEN
578
579 OPEN c_ownstu(p_popdlhe_dtls.ownstu);
580 FETCH c_ownstu INTO l_person_id;
581 CLOSE c_ownstu;
582
583 IF l_person_id IS NOT NULL THEN
584 RETURN l_person_id;
585 END IF;
586
587 END IF;
588
589 -- resort to HUSID
590 OPEN c_husid(p_popdlhe_dtls.husid);
591 FETCH c_husid INTO l_person_id;
592 CLOSE c_husid;
593
594 IF l_person_id IS NOT NULL THEN
595 RETURN l_person_id;
596 END IF;
597
598 -- At this stage we have not found person id
599 -- return NULL and log message
600 Fnd_Message.Set_Name('IGS','IGS_HE_DLHE_FAIL_PERSON_DERIVE');
601 Fnd_Message.Set_Token('POPDLHE_ID',p_popdlhe_dtls.popdlhe_id);
602 Fnd_Message.Set_Token('HUSID',p_popdlhe_dtls.husid);
603 fnd_file.put_line(fnd_file.log, fnd_message.get);
604
605 RETURN NULL;
606
607
608 EXCEPTION
609 WHEN OTHERS THEN
610 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
611 Fnd_Message.Set_Token('NAME','igs_he_import_popdlhe.get_person_id - ' ||SQLERRM);
612 fnd_file.put_line(fnd_file.log, fnd_message.get);
613 App_Exception.Raise_Exception;
614
615 END get_person_id;
616
617
618 PROCEDURE log_results(p_return_name IN igs_he_sub_rtn_qual.return_name%TYPE,
619 p_qual_period IN igs_he_sub_rtn_qual.qual_period_code%TYPE,
620 p_total_dlhe_cnt IN NUMBER,
621 p_new_dlhe_cnt IN NUMBER,
622 p_upd_dlhe_cnt IN NUMBER,
623 p_fail_dlhe_cnt IN NUMBER,
624 p_not_mod_dlhe_cnt IN NUMBER) IS
625 /******************************************************************
626 Created By : Jonathan Baber
627 Date Created By : 24-Aug-05
628 Purpose : Logs process stats
629 Known limitations,enhancements,remarks:
630 Change History
631 Who When What
632 *******************************************************************/
633 BEGIN
634
635 -- Report the total number of DLHE records processed
636 fnd_message.set_name('IGS','IGS_HE_DLHE_IMP_IDENT_POP');
637 fnd_message.set_token('TOTAL_DLHE', p_total_dlhe_cnt);
638 fnd_message.set_token('RETURN_NAME',p_return_name);
639 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
640 fnd_file.put_line(fnd_file.log, fnd_message.get);
641
642 -- Report the total number of new student DLHE records created
643 fnd_message.set_name('IGS','IGS_HE_DLHE_REC_CREATED');
644 fnd_message.set_token('CREATED_DLHE', p_new_dlhe_cnt);
645 fnd_message.set_token('RETURN_NAME',p_return_name);
646 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
647 fnd_file.put_line(fnd_file.log, fnd_message.get);
648
649 -- Report the total number of student DLHE records updated with the current qualifying period
650 fnd_message.set_name('IGS','IGS_HE_DLHE_REC_UPDATED');
651 fnd_message.set_token('UPDATED_DLHE', p_upd_dlhe_cnt);
652 fnd_message.set_token('RETURN_NAME',p_return_name);
653 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
654 fnd_file.put_line(fnd_file.log, fnd_message.get);
655
656 -- Report the total number of students failed to satisfy the field validations
657 fnd_message.set_name('IGS','IGS_HE_DLHE_FAILED_STD');
658 fnd_message.set_token('FAIL_DLHE', p_fail_dlhe_cnt);
659 fnd_message.set_token('RETURN_NAME',p_return_name);
660 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
661 fnd_file.put_line(fnd_file.log, fnd_message.get);
662
663 -- Report the total number of students have the student DLHE records with open qualifying period,
664 -- for them not required to modify student DLHE record.
665 fnd_message.set_name('IGS','IGS_HE_DLHE_NOT_MODIFIED');
666 fnd_message.set_token('NOT_MOD', p_not_mod_dlhe_cnt);
667 fnd_message.set_token('RETURN_NAME',p_return_name);
668 fnd_message.set_token('QUAL_PERIOD',p_qual_period);
669 fnd_file.put_line(fnd_file.log, fnd_message.get);
670
671 EXCEPTION
672 WHEN OTHERS THEN
673 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
674 Fnd_Message.Set_Token('NAME','igs_he_import_popdlhe.log_results - ' ||SQLERRM);
675 fnd_file.put_line(fnd_file.log, fnd_message.get);
676 App_Exception.Raise_Exception;
677
678 END log_results;
679
680 END igs_he_import_popdlhe;