[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_LI_IMP_PKG
Source
1 PACKAGE BODY IGF_SL_DL_LI_IMP_PKG AS
2 /* $Header: IGFSL20B.pls 120.6 2006/09/07 13:20:52 bvisvana ship $ */
3 CURSOR c_interface (cp_batch_id NUMBER,
4 cp_alternate_code VARCHAR2,
5 p_import_status_type_1 igf_sl_li_dlor_ints.import_status_type%TYPE,
6 p_import_status_type_2 igf_sl_li_dlor_ints.import_status_type%TYPE
7 ) IS
8 SELECT rowid,
9 batch_num batch_num ,
10 TRIM(ci_alternate_code) ci_alternate_code ,
11 TRIM(person_number) person_number ,
12 TRIM(award_number_txt) award_number_txt ,
13 TRIM(loan_number_txt) loan_number_txt,
14 TRIM(import_status_type) import_status_type ,
15 loan_seq_num loan_seq_num ,
16 TRUNC(loan_per_begin_date) loan_per_begin_date ,
17 TRUNC(loan_per_end_date) loan_per_end_date ,
18 TRIM(loan_status_code) loan_status_code ,
19 TRUNC(loan_status_date) loan_status_date ,
20 TRIM(active_flag) active_flag ,
21 TRUNC(active_date) active_date ,
22 TRIM(borr_person_number) borr_person_number ,
23 TRIM(grade_level_code) grade_level_code ,
24 TRUNC(orig_acknowledgement_date) orig_acknowledgement_date ,
25 TRUNC(orig_batch_date) orig_batch_date ,
26 TRIM(orig_send_batch_id_txt) orig_send_batch_id_txt ,
27 TRIM(pnote_status_code) pnote_status_code ,
28 TRIM(pnote_batch_seq_num_txt) pnote_batch_seq_num_txt ,
29 TRIM(pnote_id_txt) pnote_id_txt ,
30 TRIM(pnote_print_ind_code) pnote_print_ind_code ,
31 pnote_accept_amt pnote_accept_amt ,
32 TRUNC(pnote_accept_date) pnote_accept_date ,
33 TRIM(unsub_elig_for_depnt_code) unsub_elig_for_depnt_code ,
34 TRIM(unsub_elig_for_heal_code) unsub_elig_for_heal_code ,
35 TRIM(loan_chg_status) loan_chg_status ,
36 TRUNC(loan_chg_status_date) loan_chg_status_date ,
37 TRIM(pnote_status_type) pnote_status_type ,
38 TRIM(pnote_indicator_code) pnote_indicator_code ,
39 TRUNC(mpn_acknowledgement_date) mpn_acknowledgement_date ,
40 TRIM(mpn_reject_code) mpn_reject_code ,
41 orig_fee_perct_num orig_fee_perct_num ,
42 TRUNC(credit_decision_date) credit_decision_date ,
43 TRIM(credit_override_code) credit_override_code ,
44 endorser_amount endorser_amount,
45 TRIM(cr_desc_batch_id_txt) cr_desc_batch_id_txt,
46 TRIM(orig_reject_code) orig_reject_code ,
47 TRIM(disclosure_print_ind_code) disclosure_print_ind_code ,
48 TRIM(s_default_status_code) s_default_status_code ,
49 TRUNC(sch_cert_date) sch_cert_date ,
50 TRIM(p_default_status_code) p_default_status_code ,
51 loan_approved_amt loan_approved_amt ,
52 TRIM(import_record_type) import_record_type,
53 transaction_num transaction_num,
54 TRIM(atd_entity_id_txt) atd_entity_id_txt,
55 TRIM(rep_entity_id_txt) rep_entity_id_txt,
56 credit_status credit_status
57 FROM igf_sl_li_dlor_ints dlint
58 WHERE dlint.batch_num = cp_batch_id
59 AND dlint.ci_alternate_code = cp_alternate_code
60 AND (dlint.import_status_type = p_import_status_type_1 OR dlint.import_status_type = p_import_status_type_2);
61
62 CURSOR c_disb_interface(cp_alternate_code VARCHAR2,
63 cp_person_number VARCHAR2,
64 cp_award_number_txt VARCHAR2,
65 cp_loan_number VARCHAR2
66 ) IS
67 SELECT rowid,
68 TRIM(ci_alternate_code) ci_alternate_code ,
69 TRIM(person_number) person_number ,
70 TRIM(award_number_txt) award_number_txt,
71 disbursement_num disbursement_num ,
72 disbursement_seq_num disbursement_seq_num,
73 TRIM(loan_number_txt) loan_number_txt,
74 TRUNC(disbursement_date) disbursement_date,
75 gross_disbursement_amt gross_disbursement_amt,
76 TRIM(booking_batch_id_txt) booking_batch_id_txt,
77 TRUNC(booked_date) booked_date,
78 TRIM(disbursement_batch_id_txt) disbursement_batch_id_txt,
79 TRIM(disbursement_activity_code) disbursement_activity_code ,
80 TRIM(disbursement_activity_st_txt) disbursement_activity_st_txt,
81 loc_disbursement_gross_amt loc_disbursement_gross_amt,
82 loc_fee_1_amt loc_fee_1_amt ,
83 loc_disbursement_net_amt loc_disbursement_net_amt,
84 servicer_refund_amt servicer_refund_amt ,
85 loc_int_rebate_amt loc_int_rebate_amt ,
86 loc_net_booked_loan_amt loc_net_booked_loan_amt ,
87 TRUNC(acknowledgement_date) acknowledgement_date,
88 TRIM(school_code_txt) school_code_txt,
89 TRIM(confirmation_flag) confirmation_flag ,
90 interest_rebate_amt interest_rebate_amt ,
91 TRIM(user_identifier_txt) user_identifier_txt,
92 TRUNC(disbursement_activity_date) disbursement_activity_date
93
94
95
96 FROM igf_sl_li_dldb_ints dlint
97 WHERE dlint.ci_alternate_code = cp_alternate_code
98 AND dlint.person_number = cp_person_number
99 AND dlint.award_number_txt = cp_award_number_txt
100 AND dlint.loan_number_txt = cp_loan_number
101 ORDER BY disbursement_num ,disbursement_seq_num ;
102
103 CURSOR c_chg_interface(p_loan_number VARCHAR2)
104 IS
105 SELECT TRIM(loan_number_txt) loan_number_txt,
106 TRIM(change_code) change_code ,
107 TRIM(send_batch_id_txt) send_batch_id_txt,
108 TRIM(resp_batch_id_txt) resp_batch_id_txt,
109 TRIM(reject_code) reject_code,
110 TRIM(new_value_txt) new_value_txt,
111 TRIM(loan_ident_err_code) loan_ident_err_code
112
113 FROM igf_sl_li_chg_ints slchg
114 WHERE slchg.loan_number_txt = p_loan_number ;
115
116 IMPORT_ERROR EXCEPTION;
117 g_igf_sl_message_table igf_sl_message_table;
118 ln_origination_id NUMBER;
119 ln_loan_id igf_sl_loans.loan_id%TYPE;
120 ln_lor_resp_num NUMBER;
121 ln_dbth_id igf_sl_cl_batch_all.cbth_id%TYPE;
122 lv_fed_fund_code igf_aw_fund_cat.fed_fund_code%TYPE;
123 g_award_year VARCHAR2(3);
124 l_award_year_status VARCHAR2(80);
125 g_request_id NUMBER := NULL;
126 l_b_person_id NUMBER ;
127 g_error_string VARCHAR2(200);
128 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
129 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
130
131 PROCEDURE log_input_params( p_batch_num IN igf_aw_li_coa_ints.batch_num%TYPE ,
132 p_alternate_code IN igs_ca_inst.alternate_code%TYPE ,
133 p_delete_flag IN VARCHAR2)
134 IS
135 /*
136 || Created By : rasahoo
137 || Created On : 07-July-2003
138 || Purpose : Logs all the Input Parameters
139 || Known limitations, enhancements or remarks :
140 || Change History :
141 || Who When What
142 || (reverse chronological order - newest change first)
143 */
144
145 -- cursor to get batch desc for the batch id from igf_ap_li_bat_ints
146 CURSOR c_batch_desc(cp_batch_num igf_aw_li_coa_ints.batch_num%TYPE ) IS
147 SELECT batch_desc, batch_type
148 FROM igf_ap_li_bat_ints
149 WHERE batch_num = cp_batch_num ;
150
151 l_delete_flag_prmpt VARCHAR2(80);
152 l_error VARCHAR2(80);
153 l_lkup_type VARCHAR2(60) ;
154 l_lkup_code VARCHAR2(60) ;
155 l_batch_desc igf_ap_li_bat_ints.batch_desc%TYPE ;
156 l_batch_type igf_ap_li_bat_ints.batch_type%TYPE ;
157 l_batch_id igf_ap_li_bat_ints.batch_type%TYPE ;
158 l_yes_no igf_lookups_view.meaning%TYPE ;
159 l_award_year_pmpt igf_lookups_view.meaning%TYPE ;
160 l_params_pass_prmpt igf_lookups_view.meaning%TYPE ;
161 l_person_number_prmpt igf_lookups_view.meaning%TYPE ;
162 l_batch_num_prmpt igf_lookups_view.meaning%TYPE ;
163
164 BEGIN -- begin log parameters
165
166 -- get the batch description
167 OPEN c_batch_desc( p_batch_num) ;
168 FETCH c_batch_desc INTO l_batch_desc, l_batch_type ;
169 CLOSE c_batch_desc ;
170
171 fnd_message.set_name('IGS','IGS_GE_ASK_DEL_REC');
172 l_delete_flag_prmpt := fnd_message.get ;
173
174 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
175 l_person_number_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
176 l_batch_num_prmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
177 l_award_year_pmpt := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
178 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_delete_flag);
179 l_params_pass_prmpt := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PARAMETER_PASS');
180
181 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
182 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
183 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
184
185 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
186 FND_FILE.PUT_LINE( FND_FILE.LOG, l_params_pass_prmpt) ; --Parameters Passed
187 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ') ;
188
189 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_award_year_pmpt, 40) || ' : '|| p_alternate_code ) ;
190
191 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_batch_num_prmpt, 40) || ' : '|| p_batch_num || '-' || l_batch_desc ) ;
192
193
194 FND_FILE.PUT_LINE( FND_FILE.LOG, RPAD( l_delete_flag_prmpt, 40) || ' : '|| l_yes_no ) ;
195 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
196 FND_FILE.PUT_LINE( FND_FILE.LOG, '-------------------------------------------------------------');
197 FND_FILE.PUT_LINE( FND_FILE.LOG, ' ');
198
199 END log_input_params ;
200
201 PROCEDURE print_message(p_igf_sl_message_table IN igf_sl_message_table) AS
202 /*
203 || Created By : rasahoo
204 || Created On : 08-July-2003
205 || Purpose : Print the error messages stored in PL/SQL message table.
206 || Known limitations, enhancements or remarks :
207 || Change History :
208 || Who When What
209 || (reverse chronological order - newest change first)
210 */
211
212 indx NUMBER;
213 l_error VARCHAR2(30);
214 BEGIN
215 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
216 IF p_igf_sl_message_table.COUNT<>0 THEN
217 FOR indx IN p_igf_sl_message_table.FIRST..p_igf_sl_message_table.LAST
218 LOOP
219 fnd_file.put_line(fnd_file.log,p_igf_sl_message_table(indx).msg_text);
220 END LOOP;
221 END IF;
222 EXCEPTION
223 WHEN others THEN
224 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
225 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_li_imp_pkg.print_message.exception','Exception :'||SQLERRM);
226 END IF;
227 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
228 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.PRINT_MESSAGE');
229 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
230
231 RAISE IMPORT_ERROR;
232
233 END print_message;
234
235 FUNCTION is_pnote_id_valid ( l_value VARCHAR2)
236 RETURN BOOLEAN AS
237 /*
238 || Created By : rasahoo
239 || Created On : 08-July-2003
240 || Purpose : It checks for the vlidity promisorry note id.
241 || Known limitations, enhancements or remarks :
242 || Change History :
243 || Who When What
244 || (reverse chronological order - newest change first)
245 */
246
247 l_char_set VARCHAR2(100) := '0123456789';
248 l_ssn VARCHAR2(9);
249 l_loan_type VARCHAR2(1);
250 l_pgm_yr VARCHAR2(2);
251 l_sl_code VARCHAR2(6);
252 l_seq_num VARCHAR2(3);
253 BEGIN
254 l_ssn := SUBSTR(l_value,1,9);
255 l_loan_type := SUBSTR(l_value,10,1);
256 l_pgm_yr := SUBSTR(l_value,11,2);
257 l_sl_code := SUBSTR(l_value,13,6);
258 l_seq_num := SUBSTR(l_value,19,3);
259 -- Check for ssn
260 IF NVL(LENGTH(TRIM(TRANSLATE(l_ssn ,l_char_set,LPAD(' ',LENGTH(l_char_set),' ' )))),0) > 0
261 OR LENGTH(TRIM(l_ssn)) <> 9
262 OR TO_NUMBER(SUBSTR(l_ssn ,1,3)) < 1
263 OR TO_NUMBER(SUBSTR(l_ssn ,4,2)) < 1
264 OR TO_NUMBER(SUBSTR(l_ssn ,6,4)) < 1
265 -- check for loan type
266 OR l_loan_type <> 'N'
267 -- check for program year
268 OR l_pgm_yr NOT IN ('03','04','05','06')
269 -- check for school code
270 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
271 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_char_set,LPAD(' ',LENGTH(l_char_set),' ' )))),0) > 0
272 -- check for sequence number
273 OR LENGTH(TRIM(l_seq_num)) <> 3
274 OR TO_NUMBER(l_seq_num) NOT BETWEEN 1 AND 999 THEN
275
276 RETURN FALSE;
277 ELSE
278 RETURN TRUE;
279 END IF;
280 EXCEPTION WHEN OTHERS THEN
281 RETURN FALSE;
282 END is_pnote_id_valid;
283
284 FUNCTION is_batch_id_valid ( l_value VARCHAR2)
285 RETURN BOOLEAN AS
286 /*
287 || Created By : rasahoo
288 || Created On : 08-July-2003
289 || Purpose : It checks for the vlidity of batch id.
290 || Known limitations, enhancements or remarks :
291 || Change History :
292 || Who When What
293 || bvisvana 25-Aug-2006 Bug 5478287 - Extending the check for cycle year 6 and 7..
294 || (reverse chronological order - newest change first)
295 */
296
297 l_char_set VARCHAR2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
298 l_num_set VARCHAR2(10) := '1234567890';
299 l_batch_type VARCHAR2(2) := NULL;
300 l_cycle_ind VARCHAR2(1) := NULL;
301 l_sl_code VARCHAR2(6) := NULL;
302 l_dt_btch_created VARCHAR2(8) := NULL;
303 l_tm_btch_created VARCHAR2(6) := NULL;
304 BEGIN
305 l_batch_type := SUBSTR(l_value,1,2);
306 l_cycle_ind := SUBSTR(l_value,3,1);
307 l_sl_code := SUBSTR(l_value,4,6);
308 l_dt_btch_created := SUBSTR(l_value,10,8);
309 l_tm_btch_created := SUBSTR(l_value,18,6);
310
311 IF LENGTH(TRIM(l_value)) <> 23
312 -- check for batch type
313 OR SUBSTR(l_batch_type,1,1) <> '#'
314 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_batch_type,2,1),l_char_set,LPAD(' ',LENGTH(l_char_set),' ' )))),0) > 0
315 -- check for cycle indicator
316 OR l_cycle_ind NOT IN ('3', '4','5','6','7') -- Bug 5478287
317 -- check for school code
318 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
319 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
320 OR (TRANSLATE(SUBSTR(l_sl_code,2,5),' ','*')) <> (SUBSTR(l_sl_code,2,5))
321 THEN
322 RETURN FALSE;
323 ELSE
324 RETURN TRUE;
325 END IF;
326 END is_batch_id_valid;
327
328 FUNCTION is_school_code_valid(l_value VARCHAR2)
329 RETURN BOOLEAN AS
330 /*
331 || Created By : rasahoo
332 || Created On : 08-July-2003
333 || Purpose : It checks for the vlidity of school code.
334 || Known limitations, enhancements or remarks :
335 || Change History :
336 || Who When What
337 || (reverse chronological order - newest change first)
338 */
339 CURSOR c_get_dl_school IS
340 SELECT 'X'
341 FROM HZ_PARTIES HZ,
342 IGS_OR_ORG_ALT_IDS OLI,
343 IGS_OR_ORG_ALT_IDTYP OLT
344 WHERE OLI.ORG_STRUCTURE_ID = HZ.PARTY_NUMBER
345 AND OLI.ORG_ALTERNATE_ID_TYPE = OLT.ORG_ALTERNATE_ID_TYPE
346 AND SYSDATE BETWEEN OLI.START_DATE AND NVL (END_DATE, SYSDATE)
347 AND HZ.STATUS = 'A'
348 AND OLI.ORG_ALTERNATE_ID = l_value
349 AND system_id_type = 'DL_SCH_CD';
350
351 lv_exists VARCHAR2(1);
352 l_num_set VARCHAR2(10) := '1234567890';
353 BEGIN
354 IF LENGTH(TRIM(l_value)) <> 6
355 OR SUBSTR(l_value,1,1) NOT IN ('G','E')
356 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_value,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
357 OR (TRANSLATE(SUBSTR(l_value,2,5),' ','*')) <> (SUBSTR(l_value,2,5))
358 THEN
359 RETURN FALSE;
360 ELSE
361 OPEN c_get_dl_school;
362 FETCH c_get_dl_school INTO lv_exists;
363 CLOSE c_get_dl_school;
364 IF(NVL(lv_exists,'N')='X')THEN
365 RETURN TRUE;
366 ELSE
367 RETURN FALSE;
368 END IF;
369 END IF;
370 END is_school_code_valid;
371
372 FUNCTION is_loan_number_valid ( l_value VARCHAR2)
373 RETURN BOOLEAN AS
374 /*
375 || Created By : rasahoo
376 || Created On : 08-July-2003
377 || Purpose : It checks for the vlidity of loan number.
378 || Known limitations, enhancements or remarks :
379 || Change History :
380 || Who When What
381 || bvisvana 25-Aug-2006 Bug 5478287 - Extending the logic for 2007
382 || (reverse chronological order - newest change first)
383 */
384
385 l_char_set VARCHAR2(100) := '0123456789';
386 l_ssn VARCHAR2(9);
387 l_loan_type VARCHAR2(1);
388 l_pgm_yr VARCHAR2(2);
389 l_sl_code VARCHAR2(6);
390 l_seq_num VARCHAR2(3);
391 BEGIN
392 l_ssn := SUBSTR(l_value,1,9);
393 l_loan_type := SUBSTR(l_value,10,1);
394 l_pgm_yr := SUBSTR(l_value,11,2);
395 l_sl_code := SUBSTR(l_value,13,6);
396 l_seq_num := SUBSTR(l_value,19,3);
397 -- Check for ssn
398 IF NVL(LENGTH(TRIM(TRANSLATE(l_ssn ,l_char_set,LPAD(' ',LENGTH(l_char_set),' ' )))),0) > 0
399 OR LENGTH(TRIM(l_ssn)) <> 9
400 OR TO_NUMBER(SUBSTR(l_ssn ,1,3)) < 1
401 OR TO_NUMBER(SUBSTR(l_ssn ,4,2)) < 1
402 OR TO_NUMBER(SUBSTR(l_ssn ,6,4)) < 1
403 -- check for loan type
404 OR l_loan_type NOT IN ('S','U','P')
405 -- check for program year
406 OR l_pgm_yr NOT IN ('03','04','05','06','07') -- Bug 5478287
407 -- check for school code
408 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
409 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_char_set,LPAD(' ',LENGTH(l_char_set),' ' )))),0) > 0
410 -- check for sequence number
411 OR LENGTH(TRIM(l_seq_num)) <> 3
412 OR TO_NUMBER(l_seq_num) NOT BETWEEN 1 AND 999 THEN
413
414 RETURN FALSE;
415 ELSE
416 -- the school code in the loan number should be a valid school code in the system
417 IF(is_school_code_valid(l_sl_code))THEN
418 RETURN TRUE;
419 ELSE
420 RETURN FALSE;
421 END IF;
422 END IF;
423 EXCEPTION WHEN OTHERS THEN
424 RETURN FALSE;
425 END is_loan_number_valid;
426
427 FUNCTION Val_Date ( l_value IN VARCHAR2)
428 RETURN BOOLEAN AS
429 /*
430 || Created By : rasahoo
431 || Created On : 08-July-2003
432 || Purpose : It checks for the vlidity of date which lies between 19000101 AND 20991231 .
433 || Known limitations, enhancements or remarks :
434 || Change History :
435 || Who When What
436 || (reverse chronological order - newest change first)
437 */
438
439 BEGIN
440
441 IF TO_NUMBER(l_value) BETWEEN 19000101 AND 20991231
442 THEN
443 RETURN TRUE ;
444 ELSE
445 RETURN FALSE;
446 END IF;
447 EXCEPTION WHEN OTHERS THEN
448 RETURN FALSE;
449 END Val_Date;
450
451 FUNCTION Val_Date_2 ( l_value IN VARCHAR2)
452 RETURN BOOLEAN AS
453 /*
454 || Created By : rasahoo
455 || Created On : 03-June-2003
456 || Purpose :Validate the validity of date
457 || date should be between 20020622 AND 20050927
458 || Known limitations, enhancements or remarks :
459 || Change History :
460 || Who When What
461 || (reverse chronological order - newest change first)
462 */
463 BEGIN
464
465 IF TO_NUMBER(l_value) BETWEEN 20020622 AND 20050927
466 THEN
467 RETURN TRUE ;
468 ELSE
469 RETURN FALSE;
470 END IF;
471 EXCEPTION WHEN OTHERS THEN
472 RETURN FALSE;
473 END Val_Date_2;
474
475 FUNCTION is_pnote_batch_id_valid ( l_value VARCHAR2)
476 RETURN BOOLEAN
477 AS
478 /*
479 || Created By : rasahoo
480 || Created On : 03-June-2003
481 || Purpose : Checks for the validity of Promissory note batch id.
482 || Known limitations, enhancements or remarks :
483 || Change History :
484 || Who When What
485 || bvisvana 25-Aug-20006 Bug 5478287 - Extending for cycle year 6 and 7
486 || (reverse chronological order - newest change first)
487 */
488 l_char_set VARCHAR2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
489 l_num_set VARCHAR2(10) := '1234567890';
490 l_batch_type VARCHAR2(2) := NULL;
491 l_cycle_ind VARCHAR2(1) := NULL;
492 l_sl_code VARCHAR2(6) := NULL;
493 l_dt_btch_created VARCHAR2(8) := NULL;
494 l_tm_btch_created VARCHAR2(6) := NULL;
495 BEGIN
496 l_batch_type := SUBSTR(l_value,1,2);
497 l_cycle_ind := SUBSTR(l_value,3,1);
498 l_sl_code := SUBSTR(l_value,4,6);
499 l_dt_btch_created := SUBSTR(l_value,10,8);
500 l_tm_btch_created := SUBSTR(l_value,18,6);
501
502 IF LENGTH(TRIM(l_value)) <> 23
503 -- check for batch type
504 OR SUBSTR(l_batch_type,1,2) NOT IN ('#A','#D','PF')
505 -- check for cycle indicator
506 OR l_cycle_ind NOT IN ('3', '4','5','6','7') -- Bug 5478287
507 -- check for school code
508 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
509 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
510 OR (TRANSLATE(SUBSTR(l_sl_code,2,5),' ','*')) <> (SUBSTR(l_sl_code,2,5))
511 THEN
512 RETURN FALSE;
513 ELSE
514 RETURN TRUE;
515 END IF;
516 END is_pnote_batch_id_valid;
517
518 FUNCTION is_disb_batch_id_valid ( l_value VARCHAR2)
519 RETURN BOOLEAN
520 AS
521 /*
522 || Created By : rasahoo
523 || Created On : 03-June-2003
524 || Purpose : Checks for validity of disbursement batch id.
525 || Change History :
526 || Who When What
527 || bvisvana 25-Aug-2006 Bug 5478287 - Extending for 6 and 7
528 || (reverse chronological order - newest change first)
529 */
530 l_char_set VARCHAR2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
531 l_num_set VARCHAR2(10) := '1234567890';
532 l_batch_type VARCHAR2(2) := NULL;
533 l_cycle_ind VARCHAR2(1) := NULL;
534 l_sl_code VARCHAR2(6) := NULL;
535 l_dt_btch_created VARCHAR2(8) := NULL;
536 l_tm_btch_created VARCHAR2(6) := NULL;
537 BEGIN
538 l_batch_type := SUBSTR(l_value,1,2);
539 l_cycle_ind := SUBSTR(l_value,3,1);
540 l_sl_code := SUBSTR(l_value,4,6);
541 l_dt_btch_created := SUBSTR(l_value,10,8);
542 l_tm_btch_created := SUBSTR(l_value,18,6);
543
544 IF LENGTH(TRIM(l_value)) <> 23
545 -- check for batch type
546 OR SUBSTR(l_batch_type,1,2) NOT IN ('#H','#B','SP')
547 -- check for cycle indicator
548 OR l_cycle_ind NOT IN ('3', '4','5','6','7') -- bug 5478287
549 -- check for school code
550 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
551 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
552 OR (TRANSLATE(SUBSTR(l_sl_code,2,5),' ','*')) <> (SUBSTR(l_sl_code,2,5))
553 THEN
554 RETURN FALSE;
555 ELSE
556 RETURN TRUE;
557 END IF;
558 END is_disb_batch_id_valid;
559
560 FUNCTION is_booking_batch_id_valid ( l_value VARCHAR2)
561 RETURN BOOLEAN
562 AS
563 /*
564 || Created By : rasahoo
565 || Created On : 03-June-2003
566 || Purpose : Checks for validity of disbursement batch id.
567 || Change History :
568 || Who When What
569 || bvisvana 25-Aug-2006 Bug 5478287 - Extending for cycle year 6 and 7
570 || (reverse chronological order - newest change first)
571 */
572 l_char_set VARCHAR2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
573 l_num_set VARCHAR2(10) := '1234567890';
574 l_batch_type VARCHAR2(2) := NULL;
575 l_cycle_ind VARCHAR2(1) := NULL;
576 l_sl_code VARCHAR2(6) := NULL;
577 l_dt_btch_created VARCHAR2(8) := NULL;
578 l_tm_btch_created VARCHAR2(6) := NULL;
579 BEGIN
580 l_batch_type := SUBSTR(l_value,1,2);
581 l_cycle_ind := SUBSTR(l_value,3,1);
582 l_sl_code := SUBSTR(l_value,4,6);
583 l_dt_btch_created := SUBSTR(l_value,10,8);
584 l_tm_btch_created := SUBSTR(l_value,18,6);
585
586 IF LENGTH(TRIM(l_value)) <> 23
587 -- check for batch type
588 OR SUBSTR(l_batch_type,1,2) <>'#B'
589 -- check for cycle indicator
590 OR l_cycle_ind NOT IN ('3', '4','5','6','7') -- Bug 5478287
591 -- check for school code
592 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
593 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
594 OR (TRANSLATE(SUBSTR(l_sl_code,2,5),' ','*')) <> (SUBSTR(l_sl_code,2,5))
595 THEN
596 RETURN FALSE;
597 ELSE
598 RETURN TRUE;
599 END IF;
600 END is_booking_batch_id_valid;
601
602 FUNCTION is_numeric ( l_value VARCHAR2)
603 RETURN BOOLEAN
604 AS
605 /*
606 || Created By : rasahoo
607 || Created On : 03-June-2003
608 || Purpose : Checks whether the value is numeric or not.
609 || Change History :
610 || Who When What
611 || (reverse chronological order - newest change first)
612 */
613
614 l_num_set VARCHAR2(10) := '1234567890';
615 BEGIN
616 IF NVL(LENGTH(TRIM(TRANSLATE(l_value,l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
617 OR (TRANSLATE(l_value,' ','*')) <> l_value THEN
618 RETURN FALSE;
619 ELSE
620 RETURN TRUE;
621 END IF;
622 END is_numeric;
623
624 FUNCTION is_credit_batch_id_valid ( l_value VARCHAR2)
625 RETURN BOOLEAN
626 AS
627 /*
628 || Created By : rasahoo
629 || Created On : 03-June-2003
630 || Purpose : Checks for the validity of credit batch id..
631 || Change History :
632 || Who When What
633 || bvisvana 25-Aug-2006 bug 5478287 - Extending for cycle year 6 and 7
634 || (reverse chronological order - newest change first)
635 */
636 l_char_set VARCHAR2(100) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
637 l_num_set VARCHAR2(10) := '1234567890';
638 l_batch_type VARCHAR2(2) := NULL;
639 l_cycle_ind VARCHAR2(1) := NULL;
640 l_sl_code VARCHAR2(6) := NULL;
641 l_dt_btch_created VARCHAR2(8) := NULL;
642 l_tm_btch_created VARCHAR2(6) := NULL;
643 BEGIN
644 l_batch_type := SUBSTR(l_value,1,2);
645 l_cycle_ind := SUBSTR(l_value,3,1);
646 l_sl_code := SUBSTR(l_value,4,6);
647 l_dt_btch_created := SUBSTR(l_value,10,8);
648 l_tm_btch_created := SUBSTR(l_value,18,6);
649
650 IF LENGTH(TRIM(l_value)) <> 23
651 -- check for batch type
652 OR SUBSTR(l_batch_type,1,2) NOT IN ('#D','PF')
653 -- check for cycle indicator
654 OR l_cycle_ind NOT IN ('3', '4','5','6','7')
655 -- check for school code
656 OR SUBSTR(l_sl_code,1,1) NOT IN ('G','E')
657 OR NVL(LENGTH(TRIM(TRANSLATE(SUBSTR(l_sl_code,2,5),l_num_set,LPAD(' ',LENGTH(l_num_set),' ' )))),0) > 0
658 OR (TRANSLATE(SUBSTR(l_sl_code,2,5),' ','*')) <> (SUBSTR(l_sl_code,2,5))
659 THEN
660 RETURN FALSE;
661 ELSE
662 RETURN TRUE;
663 END IF;
664 END is_credit_batch_id_valid;
665
666 PROCEDURE validate_loan_disb( p_disb_interface IN c_disb_interface%ROWTYPE,
667 p_award_id IN NUMBER,
668 p_d_status OUT NOCOPY BOOLEAN,
669 p_igf_sl_msg_table OUT NOCOPY igf_sl_message_table
670 )
671 AS
672 /*
673 || Created By : rasahoo
674 || Created On : 08-July-2003
675 || Purpose : This procedure is used to validate the loan origination disbursement interface record
676 || Known limitations, enhancements or remarks :
677 || Change History :
678 || Who When What
679 || (reverse chronological order - newest change first)
680 */
681 indx NUMBER := 0;
682 l_error VARCHAR2(10);
683 l_valid BOOLEAN;
684 CURSOR c_gross_amt(cp_award_id NUMBER,
685 cp_disb_num NUMBER,
686 cp_disb_seq_num NUMBER )
687 IS
688 SELECT disb_gross_amt
689 FROM igf_db_awd_disb_dtl_all
690 WHERE award_id = cp_award_id
691 AND disb_num = cp_disb_num
692 AND disb_seq_num = cp_disb_seq_num;
693
694 l_gross_amt c_gross_amt%ROWTYPE;
695 BEGIN
696 l_valid := TRUE;
697 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
698 p_d_status := TRUE;
699
700
701 p_igf_sl_msg_table.DELETE;
702
703 IF (p_disb_interface.disbursement_num < 0) OR (p_disb_interface.disbursement_num > 99) THEN
704 indx := indx + 1;
705 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISBURSEMENT_NUM';
706 p_d_status := FALSE;
707 END IF;
708
709
710 IF p_disb_interface.booking_batch_id_txt IS NOT NULL THEN
711 l_valid := is_booking_batch_id_valid(p_disb_interface.booking_batch_id_txt);
712 IF NOT l_valid THEN
713 indx := indx + 1;
714 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'BOOKING_BATCH_ID_TXT';
715 p_d_status := FALSE;
716 END IF;
717 END IF;
718
719 IF l_award_year_status = 'O' THEN
720 OPEN c_gross_amt(p_award_id,p_disb_interface.disbursement_num,p_disb_interface.disbursement_seq_num);
721 FETCH c_gross_amt INTO l_gross_amt;
722 CLOSE c_gross_amt;
723
724 IF (NVL(l_gross_amt.disb_gross_amt ,0)<> NVL(p_disb_interface.gross_disbursement_amt,0))
725 OR NVL(p_disb_interface.gross_disbursement_amt ,0) < 0
726 OR p_disb_interface.gross_disbursement_amt IS NULL THEN
727 indx := indx + 1;
728 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'GROSS_DISBURSEMENT_AMT';
729 p_d_status := FALSE;
730 END IF;
731 ELSE
732 IF NVL(p_disb_interface.gross_disbursement_amt,0) < 0
733 OR p_disb_interface.gross_disbursement_amt IS NULL THEN
734 indx := indx + 1;
735 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'GROSS_DISBURSEMENT_AMT';
736 p_d_status := FALSE;
737 END IF;
738
739 END IF;
740
741
742 IF igf_ap_gen.get_lookup_meaning('IGF_DB_DL_ACTIVITY',p_disb_interface.DISBURSEMENT_ACTIVITY_CODE) IS NULL
743 OR p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IS NULL THEN
744 indx := indx + 1;
745 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISBURSEMENT_ACTIVITY_CODE';
746 p_d_status := FALSE;
747 END IF;
748
749 l_valid := Val_Date_2(TO_CHAR(p_disb_interface.DISBURSEMENT_ACTIVITY_DATE,'YYYYMMDD'));
750 IF NOT l_valid THEN
751 indx := indx + 1;
752 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISBURSEMENT_ACTIVITY_DATE';
753 p_d_status := FALSE;
754 END IF;
755
756 l_valid := Val_Date_2(TO_CHAR(p_disb_interface.DISBURSEMENT_DATE,'YYYYMMDD'));
757 IF NOT l_valid THEN
758 indx := indx + 1;
759 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISBURSEMENT_DATE';
760 p_d_status := FALSE;
761 END IF;
762
763 -- validations for INTEREST_REBATE_AMT
764
765 IF NVL(p_disb_interface.INTEREST_REBATE_AMT,0) <= 0 THEN
766 indx := indx + 1;
767 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'INTEREST_REBATE_AMT';
768 p_d_status := FALSE;
769 END IF;
770
771 -- Validations for LOC_DISBURSEMENT_GROSS_AMT
772
773 IF l_award_year_status = 'O' THEN
774 IF g_award_year = '3' THEN
775
776 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
777 AND NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) <= 0) THEN
778 indx := indx + 1;
779 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_GROSS_AMT';
780 p_d_status := FALSE;
781 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
782 IF p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT IS NOT NULL THEN
783 IF NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) > 0
784 OR NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) < 0 THEN
785 indx := indx + 1;
786 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_GROSS_AMT';
787 p_d_status := FALSE;
788 END IF;
789 END IF;
790 END IF;
791 ELSIF g_award_year IN ('4','5') THEN
792
793 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
794 AND NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) < 0) THEN
795 indx := indx + 1;
796 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_GROSS_AMT';
797 p_d_status := FALSE;
798
799 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
800 IF p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT IS NOT NULL THEN
801 IF NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) > 0
802 OR NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) < 0 THEN
803 indx := indx + 1;
804 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_GROSS_AMT';
805 p_d_status := FALSE;
806 END IF;
807 END IF;
808
809 END IF;
810
811 END IF;
812 ELSE
813 IF NVL(p_disb_interface.LOC_DISBURSEMENT_GROSS_AMT,0) < 0 THEN
814 indx := indx + 1;
815 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_GROSS_AMT';
816 p_d_status := FALSE;
817
818 END IF;
819 END IF;
820 -- validations for LOC_FEE_1_AMT
821 IF l_award_year_status = 'O' THEN
822 IF g_award_year = '3' THEN
823
824 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
825 AND NVL(p_disb_interface.LOC_FEE_1_AMT,0) <= 0) THEN
826 indx := indx + 1;
827 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_FEE_1_AMT';
828 p_d_status := FALSE;
829 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
830 IF p_disb_interface.LOC_FEE_1_AMT IS NOT NULL THEN
831 IF p_disb_interface.LOC_FEE_1_AMT > 0
832 OR p_disb_interface.LOC_FEE_1_AMT < 0 THEN
833 indx := indx + 1;
834 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_FEE_1_AMT';
835 p_d_status := FALSE;
836 END IF;
837 END IF;
838 END IF;
839 ELSIF g_award_year IN ('4','5') THEN
840
841 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
842 AND NVL(p_disb_interface.LOC_FEE_1_AMT,0) < 0) THEN
843 indx := indx + 1;
844 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_FEE_1_AMT';
845 p_d_status := FALSE;
846
847 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
848 IF p_disb_interface.LOC_FEE_1_AMT IS NOT NULL THEN
849 IF p_disb_interface.LOC_FEE_1_AMT > 0
850 OR p_disb_interface.LOC_FEE_1_AMT < 0 THEN
851 indx := indx + 1;
852 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_FEE_1_AMT';
853 p_d_status := FALSE;
854 END IF;
855 END IF;
856
857 END IF;
858
859 END IF;
860 ELSE
861 IF NVL(p_disb_interface.LOC_FEE_1_AMT,0) <= 0 THEN
862 indx := indx + 1;
863 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_FEE_1_AMT';
864 p_d_status := FALSE;
865
866 END IF;
867 END IF;
868
869 -- validations for LOC_INT_REBATE_AMT
870
871 IF l_award_year_status = 'O' THEN
872 IF g_award_year = '3' THEN
873
874 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
875 AND NVL(p_disb_interface.LOC_INT_REBATE_AMT,0) <= 0) THEN
876 indx := indx + 1;
877 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_INT_REBATE_AMT';
878 p_d_status := FALSE;
879 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
880 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IS NOT NULL THEN
881 IF p_disb_interface.LOC_INT_REBATE_AMT > 0
882 OR p_disb_interface.LOC_INT_REBATE_AMT < 0 THEN
883 indx := indx + 1;
884 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_INT_REBATE_AMT';
885 p_d_status := FALSE;
886 END IF;
887 END IF;
888 END IF;
889
890 ELSIF g_award_year IN ('4','5') THEN
891
892 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
893 AND p_disb_interface.LOC_INT_REBATE_AMT < 0) THEN
894 indx := indx + 1;
895 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_INT_REBATE_AMT';
896 p_d_status := FALSE;
897
898 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
899 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IS NOT NULL THEN
900 IF p_disb_interface.LOC_INT_REBATE_AMT > 0
901 OR p_disb_interface.LOC_INT_REBATE_AMT < 0 THEN
902 indx := indx + 1;
903 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_INT_REBATE_AMT';
904 p_d_status := FALSE;
905 END IF;
906 END IF;
907
908 END IF;
909
910 END IF;
911 ELSE
912 IF NVL(p_disb_interface.LOC_INT_REBATE_AMT,0) <= 0 THEN
913 indx := indx + 1;
914 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_INT_REBATE_AMT';
915 p_d_status := FALSE;
916
917 END IF;
918 END IF;
919
920 -- validations for LOC_DISBURSEMENT_NET_AMT
921
922 IF l_award_year_status = 'O' THEN
923 IF g_award_year = '3' THEN
924
925 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
926 AND NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) <= 0) THEN
927 indx := indx + 1;
928 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_NET_AMT';
929 p_d_status := FALSE;
930 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
931 IF p_disb_interface.LOC_DISBURSEMENT_NET_AMT IS NOT NULL THEN
932 IF NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) > 0
933 OR NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) < 0 THEN
934 indx := indx + 1;
935 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_NET_AMT';
936 p_d_status := FALSE;
937 END IF;
938 END IF;
939 END IF;
940 ELSIF g_award_year IN ('4','5') THEN
941
942 IF ( p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D')
943 AND NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) < 0) THEN
944 indx := indx + 1;
945 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_NET_AMT';
946 p_d_status := FALSE;
947
948 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
949 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IS NOT NULL THEN
950 IF NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) > 0
951 OR NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) < 0 THEN
952 indx := indx + 1;
953 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_NET_AMT';
954 p_d_status := FALSE;
955 END IF;
956 END IF;
957
958 END IF;
959
960 END IF;
961 ELSE
962 IF NVL(p_disb_interface.LOC_DISBURSEMENT_NET_AMT,0) <= 0 THEN
963 indx := indx + 1;
964 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_DISBURSEMENT_NET_AMT';
965 p_d_status := FALSE;
966
967 END IF;
968 END IF;
969
970 -- Validations for DISBURSEMENT_BATCH_ID_TXT
971
972 l_valid := is_disb_batch_id_valid(p_disb_interface.DISBURSEMENT_BATCH_ID_TXT);
973 IF p_disb_interface.DISBURSEMENT_BATCH_ID_TXT IS NULL
974 OR NOT l_valid THEN
975 indx := indx + 1;
976 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISBURSEMENT_BATCH_ID_TXT';
977 p_d_status := FALSE;
978 END IF;
979
980 -- Validations for SERVICER_REFUND_AMT
981 IF l_award_year_status = 'O' THEN
982 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D','Q') THEN
983 IF p_disb_interface.SERVICER_REFUND_AMT IS NOT NULL THEN
984 indx := indx + 1;
985 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'SERVICER_REFUND_AMT';
986 p_d_status := FALSE;
987 END IF;
988 ELSE
989 IF NVL(p_disb_interface.SERVICER_REFUND_AMT,0) < 0 THEN
990 indx := indx + 1;
991 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'SERVICER_REFUND_AMT';
992 p_d_status := FALSE;
993 END IF;
994 END IF;
995 ELSE
996 IF NVL(p_disb_interface.SERVICER_REFUND_AMT,0) < 0 THEN
997 indx := indx + 1;
998 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'SERVICER_REFUND_AMT';
999 p_d_status := FALSE;
1000 END IF;
1001 END IF;
1002
1003 -- validations for loc_net_booked_loan_amt
1004 IF l_award_year_status = 'O' THEN
1005 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D','Q') THEN
1006 IF p_disb_interface.LOC_NET_BOOKED_LOAN_AMT IS NOT NULL THEN
1007 indx := indx + 1;
1008 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_NET_BOOKED_LOAN_AMT';
1009 p_d_status := FALSE;
1010 END IF;
1011 ELSIF NVL(p_disb_interface.LOC_NET_BOOKED_LOAN_AMT,0) < 0 THEN
1012 indx := indx + 1;
1013 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_NET_BOOKED_LOAN_AMT';
1014 p_d_status := FALSE;
1015 END IF;
1016 ELSIF NVL(p_disb_interface.LOC_NET_BOOKED_LOAN_AMT,0) < 0 THEN
1017 indx := indx + 1;
1018 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOC_NET_BOOKED_LOAN_AMT';
1019 p_d_status := FALSE;
1020
1021 END IF;
1022
1023 -- validations for acknowledgement_date
1024 l_valid := Val_Date(TO_CHAR(p_disb_interface.ACKNOWLEDGEMENT_DATE,'YYYYMMDD'));
1025 IF NOT l_valid THEN
1026 indx := indx + 1;
1027 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ACKNOWLEDGEMENT_DATE';
1028 p_d_status := FALSE;
1029 END IF;
1030
1031 -- validations for confirmation_flag
1032 IF l_award_year_status = 'O' THEN
1033 IF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE IN ('A','D') THEN
1034 IF p_disb_interface.CONFIRMATION_FLAG IS NOT NULL THEN
1035 IF p_disb_interface.CONFIRMATION_FLAG <> 'Y' THEN
1036 indx := indx + 1;
1037 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CONFIRMATION_FLAG';
1038 p_d_status := FALSE;
1039 END IF;
1040 END IF;
1041 ELSIF p_disb_interface.DISBURSEMENT_ACTIVITY_CODE = 'Q' THEN
1042 IF p_disb_interface.CONFIRMATION_FLAG IS NOT NULL THEN
1043 indx := indx + 1;
1044 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CONFIRMATION_FLAG';
1045 p_d_status := FALSE;
1046 END IF;
1047 END IF;
1048 END IF;
1049
1050 -- validations for school_code_txt
1051 IF p_disb_interface.SCHOOL_CODE_TXT IS NOT NULL THEN
1052 l_valid := is_school_code_valid(p_disb_interface.SCHOOL_CODE_TXT);
1053 IF NOT l_valid THEN
1054 indx := indx + 1;
1055 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'SCHOOL_CODE_TXT';
1056 p_d_status := FALSE;
1057 END IF;
1058 END IF;
1059
1060 EXCEPTION
1061
1062 WHEN others THEN
1063 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1064 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_li_imp_pkg.validate_loan_disb.exception','Exception: '||SQLERRM);
1065 END IF;
1066 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1067 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.VALIDATE_LOAN_DISB');
1068 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
1069
1070 RAISE IMPORT_ERROR;
1071
1072 END validate_loan_disb;
1073
1074 PROCEDURE validate_loan_orig_int( p_interface IN c_interface%ROWTYPE,
1075 p_award_id IN NUMBER,
1076 p_status OUT NOCOPY BOOLEAN,
1077 p_igf_sl_msg_table OUT NOCOPY igf_sl_message_table
1078 )
1079 AS
1080 /*
1081 || Created By : rasahoo
1082 || Created On : 08-July-2003
1083 || Purpose : This procedure is used to validate the loan origination interface record
1084 || Known limitations, enhancements or remarks :
1085 || Change History :
1086 || Who When What
1087 || rasahoo 11-Aug-2003 Removed the validation for Change status type and
1088 || added validation logic for loan_chg_status
1089 || (reverse chronological order - newest change first)
1090 */
1091 l_valid BOOLEAN;
1092 indx NUMBER := 0;
1093 l_error VARCHAR2(20);
1094 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
1095 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
1096
1097 -- Get the details of
1098 CURSOR c_accepted_amt(p_award_id NUMBER)
1099 IS
1100 SELECT accepted_amt
1101 FROM igf_aw_award
1102 WHERE award_id = p_award_id;
1103
1104 l_accepted_amt c_accepted_amt%ROWTYPE;
1105
1106 BEGIN
1107 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
1108 l_valid := TRUE;
1109
1110 -- intialize process status
1111 p_status := TRUE;
1112 p_igf_sl_msg_table.DELETE;
1113
1114 -- validate loan number
1115 l_valid := is_loan_number_valid(p_interface.loan_number_txt);
1116 IF (p_interface.loan_number_txt IS NULL) OR (l_valid = FALSE) THEN
1117 indx := indx + 1;
1118 fnd_message.set_name('IGF','IGF_SL_INVAL_DL_ID');
1119 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1120 p_status := FALSE;
1121 END IF;
1122
1123 -- Validate loan_seq_num
1124 IF p_interface.loan_seq_num IS NULL
1125 OR NVL(p_interface.loan_seq_num ,0) < 1 THEN
1126 indx := indx + 1;
1127 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_SEQ_NUM';
1128 p_status := FALSE;
1129 END IF;
1130
1131 -- validate loan_per_begin_date
1132 IF p_interface.loan_per_begin_date IS NULL THEN
1133 indx := indx + 1;
1134 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_PER_BEGIN_DATE';
1135 p_status := FALSE;
1136 END IF;
1137
1138 -- validate loan_per_end_date
1139 IF p_interface.loan_per_end_date IS NULL
1140 OR (p_interface.loan_per_end_date < p_interface.loan_per_begin_date) THEN
1141 indx := indx + 1;
1142 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_PER_END_DATE';
1143 p_status := FALSE;
1144 END IF;
1145
1146 -- validate loan_status_code
1147 IF igf_ap_gen.get_lookup_meaning('IGF_SL_LOAN_STATUS', p_interface.loan_status_code) IS NULL
1148 OR p_interface.loan_status_code IN ('B','C','R','S','T')
1149 OR p_interface.loan_status_code IS NULL THEN
1150 indx := indx + 1;
1151 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_STATUS_CODE';
1152 p_status := FALSE;
1153 END IF;
1154
1155 --Validations for loan_chg_status
1156 IF p_interface.loan_chg_status IS NOT NULL THEN
1157 IF igf_ap_gen.get_lookup_meaning('IGF_SL_LOAN_CHG_STATUS', p_interface.loan_chg_status) IS NULL
1158 OR p_interface.loan_chg_status IN ('S','B') THEN
1159 indx := indx + 1;
1160 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_CHG_STATUS';
1161 p_status := FALSE;
1162 ELSIF p_interface.loan_chg_status IN ('A','R') AND p_interface.loan_status_code <> 'A' THEN
1163 indx := indx + 1;
1164 fnd_message.set_name('IGF','IGF_SL_LI_INVALID_CHG_STAT');
1165 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1166 p_status := FALSE;
1167 END IF;
1168 END IF;
1169
1170 --Validations for active_flag
1171 IF p_interface.active_flag IS NULL
1172 OR igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_interface.active_flag) IS NULL THEN
1173 indx := indx + 1;
1174 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ACTIVE_FLAG';
1175 p_status := FALSE;
1176 END IF;
1177
1178 --Validations for grade_level_code
1179 IF p_interface.grade_level_code IS NULL
1180 OR igf_ap_gen.get_lookup_meaning('IGF_AP_GRADE_LEVEL',p_interface.grade_level_code) IS NULL
1181 OR p_interface.grade_level_code = '0/1' THEN
1182 indx := indx + 1;
1183 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'GRADE_LEVEL_CODE';
1184 p_status := FALSE;
1185 END IF;
1186
1187 -- Validations for loan_approved_amt
1188 OPEN c_accepted_amt(p_award_id);
1189 FETCH c_accepted_amt INTO l_accepted_amt;
1190 CLOSE c_accepted_amt;
1191
1192 IF (p_interface.LOAN_STATUS_CODE = 'A' AND p_interface.loan_approved_amt IS NULL)
1193 OR NVL(p_interface.loan_approved_amt,0) < 0
1194 OR p_interface.loan_approved_amt <> l_accepted_amt.accepted_amt THEN
1195 indx := indx + 1;
1196 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'LOAN_APPROVED_AMT';
1197 p_status := FALSE;
1198 END IF;
1199
1200 -- Validations for orig_send_batch_id_txt
1201 IF p_interface.loan_status_code IN ('A') THEN
1202 IF p_interface.orig_send_batch_id_txt IS NULL THEN
1203 -- error out displaying the appropriate message
1204 indx := indx + 1;
1205 fnd_message.set_name('IGF','IGF_SL_INVALID_FLD');
1206 fnd_message.set_token('FIELD','ORIG_SEND_BATCH_ID_TXT');
1207 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get();
1208 p_status := FALSE;
1209 END IF;
1210 ELSE
1211 l_valid := is_batch_id_valid(p_interface.orig_send_batch_id_txt);
1212 IF (NOT l_valid ) THEN
1213 indx := indx + 1;
1214 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ORIG_SEND_BATCH_ID_TXT';
1215 p_status := FALSE;
1216 END IF;
1217 END IF;
1218
1219 -- validations for unsub_elig_for_depnt_code
1220 IF l_award_year_status <> 'O' AND p_interface.UNSUB_ELIG_FOR_DEPNT_CODE IS NOT NULL THEN
1221 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_DEP_UNSUB_ELIG',p_interface.UNSUB_ELIG_FOR_DEPNT_CODE) IS NULL THEN
1222 indx := indx + 1;
1223 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'UNSUB_ELIG_FOR_DEPNT_CODE';
1224 p_status := FALSE;
1225 END IF;
1226 END IF;
1227
1228 IF l_award_year_status = 'O' THEN
1229 IF p_interface.LOAN_STATUS_CODE = 'A' AND lv_fed_fund_code = 'DLU' THEN
1230 IF p_interface.UNSUB_ELIG_FOR_DEPNT_CODE IS NULL THEN
1231 indx := indx + 1;
1232 fnd_message.set_name('IGF','IGF_SL_UNSUB_ELIG_REQ');
1233 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1234 p_status := FALSE;
1235 ELSIF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_DEP_UNSUB_ELIG',p_interface.UNSUB_ELIG_FOR_DEPNT_CODE) IS NULL THEN
1236 indx := indx + 1;
1237 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'UNSUB_ELIG_FOR_DEPNT_CODE';
1238 p_status := FALSE;
1239 END IF;
1240 END IF;
1241
1242 IF lv_fed_fund_code IN ('DLP','DLS') THEN
1243 IF p_interface.UNSUB_ELIG_FOR_DEPNT_CODE IS NOT NULL THEN
1244 indx := indx + 1;
1245 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'UNSUB_ELIG_FOR_DEPNT_CODE';
1246 p_status := FALSE;
1247 END IF;
1248 END IF;
1249 END IF;
1250
1251 -- validations for orig_fee_perct_num
1252 IF (p_interface.orig_fee_perct_num IS NOT NULL AND p_interface.orig_fee_perct_num < 0) THEN
1253 indx := indx + 1;
1254 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ORIG_FEE_PERCT_NUM';
1255 p_status := FALSE;
1256 END IF;
1257
1258 -- validations for s_default_status_code
1259 IF l_award_year_status <> 'O' AND p_interface.S_DEFAULT_STATUS_CODE IS NOT NULL THEN
1260 IF igf_ap_gen.get_lookup_meaning('IGF_SL_S_DEFAULT_STATUS',p_interface.S_DEFAULT_STATUS_CODE) IS NULL
1261 OR p_interface.S_DEFAULT_STATUS_CODE = 'Y' THEN
1262 indx := indx + 1;
1263 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'S_DEFAULT_STATUS_CODE';
1264 p_status := FALSE;
1265 END IF;
1266 END IF;
1267
1268
1269 IF l_award_year_status = 'O' THEN
1270 IF p_interface.LOAN_STATUS_CODE = 'A' AND lv_fed_fund_code = 'DLP' THEN
1271 IF p_interface.S_DEFAULT_STATUS_CODE IS NULL THEN
1272 indx := indx + 1;
1273 fnd_message.set_name('IGF','IGF_SL_DEF_STAT_REQ');
1274 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1275 p_status := FALSE;
1276 ELSIF igf_ap_gen.get_lookup_meaning('IGF_SL_S_DEFAULT_STATUS',p_interface.S_DEFAULT_STATUS_CODE) IS NULL
1277 OR p_interface.S_DEFAULT_STATUS_CODE = 'Y' THEN
1278 indx := indx + 1;
1279 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'S_DEFAULT_STATUS_CODE';
1280 p_status := FALSE;
1281 END IF;
1282 END IF;
1283
1284
1285 IF lv_fed_fund_code IN ('DLU','DLS') THEN
1286 IF p_interface.S_DEFAULT_STATUS_CODE IS NOT NULL THEN
1287 indx := indx + 1;
1288 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'S_DEFAULT_STATUS_CODE';
1289 p_status := FALSE;
1290 END IF;
1291 END IF;
1292 END IF;
1293
1294 -- validation for pnote_accept_amt
1295 IF l_award_year_status = 'O' THEN
1296
1297 IF p_interface.LOAN_STATUS_CODE = 'A' AND lv_fed_fund_code = 'DLP' THEN
1298 IF p_interface.PNOTE_ACCEPT_AMT IS NULL THEN
1299 indx := indx + 1;
1300 fnd_message.set_name('IGF','IGF_SL_PNOTE_ACCEPT_AMT_REQ');
1301 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1302 p_status := FALSE;
1303 ELSIF NVL(p_interface.PNOTE_ACCEPT_AMT,0) < 0 THEN
1304 indx := indx + 1;
1305 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_ACCEPT_AMT';
1306 p_status := FALSE;
1307 END IF;
1308 END IF;
1309 END IF;
1310
1311 -- validation for orig_batch_date
1312 IF (p_interface.LOAN_STATUS_CODE = 'A' AND p_interface.ORIG_BATCH_DATE IS NULL)
1313 OR ((p_interface.ORIG_BATCH_DATE IS NOT NULL) AND (NOT Val_Date(TO_CHAR(p_interface.ORIG_BATCH_DATE,'YYYYMMDD')))) THEN
1314 indx := indx + 1;
1315 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ORIG_BATCH_DATE';
1316 p_status := FALSE;
1317 END IF;
1318
1319 -- Validations for unsub_elig_for_heal_code
1320 IF l_award_year_status <> 'O' AND p_interface.UNSUB_ELIG_FOR_HEAL_CODE IS NOT NULL THEN
1321 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_HP_UNSUB_ELIG',p_interface.UNSUB_ELIG_FOR_HEAL_CODE) IS NULL THEN
1322 indx := indx + 1;
1323 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'UNSUB_ELIG_FOR_HEAL_CODE';
1324 p_status := FALSE;
1325 END IF;
1326 END IF;
1327 IF l_award_year_status = 'O' THEN
1328 IF p_interface.LOAN_STATUS_CODE = 'A' AND lv_fed_fund_code = 'DLU' THEN
1329 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_HP_UNSUB_ELIG',p_interface.UNSUB_ELIG_FOR_HEAL_CODE) IS NULL THEN
1330 indx := indx + 1;
1331 fnd_message.set_name('IGF','IGF_SL_UNSUB_ELG_HEAL_REQ');
1332 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1333 p_status := FALSE;
1334 END IF;
1335 ELSIF lv_fed_fund_code = 'DLU' AND p_interface.UNSUB_ELIG_FOR_HEAL_CODE IS NOT NULL THEN
1336 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_HP_UNSUB_ELIG',p_interface.UNSUB_ELIG_FOR_HEAL_CODE) IS NULL THEN
1337 indx := indx + 1;
1338 fnd_message.set_name('IGF','IGF_SL_UNSUB_ELG_HEAL_REQ');
1339 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1340 p_status := FALSE;
1341 END IF;
1342 END IF;
1343
1344
1345 IF lv_fed_fund_code IN ('DLP','DLS') THEN
1346 IF p_interface.UNSUB_ELIG_FOR_HEAL_CODE IS NOT NULL THEN
1347 indx := indx + 1;
1348 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'UNSUB_ELIG_FOR_HEAL_CODE';
1349 p_status := FALSE;
1350 END IF;
1351 END IF;
1352 END IF;
1353
1354 -- Validations for disclosure_print_ind_code
1355 IF p_interface.DISCLOSURE_PRINT_IND_CODE IS NOT NULL THEN
1356 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DISCLOSURE_PRINT_IND',p_interface.DISCLOSURE_PRINT_IND_CODE) IS NULL
1357 OR p_interface.DISCLOSURE_PRINT_IND_CODE IN ('N','Z') THEN
1358 indx := indx + 1;
1359 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'DISCLOSURE_PRINT_IND_CODE';
1360 p_status := FALSE;
1361 END IF;
1362 END IF;
1363
1364 -- Validations for credit_decision_date
1365 IF l_award_year_status <> 'O' AND p_interface.CREDIT_DECISION_DATE IS NOT NULL THEN
1366 IF NOT Val_Date(TO_CHAR(p_interface.CREDIT_DECISION_DATE,'YYYYMMDD')) THEN
1367 indx := indx + 1;
1368 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_DECISION_DATE';
1369 p_status := FALSE;
1370 END IF;
1371 END IF;
1372
1373 IF l_award_year_status = 'O' THEN
1374 IF lv_fed_fund_code = 'DLP' THEN
1375 IF p_interface.loan_status_code = 'A' AND p_interface.CREDIT_DECISION_DATE IS NULL THEN
1376 indx := indx + 1;
1377 fnd_message.set_name('IGF','IGF_SL_CREDIT_DECS_DATE_REQ');
1378 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1379 p_status := FALSE;
1380 ELSIF p_interface.CREDIT_DECISION_DATE IS NOT NULL THEN
1381 IF NOT Val_Date(TO_CHAR(p_interface.CREDIT_DECISION_DATE,'YYYYMMDD')) THEN
1382 indx := indx + 1;
1383 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_DECISION_DATE';
1384 p_status := FALSE;
1385 END IF;
1386 END IF;
1387 END IF;
1388 IF lv_fed_fund_code IN ('DLU','DLS') THEN
1389 IF p_interface.CREDIT_DECISION_DATE IS NOT NULL THEN
1390 indx := indx + 1;
1391 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_DECISION_DATE';
1392 p_status := FALSE;
1393 END IF;
1394 END IF;
1395 END IF;
1396
1397 -- -- Validations for credit_override_code
1398 IF l_award_year_status <> 'O' AND p_interface.CREDIT_OVERRIDE_CODE IS NOT NULL THEN
1399 IF igf_ap_gen.get_lookup_meaning('IGF_SL_CREDIT_OVERRIDE',p_interface.CREDIT_OVERRIDE_CODE) IS NULL
1400 OR p_interface.CREDIT_OVERRIDE_CODE IN ('01','05','10','15','20','25','30','35') THEN
1401 indx := indx + 1;
1402 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_OVERRIDE_CODE';
1403 p_status := FALSE;
1404 END IF;
1405 END IF;
1406 IF l_award_year_status = 'O' THEN
1407 IF p_interface.LOAN_STATUS_CODE = 'A' AND lv_fed_fund_code = 'DLP' THEN
1408 IF p_interface.CREDIT_OVERRIDE_CODE IS NULL THEN
1409 indx := indx + 1;
1410 fnd_message.set_name('IGF','IGF_SL_CREDIT_OVERRIDE_REQ');
1411 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get;
1412 p_status := FALSE;
1413 ELSIF igf_ap_gen.get_lookup_meaning('IGF_SL_CREDIT_OVERRIDE',p_interface.CREDIT_OVERRIDE_CODE) IS NULL
1414 OR p_interface.CREDIT_OVERRIDE_CODE IN ('01','05','10','15','20','25','30','35') THEN
1415 indx := indx + 1;
1416 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_OVERRIDE_CODE';
1417 p_status := FALSE;
1418
1419 END IF;
1420 END IF;
1421
1422 IF lv_fed_fund_code IN ('DLU','DLS') THEN
1423 IF p_interface.CREDIT_OVERRIDE_CODE IS NOT NULL THEN
1424 indx := indx + 1;
1425 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CREDIT_OVERRIDE_CODE';
1426 p_status := FALSE;
1427 END IF;
1428 END IF;
1429 END IF;
1430
1431 -- Validations for pnote_id_txt
1432 IF p_interface.loan_status_code = 'N' AND p_interface.PNOTE_ID_TXT IS NOT NULL THEN
1433 indx := indx + 1;
1434 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_ID_TXT';
1435 p_status := FALSE;
1436
1437 ELSE
1438 IF g_award_year = '3'
1439 AND lv_fed_fund_code = 'DLP'
1440 AND p_interface.PNOTE_ID_TXT IS NOT NULL THEN
1441
1442 indx := indx + 1;
1443 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_ID_TXT';
1444 p_status := FALSE;
1445 ELSIF (g_award_year IN ('4','5')
1446 OR lv_fed_fund_code IN ('DLS','DLU'))
1447 AND p_interface.PNOTE_ID_TXT IS NOT NULL THEN
1448 l_valid := is_pnote_id_valid(p_interface.PNOTE_ID_TXT);
1449 IF NOT l_valid THEN
1450 indx := indx + 1;
1451 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_ID_TXT';
1452 p_status := FALSE;
1453 END IF;
1454 END IF;
1455 END IF;
1456
1457 -- -- Validations for pnote_batch_seq_num_txt
1458 IF p_interface.loan_status_code = 'N'
1459 AND p_interface.PNOTE_BATCH_SEQ_NUM_TXT IS NOT NULL THEN
1460 indx := indx + 1;
1461 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_BATCH_SEQ_NUM_TXT';
1462 p_status := FALSE;
1463 ELSIF p_interface.PNOTE_BATCH_SEQ_NUM_TXT IS NOT NULL THEN
1464 l_valid := is_pnote_batch_id_valid(p_interface.PNOTE_BATCH_SEQ_NUM_TXT);
1465 IF NOT l_valid THEN
1466 indx := indx + 1;
1467 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_BATCH_SEQ_NUM_TXT';
1468 p_status := FALSE;
1469 END IF;
1470 END IF;
1471
1472 -- validations for pnote_status_code
1473 IF p_interface.PNOTE_STATUS_CODE IS NULL THEN
1474 indx := indx + 1;
1475 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_CODE';
1476 p_status := FALSE;
1477 ELSE
1478 IF p_interface.LOAN_STATUS_CODE = 'N' THEN
1479 IF p_interface.PNOTE_STATUS_CODE <> 'N' THEN
1480 indx := indx + 1;
1481 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_CODE';
1482 p_status := FALSE;
1483 END IF;
1484 ELSIF p_interface.LOAN_STATUS_CODE = 'G' THEN
1485 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_PNOTE_STATUS',p_interface.PNOTE_STATUS_CODE) IS NULL
1486 OR p_interface.PNOTE_STATUS_CODE IN ('A','C','F','I','R','X') THEN
1487 indx := indx + 1;
1488 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_CODE';
1489 p_status := FALSE;
1490 END IF;
1491 ELSIF p_interface.LOAN_STATUS_CODE = 'A' THEN
1492 IF igf_ap_gen.get_lookup_meaning('IGF_SL_DL_PNOTE_STATUS',p_interface.PNOTE_STATUS_CODE) IS NULL THEN
1493 indx := indx + 1;
1494 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_CODE';
1495 p_status := FALSE;
1496 END IF;
1497 END IF;
1498 END IF;
1499
1500 -- Validatios for pnote_status_type
1501 IF ( p_interface.loan_status_code = 'N' OR lv_fed_fund_code = 'DLP')
1502 AND p_interface.PNOTE_STATUS_TYPE IS NOT NULL THEN
1503 indx := indx + 1;
1504 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_TYPE';
1505 p_status := FALSE;
1506 ELSIF p_interface.PNOTE_STATUS_TYPE IS NOT NULL THEN
1507 IF igf_ap_gen.get_lookup_meaning('IGF_SL_PNOTE_TYPE',p_interface.PNOTE_STATUS_TYPE) IS NULL THEN
1508 indx := indx + 1;
1509 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_STATUS_TYPE';
1510 p_status := FALSE;
1511 END IF;
1512 END IF;
1513
1514 -- Validatios for pnote_indicator_code
1515 IF ( p_interface.loan_status_code = 'N' OR ( g_award_year = '3' AND lv_fed_fund_code = 'DLP'))
1516 AND p_interface.PNOTE_INDICATOR_CODE IS NOT NULL THEN
1517 indx := indx + 1;
1518 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_INDICATOR_CODE';
1519 p_status := FALSE;
1520 ELSIF p_interface.PNOTE_INDICATOR_CODE NOT IN ('Y','N') THEN
1521 indx := indx + 1;
1522 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_INDICATOR_CODE';
1523 p_status := FALSE;
1524 END IF;
1525
1526 -- Validatios for pnote_print_ind_code
1527 IF p_interface.loan_status_code = 'A' AND p_interface.PNOTE_PRINT_IND_CODE IS NULL THEN
1528 indx := indx + 1;
1529 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_PRINT_IND_CODE';
1530 p_status := FALSE;
1531 ELSIF p_interface.PNOTE_PRINT_IND_CODE IS NOT NULL THEN
1532 IF g_award_year = '3' THEN
1533 IF igf_ap_gen.get_lookup_meaning('IGF_SL_PNOTE_PRINT_IND',p_interface.PNOTE_PRINT_IND_CODE) IS NULL
1534 OR p_interface.PNOTE_PRINT_IND_CODE = 'V' THEN
1535 indx := indx + 1;
1536 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_PRINT_IND_CODE';
1537 p_status := FALSE;
1538 END IF;
1539 ELSIF g_award_year IN ('4','5') THEN
1540 IF igf_ap_gen.get_lookup_meaning('IGF_SL_PNOTE_PRINT_IND',p_interface.PNOTE_PRINT_IND_CODE) IS NULL THEN
1541 indx := indx + 1;
1542 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'PNOTE_PRINT_IND_CODE';
1543 p_status := FALSE;
1544 END IF;
1545 END IF;
1546 END IF;
1547
1548 -- Validatios for mpn_acknowledgement_date
1549 IF p_interface.PNOTE_STATUS_CODE IN ('A','I','C') THEN
1550 IF NOT Val_Date(TO_CHAR(p_interface.MPN_ACKNOWLEDGEMENT_DATE,'YYYYMMDD')) THEN
1551 indx := indx + 1;
1552 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'MPN_ACKNOWLEDGEMENT_DATE';
1553 p_status := FALSE;
1554 END IF;
1555 END IF;
1556
1557 -- validations for endorser_amount
1558 IF l_award_year_status = 'O'
1559 AND lv_fed_fund_code = 'DLP'
1560 AND p_interface.endorser_amount IS NOT NULL THEN
1561 IF NVL(p_interface.endorser_amount,0) < 0 THEN
1562 indx := indx + 1;
1563 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ENDORSER_AMOUNT';
1564 p_status := FALSE;
1565 END IF;
1566 ELSIF (l_award_year_status = 'O'
1567 AND lv_fed_fund_code IN ('DLS','DLU')
1568 AND p_interface.endorser_amount IS NOT NULL)
1569 OR (g_award_year = '3'
1570 AND p_interface.endorser_amount IS NOT NULL) THEN
1571 indx := indx + 1;
1572 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'ENDORSER_AMOUNT';
1573 p_status := FALSE;
1574 END IF;
1575
1576 -- Validations for cr_desc_batch_id_txt
1577 l_valid := is_credit_batch_id_valid(p_interface.cr_desc_batch_id_txt);
1578 IF p_interface.loan_status_code = 'A' AND lv_fed_fund_code = 'DLP' THEN
1579 IF p_interface.cr_desc_batch_id_txt IS NULL
1580 OR (NOT l_valid ) THEN
1581 indx := indx + 1;
1582 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CR_DESC_BATCH_ID_TXT';
1583 p_status := FALSE;
1584 END IF;
1585 ELSIF p_interface.loan_status_code IN ('N','R')
1586 AND lv_fed_fund_code = 'DLP'
1587 AND p_interface.cr_desc_batch_id_txt IS NOT NULL
1588 AND (NOT l_valid ) THEN
1589 indx := indx + 1;
1590 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CR_DESC_BATCH_ID_TXT';
1591 p_status := FALSE;
1592 ELSIF lv_fed_fund_code IN ('DLS','DLU')
1593 AND p_interface.cr_desc_batch_id_txt IS NOT NULL THEN
1594 indx := indx + 1;
1595 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'CR_DESC_BATCH_ID_TXT';
1596 p_status := FALSE;
1597 END IF;
1598
1599 -- Validations for s_default_status_code
1600 IF p_interface.S_DEFAULT_STATUS_CODE IS NOT NULL THEN
1601 IF igf_ap_gen.get_lookup_meaning('IGF_SL_S_DEFAULT_STATUS',p_interface.S_DEFAULT_STATUS_CODE) IS NULL THEN
1602 indx := indx + 1;
1603 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'S_DEFAULT_STATUS_CODE';
1604 p_status := FALSE;
1605 END IF;
1606 END IF;
1607
1608 -- Validations for p_default_status_code
1609 IF p_interface.P_DEFAULT_STATUS_CODE IS NOT NULL THEN
1610 IF igf_ap_gen.get_lookup_meaning('IGF_SL_P_DEFAULT_STATUS',p_interface.P_DEFAULT_STATUS_CODE) IS NULL THEN
1611 indx := indx + 1;
1612 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || g_error_string|| ' ' || 'P_DEFAULT_STATUS_CODE';
1613 p_status := FALSE;
1614 END IF;
1615 END IF;
1616
1617 -- If the loan status is 'Acknowlegded' then p_interface.orig_send_batch_id_txt
1618 -- has to be not null to be inserted in the igf_sl_dl_lor_resp ,igf_sl_dl_batch table
1619 IF p_interface.loan_status_code IN ('A') THEN
1620 IF p_interface.orig_acknowledgement_date IS NULL THEN
1621 indx := indx + 1;
1622 fnd_message.set_name('IGF','IGF_SL_INVALID_FLD');
1623 fnd_message.set_token('FIELD','ORIG_ACKNOWLEDGEMENT_DATE');
1624 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get();
1625 p_status := FALSE;
1626 END IF;
1627 END IF;
1628
1629 IF p_interface.credit_status IS NOT NULL THEN
1630 IF igf_ap_gen.get_lookup_meaning('IGF_SL_CREDIT_STATUS',p_interface.credit_status) IS NULL THEN
1631 indx := indx + 1;
1632 fnd_message.set_name('IGF','IGF_AP_INV_FLD_VAL');
1633 fnd_message.set_token('FIELD','CREDIT_STATUS');
1634 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get();
1635 p_status := FALSE;
1636 ELSE
1637 IF p_interface.loan_status_code = 'A' AND p_interface.credit_status = 'D' THEN
1638 indx := indx + 1;
1639 fnd_message.set_name('IGF','IGF_SL_DEC_LOAN_CRDT');
1640 p_igf_sl_msg_table(indx).msg_text := RPAD(l_error,12) || fnd_message.get();
1641 p_status := FALSE;
1642 END IF;
1643 END IF;
1644 END IF;
1645
1646 EXCEPTION
1647
1648 WHEN others THEN
1649 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1650 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_li_imp_pkg.validate_loan_orig_int.exception','Exception: '||SQLERRM);
1651 END IF;
1652 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1653 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.VALIDATE_LOAN_ORIG_INT');
1654 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
1655
1656 RAISE IMPORT_ERROR;
1657
1658 END validate_loan_orig_int;
1659
1660
1661
1662 PROCEDURE loans_insert_row(p_interface IN c_interface%ROWTYPE,
1663 p_award_id IN NUMBER)
1664 AS
1665 /*
1666 || Created By : rasahoo
1667 || Created On : 03-June-2003
1668 || Purpose : Inserts legacy data into loans Table .
1669 || Change History :
1670 || Who When What
1671 || (reverse chronological order - newest change first)
1672 */
1673 ln_rowid ROWID;
1674 BEGIN
1675 ln_rowid := NULL;
1676
1677 igf_sl_loans_pkg.insert_row (
1678 x_mode => 'R',
1679 x_rowid => ln_rowid,
1680 x_loan_id => ln_loan_id,
1681 x_award_id => p_award_id,
1682 x_seq_num => p_interface.loan_seq_num,
1683 x_loan_number => p_interface.loan_number_txt ,
1684 x_loan_per_begin_date => p_interface.loan_per_begin_date,
1685 x_loan_per_end_date => p_interface.loan_per_end_date,
1686 x_loan_status => p_interface.loan_status_code,
1687 x_loan_status_date => p_interface.loan_status_date,
1688 x_loan_chg_status => p_interface.loan_chg_status,
1689 x_loan_chg_status_date => p_interface.loan_chg_status_date,
1690 x_active => p_interface.active_flag,
1691 x_active_date => p_interface.active_date,
1692 x_borw_detrm_code => NULL,
1693 x_legacy_record_flag => 'Y',
1694 x_external_loan_id_txt => NULL
1695 );
1696 EXCEPTION
1697 WHEN OTHERS THEN
1698 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1699 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_INSERT_ROW');
1700 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
1701
1702 RAISE IMPORT_ERROR;
1703 END loans_insert_row;
1704
1705 PROCEDURE loans_orig_insert_row(p_interface IN c_interface%ROWTYPE)
1706 AS
1707 /*
1708 || Created By : rasahoo
1709 || Created On : 03-June-2003
1710 || Purpose : Inserts legacy data into loans origination Table .
1711 || Change History :
1712 || Who When What
1713 -----------------------------------------------------------------------------------
1714 bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
1715 a) Impact of adding the relationship_cd
1716 in igf_sl_lor_all table and obsoleting
1717 BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
1718 GUARANTOR_ID, DUNS_GUARNT_ID,
1719 LENDER_ID, DUNS_LENDER_ID
1720 LEND_NON_ED_BRC_ID, RECIPIENT_ID
1721 RECIPIENT_TYPE,DUNS_RECIP_ID
1722 RECIP_NON_ED_BRC_ID columns.
1723 -----------------------------------------------------------------------------------
1724 || veramach 23-SEP-2003 Bug 3104228:
1725 || 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
1726 || cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
1727 || p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
1728 || chg_batch_id,appl_send_error_codes from igf_sl_lor
1729 || (reverse chronological order - newest change first)
1730 */
1731 ln_rowid ROWID;
1732 l_orig_status VARCHAR2(1);
1733 BEGIN
1734 l_orig_status := NULL;
1735 IF p_interface.loan_status_code = 'A' THEN
1736 l_orig_status := 'B';
1737 END IF;
1738
1739 ln_rowid := NULL;
1740 igf_sl_lor_pkg.insert_row (
1741 x_mode => 'R',
1742 x_rowid => ln_rowid,
1743 X_origination_id => ln_origination_id,
1744 X_loan_id => ln_loan_id,
1745 X_sch_cert_date => p_interface.sch_cert_date,
1746 X_orig_status_flag => l_orig_status,
1747 X_orig_batch_id => p_interface.orig_send_batch_id_txt,
1748 X_orig_batch_date => p_interface.orig_batch_date,
1749 X_chg_batch_id => NULL,
1750 X_orig_ack_date => p_interface.orig_acknowledgement_date,
1751 X_credit_override => p_interface.credit_override_code,
1752 X_credit_decision_date => p_interface.credit_decision_date,
1753 X_req_serial_loan_code => NULL,
1754 X_act_serial_loan_code => NULL,
1755 X_pnote_delivery_code => NULL,
1756 X_pnote_status => p_interface.pnote_status_code,
1757 x_pnote_status_date => p_interface.pnote_accept_date,
1758 x_pnote_id => p_interface.pnote_id_txt,
1759 x_pnote_print_ind => p_interface.pnote_print_ind_code,
1760 x_pnote_accept_amt => p_interface.pnote_accept_amt,
1761 X_pnote_accept_date => p_interface.pnote_accept_date,
1762 X_unsub_elig_for_heal => p_interface.unsub_elig_for_heal_code,
1763 x_disclosure_print_ind => p_interface.disclosure_print_ind_code,
1764 x_orig_fee_perct => p_interface.orig_fee_perct_num,
1765 x_borw_confirm_ind => NULL,
1766 X_borw_interest_ind => NULL,
1767 X_borw_outstd_loan_code => NULL,
1768 X_unsub_elig_for_depnt => p_interface.unsub_elig_for_depnt_code,
1769 X_guarantee_amt => NULL,
1770 X_guarantee_date => NULL,
1771 X_guarnt_amt_redn_code => NULL,
1772 X_guarnt_status_code => NULL,
1773 X_guarnt_status_date => NULL,
1774 X_lend_apprv_denied_code => NULL,
1775 X_lend_apprv_denied_date => NULL,
1776 X_lend_status_code => NULL,
1777 X_lend_status_date => NULL,
1778 X_guarnt_adj_ind => NULL,
1779 X_grade_level_code => p_interface.grade_level_code,
1780 X_enrollment_code => NULL,
1781 X_anticip_compl_date => NULL,
1782 X_borw_lender_id => NULL,
1783 X_duns_borw_lender_id => NULL,
1784 X_guarantor_id => NULL,
1785 X_duns_guarnt_id => NULL,
1786 X_prc_type_code => NULL,
1787 X_cl_seq_number => NULL,
1788 X_last_resort_lender => NULL,
1789 X_lender_id => NULL,
1790 X_duns_lender_id => NULL,
1791 X_lend_non_ed_brc_id => NULL,
1792 X_recipient_id => NULL,
1793 X_recipient_type => NULL,
1794 X_duns_recip_id => NULL,
1795 X_recip_non_ed_brc_id => NULL,
1796 X_rec_type_ind => NULL,
1797 X_cl_loan_type => NULL,
1798 X_cl_rec_status => NULL,
1799 X_cl_rec_status_last_update => NULL,
1800 X_alt_prog_type_code => NULL,
1801 X_alt_appl_ver_code => NULL,
1802 X_mpn_confirm_code => NULL,
1803 X_resp_to_orig_code => NULL,
1804 X_appl_loan_phase_code => NULL,
1805 X_appl_loan_phase_code_chg => NULL,
1806 X_appl_send_error_codes => NULL,
1807 X_tot_outstd_stafford => NULL,
1808 X_tot_outstd_plus => NULL,
1809 X_alt_borw_tot_debt => NULL,
1810 X_act_interest_rate => NULL,
1811 X_service_type_code => NULL,
1812 X_rev_notice_of_guarnt => NULL,
1813 X_sch_refund_amt => NULL,
1814 X_sch_refund_date => NULL,
1815 X_uniq_layout_vend_code => NULL,
1816 X_uniq_layout_ident_code => NULL,
1817 X_p_person_id => l_b_person_id,
1818 X_p_ssn_chg_date => NULL,
1819 X_p_dob_chg_date => NULL,
1820 X_p_permt_addr_chg_date => NULL,
1821 X_p_default_status => p_interface.p_default_status_code,
1822 X_p_signature_code => NULL,
1823 X_p_signature_date => NULL,
1824 X_s_ssn_chg_date => NULL,
1825 X_s_dob_chg_date => NULL,
1826 X_s_permt_addr_chg_date => NULL,
1827 X_s_local_addr_chg_date => NULL,
1828 X_s_default_status => p_interface.s_default_status_code,
1829 X_s_signature_code => NULL,
1830 X_pnote_batch_id => p_interface.pnote_batch_seq_num_txt ,
1831 X_pnote_ack_date => p_interface.mpn_acknowledgement_date,
1832 X_pnote_mpn_ind => p_interface.pnote_indicator_code,
1833 X_elec_mpn_ind => p_interface.pnote_status_type,
1834 X_borr_sign_ind => NULL,
1835 X_stud_sign_ind => NULL,
1836 X_borr_credit_auth_code => NULL,
1837 x_relationship_cd => NULL,
1838 x_interest_rebate_percent_num => NULL,
1839 x_cps_trans_num => p_interface.transaction_num,
1840 x_atd_entity_id_txt => p_interface.atd_entity_id_txt,
1841 x_rep_entity_id_txt => p_interface.rep_entity_id_txt,
1842 x_crdt_decision_status => p_interface.credit_status,
1843 x_note_message => NULL,
1844 x_book_loan_amt => NULL,
1845 x_book_loan_amt_date => NULL,
1846 x_pymt_servicer_amt => NULL,
1847 x_pymt_servicer_date => NULL,
1848 x_external_loan_id_txt => NULL,
1849 x_alt_approved_amt => NULL,
1850 x_flp_approved_amt => NULL,
1851 x_fls_approved_amt => NULL,
1852 x_flu_approved_amt => NULL,
1853 x_guarantor_use_txt => NULL,
1854 x_lender_use_txt => NULL,
1855 x_loan_app_form_code => NULL,
1856 x_reinstatement_amt => NULL,
1857 x_requested_loan_amt => NULL,
1858 x_school_use_txt => NULL,
1859 x_deferment_request_code => NULL,
1860 x_eft_authorization_code => NULL,
1861 x_actual_record_type_code => NULL,
1862 x_override_grade_level_code => NULL,
1863 x_b_alien_reg_num_txt => NULL,
1864 x_esign_src_typ_cd => NULL,
1865 x_acad_begin_date => NULL,
1866 x_acad_end_date => NULL);
1867
1868 EXCEPTION
1869 WHEN OTHERS THEN
1870 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1871 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_ORIG_INSERT_ROW');
1872 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
1873
1874 RAISE IMPORT_ERROR;
1875 END loans_orig_insert_row;
1876
1877 PROCEDURE loans_orig_loc_insert_row(p_interface IN c_interface%ROWTYPE,
1878 p_award_id IN NUMBER,
1879 p_base_id IN NUMBER,
1880 p_fed_fund IN VARCHAR2)
1881 AS
1882 /*
1883 || Created By : rasahoo
1884 || Created On : 03-June-2003
1885 || Purpose : Inserts legacy data into loans origination loc Table .
1886 || Change History :
1887 || Who When What
1888 -----------------------------------------------------------------------------------
1889 pssahni 28-Oct-2004 Bug 3416863 FA149 COD-XML
1890 Added columns x_award_id, x_base_id, x_document_id_txt,
1891 x_loan_key_num, x_interest_rebate_percent_num, x_fin_award_year,
1892 x_cps_trans_num, x_atd_entity_id_txt, x_rep_entity_id_txt,
1893 x_source_entity_id_txt, x_pymt_servicer_amt, x_pymt_servicer_date,
1894 x_book_loan_amt, x_book_loan_amt_date, x_s_chg_birth_date,
1895 x_s_chg_ssn, x_s_chg_last_name, x_b_chg_birth_date, x_b_chg_ssn,
1896 x_b_chg_last_name, x_note_message, x_full_resp_code, x_s_permt_county,
1897 x_b_permt_county, x_s_permt_country, x_b_permt_country, x_crdt_decision_status
1898 -----------------------------------------------------------------------------------
1899 bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
1900 The DUNS_BORW_LENDER_ID
1901 DUNS_GUARNT_ID
1902 DUNS_LENDER_ID
1903 DUNS_RECIP_ID columns are osboleted from the
1904 igf_sl_lor_loc_all table.
1905 -----------------------------------------------------------------------------------
1906 || veramach 23-SEP-2003 Bug 3104228:
1907 || 1. Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
1908 || cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
1909 || p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
1910 || chg_batch_id from igf_sl_lor _loc
1911 || veramach 16-SEP-2003 FA 122 loan enhancements
1912 || 1. c_loan_dtls does not select borrower information from igf_sl_lor_dtls_v
1913 || 2. igf_sl_gen.get_person_details is now used to get borrower information
1914 || (reverse chronological order - newest change first)
1915 */
1916
1917 CURSOR c_award_amt IS
1918 SELECT offered_amt,
1919 accepted_amt
1920 FROM igf_aw_award_all
1921 WHERE award_id = p_award_id;
1922
1923 l_award_amt c_award_amt%ROWTYPE;
1924
1925 CURSOR c_loan_dtls(p_loan_id NUMBER,
1926 cp_origination_id NUMBER) IS
1927 SELECT loans.row_id,
1928 loans.loan_id,
1929 lor.s_default_status,
1930 lor.p_default_status,
1931 lor.p_person_id,
1932 fabase.person_id student_id
1933 FROM igf_sl_loans loans,
1934 igf_sl_lor lor,
1935 igf_aw_award awd,
1936 igf_ap_fa_base_rec fabase
1937 WHERE fabase.base_id = awd.base_id
1938 AND loans.award_id = awd.award_id
1939 AND loans.loan_id = lor.loan_id
1940 AND loans.loan_id = p_loan_id;
1941
1942
1943 loan_rec c_loan_dtls%ROWTYPE;
1944
1945 student_dtl_rec igf_sl_gen.person_dtl_rec;
1946 student_dtl_cur igf_sl_gen.person_dtl_cur;
1947
1948 parent_dtl_rec igf_sl_gen.person_dtl_rec;
1949 parent_dtl_cur igf_sl_gen.person_dtl_cur;
1950
1951 CURSOR cur_isir_depend_status (cp_person_id NUMBER)
1952 IS
1953 SELECT isir.dependency_status
1954 FROM igf_ap_fa_base_rec fabase,igf_ap_isir_matched isir
1955 WHERE isir.base_id = fabase.base_id
1956 AND isir.payment_isir = 'Y'
1957 AND isir.system_record_type = 'ORIGINAL'
1958 AND fabase.person_id = cp_person_id;
1959
1960 l_student_license cur_isir_depend_status%ROWTYPE;
1961
1962 ln_row_id ROWID;
1963 lv_p_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
1964 lv_s_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
1965 lv_s_license_number igf_ap_isir_matched.driver_license_number%TYPE;
1966 lv_s_license_state igf_ap_isir_matched.driver_license_state%TYPE;
1967 lv_s_citizenship_status VARCHAR2(30);
1968 lv_alien_reg_num igf_ap_isir_matched.alien_reg_number%TYPE;
1969 lv_dependency_status igf_ap_isir_matched.dependency_status%TYPE;
1970 lv_s_legal_res_date igf_ap_isir_matched.s_legal_resd_date%TYPE;
1971 lv_s_legal_res_state igf_ap_isir_matched.s_state_legal_residence%TYPE;
1972
1973 BEGIN
1974
1975 ln_row_id := NULL;
1976
1977 OPEN c_award_amt;
1978 FETCH c_award_amt INTO l_award_amt;
1979 CLOSE c_award_amt;
1980
1981 OPEN c_loan_dtls(ln_loan_id,ln_origination_id);
1982 FETCH c_loan_dtls INTO loan_rec;
1983 igf_sl_gen.get_person_details(loan_rec.student_id,student_dtl_cur);
1984 FETCH student_dtl_cur INTO student_dtl_rec;
1985 igf_sl_gen.get_person_details(loan_rec.p_person_id,parent_dtl_cur);
1986 FETCH parent_dtl_cur INTO parent_dtl_rec;
1987
1988 CLOSE c_loan_dtls;
1989 CLOSE student_dtl_cur;
1990 CLOSE parent_dtl_cur;
1991
1992 OPEN cur_isir_depend_status(loan_rec.student_id);
1993 FETCH cur_isir_depend_status INTO lv_dependency_status;
1994 CLOSE cur_isir_depend_status;
1995
1996 lv_s_permt_phone := igf_sl_gen.get_person_phone(loan_rec.student_id);
1997 lv_p_permt_phone := igf_sl_gen.get_person_phone(loan_rec.p_person_id);
1998
1999 --Code added for bug 3603289 start
2000 lv_s_license_number := student_dtl_rec.p_license_num;
2001 lv_s_license_state := student_dtl_rec.p_license_state;
2002 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
2003 lv_alien_reg_num := student_dtl_rec.p_alien_reg_num;
2004 lv_s_legal_res_date := student_dtl_rec.p_legal_res_date;
2005 lv_s_legal_res_state := student_dtl_rec.p_state_of_legal_res;
2006 --Code added for bug 3603289 end
2007
2008 igf_sl_lor_loc_pkg.insert_row (
2009 x_mode => 'R',
2010 x_rowid => ln_row_id,
2011 x_loan_id => ln_loan_id,
2012 x_origination_id => ln_origination_id,
2013 x_loan_number => p_interface.loan_number_txt,
2014 x_loan_type => p_fed_fund,
2015 x_loan_amt_offered => l_award_amt.offered_amt ,
2016 x_loan_amt_accepted => l_award_amt.accepted_amt ,
2017 x_loan_per_begin_date => p_interface.loan_per_begin_date,
2018 x_loan_per_end_date => p_interface.loan_per_end_date,
2019 x_acad_yr_begin_date => NULL,
2020 x_acad_yr_end_date => NULL,
2021 x_loan_status => p_interface.loan_status_code,
2022 x_loan_status_date => p_interface.loan_status_date,
2023 x_loan_chg_status => p_interface.loan_chg_status,
2024 x_loan_chg_status_date => p_interface.loan_chg_status_date,
2025 x_req_serial_loan_code => NULL,
2026 x_act_serial_loan_code => NULL,
2027 x_active => p_interface.active_flag,
2028 x_active_date => p_interface.active_date,
2029 x_sch_cert_date => p_interface.sch_cert_date,
2030 x_orig_status_flag => NULL,
2031 x_orig_batch_id => p_interface.orig_send_batch_id_txt,
2032 x_orig_batch_date => p_interface.orig_batch_date,
2033 x_chg_batch_id => NULL,
2034 x_orig_ack_date => p_interface.orig_acknowledgement_date,
2035 x_credit_override => p_interface.credit_override_code,
2036 x_credit_decision_date => p_interface.credit_decision_date,
2037 x_pnote_delivery_code => NULL,
2038 x_pnote_status => p_interface.pnote_status_code,
2039 x_pnote_status_date => NULL,
2040 x_pnote_id => p_interface.pnote_id_txt,
2041 x_pnote_print_ind => p_interface.pnote_print_ind_code,
2042 x_pnote_accept_amt => p_interface.pnote_accept_amt,
2043 x_pnote_accept_date => p_interface.pnote_accept_date ,
2044 x_p_signature_code => NULL,
2045 x_p_signature_date => NULL,
2046 x_s_signature_code => NULL,
2047 x_unsub_elig_for_heal => p_interface.unsub_elig_for_heal_code,
2048 x_disclosure_print_ind => p_interface.disclosure_print_ind_code,
2049 x_orig_fee_perct => p_interface.orig_fee_perct_num,
2050 x_borw_confirm_ind => NULL,
2051 x_borw_interest_ind => NULL,
2052 x_unsub_elig_for_depnt => p_interface.unsub_elig_for_depnt_code,
2053 x_guarantee_amt => NULL,
2054 x_guarantee_date => NULL,
2055 x_guarnt_adj_ind => NULL,
2056 x_guarnt_amt_redn_code => NULL,
2057 x_guarnt_status_code => NULL,
2058 x_guarnt_status_date => NULL,
2059 x_lend_apprv_denied_code => NULL,
2060 x_lend_apprv_denied_date => NULL,
2061 x_lend_status_code => NULL,
2062 x_lend_status_date => NULL,
2063 x_grade_level_code => p_interface.grade_level_code,
2064 x_enrollment_code => NULL,
2065 x_anticip_compl_date => NULL,
2066 x_borw_lender_id => NULL,
2067 x_duns_borw_lender_id => NULL,
2068 x_guarantor_id => NULL,
2069 x_duns_guarnt_id => NULL,
2070 x_prc_type_code => NULL,
2071 x_rec_type_ind => NULL,
2072 x_cl_loan_type => NULL,
2073 x_cl_seq_number => NULL,
2074 x_last_resort_lender => NULL,
2075 x_lender_id => NULL,
2076 x_duns_lender_id => NULL,
2077 x_lend_non_ed_brc_id => NULL,
2078 x_recipient_id => NULL,
2079 x_recipient_type => NULL,
2080 x_duns_recip_id => NULL,
2081 x_recip_non_ed_brc_id => NULL,
2082 x_cl_rec_status => NULL,
2083 x_cl_rec_status_last_update => NULL,
2084 x_alt_prog_type_code => NULL,
2085 x_alt_appl_ver_code => NULL,
2086 x_borw_outstd_loan_code => NULL,
2087 x_mpn_confirm_code => NULL,
2088 x_resp_to_orig_code => NULL,
2089 x_appl_loan_phase_code => NULL,
2090 x_appl_loan_phase_code_chg => NULL,
2091 x_tot_outstd_stafford => NULL,
2092 x_tot_outstd_plus => NULL,
2093 x_alt_borw_tot_debt => NULL,
2094 x_act_interest_rate => NULL,
2095 x_service_type_code => NULL,
2096 x_rev_notice_of_guarnt => NULL,
2097 x_sch_refund_amt => NULL,
2098 x_sch_refund_date => NULL,
2099 x_uniq_layout_vend_code => NULL,
2100 x_uniq_layout_ident_code => NULL,
2101 x_p_person_id => loan_rec.p_person_id,
2102 x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
2103 x_p_ssn_chg_date => NULL,
2104 x_p_last_name => parent_dtl_rec.p_last_name,
2105 x_p_first_name => parent_dtl_rec.p_first_name,
2106 x_p_middle_name => parent_dtl_rec.p_middle_name,
2107 x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
2108 x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
2109 x_p_permt_city => parent_dtl_rec.p_permt_city,
2110 x_p_permt_state => parent_dtl_rec.p_permt_state,
2111 x_p_permt_zip => parent_dtl_rec.p_permt_zip,
2112 x_p_permt_addr_chg_date => NULL,
2113 x_p_permt_phone => lv_p_permt_phone,
2114 x_p_email_addr => parent_dtl_rec.p_email_addr,
2115 x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
2116 x_p_dob_chg_date => NULL,
2117 x_p_license_num => parent_dtl_rec.p_license_num,
2118 x_p_license_state => parent_dtl_rec.p_license_state,
2119 x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
2120 x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
2121 x_p_default_status => loan_rec.p_default_status,
2122 x_p_foreign_postal_code => NULL,
2123 x_p_state_of_legal_res => parent_dtl_rec.p_state_of_legal_res,
2124 x_p_legal_res_date => parent_dtl_rec.p_legal_res_date,
2125 x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
2126 x_s_ssn_chg_date => NULL,
2127 x_s_last_name => student_dtl_rec.p_last_name,
2128 x_s_first_name => student_dtl_rec.p_first_name,
2129 x_s_middle_name => student_dtl_rec.p_middle_name,
2130 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
2131 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
2132 x_s_permt_city => student_dtl_rec.p_permt_city,
2133 x_s_permt_state => student_dtl_rec.p_permt_state,
2134 x_s_permt_zip => student_dtl_rec.p_permt_zip,
2135 x_s_permt_addr_chg_date => NULL,
2136 x_s_permt_phone => lv_s_permt_phone,
2137 x_s_local_addr1 => student_dtl_rec.p_local_addr1,
2138 x_s_local_addr2 => student_dtl_rec.p_local_addr2,
2139 x_s_local_city => student_dtl_rec.p_local_city,
2140 x_s_local_state => student_dtl_rec.p_local_state,
2141 x_s_local_zip => student_dtl_rec.p_local_zip,
2142 x_s_local_addr_chg_date => NULL,
2143 x_s_email_addr => student_dtl_rec.p_email_addr,
2144 x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
2145 x_s_dob_chg_date => NULL,
2146 x_s_license_num => lv_s_license_number,
2147 x_s_license_state => lv_s_license_state,
2148 x_s_depncy_status => lv_dependency_status,
2149 x_s_default_status => p_interface.s_default_status_code,
2150 x_s_citizenship_status => lv_s_citizenship_status,
2151 x_s_alien_reg_num => lv_alien_reg_num,
2152 x_s_foreign_postal_code => NULL,
2153 x_pnote_batch_id => p_interface.pnote_batch_seq_num_txt,
2154 x_pnote_ack_date => p_interface.mpn_acknowledgement_date,
2155 x_pnote_mpn_ind => p_interface.pnote_indicator_code,
2156 x_award_id => p_award_id,
2157 x_base_id => p_base_id,
2158 x_document_id_txt => NULL,
2159 x_loan_key_num => NULL,
2160 x_interest_rebate_percent_num => NULL,
2161 x_fin_award_year => NULL,
2162 x_cps_trans_num => p_interface.transaction_num,
2163 x_atd_entity_id_txt => p_interface.atd_entity_id_txt,
2164 x_rep_entity_id_txt => p_interface.rep_entity_id_txt,
2165 x_source_entity_id_txt => NULL,
2166 x_pymt_servicer_amt => NULL,
2167 x_pymt_servicer_date => NULL,
2168 x_book_loan_amt => NULL,
2169 x_book_loan_amt_date => NULL,
2170 x_s_chg_birth_date => NULL,
2171 x_s_chg_ssn => NULL,
2172 x_s_chg_last_name => NULL,
2173 x_b_chg_birth_date => NULL,
2174 x_b_chg_ssn => NULL,
2175 x_b_chg_last_name => NULL,
2176 x_note_message => NULL,
2177 x_full_resp_code => NULL,
2178 x_s_permt_county => NULL,
2179 x_b_permt_county => NULL,
2180 x_s_permt_country => NULL,
2181 x_b_permt_country => NULL,
2182 x_crdt_decision_status => p_interface.credit_status,
2183 x_external_loan_id_txt => NULL,
2184 x_alt_approved_amt => NULL,
2185 x_flp_approved_amt => NULL,
2186 x_fls_approved_amt => NULL,
2187 x_flu_approved_amt => NULL,
2188 x_guarantor_use_txt => NULL,
2189 x_lender_use_txt => NULL,
2190 x_loan_app_form_code => NULL,
2191 x_reinstatement_amt => NULL,
2192 x_requested_loan_amt => NULL,
2193 x_school_use_txt => NULL,
2194 x_deferment_request_code => NULL,
2195 x_eft_authorization_code => NULL,
2196 x_actual_record_type_code => NULL,
2197 x_alt_borrower_ind_flag => NULL,
2198 x_borower_credit_authoriz_flag => NULL,
2199 x_borower_electronic_sign_flag => NULL,
2200 x_cost_of_attendance_amt => NULL,
2201 x_established_fin_aid_amount => NULL,
2202 x_expect_family_contribute_amt => NULL,
2203 x_mpn_type_flag => p_interface.pnote_status_type,
2204 x_school_id_txt => NULL,
2205 x_student_electronic_sign_flag => NULL,
2206 x_esign_src_typ_cd => NULL);
2207
2208
2209 EXCEPTION
2210 WHEN OTHERS THEN
2211 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2212 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOANS_ORIG_LOC_INSERT_ROW');
2213 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2214
2215 RAISE IMPORT_ERROR;
2216
2217 END loans_orig_loc_insert_row;
2218
2219 PROCEDURE lor_resp_insert_row(p_interface IN c_interface%ROWTYPE)
2220 AS
2221 /*
2222 || Created By : rasahoo
2223 || Created On : 03-June-2003
2224 || Purpose : Inserts legacy data into loans origination response Table .
2225 || Change History :
2226 || Who When What
2227 || (reverse chronological order - newest change first)
2228 */
2229 ln_rowid ROWID;
2230
2231 BEGIN
2232
2233 ln_rowid := NULL;
2234 igf_sl_dl_lor_resp_pkg.insert_row (
2235 x_mode => 'R',
2236 x_rowid => ln_rowid,
2237 x_lor_resp_num => ln_lor_resp_num,
2238 x_dbth_id => ln_dbth_id,
2239 x_orig_batch_id => p_interface.orig_send_batch_id_txt,
2240 x_loan_number => p_interface.loan_number_txt ,
2241 x_orig_ack_date => p_interface.orig_acknowledgement_date ,
2242 x_orig_status_flag => p_interface.loan_status_code ,
2243 x_orig_reject_reasons => p_interface.orig_reject_code,
2244 x_pnote_status => p_interface.pnote_status_code,
2245 x_pnote_id => p_interface.pnote_id_txt,
2246 x_pnote_accept_amt => p_interface.pnote_accept_amt,
2247 x_loan_amount_accepted => p_interface.loan_approved_amt,
2248 x_status => 'Y',
2249 x_elec_mpn_ind => p_interface.pnote_status_type
2250 );
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2254 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOR_RESP_INSERT_ROW');
2255 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2256
2257 RAISE IMPORT_ERROR;
2258
2259 END lor_resp_insert_row;
2260
2261 PROCEDURE lor_crresp_insert_row(p_interface IN c_interface%ROWTYPE)
2262 AS
2263 /*
2264 || Created By : rasahoo
2265 || Created On : 03-June-2003
2266 || Purpose : Inserts legacy data into igf_sl_dl_lor_crresp Table .
2267 || Change History :
2268 || Who When What
2269 || (reverse chronological order - newest change first)
2270 */
2271 ln_rowid ROWID;
2272
2273 BEGIN
2274 ln_rowid := NULL;
2275
2276 igf_sl_dl_lor_crresp_pkg.insert_row (
2277 x_mode => 'R',
2278 x_rowid => ln_rowid,
2279 X_lor_resp_num => ln_lor_resp_num,
2280 X_dbth_id => ln_dbth_id,
2281 X_loan_number => p_interface.loan_number_txt,
2282 X_credit_override => p_interface.credit_override_code,
2283 X_credit_decision_date => p_interface.credit_decision_date,
2284 X_status => 'Y' ,
2285 x_endorser_amount => p_interface.pnote_accept_amt,
2286 x_mpn_status => p_interface.pnote_status_code,
2287 x_mpn_id => p_interface.pnote_id_txt,
2288 x_mpn_type => p_interface.pnote_status_type,
2289 x_mpn_indicator => p_interface.pnote_indicator_code
2290 );
2291 EXCEPTION
2292 WHEN OTHERS THEN
2293 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2294 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.LOR_CRRESP_INSERT_ROW');
2295 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2296
2297 RAISE IMPORT_ERROR;
2298
2299 END lor_crresp_insert_row;
2300
2301 PROCEDURE pnote_resp_insert_row (p_interface IN c_interface%ROWTYPE)
2302 AS
2303 /*
2304 || Created By : rasahoo
2305 || Created On : 03-June-2003
2306 || Purpose : Inserts legacy data into igf_sl_dl_pnote_resp Table .
2307 || Change History :
2308 || Who When What
2309 || (reverse chronological order - newest change first)
2310 */
2311 ln_rowid ROWID;
2312 ln_dlpnr_id NUMBER;
2313 BEGIN
2314 ln_rowid := NULL;
2315 ln_dlpnr_id := NULL;
2316 igf_sl_dl_pnote_resp_pkg.insert_row (
2317 x_rowid => ln_rowid,
2318 x_dlpnr_id => ln_dlpnr_id,
2319 x_dbth_id => ln_dbth_id,
2320 x_pnote_ack_date => p_interface.pnote_accept_date,
2321 x_pnote_batch_id => p_interface.pnote_id_txt,
2322 x_loan_number => p_interface.loan_number_txt,
2323 x_pnote_status => p_interface.pnote_status_code,
2324 x_pnote_rej_codes => p_interface.orig_reject_code,
2325 x_mpn_ind => p_interface.pnote_indicator_code,
2326 x_pnote_accept_amt => p_interface.pnote_accept_amt,
2327 x_status => 'Y',
2328 x_mode => 'R',
2329 x_elec_mpn_ind => p_interface.pnote_status_type);
2330 EXCEPTION
2331 WHEN OTHERS THEN
2332 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2333 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.PNOTE_RESP_INSERT_ROW');
2334 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2335
2336 RAISE IMPORT_ERROR;
2337
2338 END pnote_resp_insert_row;
2339
2340 PROCEDURE pnote_insert_row(p_interface IN c_interface%ROWTYPE,
2341 p_award_id IN NUMBER)
2342 AS
2343 /*
2344 || Created By : rasahoo
2345 || Created On : 03-June-2003
2346 || Purpose : Inserts legacy data into igf_sl_dl_pnote_p_p_all Table .
2347 || Change History :
2348 || Who When What
2349 || veramach 16-SEP-2003 FA 122 loan enhancements
2350 || 1. c_loan_dtls does not select borrower information from igf_sl_lor_dtls_v
2351 || 2. igf_sl_gen.get_person_details is now used to get borrower information
2352 || (reverse chronological order - newest change first)
2353 */
2354 CURSOR c_award_amt IS
2355 SELECT offered_amt,
2356 accepted_amt
2357 FROM igf_aw_award_all
2358 WHERE award_id = p_award_id;
2359
2360 l_award_amt c_award_amt%ROWTYPE;
2361
2362 CURSOR c_loan_dtls(p_loan_id NUMBER,
2363 cp_origination_id NUMBER) IS
2364 SELECT loans.row_id,
2365 loans.loan_id,
2366 lor.s_default_status,
2367 lor.p_default_status,
2368 lor.p_person_id,
2369 fabase.person_id student_id
2370 FROM igf_sl_loans loans,
2371 igf_sl_lor lor,
2372 igf_aw_award awd,
2373 igf_ap_fa_base_rec fabase
2374 WHERE fabase.base_id = awd.base_id
2375 AND loans.award_id = awd.award_id
2376 AND loans.loan_id = lor.loan_id
2377 AND loans.loan_id = p_loan_id;
2378
2379 loan_rec c_loan_dtls%ROWTYPE;
2380
2381 student_dtl_rec igf_sl_gen.person_dtl_rec;
2382 student_dtl_cur igf_sl_gen.person_dtl_cur;
2383
2384 parent_dtl_rec igf_sl_gen.person_dtl_rec;
2385 parent_dtl_cur igf_sl_gen.person_dtl_cur;
2386
2387 CURSOR cur_isir_depend_status (cp_person_id NUMBER)
2388 IS
2389 SELECT isir.dependency_status
2390 FROM igf_ap_fa_base_rec fabase,igf_ap_isir_matched isir
2391 WHERE isir.base_id = fabase.base_id
2392 AND isir.payment_isir = 'Y'
2393 AND isir.system_record_type = 'ORIGINAL'
2394 AND fabase.person_id = cp_person_id;
2395
2396 l_student_license cur_isir_depend_status%ROWTYPE;
2397
2398 ln_rowid ROWID;
2399 ln_pnpp_id NUMBER;
2400 lv_p_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
2401 lv_s_permt_phone igf_sl_lor_loc_all.s_permt_phone%TYPE;
2402 lv_s_license_number igf_ap_isir_matched.driver_license_number%TYPE;
2403 lv_s_license_state igf_ap_isir_matched.driver_license_state%TYPE;
2404 lv_s_citizenship_status VARCHAR2(30);
2405 lv_alien_reg_num igf_ap_isir_matched.alien_reg_number%TYPE;
2406 --
2407 lv_dependency_status igf_ap_isir_matched.dependency_status%TYPE;
2408 lv_s_legal_res_date igf_ap_isir_matched.s_legal_resd_date%TYPE;
2409 lv_s_legal_res_state igf_ap_isir_matched.s_state_legal_residence%TYPE;
2410
2411
2412 BEGIN
2413
2414 ln_rowid := NULL;
2415 ln_pnpp_id := NULL;
2416
2417 OPEN c_award_amt;
2418 FETCH c_award_amt INTO l_award_amt;
2419 CLOSE c_award_amt;
2420
2421 OPEN c_loan_dtls(ln_loan_id,ln_origination_id);
2422 FETCH c_loan_dtls INTO loan_rec;
2423 igf_sl_gen.get_person_details(loan_rec.student_id,student_dtl_cur);
2424 FETCH student_dtl_cur INTO student_dtl_rec;
2425 igf_sl_gen.get_person_details(loan_rec.p_person_id,parent_dtl_cur);
2426 FETCH parent_dtl_cur INTO parent_dtl_rec;
2427
2428 CLOSE c_loan_dtls;
2429 CLOSE student_dtl_cur;
2430 CLOSE parent_dtl_cur;
2431
2432 OPEN cur_isir_depend_status(loan_rec.student_id);
2433 FETCH cur_isir_depend_status INTO lv_dependency_status;
2434 CLOSE cur_isir_depend_status;
2435
2436 lv_s_permt_phone := igf_sl_gen.get_person_phone(loan_rec.student_id);
2437 lv_p_permt_phone := igf_sl_gen.get_person_phone(loan_rec.p_person_id);
2438
2439 --Code added for bug 3603289 start
2440 lv_s_license_number := student_dtl_rec.p_license_num;
2441 lv_s_license_state := student_dtl_rec.p_license_state;
2442 lv_s_citizenship_status := student_dtl_rec.p_citizenship_status;
2443 lv_alien_reg_num := student_dtl_rec.p_alien_reg_num;
2444 lv_s_legal_res_date := student_dtl_rec.p_legal_res_date;
2445 lv_s_legal_res_state := student_dtl_rec.p_state_of_legal_res;
2446 --Code added for bug 3603289 end
2447
2448 igf_sl_dl_pnote_p_p_pkg.insert_row(
2449 x_mode => 'R',
2450 x_rowid => ln_rowid,
2451 x_pnpp_id => ln_pnpp_id,
2452 x_batch_seq_num => ln_dbth_id ,
2453 x_loan_id => ln_loan_id,
2454 x_loan_number => p_interface.loan_number_txt ,
2455 x_loan_amt_offered => l_award_amt.offered_amt,
2456 x_loan_amt_accepted => l_award_amt.accepted_amt,
2457 x_loan_per_begin_date => p_interface.loan_per_begin_date,
2458 x_loan_per_end_date => p_interface.loan_per_end_date ,
2459 x_person_id => loan_rec.student_id,
2460 x_s_ssn => SUBSTR(student_dtl_rec.p_ssn,1,9),
2461 x_s_first_name => student_dtl_rec.p_first_name,
2462 x_s_last_name => student_dtl_rec.p_last_name,
2463 x_s_middle_name => student_dtl_rec.p_middle_name,
2464 x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
2465 x_s_citizenship_status => loan_rec.s_default_status,
2466 x_s_alien_reg_number => lv_alien_reg_num,
2467 x_s_license_num => lv_s_license_number,
2468 x_s_license_state => lv_s_license_state,
2469 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
2470 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
2471 x_s_permt_city => student_dtl_rec.p_permt_city,
2472 x_s_permt_state => student_dtl_rec.p_permt_state,
2473 x_s_permt_province => NULL,
2474 x_s_permt_county => NULL,
2475 x_s_permt_country => NULL,
2476 x_s_permt_zip => student_dtl_rec.p_permt_zip,
2477 x_s_email_addr => student_dtl_rec.p_email_addr,
2478 x_s_phone => lv_s_permt_phone,
2479 x_p_person_id => loan_rec.p_person_id,
2480 x_p_ssn => SUBSTR(parent_dtl_rec.p_ssn,1,9),
2481 x_p_last_name => parent_dtl_rec.p_last_name,
2482 x_p_first_name => parent_dtl_rec.p_first_name,
2483 x_p_middle_name => parent_dtl_rec.p_middle_name,
2484 x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
2485 x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
2486 x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
2487 x_p_license_num => parent_dtl_rec.p_license_num,
2488 x_p_license_state => parent_dtl_rec.p_license_state,
2489 x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
2490 x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
2491 x_p_permt_city => parent_dtl_rec.p_permt_city,
2492 x_p_permt_state => parent_dtl_rec.p_permt_state,
2493 x_p_permt_province => NULL,
2494 x_p_permt_county => NULL,
2495 x_p_permt_country => NULL,
2496 x_p_permt_zip => parent_dtl_rec.p_permt_zip,
2497 x_p_email_addr => parent_dtl_rec.p_email_addr,
2498 x_p_phone => lv_p_permt_phone,
2499 x_status => 'Y'
2500 );
2501 EXCEPTION
2502 WHEN OTHERS THEN
2503 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2504 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.PNOTE_INSERT_ROW');
2505 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2506
2507 RAISE IMPORT_ERROR;
2508
2509 END pnote_insert_row;
2510
2511
2512 PROCEDURE disb_resp_insert_row(p_disb_interface c_disb_interface%ROWTYPE)
2513 AS
2514 /*
2515 || Created By : rasahoo
2516 || Created On : 03-June-2003
2517 || Purpose : Inserts legacy data into igf_db_dl_disb_resp_all Table .
2518 || Change History :
2519 || Who When What
2520 || (reverse chronological order - newest change first)
2521 */
2522 ln_rowid ROWID;
2523 ln_ddrp_id NUMBER;
2524
2525 BEGIN
2526
2527 ln_rowid := NULL;
2528 ln_ddrp_id := NULL;
2529
2530 igf_db_dl_disb_resp_pkg.insert_row (
2531 x_mode => 'R',
2532 x_rowid => ln_rowid,
2533 x_ddrp_id => ln_ddrp_id,
2534 x_dbth_id => ln_dbth_id,
2535 x_loan_number => p_disb_interface.loan_number_txt,
2536 x_disb_num => p_disb_interface.disbursement_num,
2537 x_disb_activity => p_disb_interface.disbursement_activity_code,
2538 x_transaction_date => p_disb_interface.disbursement_date,
2539 x_disb_seq_num => p_disb_interface.disbursement_seq_num ,
2540 x_disb_gross_amt => p_disb_interface.gross_disbursement_amt,
2541 x_fee_1 => p_disb_interface.loc_fee_1_amt,
2542 x_disb_net_amt => p_disb_interface.gross_disbursement_amt - p_disb_interface.loc_fee_1_amt + p_disb_interface.loc_int_rebate_amt,
2543 x_int_rebate_amt => p_disb_interface.loc_int_rebate_amt,
2544 x_user_ident => p_disb_interface.user_identifier_txt,
2545 x_disb_batch_id => p_disb_interface.disbursement_batch_id_txt,
2546 x_school_id => p_disb_interface.school_code_txt,
2547 x_sch_code_status => NULL,
2548 x_loan_num_status => NULL,
2549 x_disb_num_status => NULL,
2550 x_disb_activity_status => p_disb_interface.disbursement_activity_st_txt,
2551 x_trans_date_status => NULL,
2552 x_disb_seq_num_status => NULL,
2553 x_loc_disb_gross_amt => p_disb_interface.loc_disbursement_gross_amt,
2554 x_loc_fee_1 => p_disb_interface.loc_fee_1_amt,
2555 x_loc_disb_net_amt => p_disb_interface.loc_disbursement_net_amt,
2556 x_servicer_refund_amt => p_disb_interface.servicer_refund_amt,
2557 x_loc_int_rebate_amt => p_disb_interface.loc_int_rebate_amt,
2558 x_loc_net_booked_loan => p_disb_interface.loc_net_booked_loan_amt,
2559 x_ack_date => p_disb_interface.acknowledgement_date,
2560 x_affirm_flag => p_disb_interface.confirmation_flag,
2561 x_status => 'N'
2562 );
2563 EXCEPTION
2564 WHEN OTHERS THEN
2565 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2566 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DISB_RESP_INSERT_ROW');
2567 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2568
2569 RAISE IMPORT_ERROR;
2570
2571 END disb_resp_insert_row;
2572
2573 PROCEDURE db_awd_disb_update_row(l_disb_interface IN c_disb_interface%ROWTYPE,
2574 p_award_id IN NUMBER)
2575 AS
2576 /*
2577 || Created By : rasahoo
2578 || Created On : 03-June-2003
2579 || Purpose : Updates legacy data into igf_db_awd_disb_dtl Table .
2580 || Change History :
2581 || Who When What
2582 || (reverse chronological order - newest change first)
2583 */
2584 ln_rowid ROWID;
2585 ln_ddrp_id NUMBER;
2586
2587 CURSOR c_disb_det (cp_disb_num NUMBER,
2588 cp_DISBURSEMENT_SEQ_NUM VARCHAR2)
2589 IS
2590 SELECT ROWID,adisb.*
2591 FROM IGF_DB_AWD_DISB_DTL_ALL adisb
2592 WHERE adisb.award_id = p_award_id
2593 AND adisb.disb_num = cp_disb_num
2594 AND adisb.disb_seq_num = cp_disbursement_seq_num;
2595 l_rec_disb_dtl c_disb_det%ROWTYPE;
2596
2597 l_disb_status igf_db_awd_disb_dtl_all.disb_status%TYPE;
2598 BEGIN
2599
2600 ln_rowid := NULL;
2601 ln_ddrp_id := NULL;
2602 l_disb_status := NULL;
2603
2604 OPEN c_disb_det(l_disb_interface.disbursement_num,l_disb_interface.DISBURSEMENT_SEQ_NUM );
2605 FETCH c_disb_det INTO l_rec_disb_dtl;
2606 CLOSE c_disb_det;
2607
2608 IF l_disb_interface.disbursement_activity_st_txt IS NULL THEN
2609 l_disb_status := 'A';
2610 ELSE
2611 l_disb_status := 'R';
2612 END IF;
2613
2614 igf_db_awd_disb_dtl_pkg.update_row( x_rowid => l_rec_disb_dtl.rowid,
2615 x_award_id => l_rec_disb_dtl.award_id,
2616 x_disb_num => l_rec_disb_dtl.disb_num,
2617 x_disb_seq_num => l_rec_disb_dtl.disb_seq_num,
2618 x_disb_gross_amt => l_disb_interface.gross_disbursement_amt,
2619 x_fee_1 => l_disb_interface.loc_fee_1_amt,
2620 x_fee_2 => l_rec_disb_dtl.fee_2,
2621 x_disb_net_amt => l_disb_interface.loc_disbursement_net_amt,
2622 x_disb_adj_amt => l_rec_disb_dtl.disb_adj_amt,
2623 x_disb_date => l_disb_interface.disbursement_date,
2624 x_fee_paid_1 => l_rec_disb_dtl.fee_paid_1,
2625 x_fee_paid_2 => l_rec_disb_dtl.fee_paid_2,
2626 x_disb_activity => l_disb_interface.disbursement_activity_code,
2627 x_disb_batch_id => l_disb_interface.disbursement_batch_id_txt,
2628 x_disb_ack_date => l_disb_interface.acknowledgement_date,
2629 x_booking_batch_id => l_disb_interface.booking_batch_id_txt,
2630 x_booked_date => l_disb_interface.booked_date,
2631 x_disb_status => l_disb_status,
2632 x_disb_status_date => l_disb_interface.disbursement_activity_date,
2633 x_sf_status => l_rec_disb_dtl.sf_status,
2634 x_sf_status_date => l_rec_disb_dtl.sf_status_date,
2635 x_sf_invoice_num => l_rec_disb_dtl.sf_invoice_num,
2636 x_sf_credit_id => l_rec_disb_dtl.sf_credit_id,
2637 x_spnsr_credit_id => l_rec_disb_dtl.spnsr_credit_id,
2638 x_spnsr_charge_id => l_rec_disb_dtl.spnsr_charge_id,
2639 x_error_desc => l_rec_disb_dtl.error_desc,
2640 x_mode => 'R' ,
2641 x_notification_date => l_rec_disb_dtl.notification_date,
2642 x_interest_rebate_amt => l_rec_disb_dtl.interest_rebate_amt,
2643 x_ld_cal_type => l_rec_disb_dtl.ld_cal_type,
2644 x_ld_sequence_number => l_rec_disb_dtl.ld_sequence_number
2645 );
2646 EXCEPTION
2647 WHEN OTHERS THEN
2648 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2649 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DB_AWD_DISB_UPDATE_ROW');
2650 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2651
2652 RAISE IMPORT_ERROR;
2653
2654 END db_awd_disb_update_row;
2655
2656 PROCEDURE dl_chg_send_insert_row (p_chg_interface c_chg_interface%ROWTYPE )
2657 AS
2658 /*
2659 || Created By : rasahoo
2660 || Created On : 03-June-2003
2661 || Purpose : Inserts legacy data into igf_sl_dl_chg_send Table .
2662 || Change History :
2663 || Who When What
2664 || (reverse chronological order - newest change first)
2665 */
2666 ln_rowid ROWID;
2667 ln_chg_num NUMBER;
2668
2669 BEGIN
2670 ln_rowid := NULL;
2671 ln_chg_num := NULL;
2672 igf_sl_dl_chg_send_pkg.insert_row (
2673 x_mode => 'R',
2674 x_rowid => ln_rowid,
2675 X_chg_num => ln_chg_num,
2676 X_dbth_id => ln_dbth_id,
2677 X_loan_number => p_chg_interface.LOAN_NUMBER_TXT,
2678 X_chg_code => p_chg_interface.CHANGE_CODE,
2679 X_new_value => p_chg_interface.NEW_VALUE_TXT,
2680 X_status => 'S'
2681 );
2682
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2686 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_CHG_SEND_INSERT_ROW');
2687 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2688
2689 RAISE IMPORT_ERROR;
2690
2691 END dl_chg_send_insert_row;
2692
2693 PROCEDURE dl_chg_resp_insert_row (p_chg_interface c_chg_interface%ROWTYPE )
2694 AS
2695 /*
2696 || Created By : rasahoo
2697 || Created On : 03-June-2003
2698 || Purpose : Inserts legacy data into igf_sl_dl_chg_resp Table .
2699 || Change History :
2700 || Who When What
2701 || (reverse chronological order - newest change first)
2702 */
2703 ln_rowid ROWID;
2704 ln_resp_num NUMBER;
2705
2706 BEGIN
2707 ln_rowid := NULL;
2708 ln_resp_num := NULL;
2709
2710 igf_sl_dl_chg_resp_pkg.insert_row (
2711 x_mode => 'R',
2712 x_rowid => ln_rowid,
2713 X_resp_num => ln_resp_num,
2714 X_dbth_id => ln_dbth_id,
2715 X_batch_id => p_chg_interface.SEND_BATCH_ID_TXT,
2716 X_loan_number => p_chg_interface.LOAN_NUMBER_TXT,
2717 X_chg_code => p_chg_interface.CHANGE_CODE,
2718 X_reject_code => p_chg_interface.REJECT_CODE,
2719 X_new_value => p_chg_interface.NEW_VALUE_TXT,
2720 X_loan_ident_err_code => p_chg_interface.LOAN_IDENT_ERR_CODE,
2721 X_status => 'N'
2722 );
2723 EXCEPTION
2724 WHEN OTHERS THEN
2725 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2726 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_CHG_RESP_INSERT_ROW');
2727 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2728
2729 RAISE IMPORT_ERROR;
2730
2731 END dl_chg_resp_insert_row;
2732
2733 PROCEDURE dl_batch_insert_row(p_interface IN c_interface%ROWTYPE)
2734 AS
2735 /*
2736 || Created By : rasahoo
2737 || Created On : 03-June-2003
2738 || Purpose : Inserts legacy data into igf_sl_dl_batch Table .
2739 || Change History :
2740 || Who When What
2741 || bvisvana 24-Aug-2006 Bug 5478287 - Extending batch creation for 2006 and 2007
2742 || (reverse chronological order - newest change first)
2743 */
2744 ln_rowid ROWID;
2745 l_value VARCHAR2(23);
2746 l_batch_type VARCHAR2(20);
2747 l_cycle_ind VARCHAR2(20);
2748 l_sl_code VARCHAR2(20);
2749 l_dt_btch_created VARCHAR2(20);
2750 l_tm_btch_created VARCHAR2(20);
2751
2752 CURSOR c_message_class (p_batch_type VARCHAR2,
2753 p_cycle_year VARCHAR2,
2754 p_message_class igf_sl_dl_file_type.message_class%TYPE
2755 )
2756 IS
2757 SELECT message_class
2758 FROM igf_sl_dl_file_type
2759 WHERE batch_type = p_batch_type
2760 AND cycle_year = p_cycle_year
2761 AND message_class LIKE p_message_class;
2762
2763 l_message_class c_message_class%ROWTYPE;
2764
2765 BEGIN
2766 ln_dbth_id := NULL;
2767 l_value := p_interface.ORIG_SEND_BATCH_ID_TXT;
2768 l_batch_type := SUBSTR(l_value,1,2);
2769 l_cycle_ind := SUBSTR(l_value,3,1);
2770 l_sl_code := SUBSTR(l_value,4,6);
2771 l_dt_btch_created := SUBSTR(l_value,10,8);
2772 l_tm_btch_created := SUBSTR(l_value,18,6);
2773
2774 IF l_cycle_ind = '3' THEN
2775 OPEN c_message_class(l_batch_type,'2003','%OP');
2776 FETCH c_message_class INTO l_message_class;
2777 CLOSE c_message_class;
2778 ELSIF l_cycle_ind = '4' THEN
2779 OPEN c_message_class(l_batch_type,'2004','%OP');
2780 FETCH c_message_class INTO l_message_class;
2781 CLOSE c_message_class;
2782 ELSIF l_cycle_ind = '5' THEN
2783 OPEN c_message_class(l_batch_type,'2005','%OP');
2784 FETCH c_message_class INTO l_message_class;
2785 CLOSE c_message_class;
2786 -- Bug 5478287
2787 ELSIF l_cycle_ind = '6' THEN
2788 OPEN c_message_class(l_batch_type,'2006','%OP');
2789 FETCH c_message_class INTO l_message_class;
2790 CLOSE c_message_class;
2791 ELSIF l_cycle_ind = '7' THEN
2792 OPEN c_message_class(l_batch_type,'2007','%OP');
2793 FETCH c_message_class INTO l_message_class;
2794 CLOSE c_message_class;
2795 END IF;
2796
2797 igf_sl_dl_batch_pkg.insert_row (
2798 x_mode => 'R',
2799 x_rowid => ln_rowid,
2800 x_dbth_id => ln_dbth_id,
2801 x_batch_id => p_interface.ORIG_SEND_BATCH_ID_TXT,
2802 x_message_class => l_message_class.message_class,
2803 x_bth_creation_date => TO_DATE(l_dt_btch_created,'YYYYMMDD'),
2804 x_batch_rej_code => NULL,
2805 x_end_date => NULL,
2806 x_batch_type => l_batch_type,
2807 x_send_resp => 'R',
2808 x_status => 'N');
2809
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2813 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DL_BATCH_INSERT_ROW');
2814 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
2815
2816 RAISE IMPORT_ERROR;
2817
2818 END dl_batch_insert_row;
2819
2820 PROCEDURE delete_context_records(p_loan_id igf_sl_loans_all.loan_id%TYPE,
2821 p_loan_num igf_sl_loans_all.loan_number%TYPE,
2822 p_rowid ROWID)
2823
2824 AS
2825 /*
2826 || Created By : rasahoo
2827 || Created On : 03-June-2003
2828 || Purpose :
2829 || Change History :
2830 || Who When What
2831 || (reverse chronological order - newest change first)
2832 */
2833 CURSOR c_lor(cp_loan_id NUMBER)
2834 IS
2835 SELECT rowid, origination_id
2836 FROM igf_sl_lor_all
2837 WHERE loan_id = cp_loan_id;
2838
2839 l_lor c_lor%ROWTYPE;
2840
2841 CURSOR c_pnote_hist(cp_loan_id NUMBER)
2842 IS
2843 SELECT ROWID
2844 FROM igf_sl_pnote_stat_h
2845 WHERE loan_id = cp_loan_id;
2846
2847 l_pnote_hist c_pnote_hist%ROWTYPE;
2848
2849 CURSOR c_pnote(cp_loan_id NUMBER)
2850 IS
2851 SELECT ROWID
2852 FROM igf_sl_dl_pnote_p_p_all
2853 WHERE loan_id = cp_loan_id;
2854
2855 l_pnote c_pnote%ROWTYPE;
2856
2857 CURSOR c_lor_loc(cp_orig_id NUMBER)
2858 IS
2859 SELECT rowid
2860 FROM igf_sl_lor_loc_all
2861 WHERE origination_id = cp_orig_id;
2862
2863 l_lor_loc c_lor_loc%ROWTYPE;
2864
2865 CURSOR c_disb_resp(cp_loan_number VARCHAR2)
2866 IS
2867 SELECT rowid
2868 FROM igf_db_dl_disb_resp_all
2869 WHERE loan_number = cp_loan_number;
2870
2871 l_disb_resp c_disb_resp%ROWTYPE;
2872
2873 CURSOR c_lor_crresp(cp_loan_number VARCHAR2)
2874 IS
2875 SELECT rowid,dbth_id
2876 FROM igf_sl_dl_lor_crresp_all
2877 WHERE loan_number = cp_loan_number;
2878
2879 l_lor_crresp c_lor_crresp%ROWTYPE;
2880 CURSOR c_lor_resp(cp_loan_number VARCHAR2)
2881 IS
2882 SELECT rowid,dbth_id
2883 FROM igf_sl_dl_lor_resp_all
2884 WHERE loan_number = cp_loan_number;
2885
2886 l_lor_resp c_lor_resp%ROWTYPE;
2887
2888 CURSOR c_pnote_resp(cp_loan_number VARCHAR2)
2889 IS
2890 SELECT rowid,dbth_id,dlpnr_id
2891 FROM igf_sl_dl_pnote_resp_all
2892 WHERE loan_number = cp_loan_number;
2893
2894 l_pnote_resp c_pnote_resp%ROWTYPE;
2895
2896 CURSOR c_dl_chg_send (p_loan_number VARCHAR2)
2897 IS
2898 SELECT ROWID
2899 FROM igf_sl_dl_chg_send
2900 WHERE loan_number = p_loan_number;
2901
2902 l_dl_chg_send c_dl_chg_send%ROWTYPE;
2903
2904 CURSOR c_dl_chg_resp (p_loan_number VARCHAR2)
2905 IS
2906 SELECT ROWID
2907 FROM igf_sl_dl_chg_resp_all
2908 WHERE loan_number = p_loan_number;
2909
2910 CURSOR c_pdet_resp( cp_dlpnr_id NUMBER)
2911 IS
2912 SELECT ROWID
2913 FROM igf_sl_dl_pdet_resp
2914 WHERE dlpnr_id = cp_dlpnr_id;
2915
2916 l_pdet_resp c_pdet_resp%ROWTYPE;
2917
2918 l_dl_chg_resp c_dl_chg_resp%ROWTYPE;
2919
2920 BEGIN
2921
2922 FOR l_pnote IN c_pnote(p_loan_id) LOOP
2923 igf_sl_dl_pnote_p_p_pkg.delete_row(X_ROWID => l_pnote.rowid);
2924 END LOOP;
2925
2926 FOR l_pnote_hist IN c_pnote_hist(p_loan_id) LOOP
2927 igf_sl_pnote_stat_h_pkg.delete_row(X_ROWID => l_pnote_hist.rowid);
2928 END LOOP;
2929
2930 FOR l_lor IN c_lor(p_loan_id) LOOP
2931 FOR l_lor_loc IN c_lor_loc(l_lor.origination_id) LOOP
2932 igf_sl_lor_loc_pkg.delete_row(X_ROWID => l_lor_loc.rowid);
2933 END LOOP;
2934 igf_sl_lor_pkg.delete_row(X_ROWID => l_lor.rowid);
2935 END LOOP;
2936
2937 -- Delete all disbursements corresponding to this award_id.
2938 FOR l_disb_resp IN c_disb_resp(p_loan_id) LOOP
2939 igf_db_dl_disb_resp_pkg.delete_row(X_ROWID => l_disb_resp.rowid);
2940 END LOOP;
2941 -- Check if there are child records in the Loan Change Origination and Response table
2942 -- If found then delete those records.
2943 IF (p_loan_num IS NOT NULL) THEN
2944 FOR l_lor_crresp IN c_lor_crresp(p_loan_num) LOOP
2945 -- delete record
2946
2947 igf_sl_dl_lor_crresp_pkg.delete_row(X_ROWID => l_lor_crresp.rowid);
2948
2949 END LOOP;
2950
2951 FOR l_lor_resp IN c_lor_resp(p_loan_num) LOOP
2952 -- delete record
2953
2954 igf_sl_dl_lor_resp_pkg.delete_row(X_ROWID => l_lor_resp.rowid);
2955
2956 END LOOP;
2957
2958 FOR l_pnote_resp IN c_pnote_resp(p_loan_num) LOOP
2959 -- delete record
2960
2961 FOR l_pdet_resp IN c_pdet_resp(l_pnote_resp.dlpnr_id) LOOP
2962 igf_sl_dl_pdet_resp_pkg.delete_row(l_pdet_resp.rowid);
2963 END LOOP;
2964 igf_sl_dl_pnote_resp_pkg.delete_row(X_ROWID => l_pnote_resp.rowid);
2965 END LOOP;
2966 END IF;
2967
2968 FOR l_dl_chg_send IN c_dl_chg_send(p_loan_num) LOOP
2969 -- delete_row
2970
2971 igf_sl_dl_chg_send_pkg.delete_row(X_ROWID => l_dl_chg_send.rowid);
2972
2973 END LOOP;
2974
2975 FOR l_dl_chg_resp IN c_dl_chg_resp(p_loan_num) LOOP
2976 -- delete_row
2977
2978 igf_sl_dl_chg_resp_pkg.delete_row(X_ROWID => l_dl_chg_resp.rowid);
2979
2980 END LOOP;
2981 IF p_rowid IS NOT NULL THEN
2982
2983 igf_sl_loans_pkg.delete_row(X_ROWID => p_rowid);
2984
2985 END IF;
2986 EXCEPTION
2987
2988 WHEN others THEN
2989 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2990 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_li_imp_pkg.delete_context_records.exception','Exception:'||SQLERRM);
2991 END IF;
2992 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2993 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.DELETE_CONTEXT_RECORDS');
2994 fnd_file.put_line(fnd_file.log,fnd_message.get || sqlerrm);
2995
2996 RAISE IMPORT_ERROR;
2997
2998 END delete_context_records;
2999
3000 PROCEDURE insert_context_records(l_interface c_interface%ROWTYPE,
3001 l_award_id igf_aw_award_all.award_id%TYPE,
3002 l_base_id NUMBER,
3003 l_loan_num igf_sl_loans_all.loan_number%TYPE)
3004 AS
3005 /*
3006 || Created By : rasahoo
3007 || Created On : 03-June-2003
3008 || Purpose :
3009 || Change History :
3010 || Who When What
3011 || (reverse chronological order - newest change first)
3012 */
3013 l_chg_interface c_chg_interface%ROWTYPE;
3014 BEGIN
3015
3016 IF l_interface.loan_status_code IN ('A','G','N') THEN
3017 -- Insert into loans table
3018 loans_insert_row(l_interface,l_award_id);
3019 END IF;
3020
3021 IF l_interface.loan_status_code IN ('A','G','N') THEN
3022 -- Insert into loans origination table
3023 loans_orig_insert_row(l_interface);
3024 END IF;
3025
3026 IF l_interface.loan_status_code = 'A' THEN
3027 -- Insert into loans orig loc table
3028 loans_orig_loc_insert_row(l_interface,l_award_id,l_base_id,lv_fed_fund_code);
3029 END IF;
3030
3031 IF l_interface.loan_status_code = 'A' THEN
3032 -- Insert into batch table
3033 dl_batch_insert_row(l_interface);
3034 END IF;
3035
3036 FOR l_chg_interface IN c_chg_interface(l_loan_num) LOOP
3037 IF l_interface.loan_status_code = 'A' AND NVL(l_interface.loan_chg_status,'*') = 'A' AND ln_dbth_id IS NOT NULL THEN
3038 -- Insert into change send table
3039 dl_chg_send_insert_row (l_chg_interface );
3040 -- insert into change response table
3041 dl_chg_resp_insert_row (l_chg_interface);
3042 END IF;
3043 END LOOP;
3044
3045 -- insert into pnote table
3046 -- pnote_insert_row(l_interface,l_award_id);
3047 -- Insert into response tables
3048 IF l_interface.pnote_id_txt IS NOT NULL AND l_interface.pnote_accept_date IS NOT NULL AND ln_dbth_id IS NOT NULL THEN
3049 pnote_resp_insert_row (l_interface);
3050 END IF;
3051
3052 -- if the l_interface credit decision date is not null then only insert
3053 IF l_interface.loan_status_code = 'A' AND l_interface.credit_decision_date IS NOT NULL AND ln_dbth_id IS NOT NULL THEN
3054 lor_crresp_insert_row(l_interface);
3055 END IF;
3056
3057 IF l_interface.loan_status_code = 'A' AND ln_dbth_id IS NOT NULL THEN
3058 lor_resp_insert_row(l_interface);
3059 END IF;
3060
3061 EXCEPTION
3062 WHEN OTHERS THEN
3063 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3064 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.INSERT_CONTEXT_RECORDS');
3065 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
3066 RAISE IMPORT_ERROR;
3067
3068 END insert_context_records;
3069
3070 PROCEDURE insert_context_disb_records( p_disb_interface c_disb_interface%ROWTYPE,
3071 p_award_id igf_aw_award_all.award_id%TYPE
3072 )
3073 AS
3074 /*
3075 || Created By : rasahoo
3076 || Created On : 03-June-2003
3077 || Purpose :
3078 || Change History :
3079 || Who When What
3080 || (reverse chronological order - newest change first)
3081 */
3082
3083 BEGIN
3084 IF p_disb_interface.acknowledgement_date IS NOT NULL THEN
3085 -- insert into disbursement table
3086 disb_resp_insert_row(p_disb_interface);
3087 END IF;
3088 -- update disbursement table
3089 db_awd_disb_update_row(p_disb_interface ,p_award_id);
3090 EXCEPTION
3091 WHEN OTHERS THEN
3092 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3093 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.INSERT_CONTEXT_DISB_RECORDS');
3094 fnd_file.put_line(fnd_file.log,fnd_message.get || SQLERRM);
3095
3096 RAISE IMPORT_ERROR;
3097
3098 END insert_context_disb_records;
3099
3100
3101
3102 PROCEDURE run ( errbuf IN OUT NOCOPY VARCHAR2,
3103 retcode IN OUT NOCOPY NUMBER,
3104 p_awd_yr IN VARCHAR2,
3105 p_batch_id IN NUMBER,
3106 p_delete_flag IN VARCHAR2
3107 )
3108 IS
3109 /*
3110 || Created By : RASAHOO
3111 || Created On : 07-July-2003
3112 || Purpose : This procedure is to import legacy data.
3113 || Known limitations, enhancements or remarks :
3114 || Change History :
3115 || Who When What
3116 || tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
3117 || bvisvana 07-July-2005 Bug # 4008991 - IGF_GR_BATCH_DOES_NOT_EXIST replaced by IGF_SL_GR_BATCH_DOES_NO_EXIST
3118 || rasahoo 14-Aug-2003 #3096267 message in log file added
3119 || to indicate when one record successfully get imported.
3120 || (reverse chronological order - newest change first
3121 */
3122
3123
3124 l_error VARCHAR2(80);
3125 l_chk_profile VARCHAR2(1) := 'N';
3126 lv_flag_lo BOOLEAN := FALSE;
3127 l_batch_valid VARCHAR2(1) ;
3128 l_processing VARCHAR2(80);
3129 l_person_number VARCHAR2(80);
3130 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
3131 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
3132 l_valid_for_dml VARCHAR2(2);
3133 l_error_flag BOOLEAN := FALSE;
3134 l_award_id igf_aw_award_all.award_id%TYPE;
3135 p_status BOOLEAN;
3136 l_disb_interface c_disb_interface%ROWTYPE;
3137 l_chg_interface c_chg_interface%ROWTYPE;
3138 p_d_status BOOLEAN;
3139 p_d_status2 BOOLEAN;
3140 l_loan_disb VARCHAR2(80);
3141 lv_loan_id igf_sl_loans_all.loan_id%TYPE;
3142 lv_loan_num igf_sl_loans_all.loan_number%TYPE;
3143 lv_rowid ROWID;
3144 l_legacy_flag VARCHAR2(1);
3145 l_success_record_cnt NUMBER := 0;
3146 l_total_record_cnt NUMBER := 0;
3147 l_debug_str VARCHAR2(3000) := NULL ;
3148 l_school_code VARCHAR2(6);
3149 l_num_of_disb_rec NUMBER := 0;
3150 lb_write_log BOOLEAN := FALSE;
3151 l_loan_id_msg VARCHAR(10);
3152 -- cursor to get alternate code for award year
3153 CURSOR c_alternate_code( cp_ci_cal_type igs_ca_inst.cal_type%TYPE ,
3154 cp_ci_sequence_number igs_ca_inst.sequence_number%TYPE ) IS
3155 SELECT alternate_code
3156 FROM igs_ca_inst
3157 WHERE cal_type = cp_ci_cal_type
3158 AND sequence_number = cp_ci_sequence_number ;
3159
3160 l_alternate_code igs_ca_inst.alternate_code%TYPE ;
3161
3162 CURSOR c_award_year (cp_cal_type VARCHAR2,
3163 cp_seq_num NUMBER)
3164 IS
3165 SELECT batch_year
3166 FROM igf_ap_batch_aw_map
3167 WHERE ci_cal_type = cp_cal_type
3168 AND ci_sequence_number = cp_seq_num;
3169
3170 l_award_year c_award_year%ROWTYPE;
3171
3172 CURSOR c_award_det(cp_cal_type VARCHAR2,
3173 cp_seq_number NUMBER) IS
3174 SELECT batch_year,
3175 award_year_status_code status,
3176 sys_award_year
3177 FROM igf_ap_batch_aw_map
3178 WHERE ci_cal_type = cp_cal_type
3179 AND ci_sequence_number = cp_seq_number;
3180
3181 l_award_det c_award_det%ROWTYPE;
3182
3183 CURSOR c_award_ref (cp_base_id NUMBER,
3184 cp_award_number VARCHAR2)
3185 IS
3186
3187 SELECT awd.award_id,awd.award_status
3188 FROM igf_aw_award_all awd
3189 WHERE awd.base_id = cp_base_id
3190 AND awd.award_number_txt = cp_award_number;
3191
3192 l_award_ref c_award_ref%ROWTYPE;
3193
3194 CURSOR c_act_isir(cp_base_id NUMBER,
3195 p_active igf_ap_isir_matched.active_isir%TYPE
3196 )
3197 IS
3198
3199 SELECT 1
3200 FROM igf_ap_isir_matched isir
3201 WHERE isir.base_id = cp_base_id
3202 AND isir.active_isir = p_active;
3203
3204 l_act_isir c_act_isir%ROWTYPE;
3205
3206 CURSOR c_fed_fund_code(cp_award_id NUMBER) IS
3207 SELECT
3208 fc.fed_fund_code,
3209 fc.sys_fund_type
3210 FROM
3211 igf_aw_award aw,
3212 igf_aw_fund_mast fm,
3213 igf_aw_fund_cat fc
3214 WHERE
3215 aw.award_id = cp_award_id and
3216 fm.fund_id = aw.fund_id and
3217 fc.fund_code = fm.fund_code;
3218 l_fed_fund_code c_fed_fund_code%ROWTYPE;
3219
3220 CURSOR c_relationship (cp_person_number VARCHAR2,
3221 cp_b_person_number VARCHAR2)
3222 IS
3223 SELECT 'X'
3224 FROM hz_relationships pr,
3225 igs_pe_hz_parties pe,
3226 hz_parties br,
3227 hz_parties st
3228 WHERE
3229 br.party_number = cp_b_person_number
3230 AND st.party_number = cp_person_number
3231 AND pr.subject_id = st.party_id
3232 AND pr.object_id = br.party_id
3233 AND st.party_id = pe.party_id;
3234
3235 l_relationship c_relationship%ROWTYPE;
3236
3237 CURSOR c_disb_det(cp_award_id NUMBER,
3238 cp_disb_num NUMBER,
3239 cp_disb_seq_num NUMBER)
3240 IS
3241 SELECT 1
3242 FROM igf_db_awd_disb_dtl_all adisb
3243 WHERE adisb.award_id = cp_award_id
3244 AND adisb.disb_num = cp_disb_num
3245 AND adisb.disb_seq_num = cp_disb_seq_num;
3246
3247 l_disb_det c_disb_det%ROWTYPE;
3248
3249 CURSOR c_chk_loan_exist (cp_award_id NUMBER)
3250 IS
3251 SELECT
3252 rowid,
3253 loan_id ,
3254 loan_number,
3255 legacy_record_flag
3256 FROM igf_sl_loans_all
3257 WHERE award_id = cp_award_id;
3258
3259 l_chk_loan_exist c_chk_loan_exist%ROWTYPE;
3260
3261 CURSOR c_chk_loan (cp_loan_num VARCHAR2)
3262 IS
3263 SELECT
3264 rowid,
3265 award_id
3266 FROM igf_sl_loans_all
3267 WHERE loan_number = cp_loan_num ;
3268
3269 l_chk_loan c_chk_loan%ROWTYPE;
3270
3271 CURSOR c_alt_borw(cp_loan_id NUMBER)
3272 IS
3273 SELECT rowid
3274 FROM igf_sl_alt_borw_all
3275 WHERE loan_id = cp_loan_id;
3276
3277 l_alt_borw c_alt_borw%ROWTYPE;
3278
3279
3280 CURSOR c_sl_dl_setup(p_ci_cal_type VARCHAR2,
3281 p_ci_sequence_number NUMBER)
3282 IS
3283 SELECT orig_fee_perct_stafford,
3284 orig_fee_perct_plus
3285 FROM igf_sl_dl_setup
3286 WHERE ci_cal_type = p_ci_cal_type
3287 AND ci_sequence_number = p_ci_sequence_number;
3288
3289
3290 l_sl_dl_setup c_sl_dl_setup%ROWTYPE;
3291
3292 CURSOR c_int_disb_rec( cp_award_id NUMBER,
3293 cp_alternate_code VARCHAR2,
3294 cp_person_number VARCHAR2,
3295 cp_award_number_txt VARCHAR2,
3296 cp_loan_number VARCHAR2)
3297 IS
3298 SELECT disb_num,
3299 disb_seq_num
3300 FROM igf_db_awd_disb_dtl_all adisb
3301 WHERE adisb.award_id = cp_award_id
3302 AND (disb_num,disb_seq_num) NOT IN
3303 (
3304 SELECT disbursement_num,disbursement_seq_num
3305 FROM igf_sl_li_dldb_ints dlint
3306 WHERE dlint.ci_alternate_code = cp_alternate_code
3307 AND dlint.person_number = cp_person_number
3308 AND dlint.award_number_txt = cp_award_number_txt
3309 AND dlint.loan_number_txt = cp_loan_number
3310 );
3311
3312 l_int_disb_rec c_int_disb_rec%ROWTYPE;
3313
3314
3315 CURSOR c_person_id(cp_person_number VARCHAR2,
3316 p_party_type hz_parties.party_type%TYPE
3317 )
3318 IS
3319 SELECT PARTY_ID
3320 FROM hz_parties
3321 WHERE party_number = cp_person_number and party_type = p_party_type;
3322
3323
3324 CURSOR c_atd_rep_comb(p_atd_entity_id_txt VARCHAR2, p_rep_entity_id_txt VARCHAR2)
3325 IS
3326 SELECT atd.atd_entity_id_txt, rep.rep_entity_id_txt
3327 FROM igf_gr_attend_pell atd, igf_gr_report_pell rep
3328 WHERE atd.rcampus_id = rep.rcampus_id
3329 AND atd.atd_entity_id_txt = p_atd_entity_id_txt
3330 AND rep.rep_entity_id_txt = p_rep_entity_id_txt;
3331
3332 atd_rep_comb_rec c_atd_rep_comb%ROWTYPE;
3333
3334
3335 CURSOR c_chk_isir_dtls(p_base_id NUMBER, p_transaction_num NUMBER)
3336 IS
3337 SELECT isir_id
3338 FROM igf_ap_isir_matched_all
3339 WHERE TO_NUMBER(transaction_num) = p_transaction_num
3340 AND base_id = p_base_id;
3341
3342 chk_isir_dtls_rec c_chk_isir_dtls%ROWTYPE;
3343
3344 BEGIN
3345 igf_aw_gen.set_org_id(NULL);
3346 errbuf := NULL;
3347 retcode := 0;
3348 l_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
3349 l_seq_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
3350
3351 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
3352 l_processing := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','PROCESSING');
3353 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
3354 l_loan_disb := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','LOAN_DISB');
3355 l_loan_id_msg := igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','LOAN_ID');
3356
3357
3358 g_error_string := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG', 'ENTITY_NAME') ;
3359
3360 -- Get the Award Year Alternate Code
3361 OPEN c_alternate_code( l_cal_type, l_seq_number ) ;
3362 FETCH c_alternate_code INTO l_alternate_code ;
3363 CLOSE c_alternate_code ;
3364
3365 -- Log input parameters
3366 log_input_params( p_batch_id, l_alternate_code , p_delete_flag);
3367
3368
3369
3370 OPEN c_award_year(l_cal_type ,l_seq_number );
3371 FETCH c_award_year INTO l_award_year;
3372 CLOSE c_award_year;
3373
3374 g_award_year := l_award_year.batch_year;
3375
3376 -- Check if the profiles are set
3377 -- if country code is not'US' AND does not participate in financial aidprogram THEN
3378 -- write into the log file and exit process
3379 l_chk_profile := igf_ap_gen.check_profile;
3380
3381 IF l_chk_profile = 'N' THEN
3382 fnd_message.set_name('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
3383 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3384 RETURN;
3385 END IF;
3386
3387 -- Check If the Batch Entered is Valid or Not. If not Valid then error out
3388 l_batch_valid := igf_ap_gen.check_batch ( p_batch_id, 'LOANS') ;
3389 IF NVL(l_batch_valid,'N') <> 'Y' THEN
3390 -- Bug # 4008991
3391 fnd_message.set_name('IGF','IGF_SL_GR_BATCH_DOES_NO_EXIST');
3392 fnd_message.set_token('BATCH_ID',p_batch_id);
3393 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3394 RETURN;
3395 END IF;
3396
3397 -- Check If the Award Year Entered is Valid or Not. If not Valid then error out
3398 OPEN c_award_det(l_cal_type,l_seq_number);
3399 FETCH c_award_det INTO l_award_det;
3400 IF c_award_det%NOTFOUND THEN
3401 fnd_message.set_name('IGF','IGF_AP_AWD_YR_NOT_FOUND');
3402 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3403 CLOSE c_award_det;
3404 RETURN;
3405 ELSIF l_award_det.status NOT IN ('LD','O') THEN
3406 fnd_message.set_name('IGF','IGF_AP_LG_INVALID_STAT');
3407 fnd_message.set_token('AWARD_STATUS',l_award_det.status);
3408 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3409 CLOSE c_award_det;
3410 RETURN;
3411 ELSE
3412 CLOSE c_award_det;
3413 END IF;
3414
3415 l_award_year_status := l_award_det.status ;
3416
3417 IF (l_award_year_status = 'O') THEN
3418 lv_flag_lo := TRUE;
3419 ELSE
3420 lv_flag_lo := FALSE;
3421 END IF;
3422
3423 FOR l_interface IN c_interface(p_batch_id,l_alternate_code,'U','R') LOOP
3424
3425 BEGIN
3426 SAVEPOINT sp1;
3427
3428 l_total_record_cnt := l_total_record_cnt + 1;
3429 -- Initialize the variables
3430 l_valid_for_dml := 'Y' ;
3431 lv_person_id := NULL;
3432 lv_base_id := NULL;
3433 l_award_ref := NULL;
3434 l_act_isir := NULL;
3435 l_fed_fund_code := NULL;
3436 l_disb_interface := NULL;
3437 ln_loan_id := NULL;
3438 ln_origination_id := NULL;
3439 ln_lor_resp_num := NULL;
3440 ln_dbth_id := NULL;
3441 l_debug_str := NULL;
3442 l_b_person_id := NULL;
3443
3444
3445
3446
3447
3448 -- Initialize lb_write_log for writing into debug log table
3449 IF fnd_log.TEST(FND_LOG.LEVEL_STATEMENT,'IGF_SL_DL_LI_IMP_PKG') THEN
3450 lb_write_log := TRUE;
3451 END IF;
3452 fnd_file.put_line(fnd_file.log,l_processing ||' '||l_person_number||' '||l_interface.person_number);
3453 fnd_file.new_line(fnd_file.log,1);
3454 -- check if person exists in oss
3455 igf_ap_gen.check_person(l_interface.person_number,l_cal_type,l_seq_number,lv_person_id,lv_base_id);
3456 l_debug_str := l_debug_str ||' Processing for person number ' || l_interface.person_number;
3457 IF lv_person_id IS NULL THEN
3458 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
3459 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3460 l_valid_for_dml := 'N' ;
3461 l_error_flag := TRUE;
3462 END IF;
3463 l_debug_str := l_debug_str || 'check if person exists in oss- completed';
3464 -- check if Base record exists in oss
3465
3466 IF lv_base_id IS NULL THEN
3467 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
3468 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3469 l_valid_for_dml := 'N' ;
3470 l_error_flag := TRUE;
3471 END IF;
3472
3473 l_debug_str := l_debug_str || 'check if Base record exists in oss - completed';
3474 l_debug_str := l_debug_str || 'Processing for loan number '|| l_interface.loan_number_txt ;
3475 fnd_file.put_line(fnd_file.log,l_processing ||' '||l_loan_id_msg||' '||l_interface.loan_number_txt);
3476 fnd_file.new_line(fnd_file.log,1);
3477
3478 -- FA 149 Enhancements
3479 -- Check if the award year is COD-XML or not
3480 IF (igf_sl_dl_validation.check_full_participant(l_cal_type,l_seq_number,'DL')) THEN
3481 -- Incase of COD-XML award year support loans with ready to send status
3482 IF l_interface.loan_status_code <> 'G' THEN
3483 fnd_message.set_name('IGF','IGF_SL_STATUS_NOT_RDY');
3484 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3485 l_valid_for_dml := 'N' ;
3486 l_error_flag := TRUE;
3487 END IF;
3488
3489 -- Attending and Reporting Pell entity ids must not be null and their combination should be valid
3490
3491 IF (l_interface.atd_entity_id_txt IS NULL) OR (l_interface.rep_entity_id_txt IS NULL ) THEN
3492 fnd_message.set_name('IGF','IGF_SL_ATD_REP_PELL_NOT_CORR');
3493 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3494 l_valid_for_dml := 'N' ;
3495 l_error_flag := TRUE;
3496
3497 ELSE
3498 -- Check if their combination is valid
3499 OPEN c_atd_rep_comb(l_interface.atd_entity_id_txt, l_interface.rep_entity_id_txt);
3500 FETCH c_atd_rep_comb INTO atd_rep_comb_rec;
3501
3502 IF c_atd_rep_comb%NOTFOUND THEN
3503 fnd_message.set_name('IGF','IGF_SL_ATD_REP_PELL_NOT_CORR');
3504 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3505 l_valid_for_dml := 'N' ;
3506 l_error_flag := TRUE;
3507 END IF;
3508 CLOSE c_atd_rep_comb;
3509 END IF;
3510
3511 -- Transaction number must have a not null value between 1 and 99
3512 IF (l_interface.transaction_num IS NULL) OR (l_interface.transaction_num < 1 ) OR (l_interface.transaction_num > 99) THEN
3513 fnd_message.set_name('IGF','IGF_AP_TRANS_NUM_INVLD');
3514 fnd_message.set_token('TRNM',l_interface.transaction_num);
3515 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3516 l_valid_for_dml := 'N' ;
3517 l_error_flag := TRUE;
3518 ELSE
3519 -- Person should have an ISIR with the said transaction number
3520 OPEN c_chk_isir_dtls(lv_base_id, l_interface.transaction_num);
3521 FETCH c_chk_isir_dtls INTO chk_isir_dtls_rec ;
3522
3523 IF c_chk_isir_dtls%NOTFOUND THEN
3524 fnd_message.set_name('IGF','IGF_AP_ISIR_DTLS_NOT_FOUND');
3525 fnd_message.set_token('STUD',l_interface.person_number);
3526 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3527 l_valid_for_dml := 'N' ;
3528 l_error_flag := TRUE;
3529 END IF;
3530
3531 CLOSE c_chk_isir_dtls;
3532 END IF;
3533
3534
3535 END IF; -- Check if the award year is COD-XML or not
3536
3537 -- check if corresponding award is present in the awards table
3538
3539 OPEN c_award_ref(lv_base_id,l_interface.award_number_txt);
3540 FETCH c_award_ref INTO l_award_ref;
3541 IF (c_award_ref%NOTFOUND) THEN
3542 CLOSE c_award_ref;
3543 fnd_message.set_name('IGF','IGF_SL_CL_LI_NO_AW_REF');
3544 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3545 l_valid_for_dml := 'N' ;
3546 l_error_flag := TRUE;
3547 ELSE
3548 CLOSE c_award_ref;
3549 l_award_id := l_award_ref.award_id;
3550 IF l_award_ref.award_status = 'CANCELLED' THEN
3551 fnd_message.set_name('IGF','IGF_SL_TERMINATED_LOAN');
3552 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3553 l_valid_for_dml := 'N' ;
3554 l_error_flag := TRUE;
3555 l_award_id := l_award_ref.award_id;
3556 ELSE
3557 l_award_id := l_award_ref.award_id;
3558 END IF;
3559 END IF;
3560 l_debug_str := l_debug_str || ' Processing for person number ' || l_interface.person_number ||' And Award id ' || TO_CHAR(l_award_id) ;
3561 l_debug_str := l_debug_str || 'check if Base record exists in oss - completed';
3562
3563 -- check for active isir only if open award year
3564
3565 IF ( lv_flag_lo = TRUE ) THEN
3566 OPEN c_act_isir(lv_base_id,'Y');
3567 FETCH c_act_isir INTO l_act_isir;
3568 IF (c_act_isir%NOTFOUND) THEN
3569 CLOSE c_act_isir;
3570 fnd_message.set_name('IGF','IGF_AP_PAY_ISIR_EXCEED_ONE');
3571 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3572 l_valid_for_dml := 'N' ;
3573 l_error_flag := TRUE;
3574 ELSE
3575 CLOSE c_act_isir;
3576 END IF;
3577 END IF;
3578 l_debug_str := l_debug_str || 'check for active isir only if open award year - completed';
3579
3580 -- Check if Fed_fund_Code in ('DLP','DLU','DLS'). If Not exist then error out
3581
3582 OPEN c_fed_fund_code(l_award_id);
3583 FETCH c_fed_fund_code INTO l_fed_fund_code;
3584 CLOSE c_fed_fund_code;
3585 IF l_fed_fund_code.fed_fund_code IS NULL OR l_fed_fund_code.fed_fund_code NOT IN ('DLP','DLU','DLS') THEN
3586 fnd_message.set_name('IGF','IGF_SL_CL_INV_FED_FND_CD');
3587 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3588 l_valid_for_dml := 'N' ;
3589 l_error_flag := TRUE;
3590 ELSE
3591 lv_fed_fund_code := l_fed_fund_code.fed_fund_code ;
3592 END IF;
3593 l_debug_str := l_debug_str || 'Check if Fed_fund_Code in (DLP,DLU,DLS) - completed';
3594
3595 -- check if FED_FUND_CODE is 'DLP' AND the BORR_PERSON_NUMBER is NULL then error out
3596
3597 IF l_fed_fund_code.fed_fund_code = 'DLP' THEN
3598 IF l_interface.borr_person_number IS NULL THEN
3599 fnd_message.set_name('IGF','IGF_SL_CL_BOR_NUM_REQD');
3600 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3601 l_valid_for_dml := 'N' ;
3602 l_error_flag := TRUE;
3603 ELSE
3604 OPEN c_relationship(l_interface.person_number,l_interface.borr_person_number);
3605 FETCH c_relationship INTO l_relationship;
3606
3607 IF (c_relationship%NOTFOUND) THEN
3608 fnd_message.set_name('IGF','IGF_SL_CL_INV_BOR_REL');
3609 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3610 l_valid_for_dml := 'N' ;
3611 l_error_flag := TRUE;
3612
3613 -- Fetch the Borrow person ID to check if party exists
3614 OPEN c_person_id(l_interface.borr_person_number,'PERSON');
3615 FETCH c_person_id INTO l_b_person_id;
3616 CLOSE c_person_id;
3617 IF l_b_person_id IS NULL THEN
3618 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
3619 fnd_file.put_line(fnd_file.log, RPAD(l_error,12) || g_error_string|| ' ' || 'BORR_PERSON_NUMBER' ||' ' ||fnd_message.get);
3620 END IF;
3621
3622 CLOSE c_relationship;
3623 ELSE
3624 CLOSE c_relationship;
3625 -- Fetch the Borrow person ID
3626 OPEN c_person_id(l_interface.borr_person_number,'PERSON');
3627 FETCH c_person_id INTO l_b_person_id;
3628 CLOSE c_person_id;
3629
3630 END IF;
3631 END IF;
3632 ELSE
3633 IF l_interface.borr_person_number IS NOT NULL THEN
3634 fnd_message.set_name('IGF','IGF_SL_CL_BORW_NOT_REQD');
3635 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3636 l_valid_for_dml := 'N' ;
3637 l_error_flag := TRUE;
3638 END IF;
3639 END IF;
3640
3641 -- validate loan origination record
3642 validate_loan_orig_int(l_interface,l_award_id,p_status,g_igf_sl_message_table);
3643
3644
3645
3646 l_debug_str := l_debug_str || ' validate loan origination record - completed ';
3647 -- If record is invalid print the error mesages to the log file
3648 IF p_status = FALSE THEN
3649 l_valid_for_dml := 'N';
3650 l_error_flag := TRUE;
3651 print_message(g_igf_sl_message_table);
3652 ELSE -- Collect the value for ORIG_FEE_PERCT from DL setup if ORIG_FEE_PERCT is null
3653 l_school_code := SUBSTR(l_interface.loan_number_txt,13,6);
3654 IF l_interface.orig_fee_perct_num IS NULL THEN
3655 OPEN c_sl_dl_setup(l_cal_type,l_seq_number);
3656 FETCH c_sl_dl_setup INTO l_sl_dl_setup;
3657 CLOSE c_sl_dl_setup;
3658 IF lv_fed_fund_code = 'DLP' THEN
3659 l_interface.orig_fee_perct_num := l_sl_dl_setup.orig_fee_perct_plus;
3660 ELSE
3661 l_interface.orig_fee_perct_num := l_sl_dl_setup.orig_fee_perct_stafford;
3662 END IF;
3663 END IF;
3664 END IF;
3665
3666
3667 -- if record is valid then validate corresponding disbursement records
3668 IF l_valid_for_dml = 'Y' AND p_status = TRUE THEN
3669 p_d_status := TRUE;
3670 p_d_status2 := TRUE;
3671
3672 l_debug_str := l_debug_str || ' fetching disbursement records ';
3673 l_num_of_disb_rec := 0;
3674
3675 FOR l_disb_interface IN c_disb_interface(l_interface.ci_alternate_code,l_interface.person_number,l_interface.award_number_txt,l_interface.loan_number_txt) LOOP
3676 l_num_of_disb_rec := l_num_of_disb_rec + 1 ;
3677 IF l_disb_interface.acknowledgement_date IS NOT NULL THEN
3678 OPEN c_disb_det(l_award_id,l_disb_interface.disbursement_num,l_disb_interface.disbursement_seq_num);
3679 FETCH c_disb_det INTO l_disb_det;
3680 IF (c_disb_det%NOTFOUND) THEN
3681 CLOSE c_disb_det;
3682 l_valid_for_dml := 'N' ;
3683 l_error_flag := TRUE;
3684 fnd_message.set_name('IGF','IGF_SL_DISB_SEQ_NO_EXIST');
3685 fnd_message.set_token('DISB_NUM',l_disb_interface.disbursement_num);
3686 fnd_message.set_token('SEQ_NUM',l_disb_interface.disbursement_seq_num);
3687 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3688 p_d_status := FALSE;
3689 p_d_status2 := FALSE;
3690 ELSE
3691 -- Validate Disbursement record
3692 CLOSE c_disb_det;
3693 l_debug_str := l_debug_str || ' validating disbursement records with Disbursement Number : ' ||
3694 TO_CHAR(l_disb_interface.disbursement_num) || ' And disbursement Sequence Number : ' || TO_CHAR(l_disb_interface.disbursement_seq_num) ;
3695
3696 fnd_message.set_name('IGF','IGF_SL_VAL_DB_SEQ_NUM');
3697 fnd_message.set_token('DISB_NUM',l_disb_interface.disbursement_num);
3698 fnd_message.set_token('SEQ_NUM',l_disb_interface.disbursement_seq_num);
3699 fnd_file.put_line(fnd_file.log, fnd_message.get);
3700 fnd_file.new_line(fnd_file.log,1);
3701 validate_loan_disb( l_disb_interface,l_award_id, p_d_status,g_igf_sl_message_table);
3702
3703 IF p_d_status = FALSE THEN
3704 p_d_status2 := FALSE;
3705 print_message(g_igf_sl_message_table);
3706 l_error_flag := TRUE;
3707 l_valid_for_dml := 'N' ;
3708 END IF;
3709 END IF;
3710 END IF;
3711 END LOOP;
3712
3713 -- Since p_d_status will hold the value of only the last disbursement
3714 -- p_d_status2 is used which will become false if any 1 disb fails
3715
3716 p_d_status := p_d_status2;
3717
3718 -- If no disbursement record found in Interface table then log message
3719 IF l_num_of_disb_rec <> 0 THEN
3720 OPEN c_int_disb_rec( l_award_id,
3721 l_interface.ci_alternate_code,
3722 l_interface.person_number,
3723 l_interface.award_number_txt,
3724 l_interface.loan_number_txt);
3725 FETCH c_int_disb_rec INTO l_int_disb_rec;
3726
3727 IF c_int_disb_rec%FOUND THEN
3728 CLOSE c_int_disb_rec;
3729 l_valid_for_dml := 'N' ;
3730 l_error_flag := TRUE;
3731 fnd_message.set_name('IGF','IGF_SL_AC_DISB_SEQ_NO_EXIST');
3732 fnd_message.set_token('DISB_NUM',l_int_disb_rec.disb_num);
3733 fnd_message.set_token('SEQ_NUM',l_int_disb_rec.disb_seq_num);
3734 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3735 ELSE
3736 CLOSE c_int_disb_rec;
3737 END IF;
3738 END IF;
3739 END IF;
3740
3741
3742 -- check valid for dml and process status
3743 IF l_valid_for_dml = 'Y' AND p_status = TRUE AND p_d_status = TRUE THEN
3744
3745 -- check for duplicate loan number
3746 OPEN c_chk_loan(l_interface.loan_number_txt);
3747 FETCH c_chk_loan INTO l_chk_loan;
3748 CLOSE c_chk_loan;
3749 IF l_chk_loan.award_id <> l_award_id THEN
3750 fnd_message.set_name('IGF','IGF_SL_DUP_LOAN');
3751 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3752 l_error_flag := TRUE;
3753 END IF;
3754
3755 IF NOT l_error_flag THEN
3756 -- check import record type 'U'
3757 IF (NVL(l_interface.import_record_type,'X') = 'U' ) THEN
3758 OPEN c_chk_loan_exist(l_award_id);
3759 FETCH c_chk_loan_exist INTO l_chk_loan_exist;
3760 -- check loan exists
3761 IF (c_chk_loan_exist%NOTFOUND) THEN
3762 CLOSE c_chk_loan_exist;
3763 fnd_message.set_name('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
3764 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3765 l_error_flag := TRUE;
3766 ELSE
3767 CLOSE c_chk_loan_exist;
3768 lv_loan_id := l_chk_loan_exist.loan_id;
3769 lv_rowid := l_chk_loan_exist.ROWID;
3770 lv_loan_num := l_chk_loan_exist.loan_number;
3771 l_legacy_flag := l_chk_loan_exist.legacy_record_flag;
3772 -- if record exists and legacy flag is not set then error out
3773 IF (lv_flag_lo = TRUE) AND (NVL(l_legacy_flag,'N') = 'N') THEN
3774 fnd_message.set_name('IGF','IGF_SL_CL_UPD_OPEN');
3775 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3776 l_error_flag := TRUE;
3777 ELSE
3778
3779 delete_context_records(lv_loan_id,lv_loan_num,lv_rowid);
3780 l_debug_str := l_debug_str || ' deleted all context records for import record type U ';
3781
3782 insert_context_records(l_interface,l_award_id,lv_base_id,lv_loan_num);
3783 FOR l_disb_interface IN c_disb_interface(l_interface.ci_alternate_code,l_interface.person_number,l_interface.award_number_txt,l_interface.loan_number_txt) LOOP
3784 -- unless the loan origination is acknowledged no disbursement needs to be imported
3785 IF ln_dbth_id IS NOT NULL THEN
3786 insert_context_disb_records(l_disb_interface,l_award_id);
3787 END IF;
3788 END LOOP;
3789 l_debug_str := l_debug_str || ' inserted all context records for import record type U ';
3790
3791 END IF; -- check for legacy flag
3792 END IF; -- check loan exists
3793 ELSE --update flag check 'U'
3794 OPEN c_chk_loan_exist(l_award_id);
3795 FETCH c_chk_loan_exist INTO l_chk_loan_exist;
3796 IF (c_chk_loan_exist%FOUND) THEN
3797 CLOSE c_chk_loan_exist;
3798 fnd_message.set_name('IGF','IGF_SL_CL_RECORD_EXIST');
3799 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
3800 l_error_flag := TRUE;
3801 ELSE
3802 CLOSE c_chk_loan_exist;
3803 lv_loan_id := l_chk_loan_exist.loan_id;
3804 lv_loan_num := l_chk_loan_exist.loan_number;
3805 insert_context_records(l_interface,l_award_id,lv_base_id,lv_loan_num);
3806 FOR l_disb_interface IN c_disb_interface(l_interface.ci_alternate_code,l_interface.person_number,l_interface.award_number_txt,l_interface.loan_number_txt) LOOP
3807 -- unless the loan origination is acknowledged no disbursement needs to be imported
3808 IF ln_dbth_id IS NOT NULL THEN
3809 insert_context_disb_records( l_disb_interface,l_award_id);
3810 END IF;
3811 END LOOP;
3812 l_debug_str := l_debug_str || ' inserted all context records for import record type not equal U ';
3813 END IF;
3814
3815 END IF;-- check import record type 'U'
3816 END IF; -- if not error then
3817 END IF; -- check valid for dml and process status
3818
3819 EXCEPTION
3820 WHEN IMPORT_ERROR THEN
3821 l_error_flag := TRUE;
3822 fnd_message.set_name('IGF','IGF_SL_CL_LI_UPD_FLD');
3823 fnd_file.put_line(fnd_file.log,RPAD(l_error,11) || fnd_message.get);
3824 fnd_file.new_line(fnd_file.log,1);
3825 ROLLBACK TO sp1;
3826
3827 WHEN OTHERS THEN
3828 RAISE;
3829
3830 END; -- end of first block for exception handling, if exception occurs it will rollback to the savepoint sp1
3831
3832 BEGIN -- Block for updating and deleting Interface Record
3833 IF l_error_flag = TRUE
3834 OR p_status = FALSE
3835 OR p_d_status = FALSE THEN
3836 l_error_flag := FALSE;
3837 -- update the legacy interface table column import_status to 'E'
3838 l_debug_str := l_debug_str || 'Before update of interface table : status E ';
3839 UPDATE igf_sl_li_dlor_ints
3840 SET import_status_type = 'E',
3841 last_update_date = SYSDATE,
3842 last_update_login = fnd_global.login_id,
3843 request_id = fnd_global.conc_request_id,
3844 program_id = fnd_global.conc_program_id,
3845 program_application_id = fnd_global.prog_appl_id,
3846 program_update_date = SYSDATE
3847 WHERE ROWID = l_interface.ROWID;
3848 ELSE
3849
3850 IF p_delete_flag = 'Y' THEN
3851
3852 DELETE
3853 FROM igf_sl_li_chg_ints slchg
3854 WHERE slchg.loan_number_txt = l_interface.loan_number_txt;
3855
3856 l_debug_str := l_debug_str || ' Before deleting disb interface table record ';
3857 DELETE
3858 FROM igf_sl_li_dldb_ints
3859 WHERE ci_alternate_code = l_disb_interface.ci_alternate_code
3860 AND person_number = l_disb_interface.person_number
3861 AND award_number_txt = l_disb_interface.award_number_txt
3862 AND loan_number_txt = l_disb_interface.loan_number_txt;
3863
3864 l_debug_str := l_debug_str || ' Before deleting orig interface table record ';
3865
3866 DELETE
3867 FROM igf_sl_li_dlor_ints
3868 WHERE ROWID = l_interface.ROWID;
3869
3870 l_debug_str := l_debug_str || ' After deleting orig interface table record ';
3871
3872 ELSE
3873 -- update the legacy interface table column import_status to 'I'
3874 l_debug_str := l_debug_str || ' Before update of interface table : status I ';
3875 UPDATE igf_sl_li_dlor_ints
3876 SET import_status_type = 'I',
3877 last_update_date = SYSDATE,
3878 last_update_login = fnd_global.login_id,
3879 request_id = fnd_global.conc_request_id,
3880 program_id = fnd_global.conc_program_id,
3881 program_application_id = fnd_global.prog_appl_id,
3882 program_update_date = SYSDATE
3883 WHERE ROWID = l_interface.ROWID;
3884
3885 l_debug_str := l_debug_str || ' After update of interface table : status I ';
3886 END IF;
3887 l_success_record_cnt := l_success_record_cnt + 1;
3888 fnd_message.set_name('IGF','IGF_SL_LI_IMP_SUCCES');
3889 fnd_file.put_line(fnd_file.log, fnd_message.get);
3890
3891 END IF;
3892 fnd_file.new_line(fnd_file.log,1);
3893 -- Write debug messages
3894 IF lb_write_log THEN
3895 IF g_request_id IS NULL THEN
3896 g_request_id := fnd_global.conc_request_id;
3897 END IF;
3898 fnd_log.string_with_context(FND_LOG.LEVEL_STATEMENT,'IGF_SL_DL_LI_IMP_PKG', l_debug_str,NULL,NULL,NULL,NULL,NULL,TO_CHAR(g_request_id));
3899 END IF;
3900
3901 END; -- block for updating and deleting interface records
3902 END LOOP;
3903
3904 IF l_total_record_cnt = 0 THEN
3905 fnd_message.set_name('IGF','IGF_SL_DL_LI_NO_RECORDS');
3906 fnd_message.set_token('AID_YR', l_alternate_code);
3907 fnd_message.set_token('BATCH_ID',p_batch_id);
3908 fnd_file.put_line(fnd_file.log,RPAD(l_error,11) || fnd_message.get);
3909 RETURN;
3910 END IF;
3911 -- Print in the out put file the total number of records successfully imported.
3912
3913 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_PROCESSED'), 40) || ' : ' || TO_CHAR(l_total_record_cnt));
3914 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_SUCCESSFUL'), 40) || ' : ' || TO_CHAR(l_success_record_cnt));
3915 fnd_file.put_line(fnd_file.output, RPAD(igf_ap_gen.get_lookup_meaning('IGF_GE_PARAMETERS','RECORDS_REJECTED'), 40) || ' : ' || TO_CHAR(l_total_record_cnt - l_success_record_cnt));
3916
3917
3918 EXCEPTION
3919
3920 WHEN others THEN
3921 ROLLBACK;
3922 fnd_log.string_with_context(FND_LOG.LEVEL_STATEMENT,'IGF_SL_DL_LI_IMP_PKG', l_debug_str || SQLERRM,NULL,NULL,NULL,NULL,NULL,TO_CHAR(g_request_id));
3923 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3924 fnd_message.set_token('NAME','IGF_SL_DL_LI_IMP_PKG.RUN');
3925 fnd_file.put_line(fnd_file.log,fnd_message.get || sqlerrm);
3926 retcode := 2;
3927 errbuf := fnd_message.get;
3928 igs_ge_msg_stack.conc_exception_hndl;
3929 END run;
3930
3931 END IGF_SL_DL_LI_IMP_PKG;