[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_SS_PKG
Source
1 PACKAGE BODY IGF_AP_SS_PKG AS
2 /* $Header: IGFAP30B.pls 120.4 2006/04/19 04:22:53 azmohamm ship $ */
3
4 PROCEDURE set_internal_isir (
5 p_isir_id IN IGF_AP_ISIR_MATCHED_ALL.ISIR_ID%TYPE,
6 p_ret_isir_id IN OUT NOCOPY IGF_AP_ISIR_MATCHED_ALL.ISIR_ID%TYPE
7 )IS
8 /*
9 || Created By : cdcruz
10 || Created On : 27-NOV-2002
11 || Purpose : To be used for all self service wrappers
12 || Known limitations, enhancements or remarks :
13 || Change History :
14 || Who When What
15 || azmohamm 19-Apr-2006 4950206: Copied the verification item values
16 || to the internal ISIR record
17 || bkkumar 15-Dec-2003 2826844: Added UPDATE NOWAIT to the cursor
18 || chk_internal so that it will throw exception
19 || if the row is locked and also it will return
20 || p_ret_isir_id = -2 . Also after creating the
21 || internal record it will commit and also obtain
22 || lock on it so that no one else can update the same
23 || record.
24 || asbala 19-nov-2003 3026594: FA128 - ISIR Federal Updates 04- 05,
25 || modified signature of igf_ap_isir_matched_pkg
26 || (reverse chronological order - newest change first)
27 */
28
29 CURSOR c_isir (l_isir_id igf_ap_isir_matched_all.isir_id%type)
30 IS
31 SELECT ISIR.*
32 FROM IGF_AP_ISIR_MATCHED ISIR
33 WHERE ISIR.ISIR_ID = l_isir_id ;
34
35 lv_isir c_isir%rowtype ;
36
37 CURSOR chk_internal (l_base_id igf_ap_isir_matched_all.base_id%type)
38 IS
39 SELECT isir.rowid row_id,isir.isir_id FROM IGF_AP_ISIR_MATCHED_ALL ISIR
40 WHERE
41 ISIR.BASE_ID = l_base_id AND
42 NVL(ISIR.SYSTEM_RECORD_TYPE,'X') = 'INTERNAL'
43 FOR UPDATE NOWAIT;
44
45 lv_chk_internal chk_internal%rowtype ;
46 lv_rowid VARCHAR2(30) ;
47
48 CURSOR chk_row_id
49 IS
50 SELECT isir.rowid row_id
51 FROM IGF_AP_ISIR_MATCHED_ALL ISIR
52 WHERE
53 ISIR.rowid = lv_rowid
54 FOR UPDATE NOWAIT;
55
56 CURSOR cur_get_ver_data (pn_base_id number) is
57 SELECT lkup.lookup_code col_name,
58 verf.item_value col_val
59 FROM igf_ap_inst_ver_item verf ,
60 igf_ap_fa_base_rec_all fabase ,
61 igf_ap_batch_aw_map map ,
62 igf_fc_sar_cd_mst sar ,
63 igf_lookups_view lkup
64 WHERE fabase.base_id = verf.base_id
65 AND verf.udf_vern_item_seq_num = 1
66 AND map.ci_cal_type = fabase.ci_cal_type
67 AND map.ci_sequence_number = fabase.ci_sequence_number
68 AND sar.sys_award_year = map.sys_award_year
69 AND sar.sar_field_number = verf.isir_map_col
70 AND lkup.lookup_type = 'IGF_AP_SAR_FIELD_MAP'
71 AND lkup.lookup_code = sar.sar_field_name
72 AND NVL(verf.waive_flag,'N') = 'N'
73 AND ((verf.item_value IS NOT NULL) OR (verf.item_value IS NULL AND verf.use_blank_flag = 'Y'))
74 AND verf.base_id = pn_base_id ;
75
76 pn_isir_id igf_ap_isir_matched_all.isir_id%type ;
77 pn_base_id igf_ap_isir_matched_all.base_id%type;
78
79 l_get_ver_data_rec cur_get_ver_data%ROWTYPE;
80
81 BEGIN
82
83 lv_rowid := null;
84
85 OPEN c_isir(p_isir_id) ;
86 FETCH c_isir INTO lv_isir ;
87 IF c_isir%FOUND THEN
88 -- Initialize the package variable with the isir rec values
89 igf_ap_batch_ver_prc_pkg.lp_isir_rec := lv_isir;
90 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
91 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','fetched ISIR Detials for ISIR ID: '||lv_isir.isir_id);
92 END IF;
93
94 OPEN cur_get_ver_data ( lv_isir.base_id ) ;
95 LOOP
96 FETCH cur_get_ver_data INTO l_get_ver_data_rec ;
97 EXIT WHEN cur_get_ver_data%NOTFOUND;
98
99 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
100 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','fetching verification items Item Name: '||l_get_ver_data_rec.col_name||' value:'||l_get_ver_data_rec.col_val);
101 END IF;
102
103 EXECUTE IMMEDIATE 'BEGIN igf_ap_batch_ver_prc_pkg.lp_isir_rec.'
104 || l_get_ver_data_rec.col_name || ' := ' || '''' || l_get_ver_data_rec.col_val || '''' || ' ; END;' ;
105 END LOOP;
106 CLOSE cur_get_ver_data ;
107
108 lv_isir := igf_ap_batch_ver_prc_pkg.lp_isir_rec;
109
110 -- Check if the student has an Internal ISIR
111
112 OPEN chk_internal(lv_isir.base_id);
113 FETCH chk_internal into lv_chk_internal ;
114 IF chk_internal%FOUND THEN
115
116 p_ret_isir_id := lv_chk_internal.isir_id ;
117
118 igf_ap_isir_matched_pkg.update_row(
119 x_Mode => 'R',
120 x_rowid => lv_chk_internal.row_id,
121 x_isir_id => lv_chk_internal.isir_id,
122 x_base_id => lv_isir.base_id,
123 x_batch_year => lv_isir.batch_year ,
124 x_transaction_num => lv_isir.transaction_num ,
125 x_current_ssn => lv_isir.current_ssn ,
126 x_ssn_name_change => lv_isir.ssn_name_change ,
127 x_original_ssn => lv_isir.original_ssn ,
128 x_orig_name_id => lv_isir.orig_name_id ,
129 x_last_name => lv_isir.last_name ,
130 x_first_name => lv_isir.first_name ,
131 x_middle_initial => lv_isir.middle_initial ,
132 x_perm_mail_add => lv_isir.perm_mail_add ,
133 x_perm_city => lv_isir.perm_city ,
134 x_perm_state => lv_isir.perm_state ,
135 x_perm_zip_code => lv_isir.perm_zip_code ,
136 x_date_of_birth => lv_isir.date_of_birth ,
137 x_phone_number => lv_isir.phone_number ,
138 x_driver_license_number => lv_isir.driver_license_number ,
139 x_driver_license_state => lv_isir.driver_license_state ,
140 x_citizenship_status => lv_isir.citizenship_status ,
141 x_alien_reg_number => lv_isir.alien_reg_number ,
142 x_s_marital_status => lv_isir.s_marital_status ,
143 x_s_marital_status_date => lv_isir.s_marital_status_date ,
144 x_summ_enrl_status => lv_isir.summ_enrl_status ,
145 x_fall_enrl_status => lv_isir.fall_enrl_status ,
146 x_winter_enrl_status => lv_isir.winter_enrl_status ,
147 x_spring_enrl_status => lv_isir.spring_enrl_status ,
148 x_summ2_enrl_status => lv_isir.summ2_enrl_status ,
149 x_fathers_highest_edu_level => lv_isir.fathers_highest_edu_level ,
150 x_mothers_highest_edu_level => lv_isir.mothers_highest_edu_level ,
151 x_s_state_legal_residence => lv_isir.s_state_legal_residence ,
152 x_legal_residence_before_date => lv_isir.legal_residence_before_date ,
153 x_s_legal_resd_date => lv_isir.s_legal_resd_date ,
154 x_ss_r_u_male => lv_isir.ss_r_u_male ,
155 x_selective_service_reg => lv_isir.selective_service_reg ,
156 x_degree_certification => lv_isir.degree_certification ,
157 x_grade_level_in_college => lv_isir.grade_level_in_college ,
158 x_high_school_diploma_ged => lv_isir.high_school_diploma_ged ,
159 x_first_bachelor_deg_by_date => lv_isir.first_bachelor_deg_by_date ,
160 x_interest_in_loan => lv_isir.interest_in_loan ,
161 x_interest_in_stud_employment => lv_isir.interest_in_stud_employment ,
162 x_drug_offence_conviction => lv_isir.drug_offence_conviction ,
163 x_s_tax_return_status => lv_isir.s_tax_return_status ,
164 x_s_type_tax_return => lv_isir.s_type_tax_return ,
165 x_s_elig_1040ez => lv_isir.s_elig_1040ez ,
166 x_s_adjusted_gross_income => lv_isir.s_adjusted_gross_income ,
167 x_s_fed_taxes_paid => lv_isir.s_fed_taxes_paid ,
168 x_s_exemptions => lv_isir.s_exemptions ,
169 x_s_income_from_work => lv_isir.s_income_from_work ,
170 x_spouse_income_from_work => lv_isir.spouse_income_from_work ,
171 x_s_toa_amt_from_wsa => lv_isir.s_toa_amt_from_wsa ,
172 x_s_toa_amt_from_wsb => lv_isir.s_toa_amt_from_wsb ,
173 x_s_toa_amt_from_wsc => lv_isir.s_toa_amt_from_wsc ,
174 x_s_investment_networth => lv_isir.s_investment_networth ,
175 x_s_busi_farm_networth => lv_isir.s_busi_farm_networth ,
176 x_s_cash_savings => lv_isir.s_cash_savings ,
177 x_va_months => lv_isir.va_months ,
178 x_va_amount => lv_isir.va_amount ,
179 x_stud_dob_before_date => lv_isir.stud_dob_before_date ,
180 x_deg_beyond_bachelor => lv_isir.deg_beyond_bachelor ,
181 x_s_married => lv_isir.s_married ,
182 x_s_have_children => lv_isir.s_have_children ,
183 x_legal_dependents => lv_isir.legal_dependents ,
184 x_orphan_ward_of_court => lv_isir.orphan_ward_of_court ,
185 x_s_veteran => lv_isir.s_veteran ,
186 x_p_marital_status => lv_isir.p_marital_status ,
187 x_father_ssn => lv_isir.father_ssn ,
188 x_f_last_name => lv_isir.f_last_name ,
189 x_mother_ssn => lv_isir.mother_ssn ,
190 x_m_last_name => lv_isir.m_last_name ,
191 x_p_num_family_member => lv_isir.p_num_family_member ,
192 x_p_num_in_college => lv_isir.p_num_in_college ,
193 x_p_state_legal_residence => lv_isir.p_state_legal_residence ,
194 x_p_state_legal_res_before_dt => lv_isir.p_state_legal_res_before_dt ,
195 x_p_legal_res_date => lv_isir.p_legal_res_date ,
196 x_age_older_parent => lv_isir.age_older_parent ,
197 x_p_tax_return_status => lv_isir.p_tax_return_status ,
198 x_p_type_tax_return => lv_isir.p_type_tax_return ,
199 x_p_elig_1040aez => lv_isir.p_elig_1040aez ,
200 x_p_adjusted_gross_income => lv_isir.p_adjusted_gross_income ,
201 x_p_taxes_paid => lv_isir.p_taxes_paid ,
202 x_p_exemptions => lv_isir.p_exemptions ,
203 x_f_income_work => lv_isir.f_income_work ,
204 x_m_income_work => lv_isir.m_income_work ,
205 x_p_income_wsa => lv_isir.p_income_wsa ,
206 x_p_income_wsb => lv_isir.p_income_wsb ,
207 x_p_income_wsc => lv_isir.p_income_wsc ,
208 x_p_investment_networth => lv_isir.p_investment_networth ,
209 x_p_business_networth => lv_isir.p_business_networth ,
210 x_p_cash_saving => lv_isir.p_cash_saving ,
211 x_s_num_family_members => lv_isir.s_num_family_members ,
212 x_s_num_in_college => lv_isir.s_num_in_college ,
213 x_first_college => lv_isir.first_college ,
214 x_first_house_plan => lv_isir.first_house_plan ,
215 x_second_college => lv_isir.second_college ,
216 x_second_house_plan => lv_isir.second_house_plan ,
217 x_third_college => lv_isir.third_college ,
218 x_third_house_plan => lv_isir.third_house_plan ,
219 x_fourth_college => lv_isir.fourth_college ,
220 x_fourth_house_plan => lv_isir.fourth_house_plan ,
221 x_fifth_college => lv_isir.fifth_college ,
222 x_fifth_house_plan => lv_isir.fifth_house_plan ,
223 x_sixth_college => lv_isir.sixth_college ,
224 x_sixth_house_plan => lv_isir.sixth_house_plan ,
225 x_date_app_completed => lv_isir.date_app_completed ,
226 x_signed_by => lv_isir.signed_by ,
227 x_preparer_ssn => lv_isir.preparer_ssn ,
228 x_preparer_emp_id_number => lv_isir.preparer_emp_id_number ,
229 x_preparer_sign => lv_isir.preparer_sign ,
230 x_transaction_receipt_date => lv_isir.transaction_receipt_date ,
231 x_dependency_override_ind => lv_isir.dependency_override_ind ,
232 x_faa_fedral_schl_code => lv_isir.faa_fedral_schl_code ,
233 x_faa_adjustment => lv_isir.faa_adjustment ,
234 x_input_record_type => lv_isir.input_record_type ,
235 x_serial_number => lv_isir.serial_number ,
236 x_batch_number => lv_isir.batch_number ,
237 x_early_analysis_flag => lv_isir.early_analysis_flag ,
238 x_app_entry_source_code => lv_isir.app_entry_source_code ,
239 x_eti_destination_code => lv_isir.eti_destination_code ,
240 x_reject_override_b => lv_isir.reject_override_b ,
241 x_reject_override_n => lv_isir.reject_override_n ,
242 x_reject_override_w => lv_isir.reject_override_w ,
243 x_assum_override_1 => lv_isir.assum_override_1 ,
244 x_assum_override_2 => lv_isir.assum_override_2 ,
245 x_assum_override_3 => lv_isir.assum_override_3 ,
246 x_assum_override_4 => lv_isir.assum_override_4 ,
247 x_assum_override_5 => lv_isir.assum_override_5 ,
248 x_assum_override_6 => lv_isir.assum_override_6 ,
249 x_dependency_status => lv_isir.dependency_status ,
250 x_s_email_address => lv_isir.s_email_address ,
251 x_nslds_reason_code => lv_isir.nslds_reason_code ,
252 x_app_receipt_date => lv_isir.app_receipt_date ,
253 x_processed_rec_type => lv_isir.processed_rec_type ,
254 x_hist_correction_for_tran_id => lv_isir.hist_correction_for_tran_id ,
255 x_system_generated_indicator => lv_isir.system_generated_indicator ,
256 x_dup_request_indicator => lv_isir.dup_request_indicator ,
257 x_source_of_correction => lv_isir.source_of_correction ,
258 x_p_cal_tax_status => lv_isir.p_cal_tax_status ,
259 x_s_cal_tax_status => lv_isir.s_cal_tax_status ,
260 x_graduate_flag => lv_isir.graduate_flag ,
261 x_auto_zero_efc => lv_isir.auto_zero_efc ,
262 x_efc_change_flag => lv_isir.efc_change_flag ,
263 x_sarc_flag => lv_isir.sarc_flag ,
264 x_simplified_need_test => lv_isir.simplified_need_test ,
265 x_reject_reason_codes => lv_isir.reject_reason_codes ,
266 x_select_service_match_flag => lv_isir.select_service_match_flag ,
267 x_select_service_reg_flag => lv_isir.select_service_reg_flag ,
268 x_ins_match_flag => lv_isir.ins_match_flag ,
269 x_ins_verification_number => NULL,
270 x_sec_ins_match_flag => lv_isir.sec_ins_match_flag ,
271 x_sec_ins_ver_number => lv_isir.sec_ins_ver_number ,
272 x_ssn_match_flag => lv_isir.ssn_match_flag ,
273 x_ssa_citizenship_flag => lv_isir.ssa_citizenship_flag ,
274 x_ssn_date_of_death => lv_isir.ssn_date_of_death ,
275 x_nslds_match_flag => lv_isir.nslds_match_flag ,
276 x_va_match_flag => lv_isir.va_match_flag ,
277 x_prisoner_match => lv_isir.prisoner_match ,
278 x_verification_flag => lv_isir.verification_flag ,
279 x_subsequent_app_flag => lv_isir.subsequent_app_flag ,
280 x_app_source_site_code => lv_isir.app_source_site_code ,
281 x_tran_source_site_code => lv_isir.tran_source_site_code ,
282 x_drn => lv_isir.drn ,
283 x_tran_process_date => lv_isir.tran_process_date ,
284 x_computer_batch_number => lv_isir.computer_batch_number ,
285 x_correction_flags => lv_isir.correction_flags ,
286 x_highlight_flags => lv_isir.highlight_flags ,
287 x_paid_efc => NULL ,
288 x_primary_efc => lv_isir.primary_efc ,
289 x_secondary_efc => lv_isir.secondary_efc ,
290 x_fed_pell_grant_efc_type => NULL,
291 x_primary_efc_type => lv_isir.primary_efc_type ,
292 x_sec_efc_type => lv_isir.sec_efc_type ,
293 x_primary_alternate_month_1 => lv_isir.primary_alternate_month_1 ,
294 x_primary_alternate_month_2 => lv_isir.primary_alternate_month_2 ,
295 x_primary_alternate_month_3 => lv_isir.primary_alternate_month_3 ,
296 x_primary_alternate_month_4 => lv_isir.primary_alternate_month_4 ,
297 x_primary_alternate_month_5 => lv_isir.primary_alternate_month_5 ,
298 x_primary_alternate_month_6 => lv_isir.primary_alternate_month_6 ,
299 x_primary_alternate_month_7 => lv_isir.primary_alternate_month_7 ,
300 x_primary_alternate_month_8 => lv_isir.primary_alternate_month_8 ,
301 x_primary_alternate_month_10 => lv_isir.primary_alternate_month_10 ,
302 x_primary_alternate_month_11 => lv_isir.primary_alternate_month_11 ,
303 x_primary_alternate_month_12 => lv_isir.primary_alternate_month_12 ,
304 x_sec_alternate_month_1 => lv_isir.sec_alternate_month_1 ,
305 x_sec_alternate_month_2 => lv_isir.sec_alternate_month_2 ,
306 x_sec_alternate_month_3 => lv_isir.sec_alternate_month_3 ,
307 x_sec_alternate_month_4 => lv_isir.sec_alternate_month_4 ,
308 x_sec_alternate_month_5 => lv_isir.sec_alternate_month_5 ,
309 x_sec_alternate_month_6 => lv_isir.sec_alternate_month_6 ,
310 x_sec_alternate_month_7 => lv_isir.sec_alternate_month_7 ,
311 x_sec_alternate_month_8 => lv_isir.sec_alternate_month_8 ,
312 x_sec_alternate_month_10 => lv_isir.sec_alternate_month_10 ,
313 x_sec_alternate_month_11 => lv_isir.sec_alternate_month_11 ,
314 x_sec_alternate_month_12 => lv_isir.sec_alternate_month_12 ,
315 x_total_income => lv_isir.total_income ,
316 x_allow_total_income => lv_isir.allow_total_income ,
317 x_state_tax_allow => lv_isir.state_tax_allow ,
318 x_employment_allow => lv_isir.employment_allow ,
319 x_income_protection_allow => lv_isir.income_protection_allow ,
320 x_available_income => lv_isir.available_income ,
321 x_contribution_from_ai => lv_isir.contribution_from_ai ,
322 x_discretionary_networth => lv_isir.discretionary_networth ,
323 x_efc_networth => lv_isir.efc_networth ,
324 x_asset_protect_allow => lv_isir.asset_protect_allow ,
325 x_parents_cont_from_assets => lv_isir.parents_cont_from_assets ,
326 x_adjusted_available_income => lv_isir.adjusted_available_income ,
327 x_total_student_contribution => lv_isir.total_student_contribution ,
328 x_total_parent_contribution => lv_isir.total_parent_contribution ,
329 x_parents_contribution => lv_isir.parents_contribution ,
330 x_student_total_income => lv_isir.student_total_income ,
331 x_sati => lv_isir.sati ,
332 x_sic => lv_isir.sic ,
333 x_sdnw => lv_isir.sdnw ,
334 x_sca => lv_isir.sca ,
335 x_fti => lv_isir.fti ,
336 x_secti => lv_isir.secti ,
337 x_secati => lv_isir.secati ,
338 x_secstx => lv_isir.secstx ,
339 x_secea => lv_isir.secea ,
340 x_secipa => lv_isir.secipa ,
341 x_secai => lv_isir.secai ,
342 x_seccai => lv_isir.seccai ,
343 x_secdnw => lv_isir.secdnw ,
344 x_secnw => lv_isir.secnw ,
345 x_secapa => lv_isir.secapa ,
346 x_secpca => lv_isir.secpca ,
347 x_secaai => lv_isir.secaai ,
348 x_sectsc => lv_isir.sectsc ,
349 x_sectpc => lv_isir.sectpc ,
350 x_secpc => lv_isir.secpc ,
351 x_secsti => lv_isir.secsti ,
352 x_secsic => lv_isir.secsic ,
353 x_secsati => lv_isir.secsati ,
354 x_secsdnw => lv_isir.secsdnw ,
355 x_secsca => lv_isir.secsca ,
356 x_secfti => lv_isir.secfti ,
357 x_a_citizenship => lv_isir.a_citizenship ,
358 x_a_student_marital_status => lv_isir.a_student_marital_status ,
359 x_a_student_agi => lv_isir.a_student_agi ,
360 x_a_s_us_tax_paid => lv_isir.a_s_us_tax_paid ,
361 x_a_s_income_work => lv_isir.a_s_income_work ,
362 x_a_spouse_income_work => lv_isir.a_spouse_income_work ,
363 x_a_s_total_wsc => lv_isir.a_s_total_wsc ,
364 x_a_date_of_birth => lv_isir.a_date_of_birth ,
365 x_a_student_married => lv_isir.a_student_married ,
366 x_a_have_children => lv_isir.a_have_children ,
367 x_a_s_have_dependents => lv_isir.a_s_have_dependents ,
368 x_a_va_status => lv_isir.a_va_status ,
369 x_a_s_num_in_family => lv_isir.a_s_num_in_family ,
370 x_a_s_num_in_college => lv_isir.a_s_num_in_college ,
371 x_a_p_marital_status => lv_isir.a_p_marital_status ,
372 x_a_father_ssn => lv_isir.a_father_ssn ,
373 x_a_mother_ssn => lv_isir.a_mother_ssn ,
374 x_a_parents_num_family => lv_isir.a_parents_num_family ,
375 x_a_parents_num_college => lv_isir.a_parents_num_college ,
376 x_a_parents_agi => lv_isir.a_parents_agi ,
377 x_a_p_us_tax_paid => lv_isir.a_p_us_tax_paid ,
378 x_a_f_work_income => lv_isir.a_f_work_income ,
379 x_a_m_work_income => lv_isir.a_m_work_income ,
380 x_a_p_total_wsc => lv_isir.a_p_total_wsc ,
381 x_comment_codes => lv_isir.comment_codes ,
382 x_sar_ack_comm_code => lv_isir.sar_ack_comm_code ,
383 x_pell_grant_elig_flag => lv_isir.pell_grant_elig_flag ,
384 x_reprocess_reason_code => lv_isir.reprocess_reason_code ,
385 x_duplicate_date => lv_isir.duplicate_date ,
386 x_isir_transaction_type => lv_isir.isir_transaction_type ,
387 x_fedral_schl_code_indicator => lv_isir.fedral_schl_code_indicator ,
388 x_multi_school_code_flags => lv_isir.multi_school_code_flags ,
389 x_dup_ssn_indicator => lv_isir.dup_ssn_indicator ,
390 x_system_record_type => 'INTERNAL' ,
391 x_payment_isir => 'N' ,
392 x_receipt_status => lv_isir.receipt_status ,
393 x_isir_receipt_completed => lv_isir.isir_receipt_completed ,
394 x_active_isir => 'N' ,
395 x_fafsa_data_verify_flags => lv_isir.fafsa_data_verify_flags ,
396 x_reject_override_a => lv_isir.reject_override_a ,
397 x_reject_override_c => lv_isir.reject_override_c ,
398 x_parent_marital_status_date => lv_isir.parent_marital_status_date ,
399 x_legacy_record_flag => NULL ,
400 x_father_first_name_initial => lv_isir.father_first_name_initial_txt ,
401 x_father_step_father_birth_dt => lv_isir.father_step_father_birth_date ,
402 x_mother_first_name_initial => lv_isir.mother_first_name_initial_txt ,
403 x_mother_step_mother_birth_dt => lv_isir.mother_step_mother_birth_date ,
404 x_parents_email_address_txt => lv_isir.parents_email_address_txt ,
405 x_address_change_type => lv_isir.address_change_type ,
406 x_cps_pushed_isir_flag => lv_isir.cps_pushed_isir_flag ,
407 x_electronic_transaction_type => lv_isir.electronic_transaction_type ,
408 x_sar_c_change_type => lv_isir.sar_c_change_type ,
409 x_father_ssn_match_type => lv_isir.father_ssn_match_type ,
410 x_mother_ssn_match_type => lv_isir.mother_ssn_match_type ,
411 x_reject_override_g_flag => lv_isir.reject_override_g_flag,
412 x_dhs_verification_num_txt => lv_isir.dhs_verification_num_txt ,
413 x_data_file_name_txt => lv_isir.data_file_name_txt ,
414 x_message_class_txt => NULL, -- Passing NULL as the record is created internally and not imported from external system
415 x_reject_override_3_flag => lv_isir.reject_override_3_flag,
416 x_reject_override_12_flag => lv_isir.reject_override_12_flag,
417 x_reject_override_j_flag => lv_isir.reject_override_j_flag,
418 x_reject_override_k_flag => lv_isir.reject_override_k_flag,
419 x_rejected_status_change_flag => lv_isir.rejected_status_change_flag,
420 x_verification_selection_flag => lv_isir.verification_selection_flag
421 );
422
423
424 ELSE
425 lv_rowid := null;
426 pn_isir_id := null;
427
428 igf_ap_isir_matched_pkg.insert_row(
429 x_Mode => 'R',
430 x_rowid => lv_rowid,
431 x_isir_id => pn_isir_id,
432 x_base_id => lv_isir.base_id,
433 x_batch_year => lv_isir.batch_year ,
434 x_transaction_num => lv_isir.transaction_num ,
435 x_current_ssn => lv_isir.current_ssn ,
436 x_ssn_name_change => lv_isir.ssn_name_change ,
437 x_original_ssn => lv_isir.original_ssn ,
438 x_orig_name_id => lv_isir.orig_name_id ,
439 x_last_name => lv_isir.last_name ,
440 x_first_name => lv_isir.first_name ,
441 x_middle_initial => lv_isir.middle_initial ,
442 x_perm_mail_add => lv_isir.perm_mail_add ,
443 x_perm_city => lv_isir.perm_city ,
444 x_perm_state => lv_isir.perm_state ,
445 x_perm_zip_code => lv_isir.perm_zip_code ,
446 x_date_of_birth => lv_isir.date_of_birth ,
447 x_phone_number => lv_isir.phone_number ,
448 x_driver_license_number => lv_isir.driver_license_number ,
449 x_driver_license_state => lv_isir.driver_license_state ,
450 x_citizenship_status => lv_isir.citizenship_status ,
451 x_alien_reg_number => lv_isir.alien_reg_number ,
452 x_s_marital_status => lv_isir.s_marital_status ,
453 x_s_marital_status_date => lv_isir.s_marital_status_date ,
454 x_summ_enrl_status => lv_isir.summ_enrl_status ,
455 x_fall_enrl_status => lv_isir.fall_enrl_status ,
456 x_winter_enrl_status => lv_isir.winter_enrl_status ,
457 x_spring_enrl_status => lv_isir.spring_enrl_status ,
458 x_summ2_enrl_status => lv_isir.summ2_enrl_status ,
459 x_fathers_highest_edu_level => lv_isir.fathers_highest_edu_level ,
460 x_mothers_highest_edu_level => lv_isir.mothers_highest_edu_level ,
461 x_s_state_legal_residence => lv_isir.s_state_legal_residence ,
462 x_legal_residence_before_date => lv_isir.legal_residence_before_date ,
463 x_s_legal_resd_date => lv_isir.s_legal_resd_date ,
464 x_ss_r_u_male => lv_isir.ss_r_u_male ,
465 x_selective_service_reg => lv_isir.selective_service_reg ,
466 x_degree_certification => lv_isir.degree_certification ,
467 x_grade_level_in_college => lv_isir.grade_level_in_college ,
468 x_high_school_diploma_ged => lv_isir.high_school_diploma_ged ,
469 x_first_bachelor_deg_by_date => lv_isir.first_bachelor_deg_by_date ,
470 x_interest_in_loan => lv_isir.interest_in_loan ,
471 x_interest_in_stud_employment => lv_isir.interest_in_stud_employment ,
472 x_drug_offence_conviction => lv_isir.drug_offence_conviction ,
473 x_s_tax_return_status => lv_isir.s_tax_return_status ,
474 x_s_type_tax_return => lv_isir.s_type_tax_return ,
475 x_s_elig_1040ez => lv_isir.s_elig_1040ez ,
476 x_s_adjusted_gross_income => lv_isir.s_adjusted_gross_income ,
477 x_s_fed_taxes_paid => lv_isir.s_fed_taxes_paid ,
478 x_s_exemptions => lv_isir.s_exemptions ,
479 x_s_income_from_work => lv_isir.s_income_from_work ,
480 x_spouse_income_from_work => lv_isir.spouse_income_from_work ,
481 x_s_toa_amt_from_wsa => lv_isir.s_toa_amt_from_wsa ,
482 x_s_toa_amt_from_wsb => lv_isir.s_toa_amt_from_wsb ,
483 x_s_toa_amt_from_wsc => lv_isir.s_toa_amt_from_wsc ,
484 x_s_investment_networth => lv_isir.s_investment_networth ,
485 x_s_busi_farm_networth => lv_isir.s_busi_farm_networth ,
486 x_s_cash_savings => lv_isir.s_cash_savings ,
487 x_va_months => lv_isir.va_months ,
488 x_va_amount => lv_isir.va_amount ,
489 x_stud_dob_before_date => lv_isir.stud_dob_before_date ,
490 x_deg_beyond_bachelor => lv_isir.deg_beyond_bachelor ,
491 x_s_married => lv_isir.s_married ,
492 x_s_have_children => lv_isir.s_have_children ,
493 x_legal_dependents => lv_isir.legal_dependents ,
494 x_orphan_ward_of_court => lv_isir.orphan_ward_of_court ,
495 x_s_veteran => lv_isir.s_veteran ,
496 x_p_marital_status => lv_isir.p_marital_status ,
497 x_father_ssn => lv_isir.father_ssn ,
498 x_f_last_name => lv_isir.f_last_name ,
499 x_mother_ssn => lv_isir.mother_ssn ,
500 x_m_last_name => lv_isir.m_last_name ,
501 x_p_num_family_member => lv_isir.p_num_family_member ,
502 x_p_num_in_college => lv_isir.p_num_in_college ,
503 x_p_state_legal_residence => lv_isir.p_state_legal_residence ,
504 x_p_state_legal_res_before_dt => lv_isir.p_state_legal_res_before_dt ,
505 x_p_legal_res_date => lv_isir.p_legal_res_date ,
506 x_age_older_parent => lv_isir.age_older_parent ,
507 x_p_tax_return_status => lv_isir.p_tax_return_status ,
508 x_p_type_tax_return => lv_isir.p_type_tax_return ,
509 x_p_elig_1040aez => lv_isir.p_elig_1040aez ,
510 x_p_adjusted_gross_income => lv_isir.p_adjusted_gross_income ,
511 x_p_taxes_paid => lv_isir.p_taxes_paid ,
512 x_p_exemptions => lv_isir.p_exemptions ,
513 x_f_income_work => lv_isir.f_income_work ,
514 x_m_income_work => lv_isir.m_income_work ,
515 x_p_income_wsa => lv_isir.p_income_wsa ,
516 x_p_income_wsb => lv_isir.p_income_wsb ,
517 x_p_income_wsc => lv_isir.p_income_wsc ,
518 x_p_investment_networth => lv_isir.p_investment_networth ,
519 x_p_business_networth => lv_isir.p_business_networth ,
520 x_p_cash_saving => lv_isir.p_cash_saving ,
521 x_s_num_family_members => lv_isir.s_num_family_members ,
522 x_s_num_in_college => lv_isir.s_num_in_college ,
523 x_first_college => lv_isir.first_college ,
524 x_first_house_plan => lv_isir.first_house_plan ,
525 x_second_college => lv_isir.second_college ,
526 x_second_house_plan => lv_isir.second_house_plan ,
527 x_third_college => lv_isir.third_college ,
528 x_third_house_plan => lv_isir.third_house_plan ,
529 x_fourth_college => lv_isir.fourth_college ,
530 x_fourth_house_plan => lv_isir.fourth_house_plan ,
531 x_fifth_college => lv_isir.fifth_college ,
532 x_fifth_house_plan => lv_isir.fifth_house_plan ,
533 x_sixth_college => lv_isir.sixth_college ,
534 x_sixth_house_plan => lv_isir.sixth_house_plan ,
535 x_date_app_completed => lv_isir.date_app_completed ,
536 x_signed_by => lv_isir.signed_by ,
537 x_preparer_ssn => lv_isir.preparer_ssn ,
538 x_preparer_emp_id_number => lv_isir.preparer_emp_id_number ,
539 x_preparer_sign => lv_isir.preparer_sign ,
540 x_transaction_receipt_date => lv_isir.transaction_receipt_date ,
541 x_dependency_override_ind => lv_isir.dependency_override_ind ,
542 x_faa_fedral_schl_code => lv_isir.faa_fedral_schl_code ,
543 x_faa_adjustment => lv_isir.faa_adjustment ,
544 x_input_record_type => lv_isir.input_record_type ,
545 x_serial_number => lv_isir.serial_number ,
546 x_batch_number => lv_isir.batch_number ,
547 x_early_analysis_flag => lv_isir.early_analysis_flag ,
548 x_app_entry_source_code => lv_isir.app_entry_source_code ,
549 x_eti_destination_code => lv_isir.eti_destination_code ,
550 x_reject_override_b => lv_isir.reject_override_b ,
551 x_reject_override_n => lv_isir.reject_override_n ,
552 x_reject_override_w => lv_isir.reject_override_w ,
553 x_assum_override_1 => lv_isir.assum_override_1 ,
554 x_assum_override_2 => lv_isir.assum_override_2 ,
555 x_assum_override_3 => lv_isir.assum_override_3 ,
556 x_assum_override_4 => lv_isir.assum_override_4 ,
557 x_assum_override_5 => lv_isir.assum_override_5 ,
558 x_assum_override_6 => lv_isir.assum_override_6 ,
559 x_dependency_status => lv_isir.dependency_status ,
560 x_s_email_address => lv_isir.s_email_address ,
561 x_nslds_reason_code => lv_isir.nslds_reason_code ,
562 x_app_receipt_date => lv_isir.app_receipt_date ,
563 x_processed_rec_type => lv_isir.processed_rec_type ,
564 x_hist_correction_for_tran_id => lv_isir.hist_correction_for_tran_id ,
565 x_system_generated_indicator => lv_isir.system_generated_indicator ,
566 x_dup_request_indicator => lv_isir.dup_request_indicator ,
567 x_source_of_correction => lv_isir.source_of_correction ,
568 x_p_cal_tax_status => lv_isir.p_cal_tax_status ,
569 x_s_cal_tax_status => lv_isir.s_cal_tax_status ,
570 x_graduate_flag => lv_isir.graduate_flag ,
571 x_auto_zero_efc => lv_isir.auto_zero_efc ,
572 x_efc_change_flag => lv_isir.efc_change_flag ,
573 x_sarc_flag => lv_isir.sarc_flag ,
574 x_simplified_need_test => lv_isir.simplified_need_test ,
575 x_reject_reason_codes => lv_isir.reject_reason_codes ,
576 x_select_service_match_flag => lv_isir.select_service_match_flag ,
577 x_select_service_reg_flag => lv_isir.select_service_reg_flag ,
578 x_ins_match_flag => lv_isir.ins_match_flag ,
579 x_ins_verification_number => NULL,
580 x_sec_ins_match_flag => lv_isir.sec_ins_match_flag ,
581 x_sec_ins_ver_number => lv_isir.sec_ins_ver_number ,
582 x_ssn_match_flag => lv_isir.ssn_match_flag ,
583 x_ssa_citizenship_flag => lv_isir.ssa_citizenship_flag ,
584 x_ssn_date_of_death => lv_isir.ssn_date_of_death ,
585 x_nslds_match_flag => lv_isir.nslds_match_flag ,
586 x_va_match_flag => lv_isir.va_match_flag ,
587 x_prisoner_match => lv_isir.prisoner_match ,
588 x_verification_flag => lv_isir.verification_flag ,
589 x_subsequent_app_flag => lv_isir.subsequent_app_flag ,
590 x_app_source_site_code => lv_isir.app_source_site_code ,
591 x_tran_source_site_code => lv_isir.tran_source_site_code ,
592 x_drn => lv_isir.drn ,
593 x_tran_process_date => lv_isir.tran_process_date ,
594 x_computer_batch_number => lv_isir.computer_batch_number ,
595 x_correction_flags => lv_isir.correction_flags ,
596 x_highlight_flags => lv_isir.highlight_flags ,
597 x_paid_efc => NULL ,
598 x_primary_efc => lv_isir.primary_efc ,
599 x_secondary_efc => lv_isir.secondary_efc ,
600 x_fed_pell_grant_efc_type => NULL ,
601 x_primary_efc_type => lv_isir.primary_efc_type ,
602 x_sec_efc_type => lv_isir.sec_efc_type ,
603 x_primary_alternate_month_1 => lv_isir.primary_alternate_month_1 ,
604 x_primary_alternate_month_2 => lv_isir.primary_alternate_month_2 ,
605 x_primary_alternate_month_3 => lv_isir.primary_alternate_month_3 ,
606 x_primary_alternate_month_4 => lv_isir.primary_alternate_month_4 ,
607 x_primary_alternate_month_5 => lv_isir.primary_alternate_month_5 ,
608 x_primary_alternate_month_6 => lv_isir.primary_alternate_month_6 ,
609 x_primary_alternate_month_7 => lv_isir.primary_alternate_month_7 ,
610 x_primary_alternate_month_8 => lv_isir.primary_alternate_month_8 ,
611 x_primary_alternate_month_10 => lv_isir.primary_alternate_month_10 ,
612 x_primary_alternate_month_11 => lv_isir.primary_alternate_month_11 ,
613 x_primary_alternate_month_12 => lv_isir.primary_alternate_month_12 ,
614 x_sec_alternate_month_1 => lv_isir.sec_alternate_month_1 ,
615 x_sec_alternate_month_2 => lv_isir.sec_alternate_month_2 ,
616 x_sec_alternate_month_3 => lv_isir.sec_alternate_month_3 ,
617 x_sec_alternate_month_4 => lv_isir.sec_alternate_month_4 ,
618 x_sec_alternate_month_5 => lv_isir.sec_alternate_month_5 ,
619 x_sec_alternate_month_6 => lv_isir.sec_alternate_month_6 ,
620 x_sec_alternate_month_7 => lv_isir.sec_alternate_month_7 ,
621 x_sec_alternate_month_8 => lv_isir.sec_alternate_month_8 ,
622 x_sec_alternate_month_10 => lv_isir.sec_alternate_month_10 ,
623 x_sec_alternate_month_11 => lv_isir.sec_alternate_month_11 ,
624 x_sec_alternate_month_12 => lv_isir.sec_alternate_month_12 ,
625 x_total_income => lv_isir.total_income ,
626 x_allow_total_income => lv_isir.allow_total_income ,
627 x_state_tax_allow => lv_isir.state_tax_allow ,
628 x_employment_allow => lv_isir.employment_allow ,
629 x_income_protection_allow => lv_isir.income_protection_allow ,
630 x_available_income => lv_isir.available_income ,
631 x_contribution_from_ai => lv_isir.contribution_from_ai ,
632 x_discretionary_networth => lv_isir.discretionary_networth ,
633 x_efc_networth => lv_isir.efc_networth ,
634 x_asset_protect_allow => lv_isir.asset_protect_allow ,
635 x_parents_cont_from_assets => lv_isir.parents_cont_from_assets ,
636 x_adjusted_available_income => lv_isir.adjusted_available_income ,
637 x_total_student_contribution => lv_isir.total_student_contribution ,
638 x_total_parent_contribution => lv_isir.total_parent_contribution ,
639 x_parents_contribution => lv_isir.parents_contribution ,
640 x_student_total_income => lv_isir.student_total_income ,
641 x_sati => lv_isir.sati ,
642 x_sic => lv_isir.sic ,
643 x_sdnw => lv_isir.sdnw ,
644 x_sca => lv_isir.sca ,
645 x_fti => lv_isir.fti ,
646 x_secti => lv_isir.secti ,
647 x_secati => lv_isir.secati ,
648 x_secstx => lv_isir.secstx ,
649 x_secea => lv_isir.secea ,
650 x_secipa => lv_isir.secipa ,
651 x_secai => lv_isir.secai ,
652 x_seccai => lv_isir.seccai ,
653 x_secdnw => lv_isir.secdnw ,
654 x_secnw => lv_isir.secnw ,
655 x_secapa => lv_isir.secapa ,
656 x_secpca => lv_isir.secpca ,
657 x_secaai => lv_isir.secaai ,
658 x_sectsc => lv_isir.sectsc ,
659 x_sectpc => lv_isir.sectpc ,
660 x_secpc => lv_isir.secpc ,
661 x_secsti => lv_isir.secsti ,
662 x_secsic => lv_isir.secsic ,
663 x_secsati => lv_isir.secsati ,
664 x_secsdnw => lv_isir.secsdnw ,
665 x_secsca => lv_isir.secsca ,
666 x_secfti => lv_isir.secfti ,
667 x_a_citizenship => lv_isir.a_citizenship ,
668 x_a_student_marital_status => lv_isir.a_student_marital_status ,
669 x_a_student_agi => lv_isir.a_student_agi ,
670 x_a_s_us_tax_paid => lv_isir.a_s_us_tax_paid ,
671 x_a_s_income_work => lv_isir.a_s_income_work ,
672 x_a_spouse_income_work => lv_isir.a_spouse_income_work ,
673 x_a_s_total_wsc => lv_isir.a_s_total_wsc ,
674 x_a_date_of_birth => lv_isir.a_date_of_birth ,
675 x_a_student_married => lv_isir.a_student_married ,
676 x_a_have_children => lv_isir.a_have_children ,
677 x_a_s_have_dependents => lv_isir.a_s_have_dependents ,
678 x_a_va_status => lv_isir.a_va_status ,
679 x_a_s_num_in_family => lv_isir.a_s_num_in_family ,
680 x_a_s_num_in_college => lv_isir.a_s_num_in_college ,
681 x_a_p_marital_status => lv_isir.a_p_marital_status ,
682 x_a_father_ssn => lv_isir.a_father_ssn ,
683 x_a_mother_ssn => lv_isir.a_mother_ssn ,
684 x_a_parents_num_family => lv_isir.a_parents_num_family ,
685 x_a_parents_num_college => lv_isir.a_parents_num_college ,
686 x_a_parents_agi => lv_isir.a_parents_agi ,
687 x_a_p_us_tax_paid => lv_isir.a_p_us_tax_paid ,
688 x_a_f_work_income => lv_isir.a_f_work_income ,
689 x_a_m_work_income => lv_isir.a_m_work_income ,
690 x_a_p_total_wsc => lv_isir.a_p_total_wsc ,
691 x_comment_codes => lv_isir.comment_codes ,
692 x_sar_ack_comm_code => lv_isir.sar_ack_comm_code ,
693 x_pell_grant_elig_flag => lv_isir.pell_grant_elig_flag ,
694 x_reprocess_reason_code => lv_isir.reprocess_reason_code ,
695 x_duplicate_date => lv_isir.duplicate_date ,
696 x_isir_transaction_type => lv_isir.isir_transaction_type ,
697 x_fedral_schl_code_indicator => lv_isir.fedral_schl_code_indicator ,
698 x_multi_school_code_flags => lv_isir.multi_school_code_flags ,
699 x_dup_ssn_indicator => lv_isir.dup_ssn_indicator ,
700 x_system_record_type => 'INTERNAL' ,
701 x_payment_isir => 'N' ,
702 x_receipt_status => lv_isir.receipt_status ,
703 x_isir_receipt_completed => lv_isir.isir_receipt_completed ,
704 x_active_isir => 'N' ,
705 x_fafsa_data_verify_flags => lv_isir.fafsa_data_verify_flags ,
706 x_reject_override_a => lv_isir.reject_override_a ,
707 x_reject_override_c => lv_isir.reject_override_c ,
708 x_parent_marital_status_date => lv_isir.parent_marital_status_date ,
709 x_legacy_record_flag => NULL ,
710 x_father_first_name_initial => lv_isir.FATHER_FIRST_NAME_INITIAL_TXT ,
711 x_father_step_father_birth_dt => lv_isir.FATHER_STEP_FATHER_BIRTH_DATE ,
712 x_mother_first_name_initial => lv_isir.MOTHER_FIRST_NAME_INITIAL_TXT ,
713 x_mother_step_mother_birth_dt => lv_isir.MOTHER_STEP_MOTHER_BIRTH_DATE ,
714 x_parents_email_address_txt => lv_isir.PARENTS_EMAIL_ADDRESS_TXT ,
715 x_address_change_type => lv_isir.ADDRESS_CHANGE_TYPE ,
716 x_cps_pushed_isir_flag => lv_isir.CPS_PUSHED_ISIR_FLAG ,
717 x_electronic_transaction_type => lv_isir.ELECTRONIC_TRANSACTION_TYPE ,
718 x_sar_c_change_type => lv_isir.SAR_C_CHANGE_TYPE ,
719 x_father_ssn_match_type => lv_isir.FATHER_SSN_MATCH_TYPE ,
720 x_mother_ssn_match_type => lv_isir.MOTHER_SSN_MATCH_TYPE ,
721 x_reject_override_g_flag => lv_isir.REJECT_OVERRIDE_G_FLAG,
722 x_dhs_verification_num_txt => lv_isir.dhs_verification_num_txt ,
723 x_data_file_name_txt => lv_isir.data_file_name_txt ,
724 x_message_class_txt => NULL, -- Passing NULL as the record is created internally
725 x_reject_override_3_flag => lv_isir.reject_override_3_flag,
726 x_reject_override_12_flag => lv_isir.reject_override_12_flag,
727 x_reject_override_j_flag => lv_isir.reject_override_j_flag,
728 x_reject_override_k_flag => lv_isir.reject_override_k_flag,
729 x_rejected_status_change_flag => lv_isir.rejected_status_change_flag,
730 x_verification_selection_flag => lv_isir.verification_selection_flag
731 );
732 p_ret_isir_id := pn_isir_id ;
733
734 COMMIT;
735 OPEN chk_row_id;
736 FETCH chk_row_id into lv_rowid;
737 CLOSE chk_row_id;
738 END IF;
739 CLOSE chk_internal ;
740 CLOSE c_isir;
741 ELSE
742 -- ISIR Not FOUND hence return -1
743 CLOSE c_isir;
744 p_ret_isir_id := -1 ;
745 RETURN ;
746 END IF;
747 EXCEPTION
748 WHEN OTHERS THEN
749 IF SQLCODE = -54 THEN
750 -- ORA-00054: resource busy and acquire with NOWAIT specified
751 p_ret_isir_id := -2;
752 ELSE
753 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
754 FND_MESSAGE.SET_TOKEN('NAME','igf_ap_ss_pkg.set_internal_isir');
755 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
756 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_ss_pkg.set_internal_isir.exception','Exception: '||SQLERRM);
757 END IF;
758 IGS_GE_MSG_STACK.ADD;
759 END IF;
760 END set_internal_isir;
761
762 FUNCTION get_pid (
763 p_pid_grp IN igs_pe_persid_group.group_id%TYPE,
764 p_status OUT NOCOPY VARCHAR2,
765 p_group_type OUT NOCOPY igs_pe_persid_group_v.group_type%TYPE
766 ) RETURN VARCHAR2 IS
767 /*************************************************************
768 Change History
769 Who When What
770 ridas 07-Feb-2006 Bug #5021084. Replaced function IGS_GET_DYNAMIC_SQL with GET_DYNAMIC_SQL.
771 (reverse chronological order - newest change first)
772 ***************************************************************/
773
774 lv_ret_sql VARCHAR2(32767);
775 lv_status VARCHAR2(1);
776
777 g_api_version CONSTANT NUMBER := 1.0;
778 g_api_name CONSTANT VARCHAR2(30) := 'get_dynamic_sql';
779 g_pkg_name CONSTANT VARCHAR2(30) := 'igs_dynamic_perid_group';
780 g_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| g_api_name;
781 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
782
783 BEGIN
784 --Bug #5021084.
785 lv_ret_sql := igs_pe_dynamic_persid_group.get_dynamic_sql
786 (p_pid_grp,lv_status,lv_group_type);
787 p_status := lv_status;
788 p_group_type := lv_group_type;
789 RETURN lv_ret_sql;
790 END;
791
792
793 PROCEDURE update_isir(
794 p_isir_rec igf_ap_isir_matched%ROWTYPE,
795 p_isir_id igf_ap_isir_matched.ISIR_ID%TYPE,
796 p_rowid VARCHAR2
797 ) IS
798 ------------------------------------------------------------------
799 --Created by : brajendr
800 --Date created: 17-Feb-2003
801 --
802 --Purpose:
803 --
804 --
805 --Known limitations/enhancements and/or remarks:
806 --
807 --Change History:
808 --Who When What
809 --asbala 19-nov-2003 3026594: FA128 - ISIR Federal Updates 04- 05,
810 -- modified signature of igf_ap_isir_matched_pkg
811 -------------------------------------------------------------------
812
813 BEGIN
814
815 --The existing Correction ISIR is updated with the Simulated ISIR values
816 igf_ap_isir_matched_pkg.update_row(
817 x_rowid => p_rowid,
818 x_isir_id => p_isir_id,
819 x_base_id => p_isir_rec.base_id,
820 x_batch_year => p_isir_rec.batch_year,
821 x_transaction_num => p_isir_rec.transaction_num,
822 x_current_ssn => p_isir_rec.current_ssn,
823 x_ssn_name_change => p_isir_rec.ssn_name_change,
824 x_original_ssn => p_isir_rec.original_ssn,
825 x_orig_name_id => p_isir_rec.orig_name_id,
826 x_last_name => p_isir_rec.last_name,
827 x_first_name => p_isir_rec.first_name,
828 x_middle_initial => p_isir_rec.middle_initial,
829 x_perm_mail_add => p_isir_rec.perm_mail_add,
830 x_perm_city => p_isir_rec.perm_city,
831 x_perm_state => p_isir_rec.perm_state,
832 x_perm_zip_code => p_isir_rec.perm_zip_code,
833 x_date_of_birth => p_isir_rec.date_of_birth,
834 x_phone_number => p_isir_rec.phone_number,
835 x_driver_license_number => p_isir_rec.driver_license_number,
836 x_driver_license_state => p_isir_rec.driver_license_state,
837 x_citizenship_status => p_isir_rec.citizenship_status,
838 x_alien_reg_number => p_isir_rec.alien_reg_number,
839 x_s_marital_status => p_isir_rec.s_marital_status,
840 x_s_marital_status_date => p_isir_rec.s_marital_status_date,
841 x_summ_enrl_status => p_isir_rec.summ_enrl_status,
842 x_fall_enrl_status => p_isir_rec.fall_enrl_status,
843 x_winter_enrl_status => p_isir_rec.winter_enrl_status,
844 x_spring_enrl_status => p_isir_rec.spring_enrl_status,
845 x_summ2_enrl_status => p_isir_rec.summ2_enrl_status,
846 x_fathers_highest_edu_level => p_isir_rec.fathers_highest_edu_level,
847 x_mothers_highest_edu_level => p_isir_rec.mothers_highest_edu_level,
848 x_s_state_legal_residence => p_isir_rec.s_state_legal_residence,
849 x_legal_residence_before_date => p_isir_rec.legal_residence_before_date,
850 x_s_legal_resd_date => p_isir_rec.s_legal_resd_date,
851 x_ss_r_u_male => p_isir_rec.ss_r_u_male,
852 x_selective_service_reg => p_isir_rec.selective_service_reg,
853 x_degree_certification => p_isir_rec.degree_certification,
854 x_grade_level_in_college => p_isir_rec.grade_level_in_college,
855 x_high_school_diploma_ged => p_isir_rec.high_school_diploma_ged,
856 x_first_bachelor_deg_by_date => p_isir_rec.first_bachelor_deg_by_date,
857 x_interest_in_loan => p_isir_rec.interest_in_loan,
858 x_interest_in_stud_employment => p_isir_rec.interest_in_stud_employment,
859 x_drug_offence_conviction => p_isir_rec.drug_offence_conviction,
860 x_s_tax_return_status => p_isir_rec.s_tax_return_status,
861 x_s_type_tax_return => p_isir_rec.s_type_tax_return,
862 x_s_elig_1040ez => p_isir_rec.s_elig_1040ez,
863 x_s_adjusted_gross_income => p_isir_rec.s_adjusted_gross_income,
864 x_s_fed_taxes_paid => p_isir_rec.s_fed_taxes_paid,
865 x_s_exemptions => p_isir_rec.s_exemptions,
866 x_s_income_from_work => p_isir_rec.s_income_from_work,
867 x_spouse_income_from_work => p_isir_rec.spouse_income_from_work,
868 x_s_toa_amt_from_wsa => p_isir_rec.s_toa_amt_from_wsa,
869 x_s_toa_amt_from_wsb => p_isir_rec.s_toa_amt_from_wsb,
870 x_s_toa_amt_from_wsc => p_isir_rec.s_toa_amt_from_wsc,
871 x_s_investment_networth => p_isir_rec.s_investment_networth,
872 x_s_busi_farm_networth => p_isir_rec.s_busi_farm_networth,
873 x_s_cash_savings => p_isir_rec.s_cash_savings,
874 x_va_months => p_isir_rec.va_months,
875 x_va_amount => p_isir_rec.va_amount,
876 x_stud_dob_before_date => p_isir_rec.stud_dob_before_date,
877 x_deg_beyond_bachelor => p_isir_rec.deg_beyond_bachelor,
878 x_s_married => p_isir_rec.s_married,
879 x_s_have_children => p_isir_rec.s_have_children,
880 x_legal_dependents => p_isir_rec.legal_dependents,
881 x_orphan_ward_of_court => p_isir_rec.orphan_ward_of_court,
882 x_s_veteran => p_isir_rec.s_veteran,
883 x_p_marital_status => p_isir_rec.p_marital_status,
884 x_father_ssn => p_isir_rec.father_ssn,
885 x_f_last_name => p_isir_rec.f_last_name,
886 x_mother_ssn => p_isir_rec.mother_ssn,
887 x_m_last_name => p_isir_rec.m_last_name,
888 x_p_num_family_member => p_isir_rec.p_num_family_member,
889 x_p_num_in_college => p_isir_rec.p_num_in_college,
890 x_p_state_legal_residence => p_isir_rec.p_state_legal_residence,
891 x_p_state_legal_res_before_dt => p_isir_rec.p_state_legal_res_before_dt,
892 x_p_legal_res_date => p_isir_rec.p_legal_res_date,
893 x_age_older_parent => p_isir_rec.age_older_parent,
894 x_p_tax_return_status => p_isir_rec.p_tax_return_status,
895 x_p_type_tax_return => p_isir_rec.p_type_tax_return,
896 x_p_elig_1040aez => p_isir_rec.p_elig_1040aez,
897 x_p_adjusted_gross_income => p_isir_rec.p_adjusted_gross_income,
898 x_p_taxes_paid => p_isir_rec.p_taxes_paid,
899 x_p_exemptions => p_isir_rec.p_exemptions,
900 x_f_income_work => p_isir_rec.f_income_work,
901 x_m_income_work => p_isir_rec.m_income_work,
902 x_p_income_wsa => p_isir_rec.p_income_wsa,
903 x_p_income_wsb => p_isir_rec.p_income_wsb,
904 x_p_income_wsc => p_isir_rec.p_income_wsc,
905 x_p_investment_networth => p_isir_rec.p_investment_networth,
906 x_p_business_networth => p_isir_rec.p_business_networth,
907 x_p_cash_saving => p_isir_rec.p_cash_saving,
908 x_s_num_family_members => p_isir_rec.s_num_family_members,
909 x_s_num_in_college => p_isir_rec.s_num_in_college,
910 x_first_college => p_isir_rec.first_college,
911 x_first_house_plan => p_isir_rec.first_house_plan,
912 x_second_college => p_isir_rec.second_college,
913 x_second_house_plan => p_isir_rec.second_house_plan,
914 x_third_college => p_isir_rec.third_college,
915 x_third_house_plan => p_isir_rec.third_house_plan,
916 x_fourth_college => p_isir_rec.fourth_college,
917 x_fourth_house_plan => p_isir_rec.fourth_house_plan,
918 x_fifth_college => p_isir_rec.fifth_college,
919 x_fifth_house_plan => p_isir_rec.fifth_house_plan,
920 x_sixth_college => p_isir_rec.sixth_college,
921 x_sixth_house_plan => p_isir_rec.sixth_house_plan,
922 x_date_app_completed => p_isir_rec.date_app_completed,
923 x_signed_by => p_isir_rec.signed_by,
924 x_preparer_ssn => p_isir_rec.preparer_ssn,
925 x_preparer_emp_id_number => p_isir_rec.preparer_emp_id_number,
926 x_preparer_sign => p_isir_rec.preparer_sign,
927 x_transaction_receipt_date => p_isir_rec.transaction_receipt_date,
928 x_dependency_override_ind => p_isir_rec.dependency_override_ind,
929 x_faa_fedral_schl_code => p_isir_rec.faa_fedral_schl_code,
930 x_faa_adjustment => p_isir_rec.faa_adjustment,
931 x_input_record_type => p_isir_rec.input_record_type,
932 x_serial_number => p_isir_rec.serial_number,
933 x_batch_number => p_isir_rec.batch_number,
934 x_early_analysis_flag => p_isir_rec.early_analysis_flag,
935 x_app_entry_source_code => p_isir_rec.app_entry_source_code,
936 x_eti_destination_code => p_isir_rec.eti_destination_code,
937 x_reject_override_b => p_isir_rec.reject_override_b,
938 x_reject_override_n => p_isir_rec.reject_override_n,
939 x_reject_override_w => p_isir_rec.reject_override_w,
940 x_assum_override_1 => p_isir_rec.assum_override_1,
941 x_assum_override_2 => p_isir_rec.assum_override_2,
942 x_assum_override_3 => p_isir_rec.assum_override_3,
943 x_assum_override_4 => p_isir_rec.assum_override_4,
944 x_assum_override_5 => p_isir_rec.assum_override_5,
945 x_assum_override_6 => p_isir_rec.assum_override_6,
946 x_dependency_status => p_isir_rec.dependency_status,
947 x_s_email_address => p_isir_rec.s_email_address,
948 x_nslds_reason_code => p_isir_rec.nslds_reason_code,
949 x_app_receipt_date => p_isir_rec.app_receipt_date,
950 x_processed_rec_type => p_isir_rec.processed_rec_type,
951 x_hist_correction_for_tran_id => p_isir_rec.hist_correction_for_tran_id,
952 x_system_generated_indicator => p_isir_rec.system_generated_indicator,
953 x_dup_request_indicator => p_isir_rec.dup_request_indicator,
954 x_source_of_correction => p_isir_rec.source_of_correction,
955 x_p_cal_tax_status => p_isir_rec.p_cal_tax_status,
956 x_s_cal_tax_status => p_isir_rec.s_cal_tax_status,
957 x_graduate_flag => p_isir_rec.graduate_flag,
958 x_auto_zero_efc => p_isir_rec.auto_zero_efc,
959 x_efc_change_flag => p_isir_rec.efc_change_flag,
960 x_sarc_flag => p_isir_rec.sarc_flag,
961 x_simplified_need_test => p_isir_rec.simplified_need_test,
962 x_reject_reason_codes => p_isir_rec.reject_reason_codes,
963 x_select_service_match_flag => p_isir_rec.select_service_match_flag,
964 x_select_service_reg_flag => p_isir_rec.select_service_reg_flag,
965 x_ins_match_flag => p_isir_rec.ins_match_flag,
966 x_ins_verification_number => NULL,
967 x_sec_ins_match_flag => p_isir_rec.sec_ins_match_flag,
968 x_sec_ins_ver_number => p_isir_rec.sec_ins_ver_number,
969 x_ssn_match_flag => p_isir_rec.ssn_match_flag,
970 x_ssa_citizenship_flag => p_isir_rec.ssa_citizenship_flag,
971 x_ssn_date_of_death => p_isir_rec.ssn_date_of_death,
972 x_nslds_match_flag => p_isir_rec.nslds_match_flag,
973 x_va_match_flag => p_isir_rec.va_match_flag,
974 x_prisoner_match => p_isir_rec.prisoner_match,
975 x_verification_flag => p_isir_rec.verification_flag,
976 x_subsequent_app_flag => p_isir_rec.subsequent_app_flag,
977 x_app_source_site_code => p_isir_rec.app_source_site_code,
978 x_tran_source_site_code => p_isir_rec.tran_source_site_code,
979 x_drn => p_isir_rec.drn,
980 x_tran_process_date => p_isir_rec.tran_process_date,
981 x_correction_flags => p_isir_rec.correction_flags,
982 x_computer_batch_number => p_isir_rec.computer_batch_number,
983 x_highlight_flags => p_isir_rec.highlight_flags,
984 x_paid_efc => NULL,
985 x_primary_efc => p_isir_rec.primary_efc,
986 x_secondary_efc => p_isir_rec.secondary_efc,
987 x_fed_pell_grant_efc_type => NULL,
988 x_primary_efc_type => p_isir_rec.primary_efc_type,
989 x_sec_efc_type => p_isir_rec.sec_efc_type,
990 x_primary_alternate_month_1 => p_isir_rec.primary_alternate_month_1,
991 x_primary_alternate_month_2 => p_isir_rec.primary_alternate_month_2,
992 x_primary_alternate_month_3 => p_isir_rec.primary_alternate_month_3,
993 x_primary_alternate_month_4 => p_isir_rec.primary_alternate_month_4,
994 x_primary_alternate_month_5 => p_isir_rec.primary_alternate_month_5,
995 x_primary_alternate_month_6 => p_isir_rec.primary_alternate_month_6,
996 x_primary_alternate_month_7 => p_isir_rec.primary_alternate_month_7,
997 x_primary_alternate_month_8 => p_isir_rec.primary_alternate_month_8,
998 x_primary_alternate_month_10 => p_isir_rec.primary_alternate_month_10,
999 x_primary_alternate_month_11 => p_isir_rec.primary_alternate_month_11,
1000 x_primary_alternate_month_12 => p_isir_rec.primary_alternate_month_12,
1001 x_sec_alternate_month_1 => p_isir_rec.sec_alternate_month_1,
1002 x_sec_alternate_month_2 => p_isir_rec.sec_alternate_month_2,
1003 x_sec_alternate_month_3 => p_isir_rec.sec_alternate_month_3,
1004 x_sec_alternate_month_4 => p_isir_rec.sec_alternate_month_4,
1005 x_sec_alternate_month_5 => p_isir_rec.sec_alternate_month_5,
1006 x_sec_alternate_month_6 => p_isir_rec.sec_alternate_month_6,
1007 x_sec_alternate_month_7 => p_isir_rec.sec_alternate_month_7,
1008 x_sec_alternate_month_8 => p_isir_rec.sec_alternate_month_8,
1009 x_sec_alternate_month_10 => p_isir_rec.sec_alternate_month_10,
1010 x_sec_alternate_month_11 => p_isir_rec.sec_alternate_month_11,
1011 x_sec_alternate_month_12 => p_isir_rec.sec_alternate_month_12,
1012 x_total_income => p_isir_rec.total_income,
1013 x_allow_total_income => p_isir_rec.allow_total_income,
1014 x_state_tax_allow => p_isir_rec.state_tax_allow,
1015 x_employment_allow => p_isir_rec.employment_allow,
1016 x_income_protection_allow => p_isir_rec.income_protection_allow,
1017 x_available_income => p_isir_rec.available_income,
1018 x_contribution_from_ai => p_isir_rec.contribution_from_ai,
1019 x_discretionary_networth => p_isir_rec.discretionary_networth,
1020 x_efc_networth => p_isir_rec.efc_networth,
1021 x_asset_protect_allow => p_isir_rec.asset_protect_allow,
1022 x_parents_cont_from_assets => p_isir_rec.parents_cont_from_assets,
1023 x_adjusted_available_income => p_isir_rec.adjusted_available_income,
1024 x_total_student_contribution => p_isir_rec.total_student_contribution,
1025 x_total_parent_contribution => p_isir_rec.total_parent_contribution,
1026 x_parents_contribution => p_isir_rec.parents_contribution,
1027 x_student_total_income => p_isir_rec.student_total_income,
1028 x_sati => p_isir_rec.sati,
1029 x_sic => p_isir_rec.sic,
1030 x_sdnw => p_isir_rec.sdnw,
1031 x_sca => p_isir_rec.sca,
1032 x_fti => p_isir_rec.fti,
1033 x_secti => p_isir_rec.secti,
1034 x_secati => p_isir_rec.secati,
1035 x_secstx => p_isir_rec.secstx,
1036 x_secea => p_isir_rec.secea,
1037 x_secipa => p_isir_rec.secipa,
1038 x_secai => p_isir_rec.secai,
1039 x_seccai => p_isir_rec.seccai,
1040 x_secdnw => p_isir_rec.secdnw,
1041 x_secnw => p_isir_rec.secnw,
1042 x_secapa => p_isir_rec.secapa,
1043 x_secpca => p_isir_rec.secpca,
1044 x_secaai => p_isir_rec.secaai,
1045 x_sectsc => p_isir_rec.sectsc,
1046 x_sectpc => p_isir_rec.sectpc,
1047 x_secpc => p_isir_rec.secpc,
1048 x_secsti => p_isir_rec.secsti,
1049 x_secsati => p_isir_rec.secsati,
1050 x_secsic => p_isir_rec.secsic,
1051 x_secsdnw => p_isir_rec.secsdnw,
1052 x_secsca => p_isir_rec.secsca,
1053 x_secfti => p_isir_rec.secfti,
1054 x_a_citizenship => p_isir_rec.a_citizenship,
1055 x_a_student_marital_status => p_isir_rec.a_student_marital_status,
1056 x_a_student_agi => p_isir_rec.a_student_agi,
1057 x_a_s_us_tax_paid => p_isir_rec.a_s_us_tax_paid,
1058 x_a_s_income_work => p_isir_rec.a_s_income_work,
1059 x_a_spouse_income_work => p_isir_rec.a_spouse_income_work,
1060 x_a_s_total_wsc => p_isir_rec.a_s_total_wsc,
1061 x_a_date_of_birth => p_isir_rec.a_date_of_birth,
1062 x_a_student_married => p_isir_rec.a_student_married,
1063 x_a_have_children => p_isir_rec.a_have_children,
1064 x_a_s_have_dependents => p_isir_rec.a_s_have_dependents,
1065 x_a_va_status => p_isir_rec.a_va_status,
1066 x_a_s_num_in_family => p_isir_rec.a_s_num_in_family,
1067 x_a_s_num_in_college => p_isir_rec.a_s_num_in_college,
1068 x_a_p_marital_status => p_isir_rec.a_p_marital_status,
1069 x_a_father_ssn => p_isir_rec.a_father_ssn,
1070 x_a_mother_ssn => p_isir_rec.a_mother_ssn,
1071 x_a_parents_num_family => p_isir_rec.a_parents_num_family,
1072 x_a_parents_num_college => p_isir_rec.a_parents_num_college,
1073 x_a_parents_agi => p_isir_rec.a_parents_agi,
1074 x_a_p_us_tax_paid => p_isir_rec.a_p_us_tax_paid,
1075 x_a_f_work_income => p_isir_rec.a_f_work_income,
1076 x_a_m_work_income => p_isir_rec.a_m_work_income,
1077 x_a_p_total_wsc => p_isir_rec.a_p_total_wsc,
1078 x_comment_codes => p_isir_rec.comment_codes,
1079 x_sar_ack_comm_code => p_isir_rec.sar_ack_comm_code,
1080 x_pell_grant_elig_flag => p_isir_rec.pell_grant_elig_flag,
1081 x_reprocess_reason_code => p_isir_rec.reprocess_reason_code,
1082 x_duplicate_date => p_isir_rec.duplicate_date,
1083 x_isir_transaction_type => p_isir_rec.isir_transaction_type,
1084 x_fedral_schl_code_indicator => p_isir_rec.fedral_schl_code_indicator,
1085 x_multi_school_code_flags => p_isir_rec.multi_school_code_flags,
1086 x_dup_ssn_indicator => p_isir_rec.dup_ssn_indicator,
1087 x_payment_isir => p_isir_rec.payment_isir,
1088 x_receipt_status => p_isir_rec.receipt_status,
1089 x_isir_receipt_completed => p_isir_rec.isir_receipt_completed,
1090 x_system_record_type => p_isir_rec.system_record_type,
1091 x_verif_track_flag => p_isir_rec.verif_track_flag,
1092 x_active_isir => p_isir_rec.active_isir,
1093 x_fafsa_data_verify_flags => p_isir_rec.fafsa_data_verify_flags,
1094 x_reject_override_a => p_isir_rec.reject_override_a,
1095 x_reject_override_c => p_isir_rec.reject_override_c,
1096 x_parent_marital_status_date => p_isir_rec.parent_marital_status_date,
1097 x_mode => 'R',
1098 x_legacy_record_flag => NULL,
1099 x_father_first_name_initial => p_isir_rec.father_first_name_initial_txt ,
1100 x_father_step_father_birth_dt => p_isir_rec.father_step_father_birth_date ,
1101 x_mother_first_name_initial => p_isir_rec.mother_first_name_initial_txt ,
1102 x_mother_step_mother_birth_dt => p_isir_rec.mother_step_mother_birth_date ,
1103 x_parents_email_address_txt => p_isir_rec.parents_email_address_txt ,
1104 x_address_change_type => p_isir_rec.address_change_type ,
1105 x_cps_pushed_isir_flag => p_isir_rec.cps_pushed_isir_flag ,
1106 x_electronic_transaction_type => p_isir_rec.electronic_transaction_type ,
1107 x_sar_c_change_type => p_isir_rec.sar_c_change_type ,
1108 x_father_ssn_match_type => p_isir_rec.father_ssn_match_type ,
1109 x_mother_ssn_match_type => p_isir_rec.mother_ssn_match_type ,
1110 x_reject_override_g_flag => p_isir_rec.reject_override_g_flag,
1111 x_dhs_verification_num_txt => p_isir_rec.dhs_verification_num_txt ,
1112 x_data_file_name_txt => p_isir_rec.data_file_name_txt ,
1113 x_message_class_txt => p_isir_rec.message_class_txt ,
1114 x_reject_override_3_flag => p_isir_rec.reject_override_3_flag,
1115 x_reject_override_12_flag => p_isir_rec.reject_override_12_flag,
1116 x_reject_override_j_flag => p_isir_rec.reject_override_j_flag,
1117 x_reject_override_k_flag => p_isir_rec.reject_override_k_flag,
1118 x_rejected_status_change_flag => p_isir_rec.rejected_status_change_flag,
1119 x_verification_selection_flag => p_isir_rec.verification_selection_flag
1120 ) ;
1121 END update_isir;
1122
1123
1124 PROCEDURE save_as_correction_isir(
1125 p_org_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
1126 p_mod_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
1127 p_cal_type IN igf_ap_fa_base_rec_all.ci_cal_type%TYPE,
1128 p_sequence_number IN igf_ap_fa_base_rec_all.ci_sequence_number%TYPE,
1129 p_corr_status IN VARCHAR2 ,
1130 x_msg_count OUT NOCOPY NUMBER,
1131 x_msg_data OUT NOCOPY VARCHAR2,
1132 x_return_status OUT NOCOPY VARCHAR2,
1133 p_msg_name OUT NOCOPY VARCHAR2
1134 ) IS
1135
1136 ------------------------------------------------------------------
1137 --Created by : brajendr
1138 --Date created: 17-Feb-2003
1139 --
1140 --Purpose:
1141 --
1142 --
1143 --Known limitations/enhancements and/or remarks:
1144 --
1145 --Change History:
1146 --Who When What
1147 -------------------------------------------------------------------
1148
1149 -- Get the details of ISIR Record from isir_matched table
1150 CURSOR c_isir_detials(
1151 p_isir_id igf_ap_isir_matched_all.isir_id%TYPE
1152 ) IS
1153 SELECT *
1154 FROM igf_ap_isir_matched
1155 WHERE isir_id = p_isir_id;
1156
1157 -- Get the details of
1158 CURSOR c_get_pymt_isir(
1159 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1160 ) IS
1161 SELECT isir_id
1162 FROM igf_ap_isir_matched
1163 WHERE base_id = c_base_id
1164 AND system_record_type = 'ORIGINAL'
1165 AND payment_isir = 'Y' ;
1166
1167 -- Get the details of
1168 CURSOR c_get_active_isir(
1169 c_base_id igf_ap_fa_base_rec_all.base_id%TYPE
1170 ) IS
1171 SELECT *
1172 FROM igf_ap_isir_matched
1173 WHERE base_id = c_base_id
1174 AND active_isir = 'Y';
1175
1176
1177 isir_detials_rec igf_ap_isir_matched%ROWTYPE;
1178 corr_detials_rec igf_ap_isir_matched%ROWTYPE;
1179 l_payment_isir_id igf_ap_isir_matched_all.isir_id%TYPE;
1180 isir_row_id ROWID;
1181 l_message VARCHAR2(50);
1182 l_old_active_isir_id igf_ap_isir_matched_all.isir_id%TYPE;
1183 l_anticip_status VARCHAR2(30);
1184 l_awd_prc_status VARCHAR2(30);
1185
1186
1187 BEGIN
1188
1189 SAVEPOINT SP_ISIR;
1190
1191 FND_MSG_PUB.initialize;
1192 x_return_status := FND_API.G_RET_STS_SUCCESS;
1193
1194 -- Fetch the modified ISIR details (Internal ISIR Record)
1195 OPEN c_isir_detials( p_mod_isir_id);
1196 FETCH c_isir_detials INTO isir_detials_rec;
1197 CLOSE c_isir_detials;
1198
1199 -- Fetch the Correction ISIR details
1200 OPEN c_isir_detials( p_org_isir_id);
1201 FETCH c_isir_detials INTO corr_detials_rec;
1202 CLOSE c_isir_detials;
1203
1204 l_old_active_isir_id := NULL;
1205
1206 -- make the existing Active ISIR = 'N'
1207 FOR c_get_active_isir_rec IN c_get_active_isir(corr_detials_rec.base_id) LOOP
1208 c_get_active_isir_rec.active_isir := 'N';
1209 l_old_active_isir_id := c_get_active_isir_rec.isir_id;
1210 update_isir( c_get_active_isir_rec, c_get_active_isir_rec.isir_id, c_get_active_isir_rec.row_id);
1211 END LOOP;
1212
1213 isir_detials_rec.system_record_type := 'CORRECTION';
1214 isir_detials_rec.isir_transaction_type := 'C';
1215 isir_detials_rec.active_isir := 'Y';
1216 isir_detials_rec.message_class_txt := NULL; -- Passing NULL as the record is created internally and not imported from external system
1217 l_anticip_status := NULL;
1218 l_awd_prc_status := NULL;
1219
1220
1221 -- Correction ISIR record is alredy exits, then overwrite the existing correction ISIR with the modified values
1222 IF corr_detials_rec.system_record_type = 'CORRECTION' THEN
1223
1224 update_isir( isir_detials_rec, p_org_isir_id, corr_detials_rec.row_id);
1225
1226 -- Update the anticipated data and Award process status data.
1227 igf_ap_isir_gen_pkg.upd_ant_data_awd_prc_status( p_old_active_isir_id => l_old_active_isir_id,
1228 p_new_active_isir_id => p_org_isir_id,
1229 p_upd_ant_val => 'Y',
1230 p_anticip_status => l_anticip_status,
1231 p_awd_prc_status => l_awd_prc_status
1232 );
1233
1234
1235 -- Correction ISIR is not present in the system
1236 ELSE
1237
1238 update_isir( isir_detials_rec, p_mod_isir_id, isir_detials_rec.row_id);
1239
1240 -- Update the anticipated data and Award process status data.
1241 igf_ap_isir_gen_pkg.upd_ant_data_awd_prc_status( p_old_active_isir_id => l_old_active_isir_id,
1242 p_new_active_isir_id => p_mod_isir_id,
1243 p_upd_ant_val => 'Y',
1244 p_anticip_status => l_anticip_status,
1245 p_awd_prc_status => l_awd_prc_status
1246 );
1247
1248
1249 END IF;
1250
1251 OPEN c_get_pymt_isir( isir_detials_rec.base_id);
1252 FETCH c_get_pymt_isir INTO l_payment_isir_id;
1253 CLOSE c_get_pymt_isir;
1254
1255 -- Bug 3598933
1256 IF l_payment_isir_id is NULL THEN
1257 -- Student does not have a Payment ISIR
1258 -- Hence Create Correction Records against the Initial ISIR Being Saved as the Correction Rec.
1259 l_payment_isir_id := p_org_isir_id;
1260
1261 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1262 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_ss_pkg.save_as_correction_isir.debug','Corrections generated against Non Payment Original ISIR');
1263 END IF;
1264
1265 END IF;
1266
1267
1268 --Compare the Correction ISIR Created now with the Payment ISIR
1269 igf_aw_gen_002.compare_isirs(l_payment_isir_id, p_mod_isir_id, p_cal_type, p_sequence_number,p_corr_status);
1270
1271 fnd_msg_pub.count_and_get(
1272 p_encoded => fnd_api.g_false,
1273 p_count => x_msg_count,
1274 p_data => x_msg_data
1275 );
1276
1277 -- If the Current SSN values are changed then Make a call to IGF_GR_GEN
1278 IF corr_detials_rec.current_ssn <> isir_detials_rec.current_ssn THEN
1279 igf_gr_gen.update_current_ssn(
1280 isir_detials_rec.base_id,
1281 isir_detials_rec.current_ssn,
1282 l_message
1283 );
1284 IF l_message = 'IGF_GR_UPDT_SSN_FAIL' THEN
1285 x_return_status := 'W';
1286 p_msg_name := 'IGF_GR_UPDT_SSN_FAIL';
1287 END IF;
1288 ELSE
1289 p_msg_name := NULL;
1290 END IF;
1291
1292 EXCEPTION
1293 WHEN OTHERS THEN
1294
1295 ROLLBACK TO SP_ISIR;
1296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1297 fnd_msg_pub.count_and_get(
1298 p_encoded => fnd_api.g_false,
1299 p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302
1303 END save_as_correction_isir;
1304
1305
1306 PROCEDURE create_simulation_isir(
1307 p_mod_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
1308 x_msg_count OUT NOCOPY NUMBER,
1309 x_msg_data OUT NOCOPY VARCHAR2,
1310 x_return_status OUT NOCOPY VARCHAR2
1311 ) IS
1312 ------------------------------------------------------------------
1313 --Created by : brajendr
1314 --Date created: 17-Feb-2003
1315 --
1316 --Purpose:
1317 --
1318 --
1319 --Known limitations/enhancements and/or remarks:
1320 --
1321 --Change History:
1322 --Who When What
1323 -------------------------------------------------------------------
1324
1325 -- Get the details of ISIR Record from isir_matched table
1326 CURSOR c_isir_detials(
1327 p_isir_id igf_ap_isir_matched_all.isir_id%TYPE
1328 ) IS
1329 SELECT *
1330 FROM igf_ap_isir_matched
1331 WHERE isir_id = p_isir_id;
1332
1333 --Cursor to get the Max Transaction Number from Prev SIMULATION ISIR Ids
1334 CURSOR cur_get_max_trans( c_base_id igf_ap_fa_base_rec_all.base_id%TYPE) IS
1335 SELECT NVL(MAX(NVL(transaction_num,0)),0)+1
1336 FROM igf_ap_isir_matched
1337 WHERE system_record_type='SIMULATION'
1338 AND base_id = c_base_id;
1339
1340 l_trans igf_ap_isir_matched.transaction_num%TYPE;
1341 isir_detials_rec igf_ap_isir_matched%ROWTYPE;
1342
1343 BEGIN
1344
1345 SAVEPOINT SP_ISIR;
1346
1347 FND_MSG_PUB.initialize;
1348 x_return_status := FND_API.G_RET_STS_SUCCESS;
1349
1350 -- Fetch the modified ISIR details
1351 OPEN c_isir_detials( p_mod_isir_id);
1352 FETCH c_isir_detials INTO isir_detials_rec;
1353 CLOSE c_isir_detials;
1354
1355 --Arrive at the Transaction Number for the new simulation record to be inserted
1356 OPEN cur_get_max_trans(isir_detials_rec.base_id);
1357 FETCH cur_get_max_trans INTO l_trans;
1358 CLOSE cur_get_max_trans;
1359
1360 --Do not proceed further if transaction number >99
1361 IF l_trans > 99 THEN
1362 fnd_message.set_name('IGF','IGF_AP_SIM_ISIR_MAX');
1363 fnd_msg_pub.add;
1364 x_return_status := fnd_api.g_ret_sts_error;
1365
1366 ELSE
1367
1368 isir_detials_rec.transaction_num:=LPAD(l_trans,2,'0');
1369 isir_detials_rec.system_record_type := 'SIMULATION';
1370 isir_detials_rec.isir_transaction_type := 'S';
1371 isir_detials_rec.active_isir := 'N';
1372 isir_detials_rec.message_class_txt := NULL; -- Passing NULL as the record is created internally and not imported from external system
1373
1374 update_isir( isir_detials_rec, p_mod_isir_id, isir_detials_rec.row_id);
1375
1376 END IF;
1377
1378 fnd_msg_pub.count_and_get(
1379 p_encoded => fnd_api.g_false,
1380 p_count => x_msg_count,
1381 p_data => x_msg_data
1382 );
1383
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386
1387 ROLLBACK TO SP_ISIR;
1388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1389 fnd_msg_pub.count_and_get(
1390 p_encoded => fnd_api.g_false,
1391 p_count => x_msg_count,
1392 p_data => x_msg_data
1393 );
1394
1395 END create_simulation_isir;
1396
1397
1398 PROCEDURE compute_efc(
1399 p_isir_id IN igf_ap_isir_matched_all.isir_id%TYPE,
1400 p_system_award_year IN VARCHAR2,
1401 p_ignore_warnings IN VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 x_return_status OUT NOCOPY VARCHAR2
1405 ) IS
1406 /*
1407 || Created By : masehgal
1408 || Created On : 18-Feb-2003
1409 || Purpose : To be used for all self service wrappers to do efc calculations
1410 || Known limitations, enhancements or remarks :
1411 || Change History :
1412 || Who When What
1413 || masehgal 04-Mar-2003 # 2826603 Storing intermediate values for both
1414 || Success ( "S" ) and Warning ( "W" )
1415 || (reverse chronological order - newest change first)
1416 */
1417
1418 CURSOR cur_isir_rec ( cp_isir_id igf_ap_isir_matched_all.isir_id%TYPE ) IS
1419 SELECT isir.*
1420 FROM igf_ap_isir_matched isir
1421 WHERE isir_id = cp_isir_id ;
1422
1423 isir_rec cur_isir_rec%ROWTYPE ;
1424 l_msg_str VARCHAR2(20000) := '';
1425
1426 -- Bug 4950206: Moved the follwing commented code to set_internal_isir procedure
1427 -- CURSOR cur_get_ver_data (pn_base_id number) is
1428 -- SELECT lkup.lookup_code col_name,
1429 -- verf.item_value col_val
1430 -- FROM igf_ap_inst_ver_item verf ,
1431 -- igf_ap_fa_base_rec_all fabase ,
1432 -- igf_ap_batch_aw_map map ,
1433 -- igf_fc_sar_cd_mst sar ,
1434 -- igf_lookups_view lkup
1435 -- WHERE fabase.base_id = verf.base_id
1436 -- AND verf.udf_vern_item_seq_num = 1
1437 -- AND map.ci_cal_type = fabase.ci_cal_type
1438 -- AND map.ci_sequence_number = fabase.ci_sequence_number
1439 -- AND sar.sys_award_year = map.sys_award_year
1440 -- AND sar.sar_field_number = verf.isir_map_col
1441 -- AND lkup.lookup_type = 'IGF_AP_SAR_FIELD_MAP'
1442 -- AND lkup.lookup_code = sar.sar_field_name
1443 -- AND NVL(verf.waive_flag,'N') = 'N'
1444 -- AND ((verf.item_value IS NOT NULL) OR (verf.item_value IS NULL AND verf.use_blank_flag = 'Y'))
1445 -- AND verf.base_id = pn_base_id ;
1446
1447 -- l_get_ver_data_rec cur_get_ver_data%ROWTYPE;
1448
1449 BEGIN
1450
1451 FND_MSG_PUB.initialize;
1452 x_return_status := FND_API.G_RET_STS_SUCCESS;
1453
1454 SAVEPOINT SP_ISIR;
1455
1456 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1457 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Starting, Completed initializing the message pub and SAVEPOINT');
1458 END IF;
1459
1460 OPEN cur_isir_rec ( p_isir_id ) ;
1461 FETCH cur_isir_rec INTO isir_rec ;
1462 CLOSE cur_isir_rec ;
1463
1464 -- Bug 4752938: Moved the follwing commented code to set_internal_isir procedure
1465 -- Initialize the package variable with the isir rec values
1466 --igf_ap_batch_ver_prc_pkg.lp_isir_rec := isir_rec;
1467 --IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1468 -- FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','fetched ISIR Detials for ISIR ID: '||isir_rec.isir_id);
1469 --END IF;
1470
1471 --OPEN cur_get_ver_data ( isir_rec.base_id ) ;
1472 --LOOP
1473 -- FETCH cur_get_ver_data INTO l_get_ver_data_rec ;
1474 -- EXIT WHEN cur_get_ver_data%NOTFOUND;
1475
1476 -- IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1477 -- FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','fetching verification items Item Name: '||l_get_ver_data_rec.col_name||' value:'||l_get_ver_data_rec.col_val);
1478 -- END IF;
1479
1480 -- EXECUTE IMMEDIATE 'BEGIN igf_ap_batch_ver_prc_pkg.lp_isir_rec.'
1481 -- || l_get_ver_data_rec.col_name || ' := ' || '''' || l_get_ver_data_rec.col_val || '''' || ' ; END;' ;
1482 --END LOOP;
1483 --CLOSE cur_get_ver_data ;
1484
1485 --isir_rec := igf_ap_batch_ver_prc_pkg.lp_isir_rec;
1486
1487 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1488 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Before calling main Calculate EFC routine with Ignore Warnings:'||p_ignore_warnings);
1489 END IF;
1490
1491 igf_ap_efc_calc.calculate_efc(
1492 p_isir_rec => isir_rec,
1493 p_ignore_warnings => p_ignore_warnings,
1494 p_sys_batch_yr => p_system_award_year,
1495 p_return_status => x_return_status
1496 );
1497
1498 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1499 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','After calling main Calculate EFC routine Return Status:'||x_return_status);
1500 END IF;
1501
1502 -- masehgal # 2826603
1503 -- Storing intermediate values for both Success ( "S" ) and Warning ( "W" )
1504 IF x_return_status IN ('S','W') THEN
1505
1506 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1507 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Updating ISIR Rec since the return status is either W or S');
1508 END IF;
1509
1510 igf_ap_isir_matched_pkg.update_row(
1511 x_rowid => isir_rec.row_id,
1512 x_isir_id => p_isir_id,
1513 x_base_id => isir_rec.base_id,
1514 x_batch_year => isir_rec.batch_year,
1515 x_transaction_num => isir_rec.transaction_num,
1516 x_current_ssn => isir_rec.current_ssn,
1517 x_ssn_name_change => isir_rec.ssn_name_change,
1518 x_original_ssn => isir_rec.original_ssn,
1519 x_orig_name_id => isir_rec.orig_name_id,
1520 x_last_name => isir_rec.last_name,
1521 x_first_name => isir_rec.first_name,
1522 x_middle_initial => isir_rec.middle_initial,
1523 x_perm_mail_add => isir_rec.perm_mail_add,
1524 x_perm_city => isir_rec.perm_city,
1525 x_perm_state => isir_rec.perm_state,
1526 x_perm_zip_code => isir_rec.perm_zip_code,
1527 x_date_of_birth => isir_rec.date_of_birth,
1528 x_phone_number => isir_rec.phone_number,
1529 x_driver_license_number => isir_rec.driver_license_number,
1530 x_driver_license_state => isir_rec.driver_license_state,
1531 x_citizenship_status => isir_rec.citizenship_status,
1532 x_alien_reg_number => isir_rec.alien_reg_number,
1533 x_s_marital_status => isir_rec.s_marital_status,
1534 x_s_marital_status_date => isir_rec.s_marital_status_date,
1535 x_summ_enrl_status => isir_rec.summ_enrl_status,
1536 x_fall_enrl_status => isir_rec.fall_enrl_status,
1537 x_winter_enrl_status => isir_rec.winter_enrl_status,
1538 x_spring_enrl_status => isir_rec.spring_enrl_status,
1539 x_summ2_enrl_status => isir_rec.summ2_enrl_status,
1540 x_fathers_highest_edu_level => isir_rec.fathers_highest_edu_level,
1541 x_mothers_highest_edu_level => isir_rec.mothers_highest_edu_level,
1542 x_s_state_legal_residence => isir_rec.s_state_legal_residence,
1543 x_legal_residence_before_date => isir_rec.legal_residence_before_date,
1544 x_s_legal_resd_date => isir_rec.s_legal_resd_date,
1545 x_ss_r_u_male => isir_rec.ss_r_u_male,
1546 x_selective_service_reg => isir_rec.selective_service_reg,
1547 x_degree_certification => isir_rec.degree_certification,
1548 x_grade_level_in_college => isir_rec.grade_level_in_college,
1549 x_high_school_diploma_ged => isir_rec.high_school_diploma_ged,
1550 x_first_bachelor_deg_by_date => isir_rec.first_bachelor_deg_by_date,
1551 x_interest_in_loan => isir_rec.interest_in_loan,
1552 x_interest_in_stud_employment => isir_rec.interest_in_stud_employment,
1553 x_drug_offence_conviction => isir_rec.drug_offence_conviction,
1554 x_s_tax_return_status => isir_rec.s_tax_return_status,
1555 x_s_type_tax_return => isir_rec.s_type_tax_return,
1556 x_s_elig_1040ez => isir_rec.s_elig_1040ez,
1557 x_s_adjusted_gross_income => isir_rec.s_adjusted_gross_income,
1558 x_s_fed_taxes_paid => isir_rec.s_fed_taxes_paid,
1559 x_s_exemptions => isir_rec.s_exemptions,
1560 x_s_income_from_work => isir_rec.s_income_from_work,
1561 x_spouse_income_from_work => isir_rec.spouse_income_from_work,
1562 x_s_toa_amt_from_wsa => isir_rec.s_toa_amt_from_wsa,
1563 x_s_toa_amt_from_wsb => isir_rec.s_toa_amt_from_wsb,
1564 x_s_toa_amt_from_wsc => isir_rec.s_toa_amt_from_wsc,
1565 x_s_investment_networth => isir_rec.s_investment_networth,
1566 x_s_busi_farm_networth => isir_rec.s_busi_farm_networth,
1567 x_s_cash_savings => isir_rec.s_cash_savings,
1568 x_va_months => isir_rec.va_months,
1569 x_va_amount => isir_rec.va_amount,
1570 x_stud_dob_before_date => isir_rec.stud_dob_before_date,
1571 x_deg_beyond_bachelor => isir_rec.deg_beyond_bachelor,
1572 x_s_married => isir_rec.s_married,
1573 x_s_have_children => isir_rec.s_have_children,
1574 x_legal_dependents => isir_rec.legal_dependents,
1575 x_orphan_ward_of_court => isir_rec.orphan_ward_of_court,
1576 x_s_veteran => isir_rec.s_veteran,
1577 x_p_marital_status => isir_rec.p_marital_status,
1578 x_father_ssn => isir_rec.father_ssn,
1579 x_f_last_name => isir_rec.f_last_name,
1580 x_mother_ssn => isir_rec.mother_ssn,
1581 x_m_last_name => isir_rec.m_last_name,
1582 x_p_num_family_member => isir_rec.p_num_family_member,
1583 x_p_num_in_college => isir_rec.p_num_in_college,
1584 x_p_state_legal_residence => isir_rec.p_state_legal_residence,
1585 x_p_state_legal_res_before_dt => isir_rec.p_state_legal_res_before_dt,
1586 x_p_legal_res_date => isir_rec.p_legal_res_date,
1587 x_age_older_parent => isir_rec.age_older_parent,
1588 x_p_tax_return_status => isir_rec.p_tax_return_status,
1589 x_p_type_tax_return => isir_rec.p_type_tax_return,
1590 x_p_elig_1040aez => isir_rec.p_elig_1040aez,
1591 x_p_adjusted_gross_income => isir_rec.p_adjusted_gross_income,
1592 x_p_taxes_paid => isir_rec.p_taxes_paid,
1593 x_p_exemptions => isir_rec.p_exemptions,
1594 x_f_income_work => isir_rec.f_income_work,
1595 x_m_income_work => isir_rec.m_income_work,
1596 x_p_income_wsa => isir_rec.p_income_wsa,
1597 x_p_income_wsb => isir_rec.p_income_wsb,
1598 x_p_income_wsc => isir_rec.p_income_wsc,
1599 x_p_investment_networth => isir_rec.p_investment_networth,
1600 x_p_business_networth => isir_rec.p_business_networth,
1601 x_p_cash_saving => isir_rec.p_cash_saving,
1602 x_s_num_family_members => isir_rec.s_num_family_members,
1603 x_s_num_in_college => isir_rec.s_num_in_college,
1604 x_first_college => isir_rec.first_college,
1605 x_first_house_plan => isir_rec.first_house_plan,
1606 x_second_college => isir_rec.second_college,
1607 x_second_house_plan => isir_rec.second_house_plan,
1608 x_third_college => isir_rec.third_college,
1609 x_third_house_plan => isir_rec.third_house_plan,
1610 x_fourth_college => isir_rec.fourth_college,
1611 x_fourth_house_plan => isir_rec.fourth_house_plan,
1612 x_fifth_college => isir_rec.fifth_college,
1613 x_fifth_house_plan => isir_rec.fifth_house_plan,
1614 x_sixth_college => isir_rec.sixth_college,
1615 x_sixth_house_plan => isir_rec.sixth_house_plan,
1616 x_date_app_completed => isir_rec.date_app_completed,
1617 x_signed_by => isir_rec.signed_by,
1618 x_preparer_ssn => isir_rec.preparer_ssn,
1619 x_preparer_emp_id_number => isir_rec.preparer_emp_id_number,
1620 x_preparer_sign => isir_rec.preparer_sign,
1621 x_transaction_receipt_date => isir_rec.transaction_receipt_date,
1622 x_dependency_override_ind => isir_rec.dependency_override_ind,
1623 x_faa_fedral_schl_code => isir_rec.faa_fedral_schl_code,
1624 x_faa_adjustment => isir_rec.faa_adjustment,
1625 x_input_record_type => isir_rec.input_record_type,
1626 x_serial_number => isir_rec.serial_number,
1627 x_batch_number => isir_rec.batch_number,
1628 x_early_analysis_flag => isir_rec.early_analysis_flag,
1629 x_app_entry_source_code => isir_rec.app_entry_source_code,
1630 x_eti_destination_code => isir_rec.eti_destination_code,
1631 x_reject_override_b => isir_rec.reject_override_b,
1632 x_reject_override_n => isir_rec.reject_override_n,
1633 x_reject_override_w => isir_rec.reject_override_w,
1634 x_assum_override_1 => isir_rec.assum_override_1,
1635 x_assum_override_2 => isir_rec.assum_override_2,
1636 x_assum_override_3 => isir_rec.assum_override_3,
1637 x_assum_override_4 => isir_rec.assum_override_4,
1638 x_assum_override_5 => isir_rec.assum_override_5,
1639 x_assum_override_6 => isir_rec.assum_override_6,
1640 x_dependency_status => isir_rec.dependency_status,
1641 x_s_email_address => isir_rec.s_email_address,
1642 x_nslds_reason_code => isir_rec.nslds_reason_code,
1643 x_app_receipt_date => isir_rec.app_receipt_date,
1644 x_processed_rec_type => isir_rec.processed_rec_type,
1645 x_hist_correction_for_tran_id => isir_rec.hist_correction_for_tran_id,
1646 x_system_generated_indicator => isir_rec.system_generated_indicator,
1647 x_dup_request_indicator => isir_rec.dup_request_indicator,
1648 x_source_of_correction => isir_rec.source_of_correction,
1649 x_p_cal_tax_status => isir_rec.p_cal_tax_status,
1650 x_s_cal_tax_status => isir_rec.s_cal_tax_status,
1651 x_graduate_flag => isir_rec.graduate_flag,
1652 x_auto_zero_efc => isir_rec.auto_zero_efc,
1653 x_efc_change_flag => isir_rec.efc_change_flag,
1654 x_sarc_flag => isir_rec.sarc_flag,
1655 x_simplified_need_test => isir_rec.simplified_need_test,
1656 x_reject_reason_codes => isir_rec.reject_reason_codes,
1657 x_select_service_match_flag => isir_rec.select_service_match_flag,
1658 x_select_service_reg_flag => isir_rec.select_service_reg_flag,
1659 x_ins_match_flag => isir_rec.ins_match_flag,
1660 x_ins_verification_number => NULL,
1661 x_sec_ins_match_flag => isir_rec.sec_ins_match_flag,
1662 x_sec_ins_ver_number => isir_rec.sec_ins_ver_number,
1663 x_ssn_match_flag => isir_rec.ssn_match_flag,
1664 x_ssa_citizenship_flag => isir_rec.ssa_citizenship_flag,
1665 x_ssn_date_of_death => isir_rec.ssn_date_of_death,
1666 x_nslds_match_flag => isir_rec.nslds_match_flag,
1667 x_va_match_flag => isir_rec.va_match_flag,
1668 x_prisoner_match => isir_rec.prisoner_match,
1669 x_verification_flag => isir_rec.verification_flag,
1670 x_subsequent_app_flag => isir_rec.subsequent_app_flag,
1671 x_app_source_site_code => isir_rec.app_source_site_code,
1672 x_tran_source_site_code => isir_rec.tran_source_site_code,
1673 x_drn => isir_rec.drn,
1674 x_tran_process_date => isir_rec.tran_process_date,
1675 x_correction_flags => isir_rec.correction_flags,
1676 x_computer_batch_number => isir_rec.computer_batch_number,
1677 x_highlight_flags => isir_rec.highlight_flags,
1678 x_paid_efc => NULL,
1679 x_primary_efc => isir_rec.primary_efc,
1680 x_secondary_efc => isir_rec.secondary_efc,
1681 x_fed_pell_grant_efc_type => NULL,
1682 x_primary_efc_type => isir_rec.primary_efc_type,
1683 x_sec_efc_type => isir_rec.sec_efc_type,
1684 x_primary_alternate_month_1 => isir_rec.primary_alternate_month_1,
1685 x_primary_alternate_month_2 => isir_rec.primary_alternate_month_2,
1686 x_primary_alternate_month_3 => isir_rec.primary_alternate_month_3,
1687 x_primary_alternate_month_4 => isir_rec.primary_alternate_month_4,
1688 x_primary_alternate_month_5 => isir_rec.primary_alternate_month_5,
1689 x_primary_alternate_month_6 => isir_rec.primary_alternate_month_6,
1690 x_primary_alternate_month_7 => isir_rec.primary_alternate_month_7,
1691 x_primary_alternate_month_8 => isir_rec.primary_alternate_month_8,
1692 x_primary_alternate_month_10 => isir_rec.primary_alternate_month_10,
1693 x_primary_alternate_month_11 => isir_rec.primary_alternate_month_11,
1694 x_primary_alternate_month_12 => isir_rec.primary_alternate_month_12,
1695 x_sec_alternate_month_1 => isir_rec.sec_alternate_month_1,
1696 x_sec_alternate_month_2 => isir_rec.sec_alternate_month_2,
1697 x_sec_alternate_month_3 => isir_rec.sec_alternate_month_3,
1698 x_sec_alternate_month_4 => isir_rec.sec_alternate_month_4,
1699 x_sec_alternate_month_5 => isir_rec.sec_alternate_month_5,
1700 x_sec_alternate_month_6 => isir_rec.sec_alternate_month_6,
1701 x_sec_alternate_month_7 => isir_rec.sec_alternate_month_7,
1702 x_sec_alternate_month_8 => isir_rec.sec_alternate_month_8,
1703 x_sec_alternate_month_10 => isir_rec.sec_alternate_month_10,
1704 x_sec_alternate_month_11 => isir_rec.sec_alternate_month_11,
1705 x_sec_alternate_month_12 => isir_rec.sec_alternate_month_12,
1706 x_total_income => isir_rec.total_income,
1707 x_allow_total_income => isir_rec.allow_total_income,
1708 x_state_tax_allow => isir_rec.state_tax_allow,
1709 x_employment_allow => isir_rec.employment_allow,
1710 x_income_protection_allow => isir_rec.income_protection_allow,
1711 x_available_income => isir_rec.available_income,
1712 x_contribution_from_ai => isir_rec.contribution_from_ai,
1713 x_discretionary_networth => isir_rec.discretionary_networth,
1714 x_efc_networth => isir_rec.efc_networth,
1715 x_asset_protect_allow => isir_rec.asset_protect_allow,
1716 x_parents_cont_from_assets => isir_rec.parents_cont_from_assets,
1717 x_adjusted_available_income => isir_rec.adjusted_available_income,
1718 x_total_student_contribution => isir_rec.total_student_contribution,
1719 x_total_parent_contribution => isir_rec.total_parent_contribution,
1720 x_parents_contribution => isir_rec.parents_contribution,
1721 x_student_total_income => isir_rec.student_total_income,
1722 x_sati => isir_rec.sati,
1723 x_sic => isir_rec.sic,
1724 x_sdnw => isir_rec.sdnw,
1725 x_sca => isir_rec.sca,
1726 x_fti => isir_rec.fti,
1727 x_secti => isir_rec.secti,
1728 x_secati => isir_rec.secati,
1729 x_secstx => isir_rec.secstx,
1730 x_secea => isir_rec.secea,
1731 x_secipa => isir_rec.secipa,
1732 x_secai => isir_rec.secai,
1733 x_seccai => isir_rec.seccai,
1734 x_secdnw => isir_rec.secdnw,
1735 x_secnw => isir_rec.secnw,
1736 x_secapa => isir_rec.secapa,
1737 x_secpca => isir_rec.secpca,
1738 x_secaai => isir_rec.secaai,
1739 x_sectsc => isir_rec.sectsc,
1740 x_sectpc => isir_rec.sectpc,
1741 x_secpc => isir_rec.secpc,
1742 x_secsti => isir_rec.secsti,
1743 x_secsati => isir_rec.secsati,
1744 x_secsic => isir_rec.secsic,
1745 x_secsdnw => isir_rec.secsdnw,
1746 x_secsca => isir_rec.secsca,
1747 x_secfti => isir_rec.secfti,
1748 x_a_citizenship => isir_rec.a_citizenship,
1749 x_a_student_marital_status => isir_rec.a_student_marital_status,
1750 x_a_student_agi => isir_rec.a_student_agi,
1751 x_a_s_us_tax_paid => isir_rec.a_s_us_tax_paid,
1752 x_a_s_income_work => isir_rec.a_s_income_work,
1753 x_a_spouse_income_work => isir_rec.a_spouse_income_work,
1754 x_a_s_total_wsc => isir_rec.a_s_total_wsc,
1755 x_a_date_of_birth => isir_rec.a_date_of_birth,
1756 x_a_student_married => isir_rec.a_student_married,
1757 x_a_have_children => isir_rec.a_have_children,
1758 x_a_s_have_dependents => isir_rec.a_s_have_dependents,
1759 x_a_va_status => isir_rec.a_va_status,
1760 x_a_s_num_in_family => isir_rec.a_s_num_in_family,
1761 x_a_s_num_in_college => isir_rec.a_s_num_in_college,
1762 x_a_p_marital_status => isir_rec.a_p_marital_status,
1763 x_a_father_ssn => isir_rec.a_father_ssn,
1764 x_a_mother_ssn => isir_rec.a_mother_ssn,
1765 x_a_parents_num_family => isir_rec.a_parents_num_family,
1766 x_a_parents_num_college => isir_rec.a_parents_num_college,
1767 x_a_parents_agi => isir_rec.a_parents_agi,
1768 x_a_p_us_tax_paid => isir_rec.a_p_us_tax_paid,
1769 x_a_f_work_income => isir_rec.a_f_work_income,
1770 x_a_m_work_income => isir_rec.a_m_work_income,
1771 x_a_p_total_wsc => isir_rec.a_p_total_wsc,
1772 x_comment_codes => isir_rec.comment_codes,
1773 x_sar_ack_comm_code => isir_rec.sar_ack_comm_code,
1774 x_pell_grant_elig_flag => isir_rec.pell_grant_elig_flag,
1775 x_reprocess_reason_code => isir_rec.reprocess_reason_code,
1776 x_duplicate_date => isir_rec.duplicate_date,
1777 x_isir_transaction_type => isir_rec.isir_transaction_type,
1778 x_fedral_schl_code_indicator => isir_rec.fedral_schl_code_indicator,
1779 x_multi_school_code_flags => isir_rec.multi_school_code_flags,
1780 x_dup_ssn_indicator => isir_rec.dup_ssn_indicator,
1781 x_payment_isir => isir_rec.payment_isir,
1782 x_receipt_status => isir_rec.receipt_status,
1783 x_isir_receipt_completed => isir_rec.isir_receipt_completed,
1784 x_system_record_type => isir_rec.system_record_type,
1785 x_verif_track_flag => isir_rec.verif_track_flag,
1786 x_active_isir => isir_rec.active_isir,
1787 x_fafsa_data_verify_flags => isir_rec.fafsa_data_verify_flags,
1788 x_reject_override_a => isir_rec.reject_override_a,
1789 x_reject_override_c => isir_rec.reject_override_c,
1790 x_parent_marital_status_date => isir_rec.parent_marital_status_date,
1791 x_mode => 'R',
1792 x_legacy_record_flag => NULL,
1793 x_father_first_name_initial => isir_rec.father_first_name_initial_txt ,
1794 x_father_step_father_birth_dt => isir_rec.father_step_father_birth_date ,
1795 x_mother_first_name_initial => isir_rec.mother_first_name_initial_txt ,
1796 x_mother_step_mother_birth_dt => isir_rec.mother_step_mother_birth_date ,
1797 x_parents_email_address_txt => isir_rec.parents_email_address_txt ,
1798 x_address_change_type => isir_rec.address_change_type ,
1799 x_cps_pushed_isir_flag => isir_rec.cps_pushed_isir_flag ,
1800 x_electronic_transaction_type => isir_rec.electronic_transaction_type ,
1801 x_sar_c_change_type => isir_rec.sar_c_change_type ,
1802 x_father_ssn_match_type => isir_rec.father_ssn_match_type ,
1803 x_mother_ssn_match_type => isir_rec.mother_ssn_match_type ,
1804 x_reject_override_g_flag => isir_rec.reject_override_g_flag,
1805 x_dhs_verification_num_txt => isir_rec.dhs_verification_num_txt ,
1806 x_data_file_name_txt => isir_rec.data_file_name_txt ,
1807 x_message_class_txt => isir_rec.message_class_txt ,
1808 x_reject_override_3_flag => isir_rec.reject_override_3_flag,
1809 x_reject_override_12_flag => isir_rec.reject_override_12_flag,
1810 x_reject_override_j_flag => isir_rec.reject_override_j_flag,
1811 x_reject_override_k_flag => isir_rec.reject_override_k_flag,
1812 x_rejected_status_change_flag => isir_rec.rejected_status_change_flag,
1813 x_verification_selection_flag => isir_rec.verification_selection_flag
1814 ) ;
1815 END IF; -- End of Return status 'S'/'W'
1816
1817 IF x_return_status = 'W' THEN
1818 FOR i IN 1..fnd_msg_pub.count_msg LOOP
1819 l_msg_str := l_msg_str ||' '|| to_char(i) ||'. '|| fnd_msg_pub.get(i,'F');
1820 END LOOP;
1821
1822
1823 -- Append the last Message
1824 fnd_message.SET_NAME('IGF','IGF_AP_SUPRESS_REJ_CODE');
1825 l_msg_str := l_msg_str ||' '|| fnd_message.GET;
1826
1827
1828 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1829 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Wrapping Warning messages, l_msg_str:'||l_msg_str);
1830 END IF;
1831
1832 ELSE
1833
1834 fnd_msg_pub.count_and_get(
1835 p_encoded => fnd_api.g_false,
1836 p_count => x_msg_count,
1837 p_data => l_msg_str
1838 );
1839 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1840 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Wrapping Error messages, l_msg_str:'||l_msg_str);
1841 END IF;
1842
1843 END IF;
1844
1845 x_msg_data := l_msg_str;
1846
1847 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1848 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc','Returning back to SS page');
1849 END IF;
1850
1851 EXCEPTION
1852
1853 WHEN FND_API.G_EXC_ERROR THEN
1854 ROLLBACK TO SP_ISIR;
1855 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1856 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc.exception:FND_API.G_EXC_ERROR',SQLERRM);
1857 END IF;
1858 x_return_status := FND_API.G_RET_STS_ERROR;
1859 fnd_msg_pub.count_and_get(
1860 p_count => x_msg_count,
1861 p_data => x_msg_data
1862 );
1863
1864 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1865 ROLLBACK TO SP_ISIR;
1866 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1867 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc.exception:FND_API.G_EXC_UNEXPECTED_ERROR',SQLERRM);
1868 END IF;
1869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1870 fnd_msg_pub.count_and_get(
1871 p_count => x_msg_count,
1872 p_data => x_msg_data
1873 );
1874
1875 WHEN OTHERS THEN
1876 ROLLBACK TO SP_ISIR;
1877 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1878 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'igf.plsql.igf_ap_ss_pkg.compute_efc.exception:OTHERS',SQLERRM);
1879 END IF;
1880 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1881 fnd_msg_pub.count_and_get(
1882 p_encoded => fnd_api.g_false,
1883 p_count => x_msg_count,
1884 p_data => x_msg_data
1885 );
1886
1887
1888 END compute_efc;
1889
1890
1891 PROCEDURE get_dynamic_dates(
1892 p_sys_award_year IN VARCHAR2,
1893 p_current_year OUT NOCOPY VARCHAR2,
1894 p_next_year OUT NOCOPY VARCHAR2,
1895 p_award_year OUT NOCOPY VARCHAR2,
1896 p_legal_res_dt OUT NOCOPY VARCHAR2,
1897 p_first_bachlor OUT NOCOPY VARCHAR2,
1898 p_born_before OUT NOCOPY VARCHAR2
1899 ) AS
1900 /*
1901 || Created By : brajendr
1902 || Created On : 12-Mar-2003
1903 || Purpose :
1904 || Known limitations, enhancements or remarks :
1905 || Change History :
1906 || Who When What
1907 || (reverse chronological order - newest change first)
1908 */
1909
1910 ld_date DATE;
1911 BEGIN
1912
1913 -- Assign the Default Date
1914 IF p_sys_award_year = '0203' THEN
1915 ld_date := TO_DATE('01/01/2002','DD/MM/YYYY');
1916
1917 ELSIF p_sys_award_year = '0304' THEN
1918 ld_date := TO_DATE('01/01/2003','DD/MM/YYYY');
1919
1920 ELSIF p_sys_award_year = '0405' THEN
1921 ld_date := TO_DATE('01/01/2004','DD/MM/YYYY');
1922
1923 ELSIF p_sys_award_year = '0506' THEN
1924 ld_date := TO_DATE('01/01/2005','DD/MM/YYYY');
1925
1926 ELSIF p_sys_award_year = '0607' THEN
1927 ld_date := TO_DATE('01/01/2006','DD/MM/YYYY');
1928
1929 ELSIF p_sys_award_year = '0708' THEN
1930 ld_date := TO_DATE('01/01/2007','DD/MM/YYYY');
1931
1932 ELSIF p_sys_award_year = '0809' THEN
1933 ld_date := TO_DATE('01/01/2008','DD/MM/YYYY');
1934
1935 END IF;
1936
1937 -- Assign the Dynamic Prompts to the OUt Variables
1938 p_current_year := TO_CHAR( ld_date, 'YYYY');
1939 p_next_year := TO_CHAR( ADD_MONTHS(ld_date, 12), 'YYYY');
1940 p_award_year := p_current_year || '-' || p_next_year;
1941 p_legal_res_dt := fnd_date.date_to_displaydate( ADD_MONTHS( ld_date, -60));
1942 p_first_bachlor := fnd_date.date_to_displaydate( ADD_MONTHS( ld_date, 6));
1943 p_born_before := fnd_date.date_to_displaydate( ADD_MONTHS( ld_date, -276));
1944
1945 END get_dynamic_dates;
1946
1947 PROCEDURE get_internal_isir_id (
1948 p_isir_id IN IGF_AP_ISIR_MATCHED_ALL.ISIR_ID%TYPE,
1949 p_ret_isir_id IN OUT NOCOPY IGF_AP_ISIR_MATCHED_ALL.ISIR_ID%TYPE
1950 )IS
1951 /*
1952 || Created By : hkodali
1953 || Created On : 07-01-2005
1954 || Purpose : To get the ISIR Id of internal ISIR, which will help to know if the Internal ISIR is locked.
1955 || If Internal ISIR id is 0 the no internal ISIR present.
1956 || If Internal ISIR id is -2 , then internal ISIR is locked by another user
1957 || If Internal ISIR present and not locked by anothre user then the actual ISIR Id will be returned.
1958 || Known limitations, enhancements or remarks :
1959 || Change History :
1960 || Who When What
1961 || (reverse chronological order - newest change first)
1962 */
1963
1964 CURSOR c_isir (l_isir_id igf_ap_isir_matched_all.isir_id%type)
1965 IS
1966 SELECT ISIR.rowid row_id,ISIR.*
1967 FROM IGF_AP_ISIR_MATCHED_ALL ISIR
1968 WHERE ISIR.ISIR_ID = l_isir_id ;
1969
1970 lv_isir c_isir%rowtype ;
1971
1972 CURSOR chk_internal (l_base_id igf_ap_isir_matched_all.base_id%type)
1973 IS
1974 SELECT isir.rowid row_id,isir.isir_id FROM IGF_AP_ISIR_MATCHED_ALL ISIR
1975 WHERE
1976 ISIR.BASE_ID = l_base_id AND
1977 NVL(ISIR.SYSTEM_RECORD_TYPE,'X') = 'INTERNAL'
1978 FOR UPDATE NOWAIT;
1979
1980 lv_chk_internal chk_internal%rowtype ;
1981 lv_rowid VARCHAR2(30) ;
1982 pn_isir_id igf_ap_isir_matched_all.isir_id%type ;
1983
1984 BEGIN
1985
1986 OPEN c_isir(p_isir_id) ;
1987 FETCH c_isir INTO lv_isir ;
1988 IF c_isir%FOUND THEN
1989 -- Check if the student has an Internal ISIR
1990 OPEN chk_internal(lv_isir.base_id);
1991 FETCH chk_internal into lv_chk_internal ;
1992 IF chk_internal%FOUND THEN
1993 CLOSE chk_internal ;
1994 p_ret_isir_id := lv_chk_internal.isir_id ;
1995 ELSE
1996 CLOSE chk_internal ;
1997 lv_rowid := null;
1998 pn_isir_id := null;
1999
2000 igf_ap_isir_matched_pkg.insert_row(
2001 x_Mode => 'R',
2002 x_rowid => lv_rowid,
2003 x_isir_id => pn_isir_id,
2004 x_base_id => lv_isir.base_id,
2005 x_batch_year => lv_isir.batch_year ,
2006 x_transaction_num => lv_isir.transaction_num ,
2007 x_current_ssn => lv_isir.current_ssn ,
2008 x_ssn_name_change => lv_isir.ssn_name_change ,
2009 x_original_ssn => lv_isir.original_ssn ,
2010 x_orig_name_id => lv_isir.orig_name_id ,
2011 x_last_name => lv_isir.last_name ,
2012 x_first_name => lv_isir.first_name ,
2013 x_middle_initial => lv_isir.middle_initial ,
2014 x_perm_mail_add => lv_isir.perm_mail_add ,
2015 x_perm_city => lv_isir.perm_city ,
2016 x_perm_state => lv_isir.perm_state ,
2017 x_perm_zip_code => lv_isir.perm_zip_code ,
2018 x_date_of_birth => lv_isir.date_of_birth ,
2019 x_phone_number => lv_isir.phone_number ,
2020 x_driver_license_number => lv_isir.driver_license_number ,
2021 x_driver_license_state => lv_isir.driver_license_state ,
2022 x_citizenship_status => lv_isir.citizenship_status ,
2023 x_alien_reg_number => lv_isir.alien_reg_number ,
2024 x_s_marital_status => lv_isir.s_marital_status ,
2025 x_s_marital_status_date => lv_isir.s_marital_status_date ,
2026 x_summ_enrl_status => lv_isir.summ_enrl_status ,
2027 x_fall_enrl_status => lv_isir.fall_enrl_status ,
2028 x_winter_enrl_status => lv_isir.winter_enrl_status ,
2029 x_spring_enrl_status => lv_isir.spring_enrl_status ,
2030 x_summ2_enrl_status => lv_isir.summ2_enrl_status ,
2031 x_fathers_highest_edu_level => lv_isir.fathers_highest_edu_level ,
2032 x_mothers_highest_edu_level => lv_isir.mothers_highest_edu_level ,
2033 x_s_state_legal_residence => lv_isir.s_state_legal_residence ,
2034 x_legal_residence_before_date => lv_isir.legal_residence_before_date ,
2035 x_s_legal_resd_date => lv_isir.s_legal_resd_date ,
2036 x_ss_r_u_male => lv_isir.ss_r_u_male ,
2037 x_selective_service_reg => lv_isir.selective_service_reg ,
2038 x_degree_certification => lv_isir.degree_certification ,
2039 x_grade_level_in_college => lv_isir.grade_level_in_college ,
2040 x_high_school_diploma_ged => lv_isir.high_school_diploma_ged ,
2041 x_first_bachelor_deg_by_date => lv_isir.first_bachelor_deg_by_date ,
2042 x_interest_in_loan => lv_isir.interest_in_loan ,
2043 x_interest_in_stud_employment => lv_isir.interest_in_stud_employment ,
2044 x_drug_offence_conviction => lv_isir.drug_offence_conviction ,
2045 x_s_tax_return_status => lv_isir.s_tax_return_status ,
2046 x_s_type_tax_return => lv_isir.s_type_tax_return ,
2047 x_s_elig_1040ez => lv_isir.s_elig_1040ez ,
2048 x_s_adjusted_gross_income => lv_isir.s_adjusted_gross_income ,
2049 x_s_fed_taxes_paid => lv_isir.s_fed_taxes_paid ,
2050 x_s_exemptions => lv_isir.s_exemptions ,
2051 x_s_income_from_work => lv_isir.s_income_from_work ,
2052 x_spouse_income_from_work => lv_isir.spouse_income_from_work ,
2053 x_s_toa_amt_from_wsa => lv_isir.s_toa_amt_from_wsa ,
2054 x_s_toa_amt_from_wsb => lv_isir.s_toa_amt_from_wsb ,
2055 x_s_toa_amt_from_wsc => lv_isir.s_toa_amt_from_wsc ,
2056 x_s_investment_networth => lv_isir.s_investment_networth ,
2057 x_s_busi_farm_networth => lv_isir.s_busi_farm_networth ,
2058 x_s_cash_savings => lv_isir.s_cash_savings ,
2059 x_va_months => lv_isir.va_months ,
2060 x_va_amount => lv_isir.va_amount ,
2061 x_stud_dob_before_date => lv_isir.stud_dob_before_date ,
2062 x_deg_beyond_bachelor => lv_isir.deg_beyond_bachelor ,
2063 x_s_married => lv_isir.s_married ,
2064 x_s_have_children => lv_isir.s_have_children ,
2065 x_legal_dependents => lv_isir.legal_dependents ,
2066 x_orphan_ward_of_court => lv_isir.orphan_ward_of_court ,
2067 x_s_veteran => lv_isir.s_veteran ,
2068 x_p_marital_status => lv_isir.p_marital_status ,
2069 x_father_ssn => lv_isir.father_ssn ,
2070 x_f_last_name => lv_isir.f_last_name ,
2071 x_mother_ssn => lv_isir.mother_ssn ,
2072 x_m_last_name => lv_isir.m_last_name ,
2073 x_p_num_family_member => lv_isir.p_num_family_member ,
2074 x_p_num_in_college => lv_isir.p_num_in_college ,
2075 x_p_state_legal_residence => lv_isir.p_state_legal_residence ,
2076 x_p_state_legal_res_before_dt => lv_isir.p_state_legal_res_before_dt ,
2077 x_p_legal_res_date => lv_isir.p_legal_res_date ,
2078 x_age_older_parent => lv_isir.age_older_parent ,
2079 x_p_tax_return_status => lv_isir.p_tax_return_status ,
2080 x_p_type_tax_return => lv_isir.p_type_tax_return ,
2081 x_p_elig_1040aez => lv_isir.p_elig_1040aez ,
2082 x_p_adjusted_gross_income => lv_isir.p_adjusted_gross_income ,
2083 x_p_taxes_paid => lv_isir.p_taxes_paid ,
2084 x_p_exemptions => lv_isir.p_exemptions ,
2085 x_f_income_work => lv_isir.f_income_work ,
2086 x_m_income_work => lv_isir.m_income_work ,
2087 x_p_income_wsa => lv_isir.p_income_wsa ,
2088 x_p_income_wsb => lv_isir.p_income_wsb ,
2089 x_p_income_wsc => lv_isir.p_income_wsc ,
2090 x_p_investment_networth => lv_isir.p_investment_networth ,
2091 x_p_business_networth => lv_isir.p_business_networth ,
2092 x_p_cash_saving => lv_isir.p_cash_saving ,
2093 x_s_num_family_members => lv_isir.s_num_family_members ,
2094 x_s_num_in_college => lv_isir.s_num_in_college ,
2095 x_first_college => lv_isir.first_college ,
2096 x_first_house_plan => lv_isir.first_house_plan ,
2097 x_second_college => lv_isir.second_college ,
2098 x_second_house_plan => lv_isir.second_house_plan ,
2099 x_third_college => lv_isir.third_college ,
2100 x_third_house_plan => lv_isir.third_house_plan ,
2101 x_fourth_college => lv_isir.fourth_college ,
2102 x_fourth_house_plan => lv_isir.fourth_house_plan ,
2103 x_fifth_college => lv_isir.fifth_college ,
2104 x_fifth_house_plan => lv_isir.fifth_house_plan ,
2105 x_sixth_college => lv_isir.sixth_college ,
2106 x_sixth_house_plan => lv_isir.sixth_house_plan ,
2107 x_date_app_completed => lv_isir.date_app_completed ,
2108 x_signed_by => lv_isir.signed_by ,
2109 x_preparer_ssn => lv_isir.preparer_ssn ,
2110 x_preparer_emp_id_number => lv_isir.preparer_emp_id_number ,
2111 x_preparer_sign => lv_isir.preparer_sign ,
2112 x_transaction_receipt_date => lv_isir.transaction_receipt_date ,
2113 x_dependency_override_ind => lv_isir.dependency_override_ind ,
2114 x_faa_fedral_schl_code => lv_isir.faa_fedral_schl_code ,
2115 x_faa_adjustment => lv_isir.faa_adjustment ,
2116 x_input_record_type => lv_isir.input_record_type ,
2117 x_serial_number => lv_isir.serial_number ,
2118 x_batch_number => lv_isir.batch_number ,
2119 x_early_analysis_flag => lv_isir.early_analysis_flag ,
2120 x_app_entry_source_code => lv_isir.app_entry_source_code ,
2121 x_eti_destination_code => lv_isir.eti_destination_code ,
2122 x_reject_override_b => lv_isir.reject_override_b ,
2123 x_reject_override_n => lv_isir.reject_override_n ,
2124 x_reject_override_w => lv_isir.reject_override_w ,
2125 x_assum_override_1 => lv_isir.assum_override_1 ,
2126 x_assum_override_2 => lv_isir.assum_override_2 ,
2127 x_assum_override_3 => lv_isir.assum_override_3 ,
2128 x_assum_override_4 => lv_isir.assum_override_4 ,
2129 x_assum_override_5 => lv_isir.assum_override_5 ,
2130 x_assum_override_6 => lv_isir.assum_override_6 ,
2131 x_dependency_status => lv_isir.dependency_status ,
2132 x_s_email_address => lv_isir.s_email_address ,
2133 x_nslds_reason_code => lv_isir.nslds_reason_code ,
2134 x_app_receipt_date => lv_isir.app_receipt_date ,
2135 x_processed_rec_type => lv_isir.processed_rec_type ,
2136 x_hist_correction_for_tran_id => lv_isir.hist_correction_for_tran_id ,
2137 x_system_generated_indicator => lv_isir.system_generated_indicator ,
2138 x_dup_request_indicator => lv_isir.dup_request_indicator ,
2139 x_source_of_correction => lv_isir.source_of_correction ,
2140 x_p_cal_tax_status => lv_isir.p_cal_tax_status ,
2141 x_s_cal_tax_status => lv_isir.s_cal_tax_status ,
2142 x_graduate_flag => lv_isir.graduate_flag ,
2143 x_auto_zero_efc => lv_isir.auto_zero_efc ,
2144 x_efc_change_flag => lv_isir.efc_change_flag ,
2145 x_sarc_flag => lv_isir.sarc_flag ,
2146 x_simplified_need_test => lv_isir.simplified_need_test ,
2147 x_reject_reason_codes => lv_isir.reject_reason_codes ,
2148 x_select_service_match_flag => lv_isir.select_service_match_flag ,
2149 x_select_service_reg_flag => lv_isir.select_service_reg_flag ,
2150 x_ins_match_flag => lv_isir.ins_match_flag ,
2151 x_ins_verification_number => NULL,
2152 x_sec_ins_match_flag => lv_isir.sec_ins_match_flag ,
2153 x_sec_ins_ver_number => lv_isir.sec_ins_ver_number ,
2154 x_ssn_match_flag => lv_isir.ssn_match_flag ,
2155 x_ssa_citizenship_flag => lv_isir.ssa_citizenship_flag ,
2156 x_ssn_date_of_death => lv_isir.ssn_date_of_death ,
2157 x_nslds_match_flag => lv_isir.nslds_match_flag ,
2158 x_va_match_flag => lv_isir.va_match_flag ,
2159 x_prisoner_match => lv_isir.prisoner_match ,
2160 x_verification_flag => lv_isir.verification_flag ,
2161 x_subsequent_app_flag => lv_isir.subsequent_app_flag ,
2162 x_app_source_site_code => lv_isir.app_source_site_code ,
2163 x_tran_source_site_code => lv_isir.tran_source_site_code ,
2164 x_drn => lv_isir.drn ,
2165 x_tran_process_date => lv_isir.tran_process_date ,
2166 x_computer_batch_number => lv_isir.computer_batch_number ,
2167 x_correction_flags => lv_isir.correction_flags ,
2168 x_highlight_flags => lv_isir.highlight_flags ,
2169 x_paid_efc => NULL ,
2170 x_primary_efc => lv_isir.primary_efc ,
2171 x_secondary_efc => lv_isir.secondary_efc ,
2172 x_fed_pell_grant_efc_type => NULL ,
2173 x_primary_efc_type => lv_isir.primary_efc_type ,
2174 x_sec_efc_type => lv_isir.sec_efc_type ,
2175 x_primary_alternate_month_1 => lv_isir.primary_alternate_month_1 ,
2176 x_primary_alternate_month_2 => lv_isir.primary_alternate_month_2 ,
2177 x_primary_alternate_month_3 => lv_isir.primary_alternate_month_3 ,
2178 x_primary_alternate_month_4 => lv_isir.primary_alternate_month_4 ,
2179 x_primary_alternate_month_5 => lv_isir.primary_alternate_month_5 ,
2180 x_primary_alternate_month_6 => lv_isir.primary_alternate_month_6 ,
2181 x_primary_alternate_month_7 => lv_isir.primary_alternate_month_7 ,
2182 x_primary_alternate_month_8 => lv_isir.primary_alternate_month_8 ,
2183 x_primary_alternate_month_10 => lv_isir.primary_alternate_month_10 ,
2184 x_primary_alternate_month_11 => lv_isir.primary_alternate_month_11 ,
2185 x_primary_alternate_month_12 => lv_isir.primary_alternate_month_12 ,
2186 x_sec_alternate_month_1 => lv_isir.sec_alternate_month_1 ,
2187 x_sec_alternate_month_2 => lv_isir.sec_alternate_month_2 ,
2188 x_sec_alternate_month_3 => lv_isir.sec_alternate_month_3 ,
2189 x_sec_alternate_month_4 => lv_isir.sec_alternate_month_4 ,
2190 x_sec_alternate_month_5 => lv_isir.sec_alternate_month_5 ,
2191 x_sec_alternate_month_6 => lv_isir.sec_alternate_month_6 ,
2192 x_sec_alternate_month_7 => lv_isir.sec_alternate_month_7 ,
2193 x_sec_alternate_month_8 => lv_isir.sec_alternate_month_8 ,
2194 x_sec_alternate_month_10 => lv_isir.sec_alternate_month_10 ,
2195 x_sec_alternate_month_11 => lv_isir.sec_alternate_month_11 ,
2196 x_sec_alternate_month_12 => lv_isir.sec_alternate_month_12 ,
2197 x_total_income => lv_isir.total_income ,
2198 x_allow_total_income => lv_isir.allow_total_income ,
2199 x_state_tax_allow => lv_isir.state_tax_allow ,
2200 x_employment_allow => lv_isir.employment_allow ,
2201 x_income_protection_allow => lv_isir.income_protection_allow ,
2202 x_available_income => lv_isir.available_income ,
2203 x_contribution_from_ai => lv_isir.contribution_from_ai ,
2204 x_discretionary_networth => lv_isir.discretionary_networth ,
2205 x_efc_networth => lv_isir.efc_networth ,
2206 x_asset_protect_allow => lv_isir.asset_protect_allow ,
2207 x_parents_cont_from_assets => lv_isir.parents_cont_from_assets ,
2208 x_adjusted_available_income => lv_isir.adjusted_available_income ,
2209 x_total_student_contribution => lv_isir.total_student_contribution ,
2210 x_total_parent_contribution => lv_isir.total_parent_contribution ,
2211 x_parents_contribution => lv_isir.parents_contribution ,
2212 x_student_total_income => lv_isir.student_total_income ,
2213 x_sati => lv_isir.sati ,
2214 x_sic => lv_isir.sic ,
2215 x_sdnw => lv_isir.sdnw ,
2216 x_sca => lv_isir.sca ,
2217 x_fti => lv_isir.fti ,
2218 x_secti => lv_isir.secti ,
2219 x_secati => lv_isir.secati ,
2220 x_secstx => lv_isir.secstx ,
2221 x_secea => lv_isir.secea ,
2222 x_secipa => lv_isir.secipa ,
2223 x_secai => lv_isir.secai ,
2224 x_seccai => lv_isir.seccai ,
2225 x_secdnw => lv_isir.secdnw ,
2226 x_secnw => lv_isir.secnw ,
2227 x_secapa => lv_isir.secapa ,
2228 x_secpca => lv_isir.secpca ,
2229 x_secaai => lv_isir.secaai ,
2230 x_sectsc => lv_isir.sectsc ,
2231 x_sectpc => lv_isir.sectpc ,
2232 x_secpc => lv_isir.secpc ,
2233 x_secsti => lv_isir.secsti ,
2234 x_secsic => lv_isir.secsic ,
2235 x_secsati => lv_isir.secsati ,
2236 x_secsdnw => lv_isir.secsdnw ,
2237 x_secsca => lv_isir.secsca ,
2238 x_secfti => lv_isir.secfti ,
2239 x_a_citizenship => lv_isir.a_citizenship ,
2240 x_a_student_marital_status => lv_isir.a_student_marital_status ,
2241 x_a_student_agi => lv_isir.a_student_agi ,
2242 x_a_s_us_tax_paid => lv_isir.a_s_us_tax_paid ,
2243 x_a_s_income_work => lv_isir.a_s_income_work ,
2244 x_a_spouse_income_work => lv_isir.a_spouse_income_work ,
2245 x_a_s_total_wsc => lv_isir.a_s_total_wsc ,
2246 x_a_date_of_birth => lv_isir.a_date_of_birth ,
2247 x_a_student_married => lv_isir.a_student_married ,
2248 x_a_have_children => lv_isir.a_have_children ,
2249 x_a_s_have_dependents => lv_isir.a_s_have_dependents ,
2250 x_a_va_status => lv_isir.a_va_status ,
2251 x_a_s_num_in_family => lv_isir.a_s_num_in_family ,
2252 x_a_s_num_in_college => lv_isir.a_s_num_in_college ,
2253 x_a_p_marital_status => lv_isir.a_p_marital_status ,
2254 x_a_father_ssn => lv_isir.a_father_ssn ,
2255 x_a_mother_ssn => lv_isir.a_mother_ssn ,
2256 x_a_parents_num_family => lv_isir.a_parents_num_family ,
2257 x_a_parents_num_college => lv_isir.a_parents_num_college ,
2258 x_a_parents_agi => lv_isir.a_parents_agi ,
2259 x_a_p_us_tax_paid => lv_isir.a_p_us_tax_paid ,
2260 x_a_f_work_income => lv_isir.a_f_work_income ,
2261 x_a_m_work_income => lv_isir.a_m_work_income ,
2262 x_a_p_total_wsc => lv_isir.a_p_total_wsc ,
2263 x_comment_codes => lv_isir.comment_codes ,
2264 x_sar_ack_comm_code => lv_isir.sar_ack_comm_code ,
2265 x_pell_grant_elig_flag => lv_isir.pell_grant_elig_flag ,
2266 x_reprocess_reason_code => lv_isir.reprocess_reason_code ,
2267 x_duplicate_date => lv_isir.duplicate_date ,
2268 x_isir_transaction_type => lv_isir.isir_transaction_type ,
2269 x_fedral_schl_code_indicator => lv_isir.fedral_schl_code_indicator ,
2270 x_multi_school_code_flags => lv_isir.multi_school_code_flags ,
2271 x_dup_ssn_indicator => lv_isir.dup_ssn_indicator ,
2272 x_system_record_type => 'INTERNAL' ,
2273 x_payment_isir => 'N' ,
2274 x_receipt_status => lv_isir.receipt_status ,
2275 x_isir_receipt_completed => lv_isir.isir_receipt_completed ,
2276 x_active_isir => 'N' ,
2277 x_fafsa_data_verify_flags => lv_isir.fafsa_data_verify_flags ,
2278 x_reject_override_a => lv_isir.reject_override_a ,
2279 x_reject_override_c => lv_isir.reject_override_c ,
2280 x_parent_marital_status_date => lv_isir.parent_marital_status_date ,
2281 x_legacy_record_flag => NULL ,
2282 x_father_first_name_initial => lv_isir.FATHER_FIRST_NAME_INITIAL_TXT ,
2283 x_father_step_father_birth_dt => lv_isir.FATHER_STEP_FATHER_BIRTH_DATE ,
2284 x_mother_first_name_initial => lv_isir.MOTHER_FIRST_NAME_INITIAL_TXT ,
2285 x_mother_step_mother_birth_dt => lv_isir.MOTHER_STEP_MOTHER_BIRTH_DATE ,
2286 x_parents_email_address_txt => lv_isir.PARENTS_EMAIL_ADDRESS_TXT ,
2287 x_address_change_type => lv_isir.ADDRESS_CHANGE_TYPE ,
2288 x_cps_pushed_isir_flag => lv_isir.CPS_PUSHED_ISIR_FLAG ,
2289 x_electronic_transaction_type => lv_isir.ELECTRONIC_TRANSACTION_TYPE ,
2290 x_sar_c_change_type => lv_isir.SAR_C_CHANGE_TYPE ,
2291 x_father_ssn_match_type => lv_isir.FATHER_SSN_MATCH_TYPE ,
2292 x_mother_ssn_match_type => lv_isir.MOTHER_SSN_MATCH_TYPE ,
2293 x_reject_override_g_flag => lv_isir.REJECT_OVERRIDE_G_FLAG,
2294 x_dhs_verification_num_txt => lv_isir.dhs_verification_num_txt ,
2295 x_data_file_name_txt => lv_isir.data_file_name_txt ,
2296 x_message_class_txt => NULL, -- Passing NULL as the record is created internally
2297 x_reject_override_3_flag => lv_isir.reject_override_3_flag,
2298 x_reject_override_12_flag => lv_isir.reject_override_12_flag,
2299 x_reject_override_j_flag => lv_isir.reject_override_j_flag,
2300 x_reject_override_k_flag => lv_isir.reject_override_k_flag,
2301 x_rejected_status_change_flag => lv_isir.rejected_status_change_flag,
2302 x_verification_selection_flag => lv_isir.verification_selection_flag
2303 );
2304 p_ret_isir_id := pn_isir_id ;
2305 COMMIT;
2306
2307 OPEN chk_internal(lv_isir.base_id);
2308 FETCH chk_internal into lv_chk_internal ;
2309 IF chk_internal%FOUND THEN
2310 CLOSE chk_internal ;
2311 p_ret_isir_id := lv_chk_internal.isir_id ;
2312 ELSE
2313 CLOSE chk_internal ;
2314 END IF;
2315
2316 END IF;
2317 CLOSE c_isir;
2318 ELSE
2319 -- ISIR Not FOUND hence return -1
2320 CLOSE c_isir;
2321 p_ret_isir_id := -1 ;
2322 RETURN ;
2323 END IF;
2324 EXCEPTION
2325 WHEN OTHERS THEN
2326 IF SQLCODE = -54 THEN
2327 -- ORA-00054: resource busy and acquire with NOWAIT specified
2328 p_ret_isir_id := -2;
2329 ELSE
2330 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
2331 FND_MESSAGE.SET_TOKEN('NAME','igf_ap_ss_pkg.set_internal_isir');
2332 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
2333 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_ss_pkg.set_internal_isir.exception','Exception: '||SQLERRM);
2334 END IF;
2335 IGS_GE_MSG_STACK.ADD;
2336 END IF;
2337 END get_internal_isir_id;
2338
2339 PROCEDURE insert_into_todo(
2340 p_base_id NUMBER,
2341 p_seq_num NUMBER,
2342 p_status VARCHAR2,
2343 p_req_for_app VARCHAR2,
2344 p_freq_attempt NUMBER,
2345 p_max_attempt NUMBER
2346 ) IS
2347 /*
2348 || Change History :
2349 || Who When What
2350 || museshad 17-Nov-2005 Bug 4741517.
2351 || ToDo Item was getting inserted as 'Inactive'
2352 || bcoz the x_inactive_flag was being sent as
2353 || NULL. Modified this and made x_inactive_flag
2354 || as 'N'.
2355 || (reverse chronological order - newest change first)
2356 */
2357 l_row_id rowid;
2358 BEGIN
2359 igf_ap_td_item_inst_pkg.insert_row(
2360 x_mode => 'R',
2361 x_rowid => l_row_id,
2362 x_base_id => p_base_id,
2363 x_item_sequence_number => p_seq_num,
2364 x_status => p_status,
2365 x_status_date => SYSDATE,
2366 x_add_date => SYSDATE,
2367 x_corsp_date => NULL,
2368 x_corsp_count => NULL,
2369 x_inactive_flag => 'N',
2370 x_required_for_application => p_req_for_app,
2371 x_max_attempt => p_freq_attempt,
2372 x_freq_attempt => p_max_attempt,
2373 x_legacy_record_flag => NULL,
2374 x_clprl_id => NULL );
2375
2376 END insert_into_todo;
2377
2378
2379 END IGF_AP_SS_PKG;