[Home] [Help]
PACKAGE BODY: APPS.IGF_GR_GEN_XML
Source
1 PACKAGE BODY IGF_GR_GEN_XML AS
2 /* $Header: IGFGR12B.pls 120.13 2006/04/24 00:22:15 rajagupt noship $ */
3 /*************************************************************
4 Process Flow
5 main()
6 1. log_input_parameters()
7 2. validate_input_parameters()
8 3. Process RFMS records based on input criteria.
9 4. process_rfms_record()
10 5. General validations
11 6. XML Validations
12 7. insert_into_cod_tables() (IGF_GR_COD_DTLS and IGF_AW_DB_COD_DTLS)
13 8. submit_xml_event()
14 9. XML Gateway Standard to create xml
15 10. store_xml()
16 11. This will insert xml file into IGF_SL_COD_DOC_DTLS
17 12. Launch print xml sub process IGFGRJ14
18 13. Log input parameters
19 14. igf_sl_dl_gen_xml.edit_clob()
20 Edit the clob file and update it in IGF_SL_COD_DOC_DTLS table.
21 15. igf_sl_dl_gen_xml.print_out_xml()
22 Print XML file in out file.
23 /*************************************************************/
24
25 /*************************************************************
26 Created By : ugummall
27 Date Created On : 2004/10/04
28 Purpose :
29 Know limitations, enhancements or remarks
30 Change History
31 Who When What
32 (reverse chronological order - newest change first)
33 ***************************************************************/
34
35 gv_document_id_txt VARCHAR2(30);
36 g_ver_num VARCHAR2(30);
37 g_full_resp_code VARCHAR2(30);
38 student_dtl_rec igf_sl_gen.person_dtl_rec;
39
40 FUNCTION is_alpha_numeric(p_param_value IN VARCHAR2)
41 RETURN BOOLEAN AS
42 /*************************************************************
43 Created By : bvisvana
44 Date Created On : 2005/10/11
45 Purpose : To check for Alpha numeric - Related to Bug # 4124839
46 Know limitations, enhancements or remarks
47 Change History
48 Who When What
49 (reverse chronological order - newest change first)
50 ***************************************************************/
51 l_num NUMBER;
52 BEGIN
53 l_num := TO_NUMBER(TRIM(p_param_value));
54 RETURN FALSE;
55 EXCEPTION
56 WHEN VALUE_ERROR THEN
57 RETURN TRUE;
58 WHEN OTHERS THEN
59 RETURN TRUE;
60 END is_alpha_numeric;
61
62
63 PROCEDURE set_nls_fmt(PARAM in VARCHAR2)
64 AS
65 /*************************************************************
66 Created By : ugummall
67 Date Created On : 2004/10/04
68 Purpose :
69 Know limitations, enhancements or remarks
70 Change History
71 Who When What
72 (reverse chronological order - newest change first)
73 ***************************************************************/
74 l_temp VARCHAR2(10);
75 l_sql_stmt VARCHAR2(100);
76 BEGIN
77 l_temp := '.,';
78 l_sql_stmt := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''' || l_temp || '''';
79 EXECute IMMEDIATE l_sql_stmt;
80 END set_nls_fmt;
81
82 FUNCTION get_grp_name ( p_per_grp_id IN NUMBER)
83 RETURN VARCHAR2
84 /*************************************************************
85 Created By : ugummall
86 Date Created On : 2004/10/04
87 Purpose :
88 Know limitations, enhancements or remarks
89 Change History
90 Who When What
91 (reverse chronological order - newest change first)
92 ***************************************************************/
93 AS
94
95 CURSOR cur_get_grp_name ( p_per_grp_id NUMBER) IS
96 SELECT group_cd
97 FROM IGS_PE_PERSID_GROUP_ALL
98 WHERE group_id = p_per_grp_id;
99 get_grp_name_rec cur_get_grp_name%ROWTYPE;
100
101 BEGIN
102 OPEN cur_get_grp_name (p_per_grp_id);
103 FETCH cur_get_grp_name INTO get_grp_name_rec;
104 CLOSE cur_get_grp_name;
105
106 RETURN get_grp_name_rec.group_cd;
107 EXCEPTION
108 WHEN OTHERS THEN
109 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
110 fnd_message.set_token('NAME','IGF_GR_GEN_XML.GET_GRP_NAME');
111 igs_ge_msg_stack.add;
112 app_exception.raise_exception;
113 END get_grp_name;
114
115 FUNCTION check_fa_rec ( p_base_id NUMBER,
116 p_cal_type VARCHAR2,
117 p_seq_number NUMBER)
118 RETURN BOOLEAN
119 AS
120 /*************************************************************
121 Created By : ugummall
122 Date Created On : 2004/10/04
123 Purpose :
124 Know limitations, enhancements or remarks
125 Change History
126 Who When What
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 CURSOR cur_chk_fa ( cp_base_id NUMBER,
131 cp_cal_type VARCHAR2,
132 cp_seq_number NUMBER) IS
133 SELECT base_id
134 FROM IGF_AP_FA_BASE_REC_ALL
135 WHERE base_id = cp_base_id
136 AND ci_cal_type = cp_cal_type
137 AND ci_sequence_number = cp_seq_number;
138 chk_fa_rec cur_chk_fa%ROWTYPE;
139
140 BEGIN
141 OPEN cur_chk_fa (p_base_id,p_cal_type,p_seq_number);
142 FETCH cur_chk_fa INTO chk_fa_rec;
143 CLOSE cur_chk_fa;
144 IF chk_fa_rec.base_id IS NULL THEN
145 RETURN FALSE;
146 ELSE
147 RETURN TRUE;
148 END IF;
149 EXCEPTION
150 WHEN OTHERS THEN
151 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
152 fnd_message.set_token('NAME','IGF_GR_GEN_XML.CHECK_FA_REC');
153 igs_ge_msg_stack.add;
154 app_exception.raise_exception;
155 END check_fa_rec;
156
157 FUNCTION per_in_fa ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE,
158 p_ci_cal_type VARCHAR2,
159 p_ci_sequence_number NUMBER,
160 p_base_id OUT NOCOPY NUMBER
161 )
162 RETURN VARCHAR2
163 AS
164 /*************************************************************
165 Created By : ugummall
166 Date Created On : 2004/10/04
167 Purpose :
168 Know limitations, enhancements or remarks
169 Change History
170 Who When What
171 (reverse chronological order - newest change first)
172 ***************************************************************/
173
174 CURSOR cur_get_pers_num ( p_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
175 SELECT person_number
176 FROM IGS_PE_PERSON_BASE_V
177 WHERE person_id = p_person_id;
178 get_pers_num_rec cur_get_pers_num%ROWTYPE;
179
180 CURSOR cur_get_base ( p_cal_type igs_ca_inst_all.cal_type%TYPE,
181 p_sequence_number igs_ca_inst_all.sequence_number%TYPE,
182 p_person_id igf_ap_fa_base_rec_all.person_id%TYPE) IS
183 SELECT base_id
184 FROM IGF_AP_FA_BASE_REC_ALL
185 WHERE person_id = p_person_id
186 AND ci_cal_type = p_cal_type
187 AND ci_sequence_number = p_sequence_number;
188
189 BEGIN
190 OPEN cur_get_pers_num(p_person_id);
191 FETCH cur_get_pers_num INTO get_pers_num_rec;
192
193 IF cur_get_pers_num%NOTFOUND THEN
194 CLOSE cur_get_pers_num;
195 RETURN NULL;
196 ELSE
197 CLOSE cur_get_pers_num;
198
199 OPEN cur_get_base(p_ci_cal_type,p_ci_sequence_number,p_person_id);
200 FETCH cur_get_base INTO p_base_id;
201 CLOSE cur_get_base;
202
203 RETURN get_pers_num_rec.person_number;
204 END IF;
205
206 EXCEPTION
207 WHEN OTHERS THEN
208 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
209 fnd_message.set_token('NAME','IGF_GR_GEN_XML.PER_IN_FA');
210 igs_ge_msg_stack.add;
211 app_exception.raise_exception;
212 END per_in_fa;
213
214 PROCEDURE log_input_parameters (
215 p_award_year IN VARCHAR2,
216 p_source_entity_id IN VARCHAR2,
217 p_report_entity_id IN VARCHAR2,
218 p_attend_entity_id IN VARCHAR2,
219 p_base_id IN NUMBER,
220 p_persid_grp IN NUMBER
221 )
222 AS
223 /*************************************************************
224 Created By : ugummall
225 Date Created On : 2004/10/04
226 Purpose :
227 Know limitations, enhancements or remarks
228 Change History
229 Who When What
230 (reverse chronological order - newest change first)
231 ***************************************************************/
232
233 -- To get group description for group_id
234 CURSOR cur_person_group(cp_persid_grp igs_pe_persid_group_all.group_id%TYPE) IS
235 SELECT group_cd group_name
236 FROM igs_pe_persid_group_all
237 WHERE group_id = cp_persid_grp;
238 rec_person_group cur_person_group%ROWTYPE;
239
240 l_ci_cal_type VARCHAR2(11);
241 l_ci_sequence_number NUMBER(5);
242 l_msg_str_1 VARCHAR2(2000);
243 l_msg_str_2 VARCHAR2(2000);
244 l_msg_str_3 VARCHAR2(2000);
245 l_msg_str_4 VARCHAR2(2000);
246 l_msg_str_5 VARCHAR2(2000);
247 l_msg_str_6 VARCHAR2(2000);
248
249 BEGIN
250
251 -- Get cal type and sequence number from award year
252 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
253 l_ci_sequence_number := NVL(TO_NUMBER(SUBSTR(p_award_year,11)),0);
254
255 -- show award year
256 l_msg_str_1 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','AWARD_YEAR'),30) ||
257 RPAD(igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number),20);
258 fnd_file.put_line(fnd_file.log,l_msg_str_1);
259
260 -- show source entity id
261 l_msg_str_2 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'SOURCE_ENTITY_ID'),30) || p_source_entity_id;
262 fnd_file.put_line(fnd_file.log,l_msg_str_2);
263
264 -- show reporting entity
265 IF (p_report_entity_id IS NOT NULL) THEN
266 l_msg_str_3 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'REPORT_ENTITY_ID'),30) || p_report_entity_id;
267 fnd_file.put_line(fnd_file.log,l_msg_str_3);
268 END IF;
269
270 -- show attending entity
271 IF (p_attend_entity_id IS NOT NULL) THEN
272 l_msg_str_4 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'ATTEND_ENTITY_ID'),30) || p_attend_entity_id;
273 fnd_file.put_line(fnd_file.log,l_msg_str_4);
274 END IF;
275
276 -- show person number
277 IF (p_base_id IS NOT NULL) THEN
278 l_msg_str_5 := RPAD(igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','BASE_ID'),30) ||
279 RPAD(igf_gr_gen.get_per_num(p_base_id),20);
280 fnd_file.put_line(fnd_file.log,l_msg_str_5);
281 END IF;
282
283 -- show persond id group
284 IF (p_persid_grp IS NOT NULL) THEN
285 OPEN cur_person_group(p_persid_grp);
286 FETCH cur_person_group INTO rec_person_group;
287 CLOSE cur_person_group;
288 l_msg_str_6 := RPAD(igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS', 'PERSON_ID_GROUP'),30) || rec_person_group.group_name;
289 fnd_file.put_line(fnd_file.log,l_msg_str_6);
290 END IF;
291
292 EXCEPTION
293 WHEN OTHERS THEN
294 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
295 fnd_message.set_token('NAME','IGF_GR_GEN_XML.LOG_INPUT_PARAMETERS');
296 igs_ge_msg_stack.add;
297 app_exception.raise_exception;
298 END log_input_parameters;
299
300 FUNCTION validate_input_parameters (
301 p_award_year IN VARCHAR2,
302 p_source_entity_id IN VARCHAR2,
303 p_report_entity_id IN VARCHAR2,
304 p_attend_entity_id IN VARCHAR2,
305 p_base_id IN NUMBER,
306 p_persid_grp IN NUMBER
307 )
308 RETURN BOOLEAN
309 AS
310 /*************************************************************
311 Created By : ugummall
312 Date Created On : 2004/10/04
313 Purpose :
314 Know limitations, enhancements or remarks
315 Change History
316 Who When What
317 bvisvana 09-Aug-2005 Bug # 4124839 - Included the exception VALUE_ERROR
318 bvisvana 11-Oct-2005 Bug # 4124839 - Removed the exception VALUE_ERROR and handle it by adding a new function
319 is_alpha_numeric
320 (reverse chronological order - newest change first)
321 ***************************************************************/
322
323 -- To get award year details from System award year mappings table.
324 CURSOR cur_awd_year ( cp_cal_type igs_ca_inst.cal_type%TYPE,
325 cp_sequence_number igs_ca_inst.sequence_number%TYPE) IS
326 SELECT bam.award_year_status_code,
327 bam.pell_participant_code,
328 bam.sys_award_year
329 FROM IGF_AP_BATCH_AW_MAP bam
330 WHERE bam.ci_cal_type = cp_cal_type
331 AND bam.ci_sequence_number = cp_sequence_number;
332 rec_awd_year cur_awd_year%ROWTYPE;
333
334 -- To get source entity id from Pell ID/Entity ID relationships.
335 CURSOR cur_source_entity ( cp_cal_type igs_ca_inst.cal_type%TYPE,
336 cp_sequence_number igs_ca_inst.sequence_number%TYPE,
337 cp_source_entity_id igf_gr_report_pell.rep_entity_id_txt%TYPE) IS
338 SELECT 'x'
339 FROM IGF_GR_REPORT_PELL rep
340 WHERE rep.ci_cal_type = cp_cal_type
341 AND rep.ci_sequence_number = cp_sequence_number
342 AND rep.rep_entity_id_txt = cp_source_entity_id;
343
344 -- To check pell origination records exists or not with given report entity id.
345 CURSOR cur_rep_entity ( cp_cal_type igs_ca_inst.cal_type%TYPE,
346 cp_sequence_number igs_ca_inst.sequence_number%TYPE,
347 cp_report_entity_id igf_gr_rfms.rep_entity_id_txt%TYPE ) IS
348 SELECT rfms.origination_id
349 FROM IGF_GR_RFMS rfms
350 WHERE rfms.ci_cal_type = cp_cal_type
351 AND rfms.ci_sequence_number = cp_sequence_number
352 AND rfms.rep_entity_id_txt = cp_report_entity_id;
353
354 -- To check pell origination records exists or not with given report and attend entity ids.
355 CURSOR cur_attd_entity ( cp_cal_type igs_ca_inst.cal_type%TYPE,
356 cp_sequence_number igs_ca_inst.sequence_number%TYPE,
357 cp_report_entity_id igf_gr_rfms.rep_entity_id_txt%TYPE,
358 cp_attend_entity_id igf_gr_rfms.atd_entity_id_txt%TYPE ) IS
359 SELECT rfms.origination_id
360 FROM IGF_GR_RFMS rfms
361 WHERE rfms.ci_cal_type = cp_cal_type
362 AND rfms.ci_sequence_number = cp_sequence_number
363 AND rfms.rep_entity_id_txt = cp_report_entity_id
364 AND rfms.atd_entity_id_txt = cp_attend_entity_id;
365
366 -- To check fa base record exists or not.
367 CURSOR cur_fa_base ( cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
368 SELECT fabase.base_id
369 FROM IGF_AP_FA_BASE_REC_ALL fabase
370 WHERE fabase.base_id = cp_base_id;
371
372 -- To check pell origination records exists or not with given person.
373 CURSOR cur_person_number ( cp_cal_type igs_ca_inst.cal_type%TYPE,
374 cp_sequence_number igs_ca_inst.sequence_number%TYPE,
375 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE ) IS
376 SELECT 'x'
377 FROM IGF_GR_RFMS rfms
378 WHERE rfms.ci_cal_type = cp_cal_type
379 AND rfms.ci_sequence_number = cp_sequence_number
380 AND rfms.base_id = cp_base_id;
381
382 -- To check fa base record exists or not.
383 CURSOR cur_person_group ( cp_persid_grp igs_pe_persid_group_all.group_id%TYPE) IS
384 SELECT 'x'
385 FROM IGS_PE_PERSID_GROUP_ALL pers
386 WHERE pers.group_id = cp_persid_grp
387 AND pers.closed_ind = 'N';
388
389 l_params_status BOOLEAN;
390 l_ci_cal_type VARCHAR2(11);
391 l_ci_sequence_number NUMBER;
392 lv_dummy VARCHAR2(40);
393 ln_dummy NUMBER(15);
394
395 BEGIN
396
397 l_params_status := TRUE;
398
399 -- Get cal type and sequence number from award year
400 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
401 l_ci_sequence_number := NVL(TO_NUMBER(SUBSTR(p_award_year,11)),0);
402
403 -- Award year is mandatory parameter
404 IF p_award_year IS NULL OR l_ci_cal_type IS NULL OR l_ci_sequence_number IS NULL THEN
405 l_params_status := FALSE;
406 fnd_message.set_name('IGF', 'IGF_SL_COD_REQ_PARAM');
407 fnd_message.set_token('PARAM', igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','AWARD_YEAR'));
408 fnd_file.put_line(fnd_file.log, fnd_message.get);
409 fnd_file.new_line(fnd_file.log, 1);
410 END IF;
411
412 -- Source entity id is mandatory parameter
413 IF TRIM(p_source_entity_id) IS NULL THEN
414 l_params_status := FALSE;
415 fnd_message.set_name('IGF', 'IGF_SL_COD_REQ_PARAM');
416 fnd_message.set_token('PARAM', igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'SOURCE_ENTITY_ID'));
417 fnd_file.put_line(fnd_file.log, fnd_message.get);
418 fnd_file.new_line(fnd_file.log, 1);
419 ELSIF is_alpha_numeric(p_source_entity_id) OR LENGTH(TRIM(p_source_entity_id)) > 8 THEN
420 -- bvisvana BUg # 4124839
421 l_params_status := FALSE;
422 fnd_message.set_name('IGF','IGF_SL_COD_INVL_SOURCE_ID');
423 fnd_file.put_line(fnd_file.log, fnd_message.get);
424 fnd_file.new_line(fnd_file.log, 1);
425 END IF;
426
427 -- validating person number and person group are mutually exclusive or not.
428 IF ( (p_base_id IS NOT NULL) AND (p_persid_grp IS NOT NULL) ) THEN
429 l_params_status := FALSE;
430 fnd_message.set_name('IGF', 'IGF_SL_COD_INV_PARAM');
431 fnd_message.set_token('PARAM1', igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS', 'BASE_ID'));
432 fnd_message.set_token('PARAM2', igf_aw_gen.lookup_desc('IGF_GE_PARAMETERS', 'PERSON_ID_GROUP'));
433 fnd_file.put_line(fnd_file.log, fnd_message.get);
434 fnd_file.new_line(fnd_file.log, 1);
435 END IF;
436
437 -- Validating attributes for COD support.
438 IF p_award_year IS NOT NULL THEN
439 OPEN cur_awd_year(l_ci_cal_type, l_ci_sequence_number);
440 FETCH cur_awd_year INTO rec_awd_year;
441 IF cur_awd_year%NOTFOUND
442 OR rec_awd_year.award_year_status_code IS NULL
443 OR rec_awd_year.pell_participant_code IS NULL
444 OR rec_awd_year.sys_award_year IS NULL
445 THEN
446 -- ie award year is not setup at System Award Year Mappings Form.
447 l_params_status := FALSE;
448 fnd_message.set_name('IGF', 'IGF_SL_COD_INV_AWD_YR');
449 fnd_file.put_line(fnd_file.log, fnd_message.get);
450 fnd_file.new_line(fnd_file.log, 1);
451 ELSE
452 -- check award year is Open award year or not.
453 IF (rec_awd_year.award_year_status_code <> 'O') THEN
454 l_params_status := FALSE;
455 fnd_message.set_name('IGF', 'IGF_GR_COD_AWDYR_OPEN');
456 fnd_file.put_line(fnd_file.log, fnd_message.get);
457 fnd_file.new_line(fnd_file.log, 1);
458 END IF;
459
460 -- check pell participation is Full or not.
461 IF (rec_awd_year.pell_participant_code <> 'FULL_PARTICIPANT') THEN
462 l_params_status := FALSE;
463 fnd_message.set_name('IGF', 'IGF_GR_COD_AWDYR_FULL');
464 fnd_file.put_line(fnd_file.log, fnd_message.get);
465 fnd_file.new_line(fnd_file.log, 1);
466 END IF;
467
468 -- check wether the award year is before 04-05.
469 IF (rec_awd_year.sys_award_year < '0405') THEN
470 l_params_status := FALSE;
471 fnd_message.set_name('IGF', 'IGF_SL_COD_XML_SUPPORT');
472 fnd_file.put_line(fnd_file.log, fnd_message.get);
473 fnd_file.new_line(fnd_file.log, 1);
474 END IF;
475 END IF;
476 CLOSE cur_awd_year;
477 END IF;
478
479 -- Validating source entity id.
480 IF (p_source_entity_id IS NOT NULL) THEN
481 OPEN cur_source_entity(l_ci_cal_type, l_ci_sequence_number, p_source_entity_id);
482 FETCH cur_source_entity INTO lv_dummy;
483 IF cur_source_entity%NOTFOUND THEN
484 -- Not a valid source entity id.
485 l_params_status := FALSE;
486 fnd_message.set_name('IGF', 'IGF_SL_COD_INV_SRC_ID');
487 fnd_file.put_line(fnd_file.log, fnd_message.get);
488 fnd_file.new_line(fnd_file.log, 1);
489 END IF;
490 CLOSE cur_source_entity;
491 END IF;
492
493 -- Validate Reporting Entity ID. Records with report entity id exists or not.
494 IF (p_report_entity_id IS NOT NULL) THEN
495 OPEN cur_rep_entity(l_ci_cal_type, l_ci_sequence_number, p_report_entity_id);
496 FETCH cur_rep_entity INTO lv_dummy;
497 IF cur_rep_entity%NOTFOUND THEN
498 -- No origination records to process.
499 l_params_status := FALSE;
500 fnd_message.set_name('IGF', 'IGF_GR_COD_INV_REP_ID');
501 fnd_message.set_token('REPORTING_ID', p_report_entity_id);
502 fnd_file.put_line(fnd_file.log, fnd_message.get);
503 fnd_file.new_line(fnd_file.log, 1);
504 END IF;
505 CLOSE cur_rep_entity;
506 END IF;
507
508 -- Validate attending entity id. Is it present without reporting entity id.
509 IF (p_attend_entity_id IS NOT NULL AND p_report_entity_id IS NULL) THEN
510 l_params_status := FALSE;
511 fnd_message.set_name('IGF', 'IGF_SL_COD_INV_ATD_PARAM');
512 fnd_file.put_line(fnd_file.log, fnd_message.get);
513 fnd_file.new_line(fnd_file.log, 1);
514 END IF;
515
516 -- Validate Attending Entity ID. Records with report and attend entity id exists or not.
517 IF (p_attend_entity_id IS NOT NULL AND p_report_entity_id IS NOT NULL) THEN
518 OPEN cur_attd_entity(l_ci_cal_type, l_ci_sequence_number, p_report_entity_id, p_attend_entity_id);
519 FETCH cur_attd_entity INTO lv_dummy;
520 IF cur_attd_entity%NOTFOUND THEN
521 -- No origination records to process.
522 l_params_status := FALSE;
523 fnd_message.set_name('IGF', 'IGF_GR_COD_INV_ATD_ID');
524 fnd_message.set_token('REPORTING_ID', p_report_entity_id);
525 fnd_message.set_token('ATTENDING_ID', p_attend_entity_id);
526 fnd_file.put_line(fnd_file.log, fnd_message.get);
527 fnd_file.new_line(fnd_file.log, 1);
528 END IF;
529 CLOSE cur_attd_entity;
530 END IF;
531
532 -- Validate Person Number.
533 IF (p_base_id IS NOT NULL) THEN
534 -- check fa base record exists or not.
535 IF NOT check_fa_rec(p_base_id, l_ci_cal_type, l_ci_sequence_number) THEN
536 l_params_status := FALSE;
537 fnd_message.set_name('IGF', 'IGF_SP_NO_FA_BASE_REC');
538 fnd_file.put_line(fnd_file.log, fnd_message.get);
539 fnd_file.new_line(fnd_file.log, 1);
540 ELSE
541 -- check wether this person has any pell origination records or not.
542 OPEN cur_person_number(l_ci_cal_type, l_ci_sequence_number, p_base_id);
543 FETCH cur_person_number INTO lv_dummy;
544 IF cur_person_number%NOTFOUND THEN
545 -- no pell origination records for this person.
546 l_params_status := FALSE;
547 fnd_message.set_name('IGF', 'IGF_GR_COD_NO_ORIG_REC');
548 fnd_message.set_token('PERSON_NUMBER', igf_gr_gen.get_per_num(p_base_id));
549 fnd_file.put_line(fnd_file.log, fnd_message.get);
550 fnd_file.new_line(fnd_file.log, 1);
551 END IF;
552 CLOSE cur_person_number;
553 END IF;
554 END IF;
555
556 -- Validate Person Id group.
557 IF (p_persid_grp IS NOT NULL) THEN
558 OPEN cur_person_group(p_persid_grp);
559 FETCH cur_person_group INTO lv_dummy;
560 IF cur_person_group%NOTFOUND THEN
561 -- Invalid person id group.
562 l_params_status := FALSE;
563 fnd_message.set_name('IGF', 'IGF_SL_COD_PERSID_GRP_INV');
564 fnd_file.put_line(fnd_file.log, fnd_message.get);
565 fnd_file.new_line(fnd_file.log, 1);
566 END IF;
567 CLOSE cur_person_group;
568 END IF;
569
570 RETURN l_params_status;
571
572 EXCEPTION
573 WHEN OTHERS THEN
574 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
575 fnd_message.set_token('NAME','IGF_GR_GEN_XML.VALIDATE_INPUT_PARAMETERS');
576 igs_ge_msg_stack.add;
577 app_exception.raise_exception;
578 END validate_input_parameters;
579
580 FUNCTION xml_boundary_validate ( p_rfms_rec cur_rfms%ROWTYPE )
581 RETURN BOOLEAN AS
582 /*************************************************************
583 Created By : ugummall
584 Date Created On : 2004/10/04
585 Purpose :
586 Know limitations, enhancements or remarks
587 Change History
588 Who When What
589 (reverse chronological order - newest change first)
590 ***************************************************************/
591
592 -- Cursor to get disbursement data from IGF_AW_DB_CHG_DTLS table.
593 CURSOR cur_disb_chg_dtls ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
594 SELECT award_id,
595 disb_num,
596 disb_seq_num,
597 disb_accepted_amt,
598 disb_date
599 FROM IGF_AW_DB_CHG_DTLS dbchgdtls
600 WHERE dbchgdtls.award_id = cp_award_id;
601 rec_disb_chg_dtls cur_disb_chg_dtls%ROWTYPE;
602
603
604 student_dtl_cur igf_sl_gen.person_dtl_cur;
605 lv_complete BOOLEAN := TRUE;
606 l_award_id igf_aw_db_chg_dtls.award_id%TYPE;
607
608 --akomurav
609
610 CURSOR cur_isir_info (p_base_id NUMBER) IS
611 SELECT payment_isir,transaction_num,dependency_status,
612 date_of_birth,current_ssn,last_name,middle_initial
613 FROM igf_ap_isir_matched_all
614 WHERE base_id = p_base_id
615 AND payment_isir = 'Y'
616 AND system_record_type = 'ORIGINAL';
617
618
619
620 isir_info_rec cur_isir_info%ROWTYPE;
621 --akomurav
622
623 BEGIN
624
625 -- Use the igf_sl_gen.get_person_details for getting the student
626 igf_sl_gen.get_person_details(igf_gr_gen.get_person_id(p_rfms_rec.base_id),student_dtl_cur);
627 FETCH student_dtl_cur INTO student_dtl_rec;
628
629 CLOSE student_dtl_cur;
630
631 --akomurav
632 OPEN cur_isir_info (p_rfms_rec.base_id);
633 FETCH cur_isir_info INTO isir_info_rec;
634 CLOSE cur_isir_info;
635
636 student_dtl_rec.p_date_of_birth := isir_info_rec.date_of_birth;
637 student_dtl_rec.p_ssn := isir_info_rec.current_ssn;
638 student_dtl_rec.p_last_name := UPPER(isir_info_rec.last_name);
639 student_dtl_rec.p_middle_name := UPPER(isir_info_rec.middle_initial);
640 --akomurav
641
642
643 lv_complete := TRUE;
644
645 -- validating full response code
646 IF ( p_rfms_rec.full_resp_code NOT IN ('S', 'F', 'M', 'N') ) THEN
647 lv_complete := FALSE;
648 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
649 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FULL_RESP_CD'));
650 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.full_resp_code);
651 fnd_file.put_line(fnd_file.log, fnd_message.get);
652 END IF;
653
654 -- validating student's date of birth
655 IF g_ver_num = '2004-2005' THEN
656 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011905', 'DDMMYYYY')) OR (student_dtl_rec.p_date_of_birth > TO_DATE('31121996', 'DDMMYYYY')) ) THEN
657 lv_complete := FALSE;
658 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
659 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_DATE_OF_BIRTH'));
660 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.birth_dt);
661 fnd_file.put_line(fnd_file.log, fnd_message.get);
662 END IF;
663 END IF;
664 IF g_ver_num = '2005-2006' THEN
665 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011906', 'DDMMYYYY')) OR (student_dtl_rec.p_date_of_birth > TO_DATE('31121997', 'DDMMYYYY')) ) THEN
666 lv_complete := FALSE;
667 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
668 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_DATE_OF_BIRTH'));
669 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.birth_dt);
670 fnd_file.put_line(fnd_file.log, fnd_message.get);
671 END IF;
672 END IF;
673 IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
674 IF ( (student_dtl_rec.p_date_of_birth < TO_DATE('01011907', 'DDMMYYYY')) OR (student_dtl_rec.p_date_of_birth > TO_DATE('31121998', 'DDMMYYYY')) ) THEN
675 lv_complete := FALSE;
676 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
677 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_DATE_OF_BIRTH'));
678 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.birth_dt);
679 fnd_file.put_line(fnd_file.log, fnd_message.get);
680 END IF;
681 END IF;
682
683 -- validating student's SSN
684 IF ( (student_dtl_rec.p_ssn < '001010001') OR (student_dtl_rec.p_ssn > '999999998') ) THEN
685 lv_complete := FALSE;
686 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
687 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_SSN'));
688 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_ssn);
689 fnd_file.put_line(fnd_file.log, fnd_message.get);
690 END IF;
691
692 -- validating student's first name
693 IF ( LENGTH(student_dtl_rec.p_first_name) > 12) THEN
694 lv_complete := FALSE;
695 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
696 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_FIRST_NAME'));
697 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_first_name);
698 fnd_file.put_line(fnd_file.log, fnd_message.get);
699 END IF;
700
701 -- validating student's last name
702 IF ( LENGTH(student_dtl_rec.p_last_name) > 35) THEN
703 lv_complete := FALSE;
704 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
705 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_LAST_NAME'));
706 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_last_name);
707 fnd_file.put_line(fnd_file.log, fnd_message.get);
708 END IF;
709
710 -- validating student's middle name
711 IF ( LENGTH(student_dtl_rec.p_middle_name) > 1) THEN
712 lv_complete := FALSE;
713 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
714 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_MIDDLE_NAME'));
715 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_middle_name);
716 fnd_file.put_line(fnd_file.log, fnd_message.get);
717 END IF;
718
719 -- validating student's driver licencse state
720 IF ( (LENGTH(student_dtl_rec.p_license_state) < 2) OR (LENGTH(student_dtl_rec.p_license_state) > 3) )THEN
721 lv_complete := FALSE;
722 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
723 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','P_LICENSE_STATE'));
724 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_license_state);
725 fnd_file.put_line(fnd_file.log, fnd_message.get);
726 END IF;
727
728 -- validating student's driver licencse number
729 IF ( LENGTH(student_dtl_rec.p_license_num) > 20) THEN
730 lv_complete := FALSE;
731 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
732 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_LICENSE_NUM'));
733 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_license_num);
734 fnd_file.put_line(fnd_file.log, fnd_message.get);
735 END IF;
736
737 -- validating student's citizenship status code
738 IF ( (LENGTH(student_dtl_rec.p_citizenship_status) > 1) OR (student_dtl_rec.p_citizenship_status NOT IN ('1', '2', '3')) ) THEN
739 lv_complete := FALSE;
740 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
741 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','S_CITIZENSHIP_STATUS'));
742 fnd_message.set_token('FIELD_VALUE',student_dtl_rec.p_citizenship_status);
743 fnd_file.put_line(fnd_file.log, fnd_message.get);
744 END IF;
745
746 -- validating note message
747 IF ( LENGTH(p_rfms_rec.note_message) > 20) THEN
748 lv_complete := FALSE;
749 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
750 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','NOTE_MESSAGE'));
751 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.note_message);
752 fnd_file.put_line(fnd_file.log, fnd_message.get);
753 END IF;
754
755 -- validating student's low_tution_fee
756 IF ( (LENGTH(p_rfms_rec.low_tution_fee) > 1) OR (p_rfms_rec.low_tution_fee NOT IN ('1', '2', '3', '4')) ) THEN
757 lv_complete := FALSE;
758 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
759 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','LOW_TUTION_FEE'));
760 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.low_tution_fee);
761 fnd_file.put_line(fnd_file.log, fnd_message.get);
762 END IF;
763
764 -- validating transaction number
765 IF ( (LENGTH(p_rfms_rec.transaction_num) < '01') OR (LENGTH(p_rfms_rec.transaction_num) > '99') ) THEN
766 lv_complete := FALSE;
767 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
768 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','TRANSACTION_NUMBER'));
769 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.transaction_num);
770 fnd_file.put_line(fnd_file.log, fnd_message.get);
771 END IF;
772
773 -- validating enrollment date
774 IF g_ver_num = '2004-2005' THEN
775 IF ( (p_rfms_rec.enrollment_dt < TO_DATE('01012004', 'DDMMYYYY')) OR (p_rfms_rec.enrollment_dt > TO_DATE('30062005', 'DDMMYYYY')) ) THEN
776 lv_complete := FALSE;
777 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
778 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ENROLLMENT_DT'));
779 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.enrollment_dt);
780 fnd_file.put_line(fnd_file.log, fnd_message.get);
781 END IF;
782 END IF;
783 IF g_ver_num = '2005-2006' THEN
784 IF ( (p_rfms_rec.enrollment_dt < TO_DATE('01012005', 'DDMMYYYY')) OR (p_rfms_rec.enrollment_dt > TO_DATE('30062006', 'DDMMYYYY')) ) THEN
785 lv_complete := FALSE;
786 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
787 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ENROLLMENT_DT'));
788 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.enrollment_dt);
789 fnd_file.put_line(fnd_file.log, fnd_message.get);
790 END IF;
791 END IF;
792 IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
793 IF ( (p_rfms_rec.enrollment_dt < TO_DATE('01012006', 'DDMMYYYY')) OR (p_rfms_rec.enrollment_dt > TO_DATE('30062007', 'DDMMYYYY')) ) THEN
794 lv_complete := FALSE;
795 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
796 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_GR_LOOKUPS','ENROLLMENT_DT'));
797 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.enrollment_dt);
798 fnd_file.put_line(fnd_file.log, fnd_message.get);
799 END IF;
800 END IF;
801
802 -- validating secondary efc
803 IF ( (LENGTH(p_rfms_rec.secondary_efc_cd) > 1) OR (p_rfms_rec.secondary_efc_cd NOT IN ('O', 'S')) ) THEN
804 lv_complete := FALSE;
805 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
806 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_AP_AWARDING_FC_TYPE','2'));
807 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.secondary_efc);
808 fnd_file.put_line(fnd_file.log, fnd_message.get);
809 END IF;
810
811 -- validating verification status code
812 IF ( (LENGTH(p_rfms_rec.ver_status_code) > 1) OR (p_rfms_rec.ver_status_code NOT IN ('W', 'V', 'S')) ) THEN
813 lv_complete := FALSE;
814 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
815 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_VER'));
816 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.ver_status_code);
817 fnd_file.put_line(fnd_file.log, fnd_message.get);
818 END IF;
819
820 -- validating incarcerated status.
821 IF ( p_rfms_rec.incrcd_fed_pell_rcp_cd IS NOT NULL AND ((LENGTH(p_rfms_rec.incrcd_fed_pell_rcp_cd) > 1) OR (p_rfms_rec.incrcd_fed_pell_rcp_cd NOT IN ('Y', 'N'))) ) THEN
822 lv_complete := FALSE;
823 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
824 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','INCRCD_FED_PELL_RCP_CD'));
825 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.incrcd_fed_pell_rcp_cd);
826 fnd_file.put_line(fnd_file.log, fnd_message.get);
827 END IF;
828
829 IF ( p_rfms_rec.pell_amount >= 999999999.99) THEN
830 lv_complete := FALSE;
831 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
832 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_PELL'));
833 fnd_message.set_token('FIELD_VALUE',p_rfms_rec.pell_amount);
834 fnd_file.put_line(fnd_file.log, fnd_message.get);
835 END IF;
836
837 -- validating disbursement data from the IGF_AW_DB_CHG_DTLS table
838 l_award_id := p_rfms_rec.award_id;
839 FOR rec_disb_chg_dtls IN cur_disb_chg_dtls(l_award_id) LOOP
840 -- validating disbursement accepted amount
841 IF ( rec_disb_chg_dtls.disb_accepted_amt >= 999999999.99) THEN
842 lv_complete := FALSE;
843 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
844 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_AMT'));
845 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_accepted_amt);
846 fnd_file.put_line(fnd_file.log, fnd_message.get);
847 END IF;
848
849 -- validating disbursement number
850 IF ( rec_disb_chg_dtls.disb_num >= 60) THEN
851 lv_complete := FALSE;
852 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
853 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_AW_LOOKUPS_MSG','DISB_NUM'));
854 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_num);
855 fnd_file.put_line(fnd_file.log, fnd_message.get);
856 END IF;
857
858 -- validating disbursement sequence number
859 IF ( rec_disb_chg_dtls.disb_seq_num >= 99) THEN
860 lv_complete := FALSE;
861 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
862 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_LOAN_FIELDS','DISB_SEQ_NUM'));
863 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_seq_num);
864 fnd_file.put_line(fnd_file.log, fnd_message.get);
865 END IF;
866
867 -- validating disbursement date
868 IF g_ver_num = '2004-2005' THEN
869 IF ( (rec_disb_chg_dtls.disb_date < TO_DATE('22062003', 'DDMMYYYY')) OR (rec_disb_chg_dtls.disb_date > TO_DATE('27102006', 'DDMMYYYY')) ) THEN
870 lv_complete := FALSE;
871 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
872 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_CL_CHANGE_FIELDS','DISB_DATE'));
873 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_date);
874 fnd_file.put_line(fnd_file.log, fnd_message.get);
875 END IF;
876 END IF;
877 IF g_ver_num = '2005-2006' THEN
878 IF ( (rec_disb_chg_dtls.disb_date < TO_DATE('22062004', 'DDMMYYYY')) OR (rec_disb_chg_dtls.disb_date > TO_DATE('27102007', 'DDMMYYYY')) ) THEN
879 lv_complete := FALSE;
880 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
881 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_CL_CHANGE_FIELDS','DISB_DATE'));
882 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_date);
883 fnd_file.put_line(fnd_file.log, fnd_message.get);
884 END IF;
885 END IF;
886 IF g_ver_num = '2006-2007' THEN -- check made as part of FA162(COD Reg Updates R12 porting)
887 IF ( (rec_disb_chg_dtls.disb_date < TO_DATE('01072006', 'DDMMYYYY')) OR (rec_disb_chg_dtls.disb_date > TO_DATE('27102009', 'DDMMYYYY')) ) THEN
888 lv_complete := FALSE;
889 fnd_message.set_name('IGF','IGF_GR_COD_INVALID_VALUE');
890 fnd_message.set_token('FIELD_NAME',igf_aw_gen.lookup_desc('IGF_SL_CL_CHANGE_FIELDS','DISB_DATE'));
891 fnd_message.set_token('FIELD_VALUE',rec_disb_chg_dtls.disb_date);
892 fnd_file.put_line(fnd_file.log, fnd_message.get);
893 END IF;
894 END IF;
895 END LOOP;
896
897 RETURN lv_complete;
898
899 EXCEPTION
900 WHEN OTHERS THEN
901 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
902 fnd_message.set_token('NAME','IGF_GR_GEN_XML.XML_BOUNDARY_VALIDATE');
903 igs_ge_msg_stack.add;
904 app_exception.raise_exception;
905 END xml_boundary_validate;
906
907 PROCEDURE insert_into_cod_tables(p_rfms_rec cur_rfms%ROWTYPE, p_source_entity_id VARCHAR2)
908 IS
909 /*************************************************************
910 Created By : ugummall
911 Date Created On : 2004/10/04
912 Purpose :
913 Know limitations, enhancements or remarks
914 Change History
915 Who When What
916 (reverse chronological order - newest change first)
917 ***************************************************************/
918
919 -- Cursor to get disbursement data from IGF_AW_DB_CHG_DTLS table.
920 CURSOR cur_disb_chg_dtls ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
921 SELECT award_id,
922 disb_num,
923 disb_seq_num,
924 disb_accepted_amt,
925 disb_date
926 FROM IGF_AW_DB_CHG_DTLS dbchgdtls
927 WHERE dbchgdtls.award_id = cp_award_id;
928 rec_disb_chg_dtls cur_disb_chg_dtls%ROWTYPE;
929
930 -- Cursor to get ssn, last_name, dob from IGF_GR_COD_DTLS table
931 CURSOR cur_get_chg_data ( cp_origination_id igf_gr_cod_dtls.origination_id%TYPE) IS
932 SELECT s_ssn, s_last_name, s_date_of_birth
933 FROM IGF_GR_COD_DTLS
934 WHERE origination_id = cp_origination_id;
935 rec_get_chg_data cur_get_chg_data%ROWTYPE;
936
937 -- Cursor to get disbursement data.
938 CURSOR cur_disb_rec (cp_award_id NUMBER) IS
939 SELECT chg.*
940 FROM igf_aw_db_chg_dtls chg
941 WHERE award_id = cp_award_id
942 AND disb_status = 'G'; -- Ready to Send (introduced this predicate again bcz of bug #4390096)
943
944 student_dtl_cur igf_sl_gen.person_dtl_cur;
945
946 lv_last_name VARCHAR2(150);
947 lv_ssn VARCHAR2(30);
948 ld_dob DATE;
949 lv_chg_last_name VARCHAR2(150);
950 lv_chg_ssn VARCHAR2(30);
951 ld_chg_dob DATE;
952 lv_rowid ROWID;
953 l_s_phone VARCHAR2(30);
954
955
956 --akomurav
957
958 CURSOR cur_isir_info (p_base_id NUMBER) IS
959 SELECT payment_isir,transaction_num,dependency_status,
960 date_of_birth,current_ssn,last_name,middle_initial
961 FROM igf_ap_isir_matched_all
962 WHERE base_id = p_base_id
963 AND payment_isir = 'Y'
964 AND system_record_type = 'ORIGINAL';
965
966
967
968 isir_info_rec cur_isir_info%ROWTYPE;
969 --akomurav
970
971 BEGIN
972
973 -- Use the igf_sl_gen.get_person_details for getting the student
974 igf_sl_gen.get_person_details(igf_gr_gen.get_person_id(p_rfms_rec.base_id),student_dtl_cur);
975 FETCH student_dtl_cur INTO student_dtl_rec;
976 CLOSE student_dtl_cur;
977
978 --akomurav
979 OPEN cur_isir_info (p_rfms_rec.base_id);
980 FETCH cur_isir_info INTO isir_info_rec;
981 CLOSE cur_isir_info;
982
983 student_dtl_rec.p_date_of_birth := isir_info_rec.date_of_birth;
984 student_dtl_rec.p_ssn := isir_info_rec.current_ssn;
985 student_dtl_rec.p_last_name := UPPER(isir_info_rec.last_name);
986 student_dtl_rec.p_middle_name := UPPER(isir_info_rec.middle_initial);
987 --akomurav
988
989
990
991 lv_chg_last_name := NULL;
992 lv_chg_ssn := NULL;
993 ld_chg_dob := NULL;
994
995 OPEN cur_get_chg_data(p_rfms_rec.origination_id);
996 FETCH cur_get_chg_data INTO rec_get_chg_data;
997 IF cur_get_chg_data%NOTFOUND THEN
998 lv_last_name := UPPER(student_dtl_rec.p_last_name);
999 lv_ssn := student_dtl_rec.p_ssn;
1000 ld_dob := student_dtl_rec.p_date_of_birth;
1001 CLOSE cur_get_chg_data;
1002 ELSE
1003 lv_last_name := UPPER(rec_get_chg_data.s_last_name);
1004 lv_ssn := rec_get_chg_data.s_ssn;
1005 ld_dob := rec_get_chg_data.s_date_of_birth;
1006
1007 IF UPPER(rec_get_chg_data.s_last_name) <> UPPER(student_dtl_rec.p_last_name) THEN
1008 lv_chg_last_name := UPPER(student_dtl_rec.p_last_name);
1009 END IF;
1010
1011 IF rec_get_chg_data.s_ssn <> student_dtl_rec.p_ssn THEN
1012 lv_chg_ssn := student_dtl_rec.p_ssn;
1013 END IF;
1014
1015 IF rec_get_chg_data.s_date_of_birth <> student_dtl_rec.p_date_of_birth THEN
1016 ld_chg_dob := student_dtl_rec.p_date_of_birth;
1017 END IF;
1018
1019 CLOSE cur_get_chg_data;
1020 END IF;
1021 --akomurav
1022
1023 l_s_phone := igf_sl_gen.get_person_phone(igf_gr_gen.get_person_id(p_rfms_rec.base_id));
1024
1025 IF l_s_phone = 'N/A' then
1026 l_s_phone := NULL;
1027 END IF;
1028
1029 igf_gr_cod_dtls_pkg.add_row(
1030 x_rowid => lv_rowid,
1031 x_origination_id => p_rfms_rec.origination_id,
1032 x_award_id => p_rfms_rec.award_id,
1033 x_document_id_txt => gv_document_id_txt,
1034 x_base_id => p_rfms_rec.base_id,
1035 x_fin_award_year => SUBSTR(g_ver_num,-4),
1036 x_cps_trans_num => p_rfms_rec.transaction_num,
1037 x_award_amt => p_rfms_rec.pell_amount,
1038 x_coa_amt => p_rfms_rec.coa_amount,
1039 x_low_tution_fee => p_rfms_rec.low_tution_fee,
1040 x_incarc_flag => p_rfms_rec.incrcd_fed_pell_rcp_cd,
1041 x_ver_status_code => p_rfms_rec.ver_status_code,
1042 x_enrollment_date => p_rfms_rec.enrollment_dt,
1043 x_sec_efc_code => p_rfms_rec.secondary_efc,
1044 x_ytd_disb_amt => NULL,
1045 x_tot_elig_used => NULL,
1046 x_schd_pell_amt => NULL,
1047 x_neg_pend_amt => NULL,
1048 x_cps_verif_flag => NULL,
1049 x_high_cps_trans_num => NULL,
1050 x_note_message => p_rfms_rec.note_message,
1051 x_full_resp_code => NVL(g_full_resp_code, 'F'),
1052 x_atd_entity_id_txt => p_rfms_rec.atd_entity_id_txt,
1053 x_rep_entity_id_txt => p_rfms_rec.rep_entity_id_txt,
1054 x_source_entity_id_txt => p_source_entity_id,
1055 x_pell_status => p_rfms_rec.orig_action_code,
1056 x_pell_status_date => p_rfms_rec.orig_status_dt,
1057 x_s_ssn => lv_ssn,
1058 x_driver_lic_state => student_dtl_rec.p_license_state,
1059 x_driver_lic_number => student_dtl_rec.p_license_num,
1060 x_s_date_of_birth => ld_dob,
1061 x_first_name => UPPER(student_dtl_rec.p_first_name),
1062 x_middle_name => UPPER(student_dtl_rec.p_middle_name),
1063 x_s_last_name => lv_last_name,
1064 x_s_chg_date_of_birth => ld_chg_dob,
1065 x_s_chg_ssn => lv_chg_ssn,
1066 x_s_chg_last_name => lv_chg_last_name,
1067 x_permt_addr_foreign_flag => NULL,
1068 x_addr_type_code => NULL,
1069 x_permt_addr_line_1 => UPPER(student_dtl_rec.p_permt_addr1),
1070 x_permt_addr_line_2 => UPPER(student_dtl_rec.p_permt_addr2),
1071 x_permt_addr_line_3 => NULL,--UPPER(student_dtl_rec.p_permt_addr3),
1072 x_permt_addr_city => UPPER(student_dtl_rec.p_permt_city),
1073 x_permt_addr_state_code => UPPER(student_dtl_rec.p_permt_state),
1074 x_permt_addr_post_code => UPPER(student_dtl_rec.p_permt_zip),
1075 x_permt_addr_county => UPPER(student_dtl_rec.p_county),
1076 x_permt_addr_country => UPPER(student_dtl_rec.p_country),
1077 x_phone_number_1 => l_s_phone,
1078 x_phone_number_2 => NULL,
1079 x_phone_number_3 => NULL,
1080 x_email_address => UPPER(student_dtl_rec.p_email_addr),
1081 x_citzn_status_code => student_dtl_rec.p_citizenship_status,
1082 x_mode => 'R');
1083
1084 FOR rec IN cur_disb_rec (p_rfms_rec.award_id)
1085 LOOP
1086 lv_rowid := NULL;
1087 IF rec.disb_conf_flag IS NULL THEN
1088 rec.disb_conf_flag := 'false';
1089 ELSIF rec.disb_conf_flag = 'Y' THEN
1090 rec.disb_conf_flag := 'false';
1091 ELSIF rec.disb_conf_flag = 'N' THEN
1092 rec.disb_conf_flag := 'true';
1093 END IF;
1094 igf_aw_db_cod_dtls_pkg.add_row( x_rowid => lv_rowid,
1095 x_award_id => rec.award_id,
1096 x_document_id_txt => gv_document_id_txt,
1097 x_disb_num => rec.disb_num,
1098 x_disb_seq_num => rec.disb_seq_num,
1099 x_disb_accepted_amt => rec.disb_accepted_amt,
1100 x_orig_fee_amt => rec.orig_fee_amt,
1101 x_disb_net_amt => rec.disb_net_amt,
1102 x_disb_date => rec.disb_date,
1103 x_disb_rel_flag => LOWER(rec.disb_rel_flag),
1104 x_first_disb_flag => rec.first_disb_flag,
1105 x_interest_rebate_amt => rec.interest_rebate_amt,
1106 x_disb_conf_flag => rec.disb_conf_flag,
1107 x_pymnt_per_start_date => rec.pymnt_prd_start_date,
1108 x_note_message => rec.note_message,
1109 x_rep_entity_id_txt => p_rfms_rec.rep_entity_id_txt,
1110 x_atd_entity_id_txt => p_rfms_rec.atd_entity_id_txt,
1111 x_mode => 'R');
1112
1113 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1114 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.insert_into_cod_tables.debug','after inserting cod db dtls seq num, disb num, award id' || rec.disb_seq_num || ' , ' || rec.disb_num || ' , ' || rec.award_id);
1115 END IF;
1116 END LOOP;
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1120 fnd_message.set_token('NAME','IGF_GR_GEN_XML.INSERT_INTO_COD_TABLES');
1121 igs_ge_msg_stack.add;
1122 app_exception.raise_exception;
1123 END insert_into_cod_tables;
1124
1125 PROCEDURE process_rfms_record(p_rfms_rec IN OUT NOCOPY cur_rfms%ROWTYPE, p_source_entity_id VARCHAR2)
1126 IS
1127 /*************************************************************
1128 Created By : ugummall
1129 Date Created On : 2004/10/04
1130 Purpose :
1131 Know limitations, enhancements or remarks
1132 Change History
1133 Who When What
1134 (reverse chronological order - newest change first)
1135 ***************************************************************/
1136
1137 CURSOR cur_attending_entity ( cp_ci_cal_type igf_gr_report_pell.ci_cal_type%TYPE,
1138 cp_ci_sequence_number igf_gr_report_pell.ci_sequence_number%TYPE,
1139 cp_rep_entity_id_txt igf_gr_report_pell.rep_entity_id_txt%TYPE,
1140 cp_atd_entity_id_txt igf_gr_attend_pell.atd_entity_id_txt%TYPE) IS
1141 SELECT 'Y'
1142 FROM IGF_GR_REPORT_PELL rep, IGF_GR_ATTEND_PELL atd
1143 WHERE rep.rcampus_id = atd.rcampus_id
1144 AND rep.ci_cal_type = cp_ci_cal_type
1145 AND rep.ci_sequence_number = cp_ci_sequence_number
1146 AND rep.rep_entity_id_txt = cp_rep_entity_id_txt
1147 AND atd.atd_entity_id_txt = cp_atd_entity_id_txt;
1148 rec_attending_entity cur_attending_entity%ROWTYPE;
1149
1150 CURSOR cur_pymnt_isir ( cp_base_id igf_gr_rfms.base_id%TYPE ) IS
1151 SELECT isir.transaction_num
1152 FROM IGF_AP_ISIR_MATCHED_ALL isir
1153 WHERE isir.base_id = cp_base_id
1154 AND isir.payment_isir = 'Y'
1155 AND isir.system_record_type = 'ORIGINAL';
1156 cur_pymnt_isir_rec cur_pymnt_isir%rowtype;
1157
1158 CURSOR cur_disb_amt_tot ( cp_award_id igf_aw_db_chg_dtls.award_id%TYPE) IS
1159 SELECT sum(a.disb_accepted_amt) disb_amt_tot
1160 FROM IGF_AW_DB_CHG_DTLS a
1161 WHERE a.award_id = cp_award_id
1162 AND NVL(a.disb_activity, 'x') <> 'Q'
1163 AND a.disb_seq_num = ( SELECT max(b.disb_seq_num)
1164 FROM IGF_AW_DB_CHG_DTLS b
1165 WHERE b.award_id = cp_award_id
1166 AND NVL(b.disb_activity, 'x') <> 'Q'
1167 AND b.disb_num = a.disb_num);
1168 rec_disb_amt_tot cur_disb_amt_tot%ROWTYPE;
1169
1170 -- To get number of disbursements with disbursement release indicator 'true'
1171 CURSOR cur_get_num_of_disb (cp_award_id igf_aw_awd_disb.award_id%TYPE) IS
1172 SELECT count(*)
1173 FROM IGF_AW_AWD_DISB disb
1174 WHERE disb.award_id = cp_award_id
1175 AND UPPER(disb.hold_rel_ind) = 'TRUE';
1176 ln_num_disb_with_relflag_true NUMBER(3);
1177
1178 l_isir_present BOOLEAN;
1179 lb_spoint_est BOOLEAN;
1180 l_pell_amt igf_gr_rfms.pell_amount%TYPE;
1181 l_ft_pell_amt igf_gr_rfms_all.ft_pell_amount%TYPE;
1182 l_return_status VARCHAR2(1);
1183 l_return_mesg_text VARCHAR2(2000);
1184
1185 BEGIN
1186 -- This is Step 4.
1187
1188 IF gv_document_id_txt IS NULL THEN
1189 gv_document_id_txt := TO_CHAR(TRUNC(SYSDATE),'YYYY-MM-DD')||'T'||TO_CHAR(SYSDATE,'HH:MM:SS') || '.00' || LPAD(p_source_entity_id, 8, '0');
1190 END IF;
1191 -- 1. validate general validations
1192 -- 2. validate xml boundary validations
1193 -- 3. insert data into IGF_GR_COD_DTLS and IGF_AW_DB_COD_DTLS tables.
1194
1195 fnd_message.set_name('IGF','IGF_GR_PROCESS_STUD');
1196 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(p_rfms_rec.base_id));
1197 fnd_message.set_token('ORIG_ID',p_rfms_rec.origination_id);
1198 fnd_file.put_line(fnd_file.log,fnd_message.get);
1199
1200 -- validate general validations
1201
1202 -- check if attending entity id is a child of reporting entity id.
1203 rec_attending_entity := NULL;
1204 OPEN cur_attending_entity ( p_rfms_rec.ci_cal_type, p_rfms_rec.ci_sequence_number, p_rfms_rec.rep_entity_id_txt, p_rfms_rec.atd_entity_id_txt);
1205 FETCH cur_attending_entity INTO rec_attending_entity;
1206 IF (cur_attending_entity%NOTFOUND) THEN -- Attending entity child record exists?
1207 -- No attending pell child record exists. Do not process this record
1208 CLOSE cur_attending_entity;
1209 fnd_message.set_name('IGF','IGF_GR_ATD_ENTITY_NOT_SETUP');
1210 fnd_message.set_token('ATD_ENTITY', p_rfms_rec.atd_entity_id_txt);
1211 fnd_file.put_line(fnd_file.log, fnd_message.get);
1212 RETURN;
1213 ELSE
1214 -- attending pell child record exists. Proceed further...
1215 CLOSE cur_attending_entity;
1216 END IF;
1217
1218 -- Get The Payment ISIR Transaction Number
1219 cur_pymnt_isir_rec := NULL;
1220 l_isir_present := TRUE;
1221 OPEN cur_pymnt_isir(p_rfms_rec.base_id);
1222 FETCH cur_pymnt_isir INTO cur_pymnt_isir_rec;
1223 IF cur_pymnt_isir%NOTFOUND THEN
1224 l_isir_present := FALSE;
1225 END IF;
1226 CLOSE cur_pymnt_isir;
1227
1228 IF NOT l_isir_present THEN
1229 fnd_message.set_name('IGF','IGF_AP_NO_PAYMENT_ISIR');
1230 fnd_file.put_line(fnd_file.log,fnd_message.get);
1231
1232 -- If the Transaction Number being reported does not match do not Originate
1233 ELSIF p_rfms_rec.transaction_num <> NVL(cur_pymnt_isir_rec.transaction_num,-1) THEN
1234 fnd_message.set_name('IGF','IGF_GR_PYMNT_ISIR_MISMATCH');
1235 fnd_file.put_line(fnd_file.log,fnd_message.get);
1236 RETURN;
1237 END IF;
1238
1239 -- Get disbursements amounts total
1240 rec_disb_amt_tot := NULL;
1241 OPEN cur_disb_amt_tot(p_rfms_rec.award_id);
1242 FETCH cur_disb_amt_tot INTO rec_disb_amt_tot;
1243 CLOSE cur_disb_amt_tot;
1244
1245 -- If origination record's amount is less than sum of disbursement amounts, then do not originate.
1246 IF (p_rfms_rec.pell_amount < rec_disb_amt_tot.disb_amt_tot) THEN
1247 fnd_message.set_name('IGF','IGF_GR_PELL_DIFF_AMTS');
1248 fnd_message.set_token('DISB_AMT', TO_CHAR(rec_disb_amt_tot.disb_amt_tot));
1249 fnd_message.set_token('PELL_TOT', TO_CHAR(p_rfms_rec.pell_amount));
1250 fnd_file.put_line(fnd_file.log,fnd_message.get);
1251 RETURN;
1252 END IF;
1253
1254 -- If Student's verification status is 'W' then we can send only one disbursment with
1255 -- disbursement release indicator as 'true'. If there are more than one disb, then log a mesg.
1256 IF (p_rfms_rec.ver_status_code = 'W') THEN
1257 -- count number of disb with release indicator as 'true'
1258 ln_num_disb_with_relflag_true := 0;
1259 OPEN cur_get_num_of_disb(p_rfms_rec.award_id);
1260 FETCH cur_get_num_of_disb INTO ln_num_disb_with_relflag_true;
1261 CLOSE cur_get_num_of_disb;
1262
1263 IF ln_num_disb_with_relflag_true > 1 THEN
1264 fnd_message.set_name('IGF','IGF_GR_COD_VERF_STAT_W');
1265 fnd_file.put_line(fnd_file.log, fnd_message.get);
1266 RETURN;
1267 END IF;
1268 END IF;
1269
1270 -- Check accepted pell <= Calculated Pell. Otherwise log mesg.
1271 igf_gr_pell_calc.calc_ft_max_pell ( cp_base_id => p_rfms_rec.base_id,
1272 cp_cal_type => p_rfms_rec.ci_cal_type,
1273 cp_sequence_number => p_rfms_rec.ci_sequence_number,
1274 cp_flag => 'FULL_TIME',
1275 cp_aid => l_pell_amt,
1276 cp_ft_aid => l_ft_pell_amt,
1277 cp_return_status => l_return_status,
1278 cp_message => l_return_mesg_text
1279 );
1280 IF (l_return_status = 'E') THEN
1281 fnd_file.put_line(fnd_file.log, l_return_mesg_text);
1282 RETURN;
1283 ELSE
1284 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1285 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_gen_xml.process_rfms_record.debug','l_pell_amt = ' || l_pell_amt || 'and l_ft_pell_amt = ' || l_ft_pell_amt);
1286 END IF;
1287 IF p_rfms_rec.pell_amount > l_ft_pell_amt THEN
1288 fnd_message.set_name('IGF','IGF_GR_LIMIT_EXC');
1289 fnd_message.set_token('PEL_AMT',l_ft_pell_amt);
1290 fnd_message.set_token('AWD_AMT',p_rfms_rec.pell_amount);
1291 fnd_file.put_line(fnd_file.log, fnd_message.get);
1292 RETURN;
1293 END IF;
1294 END IF;
1295
1296 -- Updates to COD Technical Reference (May 05 changes)
1297 -- Attendance Cost tag is mandatory when processing a Pell Grant anticipated/actual disbursement.
1298 -- Check wether the attendance cost value is NULL or not. If NULL, then log a message.
1299 IF p_rfms_rec.coa_amount IS NULL THEN
1300 fnd_message.set_name('IGF','IGF_GR_NO_COA_NULL');
1301 fnd_file.put_line(fnd_file.log, fnd_message.get);
1302 RETURN;
1303 END IF;
1304
1305 -- xml boundary validations.
1306 IF NOT xml_boundary_validate (p_rfms_rec) THEN
1307 RETURN;
1308 END IF;
1309
1310 -- insert data into cod tables.
1311 lb_spoint_est := FALSE;
1312 SAVEPOINT IGFGR12B_PROCESS_RFMS_REC;
1313 lb_spoint_est := TRUE;
1314 insert_into_cod_tables(p_rfms_rec, p_source_entity_id);
1315
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1319 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_gen_xml.process_rfms_record.exception','Exception:'||SQLERRM);
1320 END IF;
1321 fnd_message.set_name('IGF','IGF_GR_XML_INSERT_EXC');
1322 fnd_message.set_token('ORIGINATION_ID',p_rfms_rec.origination_id);
1323 fnd_file.put_line(fnd_file.log,fnd_message.get);
1324 fnd_file.put_line(fnd_file.log,SQLERRM);
1325 IF lb_spoint_est THEN
1326 lb_spoint_est := FALSE;
1327 ROLLBACK TO IGFGR12B_PROCESS_RFMS_REC;
1328 END IF;
1329 END process_rfms_record;
1330
1331 PROCEDURE submit_xml_event ( p_document_id VARCHAR2)
1332 IS
1333 /*************************************************************
1334 Created By : ugummall
1335 Date Created On : 2004/10/04
1336 Purpose :
1337 Know limitations, enhancements or remarks
1338 Change History
1339 Who When What
1340 (reverse chronological order - newest change first)
1341 ***************************************************************/
1342
1343 l_parameter_list wf_parameter_list_t;
1344
1345 l_event_name VARCHAR2(255);
1346 l_event_key NUMBER;
1347 l_map_code VARCHAR2(255);
1348 l_param_1 VARCHAR2(255);
1349
1350 CURSOR cur_sequence IS SELECT IGF_GR_PELL_GEN_XML_S.NEXTVAL FROM DUAL;
1351
1352 BEGIN
1353
1354 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1355 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.submit_xml_event','p_document id: '||p_document_id);
1356 END IF;
1357
1358 l_parameter_list := wf_parameter_list_t();
1359 l_event_name := 'oracle.apps.igf.gr.genxml';
1360 l_map_code := 'IGF_GR_PELL_OUT';
1361 l_param_1 := p_document_id;
1362
1363 OPEN cur_sequence;
1364 FETCH cur_sequence INTO l_event_key;
1365 CLOSE cur_sequence;
1366
1367 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1368 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.submit_xml_event','l_event_key : '||l_event_key);
1369 END IF;
1370
1371 -- Now add the parameters to the list to be passed to the workflow
1372
1373 wf_event.addparametertolist(
1374 p_name => 'EVENT_NAME',
1375 p_value => l_event_name,
1376 p_parameterlist => l_parameter_list
1377 );
1378 wf_event.addparametertolist(
1379 p_name => 'EVENT_KEY',
1380 p_value => l_event_key,
1381 p_parameterlist => l_parameter_list
1382 );
1383 wf_event.addparametertolist(
1384 p_name => 'ECX_MAP_CODE',
1385 p_value => l_map_code,
1386 p_parameterlist => l_parameter_list
1387 );
1388
1389 wf_event.addparametertolist(
1390 p_name => 'ECX_PARAMETER1',
1391 p_value => l_param_1,
1392 p_parameterlist => l_parameter_list
1393 );
1394
1395 wf_event.RAISE (
1396 p_event_name => l_event_name,
1397 p_event_key => l_event_key,
1398 p_parameters => l_parameter_list);
1399
1400 fnd_message.set_name('IGF','IGF_GR_COD_RAISE_EVENT');
1401 fnd_message.set_token('EVENT_KEY_VALUE',l_event_key);
1402 fnd_file.put_line(fnd_file.log,fnd_message.get);
1403 fnd_file.new_line(fnd_file.log,1);
1404
1405 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1406 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.submit_xml_event','raised event ');
1407 END IF;
1408
1409 EXCEPTION
1410 WHEN OTHERS THEN
1411 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1412 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_gen_xml.submit_xml_event.exception','Exception:'||SQLERRM);
1413 END IF;
1414 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1415 fnd_message.set_token('NAME','IGF_GR_GEN_XML.SUBMIT_XML_EVENT');
1416 igs_ge_msg_stack.add;
1417 END submit_xml_event;
1418
1419 PROCEDURE main (
1420 errbuf OUT NOCOPY VARCHAR2,
1421 retcode OUT NOCOPY NUMBER,
1422 p_award_year IN VARCHAR2,
1423 p_source_entity_id IN VARCHAR2,
1424 p_report_entity_id IN VARCHAR2,
1425 p_rep_dummy IN VARCHAR2,
1426 p_attend_entity_id IN VARCHAR2,
1427 p_atd_dummy IN VARCHAR2,
1428 p_base_id IN IGF_GR_RFMS_ALL.BASE_ID%TYPE,
1429 p_per_dummy IN NUMBER,
1430 p_persid_grp IN NUMBER
1431 )
1432 IS
1433 /*************************************************************
1434 Created By : ugummall
1435 Date Created On : 2004/10/04
1436 Purpose :
1437 Know limitations, enhancements or remarks
1438 Change History
1439 Who When What
1440 (reverse chronological order - newest change first)
1441 ridas 08-Feb-2006 Bug #5021084. Added new parameter 'lv_group_type' in call to igf_ap_ss_pkg.get_pid
1442 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1443 ***************************************************************/
1444
1445 CURSOR cur_cod_dtls ( cp_document_id VARCHAR2) IS
1446 SELECT document_id_txt
1447 FROM IGF_GR_COD_DTLS
1448 WHERE document_id_txt = cp_document_id;
1449 cod_dtls_rec cur_cod_dtls%ROWTYPE;
1450
1451 CURSOR cur_full_resp_code ( cp_cal_type VARCHAR2, cp_sequence_number NUMBER, cp_source_entity_id VARCHAR2) IS
1452 SELECT response_option_code
1453 FROM IGF_GR_PELL_SETUP_ALL
1454 WHERE ci_cal_type = cp_cal_type
1455 AND ci_sequence_number = cp_sequence_number
1456 AND rep_entity_id_txt = cp_source_entity_id;
1457 rec_full_resp_code cur_full_resp_code%ROWTYPE;
1458
1459 TYPE cur_person_id_type IS REF CURSOR;
1460 cur_per_grp cur_person_id_type;
1461
1462 l_params_status BOOLEAN;
1463 l_flag BOOLEAN;
1464 lb_record_exist BOOLEAN;
1465 lb_record_exist_stdnt BOOLEAN;
1466 lv_status VARCHAR2(1);
1467 l_ci_cal_type VARCHAR2(11);
1468 l_ci_sequence_number NUMBER;
1469 ln_base_id NUMBER;
1470 lv_person_number hz_parties.party_number%TYPE;
1471 l_person_id hz_parties.party_id%TYPE;
1472 l_list VARCHAR2(32767);
1473 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
1474
1475 BEGIN
1476
1477 --
1478 -- Steps
1479 -- 1. Print parameters
1480 -- 2. Validate parameters
1481 -- 3. Find PELL records to be processed
1482 -- 4. Validate Pell records
1483 -- 5. Insert valid pell records into IGF_GR_COD_DTLS, and disb records into IGF_AW_DB_COD_DTLS
1484 -- 6. Raise Business Event
1485 --
1486 igf_aw_gen.set_org_id(NULL);
1487 retcode := 0;
1488 l_ci_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
1489 l_ci_sequence_number := NVL(TO_NUMBER(SUBSTR(p_award_year,11)),0);
1490 g_ver_num := igf_aw_gen.get_ver_num(l_ci_cal_type,l_ci_sequence_number,'P');
1491
1492 -- Step 1. Print parameters
1493 log_input_parameters(p_award_year, p_source_entity_id, p_report_entity_id, p_attend_entity_id, p_base_id, p_persid_grp);
1494
1495 -- Step 2. Validate input parameters
1496 IF NOT validate_input_parameters(p_award_year, p_source_entity_id, p_report_entity_id, p_attend_entity_id, p_base_id, p_persid_grp) THEN
1497 RETURN;
1498 END IF;
1499
1500 -- Get the full response code using Source Entity ID.
1501 OPEN cur_full_resp_code(l_ci_cal_type, l_ci_sequence_number, p_source_entity_id);
1502 FETCH cur_full_resp_code INTO rec_full_resp_code;
1503 IF cur_full_resp_code%NOTFOUND THEN
1504 -- print error message;
1505 fnd_message.set_name('IGF','IGF_GR_NO_RESP_OPT_SRC_ENTITY');
1506 fnd_file.put_line(fnd_file.log, fnd_message.get);
1507 CLOSE cur_full_resp_code;
1508 RETURN;
1509 ELSE
1510 g_full_resp_code := rec_full_resp_code.response_option_code;
1511 CLOSE cur_full_resp_code;
1512 END IF;
1513
1514 -- Start of Step 3. Find all PELL records to be processed and process them.
1515 -- Processing when base_id is given.
1516 IF p_base_id IS NOT NULL THEN
1517 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1518 fnd_message.set_token('STDNT',igf_gr_gen.get_per_num(p_base_id));
1519 fnd_file.put_line(fnd_file.log, fnd_message.get);
1520
1521 lb_record_exist := FALSE;
1522 FOR rec IN cur_rfms (l_ci_cal_type, l_ci_sequence_number, p_report_entity_id, p_attend_entity_id, p_base_id)
1523 LOOP
1524 process_rfms_record(rec,p_source_entity_id);
1525 IF NOT lb_record_exist THEN
1526 lb_record_exist := TRUE;
1527 END IF;
1528 END LOOP;
1529
1530 IF NOT lb_record_exist THEN
1531 fnd_message.set_name('IGF','IGF_GR_COD_NO_STDNT_RFMS_REC');
1532 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(p_base_id));
1533 fnd_file.put_line(fnd_file.log, fnd_message.get);
1534 fnd_file.new_line(fnd_file.log, 1);
1535 RETURN;
1536 END IF;
1537 END IF;
1538
1539 -- Processing when person id group is given.
1540 IF p_persid_grp IS NOT NULL THEN
1541 fnd_message.set_name('IGF','IGF_AW_PERSON_ID_GROUP');
1542 fnd_message.set_token('P_PER_GRP',get_grp_name(p_persid_grp));
1543 fnd_file.new_line(fnd_file.log, 1);
1544 fnd_file.put_line(fnd_file.log, fnd_message.get);
1545
1546 --Bug #5021084
1547 l_list := igf_ap_ss_pkg.get_pid(p_persid_grp,lv_status,lv_group_type);
1548
1549 --Bug #5021084. Passing Group ID if the group type is STATIC.
1550 IF lv_group_type = 'STATIC' THEN
1551 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ' USING p_persid_grp;
1552 ELSIF lv_group_type = 'DYNAMIC' THEN
1553 OPEN cur_per_grp FOR ' SELECT PARTY_ID FROM HZ_PARTIES WHERE PARTY_ID IN (' || l_list || ') ';
1554 END IF;
1555
1556 FETCH cur_per_grp INTO l_person_id;
1557
1558 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1559 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug','Starting to process person group '||p_persid_grp);
1560 END IF;
1561
1562 IF cur_per_grp%NOTFOUND THEN
1563 CLOSE cur_per_grp;
1564 fnd_message.set_name('IGF','IGF_DB_NO_PER_GRP');
1565 fnd_file.put_line(fnd_file.log,fnd_message.get);
1566 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug','No persons in group '||p_persid_grp);
1568 END IF;
1569 ELSE
1570 IF cur_per_grp%FOUND THEN -- Check if the person exists in FA.
1571 lb_record_exist := FALSE;
1572 LOOP
1573 ln_base_id := NULL;
1574 lv_person_number := NULL;
1575 lv_person_number := per_in_fa (l_person_id,l_ci_cal_type,l_ci_sequence_number,ln_base_id);
1576 IF lv_person_number IS NOT NULL THEN
1577 IF ln_base_id IS NOT NULL THEN
1578 fnd_message.set_name('IGF','IGF_AW_PROC_STUD');
1579 fnd_message.set_token('STDNT',lv_person_number);
1580 fnd_file.put_line(fnd_file.log, fnd_message.get);
1581
1582 lb_record_exist_stdnt := FALSE;
1583 FOR rec IN cur_rfms (l_ci_cal_type, l_ci_sequence_number, p_report_entity_id, p_attend_entity_id, ln_base_id)
1584 LOOP
1585 process_rfms_record(rec,p_source_entity_id);
1586 IF NOT lb_record_exist_stdnt THEN
1587 lb_record_exist_stdnt := TRUE;
1588 END IF;
1589 IF NOT lb_record_exist THEN
1590 lb_record_exist := TRUE;
1591 END IF;
1592 END LOOP;
1593
1594 IF NOT lb_record_exist_stdnt THEN
1595 fnd_message.set_name('IGF','IGF_GR_COD_NO_STDNT_RFMS_REC');
1596 fnd_message.set_token('PER_NUM',igf_gr_gen.get_per_num(p_base_id));
1597 fnd_file.put_line(fnd_file.log, fnd_message.get);
1598 fnd_file.new_line(fnd_file.log, 1);
1599 END IF;
1600 ELSE -- log a message and skip this person, base id not found
1601 fnd_message.set_name('IGF','IGF_GR_LI_PER_INVALID');
1602 fnd_message.set_token('PERSON_NUMBER',lv_person_number);
1603 fnd_message.set_token('AWD_YR',igf_gr_gen.get_alt_code(l_ci_cal_type,l_ci_sequence_number));
1604 fnd_file.put_line(fnd_file.log,fnd_message.get);
1605 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1606 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug',igf_gr_gen.get_per_num_oss(l_person_id) || ' not in FA');
1607 END IF;
1608 END IF; -- base id not found
1609 ELSE
1610 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
1611 fnd_file.put_line(fnd_file.log,RPAD(' ',5) ||fnd_message.get);
1612 END IF; -- person number not null
1613
1614 FETCH cur_per_grp INTO l_person_id;
1615 EXIT WHEN cur_per_grp%NOTFOUND;
1616 END LOOP;
1617 CLOSE cur_per_grp;
1618 IF NOT lb_record_exist THEN
1619 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
1620 fnd_file.put_line(fnd_file.log, fnd_message.get);
1621 fnd_file.new_line(fnd_file.log, 1);
1622 END IF;
1623 END IF; -- group found
1624 END IF;
1625 END IF;
1626
1627 -- Processing when base_id and persond id groups are not given.
1628 IF p_base_id IS NULL AND p_persid_grp IS NULL THEN
1629 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1630 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug',' processing when base_id and person id groups are null');
1631 END IF;
1632
1633 lb_record_exist := FALSE;
1634 FOR rec IN cur_rfms (l_ci_cal_type, l_ci_sequence_number, p_report_entity_id, p_attend_entity_id, p_base_id)
1635 LOOP
1636 process_rfms_record(rec,p_source_entity_id);
1637 IF NOT lb_record_exist THEN
1638 lb_record_exist := TRUE;
1639 END IF;
1640 END LOOP;
1641 IF NOT lb_record_exist THEN
1642 fnd_message.set_name('IGF','IGF_GR_NO_RFMS_ORIG');
1643 fnd_file.put_line(fnd_file.log, fnd_message.get);
1644 fnd_file.new_line(fnd_file.log, 1);
1645 END IF;
1646 END IF;
1647 -- End of Step 3.
1648
1649 -- Start of Step 6.
1650 OPEN cur_cod_dtls(gv_document_id_txt);
1651 FETCH cur_cod_dtls INTO cod_dtls_rec;
1652 CLOSE cur_cod_dtls;
1653
1654 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1655 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug',' gv_document_id_txt ' || gv_document_id_txt);
1656 END IF;
1657
1658 IF cod_dtls_rec.document_id_txt IS NULL THEN
1659 fnd_message.set_name('IGF','IGF_GR_COD_NO_REC');
1660 fnd_file.put_line(fnd_file.log,fnd_message.get);
1661 RETURN;
1662 ELSE
1663 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1664 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug',' before submit event ');
1665 END IF;
1666 submit_xml_event (gv_document_id_txt);
1667 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1668 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.main.debug',' after submit event ');
1669 END IF;
1670 END IF;
1671 -- End of Step 6.
1672
1673 COMMIT;
1674 EXCEPTION
1675 WHEN OTHERS THEN
1676 ROLLBACK;
1677 retcode := 2;
1678 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1679 fnd_file.put_line(fnd_file.log,SQLERRM);
1680 igs_ge_msg_stack.conc_exception_hndl;
1681 END main;
1682
1683 PROCEDURE store_xml ( itemtype IN VARCHAR2,
1684 itemkey IN VARCHAR2,
1685 actid IN NUMBER,
1686 funcmode IN VARCHAR2,
1687 resultout OUT NOCOPY VARCHAR2)
1688 IS
1689 /*************************************************************
1690 Created By : ugummall
1691 Date Created On : 2004/10/04
1692 Purpose :
1693 Know limitations, enhancements or remarks
1694 Change History
1695 Who When What
1696 (reverse chronological order - newest change first)
1697 ***************************************************************/
1698
1699 l_clob CLOB;
1700 l_event wf_event_t;
1701 ln_request_id NUMBER;
1702 lv_rowid ROWID;
1703 lv_document_id VARCHAR2(30);
1704
1705 BEGIN
1706
1707 --
1708 -- Steps
1709 -- 1. Read event data
1710 -- 2. Push xml into table
1711 -- 3. Launch Concurrent Request
1712 --
1713
1714 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1715 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' before reading lob ');
1716 END IF;
1717
1718 l_event := wf_engine.getitemattrevent ( itemtype,
1719 itemkey,
1720 'ECX_EVENT_MESSAGE'
1721 );
1722 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1723 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' after reading lob ');
1724 END IF;
1725
1726 l_clob := l_event.geteventdata;
1727
1728 IF DBMS_LOB.GETLENGTH(l_clob) = 0 THEN
1729 resultout := 'EMPTY_CLOB';
1730 ELSE
1731 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1732 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' get doc id ');
1733 END IF;
1734 lv_document_id := NULL;
1735 lv_document_id := wf_engine.getitemattrtext ( itemtype,
1736 itemkey,
1737 'ECX_PARAMETER1'
1738 );
1739 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1740 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' get doc id = ' || lv_document_id);
1741 END IF;
1742
1743 IF lv_document_id IS NULL THEN
1744 resultout := 'DOCUMENT_ID_NOT_FOUND';
1745 ELSE
1746 lv_rowid := NULL;
1747 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1748 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' insert into doc dtls ');
1749 END IF;
1750
1751 igf_sl_cod_doc_dtls_pkg.insert_row(
1752 x_rowid => lv_rowid,
1753 x_document_id_txt => lv_document_id,
1754 x_outbound_doc => l_clob,
1755 x_inbound_doc => NULL,
1756 x_send_date => NULL,
1757 x_ack_date => NULL,
1758 x_doc_status => 'R',
1759 x_doc_type => 'PELL',
1760 x_full_resp_code => NULL,
1761 x_mode => 'R');
1762 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1763 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' before submitting req ');
1764 END IF;
1765
1766 ln_request_id := apps.fnd_request.submit_request(
1767 'IGF','IGFGRJ14','','',FALSE,
1768 lv_document_id,CHR(0),
1769 '','','','','','','','',
1770 '','','','','','','','','','',
1771 '','','','','','','','','','',
1772 '','','','','','','','','','',
1773 '','','','','','','','','','',
1774 '','','','','','','','','','',
1775 '','','','','','','','','','',
1776 '','','','','','','','','','',
1777 '','','','','','','','','','',
1778 '','','','','','','','','','');
1779
1780 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1781 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug',' request id ' || ln_request_id);
1782 END IF;
1783
1784 IF ln_request_id = 0 THEN
1785 resultout := 'CONCURRENT_REQUEST_FAILED';
1786 ELSE
1787 resultout := 'SUCCESS';
1788 END IF; -- request failed
1789 END IF; -- doc id is null
1790 END IF; -- lob length
1791
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 resultout := 'E';
1795 wf_core.context ( 'IGF_GR_GEN_XML',
1796 'STORE_XML',
1797 itemtype,
1798 itemkey,
1799 to_char(actid),
1800 funcmode
1801 );
1802 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1803 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.store_xml.debug','sqlerrm ' || SQLERRM);
1804 END IF;
1805 END store_xml;
1806
1807 PROCEDURE update_status(p_document_id_txt VARCHAR2)
1808 IS
1809 /*************************************************************
1810 Created By : ugummall
1811 Date Created On : 2004/10/04
1812 Purpose :
1813 Know limitations, enhancements or remarks
1814 Change History
1815 Who When What
1816 (reverse chronological order - newest change first)
1817 ***************************************************************/
1818
1819 CURSOR cur_cod_orig ( cp_document_id_txt VARCHAR2) IS
1820 SELECT coddtls.origination_id
1821 FROM IGF_GR_COD_DTLS coddtls
1822 WHERE coddtls.document_id_txt = cp_document_id_txt;
1823
1824 CURSOR cur_cod_disb ( cp_document_id_txt VARCHAR2) IS
1825 SELECT disb.award_id, disb.disb_num, disb.disb_seq_num
1826 FROM IGF_AW_DB_COD_DTLS disb
1827 WHERE disb.document_id_txt = cp_document_id_txt;
1828
1829 CURSOR cur_rfms_orig ( cp_origination_id VARCHAR2) IS
1830 SELECT rfms.*
1831 FROM IGF_GR_RFMS rfms
1832 WHERE rfms.origination_id = cp_origination_id;
1833
1834 CURSOR cur_sys_disb ( cp_award_id NUMBER, cp_disb_num NUMBER, cp_disb_seq NUMBER) IS
1835 SELECT disb.rowid row_id,disb.*
1836 FROM IGF_AW_DB_CHG_DTLS disb
1837 WHERE disb.award_id = cp_award_id
1838 AND disb.disb_num = cp_disb_num
1839 AND disb.disb_seq_num = cp_disb_seq;
1840
1841 l_pell_amt igf_gr_rfms.pell_amount%TYPE;
1842 l_ft_pell_amt igf_gr_rfms_all.ft_pell_amount%TYPE;
1843 l_return_status VARCHAR2(1);
1844 l_return_mesg_text VARCHAR2(2000);
1845
1846 BEGIN
1847
1848 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1849 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.update_status.debug','First doc id ' || p_document_id_txt);
1850 END IF;
1851
1852 FOR cod_rec IN cur_cod_orig(p_document_id_txt)
1853 LOOP
1854 FOR rfms_rec IN cur_rfms_orig (cod_rec.origination_id)
1855 LOOP
1856 IF NVL(rfms_rec.orig_action_code,'*') = 'R' THEN
1857 rfms_rec.orig_action_code := 'S';
1858 END IF;
1859
1860 igf_gr_rfms_pkg.update_row ( x_rowid => rfms_rec.row_id,
1861 x_origination_id => rfms_rec.origination_id,
1862 x_ci_cal_type => rfms_rec.ci_cal_type,
1863 x_ci_sequence_number => rfms_rec.ci_sequence_number,
1864 x_base_id => rfms_rec.base_id,
1865 x_award_id => rfms_rec.award_id ,
1866 x_rfmb_id => rfms_rec.rfmb_id ,
1867 x_sys_orig_ssn => rfms_rec.sys_orig_ssn ,
1868 x_sys_orig_name_cd => rfms_rec.sys_orig_name_cd ,
1869 x_transaction_num => rfms_rec.transaction_num ,
1870 x_efc => rfms_rec.efc,
1871 x_ver_status_code => rfms_rec.ver_status_code ,
1872 x_secondary_efc => rfms_rec.secondary_efc ,
1873 x_secondary_efc_cd => rfms_rec.secondary_efc_cd ,
1874 x_pell_amount => rfms_rec.pell_amount ,
1875 x_pell_profile => rfms_rec.pell_profile ,
1876 x_enrollment_status => rfms_rec.enrollment_status ,
1877 x_enrollment_dt => rfms_rec.enrollment_dt ,
1878 x_coa_amount => rfms_rec.coa_amount ,
1879 x_academic_calendar => rfms_rec.academic_calendar ,
1880 x_payment_method => rfms_rec.payment_method ,
1881 x_total_pymt_prds => rfms_rec.total_pymt_prds ,
1882 x_incrcd_fed_pell_rcp_cd => rfms_rec.incrcd_fed_pell_rcp_cd ,
1883 x_attending_campus_id => rfms_rec.attending_campus_id ,
1884 x_est_disb_dt1 => rfms_rec.est_disb_dt1 ,
1885 x_orig_action_code => rfms_rec.orig_action_code ,
1886 x_orig_status_dt => rfms_rec.orig_status_dt ,
1887 x_orig_ed_use_flags => rfms_rec.orig_ed_use_flags ,
1888 x_ft_pell_amount => rfms_rec.ft_pell_amount ,
1889 x_prev_accpt_efc => rfms_rec.prev_accpt_efc ,
1890 x_prev_accpt_tran_no => rfms_rec.prev_accpt_tran_no ,
1891 x_prev_accpt_sec_efc_cd => rfms_rec.prev_accpt_sec_efc_cd ,
1892 x_prev_accpt_coa => rfms_rec.prev_accpt_coa ,
1893 x_orig_reject_code => rfms_rec.orig_reject_code ,
1894 x_wk_inst_time_calc_pymt => rfms_rec.wk_inst_time_calc_pymt ,
1895 x_wk_int_time_prg_def_yr => rfms_rec.wk_int_time_prg_def_yr ,
1896 x_cr_clk_hrs_prds_sch_yr => rfms_rec.cr_clk_hrs_prds_sch_yr ,
1897 x_cr_clk_hrs_acad_yr => rfms_rec.cr_clk_hrs_acad_yr ,
1898 x_inst_cross_ref_cd => rfms_rec.inst_cross_ref_cd ,
1899 x_low_tution_fee => rfms_rec.low_tution_fee ,
1900 x_rec_source => rfms_rec.rec_source ,
1901 x_pending_amount => rfms_rec.pending_amount ,
1902 x_mode => 'R' ,
1903 x_birth_dt => rfms_rec.birth_dt ,
1904 x_last_name => rfms_rec.last_name ,
1905 x_first_name => rfms_rec.first_name ,
1906 x_middle_name => rfms_rec.middle_name ,
1907 x_current_ssn => rfms_rec.current_ssn ,
1908 x_legacy_record_flag => rfms_rec.legacy_record_flag ,
1909 x_reporting_pell_cd => rfms_rec.rep_pell_id ,
1910 x_rep_entity_id_txt => rfms_rec.rep_entity_id_txt ,
1911 x_atd_entity_id_txt => rfms_rec.atd_entity_id_txt ,
1912 x_note_message => rfms_rec.note_message ,
1913 x_full_resp_code => rfms_rec.full_resp_code ,
1914 x_document_id_txt => p_document_id_txt
1915 );
1916 END LOOP;
1917 END LOOP;
1918
1919 FOR cod_rec IN cur_cod_disb(p_document_id_txt)
1920 LOOP
1921 FOR sys_rec IN cur_sys_disb(cod_rec.award_id,cod_rec.disb_num,cod_rec.disb_seq_num)
1922 LOOP
1923 sys_rec.disb_status := 'S';
1924 sys_rec.disb_status_date := TRUNC(SYSDATE);
1925
1926 igf_aw_db_chg_dtls_pkg.update_row ( x_rowid => sys_rec.row_id,
1927 x_award_id => sys_rec.award_id,
1928 x_disb_num => sys_rec.disb_num,
1929 x_disb_seq_num => sys_rec.disb_seq_num,
1930 x_disb_accepted_amt => sys_rec.disb_accepted_amt,
1931 x_orig_fee_amt => sys_rec.orig_fee_amt,
1932 x_disb_net_amt => sys_rec.disb_net_amt,
1933 x_disb_date => sys_rec.disb_date,
1934 x_disb_activity => sys_rec.disb_activity,
1935 x_disb_status => sys_rec.disb_status,
1936 x_disb_status_date => sys_rec.disb_status_date,
1937 x_disb_rel_flag => sys_rec.disb_rel_flag,
1938 x_first_disb_flag => sys_rec.first_disb_flag,
1939 x_interest_rebate_amt => sys_rec.interest_rebate_amt,
1940 x_disb_conf_flag => sys_rec.disb_conf_flag,
1941 x_pymnt_prd_start_date => sys_rec.pymnt_prd_start_date,
1942 x_note_message => sys_rec.note_message,
1943 x_batch_id_txt => sys_rec.batch_id_txt,
1944 x_ack_date => sys_rec.ack_date,
1945 x_booking_id_txt => sys_rec.booking_id_txt,
1946 x_booking_date => sys_rec.booking_date,
1947 x_mode => 'R'
1948 );
1949 END LOOP;
1950 END LOOP;
1951
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1955 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_gr_gen_xml.update_status.exception','Exception:'||SQLERRM);
1956 END IF;
1957 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1958 fnd_message.set_token('NAME','IGF_GR_GEN_XML.UPDATE_STATUS');
1959 igs_ge_msg_stack.add;
1960 app_exception.raise_exception;
1961 END update_status;
1962
1963 PROCEDURE print_xml ( errbuf OUT NOCOPY VARCHAR2,
1964 retcode OUT NOCOPY NUMBER,
1965 p_document_id VARCHAR2)
1966 IS
1967 /*************************************************************
1968 Created By : ugummall
1969 Date Created On : 2004/10/04
1970 Purpose :
1971 Know limitations, enhancements or remarks
1972 Change History
1973 Who When What
1974 (reverse chronological order - newest change first)
1975 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1976 ***************************************************************/
1977
1978 CURSOR c_get_parameters IS
1979 SELECT meaning, lookup_code
1980 FROM IGF_LOOKUPS_VIEW
1981 WHERE lookup_type = 'IGF_GE_PARAMETERS'
1982 AND lookup_code IN ('PARAMETER_PASS','DOCUMENT_ID');
1983 parameter_rec c_get_parameters%ROWTYPE;
1984
1985 lv_parameter_pass VARCHAR2(80);
1986 lv_document_id VARCHAR2(80);
1987 lc_newxmldoc CLOB;
1988 lv_rowid ROWID;
1989
1990 BEGIN
1991 --
1992 -- Steps
1993 --
1994 -- 1. Print parameters
1995 -- 2. Validate parameters
1996 -- 3. Edit CLOB for additional tags
1997 -- 4. Update DOC_DTLS table
1998 -- 5. Update LOR_LOC table, DISB table for Status
1999 -- 5. Print CLOB on the output file
2000 --
2001 igf_aw_gen.set_org_id(NULL);
2002 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2003 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.print_xml.debug','p doc id ' || p_document_id);
2004 END IF;
2005
2006 OPEN c_get_parameters;
2007 LOOP
2008 FETCH c_get_parameters INTO parameter_rec;
2009 EXIT WHEN c_get_parameters%NOTFOUND;
2010
2011 IF parameter_rec.lookup_code ='PARAMETER_PASS' THEN
2012 lv_parameter_pass := TRIM(parameter_rec.meaning);
2013 ELSIF parameter_rec.lookup_code ='DOCUMENT_ID' THEN
2014 lv_document_id := TRIM(parameter_rec.meaning);
2015 END IF;
2016 END LOOP;
2017 CLOSE c_get_parameters;
2018
2019 fnd_file.new_line(fnd_file.log,1);
2020 fnd_file.put_line(fnd_file.log, lv_parameter_pass); --------------Parameters Passed--------------
2021 fnd_file.new_line(fnd_file.log,1);
2022
2023 fnd_file.put_line(fnd_file.log, RPAD(lv_document_id,40) || ' : '|| p_document_id);
2024
2025 fnd_file.new_line(fnd_file.log,1);
2026 fnd_file.put_line(fnd_file.log, '--------------------------------------------------------');
2027 fnd_file.new_line(fnd_file.log,1);
2028
2029 igf_sl_dl_gen_xml.edit_clob(p_document_id,lc_newxmldoc,lv_rowid);
2030
2031 -- update the modified clob in DOC_DTLS table
2032 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2033 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.print_xml.debug','before updating status');
2034 END IF;
2035 igf_sl_cod_doc_dtls_pkg.update_row ( x_rowid => lv_rowid,
2036 x_document_id_txt => p_document_id,
2037 x_outbound_doc => lc_newxmldoc,
2038 x_inbound_doc => NULL,
2039 x_send_date => TRUNC(SYSDATE),
2040 x_ack_date => NULL,
2041 x_doc_status => 'S',
2042 x_doc_type => 'PELL',
2043 x_full_resp_code => NULL,
2044 x_mode => 'R');
2045 --
2046 -- update IGF_GR_RFMS_ALL.ORIG_ACTION_CODE to sent
2047 -- update IGF_AW_DB_CHG_DTLS.DISB_STATUS to sent
2048 update_status(p_document_id);
2049
2050 -- print out xml outfile
2051 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2052 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.print_xml.debug','before calling print_out_xml method');
2053 END IF;
2054 igf_sl_dl_gen_xml.print_out_xml(lc_newxmldoc);
2055
2056 COMMIT;
2057 EXCEPTION
2058 WHEN OTHERS THEN
2059 ROLLBACK;
2060 retcode := 2;
2061 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2062 fnd_file.put_line(fnd_file.log,SQLERRM);
2063 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2064 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_gr_gen_xml.print_xml.debug','OTHERS exception raised in print_xml: ' || SQLERRM);
2065 END IF;
2066 igs_ge_msg_stack.conc_exception_hndl;
2067 END print_xml;
2068
2069 END IGF_GR_GEN_XML;