[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_YTD_LOAD_DATA
Source
1 PACKAGE BODY igf_gr_ytd_load_data AS
2 /* $Header: IGFGR04B.pls 120.2 2006/04/06 06:09:41 veramach ship $ */
3
4 /***************************************************************
5 Created By : avenkatr
6 Date Created By : 2000/12/20
7 Purpose : To upload data into IGF_GR_YTD_ORIG and IGF_GR_YTD_DISB files
8
9 Known Limitations,Enhancements or Remarks
10 Change History : Big Id : 1706091 Wrong error message and token
11 Who When What
12 veramach 29-Jan-2004 Bug 3408092 Added 2004-2005 in g_ver_num checks
13 avenkatr 26-MAR-2001 1. Message token changed
14 2. Substr positions corrected in procedure "split_into_disb_fields"
15 ***************************************************************/
16
17
18 param_error EXCEPTION;
19 invalid_version EXCEPTION; -- Thrown if the award year doesn't matches with that on the flat file.
20
21 g_ver_num VARCHAR2(30) DEFAULT NULL; -- Flat File Version Number
22 g_c_alt_code VARCHAR2(80) DEFAULT NULL; -- To hold alternate code.
23 g_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE;
24 g_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE;
25
26
27 PROCEDURE split_into_orig_fields ( p_record_data IN igf_gr_load_file_t.record_data%TYPE,
28 p_ytd_orig_row OUT NOCOPY igf_gr_ytd_orig%ROWTYPE)
29 AS
30 /***************************************************************
31 Created By : avenkatr
32 Date Created By : 2000/12/20
33 Purpose : To split data in the single record_data column of igf_gr_load_file_t
34 into the different columns of igf_gr_ytd_orig table
35
36 Known Limitations,Enhancements or Remarks
37 Change History :
38 Who When What
39 smvk 11-Feb-2003 Bug # 2758812. Added '2003-2004' in g_ver_num checking.
40 ***************************************************************/
41
42 CURSOR cur_get_orig ( p_orig_id igf_gr_rfms_all.origination_id%TYPE,
43 p_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
44 p_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE)
45 IS
46 SELECT
47 COUNT(origination_id) rec_count
48 FROM
49 igf_gr_rfms
50 WHERE
51 ci_cal_type = p_ci_cal_type AND
52 ci_sequence_number = p_ci_sequence_number AND
53 p_orig_id = origination_id;
54
55 get_orig_rec cur_get_orig%ROWTYPE;
56
57
58
59 BEGIN
60
61
62 IF g_ver_num IN ('2002-2003', '2003-2004','2004-2005') THEN
63
64 BEGIN
65
66 p_ytd_orig_row.origination_id := SUBSTR( p_record_data, 2, 23) ;
67 OPEN cur_get_orig(p_ytd_orig_row.origination_id,
68 g_ci_cal_type,
69 g_ci_sequence_number);
70 FETCH cur_get_orig INTO get_orig_rec;
71 CLOSE cur_get_orig;
72
73 --
74 -- If origination id does not exist in the System, skip this record
75 --
76 IF get_orig_rec.rec_count = 0 THEN
77
78 fnd_message.set_name('IGF','IGF_GR_REC_NOT_FOUND');
79 fnd_message.set_token('ORIG_ID',p_ytd_orig_row.origination_id);
80 fnd_file.put_line(fnd_file.log,fnd_message.get);
81 RAISE igf_gr_gen.skip_this_record;
82
83 ELSE
84
85 p_ytd_orig_row.original_ssn := SUBSTR( p_record_data, 25, 9);
86 p_ytd_orig_row.original_name_cd := SUBSTR( p_record_data, 34, 2);
87 p_ytd_orig_row.attend_pell_id := SUBSTR( p_record_data, 36, 6);
88 p_ytd_orig_row.ed_use := SUBSTR( p_record_data, 42, 5);
89 p_ytd_orig_row.inst_cross_ref_code := SUBSTR( p_record_data, 47, 13);
90 p_ytd_orig_row.action_code := SUBSTR( p_record_data, 60, 1);
91 p_ytd_orig_row.accpt_awd_amt := TO_NUMBER(SUBSTR( p_record_data, 62, 7))/100;
92 p_ytd_orig_row.accpt_disb_dt1 := fnd_date.string_to_date(SUBSTR( p_record_data, 69, 8), 'YYYYMMDD');
93 p_ytd_orig_row.accpt_disb_dt2 := fnd_date.string_to_date(SUBSTR( p_record_data, 77, 8), 'YYYYMMDD');
94 p_ytd_orig_row.accpt_disb_dt3 := fnd_date.string_to_date(SUBSTR( p_record_data, 85, 8), 'YYYYMMDD');
95 p_ytd_orig_row.accpt_disb_dt4 := fnd_date.string_to_date(SUBSTR( p_record_data, 93, 8), 'YYYYMMDD');
96 p_ytd_orig_row.accpt_disb_dt5 := fnd_date.string_to_date(SUBSTR( p_record_data, 101, 8), 'YYYYMMDD');
97 p_ytd_orig_row.accpt_disb_dt6 := fnd_date.string_to_date(SUBSTR( p_record_data, 109, 8), 'YYYYMMDD');
98 p_ytd_orig_row.accpt_disb_dt7 := fnd_date.string_to_date(SUBSTR( p_record_data, 117, 8), 'YYYYMMDD');
99 p_ytd_orig_row.accpt_disb_dt8 := fnd_date.string_to_date(SUBSTR( p_record_data, 125, 8), 'YYYYMMDD');
100 p_ytd_orig_row.accpt_disb_dt9 := fnd_date.string_to_date(SUBSTR( p_record_data, 133, 8), 'YYYYMMDD');
101 p_ytd_orig_row.accpt_disb_dt10 := fnd_date.string_to_date(SUBSTR( p_record_data, 141, 8), 'YYYYMMDD');
102 p_ytd_orig_row.accpt_disb_dt11 := fnd_date.string_to_date(SUBSTR( p_record_data, 149, 8), 'YYYYMMDD');
103 p_ytd_orig_row.accpt_disb_dt12 := fnd_date.string_to_date(SUBSTR( p_record_data, 157, 8), 'YYYYMMDD');
104 p_ytd_orig_row.accpt_disb_dt13 := fnd_date.string_to_date(SUBSTR( p_record_data, 165, 8), 'YYYYMMDD');
105 p_ytd_orig_row.accpt_disb_dt14 := fnd_date.string_to_date(SUBSTR( p_record_data, 173, 8), 'YYYYMMDD');
106 p_ytd_orig_row.accpt_disb_dt15 := fnd_date.string_to_date(SUBSTR( p_record_data, 181, 8), 'YYYYMMDD');
107 p_ytd_orig_row.accpt_enrl_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 189, 8), 'YYYYMMDD');
108 p_ytd_orig_row.accpt_low_tut_flg := SUBSTR( p_record_data, 197, 1);
109 p_ytd_orig_row.accpt_ver_stat_flg := SUBSTR( p_record_data, 198, 1);
110 p_ytd_orig_row.accpt_incr_pell_cd := SUBSTR( p_record_data, 199, 1);
111 p_ytd_orig_row.accpt_tran_num := SUBSTR( p_record_data, 200, 2);
112 p_ytd_orig_row.accpt_efc := SUBSTR( p_record_data, 202, 5);
113 p_ytd_orig_row.accpt_sec_efc := SUBSTR( p_record_data, 207, 1);
114 p_ytd_orig_row.accpt_acad_cal := SUBSTR( p_record_data, 208, 1);
115 p_ytd_orig_row.accpt_pymt_method := SUBSTR( p_record_data, 209, 1);
116 p_ytd_orig_row.accpt_coa := TO_NUMBER(SUBSTR( p_record_data, 210, 7))/100;
117 p_ytd_orig_row.accpt_enrl_stat := SUBSTR( p_record_data, 217, 1);
118 p_ytd_orig_row.accpt_wks_inst_pymt := SUBSTR( p_record_data, 218, 2);
119 p_ytd_orig_row.wk_inst_time_calc_pymt := TO_NUMBER(SUBSTR( p_record_data, 220, 2));
120 p_ytd_orig_row.accpt_wks_acad := SUBSTR( p_record_data, 222, 4);
121 p_ytd_orig_row.accpt_cr_acad_yr := SUBSTR( p_record_data, 226, 4);
122 p_ytd_orig_row.inst_seq_num := SUBSTR( p_record_data, 230, 3);
123 p_ytd_orig_row.sch_full_time_pell := TO_NUMBER(SUBSTR( p_record_data, 252, 5));
124 p_ytd_orig_row.stud_name := SUBSTR( p_record_data, 257, 29);
125 p_ytd_orig_row.ssn := SUBSTR( p_record_data, 286, 9);
126 p_ytd_orig_row.stud_dob := fnd_date.string_to_date(SUBSTR( p_record_data, 295, 8), 'YYYYMMDD');
127 p_ytd_orig_row.cps_ver_sel_cd := fnd_date.string_to_date(SUBSTR( p_record_data, 303, 1), 'YYYYMMDD');
128 p_ytd_orig_row.ytd_disb_amt := TO_NUMBER(SUBSTR( p_record_data, 304, 7))/100;
129 p_ytd_orig_row.batch_id := SUBSTR( p_record_data, 311, 26);
130 p_ytd_orig_row.process_date := fnd_date.string_to_date(SUBSTR( p_record_data, 337, 8), 'YYYYMMDD');
131
132 END IF;
133
134 EXCEPTION
135 WHEN OTHERS THEN -- Number / Date format exception
136 RAISE igf_gr_gen.skip_this_record;
137
138 END;
139
140 ELSIF g_ver_num IN ('2005-2006','2006-2007') THEN
141
142 p_ytd_orig_row.original_ssn := SUBSTR( p_record_data, 25, 9);
143 p_ytd_orig_row.original_name_cd := SUBSTR( p_record_data, 34, 2);
144 p_ytd_orig_row.attend_pell_id := SUBSTR( p_record_data, 36, 6);
145 p_ytd_orig_row.ed_use := SUBSTR( p_record_data, 42, 5);
146 p_ytd_orig_row.inst_cross_ref_code := SUBSTR( p_record_data, 47, 13);
147 p_ytd_orig_row.action_code := SUBSTR( p_record_data, 60, 1);
148 p_ytd_orig_row.accpt_awd_amt := TO_NUMBER(SUBSTR( p_record_data, 62, 7))/100;
149 p_ytd_orig_row.accpt_disb_dt1 := fnd_date.string_to_date(SUBSTR( p_record_data, 69, 8), 'YYYYMMDD');
150 p_ytd_orig_row.accpt_disb_dt2 := fnd_date.string_to_date(SUBSTR( p_record_data, 77, 8), 'YYYYMMDD');
151 p_ytd_orig_row.accpt_disb_dt3 := fnd_date.string_to_date(SUBSTR( p_record_data, 85, 8), 'YYYYMMDD');
152 p_ytd_orig_row.accpt_disb_dt4 := fnd_date.string_to_date(SUBSTR( p_record_data, 93, 8), 'YYYYMMDD');
153 p_ytd_orig_row.accpt_disb_dt5 := fnd_date.string_to_date(SUBSTR( p_record_data, 101, 8), 'YYYYMMDD');
154 p_ytd_orig_row.accpt_disb_dt6 := fnd_date.string_to_date(SUBSTR( p_record_data, 109, 8), 'YYYYMMDD');
155 p_ytd_orig_row.accpt_disb_dt7 := fnd_date.string_to_date(SUBSTR( p_record_data, 117, 8), 'YYYYMMDD');
156 p_ytd_orig_row.accpt_disb_dt8 := fnd_date.string_to_date(SUBSTR( p_record_data, 125, 8), 'YYYYMMDD');
157 p_ytd_orig_row.accpt_disb_dt9 := fnd_date.string_to_date(SUBSTR( p_record_data, 133, 8), 'YYYYMMDD');
158 p_ytd_orig_row.accpt_disb_dt10 := fnd_date.string_to_date(SUBSTR( p_record_data, 141, 8), 'YYYYMMDD');
159 p_ytd_orig_row.accpt_disb_dt11 := fnd_date.string_to_date(SUBSTR( p_record_data, 149, 8), 'YYYYMMDD');
160 p_ytd_orig_row.accpt_disb_dt12 := fnd_date.string_to_date(SUBSTR( p_record_data, 157, 8), 'YYYYMMDD');
161 p_ytd_orig_row.accpt_disb_dt13 := fnd_date.string_to_date(SUBSTR( p_record_data, 165, 8), 'YYYYMMDD');
162 p_ytd_orig_row.accpt_disb_dt14 := fnd_date.string_to_date(SUBSTR( p_record_data, 173, 8), 'YYYYMMDD');
163 p_ytd_orig_row.accpt_disb_dt15 := fnd_date.string_to_date(SUBSTR( p_record_data, 181, 8), 'YYYYMMDD');
164 p_ytd_orig_row.accpt_enrl_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 189, 8), 'YYYYMMDD');
165 p_ytd_orig_row.accpt_low_tut_flg := SUBSTR( p_record_data, 197, 1);
166 p_ytd_orig_row.accpt_ver_stat_flg := SUBSTR( p_record_data, 198, 1);
167 p_ytd_orig_row.accpt_incr_pell_cd := SUBSTR( p_record_data, 199, 1);
168 p_ytd_orig_row.accpt_tran_num := SUBSTR( p_record_data, 200, 2);
169 p_ytd_orig_row.accpt_efc := SUBSTR( p_record_data, 202, 5);
170 p_ytd_orig_row.accpt_sec_efc := NULL;
171 p_ytd_orig_row.accpt_acad_cal := NULL;
172 p_ytd_orig_row.accpt_pymt_method := NULL;
173 p_ytd_orig_row.accpt_coa := TO_NUMBER(SUBSTR( p_record_data, 210, 7))/100;
174 p_ytd_orig_row.accpt_enrl_stat := SUBSTR( p_record_data, 217, 1);
175 p_ytd_orig_row.accpt_wks_inst_pymt := NULL;
176 p_ytd_orig_row.wk_inst_time_calc_pymt := NULL;
177 p_ytd_orig_row.accpt_wks_acad := NULL;
178 p_ytd_orig_row.accpt_cr_acad_yr := NULL;
179 p_ytd_orig_row.inst_seq_num := SUBSTR( p_record_data, 230, 3);
180 p_ytd_orig_row.sch_full_time_pell := TO_NUMBER(SUBSTR( p_record_data, 252, 5));
181 p_ytd_orig_row.stud_name := SUBSTR( p_record_data, 257, 29);
182 p_ytd_orig_row.ssn := SUBSTR( p_record_data, 286, 9);
183 p_ytd_orig_row.stud_dob := fnd_date.string_to_date(SUBSTR( p_record_data, 295, 8), 'YYYYMMDD');
184 p_ytd_orig_row.cps_ver_sel_cd := SUBSTR( p_record_data, 303, 1);
185 p_ytd_orig_row.ytd_disb_amt := TO_NUMBER(SUBSTR( p_record_data, 304, 7))/100;
186 p_ytd_orig_row.batch_id := NULL;
187 p_ytd_orig_row.process_date := fnd_date.string_to_date(SUBSTR( p_record_data, 337, 8), 'YYYYMMDD');
188
189
190 ELSE
191 RAISE igf_gr_gen.no_file_version;
192 END IF;
193
194 EXCEPTION
195
196 WHEN igf_gr_gen.skip_this_record THEN
197 RAISE;
198
199 WHEN igf_gr_gen.no_file_version THEN
200 RAISE;
201
202 WHEN OTHERS THEN
203 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
204 fnd_message.set_token('NAME','igf_gr_ytd_load_data.split_into_orig_fields');
205 igs_ge_msg_stack.add;
206 app_exception.raise_exception;
207
208 END split_into_orig_fields;
209
210
211 PROCEDURE split_into_disb_fields ( p_record_data IN igf_gr_load_file_t.record_data%TYPE,
212 p_ytd_disb_row OUT NOCOPY igf_gr_ytd_disb%ROWTYPE)
213 AS
214 /***************************************************************
215 Created By : avenkatr
216 Date Created By : 2000/12/20
217 Purpose : To split data in the single record_data column of igf_gr_load_file_t
218 into the different columns of igf_gr_ytd_disb table
219
220 Known Limitations,Enhancements or Remarks
221 Change History :
222 Who When What
223 smvk 11-Feb-2003 Bug # 2758812. Added '2003-2004' in g_ver_num checking.
224 ***************************************************************/
225
226 CURSOR cur_get_orig ( p_orig_id igf_gr_rfms_all.origination_id%TYPE,
227 p_ci_cal_type igf_gr_rfms.ci_cal_type%TYPE,
228 p_ci_sequence_number igf_gr_rfms.ci_sequence_number%TYPE)
229 IS
230 SELECT
231 COUNT(origination_id) rec_count
232 FROM
233 igf_gr_rfms
234 WHERE
235 ci_cal_type = p_ci_cal_type AND
236 ci_sequence_number = p_ci_sequence_number AND
237 p_orig_id = origination_id;
238
239 get_orig_rec cur_get_orig%ROWTYPE;
240
241
242 BEGIN
243
244 IF g_ver_num IN ('2002-2003', '2003-2004','2004-2005') THEN
245
246 BEGIN
247 --
248 -- If origination id does not exist in the System, skip this record
249 --
250 p_ytd_disb_row.origination_id := SUBSTR( p_record_data, 2, 23) ;
251
252 OPEN cur_get_orig(p_ytd_disb_row.origination_id,
253 g_ci_cal_type,
254 g_ci_sequence_number);
255 FETCH cur_get_orig INTO get_orig_rec;
256 CLOSE cur_get_orig;
257
258 --
259 -- If origination id does not exist in the System, skip this record
260 --
261 IF get_orig_rec.rec_count = 0 THEN
262
263 fnd_message.set_name('IGF','IGF_GR_REC_NOT_FOUND');
264 fnd_message.set_token('ORIG_ID',p_ytd_disb_row.origination_id);
265 fnd_file.put_line(fnd_file.log,fnd_message.get);
266 RAISE igf_gr_gen.skip_this_record;
267
268 ELSE
269
270 p_ytd_disb_row.inst_cross_ref_code := SUBSTR( p_record_data, 25, 13);
271 p_ytd_disb_row.action_code := SUBSTR( p_record_data, 38, 1);
272 p_ytd_disb_row.disb_ref_num := SUBSTR( p_record_data, 39, 2);
273 p_ytd_disb_row.disb_accpt_amt := TO_NUMBER(SUBSTR( p_record_data, 41, 7))/100;
274 p_ytd_disb_row.db_cr_flag := SUBSTR( p_record_data, 48, 1);
275 p_ytd_disb_row.disb_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 49, 8), 'YYYYMMDD');
276 p_ytd_disb_row.pymt_prd_start_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 94, 8), 'YYYYMMDD');
277 p_ytd_disb_row.disb_batch_id := SUBSTR( p_record_data, 139, 26);
278
279 END IF;
280
281 EXCEPTION
282 WHEN OTHERS THEN -- Number / Date format exception
283 RAISE igf_gr_gen.skip_this_record;
284
285 END;
286
287 ELSIF g_ver_num IN ('2005-2006','2006-2007') THEN
288
289 p_ytd_disb_row.inst_cross_ref_code := SUBSTR( p_record_data, 25, 13);
290 p_ytd_disb_row.action_code := SUBSTR( p_record_data, 38, 1);
291 p_ytd_disb_row.disb_ref_num := SUBSTR( p_record_data, 39, 2);
292 p_ytd_disb_row.disb_accpt_amt := TO_NUMBER(SUBSTR( p_record_data, 41, 7))/100;
293 p_ytd_disb_row.db_cr_flag := SUBSTR( p_record_data, 48, 1);
294 p_ytd_disb_row.disb_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 49, 8), 'YYYYMMDD');
295 p_ytd_disb_row.pymt_prd_start_dt := fnd_date.string_to_date(SUBSTR( p_record_data, 58, 8), 'YYYYMMDD');
296 p_ytd_disb_row.disb_batch_id := SUBSTR( p_record_data, 104, 26);
297 p_ytd_disb_row.disb_process_date := fnd_date.string_to_date(SUBSTR(p_record_data, 130, 8), 'YYYYMMDD');
298 p_ytd_disb_row.routing_id_txt := SUBSTR(p_record_data, 138, 8);
299 p_ytd_disb_row.fin_award_year_num := TO_NUMBER(SUBSTR(p_record_data, 146, 4));
300 p_ytd_disb_row.attend_entity_id_txt := SUBSTR(p_record_data, 150, 6);
301 p_ytd_disb_row.student_name := SUBSTR(p_record_data, 156, 29);
302 p_ytd_disb_row.current_ssn_txt := SUBSTR(p_record_data, 185, 9);
303 p_ytd_disb_row.student_birth_date := fnd_date.string_to_date(SUBSTR(p_record_data, 194, 8), 'YYYYMMDD');
304 p_ytd_disb_row.disb_seq_num := TO_NUMBER(SUBSTR(p_record_data, 202, 2));
305 p_ytd_disb_row.disb_rel_ind := SUBSTR(p_record_data, 204, 1);
306 p_ytd_disb_row.prev_disb_seq_num := TO_NUMBER(SUBSTR(p_record_data, 205, 2));
307
308 ELSE
309 RAISE igf_gr_gen.no_file_version;
310 END IF;
311
312 EXCEPTION
313
314 WHEN igf_gr_gen.skip_this_record THEN
315 RAISE;
316
317 WHEN igf_gr_gen.no_file_version THEN
318 RAISE;
319
320 WHEN OTHERS THEN
321 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
322 fnd_message.set_token('NAME','igf_gr_ytd_load_data.split_into_disb_fields');
323 igs_ge_msg_stack.add;
324 app_exception.raise_exception;
325
326 END split_into_disb_fields ;
327
328
329 PROCEDURE insert_in_ytdor_table ( p_orig_rec IN igf_gr_ytd_orig%ROWTYPE )
330 AS
331 /***************************************************************
332 Created By : avenkatr
333 Date Created By : 2000/12/19
334 Purpose : To Load data into IGF_GR_MRR table
335
336 Known Limitations,Enhancements or Remarks
337 Change History :
338 Who When What
339 ***************************************************************/
340 lv_rowid VARCHAR2(25);
341 lv_ytdor_id NUMBER;
342
343 BEGIN
344
345 igf_gr_ytd_orig_pkg.insert_row (
346 x_rowid => lv_rowid,
347 x_ytdor_id => lv_ytdor_id,
348 x_origination_id => p_orig_rec.origination_id,
349 x_original_ssn => p_orig_rec.original_ssn,
350 x_original_name_cd => p_orig_rec.original_name_cd,
351 x_attend_pell_id => p_orig_rec.attend_pell_id,
352 x_ed_use => P_orig_rec.ed_use,
353 x_inst_cross_ref_code => p_orig_rec.inst_cross_ref_code,
354 x_action_code => p_orig_rec.action_code,
355 x_accpt_awd_amt => p_orig_rec.accpt_awd_amt,
356 x_accpt_disb_dt1 => p_orig_rec.accpt_disb_dt1,
357 x_accpt_disb_dt2 => p_orig_rec.accpt_disb_dt2,
358 x_accpt_disb_dt3 => p_orig_rec.accpt_disb_dt3,
359 x_accpt_disb_dt4 => p_orig_rec.accpt_disb_dt4,
360 x_accpt_disb_dt5 => p_orig_rec.accpt_disb_dt5,
361 x_accpt_disb_dt6 => p_orig_rec.accpt_disb_dt6,
362 x_accpt_disb_dt7 => p_orig_rec.accpt_disb_dt7,
363 x_accpt_disb_dt8 => p_orig_rec.accpt_disb_dt8,
364 x_accpt_disb_dt9 => p_orig_rec.accpt_disb_dt9,
365 x_accpt_disb_dt10 => p_orig_rec.accpt_disb_dt10,
366 x_accpt_disb_dt11 => p_orig_rec.accpt_disb_dt11,
367 x_accpt_disb_dt12 => p_orig_rec.accpt_disb_dt12,
368 x_accpt_disb_dt13 => p_orig_rec.accpt_disb_dt13,
369 x_accpt_disb_dt14 => p_orig_rec.accpt_disb_dt14,
370 x_accpt_disb_dt15 => p_orig_rec.accpt_disb_dt15,
371 x_accpt_enrl_dt => p_orig_rec.accpt_enrl_dt,
372 x_accpt_low_tut_flg => p_orig_rec.accpt_low_tut_flg,
373 x_accpt_ver_stat_flg => p_orig_rec.accpt_ver_stat_flg,
374 x_accpt_incr_pell_cd => p_orig_rec.accpt_incr_pell_cd,
375 x_accpt_tran_num => p_orig_rec.accpt_tran_num,
376 x_accpt_efc => p_orig_rec.accpt_efc,
377 x_accpt_sec_efc => p_orig_rec.accpt_sec_efc,
378 x_accpt_acad_cal => p_orig_rec.accpt_acad_cal,
379 x_accpt_pymt_method => p_orig_rec.accpt_pymt_method,
380 x_accpt_coa => p_orig_rec.accpt_coa,
381 x_accpt_enrl_stat => p_orig_rec.accpt_enrl_stat,
382 x_accpt_wks_inst_pymt => p_orig_rec.accpt_wks_inst_pymt,
383 x_wk_inst_time_calc_pymt => p_orig_rec.wk_inst_time_calc_pymt,
384 x_accpt_wks_acad => p_orig_rec.accpt_wks_acad,
385 x_accpt_cr_acad_yr => p_orig_rec.accpt_cr_acad_yr,
386 x_inst_seq_num => p_orig_rec.inst_seq_num,
387 x_sch_full_time_pell => p_orig_rec.sch_full_time_pell,
388 x_stud_name => p_orig_rec.stud_name,
389 x_ssn => p_orig_rec.ssn ,
390 x_stud_dob => p_orig_rec.stud_dob,
391 x_cps_ver_sel_cd => p_orig_rec.cps_ver_sel_cd,
392 x_ytd_disb_amt => p_orig_rec.ytd_disb_amt,
393 x_batch_id => p_orig_rec.batch_id ,
394 x_process_date => p_orig_rec.process_date,
395 x_mode => 'R',
396 x_ci_cal_type => g_ci_cal_type,
397 x_ci_sequence_number => g_ci_sequence_number
398 );
399
400 EXCEPTION
401
402 WHEN OTHERS THEN
403 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
404 fnd_message.set_token('NAME','igf_gr_ytd_load_data.insert_in_ytdor_table');
405 igs_ge_msg_stack.add;
406 app_exception.raise_exception;
407
408 END insert_in_ytdor_table;
409
410
411 PROCEDURE insert_in_ytdds_table ( p_orig_rec IN igf_gr_ytd_disb%ROWTYPE )
412 AS
413 /***************************************************************
414 Created By : avenkatr
415 Date Created By : 2000/12/22
416 Purpose : To Load data into IGF_GR_YTD_DISB table
417
418 Known Limitations,Enhancements or Remarks
419 Change History :
420 Who When What
421 ***************************************************************/
422 lv_rowid VARCHAR2(25);
423 lv_ytdds_id NUMBER;
424
425 BEGIN
426
427 igf_gr_ytd_disb_pkg.insert_row (
428 x_rowid => lv_rowid,
429 x_ytdds_id => lv_ytdds_id,
430 x_origination_id => p_orig_rec.origination_id,
431 x_inst_cross_ref_code => p_orig_rec.inst_cross_ref_code,
432 x_action_code => p_orig_rec.action_code,
433 x_disb_ref_num => NVL(p_orig_rec.disb_ref_num,0),
434 x_disb_accpt_amt => NVL(p_orig_rec.disb_accpt_amt,0),
435 x_db_cr_flag => p_orig_rec.db_cr_flag,
436 x_disb_dt => p_orig_rec.disb_dt,
437 x_pymt_prd_start_dt => NVL(p_orig_rec.pymt_prd_start_dt,TRUNC(SYSDATE)),
438 x_disb_batch_id => p_orig_rec.disb_batch_id,
439 x_mode => 'R',
440 x_ci_cal_type => g_ci_cal_type,
441 x_ci_sequence_number => g_ci_sequence_number,
442 x_student_name => p_orig_rec.student_name,
443 x_current_ssn_txt => p_orig_rec.current_ssn_txt,
444 x_student_birth_date => p_orig_rec.student_birth_date,
445 x_disb_process_date => p_orig_rec.disb_process_date,
446 x_routing_id_txt => p_orig_rec.routing_id_txt,
447 x_fin_award_year_num => p_orig_rec.fin_award_year_num,
448 x_attend_entity_id_txt => p_orig_rec.attend_entity_id_txt,
449 x_disb_seq_num => p_orig_rec.disb_seq_num,
450 x_disb_rel_ind => p_orig_rec.disb_rel_ind,
451 x_prev_disb_seq_num => p_orig_rec.prev_disb_seq_num
452 ) ;
453
454 EXCEPTION
455
456 WHEN OTHERS THEN
457 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
458 fnd_message.set_token('NAME','igf_gr_ytd_load_data.insert_in_ytdds_table');
459 igs_ge_msg_stack.add;
460 app_exception.raise_exception;
461
462 END insert_in_ytdds_table;
463
464 PROCEDURE load_ack
465 IS
466 /***************************************************************
467 Created By :
468 Date Created By :
469 Purpose :
470
471 Known Limitations,Enhancements or Remarks
472 Change History :
473 Who When What
474 smvk 11-Feb-2003 Bug # 2758812. Added the code to check version mismatch and
475 validate the number of records mentioned in the trailer record.
476 ***************************************************************/
477
478 l_last_gldr_id NUMBER;
479 l_number_rec NUMBER;
480 lp_count NUMBER DEFAULT 0;
481 lf_count NUMBER DEFAULT 0;
482 l_batch_id VARCHAR2(100);
483
484 l_c_message VARCHAR2(30); -- Local variable to hold message
485
486 BEGIN
487
488 igf_gr_gen.process_pell_ack ( g_ver_num,
489 'YTD',
490 l_number_rec,
491 l_last_gldr_id,
492 l_batch_id);
493
494 -- Check the award year matches with the award year in PELL setup.
495 igf_gr_gen.match_file_version (g_ver_num, l_batch_id, l_c_message);
496 IF l_c_message = 'IGF_GR_VRSN_MISMTCH' THEN
497 fnd_message.set_name ('IGF',l_c_message);
498 fnd_message.set_token('CYCL',substr(l_batch_id,3,4));
499 fnd_message.set_token('BATCH',l_batch_id);
500 fnd_message.set_token('AWD_YR',g_c_alt_code);
501 fnd_message.set_token('VRSN',g_ver_num);
502 fnd_file.put_line(fnd_file.log,fnd_message.get);
503 RAISE invalid_version;
504 END IF;
505
506 IF l_number_rec > 0 THEN
507
508 DECLARE
509
510 CURSOR c_ytd_data
511 IS
512 SELECT
513 record_data
514 FROM
515 igf_gr_load_file_t
516 WHERE
517 gldr_id BETWEEN 2 AND (l_last_gldr_id - 1)
518 AND
519 file_type = 'YTD'
520 ORDER BY
521 gldr_id;
522
523 ytd_rec_data c_ytd_data%ROWTYPE;
524 lv_ytdor_row igf_gr_ytd_orig%ROWTYPE;
525 lv_ytdds_row igf_gr_ytd_disb%ROWTYPE;
526
527 BEGIN
528 --
529 -- Check for the type of data in the Flat File
530 --
531 FOR ytd_rec_data IN c_ytd_data LOOP
532
533 IF ( (SUBSTR(ytd_rec_data.record_data, 1, 1)) = 'O' ) THEN
534 --
535 -- This file has Origination records and has to be uploaded in igf_gr_ytd_orig table
536 --
537 BEGIN
538 --
539 -- Split the data in the column of igf_gr_load_file_t into the columns of igf_gr_ytd_orig file
540 --
541 split_into_orig_fields (ytd_rec_data.record_data,
542 lv_ytdor_row);
543
544 --
545 -- Insert this new record into the igf_gr_ytd_orig table
546 --
547 insert_in_ytdor_table (lv_ytdor_row );
548 --
549 -- Make an entry in the log file indicating Success
550 --
551 fnd_message.set_name('IGF','IGF_GR_YTDOR_LOAD_PASS');
552 fnd_message.set_token('ORIGINATION_ID',lv_ytdor_row.origination_id);
553 fnd_file.put_line(fnd_file.log,fnd_message.get());
554
555 lp_count := lp_count + 1;
556
557 EXCEPTION
558 WHEN igf_gr_gen.skip_this_record THEN
559 fnd_message.set_name('IGF','IGF_GR_YTDOR_LOAD_FAIL');
560 fnd_message.set_token('ORIGINATION_ID',SUBSTR( ytd_rec_data.record_data, 2, 23));
561 fnd_file.put_line(fnd_file.log,fnd_message.get());
562 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
563 fnd_file.put_line(fnd_file.log,fnd_message.get);
564 lf_count := lf_count + 1;
565
566 WHEN igf_gr_gen.no_file_version THEN
567 RAISE;
568
569 END;
570
571 ELSIF ( (SUBSTR(ytd_rec_data.record_data, 1, 1)) = 'D' ) THEN
572
573 --
574 -- This file has Disbursement records and has to be updated in the igf_gr_ytd_disb file
575 --
576 BEGIN
577 --
578 -- Split the data in the column of igf_gr_load_file_t into the columns of igf_gr_ytd_orig file
579 --
580 split_into_disb_fields (ytd_rec_data.record_data,
581 lv_ytdds_row) ;
582
583 --
584 -- Insert this new record into the igf_gr_ytd_orig table
585 --
586 insert_in_ytdds_table (lv_ytdds_row ) ;
587 --
588 -- Make an entry in the log file indicating Success
589 --
590 fnd_message.set_name('IGF','IGF_GR_YTDDS_LOAD_PASS');
591 fnd_message.set_token('ORIGINATION_ID',lv_ytdds_row.origination_id);
592 fnd_file.put_line(fnd_file.log,fnd_message.get());
593
594 lp_count := lp_count + 1;
595
596 EXCEPTION
597
598 WHEN igf_gr_gen.skip_this_record THEN
599 --
600 -- Make an entry in the log file indicating Failure
601 --
602 fnd_message.set_name('IGF','IGF_GR_YTDDS_LOAD_FAIL');
603 fnd_message.set_token('ORIGINATION_ID',SUBSTR( ytd_rec_data.record_data, 2, 23));
604 fnd_file.put_line(fnd_file.log,fnd_message.get());
605 lf_count := lf_count + 1;
606 fnd_message.set_name('IGF','IGF_SL_SKIPPING');
607 fnd_file.put_line(fnd_file.log,fnd_message.get);
608
609 WHEN igf_gr_gen.no_file_version THEN
610 RAISE;
611
612 END ;
613 ELSE
614 lf_count := lf_count + 1;
615 END IF;
616
617 END LOOP;
618
619 END;
620
621 END IF;
622
623 IF l_number_rec <> (lp_count + lf_count) THEN
624 fnd_message.set_name('IGF','IGF_GE_RECORD_NUM_NOT_MATCH');
625 fnd_file.put_line(fnd_file.log,fnd_message.get);
626 RAISE igf_gr_gen.file_not_loaded;
627 END IF;
628
629 fnd_message.set_name('IGF','IGF_GR_FILE_REC_CNT');
630 fnd_message.set_token('CNT',l_number_rec);
631 fnd_file.put_line(fnd_file.log,fnd_message.get);
632
633 fnd_message.set_name('IGF','IGF_GR_FILE_REC_PAS');
634 fnd_message.set_token('CNT',lp_count);
635 fnd_file.put_line(fnd_file.log,fnd_message.get);
636
637 fnd_message.set_name('IGF','IGF_GR_FILE_REC_FAL');
638 fnd_message.set_token('CNT',lf_count);
639 fnd_file.put_line(fnd_file.log,fnd_message.get);
640
641 EXCEPTION
642
643 WHEN invalid_version THEN
644 RAISE;
645 WHEN igf_gr_gen.no_file_version THEN
646 RAISE;
647 WHEN igf_gr_gen.corrupt_data_file THEN
648 RAISE;
649 WHEN igf_gr_gen.file_not_loaded THEN
650 RAISE;
651
652 WHEN OTHERS THEN
653 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
654 fnd_message.set_token('NAME','igf_gr_ytd_load_data.load_ack');
655 igs_ge_msg_stack.add;
656 app_exception.raise_exception;
657
658 END load_ack;
659
660
661 --
662 -- MAIN PROCEDURE
663 --
664
665 PROCEDURE ytd_load_file(
666 errbuf OUT NOCOPY VARCHAR2,
667 retcode OUT NOCOPY NUMBER,
668 p_awd_yr IN VARCHAR2,
669 p_org_id IN NUMBER
670 )
671 AS
672 /***************************************************************
673 Created By : avenkatr
674 Date Created By : 2000/12/19
675 Purpose : To Load the IGF_GR_YTD_ORIG and IGF_GR_YTD_DISB tables
676
677 Known Limitations,Enhancements or Remarks
678 Change History :
679 Who When What
680 ***************************************************************/
681
682
683
684 BEGIN
685
686 retcode := 0;
687 igf_aw_gen.set_org_id(p_org_id);
688
689 g_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_awd_yr,1,10)));
690 g_ci_sequence_number := TO_NUMBER(SUBSTR(p_awd_yr,11));
691
692 IF g_ci_cal_type IS NULL OR g_ci_sequence_number IS NULL THEN
693 RAISE param_error;
694 END IF;
695
696 --
697 -- Get the Flat File Version and then Proceed
698 --
699 g_ver_num := igf_aw_gen.get_ver_num(g_ci_cal_type,g_ci_sequence_number,'P');
700 g_c_alt_code := igf_gr_gen.get_alt_code(g_ci_cal_type,g_ci_sequence_number);
701
702 IF g_ver_num ='NULL' THEN
703 RAISE igf_gr_gen.no_file_version;
704 ELSE
705 load_ack;
706 END IF;
707
708 COMMIT;
709
710 EXCEPTION
711
712 WHEN invalid_version THEN
713 ROLLBACK;
714 retcode := 2;
715
716 WHEN param_error THEN
717 ROLLBACK;
718 retcode := 2;
719 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
720 fnd_file.put_line(fnd_file.log,errbuf);
721
722 WHEN igf_gr_gen.corrupt_data_file THEN
723 ROLLBACK;
724 retcode := 2;
725 errbuf := fnd_message.get_string('IGF','IGF_GR_CORRUPT_DATA_FILE');
726 fnd_file.put_line(fnd_file.log,errbuf);
727
728 WHEN igf_gr_gen.file_not_loaded THEN
729 ROLLBACK;
730 retcode := 2;
731 errbuf := fnd_message.get_string('IGF','IGF_GE_FILE_NOT_LOADED');
732 fnd_file.put_line(fnd_file.log,errbuf);
733
734 WHEN igf_gr_gen.no_file_version THEN
735 ROLLBACK;
736 retcode := 2;
737 errbuf := fnd_message.get_string('IGF','IGF_GR_VERSION_NOTFOUND');
738 igs_ge_msg_stack.conc_exception_hndl;
739
740 WHEN others THEN
741
742 ROLLBACK;
743 retcode := 2;
744 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
745 igs_ge_msg_stack.conc_exception_hndl;
746
747 END ytd_load_file;
748
749 END igf_gr_ytd_load_data;