1 PACKAGE BODY igf_gr_rfms_disb_orig AS
2 /* $Header: IGFGR03B.pls 120.4 2006/02/08 23:46:42 ridas ship $ */
3
4 ------------------------------------------------------------------------
5 -- bvisvana 07-July-2005 Bug # 4008991 - IGF_GR_BATCH_DOES_NOT_EXIST replaced by IGF_SL_GR_BATCH_DOES_NO_EXIST
6 ------------------------------------------------------------------------
7 -- ayedubat 20-OCT-2004 FA 149 COD-XML Standards build bug # 3416863
8 -- Changed the logic as per the TD, FA149_TD_COD_XML_i1a.doc
9 ------------------------------------------------------------------------
10 -- veramach 29-Jan-2004 Bug 3408092 Added 2004-2005 in g_ver_num checks
11 ------------------------------------------------------------------------
12 -- ugummall 08-JAN-2004 Bug 3318202. Changed the order of parameters and removed p_org_id in main procedure.
13 ------------------------------------------------------------------------
14 -- ugummall 06-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
15 -- 1. Added two extra parameters to main and rfms_disb_orig procedures.
16 -- 2. Modified cursor cur_rfms_disb to include reporting and attending pell ids.
17 -- 3. Removed l_rep_pell_id and its references. Used p_reporting_pell, newly
18 -- passed parameter, in igf_gr_gen.{get_pell_trailer, get_pell_header} procedures.
19 -- 4. New cursor cur_attending_pell to check attending pell is a child of reporting
20 -- pell or not.
21 -- 5. In rfms_disb_orig prcodure processed only those records for which attending
22 -- pell is a child of reporting pell id.
23 ------------------------------------------------------------------------------
24 -- ugummall 03-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
25 -- Added two extra parameters in call to igf_gr_gen.get_pell_header
26 ------------------------------------------------------------------------------
27 -- cdcruz 16-Aug-2003 Bug BUG FA121-3085558
28 -- Check added to check for Transaction Number
29 -- Match against Payment ISIR.
30 ------------------------------------------------------------------------
31 -- rasahoo 13-May-2003 Bug #2938258 If Disbursement Record is not part of this batch
32 -- in the System then raise error.Cannot process Disbursement
33 -- Record which status for acknowledgement processing is not "Sent".
34 -------------------------------------------------------------------------------
35 -- sjadhav 06-Feb-2003 FA116 Build Bug - 2758812
36 -- added invalid_version expcetion
37 -- modified for 03-04 compliance
38 -- output file in UPPERCASE
39 ------------------------------------------------------------------------------
40 -- sjadhav Bug 2383690 added igf_gr_gen.send_orig_disb call
41 ------------------------------------------------------------------------------
42 -- sjadhav FEB13th,2002 BUG 2216956
43 -- Removed flag and disbursement number parameters
44 -- Added Award Year parameter to main_ack
45 ------------------------------------------------------------------------------
46 -- sjadhav 18-jun-2001 Bug ID : 1823995
47 -- 1. Query for the main cursor 'cur_rfms_disb'
48 -- modified to have NVL clause
49 -- 2. base_id is made null if the process is
50 -- run for an award year
51 ------------------------------------------------------------------------------
52 -- sjadhav 25-apr-2001 Bug No : 1750071
53 -- Do not create Origination Record if any of
54 -- the following field is null
55 -- disb_ref_num,db_cr_flag,disb_amt
56 ------------------------------------------------------------------------------
57 -- sjadhav 19-apr-2001 Bug ID : 1731177 Added main_ack to process
58 -- rfms disb ack . this will get
59 -- called from conc. mgr.
60 -- Removed l_mode from main
61 ------------------------------------------------------------------------------
62
63
64 no_data_in_table EXCEPTION;
65 param_error EXCEPTION;
66 batch_not_created EXCEPTION;
67 invalid_version EXCEPTION;
68 next_record EXCEPTION;
69
70 l_cy_yr VARCHAR2(10) DEFAULT NULL; -- to hold cycle year
71 l_msg_prn_1 BOOLEAN DEFAULT TRUE;
72 g_ver_num VARCHAR2(30) DEFAULT NULL; -- Flat File Version Number
73 g_alt_code VARCHAR2(80);
74 g_header VARCHAR2(1000);
75 g_header_written VARCHAR2(1) DEFAULT 'N';
76 g_trans_type VARCHAR2(1);
77 g_persid_grp igs_pe_persid_group_all.group_id%TYPE;
78
79 -- Main Cursor to pick up RFMS Records
80 -- for preparing Origination File
81
82 -- FA 126. This cursor cur_rfms_disb is modified to include reporting and attending pell ids.
83 CURSOR cur_rfms_disb(
84 l_base_id igf_gr_rfms.base_id%TYPE,
85 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
86 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE,
87 cp_reporting_pell igf_gr_rfms.rep_pell_id%TYPE,
88 cp_attending_pell igf_gr_rfms.attending_campus_id%TYPE
89 ) IS
90 SELECT rfmd.*,
91 rfms.inst_cross_ref_cd,
92 rfms.base_id,
93 rfms.award_id,
94 rfms.transaction_num,
95 rfms.ci_cal_type,
96 rfms.ci_sequence_number,
97 rfms.rep_pell_id,
98 rfms.attending_campus_id,
99 rfms.pell_amount
100 FROM igf_gr_rfms rfms ,
101 igf_gr_rfms_disb rfmd,
102 igf_gr_pell_setup setup
103 WHERE rfms.base_id = NVL(l_base_id,rfms.base_id)
104 AND rfms.ci_cal_type = l_ci_cal_type
105 AND rfms.ci_sequence_number = l_ci_sequence_number
106 AND rfms.rep_pell_id = cp_reporting_pell
107 AND rfms.attending_campus_id = NVL(cp_attending_pell, rfms.attending_campus_id)
108 AND rfmd.disb_ack_act_status = 'R'
109 AND rfmd.origination_id = rfms.origination_id
110 AND rfmd.disb_ref_num IN (SELECT disb.disb_num
111 FROM igf_aw_awd_disb_all disb
112 WHERE disb.trans_type IN ('A')
113 AND disb.award_id = rfms.award_id
114 AND g_trans_type IN ('A','P')
115 UNION ALL
116 SELECT disb.disb_num
117 FROM igf_aw_awd_disb_all disb
118 WHERE disb.trans_type IN ('P')
119 AND disb.award_id = rfms.award_id
120 AND disb.disb_date <= DECODE(setup.funding_method,'J',TRUNC(SYSDATE) + 7,'A',TRUNC(SYSDATE) + 30)
121 AND g_trans_type = 'P'
122 )
123 AND setup.rep_pell_id = rfms.rep_pell_id
124 AND setup.ci_cal_type = rfms.ci_cal_type
125 AND setup.ci_sequence_number = rfms.ci_sequence_number
126 AND rfms.orig_action_code IN ('A','D','C')
127 ORDER BY rfms.origination_id
128 FOR UPDATE OF disb_ack_act_status NOWAIT;
129 rec_rfms_disb cur_rfms_disb%ROWTYPE;
130
131 PROCEDURE upd_rfms_disb(p_rec_disb_orig cur_rfms_disb%ROWTYPE,
132 p_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE) IS
133
134 ------------------------------------------------------------------------------
135 --
136 -- Created By : sjadhav
137 --
138 -- Date Created On : 2001/01/03
139 -- Purpose :
140 -- This procedure updates the records which are sent to
141 -- external processor.
142 -- orig_ack_act_status is updated to 'S' for the record which is
143 -- sent to external processor.
144 -- Know limitations, enhancements or remarks
145 -- Change History
146 -- Who When What
147 --
148 -- (reverse chronological order - newest change first)
149 --
150 ------------------------------------------------------------------------------
151
152 BEGIN
153
154 igf_gr_rfms_disb_pkg.update_row (
155 x_rowid => p_rec_disb_orig.row_id,
156 x_rfmd_id => p_rec_disb_orig.rfmd_id ,
157 x_origination_id => p_rec_disb_orig.origination_id,
158 x_disb_ref_num => p_rec_disb_orig.disb_ref_num,
159 x_disb_dt => p_rec_disb_orig.disb_dt,
160 x_disb_amt => p_rec_disb_orig.disb_amt,
161 x_db_cr_flag => p_rec_disb_orig.db_cr_flag,
162 x_disb_ack_act_status => 'S', -- record processed
163 x_disb_status_dt => p_rec_disb_orig.disb_status_dt,
164 x_accpt_disb_dt => p_rec_disb_orig.accpt_disb_dt ,
165 x_disb_accpt_amt => p_rec_disb_orig.disb_accpt_amt,
166 x_accpt_db_cr_flag => p_rec_disb_orig.accpt_db_cr_flag,
167 x_disb_ytd_amt => p_rec_disb_orig.disb_ytd_amt,
168 x_pymt_prd_start_dt => p_rec_disb_orig.pymt_prd_start_dt,
169 x_accpt_pymt_prd_start_dt => p_rec_disb_orig.accpt_pymt_prd_start_dt,
170 x_edit_code => p_rec_disb_orig.edit_code ,
171 x_rfmb_id => p_rfmb_id,
172 x_mode => 'R',
173 x_ed_use_flags => p_rec_disb_orig.ed_use_flags);
174
175
176 EXCEPTION
177 WHEN OTHERS THEN
178 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
179 fnd_message.set_token('NAME','IGF_GR_RFMS_DISB_ORIG.UPD_RFMS_DISB');
180 igs_ge_msg_stack.add;
181 app_exception.raise_exception;
182 END upd_rfms_disb;
183
184
185 PROCEDURE prepare_data
186 ( p_rfms_rec IN cur_rfms_disb%ROWTYPE,
187 p_num_of_records IN OUT NOCOPY NUMBER,
188 p_rfmb_id IN igf_gr_rfms_batch.rfmb_id%TYPE)
189 IS
190 ------------------------------------------------------------------------------
191 --
192 -- Created By : sjadhav
193 --
194 -- Date Created On : 2001/01/03
195 -- Purpose :This procedure loads the record data into datafile
196 -- Know limitations, enhancements or remarks
197 -- Change History
198 -- Who When What
199 -- ugummall 06-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
200 -- 1. Since Header needs to be written only when at least one data record to
201 -- be written is there, it is written just before writing first data record
202 -- into output file.
203 --
204 -- (reverse chronological order - newest change first)
205 --
206 ------------------------------------------------------------------------------
207
208 l_data VARCHAR2(1000);
209 -- This cursor gets l_inst_crref_id from igf_gr_pell_setup
210 -- for a particualr origination
211
212
213 BEGIN
214
215 l_data := NULL;
216 --
217 --
218 -- Bug No : 1750071
219 -- Do not create Origination Record if any of the following field is null
220 -- disb_ref_num,db_cr_flag,disb_amt
221 -- Write into the log file which records were not originated
222 --
223 --
224 IF p_rfms_rec.disb_ref_num IS NULL OR
225 p_rfms_rec.db_cr_flag IS NULL OR
226 p_rfms_rec.disb_amt IS NULL THEN
227 fnd_file.new_line(fnd_file.log,1);
228 fnd_message.set_name('IGF','IGF_GR_ORIG_DATA_REQD');
229 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(p_rfms_rec.base_id));
230 fnd_file.put_line(fnd_file.log,fnd_message.get);
231 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_REF_NUM'),50) || ' : ' ||p_rfms_rec.disb_ref_num);
232 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DB_CR_FLAG'),50) || ' : ' ||p_rfms_rec.db_cr_flag);
233 fnd_file.put_line(fnd_file.log, RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_AMT'),50) || ' : ' ||p_rfms_rec.disb_amt);
234 fnd_file.new_line(fnd_file.log,1);
235 ELSE
236 IF g_ver_num IN ('2002-2003','2003-2004','2004-2005') THEN
237 l_data := RPAD(p_rfms_rec.origination_id,23) ||
238 RPAD(NVL(p_rfms_rec.inst_cross_ref_cd,' '),13) ||
239 RPAD(' ',1) || -- Action Code
240 LPAD(p_rfms_rec.disb_ref_num,2,'0') ||
241 RPAD(p_rfms_rec.db_cr_flag,1) ||
242 LPAD(TO_CHAR(ABS(100*NVL(p_rfms_rec.disb_amt,0))),7,'0') ||
243 RPAD(NVL(TO_CHAR(p_rfms_rec.disb_dt,'YYYYMMDD'),' '),8) ||
244 RPAD(NVL(TO_CHAR(p_rfms_rec.pymt_prd_start_dt,'YYYYMMDD'),' '),8) ||
245 RPAD(' ',37); -- Unused
246 --
247 -- The length of this record is 100
248 -- update rfms_disb table for the records sent.
249 --
250 -- Bug 2383690
251 -- If this is not first disbursement and
252 -- If fed verf stat is W, do not send
253 -- log message
254 --
255
256 IF NVL(p_rfms_rec.disb_ref_num,0) <>
257 NVL(igf_gr_gen.get_min_pell_disb ( p_rfms_rec.origination_id ),0) THEN
258 IF igf_gr_gen.send_orig_disb( p_rfms_rec.origination_id) THEN
259
260 IF (g_header_written = 'N') THEN
261 fnd_file.put_line(fnd_file.output, UPPER(g_header));
262 g_header_written := 'Y';
263 END IF;
264
265 fnd_file.put_line(fnd_file.output,UPPER(l_data));
266 upd_rfms_disb(p_rfms_rec,p_rfmb_id);
267 p_num_of_records := p_num_of_records + 1;
268
269 ELSE
270
271 fnd_message.set_name('IGF','IGF_GR_VERF_STAT_W');
272 fnd_message.set_token('ORIG_ID',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID') ||
273 ' : ' || p_rfms_rec.origination_id ||
274 ' , ' || igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_REF_NUM') ||
275 ' : ' || p_rfms_rec.disb_ref_num);
276
277 fnd_file.put_line(fnd_file.log,fnd_message.get);
278 END IF;
279 ELSE
280
281 IF (g_header_written = 'N') THEN
282 fnd_file.put_line(fnd_file.output, UPPER(g_header));
283 g_header_written := 'Y';
284 END IF;
285
286 fnd_file.put_line(fnd_file.output,UPPER(l_data));
287 upd_rfms_disb(p_rfms_rec,p_rfmb_id);
288 p_num_of_records := p_num_of_records + 1;
289
290 END IF;
291
292
293 ELSE
294 RAISE igf_gr_gen.no_file_version;
295 END IF;
296 END IF;
297
298
299 EXCEPTION
300
301 WHEN igf_gr_gen.no_file_version THEN
302 RAISE;
303
304 WHEN OTHERS THEN
305 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
306 fnd_message.set_token('NAME','IGF_GR_RFMS_DISB_ORIG.PREPARE_DATA');
307 igs_ge_msg_stack.add;
308 app_exception.raise_exception;
309
310 END prepare_data;
311
312 PROCEDURE log_message
313 (p_batch_id VARCHAR2,
314 p_origination_id igf_gr_rfms.origination_id%TYPE ) IS
315
316 ------------------------------------------------------------------------------
317 --
318 -- Created By : sjadhav
319 --
320 -- Date Created On : 2001/01/03
321 -- Purpose :This overloaded procedure formats the messages
322 -- to be put into log file.
323 -- Know limitations, enhancements or remarks
324 -- Change History
325 -- Who When What
326 --
327 -- (reverse chronological order - newest change first)
328 --
329 ------------------------------------------------------------------------------
330 l_msg_str_0 VARCHAR2(1000);
331 l_msg_str_1 VARCHAR2(1000);
332 BEGIN
333
334 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BATCH_ID'),50) ||
335 RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50);
336 l_msg_str_1 := RPAD(p_batch_id,50) ||
337 RPAD(p_origination_id,50);
338
339 fnd_file.put_line(fnd_file.log,'');
340 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
341 fnd_file.put_line(fnd_file.log,fnd_message.get);
342 fnd_file.put_line(fnd_file.log,' ');
343 fnd_file.put_line(fnd_file.log,l_msg_str_0);
344 fnd_file.put_line(fnd_file.log,RPAD('-',100,'-'));
345
346 fnd_file.put_line(fnd_file.log,l_msg_str_1);
347
348
349 EXCEPTION
350 WHEN others THEN
351 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
352 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
353 igs_ge_msg_stack.add;
354 app_exception.raise_exception;
355 END log_message;
356
357 PROCEDURE out_message( p_origination_id igf_gr_rfms_disb.origination_id%TYPE,
358 p_disb_ref_num igf_gr_rfms_disb.disb_ref_num%TYPE)
359 IS
360 ------------------------------------------------------------------------------
361 --
362 -- Created By : sjadhav
363 --
364 -- Date Created On : 2001/01/03
365 -- Purpose :This overloaded procedure formats the messages
366 -- to be put into log file.
367 -- Know limitations, enhancements or remarks
368 -- Change History
369 -- Who When What
370 --
371 -- (reverse chronological order - newest change first)
372 --
373 ------------------------------------------------------------------------------
374
375 l_msg_str_0 VARCHAR2(1000);
376 l_msg_str_1 VARCHAR2(1000);
377
378 BEGIN
379
380 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50) ||
381 RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_REF_NUM'),50);
382
383 l_msg_str_1 := RPAD(p_origination_id,30) ||
384 RPAD(p_disb_ref_num,30);
385
386 IF l_msg_prn_1 = TRUE THEN
387 fnd_message.set_name('IGF','IGF_GR_RECORDS_UPDATED');
388 fnd_file.put_line(fnd_file.output,fnd_message.get);
389 fnd_file.new_line(fnd_file.output,1);
390 fnd_file.put_line(fnd_file.output,l_msg_str_0);
391 fnd_file.put_line(fnd_file.output,RPAD('-',100,'-'));
392 l_msg_prn_1 := FALSE;
393 END IF;
394 fnd_file.put_line(fnd_file.output,l_msg_str_1);
395
396
397 EXCEPTION
398 WHEN others THEN
399 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
400 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
401 igs_ge_msg_stack.add;
402 app_exception.raise_exception;
403 END out_message;
404
405
406 PROCEDURE log_rej_message(p_origination_id igf_gr_rfms_disb.origination_id%TYPE,
407 p_disb_ref_num igf_gr_rfms_disb.disb_ref_num%TYPE,
408 p_edit_code igf_gr_rfms_disb.edit_code%TYPE)
409 IS
410 ------------------------------------------------------------------------------
411 --
412 -- Created By : sjadhav
413 --
414 -- Date Created On : 2001/01/03
415 -- Purpose :This overloaded procedure formats the messages
416 -- to be put into log file.
417 -- Know limitations, enhancements or remarks
418 -- Change History
419 -- Who When What
420 --
421 -- (reverse chronological order - newest change first)
422 --
423 ------------------------------------------------------------------------------
424
425 l_msg_str_0 VARCHAR2(1000) DEFAULT NULL;
426 l_msg_str_1 VARCHAR2(1000) DEFAULT NULL;
427 l_msg_str_2 VARCHAR2(1000) DEFAULT NULL;
428 l_msg_str_3 VARCHAR2(1000) DEFAULT NULL;
429
430 CURSOR cur_err_desc(l_err_cd igf_gr_rfms_error.edit_code%TYPE) IS
431 SELECT
432 igf_gr_rfms_error.message
433 FROM igf_gr_rfms_error
434 WHERE
435 igf_gr_rfms_error.edit_code = l_err_cd;
436
437 l_count NUMBER DEFAULT 1;
438 l_error_code igf_gr_rfms_error.edit_code%TYPE;
439 l_msg_desc VARCHAR2(4000) DEFAULT NULL;
440
441 BEGIN
442
443 IF NVL(TO_NUMBER(RTRIM(LTRIM(p_edit_code))),0) > 0 THEN
444 fnd_file.new_line(fnd_file.log,1);
445 fnd_message.set_name('IGF','IGF_GR_REC_CONTAIN_EDIT_CODES');
446 fnd_file.put_line(fnd_file.log,fnd_message.get);
447
448 l_msg_str_0 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ORIGINATION_ID'),50) ||
449 RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','DISB_REF_NUM'),50);
450
451 l_msg_str_1 := RPAD(p_origination_id,30) ||
452 RPAD(p_disb_ref_num,30);
453
454
455 l_msg_str_2 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','EDIT_CODE'),50);
456 fnd_file.put_line(fnd_file.log,l_msg_str_0);
457 fnd_file.put_line(fnd_file.log,RPAD('-',100,'-'));
458 fnd_file.put_line(fnd_file.log,l_msg_str_1);
459 fnd_file.put_line(fnd_file.log,' ');
460
461 IF NVL(p_edit_code,'0') <> '0' THEN
462 fnd_file.put_line(fnd_file.log,l_msg_str_2);
463 fnd_file.put_line(fnd_file.log,RPAD('-',50,'-'));
464 END IF;
465
466 FOR l_cn IN 1 .. 25 LOOP
467
468 l_error_code := NVL(SUBSTR(p_edit_code,l_count,3),'000');
469 IF l_error_code <>'000' THEN
470
471 l_msg_str_3 := RPAD(l_error_code,5);
472 fnd_file.put_line(fnd_file.log,l_msg_str_3);
473
474 END IF;
475 l_count := l_count + 3;
476 END LOOP;
477 END IF;
478
479
480 EXCEPTION
481
482 WHEN others THEN
483 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
484 fnd_message.set_token('NAME','IGF_GR_RFMS_ORIG.LOG_MESSAGE');
485 igs_ge_msg_stack.add;
486 app_exception.raise_exception;
487
488 END log_rej_message;
489
490
491 PROCEDURE rfms_disb_orig(
492 p_ci_cal_type IN VARCHAR2,
493 p_ci_sequence_number IN NUMBER,
494 p_base_id IN VARCHAR2,
495 p_reporting_pell IN VARCHAR2,
496 p_attending_pell IN VARCHAR2
497 ) IS
498 ------------------------------------------------------------------------------
499 --
500 -- Created By : sjadhav
501 --
502 -- Date Created On : 2001/01/03
503 -- Purpose : This procedure reads the data from igf_gr_rfms table
504 -- for the paricular award year and base id and loads the data
505 -- into a datafile after formatting
506 --
507 -- Know limitations, enhancements or remarks
508 -- Change History
509 -- Who When What
510 -- ugummall 10-DEC-2003 Bug 3252832. FA 131 - COD Updates
511 -- Added check - Amount should not be less than sum of disbursement amounts.
512 -- Added cursor cur_disb_amt_tot
513 -- ugummall 06-NOV-2003 Bug 3102439. FA 126 Multiple FA Offices.
514 -- 1. Added two extra parameters namely p_reporting_pell
515 -- and p_attending_pell to this procedure.
516 -- 2. Added cursor cur_attending_pell. Added check for attending pell
517 -- is a child of reporting pell. If not skipped Context Student's records.
518 -- 3. Parameters are shown in log file irrespective of wether cur_rfms
519 -- fetches the records or not. This is done for clarity.
520 -- ugummall 03-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
521 -- Added two extra parameters in call to igf_gr_gen.get_pell_header
522 -- rasahoo 16-Oct-2003 FA121-Bug# 3085558 cur_pymnt_isir_rec is initialised to null
523 -- cdcruz 16-Sep-03 FA121-Bug# 3085558 New Cursor added cur_pymnt_isir
524 -- That checks for the Transaction Number
525 -- On the Payment ISIR
526 -- Entire Looping Changed
527 --
528 -- (reverse chronological order - newest change first)
529 --
530 ------------------------------------------------------------------------------
531
532 -- l_header is removed, and new g_header is used.
533
534 l_record VARCHAR2(4000);
535 l_trailer VARCHAR2(1000);
536 l_num_of_rec NUMBER DEFAULT 0;
537 l_amount_total NUMBER DEFAULT 0;
538
539 p_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE;
540 l_batch_id VARCHAR2(60);
541 l_running_orig_id VARCHAR2(30);
542 l_last_disb_rec VARCHAR2(1);
543 l_valid_rec BOOLEAN;
544 I NUMBER;
545
546 CURSOR cur_pymnt_isir(
547 l_base_id igf_gr_rfms.base_id%TYPE
548 ) IS
549 SELECT isir.transaction_num
550 FROM igf_ap_isir_matched isir
551 WHERE isir.base_id = l_base_id
552 AND isir.payment_isir = 'Y' ;
553 cur_pymnt_isir_rec cur_pymnt_isir%rowtype;
554
555 -- Cursor to check attending pell id is a child of reporting pell id
556 CURSOR cur_attending_pell(
557 cp_ci_cal_type igf_gr_report_pell.ci_cal_type%TYPE,
558 cp_ci_sequence_number igf_gr_report_pell.ci_sequence_number%TYPE,
559 cp_reporting_pell igf_gr_report_pell.reporting_pell_cd%TYPE,
560 cp_attending_pell igf_gr_attend_pell.attending_pell_cd%TYPE
561 ) IS
562 SELECT 'Y'
563 FROM igf_gr_report_pell rep,
564 igf_gr_attend_pell att
565 WHERE rep.rcampus_id = att.rcampus_id
566 AND rep.ci_cal_type = cp_ci_cal_type
567 AND rep.ci_sequence_number = cp_ci_sequence_number
568 AND rep.reporting_pell_cd = cp_reporting_pell
569 AND att.attending_pell_cd = cp_attending_pell;
570 l_attending_pell_exists cur_attending_pell%ROWTYPE;
571
572 -- To get sum of disbursement amounts.
573 CURSOR cur_disb_amt_tot(cp_origination_id igf_gr_rfms_disb.origination_id%TYPE) IS
574 SELECT SUM(disb_amt) disb_amt_tot
575 FROM igf_gr_rfms_disb
576 WHERE origination_id = cp_origination_id;
577 rec_disb_amt_tot cur_disb_amt_tot%ROWTYPE;
578
579 BEGIN
580
581 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
582 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.rfms_disb_orig.debug','p_ci_cal_type:'||p_ci_cal_type);
583 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.rfms_disb_orig.debug','p_ci_sequence_number:'||p_ci_sequence_number);
584 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.rfms_disb_orig.debug','p_base_id:'||p_base_id);
585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.rfms_disb_orig.debug','p_reporting_pell:'||p_reporting_pell);
586 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.rfms_disb_orig.debug','p_attending_pell:'||p_attending_pell);
587 END IF;
588
589 -- FA 126. Passed extra parameters p_reporting_pell and p_attending_pell to this cursor.
590 OPEN cur_rfms_disb(p_base_id,p_ci_cal_type,p_ci_sequence_number, p_reporting_pell, p_attending_pell);
591 FETCH cur_rfms_disb INTO rec_rfms_disb;
592
593 -- If the table does not contain any data for this base_id or award_year
594 -- message is logged into log file and relevent details are also shown
595 IF cur_rfms_disb%NOTFOUND THEN
596 CLOSE cur_rfms_disb;
597 IF (g_persid_grp IS NULL)THEN
598 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
599 igs_ge_msg_stack.add;
600 fnd_file.put_line(fnd_file.log,fnd_message.get);
601 RAISE no_data_in_table;
602 ELSE
603 RAISE next_record;
604 END IF;
605 END IF;
606
607 -- since the table has data, prepare a header record
608 g_header := igf_gr_gen.get_pell_header(g_ver_num,
609 l_cy_yr,
610 p_reporting_pell,
611 '#D',
612 p_rfmb_id,
613 l_batch_id,
614 p_ci_cal_type,
615 p_ci_sequence_number);
616
617 -- Header can not be written into output datafile unless and until we are sure that
618 -- at least one data record will be created. Hence commenting out the following line.
619 -- fnd_file.put_line(fnd_file.output,UPPER(l_header));
620 -- Header(above line) will be written in prepare_data procedure just before first
621 -- data record is written. by ugummall.
622
623 fnd_file.new_line(fnd_file.log,1);
624 fnd_file.put_line(fnd_file.log,RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BATCH_ID'),10)||' :'
625 ||' '|| l_batch_id);
626 fnd_file.new_line(fnd_file.log,1);
627
628 l_amount_total := 0;
629 l_num_of_rec := 0;
630
631
632 -- Initiallise the current Origination ID to -1
633 l_running_orig_id := '-1';
634 l_last_disb_rec := 'N' ;
635
636 LOOP
637 IF (l_running_orig_id <> rec_rfms_disb.origination_id) THEN
638
639 -- Student in Context has changed so do the Transaction Check as well Attending Pell child check.
640 l_running_orig_id := rec_rfms_disb.origination_id ;
641 l_valid_rec := TRUE;
642
643 fnd_message.set_name('IGF','IGF_GR_RFMS_ORG_ID');
644 fnd_message.set_token('ORIG_ID',l_running_orig_id);
645 fnd_file.put_line(fnd_file.log,fnd_message.get);
646
647 -- Get The Payment ISIR Transaction Number
648 cur_pymnt_isir_rec := NULL;
649 OPEN cur_pymnt_isir(rec_rfms_disb.base_id);
650 FETCH cur_pymnt_isir INTO cur_pymnt_isir_rec;
651 CLOSE cur_pymnt_isir;
652
653 -- If the Transaction Number being reported does not match do not Originate
654 IF rec_rfms_disb.transaction_num <> NVL(cur_pymnt_isir_rec.transaction_num,-1) THEN
655 l_valid_rec := FALSE;
656
657 IF cur_pymnt_isir_rec.transaction_num IS NULL THEN
658 fnd_message.set_name('IGF','IGF_AP_NO_PAYMENT_ISIR');
659 fnd_file.put_line(fnd_file.log,fnd_message.get);
660 ELSE
661 fnd_message.set_name('IGF','IGF_GR_PYMNT_ISIR_MISMATCH');
662 fnd_file.put_line(fnd_file.log,fnd_message.get);
663 END IF;
664 END IF;
665
666 -- check if attending pell is a child of reporting pell id.
667 l_attending_pell_exists := NULL;
668 OPEN cur_attending_pell(rec_rfms_disb.ci_cal_type,
669 rec_rfms_disb.ci_sequence_number,
670 rec_rfms_disb.rep_pell_id,
671 rec_rfms_disb.attending_campus_id);
672 FETCH cur_attending_pell INTO l_attending_pell_exists;
673
674 -- If Attending pell child record does not exist?
675 IF (cur_attending_pell%NOTFOUND) THEN
676 l_valid_rec := FALSE;
677
678 FND_MESSAGE.SET_NAME('IGF', 'IGF_GR_ATTEND_PELL_NOT_SETUP');
679 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
680 END IF;
681 CLOSE cur_attending_pell;
682
683 -- Check if sum of disbursement amounts is less than origination record's amount.
684 rec_disb_amt_tot := NULL;
685 OPEN cur_disb_amt_tot(rec_rfms_disb.origination_id);
686 FETCH cur_disb_amt_tot INTO rec_disb_amt_tot;
687 CLOSE cur_disb_amt_tot;
688
689 IF (rec_rfms_disb.pell_amount < rec_disb_amt_tot.disb_amt_tot) THEN
690 l_valid_rec := FALSE;
691 fnd_message.set_name('IGF','IGF_GR_PELL_DIFF_AMTS');
692 FND_MESSAGE.SET_TOKEN('DISB_AMT', TO_CHAR(rec_disb_amt_tot.disb_amt_tot));
693 FND_MESSAGE.SET_TOKEN('PELL_TOT', TO_CHAR(rec_rfms_disb.pell_amount));
694
695 fnd_file.put_line(fnd_file.log,fnd_message.get);
696 END IF;
697
698 -- if Context student record is invalid for sending his disbersements
699 -- due to transaction number mismatch or attending pell child does not exist.
700 IF NOT l_valid_rec THEN
701 -- Since context student record is not valid, skip all his records.
702 LOOP
703 FETCH cur_rfms_disb INTO rec_rfms_disb;
704 IF (cur_rfms_disb%NOTFOUND) THEN
705 l_last_disb_rec := 'Y';
706 EXIT;
707 END IF;
708 IF (l_running_orig_id <> rec_rfms_disb.origination_id) THEN
709 -- New Student arrived.
710 EXIT;
711 END IF;
712 END LOOP; -- end of skipping context student's records.
713 END IF; -- end of context student record is invalid.
714 END IF; -- end of Student in Context has changed.
715
716 IF l_last_disb_rec = 'Y' THEN
717 EXIT ;
718 END IF;
719
720 IF (l_valid_rec) THEN
721 l_amount_total := l_amount_total + NVL(rec_rfms_disb.disb_amt,0); -- Check this
722 prepare_data(rec_rfms_disb,l_num_of_rec,p_rfmb_id);
723 FETCH cur_rfms_disb INTO rec_rfms_disb;
724 EXIT WHEN cur_rfms_disb%NOTFOUND;
725 END IF;
726 END LOOP;
727
728 CLOSE cur_rfms_disb;
729 -- since the table has data, prepare a trailer record
730 l_trailer := igf_gr_gen.get_pell_trailer(g_ver_num,
731 l_cy_yr,
732 p_reporting_pell,
733 '#D',
734 l_num_of_rec,
735 l_amount_total,
736 l_batch_id);
737
738 fnd_file.new_line(fnd_file.log,1);
739 fnd_message.set_name('IGF','IGF_GR_ORIG_REC_NUM');
740 fnd_message.set_token('TOT_NUM',TO_CHAR(l_num_of_rec));
741 fnd_file.put_line(fnd_file.log,fnd_message.get);
742 -- p_num_of_rec || ' records written into data file
743 fnd_file.new_line(fnd_file.log,1);
744
745 IF l_num_of_rec > 0 THEN
746 fnd_file.put_line(fnd_file.output,UPPER(l_trailer));
747 ELSE
748 RAISE batch_not_created;
749 END IF;
750
751 EXCEPTION
752 WHEN igf_gr_gen.no_file_version THEN
753 RAISE;
754 WHEN batch_not_created THEN
755 RAISE;
756 WHEN no_data_in_table THEN
757 RAISE;
758 WHEN next_record THEN
759 RAISE;
760 WHEN OTHERS THEN
761 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
762 fnd_message.set_token('NAME','IGF_GR_RFMS_DISB_ORIG.RFMS_DISB_ORIG');
763 igs_ge_msg_stack.add;
764 app_exception.raise_exception;
765 END rfms_disb_orig;
766
767
768 PROCEDURE rfms_disb_ack IS
769
770 ------------------------------------------------------------------------------
771 --
772 -- Created By : sjadhav
773 --
774 -- Date Created On : 2001/01/03
775 -- Purpose : This procedure reads the data from datafile(RFMS Ack File )
776 -- and loads data into origination table after formatting
777 -- Know limitations, enhancements or remarks
778 -- Change History
779 -- Who When What
780 -- rasahoo 13-May-2003 Bug #2938258 If Disbursement Record is not part of this batch
781 -- in the System then raise error.Cannot process Disbursement
782 -- Record which status for acknowledgement processing is not "Sent".
783 --------------------------------------------------------------------------------------------
784 --
785 -- (reverse chronological order - newest change first)
786 --
787 ------------------------------------------------------------------------------
788
789 l_last_gldr_id NUMBER;
790 l_number_rec NUMBER;
791 l_count NUMBER DEFAULT 1;
792 l_batch_id VARCHAR2(100);
793 l_rfms_process_dt VARCHAR2(200);
794 lp_count NUMBER DEFAULT 0;
795 lf_count NUMBER DEFAULT 0;
796 lv_message fnd_new_messages.message_name%TYPE;
797 SYSTEM_STATUS VARCHAR2(20);
798 ----Bug #2938258
799
800 CURSOR cur_gr_rfmb_disb ( p_batch_id igf_gr_rfms_batch.batch_id%TYPE)
801 IS
802 SELECT
803 rfmb_id
804 FROM
805 igf_gr_rfms_batch
806 WHERE batch_id = p_batch_id;
807
808 cur_get_rfmb_disb cur_gr_rfmb_disb%ROWTYPE;
809 l_rfmb_id igf_gr_rfms_batch.rfmb_id%TYPE;
810 --end -Bug #2938258
811 BEGIN
812
813 igf_gr_gen.process_pell_ack (g_ver_num,
814 'GR_RFMS_DISB_ORIG',
815 l_number_rec,
816 l_last_gldr_id,
817 l_batch_id);
818
819 ----Bug #2938258
820
821 OPEN cur_gr_rfmb_disb(l_batch_id);
822 FETCH cur_gr_rfmb_disb INTO cur_get_rfmb_disb;
823 CLOSE cur_gr_rfmb_disb;
824 l_rfmb_id := cur_get_rfmb_disb.rfmb_id;
825 --end -Bug #2938258
826 --
827 -- Check the award year matches with the award year in PELL setup.
828 --
829 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, lv_message);
830
831 IF lv_message = 'IGF_GR_VRSN_MISMTCH' THEN
832 fnd_message.set_name('IGF','IGF_GR_VRSN_MISMTCH');
833 fnd_message.set_token('CYCL',SUBSTR(l_batch_id,3,4));
834 fnd_message.set_token('BATCH',l_batch_id);
835 fnd_message.set_token('VRSN',g_ver_num);
836 fnd_message.set_token('AWD_YR',g_alt_code);
837 fnd_file.put_line(fnd_file.log,fnd_message.get);
838 RAISE invalid_version;
839 END IF;
840
841 IF l_number_rec > 0 THEN
842
843 DECLARE
844
845 l_actual_rec NUMBER DEFAULT 0;
846 l_origination_id igf_gr_rfms.origination_id%TYPE;
847 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
848 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
849 l_chk_flag BOOLEAN DEFAULT FALSE;
850
851 CURSOR cur_award(l_origination_id igf_gr_rfms.origination_id%TYPE) IS
852 SELECT
853 *
854 FROM
855 igf_gr_rfms
856 WHERE
857 origination_id = l_origination_id;
858
859 rec_award cur_award%ROWTYPE;
860
861 CURSOR c_rfms_data IS
862 SELECT record_data
863 FROM
864 igf_gr_load_file_t
865 WHERE
866 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1)
867 AND file_type = 'GR_RFMS_DISB_ORIG';
868
869 CURSOR cur_disb_orig(l_origination_id igf_gr_rfms_disb.origination_id%TYPE,
870 l_disb_ref_num igf_gr_rfms_disb.disb_ref_num%TYPE)
871 IS
872 SELECT *
873 FROM igf_gr_rfms_disb
874 WHERE
875 origination_id = l_origination_id AND
876 disb_ref_num = l_disb_ref_num
877 FOR UPDATE OF edit_code NOWAIT;
878
879 rec_disb_orig cur_disb_orig%ROWTYPE;
880 l_rfms igf_gr_rfms_disb%ROWTYPE;
881 l_disb_ref_num igf_gr_rfms_disb.disb_ref_num%TYPE;
882
883 BEGIN
884
885 FOR rec_data IN c_rfms_data LOOP
886
887 BEGIN -- For Loop Inner Begin
888
889 l_actual_rec := l_actual_rec + 1;
890
891
892 BEGIN
893 IF g_ver_num IN ('2002-2003','2003-2004','2004-2005') THEN
894
895 l_rfms.origination_id := LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23)));
896 l_disb_ref_num := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,38,2)))),0);
897 l_rfms.disb_ack_act_status := LTRIM(RTRIM(SUBSTR(rec_data.record_data,37,1)));
898 l_rfms.accpt_db_cr_flag := LTRIM(RTRIM(SUBSTR(rec_data.record_data,40,1)));
899 l_rfms.disb_accpt_amt := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,41,7))))/100,0); -- check
900 l_rfms.accpt_disb_dt := fnd_date.string_to_date(LTRIM(RTRIM(SUBSTR(rec_data.record_data,48,8))),'YYYYMMDD');
901 l_rfms.disb_ytd_amt := NVL(TO_NUMBER(RTRIM(LTRIM(SUBSTR(rec_data.record_data,101,7))))/100,0); -- check
902 l_rfms.accpt_pymt_prd_start_dt := fnd_date.string_to_date(LTRIM(RTRIM(SUBSTR(rec_data.record_data,56,8))),'YYYYMMDD');
903 l_rfms.edit_code := LTRIM(RTRIM(SUBSTR(rec_data.record_data,108,75)));
904 l_rfms.ed_use_flags := LTRIM(RTRIM(SUBSTR(rec_data.record_data,183,10)));
905
906 ELSE
907 RAISE igf_gr_gen.no_file_version;
908 END IF;
909
910
911 EXCEPTION
912 -- The exception caught here will be the data format exceptions
913 WHEN OTHERS THEN
914 lf_count := lf_count + 1;
915 fnd_file.put_line(fnd_file.log, ' ' );
916 fnd_message.set_name('IGF','IGF_GR_DISB_INVALID_RECORD');
917 fnd_message.set_token('ORIG_ID',l_rfms.origination_id);
918 -- Cannot Process Record for Origination ORIG_ID as it contains corrupt data
919 fnd_file.put_line(fnd_file.log,fnd_message.get);
920
921 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
922 fnd_file.put_line(fnd_file.log,fnd_message.get);
923 fnd_file.put_line(fnd_file.log, ' ' );
924
925 RAISE igf_gr_gen.skip_this_record;
926
927 END;
928
929 OPEN cur_award(l_rfms.origination_id);
930 FETCH cur_award INTO rec_award;
931
932 IF cur_award%NOTFOUND THEN
933 CLOSE cur_award;
934 log_message(l_batch_id,l_rfms.origination_id);
935 RAISE igf_gr_gen.skip_this_record;
936 END IF;
937
938 OPEN cur_disb_orig(l_rfms.origination_id,l_disb_ref_num);
939 FETCH cur_disb_orig INTO rec_disb_orig;
940
941 IF cur_disb_orig%NOTFOUND THEN
942 CLOSE cur_disb_orig;
943 fnd_file.put_line(fnd_file.log,' ');
944 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
945 fnd_file.put_line(fnd_file.log,fnd_message.get);
946 --write to log file
947 log_message(l_batch_id,LTRIM(RTRIM(SUBSTR(rec_data.record_data,1,23))));
948 RAISE igf_gr_gen.skip_this_record;
949 END IF;
950
951 ----Bug #2938258
952 IF l_rfmb_id<>rec_disb_orig.rfmb_id THEN
953
954 fnd_message.set_name('IGF','IGF_GR_DISB_BATCH_MISMATCH');
955 fnd_message.set_token('BATCH_ID',l_batch_id);
956 fnd_message.set_token('DISB_ID',l_rfms.origination_id );
957 fnd_file.put_line(fnd_file.log,fnd_message.get);
958 fnd_file.new_line(fnd_file.log,1);
959 RAISE igf_gr_gen.skip_this_record;
960 END IF;
961
962 IF rec_disb_orig.disb_ack_act_status <> 'S' THEN
963
964
965 fnd_message.set_name('IGF','IGF_GR_DISB_NOT_IN_SENT');
966 fnd_message.set_token('DISB_ID',l_rfms.origination_id );
967 SYSTEM_STATUS := igf_aw_gen.lookup_desc('IGF_GR_ORIG_STATUS',rec_disb_orig.disb_ack_act_status);
968 fnd_message.set_token('SYS_STATUS',SYSTEM_STATUS );
969 fnd_file.put_line(fnd_file.log,fnd_message.get);
970 fnd_file.new_line(fnd_file.log,1);
971
972 RAISE igf_gr_gen.skip_this_record;
973 END IF;
974
975 --end -Bug #2938258
976
977 l_rfms.disb_ref_num := rec_disb_orig.disb_ref_num;
978 l_rfms.rfmd_id := rec_disb_orig.rfmd_id;
979 l_rfms.rfmb_id := rec_disb_orig.rfmb_id;
980 l_rfms.disb_amt := rec_disb_orig.disb_amt;
981 l_rfms.disb_dt := rec_disb_orig.disb_dt;
982 l_rfms.db_cr_flag := rec_disb_orig.db_cr_flag;
983 l_rfms.pymt_prd_start_dt := rec_disb_orig.accpt_pymt_prd_start_dt;
984 l_rfms.disb_status_dt := TRUNC(SYSDATE);
985
986 lp_count := lp_count + 1;
987 igf_gr_rfms_disb_pkg.update_row (
988 x_rowid => rec_disb_orig.row_id,
989 x_rfmd_id => l_rfms.rfmd_id ,
990 x_origination_id => l_rfms.origination_id,
991 x_disb_ref_num => l_rfms.disb_ref_num,
992 x_disb_dt => l_rfms.disb_dt,
993 x_disb_amt => l_rfms.disb_amt,
994 x_db_cr_flag => l_rfms.db_cr_flag,
995 x_disb_ack_act_status => l_rfms.disb_ack_act_status,
996 x_disb_status_dt => l_rfms.disb_status_dt,
997 x_accpt_disb_dt => l_rfms.accpt_disb_dt ,
998 x_disb_accpt_amt => l_rfms.disb_accpt_amt,
999 x_accpt_db_cr_flag => l_rfms.accpt_db_cr_flag,
1000 x_disb_ytd_amt => l_rfms.disb_ytd_amt,
1001 x_pymt_prd_start_dt => l_rfms.pymt_prd_start_dt,
1002 x_accpt_pymt_prd_start_dt => l_rfms.accpt_pymt_prd_start_dt,
1003 x_edit_code => l_rfms.edit_code,
1004 x_rfmb_id => l_rfms.rfmb_id,
1005 x_mode => 'R',
1006 x_ed_use_flags => l_rfms.ed_use_flags);
1007
1008 --write to output file
1009
1010 out_message( l_rfms.origination_id,
1011 l_rfms.disb_ref_num);
1012
1013 --write to log file
1014 log_rej_message( l_rfms.origination_id,
1015 l_rfms.disb_ref_num,
1016 l_rfms.edit_code);
1017
1018 IF l_rfms.disb_accpt_amt <> l_rfms.disb_amt THEN
1019
1020 igf_gr_gen.insert_sys_holds(rec_award.award_id,rec_disb_orig.disb_ref_num,'PELL');
1021 fnd_message.set_name('IGF','IGF_GR_DIFF_PELL_DISB');
1022 fnd_message.set_token('ORIG_ID',l_rfms.origination_id);
1023 fnd_message.set_token('DISB_NUM',l_rfms.disb_ref_num);
1024 fnd_file.put_line(fnd_file.log,fnd_message.get);
1025
1026 fnd_message.set_name('IGF','IGF_GR_REPORTED_AMT');
1027 fnd_message.set_token('AMT', TO_CHAR(l_rfms.disb_amt));
1028 fnd_file.put_line(fnd_file.log,fnd_message.get);
1029
1030 fnd_message.set_name('IGF','IGF_GR_RECEIVED_AMT');
1031 fnd_message.set_token('AMT', TO_CHAR(l_rfms.disb_accpt_amt));
1032 fnd_file.put_line(fnd_file.log,fnd_message.get);
1033 fnd_file.new_line(fnd_file.log,1);
1034
1035 END IF;
1036
1037 CLOSE cur_award;
1038 CLOSE cur_disb_orig;
1039
1040 EXCEPTION
1041
1042 WHEN igf_gr_gen.skip_this_record THEN
1043 IF cur_award%ISOPEN THEN
1044 CLOSE cur_award;
1045 END IF;
1046 IF cur_disb_orig%ISOPEN THEN
1047 CLOSE cur_disb_orig;
1048 END IF;
1049
1050 END; -- For Loop Inner Begin-End
1051
1052 END LOOP;
1053
1054 IF l_actual_rec <> l_number_rec THEN
1055 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
1056 igs_ge_msg_stack.add;
1057 RAISE igf_gr_gen.file_not_loaded;
1058 END IF;
1059
1060 END; -- Inner Begin
1061
1062 fnd_file.new_line(fnd_file.log,2);
1063
1064 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
1065 fnd_message.set_token('CNT',l_number_rec);
1066 fnd_file.put_line(fnd_file.log,fnd_message.get);
1067
1068 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
1069 fnd_message.set_token('CNT',lp_count);
1070 fnd_file.put_line(fnd_file.log,fnd_message.get);
1071
1072 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
1073 fnd_message.set_token('CNT',lf_count);
1074 fnd_file.put_line(fnd_file.log,fnd_message.get);
1075
1076 END IF; -- if l_num_rec --
1077
1078 EXCEPTION
1079
1080 WHEN invalid_version THEN
1081 RAISE;
1082 WHEN igf_gr_gen.no_file_version THEN
1083 RAISE;
1084 WHEN igf_gr_gen.corrupt_data_file THEN
1085 RAISE;
1086 WHEN no_data_in_table THEN
1087 RAISE;
1088 WHEN igf_gr_gen.batch_not_in_system THEN
1089 -- Bug # 4008991
1090 fnd_message.set_name('IGF','IGF_SL_GR_BATCH_DOES_NO_EXIST');
1091 fnd_message.set_token('BATCH_ID',l_batch_id);
1092 RAISE;
1093 WHEN igf_gr_gen.file_not_loaded THEN
1094 RAISE;
1095 WHEN OTHERS THEN
1096 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1097 fnd_message.set_token('NAME','IGF_GR_RFMS_DISB_ORIG.RFMS_DISB_ACK');
1098 igs_ge_msg_stack.add;
1099 app_exception.raise_exception;
1100 END rfms_disb_ack;
1101
1102
1103 PROCEDURE main(
1104 errbuf OUT NOCOPY VARCHAR2,
1105 retcode OUT NOCOPY NUMBER,
1106 award_year IN VARCHAR2,
1107 p_reporting_pell IN VARCHAR2,
1108 p_attending_pell IN VARCHAR2,
1109 p_trans_type IN VARCHAR2,
1110 base_id IN igf_gr_rfms_all.base_id%TYPE,
1111 p_dummy IN VARCHAR2,
1112 p_pers_id_grp IN NUMBER
1113 )
1114 AS
1115
1116 ------------------------------------------------------------------------------
1117 --
1118 -- Created By : sjadhav
1119 --
1120 -- Date Created On : 2001/01/03
1121 --
1122 -- Purpose :This is the procedeure called by con prog.
1123 -- This procedure updates igf_aw_awd_disb table based on
1124 -- certain conditions.
1125 -- This process will set the status of all the records
1126 -- processed as 'Processed' in the igf_db_cl_disb_resp
1127 -- table.
1128 --
1129 -- Know limitations, enhancements or remarks
1130 -- Change History
1131 -- Who When What
1132 -- ridas 08-FEB-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1133 -- tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1134 -- ugummall 08-JAN-2004 Bug 3318202. Changed the order of parameters and removed p_org_id
1135 -- ugummall 06-NOV-2003 Bug 3102439. FA 126 - Multiple FA Offices.
1136 -- 1. Added two parameters to this procedure namely
1137 -- p_reporting_pell and p_attending_pell
1138 -- 2. base_id and p_attending_pell are mutually exclusive. Added this check.
1139 --
1140 -- brajendr 02-Sep-2002 Bug # 2483249
1141 -- Modifed the messages as per the message standards
1142
1143 -- bug 2216956
1144 -- sjadhav, FEB13th,2002
1145 --
1146 -- Removed flag and disbursement number parameters
1147 -- Added Award Year parameter to main_ack
1148 --
1149 --
1150 -- Bug ID : 1731177
1151 -- Who When What
1152 -- sjadhav 19-apr-2001 Added main_ack to process
1153 -- rfms disb ack . this will get
1154 -- called from conc. mgr.
1155 -- Removed l_mode from main
1156 --
1157 --(reverse chronological order - newest change first)
1158 --
1159 ------------------------------------------------------------------------------
1160
1161 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
1162 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
1163 ln_base_id NUMBER;
1164 lv_status VARCHAR2(1);
1165 l_list VARCHAR2(32767);
1166 TYPE base_idRefCur IS REF CURSOR;
1167 c_base_id base_idRefCur;
1168
1169 l_msg_str_1 VARCHAR2(1000);
1170 l_msg_str_2 VARCHAR2(1000);
1171 l_msg_str_3 VARCHAR2(1000);
1172 l_msg_str_4 VARCHAR2(1000);
1173 l_msg_str_5 VARCHAR2(1000);
1174 l_msg_str_6 VARCHAR2(1000);
1175 -- Get person id group name
1176 CURSOR c_pers_id_grp_name(
1177 cp_persid_grp igs_pe_persid_group_all.group_id%TYPE
1178 ) IS
1179 SELECT group_cd group_name
1180 FROM igs_pe_persid_group_all
1181 WHERE group_id = cp_persid_grp;
1182 l_pers_id_grp_name c_pers_id_grp_name%ROWTYPE;
1183
1184 l_error NUMBER;
1185 l_record NUMBER;
1186 l_cod_year_flag BOOLEAN;
1187 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1188
1189 BEGIN
1190 igf_aw_gen.set_org_id(NULL);
1191 retcode := 0;
1192
1193 ln_base_id := base_id;
1194 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(award_year,1,10)));
1195 l_ci_sequence_number := TO_NUMBER(SUBSTR(award_year,11));
1196 g_trans_type := p_trans_type;
1197 g_persid_grp := p_pers_id_grp;
1198
1199 -- Check wether the awarding year is COD-XML processing Year or not
1200 l_cod_year_flag := NULL;
1201 l_cod_year_flag := igf_sl_dl_validation.check_full_participant (l_ci_cal_type,l_ci_sequence_number,'PELL');
1202
1203 -- This process is allowed to run only for PHASE_IN_PARTICIPANT
1204 -- If the award year is FULL_PARTICIPANT then raise the error message
1205 -- and stop processing else continue the process
1206 IF l_cod_year_flag THEN
1207
1208 fnd_message.set_name('IGF','IGF_GR_COD_NO_DISB');
1209 fnd_file.put_line(fnd_file.log,fnd_message.get);
1210 RETURN;
1211
1212 END IF;
1213
1214 -- FA 126. base_id and attending pell are mutually exclusive.
1215 IF (base_id IS NOT NULL AND p_attending_pell IS NOT NULL) OR (p_pers_id_grp IS NOT NULL AND p_attending_pell IS NOT NULL) THEN
1216 FND_MESSAGE.SET_NAME('IGF', 'IGF_GR_PORIG_INCOMPAT');
1217 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
1218 RETURN;
1219 END IF;
1220
1221 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
1222 RAISE param_error;
1223 END IF;
1224
1225 IF ln_base_id IS NOT NULL AND p_pers_id_grp IS NOT NULL THEN
1226 RAISE param_error;
1227 END IF;
1228 --
1229 -- Get the Flat File Version and then Proceed
1230 --
1231 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
1232
1233 --
1234 -- Get the Cycle Year
1235 --
1236 l_cy_yr := igf_gr_gen.get_cycle_year(l_ci_cal_type,l_ci_sequence_number);
1237
1238 IF g_ver_num ='NULL' THEN
1239 RAISE igf_gr_gen.no_file_version;
1240 END IF;
1241
1242 -- show parameter 1 - award year
1243 l_msg_str_1 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','AWARD_YEAR'),20) || RPAD(igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number),20);
1244 fnd_file.put_line(fnd_file.log,l_msg_str_1);
1245
1246 -- show parameter 2 - report pell id
1247 l_msg_str_3 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'REPORT_PELL'),20) || p_reporting_pell;
1248 fnd_file.put_line(fnd_file.log,l_msg_str_3);
1249
1250 -- show parameter 3 - attend pell id
1251 IF (p_attending_pell IS NOT NULL) THEN
1252 l_msg_str_4 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'ATTEND_PELL'),20) || p_attending_pell;
1253 fnd_file.put_line(fnd_file.log,l_msg_str_4);
1254 END IF;
1255
1256 -- show trans_type parameter
1257 l_msg_str_6 := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS', 'TRANS_TYPE'),20) || igf_aw_gen.lookup_desc('IGF_GR_TRANS_TYPE',p_trans_type);
1258 fnd_file.put_line(fnd_file.log,l_msg_str_6);
1259
1260 -- show parameter 4 - base id
1261 IF (base_id IS NOT NULL) THEN
1262 l_msg_str_2 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BASE_ID'),20) || RPAD(igf_gr_gen.get_per_num(base_id),20);
1263 fnd_file.put_line(fnd_file.log,l_msg_str_2);
1264 END IF;
1265
1266 -- show parameter 5 - person id group
1267 IF p_pers_id_grp IS NOT NULL THEN
1268 OPEN c_pers_id_grp_name(p_pers_id_grp);
1269 FETCH c_pers_id_grp_name INTO l_pers_id_grp_name;
1270 CLOSE c_pers_id_grp_name;
1271 l_msg_str_5 := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS','PERSON_ID_GROUP'),20) || l_pers_id_grp_name.group_name;
1272 fnd_file.put_line(fnd_file.log,l_msg_str_5);
1273 END IF;
1274 fnd_file.new_line(fnd_file.log,1);
1275
1276 /*
1277 Main logic starts here.
1278 */
1279 IF ln_base_id IS NOT NULL THEN
1280 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1281 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.main.debug','(A)calling rfms_disb_orig with base_id:'||ln_base_id);
1282 END IF;
1283 --call the main processing routine
1284 rfms_disb_orig(l_ci_cal_type,l_ci_sequence_number,ln_base_id, p_reporting_pell, p_attending_pell);
1285
1286 ELSIF p_attending_pell IS NOT NULL THEN
1287 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1288 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.main.debug','(B)calling rfms_disb_orig with attedning pell:'||p_attending_pell);
1289 END IF;
1290 --call the main processing routine
1291 rfms_disb_orig(l_ci_cal_type,l_ci_sequence_number,ln_base_id, p_reporting_pell, p_attending_pell);
1292
1293 ELSIF p_pers_id_grp IS NOT NULL THEN
1294 --Bug #5021084
1295 l_list := igf_ap_ss_pkg.get_pid(p_pers_id_grp,lv_status,lv_group_type);
1296
1297 --Bug #5021084. Passing Group ID if the group type is STATIC.
1298 IF lv_group_type = 'STATIC' THEN
1299 OPEN c_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_ci_cal_type,l_ci_sequence_number,p_pers_id_grp;
1300 ELSIF lv_group_type = 'DYNAMIC' THEN
1301 OPEN c_base_id FOR ' SELECT base_id FROM igf_ap_fa_base_rec_all WHERE ci_cal_type = :p_ci_cal_type AND ci_sequence_number = :p_ci_sequence_number AND person_id IN (' || l_list || ') ' USING l_ci_cal_type,l_ci_sequence_number;
1302 END IF;
1303
1304 FETCH c_base_id INTO ln_base_id;
1305
1306 IF c_base_id%FOUND THEN
1307
1308 WHILE c_base_id%FOUND LOOP
1309 BEGIN
1310 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1311 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_rfms_disb_orig.main.debug','(C)calling rfms_disb_orig with base_id:'||ln_base_id);
1312 END IF;
1313
1314 l_record := NVL(l_record,0) + 1;
1315 --call the main processing routine
1316 rfms_disb_orig(l_ci_cal_type,l_ci_sequence_number,ln_base_id, p_reporting_pell, p_attending_pell);
1317 EXCEPTION
1318 WHEN next_record THEN
1319 l_error := NVL(l_error,0) + 1;
1320 END;
1321 FETCH c_base_id INTO ln_base_id;
1322 END LOOP;
1323 CLOSE c_base_id;
1324 IF l_error = l_record THEN
1325 RAISE no_data_in_table;
1326 END IF;
1327 ELSE
1328 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
1329 fnd_file.put_line(fnd_file.log,fnd_message.get);
1330 END IF;
1331 ELSE
1332 RAISE param_error;
1333 END IF;
1334
1335 COMMIT;
1336
1337 EXCEPTION
1338 WHEN igf_gr_gen.no_file_version THEN
1339 ROLLBACK;
1340 retcode := 2;
1341 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
1342 fnd_file.put_line(fnd_file.log,errbuf);
1343 WHEN batch_not_created THEN
1344 ROLLBACK;
1345 WHEN param_error THEN
1346 ROLLBACK;
1347 retcode := 2;
1348 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
1349 fnd_file.put_line(fnd_file.log,errbuf);
1350 WHEN no_data_in_table THEN
1351 ROLLBACK;
1352 errbuf := fnd_message.get_string('IGF','IGF_AP_NO_DATA_FOUND');
1353 fnd_file.put_line(fnd_file.log,errbuf);
1354 WHEN OTHERS THEN
1355 ROLLBACK;
1356 retcode := 2;
1357 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || SQLERRM;
1358 igs_ge_msg_stack.conc_exception_hndl;
1359 END main;
1360
1361
1362 PROCEDURE main_ack( errbuf OUT NOCOPY VARCHAR2,
1363 retcode OUT NOCOPY NUMBER,
1364 p_awd_yr IN VARCHAR2,
1365 p_org_id IN NUMBER)
1366 AS
1367
1368 ------------------------------------------------------------------------------
1369 -- This process is called from concurrent manager. This process
1370 -- will invoke rfms disbursements acknowledgement.
1371 -- Who When What
1372 --
1373 -- bug 2216956
1374 -- sjadhav, FEB13th,2002
1375 --
1376 -- Removed flag and disbursement number parameters
1377 -- Added Award Year parameter to main_ack
1378 --
1379 -- sjadhav 19-apr-2001 Added main_ack to process
1380 -- rfms disb ack . this will get
1381 -- called from conc. mgr.
1382 -- Removed l_mode from main
1383 --
1384 -- (reverse chronological order - newest change first)
1385 --
1386 ------------------------------------------------------------------------------
1387
1388 l_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
1389 l_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
1390
1391 BEGIN
1392
1393 retcode := 0;
1394 igf_aw_gen.set_org_id(p_org_id);
1395 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
1396 l_ci_sequence_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
1397
1398 IF l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
1399 RAISE param_error;
1400 END IF;
1401
1402 -- Check wether the awarding year is COD-XML processing Year or not
1403 -- This process is allowed to run only for PHASE_IN_PARTICIPANT
1404 -- If the award year is FULL_PARTICIPANT then raise the error message
1405 -- and stop processing else continue the process
1406 IF igf_sl_dl_validation.check_full_participant (l_ci_cal_type,l_ci_sequence_number,'PELL') THEN
1407
1408 fnd_message.set_name('IGF','IGF_GR_COD_NO_DISB_ACK');
1409 fnd_file.put_line(fnd_file.log,fnd_message.get);
1410 RETURN;
1411
1412 END IF;
1413
1414 --
1415 -- Get the Flat File Version and then Proceed
1416 --
1417
1418 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
1419 g_alt_code := igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number);
1420 l_cy_yr := igf_gr_gen.get_cycle_year(l_ci_cal_type,l_ci_sequence_number);
1421
1422 IF g_ver_num ='NULL' THEN
1423 RAISE igf_gr_gen.no_file_version;
1424 ELSE
1425 rfms_disb_ack;
1426 END IF;
1427
1428 COMMIT;
1429
1430 EXCEPTION
1431
1432 WHEN invalid_version THEN
1433 ROLLBACK;
1434 retcode := 2;
1435
1436 WHEN igf_gr_gen.no_file_version THEN
1437 ROLLBACK;
1438 retcode := 2;
1439 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
1440 fnd_file.put_line(fnd_file.log,errbuf);
1441
1442 WHEN batch_not_created THEN
1443 ROLLBACK;
1444
1445 WHEN param_error THEN
1446 ROLLBACK;
1447 retcode := 2;
1448 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
1449 fnd_file.put_line(fnd_file.log,errbuf);
1450
1451 WHEN igf_gr_gen.corrupt_data_file THEN
1452 ROLLBACK;
1453 retcode := 2;
1454 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
1455 fnd_file.put_line(fnd_file.log,errbuf);
1456
1457 WHEN igf_gr_gen.batch_not_in_system THEN
1458 ROLLBACK;
1459 retcode := 2;
1460 errbuf := fnd_message.get; -- Bug # 4008991
1461 fnd_file.put_line(fnd_file.log,errbuf);
1462
1463 WHEN igf_gr_gen.file_not_loaded THEN
1464 ROLLBACK;
1465 retcode := 2;
1466 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
1467 fnd_file.put_line(fnd_file.log,errbuf);
1468
1469 WHEN no_data_in_table THEN
1470 ROLLBACK;
1471 errbuf := fnd_message.get_string('IGF','IGF_AP_NO_DATA_FOUND');
1472 fnd_file.put_line(fnd_file.log,errbuf);
1473
1474 WHEN OTHERS THEN
1475 ROLLBACK;
1476 retcode := 2;
1477 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || SQLERRM;
1478 igs_ge_msg_stack.conc_exception_hndl;
1479
1480 END main_ack;
1481
1482 END igf_gr_rfms_disb_orig;
[Home] [Help]