[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_VERIFY_PROCESS
Source
1 PACKAGE BODY igf_gr_verify_process AS
2 /* $Header: IGFGR09B.pls 120.3 2006/02/08 23:48:36 ridas noship $ */
3
4 /***************************************************************
5 Created By : smvk
6 Date Created By : 06-Feb-2003
7 Purpose : To update the Verification status of the person in the person id group p_c_per_grp
8 whose current verification status is p_c_from to p_c_to.
9
10 Known Limitations,Enhancements or Remarks
11 Change History :
12 Who When What
13 | svuppala 14-Oct-2004 Bug # 3416936 |
14 | Modified TBH call to addeded field |
15 | Eligible for Additional Unsubsidized Loans |
16 -- | gvarapra 14-sep-2004 FA138 - ISIR Enhancements |
17 -- | Changed arguments in call to |
18 -- | IGF_AP_FA_BASE_RECORD_PKG. |
19 ***************************************************************/
20
21 param_error EXCEPTION;
22 g_n_indent NUMBER(10) := 0;
23 g_c_indent_space VARCHAR2(100) := NULL;
24
25 --Local PROCEDURES
26 PROCEDURE get_increment(p_c_msg_string IN VARCHAR) AS
27 /***************************************************************
28 Created By : smvk
29 Date Created By : 06-Feb-2003
30 Purpose : To increment the indentation dynamically. used for fomatting the log file.
31 Known Limitations,Enhancements or Remarks
32 Change History :
33 Who When What
34 ***************************************************************/
35 BEGIN
36 IF g_n_indent = 0 THEN
37 g_n_indent := instr(p_c_msg_string,' ');
38 ELSE
39 FOR i in 1.. g_n_indent LOOP
40 g_c_indent_space := g_c_indent_space || ' ';
41 END LOOP;
42 END IF;
43 END get_increment;
44
45 PROCEDURE get_decrement AS
46 /***************************************************************
47 Created By : smvk
48 Date Created By : 06-Feb-2003
49 Purpose : To decrement the indentation dynamically. used for fomatting the log file.
50 Known Limitations,Enhancements or Remarks
51 Change History :
52 Who When What
53 ***************************************************************/
54 BEGIN
55 IF g_n_indent > 0 THEN
56 g_c_indent_space := substr(g_c_indent_space,g_n_indent +1);
57 END IF;
58 END get_decrement;
59
60 PROCEDURE print(p_c_msg_string IN VARCHAR2) IS
61 /***************************************************************
62 Created By : smvk
63 Date Created By : 06-Feb-2003
64 Purpose : Print the string.
65 Known Limitations,Enhancements or Remarks
66 Change History :
67 Who When What
68 ***************************************************************/
69 BEGIN
70 fnd_file.put_line(fnd_file.log,g_c_indent_space ||p_c_msg_string);
71 END print;
72
73 PROCEDURE print_increment(p_c_msg_string IN VARCHAR2) IS
74 /***************************************************************
75 Created By : smvk
76 Date Created By : 06-Feb-2003
77 Purpose : Print the string with proper indentation.
78 Known Limitations,Enhancements or Remarks
79 Change History :
80 Who When What
81 ***************************************************************/
82 BEGIN
83 get_increment(p_c_msg_string);
84 print(p_c_msg_string);
85 END print_increment;
86
87
88 --
89 -- MAIN PROCEDURE
90 --
91
92 PROCEDURE main(
93 errbuf OUT NOCOPY VARCHAR2,
94 retcode OUT NOCOPY NUMBER,
95 p_c_awd_yr IN VARCHAR2,
96 p_n_per_grp_id IN NUMBER,
97 p_c_from IN VARCHAR2,
98 p_c_to IN VARCHAR2
99 ) AS
100 /***************************************************************
101 Created By : smvk
102 Date Created By : 06-Feb-2003
103 Purpose : To update the Verification status of the person in the person id group p_c_per_grp
104 whose current verification status is p_c_from to p_c_to.
105
106 Known Limitations,Enhancements or Remarks
107 Change History :
108 Who When What
109 ridas 08-FEB-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
110 tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
111 rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
112 added new parameter award_fmly_contribution_type to
113 igf_ap_fa_base_rec_pkg.update_row
114 ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
115 added new parameter assoc_org_num to
116 igf_ap_fa_base_rec_pkg.update_row call
117 nsidana 4/28/2003 Bug 2806057 : Log file should be more appropriate.
118
119 ***************************************************************/
120
121 CURSOR c_group_code(cp_n_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE) IS
122 SELECT group_cd
123 FROM igs_pe_all_persid_group_v
124 WHERE group_id = cp_n_grp_id;
125
126 CURSOR c_fa_base_dtls ( cp_c_ci_cal_type IN igf_ap_fa_base_rec.ci_cal_type%TYPE,
127 cp_n_ci_seq_num IN igf_ap_fa_base_rec.ci_sequence_number%TYPE,
128 cp_n_person_id IN igf_ap_fa_base_rec.person_id%TYPE ) IS
129 SELECT *
130 FROM igf_ap_fa_base_rec
131 WHERE ci_cal_type = cp_c_ci_cal_type
132 AND ci_sequence_number = cp_n_ci_seq_num
133 AND person_id = cp_n_person_id ;
134
135 TYPE c_pregrpcurtyp IS REF CURSOR ;
136 cur_per_grp c_pregrpcurtyp;
137 TYPE cpergrptyp IS RECORD ( person_id igf_ap_fa_base_rec_all.person_id%TYPE,
138 person_number igs_pe_person_base_v.person_number%TYPE);
139 per_grp_rec cpergrptyp ;
140
141 l_c_ci_cal_type igf_ap_fa_base_rec.ci_cal_type%TYPE;
142 l_n_ci_sequence_number igf_ap_fa_base_rec.ci_sequence_number%TYPE;
143 l_c_status VARCHAR2(1) := 'S'; /*Defaulted to 'S' and the function will return 'F' in case of failure */
144 l_c_sql_stmt VARCHAR2(32767);
145 rec_grp_cd c_group_code%ROWTYPE;
146 rec_fa_base_dtls c_fa_base_dtls%ROWTYPE;
147 l_c_msg VARCHAR2(2000);
148 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
149
150 BEGIN
151 igf_aw_gen.set_org_id(NULL);
152 retcode := 0;
153 l_c_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_c_awd_yr,1,10)));
154 l_n_ci_sequence_number := TO_NUMBER(SUBSTR(p_c_awd_yr,11));
155
156 IF l_c_ci_cal_type IS NULL OR l_n_ci_sequence_number IS NULL THEN
157 RAISE param_error;
158 END IF;
159
160 fnd_message.set_name('IGF','IGF_AW_PROC_AWD');
161 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(l_c_ci_cal_type,l_n_ci_sequence_number));
162 l_c_msg := fnd_message.get;
163 print_increment(l_c_msg);
164
165 IF p_c_from <> p_c_to THEN
166 --Bug #5021084
167 l_c_sql_stmt := igf_ap_ss_pkg.get_pid(p_n_per_grp_id,l_c_status,lv_group_type);
168
169 --Bug #5021084. Passing Group ID if the group type is STATIC.
170 IF lv_group_type = 'STATIC' THEN
171 OPEN cur_per_grp
172 FOR
173 '
174 SELECT
175 person_id,
176 person_number
177 FROM
178 igs_pe_person_base_v
179 WHERE
180 person_id IN ('||l_c_sql_stmt||') ' USING p_n_per_grp_id;
181 ELSIF lv_group_type = 'DYNAMIC' THEN
182 OPEN cur_per_grp
183 FOR
184 '
185 SELECT
186 person_id,
187 person_number
188 FROM
189 igs_pe_person_base_v
190 WHERE
191 person_id IN ('||l_c_sql_stmt||')
192 ';
193 END IF;
194
195 FETCH cur_per_grp INTO per_grp_rec;
196
197 IF cur_per_grp%NOTFOUND THEN
198 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
199 fnd_file.put_line(fnd_file.log,fnd_message.get);
200 ELSE
201 OPEN c_group_code(p_n_per_grp_id);
202 FETCH c_group_code INTO rec_grp_cd;
203 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
204 fnd_message.set_token('P_PER_GRP',rec_grp_cd.group_cd);
205 l_c_msg := fnd_message.get;
206 print_increment(l_c_msg);
207 CLOSE c_group_code;
208 LOOP
209 fnd_message.set_name ('IGF', 'IGF_AW_PROC_STUD');
210 fnd_message.set_token('STDNT',per_grp_rec.person_number);
211 l_c_msg := fnd_message.get;
212 fnd_file.new_line(fnd_file.log,1);
213 print_increment(l_c_msg);
214
215 OPEN c_fa_base_dtls(l_c_ci_cal_type,l_n_ci_sequence_number, per_grp_rec.person_id);
216 FETCH c_fa_base_dtls INTO rec_fa_base_dtls;
217 IF c_fa_base_dtls%FOUND THEN
218 IF rec_fa_base_dtls.fed_verif_status IS NULL OR
219 rec_fa_base_dtls.fed_verif_status = p_c_from THEN
220 igf_ap_fa_base_rec_pkg.update_row ( x_rowid => rec_fa_base_dtls.row_id ,
221 x_base_id => rec_fa_base_dtls.base_id ,
222 x_ci_cal_type => rec_fa_base_dtls.ci_cal_type ,
223 x_person_id => rec_fa_base_dtls.person_id ,
224 x_ci_sequence_number => rec_fa_base_dtls.ci_sequence_number ,
225 x_org_id => rec_fa_base_dtls.org_id ,
226 x_coa_pending => rec_fa_base_dtls.coa_pending ,
227 x_verification_process_run => rec_fa_base_dtls.verification_process_run ,
228 x_inst_verif_status_date => rec_fa_base_dtls.inst_verif_status_date ,
229 x_manual_verif_flag => rec_fa_base_dtls.manual_verif_flag ,
230 x_fed_verif_status => p_c_to ,
231 x_fed_verif_status_date => TRUNC(SYSDATE) , -- updating the date to system date.
232 x_inst_verif_status => rec_fa_base_dtls.inst_verif_status ,
233 x_nslds_eligible => rec_fa_base_dtls.nslds_eligible ,
234 x_ede_correction_batch_id => rec_fa_base_dtls.ede_correction_batch_id ,
235 x_fa_process_status_date => rec_fa_base_dtls.fa_process_status_date ,
236 x_isir_corr_status => rec_fa_base_dtls.isir_corr_status ,
237 x_isir_corr_status_date => rec_fa_base_dtls.isir_corr_status_date ,
238 x_isir_status => rec_fa_base_dtls.isir_status ,
239 x_isir_status_date => rec_fa_base_dtls.isir_status_date ,
240 x_coa_code_f => rec_fa_base_dtls.coa_code_f ,
241 x_coa_code_i => rec_fa_base_dtls.coa_code_i ,
242 x_coa_f => rec_fa_base_dtls.coa_f ,
243 x_coa_i => rec_fa_base_dtls.coa_i ,
244 x_disbursement_hold => rec_fa_base_dtls.disbursement_hold ,
245 x_fa_process_status => rec_fa_base_dtls.fa_process_status ,
246 x_notification_status => rec_fa_base_dtls.notification_status ,
247 x_notification_status_date => rec_fa_base_dtls.notification_status_date ,
248 x_packaging_hold => rec_fa_base_dtls.packaging_hold ,
249 x_packaging_status => rec_fa_base_dtls.packaging_status ,
250 x_packaging_status_date => rec_fa_base_dtls.packaging_status_date ,
251 x_total_package_accepted => rec_fa_base_dtls.total_package_accepted ,
252 x_total_package_offered => rec_fa_base_dtls.total_package_offered ,
253 x_admstruct_id => rec_fa_base_dtls.admstruct_id ,
254 x_admsegment_1 => rec_fa_base_dtls.admsegment_1 ,
255 x_admsegment_2 => rec_fa_base_dtls.admsegment_2 ,
256 x_admsegment_3 => rec_fa_base_dtls.admsegment_3 ,
257 x_admsegment_4 => rec_fa_base_dtls.admsegment_4 ,
258 x_admsegment_5 => rec_fa_base_dtls.admsegment_5 ,
259 x_admsegment_6 => rec_fa_base_dtls.admsegment_6 ,
260 x_admsegment_7 => rec_fa_base_dtls.admsegment_7 ,
261 x_admsegment_8 => rec_fa_base_dtls.admsegment_8 ,
262 x_admsegment_9 => rec_fa_base_dtls.admsegment_9 ,
263 x_admsegment_10 => rec_fa_base_dtls.admsegment_10 ,
264 x_admsegment_11 => rec_fa_base_dtls.admsegment_11 ,
265 x_admsegment_12 => rec_fa_base_dtls.admsegment_12 ,
266 x_admsegment_13 => rec_fa_base_dtls.admsegment_13 ,
267 x_admsegment_14 => rec_fa_base_dtls.admsegment_14 ,
268 x_admsegment_15 => rec_fa_base_dtls.admsegment_15 ,
269 x_admsegment_16 => rec_fa_base_dtls.admsegment_16 ,
270 x_admsegment_17 => rec_fa_base_dtls.admsegment_17 ,
271 x_admsegment_18 => rec_fa_base_dtls.admsegment_18 ,
272 x_admsegment_19 => rec_fa_base_dtls.admsegment_19 ,
273 x_admsegment_20 => rec_fa_base_dtls.admsegment_20 ,
274 x_packstruct_id => rec_fa_base_dtls.packstruct_id ,
275 x_packsegment_1 => rec_fa_base_dtls.packsegment_1 ,
276 x_packsegment_2 => rec_fa_base_dtls.packsegment_2 ,
277 x_packsegment_3 => rec_fa_base_dtls.packsegment_3 ,
278 x_packsegment_4 => rec_fa_base_dtls.packsegment_4 ,
279 x_packsegment_5 => rec_fa_base_dtls.packsegment_5 ,
280 x_packsegment_6 => rec_fa_base_dtls.packsegment_6 ,
281 x_packsegment_7 => rec_fa_base_dtls.packsegment_7 ,
282 x_packsegment_8 => rec_fa_base_dtls.packsegment_8 ,
283 x_packsegment_9 => rec_fa_base_dtls.packsegment_9 ,
284 x_packsegment_10 => rec_fa_base_dtls.packsegment_10 ,
285 x_packsegment_11 => rec_fa_base_dtls.packsegment_11 ,
286 x_packsegment_12 => rec_fa_base_dtls.packsegment_12 ,
287 x_packsegment_13 => rec_fa_base_dtls.packsegment_13 ,
288 x_packsegment_14 => rec_fa_base_dtls.packsegment_14 ,
289 x_packsegment_15 => rec_fa_base_dtls.packsegment_15 ,
290 x_packsegment_16 => rec_fa_base_dtls.packsegment_16 ,
291 x_packsegment_17 => rec_fa_base_dtls.packsegment_17 ,
292 x_packsegment_18 => rec_fa_base_dtls.packsegment_18 ,
293 x_packsegment_19 => rec_fa_base_dtls.packsegment_19 ,
294 x_packsegment_20 => rec_fa_base_dtls.packsegment_20 ,
295 x_miscstruct_id => rec_fa_base_dtls.miscstruct_id ,
296 x_miscsegment_1 => rec_fa_base_dtls.miscsegment_1 ,
297 x_miscsegment_2 => rec_fa_base_dtls.miscsegment_2 ,
298 x_miscsegment_3 => rec_fa_base_dtls.miscsegment_3 ,
299 x_miscsegment_4 => rec_fa_base_dtls.miscsegment_4 ,
300 x_miscsegment_5 => rec_fa_base_dtls.miscsegment_5 ,
301 x_miscsegment_6 => rec_fa_base_dtls.miscsegment_6 ,
302 x_miscsegment_7 => rec_fa_base_dtls.miscsegment_7 ,
303 x_miscsegment_8 => rec_fa_base_dtls.miscsegment_8 ,
304 x_miscsegment_9 => rec_fa_base_dtls.miscsegment_9 ,
305 x_miscsegment_10 => rec_fa_base_dtls.miscsegment_10 ,
306 x_miscsegment_11 => rec_fa_base_dtls.miscsegment_11 ,
307 x_miscsegment_12 => rec_fa_base_dtls.miscsegment_12 ,
308 x_miscsegment_13 => rec_fa_base_dtls.miscsegment_13 ,
309 x_miscsegment_14 => rec_fa_base_dtls.miscsegment_14 ,
310 x_miscsegment_15 => rec_fa_base_dtls.miscsegment_15 ,
311 x_miscsegment_16 => rec_fa_base_dtls.miscsegment_16 ,
312 x_miscsegment_17 => rec_fa_base_dtls.miscsegment_17 ,
313 x_miscsegment_18 => rec_fa_base_dtls.miscsegment_18 ,
314 x_miscsegment_19 => rec_fa_base_dtls.miscsegment_19 ,
315 x_miscsegment_20 => rec_fa_base_dtls.miscsegment_20 ,
316 x_prof_judgement_flg => rec_fa_base_dtls.prof_judgement_flg ,
317 x_nslds_data_override_flg => rec_fa_base_dtls.nslds_data_override_flg ,
318 x_target_group => rec_fa_base_dtls.target_group ,
319 x_coa_fixed => rec_fa_base_dtls.coa_fixed ,
320 x_coa_pell => rec_fa_base_dtls.coa_pell ,
321 x_mode => 'R' ,
322 x_profile_status => rec_fa_base_dtls.profile_status ,
323 x_profile_status_date => rec_fa_base_dtls.profile_status_date ,
324 x_profile_fc => rec_fa_base_dtls.profile_fc ,
325 x_tolerance_amount => rec_fa_base_dtls.tolerance_amount ,
326 x_manual_disb_hold => rec_fa_base_dtls.manual_disb_hold ,
327 x_pell_alt_expense => rec_fa_base_dtls.pell_alt_expense ,
328 x_assoc_org_num => rec_fa_base_dtls.assoc_org_num ,
329 x_award_fmly_contribution_type => rec_fa_base_dtls.award_fmly_contribution_type,
330 x_isir_locked_by => rec_fa_base_dtls.isir_locked_by,
331 x_adnl_unsub_loan_elig_flag => rec_fa_base_dtls.adnl_unsub_loan_elig_flag,
332 x_lock_awd_flag => rec_fa_base_dtls.lock_awd_flag,
333 x_lock_coa_flag => rec_fa_base_dtls.lock_coa_flag
334
335 );
336 -- Bug 2806057 : Log file message should be more appropriate.
337 -- nsidana 4/28/2003
338
339 -- If the vale of the FED_VERIF_STATUS is NULL in the FA BASE REC, log the message having no FROM token.
340
341 IF (rec_fa_base_dtls.fed_verif_status IS NULL) THEN
342 fnd_message.set_name('IGF','IGF_GR_VERI_COM_FROM_BLANK');
343 fnd_message.set_token('PERSON_NUMBER',per_grp_rec.person_number);
344 fnd_message.set_token('P_TO',igf_aw_gen.LOOKUP_DESC('IGF_FED_VERIFY_STATUS',p_c_to));
345
346 ELSIF (rec_fa_base_dtls.fed_verif_status = p_c_from) THEN
347 -- Log the message with the two tokens, as being done before.
348 fnd_message.set_name('IGF','IGF_GR_VERI_COMPLETE');
349 fnd_message.set_token('PERSON_NUMBER',per_grp_rec.person_number);
350 fnd_message.set_token('P_FROM',igf_aw_gen.LOOKUP_DESC('IGF_FED_VERIFY_STATUS',p_c_from));
351 fnd_message.set_token('P_TO',igf_aw_gen.LOOKUP_DESC('IGF_FED_VERIFY_STATUS',p_c_to));
352 END IF;
353
354 l_c_msg := fnd_message.get;
355 print_increment(l_c_msg);
356 ELSE
357 fnd_message.set_name('IGF','IGF_GR_VERI_NO_UPDT');
358 fnd_message.set_token('P_FROM',igf_aw_gen.LOOKUP_DESC('IGF_FED_VERIFY_STATUS',p_c_from));
359 l_c_msg := fnd_message.get;
360 print_increment(l_c_msg);
361 END IF;
362 ELSE
363 fnd_message.set_name('IGF','IGF_DB_NO_FA_PER');
364 fnd_message.set_token('PER_NUM',per_grp_rec.person_number);
365 l_c_msg := fnd_message.get;
366 print_increment(l_c_msg);
367 END IF;
368 CLOSE c_fa_base_dtls;
369 FETCH cur_per_grp INTO per_grp_rec;
370 EXIT WHEN cur_per_grp%NOTFOUND;
371 get_decrement;get_decrement;
372 END LOOP;
373 END IF;
374 CLOSE cur_per_grp;
375 ELSE
376 RAISE param_error;
377 END IF;
378
379 COMMIT;
380
381 EXCEPTION
382
383 WHEN param_error THEN
384 ROLLBACK;
385 retcode := 2;
386 errbuf := fnd_message.get_string('IGF','IGF_AW_PARAM_ERR');
387 fnd_file.put_line(fnd_file.log,errbuf);
388
389 WHEN others THEN
390 ROLLBACK;
391 retcode := 2;
392 fnd_file.put_line(fnd_file.log,sqlerrm);
393 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
394 igs_ge_msg_stack.conc_exception_hndl;
395
396 END main;
397
398 END igf_gr_verify_process;