[Home] [Help]
PACKAGE BODY: APPS.IGF_AW_GEN
Source
1 PACKAGE BODY igf_aw_gen AS
2 /* $Header: IGFAW02B.pls 120.14 2006/06/06 07:31:50 akomurav ship $ */
3
4 --------------------------------------------------------------------------------------
5 -- Who When What
6 --------------------------------------------------------------------------------------
7 -- museshad 06-Apr-2006 Bug 5140851. For FWS awards, award-level paid_amt
8 -- must not be reset with the sum of disbursement-level
9 -- paid_amts. Fixed this in update_award().
10 --------------------------------------------------------------------------------------
11 -- museshad 24-Oct-2005 In update_fmast(), cancelled_amt was getting updated
12 -- incorrectly. Fixed this.
13 --------------------------------------------------------------------------------------
14 -- museshad 08-Aug-2005 Bug 3954451. Cancel award, if all disbursements are
15 -- cancelled.
16 --------------------------------------------------------------------------------------
17 -- museshad 14-Jul-2005 Build FA 140.
18 -- Modified TBH call due to the addition of new
19 -- columns to igf_aw_fund_mast_all.
20 --------------------------------------------------------------------------------------
21 -- mnade 6/6/2005 FA 157 - 4382371 - Added
22 -- get_notification_status update_notification_status
23 -- update_awd_notification_status get_concurrent_prog_name
24 -- is_fund_locked_for_awd_period.
25 -- TBH impact fornotification_status_code, Disb Rounding.
26 --------------------------------------------------------------------------------------
27 -- mnade 1-Feb-2005 Bug - 4089662 - Added IGF_AW_LOAN_LMT_NOT_FND in others for avoiding
28 -- exception messages being appended
29 --------------------------------------------------------------------------------------
30 -- cdcruz 05-Dec-04 FA 152/FA 137 - Adjustment entries were not getting created in
31 -- Proc update_disb. Cursor cur_get_fed, moved to the right position
32 ---------------------------------------------------------------------------------------
33 -- veramach Oct 2004 FA 152/FA 137 - Obsoleted efc_coa,efc_resource,rem_need_fm,rem_need_im
34 --------------------------------------------------------------------------------------
35 -- sjadhav 21-Oct-2004 Bug # 3416936 FA 134, update sl loans chg status
36 --------------------------------------------------------------------------------------
37 -- brajendr 13-Oct-2004 FA152 COA and FA137 Repackaging design changes
38 -- Added the new column to the form and the TBH calls
39 --------------------------------------------------------------------------------------
40 -- svuppala 14-Oct-04 Bug # 3416936 Modified TBH call to addeded field
41 -- Eligible for Additional Unsubsidized Loans
42 --------------------------------------------------------------------------------------
43 -- ayedubat 12-OCT-2004 Changed the update_disb procedure for FA 149 build bug # 3416863
44 -- veramach July 2004 FA 151 HR Integration (bug # 3709292)
45 -- Impact of obsoleting columns from igf_aw_fund_mast_all table
46 --------------------------------------------------------------------------------------
47 -- sjadhav 10-Dec-2003 FA 131 Changes
48 -- De-link auto update of Pell Disbursement and Pell
49 -- origination amounts
50 --------------------------------------------------------------------------------------
51 -- sjadhav 3-Dec-2003 FA 131 Build changes
52 -- Modified award and disb table handlers
53 --------------------------------------------------------------------------------------
54 -- rasahoo 2-Dec-2003 FA 128 ISIR update 2004-05 New parameters added
55 --------------------------------------------------------------------------------------
56 -- veramach 11-NOV-2003 Changed the signature of check_ld_cal_tps -
57 -- adplans_id is passed instead of fund_id and out
58 -- variable is VARCHAR2 instead of BOOLEAN
59 --------------------------------------------------------------------------------------
60 -- veramach 1-NOV-2003 FA 125(#3160568) Added apdlans_id in the calls to
61 -- igf_aw_award_pkg.update_row
62 --------------------------------------------------------------------------------------
63 -- ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
64 -- added new parameter assoc_org_num to
65 -- igf_ap_fa_base_rec_pkg.update_row call.
66 --------------------------------------------------------------------------------------
67 -- bkkumar 04-jun-2003 Bug 2858504 Added legacy_record_flag
68 -- and award_number_txt in the table
69 -- handler calls for igf_aw_award_pkg.update_row
70 --------------------------------------------------------------------------------------
71 -- sjadhav 26-Mar-2003 Bug 2863960
72 -- Modified routine update_disb to populate disb gross
73 -- amount in the adjustment table with disb accepted
74 -- amount
75 -----------------------------------------------------------------------------------
76 -- sjadhav 18-Feb-2003 Bug 2758823
77 -- Modified update_disb routine to create adjustment
78 -- for disbursement date change for direct loan award
79 -- Modified cursor cur_fund_dtls,c_fm_need,c_awd_tot
80 -- c_im_need for sql tuning
81 --------------------------------------------------------------------------------------
82 -- cdcruz 18-Feb-2003 Bug 2758804
83 -- Reference To Efc Setup Tables Removed
84 --------------------------------------------------------------------------------------
85 -- sjadhav 05-Feb-2003 FA116 Build - Bug 2758812
86 -- Modified update_award to set pell origination
87 -- status to 'R' and batch id to null(i.e.rfmb_id)
88 --------------------------------------------------------------------------------------
89 -- brajendr 19-Dec-2002 Bug # 2708599
90 -- Modifed the procedure update_fmast for deletion
91 -- of Simulated awards
92 --------------------------------------------------------------------------------------
93 -- cdcruz 07-Nov-02 Modified the update_row call of
94 -- IGF_AW_FUND_MAST_PKG
95 -- Sap type is obsoleted in the SAP build fa101
96 --------------------------------------------------------------------------------------
97 -- adhawan 25-oct-2002 ALT_PELL_SCHEDULE added for FA108 Awarding
98 -- Enhancements efc_coa --Modified the coa_total_cur
99 -- to select from directly for term load calendar
100 -- efc_coa --Obsoleted the usage of p_flag ,
101 -- it is kept only for backward compatibility
102 -- efc_resource Modified the award_total_cur to select
103 -- from igf_aw_adisb_coa_match_v instead of
104 -- igf_aw_coa_citsn efc_resource Obsoleted the usage
105 -- of p_flag , it is kept only for backward
106 -- compatibility
107 --------------------------------------------------------------------------------------
108 -- masehgal 25-Sep-2002 FA 104 - To Do Enhancements
109 -- Added manual_disb_hold in FA Base update
110 --------------------------------------------------------------------------------------
111 -- brajendr 14-Jun-2002 Modified the update_row call of
112 -- igf_aw_award_pkg.update_row
113 -- to calculate the sum of the paid amount of all
114 -- the disbursements
115 --------------------------------------------------------------------------------------
116 -- brajendr 12-Jun-2002 Modified the update_row call of
117 -- IGF_AW_FUND_MAST_PKG as
118 -- Student Employment related columns
119 -- are missing in the call
120 --------------------------------------------------------------------------------------
121 -- sjadhav 24-apr-2002 Bug # 2340471.Restored changes done for Bug
122 -- 2144600,2222272
123 --------------------------------------------------------------------------------------
124 -- agairola 15-Mar-2002 Modified the call for the
125 -- IGF_SL_LOANS_PKG.UPDATE_ROW
126 -- for Borrower Determination - 2144600
127 --------------------------------------------------------------------------------------
128 -- jbegum 15-Feb-02 As part of Enh bug #2222272 modified FUNCTION
129 -- get_org_id
130 -- Explicitly the org id is being returned as null
131 -- to remove multi org functionality from OSS
132 --------------------------------------------------------------------------------------
133 -- cdcruz 06-May-2002 Modified the procedure update_fmast
134 -- All summary tab updations in Fund Manager
135 -- are now revised - Bug 2310222
136 --------------------------------------------------------------------------------------
137 -- sjadhav Feb 13, 2002 Bug 2216956
138 -- This function would return Version Number from
139 -- batch year mappings table
140 --------------------------------------------------------------------------------------
141
142
143 FUNCTION get_ver_num ( p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
144 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
145 p_process IN VARCHAR2)
146 RETURN VARCHAR2
147 IS
148
149 CURSOR cur_ver_num ( p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
150 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE)
151 IS
152 SELECT dl_code,
153 pell_code,
154 ffel_code,
155 isir_code,
156 profile_code
157 FROM
158 igf_ap_batch_aw_map
159 WHERE
160 ci_cal_type = p_cal_type AND
161 ci_sequence_number = p_seq_num;
162
163 ver_num_rec cur_ver_num%ROWTYPE;
164
165
166 BEGIN
167
168 OPEN cur_ver_num (p_cal_type,p_seq_num);
169 FETCH cur_ver_num INTO ver_num_rec;
170
171 IF cur_ver_num%NOTFOUND THEN
172 CLOSE cur_ver_num;
173 RETURN 'NULL';
174 ELSIF cur_ver_num%FOUND THEN
175 CLOSE cur_ver_num;
176
177 IF p_process ='D' THEN
178 RETURN ver_num_rec.dl_code;
179 ELSIF p_process ='F' THEN
180 RETURN ver_num_rec.ffel_code;
181 ELSIF p_process ='P' THEN
182 RETURN ver_num_rec.pell_code;
183 ELSIF p_process ='I' THEN
184 RETURN ver_num_rec.isir_code;
185 ELSIF p_process ='R' THEN
186 RETURN ver_num_rec.profile_code;
187 ELSE
188 RETURN 'NULL';
189 END IF;
190 END IF;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
195 fnd_message.set_token('NAME','IGF_AW_GEN.GET_VER_NUM' ||' ' || SQLERRM);
196 igs_ge_msg_stack.add;
197 app_exception.raise_exception;
198
199 END get_ver_num;
200
201
202
203 FUNCTION lookup_desc( l_type in VARCHAR2 , l_code in VARCHAR2 )RETURN VARCHAR2
204 IS
205
206 CURSOR c_desc( x_type igf_lookups_view.lookup_type%TYPE,
207 x_code igf_lookups_view.lookup_code%TYPE)
208 IS
209 SELECT meaning
210 FROM
211 igf_lookups_view
212 WHERE
213 lookup_code = UPPER(TRIM(x_code)) AND
214 lookup_type = UPPER(TRIM(x_type)) ;
215
216 --
217 -- For OSS Lookups
218 --
219
220 CURSOR cur_oss_desc( x_type igf_lookups_view.lookup_type%TYPE,
221 x_code igf_lookups_view.lookup_code%TYPE)
222 IS
223 SELECT meaning
224 FROM
225 igs_lookups_view
226 WHERE
227 lookup_code = UPPER(TRIM(x_code)) AND
228 lookup_type = UPPER(TRIM(x_type)) ;
229
230 l_desc VARCHAR2(80) DEFAULT NULL;
231
232 BEGIN
233
234 IF l_code IS NULL THEN
235 RETURN NULL ;
236 ELSE
237
238 OPEN c_desc(l_type,l_code);
239 FETCH c_desc INTO l_desc;
240
241 IF c_desc%NOTFOUND THEN
242 CLOSE c_desc;
243 --
244 -- If not found in IGF, then look in OSS
245 --
246 OPEN cur_oss_desc(l_type,l_code);
247 FETCH cur_oss_desc INTO l_desc;
248 CLOSE cur_oss_desc;
249 ELSE
250 CLOSE c_desc ;
251 END IF;
252
253 END IF ;
254
255 RETURN l_desc;
256
257 END lookup_desc;
258
259 FUNCTION chk_disb_status(p_award_id igf_aw_award_all.award_id%TYPE)
260 RETURN BOOLEAN
261 IS
262 ------------------------------------------------------------------
263 --Created by : museshad
264 --Date created: 22-Sep-2005
265 --
266 --Purpose: Returns TRUE if all the disbursements in the award are
267 -- cancelled, else returns FALSE
268 --
269 --Known limitations/enhancements and/or remarks:
270 --
271 --Change History:
272 --Who When What
273 -------------------------------------------------------------------
274
275 CURSOR cur_get_cancl_disb(cp_award_id igf_aw_award_all.award_id%TYPE)
276 IS
277 SELECT 'X'
278 FROM igf_aw_awd_disb_all
279 WHERE award_id = cp_award_id AND
280 trans_type <> 'C';
281
282 l_cur_get_cancl_disb_rec cur_get_cancl_disb%ROWTYPE;
283
284 BEGIN
285
286 OPEN cur_get_cancl_disb(cp_award_id => p_award_id);
287 FETCH cur_get_cancl_disb INTO l_cur_get_cancl_disb_rec;
288
289 IF (cur_get_cancl_disb%FOUND) THEN
290 CLOSE cur_get_cancl_disb;
291 RETURN FALSE;
292 ELSE
293 CLOSE cur_get_cancl_disb;
294 RETURN TRUE;
295 END IF;
296
297 END chk_disb_status;
298
299 PROCEDURE update_disb(p_disb_old_rec igf_aw_awd_disb_all%ROWTYPE,
300 p_disb_new_rec igf_aw_awd_disb_all%ROWTYPE)
301 IS
302
303 --------------------------------------------------------------------------------------
304 -- sjadhav 05-Nov-2004 FA 134 Build. Update loans table if disb data changes
305 --------------------------------------------------------------------------------------
306 -- ayedubat 11-OCT-2004 Changed the calling of procedure, igf_db_awd_disb_dtl_pkg.insert_row
307 -- to pass NULL values for the columns, DISB_STATUS, DISB_STATUS_DATE,
308 -- DISB_BATCH_ID, DISB_ACK_DATE, BOOKING_BATCH_ID and BOOKED_DATE for Bug, 3416863
309 -- sjadhav 18-Feb-2003 Bug 2758823
310 -- Modified update_disb routine to create adjustment
311 -- for disbursement date change for direct loan award
312 --------------------------------------------------------------------------------------
313 -- sjadhav, Feb 08th 2002
314 --
315 -- This procedure creates adjustments for actual disbursement
316 --
317 -- If the Direct Loan is Accepted and if any amounts change then
318 -- update Loan Change Status to 'Ready to Send'
319 --------------------------------------------------------------------------------------
320 --
321 -- Cursor to get loan details
322 --
323 CURSOR cur_loans(
324 p_award_id igf_db_awd_disb_dtl.award_id%TYPE
325 )
326 IS
327 SELECT loan.*
328 FROM igf_sl_loans loan
329 WHERE loan.award_id = p_award_id
330 FOR UPDATE OF loan_chg_status;
331
332 loans_rec cur_loans%ROWTYPE;
333
334
335 --
336 -- Cursor to get Fed Fund Code
337 --
338 CURSOR cur_get_fed(
339 p_award_id igf_db_awd_disb_dtl.award_id%TYPE
340 )
341 IS
342 SELECT cat.fed_fund_code, awd.award_status award_status
343 FROM igf_aw_award awd, igf_aw_fund_mast fmast, igf_aw_fund_cat cat
344 WHERE awd.award_id = p_award_id
345 AND awd.fund_id = fmast.fund_id
346 AND fmast.fund_code = cat.fund_code;
347
348 get_fed_rec cur_get_fed%ROWTYPE;
349
350 CURSOR c_max_seq_num(
351 p_award_id igf_db_awd_disb_dtl.award_id%TYPE,
352 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
353 )
354 IS
355 SELECT MAX(disb_seq_num) max_num
356 FROM igf_db_awd_disb_dtl
357 WHERE award_id = p_award_id AND disb_num = p_disb_num;
358
359
360 CURSOR c_get_net_total(
361 p_award_id igf_db_awd_disb_dtl.award_id%TYPE,
362 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE
363 )
364 IS
365 SELECT
366 SUM(DECODE(DISB_ACTIVITY,'D',DISB_NET_AMT,'A',DISB_ADJ_AMT,'Q',0)) net_total
367 FROM igf_db_awd_disb_dtl_all
368 WHERE award_id =p_award_id AND disb_num = p_disb_num AND sf_status in('R','P','E');
369
370 r_get_net_total c_get_net_total%ROWTYPE;
371
372
373 lv_max_seq_num c_max_seq_num%ROWTYPE;
374 lv_rowid VARCHAR2(25);
375 dbdtlrec igf_db_awd_disb_dtl%ROWTYPE;
376 BEGIN
377
378 --
379 -- check if any of the following fields are changed
380 --
381 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
382 THEN
383 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
384 'p_disb_old_rec.disb_net_amt:' || p_disb_old_rec.disb_net_amt);
385 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
386 'p_disb_new_rec.disb_net_amt:' || p_disb_new_rec.disb_net_amt);
387 END IF;
388
389 OPEN cur_get_fed(p_disb_old_rec.award_id);
390 FETCH cur_get_fed INTO get_fed_rec;
391 CLOSE cur_get_fed;
392
393 --akomurav
394 IF p_disb_old_rec.disb_net_amt <> p_disb_new_rec.disb_net_amt AND (p_disb_old_rec.ld_cal_type = p_disb_new_rec.ld_cal_type AND p_disb_old_rec.ld_sequence_number = p_disb_new_rec.ld_sequence_number)
395 THEN
396
397 --
398 -- if there is change in the above amounts, decide if we should create
399 -- an adjustment or not
400 --
401 IF p_disb_old_rec.trans_type = 'A' OR p_disb_new_rec.trans_type = 'A'
402 THEN
403 OPEN c_max_seq_num(p_disb_new_rec.award_id, p_disb_new_rec.disb_num);
404 FETCH c_max_seq_num INTO lv_max_seq_num;
405 CLOSE c_max_seq_num;
406
407 IF lv_max_seq_num.max_num > 0
408 THEN
409 dbdtlrec.award_id := p_disb_new_rec.award_id;
410 dbdtlrec.disb_num := p_disb_new_rec.disb_num;
411 dbdtlrec.disb_seq_num := TO_NUMBER(lv_max_seq_num.max_num) + 1;
412 dbdtlrec.disb_gross_amt := p_disb_new_rec.disb_accepted_amt;
413 dbdtlrec.fee_1 := p_disb_new_rec.fee_1;
414 dbdtlrec.fee_2 := p_disb_new_rec.fee_2;
415 dbdtlrec.disb_net_amt := p_disb_new_rec.disb_net_amt;
416 dbdtlrec.disb_adj_amt := p_disb_new_rec.disb_net_amt
417 - p_disb_old_rec.disb_net_amt;
418 dbdtlrec.disb_date := p_disb_new_rec.disb_date;
419 dbdtlrec.fee_paid_1 := p_disb_new_rec.fee_paid_1;
420 dbdtlrec.fee_paid_2 := p_disb_new_rec.fee_paid_2;
421 dbdtlrec.disb_activity := 'A';
422 dbdtlrec.disb_batch_id := NULL;
423 dbdtlrec.disb_ack_date := NULL;
424 dbdtlrec.booking_batch_id := NULL;
425 dbdtlrec.booked_date := NULL;
426 dbdtlrec.disb_status := NULL;
427 dbdtlrec.disb_status_date := NULL;
428 dbdtlrec.sf_status := 'R';
429 dbdtlrec.sf_status_date := TRUNC(SYSDATE);
430 dbdtlrec.sf_invoice_num := NULL;
431 dbdtlrec.spnsr_credit_id := NULL;
432 dbdtlrec.spnsr_charge_id := NULL;
433 dbdtlrec.sf_credit_id := NULL;
434 dbdtlrec.error_desc := NULL;
435 dbdtlrec.ld_cal_type := p_disb_new_rec.ld_cal_type;
436 dbdtlrec.ld_sequence_number := p_disb_new_rec.ld_sequence_number;
437
438
439 --Only if the Award Status is Accepted will the adjustment details would be created .
440 IF get_fed_rec.award_status IN ('ACCEPTED', 'CANCELLED')
441 THEN
442 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
443 THEN
444 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
445 'Inserting igf_db_awd_disb_dtl award_id '|| dbdtlrec.award_id);
446 END IF;
447
448 igf_db_awd_disb_dtl_pkg.insert_row(
449 x_rowid => lv_rowid,
450 x_award_id => dbdtlrec.award_id,
451 x_disb_num => dbdtlrec.disb_num,
452 x_disb_seq_num => dbdtlrec.disb_seq_num,
453 x_disb_gross_amt => dbdtlrec.disb_gross_amt,
454 x_fee_1 => dbdtlrec.fee_1,
455 x_fee_2 => dbdtlrec.fee_2,
456 x_disb_net_amt => dbdtlrec.disb_net_amt,
457 x_disb_adj_amt => dbdtlrec.disb_adj_amt,
458 x_disb_date => dbdtlrec.disb_date,
459 x_fee_paid_1 => dbdtlrec.fee_paid_1,
460 x_fee_paid_2 => dbdtlrec.fee_paid_2,
461 x_disb_activity => dbdtlrec.disb_activity,
462 x_disb_batch_id => NULL,
463 x_disb_ack_date => NULL,
464 x_booking_batch_id => NULL,
465 x_booked_date => NULL,
466 x_disb_status => NULL,
467 x_disb_status_date => NULL,
468 x_sf_status => dbdtlrec.sf_status,
469 x_sf_status_date => dbdtlrec.sf_status_date,
470 x_sf_invoice_num => dbdtlrec.sf_invoice_num,
471 x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
472 x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
473 x_sf_credit_id => dbdtlrec.sf_credit_id,
474 x_error_desc => dbdtlrec.error_desc,
475 x_mode => 'R',
476 x_notification_date => NULL,
477 x_interest_rebate_amt => NULL,
478 x_ld_cal_type => dbdtlrec.ld_cal_type,
479 x_ld_sequence_number => dbdtlrec.ld_sequence_number
480 );
481 END IF; -- award rec status is accepted/cancelled
482 END IF; -- max number > 0
483 END IF; -- trans type check
484 END IF; -- net amount check
485
486
487 --akomurav #5145680 if the ld cal type is changed due to repackage then a invoice rec with old amount and credit with new amt is created in the
488 --disb_dtl table in ready staus. These will be then picked by the "Transfer to student Account" process.
489 IF p_disb_old_rec.ld_cal_type <> p_disb_new_rec.ld_cal_type OR p_disb_old_rec.ld_sequence_number <> p_disb_new_rec.ld_sequence_number
490 THEN
491
492 --
493 -- if there is change in the cal_type of sequence_number, decide if we should create
494 -- an adjustment or not
495 --
496 IF p_disb_old_rec.trans_type = 'A' OR p_disb_new_rec.trans_type = 'A'
497 THEN
498 OPEN c_max_seq_num(p_disb_new_rec.award_id, p_disb_new_rec.disb_num);
499 FETCH c_max_seq_num INTO lv_max_seq_num;
500 CLOSE c_max_seq_num;
501
502 OPEN c_get_net_total(p_disb_new_rec.award_id, p_disb_new_rec.disb_num);
503 FETCH c_get_net_total INTO r_get_net_total;
504 CLOSE c_get_net_total;
505
506 IF lv_max_seq_num.max_num > 0
507 THEN
508 dbdtlrec.award_id := p_disb_new_rec.award_id;
509 dbdtlrec.disb_num := p_disb_new_rec.disb_num;
510 dbdtlrec.disb_seq_num := TO_NUMBER(lv_max_seq_num.max_num) + 1;
511 dbdtlrec.disb_gross_amt := 0;
512 dbdtlrec.fee_1 := p_disb_old_rec.fee_1;
513 dbdtlrec.fee_2 := p_disb_old_rec.fee_2;
514 dbdtlrec.disb_net_amt := 0;
515 dbdtlrec.disb_adj_amt := -r_get_net_total.net_total;
516 dbdtlrec.disb_date := p_disb_old_rec.disb_date;
517 dbdtlrec.fee_paid_1 := p_disb_old_rec.fee_paid_1;
518 dbdtlrec.fee_paid_2 := p_disb_old_rec.fee_paid_1;
519 dbdtlrec.disb_activity := 'A';
520 dbdtlrec.disb_batch_id := NULL;
521 dbdtlrec.disb_ack_date := NULL;
522 dbdtlrec.booking_batch_id := NULL;
523 dbdtlrec.booked_date := NULL;
524 dbdtlrec.disb_status := NULL;
525 dbdtlrec.disb_status_date := NULL;
526 dbdtlrec.sf_status := 'R';
527 dbdtlrec.sf_status_date := TRUNC(SYSDATE);
528 dbdtlrec.sf_invoice_num := NULL;
529 dbdtlrec.spnsr_credit_id := NULL;
530 dbdtlrec.spnsr_charge_id := NULL;
531 dbdtlrec.sf_credit_id := NULL;
532 dbdtlrec.error_desc := NULL;
533 dbdtlrec.ld_cal_type:= p_disb_old_rec.ld_cal_type;
534 dbdtlrec.ld_sequence_number := p_disb_old_rec.ld_sequence_number;
535
536
537
538 --Only if the Award Status is Accepted will the adjustment details would be created .
539 IF get_fed_rec.award_status IN ('ACCEPTED', 'CANCELLED')
540 THEN
541 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
542 THEN
543 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
544 'Inserting igf_db_awd_disb_dtl award_id '|| dbdtlrec.award_id);
545 END IF;
546 --creating an invoice record in disb_dtl table
547 igf_db_awd_disb_dtl_pkg.insert_row(
548 x_rowid => lv_rowid,
549 x_award_id => dbdtlrec.award_id,
550 x_disb_num => dbdtlrec.disb_num,
551 x_disb_seq_num => dbdtlrec.disb_seq_num,
552 x_disb_gross_amt => dbdtlrec.disb_gross_amt,
553 x_fee_1 => dbdtlrec.fee_1,
554 x_fee_2 => dbdtlrec.fee_2,
555 x_disb_net_amt => dbdtlrec.disb_net_amt,
556 x_disb_adj_amt => dbdtlrec.disb_adj_amt,
557 x_disb_date => dbdtlrec.disb_date,
558 x_fee_paid_1 => dbdtlrec.fee_paid_1,
559 x_fee_paid_2 => dbdtlrec.fee_paid_2,
560 x_disb_activity => dbdtlrec.disb_activity,
561 x_disb_batch_id => NULL,
562 x_disb_ack_date => NULL,
563 x_booking_batch_id => NULL,
564 x_booked_date => NULL,
565 x_disb_status => NULL,
566 x_disb_status_date => NULL,
567 x_sf_status => dbdtlrec.sf_status,
568 x_sf_status_date => dbdtlrec.sf_status_date,
569 x_sf_invoice_num => dbdtlrec.sf_invoice_num,
570 x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
571 x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
572 x_sf_credit_id => dbdtlrec.sf_credit_id,
573 x_error_desc => dbdtlrec.error_desc,
574 x_mode => 'R',
575 x_notification_date => NULL,
576 x_interest_rebate_amt => NULL,
577 x_ld_cal_type => dbdtlrec.ld_cal_type,
578 x_ld_sequence_number => dbdtlrec.ld_sequence_number
579 );
580
581 dbdtlrec.fee_1 := p_disb_new_rec.fee_1;
582 dbdtlrec.fee_2 := p_disb_new_rec.fee_2;
583 dbdtlrec.fee_paid_1 := p_disb_new_rec.fee_paid_1;
584 dbdtlrec.fee_paid_2 := p_disb_new_rec.fee_paid_1;
585 dbdtlrec.disb_net_amt := p_disb_new_rec.disb_net_amt;
586 dbdtlrec.disb_gross_amt := p_disb_new_rec.disb_gross_amt;
587 dbdtlrec.disb_adj_amt := p_disb_new_rec.disb_net_amt;
588 dbdtlrec.ld_cal_type := p_disb_new_rec.ld_cal_type;
589 dbdtlrec.ld_sequence_number := p_disb_new_rec.ld_sequence_number;
590 dbdtlrec.disb_date := p_disb_old_rec.disb_date;--this should be old disb date only
591
592 --creating an credit record in disb_dtl table
593
594 igf_db_awd_disb_dtl_pkg.insert_row(
595 x_rowid => lv_rowid,
596 x_award_id => dbdtlrec.award_id,
597 x_disb_num => dbdtlrec.disb_num,
598 x_disb_seq_num => dbdtlrec.disb_seq_num+1,
599 x_disb_gross_amt => dbdtlrec.disb_gross_amt,
600 x_fee_1 => dbdtlrec.fee_1,
601 x_fee_2 => dbdtlrec.fee_2,
602 x_disb_net_amt => dbdtlrec.disb_net_amt,
603 x_disb_adj_amt => dbdtlrec.disb_adj_amt,
604 x_disb_date => dbdtlrec.disb_date,
605 x_fee_paid_1 => dbdtlrec.fee_paid_1,
606 x_fee_paid_2 => dbdtlrec.fee_paid_2,
607 x_disb_activity => dbdtlrec.disb_activity,
608 x_disb_batch_id => NULL,
609 x_disb_ack_date => NULL,
610 x_booking_batch_id => NULL,
611 x_booked_date => NULL,
612 x_disb_status => NULL,
613 x_disb_status_date => NULL,
614 x_sf_status => dbdtlrec.sf_status,
615 x_sf_status_date => dbdtlrec.sf_status_date,
616 x_sf_invoice_num => dbdtlrec.sf_invoice_num,
617 x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
618 x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
619 x_sf_credit_id => dbdtlrec.sf_credit_id,
620 x_error_desc => dbdtlrec.error_desc,
621 x_mode => 'R',
622 x_notification_date => NULL,
623 x_interest_rebate_amt => NULL,
624 x_ld_cal_type => dbdtlrec.ld_cal_type,
625 x_ld_sequence_number => dbdtlrec.ld_sequence_number
626 );
627
628 END IF; -- award rec status is accepted/cancelled
629 END IF; -- max number > 0
630 END IF; -- trans type check
631 END IF; -- ld_cal_type or ld_sequence_number change
632
633
634
635 --
636 -- check if any of the disb dates are changed
637 --
638 IF TRUNC(p_disb_old_rec.disb_date) <> TRUNC(p_disb_new_rec.disb_date)
639 THEN
640 IF p_disb_old_rec.trans_type = 'A' OR p_disb_new_rec.trans_type = 'A'
641 THEN
642 OPEN c_max_seq_num(p_disb_new_rec.award_id, p_disb_new_rec.disb_num);
643 FETCH c_max_seq_num INTO lv_max_seq_num;
644 CLOSE c_max_seq_num;
645
646 IF lv_max_seq_num.max_num > 0
647 THEN
648 dbdtlrec.award_id := p_disb_new_rec.award_id;
649 dbdtlrec.disb_num := p_disb_new_rec.disb_num;
650 dbdtlrec.disb_seq_num := lv_max_seq_num.max_num + 1;
651 dbdtlrec.disb_gross_amt := p_disb_new_rec.disb_accepted_amt;
652 dbdtlrec.fee_1 := p_disb_new_rec.fee_1;
653 dbdtlrec.fee_2 := p_disb_new_rec.fee_2;
654 dbdtlrec.disb_net_amt := p_disb_new_rec.disb_net_amt;
655 dbdtlrec.disb_adj_amt := 0;
656 dbdtlrec.disb_date := p_disb_new_rec.disb_date;
657 dbdtlrec.fee_paid_1 := p_disb_new_rec.fee_paid_1;
658 dbdtlrec.fee_paid_2 := p_disb_new_rec.fee_paid_2;
659 dbdtlrec.disb_activity := 'Q';
660 dbdtlrec.disb_batch_id := NULL;
661 dbdtlrec.disb_ack_date := NULL;
662 dbdtlrec.booking_batch_id := NULL;
663 dbdtlrec.booked_date := NULL;
664 dbdtlrec.disb_status := NULL;
665 dbdtlrec.disb_status_date := NULL;
666 dbdtlrec.sf_status := 'N';
667 dbdtlrec.sf_status_date := TRUNC(SYSDATE);
668 dbdtlrec.sf_invoice_num := NULL;
669 dbdtlrec.spnsr_credit_id := NULL;
670 dbdtlrec.spnsr_charge_id := NULL;
671 dbdtlrec.sf_credit_id := NULL;
672 dbdtlrec.error_desc := NULL;
673 dbdtlrec.ld_cal_type := p_disb_new_rec.ld_cal_type;
674 dbdtlrec.ld_sequence_number := p_disb_new_rec.ld_sequence_number;
675
676
677
678 --Only if the Award Status is Accepted will the adjustment details would be created .
679 IF get_fed_rec.award_status = 'ACCEPTED'
680 THEN
681 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
682 THEN
683 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
684 'Inserting to igf_db_awd_disb_dtl award_id:'|| dbdtlrec.award_id);
685 END IF;
686
687 igf_db_awd_disb_dtl_pkg.insert_row(
688 x_rowid => lv_rowid,
689 x_award_id => dbdtlrec.award_id,
690 x_disb_num => dbdtlrec.disb_num,
691 x_disb_seq_num => dbdtlrec.disb_seq_num,
692 x_disb_gross_amt => dbdtlrec.disb_gross_amt,
693 x_fee_1 => dbdtlrec.fee_1,
694 x_fee_2 => dbdtlrec.fee_2,
695 x_disb_net_amt => dbdtlrec.disb_net_amt,
696 x_disb_adj_amt => dbdtlrec.disb_adj_amt,
697 x_disb_date => dbdtlrec.disb_date,
698 x_fee_paid_1 => dbdtlrec.fee_paid_1,
699 x_fee_paid_2 => dbdtlrec.fee_paid_2,
700 x_disb_activity => dbdtlrec.disb_activity,
701 x_disb_batch_id => NULL,
702 x_disb_ack_date => NULL,
703 x_booking_batch_id => NULL,
704 x_booked_date => NULL,
705 x_disb_status => NULL,
706 x_disb_status_date => NULL,
707 x_sf_status => dbdtlrec.sf_status,
708 x_sf_status_date => dbdtlrec.sf_status_date,
709 x_sf_invoice_num => dbdtlrec.sf_invoice_num,
710 x_spnsr_credit_id => dbdtlrec.spnsr_credit_id,
711 x_spnsr_charge_id => dbdtlrec.spnsr_charge_id,
712 x_sf_credit_id => dbdtlrec.sf_credit_id,
713 x_error_desc => dbdtlrec.error_desc,
714 x_mode => 'R',
715 x_notification_date => NULL,
716 x_interest_rebate_amt => NULL,
717 x_ld_cal_type => dbdtlrec.ld_cal_type,
718 x_ld_sequence_number => dbdtlrec.ld_sequence_number
719
720 );
721 END IF; -- award rec status is accepted
722 END IF; -- max number > 0
723 END IF; -- trans type check
724 END IF; -- date check
725
726 --
727 -- Check if the DL Loan Status is 'Accepted', If yes then change the
728 -- Loan Change Status to 'Ready to Send'
729 --
730
731 FOR loans_rec IN cur_loans(p_disb_old_rec.award_id)
732 LOOP
733 IF igf_sl_gen.chk_cl_fed_fund_code(get_fed_rec.fed_fund_code) = 'TRUE'
734 AND loans_rec.loan_status = 'A' AND NVL(loans_rec.loan_chg_status,'*') <> 'S'
735 THEN
736 IF igf_sl_award.get_loan_cl_version(loans_rec.award_id) = 'RELEASE-4'
737 THEN
738 IF TRUNC(p_disb_old_rec.disb_date) <> TRUNC(p_disb_new_rec.disb_date)
739 OR p_disb_old_rec.disb_net_amt <> p_disb_new_rec.disb_net_amt
740 OR p_disb_old_rec.hold_rel_ind <> p_disb_old_rec.hold_rel_ind
741 THEN
742 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
743 THEN
744 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
745 'Updating loan ' || loans_rec.loan_id || ' as ready to send');
746 END IF;
747 igf_sl_loans_pkg.update_row(
748 x_rowid => loans_rec.row_id,
749 x_loan_id => loans_rec.loan_id,
750 x_award_id => loans_rec.award_id,
751 x_seq_num => loans_rec.seq_num,
752 x_loan_number => loans_rec.loan_number,
753 x_loan_per_begin_date => loans_rec.loan_per_begin_date,
754 x_loan_per_end_date => loans_rec.loan_per_end_date,
755 x_loan_status => loans_rec.loan_status,
756 x_loan_status_date => loans_rec.loan_status_date,
757 x_loan_chg_status => 'G',
758 x_loan_chg_status_date => TRUNC(SYSDATE),
759 x_active => loans_rec.active,
760 x_active_date => loans_rec.active_date,
761 x_borw_detrm_code => loans_rec.borw_detrm_code,
762 x_external_loan_id_txt => loans_rec.external_loan_id_txt,
763 x_mode => 'R'
764 );
765 END IF;
766 END IF;
767 END IF;
768 END LOOP;
769
770 FOR loans_rec IN cur_loans(p_disb_old_rec.award_id)
771 LOOP
772 IF igf_sl_gen.chk_dl_fed_fund_code(get_fed_rec.fed_fund_code) = 'TRUE'
773 AND loans_rec.loan_status = 'A' AND NVL(loans_rec.loan_chg_status,'*') <> 'S'
774 THEN
775 IF TRUNC(p_disb_old_rec.disb_date) <> TRUNC(p_disb_new_rec.disb_date)
776 OR p_disb_old_rec.disb_net_amt <> p_disb_new_rec.disb_net_amt
777 THEN
778 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
779 THEN
780 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_disb.debug',
781 'Updating loan ' || loans_rec.loan_id || ' as ready to send');
782 END IF;
783
784 igf_sl_loans_pkg.update_row(
785 x_rowid => loans_rec.row_id,
786 x_loan_id => loans_rec.loan_id,
787 x_award_id => loans_rec.award_id,
788 x_seq_num => loans_rec.seq_num,
789 x_loan_number => loans_rec.loan_number,
790 x_loan_per_begin_date => loans_rec.loan_per_begin_date,
791 x_loan_per_end_date => loans_rec.loan_per_end_date,
792 x_loan_status => loans_rec.loan_status,
793 x_loan_status_date => loans_rec.loan_status_date,
794 x_loan_chg_status => 'G',
795 x_loan_chg_status_date => TRUNC(SYSDATE),
796 x_active => loans_rec.active,
797 x_active_date => loans_rec.active_date,
798 x_borw_detrm_code => loans_rec.borw_detrm_code,
799 x_external_loan_id_txt => loans_rec.external_loan_id_txt,
800 x_mode => 'R'
801 );
802
803 END IF;
804 END IF;
805 END LOOP;
806
807 EXCEPTION
808 WHEN OTHERS
809 THEN
810 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXP');
811 fnd_message.set_token('NAME', 'IGF_AW_GEN.UPDATE_DISB' || ' ' || SQLERRM);
812 igs_ge_msg_stack.ADD;
813 app_exception.raise_exception;
814 END update_disb;
815
816
817 PROCEDURE update_fabase_awds(
818 p_base_id in igf_ap_fa_base_rec_all.base_id%TYPE,
819 p_pack_status igf_ap_fa_base_rec.packaging_status%TYPE
820 ) IS
821 /*
822 || Created By : cdcruz
823 || Created On : 29-JAN-2001
824 || Purpose : Update FA Base record with the latest Packaging deails
825 || Known limitations, enhancements or remarks :
826 || Change History :
827 || Who When What
828 || (reverse chronological order - newest change first)
829 || rasahoo 27-NOV-2003 FA 128 - ISIR update 2004-05
830 || added new parameter award_fmly_contribution_type to
831 || igf_ap_fa_base_rec_pkg.update_row
832 ||
833 || ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
834 || added new parameter assoc_org_num to
835 || igf_ap_fa_base_rec_pkg.update_row call.
836 ||
837 || brajendr 18-Dec-2002 Bug # 2691832
838 || Modified the logic for updating the Packaging Status.
839 ||
840 || masehgal 11-Nov-2002 FA 101 - SAP Obsoletion
841 || removed packaging hold
842 ||
843 || masehgal 25-Sep-2002 FA 104 - To Do Enhancements
844 || Added manual_disb_hold in FA Base update
845 ||
846 || avenkatr 27-JUN-2001 1. Added p_pack_status parameter.
847 */
848
849 -- Sums up studentwise packaged totals
850 CURSOR c_pkg_tot ( x_base_id igf_aw_award_t.base_id%TYPE) IS
851 SELECT SUM(NVL(awd.offered_amt,0)) offered_amt,
852 SUM(NVL(awd.accepted_amt,0)) accepted_amt
853 FROM igf_aw_award awd
854 WHERE awd.base_id = x_base_id
855 AND award_status NOT IN( 'CANCELLED', 'DECLINED','SIMULATED' ) ;
856
857 l_pkg_tot c_pkg_tot%rowtype ;
858
859 CURSOR c_stud_det ( x_base_id igf_ap_fa_base_rec.base_id%TYPE) IS
860 SELECT fabase.*
861 FROM igf_ap_fa_base_rec fabase
862 WHERE fabase.base_id = x_base_id ;
863
864 l_stud_det c_stud_det%rowtype ;
865
866 BEGIN
867
868 --- Get packaged Totals
869 igf_aw_gen.set_org_id(NULL);
870
871 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
872 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','p_base_id:'||p_base_id);
873 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','p_pack_status:'||p_pack_status);
874 END IF;
875
876 OPEN c_pkg_tot ( p_base_id ) ;
877 FETCH c_pkg_tot INTO l_pkg_tot ;
878 CLOSE c_pkg_tot ;
879
880 -- Update the Students FA Base Record
881 OPEN c_stud_det ( p_base_id ) ;
882 FETCH c_stud_det INTO l_stud_det ;
883 CLOSE c_stud_det ;
884
885 /* Bug # 2691832
886 -- Packing Status should be modified as
887 -----------------------------------------------------------------------
888 Calling From Parameter Val Initial Status Final Status
889 -----------------------------------------------------------------------
890 Auto Pkg --> AUTO_PACKAGED --> NULL/SIMULATED --> AUTO_PACKAGED
891 Simulated Pkg --> SIMULATED --> NULL/SIMULATED --> SIMULATED
892 Single Fund --> SINGLE --> AUTO_PACKAGED --> REVISED ( Other Statuses --> No Change)
893 Cancelled --> CANCELLED --> AUTO_PACKAGED --> REVISED ( Other Statuses --> No Change)
894 Forms --> REVISED --> AUTO_PACKAGED --> REVISED ( Other Statuses --> No Change)
895 -----------------------------------------------------------------------
896 */
897
898 IF p_pack_status = 'AUTO_PACKAGED' THEN
899 l_stud_det.packaging_status := 'AUTO_PACKAGED';
900
901 ELSIF p_pack_status = 'SIMULATED' THEN
902 l_stud_det.packaging_status := 'SIMULATED';
903
904 ELSIF p_pack_status IN ('CANCELLED', 'REVISED', 'SINGLE') THEN
905
906 IF l_stud_det.packaging_status = 'AUTO_PACKAGED' THEN
907 l_stud_det.packaging_status := 'REVISED';
908 END IF;
909
910 END IF;
911
912 l_stud_det.packaging_status_date := Trunc(Sysdate) ;
913 l_stud_det.total_package_accepted := l_pkg_tot.accepted_amt ;
914 l_stud_det.total_package_offered := l_pkg_tot.offered_amt ;
915
916 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
917 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fabase_awds.debug','Updating igf_ap_fa_base_rec');
918 END IF;
919 igf_ap_fa_base_rec_pkg.update_row(
920 x_rowid => l_stud_det.row_id,
921 x_base_id => l_stud_det.base_id,
922 x_ci_cal_type => l_stud_det.ci_cal_type,
923 x_person_id => l_stud_det.person_id,
924 x_ci_sequence_number => l_stud_det.ci_sequence_number,
925 x_org_id => l_stud_det.org_id,
926 x_coa_pending => l_stud_det.coa_pending,
927 x_verification_process_run => l_stud_det.verification_process_run,
928 x_inst_verif_status_date => l_stud_det.inst_verif_status_date,
929 x_manual_verif_flag => l_stud_det.manual_verif_flag,
930 x_fed_verif_status => l_stud_det.fed_verif_status,
931 x_fed_verif_status_date => l_stud_det.fed_verif_status_date,
932 x_inst_verif_status => l_stud_det.inst_verif_status,
933 x_nslds_eligible => l_stud_det.nslds_eligible,
934 x_ede_correction_batch_id => l_stud_det.ede_correction_batch_id,
935 x_fa_process_status_date => l_stud_det.fa_process_status_date,
936 x_isir_corr_status => l_stud_det.isir_corr_status,
937 x_isir_corr_status_date => l_stud_det.isir_corr_status_date,
938 x_isir_status => l_stud_det.isir_status,
939 x_isir_status_date => l_stud_det.isir_status_date,
940 x_coa_code_f => l_stud_det.coa_code_f,
941 x_coa_code_i => l_stud_det.coa_code_i,
942 x_coa_f => l_stud_det.coa_f,
943 x_coa_i => l_stud_det.coa_i,
944 x_disbursement_hold => l_stud_det.disbursement_hold,
945 x_fa_process_status => l_stud_det.fa_process_status,
946 x_notification_status => l_stud_det.notification_status,
947 x_notification_status_date => l_stud_det.notification_status_date,
948 x_packaging_status => l_stud_det.packaging_status,
949 x_packaging_status_date => l_stud_det.packaging_status_date,
950 x_total_package_accepted => l_stud_det.total_package_accepted,
951 x_total_package_offered => l_stud_det.total_package_offered,
952 x_admstruct_id => l_stud_det.admstruct_id,
953 x_admsegment_1 => l_stud_det.admsegment_1,
954 x_admsegment_2 => l_stud_det.admsegment_2,
955 x_admsegment_3 => l_stud_det.admsegment_3,
956 x_admsegment_4 => l_stud_det.admsegment_4,
957 x_admsegment_5 => l_stud_det.admsegment_5,
958 x_admsegment_6 => l_stud_det.admsegment_6,
959 x_admsegment_7 => l_stud_det.admsegment_7,
960 x_admsegment_8 => l_stud_det.admsegment_8,
961 x_admsegment_9 => l_stud_det.admsegment_9,
962 x_admsegment_10 => l_stud_det.admsegment_10,
963 x_admsegment_11 => l_stud_det.admsegment_11,
964 x_admsegment_12 => l_stud_det.admsegment_12,
965 x_admsegment_13 => l_stud_det.admsegment_13,
966 x_admsegment_14 => l_stud_det.admsegment_14,
967 x_admsegment_15 => l_stud_det.admsegment_15,
968 x_admsegment_16 => l_stud_det.admsegment_16,
969 x_admsegment_17 => l_stud_det.admsegment_17,
970 x_admsegment_18 => l_stud_det.admsegment_18,
971 x_admsegment_19 => l_stud_det.admsegment_19,
972 x_admsegment_20 => l_stud_det.admsegment_20,
973 x_packstruct_id => l_stud_det.packstruct_id,
974 x_packsegment_1 => l_stud_det.packsegment_1,
975 x_packsegment_2 => l_stud_det.packsegment_2,
976 x_packsegment_3 => l_stud_det.packsegment_3,
977 x_packsegment_4 => l_stud_det.packsegment_4,
978 x_packsegment_5 => l_stud_det.packsegment_5,
979 x_packsegment_6 => l_stud_det.packsegment_6,
980 x_packsegment_7 => l_stud_det.packsegment_7,
981 x_packsegment_8 => l_stud_det.packsegment_8,
982 x_packsegment_9 => l_stud_det.packsegment_9,
983 x_packsegment_10 => l_stud_det.packsegment_10,
984 x_packsegment_11 => l_stud_det.packsegment_11,
985 x_packsegment_12 => l_stud_det.packsegment_12,
986 x_packsegment_13 => l_stud_det.packsegment_13,
987 x_packsegment_14 => l_stud_det.packsegment_14,
988 x_packsegment_15 => l_stud_det.packsegment_15,
989 x_packsegment_16 => l_stud_det.packsegment_16,
990 x_packsegment_17 => l_stud_det.packsegment_17,
991 x_packsegment_18 => l_stud_det.packsegment_18,
992 x_packsegment_19 => l_stud_det.packsegment_19,
993 x_packsegment_20 => l_stud_det.packsegment_20,
994 x_miscstruct_id => l_stud_det.miscstruct_id,
995 x_miscsegment_1 => l_stud_det.miscsegment_1,
996 x_miscsegment_2 => l_stud_det.miscsegment_2,
997 x_miscsegment_3 => l_stud_det.miscsegment_3,
998 x_miscsegment_4 => l_stud_det.miscsegment_4,
999 x_miscsegment_5 => l_stud_det.miscsegment_5,
1000 x_miscsegment_6 => l_stud_det.miscsegment_6,
1001 x_miscsegment_7 => l_stud_det.miscsegment_7,
1002 x_miscsegment_8 => l_stud_det.miscsegment_8,
1003 x_miscsegment_9 => l_stud_det.miscsegment_9,
1004 x_miscsegment_10 => l_stud_det.miscsegment_10,
1005 x_miscsegment_11 => l_stud_det.miscsegment_11,
1006 x_miscsegment_12 => l_stud_det.miscsegment_12,
1007 x_miscsegment_13 => l_stud_det.miscsegment_13,
1008 x_miscsegment_14 => l_stud_det.miscsegment_14,
1009 x_miscsegment_15 => l_stud_det.miscsegment_15,
1010 x_miscsegment_16 => l_stud_det.miscsegment_16,
1011 x_miscsegment_17 => l_stud_det.miscsegment_17,
1012 x_miscsegment_18 => l_stud_det.miscsegment_18,
1013 x_miscsegment_19 => l_stud_det.miscsegment_19,
1014 x_miscsegment_20 => l_stud_det.miscsegment_20,
1015 x_prof_judgement_flg => l_stud_det.prof_judgement_flg,
1016 x_nslds_data_override_flg => l_stud_det.nslds_data_override_flg ,
1017 x_target_group => l_stud_det.target_group,
1018 x_coa_fixed => l_stud_det.coa_fixed,
1019 x_coa_pell => l_stud_det.coa_pell,
1020 x_profile_status => l_stud_det.profile_status,
1021 x_profile_status_date => l_stud_det.profile_status_date,
1022 x_profile_fc => l_stud_det.profile_fc,
1023 x_tolerance_amount => l_stud_det.tolerance_amount,
1024 x_manual_disb_hold => l_stud_det.manual_disb_hold,
1025 x_mode => 'R',
1026 x_pell_alt_expense => l_stud_det.pell_alt_expense,
1027 x_assoc_org_num => l_stud_det.assoc_org_num,
1028 x_award_fmly_contribution_type => l_stud_det.award_fmly_contribution_type,
1029 x_isir_locked_by => l_stud_det.isir_locked_by,
1030 x_adnl_unsub_loan_elig_flag => l_stud_det.adnl_unsub_loan_elig_flag,
1031 x_lock_awd_flag => l_stud_det.lock_awd_flag,
1032 x_lock_coa_flag => l_stud_det.lock_coa_flag
1033 );
1034
1035 EXCEPTION
1036 WHEN OTHERS THEN
1037 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1038 fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_FA_BASE_AWD' ||' ' || SQLERRM);
1039 igs_ge_msg_stack.add;
1040 app_exception.raise_exception ;
1041 END update_fabase_awds;
1042
1043
1044 FUNCTION get_org_id RETURN NUMBER AS
1045 l_org_id NUMBER(15);
1046 CURSOR get_orgid IS
1047 SELECT NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),NULL)
1048 FROM dual;
1049 BEGIN
1050 -- Commented out NOCOPY by jbegum as part of Enh bug #2222272
1051 -- This code has been commented out NOCOPY to remove multi org functionality from OSS
1052 /* OPEN get_orgid;
1053 FETCH get_orgid INTO l_org_id;
1054 CLOSE get_orgid;*/
1055
1056 -- Added by jbegum as part of Enh bug #2222272
1057 -- The org_id is being passed as null to remove multi org functionality from OSS
1058 l_org_id := NULL;
1059
1060 RETURN l_org_id;
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1065 fnd_message.set_token('NAME','IGF_AW_GEN.GET_ORG_ID' ||' ' || SQLERRM);
1066 igs_ge_msg_stack.add;
1067 app_exception.raise_exception;
1068
1069 END get_org_id;
1070
1071 /*
1072 || Change History :
1073 || Who When What
1074 || (reverse chronological order - newest change first)
1075 || tsailaja 17-Jan-2006 removed Exception handling as it would be handled by the calling routine
1076 || changes made against bug No: 4947880
1077 */
1078 PROCEDURE set_org_id(p_context IN VARCHAR2) AS
1079 p_org_id Varchar2(10);
1080 BEGIN
1081 igs_ge_gen_003.set_org_id(p_context);
1082 END set_org_id;
1083
1084
1085 PROCEDURE update_fmast( x_old_ref in igf_aw_award_all%ROWTYPE,
1086 x_new_ref in igf_aw_award_all%ROWTYPE,
1087 flag in Varchar )
1088 IS
1089 /*
1090 || Created By : pkpatel
1091 || Created On : 11-DEC-2001
1092 || Purpose :
1093 || Known limitations, enhancements or remarks :
1094 || Change History :
1095 || Who When What
1096 || (reverse chronological order - newest change first)
1097 || museshad 24-Oct-2005 cancelled_amt was getting updated incorrectly.
1098 || Fixed this. Note that, when a Offered/Accepted award (with
1099 || award amount say X) is Cancelled, then the cancelled_amt
1100 || of the fund is 0 and not X. The cancelled_amt for a fund
1101 || always remains at 0 and it is not used anywhere.
1102 ||
1103 || museshad 14-Jul-2005 Build FA 140.
1104 || Modified TBH call due to the addition of new
1105 || columns to igf_aw_fund_mast_all table.
1106 ||
1107 || smvk 10_feb_2003 Bug # 2758812. Added send_without_doc column in the igf_aw_fund_mast_pkg.update_row call.
1108 ||
1109 || brajendr 19-Dec-2002 Bug # 2708599
1110 || Modifed the procedure update_fmast for deletion of Simulated awards,
1111 || earlier it was looking at new refferences, modified to old refferences
1112 ||
1113 || pkpatel 11-DEC-2001 Bug NO:2154941 Disbursement DLD
1114 || Removed the reference to dropped columns from, IGF_AW_FUND_MAST
1115 ||
1116 || cdcruz 06-MAY-2002 Bug NO: 2310222 Summary Tab updation
1117 || All summary column counts and totals modified
1118 */
1119
1120
1121 CURSOR c_fmast( x_fund_id igf_aw_fund_mast.fund_id%TYPE) IS
1122 SELECT fmast.*
1123 FROM igf_aw_fund_mast fmast
1124 WHERE fund_id = x_fund_id ;
1125
1126 l_fmast c_fmast%ROWTYPE;
1127
1128 BEGIN
1129
1130 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1131 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','flag:'||flag);
1132 END IF;
1133 IF ( flag = 'DELETE' ) THEN
1134 OPEN c_fmast( x_old_ref.fund_id );
1135 ELSE
1136 OPEN c_fmast( x_new_ref.fund_id );
1137 END IF;
1138
1139 FETCH c_fmast INTO l_fmast;
1140 IF ( c_fmast%NOTFOUND ) THEN
1141 CLOSE c_fmast;
1142 RAISE NO_DATA_FOUND;
1143 END IF;
1144 CLOSE c_fmast;
1145
1146 -- For simulation no updation to fund manager
1147 -- Simulated award cannot be change to any other status
1148
1149 IF ( x_old_ref.award_status = 'SIMULATED' ) THEN
1150 RETURN;
1151 END IF;
1152
1153 IF ( flag = 'INSERT' ) THEN
1154
1155
1156 IF ( x_new_ref.award_status = 'OFFERED' ) THEN
1157 l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1158 l_fmast.total_offered := NVL(l_fmast.total_offered,0) + 1 ;
1159
1160 ELSIF ( x_new_ref.award_status = 'ACCEPTED' ) THEN
1161 l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1162 l_fmast.total_offered := NVL(l_fmast.total_offered,0) + 1 ;
1163 l_fmast.accepted_amt := NVL(l_fmast.accepted_amt,0) + NVL(x_new_ref.accepted_amt,0) ;
1164 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) + 1 ;
1165
1166 END IF;
1167
1168
1169 ELSIF ( flag = 'UPDATE' ) THEN
1170
1171 -- First update the amounts which is independent of Status
1172
1173 l_fmast.accepted_amt := NVL(l_fmast.accepted_amt,0) - NVL(x_old_ref.accepted_amt,0) + NVL(x_new_ref.accepted_amt,0) ;
1174 l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) - NVL(x_old_ref.offered_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1175 l_fmast.disbursed_amt := NVL(l_fmast.disbursed_amt ,0) - NVL(x_old_ref.paid_amt ,0) + NVL(x_new_ref.paid_amt,0) ;
1176
1177 -- Update the Disbursed Count ( If the paid amt has just incremented then +1
1178
1179 IF NVL(x_new_ref.paid_amt,0) > 0 and NVL(x_old_ref.paid_amt,0) = 0 THEN
1180
1181 l_fmast.total_disbursed := NVL(l_fmast.total_disbursed,0) + 1 ;
1182
1183 ELSIF NVL(x_new_ref.paid_amt,0) = 0 and NVL(x_old_ref.paid_amt,0) > 0 THEN
1184
1185 l_fmast.total_disbursed := NVL(l_fmast.total_disbursed,0) - 1 ;
1186
1187 END IF;
1188
1189 -- Status updation determines the change in counts
1190
1191 IF ( x_old_ref.award_status = 'OFFERED' ) THEN
1192
1193 IF ( x_new_ref.award_status = 'ACCEPTED' ) THEN
1194 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) + 1 ;
1195
1196 ELSIF ( x_new_ref.award_status = 'CANCELLED' ) THEN
1197 l_fmast.cancelled_amt := NVL(l_fmast.cancelled_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1198 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) + 1 ;
1199
1200 ELSIF ( x_new_ref.award_status = 'DECLINED' ) THEN
1201 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1202 l_fmast.total_declined := NVL(l_fmast.total_declined,0) + 1 ;
1203
1204 END IF;
1205
1206 ELSIF ( x_old_ref.award_status = 'ACCEPTED' ) THEN
1207
1208 IF ( x_new_ref.award_status = 'OFFERED' ) THEN
1209 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) - 1 ;
1210
1211 ELSIF ( x_new_ref.award_status = 'CANCELLED' ) THEN
1212 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) - 1 ;
1213 l_fmast.cancelled_amt := NVL(l_fmast.cancelled_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1214 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) + 1 ;
1215
1216 ELSIF ( x_new_ref.award_status = 'DECLINED' ) THEN
1217 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) - 1 ;
1218 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1219 l_fmast.total_declined := NVL(l_fmast.total_declined,0) + 1 ;
1220
1221 END IF;
1222
1223 ELSIF ( x_old_ref.award_status = 'CANCELLED' ) THEN
1224
1225 IF ( x_new_ref.award_status = 'ACCEPTED' ) THEN
1226 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) - 1 ;
1227 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) + 1 ;
1228
1229 ELSIF ( x_new_ref.award_status = 'OFFERED' ) THEN
1230 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) - 1 ;
1231
1232 ELSIF ( x_new_ref.award_status = 'DECLINED' ) THEN
1233 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) - 1 ;
1234 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) + NVL(x_new_ref.offered_amt,0) ;
1235 l_fmast.total_declined := NVL(l_fmast.total_declined,0) + 1 ;
1236
1237 END IF;
1238
1239 ELSIF ( x_old_ref.award_status = 'DECLINED' ) THEN
1240
1241 IF ( x_new_ref.award_status = 'ACCEPTED' ) THEN
1242 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) - NVL(x_new_ref.offered_amt,0) ;
1243 l_fmast.total_declined := NVL(l_fmast.total_declined,0) - 1 ;
1244 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) + 1 ;
1245
1246 ELSIF ( x_new_ref.award_status = 'OFFERED' ) THEN
1247 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) - NVL(x_new_ref.offered_amt,0) ;
1248 l_fmast.total_declined := NVL(l_fmast.total_declined,0) - 1 ;
1249
1250 ELSIF ( x_new_ref.award_status = 'CANCELLED' ) THEN
1251 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) - NVL(x_new_ref.offered_amt,0) ;
1252 l_fmast.total_declined := NVL(l_fmast.total_declined,0) - 1 ;
1253 l_fmast.cancelled_amt := NVL(l_fmast.cancelled_amt,0) + x_new_ref.offered_amt ;
1254 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) + 1 ;
1255
1256 END IF;
1257 END IF;
1258
1259 ELSIF ( flag = 'DELETE' ) THEN
1260
1261
1262 l_fmast.offered_amt := NVL(l_fmast.offered_amt,0) - NVL(x_old_ref.offered_amt,0) ;
1263 l_fmast.accepted_amt := NVL(l_fmast.accepted_amt,0) - NVL(x_old_ref.accepted_amt,0) ;
1264
1265 l_fmast.total_offered := NVL(l_fmast.total_offered,0) - 1 ;
1266
1267
1268 IF ( x_old_ref.award_status = 'ACCEPTED' ) THEN
1269
1270 l_fmast.total_accepted := NVL(l_fmast.total_accepted,0) - 1 ;
1271
1272 ELSIF ( x_old_ref.award_status = 'CANCELLED' ) THEN
1273 l_fmast.cancelled_amt := NVL(l_fmast.cancelled_amt,0) - NVL(x_old_ref.offered_amt,0) ;
1274 l_fmast.total_cancelled := NVL(l_fmast.total_cancelled,0) - 1 ;
1275
1276 ELSIF ( x_old_ref.award_status = 'DECLINED' ) THEN
1277 l_fmast.declined_amt := NVL(l_fmast.declined_amt,0) - NVL(x_old_ref.offered_amt,0) ;
1278 l_fmast.total_declined := NVL(l_fmast.total_declined,0) - 1 ;
1279
1280 END IF;
1281
1282
1283 END IF;
1284
1285 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1286 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.offered_amt:'||l_fmast.offered_amt);
1287 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.accepted_amt:'||l_fmast.accepted_amt);
1288 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_offered:'||l_fmast.total_offered);
1289 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.cancelled_amt:'||l_fmast.cancelled_amt);
1290 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_cancelled:'||l_fmast.total_cancelled);
1291 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.declined_amt:'||l_fmast.declined_amt);
1292 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_fmast.debug','l_fmast.total_declined:'||l_fmast.total_declined);
1293 END IF;
1294 igf_aw_fund_mast_pkg.update_row (
1295 x_rowid => l_fmast.row_id,
1296 x_fund_id => l_fmast.fund_id,
1297 x_fund_code => l_fmast.fund_code,
1298 x_ci_cal_type => l_fmast.ci_cal_type,
1299 x_ci_sequence_number => l_fmast.ci_sequence_number,
1300 x_description => l_fmast.description,
1301 x_discontinue_fund => l_fmast.discontinue_fund,
1302 x_entitlement => l_fmast.entitlement,
1303 x_auto_pkg => l_fmast.auto_pkg,
1304 x_self_help => l_fmast.self_help,
1305 x_allow_man_pkg => l_fmast.allow_man_pkg,
1306 x_update_need => l_fmast.update_need,
1307 x_disburse_fund => l_fmast.disburse_fund,
1308 x_available_amt => l_fmast.available_amt,
1309 x_offered_amt => l_fmast.offered_amt,
1310 x_pending_amt => l_fmast.pending_amt,
1311 x_accepted_amt => l_fmast.accepted_amt,
1312 x_declined_amt => l_fmast.declined_amt,
1313 x_cancelled_amt => l_fmast.cancelled_amt,
1314 x_remaining_amt => l_fmast.remaining_amt,
1315 x_enrollment_status => l_fmast.enrollment_status,
1316 x_prn_award_letter => l_fmast.prn_award_letter,
1317 x_over_award_amt => l_fmast.over_award_amt,
1318 x_over_award_perct => l_fmast.over_award_perct,
1319 x_min_award_amt => l_fmast.min_award_amt,
1320 x_max_award_amt => l_fmast.max_award_amt,
1321 x_max_yearly_amt => l_fmast.max_yearly_amt,
1322 x_max_life_amt => l_fmast.max_life_amt,
1323 x_max_life_term => l_fmast.max_life_term,
1324 x_fm_fc_methd => l_fmast.fm_fc_methd,
1325 x_roundoff_fact => l_fmast.roundoff_fact,
1326 x_replace_fc => l_fmast.replace_fc,
1327 x_allow_overaward => l_fmast.allow_overaward,
1328 x_pckg_awd_stat => l_fmast.pckg_awd_stat,
1329 x_org_record_req => l_fmast.org_record_req,
1330 x_disb_record_req => l_fmast.disb_record_req,
1331 x_prom_note_req => l_fmast.prom_note_req,
1332 x_min_num_disb => l_fmast.min_num_disb,
1333 x_max_num_disb => l_fmast.max_num_disb,
1334 X_FEE_TYPE => l_fmast.FEE_TYPE ,
1335 x_total_offered => l_fmast.total_offered,
1336 x_total_accepted => l_fmast.total_accepted,
1337 x_total_declined => l_fmast.total_declined,
1338 x_total_revoked => l_fmast.total_revoked,
1339 x_total_cancelled => l_fmast.total_cancelled,
1340 x_total_disbursed => l_fmast.total_disbursed,
1341 x_total_committed => l_fmast.total_committed,
1342 x_committed_amt => l_fmast.committed_amt,
1343 x_disbursed_amt => l_fmast.disbursed_amt,
1344 x_awd_notice_txt => l_fmast.awd_notice_txt,
1345 x_attribute_category => l_fmast.attribute_category,
1346 x_attribute1 => l_fmast.attribute1,
1347 x_attribute2 => l_fmast.attribute2,
1348 x_attribute3 => l_fmast.attribute3,
1349 x_attribute4 => l_fmast.attribute4,
1350 x_attribute5 => l_fmast.attribute5,
1351 x_attribute6 => l_fmast.attribute6,
1352 x_attribute7 => l_fmast.attribute7,
1353 x_attribute8 => l_fmast.attribute8,
1354 x_attribute9 => l_fmast.attribute9,
1355 x_attribute10 => l_fmast.attribute10,
1356 x_attribute11 => l_fmast.attribute11,
1357 x_attribute12 => l_fmast.attribute12,
1358 x_attribute13 => l_fmast.attribute13,
1359 x_attribute14 => l_fmast.attribute14,
1360 x_attribute15 => l_fmast.attribute15,
1361 x_attribute16 => l_fmast.attribute16,
1362 x_attribute17 => l_fmast.attribute17,
1363 x_attribute18 => l_fmast.attribute18,
1364 x_attribute19 => l_fmast.attribute19,
1365 x_attribute20 => l_fmast.attribute20,
1366 x_disb_verf_da => l_fmast.disb_verf_da,
1367 x_fund_exp_da => l_fmast.fund_exp_da,
1368 x_nslds_disb_da => l_fmast.nslds_disb_da,
1369 x_disb_exp_da => l_fmast.disb_exp_da,
1370 x_fund_recv_reqd => l_fmast.fund_recv_reqd,
1371 x_show_on_bill => l_fmast.show_on_bill,
1372 x_bill_desc => l_fmast.bill_desc,
1373 x_credit_type_id => l_fmast.credit_type_id,
1374 x_spnsr_ref_num => l_fmast.spnsr_ref_num,
1375 x_party_id => l_fmast.party_id,
1376 x_spnsr_fee_type => l_fmast.spnsr_fee_type,
1377 x_min_credit_points => l_fmast.min_credit_points,
1378 x_group_id => l_fmast.group_id,
1379 x_threshold_perct => l_fmast.threshold_perct,
1380 x_threshold_value => l_fmast.threshold_value,
1381 x_spnsr_attribute_category => l_fmast.spnsr_attribute_category,
1382 x_spnsr_attribute1 => l_fmast.spnsr_attribute1,
1383 x_spnsr_attribute2 => l_fmast.spnsr_attribute2,
1384 x_spnsr_attribute3 => l_fmast.spnsr_attribute3,
1385 x_spnsr_attribute4 => l_fmast.spnsr_attribute4,
1386 x_spnsr_attribute5 => l_fmast.spnsr_attribute5,
1387 x_spnsr_attribute6 => l_fmast.spnsr_attribute6,
1388 x_spnsr_attribute7 => l_fmast.spnsr_attribute7,
1389 x_spnsr_attribute8 => l_fmast.spnsr_attribute8,
1390 x_spnsr_attribute9 => l_fmast.spnsr_attribute9,
1391 x_spnsr_attribute10 => l_fmast.spnsr_attribute10,
1392 x_spnsr_attribute11 => l_fmast.spnsr_attribute11,
1393 x_spnsr_attribute12 => l_fmast.spnsr_attribute12,
1394 x_spnsr_attribute13 => l_fmast.spnsr_attribute13,
1395 x_spnsr_attribute14 => l_fmast.spnsr_attribute14,
1396 x_spnsr_attribute15 => l_fmast.spnsr_attribute15,
1397 x_spnsr_attribute16 => l_fmast.spnsr_attribute16,
1398 x_spnsr_attribute17 => l_fmast.spnsr_attribute17,
1399 x_spnsr_attribute18 => l_fmast.spnsr_attribute18,
1400 x_spnsr_attribute19 => l_fmast.spnsr_attribute19,
1401 x_spnsr_attribute20 => l_fmast.spnsr_attribute20,
1402 x_ver_app_stat_override => l_fmast.ver_app_stat_override,
1403 x_gift_aid => l_fmast.gift_aid,
1404 x_send_without_doc => l_fmast.send_without_doc, -- Bug # 2758812. Added send_without_doc column.
1405 x_re_pkg_verif_flag => l_fmast.re_pkg_verif_flag,
1406 x_donot_repkg_if_code => l_fmast.donot_repkg_if_code,
1407 x_lock_award_flag => l_fmast.lock_award_flag,
1408 x_disb_rounding_code => l_fmast.disb_rounding_code,
1409 x_view_only_flag => l_fmast.view_only_flag,
1410 x_accept_less_amt_flag => l_fmast.accept_less_amt_flag,
1411 x_allow_inc_post_accept_flag => l_fmast.allow_inc_post_accept_flag,
1412 x_min_increase_amt => l_fmast.min_increase_amt,
1413 x_allow_dec_post_accept_flag => l_fmast.allow_dec_post_accept_flag,
1414 x_min_decrease_amt => l_fmast.min_decrease_amt,
1415 x_allow_decln_post_accept_flag => l_fmast.allow_decln_post_accept_flag,
1416 x_status_after_decline => l_fmast.status_after_decline,
1417 x_fund_information_txt => l_fmast.fund_information_txt,
1418 x_mode => 'R'
1419 );
1420
1421 EXCEPTION
1422 WHEN OTHERS THEN
1423 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1424 fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_FMAST' ||' ' || SQLERRM);
1425 igs_ge_msg_stack.add;
1426 app_exception.raise_exception;
1427
1428 END update_fmast;
1429
1430 PROCEDURE update_award( p_award_id IN igf_aw_award_all.award_id%TYPE,
1431 p_disb_num IN igf_aw_awd_disb_all.disb_num%TYPE,
1432 p_disb_amt IN igf_aw_awd_disb_all.disb_net_amt%TYPE,
1433 p_disb_dt IN igf_aw_awd_disb_all.disb_date%TYPE,
1434 p_action IN VARCHAR2,
1435 x_called_from IN VARCHAR2
1436 )
1437 IS
1438 --------------------------------------------------------------------------------------
1439 -- museshad 05-Apr-2006 Bug 5140851. Do not reset paid amount for FWS awards
1440 -- bcoz paid amount for FWS is maintained only at award
1441 -- level. Retain award paid amout for FWS awards.
1442 --------------------------------------------------------------------------------------
1443 -- museshad 08-Aug-2005 Bug 3954451. Cancel award(and make offered amount
1444 -- and accepted amount as 0), if all disbursements
1445 -- in the award are cancelled.
1446 --------------------------------------------------------------------------------------
1447 -- veramach 05-Jul-2004 bug 3682032 Modified logic so that if error 'fund locked'
1448 -- error is thrown by the awards table handler, it is passed
1449 -- correctly to the caller
1450 --------------------------------------------------------------------------------------
1451 -- sjadhav 10-Dec-2003 FA 131 Changes
1452 -- De-link auto update of Pell Disbursement and Pell
1453 -- origination amounts
1454 --------------------------------------------------------------------------------------
1455 -- veramach 1-NOV-2003 FA 125(#3160568) Added apdlans_id in the calls
1456 -- to igf_aw_award_pkg.update_row
1457 --------------------------------------------------------------------------------------
1458 -- rasahoo 09-Sep-2003 Bug 33094878 Added the call to update_fabase_awds
1459 -- as the FA base rec was not getting updated when any
1460 -- DML Operations performed from Disbursement form.
1461 --------------------------------------------------------------------------------------
1462 -- sjadhav 05-Feb-2003 FA116 Build - Bug 2758812
1463 -- Modified update_award to set pell origination
1464 -- status to 'R'.
1465 --------------------------------------------------------------------------------------
1466 -- sjadhav Jan 25,2002 Bug ID : 2154941
1467 -- This routine is called throgh table handler of
1468 -- igf_aw_awd_Disb table. This routine updates
1469 -- igf_aw_Award,igf_gr_rfms,igf_gr_rfms_disb
1470 -- tables to reflect the changes made in
1471 -- igf_aw_Awd_disb table
1472 --------------------------------------------------------------------------------------
1473
1474 --
1475 -- Cursor to Get The Award Record which will be updated
1476 --
1477 CURSOR cur_award (p_award_id igf_aw_award_all.award_id%TYPE)
1478 IS
1479 SELECT
1480 awd.rowid row_id,awd.*
1481 FROM
1482 igf_aw_award_all awd
1483 WHERE
1484 award_id = p_award_id
1485 FOR UPDATE OF offered_amt;
1486
1487 award_rec cur_award%ROWTYPE;
1488
1489 --
1490 -- Cursor to get Total Amounts from Disbursement Table
1491 -- The paid amount should be updated by the Student Finance process,
1492 -- that is why we are not taking the Paid amount in this cursor
1493 --
1494 CURSOR cur_disb (p_award_id igf_aw_award_all.award_id%TYPE)
1495 IS
1496 SELECT
1497 SUM(NVL(disb_gross_amt,0)) offered_amt,
1498 SUM(NVL(disb_accepted_amt,0)) accepted_amt,
1499 SUM(NVL(disb_paid_amt,0)) paid_amt
1500 FROM
1501 igf_aw_awd_disb_all
1502 WHERE
1503 award_id = p_award_id;
1504
1505 disb_rec cur_disb%ROWTYPE;
1506
1507 -- Get the fund code of the fund.
1508 CURSOR cur_fund_dtls ( c_fund_id igf_aw_fund_mast.fund_id%TYPE)
1509 IS
1510 SELECT
1511 cat.fed_fund_code
1512 FROM
1513 igf_aw_fund_mast_all fmast,
1514 igf_aw_fund_cat_all cat
1515 WHERE
1516 fmast.fund_id = c_fund_id AND
1517 fmast.fund_code = cat.fund_code;
1518
1519 cur_fund_dtls_rec cur_fund_dtls%ROWTYPE;
1520
1521 -- museshad (Bug 3954451)
1522 -- Returns 'X' for all the non-cancelled disbursements in the award
1523 CURSOR cur_get_cancl_disb(cp_award_id igf_aw_award_all.award_id%TYPE)
1524 IS
1525 SELECT 'X'
1526 FROM igf_aw_awd_disb_all
1527 WHERE award_id = cp_award_id AND
1528 trans_type <> 'C';
1529
1530 l_cur_get_cancl_disb_rec cur_get_cancl_disb%ROWTYPE;
1531 -- museshad (Bug 3954451)
1532
1533 -- Get accepted amt
1534 CURSOR c_get_accept_amt(
1535 cp_award_id igf_aw_award_all.award_id%TYPE
1536 ) IS
1537 SELECT SUM(disb_accepted_amt) accepted_amt
1538 FROM igf_aw_awd_disb_all
1539 WHERE award_id = cp_award_id
1540 AND trans_type <> 'C';
1541 l_get_accept_amt c_get_accept_amt%ROWTYPE;
1542
1543 l_app VARCHAR2(80);
1544 l_name VARCHAR2(80);
1545 l_v_called_from VARCHAR2(30);
1546
1547 -- museshad (Bug 3954451)
1548 l_awd_status igf_aw_award_all.award_status%TYPE;
1549 l_awd_off_amt igf_aw_award_all.offered_amt%TYPE;
1550 l_awd_acc_amt igf_aw_award_all.accepted_amt%TYPE;
1551 l_awd_proc_status_code igf_aw_award_all.awd_proc_status_code%TYPE;
1552 -- museshad (Bug 3954451)
1553
1554 BEGIN
1555 l_v_called_from := x_called_from;
1556 --
1557 -- Get Number of Disbursements
1558 --
1559 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1560 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_award_id: '||p_award_id);
1561 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_num: '||p_disb_num);
1562 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_amt: '||p_disb_amt);
1563 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_disb_dt: '||p_disb_dt);
1564 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','p_action: '||p_action);
1565 END IF;
1566
1567
1568 OPEN cur_award(p_award_id);
1569 FETCH cur_award INTO award_rec;
1570
1571 IF cur_award%NOTFOUND THEN
1572 CLOSE cur_award;
1573 NULL;
1574
1575 ELSIF cur_award%FOUND THEN
1576 CLOSE cur_award;
1577
1578 OPEN cur_disb(p_award_id);
1579 FETCH cur_disb INTO disb_rec;
1580 CLOSE cur_disb;
1581
1582 OPEN cur_fund_dtls( award_rec.fund_id);
1583 FETCH cur_fund_dtls INTO cur_fund_dtls_rec;
1584 CLOSE cur_fund_dtls;
1585
1586 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1587 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','disb_rec.paid_amt: '||disb_rec.paid_amt);
1588 END IF;
1589
1590 /*
1591 If the user updates the disb_accepted_amt of an award while the award is still in
1592 OFFERED state, silently update the status to ACCEPTED
1593 */
1594 IF award_rec.award_status = 'OFFERED' THEN
1595 l_get_accept_amt := NULL;
1596 OPEN c_get_accept_amt(p_award_id);
1597 FETCH c_get_accept_amt INTO l_get_accept_amt;
1598 CLOSE c_get_accept_amt;
1599
1600 IF NVL(l_get_accept_amt.accepted_amt,-1) > 0 THEN
1601 l_awd_status := 'ACCEPTED';
1602 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1603 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_award.debug','setting the award status to accepted');
1604 END IF;
1605 END IF;
1606 END IF;
1607
1608 -- museshad (Bug 3954451)
1609 IF chk_disb_status(p_award_id => p_award_id) THEN
1610 -- All disbursements in the award are cancelled.
1611 -- Mark Award has cancelled
1612 IF award_rec.award_status <> 'DECLINED' THEN
1613 l_awd_status := 'CANCELLED';
1614 l_awd_off_amt := 0;
1615 l_awd_acc_amt := 0;
1616 l_awd_proc_status_code := 'AWARDED';
1617 ELSE
1618 l_awd_status := 'DECLINED';
1619 l_awd_off_amt := disb_rec.offered_amt;
1620 l_awd_acc_amt := 0;
1621 l_awd_proc_status_code := 'AWARDED';
1622 END IF;
1623 -- Log
1624 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1625 fnd_log.string(fnd_log.level_statement,
1626 'igf.plsql.igf_aw_gen.update_award.debug',
1627 'All the disbursements in award ' ||award_rec.award_id|| ' are cancelled. Cancelling the award.');
1628 END IF;
1629 ELSE
1630 -- There is atleast one uncancelled disbursement in the award.
1631 -- Retain existing values
1632 l_awd_status := award_rec.award_status;
1633 l_awd_off_amt := disb_rec.offered_amt;
1634 l_awd_acc_amt := disb_rec.accepted_amt;
1635 l_awd_proc_status_code := award_rec.awd_proc_status_code;
1636 END IF;
1637 -- museshad (Bug 3954451)
1638
1639 /* Bug 5140851: Do not reset paid amount for FWS awards bcoz paid amount for FWS is maintained
1640 only at award level. Retain award paid amout for FWS awards.
1641 */
1642 IF cur_fund_dtls_rec.fed_fund_code = 'FWS' THEN
1643 disb_rec.paid_amt := award_rec.paid_amt;
1644 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1645 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_aw_gen.update_award.debug',
1646 'Not updtaing award paid_amt with sum of disb paid_amt bcoz this is an FWS award. Retaining existing award paid amt. Award Id= '
1647 ||award_rec.award_id);
1648 END IF;
1649 END IF;
1650
1651 igf_aw_award_pkg.update_row ( x_mode => 'R',
1652 x_rowid => award_rec.row_id,
1653 x_award_id => award_rec.award_id,
1654 x_fund_id => award_rec.fund_id,
1655 x_base_id => award_rec.base_id,
1656 x_offered_amt => l_awd_off_amt,
1657 x_accepted_amt => l_awd_acc_amt,
1658 x_paid_amt => disb_rec.paid_amt,
1659 x_packaging_type => award_rec.packaging_type,
1660 x_batch_id => award_rec.batch_id,
1661 x_manual_update => award_rec.manual_update,
1662 x_rules_override => award_rec.rules_override,
1663 x_award_date => award_rec.award_date,
1664 x_award_status => l_awd_status,
1665 x_attribute_category => award_rec.attribute_category,
1666 x_attribute1 => award_rec.attribute1,
1667 x_attribute2 => award_rec.attribute2,
1668 x_attribute3 => award_rec.attribute3,
1669 x_attribute4 => award_rec.attribute4,
1670 x_attribute5 => award_rec.attribute5,
1671 x_attribute6 => award_rec.attribute6,
1672 x_attribute7 => award_rec.attribute7,
1673 x_attribute8 => award_rec.attribute8,
1674 x_attribute9 => award_rec.attribute9,
1675 x_attribute10 => award_rec.attribute10,
1676 x_attribute11 => award_rec.attribute11,
1677 x_attribute12 => award_rec.attribute12,
1678 x_attribute13 => award_rec.attribute13,
1679 x_attribute14 => award_rec.attribute14,
1680 x_attribute15 => award_rec.attribute15,
1681 x_attribute16 => award_rec.attribute16,
1682 x_attribute17 => award_rec.attribute17,
1683 x_attribute18 => award_rec.attribute18,
1684 x_attribute19 => award_rec.attribute19,
1685 x_attribute20 => award_rec.attribute20,
1686 x_rvsn_id => award_rec.rvsn_id,
1687 x_alt_pell_schedule => award_rec.alt_pell_schedule,
1688 x_award_number_txt => award_rec.award_number_txt,
1689 x_legacy_record_flag => NULL,
1690 x_adplans_id => award_rec.adplans_id,
1691 x_lock_award_flag => award_rec.lock_award_flag,
1692 x_app_trans_num_txt => award_rec.app_trans_num_txt,
1693 x_awd_proc_status_code => award_rec.awd_proc_status_code,
1694 x_notification_status_code => award_rec.notification_status_code,
1695 x_notification_status_date => award_rec.notification_status_date,
1696 x_called_from => l_v_called_from,
1697 x_publish_in_ss_flag => award_rec.publish_in_ss_flag
1698 );
1699
1700 END IF;
1701
1702 --
1703 -- Update FA Base record
1704 --
1705 update_fabase_awds(award_rec.base_id, 'REVISED');
1706
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 fnd_message.parse_encoded(fnd_message.get_encoded,l_app,l_name);
1710 IF l_name IN (
1711 'IGF_SL_CL_CHG_BSSN_REQD',
1712 'IGF_SL_CL_CHG_GID_REQD',
1713 'IGF_SL_CL_CHG_GSEQ_REQD',
1714 'IGF_SL_CL_CHG_LID_REQD',
1715 'IGF_SL_CL_CHG_LNUMB_REQD',
1716 'IGF_SL_CL_CHG_LOANT_REQD',
1717 'IGF_SL_CL_CHG_SCHID_REQD',
1718 'IGF_SL_CL_CHG_SCHID_REQD',
1719 'IGF_SL_CL_CHG_SSSN_REQD',
1720 'IGF_SL_CL_GRD_AMT_VAL',
1721 'IGS_GE_INVALID_VALUE',
1722 'IGF_AW_FUND_LOCK_ERR',
1723 'IGF_AW_LOAN_LMT_NOT_FND', -- mnade 1-Feb-2005 - 4089662
1724 'IGF_AW_LOAN_LMT_NOT_FND_WNG'
1725 ) THEN
1726 fnd_message.set_name(SUBSTR(l_name,1,3),l_name);
1727 igs_ge_msg_stack.add;
1728 app_exception.raise_exception ;
1729 ELSE
1730 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1731 fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_AWARD' || ' ' || SQLERRM);
1732 igs_ge_msg_stack.add;
1733 app_exception.raise_exception ;
1734 END IF;
1735 END update_award;
1736
1737
1738 PROCEDURE check_ld_cal_tps( p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE,
1739 p_found OUT NOCOPY VARCHAR2 ) IS
1740 ------------------------------------------------------------------
1741 --Created by :
1742 --Date created:
1743 --
1744 --Purpose:
1745 --
1746 --
1747 --Known limitations/enhancements and/or remarks:
1748 --
1749 --Change History:
1750 --Who When What
1751 --veramach 11-NOV-2003 FA 125 multiple distr methods
1752 -- removed fund_id as parameter and added adplans_id
1753 -- changed p_found datatype from boolean to varchar2
1754 -------------------------------------------------------------------
1755
1756 --Get all terms using linked to the distribution plan
1757 CURSOR cur_check_terms(
1758 p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1759 ) IS
1760 SELECT terms.adterms_id
1761 FROM igf_aw_dp_terms terms
1762 WHERE terms.adplans_id = p_adplans_id;
1763 l_check_terms cur_check_terms%ROWTYPE;
1764 --Get incomplete terms
1765 CURSOR cur_check_teach(
1766 p_adplans_id igf_aw_awd_dist_plans.adplans_id%TYPE
1767 ) IS
1768 SELECT terms.adterms_id
1769 FROM igf_aw_dp_terms terms
1770 WHERE terms.adplans_id = p_adplans_id
1771 AND NOT EXISTS (
1772 SELECT 'x'
1773 FROM igf_aw_dp_teach_prds tech
1774 WHERE tech.adterms_id = terms.adterms_id
1775 );
1776 l_check_teach cur_check_teach%ROWTYPE;
1777 BEGIN
1778 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1779 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.check_ld_cal_tps.debug','p_adplans_id:'||p_adplans_id);
1780 END IF;
1781 OPEN cur_check_terms(p_adplans_id);
1782 FETCH cur_check_terms INTO l_check_terms;
1783 IF cur_check_terms%FOUND THEN
1784 OPEN cur_check_teach(p_adplans_id);
1785 FETCH cur_check_teach INTO l_check_teach;
1786 IF cur_check_teach%NOTFOUND THEN
1787 p_found := 'TRUE';
1788 ELSE
1789 p_found := 'IGF_AW_DIST_TERMS_TEACH_FAIL';
1790 END IF;
1791 ELSE
1792 p_found := 'IGF_AW_DIST_PLAN_TERMS_FAIL';
1793 END IF;
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1797 fnd_message.set_token('NAME','IGF_AW_GEN.CHECK_LD_CAL_TPS' ||' ' || SQLERRM);
1798 igs_ge_msg_stack.add;
1799 app_exception.raise_exception;
1800
1801 END check_ld_cal_tps;
1802
1803 PROCEDURE check_number_format(str varchar2,ret out NOCOPY number) IS
1804
1805 invalid_number EXCEPTION;
1806 PRAGMA EXCEPTION_INIT(invalid_number,-01722);
1807 l_cur_check_number NUMBER ;
1808
1809 --Cursor that will convert the String into a Number
1810 CURSOR cur_check_number IS
1811 SELECT TO_NUMBER(LTRIM(RTRIM(str)))
1812 FROM DUAL;
1813
1814 BEGIN
1815
1816 ret:=0;
1817
1818 OPEN cur_check_number;
1819 FETCH cur_check_number INTO l_cur_check_number;
1820 CLOSE cur_check_number;
1821
1822 EXCEPTION
1823
1824 WHEN invalid_number THEN
1825 CLOSE cur_check_number;
1826 ret:=1;
1827
1828 WHEN OTHERS THEN
1829 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1830 fnd_message.set_token('NAME','IGF_AW_GEN.CHECK_NUMBER_FORMAT' ||' ' || SQLERRM);
1831 igs_ge_msg_stack.add;
1832 app_exception.raise_exception;
1833
1834 END check_number_format;
1835
1836 PROCEDURE depend_stat_2001
1837 ( p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
1838 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
1839 p_method_code IN VARCHAR2,
1840 p_category OUT NOCOPY NUMBER,
1841 p_dependency_status OUT NOCOPY VARCHAR2)
1842 AS
1843 /*
1844 || Created By : pkpatel
1845 || Created On : 11-DEC-2001
1846 || Purpose :Bug No - 2142666 EFC DLD
1847 || It finds the Dependency Status and eligibility of student for processing Simplified and Auto Zero EFC.
1848 || Known limitations, enhancements or remarks :
1849 || Change History :
1850 || Who When What
1851 || (reverse chronological order - newest change first)
1852 || CDCRUZ 14-FEB-03 Obsoleted by the FACR105 Bug# 2758804
1853 */
1854
1855
1856 BEGIN
1857
1858 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1859 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.depend_stat_2001.debug','p_base_id:'||p_base_id);
1860 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.depend_stat_2001.debug','p_isir_id:'||p_isir_id);
1861 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.depend_stat_2001.debug','p_method_code:'||p_method_code);
1862 END IF;
1863 p_category := 0 ;
1864 p_dependency_status := '' ;
1865
1866 EXCEPTION
1867 WHEN OTHERS THEN
1868 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1869 fnd_message.set_token('NAME','IGF_AW_GEN.DEPEND_STAT_2001' ||' ' || SQLERRM);
1870 igs_ge_msg_stack.add;
1871 app_exception.raise_exception;
1872
1873 END depend_stat_2001;
1874
1875 /*
1876 || Created By : mnade
1877 || Created On : 5/24/2005
1878 ||
1879 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
1880 || Searches for the award notification status for given person for given awarding period the terms for which
1881 || fall under the given awarding period. If all awards carry same notification status in that case carry the same with
1882 || latest date. In case there are multiple, return the least significant one , with latest date.
1883 ||
1884 || Known limitations, enhancements or remarks :
1885 || Change History :
1886 || Who When What
1887 || (reverse chronological order - newest change first)
1888 */
1889 PROCEDURE get_notification_status (
1890 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1891 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
1892 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
1893 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
1894 p_notification_status_code OUT NOCOPY igf_aw_award_all.notification_status_code%TYPE,
1895 p_notification_status_date OUT NOCOPY igf_aw_award_all.notification_status_date%TYPE
1896 ) AS
1897 -- mnade 5/24/2005 using the base query from IGFAW016.pld for the same C_PROCESS_STATUS
1898 CURSOR c_process_status (cp_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
1899 cp_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1900 cp_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
1901 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1902 IS
1903 SELECT TO_CHAR(MIN(status_order)) min_award_status, MAX(notification_status_date) notification_status_date
1904 FROM
1905 (SELECT
1906 awd.award_id,
1907 notification_status_code,
1908 notification_status_date,
1909 DECODE(AWD.notification_status_code,
1910 NULL, 99,
1911 'R', 1,
1912 'D', 2,
1913 'S', 3,
1914 'F', 3
1915 ) STATUS_ORDER
1916 FROM
1917 IGF_AW_AWARD_all AWD,
1918 IGF_AW_FUND_MAST FMAST
1919 WHERE
1920 FMAST.CI_CAL_TYPE = cp_ci_cal_type AND
1921 FMAST.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
1922 AWD.FUND_ID = FMAST.FUND_ID AND
1923 AWD.BASE_ID = cp_base_id AND
1924 NOT EXISTS
1925 (SELECT DISB.LD_CAL_TYPE, DISB.LD_SEQUENCE_NUMBER
1926 FROM IGF_AW_AWD_DISB DISB
1927 WHERE
1928 DISB.AWARD_ID = AWD.AWARD_ID
1929 MINUS
1930 SELECT LD_CAL_TYPE, LD_SEQUENCE_NUMBER
1931 FROM IGF_AW_AWD_PRD_TERM APT
1932 WHERE APT.CI_CAL_TYPE = cp_ci_cal_type AND
1933 APT.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
1934 APT.award_prd_cd = NVL(cp_award_prd_code, award_prd_cd))) temp;
1935 l_process_status c_process_status%ROWTYPE;
1936 CURSOR c_rev_mapping (cp_mapped_value NUMBER)
1937 IS
1938 SELECT val_data FROM
1939 (
1940 SELECT 1 key_data, 'R' val_data from dual union all
1941 SELECT 2 key_data, 'D' val_data from dual union all
1942 SELECT 3 key_data, 'S' val_data from dual union all
1943 SELECT 4 key_data, 'F' val_data from dual
1944 ) MAPPING
1945 WHERE key_data = cp_mapped_value;
1946 BEGIN
1947 OPEN c_process_status (
1948 cp_ci_cal_type => p_cal_type,
1949 cp_ci_sequence_number => p_seq_num,
1950 cp_award_prd_code => p_awarding_period,
1951 cp_base_id => p_base_id);
1952 FETCH c_process_status INTO l_process_status;
1953 IF c_process_status%FOUND THEN -- award rank found?
1954 OPEN c_rev_mapping (cp_mapped_value => l_process_status.min_award_status);
1955 FETCH c_rev_mapping INTO p_notification_status_code;
1956 p_notification_status_date := l_process_status.notification_status_date;
1957 CLOSE c_rev_mapping;
1958 END IF; -- END award rank found?
1959 CLOSE c_process_status;
1960 EXCEPTION
1961 WHEN OTHERS THEN
1962 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1963 fnd_message.set_token('NAME','IGF_AW_GEN.GET_NOTIFICATION_STATUS ' || SQLERRM);
1964 igs_ge_msg_stack.add;
1965 app_exception.raise_exception;
1966 END get_notification_status;
1967
1968 /*
1969 || Created By : mnade
1970 || Created On : 5/24/2005
1971 ||
1972 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
1973 || Searches for awards for given person for given awarding period the terms for which
1974 || fall under the given awarding period. All awards will be updated to carry supplied
1975 || Notification Status and Notification Status Date.
1976 ||
1977 || Known limitations, enhancements or remarks :
1978 || Change History :
1979 || Who When What
1980 || (reverse chronological order - newest change first)
1981 */
1982 PROCEDURE update_notification_status (
1983 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1984 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
1985 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
1986 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
1987 p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
1988 p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
1989 p_called_from IN VARCHAR2
1990 ) AS
1991 -- mnade 5/24/2005 using the base query from IGFAW016.pld for the same C_PROCESS_STATUS
1992 CURSOR c_awards (cp_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
1993 cp_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE,
1994 cp_award_prd_code igf_aw_awd_prd_term.award_prd_cd%TYPE,
1995 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1996 IS
1997 SELECT
1998 awd.award_id
1999 FROM
2000 IGF_AW_AWARD_all AWD,
2001 IGF_AW_FUND_MAST FMAST
2002 WHERE
2003 FMAST.CI_CAL_TYPE = cp_ci_cal_type AND
2004 FMAST.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
2005 AWD.FUND_ID = FMAST.FUND_ID AND
2006 AWD.BASE_ID = cp_base_id AND
2007 NOT EXISTS
2008 (SELECT DISB.LD_CAL_TYPE, DISB.LD_SEQUENCE_NUMBER
2009 FROM IGF_AW_AWD_DISB DISB
2010 WHERE
2011 DISB.AWARD_ID = AWD.AWARD_ID
2012 MINUS
2013 SELECT LD_CAL_TYPE, LD_SEQUENCE_NUMBER
2014 FROM IGF_AW_AWD_PRD_TERM APT
2015 WHERE APT.CI_CAL_TYPE = cp_ci_cal_type AND
2016 APT.CI_SEQUENCE_NUMBER = cp_ci_sequence_number AND
2017 APT.award_prd_cd = NVL(cp_award_prd_code, award_prd_cd));
2018 award_rec c_awards%ROWTYPE;
2019 BEGIN
2020 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2021 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_cal_type - ' || p_cal_type);
2022 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_seq_num - ' || p_seq_num);
2023 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_awarding_period - ' || p_awarding_period);
2024 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_base_id - ' || p_base_id);
2025 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_code - ' || p_notification_status_code);
2026 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_date - ' || p_notification_status_date);
2027 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_awarding_period - ' || p_awarding_period);
2028 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_called_from - ' || p_called_from);
2029 END IF;
2030 OPEN c_awards ( cp_ci_cal_type => p_cal_type,
2031 cp_ci_sequence_number => p_seq_num,
2032 cp_award_prd_code => p_awarding_period,
2033 cp_base_id => p_base_id);
2034 LOOP -- Award Noification Status Update
2035 FETCH c_awards INTO award_rec;
2036 EXIT WHEN c_awards%NOTFOUND;
2037 update_awd_notification_status (
2038 p_award_id => award_rec.award_id,
2039 p_notification_status_code => p_notification_status_code,
2040 p_notification_status_date => p_notification_status_date,
2041 p_called_from => p_called_from
2042 );
2043 END LOOP; -- END Award Noification Status Update
2044 EXCEPTION
2045 WHEN OTHERS THEN
2046 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2047 fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_NOTIFICATION_STATUS ' || SQLERRM);
2048 igs_ge_msg_stack.add;
2049 app_exception.raise_exception;
2050 END update_notification_status;
2051
2052 /*
2053 || Created By : mnade
2054 || Created On : 5/24/2005
2055 ||
2056 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
2057 || Updates the Notification Status and Notification Status Date for given award.
2058 ||
2059 || Known limitations, enhancements or remarks :
2060 || Change History :
2061 || Who When What
2062 || (reverse chronological order - newest change first)
2063 */
2064 PROCEDURE update_awd_notification_status (
2065 p_award_id IN igf_aw_award_all.award_id%TYPE,
2066 p_notification_status_code IN igf_aw_award_all.notification_status_code%TYPE,
2067 p_notification_status_date IN igf_aw_award_all.notification_status_date%TYPE,
2068 p_called_from IN VARCHAR2
2069 ) AS
2070 -- mnade 5/24/2005 using the base query from IGFAW016.pld for the same C_PROCESS_STATUS
2071 CURSOR c_award (cp_award_id igf_aw_award_all.award_id%TYPE)
2072 IS
2073 SELECT
2074 awd.rowid row_id, awd.*
2075 FROM
2076 IGF_AW_AWARD_all awd
2077 WHERE
2078 awd.award_id = cp_award_id;
2079 -- award_rec c_award%ROWTYPE;
2080 BEGIN
2081
2082 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2083 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_award_id - ' || p_award_id);
2084 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_code - ' || p_notification_status_code);
2085 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_notification_status_date - ' || p_notification_status_date);
2086 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_aw_gen.update_notification_status.debug', 'p_called_from - ' || p_called_from);
2087 END IF;
2088
2089 -- OPEN c_award (cp_award_id => p_award_id);
2090 FOR award_rec IN c_award (cp_award_id => p_award_id) LOOP
2091 -- FETCH c_award INTO award_rec;
2092 igf_aw_award_pkg.update_row ( x_mode => 'R',
2093 x_rowid => award_rec.row_id,
2094 x_award_id => award_rec.award_id,
2095 x_fund_id => award_rec.fund_id,
2096 x_base_id => award_rec.base_id,
2097 x_offered_amt => award_rec.offered_amt,
2098 x_accepted_amt => award_rec.accepted_amt,
2099 x_paid_amt => award_rec.paid_amt,
2100 x_packaging_type => award_rec.packaging_type,
2101 x_batch_id => award_rec.batch_id,
2102 x_manual_update => award_rec.manual_update,
2103 x_rules_override => award_rec.rules_override,
2104 x_award_date => award_rec.award_date,
2105 x_award_status => award_rec.award_status,
2106 x_attribute_category => award_rec.attribute_category,
2107 x_attribute1 => award_rec.attribute1,
2108 x_attribute2 => award_rec.attribute2,
2109 x_attribute3 => award_rec.attribute3,
2110 x_attribute4 => award_rec.attribute4,
2111 x_attribute5 => award_rec.attribute5,
2112 x_attribute6 => award_rec.attribute6,
2113 x_attribute7 => award_rec.attribute7,
2114 x_attribute8 => award_rec.attribute8,
2115 x_attribute9 => award_rec.attribute9,
2116 x_attribute10 => award_rec.attribute10,
2117 x_attribute11 => award_rec.attribute11,
2118 x_attribute12 => award_rec.attribute12,
2119 x_attribute13 => award_rec.attribute13,
2120 x_attribute14 => award_rec.attribute14,
2121 x_attribute15 => award_rec.attribute15,
2122 x_attribute16 => award_rec.attribute16,
2123 x_attribute17 => award_rec.attribute17,
2124 x_attribute18 => award_rec.attribute18,
2125 x_attribute19 => award_rec.attribute19,
2126 x_attribute20 => award_rec.attribute20,
2127 x_rvsn_id => award_rec.rvsn_id,
2128 x_alt_pell_schedule => award_rec.alt_pell_schedule,
2129 x_award_number_txt => award_rec.award_number_txt,
2130 x_legacy_record_flag => NULL,
2131 x_adplans_id => award_rec.adplans_id,
2132 x_lock_award_flag => award_rec.lock_award_flag,
2133 x_app_trans_num_txt => award_rec.app_trans_num_txt,
2134 x_awd_proc_status_code => award_rec.awd_proc_status_code,
2135 x_notification_status_code => p_notification_status_code,
2136 x_notification_status_date => p_notification_status_date,
2137 x_called_from => p_called_from,
2138 x_publish_in_ss_flag => award_rec.publish_in_ss_flag
2139 );
2140 -- CLOSE c_award;
2141 END LOOP;
2142 EXCEPTION
2143 WHEN OTHERS THEN
2144 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2145 fnd_message.set_token('NAME','IGF_AW_GEN.UPDATE_AWD_NOTIFICATION_STATUS ' || SQLERRM);
2146 igs_ge_msg_stack.add;
2147 app_exception.raise_exception;
2148 END update_awd_notification_status;
2149
2150
2151 /*
2152 || Created By : mnade
2153 || Created On : 5/24/2005
2154 ||
2155 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
2156 || Gets the concurrent program name for the cp id being passed.
2157 ||
2158 || Known limitations, enhancements or remarks :
2159 || Change History :
2160 || Who When What
2161 || (reverse chronological order - newest change first)
2162 */
2163 FUNCTION get_concurrent_prog_name (p_program_id IN fnd_concurrent_programs_tl.concurrent_program_id%TYPE) RETURN VARCHAR2 IS
2164 CURSOR c_cp_name IS
2165 SELECT
2166 user_concurrent_program_name
2167 FROM
2168 FND_CONCURRENT_PROGRAMS_TL
2169 WHERE
2170 APPLICATION_ID = 8406 AND
2171 CONCURRENT_PROGRAM_ID = p_program_id AND
2172 LANGUAGE = userenv('LANG');
2173 l_user_concurrent_program_name fnd_concurrent_programs_tl.user_concurrent_program_name%TYPE;
2174 BEGIN
2175 OPEN c_cp_name;
2176 FETCH c_cp_name INTO l_user_concurrent_program_name;
2177 CLOSE c_cp_name;
2178 RETURN l_user_concurrent_program_name;
2179 EXCEPTION
2180 WHEN OTHERS THEN
2181 RETURN NULL;
2182 END get_concurrent_prog_name;
2183
2184
2185 /*
2186 || Created By : mnade
2187 || Created On : 6/6/2005
2188 ||
2189 || Purpose : Bug No - 4382371 FA 157 - Packaging Phase II
2190 || Checks if there is any award locked under given awarding period for the student
2191 || and returns true of there is any award locked for the student.
2192 ||
2193 || Known limitations, enhancements or remarks :
2194 || Change History :
2195 || Who When What
2196 || (reverse chronological order - newest change first)
2197 */
2198 FUNCTION is_fund_locked_for_awd_period (
2199 p_base_id IN igf_ap_fa_base_rec_all.base_id%type,
2200 p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2201 p_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
2202 p_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
2203 p_fund_id IN igf_aw_award_all.fund_id%TYPE
2204 ) RETURN BOOLEAN AS
2205 CURSOR locked_award_count_cur (
2206 cp_base_id IN igf_ap_fa_base_rec_all.base_id%type,
2207 cp_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2208 cp_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
2209 cp_awarding_period IN igf_aw_award_prd.award_prd_cd%TYPE,
2210 cp_fund_id IN igf_aw_award_all.fund_id%TYPE
2211 )
2212 IS
2213 SELECT
2214 COUNT(awd.award_id) lock_count
2215 FROM
2216 igf_aw_award_all awd,
2217 igf_aw_awd_disb_all disb,
2218 igf_aw_awd_prd_term apt
2219 WHERE
2220 disb.award_id = awd.award_id
2221 AND disb.ld_cal_type = apt.ld_cal_type
2222 AND disb.ld_sequence_number = apt.ld_sequence_number
2223 AND NVL(awd.lock_award_flag, 'N') = 'Y'
2224 AND awd.fund_id = cp_fund_id
2225 AND apt.ci_cal_type = cp_cal_type
2226 AND apt.ci_sequence_number = cp_seq_num
2227 AND apt.award_prd_cd = NVL(cp_awarding_period, award_prd_cd)
2228 AND awd.base_id = cp_base_id;
2229
2230 l_locked_award_count NUMBER := 0;
2231 l_flag BOOLEAN := FALSE;
2232
2233 BEGIN
2234 OPEN locked_award_count_cur (
2235 cp_base_id => p_base_id,
2236 cp_cal_type => p_cal_type,
2237 cp_seq_num => p_seq_num,
2238 cp_awarding_period => p_awarding_period,
2239 cp_fund_id => p_fund_id
2240 );
2241 FETCH locked_award_count_cur INTO l_locked_award_count;
2242 CLOSE locked_award_count_cur;
2243 IF l_locked_award_count > 0 THEN
2244 l_flag := TRUE;
2245 END IF;
2246 RETURN l_flag;
2247 EXCEPTION
2248 WHEN OTHERS THEN
2249 RETURN NULL;
2250 END;
2251
2252
2253
2254 END igf_aw_gen;