[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_DL_GEN_XML
Source
1 PACKAGE BODY igf_sl_dl_gen_xml AS
2 /* $Header: IGFSL25B.pls 120.8 2006/08/08 06:59:59 veramach noship $ */
3
4 ------------------------------------------------------------------------------------------------
5 --
6 -- Process Flow
7 -- main()
8 -- --> 1.validate parameters
9 -- --> 2.process_loan()
10 -- --> 1.igf_sl_dl_validation.cod_loan_validations()
11 -- --> 2.insert_lor_loc() for valid loans
12 -- --> check for valid loans in igf_sl_lor_loc_all for document_id
13 -- --> if yes then submit_xml_event()
14 -- --> 1.XML Gatway Standard to create xml
15 -- --> 2.store_xml() - workflow process
16 -- --> 1.check for CLOB length, proper document id,
17 -- proper submission of request if okay then
18 -- --> 2.insert into igf_sl_cod_doc_dtls
19 -- --> 3.submit concurrent job IGFSLJ19-print_xml()
20 -- --> 1. edit_clob()
21 -- --> 2. update_status(), update igf_sl_cod_doc_dtls
22 -- --> 3. print_out_xml()
23 ------------------------------------------------------------------------------------------------
24 gv_document_id_txt VARCHAR2(30);
25 gv_dl_version VARCHAR2(30);
26 gn_new_base_id NUMBER;
27 gn_old_base_id NUMBER;
28
29 TYPE loan_ley_record IS RECORD
30 ( orig_fee_pct_num NUMBER,
31 int_reb_pct_num NUMBER,
32 pnote_print_code VARCHAR2(30),
33 disclosure_print_code VARCHAR2(30),
34 grade_level_code VARCHAR2(30),
35 fin_awd_begin_date DATE,
36 fin_awd_end_date DATE,
37 acad_yr_begin_date DATE,
38 acad_yr_end_date DATE
39 );
40
41 TYPE loan_key_list IS TABLE OF loan_ley_record;
42 loan_key_rec loan_key_list;
43
44 ln_count NUMBER;
45
46
47 PROCEDURE edit_clob(p_document_id_txt VARCHAR2, p_xml_clob OUT NOCOPY CLOB,p_rowid OUT NOCOPY ROWID)
48 IS
49
50 CURSOR cur_doc_dtls (p_document_id_txt VARCHAR2)
51 IS
52 SELECT rowid ,document_id_txt,outbound_doc
53 FROM igf_sl_cod_doc_dtls
54 WHERE document_id_txt = p_document_id_txt
55 AND doc_status = 'R'
56 FOR UPDATE OF outbound_doc;
57
58 doc_dtls_rec cur_doc_dtls%ROWTYPE;
59 lc_xmldoc CLOB;
60 lv_buffer VARCHAR2(32767);
61 ln_amount INTEGER;
62 ln_len NUMBER;
63 ln_offset NUMBER;
64 ln_start_pos INTEGER;
65 ln_end_pos INTEGER;
66 lv_document_id_txt VARCHAR2(30);
67
68 BEGIN
69
70 OPEN cur_doc_dtls(p_document_id_txt);
71 FETCH cur_doc_dtls INTO p_rowid,lv_document_id_txt, lc_xmldoc;
72 IF cur_doc_dtls%NOTFOUND THEN
73 CLOSE cur_doc_dtls;
74 fnd_message.set_name('IGF','IGF_SL_DL_PRINT_DOC_FAIL');
75 fnd_file.put_line(fnd_file.log, fnd_message.get);
76 RETURN;
77 ELSIF cur_doc_dtls%FOUND THEN
78 CLOSE cur_doc_dtls;
79 OPEN cur_doc_dtls(p_document_id_txt);
80 FETCH cur_doc_dtls INTO p_rowid,lv_document_id_txt, p_xml_clob;
81 CLOSE cur_doc_dtls;
82 END IF;
83
84 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
85 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.edit_clob.debug','p doc id is valid');
86 END IF;
87
88 -- Editing LoB
89 ln_amount := DBMS_LOB.GETLENGTH(p_xml_clob);
90 DBMS_LOB.ERASE(p_xml_clob,ln_amount,1);
91 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
92 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.edit_clob.debug','CLOB ln_amount ' || ln_amount);
93 END IF;
94
95 -- find doc between first tag and end of root tag
96 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
97 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.edit_clob.debug','Find start and end positions ');
98 END IF;
99
100 ln_start_pos := DBMS_LOB.INSTR(lc_xmldoc,'<CommonRecord',1,1);
101 ln_end_pos := DBMS_LOB.INSTR(lc_xmldoc,'</CR>',1,1);
102
103 DBMS_LOB.COPY(p_xml_clob, lc_xmldoc, ln_end_pos-ln_start_pos, 1, ln_start_pos);
104 -- DBMS_LOB.COPY(lobd, lobs, amt, dest_offset, src_offset);
105 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
106 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.edit_clob.debug','End of printing ');
107 END IF;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
112 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.edit_clob.exception','Exception:'||SQLERRM);
113 END IF;
114 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
115 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.EDIT_CLOB');
116 igs_ge_msg_stack.add;
117 app_exception.raise_exception;
118 END edit_clob;
119
120 PROCEDURE update_status(p_document_id_txt VARCHAR2)
121 IS
122
123 CURSOR cur_cod_loans (p_document_id_txt VARCHAR2)
124 IS
125 SELECT loc.loan_id FROM igf_sl_lor_loc_all loc
126 WHERE loc.document_id_txt = p_document_id_txt;
127
128 CURSOR cur_cod_disb (p_document_id_txt VARCHAR2)
129 IS
130 SELECT disb.award_id, disb.disb_num, disb.disb_seq_num
131 FROM igf_aw_db_cod_dtls disb
132 WHERE disb.document_id_txt = p_document_id_txt;
133
134 CURSOR cur_sys_loans (p_loan_id NUMBER)
135 IS
136 SELECT loan.* FROM igf_sl_loans loan
137 WHERE loan.loan_id = p_loan_id;
138
139 CURSOR cur_sys_disb (p_award_id NUMBER,p_disb_num NUMBER, p_disb_seq NUMBER)
140 IS
141 SELECT disb.rowid row_id,disb.* FROM igf_aw_db_chg_dtls disb
142 WHERE disb.award_id = p_award_id AND
143 disb.disb_num = p_disb_num AND
144 disb.disb_seq_num = p_disb_seq;
145 BEGIN
146
147 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
148 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.update_status.debug','First doc id ' || p_document_id_txt);
149 END IF;
150
151 FOR cod_rec IN cur_cod_loans(p_document_id_txt)
152 LOOP
153 FOR sys_rec IN cur_sys_loans (cod_rec.loan_id)
154 LOOP
155 IF sys_rec.loan_status = 'A' THEN
156 sys_rec.loan_chg_status := 'S';
157 sys_rec.loan_chg_status_date := TRUNC(SYSDATE);
158 ELSIF sys_rec.loan_status = 'G' THEN
159 sys_rec.loan_status := 'S';
160 sys_rec.loan_status_date := TRUNC(SYSDATE);
161 END IF;
162 igf_sl_loans_pkg.update_row(x_rowid => sys_rec.row_id,
163 x_loan_id => sys_rec.loan_id,
164 x_award_id => sys_rec.award_id,
165 x_seq_num => sys_rec.seq_num,
166 x_loan_number => sys_rec.loan_number,
167 x_loan_per_begin_date => sys_rec.loan_per_begin_date,
168 x_loan_per_end_date => sys_rec.loan_per_end_date,
169 x_loan_status => sys_rec.loan_status,
170 x_loan_status_date => sys_rec.loan_status_date,
171 x_loan_chg_status => sys_rec.loan_chg_status,
172 x_loan_chg_status_date => sys_rec.loan_chg_status_date,
173 x_active => sys_rec.active,
174 x_active_date => sys_rec.active_date,
175 x_borw_detrm_code => sys_rec.borw_detrm_code,
176 x_mode => 'R',
177 x_legacy_record_flag => sys_rec.legacy_record_flag,
178 x_external_loan_id_txt => sys_rec.external_loan_id_txt,
179 x_called_from => NULL);
180 END LOOP;
181 END LOOP;
182
183 FOR cod_rec IN cur_cod_disb(p_document_id_txt)
184 LOOP
185 FOR sys_rec IN cur_sys_disb(cod_rec.award_id,cod_rec.disb_num,cod_rec.disb_seq_num)
186 LOOP
187
188 sys_rec.disb_status := 'S';
189 sys_rec.disb_status_date := TRUNC(SYSDATE);
190
191 igf_aw_db_chg_dtls_pkg.update_row(x_rowid => sys_rec.row_id,
192 x_award_id => sys_rec.award_id,
193 x_disb_num => sys_rec.disb_num,
194 x_disb_seq_num => sys_rec.disb_seq_num,
195 x_disb_accepted_amt => sys_rec.disb_accepted_amt,
196 x_orig_fee_amt => sys_rec.orig_fee_amt,
197 x_disb_net_amt => sys_rec.disb_net_amt,
198 x_disb_date => sys_rec.disb_date,
199 x_disb_activity => sys_rec.disb_activity,
200 x_disb_status => sys_rec.disb_status,
201 x_disb_status_date => sys_rec.disb_status_date,
202 x_disb_rel_flag => sys_rec.disb_rel_flag,
203 x_first_disb_flag => sys_rec.first_disb_flag,
204 x_interest_rebate_amt => sys_rec.interest_rebate_amt,
205 x_disb_conf_flag => sys_rec.disb_conf_flag,
206 x_pymnt_prd_start_date => sys_rec.pymnt_prd_start_date,
207 x_note_message => sys_rec.note_message,
208 x_batch_id_txt => sys_rec.batch_id_txt,
209 x_ack_date => sys_rec.ack_date,
210 x_booking_id_txt => sys_rec.booking_id_txt,
211 x_booking_date => sys_rec.booking_date,
212 x_mode => 'R'
213 );
214 END LOOP;
215 END LOOP;
216
217 EXCEPTION
218 WHEN OTHERS THEN
219 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
220 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.update_status.exception','Exception:'||SQLERRM);
221 END IF;
222 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
223 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.UPDATE_STATUS');
224 igs_ge_msg_stack.add;
225 app_exception.raise_exception;
226 END update_status;
227
228 PROCEDURE print_out_xml(p_xml_clob CLOB)
229 IS
230 lv_myclob_text VARCHAR2(32767);
231 ln_len NUMBER;
232 ln_offset NUMBER;
233 ln_amount INTEGER;
234 BEGIN
235
236 ln_len := dbms_lob.getlength(p_xml_clob);
237 ln_offset := 1;
238 ln_amount := 1023; -- changed from 32767 to 1023 so that it can handle upto Fixed-width-32-byte CLOBs objects (32767/1023 = 32) Bug 4323926
239
240 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
241 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','ln_len ' || ln_len);
242 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','ln_offset ' || ln_offset);
243 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','ln_amount ' || ln_amount);
244 END IF;
245
246 WHILE (ln_len > 0) LOOP
247 lv_myclob_text := DBMS_LOB.SUBSTR (p_xml_clob, ln_amount, ln_offset);
248 fnd_file.put(fnd_file.output,lv_myclob_text);
249 ln_amount := LENGTH(lv_myclob_text); -- this will handle any Character Set. But to be optimistic ln_amount is initialized to 1023 instead of 32767. Bug 4323926
250 IF ln_amount = 0 THEN
251 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
252 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','ln_amount is Zero after DBMS_LOB.SUBSTR, ln_len = ' || ln_len);
253 END IF;
254 EXIT;
255 END IF;
256 ln_len := ln_len - ln_amount;
257 ln_offset := ln_offset + ln_amount;
258 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
259 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','Loop ln_len ' || ln_len);
260 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','Loop ln_offset ' || ln_offset);
261 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.debug','Loop ln_amount ' || ln_amount);
262 END IF;
263 END LOOP;
264 fnd_file.new_line(fnd_file.output,1);
265
266 EXCEPTION
267 WHEN OTHERS THEN
268 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
269 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.print_out_xml.exception','Exception:'||SQLERRM);
270 END IF;
271 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
272 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.PRINT_OUT_XML');
273 igs_ge_msg_stack.add;
274 app_exception.raise_exception;
275 END print_out_xml;
276
277 PROCEDURE insert_lor_loc(p_loan_rec cur_pick_loans%ROWTYPE,
278 p_source_id VARCHAR2,
279 student_dtl_rec igf_sl_gen.person_dtl_rec,
280 parent_dtl_rec igf_sl_gen.person_dtl_rec,
281 p_isir_ssn VARCHAR2, p_isir_dob DATE,
282 p_isir_lname VARCHAR2, p_isir_dep VARCHAR2,
283 p_isir_tnum NUMBER, p_acad_begin DATE,
284 p_acad_end DATE, p_s_phone VARCHAR2, p_p_phone VARCHAR2)
285 IS
286
287 CURSOR cur_setup (p_cal_type VARCHAR2, p_seq_number NUMBER) IS
288 SELECT response_option_code, int_rebate
289 FROM igf_sl_dl_setup_all
290 WHERE ci_cal_type = p_cal_type
291 AND ci_sequence_number = p_seq_number;
292
293 setup_rec cur_setup%ROWTYPE;
294
295 CURSOR cur_loan_oldinfo (p_loan_id NUMBER) IS
296 SELECT loc.*
297 FROM igf_sl_lor_loc_all loc
298 WHERE loc.loan_id = p_loan_id;
299
300 loan_oldinfo_rec cur_loan_oldinfo%ROWTYPE;
301
302 CURSOR cur_disb_rec (p_award_id NUMBER) IS
303 SELECT chg.*
304 FROM igf_aw_db_chg_dtls chg
305 WHERE award_id = p_award_id
306 --AND disb_status = 'G'; -- Ready to Send (commented bcz of the bug 4105689)
307 AND disb_status = 'G'; -- Ready to Send (uncommented again bcz of another bug 4390096)
308
309 lv_elig_heal VARCHAR2(30);
310 lv_elig_dep VARCHAR2(30);
311 lv_rowid ROWID;
312 ln_loan_key NUMBER;
313 lb_add_newkey BOOLEAN;
314 lv_loan_status VARCHAR2(30);
315 ld_loan_status_date DATE;
316 lv_loan_chg_status VARCHAR2(30);
317 ld_loan_chg_status_date DATE;
318
319
320 BEGIN
321
322 OPEN cur_setup(p_loan_rec.ci_cal_type,p_loan_rec.ci_sequence_number);
323 FETCH cur_setup INTO setup_rec;
324 CLOSE cur_setup;
325
326 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
327 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',setup_rec.response_option_code);
328 END IF;
329
330 OPEN cur_loan_oldinfo(p_loan_rec.loan_id);
331 FETCH cur_loan_oldinfo INTO loan_oldinfo_rec;
332 CLOSE cur_loan_oldinfo;
333
334 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
335
336 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','start of modified values for Loan ID> ' || loan_oldinfo_rec.loan_id);
337 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.b_chg_birth_date);
338 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.b_chg_last_name);
339 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.b_chg_ssn);
340 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.loan_number);
341 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.p_date_of_birth);
342 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.p_last_name);
343 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.p_ssn);
344 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.s_chg_birth_date);
345 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.s_chg_last_name);
346 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.s_chg_ssn);
347 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.s_date_of_birth);
348 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','** last name > ' || loan_oldinfo_rec.s_last_name);
349 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',loan_oldinfo_rec.s_ssn);
350 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',parent_dtl_rec.p_date_of_birth);
351 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',parent_dtl_rec.p_last_name);
352 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',parent_dtl_rec.p_ssn);
353 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',student_dtl_rec.p_date_of_birth);
354 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',student_dtl_rec.p_last_name);
355 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug',student_dtl_rec.p_ssn);
356 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','end of modified values');
357 END IF;
358
359
360 IF loan_oldinfo_rec.loan_number IS NOT NULL THEN
361 -- Check for identifier information change?
362 IF loan_oldinfo_rec.s_ssn <> student_dtl_rec.p_ssn THEN
363 loan_oldinfo_rec.s_chg_ssn := student_dtl_rec.p_ssn;
364 END IF;
365 IF loan_oldinfo_rec.s_date_of_birth <> student_dtl_rec.p_date_of_birth THEN
366 loan_oldinfo_rec.s_chg_birth_date := student_dtl_rec.p_date_of_birth;
367 END IF;
368 IF UPPER(loan_oldinfo_rec.s_last_name) <> UPPER(student_dtl_rec.p_last_name) THEN
369 loan_oldinfo_rec.s_chg_last_name := student_dtl_rec.p_last_name;
370
371 END IF;
372 IF p_loan_rec.fed_fund_code ='DLP' THEN
373 IF loan_oldinfo_rec.p_ssn <> parent_dtl_rec.p_ssn THEN
374 loan_oldinfo_rec.b_chg_ssn := parent_dtl_rec.p_ssn;
375 END IF;
376 IF loan_oldinfo_rec.p_date_of_birth <> parent_dtl_rec.p_date_of_birth THEN
377 loan_oldinfo_rec.b_chg_birth_date := parent_dtl_rec.p_date_of_birth;
378 END IF;
379 IF UPPER(loan_oldinfo_rec.p_last_name) <> UPPER(parent_dtl_rec.p_last_name) THEN
380 loan_oldinfo_rec.b_chg_last_name := parent_dtl_rec.p_last_name;
381 END IF;
382 END IF;
383 END IF;
384
385 --
386 -- Loan Key derivation
387 --
388 gn_old_base_id := gn_new_base_id;
389 gn_new_base_id := p_loan_rec.base_id;
390
391 IF gn_old_base_id = gn_new_base_id THEN
392 --
393 -- use same PL/SQL table data
394 --
395 NULL;
396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
397 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key determine');
398 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','gn_new_base_id ' || gn_new_base_id );
399 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','gn_old_base_id ' || gn_old_base_id );
400 END IF;
401 ELSE
402 --
403 -- re-initialize the PL/SQL table
404 --
405 loan_key_rec.DELETE;
406 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
407 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key determine');
408 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','gn_new_base_id ' || gn_new_base_id );
409 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','gn_old_base_id ' || gn_old_base_id );
410 END IF;
411 END IF;
412
413 ln_count := loan_key_rec.COUNT;
414
415 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
416 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key determine ln_count ' || ln_count);
417 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key gn_new_base_id = gn_old_base_id ');
418 END IF;
419
420 IF ln_count = 0 THEN
421 loan_key_rec.EXTEND;
422 ln_count := loan_key_rec.COUNT;
423 loan_key_rec(ln_count).orig_fee_pct_num := p_loan_rec.orig_fee_perct;
424 loan_key_rec(ln_count).int_reb_pct_num := setup_rec.int_rebate;
425 loan_key_rec(ln_count).pnote_print_code := p_loan_rec.pnote_print_ind;
426 loan_key_rec(ln_count).disclosure_print_code := p_loan_rec.disclosure_print_ind;
427 loan_key_rec(ln_count).grade_level_code := p_loan_rec.grade_level_code;
428 loan_key_rec(ln_count).fin_awd_begin_date := p_loan_rec.loan_per_begin_date;
429 loan_key_rec(ln_count).fin_awd_end_date := p_loan_rec.loan_per_end_date;
430 loan_key_rec(ln_count).acad_yr_begin_date := p_acad_begin;
431 loan_key_rec(ln_count).acad_yr_end_date := p_acad_end;
432 ln_loan_key := ln_count;
433 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
434 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key ' || ln_loan_key);
435 END IF;
436 END IF;
437
438 IF ln_count >= 1 THEN
439 -- check if the data is same, else insert new loan key
440 FOR i IN 1..ln_count LOOP
441 IF loan_key_rec(i).orig_fee_pct_num <> p_loan_rec.orig_fee_perct OR
442 loan_key_rec(i).int_reb_pct_num <> setup_rec.int_rebate OR
443 loan_key_rec(i).pnote_print_code <> p_loan_rec.pnote_print_ind OR
444 loan_key_rec(i).disclosure_print_code <> p_loan_rec.disclosure_print_ind OR
445 loan_key_rec(i).grade_level_code <> p_loan_rec.grade_level_code OR
446 loan_key_rec(i).fin_awd_begin_date <> p_loan_rec.loan_per_begin_date OR
447 loan_key_rec(i).fin_awd_end_date <> p_loan_rec.loan_per_end_date OR
448 loan_key_rec(i).acad_yr_begin_date <> p_acad_begin OR
449 loan_key_rec(i).acad_yr_end_date <> p_acad_end
450 THEN
451 lb_add_newkey := TRUE;
452 ELSE
453 lb_add_newkey := FALSE;
454 ln_loan_key := i;
455 EXIT;
456 END IF;
457 END LOOP;
458 IF lb_add_newkey THEN
459 loan_key_rec.EXTEND;
460 ln_loan_key := loan_key_rec.COUNT;
461 loan_key_rec(ln_loan_key).orig_fee_pct_num := p_loan_rec.orig_fee_perct;
462 loan_key_rec(ln_loan_key).int_reb_pct_num := setup_rec.int_rebate;
463 loan_key_rec(ln_loan_key).pnote_print_code := p_loan_rec.pnote_print_ind;
464 loan_key_rec(ln_loan_key).disclosure_print_code := p_loan_rec.disclosure_print_ind;
465 loan_key_rec(ln_loan_key).grade_level_code := p_loan_rec.grade_level_code;
466 loan_key_rec(ln_loan_key).fin_awd_begin_date := p_loan_rec.loan_per_begin_date;
467 loan_key_rec(ln_loan_key).fin_awd_end_date := p_loan_rec.loan_per_end_date;
468 loan_key_rec(ln_loan_key).acad_yr_begin_date := p_acad_begin;
469 loan_key_rec(ln_loan_key).acad_yr_end_date := p_acad_end;
470 END IF;
471 END IF;
472
473 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
474 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','loan key = ' || ln_loan_key);
475 END IF;
476
477 IF ln_loan_key > 99 THEN
478 fnd_message.set_name('IGF','IGF_SL_COD_99_KEYS');
479 fnd_message.set_token('LOAN_NUMBER',p_loan_rec.loan_number);
480 fnd_file.put_line(fnd_file.log, fnd_message.get);
481 RETURN;
482 END IF;
483
484 lv_rowid := NULL;
485
486 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','inserting lor loc ');
488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','inserting lor loc p_p_phone ' || p_p_phone);
489 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','inserting lor loc p_s_phone ' || p_s_phone);
490 END IF;
491 --
492 -- update change identifiers and new identifiers
493 --
494 IF p_loan_rec.unsub_elig_for_heal IS NULL THEN
495 lv_elig_heal := 'false';
496 ELSIF p_loan_rec.unsub_elig_for_heal = 'N' THEN
497 lv_elig_heal := 'false';
498 ELSIF p_loan_rec.unsub_elig_for_heal = 'Y' THEN
499 lv_elig_heal := 'true';
500 END IF;
501
502 IF p_loan_rec.unsub_elig_for_depnt IS NULL THEN
503 lv_elig_dep := 'false';
504 ELSIF p_loan_rec.unsub_elig_for_depnt = 'N' THEN
505 lv_elig_dep := 'false';
506 ELSIF p_loan_rec.unsub_elig_for_depnt = 'Y' THEN
507 lv_elig_dep := 'true';
508 END IF;
509
510 lv_loan_status := p_loan_rec.loan_status;
511 ld_loan_status_date := p_loan_rec.loan_status_date;
512 lv_loan_chg_status := p_loan_rec.loan_chg_status;
513 ld_loan_chg_status_date := p_loan_rec.loan_chg_status_date;
514
515 IF p_loan_rec.loan_status = 'A' THEN
516 lv_loan_chg_status := 'S';
517 ld_loan_chg_status_date := TRUNC(SYSDATE);
518 ELSIF p_loan_rec.loan_status = 'G' THEN
519 lv_loan_status := 'S';
520 ld_loan_status_date := TRUNC(SYSDATE);
521 END IF;
522
523 igf_sl_lor_loc_pkg.add_row(x_rowid => lv_rowid,
524 x_loan_id => p_loan_rec.loan_id,
525 x_origination_id => p_loan_rec.origination_id,
526 x_loan_number => p_loan_rec.loan_number,
527 x_loan_type => p_loan_rec.fed_fund_code,
528 x_loan_amt_offered => p_loan_rec.offered_amt,
529 x_loan_amt_accepted => p_loan_rec.accepted_amt,
530 x_loan_per_begin_date => p_loan_rec.loan_per_begin_date,
531 x_loan_per_end_date => p_loan_rec.loan_per_end_date,
532 x_acad_yr_begin_date => p_acad_begin,
533 x_acad_yr_end_date => p_acad_end,
534 x_loan_status => lv_loan_status,
535 x_loan_status_date => ld_loan_status_date,
536 x_loan_chg_status => lv_loan_chg_status,
537 x_loan_chg_status_date => ld_loan_chg_status_date,
538 x_req_serial_loan_code => NULL, -- FFELP
539 x_act_serial_loan_code => NULL, -- FFELP
540 x_active => p_loan_rec.active,
541 x_active_date => p_loan_rec.active_date,
542 x_sch_cert_date => NULL, -- FFELP
543 x_orig_status_flag => p_loan_rec.orig_status_flag,
544 x_orig_batch_id => p_loan_rec.orig_batch_id,
545 x_orig_batch_date => TRUNC(SYSDATE),
546 x_chg_batch_id => p_loan_rec.chg_batch_id,
547 x_orig_ack_date => p_loan_rec.orig_ack_date,
548 x_credit_override => p_loan_rec.credit_override,
549 x_credit_decision_date => p_loan_rec.credit_decision_date,
550 x_pnote_delivery_code => p_loan_rec.pnote_delivery_code,
551 x_pnote_status => p_loan_rec.pnote_status,
552 x_pnote_status_date => p_loan_rec.pnote_status_date,
553 x_pnote_id => p_loan_rec.pnote_id,
554 x_pnote_print_ind => p_loan_rec.pnote_print_ind,
555 x_pnote_accept_amt => p_loan_rec.pnote_accept_amt,
556 x_pnote_accept_date => p_loan_rec.pnote_accept_date,
557 x_p_signature_code => p_loan_rec.p_signature_code,
558 x_p_signature_date => p_loan_rec.p_signature_date,
559 x_s_signature_code => p_loan_rec.s_signature_code,
560 x_unsub_elig_for_heal => lv_elig_heal,
561 x_disclosure_print_ind => p_loan_rec.disclosure_print_ind,
562 x_orig_fee_perct => p_loan_rec.orig_fee_perct,
563 x_borw_confirm_ind => p_loan_rec.borw_confirm_ind,
564 x_borw_interest_ind => p_loan_rec.borw_interest_ind,
565 x_unsub_elig_for_depnt => lv_elig_dep,
566 x_guarantee_amt => NULL, -- FFELP
567 x_guarantee_date => NULL, -- FFELP
568 x_guarnt_adj_ind => NULL, -- FFELP
569 x_guarnt_amt_redn_code => NULL, -- FFELP
570 x_guarnt_status_code => NULL, -- FFELP
571 x_guarnt_status_date => NULL, -- FFELP
572 x_lend_apprv_denied_code => NULL, -- FFELP
573 x_lend_apprv_denied_date => NULL, -- FFELP
574 x_lend_status_code => NULL, -- FFELP
575 x_lend_status_date => NULL, -- FFELP
576 x_grade_level_code => p_loan_rec.grade_level_code,
577 x_enrollment_code => p_loan_rec.enrollment_code,
578 x_anticip_compl_date => NULL, -- FFELP
579 x_borw_lender_id => NULL, -- FFELP
580 x_duns_borw_lender_id => NULL, -- FFELP
581 x_guarantor_id => NULL, -- FFELP
582 x_duns_guarnt_id => NULL, -- FFELP
583 x_prc_type_code => NULL, -- FFELP
584 x_rec_type_ind => NULL, -- FFELP
585 x_cl_loan_type => NULL, -- FFELP
586 x_cl_seq_number => NULL, -- FFELP
587 x_last_resort_lender => NULL, -- FFELP
588 x_lender_id => NULL, -- FFELP
589 x_duns_lender_id => NULL, -- FFELP
590 x_lend_non_ed_brc_id => NULL, -- FFELP
591 x_recipient_id => NULL, -- FFELP
592 x_recipient_type => NULL, -- FFELP
593 x_duns_recip_id => NULL, -- FFELP
594 x_recip_non_ed_brc_id => NULL, -- FFELP
595 x_cl_rec_status => NULL, -- FFELP
596 x_cl_rec_status_last_update => NULL, -- FFELP
597 x_alt_prog_type_code => NULL, -- FFELP
598 x_alt_appl_ver_code => NULL, -- FFELP
599 x_borw_outstd_loan_code => NULL, -- FFELP
600 x_mpn_confirm_code => NULL, -- FFELP
601 x_resp_to_orig_code => NULL, -- FFELP
602 x_appl_loan_phase_code => NULL, -- FFELP
603 x_appl_loan_phase_code_chg => NULL, -- FFELP
604 x_tot_outstd_stafford => NULL, -- FFELP
605 x_tot_outstd_plus => NULL, -- FFELP
606 x_alt_borw_tot_debt => NULL, -- FFELP
607 x_act_interest_rate => NULL, -- FFELP
608 x_service_type_code => NULL, -- FFELP
609 x_rev_notice_of_guarnt => NULL, -- FFELP
610 x_sch_refund_amt => NULL, -- FFELP
611 x_sch_refund_date => NULL, -- FFELP
612 x_uniq_layout_vend_code => NULL, -- FFELP
613 x_uniq_layout_ident_code => NULL, -- FFELP
614 x_p_person_id => p_loan_rec.p_person_id,
615 x_p_ssn => NVL(loan_oldinfo_rec.p_ssn,parent_dtl_rec.p_ssn), -- attribute
616 x_p_ssn_chg_date => NULL, -- FFELP
617 x_p_last_name => UPPER(NVL(loan_oldinfo_rec.p_last_name,parent_dtl_rec.p_last_name)),-- attribute
618 x_p_first_name => UPPER(parent_dtl_rec.p_first_name),
619 x_p_middle_name => UPPER(parent_dtl_rec.p_middle_name),
620 x_p_permt_addr1 => UPPER(parent_dtl_rec.p_permt_addr1),
621 x_p_permt_addr2 => UPPER(parent_dtl_rec.p_permt_addr2),
622 x_p_permt_city => UPPER(parent_dtl_rec.p_permt_city),
623 x_p_permt_state => UPPER(parent_dtl_rec.p_permt_state),
624 x_p_permt_zip => UPPER(parent_dtl_rec.p_permt_zip),
625 x_p_permt_addr_chg_date => NULL, -- FFELP
626 x_p_permt_phone => p_p_phone,
627 x_p_email_addr => UPPER(parent_dtl_rec.p_email_addr),
628 x_p_date_of_birth => NVL(loan_oldinfo_rec.p_date_of_birth,parent_dtl_rec.p_date_of_birth), -- attribute
629 x_p_dob_chg_date => NULL, -- FFELP
630 x_p_license_num => parent_dtl_rec.p_license_num,
631 x_p_license_state => UPPER(parent_dtl_rec.p_license_state),
632 x_p_citizenship_status => parent_dtl_rec.p_citizenship_status,
633 x_p_alien_reg_num => NULL, -- FFELP
634 x_p_default_status => p_loan_rec.p_default_status,
635 x_p_foreign_postal_code => NULL, -- FFELP
636 x_p_state_of_legal_res => NULL, -- FFELP
637 x_p_legal_res_date => NULL, -- FFELP
638 x_s_ssn => NVL(loan_oldinfo_rec.s_ssn,student_dtl_rec.p_ssn), -- attribute
639 x_s_ssn_chg_date => NULL, -- FFELP
640 x_s_last_name => UPPER(NVL(loan_oldinfo_rec.s_last_name,student_dtl_rec.p_last_name)), -- attribute
641 x_s_first_name => UPPER(student_dtl_rec.p_first_name),
642 x_s_middle_name => UPPER(student_dtl_rec.p_middle_name),
643 x_s_permt_addr1 => UPPER(student_dtl_rec.p_permt_addr1),
644 x_s_permt_addr2 => UPPER(student_dtl_rec.p_permt_addr2),
645 x_s_permt_city => UPPER(student_dtl_rec.p_permt_city),
646 x_s_permt_state => UPPER(student_dtl_rec.p_permt_state),
647 x_s_permt_zip => UPPER(student_dtl_rec.p_permt_zip),
648 x_s_permt_addr_chg_date => NULL, -- FFELP
649 x_s_permt_phone => p_s_phone,
650 x_s_local_addr1 => NULL, -- Not Supported
651 x_s_local_addr2 => NULL, -- Not Supported
652 x_s_local_city => NULL, -- Not Supported
653 x_s_local_state => NULL, -- Not Supported
654 x_s_local_zip => NULL, -- Not Supported
655 x_s_local_addr_chg_date => NULL, -- Not Supported
656 x_s_email_addr => UPPER(student_dtl_rec.p_email_addr),
657 x_s_date_of_birth => NVL(loan_oldinfo_rec.s_date_of_birth,student_dtl_rec.p_date_of_birth), -- attribute
658 x_s_dob_chg_date => NULL, -- FFELP
659 x_s_license_num => UPPER(student_dtl_rec.p_license_num),
660 x_s_license_state => UPPER(student_dtl_rec.p_license_state),
661 x_s_depncy_status => p_isir_dep,
662 x_s_default_status => p_loan_rec.s_default_status,
663 x_s_citizenship_status => student_dtl_rec.p_citizenship_status,
664 x_s_alien_reg_num => NULL, -- FFELP
665 x_s_foreign_postal_code => NULL, -- FFELP
666 x_mode => 'R',
667 x_pnote_batch_id => p_loan_rec.pnote_batch_id,
668 x_pnote_ack_date => p_loan_rec.pnote_ack_date,
669 x_pnote_mpn_ind => p_loan_rec.pnote_mpn_ind,
670 x_award_id => p_loan_rec.award_id,
671 x_base_id => p_loan_rec.base_id,
672 x_document_id_txt => gv_document_id_txt,
673 x_loan_key_num => ln_loan_key,
674 x_interest_rebate_percent_num => setup_rec.int_rebate,
675 x_fin_award_year => SUBSTR(gv_dl_version,-4),
676 x_cps_trans_num => p_isir_tnum,
677 x_atd_entity_id_txt => p_loan_rec.atd_entity_id_txt,
678 x_rep_entity_id_txt => p_loan_rec.rep_entity_id_txt,
679 x_source_entity_id_txt => p_source_id,
680 x_pymt_servicer_amt => p_loan_rec.pymt_servicer_amt,
681 x_pymt_servicer_date => p_loan_rec.pymt_servicer_date,
682 x_book_loan_amt => p_loan_rec.book_loan_amt,
683 x_book_loan_amt_date => p_loan_rec.book_loan_amt_date,
684 x_s_chg_birth_date => loan_oldinfo_rec.s_chg_birth_date,
685 x_s_chg_ssn => loan_oldinfo_rec.s_chg_ssn,
686 x_s_chg_last_name => UPPER(loan_oldinfo_rec.s_chg_last_name),
687 x_b_chg_birth_date => loan_oldinfo_rec.b_chg_birth_date,
688 x_b_chg_ssn => loan_oldinfo_rec.b_chg_ssn,
689 x_b_chg_last_name => UPPER(loan_oldinfo_rec.b_chg_last_name),
690 x_note_message => p_loan_rec.note_message,
691 x_full_resp_code => NVL(setup_rec.response_option_code,'F'),
692 x_s_permt_county => UPPER(student_dtl_rec.p_county),
693 x_b_permt_county => UPPER(parent_dtl_rec.p_county),
694 x_s_permt_country => UPPER(student_dtl_rec.p_country),
695 x_b_permt_country => UPPER(parent_dtl_rec.p_country),
696 x_crdt_decision_status => p_loan_rec.crdt_decision_status,
697 x_mpn_type_flag => p_loan_rec.elec_mpn_ind,
698 x_alt_borrower_ind_flag => NULL,-- FFELP
699 x_borower_credit_authoriz_flag => NULL,-- FFELP
700 x_borower_electronic_sign_flag => NULL,-- FFELP
701 x_cost_of_attendance_amt => NULL,-- FFELP
702 x_deferment_request_code => NULL,-- FFELP
703 x_eft_authorization_code => NULL,-- FFELP
704 x_established_fin_aid_amount => NULL,-- FFELP
705 x_expect_family_contribute_amt => NULL,-- FFELP
706 x_external_loan_id_txt => NULL,-- FFELP
707 x_flp_approved_amt => NULL,-- FFELP
708 x_fls_approved_amt => NULL,-- FFELP
709 x_flu_approved_amt => NULL,-- FFELP
710 x_guarantor_use_txt => NULL,-- FFELP
711 x_lender_use_txt => NULL,-- FFELP
712 x_loan_app_form_code => NULL,-- FFELP
713 x_reinstatement_amt => NULL,-- FFELP
714 x_requested_loan_amt => NULL,-- FFELP
715 x_school_id_txt => NULL,-- FFELP
716 x_school_use_txt => NULL,-- FFELP
717 x_student_electronic_sign_flag => NULL,-- FFELP
718 x_actual_record_type_code => NULL,-- FFELP
719 x_alt_approved_amt => NULL,
720 x_esign_src_typ_cd => NULL
721 );-- FFELP
722
723 --
724 -- insert/update disbursement information
725 --
726 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
727 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','inserting cod db dtls');
728 END IF;
729
730 FOR rec IN cur_disb_rec (p_loan_rec.award_id)
731 LOOP
732 lv_rowid := NULL; -- pass atd entity id, rep entity id here
733 IF rec.disb_conf_flag IS NULL THEN
734 rec.disb_conf_flag := 'false';
735 END IF;
736 IF rec.disb_conf_flag = 'Y' THEN
737 rec.disb_conf_flag := 'false';
738 ELSIF rec.disb_conf_flag = 'N' THEN
739 rec.disb_conf_flag := 'true';
740 END IF;
741 igf_aw_db_cod_dtls_pkg.add_row(x_rowid => lv_rowid,
742 x_award_id => rec.award_id,
743 x_document_id_txt => gv_document_id_txt,
744 x_disb_num => rec.disb_num,
745 x_disb_seq_num => rec.disb_seq_num,
746 x_disb_accepted_amt => rec.disb_accepted_amt,
747 x_orig_fee_amt => rec.orig_fee_amt,
748 x_disb_net_amt => rec.disb_net_amt,
749 x_disb_date => rec.disb_date,
750 x_disb_rel_flag => LOWER(rec.disb_rel_flag),
751 x_first_disb_flag => rec.first_disb_flag,
752 x_interest_rebate_amt => rec.interest_rebate_amt,
753 x_disb_conf_flag => rec.disb_conf_flag,
754 x_pymnt_per_start_date => rec.pymnt_prd_start_date,
755 x_note_message => rec.note_message,
756 x_rep_entity_id_txt => p_loan_rec.rep_entity_id_txt,
757 x_atd_entity_id_txt => p_loan_rec.atd_entity_id_txt,
758 x_mode => 'R');
759
760 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
761 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','after inserting cod db dtls seq num, disb num, award id' || rec.disb_seq_num || ' , ' || rec.disb_num || ' , ' || rec.award_id);
762 END IF;
763 END LOOP;
764
765 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
766 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.insert_lor_loc.debug','after inserting cod db dtls');
767 END IF;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
772 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.log_parameters.exception','Exception:'||SQLERRM);
773 END IF;
774 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
775 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.LOG_PARAMETERS');
776 app_exception.raise_exception;
777
778 END insert_lor_loc;
779
780 PROCEDURE process_loan(p_loan_rec cur_pick_loans%ROWTYPE, p_source_id VARCHAR2)
781 IS
782
783 lb_valid_loan BOOLEAN;
784 lb_spoint_est BOOLEAN;
785
786 l_msg_name fnd_new_messages.message_name%TYPE;
787 l_aid NUMBER;
788 l_loan_tab igf_aw_packng_subfns.std_loan_tab := igf_aw_packng_subfns.std_loan_tab();
789
790 student_dtl_cur igf_sl_gen.person_dtl_cur;
791 parent_dtl_cur igf_sl_gen.person_dtl_cur;
792 student_dtl_rec igf_sl_gen.person_dtl_rec;
793 parent_dtl_rec igf_sl_gen.person_dtl_rec;
794
795 p_isir_ssn VARCHAR2(30);
796 p_isir_dob DATE;
797 p_isir_lname VARCHAR2(100);
798 p_isir_dep VARCHAR2(1);
799 p_isir_tnum NUMBER;
800 p_acad_begin DATE;
801 p_acad_end DATE;
802 p_s_phone VARCHAR2(30);
803 p_p_phone VARCHAR2(30);
804
805 CURSOR cur_isir_info (p_base_id NUMBER) IS
806 SELECT payment_isir,transaction_num,dependency_status,
807 date_of_birth,current_ssn,last_name
808 FROM igf_ap_isir_matched_all
809 WHERE base_id = p_base_id
810 AND payment_isir = 'Y'
811 AND system_record_type = 'ORIGINAL';
812
813 isir_info_rec cur_isir_info%ROWTYPE;
814
815 BEGIN
816
817 IF gv_document_id_txt IS NULL THEN
818 gv_document_id_txt := TO_CHAR(TRUNC(SYSDATE),'YYYY-MM-DD')||'T'||TO_CHAR(SYSDATE,'HH:MM:SS') || '.00' ||LPAD(p_source_id,8,'0');
819 END IF;
820 --
821 -- 4. validate loans
822 --
823
824 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
825 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.process_loan.debug','Calling validate Loan for Loan Number : ' || p_loan_rec.loan_number);
826 END IF;
827
828 lb_valid_loan := igf_sl_dl_validation.cod_loan_validations(p_loan_rec,'JOB',p_isir_ssn,
829 p_isir_dob,p_isir_lname,
830 p_isir_dep,p_isir_tnum,
831 p_acad_begin,p_acad_end,p_s_phone,p_p_phone);
832
833 IF lb_valid_loan THEN
834
835 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER')||' : '||p_loan_rec.loan_number);
836 fnd_file.new_line(fnd_file.log,1);
837 -- Check for Loan Amount
838 IF p_loan_rec.fed_fund_code IN ('DLS','DLU') THEN
839 l_aid := 0;
840 l_msg_name := NULL;
841 -- since the fund amount is already awarded to the student then l_aid is passed as 0.
842 igf_aw_packng_subfns.check_loan_limits (
843 p_loan_rec.base_id,
844 p_loan_rec.fed_fund_code,
845 p_loan_rec.award_id,
846 NULL,
847 l_aid,
848 l_loan_tab,
849 l_msg_name
850 );
851 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
852 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.process_loan.debug','The values returned from check_loan_limits l_aid : ' || l_aid);
853 END IF;
854 -- If the returned l_aid is 0 with no message returned or l_aid is greater than 0 then
855 -- the set up is fine otherwise show the corresponding error message in the log.
856 IF l_msg_name IS NOT NULL THEN
857 --Error has occured
858 IF l_aid = 0 THEN
859 fnd_message.set_name('IGF',l_msg_name);
860 fnd_file.put_line(fnd_file.log,fnd_message.get);
861 RETURN;
862 ELSIF l_aid < 0 THEN
863 fnd_message.set_name('IGF',l_msg_name);
864 fnd_message.set_token('FUND_CODE',p_loan_rec.fed_fund_code);
865 fnd_file.put_line(fnd_file.log,fnd_message.get);
866 END IF ;
867 END IF;
868 END IF;
869
870 igf_sl_gen.get_person_details(igf_gr_gen.get_person_id(p_loan_rec.base_id),student_dtl_cur);
871 FETCH student_dtl_cur INTO student_dtl_rec;
872 CLOSE student_dtl_cur;
873
874 IF p_loan_rec.fed_fund_code = 'DLP' THEN
875 igf_sl_gen.get_person_details(p_loan_rec.p_person_id,parent_dtl_cur);
876 FETCH parent_dtl_cur INTO parent_dtl_rec;
877 CLOSE parent_dtl_cur;
878 END IF;
879
880 -- 5. insert valid loans
881 --
882 lb_spoint_est := FALSE;
883 SAVEPOINT IGFSL25B_PROCESS_LOAN;
884 lb_spoint_est := TRUE;
885 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
886 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.process_loan.debug','inert lor loc for Loan Loan Number : ' || p_loan_rec.loan_number);
887 END IF;
888
889 -- Get ISIR Information
890 --
891 OPEN cur_isir_info (p_loan_rec.base_id);
892 FETCH cur_isir_info INTO isir_info_rec;
893 CLOSE cur_isir_info;
894
895
896 IF isir_info_rec.date_of_birth <> student_dtl_rec.p_date_of_birth OR
897 isir_info_rec.current_ssn <> student_dtl_rec.p_ssn OR
898 isir_info_rec.last_name <> UPPER(student_dtl_rec.p_last_name) THEN
899
900 --akomurav
901 IF isir_info_rec.date_of_birth <> student_dtl_rec.p_date_of_birth THEN
902
903 fnd_message.set_name('IGF','IGF_SL_DOB_MISMATCH');
904 fnd_message.set_token('P_DOB',to_char(student_dtl_rec.p_date_of_birth));
905 fnd_message.set_token('ISIR_DOB',to_char(isir_info_rec.date_of_birth));
906 fnd_file.put_line(fnd_file.log,' ' || fnd_message.get);
907 student_dtl_rec.p_date_of_birth := isir_info_rec.date_of_birth;
908
909 END IF;
910
911 IF isir_info_rec.current_ssn <> student_dtl_rec.p_ssn THEN
912
913 fnd_message.set_name('IGF','IGF_SL_SSN_MISMATCH');
914 fnd_message.set_token('P_SSN',student_dtl_rec.p_ssn);
915 fnd_message.set_token('ISIR_SSN',isir_info_rec.current_ssn);
916 fnd_file.put_line(fnd_file.log,' ' || fnd_message.get);
917 student_dtl_rec.p_ssn := isir_info_rec.current_ssn;
918
919 END IF;
920
921 IF isir_info_rec.last_name <> UPPER(student_dtl_rec.p_last_name) THEN
922
923 fnd_message.set_name('IGF','IGF_SL_NAME_MISMATCH');
924 fnd_message.set_token('P_LAST_NAME',UPPER(student_dtl_rec.p_last_name));
925 fnd_message.set_token('ISIR_NAME',UPPER(isir_info_rec.last_name));
926 fnd_file.put_line(fnd_file.log,' ' || fnd_message.get);
927 student_dtl_rec.p_last_name := UPPER(isir_info_rec.last_name);
928
929
930
931 END IF;
932
933 END IF;
934
935 --
936 -- do DML after this savepoint
937 -- insert / update UPPERCASE information only
938 insert_lor_loc(p_loan_rec,p_source_id,student_dtl_rec,parent_dtl_rec,
939 p_isir_ssn,p_isir_dob,p_isir_lname,p_isir_dep,p_isir_tnum,
940 p_acad_begin,p_acad_end,p_s_phone, p_p_phone);
941
942
943 ELSE
944 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
945 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.process_loan.debug',' Failed validations Loan Number : ' || p_loan_rec.loan_number);
946 END IF;
947 END IF; -- valid loan
948
949 EXCEPTION
950 WHEN OTHERS THEN
951 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
952 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.process_loan.exception','Exception:'||SQLERRM);
953 END IF;
954 fnd_message.set_name('IGF','IGF_SL_DL_XML_INSERT_EXC');
955 fnd_message.set_token('LOAN_NUMBER',p_loan_rec.loan_number);
956 fnd_file.put_line(fnd_file.log,fnd_message.get);
957 fnd_file.put_line(fnd_file.log,SQLERRM);
958 IF lb_spoint_est THEN
959 lb_spoint_est := FALSE;
960 ROLLBACK TO IGFSL25B_PROCESS_LOAN;
961 END IF;
962 END process_loan;
963
964 FUNCTION per_in_fa ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
965 p_ci_cal_type VARCHAR2,
966 p_ci_sequence_number NUMBER,
967 p_base_id OUT NOCOPY NUMBER
968 )
969 RETURN VARCHAR2
970 IS
971 CURSOR cur_get_pers_num ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
972 IS
973 SELECT person_number
974 FROM igs_pe_person_base_v
975 WHERE
976 person_id = p_person_id;
977
978 get_pers_num_rec cur_get_pers_num%ROWTYPE;
979
980 CURSOR cur_get_base (p_cal_type igs_ca_inst_all.cal_type%TYPE,
981 p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
982 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
983 IS
984 SELECT
985 base_id
986 FROM
987 igf_ap_fa_base_rec_all
988 WHERE
989 person_id = p_person_id AND
990 ci_cal_type = p_cal_type AND
991 ci_sequence_number = p_sequence_number;
992
993 BEGIN
994
995 OPEN cur_get_pers_num(p_person_id);
996 FETCH cur_get_pers_num INTO get_pers_num_rec;
997
998 IF cur_get_pers_num%NOTFOUND THEN
999 CLOSE cur_get_pers_num;
1000 RETURN NULL;
1001 ELSE
1002 CLOSE cur_get_pers_num;
1003 OPEN cur_get_base(p_ci_cal_type,p_ci_sequence_number,p_person_id);
1004 FETCH cur_get_base INTO p_base_id;
1005 CLOSE cur_get_base;
1006
1007 RETURN get_pers_num_rec.person_number;
1008
1009 END IF;
1010
1011 EXCEPTION
1012 WHEN OTHERS THEN
1013 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1014 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.PER_IN_FA');
1015 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1016 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.per_in_fa.exception','Exception:'||SQLERRM);
1017 END IF;
1018 igs_ge_msg_stack.add;
1019 app_exception.raise_exception;
1020 END per_in_fa;
1021
1022 PROCEDURE submit_xml_event (p_document_id_txt VARCHAR2)
1023 IS
1024
1025 l_parameter_list wf_parameter_list_t;
1026
1027 l_event_name VARCHAR2(255);
1028 l_event_key NUMBER;
1029 l_map_code VARCHAR2(255);
1030 l_param_1 VARCHAR2(255);
1031 lv_role fnd_user.user_name%TYPE;
1032
1033 CURSOR cur_sequence IS SELECT IGF_SL_DL_GEN_XML_S.NEXTVAL FROM DUAL;
1034
1035 BEGIN
1036
1037 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1038 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.submit_xml_event','p_document id: '||p_document_id_txt);
1039 END IF;
1040
1041 l_parameter_list := wf_parameter_list_t();
1042 l_event_name := 'oracle.apps.igf.sl.genxml';
1043 l_map_code := 'IGF_SL_DL_OUT';
1044 l_param_1 := p_document_id_txt;
1045
1046
1047 OPEN cur_sequence;
1048 FETCH cur_sequence INTO l_event_key;
1049 CLOSE cur_sequence;
1050
1051 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1052 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.submit_xml_event','l_event_key : '||l_event_key);
1053 END IF;
1054
1055 -- Now add the parameters to the list to be passed to the workflow
1056
1057 lv_role := fnd_global.user_name;
1058
1059 wf_event.addparametertolist(
1060 p_name => 'USER_ID',
1061 p_value => lv_role,
1062 p_parameterlist => l_parameter_list
1063 );
1064 wf_event.addparametertolist(
1065 p_name => 'EVENT_NAME',
1066 p_value => l_event_name,
1067 p_parameterlist => l_parameter_list
1068 );
1069 wf_event.addparametertolist(
1070 p_name => 'EVENT_KEY',
1071 p_value => l_event_key,
1072 p_parameterlist => l_parameter_list
1073 );
1074 wf_event.addparametertolist(
1075 p_name => 'ECX_MAP_CODE',
1076 p_value => l_map_code,
1077 p_parameterlist => l_parameter_list
1078 );
1079
1080 wf_event.addparametertolist(
1081 p_name => 'ECX_PARAMETER1',
1082 p_value => l_param_1,
1083 p_parameterlist => l_parameter_list
1084 );
1085
1086 wf_event.RAISE (
1087 p_event_name => l_event_name,
1088 p_event_key => l_event_key,
1089 p_parameters => l_parameter_list);
1090
1091
1092 fnd_message.set_name('IGF','IGF_SL_COD_RAISE_EVENT');
1093 fnd_message.set_token('EVENT_KEY_VALUE',l_event_key);
1094 fnd_file.new_line(fnd_file.log,1);
1095 fnd_file.put_line(fnd_file.log,fnd_message.get);
1096 fnd_file.new_line(fnd_file.log,1);
1097
1098 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1099 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.submit_xml_event','raised event ');
1100 END IF;
1101
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1105 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.submit_xml_event.exception','Exception:'||SQLERRM);
1106 END IF;
1107 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1108 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.SUBMIT_XML_EVENT');
1109 igs_ge_msg_stack.add;
1110 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1111 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.submit_xml_event.debug','sqlerrm ' || SQLERRM);
1112 END IF;
1113 app_exception.raise_exception;
1114 END submit_xml_event;
1115
1116 FUNCTION get_fund_desc(p_fund_id IN NUMBER)
1117 RETURN VARCHAR2 IS
1118 CURSOR cur_get_fund_desc (p_fund_id NUMBER)
1119 IS
1120 SELECT description FROM igf_aw_fund_mast_all
1121 WHERE fund_id = p_fund_id;
1122
1123 get_fund_desc_rec cur_get_fund_desc%ROWTYPE;
1124
1125 BEGIN
1126
1127 OPEN cur_get_fund_desc (p_fund_id);
1128 FETCH cur_get_fund_desc INTO get_fund_desc_rec;
1129 CLOSE cur_get_fund_desc;
1130
1131 RETURN get_fund_desc_rec.description;
1132
1133 END get_fund_desc;
1134
1135 FUNCTION get_loan_number(p_loan_id IN NUMBER)
1136 RETURN VARCHAR2 IS
1137 CURSOR cur_get_loan_number (p_loan_id NUMBER)
1138 IS
1139 SELECT loan_number FROM igf_sl_loans_all
1140 WHERE loan_id = p_loan_id;
1141
1142 get_loan_number_rec cur_get_loan_number%ROWTYPE;
1143
1144 BEGIN
1145
1146 OPEN cur_get_loan_number (p_loan_id);
1147 FETCH cur_get_loan_number INTO get_loan_number_rec;
1148 CLOSE cur_get_loan_number;
1149
1150 RETURN get_loan_number_rec.loan_number;
1151
1152 END get_loan_number;
1153
1154 FUNCTION get_grp_name(p_per_grp_id IN NUMBER)
1155 RETURN VARCHAR2 IS
1156
1157 CURSOR cur_get_grp_name (p_per_grp_id NUMBER)
1158 IS
1159 SELECT group_cd
1160 FROM igs_pe_persid_group_all
1161 WHERE group_id = p_per_grp_id;
1162
1163
1164 get_grp_name_rec cur_get_grp_name%ROWTYPE;
1165
1166 BEGIN
1167
1168 OPEN cur_get_grp_name (p_per_grp_id);
1169 FETCH cur_get_grp_name INTO get_grp_name_rec;
1170 CLOSE cur_get_grp_name;
1171
1172 RETURN get_grp_name_rec.group_cd;
1173
1174 END get_grp_name;
1175
1176 FUNCTION check_fa_rec(p_base_id NUMBER,
1177 p_cal_type VARCHAR2,
1178 p_seq_number NUMBER)
1179 RETURN BOOLEAN
1180 IS
1181 CURSOR cur_chk_fa (p_base_id NUMBER,
1182 p_cal_type VARCHAR2,
1183 p_seq_number NUMBER)
1184 IS
1185 SELECT base_id
1186 FROM igf_ap_fa_base_rec_all
1187 WHERE base_id = p_base_id AND
1188 ci_cal_type = p_cal_type AND
1189 ci_sequence_number = p_seq_number;
1190
1191 chk_fa_rec cur_chk_fa%ROWTYPE;
1192
1193 BEGIN
1194
1195 OPEN cur_chk_fa (p_base_id,p_cal_type,p_seq_number);
1196 FETCH cur_chk_fa INTO chk_fa_rec;
1197 CLOSE cur_chk_fa;
1198 IF chk_fa_rec.base_id IS NULL THEN
1199 RETURN FALSE;
1200 ELSE
1201 RETURN TRUE;
1202 END IF;
1203
1204 END check_fa_rec;
1205
1206 PROCEDURE log_parameters(p_cal_type VARCHAR2,
1207 p_seq_number NUMBER,
1208 p_source_id VARCHAR2,
1209 p_report_id VARCHAR2,
1210 p_attend_id VARCHAR2,
1211 p_fund_id NUMBER,
1212 p_base_id NUMBER,
1213 p_loan_id NUMBER,
1214 p_pgroup_id NUMBER)
1215 IS
1216 CURSOR c_get_parameters
1217 IS
1218 SELECT meaning, lookup_code
1219 FROM igf_lookups_view
1220 WHERE lookup_type = 'IGF_GE_PARAMETERS'
1221 AND lookup_code IN ('PARAMETER_PASS',
1222 'AWARD_YEAR',
1223 'SOURCE_ENTITY_ID', -- New
1224 'REPORT_ENTITY_ID', -- New
1225 'ATTEND_ENTITY_ID', -- New
1226 'LOAN_TYPE', -- New
1227 'PERSON_NUMBER',
1228 'LOAN_NUMBER', -- New
1229 'PERSON_ID_GROUP');
1230
1231 parameter_rec c_get_parameters%ROWTYPE;
1232
1233 CURSOR cur_get_loan_number (p_loan_id NUMBER)
1234 IS
1235 SELECT loan_number
1236 FROM igf_sl_loans_all
1237 WHERE loan_id = p_loan_id;
1238
1239 get_loan_number_rec cur_get_loan_number%ROWTYPE;
1240
1241 lv_parameter_pass VARCHAR2(80);
1242 lv_award_year VARCHAR2(80);
1243 lv_source_entity_id VARCHAR2(80);
1244 lv_report_entity_id VARCHAR2(80);
1245 lv_attend_entity_id VARCHAR2(80);
1246 lv_loan_type VARCHAR2(80);
1247 lv_person_number VARCHAR2(80);
1248 lv_loan_number VARCHAR2(80);
1249 lv_person_id_group VARCHAR2(80);
1250
1251 BEGIN
1252
1253 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1254 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.log_parameters.debug','In log parameters');
1255 END IF;
1256
1257 OPEN c_get_parameters;
1258 LOOP
1259 FETCH c_get_parameters INTO parameter_rec;
1260 EXIT WHEN c_get_parameters%NOTFOUND;
1261
1262 IF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
1263 lv_parameter_pass := TRIM(parameter_rec.meaning);
1264 ELSIF parameter_rec.lookup_code ='AWARD_YEAR' THEN
1265 lv_award_year := TRIM(parameter_rec.meaning);
1266 ELSIF parameter_rec.lookup_code ='SOURCE_ENTITY_ID' THEN
1267 lv_source_entity_id := TRIM(parameter_rec.meaning);
1268 ELSIF parameter_rec.lookup_code ='REPORT_ENTITY_ID' THEN
1269 lv_report_entity_id := TRIM(parameter_rec.meaning);
1270 ELSIF parameter_rec.lookup_code ='ATTEND_ENTITY_ID' THEN
1271 lv_attend_entity_id := TRIM(parameter_rec.meaning);
1272 ELSIF parameter_rec.lookup_code ='LOAN_TYPE' THEN
1273 lv_loan_type := TRIM(parameter_rec.meaning);
1274 ELSIF parameter_rec.lookup_code ='PERSON_NUMBER' THEN
1275 lv_person_number := TRIM(parameter_rec.meaning);
1276 ELSIF parameter_rec.lookup_code ='LOAN_NUMBER' THEN
1277 lv_loan_number := TRIM(parameter_rec.meaning);
1278 ELSIF parameter_rec.lookup_code ='PERSON_ID_GROUP' THEN
1279 lv_person_id_group := TRIM(parameter_rec.meaning);
1280 END IF;
1281 END LOOP;
1282 CLOSE c_get_parameters;
1283
1284 fnd_file.new_line(fnd_file.log,1);
1285 fnd_file.put_line(fnd_file.log, lv_parameter_pass); --------------Parameters Passed--------------
1286 fnd_file.new_line(fnd_file.log,1);
1287
1288 fnd_file.put_line(fnd_file.log, RPAD(lv_award_year,40) || ' : '|| igf_gr_gen.get_alt_code(p_cal_type,p_seq_number));
1289 fnd_file.put_line(fnd_file.log, RPAD(lv_source_entity_id,40) || ' : '|| p_source_id);
1290 fnd_file.put_line(fnd_file.log, RPAD(lv_report_entity_id,40) || ' : '|| p_report_id);
1291 fnd_file.put_line(fnd_file.log, RPAD(lv_attend_entity_id,40) || ' : '|| p_attend_id);
1292 fnd_file.put_line(fnd_file.log, RPAD(lv_loan_type,40) || ' : '|| get_fund_desc(p_fund_id));
1293 fnd_file.put_line(fnd_file.log, RPAD(lv_person_number,40) || ' : '|| igf_gr_gen.get_per_num(p_base_id));
1294 fnd_file.put_line(fnd_file.log, RPAD(lv_loan_number,40) || ' : '|| get_loan_number(p_loan_id));
1295 fnd_file.put_line(fnd_file.log, RPAD(lv_person_id_group,40) || ' : '|| get_grp_name(p_pgroup_id));
1296
1297 fnd_file.new_line(fnd_file.log,1);
1298 fnd_file.put_line(fnd_file.log, '--------------------------------------------------------');
1299 fnd_file.new_line(fnd_file.log,1);
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1304 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_sl_dl_gen_xml.log_parameters.exception','Exception:'||SQLERRM);
1305 END IF;
1306 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1307 fnd_message.set_token('NAME','IGF_SL_DL_GEN_XML.LOG_PARAMETERS');
1308 igs_ge_msg_stack.add;
1309 app_exception.raise_exception;
1310
1311 END log_parameters;
1312
1313 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
1314 retcode OUT NOCOPY NUMBER,
1315 p_award_year VARCHAR2,
1316 p_source_id VARCHAR2,
1317 p_report_id VARCHAR2,
1318 p_attend_id VARCHAR2,
1319 p_fund_id NUMBER,
1320 p_fund_dummy NUMBER,
1321 p_base_id NUMBER,
1322 p_base_dummy NUMBER,
1323 p_loan_id NUMBER,
1324 p_loan_dummy NUMBER,
1325 p_pgroup_id NUMBER)
1326 IS
1327 /* -----------------------------------------------------------------------------------
1328 Know limitations, enhancements or remarks
1329 Change History:
1330 -----------------------------------------------------------------------------------
1331 Who When What
1332 ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1333 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1334 -----------------------------------------------------------------------------------
1335 */
1336
1337
1338 CURSOR cur_cod_dtls (p_document_id_txt VARCHAR2)
1339 IS
1340 SELECT document_id_txt
1341 FROM igf_sl_lor_loc_all
1342 WHERE document_id_txt = p_document_id_txt;
1343
1344 cod_dtls_rec cur_cod_dtls%ROWTYPE;
1345
1346 CURSOR cur_award_year (p_cal_type VARCHAR2,
1347 p_seq_number NUMBER)
1348 IS
1349 SELECT award_year_status_code,dl_participant_code, sys_award_year
1350 FROM igf_ap_batch_aw_map_all
1351 WHERE ci_sequence_number = p_seq_number AND ci_cal_type = p_cal_type;
1352
1353 award_year_rec cur_award_year%ROWTYPE;
1354
1355 CURSOR cur_source_id (p_source_id VARCHAR2,
1356 p_cal_type VARCHAR2,
1357 p_seq_number NUMBER)
1358 IS
1359 SELECT
1360 rep.rep_entity_id_txt
1361 FROM
1362 igf_gr_report_pell rep
1363 WHERE
1364 rep.rep_entity_id_txt = p_source_id AND
1365 rep.ci_cal_type = p_cal_type AND
1366 rep.ci_sequence_number = p_seq_number;
1367
1368 source_id_rec cur_source_id%ROWTYPE;
1369
1370 CURSOR cur_report_id (p_report_id VARCHAR2,
1371 p_cal_type VARCHAR2,
1372 p_seq_number NUMBER)
1373 IS
1374 SELECT
1375 lor.loan_id
1376 FROM
1377 igf_sl_lor_all lor,
1378 igf_sl_loans_all loan,
1379 igf_aw_award_all awd,
1380 igf_aw_fund_mast_all fmast
1381 WHERE
1382 loan.award_id = awd.award_id AND
1383 awd.fund_id = fmast.fund_id AND
1384 fmast.ci_sequence_number = p_seq_number AND
1385 fmast.ci_cal_type = p_cal_type AND
1386 lor.loan_id = loan.loan_id AND
1387 lor.rep_entity_id_txt = p_report_id;
1388
1389 report_id_rec cur_report_id%ROWTYPE;
1390
1391
1392 CURSOR cur_attend_id (p_report_id VARCHAR2,
1393 p_attend_id VARCHAR2,
1394 p_cal_type VARCHAR2,
1395 p_seq_number NUMBER)
1396 IS
1397 SELECT
1398 lor.loan_id
1399 FROM
1400 igf_sl_lor_all lor,
1401 igf_sl_loans_all loan,
1402 igf_aw_award_all awd,
1403 igf_aw_fund_mast_all fmast
1404 WHERE
1405 loan.award_id = awd.award_id AND
1406 awd.fund_id = fmast.fund_id AND
1407 fmast.ci_sequence_number = p_seq_number AND
1408 fmast.ci_cal_type = p_cal_type AND
1409 lor.loan_id = loan.loan_id AND
1410 lor.rep_entity_id_txt = p_report_id AND
1411 lor.atd_entity_id_txt = p_attend_id;
1412
1413 attend_id_rec cur_attend_id%ROWTYPE;
1414
1415
1416 CURSOR cur_chk_loan (p_base_id NUMBER)
1417 IS
1418 SELECT loan.loan_id
1419 FROM
1420 igf_sl_loans_all loan,
1421 igf_aw_award_all awd
1422 WHERE
1423 awd.award_id = loan.award_id AND
1424 awd.base_id = p_base_id AND
1425 (
1426 loan.loan_status = 'G' OR
1427 loan.loan_chg_status = 'G'
1428 );
1429
1430 chk_loan_rec cur_chk_loan%ROWTYPE;
1431
1432
1433 CURSOR cur_chk_pidgroup (p_pgroup_id NUMBER)
1434 IS
1435 SELECT group_id
1436 FROM igs_pe_persid_group_all
1437 WHERE
1438 group_id = p_pgroup_id AND
1439 closed_ind = 'N';
1440
1441 chk_pidgroup_rec cur_chk_pidgroup%ROWTYPE;
1442
1443 lv_cal_type VARCHAR2(30);
1444 ln_seq_number NUMBER;
1445 lv_person_number hz_parties.party_number%TYPE;
1446
1447 lb_record_exist BOOLEAN;
1448
1449 l_list VARCHAR2(32767);
1450 lv_status VARCHAR2(1);
1451 TYPE cur_person_id_type IS REF CURSOR;
1452 cur_per_grp cur_person_id_type;
1453
1454 l_person_id hz_parties.party_id%TYPE;
1455 ln_base_id NUMBER;
1456 ln_top NUMBER;
1457 ln_sm NUMBER;
1458 ln_rs NUMBER;
1459 ln_rp NUMBER;
1460 ln_as NUMBER;
1461 ln_st NUMBER;
1462 ln_db NUMBER;
1463 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1464
1465 BEGIN
1466
1467 --
1468 -- Steps
1469 -- 1. Print parameters
1470 -- 2. Validate parameters
1471 -- 3. Find Loans to be processed
1472 -- 4. Validate Loans
1473 -- 5. Insert valid loan records into LOR_LOC, and disb records into _DB_LOC
1474 -- 6. Raise Business Event
1475 --
1476 igf_aw_gen.set_org_id(NULL);
1477 lv_cal_type := RTRIM(SUBSTR(p_award_year,1,10));
1478 ln_seq_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
1479 ln_count := 0;
1480 loan_key_rec := loan_key_list();
1481 loan_key_rec.DELETE;
1482 gn_old_base_id := -1;
1483 gn_new_base_id := 0;
1484
1485 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1486 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_award_year: '||p_award_year);
1487 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','award cal_type : ' || lv_cal_type);
1488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','award ci_seq_num : ' || ln_seq_number);
1489 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_source_id: '||p_source_id);
1490 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_report_id:'||p_report_id);
1491 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_attend_id: '||p_attend_id);
1492 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_fund_id: '||p_fund_id);
1493 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_fund_dummy: '||p_fund_dummy);
1494 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_base_id: '||p_base_id);
1495 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_base_dummy: '||p_base_dummy);
1496 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_loan_id: '||p_loan_id);
1497 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_loan_dummy: '||p_loan_dummy);
1498 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','p_pgroup_id: '||p_pgroup_id);
1499 END IF;
1500
1501 -- 1. Print parameters
1502 log_parameters(lv_cal_type,ln_seq_number,
1503 TRIM(p_source_id),
1504 TRIM(p_report_id),
1505 TRIM(p_attend_id),
1506 p_fund_id,
1507 p_base_id,
1508 p_loan_id,
1509 p_pgroup_id);
1510
1511 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1512 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','after log parameters');
1513 END IF;
1514
1515 -- 2. Validate parameters
1516 IF p_award_year IS NULL OR lv_cal_type IS NULL OR ln_seq_number IS NULL THEN
1517 fnd_message.set_name('IGF','IGF_SL_COD_REQ_PARAM');
1518 fnd_message.set_token('PARAM',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','AWARD_YEAR'));
1519 fnd_file.put_line(fnd_file.log, fnd_message.get);
1520 fnd_file.new_line(fnd_file.log, 1);
1521 RETURN;
1522 END IF;
1523
1524 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1525 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','award year is not null');
1526 END IF;
1527
1528 IF TRIM(p_source_id) IS NULL THEN
1529 fnd_message.set_name('IGF','IGF_SL_COD_REQ_PARAM');
1530 fnd_message.set_token('PARAM',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','SOURCE_ENTITY_ID'));
1531 fnd_file.put_line(fnd_file.log, fnd_message.get);
1532 fnd_file.new_line(fnd_file.log, 1);
1533 RETURN;
1534 END IF;
1535
1536 IF LENGTH(TRIM(p_source_id)) > 8 OR TRIM(p_source_id) > 99999999
1537 OR NOT igf_sl_dl_validation.validate_id(p_source_id) THEN
1538 fnd_message.set_name('IGF','IGF_SL_COD_INVL_SOURCE_ID');
1539 fnd_file.put_line(fnd_file.log, fnd_message.get);
1540 fnd_file.new_line(fnd_file.log, 1);
1541 RETURN;
1542 END IF;
1543
1544 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1545 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','source id is not null');
1546 END IF;
1547
1548 IF p_pgroup_id IS NOT NULL AND p_base_id IS NOT NULL THEN
1549 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
1550 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
1551 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_NUMBER'));
1552 fnd_file.put_line(fnd_file.log, fnd_message.get);
1553 fnd_file.new_line(fnd_file.log, 1);
1554 RETURN;
1555 END IF;
1556
1557 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1558 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','base id and pgroup id check');
1559 END IF;
1560
1561 IF p_pgroup_id IS NOT NULL AND p_loan_id IS NOT NULL THEN
1562 fnd_message.set_name('IGF','IGF_SL_COD_INV_PARAM');
1563 fnd_message.set_token('PARAM1',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'));
1564 fnd_message.set_token('PARAM2',igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','LOAN_NUMBER'));
1565 fnd_file.put_line(fnd_file.log, fnd_message.get);
1566 fnd_file.new_line(fnd_file.log, 1);
1567 RETURN;
1568 END IF;
1569
1570 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1571 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','loan id and pgroup id check');
1572 END IF;
1573
1574 IF p_attend_id IS NOT NULL AND p_report_id IS NULL THEN
1575 fnd_message.set_name('IGF','IGF_SL_COD_INV_ATD_PARAM');
1576 fnd_file.put_line(fnd_file.log, fnd_message.get);
1577 fnd_file.new_line(fnd_file.log, 1);
1578 RETURN;
1579 END IF;
1580
1581 OPEN cur_award_year(lv_cal_type,ln_seq_number);
1582 FETCH cur_award_year INTO award_year_rec;
1583 CLOSE cur_award_year;
1584
1585 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1586 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','loan id and pgroup id check 1');
1587 END IF;
1588
1589 IF award_year_rec.sys_award_year IS NULL OR
1590 award_year_rec.dl_participant_code IS NULL OR
1591 award_year_rec.award_year_status_code IS NULL
1592 THEN
1593 fnd_message.set_name('IGF','IGF_SL_COD_INV_AWD_YR');
1594 fnd_file.put_line(fnd_file.log, fnd_message.get);
1595 fnd_file.new_line(fnd_file.log, 1);
1596 RETURN;
1597 END IF;
1598
1599 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1600 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','batch year not empty');
1601 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','sys award year ' || award_year_rec.sys_award_year);
1602 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','dl participant code ' || award_year_rec.dl_participant_code);
1603 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','award year status code ' || award_year_rec.award_year_status_code);
1604 END IF;
1605
1606 IF award_year_rec.award_year_status_code <> 'O' THEN
1607 fnd_message.set_name('IGF','IGF_SL_COD_AWDYR_OPEN');
1608 fnd_file.put_line(fnd_file.log, fnd_message.get);
1609 fnd_file.new_line(fnd_file.log, 1);
1610 RETURN;
1611 END IF;
1612
1613 IF award_year_rec.dl_participant_code <> 'FULL_PARTICIPANT' THEN
1614 fnd_message.set_name('IGF','IGF_SL_COD_AWDYR_FULL');
1615 fnd_file.put_line(fnd_file.log, fnd_message.get);
1616 fnd_file.new_line(fnd_file.log, 1);
1617 RETURN;
1618 END IF;
1619
1620 IF award_year_rec.sys_award_year < '0405' THEN
1621 fnd_message.set_name('IGF','IGF_SL_COD_XML_SUPPORT');
1622 fnd_file.put_line(fnd_file.log, fnd_message.get);
1623 fnd_file.new_line(fnd_file.log, 1);
1624 RETURN;
1625 END IF;
1626
1627 OPEN cur_source_id(p_source_id,lv_cal_type,ln_seq_number);
1628 FETCH cur_source_id INTO source_id_rec;
1629 CLOSE cur_source_id;
1630
1631 IF source_id_rec.rep_entity_id_txt IS NULL THEN
1632 fnd_message.set_name('IGF','IGF_SL_COD_INV_SRC_ID');
1633 fnd_file.put_line(fnd_file.log, fnd_message.get);
1634 fnd_file.new_line(fnd_file.log, 1);
1635 RETURN;
1636 END IF;
1637
1638 IF p_report_id IS NOT NULL THEN
1639
1640 OPEN cur_report_id(p_report_id,lv_cal_type,ln_seq_number);
1641 FETCH cur_report_id INTO report_id_rec;
1642 CLOSE cur_report_id;
1643
1644 IF report_id_rec.loan_id IS NULL THEN
1645 fnd_message.set_name('IGF','IGF_SL_COD_INV_REP_ID');
1646 fnd_message.set_token('REPORTING_ID',p_report_id);
1647 fnd_file.put_line(fnd_file.log, fnd_message.get);
1648 fnd_file.new_line(fnd_file.log, 1);
1649 RETURN;
1650 END IF;
1651
1652 IF p_attend_id IS NOT NULL THEN
1653 OPEN cur_attend_id(p_report_id,p_attend_id,lv_cal_type,ln_seq_number);
1654 FETCH cur_attend_id INTO attend_id_rec;
1655 CLOSE cur_attend_id;
1656
1657 IF attend_id_rec.loan_id IS NULL THEN
1658 fnd_message.set_name('IGF','IGF_SL_COD_INV_ATD_ID');
1659 fnd_message.set_token('REPORTING_ID',p_report_id);
1660 fnd_message.set_token('ATTENDING_ID',p_attend_id);
1661 fnd_file.put_line(fnd_file.log, fnd_message.get);
1662 fnd_file.new_line(fnd_file.log, 1);
1663 RETURN;
1664 END IF;
1665 END IF;
1666
1667 END IF;
1668
1669 IF p_base_id IS NOT NULL AND
1670 ( igf_gr_gen.get_per_num(p_base_id) IS NULL OR
1671 NOT check_fa_rec(p_base_id, lv_cal_type, ln_seq_number))
1672 THEN
1673 fnd_message.set_name('IGF','IGF_SP_NO_FA_BASE_REC');
1674 fnd_file.put_line(fnd_file.log, fnd_message.get);
1675 fnd_file.new_line(fnd_file.log, 1);
1676 RETURN;
1677 END IF;
1678
1679 IF p_base_id IS NOT NULL THEN
1680 OPEN cur_chk_loan(p_base_id);
1681 FETCH cur_chk_loan INTO chk_loan_rec;
1682 CLOSE cur_chk_loan;
1683 IF chk_loan_rec.loan_id IS NULL THEN
1684 fnd_message.set_name('IGF','IGF_SL_COD_NO_ORIG_REC');
1685 fnd_message.set_token('PERSON_NUMBER', igf_gr_gen.get_per_num(p_base_id));
1686 fnd_file.put_line(fnd_file.log, fnd_message.get);
1687 fnd_file.new_line(fnd_file.log, 1);
1688 RETURN;
1689 END IF;
1690 END IF;
1691
1692 IF p_pgroup_id IS NOT NULL THEN
1693 OPEN cur_chk_pidgroup (p_pgroup_id);
1694 FETCH cur_chk_pidgroup INTO chk_pidgroup_rec;
1695 CLOSE cur_chk_pidgroup;
1696 IF chk_pidgroup_rec.group_id IS NULL THEN
1697 fnd_message.set_name('IGF','IGF_SL_COD_PERSID_GRP_INV');
1698 fnd_file.put_line(fnd_file.log, fnd_message.get);
1699 fnd_file.new_line(fnd_file.log, 1);
1700 RETURN;
1701 END IF;
1702 END IF;
1703
1704 -- End of validations
1705
1706 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1707 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','parameter validation successful');
1708 END IF;
1709 --
1710 -- 3. Find Loans to be processed
1711 --
1712 gv_dl_version := igf_sl_gen.get_dl_version(lv_cal_type, ln_seq_number);
1713
1714 lb_record_exist := FALSE;
1715
1716 IF p_base_id IS NOT NULL THEN
1717 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1718 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(p_base_id));
1719 fnd_file.put_line(fnd_file.log, fnd_message.get);
1720 FOR rec IN cur_pick_loans (lv_cal_type,ln_seq_number,
1721 p_base_id,p_report_id,p_attend_id,
1722 p_fund_id,p_loan_id)
1723 LOOP
1724 rec.grade_level_code := NVL(rec.override_grade_level_code,rec.grade_level_code);
1725 process_loan(rec,p_source_id);
1726 IF NOT lb_record_exist THEN
1727 lb_record_exist := TRUE;
1728 END IF;
1729 END LOOP;
1730 IF NOT lb_record_exist THEN
1731 fnd_file.new_line(fnd_file.log, 1);
1732 fnd_message.set_name('IGF','IGF_SL_NO_LOR_XML_REC');
1733 fnd_file.put_line(fnd_file.log, fnd_message.get);
1734 fnd_file.new_line(fnd_file.log, 1);
1735 RETURN;
1736 END IF;
1737 END IF;
1738
1739 IF p_pgroup_id IS NOT NULL THEN
1740 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
1741 fnd_message.set_token('P_PER_GRP',get_grp_name(p_pgroup_id));
1742 fnd_file.new_line(fnd_file.log, 1);
1743 fnd_file.put_line(fnd_file.log, fnd_message.get);
1744
1745 --Bug #5021084
1746 l_list := igf_ap_ss_pkg.get_pid(p_pgroup_id,lv_status,lv_group_type);
1747
1748 --Bug #5021084. Passing Group ID if the group type is STATIC.
1749 IF lv_group_type = 'STATIC' THEN
1750 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_pgroup_id;
1751 ELSIF lv_group_type = 'DYNAMIC' THEN
1752 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
1753 END IF;
1754
1755 FETCH cur_per_grp INTO l_person_id;
1756
1757 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1758 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','Starting to process person group '||p_pgroup_id);
1759 END IF;
1760
1761 IF cur_per_grp%NOTFOUND THEN
1762 CLOSE cur_per_grp;
1763 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
1764 fnd_file.put_line(fnd_file.log,fnd_message.get);
1765 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1766 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','No persons in group '||p_pgroup_id);
1767 END IF;
1768 ELSE
1769 IF cur_per_grp%FOUND THEN -- Check if the person exists in FA.
1770 lb_record_exist := FALSE;
1771 LOOP
1772 ln_base_id := 0;
1773 lv_person_number := NULL;
1774 lv_person_number := per_in_fa (l_person_id,lv_cal_type,ln_seq_number,ln_base_id);
1775 IF lv_person_number IS NOT NULL THEN
1776 IF ln_base_id IS NOT NULL THEN
1777 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1778 fnd_message.set_token('STDNT',lv_person_number);
1779 fnd_file.put_line(fnd_file.log, fnd_message.get);
1780 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1781 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','PIDG base id ' || ln_base_id);
1782 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','PIDG lv_person_number ' || lv_person_number);
1783 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','PIDG l_person_id ' || l_person_id);
1784 END IF;
1785 FOR rec IN cur_pick_loans (lv_cal_type,ln_seq_number,
1786 ln_base_id,p_report_id,p_attend_id,
1787 p_fund_id,p_loan_id)
1788 LOOP
1789 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1790 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','Processing PIDG base id ' || ln_base_id);
1791 END IF;
1792 rec.grade_level_code := NVL(rec.override_grade_level_code,rec.grade_level_code);
1793 process_loan(rec,p_source_id);
1794 IF NOT lb_record_exist THEN
1795 lb_record_exist := TRUE;
1796 END IF;
1797 END LOOP;
1798 ELSE -- log a message and skip this person, base id not found
1799 fnd_message.set_name('IGF','IGF_GR_LI_PER_INVALID');
1800 fnd_message.set_token('PERSON_NUMBER',lv_person_number);
1801 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(lv_cal_type,ln_seq_number));
1802 fnd_file.put_line(fnd_file.log,fnd_message.get);
1803 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1804 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug',igf_gr_gen.get_per_num_oss(l_person_id) || ' not in FA');
1805 END IF;
1806 END IF; -- base id not found
1807 ELSE
1808 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
1809 fnd_file.put_line(fnd_file.log,RPAD(' ',5) ||fnd_message.get);
1810 END IF; -- person number not null
1811
1812 FETCH cur_per_grp INTO l_person_id;
1813 EXIT WHEN cur_per_grp%NOTFOUND;
1814 END LOOP;
1815 IF NOT lb_record_exist THEN
1816 fnd_file.new_line(fnd_file.log, 1);
1817 fnd_message.set_name('IGF','IGF_SL_NO_LOR_XML_REC');
1818 fnd_file.put_line(fnd_file.log, fnd_message.get);
1819 fnd_file.new_line(fnd_file.log, 1);
1820 CLOSE cur_per_grp;
1821 RETURN;
1822 END IF;
1823 CLOSE cur_per_grp;
1824 END IF; -- group found
1825 END IF; -- group not found
1826 END IF; -- pid group is not null
1827
1828 -- base id or person group id is not given, so process records for given
1829 -- input combination
1830 IF p_base_id IS NULL AND p_pgroup_id IS NULL THEN
1831 lb_record_exist := FALSE;
1832 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1833 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug',' processing for other combo');
1834 END IF;
1835 FOR rec IN cur_pick_loans (lv_cal_type,ln_seq_number,
1836 p_base_id,p_report_id,p_attend_id,
1837 p_fund_id,p_loan_id)
1838 LOOP
1839 rec.grade_level_code := NVL(rec.override_grade_level_code,rec.grade_level_code);
1840 process_loan(rec,p_source_id);
1841 IF NOT lb_record_exist THEN
1842 lb_record_exist := TRUE;
1843 END IF;
1844 END LOOP;
1845 IF NOT lb_record_exist THEN
1846 fnd_file.new_line(fnd_file.log, 1);
1847 fnd_message.set_name('IGF','IGF_SL_NO_LOR_XML_REC');
1848 fnd_file.put_line(fnd_file.log, fnd_message.get);
1849 fnd_file.new_line(fnd_file.log, 1);
1850 RETURN;
1851 END IF;
1852 END IF;
1853 --
1854 -- End of Step 3
1855 --
1856
1857 -- 6. Submit Business Event, only if there are records to be put
1858 OPEN cur_cod_dtls(gv_document_id_txt);
1859 FETCH cur_cod_dtls INTO cod_dtls_rec;
1860 CLOSE cur_cod_dtls;
1861
1862 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1863 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug',' gv_document_id_txt ' || gv_document_id_txt);
1864 END IF;
1865
1866 IF cod_dtls_rec.document_id_txt IS NULL THEN
1867 fnd_message.set_name('IGF','IGF_SL_COD_NO_DL_REC');
1868 fnd_file.new_line(fnd_file.log, 1);
1869 fnd_file.put_line(fnd_file.log,fnd_message.get);
1870 fnd_file.new_line(fnd_file.log, 1);
1871 RETURN;
1872 ELSE
1873 SELECT COUNT(*) INTO ln_top FROM igf_sl_cod_top_v WHERE document_id_txt = gv_document_id_txt;
1874 SELECT COUNT(*) INTO ln_sm FROM igf_sl_rep_smry_v WHERE document_id_txt = gv_document_id_txt;
1875 SELECT COUNT(*) INTO ln_rs FROM igf_sl_rep_rs_v WHERE document_id_txt = gv_document_id_txt;
1876 SELECT COUNT(*) INTO ln_rp FROM igf_sl_cod_rep_v WHERE document_id_txt = gv_document_id_txt;
1877 SELECT COUNT(*) INTO ln_as FROM igf_sl_rep_as_v WHERE document_id_txt = gv_document_id_txt;
1878 SELECT COUNT(*) INTO ln_st FROM igf_sl_rep_stdnt_v WHERE document_id_txt = gv_document_id_txt;
1879 SELECT COUNT(*) INTO ln_db FROM igf_sl_db_cod_rep_v WHERE document_id_txt = gv_document_id_txt;
1880 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1881 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_top ' ||ln_top);
1882 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_sm ' ||ln_sm );
1883 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_rs ' ||ln_rs );
1884 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_rp ' ||ln_rp );
1885 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_as ' ||ln_as );
1886 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_st ' ||ln_st );
1887 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug','ln_db ' ||ln_db );
1888 END IF;
1889 IF ln_top = 0 OR ln_sm = 0 OR ln_rs = 0 OR ln_rp = 0 OR ln_as = 0 OR ln_st = 0 THEN
1890 fnd_message.set_name('IGF','IGF_SL_COD_NO_DL_REC');
1891 fnd_file.put_line(fnd_file.log,fnd_message.get);
1892 RETURN;
1893 ELSE
1894 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1895 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug',' before submit event ');
1896 END IF;
1897 submit_xml_event (gv_document_id_txt);
1898 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1899 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.main.debug',' after submit event ');
1900 END IF;
1901 END IF;
1902 END IF;
1903
1904 COMMIT;
1905 EXCEPTION
1906 WHEN OTHERS THEN
1907 ROLLBACK;
1908 retcode := 2;
1909 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1910 fnd_file.put_line(fnd_file.log,SQLERRM);
1911 igs_ge_msg_stack.conc_exception_hndl;
1912 END main;
1913 /* -----------------------------------------------------------------------------------
1914 Know limitations, enhancements or remarks
1915 Change History:
1916 -----------------------------------------------------------------------------------
1917 Who When What
1918 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1919 -----------------------------------------------------------------------------------
1920 */
1921 PROCEDURE print_xml(errbuf OUT NOCOPY VARCHAR2,
1922 retcode OUT NOCOPY NUMBER,
1923 p_document_id_txt VARCHAR2)
1924 IS
1925
1926 CURSOR c_get_parameters
1927 IS
1928 SELECT meaning, lookup_code
1929 FROM igf_lookups_view
1930 WHERE lookup_type = 'IGF_GE_PARAMETERS'
1931 AND lookup_code IN ('PARAMETER_PASS',
1932 'DOCUMENT_ID');
1933
1934 parameter_rec c_get_parameters%ROWTYPE;
1935
1936 lv_parameter_pass VARCHAR2(80);
1937 lv_document_id_txt VARCHAR2(80);
1938 lc_newxmldoc CLOB;
1939 lv_rowid ROWID;
1940
1941 BEGIN
1942 igf_aw_gen.set_org_id(NULL);
1943 --
1944 -- Steps
1945 --
1946 -- 1. Print parameters
1947 -- 2. Validate parameters
1948 -- 3. Edit CLOB for additional tags
1949 -- 4. Update DOC_DTLS table
1950 -- 5. Update LOR_LOC table, DISB table for Status
1951 -- 5. Print CLOB on the output file
1952 --
1953
1954 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1955 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_xml.debug','p doc id ' || p_document_id_txt);
1956 END IF;
1957
1958 OPEN c_get_parameters;
1959 LOOP
1960 FETCH c_get_parameters INTO parameter_rec;
1961 EXIT WHEN c_get_parameters%NOTFOUND;
1962
1963 IF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
1964 lv_parameter_pass := TRIM(parameter_rec.meaning);
1965 ELSIF parameter_rec.lookup_code ='DOCUMENT_ID' THEN
1966 lv_document_id_txt := TRIM(parameter_rec.meaning);
1967 END IF;
1968 END LOOP;
1969 CLOSE c_get_parameters;
1970
1971 fnd_file.new_line(fnd_file.log,1);
1972 fnd_file.put_line(fnd_file.log, lv_parameter_pass); --------------Parameters Passed--------------
1973 fnd_file.new_line(fnd_file.log,1);
1974
1975 fnd_file.put_line(fnd_file.log, RPAD(lv_document_id_txt,40) || ' : '|| p_document_id_txt);
1976
1977 fnd_file.new_line(fnd_file.log,1);
1978 fnd_file.put_line(fnd_file.log, '--------------------------------------------------------');
1979 fnd_file.new_line(fnd_file.log,1);
1980
1981 edit_clob(p_document_id_txt,lc_newxmldoc,lv_rowid);
1982 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1983 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_xml.debug','After edit CLOB ');
1984 END IF;
1985 --
1986 -- update loan status or loan change status to sent
1987 -- update disb status to sent
1988 update_status(p_document_id_txt);
1989 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1990 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_xml.debug','Calling update status, doc id ' || p_document_id_txt);
1991 END IF;
1992 --
1993 --
1994 -- print xml outfile
1995 -- update clob into database
1996 print_out_xml(lc_newxmldoc);
1997 igf_sl_cod_doc_dtls_pkg.update_row(x_rowid => lv_rowid,
1998 x_document_id_txt => p_document_id_txt,
1999 x_outbound_doc => lc_newxmldoc,
2000 x_inbound_doc => NULL,
2001 x_send_date => TRUNC(SYSDATE),
2002 x_ack_date => NULL,
2003 x_doc_status => 'S',
2004 x_doc_type => 'DL',
2005 x_full_resp_code => NULL,
2006 x_mode => 'R');
2007
2008 COMMIT;
2009 EXCEPTION
2010 WHEN OTHERS THEN
2011 ROLLBACK;
2012 retcode := 2;
2013 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2014 fnd_file.put_line(fnd_file.log,SQLERRM);
2015 igs_ge_msg_stack.conc_exception_hndl;
2016 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2017 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.print_xml.debug','sqlerrm ' || SQLERRM);
2018 END IF;
2019 app_exception.raise_exception;
2020
2021 END print_xml;
2022
2023 PROCEDURE store_xml(itemtype IN VARCHAR2,
2024 itemkey IN VARCHAR2,
2025 actid IN NUMBER,
2026 funcmode IN VARCHAR2,
2027 resultout OUT NOCOPY VARCHAR2)
2028 IS
2029
2030 l_clob CLOB;
2031 l_event wf_event_t;
2032 ln_request_id NUMBER;
2033 lv_rowid ROWID;
2034 lv_document_id_txt VARCHAR2(30);
2035
2036 BEGIN
2037
2038 --
2039 -- Steps
2040 -- 1. Read event data
2041 -- 2. Push xml into table
2042 -- 3. Launch Concurrent Request
2043 --
2044 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2045 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' before reading lob ');
2046 END IF;
2047 l_event := wf_engine.getitemattrevent(
2048 itemtype,
2049 itemkey,
2050 'ECX_EVENT_MESSAGE');
2051
2052 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2053 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' after reading lob ');
2054 END IF;
2055 l_clob := l_event.geteventdata;
2056
2057 IF DBMS_LOB.GETLENGTH(l_clob) = 0 THEN
2058 resultout := 'EMPTY_CLOB';
2059 ELSE
2060
2061 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2062 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' get doc id ');
2063 END IF;
2064 lv_document_id_txt := NULL;
2065 lv_document_id_txt := wf_engine.getitemattrtext(
2066 itemtype,
2067 itemkey,
2068 'ECX_PARAMETER1');
2069 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2070 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' get doc id = ' || lv_document_id_txt);
2071 END IF;
2072
2073 IF lv_document_id_txt IS NULL THEN
2074 resultout := 'DOCUMENT_ID_NOT_FOUND';
2075 ELSE
2076 lv_rowid := NULL;
2077 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2078 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' insert into doc dtls ');
2079 END IF;
2080
2081 igf_sl_cod_doc_dtls_pkg.insert_row(
2082 x_rowid => lv_rowid,
2083 x_document_id_txt => lv_document_id_txt,
2084 x_outbound_doc => l_clob,
2085 x_inbound_doc => NULL,
2086 x_send_date => NULL,
2087 x_ack_date => NULL,
2088 x_doc_status => 'R',
2089 x_doc_type => 'DL',
2090 x_full_resp_code => NULL,
2091 x_mode => 'R');
2092
2093 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2094 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' before submitting req ');
2095 END IF;
2096
2097 ln_request_id := apps.fnd_request.submit_request(
2098 'IGF','IGFSLJ19','','',FALSE,
2099 lv_document_id_txt,CHR(0),
2100 '','','','','','','','',
2101 '','','','','','','','','','',
2102 '','','','','','','','','','',
2103 '','','','','','','','','','',
2104 '','','','','','','','','','',
2105 '','','','','','','','','','',
2106 '','','','','','','','','','',
2107 '','','','','','','','','','',
2108 '','','','','','','','','','',
2109 '','','','','','','','','','');
2110
2111
2112 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2113 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug',' request id ' || ln_request_id);
2114 END IF;
2115
2116 IF ln_request_id = 0 THEN
2117 resultout := 'CONCURRENT_REQUEST_FAILED';
2118 ELSE
2119 resultout := 'SUCCESS';
2120 END IF; -- request failed
2121 END IF; -- doc id is null
2122 END IF; -- lob length
2123
2124 EXCEPTION
2125 WHEN OTHERS THEN
2126 resultout := 'E';
2127 wf_core.context ('IGF_SL_DL_GEN_XML',
2128 'STORE_XML', itemtype,
2129 itemkey,to_char(actid), funcmode);
2130 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2131 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_dl_gen_xml.store_xml.debug','sqlerrm ' || SQLERRM);
2132 END IF;
2133 END store_xml;
2134
2135 END igf_sl_dl_gen_xml;