[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_GEN
Source
1 PACKAGE BODY igf_gr_gen AS
2 /* $Header: IGFGR08B.pls 120.3 2006/04/06 06:11:10 veramach ship $ */
3
4 -----------------------------------------------------------------------------------
5 -- Who When What
6 ------------------------------------------------------------------------
7 -- cdcruz 28-Oct-2004 FA152 Auto Re-pkg Build
8 -- Modified the call to igf_aw_packng_subfns.get_fed_efc()
9 -- as part of dependency.
10 ------------------------------------------------------------------------
11 --ayedubat 13-OCT-04 FA 149 COD-XML Standards build bug # 3416863
12 -- Changed the TBH calls of the packages: igf_gr_rfms_pkg
13 -- veramach 29-Jan-2004 Bug 3408092 Added 2004-2005 in g_ver_num checks
14 -----------------------------------------------------------------------------------
15 -- veramach 10-Dec-2003 FA 131 COD Updates
16 -- Removed function get_rep_pell_id
17 -----------------------------------------------------------------------------------
18 -- ugummall 03-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
19 -- 1. Added two extra parameters p_ci_cal_type and p_ci_sequence_number
20 -- to get_pell_header
21 -- 2. Removed cursor c_ope_id and added cur_get_ope_id to get ope id
22 -- from igf_gr_report_pell table rather igf_ap_fa_setup table.
23 -----------------------------------------------------------------------------------
24 -- sjadhav 26-Jun-2003 Bug 2938258
25 -- Pell and Disb records batch id (rfmb_id) set to NULL
26 -- when record moved to ready to send status
27 -----------------------------------------------------------------------------------
28 --bkkumar 24-jun-2003 Bug #2974248 Added the code for proceeding in case of
29 -- warning codes.
30 -----------------------------------------------------------------------------------
31 --rasahoo 13-May-2003 Bug #2938258 Added code for Resetting Origination Status
32 -- to "Ready to Send" for the rejected Pell Disbursement Records.
33 ------------------------------------------------------------------------------------
34 -- gmuralid 10-Apr-2003 Bug 2744419
35 -- Modified function get_calendar_desc to
36 -- return alternate code if description
37 -- is null.
38 -----------------------------------------------------------------------------------
39 -- gmuralid 10-Apr-2003 Bug 2744419
40 -- Added Function get_calendar_desc to get
41 -- the calendar description.
42 -----------------------------------------------------------------------------------
43 -- sjadhav 01-Apr-2003 Bug 2875503
44 -- Changed in parameter for get_ssn_digits
45 -----------------------------------------------------------------------------------
46 -- gmuralid 27-03-2003 BUG 2863929 - OPE ID poulated in pell header record
47 -----------------------------------------------------------------------------------
48 -- sjadhav 03-Mar-2003 Bug 2781382
49 -- Return NULL if efc is null in get_pell_efc
50 -----------------------------------------------------------------------------------
51 -- sjadhav 05-Feb-2003 FA116 Build - Bug 2758812 - 2/4/03
52 -- Added update_current_ssn,update_pell_status,
53 -- match_file_version,get_min_disb_number
54 -----------------------------------------------------------------------------------
55 -- sjadhav Nov,18,2002. Bug 2590991
56 -- Routine to fetch base id
57 -----------------------------------------------------------------------------------
58 -- sjadhav Oct.25.2002 Bug 2613546,2606001
59 -- get_tufees_code,get_def_awd_year,ovrd_coa_exist,
60 -- delete_coa,update_item_dist,insert_coa_items,
61 -- insert_coa_terms,get_pell_code,insert_stu_coa_terms,
62 -- delete_stu_coa_terms,delete_stu_coa_items,
63 -- update_stu_coa_items routines added
64 -----------------------------------------------------------------------------------
65 -- sjadhav Oct.10.2002 Bug 2383690
66 -- 1. Added send_orig_disb
67 -- 2. Added get_min_pell_disb
68 -- 3. Added get_min_awd_disb
69 --
70 -- nsidana 10/31/2003 Multiple FA offices.
71 -- Added 3 new functions to derive the reporting pell ID
72 -- for a student.
73 -----------------------------------------------------------------------------------
74 --
75 -- sjadhav
76 -- This is a generic Utility Package aimed at centralization of
77 -- common functions/procedures
78 --
79 -- This package contains
80 -- 1. get_rep_pell_id
81 -- 2. get_pell_header
82 -- 3. get_pell_trailer
83 -- 4. process_pell_ack
84 -- routines which are very specific to Pell Subsytem
85 --
86 -- Other routines are general
87 --
88 -----------------------------------------------------------------------------------
89
90
91
92
93 g_batch_dt VARCHAR2(20);
94
95 FUNCTION chk_orig_isir_exists( p_base_id IN igf_ap_fa_base_rec.base_id%TYPE,
96 p_transaction_num IN igf_ap_ISIR_matched.transaction_num%TYPE)
97 RETURN BOOLEAN
98 AS
99 --------------------------------------------------------------------------------------------------------
100 -- Created By : rasahoo
101 -- Date Created By : Sep 26, 2003
102 -- Purpose : check if an RFMS origination record exists for the context Base ID
103 -- that does not use Passed ISIR transaction number as the Payment ISIR
104 --Change History:
105 --Who When What
106 --
107 ----------------------------------------------------------------------------------------------------------
108 -- retrieves records for which RFMS Originations exists in status Accepted or Sent, with a different Transaction Number
109 CURSOR chk_ex_orig_rec (cp_base_id igf_ap_fa_base_rec.base_id%TYPE,
110 cp_transaction_num igf_ap_ISIR_matched.transaction_num%TYPE)
111 IS
112 SELECT 'X'
113 FROM igf_gr_rfms rfms
114 WHERE rfms.base_id = cp_base_id
115 AND rfms.orig_action_code in ('A','S')
116 AND rfms.transaction_num <> cp_transaction_num ;
117
118 l_chk_ex_orig_rec chk_ex_orig_rec%ROWTYPE;
119
120 BEGIN
121
122 OPEN chk_ex_orig_rec(p_base_id,p_transaction_num);
123 FETCH chk_ex_orig_rec INTO l_chk_ex_orig_rec;
124 IF chk_ex_orig_rec%FOUND THEN
125 CLOSE chk_ex_orig_rec;
126 RETURN TRUE;
127 ELSE
128 CLOSE chk_ex_orig_rec;
129 RETURN FALSE;
130 END IF;
131 END chk_orig_isir_exists;
132
133 FUNCTION get_cycle_year (p_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
134 p_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE)
135 RETURN VARCHAR2
136 IS
137 --------------------------------------------------------------------------------------------
138 --
139 --Change History:
140 --Bug No:-2460904 Desc :- Pell Formatting Issues
141 --Who When What
142 --mesriniv 22-jul-2002 Cycle Year should be 4 chars starting from 6th char in File Version.
143 -- Eg If File Version is 2002-2003 then cycle year is 2003
144 -- Cursor used to pick the year part of end date from Cal Instance for award year
145 -- may not always return the ending year.
146 -- Removed cursor which picks the year part of end date from IGS_CA_INST for the calendar instance.
147
148 l_ver_num VARCHAR2(30); -- Flat File Version Number
149 l_cycle_year VARCHAR2(4);
150
151 BEGIN
152
153 -- Get the Flat File Version and then Proceed
154 --
155 l_ver_num := igf_aw_gen.get_ver_num(p_ci_cal_type,p_ci_sequence_number,'P');
156 IF l_ver_num IS NOT NULL THEN
157 l_cycle_year:=SUBSTR(l_ver_num,6,4);
158 END IF;
159
160
161 RETURN l_cycle_year;
162
163 END get_cycle_year;
164
165
166 FUNCTION disb_has_adj ( p_award_id igf_aw_award_all.award_id%TYPE,
167 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
168 RETURN BOOLEAN
169 IS
170
171 --------------------------------------------------------------------------------------------
172 --
173 --------------------------------------------------------------------------------------------
174
175 CURSOR cur_get_adj ( p_award_id igf_aw_award_all.award_id%TYPE,
176 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
177 IS
178 SELECT
179 COUNT (disb_num)
180 FROM
181 igf_db_awd_disb_dtl
182 WHERE
183 p_award_id = award_id AND
184 p_disb_num = disb_num;
185
186 ln_rec_count NUMBER;
187
188 BEGIN
189
190 OPEN cur_get_adj(p_award_id,p_disb_num);
191 FETCH cur_get_adj INTO ln_rec_count;
192 CLOSE cur_get_adj;
193
194 IF ln_rec_count > 0 THEN
195 RETURN TRUE;
196 ELSE
197 RETURN FALSE;
198 END IF;
199
200
201 END disb_has_adj;
202
203
204 FUNCTION get_alt_code ( p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
205 p_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
206 RETURN VARCHAR2
207 IS
208 --------------------------------------------------------------------------------------------
209 --
210 -- Created By : sjadhav
211 -- Date Created By : Jan 07,2002
212 -- Purpose : Returns alternate code of calendar
213 --
214 --------------------------------------------------------------------------------------------
215
216 CURSOR cur_alt_code ( p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
217 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
218
219 IS
220 SELECT
221 alternate_code
222 FROM
223 igs_ca_inst
224 WHERE
225 cal_type = p_ci_cal_type AND
226 sequence_number = p_ci_sequence_number;
227
228 alt_code_rec cur_alt_code%ROWTYPE;
229
230 BEGIN
231
232 OPEN cur_alt_code(p_ci_cal_type,p_ci_sequence_number);
233 FETCH cur_alt_code INTO alt_code_rec;
234
235 IF cur_alt_code%NOTFOUND THEN
236 CLOSE cur_alt_code;
237 RETURN NULL;
238 ELSE
239 CLOSE cur_alt_code;
240 RETURN alt_code_rec.alternate_code;
241 END IF;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
246 fnd_message.set_token('NAME','IGF_GR_GEN.GET_ALT_CODE'|| ' ' || SQLERRM);
247 igs_ge_msg_stack.add;
248 app_exception.raise_exception;
249
250
251 END get_alt_code;
252
253 FUNCTION get_calendar_desc ( p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
254 p_ci_sequence_number IN igs_ca_inst_all.sequence_number%TYPE)
255 RETURN VARCHAR2
256 IS
257 --------------------------------------------------------------------------------------------
258 --
259 -- Created By : gmuralid
260 -- Date Created By : Apr 10, 2003
261 -- Purpose : Returns calendar description.
262 --
263 --------------------------------------------------------------------------------------------
264
265 CURSOR cur_cal_desc ( p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
266 p_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE)
267
268 IS
269 SELECT
270 description,
271 alternate_code
272 FROM
273 igs_ca_inst
274 WHERE
275 cal_type = p_ci_cal_type AND
276 sequence_number = p_ci_sequence_number;
277
278 cal_rec cur_cal_desc%ROWTYPE;
279
280 BEGIN
281
282 OPEN cur_cal_desc(p_ci_cal_type,p_ci_sequence_number);
283 FETCH cur_cal_desc INTO cal_rec;
284
285 IF cur_cal_desc%NOTFOUND THEN
286 CLOSE cur_cal_desc;
287 RETURN NULL;
288 ELSE
289 CLOSE cur_cal_desc;
290 RETURN NVL(cal_rec.description,cal_rec.alternate_code);
291 END IF;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
296 fnd_message.set_token('NAME','IGF_GR_GEN.GET_CALENDAR_DESC'|| ' ' || SQLERRM);
297 igs_ge_msg_stack.add;
298 app_exception.raise_exception;
299
300
301 END get_calendar_desc;
302
303 FUNCTION get_per_num ( p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE)
304 RETURN VARCHAR2
305 IS
306 --------------------------------------------------------------------------------------------
307 --
308 -- Created By : sjadhav
309 -- Date Created By : Jan 07,2002
310 -- Purpose : Returns person number for the Base id passed
311 --
312 --------------------------------------------------------------------------------------------
313
314 CURSOR cur_fa_pers ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
315 IS
316 SELECT person_number
317 FROM igs_pe_person_base_v
318 WHERE person_id =
319 (
320 SELECT person_id
321 FROM
322 igf_ap_fa_base_rec
323 WHERE
324 base_id = p_base_id
325 );
326
327 fa_pers_rec cur_fa_pers%ROWTYPE;
328
329 BEGIN
330
331 OPEN cur_fa_pers(p_base_id);
332 FETCH cur_fa_pers INTO fa_pers_rec;
333
334 IF cur_fa_pers%NOTFOUND THEN
335 CLOSE cur_fa_pers;
336 RETURN NULL;
337 ELSE
338 CLOSE cur_fa_pers;
339 RETURN fa_pers_rec.person_number;
340 END IF;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
345 fnd_message.set_token('NAME','IGF_GR_GEN.GET_PER_NUM'|| ' ' || SQLERRM);
346 igs_ge_msg_stack.add;
347 app_exception.raise_exception;
348
349 END get_per_num;
350
351
352
353 FUNCTION get_per_num ( p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
354 p_person_number OUT NOCOPY igf_ap_person_v.person_number%TYPE )
355 RETURN BOOLEAN
356 IS
357 --------------------------------------------------------------------------------------------
358 --
359 -- Created By : sjadhav
360 -- Date Created By : Jan 07,2002
361 -- Purpose : Returns person number for the person id passed in
362 -- financial aid
363 --
364 --------------------------------------------------------------------------------------------
365
366 CURSOR cur_fa_pers ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
367 IS
368 SELECT person_number
369 FROM igf_ap_person_v
370 WHERE
371 person_id = p_person_id;
372
373 fa_pers_rec cur_fa_pers%ROWTYPE;
374
375 BEGIN
376
377 OPEN cur_fa_pers(p_person_id);
378 FETCH cur_fa_pers INTO fa_pers_rec;
379
380 IF cur_fa_pers%NOTFOUND THEN
381 CLOSE cur_fa_pers;
382 RETURN FALSE;
383 ELSE
384 CLOSE cur_fa_pers;
385 p_person_number := fa_pers_rec.person_number;
386 RETURN TRUE;
387 END IF;
388
389 EXCEPTION
390 WHEN OTHERS THEN
391 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
392 fnd_message.set_token('NAME','IGF_GR_GEN.GET_PER_NUM'|| ' ' || SQLERRM);
393 igs_ge_msg_stack.add;
394 app_exception.raise_exception;
395
396 END get_per_num;
397
398
399 FUNCTION get_person_id ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
400 RETURN VARCHAR2
401 IS
402 --------------------------------------------------------------------------------------------
403 --
404 -- Created By : sjadhav
405 -- Date Created By : Jan 07,2002
406 -- Purpose : Returns Person ID for the Base id passed
407 --
408 --------------------------------------------------------------------------------------------
409
410 CURSOR cur_fa_pers ( p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
411 IS
412 SELECT person_id
413 FROM igf_ap_fa_base_rec
414 WHERE
415 base_id = p_base_id;
416
417 fa_pers_rec cur_fa_pers%ROWTYPE;
418
419 BEGIN
420
421 OPEN cur_fa_pers(p_base_id);
422 FETCH cur_fa_pers INTO fa_pers_rec;
423
424 IF cur_fa_pers%NOTFOUND THEN
425 CLOSE cur_fa_pers;
426 RETURN NULL;
427 ELSE
428 CLOSE cur_fa_pers;
429 RETURN fa_pers_rec.person_id;
430 END IF;
431
432 EXCEPTION
433 WHEN OTHERS THEN
434 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
435 fnd_message.set_token('NAME','IGF_GR_GEN.GET_PER_ID'|| ' ' || SQLERRM);
436 igs_ge_msg_stack.add;
437 app_exception.raise_exception;
438
439 END get_person_id;
440
441
442 FUNCTION get_per_num_oss ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
443 RETURN VARCHAR2
444 IS
445 --------------------------------------------------------------------------------------------
446 --
447 -- Created By : sjadhav
448 -- Date Created By : Jan 07,2002
449 -- Purpose : Returns person number for the Base id passed
450 --
451 --------------------------------------------------------------------------------------------
452
453 CURSOR cur_fa_pers ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
454 IS
455 SELECT person_number
456 FROM igs_pe_person_base_v
457 WHERE
458 person_id = p_person_id;
459
460 fa_pers_rec cur_fa_pers%ROWTYPE;
461
462 BEGIN
463
464 OPEN cur_fa_pers(p_person_id);
465 FETCH cur_fa_pers INTO fa_pers_rec;
466
467 IF cur_fa_pers%NOTFOUND THEN
468 CLOSE cur_fa_pers;
469 RETURN NULL;
470 ELSE
471 CLOSE cur_fa_pers;
472 RETURN fa_pers_rec.person_number;
473 END IF;
474
475 EXCEPTION
476 WHEN OTHERS THEN
477 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
478 fnd_message.set_token('NAME','IGF_GR_GEN.GET_PER_NUM_OSS'|| ' ' || SQLERRM);
479 igs_ge_msg_stack.add;
480 app_exception.raise_exception;
481
482 END get_per_num_oss;
483
484
485 PROCEDURE insert_sys_holds ( p_award_id igf_aw_award_all.award_id%TYPE,
486 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
487 p_hold igf_db_disb_holds_all.hold%TYPE)
488 IS
489 --------------------------------------------------------------------------------------------
490 --
491 -- sjadhav, 15Feb2002
492 --
493 -- This procedure puts hold on Planned Disbursements
494 -- This process can be modified to insert holds on Actual Disbursements as well
495 --
496 --------------------------------------------------------------------------------------------
497
498 CURSOR cur_get_adisb ( p_award_id igf_aw_award_all.award_id%TYPE,
499 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
500 IS
501 SELECT
502 awd.award_id,awd.disb_num
503 FROM
504 igf_aw_awd_disb awd
505 WHERE
506 awd.award_id = p_award_id AND
507 awd.disb_num = NVL(p_disb_num,awd.disb_num) AND
508 awd.trans_type = 'P';
509
510 awd_rec cur_get_adisb%ROWTYPE;
511 holds_rec igf_db_disb_holds_all%ROWTYPE;
512
513 lv_rowid ROWID;
514
515 CURSOR cur_chk_holds ( p_award_id igf_aw_awd_disb_all.award_id%TYPE,
516 p_disb_num igf_aw_awd_disb_all.disb_num%TYPE,
517 p_hold igf_db_disb_holds_all.hold%TYPE)
518 IS
519 SELECT COUNT(hold_id) cnt
520 FROM
521 igf_db_disb_holds
522 WHERE
523 award_id = p_award_id AND
524 disb_num = p_disb_num AND
525 NVL(release_flag,'N') = 'N' AND
526 hold = p_hold;
527
528 chk_holds_rec cur_chk_holds%ROWTYPE;
529
530 l_app VARCHAR2(50);
531 l_name VARCHAR2(30);
532
533 BEGIN
534
535 lv_rowid := NULL;
536
537 FOR awd_rec IN cur_get_adisb (p_award_id,p_disb_num)
538
539 LOOP
540 OPEN cur_chk_holds(awd_rec.award_id,awd_rec.disb_num,p_hold);
541 FETCH cur_chk_holds INTO chk_holds_rec;
542 CLOSE cur_chk_holds;
543
544 IF NVL(chk_holds_rec.cnt,0) = 0 THEN
545 igf_db_disb_holds_pkg.insert_row(x_rowid => lv_rowid,
546 x_hold_id => holds_rec.hold_id,
547 x_award_id => awd_rec.award_id,
548 x_disb_num => awd_rec.disb_num,
549 x_hold => p_hold,
550 x_hold_date => TRUNC(SYSDATE),
551 x_hold_type => 'SYSTEM',
552 x_release_date => NULL,
553 x_release_flag => 'N',
554 x_release_reason => NULL,
555 x_mode => 'R');
556
557 END IF;
558
559 END LOOP;
560
561 EXCEPTION
562
563 WHEN OTHERS THEN
564 --
565 -- This will ensure exception raised from the isnert hold tbh
566 -- are is not thrown
567 --
568 fnd_message.parse_encoded(fnd_message.get_encoded, l_app, l_name);
569 IF l_name = 'IGF_DB_HOLD_EXISTS' THEN
570 NULL;
571 ELSE
572 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
573 fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_SYS_HOLDS'|| ' ' || SQLERRM);
574 igs_ge_msg_stack.add;
575 app_exception.raise_exception;
576 END IF;
577
578 END insert_sys_holds;
579
580
581 ----------------------------------------------------------------------------------------
582 -- Pell Routines
583 ----------------------------------------------------------------------------------------
584
585 FUNCTION get_pell_efc ( p_base_id IN igf_aw_award_all.base_id%TYPE)
586 RETURN NUMBER
587 IS
588
589 ln_pell_efc NUMBER;
590 ln_efc NUMBER;
591
592 BEGIN
593
594
595 igf_aw_packng_subfns.get_fed_efc(
596 l_base_id => p_base_id,
597 l_awd_prd_code => NULL,
598 l_efc_f => ln_efc,
599 l_pell_efc => ln_pell_efc,
600 l_efc_ay => ln_efc
601 );
602
603
604 RETURN ln_pell_efc;
605
606 END get_pell_efc;
607
608 FUNCTION get_pell_header (p_ver_num IN VARCHAR2,
609 p_cycle_year IN VARCHAR2,
610 p_rep_pell_id IN igf_gr_pell_setup_all.rep_pell_id%TYPE,
611 p_batch_type IN VARCHAR2,
612 p_rfmb_id OUT NOCOPY igf_gr_rfms_batch.rfmb_id%TYPE,
613 p_batch_id OUT NOCOPY VARCHAR2,
614 p_ci_cal_type IN VARCHAR2,
615 p_ci_sequence_number IN NUMBER)
616 RETURN VARCHAR2
617 IS
618 --------------------------------------------------------------------------------------------
619 --
620 --------------------------------------------------------------------------------------------
621
622 l_header VARCHAR2(1000);
623 l_rowid VARCHAR2(30);
624 ln_data_rec_len NUMBER;
625
626 -- Modified this cursor c_ope_id to get ope id from igf_gr_report_pell
627 -- table instead of igf_ap_fa_setup w.r.t FA 126
628 CURSOR cur_get_ope_id( cp_ci_cal_type igf_gr_report_pell.ci_cal_type%TYPE,
629 cp_ci_seq_num igf_gr_report_pell.ci_sequence_number%TYPE,
630 cp_rep_pell_id igf_gr_report_pell.reporting_pell_cd%TYPE
631 )
632 IS
633 SELECT ope_cd
634 FROM igf_gr_report_pell rpell
635 WHERE rpell.ci_cal_type = cp_ci_cal_type
636 AND rpell.ci_sequence_number = cp_ci_seq_num
637 AND rpell.reporting_pell_cd = cp_rep_pell_id;
638
639 get_ope_id_rec cur_get_ope_id%ROWTYPE;
640
641 BEGIN
642
643
644 IF p_ver_num IN ('2002-2003','2003-2004','2004-2005','2005-2006') THEN
645
646 g_batch_dt := TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS');
647 p_batch_id := p_batch_type || -- This indicates Origination
648 p_cycle_year || -- This is cycle Year ..
649 RPAD(NVL(p_rep_pell_id,' '),6) ||
650 g_batch_dt;
651 IF p_batch_type = '#O' THEN
652 ln_data_rec_len := 300;
653 ELSIF p_batch_type = '#D' THEN
654 ln_data_rec_len := 100;
655 END IF;
656
657 -- Get OPE ID.
658 OPEN cur_get_ope_id(p_ci_cal_type, p_ci_sequence_number, p_rep_pell_id);
659 FETCH cur_get_ope_id INTO get_ope_id_rec;
660 CLOSE cur_get_ope_id;
661
662 l_header := NULL;
663 l_header := RPAD('GRANT HDR',10) ||
664 LPAD(ln_data_rec_len,4,'0') ||
665 RPAD(NVL(p_batch_id,' '),26)||
666 RPAD(NVL(get_ope_id_rec.ope_cd,' '),8) || -- OPE ID
667 RPAD('IGS1157',10) || -- Software Provider
668 RPAD(' ',5) || -- Unused
669 RPAD(' ',5) || -- ED Use Only
670 RPAD(' ',8) || -- Process Date by Put in by RFMS
671 RPAD(' ',24); -- Batch Reject Reasons
672
673 --
674 -- Header Record Length=100, this is same length as that of data record
675 --
676
677 --
678 -- Insert Batch ID of this batch into igf_gr_Rfms_batch table
679 --
680
681
682 l_rowid := NULL;
683
684 igf_gr_rfms_batch_pkg.insert_row (
685 x_rowid => l_rowid,
686 x_rfmb_id => p_rfmb_id,
687 x_batch_id => p_batch_id,
688 x_data_rec_length => ln_data_rec_len,
689 x_ope_id => NULL,
690 x_software_providor => NULL,
691 x_rfms_process_dt => TRUNC(SYSDATE),
692 x_rfms_ack_dt => NULL,
693 x_rfms_ack_batch_id => NULL,
694 x_reject_reason => NULL,
695 x_mode => 'R');
696
697 RETURN l_header;
698
699 ELSE
700 RAISE no_file_version;
701 END IF;
702
703
704 EXCEPTION
705
706 WHEN no_file_version THEN
707 RAISE;
708
709 WHEN OTHERS THEN
710 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
711 fnd_message.set_token('NAME','IGF_GR_GEN.PREPARE_HEADER'|| ' ' || SQLERRM);
712 igs_ge_msg_stack.add;
713 app_exception.raise_exception;
714
715
716 END get_pell_header;
717
718
719 FUNCTION get_pell_trailer (p_ver_num IN VARCHAR2,
720 p_cycle_year IN VARCHAR2,
721 p_rep_pell_id IN igf_gr_pell_setup_all.rep_pell_id%TYPE,
722 p_batch_type IN VARCHAR2,
723 p_num_of_rec IN NUMBER,
724 p_amount_total IN NUMBER,
725 p_batch_id OUT NOCOPY VARCHAR2)
726 RETURN VARCHAR2
727 IS
728
729 --------------------------------------------------------------------------------------------
730 --
731 --------------------------------------------------------------------------------------------
732
733 l_trailer VARCHAR2(1000);
734 l_sign_ind VARCHAR2(10);
735 ln_data_rec_len NUMBER;
736
737 BEGIN
738
739 IF p_ver_num IN ('2002-2003','2003-2004','2004-2005','2005-2006') THEN
740
741 p_batch_id := p_batch_type || -- This indicates Origination
742 RPAD(NVL(p_cycle_year,' '),4) || -- This is cycle Year ..
743 RPAD(NVL(p_rep_pell_id,' '),6) ||
744 g_batch_dt;
745
746 IF NVL(p_amount_total,0) >= 0 THEN
747 l_sign_ind := 'P';
748 ELSE
749 l_sign_ind := 'N';
750 END IF;
751
752 l_trailer := NULL;
753
754 IF p_batch_type = '#O' THEN
755 ln_data_rec_len := 300;
756 ELSIF p_batch_type = '#D' THEN
757 ln_data_rec_len := 100;
758 END IF;
759
760 l_trailer := RPAD('GRANT TLR',10) ||
761 LPAD(ln_data_rec_len,4,'0') ||
762 RPAD(NVL(p_batch_id,' '),26) ||
763 LPAD(NVL(p_num_of_rec,0),6,'0') ||
764 LPAD(TO_CHAR(ABS(NVL(100*p_amount_total,0))),11,'0') ||
765 RPAD(NVL(l_sign_ind,' '),1) ||
766 RPAD(' ',6) || -- updated by RFMS
767 RPAD(' ',11) || -- updated by RFMS
768 RPAD(' ',1) || -- Accepted and corrected sign indicator
769 RPAD(' ',6) || -- Number of Duplicate Records, updated by RFMS
770 RPAD(' ',18); -- updated by RFMS
771
772 RETURN l_trailer;
773
774 ELSE
775 RAISE no_file_version;
776 END IF;
777
778
779 EXCEPTION
780
781 WHEN no_file_version THEN
782 RAISE;
783
784 WHEN OTHERS THEN
785 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
786 fnd_message.set_token('NAME','IGF_GR_GEN.PREPARE_TRAILER'|| ' ' || SQLERRM);
787 igs_ge_msg_stack.add;
788 app_exception.raise_exception;
789
790 END get_pell_trailer;
791
792
793 PROCEDURE process_pell_ack ( p_ver_num IN VARCHAR2,
794 p_file_type IN VARCHAR2,
795 p_number_rec OUT NOCOPY NUMBER,
796 p_last_gldr_id OUT NOCOPY NUMBER,
797 p_batch_id OUT NOCOPY VARCHAR2)
798 IS
799
800 ----------------------------------------------------------------------------------------------
801 --
802 --Change History:
803 --Bug No:-2460904 Desc :- Pell Formatting Issues
804 --Who When What
805 --rasahoo 13-May-2003 Bug #2938258 Added code for Resetting Origination Status
806 -- to "Ready to Send" for the rejected Pell Disbursement Records.
807 --bkkumar 24-jun-2003 Bug #2974248 Added the code for proceeding in case of
808 -- warning codes.
809 --------------------------------------------------------------------------------------------
810
811
812
813 CURSOR cur_header (p_file_type VARCHAR2)
814 IS
815 SELECT
816 record_data
817 FROM
818 igf_gr_load_file_t
819 WHERE gldr_id = 1
820 AND record_data LIKE 'GRANT HDR%'
821 AND file_type = p_file_type;
822
823 header_rec cur_header%ROWTYPE;
824
825 CURSOR cur_trailer (p_file_type VARCHAR2)
826 IS
827 SELECT
828 gldr_id last_gldr_id,
829 record_data
830 FROM
831 igf_gr_load_file_t
832 WHERE
833 gldr_id = (SELECT MAX(gldr_id) FROM igf_gr_load_file_t) AND
834 record_data LIKE 'GRANT TLR%' AND
835 file_type = p_file_type;
836
837 trailer_rec cur_trailer%ROWTYPE;
838
839 CURSOR cur_rfms_batch ( p_batch_id igf_gr_rfms_batch_all.batch_id%TYPE)
840 IS
841 SELECT *
842 FROM
843 igf_gr_rfms_batch
844 WHERE
845 batch_id = p_batch_id
846 FOR UPDATE OF rfms_ack_dt NOWAIT;
847
848 rfms_batch_rec cur_rfms_batch%ROWTYPE;
849
850
851
852
853 CURSOR cur_gr_rfms(p_rfmb_id igf_gr_rfms.rfmb_id%TYPE)
854 IS
855 SELECT
856 *
857 FROM
858 igf_gr_rfms
859 WHERE
860 rfmb_id = p_rfmb_id AND
861 orig_action_code = 'S';
862
863 cur_get_rfms cur_gr_rfms%ROWTYPE;
864
865 CURSOR cur_gr_rfms_disb(p_rfmb_id igf_gr_rfms_disb.rfmb_id%TYPE)
866 IS
867 SELECT
868 *
869 FROM
870 igf_gr_rfms_disb
871 WHERE
872 rfmb_id = p_rfmb_id AND
873 disb_ack_act_status = 'S';
874
875 cur_get_rfms_disb cur_gr_rfms_disb%ROWTYPE;
876 l_file_name VARCHAR2(100);
877 l_rfms_process_dt VARCHAR2(200);
878 l_batch_rej_reason VARCHAR2(300);
879 l_rowid VARCHAR2(25);
880 l_count NUMBER;
881 l_error_code igf_gr_rfms_error.edit_code%TYPE;
882 lb_error_cd BOOLEAN := FALSE;
883 l_rfmb_id igf_gr_rfms.rfmb_id%TYPE;
884 l_disb_rfmb_id igf_gr_rfms_disb.rfmb_id%TYPE;
885
886 BEGIN
887
888 l_count := 1;
889
890 IF p_ver_num IN ('2002-2003','2003-2004','2004-2005','2005-2006','2006-2007') THEN
891
892 OPEN cur_header (p_file_type);
893 FETCH cur_header INTO header_rec;
894
895 IF cur_header%NOTFOUND THEN
896 CLOSE cur_header;
897 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
898 -- File uploaded is incomplete.
899 igs_ge_msg_stack.add;
900 RAISE file_not_loaded;
901 END IF;
902 CLOSE cur_header;
903
904 BEGIN
905 l_file_name := LTRIM(RTRIM(SUBSTR(header_rec.record_data,1,10)));
906 p_batch_id := LTRIM(RTRIM(SUBSTR(header_rec.record_data,15,26)));
907 l_rfms_process_dt := LTRIM(RTRIM(SUBSTR(header_rec.record_data,69,8)));
908 l_batch_rej_reason := NVL(LTRIM(RTRIM(SUBSTR(header_rec.record_data,77,24))),0);
909 --
910 -- This will make sure process does not bomb if the data is corrupt
911 --
912 EXCEPTION
913 WHEN OTHERS THEN
914 RAISE corrupt_data_file;
915 END;
916
917 IF LTRIM(RTRIM(l_file_name)) = 'GRANT HDR' THEN -- Remove LIKE, put =
918
919 --
920 -- Update the igf_gr_rfms_batch table to reflect new values
921 -- This is done only for #O and #D Files
922 --
923 IF p_file_type IN ('GR_RFMS_ORIG','GR_RFMS_DISB_ORIG') THEN
924 OPEN cur_rfms_batch(p_batch_id);
925 FETCH cur_rfms_batch INTO rfms_batch_rec;
926 IF cur_rfms_batch%NOTFOUND THEN
927 CLOSE cur_rfms_batch;
928 RAISE batch_not_in_system;
929 END IF;
930
931 igf_gr_rfms_batch_pkg.update_row (
932 x_rowid => rfms_batch_rec.row_id,
933 x_rfmb_id => rfms_batch_rec.rfmb_id,
934 x_batch_id => rfms_batch_rec.batch_id,
935 x_data_rec_length => rfms_batch_rec.data_rec_length,
936 x_ope_id => rfms_batch_rec.ope_id,
937 x_software_providor => rfms_batch_rec.software_providor,
938 x_rfms_process_dt => fnd_date.string_to_date(l_rfms_process_dt,'YYYYMMDD'),
939 x_rfms_ack_dt => TRUNC(SYSDATE),
940 x_rfms_ack_batch_id => p_batch_id,
941 x_reject_reason => l_batch_rej_reason,
942 x_mode => 'R' );
943
944
945
946 CLOSE cur_rfms_batch;
947 END IF;
948
949 IF TO_NUMBER(l_batch_rej_reason) > 0 THEN
950
951 p_number_rec := 0;
952 fnd_file.new_line(fnd_file.log,1);
953 fnd_message.set_name('IGF','IGF_GR_BATCH_REJ');
954 fnd_file.put_line(fnd_file.log,fnd_message.get);
955 fnd_file.new_line(fnd_file.log,1);
956
957 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BATCH_ID') ||' ' || p_batch_id);
958 fnd_file.put_line(fnd_file.log,RPAD('-',35,'-'));
959 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','EDIT_CODE') || ' ' || igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','TYPE'));
960 fnd_file.put_line(fnd_file.log,RPAD('-',35,'-'));
961
962 BEGIN
963 FOR l_cn IN 1 .. 8
964 LOOP
965
966 l_error_code := NVL(SUBSTR(l_batch_rej_reason,l_count,3),'000');
967
968 IF NVL(l_error_code,'*') <> '000' THEN
969 IF l_error_code NOT IN ('216','218','219','220','222','235','239','240') THEN
970 fnd_file.put_line(fnd_file.log,RPAD(l_error_code,10) || ' ' || igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ERROR'));
971 IF NOT lb_error_cd THEN
972 lb_error_cd := TRUE;
973 END IF;
974 ELSE
975 fnd_file.put_line(fnd_file.log,RPAD(l_error_code,10) || ' ' || igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','WARN'));
976 END IF;
977 END IF;
978 l_count := l_count + 3;
979 END LOOP;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 NULL;
984
985 END;
986 fnd_file.new_line(fnd_file.log,1);
987
988 ----Bug #2974248
989 IF lb_error_cd THEN
990 ----Bug #2938258
991 IF p_file_type = 'GR_RFMS_ORIG' THEN
992
993 fnd_message.set_name('IGF','IGF_GR_RESET_REJ_ORIG_BTCH_REC');
994 fnd_message.set_token('BATCH_ID',p_batch_id);
995 fnd_file.put_line(fnd_file.log,fnd_message.get);
996 fnd_file.new_line(fnd_file.log,1);
997
998 l_rfmb_id:=rfms_batch_rec.rfmb_id;
999
1000
1001 FOR cur_get_rfms IN cur_gr_rfms(l_rfmb_id)
1002 LOOP
1003 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID') ||' : ' || cur_get_rfms.origination_id);
1004
1005 igf_gr_rfms_pkg.update_row(
1006 x_rowid => cur_get_rfms.row_id,
1007 x_origination_id => cur_get_rfms.origination_id,
1008 x_ci_cal_type => cur_get_rfms.ci_cal_type,
1009 x_ci_sequence_number => cur_get_rfms.ci_sequence_number,
1010 x_base_id => cur_get_rfms.base_id,
1011 x_award_id => cur_get_rfms.award_id,
1012 x_rfmb_id => NULL,
1013 x_sys_orig_ssn => cur_get_rfms.sys_orig_ssn,
1014 x_sys_orig_name_cd => cur_get_rfms.sys_orig_name_cd,
1015 x_transaction_num => cur_get_rfms.transaction_num,
1016 x_efc => igf_gr_gen.get_pell_efc(cur_get_rfms.base_id),
1017 x_ver_status_code => cur_get_rfms.ver_status_code,
1018 x_secondary_efc => cur_get_rfms.secondary_efc,
1019 x_secondary_efc_cd => igf_gr_gen.get_pell_efc_code(cur_get_rfms.base_id),
1020 x_pell_amount => cur_get_rfms.pell_amount,
1021 x_pell_profile => cur_get_rfms.pell_profile,
1022 x_enrollment_status => cur_get_rfms.enrollment_status,
1023 x_enrollment_dt => cur_get_rfms.enrollment_dt,
1024 x_coa_amount => cur_get_rfms.coa_amount,
1025 x_academic_calendar => cur_get_rfms.academic_calendar,
1026 x_payment_method => cur_get_rfms.payment_method,
1027 x_total_pymt_prds => cur_get_rfms.total_pymt_prds,
1028 x_incrcd_fed_pell_rcp_cd => cur_get_rfms.incrcd_fed_pell_rcp_cd,
1029 x_attending_campus_id => cur_get_rfms.attending_campus_id,
1030 x_est_disb_dt1 => cur_get_rfms.est_disb_dt1,
1031 x_orig_action_code => 'R',
1032 x_orig_status_dt => cur_get_rfms.orig_status_dt,
1033 x_orig_ed_use_flags => cur_get_rfms.orig_ed_use_flags,
1034 x_ft_pell_amount => cur_get_rfms.ft_pell_amount,
1035 x_prev_accpt_efc => cur_get_rfms.prev_accpt_efc,
1036 x_prev_accpt_tran_no => cur_get_rfms.prev_accpt_tran_no,
1037 x_prev_accpt_sec_efc_cd => cur_get_rfms.prev_accpt_sec_efc_cd,
1038 x_prev_accpt_coa => cur_get_rfms.prev_accpt_coa,
1039 x_orig_reject_code => cur_get_rfms.orig_reject_code,
1040 x_wk_inst_time_calc_pymt => cur_get_rfms.wk_inst_time_calc_pymt,
1041 x_wk_int_time_prg_def_yr => cur_get_rfms.wk_int_time_prg_def_yr,
1042 x_cr_clk_hrs_prds_sch_yr => cur_get_rfms.cr_clk_hrs_prds_sch_yr,
1043 x_cr_clk_hrs_acad_yr => cur_get_rfms.cr_clk_hrs_acad_yr,
1044 x_inst_cross_ref_cd => cur_get_rfms.inst_cross_ref_cd,
1045 x_low_tution_fee => cur_get_rfms.low_tution_fee,
1046 x_rec_source => cur_get_rfms.rec_source,
1047 x_pending_amount => cur_get_rfms.pending_amount,
1048 x_mode => 'R',
1049 x_birth_dt => cur_get_rfms.birth_dt,
1050 x_last_name => cur_get_rfms.last_name,
1051 x_first_name => cur_get_rfms.first_name,
1052 x_middle_name => cur_get_rfms.middle_name,
1053 x_current_ssn => cur_get_rfms.current_ssn,
1054 x_legacy_record_flag => NULL,
1055 x_reporting_pell_cd => cur_get_rfms.rep_pell_id,
1056 x_rep_entity_id_txt => cur_get_rfms.rep_entity_id_txt,
1057 x_atd_entity_id_txt => cur_get_rfms.atd_entity_id_txt,
1058 x_note_message => cur_get_rfms.note_message,
1059 x_full_resp_code => cur_get_rfms.full_resp_code,
1060 x_document_id_txt => cur_get_rfms.document_id_txt
1061 );
1062
1063 END LOOP;
1064 ELSIF p_file_type = 'GR_RFMS_DISB_ORIG' THEN
1065
1066 fnd_message.set_name('IGF','IGF_GR_RESET_REJ_DISB_BTCH_REC');
1067 fnd_message.set_token('BATCH_ID',p_batch_id);
1068 fnd_file.put_line(fnd_file.log,fnd_message.get);
1069 fnd_file.new_line(fnd_file.log,1);
1070 l_disb_rfmb_id:=rfms_batch_rec.rfmb_id;
1071 FOR cur_get_rfms_disb IN cur_gr_rfms_disb ( l_disb_rfmb_id )
1072 LOOP
1073 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID') ||' : ' || cur_get_rfms_disb.origination_id
1074 ||' , '
1075 ||igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_REF_NUM') ||' : ' || cur_get_rfms_disb.disb_ref_num);
1076
1077 igf_gr_rfms_disb_pkg.update_row (
1078 x_rowid => cur_get_rfms_disb.row_id,
1079 x_rfmd_id => cur_get_rfms_disb.rfmd_id ,
1080 x_origination_id => cur_get_rfms_disb.origination_id,
1081 x_disb_ref_num => cur_get_rfms_disb.disb_ref_num,
1082 x_disb_dt => cur_get_rfms_disb.disb_dt,
1083 x_disb_amt => cur_get_rfms_disb.disb_amt,
1084 x_db_cr_flag => cur_get_rfms_disb.db_cr_flag,
1085 x_disb_ack_act_status => 'R', -- record processed
1086 x_disb_status_dt => cur_get_rfms_disb.disb_status_dt,
1087 x_accpt_disb_dt => cur_get_rfms_disb.accpt_disb_dt ,
1088 x_disb_accpt_amt => cur_get_rfms_disb.disb_accpt_amt,
1089 x_accpt_db_cr_flag => cur_get_rfms_disb.accpt_db_cr_flag,
1090 x_disb_ytd_amt => cur_get_rfms_disb.disb_ytd_amt,
1091 x_pymt_prd_start_dt => cur_get_rfms_disb.pymt_prd_start_dt,
1092 x_accpt_pymt_prd_start_dt => cur_get_rfms_disb.accpt_pymt_prd_start_dt,
1093 x_edit_code => cur_get_rfms_disb.edit_code ,
1094 x_rfmb_id => NULL,
1095 x_mode => 'R',
1096 x_ed_use_flags => cur_get_rfms_disb.ed_use_flags);
1097
1098 END LOOP;
1099 END IF;
1100 END IF;
1101 ---end Bug #2938258
1102
1103 END IF;
1104
1105 ELSE
1106 fnd_message.set_name('IGF','IGF_GE_INVALID_FILE');
1107 igs_ge_msg_stack.add;
1108 RAISE file_not_loaded;
1109 END IF;
1110
1111 IF NOT lb_error_cd THEN
1112
1113 OPEN cur_trailer (p_file_type);
1114 FETCH cur_trailer INTO trailer_rec;
1115 -- check for a proper trailer record
1116
1117 IF cur_trailer%NOTFOUND THEN
1118 CLOSE cur_trailer;
1119 fnd_message.set_name('IGF','IGF_GE_FILE_NOT_COMPLETE');
1120 --File uploaded is incomplete.
1121 igs_ge_msg_stack.add;
1122 RAISE file_not_loaded;
1123 END IF;
1124 CLOSE cur_trailer;
1125
1126 BEGIN
1127 --
1128 -- This will make sure process does not bomb if the data is corrupt
1129 --
1130
1131 p_number_rec := TO_NUMBER(SUBSTR(trailer_rec.record_data,41,6));
1132
1133 EXCEPTION
1134 WHEN OTHERS THEN
1135 RAISE corrupt_data_file;
1136 END;
1137
1138 p_last_gldr_id := trailer_rec.last_gldr_id;
1139
1140
1141 END IF;
1142
1143
1144 ELSE
1145 RAISE no_file_version;
1146 END IF;
1147
1148
1149 EXCEPTION
1150
1151 WHEN no_file_version THEN
1152 RAISE;
1153
1154 WHEN corrupt_data_file THEN
1155 RAISE;
1156
1157 WHEN batch_not_in_system THEN
1158 RAISE;
1159
1160 WHEN file_not_loaded THEN
1161 RAISE;
1162
1163 WHEN OTHERS THEN
1164
1165 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1166 fnd_message.set_token('NAME','IGF_GR_GEN.PROCESS_PELL_ACK'|| ' ' || SQLERRM);
1167 igs_ge_msg_stack.add;
1168 app_exception.raise_exception;
1169
1170 END process_pell_ack;
1171
1172
1173 FUNCTION send_orig_disb ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1174 RETURN BOOLEAN
1175 IS
1176
1177 --
1178 -- This routine is called from pell origination processes
1179 -- before updating rfms table with the batch id seq no
1180 --
1181 -- Function to determine if an Origination / Disbursement Record
1182 -- can be reported or not
1183 --
1184
1185 CURSOR cur_rfms_dat ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1186 IS
1187 SELECT
1188 ver_status_code
1189 FROM
1190 igf_gr_rfms
1191 WHERE
1192 origination_id = p_orig_id;
1193
1194 rfms_dat_rec cur_rfms_dat%ROWTYPE;
1195
1196 lb_send BOOLEAN;
1197
1198 BEGIN
1199
1200 lb_send := TRUE;
1201
1202 OPEN cur_rfms_dat ( p_orig_id );
1203 FETCH cur_rfms_dat INTO rfms_dat_rec;
1204 CLOSE cur_rfms_dat;
1205
1206 IF NOT fresh_origintn(p_orig_id) AND
1207 NVL(rfms_dat_rec.ver_status_code,'X') = 'W'THEN
1208 lb_send := FALSE;
1209 END IF;
1210
1211 RETURN lb_send;
1212
1213 EXCEPTION
1214
1215 WHEN OTHERS THEN
1216
1217 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1218 fnd_message.set_token('NAME','IGF_GR_GEN.SEND_ORIG_DISB'|| ' ' || SQLERRM);
1219 igs_ge_msg_stack.add;
1220 app_exception.raise_exception;
1221
1222 END send_orig_disb;
1223
1224
1225
1226 FUNCTION get_min_pell_disb ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1227 RETURN NUMBER
1228 IS
1229
1230 --
1231 --
1232 --
1233 CURSOR cur_pell_disb ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1234 IS
1235 SELECT
1236 MIN(disb_ref_num)
1237 FROM
1238 igf_gr_rfms_disb
1239 WHERE
1240 origination_id = p_orig_id;
1241
1242 ln_min_num NUMBER(10);
1243
1244 BEGIN
1245
1246 OPEN cur_pell_disb ( p_orig_id );
1247 FETCH cur_pell_disb INTO ln_min_num;
1248 CLOSE cur_pell_disb ;
1249
1250 RETURN ln_min_num;
1251
1252 EXCEPTION
1253 WHEN OTHERS THEN
1254
1255 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1256 fnd_message.set_token('NAME','IGF_GR_GEN.GET_MIN_PELL_DISB'|| ' ' || SQLERRM);
1257 igs_ge_msg_stack.add;
1258 app_exception.raise_exception;
1259
1260
1261 END get_min_pell_disb;
1262
1263 FUNCTION get_min_awd_disb ( p_award_id igf_aw_award_all.award_id%TYPE)
1264 RETURN NUMBER
1265 IS
1266 --
1267 --
1268 --
1269 CURSOR cur_awd_disb ( p_award_id igf_aw_award_all.award_id%TYPE)
1270 IS
1271 SELECT
1272 MIN(disb_num)
1273 FROM
1274 igf_aw_awd_disb
1275 WHERE
1276 award_id = p_award_id;
1277
1278 ln_min_num NUMBER(10);
1279
1280 BEGIN
1281
1282 OPEN cur_awd_disb ( p_award_id );
1283 FETCH cur_awd_disb INTO ln_min_num;
1284 CLOSE cur_awd_disb ;
1285
1286 RETURN ln_min_num;
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290
1291 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1292 fnd_message.set_token('NAME','IGF_GR_GEN.GET_MIN_AWD_DISB'|| ' ' || SQLERRM);
1293 igs_ge_msg_stack.add;
1294 app_exception.raise_exception;
1295
1296 END get_min_awd_disb;
1297
1298
1299
1300 FUNCTION get_pell_efc_code ( p_base_id IN igf_aw_award_all.base_id%TYPE)
1301 RETURN VARCHAR2
1302 IS
1303 --rasahoo 27-Nov-2003 FA128 Isir Update
1304 -- removed cursor 'cur_sec_efc_type' and added cursor 'get_awd_fmly_contrib_type'
1305 -- sjadhav
1306 -- Bug 2460904
1307 -- Fuction to determine the efc type of Pell Record.
1308 -- ISIR paid efc can be either Primary or Secondary.
1309 -- Based on ISIR efc, we will map Pell Sec EFC Code.
1310 --
1311
1312 CURSOR get_awd_fmly_contrib_type ( p_base_id igf_aw_award_all.base_id%TYPE)
1313 IS
1314 SELECT award_fmly_contribution_type
1315 FROM igf_ap_fa_base_rec
1316 WHERE base_id = p_base_id;
1317
1318
1319 c_awd_fmly_contrib_type get_awd_fmly_contrib_type%ROWTYPE;
1320
1321 --
1322 -- Cursor to get Origination ID
1323 --
1324 CURSOR cur_get_orgn ( p_base_id igf_aw_award_all.base_id%TYPE)
1325 IS
1326 SELECT
1327 origination_id,
1328 secondary_efc_cd
1329 FROM
1330 igf_gr_rfms
1331 WHERE
1332 base_id = p_base_id;
1333
1334 get_orgn_rec cur_get_orgn%ROWTYPE;
1335
1336 l_sec_efc_type igf_gr_rfms_all.secondary_efc_cd%TYPE;
1337
1338 BEGIN
1339
1340 OPEN cur_get_orgn(p_base_id);
1341 FETCH cur_get_orgn INTO get_orgn_rec;
1342 CLOSE cur_get_orgn;
1343
1344
1345 l_sec_efc_type := NULL;
1346
1347 OPEN get_awd_fmly_contrib_type(p_base_id);
1348 FETCH get_awd_fmly_contrib_type INTO c_awd_fmly_contrib_type;
1349 CLOSE get_awd_fmly_contrib_type;
1350
1351 IF c_awd_fmly_contrib_type.award_fmly_contribution_type IS NOT NULL THEN
1352 --
1353 -- Fresh Orign
1354 --
1355 IF fresh_origintn(get_orgn_rec.origination_id) THEN
1356
1357 IF c_awd_fmly_contrib_type.award_fmly_contribution_type = '2' THEN
1358 l_sec_efc_type := 'S';
1359 END IF;
1360 --
1361 -- Subs Orign
1362 --
1363 ELSE
1364
1365 IF c_awd_fmly_contrib_type.award_fmly_contribution_type = '2' THEN
1366 l_sec_efc_type := 'S';
1367 ELSIF c_awd_fmly_contrib_type.award_fmly_contribution_type = '1' AND
1368 get_orgn_rec.secondary_efc_cd = 'S' THEN
1369 l_sec_efc_type := 'O';
1370 END IF;
1371
1372 END IF;
1373
1374 END IF;
1375
1376 RETURN l_sec_efc_type;
1377
1378 EXCEPTION
1379
1380 WHEN OTHERS THEN
1381
1382 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1383 fnd_message.set_token('NAME','IGF_GR_GEN.GET_PELL_EFC_CODE'|| ' ' || SQLERRM);
1384 igs_ge_msg_stack.add;
1385 app_exception.raise_exception;
1386
1387 END get_pell_efc_code;
1388
1389
1390 FUNCTION fresh_origintn ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1391 RETURN BOOLEAN
1392 IS
1393
1394 --
1395 -- Function to determine if an Origination Record is being
1396 -- sent for first time or not
1397 --
1398
1399 CURSOR cur_rfms_dat ( p_orig_id igf_gr_rfms_all.origination_id%TYPE)
1400 IS
1401 SELECT
1402 batch.rfms_ack_batch_id
1403 FROM
1404 igf_gr_rfms pell,
1405 igf_gr_rfms_batch batch
1406 WHERE
1407 origination_id = p_orig_id AND
1408 pell.rfmb_id = batch.rfmb_id AND
1409 batch.rfms_ack_batch_id IS NOT NULL;
1410
1411 rfms_dat_rec cur_rfms_dat%ROWTYPE;
1412
1413 lb_send BOOLEAN;
1414
1415 BEGIN
1416
1417 lb_send := FALSE;
1418
1419 --
1420 -- Check if Origination is being reported for first time
1421 -- if rfms_ack_batch_id is not null it means, origination is being sent
1422 -- again
1423 --
1424 OPEN cur_rfms_dat ( p_orig_id );
1425 FETCH cur_rfms_dat INTO rfms_dat_rec;
1426 CLOSE cur_rfms_dat;
1427
1428 IF NVL(rfms_dat_rec.rfms_ack_batch_id,'X') = 'X' THEN
1429 lb_send := TRUE;
1430 END IF;
1431
1432 RETURN lb_send;
1433
1434 EXCEPTION
1435
1436 WHEN OTHERS THEN
1437
1438 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1439 fnd_message.set_token('NAME','IGF_GR_GEN.FRESH_ORIGINTN'|| ' ' || SQLERRM);
1440 igs_ge_msg_stack.add;
1441 app_exception.raise_exception;
1442
1443 END fresh_origintn;
1444
1445
1446 FUNCTION get_fund_id ( p_award_id igf_aw_award_all.award_id%TYPE)
1447 RETURN NUMBER
1448 IS
1449
1450 --
1451 -- Function to retreive Fund ID from Award ID
1452 --
1453
1454
1455 CURSOR cur_get_fund ( p_award_id igf_aw_award_all.award_id%TYPE)
1456 IS
1457 SELECT fund_id
1458 FROM
1459 igf_aw_award
1460 WHERE
1461 award_id = p_award_id;
1462
1463 get_fund_rec cur_get_fund%ROWTYPE;
1464
1465 BEGIN
1466
1467 OPEN cur_get_fund ( p_award_id);
1468 FETCH cur_get_fund INTO get_fund_rec;
1469 CLOSE cur_get_fund;
1470
1471 RETURN NVL(get_fund_rec.fund_id,-1);
1472
1473 EXCEPTION
1474
1475 WHEN OTHERS THEN
1476
1477 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1478 fnd_message.set_token('NAME','IGF_GR_GEN.GET_FUND_ID'|| ' ' || SQLERRM);
1479 igs_ge_msg_stack.add;
1480 app_exception.raise_exception;
1481
1482
1483 END get_fund_id;
1484
1485
1486 FUNCTION get_ssn_digits(p_ssn igs_pe_alt_pers_id.api_person_id_uf%TYPE)
1487 RETURN VARCHAR2 IS
1488 --
1489 -- sjadhav
1490 -- This functions strips formatted ssn od special chars and
1491 -- returns sanitisd ssn
1492 --
1493
1494 lv_ssn igs_pe_alt_pers_id.api_person_id_uf%TYPE;
1495 lv_compare_str VARCHAR2(80);
1496
1497 BEGIN
1498
1499 lv_compare_str := '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ"[]{}`~!@#$%^&*_+=-,./?><():; ' ||'''';
1500 lv_ssn := TRANSLATE (UPPER(LTRIM(RTRIM(p_ssn))),lv_compare_str,'1234567890');
1501
1502 RETURN SUBSTR(RTRIM(LTRIM(lv_ssn)),1,9);
1503
1504 EXCEPTION
1505
1506 WHEN OTHERS THEN
1507
1508 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1509 fnd_message.set_token('NAME','IGF_GR_GEN.GET_SSN_DIGITS'|| ' ' || SQLERRM);
1510 igs_ge_msg_stack.add;
1511 app_exception.raise_exception;
1512
1513 END get_ssn_digits;
1514
1515
1516 PROCEDURE delete_coa( p_record IN VARCHAR2,
1517 p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
1518 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
1519 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
1520 p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE
1521 )
1522 IS
1523
1524 --
1525 -- Bug 2613546
1526 -- sjadhav,
1527 -- routine to delete cost of attendance group/item childs
1528 --
1529
1530 --
1531 -- Cursor to fetch the COA Items
1532 --
1533
1534 CURSOR cur_coa_items ( p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
1535 p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
1536 p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE
1537 )
1538
1539
1540 IS
1541 SELECT
1542 *
1543 FROM
1544 igf_aw_coa_grp_item
1545 WHERE
1546 coa_code = p_coa_code AND
1547 ci_cal_type = p_cal_type AND
1548 ci_sequence_number = p_sequence_number;
1549
1550
1551 --
1552 -- Cursor to fetch COA Terms
1553 --
1554
1555 CURSOR cur_coa_terms ( p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
1556 p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
1557 p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE
1558 )
1559 IS
1560 SELECT
1561 row_id
1562 FROM
1563 igf_aw_coa_ld
1564 WHERE
1565 coa_code = p_coa_code AND
1566 ci_cal_type = p_cal_type AND
1567 ci_sequence_number = p_sequence_number;
1568
1569 --
1570 -- Cursor to fetch Overridden COA Items
1571 --
1572
1573 CURSOR cur_coa_ovrd_items( p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
1574 p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
1575 p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE,
1576 p_item_code igf_aw_coa_grp_item_all.item_code%TYPE)
1577 IS
1578 SELECT
1579 row_id
1580 FROM
1581 igf_aw_cit_ld_overide
1582 WHERE
1583 coa_code = p_coa_code AND
1584 ci_cal_type = p_cal_type AND
1585 ci_sequence_number = p_sequence_number AND
1586 item_code = p_item_code;
1587
1588
1589 BEGIN
1590
1591
1592 --
1593 -- If p_record = COA_GROUP it means we have to delete all child records
1594 -- for COA Grooup
1595 -- 1. First Delete Overridden Term Distribution for Items
1596 -- 2. Next Delete COA Items
1597 -- 3. Then delete COA Terms
1598 --
1599
1600 --
1601 -- If p_record = COA_TERM it means we have to delete
1602 -- ONLY Overridden Term Distribution for all Items
1603 --
1604
1605 IF p_record IN ('COA_GROUP','COA_TERM') THEN
1606 FOR coa_items_rec IN cur_coa_items(p_coa_code,
1607 p_cal_type,
1608 p_sequence_number)
1609 LOOP
1610 --
1611 -- Loop thru the term overide recs and delete
1612 --
1613 FOR l_term IN cur_coa_ovrd_items(p_coa_code,
1614 p_cal_type,
1615 p_sequence_number,
1616 coa_items_rec.item_code)
1617 LOOP
1618 igf_aw_cit_ld_ovrd_pkg.delete_row(l_term.row_id);
1619 END LOOP;
1620
1621 --
1622 -- Delete Items only if p_record = COA_GROUP
1623 --
1624
1625 IF p_record = 'COA_GROUP' THEN
1626 igf_aw_coa_grp_item_pkg.delete_row(coa_items_rec.row_id);
1627 END IF;
1628
1629 --
1630 -- Update Items with item_dist = 'N' only if p_record = COA_TERM
1631 --
1632
1633 IF p_record = 'COA_TERM' THEN
1634 igf_aw_coa_grp_item_pkg.update_row (
1635 x_mode => 'R',
1636 x_rowid => coa_items_rec.row_id,
1637 x_coa_code => coa_items_rec.coa_code,
1638 x_ci_cal_type => coa_items_rec.ci_cal_type,
1639 x_ci_sequence_number => coa_items_rec.ci_sequence_number,
1640 x_item_code => coa_items_rec.item_code,
1641 x_default_value => coa_items_rec.default_value,
1642 x_fixed_cost => coa_items_rec.fixed_cost,
1643 x_pell_coa => NULL,
1644 x_active => coa_items_rec.active,
1645 x_pell_amount => coa_items_rec.pell_amount,
1646 x_pell_alternate_amt => coa_items_rec.pell_alternate_amt,
1647 x_item_dist => 'N',
1648 x_lock_flag => coa_items_rec.lock_flag);
1649 END IF;
1650
1651 END LOOP;
1652
1653 IF p_record = 'COA_GROUP' THEN
1654 --
1655 -- Loop thru the recs and delete
1656 --
1657
1658 FOR l_term IN cur_coa_terms(p_coa_code,
1659 p_cal_type,
1660 p_sequence_number)
1661 LOOP
1662 igf_aw_coa_ld_pkg.delete_row(l_term.row_id);
1663 END LOOP;
1664
1665 END IF;
1666
1667 ELSIF p_record = 'COA_ITEM' THEN
1668 --
1669 -- Loop thru the recs and delete
1670 --
1671 FOR l_term IN cur_coa_ovrd_items(p_coa_code,
1672 p_cal_type,
1673 p_sequence_number,
1674 p_item_code)
1675 LOOP
1676 igf_aw_cit_ld_ovrd_pkg.delete_row(l_term.row_id);
1677 END LOOP;
1678
1679 END IF;
1680
1681 EXCEPTION
1682 WHEN NO_DATA_FOUND THEN NULL;
1683 WHEN OTHERS THEN
1684
1685 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1686 fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_COA'|| ' ' || SQLERRM);
1687 igs_ge_msg_stack.add;
1688 app_exception.raise_exception;
1689
1690 END delete_coa;
1691
1692
1693 PROCEDURE update_item_dist( p_coa_code IN igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
1694 p_cal_type IN igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
1695 p_sequence_number IN igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
1696 p_item_code IN igf_aw_cit_ld_ovrd_all.item_code%TYPE,
1697 p_upd_result OUT NOCOPY VARCHAR2)
1698
1699 IS
1700
1701 --
1702 -- Bug 2613546
1703 -- sjadhav,
1704 -- routine to update cost of attendance item distribution
1705 --
1706
1707 --
1708 -- Cursor to fetch the COA Items
1709 --
1710
1711 CURSOR cur_coa_items ( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
1712 p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
1713 p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
1714 p_item_code igf_aw_cit_ld_ovrd_all.item_code%TYPE
1715 )
1716 IS
1717 SELECT
1718 *
1719 FROM
1720 igf_aw_coa_grp_item
1721 WHERE
1722 item_code = p_item_code AND
1723 coa_code = p_coa_code AND
1724 ci_cal_type = p_cal_type AND
1725 ci_sequence_number = p_sequence_number;
1726
1727 coa_items_rec cur_coa_items%ROWTYPE;
1728
1729 --
1730 -- Cursor to fetch the Default COA Distribtuon
1731 --
1732 CURSOR cur_default_ld( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
1733 p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
1734 p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE
1735 )
1736 IS
1737 SELECT
1738 ld_perct
1739 FROM
1740 igf_aw_coa_ld
1741 WHERE
1742 coa_code = p_coa_code AND
1743 ci_cal_type = p_cal_type AND
1744 ci_sequence_number = p_sequence_number
1745 ORDER BY
1746 ld_sequence_number;
1747
1748 default_ld_rec cur_default_ld%ROWTYPE;
1749
1750 --
1751 -- Cursor to fetch the Overidden COA Distribtuon
1752 --
1753 CURSOR cur_overide_ld( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
1754 p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
1755 p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
1756 p_item_code igf_aw_cit_ld_ovrd_all.item_code%TYPE
1757 )
1758 IS
1759 SELECT
1760 ld_perct
1761 FROM
1762 igf_aw_cit_ld_overide
1763 WHERE
1764 coa_code = p_coa_code AND
1765 ci_cal_type = p_cal_type AND
1766 ci_sequence_number = p_sequence_number AND
1767 item_code = p_item_code
1768 ORDER BY
1769 ld_sequence_number;
1770
1771 overide_ld_rec cur_overide_ld%ROWTYPE;
1772
1773 lv_item_dist igf_aw_coa_grp_item_all.item_dist%TYPE;
1774
1775 CURSOR cur_pct_total( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
1776 p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
1777 p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE,
1778 p_item_code igf_aw_cit_ld_ovrd_all.item_code%TYPE
1779 )
1780 IS
1781 SELECT
1782 SUM(ld_perct)
1783 FROM
1784 igf_aw_cit_ld_overide
1785 WHERE
1786 coa_code = p_coa_code AND
1787 ci_cal_type = p_cal_type AND
1788 ci_sequence_number = p_sequence_number AND
1789 item_code = p_item_code;
1790
1791 ln_total NUMBER;
1792
1793 --
1794 -- PL/SQL table to store default load %
1795 --
1796
1797 TYPE def_list IS TABLE OF igf_aw_coa_ld.ld_perct%TYPE
1798 INDEX BY BINARY_INTEGER;
1799 def_ele def_list;
1800
1801 --
1802 -- PL/SQL table to store Overidden load %
1803 --
1804
1805 TYPE ovd_list IS TABLE OF igf_aw_cit_ld_overide.ld_perct%TYPE
1806 INDEX BY BINARY_INTEGER;
1807 ovd_ele ovd_list;
1808
1809 ln_count_i BINARY_INTEGER;
1810 ln_count_j BINARY_INTEGER;
1811
1812 BEGIN
1813
1814
1815 lv_item_dist := 'N';
1816 p_upd_result := 'FALSE';
1817
1818 OPEN cur_pct_total ( p_coa_code,
1819 p_cal_type,
1820 p_sequence_number,
1821 p_item_code);
1822 FETCH cur_pct_total INTO ln_total;
1823 CLOSE cur_pct_total;
1824
1825 IF ln_total <> 100 THEN
1826 p_upd_result := 'PERCT_ERROR';
1827 ELSE
1828
1829 ln_count_i := 1;
1830
1831 FOR default_ld_rec IN cur_default_ld ( p_coa_code,
1832 p_cal_type,
1833 p_sequence_number)
1834 LOOP
1835 def_ele(ln_count_i) := default_ld_rec.ld_perct;
1836 ln_count_i := ln_count_i + 1;
1837 END LOOP;
1838
1839 ln_count_j := ln_count_i;
1840 ln_count_i := 1;
1841
1842 FOR overide_ld_rec IN cur_overide_ld ( p_coa_code,
1843 p_cal_type,
1844 p_sequence_number,
1845 p_item_code)
1846 LOOP
1847 ovd_ele(ln_count_i) := overide_ld_rec.ld_perct;
1848 ln_count_i := ln_count_i + 1;
1849 END LOOP;
1850
1851
1852 --
1853 -- compare default and ovrd load %
1854 --
1855
1856 ln_count_i := 1;
1857
1858 LOOP
1859 EXIT WHEN ln_count_i >= ln_count_j;
1860 IF ovd_ele(ln_count_i) <> def_ele(ln_count_i) THEN
1861 lv_item_dist := 'Y';
1862 EXIT;
1863 END IF;
1864 ln_count_i := ln_count_i + 1;
1865
1866 END LOOP;
1867
1868
1869 OPEN cur_coa_items(p_coa_code,
1870 p_cal_type,
1871 p_sequence_number,
1872 p_item_code);
1873 FETCH cur_coa_items INTO coa_items_rec;
1874 CLOSE cur_coa_items;
1875 --Bug ID 2689362
1876 IF lv_item_dist='N' THEN
1877
1878 delete_coa( 'COA_ITEM' ,
1879 coa_items_rec.coa_code,
1880 coa_items_rec.ci_cal_type,
1881 coa_items_rec.ci_sequence_number,
1882 coa_items_rec.item_code
1883 );
1884
1885 END IF;
1886
1887 IF NVL(coa_items_rec.item_dist,'N') <> lv_item_dist THEN
1888
1889 igf_aw_coa_grp_item_pkg.update_row (
1890 x_mode => 'R',
1891 x_rowid => coa_items_rec.row_id,
1892 x_coa_code => coa_items_rec.coa_code,
1893 x_ci_cal_type => coa_items_rec.ci_cal_type,
1894 x_ci_sequence_number => coa_items_rec.ci_sequence_number,
1895 x_item_code => coa_items_rec.item_code,
1896 x_default_value => coa_items_rec.default_value,
1897 x_fixed_cost => coa_items_rec.fixed_cost,
1898 x_pell_coa => NULL,
1899 x_active => coa_items_rec.active,
1900 x_pell_amount => coa_items_rec.pell_amount,
1901 x_pell_alternate_amt => coa_items_rec.pell_alternate_amt,
1902 x_item_dist => lv_item_dist,
1903 x_lock_flag => coa_items_rec.lock_flag);
1904
1905 p_upd_result := 'TRUE';
1906 COMMIT;
1907
1908 END IF;
1909
1910 END IF;
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914
1915 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1916 fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_ITEM_DIST'|| ' ' || SQLERRM);
1917 igs_ge_msg_stack.add;
1918 app_exception.raise_exception;
1919
1920 END update_item_dist;
1921
1922
1923 PROCEDURE get_def_awd_year(p_alternate_code OUT NOCOPY igs_ca_inst_all.alternate_code%TYPE,
1924 p_cal_type OUT NOCOPY igs_ca_inst_all.cal_type%TYPE,
1925 p_sequence_number OUT NOCOPY igs_ca_inst_all.sequence_number%TYPE,
1926 p_start_date OUT NOCOPY igs_ca_inst_all.start_dt%TYPE,
1927 p_end_date OUT NOCOPY igs_ca_inst_all.end_dt%TYPE,
1928 p_err_msg OUT NOCOPY VARCHAR2
1929 )
1930
1931 IS
1932 --
1933 -- Bug 2613546,2606001
1934 -- sjadhav
1935 -- Oct,22,2002.
1936 --
1937 -- the first record fetched from this cursor
1938 -- will be the default award year
1939 --
1940
1941 CURSOR cur_get_awd_yr
1942 IS
1943 SELECT
1944 alternate_code,
1945 cal_type,
1946 sequence_number,
1947 start_dt,
1948 end_dt
1949 FROM
1950 igf_ap_award_year_v
1951 ORDER BY
1952 ABS(TRUNC(SYSDATE) - TRUNC(start_dt));
1953
1954
1955 BEGIN
1956
1957 p_err_msg := 'NULL';
1958
1959 OPEN cur_get_awd_yr;
1960 FETCH cur_get_awd_yr
1961 INTO
1962 p_alternate_code,
1963 p_cal_type,
1964 p_sequence_number,
1965 p_start_date,
1966 p_end_date;
1967
1968 IF cur_get_awd_yr%NOTFOUND THEN
1969 p_err_msg := 'IGF_AW_AWDYR_NOT_FOUND';
1970 END IF;
1971
1972 CLOSE cur_get_awd_yr;
1973
1974 EXCEPTION
1975
1976 WHEN NO_DATA_FOUND THEN
1977 IF cur_get_awd_yr%ISOPEN THEN
1978 CLOSE cur_get_awd_yr;
1979 END IF;
1980
1981 WHEN OTHERS THEN
1982
1983 IF cur_get_awd_yr%ISOPEN THEN
1984 CLOSE cur_get_awd_yr;
1985 END IF;
1986 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1987 fnd_message.set_token('NAME','IGF_GR_GEN.GET_DEF_AWD_YEAR'|| ' ' || SQLERRM);
1988 igs_ge_msg_stack.add;
1989 app_exception.raise_exception;
1990
1991 END get_def_awd_year;
1992
1993 --
1994 -- Bug 2613546,2606001
1995 -- sjadhav
1996 -- Oct,22,2002.
1997 --
1998 -- ovrd_coa_exist will check if there are any overridden coa items
1999 -- for a coa group
2000 --
2001
2002 PROCEDURE ovrd_coa_exist( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
2003 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
2004 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
2005 p_exist OUT NOCOPY VARCHAR2
2006 )
2007 IS
2008
2009
2010 --
2011 -- Cursor to fetch the Overidden COA Items
2012 --
2013 CURSOR cur_overide_ld( p_coa_code igf_aw_cit_ld_ovrd_all.coa_code%TYPE,
2014 p_cal_type igf_aw_cit_ld_ovrd_all.ci_cal_type%TYPE,
2015 p_sequence_number igf_aw_cit_ld_ovrd_all.ci_sequence_number%TYPE
2016 )
2017 IS
2018 SELECT
2019 ld_perct
2020 FROM
2021 igf_aw_cit_ld_overide
2022 WHERE
2023 coa_code = p_coa_code AND
2024 ci_cal_type = p_cal_type AND
2025 ci_sequence_number = p_sequence_number;
2026
2027 ln_perct igf_aw_cit_ld_ovrd_all.ld_perct%TYPE;
2028
2029 BEGIN
2030
2031
2032 OPEN cur_overide_ld ( p_coa_code,
2033 p_cal_type,
2034 p_sequence_number );
2035 FETCH cur_overide_ld INTO ln_perct;
2036
2037 IF cur_overide_ld%NOTFOUND THEN
2038 p_exist := 'N';
2039 ELSIF cur_overide_ld%FOUND THEN
2040 p_exist := 'Y';
2041 END IF;
2042
2043 CLOSE cur_overide_ld;
2044
2045 EXCEPTION
2046 WHEN OTHERS THEN
2047
2048 IF cur_overide_ld%ISOPEN THEN
2049 CLOSE cur_overide_ld;
2050 END IF;
2051 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2052 fnd_message.set_token('NAME','IGF_GR_GEN.OVRD_COA_EXIST'|| ' ' || SQLERRM);
2053 igs_ge_msg_stack.add;
2054 app_exception.raise_exception;
2055
2056
2057 END ovrd_coa_exist;
2058
2059
2060 FUNCTION get_tufees_code(p_base_id IN igf_gr_rfms_all.base_id%TYPE,
2061 p_cal_type IN igf_gr_rfms_all.ci_cal_type%TYPE,
2062 p_sequence_number IN igf_gr_rfms_all.ci_sequence_number%TYPE)
2063 RETURN VARCHAR2
2064 IS
2065
2066 --
2067 -- First check if the award is a regular or alternate pell
2068 -- award
2069 --
2070
2071 CURSOR cur_get_award(
2072 p_base_id igf_gr_rfms_all.base_id%TYPE
2073 ) IS
2074 SELECT alt_pell_schedule
2075 FROM igf_aw_award_all awd,
2076 igf_aw_fund_mast_all fmast,
2077 igf_aw_fund_cat_all fcat
2078 WHERE awd.base_id = p_base_id
2079 AND awd.fund_id = fmast.fund_id
2080 AND awd.award_status IN ('ACCEPTED','OFFERED')
2081 AND fmast.fund_code = fcat.fund_code
2082 AND fcat.fed_fund_code = 'PELL';
2083
2084
2085 --
2086 -- Function to return loa tuition fees code
2087 --
2088 CURSOR cur_get_alt (p_base_id igf_gr_rfms_all.base_id%TYPE)
2089 IS
2090 SELECT
2091 pell_alt_expense
2092 FROM
2093 igf_ap_fa_base_rec
2094 WHERE
2095 base_id = p_base_id;
2096
2097 get_alt_rec cur_get_alt%ROWTYPE;
2098
2099 CURSOR cur_tufees_code (p_exp igf_ap_fa_base_rec_all.pell_alt_expense%TYPE,
2100 p_cal_type igf_gr_rfms_all.ci_cal_type%TYPE,
2101 p_sequence_number igf_gr_rfms_all.ci_sequence_number%TYPE)
2102 IS
2103 SELECT ltfees.lt_fees_code
2104 FROM igf_gr_tuition_fee_codes ltfees,
2105 igf_ap_batch_aw_map_all batch
2106 WHERE p_exp BETWEEN ltfees.min_range_amt AND ltfees.max_range_amt
2107 AND batch.ci_cal_type = p_cal_type
2108 AND batch.ci_sequence_number = p_sequence_number
2109 AND batch.sys_award_year = ltfees.sys_awd_yr;
2110
2111 tufees_code_rec cur_tufees_code%ROWTYPE;
2112
2113 lv_fees_code igf_gr_tuition_fee_codes.lt_fees_code%TYPE;
2114
2115 BEGIN
2116
2117 OPEN cur_get_award (p_base_id);
2118 FETCH cur_get_award INTO lv_fees_code;
2119 CLOSE cur_get_award;
2120
2121 IF NVL(lv_fees_code,'N') = 'A' THEN
2122
2123 OPEN cur_get_alt(p_base_id);
2124 FETCH cur_get_alt INTO get_alt_rec;
2125 CLOSE cur_get_alt;
2126
2127 OPEN cur_tufees_code(get_alt_rec.pell_alt_expense,
2128 p_cal_type,
2129 p_sequence_number);
2130 FETCH cur_tufees_code INTO tufees_code_rec;
2131 CLOSE cur_tufees_code;
2132
2133 lv_fees_code := tufees_code_rec.lt_fees_code;
2134
2135 END IF;
2136
2137 RETURN lv_fees_code;
2138
2139 EXCEPTION
2140 WHEN OTHERS THEN
2141
2142 IF cur_get_alt%ISOPEN THEN
2143 CLOSE cur_get_alt;
2144 END IF;
2145 IF cur_tufees_code%ISOPEN THEN
2146 CLOSE cur_tufees_code;
2147 END IF;
2148
2149 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2150 fnd_message.set_token('NAME','IGF_GR_GEN.GET_TUFEES_CODE'|| ' ' || SQLERRM);
2151 igs_ge_msg_stack.add;
2152 app_exception.raise_exception;
2153
2154 END get_tufees_code;
2155
2156 PROCEDURE insert_coa_items( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
2157 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
2158 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
2159 p_item_code IN igf_aw_coa_grp_item_all.item_code%TYPE,
2160 p_count OUT NOCOPY NUMBER
2161 )
2162
2163 IS
2164 --
2165 -- Cursor to get default load periods
2166 --
2167
2168 CURSOR cur_default_ld(p_coa_code IN VARCHAR2,
2169 p_cal_type IN VARCHAR2,
2170 p_sequence_number IN NUMBER
2171 )
2172 IS
2173 SELECT
2174 ld_cal_type,
2175 ld_sequence_number,
2176 ld_perct,
2177 ci_cal_type,
2178 ci_sequence_number
2179 FROM
2180 igf_aw_coa_ld
2181 WHERE
2182 coa_code = p_coa_code AND
2183 ci_cal_type = p_cal_type AND
2184 ci_sequence_number = p_sequence_number;
2185
2186 --
2187 -- Cursor to get default load periods
2188 --
2189
2190 CURSOR cur_overide (p_coa_code VARCHAR2,
2191 p_item_code VARCHAR2,
2192 p_cal_type VARCHAR2,
2193 p_sequence_number NUMBER,
2194 p_ld_cal_type VARCHAR2,
2195 p_ld_sequence_number NUMBER
2196 )
2197 IS
2198 SELECT
2199 cldo_id
2200 FROM
2201 igf_aw_cit_ld_overide
2202 WHERE
2203 coa_code = p_coa_code AND
2204 ci_cal_type = p_cal_type AND
2205 ci_sequence_number = p_sequence_number AND
2206 item_code = p_item_code AND
2207 ld_cal_type = p_ld_cal_type AND
2208 ld_sequence_number = p_ld_sequence_number;
2209
2210 overide_rec cur_overide%ROWTYPE;
2211 default_ld_rec cur_default_ld%ROWTYPE;
2212 l_cldo_id igf_aw_cit_ld_ovrd_all.cldo_id%TYPE;
2213 lv_ld_rowid ROWID;
2214 ln_count NUMBER(10);
2215
2216 BEGIN
2217
2218 FOR default_ld_rec IN cur_default_ld (p_coa_code,
2219 p_cal_type,
2220 p_sequence_number)
2221 LOOP
2222
2223 ln_count := ln_count + 1;
2224 lv_ld_rowid := NULL;
2225 l_cldo_id := NULL;
2226
2227 OPEN cur_overide(p_coa_code,
2228 p_item_code,
2229 default_ld_rec.ci_cal_type,
2230 default_ld_rec.ci_sequence_number,
2231 default_ld_rec.ld_cal_type,
2232 default_ld_rec.ld_sequence_number
2233 );
2234
2235 FETCH cur_overide INTO overide_rec;
2236
2237 IF cur_overide%NOTFOUND THEN
2238
2239 igf_aw_cit_ld_ovrd_pkg.insert_row (
2240 x_mode => 'R',
2241 x_rowid => lv_ld_rowid,
2242 x_cldo_id => l_cldo_id,
2243 x_coa_code => p_coa_code,
2244 x_ci_cal_type => p_cal_type,
2245 x_ci_sequence_number => p_sequence_number,
2246 x_item_code => p_item_code,
2247 x_ld_cal_type => default_ld_rec.ld_cal_type,
2248 x_ld_sequence_number => default_ld_rec.ld_sequence_number,
2249 x_ld_perct => default_ld_rec.ld_perct
2250 );
2251 END IF;
2252 CLOSE cur_overide;
2253
2254 END LOOP;
2255
2256 p_count := ln_count;
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260
2261 IF cur_default_ld%ISOPEN THEN
2262 CLOSE cur_default_ld;
2263 END IF;
2264
2265 IF cur_overide%ISOPEN THEN
2266 CLOSE cur_overide;
2267 END IF;
2268
2269 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2270 fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_COA_ITEMS'|| ' ' || SQLERRM);
2271 igs_ge_msg_stack.add;
2272 app_exception.raise_exception;
2273
2274 END insert_coa_items;
2275
2276 PROCEDURE insert_coa_terms( p_coa_code IN igf_aw_coa_group_all.coa_code%TYPE,
2277 p_cal_type IN igf_aw_coa_group_all.ci_cal_type%TYPE,
2278 p_sequence_number IN igf_aw_coa_group_all.ci_sequence_number%TYPE,
2279 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
2280 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE
2281 )
2282
2283 IS
2284
2285 CURSOR cur_get_items (p_coa_code igf_aw_coa_group_all.coa_code%TYPE,
2286 p_cal_type igf_aw_coa_group_all.ci_cal_type%TYPE,
2287 p_sequence_number igf_aw_coa_group_all.ci_sequence_number%TYPE)
2288 IS
2289 SELECT
2290 DISTINCT item_code
2291 FROM
2292 igf_aw_cit_ld_overide
2293 WHERE
2294 coa_code = p_coa_code AND
2295 ci_cal_type = p_cal_type AND
2296 ci_sequence_number = p_sequence_number;
2297
2298 get_items_rec cur_get_items%ROWTYPE;
2299
2300
2301 l_cldo_id igf_aw_cit_ld_ovrd_all.cldo_id%TYPE;
2302 lv_ld_rowid ROWID;
2303
2304 BEGIN
2305
2306 FOR get_items_rec IN cur_get_items(p_coa_code,
2307 p_cal_type,
2308 p_sequence_number)
2309 LOOP
2310
2311 lv_ld_rowid := NULL;
2312 l_cldo_id := NULL;
2313
2314 igf_aw_cit_ld_ovrd_pkg.insert_row (
2315 x_mode => 'R',
2316 x_rowid => lv_ld_rowid,
2317 x_cldo_id => l_cldo_id,
2318 x_coa_code => p_coa_code,
2319 x_ci_cal_type => p_cal_type,
2320 x_ci_sequence_number => p_sequence_number,
2321 x_item_code => get_items_rec.item_code,
2322 x_ld_cal_type => p_ld_cal_type,
2323 x_ld_sequence_number => p_ld_sequence_number,
2324 x_ld_perct => 0
2325 );
2326
2327 END LOOP;
2328
2329
2330 EXCEPTION
2331 WHEN OTHERS THEN
2332
2333 IF cur_get_items%ISOPEN THEN
2334 CLOSE cur_get_items;
2335 END IF;
2336
2337 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2338 fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_COA_TERMS'|| ' ' || SQLERRM);
2339 igs_ge_msg_stack.add;
2340 app_exception.raise_exception;
2341
2342 END insert_coa_terms;
2343
2344
2345 PROCEDURE insert_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
2346 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
2347 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
2348 p_result OUT NOCOPY VARCHAR2
2349 )
2350 IS
2351
2352 CURSOR cur_get_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE)
2353 IS
2354 SELECT item_code
2355 FROM igf_aw_coa_items
2356 WHERE base_id = p_base_id;
2357
2358 lv_rowid ROWID;
2359
2360 BEGIN
2361
2362 p_result := 'S';
2363
2364 FOR get_terms_rec IN cur_get_terms (p_base_id) LOOP
2365
2366 lv_rowid := NULL;
2367
2368
2369 igf_aw_coa_itm_terms_pkg.insert_row( x_rowid => lv_rowid,
2370 x_base_id => p_base_id,
2371 x_item_code => get_terms_rec.item_code,
2372 x_amount => 0,
2373 x_ld_cal_type => p_ld_cal_type,
2374 x_ld_sequence_number => p_ld_sequence_number,
2375 x_mode => 'R',
2376 x_lock_flag => 'N'
2377 );
2378 END LOOP;
2379
2380 EXCEPTION
2381
2382 WHEN OTHERS THEN
2383 p_result := 'F';
2384 IF cur_get_terms%ISOPEN THEN
2385 CLOSE cur_get_terms;
2386 END IF;
2387
2388 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2389 fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_STU_COA_TERMS'|| ' ' || SQLERRM);
2390 igs_ge_msg_stack.add;
2391 app_exception.raise_exception;
2392
2393 END insert_stu_coa_terms;
2394
2395 --CODE ADDED BY GAUTAM
2396
2397 PROCEDURE insert_existing_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
2398 p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
2399 p_result OUT NOCOPY VARCHAR2
2400 )
2401 IS
2402
2403 CURSOR cur_get_existing_terms(c_base_id igf_aw_coa_itm_terms.base_id%TYPE)
2404 IS
2405 SELECT DISTINCT ld_cal_type,ld_sequence_number
2406 FROM igf_aw_coa_itm_terms
2407 WHERE base_id = c_base_id;
2408
2409 get_exisiting_terms_rec cur_get_existing_terms%ROWTYPE;
2410 lv_rowid ROWID;
2411
2412 BEGIN
2413
2414 p_result := 'S';
2415
2416 FOR get_existing_terms_rec IN cur_get_existing_terms(p_base_id) LOOP
2417
2418 lv_rowid := NULL;
2419
2420 igf_aw_coa_itm_terms_pkg.insert_row( x_rowid => lv_rowid,
2421 x_base_id => p_base_id,
2422 x_item_code => p_item_code,
2423 x_amount => 0,
2424 x_ld_cal_type => get_existing_terms_rec.ld_cal_type,
2425 x_ld_sequence_number => get_existing_terms_rec.ld_sequence_number,
2426 x_mode => 'R',
2427 x_lock_flag => 'N'
2428 );
2429 END LOOP;
2430
2431 EXCEPTION
2432
2433 WHEN OTHERS THEN
2434 p_result := 'F';
2435 IF cur_get_existing_terms%ISOPEN THEN
2436 CLOSE cur_get_existing_terms;
2437 END IF;
2438
2439 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2440 fnd_message.set_token('NAME','IGF_GR_GEN.INSERT_EXISTING_TERMS'|| ' ' || SQLERRM);
2441 igs_ge_msg_stack.add;
2442 app_exception.raise_exception;
2443
2444 END insert_existing_terms;
2445
2446 PROCEDURE delete_stu_coa_terms( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
2447 p_ld_cal_type IN igf_aw_coa_ld_all.ld_cal_type%TYPE,
2448 p_ld_sequence_number IN igf_aw_coa_ld_all.ld_sequence_number%TYPE,
2449 p_result OUT NOCOPY VARCHAR2
2450 )
2451
2452 IS
2453
2454
2455 CURSOR cur_get_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
2456 p_ld_cal_type igf_aw_coa_ld_all.ld_cal_type%TYPE,
2457 p_ld_sequence_number igf_aw_coa_ld_all.ld_sequence_number%TYPE
2458 )
2459 IS
2460 SELECT rowid
2461 FROM igf_aw_coa_itm_terms
2462 WHERE base_id = p_base_id
2463 AND ld_cal_type = p_ld_cal_type
2464 AND ld_sequence_number = p_ld_sequence_number;
2465
2466
2467 BEGIN
2468
2469 p_result := 'S';
2470
2471 FOR get_terms_rec IN cur_get_terms (p_base_id,
2472 p_ld_cal_type,
2473 p_ld_sequence_number)
2474 LOOP
2475 igf_aw_coa_itm_terms_pkg.delete_row( x_rowid => get_terms_rec.rowid);
2476 END LOOP;
2477
2478 EXCEPTION
2479
2480 WHEN OTHERS THEN
2481 p_result := 'F';
2482 IF cur_get_terms%ISOPEN THEN
2483 CLOSE cur_get_terms;
2484 END IF;
2485
2486 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2487 fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_STU_COA_TERMS'|| ' ' || SQLERRM);
2488 igs_ge_msg_stack.add;
2489 app_exception.raise_exception;
2490
2491 END delete_stu_coa_terms;
2492
2493
2494 PROCEDURE delete_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
2495 p_result OUT NOCOPY VARCHAR2,
2496 p_item_code IN igf_aw_coa_items.item_code%TYPE
2497 )
2498 IS
2499
2500 CURSOR cur_get_coa_itms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
2501 p_item_code igf_aw_coa_items.item_code%TYPE)
2502 IS
2503 SELECT item_code,rowid
2504 FROM igf_aw_coa_items
2505 WHERE base_id = p_base_id
2506 AND item_code = NVL(p_item_code,item_code);
2507
2508
2509 CURSOR cur_get_coa_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
2510 p_item_code igf_aw_coa_itm_terms.item_code%TYPE)
2511
2512 IS
2513 SELECT rowid
2514 FROM igf_aw_coa_itm_terms
2515 WHERE base_id = p_base_id
2516 AND item_code = p_item_code;
2517
2518 BEGIN
2519
2520 p_result := 'S';
2521
2522 FOR get_coa_itms_rec IN cur_get_coa_itms (p_base_id,
2523 p_item_code)
2524 LOOP
2525 FOR get_coa_terms_rec IN cur_get_coa_terms (p_base_id,
2526 get_coa_itms_rec.item_code)
2527 LOOP
2528 igf_aw_coa_itm_terms_pkg.delete_row(x_rowid => get_coa_terms_rec.rowid);
2529 END LOOP;
2530 -- igf_aw_coa_items_pkg.delete_row(x_rowid => get_coa_itms_rec.rowid);
2531 END LOOP;
2532
2533 EXCEPTION
2534 WHEN OTHERS THEN
2535 p_result := 'F';
2536 IF cur_get_coa_itms%ISOPEN THEN
2537 CLOSE cur_get_coa_itms;
2538 END IF;
2539 IF cur_get_coa_terms%ISOPEN THEN
2540 CLOSE cur_get_coa_terms;
2541 END IF;
2542
2543 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2544 fnd_message.set_token('NAME','IGF_GR_GEN.DELETE_STU_COA_ITEMS'|| ' ' || SQLERRM);
2545 igs_ge_msg_stack.add;
2546 app_exception.raise_exception;
2547
2548 END delete_stu_coa_items;
2549
2550 PROCEDURE update_stu_coa_items( p_base_id IN igf_aw_coa_itm_terms.base_id%TYPE,
2551 p_item_code IN igf_aw_coa_itm_terms.item_code%TYPE,
2552 p_result OUT NOCOPY VARCHAR2)
2553 IS
2554
2555 CURSOR cur_get_items (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
2556 p_item_code igf_aw_coa_itm_terms.item_code%TYPE)
2557 IS
2558 SELECT items.rowid,items.*
2559 FROM
2560 igf_aw_coa_items items
2561 WHERE base_id = p_base_id
2562 AND item_code = NVL(p_item_code,item_code);
2563
2564 CURSOR cur_get_terms (p_base_id igf_aw_coa_itm_terms.base_id%TYPE,
2565 p_item_code igf_aw_coa_items.item_code%TYPE)
2566 IS
2567 SELECT
2568 SUM (amount) item_amount
2569 FROM igf_aw_coa_itm_terms
2570 WHERE base_id = p_base_id
2571 AND item_code = p_item_code
2572 GROUP BY item_code;
2573
2574 BEGIN
2575
2576 p_result := 'S';
2577
2578 FOR get_items_rec IN cur_get_items ( p_base_id,
2579 p_item_code)
2580 LOOP
2581 FOR get_terms_rec IN cur_get_terms ( p_base_id,
2582 get_items_rec.item_code)
2583 LOOP
2584 igf_aw_coa_items_pkg.update_row (x_rowid => get_items_rec.rowid,
2585 x_base_id => p_base_id,
2586 x_item_code => get_items_rec.item_code,
2587 x_amount => get_terms_rec.item_amount,
2588 x_pell_coa_amount => get_items_rec.pell_coa_amount,
2589 x_alt_pell_amount => get_items_rec.alt_pell_amount,
2590 x_fixed_cost => get_items_rec.fixed_cost,
2591 x_mode => 'R',
2592 x_lock_flag => get_items_rec.lock_flag
2593 );
2594
2595
2596 END LOOP;
2597
2598 END LOOP;
2599
2600
2601 EXCEPTION
2602
2603 WHEN OTHERS THEN
2604 p_result := 'F';
2605
2606 IF cur_get_items%ISOPEN THEN
2607 CLOSE cur_get_items;
2608 END IF;
2609 IF cur_get_terms%ISOPEN THEN
2610 CLOSE cur_get_terms;
2611 END IF;
2612
2613 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2614 fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_STU_COA_ITEMS'|| ' ' || SQLERRM);
2615 igs_ge_msg_stack.add;
2616 app_exception.raise_exception;
2617
2618 END update_stu_coa_items;
2619
2620
2621
2622 FUNCTION get_pell_code(p_att_code IN igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2623 p_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
2624 p_sequence_number IN igf_ap_fa_base_rec.ci_sequence_number%TYPE)
2625 RETURN VARCHAR2
2626 /*
2627 || Created By :
2628 || Created On :
2629 || Purpose :
2630 || Known limitations, enhancements or remarks :
2631 || Change History :
2632 || Who When What
2633 || rasahoo 01-Sep-2003 Replaced igf_ap_fa_base_h_all.derived_attend_type%TYPE
2634 || with igs_en_stdnt_ps_att_all.derived_att_type%TYPE
2635 || as part of the build FA-114 (Obsoletion of FA base record History)
2636 || (reverse chronological order - newest change first)
2637 */
2638 IS
2639
2640 CURSOR cur_get_pell (p_att_code igs_en_stdnt_ps_att_all.derived_att_type%TYPE,
2641 p_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE,
2642 p_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE
2643 )
2644 IS
2645 SELECT pell_att_code
2646 FROM igf_ap_attend_map
2647 WHERE attendance_type = p_att_code
2648 AND cal_type = p_cal_type
2649 AND sequence_number = p_sequence_number;
2650
2651 get_pell_rec cur_get_pell%ROWTYPE;
2652
2653 BEGIN
2654 IF p_att_code IS NULL THEN
2655 RETURN '5';
2656 ELSE
2657 OPEN cur_get_pell(p_att_code,
2658 p_cal_type,
2659 p_sequence_number);
2660 FETCH cur_get_pell INTO get_pell_rec;
2661 CLOSE cur_get_pell;
2662
2663 RETURN get_pell_rec.pell_att_code;
2664 END IF;
2665 END get_pell_code;
2666
2667
2668 --
2669 -- Bug 2590991
2670 -- sjadhav
2671 -- Nov,18,2002.
2672 --
2673 -- Routine to fetch base id
2674 --
2675
2676 PROCEDURE get_base_id(p_cal_type IN igs_ca_inst_all.cal_type%TYPE,
2677 p_sequence_number IN igs_ca_inst_all.sequence_number%TYPE,
2678 p_person_id IN igf_ap_fa_base_rec_all.person_id%TYPE,
2679 p_base_id OUT NOCOPY igf_ap_fa_base_rec_all.base_id%TYPE,
2680 p_err_msg OUT NOCOPY VARCHAR2
2681 )
2682 IS
2683
2684
2685 --
2686 -- Cursor to get base id
2687 --
2688
2689 CURSOR cur_get_base (p_cal_type igs_ca_inst_all.cal_type%TYPE,
2690 p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
2691 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE)
2692 IS
2693 SELECT
2694 base_id
2695 FROM
2696 igf_ap_fa_base_rec
2697 WHERE
2698 person_id = p_person_id AND
2699 ci_cal_type = p_cal_type AND
2700 ci_sequence_number = p_sequence_number;
2701
2702
2703 BEGIN
2704
2705 p_err_msg := 'NULL';
2706 OPEN cur_get_base( p_cal_type,
2707 p_sequence_number,
2708 p_person_id);
2709 FETCH cur_get_base INTO p_base_id;
2710
2711 IF cur_get_base%NOTFOUND THEN
2712 p_err_msg := 'IGF_AP_NO_FA_APPL_MSG';
2713 END IF;
2714
2715 CLOSE cur_get_base;
2716
2717 EXCEPTION
2718
2719 WHEN NO_DATA_FOUND THEN
2720 IF cur_get_base%ISOPEN THEN
2721 CLOSE cur_get_base;
2722 END IF;
2723
2724 WHEN OTHERS THEN
2725
2726 IF cur_get_base%ISOPEN THEN
2727 CLOSE cur_get_base;
2728 END IF;
2729 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2730 fnd_message.set_token('NAME','IGF_GR_GEN.GET_BASE_ID'|| ' ' || SQLERRM);
2731 igs_ge_msg_stack.add;
2732 app_exception.raise_exception;
2733
2734 END get_base_id;
2735
2736 PROCEDURE update_current_ssn (p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
2737 p_cur_ssn IN igf_ap_isir_matched_all.current_ssn%TYPE,
2738 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE)
2739 IS
2740 --
2741 -- sjadhav,2/4/03
2742 -- FA116 Build - Bug 2758812 - 2/4/03
2743 -- update_current_ssn
2744 -- updates current ssn with the new value from active isir
2745 --
2746
2747 CURSOR cur_get_pell (p_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
2748 IS
2749 SELECT pell.*
2750 FROM
2751 igf_gr_rfms pell
2752 WHERE
2753 base_id = p_base_id
2754 FOR UPDATE OF current_ssn;
2755
2756 get_pell_rec cur_get_pell%ROWTYPE;
2757
2758 BEGIN
2759
2760 p_message := 'NULL';
2761 OPEN cur_get_pell(p_base_id);
2762 FETCH cur_get_pell INTO get_pell_rec;
2763
2764 IF cur_get_pell%FOUND THEN
2765 -- IF get_pell_rec.current_ssn <> p_cur_ssn THEN
2766 IF NVL(get_pell_rec.current_ssn, -1) <> NVL(p_cur_ssn, -1) THEN
2767
2768 IF get_pell_rec.orig_action_code <>'S' THEN
2769
2770 get_pell_rec.current_ssn := get_ssn_digits(p_cur_ssn);
2771 get_pell_rec.rfmb_id := NULL;
2772 get_pell_rec.orig_action_code := 'R';
2773
2774 igf_gr_rfms_pkg.update_row(
2775 x_rowid => get_pell_rec.row_id,
2776 x_origination_id => get_pell_rec.origination_id,
2777 x_ci_cal_type => get_pell_rec.ci_cal_type,
2778 x_ci_sequence_number => get_pell_rec.ci_sequence_number,
2779 x_base_id => get_pell_rec.base_id,
2780 x_award_id => get_pell_rec.award_id,
2781 x_rfmb_id => get_pell_rec.rfmb_id,
2782 x_sys_orig_ssn => get_pell_rec.sys_orig_ssn,
2783 x_sys_orig_name_cd => get_pell_rec.sys_orig_name_cd,
2784 x_transaction_num => get_pell_rec.transaction_num,
2785 x_efc => get_pell_rec.efc,
2786 x_ver_status_code => get_pell_rec.ver_status_code,
2787 x_secondary_efc => get_pell_rec.secondary_efc,
2788 x_secondary_efc_cd => get_pell_rec.secondary_efc_cd,
2789 x_pell_amount => get_pell_rec.pell_amount,
2790 x_pell_profile => get_pell_rec.pell_profile,
2791 x_enrollment_status => get_pell_rec.enrollment_status,
2792 x_enrollment_dt => get_pell_rec.enrollment_dt,
2793 x_coa_amount => get_pell_rec.coa_amount,
2794 x_academic_calendar => get_pell_rec.academic_calendar,
2795 x_payment_method => get_pell_rec.payment_method,
2796 x_total_pymt_prds => get_pell_rec.total_pymt_prds,
2797 x_incrcd_fed_pell_rcp_cd => get_pell_rec.incrcd_fed_pell_rcp_cd,
2798 x_attending_campus_id => get_pell_rec.attending_campus_id,
2799 x_est_disb_dt1 => get_pell_rec.est_disb_dt1,
2800 x_orig_action_code => get_pell_rec.orig_action_code,
2801 x_orig_status_dt => get_pell_rec.orig_status_dt,
2802 x_orig_ed_use_flags => get_pell_rec.orig_ed_use_flags,
2803 x_ft_pell_amount => get_pell_rec.ft_pell_amount,
2804 x_prev_accpt_efc => get_pell_rec.prev_accpt_efc,
2805 x_prev_accpt_tran_no => get_pell_rec.prev_accpt_tran_no,
2806 x_prev_accpt_sec_efc_cd => get_pell_rec.prev_accpt_sec_efc_cd,
2807 x_prev_accpt_coa => get_pell_rec.prev_accpt_coa,
2808 x_orig_reject_code => get_pell_rec.orig_reject_code,
2809 x_wk_inst_time_calc_pymt => get_pell_rec.wk_inst_time_calc_pymt,
2810 x_wk_int_time_prg_def_yr => get_pell_rec.wk_int_time_prg_def_yr,
2811 x_cr_clk_hrs_prds_sch_yr => get_pell_rec.cr_clk_hrs_prds_sch_yr,
2812 x_cr_clk_hrs_acad_yr => get_pell_rec.cr_clk_hrs_acad_yr,
2813 x_inst_cross_ref_cd => get_pell_rec.inst_cross_ref_cd,
2814 x_low_tution_fee => get_pell_rec.low_tution_fee,
2815 x_rec_source => get_pell_rec.rec_source,
2816 x_pending_amount => get_pell_rec.pending_amount,
2817 x_mode => 'R',
2818 x_birth_dt => get_pell_rec.birth_dt,
2819 x_last_name => get_pell_rec.last_name,
2820 x_first_name => get_pell_rec.first_name,
2821 x_middle_name => get_pell_rec.middle_name,
2822 x_current_ssn => get_pell_rec.current_ssn,
2823 x_legacy_record_flag => NULL,
2824 x_reporting_pell_cd => get_pell_rec.rep_pell_id,
2825 x_rep_entity_id_txt => get_pell_rec.rep_entity_id_txt,
2826 x_atd_entity_id_txt => get_pell_rec.atd_entity_id_txt,
2827 x_note_message => get_pell_rec.note_message,
2828 x_full_resp_code => get_pell_rec.full_resp_code,
2829 x_document_id_txt => get_pell_rec.document_id_txt
2830
2831 );
2832
2833 ELSE
2834 p_message := 'IGF_GR_UPDT_SSN_FAIL';
2835 END IF;
2836 END IF;
2837 END IF;
2838
2839 CLOSE cur_get_pell;
2840
2841 EXCEPTION
2842
2843 WHEN NO_DATA_FOUND THEN
2844 IF cur_get_pell%ISOPEN THEN
2845 CLOSE cur_get_pell;
2846 END IF;
2847
2848 WHEN app_exception.record_lock_exception THEN
2849 IF cur_get_pell%ISOPEN THEN
2850 CLOSE cur_get_pell;
2851 END IF;
2852 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
2853 fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_CURRENT_SSN');
2854 igs_ge_msg_stack.add;
2855 app_exception.raise_exception;
2856
2857 WHEN OTHERS THEN
2858 IF cur_get_pell%ISOPEN THEN
2859 CLOSE cur_get_pell;
2860 END IF;
2861 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2862 fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_CURRENT_SSN'|| ' ' || SQLERRM);
2863 igs_ge_msg_stack.add;
2864 app_exception.raise_exception;
2865
2866 END update_current_ssn;
2867
2868 PROCEDURE update_pell_status (p_award_id IN igf_aw_award_all.award_id%TYPE,
2869 p_fed_fund_code IN igf_aw_fund_cat_all.fed_fund_code%TYPE,
2870 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE,
2871 p_status_desc OUT NOCOPY igf_lookups_view.meaning%TYPE)
2872 IS
2873
2874 --
2875 -- sjadhav,2/4/03
2876 -- FA116 Build - Bug 2758812 - 2/4/03
2877 -- update_pell_status
2878 -- sets pell origination status desc
2879 --
2880
2881 CURSOR cur_get_pell (p_award_id igf_gr_rfms_all.award_id%TYPE)
2882 IS
2883 SELECT pell.orig_action_code
2884 FROM
2885 igf_gr_rfms pell
2886 WHERE
2887 award_id = p_award_id;
2888
2889
2890 get_pell_rec cur_get_pell%ROWTYPE;
2891
2892 BEGIN
2893
2894 p_message := 'NULL';
2895 p_status_desc := 'NULL';
2896
2897 IF p_fed_fund_code = 'PELL' THEN
2898
2899 OPEN cur_get_pell(p_award_id);
2900 FETCH cur_get_pell INTO get_pell_rec;
2901
2902 IF cur_get_pell%FOUND THEN
2903 IF get_pell_rec.orig_action_code IN ('A','C','D','E') THEN
2904 p_message := 'IGF_GR_ORIG_STAT_CHG';
2905 p_status_desc := igf_aw_gen.lookup_desc('IGF_GR_ORIG_STATUS',
2906 get_pell_rec.orig_action_code);
2907 END IF;
2908 END IF;
2909 CLOSE cur_get_pell;
2910
2911 END IF;
2912
2913 EXCEPTION
2914
2915 WHEN NO_DATA_FOUND THEN
2916 IF cur_get_pell%ISOPEN THEN
2917 CLOSE cur_get_pell;
2918 END IF;
2919
2920 WHEN OTHERS THEN
2921 IF cur_get_pell%ISOPEN THEN
2922 CLOSE cur_get_pell;
2923 END IF;
2924 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2925 fnd_message.set_token('NAME','IGF_GR_GEN.UPDATE_PELL_STATUS'|| ' ' || SQLERRM);
2926 igs_ge_msg_stack.add;
2927 app_exception.raise_exception;
2928
2929 END update_pell_status;
2930
2931 PROCEDURE match_file_version (p_version IN igf_lookups_view.lookup_code%TYPE,
2932 p_batch_id IN igf_gr_rfms_batch_all.batch_id%TYPE,
2933 p_message OUT NOCOPY fnd_new_messages.message_name%TYPE)
2934 IS
2935
2936 --
2937 -- sjadhav,2/4/03
2938 -- FA116 Build - Bug 2758812 - 2/4/03
2939 -- match_file_version
2940 -- compares cycle year from pell version and
2941 -- batch id
2942 --
2943
2944 BEGIN
2945
2946 p_message := 'NULL';
2947 IF SUBSTR(p_version,-4,4) <> SUBSTR(p_batch_id,3,4) THEN
2948 p_message := 'IGF_GR_VRSN_MISMTCH';
2949 END IF;
2950
2951 END match_file_version;
2952
2953
2954
2955 FUNCTION get_min_disb_number (p_award_id igf_aw_award_all.award_id%TYPE)
2956 RETURN NUMBER
2957 IS
2958 --
2959 --
2960 --
2961 CURSOR cur_min_disb (p_award_id igf_aw_award_all.award_id%TYPE)
2962 IS
2963 SELECT
2964 MIN(disb_num)
2965 FROM
2966 igf_aw_awd_disb
2967 WHERE
2968 award_id = p_award_id;
2969
2970 ln_min_num igf_aw_awd_disb_all.disb_num%TYPE;
2971
2972 BEGIN
2973
2974 OPEN cur_min_disb ( p_award_id );
2975 FETCH cur_min_disb INTO ln_min_num;
2976 CLOSE cur_min_disb ;
2977
2978 RETURN ln_min_num;
2979
2980 EXCEPTION
2981
2982 WHEN OTHERS THEN
2983
2984 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2985 fnd_message.set_token('NAME','IGF_GR_GEN.GET_MIN_DISB_NUMBER'|| ' ' || SQLERRM);
2986 igs_ge_msg_stack.add;
2987 app_exception.raise_exception;
2988
2989 END get_min_disb_number;
2990
2991
2992 FUNCTION get_rep_pell_from_ope(p_cal_type igs_ca_inst_all.cal_type%TYPE,
2993 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
2994 p_ope_cd igf_gr_report_pell.ope_cd%TYPE)
2995 RETURN VARCHAR2
2996 AS
2997
2998 CURSOR c_get_rep_pell_from_ope(cp_cal_type VARCHAR2,cp_seq_num NUMBER,cp_ope_cd VARCHAR2)
2999 IS
3000 SELECT grp.reporting_pell_cd
3001 FROM
3002 igf_gr_report_pell grp
3003 WHERE
3004 grp.ci_cal_type = cp_cal_type AND
3005 grp.ci_sequence_number = cp_seq_num AND
3006 grp.ope_cd = cp_ope_cd;
3007
3008 l_rep_pell VARCHAR2(30):=NULL;
3009
3010 BEGIN
3011
3012 OPEN c_get_rep_pell_from_ope(p_cal_type,p_seq_num,p_ope_cd);
3013 FETCH c_get_rep_pell_from_ope INTO l_rep_pell;
3014 IF (c_get_rep_pell_from_ope%NOTFOUND)
3015 THEN
3016 CLOSE c_get_rep_pell_from_ope;
3017 RETURN null;
3018 ELSE
3019 CLOSE c_get_rep_pell_from_ope;
3020 RETURN l_rep_pell;
3021 END IF;
3022
3023 END get_rep_pell_from_ope;
3024
3025 FUNCTION get_rep_pell_from_att(p_cal_type igs_ca_inst_all.cal_type%TYPE,
3026 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
3027 p_att_pell igf_gr_attend_pell.attending_pell_cd%TYPE)
3028 RETURN VARCHAR2
3029 AS
3030
3031 CURSOR c_get_rep_pell_from_att(cp_cal_type VARCHAR2,cp_seq_num NUMBER,cp_att_pell VARCHAR2)
3032 IS
3033 SELECT grp.reporting_pell_cd
3034 FROM
3035 igf_gr_attend_pell gap,
3036 igf_gr_report_pell grp
3037 WHERE
3038 gap.ci_cal_type = cp_cal_type AND
3039 gap.ci_sequence_number = cp_seq_num AND
3040 gap.attending_pell_cd = cp_att_pell AND
3041 gap.rcampus_id = grp.rcampus_id;
3042
3043 l_rep_pell VARCHAR2(30);
3044
3045 BEGIN
3046
3047 OPEN c_get_rep_pell_from_att(p_cal_type,p_seq_num,p_att_pell);
3048 FETCH c_get_rep_pell_from_att INTO l_rep_pell;
3049 IF (c_get_rep_pell_from_att%NOTFOUND)
3050 THEN
3051 CLOSE c_get_rep_pell_from_att;
3052 RETURN null;
3053 ELSE
3054 CLOSE c_get_rep_pell_from_att;
3055 RETURN l_rep_pell;
3056 END IF;
3057
3058 END get_rep_pell_from_att;
3059
3060 FUNCTION get_rep_pell_from_base(p_cal_type igs_ca_inst_all.cal_type%TYPE,
3061 p_seq_num igs_ca_inst_all.sequence_number%TYPE,
3062 p_base_id NUMBER)
3063 RETURN VARCHAR2
3064 AS
3065
3066 l_office_cd VARCHAR2(30);
3067 l_return_status VARCHAR2(1);
3068 l_msg_data VARCHAR2(30);
3069 l_rep_pell VARCHAR2(30);
3070
3071 BEGIN
3072
3073 igf_sl_gen.get_stu_fao_code(p_base_id,'PELL_ID',l_office_cd,l_return_status,l_msg_data);
3074 IF (l_return_status='E')
3075 THEN
3076 RETURN null;
3077 END IF;
3078 IF ((l_return_status ='S') AND (l_office_cd IS NOT NULL))
3079 THEN
3080 l_rep_pell := get_rep_pell_from_att(p_cal_type,p_seq_num,l_office_cd);
3081 RETURN l_rep_pell;
3082 END IF;
3083
3084 END get_rep_pell_from_base;
3085
3086 END igf_gr_gen;