1 PACKAGE BODY igf_sl_cl_validation AS
2 /* $Header: IGFSL07B.pls 120.10 2006/08/08 06:27:07 akomurav ship $ */
3
4 /*
5 --
6 -----------------------------------------------------------------------------------
7 -- Who When What
8 ----------------------------------------------------------------------------------
9 rajagupt 02-Mar-2006 FA 161 - Bug # 5006587 - CL4 Addendum
10 Two new columns (borrower alien reg number and e-signature source type code) +
11 Validations based on fed appl form code
12 museshad 05-Oct-2005 Bug 4116399.
13 Added Stafford loan limit validation
14 bvisvana 12-Sep-2005 SBCC Bug # 4575843 - Check for requested loan amount. It should be in whole number.
15 museshad 11-Aug-2005 Bug 4103922.
16 Disbursement Hold/Release indicator validation was
17 incorrect. Fixed this.
18 mnade 10-Jan-2005 Bug - 4103342
19 Validate the Requested Amount and Accepted Amount.
20 smadathi 29-oct-2004 Bug 3416936. Added new business logic as part of
21 CL4 changes
22 brajendr 12-Oct-2004 FA138 ISIR Enhacements
23 Modified the reference of payment_isir_id
24
25 -- veramach 04-May-2004 bug 3603289
26 -- Modified cursor cur_student_licence to select
27 -- dependency_status from ISIR. other details are
28 -- derived from igf_sl_gen.get_person_details.
29 -----------------------------------------------------------------------------------
30 -- ugummall 29-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
31 -- 1. Added two new parameters p_school_id and p_base_id to cl_lar_validate function.
32 -- 2. Changed the cursor cur_lor_details_recs to select only those records related to
33 -- School ID(OPE ID) and Base ID if they are not null.
34 -- Otherwise(if they are null) select all.
35 -- 3. Changed the cursor cur_school_id to fetch only one attribute eft_authorization.
36 -- 4. p_school_id is used instead of lv_school_id(fetched from cur_school_id)
37 -- veramach 19-SEP-2003 FA 122 Loan Enhancements Build
38 -- 1. changed cursor cur_lor_details_recs not to select student/borrower information.
39 -- This is derived from igf_sl_gen.get_person_details
40 ----------------------------------------------------------------------------------
41 -- gmuralid 03-07-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
42 -- Added legacy record flag as parameter to
43 -- igf_sl_loans_pkg
44 -----------------------------------------------------------------------------------
45 -- sjadhav 26-Mar-2003 Bug 2863960
46 -- Modified cursor cur_count_disb_amount to replace
47 -- disb gross amount with disb accepted amount
48 -----------------------------------------------------------------------------------
49 -- masehgal 08-Jan-2003 # 2593215 Removed redundant calls to
50 -- begin/end date fetching functions of SL11B.
51 -----------------------------------------------------------------------------------
52 */
53 -- Bug 2415041, sjadhav
54 -- Following fields are deemed as optional henceforth
55 --
56 -- S_PERMT_ADDR2
57 -- P_PERMT_ADDR2
58 -- S_MIDDLE_NAME
59 -- P_MIDDLE_NAME
60 -- S_PERMT_PHONE
61 -- P_PERMT_PHONE
62 --
63 -- set_complete_status for these fields is taken out
64 -- fill in spaces for addr2/middle initial/
65 -- phone number is take care of in igfsl12bpls package
66 -- all these fields if null are filled with spaces
67 -- while sending origination record [ see igfsl08bpls ]
68 --
69 --
70 -----------------------------------------------------------------------------------
71 --
72 -- Created By : mesriniv
73 -- Date Created By : 2000/11/17
74 -- Purpose : To validate the Commom Line Loans
75 -- from Loans Origination and set the
76 -- Loan Status accordingly
77 -- Known Limitations,Enhancements or Remarks
78 --
79 -----------------------------------------------------------------------------------
80 --
81 p_cl_version VARCHAR2(30);
82 g_loan_id igf_sl_Loans_all.loan_id%TYPE;
83 g_debug_runtime_level NUMBER;
84 g_update_mode_required BOOLEAN := FALSE;
85
86 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
87 p_v_log_category IN VARCHAR2,
88 p_v_string IN VARCHAR2 ) AS
89 ------------------------------------------------------------------
90 --Created by : bvisvana, Oracle IDC
91 --Date created: 10 Apr 2005
92 --Known limitations/enhancements and/or remarks:
93 --
94 --Change History:
95 --Who When What
96 ------------------------------------------------------------------
97 BEGIN
98 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
99 fnd_log.string( fnd_log.level_statement, 'igf.plsql.igf_sl_cl_validation.'||p_v_module||'.'||p_v_log_category, p_v_string);
100 END IF;
101 END log_to_fnd;
102
103 FUNCTION check_for_reqd(
104 p_loan_number igf_sl_loans.loan_number%TYPE,
105 p_loan_catg igf_sl_reqd_fields.loan_type%TYPE,
106 p_field_name igf_sl_reqd_fields.field_name%TYPE,
107 p_field_value VARCHAR2,
108 p_prc_type_code igf_sl_reqd_fields.prc_type_code%TYPE
109 ) RETURN BOOLEAN
110 AS
111
112 /***************************************************************
113 Created By : mesriniv
114 Date Created By : 2000/11/17
115 Purpose : To Check if the Column value is NULL and then insert a record
116 into the Edit Report Table specifying that the Column Value
117 is Recommended or Strongly Recommended.
118 Known Limitations,Enhancements or Remarks
119 Change History :
120 Who When What
121 ***************************************************************/
122 lv_complete BOOLEAN := TRUE;
123 lv_err_type fnd_lookups.lookup_type%TYPE;
124 lv_data_reqd fnd_lookups.lookup_code%TYPE;
125 lv_data_recomm fnd_lookups.lookup_code%TYPE;
126
127 -- Value of Spec Version in Table is same as Loan Type sent as Parameter
128
129 CURSOR cur_reqd IS
130 SELECT DISTINCT status from igf_sl_reqd_fields lrf
131 WHERE lrf.loan_type = p_loan_catg
132 AND lrf.field_name = p_field_name
133 AND lrf.spec_version = p_cl_version
134 -- AND lrf.transaction_type = '@1'
135 AND lrf.transaction_type IN ('@1','@4') -- To Include @4 Record for borrower and Cosigner details
136 AND lrf.prc_type_code = p_prc_type_code;
137
138 BEGIN
139
140 lv_err_type := 'IGF_SL_ERR_CODES';
141 lv_data_reqd := 'DATA_REQD';
142 lv_data_recomm := 'DATA_RECOMM';
143
144 IF p_field_value IS NULL THEN
145 FOR irec in cur_reqd LOOP
146 IF irec.status = 'R' THEN
147 -- If the Data is required.
148 lv_complete := FALSE;
149 igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_reqd, p_field_name, p_field_value);
150 log_to_fnd('check_for_reqd','debug','irec.status:R');
151 ELSIF irec.status = 'S' THEN
152 -- If the Data is strongly recommended.
153 igf_sl_edit.insert_edit(p_loan_number, 'V', lv_err_type, lv_data_recomm, p_field_name, p_field_value);
154 log_to_fnd('check_for_reqd','debug','irec.status:S');
155 END IF;
156 END LOOP;
157 END IF;
158 RETURN lv_complete;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
163 fnd_message.set_token('NAME','IGF_SL_CL_VALIDATION.CHECK_FOR_REQD');
164 fnd_file.put_line(fnd_file.log,SQLERRM);
165 igs_ge_msg_stack.add;
166 IF (fnd_log.level_exception >= g_debug_runtime_level) THEN
167 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_cl_validation.check_for_reqd.exception', 'Unhandled excepion in check_for_reqd');
168 END IF;
169 app_exception.raise_exception;
170
171 END check_for_reqd;
172
173
174 FUNCTION cl_lar_validate(
175 p_ci_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
176 p_ci_sequence_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
177 p_loan_number IN igf_sl_loans_all.loan_number%TYPE,
178 p_loan_catg IN igf_lookups_view.lookup_code%TYPE,
179 p_call_mode IN VARCHAR2,
180 p_school_id IN VARCHAR2,
181 p_base_id IN VARCHAR2
182 )
183 RETURN BOOLEAN
184 AS
185 /***************************************************************
186 Created By : mesriniv
187 Date Created By : 2000/11/17
188 Purpose : To Validate the Common Line Loans.
189 Known Limitations,Enhancements or Remarks
190
191 Change History :
192 FA 122 Loan Enhancements Build
193 changed cursor cur_lor_details_recs not to select student/borrower information.
194 This is derived from igf_sl_gen.get_person_details
195
196 Bug No: 2400487 Desc : FFELP Validation and Origination Issues.
197 Who When What
198 rajagupt 10-Apr-2006 Bug 5006587 . Removed validations (set_complete_status()) for the following field names
199 For PLUS -> S_DEFAULT_STATUS,BORW_INTEREST_IND,P_STATE_OF_LEGAL_RES,S_SIGNATURE_CODE,BORW_OUTSTD_LOAN_CODE
200 museshad 05-Oct-2005 Bug 4116399.
201 Added Stafford loan limit validation.
202 bvisvana 25-Aug-2005 Bug 4127532. Removed the Validation of checking default status of borrower and student.
203 For ALT loans, the loan should be sent irrespective of the default status of the borrower or the student.
204 museshad 11-Aug-2005 Bug 4103922.
205 Disbursement Hold/Release indicator validation was incorrect.
206 The variable 'ln_disb_amt_count' was used in the place of
207 'ln_disb_ind_count'. Corrected this.
208 smadathi 29-oct-2004 Bug 3416936. Added new business logic as part of
209 CL4 changes
210 ridas 17-SEP-04 Bug #3691137: Query optimized by using the table igf_sl_cl_recipient
211 instead of the view igf_sl_cl_recipient_v
212
213 bkkumar 10-apr-04 FACR116 - Added the validation for ALT_LOAN_CODE
214 mesriniv 8-jun-2002 Commented Check for P_SIGNATURE_CODE for CL_STAFFORD
215 Commented Check for LOAN_SEQ_NUMBER for all types of Loans
216 Added Check for S_SIGNATURE_CODE for CL_STAFFORD.
217
218 Bug Id : 1769051 - Developement for Nov 2001 release
219 Who When What
220 avenkatr 09-MAY-2001 1. Added validations for 'Alternate' Loans
221
222 Bug Id : 1720677 Desc : Mapping of school id in the CommonLine Setup
223 to ope_id of FinancialAid Office Setup.
224 Who When What
225 mesriniv 05-APR-2001 Changed the occurrences of field fao_id
226 to ope_id
227 ***************************************************************/
228
229 lv_loan_status igf_sl_loans_all.loan_status%TYPE;
230 lv_s_citizenship_status igf_ap_isir_matched.citizenship_status%TYPE;
231 lv_s_license_number igf_ap_isir_matched.driver_license_number%TYPE;
232 lv_s_license_state igf_ap_isir_matched.driver_license_state%TYPE;
233 lv_dependency_status igf_ap_isir_matched.dependency_status%TYPE;
234 ln_disb_amt_count NUMBER(2);
235 ln_disb_ind_count NUMBER(2);
236 ln_disb_dates_count NUMBER(2);
237 l_phone VARCHAR2(100);
238
239 student_dtl_rec igf_sl_gen.person_dtl_rec;
240 student_dtl_cur igf_sl_gen.person_dtl_cur;
241
242 parent_dtl_rec igf_sl_gen.person_dtl_rec;
243 parent_dtl_cur igf_sl_gen.person_dtl_cur;
244
245 l_n_coa igf_ap_fa_base_rec_all.coa_f%TYPE;
246 l_n_efc igf_ap_fa_base_rec_all.efc_f%TYPE;
247 l_n_pell_efc NUMBER;
248 l_n_efc_f NUMBER;
249 lvc_check_loan VARCHAR2(3);
250 lv_complete BOOLEAN := TRUE;
251 lv_msg_name fnd_new_messages.message_name%TYPE;
252 lv_lookup_code igf_lookups_view.LOOKUP_CODE%TYPE;
253 lv_warning BOOLEAN := FALSE;
254 p_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE;
255 p_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE;
256 p_status_1 igf_sl_loans.loan_status%TYPE;
257 p_status_2 igf_sl_loans.loan_status%TYPE;
258 p_status_3 igf_sl_loans.loan_status%TYPE;
259 p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE;
260 p_seq_number igf_ap_fa_base_rec.ci_sequence_number%TYPE;
261
262 -- Query optimized by using the table igf_sl_cl_recipient instead of the view igf_sl_cl_recipient_v (bug #3691137)
263
264 -- masehgal # 2593215 there was a call to begin/end date fetching functions of SL11B.
265 -- However, these are not getting used anywhere .. therefore removing them ...
266
267 -- Take data from LOR View satisfying the Input Parameters
268 -- If p_call_mode = "JOB", then records with Loan Status with Ready To Send ('G')
269 -- ELSE, if "FORM", then we need to validate for "Ready to Send", "Not Ready", "Rejected"
270
271
272 CURSOR cur_lor_details_recs(
273 p_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE,
274 p_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE,
275 p_status_1 igf_sl_loans.loan_status%TYPE,
276 p_status_2 igf_sl_loans.loan_status%TYPE,
277 p_status_3 igf_sl_loans.loan_status%TYPE,
278 p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
279 p_seq_number igf_ap_fa_base_rec.ci_sequence_number%TYPE,
280 p_active igf_sl_loans.active%TYPE,
281 p_school_id VARCHAR2,
282 p_base_id VARCHAR2
283 )IS
284 SELECT loans.row_id,
285 loans.loan_id,
286 loans.loan_number,
287 loans.award_id,
288 awd.accepted_amt loan_amt_accepted,
289 lor.requested_loan_amt,
290 loans.loan_per_begin_date,
291 loans.loan_per_end_date,
292 lor.orig_fee_perct,
293 lor.pnote_print_ind,
294 lor.s_default_status,
295 lor.p_default_status,
296 lor.p_person_id,
297 lor.sch_cert_date,
298 lor.prc_type_code,
299 lor.anticip_compl_date,
300 lor.cl_loan_type,
301 lor.borw_interest_ind,
302 lor.grade_level_code,
303 lor.enrollment_code,
304 lor.req_serial_loan_code,
305 lor.pnote_delivery_code,
306 lor.s_signature_code,
307 lor.p_signature_code,
308 lor.borw_outstd_loan_code,
309 lor.cl_seq_number,
310 lor.rec_type_ind,
311 lor.p_signature_date,
312 lor.borr_sign_ind,
313 lor.stud_sign_ind,
314 lor.borr_credit_auth_code,
315 lor.origination_id,
316 lor.relationship_cd,
317 lor.eft_authorization_code,
318 lor.b_alien_reg_num_txt, -- FA 161 - Bug # 5006583
319 lor.deferment_request_code,
320 lor.loan_app_form_code,
321 recip.lender_id,
322 recip.guarantor_id,
323 recip.lend_non_ed_brc_id,
324 recip.lender_id borw_lender_id,
325 fabase.base_id,
326 fabase.person_id student_id,
327 awd.accepted_amt,
328 fcat.fed_fund_code,
329 fcat.alt_loan_code,
330 TRUNC(fabase.coa_f) coa_f,
331 loans.external_loan_id_txt
332 FROM igf_sl_loans loans,
333 igf_sl_lor lor,
334 igf_aw_award awd,
335 igf_aw_fund_mast fmast,
336 igf_aw_fund_cat fcat,
337 igf_ap_fa_base_rec fabase,
338 igf_sl_cl_recipient recip
339 WHERE fabase.ci_cal_type = p_cal_type
340 AND fabase.ci_sequence_number = p_seq_number
341 AND fabase.base_id = awd.base_id
342 AND fabase.base_id = NVL(p_base_id, fabase.base_id)
343 AND awd.fund_id = fmast.fund_id
344 AND fmast.fund_code = fcat.fund_code
345 AND (fcat.fed_fund_code = p_fed_fund_1 OR fcat.fed_fund_code = p_fed_fund_2)
346 AND loans.award_id = awd.award_id
347 AND loans.loan_number = NVL(p_loan_number,loans.loan_number)
348 AND loans.loan_id = lor.loan_id
349 AND (loans.loan_status = p_status_1 OR loans.loan_status = p_status_2 OR loans.loan_status = p_status_3)
350 AND loans.active = p_active
351 AND lor.relationship_cd = recip.relationship_cd
352 AND substr(loans.loan_number, 1, 6) = NVL(substr(p_school_id, 1, 6), substr(loans.loan_number, 1, 6));
353
354
355 lor_rec_temp cur_lor_details_recs%ROWTYPE;
356 -- Cursor to fetch Student License No.,State and Citizenship Status
357
358 CURSOR cur_isir_depend_status IS
359 SELECT isir.dependency_status
360 FROM igf_ap_isir_matched isir
361 WHERE isir.payment_isir = 'Y'
362 AND isir.system_record_type = 'ORIGINAL'
363 AND isir.base_id=lor_rec_temp.base_id;
364
365 -- Cursor to find whether any of the disbursment amounts is zero or less than zero
366
367 CURSOR cur_count_disb_amount
368 IS
369 SELECT COUNT(award_id)
370 FROM igf_aw_awd_disb awdb
371 WHERE award_id = lor_rec_temp.award_id
372 AND disb_accepted_amt <= 0;
373
374 -- Cursor to find whether any of the disbursment indicators is NULL
375
376 CURSOR cur_count_disb_ind IS
377 SELECT COUNT(award_id)
378 FROM igf_aw_awd_disb awdb
379 WHERE award_id=lor_rec_temp.award_id
380 AND hold_rel_ind IS NULL;
381
382 -- Cursor to find whether any of the disbursment dates is NULL
383
384 CURSOR cur_count_disb_dates IS
385 SELECT COUNT(award_id)
386 FROM igf_aw_awd_disb awdb
387 WHERE award_id=lor_rec_temp.award_id
388 AND disb_date IS NULL;
389
390
391 -- Cursor to fetch the School Id and Non Ed Branch Id for CL Loan
392 -- FA 126 - Multiple FA Offices.
393 -- This cursor is modified to fetch only one attribute eft_authorization.
394 -- School Id is now available as passed in parameter p_school_id.
395 -- Non Ed Branch is not used.
396
397 CURSOR c_nof_awd_disb (cp_n_award_id igf_aw_award_all.award_id%TYPE) IS
398 SELECT COUNT(awd.disb_num) tot_disb
399 FROM igf_aw_awd_disb_all awd
400 WHERE awd.award_id = cp_n_award_id
401 GROUP BY awd.award_id
402 HAVING COUNT(awd.disb_num) > 4;
403
404 -- FA 161 - Bug # 5006583
405 CURSOR citizenship_dtl_cur (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE) IS
406 SELECT
407 pct.restatus_code
408 FROM igs_pe_eit_restatus_v pct
409 WHERE pct.person_id = cp_person_id AND
410 SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
411 citizenship_dtl_rec citizenship_dtl_cur%ROWTYPE;
412
413 CURSOR cur_fa_mapping ( cp_citizenship_status igf_sl_pe_citi_map.pe_citi_stat_code%TYPE,
414 cp_cal_type igf_sl_pe_citi_map.ci_cal_type%TYPE,
415 cp_sequence_number igf_sl_pe_citi_map.ci_sequence_number%TYPE) IS
416 SELECT trim(fa_citi_stat_code) fa_citi_stat_code FROM igf_sl_pe_citi_map
417 WHERE pe_citi_stat_code = cp_citizenship_status
418 AND ci_sequence_number = cp_sequence_number
419 AND ci_cal_type = cp_cal_type;
420 cur_fa_mapping_rec cur_fa_mapping%ROWTYPE;
421
422 l_n_disb_cnt NUMBER;
423
424
425 -- Procedure to Set the Status of Record
426 PROCEDURE set_complete_status(p_complete BOOLEAN)
427 AS
428 /***************************************************************
429 Created By : mesriniv
430 Date Created By : 2000/11/17
431 Purpose : To Set the Completeness of Record
432 Known Limitations,Enhancements or Remarks
433 Change History :
434 Who When What
435 agairola 15-Mar-2002 Modified the IGF_SL_LOANS_PKG update row call
436 as part of Refunds DLD
437
438 museshad 05-May-2005 Bug# 4346258
439 Added the parameter 'base_id' in the call to the
440 function get_cl_version(). The signature of
441 this function has been changed so that it takes
442 into account any overriding CL version for a
443 specific Organization Unit in FFELP Setup override.
444 ***************************************************************/
445 BEGIN
446 IF p_complete = FALSE THEN
447 lv_complete := FALSE;
448 log_to_fnd('set_complete_status','debug','status=FALSE');
449 END IF;
450 END set_complete_status;
451
452 FUNCTION unmetneed(
453 p_base_id IN NUMBER
454 ) RETURN NUMBER AS
455 ------------------------------------------------------------------
456 --Created by : veramach, Oracle India
457 --Date created: 24/June/2005
458 --
459 --Purpose: Bug 4440482 - Find unmet need of student
460 --
461 --
462 --Known limitations/enhancements and/or remarks:
463 --
464 --Change History:
465 --Who When What
466 -------------------------------------------------------------------
467 p_resource_f NUMBER;
468 p_resource_i NUMBER;
469 p_unmet_need_f NUMBER;
470 p_unmet_need_i NUMBER;
471 p_resource_f_fc NUMBER;
472 p_resource_i_fc NUMBER;
473
474 CURSOR resource_cur IS
475 SELECT NVL (SUM (NVL (disb.disb_gross_amt, 0)), 0) resource_f,
476 NVL (SUM (DECODE (fm.replace_fc,
477 'Y', NVL (disb.disb_gross_amt, 0),
478 0
479 )),
480 0) resource_fm_f
481 FROM igf_aw_awd_disb_all disb,
482 igf_aw_award_all awd,
483 igf_aw_fund_mast_all fm,
484 (SELECT base_id,
485 ld_cal_type,
486 ld_sequence_number
487 FROM igf_aw_coa_itm_terms
488 WHERE base_id = p_base_id
489 GROUP BY base_id, ld_cal_type, ld_sequence_number) coa
490 WHERE awd.fund_id = fm.fund_id
491 AND awd.award_id = disb.award_id
492 AND awd.base_id = p_base_id
493 AND disb.ld_cal_type = coa.ld_cal_type
494 AND disb.ld_sequence_number = coa.ld_sequence_number
495 AND awd.base_id = coa.base_id
496 AND disb.trans_type <> 'C'
497 AND awd.award_status IN ('OFFERED', 'ACCEPTED');
498
499 resource_rec resource_cur%ROWTYPE;
500 ln_coa NUMBER;
501 ln_efc NUMBER;
502 BEGIN
503 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
504 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'p_base_id:' || p_base_id);
505 END IF;
506
507 OPEN resource_cur;
508 FETCH resource_cur INTO resource_rec;
509 CLOSE resource_cur;
510
511 ln_coa := NVL (igf_aw_coa_gen.coa_amount (p_base_id), 0);
512 ln_efc := NVL (igf_aw_gen_004.efc_f (p_base_id), 0);
513 p_resource_f := resource_rec.resource_f;
514 p_resource_f_fc := resource_rec.resource_fm_f;
515 IF NVL (resource_rec.resource_fm_f, 0) > ln_efc THEN
516 p_resource_f_fc := ln_efc;
517 ELSE
518 p_resource_f_fc := resource_rec.resource_fm_f;
519 END IF;
520
521 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
522 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'ln_coa:' || ln_coa);
523 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'ln_efc:' || ln_efc);
524 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'p_resource_f:' || p_resource_f);
525 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'p_resource_f_fc:' || p_resource_f_fc);
526 END IF;
527
528 IF ln_coa > ln_efc THEN
529 p_unmet_need_f := ln_coa - ln_efc - NVL (p_resource_f, 0) + NVL (p_resource_f_fc, 0);
530 ELSE
531 p_unmet_need_f := ln_coa - p_resource_f_fc;
532 END IF;
533
534 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
535 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.unmetneed.debug', 'p_unmet_need_f:' || p_unmet_need_f);
536 END IF;
537
538 RETURN p_unmet_need_f;
539 END unmetneed;
540
541 PROCEDURE cl_alt_borr_cosig_validation (p_loan_number igf_sl_loans.loan_number%TYPE,
542 p_loan_catg VARCHAR2,
543 p_prc_type_code igf_sl_lor.prc_type_code%TYPE)
544 AS
545 /***************************************************************
546 Created By : bvisvana
547 Date Created By : 02-June-2005
548 Purpose : To Validate borrower and cosigner details in the @4 Record
549 in case of FFELP ALT Loans i.e CL_ALT
550 Known Limitations,Enhancements or Remarks
551 Change History :
552 Who When What
553 ***************************************************************/
554
555 -- Cursor to fetch the Borrower and Cosigner details for CL_ALT loans (@4 Record)
556 CURSOR c_alt_borr_details IS
557 SELECT
558 borw.borw_gross_annual_sal borw_gross_annual_sal,
559 borw.borw_other_income borw_other_income,
560 borw.cs1_gross_annual_sal_num csgnr1_gross_annual_sal,
561 borw.cs1_other_income_amt csgnr1_other_income,
562 borw.cs2_gross_annual_sal_num csgnr2_gross_annual_sal,
563 borw.cs2_other_income_amt csgnr2_other_income,
564 borw.student_major student_major,
565 lor.prc_type_code prc_type_code,
566 loans.loan_number loan_number
567 FROM
568 igf_sl_loans loans,
569 igf_sl_lor lor,
570 igf_sl_alt_borw borw
571 WHERE
572 loans.loan_number = p_loan_number AND
573 lor.prc_type_code = p_prc_type_code AND
574 loans.loan_id = lor.loan_id AND
575 lor.loan_id = borw.loan_id;
576 BEGIN
577 FOR borr_rec IN c_alt_borr_details LOOP
578 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'BORW_GROSS_ANNUAL_SAL',
579 borr_rec.borw_gross_annual_sal,borr_rec.prc_type_code));
580 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'BORW_OTHER_INCOME',
581 borr_rec.borw_other_income,borr_rec.prc_type_code));
582 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'CSGNR1_GROSS_ANNUAL_SAL',
583 borr_rec.csgnr1_gross_annual_sal,borr_rec.prc_type_code));
584 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'CSGNR1_OTHER_INCOME',
585 borr_rec.csgnr1_other_income,borr_rec.prc_type_code));
586 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'CSGNR2_GROSS_ANNUAL_SAL',
587 borr_rec.csgnr2_gross_annual_sal,borr_rec.prc_type_code));
588 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'CSGNR2_OTHER_INCOME',
589 borr_rec.csgnr2_other_income,borr_rec.prc_type_code));
590 set_complete_status(check_for_reqd(borr_rec.loan_number,p_loan_catg,'STUDENT_MAJOR',
591 borr_rec.student_major,borr_rec.prc_type_code));
592 END LOOP;
593 END cl_alt_borr_cosig_validation;
594
595 PROCEDURE loan_limit_validation (
596 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
597 p_fund_type IN igf_aw_fund_cat.fed_fund_code%TYPE,
598 p_award_id IN igf_aw_award_all.award_id%TYPE,
599 p_msg_name OUT NOCOPY fnd_new_messages.message_name%TYPE
600 )
601 IS
602 /***************************************************************
603 Created By : museshad
604 Date Created By : 05-Oct-2005
605 Purpose : Stafford loan limit validation
606 Known Limitations,Enhancements or Remarks
607 Change History :
608 Who When What
609 bvisvana 10-Apr-2006 Removed the check for defalut status of parent and student as part of Bug # 4127532
610 ***************************************************************/
611 l_aid NUMBER;
612
613 CURSOR c_get_dist_plan(cp_award_id igf_aw_award_all.award_id%TYPE)
614 IS
615 SELECT adplans_id
616 FROM igf_aw_award_all
617 WHERE award_id = cp_award_id;
618
619 l_dist_plan_rec c_get_dist_plan%ROWTYPE;
620
621 BEGIN
622
623 l_aid := 0;
624 p_msg_name := NULL;
625
626 OPEN c_get_dist_plan(p_award_id);
627 FETCH c_get_dist_plan INTO l_dist_plan_rec;
628 CLOSE c_get_dist_plan;
629
630 igf_aw_packng_subfns.check_loan_limits (
631 l_base_id => p_base_id,
632 fund_type => p_fund_type,
633 l_award_id => p_award_id,
634 l_adplans_id => l_dist_plan_rec.adplans_id,
635 l_aid => l_aid,
636 l_std_loan_tab => NULL,
637 p_msg_name => p_msg_name,
638 l_awd_period => NULL,
639 l_called_from => 'NON-PACKAGING'
640 );
641 END loan_limit_validation;
642
643 -- Start of Main Procedure
644
645 BEGIN
646
647 g_debug_runtime_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
648
649 IF p_call_mode = 'FORM' THEN
650 p_status_1 := 'G';
651 p_status_2 := 'N';
652 p_status_3 := 'R';
653 END IF;
654
655 IF p_call_mode ='JOB' THEN
656 p_status_1 := 'G';
657 p_status_2 := 'G';
658 p_status_3 := 'G';
659 END IF;
660
661 IF p_loan_catg = 'CL_STAFFORD' THEN
662 p_fed_fund_1 := 'FLS';
663 p_fed_fund_2 := 'FLU';
664 END IF;
665
666 IF p_loan_catg = 'CL_PLUS' THEN
667 p_fed_fund_1 := 'FLP';
668 p_fed_fund_2 := 'FLP';
669 END IF;
670
671 IF p_loan_catg = 'CL_ALT' THEN
672 p_fed_fund_1 := 'ALT';
673 p_fed_fund_2 := 'ALT';
674 END IF;
675
676 IF p_loan_catg = 'CL_GPLUSFL' THEN
677 p_fed_fund_1 := 'GPLUSFL';
678 p_fed_fund_2 := 'GPLUSFL';
679 END IF;
680
681 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
682 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_call_mode:' || p_call_mode);
683 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_status_1:' || p_status_1);
684 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_status_2:' || p_status_2);
685 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_status_3:' || p_status_3);
686 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_loan_catg:' || p_loan_catg);
687 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_fed_fund_1:' || p_fed_fund_1);
688 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_fed_fund_2:' || p_fed_fund_2);
689 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_ci_cal_type:' || p_ci_cal_type);
690 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_ci_seq_number:' || p_ci_sequence_number);
691 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_loan_number:' || p_loan_number);
692 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_school_id :' || p_school_id );
693 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'p_base_id :' || p_base_id );
694 END IF;
695
696
697 FOR lor_rec IN cur_lor_details_recs(p_fed_fund_1,
698 p_fed_fund_2,
699 p_status_1,
700 p_status_2,
701 p_status_3,
702 p_ci_cal_type,
703 p_ci_sequence_number,
704 'Y',
705 p_school_id,
706 p_base_id
707 ) LOOP
708
709
710 --Need not perform the validations if the Loan ID is same.
711 --Bug:-2415041 Loan Orig with incorrect error messages.
712 IF NVL(g_loan_id,0) <> lor_rec.loan_id OR p_call_mode = 'FORM' THEN
713
714 -- Initialize the Value of Completeness before checking
715
716 lv_complete := TRUE;
717
718 --No data found exception should be explicitly handled
719 --this was noticed when process was run without award year setup.
720 BEGIN
721 -- Get CommonLine Version for this Award Year.
722 -- museshad(Bug# 4346258) - Added the parameter p_base_id due to change in the
723 -- signature of the function 'get_cl_version()'
724 p_cl_version := igf_sl_gen.get_cl_version(p_ci_cal_type, p_ci_sequence_number,lor_rec.relationship_cd,p_base_id);
725
726 EXCEPTION
727 WHEN NO_DATA_FOUND THEN
728 fnd_message.set_name('IGF','IGF_SL_NO_CL_SETUP');
729 fnd_file.put_line(fnd_file.log,fnd_message.get);
730 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
731 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.set_complete_status.debug', 'No CL Setup found');
732 END IF;
733 RAISE NO_DATA_FOUND;
734 END;
735
736
737 -- Assign Current Record to Temp.Rec
738
739 lor_rec_temp := lor_rec;
740
741 -- Fetch License State and Num into Variables
742 OPEN cur_isir_depend_status;
743 FETCH cur_isir_depend_status INTO lv_dependency_status;
744 IF cur_isir_depend_status%NOTFOUND THEN
745 CLOSE cur_isir_depend_status;
746 fnd_message.set_name('IGF','IGF_GE_REC_NO_DATA_FOUND');
747 fnd_message.set_token('P_RECORD','IGF_AP_FA_BASE_REC');
748 igs_ge_msg_stack.add;
749 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
750 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate', 'cur_isir_depend_status cursor failed');
751 END IF;
752 app_exception.raise_exception;
753 END IF;
754 CLOSE cur_isir_depend_status;
755
756
757 -- Deletes the Record from the Edit Table with this Loan Number and Status as Valid
758
759 igf_sl_edit.delete_edit(lor_rec.loan_number,'V');
760
761 -- For School Certification Requests, use External Loan Number field. If the External
762 -- Loan Number is not available, error out and do not process the loan record
763 IF lor_rec.prc_type_code = 'CR' THEN
764 IF lor_rec.external_loan_id_txt IS NULL THEN
765 set_complete_status(FALSE);
766 igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','EXT_LOAN_NUM_CHK',NULL,NULL);
767 END IF;
768 END IF;
769
770 --Irrespective of Loan Type School Certification date cannot be after the Loan Period End Date
771 --and the Processing Type should be GP for this validation
772 --Bug 2477912 CL Formatting Errors.
773 IF lor_rec.sch_cert_date IS NOT NULL AND lor_rec.prc_type_code IN ('GP','GO') THEN
774 IF lor_rec.sch_cert_date > lor_rec.loan_per_end_date THEN
775 set_complete_status(FALSE);
776 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '078', 'SCH_CERT_DATE',
777 lor_rec.sch_cert_date);
778 END IF;
779
780 END IF;
781
782 --If the validation process is called from JOB then check if the Sysdate >Loan Per End Date.
783 --If so then we should make the Loan Status as NOT READY.Otherwise we can originate.
784 --Loan Origination Date cannot be after Loan Period End Date.
785 --2477912 Added error code 457 for File Transfer Date
786 --4089250 Error code 046 should be used. Not 457 -ugummall
787 IF p_call_mode ='JOB' AND lor_rec.prc_type_code IN ('GO','GP') THEN
788 IF TRUNC(SYSDATE) > lor_rec.loan_per_end_date THEN
789 set_complete_status(FALSE);
790 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '046', 'FILE_TRANS_DATE', NULL);
791 END IF;
792
793 END IF;
794
795 --Irrespective of Loan Type Anticipated Completion date cannot be before the Loan Period End Date
796 --and the Processing Type should be GP for this validation
797 --Bug 2477912 CL Formatting Errors.
798
799 IF lor_rec.anticip_compl_date IS NOT NULL AND lor_rec.prc_type_code IN ('GP','GO') THEN
800 IF lor_rec.anticip_compl_date < lor_rec.loan_per_end_date THEN
801 set_complete_status(FALSE);
802 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '053', 'ANTICIP_COMPL_DATE',
803 lor_rec.anticip_compl_date);
804 END IF;
805
806 END IF;
807
808 igf_sl_gen.get_person_details(lor_rec.student_id,student_dtl_cur);
809 FETCH student_dtl_cur INTO student_dtl_rec;
810 igf_sl_gen.get_person_details(lor_rec.p_person_id,parent_dtl_cur);
811 FETCH parent_dtl_cur INTO parent_dtl_rec;
812
813 --FA 161 - Due to the introduction of a new mapping form, we don't use the lookup tag but rather require the lookup_code itself to
814 --determine the mapping values...and hence the below blocks of citizenship_dtl_cur
815 OPEN citizenship_dtl_cur(lor_rec.student_id);
816 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
817 student_dtl_rec.p_citizenship_status := citizenship_dtl_rec.restatus_code;
818 CLOSE citizenship_dtl_cur;
819 citizenship_dtl_rec := NULL;
820 OPEN citizenship_dtl_cur(lor_rec.p_person_id);
821 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
822 parent_dtl_rec.p_citizenship_status := citizenship_dtl_rec.restatus_code;
823 CLOSE citizenship_dtl_cur;
824
825 --Code added for bug 3603289 start
826 lv_s_license_number := student_dtl_rec.p_license_num;
827 lv_s_license_state := student_dtl_rec.p_license_state;
828 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
829 log_to_fnd('cl_lar_validate','debug','P_CITIZENSHIP_STATUS = '||parent_dtl_rec.p_citizenship_status);
830 log_to_fnd('cl_lar_validate','debug','S_CITIZENSHIP_STATUS = '||student_dtl_rec.p_citizenship_status);
831 --Code added for bug 3603289 end
832
833 -- For each of the LOR records check if the Required Fields are not null
834
835 IF p_loan_catg = 'CL_STAFFORD' THEN
836 l_phone := igf_sl_gen.get_person_phone( lor_rec.student_id );
837
838 -- Following are the checks for Common Line Loan Stafford.
839
840 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_SSN',
841 student_dtl_rec.p_ssn,lor_rec.prc_type_code));
842 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_FIRST_NAME',
843 student_dtl_rec.p_first_name,lor_rec.prc_type_code));
844 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_PERMT_ADDR1',
845 student_dtl_rec.p_permt_addr1,lor_rec.prc_type_code));
846 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_PERMT_CITY',
847 student_dtl_rec.p_permt_city,lor_rec.prc_type_code));
848 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_PERMT_STATE',
849 student_dtl_rec.p_permt_state,lor_rec.prc_type_code));
850 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_PERMT_ZIP',
851 student_dtl_rec.p_permt_zip,lor_rec.prc_type_code));
852 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'BORW_LENDER_ID',
853 lor_rec.borw_lender_id,lor_rec.prc_type_code));
854 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_DATE_OF_BIRTH',
855 TO_CHAR(student_dtl_rec.p_date_of_birth),lor_rec.prc_type_code));
856 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'CL_LOAN_TYPE',
857 lor_rec.cl_loan_type,lor_rec.prc_type_code));
858 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_AMT_ACCEPTED',
859 TO_CHAR(lor_rec.loan_amt_accepted),lor_rec.prc_type_code));
860 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'BORW_INTEREST_IND',
861 lor_rec.borw_interest_ind,lor_rec.prc_type_code));
862 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'EFT_AUTHORIZATION',
863 lor_rec.eft_authorization_code,lor_rec.prc_type_code));
864 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_NUMBER',
865 lor_rec.loan_number,lor_rec.prc_type_code));
866 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'SCHOOL_ID',
867 p_school_id,lor_rec.prc_type_code));
868 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_BEGIN_DATE',
869 TO_CHAR(lor_rec.loan_per_begin_date),lor_rec.prc_type_code));
870 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_END_DATE',
871 TO_CHAR(lor_rec.loan_per_end_date),lor_rec.prc_type_code));
872 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GRADE_LEVEL_CODE',
873 lor_rec.grade_level_code,lor_rec.prc_type_code));
874 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ENROLLMENT_CODE',
875 lor_rec.enrollment_code,lor_rec.prc_type_code));
876 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ANTICIP_COMPL_DATE',
877 lor_rec.anticip_compl_date,lor_rec.prc_type_code));
878 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_AMT_ACCEPTED',
879 TO_CHAR(lor_rec.loan_amt_accepted),lor_rec.prc_type_code));
880 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LENDER_ID',
881 lor_rec.lender_id,lor_rec.prc_type_code));
882 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GUARANTOR_ID',
883 lor_rec.guarantor_id,lor_rec.prc_type_code));
884 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LICENSE_STATE',
885 lv_s_license_state,lor_rec.prc_type_code));
886 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LICENSE_NUM',
887 lv_s_license_number,lor_rec.prc_type_code));
888 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'REQ_SERIAL_LOAN_CODE',
889 lor_rec.req_serial_loan_code,lor_rec.prc_type_code));
890 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LEND_NON_ED_BRC_ID',
891 lor_rec.lend_non_ed_brc_id,lor_rec.prc_type_code));
892 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PRC_TYPE_CODE',
893 lor_rec.prc_type_code,lor_rec.prc_type_code));
894 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PNOTE_DELIVERY_CODE',
895 lor_rec.pnote_delivery_Code,lor_rec.prc_type_code));
896
897 -- rajagupt - FA 161 - Bug # 5006583
898 -- Check for Valid Federal application form code i.e Stafford = 'M'
899 IF NVL(lor_rec.loan_app_form_code,'*') <> 'M' THEN
900 set_complete_status(FALSE);
901 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
902 END IF;
903 -- Deferement Request not required for 'M' appl form code
904 IF NVL(lor_rec.loan_app_form_code,'*') = 'M' AND lor_rec.deferment_request_code IS NOT NULL THEN
905 set_complete_status(FALSE);
906 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'DEFER_REQ_CODE', NULL);
907 g_update_mode_required := TRUE;
908 END IF;
909
910 --Added this Code as CL Ref doc this is Strongly Recommended for all types of Loans.Bug 2400487
911 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_SIGNATURE_CODE',
912 lor_rec.s_signature_code,lor_rec.prc_type_code));
913
914 -- For CL Stafford If the Student is in Default, then do not originate
915 IF lor_rec.s_default_status = 'Y' AND (lor_rec.prc_type_code IN ('GP','GO')) THEN
916 set_complete_status(FALSE);
917 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'IS_DEFAULTER', 'S_DEFAULT_STATUS', NULL);
918 END IF;
919
920 --
921 -- Check for Student SSN
922 --
923 IF (lor_rec.prc_type_code IN ('GP','GO')) THEN
924 IF SUBSTR(student_dtl_rec.p_ssn,1,1) = '8' OR
925 SUBSTR(student_dtl_rec.p_ssn,1,1) = '9' OR
926 SUBSTR(student_dtl_rec.p_ssn,1,3) = '000'
927 THEN
928 set_complete_status(FALSE);
929 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
930 END IF;
931 END IF;
932
933
934 ELSIF p_loan_catg IN ('CL_PLUS','CL_GPLUSFL') THEN
935 l_phone := igf_sl_gen.get_person_phone( lor_rec.p_person_id );
936 -- Following are the checks for Common Line Loan PLUS and Grad Plus Loan.
937
938 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_SSN',
939 parent_dtl_rec.p_ssn,lor_rec.prc_type_code));
940 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_FIRST_NAME',
941 parent_dtl_rec.p_first_name,lor_rec.prc_type_code));
942 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LAST_NAME',
943 parent_dtl_rec.p_last_name,lor_rec.prc_type_code));
944 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_FIRST_NAME',
945 student_dtl_rec.p_first_name,lor_rec.prc_type_code));
946 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LAST_NAME',
947 student_dtl_rec.p_last_name,lor_rec.prc_type_code));
948 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_SSN',
949 student_dtl_rec.p_ssn,lor_rec.prc_type_code));
950 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_DATE_OF_BIRTH',
951 student_dtl_rec.p_date_of_birth,lor_rec.prc_type_code));
952 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_CITIZENSHIP_STATUS',
953 lv_s_citizenship_status,lor_rec.prc_type_code));
954 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_ADDR1',
955 parent_dtl_rec.p_permt_addr1,lor_rec.prc_type_code));
956 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_CITY',
957 parent_dtl_rec.p_permt_city,lor_rec.prc_type_code));
958 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_STATE',
959 parent_dtl_rec.p_permt_state,lor_rec.prc_type_code));
960 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_ZIP',
961 parent_dtl_rec.p_permt_zip,lor_rec.prc_type_code));
962 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'BORW_LENDER_ID',
963 lor_rec.borw_lender_id,lor_rec.prc_type_code));
964 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_DATE_OF_BIRTH',
965 TO_CHAR(parent_dtl_rec.p_date_of_birth),lor_rec.prc_type_code));
966 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'CL_LOAN_TYPE',
967 lor_rec.cl_loan_type,lor_rec.prc_type_code));
968 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'EFT_AUTHORIZATION',
969 lor_rec.eft_authorization_code,lor_rec.prc_type_code));
970 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_SIGNATURE_CODE',
971 lor_rec.p_signature_code,lor_rec.prc_type_code));
972 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_NUMBER',
973 lor_rec.loan_number,lor_rec.prc_type_code));
974 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_CITIZENSHIP_STATUS',
975 parent_dtl_rec.p_citizenship_status,lor_rec.prc_type_code));
976 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_BEGIN_DATE',
977 TO_CHAR(lor_rec.loan_per_begin_date),lor_rec.prc_type_code));
978 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_END_DATE',
979 TO_CHAR(lor_rec.loan_per_end_date),lor_rec.prc_type_code));
980 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GRADE_LEVEL_CODE',
981 lor_rec.grade_level_code,lor_rec.prc_type_code));
982 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ENROLLMENT_CODE',
983 lor_rec.enrollment_code,lor_rec.prc_type_code));
984 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ANTICIP_COMPL_DATE',
985 TO_CHAR(lor_rec.anticip_compl_date),lor_rec.prc_type_code));
986 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_AMT_ACCEPTED',
987 TO_CHAR(lor_rec.loan_amt_accepted),lor_rec.prc_type_code));
988 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LENDER_ID',
989 lor_rec.lender_id,lor_rec.prc_type_code));
990 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GUARANTOR_ID',
991 lor_rec.guarantor_id,lor_rec.prc_type_code));
992 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LICENSE_STATE',
993 parent_dtl_rec.p_license_state,lor_rec.prc_type_code));
994 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LICENSE_NUM',
995 parent_dtl_rec.p_license_num,lor_rec.prc_type_code));
996 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LEND_NON_ED_BRC_ID',
997 lor_rec.lend_non_ed_brc_id,lor_rec.prc_type_code));
998 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PRC_TYPE_CODE',
999 lor_rec.prc_type_code,lor_rec.prc_type_code));
1000 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PNOTE_DELIVERY_CODE',
1001 lor_rec.pnote_delivery_Code,lor_rec.prc_type_code));
1002 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'REQ_SERIAL_LOAN_CODE',
1003 lor_rec.req_serial_loan_code,lor_rec.prc_type_code));
1004
1005 -- For CL PLUS If the Student or Parent is in Default then do not originate
1006 -- Check for Valid Federal application form code i.e PLUS = 'Q'
1007 IF(NVL(lor_rec.loan_app_form_code,'*') <> 'Q'AND p_loan_catg IN ('CL_PLUS')) OR
1008 (NVL(lor_rec.loan_app_form_code,'*') <> 'G' AND p_loan_catg IN ('CL_GPLUSFL')) THEN
1009 set_complete_status(FALSE);
1010 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
1011 END IF;
1012 -- Not required fields
1013 IF NVL(lor_rec.loan_app_form_code,'*') = 'Q' OR NVL(lor_rec.loan_app_form_code,'*') = 'G' THEN
1014 IF lor_rec.deferment_request_code IS NOT NULL THEN
1015 set_complete_status(FALSE);
1016 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'DEFER_REQ_CODE', NULL);
1017 g_update_mode_required := TRUE;
1018 END IF;
1019 IF lor_rec.borw_interest_ind IS NOT NULL AND (lor_rec.borw_interest_ind = 'Y' OR lor_rec.borw_interest_ind = 'YES') THEN
1020 set_complete_status(FALSE);
1021 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'BORW_INTEREST_IND', NULL);
1022 g_update_mode_required := TRUE;
1023 END IF;
1024 IF lor_rec.p_default_status IS NOT NULL THEN
1025 set_complete_status(FALSE);
1026 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'P_DEFAULT_STATUS', NULL);
1027 g_update_mode_required := TRUE;
1028 END IF;
1029 IF lor_rec.s_default_status IS NOT NULL THEN
1030 set_complete_status(FALSE);
1031 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_DEFAULT_STATUS', NULL);
1032 g_update_mode_required := TRUE;
1033 END IF;
1034 IF lor_rec.borw_outstd_loan_code IS NOT NULL THEN
1035 set_complete_status(FALSE);
1036 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'BORW_OUTSTD_LOAN_CODE', NULL);
1037 g_update_mode_required := TRUE;
1038 END IF;
1039 IF lor_rec.s_signature_code IS NOT NULL THEN
1040 set_complete_status(FALSE);
1041 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_SIGNATURE_CODE', NULL);
1042 g_update_mode_required := TRUE;
1043 END IF;
1044 IF lor_rec.stud_sign_ind IS NOT NULL AND (lor_rec.stud_sign_ind = 'Y' OR lor_rec.stud_sign_ind = 'YES') THEN
1045 set_complete_status(FALSE);
1046 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '502', 'S_ESIGN_IND_CODE', NULL);
1047 g_update_mode_required := TRUE;
1048 END IF;
1049 END IF;
1050 -- FA 161 - Bug 5006583 - Citizenship mapping code does not exists
1051 -- Student
1052 IF lv_s_citizenship_status IS NOT NULL THEN
1053 OPEN cur_fa_mapping( cp_citizenship_status => lv_s_citizenship_status,
1054 cp_cal_type => p_ci_cal_type,
1055 cp_sequence_number => p_ci_sequence_number);
1056 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1057 IF cur_fa_mapping%NOTFOUND THEN
1058 log_to_fnd('cl_lar_validate','debug','----S_CITIZENSHIP_STATUS----');
1059 set_complete_status(FALSE);
1060 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'S_CITIZENSHIP_STATUS', NULL);
1061 END IF;
1062 CLOSE cur_fa_mapping;
1063 END IF;
1064 -- Parent
1065 IF parent_dtl_rec.p_citizenship_status IS NOT NULL THEN
1066 OPEN cur_fa_mapping( cp_citizenship_status => parent_dtl_rec.p_citizenship_status,
1067 cp_cal_type => p_ci_cal_type,
1068 cp_sequence_number => p_ci_sequence_number);
1069 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1070 log_to_fnd('cl_lar_validate','debug','cur_fa_mapping_rec.fa_citi_stat_code ='||cur_fa_mapping_rec.fa_citi_stat_code||'=');
1071 log_to_fnd('cl_lar_validate','debug','lor_rec.b_alien_reg_num_txt ='||lor_rec.b_alien_reg_num_txt||'=');
1072 IF cur_fa_mapping%NOTFOUND THEN
1073 set_complete_status(FALSE);
1074 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'P_CITIZENSHIP_STATUS', NULL);
1075 -- Borw Alien registration Number is Strongly Recommended if FA Citizenship code maps to 2
1076 ELSIF (cur_fa_mapping_rec.fa_citi_stat_code = '2' AND lor_rec.b_alien_reg_num_txt IS NULL) THEN
1077 log_to_fnd('cl_lar_validate','debug','p_loan_catg ' || p_loan_catg);
1078 log_to_fnd('cl_lar_validate','debug','lor_rec.b_alien_reg_num_txt '||lor_rec.b_alien_reg_num_txt);
1079 log_to_fnd('cl_lar_validate','debug','lor_rec.prc_type_code '||lor_rec.prc_type_code);
1080
1081 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'B_ALIEN_REG_NUM',
1082 lor_rec.b_alien_reg_num_txt,lor_rec.prc_type_code));
1083 END IF;
1084 CLOSE cur_fa_mapping;
1085
1086 END IF;
1087
1088 --
1089 -- Check for Student and Borrower SSN
1090 --
1091 IF (lor_rec.prc_type_code IN ('GP','GO')) THEN
1092 IF SUBSTR(student_dtl_rec.p_ssn,1,1) = '8' OR
1093 SUBSTR(student_dtl_rec.p_ssn,1,1) = '9' OR
1094 SUBSTR(student_dtl_rec.p_ssn,1,3) = '000'
1095 THEN
1096 set_complete_status(FALSE);
1097 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
1098 END IF;
1099
1100 IF SUBSTR(parent_dtl_rec.p_ssn,1,1) = '8' OR
1101 SUBSTR(parent_dtl_rec.p_ssn,1,1) = '9' OR
1102 SUBSTR(parent_dtl_rec.p_ssn,1,3) = '000'
1103 THEN
1104 set_complete_status(FALSE);
1105 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '001', 'P_SSN', NULL);
1106 END IF;
1107 END IF;
1108 ELSIF p_loan_catg = 'CL_ALT' THEN
1109 l_phone := igf_sl_gen.get_person_phone( lor_rec.p_person_id );
1110
1111 -- Following are the checks for Common Line Loan ALTERNATE.
1112 log_to_fnd('cl_lar_validate','debug','----P_LAST_NAME----');
1113 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LAST_NAME',
1114 parent_dtl_rec.p_last_name,lor_rec.prc_type_code));
1115 log_to_fnd('cl_lar_validate','debug','----P_FIRST_NAME----');
1116 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_FIRST_NAME',
1117 parent_dtl_rec.p_first_name,lor_rec.prc_type_code));
1118 log_to_fnd('cl_lar_validate','debug','----P_SSN----');
1119 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_SSN',
1120 parent_dtl_rec.p_ssn,lor_rec.prc_type_code));
1121 log_to_fnd('cl_lar_validate','debug','----P_PERMT_ADDR1----');
1122 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_ADDR1',
1123 parent_dtl_rec.p_permt_addr1,lor_rec.prc_type_code));
1124 log_to_fnd('cl_lar_validate','debug','----P_PERMT_CITY----');
1125 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_CITY',
1126 parent_dtl_rec.p_permt_city,lor_rec.prc_type_code));
1127 log_to_fnd('cl_lar_validate','debug','----P_PERMT_STATE----');
1128 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_STATE',
1129 parent_dtl_rec.p_permt_state,lor_rec.prc_type_code));
1130 log_to_fnd('cl_lar_validate','debug','----P_PERMT_ZIP----');
1131 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_PERMT_ZIP',
1132 parent_dtl_rec.p_permt_zip,lor_rec.prc_type_code));
1133 log_to_fnd('cl_lar_validate','debug','----BORW_LENDER_ID----');
1134 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'BORW_LENDER_ID',
1135 lor_rec.borw_lender_id,lor_rec.prc_type_code));
1136 log_to_fnd('cl_lar_validate','debug','----P_DATE_OF_BIRTH----');
1137 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_DATE_OF_BIRTH',
1138 TO_CHAR(parent_dtl_rec.p_date_of_birth),lor_rec.prc_type_code));
1139 log_to_fnd('cl_lar_validate','debug','----CL_LOAN_TYPE----');
1140 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'CL_LOAN_TYPE',
1141 lor_rec.cl_loan_type,lor_rec.prc_type_code));
1142 log_to_fnd('cl_lar_validate','debug','----P_SIGNATURE_CODE----');
1143 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_SIGNATURE_CODE',
1144 lor_rec.p_signature_code,lor_rec.prc_type_code));
1145 log_to_fnd('cl_lar_validate','debug','----LOAN_NUMBER----');
1146 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_NUMBER',
1147 lor_rec.loan_number,lor_rec.prc_type_code));
1148 log_to_fnd('cl_lar_validate','debug','----BORW_OUTSTD_LOAN_CODE----');
1149 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'BORW_OUTSTD_LOAN_CODE',
1150 lor_rec.borw_outstd_loan_code,lor_rec.prc_type_code));
1151 log_to_fnd('cl_lar_validate','debug','----S_LAST_NAME----');
1152 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LAST_NAME',
1153 student_dtl_rec.p_last_name,lor_rec.prc_type_code));
1154 log_to_fnd('cl_lar_validate','debug','----S_FIRST_NAME----');
1155 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_FIRST_NAME',
1156 student_dtl_rec.p_first_name,lor_rec.prc_type_code));
1157 log_to_fnd('cl_lar_validate','debug','----S_SSN----');
1158 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_SSN',
1159 student_dtl_rec.p_ssn,lor_rec.prc_type_code));
1160 log_to_fnd('cl_lar_validate','debug','----S_DATE_OF_BIRTH----');
1161 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_DATE_OF_BIRTH',
1162 student_dtl_rec.p_date_of_birth,lor_rec.prc_type_code));
1163 log_to_fnd('cl_lar_validate','debug','----S_DEFAULT_STATUS----');
1164 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_DEFAULT_STATUS',
1165 lor_rec.s_default_status,lor_rec.prc_type_code));
1166 log_to_fnd('cl_lar_validate','debug','----SCHOOL_ID----');
1167 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'SCHOOL_ID',
1168 p_school_id,lor_rec.prc_type_code));
1169 log_to_fnd('cl_lar_validate','debug','----LOAN_PER_BEGIN_DATE----');
1170 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_BEGIN_DATE',
1171 TO_CHAR(lor_rec.loan_per_begin_date),lor_rec.prc_type_code));
1172 log_to_fnd('cl_lar_validate','debug','----LOAN_PER_END_DATE----');
1173 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LOAN_PER_END_DATE',
1174 TO_CHAR(lor_rec.loan_per_end_date),lor_rec.prc_type_code));
1175 log_to_fnd('cl_lar_validate','debug','----GRADE_LEVEL_CODE----');
1176 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GRADE_LEVEL_CODE',
1177 lor_rec.grade_level_code,lor_rec.prc_type_code));
1178 log_to_fnd('cl_lar_validate','debug','----ENROLLMENT_CODE----');
1179 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ENROLLMENT_CODE',
1180 lor_rec.enrollment_code,lor_rec.prc_type_code));
1181 log_to_fnd('cl_lar_validate','debug','----ANTICIP_COMPL_DATE----');
1182 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ANTICIP_COMPL_DATE',
1183 TO_CHAR(lor_rec.anticip_compl_date),lor_rec.prc_type_code));
1184 log_to_fnd('cl_lar_validate','debug','----LENDER_ID----');
1185 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LENDER_ID',
1186 lor_rec.lender_id,lor_rec.prc_type_code));
1187 log_to_fnd('cl_lar_validate','debug','----GUARANTOR_ID----');
1188 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'GUARANTOR_ID',
1189 lor_rec.guarantor_id,lor_rec.prc_type_code));
1190 log_to_fnd('cl_lar_validate','debug','----LEND_NON_ED_BRC_ID----');
1191 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'LEND_NON_ED_BRC_ID',
1192 lor_rec.lend_non_ed_brc_id,lor_rec.prc_type_code));
1193 log_to_fnd('cl_lar_validate','debug','----PRC_TYPE_CODE----');
1194 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PRC_TYPE_CODE',
1195 lor_rec.prc_type_code,lor_rec.prc_type_code));
1196
1197 log_to_fnd('cl_lar_validate','debug','----PNOTE_DELIVERY_CODE----');
1198 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'PNOTE_DELIVERY_CODE',
1199 lor_rec.pnote_delivery_Code,lor_rec.prc_type_code));
1200
1201 --Added this Code as CL Ref doc this is Strongly Recommended for all types of Loans.Bug 2400487
1202 log_to_fnd('cl_lar_validate','debug','----S_SIGNATURE_CODE----');
1203 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_SIGNATURE_CODE',
1204 lor_rec.s_signature_code,lor_rec.prc_type_code));
1205 --FACR116
1206 log_to_fnd('cl_lar_validate','debug','----ALT_LOAN_CODE----');
1207 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'ALT_LOAN_CODE',
1208 lor_rec.alt_loan_code,lor_rec.prc_type_code));
1209
1210 /* Check if the student is the borrower */
1211 IF ( lor_rec.p_person_id = lor_rec.student_id ) THEN
1212 log_to_fnd('cl_lar_validate','debug','----S_LICENSE_STATE----');
1213 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LICENSE_STATE',
1214 lv_s_license_state,lor_rec.prc_type_code));
1215 log_to_fnd('cl_lar_validate','debug','----S_LICENSE_NUM----');
1216 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_LICENSE_NUM',
1217 lv_s_license_number,lor_rec.prc_type_code));
1218 log_to_fnd('cl_lar_validate','debug','----S_CITIZENSHIP_STATUS----');
1219 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'S_CITIZENSHIP_STATUS',
1220 lv_s_citizenship_status,lor_rec.prc_type_code));
1221
1222
1223 -- FA 161 - Bug 5006583 - Citizenship mapping code does not exists
1224 IF lv_s_citizenship_status IS NOT NULL THEN
1225 OPEN cur_fa_mapping( cp_citizenship_status => lv_s_citizenship_status,
1226 cp_cal_type => p_ci_cal_type,
1227 cp_sequence_number => p_ci_sequence_number);
1228 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1229 IF cur_fa_mapping%NOTFOUND THEN
1230 set_complete_status(FALSE);
1231 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'S_CITIZENSHIP_STATUS', NULL);
1232 ELSIF NOT (cur_fa_mapping_rec.fa_citi_stat_code IN ('1','2') AND (lor_rec.prc_type_code IN ('GP','GO'))) THEN
1233 set_complete_status(FALSE);
1234 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '016', 'P_CITIZENSHIP_STATUS', NULL);
1235
1236 log_to_fnd('cl_lar_validate','debug','--------------------');
1237 log_to_fnd('cl_lar_validate','debug','P_CITIZENSHIP_STATUS');
1238 ELSIF cur_fa_mapping_rec.fa_citi_stat_code = '2' AND lor_rec.b_alien_reg_num_txt IS NULL THEN
1239 log_to_fnd('cl_lar_validate','debug','----B_ALIEN_REG_NUM----');
1240 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'B_ALIEN_REG_NUM',
1241 lor_rec.b_alien_reg_num_txt,lor_rec.prc_type_code));
1242 END IF;
1243 CLOSE cur_fa_mapping;
1244 END IF;
1245
1246 ELSE
1247 log_to_fnd('cl_lar_validate','debug','----P_CITIZENSHIP_STATUS----');
1248 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_CITIZENSHIP_STATUS',
1249 parent_dtl_rec.p_citizenship_status,lor_rec.prc_type_code));
1250 IF parent_dtl_rec.p_citizenship_status IS NOT NULL THEN
1251 OPEN cur_fa_mapping( cp_citizenship_status => parent_dtl_rec.p_citizenship_status,
1252 cp_cal_type => p_ci_cal_type,
1253 cp_sequence_number => p_ci_sequence_number);
1254 FETCH cur_fa_mapping INTO cur_fa_mapping_rec;
1255 IF cur_fa_mapping%NOTFOUND THEN
1256 log_to_fnd('cl_lar_validate','debug','----P_CITIZENSHIP_STATUS----');
1257 set_complete_status(FALSE);
1258 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'NO_FA_CITI_MAP_CD', 'P_CITIZENSHIP_STATUS', NULL);
1259 ELSIF NOT (cur_fa_mapping_rec.fa_citi_stat_code IN ('1','2') AND (lor_rec.prc_type_code IN ('GP','GO'))) THEN
1260 set_complete_status(FALSE);
1261 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '016', 'P_CITIZENSHIP_STATUS', NULL);
1262
1263 log_to_fnd('cl_lar_validate','debug','--------------------');
1264 log_to_fnd('cl_lar_validate','debug','P_CITIZENSHIP_STATUS');
1265
1266 ELSIF cur_fa_mapping_rec.fa_citi_stat_code = '2' AND lor_rec.b_alien_reg_num_txt IS NULL THEN
1267 log_to_fnd('cl_lar_validate','debug','----B_ALIEN_REG_NUM----');
1268 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'B_ALIEN_REG_NUM',
1269 lor_rec.b_alien_reg_num_txt,lor_rec.prc_type_code));
1270 END IF;
1271 CLOSE cur_fa_mapping;
1272 END IF;
1273
1274 log_to_fnd('cl_lar_validate','debug','----P_LICENSE_STATE----');
1275 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LICENSE_STATE',
1276 parent_dtl_rec.p_license_state,lor_rec.prc_type_code));
1277 log_to_fnd('cl_lar_validate','debug','----P_LICENSE_NUM----');
1278 set_complete_status(check_for_reqd(lor_rec.loan_number,p_loan_catg,'P_LICENSE_NUM',
1279 parent_dtl_rec.p_license_num,lor_rec.prc_type_code));
1280 END IF;
1281
1282 -- rajagupt - FA 161 - Bug # 5006583 -- Check for Valid Federal application form code i.e for ALT = null
1283 IF lor_rec.loan_app_form_code IS NOT NULL THEN
1284 set_complete_status(FALSE);
1285 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '405', 'FED_APPL_FORM_CODE', NULL);
1286 END IF;
1287
1288 --FA 157 - FFELP ALT Loan Validation - Includes Borrower and Cosigner details
1289 cl_alt_borr_cosig_validation(lor_rec.loan_number,p_loan_catg,lor_rec.prc_type_code);
1290
1291
1292 -- Commented for Bug 2477912 CL Formatting Errors
1293
1294 -- For CL ALT If the Student is not the borrower then if either the student or the borrower are in default - do not originate
1295 -- If the student is the borrower then if the student is in Default then do not originate
1296
1297 --
1298 -- Check for Student SSN
1299 --
1300 IF (lor_rec.prc_type_code IN ('GP','GO')) THEN
1301 IF SUBSTR(student_dtl_rec.p_ssn,1,1) = '8' OR
1302 SUBSTR(student_dtl_rec.p_ssn,1,1) = '9' OR
1303 SUBSTR(student_dtl_rec.p_ssn,1,3) = '000'
1304 THEN
1305 set_complete_status(FALSE);
1306 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '034', 'S_SSN', NULL);
1307 log_to_fnd('cl_lar_validate','debug','----S_SSN----');
1308 END IF;
1309 END IF;
1310 /* If student is not the borrower */
1311
1312 IF ( lor_rec.p_person_id <> lor_rec.student_id ) THEN
1313 -- Removed the check for default status of parent as a part of Bug # 4127532
1314
1315 -- Check for Borrower SSN
1316 --
1317 IF (lor_rec.prc_type_code IN ('GP','GO')) THEN
1318 IF SUBSTR(parent_dtl_rec.p_ssn,1,1) = '8' OR
1319 SUBSTR(parent_dtl_rec.p_ssn,1,1) = '9' OR
1320 SUBSTR(parent_dtl_rec.p_ssn,1,3) = '000'
1321 THEN
1322 set_complete_status(FALSE);
1323 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '001', 'P_SSN', NULL);
1324 log_to_fnd('cl_lar_validate','debug','----P_SSN----');
1325 END IF;
1326 END IF;
1327 END IF;
1328
1329 -- Removed the check for default status of student as a part of Bug # 4127532
1330 END IF; -- End of condition for p_loan_catg
1331
1332 -- For Release 4 processing, only 4 disbursements are allowed. So do not originate a loan in case
1333 -- there are more than 4 disbursements.
1334 IF p_cl_version = 'RELEASE-4' THEN
1335 log_to_fnd('cl_lar_validate','debug','Verifying the no. of disbursements');
1336 OPEN c_nof_awd_disb ( cp_n_award_id => lor_rec.award_id);
1337 FETCH c_nof_awd_disb INTO l_n_disb_cnt;
1338 IF c_nof_awd_disb%FOUND THEN
1339 log_to_fnd('cl_lar_validate','debug','The no. of disbursements='||l_n_disb_cnt);
1340 set_complete_status(FALSE);
1341 igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','FOUR_DISB_CHK',NULL,NULL);
1342 END IF;
1343 CLOSE c_nof_awd_disb;
1344 END IF;
1345
1346 -- If the Loan Amount Accepted is greater than the COA - EFC for Stafford Loans,
1347 -- then do not originate the Loan Record
1348 IF ( p_loan_catg = 'CL_STAFFORD' AND
1349 lor_rec.prc_type_code IN ('GP','GO') AND
1350 lor_rec.rec_type_ind <> 'T'
1351 ) THEN
1352
1353 IF (NVL(unmetneed(lor_rec.base_id),0) < 0) THEN
1354 set_complete_status(FALSE);
1355 igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','COA_EFC_CHK',NULL,NULL);
1356 log_to_fnd('cl_lar_validate','debug','loan_amt_accepted:'||lor_rec.loan_amt_accepted);
1357 END IF;
1358 END IF;
1359
1360 -- For Both Loan Types check the Dependency status for the Borrower
1361
1362 IF NVL(lv_dependency_status,'*') NOT IN ('I','D') AND (lor_rec.prc_type_code IN ('GP','GO')) THEN
1363 set_complete_status(FALSE);
1364 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DEPNCY_NO_EFC_CALC', 'S_DEPNCY_STATUS', NULL);
1365 log_to_fnd('cl_lar_validate','debug','-----S_DEPNCY_STATUS-----');
1366 END IF;
1367
1368 -- Loan Amount should be greater than zero for any Type of Loan. Else need to update the EditReport Table.
1369 IF NVL(lor_rec.loan_amt_accepted,0)<= 0 AND (lor_rec.prc_type_code IN ('GP','GO')) AND lor_rec.rec_type_ind <> 'T' THEN
1370 set_complete_status(FALSE);
1371 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'LESS_THAN_ZERO', 'LOAN_AMT_ACCEPTED', NULL);
1372 log_to_fnd('cl_lar_validate','debug','-----LOAN_AMT_ACCEPTED-----');
1373 END IF;
1374
1375 -- For Both Stafford and PLUS Loans all Disbursement Amounts should be Greater than Zero.Even if One
1376 -- amount is Zero or Less Than Zero then Need to Specify its Invalid
1377
1378 IF (lor_rec.prc_type_code IN ('GP','GO') AND lor_rec.rec_type_ind <> 'T' ) THEN
1379 -- For Release 4 processing, Disbursement Amount is needed only for Alternative Loans.
1380 -- It is not used in PLUS and Stafford Loans
1381 IF ((p_cl_version = 'RELEASE-4' AND p_loan_catg = 'CL_ALT') OR (p_cl_version = 'RELEASE-5')) THEN
1382 OPEN cur_count_disb_amount;
1383 FETCH cur_count_disb_amount INTO ln_disb_amt_count;
1384 IF ln_disb_amt_count <> 0 THEN
1385 set_complete_status(FALSE);
1386 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', 'DISB_MORE_THAN_ZERO', NULL, NULL);
1387 log_to_fnd('cl_lar_validate','debug','-----DISB_MORE_THAN_ZERO-----');
1388 END IF;
1389 CLOSE cur_count_disb_amount;
1390 END IF;
1391 END IF;
1392
1393 --For Both Stafford and PLUS loans all Disbursement Indicators should be not null.Even if one
1394 --Indicator is null then Need to Specify its Invalid
1395
1396 OPEN cur_count_disb_ind;
1397 FETCH cur_count_disb_ind INTO ln_disb_ind_count;
1398 IF ln_disb_ind_count <> 0 THEN
1399 set_complete_status(FALSE);
1400 igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','HOLD_REL_NOT_NULL',NULL,NULL);
1401 log_to_fnd('cl_lar_validate','debug','-----HOLD_REL_NOT_NULL-----');
1402 END IF;
1403 CLOSE cur_count_disb_ind;
1404
1405
1406 --For Both Stafford and PLUS loans all Disbursement Dates should be not null.Even if one
1407 --Date is null then Need to Specify its Invalid
1408 IF (lor_rec.prc_type_code IN ('GP','GO')) THEN
1409 OPEN cur_count_disb_dates;
1410 FETCH cur_count_disb_dates INTO ln_disb_dates_count;
1411 IF ln_disb_dates_count <> 0 THEN
1412 set_complete_status(FALSE);
1413 igf_sl_edit.insert_edit(lor_rec.loan_number,'V','IGF_SL_ERR_CODES','DISB_DATE_NOT_NULL',NULL,NULL);
1414 log_to_fnd('cl_lar_validate','debug','-----DISB_DATE_NOT_NULL-----');
1415 END IF;
1416 CLOSE cur_count_disb_dates;
1417 END IF;
1418 --Check added for address usages for both loan types
1419 --Possible that if more than one address has same address usage then
1420 --LOR DTLS View will fetch more than one loan record for the same loan id.
1421 --To avoid this from originating 2 records having all details same except
1422 --for addresses,we can make the loan status as Not Ready and show in reject details.
1423
1424 --MN 10-Jan-2005 Check for Certification amount and requested amount.
1425 -- bvisvana - Bug # 4575843 - Check for requested loan amount. It should be in whole number.
1426 IF (lor_rec.requested_loan_amt < lor_rec.loan_amt_accepted) OR
1427 ((lor_rec.requested_loan_amt - TRUNC(lor_rec.requested_loan_amt)) <> 0) THEN
1428 set_complete_status(FALSE);
1429 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_CL_ERROR', '014', 'REQUESTED_LOAN_AMT', NULL);
1430 log_to_fnd('cl_lar_validate','debug','-----REQUESTED_LOAN_AMT-----');
1431 END IF;
1432
1433 -- museshad (Bug 4116399) Stafford loan limit validation
1434 IF lor_rec.fed_fund_code NOT IN ('PRK','DLP','FLP','ALT','GPLUSFL') THEN
1435 lv_msg_name := NULL;
1436 lv_lookup_code := NULL;
1437 loan_limit_validation (
1438 p_base_id => lor_rec.base_id,
1439 p_fund_type => lor_rec.fed_fund_code,
1440 p_award_id => lor_rec.award_id,
1441 p_msg_name => lv_msg_name
1442 );
1443
1444 IF lv_msg_name IS NOT NULL THEN
1445 -- Stafforf loan limit validation failed
1446 lv_warning := TRUE;
1447
1448 IF lv_msg_name = 'IGF_AW_AGGR_LMT_ERR' THEN
1449 lv_lookup_code := 'AGGR_LMT_CHK';
1450 ELSIF lv_msg_name = 'IGF_AW_ANNUAL_LMT_ERR' THEN
1451 lv_lookup_code := 'ANNUAL_LMT_CHK';
1452 ELSIF lv_msg_name = 'IGF_AW_SUB_AGGR_LMT_ERR' THEN
1453 lv_lookup_code := 'SUB_AGGR_LMT_CHK';
1454 ELSIF lv_msg_name = 'IGF_AW_SUB_LMT_ERR' THEN
1455 lv_lookup_code := 'SUB_LMT_CHK';
1456 ELSIF lv_msg_name = 'IGF_AW_LOAN_LMT_NOT_FND' THEN
1457 set_complete_status(FALSE); -- For Bug # 5091652
1458 lv_lookup_code := 'LOAN_LMT_SETUP_CHK';
1459 END IF;
1460
1461 igf_sl_edit.insert_edit(lor_rec.loan_number, 'V', 'IGF_SL_ERR_CODES', lv_lookup_code, NULL, NULL);
1462
1463 -- Log
1464 log_to_fnd('cl_lar_validate','debug','Stafford loan limit validation failed with message: ' ||lv_msg_name);
1465
1466 END IF;
1467 END IF;
1468 -- museshad (Bug 4116399)
1469
1470 IF p_call_mode = 'JOB' THEN
1471
1472 -- If any validation fails or if any required data is missing, then set Loan Status=Not Ready.
1473 -- Else, set to "Valid and Ready to Send"
1474 IF lv_complete = FALSE THEN
1475 lv_loan_status := 'N'; -- NOT READY
1476 ELSE
1477 lv_loan_status := 'V'; -- VALID and READY TO SEND
1478 END IF;
1479
1480 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1481 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'lv_loan_status:' || lv_loan_status);
1482 END IF;
1483
1484 DECLARE
1485 CURSOR c_tbh_cur IS
1486 SELECT igf_sl_loans.* FROM igf_sl_loans
1487 WHERE loan_id = lor_rec.loan_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
1488 BEGIN
1489
1490 FOR tbh_rec in c_tbh_cur LOOP
1491
1492 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1493 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'Updating tbh_rec.loan_id:' || tbh_rec.loan_id);
1494 END IF;
1495
1496 -- Modified the update row call for the Borrower Determination as part of Refunds DLD 2144600
1497 igf_sl_loans_pkg.update_row (
1498 X_Mode => 'R',
1499 x_rowid => tbh_rec.row_id,
1500 x_loan_id => tbh_rec.loan_id,
1501 x_award_id => tbh_rec.award_id,
1502 x_seq_num => tbh_rec.seq_num,
1503 x_loan_number => tbh_rec.loan_number,
1504 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
1505 x_loan_per_end_date => tbh_rec.loan_per_end_date,
1506 x_loan_status => lv_loan_status,
1507 x_loan_status_date => TRUNC(SYSDATE),
1508 x_loan_chg_status => tbh_rec.loan_chg_status,
1509 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
1510 x_active => tbh_rec.active,
1511 x_active_date => tbh_rec.active_date,
1512 x_borw_detrm_code => tbh_rec.borw_detrm_code,
1513 x_legacy_record_flag => NULL,
1514 x_external_loan_id_txt => tbh_rec.external_loan_id_txt
1515
1516 );
1517
1518 IF (fnd_log.level_statement >= g_debug_runtime_level) THEN
1519 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_cl_validation.cl_lar_validate.debug', 'Updated tbh_rec.loan_id:' || tbh_rec.loan_id);
1520 END IF;
1521
1522 END LOOP;
1523 END;
1524
1525 -- If the Completeness of Record is False then Log File should be updated with the details
1526
1527 IF lv_complete = FALSE OR lv_warning = TRUE THEN
1528 -- Display reject details on the Concurrent Manager Log File.
1529 DECLARE
1530 lv_log_mesg VARCHAR2(1000);
1531 CURSOR c_reject IS
1532 SELECT RPAD(field_desc,70)||sl_error_desc reject_desc FROM igf_sl_edit_report_v
1533 WHERE loan_number = lor_rec.loan_number
1534 AND orig_chg_code = 'V';
1535 BEGIN
1536 fnd_file.put_line(fnd_file.log, '');
1537 fnd_file.put_line(fnd_file.log, '');
1538
1539 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER')||' : '||igf_gr_gen.get_per_num(lor_rec.base_id);
1540 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1541
1542 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_NUMBER')||' : '||lor_rec.loan_number;
1543 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1544
1545 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_SSN') ||' : '||student_dtl_rec.p_ssn;
1546 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1547
1548 lv_log_mesg := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_FULL_NAME')||' : '
1549 ||student_dtl_rec.p_first_name||' '||student_dtl_rec.p_last_name;
1550 fnd_file.put_line(fnd_file.log, lv_log_mesg);
1551
1552 FOR rej_rec IN c_reject LOOP
1553 fnd_file.put_line(fnd_file.log,' '||rej_rec.reject_desc);
1554 END LOOP;
1555 --FA 161 - Bug 5006583 - Give message to use the update mode
1556 IF g_update_mode_required THEN
1557 fnd_file.new_line(fnd_file.log,2);
1558 fnd_message.set_name('IGF','IGF_SL_CL_LOAN_UPD_CORR');
1559 fnd_file.put_line(fnd_file.log,fnd_message.get);
1560 END IF;
1561 END;
1562 END IF;
1563
1564 ELSE
1565 -- If the validation routinue is called from FORM, then just return the Status of TRUE/FALSE.
1566 NULL;
1567
1568 END IF;
1569
1570 g_loan_id:=lor_rec.loan_id; --Keep changing Global Loan ID everytime it is different.
1571
1572 END IF; --Check for Global Loan ID not being same.
1573
1574
1575 END LOOP;
1576
1577
1578 IF p_call_mode = 'JOB' THEN
1579 RETURN TRUE;
1580 ELSE
1581 RETURN lv_complete;
1582 END IF;
1583
1584 EXCEPTION
1585 WHEN NO_DATA_FOUND THEN
1586 NULL;
1587 WHEN OTHERS THEN
1588 --Removed the fnd_message.setname with Package.Procedure name.
1589 --This is because this procedure in turn calls igf_sl_dl_record package academic year functions
1590 --which return valid exceptions.only if we just propogate this exception
1591 --we will be able to trap both in form and in dl orig process.
1592 --hence removed code.
1593 --Bug :-2415041 Loan Orig with incorrect error messages.
1594
1595 app_exception.raise_exception;
1596
1597 END cl_lar_validate;
1598
1599 END igf_sl_cl_validation;
[Home] [Help]