[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_1098T_EXTRACT_DATA
Source
1 PACKAGE BODY igs_fi_1098t_extract_data AS
2 /* $Header: IGSFI91B.pls 120.11 2006/06/27 14:15:37 skharida noship $ */
3
4 /******************************************************************
5 Created By : Amit Gairola
6 Date Created By : 14-Apr-2005
7 Purpose : Package for the 1098T Extract Data
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 skharida 26/06/2006 Bug 5208136 Modified procedures with new igs_fi_inv_int_pkg signatiure.
13 skharida 22/05/2006 Bug 5213590: Added changes to check for waiver name for box 4,5 charges
14 and credits
15 abshriva 12/05/2006 Bug 5217319: Amount Precision changein extract_data_main
16 abshriva 30/11/05 Bug:4768071 - Modification made in procedure 'insert_1098t_data'
17 agairola 23/11/05 Bug:4747419 - Modified box45_credits and box45_charges
18 abshriva 9/11/05 Bug:4695680-Modification made in procedure'insert_1098t_data'
19 abshriva 26/10/05 Bug: 4697644-Modification made in procedure 'insert_1098t_data'
20 ***************************************************************** */
21
22 g_v_label_tax_year igs_lookup_values.meaning%TYPE;
23 g_v_label_person igs_lookup_values.meaning%TYPE;
24 g_v_label_persgrp igs_lookup_values.meaning%TYPE;
25 g_v_label_override_excl igs_lookup_values.meaning%TYPE;
26 g_v_label_file_addr igs_lookup_values.meaning%TYPE;
27 g_v_label_test_run igs_lookup_values.meaning%TYPE;
28 g_v_label_stdnt_name igs_lookup_values.meaning%TYPE;
29 g_v_line_sep CONSTANT VARCHAR2(100) := '+'||RPAD('-',75,'-')||'+';
30
31 g_b_non_zero_credits_flag BOOLEAN;
32 g_b_chg_crd_found BOOLEAN;
33
34 g_v_validation_status igs_fi_1098t_data.status_code%TYPE;
35
36 g_v_label_name_control igs_lookup_values.meaning%TYPE;
37 g_v_label_tin igs_lookup_values.meaning%TYPE;
38 g_v_label_val_status igs_lookup_values.meaning%TYPE;
39 g_v_label_err_desc igs_lookup_values.meaning%TYPE;
40 g_v_label_correct_ret igs_lookup_values.meaning%TYPE;
41 g_v_label_box2 igs_lookup_values.meaning%TYPE;
42 g_v_label_box3 igs_lookup_values.meaning%TYPE;
43 g_v_label_box4 igs_lookup_values.meaning%TYPE;
44 g_v_label_box5 igs_lookup_values.meaning%TYPE;
45 g_v_label_box6 igs_lookup_values.meaning%TYPE;
46 g_v_label_box8 igs_lookup_values.meaning%TYPE;
47 g_v_label_box9 igs_lookup_values.meaning%TYPE;
48 g_v_label_boxval igs_lookup_values.meaning%TYPE;
49
50 g_v_package_name VARCHAR2(100) := 'igs.plsql.igs_fi_1098t_extract_data.';
51
52 e_resource_busy EXCEPTION;
53
54 PRAGMA EXCEPTION_INIT(e_resource_busy, -0054);
55
56 CURSOR cur_1098t_setup(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) IS
57 SELECT *
58 FROM igs_fi_1098t_setup
59 WHERE tax_year_name = cp_v_tax_year_name;
60
61 CURSOR cur_chk_1098t_sfts(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
62 cp_v_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE) IS
63 SELECT 'x'
64 FROM igs_fi_1098t_sfts
65 WHERE tax_year_name = cp_v_tax_year_name
66 AND sys_fund_type = cp_v_sys_fund_type;
67
68 g_rec_1098t_setup cur_1098t_setup%ROWTYPE;
69
70 TYPE r_1098t_drilldown IS RECORD(transaction_id igs_fi_1098t_dtls.transaction_id%TYPE,
71 transaction_code igs_fi_1098t_dtls.transaction_code%TYPE,
72 box_num igs_fi_1098t_dtls.box_num%TYPE);
73 TYPE t_1098t_drilldown IS TABLE OF r_1098t_drilldown
74 INDEX BY BINARY_INTEGER;
75
76 l_t_1098t_drilldown t_1098t_drilldown;
77 l_n_cntr PLS_INTEGER;
78
79 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
80 p_v_string IN VARCHAR2 ) IS
81 /******************************************************************
82 Created By : Amit Gairola
83 Date Created By : 06-May-2005
84 Purpose : Procedure for logging
85
86 Known limitations,enhancements,remarks:
87 Change History
88 Who When What
89 ***************************************************************** */
90 BEGIN
91
92 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
93 fnd_log.string( fnd_log.level_statement, g_v_package_name||p_v_module, p_v_string);
94 END IF;
95
96 END log_to_fnd;
97
98 PROCEDURE set_validation_status(p_v_validation_status VARCHAR2) AS
99 /******************************************************************
100 Created By : Amit Gairola
101 Date Created By : 06-May-2005
102 Purpose : Procedure for setting validation status
103
104 Known limitations,enhancements,remarks:
105 Change History
106 Who When What
107 ***************************************************************** */
108 BEGIN
109 IF g_v_validation_status <> 'DNT_RPT' OR g_v_validation_status IS NULL THEN
110 g_v_validation_status := p_v_validation_status;
111 END IF;
112 END set_validation_status;
113
114 FUNCTION validate_namecontrol(p_v_name_control igs_fi_1098t_data.stu_name_control%TYPE) RETURN VARCHAR2 AS
115 /******************************************************************
116 Created By : Amit Gairola
117 Date Created By : 06-May-2005
118 Purpose : Function for validating Name Control
119
120 Known limitations,enhancements,remarks:
121 Change History
122 Who When What
123 ***************************************************************** */
124 l_v_ret_val VARCHAR2(1);
125 l_n_asc_val NUMBER;
126 BEGIN
127 l_v_ret_val := 'Y';
128
129 -- If Name Control is Null,return 'Y'
130 IF p_v_name_control IS NULL THEN
131 RETURN 'Y';
132 END IF;
133
134 -- If the length of Name Control is > 4, then return N
135 IF LENGTH(p_v_name_control) > 4 THEN
136 RETURN 'N';
137 END IF;
138
139 -- Check for invalid characters. Valid characters are
140 -- 0 to 9, A to Z, a to z , ampersand and -
141
142 FOR l_n_strlen IN 1..LENGTH(p_v_name_control) LOOP
143 l_n_asc_val := ASCII(SUBSTR(p_v_name_control,l_n_strlen,1));
144 IF NOT ((l_n_asc_val BETWEEN 48 AND 57) OR
145 (l_n_asc_val BETWEEN 65 AND 90) OR
146 (l_n_asc_val BETWEEN 97 AND 122) OR
147 (l_n_asc_val IN (38,45))) THEN
148 l_v_ret_val := 'N';
149 EXIT;
150 END IF;
151 END LOOP;
152
153 RETURN l_v_ret_val;
154 END validate_namecontrol;
155
156 FUNCTION validate_tin(p_v_api_pers_id igs_pe_alt_pers_id.api_person_id%TYPE) RETURN BOOLEAN AS
157 /******************************************************************
158 Created By : Amit Gairola
159 Date Created By : 04-Aug-2005
160 Purpose : Procedure for validating TIN
161
162 Known limitations,enhancements,remarks:
163 Change History
164 Who When What
165 ***************************************************************** */
166 l_n_cntr NUMBER(5);
167 l_v_str1 VARCHAR2(1);
168
169 l_b_bool BOOLEAN;
170 BEGIN
171 l_v_str1 := SUBSTR(p_v_api_pers_id,1,1);
172
173 l_b_bool := FALSE;
174
175 FOR l_n_cntr IN 2..LENGTH(p_v_api_pers_id) LOOP
176 IF l_v_str1 <> SUBSTR(p_v_api_pers_id,l_n_cntr,1) THEN
177 l_b_bool := TRUE;
178 EXIT;
179 END IF;
180 END LOOP;
181
182 RETURN l_b_bool;
183 END validate_tin;
184
185 PROCEDURE update_credits(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
186 p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
187
188 /******************************************************************
189 Created By : Amit Gairola
190 Date Created By : 06-May-2005
191 Purpose : Procedure for updating Credits
192
193 Known limitations,enhancements,remarks:
194 Change History
195 Who When What
196 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
197 ***************************************************************** */
198
199 -- Cursor for selecting all the records from the credits table for the person id
200 -- having the tax_year_name as the current tax year
201 CURSOR cur_crd(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
202 cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
203 SELECT crd.rowid row_id,
204 crd.*
205 FROM igs_fi_credits_all crd
206 WHERE party_id = cp_n_person_id
207 AND tax_year_code = cp_v_tax_year
208 FOR UPDATE OF tax_year_code NOWAIT;
209
210
211 BEGIN
212 log_to_fnd(p_v_module => 'update_credits',
213 p_v_string => 'Updating Credit transactions');
214
215 FOR l_rec_crd IN cur_crd(p_n_person_id,
216 p_v_tax_year) LOOP
217 igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
218 x_credit_id => l_rec_crd.credit_id,
219 x_credit_number => l_rec_crd.credit_number,
220 x_status => l_rec_crd.status,
221 x_credit_source => l_rec_crd.credit_source,
222 x_party_id => l_rec_crd.party_id,
223 x_credit_type_id => l_rec_crd.credit_type_id,
224 x_credit_instrument => l_rec_crd.credit_instrument,
225 x_description => l_rec_crd.description,
226 x_amount => l_rec_crd.amount,
227 x_currency_cd => l_rec_crd.currency_cd,
228 x_exchange_rate => l_rec_crd.exchange_rate,
229 x_transaction_date => l_rec_crd.transaction_date,
230 x_effective_date => l_rec_crd.effective_date,
231 x_reversal_date => l_rec_crd.reversal_date,
232 x_reversal_reason_code => l_rec_crd.reversal_reason_code,
233 x_reversal_comments => l_rec_crd.reversal_comments,
234 x_unapplied_amount => l_rec_crd.unapplied_amount,
235 x_source_transaction_id => l_rec_crd.source_transaction_id,
236 x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
237 x_merchant_id => l_rec_crd.merchant_id,
238 x_credit_card_code => l_rec_crd.credit_card_code,
239 x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
240 x_credit_card_number => l_rec_crd.credit_card_number,
241 x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
242 x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
243 x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
244 x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
245 x_fee_cal_type => l_rec_crd.fee_cal_type,
246 x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
247 x_attribute_category => l_rec_crd.attribute_category,
248 x_attribute1 => l_rec_crd.attribute1,
249 x_attribute2 => l_rec_crd.attribute2,
250 x_attribute3 => l_rec_crd.attribute3,
251 x_attribute4 => l_rec_crd.attribute4,
252 x_attribute5 => l_rec_crd.attribute5,
253 x_attribute6 => l_rec_crd.attribute6,
254 x_attribute7 => l_rec_crd.attribute7,
255 x_attribute8 => l_rec_crd.attribute8,
256 x_attribute9 => l_rec_crd.attribute9,
257 x_attribute10 => l_rec_crd.attribute10,
258 x_attribute11 => l_rec_crd.attribute11,
259 x_attribute12 => l_rec_crd.attribute12,
260 x_attribute13 => l_rec_crd.attribute13,
261 x_attribute14 => l_rec_crd.attribute14,
262 x_attribute15 => l_rec_crd.attribute15,
263 x_attribute16 => l_rec_crd.attribute16,
264 x_attribute17 => l_rec_crd.attribute17,
265 x_attribute18 => l_rec_crd.attribute18,
266 x_attribute19 => l_rec_crd.attribute19,
267 x_attribute20 => l_rec_crd.attribute20,
268 x_gl_date => l_rec_crd.gl_date,
269 x_check_number => l_rec_crd.check_number,
270 x_source_transaction_type => l_rec_crd.source_transaction_type,
271 x_source_transaction_ref => l_rec_crd.source_transaction_ref,
272 x_credit_card_status_code => l_rec_crd.credit_card_status_code,
273 x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
274 x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
275 x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
276 x_batch_name => l_rec_crd.batch_name,
277 x_deposit_date => l_rec_crd.deposit_date,
278 x_source_invoice_id => l_rec_crd.source_invoice_id,
279 x_tax_year_code => null,
280 x_waiver_name => l_rec_crd.waiver_name);
281 END LOOP;
282 END update_credits;
283
284 PROCEDURE update_charges(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
285 p_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) AS
286
287 /******************************************************************
288 Created By : Amit Gairola
289 Date Created By : 06-May-2005
290 Purpose : Procedure for updating Charges
291
292 Known limitations,enhancements,remarks:
293 Change History
294 Who When What
295 skharida 26-Jun-2006 Bug 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
296 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
297 ***************************************************************** */
298
299 -- Cursor for selecting all the Charge records for the person and tax year.
300 CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
301 cp_v_tax_year igs_fi_1098t_setup.tax_year_code%TYPE) IS
302 SELECT inv.rowid row_id, inv.*
303 FROM igs_fi_inv_int_all inv
304 WHERE person_id = cp_n_person_id
305 AND tax_year_code = cp_v_tax_year
306 FOR UPDATE OF tax_year_code NOWAIT;
307
308 BEGIN
309 log_to_fnd(p_v_module => 'update_charges',
310 p_v_string => 'Updating Charge transactions');
311
312 FOR l_rec_chg IN cur_chg(p_n_person_id,
313 p_v_tax_year) LOOP
314 igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
315 x_invoice_id => l_rec_chg.invoice_id,
316 x_person_id => l_rec_chg.person_id,
317 x_fee_type => l_rec_chg.fee_type,
318 x_fee_cat => l_rec_chg.fee_cat,
319 x_fee_cal_type => l_rec_chg.fee_cal_type,
320 x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
321 x_course_cd => l_rec_chg.course_cd,
322 x_attendance_mode => l_rec_chg.attendance_mode,
323 x_attendance_type => l_rec_chg.attendance_type,
324 x_invoice_amount_due => l_rec_chg.invoice_amount_due,
325 x_invoice_creation_date => l_rec_chg.invoice_creation_date,
326 x_invoice_desc => l_rec_chg.invoice_desc,
327 x_transaction_type => l_rec_chg.transaction_type,
328 x_currency_cd => l_rec_chg.currency_cd,
329 x_status => l_rec_chg.status,
330 x_attribute_category => l_rec_chg.attribute_category,
331 x_attribute1 => l_rec_chg.attribute1,
332 x_attribute2 => l_rec_chg.attribute2,
333 x_attribute3 => l_rec_chg.attribute3,
334 x_attribute4 => l_rec_chg.attribute4,
335 x_attribute5 => l_rec_chg.attribute5,
336 x_attribute6 => l_rec_chg.attribute6,
337 x_attribute7 => l_rec_chg.attribute7,
338 x_attribute8 => l_rec_chg.attribute8,
339 x_attribute9 => l_rec_chg.attribute9,
340 x_attribute10 => l_rec_chg.attribute10,
341 x_invoice_amount => l_rec_chg.invoice_amount,
342 x_bill_id => l_rec_chg.bill_id,
343 x_bill_number => l_rec_chg.bill_number,
344 x_bill_date => l_rec_chg.bill_date,
345 x_waiver_flag => l_rec_chg.waiver_flag,
346 x_waiver_reason => l_rec_chg.waiver_reason,
347 x_effective_date => l_rec_chg.effective_date,
348 x_invoice_number => l_rec_chg.invoice_number,
349 x_exchange_rate => l_rec_chg.exchange_rate,
350 x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
351 x_optional_fee_flag => l_rec_chg.optional_fee_flag,
352 x_reversal_gl_date => l_rec_chg.reversal_gl_date,
353 x_tax_year_code => NULL,
354 x_waiver_name => l_rec_chg.waiver_name);
355 END LOOP;
356 END update_charges;
357
358 FUNCTION chk_prior_lps(p_v_load_cal_type igs_ca_inst.cal_type%TYPE,
359 p_n_load_ci_seq igs_ca_inst.sequence_number%TYPE,
360 p_d_txn_date DATE) RETURN BOOLEAN AS
361
362 /******************************************************************
363 Created By : Amit Gairola
364 Date Created By : 06-May-2005
365 Purpose : Procedure for checking Prior Load Period
366
367 Known limitations,enhancements,remarks:
368 Change History
369 Who When What
370 ***************************************************************** */
371
372 CURSOR cur_prior_lps(cp_v_load_cal_type igs_ca_inst.cal_type%TYPE,
373 cp_n_load_ci_seq_num igs_ca_inst.sequence_number%TYPE,
374 cp_d_end_date igs_fi_1098t_setup.end_date%TYPE) IS
375 SELECT stp.tax_year_name
376 FROM igs_fi_1098t_lps lps,
377 igs_fi_1098t_setup stp
378 WHERE lps.cal_type = cp_v_load_cal_type
379 AND lps.sequence_number = cp_n_load_ci_seq_num
380 AND lps.tax_year_name = stp.tax_year_name
381 AND TRUNC(stp.end_date) <= TRUNC(cp_d_end_date)
382 ORDER BY stp.end_date DESC;
383
384 l_b_ret_val BOOLEAN;
385 l_rec_prior_lps cur_prior_lps%ROWTYPE;
386 l_b_rec_found BOOLEAN;
387 BEGIN
388 log_to_fnd(p_v_module => 'chk_prior_lps',
389 p_v_string => 'Checking Prior Load Period for Load Cal = '||p_v_load_cal_type||' and seq '||p_n_load_ci_seq);
390 l_b_ret_val := TRUE;
391 l_b_rec_found := FALSE;
392
393 -- Select the Latest Tax Year for which the Load Period is associated
394 OPEN cur_prior_lps(p_v_load_cal_type,
395 p_n_load_ci_seq,
396 g_rec_1098t_setup.end_date);
397 FETCH cur_prior_lps INTO l_rec_prior_lps;
398 IF cur_prior_lps%FOUND THEN
399 l_b_rec_found := TRUE;
400 END IF;
401 CLOSE cur_prior_lps;
402
403 -- If this load period is not associated to any tax year, then
404 -- it should not be reported
405 IF NOT l_b_rec_found THEN
406 RETURN FALSE;
407 END IF;
408
409 -- IF the Transaction Date is between the Tax Year Start Date and End Date then
410 IF TRUNC(p_d_txn_date) BETWEEN TRUNC(g_rec_1098t_setup.start_date) AND
411 TRUNC(g_rec_1098t_setup.end_date) THEN
412
413 -- If there was a record found then report the transaction
414 IF l_b_rec_found THEN
415 l_b_ret_val := TRUE;
416 END IF;
417
418 -- The Transaction Date is earlier than the start date of the tax year in context
419 ELSE
420
421 -- If the latest tax year value is Null, then this should return false.
422 IF l_rec_prior_lps.tax_year_name <> g_rec_1098t_setup.tax_year_name THEN
423 log_to_fnd(p_v_module => 'chk_prior_lps',
424 p_v_string => 'Tax Year fetched '||l_rec_prior_lps.tax_year_name||' does not match with current tax year');
425 l_b_ret_val := FALSE;
426 END IF;
427 END IF;
428
429 RETURN l_b_ret_val;
430 END chk_prior_lps;
431
432 PROCEDURE init AS
433 /******************************************************************
434 Created By : Amit Gairola
435 Date Created By : 06-May-2005
436 Purpose : Procedure for initializing
437
438 Known limitations,enhancements,remarks:
439 Change History
440 Who When What
441 ***************************************************************** */
442 BEGIN
443
444 -- Initialize the different global variables
445
446 log_to_fnd(p_v_module => 'Init',
447 p_v_string => 'Initializing variables');
448 g_v_label_tax_year := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
449 'TAX_YEAR');
450 g_v_label_person := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
451 'PERSON');
452 g_v_label_persgrp := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
453 'PERSON_GROUP');
454 g_v_label_override_excl := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
455 'OVERRIDE_EXCL');
456 g_v_label_file_addr := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
457 'FILE_CORRECTION');
458 g_v_label_test_run := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
459 'TEST_RUN');
460 g_v_label_stdnt_name := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
461 'STUDENT_NAME');
462
463 g_v_label_name_control := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
464 'NAME_CONTROL');
465 g_v_label_tin := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
466 'TIN');
467 g_v_label_val_status := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
468 'VAL_STATUS');
469 g_v_label_err_desc := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
470 'ERR_DESC');
471 g_v_label_correct_ret := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
472 'CORRECTED_RETURN');
473 g_v_label_box2 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
474 'BOX2');
475 g_v_label_box3 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
476 'BOX3');
477 g_v_label_box4 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
478 'BOX4');
479 g_v_label_box5 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
480 'BOX5');
481 g_v_label_box6 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
482 'BOX6');
483 g_v_label_box8 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
484 'BOX8');
485 g_v_label_box9 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
486 'BOX9');
487 g_v_label_boxval := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
488 'BOXVAL');
489 END init;
490
491 PROCEDURE log_line(p_v_parm_name VARCHAR2,
492 p_v_parm_value VARCHAR2) AS
493 /******************************************************************
494 Created By : Amit Gairola
495 Date Created By : 06-May-2005
496 Purpose : Procedure for logging a line
497
498 Known limitations,enhancements,remarks:
499 Change History
500 Who When What
501 ***************************************************************** */
502 BEGIN
503 fnd_file.put_line(fnd_file.log,
504 p_v_parm_name||' : '||p_v_parm_value);
505 END log_line;
506
507 FUNCTION chk_non_credit_course(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
508 p_v_override_excl VARCHAR2,
509 p_v_load_cal_type igs_ca_inst.cal_type%TYPE,
510 p_n_load_ci_seq igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN AS
511 /******************************************************************
512 Created By : Amit Gairola
513 Date Created By : 06-May-2005
514 Purpose : Function for checking Non Credit Courses
515
516 Known limitations,enhancements,remarks:
517 Change History
518 Who When What
519 ***************************************************************** */
520 l_v_attendance igs_en_atd_type.attendance_type%TYPE;
521 l_n_credit_pts igs_fi_invln_int.credit_points%TYPE;
522 l_n_fte igs_fi_invln_int.eftsu%TYPE;
523 BEGIN
524 IF p_v_override_excl = 'N' THEN
525 IF g_rec_1098t_setup.excl_non_credit_course_flag = 'Y' THEN
526
527 -- Call EN API for Institution Attendance Type
528 igs_en_prc_load.enrp_get_inst_latt(p_person_id => p_n_person_id,
529 p_load_cal_type => p_v_load_cal_type,
530 p_load_seq_number => p_n_load_ci_seq,
531 p_attendance => l_v_attendance,
532 p_credit_points => l_n_credit_pts,
533 p_fte => l_n_fte);
534
535 -- If there are some credit points, then return true else return false
536 IF l_n_credit_pts > 0 THEN
537 RETURN TRUE;
538 ELSE
539 RETURN FALSE;
540 END IF;
541 END IF;
542 END IF;
543
544 RETURN TRUE;
545 END chk_non_credit_course;
546
547 PROCEDURE log_params(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
548 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
549 p_n_person_grp_id igs_pe_persid_group_all.group_id%TYPE,
550 p_v_override_excl VARCHAR2,
551 p_v_file_addr_correction VARCHAR2,
552 p_v_test_run VARCHAR2) AS
553
554 /******************************************************************
555 Created By : Amit Gairola
556 Date Created By : 06-May-2005
557 Purpose : Procedure for logging parameters
558
559 Known limitations,enhancements,remarks:
560 Change History
561 Who When What
562 ***************************************************************** */
563 l_v_party_number hz_parties.party_number%TYPE;
564
565 CURSOR cur_persid_grp(cp_n_person_grp_id igs_pe_persid_group_all.group_id%TYPE) IS
566 SELECT group_cd
567 FROM igs_pe_persid_group_all
568 WHERE group_id = cp_n_person_grp_id;
569
570 l_v_group_cd igs_pe_persid_group_all.group_cd%TYPE;
571 BEGIN
572
573 -- Log the parameters
574 fnd_file.put_line(fnd_file.log,
575 fnd_message.get_string('IGS',
576 'IGS_FI_ANC_LOG_PARM'));
577 log_line(g_v_label_tax_year, p_v_tax_year_name);
578
579 IF p_n_person_id IS NOT NULL THEN
580 l_v_party_number := igs_fi_gen_008.get_party_number(p_n_person_id);
581 END IF;
582
583 log_line(g_v_label_person, l_v_party_number);
584
585 IF p_n_person_grp_id IS NOT NULL THEN
586 OPEN cur_persid_grp(p_n_person_grp_id);
587 FETCH cur_persid_grp INTO l_v_group_cd;
588 CLOSE cur_persid_grp;
589 END IF;
590 log_line(g_v_label_persgrp, l_v_group_cd);
591
592 log_line(g_v_label_override_excl,
593 igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_v_override_excl));
594
595 log_line(g_v_label_file_addr,
596 igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_v_file_addr_correction));
597
598 log_line(g_v_label_test_run,
599 igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_v_test_run));
600 fnd_file.put_line(fnd_file.log,
601 g_v_line_sep);
602 END log_params;
603
604 FUNCTION validate_params(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
605 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
606 p_n_person_grp_id igs_pe_persid_group_all.group_id%TYPE,
607 p_v_override_excl VARCHAR2,
608 p_v_file_addr_correction VARCHAR2,
609 p_v_test_run VARCHAR2) RETURN BOOLEAN AS
610 /******************************************************************
611 Created By : Amit Gairola
612 Date Created By : 06-May-2005
613 Purpose : Function for validating Parameters
614
615 Known limitations,enhancements,remarks:
616 Change History
617 Who When What
618 ***************************************************************** */
619 l_b_validate BOOLEAN;
620 l_c_val VARCHAR2(1);
621
622 CURSOR cur_val_person_id(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
623 SELECT 'x'
624 FROM igs_pe_person_base_v
625 WHERE person_id = cp_n_person_id;
626
627 CURSOR cur_val_persgrp(cp_n_pers_grp_id igs_pe_persid_group_all.group_id%TYPE,
628 cp_v_closed_ind igs_pe_persid_group_all.closed_ind%TYPE) IS
629 SELECT 'x'
630 FROM igs_pe_persid_group_all
631 WHERE group_id = cp_n_pers_grp_id
632 AND closed_ind = cp_v_closed_ind;
633
634 CURSOR cur_val_one_lps(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) IS
635 SELECT 'x'
636 FROM igs_fi_1098t_lps
637 WHERE tax_year_name = cp_v_tax_year_name;
638
639 CURSOR cur_val_one_ft(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) IS
640 SELECT 'x'
641 FROM igs_fi_1098t_fts
642 WHERE tax_year_name = cp_v_tax_year_name;
643
644 BEGIN
645 l_b_validate := TRUE;
646
647 -- Validate if the Person ID and Person ID Group are both passed
648 IF ((p_n_person_id IS NOT NULL) AND (p_n_person_grp_id IS NOT NULL)) THEN
649 l_b_validate := FALSE;
650 fnd_message.set_module(g_v_package_name||'validate_parameters');
651 fnd_message.set_name('IGS',
652 'IGS_FI_NO_PERS_PGRP');
653 fnd_file.put_line(fnd_file.log,
654 fnd_message.get);
655 END IF;
656
657 -- Validate if neither Person ID nor Person ID Group is passed
658 IF ((p_n_person_id IS NULL) AND (p_n_person_grp_id IS NULL)) THEN
659 fnd_message.set_module(g_v_package_name||'validate_parameters');
660 l_b_validate := FALSE;
661 fnd_message.set_name('IGS',
662 'IGS_FI_PRS_PRSIDGRP_NULL');
663 fnd_file.put_line(fnd_file.log,
664 fnd_message.get);
665 END IF;
666
667 -- Validate Tax Year
668 OPEN cur_1098t_setup(p_v_tax_year_name);
669 FETCH cur_1098t_setup INTO g_rec_1098t_setup;
670 IF cur_1098t_setup%NOTFOUND THEN
671 fnd_message.set_module(g_v_package_name||'validate_parameters');
672 l_b_validate := FALSE;
673 fnd_message.set_name('IGS',
674 'IGS_FI_INVALID_PARAMETER');
675 fnd_message.set_token('PARAMETER',
676 g_v_label_tax_year);
677 fnd_file.put_line(fnd_file.log,
678 fnd_message.get);
679 END IF;
680 CLOSE cur_1098t_setup;
681
682 -- Validate Person Id if passed
683 IF p_n_person_id IS NOT NULL THEN
684 OPEN cur_val_person_id(p_n_person_id);
685 FETCH cur_val_person_id INTO l_c_val;
686 IF cur_val_person_id%NOTFOUND THEN
687 fnd_message.set_module(g_v_package_name||'validate_parameters');
688 l_b_validate := FALSE;
689 fnd_message.set_name('IGS',
690 'IGS_FI_INVALID_PARAMETER');
691 fnd_message.set_token('PARAMETER',
692 g_v_label_person);
693 fnd_file.put_line(fnd_file.log,
694 fnd_message.get);
695 END IF;
696 CLOSE cur_val_person_id;
697 END IF;
698
699 -- Validate Person Id Group if Passed
700 IF p_n_person_grp_id IS NOT NULL THEN
701 OPEN cur_val_persgrp(p_n_person_grp_id,
702 'N');
703 FETCH cur_val_persgrp INTO l_c_val;
704 IF cur_val_persgrp%NOTFOUND THEN
705 fnd_message.set_module(g_v_package_name||'validate_parameters');
706 l_b_validate := FALSE;
707 fnd_message.set_name('IGS',
708 'IGS_FI_INVALID_PARAMETER');
709 fnd_message.set_token('PARAMETER',
710 g_v_label_persgrp);
711 fnd_file.put_line(fnd_file.log,
712 fnd_message.get);
713 END IF;
714 CLOSE cur_val_persgrp;
715 END IF;
716
717 -- Validate if atleast one load period is associated to the tax year
718 OPEN cur_val_one_lps(p_v_tax_year_name);
719 FETCH cur_val_one_lps INTO l_c_val;
720 IF cur_val_one_lps%NOTFOUND THEN
721 fnd_message.set_module(g_v_package_name||'validate_parameters');
722 l_b_validate := FALSE;
723 fnd_message.set_name('IGS',
724 'IGS_FI_1098T_NO_LPS');
725 fnd_message.set_token('TAX_YEAR_NAME',
726 p_v_tax_year_name);
727 fnd_file.put_line(fnd_file.log,
728 fnd_message.get);
729 END IF;
730 CLOSE cur_val_one_lps;
731
732 -- Validate if atleast one Fee Type is associated with the tax year
733 OPEN cur_val_one_ft(p_v_tax_year_name);
734 FETCH cur_val_one_ft INTO l_c_val;
735 IF cur_val_one_ft%NOTFOUND THEN
736 fnd_message.set_module(g_v_package_name||'validate_parameters');
737 l_b_validate := FALSE;
738 fnd_message.set_name('IGS',
739 'IGS_FI_1098T_NO_FTS');
740 fnd_message.set_token('TAX_YEAR_NAME',
741 p_v_tax_year_name);
742 fnd_file.put_line(fnd_file.log,
743 fnd_message.get);
744 END IF;
745 CLOSE cur_val_one_ft;
746
747 IF p_v_override_excl NOT IN ('Y','N') THEN
748 fnd_message.set_module(g_v_package_name||'validate_parameters');
749 l_b_validate := FALSE;
750 fnd_message.set_name('IGS',
751 'IGS_FI_INVALID_PARAMETER');
752 fnd_message.set_token('PARAMETER',
753 g_v_label_override_excl);
754 fnd_file.put_line(fnd_file.log,
755 fnd_message.get);
756 END IF;
757
758 IF p_v_file_addr_correction NOT IN ('Y','N') THEN
759 fnd_message.set_module(g_v_package_name||'validate_parameters');
760 l_b_validate := FALSE;
761 fnd_message.set_name('IGS',
762 'IGS_FI_INVALID_PARAMETER');
763 fnd_message.set_token('PARAMETER',
764 g_v_label_file_addr);
765 fnd_file.put_line(fnd_file.log,
766 fnd_message.get);
767 END IF;
768
769 IF p_v_test_run NOT IN ('Y','N') THEN
770 fnd_message.set_module(g_v_package_name||'validate_parameters');
771 l_b_validate := FALSE;
772 fnd_message.set_name('IGS',
773 'IGS_FI_INVALID_PARAMETER');
774 fnd_message.set_token('PARAMETER',
775 g_v_label_test_run);
776 fnd_file.put_line(fnd_file.log,
777 fnd_message.get);
778 END IF;
779
780 RETURN l_b_validate;
781 END validate_params;
782
783 FUNCTION get_sys_fund_type(p_n_credit_id igs_fi_credits_all.credit_id%TYPE,
784 p_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) RETURN VARCHAR2 AS
785
786 /******************************************************************
787 Created By : Amit Gairola
788 Date Created By : 06-May-2005
789 Purpose : Function for getting System Fund Type
790
791 Known limitations,enhancements,remarks:
792 Change History
793 Who When What
794 ***************************************************************** */
795
796 CURSOR cur_fund_id_crd(cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
797 SELECT awd.fund_id
798 FROM igf_db_awd_disb_dtl_all disb,
799 igf_aw_award_all awd
800 WHERE awd.award_id = disb.award_id
801 AND disb.sf_credit_id = cp_n_credit_id;
802
803 CURSOR cur_fund_id_inv(cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE) IS
804 SELECT awd.fund_id
805 FROM igf_db_awd_disb_dtl_all disb,
806 igf_aw_award_all awd
807 WHERE awd.award_id = disb.award_id
808 AND disb.sf_invoice_num = cp_n_invoice_id;
809
810 CURSOR cur_sys_fund_type(cp_n_fund_id igf_aw_award_all.fund_id%TYPE) IS
811 SELECT fcat.sys_fund_type
812 FROM igf_aw_fund_mast_all fmast,
813 igf_aw_fund_cat_all fcat
814 WHERE fmast.fund_id = cp_n_fund_id
815 AND fmast.fund_code = fcat.fund_code;
816
817 l_v_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE;
818 l_n_fund_id igf_aw_award_all.fund_id%TYPE;
819
820 BEGIN
821
822 -- Fetch the Fund ID from the Disbursements table
823 IF p_n_credit_id IS NOT NULL THEN
824 OPEN cur_fund_id_crd(p_n_credit_id);
825 FETCH cur_fund_id_crd INTO l_n_fund_id;
826 CLOSE cur_fund_id_crd;
827 ELSIF p_n_invoice_id IS NOT NULL THEN
828 OPEN cur_fund_id_inv(p_n_invoice_id);
829 FETCH cur_fund_id_inv INTO l_n_fund_id;
830 CLOSE cur_fund_id_inv;
831 END IF;
832
833 -- get the system fund type based on the fund id determined earlier
834 OPEN cur_sys_fund_type(l_n_fund_id);
835 FETCH cur_sys_fund_type INTO l_v_sys_fund_type;
836 CLOSE cur_sys_fund_type;
837
838 RETURN l_v_sys_fund_type;
839 END get_sys_fund_type;
840
841 PROCEDURE get_alt_person_id(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
842 p_v_person_id_type igs_pe_person_id_typ.person_id_type%TYPE,
843 p_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE,
844 p_v_api_pers_id OUT NOCOPY igs_pe_alt_pers_id.api_person_id%TYPE,
845 p_v_api_pers_id_uf OUT NOCOPY igs_pe_alt_pers_id.api_person_id_uf%TYPE) AS
846
847 /******************************************************************
848 Created By : Amit Gairola
849 Date Created By : 06-May-2005
850 Purpose : Function for getting Alternate Person Id
851
852 Known limitations,enhancements,remarks:
853 Change History
854 Who When What
855 ***************************************************************** */
856 CURSOR cur_api_pid1(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
857 cp_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE) IS
858 SELECT altid.api_person_id,
859 altid.api_person_id_uf
860 FROM igs_pe_alt_pers_id altid,
861 igs_pe_person_id_typ pid
862 WHERE altid.pe_person_id = cp_n_person_id
863 AND altid.person_id_type = pid.person_id_type
864 AND pid.s_person_id_type = cp_v_s_person_id_type
865 AND sysdate BETWEEN altid.start_dt AND NVL(altid.end_dt, sysdate)
866 AND pid.closed_ind = 'N';
867
868 CURSOR cur_api_pid2(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
869 cp_v_person_id_type igs_pe_person_id_typ.person_id_type%TYPE,
870 cp_v_s_person_id_type igs_pe_person_id_typ.s_person_id_type%TYPE) IS
871 SELECT altid.api_person_id,
872 altid.api_person_id_uf
873 FROM igs_pe_alt_pers_id altid,
874 igs_pe_person_id_typ pid
875 WHERE altid.pe_person_id = cp_n_person_id
876 AND altid.person_id_type = pid.person_id_type
877 AND pid.s_person_id_type = cp_v_s_person_id_type
878 AND altid.person_id_type = cp_v_person_id_type
879 AND sysdate BETWEEN altid.start_dt AND NVL(altid.end_dt, sysdate)
880 AND pid.closed_ind = 'N';
881
882 l_v_api_person_id igs_pe_alt_pers_id.api_person_id%TYPE;
883 l_v_api_person_id_uf igs_pe_alt_pers_id.api_person_id_uf%TYPE;
884
885 BEGIN
886 IF p_v_s_person_id_type IN ('SSN','NAME_CONTROL') THEN
887 OPEN cur_api_pid1(p_n_person_id,
888 p_v_s_person_id_type);
889 FETCH cur_api_pid1 INTO l_v_api_person_id,l_v_api_person_id_uf ;
890 CLOSE cur_api_pid1;
891 ELSIF p_v_s_person_id_type = 'TAXID' THEN
892 OPEN cur_api_pid2(p_n_person_id,
893 p_v_person_id_type,
894 p_v_s_person_id_type);
895 FETCH cur_api_pid2 INTO l_v_api_person_id,l_v_api_person_id_uf;
896 CLOSE cur_api_pid2;
897 END IF;
898
899 p_v_api_pers_id := l_v_api_person_id;
900 p_v_api_pers_id_uf := l_v_api_person_id_uf;
901
902 END get_alt_person_id;
903
904 PROCEDURE box236_credits(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
905 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
906 p_v_override_excl VARCHAR2,
907 p_n_orig_credit OUT NOCOPY NUMBER,
908 p_n_adj_credit OUT NOCOPY NUMBER) AS
909
910 /******************************************************************
911 Created By : Amit Gairola
912 Date Created By : 06-May-2005
913 Purpose : Procedure for Box 2 and 3 Credits
914
915 Known limitations,enhancements,remarks:
916 Change History
917 Who When What
918 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
919 ***************************************************************** */
920 l_n_orig_credit NUMBER;
921 l_n_adj_credit NUMBER;
922 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
923 l_n_load_ci_seq igs_ca_inst.sequence_number%TYPE;
924
925 -- Cursor for selecting the Negative Charge Adjustment credits, where the fee type is associated
926 -- to the current tax year being processed
927 CURSOR cur_qtre_crd(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
928 cp_d_end_date DATE,
929 cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
930 SELECT crd.*,
931 crd.rowid row_id,
932 fts.fee_type,
933 inv.invoice_id
934 FROM igs_fi_credits_all crd,
935 igs_fi_cr_types_all crt,
936 igs_fi_1098t_fts fts,
937 igs_fi_inv_int_all inv,
938 igs_fi_applications app
939 WHERE crd.credit_type_id = crt.credit_type_id
940 AND crt.credit_class = 'CHGADJ'
941 AND crd.party_id = cp_n_person_id
942 AND inv.invoice_id = app.invoice_id
943 AND crd.credit_id = app.credit_id
944 AND inv.fee_type = fts.fee_type
945 AND fts.tax_year_name = cp_v_tax_year_name
946 AND TRUNC(crd.transaction_date) <= TRUNC(cp_d_end_date)
947 AND crd.tax_year_code IS NULL
948 ORDER BY crd.fee_ci_sequence_number;
949
950 -- Cursor for checking if any charge for the same person and having the same FTCI was
951 -- reported in a prior year
952 CURSOR cur_chk_inv(cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
953 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
954 cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
955 cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
956 cp_d_start_date DATE) IS
957 SELECT 'x'
958 FROM igs_fi_inv_int_all inv,
959 igs_fi_1098t_setup stp
960 WHERE inv.person_id = cp_n_person_id
961 AND inv.fee_type = cp_v_fee_type
962 AND inv.fee_cal_type = cp_v_fee_cal_type
963 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
964 AND TRUNC(invoice_creation_date) < TRUNC(cp_d_start_date)
965 AND inv.tax_year_code IS NOT NULL
966 AND inv.tax_year_code = stp.tax_year_code
967 AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
968
969 l_b_process_credit BOOLEAN;
970 l_b_non_zero_credits_flag BOOLEAN;
971 l_v_message_name VARCHAR2(2000);
972 l_b_bool BOOLEAN;
973 l_var VARCHAR2(1);
974 l_n_box_num NUMBER(5);
975
976 l_n_prev_fee_ci_seq igs_ca_inst.sequence_number%TYPE;
977
978 BEGIN
979 l_n_orig_credit := 0;
980 l_n_adj_credit := 0;
981
982 l_n_prev_fee_ci_seq := null;
983
984 log_to_fnd(p_v_module => 'box236_credits',
985 p_v_string => 'Looping for the eligible credit records');
986
987 -- Loop across all the negative adjustment credits whose source invoices are identified as
988 -- QTRE
989 FOR l_rec_crd IN cur_qtre_crd(p_v_tax_year_name,
990 g_rec_1098t_setup.end_date,
991 p_n_person_id) LOOP
992
993 log_to_fnd(p_v_module => 'box236_credits',
994 p_v_string => 'Credit Record Found : '||l_rec_crd.credit_number);
995
996 IF l_n_prev_fee_ci_seq IS NULL OR l_n_prev_fee_ci_seq <> l_rec_crd.fee_ci_sequence_number THEN
997 l_n_prev_fee_ci_seq := l_rec_crd.fee_ci_sequence_number;
998
999 l_b_process_credit := TRUE;
1000
1001 -- Get the load period mapping for the fee period of the Source charge transaction
1002 l_b_bool := igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => l_rec_crd.fee_cal_type,
1003 p_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
1004 p_cal_category => 'FEE',
1005 p_ret_cal_type => l_v_load_cal_type,
1006 p_ret_ci_sequence_number => l_n_load_ci_seq,
1007 p_message_name => l_v_message_name);
1008
1009 IF l_v_message_name IS NOT NULL THEN
1010 fnd_message.set_name('IGS',
1011 l_v_message_name);
1012 fnd_message.set_module(g_v_package_name||'box236_credits');
1013 fnd_file.put_line(fnd_file.log,
1014 fnd_message.get);
1015 l_b_process_credit := FALSE;
1016 END IF;
1017 END IF;
1018
1019 IF l_b_process_credit THEN
1020
1021 -- Check for the Prior Load Period
1022 log_to_fnd(p_v_module => 'box236_credits',
1023 p_v_string => 'Checking for prior Load Period');
1024 l_b_process_credit := chk_prior_lps(p_v_load_cal_type => l_v_load_cal_type,
1025 p_n_load_ci_seq => l_n_load_ci_seq,
1026 p_d_txn_date => l_rec_crd.transaction_date);
1027 END IF;
1028
1029 IF l_b_process_credit THEN
1030 g_b_chg_crd_found := TRUE;
1031
1032 IF g_b_non_zero_credits_flag = FALSE THEN
1033
1034 -- Check for Non Credit Courses
1035 IF chk_non_credit_course(p_n_person_id,
1036 p_v_override_excl,
1037 l_v_load_cal_type,
1038 l_n_load_ci_seq) THEN
1039
1040 g_b_non_zero_credits_flag := TRUE;
1041 log_to_fnd(p_v_module => 'box236_credits',
1042 p_v_string => 'Non Zero Credits Flag set to FALSE');
1043 END IF;
1044 END IF;
1045
1046
1047 -- Check if the Source Invoice Id has been reported in a prior tax year
1048 OPEN cur_chk_inv(l_rec_crd.fee_cal_type,
1049 l_rec_crd.fee_ci_sequence_number,
1050 l_rec_crd.party_id,
1051 l_rec_crd.fee_type,
1052 g_rec_1098t_setup.start_date);
1053 FETCH cur_chk_inv INTO l_var;
1054 IF cur_chk_inv%FOUND THEN
1055
1056 -- If yes, then the credit should be marked for Box 3
1057 l_n_adj_credit := NVL(l_n_adj_credit,0) +
1058 NVL(l_rec_crd.amount,0);
1059 l_n_box_num := 3;
1060 log_to_fnd(p_v_module => 'box236_credits',
1061 p_v_string => 'Charge Record Found for Box 3');
1062 ELSE
1063
1064 -- Else the credit should be marked for Box 2
1065 l_n_orig_credit := NVL(l_n_orig_credit,0) +
1066 NVL(l_rec_crd.amount,0);
1067
1068 l_n_box_num := 2;
1069 log_to_fnd(p_v_module => 'box236_credits',
1070 p_v_string => 'Charge Record Not Found. Hence marking for Box 2');
1071 END IF;
1072 CLOSE cur_chk_inv;
1073
1074 l_n_cntr := l_n_cntr + 1;
1075 l_t_1098t_drilldown(l_n_cntr).transaction_id := l_rec_crd.credit_id;
1076 l_t_1098t_drilldown(l_n_cntr).transaction_code := 'C';
1077 l_t_1098t_drilldown(l_n_cntr).box_num := l_n_box_num;
1078
1079 log_to_fnd(p_v_module => 'box236_credits',
1080 p_v_string => 'Updating the Credits Record. Credit Number :'||l_rec_crd.credit_number);
1081
1082 igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
1083 x_credit_id => l_rec_crd.credit_id,
1084 x_credit_number => l_rec_crd.credit_number,
1085 x_status => l_rec_crd.status,
1086 x_credit_source => l_rec_crd.credit_source,
1087 x_party_id => l_rec_crd.party_id,
1088 x_credit_type_id => l_rec_crd.credit_type_id,
1089 x_credit_instrument => l_rec_crd.credit_instrument,
1090 x_description => l_rec_crd.description,
1091 x_amount => l_rec_crd.amount,
1092 x_currency_cd => l_rec_crd.currency_cd,
1093 x_exchange_rate => l_rec_crd.exchange_rate,
1094 x_transaction_date => l_rec_crd.transaction_date,
1095 x_effective_date => l_rec_crd.effective_date,
1096 x_reversal_date => l_rec_crd.reversal_date,
1097 x_reversal_reason_code => l_rec_crd.reversal_reason_code,
1098 x_reversal_comments => l_rec_crd.reversal_comments,
1099 x_unapplied_amount => l_rec_crd.unapplied_amount,
1100 x_source_transaction_id => l_rec_crd.source_transaction_id,
1101 x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
1102 x_merchant_id => l_rec_crd.merchant_id,
1103 x_credit_card_code => l_rec_crd.credit_card_code,
1104 x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
1105 x_credit_card_number => l_rec_crd.credit_card_number,
1106 x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
1107 x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
1108 x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
1109 x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
1110 x_fee_cal_type => l_rec_crd.fee_cal_type,
1111 x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
1112 x_attribute_category => l_rec_crd.attribute_category,
1113 x_attribute1 => l_rec_crd.attribute1,
1114 x_attribute2 => l_rec_crd.attribute2,
1115 x_attribute3 => l_rec_crd.attribute3,
1116 x_attribute4 => l_rec_crd.attribute4,
1117 x_attribute5 => l_rec_crd.attribute5,
1118 x_attribute6 => l_rec_crd.attribute6,
1119 x_attribute7 => l_rec_crd.attribute7,
1120 x_attribute8 => l_rec_crd.attribute8,
1121 x_attribute9 => l_rec_crd.attribute9,
1122 x_attribute10 => l_rec_crd.attribute10,
1123 x_attribute11 => l_rec_crd.attribute11,
1124 x_attribute12 => l_rec_crd.attribute12,
1125 x_attribute13 => l_rec_crd.attribute13,
1126 x_attribute14 => l_rec_crd.attribute14,
1127 x_attribute15 => l_rec_crd.attribute15,
1128 x_attribute16 => l_rec_crd.attribute16,
1129 x_attribute17 => l_rec_crd.attribute17,
1130 x_attribute18 => l_rec_crd.attribute18,
1131 x_attribute19 => l_rec_crd.attribute19,
1132 x_attribute20 => l_rec_crd.attribute20,
1133 x_gl_date => l_rec_crd.gl_date,
1134 x_check_number => l_rec_crd.check_number,
1135 x_source_transaction_type => l_rec_crd.source_transaction_type,
1136 x_source_transaction_ref => l_rec_crd.source_transaction_ref,
1137 x_credit_card_status_code => l_rec_crd.credit_card_status_code,
1138 x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
1139 x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
1140 x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
1141 x_batch_name => l_rec_crd.batch_name,
1142 x_deposit_date => l_rec_crd.deposit_date,
1143 x_source_invoice_id => l_rec_crd.source_invoice_id,
1144 x_tax_year_code => g_rec_1098t_setup.tax_year_code,
1145 x_waiver_name => l_rec_crd.waiver_name);
1146 END IF;
1147 END LOOP;
1148
1149 log_to_fnd(p_v_module => 'box236_credits',
1150 p_v_string => 'Original Credit '||l_n_orig_credit);
1151
1152 log_to_fnd(p_v_module => 'box236_credits',
1153 p_v_string => 'Adjustment Credit '||l_n_adj_credit);
1154
1155 p_n_orig_credit := l_n_orig_credit;
1156 p_n_adj_credit := l_n_adj_credit;
1157
1158 END box236_credits;
1159
1160 PROCEDURE box236_charges(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1161 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1162 p_v_override_excl VARCHAR2,
1163 p_n_orig_charge OUT NOCOPY NUMBER,
1164 p_n_adj_charge OUT NOCOPY NUMBER,
1165 p_v_next_acad_flag OUT NOCOPY VARCHAR2) AS
1166
1167 /******************************************************************
1168 Created By : Amit Gairola
1169 Date Created By : 06-May-2005
1170 Purpose : Procedure for Box 2, 3 and 6 from Charges
1171
1172 Known limitations,enhancements,remarks:
1173 Change History
1174 Who When What
1175 skharida 26-Jun-2006 Bug 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
1176 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
1177 ***************************************************************** */
1178 l_n_orig_chg NUMBER;
1179 l_n_adj_chg NUMBER;
1180 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
1181 l_n_load_ci_seq igs_ca_inst.sequence_number%TYPE;
1182
1183 CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1184 cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1185 cp_d_end_date DATE) IS
1186 SELECT inv.*, inv.rowid row_id
1187 FROM igs_fi_inv_int_all inv,
1188 igs_fi_1098t_fts fts
1189 WHERE inv.fee_type = fts.fee_type
1190 AND inv.person_id = cp_n_person_id
1191 AND fts.tax_year_name = cp_v_tax_year_name
1192 AND TRUNC(inv.invoice_creation_date) <= TRUNC(cp_d_end_date)
1193 AND inv.tax_year_code IS NULL
1194 ORDER BY inv.fee_ci_sequence_number;
1195
1196 CURSOR cur_next_acad_flag(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1197 cp_v_load_cal_type igs_ca_inst.cal_type%TYPE,
1198 cp_n_load_ci_seq igs_ca_inst.sequence_number%TYPE) IS
1199 SELECT 'x'
1200 FROM igs_fi_1098t_lps
1201 WHERE tax_year_name = cp_v_tax_year_name
1202 AND cal_type = cp_v_load_cal_type
1203 AND sequence_number = cp_n_load_ci_seq
1204 AND next_acad_flag = 'Y';
1205
1206 CURSOR cur_chk_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1207 cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
1208 cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1209 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1210 cp_d_start_date DATE) IS
1211 SELECT 'x'
1212 FROM igs_fi_inv_int_all inv,
1213 igs_fi_1098t_setup stp
1214 WHERE inv.person_id = cp_n_person_id
1215 AND inv.fee_type = cp_v_fee_type
1216 AND inv.fee_cal_type = cp_v_fee_cal_type
1217 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
1218 AND inv.tax_year_code IS NOT NULL
1219 AND inv.tax_year_code = stp.tax_year_code
1220 AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date)
1221 AND TRUNC(invoice_creation_date) < TRUNC(cp_d_start_date);
1222
1223 l_b_prc_chg BOOLEAN;
1224 l_v_next_acad_flag igs_fi_1098t_lps.next_acad_flag%TYPE;
1225 l_b_non_zero_credits_flag BOOLEAN;
1226 l_v_message_name VARCHAR2(2000);
1227 l_var VARCHAR2(1);
1228 l_b_bool BOOLEAN;
1229 l_n_box_num NUMBER(5);
1230 l_n_prev_fee_ci_seq igs_ca_inst.sequence_number%TYPE;
1231 BEGIN
1232 l_v_next_acad_flag := 'N';
1233 l_b_non_zero_credits_flag := FALSE;
1234 l_n_orig_chg := 0;
1235 l_n_adj_chg := 0;
1236 l_n_prev_fee_ci_seq := null;
1237
1238 log_to_fnd(p_v_module => 'box236_charges',
1239 p_v_string => 'Inside Box236 Charges');
1240
1241 -- Loop across all the QTRE charges with null tax year
1242 FOR l_rec_chg IN cur_chg(p_n_person_id,
1243 p_v_tax_year_name,
1244 g_rec_1098t_setup.end_date) LOOP
1245 log_to_fnd(p_v_module => 'box236_charges',
1246 p_v_string => 'Found the Charge transaction'||l_rec_chg.invoice_id);
1247 IF l_n_prev_fee_ci_seq IS NULL OR l_n_prev_fee_ci_seq <> l_rec_chg.fee_ci_sequence_number THEN
1248 l_b_prc_chg := TRUE;
1249
1250 -- Get the load period for the charge fee period
1251 l_b_bool := igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => l_rec_chg.fee_cal_type,
1252 p_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
1253 p_cal_category => 'FEE',
1254 p_ret_cal_type => l_v_load_cal_type,
1255 p_ret_ci_sequence_number => l_n_load_ci_seq,
1256 p_message_name => l_v_message_name);
1257 IF l_v_message_name IS NOT NULL THEN
1258 l_b_prc_chg := FALSE;
1259 fnd_message.set_module(g_v_package_name||'box236_charges');
1260 fnd_message.set_name('IGS',
1261 l_v_message_name);
1262 fnd_file.put_line(fnd_file.log,
1263 fnd_message.get);
1264 END IF;
1265 END IF;
1266
1267 -- Check for the Prior Load Period
1268 IF l_b_prc_chg THEN
1269 l_b_prc_chg := chk_prior_lps(p_v_load_cal_type => l_v_load_cal_type,
1270 p_n_load_ci_seq => l_n_load_ci_seq,
1271 p_d_txn_date => l_rec_chg.invoice_creation_date);
1272 END IF;
1273
1274 IF l_b_prc_chg THEN
1275 g_b_chg_crd_found := TRUE;
1276 log_to_fnd(p_v_module => 'box236_charges',
1277 p_v_string => 'Charge validations successful');
1278
1279 -- Calculate the Next Academic Flag
1280 IF l_v_next_acad_flag = 'N' THEN
1281 OPEN cur_next_acad_flag(p_v_tax_year_name,
1282 l_v_load_cal_type,
1283 l_n_load_ci_seq);
1284 FETCH cur_next_acad_flag INTO l_var;
1285 IF cur_next_acad_flag%FOUND THEN
1286 l_v_next_acad_flag := 'Y';
1287 END IF;
1288 CLOSE cur_next_acad_flag;
1289 END IF;
1290
1291 log_to_fnd(p_v_module => 'box236_charges',
1292 p_v_string => 'Next acad flag '||l_v_next_acad_flag);
1293
1294 IF NOT g_b_non_zero_credits_flag THEN
1295
1296 -- Check for Non Credit Course
1297 IF chk_non_credit_course(p_n_person_id => l_rec_chg.person_id,
1298 p_v_override_excl => p_v_override_excl,
1299 p_v_load_cal_type => l_v_load_cal_type,
1300 p_n_load_ci_seq => l_n_load_ci_seq) THEN
1301 g_b_non_zero_credits_flag := TRUE;
1302 END IF;
1303 END IF;
1304
1305 -- Check if the charge is an original or adjustment charge
1306 OPEN cur_chk_chg(l_rec_chg.person_id,
1307 l_rec_chg.fee_type,
1308 l_rec_chg.fee_cal_type,
1309 l_rec_chg.fee_ci_sequence_number,
1310 g_rec_1098t_setup.start_date);
1311 FETCH cur_chk_chg INTO l_var;
1312 IF cur_chk_chg%FOUND THEN
1313 l_n_adj_chg := NVL(l_n_adj_chg,0) +
1314 NVL(l_rec_chg.invoice_amount,0);
1315 l_n_box_num := 3;
1316 ELSE
1317 l_n_orig_chg := NVL(l_n_orig_chg,0) +
1318 NVL(l_rec_chg.invoice_amount,0);
1319 l_n_box_num := 2;
1320 END IF;
1321 CLOSE cur_chk_chg;
1322
1323
1324 l_n_cntr := l_n_cntr + 1;
1325 l_t_1098t_drilldown(l_n_cntr).transaction_id := l_rec_chg.invoice_id;
1326 l_t_1098t_drilldown(l_n_cntr).transaction_code := 'D';
1327 l_t_1098t_drilldown(l_n_cntr).box_num := l_n_box_num;
1328
1329 log_to_fnd(p_v_module => 'box236_charges',
1330 p_v_string => 'Updating Invoice record '||l_rec_chg.invoice_id);
1331 igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
1332 x_invoice_id => l_rec_chg.invoice_id,
1333 x_person_id => l_rec_chg.person_id,
1334 x_fee_type => l_rec_chg.fee_type,
1335 x_fee_cat => l_rec_chg.fee_cat,
1336 x_fee_cal_type => l_rec_chg.fee_cal_type,
1337 x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
1338 x_course_cd => l_rec_chg.course_cd,
1339 x_attendance_mode => l_rec_chg.attendance_mode,
1340 x_attendance_type => l_rec_chg.attendance_type,
1341 x_invoice_amount_due => l_rec_chg.invoice_amount_due,
1342 x_invoice_creation_date => l_rec_chg.invoice_creation_date,
1343 x_invoice_desc => l_rec_chg.invoice_desc,
1344 x_transaction_type => l_rec_chg.transaction_type,
1345 x_currency_cd => l_rec_chg.currency_cd,
1346 x_status => l_rec_chg.status,
1347 x_attribute_category => l_rec_chg.attribute_category,
1348 x_attribute1 => l_rec_chg.attribute1,
1349 x_attribute2 => l_rec_chg.attribute2,
1350 x_attribute3 => l_rec_chg.attribute3,
1351 x_attribute4 => l_rec_chg.attribute4,
1352 x_attribute5 => l_rec_chg.attribute5,
1353 x_attribute6 => l_rec_chg.attribute6,
1354 x_attribute7 => l_rec_chg.attribute7,
1355 x_attribute8 => l_rec_chg.attribute8,
1356 x_attribute9 => l_rec_chg.attribute9,
1357 x_attribute10 => l_rec_chg.attribute10,
1358 x_invoice_amount => l_rec_chg.invoice_amount,
1359 x_bill_id => l_rec_chg.bill_id,
1360 x_bill_number => l_rec_chg.bill_number,
1361 x_bill_date => l_rec_chg.bill_date,
1362 x_waiver_flag => l_rec_chg.waiver_flag,
1363 x_waiver_reason => l_rec_chg.waiver_reason,
1364 x_effective_date => l_rec_chg.effective_date,
1365 x_invoice_number => l_rec_chg.invoice_number,
1366 x_exchange_rate => l_rec_chg.exchange_rate,
1367 x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
1368 x_optional_fee_flag => l_rec_chg.optional_fee_flag,
1369 x_reversal_gl_date => l_rec_chg.reversal_gl_date,
1370 x_tax_year_code => g_rec_1098t_setup.tax_year_code,
1371 x_waiver_name => l_rec_chg.waiver_name);
1372 END IF;
1373 END LOOP;
1374
1375 p_n_orig_charge := l_n_orig_chg;
1376 p_n_adj_charge := l_n_adj_chg;
1377 p_v_next_acad_flag := l_v_next_acad_flag;
1378 END box236_charges;
1379
1380 PROCEDURE box45_credits(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1381 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1382 p_n_orig_credit OUT NOCOPY NUMBER,
1383 p_n_adj_credit OUT NOCOPY NUMBER) AS
1384
1385 /******************************************************************
1386 Created By : Amit Gairola
1387 Date Created By : 06-May-2005
1388 Purpose : Procedure for Box 4 and 5 for Credits
1389
1390 Known limitations,enhancements,remarks:
1391 Change History
1392 Who When What
1393 skharida 22/05/06 Bug 5213590 - Added changes to check for waiver name
1394 agairola 23/11/05 Bug:4747419 - Modified logic for Waiver Credits
1395 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
1396 ***************************************************************** */
1397 l_n_aid_orig_credit NUMBER;
1398 l_n_aid_adj_credit NUMBER;
1399 l_b_party_sponsor BOOLEAN;
1400 l_b_process_credit BOOLEAN;
1401 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
1402 l_n_load_ci_seq igs_ca_inst.sequence_number%TYPE;
1403
1404 l_v_crd_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE;
1405 l_v_orig_crd_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE;
1406 l_v_message_name VARCHAR2(2000);
1407 l_var VARCHAR2(1);
1408 l_n_box_num NUMBER(5);
1409
1410 CURSOR cur_chk_spn(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1411 cp_v_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE) IS
1412 SELECT 'x'
1413 FROM igf_aw_fund_mast_all fmast,
1414 igf_aw_fund_cat_all fcat
1415 WHERE fmast.fund_code = fcat.fund_code
1416 AND fcat.sys_fund_type = cp_v_sys_fund_type
1417 AND fmast.party_id = cp_n_person_id;
1418
1419 CURSOR cur_crd(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1420 cp_d_start_date DATE,
1421 cp_d_end_date DATE) IS
1422 SELECT crd.*,
1423 crd.rowid row_id,
1424 crt.credit_class
1425 FROM igs_fi_credits_all crd,
1426 igs_fi_cr_types_all crt
1427 WHERE crd.credit_type_id = crt.credit_type_id
1428 AND crd.status = 'CLEARED'
1429 AND crd.party_id = cp_n_person_id
1430 AND crt.credit_class IN ('SPNSP','EXTFA','INTFA','WAIVER')
1431 AND TRUNC(crd.transaction_date) BETWEEN TRUNC(cp_d_start_date) AND TRUNC(cp_d_end_date)
1432 AND crd.tax_year_code IS NULL;
1433
1434 CURSOR cur_chk_adj(cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
1435 SELECT 'x'
1436 FROM igf_db_awd_disb_dtl_all
1437 WHERE spnsr_credit_id = cp_n_credit_id;
1438
1439 CURSOR cur_chk_orig_credit(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1440 cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1441 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1442 cp_d_start_date DATE) IS
1443 SELECT crd.credit_id,
1444 crd.waiver_name
1445 FROM igs_fi_credits_all crd,
1446 igs_fi_1098t_setup stp
1447 WHERE party_id = cp_n_person_id
1448 AND fee_cal_type = cp_v_fee_cal_type
1449 AND fee_ci_sequence_number = cp_n_fee_ci_seq
1450 AND TRUNC(transaction_date) < TRUNC(cp_d_start_date)
1451 AND crd.tax_year_code IS NOT NULL
1452 AND crd.tax_year_code = stp.tax_year_code
1453 AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
1454
1455 l_rec_orig_crd cur_chk_orig_credit%ROWTYPE;
1456 l_b_orig_credit BOOLEAN;
1457 l_b_rec_found BOOLEAN;
1458 BEGIN
1459 l_n_aid_orig_credit := 0;
1460 l_n_aid_adj_credit := 0;
1461 l_b_party_sponsor := FALSE;
1462
1463 log_to_fnd(p_v_module => 'box45_credits',
1464 p_v_string => 'Inside Box 45 Credits');
1465
1466 -- Check if the party is a sponsor
1467 OPEN cur_chk_spn(p_n_person_id,
1468 'SPONSOR');
1469 FETCH cur_chk_spn INTO l_var;
1470 IF cur_chk_spn%FOUND THEN
1471 l_b_party_sponsor := TRUE;
1472 END IF;
1473 CLOSE cur_chk_spn;
1474
1475 IF l_b_party_sponsor THEN
1476 log_to_fnd(p_v_module => 'box45_credits',
1477 p_v_string => 'Party '||p_n_person_id||' is a Sponsor');
1478 END IF;
1479
1480
1481 -- Loop across all credits that have null value for tax year
1482 FOR l_rec_crd IN cur_crd(p_n_person_id,
1483 g_rec_1098t_setup.start_date,
1484 g_rec_1098t_setup.end_date) LOOP
1485 l_b_process_credit := TRUE;
1486
1487 IF l_rec_crd.credit_class <> 'WAIVER' THEN
1488 -- Get the System Fund Type
1489 l_v_crd_sys_fund_type := get_sys_fund_type(p_n_credit_id => l_rec_crd.credit_id,
1490 p_n_invoice_id => null);
1491 log_to_fnd(p_v_module => 'box45_credits',
1492 p_v_string => 'System Fund Type'||l_v_crd_sys_fund_type);
1493
1494 -- Check if the System Fund Type is associated with the tax year
1495 OPEN cur_chk_1098t_sfts(p_v_tax_year_name,
1496 l_v_crd_sys_fund_type);
1497 FETCH cur_chk_1098t_sfts INTO l_var;
1498 IF cur_chk_1098t_sfts%NOTFOUND THEN
1499 log_to_fnd(p_v_module => 'box45_credits',
1500 p_v_string => 'System Fund Type is not associated with the Tax Year');
1501
1502 l_b_process_credit := FALSE;
1503 END IF;
1504 CLOSE cur_chk_1098t_sfts;
1505 END IF;
1506
1507 -- If the party is a sponsor, then check if the credit is due to downward adjustment of
1508 -- Sponsorship
1509 IF l_b_party_sponsor THEN
1510 OPEN cur_chk_adj(l_rec_crd.credit_id);
1511 FETCH cur_chk_adj INTO l_var;
1512 IF cur_chk_adj%FOUND THEN
1513 log_to_fnd(p_v_module => 'box45_credits',
1514 p_v_string => 'For the sponsor, this credit is due to a downward adj of sponsorship. Hence it is not reported');
1515 l_b_process_credit := FALSE;
1516 END IF;
1517 CLOSE cur_chk_adj;
1518 END IF;
1519
1520 -- Check for original or adjustment credit
1521 IF l_b_process_credit THEN
1522 l_b_orig_credit := TRUE;
1523 FOR l_rec_orig_crd IN cur_chk_orig_credit(p_n_person_id,
1524 l_rec_crd.fee_cal_type,
1525 l_rec_crd.fee_ci_sequence_number,
1526 g_rec_1098t_setup.start_date) LOOP
1527 l_b_rec_found := TRUE;
1528
1529 IF l_rec_crd.credit_class = 'WAIVER' THEN
1530 log_to_fnd(p_v_module => 'box45_credits',
1531 p_v_string => 'Waiver Credit '||l_rec_crd.credit_id||' is an adjustment credit');
1532 IF ((l_rec_orig_crd.waiver_name IS NOT NULL)
1533 AND (l_rec_orig_crd.waiver_name = l_rec_crd.waiver_name)) THEN
1534 l_b_orig_credit := FALSE;
1535 EXIT;
1536 END IF;
1537 ELSE
1538
1539 l_v_orig_crd_sys_fund_type := get_sys_fund_type(p_n_credit_id => l_rec_orig_crd.credit_id,
1540 p_n_invoice_id => null);
1541 IF l_v_orig_crd_sys_fund_type = l_v_crd_sys_fund_type THEN
1542 log_to_fnd(p_v_module => 'box45_credits',
1543 p_v_string => 'Credit '||l_rec_crd.credit_id||' is an adjustment credit');
1544 l_b_orig_credit := FALSE;
1545 EXIT;
1546 END IF;
1547 END IF;
1548 END LOOP;
1549
1550 -- If it is original credit, then it is identified for Box4 else
1551 -- it is an adjustment credit and it is identified for Box5
1552 IF l_b_orig_credit THEN
1553 l_n_aid_orig_credit := NVL(l_n_aid_orig_credit,0) +
1554 NVL(l_rec_crd.amount,0);
1555 l_n_box_num := 4;
1556 ELSE
1557 l_n_aid_adj_credit := NVL(l_n_aid_adj_credit,0) +
1558 NVL(l_rec_crd.amount,0);
1559 l_n_box_num := 5;
1560 END IF;
1561
1562 l_n_cntr := l_n_cntr + 1;
1563 l_t_1098t_drilldown(l_n_cntr).transaction_id := l_rec_crd.credit_id;
1564 l_t_1098t_drilldown(l_n_cntr).transaction_code := 'C';
1565 l_t_1098t_drilldown(l_n_cntr).box_num := l_n_box_num;
1566
1567 l_n_box_num := null;
1568
1569 log_to_fnd(p_v_module => 'box45_credits',
1570 p_v_string => 'Updating the Credit '||l_rec_crd.credit_id);
1571 igs_fi_credits_pkg.update_row(x_rowid => l_rec_crd.row_id,
1572 x_credit_id => l_rec_crd.credit_id,
1573 x_credit_number => l_rec_crd.credit_number,
1574 x_status => l_rec_crd.status,
1575 x_credit_source => l_rec_crd.credit_source,
1576 x_party_id => l_rec_crd.party_id,
1577 x_credit_type_id => l_rec_crd.credit_type_id,
1578 x_credit_instrument => l_rec_crd.credit_instrument,
1579 x_description => l_rec_crd.description,
1580 x_amount => l_rec_crd.amount,
1581 x_currency_cd => l_rec_crd.currency_cd,
1582 x_exchange_rate => l_rec_crd.exchange_rate,
1583 x_transaction_date => l_rec_crd.transaction_date,
1584 x_effective_date => l_rec_crd.effective_date,
1585 x_reversal_date => l_rec_crd.reversal_date,
1586 x_reversal_reason_code => l_rec_crd.reversal_reason_code,
1587 x_reversal_comments => l_rec_crd.reversal_comments,
1588 x_unapplied_amount => l_rec_crd.unapplied_amount,
1589 x_source_transaction_id => l_rec_crd.source_transaction_id,
1590 x_receipt_lockbox_number => l_rec_crd.receipt_lockbox_number,
1591 x_merchant_id => l_rec_crd.merchant_id,
1592 x_credit_card_code => l_rec_crd.credit_card_code,
1593 x_credit_card_holder_name => l_rec_crd.credit_card_holder_name,
1594 x_credit_card_number => l_rec_crd.credit_card_number,
1595 x_credit_card_expiration_date => l_rec_crd.credit_card_expiration_date,
1596 x_credit_card_approval_code => l_rec_crd.credit_card_approval_code,
1597 x_awd_yr_cal_type => l_rec_crd.awd_yr_cal_type,
1598 x_awd_yr_ci_sequence_number => l_rec_crd.awd_yr_ci_sequence_number,
1599 x_fee_cal_type => l_rec_crd.fee_cal_type,
1600 x_fee_ci_sequence_number => l_rec_crd.fee_ci_sequence_number,
1601 x_attribute_category => l_rec_crd.attribute_category,
1602 x_attribute1 => l_rec_crd.attribute1,
1603 x_attribute2 => l_rec_crd.attribute2,
1604 x_attribute3 => l_rec_crd.attribute3,
1605 x_attribute4 => l_rec_crd.attribute4,
1606 x_attribute5 => l_rec_crd.attribute5,
1607 x_attribute6 => l_rec_crd.attribute6,
1608 x_attribute7 => l_rec_crd.attribute7,
1609 x_attribute8 => l_rec_crd.attribute8,
1610 x_attribute9 => l_rec_crd.attribute9,
1611 x_attribute10 => l_rec_crd.attribute10,
1612 x_attribute11 => l_rec_crd.attribute11,
1613 x_attribute12 => l_rec_crd.attribute12,
1614 x_attribute13 => l_rec_crd.attribute13,
1615 x_attribute14 => l_rec_crd.attribute14,
1616 x_attribute15 => l_rec_crd.attribute15,
1617 x_attribute16 => l_rec_crd.attribute16,
1618 x_attribute17 => l_rec_crd.attribute17,
1619 x_attribute18 => l_rec_crd.attribute18,
1620 x_attribute19 => l_rec_crd.attribute19,
1621 x_attribute20 => l_rec_crd.attribute20,
1622 x_gl_date => l_rec_crd.gl_date,
1623 x_check_number => l_rec_crd.check_number,
1624 x_source_transaction_type => l_rec_crd.source_transaction_type,
1625 x_source_transaction_ref => l_rec_crd.source_transaction_ref,
1626 x_credit_card_status_code => l_rec_crd.credit_card_status_code,
1627 x_credit_card_payee_cd => l_rec_crd.credit_card_payee_cd,
1628 x_credit_card_tangible_cd => l_rec_crd.credit_card_tangible_cd,
1629 x_lockbox_interface_id => l_rec_crd.lockbox_interface_id,
1630 x_batch_name => l_rec_crd.batch_name,
1631 x_deposit_date => l_rec_crd.deposit_date,
1632 x_source_invoice_id => l_rec_crd.source_invoice_id,
1633 x_tax_year_code => g_rec_1098t_setup.tax_year_code,
1634 x_waiver_name => l_rec_crd.waiver_name);
1635 END IF;
1636 END LOOP;
1637
1638 p_n_orig_credit := l_n_aid_orig_credit;
1639 p_n_adj_credit := l_n_aid_adj_credit;
1640 END box45_credits;
1641
1642 PROCEDURE box45_charges(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1643 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1644 p_n_orig_charge OUT NOCOPY NUMBER,
1645 p_n_adj_charge OUT NOCOPY NUMBER) AS
1646
1647 /******************************************************************
1648 Created By : Amit Gairola
1649 Date Created By : 06-May-2005
1650 Purpose : Procedure for box 4 and 5 charges
1651
1652 Known limitations,enhancements,remarks:
1653 Change History
1654 Who When What
1655 skharida 26/06/06 Bug 5208136 - Removed the obsoleted columns of the table IGS_FI_INV_INT_ALL
1656 skharida 22/05/06 Bug 5213590 - Added changes to check for waiver name
1657 agairola 23/11/05 Bug:4747419 - Modified logic for Waiver Adjustment Charges
1658 agairola 05-Aug-2005 Changes as per Waiver build: 3392095
1659 ***************************************************************** */
1660 l_n_aid_orig_charge NUMBER;
1661 l_n_aid_adj_charge NUMBER;
1662
1663 CURSOR cur_chg(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1664 cp_d_start_date DATE,
1665 cp_d_end_date DATE) IS
1666 SELECT inv.rowid row_id,
1667 inv.*,
1668 ft.s_fee_type
1669 FROM igs_fi_inv_int_all inv,
1670 igs_fi_fee_type ft
1671 WHERE inv.person_id = cp_n_person_id
1672 AND ft.fee_type = inv.fee_type
1673 AND inv.tax_year_code IS NULL
1674 AND TRUNC(inv.invoice_creation_date) BETWEEN TRUNC(cp_d_start_date) AND TRUNC(cp_d_end_date)
1675 AND ft.s_fee_type IN ('AID_ADJ','WAIVER_ADJ');
1676
1677 CURSOR cur_chk_orig_credit(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1678 cp_v_fee_cal_type igs_ca_inst.cal_type%TYPE,
1679 cp_n_fee_ci_seq igs_ca_inst.sequence_number%TYPE,
1680 cp_d_start_date DATE) IS
1681 SELECT crd.credit_id,
1682 crd.waiver_name
1683 FROM igs_fi_credits_all crd,
1684 igs_fi_1098t_setup stp
1685 WHERE crd.party_id = cp_n_person_id
1686 AND crd.fee_cal_type = cp_v_fee_cal_type
1687 AND crd.fee_ci_sequence_number = cp_n_fee_ci_seq
1688 AND TRUNC(transaction_date) < TRUNC(cp_d_start_date)
1689 AND crd.tax_year_code IS NOT NULL
1690 AND crd.tax_year_code = stp.tax_year_code
1691 AND TRUNC(stp.end_date) < TRUNC(cp_d_start_date);
1692
1693 l_rec_orig_crd cur_chk_orig_credit%ROWTYPE;
1694 l_b_process_charge BOOLEAN;
1695 l_v_chg_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE;
1696 l_v_orig_crd_sys_fund_type igf_aw_fund_cat_all.sys_fund_type%TYPE;
1697 l_b_orig_credit BOOLEAN;
1698 l_n_box_num NUMBER;
1699 l_var VARCHAR2(1);
1700 BEGIN
1701 l_n_aid_orig_charge := 0;
1702 l_n_aid_adj_charge := 0;
1703
1704 log_to_fnd(p_v_module => 'box45_charges',
1705 p_v_string => 'Inside Box 45 Charges');
1706
1707 -- Loop through the Aid Adjustment charges
1708 FOR l_rec_chg IN cur_chg(p_n_person_id,
1709 g_rec_1098t_setup.start_date,
1710 g_rec_1098t_setup.end_date) LOOP
1711 l_b_process_charge := TRUE;
1712
1713 IF l_rec_chg.s_fee_type <> 'WAIVER_ADJ' THEN
1714 l_v_chg_sys_fund_type := get_sys_fund_type(p_n_credit_id => null,
1715 p_n_invoice_id => l_rec_chg.invoice_id);
1716 log_to_fnd(p_v_module => 'box45_charges',
1717 p_v_string => 'Processing Invoice Id '||l_rec_chg.invoice_id||' For this invoice, system fund type is '||l_v_chg_sys_fund_type);
1718
1719 -- Check if the System Fund Type is associated to the Tax Year
1720 OPEN cur_chk_1098t_sfts(p_v_tax_year_name,
1721 l_v_chg_sys_fund_type);
1722 FETCH cur_chk_1098t_sfts INTO l_var;
1723 IF cur_chk_1098t_sfts%NOTFOUND THEN
1724 log_to_fnd(p_v_module => 'box45_charges',
1725 p_v_string => 'System Fund Type not associated to the tax year');
1726 l_b_process_charge := FALSE;
1727 END IF;
1728 CLOSE cur_chk_1098t_sfts;
1729 END IF;
1730
1731
1732 IF l_b_process_charge THEN
1733 l_b_orig_credit := TRUE;
1734
1735 -- Evaluate if the Charge selected is an original or adjustment charge
1736 FOR l_rec_orig_crd IN cur_chk_orig_credit(p_n_person_id,
1737 l_rec_chg.fee_cal_type,
1738 l_rec_chg.fee_ci_sequence_number,
1739 g_rec_1098t_setup.start_date) LOOP
1740
1741 IF l_rec_chg.s_fee_type = 'WAIVER_ADJ' THEN
1742 log_to_fnd(p_v_module => 'box45_charges',
1743 p_v_string => 'For the waiver adjustment charge, identified it is adjustment');
1744 IF ((l_rec_orig_crd.waiver_name = l_rec_chg.waiver_name)
1745 AND (l_rec_orig_crd.waiver_name IS NOT NULL)) THEN
1746 l_b_orig_credit := FALSE;
1747 EXIT;
1748 END IF;
1749 ELSE
1750
1751 l_v_orig_crd_sys_fund_type := get_sys_fund_type(p_n_credit_id => l_rec_orig_crd.credit_id,
1752 p_n_invoice_id => null);
1753
1754 IF l_v_orig_crd_sys_fund_type = l_v_chg_sys_fund_type THEN
1755 log_to_fnd(p_v_module => 'box45_charges',
1756 p_v_string => 'System Fund Type not associated to the tax year');
1757 l_b_orig_credit := FALSE;
1758 EXIT;
1759 END IF;
1760 END IF;
1761 END LOOP;
1762
1763 IF l_b_orig_credit THEN
1764 l_n_aid_orig_charge := NVL(l_n_aid_orig_charge,0) +
1765 NVL(l_rec_chg.invoice_amount,0);
1766 l_n_box_num := 4;
1767 ELSE
1768 l_n_aid_adj_charge := NVL(l_n_aid_adj_charge,0) +
1769 NVL(l_rec_chg.invoice_amount,0);
1770 l_n_box_num := 5;
1771 END IF;
1772
1773 l_n_cntr := l_n_cntr + 1;
1774 l_t_1098t_drilldown(l_n_cntr).transaction_id := l_rec_chg.invoice_id;
1775 l_t_1098t_drilldown(l_n_cntr).transaction_code := 'D';
1776 l_t_1098t_drilldown(l_n_cntr).box_num := l_n_box_num;
1777
1778 log_to_fnd(p_v_module => 'box45_charges',
1779 p_v_string => 'Updating Charge transaction '||l_rec_chg.invoice_id);
1780 igs_fi_inv_int_pkg.update_row(x_rowid => l_rec_chg.row_id,
1781 x_invoice_id => l_rec_chg.invoice_id,
1782 x_person_id => l_rec_chg.person_id,
1783 x_fee_type => l_rec_chg.fee_type,
1784 x_fee_cat => l_rec_chg.fee_cat,
1785 x_fee_cal_type => l_rec_chg.fee_cal_type,
1786 x_fee_ci_sequence_number => l_rec_chg.fee_ci_sequence_number,
1787 x_course_cd => l_rec_chg.course_cd,
1788 x_attendance_mode => l_rec_chg.attendance_mode,
1789 x_attendance_type => l_rec_chg.attendance_type,
1790 x_invoice_amount_due => l_rec_chg.invoice_amount_due,
1791 x_invoice_creation_date => l_rec_chg.invoice_creation_date,
1792 x_invoice_desc => l_rec_chg.invoice_desc,
1793 x_transaction_type => l_rec_chg.transaction_type,
1794 x_currency_cd => l_rec_chg.currency_cd,
1795 x_status => l_rec_chg.status,
1796 x_attribute_category => l_rec_chg.attribute_category,
1797 x_attribute1 => l_rec_chg.attribute1,
1798 x_attribute2 => l_rec_chg.attribute2,
1799 x_attribute3 => l_rec_chg.attribute3,
1800 x_attribute4 => l_rec_chg.attribute4,
1801 x_attribute5 => l_rec_chg.attribute5,
1802 x_attribute6 => l_rec_chg.attribute6,
1803 x_attribute7 => l_rec_chg.attribute7,
1804 x_attribute8 => l_rec_chg.attribute8,
1805 x_attribute9 => l_rec_chg.attribute9,
1806 x_attribute10 => l_rec_chg.attribute10,
1807 x_invoice_amount => l_rec_chg.invoice_amount,
1808 x_bill_id => l_rec_chg.bill_id,
1809 x_bill_number => l_rec_chg.bill_number,
1810 x_bill_date => l_rec_chg.bill_date,
1811 x_waiver_flag => l_rec_chg.waiver_flag,
1812 x_waiver_reason => l_rec_chg.waiver_reason,
1813 x_effective_date => l_rec_chg.effective_date,
1814 x_invoice_number => l_rec_chg.invoice_number,
1815 x_exchange_rate => l_rec_chg.exchange_rate,
1816 x_bill_payment_due_date => l_rec_chg.bill_payment_due_date,
1817 x_optional_fee_flag => l_rec_chg.optional_fee_flag,
1818 x_reversal_gl_date => l_rec_chg.reversal_gl_date,
1819 x_tax_year_code => g_rec_1098t_setup.tax_year_code,
1820 x_waiver_name => l_rec_chg.waiver_name);
1821 END IF;
1822 END LOOP;
1823
1824 p_n_orig_charge := l_n_aid_orig_charge;
1825 p_n_adj_charge := l_n_aid_adj_charge;
1826
1827 END box45_charges;
1828
1829 FUNCTION compute_box8(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1830 p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) RETURN VARCHAR2 AS
1831
1832 /******************************************************************
1833 Created By : Amit Gairola
1834 Date Created By : 06-May-2005
1835 Purpose : Function for Box 8
1836
1837 Known limitations,enhancements,remarks:
1838 Change History
1839 Who When What
1840 ***************************************************************** */
1841 CURSOR cur_lps(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) IS
1842 SELECT cal_type,
1843 sequence_number
1844 FROM igs_fi_1098t_lps
1845 WHERE tax_year_name = cp_v_tax_year_name
1846 AND half_time_flag = 'Y';
1847
1848 CURSOR cur_ats(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1849 cp_v_att_type igs_en_atd_type.attendance_type%TYPE) IS
1850 SELECT 'x'
1851 FROM igs_fi_1098t_ats
1852 WHERE tax_year_name = cp_v_tax_year_name
1853 AND attendance_type = cp_v_att_type;
1854
1855 l_v_att_type igs_en_atd_type.attendance_type%TYPE;
1856 l_n_cp igs_fi_invln_int_all.credit_points%TYPE;
1857 l_n_fte igs_fi_invln_int_all.eftsu%TYPE;
1858 l_v_half_time_flag igs_fi_1098t_data.half_time_flag%TYPE;
1859 l_var VARCHAR2(1);
1860 BEGIN
1861 l_v_half_time_flag := 'N';
1862
1863 log_to_fnd(p_v_module => 'compute_box8',
1864 p_v_string => 'Inside Box 8');
1865
1866 -- Call the EN API to check for the Institution Attendance Type
1867 -- for all the Load Periods associated to the tax year
1868 FOR l_rec_lps IN cur_lps(p_v_tax_year_name) LOOP
1869 igs_en_prc_load.enrp_get_inst_latt(p_person_id => p_n_person_id,
1870 p_load_cal_type => l_rec_lps.cal_type,
1871 p_load_seq_number => l_rec_lps.sequence_number,
1872 p_attendance => l_v_att_type,
1873 p_credit_points => l_n_cp,
1874 p_fte => l_n_fte);
1875
1876 -- Check if the Attendance Type is associated to the Tax Year
1877 OPEN cur_ats(p_v_tax_year_name,
1878 l_v_att_type);
1879 FETCH cur_ats INTO l_var;
1880 IF cur_ats%FOUND THEN
1881 l_v_half_time_flag := 'Y';
1882 END IF;
1883 CLOSE cur_ats;
1884
1885 IF l_v_half_time_flag = 'Y' THEN
1886 exit;
1887 END IF;
1888 END LOOP;
1889
1890 log_to_fnd(p_v_module => 'compute_box8',
1891 p_v_string => 'Half time flag computed to '||l_v_half_time_flag);
1892
1893 RETURN l_v_half_time_flag;
1894 END compute_box8;
1895
1896 FUNCTION compute_box9(p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1897 p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) RETURN VARCHAR2 AS
1898
1899 /******************************************************************
1900 Created By : Amit Gairola
1901 Date Created By : 06-May-2005
1902 Purpose : Function for Box 9
1903
1904 Known limitations,enhancements,remarks:
1905 Change History
1906 Who When What
1907 ***************************************************************** */
1908
1909 CURSOR cur_lps(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE) IS
1910 SELECT cal_type,
1911 sequence_number
1912 FROM igs_fi_1098t_lps
1913 WHERE tax_year_name = cp_v_tax_year_name
1914 AND grad_flag = 'Y';
1915
1916 CURSOR cur_load_teach(cp_v_cal_type igs_ca_inst.cal_type%TYPE,
1917 cp_n_ci_seq igs_ca_inst.sequence_number%TYPE) IS
1918 SELECT teach_cal_type,
1919 teach_ci_sequence_number
1920 FROM igs_ca_load_to_teach_v
1921 WHERE load_cal_type = cp_v_cal_type
1922 AND load_ci_sequence_number = cp_n_ci_seq;
1923
1924 CURSOR cur_en_su_att(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
1925 cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1926 cp_v_teach_cal_type igs_ca_inst.cal_type%TYPE,
1927 cp_n_teach_ci_seq igs_ca_inst.sequence_number%TYPE) IS
1928 SELECT 'x'
1929 FROM igs_en_su_attempt_all sua,
1930 igs_ps_ver ps,
1931 igs_fi_1098t_pts pts
1932 WHERE sua.person_id = cp_n_person_id
1933 AND sua.cal_type = cp_v_teach_cal_type
1934 AND sua.ci_sequence_number = cp_n_teach_ci_seq
1935 AND sua.unit_attempt_status NOT IN ('UNCONFIRM',
1936 'WAITLISTED')
1937 AND ps.course_cd = sua.course_cd
1938 AND pts.course_type = ps.course_type
1939 AND pts.tax_year_name = cp_v_tax_year_name;
1940
1941 l_v_grad_flag VARCHAR2(1);
1942 l_var VARCHAR2(1);
1943 BEGIN
1944 l_v_grad_flag := 'N';
1945 log_to_fnd(p_v_module => 'compute_box9',
1946 p_v_string => 'Inside Box 9');
1947
1948 -- Loop across the Load Periods for the tax year
1949 FOR l_rec_lps IN cur_lps(p_v_tax_year_name) LOOP
1950
1951 -- Fetch the teaching periods associated to the load period
1952 FOR l_rec_teach IN cur_load_teach(l_rec_lps.cal_type,
1953 l_rec_lps.sequence_number) LOOP
1954
1955 -- Verify if there exists a unit section for the student program attempt
1956 -- for the teaching period identified where the program type is setup as
1957 -- graduate program type in 1098T setup
1958 OPEN cur_en_su_att(p_n_person_id,
1959 p_v_tax_year_name,
1960 l_rec_teach.teach_cal_type,
1961 l_rec_teach.teach_ci_sequence_number);
1962 FETCH cur_en_su_att INTO l_var;
1963 IF cur_en_su_att%FOUND THEN
1964 l_v_grad_flag := 'Y';
1965 END IF;
1966 CLOSE cur_en_su_att;
1967
1968 IF l_v_grad_flag = 'Y' THEN
1969 EXIT;
1970 END IF;
1971 END LOOP;
1972
1973 IF l_v_grad_flag = 'Y' THEN
1974 EXIT;
1975 END IF;
1976 END LOOP;
1977
1978 log_to_fnd(p_v_module => 'compute_box9',
1979 p_v_string => 'Grad Flag computed to '||l_v_grad_flag);
1980
1981 RETURN l_v_grad_flag;
1982 END compute_box9;
1983
1984 PROCEDURE insert_1098t_data(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
1985 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
1986 p_v_full_name igs_pe_person_base_v.full_name%TYPE,
1987 p_n_box2 igs_fi_1098t_data.billed_amt%TYPE,
1988 p_n_box3 igs_fi_1098t_data.adj_amt%TYPE,
1989 p_n_box4 igs_fi_1098t_data.fin_aid_amt%TYPE,
1990 p_n_box5 igs_fi_1098t_data.fin_aid_adj_amt%TYPE,
1991 p_v_box6 igs_fi_1098t_data.next_acad_flag%TYPE,
1992 p_v_box8 igs_fi_1098t_data.half_time_flag%TYPE,
1993 p_v_box9 igs_fi_1098t_data.grad_flag%TYPE,
1994 p_v_file_addr_correction VARCHAR2) AS
1995 /******************************************************************
1996 Created By : Amit Gairola
1997 Date Created By : 06-May-2005
1998 Purpose : Procedure for creating 1098t records
1999
2000 Known limitations,enhancements,remarks:
2001 Change History
2002 Who When What
2003 abshriva 30/11/05 Bug:4768071-Modification in where clause of cursor cur_party_sites
2004 abshriva 9/11/05 Bug:4695680-Modification in where clause and inclusion of new table
2005 'igs_pe_hz_pty_sites' in cursor 'cur_party_sites'
2006 abshriva 26/10/05 Bug:4697644-Modification in where clause for cursor 'cur_party_sites'
2007
2008 ***************************************************************** */
2009
2010 CURSOR cur_data_exists(cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2011 cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
2012 SELECT tdata.rowid row_id, tdata.*
2013 FROM igs_fi_1098t_data tdata
2014 WHERE tdata.party_id = cp_n_person_id
2015 AND tdata.tax_year_name = cp_v_tax_year_name
2016 AND tdata.status_code <> 'DNT_RPT'
2017 ORDER BY tdata.stu_1098t_id desc
2018 FOR UPDATE NOWAIT;
2019
2020 l_rec_1098t_data cur_data_exists%ROWTYPE;
2021
2022 l_v_api_pers_id igs_pe_alt_pers_id.api_person_id%TYPE;
2023 l_v_api_pers_id_uf igs_pe_alt_pers_id.api_person_id_uf%TYPE;
2024
2025 l_v_rowid VARCHAR2(2000);
2026
2027 CURSOR cur_party_sites(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2028 cp_v_party_site_use_code igs_fi_1098t_setup.perm_party_site_use_code%TYPE) IS
2029 SELECT ps.location_id,
2030 ps.identifying_address_flag,
2031 ps.status
2032 FROM hz_party_site_uses hps,
2033 hz_party_sites ps,
2034 igs_pe_hz_pty_sites igsps
2035 WHERE ps.party_id = cp_n_person_id
2036 AND ps.party_site_id = hps.party_site_id
2037 AND hps.site_use_type = cp_v_party_site_use_code
2038 AND hps.status = 'A'
2039 AND igsps.party_site_id(+)=ps.party_site_id
2040 AND TRUNC(sysdate)
2041 BETWEEN TRUNC(NVL(igsps.start_date,sysdate))
2042 AND TRUNC(NVL(igsps.end_date,sysdate));
2043
2044 CURSOR cur_locations(cp_location_id hz_party_sites.location_id%TYPE) IS
2045 SELECT loc.address1,
2046 loc.address2,
2047 loc.address3,
2048 loc.address4,
2049 loc.country,
2050 loc.city,
2051 loc.state,
2052 loc.postal_code,
2053 loc.province,
2054 loc.county,
2055 loc.delivery_point_code
2056 FROM hz_locations loc
2057 WHERE location_id = cp_location_id;
2058
2059 l_rec_locations cur_locations%ROWTYPE;
2060
2061 l_n_perm_cntr PLS_INTEGER;
2062 l_n_temp_cntr PLS_INTEGER;
2063 l_n_location_id hz_locations.location_id%TYPE;
2064 l_v_error_code igs_fi_1098t_data.error_code%TYPE;
2065
2066 l_b_rec_exists BOOLEAN;
2067
2068 CURSOR cur_1098t_dtl(cp_n_stu_1098t_id igs_fi_1098t_data.stu_1098t_id%TYPE) IS
2069 SELECT rowid
2070 FROM igs_fi_1098t_dtls
2071 WHERE stu_1098t_id = cp_n_stu_1098t_id;
2072
2073 CURSOR cur_1098t_data_lat(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2074 cp_v_tax_year_name igs_fi_1098t_data.tax_year_name%TYPE) IS
2075 SELECT dat.*, dat.rowid row_id
2076 FROM igs_fi_1098t_data dat
2077 WHERE party_id = cp_n_person_id
2078 AND tax_year_name = cp_v_tax_year_name
2079 AND irs_filed_flag = 'Y'
2080 AND status_code <> 'DNT_RPT'
2081 ORDER BY stu_1098t_id DESC;
2082
2083
2084 l_rec_1098t_data_lat cur_1098t_data_lat%ROWTYPE;
2085 l_b_perm_addr BOOLEAN;
2086 l_b_temp_addr BOOLEAN;
2087 l_v_name_control igs_pe_alt_pers_id.api_person_id%TYPE;
2088 l_v_name_control_uf igs_pe_alt_pers_id.api_person_id_uf%TYPE;
2089 l_b_correction_record BOOLEAN;
2090 l_v_correction_flag igs_fi_1098t_data.correction_flag%TYPE;
2091 l_v_correction_code igs_fi_1098t_data.correction_type_code%TYPE;
2092 l_n_stu_1098t_id igs_fi_1098t_data.stu_1098t_id%TYPE;
2093 l_b_txn BOOLEAN;
2094
2095 l_v_val_name_ctrl VARCHAR2(1);
2096
2097 BEGIN
2098 l_v_val_name_ctrl := 'Y';
2099 log_to_fnd(p_v_module => 'insert_1098t_data',
2100 p_v_string => 'Inside creating 1098t transactions');
2101
2102 -- Get the alternate Person Id.
2103 get_alt_person_id(p_n_person_id,
2104 null,
2105 'SSN',
2106 l_v_api_pers_id,
2107 l_v_api_pers_id_uf);
2108
2109 l_v_correction_flag := 'N';
2110 l_v_error_code := null;
2111
2112 IF l_v_api_pers_id IS NULL THEN
2113 get_alt_person_id(p_n_person_id,
2114 g_rec_1098t_setup.person_id_type,
2115 'TAXID',
2116 l_v_api_pers_id,
2117 l_v_api_pers_id_uf);
2118 END IF;
2119
2120
2121 log_to_fnd(p_v_module => 'insert_1098t_data',
2122 p_v_string => 'API Person Id calculated as '||l_v_api_pers_id);
2123
2124 l_b_txn := FALSE;
2125
2126 -- If alternate person id is null, then mark the status as Failed with Invalid TIN as
2127 -- error code
2128 IF l_v_api_pers_id IS NULL THEN
2129 set_validation_status('FAILED');
2130 l_v_error_code := 'INVALID_TIN';
2131 ELSE
2132
2133 -- Else validate the API Person Id
2134 IF NOT igf_ap_li_isir_imp_proc.val_ssn(l_value => l_v_api_pers_id_uf) THEN
2135 log_to_fnd(p_v_module => 'insert_1098t_data',
2136 p_v_string => 'Call to igf procedure for validating SSN failed');
2137 set_validation_status('FAILED');
2138 l_v_error_code := 'INVALID_TIN';
2139 ELSE
2140 IF NOT validate_tin(l_v_api_pers_id_uf) THEN
2141 log_to_fnd(p_v_module => 'insert_1098t_data',
2142 p_v_string => 'Call to validate_tin failed');
2143 set_validation_status('FAILED');
2144 l_v_error_code := 'INVALID_TIN';
2145 END IF;
2146 END IF;
2147 END IF;
2148
2149 l_n_perm_cntr := 0;
2150 l_n_temp_cntr := 0;
2151
2152 l_b_perm_addr := FALSE;
2153
2154 -- Loop across the Party Sites for the person id and the permanent party site use code
2155 FOR l_rec_party_sites IN cur_party_sites(p_n_person_id,
2156 g_rec_1098t_setup.perm_party_site_use_code) LOOP
2157 l_n_perm_cntr := l_n_perm_cntr + 1;
2158
2159 -- if the identifying_address_flag flag is set and the status is Active, then the permanent address
2160 -- is found
2161 IF (l_rec_party_sites.identifying_address_flag = 'Y' AND l_rec_party_sites.status = 'A') THEN
2162 l_n_location_id := l_rec_party_sites.location_id;
2163 l_b_perm_addr := TRUE;
2164 EXIT;
2165 END IF;
2166 END LOOP;
2167
2168 -- If there were multiple party sites identified but none of them had the identifying_address_flag
2169 -- flag set with status as active then mark the record as FAILED with INVALID_ADD
2170 IF l_n_perm_cntr >= 1 AND NOT l_b_perm_addr THEN
2171 log_to_fnd(p_v_module => 'insert_1098t_data',
2172 p_v_string => 'Permanent Address could not be identified. Hence Invalid Address');
2173 set_validation_status('FAILED');
2174 l_v_error_code := 'INVALID_ADD';
2175 END IF;
2176
2177 -- If there were no records found, then use the temporary party site use code.
2178 IF l_n_perm_cntr = 0 THEN
2179 l_b_temp_addr := FALSE;
2180 FOR l_rec_party_sites IN cur_party_sites(p_n_person_id,
2181 g_rec_1098t_setup.temp_party_site_use_code) LOOP
2182 l_n_perm_cntr := l_n_perm_cntr + 1;
2183 IF (l_rec_party_sites.identifying_address_flag = 'Y' AND l_rec_party_sites.status = 'A') THEN
2184 l_n_location_id := l_rec_party_sites.location_id;
2185 l_b_temp_addr := TRUE;
2186 EXIT;
2187 END IF;
2188 END LOOP;
2189
2190 IF l_n_perm_cntr >= 1 AND NOT l_b_temp_addr THEN
2191 log_to_fnd(p_v_module => 'insert_1098t_data',
2192 p_v_string => 'Temporary Address could not be identified. Hence Invalid Address');
2193 set_validation_status('FAILED');
2194 l_v_error_code := 'INVALID_ADD';
2195 END IF;
2196 END IF;
2197
2198 -- If there are no addresses defined for the address usage, then
2199 IF l_n_perm_cntr = 0 THEN
2200 log_to_fnd(p_v_module => 'insert_1098t_data',
2201 p_v_string => 'Neither temporary nor permanent address could be identified. Hence Invalid Address');
2202 set_validation_status('FAILED');
2203 l_v_error_code := 'INVALID_ADD';
2204 END IF;
2205
2206 -- Fetch the location values
2207 IF l_n_location_id IS NOT NULL THEN
2208 OPEN cur_locations(l_n_location_id);
2209 FETCH cur_locations INTO l_rec_locations;
2210 CLOSE cur_locations;
2211 END IF;
2212
2213 IF l_rec_locations.country IS NULL OR
2214 l_rec_locations.city IS NULL OR
2215 l_rec_locations.state IS NULL OR
2216 l_rec_locations.postal_code IS NULL THEN
2217 set_validation_status('FAILED');
2218 l_v_error_code := 'INVALID_ADD';
2219 END IF;
2220
2221 -- Get the name control
2222 get_alt_person_id(p_n_person_id => p_n_person_id,
2223 p_v_person_id_type => null,
2224 p_v_s_person_id_type => 'NAME_CONTROL',
2225 p_v_api_pers_id => l_v_name_control,
2226 p_v_api_pers_id_uf => l_v_name_control_uf);
2227
2228 -- Validate Name Control.
2229 l_v_val_name_ctrl := validate_namecontrol(p_v_name_control => l_v_name_control);
2230
2231 log_to_fnd(p_v_module => 'insert_1098t_data',
2232 p_v_string => 'Name Control = '||l_v_name_control);
2233
2234 -- If the name control is invalid, then status is failed and error code is
2235 -- set to INVALID_NAME_CONTROL
2236
2237 IF l_v_val_name_ctrl = 'N' THEN
2238 set_validation_status('FAILED');
2239 l_v_error_code := 'INVALID_NAME_CONTROL';
2240 END IF;
2241
2242 l_b_rec_exists := FALSE;
2243
2244 -- Check if a record exists for the person and tax year
2245 OPEN cur_data_exists(p_v_tax_year_name,
2246 p_n_person_id);
2247 FETCH cur_data_exists INTO l_rec_1098t_data;
2248 IF cur_data_exists%FOUND THEN
2249 l_b_rec_exists := TRUE;
2250 END IF;
2251 CLOSE cur_data_exists;
2252
2253 log_line(g_v_label_name_control,
2254 l_v_name_control);
2255
2256 log_line(g_v_label_tin,
2257 l_v_api_pers_id);
2258
2259 fnd_file.new_line(fnd_file.log);
2260
2261 IF NOT l_b_rec_exists THEN
2262
2263 -- If there are no records existing, then create a new record in the 1098T Extract table
2264 -- and 1098T Details table
2265
2266 log_to_fnd(p_v_module => 'insert_1098t_data',
2267 p_v_string => 'There is no 1098T record existing. Hence creating a new record.');
2268
2269 l_v_rowid := null;
2270 l_n_stu_1098t_id := null;
2271 l_b_txn := TRUE;
2272 igs_fi_1098t_data_pkg.insert_row( x_rowid => l_v_rowid,
2273 x_stu_1098t_id => l_n_stu_1098t_id,
2274 x_tax_year_name => p_v_tax_year_name,
2275 x_party_id => p_n_person_id,
2276 x_extract_date => trunc(sysdate),
2277 x_party_name => p_v_full_name,
2278 x_taxid => l_v_api_pers_id,
2279 x_stu_name_control => l_v_name_control,
2280 x_country => l_rec_locations.country,
2281 x_address1 => l_rec_locations.address1,
2282 x_address2 => l_rec_locations.address2,
2283 x_refund_amt => 0,
2284 x_half_time_flag => p_v_box8,
2285 x_grad_flag => p_v_box9,
2286 x_special_data_entry => null,
2287 x_status_code => g_v_validation_status,
2288 x_error_code => l_v_error_code,
2289 x_file_name => null,
2290 x_irs_filed_flag => 'N',
2291 x_correction_flag => 'N',
2292 x_correction_type_code => null,
2293 x_stmnt_print_flag => 'N',
2294 x_override_flag => 'N',
2295 x_address3 => l_rec_locations.address3,
2296 x_address4 => l_rec_locations.address4,
2297 x_city => l_rec_locations.city,
2298 x_postal_code => l_rec_locations.postal_code,
2299 x_state => l_rec_locations.state,
2300 x_province => l_rec_locations.province,
2301 x_county => l_rec_locations.county,
2302 x_delivery_point_code => l_rec_locations.delivery_point_code,
2303 x_payment_amt => 0,
2304 x_billed_amt => p_n_box2,
2305 x_adj_amt => p_n_box3,
2306 x_fin_aid_amt => p_n_box4,
2307 x_fin_aid_adj_amt => p_n_box5,
2308 x_next_acad_flag => p_v_box6,
2309 x_batch_id => null,
2310 x_mode => 'R');
2311 IF l_t_1098t_drilldown.COUNT > 0 THEN
2312 log_to_fnd(p_v_module => 'insert_1098t_data',
2313 p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
2314 FOR l_n_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
2315 IF l_t_1098t_drilldown.EXISTS(l_n_rec_cntr) THEN
2316 l_v_rowid := null;
2317 igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
2318 x_stu_1098t_id => l_n_stu_1098t_id,
2319 x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
2320 x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
2321 x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
2322 END IF;
2323 END LOOP;
2324 END IF;
2325 ELSE
2326 log_to_fnd(p_v_module => 'insert_1098t_data',
2327 p_v_string => '1098T Data record exists for the person');
2328
2329 -- If the record exists, then check if it has already been filed to IRS
2330 IF l_rec_1098t_data.irs_filed_flag = 'Y' THEN
2331 log_to_fnd(p_v_module => 'insert_1098t_data',
2332 p_v_string => 'Original Record has been filed to IRS');
2333 l_b_correction_record := FALSE;
2334
2335 -- Check if any of the box amounts have changed. If yes, then set the flag for
2336 -- correction record identification
2337 IF (l_rec_1098t_data.billed_amt <> p_n_box2 OR
2338 l_rec_1098t_data.adj_amt <> p_n_box3 OR
2339 l_rec_1098t_data.fin_aid_amt <> p_n_box4 OR
2340 l_rec_1098t_data.fin_aid_adj_amt <> p_n_box5 OR
2341 l_rec_1098t_data.next_acad_flag <> p_v_box6 OR
2342 l_rec_1098t_data.half_time_flag <> p_v_box8 OR
2343 l_rec_1098t_data.grad_flag <> p_v_box9
2344 ) THEN
2345 log_to_fnd(p_v_module => 'insert_1098t_data',
2346 p_v_string => 'Correction record needs to be created as the box values are different');
2347
2348 l_b_correction_record := TRUE;
2349 l_v_correction_flag := 'Y';
2350 l_v_correction_code := '1';
2351 END IF;
2352
2353 -- If the file address correction parameter has been passed as Yes, then check if there is a change in
2354 -- the address details
2355 IF p_v_file_addr_correction = 'Y' AND NOT l_b_correction_record THEN
2356 IF (((l_rec_1098t_data.address1 = l_rec_locations.address1) OR
2357 (l_rec_1098t_data.address1 IS NULL AND l_rec_locations.address1 IS NULL)) AND
2358 ((l_rec_1098t_data.address2 = l_rec_locations.address2) OR
2359 (l_rec_1098t_data.address2 IS NULL AND l_rec_locations.address2 IS NULL)) AND
2360 ((l_rec_1098t_data.address3 = l_rec_locations.address3) OR
2361 (l_rec_1098t_data.address3 IS NULL AND l_rec_locations.address3 IS NULL)) AND
2362 ((l_rec_1098t_data.address4 = l_rec_locations.address4) OR
2363 (l_rec_1098t_data.address4 IS NULL AND l_rec_locations.address4 IS NULL)) AND
2364 ((l_rec_1098t_data.city = l_rec_locations.city) OR
2365 (l_rec_1098t_data.city IS NULL AND l_rec_locations.city IS NULL)) AND
2366 ((l_rec_1098t_data.state = l_rec_locations.state) OR
2367 (l_rec_1098t_data.state IS NULL AND l_rec_locations.state IS NULL)) AND
2368 ((l_rec_1098t_data.province = l_rec_locations.province) OR
2369 (l_rec_1098t_data.province IS NULL AND l_rec_locations.province IS NULL)) AND
2370 ((l_rec_1098t_data.county = l_rec_locations.county) OR
2371 (l_rec_1098t_data.county IS NULL AND l_rec_locations.county IS NULL)) AND
2372 ((l_rec_1098t_data.country = l_rec_locations.country) OR
2373 (l_rec_1098t_data.country IS NULL AND l_rec_locations.country IS NULL)) AND
2374 ((l_rec_1098t_data.postal_code = l_rec_locations.postal_code) OR
2375 (l_rec_1098t_data.postal_code IS NULL AND l_rec_locations.postal_code IS NULL)) AND
2376 ((l_rec_1098t_data.delivery_point_code = l_rec_locations.delivery_point_code) OR
2377 (l_rec_1098t_data.delivery_point_code IS NULL AND l_rec_locations.delivery_point_code IS NULL))) THEN
2378 null;
2379 ELSE
2380 log_to_fnd(p_v_module => 'insert_1098t_data',
2381 p_v_string => 'Correction record needs to be created as the address has changed');
2382 l_b_correction_record := TRUE;
2383 l_v_correction_flag := 'Y';
2384 l_v_correction_code := '1';
2385 END IF;
2386 END IF;
2387
2388 -- Check if there is a change in the Tax ID. If yes, then set the flag for correction record
2389 IF ((l_rec_1098t_data.taxid <> l_v_api_pers_id)) THEN
2390 log_to_fnd(p_v_module => 'insert_1098t_data',
2391 p_v_string => 'Correction record needs to be created as the Alternate Person Id has changed');
2392 l_b_correction_record := TRUE;
2393 l_v_correction_flag := 'Y';
2394 l_v_correction_code := '2';
2395 END IF;
2396
2397 -- Check if there is a change in the Full Name. If yes, then set the flag for correction record
2398 IF ((l_rec_1098t_data.party_name <> p_v_full_name)) THEN
2399 log_to_fnd(p_v_module => 'insert_1098t_data',
2400 p_v_string => 'Correction record needs to be created as full name has changed');
2401 l_b_correction_record := TRUE;
2402 l_v_correction_flag := 'Y';
2403 l_v_correction_code := '2';
2404 END IF;
2405
2406 IF NOT l_b_correction_record THEN
2407 fnd_message.set_name('IGS',
2408 'IGS_FI_1098T_REC_NOT_CREATED');
2409 fnd_file.put_line(fnd_file.log,
2410 fnd_message.get);
2411 log_to_fnd(p_v_module => 'insert_1098t_data',
2412 p_v_string => 'Correction Record does not need to be created. Hence exiting from this procedure');
2413 RETURN;
2414 END IF;
2415
2416 l_v_rowid := null;
2417 l_n_stu_1098t_id := null;
2418 l_b_txn := TRUE;
2419 log_to_fnd(p_v_module => 'insert_1098t_data',
2420 p_v_string => 'Creating a new correction record');
2421
2422 -- Creating a new record as the original has already been filed to IRS
2423 igs_fi_1098t_data_pkg.insert_row( x_rowid => l_v_rowid,
2424 x_stu_1098t_id => l_n_stu_1098t_id,
2425 x_tax_year_name => p_v_tax_year_name,
2426 x_party_id => p_n_person_id,
2427 x_extract_date => trunc(sysdate),
2428 x_party_name => p_v_full_name,
2429 x_taxid => l_v_api_pers_id,
2430 x_stu_name_control => l_v_name_control,
2431 x_country => l_rec_locations.country,
2432 x_address1 => l_rec_locations.address1,
2433 x_address2 => l_rec_locations.address2,
2434 x_refund_amt => l_rec_1098t_data.refund_amt,
2435 x_half_time_flag => p_v_box8,
2436 x_grad_flag => p_v_box9,
2437 x_special_data_entry => l_rec_1098t_data.special_data_entry,
2438 x_status_code => g_v_validation_status,
2439 x_error_code => l_v_error_code,
2440 x_file_name => null,
2441 x_irs_filed_flag => 'N',
2442 x_correction_flag => l_v_correction_flag,
2443 x_correction_type_code => l_v_correction_code,
2444 x_stmnt_print_flag => 'N',
2445 x_override_flag => 'N',
2446 x_address3 => l_rec_locations.address3,
2447 x_address4 => l_rec_locations.address4,
2448 x_city => l_rec_locations.city,
2449 x_postal_code => l_rec_locations.postal_code,
2450 x_state => l_rec_locations.state,
2451 x_province => l_rec_locations.province,
2452 x_county => l_rec_locations.county,
2453 x_delivery_point_code => l_rec_locations.delivery_point_code,
2454 x_payment_amt => l_rec_1098t_data.payment_amt,
2455 x_billed_amt => p_n_box2,
2456 x_adj_amt => p_n_box3,
2457 x_fin_aid_amt => p_n_box4,
2458 x_fin_aid_adj_amt => p_n_box5,
2459 x_next_acad_flag => p_v_box6,
2460 x_batch_id => null,
2461 x_mode => 'R');
2462
2463 IF l_t_1098t_drilldown.COUNT > 0 THEN
2464 log_to_fnd(p_v_module => 'insert_1098t_data',
2465 p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
2466 FOR l_n_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
2467 IF l_t_1098t_drilldown.EXISTS(l_n_rec_cntr) THEN
2468 l_v_rowid := null;
2469 igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
2470 x_stu_1098t_id => l_n_stu_1098t_id,
2471 x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
2472 x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
2473 x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
2474 END IF;
2475 END LOOP;
2476 END IF;
2477 ELSE
2478
2479 -- If the record has not been filed to IRS,
2480 log_to_fnd(p_v_module => 'insert_1098t_data',
2481 p_v_string => 'Original Record has not been filed to IRS');
2482
2483 -- Check if the correction flag has been set for the 1098t record
2484 IF l_rec_1098t_data.correction_flag = 'N' THEN
2485 log_to_fnd(p_v_module => 'insert_1098t_data',
2486 p_v_string => 'Correction flag has not been set. Hence delete all detail records.');
2487
2488 -- If it is No, then delete all the records in the 1098T details table
2489 FOR l_rec_dtl IN cur_1098t_dtl(l_rec_1098t_data.stu_1098t_id) LOOP
2490 igs_fi_1098t_dtls_pkg.delete_row(x_rowid => l_rec_dtl.rowid);
2491 END LOOP;
2492
2493 l_b_txn := TRUE;
2494
2495 log_to_fnd(p_v_module => 'insert_1098t_data',
2496 p_v_string => 'Update the 1098T Transaction record');
2497
2498 -- Update the 1098T record and create new records in the details table
2499 igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
2500 x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
2501 x_tax_year_name => p_v_tax_year_name,
2502 x_party_id => l_rec_1098t_data.party_id,
2503 x_extract_date => trunc(sysdate),
2504 x_party_name => p_v_full_name,
2505 x_taxid => l_v_api_pers_id,
2506 x_stu_name_control => l_v_name_control,
2507 x_country => l_rec_locations.country,
2508 x_address1 => l_rec_locations.address1,
2509 x_address2 => l_rec_locations.address2,
2510 x_refund_amt => l_rec_1098t_data.refund_amt,
2511 x_half_time_flag => p_v_box8,
2512 x_grad_flag => p_v_box9,
2513 x_special_data_entry => l_rec_1098t_data.special_data_entry,
2514 x_status_code => g_v_validation_status,
2515 x_error_code => l_v_error_code,
2516 x_file_name => null,
2517 x_irs_filed_flag => 'N',
2518 x_correction_flag => l_rec_1098t_data.correction_flag,
2519 x_correction_type_code => l_rec_1098t_data.correction_type_code,
2520 x_stmnt_print_flag => 'N',
2521 x_override_flag => 'N',
2522 x_address3 => l_rec_locations.address3,
2523 x_address4 => l_rec_locations.address4,
2524 x_city => l_rec_locations.city,
2525 x_postal_code => l_rec_locations.postal_code,
2526 x_state => l_rec_locations.state,
2527 x_province => l_rec_locations.province,
2528 x_county => l_rec_locations.county,
2529 x_delivery_point_code => l_rec_locations.delivery_point_code,
2530 x_payment_amt => l_rec_1098t_data.payment_amt,
2531 x_billed_amt => p_n_box2,
2532 x_adj_amt => p_n_box3,
2533 x_fin_aid_amt => p_n_box4,
2534 x_fin_aid_adj_amt => p_n_box5,
2535 x_next_acad_flag => p_v_box6,
2536 x_batch_id => null,
2537 x_mode => 'R');
2538 IF l_t_1098t_drilldown.COUNT > 0 THEN
2539 log_to_fnd(p_v_module => 'insert_1098t_data',
2540 p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
2541 FOR l_n_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
2542 IF l_t_1098t_drilldown.EXISTS(l_n_rec_cntr) THEN
2543 l_v_rowid := null;
2544 igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
2545 x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
2546 x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
2547 x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
2548 x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
2549 END IF;
2550 END LOOP;
2551 END IF;
2552 ELSE
2553
2554
2555 log_to_fnd(p_v_module => 'insert_1098t_data',
2556 p_v_string => 'Correction Flag has been set.');
2557 OPEN cur_1098t_data_lat(p_n_person_id,
2558 p_v_tax_year_name);
2559 FETCH cur_1098t_data_lat INTO l_rec_1098t_data_lat;
2560 CLOSE cur_1098t_data_lat;
2561
2562 l_b_correction_record := FALSE;
2563
2564 -- Check if the box amounts have changed.
2565 IF (l_rec_1098t_data_lat.billed_amt <> p_n_box2 OR
2566 l_rec_1098t_data_lat.adj_amt <> p_n_box3 OR
2567 l_rec_1098t_data_lat.fin_aid_amt <> p_n_box4 OR
2568 l_rec_1098t_data_lat.fin_aid_adj_amt <> p_n_box5 OR
2569 l_rec_1098t_data_lat.next_acad_flag <> p_v_box6 OR
2570 l_rec_1098t_data_lat.half_time_flag <> p_v_box8 OR
2571 l_rec_1098t_data_lat.grad_flag <> p_v_box9
2572 ) THEN
2573 log_to_fnd(p_v_module => 'insert_1098t_data',
2574 p_v_string => 'Correction record needs to be created as the box values have changed');
2575 l_v_correction_flag := 'Y';
2576 l_v_correction_code := '1';
2577 l_b_correction_record := TRUE;
2578 END IF;
2579
2580 -- If the file address correction has been passed as Y, check for address change
2581 IF (p_v_file_addr_correction = 'Y' AND NOT l_b_correction_record) THEN
2582 IF (((l_rec_1098t_data_lat.address1 = l_rec_locations.address1) OR
2583 (l_rec_1098t_data_lat.address1 IS NULL AND l_rec_locations.address1 IS NULL)) AND
2584 ((l_rec_1098t_data_lat.address2 = l_rec_locations.address2) OR
2585 (l_rec_1098t_data_lat.address2 IS NULL AND l_rec_locations.address2 IS NULL)) AND
2586 ((l_rec_1098t_data_lat.address3 = l_rec_locations.address3) OR
2587 (l_rec_1098t_data_lat.address3 IS NULL AND l_rec_locations.address3 IS NULL)) AND
2588 ((l_rec_1098t_data_lat.address4 = l_rec_locations.address4) OR
2589 (l_rec_1098t_data_lat.address4 IS NULL AND l_rec_locations.address4 IS NULL)) AND
2590 ((l_rec_1098t_data_lat.city = l_rec_locations.city) OR
2591 (l_rec_1098t_data_lat.city IS NULL AND l_rec_locations.city IS NULL)) AND
2592 ((l_rec_1098t_data_lat.state = l_rec_locations.state) OR
2593 (l_rec_1098t_data_lat.state IS NULL AND l_rec_locations.state IS NULL)) AND
2594 ((l_rec_1098t_data_lat.province = l_rec_locations.province) OR
2595 (l_rec_1098t_data_lat.province IS NULL AND l_rec_locations.province IS NULL)) AND
2596 ((l_rec_1098t_data_lat.county = l_rec_locations.county) OR
2597 (l_rec_1098t_data_lat.county IS NULL AND l_rec_locations.county IS NULL)) AND
2598 ((l_rec_1098t_data_lat.country = l_rec_locations.country) OR
2599 (l_rec_1098t_data_lat.country IS NULL AND l_rec_locations.country IS NULL)) AND
2600 ((l_rec_1098t_data_lat.postal_code = l_rec_locations.postal_code) OR
2601 (l_rec_1098t_data_lat.postal_code IS NULL AND l_rec_locations.postal_code IS NULL)) AND
2602 ((l_rec_1098t_data_lat.delivery_point_code = l_rec_locations.delivery_point_code) OR
2603 (l_rec_1098t_data_lat.delivery_point_code IS NULL AND l_rec_locations.delivery_point_code IS NULL))) THEN
2604 null;
2605 ELSE
2606 log_to_fnd(p_v_module => 'insert_1098t_data',
2607 p_v_string => 'Correction record needs to be created as the address has changed');
2608 l_b_correction_record := TRUE;
2609 l_v_correction_flag := 'Y';
2610 l_v_correction_code := '1';
2611 END IF;
2612 END IF;
2613
2614 -- Check for change in Alternate Person Id
2615 IF ((l_rec_1098t_data_lat.taxid <> l_v_api_pers_id)) THEN
2616 log_to_fnd(p_v_module => 'insert_1098t_data',
2617 p_v_string => 'Correction record needs to be created as Alternate Person Id has changed');
2618 l_b_correction_record := TRUE;
2619 l_v_correction_flag := 'Y';
2620 l_v_correction_code := '2';
2621 END IF;
2622
2623 -- Check for change in the Party Name
2624 IF l_rec_1098t_data_lat.party_name <> p_v_full_name THEN
2625 log_to_fnd(p_v_module => 'insert_1098t_data',
2626 p_v_string => 'Correction record needs to be created as the address has changed');
2627 l_b_correction_record := TRUE;
2628 l_v_correction_flag := 'Y';
2629 l_v_correction_code := '2';
2630 END IF;
2631
2632 IF NOT l_b_correction_record THEN
2633 log_to_fnd(p_v_module => 'insert_1098t_data',
2634 p_v_string => 'Correction record needs not be created. Hence updating the record with DNT_RPT status');
2635 l_b_txn := TRUE;
2636
2637 g_v_validation_status := 'DNT_RPT';
2638
2639 -- If there is no change, then update the record with status as DNT_RPT
2640 igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
2641 x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
2642 x_tax_year_name => l_rec_1098t_data.tax_year_name,
2643 x_party_id => l_rec_1098t_data.party_id,
2644 x_extract_date => l_rec_1098t_data.extract_date,
2645 x_party_name => l_rec_1098t_data.party_name,
2646 x_taxid => l_rec_1098t_data.taxid,
2647 x_stu_name_control => l_rec_1098t_data.stu_name_control,
2648 x_country => l_rec_1098t_data.country,
2649 x_address1 => l_rec_1098t_data.address1,
2650 x_address2 => l_rec_1098t_data.address2,
2651 x_refund_amt => l_rec_1098t_data.refund_amt,
2652 x_half_time_flag => l_rec_1098t_data.half_time_flag,
2653 x_grad_flag => l_rec_1098t_data.grad_flag,
2654 x_special_data_entry => l_rec_1098t_data.special_data_entry,
2655 x_status_code => 'DNT_RPT',
2656 x_error_code => l_rec_1098t_data.error_code,
2657 x_file_name => l_rec_1098t_data.file_name,
2658 x_irs_filed_flag => l_rec_1098t_data.irs_filed_flag,
2659 x_correction_flag => l_rec_1098t_data.correction_flag,
2660 x_correction_type_code => l_rec_1098t_data.correction_type_code,
2661 x_stmnt_print_flag => l_rec_1098t_data.stmnt_print_flag,
2662 x_override_flag => l_rec_1098t_data.override_flag,
2663 x_address3 => l_rec_1098t_data.address3,
2664 x_address4 => l_rec_1098t_data.address4,
2665 x_city => l_rec_1098t_data.city,
2666 x_postal_code => l_rec_1098t_data.postal_code,
2667 x_state => l_rec_1098t_data.state,
2668 x_province => l_rec_1098t_data.province,
2669 x_county => l_rec_1098t_data.county,
2670 x_delivery_point_code => l_rec_1098t_data.delivery_point_code,
2671 x_payment_amt => l_rec_1098t_data.payment_amt,
2672 x_billed_amt => l_rec_1098t_data.billed_amt,
2673 x_adj_amt => l_rec_1098t_data.adj_amt,
2674 x_fin_aid_amt => l_rec_1098t_data.fin_aid_amt,
2675 x_fin_aid_adj_amt => l_rec_1098t_data.fin_aid_adj_amt,
2676 x_next_acad_flag => l_rec_1098t_data.next_acad_flag,
2677 x_batch_id => l_rec_1098t_data.batch_id,
2678 x_mode => 'R');
2679 ELSE
2680
2681 log_to_fnd(p_v_module => 'insert_1098t_data',
2682 p_v_string => 'Correction record needs to be created. Hence delete all detail records');
2683
2684 -- If there is a change, then delete records from the 1098T detail table
2685 FOR l_rec_dtl IN cur_1098t_dtl(l_rec_1098t_data.stu_1098t_id) LOOP
2686 igs_fi_1098t_dtls_pkg.delete_row(x_rowid => l_rec_dtl.rowid);
2687 END LOOP;
2688
2689 l_b_txn := TRUE;
2690 log_to_fnd(p_v_module => 'insert_1098t_data',
2691 p_v_string => 'Updating the 1098T record');
2692
2693 -- Update the 1098T table and create new detail records
2694 igs_fi_1098t_data_pkg.update_row(x_rowid => l_rec_1098t_data.row_id,
2695 x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
2696 x_tax_year_name => p_v_tax_year_name,
2697 x_party_id => l_rec_1098t_data.party_id,
2698 x_extract_date => trunc(sysdate),
2699 x_party_name => p_v_full_name,
2700 x_taxid => l_v_api_pers_id,
2701 x_stu_name_control => l_v_name_control,
2702 x_country => l_rec_locations.country,
2703 x_address1 => l_rec_locations.address1,
2704 x_address2 => l_rec_locations.address2,
2705 x_refund_amt => l_rec_1098t_data.refund_amt,
2706 x_half_time_flag => p_v_box8,
2707 x_grad_flag => p_v_box9,
2708 x_special_data_entry => l_rec_1098t_data.special_data_entry,
2709 x_status_code => g_v_validation_status,
2710 x_error_code => l_v_error_code,
2711 x_file_name => null,
2712 x_irs_filed_flag => 'N',
2713 x_correction_flag => l_v_correction_flag,
2714 x_correction_type_code => l_v_correction_code,
2715 x_stmnt_print_flag => 'N',
2716 x_override_flag => 'N',
2717 x_address3 => l_rec_locations.address3,
2718 x_address4 => l_rec_locations.address4,
2719 x_city => l_rec_locations.city,
2720 x_postal_code => l_rec_locations.postal_code,
2721 x_state => l_rec_locations.state,
2722 x_province => l_rec_locations.province,
2723 x_county => l_rec_locations.county,
2724 x_delivery_point_code => l_rec_locations.delivery_point_code,
2725 x_payment_amt => l_rec_1098t_data.payment_amt,
2726 x_billed_amt => p_n_box2,
2727 x_adj_amt => p_n_box3,
2728 x_fin_aid_amt => p_n_box4,
2729 x_fin_aid_adj_amt => p_n_box5,
2730 x_next_acad_flag => p_v_box6,
2731 x_batch_id => null,
2732 x_mode => 'R');
2733 IF l_t_1098t_drilldown.COUNT > 0 THEN
2734 log_to_fnd(p_v_module => 'insert_1098t_data',
2735 p_v_string => 'Creating '||l_t_1098t_drilldown.COUNT||' detail records for the 1098T record');
2736 FOR l_n_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
2737 IF l_t_1098t_drilldown.EXISTS(l_n_rec_cntr) THEN
2738 l_v_rowid := null;
2739 igs_fi_1098t_dtls_pkg.insert_row(x_rowid => l_v_rowid,
2740 x_stu_1098t_id => l_rec_1098t_data.stu_1098t_id,
2741 x_transaction_id => l_t_1098t_drilldown(l_n_rec_cntr).transaction_id,
2742 x_transaction_code => l_t_1098t_drilldown(l_n_rec_cntr).transaction_code,
2743 x_box_num => l_t_1098t_drilldown(l_n_rec_cntr).box_num);
2744 END IF;
2745 END LOOP;
2746 END IF;
2747 END IF;
2748 END IF; -- Correction Flag
2749 END IF; -- Record has not been filed with IRS
2750 END IF; -- Record Exists
2751
2752 -- Log the details if there is a 1098T record created or updated
2753 IF l_b_txn THEN
2754 log_line(g_v_label_val_status,
2755 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_1098T_STATUS_CODE',
2756 g_v_validation_status));
2757 log_line(g_v_label_err_desc,
2758 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_1098T_ERR_CODE',
2759 l_v_error_code));
2760 log_line(g_v_label_correct_ret,
2761 igs_fi_gen_gl.get_lkp_meaning('YES_NO',
2762 NVL(l_v_correction_flag,l_rec_1098t_data.correction_flag)));
2763 fnd_file.new_line(fnd_file.log);
2764 log_line(g_v_label_boxval,null);
2765 log_line(g_v_label_box2,
2766 p_n_box2);
2767 log_line(g_v_label_box3,
2768 p_n_box3);
2769 log_line(g_v_label_box4,
2770 p_n_box4);
2771 log_line(g_v_label_box5,
2772 p_n_box5);
2773 log_line(g_v_label_box6,
2774 p_v_box6);
2775 log_line(g_v_label_box8,
2776 p_v_box8);
2777 log_line(g_v_label_box9,
2778 p_v_box9);
2779
2780 -- If the 1098T transaction has invalid address error code then
2781 -- log the message to the log file
2782 IF l_v_error_code = 'INVALID_ADD' THEN
2783 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_FI_INV_NOT_FILE'));
2784 END IF;
2785
2786 -- If the name control is invalid, then log the message
2787 IF l_v_val_name_ctrl = 'N' THEN
2788 fnd_file.put_line(fnd_file.log, fnd_message.get_string('IGS','IGS_FI_INVALID_NAME_CTRL'));
2789 END IF;
2790 END IF;
2791 END insert_1098t_data;
2792
2793 PROCEDURE extract_data_main(p_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2794 p_n_person_id igs_pe_person_base_v.person_id%TYPE,
2795 p_v_override_excl VARCHAR2,
2796 p_v_file_addr_correction VARCHAR2) AS
2797 /******************************************************************
2798 Created By : Amit Gairola
2799 Date Created By : 06-May-2005
2800 Purpose : Main procedure for a person id
2801
2802 Known limitations,enhancements,remarks:
2803 Change History
2804 Who When What
2805 abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
2806 ***************************************************************** */
2807
2808 CURSOR cur_pers_dtl(cp_n_person_id igs_pe_person_base_v.person_id%TYPE) IS
2809 SELECT person_number,
2810 first_name,
2811 last_name
2812 FROM igs_pe_person_base_v
2813 WHERE person_id = cp_n_person_id;
2814
2815 l_rec_pers_dtl cur_pers_dtl%ROWTYPE;
2816
2817 CURSOR cur_chk_rec_exists(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2818 cp_v_tax_year_name igs_fi_1098t_setup.tax_year_name%TYPE,
2819 cp_v_status igs_fi_1098t_data.status_code%TYPE) IS
2820 SELECT dat.override_flag, dat.irs_filed_flag
2821 FROM igs_fi_1098t_data dat
2822 WHERE dat.party_id = cp_n_person_id
2823 AND dat.tax_year_name = cp_v_tax_year_name
2824 AND dat.status_code <> cp_v_status
2825 ORDER BY stu_1098t_id DESC;
2826
2827 l_rec_1098t_data cur_chk_rec_exists%ROWTYPE;
2828
2829 CURSOR cur_non_res_alien(cp_n_person_id igs_pe_person_base_v.person_id%TYPE,
2830 cp_d_start_date DATE,
2831 cp_d_end_date DATE) IS
2832 SELECT 'x'
2833 FROM igs_pe_typ_instances_all ptyp,
2834 igs_pe_person_types ptm
2835 WHERE ptyp.person_id = cp_n_person_id
2836 AND ptyp.person_type_code = ptm.person_type_code
2837 AND ptm.system_type IN ('EXCHG_VISITOR','NONIMG_STUDENT')
2838 AND TRUNC(ptyp.start_date) <= TRUNC(cp_d_start_date)
2839 AND ((TRUNC(ptyp.end_date) >= TRUNC(cp_d_end_date)) OR (ptyp.end_date IS NULL));
2840
2841 l_b_new_run BOOLEAN;
2842 l_n_box2 igs_fi_1098t_data.billed_amt%TYPE;
2843 l_n_box3 igs_fi_1098t_data.adj_amt%TYPE;
2844 l_n_box4 igs_fi_1098t_data.fin_aid_amt%TYPE;
2845 l_n_box5 igs_fi_1098t_data.fin_aid_adj_amt%TYPE;
2846 l_v_box6 igs_fi_1098t_data.next_acad_flag%TYPE;
2847 l_v_box8 igs_fi_1098t_data.half_time_flag%TYPE;
2848 l_v_box9 igs_fi_1098t_data.grad_flag%TYPE;
2849 l_var VARCHAR2(1);
2850 l_n_orig_credit NUMBER;
2851 l_n_adj_credit NUMBER;
2852 l_n_orig_charge NUMBER;
2853 l_n_adj_charge NUMBER;
2854 l_v_next_acad_flag igs_fi_1098t_data.next_acad_flag%TYPE;
2855
2856 l_n_orig_billed_amt NUMBER;
2857 l_n_adj_billed_amt NUMBER;
2858 l_n_aid_orig_credit NUMBER;
2859 l_n_aid_adj_credit NUMBER;
2860 l_n_aid_orig_charge NUMBER;
2861 l_n_aid_adj_charge NUMBER;
2862 l_n_orig_spgrant_amt NUMBER;
2863 l_n_adj_spgrant_amt NUMBER;
2864 l_v_full_name igs_fi_1098t_data.party_name%TYPE;
2865
2866 BEGIN
2867 l_b_new_run := FALSE;
2868 set_validation_status('PASSED');
2869 l_n_cntr := 0;
2870 l_t_1098t_drilldown.DELETE;
2871 l_n_box2 := 0;
2872 l_n_box3 := 0;
2873 l_n_box4 := 0;
2874 l_n_box5 := 0;
2875 l_v_box6 := null;
2876 l_v_box8 := null;
2877 l_v_box9 := null;
2878 g_b_non_zero_credits_flag := FALSE;
2879 g_b_chg_crd_found := FALSE;
2880
2881 -- Get the person details
2882 OPEN cur_pers_dtl(p_n_person_id);
2883 FETCH cur_pers_dtl INTO l_rec_pers_dtl;
2884 CLOSE cur_pers_dtl;
2885
2886 l_v_full_name := l_rec_pers_dtl.last_name||' '||l_rec_pers_dtl.first_name;
2887
2888 fnd_file.new_line(fnd_file.log);
2889
2890 fnd_file.put_line(fnd_file.log,
2891 g_v_line_sep);
2892 log_line(g_v_label_person,
2893 l_rec_pers_dtl.person_number);
2894 log_line(g_v_label_stdnt_name,
2895 l_v_full_name);
2896
2897 -- Check if 1098T record exists with the values overridden
2898 OPEN cur_chk_rec_exists(p_n_person_id,
2899 p_v_tax_year_name,
2900 'DNT_RPT');
2901 FETCH cur_chk_rec_exists INTO l_rec_1098t_data;
2902 IF cur_chk_rec_exists%NOTFOUND THEN
2903 l_b_new_run := TRUE;
2904 END IF;
2905 CLOSE cur_chk_rec_exists;
2906
2907 IF NOT l_b_new_run THEN
2908 IF l_rec_1098t_data.override_flag = 'Y' THEN
2909 fnd_message.set_module(g_v_package_name||'extract_data_main');
2910 fnd_message.set_name('IGS',
2911 'IGS_FI_1098T_STU_OVERRIDDEN');
2912 fnd_message.set_token('TAX_YEAR_NAME',
2913 p_v_tax_year_name);
2914 fnd_file.put_line(fnd_file.log,
2915 fnd_message.get);
2916 RETURN;
2917 END IF;
2918 END IF;
2919
2920 -- IF the override Exclusions parameter is passed as No
2921 IF p_v_override_excl = 'N' THEN
2922
2923 -- Check if the Exclude Non Resident Alien flag is checked at
2924 -- 1098T setup
2925 IF g_rec_1098t_setup.excl_non_res_alien_flag = 'Y' THEN
2926
2927 -- Check if the person is Exchange visitor or Non Immigrant Student
2928 OPEN cur_non_res_alien(p_n_person_id,
2929 g_rec_1098t_setup.start_date,
2930 g_rec_1098t_setup.end_date);
2931 FETCH cur_non_res_alien INTO l_var;
2932 CLOSE cur_non_res_alien;
2933
2934 IF l_var IS NOT NULL THEN
2935 fnd_message.set_module(g_v_package_name||'extract_data_main');
2936 fnd_message.set_name('IGS',
2937 'IGS_FI_1098T_STU_EXCL_ALIEN');
2938 fnd_file.put_line(fnd_file.log,
2939 fnd_message.get);
2940 RETURN;
2941 END IF;
2942 END IF;
2943 END IF;
2944
2945 -- Update all charges and credits for the person and tax year
2946 -- set the tax year to null
2947 update_charges(p_n_person_id => p_n_person_id,
2948 p_v_tax_year => g_rec_1098t_setup.tax_year_code);
2949
2950 update_credits(p_n_person_id => p_n_person_id,
2951 p_v_tax_year => g_rec_1098t_setup.tax_year_code);
2952
2953
2954 -- calculate box 2 and 3 amounts from the credits
2955 box236_credits(p_v_tax_year_name => p_v_tax_year_name,
2956 p_n_person_id => p_n_person_id,
2957 p_v_override_excl => p_v_override_excl,
2958 p_n_orig_credit => l_n_orig_credit,
2959 p_n_adj_credit => l_n_adj_credit);
2960
2961 -- calculate box 2 and 3 amounts from the charges
2962 box236_charges(p_v_tax_year_name => p_v_tax_year_name,
2963 p_n_person_id => p_n_person_id,
2964 p_v_override_excl => p_v_override_excl,
2965 p_n_orig_charge => l_n_orig_charge,
2966 p_n_adj_charge => l_n_adj_charge,
2967 p_v_next_acad_flag => l_v_next_acad_flag);
2968
2969 -- If the override exclusions parameter is set to No
2970 IF p_v_override_excl = 'N' THEN
2971
2972 -- Check if the Non Zero credits global variable has been set to FALSE
2973 -- If yes, then log message and return
2974 IF g_b_chg_crd_found THEN
2975 IF NOT g_b_non_zero_credits_flag THEN
2976 fnd_message.set_module(g_v_package_name||'extract_data_main');
2977 fnd_message.set_name('IGS',
2978 'IGS_FI_1098T_STU_EXCL_COURSE');
2979 fnd_file.put_line(fnd_file.log,
2980 fnd_message.get);
2981 RETURN;
2982 END IF;
2983 END IF;
2984 END IF;
2985
2986 -- Calculate Original and Adjustment Billed Amount
2987 l_n_orig_billed_amt := NVL(l_n_orig_charge,0) -
2988 NVL(l_n_orig_credit,0);
2989
2990 l_n_adj_billed_amt := NVL(l_n_adj_charge,0) -
2991 NVL(l_n_adj_credit,0);
2992
2993 -- If Adjustment Billed Amount > 0 then box 3 is 0
2994 -- box 2 = orig billed + adj billed
2995 IF l_n_adj_billed_amt >= 0 THEN
2996 l_n_box2 := l_n_orig_billed_amt +
2997 l_n_adj_billed_amt;
2998 l_n_box3 := 0;
2999
3000 -- update all records in the PLSQL table where box number is 3 to 2
3001 IF l_t_1098t_drilldown.COUNT > 0 THEN
3002 FOR l_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
3003 IF l_t_1098t_drilldown.EXISTS(l_rec_cntr) THEN
3004 IF l_t_1098t_drilldown(l_rec_cntr).box_num = 3 THEN
3005 l_t_1098t_drilldown(l_rec_cntr).box_num := 2;
3006 END IF;
3007 END IF;
3008 END LOOP;
3009 END IF;
3010 ELSE
3011 -- Otherwise box2 = original billed amt
3012 -- box3 = absolute value of Adjustment Billed Amount
3013 l_n_box2 := l_n_orig_billed_amt;
3014 l_n_box3 := ABS(l_n_adj_billed_amt);
3015 END IF;
3016
3017 -- Calculate Box 4 and 5 amounts from credits and charges
3018 box45_credits(p_v_tax_year_name => p_v_tax_year_name,
3019 p_n_person_id => p_n_person_id,
3020 p_n_orig_credit => l_n_aid_orig_credit,
3021 p_n_adj_credit => l_n_aid_adj_credit);
3022
3023 box45_charges(p_v_tax_year_name => p_v_tax_year_name,
3024 p_n_person_id => p_n_person_id,
3025 p_n_orig_charge => l_n_aid_orig_charge,
3026 p_n_adj_charge => l_n_aid_adj_charge);
3027
3028 -- Calculate Original and Adjustment Sponsorship grant amount
3029 l_n_orig_spgrant_amt := NVL(l_n_aid_orig_credit,0) -
3030 NVL(l_n_aid_orig_charge,0);
3031
3032 l_n_adj_spgrant_amt := NVL(l_n_aid_adj_credit,0) -
3033 NVL(l_n_aid_adj_charge,0);
3034
3035 -- If the adjustment amount > 0, then box 5 is 0
3036 IF l_n_adj_spgrant_amt >=0 THEN
3037 l_n_box4 := l_n_orig_spgrant_amt +
3038 l_n_adj_spgrant_amt;
3039 l_n_box5 := 0;
3040
3041 IF l_t_1098t_drilldown.COUNT > 0 THEN
3042 FOR l_rec_cntr IN l_t_1098t_drilldown.FIRST..l_t_1098t_drilldown.LAST LOOP
3043 IF l_t_1098t_drilldown.EXISTS(l_rec_cntr) THEN
3044 IF l_t_1098t_drilldown(l_rec_cntr).box_num = 5 THEN
3045 l_t_1098t_drilldown(l_rec_cntr).box_num := 4;
3046 END IF;
3047 END IF;
3048 END LOOP;
3049 END IF;
3050 ELSE
3051 l_n_box4 := l_n_orig_spgrant_amt;
3052 l_n_box5 := ABS(l_n_adj_spgrant_amt);
3053 END IF;
3054
3055 -- If all box2,3,4 and 5 are 0, then no record needs to be created
3056 IF l_n_box4 = 0 AND l_n_box5 = 0 AND l_n_box2 = 0 AND l_n_box3 = 0 THEN
3057 fnd_message.set_module(g_v_package_name||'extract_data_main');
3058 fnd_message.set_name('IGS',
3059 'IGS_FI_1098T_BOX_ZERO');
3060 fnd_file.put_line(fnd_file.log,
3061 fnd_message.get);
3062 RETURN;
3063 END IF;
3064
3065 -- If the override Exclusions parameter is set to No and
3066 -- Exclude Tuition Waiver flag is yes
3067 IF p_v_override_excl = 'N' THEN
3068 IF g_rec_1098t_setup.excl_tuit_waiv_flag = 'Y' THEN
3069
3070 -- Check if Box4 is greater than or equal to Box 2
3071 IF l_n_box4 >= l_n_box2 THEN
3072
3073 -- If there are no records existing in the 1098T table
3074 -- then log the message and return
3075 IF l_b_new_run THEN
3076 fnd_message.set_module(g_v_package_name||'extract_data_main');
3077 fnd_message.set_name('IGS',
3078 'IGS_FI_1098T_STU_EXCL_WAIVE');
3079 fnd_file.put_line(fnd_file.log,
3080 fnd_message.get);
3081 RETURN;
3082 ELSE
3083 -- Else check for IRS filed flag. If it is set to N, then validation status
3084 -- is Do Not Report
3085 IF l_rec_1098t_data.irs_filed_flag = 'N' THEN
3086 g_v_validation_status := 'DNT_RPT';
3087 END IF;
3088 END IF;
3089 END IF;
3090 END IF;
3091 END IF;
3092
3093 -- Derive box 6,8 and 9
3094 l_v_box6 := l_v_next_acad_flag;
3095
3096 l_v_box8 := compute_box8(p_n_person_id => p_n_person_id,
3097 p_v_tax_year_name => p_v_tax_year_name);
3098
3099 l_v_box9 := compute_box9(p_n_person_id => p_n_person_id,
3100 p_v_tax_year_name => p_v_tax_year_name);
3101
3102 l_n_box2 := igs_fi_gen_gl.get_formatted_amount(l_n_box2);
3103 l_n_box3 := igs_fi_gen_gl.get_formatted_amount(l_n_box3);
3104 l_n_box4 := igs_fi_gen_gl.get_formatted_amount(l_n_box4);
3105 l_n_box5 := igs_fi_gen_gl.get_formatted_amount(l_n_box5);
3106
3107 -- Create 1098T Transactions
3108 insert_1098t_data(p_v_tax_year_name => p_v_tax_year_name,
3109 p_n_person_id => p_n_person_id,
3110 p_v_full_name => l_v_full_name,
3111 p_n_box2 => l_n_box2,
3112 p_n_box3 => l_n_box3,
3113 p_n_box4 => l_n_box4,
3114 p_n_box5 => l_n_box5,
3115 p_v_box6 => l_v_box6,
3116 p_v_box8 => l_v_box8,
3117 p_v_box9 => l_v_box9,
3118 p_v_file_addr_correction => p_v_file_addr_correction);
3119
3120
3121 END extract_data_main;
3122
3123 PROCEDURE extract(errbuf OUT NOCOPY VARCHAR2,
3124 retcode OUT NOCOPY NUMBER,
3125 p_v_tax_year_name VARCHAR2,
3126 p_n_person_id NUMBER,
3127 p_n_person_grp_id NUMBER,
3128 p_v_override_excl VARCHAR2,
3129 p_v_file_addr_correction VARCHAR2,
3130 p_v_test_run VARCHAR2) AS
3131
3132 /******************************************************************
3133 Created By : Amit Gairola
3134 Date Created By : 06-May-2005
3135 Purpose : Main procedure called from Concurrent Manager
3136
3137 Known limitations,enhancements,remarks:
3138 Change History
3139 Who When What
3140 ridas 14-Feb-2006 Bug #5021084. Added new parameter lv_group_type
3141 in call to igf_ap_ss_pkg.get_pid
3142 ***************************************************************** */
3143 TYPE c_per_grp_cur IS REF CURSOR;
3144
3145 cur_per_grp c_per_grp_cur;
3146 l_n_party_id hz_parties.party_id%TYPE;
3147 l_v_stmnt VARCHAR2(32767);
3148 l_v_status VARCHAR2(2000);
3149 lv_group_type igs_pe_persid_group_v.group_type%TYPE;
3150
3151 BEGIN
3152 SAVEPOINT SP_EXTRACT_1098T_DATA;
3153 retcode := 0;
3154
3155 -- Initialize the variables
3156 init;
3157
3158 -- Log parameters
3159 log_params(p_v_tax_year_name => p_v_tax_year_name,
3160 p_n_person_id => p_n_person_id,
3161 p_n_person_grp_id => p_n_person_grp_id,
3162 p_v_override_excl => p_v_override_excl,
3163 p_v_file_addr_correction => p_v_file_addr_correction,
3164 p_v_test_run => p_v_test_run);
3165
3166 -- Validate parameters
3167 IF NOT validate_params(p_v_tax_year_name => p_v_tax_year_name,
3168 p_n_person_id => p_n_person_id,
3169 p_n_person_grp_id => p_n_person_grp_id,
3170 p_v_override_excl => p_v_override_excl,
3171 p_v_file_addr_correction => p_v_file_addr_correction,
3172 p_v_test_run => p_v_test_run) THEN
3173 retcode := 2;
3174 RETURN;
3175 END IF;
3176
3177 -- If person Id is not null then call the extract_data_main for the person id passed
3178 IF p_n_person_id IS NOT NULL THEN
3179 BEGIN
3180 extract_data_main(p_v_tax_year_name => p_v_tax_year_name,
3181 p_n_person_id => p_n_person_id,
3182 p_v_override_excl => p_v_override_excl,
3183 p_v_file_addr_correction => p_v_file_addr_correction);
3184 EXCEPTION
3185 WHEN OTHERS THEN
3186 retcode := 2;
3187 ROLLBACK TO SAVEPOINT SP_EXTRACT_1098T_DATA;
3188 fnd_message.set_module(g_v_package_name||'extract_data');
3189 fnd_file.put_line(fnd_file.log,
3190 sqlerrm);
3191 END;
3192 ELSE
3193
3194 -- If the person id group is not null, then loop across all the persons in the person
3195 -- group and extract the 1098T data for them
3196 -- Bug #5021084
3197 l_v_stmnt := igf_ap_ss_pkg.get_pid(p_pid_grp => p_n_person_grp_id,
3198 p_status => l_v_status,
3199 p_group_type => lv_group_type);
3200
3201 IF l_v_status <> 'S' THEN
3202 fnd_file.put_line(fnd_file.log, l_v_stmnt);
3203 retcode := 2;
3204 RETURN;
3205 END IF;
3206
3207 --Bug #5021084. Passing Group ID if the group type is STATIC.
3208 IF lv_group_type = 'STATIC' THEN
3209 OPEN cur_per_grp FOR l_v_stmnt USING p_n_person_grp_id;
3210 ELSIF lv_group_type = 'DYNAMIC' THEN
3211 OPEN cur_per_grp FOR l_v_stmnt;
3212 END IF;
3213
3214 LOOP
3215 FETCH cur_per_grp INTO l_n_party_id;
3216 EXIT WHEN cur_per_grp%NOTFOUND;
3217 BEGIN
3218 SAVEPOINT SP_EXTRACT_MAIN;
3219 extract_data_main(p_v_tax_year_name => p_v_tax_year_name,
3220 p_n_person_id => l_n_party_id,
3221 p_v_override_excl => p_v_override_excl,
3222 p_v_file_addr_correction => p_v_file_addr_correction);
3223 EXCEPTION
3224 WHEN OTHERS THEN
3225 retcode := 1;
3226 ROLLBACK TO SP_EXTRACT_MAIN;
3227 fnd_message.set_module(g_v_package_name||'extract_data');
3228 fnd_file.put_line(fnd_file.log,
3229 sqlerrm);
3230 END;
3231 END LOOP;
3232 CLOSE cur_per_grp;
3233 END IF;
3234
3235 -- If the Test Run is Yes, then rollback the transactions else commit
3236 IF p_v_test_run = 'Y' THEN
3237 ROLLBACK TO SP_EXTRACT_1098T_DATA;
3238 fnd_message.set_name('IGS',
3239 'IGS_FI_PRC_TEST_RUN');
3240 fnd_file.put_line(fnd_file.log,
3241 fnd_message.get);
3242 ELSE
3243 COMMIT;
3244 END IF;
3245
3246 EXCEPTION
3247 WHEN e_resource_busy THEN
3248 ROLLBACK TO SP_EXTRACT_1098T_DATA;
3249 retcode := 2;
3250 fnd_message.set_module(g_v_package_name||'extract');
3251 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
3252 fnd_file.put_line(fnd_file.log,fnd_message.get());
3253 fnd_file.new_line(fnd_file.log);
3254 WHEN OTHERS THEN
3255 retcode := 2;
3256 ROLLBACK TO SP_EXTRACT_1098T_DATA;
3257 fnd_message.set_module(g_v_package_name||'extract');
3258 igs_ge_msg_stack.conc_exception_hndl;
3259 fnd_file.put_line(fnd_file.log, fnd_message.get || ' - ' || SQLERRM);
3260 END extract;
3261 END igs_fi_1098t_extract_data;