1 PACKAGE BODY igf_sl_dl_validation AS
2 /* $Header: IGFSL02B.pls 120.7 2006/04/26 06:59:16 rajagupt ship $ */
3 --------------------------------------------------------------------------
4 -- museshad 28-Nov-2005 Bug 4116399.
5 -- Added Stafford loan limit validation check in
6 -- cod_loan_validations() and dl_lar_validate()
7 --------------------------------------------------------------------------
8 -- veramach 04-May-2004 bug 3603289
9 -- (a) Modified cursor cur_student_licence to select
10 -- dependency_status from ISIR. other details are
11 -- derived from igf_sl_gen.get_person_details.
12 -- (b) Modified logic dl_lar_validate so that the loop
13 -- executes whenever called from FORM.
14 --------------------------------------------------------------------------
15 -- sjadhav, Jan 26,2002
16 -- Bug 2154941
17 -- Modified usages of cur_disb to reflect the completion
18 -- status of a loan
19 --
20 -------------------------------------------------------------------------
21
22 p_dl_version igf_lookups_view.lookup_code%TYPE;
23 g_loan_id igf_sl_Loans_all.loan_id%TYPE;
24 student_dtl_rec igf_sl_gen.person_dtl_rec;
25 parent_dtl_rec igf_sl_gen.person_dtl_rec;
26
27 CURSOR cur_disb(c_award_id igf_aw_awd_disb.award_id%TYPE) IS
28 SELECT COUNT(*) disb_count FROM igf_aw_awd_disb_all
29 WHERE award_id = c_award_id
30 AND ( nvl(disb_gross_amt,0) <= 0
31 OR nvl(fee_1 ,0) <= 0
32 OR nvl(disb_net_amt ,0) <= 0);
33
34 CURSOR cur_isir(p_base_id igf_ap_fa_base_rec.base_id%TYPE) is
35 SELECT isr.dependency_status s_depncy_status
36 FROM igf_ap_isir_matched_all isr
37 WHERE isr.base_id = p_base_id
38 AND isr.payment_isir = 'Y'
39 AND isr.system_record_type = 'ORIGINAL';
40
41 -- Cursor to get number of disbursements. COD-XML build.
42 CURSOR cur_get_no_of_disbursements ( cp_award_id igf_aw_awd_disb.award_id%TYPE) IS
43 SELECT COUNT(*) disb_count
44 FROM igf_aw_awd_disb_all disb
45 WHERE disb.award_id = cp_award_id;
46
47 CURSOR cur_special_school ( cp_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
48 cp_seq_number igf_ap_fa_base_rec.ci_sequence_number%TYPE) IS
49 SELECT dlsetup.special_school
50 FROM igf_sl_dl_setup_all dlsetup
51 WHERE dlsetup.ci_cal_type = cp_cal_type
52 AND dlsetup.ci_sequence_number = cp_seq_number;
53
54 CURSOR cur_isir_info (p_base_id NUMBER) IS
55 SELECT payment_isir,transaction_num,dependency_status,
56 date_of_birth,current_ssn,last_name
57 FROM igf_ap_isir_matched_all
58 WHERE base_id = p_base_id
59 AND payment_isir = 'Y'
60 AND system_record_type = 'ORIGINAL';
61
62 CURSOR cur_loan_at_cod ( cp_loan_number igf_sl_loans_all.loan_number%TYPE) IS
63 SELECT 'x'
64 FROM igf_sl_lor_loc_history
65 WHERE loan_number = cp_loan_number;
66
67 isir_info_rec cur_isir_info%ROWTYPE;
68
69
70 FUNCTION check_for_reqd(p_loan_number igf_sl_loans.loan_number%TYPE,
71 p_loan_catg igf_sl_reqd_fields.loan_type%TYPE,
72 p_field_name igf_sl_reqd_fields.field_name%TYPE,
73 p_field_value VARCHAR2
74 ) RETURN BOOLEAN
75 AS
76 /*************************************************************
77 Created By : venagara
78 Date Created On : 2000/11/07
79 Purpose :
80 Know limitations, enhancements or remarks
81 Change History
82 Who When What
83
84 (reverse chronological order - newest change first)
85 ***************************************************************/
86
87 lv_complete BOOLEAN := TRUE;
88 lv_err_type fnd_lookups.lookup_type%TYPE;
89 lv_data_reqd fnd_lookups.lookup_code%TYPE;
90 lv_data_recomm fnd_lookups.lookup_code%TYPE;
91
92 CURSOR cur_reqd IS
93 SELECT status from igf_sl_reqd_fields lrf
94 WHERE lrf.spec_version = p_dl_version
95 AND lrf.loan_type = p_loan_catg
96 AND lrf.field_name = p_field_name;
97
98 BEGIN
99
100 lv_err_type := 'IGF_SL_ERR_CODES';
101 lv_data_reqd := 'DATA_REQD';
102 lv_data_recomm := 'DATA_RECOMM';
103
104 IF p_field_name LIKE '%ADDR%' THEN
105 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
106 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.check_for_reqd.debug','p_field_name = ' ||p_field_name);
107 END IF;
108 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
109 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.check_for_reqd.debug','p_field_value = ' ||p_field_value);
110 END IF;
111 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
112 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.check_for_reqd.debug','p_dl_version = ' ||p_dl_version);
113 END IF;
114 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
115 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.check_for_reqd.debug','p_loan_catg = ' ||p_loan_catg);
116 END IF;
117 END IF;
118
119 IF TRIM(p_field_value) IS NULL THEN
120
121 FOR irec IN cur_reqd LOOP
122
123 IF irec.status = 'R' THEN
124 -- If the Data is required.
125 lv_complete := FALSE;
126 igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_reqd, p_field_name, p_field_value);
127
128 ELSIF irec.status = 'S' THEN
129 -- If the Data is strongly recommended.
130 igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_recomm, p_field_name, p_field_value);
131
132 END IF;
133
134 END LOOP;
135
136 END IF;
137
138 RETURN lv_complete;
139
140 EXCEPTION
141 WHEN others THEN
142 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
143 fnd_message.set_token('NAME','igf_sl_dl_validation.check_for_reqd');
144 igs_ge_msg_stack.add;
145 app_exception.raise_exception;
146 END check_for_reqd;
147
148 FUNCTION get_system_grade_level(p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
149 p_loan_per_begin_date IN DATE,
150 p_loan_per_end_date IN DATE
151 )
152 RETURN VARCHAR2
153 IS
154 /*************************************************************
155 Created By : Uday Kiran Reddy
156 Date Created On : May 05, 2005
157 Purpose :
158 Know limitations, enhancements or remarks
159 Change History
160 Who When What
161 ugummall 05-MAY-2005 Bug 4346421. <STUDENTLEVELCODE> tag in DL Outbound XML showing wrong value.
162 Matching override_grade_level_code/grade_level_code with system value.
163
164 (reverse chronological order - newest change first)
165 ***************************************************************/
166
167 CURSOR cur_get_person_id(cp_base_id NUMBER) IS
168 SELECT person_id
169 FROM IGF_AP_FA_BASE_REC_ALL
170 WHERE base_id = cp_base_id;
171 person_id_rec cur_get_person_id%ROWTYPE;
172
173 l_person_id NUMBER;
174 l_class_standing igs_pr_css_class_std_v.class_standing%TYPE := NULL;
175 l_course_cd VARCHAR2(10);
176 l_ver_number NUMBER;
177 l_course_type igs_ps_ver.course_type%TYPE;
178 l_cutoff_date DATE;
179 l_pred_flag VARCHAR2(1);
180 l_dl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
181 l_cl_std_code igf_ap_class_std_map.cl_std_code%TYPE;
182
183 BEGIN
184
185 l_class_standing := NULL;
186
187 OPEN cur_get_person_id (p_base_id);
188 FETCH cur_get_person_id INTO person_id_rec;
189 CLOSE cur_get_person_id;
190 l_person_id := person_id_rec.person_id;
191
192 -- Call generic API get_key_program to get the cource code
193 igf_ap_gen_001.get_key_program(p_base_id, l_course_cd, l_ver_number);
194 --igs_en_gen_015.get_academic_cal( l_person_id,l_course_cd,l_acad_cal_type,l_acad_seq_num,l_message,NULL);
195
196 l_cutoff_date := TRUNC(SYSDATE);
197
198 --1. If SYSDATE is before Loan Period Begin Date, then use Predictive Class Standing
199 IF l_cutoff_date < p_loan_per_begin_date THEN
200 l_pred_flag := 'Y';
201 l_cutoff_date := p_loan_per_begin_date;
202 --2. If SYSDATE is between Loan Period, then use Loan Period Begin Date as Effective Date to derive Grade Level
203 ELSIF l_cutoff_date < p_loan_per_end_date THEN
204 l_pred_flag := 'N';
205 l_cutoff_date := p_loan_per_begin_date;
206 --3. If SYSDATE is after Loan Period, then use Loan Period Begin Date as Effective Date to derive Grade Level
207 ELSE
208 l_pred_flag := 'N';
209 l_cutoff_date := p_loan_per_begin_date;
210 END IF;
211
212 l_class_standing := igs_pr_get_class_std.get_class_standing(
213 l_person_id,
214 l_course_cd,
215 l_pred_flag,
216 l_cutoff_date,
217 NULL,
218 NULL,'F');
219 l_course_type := igf_ap_gen_001.get_enrl_program_type(p_base_id);
220 igf_sl_lar_creation.get_dl_cl_std_code(p_base_id,
221 l_class_standing,
222 l_course_type,
223 l_dl_std_code,
224 l_cl_std_code);
225
226 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
227 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.get_system_grade_level.debug','System Grade Level is : ' || l_dl_std_code || ' - ' || igf_aw_gen.lookup_desc('IGF_AP_GRADE_LEVEL',l_dl_std_code ));
228 END IF;
229
230 RETURN l_dl_std_code;
231
232 END get_system_grade_level;
233
234 PROCEDURE loan_limit_validation (
235 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
236 p_fund_type IN igf_aw_fund_cat.fed_fund_code%TYPE,
237 p_award_id IN igf_aw_award_all.award_id%TYPE,
238 p_msg_name OUT NOCOPY fnd_new_messages.message_name%TYPE
239 )
240 IS
241 /***************************************************************
242 Created By : museshad
243 Date Created By : 23-Nov-2005
244 Purpose : Stafford loan limit validation
245 Known Limitations,Enhancements or Remarks
246 Change History :
247 Who When What
248 ***************************************************************/
249 l_aid NUMBER;
250
251 CURSOR c_get_dist_plan(cp_award_id igf_aw_award_all.award_id%TYPE)
252 IS
253 SELECT adplans_id
254 FROM igf_aw_award_all
255 WHERE award_id = cp_award_id;
256
257 l_dist_plan_rec c_get_dist_plan%ROWTYPE;
258
259 BEGIN
260 -- Log IN parameters
261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
262 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.loan_limit_validation.debug','IN Param p_base_id= ' ||p_base_id);
263 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.loan_limit_validation.debug','IN Param p_fund_type= ' ||p_fund_type);
264 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.loan_limit_validation.debug','IN Param p_award_id= ' ||p_award_id);
265 END IF;
266
267 l_aid := 0;
268 p_msg_name := NULL;
269
270 OPEN c_get_dist_plan(p_award_id);
271 FETCH c_get_dist_plan INTO l_dist_plan_rec;
272 CLOSE c_get_dist_plan;
273
274 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
275 fnd_log.string(fnd_log.level_statement,
276 'igf.plsql.igf_sl_dl_validation.loan_limit_validation.debug',
277 'Calling check_loan_limits() with parameters l_base_id/fund_type/l_award_id/l_adplans_id/ ' ||p_base_id|| '/' ||p_fund_type|| '/' ||p_award_id|| '/' ||l_dist_plan_rec.adplans_id);
278 END IF;
279
280 igf_aw_packng_subfns.check_loan_limits (
281 l_base_id => p_base_id,
282 fund_type => p_fund_type,
283 l_award_id => p_award_id,
284 l_adplans_id => l_dist_plan_rec.adplans_id,
285 l_aid => l_aid,
286 l_std_loan_tab => NULL,
287 p_msg_name => p_msg_name,
288 l_awd_period => NULL,
289 l_called_from => 'NON-PACKAGING'
290 );
291
292 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
293 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.loan_limit_validation.debug','check_loan_limits() returned with message ' ||p_msg_name);
294 END IF;
295 END loan_limit_validation;
296
297 /* MAIN PROCEDURE */
298 FUNCTION dl_lar_validate(p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
299 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
300 p_loan_catg fnd_lookups.lookup_code%TYPE,
301 p_loan_number igf_sl_loans_all.loan_number%TYPE,
302 p_call_mode VARCHAR2,
303 p_school_code VARCHAR2 )
304 RETURN BOOLEAN
305 AS
306 /*************************************************************
307 Created By : venagara
308 Date Created On : 2000/11/07
309 Purpose :
310 Know limitations, enhancements or remarks
311 Change History
312 Who When What
313 museshad 28-Nov-2005 Bug 4116399.
314 Added Stafford loan limit validation check.
315 ugummall 22-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
316 added one parameter p_school_code to this function.
317 bkkumar 30-sep-2003 Bug 3104228 Changed the cursor cur_loans
318 containing igf_sl_lor_dtls_v with simple
319 joins and got the details of student and parent
320 from igf_sl_gen.get_person_details.
321 Added the debugging log messages.
322
323 gmuralid 3-July-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
324 Added legacy record flag as parameter to
325 igf_sl_loans_pkg
326
327 masehgal # 2593215 removed begin/end dates fetching functions
328 used procedure get_acad_cal_dtls instead
329 added another validation to report acad cal not found
330 agairola 15-Mar-2002 Modified the IGF_SL_LOANS_PKG.UPDATE_ROW call
331 to include borrower determination as part of
332 Refunds DLD 2144600
333
334 (reverse chronological order - newest change first)
335 ***************************************************************/
336
337 lv_loan_catg igf_sl_reqd_fields.loan_type%TYPE;
338 lv_loan_status igf_sl_loans_all.loan_status%TYPE;
339 lv_s_depncy_status igf_ap_isir_matched_all.dependency_status%TYPE;
340 lv_s_citizenship_status igf_ap_isir_matched_all.citizenship_status%TYPE;
341 lv_acad_cal_type igs_ca_inst_all.cal_type%TYPE ;
342 lv_acad_seq_num igs_ca_inst_all.sequence_number%TYPE ;
343 lv_acad_begin_date igs_ca_inst_all.start_dt%TYPE;
344 lv_acad_end_date igs_ca_inst_all.end_dt%TYPE;
345 l_status_1 igf_sl_loans_all.loan_status%TYPE;
346 l_status_2 igf_sl_loans_all.loan_status%TYPE;
347 l_status_3 igf_sl_loans_all.loan_status%TYPE;
348 l_fed_fund_1 igf_aw_fund_cat_all.fed_fund_code%TYPE;
349 l_fed_fund_2 igf_aw_fund_cat_all.fed_fund_code%TYPE;
350 student_dtl_cur igf_sl_gen.person_dtl_cur;
351 parent_dtl_cur igf_sl_gen.person_dtl_cur;
352 lv_message VARCHAR2(100);
353 lv_complete BOOLEAN;
354 lv_disb_count NUMBER;
355 lv_special_school VARCHAR2(30);
356 l_dl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
357 lv_dummy varchar2(1);
358 lv_fed_fund_code igf_aw_fund_cat_all.fed_fund_code%TYPE;
359 lv_msg_name fnd_new_messages.message_name%TYPE;
360 lv_lookup_code igf_lookups_view.lookup_code%TYPE;
361
362 /***********************************************************************************
363 P_LOAN_CATG : Valid values are DL_STAFFORD, DL_PLUS
364 P_LOAN_NUMBER : Possible values are "A particular Loan#" or NULL
365 A valid Loan# will be passed from the Origination form and
366 NULL will be passed as a default value from the concurrent job prog
367 P_CALL_MODE : Valid values are JOB, FORM
368 If invoked from FORM, then only a specific LOAN_NUMBER can be passed.
369 ***********************************************************************************/
370
371 -- If p_call_mode = "JOB", then records with Loan Status with Ready To Send ('G')
372 -- ELSE, if "FORM", then we need to validate for "Ready to Send", "Not Ready", "Rejected"
373
374 -- G : Ready to Send
375 -- R : Rejected
376 -- N : Not Ready
377
378 -- FA 122 Loan Enhancements changed the cursor to obsolete the view igf_sl_lor_dtls_v
379 CURSOR cur_loans(
380 cp_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
381 cp_seq_number igf_ap_fa_base_rec.ci_sequence_number%TYPE,
382 cp_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE,
383 cp_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE,
384 cp_status_1 igf_sl_loans.loan_status%TYPE,
385 cp_status_2 igf_sl_loans.loan_status%TYPE,
386 cp_status_3 igf_sl_loans.loan_status%TYPE,
387 cp_loan_number igf_sl_loans.loan_number%TYPE,
388 cp_active igf_sl_loans.active%TYPE,
389 cp_school_code VARCHAR2
390 )
391 IS
392 SELECT
393 loans.rowid row_id,
394 loans.loan_id,
395 loans.loan_number,
396 loans.award_id,
397 loans.loan_per_begin_date,
398 loans.loan_per_end_date,
399 lor.orig_fee_perct,
400 lor.pnote_print_ind,
401 lor.s_default_status,
402 lor.p_default_status,
403 lor.p_person_id,
404 fabase.person_id student_id,
405 fabase.base_id,
406 awd.accepted_amt,
407 lor.grade_level_code,
408 lor.override_grade_level_code
409 FROM
410 igf_sl_loans_all loans,
411 igf_sl_lor_all lor,
412 igf_aw_award_all awd,
413 igf_aw_fund_mast_all fmast,
414 igf_aw_fund_cat_all fcat,
415 igf_ap_fa_base_rec_all fabase
416 WHERE fabase.ci_cal_type = cp_cal_type
417 AND fabase.ci_sequence_number = cp_seq_number
418 AND fabase.base_id = awd.base_id
419 AND awd.fund_id = fmast.fund_id
420 AND fmast.fund_code = fcat.fund_code
421 AND (fcat.fed_fund_code = cp_fed_fund_1 OR fcat.fed_fund_code = cp_fed_fund_2)
422 AND loans.award_id = awd.award_id
423 AND loans.loan_number = NVL(cp_loan_number,loans.loan_number)
424 AND loans.loan_id = lor.loan_id
425 AND (loans.loan_status = cp_status_1 OR loans.loan_status = cp_status_2 OR loans.loan_status = cp_status_3)
426 AND loans.active = cp_active
427 AND SUBSTR(loans.loan_number,13,6) = NVL(cp_school_code, SUBSTR(loans.loan_number,13,6));
428
429 orec cur_loans%ROWTYPE;
430
431 -- museshad (Bug 4116399)
432 CURSOR cur_get_fed_fund_code(cp_loan_number igf_sl_loans.loan_number%TYPE)
433 IS
434 SELECT fcat.fed_fund_code
435 FROM igf_sl_loans_all loans,
436 igf_aw_award_all awd,
437 igf_aw_fund_mast_all fmast,
438 igf_aw_fund_cat_all fcat
439 WHERE
440 loans.award_id = awd.award_id AND
441 awd.fund_id = fmast.fund_id AND
442 fmast.fund_code = fcat.fund_code AND
443 loans.loan_number = cp_loan_number;
444 -- museshad (Bug 4116399)
445
446 PROCEDURE set_complete_status(p_complete BOOLEAN)
447 AS
448 BEGIN
449 IF p_complete = FALSE THEN
450 lv_complete := FALSE;
451 END IF;
452 END set_complete_status;
453
454 BEGIN
455
456 lv_complete := TRUE;
457 BEGIN
458 p_dl_version := igf_sl_gen.get_dl_version(p_ci_cal_type, p_ci_sequence_number);
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 fnd_message.set_name('IGF','IGF_SL_NO_DL_SETUP');
462 fnd_file.put_line(fnd_file.log,fnd_message.get);
463 RAISE NO_DATA_FOUND;
464 END;
465
466
467 --Initialise the Global Loan ID
468 g_loan_id := NULL;
469
470 -- FA 122 populate the l_status_1,l_status_2,l_status_3 fields
471 IF p_call_mode = 'FORM' THEN
472 l_status_1 := 'G';
473 l_status_2 := 'N';
474 l_status_3 := 'R';
475 ELSIF p_call_mode = 'JOB' THEN
476 l_status_1 := 'G';
477 l_status_2 := 'G';
478 l_status_3 := 'G';
479 END IF;
480
481 -- FA 122 populate the l_fed_fund_1,l_fed_fund_2
482 IF p_loan_catg = 'DL_STAFFORD' THEN
483 l_fed_fund_1 := 'DLS';
484 l_fed_fund_2 := 'DLU';
485 ELSIF p_loan_catg = 'DL_PLUS' THEN
486 l_fed_fund_1 := 'DLP';
487 l_fed_fund_2 := 'DLP';
488 END IF;
489
490 -- FA 122 pass all the derived paramters to the cur_loans to get the necessary details
491 FOR orec IN cur_loans(p_ci_cal_type,
492 p_ci_sequence_number ,
493 l_fed_fund_1,
494 l_fed_fund_2,
495 l_status_1,
496 l_status_2,
497 l_status_3,
498 p_loan_number,
499 'Y',
500 p_school_code)
501 LOOP
502 --Need not perform the validations if the Loan ID is same.
503 IF NVL(g_loan_id,0) <> orec.loan_id OR p_call_mode = 'FORM' THEN
504
505 -- FA 122 Loan Enhancements Use the igf_sl_gen.get_person_details for getting the student as
506 -- well as parent details.
507 igf_sl_gen.get_person_details(orec.student_id,student_dtl_cur);
508 FETCH student_dtl_cur INTO student_dtl_rec;
509
510 -- this will fetch the parent details
511 igf_sl_gen.get_person_details(orec.p_person_id,parent_dtl_cur);
512 FETCH parent_dtl_cur INTO parent_dtl_rec;
513
514 CLOSE student_dtl_cur;
515 CLOSE parent_dtl_cur;
516
517 -- PUT THE DEBUGGING LOG MESSAGES
518
519 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
520 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','loan_number passed to igf_sl_dl_record.get_acad_cal_dtls:'|| orec.loan_number);
521 END IF;
522
523 -- Get the Academic Year Start and End Dates for this Award Year
524 -- masehgal # 2593215 removed begin/end dates fetching functions
525 -- used procedure get_acad_cal_dtls instead
526 igf_sl_dl_record.get_acad_cal_dtls ( orec.loan_number,
527 lv_acad_cal_type,
528 lv_acad_seq_num,
529 lv_acad_begin_date,
530 lv_acad_end_date,
531 lv_message );
532
533 -- This has p_loan_number as IN and begin and cal_type, sequence_number and dates as OUT parametes.
534 -- If lv_message is null then it implies that acad year was found ....
535 -- We will log that as an error code in processing below where we chk if acad_dates are null and
536 -- report appropriate error code.
537
538 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
539 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','lv_message got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_message);
540 END IF;
541 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
542 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','lv_acad_begin_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_begin_date);
543 END IF;
544 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
545 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','lv_acad_end_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_end_date);
546 END IF;
547 lv_complete := TRUE;
548
549 -- Delete records from Edit Report table, with type="V" (VALIDATION) for this Loan#
550 igf_sl_edit.delete_edit(orec.loan_number, 'V');
551
552 lv_s_depncy_status := NULL;
553 lv_s_citizenship_status := NULL;
554 FOR isir_rec IN cur_isir(orec.base_id) LOOP
555 lv_s_depncy_status := isir_rec.s_depncy_status;
556 END LOOP;
557 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
558
559 IF p_loan_catg = 'DL_STAFFORD' THEN
560
561 lv_loan_catg := p_loan_catg;
562
563 -- Following are the checks for Direct Loan Stafford.
564 --FA 122 get the student details from the student_dtl_rec
565 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_SSN', student_dtl_rec.p_ssn));
566 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_FIRST_NAME', student_dtl_rec.p_first_name));
567 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_LAST_NAME', student_dtl_rec.p_last_name));
568 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_PERMT_ADDR1', student_dtl_rec.p_permt_addr1));
569 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_PERMT_CITY', student_dtl_rec.p_permt_city));
570 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_PERMT_STATE', student_dtl_rec.p_permt_state));
571 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_PERMT_ZIP', student_dtl_rec.p_permt_zip));
572 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth));
573 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'PNOTE_PRINT_IND', orec.pnote_print_ind));
574 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'ORIG_FEE_PERCT', to_char(orec.orig_fee_perct)));
575 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_DEPNCY_STATUS', lv_s_depncy_status));
576
577 -- If the Student is in Default, then do not originate
578 IF orec.s_default_status = 'Y' THEN
579 set_complete_status(FALSE);
580 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
581 END IF;
582
583 ELSIF p_loan_catg = 'DL_PLUS' THEN
584 lv_loan_catg := p_loan_catg;
585
586 -- Following are the checks for Direct Loan PLUS.
587 --FA 122 get the parent details from the parent_dtl_rec
588 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_SSN' , parent_dtl_rec.p_ssn));
589 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_FIRST_NAME' , parent_dtl_rec.p_first_name));
590 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_LAST_NAME' , parent_dtl_rec.p_last_name));
591 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_PERMT_ADDR1', parent_dtl_rec.p_permt_addr1));
592 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_PERMT_CITY', parent_dtl_rec.p_permt_city));
593 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_PERMT_STATE', parent_dtl_rec.p_permt_state));
594 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_PERMT_ZIP', parent_dtl_rec.p_permt_zip));
595 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_DATE_OF_BIRTH', to_char(parent_dtl_rec.p_date_of_birth)));
596 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_CITIZENSHIP_STATUS',parent_dtl_rec.p_citizenship_status));
597 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'P_DEFAULT_STATUS', orec.p_default_status));
598 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'PNOTE_PRINT_IND', orec.pnote_print_ind));
599 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_SSN', student_dtl_rec.p_ssn ));
600 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_FIRST_NAME', student_dtl_rec.p_first_name));
601 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_LAST_NAME', student_dtl_rec.p_last_name));
602 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_CITIZENSHIP_STATUS',lv_s_citizenship_status));
603 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_DATE_OF_BIRTH', to_char(student_dtl_rec.p_date_of_birth)));
604 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'S_DEPNCY_STATUS', lv_s_depncy_status));
605
606 -- For PLUS, If the Student/Parent is in Default, then do not originate
607 IF orec.s_default_status = 'Y' THEN
608 set_complete_status(FALSE);
609 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
610 END IF;
611 IF orec.p_default_status = 'Y' THEN
612 set_complete_status(FALSE);
613 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'P_DEFAULT_STATUS', NULL);
614 END IF;
615
616 END IF; -- End of "p_loan_catg" check.
617
618 set_complete_status(check_for_reqd(orec.loan_number,lv_loan_catg,'GRADE_LEVEL_CODE', orec.grade_level_code));
619
620 -- The following validation introduced in as part of IGS.L.6R bug 4346421.
621 -- But due to Cust bug 4568942, this validation is removed. (Reverting back 4346421)
622 /*
623 -- Validate grade level (bug 4346421)
624 -- get System Grade Level, compare with NVL(override_grade_level_code, grade_level_code). If not matching, then log mesg.
625 l_dl_std_code := get_system_grade_level(orec.base_id, orec.loan_per_begin_date, orec.loan_per_end_date);
626 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
627 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','l_dl_std_code got from get_system_grade_level function :'|| l_dl_std_code);
628 END IF;
629
630 IF (orec.override_grade_level_code IS NOT NULL) THEN
631 IF (orec.override_grade_level_code <> l_dl_std_code) THEN
632 set_complete_status(FALSE);
633 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'OVER_GRD_LVL_MISMATCH', 'OVERRIDE_GRADE_LEVEL_CODE', NULL);
634 END IF;
635 ELSIF orec.grade_level_code IS NOT NULL THEN
636 IF (orec.grade_level_code <> l_dl_std_code) THEN
637 set_complete_status(FALSE);
638 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'GRD_LVL_MISMATCH', 'GRADE_LEVEL_CODE', NULL);
639 END IF;
640 END IF;
641 */
642
643
644 -- Check if number of disbursements is one or not. If one, it must be Special School. FA 149.
645 -- get number of disbursements
646
647 lv_disb_count := -1;
648 OPEN cur_get_no_of_disbursements(orec.award_id);
649 FETCH cur_get_no_of_disbursements INTO lv_disb_count;
650 IF (lv_disb_count = 1) THEN
651 -- Check if it is Special School or not.
652 OPEN cur_special_school(p_ci_cal_type, p_ci_sequence_number);
653 FETCH cur_special_school INTO lv_special_school;
654 IF (lv_special_school IS NOT NULL AND lv_special_school <> 'Y') THEN
655 set_complete_status(FALSE);
656 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INVALID_DISB_INFO', NULL, NULL);
657 END IF;
658 CLOSE cur_special_school;
659 END IF;
660 CLOSE cur_get_no_of_disbursements;
661
662 -- Below are some of the checks which are to be done for both STAFFORD and PLUS
663
664 -- For any disbursement, if disb-gross-amount, origination-fees or the disbursement net amount,
665 -- should be more than ZERO. This is for both Stafford and PLUS
666
667 -- COD Specific. If amounts are zero still we need to sent if it had already been with COD.
668 -- Check wether the record is at LOR or not.
669 -- If yes, omit the following check. Bug 4368529
670 lv_dummy := NULL;
671 OPEN cur_loan_at_cod(orec.loan_number);
672 FETCH cur_loan_at_cod INTO lv_dummy;
673 CLOSE cur_loan_at_cod;
674
675 IF lv_dummy IS NULL THEN
676 FOR irec in cur_disb(orec.award_id) LOOP
677 IF NVL(irec.disb_count,0) > 0 THEN
678 set_complete_status(FALSE);
679 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DISB_MORE_THAN_ZERO', NULL, NULL);
680 END IF;
681 END LOOP;
682
683 -- Loan Amount should be greater than zero. Applicable to Stafford and PLUS
684 IF nvl(orec.accepted_amt,0) <= 0 THEN
685 set_complete_status(FALSE);
686 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'LOAN_AMT_ACCEPTED', NULL);
687 END IF;
688 END IF;
689
690 -- Origination Fee should be greater than zero. Applicable to Stafford and PLUS
691 IF nvl(orec.orig_fee_perct,0) <= 0 THEN
692 set_complete_status(FALSE);
693 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'ORIG_FEE_PERCT', NULL);
694 END IF;
695
696 -- Check if the Loan Begin and End Dates fall within the Academic Begin and End Dates.
697
698 -- masehgal # 2593215 added this check - acad year not found.
699 IF (lv_acad_begin_date IS NULL OR lv_acad_end_date IS NULL) THEN
700 -- irrespective if it is called from form or job, insert a record in edit report with a new error code
701 set_complete_status(FALSE);
702 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'ACAD_YEAR_REQD', NULL, NULL);
703 END IF ;
704
705 IF orec.loan_per_begin_date < lv_acad_begin_date THEN
706 set_complete_status(FALSE);
707 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LOAN_BEGDT_LT_ACAD_BEGDT', NULL, NULL);
708 END IF;
709 IF orec.loan_per_end_date > lv_acad_end_date THEN
710
711 set_complete_status(FALSE);
712 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LOAN_ENDDT_GT_ACAD_ENDDT', NULL, NULL);
713 END IF;
714
715 -- Check whether the EFC Information is available for the Student, using the ISIR's Dependency Status
716 -- If ISIR's Depncy-status is X,Y, then NO EFC is Calculated.
717 -- If ISIR's Depncy-status is I,D, then alculated EFC info has been provided.
718 IF nvl(lv_s_depncy_status,'*') NOT IN ('I','D') THEN
719 set_complete_status(FALSE);
720 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DEPNCY_NO_EFC_CALC', 'S_DEPNCY_STATUS', lv_s_depncy_status);
721 END IF;
722
723 -- museshad (Bug 4116399) - Stafford loan limit validation for DLS, DLU
724 IF p_loan_catg = 'DL_STAFFORD' THEN
725 lv_msg_name := NULL;
726 lv_lookup_code := NULL;
727
728 OPEN cur_get_fed_fund_code(cp_loan_number => orec.loan_number);
729 FETCH cur_get_fed_fund_code INTO lv_fed_fund_code;
730 CLOSE cur_get_fed_fund_code;
731
732 loan_limit_validation (
733 p_base_id => orec.base_id,
734 p_fund_type => lv_fed_fund_code,
735 p_award_id => orec.award_id,
736 p_msg_name => lv_msg_name
737 );
738
739 IF lv_msg_name IS NOT NULL THEN
740 -- Stafford loan limit validation FAILED
741 set_complete_status(FALSE);
742
743 IF lv_msg_name = 'IGF_AW_AGGR_LMT_ERR' THEN
744 lv_lookup_code := 'AGGR_LMT_CHK';
745 ELSIF lv_msg_name = 'IGF_AW_ANNUAL_LMT_ERR' THEN
746 lv_lookup_code := 'ANNUAL_LMT_CHK';
747 ELSIF lv_msg_name = 'IGF_AW_SUB_AGGR_LMT_ERR' THEN
748 lv_lookup_code := 'SUB_AGGR_LMT_CHK';
749 ELSIF lv_msg_name = 'IGF_AW_SUB_LMT_ERR' THEN
750 lv_lookup_code := 'SUB_LMT_CHK';
751 ELSIF lv_msg_name = 'IGF_AW_LOAN_LMT_NOT_FND' THEN
752 lv_lookup_code := 'LOAN_LMT_SETUP_CHK';
753 END IF;
754
755 igf_sl_edit.insert_edit(orec.loan_number, 'V', 'IGF_SL_ERR_CODES', lv_lookup_code, NULL, NULL);
756
757 -- Log
758 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
759 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','Stafford loan limit validation for award_id ' ||orec.award_id|| ' FAILED with message: ' ||lv_msg_name);
760 END IF;
761 ELSE
762 -- Stafford loan limit validation PASSED
763 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
764 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.dl_lar_validate.debug','Stafford loan limit validation PASSED for award_id ' ||orec.award_id);
765 END IF;
766 END IF;
767 END IF;
768 -- museshad (Bug 4116399)
769
770 IF p_call_mode = 'JOB' THEN
771
772 -- If any validation fails or if any required data is missing, then set Loan Status=Not Ready.
773 -- Else, set to "Valid and Ready to Send"
774 IF lv_complete = FALSE THEN
775 lv_loan_status := 'N'; -- NOT READY
776 ELSE
777 lv_loan_status := 'V'; -- VALID and READY TO SEND
778 END IF;
779
780 DECLARE
781 lv_row_id VARCHAR2(25);
782 CURSOR c_tbh_cur IS
783 SELECT igf_sl_loans.* FROM igf_sl_loans
784 WHERE rowid = orec.row_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
785 BEGIN
786 FOR tbh_rec in c_tbh_cur LOOP
787
788 -- Modified the call for IGF_SL_LOANS_PKG.UPDATE_ROW to include the Borrower Determination
789 -- as part of Refunds DLD - 2144600
790 igf_sl_loans_pkg.update_row (
791 X_Mode => 'R',
792 x_rowid => tbh_rec.row_id,
793 x_loan_id => tbh_rec.loan_id,
794 x_award_id => tbh_rec.award_id,
795 x_seq_num => tbh_rec.seq_num,
796 x_loan_number => tbh_rec.loan_number,
797 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
798 x_loan_per_end_date => tbh_rec.loan_per_end_date,
799 x_loan_status => lv_loan_status,
800 x_loan_status_date => TRUNC(SYSDATE),
801 x_loan_chg_status => tbh_rec.loan_chg_status,
802 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
803 x_active => tbh_rec.active,
804 x_active_date => tbh_rec.active_date,
805 x_borw_detrm_code => tbh_rec.borw_detrm_code,
806 x_legacy_record_flag => NULL,
807 x_external_loan_id_txt => tbh_rec.external_loan_id_txt
808
809 );
810 END LOOP;
811 END;
812
813 IF lv_complete = FALSE THEN
814 -- Display reject details on the Concurrent Manager Log File.
815 DECLARE
816 lv_log_mesg VARCHAR2(1000);
817 CURSOR c_reject IS
818 SELECT rpad(field_desc,50)||sl_error_desc reject_desc FROM igf_sl_edit_report_v
819 WHERE loan_number = orec.loan_number
820 AND orig_chg_code = 'V';
821 BEGIN
822
823
824 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
825 FND_FILE.PUT_LINE(FND_FILE.LOG, '');
826 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER')||' : '||orec.loan_number;
827 FND_FILE.PUT_LINE(FND_FILE.LOG, lv_log_mesg);
828 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_SSN') ||' : '||student_dtl_rec.p_ssn;
829 FND_FILE.PUT_LINE(FND_FILE.LOG, lv_log_mesg);
830 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_FULL_NAME')||' : '
831 ||student_dtl_rec.p_first_name||' '||student_dtl_rec.p_last_name;
832 FND_FILE.PUT_LINE(FND_FILE.LOG, lv_log_mesg);
833 FOR rej_rec IN c_reject LOOP
834 FND_FILE.PUT_LINE(FND_FILE.LOG,' '||rej_rec.reject_desc);
835 END LOOP;
836
837 END;
838 END IF;
839
840 ELSE
841 -- If the validation routinue is called from FORM, then just return the Status of TRUE/FALSE.
842 NULL;
843
844 END IF;
845
846 g_loan_id:=orec.loan_id; --Keep changing Global Loan ID everytime it is different.
847
848 END IF; --Check for Global Loan ID not being same.
849
850
851 END LOOP;
852
853 IF p_call_mode = 'JOB' THEN
854 RETURN TRUE;
855 ELSE
856 RETURN lv_complete;
857 END IF;
858
859 EXCEPTION
860 --Added this for Handling the exception when DL Setup is not found
861 WHEN NO_DATA_FOUND THEN
862 NULL;
863
864 WHEN app_exception.record_lock_exception THEN
865 RAISE;
866 WHEN OTHERS THEN
867
868 --Removed the fnd_message.setname with Package.Procedure name.
869 --This is because this procedure in turn calls igf_sl_dl_record package academic year functions
870 --which return valid exceptions.only if we just propogate this exception
871 --we will be able to trap both in form and in dl orig process.
872 --hence removed code.
873 --Bug :-2415041 Loan Orig with incorrect error messages.
874
875 app_exception.raise_exception;
876
877 END dl_lar_validate;
878
879 FUNCTION cod_loan_validations ( p_loan_rec igf_sl_dl_gen_xml.cur_pick_loans%ROWTYPE,
880 p_call_from VARCHAR2,
881 p_isir_ssn OUT NOCOPY VARCHAR2,
882 p_isir_dob OUT NOCOPY DATE,
883 p_isir_lname OUT NOCOPY VARCHAR2,
884 p_isir_dep OUT NOCOPY VARCHAR2,
885 p_isir_tnum OUT NOCOPY NUMBER,
886 p_acad_begin OUT NOCOPY DATE,
887 p_acad_end OUT NOCOPY DATE,
888 p_s_phone OUT NOCOPY VARCHAR2,
889 p_p_phone OUT NOCOPY VARCHAR2 )
890 RETURN BOOLEAN AS
891 /*************************************************************
892 Created By : ugummall
893 Date Created On : 01-OCT-2004
894 Purpose :
895 Know limitations, enhancements or remarks
896 Change History
897 Who When What
898 museshad 28-Nov-2005 Bug 4116399.
899 Added Stafford loan limit validation check.
900 (reverse chronological order - newest change first)
901 akomurav 24-1-2006 FA162.The values of Acad begin date and end date are now directly populated fromIGF_SL_LOR_ALL table i.e p_loan_rec record.
902 ***************************************************************/
903
904 CURSOR cur_disb_chg_dtls ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
905 SELECT db.award_id,
906 db.disb_num,
907 db.disb_seq_num,
908 db.disb_accepted_amt,
909 db.disb_date,
910 db.orig_fee_amt,
911 db.disb_net_amt,
912 db.interest_rebate_amt
913 FROM igf_aw_db_chg_dtls db
914 WHERE db.award_id = cp_award_id;
915
916 rec_disb_chg_dtls cur_disb_chg_dtls%ROWTYPE;
917 lv_s_depncy_status igf_ap_isir_matched_all.dependency_status%TYPE;
918 lv_s_citizenship_status igf_ap_isir_matched_all.citizenship_status%TYPE;
919 lv_acad_cal_type igs_ca_inst_all.cal_type%TYPE ;
920 lv_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
921 lv_acad_begin_date igs_ca_inst_all.start_dt%TYPE;
922 lv_acad_end_date igs_ca_inst_all.end_dt%TYPE;
923 lv_message VARCHAR2(100);
924 l_p_phone VARCHAR2(80);
925 l_s_phone VARCHAR2(80);
926 lv_special_school VARCHAR2(30);
927 lv_complete BOOLEAN;
928 lv_disb_count NUMBER;
929 student_dtl_cur igf_sl_gen.person_dtl_cur;
930 parent_dtl_cur igf_sl_gen.person_dtl_cur;
931 l_dl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
932 lv_dummy varchar2(1);
933 lv_lookup_code igf_lookups_view.LOOKUP_CODE%TYPE;
934
935
936 PROCEDURE set_complete_status(p_complete BOOLEAN)
937 AS
938 BEGIN
939 IF p_complete = FALSE THEN
940 lv_complete := FALSE;
941 END IF;
942 END set_complete_status;
943
944 BEGIN
945
946 BEGIN
947 p_dl_version := igf_sl_gen.get_dl_version(p_loan_rec.ci_cal_type, p_loan_rec.ci_sequence_number);
948 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
949 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','p_dl_version '|| p_dl_version );
950 END IF;
951 EXCEPTION
952 WHEN NO_DATA_FOUND THEN
953 fnd_message.set_name('IGF','IGF_SL_NO_DL_SETUP');
954 fnd_file.put_line(fnd_file.log,fnd_message.get);
955 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
956 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Setup gone ' );
957 END IF;
958 RAISE NO_DATA_FOUND;
959 END;
960
961 lv_complete := TRUE;
962
963 --
964 -- Get ISIR Information
965 --
966 OPEN cur_isir_info (p_loan_rec.base_id);
967 FETCH cur_isir_info INTO isir_info_rec;
968 CLOSE cur_isir_info;
969
970 -- Delete records from Edit Report table, with type="V" (VALIDATION) for this Loan#
971 igf_sl_edit.delete_edit(p_loan_rec.loan_number, 'V');
972 --
973 -- Get academic calendar details
974 --
975 --akomurav FA162 This code will pick the values from the lor_all table
976 --The procedure igf_sl_dl_record.get_acad_cal_dtls will be called only if the acad_begin_date and acad_end_date are NULL
977 lv_acad_begin_date := p_loan_rec.acad_begin_date;
978 lv_acad_end_date := p_loan_rec.acad_end_date;
979
980 IF lv_acad_begin_date IS NULL OR lv_acad_end_date IS NULL THEN
981 igf_sl_dl_record.get_acad_cal_dtls ( p_loan_rec.loan_number,
982 lv_acad_cal_type,
983 lv_acad_seq_num,
984 lv_acad_begin_date,
985 lv_acad_end_date,
986 lv_message );
987 END IF;
988
989 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
990 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_message got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_message);
991 END IF;
992 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
993 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_acad_begin_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_begin_date);
994 END IF;
995 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
996 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_acad_end_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_end_date);
997 END IF;
998
999 igf_sl_gen.get_person_details(igf_gr_gen.get_person_id(p_loan_rec.base_id),student_dtl_cur);
1000 FETCH student_dtl_cur INTO student_dtl_rec;
1001
1002 igf_sl_gen.get_person_details(p_loan_rec.p_person_id,parent_dtl_cur);
1003 FETCH parent_dtl_cur INTO parent_dtl_rec;
1004
1005 CLOSE student_dtl_cur;
1006 CLOSE parent_dtl_cur;
1007
1008 lv_s_depncy_status := NULL;
1009 lv_s_citizenship_status := NULL;
1010 lv_s_depncy_status := isir_info_rec.dependency_status;
1011 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
1012 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1013 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_s_citizenship_status '|| lv_s_citizenship_status);
1014 END IF;
1015
1016 p_isir_ssn := isir_info_rec.current_ssn;
1017 p_isir_dob := isir_info_rec.date_of_birth;
1018 p_isir_lname := isir_info_rec.last_name;
1019 p_isir_dep := isir_info_rec.dependency_status;
1020 p_isir_tnum := isir_info_rec.transaction_num;
1021 p_acad_begin := lv_acad_begin_date;
1022 p_acad_end := lv_acad_end_date;
1023
1024 IF p_loan_rec.fed_fund_code IN ('DLS','DLU') THEN
1025 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_SSN', student_dtl_rec.p_ssn));
1026 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_FIRST_NAME', student_dtl_rec.p_first_name));
1027 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_LAST_NAME', student_dtl_rec.p_last_name));
1028 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_PERMT_ADDR1', student_dtl_rec.p_permt_addr1));
1029 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1030 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','S_PERMT_ADDR1 = ' ||student_dtl_rec.p_permt_addr1);
1031 END IF;
1032 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_PERMT_CITY', student_dtl_rec.p_permt_city));
1033 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_PERMT_STATE', student_dtl_rec.p_permt_state));
1034 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_PERMT_ZIP', student_dtl_rec.p_permt_zip));
1035 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth));
1036 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','PNOTE_PRINT_IND', p_loan_rec.pnote_print_ind));
1037 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','ORIG_FEE_PERCT', TO_CHAR(p_loan_rec.orig_fee_perct)));
1038 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_DEPNCY_STATUS', lv_s_depncy_status));
1039 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','GRADE_LEVEL_CODE',p_loan_rec.grade_level_code));
1040 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','LOAN_AMT_ACCEPTED', p_loan_rec.accepted_amt));
1041 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','LOAN_NUMBER', p_loan_rec.loan_number));
1042 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_DEFAULT_STATUS',p_loan_rec.s_default_status));
1043 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','ATD_ENTITY_ID_TXT',p_loan_rec.atd_entity_id_txt));
1044 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','CPS_TRANS_NUM',p_loan_rec.cps_trans_num));
1045 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','LOAN_PER_BEGIN_DATE',p_loan_rec.loan_per_begin_date));
1046 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','LOAN_PER_END_DATE',p_loan_rec.loan_per_end_date));
1047 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','REP_ENTITY_ID_TXT',p_loan_rec.rep_entity_id_txt));
1048 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_STAFFORD','S_CITIZENSHIP_STATUS',student_dtl_rec.p_citizenship_status));
1049
1050 -- If the Student is in Default, then do not originate
1051 IF p_loan_rec.s_default_status = 'Y' THEN
1052 set_complete_status(FALSE);
1053 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
1054 END IF;
1055
1056 ELSIF p_loan_rec.fed_fund_code = 'DLP' THEN
1057
1058 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_SSN' , parent_dtl_rec.p_ssn));
1059 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_FIRST_NAME' , parent_dtl_rec.p_first_name));
1060 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_LAST_NAME' , parent_dtl_rec.p_last_name));
1061 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_PERMT_ADDR1', parent_dtl_rec.p_permt_addr1));
1062 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1063 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','P_PERMT_ADDR1 = ' ||parent_dtl_rec.p_permt_addr1);
1064 END IF;
1065 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_PERMT_CITY', parent_dtl_rec.p_permt_city));
1066 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_PERMT_STATE', parent_dtl_rec.p_permt_state));
1067 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_PERMT_ZIP', parent_dtl_rec.p_permt_zip));
1068 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_DATE_OF_BIRTH', TO_CHAR(parent_dtl_rec.p_date_of_birth)));
1069 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_CITIZENSHIP_STATUS',parent_dtl_rec.p_citizenship_status));
1070 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','P_DEFAULT_STATUS', p_loan_rec.p_default_status));
1071 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','PNOTE_PRINT_IND', p_loan_rec.pnote_print_ind));
1072 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_SSN', student_dtl_rec.p_ssn));
1073 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_FIRST_NAME', student_dtl_rec.p_first_name));
1074 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_LAST_NAME', student_dtl_rec.p_last_name));
1075 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_PERMT_ADDR1', student_dtl_rec.p_permt_addr1));
1076 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1077 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','S_PERMT_ADDR1 = ' ||student_dtl_rec.p_permt_addr1);
1078 END IF;
1079 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_PERMT_CITY', student_dtl_rec.p_permt_city));
1080 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_PERMT_STATE', student_dtl_rec.p_permt_state));
1081 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_PERMT_ZIP', student_dtl_rec.p_permt_zip));
1082 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth));
1083 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','PNOTE_PRINT_IND', p_loan_rec.pnote_print_ind));
1084 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','ORIG_FEE_PERCT', TO_CHAR(p_loan_rec.orig_fee_perct)));
1085 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_DEPNCY_STATUS', lv_s_depncy_status));
1086 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','GRADE_LEVEL_CODE',p_loan_rec.grade_level_code));
1087 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','LOAN_AMT_ACCEPTED', p_loan_rec.accepted_amt));
1088 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','LOAN_NUMBER', p_loan_rec.loan_number));
1089 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_DEFAULT_STATUS',p_loan_rec.s_default_status));
1090 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','ATD_SCHL_ENTITY_ID',p_loan_rec.atd_entity_id_txt));
1091 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','CPS_TRANS_NUM',p_loan_rec.cps_trans_num));
1092 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','LOAN_PER_BEGIN_DATE',p_loan_rec.loan_per_begin_date));
1093 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','LOAN_PER_END_DATE',p_loan_rec.loan_per_end_date));
1094 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','REP_SCHL_ENTITY_ID',p_loan_rec.rep_entity_id_txt));
1095 set_complete_status(check_for_reqd(p_loan_rec.loan_number,'DL_PLUS','S_CITIZENSHIP_STATUS',student_dtl_rec.p_citizenship_status));
1096
1097 -- For PLUS, If the Student/Parent is in Default, then do not originate
1098 IF p_loan_rec.s_default_status = 'Y' THEN
1099 set_complete_status(FALSE);
1100 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
1101 END IF;
1102 IF p_loan_rec.p_default_status = 'Y' THEN
1103 set_complete_status(FALSE);
1104 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'P_DEFAULT_STATUS', NULL);
1105 END IF;
1106 END IF; -- End of "p_loan_catg" check.
1107
1108 -- Check if number of disbursements is one or not. If one, it must be Special School. FA 149.
1109 -- get number of disbursements
1110 lv_disb_count := -1;
1111
1112 OPEN cur_get_no_of_disbursements(p_loan_rec.award_id);
1113 FETCH cur_get_no_of_disbursements INTO lv_disb_count;
1114 CLOSE cur_get_no_of_disbursements;
1115 IF (lv_disb_count = 1) THEN
1116 -- Check if it is Special School or not.
1117 OPEN cur_special_school(p_loan_rec.ci_cal_type, p_loan_rec.ci_sequence_number);
1118 FETCH cur_special_school INTO lv_special_school;
1119 IF (lv_special_school IS NOT NULL AND lv_special_school <> 'Y') THEN
1120 set_complete_status(FALSE);
1121 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INVALID_DISB_INFO', NULL, NULL);
1122 END IF;
1123 CLOSE cur_special_school;
1124 END IF;
1125
1126 -- Below are some of the checks which are to be done for both STAFFORD and PLUS
1127 -- For any disbursement, if disb-gross-amount, origination-fees or the disbursement net amount,
1128 -- should be more than ZERO. This is for both Stafford and PLUS
1129
1130 -- COD Specific. If amounts are zero still we need to sent if it had already been with COD.
1131 -- Check wether the record is at LOR or not.
1132 -- If yes, omit the following check. Bug 4368529
1133 lv_dummy := NULL;
1134 OPEN cur_loan_at_cod(p_loan_rec.loan_number);
1135 FETCH cur_loan_at_cod INTO lv_dummy;
1136 CLOSE cur_loan_at_cod;
1137
1138 IF lv_dummy IS NULL THEN
1139 FOR irec IN cur_disb(p_loan_rec.award_id) LOOP
1140 IF NVL(irec.disb_count,0) > 0 THEN
1141 set_complete_status(FALSE);
1142 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DISB_MORE_THAN_ZERO', NULL, NULL);
1143 END IF;
1144 END LOOP;
1145
1146 -- Loan Amount should be greater than zero. Applicable to Stafford and PLUS
1147 IF NVL(p_loan_rec.accepted_amt,0) <= 0 THEN
1148 set_complete_status(FALSE);
1149 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'LOAN_AMT_ACCEPTED', p_loan_rec.accepted_amt);
1150 END IF;
1151 END IF;
1152
1153 -- Origination Fee should be greater than zero. Applicable to Stafford and PLUS
1154 IF NVL(p_loan_rec.orig_fee_perct,0) <= 0 THEN
1155 set_complete_status(FALSE);
1156 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'ORIG_FEE_PERCT', p_loan_rec.orig_fee_perct);
1157 END IF;
1158
1159 -- Payment ISIR missing
1160 IF isir_info_rec.payment_isir IS NULL THEN
1161 set_complete_status(FALSE);
1162 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_PYMNT_ISIR', NULL, NULL);
1163 END IF;
1164
1165 -- Payment ISIR mismatch
1166 IF TO_NUMBER(isir_info_rec.transaction_num) <> TO_NUMBER(p_loan_rec.cps_trans_num) THEN
1167 --MN 11/29/2004 16:23 - As the Transaction Number is numeric data stored in VarChar2, added To_Number
1168 set_complete_status(FALSE);
1169 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'PYMNT_ISIR_MISMATCH', NULL, NULL);
1170 END IF;
1171
1172 IF (lv_acad_begin_date IS NULL OR lv_acad_end_date IS NULL) THEN
1173 set_complete_status(FALSE);
1174 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'ACAD_YEAR_REQD', NULL, NULL);
1175 END IF ;
1176
1177 IF p_loan_rec.loan_per_begin_date < lv_acad_begin_date THEN
1178 set_complete_status(FALSE);
1179 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LOAN_BEGDT_LT_ACAD_BEGDT', NULL, NULL);
1180 END IF;
1181
1182 IF p_loan_rec.loan_per_end_date > lv_acad_end_date THEN
1183 set_complete_status(FALSE);
1184 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LOAN_ENDDT_GT_ACAD_ENDDT', NULL, NULL);
1185 END IF;
1186
1187 -- Check whether the EFC Information is available for the Student, using the ISIR's Dependency Status
1188 -- If ISIR's Depncy-status is X,Y, then NO EFC is Calculated.
1189 -- If ISIR's Depncy-status is I,D, then alculated EFC info has been provided.
1190 IF NVL(lv_s_depncy_status,'*') NOT IN ('I','D') THEN
1191 set_complete_status(FALSE);
1192 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DEPNCY_NO_EFC_CALC', 'S_DEPNCY_STATUS', lv_s_depncy_status);
1193 END IF;
1194
1195 -- The following validation introduced in as part of IGS.L.6R bug 4346421.
1196 -- But due to Cust bug 4568942, this validation is removed. (Reverting back 4346421)
1197 /*
1198 -- Validate grade level (bug 4346421)
1199 -- get System Grade Level, compare with NVL(override_grade_level_code, grade_level_code). If not matching, then log mesg.
1200 l_dl_std_code := get_system_grade_level(p_loan_rec.base_id, p_loan_rec.loan_per_begin_date, p_loan_rec.loan_per_end_date);
1201 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1202 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','l_dl_std_code got from get_system_grade_level function :'|| l_dl_std_code);
1203 END IF;
1204
1205 IF (p_loan_rec.override_grade_level_code IS NOT NULL) THEN
1206 IF (p_loan_rec.override_grade_level_code <> l_dl_std_code) THEN
1207 set_complete_status(FALSE);
1208 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'OVER_GRD_LVL_MISMATCH', 'OVERRIDE_GRADE_LEVEL_CODE', NULL);
1209 END IF;
1210 ELSIF p_loan_rec.grade_level_code IS NOT NULL THEN
1211 IF (p_loan_rec.grade_level_code <> l_dl_std_code) THEN
1212 set_complete_status(FALSE);
1213 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'GRD_LVL_MISMATCH', 'GRADE_LEVEL_CODE', NULL);
1214 END IF;
1215 END IF;
1216 */
1217
1218 --
1219 -- COD Specific validations
1220 --
1221
1222 -- Check if the isir information is same as sws information
1223 IF p_loan_rec.grade_level_code NOT IN ('0','1','2','3','4','5','6','7') THEN
1224 lv_complete := FALSE;
1225 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_GRD_LEVEL', 'GRADE_LEVEL_CODE',p_loan_rec.grade_level_code);
1226 END IF;
1227 IF p_loan_rec.disclosure_print_ind IS NULL OR p_loan_rec.disclosure_print_ind NOT IN ('S','R','Y') THEN
1228 lv_complete := FALSE;
1229 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISC_IND', 'DISC_PRINT_IND',p_loan_rec.disclosure_print_ind);
1230 END IF;
1231 IF p_loan_rec.pnote_print_ind IS NULL OR p_loan_rec.pnote_print_ind NOT IN ('S','R','Z','V','O') THEN
1232 lv_complete := FALSE;
1233 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_PNOTE_IND', 'PNOTE_PRINT_IND',p_loan_rec.pnote_print_ind);
1234 END IF;
1235
1236 /* Not inserting this edit ---
1237 IF isir_info_rec.date_of_birth <> student_dtl_rec.p_date_of_birth OR
1238 isir_info_rec.current_ssn <> student_dtl_rec.p_ssn OR
1239 isir_info_rec.last_name <> UPPER(student_dtl_rec.p_last_name) THEN
1240
1241
1242
1243 --lv_complete := FALSE;
1244 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_ISIR_INFO_CHG', NULL,NULL);
1245 END IF;
1246
1247 */
1248
1249 IF p_dl_version = '2004-2005' THEN
1250 -- 1. validating student's date of birth
1251 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011905', 'DDMMYYYY')) OR
1252 (student_dtl_rec.p_date_of_birth > TO_DATE('31121996', 'DDMMYYYY')) )
1253 THEN
1254 lv_complete := FALSE;
1255 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INVALID_DOB0405', 'S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth);
1256 END IF;
1257 END IF;
1258 IF p_dl_version = '2005-2006' THEN
1259 -- 1. validating student's date of birth
1260 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011906', 'DDMMYYYY')) OR
1261 (student_dtl_rec.p_date_of_birth > TO_DATE('31121997', 'DDMMYYYY')) )
1262 THEN
1263 lv_complete := FALSE;
1264 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INVALID_DOB0506', 'S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth);
1265 END IF;
1266 END IF;
1267 IF p_dl_version = '2006-2007' THEN
1268 -- 1. validating student's date of birth
1269 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011907', 'DDMMYYYY')) OR
1270 (student_dtl_rec.p_date_of_birth > TO_DATE('31121998', 'DDMMYYYY')) )
1271 THEN
1272 lv_complete := FALSE;
1273 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INVALID_DOB0607', 'S_DATE_OF_BIRTH', student_dtl_rec.p_date_of_birth);
1274 END IF;
1275 END IF;
1276 -- validating student's SSN
1277 IF ( (student_dtl_rec.p_ssn < '001010001') OR (student_dtl_rec.p_ssn > '999999998') ) THEN
1278 lv_complete := FALSE;
1279 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INVALID_SSN', 'S_SSN', student_dtl_rec.p_ssn);
1280 END IF;
1281
1282 -- validating student's first name
1283 IF ( LENGTH(student_dtl_rec.p_first_name) > 12) THEN
1284 lv_complete := FALSE;
1285 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_FIRST_NAME', 'S_FIRST_NAME', student_dtl_rec.p_first_name);
1286 END IF;
1287
1288 -- validating student's last name
1289 IF ( LENGTH(student_dtl_rec.p_last_name) > 35) THEN
1290 lv_complete := FALSE;
1291 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_LAST_NAME', 'S_LAST_NAME', student_dtl_rec.p_last_name);
1292 END IF;
1293
1294 -- validating student's address line 1
1295 IF ( (LENGTH(student_dtl_rec.p_permt_addr1) < 1) OR
1296 (LENGTH(student_dtl_rec.p_permt_addr1) > 40) ) THEN
1297 lv_complete := FALSE;
1298 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_ADDR_LINE', 'S_PERMT_ADDR1', student_dtl_rec.p_permt_addr1);
1299 END IF;
1300
1301 -- validating student's address line 2
1302 IF ( (LENGTH(student_dtl_rec.p_permt_addr2) < 1) OR (LENGTH(student_dtl_rec.p_permt_addr2) > 40) ) THEN
1303 lv_complete := FALSE;
1304 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_ADDR_LINE', 'S_PERMT_ADDR2', student_dtl_rec.p_permt_addr2);
1305 END IF;
1306
1307 -- validating student's city
1308 IF ( LENGTH(student_dtl_rec.p_permt_city) > 24) THEN
1309 lv_complete := FALSE;
1310 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_CITY', 'S_PERMT_CITY', student_dtl_rec.p_permt_city);
1311 END IF;
1312
1313 -- validating student's E-MAIL
1314 IF ( LENGTH(student_dtl_rec.p_email_addr) > 128) THEN
1315 lv_complete := FALSE;
1316 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_EMAIL', 'S_EMAIL_ADDR', student_dtl_rec.p_email_addr);
1317 END IF;
1318
1319 -- validating student's Phone Number sl03b.pls
1320 l_s_phone := igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(p_loan_rec.base_id));
1321 IF l_s_phone = 'N/A' THEN
1322 p_s_phone := NULL;--bug 4093556, When there is no phone number existing for a student then the phone number tag was populated using '000000000000' .
1323 --This has being changed to NULL(akomurav)
1324 ELSE
1325 IF NOT validate_id(l_s_phone) THEN
1326 lv_complete := FALSE;
1327 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_PHONE_NUM_D', 'S_PERMT_PHONE', l_s_phone);
1328 ELSE
1329 p_s_phone := l_s_phone;
1330 END IF;
1331 IF LENGTH(l_s_phone) > 17 OR LENGTH(l_s_phone) < 10 THEN
1332 lv_complete := FALSE;
1333 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_PHONE_NUM', 'S_PERMT_PHONE', l_s_phone);
1334 END IF;
1335 END IF;
1336
1337 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1338 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','l_s_phone '|| l_s_phone);
1339 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','p_s_phone '|| p_s_phone);
1340 END IF;
1341
1342 -- validating student's Zip Code
1343 IF ( (LENGTH(student_dtl_rec.p_permt_zip) < 5) OR (LENGTH(student_dtl_rec.p_permt_zip) > 13) ) THEN
1344 lv_complete := FALSE;
1345 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_ZIP_CODE', 'S_PERMT_ZIP', student_dtl_rec.p_permt_zip);
1346 END IF;
1347
1348 -- validating student's State Code
1349 IF ( (LENGTH(student_dtl_rec.p_permt_state) < 2) OR (LENGTH(student_dtl_rec.p_permt_state) > 3) ) THEN
1350 lv_complete := FALSE;
1351 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_STATE_CODE', 'S_PERMT_STATE', student_dtl_rec.p_permt_state);
1352 END IF;
1353
1354 -- validating student's driver licencse number
1355 IF ( LENGTH(student_dtl_rec.p_license_num) > 20) THEN
1356 lv_complete := FALSE;
1357 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_DRIV_LIC_NUM', 'S_LICENSE_NUM', student_dtl_rec.p_license_num);
1358 END IF;
1359
1360 IF student_dtl_rec.p_license_num IS NOT NULL AND student_dtl_rec.p_license_state IS NULL THEN
1361 lv_complete := FALSE;
1362 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_DRIV_LIC_NUM_STATE', 'P_LICENSE_NUM', NULL);
1363 END IF;
1364
1365 IF student_dtl_rec.p_license_state IS NOT NULL AND student_dtl_rec.p_license_num IS NULL THEN
1366 lv_complete := FALSE;
1367 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_DRIV_LIC_STATE_NUM', 'P_LICENSE_ST', NULL);
1368 END IF;
1369
1370 -- validating student's citizenship status code
1371 IF ( LENGTH(student_dtl_rec.p_citizenship_status) > 1) THEN
1372 lv_complete := FALSE;
1373 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'S_INV_CIT_STATUS', 'S_CITIZENSHIP_STATUS', student_dtl_rec.p_citizenship_status);
1374 END IF;
1375
1376 -- validating transaction number
1377 IF ( (to_number(p_loan_rec.cps_trans_num) < 1) OR (to_number(p_loan_rec.cps_trans_num) > 99) ) THEN
1378 lv_complete := FALSE;
1379 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INVALID_TRANS_NUM', 'CPS_TRANS_NUM', p_loan_rec.cps_trans_num);
1380 END IF;
1381
1382 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1383 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','CPS Tran Number Check');
1384 END IF;
1385
1386 -- validating loan_per_begin_date
1387 IF p_dl_version = '2004-2005' THEN
1388 IF p_loan_rec.loan_per_begin_date < TO_DATE('02072003', 'DDMMYYYY') OR
1389 p_loan_rec.loan_per_begin_date > TO_DATE('30062005', 'DDMMYYYY') THEN
1390 lv_complete := FALSE;
1391 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_STARTDT0405', 'LOAN_PER_BEGIN', p_loan_rec.loan_per_begin_date );
1392 END IF;
1393 END IF;
1394 -- validating loan_per_end_date
1395 IF p_dl_version = '2004-2005' THEN
1396 IF p_loan_rec.loan_per_end_date < TO_DATE('01072004', 'DDMMYYYY') OR
1397 p_loan_rec.loan_per_end_date > TO_DATE('29062006', 'DDMMYYYY') THEN
1398 lv_complete := FALSE;
1399 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_ENDDT0405', 'LOAN_PER_END_DATE', p_loan_rec.loan_per_end_date);
1400 END IF;
1401 END IF;
1402 -- validating loan_per_begin_date
1403 IF p_dl_version = '2005-2006' THEN
1404 IF p_loan_rec.loan_per_begin_date < TO_DATE('02072004', 'DDMMYYYY') OR
1405 p_loan_rec.loan_per_begin_date > TO_DATE('30062006', 'DDMMYYYY') THEN
1406 lv_complete := FALSE;
1407 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_STARTDT0506', 'LOAN_PER_BEGIN', p_loan_rec.loan_per_begin_date );
1408 END IF;
1409 END IF;
1410 -- validating loan_per_end_date
1411 IF p_dl_version = '2005-2006' THEN
1412 IF p_loan_rec.loan_per_end_date < TO_DATE('01072005', 'DDMMYYYY') OR
1413 p_loan_rec.loan_per_end_date > TO_DATE('29062007', 'DDMMYYYY') THEN
1414 lv_complete := FALSE;
1415 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_ENDDT0506', 'LOAN_PER_END_DATE', p_loan_rec.loan_per_end_date);
1416 END IF;
1417 END IF;
1418 -- validating loan_per_begin_date
1419 IF p_dl_version = '2006-2007' THEN
1420 IF p_loan_rec.loan_per_begin_date < TO_DATE('02072005', 'DDMMYYYY') OR
1421 p_loan_rec.loan_per_begin_date > TO_DATE('30062007', 'DDMMYYYY') THEN
1422 lv_complete := FALSE;
1423 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_STARTDT0607', 'LOAN_PER_BEGIN', p_loan_rec.loan_per_begin_date );
1424 END IF;
1425 END IF;
1426 -- validating loan_per_end_date
1427 IF p_dl_version = '2006-2007' THEN
1428 IF p_loan_rec.loan_per_end_date < TO_DATE('01072006', 'DDMMYYYY') OR
1429 p_loan_rec.loan_per_end_date > TO_DATE('29062008', 'DDMMYYYY') THEN
1430 lv_complete := FALSE;
1431 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_LOAN_PER_ENDDT0607', 'LOAN_PER_END_DATE', p_loan_rec.loan_per_end_date);
1432 END IF;
1433 END IF;
1434
1435 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1436 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Loan Per Dates Check over');
1437 END IF;
1438
1439 -- validating academic begin and end dates
1440 IF (lv_message IS NULL) THEN
1441 -- validating academic begin date
1442 IF p_dl_version = '2004-2005' THEN
1443 IF lv_acad_begin_date IS NULL OR
1444 lv_acad_begin_date < TO_DATE('02072003', 'DDMMYYYY') OR
1445 lv_acad_begin_date > TO_DATE('30062005', 'DDMMYYYY') THEN
1446 lv_complete := FALSE;
1447 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_STARTDT0405', 'ACAD_PER_BEGIN_DATE', lv_acad_begin_date);
1448 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1449 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_acad_begin_date '|| lv_acad_begin_date);
1450 END IF;
1451
1452 END IF;
1453 -- validating academic end date
1454 IF lv_acad_end_date IS NULL OR
1455 lv_acad_end_date < TO_DATE('01072004', 'DDMMYYYY') OR
1456 lv_acad_end_date > TO_DATE('29062006', 'DDMMYYYY') THEN
1457 lv_complete := FALSE;
1458 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_ENDDT0405', 'ACAD_PER_END_DATE', lv_acad_end_date);
1459 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1460 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','lv_acad_end_date '|| lv_acad_end_date);
1461 END IF;
1462
1463 END IF;
1464 END IF;
1465 IF p_dl_version = '2005-2006' THEN
1466 IF lv_acad_begin_date IS NULL OR
1467 lv_acad_begin_date < TO_DATE('02072004', 'DDMMYYYY') OR
1468 lv_acad_begin_date > TO_DATE('30062006', 'DDMMYYYY') THEN
1469 lv_complete := FALSE;
1470 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_STARTDT0506', 'ACAD_PER_BEGIN_DATE', lv_acad_begin_date);
1471 END IF;
1472 -- validating academic end date
1473 IF lv_acad_end_date IS NULL OR
1474 lv_acad_end_date < TO_DATE('01072005', 'DDMMYYYY') OR
1475 lv_acad_end_date > TO_DATE('29062007', 'DDMMYYYY') THEN
1476 lv_complete := FALSE;
1477 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_ENDDT0506', 'ACAD_PER_END_DATE', lv_acad_end_date);
1478 END IF;
1479 END IF;
1480 IF p_dl_version = '2006-2007' THEN
1481 IF lv_acad_begin_date IS NULL OR
1482 lv_acad_begin_date < TO_DATE('02072005', 'DDMMYYYY') OR
1483 lv_acad_begin_date > TO_DATE('30062007', 'DDMMYYYY') THEN
1484 lv_complete := FALSE;
1485 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_STARTDT0607', 'ACAD_PER_BEGIN_DATE', lv_acad_begin_date);
1486 END IF;
1487 -- validating academic end date
1488 IF lv_acad_end_date IS NULL OR
1489 lv_acad_end_date < TO_DATE('01072006', 'DDMMYYYY') OR
1490 lv_acad_end_date > TO_DATE('29062008', 'DDMMYYYY') THEN
1491 lv_complete := FALSE;
1492 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ACAD_PER_ENDDT0607', 'ACAD_PER_END_DATE', lv_acad_end_date);
1493 END IF;
1494 END IF;
1495 END IF;
1496 -- validating note message
1497 IF ( LENGTH(p_loan_rec.note_message) > 20) THEN
1498 lv_complete := FALSE;
1499 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_NOTE_MSG', 'NOTE_MESSAGE', p_loan_rec.note_message);
1500 END IF;
1501
1502 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1503 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Note Message Check Over');
1504 END IF;
1505
1506 -- validating orig_fee_perct
1507 IF ( p_loan_rec.orig_fee_perct >= 100) THEN
1508 lv_complete := FALSE;
1509 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_ORIG_FEE_PCT', 'ORIG_FEE_PCT_NUM', p_loan_rec.orig_fee_perct);
1510 END IF;
1511 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1512 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' ORIG_FEE_PCT_NUM Check Over');
1513 END IF;
1514
1515 -- validating int_reb_pct
1516 IF p_loan_rec.interest_rebate_percent_num >= 100 OR p_loan_rec.interest_rebate_percent_num < 0 THEN
1517 lv_complete := FALSE;
1518 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_INT_REB_PCT', 'INT_REB_PCT_NUM', p_loan_rec.interest_rebate_percent_num);
1519 END IF;
1520
1521 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1522 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' INT_REB_PCT_NUM Check Over');
1523 END IF;
1524
1525 IF p_loan_rec.accepted_amt >= 999999999.99 OR p_loan_rec.accepted_amt < 0 THEN
1526 lv_complete := FALSE;
1527 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_AWD_AMT', 'LOAN_AMT_ACCEPTED', p_loan_rec.accepted_amt);
1528 END IF;
1529 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1530 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' LOAN_AMT_ACCEPTED Check Over');
1531 END IF;
1532
1533 -- validating disbursement data from the IGF_AW_DB_CHG_DTLS table
1534 FOR rec_disb_chg_dtls IN cur_disb_chg_dtls(p_loan_rec.award_id) LOOP
1535 -- validating disbursement gross amount
1536 IF rec_disb_chg_dtls.disb_accepted_amt >= 999999999.99 OR rec_disb_chg_dtls.disb_accepted_amt < 0 THEN
1537 lv_complete := FALSE;
1538 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_AMT', 'DISB_GROSS_AMT', rec_disb_chg_dtls.disb_accepted_amt);
1539 END IF;
1540 IF rec_disb_chg_dtls.disb_net_amt >= 999999999.99 OR rec_disb_chg_dtls.disb_net_amt < 0 THEN
1541 lv_complete := FALSE;
1542 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_AMT', 'DISB_NET_AMT', rec_disb_chg_dtls.disb_net_amt);
1543 END IF;
1544 IF rec_disb_chg_dtls.orig_fee_amt >= 999999999.99 OR rec_disb_chg_dtls.orig_fee_amt < 0 THEN
1545 lv_complete := FALSE;
1546 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_AMT', 'ORIG_FEE_AMT ', rec_disb_chg_dtls.orig_fee_amt);
1547 END IF;
1548 IF rec_disb_chg_dtls.disb_net_amt >= 999999999.99 OR rec_disb_chg_dtls.interest_rebate_amt < 0 THEN
1549 lv_complete := FALSE;
1550 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_AMT', 'INTEREST_REBATE_AMT', rec_disb_chg_dtls.interest_rebate_amt);
1551 END IF;
1552
1553 -- validating disbursement number
1554 IF ( rec_disb_chg_dtls.disb_num >= 21) THEN
1555 lv_complete := FALSE;
1556 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_NUM', 'DISB_NUM', rec_disb_chg_dtls.disb_num);
1557 END IF;
1558 -- validating disbursement sequence number
1559 IF ( rec_disb_chg_dtls.disb_seq_num >= 66) THEN
1560 lv_complete := FALSE;
1561 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_SEQ_NUM', 'DISB_SEQ_NUM', rec_disb_chg_dtls.disb_seq_num);
1562 END IF;
1563 -- validating disbursement date
1564 IF p_dl_version = '2004-2005' THEN
1565 IF rec_disb_chg_dtls.disb_date < TO_DATE('22062003', 'DDMMYYYY') OR
1566 rec_disb_chg_dtls.disb_date > TO_DATE('27102006', 'DDMMYYYY') THEN
1567 lv_complete := FALSE;
1568 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_DATE0405', 'DISB_DATE', rec_disb_chg_dtls.disb_date);
1569 END IF;
1570 END IF;
1571 IF p_dl_version = '2005-2006' THEN
1572 IF rec_disb_chg_dtls.disb_date < TO_DATE('22062004', 'DDMMYYYY') OR
1573 rec_disb_chg_dtls.disb_date > TO_DATE('27102007', 'DDMMYYYY') THEN
1574 lv_complete := FALSE;
1575 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_DATE0506', 'DISB_DATE', rec_disb_chg_dtls.disb_date);
1576 END IF;
1577 END IF;
1578 IF p_dl_version = '2006-2007' THEN
1579 IF rec_disb_chg_dtls.disb_date < TO_DATE('22062005', 'DDMMYYYY') OR
1580 rec_disb_chg_dtls.disb_date > TO_DATE('27102008', 'DDMMYYYY') THEN
1581 lv_complete := FALSE;
1582 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'INV_DISB_DATE0607', 'DISB_DATE', rec_disb_chg_dtls.disb_date);
1583 END IF;
1584 END IF;
1585 END LOOP;
1586 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1587 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' Disb Check Over');
1588 END IF;
1589 --
1590 -- entity id validations
1591 --
1592 IF NOT validate_id(p_loan_rec.atd_entity_id_txt) THEN
1593 lv_complete := FALSE;
1594 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'ATD_SCHL_ID_NUM', 'ATD_ENTITY_ID_TXT', p_loan_rec.atd_entity_id_txt);
1595 ELSE
1596 IF p_loan_rec.atd_entity_id_txt > 99999999 THEN
1597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1598 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' p_loan_rec.atd_entity_id_txt ' ||p_loan_rec.atd_entity_id_txt );
1599 END IF;
1600 lv_complete := FALSE;
1601 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'ATD_SCHL_ID_MAX', 'ATD_ENTITY_ID_TXT', p_loan_rec.atd_entity_id_txt);
1602 END IF;
1603 END IF;
1604
1605 IF NOT validate_id(p_loan_rec.rep_entity_id_txt)THEN
1606 lv_complete := FALSE;
1607 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'REP_SCHL_ID_NUM', 'REP_ENTITY_ID_TXT', p_loan_rec.rep_entity_id_txt);
1608 ELSE
1609 IF p_loan_rec.rep_entity_id_txt > 99999999 THEN
1610 lv_complete := FALSE;
1611 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'REP_SCHL_ID_MAX', 'REP_ENTITY_ID_TXT', p_loan_rec.rep_entity_id_txt);
1612 END IF;
1613 END IF;
1614 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1615 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug',' Entity ID Check Over');
1616 END IF;
1617
1618 IF p_loan_rec.fed_fund_code = 'DLP' THEN
1619
1620 IF p_dl_version = '2004-2005' THEN
1621 -- validating borrower's date of birth
1622 IF parent_dtl_rec.p_date_of_birth < TO_DATE('01011905', 'DDMMYYYY') OR
1623 parent_dtl_rec.p_date_of_birth > TO_DATE('31121996', 'DDMMYYYY') THEN
1624 lv_complete := FALSE;
1625 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INVALID_DOB0405', 'P_DATE_OF_BIRTH', parent_dtl_rec.p_date_of_birth);
1626 END IF;
1627 END IF;
1628 IF p_dl_version = '2005-2006' THEN
1629 -- validating borrower's date of birth
1630 IF parent_dtl_rec.p_date_of_birth < TO_DATE('01011906', 'DDMMYYYY') OR
1631 parent_dtl_rec.p_date_of_birth > TO_DATE('31121997', 'DDMMYYYY') THEN
1632 lv_complete := FALSE;
1633 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INVALID_DOB0506', 'P_DATE_OF_BIRTH', parent_dtl_rec.p_date_of_birth);
1634 END IF;
1635 END IF;
1636 IF p_dl_version = '2006-2007' THEN
1637 -- validating borrower's date of birth
1638 IF parent_dtl_rec.p_date_of_birth < TO_DATE('01011907', 'DDMMYYYY') OR
1639 parent_dtl_rec.p_date_of_birth > TO_DATE('31121998', 'DDMMYYYY') THEN
1640 lv_complete := FALSE;
1641 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INVALID_DOB0607', 'P_DATE_OF_BIRTH', parent_dtl_rec.p_date_of_birth);
1642 END IF;
1643 END IF;
1644 -- validating borrower's SSN
1645 IF ( (parent_dtl_rec.p_ssn < '001010001') OR (parent_dtl_rec.p_ssn > '999999998') ) THEN
1646 lv_complete := FALSE;
1647 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INVALID_SSN', 'P_SSN', parent_dtl_rec.p_ssn);
1648 END IF;
1649
1650 -- validating borrower's first name
1651 IF ( LENGTH(parent_dtl_rec.p_first_name) > 12) THEN
1652 lv_complete := FALSE;
1653 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_FIRST_NAME', 'P_FIRST_NAME', parent_dtl_rec.p_first_name);
1654 END IF;
1655
1656 -- validating borrower's last name
1657 IF ( LENGTH(parent_dtl_rec.p_last_name) > 35) THEN
1658 lv_complete := FALSE;
1659 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_LAST_NAME', 'P_LAST_NAME', parent_dtl_rec.p_last_name);
1660 END IF;
1661
1662 -- validating borrower's address line 1
1663 IF ( (LENGTH(parent_dtl_rec.p_permt_addr1) < 1) OR (LENGTH(parent_dtl_rec.p_permt_addr1) > 40) ) THEN
1664 lv_complete := FALSE;
1665 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_ADDR_LINE', 'P_PERMT_ADDR1', parent_dtl_rec.p_permt_addr1);
1666 END IF;
1667
1668 -- validating borrower's address line 2
1669 IF ( (LENGTH(parent_dtl_rec.p_permt_addr2) < 1) OR (LENGTH(parent_dtl_rec.p_permt_addr2) > 40) ) THEN
1670 lv_complete := FALSE;
1671 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_ADDR_LINE', 'P_PERMT_ADDR2', parent_dtl_rec.p_permt_addr2);
1672 END IF;
1673
1674 -- validating borrower's city
1675 IF ( LENGTH(parent_dtl_rec.p_permt_city) > 24) THEN
1676 lv_complete := FALSE;
1677 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_CITY', 'P_PERMT_CITY', parent_dtl_rec.p_permt_city);
1678 END IF;
1679
1680 -- validating borrower's E-MAIL
1681 IF ( LENGTH(parent_dtl_rec.p_email_addr) > 128) THEN
1682 lv_complete := FALSE;
1683 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_EMAIL', 'P_EMAIL_ADDR', parent_dtl_rec.p_email_addr);
1684 END IF;
1685
1686 -- validating borrower's Phone Number
1687 l_p_phone := igf_sl_gen.get_person_phone(p_loan_rec.p_person_id);
1688 IF l_p_phone = 'N/A' THEN
1689 p_p_phone := NULL; --bug 4093556, When there is no phone number existing for a student then the phone number tag was populated using '000000000000' .
1690 --This has being changed to NULL(akomurav)
1691 ELSE
1692 IF NOT validate_id(l_p_phone) THEN
1693 lv_complete := FALSE;
1694 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_PHONE_NUM_D', 'P_PERMT_PHONE', l_p_phone);
1695 ELSE
1696 p_p_phone := l_p_phone;
1697 END IF;
1698 IF LENGTH(l_p_phone) > 17 OR LENGTH(l_p_phone) < 10 THEN
1699 lv_complete := FALSE;
1700 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_PHONE_NUM', 'P_PERMT_PHONE', l_p_phone);
1701 END IF;
1702 END IF;
1703 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1704 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','l_p_phone '|| l_p_phone);
1705 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','p_p_phone '|| p_p_phone);
1706 END IF;
1707
1708 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1709 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Before zip vali');
1710 END IF;
1711
1712 -- validating borrower's Zip Code
1713 IF ( (LENGTH(parent_dtl_rec.p_permt_zip) < 5) OR (LENGTH(parent_dtl_rec.p_permt_zip) > 13) ) THEN
1714 lv_complete := FALSE;
1715 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_ZIP_CODE', 'P_PERMT_ZIP', parent_dtl_rec.p_permt_zip);
1716 END IF;
1717
1718 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1719 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_permt_zip '|| parent_dtl_rec.p_permt_zip);
1720 END IF;
1721
1722 -- validating borrower's State Code
1723 IF ( (LENGTH(parent_dtl_rec.p_permt_state) < 2) OR (LENGTH(parent_dtl_rec.p_permt_state) > 3) ) THEN
1724 lv_complete := FALSE;
1725 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_STATE_CODE', 'P_PERMT_STATE', parent_dtl_rec.p_permt_state);
1726 END IF;
1727
1728 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1729 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_permt_state '|| parent_dtl_rec.p_permt_state);
1730 END IF;
1731
1732 -- validating borrower's driver licencse number
1733 IF ( LENGTH(parent_dtl_rec.p_license_num) > 20) THEN
1734 lv_complete := FALSE;
1735 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_DRIV_LIC_NUM', 'P_LICENSE_NUM', parent_dtl_rec.p_license_num);
1736 END IF;
1737
1738 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1739 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_license_num '|| parent_dtl_rec.p_license_num);
1740 END IF;
1741
1742 IF parent_dtl_rec.p_license_num IS NOT NULL AND parent_dtl_rec.p_license_state IS NULL THEN
1743 lv_complete := FALSE;
1744 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_DRIV_LIC_NUM_STATE', 'P_LICENSE_NUM', NULL);
1745 END IF;
1746
1747 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1748 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_license_state '|| parent_dtl_rec.p_license_state);
1749 END IF;
1750
1751 IF parent_dtl_rec.p_license_state IS NOT NULL AND parent_dtl_rec.p_license_num IS NULL THEN
1752 lv_complete := FALSE;
1753 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_DRIV_LIC_STATE_NUM', 'P_LICENSE_ST', NULL);
1754 END IF;
1755
1756 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1757 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_license_num '|| parent_dtl_rec.p_license_num);
1758 END IF;
1759
1760 -- validating borrower's citizenship status code
1761 IF ( LENGTH(parent_dtl_rec.p_citizenship_status) > 1) THEN
1762 lv_complete := FALSE;
1763 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'P_INV_CIT_STATUS', 'P_CITIZENSHIP_STATUS', parent_dtl_rec.p_citizenship_status);
1764 END IF;
1765
1766 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1767 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','parent_dtl_rec.p_citizenship_status '|| parent_dtl_rec.p_citizenship_status);
1768 END IF;
1769
1770 END IF;
1771
1772 -- museshad (Bug 4116399) - Stafford loan limit validation
1773 IF p_loan_rec.fed_fund_code IN ('DLS', 'DLU') THEN
1774 lv_message := NULL;
1775 lv_lookup_code := NULL;
1776
1777 loan_limit_validation (
1778 p_base_id => p_loan_rec.base_id,
1779 p_fund_type => p_loan_rec.fed_fund_code,
1780 p_award_id => p_loan_rec.award_id,
1781 p_msg_name => lv_message
1782 );
1783
1784 IF lv_message IS NOT NULL THEN
1785 -- Stafforf loan limit validation FAILED
1786 lv_complete := FALSE;
1787
1788 IF lv_message = 'IGF_AW_AGGR_LMT_ERR' THEN
1789 lv_lookup_code := 'AGGR_LMT_CHK';
1790 ELSIF lv_message = 'IGF_AW_ANNUAL_LMT_ERR' THEN
1791 lv_lookup_code := 'ANNUAL_LMT_CHK';
1792 ELSIF lv_message = 'IGF_AW_SUB_AGGR_LMT_ERR' THEN
1793 lv_lookup_code := 'SUB_AGGR_LMT_CHK';
1794 ELSIF lv_message = 'IGF_AW_SUB_LMT_ERR' THEN
1795 lv_lookup_code := 'SUB_LMT_CHK';
1796 ELSIF lv_message = 'IGF_AW_LOAN_LMT_NOT_FND' THEN
1797 lv_lookup_code := 'LOAN_LMT_SETUP_CHK';
1798 END IF;
1799
1800 igf_sl_edit.insert_edit(p_loan_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', lv_lookup_code, NULL, NULL);
1801
1802 -- Log
1803 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1804 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Stafford loan limit validation for award_id ' ||p_loan_rec.award_id|| ' FAILED with message: ' ||lv_message);
1805 END IF;
1806 ELSE
1807 -- Stafforf loan limit validation PASSED
1808 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1809 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.debug','Stafford loan limit validation PASSED for award_id ' ||p_loan_rec.award_id);
1810 END IF;
1811 END IF;
1812 END IF;
1813 -- museshad (Bug 4116399)
1814
1815 IF lv_complete = FALSE AND p_call_from = 'JOB' THEN
1816 -- Display reject details on the Concurrent Manager Log File.
1817 DECLARE
1818 lv_log_mesg VARCHAR2(1000);
1819 CURSOR c_reject IS
1820 SELECT rpad(field_desc,50)||sl_error_desc reject_desc FROM igf_sl_edit_report_v
1821 WHERE loan_number = p_loan_rec.loan_number
1822 AND orig_chg_code = 'V';
1823 BEGIN
1824 fnd_file.put_line(fnd_file.log, '');
1825 fnd_file.put_line(fnd_file.log, '');
1826 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER')||' : '||p_loan_rec.loan_number;
1827 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1828 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_SSN') ||' : '||student_dtl_rec.p_ssn;
1829 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1830 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_FULL_NAME')||' : '
1831 ||student_dtl_rec.p_first_name||' '||student_dtl_rec.p_last_name;
1832 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1833 FOR rej_rec IN c_reject LOOP
1834 fnd_file.put_line(fnd_file.log,' '||rej_rec.reject_desc);
1835 END LOOP;
1836 END;
1837 END IF;
1838
1839 RETURN lv_complete;
1840
1841 EXCEPTION
1842 WHEN OTHERS THEN
1843 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1844 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_validation.cod_loan_validations.exception','Exception:'||SQLERRM);
1845 END IF;
1846 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1847 fnd_message.set_token('NAME','IGF_SL_DL_VALIDATION.COD_LOAN_VALIDATIONS');
1848 igs_ge_msg_stack.add;
1849 app_exception.raise_exception;
1850 END cod_loan_validations;
1851
1852
1853 FUNCTION cod_loan_validations ( p_cal_type VARCHAR2, p_seq_number NUMBER,
1854 p_base_id NUMBER,
1855 p_report_id VARCHAR2,
1856 p_attend_id VARCHAR2,
1857 p_fund_id NUMBER,
1858 p_loan_id NUMBER)
1859 RETURN BOOLEAN
1860 IS
1861 loan_rec igf_sl_dl_gen_xml.cur_pick_loans_all_status%ROWTYPE;
1862 p_isir_ssn VARCHAR2(30);
1863 p_isir_dob DATE;
1864 p_isir_lname VARCHAR2(100);
1865 p_isir_dep VARCHAR2(1);
1866 p_isir_tnum NUMBER;
1867 p_acad_begin DATE;
1868 p_acad_end DATE;
1869 p_s_phone VARCHAR2(30);
1870 p_p_phone VARCHAR2(30);
1871
1872 BEGIN
1873 OPEN igf_sl_dl_gen_xml.cur_pick_loans_all_status(p_cal_type, p_seq_number,
1874 p_base_id,
1875 p_report_id,
1876 p_attend_id,
1877 p_fund_id,
1878 p_loan_id);
1879 FETCH igf_sl_dl_gen_xml.cur_pick_loans_all_status INTO loan_rec;
1880 CLOSE igf_sl_dl_gen_xml.cur_pick_loans_all_status;
1881
1882 loan_rec.grade_level_code := NVL(loan_rec.override_grade_level_code,loan_rec.grade_level_code);
1883
1884 RETURN cod_loan_validations(loan_rec,'FORM',p_isir_ssn,
1885 p_isir_dob,p_isir_lname,
1886 p_isir_dep,p_isir_tnum,
1887 p_acad_begin,p_acad_end,p_s_phone,p_p_phone);
1888
1889 END cod_loan_validations;
1890
1891 FUNCTION check_full_participant (
1892 p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1893 p_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
1894 p_fund_type IN VARCHAR2
1895 )
1896 RETURN BOOLEAN
1897 AS
1898 /*************************************************************
1899 Created By : ugummall
1900 Date Created On : 01-OCT-2004
1901 Purpose :
1902 Know limitations, enhancements or remarks
1903 Change History
1904 Who When What
1905
1906 (reverse chronological order - newest change first)
1907 ***************************************************************/
1908
1909 -- Cursor to get pell participant code and direct loan participant code
1910 CURSOR cur_get_participants_codes ( cp_cal_type igs_ca_inst_all.cal_type%TYPE,
1911 cp_sequence_number igs_ca_inst_all.sequence_number%TYPE) IS
1912 SELECT bam.dl_participant_code, bam.pell_participant_code
1913 FROM igf_ap_batch_aw_map_all bam
1914 WHERE bam.ci_cal_type = cp_cal_type
1915 AND bam.ci_sequence_number = cp_sequence_number
1916 AND bam.award_year_status_code = 'O';
1917
1918 rec_get_participants_codes cur_get_participants_codes%ROWTYPE;
1919
1920 BEGIN
1921
1922 IF (p_fund_type IS NULL OR p_ci_cal_type IS NULL OR p_ci_sequence_number IS NULL) THEN
1923 RETURN FALSE;
1924 END IF;
1925
1926 -- open the cursor to get participant codes.
1927 OPEN cur_get_participants_codes(p_ci_cal_type, p_ci_sequence_number);
1928 FETCH cur_get_participants_codes INTO rec_get_participants_codes;
1929 IF cur_get_participants_codes%NOTFOUND THEN
1930 CLOSE cur_get_participants_codes;
1931 RETURN FALSE;
1932 ELSE
1933 CLOSE cur_get_participants_codes;
1934 IF (p_fund_type = 'PELL') THEN
1935 IF (rec_get_participants_codes.pell_participant_code = 'FULL_PARTICIPANT') THEN
1936 RETURN TRUE;
1937 ELSE
1938 RETURN FALSE;
1939 END IF;
1940 ELSIF ( p_fund_type = 'DL') THEN
1941 IF (rec_get_participants_codes.dl_participant_code = 'FULL_PARTICIPANT') THEN
1942 RETURN TRUE;
1943 ELSE
1944 RETURN FALSE;
1945 END IF;
1946 ELSE
1947 RETURN FALSE;
1948 END IF;
1949 END IF;
1950
1951 EXCEPTION
1952 WHEN OTHERS THEN
1953 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1954 fnd_message.set_token('NAME','IGF_SL_DL_VALIDATION.CHECK_FULL_PARTICIPANT');
1955 igs_ge_msg_stack.add;
1956 app_exception.raise_exception;
1957
1958 END check_full_participant;
1959
1960 FUNCTION validate_id (p_entity_id VARCHAR2)
1961 RETURN BOOLEAN
1962 IS
1963 lv_compare_str VARCHAR2(80);
1964 BEGIN
1965 lv_compare_str := '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ"[]{}`~!@#$%^&*_+=-,./?><():; ' ||'''';
1966 IF p_entity_id = TRANSLATE(UPPER(p_entity_id),lv_compare_str ,'1234567890') THEN
1967 RETURN TRUE;
1968 ELSE
1969 RETURN FALSE;
1970 END IF;
1971
1972 EXCEPTION
1973 WHEN OTHERS THEN
1974 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1975 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_validation.validate_id.exception','Exception:'||SQLERRM);
1976 END IF;
1977 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1978 fnd_message.set_token('NAME','IGF_SL_DL_VALIDATION.VALIDATE_ID');
1979 igs_ge_msg_stack.add;
1980 app_exception.raise_exception;
1981
1982 END validate_id;
1983
1984 END igf_sl_dl_validation;
[Home] [Help]