[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_ORIG
Source
1 PACKAGE BODY igf_sl_dl_orig AS
2 /* $Header: IGFSL03B.pls 120.2 2006/04/17 04:59:46 akomurav noship $ */
3
4 PROCEDURE dl_originate(errbuf OUT NOCOPY VARCHAR2,
5 retcode OUT NOCOPY NUMBER,
6 p_award_year VARCHAR2,
7 p_dl_loan_catg igf_lookups_view.lookup_code%TYPE,
8 p_loan_number igf_sl_loans_all.loan_number%TYPE,
9 p_org_id IN NUMBER,
10 school_type IN VARCHAR2,
11 p_school_code IN VARCHAR2)
12 AS
13 --
14 -----------------------------------------------------------------------------------
15 --
16 -- Created By : venagara
17 -- Date Created On : 2000/11/13
18 -- Purpose :
19 -- Know limitations, enhancements or remarks
20 -- Change History:
21 -- Procedure dl_originate :
22 -- User inputs are
23 -- Award year (required) : Consists of Cal_type and sequence_number concatenated
24 -- DL_LOAN_TYPE : Valid values are DL_STAFFORD/DL_PLUS.
25 -- Lookup_type = 'IGF_SL_DL_LOAN_CATG' for the above
26 -- LOAN_NUMBER : Can be a particular Loan_number or can be NULL, if the
27 -- user wants to select all loan_numbers.
28 --
29 -----------------------------------------------------------------------------------
30 -- Who When What
31 -----------------------------------------------------------------------------------
32 -- akomurav 17-Apr-2006 Build FA161 and 162
33 -- TBH Impact change in igf_sl_lor_loc_pkg.insert_row()
34 -- and igf_sl_lor_pkg.update_row().
35 -------------------------------------------------------------------------------------
36 -- museshad 20-Feb-2005 Bug 5045452 - SQL Repository Issue.
37 -- Modified the cursor c_loans for better performance
38 -------------------------------------------------------------------------------------
39 -- pssahni 22-Dec-2004 Bug# 4081177 This process runs only for phase_in_participant
40 -------------------------------------------------------------------------------------
41 -- svuppala 4-Nov-2004 #3416936 FA 134 TBH impacts for newly added columns
42
43 -- veramach 04-May-2004 bug 3603289
44 -- modified cursor c_fabase to select only dependency_status from ISIR.Other details
45 -- are derived from igf_sl_gen.get_person_details
46 -----------------------------------------------------------------------------------
47 -- ugummall 23-OCT-2003 Bug 3102439. FA 126 Multiple FA Offices.
48 -- Modified the cursor cur_loan_details to include the clause which
49 -- filter only the loans having the school id matched with parameter p_school_code.
50 -----------------------------------------------------------------------------------
51 -- ugummall 17-OCT-2003 Bug 3102439. FA 126 Multiple FA Offices.
52 -- 1. Added two new parameters to dl_originate process.
53 -- 2. Passed the parameter p_school_code to DLHeader_cur
54 -- as extra parameter
55 -- 3. Processed only those students whose associated org unit
56 -- has an alternate identifier of Direct Loan School Code and it
57 -- is matching with the supplied p_school_code parameter.
58 -----------------------------------------------------------------------------------
59 -- sjadhav 7-Oct-2003 Bug 3104228 FA 122 Added cur loan details loop
60 -- Removed ref to obsolete columns
61 -----------------------------------------------------------------------------------
62 -- bkkumar 06-oct-2003 Bug 3104228 FA 122 Loans Enhancements
63 -- a) Impact of adding the relationship_cd
64 -- in igf_sl_lor_all table and obsoleting
65 -- BORW_LENDER_ID, DUNS_BORW_LENDER_ID,
66 -- GUARANTOR_ID, DUNS_GUARNT_ID,
67 -- LENDER_ID, DUNS_LENDER_ID
68 -- LEND_NON_ED_BRC_ID, RECIPIENT_ID
69 -- RECIPIENT_TYPE,DUNS_RECIP_ID
70 -- RECIP_NON_ED_BRC_ID columns.
71 -- b) Also the DUNS_BORW_LENDER_ID
72 -- DUNS_GUARNT_ID
73 -- DUNS_LENDER_ID
74 -- DUNS_RECIP_ID columns are osboleted from the
75 -- igf_sl_lor_loc_all table.
76 -----------------------------------------------------------------------------------
77 -- bkkumar 30-sep-2003 Bug 3104228 FA 122 Loans Enhancements
78 -- Changed the cursor c_tbh_cur
79 -- containing igf_sl_lor_dtls_v with simple
80 -- joins and got the details of student and parent
81 -- from igf_sl_gen.get_person_details.
82 -- Added the debugging log messages.
83 -- OBSOLETED FIELDS ::
84 -- A) Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
85 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
86 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
87 -- chg_batch_id,appl_send_error_codes from igf_sl_lor
88 -- B) Obsoleted lend_apprv_denied_code,lend_apprv_denied_date,cl_rec_status_last_update,
89 -- cl_rec_status,mpn_confirm_code,appl_loan_phase_code_chg,appl_loan_phase_code,
90 -- p_ssn_chg_date,p_dob_chg_date,s_ssn_chg_date,s_dob_chg_date,s_local_addr_chg_date,
91 -- chg_batch_id from igf_sl_lor_loc
92 --------------------------------------------------------------------------------------------------
93 -- gmuralid 03-07-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
94 -- Added legacy record flag as parameter to
95 -- igf_sl_loans_pkg
96 -----------------------------------------------------------------------------------
97 -- sjadhav 26-Mar-2003 Bug 2863960
98 -- Modified routine to populate disb gross
99 -- amount in the igf_sl_awd_disb_loc table with
100 -- disb accepted amount
101 -----------------------------------------------------------------------------------
102 -- masehgal # 2593215 removed begin/end dates fetching functions
103 -- used procedure get_acad_cal_dtls instead
104 -----------------------------------------------------------------------------------
105 -- mesriniv 1-jul-2002 Added code for inserting Student Phone/Parent Phone.
106 -----------------------------------------------------------------------------------
107 -- mesriniv 21-jun-2002 Bug :- 2426609 SSN Format Incorrect in Output File
108 -- While inserting Student SSN/Parent SSN
109 -- formatting and substr of 9 chars is done.
110 -----------------------------------------------------------------------------------
111 -- mesriniv 23-APR-2002 Bug No:2332668 Desc:LOAN ORIGINATION PROCESS NOT
112 -- RUNNING SUCCESSFULLY.
113 -- Added code to display the Parameters Passed
114 -----------------------------------------------------------------------------------
115 -- agairola 15-Mar-2002 Modified the Update Row call of the IGF_SL_LOANS_PKG
116 -- to include Borrower Determination as part of
117 -- Refunds 2144600
118 -----------------------------------------------------------------------------------
119 -- adhawan 19th feb 2002 Bug:2216956
120 -- added elec_mpn_ind , borr_sign_ind ,
121 -- stud_sign_ind, borr_credit_auth_code
122 -- in the call to igf_sl_lor tbh
123 -----------------------------------------------------------------------------------
124 --
125 lv_cal_type igs_ca_inst.cal_type%TYPE;
126 lv_cal_seq_num igs_ca_inst.sequence_number%TYPE;
127 lv_dl_version igf_lookups_view.lookup_code%TYPE;
128 lv_dbth_id igf_sl_dl_batch.dbth_id%TYPE;
129 lv_batch_id igf_sl_dl_batch.batch_id%TYPE;
130 lv_mesg_class igf_sl_dl_batch.message_class%TYPE;
131
132 lv_data_record VARCHAR2(4000);
133 lv_data_header_record VARCHAR2(4000);
134
135 lv_orig_award_id igf_aw_award.award_id%TYPE;
136 lv_orig_loan_id igf_sl_loans.loan_id%TYPE;
137 lv_orig_loan_num igf_sl_loans.loan_number%TYPE;
138 lv_num_of_rec NUMBER := 0;
139 lv_acad_begin_dt igs_ca_inst.start_dt%TYPE := NULL;
140 lv_acad_end_dt igs_ca_inst.end_dt%TYPE := NULL;
141 l_i NUMBER(1);
142 l_alternate_code igs_ca_inst.alternate_code%TYPE;
143 l_display VARCHAR2(1) := 'N';
144
145 -- masehgal # 2593215 added variables to retrieve values
146 lv_acad_cal_type igs_ps_ofr_inst.cal_type%TYPE := NULL;
147 lv_acad_seq_num igs_ps_ofr_inst.ci_sequence_number%TYPE := NULL;
148 lv_message VARCHAR2(100) := NULL ;
149
150 TYPE l_parameters IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
151 l_para_rec l_parameters;
152
153
154 -- REF Cursor Record Types.
155 Header_Rec igf_sl_dl_record.DLHeaderType;
156 Orig_Rec igf_sl_dl_record.DLOrigType;
157 Trailer_Rec igf_sl_dl_record.DLTrailerType;
158
159
160 lv_loan_id igf_sl_loans.loan_id%TYPE;
161 lv_bool BOOLEAN;
162 no_loan_data EXCEPTION;
163 yr_full_participant EXCEPTION;
164
165 CURSOR cur_loan_details (
166 cp_cal_type igs_ca_inst.cal_type%TYPE,
167 cp_seq_number igs_ca_inst.sequence_number%TYPE,
168 cp_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE,
169 cp_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE,
170 cp_loan_status igf_sl_loans.loan_status%TYPE,
171 cp_active igf_sl_loans.active%TYPE,
172 cp_dl_loan_number igf_sl_loans.loan_number%TYPE
173 )
174 IS
175 SELECT
176 loans.loan_number,
177 fabase.base_id
178 FROM
179 igf_sl_loans loans,
180 igf_sl_lor lor,
181 igf_aw_award awd,
182 igf_aw_fund_mast fmast,
183 igf_aw_fund_cat fcat,
184 igf_ap_fa_base_rec fabase,
185 igf_ap_isir_matched isr
186 WHERE
187 fabase.ci_cal_type = cp_cal_type AND
188 fabase.ci_sequence_number = cp_seq_number AND
189 fabase.base_id = awd.base_id AND
190 awd.fund_id = fmast.fund_id AND
191 fabase.base_id = isr.base_id AND
192 -- fabase.payment_isir_id = isr.isir_id AND
193 isr.payment_isir = 'Y' AND
194 isr.system_record_type = 'ORIGINAL' AND
195 fmast.fund_code = fcat.fund_code AND
196 (fcat.fed_fund_code = cp_fed_fund_1 OR fcat.fed_fund_code = cp_fed_fund_2) AND
197 loans.award_id = awd.award_id AND
198 loans.loan_number LIKE NVL(cp_dl_loan_number,loans.loan_number) AND
199 loans.loan_id = lor.loan_id AND
200 loans.loan_status = cp_loan_status AND
201 loans.active = cp_active AND
202 substr(loans.loan_number, 13, 6) = p_school_code;
203
204 loan_details_rec cur_loan_details%ROWTYPE;
205
206 l_fed_fund_1 igf_aw_fund_cat.fed_fund_code%TYPE;
207 l_fed_fund_2 igf_aw_fund_cat.fed_fund_code%TYPE;
208
209
210 CURSOR c_loans IS
211 SELECT loan.loan_id
212 FROM igf_sl_loans_all loan,
213 igf_aw_award_all awd,
214 igf_aw_fund_mast_all fmast,
215 igf_aw_fund_cat_all fcat
216 WHERE
217 loan.award_id = awd.award_id AND
218 awd.fund_id = fmast.fund_id AND
219 fcat.fund_code = fmast.fund_code AND
220 fmast.ci_cal_type = lv_cal_type AND
221 fmast.ci_sequence_number = lv_cal_seq_num AND
222 loan.loan_number LIKE DECODE(p_loan_number, NULL, '%', p_loan_number) AND
223 fcat.fed_fund_code in
224 (SELECT lookup_code FROM igf_lookups_view
225 WHERE lookup_type = decode(p_dl_loan_catg,
226 'DL_STAFFORD', 'IGF_SL_DL_STAFFORD',
227 'DL_PLUS', 'IGF_SL_DL_PLUS') AND
228 enabled_flag = 'Y') AND
229 loan.loan_status = 'V' AND -- "Valid and Ready to Send"
230 loan.active = 'Y';
231
232 --Cursor to fetch the Meaning for displaying parameters passed
233 --Used UNION ALL here since individual select clauses
234 --have the same cost
235 --Bug 2332668
236
237 CURSOR cur_get_parameters IS
238 SELECT meaning FROM igf_lookups_view
239 WHERE lookup_type='IGF_SL_DL_LOAN_CATG' AND lookup_code=p_dl_loan_catg AND enabled_flag = 'Y'
240
241 UNION ALL
242
243 SELECT meaning FROM igf_lookups_view
244 WHERE lookup_type='IGF_GE_PARAMETERS' AND lookup_code IN ('AWARD_YEAR','LOAN_CATG','LOAN_ID','PARAMETER_PASS') AND enabled_flag = 'Y';
245
246 --Cursor to get the alternate code for the calendar instance
247 --Bug 2332668
248 CURSOR cur_alternate_code IS
249 SELECT ca.alternate_code FROM igs_ca_inst ca
250 WHERE ca.cal_type =lv_cal_type
251 AND ca.sequence_number = lv_cal_seq_num;
252
253 -- Get the details of school meaning from lookups to print in the log file
254 CURSOR c_get_sch_code IS
255 SELECT meaning
256 FROM igs_lookups_view
257 WHERE lookup_type = 'OR_SYSTEM_ID_TYPE'
258 AND lookup_code = 'DL_SCH_CD'
259 AND enabled_flag = 'Y';
260 c_get_sch_code_rec c_get_sch_code%ROWTYPE;
261
262 BEGIN
263
264 retcode := 0;
265 igf_aw_gen.set_org_id(p_org_id);
266 lv_cal_type := rtrim(substr(p_award_year,1,10));
267 lv_cal_seq_num := rtrim(substr(p_award_year,11));
268
269 -- Check whether the award year is full participant
270 IF (igf_sl_dl_validation.check_full_participant (lv_cal_type, lv_cal_seq_num,'DL') ) THEN
271 -- Log an error message
272 fnd_message.set_name('IGF','IGF_SL_COD_NO_ORIG');
273 fnd_file.put_line(fnd_file.log,fnd_message.get);
274 raise yr_full_participant;
275 END IF;
276
277
278 --Get the alternate code
279 OPEN cur_alternate_code;
280 FETCH cur_alternate_code INTO l_alternate_code;
281 IF cur_alternate_code%NOTFOUND THEN
282 CLOSE cur_alternate_code;
283 fnd_message.set_name('IGF','IGF_SL_NO_CALENDAR');
284 IGS_GE_MSG_STACK.ADD;
285 fnd_file.put_line(fnd_file.log,fnd_message.get);
286
287 app_exception.raise_exception;
288 END IF;
289 CLOSE cur_alternate_code;
290
291 --Write the details of Parameters Passed into LOG File.
292 --Bug 2332668
293 l_i:=0;
294 OPEN cur_get_parameters;
295 LOOP
296 l_i:=l_i+1;
297 FETCH cur_get_parameters INTO l_para_rec(l_i);
298 EXIT WHEN cur_get_parameters%NOTFOUND;
299 END LOOP;
300 CLOSE cur_get_parameters;
301
302 OPEN c_get_sch_code; FETCH c_get_sch_code INTO c_get_sch_code_rec; CLOSE c_get_sch_code;
303
304 --Show the parameters passed
305 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(5),50,' '));
306 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(2),50,' ')||':'||RPAD(' ',4,' ')||l_alternate_code);
307 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(3),50,' ')||':'||RPAD(' ',4,' ')||l_para_rec(1));
308 IF (p_loan_number IS NOT NULL) THEN
309 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(4),50,' ')||':'||RPAD(' ',4,' ')||p_loan_number);
310 END IF;
311 fnd_file.put_line(fnd_file.log,RPAD(c_get_sch_code_rec.meaning,50,' ')||':'||RPAD(' ',4,' ')||p_school_code);
312
313 -- Get the Direct Loan File Spec Version
314 --Bug :-2490289 DL Header and Trailer Formatting Error.
315 --Handled the NO_DATA_FOUND exception if the DL Setup record
316 --is not available
317 BEGIN
318
319 lv_dl_version := igf_sl_gen.get_dl_version(lv_cal_type, lv_cal_seq_num);
320
321 EXCEPTION
322 WHEN NO_DATA_FOUND THEN
323
324 fnd_message.set_name('IGF','IGF_DB_DL_VERSION_FALSE');
325 fnd_file.put_line(fnd_file.log,fnd_message.get);
326
327 RAISE NO_DATA_FOUND;
328 END;
329
330
331 -- Validate the Loans, with Status "Ready to Send" before origination.
332 -- If the Loan is Valid, then dl_lar_validate procedure will set the Loan to
333 -- an intermediate status "Valid and Ready to Send". These records only be
334 -- Originated. Further, if any exception occurs while processing, then
335 -- this intermadiate status needs to be rolledback.
336
337 lv_bool := igf_sl_dl_validation.dl_lar_validate(lv_cal_type, lv_cal_seq_num,
338 p_dl_loan_catg, p_loan_number, 'JOB', p_school_code);
339
340
341 -- Create an origination File only if there are ACTIVE loan records
342 -- with Status="Valid and Ready to Send".
343 OPEN c_loans;
344 FETCH c_loans INTO lv_loan_id;
345 IF c_loans%NOTFOUND THEN
346 CLOSE c_loans;
347 RAISE no_loan_data;
348 END IF;
349 CLOSE c_loans;
350
351
352 -- Initialise the Data Record field
353 lv_data_record := NULL;
354 lv_data_header_record := NULL;
355
356
357 -- Using REF CURSORS.
358 -- Header Record specifications, for each Direct Loan Version
359 -- is specified in the igf_sl_dl_record.DLHeader_cur procedure.
360 -- By calling this procedure, the following are done
361 -- 1. Computes Batch ID
362 -- 2. Inserts the Batch ID details in igf_sl_dl_batch
363 -- 3. For the specified version, Opens a REF CURSOR, having
364 -- header file Specs.
365 igf_sl_dl_record.DLHeader_cur(lv_dl_version, p_dl_loan_catg,
366 lv_cal_type, lv_cal_seq_num, 'DL_ORIG_SEND', p_school_code,
367 lv_dbth_id, lv_batch_id, lv_mesg_class, Header_Rec);
368 FETCH Header_Rec into lv_data_header_record;
369 IF Header_Rec%NOTFOUND THEN
370 fnd_message.set_name ('IGF', 'IGF_GE_HDR_CREATE_ERROR');
371 igs_ge_msg_stack.add;
372 app_exception.raise_exception;
373 END IF;
374
375 -- FA 122 Debug log messages
376 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
377 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_orig.dl_originate.debug','loan_number passed to igf_sl_dl_record.get_acad_cal_dtls:'|| p_loan_number);
378 END IF;
379 -- Get Academic Dates for the Award Year.
380 -- masehgal # 2593215 removed begin/end dates fetching functions
381 -- used procedure get_acad_cal_dtls instead
382 igf_sl_dl_record.get_acad_cal_dtls ( p_loan_number,
383 lv_acad_cal_type,
384 lv_acad_seq_num,
385 lv_acad_begin_dt,
386 lv_acad_end_dt,
387 lv_message ) ;
388 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
389 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_orig.dl_originate.debug','lv_message got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_message);
390 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_orig.dl_originate.debug','lv_acad_begin_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_begin_dt);
391 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_dl_orig.dl_originate.debug','lv_acad_end_date got from igf_sl_dl_record.get_acad_cal_dtls:'|| lv_acad_end_dt);
392 END IF;
393
394 -- Opening REF Cursor for Transaction Record specification
395
396 IF p_dl_loan_catg = 'DL_STAFFORD' THEN
397 l_fed_fund_1 := 'DLS';
398 l_fed_fund_2 := 'DLU';
399 ELSIF p_dl_loan_catg = 'DL_PLUS' THEN
400 l_fed_fund_1 := 'DLP';
401 l_fed_fund_2 := 'DLP';
402 END IF;
403
404 FOR loan_details_rec IN cur_loan_details(lv_cal_type,lv_cal_seq_num,l_fed_fund_1,l_fed_fund_2,'V','Y',p_loan_number)
405 LOOP
406
407 igf_sl_dl_record.DLOrig_cur(lv_dl_version, p_dl_loan_catg,
408 lv_cal_type, lv_cal_seq_num, loan_details_rec.loan_number,
409 lv_batch_id, p_school_code, Orig_Rec);
410 LOOP
411 -- Initialise the Data Record field
412 lv_data_record := NULL;
413
414 FETCH Orig_Rec INTO lv_orig_award_id, lv_orig_loan_id,
415 lv_orig_loan_num, lv_data_record;
416 IF Orig_Rec%NOTFOUND THEN
417 -- If all the Transaction records are written to the File, then Exit.
418 EXIT;
419 END IF;
420
421 IF (substr(lv_data_record, 13, 6) = p_school_code) THEN
422 lv_num_of_rec := lv_num_of_rec + 1;
423 IF(lv_num_of_rec = 1)THEN
424 -- Write the Header Record into the Output file only there is atleast one record to process
425 -- write header only once.
426 fnd_file.put_line(FND_FILE.OUTPUT, lv_data_header_record);
427 END IF;
428
429 -- Write the Transaction Record into the Output File.
430 fnd_file.put_line(FND_FILE.OUTPUT, lv_data_record);
431
432
433
434 -- Delete this loan record in IGF_SL_LOR_LOC
435 DECLARE
436 lv_row_id VARCHAR2(25);
437 CURSOR c_tbh_cur IS
438 SELECT row_id row_id FROM igf_sl_lor_loc
439 WHERE loan_id = lv_orig_loan_id FOR UPDATE OF igf_sl_lor_loc.sch_cert_date NOWAIT;
440 BEGIN
441 FOR tbh_rec in c_tbh_cur LOOP
442 igf_sl_lor_loc_pkg.delete_row (tbh_rec.row_id);
443 END LOOP;
444 END;
445
446 -- Delete this loan record in IGF_SL_AWD_DISB_LOC.
447 DECLARE
448 lv_row_id VARCHAR2(25);
449 CURSOR c_tbh_cur IS
450 SELECT row_id row_id FROM igf_sl_awd_disb_loc
451 WHERE award_id = lv_orig_award_id FOR UPDATE OF igf_sl_awd_disb_loc.disb_date NOWAIT;
452 BEGIN
453 FOR tbh_rec in c_tbh_cur LOOP
454 igf_sl_awd_disb_loc_pkg.delete_row (tbh_rec.row_id);
455 END LOOP;
456 END;
457
458
459 -- Insert the Origination Data being sent to LOC, into "_LOC" tables
460 DECLARE
461
462 lv_row_id VARCHAR2(25);
463 lv_base_id igf_ap_fa_base_rec.base_id%TYPE;
464
465 student_dtl_cur igf_sl_gen.person_dtl_cur;
466 parent_dtl_cur igf_sl_gen.person_dtl_cur;
467 student_dtl_rec igf_sl_gen.person_dtl_rec;
468 parent_dtl_rec igf_sl_gen.person_dtl_rec;
469
470 -- FA 122 Loan Enhancements added this cursor to remove the cursor based on the obsolete view igf_sl_lor_dtls_v
471
472 CURSOR c_tbh_cur IS
473 SELECT loans.rowid row_id,
474 loans.loan_id,
475 loans.loan_number,
476 loans.award_id,
477 awd.offered_amt,
478 awd.accepted_amt,
479 loans.loan_per_begin_date,
480 loans.loan_per_end_date,
481 loans.loan_status,
482 loans.loan_status_date,
483 loans.loan_chg_status,
484 loans.loan_chg_status_date,
485 loans.active,
486 loans.active_date,
487 lor.orig_fee_perct,
488 lor.pnote_print_ind,
489 lor.s_default_status,
490 lor.p_default_status,
491 lor.p_person_id,
492 lor.sch_cert_date,
493 lor.prc_type_code,
494 lor.anticip_compl_date,
495 lor.cl_loan_type,
496 lor.borw_interest_ind,
497 lor.grade_level_code,
498 lor.enrollment_code,
499 lor.req_serial_loan_code,
500 lor.lend_non_ed_brc_id,
501 lor.pnote_delivery_code,
502 lor.s_signature_code,
503 lor.p_signature_code,
504 lor.borw_outstd_loan_code,
505 lor.cl_seq_number,
506 lor.rec_type_ind,
507 lor.p_signature_date,
508 lor.borr_sign_ind,
509 lor.stud_sign_ind,
510 lor.borr_credit_auth_code,
511 lor.origination_id,
512 lor.act_serial_loan_code,
513 lor.orig_status_flag,
514 lor.orig_batch_id,
515 lor.orig_batch_date,
516 lor.orig_ack_date,
517 lor.credit_override,
518 lor.credit_decision_date,
519 lor.pnote_status,
520 lor.pnote_status_date,
521 lor.pnote_id,
522 lor.pnote_accept_amt,
523 lor.pnote_accept_date,
524 lor.borw_confirm_ind,
525 lor.unsub_elig_for_heal,
526 lor.disclosure_print_ind,
527 lor.unsub_elig_for_depnt,
528 lor.guarantee_amt,
529 lor.guarantee_date,
530 lor.guarnt_adj_ind,
531 lor.guarnt_amt_redn_code,
532 lor.guarnt_status_code,
533 lor.guarnt_status_date,
534 lor.lend_status_code,
535 lor.lend_status_date,
536 lor.last_resort_lender,
537 lor.alt_prog_type_code,
538 lor.alt_appl_ver_code,
539 lor.resp_to_orig_code,
540 lor.tot_outstd_stafford,
541 lor.tot_outstd_plus,
542 lor.alt_borw_tot_debt,
543 lor.act_interest_rate,
544 lor.service_type_code,
545 lor.rev_notice_of_guarnt,
546 lor.sch_refund_amt,
547 lor.sch_refund_date,
548 lor.uniq_layout_vend_code,
549 lor.uniq_layout_ident_code,
550 lor.pnote_batch_id,
551 lor.pnote_ack_date,
552 lor.pnote_mpn_ind,
553 fabase.person_id student_id,
554 fabase.base_id,
555 fmast.fund_code loan_type,
556 lor.interest_rebate_percent_num,
557 lor.cps_trans_num,
558 lor.atd_entity_id_txt,
559 lor.rep_entity_id_txt,
560 lor.crdt_decision_status,
561 lor.note_message,
562 lor.book_loan_amt,
563 lor.book_loan_amt_date,
564 lor.pymt_servicer_amt,
565 lor.pymt_servicer_date,
566 lor.external_loan_id_txt,
567 lor.deferment_request_code,
568 lor.eft_authorization_code,
569 lor.requested_loan_amt,
570 lor.actual_record_type_code,
571 lor.reinstatement_amt,
572 lor.school_use_txt,
573 lor.lender_use_txt,
574 lor.guarantor_use_txt,
575 lor.fls_approved_amt,
576 lor.flu_approved_amt,
577 lor.flp_approved_amt,
578 lor.alt_approved_amt,
579 lor.loan_app_form_code,
580 lor.override_grade_level_code,
581 lor.b_alien_reg_num_txt,
582 lor.esign_src_typ_cd,
583 lor.acad_begin_date,
584 lor.acad_end_date
585 FROM igf_sl_loans_all loans,
586 igf_sl_lor_all lor,
587 igf_aw_award_all awd,
588 igf_ap_fa_base_rec_all fabase,
589 igf_aw_fund_mast_all fmast
590 WHERE loans.loan_id = lv_orig_loan_id
591 AND loans.loan_id = lor.loan_id
592 AND loans.award_id = awd.award_id
593 AND awd.base_id = fabase.base_id
594 AND fmast.fund_id = awd.fund_id;
595
596 CURSOR c_fabase IS
597 SELECT isr.dependency_status dependency_status
598 FROM IGF_AP_FA_BASE_REC fabase,
599 IGF_AP_ISIR_MATCHED isr
600 WHERE
601 fabase.base_id = isr.base_id
602 AND isr.payment_isir = 'Y'
603 AND isr.system_record_type = 'ORIGINAL'
604 AND fabase.base_id = lv_base_id;
605
606 -- Get the Student Phone
607 CURSOR cur_get_s_phone IS select
608 DECODE(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(lv_base_id)),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(lv_base_id)),10,0))
609 FROM DUAL;
610
611 -- Get the Parent Phone
612 CURSOR cur_get_p_phone(p_per_id igf_ap_fa_base_rec.person_id%TYPE) IS
613 SELECT DECODE(igf_sl_gen.get_person_phone(p_per_id),'N/A',LPAD(' ',10,' '),LPAD(igf_sl_gen.get_person_phone(p_per_id),10,0))
614 FROM DUAL;
615
616 l_p_phone VARCHAR2(80);
617 l_s_phone VARCHAR2(80);
618
619 c_fabase_rec c_fabase%ROWTYPE;
620
621 BEGIN
622 FOR tbh_rec in c_tbh_cur LOOP
623
624 -- FA 122 Loan Enhancements Use the igf_sl_gen.get_person_details for getting the student as
625 -- well as parent details.
626 -- get the student details
627 igf_sl_gen.get_person_details(tbh_rec.student_id,student_dtl_cur);
628 FETCH student_dtl_cur INTO student_dtl_rec;
629
630 -- get the parent details
631 igf_sl_gen.get_person_details(tbh_rec.p_person_id,parent_dtl_cur);
632 FETCH parent_dtl_cur INTO parent_dtl_rec;
633
634 CLOSE student_dtl_cur;
635 CLOSE parent_dtl_cur;
636
637 lv_base_id := tbh_rec.base_id;
638
639 --Removed the RAISE for NO_DATA_FOUND as it is not required .
640 --If there were no ISIR Matched then the DL Validation Process would make the Loan Status as
641 ---Not Ready
642 --Bug :-2490289 DL Header and Trailer Formatting Error.
643 -- Get Additional Information from FABASE Record.
644 OPEN c_fabase;
645 FETCH c_fabase INTO c_fabase_rec;
646 CLOSE c_fabase;
647
648
649 l_p_phone:=NULL;
650 l_s_phone:=NULL;
651 IF p_dl_loan_catg='DL_STAFFORD' THEN
652 OPEN cur_get_s_phone;
653 FETCH cur_get_s_phone INTO l_s_phone;
654 CLOSE cur_get_s_phone;
655 ELSIF p_dl_loan_catg='DL_PLUS' THEN
656 OPEN cur_get_p_phone(tbh_rec.p_person_id);
657 FETCH cur_get_p_phone INTO l_p_phone;
658 CLOSE cur_get_p_phone;
659
660 END IF;
661
662 -- FA 122 Loan Enhancemnets inserted the obsolted fields with NULL
663 -- student details fetched from student_dtl_rec
664 -- parent details fetched from parent_dtl_rec
665
666 igf_sl_lor_loc_pkg.insert_row (
667 X_Mode => 'R',
668 x_rowid => lv_row_id,
669 x_loan_id => tbh_rec.loan_id,
670 x_origination_id => tbh_rec.origination_id,
671 x_loan_number => tbh_rec.loan_number,
672 x_loan_type => tbh_rec.loan_type,
673 x_loan_amt_offered => tbh_rec.offered_amt ,
674 x_loan_amt_accepted => tbh_rec.accepted_amt ,
675 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
676 x_loan_per_end_date => tbh_rec.loan_per_end_date,
677 x_acad_yr_begin_date => lv_acad_begin_dt,
678 x_acad_yr_end_date => lv_acad_end_dt,
679 x_loan_status => tbh_rec.loan_status,
680 x_loan_status_date => tbh_rec.loan_status_date,
681 x_loan_chg_status => tbh_rec.loan_chg_status,
682 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
683 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
684 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
685 x_active => tbh_rec.active,
686 x_active_date => tbh_rec.active_date,
687 x_sch_cert_date => tbh_rec.sch_cert_date,
688 x_orig_status_flag => tbh_rec.orig_status_flag,
689 x_orig_batch_id => tbh_rec.orig_batch_id,
690 x_orig_batch_date => tbh_rec.orig_batch_date,
691 x_chg_batch_id => NULL,
692 x_orig_ack_date => tbh_rec.orig_ack_date,
693 x_credit_override => tbh_rec.credit_override,
694 x_credit_decision_date => tbh_rec.credit_decision_date,
695 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
696 x_pnote_status => tbh_rec.pnote_status,
697 x_pnote_status_date => tbh_rec.pnote_status_date,
698 x_pnote_id => tbh_rec.pnote_id,
699 x_pnote_print_ind => tbh_rec.pnote_print_ind,
700 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
701 x_pnote_accept_date => tbh_rec.pnote_accept_date,
702 x_p_signature_code => tbh_rec.p_signature_code,
703 x_p_signature_date => tbh_rec.p_signature_date,
704 x_s_signature_code => tbh_rec.s_signature_code,
705 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
706 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
707 x_orig_fee_perct => tbh_rec.orig_fee_perct,
708 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
709 x_borw_interest_ind => tbh_rec.borw_interest_ind,
710 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
711 x_guarantee_amt => tbh_rec.guarantee_amt,
712 x_guarantee_date => tbh_rec.guarantee_date,
713 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
714 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
715 x_guarnt_status_code => tbh_rec.guarnt_status_code,
716 x_guarnt_status_date => tbh_rec.guarnt_status_date,
717 x_lend_apprv_denied_code => NULL,
718 x_lend_apprv_denied_date => NULL,
719 x_lend_status_code => tbh_rec.lend_status_code,
720 x_lend_status_date => tbh_rec.lend_status_date,
721 x_grade_level_code => tbh_rec.grade_level_code,
722 x_enrollment_code => tbh_rec.enrollment_code,
723 x_anticip_compl_date => tbh_rec.anticip_compl_date,
724 x_borw_lender_id => NULL,
725 x_duns_borw_lender_id => NULL,
726 x_guarantor_id => NULL,
727 x_duns_guarnt_id => NULL,
728 x_prc_type_code => tbh_rec.prc_type_code,
729 x_rec_type_ind => tbh_rec.rec_type_ind,
730 x_cl_loan_type => tbh_rec.cl_loan_type,
731 x_cl_seq_number => tbh_rec.cl_seq_number,
732 x_last_resort_lender => tbh_rec.last_resort_lender,
733 x_lender_id => NULL,
734 x_duns_lender_id => NULL,
735 x_lend_non_ed_brc_id => NULL,
736 x_recipient_id => NULL,
737 x_recipient_type => NULL,
738 x_duns_recip_id => NULL,
739 x_recip_non_ed_brc_id => NULL,
740 x_cl_rec_status => NULL,
741 x_cl_rec_status_last_update => NULL,
742 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
743 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
744 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
745 x_mpn_confirm_code => NULL,
746 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
747 x_appl_loan_phase_code => NULL,
748 x_appl_loan_phase_code_chg => NULL,
749 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
750 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
751 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
752 x_act_interest_rate => tbh_rec.act_interest_rate,
753 x_service_type_code => tbh_rec.service_type_code,
754 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
755 x_sch_refund_amt => tbh_rec.sch_refund_amt,
756 x_sch_refund_date => tbh_rec.sch_refund_date,
757 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
758 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
759 x_p_person_id => tbh_rec.p_person_id,
760 x_p_ssn => parent_dtl_rec.p_ssn,
761 x_p_ssn_chg_date => NULL,
762 x_p_last_name => parent_dtl_rec.p_last_name,
763 x_p_first_name => parent_dtl_rec.p_first_name,
764 x_p_middle_name => parent_dtl_rec.p_middle_name,
765 x_p_permt_addr1 => parent_dtl_rec.p_permt_addr1,
766 x_p_permt_addr2 => parent_dtl_rec.p_permt_addr2,
767 x_p_permt_city => parent_dtl_rec.p_permt_city,
768 x_p_permt_state => parent_dtl_rec.p_permt_state,
769 x_p_permt_zip => parent_dtl_rec.p_permt_zip,
770 x_p_permt_addr_chg_date => NULL,
771 x_p_permt_phone => l_p_phone,
772 x_p_email_addr => parent_dtl_rec.p_email_addr,
773 x_p_date_of_birth => parent_dtl_rec.p_date_of_birth,
774 x_p_dob_chg_date => NULL,
775 x_p_license_num => parent_dtl_rec.p_license_num,
776 x_p_license_state => parent_dtl_rec.p_license_state,
777 x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
778 x_p_alien_reg_num => parent_dtl_rec.p_alien_reg_num,
779 x_p_default_status => tbh_rec.p_default_status,
780 x_p_foreign_postal_code => NULL,
781 x_p_state_of_legal_res => parent_dtl_rec.p_state_of_legal_res,
782 x_p_legal_res_date => parent_dtl_rec.p_legal_res_date,
783 x_s_ssn => student_dtl_rec.p_ssn,
784 x_s_ssn_chg_date => NULL,
785 x_s_last_name => student_dtl_rec.p_last_name,
786 x_s_first_name => student_dtl_rec.p_first_name,
787 x_s_middle_name => student_dtl_rec.p_middle_name,
788 x_s_permt_addr1 => student_dtl_rec.p_permt_addr1,
789 x_s_permt_addr2 => student_dtl_rec.p_permt_addr2,
790 x_s_permt_city => student_dtl_rec.p_permt_city,
791 x_s_permt_state => student_dtl_rec.p_permt_state,
792 x_s_permt_zip => student_dtl_rec.p_permt_zip,
793 x_s_permt_addr_chg_date => NULL,
794 x_s_permt_phone => l_s_phone,
795 x_s_local_addr1 => student_dtl_rec.p_local_addr1,
796 x_s_local_addr2 => student_dtl_rec.p_local_addr2,
797 x_s_local_city => student_dtl_rec.p_local_city,
798 x_s_local_state => student_dtl_rec.p_local_state,
799 x_s_local_zip => student_dtl_rec.p_local_zip,
800 x_s_local_addr_chg_date => NULL,
801 x_s_email_addr => student_dtl_rec.p_email_addr,
802 x_s_date_of_birth => student_dtl_rec.p_date_of_birth,
803 x_s_dob_chg_date => NULL,
804 x_s_license_num => student_dtl_rec.p_license_num,
805 x_s_license_state => student_dtl_rec.p_license_state,
806 x_s_depncy_status => c_fabase_rec.dependency_status,
807 x_s_default_status => tbh_rec.s_default_status,
808 x_s_citizenship_status => student_dtl_rec.p_citizenship_status,
809 x_s_alien_reg_num => student_dtl_rec.p_alien_reg_num,
810 x_s_foreign_postal_code => NULL,
811 x_pnote_batch_id => tbh_rec.pnote_batch_id,
812 x_pnote_ack_date => tbh_rec.pnote_ack_date,
813 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind,
814 --Added fields
815 x_award_id => tbh_rec.award_id,
816 x_base_id => tbh_rec.base_id,
817 x_document_id_txt => NULL,
818 x_loan_key_num => NULL,
819 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
820 x_fin_award_year => NULL,
821 x_cps_trans_num => tbh_rec.cps_trans_num,
822 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt,
823 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
824 x_source_entity_id_txt => NULL,
825 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
826 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
827 x_book_loan_amt => tbh_rec.book_loan_amt,
828 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
829 x_s_chg_birth_date => NULL,
830 x_s_chg_ssn => NULL,
831 x_s_chg_last_name => NULL,
832 x_b_chg_birth_date => NULL,
833 x_b_chg_ssn => NULL,
834 x_b_chg_last_name => NULL,
835 x_note_message => tbh_rec.note_message,
836 x_full_resp_code => NULL,
837 x_s_permt_county => NULL,
838 x_b_permt_county => NULL,
839 x_s_permt_country => NULL,
840 x_b_permt_country => NULL,
841 x_crdt_decision_status => tbh_rec.crdt_decision_status,
842 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
843 x_deferment_request_code => tbh_rec.deferment_request_code,
844 x_eft_authorization_code => tbh_rec.eft_authorization_code,
845 x_requested_loan_amt => tbh_rec.requested_loan_amt,
846 x_actual_record_type_code => tbh_rec.actual_record_type_code,
847 x_reinstatement_amt => tbh_rec.reinstatement_amt,
848 x_lender_use_txt => tbh_rec.lender_use_txt,
849 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
850 x_fls_approved_amt => tbh_rec.fls_approved_amt,
851 x_flu_approved_amt => tbh_rec.flu_approved_amt,
852 x_flp_approved_amt => tbh_rec.flp_approved_amt,
853 x_alt_approved_amt => tbh_rec.alt_approved_amt,
854 x_loan_app_form_code => tbh_rec.loan_app_form_code,
855 x_alt_borrower_ind_flag => NULL,
856 x_school_id_txt => NULL,
857 x_cost_of_attendance_amt => NULL,
858 x_established_fin_aid_amount => NULL,
859 x_student_electronic_sign_flag => NULL,
860 x_mpn_type_flag => NULL,
861 x_school_use_txt => tbh_rec.school_use_txt,
862 x_expect_family_contribute_amt => NULL,
863 x_borower_electronic_sign_flag => NULL,
864 x_borower_credit_authoriz_flag => NULL,
865 x_esign_src_typ_cd => NULL
866
867 );
868
869
870 END LOOP;
871 END;
872
873
874 -- Insert the Origination Data being sent to LOC, into "_LOC" tables
875 DECLARE
876 lv_row_id VARCHAR2(25);
877 CURSOR c_tbh_cur IS
878 SELECT * FROM igf_aw_awd_disb
879 WHERE award_id = lv_orig_award_id;
880 BEGIN
881 FOR tbh_rec in c_tbh_cur LOOP
882 igf_sl_awd_disb_loc_pkg.insert_row (
883 X_Mode => 'R',
884 x_rowid => lv_row_id,
885 x_award_id => tbh_rec.award_id,
886 x_disb_num => tbh_rec.disb_num,
887 x_disb_gross_amt => tbh_rec.disb_accepted_amt,
888 x_fee_1 => tbh_rec.fee_1,
889 x_fee_2 => tbh_rec.fee_2,
890 x_disb_net_amt => tbh_rec.disb_net_amt,
891 x_disb_date => tbh_rec.disb_date,
892 x_hold_rel_ind => tbh_rec.hold_rel_ind,
893 x_fee_paid_1 => tbh_rec.fee_paid_1,
894 x_fee_paid_2 => tbh_rec.fee_paid_2
895 );
896 END LOOP;
897 END;
898
899 IF l_display <> 'Y' THEN
900 --Display mesg in LOG File that Records have been originated and an Output file has been created.
901 fnd_file.new_line(fnd_file.log,2);
902 fnd_message.set_name('IGF','IGF_SL_LOAN_ORIGINATED');
903 fnd_message.set_token('LOAN_CATEG',l_para_rec(1));
904 fnd_message.set_token('FILE_VERSION',lv_dl_version);
905 fnd_file.put_line(fnd_file.log,fnd_message.get);
906 fnd_file.new_line(fnd_file.log,2);
907 l_display :='Y';
908 END IF;
909
910
911 -- Update LOAN_STATUS, LOAN_STATUS_DATE to SENT, Current Date
912 DECLARE
913 lv_row_id VARCHAR2(25);
914 CURSOR c_tbh_cur IS
915 SELECT igf_sl_loans.* FROM igf_sl_loans
916 WHERE loan_id = lv_orig_loan_id FOR UPDATE OF igf_sl_loans.loan_status NOWAIT;
917 BEGIN
918 FOR tbh_rec in c_tbh_cur LOOP
919
920 -- Modified the update row call to include the Borrower Determination as part of
921 -- Refunds DLD 2144600
922 igf_sl_loans_pkg.update_row (
923 X_Mode => 'R',
924 x_rowid => tbh_rec.row_id,
925 x_loan_id => tbh_rec.loan_id,
926 x_award_id => tbh_rec.award_id,
927 x_seq_num => tbh_rec.seq_num,
928 x_loan_number => tbh_rec.loan_number,
929 x_loan_per_begin_date => tbh_rec.loan_per_begin_date,
930 x_loan_per_end_date => tbh_rec.loan_per_end_date,
931 x_loan_status => 'S',
932 x_loan_status_date => TRUNC(SYSDATE),
933 x_loan_chg_status => tbh_rec.loan_chg_status,
934 x_loan_chg_status_date => tbh_rec.loan_chg_status_date,
935 x_active => tbh_rec.active,
936 x_active_date => tbh_rec.active_date,
937 x_borw_detrm_code => tbh_rec.borw_detrm_code,
938 x_legacy_record_flag => NULL,
939 x_external_loan_id_txt => tbh_rec.external_loan_id_txt
940
941 );
942
943 END LOOP;
944 END;
945
946 -- Update the BATCH ID and BATCH_DATE
947 DECLARE
948 lv_row_id VARCHAR2(25);
949 CURSOR c_tbh_cur IS
950 SELECT igf_sl_lor.* FROM igf_sl_lor
951 WHERE loan_id = lv_orig_loan_id FOR UPDATE OF igf_sl_lor.sch_cert_date NOWAIT;
952 BEGIN
953
954 -- FA 122 Loan Enhancemnets updated the obsolted fields with NULL
955
956 FOR tbh_rec in c_tbh_cur LOOP
957 igf_sl_lor_pkg.update_row (
958 X_Mode => 'R',
959 x_rowid => tbh_rec.row_id,
960 x_origination_id => tbh_rec.origination_id,
961 x_loan_id => tbh_rec.loan_id,
962 x_sch_cert_date => tbh_rec.sch_cert_date,
963 x_orig_status_flag => tbh_rec.orig_status_flag,
964 x_orig_batch_id => lv_batch_id,
965 x_orig_batch_date => TRUNC(SYSDATE),
966 x_chg_batch_id => NULL,
967 x_orig_ack_date => tbh_rec.orig_ack_date,
968 x_credit_override => tbh_rec.credit_override,
969 x_credit_decision_date => tbh_rec.credit_decision_date,
970 x_req_serial_loan_code => tbh_rec.req_serial_loan_code,
971 x_act_serial_loan_code => tbh_rec.act_serial_loan_code,
972 x_pnote_delivery_code => tbh_rec.pnote_delivery_code,
973 x_pnote_status => tbh_rec.pnote_status,
974 x_pnote_status_date => tbh_rec.pnote_status_date,
975 x_pnote_id => tbh_rec.pnote_id,
976 x_pnote_print_ind => tbh_rec.pnote_print_ind,
977 x_pnote_accept_amt => tbh_rec.pnote_accept_amt,
978 x_pnote_accept_date => tbh_rec.pnote_accept_date,
979 x_unsub_elig_for_heal => tbh_rec.unsub_elig_for_heal,
980 x_disclosure_print_ind => tbh_rec.disclosure_print_ind,
981 x_orig_fee_perct => tbh_rec.orig_fee_perct,
982 x_borw_confirm_ind => tbh_rec.borw_confirm_ind,
983 x_borw_interest_ind => tbh_rec.borw_interest_ind,
984 x_borw_outstd_loan_code => tbh_rec.borw_outstd_loan_code,
985 x_unsub_elig_for_depnt => tbh_rec.unsub_elig_for_depnt,
986 x_guarantee_amt => tbh_rec.guarantee_amt,
987 x_guarantee_date => tbh_rec.guarantee_date,
988 x_guarnt_amt_redn_code => tbh_rec.guarnt_amt_redn_code,
989 x_guarnt_status_code => tbh_rec.guarnt_status_code,
990 x_guarnt_status_date => tbh_rec.guarnt_status_date,
991 x_lend_apprv_denied_code => NULL,
992 x_lend_apprv_denied_date => NULL,
993 x_lend_status_code => tbh_rec.lend_status_code,
994 x_lend_status_date => tbh_rec.lend_status_date,
995 x_guarnt_adj_ind => tbh_rec.guarnt_adj_ind,
996 x_grade_level_code => tbh_rec.grade_level_code,
997 x_enrollment_code => tbh_rec.enrollment_code,
998 x_anticip_compl_date => tbh_rec.anticip_compl_date,
999 x_borw_lender_id => NULL,
1000 x_duns_borw_lender_id => NULL,
1001 x_guarantor_id => NULL,
1002 x_duns_guarnt_id => NULL,
1003 x_prc_type_code => tbh_rec.prc_type_code,
1004 x_cl_seq_number => tbh_rec.cl_seq_number,
1005 x_last_resort_lender => tbh_rec.last_resort_lender,
1006 x_lender_id => NULL,
1007 x_duns_lender_id => NULL,
1008 x_lend_non_ed_brc_id => NULL,
1009 x_recipient_id => NULL,
1010 x_recipient_type => NULL,
1011 x_duns_recip_id => NULL,
1012 x_recip_non_ed_brc_id => NULL,
1013 x_rec_type_ind => tbh_rec.rec_type_ind,
1014 x_cl_loan_type => tbh_rec.cl_loan_type,
1015 x_cl_rec_status => NULL,
1016 x_cl_rec_status_last_update => NULL,
1017 x_alt_prog_type_code => tbh_rec.alt_prog_type_code,
1018 x_alt_appl_ver_code => tbh_rec.alt_appl_ver_code,
1019 x_mpn_confirm_code => NULL,
1020 x_resp_to_orig_code => tbh_rec.resp_to_orig_code,
1021 x_appl_loan_phase_code => NULL,
1022 x_appl_loan_phase_code_chg => NULL,
1023 x_appl_send_error_codes => NULL,
1024 x_tot_outstd_stafford => tbh_rec.tot_outstd_stafford,
1025 x_tot_outstd_plus => tbh_rec.tot_outstd_plus,
1026 x_alt_borw_tot_debt => tbh_rec.alt_borw_tot_debt,
1027 x_act_interest_rate => tbh_rec.act_interest_rate,
1028 x_service_type_code => tbh_rec.service_type_code,
1029 x_rev_notice_of_guarnt => tbh_rec.rev_notice_of_guarnt,
1030 x_sch_refund_amt => tbh_rec.sch_refund_amt,
1031 x_sch_refund_date => tbh_rec.sch_refund_date,
1032 x_uniq_layout_vend_code => tbh_rec.uniq_layout_vend_code,
1033 x_uniq_layout_ident_code => tbh_rec.uniq_layout_ident_code,
1034 x_p_person_id => tbh_rec.p_person_id,
1035 x_p_ssn_chg_date => NULL,
1036 x_p_dob_chg_date => NULL,
1037 x_p_permt_addr_chg_date => NULL,
1038 x_p_default_status => tbh_rec.p_default_status,
1039 x_p_signature_code => tbh_rec.p_signature_code,
1040 x_p_signature_date => tbh_rec.p_signature_date,
1041 x_s_ssn_chg_date => NULL,
1042 x_s_dob_chg_date => NULL,
1043 x_s_permt_addr_chg_date => NULL,
1044 x_s_local_addr_chg_date => NULL,
1045 x_s_default_status => tbh_rec.s_default_status,
1046 x_s_signature_code => tbh_rec.s_signature_code,
1047 x_pnote_batch_id => tbh_rec.pnote_batch_id,
1048 x_pnote_ack_date => tbh_rec.pnote_ack_date,
1049 x_pnote_mpn_ind => tbh_rec.pnote_mpn_ind ,
1050 x_elec_mpn_ind => tbh_rec.elec_mpn_ind ,
1051 x_borr_sign_ind => tbh_rec.borr_sign_ind ,
1052 x_stud_sign_ind => tbh_rec.stud_sign_ind ,
1053 x_borr_credit_auth_code => tbh_rec.borr_credit_auth_code ,
1054 x_relationship_cd => tbh_rec.relationship_cd,
1055 x_interest_rebate_percent_num => tbh_rec.interest_rebate_percent_num,
1056 x_cps_trans_num => tbh_rec.cps_trans_num,
1057 x_atd_entity_id_txt => tbh_rec.atd_entity_id_txt ,
1058 x_rep_entity_id_txt => tbh_rec.rep_entity_id_txt,
1059 x_crdt_decision_status => tbh_rec.crdt_decision_status,
1060 x_note_message => tbh_rec.note_message,
1061 x_book_loan_amt => tbh_rec.book_loan_amt ,
1062 x_book_loan_amt_date => tbh_rec.book_loan_amt_date,
1063 x_pymt_servicer_amt => tbh_rec.pymt_servicer_amt,
1064 x_pymt_servicer_date => tbh_rec.pymt_servicer_date,
1065 x_requested_loan_amt => tbh_rec.requested_loan_amt,
1066 x_eft_authorization_code => tbh_rec.eft_authorization_code,
1067 x_external_loan_id_txt => tbh_rec.external_loan_id_txt,
1068 x_deferment_request_code => tbh_rec.deferment_request_code ,
1069 x_actual_record_type_code => tbh_rec.actual_record_type_code,
1070 x_reinstatement_amt => tbh_rec.reinstatement_amt,
1071 x_school_use_txt => tbh_rec.school_use_txt,
1072 x_lender_use_txt => tbh_rec.lender_use_txt,
1073 x_guarantor_use_txt => tbh_rec.guarantor_use_txt,
1074 x_fls_approved_amt => tbh_rec.fls_approved_amt,
1075 x_flu_approved_amt => tbh_rec.flu_approved_amt,
1076 x_flp_approved_amt => tbh_rec.flp_approved_amt,
1077 x_alt_approved_amt => tbh_rec.alt_approved_amt,
1078 x_loan_app_form_code => tbh_rec.loan_app_form_code,
1079 x_override_grade_level_code => tbh_rec.override_grade_level_code,
1080 x_b_alien_reg_num_txt => tbh_rec.b_alien_reg_num_txt,
1081 x_esign_src_typ_cd => tbh_rec.esign_src_typ_cd,
1082 x_acad_begin_date => tbh_rec.acad_begin_date,
1083 x_acad_end_date => tbh_rec.acad_end_date
1084 );
1085
1086 END LOOP;
1087 END;
1088 END IF;
1089 END LOOP; -- loop for ref cursor records
1090 END LOOP; -- loop for loan details cursor
1091 -- Initialise the Data Record field
1092 lv_data_record := NULL;
1093 IF(lv_num_of_rec > 0)THEN
1094 -- Write the Trailer Record
1095 igf_sl_dl_record.DLTrailer_cur(lv_dl_version, lv_num_of_rec, Trailer_Rec);
1096 FETCH Trailer_Rec into lv_data_record;
1097 IF Header_Rec%NOTFOUND THEN
1098 fnd_message.set_name ('IGF', 'IGF_GE_TRL_CREATE_ERROR');
1099 igs_ge_msg_stack.add;
1100 app_exception.raise_exception;
1101 END IF;
1102 -- Write the Trailer Record into the Output file.
1103 fnd_file.put_line(FND_FILE.OUTPUT, lv_data_record);
1104 ELSE
1105 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGF','IGF_SL_NO_LOAN_ORIG_DATA'));
1106 --MN 27-Dec-2004 In case there are no records getting generated, the V state will be reverted back.
1107 ROLLBACK;
1108 END IF;
1109
1110 COMMIT;
1111
1112 EXCEPTION
1113
1114 WHEN no_data_found THEN
1115 NULL;
1116
1117 WHEN no_loan_data THEN
1118 -- Please Note that this is NOT an exception. It is a Valid and proper way of
1119 -- of exiting a process if there is not Data.
1120 COMMIT; -- Commit is done here, so that the "Validation reject details" get committed.
1121 retcode := 0;
1122 errbuf := NULL;
1123 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGF','IGF_SL_NO_LOAN_ORIG_DATA'));
1124
1125 WHEN yr_full_participant THEN
1126 NULL;
1127
1128
1129 WHEN app_exception.record_lock_exception THEN
1130
1131 ROLLBACK;
1132
1133 IF c_loans%ISOPEN THEN
1134 CLOSE c_loans;
1135 END IF;
1136
1137 retcode := 2;
1138 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1139 igs_ge_msg_stack.conc_exception_hndl;
1140
1141 WHEN OTHERS THEN
1142 ROLLBACK;
1143 IF c_loans%ISOPEN THEN
1144 CLOSE c_loans;
1145 END IF;
1146 retcode := 2;
1147 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1148 fnd_file.put_line(fnd_file.log,SQLERRM);
1149 igs_ge_msg_stack.conc_exception_hndl;
1150 END dl_originate;
1151
1152
1153 END igf_sl_dl_orig;