1 PACKAGE BODY igf_sl_pref_lender AS
2 /* $Header: IGFSL21B.pls 120.7 2006/05/05 00:56:24 veramach ship $ */
3 --=========================================================================
4 -- Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA
5 -- All rights reserved.
6 -- ========================================================================
7 --
8 -- DESCRIPTION
9 -- PL/SQL body for package: IGF_SL_PREF_LENDER
10 --
11 -- NOTES
12 --
13 -- This process is used to assign a preferred lender to a group of students
14 -- using Person ID Groups.
15 --
16 ----------------------------------------------------------------------------------
17 -- HISTORY
18 ----------------------------------------------------------------------------------
19 -- who when what
20 ----------------------------------------------------------------------------------
21 -- upinjark 16-Feb-2005 Bug #4187798. Modified line no 135,168,499
22 -- replacing FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT
23 -- with FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION
24 ----------------------------------------------------------------------------------
25 -- ridas 03-JAN-2005 Bug #4097414. Modified the cursor c_get_persons
26 ----------------------------------------------------------------------------------
27 -- sjadhav 14-oct-2003 Changed update flag code to update
28 ----------------------------------------------------------------------------------
29 -- bkkumar 01-SEP-2003 FFELP Loans - Assign Preferred Lender
30 -- FA 122 Loan Enhancements
31 ----------------------------------------------------------------------------------
32 -- bkkumar 29-sep-2003 Incorporated review comments.
33 ----------------------------------------------------------------------------------
34
35 ----------------------------------------------------------------------------------
36
37 TYPE log_record IS RECORD
38 ( person_number VARCHAR2(30),
39 message_text fnd_new_messages.message_text%TYPE);
40
41 -- The PL/SQL table for storing the log messages
42 TYPE LogTab IS TABLE OF log_record
43 index by binary_integer;
44
45 g_log_tab LogTab;
46 g_log_tab_index NUMBER := 0; -- index for the log table
47
48 PROCEDURE print_log_process(
49 p_start_date IN DATE,
50 p_pergrp_id IN VARCHAR2,
51 p_rel_code IN VARCHAR2,
52 p_update IN VARCHAR2
53 ) IS
54 /*
55 || Created By : bkkumar
56 || Created On : 26-MAY-2003
57 || Purpose : This process gets the records from the pl/sql table and print in the log file
58 || Known limitations, enhancements or remarks :
59 || Change History :
60 || Who When What
61 || (reverse chronological order - newest change first)
62 */
63
64 l_count NUMBER(5) := g_log_tab.COUNT;
65 l_person_number VARCHAR2(80);
66 l_rel_code VARCHAR2(80);
67 l_start_dt VARCHAR2(80);
68 l_pergrp_id VARCHAR2(80);
69 l_yes_no VARCHAR2(10);
70 l_param_passed VARCHAR2(80);
71 l_award_yr_status VARCHAR2(80);
72 l_update VARCHAR2(80);
73
74 -- cursor for getting the lookup meanings
75 CURSOR c_get_meaning( cp_lookup_type igf_lookups_view.lookup_type%TYPE)
76 IS
77 SELECT lookup_code ,
78 meaning
79 FROM igf_lookups_view
80 WHERE lookup_type = cp_lookup_type
81 AND lookup_code IN ('PARAMETER_PASS','PERSON_ID_GROUP','REL_CODE','START_DATE','UPDATE');
82 l_get_meaning c_get_meaning%ROWTYPE;
83
84 BEGIN
85 -- open the cursor for getting the lookup meanings to be displayed on the log file
86
87 FOR l_get_meaning IN c_get_meaning('IGF_GE_PARAMETERS') LOOP
88 IF l_get_meaning.lookup_code = 'REL_CODE' THEN
89 l_rel_code := l_get_meaning.meaning;
90 ELSIF l_get_meaning.lookup_code = 'PERSON_ID_GROUP' THEN
91 l_pergrp_id := l_get_meaning.meaning;
92 ELSIF l_get_meaning.lookup_code = 'START_DATE' THEN
93 l_start_dt := l_get_meaning.meaning;
94 ELSIF l_get_meaning.lookup_code = 'PARAMETER_PASS' THEN
95 l_param_passed := l_get_meaning.meaning;
96 ELSIF l_get_meaning.lookup_code = 'UPDATE' THEN
97 l_update := l_get_meaning.meaning;
98 END IF;
99 END LOOP;
100
101 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_update);
102
103 -- here the input parameters are to be logged to the log file
104 fnd_file.put_line(fnd_file.log,l_param_passed);
105 fnd_file.put_line(fnd_file.log,RPAD(l_pergrp_id,30) || ' : ' || p_pergrp_id);
106 fnd_file.put_line(fnd_file.log,RPAD(l_rel_code,30) || ' : ' || p_rel_code);
107 fnd_file.put_line(fnd_file.log,RPAD(l_start_dt,30) || ' : ' || p_start_date);
108
109 fnd_file.put_line(fnd_file.log,RPAD(l_update,30) || ' : ' || l_yes_no);
110
111 -- loop through the log table to display the message after proper formatiing
112 FOR i IN 1..l_count LOOP
113 IF g_log_tab(i).person_number IS NOT NULL THEN
114 fnd_file.put_line(fnd_file.log,'---------------------------------------------------------------------------------');
115 fnd_file.put_line(fnd_file.log,'');
116 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
117 fnd_message.set_token('PERSON_NUM',g_log_tab(i).person_number);
118 fnd_file.put_line(fnd_file.log,fnd_message.get);
119 fnd_file.put_line(fnd_file.log,'');
120 END IF;
121 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
122 END LOOP;
123
124 EXCEPTION
125 WHEN others THEN
126 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
127 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_pref_lender.print_log_process.exception',SQLERRM);
128 END IF;
129 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
130 fnd_message.set_token('NAME','IGF_SL_PREF_LENDER.PRINT_LOG_PROCESS');
131 app_exception.raise_exception;
132
133 END print_log_process;
134
135 PROCEDURE add_log_table_process(
136 p_person_number IN VARCHAR2,
137 p_error IN VARCHAR2,
138 p_message_str IN VARCHAR2
139 ) IS
140 /*
141 || Created By : bkkumar
142 || Created On : 26-MAY-2003
143 || Purpose : This process adds a record to the global pl/sql table containing log messages
144 || Known limitations, enhancements or remarks :
145 || Change History :
146 || Who When What
147 || (reverse chronological order - newest change first)
148 */
149
150 BEGIN
151 -- add the corresponding message to the log table
152 g_log_tab_index := g_log_tab_index + 1;
153 g_log_tab(g_log_tab_index).person_number := p_person_number;
154 g_log_tab(g_log_tab_index).message_text := RPAD('',12) || p_message_str;
155
156 EXCEPTION
157 WHEN others THEN
158 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
159 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_pref_lender.add_log_table_process.exception',SQLERRM);
160 END IF;
161 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
162 fnd_message.set_token('NAME','IGF_SL_PREF_LENDER.ADD_LOG_TABLE_PROCESS');
163 app_exception.raise_exception;
164
165 END add_log_table_process;
166
167 PROCEDURE check_for_todo(
168 p_person_id hz_parties.party_id%TYPE
169 ) AS
170 ------------------------------------------------------------------
171 --Created by : veramach, Oracle India
172 --Date created: 03 /June /2005
173 --
174 --Purpose:
175 --
176 --
177 --Known limitations/enhancements and/or remarks:
178 --
179 --Change History:
180 --Who When What
181 -------------------------------------------------------------------
182
183 -- Get to do item
184 CURSOR c_todo(
185 cp_person_id hz_parties.party_id%TYPE
186 ) IS
187 SELECT tdi.ROWID row_id,
188 tdi.*
189 FROM igf_ap_td_item_inst_all tdi,
190 igf_ap_td_item_mst_all tdm,
191 igf_ap_fa_base_rec_all fa
192 WHERE fa.person_id = cp_person_id
193 AND tdi.item_sequence_number = tdm.todo_number
194 AND fa.base_id = tdi.base_id
195 AND tdm.system_todo_type_code = 'PREFLEND'
196 AND NVL(tdi.inactive_flag,'Y') = 'N'
197 AND tdi.status IN ('INC','REQ');
198
199 BEGIN
200
201 FOR l_todo IN c_todo(p_person_id) LOOP
202 igf_ap_td_item_inst_pkg.update_row(
203 x_rowid => l_todo.row_id,
204 x_base_id => l_todo.base_id,
205 x_item_sequence_number => l_todo.item_sequence_number,
206 x_status => 'COM',
207 x_status_date => TRUNC(SYSDATE),
208 x_add_date => l_todo.add_date,
209 x_corsp_date => l_todo.corsp_date,
210 x_corsp_count => l_todo.corsp_count,
211 x_inactive_flag => l_todo.inactive_flag,
212 x_freq_attempt => l_todo.freq_attempt,
213 x_max_attempt => l_todo.max_attempt,
214 x_required_for_application => l_todo.required_for_application,
215 x_mode => 'R',
216 x_legacy_record_flag => l_todo.legacy_record_flag,
217 x_clprl_id => l_todo.clprl_id
218 );
219 END LOOP;
220
221 EXCEPTION
222 WHEN others THEN
223 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
224 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_pref_lender.check_for_todo.exception',SQLERRM);
225 END IF;
226 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
227 fnd_message.set_token('NAME','IGF_SL_PREF_LENDER.CHECK_FOR_TODO');
228 app_exception.raise_exception;
229 END check_for_todo;
230
231 PROCEDURE main (
232 errbuf OUT NOCOPY VARCHAR2,
233 retcode OUT NOCOPY NUMBER,
234 p_pergrp_id IN NUMBER,
235 p_rel_code IN VARCHAR2,
236 p_start_date IN VARCHAR2,
237 p_update IN VARCHAR2
238 )
239 IS
240 /*
241 || Created By : bkkumar
242 || Created On : 01-SEP-2003
243 || Purpose : Main process which assigns a preferred lender to a student.
244 ||
245 || Known limitations, enhancements or remarks :
246 || Change History :
247 || Who When What
248 || ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
249 || tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
250 || (reverse chronological order - newest change first)
251 */
252
253 l_terminate_flag BOOLEAN := FALSE;
254 l_error_flag BOOLEAN := FALSE;
255 l_error VARCHAR2(80);
256 lv_row_id VARCHAR2(80) := NULL;
257 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
258 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
259 l_success_record_cnt NUMBER := 0;
260 l_error_record_cnt NUMBER := 0;
261 l_message VARCHAR2(2000);
262 l_msg_count NUMBER;
263 l_msg_number NUMBER;
264 l_app VARCHAR2(50);
265 l_return_status VARCHAR2(50);
266 l_total_record_cnt NUMBER := 0;
267 l_debug_str VARCHAR2(800) := NULL;
268 l_start_date DATE;
269 l_clprl_id igf_sl_cl_pref_lenders.clprl_id%TYPE := NULL;
270
271
272
273 CURSOR c_get_relation_code (
274 cp_rel_code igf_sl_cl_recipient.relationship_cd%TYPE
275 )
276 IS
277 SELECT relationship_cd
278 FROM igf_sl_cl_recipient
279 WHERE relationship_cd = cp_rel_code
280 AND enabled = 'Y';
281
282
283 l_get_relation_code c_get_relation_code%ROWTYPE;
284
285 CURSOR c_chk_perid_grp (
286 cp_perid_grp igs_pe_persid_group_all.group_id%TYPE,
287 cp_closed_ind igs_pe_persid_group_all.closed_ind%TYPE
288 )
289 IS
290 SELECT group_cd
291 FROM igs_pe_persid_group_all
292 WHERE group_id = cp_perid_grp
293 AND closed_ind = cp_closed_ind
294 AND create_dt <= SYSDATE;
295
296 l_chk_perid_grp c_chk_perid_grp%ROWTYPE;
297
298 TYPE RefCur IS REF CURSOR;
299 c_get_persons RefCur;
300
301 l_person_id hz_parties.party_id%TYPE;
302 l_person_number hz_parties.party_number%TYPE;
303
304 CURSOR c_chk_pref_lender (
305 cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE
306 )
307 IS
308 SELECT ROWID row_id,
309 clprl_id,
310 relationship_cd,
311 start_date,
312 end_date
313 FROM igf_sl_cl_pref_lenders
314 WHERE person_id = cp_person_id
315 AND end_date IS NULL;
316
317 l_chk_pref_lender c_chk_pref_lender%ROWTYPE;
318
319 lv_status VARCHAR2(1);
320 l_list VARCHAR2(32767);
321 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
322
323 BEGIN
324 igf_aw_gen.set_org_id(NULL);
325
326 errbuf := NULL;
327 retcode := 0;
328 g_log_tab_index := 0;
329
330 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
331
332 -- validation for the start date
333 IF p_start_date IS NOT NULL THEN
334 BEGIN
335 l_start_date := IGS_GE_DATE.IGSDATE(p_start_date);
336 EXCEPTION
337 WHEN OTHERS THEN
338 fnd_message.set_name('IGF','IGF_SL_INVALID_START_DATE');
339 add_log_table_process(NULL,l_error,fnd_message.get);
340 l_terminate_flag := TRUE;
341 END;
342 ELSE
343 l_start_date := TRUNC(SYSDATE);
344 END IF;
345
346 -- validation for the relationship code
347 l_get_relation_code := NULL;
348 OPEN c_get_relation_code(p_rel_code);
349 FETCH c_get_relation_code INTO l_get_relation_code;
350 CLOSE c_get_relation_code;
351
352 IF l_get_relation_code.relationship_cd IS NULL THEN
353 fnd_message.set_name('IGF','IGF_SL_INVALID_REL_CODE');
354 add_log_table_process(NULL,l_error,fnd_message.get);
355 l_terminate_flag := TRUE;
356 END IF;
357
358 -- validation for the person id group
359 l_chk_perid_grp := NULL;
360 OPEN c_chk_perid_grp(p_pergrp_id,'N');
361 FETCH c_chk_perid_grp INTO l_chk_perid_grp;
362 CLOSE c_chk_perid_grp;
363
364 IF l_chk_perid_grp.group_cd IS NULL THEN
365 fnd_message.set_name('IGS','IGS_FI_INVPERS_ID_GRP');
366 add_log_table_process(NULL,l_error,fnd_message.get);
367 l_terminate_flag := TRUE;
368 END IF;
369
370 -- if either of the above condition fails then log the message in log file and exit.
371 IF l_terminate_flag = TRUE THEN
372 print_log_process(l_start_date,l_chk_perid_grp.group_cd,p_rel_code,p_update);
373 RETURN;
374 END IF;
375
376 -- THE MAIN LOOP STARTS HERE FOR FETCHING THE PERSONS IN THE PERSON ID GROUP
377 -- Bug #5021084
378 l_list := igf_ap_ss_pkg.get_pid(p_pergrp_id,lv_status,lv_group_type);
379
380 --Bug #5021084. Passing Group ID if the group type is STATIC.
381 IF lv_group_type = 'STATIC' THEN
382 OPEN c_get_persons FOR ' SELECT party_id person_id, party_number person_number FROM hz_parties WHERE party_id IN (' || l_list || ') ' USING p_pergrp_id;
383 ELSIF lv_group_type = 'DYNAMIC' THEN
384 OPEN c_get_persons FOR ' SELECT party_id person_id, party_number person_number FROM hz_parties WHERE party_id IN (' || l_list || ') ';
385 END IF;
386
387 LOOP
388 BEGIN
389 SAVEPOINT sp1;
390 l_person_id := NULL;
391 l_person_number := NULL;
392
393 FETCH c_get_persons INTO l_person_id,l_person_number;
394 EXIT WHEN c_get_persons%NOTFOUND OR c_get_persons%NOTFOUND IS NULL;
395 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
396 l_debug_str := 'Person Number is:' || l_person_number;
397 END IF;
398 l_msg_count := NULL;
399 l_msg_number := NULL;
400 l_return_status := NULL;
401 -- check if the person has an active lender already
402 lv_row_id := NULL;
403 l_clprl_id := NULL;
404 l_chk_pref_lender := NULL;
405 OPEN c_chk_pref_lender(l_person_id);
406 FETCH c_chk_pref_lender INTO l_chk_pref_lender;
407 CLOSE c_chk_pref_lender;
408
409 IF l_chk_pref_lender.start_date IS NULL THEN
410 l_message := NULL;
411 -- insert a record into the igf_sl_cl_pref_lenders table as no active lender exists
412 BEGIN
413 igf_sl_cl_pref_lenders_pkg.insert_row (
414 x_mode => 'R',
415 x_clprl_id => l_clprl_id,
416 x_rowid => lv_row_id,
417 x_msg_count => l_msg_count,
418 x_msg_data => l_msg_number,
419 x_return_status => l_return_status,
420 x_person_id => l_person_id,
421 x_start_date => l_start_date,
422 x_relationship_cd => p_rel_code,
423 x_end_date => NULL
424 );
425 -- here exception will be raised if the lender setup is invalid after performing the above operation
426 -- the validation is done in the TBH of igf_sl_cl_pref_lenders
427 -- if no exception is raised then add the sucessful message to the log
428 fnd_message.set_name('IGF','IGF_SL_CL_LEND_ADD');
429 fnd_message.set_token('PERS_NUM',l_person_number);
430 fnd_message.set_token('REL_CODE',p_rel_code);
431 add_log_table_process(l_person_number,'',fnd_message.get);
432 check_for_todo(l_person_id);
433 EXCEPTION WHEN OTHERS THEN
434 -- check which messgae is there in the stack and display the appropriate message
435 fnd_message.parse_encoded(fnd_message.get_encoded,l_app,l_message);
436 IF l_message IS NOT NULL THEN
437 fnd_message.set_name(l_app,l_message);
438 add_log_table_process(l_person_number,l_error,fnd_message.get);
439 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
440 add_log_table_process(NULL,l_error,fnd_message.get);
441 ROLLBACK TO sp1;
442 l_error_flag := TRUE;
443 END IF;
444 END;
445
446 ELSE
447 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
448 l_debug_str := l_debug_str || ' Person has a active lender relationship';
449 END IF;
450 -- means already the active lender exists
451 IF l_chk_pref_lender.relationship_cd = p_rel_code THEN
452 -- means same active relationship already exists
453 fnd_message.set_name('IGF','IGF_SL_CL_LEND_EXISTS');
454 add_log_table_process(l_person_number,l_error,fnd_message.get);
455 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
456 add_log_table_process(NULL,l_error,fnd_message.get);
457 l_error_flag := TRUE;
458 ELSE
459 IF p_update = 'N' THEN
460 -- log a message that existing relationship cannot be updated
461 fnd_message.set_name('IGF','IGF_SL_CL_LEND_NOT_ADD');
462 add_log_table_process(l_person_number,l_error,fnd_message.get);
463 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
464 add_log_table_process(NULL,l_error,fnd_message.get);
465 l_error_flag := TRUE;
466 ELSE
467 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
468 l_debug_str := l_debug_str || ' Previous record has to be updated';
469 END IF;
470 l_message := NULL;
471 -- Previous relationship record has to be end dated and a new record has to be added .
472 BEGIN
473 igf_sl_cl_pref_lenders_pkg.update_row (
474 x_mode => 'R',
475 x_clprl_id => l_chk_pref_lender.clprl_id,
476 x_rowid => l_chk_pref_lender.row_id,
477 x_msg_count => l_msg_count,
478 x_msg_data => l_msg_number,
479 x_return_status => l_return_status,
480 x_person_id => l_person_id,
481 x_start_date => l_chk_pref_lender.start_date,
482 x_relationship_cd => l_chk_pref_lender.relationship_cd,
483 x_end_date => TRUNC(l_start_date-1)
484 );
485 igf_sl_cl_pref_lenders_pkg.insert_row (
486 x_mode => 'R',
487 x_rowid => lv_row_id,
488 x_clprl_id => l_clprl_id,
489 x_msg_count => l_msg_count,
490 x_msg_data => l_msg_number,
491 x_return_status => l_return_status,
492 x_person_id => l_person_id,
493 x_start_date => l_start_date,
494 x_relationship_cd => p_rel_code,
495 x_end_date => NULL
496 );
497 -- here exception will be raised if the lender setup is invalid after performing the above operation
498 -- the validation is done in the TBH of igf_sl_cl_pref_lenders
499 -- if no exception is raised then add the sucessful message to the log
500 fnd_message.set_name('IGF','IGF_SL_CL_LEND_ADD');
501 fnd_message.set_token('PERS_NUM',l_person_number);
502 fnd_message.set_token('REL_CODE',p_rel_code);
503 add_log_table_process(l_person_number,'',fnd_message.get);
504
505 check_for_todo(l_person_id);
506 EXCEPTION WHEN OTHERS THEN
507 -- check which messgae is there in the stack and display the appropriate message
508 fnd_message.parse_encoded(fnd_message.get_encoded,l_app,l_message);
509 IF l_message IS NOT NULL THEN
510 fnd_message.set_name(l_app,l_message);
511 add_log_table_process(l_person_number,l_error,fnd_message.get);
512 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
513 add_log_table_process(NULL,l_error,fnd_message.get);
514 ROLLBACK TO sp1;
515 l_error_flag := TRUE;
516 END IF;
517 END;
518
519 END IF;
520 END IF;
521 END IF;
522
523 IF l_error_flag = TRUE THEN
524 l_error_flag := FALSE;
525 l_error_record_cnt := l_error_record_cnt + 1;
526 ELSE
527 l_success_record_cnt := l_success_record_cnt + 1;
528 END IF;
529 -- log the messgae in the logging framework
530 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
531 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_sl_pref_lender.main.debug',l_debug_str);
532 END IF;
533 l_debug_str := NULL;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 l_debug_str := NULL;
538 l_error_flag := FALSE;
539 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
540 fnd_message.set_token('NAME','IGF_SL_PREF_LENDER.MAIN');
541 add_log_table_process(l_person_number,l_error,fnd_message.get || ' ' || SQLERRM);
542 ROLLBACK TO sp1;
543 END;
544 -- commit the sucessful record
545 COMMIT;
546 END LOOP;
547 CLOSE c_get_persons;
548
549 -- if no record is processed then display the following message
550 IF l_success_record_cnt = 0 AND l_error_record_cnt = 0 THEN
551 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
552 add_log_table_process(NULL,l_error,fnd_message.get);
553 END IF;
554
555 -- CALL THE PRINT LOG PROCESS
556 print_log_process(l_start_date,l_chk_perid_grp.group_cd,p_rel_code,p_update);
557
558 EXCEPTION
559 WHEN others THEN
560 IF FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
561 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'igf.plsql.igf_sl_pref_lender.main.exception',SQLERRM);
562 END IF;
563 ROLLBACK;
564 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
565 fnd_message.set_token('NAME','IGF_SL_PREF_LENDER.MAIN');
566 retcode := 2;
567 errbuf := fnd_message.get;
568 igs_ge_msg_stack.conc_exception_hndl;
569
570 END main;
571
572 END igf_sl_pref_lender;
[Home] [Help]