1 PACKAGE BODY igf_gr_rfms_orig AS
2 /* $Header: IGFGR02B.pls 120.4 2006/02/08 23:45:48 ridas ship $ */
3 ------------------------------------------------------------------------
4 -- bvisvana 07-July-2005 Bug # 4008991 - IGF_GR_BATCH_DOES_NOT_EXIST replaced by IGF_SL_GR_BATCH_DOES_NO_EXIST
5 ------------------------------------------------------------------------
6 -- ayedubat 20-OCT-2004 FA 149 COD-XML Standards build bug # 3416863
7 -- Changed the logic as per the TD, FA149_TD_COD_XML_i1a.doc
8 ------------------------------------------------------------------------
9 -- veramach 12-Mar-2004 bug 3490915
10 -- Aded validation to check if award amounts in pell origination
11 -- and student award level are equal or not
12 ------------------------------------------------------------------------
13 -- veramach 29-Jan-2004 Bug 3408092 Added 2004-2005 in g_ver_num checks
14 ------------------------------------------------------------------------
15 -- ugummall 08-Jan-2003 Bug 3318202. Changed the order of parameters and removed
16 -- the parameter p_org_id in main.
17 ------------------------------------------------------------------------
18 -- ugummall 05-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
19 -- 1. Added two extra parameters to main and rfms_orig procedures.
20 -- 2. Modified cursor cur_rfms to include reporting and attending pell ids.
21 -- 3. Removed l_rep_pell_id and its references. Used p_reporting_pell, newly
22 -- passed parameter, in igf_gr_gen.{get_pell_trailer, get_pell_header} procedures.
23 -- 4. New cursor cur_attending_pell to check attending pell is a child of reporting pell or not.
24 -- 5. In rfms_orig prcodure processed only those records for which attending pell is
25 -- a child of reporting pell id.
26 ------------------------------------------------------------------------
27 -- ugummall 03-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
28 -- Added two extra parameters to igf_gr_gen.get_pell_header call.
29 ------------------------------------------------------------------------
30 -- cdcruz 15-Aug-2003 Bug BUG FA121-3085558
31 -- Check added to check for Transaction Number
32 -- Match against Payment ISIR.
33 ------------------------------------------------------------------------
34 -- sjadhav 01-Aug-2003 Bug BUG 3062062
35 -- Removed code to re-calcuate efc/efc code
36 -- after Origination is Sent
37 --------------------------------------------------------------------------
38 -- rasahoo 13-May-2003 Bug #2938258 If Origination Record is not
39 -- part of this batch in the System then raise
40 -- error.Cannot process Origination
41 -- Record which status for acknowledgement
42 -- processing is not "Sent".
43 ---------------------------------------------------------------------------
44 -- sjadhav 06-Feb-2003 FA116 Build Bug - 2758812
45 -- added invalid_version expcetion
46 -- modified for 03-04 compliance
47 -- output file in UPPERCASE
48 --------------------------------------------------------------------------
49 -- sjadhav Bug 2460904 Before sending origination check for the
50 -- ft pell amount and pell award amount.
51 -- if pell award amount is more than ft pell
52 -- amount do not send this origination record,
53 -- log a message and skip
54 -- use igf_gr_gen.get_pell_efc_code to get
55 -- value for sec efc code
56 --
57 --------------------------------------------------------------------------
58 -- sjadhav Bug 2383690 added igf_gr_gen.send_orig_disb call
59 --------------------------------------------------------------------------
60 -- sjadhav Bug 2216956 - FACR007 Removed flag parameter
61 -- Added Award Year parameter to
62 -- main_ack
63 --------------------------------------------------------------------------
64 -- sjadhav 18-jun-2001 Bug ID : 1823995 base_id is made null
65 -- if the process is run for an award year
66 --------------------------------------------------------------------------
67 --
68 --------------------------------------------------------------------------
69 -- Created By : sjadhav
70 -- Date Created On : 2001/01/03
71 --------------------------------------------------------------------------
72 --
73
74 no_data_in_table EXCEPTION;
75 param_error EXCEPTION;
76 batch_not_created EXCEPTION;
77 invalid_version EXCEPTION;
78 persid_grp_sql_stmt_error EXCEPTION;
79
80 l_cy_yr VARCHAR2(10) DEFAULT NULL; -- to hold cycle year
81 l_msg_prn_1 BOOLEAN DEFAULT TRUE;
82 g_ver_num VARCHAR2(30) DEFAULT NULL; -- Flat File Version Number
83 g_print_header VARCHAR2(1) DEFAULT 'N';
84 l_header VARCHAR2(1000);
85 g_alt_code VARCHAR2(80);
86
87 --
88 -- Main Cursor to pick up RFMS Records
89 -- for preparing Origination File
90 --
91
92 -- FA 126. This cursor cur_rfms is modified to include reporting and attending pell ids.
93
94 CURSOR cur_rfms(l_base_id igf_gr_rfms.base_id%TYPE,
95 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
96 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE,
97 cp_reporting_pell igf_gr_rfms.rep_pell_id%TYPE,
98 cp_attending_pell igf_gr_rfms.attending_campus_id%TYPE
99 )
100 IS
101 SELECT
102 rfms.*
103 FROM
104 igf_gr_rfms rfms
105 WHERE
106 rfms.orig_action_code = 'R' AND
107 rfms.base_id = NVL(l_base_id,rfms.base_id) AND
108 rfms.ci_cal_type = l_ci_cal_type AND
109 rfms.ci_sequence_number = l_ci_sequence_number AND
110 rfms.rep_pell_id = cp_reporting_pell AND
111 rfms.attending_campus_id = NVL(cp_attending_pell, rfms.attending_campus_id)
112 FOR UPDATE OF orig_action_code NOWAIT;
113
114 rfms_rec cur_rfms%ROWTYPE;
115
116
117
118 PROCEDURE update_orig_rec(p_rfms_rec cur_rfms%ROWTYPE,
119 p_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE) IS
120 --
121 -- This procedure updates the rfms origination records
122 -- which are being sent to the external processor
123 -- orig_action_code is updated to 'S' to indicate that
124 -- the record has been processed and sent
125 -- Update the RFMB_ID field in IGFGR004
126 --
127 BEGIN
128
129 igf_gr_rfms_pkg.update_row(
130 x_rowid => p_rfms_rec.row_id,
131 x_origination_id => p_rfms_rec.origination_id,
132 x_ci_cal_type => p_rfms_rec.ci_cal_type,
133 x_ci_sequence_number => p_rfms_rec.ci_sequence_number,
134 x_base_id => p_rfms_rec.base_id,
135 x_award_id => p_rfms_rec.award_id,
136 x_rfmb_id => p_rfmb_id,
137 x_sys_orig_ssn => p_rfms_rec.sys_orig_ssn,
138 x_sys_orig_name_cd => p_rfms_rec.sys_orig_name_cd,
139 x_transaction_num => p_rfms_rec.transaction_num,
140 x_efc => p_rfms_rec.efc,
141 x_ver_status_code => p_rfms_rec.ver_status_code,
142 x_secondary_efc => p_rfms_rec.secondary_efc,
143 x_secondary_efc_cd => p_rfms_rec.secondary_efc_cd,
144 x_pell_amount => p_rfms_rec.pell_amount,
145 x_pell_profile => p_rfms_rec.pell_profile,
146 x_enrollment_status => p_rfms_rec.enrollment_status,
147 x_enrollment_dt => p_rfms_rec.enrollment_dt,
148 x_coa_amount => p_rfms_rec.coa_amount,
149 x_academic_calendar => p_rfms_rec.academic_calendar,
150 x_payment_method => p_rfms_rec.payment_method,
151 x_total_pymt_prds => p_rfms_rec.total_pymt_prds,
152 x_incrcd_fed_pell_rcp_cd => p_rfms_rec.incrcd_fed_pell_rcp_cd,
153 x_attending_campus_id => p_rfms_rec.attending_campus_id,
154 x_est_disb_dt1 => p_rfms_rec.est_disb_dt1,
155 x_orig_action_code => 'S',
156 x_orig_status_dt => p_rfms_rec.orig_status_dt,
157 x_orig_ed_use_flags => p_rfms_rec.orig_ed_use_flags,
158 x_ft_pell_amount => p_rfms_rec.ft_pell_amount,
159 x_prev_accpt_efc => p_rfms_rec.prev_accpt_efc,
160 x_prev_accpt_tran_no => p_rfms_rec.prev_accpt_tran_no,
161 x_prev_accpt_sec_efc_cd => p_rfms_rec.prev_accpt_sec_efc_cd,
162 x_prev_accpt_coa => p_rfms_rec.prev_accpt_coa,
163 x_orig_reject_code => p_rfms_rec.orig_reject_code,
164 x_wk_inst_time_calc_pymt => p_rfms_rec.wk_inst_time_calc_pymt,
165 x_wk_int_time_prg_def_yr => p_rfms_rec.wk_int_time_prg_def_yr,
166 x_cr_clk_hrs_prds_sch_yr => p_rfms_rec.cr_clk_hrs_prds_sch_yr,
167 x_cr_clk_hrs_acad_yr => p_rfms_rec.cr_clk_hrs_acad_yr,
168 x_inst_cross_ref_cd => p_rfms_rec.inst_cross_ref_cd,
169 x_low_tution_fee => p_rfms_rec.low_tution_fee,
170 x_rec_source => p_rfms_rec.rec_source,
171 x_pending_amount => p_rfms_rec.pending_amount,
172 x_mode => 'R',
173 x_birth_dt => p_rfms_rec.birth_dt,
174 x_last_name => p_rfms_rec.last_name,
175 x_first_name => p_rfms_rec.first_name,
176 x_middle_name => p_rfms_rec.middle_name,
177 x_current_ssn => p_rfms_rec.current_ssn,
178 x_legacy_record_flag => NULL,
179 x_reporting_pell_cd => p_rfms_rec.rep_pell_id,
180 x_rep_entity_id_txt => p_rfms_rec.rep_entity_id_txt,
181 x_atd_entity_id_txt => p_rfms_rec.atd_entity_id_txt,
182 x_note_message => p_rfms_rec.note_message,
183 x_full_resp_code => p_rfms_rec.full_resp_code,
184 x_document_id_txt => p_rfms_rec.document_id_txt );
185 EXCEPTION
186 WHEN OTHERS THEN
187 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
188 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.UPDATE_ORIG_REC');
189 igs_ge_msg_stack.add;
190 app_exception.raise_exception;
191
192 END update_orig_rec;
193
194 PROCEDURE prepare_data
195 ( p_rfms_rec cur_rfms%ROWTYPE,
196 p_num_of_records IN OUT NOCOPY NUMBER,
197 p_rfmb_id IN igf_gr_rfms_batch.rfmb_id%TYPE,
198 p_originated IN OUT NOCOPY VARCHAR2,
199 p_enrl_status_mesgnum IN NUMBER ) IS
200 ------------------------------------------------------------------------
201 --
202 -- Created By : sjadhav
203 --
204 -- Date Created On : 2001/01/03
205 -- Purpose :This procedure loads the record data into datafile
206 -- Know limitations, enhancements or remarks
207 -- Change History:
208 -- Bug 2460904 Desc:Pell Formatting Issues
209 -- Who When What
210 -- ugummall 04-DEC-2003 Bug 3252832. FA 131 - COD Updates.
211 -- 1. Added one parameter p_enrl_status_mesgnum to avoid bug(which may arise
212 -- in future.
213 -- 2. Moved code up and down to improve performance and clarity.
214 -- mesriniv 22-jul-2002 Added IF Condition for Payment Method and Calendar.
215 -- (reverse chronological order - newest change first)
216 --
217 -----------------------------------------------------------------------
218
219 l_data VARCHAR2(1000);
220 l_wk_inst_time_calc_pymt VARCHAR2(5);
221 l_wk_int_time_prg_def_yr VARCHAR2(5);
222 l_cr_clk_hrs_acad_yr VARCHAR2(5);
223 l_cr_clk_hrs_prds_sch_yr VARCHAR2(5);
224 l_enroll_stat VARCHAR2(1);
225
226 l_person_id hz_parties.party_id%TYPE;
227 student_dtl_cur igf_sl_gen.person_dtl_cur;
228 student_dtl_rec igf_sl_gen.person_dtl_rec;
229
230 -- This cursor gets disb_dates from igf_gr_rfms_disb
231 -- for a particualr origination
232 CURSOR cur_disb(l_orig_id igf_gr_rfms_disb.origination_id%TYPE) IS
233 SELECT disb_dt
234 FROM igf_gr_rfms_disb
235 WHERE origination_id = l_orig_id
236 ORDER BY disb_ref_num;
237 l_disbursement_dates VARCHAR2(400);
238
239 BEGIN
240
241 --
242 -- Bug No : 1747297
243 --
244 -- Do not create origination record if any of the following field is null
245 -- efc, pell award, total payment periods, academic
246 -- calendar, payment mehtod, scheduled award, enrollment status, enrollment
247 -- date, first disb date and transaction number.
248 -- write into log file which origiantion id were not put into file
249 --
250
251 IF p_rfms_rec.efc IS NULL OR
252 p_rfms_rec.pell_amount IS NULL OR
253 p_rfms_rec.ft_pell_amount IS NULL OR
254 p_rfms_rec.transaction_num IS NULL OR
255 p_rfms_rec.academic_calendar IS NULL OR
256 p_rfms_rec.payment_method IS NULL OR
257 p_rfms_rec.est_disb_dt1 IS NULL OR
258 p_rfms_rec.total_pymt_prds IS NULL OR
259 p_rfms_rec.enrollment_status IS NULL OR
260 p_rfms_rec.enrollment_dt IS NULL
261 THEN
262 fnd_file.new_line(fnd_file.log,1);
263 fnd_message.set_name('IGF','IGF_GR_ORIG_DATA_REQD');
264 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(p_rfms_rec.base_id));
265 fnd_file.put_line(fnd_file.log,fnd_message.get);
266 fnd_file.new_line(fnd_file.log,1);
267 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','EFC'),50) || ' : ' ||p_rfms_rec.efc);
268 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','PELL_AMOUNT'),50) || ' : ' ||p_rfms_rec.pell_amount);
269 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','FT_PELL_AMOUNT'),50) || ' : ' ||p_rfms_rec.ft_pell_amount);
270 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','TRANSACTION_NUM'),50) || ' : ' ||p_rfms_rec.transaction_num);
271 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ACADEMIC_CALENDAR'),50) || ' : ' ||p_rfms_rec.academic_calendar);
272 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','PAYMENT_METHOD'),50) || ' : ' ||p_rfms_rec.payment_method);
273 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','EST_DISB_DT1'),50) || ' : ' ||p_rfms_rec.est_disb_dt1);
274 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','TOTAL_PYMT_PRDS'),50) || ' : ' ||p_rfms_rec.total_pymt_prds);
275 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ENROLLMENT_STATUS'),50) || ' : ' ||p_rfms_rec.enrollment_status);
276 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ENROLLMENT_DT'),50) || ' : ' ||p_rfms_rec.enrollment_dt);
277 -- 'Data not sufficient to create Origination Record for Person '
278 fnd_file.new_line(fnd_file.log,1);
279 ELSE
280
281 --Set the value for Printing Header Record as 'Y' only if its 'N'
282 --Bug 2460904
283 IF g_print_header ='N' THEN
284 g_print_header :='Y';
285 END IF;
286
287 --Following IF conditions have been added as per bug 2460904
288 --It is done with respect to the Pell Tech Document for 2002-2003
289 --For Name Fields made as UPPER
290
291 IF p_rfms_rec.payment_method = '1' THEN
292 l_wk_inst_time_calc_pymt := LPAD(' ',2,' ');
293 l_wk_int_time_prg_def_yr := LPAD(' ',2,' ');
294 ELSE
295 l_wk_inst_time_calc_pymt := LPAD(p_rfms_rec.wk_inst_time_calc_pymt,2,'0');
296 l_wk_int_time_prg_def_yr := LPAD(p_rfms_rec.wk_int_time_prg_def_yr,2,'0');
297 END IF;
298
299 IF p_rfms_rec.payment_method ='4' THEN
300 l_enroll_stat :=' ';
301 ELSE
302 l_enroll_stat := p_rfms_rec.enrollment_status;
303 END IF;
304
305 IF p_rfms_rec.academic_calendar IN ('1','2','3','4') THEN
306 l_cr_clk_hrs_acad_yr := LPAD(' ',4,' ');
307 l_cr_clk_hrs_prds_sch_yr := LPAD(' ',4,' ');
308 ELSE
309 l_cr_clk_hrs_acad_yr := LPAD(p_rfms_rec.cr_clk_hrs_acad_yr,4,'0');
310 l_cr_clk_hrs_prds_sch_yr := LPAD(NVL(p_rfms_rec.cr_clk_hrs_prds_sch_yr,'0'),4,'0');
311 END IF;
312
313 -- Prepare disbursement dates.
314 l_disbursement_dates := NULL;
315 FOR rec_disb IN cur_disb(p_rfms_rec.origination_id) LOOP
316 l_disbursement_dates := l_disbursement_dates || RPAD(NVL(TO_CHAR(rec_disb.disb_dt,'YYYYMMDD'),' '),8);
317 END LOOP;
318
319 -- Get the person details by calling the procedure
320 l_person_id := igf_gr_gen.get_person_id( P_BASE_ID => p_rfms_rec.base_id);
321
322 student_dtl_rec := NULL;
323 igf_sl_gen.get_person_details(l_person_id, student_dtl_cur);
324 FETCH student_dtl_cur INTO student_dtl_rec;
325 CLOSE student_dtl_cur;
326
327 l_data := NULL;
328 l_data := RPAD(NVL(p_rfms_rec.origination_id,' '),23,' ') ||
329 RPAD(NVL(p_rfms_rec.sys_orig_ssn,' '),9,' ') || -- Original SSN from FAFSA
330 RPAD(NVL(p_rfms_rec.sys_orig_name_cd,' '),2,' ') ||
331 RPAD(NVL(p_rfms_rec.attending_campus_id,' '),6,' ') ||
332 RPAD(' ',5,' ') || -- ED Use only
333 RPAD(NVL(p_rfms_rec.inst_cross_ref_cd,' '),13,' ') ||
334 RPAD(' ',1,' ') || -- Action Code
335 RPAD(' ',1,' ') || -- Unused
336 LPAD(TO_CHAR(ABS(100*NVL(p_rfms_rec.pell_amount,0))),7,'0') || -- Amount Awarded to the Student
337 RPAD(NVL(l_disbursement_dates,' '),120,' ') ||
338 RPAD(NVL(TO_CHAR(p_rfms_rec.enrollment_dt,'YYYYMMDD'),' '),8,' ') ||
339 RPAD(NVL(p_rfms_rec.low_tution_fee,' '),1,' ') ||
340 RPAD(NVL(p_rfms_rec.ver_status_code,' '),1,' ') ||
341 RPAD(NVL(p_rfms_rec.incrcd_fed_pell_rcp_cd,' '),1,' ') ||
342 RPAD(NVL(p_rfms_rec.transaction_num,' '),2,' ') ||
343 LPAD(TO_CHAR(ROUND(ABS(NVL(p_rfms_rec.efc,0)))),5,'0') ||
344 RPAD(NVL(p_rfms_rec.secondary_efc_cd,' '),1,' ') ||
345 RPAD(NVL(p_rfms_rec.academic_calendar,' '),1,' ') ||
346 RPAD(NVL(p_rfms_rec.payment_method,' '),1,' ') ||
347 LPAD(TO_CHAR(100*ABS(NVL(p_rfms_rec.coa_amount,0))),7,'0') ||
348 l_enroll_stat ||
349 l_wk_inst_time_calc_pymt ||
350 l_wk_int_time_prg_def_yr ||
351 l_cr_clk_hrs_acad_yr ||
352 l_cr_clk_hrs_prds_sch_yr ||
353 RPAD(' ',3,' ') || -- Inst. Internal sequence no.
354 RPAD(NVL(student_dtl_rec.p_ssn,' '),9,' ') || -- Current SSN of the Student
355 RPAD(NVL(TO_CHAR(student_dtl_rec.p_date_of_birth,'YYYYMMDD'),' '),8,' ') ||
356 RPAD(NVL(UPPER(student_dtl_rec.p_last_name),' '),16,' ') ||
357 RPAD(NVL(UPPER(student_dtl_rec.p_first_name),' '),12,' ') ||
358 RPAD(NVL(UPPER(student_dtl_rec.p_middle_name),' '),1,' ') ||
359 RPAD(' ',23,' '); -- Unused
360
361 -- The length of this record is 300
362
363 --
364 -- Bug 2383690
365 -- If NOT Originating for First Time
366 -- If fed verf stat is W, do not send
367 -- log message
368 --
369
370 IF igf_gr_gen.send_orig_disb( p_rfms_rec.origination_id) THEN
371 --Print Header for the first time and reset value so that it does not get printed further
372 --Bug 2460904
373 IF g_print_header='Y' THEN
374 fnd_file.put_line(fnd_file.output,UPPER(l_header)||RPAD(' ',200));
375 g_print_header:='Z';
376 END IF;
377
378 -- Since we are going to write the record to file, print log message if we had been set enrollment_status.
379 IF p_enrl_status_mesgnum = 1 THEN
380 -- default to 'Others'
381 fnd_message.set_name('IGF','IGF_GR_DEFAULT_ENRL_STAT');
382 fnd_message.set_token('EN_STAT',igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT', p_rfms_rec.enrollment_status));
383 fnd_file.put_line(fnd_file.log,fnd_message.get);
384 ELSIF p_enrl_status_mesgnum = 2 THEN
385 -- set to pell attendance code.
386 fnd_message.set_name('IGF','IGF_GR_SET_ENRL_STAT');
387 fnd_message.set_token('EN_STAT',igf_aw_gen.lookup_desc('IGF_GR_RFMS_ENROL_STAT', p_rfms_rec.enrollment_status));
388 fnd_file.put_line(fnd_file.log,fnd_message.get);
389 END IF;
390
391 fnd_file.put_line(fnd_file.output,UPPER(l_data));
392 p_num_of_records := p_num_of_records + 1;
393 p_originated :='Y';
394 update_orig_rec(p_rfms_rec,p_rfmb_id);
395 ELSE
396 fnd_message.set_name('IGF','IGF_GR_VERF_STAT_W');
397 fnd_message.set_token('ORIG_ID',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID') ||
398 ' : ' || p_rfms_rec.origination_id );
399 fnd_file.put_line(fnd_file.log,fnd_message.get);
400 p_originated :='N';
401 END IF;
402 END IF;
403
404 EXCEPTION
405
406 WHEN igf_gr_gen.no_file_version THEN
407 RAISE;
408
409 WHEN OTHERS THEN
410 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
411 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.PREPARE_DATA');
412 igs_ge_msg_stack.add;
413 app_exception.raise_exception;
414
415 END prepare_data;
416
417
418 PROCEDURE log_message
419 (p_batch_id VARCHAR2,
420 p_origination_id igf_gr_rfms.origination_id%TYPE ) IS
421
422 ---------------------------------------------------------------------
423 --
424 -- Created By : sjadhav
425 --
426 -- Date Created On : 2001/01/03
427 -- Purpose :This procedure formats the messages to be put into
428 -- log file.
429 -- Know limitations, enhancements or remarks
430 -- Change History
431 -- Who When What
432 --
433 -- (reverse chronological order - newest change first)
434 --
435 -----------------------------------------------------------------------
436
437 l_msg_str_0 VARCHAR2(1000);
438 l_msg_str_1 VARCHAR2(1000);
439
440 BEGIN
441
442 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BATCH_ID'),50) ||
443 RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50);
444 l_msg_str_1 := RPAD(p_batch_id,50) ||
445 RPAD(p_origination_id,50);
446
447 fnd_file.put_line(fnd_file.log,' ');
448 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
449 fnd_file.put_line(fnd_file.log,fnd_message.get);
450 fnd_file.put_line(fnd_file.log,' ');
451 fnd_file.put_line(fnd_file.log,l_msg_str_0);
452 fnd_file.put_line(fnd_file.log,RPAD('-',100,'-'));
453 fnd_file.put_line(fnd_file.log,l_msg_str_1);
454
455
456 EXCEPTION
457 WHEN others THEN
458 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
459 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
460 igs_ge_msg_stack.add;
461 app_exception.raise_exception;
462
463 END log_message;
464
465
466 PROCEDURE out_message(p_origination_id igf_gr_rfms.origination_id%TYPE) IS
467
468 ---------------------------------------------------------------------
469 --
470 -- Created By : sjadhav
471 --
472 -- Date Created On : 2001/01/03
473 -- Purpose :This procedure formats the messages to be put into
474 -- log file.
475 -- Know limitations, enhancements or remarks
476 -- Change History
477 -- Who When What
478 --
479 -- (reverse chronological order - newest change first)
480 --
481 -----------------------------------------------------------------------
482
483 l_msg_str_0 VARCHAR2(1000);
484 l_msg_str_1 VARCHAR2(1000);
485
486 BEGIN
487
488
489
490 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50);
491 l_msg_str_1 := RPAD(p_origination_id,50);
492
493 IF l_msg_prn_1 = TRUE THEN
494 fnd_message.set_name('IGF','IGF_GR_RECORDS_UPDATED');
495 fnd_file.put_line(fnd_file.output,fnd_message.get);
496 fnd_file.put_line(fnd_file.output,'');
497 fnd_file.put_line(fnd_file.output,l_msg_str_0);
498 fnd_file.put_line(fnd_file.output,RPAD('-',50,'-'));
499 l_msg_prn_1 := FALSE;
500 END IF;
501 fnd_file.put_line(fnd_file.output,l_msg_str_1);
502
503
504 EXCEPTION
505 WHEN others THEN
506 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
507 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
508 igs_ge_msg_stack.add;
509 app_exception.raise_exception;
510
511
512 END out_message;
513
514
515
516 PROCEDURE log_rej_message(p_origination_id igf_gr_rfms.origination_id%TYPE,
517 p_orig_reject_code igf_gr_rfms.orig_reject_code%TYPE) IS
518
519 ---------------------------------------------------------------------
520 --
521 -- Created By : sjadhav
522 --
523 -- Date Created On : 2001/01/03
524 -- Purpose :This procedure formats the messages to be put into
525 -- log file.
526 -- Know limitations, enhancements or remarks
527 -- Change History
528 -- Who When What
529 --
530 -- (reverse chronological order - newest change first)
531 --
532 -----------------------------------------------------------------------
533
534 l_msg_str_0 VARCHAR2(1000) DEFAULT NULL;
535 l_msg_str_1 VARCHAR2(1000) DEFAULT NULL;
536 l_msg_str_2 VARCHAR2(1000) DEFAULT NULL;
537 l_msg_str_3 VARCHAR2(1000) DEFAULT NULL;
538
539
540 l_count NUMBER DEFAULT 1;
541 l_error_code igf_gr_rfms_error.edit_code%TYPE;
542 l_msg_desc VARCHAR2(4000) DEFAULT NULL;
543
544 BEGIN
545
546 fnd_file.put_line(fnd_file.log,'');
547 IF NVL(TO_NUMBER(RTRIM(LTRIM(p_orig_reject_code))),0) > 0 THEN
548 fnd_message.set_name('IGF','IGF_GR_REC_CONTAIN_EDIT_CODES');
549 fnd_file.put_line(fnd_file.log,fnd_message.get);
550
551 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50);
552 l_msg_str_1 := RPAD(p_origination_id,50);
553 l_msg_str_2 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIG_REJECT_CODE'),50);
554
555 fnd_file.put_line(fnd_file.log,' ');
556 fnd_file.put_line(fnd_file.log,l_msg_str_0);
557 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
558 fnd_file.put_line(fnd_file.log,l_msg_str_1);
559 fnd_file.put_line(fnd_file.log,l_msg_str_2);
560 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
561
562 FOR l_cn IN 1 .. 25 LOOP
563
564 l_error_code := NVL(SUBSTR(p_orig_reject_code,l_count,3),'000');
565 IF l_error_code <> '000' THEN
566 l_msg_str_3 := RPAD(l_error_code,5);
567 fnd_file.put_line(fnd_file.log,l_msg_str_3);
568 END IF;
569 l_count := l_count + 3;
570 END LOOP;
571 END IF;
572
573
574 EXCEPTION
575 WHEN others THEN
576 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
577 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
578 igs_ge_msg_stack.add;
579 app_exception.raise_exception;
580
581
582 END log_rej_message;
583
584
585 PROCEDURE rfms_orig(
586 p_ci_cal_type IN VARCHAR2,
587 p_ci_sequence_number IN NUMBER,
588 p_base_id IN VARCHAR2,
589 p_reporting_pell IN VARCHAR2,
590 p_attending_pell IN VARCHAR2,
591 p_persid_grp IN VARCHAR2,
592 p_orig_run_mode IN VARCHAR2
593 ) IS
594 --------------------------------------------------------------------------
595 --
596 -- Created By : sjadhav
597 --
598 -- Date Created On : 2001/01/03
599 -- Purpose : This procedure reads the data from igf_gr_rfms table
600 -- for the paricular award year and base id and loads the data
601 -- into a datafile after formatting
602 --
603 -- Know limitations, enhancements or remarks
604 -- Change History
605 -- Who When What
606 -- ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
607 -- bkkumar 23-Mar-2004 Bug# 3512319 If run_mode = 'MAX_PELL' then rfms_rec.coa_amount is made 99999.
608 -- rasahoo 13-Feb-2004 Bug # 3441605 Changed The cursor "cur_get_attendance_type_code" to
609 -- "cur_base_attendance_type_code". Now it will select
610 -- "base_attendance_type_code" instead of "attendance_type_code".
611 -- Removed cursor "cur_get_pell_att_code" as it is no longer used.
612 -- ugummall 12-DEC-2003 Bug 3252832. FA 131 - COD Updates.
613 -- enrollment_status is derived only if run mode is ACTUAL_PELL. Otherwise
614 -- it is defaulted to '1' ie full-time.
615 -- ugummall 10-DEC-2003 Bug 3252832. FA 131 - COD Updates.
616 -- Added validation - Amount should not be less than sum of disbursement
617 -- amounts. Added cursor cur_disb_amt_tot.
618 -- ugummall 04-DEC-2003 Bug 3252832. FA 131 - COD Updates.
619 -- 1. Added two extra parameters namely p_persid_grp
620 -- and p_orig_run_mode to this procedure.
621 -- 2. Enrollment status derived instead of dafaulting to '1'. Added two cursors
622 -- for this. cur_get_attendance_type_code and cur_get_pell_att_code.
623 -- 3. Earlier enrollment status defaulting to '1' may print message though context
624 -- record may not be written to output file. Avoided that bug.
625 -- 4. Pell calculation logic moved little down to improve the performance.
626 -- 5. Used a different wrapper to calculate pell w.r.t. FA 131.
627 -- 6. Used ref cursor reference cursor refcur_persid_check for person-id-group logic.
628 -- ugummall 04-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
629 -- 1. Added two extra parameters namely p_reporting_pell
630 -- and p_attending_pell to this procedure.
631 -- 2. Added cursor cur_attending_pell. Added check for attending pell
632 -- is a child of reporting pell. If not skipped the record.
633 -- 3. Parameters are shown in log file irrespective of wether cur_rfms
634 -- fetches the records or not. This is done for clarity.
635 -- ugummall 03-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
636 -- Added two extra parameters to igf_gr_gen.get_pell_header call.
637 -- rasahoo 16-Oct-2003 FA121-Bug# 3085558 cur_pymnt_isir_rec is initialised to null
638 -- Changed the logic to check the presence of payment ISIR
639 -- and log message if payment isir not present
640 -- cdcruz 15-Sep-03 FA121-Bug# 3085558 New Cursor added cur_pymnt_isir
641 -- That checks for the Transaction Number
642 -- On the Payment ISIR
643 -- (reverse chronological order - newest change first)
644 --
645 -------------------------------------------------------------------------------
646
647 CURSOR cur_pymnt_isir( cp_base_id igf_gr_rfms.base_id%TYPE ) IS
648 SELECT isir.transaction_num
649 FROM igf_ap_isir_matched isir
650 WHERE isir.base_id = cp_base_id
651 AND isir.payment_isir = 'Y' ;
652
653 cur_pymnt_isir_rec cur_pymnt_isir%rowtype;
654
655 -- Cursor to check attending pell id is a child of reporting pell id
656 CURSOR cur_attending_pell(cp_ci_cal_type igf_gr_report_pell.ci_cal_type%TYPE,
657 cp_ci_sequence_number igf_gr_report_pell.ci_sequence_number%TYPE,
658 cp_reporting_pell igf_gr_report_pell.reporting_pell_cd%TYPE,
659 cp_attending_pell igf_gr_attend_pell.attending_pell_cd%TYPE)
660 IS
661 SELECT 'Y'
662 FROM igf_gr_report_pell rep,
663 igf_gr_attend_pell att
664 WHERE rep.rcampus_id = att.rcampus_id
665 AND rep.ci_cal_type = cp_ci_cal_type
666 AND rep.ci_sequence_number = cp_ci_sequence_number
667 AND rep.reporting_pell_cd = cp_reporting_pell
668 AND att.attending_pell_cd = cp_attending_pell;
669
670 l_attending_pell_exists cur_attending_pell%ROWTYPE;
671
672 l_record VARCHAR2(4000);
673 l_trailer VARCHAR2(1000);
674 l_num_of_rec NUMBER DEFAULT 0;
675 l_amount_total NUMBER DEFAULT 0;
676 l_batch_id VARCHAR2(60);
677 l_originated VARCHAR2(1);
678
679 p_rfmb_id igf_gr_rfms_batch_all.rfmb_id%TYPE;
680 l_ft_pell_amt igf_gr_rfms_all.ft_pell_amount%TYPE;
681 l_pell_mat VARCHAR2(10);
682 l_isir_present BOOLEAN := FALSE;
683
684 -- FA 131 - COD Updates Build cursors and variables. 03-DEC-2003 ugummall.
685 CURSOR cur_base_attendance_type_code(cp_award_id igf_aw_awd_disb_all.award_id%TYPE) IS
686 SELECT base_attendance_type_code
687 FROM igf_aw_awd_disb_all
688 WHERE award_id = cp_award_id
689 GROUP BY base_attendance_type_code;
690 rec_base_attendance_type_code cur_base_attendance_type_code%ROWTYPE;
691
692 CURSOR cur_disb_amt_tot(cp_origination_id igf_gr_rfms_disb.origination_id%TYPE) IS
693 SELECT sum(disb_amt) disb_amt_tot
694 FROM igf_gr_rfms_disb
695 WHERE origination_id = cp_origination_id;
696 rec_disb_amt_tot cur_disb_amt_tot%ROWTYPE;
697
698 TYPE PersonIdGroupType IS REF CURSOR ;
699 refcur_persid_check PersonIdGroupType;
700 lv_persid NUMBER(1);
701
702 l_pell_amt igf_gr_rfms.pell_amount%TYPE;
703 l_return_status VARCHAR2(1);
704 l_return_mesg_text VARCHAR2(2000);
705
706 -- Variables for the dynamic person id group
707 lv_status VARCHAR2(1);
708 lv_sql_stmt VARCHAR(32767) ;
709
710 lv_persid_flag BOOLEAN;
711 l_enrl_status_mesgnum NUMBER(1) DEFAULT 0;
712 -- End FA 131.
713
714 --Added for bug 3490915
715 -- Get sum of pell award amount
716 CURSOR c_awd_disb_tot(
717 cp_award_id igf_aw_award_all.award_id%TYPE
718 ) IS
719 SELECT DECODE( accepted_amt,0,offered_amt,NULL,offered_amt,accepted_amt) pell_award
720 FROM igf_aw_award awd
721 WHERE awd.award_id = cp_award_id
722 AND awd.award_status IN ('OFFERED','ACCEPTED');
723 l_awd_disb_tot c_awd_disb_tot%ROWTYPE;
724
725 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
726
727 BEGIN
728
729 lv_status := 'S'; -- Defaulted to 'S' and the function will return 'F' in case of failure
730 l_originated := 'N';
731 -- FA 126. Passed extra parameters p_reporting_pell and p_attending_pell to this cursor.
732 OPEN cur_rfms(p_base_id,p_ci_cal_type,p_ci_sequence_number, p_reporting_pell, p_attending_pell);
733 FETCH cur_rfms INTO rfms_rec;
734
735 -- If the table does not contain any data for this base_id or award_year
736 -- message is logged into log file and relevent details are also shown
737 IF cur_rfms%NOTFOUND THEN
738 CLOSE cur_rfms;
739 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
740 igs_ge_msg_stack.add;
741
742 RAISE no_data_in_table;
743 END IF;
744
745 -- since the table has data, prepare a header record
746 -- igf_gr_gen.get_pell_header will insert a header record which needs to be deleted(rolledback)
747 -- if no records have been written to output file. This is handled in batch_not_created exception handling
748
749 l_header := igf_gr_gen.get_pell_header(
750 g_ver_num,
751 l_cy_yr,
752 p_reporting_pell,
753 '#O',
754 p_rfmb_id,
755 l_batch_id,
756 p_ci_cal_type,
757 p_ci_sequence_number
758 );
759
760 fnd_file.new_line(fnd_file.log,1);
761 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BATCH_ID'),10)||' :'
762 ||' '|| l_batch_id);
763
764 fnd_file.new_line(fnd_file.log,1);
765 fnd_message.set_name('IGF','IGF_GR_ORIG_RECORDS');
766 fnd_file.put_line(fnd_file.log,fnd_message.get);
767
768 l_amount_total := 0;
769 l_num_of_rec := 0;
770
771 -- get the sql stantement which returns list of person-ids for a given person-id-group.
772 -- Bug #5021084
773 lv_sql_stmt := igf_ap_ss_pkg.get_pid(p_persid_grp, lv_status, lv_group_type);
774
775 IF (lv_status <> 'S') THEN
776 -- Stop processing.
777 RAISE persid_grp_sql_stmt_error;
778 END IF;
779
780 LOOP
781 -- FA 131. Check wether to consider the context record or not based on person-id-group.
782 -- lv_persid_flag = TRUE means consider the record and process it.
783 -- lv_persid_flag = FALSE means not the intended record. skip the record.
784 lv_persid_flag := TRUE;
785 IF (p_persid_grp IS NOT NULL) THEN
786 --Bug #5021084. Passing Group ID if the group type is STATIC.
787 IF lv_group_type = 'STATIC' THEN
788 OPEN refcur_persid_check FOR 'SELECT 1
789 FROM igf_ap_fa_base_rec fabase
790 WHERE fabase.base_id = :base_id
791 AND fabase.person_id in ( '||lv_sql_stmt||') ' USING rfms_rec.base_id,p_persid_grp;
792 ELSIF lv_group_type = 'DYNAMIC' THEN
793 OPEN refcur_persid_check FOR 'SELECT 1
794 FROM igf_ap_fa_base_rec fabase
795 WHERE fabase.base_id = :base_id
796 AND fabase.person_id in ( '||lv_sql_stmt||') ' USING rfms_rec.base_id;
797 END IF;
798
799 FETCH refcur_persid_check INTO lv_persid;
800 IF refcur_persid_check%NOTFOUND THEN
801 lv_persid_flag := FALSE;
802 END IF;
803 END IF;
804
805 IF (lv_persid_flag) THEN -- Is person-id in person-group or not ?
806 fnd_file.new_line(fnd_file.log,1);
807 fnd_message.set_name('IGF','IGF_GR_PROCESS_STUD');
808 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(rfms_rec.base_id));
809 fnd_message.set_token('ORIG_ID',rfms_rec.origination_id);
810 fnd_file.put_line(fnd_file.log,fnd_message.get);
811
812 -- check if attending pell is a child of reporting pell id.
813 l_attending_pell_exists := NULL;
814 OPEN cur_attending_pell(rfms_rec.ci_cal_type,
815 rfms_rec.ci_sequence_number,
816 rfms_rec.rep_pell_id,
817 rfms_rec.attending_campus_id);
818 FETCH cur_attending_pell INTO l_attending_pell_exists;
819 IF (cur_attending_pell%NOTFOUND) THEN -- Attending pell child records exists?
820
821 -- No attending pell child record exists. Skip this record
822 CLOSE cur_attending_pell;
823 FND_MESSAGE.SET_NAME('IGF','IGF_GR_ATTEND_PELL_NOT_SETUP');
824 FND_MESSAGE.SET_TOKEN('ATT_PELL','');
825 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
826 ELSE
827 -- attending pell child record exists. Proceed further...
828 CLOSE cur_attending_pell;
829
830 --
831 -- sjadhav
832 -- Bug 2460904
833 -- Before sending origination check for the ft pell amount and
834 -- pell award amount. if pell award amount is more than ft pell amount
835 -- do not send this origination record, log a message and skip
836 --
837
838 -- 12-DEC-2003. FA 131. ugummall
839 -- If the run mode FULL_TIME or MAX_PELL enrollment_status will be full time ie '1'
840 -- irrespective of its old value. Derive enrollment_status only if the run mode is ACTUAL_PELL.
841 -- Prepare Enrollment Status field if it is NULL.
842 l_enrl_status_mesgnum := 0;
843 IF (p_orig_run_mode = 'FULL_TIME' OR p_orig_run_mode = 'MAX_PELL') THEN
844 rfms_rec.enrollment_status := '1'; -- 1 stands for Pell Attendance full time
845 ELSE
846 -- p_orig_run_mode is 'ACTUAL_PELL'
847
848 IF rfms_rec.enrollment_status IS NULL THEN
849 -- FA 131 Build. 03-DEC-2003. Preparing enrollment_status field.
850 -- Earlier Enrollment status is defaulted to '1' when it is null.
851 -- w.r.t. FA 131, it is derived in the following way.
852 OPEN cur_base_attendance_type_code(rfms_rec.award_id);
853 FETCH cur_base_attendance_type_code INTO rec_base_attendance_type_code;
854
855 -- It returns one or more records. Never returns zero records.
856 IF (cur_base_attendance_type_code%ROWCOUNT > 1) THEN
857 rfms_rec.enrollment_status := '5'; -- 5 for Pell Attendance "Others"
858 l_enrl_status_mesgnum := 1;
859 ELSIF (rec_base_attendance_type_code.base_attendance_type_code IS NULL) THEN
860 -- cursor returned 1 row. And attendance_type_code is null
861 rfms_rec.enrollment_status := '5'; -- 5 for Pell Attendance "Others"
862 l_enrl_status_mesgnum := 1;
863 ELSE
864 -- cursor returned 1 row. And attendance_type_code is not null
865 rfms_rec.enrollment_status := rec_base_attendance_type_code.base_attendance_type_code;
866 l_enrl_status_mesgnum := 2;
867 END IF;
868 CLOSE cur_base_attendance_type_code;
869
870 -- NOTE: enrl_status_errnum value either 1 or 2 means some value is assigned to enrollment_status.
871 -- Need to convey that mesg in log file, saying enrollment_status set to so and so, only if
872 -- that record is going to write into the output file.
873 END IF; -- End of Preparing enrollment status field
874 END IF;
875
876 -- Get The Payment ISIR Transaction Number
877 cur_pymnt_isir_rec := NULL;
878 l_isir_present := TRUE;
879 OPEN cur_pymnt_isir(rfms_rec.base_id);
880 FETCH cur_pymnt_isir INTO cur_pymnt_isir_rec;
881 IF cur_pymnt_isir%NOTFOUND THEN
882 l_isir_present := FALSE;
883 END IF;
884 CLOSE cur_pymnt_isir;
885
886 IF NOT l_isir_present THEN
887 fnd_message.set_name('IGF','IGF_AP_NO_PAYMENT_ISIR');
888 fnd_file.put_line(fnd_file.log,fnd_message.get);
889
890 -- If the Transaction Number being reported does not match do not Originate
891 ELSIF rfms_rec.transaction_num <> NVL(cur_pymnt_isir_rec.transaction_num,-1) THEN
892 fnd_message.set_name('IGF','IGF_GR_PYMNT_ISIR_MISMATCH');
893 fnd_file.put_line(fnd_file.log,fnd_message.get);
894
895 ELSE
896
897 -- Get disbursements amounts total
898 rec_disb_amt_tot := NULL;
899 OPEN cur_disb_amt_tot(rfms_rec.origination_id);
900 FETCH cur_disb_amt_tot INTO rec_disb_amt_tot;
901 CLOSE cur_disb_amt_tot;
902
903 -- If origination record's amount is less than sum of disbursement amounts, then do not originate.
904 IF (rfms_rec.pell_amount < rec_disb_amt_tot.disb_amt_tot) THEN
905 fnd_message.set_name('IGF','IGF_GR_PELL_DIFF_AMTS');
906 FND_MESSAGE.SET_TOKEN('DISB_AMT', TO_CHAR(rec_disb_amt_tot.disb_amt_tot));
907 FND_MESSAGE.SET_TOKEN('PELL_TOT', TO_CHAR(rfms_rec.pell_amount));
908
909 fnd_file.put_line(fnd_file.log,fnd_message.get);
910 ELSE
911
912 l_awd_disb_tot := NULL;
913 OPEN c_awd_disb_tot(rfms_rec.award_id);
914 FETCH c_awd_disb_tot INTO l_awd_disb_tot;
915 CLOSE c_awd_disb_tot;
916
917 IF NVL(l_awd_disb_tot.pell_award,-1) <> rec_disb_amt_tot.disb_amt_tot AND p_orig_run_mode = 'ACTUAL_PELL' THEN
918 --put messages here
919 fnd_message.set_name('IGF','IGF_GR_PELL_AWD_DIFF_AMT');
920 fnd_message.set_token('RFMS_DISB_TOT',TO_CHAR(rec_disb_amt_tot.disb_amt_tot));
921 fnd_message.set_token('AWD_DISB_TOT',TO_CHAR(NVL(l_awd_disb_tot.pell_award,0)));
922 fnd_file.put_line(fnd_file.log,fnd_message.get);
923
924 ELSE
925
926 -- Calculate Pell amount and Full time Pell amount
927 igf_gr_pell_calc.calc_ft_max_pell(cp_base_id => rfms_rec.base_id,
928 cp_cal_type => rfms_rec.ci_cal_type,
929 cp_sequence_number => rfms_rec.ci_sequence_number,
930 cp_flag => p_orig_run_mode,
931 cp_aid => l_pell_amt,
932 cp_ft_aid => l_ft_pell_amt,
933 cp_return_status => l_return_status,
934 cp_message => l_return_mesg_text
935 );
936 IF (l_return_status = 'E') THEN
937 fnd_file.put_line(fnd_file.log,l_return_mesg_text);
938 ELSE
939 IF l_pell_amt > l_ft_pell_amt THEN
940 fnd_message.set_name('IGF','IGF_GR_LIMIT_EXC');
941 fnd_message.set_token('PEL_AMT',l_ft_pell_amt);
942 fnd_message.set_token('AWD_AMT',rfms_rec.pell_amount);
943 fnd_file.put_line(fnd_file.log,fnd_message.get);
944 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
945 fnd_file.put_line(fnd_file.log,fnd_message.get);
946 ELSE
947 rfms_rec.pell_amount := l_pell_amt;
948 rfms_rec.ft_pell_amount := l_ft_pell_amt;
949 IF (p_orig_run_mode = 'MAX_PELL') THEN
950 rfms_rec.efc := 0;
951 rfms_rec.coa_amount := 99999; --Bug 3512319
952 ELSE
953 rfms_rec.efc := igf_gr_gen.get_pell_efc(rfms_rec.base_id);
954 END IF;
955 prepare_data(rfms_rec,l_num_of_rec,p_rfmb_id,l_originated, l_enrl_status_mesgnum);
956
957 --Bug 2460904
958 --Trailer printed l_amount_total wrongly as it calculated
959 --even for records rejected by the prepare data procedure.
960 --Modified code based on whether a record is rejected or sent by prepare_data procedure.
961 --Added parameter l_originated
962
963 IF l_originated ='Y' THEN
964 l_amount_total := l_amount_total + NVL(rfms_rec.pell_amount,0);
965 l_originated :='N';
966 END IF;
967 END IF;
968 END IF;
969 END IF;
970 END IF;
971 END IF;
972 END IF; -- Attending pell child records exists?
973 END IF; -- Is person-id in person-group or not ?
974
975 FETCH cur_rfms INTO rfms_rec;
976 EXIT WHEN cur_rfms%NOTFOUND;
977 END LOOP;
978 CLOSE cur_rfms;
979
980 -- since the table has data, prepare a trailer record
981 l_trailer := igf_gr_gen.get_pell_trailer(g_ver_num,
982 l_cy_yr,
983 p_reporting_pell,
984 '#O',
985 l_num_of_rec,
986 l_amount_total,
987 l_batch_id);
988 fnd_file.new_line(fnd_file.log,1);
989 fnd_message.set_name('IGF','IGF_GR_ORIG_REC_NUM');
990 fnd_message.set_token('TOT_NUM',TO_CHAR(l_num_of_rec));
991 fnd_file.put_line(fnd_file.log,fnd_message.get);
992 -- p_num_of_rec || ' records written into data file
993 fnd_file.new_line(fnd_file.log,1);
994
995 IF l_num_of_rec > 0 THEN
996 fnd_file.put_line(fnd_file.output,UPPER(l_trailer)||RPAD(' ',200));
997 ELSE
998 RAISE batch_not_created;
999 END IF;
1000
1001 EXCEPTION
1002 WHEN igf_gr_gen.no_file_version THEN
1003 RAISE;
1004 WHEN batch_not_created THEN
1005 RAISE;
1006 WHEN no_data_in_table THEN
1007 RAISE;
1008 WHEN persid_grp_sql_stmt_error THEN
1009 RAISE;
1010 WHEN OTHERS THEN
1011 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1012 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.RFMS_ORIG');
1013 igs_ge_msg_stack.add;
1014 app_exception.raise_exception;
1015
1016 END rfms_orig;
1017
1018
1019 PROCEDURE rfms_ack IS
1020 -------------------------------------------------------------------------
1021 --
1022 -- Created By : sjadhav
1023 --
1024 -- Date Created On : 2001/01/03
1025 -- Purpose : This procedure reads the data from datafile(RFMS Ack File )
1026 -- and loads data into origination table after formatting
1027 -- Know limitations, enhancements or remarks
1028 -- Change History
1029 --
1030 -- Who When What
1031 -- rasahoo 13-May-2003 Bug #2938258 If ROrigination ecord is not part of this batch
1032 -- in the System then raise error.Cannot process Origination
1033 -- Record which status for acknowledgement processing is not "Sent".
1034 --
1035 -- (reverse chronological order - newest change first)
1036 --
1037 -------------------------------------------------------------------------
1038
1039 l_last_gldr_id NUMBER;
1040 l_number_rec NUMBER;
1041 l_count NUMBER DEFAULT 1;
1042 l_batch_id VARCHAR2(100);
1043 l_rfms_process_dt VARCHAR2(200);
1044 lp_count NUMBER DEFAULT 0;
1045 lf_count NUMBER DEFAULT 0;
1046 lv_message fnd_new_messages.message_name%TYPE;
1047 SYSTEM_STATUS VARCHAR2(20);
1048
1049 ----Bug #2938258
1050 CURSOR cur_gr_rfmb ( p_batch_id igf_gr_rfms_batch.batch_id%TYPE)
1051 IS
1052 SELECT
1053 rfmb_id
1054 FROM
1055 igf_gr_rfms_batch
1056 WHERE batch_id = p_batch_id;
1057
1058 cur_get_rfmb cur_gr_rfmb%ROWTYPE;
1059 l_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE;
1060 -----
1061
1062 BEGIN
1063
1064 igf_gr_gen.process_pell_ack ( g_ver_num,
1065 'GR_RFMS_ORIG',
1066 l_number_rec,
1067 l_last_gldr_id,
1068 l_batch_id);
1069
1070 ----Bug #2938258
1071
1072 OPEN cur_gr_rfmb(l_batch_id);
1073 FETCH cur_gr_rfmb INTO cur_get_rfmb;
1074 CLOSE cur_gr_rfmb;
1075
1076 l_rfmb_id := cur_get_rfmb.rfmb_id;
1077 --
1078 --
1079 -- Check the award year matches with the award year in PELL setup.
1080 --
1081 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, lv_message);
1082
1083 IF lv_message = 'IGF_GR_VRSN_MISMTCH' THEN
1084 fnd_message.set_name('IGF','IGF_GR_VRSN_MISMTCH');
1085 fnd_message.set_token('CYCL',SUBSTR(l_batch_id,3,4));
1086 fnd_message.set_token('BATCH',l_batch_id);
1087 fnd_message.set_token('VRSN',g_ver_num);
1088 fnd_message.set_token('AWD_YR',g_alt_code);
1089 fnd_file.put_line(fnd_file.log,fnd_message.get);
1090 RAISE invalid_version;
1091 END IF;
1092
1093 IF l_number_rec > 0 THEN
1094
1095 DECLARE
1096 l_actual_rec NUMBER DEFAULT 0;
1097 l_origination_id igf_gr_rfms.origination_id%TYPE;
1098 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
1099 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
1100
1101
1102 l_chk_flag BOOLEAN DEFAULT FALSE;
1103
1104
1105 CURSOR cur_get_pell(l_origination_id igf_gr_rfms.origination_id%TYPE) IS
1106 SELECT rfms.*
1107 FROM
1108 igf_gr_rfms rfms
1109 WHERE
1110 rfms.origination_id = l_origination_id
1111 FOR UPDATE OF origination_id NOWAIT;
1112
1113 get_pell_rec cur_get_pell%ROWTYPE;
1114
1115
1116 CURSOR c_rfms_data IS
1117 SELECT
1118 record_data
1119 FROM
1120 igf_gr_load_file_t
1121 WHERE
1122 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1) AND
1123 file_type = 'GR_RFMS_ORIG';
1124
1125 l_rfms igf_gr_rfms%ROWTYPE ;
1126 rec_data c_rfms_data%ROWTYPE;
1127
1128 ln_pell_awd NUMBER;
1129
1130 BEGIN
1131
1132 OPEN c_rfms_data;
1133 LOOP
1134
1135 FETCH c_rfms_data INTO rec_data;
1136 EXIT WHEN c_rfms_data%NOTFOUND;
1137
1138 BEGIN
1139 l_actual_rec := l_actual_rec + 1;
1140 OPEN cur_get_pell(LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23))));
1141 FETCH cur_get_pell INTO get_pell_rec;
1142
1143 IF cur_get_pell%NOTFOUND THEN
1144 CLOSE cur_get_pell;
1145 log_message(l_batch_id,LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23))));
1146 RAISE igf_gr_gen.skip_this_record;
1147 END IF;
1148 ----Bug #2938258
1149 IF l_rfmb_id<> get_pell_rec.rfmb_id THEN
1150
1151 fnd_message.set_name('IGF','IGF_GR_ORIG_BATCH_MISMATCH');
1152 fnd_message.set_token('BATCH_ID',l_batch_id);
1153 fnd_message.set_token('ORIG_ID',LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23))));
1154 fnd_file.put_line(fnd_file.log,fnd_message.get);
1155 fnd_file.new_line(fnd_file.log,1);
1156
1157 RAISE igf_gr_gen.skip_this_record;
1158 END IF;
1159 --Record should be in "Sent" status for acknowledgment processing"
1160 IF get_pell_rec.orig_action_code <> 'S' THEN
1161
1162 fnd_message.set_name('IGF','IGF_GR_ORIG_NOT_IN_SENT');
1163 fnd_message.set_token('ORIG_ID',LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23))));
1164 SYSTEM_STATUS := igf_aw_gen.lookup_desc('IGF_GR_ORIG_STATUS',get_pell_rec.orig_action_code);
1165 fnd_message.set_token('SYS_STATUS',SYSTEM_STATUS);
1166 fnd_file.put_line(fnd_file.log,fnd_message.get);
1167 fnd_file.new_line(fnd_file.log,1);
1168 RAISE igf_gr_gen.skip_this_record;
1169 END IF;
1170
1171 ----end Bug #2938258
1172
1173 -- Do not make any updates for amounts
1174 -- Updates for Amounts have to happen throgh Disbursement Routine
1175 --
1176 -- Also do not update all the Fields
1177 -- See which fields are updated by RFMS.
1178 -- Only those fields should be updated
1179 --
1180 l_rfms.origination_id := get_pell_rec.origination_id;
1181 l_rfms.ci_cal_type := get_pell_rec.ci_cal_type;
1182 l_rfms.ci_sequence_number := get_pell_rec.ci_sequence_number;
1183 l_rfms.base_id := get_pell_rec.base_id;
1184 l_rfms.award_id := get_pell_rec.award_id;
1185 l_rfms.rfmb_id := get_pell_rec.rfmb_id;
1186 l_rfms.inst_cross_ref_cd := get_pell_rec.inst_cross_ref_cd;
1187 l_rfms.rep_pell_id := get_pell_rec.rep_pell_id;
1188 BEGIN
1189 IF g_ver_num IN ('2002-2003','2003-2004','2004-2005') THEN
1190 l_rfms.sys_orig_ssn := LTRIM(RTRIM(SUBSTR(rec_data.record_data,24,9)));
1191 l_rfms.sys_orig_name_cd := LTRIM(RTRIM(SUBSTR(rec_data.record_data,33,2)));
1192 l_rfms.attending_campus_id := LTRIM(RTRIM(SUBSTR(rec_data.record_data,35,6)));
1193 l_rfms.orig_action_code := LTRIM(RTRIM(SUBSTR(rec_data.record_data,59,1)));
1194 ln_pell_awd := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,61,7)))),0)/100;
1195 l_rfms.est_disb_dt1 := fnd_date.string_to_date(LTRIM(RTRIM(SUBSTR(rec_data.record_data,68,8))),'YYYYMMDD');
1196 l_rfms.enrollment_dt := fnd_date.string_to_date(LTRIM(RTRIM(SUBSTR(rec_data.record_data,188,8))),'YYYYMMDD');
1197 l_rfms.low_tution_fee := LTRIM(RTRIM(SUBSTR(rec_data.record_data,196,1)));
1198 l_rfms.ver_status_code := LTRIM(RTRIM(SUBSTR(rec_data.record_data,197,1)));
1199 l_rfms.incrcd_fed_pell_rcp_cd := LTRIM(RTRIM(SUBSTR(rec_data.record_data,198,1)));
1200 l_rfms.transaction_num := LTRIM(RTRIM(SUBSTR(rec_data.record_data,199,2)));
1201 l_rfms.efc := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,201,5)))),0);
1202 l_rfms.secondary_efc_cd := LTRIM(RTRIM(SUBSTR(rec_data.record_data,206,1)));
1203 l_rfms.academic_calendar := LTRIM(RTRIM(SUBSTR(rec_data.record_data,207,1)));
1204 l_rfms.payment_method := LTRIM(RTRIM(SUBSTR(rec_data.record_data,208,1)));
1205 l_rfms.coa_amount := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,209,7)))),0)/100;
1206 l_rfms.enrollment_status := LTRIM(RTRIM(SUBSTR(rec_data.record_data,216,1)));
1207 l_rfms.wk_inst_time_calc_pymt := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,217,2)))),0);
1208 l_rfms.wk_int_time_prg_def_yr := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,219,2)))),0);
1209 l_rfms.cr_clk_hrs_acad_yr := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,221,4)))),0);
1210 l_rfms.cr_clk_hrs_prds_sch_yr := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,225,4)))),0);
1211 l_rfms.ft_pell_amount := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,251,5)))),0);
1212 l_rfms.prev_accpt_tran_no := LTRIM(RTRIM(SUBSTR(rec_data.record_data,256,2)));
1213 l_rfms.prev_accpt_efc := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,258,5)))),0);
1214 l_rfms.prev_accpt_sec_efc_cd := LTRIM(RTRIM(SUBSTR(rec_data.record_data,263,1)));
1215 l_rfms.prev_accpt_coa := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,264,7)))),0)/100;
1216 l_rfms.orig_reject_code := LTRIM(RTRIM(SUBSTR(rec_data.record_data,271,75)));
1217 l_rfms.orig_ed_use_flags := LTRIM(RTRIM(SUBSTR(rec_data.record_data,346,10)));
1218 l_rfms.pending_amount := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,356,7)))),0)/100;
1219 l_rfms.secondary_efc := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,363,5)))),0)/100;
1220 l_rfms.current_ssn := LTRIM(RTRIM(SUBSTR(rec_data.record_data,368,9)));
1221 l_rfms.birth_dt := fnd_date.string_to_date(LTRIM(RTRIM(SUBSTR(rec_data.record_data,377,8))),'YYYYMMDD');
1222 l_rfms.last_name := LTRIM(RTRIM(SUBSTR(rec_data.record_data,385,16)));
1223 l_rfms.first_name := LTRIM(RTRIM(SUBSTR(rec_data.record_data,401,12)));
1224 l_rfms.middle_name := LTRIM(RTRIM(SUBSTR(rec_data.record_data,413,1)));
1225 ELSE
1226 RAISE igf_gr_gen.no_file_version;
1227 END IF;
1228
1229 EXCEPTION
1230 -- The exception caught here will be the data format exceptions
1231 WHEN OTHERS THEN
1232 lf_count := lf_count + 1;
1233 fnd_message.set_name('IGF','IGF_GR_INVALID_RECORD');
1234 fnd_message.set_token('ORIG_ID',l_rfms.origination_id);
1235 fnd_file.put_line(fnd_file.log,fnd_message.get);
1236
1237 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
1238 fnd_file.put_line(fnd_file.log,fnd_message.get);
1239 -- Cannot Process Record for Origination ORIG_ID as it contains corrupt data
1240 fnd_file.put_line(fnd_file.log, ' ' );
1241
1242 RAISE igf_gr_gen.skip_this_record;
1243
1244 END;
1245 l_rfms.rec_source := get_pell_rec.rec_source;
1246 l_rfms.pell_amount := get_pell_rec.pell_amount; -- compare this amount
1247 l_rfms.pell_profile := get_pell_rec.pell_profile;
1248 l_rfms.total_pymt_prds := get_pell_rec.total_pymt_prds;
1249 l_rfms.orig_status_dt := TRUNC(SYSDATE);
1250
1251 IF ln_pell_awd <> get_pell_rec.pell_amount THEN
1252 igf_gr_gen.insert_sys_holds(get_pell_rec.award_id,NULL,'PELL');
1253 fnd_message.set_name('IGF','IGF_GR_DIFF_PELL_AMT');
1254 fnd_message.set_token('ORIG_ID',l_rfms.origination_id);
1255 -- The Reported and Received Pell Award Amount for this Origination Record are different
1256 fnd_file.put_line(fnd_file.log,fnd_message.get);
1257
1258 fnd_message.set_name('IGF','IGF_GR_REPORTED_AMT');
1259 fnd_message.set_token('AMT', TO_CHAR(get_pell_rec.pell_amount));
1260 fnd_file.put_line(fnd_file.log,fnd_message.get);
1261
1262 fnd_message.set_name('IGF','IGF_GR_RECEIVED_AMT');
1263 fnd_message.set_token('AMT', TO_CHAR(ln_pell_awd));
1264 fnd_file.put_line(fnd_file.log,fnd_message.get);
1265 fnd_file.new_line(fnd_file.log,1);
1266
1267 --
1268 -- print both amounts fnd_message.set_name('IGF','IGF_
1269 --
1270
1271 END IF;
1272 lp_count := lp_count + 1;
1273 igf_gr_rfms_pkg.update_row (
1274 x_rowid => get_pell_rec.row_id,
1275 x_origination_id => l_rfms.origination_id,
1276 x_ci_cal_type => l_rfms.ci_cal_type,
1277 x_ci_sequence_number => l_rfms.ci_sequence_number,
1278 x_base_id => l_rfms.base_id,
1279 x_award_id => l_rfms.award_id,
1280 x_rfmb_id => l_rfms.rfmb_id,
1281 x_sys_orig_ssn => l_rfms.sys_orig_ssn,
1282 x_sys_orig_name_cd => l_rfms.sys_orig_name_cd,
1283 x_transaction_num => l_rfms.transaction_num,
1284 x_efc => l_rfms.efc,
1285 x_ver_status_code => l_rfms.ver_status_code,
1286 x_secondary_efc => l_rfms.secondary_efc,
1287 x_secondary_efc_cd => l_rfms.secondary_efc_cd,
1288 x_pell_amount => l_rfms.pell_amount,
1289 x_pell_profile => l_rfms.pell_profile,
1290 x_enrollment_status => l_rfms.enrollment_status,
1291 x_enrollment_dt => l_rfms.enrollment_dt,
1292 x_coa_amount => l_rfms.coa_amount,
1293 x_academic_calendar => l_rfms.academic_calendar,
1294 x_payment_method => l_rfms.payment_method,
1295 x_total_pymt_prds => l_rfms.total_pymt_prds,
1296 x_incrcd_fed_pell_rcp_cd => l_rfms.incrcd_fed_pell_rcp_cd,
1297 x_attending_campus_id => l_rfms.attending_campus_id,
1298 x_est_disb_dt1 => l_rfms.est_disb_dt1,
1299 x_orig_action_code => l_rfms.orig_action_code,
1300 x_orig_status_dt => l_rfms.orig_status_dt,
1301 x_orig_ed_use_flags => l_rfms.orig_ed_use_flags,
1302 x_ft_pell_amount => l_rfms.ft_pell_amount,
1303 x_prev_accpt_efc => l_rfms.prev_accpt_efc,
1304 x_prev_accpt_tran_no => l_rfms.prev_accpt_tran_no,
1305 x_prev_accpt_sec_efc_cd => l_rfms.prev_accpt_sec_efc_cd,
1306 x_prev_accpt_coa => l_rfms.prev_accpt_coa,
1307 x_orig_reject_code => l_rfms.orig_reject_code,
1308 x_wk_inst_time_calc_pymt => l_rfms.wk_inst_time_calc_pymt,
1309 x_wk_int_time_prg_def_yr => l_rfms.wk_int_time_prg_def_yr,
1310 x_cr_clk_hrs_prds_sch_yr => l_rfms.cr_clk_hrs_prds_sch_yr,
1311 x_cr_clk_hrs_acad_yr => l_rfms.cr_clk_hrs_acad_yr,
1312 x_inst_cross_ref_cd => l_rfms.inst_cross_ref_cd,
1313 x_low_tution_fee => l_rfms.low_tution_fee,
1314 x_rec_source => l_rfms.rec_source,
1315 x_pending_amount => l_rfms.pending_amount,
1316 x_mode => 'R',
1317 x_birth_dt => l_rfms.birth_dt,
1318 x_last_name => l_rfms.last_name,
1319 x_first_name => l_rfms.first_name,
1320 x_middle_name => l_rfms.middle_name,
1321 x_current_ssn => l_rfms.current_ssn,
1322 x_legacy_record_flag => NULL,
1323 x_reporting_pell_cd => l_rfms.rep_pell_id,
1324 x_rep_entity_id_txt => get_pell_rec.rep_entity_id_txt,
1325 x_atd_entity_id_txt => get_pell_rec.atd_entity_id_txt,
1326 x_note_message => get_pell_rec.note_message,
1327 x_full_resp_code => get_pell_rec.full_resp_code,
1328 x_document_id_txt => get_pell_rec.document_id_txt );
1329
1330 --write to log file
1331 log_rej_message(l_rfms.origination_id,
1332 l_rfms.orig_reject_code);
1333
1334 --write to output file
1335 out_message(l_rfms.origination_id);
1336
1337 IF cur_get_pell%ISOPEN THEN
1338 CLOSE cur_get_pell;
1339 END IF;
1340
1341 EXCEPTION
1342 WHEN igf_gr_gen.skip_this_record THEN
1343 IF cur_get_pell%ISOPEN THEN
1344 CLOSE cur_get_pell;
1345 END IF;
1346 END;
1347 END LOOP;
1348
1349 CLOSE c_rfms_data;
1350
1351 IF l_actual_rec <> l_number_rec THEN
1352 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
1353 igs_ge_msg_stack.add;
1354 RAISE igf_gr_gen.file_not_loaded;
1355 END IF;
1356 END; -- Inner Begin
1357
1358 fnd_file.new_line(fnd_file.log,2);
1359 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
1360 fnd_message.set_token('CNT',l_number_rec);
1361 fnd_file.put_line(fnd_file.log,fnd_message.get);
1362
1363 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
1364 fnd_message.set_token('CNT',lp_count);
1365 fnd_file.put_line(fnd_file.log,fnd_message.get);
1366
1367 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
1368 fnd_message.set_token('CNT',lf_count);
1369 fnd_file.put_line(fnd_file.log,fnd_message.get);
1370
1371 END IF; -- if l_num_rec
1372
1373 EXCEPTION
1374
1375 WHEN invalid_version THEN
1376 RAISE;
1377 WHEN igf_gr_gen.no_file_version THEN
1378 RAISE;
1379 WHEN igf_gr_gen.corrupt_data_file THEN
1380 RAISE;
1381 WHEN no_data_in_table THEN
1382 RAISE;
1383 WHEN igf_gr_gen.batch_not_in_system THEN
1384 -- Bug # 4008991
1385 fnd_message.set_name('IGF','IGF_SL_GR_BATCH_DOES_NO_EXIST');
1386 fnd_message.set_token('BATCH_ID',l_batch_id);
1387 RAISE;
1388 WHEN igf_gr_gen.file_not_loaded THEN
1389 RAISE;
1390 WHEN OTHERS THEN
1391 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1392 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.RFMS_ACK');
1393 igs_ge_msg_stack.add;
1394 app_exception.raise_exception;
1395 END rfms_ack;
1396
1397 PROCEDURE main(errbuf OUT NOCOPY VARCHAR2,
1398 retcode OUT NOCOPY NUMBER,
1399 award_year IN VARCHAR2,
1400 p_reporting_pell IN VARCHAR2,
1401 p_attending_pell IN VARCHAR2,
1402 base_id IN igf_gr_rfms_all.base_id%TYPE,
1403 p_persid_grp IN VARCHAR2,
1404 p_orig_run_mode IN VARCHAR2 )
1405 AS
1406
1407 --------------------------------------------------------------------------
1408 --
1409 -- Created By : sjadhav
1410 -- Date Created On : 2001/01/03
1411 --
1412 -- Purpose :This is the main procedure which will be called by
1413 -- concurrent manager.It will call either rfms orination or
1414 -- rfms ack depending on the l_mode parameter
1415 --
1416 -- Know limitations, enhancements or remarks
1417 -- Change History
1418 -- Who When What
1419 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1420 -- ugummall 08-Jan-2003 Bug 3318202. Changed the order of parameters and removed
1421 -- the parameter p_org_id.
1422 -- ugummall 03-DEC-2003 Bug 3252832. FA 131 - COD Updates
1423 -- 1. Added two parameters to this procedure namely
1424 -- p_persid_grp and p_orig_run_mode.
1425 -- 2. base_id and p_persid_grp are mutually exclusive. Added this check.
1426 -- 3. Flat file version number is checked here itself to improve performance.
1427 --
1428 -- ugummall 05-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
1429 -- 1. Added two parameters to this procedure namely
1430 -- p_reporting_pell and p_attending_pell
1431 -- 2. base_id and p_attending_pell are mutually exclusive. Added this check.
1432 --
1433 -- (reverse chronological order - newest change first)
1434 --
1435 -- sjadhav, Feb 06th 2002
1436 -- Disabled Run For Parameter
1437 -- If Award Year and Base ID Both are present,
1438 -- then run the process for Student, else run for Award Year
1439 --
1440 --------------------------------------------------------------------------
1441
1442 -- To get group description for group_id
1443 CURSOR cur_person_group(cp_persid_grp igs_pe_persid_group_all.group_id%TYPE) IS
1444 SELECT group_cd group_name
1445 FROM igs_pe_persid_group_all
1446 WHERE group_id = cp_persid_grp;
1447 rec_person_group cur_person_group%ROWTYPE;
1448
1449 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
1450 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
1451 ln_base_id NUMBER;
1452 l_msg_str_1 VARCHAR2(1000);
1453 l_msg_str_2 VARCHAR2(1000);
1454 l_msg_str_3 VARCHAR2(1000);
1455 l_msg_str_4 VARCHAR2(1000);
1456 l_msg_str_5 VARCHAR2(1000);
1457 l_msg_str_6 VARCHAR2(1000);
1458 mutually_exclusive BOOLEAN DEFAULT TRUE;
1459 l_cod_year_flag BOOLEAN;
1460
1461 BEGIN
1462 igf_aw_gen.set_org_id(NULL);
1463 retcode := 0;
1464 ln_base_id := base_id;
1465 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(award_year,1,10)));
1466 l_ci_sequence_number := NVL(TO_NUMBER(SUBSTR(award_year,11)),0);
1467
1468 -- Check wether the awarding year is COD-XML processing Year or not
1469 l_cod_year_flag := NULL;
1470 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (l_ci_cal_type,l_ci_sequence_number,'PELL');
1471
1472 -- This process is allowed to run only for PHASE_IN_PARTICIPANT
1473 -- If the award year is FULL_PARTICIPANT then raise the error message
1474 -- and stop processing else continue the process
1475 IF l_cod_year_flag THEN
1476
1477 fnd_message.set_name('IGF','IGF_GR_COD_NO_ORIG');
1478 fnd_file.put_line(fnd_file.log,fnd_message.get);
1479 RETURN;
1480
1481 END IF;
1482
1483 -- show parameter 1 - award year
1484 l_msg_str_1 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','AWARD_YEAR'),30) ||
1485 RPAD(igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number),20);
1486 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1487
1488 -- show parameter 2 - report pell id
1489 l_msg_str_3 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'REPORT_PELL'),30) || p_reporting_pell;
1490 fnd_file.put_line(fnd_file.log,l_msg_str_3);
1491
1492 -- show parameter 3 - attend pell id
1493 IF (p_attending_pell IS NOT NULL) THEN
1494 l_msg_str_4 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'ATTEND_PELL'),30) || p_attending_pell;
1495 fnd_file.put_line(fnd_file.log,l_msg_str_4);
1496 END IF;
1497
1498 -- show parameter 4 - base id
1499 IF (base_id IS NOT NULL) THEN
1500 l_msg_str_2 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BASE_ID'),30) ||
1501 RPAD(igf_gr_gen.get_per_num(ln_base_id),20);
1502 fnd_file.put_line(fnd_file.log,l_msg_str_2);
1503 END IF;
1504
1505 -- show parameter 5 - Person Id Group
1506 IF (p_persid_grp IS NOT NULL) THEN
1507 OPEN cur_person_group(p_persid_grp);
1508 FETCH cur_person_group INTO rec_person_group;
1509 CLOSE cur_person_group;
1510 l_msg_str_5 := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS', 'PERSON_ID_GROUP'),30) || rec_person_group.group_name;
1511 fnd_file.put_line(fnd_file.log,l_msg_str_5);
1512 END IF;
1513
1514 -- show parameter 6 - Orgination Run Mode
1515 l_msg_str_6 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'PELL_ORIG_RUN_MODE'),30) || p_orig_run_mode;
1516 fnd_file.put_line(fnd_file.log,l_msg_str_6);
1517
1518 -- FA 126. base_id and attending pell are mutually exclusive.
1519 IF (base_id IS NOT NULL AND p_attending_pell IS NOT NULL) THEN
1520 FND_MESSAGE.SET_NAME('IGF', 'IGF_GR_PORIG_INCOMPAT');
1521 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1522 mutually_exclusive := FALSE;
1523 END IF;
1524
1525 -- FA 131. Person Number(base_id) and Person Group are mutually exclusive.
1526 IF (base_id IS NOT NULL AND p_persid_grp IS NOT NULL) THEN
1527 FND_MESSAGE.SET_NAME('IGF', 'IGF_GR_PERSID_GRP_INCOMPAT');
1528 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1529 mutually_exclusive := FALSE;
1530 END IF;
1531
1532 IF NOT mutually_exclusive THEN
1533 RETURN;
1534 END IF;
1535
1536 IF l_ci_cal_type IS NULL OR
1537 l_ci_sequence_number IS NULL THEN
1538 RAISE param_error;
1539 END IF;
1540
1541 -- Get the Flat File Version and then Proceed
1542 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
1543
1544 -- Get the Cycle Year
1545 l_cy_yr := igf_gr_gen.get_cycle_year(l_ci_cal_type,l_ci_sequence_number);
1546
1547 IF (g_ver_num ='NULL') OR (g_ver_num NOT IN ('2002-2003','2003-2004','2004-2005')) THEN
1548 RAISE igf_gr_gen.no_file_version;
1549 ELSE
1550 rfms_orig(l_ci_cal_type,l_ci_sequence_number,
1551 ln_base_id, p_reporting_pell, p_attending_pell,
1552 p_persid_grp, p_orig_run_mode
1553 );
1554 END IF;
1555
1556 COMMIT;
1557
1558 EXCEPTION
1559 WHEN igf_gr_gen.no_file_version THEN
1560 ROLLBACK;
1561 retcode := 2;
1562 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
1563 fnd_file.put_line(fnd_file.log,errbuf);
1564 WHEN batch_not_created THEN
1565 ROLLBACK;
1566 WHEN param_error THEN
1567 ROLLBACK;
1568 retcode := 2;
1569 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
1570 fnd_file.put_line(fnd_file.log,errbuf);
1571 WHEN no_data_in_table THEN
1572 ROLLBACK;
1573 errbuf := fnd_message.get_string('IGF','IGF_AP_NO_DATA_FOUND');
1574 fnd_file.put_line(fnd_file.log,errbuf);
1575 WHEN app_exception.record_lock_exception THEN
1576 ROLLBACK;
1577 retcode:=2;
1578 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1579 fnd_file.put_line(fnd_file.log,errbuf);
1580 WHEN persid_grp_sql_stmt_error THEN
1581 ROLLBACK;
1582 retcode := 2;
1583 errbuf := fnd_message.get_string('IGF','IGF_AP_INVALID_QUERY');
1584 fnd_file.put_line(fnd_file.log,errbuf);
1585 WHEN OTHERS THEN
1586 ROLLBACK;
1587 retcode := 2;
1588 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || SQLERRM;
1589 igs_ge_msg_stack.conc_exception_hndl;
1590 END main;
1591
1592 PROCEDURE main_ack( errbuf OUT NOCOPY VARCHAR2,
1593 retcode OUT NOCOPY NUMBER,
1594 p_awd_yr IN VARCHAR2,
1595 p_org_id IN NUMBER)
1596 AS
1597 ----------------------------------------------------------------------
1598 --
1599 -- Created By : sjadhav
1600 --
1601 -- Date Created On : 19-apr-2001
1602 --
1603 -- Purpose :This is the main procedure which will be called by
1604 -- concurrent manager.It calls rfms ack
1605 --
1606 --
1607 -- sjadhav
1608 -- add Award Year parameter to all Acknowledgement Processing
1609 --
1610 -- Know limitations, enhancements or remarks
1611 -- Change History
1612 -- Who When What
1613 --
1614 -- (reverse chronological order - newest change first)
1615 --tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1616 ----------------------------------------------------------------------
1617
1618 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
1619 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
1620
1621 BEGIN
1622 igf_aw_gen.set_org_id(NULL);
1623 retcode := 0;
1624 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
1625 l_ci_sequence_number := NVL(TO_NUMBER(SUBSTR(p_awd_yr,11)),0);
1626
1627 IF l_ci_cal_type IS NULL OR
1628 l_ci_sequence_number IS NULL THEN
1629 RAISE param_error;
1630 END IF;
1631
1632 -- Check wether the awarding year is COD-XML processing Year or not
1633 -- This process is allowed to run only for PHASE_IN_PARTICIPANT
1634 -- If the award year is FULL_PARTICIPANT then raise the error message
1635 -- and stop processing else continue the process
1636 IF igf_sl_dl_validation.check_full_participant (l_ci_cal_type,l_ci_sequence_number,'PELL') THEN
1637
1638 fnd_message.set_name('IGF','IGF_GR_COD_NO_ORIG_ACK');
1639 fnd_file.put_line(fnd_file.log,fnd_message.get);
1640 RETURN;
1641
1642 END IF;
1643
1644 --
1645 -- Get the Flat File Version and then Proceed
1646 --
1647 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
1648 g_alt_code := igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
1649 --
1650 -- Get the Cycle Year
1651 --
1652 l_cy_yr := igf_gr_gen.get_cycle_year(l_ci_cal_type,l_ci_sequence_number);
1653
1654 IF g_ver_num ='NULL' THEN
1655 RAISE igf_gr_gen.no_file_version;
1656 ELSE
1657 rfms_ack;
1658 END IF;
1659
1660 COMMIT;
1661
1662 EXCEPTION
1663
1664 WHEN invalid_version THEN
1665 ROLLBACK;
1666 retcode := 2;
1667
1668 WHEN igf_gr_gen.no_file_version THEN
1669 ROLLBACK;
1670 retcode := 2;
1671 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
1672 fnd_file.put_line(fnd_file.log,errbuf);
1673
1674 WHEN app_exception.record_lock_exception THEN
1675 ROLLBACK;
1676 retcode := 2;
1677 errbuf := fnd_message.get_string('IGF','IGF_GE_LOCK_ERROR');
1678 fnd_file.put_line(fnd_file.log,errbuf);
1679
1680 WHEN param_error THEN
1681 ROLLBACK;
1682 retcode := 2;
1683 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
1684 fnd_file.put_line(fnd_file.log,errbuf);
1685
1686 WHEN igf_gr_gen.file_not_loaded THEN
1687 ROLLBACK;
1688 retcode := 2;
1689 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
1690 fnd_file.put_line(fnd_file.log,errbuf);
1691
1692 WHEN no_data_in_table THEN
1693 ROLLBACK;
1694 retcode := 2;
1695 errbuf := fnd_message.get_string('IGF','IGF_AP_NO_DATA_FOUND');
1696 fnd_file.put_line(fnd_file.log,errbuf);
1697
1698 WHEN igf_gr_gen.batch_not_in_system THEN
1699 ROLLBACK;
1700 retcode := 2;
1701 errbuf := fnd_message.get; -- Bug # 4008991
1702 fnd_file.put_line(fnd_file.log,errbuf);
1703
1704 WHEN igf_gr_gen.corrupt_data_file THEN
1705 ROLLBACK;
1706 retcode := 2;
1707 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
1708 fnd_file.put_line(fnd_file.log,errbuf);
1709
1710 WHEN OTHERS THEN
1711 ROLLBACK;
1712 retcode := 2;
1713 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || SQLERRM;
1714 igs_ge_msg_stack.conc_exception_hndl;
1715
1716 END main_ack;
1717
1718 END igf_gr_rfms_orig;
[Home] [Help]