[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_ACAD_HISTORY_PKG
Source
1 PACKAGE BODY Igs_Ad_Acad_History_Pkg AS
2 /* $Header: IGSAI81B.pls 120.10 2006/05/30 11:50:09 arvsrini ship $ */
3 PROCEDURE insert_row (
4 x_rowid IN OUT NOCOPY VARCHAR2,
5 x_attribute14 IN VARCHAR2,
6 x_attribute15 IN VARCHAR2,
7 x_attribute16 IN VARCHAR2,
8 x_attribute17 IN VARCHAR2,
9 x_attribute18 IN VARCHAR2,
10 x_attribute19 IN VARCHAR2,
11 x_attribute20 IN VARCHAR2,
12 x_attribute13 IN VARCHAR2,
13 x_attribute11 IN VARCHAR2,
14 x_attribute12 IN VARCHAR2,
15 x_education_id OUT NOCOPY NUMBER,
16 x_person_id IN NUMBER,
17 x_current_inst IN VARCHAR2,
18 x_degree_attempted IN VARCHAR2,
19 x_program_code IN VARCHAR2,
20 x_degree_earned IN VARCHAR2,
21 x_comments IN VARCHAR2,
22 x_start_date IN DATE,
23 x_end_date IN DATE,
24 x_planned_completion_date IN DATE,
25 x_recalc_total_cp_attempted IN NUMBER,
26 x_recalc_total_cp_earned IN NUMBER,
27 x_recalc_total_unit_gp IN NUMBER,
28 x_recalc_tot_gpa_units_attemp IN NUMBER,
29 x_recalc_inst_gpa IN VARCHAR2,
30 x_recalc_grading_scale_id IN NUMBER,
31 x_selfrep_total_cp_attempted IN NUMBER,
32 x_selfrep_total_cp_earned IN NUMBER,
33 x_selfrep_total_unit_gp IN NUMBER,
34 X_selfrep_tot_gpa_uts_attemp IN NUMBER,
35 x_selfrep_inst_gpa IN VARCHAR2,
36 x_selfrep_grading_scale_id IN NUMBER,
37 x_selfrep_weighted_gpa IN VARCHAR2,
38 x_selfrep_rank_in_class IN NUMBER,
39 x_selfrep_weighed_rank IN VARCHAR2,
40 x_type_of_school IN VARCHAR2,
41 x_institution_code IN VARCHAR2,
42 x_attribute_category IN VARCHAR2,
43 x_attribute1 IN VARCHAR2,
44 x_attribute2 IN VARCHAR2,
45 x_attribute3 IN VARCHAR2,
46 x_attribute4 IN VARCHAR2,
47 x_attribute5 IN VARCHAR2,
48 x_attribute6 IN VARCHAR2,
49 x_attribute7 IN VARCHAR2,
50 x_attribute8 IN VARCHAR2,
51 x_attribute9 IN VARCHAR2,
52 x_attribute10 IN VARCHAR2,
53 x_selfrep_class_size IN NUMBER DEFAULT NULL,
54 x_transcript_required IN VARCHAR2 DEFAULT NULL,
55 x_school_party_id IN NUMBER,
56 x_status IN VARCHAR2,
57 x_object_version_number OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2,
60 x_mode IN VARCHAR2 ,
61 x_school_attended_name IN VARCHAR2 DEFAULT NULL,
62 x_program_type_attempted IN VARCHAR2 ,
63 x_program_type_earned IN VARCHAR2
64 )
65 AS
66 l_education_rec HZ_PERSON_INFO_V2PUB.EDUCATION_REC_TYPE;
67 lv_return_status VARCHAR2(1);
68 lv_msg_count NUMBER;
69 lv_msg_data VARCHAR2(200);
70 lv_education_id NUMBER;
71 lv_Hz_Acad_Hist_Id NUMBER;
72 l_RowId VARCHAR2(25);
73 lv_institution_code HZ_PARTIES.party_number%TYPE;
74 l_mode VARCHAR2(1);
75
76 CURSOR c_school_party_id (p_party_number hz_parties.party_number%TYPE) IS
77 SELECT party_id
78 FROM igs_pe_hz_parties
79 WHERE oss_org_unit_cd = p_party_number;
80
81 CURSOR c_school_attended_name (p_party_number hz_parties.party_number%TYPE) IS
82 SELECT party_name
83 FROM hz_parties hz, igs_pe_hz_parties php
84 WHERE php.oss_org_unit_cd = p_party_number
85 AND php.party_id = hz.party_id ;
86
87 CURSOR c_degree_code (p_degree hz_education.degree_received%TYPE) IS
88 SELECT dg.degree_cd
89 FROM igs_ps_degrees dg,
90 igs_ps_type_all ps
91 WHERE dg.degree_cd = p_degree
92 AND dg.closed_ind ='N'
93 AND dg.program_type = ps.course_type;
94
95 CURSOR get_dob_dt_cur(p_person_id igs_pe_hz_parties.party_id%TYPE)
96 IS
97 SELECT birth_date
98 FROM igs_pe_person_base_v
99 WHERE person_id = p_person_id;
100
101 tmp_var1 VARCHAR2(2000);
102 tmp_var VARCHAR2(2000);
103 lv_school_party_id hz_education.school_party_id%TYPE;
104 lv_school_attended_name hz_parties.party_name%TYPE;
105 lv_degree hz_education.degree_received%TYPE;
106 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
107 /*
108 || Created By : [email protected]
109 || Created On : 07-SEP-2000
110 || Purpose : Handles the INSERT DML logic for the table.
111 || Known limitations, enhancements or remarks :
112 || Change History :
113 || Who When What
114 || (reverse chronological order - newest change first)
115 || samaresh.in 20-NOV-2001 Added a Check to see if
116 || current institution already exists
117 || for the person id
118 || vdixit.in 23-JULY-2001 Added new column transcript_required
119 || to the tbh calls
120 || pkpatel 30-Jun-2005 Bug 4327807 (Person SS Enhancement)
121 || Removed the check for single current institution. Multiple current institutions will be allowed.
122 || Removed the cursor to get the object version number. In insert its not needed.
123 */
124 BEGIN
125 l_mode := NVL(x_mode ,'R');
126
127 lv_school_party_id := x_school_party_id;
128 l_education_rec.education_id := NULL;
129 l_education_rec.course_major := x_Program_Code;
130 l_education_rec.last_date_attended := x_End_Date;
131 l_education_rec.type_of_school := x_type_of_School;
132 l_education_rec.party_id := x_person_id;
133 l_education_rec.start_date_attended := x_Start_Date;
134 l_education_rec.Degree_received := x_degree_earned;
135 l_education_rec.school_attended_name := NULL ;
136 l_education_rec.status := NVL(x_status,'A');
137 l_education_rec.created_by_module := 'IGS';
138 l_education_rec.application_id := NULL;
139
140 --For UK Degree Earned and Degree Attempted should be null
141 IF NVL(FND_PROFILE.VALUE('OSS_COUNTRY_CODE'),'*') = 'GB'
142 AND (x_degree_attempted IS NOT NULL
143 OR x_degree_earned IS NOT NULL
144 OR x_program_code IS NOT NULL) THEN
145 Fnd_Message.Set_Name ('IGS', 'IGS_AD_GB_ACAD_HIST');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149
150 IF x_school_attended_name IS NOT NULL THEN
151 OPEN c_school_attended_name(x_institution_code);
152 FETCH c_school_attended_name INTO lv_school_attended_name;
153 CLOSE c_school_attended_name;
154 IF lv_school_attended_name <> x_school_attended_name THEN
155 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_INST');
156 IGS_GE_MSG_STACK.ADD;
157 App_Exception.Raise_Exception;
158 END IF;
159 END IF;
160
161 --Validate Degree Earned
162 IF x_degree_earned IS NOT NULL THEN
163 OPEN c_degree_code(x_degree_earned);
164 lv_degree := NULL;
165 FETCH c_degree_code INTO lv_degree;
166 CLOSE c_degree_code;
167 IF lv_degree IS NULL THEN
168 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_DEG_EARNED');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173
174 --Validate Degree Attempted
175 IF x_degree_attempted IS NOT NULL THEN
176 OPEN c_degree_code(x_degree_attempted);
177 lv_degree := NULL;
178 FETCH c_degree_code INTO lv_degree;
179 CLOSE c_degree_code;
180 IF lv_degree IS NULL THEN
181 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_DEG_ATTEMPTED');
182 IGS_GE_MSG_STACK.ADD;
183 App_Exception.Raise_Exception;
184 END IF;
185 END IF;
186
187 -- Validate Start Date and End Date
188 IF x_start_date IS NOT NULL AND x_end_date IS NOT NULL THEN
189 IF x_end_date < x_start_date THEN
190 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_DATE');
191 IGS_GE_MSG_STACK.ADD;
192 APP_EXCEPTION.RAISE_EXCEPTION;
193 END IF;
194 END IF;
195
196 --Validate Degree Earned earlier than Date of Birth
197 IF x_start_date IS NOT NULL THEN
198 OPEN get_dob_dt_cur(x_person_id);
199 FETCH get_dob_dt_cur INTO l_birth_dt;
200 CLOSE get_dob_dt_cur;
201 IF l_birth_dt IS NOT NULL AND l_birth_dt > l_education_rec.start_date_attended THEN
202 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
203 IGS_GE_MSG_STACK.ADD;
204 APP_EXCEPTION.RAISE_EXCEPTION;
205 END IF;
206 END IF;
207
208 --Validate Degree Earned earlier than Date of Birth
209 IF x_start_date IS NOT NULL OR x_end_date IS NOT NULL THEN
210
211 OPEN get_dob_dt_cur(x_person_id);
212 FETCH get_dob_dt_cur INTO l_birth_dt;
213 CLOSE get_dob_dt_cur;
214 IF l_birth_dt IS NOT NULL AND x_start_date IS NOT NULL AND l_birth_dt > x_start_date THEN
215 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
216 IGS_GE_MSG_STACK.ADD;
217 APP_EXCEPTION.RAISE_EXCEPTION;
218 END IF;
219
220 IF l_birth_dt IS NOT NULL AND x_end_date IS NOT NULL AND l_birth_dt > x_end_date THEN
221 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_ENDDT_LESS_BIRTHDT');
222 IGS_GE_MSG_STACK.ADD;
223 APP_EXCEPTION.RAISE_EXCEPTION;
224 END IF;
225 END IF;
226
227 --If institution_code is passed then
228 IF ( x_school_party_id is NULL
229 AND x_institution_code IS NOT NULL ) THEN
230 OPEN c_school_party_id(x_institution_code);
231 FETCH c_school_party_id INTO lv_school_party_id ;
232 CLOSE c_school_party_id;
233 END IF;
234 l_education_rec.school_party_id := lv_school_party_id;
235
236 hz_person_info_v2pub.create_education(
237 p_init_msg_list => FND_API.G_TRUE,
238 p_education_rec => l_education_rec,
239 x_return_status => lv_return_status,
240 x_msg_count => lv_msg_count,
241 x_msg_data => lv_msg_data,
242 x_education_id => lv_education_id );
243
244 x_education_id := lv_education_id;
245
246 IF lv_return_Status IN ('E','U') THEN
247 -- bug 2338473 logic to display more than one error modified.
248 IF lv_msg_count > 1 THEN
249 FOR i IN 1..lv_msg_count LOOP
250 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
251 tmp_var1 := tmp_var1 || ' '|| tmp_var;
252 END LOOP;
253 x_msg_data := tmp_var1;
254 x_return_status :=lv_return_status;
255 END IF;
256 RETURN;
257 ELSE
258 Igs_Ad_Hz_Acad_Hist_Pkg.Insert_Row
259 ( X_ROWID => l_RowId,
260 X_RECALC_TOTAL_CP_ATTEMPTED => X_RECALC_TOTAL_CP_ATTEMPTED,
261 X_RECALC_TOTAL_CP_EARNED => X_RECALC_TOTAL_CP_EARNED,
262 X_RECALC_TOTAL_UNIT_GP => X_RECALC_TOTAL_UNIT_GP,
263 X_RECALC_TOT_GPA_UTS_ATTEMPTED => x_recalc_tot_gpa_units_attemp,
264 X_RECALC_INST_GPA => X_RECALC_INST_GPA,
265 X_RECALC_GRADING_SCALE_ID => X_RECALC_GRADING_SCALE_ID,
266 X_SELFREP_TOTAL_CP_ATTEMPTED => X_SELFREP_TOTAL_CP_ATTEMPTED,
267 X_SELFREP_TOTAL_CP_EARNED => X_SELFREP_TOTAL_CP_EARNED,
268 X_SELFREP_TOTAL_UNIT_GP => X_SELFREP_TOTAL_UNIT_GP,
269 X_SELFREP_TOT_GPA_UTS_ATTEMP => X_SELFREP_TOT_GPA_UTS_ATTEMP,
270 X_SELFREP_INST_GPA => X_SELFREP_INST_GPA,
271 X_SELFREP_GRADING_SCALE_ID => X_SELFREP_GRADING_SCALE_ID,
272 X_SELFREP_WEIGHTED_GPA => X_SELFREP_WEIGHTED_GPA,
273 X_SELFREP_RANK_IN_CLASS => X_SELFREP_RANK_IN_CLASS,
274 X_SELFREP_WEIGHED_RANK => X_SELFREP_WEIGHED_RANK,
275 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
276 X_SELFREP_CLASS_SIZE => X_SELFREP_CLASS_SIZE,
277 X_ATTRIBUTE1 => X_ATTRIBUTE1,
278 X_ATTRIBUTE2 => X_ATTRIBUTE2,
279 X_ATTRIBUTE3 => X_ATTRIBUTE3,
280 X_ATTRIBUTE4 => X_ATTRIBUTE4,
281 X_ATTRIBUTE5 => X_ATTRIBUTE5,
282 X_ATTRIBUTE6 => X_ATTRIBUTE6,
283 X_ATTRIBUTE7 => X_ATTRIBUTE7,
284 X_ATTRIBUTE8 => X_ATTRIBUTE8,
285 X_ATTRIBUTE9 => X_ATTRIBUTE9,
286 X_ATTRIBUTE10 => X_ATTRIBUTE10,
287 X_ATTRIBUTE11 => X_ATTRIBUTE11,
288 X_ATTRIBUTE12 => X_ATTRIBUTE12,
289 X_ATTRIBUTE13 => X_ATTRIBUTE13,
290 X_ATTRIBUTE14 => X_ATTRIBUTE14,
291 X_ATTRIBUTE15 => X_ATTRIBUTE15,
292 X_ATTRIBUTE16 => X_ATTRIBUTE16,
293 X_ATTRIBUTE17 => X_ATTRIBUTE17,
294 X_ATTRIBUTE18 => X_ATTRIBUTE18,
295 X_ATTRIBUTE19 => X_ATTRIBUTE19,
296 X_ATTRIBUTE20 => X_ATTRIBUTE20,
297 X_HZ_ACAD_HIST_ID => lv_Hz_Acad_Hist_Id,
298 X_EDUCATION_ID => lv_Education_Id,
299 X_CURRENT_INST => X_CURRENT_INST,
300 X_DEGREE_ATTEMPTED => X_DEGREE_ATTEMPTED,
301 X_COMMENTS => X_COMMENTS,
302 X_PLANNED_COMPLETION_DATE => X_PLANNED_COMPLETION_DATE,
303 X_TRANSCRIPT_REQUIRED => X_TRANSCRIPT_REQUIRED ,
304 X_MODE => 'R');
305 --x_rowid := l_rowId; This line is removed as part of Bug-4089200 fix
306 END IF;
307 -- raise the business event
308 igs_ad_wf_001.ACADHIST_CRT_EVENT
309 (
310 P_HZ_ACAD_HIST_ID => lv_Hz_Acad_Hist_Id,
311 P_EDUCATION_ID => lv_Education_Id,
312 P_PERSON_ID => x_person_id,
313 P_ACTIVE_IND => NVL(x_status,'A'),
314 P_REQUIRED_IND => x_transcript_required
315 );
316
317 END insert_row;
318
319 PROCEDURE update_row (
320 x_rowid IN VARCHAR2,
321 x_attribute14 IN VARCHAR2,
322 x_attribute15 IN VARCHAR2,
323 x_attribute16 IN VARCHAR2,
324 x_attribute17 IN VARCHAR2,
325 x_attribute18 IN VARCHAR2,
326 x_attribute19 IN VARCHAR2,
327 x_attribute20 IN VARCHAR2,
328 x_attribute13 IN VARCHAR2,
329 x_attribute11 IN VARCHAR2,
330 x_attribute12 IN VARCHAR2,
331 x_education_id IN NUMBER,
332 x_person_id IN NUMBER,
333 x_current_inst IN VARCHAR2,
334 x_degree_attempted IN VARCHAR2,
335 x_program_code IN VARCHAR2,
336 x_degree_earned IN VARCHAR2,
337 x_comments IN VARCHAR2,
338 x_start_date IN DATE,
339 x_end_date IN DATE,
340 x_planned_completion_date IN DATE,
341 x_recalc_total_cp_attempted IN NUMBER,
342 x_recalc_total_cp_earned IN NUMBER,
343 x_recalc_total_unit_gp IN NUMBER,
347 x_selfrep_total_cp_attempted IN NUMBER,
344 x_recalc_tot_gpa_units_attemp IN NUMBER,
345 x_recalc_inst_gpa IN VARCHAR2,
346 x_recalc_grading_scale_id IN NUMBER,
348 x_selfrep_total_cp_earned IN NUMBER,
349 x_selfrep_total_unit_gp IN NUMBER,
350 x_selfrep_tot_gpa_uts_attemp IN NUMBER,
351 x_selfrep_inst_gpa IN VARCHAR2,
352 x_selfrep_grading_scale_id IN NUMBER,
353 x_selfrep_weighted_gpa IN VARCHAR2,
354 x_selfrep_rank_in_class IN NUMBER,
355 x_selfrep_weighed_rank IN VARCHAR2,
356 x_type_of_school IN VARCHAR2,
357 x_institution_code IN VARCHAR2,
358 x_attribute_category IN VARCHAR2,
359 x_attribute1 IN VARCHAR2,
360 x_attribute2 IN VARCHAR2,
361 x_attribute3 IN VARCHAR2,
362 x_attribute4 IN VARCHAR2,
363 x_attribute5 IN VARCHAR2,
364 x_attribute6 IN VARCHAR2,
365 x_attribute7 IN VARCHAR2,
366 x_attribute8 IN VARCHAR2,
367 x_attribute9 IN VARCHAR2,
368 x_attribute10 IN VARCHAR2,
369 x_selfrep_class_size IN NUMBER DEFAULT NULL,
370 x_transcript_required IN VARCHAR2 DEFAULT NULL,
371 x_school_party_id IN NUMBER,
372 x_status IN VARCHAR2,
373 x_object_version_number IN OUT NOCOPY NUMBER,
374 x_msg_data OUT NOCOPY VARCHAR2,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_mode IN VARCHAR2 ,
377 x_school_attended_name IN VARCHAR2 DEFAULT NULL,
378 x_program_type_attempted IN VARCHAR2 ,
379 x_program_type_earned IN VARCHAR2
380 ) AS
381 /*
382 || Created By : [email protected]
383 || Created On : 07-SEP-2000
384 || Purpose : Handles the UPDATE DML logic for the table.
385 || Known limitations, enhancements or remarks :
386 || Change History :
387 || Who When What
388 || (reverse chronological order - newest change first)
389 ||
390 || apadegal.in 10-Jun-2005 Modified the validation logic for
391 || UK Degree Earned and Degree Attempted
392 ||
393 || samaresh.in 20-NOV-2001 Added a Check to see if
394 || current institution already exists
395 || for the person id
396 || vdixit.in 23-JULY-2001 Added new column transcript_required
397 || to the tbh calls
398 || pkpatel 30-Jun-2005 Bug 4327807 (Person SS Enhancement)
399 || Removed the check for single current institution. Multiple current institutions will be allowed.
400 || Removed the cursor to get the Created_By_Module. In update no need to pass it.
401 ||
402 */
403
404 l_education_rec hz_person_info_v2pub.education_rec_type;
405 lv_return_status VARCHAR2(1);
406 lv_msg_count NUMBER;
407 lv_msg_data VARCHAR2(200);
408 lv_education_id NUMBER;
409 lv_Hz_Acad_Hist_Id NUMBER;
410 l_RowId VARCHAR2(25);
411 lv_institution_code HZ_PARTIES.party_number%TYPE;
412 lv_created_by_module VARCHAR2(255);
413 tmp_var1 VARCHAR2(2000);
414 tmp_var VARCHAR2(2000);
415 l_mode VARCHAR2(1);
416 -- variable added by ravishar
417 l_active_ind VARCHAR2(1);
418 l_transcript_required_old VARCHAR2(1);
419
420 --new cursor to get old active indicator
421 CURSOR c_old_active_ind(cp_rowid IN VARCHAR2) IS
422 SELECT status,TRANSCRIPT_REQUIRED from IGS_AD_ACAD_HISTORY_V
423 WHERE row_id = cp_rowid;
424
425 CURSOR C1 IS
426 SELECT ROWID, HZ_ACAD_HIST_ID
427 FROM IGS_AD_HZ_ACAD_HIST
428 WHERE EDUCATION_ID = x_Education_Id;
429
430 CURSOR c_school_party_id (p_party_number hz_parties.party_number%TYPE) IS
431 SELECT party_id
432 FROM igs_pe_hz_parties
433 WHERE oss_org_unit_cd = p_party_number;
434
435 CURSOR c_school_attended_name (p_party_number hz_parties.party_number%TYPE) IS
436 SELECT party_name
437 FROM hz_parties hz, igs_pe_hz_parties php
438 WHERE php.oss_org_unit_cd = p_party_number
439 AND php.party_id = hz.party_id ;
440
441 CURSOR c_adv_standing ( p_party_number hz_parties.party_number%TYPE, p_person_id hz_parties.party_id%TYPE) IS
442 SELECT '1'
443 FROM igs_av_adv_standing
444 WHERE person_id = p_person_id
445 AND exemption_institution_cd = p_party_number;
446
447 CURSOR c_degree_code (p_degree hz_education.degree_received%TYPE) IS
448 SELECT dg.degree_cd
449 FROM igs_ps_degrees dg
450 WHERE dg.degree_cd = p_degree
451 AND dg.closed_ind ='N'
452 AND dg.program_type IS NOT NULL;
453
454 CURSOR c_old_references IS
455 SELECT degree_attempted, degree_earned, program_code
456 FROM IGS_AD_ACAD_HISTORY_V ach
457 WHERE ach.education_id = x_education_id;
458
459 CURSOR get_dob_dt_cur(p_person_id igs_pe_hz_parties.party_id%TYPE)
460 IS
461 SELECT birth_date
465 --jchin Bug 4629226 - Cursor added to check external transcripts
462 FROM igs_pe_person_base_v
463 WHERE person_id = p_person_id;
464
466
467 CURSOR cur_check_external(p_education_id igs_ad_acad_history_v.education_id%TYPE)
468 IS
469 SELECT DISTINCT 1
470 FROM igs_ad_transcript_v trans, igs_ad_code_classes code
471 WHERE trans.transcript_source = code.code_id
472 AND trans.education_id = p_education_id
473 AND code.class = 'TRANSCRIPT_SOURCE'
474 AND code.class_type_code = 'ADM_CODE_CLASSES'
475 AND code.system_status = 'THIRD_PARTY_TRANSFER_EVAL';
476
477
478 old_degree_attempted IGS_AD_ACAD_HISTORY_V.degree_attempted%TYPE;
479 old_degree_earned IGS_AD_ACAD_HISTORY_V.degree_earned%TYPE;
480 old_program_code IGS_AD_ACAD_HISTORY_V.program_code%TYPE;
481
482 lv_adv_standing_exists VARCHAR2(1) := NULL ;
483 lv_school_attended_name hz_parties.party_name%TYPE;
484 lv_school_party_id hz_education.school_party_id%TYPE ;
485 lv_degree hz_education.degree_received%TYPE;
486 l_birth_dt igs_pe_person_base_v.birth_date%TYPE;
487
488 l_count NUMBER; --jchin Bug 4629226
489
490 BEGIN
491
492 l_mode := NVL(x_mode,'R');
493
494 lv_school_party_id := x_school_party_id;
495 --For UK Degree Earned and Degree Attempted should be null
496
497 OPEN c_old_references;
498 FETCH c_old_references INTO old_degree_attempted,old_degree_earned,old_program_code;
499 CLOSE c_old_references;
500
501 IF NVL(FND_PROFILE.VALUE('OSS_COUNTRY_CODE'),'*') = 'GB'
502 AND ( nvl(old_degree_attempted,'NulL') <> nvl(x_degree_attempted,'NulL') or
503 nvl(old_degree_earned,'NulL') <> nvl(x_degree_earned,'NulL') or
504 nvl(old_program_code,'NulL') <> nvl(x_program_code,'NulL')
505 )
506 THEN
507 Fnd_Message.Set_Name ('IGS', 'IGS_AD_GB_ACAD_HIST');
508 IGS_GE_MSG_STACK.ADD;
509 App_Exception.Raise_Exception;
510 END IF;
511
512
513 IF x_school_attended_name IS NOT NULL AND x_institution_code IS NOT NULL THEN
514 OPEN c_school_attended_name(x_institution_code);
515 FETCH c_school_attended_name INTO lv_school_attended_name;
516 CLOSE c_school_attended_name;
517 IF lv_school_attended_name <> x_school_attended_name THEN
518 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_INST');
519 IGS_GE_MSG_STACK.ADD;
520 App_Exception.Raise_Exception;
521 END IF;
522 END IF;
523
524 --Validate Degree Earned
528 FETCH c_degree_code INTO lv_degree;
525 IF x_degree_earned IS NOT NULL THEN
526 OPEN c_degree_code(x_degree_earned);
527 lv_degree := NULL;
529 CLOSE c_degree_code;
530 IF lv_degree IS NULL THEN
531 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_DEGREE_EARNED');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception;
534 END IF;
535 END IF;
536
537 -- Validate Start Date and End Date
538 IF x_start_date IS NOT NULL AND x_end_date IS NOT NULL THEN
539 IF x_end_date < x_start_date THEN
540 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_INVALID_DATE');
541 IGS_GE_MSG_STACK.ADD;
542 APP_EXCEPTION.RAISE_EXCEPTION;
543 END IF;
544 END IF;
545
546 --Validate Degree Earned earlier than Date of Birth
547 IF x_start_date IS NOT NULL OR x_end_date IS NOT NULL THEN
548
549 OPEN get_dob_dt_cur(x_person_id);
550 FETCH get_dob_dt_cur INTO l_birth_dt;
551 CLOSE get_dob_dt_cur;
552 IF l_birth_dt IS NOT NULL AND x_start_date IS NOT NULL AND l_birth_dt > x_start_date THEN
553 FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_STRT_DT_LESS_BIRTH_DT');
554 IGS_GE_MSG_STACK.ADD;
555 APP_EXCEPTION.RAISE_EXCEPTION;
556 END IF;
557
558 IF l_birth_dt IS NOT NULL AND x_end_date IS NOT NULL AND l_birth_dt > x_end_date THEN
559 FND_MESSAGE.SET_NAME ('IGS', 'IGS_PE_ENDDT_LESS_BIRTHDT');
560 IGS_GE_MSG_STACK.ADD;
561 APP_EXCEPTION.RAISE_EXCEPTION;
562 END IF;
563 END IF;
564
565 --Validate Degree Attempted
566 IF x_degree_attempted IS NOT NULL THEN
567 OPEN c_degree_code(x_degree_attempted);
568 lv_degree := NULL;
569 FETCH c_degree_code INTO lv_degree;
570 CLOSE c_degree_code;
571 IF lv_degree IS NULL THEN
572 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INVALID_DEGREE_ATTEMPTED');
573 IGS_GE_MSG_STACK.ADD;
574 App_Exception.Raise_Exception;
575 END IF;
576 END IF;
577
578 IF x_status = 'I' THEN
579 OPEN c_adv_standing(x_institution_code, x_person_id);
580 FETCH c_adv_standing INTO lv_adv_standing_exists;
581 CLOSE c_adv_standing;
582 IF lv_adv_standing_exists = '1' THEN
583 Fnd_Message.Set_Name ('IGS', 'IGS_AD_ADV_STD_DTLS_EXTS');
584 IGS_GE_MSG_STACK.ADD;
585 App_Exception.Raise_Exception;
586 END IF;
587 END IF;
588 --If institution_code is passed then
589 IF ( x_school_party_id is NULL
590 AND x_institution_code IS NOT NULL ) THEN
591 OPEN c_school_party_id(x_institution_code);
592 FETCH c_school_party_id INTO lv_school_party_id ;
593 CLOSE c_school_party_id;
594 END IF;
595
596 l_education_rec.school_party_id := lv_school_party_id;
597 IF lv_school_party_id IS NOT NULL THEN
598 l_education_rec.school_attended_name := chr(0);
599 ELSE
600 l_education_rec.school_attended_name := x_school_attended_name;
601 END IF;
602 l_education_rec.course_major := NVL(x_Program_Code,FND_API.G_MISS_CHAR);
603 l_education_rec.last_date_attended := NVL(x_End_Date, FND_API.G_MISS_DATE);
604 l_education_rec.type_of_school := x_type_of_School;
605 l_education_rec.party_id := x_person_id;
606 l_education_rec.start_date_attended := NVL(x_Start_Date, FND_API.G_MISS_DATE);
607 l_education_rec.Education_Id := x_Education_Id;
608 l_education_rec.Degree_received := NVL(x_degree_earned,FND_API.G_MISS_CHAR) ;
609 l_education_rec.status := x_status;
610
611
612 OPEN c_old_active_ind(x_rowid);
613 FETCH c_old_active_ind into l_active_ind,l_transcript_required_old;
614 CLOSE c_old_active_ind;
615
616 --jchin Bug 4629226 added check to prevent INACTIVE academic history records from being updated.
617 l_count := null;
618
619 OPEN cur_check_external(x_Education_Id);
620 FETCH cur_check_external INTO l_count;
621 CLOSE cur_check_external;
622
623 IF l_active_ind = 'I' THEN
624
625 IF nvl(x_status, 'I') = 'I' OR (x_status = 'A' AND l_count IS NOT NULL) THEN
626 Fnd_Message.Set_Name ('IGS', 'IGS_AD_INACTIVE_ACAD_HIST');
627 IGS_GE_MSG_STACK.ADD;
628 App_Exception.Raise_Exception;
629 END IF;
630
631 END IF;
632
633
634 --Update HZ_EDUCATION table by calling this proc hz_per_info_pub.Update_education
635 hz_person_info_v2pub.Update_education(
636 p_init_msg_list => FND_API.G_TRUE,
637 p_education_rec => l_education_rec,
638 p_object_version_number => x_object_version_number,
639 x_return_status => lv_return_status,
640 x_msg_count => lv_msg_count,
641 x_msg_data => lv_msg_data );
642
643 x_return_status := lv_return_status;
644 x_msg_data := lv_msg_data;
645
646 IF lv_return_Status IN ('E','U') THEN
647 -- bug 2338473 logic to display more than one error modified.
648 IF lv_msg_count > 1 THEN
649 FOR i IN 1..lv_msg_count LOOP
650 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
651 tmp_var1 := tmp_var1 || ' '|| tmp_var;
652 END LOOP;
653 x_msg_data := tmp_var1;
654 x_return_status :=lv_return_status;
655 END IF;
656 RETURN;
657 ELSE
658 OPEN C1;
659 FETCH C1 INTO l_RowId, lv_Hz_Acad_Hist_Id;
660 CLOSE C1;
661
662 Igs_Ad_Hz_Acad_Hist_Pkg.add_Row
663 ( X_ROWID => l_RowId,
664 X_RECALC_TOTAL_CP_ATTEMPTED => X_RECALC_TOTAL_CP_ATTEMPTED,
665 X_RECALC_TOTAL_CP_EARNED => X_RECALC_TOTAL_CP_EARNED,
666 X_RECALC_TOTAL_UNIT_GP => X_RECALC_TOTAL_UNIT_GP,
667 X_RECALC_TOT_GPA_UTS_ATTEMPTED => x_recalc_tot_gpa_units_attemp,
668 X_RECALC_INST_GPA => X_RECALC_INST_GPA,
669 X_RECALC_GRADING_SCALE_ID => X_RECALC_GRADING_SCALE_ID,
670 X_SELFREP_TOTAL_CP_ATTEMPTED => X_SELFREP_TOTAL_CP_ATTEMPTED,
671 X_SELFREP_TOTAL_CP_EARNED => X_SELFREP_TOTAL_CP_EARNED,
672 X_SELFREP_TOTAL_UNIT_GP => X_SELFREP_TOTAL_UNIT_GP,
673 X_SELFREP_TOT_GPA_UTS_ATTEMP => X_SELFREP_TOT_GPA_UTS_ATTEMP,
674 X_SELFREP_INST_GPA => X_SELFREP_INST_GPA,
675 X_SELFREP_GRADING_SCALE_ID => X_SELFREP_GRADING_SCALE_ID,
676 X_SELFREP_WEIGHTED_GPA => X_SELFREP_WEIGHTED_GPA,
677 X_SELFREP_RANK_IN_CLASS => X_SELFREP_RANK_IN_CLASS,
678 X_SELFREP_WEIGHED_RANK => X_SELFREP_WEIGHED_RANK,
679 X_SELFREP_CLASS_SIZE => X_SELFREP_CLASS_SIZE,
680 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
681 X_ATTRIBUTE1 => X_ATTRIBUTE1,
682 X_ATTRIBUTE2 => X_ATTRIBUTE2,
683 X_ATTRIBUTE3 => X_ATTRIBUTE3,
684 X_ATTRIBUTE4 => X_ATTRIBUTE4,
685 X_ATTRIBUTE5 => X_ATTRIBUTE5,
686 X_ATTRIBUTE6 => X_ATTRIBUTE6,
687 X_ATTRIBUTE7 => X_ATTRIBUTE7,
688 X_ATTRIBUTE8 => X_ATTRIBUTE8,
689 X_ATTRIBUTE9 => X_ATTRIBUTE9,
690 X_ATTRIBUTE10 => X_ATTRIBUTE10,
691 X_ATTRIBUTE11 => X_ATTRIBUTE11,
692 X_ATTRIBUTE12 => X_ATTRIBUTE12,
693 X_ATTRIBUTE13 => X_ATTRIBUTE13,
694 X_ATTRIBUTE14 => X_ATTRIBUTE14,
695 X_ATTRIBUTE15 => X_ATTRIBUTE15,
696 X_ATTRIBUTE16 => X_ATTRIBUTE16,
697 X_ATTRIBUTE17 => X_ATTRIBUTE17,
698 X_ATTRIBUTE18 => X_ATTRIBUTE18,
699 X_ATTRIBUTE19 => X_ATTRIBUTE19,
700 X_ATTRIBUTE20 => X_ATTRIBUTE20,
701 X_HZ_ACAD_HIST_ID => lv_Hz_Acad_Hist_Id,
702 X_EDUCATION_ID => x_Education_Id,
703 X_CURRENT_INST => NVL(X_CURRENT_INST,'N'),
704 X_DEGREE_ATTEMPTED => X_DEGREE_ATTEMPTED,
705 X_COMMENTS => X_COMMENTS,
706 X_PLANNED_COMPLETION_DATE => X_PLANNED_COMPLETION_DATE,
707 X_TRANSCRIPT_REQUIRED => X_TRANSCRIPT_REQUIRED,
708 X_MODE => 'R');
709 END IF;
710
711 --raise business event
712 igs_ad_wf_001.ACADHIST_UPD_EVENT
713 (
714 P_HZ_ACAD_HIST_ID => lv_Hz_Acad_Hist_Id,
715 P_EDUCATION_ID => x_Education_Id,
716 P_PERSON_ID => x_person_id,
717 P_ACTIVE_IND_OLD => l_active_ind,
718 P_ACTIVE_IND_NEW => NVL(x_status,l_active_ind),
719 P_REQUIRED_IND_NEW => x_transcript_required,
720 P_REQUIRED_IND_OLD => l_transcript_required_old
721 );
722 END update_row;
723 END Igs_Ad_Acad_History_Pkg;