1 PACKAGE BODY igf_sp_award AS
2 /* $Header: IGFSP03B.pls 120.9 2006/08/11 05:39:56 rajagupt ship $ */
3
4 ------------------------------------------------------------------------------------
5 --Created by : smanglm ( Oracle IDC)
6 --Date created: 2002/01/11
7 --
8 --Purpose: Created as part of the build for DLD Sponsorship
9 -- This is a batch process that created both Award and disbursement
10 -- for a fund in FA system. Process will also check for the eligibility
11 -- and validations before awarding the Sponsor amount to the students.
12 -- Awarding money to the students can be done manually apart from awarding
13 -- money through a batch process.
14 --
15 --Known limitations/enhancements and/or remarks:
16 --
17 --Change History:
18 --Who When What
19 --sapanigr 26-Jun-2006 Bug 5083572 Awards - Assign Sponsorship Awards (Wrong Msg. Logged)
20 -- In create_aw_award procedure, Logging the message 'New Award is created for the
21 -- person with the following details.' only once before actual creation of Disbursement.
22 -- in the loop of fetched FC records.
23 --akomurav 06-jun-2006 Bug 5276122 - Made the changes required(TBH Impact) for adding 2 new columns in IGF_DB_AWD_DISB_DTL_ALL table
24 --pathipat 18-May-2006 Bug 5194095 - Modified create_award_disb, loop_thru_spnsr_dtl_pvt and recal_dis_gross_amt
25 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified create_disb_dtl, create_aw_award, loop_thru_spnsr_dtl_pvt
26 --ayedubat 13-OCT-04 FA 149 COD-XML Standards build bug # 3416863
27 -- Changed the TBH calls of the packages: igf_aw_awd_disb_pkg and igf_db_awd_disb_dtl_pkg
28 --veramach July 2004 FA 151 HR Integration(bug #3709292)
29 -- Impact of obsoleting columns from fund manager
30 -- bkkumar 04-DEC-2003 Bug 3252382 FA 131 . TBH impact for the igf_aw_awd_disb_all
31 -- Added two columns ATTENDANCE_TYPE_CODE,BASE_ATTENDANCE_TYPE_CODE
32 -- TBH impact of the igf_aw_award Added columns LOCK_AWARD_FLAG,
33 -- APP_TRANS_NUM_TXT
34 --vvutukur 20-Jul-2003 Enh#3038511.FICR106 Build. Modified procedure create_award_disb.
35 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped, Dynamic Person Group feature is introduced,
36 -- Sponsor Code parameter is made optional
37 --bkkumar #2858504 04-jun-2003 Added legacy_record_flag and award_number_txt in the table handler calls for igf_aw_award_pkg.insert_row
38 --pathipat 25-Apr-2003 Enh 2831569 - Commercial Receivables build
39 -- Modified create_award_disb() - added call to chk_manage_account()
40 --vvutukur 26-feb-2003 Enh#2758823.FA117 Build. Modified the procedure create_disb_dtl.
41 --smadathi 30-Jan-2002 Bug 2620302. Function et_person_number ,procedure create_award_disb modified.
42 --adhawan #2613546 28-oct-2002 Added alt_pell_schedule in the table handler calls for igf_aw_award_pkg.insert_row
43
44 -- adhawan #2613546 27-oct-2002 gscc fix for Default
45 --smadathi 02-jul-2002 Bug 2427996. Modified create_award_disb procedure.
46 --smadathi 31-Jun-2002 Bug 2387604. Modified create_aw_award procedure, loop_thru_spnsr_dtl Procedure.
47 --smadathi 11-Jun-2002 Bug 2387572. procedure create_aw_award,loop_thru_spnsr_dtl_pvt modified.
48 --smadathi 31-May-2002 Bug 2387344. procedure create_award_disb,Procedure loop_thru_spnsr_dtl_pvt
49 -- modified. Function lookup_desc, log_messages added newly.
50 --smadathi 17-May-2002 Bug 2369173. Function recal_dis_gross_amt ,procedure create_aw_award,
51 -- procedure create_award_disb, Procedure loop_thru_spnsr_dtl_pvt
52 -- modified.
53
54 --vvutukur 15-apr-2002 Modifications done in create_aw_award procedure for bug#2293676.
55 -------------------------------------------------------------------------------------
56
57 -- Global Variable defined for holding the lookup meaning for generating the log file details
58 g_v_award_yr igf_lookups_view.meaning%TYPE;
59 g_v_term igf_lookups_view.meaning%TYPE;
60 g_v_person_num_pmt igf_lookups_view.meaning%TYPE;
61 g_v_person_group igf_lookups_view.meaning%TYPE;
62 g_v_spnr_cd igf_lookups_view.meaning%TYPE;
63 g_v_spnr_desc igf_lookups_view.meaning%TYPE;
64 g_v_award_type igf_lookups_view.meaning%TYPE;
65 g_v_test_mode igf_lookups_view.meaning%TYPE;
66 g_v_award_id igf_lookups_view.meaning%TYPE;
67 g_v_disb_fee_class igf_lookups_view.meaning%TYPE;
68 g_v_disb_amount igf_lookups_view.meaning%TYPE;
69 g_v_award_amount igf_lookups_view.meaning%TYPE;
70 g_v_ext_disb_amount igf_lookups_view.meaning%TYPE;
71 g_v_upd_disb_amount igf_lookups_view.meaning%TYPE;
72 g_v_ext_award_amount igf_lookups_view.meaning%TYPE;
73 g_v_upd_award_amount igf_lookups_view.meaning%TYPE;
74 g_v_person_number hz_parties.party_number%TYPE;
75 g_b_records_found BOOLEAN := FALSE;
76 g_b_award_updated BOOLEAN := FALSE;
77 g_b_msg_logged BOOLEAN := FALSE;
78 g_v_log_text VARCHAR2(32000);
79 g_rowid VARCHAR2(25);
80
81
82 -- function to return meaning for the lookup code and lookup type passed
83 -- as parameter.
84 FUNCTION lookup_desc( p_type IN VARCHAR2 ,
85 p_code IN VARCHAR2 )
86 RETURN VARCHAR2 IS
87 ------------------------------------------------------------------
88 --Created by : Sanil Madathil, Oracle IDC
89 --Date created: 31 May 2002
90 --
91 --Purpose: This function is private to this package body .
92 --
93 --
94 --
95 --
96 --Known limitations/enhancements and/or remarks:
97 --
98 --Change History:
99 --Who When What
100 -------------------------------------------------------------------
101
102 CURSOR c_desc( cp_type igs_lookups_view.lookup_type%TYPE ,
103 cp_code igs_lookups_view.lookup_code%TYPE ) IS
104 SELECT meaning
105 FROM igf_lookups_view
106 WHERE lookup_type = cp_type
107 AND lookup_code = cp_code;
108
109 l_desc igf_lookups_view.meaning%TYPE ;
110
111 BEGIN
112 IF p_code IS NULL THEN
113 RETURN NULL;
114 ELSE
115 OPEN c_desc(cp_type => p_type,
116 cp_code => p_code
117 );
118 FETCH c_desc INTO l_desc ;
119 CLOSE c_desc ;
120 END IF ;
121 RETURN l_desc ;
122 END lookup_desc;
123
124
125 FUNCTION get_show_on_bill(p_n_fund_id NUMBER) RETURN VARCHAR2
126 IS
127 /*----------------------------------------------------------------------------
128 Created By : Vinay Chappidi
129 Created On : 18-Jun-2003
130 Purpose : Generic Function returning the show-on-bill indicator for a Fund ID
131 Known limitations, enhancements or remarks :
132 Change History :
133 Who When What
134 (reverse chronological order - newest change first)
135 ----------------------------------------------------------------------------*/
136 CURSOR cur_include_as_plncrd(cp_fund_id igf_aw_fund_mast.fund_id%TYPE)
137 IS
138 SELECT show_on_bill
139 FROM igf_aw_fund_mast
140 WHERE fund_id = cp_fund_id;
141 l_v_show_on_bill igf_aw_fund_mast.show_on_bill%TYPE;
142 BEGIN
143 OPEN cur_include_as_plncrd(p_n_fund_id);
144 FETCH cur_include_as_plncrd INTO l_v_show_on_bill ;
145 CLOSE cur_include_as_plncrd;
146 RETURN l_v_show_on_bill;
147 END get_show_on_bill;
148
149
150
151 FUNCTION get_cal_inst_dtls (p_c_cal_type VARCHAR2, p_n_seq_number NUMBER) RETURN VARCHAR2
152 IS
153 /*----------------------------------------------------------------------------
154 Created By : Vinay Chappidi
155 Created On : 18-Jun-2003
156 Purpose : Generic Function returning the concatenated Calendar Instance details
157 Known limitations, enhancements or remarks :
158 Change History :
159 Who When What
160 (reverse chronological order - newest change first)
161 ----------------------------------------------------------------------------*/
162
163 -- Cursor to select the details for the award year or term calendar passed to the process
164 CURSOR c_ca_inst( cp_c_cal_type igs_ca_inst.cal_type%TYPE,
165 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE)
166 IS
167 SELECT alternate_code,
168 start_dt,
169 end_dt
170 FROM igs_ca_inst
171 WHERE cal_type = cp_c_cal_type
172 AND sequence_number = cp_n_sequence_number;
173 -- cursor variable for c_igs_ca_inst
174 l_v_ca_inst c_ca_inst%ROWTYPE;
175 BEGIN
176 OPEN c_ca_inst(p_c_cal_type, p_n_seq_number);
177 FETCH c_ca_inst INTO l_v_ca_inst;
178 IF c_ca_inst%NOTFOUND THEN
179 RETURN NULL;
180 ELSE
181 RETURN (l_v_ca_inst.alternate_code||' '||l_v_ca_inst.start_dt||' - '|| l_v_ca_inst.end_dt);
182 END IF;
183 CLOSE c_ca_inst;
184 END get_cal_inst_dtls;
185
186
187 FUNCTION get_award_amount(p_n_award_id NUMBER) RETURN NUMBER
188 IS
189 /*----------------------------------------------------------------------------
190 Created By : Vinay Chappidi
191 Created On : 18-Jun-2003
192 Purpose : Generic function returning the Award Amount
193 Known limitations, enhancements or remarks :
194 Change History :
195 Who When What
196 (reverse chronological order - newest change first)
197 ----------------------------------------------------------------------------*/
198 CURSOR c_award_amount(cp_n_award_id igf_aw_award.award_id%TYPE)
199 IS
200 SELECT accepted_amt
201 FROM igf_aw_award
202 WHERE award_id = cp_n_award_id;
203 l_n_award_amount igf_aw_award.accepted_amt%TYPE;
204 BEGIN
205 OPEN c_award_amount(p_n_award_id);
206 FETCH c_award_amount INTO l_n_award_amount;
207 CLOSE c_award_amount;
208 RETURN l_n_award_amount;
209 END get_award_amount;
210
211
212 PROCEDURE initialize IS
213 /******************************************************************
214 Created By : Vinay Chappidi
215 Created On : 18-Jun-2003
216 Purpose : Procedure for initializing the global variables
217 Known limitations, enhancements or remarks :
218 Change History :
219 Who When What
220 ******************************************************************/
221 BEGIN
222
223 -- Initialize all the constant lables/translatable text for the process
224 g_v_award_yr := lookup_desc('IGF_AW_LOOKUPS_MSG', 'AWARD_YEAR');
225 g_v_term := lookup_desc('IGF_AW_LOOKUPS_MSG', 'TERM');
226 g_v_person_num_pmt := lookup_desc('IGF_AW_LOOKUPS_MSG', 'PERSON_NUMBER');
227 g_v_person_group := lookup_desc('IGF_AW_LOOKUPS_MSG', 'PERSON_GROUP');
228 g_v_spnr_cd := lookup_desc('IGF_AW_LOOKUPS_MSG', 'SPONSOR_CD');
229 g_v_spnr_desc := lookup_desc('IGF_AW_LOOKUPS_MSG', 'SPONSOR_DESC');
230 g_v_award_type := lookup_desc('IGF_AW_LOOKUPS_MSG', 'AWARD_TYPE');
231 g_v_test_mode := lookup_desc('IGF_AW_LOOKUPS_MSG', 'TEST_MODE');
232 g_v_award_id := lookup_desc('IGF_AW_LOOKUPS_MSG', 'AWARD_ID');
233 g_v_disb_fee_class := lookup_desc('IGF_AW_LOOKUPS_MSG', 'DISB_FEE_CLASS');
234 g_v_disb_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'DISB_AMOUNT');
235 g_v_award_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'AWARD_AMT');
236 g_v_ext_disb_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'EXT_DISB_AMT');
237 g_v_upd_disb_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'UPD_DISB_AMT');
238 g_v_ext_award_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'EXT_AWARD_AMT');
239 g_v_upd_award_amount := lookup_desc('IGF_AW_LOOKUPS_MSG', 'UPD_AWARD_AMT');
240 END initialize;
241
242
243 -- Routine to log parameters.
244 PROCEDURE log_parameters ( p_v_parm_type IN VARCHAR2, p_v_parm_code IN VARCHAR2 )
245 AS
246 /*----------------------------------------------------------------------------
247 Created By : Vinay Chappidi
248 Created On : 18-Jun-2003
249 Purpose : To log input parameters to the process
250 Known limitations, enhancements or remarks :
251 Change History :
252 Who When What
253 (reverse chronological order - newest change first)
254 ----------------------------------------------------------------------------*/
255 BEGIN
256 fnd_file.put_line(fnd_file.log, p_v_parm_type || ' : ' || p_v_parm_code );
257 END log_parameters;
258
259
260 FUNCTION get_person_number (p_person_id igs_pe_person.person_id%TYPE)
261 RETURN VARCHAR2
262 AS
263 ------------------------------------------------------------------------------------
264 --Created by : smanglm ( Oracle IDC)
265 --Date created: 2002/01/11
266 --
267 --Purpose: Created as part of the build for DLD Sponsorship
268 -- this function return person number
269 --
270 -- parameter description:
271 -- p_person_id - Person ID
272 --
273 --Known limitations/enhancements and/or remarks:
274 --
275 --Change History:
276 --Who When What
277 --smadathi 30-Jan-2002 Bug 2620302. Cursor c_person_number select modified
278 -- to fetch the records from view igs_pe_person_base_v
279 -- instead of igs_pe_person. This fix is done to remove
280 -- Non-mergablity due to igs_pe_person view
281 -------------------------------------------------------------------------------------
282 -- cursor to get person number
283 CURSOR c_person_number (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
284 SELECT person_number
285 FROM igs_pe_person_base_v
286 WHERE person_id = cp_person_id;
287
288 l_person_number igs_pe_person.person_number%TYPE;
289 BEGIN
290 -- get the person number
291 OPEN c_person_number (p_person_id);
292 FETCH c_person_number INTO l_person_number;
293 CLOSE c_person_number;
294 RETURN l_person_number;
295 END get_person_number;
296
297 FUNCTION check_eligibility ( p_person_id igf_sp_stdnt_rel.person_id%TYPE,
298 p_min_att_type igf_sp_stdnt_rel.min_attendance_type%TYPE,
299 p_min_credit_points igf_sp_stdnt_rel.min_credit_points%TYPE,
300 p_ld_cal_type igf_sp_stdnt_rel.ld_cal_type%TYPE,
301 p_ld_sequence_number igf_sp_stdnt_rel.ld_sequence_number%TYPE)
302 RETURN BOOLEAN
303 AS
304 ------------------------------------------------------------------------------------
305 --Created by : smanglm ( Oracle IDC)
306 --Date created: 2002/01/11
307 --
308 --Purpose: Created as part of the build for DLD Sponsorship
309 -- this procedure checks the eligibility of the student
310 --
311 --
312 --Known limitations/enhancements and/or remarks:
313 --
314 --Change History:
315 --Who When What
316 --smadathi 17-May-2002 Bug 2369173. Incorpoarted condition to return true when
317 -- min. credit points and min. attd. tpe has not been provided
318 -- in the sponsor student relation. Moreover, the existing
319 -- comparison operators used for min.credit points and
320 -- Min. attendance type is changed.
321 -------------------------------------------------------------------------------------
322 l_min_credit_points igf_sp_stdnt_rel.min_credit_points%TYPE;
323 l_min_attendance_type igf_sp_stdnt_rel.min_attendance_type%TYPE;
324 l_fte VARCHAR2 (10); --to be verified
325
326 BEGIN
327
328 -- IF Min attendance type and min. credit points are not provided ,
329 -- eligibility check should be skipped.
330 IF p_min_att_type IS NULL AND p_min_credit_points IS NULL THEN
331 RETURN TRUE;
332 END IF;
333
334 igs_en_prc_load.enrp_get_inst_latt (p_person_id,
335 p_ld_cal_type,
336 p_ld_sequence_number,
337 l_min_attendance_type,
338 l_min_credit_points,
339 l_fte);
340
341 IF (l_min_attendance_type <> p_min_att_type) OR
342 (l_min_credit_points < p_min_credit_points) THEN
343 RETURN FALSE;
344 END IF;
345 RETURN TRUE;
346 END check_eligibility;
347
348 PROCEDURE create_disb_dtl(p_award_id igf_aw_award_all.award_id%TYPE,
349 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
350 AS
351 -------------------------------------------------------------------------------------
352 --Created by : smanglm ( Oracle IDC)
353 --Date created: 2002/01/11
354 --
355 --Purpose: Created as part of the build for DLD Sponsorship
356 -- this procedure checks the eligibility of the student
357 --
358 --
359 --Known limitations/enhancements and/or remarks:
360 --
361 --Change History:
362 --Who When What
363 -- sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igf_db_awd_disb_dtl
364 -- are now rounded off to currency precision
365 --vvutukur 26-Feb-2003 Enh#2758823.FA117 Build. Assigned value 'D' to disb_dtl_rec.disb_activity, instead of NULL.
366 -------------------------------------------------------------------------------------
367
368 CURSOR cur_chk_adj( cp_award_id igf_aw_award_all.award_id%TYPE,
369 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
370 IS
371 SELECT NVL(disb_seq_num,0) disb_seq_num
372 FROM igf_db_awd_disb_dtl
373 WHERE award_id = cp_award_id AND
374 disb_num = cp_disb_num;
375 chk_adj_rec cur_chk_adj%ROWTYPE;
376
377 CURSOR cur_get_adisb( cp_award_id igf_aw_award_all.award_id%TYPE,
378 cp_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
379 IS
380 SELECT *
381 FROM igf_aw_awd_disb
382 WHERE award_id = cp_award_id AND
383 disb_num = cp_disb_num;
384 get_adisb_rec cur_get_adisb%ROWTYPE;
385
386 disb_dtl_rec igf_db_awd_disb_dtl%ROWTYPE;
387 BEGIN
388 -- Check if any adjustment record is present for this award
389 OPEN cur_chk_adj (p_award_id, p_disb_num);
390 FETCH cur_chk_adj INTO chk_adj_rec;
391 IF cur_chk_adj%FOUND THEN
392 -- No need to create adjustement, as it is already present
393 CLOSE cur_chk_adj;
394 ELSIF cur_chk_adj%NOTFOUND THEN
395 CLOSE cur_chk_adj;
396
397 OPEN cur_get_adisb(p_award_id, p_disb_num);
398 FETCH cur_get_adisb INTO get_adisb_rec;
399 CLOSE cur_get_adisb;
400
401 -- Create transaction record in disbursement detail table
402 disb_dtl_rec.award_id := p_award_id;
403 disb_dtl_rec.disb_num := p_disb_num;
404 disb_dtl_rec.disb_seq_num := 1;
405 disb_dtl_rec.disb_gross_amt := get_adisb_rec.disb_gross_amt;
406 disb_dtl_rec.fee_1 := get_adisb_rec.fee_1;
407 disb_dtl_rec.fee_2 := get_adisb_rec.fee_1;
408 disb_dtl_rec.disb_net_amt := get_adisb_rec.disb_net_amt;
409 disb_dtl_rec.disb_adj_amt := 0;
410 disb_dtl_rec.disb_date := get_adisb_rec.disb_date;
411 disb_dtl_rec.fee_paid_1 := get_adisb_rec.fee_paid_1;
412 disb_dtl_rec.fee_paid_2 := get_adisb_rec.fee_paid_1;
413 disb_dtl_rec.sf_status := 'R'; -- Ready to Send
414 disb_dtl_rec.sf_status_date := TRUNC(SYSDATE);
415 disb_dtl_rec.disb_activity := 'D';
416 disb_dtl_rec.disb_status := NULL;
417 disb_dtl_rec.disb_status_date := NULL;
418 g_rowid := NULL;
419
420 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
421 igf_db_awd_disb_dtl_pkg.insert_row( x_rowid => g_rowid,
422 x_award_id => disb_dtl_rec.award_id ,
423 x_disb_num => disb_dtl_rec.disb_num ,
424 x_disb_seq_num => disb_dtl_rec.disb_seq_num ,
425 x_disb_gross_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_gross_amt) ,
426 x_fee_1 => disb_dtl_rec.fee_1 ,
427 x_fee_2 => disb_dtl_rec.fee_2 ,
428 x_disb_net_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_net_amt) ,
429 x_disb_adj_amt => igs_fi_gen_gl.get_formatted_amount(disb_dtl_rec.disb_adj_amt) ,
430 x_disb_date => disb_dtl_rec.disb_date ,
431 x_fee_paid_1 => disb_dtl_rec.fee_paid_1 ,
432 x_fee_paid_2 => disb_dtl_rec.fee_paid_2 ,
433 x_disb_activity => disb_dtl_rec.disb_activity ,
434 x_disb_batch_id => NULL,
435 x_disb_ack_date => NULL,
436 x_booking_batch_id => NULL,
437 x_booked_date => NULL,
438 x_disb_status => NULL,
439 x_disb_status_date => NULL,
440 x_sf_status => disb_dtl_rec.sf_status ,
441 x_sf_status_date => disb_dtl_rec.sf_status_date ,
442 x_sf_invoice_num => disb_dtl_rec.sf_invoice_num ,
443 x_spnsr_credit_id => disb_dtl_rec.spnsr_credit_id ,
444 x_spnsr_charge_id => disb_dtl_rec.spnsr_charge_id ,
445 x_sf_credit_id => disb_dtl_rec.sf_credit_id ,
446 x_error_desc => disb_dtl_rec.error_desc ,
447 x_mode => 'R',
448 x_notification_date => disb_dtl_rec.notification_date,
449 x_interest_rebate_amt => NULL,
450 x_ld_cal_type => get_adisb_rec.ld_cal_type,
451 x_ld_sequence_number => get_adisb_rec.ld_sequence_number
452 );
453 END IF;
454 END create_disb_dtl;
455
456 FUNCTION recal_dis_gross_amt (p_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE,
457 p_chk_elig VARCHAR2,
458 p_fee_cls_id igf_sp_std_fc.fee_cls_id%TYPE
459 )
460 RETURN NUMBER
461 AS
462 ------------------------------------------------------------------------------------
463 --Created by : smanglm ( Oracle IDC)
464 --Date created: 2002/01/11
465 --
466 --Purpose: Created as part of the build for DLD Sponsorship
467 -- this procedure is to write into log file
468 --
469 --Known limitations/enhancements and/or remarks:
470 --
471 --Change History:
472 --Who When What
473 --pathipat 18-May-2006 Bug 5194095 - Added code related to calculations of
474 -- Eligible/New Sponsor amounts.
475 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped
476 --smadathi 17-May-2002 Bug 2369173. Division by 100 was incorporated where
477 -- pays only percent was involved in calculation.
478 -------------------------------------------------------------------------------------
479 -- cursor to find the tot_spnsr_amt
480 CURSOR c_stdnt_rel (cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE)
481 IS
482 SELECT spstd.*, fmast.fund_code
483 FROM igf_sp_stdnt_rel_all spstd,
484 igf_aw_fund_mast_all fmast
485 WHERE spstd.spnsr_stdnt_id = cp_spnsr_stdnt_id
486 AND fmast.fund_id = spstd.fund_id;
487 rec_stdnt_rel c_stdnt_rel%ROWTYPE;
488
489 -- cursor to get the charge at fee class
490 CURSOR c_std_fc (cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE,
491 cp_fee_cls_id igf_sp_std_fc.fee_cls_id%TYPE)
492 IS
493 SELECT *
494 FROM igf_sp_std_fc
495 WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id
496 AND fee_cls_id = NVL(cp_fee_cls_id,fee_cls_id);
497
498 -- cursor to get the charge at program level
499 CURSOR c_std_prg (cp_fee_cls_id igf_sp_std_prg.fee_cls_id%TYPE)
500 IS
501 SELECT *
502 FROM igf_sp_std_prg
503 WHERE fee_cls_id = cp_fee_cls_id;
504
505 -- cursor to get the charge at unit level
506 CURSOR c_std_unit (cp_fee_cls_prg_id igf_sp_std_unit.fee_cls_prg_id%TYPE)
507 IS
508 SELECT *
509 FROM igf_sp_std_unit
510 WHERE fee_cls_prg_id = cp_fee_cls_prg_id;
511 rec_std_unit c_std_unit%ROWTYPE;
512
513 -- cursor to check forprg attempt
514 CURSOR c_prg_attempt (cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
515 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
516 cp_course_version_number igs_en_stdnt_ps_att.version_number%TYPE,
517 cp_v_load_cal_type igs_ca_inst_all.cal_type%TYPE,
518 cp_n_load_seq_num igs_ca_inst_all.sequence_number%TYPE) IS
519 SELECT 'x'
520 FROM igs_en_stdnt_ps_att psatt
521 WHERE person_id = cp_person_id
522 AND course_cd = cp_course_cd
523 AND version_number = cp_course_version_number
524 AND course_attempt_status IN ('ENROLLED','INACTIVE','DISCONTIN')
525 AND EXISTS ( SELECT 1
526 FROM igs_en_su_attempt_all sua
527 WHERE sua.person_id = psatt.person_id
528 AND sua.course_cd = psatt.course_cd
529 AND sua.unit_attempt_status IN ('ENROLLED','COMPLETED')
530 AND (cal_type, ci_sequence_number) IN ( SELECT teach_cal_type, teach_ci_sequence_number
531 FROM igs_ca_load_to_teach_v
532 WHERE load_cal_type = cp_v_load_cal_type
533 AND load_ci_sequence_number = cp_n_load_seq_num)
534 );
535
536 l_prg_attempt VARCHAR2(1);
537
538 l_tot_spnsr_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
539
540 l_msg_count NUMBER;
541 l_msg_data VARCHAR2(2000);
542 l_status BOOLEAN;
543
544 l_v_fee_cal_type igs_ca_inst_all.cal_type%TYPE;
545 l_n_fee_ci_seq_num igs_ca_inst_all.sequence_number%TYPE;
546 l_v_message_name fnd_new_messages.message_name%TYPE;
547 l_n_eligible_spns_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
548 l_n_computed_spns_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
549 l_n_new_spns_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
550 l_n_spns_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
551 l_n_final_spns_amount igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
552
553 BEGIN
554 -- get the tot_spsnr_amt
555 OPEN c_stdnt_rel (p_spnsr_stdnt_id);
556 FETCH c_stdnt_rel INTO rec_stdnt_rel;
557 CLOSE c_stdnt_rel;
558
559 -- Fetch the Fee Calendar mapped to the Load Calendar
560 IF NOT(igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => rec_stdnt_rel.ld_cal_type,
561 p_ci_sequence_number => rec_stdnt_rel.ld_sequence_number,
562 p_cal_category => 'LOAD',
563 p_ret_cal_type => l_v_fee_cal_type,
564 p_ret_ci_sequence_number => l_n_fee_ci_seq_num,
565 p_message_name => l_v_message_name)) THEN
566 -- If there was any error in retrieving the calendar info, return 0
567 IF l_v_message_name IS NOT NULL THEN
568 fnd_message.set_name('IGS',l_v_message_name);
569 fnd_file.put_line(fnd_file.log,fnd_message.get);
570 RETURN 0;
571 END IF;
572 END IF;
573
574 IF rec_stdnt_rel.tot_spnsr_amount IS NOT NULL THEN
575 -- If param p_chk_elig holds N (Planned), then return the Total Sponsor Amount
576 IF (p_chk_elig = 'N') THEN
577 RETURN rec_stdnt_rel.tot_spnsr_amount;
578 ELSE
579 -- If p_chk_elig is 'Y' (Actual), then the New Sponsor amount will be the lesser of
580 -- Eligible Sponsor Amount and the Total Sponsor Amount.
581
582 -- Fetch the sponsor amounts
583 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
584 p_v_fee_cal_type => l_v_fee_cal_type,
585 p_n_fee_seq_number => l_n_fee_ci_seq_num,
586 p_v_fund_code => rec_stdnt_rel.fund_code,
587 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
588 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
589 p_v_fee_class => NULL,
590 p_v_course_cd => NULL,
591 p_v_unit_cd => NULL,
592 p_n_unit_ver_num => NULL,
593 x_eligible_amount => l_n_eligible_spns_amount,
594 x_new_spnsp_amount => l_n_computed_spns_amount);
595 -- Determine New Sponsor amount as lesser of Eligible and Computed sponsor amount
596 IF (l_n_eligible_spns_amount = l_n_computed_spns_amount) THEN
597 l_n_new_spns_amount := l_n_computed_spns_amount;
598 ELSIF (l_n_eligible_spns_amount < l_n_computed_spns_amount) THEN
599 l_n_new_spns_amount := l_n_eligible_spns_amount;
600 ELSE
601 l_n_new_spns_amount := l_n_computed_spns_amount;
602 END IF;
603
604 -- Return the lesser of New Sponsor Amount and Total Sponsor Amount
605 IF (rec_stdnt_rel.tot_spnsr_amount = l_n_new_spns_amount) THEN
606 RETURN l_n_new_spns_amount;
607 ELSIF (rec_stdnt_rel.tot_spnsr_amount < l_n_new_spns_amount) THEN
608 RETURN rec_stdnt_rel.tot_spnsr_amount;
609 ELSE
610 RETURN l_n_new_spns_amount;
611 END IF;
612 END IF;
613 END IF;
614
615 -- sum up the amount at the fee class
616 FOR rec_std_fc IN c_std_fc (p_spnsr_stdnt_id, p_fee_cls_id)
617 LOOP
618 -- check for the presence of fee percent and max amount else look at the program level
619 IF rec_std_fc.fee_percent IS NULL AND rec_std_fc.max_amount IS NULL THEN
620 -- look at prg level
621 FOR rec_std_prg IN c_std_prg (rec_std_fc.fee_cls_id)
622 LOOP
623 -- check for program status
624 IF p_chk_elig = 'Y' THEN
625 OPEN c_prg_attempt (rec_stdnt_rel.person_id, rec_std_prg.course_cd,rec_std_prg.version_number,
626 rec_stdnt_rel.ld_cal_type,rec_stdnt_rel.ld_sequence_number);
627 FETCH c_prg_attempt INTO l_prg_attempt;
628 IF c_prg_attempt%NOTFOUND THEN
629 EXIT;
630 END IF;
631 CLOSE c_prg_attempt;
632 END IF;
633
634 IF rec_std_prg.fee_percent IS NULL AND rec_std_prg.max_amount IS NULL THEN
635 -- get it from unit level
636 OPEN c_std_unit (rec_std_prg.fee_cls_prg_id);
637 LOOP
638 FETCH c_std_unit INTO rec_std_unit;
639 EXIT WHEN c_std_unit%NOTFOUND ;
640 IF p_chk_elig = 'Y' THEN
641 IF igf_sp_gen_001.check_unit_attempt(p_person_id => rec_stdnt_rel.person_id,
642 p_ld_cal_type => rec_stdnt_rel.ld_cal_type,
643 p_ld_ci_sequence_number => rec_stdnt_rel.ld_sequence_number,
644 p_course_cd => rec_std_prg.course_cd,
645 p_course_version_number => rec_std_prg.version_number,
646 p_unit_cd => rec_std_unit.unit_cd,
647 p_unit_version_number => rec_std_unit.version_number,
648 p_msg_count => l_msg_count,
649 p_msg_data => l_msg_data) THEN
650 -- If p_chk_elig is Y (Actual), then the Eligible Amount needs to be taken into account
651 -- before awarding.
652 -- Fetch the sponsor amounts
653 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
654 p_v_fee_cal_type => l_v_fee_cal_type,
655 p_n_fee_seq_number => l_n_fee_ci_seq_num,
656 p_v_fund_code => rec_stdnt_rel.fund_code,
657 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
658 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
659 p_v_fee_class => rec_std_fc.fee_class,
660 p_v_course_cd => NULL,
661 p_v_unit_cd => rec_std_unit.unit_cd,
662 p_n_unit_ver_num => rec_std_unit.version_number,
663 x_eligible_amount => l_n_eligible_spns_amount,
664 x_new_spnsp_amount => l_n_computed_spns_amount);
665 -- Determine New Sponsor amount as lesser of Eligible and Computed sponsor amount
666 IF (l_n_eligible_spns_amount = l_n_computed_spns_amount) THEN
667 l_n_new_spns_amount := l_n_computed_spns_amount;
668 ELSIF (l_n_eligible_spns_amount < l_n_computed_spns_amount) THEN
669 l_n_new_spns_amount := l_n_eligible_spns_amount;
670 ELSE
671 l_n_new_spns_amount := l_n_computed_spns_amount;
672 END IF;
673
674 -- Determine the lesser of New Sponsor Amount and Max Amount
675 -- The lesser of the two will be added to the Total Sponsor Amount
676 IF (rec_std_unit.max_amount = l_n_new_spns_amount) THEN
677 l_n_spns_amount := l_n_new_spns_amount;
678 ELSIF (rec_std_unit.max_amount < l_n_new_spns_amount) THEN
679 l_n_spns_amount := rec_std_unit.max_amount;
680 ELSE
681 l_n_spns_amount := l_n_new_spns_amount;
682 END IF;
683 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
684 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_spns_amount,0);
685 END IF;
686 ELSE
687 -- Since p_chk_elig is N (Planned) - there is no need to consider Eligible Amounts.
688 -- The Max Amount can directly be used.
689 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(rec_std_unit.max_amount,0);
690 END IF;
691 END LOOP;
692 CLOSE c_std_unit;
693
694 ELSIF rec_std_prg.fee_percent IS NULL AND rec_std_prg.max_amount IS NOT NULL THEN
695 IF (p_chk_elig = 'N') THEN
696 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + rec_std_prg.max_amount;
697 ELSE
698 -- Fetch the Sponsor Amounts
699 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
700 p_v_fee_cal_type => l_v_fee_cal_type,
701 p_n_fee_seq_number => l_n_fee_ci_seq_num,
702 p_v_fund_code => rec_stdnt_rel.fund_code,
703 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
704 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
705 p_v_fee_class => rec_std_fc.fee_class,
706 p_v_course_cd => rec_std_prg.course_cd,
707 p_v_unit_cd => NULL,
708 p_n_unit_ver_num => NULL,
709 x_eligible_amount => l_n_eligible_spns_amount,
710 x_new_spnsp_amount => l_n_computed_spns_amount);
711 -- Determine New Sponsor amount as lesser of Eligible and Computed sponsor amount
712 IF (l_n_eligible_spns_amount = l_n_computed_spns_amount) THEN
713 l_n_new_spns_amount := l_n_computed_spns_amount;
714 ELSIF (l_n_eligible_spns_amount < l_n_computed_spns_amount) THEN
715 l_n_new_spns_amount := l_n_eligible_spns_amount;
716 ELSE
717 l_n_new_spns_amount := l_n_computed_spns_amount;
718 END IF;
719
720 -- Determine the lesser of New Sponsor Amount and Max Amount
721 -- The lesser of the two will be added to the Total Sponsor Amount
722 IF (rec_std_prg.max_amount = l_n_new_spns_amount) THEN
723 l_n_spns_amount := l_n_new_spns_amount;
724 ELSIF (rec_std_prg.max_amount < l_n_new_spns_amount) THEN
725 l_n_spns_amount := rec_std_prg.max_amount;
726 ELSE
727 l_n_spns_amount := l_n_new_spns_amount;
728 END IF;
729
730 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
731 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_spns_amount,0);
732 END IF;
733 ELSIF rec_std_prg.fee_percent IS NOT NULL AND rec_std_prg.max_amount IS NULL THEN
734 -- Fetch the Sponsor Amounts
735 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
736 p_v_fee_cal_type => l_v_fee_cal_type,
737 p_n_fee_seq_number => l_n_fee_ci_seq_num,
738 p_v_fund_code => rec_stdnt_rel.fund_code,
739 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
740 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
741 p_v_fee_class => rec_std_fc.fee_class,
742 p_v_course_cd => rec_std_prg.course_cd,
743 p_v_unit_cd => NULL,
744 p_n_unit_ver_num => NULL,
745 x_eligible_amount => l_n_eligible_spns_amount,
746 x_new_spnsp_amount => l_n_computed_spns_amount);
747
748 l_n_new_spns_amount := (rec_std_prg.fee_percent/100) * l_n_computed_spns_amount;
749
750 -- Consider the lower of Eligible Amount and % of New Amount to add
751 -- to the Total Sponsor Amount.
752 IF (l_n_new_spns_amount = l_n_eligible_spns_amount) THEN
753 l_n_spns_amount := l_n_eligible_spns_amount;
754 ELSIF (l_n_new_spns_amount < l_n_eligible_spns_amount) THEN
755 l_n_spns_amount := l_n_new_spns_amount;
756 ELSE
757 l_n_spns_amount := l_n_eligible_spns_amount;
758 END IF;
759
760 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
761 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_spns_amount,0);
762
763 ELSIF rec_std_prg.fee_percent IS NOT NULL AND rec_std_prg.max_amount IS NOT NULL THEN
764 -- Fetch the Sponsor Amounts
765 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
766 p_v_fee_cal_type => l_v_fee_cal_type,
767 p_n_fee_seq_number => l_n_fee_ci_seq_num,
768 p_v_fund_code => rec_stdnt_rel.fund_code,
769 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
770 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
771 p_v_fee_class => rec_std_fc.fee_class,
772 p_v_course_cd => rec_std_prg.course_cd,
773 p_v_unit_cd => NULL,
774 p_n_unit_ver_num => NULL,
775 x_eligible_amount => l_n_eligible_spns_amount,
776 x_new_spnsp_amount => l_n_computed_spns_amount);
777
778 l_n_new_spns_amount := (rec_std_prg.fee_percent/100) * l_n_computed_spns_amount;
779
780 -- Determine the lesser of New Sponsor Amount and Max Amount
781 -- The lesser of the two will be added to the Total Sponsor Amount
782 IF (rec_std_prg.max_amount = l_n_new_spns_amount) THEN
783 l_n_spns_amount := l_n_new_spns_amount;
784 ELSIF (rec_std_prg.max_amount < l_n_new_spns_amount) THEN
785 l_n_spns_amount := rec_std_unit.max_amount;
786 ELSE
787 l_n_spns_amount := l_n_new_spns_amount;
788 END IF;
789
790 -- Consider the lower of Eligible Amount and % of Computed Amount to add
791 -- to the Total Sponsor Amount.
792 IF (l_n_spns_amount = l_n_eligible_spns_amount) THEN
793 l_n_final_spns_amount := l_n_eligible_spns_amount;
794 ELSIF (l_n_spns_amount < l_n_eligible_spns_amount) THEN
795 l_n_final_spns_amount := l_n_spns_amount;
796 ELSE
797 l_n_final_spns_amount := l_n_eligible_spns_amount;
798 END IF;
799
800 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
801 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_final_spns_amount,0);
802
803 END IF;
804
805 END LOOP; -- for the prg level
806 ELSIF rec_std_fc.fee_percent IS NULL AND rec_std_fc.max_amount IS NOT NULL THEN
807 IF (p_chk_elig = 'N') THEN
808 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + rec_std_fc.max_amount;
809 ELSE
810 -- Fetch the Sponsor Amounts
811 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
812 p_v_fee_cal_type => l_v_fee_cal_type,
813 p_n_fee_seq_number => l_n_fee_ci_seq_num,
814 p_v_fund_code => rec_stdnt_rel.fund_code,
815 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
816 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
817 p_v_fee_class => rec_std_fc.fee_class,
818 p_v_course_cd => NULL,
819 p_v_unit_cd => NULL,
820 p_n_unit_ver_num => NULL,
821 x_eligible_amount => l_n_eligible_spns_amount,
822 x_new_spnsp_amount => l_n_computed_spns_amount);
823 -- Determine New Sponsor amount as lesser of Eligible and Computed sponsor amount
824 IF (l_n_eligible_spns_amount = l_n_computed_spns_amount) THEN
825 l_n_new_spns_amount := l_n_computed_spns_amount;
826 ELSIF (l_n_eligible_spns_amount < l_n_computed_spns_amount) THEN
827 l_n_new_spns_amount := l_n_eligible_spns_amount;
828 ELSE
829 l_n_new_spns_amount := l_n_computed_spns_amount;
830 END IF;
831
832 -- Determine the lesser of New Sponsor Amount and Max Amount
833 -- The lesser of the two will be added to the Total Sponsor Amount
834 IF (rec_std_fc.max_amount = l_n_new_spns_amount) THEN
835 l_n_spns_amount := l_n_new_spns_amount;
836 ELSIF (rec_std_fc.max_amount < l_n_new_spns_amount) THEN
837 l_n_spns_amount := rec_std_fc.max_amount;
838 ELSE
839 l_n_spns_amount := l_n_new_spns_amount;
840 END IF;
841
842 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
843 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_spns_amount,0);
844
845 END IF;
846 ELSIF rec_std_fc.fee_percent IS NOT NULL AND rec_std_fc.max_amount IS NULL THEN
847 -- Fetch the Sponsor Amounts
848 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
849 p_v_fee_cal_type => l_v_fee_cal_type,
850 p_n_fee_seq_number => l_n_fee_ci_seq_num,
851 p_v_fund_code => rec_stdnt_rel.fund_code,
852 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
853 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
854 p_v_fee_class => rec_std_fc.fee_class,
855 p_v_course_cd => NULL,
856 p_v_unit_cd => NULL,
857 p_n_unit_ver_num => NULL,
858 x_eligible_amount => l_n_eligible_spns_amount,
859 x_new_spnsp_amount => l_n_computed_spns_amount);
860
861 l_n_new_spns_amount := (rec_std_fc.fee_percent/100) * l_n_computed_spns_amount;
862
863 -- Consider the lower of Eligible Amount and % of New Amount to add
864 -- to the Total Sponsor Amount.
865 IF (l_n_new_spns_amount = l_n_eligible_spns_amount) THEN
866 l_n_spns_amount := l_n_eligible_spns_amount;
867 ELSIF (l_n_new_spns_amount < l_n_eligible_spns_amount) THEN
868 l_n_spns_amount := l_n_new_spns_amount;
869 ELSE
870 l_n_spns_amount := l_n_eligible_spns_amount;
871 END IF;
872
873 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
874 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_spns_amount,0);
875
876 ELSIF rec_std_fc.fee_percent IS NOT NULL AND rec_std_fc.max_amount IS NOT NULL THEN
877 -- Fetch the Sponsor Amounts
878 igf_sp_gen_001.get_sponsor_amts(p_n_person_id => rec_stdnt_rel.person_id,
879 p_v_fee_cal_type => l_v_fee_cal_type,
880 p_n_fee_seq_number => l_n_fee_ci_seq_num,
881 p_v_fund_code => rec_stdnt_rel.fund_code,
882 p_v_ld_cal_type => rec_stdnt_rel.ld_cal_type,
883 p_n_ld_seq_number => rec_stdnt_rel.ld_sequence_number,
884 p_v_fee_class => rec_std_fc.fee_class,
885 p_v_course_cd => NULL,
886 p_v_unit_cd => NULL,
887 p_n_unit_ver_num => NULL,
888 x_eligible_amount => l_n_eligible_spns_amount,
889 x_new_spnsp_amount => l_n_computed_spns_amount);
890
891 l_n_new_spns_amount := (rec_std_fc.fee_percent/100) * l_n_computed_spns_amount;
892
893 -- Determine the lesser of New Sponsor Amount and Max Amount
894 -- The lesser of the two will be added to the Total Sponsor Amount
895 IF (rec_std_fc.max_amount = l_n_new_spns_amount) THEN
896 l_n_spns_amount := l_n_new_spns_amount;
897 ELSIF (rec_std_fc.max_amount < l_n_new_spns_amount) THEN
898 l_n_spns_amount := rec_std_fc.max_amount;
899 ELSE
900 l_n_spns_amount := l_n_new_spns_amount;
901 END IF;
902
903 -- Consider the lower of Eligible Amount and Calculated Amount to add
904 -- to the Total Sponsor Amount.
905 IF (l_n_spns_amount = l_n_eligible_spns_amount) THEN
906 l_n_final_spns_amount := l_n_eligible_spns_amount;
907 ELSIF (l_n_spns_amount < l_n_eligible_spns_amount) THEN
908 l_n_final_spns_amount := l_n_spns_amount;
909 ELSE
910 l_n_final_spns_amount := l_n_eligible_spns_amount;
911 END IF;
912
913 -- Add the calculated Sponsor Amount to the Total Sponsor Amount
914 l_tot_spnsr_amount := NVL(l_tot_spnsr_amount,0) + NVL(l_n_final_spns_amount,0);
915 END IF;
916 END LOOP;
917 RETURN NVL(l_tot_spnsr_amount,0);
918
919 EXCEPTION
920 WHEN OTHERS THEN
921 -- close all opened cursor
922 IF c_stdnt_rel%ISOPEN THEN
923 CLOSE c_stdnt_rel;
924 END IF;
925 IF c_std_unit%ISOPEN THEN
926 CLOSE c_std_unit;
927 END IF;
928 RAISE;
929 END recal_dis_gross_amt;
930
931 FUNCTION get_disb_num (p_award_id igf_aw_award.award_id%TYPE)
932 RETURN NUMBER
933 AS
934 ------------------------------------------------------------------------------------
935 --Created by : smanglm ( Oracle IDC)
936 --Date created: 2002/01/11
937 --
938 --Purpose: Created as part of the build for DLD Sponsorship
939 -- this procedure is returns the disb num
940 --
941 --Known limitations/enhancements and/or remarks:
942 --
943 --Change History:
944 --Who When What
945 -------------------------------------------------------------------------------------
946 -- cursor to get max of disb num
947 CURSOR c_disb_num (cp_award_id igf_aw_award.award_id%TYPE)
948 IS
949 SELECT NVL(max(disb_num),0) + 1
950 FROM igf_aw_awd_disb
951 WHERE award_id = cp_award_id;
952 l_disb_num igf_aw_awd_disb.disb_num%TYPE;
953 BEGIN
954 OPEN c_disb_num (p_award_id);
955 FETCH c_disb_num INTO l_disb_num;
956 CLOSE c_disb_num;
957 RETURN l_disb_num;
958 END get_disb_num;
959
960 PROCEDURE create_aw_award (p_fund_id igf_sp_stdnt_rel.fund_id%TYPE,
961 p_base_id igf_sp_stdnt_rel.base_id%TYPE,
962 p_ld_cal_type igf_sp_stdnt_rel.ld_cal_type%TYPE,
963 p_ld_sequence_number igf_sp_stdnt_rel.ld_sequence_number%TYPE,
964 p_fee_type igf_aw_fund_mast.fee_type%TYPE,
965 p_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE,
966 p_award_type igf_aw_awd_disb.trans_type%TYPE,
967 p_person_id igs_pe_person.person_id%TYPE,
968 p_chk_elig VARCHAR2)
969 AS
970 ------------------------------------------------------------------------------------
971 --Created by : smanglm ( Oracle IDC)
972 --Date created: 2002/01/11
973 --
974 --Purpose: Created as part of the build for DLD Sponsorship
975 -- this procedure is to create award and details record
976 --
977 --Known limitations/enhancements and/or remarks:
978 --
979 --Change History:
980 --Who When What
981 --sapanigr 26-Jun-2006 Bug 5083572 Awards - Assign Sponsorship Awards (Wrong Msg. Logged)
982 -- Logging the message 'New Award is created for the person with the following details.'
983 -- only once before actual creation of Disbursement in the loop of fetched FC records.
984 --pathipat 18-May-2006 Bug 5194095 - Added call to recal_dis_gross_amt when Total Sponsor Amount has been
985 -- specified at Student Relation level.
986 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igf_aw_awd_disb, igf_aw_award
987 -- are now rounded off to currency precision
988 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped
989 --smadathi 31-Jun-2002 Bug 2387604. New Message IGF_SP_NO_DISB_DTL has been registered
990 -- for logging. This message is logged for informing the user
991 -- that no adjustment detail records are created for planned awards.
992 --smadathi 11-Jun-2002 Bug 2387572. Logging of IGF_SP_NO_AWARD added. Moreover,the code
993 -- logic has been handled to log the message IGF_SP_FUND_AWARD only
994 -- once when disbursements are created successfully. The amount token
995 -- was removed off from the message IGF_SP_CREATE_AWARD.
996 --vvutukur 15-apr-2002 Added cursor cur_include_as_plncrd to pass the show_on_bill selected from igs_fi_fund_mast
997 -- of particular fund_id. Done for bug#2293676.
998 -------------------------------------------------------------------------------------
999 -- cursor to see that tot_spnsr_amount is present in the igf_sp_stdnt_rel table
1000 CURSOR c_stdnt_rel (cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE)
1001 IS
1002 SELECT *
1003 FROM igf_sp_stdnt_rel
1004 WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
1005
1006 rec_stdnt_rel c_stdnt_rel%ROWTYPE;
1007
1008 -- cursor to fetch the FC record
1009 CURSOR c_std_fc (cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE)
1010 IS
1011 SELECT *
1012 FROM igf_sp_std_fc
1013 WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
1014
1015 l_include_as_plncrd igf_aw_fund_mast.show_on_bill%TYPE;
1016
1017 l_disb_gross_amt igf_aw_awd_disb.disb_gross_amt%TYPE;
1018 l_chk_elig VARCHAR2(1);
1019 l_award_id igf_aw_award.award_id%TYPE;
1020 l_disb_num igf_aw_awd_disb.disb_num%TYPE;
1021 l_n_cnt NUMBER := 0;
1022
1023 l_n_tot_spns_amt igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
1024
1025 BEGIN
1026 SAVEPOINT sp_award;
1027 --Get the show-on-bill value from igs_fi_fund_mast for passed fund_id.
1028 l_include_as_plncrd := get_show_on_bill(p_fund_id);
1029 -- check if tot_spnsr_amount is present in the igf_sp_stdnt_rel table
1030 OPEN c_stdnt_rel(p_spnsr_stdnt_id);
1031 FETCH c_stdnt_rel INTO rec_stdnt_rel;
1032 CLOSE c_stdnt_rel;
1033
1034 IF rec_stdnt_rel.tot_spnsr_amount IS NULL THEN
1035 -- look at FC level
1036 g_rowid := NULL;
1037 l_award_id := NULL;
1038 igf_aw_award_pkg.insert_row(x_rowid => g_rowid ,
1039 x_award_id => l_award_id ,
1040 x_fund_id => rec_stdnt_rel.fund_id,
1041 x_base_id => rec_stdnt_rel.base_id,
1042 x_offered_amt => 0 ,
1043 x_accepted_amt => 0 ,
1044 x_paid_amt => NULL,
1045 x_packaging_type => NULL,
1046 x_batch_id => NULL,
1047 x_manual_update => 'N' ,
1048 x_rules_override => NULL,
1049 x_award_date => TRUNC(SYSDATE),
1050 x_award_status => 'ACCEPTED' ,
1051 x_attribute_category => NULL,
1052 x_attribute1 => NULL,
1053 x_attribute2 => NULL,
1054 x_attribute3 => NULL,
1055 x_attribute4 => NULL,
1056 x_attribute5 => NULL,
1057 x_attribute6 => NULL,
1058 x_attribute7 => NULL,
1059 x_attribute8 => NULL,
1060 x_attribute9 => NULL,
1061 x_attribute10 => NULL,
1062 x_attribute11 => NULL,
1063 x_attribute12 => NULL,
1064 x_attribute13 => NULL,
1065 x_attribute14 => NULL,
1066 x_attribute15 => NULL,
1067 x_attribute16 => NULL,
1068 x_attribute17 => NULL,
1069 x_attribute18 => NULL,
1070 x_attribute19 => NULL,
1071 x_attribute20 => NULL,
1072 x_rvsn_id => NULL,
1073 x_alt_pell_schedule =>NULL,
1074 x_mode => 'R',
1075 x_award_number_txt => NULL,
1076 x_legacy_record_flag => NULL,
1077 x_lock_award_flag => 'N',
1078 x_app_trans_num_txt => NULL,
1079 x_awd_proc_status_code => NULL,
1080 x_notification_status_code => NULL,
1081 x_notification_status_date => NULL,
1082 x_publish_in_ss_flag => 'N'
1083 );
1084
1085 -- get the disb num
1086 l_disb_num := get_disb_num(l_award_id);
1087 l_n_cnt := 0;
1088
1089 g_b_msg_logged := FALSE;
1090 FOR rec_std_fc IN c_std_fc (rec_stdnt_rel.spnsr_stdnt_id)
1091 LOOP
1092
1093 BEGIN
1094 l_disb_gross_amt := recal_dis_gross_amt (p_spnsr_stdnt_id => rec_stdnt_rel.spnsr_stdnt_id,
1095 p_fee_cls_id => rec_std_fc.fee_cls_id,
1096 p_chk_elig => p_chk_elig);
1097
1098 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1099 l_disb_gross_amt := igs_fi_gen_gl.get_formatted_amount(l_disb_gross_amt);
1100
1101 -- Log the fee class details
1102 IF NVL(l_disb_gross_amt ,0) = 0 THEN
1103 -- No disb gross amount
1104 -- log the message only once
1105 log_parameters(g_v_disb_fee_class,igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_std_fc.fee_class));
1106 fnd_message.set_name('IGF','IGF_SP_ELGB_FAIL');
1107 fnd_file.put_line(fnd_file.log,fnd_message.get);
1108 ELSE
1109 g_b_msg_logged := TRUE;
1110 l_n_cnt := NVL(l_n_cnt,0) + 1;
1111 g_rowid := NULL;
1112 igf_aw_awd_disb_pkg.insert_row(
1113 x_rowid => g_rowid ,
1114 x_award_id => l_award_id ,
1115 x_disb_num => l_disb_num ,
1116 x_tp_cal_type => NULL ,
1117 x_tp_sequence_number => NULL ,
1118 x_disb_gross_amt => l_disb_gross_amt ,
1119 x_fee_1 => NULL ,
1120 x_fee_2 => NULL ,
1121 x_disb_net_amt => l_disb_gross_amt ,
1122 x_disb_date => TRUNC(SYSDATE) ,
1123 x_trans_type => p_award_type ,
1124 x_elig_status => NULL ,
1125 x_elig_status_date => NULL ,
1126 x_affirm_flag => NULL ,
1127 x_hold_rel_ind => NULL ,
1128 x_manual_hold_ind => NULL ,
1129 x_disb_status => NULL ,
1130 x_disb_status_date => NULL ,
1131 x_late_disb_ind => NULL ,
1132 x_fund_dist_mthd => NULL ,
1133 x_prev_reported_ind => NULL ,
1134 x_fund_release_date => NULL ,
1135 x_fund_status => NULL ,
1136 x_fund_status_date => NULL ,
1137 x_fee_paid_1 => NULL ,
1138 x_fee_paid_2 => NULL ,
1139 x_cheque_number => NULL ,
1140 x_ld_cal_type => p_ld_cal_type ,
1141 x_ld_sequence_number => p_ld_sequence_number ,
1142 x_disb_accepted_amt => l_disb_gross_amt ,
1143 x_disb_paid_amt => NULL ,
1144 x_rvsn_id => NULL ,
1145 x_int_rebate_amt => NULL ,
1146 x_force_disb => NULL ,
1147 x_min_credit_pts => NULL ,
1148 x_disb_exp_dt => NULL ,
1149 x_verf_enfr_dt => NULL ,
1150 x_fee_class => rec_std_fc.fee_class ,
1151 x_show_on_bill => l_include_as_plncrd , --for bug#2293676.
1152 x_mode => 'R' ,
1153 x_attendance_type_code => NULL ,
1154 x_base_attendance_type_code => NULL,
1155 x_payment_prd_st_date => NULL,
1156 x_change_type_code => NULL,
1157 x_fund_return_mthd_code => NULL,
1158 x_direct_to_borr_flag => 'N'
1159
1160 );
1161
1162 -- Log Message only once and when the Fee Class Details are defined
1163 IF l_n_cnt = 1 THEN
1164 fnd_message.set_name('IGF','IGF_SP_CREATE_AWARD');
1165 fnd_file.put_line(fnd_file.log,fnd_message.get);
1166 log_parameters(g_v_award_id,l_award_id);
1167 END IF;
1168
1169 fnd_message.set_name('IGF','IGF_SP_CREATE_DISB');
1170 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',p_award_type));
1171 fnd_file.put_line(fnd_file.log,fnd_message.get);
1172
1173 log_parameters(g_v_disb_fee_class,igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_std_fc.fee_class));
1174 log_parameters(g_v_disb_amount,l_disb_gross_amt);
1175
1176 IF p_award_type = 'A' THEN
1177 create_disb_dtl (l_award_id, l_disb_num);
1178 END IF;
1179 l_disb_num := l_disb_num + 1;
1180 END IF;
1181 END ;
1182 END LOOP;
1183
1184 -- log the award amount only a new award id created
1185 -- creation of a new award is identified when the g_b_msg_logged is set to TRUE
1186 IF g_b_msg_logged THEN
1187 -- log award amount only when an award is created.
1188 log_parameters(g_v_award_amount,get_award_amount(l_award_id));
1189 END IF;
1190 -- re-initialize to FALSE
1191 g_b_msg_logged := FALSE;
1192
1193 IF NVL(l_n_cnt,0) = 0 THEN
1194 ROLLBACK TO sp_award;
1195 END IF;
1196 ELSIF NVL(rec_stdnt_rel.tot_spnsr_amount,0) > 0 THEN
1197
1198 l_n_tot_spns_amt := recal_dis_gross_amt(p_spnsr_stdnt_id => rec_stdnt_rel.spnsr_stdnt_id,
1199 p_fee_cls_id => NULL,
1200 p_chk_elig => p_chk_elig);
1201
1202 -- create record in igf_aw_award and igf_aw_awd_disb with the same amount
1203 g_rowid := NULL;
1204 l_award_id := NULL;
1205
1206 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1207 l_n_tot_spns_amt := igs_fi_gen_gl.get_formatted_amount(l_n_tot_spns_amt);
1208
1209 igf_aw_award_pkg.insert_row(
1210 x_rowid => g_rowid ,
1211 x_award_id => l_award_id ,
1212 x_fund_id => rec_stdnt_rel.fund_id,
1213 x_base_id => rec_stdnt_rel.base_id,
1214 x_offered_amt => l_n_tot_spns_amt ,
1215 x_accepted_amt => l_n_tot_spns_amt ,
1216 x_paid_amt => NULL,
1217 x_packaging_type => NULL,
1218 x_batch_id => NULL,
1219 x_manual_update => 'N' ,
1220 x_rules_override => NULL,
1221 x_award_date => TRUNC(SYSDATE),
1222 x_award_status => 'ACCEPTED' ,
1223 x_attribute_category => NULL,
1224 x_attribute1 => NULL,
1225 x_attribute2 => NULL,
1226 x_attribute3 => NULL,
1227 x_attribute4 => NULL,
1228 x_attribute5 => NULL,
1229 x_attribute6 => NULL,
1230 x_attribute7 => NULL,
1231 x_attribute8 => NULL,
1232 x_attribute9 => NULL,
1233 x_attribute10 => NULL,
1234 x_attribute11 => NULL,
1235 x_attribute12 => NULL,
1236 x_attribute13 => NULL,
1237 x_attribute14 => NULL,
1238 x_attribute15 => NULL,
1239 x_attribute16 => NULL,
1240 x_attribute17 => NULL,
1241 x_attribute18 => NULL,
1242 x_attribute19 => NULL,
1243 x_attribute20 => NULL,
1244 x_rvsn_id => NULL,
1245 x_alt_pell_schedule =>NULL,
1246 x_mode => 'R',
1247 x_award_number_txt => NULL,
1248 x_legacy_record_flag => NULL,
1249 x_lock_award_flag => 'N',
1250 x_app_trans_num_txt => NULL,
1251 x_awd_proc_status_code => NULL,
1252 x_notification_status_code => NULL,
1253 x_notification_status_date => NULL,
1254 x_publish_in_ss_flag => 'N'
1255 );
1256
1257 fnd_message.set_name('IGF','IGF_SP_CREATE_AWARD');
1258 fnd_file.put_line(fnd_file.log,fnd_message.get);
1259 log_parameters(g_v_award_id,l_award_id);
1260
1261 l_disb_num := get_disb_num (l_award_id);
1262 g_rowid := NULL;
1263 igf_aw_awd_disb_pkg.insert_row(
1264 x_rowid => g_rowid ,
1265 x_award_id => l_award_id ,
1266 x_disb_num => l_disb_num ,
1267 x_tp_cal_type => NULL ,
1268 x_tp_sequence_number => NULL ,
1269 x_disb_gross_amt => l_n_tot_spns_amt ,
1270 x_fee_1 => NULL ,
1271 x_fee_2 => NULL ,
1272 x_disb_net_amt => l_n_tot_spns_amt ,
1273 x_disb_date => TRUNC(SYSDATE) ,
1274 x_trans_type => p_award_type ,
1275 x_elig_status => NULL ,
1276 x_elig_status_date => NULL ,
1277 x_affirm_flag => NULL ,
1278 x_hold_rel_ind => NULL ,
1279 x_manual_hold_ind => NULL ,
1280 x_disb_status => NULL ,
1281 x_disb_status_date => NULL ,
1282 x_late_disb_ind => NULL ,
1283 x_fund_dist_mthd => NULL ,
1284 x_prev_reported_ind => NULL ,
1285 x_fund_release_date => NULL ,
1286 x_fund_status => NULL ,
1287 x_fund_status_date => NULL ,
1288 x_fee_paid_1 => NULL ,
1289 x_fee_paid_2 => NULL ,
1290 x_cheque_number => NULL ,
1291 x_ld_cal_type => p_ld_cal_type ,
1292 x_ld_sequence_number => p_ld_sequence_number ,
1293 x_disb_accepted_amt => l_n_tot_spns_amt ,
1294 x_disb_paid_amt => NULL ,
1295 x_rvsn_id => NULL ,
1296 x_int_rebate_amt => NULL ,
1297 x_force_disb => NULL ,
1298 x_min_credit_pts => NULL ,
1299 x_disb_exp_dt => NULL ,
1300 x_verf_enfr_dt => NULL ,
1301 x_fee_class => NULL ,
1302 x_show_on_bill => l_include_as_plncrd , --for bug#2293676.
1303 x_mode => 'R' ,
1304 x_attendance_type_code => NULL ,
1305 x_base_attendance_type_code => NULL,
1306 x_payment_prd_st_date => NULL,
1307 x_change_type_code => NULL,
1308 x_fund_return_mthd_code => NULL,
1309 x_direct_to_borr_flag => 'N'
1310 );
1311
1312 fnd_message.set_name('IGF','IGF_SP_CREATE_DISB');
1313 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',p_award_type));
1314 fnd_file.put_line(fnd_file.log,fnd_message.get);
1315 log_parameters(g_v_disb_fee_class,NULL);
1316 log_parameters(g_v_disb_amount,l_n_tot_spns_amt);
1317 log_parameters(g_v_award_amount,l_n_tot_spns_amt);
1318 -- create disb detail adjustment
1319 IF p_award_type = 'A' THEN
1320 create_disb_dtl (l_award_id, l_disb_num);
1321 END IF;
1322 END IF;
1323 END create_aw_award;
1324
1325 PROCEDURE loop_thru_spnsr_dtl_pvt (p_person_id igs_pe_person.person_id%TYPE,
1326 p_award_type VARCHAR2,
1327 p_base_id igf_sp_stdnt_rel.base_id%TYPE,
1328 p_fund_id igf_sp_stdnt_rel.base_id%TYPE,
1329 p_min_attendance_type igf_sp_stdnt_rel.min_attendance_type%TYPE,
1330 p_min_credit_points igf_sp_stdnt_rel.min_credit_points%TYPE,
1331 p_ld_cal_type igf_sp_stdnt_rel.ld_cal_type%TYPE,
1332 p_ld_sequence_number igf_sp_stdnt_rel.ld_sequence_number%TYPE,
1333 p_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE,
1334 p_fee_type igf_aw_fund_mast.fee_type%TYPE,
1335 p_n_total_spnsr_amt igf_sp_stdnt_rel.tot_spnsr_amount%TYPE
1336 )
1337 AS
1338 ------------------------------------------------------------------------------------
1339 --Created by : smanglm ( Oracle IDC)
1340 --Date created: 2002/01/11
1341 --
1342 --Purpose: Created as part of the build for DLD Sponsorship
1343 -- this is the local procedure to loop_thru_spnsr_dtl
1344 -- created as the similar code is to be called from if else condt
1345 --
1346 -- parameter description:
1347 -- p_person_id - Person ID
1348 --
1349 --Known limitations/enhancements and/or remarks:
1350 --
1351 --Change History:
1352 --Who When What
1353 --pathipat 18-May-2006 Bug 5194095 - Added calls to recal_dis_gross_amt to fetch the
1354 -- total sponsor amount.
1355 --sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igf_aw_awd_disb
1356 -- are now rounded off to currency precision
1357 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped
1358 --smadathi 17-Jun-2002 Bug 2387572. Logging of IGF_SP_NO_AWARD added. The token
1359 -- amount for message IGF_SP_UPDATE_AWARD modified to handle the
1360 -- case when null values are passed.
1361 --smadathi 17-May-2002 Bug 2369173. Modified currsor c_manual_update , c_aw_awd_disb
1362 -- select statement. Added load cal type and load sequence number
1363 -- parameters to cursor c_manual_update. A new cursor c_igf_sp_std_fc
1364 -- added to fetch details from sponsor student relation table.
1365 -------------------------------------------------------------------------------------
1366 l_chk_elig VARCHAR2(1);
1367
1368 -- cursor to see the value for the manual update
1369 CURSOR c_manual_update (cp_base_id igf_sp_stdnt_rel.base_id%TYPE,
1370 cp_fund_id igf_sp_stdnt_rel.fund_id%TYPE,
1371 cp_ld_cal_type igs_ca_inst.cal_type%TYPE,
1372 cp_ld_sequence_number igs_ca_inst.sequence_number%TYPE
1373 )
1374 IS
1375 SELECT NVL(manual_update,'N'), award_id
1376 FROM igf_aw_award awd
1377 WHERE base_id = cp_base_id
1378 AND fund_id = cp_fund_id
1379 AND EXISTS (SELECT '1'
1380 FROM igf_aw_awd_disb disb
1381 WHERE disb.award_id = awd.award_id
1382 AND disb.ld_cal_type = cp_ld_cal_type
1383 AND disb.ld_sequence_number = cp_ld_sequence_number);
1384
1385 l_manual_update igf_aw_award.manual_update%TYPE;
1386 l_award_id igf_aw_award.award_id%TYPE;
1387 l_rec_count NUMBER :=0;
1388 l_disb_gross_amt igf_aw_awd_disb.disb_gross_amt%TYPE;
1389
1390 -- cursor to fetch records from igf_aw_awd_disb which have trans_type as P
1391 CURSOR c_aw_awd_disb(cp_award_id igf_aw_award.award_id%TYPE,
1392 cp_v_fee_class igf_aw_awd_disb.fee_class%TYPE,
1393 cp_trans_type igf_aw_awd_disb.trans_type%TYPE
1394 )
1395 IS
1396 SELECT *
1397 FROM igf_aw_awd_disb
1398 WHERE award_id = cp_award_id
1399 AND (
1400 (fee_class = cp_v_fee_class AND cp_v_fee_class IS NOT NULL AND fee_class IS NOT NULL)
1401 OR
1402 (cp_v_fee_class IS NULL)
1403 )
1404 AND (
1405 (cp_trans_type IS NOT NULL AND trans_type = cp_trans_type)
1406 OR
1407 (cp_trans_type IS NULL AND trans_type IN ('A','P'))
1408 );
1409
1410 rec_aw_awd_disb c_aw_awd_disb%ROWTYPE;
1411
1412 CURSOR c_igf_sp_std_fc (cp_spnsr_stdnt_id igf_sp_stdnt_rel.spnsr_stdnt_id%TYPE)
1413 IS
1414 SELECT *
1415 FROM igf_sp_std_fc
1416 WHERE spnsr_stdnt_id = cp_spnsr_stdnt_id;
1417 rec_c_igf_sp_std_fc c_igf_sp_std_fc%ROWTYPE;
1418 l_n_before_awd_amt igf_aw_award.accepted_amt%TYPE :=0;
1419
1420 l_include_as_plncrd igf_aw_fund_mast.show_on_bill%TYPE;
1421 l_v_award_type igf_aw_awd_disb.trans_type%TYPE;
1422 l_n_disb_num igf_aw_awd_disb.disb_num%TYPE;
1423
1424 l_v_upd_msg_text fnd_new_messages.message_text%TYPE;
1425
1426 TYPE l_msg_tab IS TABLE OF fnd_new_messages.message_text%TYPE INDEX BY BINARY_INTEGER;
1427 l_v_msg l_msg_tab;
1428 l_v_msg_null l_msg_tab;
1429
1430 i BINARY_INTEGER;
1431
1432 l_n_tot_spns_amt igf_sp_stdnt_rel.tot_spnsr_amount%TYPE;
1433
1434 BEGIN -- begin for loop_thru_spnsr_dtl_pvt
1435
1436 l_include_as_plncrd := get_show_on_bill(p_fund_id);
1437
1438 -- decide if eligibility check has to be made or not
1439 IF p_award_type = 'A' THEN
1440 l_chk_elig := 'Y';
1441 ELSE
1442 l_chk_elig := 'N';
1443 END IF;
1444
1445 -- Loop across all Fee Class Details
1446 OPEN c_manual_update (p_base_id,
1447 p_fund_id,
1448 p_ld_cal_type,
1449 p_ld_sequence_number
1450 );
1451 LOOP
1452 FETCH c_manual_update INTO l_manual_update,l_award_id;
1453 EXIT WHEN c_manual_update%NOTFOUND;
1454 l_rec_count := c_manual_update%ROWCOUNT;
1455 IF l_manual_update = 'Y' AND p_award_type = 'A' THEN
1456 -- fetch record from igf_aw_awd_disb based on award id obtained and update only if
1457 -- the award type is A
1458 g_b_msg_logged := FALSE;
1459 FOR rec_aw_awd_disb IN c_aw_awd_disb (l_award_id,NULL,'P')
1460 LOOP
1461 IF NOT g_b_msg_logged THEN
1462 log_parameters(g_v_award_id,l_award_id);
1463 g_b_msg_logged := TRUE;
1464 END IF;
1465
1466 fnd_message.set_name('IGF','IGF_SP_PLN_AWD_CNV_ACT_AWD');
1467 fnd_file.put_line(fnd_file.log,fnd_message.get);
1468 -- make trans type P to A
1469 igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id ,
1470 x_award_id => rec_aw_awd_disb.award_id ,
1471 x_disb_num => rec_aw_awd_disb.disb_num ,
1472 x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
1473 x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
1474 x_disb_gross_amt => rec_aw_awd_disb.disb_gross_amt ,
1475 x_fee_1 => rec_aw_awd_disb.fee_1 ,
1476 x_fee_2 => rec_aw_awd_disb.fee_2 ,
1477 x_disb_net_amt => rec_aw_awd_disb.disb_net_amt ,
1478 x_disb_date => rec_aw_awd_disb.disb_date ,
1479 x_trans_type => 'A' ,
1480 x_elig_status => rec_aw_awd_disb.elig_status ,
1481 x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
1482 x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
1483 x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
1484 x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
1485 x_disb_status => rec_aw_awd_disb.disb_status ,
1486 x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
1487 x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
1488 x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
1489 x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
1490 x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
1491 x_fund_status => rec_aw_awd_disb.fund_status ,
1492 x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
1493 x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
1494 x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
1495 x_cheque_number => rec_aw_awd_disb.cheque_number ,
1496 x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
1497 x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
1498 x_disb_accepted_amt => rec_aw_awd_disb.disb_accepted_amt ,
1499 x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
1500 x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
1501 x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
1502 x_force_disb => rec_aw_awd_disb.force_disb ,
1503 x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
1504 x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
1505 x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
1506 x_fee_class => rec_aw_awd_disb.fee_class ,
1507 x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
1508 x_mode => 'R' ,
1509 x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
1510 x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
1511 x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
1512 x_change_type_code => rec_aw_awd_disb.change_type_code ,
1513 x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
1514 x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
1515
1516 );
1517
1518 log_parameters(g_v_disb_fee_class,igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class));
1519 log_parameters(g_v_disb_amount,rec_aw_awd_disb.disb_net_amt);
1520 create_disb_dtl (rec_aw_awd_disb.award_id, rec_aw_awd_disb.disb_num);
1521 END LOOP;
1522 IF g_b_msg_logged THEN
1523 log_parameters(g_v_award_amount,get_award_amount(l_award_id));
1524 ELSE
1525 fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
1526 fnd_file.put_line(fnd_file.log, fnd_message.get);
1527 log_parameters(g_v_award_id,l_award_id);
1528 END IF;
1529 ELSIF l_manual_update = 'N' AND p_n_total_spnsr_amt IS NOT NULL AND p_award_type = 'A' THEN
1530 -- When invoked in the Actual Mode then get all the Planned Disbursement Records and check if the
1531 -- eligibility satisfies. If satisfies then update the status to Actual.
1532
1533 IF NOT check_eligibility(p_person_id => p_person_id,
1534 p_min_att_type => p_min_attendance_type,
1535 p_min_credit_points => p_min_credit_points ,
1536 p_ld_cal_type => p_ld_cal_type,
1537 p_ld_sequence_number => p_ld_sequence_number ) THEN
1538 log_parameters(g_v_disb_fee_class,NULL);
1539 fnd_message.set_name('IGF','IGF_SP_ELGB_FAIL');
1540 fnd_file.put_line(fnd_file.log,fnd_message.get);
1541 ELSE
1542 log_parameters(g_v_award_id,l_award_id);
1543
1544 -- Fetch the Disbursement Amount using the following function.
1545 l_n_tot_spns_amt := recal_dis_gross_amt(p_spnsr_stdnt_id => p_spnsr_stdnt_id,
1546 p_fee_cls_id => NULL,
1547 p_chk_elig => l_chk_elig);
1548
1549 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1550 l_n_tot_spns_amt := igs_fi_gen_gl.get_formatted_amount(l_n_tot_spns_amt);
1551
1552 -- fetch record from igf_aw_awd_disb based on award id obtained and update only if
1553 -- the award type is A
1554 FOR rec_aw_awd_disb IN c_aw_awd_disb (l_award_id,NULL,'P')
1555 LOOP
1556 fnd_message.set_name('IGF','IGF_SP_PLN_AWD_CNV_ACT_AWD');
1557 fnd_file.put_line(fnd_file.log,fnd_message.get);
1558 -- make trans type P to A
1559 igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id ,
1560 x_award_id => rec_aw_awd_disb.award_id ,
1561 x_disb_num => rec_aw_awd_disb.disb_num ,
1562 x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
1563 x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
1564 x_disb_gross_amt => l_n_tot_spns_amt ,
1565 x_fee_1 => rec_aw_awd_disb.fee_1 ,
1566 x_fee_2 => rec_aw_awd_disb.fee_2 ,
1567 x_disb_net_amt => l_n_tot_spns_amt ,
1568 x_disb_date => rec_aw_awd_disb.disb_date ,
1569 x_trans_type => 'A' ,
1570 x_elig_status => rec_aw_awd_disb.elig_status ,
1571 x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
1572 x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
1573 x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
1574 x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
1575 x_disb_status => rec_aw_awd_disb.disb_status ,
1576 x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
1577 x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
1578 x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
1579 x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
1580 x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
1581 x_fund_status => rec_aw_awd_disb.fund_status ,
1582 x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
1583 x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
1584 x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
1585 x_cheque_number => rec_aw_awd_disb.cheque_number ,
1586 x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
1587 x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
1588 x_disb_accepted_amt => l_n_tot_spns_amt ,
1589 x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
1590 x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
1591 x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
1592 x_force_disb => rec_aw_awd_disb.force_disb ,
1593 x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
1594 x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
1595 x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
1596 x_fee_class => rec_aw_awd_disb.fee_class ,
1597 x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
1598 x_mode => 'R' ,
1599 x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
1600 x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
1601 x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
1602 x_change_type_code => rec_aw_awd_disb.change_type_code ,
1603 x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
1604 x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
1605
1606 );
1607
1608 log_parameters(g_v_disb_fee_class,igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class));
1609 log_parameters(g_v_disb_amount,l_n_tot_spns_amt);
1610 create_disb_dtl(rec_aw_awd_disb.award_id, rec_aw_awd_disb.disb_num);
1611 END LOOP;
1612 log_parameters(g_v_award_amount,get_award_amount(l_award_id));
1613 END IF;
1614 ELSIF l_manual_update = 'N' AND p_n_total_spnsr_amt IS NULL THEN
1615
1616
1617 -- When the Award Type is provided as "A" then get all the disbursement records
1618 -- that are even in the Planned State.
1619 -- Otherwise get only the Planned Records.
1620 IF p_award_type = 'A' THEN
1621 l_v_award_type := NULL;
1622 ELSE
1623 l_v_award_type := 'P';
1624 END IF;
1625
1626 -- get the Existing award amount
1627 l_n_before_awd_amt := get_award_amount(l_award_id);
1628
1629 -- initalize the Binary Integer
1630 i := 0;
1631 g_b_award_updated := FALSE;
1632 g_b_msg_logged := FALSE;
1633 OPEN c_igf_sp_std_fc(p_spnsr_stdnt_id);
1634 LOOP
1635 FETCH c_igf_sp_std_fc INTO rec_c_igf_sp_std_fc;
1636 EXIT WHEN c_igf_sp_std_fc%NOTFOUND;
1637 -- fetch record from igf_aw_awd_disb based on award id obtained
1638 OPEN c_aw_awd_disb(l_award_id,rec_c_igf_sp_std_fc.fee_class,NULL);
1639 FETCH c_aw_awd_disb INTO rec_aw_awd_disb;
1640 IF c_aw_awd_disb%FOUND THEN
1641 IF NOT (rec_aw_awd_disb.trans_type = 'A' AND p_award_type = 'P') THEN
1642 -- recalculate the disbursement amount when a disbursement record already exists
1643 l_disb_gross_amt:= recal_dis_gross_amt(p_spnsr_stdnt_id => p_spnsr_stdnt_id,
1644 p_fee_cls_id => rec_c_igf_sp_std_fc.fee_cls_id,
1645 p_chk_elig => l_chk_elig);
1646
1647 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1648 l_disb_gross_amt := igs_fi_gen_gl.get_formatted_amount(l_disb_gross_amt);
1649
1650 IF NVL(l_disb_gross_amt,0) = 0 THEN
1651 -- No disb gross amount
1652 -- log the message only once
1653 i := i+1;
1654 l_v_msg(i) := g_v_disb_fee_class ||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class);
1655
1656 i := i+1;
1657 fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
1658 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',rec_aw_awd_disb.trans_type));
1659 l_v_msg(i) := fnd_message.get;
1660 ELSE
1661 IF NVL(l_disb_gross_amt,0) <> rec_aw_awd_disb.disb_net_amt THEN
1662 fnd_message.set_name('IGF','IGF_SP_UPDATE_AWARD');
1663 l_v_upd_msg_text := fnd_message.get;
1664 IF rec_aw_awd_disb.trans_type = 'P' AND p_award_type = 'A' THEN
1665 i := i+1;
1666 fnd_message.set_name('IGF','IGF_SP_PLN_AWD_CNV_ACT_AWD');
1667 l_v_msg(i) := fnd_message.get;
1668 ELSE
1669 i := i+1;
1670 fnd_message.set_name('IGF','IGF_SP_UPDATE_DISB');
1671 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',rec_aw_awd_disb.trans_type));
1672 l_v_msg(i) := fnd_message.get;
1673 END IF;
1674
1675 i := i+1;
1676 l_v_msg(i) := g_v_disb_fee_class||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class);
1677 i := i+1;
1678 l_v_msg(i) := g_v_ext_disb_amount||' : '|| rec_aw_awd_disb.disb_net_amt;
1679 i := i+1;
1680 l_v_msg(i) := g_v_upd_disb_amount||' : '|| l_disb_gross_amt;
1681
1682 igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id,
1683 x_award_id => rec_aw_awd_disb.award_id ,
1684 x_disb_num => rec_aw_awd_disb.disb_num ,
1685 x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
1686 x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
1687 x_disb_gross_amt => l_disb_gross_amt ,
1688 x_fee_1 => rec_aw_awd_disb.fee_1 ,
1689 x_fee_2 => rec_aw_awd_disb.fee_2 ,
1690 x_disb_net_amt => l_disb_gross_amt ,
1691 x_disb_date => rec_aw_awd_disb.disb_date,
1692 x_trans_type => NVL(l_v_award_type,'A'),
1693 x_elig_status => rec_aw_awd_disb.elig_status ,
1694 x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
1695 x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
1696 x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
1697 x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
1698 x_disb_status => rec_aw_awd_disb.disb_status ,
1699 x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
1700 x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
1701 x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
1702 x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
1703 x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
1704 x_fund_status => rec_aw_awd_disb.fund_status ,
1705 x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
1706 x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
1707 x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
1708 x_cheque_number => rec_aw_awd_disb.cheque_number ,
1709 x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
1710 x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
1711 x_disb_accepted_amt => l_disb_gross_amt ,
1712 x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
1713 x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
1714 x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
1715 x_force_disb => rec_aw_awd_disb.force_disb ,
1716 x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
1717 x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
1718 x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
1719 x_fee_class => rec_aw_awd_disb.fee_class ,
1720 x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
1721 x_mode => 'R' ,
1722 x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
1723 x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
1724 x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
1725 x_change_type_code => rec_aw_awd_disb.change_type_code ,
1726 x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
1727 x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
1728 );
1729
1730 IF (p_award_type = 'A') THEN
1731 -- create disb detail adjustment
1732 create_disb_dtl (rec_aw_awd_disb.award_id, rec_aw_awd_disb.disb_num);
1733 END IF;
1734 ELSE
1735 IF p_award_type ='A' AND rec_aw_awd_disb.trans_type = 'P' THEN
1736 fnd_message.set_name('IGF','IGF_SP_PLN_AWD_CNV_ACT_AWD');
1737 i := i+1;
1738 l_v_msg(i) := fnd_message.get;
1739
1740 i := i+1;
1741 l_v_msg(i) := g_v_disb_fee_class||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class);
1742
1743 i := i+1;
1744 l_v_msg(i) := g_v_disb_amount||' : '|| rec_aw_awd_disb.disb_net_amt;
1745
1746 -- when the process is invoked in the Actual Mode then when there is no change in the disbursement amount
1747 -- then the Planned Disbursement records needs to be updated to Actual
1748 igf_aw_awd_disb_pkg.update_row (x_rowid => rec_aw_awd_disb.row_id,
1749 x_award_id => rec_aw_awd_disb.award_id ,
1750 x_disb_num => rec_aw_awd_disb.disb_num ,
1751 x_tp_cal_type => rec_aw_awd_disb.tp_cal_type ,
1752 x_tp_sequence_number => rec_aw_awd_disb.tp_sequence_number ,
1753 x_disb_gross_amt => rec_aw_awd_disb.disb_gross_amt ,
1754 x_fee_1 => rec_aw_awd_disb.fee_1 ,
1755 x_fee_2 => rec_aw_awd_disb.fee_2 ,
1756 x_disb_net_amt => rec_aw_awd_disb.disb_net_amt ,
1757 x_disb_date => rec_aw_awd_disb.disb_date,
1758 x_trans_type => 'A',
1759 x_elig_status => rec_aw_awd_disb.elig_status ,
1760 x_elig_status_date => rec_aw_awd_disb.elig_status_date ,
1761 x_affirm_flag => rec_aw_awd_disb.affirm_flag ,
1762 x_hold_rel_ind => rec_aw_awd_disb.hold_rel_ind ,
1763 x_manual_hold_ind => rec_aw_awd_disb.manual_hold_ind ,
1764 x_disb_status => rec_aw_awd_disb.disb_status ,
1765 x_disb_status_date => rec_aw_awd_disb.disb_status_date ,
1766 x_late_disb_ind => rec_aw_awd_disb.late_disb_ind ,
1767 x_fund_dist_mthd => rec_aw_awd_disb.fund_dist_mthd ,
1768 x_prev_reported_ind => rec_aw_awd_disb.prev_reported_ind ,
1769 x_fund_release_date => rec_aw_awd_disb.fund_release_date ,
1770 x_fund_status => rec_aw_awd_disb.fund_status ,
1771 x_fund_status_date => rec_aw_awd_disb.fund_status_date ,
1772 x_fee_paid_1 => rec_aw_awd_disb.fee_paid_1 ,
1773 x_fee_paid_2 => rec_aw_awd_disb.fee_paid_2 ,
1774 x_cheque_number => rec_aw_awd_disb.cheque_number ,
1775 x_ld_cal_type => rec_aw_awd_disb.ld_cal_type ,
1776 x_ld_sequence_number => rec_aw_awd_disb.ld_sequence_number ,
1777 x_disb_accepted_amt => rec_aw_awd_disb.disb_accepted_amt ,
1778 x_disb_paid_amt => rec_aw_awd_disb.disb_paid_amt ,
1779 x_rvsn_id => rec_aw_awd_disb.rvsn_id ,
1780 x_int_rebate_amt => rec_aw_awd_disb.int_rebate_amt ,
1781 x_force_disb => rec_aw_awd_disb.force_disb ,
1782 x_min_credit_pts => rec_aw_awd_disb.min_credit_pts ,
1783 x_disb_exp_dt => rec_aw_awd_disb.disb_exp_dt ,
1784 x_verf_enfr_dt => rec_aw_awd_disb.verf_enfr_dt ,
1785 x_fee_class => rec_aw_awd_disb.fee_class ,
1786 x_show_on_bill => rec_aw_awd_disb.show_on_bill ,
1787 x_mode => 'R' ,
1788 x_attendance_type_code => rec_aw_awd_disb.attendance_type_code ,
1789 x_base_attendance_type_code => rec_aw_awd_disb.base_attendance_type_code ,
1790 x_payment_prd_st_date => rec_aw_awd_disb.payment_prd_st_date ,
1791 x_change_type_code => rec_aw_awd_disb.change_type_code ,
1792 x_fund_return_mthd_code => rec_aw_awd_disb.fund_return_mthd_code ,
1793 x_direct_to_borr_flag => rec_aw_awd_disb.direct_to_borr_flag
1794 );
1795
1796 IF (p_award_type = 'A') THEN
1797 -- create disb detail adjustment
1798 create_disb_dtl (rec_aw_awd_disb.award_id, rec_aw_awd_disb.disb_num);
1799 END IF;
1800 ELSE
1801 i := i+1;
1802 l_v_msg(i) := g_v_disb_fee_class||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class);
1803
1804 fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
1805 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',rec_aw_awd_disb.trans_type));
1806 i := i+1;
1807 l_v_msg(i) := fnd_message.get;
1808 END IF;
1809 END IF;
1810 END IF;
1811 ELSE
1812 i := i+1;
1813 l_v_msg(i) := g_v_disb_fee_class||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_aw_awd_disb.fee_class);
1814 fnd_message.set_name('IGF','IGF_SP_NO_UPDATE_DISB');
1815 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',rec_aw_awd_disb.trans_type));
1816 i := i+1;
1817 l_v_msg(i) := fnd_message.get;
1818 END IF;
1819 ELSE
1820 -- a new record needs to be created.
1821 l_disb_gross_amt := recal_dis_gross_amt (p_spnsr_stdnt_id => p_spnsr_stdnt_id,
1822 p_fee_cls_id => rec_c_igf_sp_std_fc.fee_cls_id,
1823 p_chk_elig => l_chk_elig);
1824
1825 -- Call to igs_fi_gen_gl.get_formatted_amount formats amount by rounding off to currency precision
1826 l_disb_gross_amt := igs_fi_gen_gl.get_formatted_amount(l_disb_gross_amt);
1827
1828 -- Log the fee class details
1829 IF NVL(l_disb_gross_amt ,0) = 0 THEN
1830 -- No disb gross amount
1831 i := i+1;
1832 l_v_msg(i) := g_v_disb_fee_class||' : '||igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_c_igf_sp_std_fc.fee_class);
1833
1834 i := i+1;
1835 fnd_message.set_name('IGF','IGF_SP_ELGB_FAIL');
1836 l_v_msg(i) := fnd_message.get;
1837 ELSE
1838 g_rowid := NULL;
1839 l_n_disb_num := get_disb_num(l_award_id);
1840 g_b_award_updated := TRUE;
1841
1842 fnd_message.set_name('IGF','IGF_SP_UPDATE_AWARD');
1843 l_v_upd_msg_text := fnd_message.get;
1844
1845
1846 igf_aw_awd_disb_pkg.insert_row(x_rowid => g_rowid,
1847 x_award_id => l_award_id,
1848 x_disb_num => l_n_disb_num,
1849 x_tp_cal_type => NULL,
1850 x_tp_sequence_number => NULL,
1851 x_disb_gross_amt => l_disb_gross_amt,
1852 x_fee_1 => NULL,
1853 x_fee_2 => NULL,
1854 x_disb_net_amt => l_disb_gross_amt,
1855 x_disb_date => TRUNC(SYSDATE),
1856 x_trans_type => p_award_type,
1857 x_elig_status => NULL,
1858 x_elig_status_date => NULL,
1859 x_affirm_flag => NULL,
1860 x_hold_rel_ind => NULL,
1861 x_manual_hold_ind => NULL,
1862 x_disb_status => NULL,
1863 x_disb_status_date => NULL,
1864 x_late_disb_ind => NULL,
1865 x_fund_dist_mthd => NULL,
1866 x_prev_reported_ind => NULL,
1867 x_fund_release_date => NULL,
1868 x_fund_status => NULL,
1869 x_fund_status_date => NULL,
1870 x_fee_paid_1 => NULL,
1871 x_fee_paid_2 => NULL,
1872 x_cheque_number => NULL,
1873 x_ld_cal_type => p_ld_cal_type,
1874 x_ld_sequence_number => p_ld_sequence_number,
1875 x_disb_accepted_amt => l_disb_gross_amt,
1876 x_disb_paid_amt => NULL,
1877 x_rvsn_id => NULL,
1878 x_int_rebate_amt => NULL,
1879 x_force_disb => NULL,
1880 x_min_credit_pts => NULL,
1881 x_disb_exp_dt => NULL,
1882 x_verf_enfr_dt => NULL,
1883 x_fee_class => rec_c_igf_sp_std_fc.fee_class,
1884 x_show_on_bill => l_include_as_plncrd,
1885 x_mode => 'R',
1886 x_attendance_type_code => NULL,
1887 x_base_attendance_type_code => NULL,
1888 x_payment_prd_st_date => NULL,
1889 x_change_type_code => NULL,
1890 x_fund_return_mthd_code => NULL,
1891 x_direct_to_borr_flag => 'N'
1892 );
1893
1894 fnd_message.set_name('IGF','IGF_SP_CREATE_DISB');
1895 fnd_message.set_token('DISB_TYPE',lookup_desc('IGF_DB_TRANS_TYPE',p_award_type));
1896 i := i+1;
1897 l_v_msg(i) := fnd_message.get;
1898 i := i+1;
1899 l_v_msg(i) := g_v_disb_fee_class||' : '|| igs_fi_gen_gl.get_lkp_meaning('FEE_CLASS',rec_c_igf_sp_std_fc.fee_class);
1900 i := i+1;
1901 l_v_msg(i) := g_v_disb_amount||' : '|| l_disb_gross_amt;
1902 IF p_award_type = 'A' THEN
1903 create_disb_dtl (l_award_id, l_n_disb_num);
1904 END IF;
1905 END IF;
1906 END IF;
1907 CLOSE c_aw_awd_disb;
1908 END LOOP;
1909 CLOSE c_igf_sp_std_fc;
1910
1911
1912 -- if the existing awards are updated then the boolean variable is set to TRUE
1913 -- log the existing award amount and the newly created award amount
1914 -- If the award is updated then the message needs to be logged in the log file first
1915 -- then the Award ID needs to be logged
1916 -- Once the Award ID is logged, then the messages from the table needs to be logged in the log file
1917 IF l_v_upd_msg_text IS NOT NULL THEN
1918 fnd_file.put_line(fnd_file.log,l_v_upd_msg_text);
1919 i := i+1;
1920 l_v_msg(i) := g_v_ext_award_amount||' : '|| l_n_before_awd_amt;
1921 i := i+1;
1922 l_v_msg(i) := g_v_upd_award_amount||' : '|| get_award_amount(l_award_id);
1923 ELSE
1924 -- When a
1925 IF (g_b_award_updated) THEN
1926 i := i+1;
1927 l_v_msg(i) := g_v_ext_award_amount||' : '|| l_n_before_awd_amt;
1928 i := i+1;
1929 l_v_msg(i) := g_v_upd_award_amount||' : '|| get_award_amount(l_award_id);
1930 g_b_award_updated := FALSE;
1931 ELSE
1932 i := i+1;
1933 l_v_msg(i) := g_v_award_amount||' : '|| get_award_amount(l_award_id);
1934 END IF;
1935 END IF;
1936 log_parameters(g_v_award_id,l_award_id);
1937 l_v_upd_msg_text := NULL;
1938
1939
1940 IF l_v_msg.COUNT > 0 THEN
1941 FOR i IN l_v_msg.FIRST .. l_v_msg.LAST LOOP
1942 IF l_v_msg.EXISTS(i) THEN
1943 IF l_v_msg(i) IS NOT NULL THEN
1944 fnd_file.put_line(fnd_file.log,l_v_msg(i));
1945 END IF;
1946 END IF;
1947 END LOOP;
1948 -- Once the messages are logged clear the table contents
1949 l_v_msg := l_v_msg_null;
1950 ELSE
1951 fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
1952 fnd_file.put_line(fnd_file.log, fnd_message.get);
1953 log_parameters(g_v_award_id,l_award_id);
1954 END IF;
1955 ELSE
1956 fnd_message.set_name('IGF','IGF_SP_AWD_NOT_UPDATED');
1957 fnd_file.put_line(fnd_file.log, fnd_message.get);
1958 log_parameters(g_v_award_id,l_award_id);
1959 END IF;-- check for manual update
1960 END LOOP;
1961 CLOSE c_manual_update;
1962
1963 -- if no record exist in igf_aw_award for rec_sp_std_dtls.base_id, rec_sp_std_dtls.fund_id
1964 -- create the record in igf_aw_award
1965 IF l_rec_count = 0 and p_award_type = 'A' THEN
1966 IF NOT check_eligibility (p_person_id,
1967 p_min_attendance_type,
1968 p_min_credit_points,
1969 p_ld_cal_type,
1970 p_ld_sequence_number) THEN
1971 log_parameters(g_v_disb_fee_class,NULL);
1972 fnd_message.set_name('IGF','IGF_SP_ELGB_FAIL');
1973 fnd_file.put_line(fnd_file.log,fnd_message.get);
1974 ELSE
1975 create_aw_award (p_fund_id => p_fund_id,
1976 p_base_id => p_base_id,
1977 p_ld_cal_type => p_ld_cal_type,
1978 p_ld_sequence_number => p_ld_sequence_number,
1979 p_fee_type => p_fee_type,
1980 p_spnsr_stdnt_id => p_spnsr_stdnt_id,
1981 p_award_type => p_award_type,
1982 p_person_id => p_person_id,
1983 p_chk_elig => 'Y');
1984 END IF;
1985 ELSIF l_rec_count = 0 and p_award_type = 'P' THEN
1986 create_aw_award (p_fund_id => p_fund_id,
1987 p_base_id => p_base_id,
1988 p_ld_cal_type => p_ld_cal_type,
1989 p_ld_sequence_number => p_ld_sequence_number,
1990 p_fee_type => p_fee_type,
1991 p_spnsr_stdnt_id => p_spnsr_stdnt_id,
1992 p_award_type => p_award_type,
1993 p_person_id => p_person_id,
1994 p_chk_elig => 'N');
1995 END IF;
1996
1997 EXCEPTION
1998 -- close open cursor
1999 WHEN OTHERS THEN
2000 IF c_manual_update%ISOPEN THEN
2001 CLOSE c_manual_update;
2002 END IF;
2003 RAISE;
2004 END loop_thru_spnsr_dtl_pvt;
2005
2006 PROCEDURE loop_thru_spnsr_dtl (p_person_id IN igs_pe_person.person_id%TYPE,
2007 p_cal_type IN igs_ca_inst.cal_type%TYPE,
2008 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
2009 p_award_type IN VARCHAR2,
2010 p_fund_id IN igf_aw_fund_mast.fund_id%TYPE,
2011 p_ld_cal_type IN igs_ca_inst.cal_type%TYPE,
2012 p_ld_sequence_number IN igs_ca_inst.sequence_number%TYPE)
2013 AS
2014 ------------------------------------------------------------------------------------
2015 --Created by : smanglm ( Oracle IDC)
2016 --Date created: 2002/01/11
2017 --
2018 --Purpose: Created as part of the build for DLD Sponsorship
2019 -- this is the local procedure to create_award_disb to loop thru the sponsor
2020 -- detail for the passed person id
2021 --
2022 -- parameter description:
2023 -- p_person_id - Person ID
2024 --
2025 --Known limitations/enhancements and/or remarks:
2026 --
2027 --Change History:
2028 --Who When What
2029 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped
2030 --smadathi 31-Jun-2002 Bug 2387604. Modified the logging of messages. Logging
2031 -- associated with a person , a term grouped together.
2032 --smadathi 31-May-2002 Bug 2387344. Cursor c_sp_std_dtls modified to consider all
2033 -- term calendars and person id's if both are not provided.
2034 --smadathi 17-May-2002 Bug 2369173. Added ld_cal_type and ld_sequence_number as
2035 -- parameters to the procedure.
2036 -------------------------------------------------------------------------------------
2037 -- cursor to get all the sponsor detail for the person id
2038 CURSOR c_sp_std_dtls (cp_person_id igs_pe_person.person_id%TYPE,
2039 cp_awd_cal_type igs_ca_inst.cal_type%TYPE,
2040 cp_awd_ci_sequence_number igs_ca_inst.sequence_number%TYPE,
2041 cp_fund_id igf_aw_fund_mast.fund_id%TYPE,
2042 cp_v_disc_fund igf_aw_fund_mast.discontinue_fund%TYPE) IS
2043 SELECT rel.*,
2044 fund.fund_code,
2045 fund.description fund_desc,
2046 fund.fee_type
2047 FROM igf_sp_stdnt_rel rel,
2048 igf_aw_fund_mast fund
2049 WHERE rel.fund_id = fund.fund_id
2050 AND rel.fund_id = cp_fund_id
2051 AND fund.discontinue_fund <> cp_v_disc_fund
2052 AND (
2053 (p_ld_cal_type IS NOT NULL AND rel.ld_cal_type = p_ld_cal_type)
2054 OR
2055 (p_ld_cal_type IS NULL)
2056 )
2057 AND (
2058 (p_ld_sequence_number IS NOT NULL AND rel.ld_sequence_number = p_ld_sequence_number)
2059 OR
2060 (p_ld_sequence_number IS NULL)
2061 )
2062 AND (
2063 (cp_person_id IS NOT NULL AND person_id = cp_person_id)
2064 OR
2065 (cp_person_id IS NULL)
2066 )
2067 AND EXISTS ( SELECT '1'
2068 FROM igf_ap_fa_base_rec
2069 WHERE base_id = rel.base_id
2070 AND person_id = rel.person_id
2071 AND ci_cal_type = cp_awd_cal_type
2072 AND ci_sequence_number = cp_awd_ci_sequence_number
2073 )
2074 ORDER BY fund.fund_code;
2075
2076 rec_sp_std_dtls c_sp_std_dtls%ROWTYPE;
2077 BEGIN
2078 -- get the stud sponsor detail
2079 OPEN c_sp_std_dtls (p_person_id, p_cal_type, p_ci_sequence_number, p_fund_id,'Y');
2080 LOOP
2081 FETCH c_sp_std_dtls INTO rec_sp_std_dtls;
2082 EXIT WHEN c_sp_std_dtls%NOTFOUND;
2083 -- initialize the Record found parameter when there are records matching to the input criteria
2084 g_b_records_found := TRUE;
2085
2086 -- When the user has not passed either the Person ID or Person Group then get the Person Number for the records
2087 -- found matching to the input criteria
2088 IF p_person_id IS NULL THEN
2089 g_v_person_number := get_person_number(rec_sp_std_dtls.person_id);
2090 END IF;
2091
2092 --Log sponsor details and the disbursment amount
2093 log_parameters(g_v_spnr_cd,rec_sp_std_dtls.fund_code);
2094 log_parameters(g_v_spnr_desc,rec_sp_std_dtls.fund_desc);
2095 log_parameters(g_v_person_num_pmt,g_v_person_number);
2096 log_parameters(g_v_term,get_cal_inst_dtls(rec_sp_std_dtls.ld_cal_type, rec_sp_std_dtls.ld_sequence_number));
2097
2098 -- check for the award type whether it is A or P, if A, check for eligibility
2099 IF p_award_type = 'A' THEN
2100 -- check for eligibility
2101 IF NOT check_eligibility (rec_sp_std_dtls.person_id,
2102 rec_sp_std_dtls.min_attendance_type,
2103 rec_sp_std_dtls.min_credit_points,
2104 rec_sp_std_dtls.ld_cal_type,
2105 rec_sp_std_dtls.ld_sequence_number) THEN
2106 log_parameters(g_v_disb_fee_class,NULL);
2107 fnd_message.set_name('IGF','IGF_SP_ELGB_FAIL');
2108 fnd_file.put_line(fnd_file.log,fnd_message.get);
2109 fnd_file.put_line(fnd_file.log, RPAD('-',77,'-'));
2110 EXIT;
2111 ELSE
2112 loop_thru_spnsr_dtl_pvt(p_person_id => rec_sp_std_dtls.person_id,
2113 p_award_type => p_award_type,
2114 p_base_id => rec_sp_std_dtls.base_id,
2115 p_fund_id => rec_sp_std_dtls.fund_id,
2116 p_min_attendance_type => rec_sp_std_dtls.min_attendance_type,
2117 p_min_credit_points => rec_sp_std_dtls.min_credit_points,
2118 p_ld_cal_type => rec_sp_std_dtls.ld_cal_type,
2119 p_ld_sequence_number => rec_sp_std_dtls.ld_sequence_number,
2120 p_fee_type => rec_sp_std_dtls.fee_type,
2121 p_spnsr_stdnt_id => rec_sp_std_dtls.spnsr_stdnt_id,
2122 p_n_total_spnsr_amt => rec_sp_std_dtls.tot_spnsr_amount);
2123 END IF;
2124 ELSIF p_award_type = 'P' THEN
2125 loop_thru_spnsr_dtl_pvt(p_person_id => rec_sp_std_dtls.person_id,
2126 p_award_type => p_award_type,
2127 p_base_id => rec_sp_std_dtls.base_id,
2128 p_fund_id => rec_sp_std_dtls.fund_id,
2129 p_min_attendance_type => rec_sp_std_dtls.min_attendance_type,
2130 p_min_credit_points => rec_sp_std_dtls.min_credit_points,
2131 p_ld_cal_type => rec_sp_std_dtls.ld_cal_type,
2132 p_ld_sequence_number => rec_sp_std_dtls.ld_sequence_number,
2133 p_fee_type => rec_sp_std_dtls.fee_type,
2134 p_spnsr_stdnt_id => rec_sp_std_dtls.spnsr_stdnt_id,
2135 p_n_total_spnsr_amt => rec_sp_std_dtls.tot_spnsr_amount);
2136 END IF;
2137 fnd_file.put_line(fnd_file.log, RPAD('-',77,'-'));
2138 END LOOP;
2139 CLOSE c_sp_std_dtls;
2140 EXCEPTION
2141 -- close open cursor
2142 WHEN OTHERS THEN
2143 IF c_sp_std_dtls%ISOPEN THEN
2144 CLOSE c_sp_std_dtls;
2145 END IF;
2146 RAISE;
2147 END loop_thru_spnsr_dtl;
2148
2149 -- main procedure which is called from the concurrent manager
2150 PROCEDURE create_award_disb
2151 (errbuf OUT NOCOPY VARCHAR2,
2152 retcode OUT NOCOPY NUMBER,
2153 p_award_year IN VARCHAR2,
2154 p_term_calendar IN VARCHAR2,
2155 p_person_id IN igs_pe_person.person_id%TYPE,
2156 p_person_group_id IN igs_pe_prsid_grp_mem.group_id%TYPE,
2157 p_fund_id IN igf_sp_stdnt_rel.fund_id%TYPE,
2158 p_award_type IN igf_aw_awd_disb.trans_type%TYPE,
2159 p_test_mode IN VARCHAR2,
2160 p_org_id IN NUMBER)
2161 AS
2162 ------------------------------------------------------------------------------------
2163 --Created by : smanglm ( Oracle IDC)
2164 --Date created: 2002/01/11
2165 --
2166 --Purpose: Created as part of the build for DLD Sponsorship
2167 -- This is the main procedure called from the concurrent job.
2168 -- This procedure will creat both Award and disbursement
2169 -- for a fund in FA system. Process will also check for the eligibility
2170 -- and validations before awarding the Sponsor amount to the students.
2171 -- Awarding money to the students can be done manually apart from awarding
2172 -- money through a batch process.
2173 --
2174 -- parameter description:
2175 --
2176 -- errbuf - standard conc. req. paramater
2177 -- retcode - standard conc. req. paramater
2178 -- p_award_year - mandatory paramater
2179 -- award year instnace for which sponsor and
2180 -- student relation info. should be rolled over
2181 -- p_term_calendar - indicates the time period (term calendar) in
2182 -- which all students should awarded
2183 -- p_person_id - Person ID for whom financial aid should be
2184 -- created in the FA system
2185 -- p_person_group_id - Indicates Person Group Id for which financial
2186 -- aids should be created in the FA system
2187 -- p_fund_id - Optional parameter
2188 -- indicates the sponsor id for whom all the award
2189 -- should be initiated
2190 -- p_award_type - indicates the award_type, whether the financial
2191 -- aid should be awarded to the students in Planned
2192 -- or Actual mode. P- Planned A- Actual
2193 -- p_test_mode - mandatory parameter
2194 -- - indicates whether the process is executed in
2195 -- Actual or Test mode
2196 --
2197 --Known limitations/enhancements and/or remarks:
2198 --
2199 --Change History:
2200 --Who When What
2201 --ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
2202 --vvutukur 20-Jul-2003 Enh#3038511.FICR106 Build. Added call to generic procedure
2203 -- igs_fi_crdapi_util.get_award_year_status to validate Award Year Status.
2204 --vchappid 22-Jun-2003 Bug 2881654, Log file format is revamped, Dynamic Person Group feature is introduced,
2205 -- Sponsor Code parameter is made optional
2206 --pathipat 25-Apr-2003 Enh 2831569 - Commercial Receivables build
2207 -- Added check for manage_accounts - call to chk_manage_account()
2208 --smadathi 30-Jan-2002 Bug 2620302. Cursor c_person_id_grp select modified
2209 -- to fetch the records from view igs_pe_prsid_grp_mem
2210 -- instead of igs_pe_prsid_grp_mem_v. This fix is done to remove
2211 -- Non-mergablity and to reduce shared memory. Also modified the
2212 -- cursor c_igs_lookups modified to fetch only active look up codes
2213 --smadathi 02-jul-2002 Bug 2427996. Cursor c_person_id_grp modified to select only active person id
2214 -- belonging to the group. The logic for logging the message IGF_SP_NO_PERSON
2215 -- has been removed.
2216 --smadathi 31-May-2002 Bug 2387344. Cursor c_igs_lookups , c_igs_pe_persid_group , c_igf_aw_fund_mast
2217 -- added. Also logic of logging of all parameters added.
2218 --smadathi 17-May-2002 Bug 2369173. Modified to hadnle the cases when
2219 -- term calendar was provided as parameter to the process
2220 -------------------------------------------------------------------------------------
2221
2222 -- variables to store cal type and seq num passed by award year
2223 l_cal_type igs_ca_inst.cal_type%TYPE;
2224 l_sequence_number igs_ca_inst.sequence_number%TYPE;
2225 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
2226 l_ld_sequence_number igs_ca_inst.sequence_number%TYPE;
2227
2228 l_v_awd_yr_status_cd igf_ap_batch_aw_map.award_year_status_code%TYPE;
2229
2230 -- cursor to select fund code from igf_aw_fund_mast to get fund code for fund id parameter
2231 -- This cursor definition is public to this package body;
2232 CURSOR c_igf_aw_fund_mast(cp_fund_id igf_aw_fund_mast.fund_id%TYPE,
2233 cp_cal_type igs_ca_inst.cal_type%TYPE,
2234 cp_sequence_number igs_ca_inst.sequence_number%TYPE,
2235 cp_v_sys_fund_type igf_aw_fund_cat.sys_fund_type%TYPE,
2236 cp_v_disc_fund igf_aw_fund_mast.discontinue_fund%TYPE)
2237 IS
2238 SELECT fmast.*
2239 FROM igf_aw_fund_mast fmast ,
2240 igf_aw_fund_cat fcat
2241 WHERE fmast.fund_code = fcat.fund_code
2242 AND (fmast.fund_id = cp_fund_id OR cp_fund_id IS NULL)
2243 AND fmast.ci_cal_type = cp_cal_type
2244 AND fmast.ci_sequence_number = cp_sequence_number
2245 AND fcat.sys_fund_type = cp_v_sys_fund_type
2246 AND fmast.discontinue_fund <> cp_v_disc_fund
2247 ORDER BY fund_id;
2248
2249 -- cursor variable for c_igf_aw_fund_mast
2250 l_c_igf_aw_fund_mast c_igf_aw_fund_mast%ROWTYPE;
2251
2252 -- Cursor for validating the Person Id Group
2253 CURSOR c_pers_id_grp(cp_n_pers_grp_id igs_pe_all_persid_group_v.group_id%TYPE)
2254 IS
2255 SELECT group_cd, closed_ind
2256 FROM igs_pe_all_persid_group_v
2257 WHERE group_id = cp_n_pers_grp_id;
2258 l_c_pers_id_grp c_pers_id_grp%ROWTYPE;
2259
2260 CURSOR c_validate_fund_id(cp_n_fund_id igf_aw_fund_mast.fund_id%TYPE)
2261 IS
2262 SELECT fund_code, discontinue_fund
2263 FROM igf_aw_fund_mast
2264 WHERE fund_id = cp_n_fund_id;
2265 l_rec_c_validate_fund_id c_validate_fund_id%ROWTYPE;
2266
2267 l_v_fund_code igf_aw_fund_mast.fund_code%TYPE;
2268
2269
2270 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
2271 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
2272
2273 -- REF CURSOR for dynamic person group.
2274 TYPE person_grp_ref_cur_type IS REF CURSOR;
2275 c_ref_person_grp person_grp_ref_cur_type;
2276 l_dynamic_sql VARCHAR2(2000);
2277 l_v_status VARCHAR2(10);
2278
2279 -- Record of person_id to get the values of
2280 TYPE person_grp_rec_type IS RECORD (l_n_person_id igs_pe_prsid_grp_mem.person_id%TYPE );
2281 rec_person_grp person_grp_rec_type;
2282
2283 l_b_award_year BOOLEAN;
2284 l_b_term BOOLEAN;
2285 l_b_person_number BOOLEAN;
2286 l_b_person_group BOOLEAN;
2287 l_b_sponsor_code BOOLEAN;
2288 l_b_award_type BOOLEAN;
2289 l_b_test_mode BOOLEAN;
2290 l_b_param_valid BOOLEAN := TRUE;
2291 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
2292
2293 BEGIN
2294
2295 -- set the org id
2296 igf_aw_gen.set_org_id(p_org_id);
2297 retcode := 0 ;
2298 initialize;
2299
2300 IF (p_award_year IS NOT NULL) THEN
2301 l_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
2302 l_sequence_number := TO_NUMBER(SUBSTR(p_award_year,11));
2303 g_v_log_text := get_cal_inst_dtls(l_cal_type,l_sequence_number);
2304 IF g_v_log_text IS NOT NULL THEN
2305 l_b_award_year := TRUE;
2306 ELSE
2307 l_b_award_year := FALSE;
2308 END IF;
2309 ELSE
2310 l_b_award_year := FALSE;
2311 END IF;
2312 log_parameters(g_v_award_yr,g_v_log_text);
2313
2314 IF p_term_calendar IS NOT NULL THEN
2315 l_ld_cal_type := LTRIM(RTRIM(SUBSTR(p_term_calendar,1,10))) ;
2316 l_ld_sequence_number := TO_NUMBER(SUBSTR(p_term_calendar,12)) ;
2317 -- get the alternate code , start date and end date for the term calendar type and sequence no passed to the process
2318 g_v_log_text := get_cal_inst_dtls(l_ld_cal_type,l_ld_sequence_number);
2319 IF g_v_log_text IS NOT NULL THEN
2320 l_b_term := TRUE;
2321 ELSE
2322 l_b_term := FALSE;
2323 END IF;
2324 ELSE
2325 g_v_log_text := NULL;
2326 END IF;
2327 log_parameters(g_v_term,g_v_log_text);
2328
2329 IF p_person_id IS NOT NULL THEN
2330 g_v_person_number := get_person_number(p_person_id);
2331 IF g_v_person_number IS NULL THEN
2332 l_b_person_number := FALSE;
2333 ELSE
2334 l_b_person_number := TRUE;
2335 END IF;
2336 END IF;
2337 log_parameters(g_v_person_num_pmt,g_v_person_number);
2338
2339 -- validate if the person group if passed is a valid person group
2340 IF p_person_group_id IS NOT NULL THEN
2341 OPEN c_pers_id_grp(p_person_group_id);
2342 FETCH c_pers_id_grp INTO l_c_pers_id_grp;
2343 IF c_pers_id_grp%NOTFOUND THEN
2344 l_b_person_group := FALSE;
2345 g_v_log_text := NULL;
2346 ELSE
2347 l_b_person_group := TRUE;
2348 g_v_log_text := l_c_pers_id_grp.group_cd;
2349 END IF;
2350 CLOSE c_pers_id_grp;
2351 ELSE
2352 g_v_log_text := NULL;
2353 END IF;
2354 log_parameters(g_v_person_group,g_v_log_text);
2355
2356 -- validate if the fund id if passed is existing in the Fund Master table
2357 IF p_fund_id IS NOT NULL THEN
2358 OPEN c_validate_fund_id(p_fund_id);
2359 FETCH c_validate_fund_id INTO l_rec_c_validate_fund_id;
2360 IF c_validate_fund_id%NOTFOUND THEN
2361 l_b_sponsor_code := FALSE;
2362 g_v_log_text := NULL;
2363 ELSE
2364 IF l_rec_c_validate_fund_id.discontinue_fund = 'Y' THEN
2365 l_b_sponsor_code := FALSE;
2366 g_v_log_text := NULL;
2367 ELSE
2368 l_b_sponsor_code := TRUE;
2369 g_v_log_text := l_rec_c_validate_fund_id.fund_code;
2370 END IF;
2371 END IF;
2372 ELSE
2373 g_v_log_text := NULL;
2374 END IF;
2375 log_parameters(g_v_spnr_cd,g_v_log_text);
2376
2377 IF p_award_type IS NOT NULL THEN
2378 IF (p_award_type NOT IN ('A','P')) THEN
2379 l_b_award_type := FALSE;
2380 g_v_log_text := NULL;
2381 ELSE
2382 l_b_award_type := TRUE;
2383 g_v_log_text := lookup_desc('IGF_DB_TRANS_TYPE',p_award_type);
2384 END IF;
2385 ELSE
2386 l_b_award_type := FALSE;
2387 g_v_log_text := NULL;
2388 END IF;
2389 log_parameters(g_v_award_type,g_v_log_text);
2390
2391 IF p_test_mode IS NOT NULL THEN
2392 IF (p_test_mode NOT IN ('Y','N')) THEN
2393 l_b_test_mode := FALSE;
2394 g_v_log_text := NULL;
2395 ELSE
2396 l_b_test_mode := TRUE;
2397 g_v_log_text := igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_test_mode);
2398 END IF;
2399 ELSE
2400 l_b_test_mode := FALSE;
2401 g_v_log_text := NULL;
2402 END IF;
2403 log_parameters(g_v_test_mode,g_v_log_text);
2404 fnd_file.put_line(fnd_file.log, RPAD('-',77,'-'));
2405
2406 -- check if the input parameters are not valid
2407 -- when not valid then return from the process after logging which all parameters are not valid
2408 IF NOT l_b_award_year THEN
2409 l_b_param_valid := FALSE;
2410 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2411 fnd_message.set_token('PARAMETER',g_v_award_yr);
2412 fnd_file.put_line(fnd_file.log, fnd_message.get);
2413 END IF;
2414
2415 IF NOT l_b_term THEN
2416 l_b_param_valid := FALSE;
2417 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2418 fnd_message.set_token('PARAMETER',g_v_term);
2419 fnd_file.put_line(fnd_file.log, fnd_message.get);
2420 END IF;
2421
2422 IF NOT l_b_person_number THEN
2423 l_b_param_valid := FALSE;
2424 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2425 fnd_message.set_token('PARAMETER',g_v_person_num_pmt);
2426 fnd_file.put_line(fnd_file.log, fnd_message.get);
2427 END IF;
2428
2429 -- If the person group validation has failed earlier or the person group
2430 -- is closed, then error message is logged
2431 IF NOT l_b_person_group OR NVL(l_c_pers_id_grp.closed_ind,'N') = 'Y' THEN
2432 l_b_param_valid := FALSE;
2433 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2434 fnd_message.set_token('PARAMETER',g_v_person_group);
2435 fnd_file.put_line(fnd_file.log, fnd_message.get);
2436 END IF;
2437
2438 IF NOT l_b_sponsor_code THEN
2439 l_b_param_valid := FALSE;
2440 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2441 fnd_message.set_token('PARAMETER',g_v_spnr_cd);
2442 fnd_file.put_line(fnd_file.log, fnd_message.get);
2443 END IF;
2444
2445 IF NOT l_b_award_type THEN
2446 l_b_param_valid := FALSE;
2447 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2448 fnd_message.set_token('PARAMETER',g_v_award_type);
2449 fnd_file.put_line(fnd_file.log, fnd_message.get);
2450 END IF;
2451
2452 IF NOT l_b_test_mode THEN
2453 l_b_param_valid := FALSE;
2454 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
2455 fnd_message.set_token('PARAMETER',g_v_test_mode);
2456 fnd_file.put_line(fnd_file.log, fnd_message.get);
2457 END IF;
2458
2459 IF NOT l_b_param_valid THEN
2460 RETURN;
2461 END IF;
2462
2463 -- validate the person_id and person_group_id parameters
2464 -- both p_person_id and person_group_id cannot be passed at a time
2465 IF p_person_id IS NOT NULL AND p_person_group_id IS NOT NULL THEN
2466 retcode := 2;
2467 -- Removed assignment of error message to errbuf to prevent dual messages appearing in the log
2468 fnd_message.set_name('IGS','IGS_FI_PRS_OR_PRSIDGRP');
2469 fnd_file.put_line(fnd_file.log, fnd_message.get);
2470 RETURN;
2471 END IF;
2472
2473 --Validate the Award Year Status. If the status is not open, log the message in log file and
2474 --complete the process with error.
2475 l_v_message_name := NULL;
2476 igs_fi_crdapi_util.get_award_year_status( p_v_awd_cal_type => l_cal_type,
2477 p_n_awd_seq_number => l_sequence_number,
2478 p_v_awd_yr_status => l_v_awd_yr_status_cd,
2479 p_v_message_name => l_v_message_name
2480 );
2481 IF l_v_message_name IS NOT NULL THEN
2482 IF l_v_message_name = 'IGF_SP_INVALID_AWD_YR_STATUS' THEN
2483 fnd_message.set_name('IGF',l_v_message_name);
2484 ELSE
2485 fnd_message.set_name('IGS',l_v_message_name);
2486 END IF;
2487 fnd_file.put_line(fnd_file.log,fnd_message.get);
2488 retcode := 2;
2489 RETURN;
2490 END IF;
2491
2492 -- Obtain the value of manage_accounts in the System Options form
2493 -- If it is null, then this process is not available, so error out.
2494 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
2495 p_v_message_name => l_v_message_name);
2496 IF (l_v_manage_acc IS NULL) THEN
2497 retcode := 2;
2498 fnd_message.set_name('IGS',l_v_message_name);
2499 fnd_file.put_line(fnd_file.log,fnd_message.get);
2500 RETURN;
2501 END IF;
2502
2503 -- Get the select statement when the person id Group is provided
2504 IF p_person_group_id IS NOT NULL THEN
2505 --Bug #5021084
2506 l_dynamic_sql := igf_ap_ss_pkg.get_pid(p_person_group_id, l_v_status,lv_group_type);
2507
2508 IF l_v_status <> 'S' THEN
2509 fnd_file.put_line(fnd_file.log, l_dynamic_sql);
2510 retcode := 2;
2511 RETURN;
2512 END IF;
2513 END IF;
2514
2515 FOR l_c_igf_aw_fund_mast IN c_igf_aw_fund_mast (p_fund_id,l_cal_type,l_sequence_number,'SPONSOR','Y')
2516 LOOP
2517
2518 IF p_person_group_id IS NOT NULL THEN
2519 -- Open the REF CURSOR for above derived SQL statement ( l_dynamic_sql )
2520 -- looping across all the valid person ids in the group.
2521
2522 --Bug #5021084. Passing Group ID if the group type is STATIC.
2523 IF lv_group_type = 'STATIC' THEN
2524 OPEN c_ref_person_grp FOR l_dynamic_sql USING p_person_group_id;
2525 ELSIF lv_group_type = 'DYNAMIC' THEN
2526 OPEN c_ref_person_grp FOR l_dynamic_sql;
2527 END IF;
2528
2529 LOOP
2530 FETCH c_ref_person_grp INTO rec_person_grp;
2531 EXIT WHEN c_ref_person_grp%NOTFOUND;
2532 -- While processing for a group get the person number for the current person and stroe it in the
2533 -- package variable.
2534 -- When the person id is passed as input parameter then while logging the process parameters, this global
2535 -- variable is initialized and has a value.
2536 g_v_person_number := get_person_number(rec_person_grp.l_n_person_id);
2537 loop_thru_spnsr_dtl (rec_person_grp.l_n_person_id, l_cal_type, l_sequence_number, p_award_type, l_c_igf_aw_fund_mast.fund_id,l_ld_cal_type,l_ld_sequence_number);
2538 END LOOP;
2539 CLOSE c_ref_person_grp;
2540 ELSE
2541 -- When the user has not provided either Person Id od Person Group then the process should assign awards for
2542 -- all eligible persons. This is handled in the local procedure loop_thru_spnsr_dtl main cursor.
2543 loop_thru_spnsr_dtl(p_person_id, l_cal_type, l_sequence_number, p_award_type,l_c_igf_aw_fund_mast.fund_id,l_ld_cal_type,l_ld_sequence_number);
2544 END IF;
2545
2546 -- Commit the transactions for each Sponsor and when test_run mode is 'N'
2547 -- Should Rollback the transactions to avoid redo log error
2548 IF p_test_mode = 'N' THEN
2549 COMMIT;
2550 ELSE
2551 ROLLBACK;
2552 END IF;
2553 END LOOP;
2554
2555 -- if the job is run with test_mode set to Y, rollback the transaction
2556 IF g_b_records_found THEN
2557 IF p_test_mode = 'Y' THEN
2558 fnd_file.put_line(fnd_file.log,'');
2559 fnd_message.set_name('IGF','IGF_SP_TEST_MODE');
2560 fnd_file.put_line(fnd_file.log,fnd_message.get);
2561 END IF;
2562 ELSE
2563 fnd_file.put_line(fnd_file.log,'');
2564 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
2565 fnd_file.put_line(fnd_file.log,fnd_message.get);
2566 END IF;
2567
2568 EXCEPTION
2569 WHEN OTHERS THEN
2570 ROLLBACK;
2571 fnd_file.put_line(fnd_file.log,SQLERRM);
2572 retcode := 2 ;
2573 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2574 fnd_message.set_token('NAME','CREATE_AWARD_DISB');
2575 errbuf := fnd_message.get||' - '||SQLERRM;
2576 igs_ge_msg_stack.conc_exception_hndl ;
2577 END create_award_disb;
2578 END igf_sp_award;
[Home] [Help]