[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_WAV_UTILS_002
Source
1 PACKAGE BODY igs_fi_wav_utils_002 AS
2 /* $Header: IGSFI97B.pls 120.6 2005/10/31 10:31:53 appldev noship $ */
3 /************************************************************************
4 Created By : Umesh Udayaprakash
5 Date Created By : 7/4/2005
6 Purpose : Generic util Pacakge for Waiver Functionality
7 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
8 Known limitations,enhancements,remarks:
9 Change History
10 Who When What
11 smadathi 28-Oct-2005 Bug 4704177: Enhancement for Tuition Waiver
12 CCR. Added function to check for the Error Account = 'Y'
13 gurprsin 25-Oct-2005 Bug 4686711, Modified the cursor definition in reverse_waiver
14 and get_waiver_reversal_amount methods.
15 akandreg 20-Oct-2005 Bug 4677083, Modified the reverse_waiver function to
16 include the GL_DATE validation.
17 *************************************************************************/
18 PROCEDURE log_to_fnd ( p_v_module IN VARCHAR2,
19 p_v_string IN VARCHAR2 ) IS
20 /******************************************************************
21 Created By : Umesh Udayaprakash
22 Date Created By : 8/5/2005
23 Purpose : Procedure for logging
24
25 Known limitations,enhancements,remarks:
26 Change History
27 Who When What
28 ***************************************************************** */
29 BEGIN
30
31 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
32
33 fnd_log.string( fnd_log.level_statement, 'igs.plsql.igs_fi_wav_utils_002.' || p_v_module, p_v_string);
34 END IF;
35
36 END log_to_fnd;
37
38 PROCEDURE call_charges_api( p_n_person_id IN hz_parties.party_id%TYPE,
39 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
40 p_n_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
41 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
42 p_v_adj_fee_type IN igs_fi_fee_type.fee_type%TYPE,
43 p_v_currency_cd IN igs_fi_control.currency_cd%TYPE,
44 p_n_waiver_amt IN igs_fi_inv_int_all.invoice_amount%TYPE,
45 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
46 p_n_invoice_id OUT NOCOPY igs_fi_inv_int.invoice_id%TYPE,
47 x_return_status OUT NOCOPY VARCHAR2) AS
48 /******************************************************************
49 Created By : Umesh Udayaprakash
50 Date Created By : 7/4/2005
51 Purpose : invoked within the waiver processing routine, public Waiver API
52 and waiver application routine for creating waiver adjustment transactions.
53 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
54 Known limitations,enhancements,remarks:
55 Change History
56 Who When What
57 ******************************************************************/
58 --Cursor for retreving the Description
59 CURSOR cur_fee_type_desc(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
60 cp_closed_ind igs_fi_fee_type.closed_ind%TYPE) IS
61 SELECT description
62 FROM igs_fi_fee_type_all
63 WHERE fee_type = cp_v_fee_type
64 AND closed_ind = cp_closed_ind;
65
66 l_rec_chg_header igs_fi_charges_api_pvt.header_rec_type;
67 l_rec_chg_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
68 l_rec_chg_line_id_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
69 l_cur_fee_type_desc cur_fee_type_desc%ROWTYPE;
70 l_n_msg_count NUMBER := 0;
71 l_v_msg_data VARCHAR2(4000) := NULL;
72 l_n_waiver_amount NUMBER;
73 l_msg VARCHAR2(2000);
74 BEGIN
75 /**
76 Check For Required parameters.
77 */
78 IF ( p_n_person_id IS NULL OR p_v_fee_cal_type IS NULL OR p_n_fee_ci_seq_number IS NULL OR
79 p_v_waiver_name IS NULL OR p_v_adj_fee_type IS NULL OR p_v_currency_cd IS NULL OR
80 p_n_waiver_amt IS NULL OR p_d_gl_date IS NULL) THEN
81 x_return_status :='E';
82 return;
83 END IF;
84 /**
85 Logging of all the Input Parameters
86 */
87 log_to_fnd(p_v_module => 'call_charges_api',
88 p_v_string => 'Person Id ' || p_n_person_id);
89 log_to_fnd(p_v_module => 'call_charges_api',
90 p_v_string => 'Fee cal Type ' || p_v_fee_cal_type);
91 log_to_fnd(p_v_module => 'call_charges_api',
92 p_v_string => 'Fee Sequence Number ' || p_n_fee_ci_seq_number);
93 log_to_fnd(p_v_module => 'call_charges_api',
94 p_v_string => 'Waiver Name ' || p_v_waiver_name);
95 log_to_fnd(p_v_module => 'call_charges_api',
96 p_v_string => 'Adjustment Fee Type ' || p_v_adj_fee_type);
97 log_to_fnd(p_v_module => 'call_charges_api',
98 p_v_string => 'Currency Code ' ||p_v_currency_cd);
99 log_to_fnd(p_v_module => 'call_charges_api',
100 p_v_string => 'Waiver Amount ' || p_n_waiver_amt);
101 log_to_fnd(p_v_module => 'call_charges_api',
102 p_v_string => 'Gl Date ' || p_d_gl_date);
103
104 OPEN cur_fee_type_desc(cp_v_fee_type => p_v_adj_fee_type,
105 cp_closed_ind => 'N');
106 FETCH cur_fee_type_desc INTO l_cur_fee_type_desc;
107
108
109 --Check whether there exists a feetype in the igs_fi_fee_type_all table.
110 IF cur_fee_type_desc%NOTFOUND THEN
111 CLOSE cur_fee_type_desc;
112 x_return_status :='E';
113 RETURN;
114 END IF;
115
116 CLOSE cur_fee_type_desc;
117
118 l_rec_chg_header.p_person_id := p_n_person_id;
119 l_rec_chg_header.p_fee_type := p_v_adj_fee_type;
120 l_rec_chg_header.p_fee_cat := NULL;
121 l_rec_chg_header.p_fee_cal_type := p_v_fee_cal_type;
122 l_rec_chg_header.p_fee_ci_sequence_number := p_n_fee_ci_seq_number;
123 l_rec_chg_header.p_course_cd := NULL;
124 l_rec_chg_header.p_attendance_type := NULL;
125 l_rec_chg_header.p_attendance_mode := NULL;
126 l_rec_chg_header.p_invoice_amount := ABS(p_n_waiver_amt);
127 l_rec_chg_header.p_invoice_creation_date := TRUNC(SYSDATE);
128 l_rec_chg_header.p_invoice_desc := l_cur_fee_type_desc.description;
129 l_rec_chg_header.p_transaction_type := 'WAIVER_ADJ';
130 l_rec_chg_header.p_currency_cd := p_v_currency_cd;
131 l_rec_chg_header.p_exchange_rate := 1;
132 l_rec_chg_header.p_effective_date := TRUNC(SYSDATE);
133 l_rec_chg_header.p_waiver_flag := NULL;
134 l_rec_chg_header.p_waiver_reason := NULL;
135 l_rec_chg_header.p_source_transaction_id := NULL;
136 l_rec_chg_header.p_waiver_name := p_v_waiver_name;
137
138 l_rec_chg_line_tbl(1).p_s_chg_method_type := NULL;
139 l_rec_chg_line_tbl(1).p_description := l_cur_fee_type_desc.description;
140 l_rec_chg_line_tbl(1).p_chg_elements := NULL;
141 l_rec_chg_line_tbl(1).p_amount := ABS(p_n_waiver_amt);
142 l_rec_chg_line_tbl(1).p_unit_attempt_status := NULL;
143 l_rec_chg_line_tbl(1).p_eftsu := NULL;
144 l_rec_chg_line_tbl(1).p_credit_points := NULL;
145 l_rec_chg_line_tbl(1).p_org_unit_cd := NULL;
146 l_rec_chg_line_tbl(1).p_attribute_category := NULL;
147 l_rec_chg_line_tbl(1).p_attribute1 := NULL;
148 l_rec_chg_line_tbl(1).p_attribute2 := NULL;
149 l_rec_chg_line_tbl(1).p_attribute3 := NULL;
150 l_rec_chg_line_tbl(1).p_attribute4 := NULL;
151 l_rec_chg_line_tbl(1).p_attribute5 := NULL;
152 l_rec_chg_line_tbl(1).p_attribute6 := NULL;
153 l_rec_chg_line_tbl(1).p_attribute7 := NULL;
154 l_rec_chg_line_tbl(1).p_attribute8 := NULL;
155 l_rec_chg_line_tbl(1).p_attribute9 := NULL;
156 l_rec_chg_line_tbl(1).p_attribute10 := NULL;
157 l_rec_chg_line_tbl(1).p_attribute11 := NULL;
158 l_rec_chg_line_tbl(1).p_attribute12 := NULL;
159 l_rec_chg_line_tbl(1).p_attribute13 := NULL;
160 l_rec_chg_line_tbl(1).p_attribute14 := NULL;
161 l_rec_chg_line_tbl(1).p_attribute15 := NULL;
162 l_rec_chg_line_tbl(1).p_attribute16 := NULL;
163 l_rec_chg_line_tbl(1).p_attribute17 := NULL;
164 l_rec_chg_line_tbl(1).p_attribute18 := NULL;
165 l_rec_chg_line_tbl(1).p_attribute19 := NULL;
166 l_rec_chg_line_tbl(1).p_attribute20 := NULL;
167 l_rec_chg_line_tbl(1).p_location_cd := NULL;
168 l_rec_chg_line_tbl(1).p_uoo_id := NULL;
169 l_rec_chg_line_tbl(1).p_d_gl_date := p_d_gl_date;
170 l_rec_chg_line_tbl(1).p_residency_status_cd := NULL;
171
172 log_to_fnd(p_v_module => 'call_charges_api',
173 p_v_string => 'Before Calling the Create Charges Api');
174
175 --Call Out ot the Create Charges methos to Create Charge.
176 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
177 p_init_msg_list => FND_API.G_FALSE,
178 p_commit => FND_API.G_FALSE,
179 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
180 p_header_rec => l_rec_chg_header,
181 p_line_tbl => l_rec_chg_line_tbl,
182 x_invoice_id => p_n_invoice_id,
183 x_line_id_tbl => l_rec_chg_line_id_tbl,
184 x_return_status => x_return_status,
185 x_msg_count => l_n_msg_count,
186 x_msg_data => l_v_msg_data,
187 x_waiver_amount => l_n_waiver_amount);
188 log_to_fnd(p_v_module => 'value of x_return_status',
189 p_v_string => 'After Calling the Create Charges Api'|| x_return_status );
190
191 IF x_return_status <> 'S' then
192 x_return_status := 'E';
193 IF l_n_msg_count = 1 THEN
194 fnd_message.set_encoded(l_v_msg_data);
195 log_to_fnd(p_v_module => 'call_charges_api',
196 p_v_string => 'Error Message ' || fnd_message.get);
197 ELSE
198 FOR l_count IN 1 .. l_n_msg_count LOOP
199 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
200 fnd_message.set_encoded(l_msg);
201 log_to_fnd(p_v_module => 'call_charges_api',
202 p_v_string => 'Error Message ' || fnd_message.get);
203 END LOOP;
204 END IF;
205 ELSE
206 log_to_fnd(p_v_module => 'call_charges_api',
207 p_v_string => 'Invoice Id ' || p_n_invoice_id);
208 END IF;
209 END call_charges_api;
210
211 PROCEDURE call_credits_api(p_n_person_id IN hz_parties.party_id%TYPE,
212 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
213 p_n_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
214 p_v_waiver_name IN igs_fi_waiver_pgms.waiver_name%TYPE,
215 p_n_credit_type_id IN igs_fi_credits.credit_id%TYPE,
216 p_v_currency_cd IN igs_fi_control.currency_cd%TYPE,
217 p_n_waiver_amt IN NUMBER,
218 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
219 p_n_credit_id OUT NOCOPY NUMBER,
220 x_return_status OUT NOCOPY VARCHAR2) AS
221 /******************************************************************
222 Created By : Umesh Udayaprakash
223 Date Created By : 7/4/2005
224 Purpose : Invoked within the waiver processing routine for creating
225 waiver credit transactions.
226 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
227 Known limitations,enhancements,remarks:
228 Change History
229 Who When What
230 ******************************************************************/
231 -- Cursor for selecting the credit type Description for the Credit id passed.
232 CURSOR cur_credit_type_desc(cp_n_credit_type_id NUMBER ) IS
233 SELECT crtyp.description
234 FROM igs_fi_cr_types crtyp
235 WHERE credit_type_id = cp_n_credit_type_id;
236
237 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
238 l_b_return_status BOOLEAN;
239 l_b_validation_flag BOOLEAN;
240 l_v_msg_name VARCHAR2(4000) := NULL;
241 l_n_msg_count NUMBER := 0;
242 l_v_msg_data VARCHAR2(4000) := NULL;
243 l_n_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE;
244 l_n_credit_number igs_fi_credits_all.credit_number%TYPE;
245 l_cur_credit_type_desc cur_credit_type_desc%ROWTYPE;
246 l_credit_rec_type igs_fi_credit_pvt.credit_rec_type;
247 l_attribute_rec_type igs_fi_credits_api_pub.attribute_rec_type;
248 l_msg VARCHAR2(2000);
249 BEGIN
250
251 IF ( p_n_person_id IS NULL OR p_v_fee_cal_type IS NULL OR p_n_fee_ci_seq_number IS NULL OR
252 p_v_waiver_name IS NULL OR p_n_credit_type_id IS NULL OR p_v_currency_cd IS NULL OR
253 p_n_waiver_amt IS NULL OR p_d_gl_date IS NULL ) THEN
254 x_return_status :='E';
255 RETURN;
256 END IF;
257 /**
258 Logging of all the Input Parameters
259 */
260 log_to_fnd(p_v_module => 'call_credits_api',
261 p_v_string => 'Person Id ' || p_n_person_id);
262 log_to_fnd(p_v_module => 'call_credits_api',
263 p_v_string => ' Fee Calendar Type ' || p_v_fee_cal_type);
264 log_to_fnd(p_v_module => 'call_credits_api',
265 p_v_string => ' Fee Sequence Number ' || p_n_fee_ci_seq_number );
266 log_to_fnd(p_v_module => 'call_credits_api',
267 p_v_string => 'Waiver Name ' || p_v_waiver_name);
268 log_to_fnd(p_v_module => 'call_credits_api',
269 p_v_string => 'Credit Type Id ' || p_n_credit_type_id);
270 log_to_fnd(p_v_module => 'call_credits_api',
271 p_v_string => ' Currency Code ' || p_v_currency_cd);
272 log_to_fnd(p_v_module => 'call_credits_api',
273 p_v_string => 'Waiver Amount ' || p_n_waiver_amt);
274 log_to_fnd(p_v_module => 'call_credits_api',
275 p_v_string => 'Gl Date ' || p_d_gl_date);
276 /**
277 Call out to Check Whether the Credit Type is active as on the System Date.
278 */
279 igs_fi_crdapi_util.validate_credit_type(p_n_credit_type_id => p_n_credit_type_id,
280 p_v_credit_class =>l_v_credit_class,
281 p_b_return_stat =>l_b_return_status);
282 log_to_fnd(p_v_module => 'call_credits_api',
283 p_v_string => 'After the Call out to validate_credit_type Api');
284 IF l_b_return_status = FALSE THEN
285 x_return_status := 'E';
286 RETURN;
287 END IF;
288 /**
289 Call out to Check Whether the Credit class is Valid or not.
290 */
291 l_b_validation_flag := igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => 'IGS_FI_CREDIT_CLASS',
292 p_v_lookup_code => l_v_credit_class);
293 log_to_fnd(p_v_module => 'call_credits_api',
294 p_v_string => 'After the Call out to validate_igs_lkp Api for validating Credit class');
295 IF l_b_validation_flag = FALSE THEN
296 x_return_status := 'E';
297 RETURN;
298 END IF;
299 /**
300 Call out to Check Whether the Credit Instrument is Valid or not.
301 */
302 l_b_validation_flag := igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => 'IGS_FI_CREDIT_INSTRUMENT',
303 p_v_lookup_code => 'WAIVER');
304 log_to_fnd(p_v_module => 'call_credits_api',
305 p_v_string => 'After the Call out to validate_igs_lkp Api for validating Credit Instrument');
306
307 IF l_b_validation_flag = FALSE THEN
308 x_return_status := 'E';
309 RETURN;
310 END IF;
311 /**
312 Call out to Check Whether the Waiver Amount passed is Valid or not.
313 */
314 igs_fi_crdapi_util.validate_amount( p_n_amount => p_n_waiver_amt,
315 p_b_return_status => l_b_validation_flag,
316 p_v_message_name => l_v_msg_name);
317 log_to_fnd(p_v_module => 'call_credits_api',
318 p_v_string => 'After the Call out to validate_amount Api for validating Waiver Amount');
319 IF l_b_validation_flag = FALSE THEN
320 fnd_message.set_name('IGS',l_v_msg_name);
321 log_to_fnd(p_v_module => 'call_credits_api',
322 p_v_string => 'Message Returned from validate_amount Api ' || fnd_message.get);
323 x_return_status := 'E';
324 RETURN;
325 END IF;
326
327 OPEN cur_credit_type_desc(p_n_credit_type_id);
328 FETCH cur_credit_type_desc INTO l_cur_credit_type_desc;
329 CLOSE cur_credit_type_desc;
330
331 l_credit_rec_type.p_credit_status :='CLEARED';
332 l_credit_rec_type.p_credit_source := NULL;
333 l_credit_rec_type.p_party_id := p_n_person_id;
334 l_credit_rec_type.p_credit_type_id := p_n_credit_type_id;
335 l_credit_rec_type.p_credit_instrument := 'WAIVER';
336 l_credit_rec_type.p_description := l_cur_credit_type_desc.description;
337 l_credit_rec_type.p_amount := p_n_waiver_amt;
338 l_credit_rec_type.p_currency_cd := p_v_currency_cd;
339 l_credit_rec_type.p_exchange_rate := 1;
340 l_credit_rec_type.p_transaction_date := TRUNC(SYSDATE);
341 l_credit_rec_type.p_effective_date := TRUNC(SYSDATE);
342 l_credit_rec_type.p_source_transaction_id := NULL;
343 l_credit_rec_type.p_receipt_lockbox_number := NULL;
344 l_credit_rec_type.p_credit_card_code := NULL;
345 l_credit_rec_type.p_credit_card_holder_name := NULL;
346 l_credit_rec_type.p_credit_card_number := NULL;
347 l_credit_rec_type.p_credit_card_expiration_date := NULL;
348 l_credit_rec_type.p_credit_card_approval_code := NULL;
349 l_credit_rec_type.p_invoice_id := NULL;
350 l_credit_rec_type.p_awd_yr_cal_type := NULL;
351 l_credit_rec_type.p_awd_yr_ci_sequence_number := NULL;
352 l_credit_rec_type.p_fee_cal_type := p_v_fee_cal_type;
353 l_credit_rec_type.p_fee_ci_sequence_number := p_n_fee_ci_seq_number;
354 l_credit_rec_type.p_check_number := NULL;
355 l_credit_rec_type.p_source_tran_type := NULL;
356 l_credit_rec_type.p_source_tran_ref_number := NULL;
357 l_credit_rec_type.p_gl_date := p_d_gl_date;
358 l_credit_rec_type.p_v_credit_card_payee_cd := NULL;
359 l_credit_rec_type.p_v_credit_card_status_code := NULL;
360 l_credit_rec_type.p_v_credit_card_tangible_cd := NULL;
361 l_credit_rec_type.p_lockbox_interface_id := NULL;
362 l_credit_rec_type.p_batch_name := NULL;
363 l_credit_rec_type.p_deposit_date := NULL;
364 l_credit_rec_type.p_waiver_name := p_v_waiver_name;
365
366 l_attribute_rec_type.p_attribute_category := NULL;
367 l_attribute_rec_type.p_attribute1 := NULL;
368 l_attribute_rec_type.p_attribute2 := NULL;
369 l_attribute_rec_type.p_attribute3 := NULL;
370 l_attribute_rec_type.p_attribute4 := NULL;
371 l_attribute_rec_type.p_attribute5 := NULL;
372 l_attribute_rec_type.p_attribute6 := NULL;
373 l_attribute_rec_type.p_attribute7 := NULL;
374 l_attribute_rec_type.p_attribute8 := NULL;
375 l_attribute_rec_type.p_attribute9 := NULL;
376 l_attribute_rec_type.p_attribute10 := NULL;
377 l_attribute_rec_type.p_attribute11 := NULL;
378 l_attribute_rec_type.p_attribute12 := NULL;
379 l_attribute_rec_type.p_attribute13 := NULL;
380 l_attribute_rec_type.p_attribute14 := NULL;
381 l_attribute_rec_type.p_attribute15 := NULL;
382 l_attribute_rec_type.p_attribute16 := NULL;
383 l_attribute_rec_type.p_attribute17 := NULL;
384 l_attribute_rec_type.p_attribute18 := NULL;
385 l_attribute_rec_type.p_attribute19 := NULL;
386 l_attribute_rec_type.p_attribute20 := NULL;
387
388 log_to_fnd(p_v_module => 'call_credits_api',
389 p_v_string => 'Before the Callout to the igs_fi_credit_pvt.create_credit Api');
390 --Call Out to Create waiver credits
391 igs_fi_credit_pvt.create_credit(p_api_version => 2.1,
392 p_init_msg_list => FND_API.G_FALSE,
393 p_commit => FND_API.G_FALSE,
394 p_validation_level => FND_API.G_VALID_LEVEL_NONE,
395 x_return_status => x_return_status,
396 x_msg_count => l_n_msg_count,
397 x_msg_data => l_v_msg_data,
398 p_credit_rec => l_credit_rec_type,
399 p_attribute_record => l_attribute_rec_type,
400 x_credit_id => p_n_credit_id,
401 x_credit_activity_id => l_n_credit_activity_id,
402 x_credit_number => l_n_credit_number);
403 IF x_return_status <> 'S' then
404 x_return_status := 'E';
405 --Code to Loop accross the message and Log It
406 IF l_n_msg_count = 1 THEN
407 fnd_message.set_encoded(l_v_msg_data);
408 log_to_fnd(p_v_module => 'call_credits_api',
409 p_v_string => 'Error Message ' || fnd_message.get);
410 ELSE
411 FOR l_count IN 1 .. l_n_msg_count LOOP
412 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
413 fnd_message.set_encoded(l_msg);
414 log_to_fnd(p_v_module => 'call_credits_api',
415 p_v_string => 'Error Message ' || fnd_message.get);
416 END LOOP;
417 END IF;
418 ELSE
419 log_to_fnd(p_v_module => 'call_credits_api',
420 p_v_string => 'Credit Id ' || p_n_credit_id );
421 END IF;
422 END call_credits_api;
423
424 PROCEDURE reverse_waiver(p_n_source_credit_id IN igs_fi_applications.credit_id%TYPE,
425 p_v_reversal_reason IN igs_lookup_values.lookup_code%TYPE,
426 p_v_reversal_comments IN igs_fi_credits_all.reversal_comments%TYPE,
427 p_d_reversal_gl_date IN DATE,
428 p_v_return_status OUT NOCOPY VARCHAR2,
429 p_v_message_name OUT NOCOPY VARCHAR2) AS
430 /******************************************************************
431 Created By : Umesh Udayaprakash
432 Date Created By : 7/4/2005
433 Purpose : procedure Used in Reverse transaction Self service Page.
434 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
435 Known limitations,enhancements,remarks:
436 Change History
437 Who When What
438 gurprsin 25-Oct-2005 Bug 4686711, Modified the cursor cur_appl_record definition.
439 akandreg 20-Oct-2005 Bug 4677083, Modified the reverse_waiver function to
440 include the GL_DATE validation.
441 ******************************************************************/
442 --Cursor to Check whether the Waiver Credit Record Exists
443 CURSOR cur_credit_rec_exists(cp_n_source_credit_id NUMBER ) IS
444 SELECT crd.rowid,crd. *
445 FROM igs_fi_credits_all crd
446 WHERE crd.credit_id = cp_n_source_credit_id;
447
448 ----Cursor to Check whether any application Records exists for the Waiver Credit Transaction
449 CURSOR cur_appl_record(cp_n_source_credit_id NUMBER) IS
450 SELECT igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
451 appl.application_id,
452 appl.credit_id,
453 appl.invoice_id
454 FROM igs_fi_applications appl,
455 igs_fi_inv_int_all inv
456 WHERE appl.credit_id = cp_n_source_credit_id
457 AND appl.invoice_id = inv.invoice_id
458 AND appl.application_type = 'APP'
459 AND inv.transaction_type <> 'WAIVER_ADJ'
460 --Added for the Bug 4686711, to exclude those charges for which unapp record exists in application table.
461 AND NOT EXISTS (
462 SELECT 'X'
463 FROM IGS_FI_APPLICATIONS APPL2
464 WHERE APPL2.APPLICATION_TYPE = 'UNAPP'
465 AND APPL2.LINK_APPLICATION_ID = APPL.APPLICATION_ID
466 AND APPL2.AMOUNT_APPLIED = - APPL.AMOUNT_APPLIED)
467 ORDER BY appl.application_id;
468
469 --Cursor to obtain the waiver program attributes.
470 CURSOR cur_waiver_prg_attr(cp_fee_cal_type igs_fi_waiver_pgms.fee_cal_type%TYPE,
471 cp_fee_ci_sequence_number igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
472 cp_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE) IS
473 SELECT fwp.fee_cal_type,
474 fwp.fee_ci_sequence_number,
475 fwp.waiver_name,
476 fwp.credit_type_id,
477 fwp.target_fee_type,
478 fwp.adjustment_fee_type
479 FROM igs_fi_waiver_pgms fwp
480 WHERE fwp.fee_cal_type = cp_fee_cal_type
481 AND fwp.fee_ci_sequence_number = cp_fee_ci_sequence_number
482 AND fwp.waiver_name = cp_waiver_name;
483
484
485 l_v_conv_proc_ind igs_fi_control.conv_process_run_ind%TYPE;
486 l_v_message_name fnd_new_messages.message_name%TYPE;
487 l_v_meaning igs_lookup_values.meaning%TYPE;
488
489 l_cur_credit_rec_exists cur_credit_rec_exists%ROWTYPE;
490 l_cur_appl_record cur_appl_record%ROWTYPE;
491 l_cur_waiver_prg_attr cur_waiver_prg_attr%ROWTYPE;
492
493 l_n_application_id igs_fi_applications.application_id%TYPE;
494 l_n_dr_gl_ccid igs_fi_cr_activities.dr_gl_ccid%TYPE;
495 l_n_cr_gl_ccid igs_fi_cr_activities.cr_gl_ccid%TYPE;
496 l_v_dr_account_cd igs_fi_cr_activities.dr_account_cd%TYPE;
497 l_v_cr_account_cd igs_fi_cr_activities.cr_account_cd%TYPE;
498 l_n_unapp_amount igs_fi_credits_all.unapplied_amount%TYPE;
499 l_n_inv_amt_due igs_fi_inv_int_all.invoice_amount_due%TYPE;
500 l_v_err_msg fnd_new_messages.message_name%TYPE;
501 l_b_status BOOLEAN;
502 l_n_wav_adj_amount NUMBER;
503 l_n_unapplied_amount NUMBER;
504 e_expected_error EXCEPTION;
505
506 l_v_currency_cd igs_fi_control_all.currency_cd%TYPE;
507 l_v_curr_desc fnd_currencies_tl.name%TYPE;
508 l_n_invoice_id igs_fi_inv_int.invoice_id%TYPE;
509
510 l_v_closing_status VARCHAR2(1);
511
512 BEGIN
513 SAVEPOINT sp_reverse_waiver; -- Save point for the procedure to Rollout if any failure occurs.
514 --Check For mandatory parameters
515 IF ( p_n_source_credit_id IS NULL OR p_v_reversal_reason IS NULL OR p_d_reversal_gl_date IS NULL) THEN
516 p_v_return_status := 'E';
517 p_v_message_name := 'IGS_UC_NO_MANDATORY_PARAMS';
518 RETURN;
519 END IF;
520 /**
521 Logging of all the Input Parameters
522 */
523 log_to_fnd(p_v_module => 'reverse_waiver',
524 p_v_string => 'Source Credit Id ' || p_n_source_credit_id );
525 log_to_fnd(p_v_module => 'reverse_waiver',
526 p_v_string => 'Reversal Reason ' || p_v_reversal_reason );
527 log_to_fnd(p_v_module => 'reverse_waiver',
528 p_v_string => 'Reversal Gl date ' || p_d_reversal_gl_date );
529
530 --Check for the hold Conversion process is executed
531 igs_fi_gen_007.finp_get_conv_prc_run_ind(p_n_conv_process_run_ind => l_v_conv_proc_ind ,
532 p_v_message_name => l_v_message_name);
533 log_to_fnd(p_v_module => 'reverse_waiver',
534 p_v_string => 'After the Callout to the finp_get_conv_prc_run_ind Api');
535
536 IF l_v_conv_proc_ind = 1 AND l_v_message_name IS NULL THEN
537 p_v_return_status := 'E';
538 p_v_message_name := 'IGS_FI_REASS_BAL_PRC_RUN';
539 RETURN;
540 END IF;
541
542 --Check whether the Waiver Credit Record Exists
543 OPEN cur_credit_rec_exists(p_n_source_credit_id);
544 FETCH cur_credit_rec_exists INTO l_cur_credit_rec_exists;
545
546 IF cur_credit_rec_exists%NOTFOUND THEN
547 CLOSE cur_credit_rec_exists;
548 p_v_return_status := 'E';
549 p_v_message_name := 'IGS_FI_WAV_SRC_CRD_INVALID';
550 RETURN;
551 END IF;
552 CLOSE cur_credit_rec_exists;
553 -- Check of Waiver Credit Status
554 IF l_cur_credit_rec_exists.STATUS = 'REVERSED' THEN
555 p_v_return_status := 'E';
556 p_v_message_name := 'IGS_FI_WAV_SRC_CRD_REVERSED';
557 RETURN;
558 END IF;
559 log_to_fnd(p_v_module => 'reverse_waiver',
560 p_v_string => 'Completed the Validation of the Source Credit id and Status');
561
562 --Bug 4677083, Modified the reverse_waiver function to include the GL_DATE validation.
563 -- Check GL Date Status
564 igs_fi_gen_gl.get_period_status_for_date(p_d_date => p_d_reversal_gl_date,
565 p_v_closing_status => l_v_closing_status,
566 p_v_message_name => l_v_message_name);
567 IF l_v_message_name IS NOT NULL THEN
568 log_to_fnd(p_v_module => 'reverse_waiver',
569 p_v_string => 'Validation Failed for GL date Status');
570 p_v_return_status := 'E';
571 p_v_message_name := l_v_message_name;
572 RETURN;
573 ELSIF l_v_closing_status NOT IN ('O','F') THEN
574 log_to_fnd(p_v_module => 'reverse_waiver',
575 p_v_string => 'GL date is not in open or Future period');
576 p_v_return_status := 'E';
577 p_v_message_name := 'IGS_FI_INVALID_GL_DATE';
578 RETURN;
579 END IF;
580
581 log_to_fnd(p_v_module => 'reverse_waiver',
582 p_v_string => 'Completed successfully the Validation of GL date Status');
583
584 l_v_meaning := igs_fi_gen_gl.get_lkp_meaning(p_v_lookup_type => 'IGS_FI_WAV_REVERSAL_REASON' ,
585 p_v_lookup_code => p_v_reversal_reason );
586 -- Check for application Records for the Waiver Credit Transaction
587 FOR l_cur_appl_record IN cur_appl_record(cp_n_source_credit_id => p_n_source_credit_id)
588 LOOP
589 l_n_application_id := l_cur_appl_record.application_id;
590
591 igs_fi_gen_007.create_application(p_application_id => l_n_application_id,
592 p_credit_id => l_cur_appl_record.credit_id,
593 p_invoice_id => l_cur_appl_record.invoice_id,
594 p_amount_apply => l_cur_appl_record.amount_applied,
595 p_appl_type => 'UNAPP',
596 p_appl_hierarchy_id => NULL,
597 p_validation => 'Y',
598 p_dr_gl_ccid => l_n_dr_gl_ccid,
599 p_cr_gl_ccid => l_n_cr_gl_ccid,
600 p_dr_account_cd => l_v_dr_account_cd,
601 p_cr_account_cd => l_v_cr_account_cd,
602 p_unapp_amount => l_n_unapplied_amount,
603 p_inv_amt_due => l_n_inv_amt_due,
604 p_err_msg => p_v_message_name,
605 p_status => l_b_status,
606 p_d_gl_date => p_d_reversal_gl_date);
607 IF l_b_status = FALSE THEN
608
609 fnd_message.set_name('IGS',p_v_message_name);
610 log_to_fnd(p_v_module => 'reverse_waiver',
611 p_v_string => 'Existing with this Message from create_application Api ' || fnd_message.get);
612 RAISE e_expected_error;
613 END IF;
614 END LOOP;
615 -- retervie the Waiver program Attribute
616 OPEN cur_waiver_prg_attr( cp_fee_cal_type =>l_cur_credit_rec_exists.fee_cal_type,
617 cp_fee_ci_sequence_number =>l_cur_credit_rec_exists.fee_ci_sequence_number,
618 cp_waiver_name =>l_cur_credit_rec_exists.waiver_name);
619
620 FETCH cur_waiver_prg_attr INTO l_cur_waiver_prg_attr;
621 CLOSE cur_waiver_prg_attr;
622 -- Call out to Reterive the local Currency Setup
623 igs_fi_gen_gl.finp_get_cur(p_v_currency_cd => l_v_currency_cd,
624 p_v_curr_desc => l_v_curr_desc,
625 p_v_message_name => l_v_message_name);
626 IF l_v_message_name <> NULL THEN
627 fnd_message.set_name('IGS',l_v_message_name);
628 log_to_fnd(p_v_module => 'reverse_waiver',
629 p_v_string => 'Exiting After the call igs_fi_gen_gl.finp_get_cur ' || fnd_message.get);
630 RAISE e_expected_error;
631 END IF ;
632 --call out to create a Waiver adjustment Charge
633 IF l_n_unapplied_amount > 0 THEN
634
635 log_to_fnd(p_v_module => 'reverse_waiver',
636 p_v_string => 'Before the Callout to call Charges Api method when the Un Applied Amount is > 0 ');
637 call_charges_api(p_n_person_id => l_cur_credit_rec_exists.party_id,
638 p_v_fee_cal_type => l_cur_credit_rec_exists.fee_cal_type,
639 p_n_fee_ci_seq_number => l_cur_credit_rec_exists.fee_ci_sequence_number,
640 p_v_waiver_name => l_cur_waiver_prg_attr.waiver_name,
641 p_v_adj_fee_type => l_cur_waiver_prg_attr.adjustment_fee_type,
642 p_v_currency_cd => l_v_currency_cd,
643 p_n_waiver_amt => l_n_unapplied_amount,
644 p_d_gl_date => p_d_reversal_gl_date,
645 p_n_invoice_id => l_n_invoice_id,
646 x_return_status => p_v_return_status);
647
648 IF p_v_return_status <> 'S' THEN
649 RAISE e_expected_error;
650 END IF;
651 log_to_fnd(p_v_module => 'reverse_waiver',
652 p_v_string => 'Before the Callout to igs_fi_gen_007.create_application');
653 --Call out for applying the waiver adjustment Cahrge against the waiver credit
654 l_n_application_id := null;
655 igs_fi_gen_007.create_application(p_application_id => l_n_application_id,
656 p_credit_id => p_n_source_credit_id,
657 p_invoice_id => l_n_invoice_id,
658 p_amount_apply => l_n_unapplied_amount,
659 p_appl_type => 'APP',
660 p_appl_hierarchy_id => NULL,
661 p_validation => 'Y',
662 p_dr_gl_ccid => l_n_dr_gl_ccid,
663 p_cr_gl_ccid => l_n_cr_gl_ccid,
664 p_dr_account_cd => l_v_dr_account_cd,
665 p_cr_account_cd => l_v_cr_account_cd,
666 p_unapp_amount => l_n_unapp_amount,
667 p_inv_amt_due => l_n_inv_amt_due,
668 p_err_msg => p_v_message_name,
669 p_status => l_b_status,
670 p_d_gl_date => p_d_reversal_gl_date);
671 IF l_b_status = FALSE THEN
672 fnd_message.set_name('IGS',p_v_message_name);
673 log_to_fnd(p_v_module => 'reverse_waiver',
674 p_v_string => 'Exiting After the call igs_fi_gen_007.create_application ' || fnd_message.get);
675 RAISE e_expected_error;
676 END IF;
677 log_to_fnd(p_v_module => 'reverse_waiver',
678 p_v_string => 'Before the Callout to update the Credit api Table');
679 --Call out to update the credit status to Reversed.
680 igs_fi_credits_pkg.update_row(x_rowid => l_cur_credit_rec_exists.rowid,
681 x_credit_id => l_cur_credit_rec_exists.credit_id,
682 x_credit_number => l_cur_credit_rec_exists.credit_number,
683 x_status => 'REVERSED',
684 x_credit_source => l_cur_credit_rec_exists.credit_source,
685 x_party_id => l_cur_credit_rec_exists.party_id,
686 x_credit_type_id => l_cur_credit_rec_exists.credit_type_id,
687 x_credit_instrument => l_cur_credit_rec_exists.credit_instrument,
688 x_description => l_cur_credit_rec_exists.description,
689 x_amount => l_cur_credit_rec_exists.amount,
690 x_currency_cd => l_cur_credit_rec_exists.currency_cd,
691 x_exchange_rate => l_cur_credit_rec_exists.exchange_rate,
692 x_transaction_date => l_cur_credit_rec_exists.transaction_date,
693 x_effective_date => l_cur_credit_rec_exists.effective_date,
694 x_reversal_date => TRUNC(SYSDATE),
695 x_reversal_reason_code => p_v_reversal_reason,
696 x_reversal_comments => p_v_reversal_comments,
697 x_unapplied_amount => l_cur_credit_rec_exists.unapplied_amount,
698 x_source_transaction_id => l_cur_credit_rec_exists.source_transaction_id,
699 x_receipt_lockbox_number => l_cur_credit_rec_exists.receipt_lockbox_number,
700 x_merchant_id => l_cur_credit_rec_exists.merchant_id,
701 x_credit_card_code => l_cur_credit_rec_exists.credit_card_code,
702 x_credit_card_holder_name => l_cur_credit_rec_exists.credit_card_holder_name,
703 x_credit_card_number => l_cur_credit_rec_exists.credit_card_number,
704 x_credit_card_expiration_date => l_cur_credit_rec_exists.credit_card_expiration_date,
705 x_credit_card_approval_code => l_cur_credit_rec_exists.credit_card_approval_code,
706 x_awd_yr_cal_type => l_cur_credit_rec_exists.awd_yr_cal_type,
707 x_awd_yr_ci_sequence_number => l_cur_credit_rec_exists.awd_yr_ci_sequence_number,
708 x_fee_cal_type => l_cur_credit_rec_exists.fee_cal_type,
709 x_fee_ci_sequence_number => l_cur_credit_rec_exists.fee_ci_sequence_number,
710 x_attribute_category => l_cur_credit_rec_exists.attribute_category,
711 x_attribute1 => l_cur_credit_rec_exists.attribute1,
712 x_attribute2 => l_cur_credit_rec_exists.attribute2,
713 x_attribute3 => l_cur_credit_rec_exists.attribute3,
714 x_attribute4 => l_cur_credit_rec_exists.attribute4,
715 x_attribute5 => l_cur_credit_rec_exists.attribute5,
716 x_attribute6 => l_cur_credit_rec_exists.attribute6,
717 x_attribute7 => l_cur_credit_rec_exists.attribute7,
718 x_attribute8 => l_cur_credit_rec_exists.attribute8,
719 x_attribute9 => l_cur_credit_rec_exists.attribute9,
720 x_attribute10 => l_cur_credit_rec_exists.attribute10,
721 x_attribute11 => l_cur_credit_rec_exists.attribute11,
722 x_attribute12 => l_cur_credit_rec_exists.attribute12,
723 x_attribute13 => l_cur_credit_rec_exists.attribute13,
724 x_attribute14 => l_cur_credit_rec_exists.attribute14,
725 x_attribute15 => l_cur_credit_rec_exists.attribute15,
726 x_attribute16 => l_cur_credit_rec_exists.attribute16,
727 x_attribute17 => l_cur_credit_rec_exists.attribute17,
728 x_attribute18 => l_cur_credit_rec_exists.attribute18,
729 x_attribute19 => l_cur_credit_rec_exists.attribute19,
730 x_attribute20 => l_cur_credit_rec_exists.attribute20,
731 x_gl_date => l_cur_credit_rec_exists.gl_date,
732 x_check_number => l_cur_credit_rec_exists.check_number,
733 x_source_transaction_type => l_cur_credit_rec_exists.source_transaction_type,
734 x_source_transaction_ref => l_cur_credit_rec_exists.source_transaction_ref,
735 x_credit_card_status_code => l_cur_credit_rec_exists.credit_card_status_code,
736 x_credit_card_payee_cd => l_cur_credit_rec_exists.credit_card_payee_cd,
737 x_credit_card_tangible_cd => l_cur_credit_rec_exists.credit_card_tangible_cd,
738 x_lockbox_interface_id => l_cur_credit_rec_exists.lockbox_interface_id,
739 x_batch_name => l_cur_credit_rec_exists.batch_name,
740 x_deposit_date => l_cur_credit_rec_exists.deposit_date,
741 x_source_invoice_id => l_cur_credit_rec_exists.source_invoice_id,
742 x_tax_year_code => l_cur_credit_rec_exists.tax_year_code,
743 x_waiver_name => l_cur_credit_rec_exists.waiver_name
744 );
745 END IF; --End of the check for l_n_unapplied_amount > 0
746 p_v_return_status :='S';
747 EXCEPTION
748 WHEN e_expected_error THEN
749 ROLLBACK TO sp_reverse_waiver;
750 p_v_message_name := 'IGS_FI_WAV_REVERSAL_FAIL';
751 p_v_return_status := 'E';
752 WHEN OTHERS THEN
753 ROLLBACK TO sp_reverse_waiver;
754 p_v_message_name := 'IGS_FI_WAV_REVERSAL_FAIL';
755 p_v_return_status := 'E';
756 END reverse_waiver;
757
758
759 FUNCTION get_waiver_reversal_amount(p_n_source_credit_id IN igs_fi_applications.credit_id%TYPE) RETURN NUMBER IS
760 /******************************************************************
761 Created By : Umesh Udayaprakash
762 Date Created By : 7/4/2005
763 Purpose : Function to return the waiver Reversal Amount.
764 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
765 Known limitations,enhancements,remarks:
766 Change History
767 Who When What
768 gurprsin 25-Oct-2005 Bug 4686711, Modified the cursor cur_waiver_credit_tran_exists definition.
769 ******************************************************************/
770
771 --Cursor to Check whether a waiver credit Record Exists
772 CURSOR cur_credit_rec_exists(cp_n_source_credit_id NUMBER ) IS
773 SELECT crd. *
774 FROM igs_fi_credits_all crd
775 WHERE crd.credit_id = cp_n_source_credit_id;
776 --Cursor to Check whether any application record exists for the waiver credit Transaction.
777 CURSOR cur_waiver_credit_tran_exists(cp_n_source_credit_id NUMBER ) IS
778 SELECT igs_fi_gen_007.get_sum_appl_amnt(appl.application_id) amount_applied,
779 appl.application_id,
780 appl.credit_id,
781 appl.invoice_id
782 FROM igs_fi_applications appl,
783 igs_fi_inv_int_all inv
784 WHERE appl.credit_id = cp_n_source_credit_id
785 AND appl.invoice_id = inv.invoice_id
786 AND appl.application_type = 'APP'
787 AND inv.transaction_type <> 'WAIVER_ADJ'
788 --Added for the Bug 4686711, to exclude those charges for which unapp record exists in application table.
789 AND NOT EXISTS (
790 SELECT 'X'
791 FROM IGS_FI_APPLICATIONS APPL2
792 WHERE APPL2.APPLICATION_TYPE = 'UNAPP'
793 AND APPL2.LINK_APPLICATION_ID = APPL.APPLICATION_ID
794 AND APPL2.AMOUNT_APPLIED = - APPL.AMOUNT_APPLIED)
795 ORDER BY appl.application_id;
796
797 l_cur_credit_rec_exists cur_credit_rec_exists%ROWTYPE;
798 l_cur_wav_credit_tran_exists cur_waiver_credit_tran_exists%ROWTYPE;
799 l_n_unapplied_amount NUMBER;
800
801 BEGIN
802 log_to_fnd(p_v_module => 'get_waiver_reversal_amount',
803 p_v_string => 'Source Credit Id ' || p_n_source_credit_id);
804
805 --Check for waiver credit record transaction
806 OPEN cur_credit_rec_exists(cp_n_source_credit_id => p_n_source_credit_id);
807 FETCH cur_credit_rec_exists INTO l_cur_credit_rec_exists;
808 IF cur_credit_rec_exists%NOTFOUND THEN
809 CLOSE cur_credit_rec_exists;
810 RETURN 0;
811 END IF; --End of check for the Cursor cur_credit_rec_exists
812 CLOSE cur_credit_rec_exists;
813
814 log_to_fnd(p_v_module => 'get_waiver_reversal_amount',
815 p_v_string => 'After the Check whether the Credit Record Exists');
816
817 --Check whether the Status of waiver credit is reversed if so return 0
818 IF l_cur_credit_rec_exists.status = 'REVERSED' THEN
819 RETURN 0;
820 END IF;
821
822 l_n_unapplied_amount := l_cur_credit_rec_exists.unapplied_amount;
823 --Looping across the application record to sum the unapplied amount value for the waiver credit Transaction.
824 FOR l_cur_wav_credit_tran_exists IN cur_waiver_credit_tran_exists( cp_n_source_credit_id => p_n_source_credit_id)
825 LOOP
826 l_n_unapplied_amount := l_n_unapplied_amount + NVL(l_cur_wav_credit_tran_exists.amount_applied,0);
827 END LOOP;
828 log_to_fnd(p_v_module => 'get_waiver_reversal_amount',
829 p_v_string => 'Before returning the unapplied Amount value' || l_n_unapplied_amount);
830 RETURN l_n_unapplied_amount;
831
832 END get_waiver_reversal_amount;
833
834 FUNCTION check_stdnt_wav_assignment(p_n_person_id IN hz_parties.party_id%TYPE,
835 p_v_fee_type IN igs_fi_f_typ_ca_inst.fee_type%TYPE,
836 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
837 p_n_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) RETURN BOOLEAN IS
838 /******************************************************************
839 Created By : Umesh Udayaprakash
840 Date Created By : 7/4/2005
841 Purpose : Invokes Charges API for creating a charge
842 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
843 Known limitations,enhancements,remarks:
844 Change History
845 Who When What
846 ******************************************************************/
847 --Cursor to Check whether a waiver Record Exists
848 CURSOR cur_waiver_pgm_rec_exists(cp_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
849 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
850 cp_n_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
851 SELECT fwp.fee_cal_type,
852 fwp.fee_ci_sequence_number,
853 fwp.waiver_name,
854 fwp.waiver_method_code
855 FROM igs_fi_waiver_pgms fwp
856 WHERE fwp.target_fee_type = cp_v_fee_type
857 AND fwp.fee_cal_type = cp_v_fee_cal_type
858 AND fwp.fee_ci_sequence_number = cp_n_fee_ci_seq_number
859 AND fwp.waiver_method_code = 'COMP_RULE';
860 --Cursor to Check whether student Assignment exists for waiver Record
861 CURSOR cur_wav_stud_assg_rec_exists(cp_n_person_id hz_parties.party_id%TYPE,
862 cp_v_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE,
863 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
864 cp_n_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
865 SELECT fwsp.waiver_name,
866 fwsp.person_id,
867 fwsp.assignment_status_code
868 FROM igs_fi_wav_std_pgms fwsp
869 WHERE fwsp.fee_cal_type = cp_v_fee_cal_type
870 AND fwsp.fee_ci_sequence_number = cp_n_fee_ci_seq_number
871 AND fwsp.waiver_name = cp_v_waiver_name
872 AND fwsp.person_id = cp_n_person_id
873 AND fwsp.assignment_status_code = 'ACTIVE';
874
875 l_cur_waiver_pgm_rec_exists cur_waiver_pgm_rec_exists%ROWTYPE;
876 l_cur_wav_stud_assg_rec_exists cur_wav_stud_assg_rec_exists%ROWTYPE;
877 l_b_return_flag BOOLEAN;
878 BEGIN
879 l_b_return_flag := FALSE;
880 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
881 p_v_string => 'Before looping the IGS_FI_WAIVER_PGMS table for waiver programs');
882 /**
883 Logging of all the Input Parameters
884 */
885 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
886 p_v_string => 'Person Id ' || p_n_person_id);
887 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
888 p_v_string => 'Fee Type ' || p_v_fee_type);
889 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
890 p_v_string => 'Fee Calendar Type ' || p_v_fee_cal_type);
891 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
892 p_v_string => 'Fee Sequence Number ' || p_n_fee_ci_seq_number);
893
894 --Looping across the waiver programs for the feetyep,feecaltype and sequence number
895 FOR l_cur_waiver_pgm_rec_exists IN cur_waiver_pgm_rec_exists ( cp_v_fee_type => p_v_fee_type,
896 cp_v_fee_cal_type => p_v_fee_cal_type,
897 cp_n_fee_ci_seq_number => p_n_fee_ci_seq_number)
898 LOOP
899 --if the waiver methos_code is not Comp_rule return from the function.
900 IF l_cur_waiver_pgm_rec_exists.waiver_method_code <> 'COMP_RULE' THEN
901 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
902 p_v_string => 'Waiver Method Code While Existing ' || l_cur_waiver_pgm_rec_exists.waiver_method_code);
903 l_b_return_flag := FALSE;
904 EXIT;
905 END IF;
906 --Check whether the Student Waiver program Assignment is active if so return True from the function
907 OPEN cur_wav_stud_assg_rec_exists( cp_n_person_id => p_n_person_id,
908 cp_v_waiver_name => l_cur_waiver_pgm_rec_exists.waiver_name,
909 cp_v_fee_cal_type => p_v_fee_cal_type,
910 cp_n_fee_ci_seq_number => p_n_fee_ci_seq_number);
911 FETCH cur_wav_stud_assg_rec_exists INTO l_cur_wav_stud_assg_rec_exists;
912 IF cur_wav_stud_assg_rec_exists%FOUND THEN
913 l_b_return_flag := TRUE;
914 CLOSE cur_wav_stud_assg_rec_exists;
915 EXIT;
916 END IF; -- END Of the check for the waiver student assignment cursor.
917 CLOSE cur_wav_stud_assg_rec_exists;
918 END LOOP;
919 log_to_fnd(p_v_module => 'check_stdnt_wav_assignment',
920 p_v_string => 'After the looping Logic to Find a Active Student Assignment');
921 RETURN l_b_return_flag;
922 END check_stdnt_wav_assignment;
923
924
925 FUNCTION check_fee_type(p_v_fee_type IN igs_fi_f_typ_ca_inst.fee_type%TYPE,
926 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
927 p_n_dest_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) RETURN BOOLEAN IS
928 /******************************************************************
929 Created By : Umesh Udayaprakash
930 Date Created By : 7/4/2005
931 Purpose : Function Used in the Rollover page to check whether the
932 Fee Type is valid or not.
933 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
934 Known limitations,enhancements,remarks:
935 Change History
936 Who When What
937 ******************************************************************/
938 --Cursor to check whether the fee type records exists in the Destination Calendar passed.
939 CURSOR cur_check_fee_type(cp_v_fee_type igs_fi_f_typ_ca_inst.fee_type%TYPE,
940 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
941 cp_n_dest_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
942 SELECT 'X'
943 FROM igs_fi_f_typ_ca_inst_all
944 WHERE fee_type = cp_v_fee_type
945 AND fee_cal_type = cp_v_fee_cal_type
946 AND fee_ci_sequence_number = cp_n_dest_fee_ci_seq_number;
947
948 l_cur_check_fee_type cur_check_fee_type%ROWTYPE;
949
950 BEGIN
951 OPEN cur_check_fee_type(cp_v_fee_type => p_v_fee_type,
952 cp_v_fee_cal_type => p_v_fee_cal_type,
953 cp_n_dest_fee_ci_seq_number => p_n_dest_fee_ci_seq_number);
954 FETCH cur_check_fee_type INTO l_cur_check_fee_type;
955 IF cur_check_fee_type%NOTFOUND THEN
956 CLOSE cur_check_fee_type;
957 RETURN FALSE;
958 END IF;
959 CLOSE cur_check_fee_type;
960 RETURN TRUE;
961 END check_fee_type;
962
963
964 PROCEDURE roll_over_wav_assign(p_rollover_rowid IN VARCHAR2,
965 p_v_stud_rollover_flag IN VARCHAR2,
966 p_n_dest_fee_ci_seq_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
967 p_v_rollover_status OUT NOCOPY VARCHAR2) IS
968 /******************************************************************
969 Created By : Umesh Udayaprakash
970 Date Created By : 7/4/2005
971 Purpose : Procedure for rolling the Waiver program and Student assignment.
972 Created as part of FI234 - Tuition Waivers enh. Bug # 3392095
973 Known limitations,enhancements,remarks:
974 Change History
975 Who When What
976 ******************************************************************/
977 --Cursor to select the waiver program to be rolled over
978 CURSOR cur_waiver_pgms (cp_rollover_rowid VARCHAR2) IS
979 SELECT pgms.*
980 FROM igs_fi_waiver_pgms pgms
981 WHERE pgms.rowid = cp_rollover_rowid;
982
983 --Cursor to select the prereq waiver programs of the waiver program to be rolled over.
984 CURSOR cur_pre_req_wav_programs(cp_v_waiver_name igs_fi_wav_pr_preqs.sub_waiver_name%TYPE,
985 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
986 cp_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
987 SELECT sup_waiver_name,
988 sub_waiver_name,
989 fee_cal_type,
990 fee_ci_sequence_number
991 FROM igs_fi_wav_pr_preqs
992 WHERE sub_waiver_name = cp_v_waiver_name
993 AND fee_cal_type = cp_v_fee_cal_type
994 AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number;
995 --Cursor to Check whether the prereq program already exists in the destincation calendar
996 CURSOR cur_prereq_wav_prgms_exist(cp_v_waiver_name igs_fi_wav_pr_preqs.sub_waiver_name%TYPE,
997 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
998 cp_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
999 SELECT 'x'
1000 FROM igs_fi_waiver_pgms
1001 WHERE waiver_name = cp_v_waiver_name
1002 AND fee_cal_type = cp_v_fee_cal_type
1003 AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number;
1004
1005 --Cursor to select the Student waiver Assignment for Rolling over.
1006 CURSOR cur_stud_waiver_assign(cp_v_waiver_name igs_fi_wav_pr_preqs.sub_waiver_name%TYPE,
1007 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
1008 cp_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
1009 SELECT stuwavpgm.*
1010 FROM igs_fi_wav_std_pgms stuwavpgm
1011 WHERE stuwavpgm.fee_cal_type = cp_v_fee_cal_type
1012 AND stuwavpgm.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
1013 AND stuwavpgm.waiver_name = cp_v_waiver_name
1014 AND stuwavpgm.assignment_status_code ='ACTIVE';
1015
1016 l_cur_waiver_pgms cur_waiver_pgms%ROWTYPE;
1017 l_cur_pre_req_wav_programs cur_pre_req_wav_programs%ROWTYPE;
1018 l_cur_prereq_wav_prgms_exist cur_prereq_wav_prgms_exist%ROWTYPE;
1019 l_cur_stud_waiver_assign cur_stud_waiver_assign%ROWTYPE;
1020 l_waiver_relation_id igs_fi_wav_pr_preqs.waiver_relation_id%TYPE;
1021 l_waiver_student_id igs_fi_wav_std_pgms.waiver_student_id%TYPE;
1022 l_waiver_pgm_found_flag BOOLEAN;
1023 e_expected_error EXCEPTION;
1024 l_rowid ROWID;
1025
1026 BEGIN
1027 SAVEPOINT sp_roll_over_waiver;
1028 l_waiver_pgm_found_flag := FALSE;
1029 /**
1030 Logging of all the Input Parameters
1031 */
1032 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1033 p_v_string => 'Rollover RowId ' || p_rollover_rowid);
1034 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1035 p_v_string => 'Student Rollover Flag ' || p_v_stud_rollover_flag);
1036 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1037 p_v_string => 'Destination Fee Sequence Number ' || p_n_dest_fee_ci_seq_number);
1038
1039 --Select the Waiver program data to be rolled over
1040 OPEN cur_waiver_pgms( cp_rollover_rowid => p_rollover_rowid);
1041 FETCH cur_waiver_pgms INTO l_cur_waiver_pgms;
1042
1043 IF cur_waiver_pgms%NOTFOUND THEN
1044 CLOSE cur_waiver_pgms;
1045 RAISE e_expected_error;
1046 END IF;
1047 CLOSE cur_waiver_pgms;
1048 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1049 p_v_string => 'After selecting the Waiver Program for Rollover');
1050 --Check whether the Adjustment fee Type is already rolled over to the destination calendar
1051 IF l_cur_waiver_pgms.adjustment_fee_type IS NOT NULL THEN
1052 IF NOT check_fee_type(p_v_fee_type => l_cur_waiver_pgms.adjustment_fee_type,
1053 p_v_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
1054 p_n_dest_fee_ci_seq_number => p_n_dest_fee_ci_seq_number) THEN
1055 RAISE e_expected_error;
1056 END IF;
1057 END IF;
1058 --Check whether the target fee Type is already rolled over to the destination calendar
1059 IF l_cur_waiver_pgms.target_fee_type IS NOT NULL THEN
1060 IF NOT check_fee_type(p_v_fee_type => l_cur_waiver_pgms.target_fee_type,
1061 p_v_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
1062 p_n_dest_fee_ci_seq_number => p_n_dest_fee_ci_seq_number) THEN
1063 RAISE e_expected_error;
1064 END IF;
1065 END IF;
1066 --Check whether the rule fee type is already rolled over to the destination calendar
1067 IF (l_cur_waiver_pgms.rule_fee_type IS NOT NULL AND l_cur_waiver_pgms.waiver_method_code = 'COMP_RULE') THEN
1068 IF NOT check_fee_type(p_v_fee_type => l_cur_waiver_pgms.rule_fee_type,
1069 p_v_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
1070 p_n_dest_fee_ci_seq_number => p_n_dest_fee_ci_seq_number) THEN
1071 RAISE e_expected_error;
1072 END IF;
1073 END IF;
1074 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1075 p_v_string => 'After The Check for the Fee Types Validation');
1076 --Rollover the waiver program to the destination calendar.
1077 l_rowid := null;
1078 igs_fi_waiver_pgms_pkg.insert_row(x_rowid => l_rowid,
1079 x_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
1080 x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
1081 x_waiver_name => l_cur_waiver_pgms.waiver_name,
1082 x_waiver_desc => l_cur_waiver_pgms.waiver_desc,
1083 x_waiver_status_code => l_cur_waiver_pgms.waiver_status_code,
1084 x_credit_type_id => l_cur_waiver_pgms.credit_type_id,
1085 x_adjustment_fee_type => l_cur_waiver_pgms.adjustment_fee_type,
1086 x_target_fee_type => l_cur_waiver_pgms.target_fee_type,
1087 x_waiver_method_code => l_cur_waiver_pgms.waiver_method_code,
1088 x_waiver_mode_code => l_cur_waiver_pgms.waiver_mode_code,
1089 x_waiver_criteria_code => l_cur_waiver_pgms.waiver_criteria_code,
1090 x_waiver_percent_alloc => l_cur_waiver_pgms.waiver_percent_alloc,
1091 x_rule_fee_type => l_cur_waiver_pgms.rule_fee_type);
1092 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1093 p_v_string => 'Before the Validation of Pre Requisite Waiver Programs');
1094 --Code logic to identify and Rollover the Pre waiver programs
1095 FOR l_cur_pre_req_wav_programs IN cur_pre_req_wav_programs(cp_v_waiver_name =>l_cur_waiver_pgms.waiver_name,
1096 cp_v_fee_cal_type =>l_cur_waiver_pgms.fee_cal_type,
1097 cp_n_fee_ci_sequence_number =>l_cur_waiver_pgms.fee_ci_sequence_number)
1098 LOOP
1099 --Check whether the pre req waiver program has been rolled over to the Destination Fee calendar.
1100 OPEN cur_prereq_wav_prgms_exist( cp_v_waiver_name =>l_cur_pre_req_wav_programs.sup_waiver_name,
1101 cp_v_fee_cal_type =>l_cur_pre_req_wav_programs.fee_cal_type,
1102 cp_n_fee_ci_sequence_number =>p_n_dest_fee_ci_seq_number );
1103
1104 FETCH cur_prereq_wav_prgms_exist INTO l_cur_prereq_wav_prgms_exist;
1105 --If the pre req waiver program has not been rolled over to the Destination Fee calendar stop further processing after Raising the Exception.
1106 IF cur_prereq_wav_prgms_exist%NOTFOUND THEN
1107 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1108 p_v_string => 'Identified preqWaiverprogram not been Rolled over to the Destination Calendar' || l_cur_pre_req_wav_programs.sup_waiver_name);
1109 CLOSE cur_prereq_wav_prgms_exist;
1110 RAISE e_expected_error;
1111 END IF;
1112 CLOSE cur_prereq_wav_prgms_exist;
1113 l_rowid := NULL;
1114 l_waiver_relation_id := NULL;
1115 --Insert the PRe req Waiver program to the destination calendar in the waiver pre req table.
1116 igs_fi_wav_pr_preqs_pkg.insert_row(x_rowid => l_rowid,
1117 x_waiver_relation_id => l_waiver_relation_id,
1118 x_fee_cal_type => l_cur_pre_req_wav_programs.fee_cal_type,
1119 x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
1120 x_sup_waiver_name => l_cur_pre_req_wav_programs.sup_waiver_name,
1121 x_sub_waiver_name => l_cur_pre_req_wav_programs.sub_waiver_name,
1122 x_mode => 'R');
1123
1124 END LOOP;
1125
1126 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1127 p_v_string => 'After the Validation of Pre Requisite Waiver Programs');
1128 --Check whether the student Asssignment Rollover has been selected.
1129 IF p_v_stud_rollover_flag = 'Y' THEN
1130
1131 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1132 p_v_string => 'Before Rolling over the Student Waiver Assignment' || l_cur_waiver_pgms.waiver_name);
1133 --Loop to identify the Active student assignments for the waiver program
1134 FOR l_cur_stud_waiver_assign IN cur_stud_waiver_assign(cp_v_waiver_name => l_cur_waiver_pgms.waiver_name,
1135 cp_v_fee_cal_type => l_cur_waiver_pgms.fee_cal_type,
1136 cp_n_fee_ci_sequence_number => l_cur_waiver_pgms.fee_ci_sequence_number)
1137 LOOP
1138 --Insert the Student Assignment to the Destination fee calendar.
1139 l_rowid := null;
1140 l_waiver_student_id := null;
1141 igs_fi_wav_std_pgms_pkg.insert_row( x_rowid => l_rowid,
1142 x_waiver_student_id => l_waiver_student_id,
1143 x_fee_cal_type => l_cur_stud_waiver_assign.fee_cal_type,
1144 x_fee_ci_sequence_number => p_n_dest_fee_ci_seq_number,
1145 x_waiver_name => l_cur_stud_waiver_assign.waiver_name,
1146 x_person_id => l_cur_stud_waiver_assign.person_id,
1147 x_assignment_status_code => l_cur_stud_waiver_assign.assignment_status_code,
1148 x_mode => 'R');
1149
1150 END LOOP;
1151 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1152 p_v_string => 'After Rolling over the Student Waiver Assignment' || l_cur_waiver_pgms.waiver_name);
1153 END IF;
1154 p_v_rollover_status := 'S';
1155 COMMIT;
1156 EXCEPTION
1157 WHEN e_expected_error THEN
1158 ROLLBACK TO sp_roll_over_waiver;
1159 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1160 p_v_string => 'Returing from the Expected Error Section' );
1161 p_v_rollover_status := 'E';
1162 WHEN OTHERS THEN
1163 ROLLBACK TO sp_roll_over_waiver;
1164 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1165 p_v_string => 'Returing from the When Others Error Section' );
1166 p_v_rollover_status := 'E';
1167 END roll_over_wav_assign;
1168
1169 PROCEDURE update_wav_assign_status(
1170 p_v_fee_cal_type IN VARCHAR2,
1171 p_n_fee_ci_seq_number IN NUMBER,
1172 p_v_waiver_name IN VARCHAR2,
1173 p_v_new_status IN VARCHAR2,
1174 x_return_status OUT NOCOPY VARCHAR2) AS
1175 /******************************************************************
1176 Created By : Anji Yedubati
1177 Date Created By : 11-JUL-2005
1178 Purpose : To update the Student Waiver Assignment Status
1179 Created as part of Tuition Waivers Enhancment Bug # 3392095
1180
1181 Known limitations,enhancements,remarks:
1182
1183 Change History :
1184 WHO WHEN WHAT
1185 ***************************************************************** */
1186
1187 CURSOR stdnt_wav_assgn_cur (
1188 cp_fee_cal_type igs_fi_wav_std_pgms.fee_cal_type%TYPE,
1189 cp_fee_ci_seq_number igs_fi_wav_std_pgms.fee_ci_sequence_number%TYPE,
1190 cp_waiver_name igs_fi_wav_std_pgms.waiver_name%TYPE,
1191 cp_new_status igs_fi_wav_std_pgms.assignment_status_code%TYPE) IS
1192 SELECT fwsp.*, fwsp.ROWID
1193 FROM igs_fi_wav_std_pgms fwsp
1194 WHERE fee_cal_type = cp_fee_cal_type
1195 AND fee_ci_sequence_number = cp_fee_ci_seq_number
1196 AND waiver_name = cp_waiver_name
1197 AND assignment_status_code <> cp_new_status;
1198
1199 BEGIN
1200
1201 x_return_status := 'S';
1202 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1203 p_v_string => 'Before the stdnt_wav_assgn_rec' );
1204 -- Identify the Student Waiver Assignments
1205 FOR stdnt_wav_assgn_rec IN stdnt_wav_assgn_cur(p_v_fee_cal_type,p_n_fee_ci_seq_number,p_v_waiver_name,p_v_new_status) LOOP
1206
1207 BEGIN
1208 --Callout to update the student Waiver Assignment Status.
1209 igs_fi_wav_std_pgms_pkg.update_row(
1210 x_rowid => stdnt_wav_assgn_rec.ROWID,
1211 x_waiver_student_id => stdnt_wav_assgn_rec.waiver_student_id,
1212 x_fee_cal_type => stdnt_wav_assgn_rec.fee_cal_type,
1213 x_fee_ci_sequence_number => stdnt_wav_assgn_rec.fee_ci_sequence_number,
1214 x_waiver_name => stdnt_wav_assgn_rec.waiver_name,
1215 x_person_id => stdnt_wav_assgn_rec.person_id,
1216 x_assignment_status_code => p_v_new_status,
1217 x_mode => 'R');
1218
1219 EXCEPTION
1220
1221 WHEN OTHERS THEN
1222
1223 x_return_status := 'E';
1224 log_to_fnd(p_v_module => 'update_wav_assign_status.exception',
1225 p_v_string => 'sqlerrm ' || SQLERRM );
1226 -- Log the SQLERRM message and return to the calling Procedure
1227 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
1228 fnd_log.string(fnd_log.level_exception,'igs.plsql.update_wav_assign_status.exception','sqlerrm ' || SQLERRM);
1229 END IF;
1230
1231 END;
1232
1233 END LOOP;
1234 log_to_fnd(p_v_module => 'roll_over_wav_assign',
1235 p_v_string => 'Returing from the When Others Error Section' );
1236 END update_wav_assign_status;
1237
1238 FUNCTION check_chg_error_account ( p_n_person_id IN hz_parties.party_id%TYPE,
1239 p_v_fee_type IN igs_fi_fee_type_all.fee_type%TYPE,
1240 p_v_fee_cal_type IN igs_ca_inst_all.cal_type%TYPE,
1241 p_n_fee_ci_seq_number IN igs_ca_inst_all.sequence_number%TYPE
1242 ) RETURN NUMBER AS
1243 ------------------------------------------------------------------
1244 --Created by : Sanil Madathil, Oracle IDC
1245 --Date created: 28 October 2005
1246 --
1247 -- Purpose:
1248 -- Invoked : from create waiver routine , public APi
1249 -- Function : public procedure to verify if charge
1250 -- transactions exists with error account "Y"
1251 -- Parameters : p_v_fee_cal_type : IN parameter. Required.
1252 -- p_n_fee_ci_seq_number : IN parameter. Required.
1253 -- p_v_fee_type : IN parameter. Required.
1254 -- p_n_person_id : IN parameter. Required.
1255 --
1256 --
1257 --Known limitations/enhancements and/or remarks:
1258 --
1259 --Change History:
1260 --Who When What
1261 ------------------------------------------------------------------
1262 -- Cursor for checking for Error Transactions
1263 CURSOR cur_chg(cp_n_person_id igs_fi_inv_int.person_id%TYPE,
1264 cp_v_fee_type igs_fi_inv_int.fee_type%TYPE,
1265 cp_v_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE,
1266 cp_n_fee_ci_seq igs_fi_inv_int.fee_ci_sequence_number%TYPE) IS
1267 SELECT 'x'
1268 FROM igs_fi_inv_int inv,
1269 igs_fi_invln_int invln
1270 WHERE inv.person_id = cp_n_person_id
1271 AND inv.fee_type = cp_v_fee_type
1272 AND inv.fee_cal_type = cp_v_fee_cal_type
1273 AND inv.fee_ci_sequence_number = cp_n_fee_ci_seq
1274 AND inv.invoice_id = invln.invoice_id
1275 AND invln.error_account = 'Y';
1276
1277 l_rec_chg cur_chg%ROWTYPE;
1278 BEGIN
1279 OPEN cur_chg(
1280 cp_n_person_id => p_n_person_id,
1281 cp_v_fee_type => p_v_fee_type,
1282 cp_v_fee_cal_type => p_v_fee_cal_type,
1283 cp_n_fee_ci_seq => p_n_fee_ci_seq_number
1284 );
1285 FETCH cur_chg INTO l_rec_chg;
1286 IF cur_chg%FOUND THEN
1287 CLOSE cur_chg;
1288 RETURN 1;
1289 END IF;
1290 CLOSE cur_chg;
1291 RETURN 0;
1292 END check_chg_error_account;
1293
1294 END igs_fi_wav_utils_002;