1 PACKAGE BODY igf_sp_rollover AS
2 /* $Header: IGFSP02B.pls 120.8 2006/06/12 08:10:48 skharida ship $ */
3
4 ------------------------------------------------------------------
5 --Created by : Sanil Madathil, Oracle IDC
6 --Date created: 24 Jan 2002
7 --
8 --Purpose:
9 --
10 --
11 --Known limitations/enhancements and/or remarks:
12 --
13 --Change History:
14 --Who When What
15 --skharida 12-Jun-2006 Bug#5093981 Modified the procedure sponsor_student_rollover
16 --gurprsin 31-May-2006 Bug 5213852,Modification done to sponsor_student_rollover,
17 -- sponsor_fund_roll_over procedure to Log the new messages
18 -- 'IGF_SP_NO_STDREL_TERM_MAP', 'IGF_SP_NO_FUND_TERM_MAP' respectively
19 -- and removed the code logic to log 'IGF_AW_FND_RLOVR_LD_NTFND'
20 -- as the later message is obsoleted.
21 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified sponsor_fund_rollover and sponsor_student_rollover
22 --akandreg 29-Mar-2006 Bug 4765537. Passed appropriate values to parameters x_lock_award_flag,
23 -- x_donot_repkg_if_code ,x_re_pkg_verif_flag of igf_aw_fund_mast_pkg.insert_row
24 --museshad 14-Jul-2005 Build FA 140.
25 -- Modified TBH call due to the addition of new
26 -- columns to igf_aw_fund_mast_all table.
27 --museshad 25-May-2005 Build FA 157.
28 -- New column 'DISB_ROUNDING_CODE' has been added
29 -- to the table 'IGF_AW_FUND_MAST_ALL'.
30 -- Modified calls to TBH.
31 --brajendr 13-Oct-2004 FA152 COA and FA137 Repackaging design changes
32 -- Added the new column to the form and the TBH calls
33 --veramach July 2004 FA 151 HR Integration(bug #3709292)
34 -- Impact of obsoleting columns from fund manager
35 --vvutukur 18-Jul-2003 Enh#3038511.FICR106 Build. Modified procedure sponsor_rollover.
36 --pathipat 28-Apr-2003 Enh 2831569 - Commercial Receivables build
37 -- Modified sponsor_rollover() - Added call to chk_manage_account()
38 --vvutukur 25-Mar-2003 Bug#2822725.Modified procedures sponsor_fund_rollover,sponsor_student_rollover to remove parameters
39 -- p_cal_type,p_sequence_number and from cursor c_igf_aw_fund_mast and its usage.Modified function lookup_desc.
40 --vchappid 18-Feb-2003 Bug 2785649, Sponsor Code is made mandatory. Modified cursor c_igf_aw_fund_mast
41 -- for removing handling NULL value of Fund ID input parameter
42 -- Fund Id is made mandatory. When this parameter is null, process will error out
43 --adhawan 06-nov-2002 Obsoletion of sap_type from the tbh of igf_aw_fund_mast_pkg
44 --2613536
45 -- adhawan 31-oct-2002 Added gift_aid to insert row of Fund Manager
46 --2613546
47 -------------------------------------------------------------------
48
49 g_c_fund_type CONSTANT VARCHAR2(10) := 'SPONSOR';
50 g_c_yes CONSTANT VARCHAR2(1) := 'Y';
51 g_c_no CONSTANT VARCHAR2(1) := 'N';
52
53
54 -- Declare an User-Defined exception for handling known error conditions
55 do_nothing EXCEPTION;
56
57 -- Forward declaration of the functions, procedures used in the package body
58 -- The functions/procedures referred are private to the package body
59
60 -- function to return meaning for the lookup code and lookup type passed
61 -- as parameter.
62 FUNCTION lookup_desc( p_type IN VARCHAR2 ,
63 p_code IN VARCHAR2
64 ) RETURN VARCHAR2;
65
66 -- procedure to log the messages
67 PROCEDURE log_messages ( p_msg_name IN VARCHAR2 ,
68 p_msg_val IN VARCHAR2
69 ) ;
70
71 -- function to validate the fund
72 FUNCTION validate_fund(p_fund IN igf_aw_fund_mast.fund_id%TYPE ,
73 p_cal_type IN igs_ca_inst.cal_type%TYPE,
74 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
75 p_err_message OUT NOCOPY VARCHAR2
76 ) RETURN BOOLEAN;
77
78 -- function to validate award year
79 FUNCTION validate_award_year(p_cal_type IN igs_ca_inst.cal_type%TYPE,
80 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
81 p_err_message OUT NOCOPY VARCHAR2
82 ) RETURN BOOLEAN;
83
84 -- procedure which rollover over the sponsor fund details
85 PROCEDURE sponsor_fund_rollover ( p_sc_cal_type IN igs_ca_inst_all.cal_type%TYPE,
86 p_sc_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
87 p_fund IN igf_aw_fund_mast_all.fund_id%TYPE
88 ) ;
89
90 -- procedure which rollover over the sponsor student relation
91 PROCEDURE sponsor_student_rollover ( p_sc_cal_type IN igs_ca_inst_all.cal_type%TYPE,
92 p_sc_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
93 p_fund IN igf_aw_fund_mast_all.fund_id%TYPE
94 );
95
96 -- Forward declaration of functions/procedures ends here
97
98 -- cursor to select fund code from igf_aw_fund_mast to get fund code for fund id parameter
99 -- This cursor definition is public to this package body;
100 CURSOR c_igf_aw_fund_mast(cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
101 SELECT fmast.*
102 FROM igf_aw_fund_mast fmast ,
103 igf_aw_fund_cat fcat
104 WHERE fmast.fund_code = fcat.fund_code
105 AND fmast.fund_id = cp_fund_id
106 AND fcat.sys_fund_type = g_c_fund_type
107 AND fmast.discontinue_fund = g_c_no;
108
109
110 -- cursor to retrieve the succeeding year for the current award year passed as
111 -- parameter to it.
112 CURSOR c_igf_aw_cal_rel(cp_cal_type igs_ca_inst.cal_type%TYPE,
113 cp_seq_num igs_ca_inst.sequence_number%TYPE
114 ) IS
115 SELECT sc_cal_type , sc_sequence_number,
116 sc_alternate_code , sc_start_dt,
117 sc_end_dt
118 FROM igf_aw_cal_rel_v
119 WHERE cr_cal_type = cp_cal_type
120 AND cr_sequence_number = cp_seq_num
121 AND active = 'Y';
122
123
124 -- This procedure is being invoked directly from the concurrent manager
125 PROCEDURE sponsor_rollover ( errbuf OUT NOCOPY VARCHAR2 ,
126 retcode OUT NOCOPY NUMBER ,
127 p_award_year IN VARCHAR2 ,
128 p_rollover IN VARCHAR2 ,
129 p_fund_id IN igf_aw_fund_mast_all.fund_id%TYPE,
130 p_run_mode IN VARCHAR2
131 ) IS
132 ------------------------------------------------------------------
133 --Created by : Sanil Madathil, Oracle IDC
134 --Date created: 24 Jan 2002
135 --
136 --Purpose:
137 --
138 --
139 --Known limitations/enhancements and/or remarks:
140 --
141 --Change History:
142 --Who When What
143 --vvutukur 20-Jul-2003 Enh#3038511.FICR106 Build. Added call to generic procedure
144 -- igs_fi_crdapi_util.get_award_year_status to validate the Status of the
145 -- Award Year passed as parameter to this proces and also the status of its Succeeding Award Year.
146 --pathipat 28-Apr-2003 Enh 2831569 - Commercial Receivables build
147 -- Added check for manage_accounts - call to chk_manage_account()
148 --vvutukur 25-Mar-2003 Bug#2822725.Modified the calls to sponsor_fund_rollover to remove parameters p_cal_type,p_sequence_number.Also
149 -- modified the cursor c_igs_lookups to check for sysdate falling between lookup start and end dates and enabled_flag.
150 -- Added tokens to the message IGF_AW_AWD_MAP_NOT_FND.
151 --vchappid 18-Feb-2003 Bug 2785649, Fund Id is made mandatory. When this parameter is null then
152 -- the process will error out
153 ------------------------------------------------------------------
154 -- Cursor to select meaning associated with the run mode passed as parameter
155 -- to the process
156 CURSOR c_igs_lookups(cp_run_flag VARCHAR2) IS
157 SELECT meaning
158 FROM igs_lookup_values
159 WHERE lookup_type= 'YES_NO'
160 AND lookup_code= cp_run_flag
161 AND NVL(enabled_flag,'N') = 'Y'
162 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE))
163 AND TRUNC(NVL(end_date_active,SYSDATE));
164
165 -- cursor variable for c_igs_lookups
166 l_c_igs_lookups c_igs_lookups%ROWTYPE;
167
168 l_ans BOOLEAN := FALSE ;
169 l_appl_name VARCHAR2(30) := NULL;
170 l_cal_type igs_ca_inst_all.cal_type%TYPE;
171 l_sequence_number igs_ca_inst_all.sequence_number%TYPE;
172 l_sc_cal_type igs_ca_inst_all.cal_type%TYPE;
173 l_sc_sequence_number igs_ca_inst_all.sequence_number%TYPE;
174 l_err_message VARCHAR2(30);
175 -- cursor variable for c_igf_aw_cal_rel
176 l_c_igf_aw_cal_rel c_igf_aw_cal_rel%ROWTYPE;
177 -- cursor variable for c_igf_aw_fund_mast
178 l_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
179
180 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
181 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
182
183 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
184
185 BEGIN
186 -- sets the orgid
187 igf_aw_gen.set_org_id(p_context => NULL) ;
188 -- initialises the retcode parameter to 0
189 retcode := 0 ;
190
191 -- Extract calendar type and Sequence number from award year parameter passed to the process
192 l_cal_type := RTRIM(SUBSTR(p_award_year ,1,10));
193 l_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_award_year,11)));
194
195 -- get the meaning of the run mode parameter passed to the process
196 OPEN c_igs_lookups(cp_run_flag => p_run_mode);
197 FETCH c_igs_lookups INTO l_c_igs_lookups;
198 CLOSE c_igs_lookups;
199
200 -- get sponsor fund code from igf_aw_fund_mast to get fund code for fund id parameter
201 OPEN c_igf_aw_fund_mast(cp_fund_id => p_fund_id);
202 FETCH c_igf_aw_fund_mast INTO l_c_igf_aw_fund_mast;
203 CLOSE c_igf_aw_fund_mast;
204
205 -- log all the parameters passed to the process
206 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','AWARD_YEAR'),p_award_year);
207 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','FUND_CODE'),l_c_igf_aw_fund_mast.fund_code||' '||l_c_igf_aw_fund_mast.description);
208 log_messages(lookup_desc('IGF_AW_LOOKUPS_MSG','TEST_MODE'),l_c_igs_lookups.meaning);
209
210 -- Obtain the value of manage_accounts in the System Options form
211 -- If it is null then this process is not available, so error out.
212 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
213 p_v_message_name => l_v_message_name
214 );
215 IF (l_v_manage_acc IS NULL) THEN
216 fnd_message.set_name('IGS',l_v_message_name);
217 fnd_file.put_line(fnd_file.log,fnd_message.get());
218 fnd_file.new_line(fnd_file.log);
219 RAISE do_nothing;
220 END IF;
221
222 -- This Section confirms that all the mandatory parameters are passed to the process
223 IF ((p_award_year IS NULL) OR (p_rollover IS NULL) OR (p_run_mode IS NULL) OR (p_fund_id IS NULL)) THEN
224 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
225 fnd_file.put_line(fnd_file.log, fnd_message.get);
226 RAISE do_nothing;
227 END IF;
228 --Validation of all mandatory parameter ends here
229
230 -- This Section confirms that all parameters passed to the process are valid
231 -- This procedure validates the award year.
232 IF p_award_year IS NOT NULL THEN
233 l_ans := validate_award_year(p_cal_type => l_cal_type,
234 p_sequence_number => l_sequence_number,
235 p_err_message => l_err_message
236 );
237 IF NOT(l_ans) and l_err_message IS NOT NULL THEN
238 fnd_message.set_name('IGS',l_err_message);
239 fnd_file.put_line(fnd_file.log, fnd_message.get);
240 RAISE do_nothing;
241 END IF;
242
243 --Validate the Award Year Status. If the status is not open, log the message in log file and
244 --complete the process with error.
245 l_v_message_name := NULL;
246 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => l_cal_type,
247 p_n_awd_seq_number => l_sequence_number,
248 p_v_awd_yr_status => l_v_awd_yr_status_cd,
249 p_v_message_name => l_v_message_name
250 );
251 IF l_v_message_name IS NOT NULL THEN
252 IF l_v_message_name = 'IGF_SP_INVALID_AWD_YR_STATUS' THEN
253 fnd_message.set_name('IGF',l_v_message_name);
254 ELSE
255 fnd_message.set_name('IGS',l_v_message_name);
256 END IF;
257 fnd_file.put_line(fnd_file.log,fnd_message.get);
258 RAISE do_nothing;
259 END IF;
260 END IF;
261
262 --Validate the fund id.
263 l_ans := validate_fund(p_fund => p_fund_id,
264 p_cal_type => l_cal_type,
265 p_sequence_number => l_sequence_number,
266 p_err_message => l_err_message);
267 IF NOT(l_ans) and l_err_message IS NOT NULL THEN
268 fnd_message.set_name('IGS',l_err_message);
269 fnd_file.put_line(fnd_file.log, fnd_message.get);
270 RAISE do_nothing;
271 END IF;
272
273 -- Check if succeeding awards are present for the current award year passed as
274 -- parameter to the process
275 OPEN c_igf_aw_cal_rel(cp_cal_type => l_cal_type,
276 cp_seq_num => l_sequence_number
277 );
278 FETCH c_igf_aw_cal_rel INTO l_c_igf_aw_cal_rel;
279 IF c_igf_aw_cal_rel%NOTFOUND THEN
280 CLOSE c_igf_aw_cal_rel;
281 fnd_message.set_name('IGF','IGF_AW_AWD_MAP_NOT_FND');
282 fnd_message.set_token('FNDID',p_fund_id);
283 fnd_message.set_token('ALTCD',p_award_year);
284 fnd_file.put_line(fnd_file.log, fnd_message.get);
285 RAISE do_nothing;
286 END IF;
287 l_sc_cal_type := l_c_igf_aw_cal_rel.sc_cal_type;
288 l_sc_sequence_number := l_c_igf_aw_cal_rel.sc_sequence_number;
289 CLOSE c_igf_aw_cal_rel;
290
291 --Validate the Succeeding Award Year Status. If the status is not open, log the message in log file and
292 --complete the process with error.
293 l_v_message_name := NULL;
294 l_v_awd_yr_status_cd := NULL;
295
296 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => l_sc_cal_type,
297 p_n_awd_seq_number => l_sc_sequence_number,
298 p_v_awd_yr_status => l_v_awd_yr_status_cd,
299 p_v_message_name => l_v_message_name
300 );
301 IF l_v_message_name IS NOT NULL THEN
302 fnd_message.set_name('IGF','IGF_SP_NXT_AWD_YR_STAT_INVALID');
303 fnd_message.set_token('ALT_CODE',l_c_igf_aw_cal_rel.sc_alternate_code);
304 fnd_file.put_line(fnd_file.log,fnd_message.get);
305 RAISE do_nothing;
306 END IF;
307
308 SAVEPOINT sp_main;
309 --Validation of all the parameters ends here
310 -- if user has given only rollover of fund alone
311 IF p_rollover = 'S' THEN
312 -- procedure which rollover the sponsor fund details
313 sponsor_fund_rollover ( p_sc_cal_type => l_sc_cal_type,
314 p_sc_seq_num => l_sc_sequence_number,
315 p_fund => p_fund_id
316 ) ;
317
318 -- if user has given only rollover of student sponsor fund alone
319 ELSIF p_rollover = 'R' THEN
320 -- procedure which rollover the sponsor student details
321 sponsor_student_rollover ( p_sc_cal_type => l_sc_cal_type,
322 p_sc_seq_num => l_sc_sequence_number,
323 p_fund => p_fund_id
324 ) ;
325
326 -- if user has given rollover of fund and student sponsor fund
327 ELSIF p_rollover = 'B' THEN
328 -- procedure which rollover the sponsor fund details
329 sponsor_fund_rollover ( p_sc_cal_type => l_sc_cal_type,
330 p_sc_seq_num => l_sc_sequence_number,
331 p_fund => p_fund_id
332 ) ;
333 fnd_file.put_line(fnd_file.log,' ');
334 sponsor_student_rollover ( p_sc_cal_type => l_sc_cal_type,
335 p_sc_seq_num => l_sc_sequence_number,
336 p_fund => p_fund_id
337 ) ;
338 END IF;
339 -- if test mode = yes then rollback all the transactions
340 IF p_run_mode = 'Y' THEN
341 ROLLBACK TO sp_main;
342 END IF;
343
344 EXCEPTION
345 WHEN do_nothing THEN
346 retcode :=2;
347 WHEN OTHERS THEN
348 retcode := 2;
349 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
350 igs_ge_msg_stack.conc_exception_hndl ;
351 END sponsor_rollover;
352
353 FUNCTION lookup_desc( p_type IN VARCHAR2 ,
354 p_code IN VARCHAR2 )
355 RETURN VARCHAR2 IS
356 ------------------------------------------------------------------
357 --Created by : Sanil Madathil, Oracle IDC
358 --Date created: 24 Jan 2002
359 --
360 --Purpose: This function is private to this package body .
361 --
362 --Known limitations/enhancements and/or remarks:
363 --
364 --Change History:
365 --Who When What
366 --vvutukur 07-Mar-2003 Bug#2822725.Removed cursor to fetch the meaning of lookup,instead used generic function.
367 -------------------------------------------------------------------
368
369 l_desc igf_lookups_view.meaning%TYPE ;
370
371 BEGIN
372 IF p_code IS NULL THEN
373 RETURN NULL;
374 ELSE
375 RETURN igf_aw_gen.lookup_desc( l_type => p_type,
376 l_code => p_code);
377 END IF ;
378 END lookup_desc; /** Function Ends Here **/
379
380
381 PROCEDURE log_messages ( p_msg_name IN VARCHAR2 ,
382 p_msg_val IN VARCHAR2
383 ) IS
384 ------------------------------------------------------------------
385 --Created by : Sanil Madathil, Oracle IDC
386 --Date created: 24 Jan 2001
387 --
388 --Purpose: This procedure is private to this package body .
389 -- The procedure logs all the parameter values ,
390 -- table values
391 --
392 --
393 --Known limitations/enhancements and/or remarks:
394 --
395 --Change History:
396 --Who When What
397 -------------------------------------------------------------------
398 BEGIN
399 fnd_message.set_name('IGS','IGS_FI_CAL_BALANCES_LOG');
400 fnd_message.set_token('PARAMETER_NAME',p_msg_name);
401 fnd_message.set_token('PARAMETER_VAL' ,p_msg_val) ;
402 fnd_file.put_line(fnd_file.log,fnd_message.get);
403 END log_messages;
404
405
406 FUNCTION validate_fund(p_fund IN igf_aw_fund_mast.fund_id%TYPE ,
407 p_cal_type IN igs_ca_inst.cal_type%TYPE,
408 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
409 p_err_message OUT NOCOPY VARCHAR2
410 ) RETURN BOOLEAN IS
411 ------------------------------------------------------------------
412 --Created by : Sanil Madathil, Oracle IDC
413 --Date created: 24 Jan 2001
414 --
415 --Purpose: This procedure is private to this package body .
416 -- The procedure logs all the parameter values ,
417 -- table values
418 --
419 --
420 --Known limitations/enhancements and/or remarks:
421 --
422 --Change History:
423 --Who When What
424 --vvutukur 07-Mar-2003 Bug#2822725. Removed parameters cal_type,sequence_number to open the cursor c_igf_aw_fund_mast.
425 ------------------------------------------------------------------
426 -- cursor variable for c_igf_aw_fund_mast
427 l_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
428 BEGIN
429 OPEN c_igf_aw_fund_mast(cp_fund_id => p_fund);
430 FETCH c_igf_aw_fund_mast INTO l_c_igf_aw_fund_mast;
431 IF c_igf_aw_fund_mast%NOTFOUND THEN
432 CLOSE c_igf_aw_fund_mast;
433 p_err_message := 'IGS_GE_INVALID_VALUE';
434 RETURN (FALSE);
435 END IF;
436 CLOSE c_igf_aw_fund_mast;
437 p_err_message := NULL;
438 RETURN (TRUE);
439 END validate_fund;
440
441
442 FUNCTION validate_award_year(p_cal_type IN igs_ca_inst.cal_type%TYPE,
443 p_sequence_number IN igs_ca_inst.sequence_number%TYPE,
444 p_err_message OUT NOCOPY VARCHAR2
445 ) RETURN BOOLEAN IS
446 ------------------------------------------------------------------
447 --Created by : Sanil Madathil, Oracle IDC
448 --Date created: 24 Jan 2001
449 --
450 --Purpose: This procedure is private to this package body .
451 -- The procedure logs all the parameter values ,
452 -- table values
453 --
454 --
455 --Known limitations/enhancements and/or remarks:
456 --
457 --Change History:
458 --Who When What
459 ------------------------------------------------------------------
460
461 -- cursor which validates whether the term calendar type and
462 -- sequence number is present in igs_ca_inst table
463 CURSOR c_igs_ca_inst(cp_cal_type igs_ca_inst.cal_type%TYPE,
464 cp_sequence_number igs_ca_inst.sequence_number%TYPE
465 ) IS
466 SELECT '1'
467 FROM igs_ca_inst
468 WHERE cal_type = cp_cal_type
469 AND sequence_number = cp_sequence_number;
470
471 l_c_igs_ca_inst c_igs_ca_inst%ROWTYPE;
472 BEGIN
473 OPEN c_igs_ca_inst(cp_cal_type => p_cal_type,
474 cp_sequence_number => p_sequence_number
475 ) ;
476 FETCH c_igs_ca_inst into l_c_igs_ca_inst ;
477 -- if records are not found for the passed calendar type and sequence number
478 -- assign error message to out NOCOPY parameter and function returns false
479 IF c_igs_ca_inst%NOTFOUND THEN
480 CLOSE c_igs_ca_inst;
481 p_err_message := 'IGS_GE_INVALID_VALUE';
482 RETURN (FALSE);
483 END IF;
484 CLOSE c_igs_ca_inst;
485 p_err_message := NULL;
486 RETURN (TRUE);
487 END validate_award_year;
488
489 -- procedure which rollover over the sponsor fund details
490 PROCEDURE sponsor_fund_rollover ( p_sc_cal_type IN igs_ca_inst_all.cal_type%TYPE,
491 p_sc_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
492 p_fund IN igf_aw_fund_mast_all.fund_id%TYPE
493 ) IS
494 ------------------------------------------------------------------
495 --Created by : Sanil Madathil, Oracle IDC
496 --Date created: 24 Jan 2001
497 --
498 --Purpose: This procedure is private to this package body .
499 -- The procedure is being called from the sponsor_rollover procedure
500 --
501 --
502 --
503 --Known limitations/enhancements and/or remarks:
504 --
505 --Change History:
506 --Who When What
507 --gurprsin 31-May-2006 Bug 5213852, Logged the new message 'IGF_SP_NO_FUND_TERM_MAP' and removed the code logic to
508 -- log 'IGF_AW_FND_RLOVR_LD_NTFND' as the later message is obsoleted. This new message
509 -- will be logged when user tries to rollover Sponsor setup and the associated term
510 -- calendar mapping does not exist for the destination award year.
511 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igf_aw_fund_mast, igf_sp_fc,
512 -- igf_sp_prg, igf_sp_unit are now rounded off to currency precision
513 --akandreg 29-Mar-2006 Bug 4765537. Passed appropriate values to parameters x_lock_award_flag,
514 -- x_donot_repkg_if_code ,x_re_pkg_verif_flag of igf_aw_fund_mast_pkg.insert_row
515 --museshad 14-Jul-2005 Build FA 140.
516 -- Modified TBH call due to the addition of new
517 -- columns to igf_aw_fund_mast_all table.
518 --museshad 25-May-2005 Build# FA157 - Bug# 4382371.
519 -- New column 'DISB_ROUNDING_CODE' has been added
520 -- to the table 'IGF_AW_FUND_MAST_ALL'.
521 -- Modified calls to TBH.
522 --vvutukur 07-Mar-2003 Bug#2822725. Removed parameters p_cal_type,p_sequence_number of this procedure as they are not required.
523 -- Also same have been removed from cursor c_igf_aw_fund_mast and its usage.
524 --smvk 09-Feb-2003 Bug # 2758812. Added send_without_doc column.
525 ------------------------------------------------------------------
526
527 l_msg_str_0 VARCHAR2(32767) := NULL;
528 l_msg_str_1 VARCHAR2(32767) := NULL;
529 l_message_name VARCHAR2(30) := NULL;
530 l_err_exception EXCEPTION;
531 l_rowid VARCHAR2(25);
532 l_fund_id igf_aw_fund_mast_all.fund_id%TYPE;
533 l_fee_cls_id igf_sp_fc_all.fee_cls_id%TYPE;
534 l_fee_cls_prg_id igf_sp_prg_all.fee_cls_prg_id%TYPE;
535 l_fee_cls_unit_id igf_sp_unit_all.fee_cls_unit_id%TYPE;
536
537 CURSOR c_igf_aw_fund_tp( cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
538 SELECT *
539 FROM igf_aw_fund_tp_v ftp
540 WHERE ftp.fund_id = cp_fund_id
541 ORDER BY tp_cal_type,tp_sequence_number;
542
543 l_c_igf_aw_fund_tp c_igf_aw_fund_tp%ROWTYPE;
544
545 CURSOR c_igf_sp_fc(cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
546 SELECT *
547 FROM igf_sp_fc_v
548 WHERE fund_id = cp_fund_id
549 ORDER BY fee_cls_id;
550
551 l_c_igf_sp_fc c_igf_sp_fc%ROWTYPE;
552
553 CURSOR c_igf_sp_prg(cp_fee_cls_id igf_sp_fc.fee_cls_id%TYPE) IS
554 SELECT *
555 FROM igf_sp_prg
556 WHERE fee_cls_id = cp_fee_cls_id
557 ORDER BY fee_cls_prg_id;
558
559 l_c_igf_sp_prg c_igf_sp_prg%ROWTYPE;
560
561 CURSOR c_igf_sp_unit(cp_fee_cls_prg_id igf_sp_prg.fee_cls_prg_id%TYPE) IS
562 SELECT *
563 FROM igf_sp_unit
564 WHERE fee_cls_prg_id = cp_fee_cls_prg_id
565 ORDER BY fee_cls_unit_id;
566
567 l_c_igf_sp_unit c_igf_sp_unit%ROWTYPE;
568 -- cursor variable for c_igf_aw_cal_rel
569 l_c_igf_aw_cal_rel c_igf_aw_cal_rel%ROWTYPE;
570 -- cursor variable for c_igf_aw_fund_mast
571 l_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
572 BEGIN
573
574 OPEN c_igf_aw_fund_mast(cp_fund_id => p_fund);
575 FETCH c_igf_aw_fund_mast INTO l_c_igf_aw_fund_mast;
576 CLOSE c_igf_aw_fund_mast;
577
578 -- log the relavant details
579 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','FUND_CODE'),32) ||
580 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','AWARD_YEAR'),19) ||
581 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','MIN_CRD_POINTS'),19) ||
582 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','MIN_ATD_TYPE'),32) ||
583 lookup_desc('IGF_AW_LOOKUPS_MSG','TOT_SPNSR_AMT');
584
585 fnd_file.put_line(fnd_file.log,l_msg_str_0);
586 fnd_file.put_line(fnd_file.log,' ');
587 l_msg_str_1 := RPAD(l_c_igf_aw_fund_mast.fund_code,32)||
588 RPAD((p_sc_cal_type||' '||p_sc_seq_num),19) ||
589 NVL(RPAD(TO_CHAR(l_c_igf_aw_fund_mast.min_credit_points),19),' ') ||
590 NVL(RPAD(l_c_igf_aw_fund_mast.enrollment_status,32),' ' )||
591 TO_CHAR(l_c_igf_aw_fund_mast.max_yearly_amt);
592
593 fnd_file.put_line(fnd_file.log,l_msg_str_1);
594 fnd_file.put_line(fnd_file.log,' ');
595
596 BEGIN
597 -- declare a save point
598 SAVEPOINT sp_fund;
599 l_rowid := NULL;
600 l_fund_id := NULL;
601 BEGIN
602 -- rollover the fund to new award year
603 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
604 igf_aw_fund_mast_pkg.insert_row (
605 x_mode => 'R',
606 x_rowid => l_rowid,
607 x_fund_id => l_fund_id,
608 x_fund_code => l_c_igf_aw_fund_mast.fund_code,
609 x_ci_cal_type => p_sc_cal_type,
610 x_ci_sequence_number => p_sc_seq_num,
611 x_description => l_c_igf_aw_fund_mast.description,
612 x_discontinue_fund => l_c_igf_aw_fund_mast.discontinue_fund,
613 x_entitlement => l_c_igf_aw_fund_mast.entitlement,
614 x_auto_pkg => l_c_igf_aw_fund_mast.auto_pkg,
615 x_self_help => l_c_igf_aw_fund_mast.self_help,
616 x_allow_man_pkg => l_c_igf_aw_fund_mast.allow_man_pkg,
617 x_update_need => l_c_igf_aw_fund_mast.update_need,
618 x_disburse_fund => l_c_igf_aw_fund_mast.disburse_fund,
619 x_available_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.available_amt),
620 x_offered_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.offered_amt),
621 x_pending_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.pending_amt),
622 x_accepted_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.accepted_amt),
623 x_declined_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.declined_amt),
624 x_cancelled_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.cancelled_amt),
625 x_remaining_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.remaining_amt),
626 x_enrollment_status => l_c_igf_aw_fund_mast.enrollment_status,
627 x_prn_award_letter => l_c_igf_aw_fund_mast.prn_award_letter,
628 x_over_award_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.over_award_amt),
629 x_over_award_perct => l_c_igf_aw_fund_mast.over_award_perct,
630 x_min_award_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.min_award_amt),
631 x_max_award_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.max_award_amt),
632 x_max_yearly_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.max_yearly_amt),
633 x_max_life_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.max_life_amt),
634 x_max_life_term => l_c_igf_aw_fund_mast.max_life_term,
635 x_fm_fc_methd => l_c_igf_aw_fund_mast.fm_fc_methd,
636 x_roundoff_fact => l_c_igf_aw_fund_mast.roundoff_fact,
637 x_replace_fc => l_c_igf_aw_fund_mast.replace_fc,
638 x_allow_overaward => l_c_igf_aw_fund_mast.allow_overaward,
639 x_pckg_awd_stat => l_c_igf_aw_fund_mast.pckg_awd_stat,
640 x_org_record_req => l_c_igf_aw_fund_mast.org_record_req,
641 x_disb_record_req => l_c_igf_aw_fund_mast.disb_record_req,
642 x_prom_note_req => l_c_igf_aw_fund_mast.prom_note_req,
643 x_min_num_disb => l_c_igf_aw_fund_mast.min_num_disb,
644 x_max_num_disb => l_c_igf_aw_fund_mast.max_num_disb,
645 x_fee_type => l_c_igf_aw_fund_mast.fee_type,
646 x_total_offered => l_c_igf_aw_fund_mast.total_offered,
647 x_total_accepted => l_c_igf_aw_fund_mast.total_accepted,
648 x_total_declined => l_c_igf_aw_fund_mast.total_declined,
649 x_total_revoked => l_c_igf_aw_fund_mast.total_revoked,
650 x_total_cancelled => l_c_igf_aw_fund_mast.total_cancelled,
651 x_total_disbursed => l_c_igf_aw_fund_mast.total_disbursed,
652 x_total_committed => l_c_igf_aw_fund_mast.total_committed,
653 x_committed_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.committed_amt),
654 x_disbursed_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.disbursed_amt),
655 x_awd_notice_txt => l_c_igf_aw_fund_mast.awd_notice_txt,
656 x_attribute_category => l_c_igf_aw_fund_mast.attribute_category,
657 x_attribute1 => l_c_igf_aw_fund_mast.attribute1,
658 x_attribute2 => l_c_igf_aw_fund_mast.attribute2,
659 x_attribute3 => l_c_igf_aw_fund_mast.attribute3,
660 x_attribute4 => l_c_igf_aw_fund_mast.attribute4,
661 x_attribute5 => l_c_igf_aw_fund_mast.attribute5,
662 x_attribute6 => l_c_igf_aw_fund_mast.attribute6,
663 x_attribute7 => l_c_igf_aw_fund_mast.attribute7,
664 x_attribute8 => l_c_igf_aw_fund_mast.attribute8,
665 x_attribute9 => l_c_igf_aw_fund_mast.attribute9,
666 x_attribute10 => l_c_igf_aw_fund_mast.attribute10,
667 x_attribute11 => l_c_igf_aw_fund_mast.attribute11,
668 x_attribute12 => l_c_igf_aw_fund_mast.attribute12,
669 x_attribute13 => l_c_igf_aw_fund_mast.attribute13,
670 x_attribute14 => l_c_igf_aw_fund_mast.attribute14,
671 x_attribute15 => l_c_igf_aw_fund_mast.attribute15,
672 x_attribute16 => l_c_igf_aw_fund_mast.attribute16,
673 x_attribute17 => l_c_igf_aw_fund_mast.attribute17,
674 x_attribute18 => l_c_igf_aw_fund_mast.attribute18,
675 x_attribute19 => l_c_igf_aw_fund_mast.attribute19,
676 x_attribute20 => l_c_igf_aw_fund_mast.attribute20,
677 x_disb_verf_da => l_c_igf_aw_fund_mast.disb_verf_da,
678 x_fund_exp_da => l_c_igf_aw_fund_mast.fund_exp_da,
679 x_nslds_disb_da => l_c_igf_aw_fund_mast.nslds_disb_da,
680 x_disb_exp_da => l_c_igf_aw_fund_mast.disb_exp_da,
681 x_fund_recv_reqd => l_c_igf_aw_fund_mast.fund_recv_reqd,
682 x_show_on_bill => l_c_igf_aw_fund_mast.show_on_bill,
683 x_bill_desc => l_c_igf_aw_fund_mast.bill_desc,
684 x_credit_type_id => l_c_igf_aw_fund_mast.credit_type_id,
685 x_spnsr_ref_num => l_c_igf_aw_fund_mast.spnsr_ref_num,
686 x_threshold_perct => l_c_igf_aw_fund_mast.threshold_perct,
687 x_threshold_value => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.threshold_value),
688 x_party_id => l_c_igf_aw_fund_mast.party_id,
689 x_spnsr_fee_type => l_c_igf_aw_fund_mast.spnsr_fee_type,
690 x_min_credit_points => l_c_igf_aw_fund_mast.min_credit_points,
691 x_group_id => l_c_igf_aw_fund_mast.group_id,
692 x_spnsr_attribute_category => l_c_igf_aw_fund_mast.spnsr_attribute_category,
693 x_spnsr_attribute1 => l_c_igf_aw_fund_mast.spnsr_attribute1,
694 x_spnsr_attribute2 => l_c_igf_aw_fund_mast.spnsr_attribute2,
695 x_spnsr_attribute3 => l_c_igf_aw_fund_mast.spnsr_attribute3,
696 x_spnsr_attribute4 => l_c_igf_aw_fund_mast.spnsr_attribute4,
697 x_spnsr_attribute5 => l_c_igf_aw_fund_mast.spnsr_attribute5,
698 x_spnsr_attribute6 => l_c_igf_aw_fund_mast.spnsr_attribute6,
699 x_spnsr_attribute7 => l_c_igf_aw_fund_mast.spnsr_attribute7,
700 x_spnsr_attribute8 => l_c_igf_aw_fund_mast.spnsr_attribute8,
701 x_spnsr_attribute9 => l_c_igf_aw_fund_mast.spnsr_attribute9,
702 x_spnsr_attribute10 => l_c_igf_aw_fund_mast.spnsr_attribute10,
703 x_spnsr_attribute11 => l_c_igf_aw_fund_mast.spnsr_attribute11,
704 x_spnsr_attribute12 => l_c_igf_aw_fund_mast.spnsr_attribute12,
705 x_spnsr_attribute13 => l_c_igf_aw_fund_mast.spnsr_attribute13,
706 x_spnsr_attribute14 => l_c_igf_aw_fund_mast.spnsr_attribute14,
707 x_spnsr_attribute15 => l_c_igf_aw_fund_mast.spnsr_attribute15,
708 x_spnsr_attribute16 => l_c_igf_aw_fund_mast.spnsr_attribute16,
709 x_spnsr_attribute17 => l_c_igf_aw_fund_mast.spnsr_attribute17,
710 x_spnsr_attribute18 => l_c_igf_aw_fund_mast.spnsr_attribute18,
711 x_spnsr_attribute19 => l_c_igf_aw_fund_mast.spnsr_attribute19,
712 x_spnsr_attribute20 => l_c_igf_aw_fund_mast.spnsr_attribute20,
713 x_ver_app_stat_override => l_c_igf_aw_fund_mast.ver_app_stat_override ,
714 x_gift_aid => l_c_igf_aw_fund_mast.gift_aid,
715 x_send_without_doc => l_c_igf_aw_fund_mast.send_without_doc, -- Bug # 2758812. Added send_without_doc column.
716 x_re_pkg_verif_flag => l_c_igf_aw_fund_mast.re_pkg_verif_flag,
717 x_donot_repkg_if_code => l_c_igf_aw_fund_mast.donot_repkg_if_code,
718 x_lock_award_flag => l_c_igf_aw_fund_mast.lock_award_flag,
719 x_disb_rounding_code => l_c_igf_aw_fund_mast.disb_rounding_code,
720 x_view_only_flag => l_c_igf_aw_fund_mast.view_only_flag,
721 x_accept_less_amt_flag => l_c_igf_aw_fund_mast.accept_less_amt_flag,
722 x_allow_inc_post_accept_flag => l_c_igf_aw_fund_mast.allow_inc_post_accept_flag,
723 x_min_increase_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.min_increase_amt),
724 x_allow_dec_post_accept_flag => l_c_igf_aw_fund_mast.allow_dec_post_accept_flag,
725 x_min_decrease_amt => igs_fi_gen_gl.get_formatted_amount(l_c_igf_aw_fund_mast.min_decrease_amt),
726 x_allow_decln_post_accept_flag => l_c_igf_aw_fund_mast.allow_decln_post_accept_flag,
727 x_status_after_decline => l_c_igf_aw_fund_mast.status_after_decline,
728 x_fund_information_txt => l_c_igf_aw_fund_mast.fund_information_txt
729 );
730
731 EXCEPTION
732 WHEN OTHERS THEN
733 -- rollsback to the save point
734 ROLLBACK TO sp_fund;
735 -- log the error message returned by the tbh
736 fnd_file.put_line(fnd_file.log,' ');
737 fnd_file.put_line(fnd_file.log,fnd_message.get);
738 -- raises user defined exception so as to skip the record
739 -- the record will not be processed further
740 RAISE l_err_exception;
741 END;
742
743 -- rolling over fund term details
744 -- log the relavant details
745 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','TERM'),12) ||
746 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','START_DT'),22) ||
747 lookup_desc('IGF_AW_LOOKUPS_MSG','END_DT');
748 fnd_file.put_line(fnd_file.log,l_msg_str_0);
749 fnd_file.put_line(fnd_file.log,' ');
750 FOR l_c_igf_aw_fund_tp IN c_igf_aw_fund_tp(cp_fund_id => l_c_igf_aw_fund_mast.fund_id)
751 LOOP
752 -- Check if succeeding terms are present for the current term passed as
753 -- parameter to the process
754 OPEN c_igf_aw_cal_rel(cp_cal_type => l_c_igf_aw_fund_tp.tp_cal_type,
755 cp_seq_num => l_c_igf_aw_fund_tp.tp_sequence_number
756 );
757 FETCH c_igf_aw_cal_rel INTO l_c_igf_aw_cal_rel;
758 IF c_igf_aw_cal_rel%NOTFOUND THEN
759 -- rolls back to the save point
760 ROLLBACK TO sp_fund;
761 CLOSE c_igf_aw_cal_rel;
762
763 --Bug 5213852, Logged the new message 'IGF_SP_NO_FUND_TERM_MAP' and removed the code logic to
764 --log 'IGF_AW_FND_RLOVR_LD_NTFND' as the later message is obsoleted.
765 fnd_message.set_name('IGF','IGF_SP_NO_FUND_TERM_MAP');
766 fnd_message.set_token('TERM_ALT_CD ',l_c_igf_aw_fund_tp.tp_alternate_code);
767 fnd_message.set_token('AWD_ALT_CD',l_c_igf_aw_fund_tp.awd_alternate_code);
768 fnd_message.set_token('FUND_CODE',l_c_igf_aw_fund_tp.fund_code);
769
770 igs_ge_msg_stack.add;
771 -- log the error message
772 fnd_file.put_line(fnd_file.log,' ');
773 fnd_file.put_line(fnd_file.log,fnd_message.get);
774 -- raises user defined exception so as to skip the record
775 RAISE l_err_exception;
776 END IF;
777 CLOSE c_igf_aw_cal_rel;
778 -- log relevant details
779 l_msg_str_1 := RPAD(l_c_igf_aw_cal_rel.sc_alternate_code,12) ||
780 RPAD(igs_ge_date.igschardt(l_c_igf_aw_cal_rel.sc_start_dt),22) ||
781 igs_ge_date.igschardt(l_c_igf_aw_cal_rel.sc_end_dt);
782 fnd_file.put_line(fnd_file.log,l_msg_str_1);
783 l_rowid := NULL;
784 BEGIN
785 igf_aw_fund_tp_pkg.insert_row (
786 x_mode => 'R',
787 x_rowid => l_rowid,
788 x_fund_id => l_fund_id,
789 x_tp_cal_type => l_c_igf_aw_cal_rel.sc_cal_type,
790 x_tp_sequence_number => l_c_igf_aw_cal_rel.sc_sequence_number,
791 x_tp_perct => l_c_igf_aw_fund_tp.tp_perct
792 );
793 EXCEPTION
794 WHEN OTHERS THEN
795 -- rolls back to the save point
796 ROLLBACK TO sp_fund;
797 -- log the error message returned by the tbh
798 fnd_message.set_name('IGF',fnd_message.get);
799 igs_ge_msg_stack.add;
800 fnd_file.put_line(fnd_file.log,' ');
801 fnd_file.put_line(fnd_file.log,fnd_message.get);
802 -- raises user defined exception so as to skip the record
803 RAISE l_err_exception;
804 END;
805 END LOOP;
806 fnd_file.put_line(fnd_file.log,' ');
807 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','FEE_CLASS'),32) ||
808 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','PERCENT'),10) ||
809 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
810 fnd_file.put_line(fnd_file.log,l_msg_str_0);
811 fnd_file.put_line(fnd_file.log,' ');
812 -- rolling over pays only fee class details
813 FOR l_c_igf_sp_fc IN c_igf_sp_fc(cp_fund_id => l_c_igf_aw_fund_mast.fund_id)
814 LOOP
815 -- log relevant details
816 l_msg_str_1 := RPAD(l_c_igf_sp_fc.fee_class,32) ||
817 NVL(RPAD(TO_CHAR(l_c_igf_sp_fc.fee_percent),10),' ' )||
818 TO_CHAR(l_c_igf_sp_fc.max_amount);
819 fnd_file.put_line(fnd_file.log,l_msg_str_1);
820 l_rowid := NULL;
821 l_fee_cls_id := NULL;
822 BEGIN
823 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
824 igf_sp_fc_pkg.insert_row (
825 x_mode => 'R',
826 x_rowid => l_rowid,
827 x_fee_cls_id => l_fee_cls_id,
828 x_fund_id => l_fund_id,
829 x_fee_class => l_c_igf_sp_fc.fee_class,
830 x_fee_percent => l_c_igf_sp_fc.fee_percent,
831 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_fc.max_amount)
832 );
833 EXCEPTION
834 WHEN OTHERS THEN
835 -- rolls back to the save point
836 ROLLBACK TO sp_fund;
837 -- log the error message returned by the tbh
838 fnd_file.put_line(fnd_file.log,' ');
839 fnd_file.put_line(fnd_file.log,fnd_message.get);
840 -- raises user defined exception so as to skip the record
841 RAISE l_err_exception;
842 END;
843 IF l_c_igf_sp_fc.fee_class = 'TUITION' THEN
844 -- rolling over pays program details
845 fnd_file.put_line(fnd_file.log,' ');
846 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','COURSE_CD'),12) ||
847 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','VERSION_NUMBER'),16) ||
848 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','PERCENT'),10) ||
849 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
850 fnd_file.put_line(fnd_file.log,l_msg_str_0);
851 fnd_file.put_line(fnd_file.log,' ');
852
853 FOR l_c_igf_sp_prg IN c_igf_sp_prg(cp_fee_cls_id => l_c_igf_sp_fc.fee_cls_id)
854 LOOP
855 -- log relevant details
856 l_msg_str_1 := RPAD(l_c_igf_sp_prg.course_cd,12) ||
857 RPAD(l_c_igf_sp_prg.version_number,16) ||
858 NVL(RPAD(TO_CHAR(l_c_igf_sp_prg.fee_percent),10),' ') ||
859 TO_CHAR(l_c_igf_sp_prg.max_amount);
860 fnd_file.put_line(fnd_file.log,l_msg_str_1);
861 l_rowid := NULL;
862 l_fee_cls_prg_id := NULL;
863 BEGIN
864 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
865 igf_sp_prg_pkg.insert_row (
866 x_mode => 'R',
867 x_rowid => l_rowid,
868 x_fee_cls_prg_id => l_fee_cls_prg_id,
869 x_fee_cls_id => l_fee_cls_id,
870 x_course_cd => l_c_igf_sp_prg.course_cd,
871 x_version_number => l_c_igf_sp_prg.version_number,
872 x_fee_percent => l_c_igf_sp_prg.fee_percent,
873 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_prg.max_amount)
874 );
875 EXCEPTION
876 WHEN OTHERS THEN
877 -- rolls back to the save point
878 ROLLBACK TO sp_fund;
879 -- log the error message returned by the tbh
880 fnd_file.put_line(fnd_file.log,' ');
881 fnd_file.put_line(fnd_file.log,fnd_message.get);
882 -- raises user defined exception so as to skip the record
883 RAISE l_err_exception;
884 END ;
885 -- rollover pays only unit details.
886 fnd_file.put_line(fnd_file.log,' ');
887 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','UNIT_CD'),12) ||
888 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','VERSION_NUMBER'),16) ||
889 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
890 fnd_file.put_line(fnd_file.log,l_msg_str_0);
891 fnd_file.put_line(fnd_file.log,' ');
892 FOR l_c_igf_sp_unit IN c_igf_sp_unit(cp_fee_cls_prg_id => l_c_igf_sp_prg.fee_cls_prg_id)
893 LOOP
894 -- log relevant details
895 l_msg_str_1 := RPAD(l_c_igf_sp_unit.unit_cd,12) ||
896 RPAD(l_c_igf_sp_unit.version_number,16) ||
897 TO_CHAR(l_c_igf_sp_unit.max_amount);
898 fnd_file.put_line(fnd_file.log,l_msg_str_1);
899 l_rowid := NULL;
900 l_fee_cls_unit_id := NULL;
901 BEGIN
902 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
903 igf_sp_unit_pkg.insert_row (
904 x_mode => 'R',
905 x_rowid => l_rowid,
906 x_fee_cls_unit_id => l_fee_cls_unit_id,
907 x_fee_cls_prg_id => l_fee_cls_prg_id,
908 x_unit_cd => l_c_igf_sp_unit.unit_cd,
909 x_version_number => l_c_igf_sp_unit.version_number,
910 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_unit.max_amount)
911 );
912 EXCEPTION
913 WHEN OTHERS THEN
914 -- rolls back to the save point
915 ROLLBACK TO sp_fund;
916 -- log the error message returned by the tbh
917 fnd_file.put_line(fnd_file.log,' ');
918 fnd_file.put_line(fnd_file.log,fnd_message.get);
919 -- raises user defined exception so as to skip the record
920 RAISE l_err_exception;
921 END;
922 END LOOP;
923 fnd_file.put_line(fnd_file.log,' ');
924 END LOOP;
925 END IF;
926 END LOOP;
927 EXCEPTION
928 WHEN l_err_exception THEN
929 NULL;
930 END;
931
932 END sponsor_fund_rollover;
933
934 -- procedure which rollover over the sponsor student relation
935 PROCEDURE sponsor_student_rollover ( p_sc_cal_type IN igs_ca_inst_all.cal_type%TYPE,
936 p_sc_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
937 p_fund IN igf_aw_fund_mast_all.fund_id%TYPE
938 ) IS
939 ------------------------------------------------------------------
940 --Created by : Sanil Madathil, Oracle IDC
941 --Date created: 24 Jan 2001
942 --
943 --Purpose: This procedure is private to this package body .
944 -- The procedure is being called from the sponsor_rollover procedure
945 --
946 --
947 --
948 --Known limitations/enhancements and/or remarks:
949 --
950 --Change History:
951 --Who When What
952 --skharida 12-Jun-2006 Bug#5093981 Appended the Person Number to the log when no FA base is found for the
953 -- person for Awards Sponsorship Rollover process
954 --gurprsin 31-May-2006 Bug 5213852, Logged the new message 'IGF_SP_NO_STDREL_TERM_MAP' and removed the code logic to
955 -- log 'IGF_AW_FND_RLOVR_LD_NTFND' as the later message is obsoleted. This new message
956 -- will be logged when user tries to rollover Sponsor-student relation and the
957 -- associated term calendar mapping does not exist for the destination award year.
958 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igf_sp_stdnt_rel, igf_sp_std_fc,
959 -- igf_sp_std_prg, igf_sp_std_unit are now rounded off to currency precision
960 --vvutukur 07-Mar-2003 Bug#2822725. Removed parameters p_cal_type,p_sequence_number and from the cursor c_igf_aw_fund_mast usage also.
961 ------------------------------------------------------------------
962 -- cursor to select fund code from igf_aw_fund_mast to get fund code for fund id parameter
963 CURSOR c_fund_mast(cp_fund_code igf_aw_fund_mast.fund_code%TYPE,
964 cp_cal_type igs_ca_inst.cal_type%TYPE,
965 cp_sequence_number igs_ca_inst.sequence_number%TYPE
966 ) IS
967 SELECT fmast.*
968 FROM igf_aw_fund_mast fmast ,
969 igf_aw_fund_cat fcat
970 WHERE fmast.fund_code = fcat.fund_code
971 AND fmast.fund_code = cp_fund_code
972 AND fmast.ci_cal_type = cp_cal_type
973 AND fmast.ci_sequence_number = cp_sequence_number
974 AND fcat.sys_fund_type = g_c_fund_type
975 AND fmast.discontinue_fund = g_c_no;
976
977 -- cursor variable for c_fund_mast
978 l_c_fund_mast c_fund_mast%ROWTYPE;
979
980 CURSOR c_igf_aw_fund_tp( cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
981 SELECT *
982 FROM igf_aw_fund_tp_v ftp
983 WHERE ftp.fund_id = cp_fund_id;
984
985 l_c_igf_aw_fund_tp c_igf_aw_fund_tp%ROWTYPE;
986
987 CURSOR c_igf_sp_stdnt_rel(cp_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
988 SELECT *
989 FROM igf_sp_stdnt_rel_v
990 WHERE fund_id = cp_fund_id ;
991
992 l_c_igf_sp_stdnt_rel c_igf_sp_stdnt_rel%ROWTYPE;
993
994
995 CURSOR c_igf_ap_fa_base_rec(cp_person_id igs_pe_person.person_id%TYPE,
996 cp_cal_type igs_ca_inst.cal_type%TYPE,
997 cp_seq_num igs_ca_inst.sequence_number%TYPE
998 ) IS
999 SELECT base_id
1000 FROM igf_ap_fa_base_rec
1001 WHERE person_id = cp_person_id
1002 AND ci_cal_type = cp_cal_type
1003 AND ci_sequence_number = cp_seq_num;
1004
1005 l_c_igf_ap_fa_base_rec c_igf_ap_fa_base_rec%ROWTYPE;
1006
1007 CURSOR c_igf_sp_std_fc(cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE) IS
1008 SELECT *
1009 FROM igf_sp_std_fc_v
1010 WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
1011
1012 l_c_igf_sp_std_fc c_igf_sp_std_fc%ROWTYPE;
1013
1014 CURSOR c_igf_sp_std_prg(cp_fee_cls_id igf_sp_fc.fee_cls_id%TYPE) IS
1015 SELECT *
1016 FROM igf_sp_std_prg
1017 WHERE fee_cls_id = cp_fee_cls_id;
1018
1019 l_c_igf_sp_std_prg c_igf_sp_std_prg%ROWTYPE;
1020
1021 CURSOR c_igf_sp_std_unit(cp_fee_cls_prg_id igf_sp_prg.fee_cls_prg_id%TYPE) IS
1022 SELECT *
1023 FROM igf_sp_std_unit
1024 WHERE fee_cls_prg_id = cp_fee_cls_prg_id;
1025
1026 l_c_igf_sp_std_unit c_igf_sp_std_unit%ROWTYPE;
1027
1028 l_msg_str_0 VARCHAR2(32767) := NULL;
1029 l_msg_str_1 VARCHAR2(32767) := NULL;
1030 l_message_name VARCHAR2(30) := NULL;
1031 l_err_exception EXCEPTION;
1032 l_stud_exception EXCEPTION;
1033 l_rowid VARCHAR2(25);
1034 l_fund_id igf_aw_fund_mast.fund_id%TYPE;
1035 l_spnsr_stdnt_id igf_sp_stdnt_rel_all.spnsr_stdnt_id%TYPE;
1036 l_fee_cls_id igf_sp_fc_all.fee_cls_id%TYPE;
1037 l_fee_cls_prg_id igf_sp_prg_all.fee_cls_prg_id%TYPE;
1038 l_fee_cls_unit_id igf_sp_unit_all.fee_cls_unit_id%TYPE;
1039 l_message VARCHAR2(2000) := NULL;
1040 l_base_id igf_ap_fa_base_rec.base_id%TYPE;
1041 l_ans BOOLEAN := TRUE;
1042 -- cursor variable for c_igf_aw_cal_rel
1043 l_c_igf_aw_cal_rel c_igf_aw_cal_rel%ROWTYPE;
1044 -- cursor variable for c_igf_aw_fund_mast
1045 l_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
1046 BEGIN
1047
1048 OPEN c_igf_aw_fund_mast(cp_fund_id => p_fund);
1049 FETCH c_igf_aw_fund_mast INTO l_c_igf_aw_fund_mast;
1050 CLOSE c_igf_aw_fund_mast;
1051 BEGIN
1052 -- declare a save point
1053 SAVEPOINT sp_student;
1054
1055 -- get the fund id of the succeeding award year for the fund passed to the process
1056 OPEN c_fund_mast (cp_fund_code => l_c_igf_aw_fund_mast.fund_code,
1057 cp_cal_type => p_sc_cal_type,
1058 cp_sequence_number => p_sc_seq_num);
1059 FETCH c_fund_mast INTO l_c_fund_mast;
1060 IF c_fund_mast%NOTFOUND THEN
1061 -- rolls back to the save point
1062 ROLLBACK TO sp_student;
1063 CLOSE c_fund_mast;
1064 fnd_message.set_name('IGF','IGF_SP_NO_FUND_ROLL');
1065 igs_ge_msg_stack.add;
1066 fnd_file.put_line(fnd_file.log,' ');
1067 fnd_file.put_line(fnd_file.log,fnd_message.get);
1068 RAISE l_err_exception;
1069 END IF;
1070 CLOSE c_fund_mast;
1071
1072 -- log the relavant details
1073 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','FUND_CODE'),32) ||
1074 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','AWARD_YEAR'),19) ;
1075 fnd_file.put_line(fnd_file.log,l_msg_str_0);
1076 fnd_file.put_line(fnd_file.log,' ');
1077 l_msg_str_1 := RPAD(l_c_igf_aw_fund_mast.fund_code,32)||
1078 RPAD((p_sc_cal_type||' '||p_sc_seq_num),19);
1079 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1080 fnd_file.put_line(fnd_file.log,' ');
1081 OPEN c_igf_aw_fund_tp(l_c_fund_mast.fund_id);
1082 FETCH c_igf_aw_fund_tp INTO l_c_igf_aw_fund_tp;
1083 IF c_igf_aw_fund_tp%NOTFOUND THEN
1084 -- rolls back to the save point
1085 ROLLBACK TO sp_student;
1086 CLOSE c_igf_aw_fund_tp;
1087 fnd_message.set_name('IGF','IGF_SP_NO_TERM');
1088 igs_ge_msg_stack.add;
1089
1090 -- log the error message
1091 fnd_file.put_line(fnd_file.log,' ');
1092 fnd_file.put_line(fnd_file.log,fnd_message.get);
1093 RAISE l_err_exception;
1094 END IF;
1095 CLOSE c_igf_aw_fund_tp;
1096
1097 -- log the relavant details
1098 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER'),32) ||
1099 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','TERM'),12) ||
1100 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','START_DT'),22) ||
1101 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','END_DT'),22)||
1102 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','MIN_CRD_POINTS'),19) ||
1103 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','MIN_ATD_TYPE'),32) ||
1104 lookup_desc('IGF_AW_LOOKUPS_MSG','TOT_SPNSR_AMT');
1105 fnd_file.put_line(fnd_file.log,l_msg_str_0);
1106 fnd_file.put_line(fnd_file.log,' ');
1107
1108 -- rolling over sponsor student relation for fund and term calendar for the succeeding award year
1109 FOR l_c_igf_sp_stdnt_rel IN c_igf_sp_stdnt_rel(cp_fund_id => l_c_igf_aw_fund_mast.fund_id)
1110 LOOP
1111 BEGIN
1112 -- declare a save point
1113 SAVEPOINT sp_spnsr_student;
1114
1115 -- checks if record exists in fa base record table for the student and award year
1116 OPEN c_igf_ap_fa_base_rec(cp_person_id => l_c_igf_sp_stdnt_rel.person_id,
1117 cp_cal_type => p_sc_cal_type,
1118 cp_seq_num => p_sc_seq_num);
1119 FETCH c_igf_ap_fa_base_rec INTO l_c_igf_ap_fa_base_rec;
1120
1121 -- error out if no fa base record exists for the student award year
1122 IF c_igf_ap_fa_base_rec%NOTFOUND THEN
1123 CLOSE c_igf_ap_fa_base_rec;
1124
1125 -- rolls back to the save point
1126 ROLLBACK TO sp_spnsr_student;
1127 fnd_message.set_name('IGF','IGF_SP_NO_FA_BASE_REC');
1128 igs_ge_msg_stack.add;
1129
1130 -- log the error message
1131 fnd_file.put_line(fnd_file.log,' ');
1132 fnd_file.put_line(fnd_file.log,l_c_igf_sp_stdnt_rel.person_number||' '||fnd_message.get);
1133
1134 -- skips the current student
1135 RAISE l_stud_exception;
1136 END IF;
1137
1138 l_base_id := l_c_igf_ap_fa_base_rec.base_id ;
1139 CLOSE c_igf_ap_fa_base_rec;
1140 -- Check if succeeding terms are present for the current term passed as
1141 -- parameter to the process
1142 OPEN c_igf_aw_cal_rel(cp_cal_type => l_c_igf_sp_stdnt_rel.ld_cal_type,
1143 cp_seq_num => l_c_igf_sp_stdnt_rel.ld_sequence_number);
1144 FETCH c_igf_aw_cal_rel INTO l_c_igf_aw_cal_rel;
1145 IF c_igf_aw_cal_rel%NOTFOUND THEN
1146
1147 -- rolls back to the save point
1148 ROLLBACK TO sp_spnsr_student;
1149 CLOSE c_igf_aw_cal_rel;
1150
1151 --Bug 5213852, Logged the new message 'IGF_SP_NO_STDREL_TERM_MAP' and removed the code logic to
1152 --log 'IGF_AW_FND_RLOVR_LD_NTFND' as the later message is obsoleted.
1153 fnd_message.set_name('IGF','IGF_SP_NO_STDREL_TERM_MAP');
1154 fnd_message.set_token('TERM_ALT_CD',l_c_igf_aw_fund_tp.tp_alternate_code);
1155 fnd_message.set_token('AWD_ALT_CD',l_c_igf_aw_fund_tp.awd_alternate_code);
1156 fnd_message.set_token('STUDENT_NUM',l_c_igf_sp_stdnt_rel.person_number);
1157 fnd_message.set_token('SPONSOR_CD',l_c_igf_aw_fund_tp.fund_code);
1158
1159 igs_ge_msg_stack.add;
1160
1161 -- log the error message
1162 fnd_file.put_line(fnd_file.log,' ');
1163 fnd_file.put_line(fnd_file.log,fnd_message.get);
1164 -- raises user defined exception so as to skip the student record
1165 RAISE l_stud_exception;
1166 END IF;
1167 CLOSE c_igf_aw_cal_rel;
1168
1169 -- log the relavant details
1170 l_msg_str_1 := RPAD(l_c_igf_sp_stdnt_rel.person_number,32)||
1171 RPAD(l_c_igf_aw_cal_rel.sc_alternate_code,12) ||
1172 RPAD(IGS_GE_DATE.IGSCHARDT(l_c_igf_aw_cal_rel.sc_start_dt),22) ||
1173 RPAD(IGS_GE_DATE.IGSCHARDT(l_c_igf_aw_cal_rel.sc_end_dt),22) ||
1174 NVL(RPAD(TO_CHAR(l_c_igf_sp_stdnt_rel.min_credit_points),19),' ') ||
1175 NVL(RPAD(l_c_igf_sp_stdnt_rel.min_attendance_type,32),' ' )||
1176 TO_CHAR(l_c_igf_sp_stdnt_rel.tot_spnsr_amount);
1177
1178 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1179 l_spnsr_stdnt_id := NULL;
1180 l_rowid := NULL;
1181 BEGIN
1182 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1183 igf_sp_stdnt_rel_pkg.insert_row (
1184 x_mode => 'R',
1185 x_rowid => l_rowid,
1186 x_spnsr_stdnt_id => l_spnsr_stdnt_id,
1187 x_fund_id => l_c_fund_mast.fund_id,
1188 x_base_id => l_base_id,
1189 x_person_id => l_c_igf_sp_stdnt_rel.person_id,
1190 x_ld_cal_type => l_c_igf_aw_cal_rel.sc_cal_type,
1191 x_ld_sequence_number => l_c_igf_aw_cal_rel.sc_sequence_number,
1192 x_tot_spnsr_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_stdnt_rel.tot_spnsr_amount),
1193 x_min_credit_points => l_c_igf_sp_stdnt_rel.min_credit_points,
1194 x_min_attendance_type => l_c_igf_sp_stdnt_rel.min_attendance_type
1195 );
1196 EXCEPTION
1197 WHEN OTHERS THEN
1198 -- rolls back to the save point
1199 ROLLBACK TO sp_spnsr_student;
1200 -- log the error message returned by the tbh
1201 fnd_file.put_line(fnd_file.log,fnd_message.get);
1202 -- raises user defined exception so as to skip the record
1203 RAISE l_stud_exception;
1204 END ;
1205
1206 -- log relevant details
1207 fnd_file.put_line(fnd_file.log,' ');
1208 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','FEE_CLASS'),32) ||
1209 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','PERCENT'),10) ||
1210 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
1211 fnd_file.put_line(fnd_file.log,l_msg_str_0);
1212 fnd_file.put_line(fnd_file.log,' ');
1213 -- rolling over fee class details
1214 FOR l_c_igf_sp_std_fc IN c_igf_sp_std_fc(cp_spnsr_stdnt_id => l_c_igf_sp_stdnt_rel.spnsr_stdnt_id)
1215 LOOP
1216 -- log relevant details
1217 l_msg_str_1 := RPAD(l_c_igf_sp_std_fc.fee_class,32) ||
1218 NVL(RPAD(TO_CHAR(l_c_igf_sp_std_fc.fee_percent),10),' ' )||
1219 TO_CHAR(l_c_igf_sp_std_fc.max_amount);
1220 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1221 l_rowid := NULL;
1222 l_fee_cls_id := NULL;
1223 BEGIN
1224 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1225 igf_sp_std_fc_pkg.insert_row (
1226 x_mode => 'R',
1227 x_rowid => l_rowid,
1228 x_fee_cls_id => l_fee_cls_id,
1229 x_spnsr_stdnt_id => l_spnsr_stdnt_id,
1230 x_fee_class => l_c_igf_sp_std_fc.fee_class,
1231 x_fee_percent => l_c_igf_sp_std_fc.fee_percent,
1232 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_std_fc.max_amount)
1233 );
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 -- rolls back to the save point
1237 ROLLBACK TO sp_student;
1238 -- log the error message returned by the tbh
1239 fnd_file.put_line(fnd_file.log,fnd_message.get);
1240 -- raises user defined exception so as to skip the record
1241 RAISE l_err_exception;
1242 END;
1243 IF l_c_igf_sp_std_fc.fee_class = 'TUITION' THEN
1244 -- log relevant details
1245 fnd_file.put_line(fnd_file.log,' ');
1246 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','COURSE_CD'),12) ||
1247 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','VERSION_NUMBER'),16) ||
1248 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','PERCENT'),10) ||
1249 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
1250 fnd_file.put_line(fnd_file.log,l_msg_str_0);
1251 fnd_file.put_line(fnd_file.log,' ');
1252 -- rolling over program details
1253 FOR l_c_igf_sp_std_prg IN c_igf_sp_std_prg(cp_fee_cls_id => l_c_igf_sp_std_fc.fee_cls_id)
1254 LOOP
1255 -- log relevant details
1256 l_msg_str_1 := RPAD(l_c_igf_sp_std_prg.course_cd,12) ||
1257 RPAD(l_c_igf_sp_std_prg.version_number,16) ||
1258 NVL(RPAD(TO_CHAR(l_c_igf_sp_std_prg.fee_percent),10),' ') ||
1259 TO_CHAR(l_c_igf_sp_std_prg.max_amount);
1260 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1261 l_fee_cls_prg_id := NULL;
1262 l_rowid := NULL;
1263 BEGIN
1264 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1265 igf_sp_std_prg_pkg.insert_row (
1266 x_mode => 'R',
1267 x_rowid => l_rowid,
1268 x_fee_cls_prg_id => l_fee_cls_prg_id,
1269 x_fee_cls_id => l_fee_cls_id,
1270 x_course_cd => l_c_igf_sp_std_prg.course_cd,
1271 x_version_number => l_c_igf_sp_std_prg.version_number,
1272 x_fee_percent => l_c_igf_sp_std_prg.fee_percent,
1273 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_std_prg.max_amount)
1274 );
1275 EXCEPTION
1276 WHEN OTHERS THEN
1277 -- rolls back to the save point
1278 ROLLBACK TO sp_student;
1279 -- log the error message returned by the tbh
1280 fnd_file.put_line(fnd_file.log,fnd_message.get);
1281 -- raises user defined exception so as to skip the record
1282 RAISE l_err_exception;
1283 END;
1284 fnd_file.put_line(fnd_file.log,' ');
1285 l_msg_str_0 := RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','UNIT_CD'),12) ||
1286 RPAD(lookup_desc('IGF_AW_LOOKUPS_MSG','VERSION_NUMBER'),16) ||
1287 lookup_desc('IGF_AW_LOOKUPS_MSG','MAX_AMOUNT');
1288 fnd_file.put_line(fnd_file.log,l_msg_str_0);
1289 fnd_file.put_line(fnd_file.log,' ');
1290 -- rolling over unit details
1291 FOR l_c_igf_sp_std_unit IN c_igf_sp_std_unit(cp_fee_cls_prg_id => l_c_igf_sp_std_prg.fee_cls_prg_id)
1292 LOOP
1293 -- log relevant details
1294 l_msg_str_1 := RPAD(l_c_igf_sp_std_unit.unit_cd,12) ||
1295 RPAD(l_c_igf_sp_std_unit.version_number,16) ||
1296 TO_CHAR(l_c_igf_sp_std_unit.max_amount);
1297 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1298 l_fee_cls_unit_id := NULL;
1299 l_rowid := NULL;
1300 BEGIN
1301 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1302 igf_sp_std_unit_pkg.insert_row (
1303 x_mode => 'R',
1304 x_rowid => l_rowid,
1305 x_fee_cls_unit_id => l_fee_cls_unit_id,
1306 x_fee_cls_prg_id => l_fee_cls_prg_id,
1307 x_unit_cd => l_c_igf_sp_std_unit.unit_cd,
1308 x_version_number => l_c_igf_sp_std_unit.version_number,
1309 x_max_amount => igs_fi_gen_gl.get_formatted_amount(l_c_igf_sp_std_unit.max_amount)
1310 );
1311 EXCEPTION
1312 WHEN OTHERS THEN
1313 -- rolls back to the save point
1314 ROLLBACK TO sp_student;
1315 -- log the error message returned by the tbh
1316 fnd_file.put_line(fnd_file.log,fnd_message.get);
1317 -- raises user defined exception so as to skip the record
1318 RAISE l_err_exception;
1319 END;
1320 END LOOP;
1321 fnd_file.put_line(fnd_file.log,' ');
1322 END LOOP;
1323 END IF;
1324 END LOOP;
1325 EXCEPTION
1326 WHEN l_stud_exception THEN
1327 NULL;
1328 END;
1329 END LOOP;
1330
1331 EXCEPTION
1332 WHEN l_err_exception THEN
1333 NULL;
1334 END;
1335 END sponsor_student_rollover;
1336
1337
1338 END igf_sp_rollover ;
[Home] [Help]