[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_PELL
Source
1 PACKAGE BODY IGF_GR_PELL AS
2 /* $Header: IGFGR01B.pls 120.0 2005/06/02 15:55:36 appldev noship $ */
3
4 --
5 ------------------------------------------------------------------------
6 -- Who When What
7 ------------------------------------------------------------------------
8 -- sjadhav 25-Oct-2004 FA 149 build bug # 3416863 - added full resp code
9 ------------------------------------------------------------------------
10 -- sjadhav 25-Oct-2004 FA 149 build bug # 3416863 - Person
11 -- information picked from SWS, so rfms columns
12 -- are not used, do not insert rfms_disb for cod
13 ------------------------------------------------------------------------
14 -- ayedubat 13-OCT-2004 Changed the logic as part of FA 149 build bug # 3416863
15 -- bkkumar 23-July-04 Bug 3773300 Added the function to get the enrollment dateas min start date
16 -- of term for the pell award disbursements.
17 -- sjalasut 10 Dec, 2003 removed get_current_term_enr_dtl and added igf_ap_gen_001.get_key_program
18 -- ugummall 09-DEC-2003. Bug 3252832. FA 131 - COD Updates.
19 -- Removed the procedure pell_calc.
20 -- ugummall 20-NOV-2003 Bug 3252832. FA 131 - COD Updates.
21 -- 1. Added two cursors cur_get_attendance_type_code and cur_get_pell_att_code
22 -- in rfms_load_rec procedure.
23 -- 2. Modified code in preparing enrollment_status field of the origination record.
24 -- in rfms_load_rec procedure.
25 ------------------------------------------------------------------------
26 -- sjalasut 7 Nov 2003 FA126. Modified the code to have attending pell
27 -- in the generate_origination_id procedure.
28 -- Pell Origination Records will have Attending Pell
29 -- in the Origination Id field.
30 -- veramach 11-OCT-2003 FA 124
31 -- 1.COA is required for awarding PELL Grants
32 -- 2.Pell Award Amount must be less than or equal to amount calculated from PELL matrix
33 ------------------------------------------------------------------------
34 -- rasahoo 01-Sep-2003 In the cursor C_FA_BASE, removed the join with igf_ap_fa_base_h
35 -- as part of the build FA-114 (obsoletion of base rec history)
36 ------------------------------------------------------------------------
37 -- sjadhav 01-Aug-2003 Bug 3062062
38 -- Removed variable lv_enrollment_status from
39 -- pell_calc routine
40 -- Removed igf_gr_gen.get_pell_efc call from
41 -- pell_calc routine
42 -- Modified pell_calc routine to default
43 -- Enrollment Status to Full time when called
44 -- from IGFGR02B - Pell Origination process
45 ------------------------------------------------------------------------
46 -- sjadhav 08-Apr-2003 Bug 2890177
47 -- Removed NVL from comparisons to decide
48 -- Regular or Alternate Pell Matrix
49 -- in pell_calc routine
50 -- When pell_calc invoked from IGFGR005
51 -- messages are added to stack
52 ------------------------------------------------------------------------
53 -- gmuralid 04-04-2003 BUG 2863895,2863910
54 -- Made the following changes in
55 -- rfms_load_rec_procedure:
56 -- 1.Modified Exception Handling where in
57 -- included the message to skip a student if
58 -- there is no set up or active and payment
59 -- isir are different or there is a duplicate
60 -- ssn.
61 -- In rfms_load procedure the following chnages
62 -- were made:
63 -- 1.Included commit just before exception
64 -- handling to ensure proper write into log
65 -- file.
66 ------------------------------------------------------------------------
67 -- gmuralid 28-Mar-2003 BUG 2863895 - The process used to error
68 -- out when active and payment isirs were
69 -- not the same.Modified code logic such that
70 -- the process continues skipping only that
71 -- particular student.Formatted log file
72 -- by including new messages.
73 ------------------------------------------------------------------------
74 -- gmuralid 28-Mar-2003 BUG 2863910 - Included duplicate SSN cursor
75 -- to check for exisiting origination id.
76 ------------------------------------------------------------------------
77 -- vvutukur 17-Feb-2003 Enh#2758804.FACR105 Build. Modified procedures
78 -- rfms_load_rec and rfms_load.
79 ------------------------------------------------------------------------
80 -- sjadhav 10-Feb-2003 Bug 2758812 - FA116 Pell Build
81 -- Modified generate_origination_id to read
82 -- pell cycle year using function
83 -- igf_gr_gen.get_cycle_year
84 ------------------------------------------------------------------------
85 -- sjadhav FA105 108 Bug 2613546,2606001
86 -- modified pell_calc routine
87 -- modified c_fa_base cursor to read
88 -- pell_alt_expense .added award year to
89 -- get_gr_ver_code fuction call
90 ------------------------------------------------------------------------
91 -- sjadhav Feb 07, 2002 Bug : 2216956
92 -- 1.Changes in the tbh calls
93 -- 2.Pick Current SSN, Last Name,First Name,
94 -- Middle Name from igf_gr_person_v
95 -- 3.Db/Cr Flag set to 'P' in case of positive
96 -- disbursement,else set to 'N'
97 -- 4.Added Exception Handlers in All procedures
98 -- 5.Modified generate_origination_id proc to
99 -- pick up the Cycle Year from the End Date
100 ------------------------------------------------------------------------
101 -- sjadhav Jan 30,2002 Bug : 2154941
102 -- Common cursor c_fa_base to pick up all
103 -- relevent information added
104 ------------------------------------------------------------------------
105 -- sjadhav 24-jul-2001 Bug ID : 1818617 added parameter
106 -- p_get_recent_info
107 ------------------------------------------------------------------------
108 -- avenkatr 06-SEP-2001 Bug Id : 1967738. Added the procedure
109 -- Generate _Origination_Id to take care of
110 -- conditions when ISIR record is not found and
111 -- when any of SSN, Start date of Award Year or
112 -- Reporting Pell Id is NULL when generating
113 -- the Origination ID
114 ------------------------------------------------------------------------
115 --
116
117
118 -- The calculation of Pell amount is done in this package
119 --
120 -- Pre-requisites
121 -- The following tables have to be populated before calling this process
122 -- igf_fa_base_rec
123 -- igf_aw_fund_mast
124 -- igf_gr_pell_setup
125 --
126 -- The pell calculation routine is called from packaging,igfgr004,igfgr005,igfaw016
127 --
128
129 NO_SETUP EXCEPTION;
130 MY_EXP EXCEPTION;
131
132 --
133 -- Cursor to Pick up Person Details
134 --
135
136 CURSOR c_fa_base ( x_base_id igf_ap_fa_base_rec.base_id%TYPE)
137 IS
138 SELECT
139 faconv.base_id,
140 faconv.person_id,
141 faconv.ci_cal_type,
142 faconv.ci_sequence_number,
143 faconv.coa_code_f,
144 fed_verif_status,
145 coa_pell,
146 pell_alt_expense,
147 isir.transaction_num,
148 isir.original_ssn,
149 isir.orig_name_id,
150 isir.secondary_efc,
151 isir.sec_efc_type,
152 isir.isir_id
153 FROM
154 igf_ap_fa_base_rec faconv,
155 igf_ap_isir_matched isir
156 WHERE
157 x_base_id = faconv.base_id AND
158 faconv.base_id = isir.base_id AND
159 isir.active_isir = 'Y'
160 ORDER BY 1;
161
162 l_fa_base c_fa_base%ROWTYPE;
163
164 lv_invoke VARCHAR2(30);
165 ln_cnt NUMBER;
166
167
168
169
170 PROCEDURE rfms_load_rec ( p_ci_cal_type IN igs_ca_inst_all.cal_type%TYPE,
171 p_ci_seq_num IN igs_ca_inst_all.sequence_number%TYPE,
172 l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE)
173 IS
174
175 --
176 ------------------------------------------------------------------------------
177 --
178 -- Created By : cdcruz
179 -- Created On : 14-NOV-2000
180 -- Purpose :
181 -- Known limitations, enhancements or remarks :
182 -- Change History :
183 ------------------------------------------------------------------------------
184 -- Who When What
185 ------------------------------------------------------------------------------
186 -- rasahoo 13-Feb-2004 Bug # 3441605 Changed The cursor "cur_get_attendance_type_code" to
187 -- "cur_base_attendance_type_code". Now it will select
188 -- "base_attendance_type_code" instead of "attendance_type_code".
189 -- Removed cursor "cur_get_pell_att_code" as it is no longer used.
190 -- ugummall 15-DEC-2003 Bug 3316665. Changed the cursor c_fa_base and added
191 -- new message when the cusor cur_chk_orig is found.
192 -- ugummall 12-DEC-2003 Bug 3252832. FA 131 - COD Updates.
193 -- 1. Changed cursor name c_coa_f to c_coa_pell and picked up coa_pell item
194 -- instead of coa_f.
195 -- 2. cusor cur_payment_isir is changed.
196 -- ugummall 10-DEC-2003 Bug 3252832. FA 131 - COD Updates.
197 -- Removed cursor c_pell_setup.
198 -- Getting Pell Setup record logic is changed.
199 -- ugummall 04-DEC-2003 Bug 3252832. FA 131 - COD Updates.
200 -- Added group by clause in cursor cur_get_attendance_type_code.
201 -- ugummall 20-NOV-2003 Bug 3252832. FA 131 - COD Updates.
202 -- 1. Added two cursors cur_get_attendance_type_code and cur_get_pell_att_code
203 -- 2. Modified code in preparing enrollment_status field of the origination record.
204 ------------------------------------------------------------------------------
205 -- veramach 11-OCT-2003 FA 124
206 -- 1.COA is required for awarding PELL Grants
207 -- 2.Pell Award Amount must be less than or equal to amount calculated from PELL matrix
208 ------------------------------------------------------------------------------
209 -- rasahoo 27-Aug-2003 Removed the call to IGF_AP_OSS_PROCESS.GET_OSS_DETAILS
210 -- as part of obsoletion of FA base record history
211 -- gmuralid 06-JAN-2003 Bug 2728405 Changed Cursor for picking award
212 -- details
213 ------------------------------------------------------------------------------
214 -- vvutukur 17-Feb-2003 Enh#2758804.FACR105 Build.Raised an exception
215 -- to show proper error message when the active
216 -- isir is not same as the payment isir.
217 ------------------------------------------------------------------------------
218 --
219
220 CURSOR c_award ( x_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
221 p_ci_cal_type igs_ca_inst_all.cal_type%TYPE,
222 p_ci_seq_num igs_ca_inst_all.sequence_number%TYPE)
223 IS
224 SELECT awd.base_id,
225 awd.award_id,
226 awd.offered_amt,
227 awd.accepted_amt,
228 awd.fund_id,
229 awd.alt_pell_schedule,
230 fmast.ci_cal_type,
231 fmast.ci_sequence_number
232 FROM igf_aw_award awd,
233 igf_aw_fund_mast fmast,
234 igf_aw_fund_cat fcat
235 WHERE fmast.ci_cal_type = p_ci_cal_type
236 AND fmast.ci_sequence_number = p_ci_seq_num
237 AND awd.base_id = NVL(x_base_id,awd.base_id)
238 AND fcat.fed_fund_code = 'PELL'
239 AND awd.award_status IN ('ACCEPTED','OFFERED')
240 AND awd.fund_id = fmast.fund_id
241 AND fmast.fund_code = fcat.fund_code
242 ORDER BY
243 awd.base_id,
244 awd.award_id;
245
246 l_award c_award%ROWTYPE;
247
248 CURSOR c_awd_disb ( x_award_id igf_aw_award.award_id%type )
249 IS
250 SELECT
251 awd.*
252 FROM
253 igf_aw_awd_disb awd
254 WHERE
255 awd.award_id = x_award_id
256 ORDER BY awd.disb_num ;
257
258 l_awd_disb c_awd_disb%rowtype ;
259
260 --
261 -- Cursor to Check if Pell Origination Record is Present
262 --
263
264 CURSOR cur_chk_orig ( x_award_id igf_aw_award.award_id%TYPE)
265 IS
266 SELECT
267 rfms.origination_id
268 FROM
269 igf_gr_rfms rfms
270 WHERE
271 rfms.award_id = x_award_id ;
272
273 chk_orig_rec cur_chk_orig%ROWTYPE;
274
275
276 CURSOR c_rfms ( x_award_id igf_aw_award.award_id%TYPE,
277 x_disb_num igf_aw_awd_disb_all.disb_num%TYPE)
278 IS
279 SELECT
280 rfmd.origination_id
281 FROM
282 igf_gr_rfms rfms,
283 igf_gr_rfms_disb rfmd
284 WHERE
285 rfms.origination_id = rfmd.origination_id AND
286 rfmd.disb_ref_num = x_disb_num AND
287 rfms.award_id = x_award_id ;
288
289 l_rfms c_rfms%ROWTYPE;
290
291
292 --
293 -- Cursor to Pick Award Details
294 --
295
296 CURSOR cur_get_awd ( p_award_id igf_aw_award_all.award_id%TYPE)
297 IS
298 SELECT
299 adisb1.disb_num,
300 adisb2.disb_date
301 FROM
302 igf_aw_awd_disb adisb1,
303 igf_aw_awd_disb adisb2
304 WHERE
305 p_award_id = adisb1.award_id
306 AND
307 adisb1.disb_num
308 IN(SELECT MAX(adisb11.disb_num) FROM igf_aw_awd_disb adisb11
309 WHERE adisb11.award_id = adisb1.award_id)
310 AND
311 adisb1.award_id = adisb2.award_id
312 AND
313 adisb2.disb_num
314 IN( SELECT MIN(adisb11.disb_num) FROM igf_aw_awd_disb adisb11
315 WHERE adisb11.award_id = adisb2.award_id);
316
317
318 get_awd_rec cur_get_awd%ROWTYPE;
319
320 l_pell_setup igf_gr_pell_setup_all%ROWTYPE;
321 l_rfms_rec igf_gr_rfms%ROWTYPE ;
322 l_rfmsd_rec igf_gr_rfms_disb%ROWTYPE ;
323
324 lv_row_id VARCHAR2(25);
325 lv_rfmd_id NUMBER(15);
326
327
328 l_origination_id VARCHAR2(30);
329 l_error VARCHAR2(30);
330 l_pell_mat VARCHAR2(10);
331 --
332 -- One Student will have only one Pell Award in a given Award Year
333 --
334 -- Cursor to select active isir.
335
336 -- Cursor to select payment isir.
337 CURSOR cur_payment_isir(c_base_id NUMBER) IS
338 SELECT isir_id
339 FROM igf_ap_isir_matched
340 WHERE base_id = c_base_id
341 AND payment_isir = 'Y';
342
343
344 CURSOR cur_chk_duplicate_ssn(c_orig_id igf_gr_rfms.origination_id%TYPE) IS
345 SELECT 'Y'
346 FROM igf_gr_rfms
347 WHERE origination_id = c_orig_id;
348
349 chk_ssn VARCHAR2(1);
350
351 l_payment_isir igf_ap_fa_base_rec.payment_isir_id%TYPE;
352
353 -- Get coa
354 CURSOR c_coa_pell(
355 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE
356 ) IS
357 SELECT coa_pell
358 FROM igf_ap_fa_base_rec_all
359 WHERE base_id = cp_base_id;
360
361 CURSOR c_get_rep_pell_id(cp_cal_type VARCHAR2,cp_seq_num NUMBER,cp_att_pell_id VARCHAR2)
362 IS
363 SELECT rep.reporting_pell_cd
364 FROM
365 igf_gr_attend_pell gap,
366 igf_gr_report_pell rep
367 WHERE
368 gap.ci_cal_type = cp_cal_type AND
369 gap.ci_sequence_number = cp_seq_num AND
370 gap.attending_pell_cd = cp_att_pell_id AND
371 gap.rcampus_id = rep.rcampus_id;
372
373 l_coa_pell igf_ap_fa_base_rec_all.coa_pell%TYPE;
374
375 CURSOR c_get_rep_entity_id(cp_cal_type igs_ca_inst_all.cal_type%TYPE,
376 cp_seq_num igs_ca_inst_all.sequence_number%TYPE,
377 cp_atd_entity_id igf_gr_rfms_all.atd_entity_id_txt%TYPE) IS
378 SELECT rep.rep_entity_id_txt
379 FROM igf_gr_attend_pell att,
380 igf_gr_report_pell rep
381 WHERE att.rcampus_id = rep.rcampus_id
382 AND att.ci_cal_type = cp_cal_type
383 AND att.ci_sequence_number = cp_seq_num
384 AND att.atd_entity_id_txt = cp_atd_entity_id;
385
386 -- Local variables (Multiple FA offices build -- 10/29/2003 nsidana.)
387
388 l_attend_pell_id VARCHAR2(30);
389 l_rep_pell_id VARCHAR2(30);
390 l_ret_status VARCHAR2(1);
391 l_msg_data VARCHAR2(30);
392 l_stu_num VARCHAR2(30);
393 l_cod_year_flag BOOLEAN;
394 l_attend_entity_id igf_gr_rfms_all.atd_entity_id_txt%TYPE ;
395 l_rep_entity_id igf_gr_rfms_all.rep_entity_id_txt%TYPE ;
396
397
398
399 -- FA 131 - COD Updates Build cursors. 20-NOV-2003 ugummall.
400 CURSOR cur_base_attendance_type_code(cp_award_id igf_aw_awd_disb_all.award_id%TYPE) IS
401 SELECT base_attendance_type_code
402 FROM igf_aw_awd_disb_all
403 WHERE award_id = cp_award_id
404 GROUP BY base_attendance_type_code;
405 rec_base_attendance_type_code cur_base_attendance_type_code%ROWTYPE;
406 -- End FA 131.
407
408 l_return_status VARCHAR2(1);
409 l_return_mesg_text VARCHAR2(2000);
410 l_ft_pell_amt igf_gr_rfms_all.ft_pell_amount%TYPE;
411 l_pell_amt igf_gr_rfms.pell_amount%TYPE;
412 l_program_cd igf_gr_pell_setup_all.course_cd%TYPE;
413 l_program_version igf_gr_pell_setup_all.version_number%TYPE;
414 l_attendance_type igs_en_stdnt_ps_att.attendance_type%TYPE;
415
416 BEGIN
417
418 -- Get the award details
419 OPEN c_award (l_base_id,p_ci_cal_type,p_ci_seq_num);
420 ln_cnt := 0;
421
422 LOOP
423 FETCH c_award INTO l_award;
424 EXIT WHEN c_award%NOTFOUND;
425
426 BEGIN
427
428 OPEN c_fa_base(l_award.base_id) ;
429 FETCH c_fa_base INTO l_fa_base ;
430
431 IF c_fa_base%NOTFOUND THEN
432 CLOSE c_fa_base ;
433
434 ELSIF c_fa_base%FOUND THEN
435 CLOSE c_fa_base ;
436
437 fnd_file.new_line(fnd_file.log,1);
438 fnd_message.set_name('IGF','IGF_AW_PROCESS_STUD');
439 fnd_message.set_token('STUD',igf_gr_gen.get_per_num(l_award.base_id));
440 fnd_file.put_line(fnd_file.log,fnd_message.get);
441 fnd_file.new_line(fnd_file.log,1);
442
443 OPEN cur_payment_isir(l_award.base_id);
444 FETCH cur_payment_isir INTO l_payment_isir;
445 CLOSE cur_payment_isir;
446
447 --If active isir is not same as the payment isir, then
448 IF ((l_fa_base.isir_id IS NULL OR l_payment_isir IS NULL)
449 OR (l_fa_base.isir_id <> l_payment_isir))
450 THEN
451 fnd_message.set_name('IGF','IGF_AP_PELL_ISIR_CHK');
452 fnd_file.put_line(fnd_file.log,fnd_message.get);
453 RAISE MY_EXP;
454 END IF;
455
456 OPEN c_coa_pell(l_award.base_id);
457 FETCH c_coa_pell INTO l_coa_pell;
458 IF l_coa_pell IS NULL THEN
459 fnd_message.set_name('IGF','IGF_AW_PK_COA_NULL');
460 fnd_file.put_line(fnd_file.log,fnd_message.get);
461 CLOSE c_coa_pell;
462 RAISE MY_EXP;
463 END IF;
464
465 IF c_coa_pell%ISOPEN THEN
466 CLOSE c_coa_pell;
467 END IF;
468
469 OPEN cur_chk_orig(l_award.award_id);
470 FETCH cur_chk_orig INTO chk_orig_rec;
471 --
472 -- Create RFMS Record only if there is no existing Record
473 --
474 IF cur_chk_orig%FOUND THEN
475 fnd_message.set_name('IGF','IGF_GR_PELL_ALREADY_EXISTS');
476 fnd_file.put_line(fnd_file.log,fnd_message.get);
477 CLOSE cur_chk_orig;
478 RAISE MY_EXP;
479 ELSE
480 CLOSE cur_chk_orig;
481
482 -- get student's key program details. added the get_key_program api as part of fa132 term based integration
483 igf_ap_gen_001.get_key_program(cp_base_id => l_award.base_id,
484 cp_course_cd => l_program_cd,
485 cp_version_number => l_program_version
486 );
487 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_pell.rfms_load_rec.debug','Key Program > Course cd>' || l_program_cd || ' Version >' || TO_CHAR(l_program_version) );
489 END IF;
490
491 -- Get Pell Setup Details
492 igf_gr_pell_calc.get_pell_setup(cp_base_id => l_award.base_id,
493 cp_course_cd => l_program_cd,
494 cp_version_number => l_program_version,
495 cp_cal_type => l_fa_base.ci_cal_type,
496 cp_sequence_number => l_fa_base.ci_sequence_number ,
497 cp_pell_setup_rec => l_pell_setup ,
498 cp_message => l_return_mesg_text,
499 cp_return_status => l_return_status
500 );
501
502 IF (l_return_status = 'E') THEN
503 fnd_file.put_line(fnd_file.log, l_return_mesg_text);
504 EXIT;
505 END IF;
506 -- End of Get Pell Setup Details.
507
508 -- Load the rfms record
509
510 -- 10/29/2003 nsidana : Multiple FA offices build.
511
512 -- Derive the attending pell ID/attending entity ID for the base ID.
513
514 l_attend_pell_id := NULL;
515 l_attend_entity_id := NULL;
516 l_rep_pell_id := NULL;
517 l_rep_entity_id := NULL;
518 l_cod_year_flag := NULL;
519
520 -- Check wether the awarding year is for COD-XML processing or flat-file processing.
521 -- If it is for COD-XML processing, then derive attending entity id otherwise derive atteinding pell id
522 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (p_ci_cal_type, p_ci_seq_num,'PELL');
523
524 -- If l_cod_year_flag is true
525 IF (l_cod_year_flag) THEN
526 -- Derive attending entity id
527 igf_sl_gen.get_stu_fao_code(l_award.base_id,'ENTITY_ID',l_attend_entity_id,l_ret_status,l_msg_data);
528
529 ELSE
530 -- Derive attending pell id
531 igf_sl_gen.get_stu_fao_code(l_award.base_id,'PELL_ID',l_attend_pell_id,l_ret_status,l_msg_data);
532
533 END IF;
534
535 IF (l_ret_status='E') THEN
536 IF (l_cod_year_flag) THEN
537 fnd_message.set_name('IGF','IGF_GR_NO_ATTEND_ENTITY_ID');
538 ELSE
539 fnd_message.set_name('IGF','IGF_GR_NO_ATTEND_PELL');
540 END IF;
541 fnd_file.put_line(fnd_file.log,fnd_message.get());
542 RAISE MY_EXP;
543
544 ELSIF ((l_ret_status='S') AND (l_attend_pell_id IS NOT NULL OR l_attend_entity_id IS NOT NULL)) THEN
545
546 -- Derive the report pell ID/reporting entity ID
547
548 -- If l_cod_year_flag is true
549 IF (l_cod_year_flag) THEN -- full participation
550
551 -- Derive reporting entity id
552 OPEN c_get_rep_entity_id( p_ci_cal_type,p_ci_seq_num,l_attend_entity_id);
553 FETCH c_get_rep_entity_id INTO l_rep_entity_id;
554 CLOSE c_get_rep_entity_id;
555
556 IF (l_rep_entity_id IS NULL) THEN
557 l_stu_num:=igf_gr_gen.get_per_num(l_award.base_id);
558 fnd_message.set_name( 'IGF', 'IGF_GR_NOREP_ENTITY');
559 fnd_message.set_token('STU_NUMBER',l_stu_num);
560 fnd_file.put_line(fnd_file.log,fnd_message.get());
561 RAISE MY_EXP;
562 END IF;
563
564 ELSE -- phase-in participation
565
566 OPEN c_get_rep_pell_id(p_ci_cal_type,p_ci_seq_num,l_attend_pell_id);
567 FETCH c_get_rep_pell_id INTO l_rep_pell_id;
568 CLOSE c_get_rep_pell_id;
569
570 IF (l_rep_pell_id IS NULL) THEN
571 l_stu_num:=igf_gr_gen.get_per_num(l_award.base_id);
572 fnd_message.set_name( 'IGF', 'IGF_GR_NOREP_PELL');
573 fnd_message.set_token('STU_NUMBER',l_stu_num);
574 fnd_file.put_line(fnd_file.log,fnd_message.get());
575 RAISE MY_EXP;
576 END IF;
577
578 END IF;
579
580 IF (l_rep_entity_id IS NOT NULL OR l_rep_pell_id IS NOT NULL) THEN
581
582 -- both reporing and attending pell IDs derived successfully...do the normal processing...
583 -- passed l_attend_pell_id instead of l_reporting_pell. This is because Origination Id
584 -- expects attending pell
585
586 IF (l_cod_year_flag) THEN -- Full Student
587
588 -- Pass Attending Entity ID COD-XML processing year
589 igf_gr_pell.generate_origination_id( l_fa_base.base_id,
590 l_attend_entity_id,
591 l_origination_id,
592 l_error );
593 ELSE -- phase-in award year
594 -- Pass Attending Pell ID
595 igf_gr_pell.generate_origination_id( l_fa_base.base_id,
596 l_attend_pell_id,
597 l_origination_id,
598 l_error );
599 END IF;
600
601 OPEN cur_chk_duplicate_ssn(l_origination_id);
602 FETCH cur_chk_duplicate_ssn INTO chk_ssn;
603 IF (cur_chk_duplicate_ssn%FOUND) THEN
604 CLOSE cur_chk_duplicate_ssn;
605 fnd_message.set_name( 'IGF', 'IGF_SL_SSN_IN_USE');
606 fnd_message.set_token('VALUE',SUBSTR(l_origination_id,1,9));
607 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(l_fa_base.base_id));
608 fnd_file.put_line(fnd_file.log,fnd_message.get());
609 RAISE MY_EXP;
610 END IF;
611 CLOSE cur_chk_duplicate_ssn;
612
613 IF ( l_error = 'ISIR' ) THEN
614 fnd_message.set_name( 'IGF', 'IGF_GR_ISIR_NOT_FOUND');
615 fnd_message.set_token('STUD', igf_gr_gen.get_per_num(l_fa_base.base_id));
616 fnd_file.put_line(fnd_file.log,fnd_message.get());
617 RAISE NO_SETUP;
618
619 ELSIF ( l_error = 'CAL' ) THEN
620
621 fnd_message.set_name( 'IGF', 'IGF_GR_CAL_NOT_FOUND');
622 fnd_message.set_token('STUD', igf_gr_gen.get_per_num(l_fa_base.base_id));
623 fnd_file.put_line(fnd_file.log,fnd_message.get());
624 RAISE NO_SETUP;
625
626 ELSIF ( l_error = 'VAL_NULL' ) then
627
628 fnd_message.set_name( 'IGF', 'IGF_GR_VALS_NULL');
629 fnd_message.set_token('STUD',igf_gr_gen.get_per_num(l_fa_base.base_id));
630 fnd_file.put_line(fnd_file.log,fnd_message.get());
631
632 RAISE NO_SETUP;
633
634 END IF;
635
636 l_rfms_rec.origination_id := l_origination_id ;
637 l_rfms_rec.ci_cal_type := l_fa_base.ci_cal_type;
638 l_rfms_rec.ci_sequence_number := l_fa_base.ci_sequence_number;
639 l_rfms_rec.base_id := l_fa_base.base_id;
640 l_rfms_rec.award_id := l_award.award_id;
641 l_rfms_rec.sys_orig_ssn := LTRIM(RTRIM(l_fa_base.original_ssn));
642 l_rfms_rec.sys_orig_name_cd := LTRIM(RTRIM(l_fa_base.orig_name_id));
643 l_rfms_rec.transaction_num := l_fa_base.transaction_num;
644 l_rfms_rec.efc := igf_gr_gen.get_pell_efc(l_fa_base.base_id);
645
646 --
647 -- Federal Verification Status Mapping with Grants Verification Status Code is impletemented
648 -- in igf_ap_batch_ver_prc_pkg packages The call has been added here
649 --
650
651 l_rfms_rec.ver_status_code := igf_ap_batch_ver_prc_pkg.get_gr_ver_code(l_fa_base.fed_verif_status,
652 l_fa_base.ci_cal_type,
653 l_fa_base.ci_sequence_number);
654 l_rfms_rec.secondary_efc_cd := igf_gr_gen.get_pell_efc_code(l_fa_base.base_id);
655
656 ---
657 -- Bug ID : 1774268
658 --
659 IF NVL(l_award.accepted_amt,0) = 0 THEN
660 l_rfms_rec.pell_amount := NVL(l_award.offered_amt,0);
661 ELSE
662 l_rfms_rec.pell_amount := NVL(l_award.accepted_amt,0);
663 END IF;
664
665 igf_gr_pell_calc.calc_ft_max_pell(cp_base_id => l_rfms_rec.base_id,
666 cp_cal_type => l_rfms_rec.ci_cal_type,
667 cp_sequence_number => l_rfms_rec.ci_sequence_number,
668 cp_flag => 'FULL_TIME',
669 cp_aid => l_pell_amt,
670 cp_ft_aid => l_ft_pell_amt,
671 cp_return_status => l_return_status,
672 cp_message => l_return_mesg_text
673 );
674
675 IF (l_return_status = 'E') THEN
676 fnd_file.put_line(fnd_file.log,l_return_mesg_text);
677 ELSE
678 IF l_rfms_rec.pell_amount > l_ft_pell_amt THEN
679 fnd_message.set_name('IGF','IGF_GR_LI_PELL_AWD_SCH_MISMTH');
680 fnd_message.set_token('AWD_AMT',l_rfms_rec.pell_amount);
681 fnd_message.set_token('SCHDL_AMT',l_ft_pell_amt);
682 fnd_file.put_line(fnd_file.log,fnd_message.get);
683 RAISE my_exp;
684 ELSE
685 l_rfms_rec.ft_pell_amount := l_ft_pell_amt;
686 END IF;
687 END IF;
688
689
690
691
692 -- Get the Full Time Pell Amount,First Disbursement Date and Number of Disbursements.
693
694 OPEN cur_get_awd(l_award.award_id);
695 FETCH cur_get_awd INTO get_awd_rec;
696 CLOSE cur_get_awd;
697
698
699 l_rfms_rec.pell_profile := l_pell_setup.pell_profile;
700
701 -- FA 131 Build. 20-NOV-2003. Preparing enrollment_status field.
702 OPEN cur_base_attendance_type_code(l_award.award_id);
703 FETCH cur_base_attendance_type_code INTO rec_base_attendance_type_code;
704
705 -- It returns one or more records. Never returns zero records.
706 IF (cur_base_attendance_type_code%ROWCOUNT > 1) THEN
707 l_rfms_rec.enrollment_status := '5'; -- 5 for Pell Attendance "Others"
708 ELSIF (rec_base_attendance_type_code.base_attendance_type_code IS NULL) THEN
709 -- cursor returned 1 row. And attendance_type_code is null
710 l_rfms_rec.enrollment_status := '5'; -- 5 for Pell Attendance "Others"
711 ELSE
712 l_rfms_rec.enrollment_status := rec_base_attendance_type_code.base_attendance_type_code;
713 END IF;
714 CLOSE cur_base_attendance_type_code;
715 -- End FA 131 Build.
716
717 l_rfms_rec.enrollment_dt := get_enrollment_date(l_award.award_id);
718 l_rfms_rec.coa_amount := l_fa_base.coa_pell;
719 l_rfms_rec.academic_calendar := l_pell_setup.academic_cal;
720 l_rfms_rec.payment_method := l_pell_setup.payment_method;
721 l_rfms_rec.total_pymt_prds := l_pell_setup.payment_periods_num;
722 l_rfms_rec.incrcd_fed_pell_rcp_cd := NULL;
723 l_rfms_rec.attending_campus_id := l_attend_pell_id;
724 l_rfms_rec.est_disb_dt1 := get_awd_rec.disb_date;
725 l_rfms_rec.orig_action_code := 'R';
726 l_rfms_rec.orig_status_dt := TRUNC(SYSDATE);
727 l_rfms_rec.orig_ed_use_flags := NULL;
728
729 l_rfms_rec.prev_accpt_efc := NULL;
730 l_rfms_rec.prev_accpt_tran_no := NULL;
731 l_rfms_rec.prev_accpt_sec_efc_cd := NULL;
732 l_rfms_rec.prev_accpt_coa := NULL;
733 l_rfms_rec.orig_reject_code := NULL;
734 l_rfms_rec.wk_inst_time_calc_pymt := l_pell_setup.wk_inst_time_calc_pymt ;
735 l_rfms_rec.wk_int_time_prg_def_yr := l_pell_setup.wk_int_time_prg_def_yr ;
736 l_rfms_rec.cr_clk_hrs_prds_sch_yr := l_pell_setup.cr_clk_hrs_prds_sch_yr ;
737 l_rfms_rec.cr_clk_hrs_acad_yr := l_pell_setup.cr_clk_hrs_acad_yr ;
738 l_rfms_rec.inst_cross_ref_cd := l_pell_setup.inst_cross_ref_code;
739 l_rfms_rec.full_resp_code := l_pell_setup.response_option_code;
740
741 --
742 -- Only for Alternate Pell Awards, Low Tution and Fees Code will
743 -- be populated
744 --
745
746 l_pell_mat := l_award.alt_pell_schedule;
747 IF l_pell_mat = 'A' THEN
748 l_rfms_rec.low_tution_fee := igf_gr_gen.get_tufees_code(l_fa_base.base_id,
749 l_fa_base.ci_cal_type,
750 l_fa_base.ci_sequence_number);
751 ELSE
752 l_rfms_rec.low_tution_fee := NULL;
753 END IF;
754
755 l_rfms_rec.rec_source := 'B';
756 l_rfms_rec.rfmb_id := NULL;
757 l_rfms_rec.pending_amount := NULL;
758
759 lv_row_id := NULL;
760
761 igf_gr_rfms_pkg.insert_row ( x_rowid => lv_row_id,
762 x_origination_id => l_rfms_rec.origination_id,
763 x_ci_cal_type => l_rfms_rec.ci_cal_type,
764 x_ci_sequence_number => l_rfms_rec.ci_sequence_number,
765 x_base_id => l_rfms_rec.base_id,
766 x_award_id => l_rfms_rec.award_id,
767 x_rfmb_id => l_rfms_rec.rfmb_id,
768 x_sys_orig_ssn => l_rfms_rec.sys_orig_ssn,
769 x_sys_orig_name_cd => l_rfms_rec.sys_orig_name_cd,
770 x_transaction_num => l_rfms_rec.transaction_num,
771 x_efc => l_rfms_rec.efc,
772 x_ver_status_code => l_rfms_rec.ver_status_code,
773 x_secondary_efc => l_rfms_rec.secondary_efc,
774 x_secondary_efc_cd => l_rfms_rec.secondary_efc_cd,
775 x_pell_amount => l_rfms_rec.pell_amount,
776 x_pell_profile => l_rfms_rec.pell_profile,
777 x_enrollment_status => l_rfms_rec.enrollment_status,
778 x_enrollment_dt => l_rfms_rec.enrollment_dt,
779 x_coa_amount => l_rfms_rec.coa_amount,
780 x_academic_calendar => l_rfms_rec.academic_calendar,
781 x_payment_method => l_rfms_rec.payment_method,
782 x_total_pymt_prds => l_rfms_rec.total_pymt_prds,
783 x_incrcd_fed_pell_rcp_cd => l_rfms_rec.incrcd_fed_pell_rcp_cd,
784 x_attending_campus_id => l_rfms_rec.attending_campus_id,
785 x_est_disb_dt1 => l_rfms_rec.est_disb_dt1,
786 x_orig_action_code => l_rfms_rec.orig_action_code,
787 x_orig_status_dt => l_rfms_rec.orig_status_dt,
788 x_orig_ed_use_flags => l_rfms_rec.orig_ed_use_flags,
789 x_ft_pell_amount => l_rfms_rec.ft_pell_amount,
790 x_prev_accpt_efc => l_rfms_rec.prev_accpt_efc,
791 x_prev_accpt_tran_no => l_rfms_rec.prev_accpt_tran_no,
792 x_prev_accpt_sec_efc_cd => l_rfms_rec.prev_accpt_sec_efc_cd,
793 x_prev_accpt_coa => l_rfms_rec.prev_accpt_coa,
794 x_orig_reject_code => l_rfms_rec.orig_reject_code,
795 x_wk_inst_time_calc_pymt => l_rfms_rec.wk_inst_time_calc_pymt,
796 x_wk_int_time_prg_def_yr => l_rfms_rec.wk_int_time_prg_def_yr,
797 x_cr_clk_hrs_prds_sch_yr => l_rfms_rec.cr_clk_hrs_prds_sch_yr,
798 x_cr_clk_hrs_acad_yr => l_rfms_rec.cr_clk_hrs_acad_yr,
799 x_inst_cross_ref_cd => l_rfms_rec.inst_cross_ref_cd,
800 x_low_tution_fee => l_rfms_rec.low_tution_fee,
801 x_rec_source => l_rfms_rec.rec_source,
802 x_pending_amount => l_rfms_rec.pending_amount,
803 x_mode => 'R',
804 x_birth_dt => NULL,
805 x_last_name => NULL,
806 x_first_name => NULL,
807 x_middle_name => NULL,
808 x_current_ssn => NULL,
809 x_legacy_record_flag => NULL,
810 x_reporting_pell_cd => l_rep_pell_id,
811 x_rep_entity_id_txt => l_rep_entity_id,
812 x_atd_entity_id_txt => l_attend_entity_id,
813 x_note_message => NULL,
814 x_full_resp_code => l_rfms_rec.full_resp_code,
815 x_document_id_txt => NULL );
816
817 fnd_file.put_line(fnd_file.log,'');
818 fnd_message.set_name('IGF','IGF_GR_CREATE_RFMS');
819 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(l_fa_base.base_id));
820 fnd_message.set_token('ORIG_ID',l_rfms_rec.origination_id);
821 fnd_file.put_line(fnd_file.log, fnd_message.get);
822 fnd_file.put_line(fnd_file.log,'');
823
824 ln_cnt := ln_cnt + 1;
825
826 --
827 -- Insert the RFMS Disbursement Details only for Phase-In
828 --
829 IF NOT l_cod_year_flag THEN
830 OPEN c_awd_disb (l_award.award_id) ;
831
832 LOOP
833 FETCH c_awd_disb into l_awd_disb ;
834 EXIT WHEN c_awd_disb%NOTFOUND;
835 --
836 -- Check if RFMS record is already created
837 --
838 OPEN c_rfms ( l_award.award_id,l_awd_disb.disb_num ) ;
839 FETCH c_rfms INTO l_rfms;
840 --
841 -- This will make sure that only new disbursements from award will go into rfms_disb table
842 --
843 IF c_rfms%NOTFOUND THEN
844 CLOSE c_rfms;
845 IF l_rfms_rec.origination_id IS NOT NULL THEN
846 l_rfmsd_rec.origination_id := l_rfms_rec.origination_id;
847 ELSE
848 l_rfmsd_rec.origination_id := chk_orig_rec.origination_id;
849 END IF;
850
851 l_rfmsd_rec.disb_ref_num := l_awd_disb.disb_num ;
852 l_rfmsd_rec.disb_dt := l_awd_disb.disb_date ;
853 l_rfmsd_rec.disb_amt := l_awd_disb.disb_net_amt ;
854
855 IF l_rfmsd_rec.disb_amt >= 0 THEN
856 l_rfmsd_rec.db_cr_flag := 'P' ;
857 ELSE
858 l_rfmsd_rec.db_cr_flag := 'N' ;
859 END IF;
860
861 l_rfmsd_rec.disb_ack_act_status := 'R' ;
862 l_rfmsd_rec.disb_status_dt := TRUNC(SYSDATE);
863 l_rfmsd_rec.disb_accpt_amt := NULL ;
864 l_rfmsd_rec.accpt_db_cr_flag := NULL ;
865 l_rfmsd_rec.disb_ytd_amt := NULL ;
866 l_rfmsd_rec.pymt_prd_start_dt := NULL ;
867 l_rfmsd_rec.accpt_pymt_prd_start_dt := NULL ;
868 l_rfmsd_rec.edit_code := NULL ;
869 l_rfmsd_rec.rfmb_id := NULL ;
870
871
872 -- Insert RFMS Disb Record
873
874 lv_row_id := NULL;
875
876 igf_gr_rfms_disb_pkg.insert_row (x_mode => 'R',
877 x_rowid => lv_row_id,
878 x_rfmd_id => lv_rfmd_id,
879 x_origination_id => l_rfmsd_rec.origination_id,
880 x_disb_ref_num => l_rfmsd_rec.disb_ref_num,
881 x_disb_dt => l_rfmsd_rec.disb_dt,
882 x_disb_amt => l_rfmsd_rec.disb_amt,
883 x_db_cr_flag => l_rfmsd_rec.db_cr_flag,
884 x_disb_ack_act_status => l_rfmsd_rec.disb_ack_act_status ,
885 x_disb_status_dt => l_rfmsd_rec.disb_status_dt ,
886 x_accpt_disb_dt => l_rfmsd_rec.accpt_disb_dt ,
887 x_disb_accpt_amt => l_rfmsd_rec.disb_accpt_amt ,
888 x_accpt_db_cr_flag => l_rfmsd_rec.accpt_db_cr_flag ,
889 x_disb_ytd_amt => l_rfmsd_rec.disb_ytd_amt ,
890 x_pymt_prd_start_dt => l_rfmsd_rec.pymt_prd_start_dt ,
891 x_accpt_pymt_prd_start_dt => l_rfmsd_rec.accpt_pymt_prd_start_dt ,
892 x_edit_code => l_rfmsd_rec.edit_code ,
893 x_rfmb_id => l_rfmsd_rec.rfmb_id,
894 x_ed_use_flags => l_rfmsd_rec.ed_use_flags);
895
896 fnd_message.set_name('IGF','IGF_GR_CREATE_RFMS_DISB');
897 fnd_message.set_token('ORIG_ID',l_rfmsd_rec.origination_id);
898 fnd_message.set_token('DISB_NUM',l_rfmsd_rec.disb_ref_num);
899 fnd_file.put_line(fnd_file.log, fnd_message.get);
900 ELSIF c_rfms%FOUND THEN
901 CLOSE c_rfms;
902 END IF;
903 END LOOP;
904 CLOSE c_awd_disb;
905 END IF; -- phase-in, insert gr disbursement
906 END IF; -- for successful derivation of the Report Pell.
907 END IF; -- for l_ret_status='E'
908 END IF; -- cur_chk_orig IF ..
909 END IF; -- cur c_fa_base IF
910
911 IF cur_chk_orig%ISOPEN THEN
912 CLOSE cur_chk_orig;
913 END IF;
914
915 EXCEPTION
916
917 WHEN NO_SETUP THEN
918 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
919 fnd_file.put_line(fnd_file.log,fnd_message.get);
920 fnd_file.new_line(fnd_file.log,1);
921
922 WHEN MY_EXP THEN
923 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
924 fnd_file.put_line(fnd_file.log,fnd_message.get);
925 fnd_file.new_line(fnd_file.log,1);
926 ln_cnt := ln_cnt +1;
927 END; -- Block
928
929 END LOOP; -- c_award LOOP
930 CLOSE c_award;
931
932 IF ln_cnt > 0 THEN
933 NULL;
934 ELSE
935 fnd_message.set_name('IGF','IGF_AP_NO_DATA_FOUND'); -- Origination Record already exist
936 fnd_file.put_line(fnd_file.log,fnd_message.get);
937 END IF;
938
939 EXCEPTION
940 WHEN OTHERS THEN
941 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
942 fnd_message.set_token('NAME','IGF_GR_PELL.RFMS_LOAD_REC');
943 fnd_file.put_line(fnd_file.log,SQLERRM);
944 igs_ge_msg_stack.add;
945 app_exception.raise_exception;
946 END rfms_load_rec ;
947
948
949 PROCEDURE rfms_load( errbuf OUT NOCOPY VARCHAR,
950 retcode OUT NOCOPY NUMBER,
951 l_award_year IN VARCHAR2,
952 l_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
953 p_org_id IN NUMBER )
954 IS
955
956 --
957 ------------------------------------------------------------------------------
958 --
959 -- Created By : cdcruz
960 -- Created On : 14-NOV-2000
961 -- Purpose :
962 -- Known limitations, enhancements or remarks :
963 -- Change History :
964 ------------------------------------------------------------------------------
965 -- Who When What
966 ------------------------------------------------------------------------------
967 -- rasahoo 27-Aug-2003 Removed the parameter P_GET_RECENT_INFO
968 -- as part of obsoletion of FA base record history
969 -- gmuralid 06-JAN-2003 Bug 2728405 Changed Cursor for picking award
970 -- details
971 ------------------------------------------------------------------------------
972 -- vvutukur 17-Feb-2003 Enh#2758804.FACR105 Build.Raised an exception
973 -- to show proper error message when the active
974 -- isir is not same as the payment isir.
975 ------------------------------------------------------------------------------
976 --
977
978 l_ci_cal_type igs_ca_inst_all.cal_type%TYPE;
979 l_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
980
981
982 BEGIN
983
984 retcode := 0 ;
985 lv_invoke := 'JOB';
986
987 igf_aw_gen.set_org_id(p_org_id);
988
989 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(l_award_year,1,10)));
990 l_ci_sequence_number := TO_NUMBER(SUBSTR(l_award_year,11));
991
992 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
993 retcode := 2 ;
994 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
995 igs_ge_msg_stack.conc_exception_hndl;
996 ELSE
997 rfms_load_rec(l_ci_cal_type,l_ci_sequence_number,l_base_id);
998
999 END IF;
1000
1001 COMMIT;
1002
1003 EXCEPTION
1004
1005 WHEN app_exception.record_lock_exception THEN
1006 ROLLBACK;
1007 retcode:=2;
1008 fnd_message.set_name('IGF','IGF_GE_LOCK_ERROR');
1009 igs_ge_msg_stack.add;
1010 errbuf := fnd_message.get;
1011
1012 WHEN OTHERS THEN
1013 ROLLBACK;
1014 retcode:=2;
1015 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1016 fnd_file.put_line(fnd_file.log,SQLERRM);
1017 igs_ge_msg_stack.conc_exception_hndl;
1018
1019 END rfms_load ;
1020
1021 PROCEDURE generate_origination_id( l_base_id IN NUMBER,
1022 l_attend_pell_id IN VARCHAR2,
1023 l_origination_id OUT NOCOPY VARCHAR2,
1024 l_error OUT NOCOPY VARCHAR2 )
1025 IS
1026
1027 ------------------------------------------------------------------------
1028 -- sjadhav 10-Feb-2003 Bug 2758812 - FA116 Pell Build
1029 -- Modified generate_origination_id to read
1030 -- pell cycle year using function
1031 -- igf_gr_gen.get_cycle_year
1032 ------------------------------------------------------------------------
1033
1034 CURSOR c_isir ( x_base_id igf_ap_fa_base_rec.base_id%TYPE)
1035 IS
1036 SELECT
1037 isir.original_ssn,
1038 isir.orig_name_id
1039 FROM
1040 igf_ap_isir_matched isir
1041 WHERE isir.base_id = x_base_id
1042 AND isir.active_isir = 'Y' ;
1043
1044 l_isir c_isir%rowtype;
1045
1046
1047 CURSOR c_cal ( x_base_id igf_ap_fa_base_rec.base_id%TYPE)
1048 IS
1049 SELECT
1050 ci_cal_type,ci_sequence_number
1051 FROM
1052 igf_ap_fa_base_rec fabase
1053 WHERE
1054 fabase.base_id = x_base_id;
1055
1056 l_cal c_cal%rowtype;
1057
1058 BEGIN
1059
1060 l_error := NULL;
1061
1062 -- Get ISIR details
1063 OPEN c_isir( l_base_id ) ;
1064 FETCH c_isir into l_isir ;
1065 IF c_isir%NOTFOUND THEN
1066 l_error := 'ISIR';
1067 CLOSE c_isir ;
1068 RETURN;
1069 END IF;
1070 CLOSE c_isir ;
1071
1072 -- Get calendar dates
1073 OPEN c_cal( l_base_id );
1074 FETCH c_cal into l_cal ;
1075 IF ( c_cal%NOTFOUND ) THEN
1076 l_error := 'CAL';
1077 CLOSE c_cal;
1078 RETURN;
1079 END IF;
1080 CLOSE c_cal;
1081
1082 IF ( (l_isir.original_ssn IS NOT NULL) AND
1083 (l_isir.orig_name_id IS NOT NULL) AND
1084 (l_cal.ci_cal_type IS NOT NULL) AND
1085 (l_attend_pell_id IS NOT NULL)) THEN
1086 l_origination_id := l_isir.original_ssn ||
1087 RPAD(l_isir.orig_name_id,2,' ') ||
1088 igf_gr_gen.get_cycle_year
1089 (l_cal.ci_cal_type,
1090 l_cal.ci_sequence_number) ||
1091 RTRIM(LTRIM(l_attend_pell_id)) ||
1092 '00';
1093 ELSE
1094 l_error := 'VAL_NULL';
1095 END IF;
1096
1097 EXCEPTION
1098
1099 WHEN OTHERS THEN
1100 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1101 fnd_message.set_token('NAME','IGF_GR_PELL.GENERATE_ORIGINATION_ID' ||' '||SQLERRM);
1102 igs_ge_msg_stack.add;
1103 app_exception.raise_exception;
1104
1105 END generate_origination_id;
1106
1107 FUNCTION get_enrollment_date(p_award_id igf_aw_award_all.award_id%TYPE)
1108 RETURN DATE
1109 AS
1110
1111 CURSOR cur_enrollment_date(cp_award_id igf_aw_award_all.award_id%TYPE) IS
1112 SELECT ld_cal_type,ld_sequence_number
1113 FROM igf_aw_awd_disb_all
1114 WHERE award_id = cp_award_id
1115 AND trans_type <> 'C';
1116
1117 CURSOR c_base_id(cp_award_id igf_aw_award_all.award_id%TYPE) IS
1118 SELECT base_id
1119 FROM igf_aw_award_all
1120 WHERE award_id = cp_award_id;
1121 l_base_id igf_ap_fa_base_rec_all.base_id%TYPE;
1122
1123 p_start_dt DATE;
1124 l_start_dt DATE;
1125 l_end_dt DATE;
1126 l_first_cycle VARCHAR2(1);
1127
1128 BEGIN
1129 p_start_dt := NULL;
1130 l_base_id := NULL;
1131
1132 OPEN c_base_id(p_award_id);
1133 FETCH c_base_id INTO l_base_id;
1134 CLOSE c_base_id;
1135
1136 l_first_cycle := 'Y';
1137
1138 FOR rec IN cur_enrollment_date(p_award_id) LOOP
1139 igf_ap_gen_001.get_term_dates(
1140 p_base_id => l_base_id,
1141 p_ld_cal_type => rec.ld_cal_type,
1142 p_ld_sequence_number => rec.ld_sequence_number,
1143 p_ld_start_date => l_start_dt,
1144 p_ld_end_date => l_end_dt
1145 );
1146 IF l_first_cycle = 'Y' THEN
1147 p_start_dt := l_start_dt;
1148 l_first_cycle := 'N';
1149 ELSE
1150 p_start_dt := LEAST(p_start_dt,l_start_dt);
1151 END IF;
1152 END LOOP;
1153
1154 RETURN p_start_dt;
1155 EXCEPTION WHEN OTHERS THEN
1156 RETURN NULL;
1157 END get_enrollment_date;
1158
1159 END igf_gr_pell;