[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_LAR_CREATION
Source
1 PACKAGE BODY igf_sl_lar_creation AS
2 /* $Header: IGFSL01B.pls 120.14 2006/08/07 13:10:15 azmohamm ship $ */
3
4 /*
5 --
6 ------------------------------------------------------------------------
7 -- Created By : mesriniv
8 -- Date Created By : 2000/11/13
9 -- Purpose : To Insert Loan Records into IGF_SL_LOANS
10 ------------------------------------------------------------------------
11 -- Who When What
12 ------------------------------------------------------------------------------------------------------------
13 azmohamm 03-AUG-2006 FA163
14 TBH Impact changes done in create_loan_records() and update_loan_rec()
15 ------------------------------------------------------------------------------------------------------------
16 bvisvana 10-Apr-2006 Build FA161. Bug 5006583
17 TBH Impact change done in insert_lor_dl_records().
18 --------------------------------------------------------------------------------------------------------------
19 museshad 20-Feb-2005 Bug 5031795 - SQL Repository issue.
20 In create_loan_records(), modified cursor cur_count_fed_code
21 for better performance.
22 ---------------------------------------------------------------------------------------------------------------
23 bvisvana 20-Sep-2005 Bug # 4127532 - For ALT Loan, if borrower and Student are equal then
24 p_default_status is set to s_default_status during the loan creation
25 ---------------------------------------------------------------------------------------------------------------
26 bvisvana 12-Sep-2005 SBCC Bug # 4575843
27 Before creating loan record, check whether the award accepted amount is in whole numbers.
28 --------------------------------------------------------------------------------------------
29 bvisvana 25-Aug-2005 Bug # 4568942. Grade Level not matching with enrollment data.
30 (Wrong argument type) g_fund_id is replaced with (correct argument type)
31 g_adplans_id while calling the Class standing wrapper igf_aw_packng_subfns.get_class_stnd
32 --------------------------------------------------------------------------------------------
33 bvisvana 18-Aug-2005 Bug # 4464629 - Removed the global variable gv_dl_version.
34 This is not used anywhere and this is causing unexpected behaviour.
35 ---------------------------------------------------------------------------------------------------------------
36 mnade 6/3/2005 FA 157 - Auto Borrower population for PLUS loans, Cosigner Data for ALT Loans.
37 Student default borrower for ALT Loans.
38 pssahni 30-Dec-2004 Bug 4087865 Application form code must be populated
39 --------------------------------------------------------------------------
40 mnade 29-Dec-2004 Bug 4085937
41 Call get entity ids Only for Full Participant
42 sjadhav 25-Oct-2004 Bug 3416863 FA 149 Build Changes
43 ------------------------------------------------------------------------
44 brajendr 12-Oct-2004 Bug 3732665 ISIR Enhacements
45 Modified the Payment ISIR reference
46
47 ugummall 23-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
48 In constructing loan_number for Direct Loan, School ID is derived from the
49 student's associated Org. For this purpose, base_id(as extra parameter) is
50 passed to this function.
51 Similarly, OPE ID and School Non Ed Brc ID are also derived for Common Line Loan.
52 --veramach 16-OCT-2003 FA124 Build remove ISIR requirement for awarding(bug # 3108506)
53 -- Added code for checking loan limits in insert_loan_records
54 -- bkkumar 06-oct-2003 Bug 3104228 Impact of adding the relationship_cd
55 in igf_sl_lor_all table and obsoleting
56 BORW_LENDER_ID,
57 DUNS_BORW_LENDER_ID,
58 GUARANTOR_ID,
59 DUNS_GUARNT_ID,
60 LENDER_ID, DUNS_LENDER_ID
61 LEND_NON_ED_BRC_ID, RECIPIENT_ID
62 RECIPIENT_TYPE,DUNS_RECIP_ID
63 RECIP_NON_ED_BRC_ID columns
64 Also the relationship code is now picked up from the
65 pick_Setup routine.
66 -- bkkumar 29-sep-2003 Bug 3104228 . FA 122 Loans Enhancements
67 In "insert_lor_cl_records" procedure
68 Derivation of the fields
69 REQ_SERIAL_LOAN_CODE,
70 PNOTE_DELIVERY_CODE,
71 BORW_INTEREST_IND,
72 BORW_LENDER_ID,
73 DUNS_BORW_LENDER_ID, -- FIELD OBSOLETED
74 GUARANTOR_ID,
75 DUNS_GUARNT_ID, -- FIELD OBSOLETED
76 PRC_TYPE_CODE,
77 LENDER_ID,
78 DUNS_LENDER_ID, -- FIELD OBSOLETED
79 LEND_NON_ED_BRC_ID,
80 RECIPIENT_ID,
81 RECIPIENT_TYPE,
82 DUNS_RECIP_ID, -- FIELD OBSOLETED
83 RECIP_NON_ED_BRC_ID,
84 is now done from the FFELP loan Setup.
85 Added the common framework logging messages.
86
87 -- rasahoo 02-Sep-2003 Replaced igf_ap_fa_base_h.class_standing%TYPE with
88 -- igs_pr_css_class_std_v.class_standing%TYPE and
89 -- igf_ap_fa_base_h.enrl_program_type%TYPE with igs_ps_ver_all.course_type%TYPE.
90 -- gmuralid 3-Juyl-2003 Bug 2995944 - Legacy Part 3 - FFELP Import
91 -- Added legacy record flag as parameter to
92 -- igf_sl_loans_pkg
93 ------------------------------------------------------------------------
94 -- sjadhav 28-Mar-2003 Bug 2863960
95 -- Corrected message token for
96 -- IGF_AP_NO_GRADE_LEVEL to PERSON_NUMBER
97 ------------------------------------------------------------------------
98 -- masehgal 10-Oct-2002 # 2591960 Integration Enhancements
99 -- Validation on a FRESHMAN ( never attended
100 -- college or did )
101 -- Sepearated dl/cl code derivation from
102 -- return entities
103 -- Created separate procedure to obtain
104 -- those
105 ------------------------------------------------------------------------
106 -- sjadhav Bug 2415013 Default Enrollment Code to Full Time
107 ------------------------------------------------------------------------
108 -- sjadhav 26-Feb-2002 Bug 2240762
109 -- Removed references to cur_tp_dates
110 -- Added two functions to get Loan Start Date
111 -- and Loan End Date
112 -- Added a check to see if the SSN is already
113 -- used in creation of Loan Number
114 ------------------------------------------------------------------------
115 -- sjadhav 24-jul-2001 Bug ID : 1818617
116 -- added parameter p_get_recent_info
117 ------------------------------------------------------------------------
118 -- adhawan 15-feb-2002 Bug Id : 2216956 added columns
119 -- elec_mpn_ind,
120 -- borr_sign_in
121 -- stud_sign_ind
122 -- borr_credit_auth_code
123 ----------------------------------------------------------------------- */
124 --
125
126 -- FA 134
127
128 -- FA 134
129
130 award_rec igf_aw_award_v%ROWTYPE;
131 dl_setup_rec igf_sl_dl_setup_all%ROWTYPE;
132 cl_setup_rec igf_sl_cl_setup_all%ROWTYPE;
133 p_incr_date_code VARCHAR2(100);
134
135 g_s_default_status igf_sl_lor_all.s_default_status%TYPE;
136 g_p_default_status igf_sl_lor_all.p_default_status%TYPE;
137 g_grade_level_dl igf_sl_lor_all.grade_level_code%TYPE;
138 g_grade_level_cl igf_sl_lor_all.grade_level_code%TYPE;
139 g_anticip_compl_date igf_sl_lor_all.anticip_compl_date%TYPE;
140 g_enrollment_code igf_sl_lor_all.enrollment_code%TYPE;
141 gv_return_status VARCHAR2(30);
142 gv_message fnd_new_messages.message_text%TYPE;
143 SKIP_THIS_RECORD EXCEPTION;
144
145 g_log_title VARCHAR2(1000);
146 g_log_start_flag BOOLEAN;
147
148 -- Parameters below is declared to hold the fund id in the
149 -- award rec loop. (Bug 2385334)
150 g_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE; -- Bug 4568942
151 g_award_id igf_aw_award_all.award_id%TYPE;
152 g_person_id igf_ap_fa_base_rec_all.person_id%TYPE;
153 gn_transaction_num igf_ap_isir_matched_all.transaction_num%TYPE;
154 gv_atd_entity_id_txt VARCHAR2(30);
155 gv_rep_entity_id_txt VARCHAR2(30);
156
157 gv_unsub_elig_for_depnt igf_sl_lor.unsub_elig_for_depnt%TYPE;
158 gn_award_id igf_aw_awd_disb_all.award_id%TYPE;
159
160 g_accepted_amt award_rec.accepted_amt%TYPE;
161
162 g_process_log igf_lookups_view.meaning%TYPE;
163 g_award_log igf_lookups_view.meaning%TYPE;
164 g_person_log igf_lookups_view.meaning%TYPE;
165 g_year VARCHAR2(80);
166 g_start_date DATE;
167 g_end_date DATE;
168 g_alternate_code igs_ca_inst_all.alternate_code%TYPE;
169 g_student_person_id NUMBER; -- Bug # 4636920 - bvisvana
170
171
172 PROCEDURE get_borrower_parent_id (
173 p_loan_id IN igf_sl_loans.loan_id%TYPE,
174 p_parent_person_id IN OUT NOCOPY NUMBER,
175 p_student_person_id IN OUT NOCOPY NUMBER
176 ) IS
177 -- takes loan_id, return parentid and parent_details for single associated parent for the student
178 /***************************************************************
179 Change History :
180 Who When What
181 bvisvana 25-Aug-2005 Bug 4127532 - Placed p_student_person_id assignment after the IF condition
182 since we need the student id for ALT loans (student = borrower) irrespective of single parent or not
183 For FLP and DLP only parent is needed and hence if only one parent, that parent_id is returned, else NULL
184 ***************************************************************/
185 CURSOR parent_id_cur (cp_loan_id igf_sl_loans_all.loan_id%TYPE) IS
186 SELECT
187 COUNT(v.row_id) parent_count,
188 MIN(v.object_ID) student_id,
189 MIN(v.SUBJECT_ID) parent_id
190 FROM
191 igs_pe_relationships_v v,
192 igf_aw_award_all awd,
193 igf_sl_loans_all loans,
194 igf_ap_fa_base_rec base
195 WHERE
196 base.person_id = v.object_id
197 AND base.base_id = awd.base_id
198 AND awd.award_id = loans.award_id
199 AND loans.loan_id = cp_loan_id
200 AND
201 RELATIONSHIP_CODE = 'PARENT_OF'
202 AND TRUNC(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
203
204 l_parent_id_rec parent_id_cur%ROWTYPE;
205
206 BEGIN
207 OPEN parent_id_cur (cp_loan_id => p_loan_id);
208 FETCH parent_id_cur INTO l_parent_id_rec;
209 CLOSE parent_id_cur;
210 IF l_parent_id_rec.parent_count = 1 THEN
211 -- Get Parent Details as required for
212 p_parent_person_id := l_parent_id_rec.parent_id;
213 END IF;
214 -- bvisvana - Bug 4127532 - Placed the assignment statement for p_student_person_id
215 -- after the IF condition since we need the student id for ALT loans (student = borrower) irrespective of parent count
216 -- For FLP and DLP only parent is needed and hence it only one parent, that is returned else NULL
217 p_student_person_id := l_parent_id_rec.student_id;
218 g_student_person_id := l_parent_id_rec.student_id; -- Bug # 4636920 - bvisvana
219 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
220 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.get_borrower_parent_id.debug',
221 'p_loan_id - ' || p_loan_id ||
222 '|student_id - ' || l_parent_id_rec.student_id ||
223 '|p_parent_person_id - ' || p_parent_person_id ||
224 '|Parent .parent_count - ' ||l_parent_id_rec.parent_count);
225 END IF;
226 EXCEPTION
227 WHEN OTHERS THEN
228 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
229 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.GET_BORROWER_PARENT_ID');
230 fnd_file.put_line(fnd_file.log,fnd_message.get || '-' || SQLERRM);
231 igs_ge_msg_stack.add;
232 app_exception.raise_exception;
233 END get_borrower_parent_id;
234
235
236 PROCEDURE populate_cosigner_data (p_loan_id igf_sl_loans_all.loan_id%TYPE,
237 p_person_id NUMBER) IS
238 /*
239 Change History
240 Who When What
241 bvisvana 07-Oct-2005 Bug # 4636920 - Cosigner details not fetched properly.
242 CS1 Student Relationship and CS1 US Citizenship status are populated correctly.
243 */
244
245 CURSOR alt_borw_cur (cp_loan_id igf_sl_alt_borw_all.loan_id%TYPE) IS
246 SELECT
247 alt.rowid row_id,
248 alt.*
249 FROM
250 igf_sl_alt_borw_all alt
251 WHERE
252 alt.loan_id = cp_loan_id;
253
254 --Bug# 5006583 - bvisvana
255 -- get cal type and sequence number
256
257 CURSOR cal_type_cur (cp_loan_id igf_sl_alt_borw_all.loan_id%TYPE) IS
258 SELECT
259 slor.ci_cal_type, slor.ci_sequence_number
260 FROM igf_sl_lor_v slor
261 WHERE slor.loan_id = cp_loan_id;
262
263 cal_type_rec cal_type_cur%ROWTYPE;
264
265 --Bug# 5006583 - bvisvana
266 CURSOR citizenship_dtl_cur (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE) IS
267 SELECT
268 pct.restatus_code
269 FROM igs_pe_eit_restatus_v pct
270 WHERE pct.person_id = cp_person_id
271 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
272
273 citizenship_dtl_rec citizenship_dtl_cur%ROWTYPE;
274
275 --Bug# 5006583
276 CURSOR cur_fa_mapping ( p_citizenship_status igf_sl_pe_citi_map.pe_citi_stat_code%TYPE,
277 p_cal_type igf_sl_pe_citi_map.ci_cal_type%TYPE,
278 p_sequence_number igf_sl_pe_citi_map.ci_sequence_number%TYPE ) IS
279 SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
280 WHERE pe_citi_stat_code = p_citizenship_status
281 AND ci_sequence_number =p_sequence_number
282 AND ci_cal_type = p_cal_type;
283
284 l_cur_fa_rec cur_fa_mapping%ROWTYPE;
285 -- Bug # 4636920 - bvisvana
286 CURSOR cur_stud_rel (cp_parent_id NUMBER , cp_student_id NUMBER) IS
287 SELECT relationship_code FROM
288 igs_pe_relationships_v v
289 WHERE v.object_id = cp_student_id AND -- child
290 v.subject_id = cp_parent_id AND -- parent
291 TRUNC(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
292 rel_code VARCHAR2(30);
293
294 l_alt_borw_rec alt_borw_cur%ROWTYPE;
295 lv_rowid ROWID;
296 l_albw_id igf_sl_alt_borw.albw_id%TYPE;
297 cosigner_dtl_cur igf_sl_gen.person_dtl_cur;
298 cosigner_dtl_rec igf_sl_gen.person_dtl_rec;
299 l_person_phone VARCHAR2(30);
300
301 BEGIN
302
303 igf_sl_gen.get_person_details(p_person_id, cosigner_dtl_cur);
304 FETCH cosigner_dtl_cur INTO cosigner_dtl_rec;
305 CLOSE cosigner_dtl_cur;
306 l_person_phone := igf_sl_gen.get_person_phone(p_person_id);
307
308 OPEN alt_borw_cur (cp_loan_id => p_loan_id);
309 FETCH alt_borw_cur INTO l_alt_borw_rec;
310 CLOSE alt_borw_cur;
311
312 -- FA 161 CL4 #5006583
313 OPEN citizenship_dtl_cur (p_person_id); --get citizenship status
314 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
315
316 OPEN cal_type_cur (p_loan_id);
317 FETCH cal_type_cur INTO cal_type_rec;
318
319 IF citizenship_dtl_cur%FOUND THEN
320 OPEN cur_fa_mapping (citizenship_dtl_rec.restatus_code, --get FA Citizenship Status Code
321 cal_type_rec.ci_cal_type,
322 cal_type_rec.ci_sequence_number);
323 FETCH cur_fa_mapping INTO l_cur_fa_rec;
324 CLOSE cur_fa_mapping;
325
326 IF NVL(l_cur_fa_rec.fa_citi_stat_code,'*') = '1' THEN
327 citizenship_dtl_rec.restatus_code := '1';
328 ELSE
329 citizenship_dtl_rec.restatus_code := NULL;
330 END IF;
331 END IF;
332 CLOSE cal_type_cur;
333 CLOSE citizenship_dtl_cur;
334
335 -- Bug # 4636920 - bvisvana
336 IF p_person_id IS NOT NULL THEN
337 OPEN cur_stud_rel(cp_parent_id => p_person_id, cp_student_id => g_student_person_id);
338 FETCH cur_stud_rel INTO rel_code;
339 CLOSE cur_stud_rel;
340 IF rel_code = 'PARENT_OF' THEN
341 rel_code := 'P';
342 ELSIF rel_code = 'CHILD_OF' THEN
343 rel_code := 'C';
344 END IF;
345 END IF;
346
347 cosigner_dtl_rec.p_permt_zip := TRANSLATE (UPPER(LTRIM(RTRIM(cosigner_dtl_rec.p_permt_zip))),'1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ`~!@#$%^&*_+=-,./?><():; ','1234567890');
348
349 igf_sl_alt_borw_pkg.add_row (
350 x_rowid => l_alt_borw_rec.row_id ,
351 x_albw_id => l_alt_borw_rec.albw_id ,
352 x_loan_id => p_loan_id ,
353 x_fed_stafford_loan_debt => l_alt_borw_rec.fed_stafford_loan_debt ,
354 x_fed_sls_debt => l_alt_borw_rec.fed_sls_debt ,
355 x_heal_debt => l_alt_borw_rec.heal_debt ,
356 x_perkins_debt => l_alt_borw_rec.perkins_debt ,
357 x_other_debt => l_alt_borw_rec.other_debt ,
358 x_crdt_undr_difft_name => l_alt_borw_rec.crdt_undr_difft_name ,
359 x_borw_gross_annual_sal => l_alt_borw_rec.borw_gross_annual_sal ,
360 x_borw_other_income => l_alt_borw_rec.borw_other_income ,
361 x_student_major => l_alt_borw_rec.student_major ,
362 x_int_rate_opt => l_alt_borw_rec.int_rate_opt ,
363 x_repayment_opt_code => l_alt_borw_rec.repayment_opt_code ,
364 x_stud_mth_housing_pymt => l_alt_borw_rec.stud_mth_housing_pymt ,
365 x_stud_mth_crdtcard_pymt => l_alt_borw_rec.stud_mth_crdtcard_pymt ,
366 x_stud_mth_auto_pymt => l_alt_borw_rec.stud_mth_auto_pymt ,
367 x_stud_mth_ed_loan_pymt => l_alt_borw_rec.stud_mth_ed_loan_pymt ,
368 x_stud_mth_other_pymt => l_alt_borw_rec.stud_mth_other_pymt ,
369 x_mode => 'R' ,
370 x_other_loan_amt => l_alt_borw_rec.other_loan_amt ,
371 x_cs1_lname => NVL(l_alt_borw_rec.cs1_lname, SUBSTR(cosigner_dtl_rec.p_last_name, 1, 35)),
372 x_cs1_fname => NVL(l_alt_borw_rec.cs1_fname, SUBSTR(cosigner_dtl_rec.p_first_name, 1, 12)),
373 x_cs1_mi_txt => NVL(l_alt_borw_rec.cs1_mi_txt, SUBSTR(cosigner_dtl_rec.p_middle_name, 1, 1)),
374 x_cs1_ssn_txt => NVL(l_alt_borw_rec.cs1_ssn_txt, SUBSTR(cosigner_dtl_rec.p_ssn, 1, 9)),
375 x_cs1_citizenship_status => NVL(l_alt_borw_rec.cs1_citizenship_status, SUBSTR(citizenship_dtl_rec.restatus_code, 1, 1)), -- rajagupt bug#5006587, instead of driving from person detail , now deriving from new mapping form
376 x_cs1_address_line_1_txt => NVL(l_alt_borw_rec.cs1_address_line_1_txt, SUBSTR(cosigner_dtl_rec.p_permt_addr1, 1, 30)),
377 x_cs1_address_line_2_txt => NVL(l_alt_borw_rec.cs1_address_line_2_txt, SUBSTR(cosigner_dtl_rec.p_permt_addr2, 1, 30)),
378 x_cs1_city_txt => NVL(l_alt_borw_rec.cs1_city_txt, SUBSTR(cosigner_dtl_rec.p_permt_city, 1, 24)),
379 x_cs1_state_txt => NVL(l_alt_borw_rec.cs1_state_txt, SUBSTR(cosigner_dtl_rec.p_permt_state, 1, 2)),
380 x_cs1_zip_txt => NVL(l_alt_borw_rec.cs1_zip_txt, SUBSTR(cosigner_dtl_rec.p_permt_zip, 1, 5)),
381 x_cs1_zip_suffix_txt => NVL(l_alt_borw_rec.cs1_zip_suffix_txt, SUBSTR(cosigner_dtl_rec.p_permt_zip, 6, 4)),
382 x_cs1_telephone_number_txt => NVL(l_alt_borw_rec.cs1_telephone_number_txt, SUBSTR(l_person_phone, 1, 10)),
383 x_cs1_signature_code_txt => l_alt_borw_rec.cs1_signature_code_txt ,
384 x_cs2_lname => l_alt_borw_rec.cs2_lname ,
385 x_cs2_fname => l_alt_borw_rec.cs2_fname ,
386 x_cs2_mi_txt => l_alt_borw_rec.cs2_mi_txt ,
387 x_cs2_ssn_txt => l_alt_borw_rec.cs2_ssn_txt ,
388 x_cs2_citizenship_status => l_alt_borw_rec.cs2_citizenship_status ,
389 x_cs2_address_line_1_txt => l_alt_borw_rec.cs2_address_line_1_txt ,
390 x_cs2_address_line_2_txt => l_alt_borw_rec.cs2_address_line_2_txt ,
391 x_cs2_city_txt => l_alt_borw_rec.cs2_city_txt ,
392 x_cs2_state_txt => l_alt_borw_rec.cs2_state_txt ,
393 x_cs2_zip_txt => l_alt_borw_rec.cs2_zip_txt ,
394 x_cs2_zip_suffix_txt => l_alt_borw_rec.cs2_zip_suffix_txt ,
395 x_cs2_telephone_number_txt => l_alt_borw_rec.cs2_telephone_number_txt ,
396 x_cs2_signature_code_txt => l_alt_borw_rec.cs2_signature_code_txt ,
397 x_cs1_credit_auth_code_txt => l_alt_borw_rec.cs1_credit_auth_code_txt ,
398 x_cs1_birth_date => NVL(l_alt_borw_rec.cs1_birth_date, cosigner_dtl_rec.p_date_of_birth),
399 x_cs1_drv_license_num_txt => NVL(l_alt_borw_rec.cs1_drv_license_num_txt, SUBSTR(cosigner_dtl_rec.p_license_num, 1, 20)),
400 x_cs1_drv_license_state_txt => NVL(l_alt_borw_rec.cs1_drv_license_state_txt, SUBSTR(cosigner_dtl_rec.p_license_state, 1, 2)),
401 x_cs1_elect_sig_ind_code_txt => l_alt_borw_rec.cs1_elect_sig_ind_code_txt ,
402 x_cs1_frgn_postal_code_txt => l_alt_borw_rec.cs1_frgn_postal_code_txt ,
403 x_cs1_frgn_tel_num_prefix_txt => l_alt_borw_rec.cs1_frgn_tel_num_prefix_txt ,
404 x_cs1_gross_annual_sal_num => l_alt_borw_rec.cs1_gross_annual_sal_num ,
405 x_cs1_mthl_auto_pay_txt => l_alt_borw_rec.cs1_mthl_auto_pay_txt ,
406 x_cs1_mthl_cc_pay_txt => l_alt_borw_rec.cs1_mthl_cc_pay_txt ,
407 x_cs1_mthl_edu_loan_pay_txt => l_alt_borw_rec.cs1_mthl_edu_loan_pay_txt ,
408 x_cs1_mthl_housing_pay_txt => l_alt_borw_rec.cs1_mthl_housing_pay_txt ,
409 x_cs1_mthl_other_pay_txt => l_alt_borw_rec.cs1_mthl_other_pay_txt ,
410 x_cs1_other_income_amt => l_alt_borw_rec.cs1_other_income_amt ,
411 x_cs1_rel_to_student_flag => NVL(l_alt_borw_rec.cs1_rel_to_student_flag,rel_code) , -- bvisvana - Bug # 4636920
412 x_cs1_suffix_txt => l_alt_borw_rec.cs1_suffix_txt ,
413 x_cs1_years_at_address_txt => l_alt_borw_rec.cs1_years_at_address_txt ,
414 x_cs2_credit_auth_code_txt => l_alt_borw_rec.cs2_credit_auth_code_txt ,
415 x_cs2_birth_date => l_alt_borw_rec.cs2_birth_date ,
416 x_cs2_drv_license_num_txt => l_alt_borw_rec.cs2_drv_license_num_txt ,
417 x_cs2_drv_license_state_txt => l_alt_borw_rec.cs2_drv_license_state_txt ,
418 x_cs2_elect_sig_ind_code_txt => l_alt_borw_rec.cs2_elect_sig_ind_code_txt ,
419 x_cs2_frgn_postal_code_txt => l_alt_borw_rec.cs2_frgn_postal_code_txt ,
420 x_cs2_frgn_tel_num_prefix_txt => l_alt_borw_rec.cs2_frgn_tel_num_prefix_txt ,
421 x_cs2_gross_annual_sal_num => l_alt_borw_rec.cs2_gross_annual_sal_num ,
422 x_cs2_mthl_auto_pay_txt => l_alt_borw_rec.cs2_mthl_auto_pay_txt ,
423 x_cs2_mthl_cc_pay_txt => l_alt_borw_rec.cs2_mthl_cc_pay_txt ,
424 x_cs2_mthl_edu_loan_pay_txt => l_alt_borw_rec.cs2_mthl_edu_loan_pay_txt ,
425 x_cs2_mthl_housing_pay_txt => l_alt_borw_rec.cs2_mthl_housing_pay_txt ,
426 x_cs2_mthl_other_pay_txt => l_alt_borw_rec.cs2_mthl_other_pay_txt ,
427 x_cs2_other_income_amt => l_alt_borw_rec.cs2_other_income_amt ,
428 x_cs2_rel_to_student_flag => l_alt_borw_rec.cs2_rel_to_student_flag ,
429 x_cs2_suffix_txt => l_alt_borw_rec.cs2_suffix_txt ,
430 x_cs2_years_at_address_txt => l_alt_borw_rec.cs2_years_at_address_txt
431 );
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
436 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.POPULATE_COSIGNER_DATA');
437 fnd_file.put_line(fnd_file.log,fnd_message.get || '-' || SQLERRM);
438 igs_ge_msg_stack.add;
439 app_exception.raise_exception;
440 END populate_cosigner_data;
441
442
443
444 FUNCTION get_fund_desc(p_fund_id IN NUMBER)
445 RETURN VARCHAR2 IS
446 CURSOR cur_get_fund_desc (p_fund_id NUMBER)
447 IS
448 SELECT fcat.fund_code||'-'||fmast.description description
449 FROM igf_aw_fund_mast_all fmast,
450 igf_aw_fund_cat_all fcat
451 WHERE fmast.fund_id = p_fund_id
452 AND fcat.fund_code = fmast.fund_code;
453
454 get_fund_desc_rec cur_get_fund_desc%ROWTYPE;
455
456 BEGIN
457
458 OPEN cur_get_fund_desc (p_fund_id);
459 FETCH cur_get_fund_desc INTO get_fund_desc_rec;
460 CLOSE cur_get_fund_desc;
461
462 RETURN get_fund_desc_rec.description;
463
464 END get_fund_desc;
465
466 FUNCTION check_fa_rec(p_base_id NUMBER,
467 p_cal_type VARCHAR2,
468 p_seq_number NUMBER)
469 RETURN BOOLEAN
470 IS
471 CURSOR cur_chk_fa (p_base_id NUMBER,
472 p_cal_type VARCHAR2,
473 p_seq_number NUMBER)
474 IS
475 SELECT base_id
476 FROM igf_ap_fa_base_rec_all
477 WHERE base_id = p_base_id AND
478 ci_cal_type = p_cal_type AND
479 ci_sequence_number = p_seq_number;
480
481 chk_fa_rec cur_chk_fa%ROWTYPE;
482
483 BEGIN
484
485 OPEN cur_chk_fa (p_base_id,p_cal_type,p_seq_number);
486 FETCH cur_chk_fa INTO chk_fa_rec;
487 CLOSE cur_chk_fa;
488 IF chk_fa_rec.base_id IS NULL THEN
489 RETURN FALSE;
490 ELSE
491 RETURN TRUE;
492 END IF;
493
494 END check_fa_rec;
495 --End of declarations for bug 2385334
496 -- Function to get the Loan Start Date
497 --
498
499 FUNCTION get_grp_name(p_per_grp_id IN NUMBER)
500 RETURN VARCHAR2 IS
501
502 CURSOR cur_get_grp_name (p_per_grp_id NUMBER)
503 IS
504 SELECT group_cd
505 FROM igs_pe_persid_group_all
506 WHERE group_id = p_per_grp_id;
507
508
509 get_grp_name_rec cur_get_grp_name%ROWTYPE;
510
511 BEGIN
512
513 OPEN cur_get_grp_name (p_per_grp_id);
514 FETCH cur_get_grp_name INTO get_grp_name_rec;
515 CLOSE cur_get_grp_name;
516
517 RETURN get_grp_name_rec.group_cd;
518
519 END get_grp_name;
520
521 PROCEDURE get_dl_entity_id(p_base_id IN NUMBER,
522 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
523 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
524 p_atd_entity_id_txt OUT NOCOPY VARCHAR2,
525 p_rep_entity_id_txt OUT NOCOPY VARCHAR2,
526 p_message OUT NOCOPY VARCHAR2,
527 p_return_status OUT NOCOPY VARCHAR2)
528 IS
529 CURSOR c_get_rep_entity_id_txt(
530 p_cal_type igs_ca_inst.cal_type%TYPE,
531 p_seq_num igs_ca_inst.sequence_number%TYPE,
532 p_atd_entity_id igf_gr_attend_pell.atd_entity_id_txt%TYPE
533 )
534 IS
535 SELECT rep.rep_entity_id_txt
536 FROM igf_gr_attend_pell gap, igf_gr_report_pell rep
537 WHERE gap.ci_cal_type = p_cal_type
538 AND gap.ci_sequence_number = p_seq_num
539 AND gap.atd_entity_id_txt = p_atd_entity_id
540 AND gap.rcampus_id = rep.rcampus_id;
541
542 l_ret_status VARCHAR2(30);
543 l_msg_data VARCHAR2(30);
544
545 BEGIN
546
547 p_atd_entity_id_txt := NULL;
548 p_rep_entity_id_txt := NULL;
549
550 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
551 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.get_dl_entity_id.debug','Entry p_base_id, p_cal_type, p_seq_num ' || p_base_id || ' : ' || p_cal_type || ' : ' || p_seq_num);
552 END IF;
553
554 -- Get attending Pell Id from Org Setup.
555 igf_sl_gen.get_stu_fao_code(
556 p_base_id => p_base_id,
557 p_office_type => 'ENTITY_ID',
558 x_office_cd => p_atd_entity_id_txt,
559 x_return_status => l_ret_status,
560 x_msg_data => l_msg_data
561 );
562
563 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
564 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.get_dl_entity_id.debug','after get fao code');
565 END IF;
566
567 IF (l_ret_status = 'E')
568 THEN
569 p_return_status := l_ret_status;
570 fnd_message.set_name('IGF', 'IGF_GR_NO_ATTEND_ENTITY_ID');
571 p_message := fnd_message.get;
572 RETURN;
573 END IF;
574
575 IF ((l_ret_status = 'S') AND (p_atd_entity_id_txt IS NOT NULL))
576 THEN
577
578 -- Derive the report pell ID.
579 OPEN c_get_rep_entity_id_txt(
580 p_cal_type,
581 p_seq_num,
582 p_atd_entity_id_txt
583 );
584 FETCH c_get_rep_entity_id_txt INTO p_rep_entity_id_txt;
585 CLOSE c_get_rep_entity_id_txt;
586 END IF;
587
588 IF (p_rep_entity_id_txt IS NULL)
589 THEN
590 fnd_message.set_name('IGF', 'IGF_GR_NOREP_ENTITY');
591 fnd_message.set_token('STU_NUMBER', igf_gr_gen.get_per_num(p_base_id));
592 p_message := fnd_message.get;
593 p_return_status := 'E';
594 RETURN;
595 END IF;
596
597 EXCEPTION
598
599 WHEN OTHERS THEN
600 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
601 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.GET_DL_ENTITY_ID');
602 fnd_file.put_line(fnd_file.log,SQLERRM);
603 igs_ge_msg_stack.add;
604 app_exception.raise_exception;
605
606 END get_dl_entity_id;
607
608 FUNCTION get_loan_start_dt ( p_award_id igf_aw_award_all.award_id%TYPE)
609 RETURN DATE
610 IS
611 --
612 -- Cursor to retrieve Loan Start Date
613 --
614 CURSOR cur_loan_start_dt ( p_award_id igf_aw_award_all.award_id%TYPE) IS
615 SELECT ld_cal_type,ld_sequence_number
616 FROM igf_aw_awd_disb awd
617 WHERE awd.award_id = p_award_id
618 AND awd.trans_type <> 'C'
619 GROUP BY awd.ld_cal_type,awd.ld_sequence_number;
620
621 CURSOR c_base_id(cp_award_id igf_aw_award_all.award_id%TYPE) IS
622 SELECT base_id
623 FROM igf_aw_award_all
624 WHERE award_id = cp_award_id;
625 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
626
627 p_start_dt DATE;
628 l_start_dt DATE;
629 l_end_dt DATE;
630 l_first_cycle VARCHAR2(1);
631
632 BEGIN
633 p_start_dt := NULL;
634 l_base_id := NULL;
635 l_start_dt := NULL;
636 l_end_dt := NULL;
637
638 OPEN c_base_id(p_award_id);
639 FETCH c_base_id INTO l_base_id;
640 CLOSE c_base_id;
641
642 l_first_cycle := 'Y';
643
644 FOR loan_start_dt_rec IN cur_loan_start_dt(p_award_id) LOOP
645 igf_ap_gen_001.get_term_dates(
646 p_base_id => l_base_id,
647 p_ld_cal_type => loan_start_dt_rec.ld_cal_type,
648 p_ld_sequence_number => loan_start_dt_rec.ld_sequence_number,
649 p_ld_start_date => l_start_dt,
650 p_ld_end_date => l_end_dt
651 );
652 IF l_first_cycle = 'Y' THEN
653 p_start_dt := l_start_dt;
654 l_first_cycle := 'N';
655 ELSE
656 p_start_dt := LEAST(p_start_dt,l_start_dt);
657 END IF;
658 END LOOP;
659 RETURN p_start_dt;
660 END get_loan_start_dt;
661
662 --
663 -- Function to get the Loan End Date
664 --
665
666 FUNCTION get_loan_end_dt ( p_award_id igf_aw_award_all.award_id%TYPE)
667 RETURN DATE
668 IS
669
670
671 --
672 -- Cursor to Retrieve Loan End Date
673 --
674 CURSOR cur_loan_end_dt ( p_award_id igf_aw_award_all.award_id%TYPE) IS
675 SELECT ld_cal_type,ld_sequence_number
676 FROM igf_aw_awd_disb awd
677 WHERE awd.award_id = p_award_id
678 AND awd.trans_type <> 'C'
679 GROUP BY awd.ld_cal_type,awd.ld_sequence_number;
680
681 CURSOR c_base_id(cp_award_id igf_aw_award_all.award_id%TYPE) IS
682 SELECT base_id
683 FROM igf_aw_award_all
684 WHERE award_id = cp_award_id;
685 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
686
687 p_end_dt DATE;
688 l_start_dt DATE;
689 l_end_dt DATE;
690 l_first_cycle VARCHAR2(1);
691
692 BEGIN
693
694 p_end_dt := NULL;
695 l_base_id := NULL;
696 l_start_dt := NULL;
697 l_end_dt := NULL;
698
699 OPEN c_base_id(p_award_id);
700 FETCH c_base_id INTO l_base_id;
701 CLOSE c_base_id;
702
703 l_first_cycle := 'Y';
704
705 FOR loan_end_dt_rec IN cur_loan_end_dt(p_award_id) LOOP
706 igf_ap_gen_001.get_term_dates(
707 p_base_id => l_base_id,
708 p_ld_cal_type => loan_end_dt_rec.ld_cal_type,
709 p_ld_sequence_number => loan_end_dt_rec.ld_sequence_number,
710 p_ld_start_date => l_start_dt,
711 p_ld_end_date => l_end_dt
712 );
713 IF l_first_cycle = 'Y' THEN
714 p_end_dt := l_end_dt;
715 l_first_cycle := 'N';
716 ELSE
717 p_end_dt := GREATEST(p_end_dt,l_end_dt);
718 END IF;
719 END LOOP;
720 RETURN p_end_dt;
721 END get_loan_end_dt;
722
723 PROCEDURE log_message(p_award_id igf_aw_award.award_id%TYPE) IS
724 BEGIN
725
726 IF g_log_start_flag = FALSE THEN
727 fnd_file.new_line(fnd_file.log,1);
728
729 IF g_log_title IS NULL THEN
730 g_log_title := igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','AWARD_ID')||' : ';
731 END IF;
732
733 fnd_file.put_line(fnd_file.log, g_log_title||TO_CHAR(p_award_id));
734 g_log_start_flag := TRUE;
735 END IF;
736
737 END log_message;
738
739
740 FUNCTION ret_loan_number(p_loan_seq_number IN igf_sl_loans.seq_num%TYPE,
741 p_base_id IN igf_aw_award_v.base_id%TYPE)
742 RETURN VARCHAR2
743 IS
744 /***************************************************************
745 Created By : mesriniv
746 Date Created By : 2000/11/13
747 Purpose : To arrive at the Loan Number
748 Known Limitations,Enhancements or Remarks
749
750
751 --
752 --
753 Change History :
754 --Bug 2470130 Desc : DL Formatting Errors.
755 Who When What
756 ugummall 23-OCT-2003 Bug 3102439. FA 126 - Multiple FA Offices.
757 In constructing loan_number for Direct Loan, School ID is derived from the
758 student's associated Org. For this purpose base_id(as extra parameter) is
759 passed to this function.
760 Similarly, OPE ID and School Non Ed Brc ID are also derived for Common Line Loan.
761 mesriniv 26-jul-2002 Used DL Version to get the program year instead of calendar end date.
762
763 Bug Id : 1720677
764 Desc : Mapping of school id in the CommonLine Setup
765 to ope_id of Financial Aid Office Setup.
766 Who When What
767 mesriniv 05-APR-2001 Changed the occurrences of field fao_id
768 to ope_id
769 ***************************************************************/
770
771 lv_loan_type VARCHAR2(100);
772 lv_loan_yr VARCHAR2(2);
773 lv_loan_number igf_sl_loans_all.loan_number%TYPE;
774 lv_incr_seq NUMBER;
775 lv_incr_seq_char VARCHAR2(100);
776 l_ssn igf_ap_isir_matched_all.current_ssn%TYPE;
777
778 x_return_status VARCHAR2(1);
779 x_msg_data VARCHAR2(30);
780 x_dlsch_cd igs_or_org_alt_ids.org_alternate_id%TYPE;
781 x_ope_cd igs_or_org_alt_ids.org_alternate_id%TYPE;
782 x_sch_non_ed_brc_cd igs_or_org_alt_ids.org_alternate_id%TYPE;
783
784 CURSOR cur_loan_num (p_loan_number igf_sl_loans_all.loan_number%TYPE) IS
785 SELECT rowid
786 FROM igf_sl_loans_all
787 WHERE loan_number = p_loan_number;
788
789 loan_num_rec cur_loan_num%ROWTYPE;
790
791 BEGIN
792
793 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
794 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','Entry ret_loan_number');
795 END IF;
796
797 IF igf_sl_gen.chk_dl_fed_fund_code(award_rec.fed_fund_code) = 'TRUE' THEN
798
799 -- Direct Loan's Loan Number
800 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
801 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','DL Loan Number Step 1');
802 END IF;
803
804 -- Loan TYPE is S from DLS,P from DLP and U from DLU
805 lv_loan_type := SUBSTR(TRIM(award_rec.fed_fund_code),3,1);
806
807 --Bug 2470130 ,To get the Program Year
808 lv_loan_yr := SUBSTR(dl_setup_rec.dl_version,8,2);
809
810 l_ssn:=NULL;
811 l_ssn := igf_gr_gen.get_ssn_digits(award_rec.ssn);
812
813 -- Bug 3102439. To get School ID for Direct Loan.
814 igf_sl_gen.get_stu_fao_code(p_base_id, 'DL_SCH_CD', x_dlsch_cd, x_return_status, x_msg_data);
815 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
816 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','after get DL SCHOOL CODE');
817 END IF;
818 IF (x_return_status = 'E') THEN
819 log_message(award_rec.award_id);
820 fnd_message.set_name('IGF', x_msg_data);
821 fnd_file.put_line(fnd_file.log, fnd_message.get);
822 RAISE SKIP_THIS_RECORD;
823 END IF;
824 lv_loan_number := LPAD(l_ssn,9,'0')
825 || lv_loan_type
826 || lv_loan_yr
827 || x_dlsch_cd
828 || LPAD(TO_CHAR(p_loan_seq_number),3,'0');
829
830 OPEN cur_loan_num (lv_loan_number);
831 FETCH cur_loan_num INTO loan_num_rec;
832 IF (cur_loan_num%FOUND) THEN
833 CLOSE cur_loan_num;
834
835 log_message(award_rec.award_id);
836 fnd_message.set_name('IGF','IGF_SL_SSN_IN_USE');
837 fnd_message.set_token('VALUE',award_rec.ssn);
838 fnd_message.set_token('PER_NUM',award_rec.person_number);
839 fnd_file.put_line(fnd_file.log,fnd_message.get);
840 RAISE SKIP_THIS_RECORD;
841 ELSE
842 CLOSE cur_loan_num;
843 END IF;
844
845
846 ELSIF igf_sl_gen.chk_cl_fed_fund_code(award_rec.fed_fund_code) = 'TRUE' THEN
847
848 -- CommonLine Loan's Loan Number
849 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
850 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','FFEL Loan Number Step 1');
851 END IF;
852
853 -- Range is 1 to 46655. So, RECYCLE option should be provided.
854 SELECT igf_sl_cl_loan_seq_s.NEXTVAL into lv_incr_seq FROM DUAL;
855
856 lv_incr_seq_char := igf_sl_gen.base10_to_base36(lv_incr_seq);
857
858 -- Bug 3102439. To get OPE ID.
859 igf_sl_gen.get_stu_fao_code(p_base_id, 'OPE_ID_NUM', x_ope_cd, x_return_status, x_msg_data);
860 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
861 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','FFEL Loan Number Step 2 x_ope_cd ' || x_ope_cd);
862 END IF;
863 IF (x_return_status = 'E') THEN
864 log_message(award_rec.award_id);
865 fnd_message.set_name('IGF', x_msg_data);
866 fnd_file.put_line(fnd_file.log, fnd_message.get);
867 RAISE SKIP_THIS_RECORD;
868 END IF;
869
870 -- Bug 3102439. To get SCH_NON_ED_BRC_ID.
871 igf_sl_gen.get_stu_fao_code(p_base_id, 'SCH_NON_ED_BRC_ID', x_sch_non_ed_brc_cd, x_return_status, x_msg_data);
872 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
873 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','FFEL Loan Number Step 3 x_sch_non_ed_brc_cd ' || x_sch_non_ed_brc_cd ||' : x_msg_data ' || x_msg_data);
874 END IF;
875 IF (x_return_status = 'E') THEN
876 IF (x_msg_data = 'IGF_AP_SCH_NONED_NOTFND') THEN
877 -- construct source non ed branch id from the last two digits of the school id
878 -- school id assigned by the ED will always be of 8 characters in length
879 x_sch_non_ed_brc_cd := substr(x_ope_cd,7,2);
880 ELSE
881 log_message(award_rec.award_id);
882 fnd_message.set_name('IGF', x_msg_data);
883 fnd_file.put_line(fnd_file.log, fnd_message.get);
884 RAISE SKIP_THIS_RECORD;
885 END IF;
886 END IF;
887 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
888 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug','Construct Loan Number ');
889 END IF;
890
891 lv_loan_number := LPAD(x_ope_cd, 6,'0')
892 || LPAD(NVL(x_sch_non_ed_brc_cd,'0'),4,'0')
893 || '1' -- Hardcoded the Computer Number.
894 || LPAD(p_incr_date_code,3,'0')
895 || LPAD(lv_incr_seq_char,3,'0');
896 END IF;
897
898 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
899 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.ret_loan_number.debug',' Loan Number = lv_loan_number ' || lv_loan_number);
900 END IF;
901
902 RETURN lv_loan_number;
903
904 EXCEPTION
905
906 WHEN SKIP_THIS_RECORD THEN
907 RAISE;
908
909 WHEN NO_DATA_FOUND THEN
910 fnd_message.set_name('IGF','IGF_GE_NO_DATA_FOUND');
911 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.RET_LOAN_NUMBER');
912 igs_ge_msg_stack.add;
913 app_exception.raise_exception;
914
915 WHEN OTHERS THEN
916 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
917 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.RET_LOAN_NUMBER');
918 fnd_file.put_line(fnd_file.log,SQLERRM);
919 igs_ge_msg_stack.add;
920 app_exception.raise_exception;
921
922 END ret_loan_number;
923
924 PROCEDURE get_dl_cl_std_code ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
925 p_class_standing IN igs_pr_css_class_std_v.class_standing%TYPE ,
926 p_program_type IN igs_ps_ver_all.course_type%TYPE ,
927 p_dl_std_code OUT NOCOPY igf_ap_class_std_map.dl_std_code%TYPE ,
928 p_cl_std_code OUT NOCOPY igf_ap_class_std_map.cl_std_code%TYPE )
929 AS
930 /***************************************************************
931 Created By : masehgal
932 Date Created By : 10-Oct-2002
933 Purpose : # 2591960 Integration Enhancements
934 To obtain the dl/cl std codes
935 Validation for FRESHMAN
936 Known Limitations,Enhancements or Remarks
937 Change History :
938 Who When What
939 ***************************************************************/
940
941 -- Cursor to get person_id, ci_cal_type and ci_sequence_number
942 CURSOR c_person_info ( cp_base_id igf_ap_fa_base_rec.base_id%TYPE ) IS
943 SELECT person_id, ci_cal_type, ci_sequence_number
944 FROM igf_ap_fa_base_rec
945 WHERE base_id = cp_base_id ;
946 lv_person_info_rec c_person_info%ROWTYPE ;
947
948 -- Cursor to get cs_schdl_id, class_std_id, css_class_std_id
949 CURSOR c_class_std ( cp_class_standing igs_pr_css_class_std_v.class_standing%TYPE ,
950 cp_program_type igs_ps_ver_all.course_type%TYPE ) IS
951 SELECT CSSV.igs_pr_cs_schdl_id ,
952 CSSV.igs_pr_class_std_id ,
953 CSSV.igs_pr_css_class_std_id
954 FROM igs_pr_css_class_std_v CSSV
955 WHERE CSSV.igs_pr_cs_schdl_id = (SELECT igs_pr_cs_schdl_id
956 FROM igs_pr_cs_schdl CS
957 WHERE CS.course_type = cp_program_type )
958 AND CSSV.class_standing = cp_class_standing ;
959 lv_class_std_rec c_class_std%ROWTYPE ;
960
961 -- Cursor to get dl_std_code
962 CURSOR c_dl_cl_std_code ( cp_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE ,
963 cp_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE ,
964 cp_cs_schdl_id igf_ap_pr_prg_type.igs_pr_cs_schdl_id%TYPE ,
965 cp_css_class_std_id igf_ap_class_std_map.igs_pr_css_class_std_id%TYPE ) IS
966 SELECT CSM.dl_std_code,
967 CSM.cl_std_code
968 FROM igf_ap_class_std_map CSM ,
969 igf_ap_pr_prg_type PPT
970 WHERE PPT.ppt_id = CSM.ppt_id
971 AND PPT.igs_pr_cs_schdl_id = cp_cs_schdl_id
972 AND PPT.cal_type = cp_ci_cal_type
973 AND PPT.sequence_number = cp_ci_sequence_number
974 AND CSM.igs_pr_css_class_std_id = cp_css_class_std_id ;
975 lv_dl_cl_std_code_rec c_dl_cl_std_code%ROWTYPE ;
976
977 -- Cursor to get acad history ( institutions attended by the student )
978 CURSOR c_get_acad_hist ( cp_person_id igf_ap_fa_base_rec.person_id%TYPE ) IS
979 SELECT COUNT(institution_code)
980 FROM igs_ad_acad_history_v acad, igs_or_org_inst_type org
981 WHERE acad.institution_type = org.institution_type
982 AND person_id = cp_person_id
983 AND org.system_inst_type = 'POST-SECONDARY' ;
984
985 lv_acad_hist_rec c_get_acad_hist%ROWTYPE;
986 l_count NUMBER(5);
987
988 BEGIN
989 l_count :=0;
990 -- GET person_id, ci_cal_type , ci_sequence_number using base_id
991 OPEN c_person_info ( p_base_id ) ;
992 FETCH c_person_info INTO lv_person_info_rec ;
993 CLOSE c_person_info ;
994
995 -- GET cs_schdl_id, class_std_id, css_class_std_id
996 OPEN c_class_std ( p_class_standing, p_program_type ) ;
997 FETCH c_class_std INTO lv_class_std_rec ;
998 CLOSE c_class_std ;
999
1000 -- GET direct loan std code using ci_cal_type, ci_sequence_number, schdl_id and class_standing_id
1001 OPEN c_dl_cl_std_code ( lv_person_info_rec.ci_cal_type,
1002 lv_person_info_rec.ci_sequence_number,
1003 lv_class_std_rec.igs_pr_cs_schdl_id,
1004 lv_class_std_rec.igs_pr_css_class_std_id ) ;
1005 FETCH c_dl_cl_std_code INTO lv_dl_cl_std_code_rec ;
1006 IF c_dl_cl_std_code%NOTFOUND THEN
1007 CLOSE c_dl_cl_std_code ;
1008
1009 RETURN ;
1010 -- Here we are returning NULL for dl_std_code, cl_std_code
1011 -- In the calling procedure we will check if it is NULL . If so,
1012 -- Log a message that no set up has ben done for this particular class standing
1013 -- i.e. No DL/CL Grade has been attached to this particular class standing in this particular award year
1014 ELSE
1015 CLOSE c_dl_cl_std_code ;
1016 END IF ;
1017
1018 p_dl_std_code := lv_dl_cl_std_code_rec.dl_std_code ;
1019 p_cl_std_code := lv_dl_cl_std_code_rec.cl_std_code ;
1020
1021 -- CHECK for DL_STD_CODE ( '0/1' )
1022 IF p_dl_std_code = '0/1' THEN
1023 -- Check for previous institutions attended by the student
1024 OPEN c_get_acad_hist(lv_person_info_rec.person_id) ;
1025 FETCH c_get_acad_hist INTO l_count;
1026 CLOSE c_get_acad_hist;
1027
1028 IF l_count =0 THEN
1029 p_dl_std_code := '0' ;
1030 ELSE
1031 p_dl_std_code := '1';
1032 END IF;
1033 END IF ;
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1038 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.GET_DL_CL_STD_CODE');
1039 fnd_file.put_line(fnd_file.log,SQLERRM);
1040 igs_ge_msg_stack.add;
1041 app_exception.raise_exception;
1042 END get_dl_cl_std_code ;
1043
1044
1045 PROCEDURE return_entities ( P_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1046 p_grd_dl OUT NOCOPY igf_sl_lor.grade_level_code%TYPE,
1047 p_enrl_stat OUT NOCOPY igf_sl_lor.enrollment_code%TYPE,
1048 p_grd_cl OUT NOCOPY igf_sl_lor.grade_level_code%TYPE,
1049 p_anticip_comp_date OUT NOCOPY igf_sl_lor.anticip_compl_date%TYPE) IS
1050 /***************************************************************
1051 Created By : kpadiyar
1052 Date Created By : 2001/04/26
1053 Purpose :
1054 Known Limitations,Enhancements or Remarks
1055
1056 Change History :
1057 Who When What
1058 rasahoo 03-Sep-2003 Removed cursor c_fabaseh and its references
1059 as part of FA-114(Obsoletion of FA base record History)
1060 masehgal 18-Dec-2002 # 2477912 Corrected variables to collect dl/cl std codes
1061 masehgal 10-Oct-2002 # 2591960 Integration Enhancements
1062 Seperated logic to get dl/cl std code to a new procedure
1063 pmarada 23-Aug-2001 Get Enrollmen, Grade-level dtls
1064 from FA-Base-Hist table
1065 Enh Bug 1818617
1066 skoppula 29-May-2002 The class standing shall be picked
1067 up from igf_aw_packng_subfns.get_class_stnd
1068 instead from igf_ap_fa_base_h. This is
1069 done to determine which class standing
1070 to be used - Actual or Predictive
1071 Bug:2385334
1072 ***************************************************************/
1073
1074 CURSOR c_cal_seq (cp_baseid NUMBER) IS
1075 SELECT ci_cal_type, ci_sequence_number
1076 FROM igf_ap_fa_base_rec
1077 WHERE base_id = cp_baseid;
1078
1079 CURSOR c_awd_yr (cp_cal_type igs_ca_inst.cal_type%TYPE ,
1080 cp_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1081 SELECT alternate_code
1082 FROM igs_ca_inst
1083 WHERE cal_type = cp_cal_type
1084 AND sequence_number = cp_sequence_number ;
1085
1086 l_person_number igf_ap_fa_con_v.person_number%TYPE;
1087 l_alternate_code igs_ca_inst_all.alternate_code%TYPE;
1088 l_cal_seq_rec c_cal_seq%ROWTYPE;
1089 l_ci_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE;
1090 l_ci_sequence_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
1091
1092
1093 -- Declarations as part of bug 2385334
1094 lv_class_standing igs_pr_css_class_std_v.class_standing%TYPE;
1095 lv_program_type igs_ps_ver_all.course_type%TYPE;
1096 -- defined for Integration Enhancements build
1097 lv_dl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
1098 lv_cl_std_code igf_ap_class_std_map.dl_std_code%TYPE;
1099
1100 BEGIN
1101
1102 lv_dl_std_code := NULL;
1103 lv_cl_std_code := NULL;
1104 p_anticip_comp_date := igf_ap_gen_001.get_anticip_compl_date(P_base_id);
1105
1106 OPEN c_cal_seq (p_base_id);
1107 FETCH c_cal_seq INTO l_cal_seq_rec;
1108 CLOSE c_cal_seq;
1109 l_ci_cal_type := l_cal_seq_rec.ci_cal_type;
1110 l_ci_sequence_number := l_cal_seq_rec.ci_sequence_number;
1111
1112 OPEN c_awd_yr (l_ci_cal_type, l_ci_sequence_number);
1113 FETCH c_awd_yr INTO l_alternate_code;
1114 CLOSE c_awd_yr;
1115
1116 l_person_number := igf_gr_gen.get_per_num ( p_base_id);
1117 -- Call to get the class standing and program_type
1118 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1119 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.return_entities.debug','The values passed to igf_aw_packng_subfns.get_class_stnd base id :' || p_base_id);
1120 END IF;
1121
1122 lv_class_standing := igf_aw_packng_subfns.get_class_stnd( p_base_id,
1123 g_person_id,
1124 g_adplans_id,
1125 g_award_id,
1126 lv_program_type ) ;
1127 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1128 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.return_entities.debug','The class standing from igf_aw_packng_subfns.get_class_stnd :' || lv_class_standing);
1129 END IF;
1130
1131 IF lv_class_standing IS NULL OR lv_program_type IS NULL THEN
1132 -- Bug # 5078693 - bvisvana - Message out that Class standing has to be defined.
1133 fnd_message.set_name('IGF','IGF_SL_NO_CLSTND');
1134 fnd_file.put_line(fnd_file.log,fnd_message.get);
1135 RAISE SKIP_THIS_RECORD;
1136 END IF;
1137 -- Call to get the dl_std_code
1138 get_dl_cl_std_code ( p_base_id,
1139 lv_class_standing,
1140 lv_program_type ,
1141 lv_dl_std_code ,
1142 lv_cl_std_code );
1143
1144 IF lv_dl_std_code IS NULL THEN
1145 -- Bug 5078693 - bvisvana
1146 fnd_message.set_name('IGF','IGF_SL_NO_CLSTND_GRDLVL');
1147 fnd_file.put_line(fnd_file.log,fnd_message.get);
1148 ELSE
1149 p_grd_dl := lv_dl_std_code ;
1150 END IF ;
1151
1152 IF lv_cl_std_code IS NULL THEN
1153 -- Bug 5078693 - bvisvana
1154 fnd_message.set_name('IGF','IGF_SL_NO_CLSTND_GRDLVL');
1155 fnd_file.put_line(fnd_file.log,fnd_message.get);
1156 ELSE
1157 p_grd_cl := lv_cl_std_code ;
1158 END IF ;
1159
1160 EXCEPTION
1161 WHEN SKIP_THIS_RECORD THEN
1162 RAISE SKIP_THIS_RECORD;
1163 WHEN NO_DATA_FOUND THEN
1164 NULL;
1165 END return_entities;
1166
1167 PROCEDURE get_fa_base_details(p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1168 p_s_default_status OUT NOCOPY igf_sl_lor.s_default_status%TYPE,
1169 p_grade_dl OUT NOCOPY igf_sl_lor.grade_level_code%TYPE,
1170 p_grade_cl OUT NOCOPY igf_sl_lor.grade_level_code%TYPE,
1171 p_enroll_code OUT NOCOPY igf_sl_lor.enrollment_code%TYPE,
1172 p_isir_present OUT NOCOPY BOOLEAN,
1173 p_anticip_comp_date OUT NOCOPY igf_sl_lor.anticip_compl_date%TYPE,
1174 p_transaction_num OUT NOCOPY igf_ap_isir_matched_all.transaction_num%TYPE,
1175 p_unsub_elig_for_depnt OUT NOCOPY igf_sl_lor.unsub_elig_for_depnt%TYPE
1176 )
1177 IS
1178 /***************************************************************
1179 Created By : mesriniv
1180 Date Created By : 2000/11/15
1181 Purpose : Return the nslds flag match status,
1182 Grade Level, Enrollment Code
1183 for the Current Base Id
1184 Known Limitations,Enhancements or Remarks
1185 Change History :
1186 Who When What
1187 ***************************************************************/
1188
1189 lv_s_default_status igf_sl_lor_all.s_default_status%TYPE;
1190 lv_match_flag igf_ap_isir_matched_all.nslds_match_flag%TYPE;
1191 lv_nslds_data_override_flg igf_ap_fa_base_rec_all.nslds_data_override_flg%TYPE;
1192 lv_adnl_unsub_loan_elig_flag igf_ap_fa_base_rec_all.adnl_unsub_loan_elig_flag%TYPE;
1193
1194 CURSOR cur_nslds_data IS
1195 SELECT isirm.nslds_match_flag, NVL(fabase.nslds_data_override_flg,'N'),isirm.transaction_num,
1196 NVL(fabase.adnl_unsub_loan_elig_flag,'N') adnl_unsub_loan_elig_flag -- FA134
1197 FROM igf_ap_isir_matched_all isirm,
1198 igf_ap_fa_base_rec_all fabase
1199 WHERE fabase.base_id = p_base_id
1200 AND fabase.base_id = isirm.base_id
1201 AND isirm.payment_isir = 'Y'
1202 AND isirm.system_record_type = 'ORIGINAL';
1203
1204 BEGIN
1205 p_isir_present := FALSE;
1206
1207 OPEN cur_nslds_data;
1208 FETCH cur_nslds_data INTO lv_match_flag,
1209 lv_nslds_data_override_flg, p_transaction_num, lv_adnl_unsub_loan_elig_flag;--fa134
1210 IF cur_nslds_data%NOTFOUND THEN
1211 p_isir_present := FALSE;
1212 ELSE
1213 IF lv_nslds_data_override_flg='Y' THEN
1214 lv_s_default_status:='Z';
1215 ELSE
1216 IF lv_match_flag='1' THEN
1217 lv_s_default_status:='N';
1218 ELSIF lv_match_flag IN ('2','3','4','7','8') THEN
1219 lv_s_default_status:='Y';
1220 END IF;
1221 END IF;
1222 p_isir_present := TRUE;
1223 END IF;
1224
1225 CLOSE cur_nslds_data;
1226
1227 p_s_default_status := lv_s_default_status;
1228 return_entities(p_base_id, p_grade_dl, p_enroll_code, p_grade_cl,p_anticip_comp_date);
1229
1230 EXCEPTION
1231 WHEN SKIP_THIS_RECORD THEN
1232 RAISE SKIP_THIS_RECORD;
1233 WHEN OTHERS THEN
1234 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1235 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.GET_FA_BASE_DETAILS');
1236 fnd_file.put_line(fnd_file.log,SQLERRM);
1237 igs_ge_msg_stack.add;
1238 app_exception.raise_exception;
1239 END get_fa_base_details;
1240
1241
1242
1243 /* Procedure to insert Direct Loan Originations into IGF_SL_LOR */
1244
1245 PROCEDURE insert_lor_dl_records(
1246 p_cal_type IN igs_ca_inst.cal_type%TYPE,
1247 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1248 p_loan_id IN igf_sl_loans.loan_id%TYPE
1249 )AS
1250 /***************************************************************
1251 Created By : mesriniv
1252 Date Created By : 2000/11/13
1253 Purpose : To Insert Records into IGF_SL_LOR for DL Loans
1254 Known Limitations,Enhancements or Remarks
1255
1256 Change History :
1257 Who When What
1258 bvisvana 10-Apr-2006 Build FA161.
1259 TBH impact change in igf_sl_lor_pkg.insert_row().
1260 pkpatel 12-05-2001 Given default value for the parameter
1261 pnote_status
1262 ***************************************************************/
1263 CURSOR getloannumber (cp_loan_id NUMBER) is
1264 SELECT LOAN_NUMBER
1265 FROM IGF_SL_LOANS_ALL lar
1266 WHERE lar.LOAN_ID = cp_loan_id;
1267
1268 lv_row_id ROWID;
1269 ln_origination_id igf_sl_lor_all.origination_id%TYPE;
1270 ld_sch_cert_date DATE;
1271 lv_orig_fee_perct igf_sl_dl_setup_all.orig_fee_perct_stafford%TYPE;
1272 lv_match_flag igf_ap_isir_matched_all.nslds_match_flag%TYPE;
1273 lv_acad_cal_type igs_ca_inst_all.cal_type%TYPE ;
1274 lv_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
1275 lv_acad_begin_date igs_ca_inst_all.start_dt%TYPE;
1276 lv_acad_end_date igs_ca_inst_all.end_dt%TYPE;
1277 lv_message VARCHAR2(100);
1278 c_loan_number getloannumber%ROWTYPE;
1279 l_parent_person_id NUMBER;
1280 l_student_person_id NUMBER;
1281
1282 BEGIN
1283
1284 -- Origination_Id will be populated from Sequence.
1285 -- Loan_Id is got from the Current value of sequence returned.
1286 -- Sch_cert_date is the Award Creation Date
1287 OPEN getloannumber(p_loan_id);
1288 FETCH getloannumber INTO c_loan_number;
1289 CLOSE getloannumber;
1290
1291 igf_sl_dl_record.get_acad_cal_dtls (c_loan_number.LOAN_NUMBER,
1292 lv_acad_cal_type,
1293 lv_acad_seq_num,
1294 lv_acad_begin_date,
1295 lv_acad_end_date,
1296 lv_message );
1297
1298 -- bvisvana - Bug 5078761
1299 IF lv_message IS NOT NULL THEN
1300 fnd_message.set_name(substr(lv_message,1,3),lv_message);
1301 fnd_file.put_line(fnd_file.log,fnd_message.get);
1302 END IF;
1303
1304 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1305 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_dl_records.debug',' Acad Begin Date :' || lv_acad_begin_date);
1306 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_dl_records.debug',' Acad End Date :' || lv_acad_end_date);
1307 END IF;
1308
1309 IF igf_sl_gen.chk_dl_stafford(award_rec.fed_fund_code) = 'TRUE' THEN
1310 lv_orig_fee_perct := dl_setup_rec.orig_fee_perct_stafford;
1311 ELSIF igf_sl_gen.chk_dl_plus(award_rec.fed_fund_code) = 'TRUE' THEN
1312 lv_orig_fee_perct := dl_setup_rec.orig_fee_perct_plus;
1313 END IF;
1314
1315
1316 IF igf_sl_gen.chk_dl_plus(award_rec.fed_fund_code) = 'TRUE' THEN -- Check if that is plus/alt loans and get parent id for the same.
1317 get_borrower_parent_id (
1318 p_loan_id => p_loan_id,
1319 p_parent_person_id => l_parent_person_id,
1320 p_student_person_id => l_student_person_id
1321 );
1322 END IF; -- END Check if that is plus/alt loans and get parent id for the same.
1323
1324
1325
1326 -- With these Default values need to insert data into igf_sl_lor table
1327 lv_row_id := NULL;
1328 ln_origination_id := NULL;
1329
1330 igf_sl_lor_pkg.insert_row (
1331 x_mode => 'R',
1332 x_rowid => lv_row_id,
1333 x_origination_id => ln_origination_id,
1334 x_loan_id => p_loan_id,
1335 x_sch_cert_date => TRUNC(award_rec.creation_date),
1336 x_orig_status_flag => NULL,
1337 x_orig_batch_id => NULL,
1338 x_orig_batch_date => NULL,
1339 x_chg_batch_id => NULL,
1340 x_orig_ack_date => NULL,
1341 x_credit_override => NULL,
1342 x_credit_decision_date => NULL,
1343 x_req_serial_loan_code => NULL,
1344 x_act_serial_loan_code => NULL,
1345 x_pnote_delivery_code => NULL,
1346 x_pnote_status => 'N',
1347 x_pnote_status_date => NULL,
1348 x_pnote_id => NULL,
1349 x_pnote_print_ind => dl_setup_rec.pnote_print_ind,
1350 x_pnote_accept_amt => NULL,
1351 x_pnote_accept_date => NULL,
1352 x_unsub_elig_for_heal => 'N',
1353 x_disclosure_print_ind => dl_setup_rec.disclosure_print_ind,
1354 x_orig_fee_perct => lv_orig_fee_perct,
1355 x_borw_confirm_ind => NULL,
1356 x_borw_interest_ind => NULL,
1357 x_borw_outstd_loan_code => NULL,
1358 x_unsub_elig_for_depnt => gv_unsub_elig_for_depnt,
1359 x_guarantee_amt => NULL,
1360 x_guarantee_date => NULL,
1361 x_guarnt_amt_redn_code => NULL,
1362 x_guarnt_status_code => NULL,
1363 x_guarnt_status_date => NULL,
1364 x_lend_apprv_denied_code => NULL,
1365 x_lend_apprv_denied_date => NULL,
1366 x_lend_status_code => NULL,
1367 x_lend_status_date => NULL,
1368 x_guarnt_adj_ind => NULL,
1369 x_grade_level_code => g_grade_level_dl,
1370 x_enrollment_code => NULL,
1371 x_anticip_compl_date => NULL,
1372 x_borw_lender_id => NULL,
1373 x_duns_borw_lender_id => NULL,
1374 x_guarantor_id => NULL,
1375 x_duns_guarnt_id => NULL,
1376 x_prc_type_code => NULL,
1377 x_cl_seq_number => NULL,
1378 x_last_resort_lender => NULL,
1379 x_lender_id => NULL,
1380 x_duns_lender_id => NULL,
1381 x_lend_non_ed_brc_id => NULL,
1382 x_recipient_id => NULL,
1383 x_recipient_type => NULL,
1384 x_duns_recip_id => NULL,
1385 x_recip_non_ed_brc_id => NULL,
1386 x_rec_type_ind => NULL,
1387 x_cl_loan_type => NULL,
1388 x_cl_rec_status => NULL,
1389 x_cl_rec_status_last_update => NULL,
1390 x_alt_prog_type_code => NULL,
1391 x_alt_appl_ver_code => NULL,
1392 x_mpn_confirm_code => NULL,
1393 x_resp_to_orig_code => NULL,
1394 x_appl_loan_phase_code => NULL,
1395 x_appl_loan_phase_code_chg => NULL,
1396 x_appl_send_error_codes => NULL,
1397 x_tot_outstd_stafford => NULL,
1398 x_tot_outstd_plus => NULL,
1399 x_alt_borw_tot_debt => NULL,
1400 x_act_interest_rate => NULL,
1401 x_service_type_code => NULL,
1402 x_rev_notice_of_guarnt => NULL,
1403 x_sch_refund_amt => NULL,
1404 x_sch_refund_date => NULL,
1405 x_uniq_layout_vend_code => NULL,
1406 x_uniq_layout_ident_code => NULL,
1407 x_p_person_id => l_parent_person_id, -- FA 157 -- derived single parent ID.
1408 x_p_ssn_chg_date => NULL,
1409 x_p_dob_chg_date => NULL,
1410 x_p_permt_addr_chg_date => NULL,
1411 x_p_default_status => 'N',
1412 x_p_signature_code => NULL,
1413 x_p_signature_date => NULL,
1414 x_s_ssn_chg_date => NULL,
1415 x_s_dob_chg_date => NULL,
1416 x_s_permt_addr_chg_date => NULL,
1417 x_s_local_addr_chg_date => NULL,
1418 x_s_default_status => g_s_default_status,
1419 x_s_signature_code => NULL,
1420 x_pnote_batch_id => NULL,
1421 x_pnote_ack_date => NULL,
1422 x_pnote_mpn_ind => NULL,
1423 x_elec_mpn_ind => NULL,
1424 x_borr_sign_ind => NULL,
1425 x_stud_sign_ind => NULL,
1426 x_borr_credit_auth_code => NULL,
1427 x_relationship_cd => NULL, -- FA 122 Added this column
1428 x_interest_rebate_percent_num => NVL(dl_setup_rec.int_rebate,0),
1429 x_cps_trans_num => gn_transaction_num,
1430 x_atd_entity_id_txt => gv_atd_entity_id_txt,
1431 x_rep_entity_id_txt => gv_rep_entity_id_txt,
1432 x_crdt_decision_status => NULL,
1433 x_note_message => NULL,
1434 x_book_loan_amt => NULL,
1435 x_book_loan_amt_date => NULL,
1436 x_pymt_servicer_amt => NULL,
1437 x_pymt_servicer_date => NULL,
1438 x_requested_loan_amt => g_accepted_amt,
1439 x_eft_authorization_code => NULL,
1440 x_external_loan_id_txt => NULL,
1441 x_deferment_request_code => NULL,
1442 x_actual_record_type_code => NULL,
1443 x_reinstatement_amt => NULL,
1444 x_school_use_txt => NULL,
1445 x_lender_use_txt => NULL,
1446 x_guarantor_use_txt => NULL,
1447 x_fls_approved_amt => NULL,
1448 x_flu_approved_amt => NULL,
1449 x_flp_approved_amt => NULL,
1450 x_alt_approved_amt => NULL,
1451 x_loan_app_form_code => NULL,
1452 x_override_grade_level_code => NULL,
1453 x_b_alien_reg_num_txt => NULL,
1454 x_esign_src_typ_cd => NULL,
1455 x_acad_begin_date => lv_acad_begin_date,
1456 x_acad_end_date => lv_acad_end_date);
1457
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1461 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_DL_RECORDS');
1462 fnd_file.put_line(fnd_file.log,SQLERRM);
1463 igs_ge_msg_stack.add;
1464 app_exception.raise_exception;
1465
1466 END insert_lor_dl_records;
1467
1468
1469 /* Procedure to insert into IGF_SL_LOR for CL Loans*/
1470
1471 PROCEDURE insert_lor_cl_records(
1472 p_cal_type IN igs_ca_inst.cal_type%TYPE,
1473 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1474 p_loan_id IN igf_sl_loans.loan_id%TYPE,
1475 p_comp_date IN igf_sl_lor.anticip_compl_date%TYPE,
1476 p_grd_cl IN igf_sl_lor.grade_level_code%TYPE
1477 )AS
1478
1479 /***************************************************************
1480 Created By : mesriniv
1481 Date Created By : 2000/11/13
1482 Purpose : To Insert Records into IGF_SL_LOR for CL Loans
1483 Known Limitations,Enhancements or Remarks
1484
1485 Change History :
1486 Who When What
1487 azmohamm 03-AUG-2006 For GPLUSFL, the new loan type would be 'GB'
1488 and the Federal application form Code would be 'G'.
1489 The borrower id is same as the student id.
1490 So while inserting into igf_sl_lor_all table person_id has to be the student's party id.
1491 rajagupt 10-Apr-2006 Bug# 5006583, FA161 CL4.
1492 cps_trans_num is inserted. deferment_request_code
1493 borw_interest_ind, borw_outstd_loan_code, p_default_status, s_default_status, stud_sign_ind
1494 are inserted with null and loan_app_form_code = 'Q' for FLP loans
1495 bvisvana 06-Jul-2005 Bug # 4473160. LOAN_NUMBER token set in the message
1496 pssahni 30-Dec-2004 Application form code must be populated
1497 bkkumar 02-04-04 FACR116 - Added the parameter to the pick_setup routine to check
1498 in teh case of 'ALT' Loans. Added validation to check if the alt_rel_code
1499 IS null or not.
1500 venagara 18-May-2001 Bug# 1769051
1501 For FLS,FLU, default cl_loan_type.
1502 bkkumar 29-sep-2003 FA 122 Loans Enhancements
1503 Derivation of the fields REQ_SERIAL_LOAN_CODE
1504 PNOTE_DELIVERY_CODE
1505 BORW_INTEREST_IND
1506 BORW_LENDER_ID
1507 DUNS_BORW_LENDER_ID -- OBSOLETED THIS FIELD
1508 GUARANTOR_ID
1509 DUNS_GUARNT_ID -- OBSOLETED THIS FIELD
1510 PRC_TYPE_CODE
1511 LENDER_ID
1512 DUNS_LENDER_ID -- OBSOLETED THIS FIELD
1513 LEND_NON_ED_BRC_ID
1514 RECIPIENT_ID
1515 RECIPIENT_TYPE
1516 DUNS_RECIP_ID -- OBSOLETED THIS FIELD
1517 RECIP_NON_ED_BRC_ID is now done from
1518 the FFELP loan Setup.
1519 bkkumar 06-oct-2003 Bug 3104228 Impact of adding the relationship_cd
1520 in igf_sl_lor_all table and obsoleting
1521 BORW_LENDER_ID,
1522 DUNS_BORW_LENDER_ID,
1523 GUARANTOR_ID,
1524 DUNS_GUARNT_ID,
1525 LENDER_ID, DUNS_LENDER_ID
1526 LEND_NON_ED_BRC_ID, RECIPIENT_ID
1527 RECIPIENT_TYPE,DUNS_RECIP_ID
1528 RECIP_NON_ED_BRC_ID columns
1529 Also the relationship code is now picked up from the
1530 pick_Setup routine.
1531 ***************************************************************/
1532
1533
1534 ln_origination_id igf_sl_lor.origination_id%TYPE;
1535 lv_cl_loan_type igf_sl_lor.cl_loan_type%TYPE;
1536 ld_sch_cert_date igf_sl_lor.sch_cert_date%TYPE;
1537 lv_s_default_status igf_sl_lor.s_default_status%TYPE;
1538 lv_grade_level_code igf_sl_lor.grade_level_code%TYPE;
1539 lv_enrollment_code igf_sl_lor.enrollment_code%TYPE;
1540 lv_anticipated_compl_date DATE;
1541 lv_row_id ROWID;
1542
1543 ln_cur_loan_id igf_sl_loans.loan_id%TYPE;
1544 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
1545 l_party_id igf_sl_cl_setup.party_id%TYPE;
1546 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
1547 l_loan_status VARCHAR2(30);
1548
1549
1550 -- cursor to get the base id from the loan id
1551 CURSOR c_get_base_id (
1552 cp_loan_id igf_sl_loans.loan_id%TYPE
1553 ) IS
1554 SELECT awd.base_id, awd.award_id
1555 FROM igf_sl_loans_all loans,
1556 igf_aw_award_all awd
1557 WHERE loans.loan_id = cp_loan_id
1558 AND loans.award_id = awd.award_id;
1559
1560 l_get_base_id c_get_base_id%ROWTYPE;
1561
1562 -- cursor to get the loan details from the current FFELP set up used by the student
1563 CURSOR c_clsetup (
1564 cp_cal_type igf_sl_cl_setup_v.ci_cal_type%TYPE,
1565 cp_seq_number igf_sl_cl_setup_v.ci_sequence_number%TYPE,
1566 cp_rel_code igf_sl_cl_setup_v.relationship_cd%TYPE,
1567 cp_party_id igf_sl_cl_setup.party_id%TYPE
1568 ) IS
1569 SELECT *
1570 FROM igf_sl_cl_setup_all
1571 WHERE ci_cal_type = cp_cal_type
1572 AND ci_sequence_number = cp_seq_number
1573 AND relationship_cd = cp_rel_code
1574 AND NVL(party_id,-1000) = NVL(cp_party_id,-1000);
1575
1576 c_clsetup_rec c_clsetup%ROWTYPE;
1577
1578 CURSOR c_disb_count (cp_award_id igf_aw_awd_disb_all.award_id%TYPE) IS
1579 SELECT COUNT(*)
1580 FROM igf_aw_awd_disb_all
1581 WHERE award_id = cp_award_id;
1582
1583 l_disb_count NUMBER;
1584
1585 CURSOR c_tbh_loans_cur (cp_loanid NUMBER) IS
1586 SELECT * FROM igf_sl_loans
1587 WHERE loan_id = cp_loanid;
1588
1589 l_tbh_loans_rec c_tbh_loans_cur%ROWTYPE;
1590
1591 l_appl_form_code igf_sl_lor_all.loan_app_form_code%TYPE;
1592 l_borw_interest_ind igf_sl_lor.borw_interest_ind%TYPE;
1593 l_borw_outstd_loan_code igf_sl_lor.borw_outstd_loan_code%TYPE;
1594 l_parent_person_id NUMBER;
1595 l_student_person_id NUMBER;
1596 l_cosigner_person_id NUMBER;
1597
1598 --Bug# 5006583
1599 CURSOR cur_fa_mapping ( p_citizenship_status igf_sl_pe_citi_map.pe_citi_stat_code%TYPE,
1600 p_cal_type igf_sl_pe_citi_map.ci_cal_type%TYPE,
1601 p_sequence_number igf_sl_pe_citi_map.ci_sequence_number%TYPE ) IS
1602 SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
1603 WHERE pe_citi_stat_code = p_citizenship_status
1604 AND ci_sequence_number =p_sequence_number
1605 AND ci_cal_type = p_cal_type;
1606
1607 --Bug# 5006583
1608 CURSOR cur_borrower_id (cp_loanid NUMBER) IS
1609 SELECT p_person_id FROM igf_sl_lor_all
1610 WHERE loan_id = cp_loanid;
1611
1612 --Bug# 5006583
1613 CURSOR citizenship_dtl_cur (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE) IS
1614 SELECT
1615 pct.restatus_code
1616 FROM igs_pe_eit_restatus_v pct
1617 WHERE pct.person_id = cp_person_id
1618 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
1619
1620 l_cur_borrower_rec cur_borrower_id%ROWTYPE;
1621 l_cur_fa_rec cur_fa_mapping%ROWTYPE;
1622 citizenship_dtl_rec citizenship_dtl_cur%ROWTYPE;
1623 alien_dtl_cur igf_sl_gen.person_dtl_cur;
1624 alien_dtl_rec igf_sl_gen.person_dtl_rec;
1625 l_b_alien_reg_num_txt igf_sl_lor_all.b_alien_reg_num_txt%TYPE;
1626 BEGIN
1627
1628 -- Origination_Id will be populated from Sequence.
1629 -- Loan_Id is got from the Current value of sequence returned.
1630 -- Rec_type_ind value is A
1631 -- Defaulting loan status
1632
1633 IF award_rec.fed_fund_code = 'FLU' THEN
1634 lv_cl_loan_type := 'SU';
1635 ELSIF award_rec.fed_fund_code = 'FLS' THEN
1636 lv_cl_loan_type := 'SF';
1637 ELSIF award_rec.fed_fund_code = 'FLP' THEN
1638 lv_cl_loan_type := 'PL';
1639 ELSIF award_rec.fed_fund_code = 'ALT' THEN
1640 lv_cl_loan_type := 'AL';
1641 ELSIF award_rec.fed_fund_code = 'GPLUSFL' THEN
1642 lv_cl_loan_type := 'GB';
1643 END IF;
1644 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1645 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The fed_fund_code being used is :' || award_rec.fed_fund_code );
1646 END IF;
1647
1648 -- l_get_base_id := NULL;
1649 OPEN c_get_base_id(p_loan_id);
1650 FETCH c_get_base_id INTO l_get_base_id;
1651 CLOSE c_get_base_id;
1652 -- FACR116 Grant Loan Changes
1653 IF lv_cl_loan_type = 'AL' THEN
1654 IF award_rec.alt_loan_code IS NULL THEN
1655 fnd_message.set_name('IGF','IGF_AW_NO_ALT_LOAN_CODE');
1656 fnd_file.put_line(fnd_file.log,fnd_message.get);
1657 RAISE SKIP_THIS_RECORD;
1658 END IF;
1659 END IF;
1660
1661 l_rel_code := NULL;
1662 l_party_id := NULL;
1663
1664 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1665 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The values passed to pick_setup base_id :' || l_get_base_id.base_id );
1666 END IF;
1667 -- this will return the current active FFELP setup for this person
1668 igf_sl_award.pick_setup(l_get_base_id.base_id,p_cal_type,p_sequence_number,l_rel_code,l_person_id,l_party_id,award_rec.alt_rel_code);
1669
1670 -- log the messages in the logging framework
1671 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1672 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','The values returned from pick setup RelCode :' || l_rel_code || ' The Party ID :' || l_party_id);
1673 END IF;
1674
1675 -- if both the rel_code and party_id is null then raise the exception SKIP_THIS_RECORD
1676 IF l_rel_code IS NULL AND l_party_id IS NULL THEN
1677 fnd_message.set_name('IGF','IGF_SL_NO_CL_SETUP');
1678 fnd_file.put_line(fnd_file.log,fnd_message.get);
1679 RAISE SKIP_THIS_RECORD;
1680 END IF;
1681
1682 -- get the deatils from the igf_sl_cl_setup table for this relationship code and party id
1683 -- c_clsetup_rec := NULL;
1684 OPEN c_clsetup(p_cal_type,p_sequence_number,l_rel_code,l_party_id);
1685 FETCH c_clsetup INTO c_clsetup_rec;
1686 CLOSE c_clsetup;
1687
1688
1689 -- Get the Anticipated Completion Date of the Student.
1690 lv_anticipated_compl_date := p_comp_date;
1691
1692
1693 l_loan_status := 'G';
1694 gn_award_id := award_rec.award_id;
1695 OPEN c_disb_count(gn_award_id);
1696 FETCH c_disb_count INTO l_disb_count;
1697 CLOSE c_disb_count;
1698
1699 OPEN c_tbh_loans_cur(p_loan_id);
1700 FETCH c_tbh_loans_cur INTO l_tbh_loans_rec;
1701 IF c_tbh_loans_cur%NOTFOUND THEN
1702 CLOSE c_tbh_loans_cur;
1703 RAISE NO_DATA_FOUND;
1704 END IF;
1705 CLOSE c_tbh_loans_cur;
1706
1707 IF (l_disb_count > 4 ) and (c_clsetup_rec.cl_version = 'RELEASE-4') THEN
1708 fnd_message.set_name('IGF','IGF_SL_CL4_NUM_OF_DISB_LOAN');
1709 fnd_message.set_token('LOAN_NUMBER',l_tbh_loans_rec.loan_number); -- bvisvana Bug # 4473160
1710 fnd_file.put_line(fnd_file.log,fnd_message.get);
1711 l_loan_status := 'N';
1712 END IF;
1713
1714 IF award_rec.fed_fund_code IN ('FLP', 'ALT') THEN -- Check if that is plus/alt loans and get parent id for the same.
1715 get_borrower_parent_id (
1716 p_loan_id => p_loan_id,
1717 p_parent_person_id => l_parent_person_id,
1718 p_student_person_id => l_student_person_id
1719 );
1720 END IF; -- END Check if that is plus/alt loans and get parent id for the same.
1721
1722 igf_sl_loans_pkg.update_row (
1723 x_mode => 'R',
1724 x_rowid => l_tbh_loans_rec.row_id,
1725 x_loan_id => l_tbh_loans_rec.loan_id,
1726 x_award_id => l_tbh_loans_rec.award_id,
1727 x_seq_num => l_tbh_loans_rec.seq_num,
1728 x_loan_number => l_tbh_loans_rec.loan_number,
1729 x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date,
1730 x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date,
1731 x_loan_status => l_loan_status,
1732 x_loan_status_date => l_tbh_loans_rec.loan_status_date,
1733 x_loan_chg_status => l_tbh_loans_rec.loan_chg_status,
1734 x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
1735 x_active => l_tbh_loans_rec.active,
1736 x_active_date => l_tbh_loans_rec.active_date ,
1737 x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
1738 x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
1739 x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt
1740 );
1741
1742 -- With these values get loan details insert data into igf_sl_lor table
1743 lv_row_id := NULL;
1744 ln_origination_id := NULL;
1745
1746 -- Bug 4087865 Populate loan application form code
1747 l_appl_form_code := NULL;
1748 g_p_default_status := 'N';
1749 l_borw_interest_ind := c_clsetup_rec.borw_interest_ind;
1750 l_borw_outstd_loan_code := 'N';
1751
1752 IF award_rec.fed_fund_code = 'FLU' THEN
1753 l_appl_form_code := 'M';
1754 ELSIF award_rec.fed_fund_code = 'FLS' THEN
1755 l_appl_form_code := 'M';
1756 ELSIF award_rec.fed_fund_code = 'FLP' THEN
1757 l_appl_form_code := 'Q';
1758 l_borw_interest_ind := NULL;
1759 l_borw_outstd_loan_code := NULL;
1760 g_p_default_status := NULL;
1761 g_s_default_status := NULL;
1762 ELSIF award_rec.fed_fund_code = 'ALT' THEN
1763 l_appl_form_code := NULL;
1764 l_cosigner_person_id := l_parent_person_id;
1765 l_parent_person_id := award_rec.person_id; -- FA 157 - For ALT loans, student is default borrower.
1766 g_p_default_status := g_s_default_status; -- bvisvana 4127532 - p_default_stauts = s_default_status
1767 ELSIF award_rec.fed_fund_code = 'GPLUSFL' THEN
1768 l_appl_form_code := 'G';
1769 l_parent_person_id := award_rec.person_id;
1770 l_borw_interest_ind := NULL;
1771 l_borw_outstd_loan_code := NULL;
1772 g_p_default_status := NULL;
1773 g_s_default_status := NULL;
1774 END IF;
1775
1776 -- l_parent_person_id holds the Borrower..(for ALT l_parent_person_id = student_id itself.)
1777
1778 --Bug# 5006583, FA161 CL4 -- Borrower Alien Regestration Number for FLP/ALT loans
1779 l_b_alien_reg_num_txt := NULL;
1780 IF l_parent_person_id IS NOT NULL THEN
1781 OPEN citizenship_dtl_cur (l_parent_person_id); --get borrower citizenship status
1782 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
1783 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1784 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','Borrower citizenship status is ' || citizenship_dtl_rec.restatus_code );
1785 END IF;
1786 IF citizenship_dtl_cur%FOUND THEN
1787 --get FA Citizenship Status Code
1788 OPEN cur_fa_mapping (p_citizenship_status => citizenship_dtl_rec.restatus_code,
1789 p_cal_type => p_cal_type,
1790 p_sequence_number => p_sequence_number);
1791 FETCH cur_fa_mapping INTO l_cur_fa_rec;
1792 CLOSE cur_fa_mapping;
1793 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1794 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','Borrower Alein reg number is ' || l_cur_fa_rec.fa_citi_stat_code);
1795 END IF;
1796
1797 IF NVL(l_cur_fa_rec.fa_citi_stat_code,'*') = '2' THEN
1798 igf_sl_gen.get_person_details(l_parent_person_id, alien_dtl_cur); --get Alein reg number
1799 FETCH alien_dtl_cur INTO alien_dtl_rec;
1800 IF alien_dtl_cur%FOUND THEN
1801 l_b_alien_reg_num_txt := alien_dtl_rec.p_alien_reg_num;
1802 END IF;
1803 CLOSE alien_dtl_cur;
1804 END IF;
1805 END IF; -- End of "IF citizenship_dtl_cur%FOUND THEN"
1806 CLOSE citizenship_dtl_cur;
1807 END IF; --End of "IF l_parent_person_id IS NOT NULL THEN"
1808
1809
1810 igf_sl_lor_pkg.insert_row (
1811 x_mode => 'R',
1812 x_rowid => lv_row_id,
1813 x_origination_id => ln_origination_id,
1814 x_loan_id => p_loan_id,
1815 x_sch_cert_date => TRUNC(award_rec.creation_date),
1816 x_orig_status_flag => NULL,
1817 x_orig_batch_id => NULL,
1818 x_orig_batch_date => NULL,
1819 x_chg_batch_id => NULL,
1820 x_orig_ack_date => NULL,
1821 x_credit_override => NULL,
1822 x_credit_decision_date => NULL,
1823 x_req_serial_loan_code => c_clsetup_rec.req_serial_loan_code,
1824 x_act_serial_loan_code => NULL,
1825 x_pnote_delivery_code => c_clsetup_rec.pnote_delivery_code,
1826 x_pnote_status => NULL,
1827 x_pnote_status_date => NULL,
1828 x_pnote_id => NULL,
1829 x_pnote_print_ind => NULL,
1830 x_pnote_accept_amt => NULL,
1831 x_pnote_accept_date => NULL,
1832 x_unsub_elig_for_heal => NULL,
1833 x_disclosure_print_ind => NULL,
1834 x_orig_fee_perct => NULL,
1835 x_borw_confirm_ind => NULL,
1836 x_borw_interest_ind => l_borw_interest_ind,
1837 x_borw_outstd_loan_code => l_borw_outstd_loan_code,
1838 x_unsub_elig_for_depnt => gv_unsub_elig_for_depnt,
1839 x_guarantee_amt => NULL,
1840 x_guarantee_date => NULL,
1841 x_guarnt_amt_redn_code => NULL,
1842 x_guarnt_status_code => NULL,
1843 x_guarnt_status_date => NULL,
1844 x_lend_apprv_denied_code => NULL,
1845 x_lend_apprv_denied_date => NULL,
1846 x_lend_status_code => NULL,
1847 x_lend_status_date => NULL,
1848 x_guarnt_adj_ind => NULL,
1849 x_grade_level_code => p_grd_cl,
1850 x_enrollment_code => g_enrollment_code,
1851 x_anticip_compl_date => lv_anticipated_compl_date,
1852 x_borw_lender_id => NULL,
1853 x_duns_borw_lender_id => NULL,
1854 x_guarantor_id => NULL,
1855 x_duns_guarnt_id => NULL,
1856 x_prc_type_code => c_clsetup_rec.prc_type_code,
1857 x_cl_seq_number => NULL,
1858 x_last_resort_lender => NULL,
1859 x_lender_id => NULL,
1860 x_duns_lender_id => NULL,
1861 x_lend_non_ed_brc_id => NULL,
1862 x_recipient_id => NULL,
1863 x_recipient_type => NULL,
1864 x_duns_recip_id => NULL,
1865 x_recip_non_ed_brc_id => NULL,
1866 x_rec_type_ind => 'A',
1867 x_cl_loan_type => lv_cl_loan_type,
1868 x_cl_rec_status => NULL,
1869 x_cl_rec_status_last_update => NULL,
1870 x_alt_prog_type_code => award_rec.alt_loan_code, -- FACR116
1871 x_alt_appl_ver_code => NULL,
1872 x_mpn_confirm_code => NULL,
1873 x_resp_to_orig_code => NULL,
1874 x_appl_loan_phase_code => NULL,
1875 x_appl_loan_phase_code_chg => NULL,
1876 x_appl_send_error_codes => NULL,
1877 x_tot_outstd_stafford => NULL,
1878 x_tot_outstd_plus => NULL,
1879 x_alt_borw_tot_debt => NULL,
1880 x_act_interest_rate => NULL,
1881 x_service_type_code => NULL,
1882 x_rev_notice_of_guarnt => NULL,
1883 x_sch_refund_amt => NULL,
1884 x_sch_refund_date => NULL,
1885 x_uniq_layout_vend_code => NULL,
1886 x_uniq_layout_ident_code => NULL,
1887 x_p_person_id => l_parent_person_id, -- FA 157 -- derived single parent ID.
1888 x_p_ssn_chg_date => NULL,
1889 x_p_dob_chg_date => NULL,
1890 x_p_permt_addr_chg_date => NULL,
1891 x_p_default_status => g_p_default_status, -- Bug 4127532 -derived borr default status from student default status
1892 x_p_signature_code => NULL,
1893 x_p_signature_date => NULL,
1894 x_s_ssn_chg_date => NULL,
1895 x_s_dob_chg_date => NULL,
1896 x_s_permt_addr_chg_date => NULL,
1897 x_s_local_addr_chg_date => NULL,
1898 x_s_default_status => g_s_default_status,
1899 x_s_signature_code => NULL,
1900 x_pnote_batch_id => NULL,
1901 x_pnote_ack_date => NULL,
1902 x_pnote_mpn_ind => NULL,
1903 x_elec_mpn_ind => NULL,
1904 x_borr_sign_ind => NULL,
1905 x_stud_sign_ind => NULL,
1906 x_borr_credit_auth_code => NULL,
1907 x_relationship_cd => l_rel_code, -- FA 122 Added this column,
1908 x_interest_rebate_percent_num => NULL,
1909 x_cps_trans_num => gn_transaction_num, -- FA161
1910 x_atd_entity_id_txt => NULL,
1911 x_rep_entity_id_txt => NULL,
1912 x_crdt_decision_status => NULL,
1913 x_note_message => NULL,
1914 x_book_loan_amt => NULL,
1915 x_book_loan_amt_date => NULL,
1916 x_pymt_servicer_amt => NULL,
1917 x_pymt_servicer_date => NULL,
1918 x_requested_loan_amt => g_accepted_amt,
1919 x_eft_authorization_code => c_clsetup_rec.eft_authorization,
1920 x_external_loan_id_txt => NULL,
1921 x_deferment_request_code => NULL,
1922 x_actual_record_type_code => NULL,
1923 x_reinstatement_amt => NULL,
1924 x_school_use_txt => NULL,
1925 x_lender_use_txt => NULL,
1926 x_guarantor_use_txt => NULL,
1927 x_fls_approved_amt => NULL,
1928 x_flu_approved_amt => NULL,
1929 x_flp_approved_amt => NULL,
1930 x_alt_approved_amt => NULL,
1931 x_loan_app_form_code => l_appl_form_code,
1932 x_override_grade_level_code => NULL,
1933 x_b_alien_reg_num_txt => l_b_alien_reg_num_txt,
1934 x_esign_src_typ_cd => NULL,
1935 x_acad_begin_date => NULL,
1936 x_acad_end_date => NULL
1937 );
1938
1939 IF award_rec.fed_fund_code = 'ALT' THEN
1940 populate_cosigner_data(p_loan_id, l_cosigner_person_id);
1941 END IF;
1942
1943 --
1944 -- update award if fees information has changed
1945 --
1946 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1947 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','before racalc fees');
1948 END IF;
1949
1950 igf_sl_award.recalc_fees(
1951 p_award_id => l_get_base_id.award_id,
1952 p_base_id => l_get_base_id.base_id,
1953 p_rel_code => l_rel_code,
1954 p_sequence_number => p_sequence_number,
1955 p_cal_type => p_cal_type);
1956
1957 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1958 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.debug','after racalc fees');
1959 END IF;
1960
1961
1962 EXCEPTION
1963 WHEN SKIP_THIS_RECORD THEN
1964 RAISE SKIP_THIS_RECORD;
1965 WHEN NO_DATA_FOUND THEN
1966 fnd_message.set_name('IGF','IGF_GE_NO_DATA_FOUND');
1967 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_CL_RECORDS');
1968 igs_ge_msg_stack.add;
1969 app_exception.raise_exception;
1970 WHEN OTHERS THEN
1971 IF FND_LOG.LEVEL_EXCEPTION >= fnd_log.g_current_runtime_level THEN
1972 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_lar_creation.insert_lor_cl_records.exception',SQLERRM);
1973 END IF;
1974 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1975 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.INSERT_LOR_CL_RECORDS');
1976 fnd_file.put_line(fnd_file.log,SQLERRM);
1977 igs_ge_msg_stack.add;
1978 app_exception.raise_exception;
1979
1980 END insert_lor_cl_records;
1981
1982
1983 PROCEDURE update_loan_rec(p_award_id number,
1984 p_fund_id number,
1985 p_base_id number,
1986 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
1987 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
1988 IS
1989
1990 CURSOR cur_loan IS
1991 SELECT loan.*,
1992 awd.adplans_id,
1993 fa.person_id,
1994 fcat.fed_fund_code,
1995 awd.base_id,
1996 awd.award_status,
1997 awd.accepted_amt,
1998 fmast.ci_cal_type,
1999 fmast.ci_sequence_number
2000 FROM igf_sl_loans_all loan,
2001 igf_aw_award_all awd,
2002 igf_aw_fund_mast_all fmast,
2003 igf_aw_fund_cat_all fcat,
2004 igf_ap_fa_base_rec_all fa
2005 WHERE loan.award_id = awd.award_id
2006 AND awd.fund_id = fmast.fund_id
2007 AND fmast.fund_code = fcat.fund_code
2008 AND fmast.ci_cal_type = p_ci_cal_type
2009 AND fmast.ci_sequence_number = p_ci_sequence_number
2010 AND awd.base_id = NVL (p_base_id, awd.base_id)
2011 AND awd.fund_id = NVL (p_fund_id, awd.fund_id)
2012 AND awd.award_id = NVL (p_award_id, awd.award_id)
2013 AND fa.base_id = NVL (p_base_id, awd.base_id)
2014 AND fa.ci_cal_type = fmast.ci_cal_type
2015 AND fa.ci_sequence_number = fmast.ci_sequence_number;
2016
2017 lv_isir_present BOOLEAN ;
2018 loan_title VARCHAR2(1000);
2019
2020 CURSOR c_tbh_cur (cp_loanid NUMBER) IS
2021 SELECT * FROM igf_sl_lor
2022 WHERE loan_id = cp_loanid;
2023
2024 l_tbh_rec igf_sl_lor%ROWTYPE;
2025
2026 CURSOR c_tbh_loans_cur (cp_loanid NUMBER) IS
2027 SELECT * FROM igf_sl_loans
2028 WHERE loan_id = cp_loanid;
2029
2030 l_tbh_loans_rec c_tbh_loans_cur%ROWTYPE;
2031
2032 lb_update Boolean;
2033 l_parent_person_id NUMBER;
2034 l_student_person_id NUMBER;
2035 --Bug# 5006583
2036 CURSOR cur_fa_mapping ( p_citizenship_status igf_sl_pe_citi_map.pe_citi_stat_code%TYPE,
2037 p_cal_type igf_sl_pe_citi_map.ci_cal_type%TYPE,
2038 p_sequence_number igf_sl_pe_citi_map.ci_sequence_number%TYPE ) IS
2039 SELECT fa_citi_stat_code FROM igf_sl_pe_citi_map
2040 WHERE pe_citi_stat_code = p_citizenship_status
2041 AND ci_sequence_number =p_sequence_number
2042 AND ci_cal_type = p_cal_type;
2043
2044 --Bug# 5006583
2045 CURSOR cur_borrower_id (cp_loanid NUMBER) IS
2046 SELECT p_person_id FROM igf_sl_lor_all
2047 WHERE loan_id = cp_loanid;
2048
2049 --Bug# 5006583
2050 CURSOR citizenship_dtl_cur (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE) IS
2051 SELECT
2052 pct.restatus_code
2053 FROM igs_pe_eit_restatus_v pct
2054 WHERE pct.person_id = cp_person_id
2055 AND SYSDATE BETWEEN start_date AND NVL(end_date,SYSDATE);
2056
2057 l_cur_borrower_rec cur_borrower_id%ROWTYPE;
2058 l_cur_fa_rec cur_fa_mapping%ROWTYPE;
2059 citizenship_dtl_rec citizenship_dtl_cur%ROWTYPE;
2060 l_rel_code igf_sl_cl_setup.relationship_cd%TYPE;
2061 l_party_id igf_sl_cl_setup.party_id%TYPE;
2062 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
2063 alien_dtl_cur igf_sl_gen.person_dtl_cur;
2064 alien_dtl_rec igf_sl_gen.person_dtl_rec;
2065
2066 lv_acad_cal_type igs_ca_inst_all.cal_type%TYPE ;
2067 lv_acad_seq_num igs_ca_inst_all.sequence_number%TYPE;
2068 lv_message VARCHAR2(100);
2069
2070 --Bug# 5006583
2071 CURSOR get_parent_id_cur (cp_loan_id igf_sl_lor.loan_id%TYPE
2072 ) IS
2073 SELECT
2074 'X'
2075 FROM
2076 igs_pe_relationships_v v,
2077 igf_aw_award_all awd,
2078 igf_sl_loans_all loans,
2079 igf_ap_fa_base_rec base
2080 WHERE
2081 base.person_id = v.object_ID
2082 AND base.base_id = awd.base_id
2083 AND awd.award_id = loans.award_id
2084 AND loans.loan_id = cp_loan_id
2085 AND
2086 RELATIONSHIP_CODE = 'PARENT_OF'
2087 AND trunc(SYSDATE) BETWEEN v.start_date and NVL(v.end_date, SYSDATE);
2088
2089
2090 get_parent_id_rec get_parent_id_cur%ROWTYPE;
2091
2092 BEGIN
2093
2094 FOR cur_loan_rec IN cur_loan LOOP
2095
2096 BEGIN
2097
2098 SAVEPOINT IGFSL01B_SP2;
2099
2100 loan_title := igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PROCESSING') || ' ' || igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS', 'LOAN_NUMBER') || ' :' || cur_loan_rec.loan_number;
2101
2102 fnd_file.put_line(fnd_file.log, loan_title);
2103
2104 g_adplans_id := cur_loan_rec.adplans_id;
2105 g_person_id := cur_loan_rec.person_id;
2106 g_award_id := cur_loan_rec.award_id;
2107 g_s_default_status := NULL;
2108 g_grade_level_dl := NULL;
2109 g_enrollment_code := NULL;
2110 g_anticip_compl_date := NULL;
2111 g_grade_level_cl := NULL;
2112 lv_isir_present := TRUE;
2113 g_log_start_flag := FALSE;
2114 gn_transaction_num := NULL;
2115 gv_atd_entity_id_txt := NULL;
2116 gv_rep_entity_id_txt := NULL;
2117 gv_message := NULL;
2118 gv_return_status := NULL;
2119
2120 --derive depe status, entity id, for cur_loan_rec.base_id
2121
2122 get_fa_base_details(cur_loan_rec.base_id, g_s_default_status, g_grade_level_dl,g_grade_level_cl,
2123 g_enrollment_code, lv_isir_present, g_anticip_compl_date,gn_transaction_num,gv_unsub_elig_for_depnt);
2124
2125 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2126 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'after call to get_fa_base_details');
2127 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_s_default_status= ' || g_s_default_status);
2128 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_grade_level_dl= ' || g_grade_level_dl);
2129 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_grade_level_cl= ' || g_grade_level_cl);
2130 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_enrollment_code= ' || g_enrollment_code);
2131 --fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'lv_isir_present= ' || lv_isir_present);
2132 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'g_anticip_compl_date= ' || g_anticip_compl_date);
2133 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'gn_transaction_num= ' || gn_transaction_num);
2134 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'gv_unsub_elig_for_depnt= ' || gv_unsub_elig_for_depnt);
2135 END IF;
2136
2137 OPEN c_tbh_cur(cur_loan_rec.loan_id);
2138 FETCH c_tbh_cur INTO l_tbh_rec;
2139 IF c_tbh_cur%NOTFOUND THEN
2140 CLOSE c_tbh_cur;
2141 RAISE NO_DATA_FOUND;
2142 END IF;
2143 CLOSE c_tbh_cur;
2144
2145 OPEN c_tbh_loans_cur(cur_loan_rec.loan_id);
2146 FETCH c_tbh_loans_cur INTO l_tbh_loans_rec;
2147 IF c_tbh_loans_cur%NOTFOUND THEN
2148 CLOSE c_tbh_loans_cur;
2149 RAISE NO_DATA_FOUND;
2150 END IF;
2151 CLOSE c_tbh_loans_cur;
2152
2153 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2154 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'fed fund code is ' ||cur_loan_rec.fed_fund_code);
2155 END IF;
2156
2157 IF cur_loan_rec.fed_fund_code in ('FLP','FLS','FLU','ALT','GPLUSFL') THEN
2158 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2159 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'inside FFELP');
2160 END IF;
2161
2162 --Bug# 5006583
2163 IF cur_loan_rec.loan_status = 'S' OR (cur_loan_rec.loan_chg_status = 'S') THEN
2164 lb_update := FALSE;
2165 fnd_message.set_name('IGF','IGF_SL_LOAN_UPD_FAIL_SENT');
2166 fnd_file.put_line(fnd_file.log,fnd_message.get);
2167 RAISE SKIP_THIS_RECORD;
2168
2169 ELSIF (cur_loan_rec.loan_status IN ('C','T')) OR (cur_loan_rec.loan_chg_status IN ('C','T')) THEN
2170 lb_update := FALSE;
2171 -- print updation failed mesg
2172 fnd_message.set_name('IGF','IGF_SL_LOAN_UPD_FAIL_CANCEL');
2173 fnd_file.put_line(fnd_file.log,fnd_message.get);
2174
2175 RAISE SKIP_THIS_RECORD;
2176 ELSIF (cur_loan_rec.award_status IN ('CANCELLED')) THEN
2177 lb_update := FALSE;
2178 fnd_message.set_name('IGF','IGF_SL_CL_AW_CANC_SKIP_UPD'); -- print updation failed mesg Since Award is cancelled.
2179 fnd_file.put_line(fnd_file.log, fnd_message.get);
2180
2181 RAISE SKIP_THIS_RECORD;
2182 ELSE
2183 lb_update := TRUE;
2184
2185 -- Following FFELP fields need to be updated in LOR table down the line
2186 -- Dependency Status - Update
2187 -- Grade Level - Update.
2188 -- Enrollment Status - Update
2189
2190 l_rel_code := NULL;
2191 l_party_id := NULL;
2192 l_person_id := NULL;
2193 IF cur_loan_rec.fed_fund_code = 'FLP' THEN
2194 l_tbh_rec.loan_app_form_code :='Q';
2195 l_tbh_rec.deferment_request_code :=NULL;
2196 l_tbh_rec.borw_interest_ind :=NULL;
2197 l_tbh_rec.borw_outstd_loan_code :=NULL;
2198 l_tbh_rec.s_default_status :=NULL;
2199 l_tbh_rec.p_default_status :=NULL;
2200 l_tbh_rec.stud_sign_ind :=NULL;
2201 l_tbh_rec.s_signature_code :=NULL;
2202 ELSIF cur_loan_rec.fed_fund_code = 'GPLUSFL' THEN
2203 l_tbh_rec.loan_app_form_code := 'G';
2204 l_tbh_rec.deferment_request_code :=NULL;
2205 l_tbh_rec.borw_interest_ind :=NULL;
2206 l_tbh_rec.borw_outstd_loan_code :=NULL;
2207 l_tbh_rec.s_default_status :=NULL;
2208 l_tbh_rec.p_default_status :=NULL;
2209 l_tbh_rec.stud_sign_ind :=NULL;
2210 l_tbh_rec.s_signature_code :=NULL;
2211 ELSE
2212 l_tbh_rec.s_default_status := g_s_default_status;
2213 END IF;
2214 igf_sl_award.pick_setup(cur_loan_rec.base_id,p_ci_cal_type,p_ci_sequence_number,l_rel_code,l_person_id,l_party_id,award_rec.alt_rel_code);
2215
2216
2217 IF l_tbh_rec.override_grade_level_code IS NULL THEN
2218 l_tbh_rec.grade_level_code := g_grade_level_cl;
2219 ELSE
2220 fnd_message.set_name('IGF','IGF_SL_CL_GRD_LEVEL_INVALID');
2221 fnd_file.put_line(fnd_file.log, fnd_message.get);
2222 END IF;
2223 -- l_tbh_rec.enrollment_code := g_enrollment_code; -- updating enrollment_code with 'F'
2224 l_tbh_rec.cps_trans_num := gn_transaction_num;
2225 l_tbh_rec.relationship_cd := l_rel_code;
2226 l_tbh_rec.anticip_compl_date := g_anticip_compl_date;
2227 l_tbh_rec.sch_cert_date := TRUNC(sysdate);
2228
2229 IF NVL(l_tbh_rec.requested_loan_amt,2) < NVL(cur_loan_rec.accepted_amt,1) THEN
2230 l_tbh_rec.requested_loan_amt := cur_loan_rec.accepted_amt;
2231 END IF;
2232
2233 l_tbh_loans_rec.loan_per_begin_date := get_loan_start_dt(cur_loan_rec.award_id);
2234 l_tbh_loans_rec.loan_per_end_date := get_loan_end_dt(cur_loan_rec.award_id);
2235
2236
2237
2238 --Bug# 5006587, FA161 CL4 -- Borrower Alien Regestration Number for FLP/ALT loans
2239 IF cur_loan_rec.fed_fund_code IN ('FLP', 'ALT', 'GPLUSFL') THEN -- Check if that is plus/alt loans and get borrower id for the same if available.
2240 OPEN cur_borrower_id(cur_loan_rec.loan_id); --get borrower id
2241 FETCH cur_borrower_id INTO l_cur_borrower_rec;
2242 CLOSE cur_borrower_id;
2243 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2244 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower Id for the fed_fund_code is ' || l_cur_borrower_rec.p_person_id );
2245 END IF;
2246 l_tbh_rec.b_alien_reg_num_txt := NULL;
2247 IF l_cur_borrower_rec.p_person_id IS NOT NULL THEN
2248 OPEN citizenship_dtl_cur (l_cur_borrower_rec.p_person_id); --get borrower citizenship status
2249 FETCH citizenship_dtl_cur INTO citizenship_dtl_rec;
2250 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2251 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower citizenship status is ' || citizenship_dtl_rec.restatus_code );
2252 END IF;
2253 IF citizenship_dtl_cur%FOUND THEN
2254
2255 OPEN cur_fa_mapping (citizenship_dtl_rec.restatus_code, --get FA Citizenship Status Code
2256 p_ci_cal_type,
2257 p_ci_sequence_number);
2258 FETCH cur_fa_mapping INTO l_cur_fa_rec;
2259 CLOSE cur_fa_mapping;
2260 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2261 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Borrower Alein reg number is ' || l_cur_fa_rec.fa_citi_stat_code);
2262 END IF;
2263 IF NVL(l_cur_fa_rec.fa_citi_stat_code,'*') = '2' THEN
2264 igf_sl_gen.get_person_details(l_cur_borrower_rec.p_person_id, alien_dtl_cur); --get Alein reg number
2265 FETCH alien_dtl_cur INTO alien_dtl_rec;
2266 IF alien_dtl_cur%FOUND THEN
2267 l_tbh_rec.b_alien_reg_num_txt := alien_dtl_rec.p_alien_reg_num;
2268 END IF;
2269 CLOSE alien_dtl_cur;
2270 END IF;
2271
2272 END IF;
2273 CLOSE citizenship_dtl_cur;
2274 END IF;
2275 END IF;
2276 IF cur_loan_rec.loan_status = 'A' then
2277 l_tbh_loans_rec.loan_chg_status := 'G';
2278
2279 ELSE
2280 l_tbh_loans_rec.loan_status := 'G';
2281 END IF;
2282
2283
2284 igf_sl_loans_pkg.update_row (
2285 x_mode => 'R',
2286 x_rowid => l_tbh_loans_rec.row_id,
2287 x_loan_id => l_tbh_loans_rec.loan_id,
2288 x_award_id => l_tbh_loans_rec.award_id,
2289 x_seq_num => l_tbh_loans_rec.seq_num,
2290 x_loan_number => l_tbh_loans_rec.loan_number,
2291 x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date,
2292 x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date,
2293 x_loan_status => l_tbh_loans_rec.loan_status,
2294 x_loan_status_date => l_tbh_loans_rec.loan_status_date,
2295 x_loan_chg_status => l_tbh_loans_rec.loan_chg_status,
2296 x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
2297 x_active => l_tbh_loans_rec.active,
2298 x_active_date => l_tbh_loans_rec.active_date ,
2299 x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
2300 x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
2301 x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt,
2302 x_called_from => 'UPDATE_MODE' --Bug# 5006587
2303 );
2304 END IF;
2305 END IF;
2306
2307 IF cur_loan_rec.fed_fund_code in ('DLP','DLS','DLU') THEN
2308 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2309 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug', 'inside FFELP');
2310 END IF;
2311
2312 IF (cur_loan_rec.loan_status IN ('S','C','T')) OR (cur_loan_rec.loan_chg_status IN ('S','C','T')) THEN
2313 lb_update := FALSE;
2314
2315 -- print updation failed mesg
2316 fnd_message.set_name('IGF','IGF_SL_LOAN_UPD_FAIL');
2317 fnd_file.put_line(fnd_file.log,fnd_message.get);
2318
2319 RAISE SKIP_THIS_RECORD;
2320 ELSE
2321 lb_update := TRUE;
2322
2323 -- update loans table
2324 l_tbh_loans_rec.loan_per_begin_date := get_loan_start_dt(cur_loan_rec.award_id);
2325 l_tbh_loans_rec.loan_per_end_date := get_loan_end_dt(cur_loan_rec.award_id);
2326
2327 IF (l_tbh_loans_rec.loan_per_begin_date IS NULL OR l_tbh_loans_rec.loan_per_end_date IS NULL) THEN
2328 fnd_message.set_name('IGF','IGF_SL_ALL_CANCEL_DISB');
2329 fnd_message.set_token('VALUE',cur_loan_rec.award_id);
2330 fnd_file.put_line(fnd_file.log, fnd_message.get);
2331 RAISE SKIP_THIS_RECORD;
2332 END IF;
2333
2334 IF cur_loan_rec.loan_status = 'A' then
2335 -- Update Loan Change Status to "Ready to Send" and loan period
2336 l_tbh_loans_rec.loan_chg_status := 'G';
2337 END IF;
2338
2339 igf_sl_loans_pkg.update_row (
2340 x_mode => 'R',
2341 x_rowid => l_tbh_loans_rec.row_id,
2342 x_loan_id => l_tbh_loans_rec.loan_id,
2343 x_award_id => l_tbh_loans_rec.award_id,
2344 x_seq_num => l_tbh_loans_rec.seq_num,
2345 x_loan_number => l_tbh_loans_rec.loan_number,
2346 x_loan_per_begin_date => l_tbh_loans_rec.loan_per_begin_date, -- this is getting updated
2347 x_loan_per_end_date => l_tbh_loans_rec.loan_per_end_date, -- this is getting updated
2348 x_loan_status => l_tbh_loans_rec.loan_status,
2349 x_loan_status_date => l_tbh_loans_rec.loan_status_date,
2350 x_loan_chg_status => l_tbh_loans_rec.loan_chg_status, -- this is getting updated
2351 x_loan_chg_status_date => l_tbh_loans_rec.loan_chg_status_date,
2352 x_active => l_tbh_loans_rec.active,
2353 x_active_date => l_tbh_loans_rec.active_date ,
2354 x_borw_detrm_code => l_tbh_loans_rec.borw_detrm_code,
2355 x_legacy_record_flag => l_tbh_loans_rec.legacy_record_flag,
2356 x_external_loan_id_txt => l_tbh_loans_rec.external_loan_id_txt
2357 );
2358
2359 -- Following DL fields need to be updated in LOR table down the line
2360 -- Dependency Status - Update
2361 -- Grade Level - No Update, override is present.
2362 -- Entity ID - Update
2363 -- ISIR Transaction Number - Update
2364
2365 l_tbh_rec.s_default_status := g_s_default_status;
2366 l_tbh_rec.grade_level_code := g_grade_level_dl;
2367 l_tbh_rec.cps_trans_num := gn_transaction_num;
2368
2369 -- Get Entity IDs
2370 get_dl_entity_id(cur_loan_rec.base_id, cur_loan_rec.ci_cal_type, cur_loan_rec.ci_sequence_number,
2371 gv_atd_entity_id_txt,gv_rep_entity_id_txt,gv_message,gv_return_status);
2372 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2373 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_return_status :' || gv_return_status);
2374 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_message :' || gv_message);
2375 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_atd_entity_id_txt :' || gv_atd_entity_id_txt);
2376 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_dl_entity_id gv_rep_entity_id_txt :' || gv_rep_entity_id_txt);
2377 END IF;
2378 IF gv_return_status = 'E' THEN
2379 log_message(cur_loan_rec.award_id);
2380 fnd_file.put_line(fnd_file.log, gv_message);
2381 RAISE SKIP_THIS_RECORD;
2382 END IF;
2383 l_tbh_rec.atd_entity_id_txt := gv_atd_entity_id_txt;
2384 l_tbh_rec.rep_entity_id_txt := gv_rep_entity_id_txt;
2385
2386 END IF; -- to update or not
2387 -- FA 163 : If acad begin date and end date are null, then re populate
2388 IF l_tbh_rec.acad_begin_date IS NULL
2389 OR l_tbh_rec.acad_end_date IS NULL THEN
2390 igf_sl_dl_record.get_acad_cal_dtls (l_tbh_loans_rec.loan_number,
2391 lv_acad_cal_type,
2392 lv_acad_seq_num,
2393 l_tbh_rec.acad_begin_date,
2394 l_tbh_rec.acad_end_date,
2395 lv_message );
2396 END IF;
2397 END IF; -- fund is DL or not
2398
2399 --FA 161 CL4 build, Populate borrower information based on the borrower specified in existing LOR
2400 IF cur_loan_rec.fed_fund_code IN ('FLP', 'DLP', 'ALT') THEN
2401 get_borrower_parent_id (
2402 p_loan_id => l_tbh_rec.loan_id,
2403 p_parent_person_id => l_parent_person_id,
2404 p_student_person_id => l_student_person_id);
2405 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2406 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_borrower_parent_id l_parent_person_id :' || l_parent_person_id);
2407 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','The values returned from get_borrower_parent_id l_student_person_id :' || l_student_person_id);
2408 END IF;
2409 -- FA 161 CL4 Build , If person has more than one parent then check whether the person id present has 'Parent of' relationship
2410 IF l_parent_person_id IS NULL AND l_tbh_rec.p_person_id IS NOT NULL THEN
2411 OPEN get_parent_id_cur (l_tbh_rec.loan_id );
2412 FETCH get_parent_id_cur INTO get_parent_id_rec;
2413 IF get_parent_id_cur%FOUND THEN
2414 l_parent_person_id := l_tbh_rec.p_person_id;
2415 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2416 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.update_loan_rec.debug','Parent id :' || l_parent_person_id);
2417 END IF;
2418 END IF;
2419 CLOSE get_parent_id_cur;
2420 END IF;
2421 IF cur_loan_rec.fed_fund_code <> 'ALT' THEN
2422 l_tbh_rec.p_person_id := l_parent_person_id;
2423 ELSE
2424 l_tbh_rec.p_person_id := l_student_person_id;
2425 -- bvisvana Bug # 4127532
2426 l_tbh_rec.p_default_status := l_tbh_rec.s_default_status;
2427 END IF ;
2428 END IF;
2429 -- END Populate borrower information based on the borrower specified in existing LOR
2430
2431 IF lb_update THEN
2432 igf_sl_lor_pkg.update_row(
2433 x_mode => 'R',
2434 x_rowid => l_tbh_rec.row_id,
2435 x_origination_id => l_tbh_rec.origination_id,
2436 x_loan_id => l_tbh_rec.loan_id,
2437 x_sch_cert_date => l_tbh_rec.sch_cert_date,
2438 x_orig_status_flag => l_tbh_rec.orig_status_flag,
2439 x_orig_batch_id => l_tbh_rec.orig_batch_id,
2440 x_orig_batch_date => l_tbh_rec.orig_batch_date,
2441 x_chg_batch_id => l_tbh_rec.chg_batch_id,
2442 x_orig_ack_date => l_tbh_rec.orig_ack_date,
2443 x_credit_override => l_tbh_rec.credit_override,
2444 x_credit_decision_date => l_tbh_rec.credit_decision_date,
2445 x_req_serial_loan_code => l_tbh_rec.req_serial_loan_code,
2446 x_act_serial_loan_code => l_tbh_rec.act_serial_loan_code,
2447 x_pnote_delivery_code => l_tbh_rec.pnote_delivery_code,
2448 x_pnote_status => l_tbh_rec.pnote_status,
2449 x_pnote_status_date => l_tbh_rec.pnote_status_date,
2450 x_pnote_id => l_tbh_rec.pnote_id,
2451 x_pnote_print_ind => l_tbh_rec.pnote_print_ind,
2452 x_pnote_accept_amt => l_tbh_rec.pnote_accept_amt,
2453 x_pnote_accept_date => l_tbh_rec.pnote_accept_date,
2454 x_pnote_batch_id => l_tbh_rec.pnote_batch_id,
2455 x_pnote_ack_date => l_tbh_rec.pnote_ack_date,
2456 x_pnote_mpn_ind => l_tbh_rec.pnote_mpn_ind,
2457 x_unsub_elig_for_heal => l_tbh_rec.unsub_elig_for_heal,
2458 x_disclosure_print_ind => l_tbh_rec.disclosure_print_ind,
2459 x_orig_fee_perct => l_tbh_rec.orig_fee_perct,
2460 x_borw_confirm_ind => l_tbh_rec.borw_confirm_ind,
2461 x_borw_interest_ind => l_tbh_rec.borw_interest_ind,
2462 x_borw_outstd_loan_code => l_tbh_rec.borw_outstd_loan_code,
2463 x_unsub_elig_for_depnt => l_tbh_rec.unsub_elig_for_depnt,
2464 x_guarantee_amt => l_tbh_rec.guarantee_amt,
2465 x_guarantee_date => l_tbh_rec.guarantee_date,
2466 x_guarnt_amt_redn_code => l_tbh_rec.guarnt_amt_redn_code,
2467 x_guarnt_status_code => l_tbh_rec.guarnt_status_code,
2468 x_guarnt_status_date => l_tbh_rec.guarnt_status_date,
2469 x_lend_apprv_denied_code => l_tbh_rec.lend_apprv_denied_code,
2470 x_lend_apprv_denied_date => l_tbh_rec.lend_apprv_denied_date,
2471 x_lend_status_code => l_tbh_rec.lend_status_code,
2472 x_lend_status_date => l_tbh_rec.lend_status_date,
2473 x_guarnt_adj_ind => l_tbh_rec.guarnt_adj_ind,
2474 x_grade_level_code => l_tbh_rec.grade_level_code,
2475 x_enrollment_code => l_tbh_rec.enrollment_code,
2476 x_anticip_compl_date => l_tbh_rec.anticip_compl_date,
2477 x_borw_lender_id => l_tbh_rec.borw_lender_id,
2478 x_duns_borw_lender_id => l_tbh_rec.duns_borw_lender_id,
2479 x_guarantor_id => l_tbh_rec.guarantor_id,
2480 x_duns_guarnt_id => l_tbh_rec.duns_guarnt_id,
2481 x_prc_type_code => l_tbh_rec.prc_type_code,
2482 x_cl_seq_number => l_tbh_rec.cl_seq_number,
2483 x_last_resort_lender => l_tbh_rec.last_resort_lender,
2484 x_lender_id => l_tbh_rec.lender_id,
2485 x_duns_lender_id => l_tbh_rec.duns_lender_id,
2486 x_lend_non_ed_brc_id => l_tbh_rec.lend_non_ed_brc_id,
2487 x_recipient_id => l_tbh_rec.recipient_id,
2488 x_recipient_type => l_tbh_rec.recipient_type,
2489 x_duns_recip_id => l_tbh_rec.duns_recip_id,
2490 x_recip_non_ed_brc_id => l_tbh_rec.recip_non_ed_brc_id,
2491 x_rec_type_ind => l_tbh_rec.rec_type_ind,
2492 x_cl_loan_type => l_tbh_rec.cl_loan_type,
2493 x_cl_rec_status => l_tbh_rec.cl_rec_status,
2494 x_cl_rec_status_last_update => l_tbh_rec.cl_rec_status_last_update,
2495 x_alt_prog_type_code => l_tbh_rec.alt_prog_type_code,
2496 x_alt_appl_ver_code => l_tbh_rec.alt_appl_ver_code,
2497 x_mpn_confirm_code => l_tbh_rec.mpn_confirm_code,
2498 x_resp_to_orig_code => l_tbh_rec.resp_to_orig_code,
2499 x_appl_loan_phase_code => l_tbh_rec.appl_loan_phase_code,
2500 x_appl_loan_phase_code_chg => l_tbh_rec.appl_loan_phase_code_chg,
2501 x_appl_send_error_codes => l_tbh_rec.appl_send_error_codes,
2502 x_tot_outstd_stafford => l_tbh_rec.tot_outstd_stafford,
2503 x_tot_outstd_plus => l_tbh_rec.tot_outstd_plus,
2504 x_alt_borw_tot_debt => l_tbh_rec.alt_borw_tot_debt,
2505 x_act_interest_rate => l_tbh_rec.act_interest_rate,
2506 x_service_type_code => l_tbh_rec.service_type_code,
2507 x_rev_notice_of_guarnt => l_tbh_rec.rev_notice_of_guarnt,
2508 x_sch_refund_amt => l_tbh_rec.sch_refund_amt,
2509 x_sch_refund_date => l_tbh_rec.sch_refund_date,
2510 x_uniq_layout_vend_code => l_tbh_rec.uniq_layout_vend_code,
2511 x_uniq_layout_ident_code => l_tbh_rec.uniq_layout_ident_code,
2512 x_p_person_id => l_tbh_rec.p_person_id,
2513 x_p_ssn_chg_date => l_tbh_rec.p_ssn_chg_date,
2514 x_p_dob_chg_date => l_tbh_rec.p_dob_chg_date,
2515 x_p_permt_addr_chg_date => l_tbh_rec.p_permt_addr_chg_date,
2516 x_p_default_status => l_tbh_rec.p_default_status,
2517 x_p_signature_code => l_tbh_rec.p_signature_code,
2518 x_p_signature_date => l_tbh_rec.p_signature_date,
2519 x_s_ssn_chg_date => l_tbh_rec.s_ssn_chg_date,
2520 x_s_dob_chg_date => l_tbh_rec.s_dob_chg_date ,
2521 x_s_permt_addr_chg_date => l_tbh_rec.s_permt_addr_chg_date,
2522 x_s_local_addr_chg_date => l_tbh_rec.s_local_addr_chg_date,
2523 x_s_default_status => l_tbh_rec.s_default_status,
2524 x_s_signature_code => l_tbh_rec.s_signature_code,
2525 x_elec_mpn_ind => l_tbh_rec.elec_mpn_ind,
2526 x_borr_sign_ind => l_tbh_rec.borr_sign_ind,
2527 x_stud_sign_ind => l_tbh_rec.stud_sign_ind,
2528 x_borr_credit_auth_code => l_tbh_rec.borr_credit_auth_code,
2529 x_relationship_cd => l_tbh_rec.relationship_cd,
2530 x_interest_rebate_percent_num => l_tbh_rec.interest_rebate_percent_num,
2531 x_cps_trans_num => l_tbh_rec.cps_trans_num,
2532 x_atd_entity_id_txt => l_tbh_rec.atd_entity_id_txt ,
2533 x_rep_entity_id_txt => l_tbh_rec.rep_entity_id_txt,
2534 x_crdt_decision_status => l_tbh_rec.crdt_decision_status,
2535 x_note_message => l_tbh_rec.note_message,
2536 x_book_loan_amt => l_tbh_rec.book_loan_amt ,
2537 x_book_loan_amt_date => l_tbh_rec.book_loan_amt_date,
2538 x_pymt_servicer_amt => l_tbh_rec.pymt_servicer_amt,
2539 x_pymt_servicer_date => l_tbh_rec.pymt_servicer_date,
2540 x_requested_loan_amt => l_tbh_rec.requested_loan_amt,
2541 x_eft_authorization_code => l_tbh_rec.eft_authorization_code,
2542 x_external_loan_id_txt => l_tbh_rec.external_loan_id_txt,
2543 x_deferment_request_code => l_tbh_rec.deferment_request_code ,
2544 x_actual_record_type_code => l_tbh_rec.actual_record_type_code,
2545 x_reinstatement_amt => l_tbh_rec.reinstatement_amt,
2546 x_school_use_txt => l_tbh_rec.school_use_txt,
2547 x_lender_use_txt => l_tbh_rec.lender_use_txt,
2548 x_guarantor_use_txt => l_tbh_rec.guarantor_use_txt,
2549 x_fls_approved_amt => l_tbh_rec.fls_approved_amt,
2550 x_flu_approved_amt => l_tbh_rec.flu_approved_amt,
2551 x_flp_approved_amt => l_tbh_rec.flp_approved_amt,
2552 x_alt_approved_amt => l_tbh_rec.alt_approved_amt,
2553 x_loan_app_form_code => l_tbh_rec.loan_app_form_code,
2554 x_override_grade_level_code => l_tbh_rec.override_grade_level_code,
2555 x_called_from => 'UPDATE_MODE', --Bug# 5006587
2556 x_b_alien_reg_num_txt => l_tbh_rec.b_alien_reg_num_txt,
2557 x_esign_src_typ_cd => l_tbh_rec.esign_src_typ_cd,
2558 x_acad_begin_date => l_tbh_rec.acad_begin_date,
2559 x_acad_end_date => l_tbh_rec.acad_end_date);
2560
2561 IF cur_loan_rec.fed_fund_code = 'ALT' THEN
2562 populate_cosigner_data (p_loan_id => l_tbh_rec.loan_id,
2563 p_person_id => l_parent_person_id);
2564 END IF;
2565 END IF;
2566
2567 EXCEPTION
2568 WHEN SKIP_THIS_RECORD THEN
2569 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
2570 fnd_file.put_line(fnd_file.log, fnd_message.get);
2571 fnd_file.new_line(fnd_file.log,2);
2572 ROLLBACK TO IGFSL01B_SP2;
2573 WHEN OTHERS THEN
2574 -- Bug # 5079098 - This is to handle any exception thrown from other wrappers / proc / func / calls.
2575 -- Instead of throwing exception it needs to handled, skipped and continue with further loan records
2576 igs_ge_msg_stack.conc_exception_hndl;
2577 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
2578 fnd_file.put_line(fnd_file.log, fnd_message.get);
2579 fnd_file.new_line(fnd_file.log,1);
2580 ROLLBACK TO IGFSL01B_SP2;
2581 END;
2582
2583 END LOOP;
2584
2585 EXCEPTION
2586 WHEN OTHERS THEN
2587 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2588 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.UPDATE_LOAN_REC');
2589 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2590 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_lar_creation.update_loan_rec.exception','Exception:'||SQLERRM);
2591 END IF;
2592 igs_ge_msg_stack.add;
2593 app_exception.raise_exception;
2594
2595 END update_loan_rec;
2596 -- FA 134
2597
2598 PROCEDURE create_loan_records(p_award_id number,
2599 p_fund_id number,
2600 p_base_id number,
2601 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
2602 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2603 p_dl_setup BOOLEAN,
2604 p_cl_setup BOOLEAN) IS
2605 /*******************************************************************************
2606 Change History :
2607 Who When What
2608 museshad 20-Feb-2006 Bug 5031795 - SQL Repository issue.
2609 Modified cursor cur_count_fed_code for better
2610 performance.
2611 *******************************************************************************/
2612 CURSOR cur_awards_recs (p_award_id number, p_fund_id number, p_base_id number) IS
2613 SELECT awdv.*
2614 FROM igf_aw_award_v awdv
2615 WHERE awdv.sys_fund_type ='LOAN'
2616 AND awdv.ci_cal_type = p_ci_cal_type
2617 AND awdv.ci_sequence_number = p_ci_sequence_number
2618 AND awdv.award_id = NVL(p_award_id, awdv.award_id)
2619 AND awdv.fund_id = NVL(p_fund_id, awdv.fund_id)
2620 AND awdv.base_id = NVL(p_base_id, awdv.base_id) -- fa134
2621 AND awdv.award_status = 'ACCEPTED'
2622 AND awdv.fed_fund_code IN ('DLP','DLS','DLU','FLP','FLS','FLU','ALT','GPLUSFL')
2623 AND awdv.award_id NOT IN ( SELECT loans.award_id
2624 FROM igf_sl_loans_all loans )
2625 ORDER BY awdv.award_id;
2626
2627 lv_isir_present BOOLEAN ;
2628
2629 lb_print_dl BOOLEAN;
2630 lb_print_cl BOOLEAN;
2631
2632 ln_seq_number igf_sl_loans_all.seq_num%TYPE;
2633 l_msg_name fnd_new_messages.message_name%TYPE;
2634 l_aid NUMBER;
2635 l_loan_tab igf_aw_packng_subfns.std_loan_tab;
2636
2637 ld_loan_prd_start_dt DATE;
2638 ld_loan_prd_end_dt DATE;
2639 l_mapping VARCHAR2(1);
2640
2641 CURSOR cur_count_fed_code IS
2642 SELECT COUNT(*) + 1
2643 FROM
2644 igf_sl_loans_all loan,
2645 igf_aw_award_all awd,
2646 igf_aw_fund_mast_all fmast,
2647 igf_aw_fund_cat_all fcat,
2648 igf_ap_fa_base_rec_all fabase
2649 WHERE
2650 loan.award_id = awd.award_id AND
2651 awd.fund_id = fmast.fund_id AND
2652 awd.base_id = fabase.base_id AND
2653 fcat.fund_code = fmast.fund_code AND
2654 fabase.ci_cal_type = award_rec.ci_cal_type AND
2655 fabase.ci_sequence_number = award_rec.ci_sequence_number AND
2656 fabase.person_id = award_rec.student_id AND
2657 fcat.fed_fund_code = award_rec.fed_fund_code;
2658
2659 lv_loan_number igf_sl_loans_all.loan_number%TYPE;
2660
2661 lv_row_id ROWID;
2662 ln_loan_id igf_sl_loans_all.loan_id%TYPE;
2663 l_head VARCHAR2(1);
2664 l_found_loans VARCHAR2(1);
2665 l_fund_type VARCHAR2(10);
2666
2667 BEGIN
2668
2669 lb_print_dl := TRUE;
2670 lb_print_cl := TRUE;
2671 l_found_loans := 'N';
2672 l_head := 'N';
2673
2674 FOR award_rec_temp IN cur_awards_recs(p_award_id, p_fund_id, p_base_id) LOOP
2675
2676 BEGIN
2677
2678 SAVEPOINT IGFSL01B_SP1;
2679
2680 fnd_file.put_line(fnd_file.log,g_process_log || ' ' || g_person_log ||' : ' || award_rec_temp.person_number);
2681 fnd_file.put_line(fnd_file.log,g_process_log || ' ' || g_award_log || ' : ' || award_rec_temp.award_id);
2682
2683
2684 IF award_rec_temp.fed_fund_code IN ('DLP','DLS','DLU') THEN
2685 IF NOT p_dl_setup THEN
2686 IF lb_print_dl THEN
2687 fnd_message.set_name('IGF','IGF_SL_NO_DL_SETUP');
2688 fnd_file.put_line(fnd_file.log,fnd_message.get);
2689 lb_print_dl := FALSE;
2690 END IF;
2691 RAISE SKIP_THIS_RECORD;
2692 END IF;
2693 END IF;
2694
2695 IF award_rec_temp.fed_fund_code IN ('FLS','FLU','FLP','ALT','GPLUSFL') THEN
2696 IF NOT p_cl_setup THEN
2697 IF lb_print_cl THEN
2698 fnd_message.set_name('IGF','IGF_SL_NO_CL_SETUP');
2699 fnd_file.put_line(fnd_file.log,fnd_message.get);
2700 lb_print_cl := FALSE;
2701 END IF;
2702 RAISE SKIP_THIS_RECORD;
2703 END IF;
2704 END IF;
2705
2706 -- Check the Loan Limts amounts for Loans other than DLP/FLP/ALT
2707 IF award_rec_temp.fed_fund_code NOT IN ('PRK','DLP','FLP','ALT','GPLUSFL') THEN
2708 l_aid := 0;
2709 l_msg_name := NULL;
2710 -- since the fund amount is already awarded to the student then l_aid is passed as 0.
2711 igf_aw_packng_subfns.check_loan_limits (
2712 l_base_id => award_rec_temp.base_id,
2713 fund_type => award_rec_temp.fed_fund_code,
2714 l_award_id => award_rec_temp.award_id,
2715 l_adplans_id => award_rec_temp.adplans_id,
2716 l_aid => l_aid,
2717 l_std_loan_tab => l_loan_tab,
2718 p_msg_name => l_msg_name
2719 );
2720 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2721 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from check_loan_limits l_aid :' || l_aid);
2722 END IF;
2723 -- If the returned l_aid is 0 with no message returned or l_aid is greater than 0 then
2724 -- the set up is fine otherwise show the corresponding error message in the log.
2725 IF l_msg_name IS NOT NULL THEN
2726 --Error has occured
2727 IF l_aid = 0 THEN
2728 -- Bug 5091652 - Treating no loan limit setup for class standings combinations as error
2729 fnd_message.set_name('IGF',l_msg_name);
2730 fnd_file.put_line(fnd_file.log,fnd_message.get);
2731 RAISE SKIP_THIS_RECORD;
2732 ELSIF l_aid < 0 THEN
2733 -- Get the warning messages for the corresponding error messages. Bug 5064622
2734 -- These messages are to be treated as warnings only.
2735 IF l_msg_name = 'IGF_AW_AGGR_LMT_ERR' THEN
2736 l_msg_name := 'IGF_AW_AGGR_LMT_WARN';
2737 ELSIF l_msg_name = 'IGF_AW_ANNUAL_LMT_ERR' THEN
2738 l_msg_name := 'IGF_AW_ANNUAL_LMT_WARN';
2739 ELSIF l_msg_name = 'IGF_AW_SUB_AGGR_LMT_ERR' THEN
2740 l_msg_name := 'IGF_AW_SUB_AGGR_LMT_WARN';
2741 ELSIF l_msg_name = 'IGF_AW_SUB_LMT_ERR' THEN
2742 l_msg_name := 'IGF_AW_SUB_LMT_WARN';
2743 ELSIF l_msg_name = 'IGF_AW_UNSUB_AGGR_LMT_ERR' THEN
2744 l_msg_name := 'IGF_AW_UNSUB_AGGR_LMT_WRN';
2745 ELSIF l_msg_name = 'IGF_AW_UNSUB_LMT_ERR' THEN
2746 l_msg_name := 'IGF_AW_UNSUB_LMT_WARN';
2747 END IF;
2748 fnd_message.set_name('IGF',l_msg_name);
2749 fnd_message.set_token('FUND_CODE',award_rec_temp.fed_fund_code);
2750 fnd_file.put_line(fnd_file.log,fnd_message.get);
2751 END IF ;
2752 -- RAISE SKIP_THIS_RECORD;
2753 END IF;
2754
2755 END IF; -- End Check the Loan Limts amounts for Loans other than DLP/FLP/ALT
2756
2757 award_rec := award_rec_temp;
2758 g_adplans_id := award_rec.adplans_id; -- Bug 4568942
2759 g_person_id := award_rec.person_id;
2760 g_award_id := award_rec.award_id;
2761 g_s_default_status := NULL;
2762 g_grade_level_dl := NULL;
2763 g_enrollment_code := NULL;
2764 g_anticip_compl_date := NULL;
2765 g_grade_level_cl := NULL;
2766 lv_isir_present := TRUE;
2767 g_log_start_flag := FALSE;
2768 gn_transaction_num := NULL;
2769 gv_atd_entity_id_txt := NULL;
2770 gv_rep_entity_id_txt := NULL;
2771 gv_message := NULL;
2772 gv_return_status := NULL;
2773
2774 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2775 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values passed to get_fabase_details Base_id :' || award_rec.base_id);
2776 END IF;
2777 -- Get the S_Default_status from NSLDS Matched Table, and
2778 -- also the Student Grade Level code and Enrollment Code
2779
2780 get_fa_base_details(award_rec.base_id, g_s_default_status, g_grade_level_dl,g_grade_level_cl,
2781 g_enrollment_code, lv_isir_present, g_anticip_compl_date,gn_transaction_num,gv_unsub_elig_for_depnt);
2782
2783
2784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2785 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Default Status :' || g_s_default_status);
2786 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Grade_level_cl :' || g_grade_level_cl);
2787 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Grade_level_dl :' || g_grade_level_dl);
2788 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details Enrollment Code :' || g_enrollment_code);
2789 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details anticip_compl_date :' || g_anticip_compl_date);
2790 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_fabase_details gn_transaction_num :' || gn_transaction_num);
2791 END IF;
2792
2793 -- If the Award is a Loan and has a Fund-Source of FEDERAL, then
2794 -- Payment ISIR record should be present in the system for the Student.
2795 -- Else, the Loan Application record should not be created.
2796
2797 IF igf_sl_gen.chk_dl_fed_fund_code(award_rec.fed_fund_code) = 'TRUE' THEN
2798 log_message(award_rec.award_id);
2799 IF gn_transaction_num IS NULL THEN
2800 fnd_message.set_name('IGF','IGF_AP_NOPAYMENT_ISIR');
2801 fnd_file.put_line(fnd_file.log, fnd_message.get);
2802 RAISE SKIP_THIS_RECORD;
2803 END IF;
2804
2805 -- MN 29-Dec-2004 Call get entity ids Only for Full Participant
2806 IF award_rec_temp.fed_fund_code in ('DLU', 'DLS', 'DLP') THEN
2807 l_fund_type := 'DL';
2808 ELSIF award_rec_temp.fed_fund_code = 'PELL' THEN
2809 l_fund_type := 'PELL';
2810 END IF;
2811 IF igf_sl_dl_validation.check_full_participant (award_rec.ci_cal_type,
2812 award_rec.ci_sequence_number,
2813 l_fund_type) THEN
2814 get_dl_entity_id(award_rec.base_id,award_rec.ci_cal_type,award_rec.ci_sequence_number,
2815 gv_atd_entity_id_txt,gv_rep_entity_id_txt,gv_message,gv_return_status);
2816 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2817 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_return_status :' || gv_return_status);
2818 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_message :' || gv_message);
2819 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_atd_entity_id_txt :' || gv_atd_entity_id_txt);
2820 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug','The values returned from get_dl_entity_id gv_rep_entity_id_txt :' || gv_rep_entity_id_txt);
2821 END IF;
2822 END IF;
2823
2824 IF gv_return_status = 'E' THEN
2825 log_message(award_rec.award_id);
2826 fnd_file.put_line(fnd_file.log, gv_message);
2827 RAISE SKIP_THIS_RECORD;
2828 END IF;
2829 END IF;
2830 IF award_rec.fund_source = 'FEDERAL' AND lv_isir_present = FALSE THEN
2831 log_message(award_rec.award_id);
2832 -- Loan application record is not created as there is no Payment ISIR record for this federal loan
2833 fnd_message.set_name('IGF','IGF_SL_FEDLOAN_NO_FABASE');
2834 fnd_file.put_line(fnd_file.log, fnd_message.get);
2835 RAISE SKIP_THIS_RECORD;
2836
2837 END IF;
2838
2839 IF award_rec.SSN IS NULL THEN
2840
2841 log_message(award_rec.award_id);
2842 fnd_message.set_name('IGF','IGF_SL_NO_SSN_PRESENT');
2843 fnd_message.set_token('VALUE',award_rec.person_number);
2844 fnd_file.put_line(fnd_file.log, fnd_message.get);
2845
2846 RAISE SKIP_THIS_RECORD;
2847 END IF;
2848
2849
2850 ld_loan_prd_start_dt := get_loan_start_dt(award_rec.award_id);
2851 ld_loan_prd_end_dt := get_loan_end_dt(award_rec.award_id);
2852
2853 IF (ld_loan_prd_start_dt IS NULL OR ld_loan_prd_end_dt IS NULL) THEN
2854
2855 log_message(award_rec.award_id);
2856 fnd_message.set_name('IGF','IGF_SL_ALL_CANCEL_DISB');
2857 fnd_message.set_token('VALUE',award_rec.award_id);
2858 fnd_file.put_line(fnd_file.log, fnd_message.get);
2859
2860 RAISE SKIP_THIS_RECORD;
2861 END IF;
2862
2863 --Grade Level Code should be NOT NULL for both Direct Loan and CommonLine/ALT Loans
2864 --Enrollment Code should be NOT NULL for FFELP
2865 l_mapping:='Y';
2866
2867 --
2868 -- sjadhav
2869 -- Bug 2415013
2870 -- Default Enrollment Code to Full Time
2871 --
2872 g_enrollment_code := 'F';
2873 -- Bug # 5078693 - bvisvana - If Grade level code is not determined it cud be due to missing grade level and class stnd mapping.
2874 -- Since same message applies , merging for all loan types
2875 IF g_grade_level_dl IS NULL OR g_grade_level_cl IS NULL THEN
2876 IF award_rec.fed_fund_code IN ('DLP','DLS','DLU','FLP','FLS','FLU','ALT','GPLUSFL') THEN
2877 l_mapping:='N';
2878 fnd_message.set_name('IGF','IGF_SL_NO_CLSTND');
2879 END IF;
2880 END IF;
2881
2882 IF l_mapping ='N' THEN
2883 log_message(award_rec.award_id);
2884 fnd_message.set_token('PERSON_NUMBER',award_rec.person_number);
2885 fnd_message.set_token('AWARD_YEAR',award_rec.ci_alternate_code);
2886 fnd_file.put_line(fnd_file.log, fnd_message.get);
2887
2888 RAISE SKIP_THIS_RECORD;
2889
2890 END IF;
2891
2892 -- To get the Seq No.value based on records available in IGF_SL_LOR
2893 ln_seq_number := 0;
2894 OPEN cur_count_fed_code;
2895 FETCH cur_count_fed_code INTO ln_seq_number;
2896 CLOSE cur_count_fed_code;
2897
2898 -- To assign value of Loan Number using function
2899 lv_loan_number := NULL;
2900 lv_loan_number := ret_loan_number(ln_seq_number, award_rec.base_id);
2901 -- Insert these values into the IGF_SL_LOANS Table
2902
2903 g_accepted_amt := award_rec.accepted_amt;
2904 gn_award_id := award_rec.award_id;
2905 -- bvisvana - Bug # 4575843 - Before creating the loan record check whether the award accepted amount is in whole numbers
2906 IF ((g_accepted_amt - TRUNC(g_accepted_amt)) <> 0) THEN
2907 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2908 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' g_accepted_amt is not a whole number');
2909 END IF;
2910 fnd_message.set_name('IGF','IGF_AW_ACCEPT_AMT_WHOLE_NUM');
2911 fnd_file.put_line(fnd_file.log, fnd_message.get);
2912 RAISE SKIP_THIS_RECORD;
2913 END IF;
2914
2915 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2916 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' gn_award_id ' || gn_award_id);
2917 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' g_accepted_amt : ' || g_accepted_amt);
2918 END IF;
2919
2920 -- Modified the call of the IGF_SL_LOANS_PKG.INSERT_ROW to include the
2921 -- borrower determination code as part of Refunds DLD 2144600
2922
2923 lv_row_id := NULL;
2924 ln_loan_id := NULL;
2925
2926 igf_sl_loans_pkg.insert_row (
2927 x_mode => 'R',
2928 x_rowid => lv_row_id,
2929 x_loan_id => ln_loan_id,
2930 x_award_id => award_rec.award_id,
2931 x_seq_num => ln_seq_number,
2932 x_loan_number => lv_loan_number,
2933 x_loan_per_begin_date => ld_loan_prd_start_dt,
2934 x_loan_per_end_date => ld_loan_prd_end_dt,
2935 x_loan_status => 'G',
2936 x_loan_status_date => TRUNC(SYSDATE),
2937 x_loan_chg_status => NULL,
2938 x_loan_chg_status_date => NULL,
2939 x_active => 'Y',
2940 x_active_date => TRUNC(SYSDATE),
2941 x_borw_detrm_code => NULL,
2942 x_legacy_record_flag => NULL,
2943 x_external_loan_id_txt => NULL
2944 );
2945
2946 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2947 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' after insert of loan record ');
2948 END IF;
2949
2950 -- Procedure call for Inserting Origination Records into IGF_SL_LOR
2951
2952 IF igf_sl_gen.chk_dl_fed_fund_code(award_rec.fed_fund_code) = 'TRUE' THEN
2953 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2954 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' calling insert_lor_dl_records');
2955 END IF;
2956 insert_lor_dl_records(p_ci_cal_type, p_ci_sequence_number, ln_loan_id);
2957
2958 ELSIF igf_sl_gen.chk_cl_fed_fund_code(award_rec.fed_fund_code) = 'TRUE' THEN
2959 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2960 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' calling insert_lor_cl_records ');
2961 END IF;
2962 insert_lor_cl_records(p_ci_cal_type, p_ci_sequence_number, ln_loan_id, g_anticip_compl_date, g_grade_level_cl);
2963 END IF;
2964
2965 --Display Heading in OutputFile
2966 IF l_head <> 'Y' THEN
2967 fnd_file.put_line(fnd_file.output,RPAD(g_year,40,' ')||': '||award_rec.ci_alternate_code);
2968 fnd_message.set_name('IGF','IGF_SL_EFF_DATES');
2969 fnd_message.set_token('EFF_DATES',RPAD(award_rec.ci_start_dt,15,' ')||'-'||LPAD(award_rec.ci_end_dt,15,' '));
2970 fnd_file.put_line(fnd_file.output,fnd_message.get);
2971 fnd_file.new_line(fnd_file.output,1);
2972 l_head := 'Y';
2973 END IF;
2974
2975 fnd_file.new_line(fnd_file.output,1);
2976 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','FUND_CODE'),40,' ')||': '||award_rec.fund_code||'-'||award_rec.description);
2977 fnd_file.put_line(fnd_file.output,RPAD(g_person_log,40,' ')||': '||award_rec.person_number);
2978 fnd_file.put_line(fnd_file.output,RPAD(g_award_log,40,' ')||': '||award_rec.award_id);
2979 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'),40,' ')||': '||lv_loan_number);
2980 fnd_file.put_line(fnd_file.output,RPAD(igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','LOAN_AMT_OFFERED'),40,' ')||': '||award_rec.accepted_amt);
2981
2982 -- Reassign Y that loan records are found
2983 IF l_found_loans <> 'Y' THEN
2984 l_found_loans := 'Y';
2985 END IF;
2986
2987 EXCEPTION
2988
2989 WHEN SKIP_THIS_RECORD THEN
2990 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
2991 fnd_file.put_line(fnd_file.log, fnd_message.get);
2992 fnd_file.new_line(fnd_file.log,2);
2993 ROLLBACK TO IGFSL01B_SP1;
2994 END;
2995
2996 END LOOP;
2997
2998 -- Bug 2324159 LAR Process to display LOG File Info
2999 -- Display appropriate message if Loans records are created
3000
3001 IF l_found_loans ='Y' THEN
3002 --Loan Application Records created
3003 fnd_message.set_name('IGF','IGF_SL_LOANS_CREATED');
3004 fnd_file.put_line(fnd_file.log,fnd_message.get);
3005 fnd_file.new_line(fnd_file.log,2);
3006 ELSIF l_found_loans ='N' THEN
3007 --Loan Application not created
3008 fnd_message.set_name('IGF','IGF_SL_NO_LOANS');
3009 fnd_file.put_line(fnd_file.log,fnd_message.get);
3010 fnd_file.new_line(fnd_file.log,2);
3011 END IF;
3012
3013 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3014 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.insert_loan_records.debug',' End of create_loan_records ');
3015 END IF;
3016
3017 EXCEPTION
3018
3019 WHEN OTHERS THEN
3020 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
3021 fnd_message.set_token('NAME','IGF_SL_LAR_CREATION.create_loan_records');
3022 fnd_file.put_line(fnd_file.log,SQLERRM);
3023 igs_ge_msg_stack.add;
3024 app_exception.raise_exception;
3025
3026 END create_loan_records;
3027
3028
3029 FUNCTION per_in_fa ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
3030 p_ci_cal_type VARCHAR2,
3031 p_ci_sequence_number NUMBER,
3032 p_base_id OUT NOCOPY NUMBER
3033 )
3034 RETURN VARCHAR2
3035 IS
3036 CURSOR cur_get_pers_num ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3037 IS
3038 SELECT person_number
3039 FROM igs_pe_person_base_v
3040 WHERE
3041 person_id = p_person_id;
3042
3043 get_pers_num_rec cur_get_pers_num%ROWTYPE;
3044
3045 CURSOR cur_get_base (p_cal_type igs_ca_inst_all.cal_type%TYPE,
3046 p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
3047 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
3048 IS
3049 SELECT
3050 base_id
3051 FROM
3052 igf_ap_fa_base_rec_all
3053 WHERE
3054 person_id = p_person_id AND
3055 ci_cal_type = p_cal_type AND
3056 ci_sequence_number = p_sequence_number;
3057
3058 BEGIN
3059
3060 OPEN cur_get_pers_num(p_person_id);
3061 FETCH cur_get_pers_num INTO get_pers_num_rec;
3062
3063 IF cur_get_pers_num%NOTFOUND THEN
3064 CLOSE cur_get_pers_num;
3065 RETURN NULL;
3066 ELSE
3067 CLOSE cur_get_pers_num;
3068 OPEN cur_get_base(p_ci_cal_type,p_ci_sequence_number,p_person_id);
3069 FETCH cur_get_base INTO p_base_id;
3070 CLOSE cur_get_base;
3071
3072 RETURN get_pers_num_rec.person_number;
3073
3074 END IF;
3075
3076 EXCEPTION
3077 WHEN OTHERS THEN
3078 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
3079 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.PER_IN_FA');
3080 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3081 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.per_in_fa.exception','Exception:'||SQLERRM);
3082 END IF;
3083 igs_ge_msg_stack.add;
3084 app_exception.raise_exception;
3085
3086 END per_in_fa;
3087
3088 -- FA 134
3089
3090 /* MAIN PROCEDURE */
3091
3092 PROCEDURE insert_loan_records(
3093 errbuf OUT NOCOPY VARCHAR2,
3094 retcode OUT NOCOPY NUMBER,
3095 p_award_year IN VARCHAR2,
3096 p_run_mode IN VARCHAR2,
3097 p_fund_id IN NUMBER,
3098 p_dummy_1 IN NUMBER,
3099 p_base_id IN NUMBER,
3100 p_dummy_2 IN NUMBER,
3101 p_award_id IN NUMBER,
3102 p_dummy_3 IN NUMBER,
3103 p_dyn_pid_grp IN NUMBER
3104 )
3105 AS
3106
3107 /***************************************************************
3108 Created By : mesriniv
3109 Date Created By : 2000/11/13
3110 Purpose : To arrive at the default values for the various columns in
3111 IGF_SL_LOANS
3112 Known Limitations,Enhancements or Remarks
3113 Change History :
3114 Bug 2367953 Modify Parameters in Loans Process
3115 Who When What
3116 ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
3117 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
3118 bkkumar 04-04-04 FACR116 - Added a savepoint so that if the insert_lor procedures raises an
3119 exception then we have to rollback and display the appropriate message
3120 bkkumar 14-Jan-04 Bug# 3360702
3121 Passed the l_aid paramter as 0 to the check_loan_limits and also displayed the
3122 error message correctly.
3123 appropriate message.
3124 veramach 16-OCT-2003 Bug # 3108506 Added code to check loan limits before inserting loan records
3125 rasahoo 27-Aug-2003 Removed the call IGF_AP_OSS_PROCESS.GET_OSS_DETAILS,
3126 Changed the signature of procedure INSERT_LOAN_RECORDS,
3127 Removed the parameter P_GET_RECENT_INFO,
3128 as part of obsoletion of FA base record history
3129 mesriniv 20-may-2002 1.Added check for Grade Level Code in case of Direct Loans
3130 2.Added NVL for sch non ed brc id when arriving at loan number for CL Loans.
3131 3.Added call to remove any special characters from SSN while concatenating SSN
3132 in Loan Number
3133 4.Added new message for Grade Level Check for DL
3134 Bug 23241893 LAR Process does not populate loan amount correctly
3135 Who When What
3136 mesriniv 19-apr-2002 Added OFFERED Status in NOT IN condition of cursor cur_awards_rec.
3137 Bug No: 2324159
3138 Desc : LOAN APPLICATION PROCESS - LOG FILE DOES NOT DISPLAY ANY INFO
3139 Who When What
3140 mesriniv 18-apr-2002 1.Added cursor cur_check_fund_award
3141 2.Initialized a variable l_found_loans to check and dsiplay
3142 info on loan records created or not.
3143 3.Added code to print list of person numbers having got the loan and details
3144 in Output File
3145 Bug No : 1978873
3146 Who When What
3147 agairola 15-Mar-2002 Modified the call for the IGF_SL_LOANS_PKG.Insert_Row
3148 for Borrower Determination Code as part of Refunds DLD
3149 Enhancement No: 2144600
3150 ENH Bug No : 1806850 Bug Desc: Awards Build for Nov 2001 Rel
3151 Who When What
3152 mesriniv 6-Jul-2001 W.r.to Awards Build Filtered
3153 Awards with award status
3154 as Declined,Cancelled or Simulated
3155 from creating Loans
3156
3157 ***************************************************************/
3158
3159 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
3160 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
3161
3162 lv_loan_number igf_sl_loans_all.loan_number%TYPE;
3163
3164 lv_loan_status igf_sl_loans_all.loan_status%TYPE;
3165
3166 lv_row_id ROWID;
3167 lv_incr_date NUMBER;
3168 l_found_loans VARCHAR2(1);
3169 l_awd igf_aw_award_all.award_id%TYPE;
3170 l_fund igf_aw_fund_mast_all.fund_code%TYPE;
3171 l_head VARCHAR2(1);
3172 ln_rec_count INTEGER;
3173
3174 --For display of heading and parameters passed
3175 TYPE l_parameters IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
3176 l_para_rec l_parameters;
3177
3178 -- for Fetching school Code for the Award Year
3179 CURSOR cur_dl_setup IS
3180 SELECT *
3181 FROM igf_sl_dl_setup_all
3182 WHERE ci_cal_type = p_ci_cal_type
3183 AND ci_sequence_number = p_ci_sequence_number;
3184
3185 CURSOR cur_cl_setup IS
3186 SELECT *
3187 FROM igf_sl_cl_setup_all
3188 WHERE ci_cal_type = p_ci_cal_type
3189 AND ci_sequence_number = p_ci_sequence_number;
3190
3191 --Cursor to get the alternate code for the calendar instance
3192 CURSOR cur_alternate_code IS
3193 SELECT ca.alternate_code,start_dt,end_dt
3194 FROM igs_ca_inst ca
3195 WHERE ca.cal_type =p_ci_cal_type
3196 AND ca.sequence_number = p_ci_sequence_number;
3197
3198 --Bug 23241893 LAR Process does not populate loan amount correctly
3199 --Added OFFERED Status in NOT IN condition.
3200
3201
3202 --Check if award exists
3203 CURSOR cur_check_fund_award (p_base_id NUMBER, p_award_id NUMBER, p_fund_id NUMBER, p_cal_type VARCHAR2, p_seq_number NUMBER)
3204 IS
3205 SELECT awd.award_id,fmast.fund_code
3206 FROM igf_aw_award_all awd,
3207 igf_aw_fund_mast_all fmast,
3208 igf_ap_fa_base_rec_all base,
3209 igf_aw_fund_cat_all fcat
3210 WHERE awd.fund_id = fmast.fund_id
3211 AND awd.base_id = base.base_id
3212 AND awd.award_status = 'ACCEPTED'
3213 AND fmast.fund_code = fcat.fund_code
3214 AND fcat.fed_fund_code IN ('DLP','DLS','DLU','FLP','FLS','FLU','ALT','GPLUSFL')
3215 AND awd.award_id = NVL(p_award_id,awd.award_id)
3216 AND awd.base_id = NVL(p_base_id,awd.base_id)
3217 AND awd.fund_id = NVL(p_fund_id,awd.fund_id)
3218 AND fmast.ci_cal_type = p_cal_type
3219 AND fmast.ci_sequence_number = p_seq_number;
3220
3221 check_fund_award_rec cur_check_fund_award%ROWTYPE;
3222 --Get the Descriptions for the Parameters Passed:
3223 -- Get the details of
3224 CURSOR cur_get_parameters IS
3225 SELECT meaning
3226 FROM igf_lookups_view
3227 WHERE lookup_type = 'IGF_GE_PARAMETERS'
3228 AND lookup_code IN ('AWARD_ID',
3229 'AWARD_YEAR',
3230 'FUND_CODE',
3231 'GET_LATEST_OSS',
3232 'PARAMETER_PASS',
3233 'PERSON_ID_GROUP',
3234 'PERSON_NUMBER',
3235 'PROCESSING',
3236 'RUN_MODE')
3237 ORDER BY lookup_code;
3238
3239 CURSOR cur_chk_pidgroup ( p_dyn_pid_grp NUMBER)
3240 IS
3241 SELECT group_id
3242 FROM igs_pe_persid_group_all
3243 WHERE
3244 group_id = p_dyn_pid_grp AND
3245 closed_ind = 'N';
3246
3247 chk_pidgroup_rec cur_chk_pidgroup%ROWTYPE;
3248
3249 l_msg_name fnd_new_messages.message_name%TYPE;
3250 l_aid NUMBER;
3251 l_loan_tab igf_aw_packng_subfns.std_loan_tab;
3252
3253 lb_dl_setup BOOLEAN;
3254 lb_cl_setup BOOLEAN;
3255 l_list VARCHAR2(32767);
3256
3257 TYPE cur_person_id_type IS REF CURSOR;
3258 cur_per_grp cur_person_id_type;
3259
3260 lv_status VARCHAR2(1);
3261 l_person_id hz_parties.party_id%TYPE;
3262 lb_record_exist BOOLEAN;
3263
3264 ln_base_id NUMBER;
3265 lv_person_number hz_parties.party_number%TYPE;
3266 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
3267
3268 BEGIN
3269
3270 igf_aw_gen.set_org_id(NULL);
3271 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3272 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','Start of main');
3273 END IF;
3274
3275 retcode := 0;
3276 l_msg_name := NULL;
3277 l_aid := 0;
3278 l_loan_tab := igf_aw_packng_subfns.std_loan_tab();
3279 l_found_loans := 'N';
3280 l_head := 'N';
3281
3282
3283 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3284 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','Main inti');
3285 END IF;
3286
3287 --Splitting the Award Year Value to ci_cal_type and ci_sequence_number
3288 p_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
3289 p_ci_sequence_number := TO_NUMBER(LTRIM(RTRIM(SUBSTR(p_award_year,11))));
3290
3291 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3292 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','get award year cal type ' || p_ci_cal_type ||' seq num ' || p_ci_sequence_number);
3293 END IF;
3294
3295 --Get the alternate code
3296 OPEN cur_alternate_code;
3297 FETCH cur_alternate_code INTO g_alternate_code,g_start_date,g_end_date;
3298 IF cur_alternate_code%NOTFOUND THEN
3299 CLOSE cur_alternate_code;
3300 fnd_message.set_name('IGF','IGF_SL_NO_CALENDAR');
3301 igs_ge_msg_stack.add;
3302 fnd_file.put_line(fnd_file.log,fnd_message.get);
3303
3304 RAISE NO_DATA_FOUND;
3305 END IF;
3306 CLOSE cur_alternate_code;
3307
3308 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3309 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','get award year alt code ' || g_alternate_code ||' awd start date ' || g_start_date || ' awd end date '|| g_end_date);
3310 END IF;
3311
3312 --List of all Parameters:
3313 ln_rec_count := 0;
3314 OPEN cur_get_parameters;
3315 LOOP
3316 ln_rec_count := ln_rec_count + 1;
3317 FETCH cur_get_parameters INTO l_para_rec(ln_rec_count);
3318 EXIT WHEN cur_get_parameters%NOTFOUND;
3319 END LOOP;
3320 CLOSE cur_get_parameters;
3321
3322 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3323 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','after getting parameter lkp desc');
3324 END IF;
3325
3326 --Show the parameters passed -- print all parameters passed
3327 g_process_log := l_para_rec(8);
3328 g_award_log := l_para_rec(1);
3329 g_person_log := l_para_rec(7);
3330 g_year := l_para_rec(2);
3331 fnd_file.new_line(fnd_file.log,2);
3332 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(5),50,' '));
3333 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(2),30,' ')||':'||RPAD(' ',4,' ')||g_alternate_code);
3334 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(9),30,' ')||':'||RPAD(' ',4,' ')||igf_aw_gen.lookup_desc('IGF_SL_CL_SLJ01_RUN_MODE',p_run_mode));
3335 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(3),30,' ')||':'||RPAD(' ',4,' ')||get_fund_desc(p_fund_id));
3336 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(7),30,' ')||':'||RPAD(' ',4,' ')||igf_gr_gen.get_per_num(p_base_id)); -- person nunmber
3337 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(1),30,' ')||':'||RPAD(' ',4,' ')||p_award_id);
3338 fnd_file.put_line(fnd_file.log,RPAD(l_para_rec(6),30,' ')||':'||RPAD(' ',4,' ')||get_grp_name(p_dyn_pid_grp));
3339 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
3340 fnd_file.new_line(fnd_file.log,2);
3341
3342
3343 -- check if parameters passed are valid ot not
3344 OPEN cur_check_fund_award(p_base_id, p_award_id, p_fund_id,p_ci_cal_type,p_ci_sequence_number);
3345 FETCH cur_check_fund_award INTO check_fund_award_rec;
3346 IF cur_check_fund_award%NOTFOUND THEN
3347 CLOSE cur_check_fund_award;
3348 fnd_message.set_name ('IGF','IGF_SL_NO_LOAN_AWARDS');
3349 fnd_file.put_line(fnd_file.log, fnd_message.get);
3350 fnd_file.new_line(fnd_file.log, 1);
3351 RETURN;
3352 ELSE
3353 CLOSE cur_check_fund_award;
3354 END IF;
3355
3356
3357
3358 -- Fetching Setup details for Direct Loans
3359 OPEN cur_dl_setup;
3360 FETCH cur_dl_setup INTO dl_setup_rec;
3361 IF cur_dl_setup%NOTFOUND THEN
3362 lb_dl_setup := FALSE;
3363 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3364 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','direct loan setup not found');
3365 END IF;
3366 END IF;
3367 CLOSE cur_dl_setup;
3368
3369 -- Fetching Setup details for CommonLine Loans
3370 OPEN cur_cl_setup;
3371 FETCH cur_cl_setup INTO cl_setup_rec;
3372 IF cur_cl_setup%NOTFOUND THEN
3373 lb_cl_setup := FALSE;
3374 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3375 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','FFELP loan setup not found');
3376 END IF;
3377 END IF;
3378 CLOSE cur_cl_setup;
3379
3380 -- Base 36 Date Code for CommonLine Loan Number
3381 SELECT TO_CHAR(SYSDATE,'DDDYY') INTO lv_incr_date FROM DUAL;
3382 p_incr_date_code := igf_sl_gen.base10_to_base36(lv_incr_date);
3383
3384 -- fa134
3385 IF p_base_id IS NOT NULL AND
3386 ( igf_gr_gen.get_per_num(p_base_id) IS NULL OR
3387 NOT check_fa_rec(p_base_id, p_ci_cal_type, p_ci_sequence_number))
3388 THEN
3389 fnd_message.set_name('IGF','IGF_SP_NO_FA_BASE_REC');
3390 fnd_file.put_line(fnd_file.log, fnd_message.get);
3391 fnd_file.new_line(fnd_file.log, 1);
3392 RETURN;
3393 END IF;
3394 -- FA134
3395 IF p_dyn_pid_grp IS NOT NULL THEN
3396 OPEN cur_chk_pidgroup ( p_dyn_pid_grp);
3397 FETCH cur_chk_pidgroup INTO chk_pidgroup_rec;
3398 CLOSE cur_chk_pidgroup;
3399 IF chk_pidgroup_rec.group_id IS NULL THEN
3400 fnd_message.set_name('IGF','IGF_SL_COD_PERSID_GRP_INV');
3401 fnd_file.put_line(fnd_file.log, fnd_message.get);
3402 fnd_file.new_line(fnd_file.log, 1);
3403 RETURN;
3404 END IF;
3405 END IF;
3406 -- FA134
3407
3408 IF p_dyn_pid_grp IS NOT NULL AND p_base_id IS NOT NULL THEN
3409 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
3410 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
3411 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'));
3412 fnd_file.put_line(fnd_file.log, fnd_message.get);
3413 fnd_file.new_line(fnd_file.log, 1);
3414 RETURN;
3415 END IF;
3416
3417 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3418 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','base id and pgroup id check');
3419 END IF;
3420
3421 IF p_dyn_pid_grp IS NOT NULL AND p_award_id IS NOT NULL THEN
3422 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
3423 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
3424 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'));
3425 fnd_file.put_line(fnd_file.log, fnd_message.get);
3426 fnd_file.new_line(fnd_file.log, 1);
3427 RETURN;
3428 END IF;
3429 -- FA134
3430
3431 IF p_run_mode = 'I' THEN -- FA134
3432
3433 IF p_base_id IS NULL AND p_dyn_pid_grp IS NULL THEN
3434 create_loan_records(p_award_id, p_fund_id, p_base_id,p_ci_cal_type, p_ci_sequence_number, lb_dl_setup, lb_cl_setup);
3435 RETURN;
3436 END IF;
3437
3438 IF p_base_id IS NOT NULL THEN
3439 create_loan_records(p_award_id, p_fund_id, p_base_id,p_ci_cal_type, p_ci_sequence_number, lb_dl_setup, lb_cl_setup);
3440 RETURN;
3441 END IF;
3442
3443 IF p_dyn_pid_grp IS NOT NULL THEN
3444
3445 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
3446 fnd_message.set_token('P_PER_GRP',get_grp_name(p_dyn_pid_grp));
3447 fnd_file.new_line(fnd_file.log, 1);
3448 fnd_file.put_line(fnd_file.log, fnd_message.get);
3449
3450 --Bug #5021084
3451 l_list := igf_ap_ss_pkg.get_pid( p_dyn_pid_grp,lv_status,lv_group_type);
3452
3453 --Bug #5021084. Passing Group ID if the group type is STATIC.
3454 IF lv_group_type = 'STATIC' THEN
3455 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_dyn_pid_grp;
3456 ELSIF lv_group_type = 'DYNAMIC' THEN
3457 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
3458 END IF;
3459
3460 FETCH cur_per_grp INTO l_person_id;
3461
3462 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3463 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','Starting to process person group '|| p_dyn_pid_grp);
3464 END IF;
3465
3466 IF cur_per_grp%NOTFOUND THEN
3467 CLOSE cur_per_grp;
3468 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
3469 fnd_file.put_line(fnd_file.log,fnd_message.get);
3470 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3471 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','No persons in group '|| p_dyn_pid_grp);
3472 END IF;
3473 ELSE
3474 IF cur_per_grp%FOUND THEN -- Check if the person exists in FA.
3475 lb_record_exist := FALSE;
3476 LOOP
3477 ln_base_id := 0;
3478 lv_person_number := NULL;
3479 lv_person_number := per_in_fa (l_person_id,p_ci_cal_type,p_ci_sequence_number,ln_base_id);
3480 IF lv_person_number IS NOT NULL THEN
3481 IF ln_base_id IS NOT NULL THEN
3482 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
3483 fnd_message.set_token('STDNT',lv_person_number);
3484 fnd_file.put_line(fnd_file.log, fnd_message.get);
3485 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3486 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG base id ' || ln_base_id);
3487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG lv_person_number ' || lv_person_number);
3488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG l_person_id ' || l_person_id);
3489 END IF;
3490 create_loan_records(p_award_id, p_fund_id, ln_base_id,p_ci_cal_type, p_ci_sequence_number, lb_dl_setup, lb_cl_setup );
3491 IF NOT lb_record_exist THEN
3492 lb_record_exist := TRUE;
3493 END IF;
3494 ELSE -- log a message and skip this person, base id not found
3495 fnd_message.set_name('IGF','IGF_GR_LI_PER_INVALID');
3496 fnd_message.set_token('PERSON_NUMBER',lv_person_number);
3497 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(p_ci_cal_type,p_ci_sequence_number));
3498 fnd_file.put_line(fnd_file.log,fnd_message.get);
3499 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3500 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug',igf_gr_gen.get_per_num_oss(l_person_id) || ' not in FA');
3501 END IF;
3502 END IF; -- base id not found
3503 ELSE
3504 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
3505 fnd_file.put_line(fnd_file.log,RPAD(' ',5) ||fnd_message.get);
3506 END IF; -- person number not null
3507
3508 FETCH cur_per_grp INTO l_person_id;
3509 EXIT WHEN cur_per_grp%NOTFOUND;
3510 END LOOP;
3511 IF NOT lb_record_exist THEN
3512 fnd_file.new_line(fnd_file.log, 1);
3513 fnd_message.set_name('IGF','IGF_SL_NO_LOAN_AWARDS');--
3514 fnd_file.put_line(fnd_file.log, fnd_message.get);
3515 fnd_file.new_line(fnd_file.log, 1);
3516 RETURN;
3517 END IF;
3518 CLOSE cur_per_grp;
3519 END IF; -- group found
3520 END IF; -- group not found
3521 END IF; -- pid group is not null
3522
3523 END IF ; -- RUN MODE = INSERT
3524
3525 IF p_run_mode = 'U' THEN
3526
3527 -- fa134, if update mode then call update_loan(pass all parameters inlciding lb_dl_s, lb_cl_s);
3528
3529 IF p_base_id IS NULL AND p_dyn_pid_grp IS NULL THEN
3530 update_loan_rec(p_award_id, p_fund_id, p_base_id,p_ci_cal_type,p_ci_sequence_number);
3531 RETURN;
3532 END IF;
3533
3534 IF p_base_id IS NOT NULL THEN
3535 update_loan_rec(p_award_id, p_fund_id, p_base_id,p_ci_cal_type,p_ci_sequence_number);
3536 RETURN;
3537 END IF;
3538
3539 IF p_dyn_pid_grp IS NOT NULL THEN
3540
3541 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
3542 fnd_message.set_token('P_PER_GRP',get_grp_name( p_dyn_pid_grp));
3543 fnd_file.new_line(fnd_file.log, 1);
3544 fnd_file.put_line(fnd_file.log, fnd_message.get);
3545
3546 --Bug #5021084
3547 l_list := NULL;
3548 lv_group_type := NULL;
3549 l_list := igf_ap_ss_pkg.get_pid( p_dyn_pid_grp,lv_status,lv_group_type);
3550
3551 --Bug #5021084. Passing Group ID if the group type is STATIC.
3552 IF lv_group_type = 'STATIC' THEN
3553 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_dyn_pid_grp;
3554 ELSIF lv_group_type = 'DYNAMIC' THEN
3555 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
3556 END IF;
3557
3558 FETCH cur_per_grp INTO l_person_id;
3559
3560 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3561 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','Starting to process person group '|| p_dyn_pid_grp);
3562 END IF;
3563
3564 IF cur_per_grp%NOTFOUND THEN
3565 CLOSE cur_per_grp;
3566 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
3567 fnd_file.put_line(fnd_file.log,fnd_message.get);
3568 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3569 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','No persons in group '|| p_dyn_pid_grp);
3570 END IF;
3571 ELSE
3572 IF cur_per_grp%FOUND THEN -- Check if the person exists in FA.
3573 lb_record_exist := FALSE;
3574 LOOP
3575 ln_base_id := 0;
3576 lv_person_number := NULL;
3577 lv_person_number := per_in_fa (l_person_id,p_ci_cal_type,p_ci_sequence_number,ln_base_id);
3578 IF lv_person_number IS NOT NULL THEN
3579 IF ln_base_id IS NOT NULL THEN
3580 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
3581 fnd_message.set_token('STDNT',lv_person_number);
3582 fnd_file.put_line(fnd_file.log, fnd_message.get);
3583 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3584 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG base id ' || ln_base_id);
3585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG lv_person_number ' || lv_person_number);
3586 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug','PIDG l_person_id ' || l_person_id);
3587 END IF;
3588 update_loan_rec(p_award_id, p_fund_id, ln_base_id,p_ci_cal_type, p_ci_sequence_number);
3589 IF NOT lb_record_exist THEN
3590 lb_record_exist := TRUE;
3591 END IF;
3592 ELSE -- log a message and skip this person, base id not found
3593 fnd_message.set_name('IGF','IGF_GR_LI_PER_INVALID');
3594 fnd_message.set_token('PERSON_NUMBER',lv_person_number);
3595 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(p_ci_cal_type,p_ci_sequence_number));
3596 fnd_file.put_line(fnd_file.log,fnd_message.get);
3597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
3598 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_lar_creation.main.debug',igf_gr_gen.get_per_num_oss(l_person_id) || ' not in FA');
3599 END IF;
3600 END IF; -- base id not found
3601 ELSE
3602 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
3603 fnd_file.put_line(fnd_file.log,RPAD(' ',5) ||fnd_message.get);
3604 END IF; -- person number not null
3605
3606 FETCH cur_per_grp INTO l_person_id;
3607 EXIT WHEN cur_per_grp%NOTFOUND;
3608 END LOOP;
3609 IF NOT lb_record_exist THEN
3610 fnd_file.new_line(fnd_file.log, 1);
3611 fnd_message.set_name('IGF','IGF_SL_NO_LOAN_AWARDS');--
3612 fnd_file.put_line(fnd_file.log, fnd_message.get);
3613 fnd_file.new_line(fnd_file.log, 1);
3614 RETURN;
3615 END IF;
3616 CLOSE cur_per_grp;
3617 END IF; -- group found
3618 END IF; -- group not found
3619 END IF; -- pid group is not null
3620
3621 END IF ; -- RUN MODE = UPDATE
3622
3623 -- COMMIT;
3624
3625 EXCEPTION
3626 WHEN NO_DATA_FOUND THEN
3627 NULL;
3628
3629 WHEN OTHERS THEN
3630 ROLLBACK;
3631 retcode :=2;
3632 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
3633 fnd_file.put_line(fnd_file.log,SQLERRM);
3634 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
3635 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_lar_creation.main.debug','SLQERRM ' || SQLERRM);
3636 END IF;
3637 igs_ge_msg_stack.conc_exception_hndl;
3638
3639 END insert_loan_records;
3640
3641 END igf_sl_lar_creation;