[Home] [Help]
1: PACKAGE BODY igf_ap_isir_gen_pkg AS
2: /* $Header: IGFAP47B.pls 120.5 2006/02/23 01:38:51 rajagupt noship $ */
3: ------------------------------------------------------------------
4: --Created by : ugummall, Oracle India
5: --Date created: 04-AUG-2004
49:
50: EXCEPTION
51: WHEN OTHERS THEN
52: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
53: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.attach_isir');
54: cp_message_out:= fnd_message.get;
55: END attach_isir;
56:
57: FUNCTION can_unlock_isir(
92:
93: BEGIN
94:
95: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
96: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','starting can_unlock_isir with p_base_id : ' || p_base_id ||' p_user_id : '|| p_user_id);
97: END IF;
98:
99: -- if base id or user id is null then return 'N'
100: IF (p_base_id IS NULL OR p_user_id IS NULL) THEN
101: RETURN 'N';
102: END IF;
103:
104: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
105: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','Before opening cursor cur_is_user_locked_isir ');
106: END IF;
107:
108: -- check if the ISIR is locked by the user
109: lv_dummy := null;
115: RETURN 'Y';
116: END IF;
117:
118: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
119: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.debug','Before opening cursor cur_is_user_sysadmin ');
120: END IF;
121:
122: -- ISIR is not locked by user. Check if user is system administrator
123: lv_dummy := null;
135:
136: EXCEPTION
137: WHEN OTHERS THEN
138: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
139: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.exception','The exception is : ' || SQLERRM );
140: END IF;
141: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
142: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.can_unlock_isir');
143: fnd_file.put_line(fnd_file.log,fnd_message.get);
138: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
139: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.can_unlock_isir.exception','The exception is : ' || SQLERRM );
140: END IF;
141: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
142: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.can_unlock_isir');
143: fnd_file.put_line(fnd_file.log,fnd_message.get);
144: igs_ge_msg_stack.add;
145: RETURN 'N';
146:
174:
175: BEGIN
176:
177: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
178: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','starting update_lock_status with p_base_id : ' || p_base_id ||' p_user_id : '|| p_user_id);
179: END IF;
180:
181: -- if p_base_id is null, return 'N'
182: IF (p_base_id IS NULL) THEN
183: RETURN 'N';
184: END IF;
185:
186: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
187: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','Before opening cursor cur_fa_base ');
188: END IF;
189:
190: OPEN cur_fa_base(p_base_id);
191: FETCH cur_fa_base INTO rec_fa_base;
195: END IF;
196: CLOSE cur_fa_base;
197:
198: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
199: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.update_lock_status.debug','Calling igf_ap_fa_base_rec_pkg.update_row for baseid : '||rec_fa_base.base_id);
200: END IF;
201:
202: -- if p_user_id is null, unlock the ISIR., update fa base record with ISIR lock status (ISIR_LOCKED_BY ) to null
203: -- If NOT NULL, update fa base record with ISIR lock status (ISIR_LOCKED_BY) to p_user_id
350:
351: BEGIN
352:
353: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
354: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.debug','starting is_awarding_pymnt_isir_exists with p_base_id : ' || p_base_id );
355: END IF;
356:
357: -- validate the input parameters
358: IF (p_base_id IS NULL) THEN
359: RETURN 'N';
360: END IF;
361:
362: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
363: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.debug','before opening cursor cur_isir ');
364: END IF;
365:
366: -- check wether the student is having any outstanding corrections or not.
367: lv_dummy := NULL;
377:
378: EXCEPTION
379: WHEN OTHERS THEN
380: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
381: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.exception','The exception is : ' || SQLERRM );
382: END IF;
383: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
384: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists');
385: fnd_file.put_line(fnd_file.log,fnd_message.get);
380: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
381: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists.exception','The exception is : ' || SQLERRM );
382: END IF;
383: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
384: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awarding_pymnt_isir_exists');
385: fnd_file.put_line(fnd_file.log,fnd_message.get);
386: igs_ge_msg_stack.add;
387: RETURN 'N';
388:
417:
418: BEGIN
419:
420: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
421: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.debug','starting are_corrections_exists with p_base_id : ' || p_base_id);
422: END IF;
423:
424: -- validate the input parameters
425: IF (p_base_id IS NULL) THEN
426: RETURN 'N';
427: END IF;
428:
429: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
430: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.debug','Before opening cursor cur_corrections ');
431: END IF;
432:
433: -- check wether the student is having any outstanding corrections or not.
434: lv_dummy := NULL;
444:
445: EXCEPTION
446: WHEN OTHERS THEN
447: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
448: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.exception','The exception is : ' || SQLERRM );
449: END IF;
450: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
451: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.are_corrections_exists');
452: fnd_file.put_line(fnd_file.log,fnd_message.get);
447: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
448: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.are_corrections_exists.exception','The exception is : ' || SQLERRM );
449: END IF;
450: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
451: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.are_corrections_exists');
452: fnd_file.put_line(fnd_file.log,fnd_message.get);
453: igs_ge_msg_stack.add;
454: RETURN 'N';
455:
496:
497: BEGIN
498:
499: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
500: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','starting delete_isir_validations with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
501: END IF;
502:
503: SAVEPOINT IGFAP47_DELETE_ISIR_VAL;
504: FND_MSG_PUB.initialize;
504: FND_MSG_PUB.initialize;
505: x_return_status := FND_API.G_RET_STS_SUCCESS;
506:
507: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
508: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','Before opening cursor cur_stud_non_simulated_awards ');
509: END IF;
510:
511: -- check if non-simulated awards exists for the student.
512: lv_dummy := null;
519: RAISE RETURN_EXP;
520: END IF;
521:
522: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
523: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir_validations.debug','Before opening cursor cur_stud_correction_isir ');
524: END IF;
525:
526: -- check if internal created correction ISIR exists for the student.
527: lv_dummy := null;
553:
554: ROLLBACK TO IGFAP47_DELETE_ISIR_VAL;
555:
556: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
557: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
558: END IF;
559: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
560: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
561:
556: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
557: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
558: END IF;
559: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
560: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
561:
562: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
563: l_err_msg := fnd_message.get||SQLERRM;
564: fnd_msg_pub.count_and_get(
736:
737: BEGIN
738:
739: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
740: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','starting delete_isir with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
741: END IF;
742:
743:
744: SAVEPOINT IGFAP47_DELETE_ISIR;
746: x_return_status := FND_API.G_RET_STS_SUCCESS;
747: l_isir_data_from_mtch_tbl := FALSE;
748:
749: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
750: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_get_rowid_isir ');
751: END IF;
752:
753: -- Check whether the ISIR is still present in the ISIR Interface table, If so, change the status to UNMATCHED and delete the ISIR AND NSLDS
754: -- If not present then Create the ISIR record from the ISIR and NSLDS data and delete the ISIR and NSLDS data from the matched tables.
763: RAISE RETURN_EXP ;
764:
765: ELSE
766: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
767: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_chk_isir_in_ints ');
768: END IF;
769:
770: rec_chk_isir_in_ints := NULL;
771: OPEN cur_chk_isir_in_ints( rec_get_rowid_isir.batch_year, rec_get_rowid_isir.transaction_num, rec_get_rowid_isir.original_ssn, rec_get_rowid_isir.orig_name_id );
776: IF rec_chk_isir_in_ints.si_id IS NOT NULL THEN
777:
778:
779: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
780: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before calling UPDATE igf_ap_isir_ints_all for si_id : '|| rec_chk_isir_in_ints.si_id);
781: END IF;
782:
783: UPDATE igf_ap_isir_ints_all
784: SET record_status = 'UNMATCHED',
792: ELSE
793:
794: -- If the ISIR data is not present in the ISIR Interface table, then insert the data from the ISIR Matched table and NSLDS data table.
795: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
796: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening cursor cur_isir_int and insert row into igf_ap_isir_ints_all');
797: END IF;
798:
799:
800: OPEN cur_isir_int(p_isir_id);
1781: SYSDATE
1782: );
1783:
1784: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1785: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','After insert row into igf_ap_isir_ints ');
1786: END IF;
1787: l_isir_data_from_mtch_tbl := TRUE;
1788:
1789: END IF;
1792:
1793: IF l_isir_data_from_mtch_tbl THEN
1794:
1795: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1796: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.debug','Before opening the cursor cur_get_rowid_nslds ');
1797: END IF;
1798:
1799: -- Delete the NSLDS Data
1800: rec_get_rowid_nslds := null;
1868:
1869: ROLLBACK TO IGFAP47_DELETE_ISIR;
1870:
1871: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1872: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
1873: END IF;
1874: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1875: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
1876:
1871: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1872: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_isir.exception','The exception is : ' || SQLERRM );
1873: END IF;
1874: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1875: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_isir');
1876:
1877: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1878: l_err_msg := fnd_message.get||SQLERRM;
1879: fnd_msg_pub.count_and_get(
1920:
1921: BEGIN
1922:
1923: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1924: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.debug','starting delete_person_match with p_si_id : ' || p_si_id);
1925: END IF;
1926:
1927: -- Get APM_ID from SI_ID
1928: rec_get_person_match := null;
1930: FETCH cur_get_person_match INTO rec_get_person_match;
1931: CLOSE cur_get_person_match;
1932:
1933: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1934: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.debug','Before for-loop rec_get_match_detail ');
1935: END IF;
1936:
1937: -- Delete match detail records (child records)
1938: FOR rec_get_match_detail IN cur_get_match_detail(rec_get_person_match.apm_id) LOOP
1944:
1945: EXCEPTION
1946: WHEN OTHERS THEN
1947: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1948: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.exception','The exception is : ' || SQLERRM );
1949: END IF;
1950: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1951: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_person_match');
1952: fnd_file.put_line(fnd_file.log,fnd_message.get);
1947: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1948: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_person_match.exception','The exception is : ' || SQLERRM );
1949: END IF;
1950: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
1951: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_person_match');
1952: fnd_file.put_line(fnd_file.log,fnd_message.get);
1953: igs_ge_msg_stack.add;
1954: END delete_person_match;
1955:
1980:
1981: BEGIN
1982:
1983: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1984: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_record.debug','starting delete_interface_record with p_si_id : ' || p_si_id);
1985: END IF;
1986:
1987: -- get row id of the ISIR interface record to be deleted.
1988: OPEN cur_get_rowid_interface(p_si_id);
1994: END IF;
1995: CLOSE cur_get_rowid_interface;
1996:
1997: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1998: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_record.debug','Before deleting record from isir_ints table and person match table');
1999: END IF;
2000:
2001: -- delete the interface record.
2002: --igf_ap_isir_intrface_pkg.delete_row(rec_get_rowid_interface.row_id);
2009:
2010: EXCEPTION
2011: WHEN OTHERS THEN
2012: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2013: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_person_match.exception','The exception is : ' || SQLERRM );
2014: END IF;
2015: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2016: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_interface_person_match');
2017: fnd_file.put_line(fnd_file.log,fnd_message.get);
2012: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2013: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.delete_interface_person_match.exception','The exception is : ' || SQLERRM );
2014: END IF;
2015: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2016: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.delete_interface_person_match');
2017: fnd_file.put_line(fnd_file.log,fnd_message.get);
2018: igs_ge_msg_stack.add;
2019: lv_status := 'E';
2020: END delete_interface_record;
2042:
2043: BEGIN
2044:
2045: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2046: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_int_records.debug','starting delete_int_records with p_si_ids : ' || p_si_ids);
2047: END IF;
2048:
2049: l_si_ids := p_si_ids;
2050:
2060: l_del_si_id := l_si_id;
2061: END IF;
2062:
2063: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2064: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.delete_int_records.debug','Calling delete_interface_record with the l_del_si_id '|| l_del_si_id);
2065: END IF;
2066:
2067: delete_interface_record( p_si_id => l_del_si_id, lv_status => lv_status);
2068:
2106:
2107: BEGIN
2108:
2109: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2110: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_isir_exists.debug','starting is_isir_exists with p_si_id : ' || p_si_id ||' p_batch_year : '|| p_batch_year);
2111: END IF;
2112:
2113: -- Checks whether student has any ISIR present in ISIR Matched table with the primary attributes of the student ISIR
2114: OPEN cur_isir_exists(p_si_id,p_batch_year);
2150: BEGIN
2151:
2152:
2153: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2154: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.is_awards_exists.debug','starting is_awards_exists with p_base_id : ' || p_base_id);
2155: END IF;
2156:
2157: -- validate the input parameters
2158: IF p_base_id IS NULL THEN
2173:
2174: EXCEPTION
2175: WHEN OTHERS THEN
2176: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2177: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awards_exists.exception','The exception is : ' || SQLERRM );
2178: END IF;
2179: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2180: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awards_exists');
2181: fnd_file.put_line(fnd_file.log,fnd_message.get);
2176: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2177: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.is_awards_exists.exception','The exception is : ' || SQLERRM );
2178: END IF;
2179: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2180: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.is_awards_exists');
2181: fnd_file.put_line(fnd_file.log,fnd_message.get);
2182: igs_ge_msg_stack.add;
2183: RETURN 'N';
2184: END is_awards_exists;
2216:
2217: BEGIN
2218:
2219: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2220: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.debug','starting chk_pell_orig with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
2221: END IF;
2222:
2223: -- validate the input parameters
2224: IF (p_base_id IS NULL OR p_isir_id IS NULL) THEN
2225: RETURN 'N';
2226: END IF;
2227:
2228: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2229: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.debug','Before calling cur_pell_orig_chk ');
2230: END IF;
2231:
2232: -- if the PELL origination isir is different from the selected ISIR then return 'P'
2233: lv_pell_orig_rec := null;
2250:
2251: EXCEPTION
2252: WHEN OTHERS THEN
2253: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2254: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.exception','The exception is : ' || SQLERRM );
2255: END IF;
2256: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2257: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.chk_pell_orig');
2258: fnd_file.put_line(fnd_file.log,fnd_message.get);
2253: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2254: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.chk_pell_orig.exception','The exception is : ' || SQLERRM );
2255: END IF;
2256: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2257: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.chk_pell_orig');
2258: fnd_file.put_line(fnd_file.log,fnd_message.get);
2259: igs_ge_msg_stack.add;
2260: RETURN 'N';
2261: END chk_pell_orig;
2288:
2289: BEGIN
2290:
2291: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2292: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.debug','starting make_awarding_isir with p_base_id : ' || p_base_id ||' p_isir_id : '|| p_isir_id);
2293: END IF;
2294:
2295: -- validate the input parameters
2296: IF (p_base_id IS NULL OR p_isir_id IS NULL) THEN
2297: RETURN 'N';
2298: END IF;
2299:
2300: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2301: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.debug','Looping the cursor cur_isir_matched');
2302: END IF;
2303:
2304: -- if no errors, then make the current ISIR as both Awarding and Payment
2305: -- then set status to N for the existing payment ISIR and Awarding ISIRs.
2323:
2324: EXCEPTION
2325: WHEN OTHERS THEN
2326: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2327: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.exception','The exception is : ' || SQLERRM );
2328: END IF;
2329: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2330: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.make_awarding_isir');
2331: fnd_file.put_line(fnd_file.log,fnd_message.get);
2326: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2327: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.make_awarding_isir.exception','The exception is : ' || SQLERRM );
2328: END IF;
2329: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2330: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.make_awarding_isir');
2331: fnd_file.put_line(fnd_file.log,fnd_message.get);
2332: igs_ge_msg_stack.add;
2333: RETURN 'N';
2334: END make_awarding_isir;
2365:
2366: BEGIN
2367:
2368: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2369: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.get_isir_message_class.debug','starting get_isir_message_class with p_message_class : ' || p_message_class);
2370: END IF;
2371:
2372: -- if the message class is NULL then return NULL
2373: -- else get the ISIR type using the tag provided at the lookup code.
2375: RETURN NULL;
2376: END IF;
2377:
2378: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2379: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.get_isir_message_class.debug','Before opening the cursor get_message_class');
2380: END IF;
2381:
2382: get_message_class_rec := NULL;
2383: OPEN get_message_class(p_message_class);
2483:
2484: IF p_new_active_isir_id IS NULL THEN
2485:
2486: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2487: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','One of the input parameter is null');
2488: END IF;
2489:
2490: p_anticip_status := 'ERROR';
2491: p_awd_prc_status := 'ERROR';
2509: IF c_ant_data%FOUND THEN
2510: CLOSE c_ant_data;
2511:
2512: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2513: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Anticipated data was found, hence updating');
2514: END IF;
2515:
2516: -- Derive the Attendance Type value and compare the the anticipated value.
2517: lc_attendance_type := NULL;
2524: END IF;
2525: CLOSE c_attendance_type;
2526:
2527: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2528: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the attendance Type : '||lc_attendance_type.attendance_type);
2529: END IF;
2530:
2531: -- Get the Class standing using the ISIR grde level
2532: lc_class_standing := NULL;
2539: END IF;
2540: CLOSE c_class_standing;
2541:
2542: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2543: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the Class standing : '||lc_class_standing.class_standing);
2544: END IF;
2545:
2546: -- Get the Attendance Mode based on the housing plan codes and federal school code.
2547: lc_attendance_mode := NULL;
2565: END IF;
2566: CLOSE c_attendance_mode;
2567:
2568: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2569: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Derived the housing status : '||lc_attendance_mode.housing_stat_code);
2570: END IF;
2571:
2572: IF lc_class_standing.class_standing IS NOT NULL OR
2573: lc_attendance_type.attendance_type IS NOT NULL OR
2588: lfc_ant_data.housing_status_code := lc_attendance_mode.housing_stat_code;
2589: END IF;
2590:
2591: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2592: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Updating Anticipated data for base_id : '|| lfc_ant_data.base_id);
2593: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug',' ld_cal_type : '|| lfc_ant_data.ld_cal_type ||' Ld_seq_num : '||lfc_ant_data.ld_sequence_number);
2594: END IF;
2595:
2596: igf_ap_fa_ant_data_pkg.update_row(
2589: END IF;
2590:
2591: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2592: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Updating Anticipated data for base_id : '|| lfc_ant_data.base_id);
2593: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug',' ld_cal_type : '|| lfc_ant_data.ld_cal_type ||' Ld_seq_num : '||lfc_ant_data.ld_sequence_number);
2594: END IF;
2595:
2596: igf_ap_fa_ant_data_pkg.update_row(
2597: x_rowid => lfc_ant_data.row_id,
2629: -- Check for the EFC values for everymonth and if not same then update the award process status of the awards
2630: IF p_old_active_isir_id IS NOT NULL THEN
2631:
2632: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2633: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','Old Active ISIR is present : '||p_old_active_isir_id);
2634: END IF;
2635:
2636: old_active_isir := NULL;
2637: OPEN cur_isir_data(p_old_active_isir_id);
2652: NVL(old_active_isir.primary_alternate_month_12,-1) <> NVL(new_active_isir.primary_alternate_month_12,-1)
2653: ) THEN
2654:
2655: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2656: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','EFC values were different, hence upadate the Awd process status');
2657: END IF;
2658:
2659: p_awd_prc_status := NULL;
2660: p_awd_prc_status := igf_aw_coa_gen.set_awd_proc_status( p_base_id => new_active_isir.base_id,
2660: p_awd_prc_status := igf_aw_coa_gen.set_awd_proc_status( p_base_id => new_active_isir.base_id,
2661: p_award_prd_code => NULL);
2662:
2663: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2664: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug','after updating Awd process status : '||p_awd_prc_status);
2665: END IF;
2666:
2667: IF p_awd_prc_status IS NOT NULL THEN
2668: fnd_message.set_name('IGF','IGF_AW_AWD_PRC_STATUS_CHNGED');
2673: END IF;
2674: END IF;
2675:
2676: IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2677: fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.debug',' -- Successfull returning with p_anticip_status : '||p_anticip_status ||' p_awd_prc_status : '||p_awd_prc_status);
2678: END IF;
2679:
2680: RETURN;
2681:
2681:
2682: EXCEPTION
2683: WHEN others THEN
2684: IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2685: fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status.exception','The exception is : ' || SQLERRM );
2686: END IF;
2687: p_anticip_status := 'ERROR';
2688: p_awd_prc_status := 'ERROR';
2689: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2686: END IF;
2687: p_anticip_status := 'ERROR';
2688: p_awd_prc_status := 'ERROR';
2689: fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
2690: fnd_message.set_token('NAME','igf_ap_isir_gen_pkg.upd_anticip_data_awd_prc_status'||SQLERRM);
2691: fnd_file.put_line(fnd_file.log,fnd_message.get);
2692: igs_ge_msg_stack.add;
2693: END upd_ant_data_awd_prc_status;
2694:
2691: fnd_file.put_line(fnd_file.log,fnd_message.get);
2692: igs_ge_msg_stack.add;
2693: END upd_ant_data_awd_prc_status;
2694:
2695: END igf_ap_isir_gen_pkg;