1 PACKAGE BODY igs_fi_ss_acct_payment AS
2 /* $Header: IGSFI63B.pls 120.7 2006/06/27 14:14:35 skharida ship $ */
3 ------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --skharida 26-Jun-2006 Bug 5208136: Modified finp_decline_Optional_fee and finp_set_optional_fee_flag procedures
7 -- removed the obseleted columns of IGS_FI_INV_INT_ALL.
8 --gurprsin 6-Dec-2005 Bug 4735807, Modified the finp_calc_fees_todo method, Added a condition to skip the addition of message onto the stack as it is already added in
9 -- finp_ins_enr_fee_ass method for the case ' if no fee category is attached to the SPA.'. Otherwise a null message
10 -- will be added and shown on the SS page.
11 --gurprsin 13-Sep-2005 Bug 3765876, Modified the Cursor c_get_msg_text select statement to add userenv. language condition with application id
12 --bannamal 26-Aug-2005 Enh#3392095 Tuition Waiver Build. Modified the call to finp_ins_enr_fee_ass
13 -- to include two new parameters.
14 --svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
15 -- Impact of Charges API version Number change
16 -- Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
17 --pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
18 -- parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
19 -- svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number.
20 -- changed logic for credit_number in procedure create_cc_credit.
21 --pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
22 -- Modified update_cc_credit() - added param x_source_invoice_id
23 -- in call to igs_fi_credits_pkg.update_row()
24 --schodava 21-Jan-2004 Bug # 3062706 - Modified procedure finp_calc_fees_todo
25 --vvutukur 04-Dec-2003 Bug#3249288.Modified proceudure finp_set_optional_fee_flag.
26 --pathipat 06-Nov-2003 Enh 3117341 - Audit and Special Fees TD
27 -- Modified finp_calc_fees_todo()
28 --vvutukur 26-Sep-2003 Enh#3045007.Payment Plans Build.Changes specific to TD.
29 --pathipat 19-Aug-2003 Enh 3076768 - Automatic Release of Holds build
30 -- Modified create_cc_credit
31 --smadathi 01-jul-2003 Bug 3029782. Modified the procedure create_cc_credit.
32 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified create_cc_credit,update_cc_credit.
33 --schodava 16-Jun-2003 Enh 2381587 - Credit Card Fund Transfer Build.
34 -- Modified procedures create_cc_credit, update_cc_credit
35 --pathipat 04-Jun-2003 Enh. 2831584- SS Enhancements build
36 -- Modified procedures create_cc_credit() and finp_set_optional_fee_flag()
37 --smadathi 03-jun-2002 Enh. Bug 2831584. Added new function get_msg_text
38 --smadathi 28-MAY-2003 Bug 2849142. Modified procedure finp_calc_fees_todo
39 --shtatiko 09-APR-2003 Enh# 2831554, modified procedure create_cc_credit
40 --vchappid 14-Mar-2003 Bug#2849142,In procedure finp_calc_fees_todo, Only one error message has to be shown
41 -- to the user when any error occurs.
42 --schodava 7-Jan-2003 Bug # 2662124. SQLs using literals is against performance standards.
43 -- Modified cursor c_credit_inst_cd to use cursor parameters.
44 --vvutukur 13-Dec-2002 Enh#2584741.Modified procedures create_cc_credit,update_cc_credit.
45 --vchappid 02-Dec-2002 Enh#2584986, NOCOPY is manually added for x_return_status parameter in procedure update_cc_credit
46 --vvutukur 15-Nov-2002 Enh#2584986.Modified procedures finp_decline_Optional_fee,create_cc_credit,
47 -- finp_set_optional_fee_flag,update_cc_credit.
48 --smadathi 06-Nov-2002 Enh. Bug 2584986. Removed procedure create_posting_int. Modified
49 -- finp_calc_fees_todo procedure .
50 --vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id from CREATE_CC_CREDIT,
51 -- UPDATE_CC_CREDIT,FINP_DECLINE_OPTIONAL_FEE,FINP_SET_OPTIONAL_FEE_FLAG.
52 -- In finp_calc_fees_todo procedure,removed DEFAULT from procedure parameters
53 -- to avoid gscc warning.
54 --smadathi 24-Jun-2002 Bug 2404720. Procedure create_cc_credit Modified.
55 --smadathi 25-Mar-2002 Bug 2280971. Added new procedure finp_calc_fees_todo
56 --jbegum 25 Feb 02 As part of Enh bug #2238226
57 -- Modified the local procedure create_posting_int
58 -- Removed the following parameters:
59 -- p_batch_name,p_source_transaction_id,p_source_transaction_type,
60 -- p_status, x_result
61 -- Added the following parameters:
62 -- p_person_number,p_adm_appl_number,x_return_status,
63 -- x_msg_count,x_msg_data
64 -- Also added the local procedure lookup_desc
65 ------------------------------------------------------------------
66
67
68 FUNCTION lookup_desc( l_type in igs_lookups_view.lookup_type%TYPE ,
69 l_code in igs_lookups_view.lookup_code%TYPE
70 )RETURN VARCHAR2 IS
71 /******************************************************************
72 Created By : jbegum
73 Date Created By : 25-FEB-2002
74 Purpose : Local Function Returns the meaning for the given lookup code
75
76 Known limitations,
77 enhancements,
78 remarks :
79 Change History
80 Who When What
81 smadathi 13-nov-2002 Bug 2584986. Modified cursor cur_desc
82 select list to fetch meaning from igs_lookup_values
83 ******************************************************************/
84
85 CURSOR cur_desc( x_type igs_lookups_view.lookup_type%TYPE, x_code igs_lookups_view.lookup_code%TYPE ) IS
86 SELECT meaning
87 FROM igs_lookup_values
88 WHERE lookup_type = x_type
89 AND lookup_code = x_code
90 AND (SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
91 AND NVL(end_date_active,SYSDATE))
92 AND enabled_flag = 'Y';
93
94 l_desc igs_lookups_view.meaning%TYPE;
95
96 BEGIN
97 IF l_code IS NULL OR l_type IS NULL THEN
98 RETURN NULL ;
99 ELSE
100 OPEN cur_desc(l_type,l_code);
101 FETCH cur_desc into l_desc ;
102 CLOSE cur_desc ;
103 END IF ;
104 RETURN l_desc ;
105 END lookup_desc;
106
107 PROCEDURE create_cc_credit(
108 p_party_id IN VARCHAR2,
109 p_description IN VARCHAR2,
110 p_amount IN VARCHAR,
111 p_credit_card_code IN VARCHAR2,
112 p_credit_card_holder_name IN VARCHAR2,
113 p_credit_card_number IN VARCHAR2,
114 p_credit_card_expiration_date IN VARCHAR2,
115 p_credit_card_approval_code IN VARCHAR2,
116 p_credit_card_tangible_cd IN VARCHAR2,
117 x_credit_id OUT NOCOPY NUMBER,
118 x_credit_activity_id OUT NOCOPY NUMBER,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2,
122 x_credit_number OUT NOCOPY VARCHAR2,
123 x_transaction_date OUT NOCOPY DATE,
124 p_credit_class IN VARCHAR2
125 ) AS
126 /*************************************************************
127 Created By :samaresh
128 Date Created By : 29-SEP-2001
129 Purpose : This Procedure calls the Create Credits API
130 Know limitations, enhancements or remarks
131 Change History
132
133 Who When What
134 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
135 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
136 svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number.
137 changed logic for credit_number.
138 pmarada 14-sep-2004 Bug 3886740, If there is no active credit type for a credit class then logging a message
139 vvutukur 26-Sep-2003 Enh#3045007.Payment Plans Build.Changes specific to TD.
140 pathipat 19-Aug-2003 Enh 3076768 - Automatic Release of Holds build
141 Added code to check for return status of 'S' from credits api
142 smadathi 01-jul-2003 Bug 3029782. Assigned TRUNC of l_credit_rec.p_transaction_date to out parameter x_transaction_date to
143 remove time component.
144 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters(lockbox_interface_id,batch_name,deposit_date) to the record
145 type variable l_credit_rec before calling credits api.
146 schodava 16-Jun-2003 Enh 2381587 - Credit Card Fund Transfer Build.
147 Modified the spec of the procedure, and call to credits API.
148 pathipat 04-Jun-2003 Enh 2831584 - SS Enhancements Build
149 Added 2 new out parameters x_Credit_number, x_transaction_date
150 shtatiko 09-APR-2003 Enh# 2831554, Replaced the call to Public Credits API with call with
151 Private API with validation level as G_VALID_LEVEL_NONE
152 schodava 7-Jan-2003 Bug # 2662124. SQLs using literals is against performance standards.
153 Modified cursor c_credit_inst_cd to use cursor parameters.
154 vvutukur 13-Dec-2002 Enh#2584741.Deposits Build.Modified the call to credits api to remove p_validation_level
155 parameter and add 3 new parameters p_v_check_number,p_v_source_tran_type,p_v_source_tran_ref_number.
156 vvutukur 15-Nov-2002 Enh#2584986.Modified the call to credits api to pass sysdate to the parameter
157 p_d_gl_date.
158 vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie., from parameters
159 list,from where clause of cursor c_credittype_id,from the call to
160 igs_fi_credits_api_pub.create_credit.
161 smadathi 24-Jun-2002 Bug 2404720. The cursor c_credittype_id select is modified to fetch description column along
162 with credit_type_id column. The call to IGS_FI_CREDITS_API_PUB.Create_Credit has been modified
163 to pass this description value to the formal parameter p_description.
164
165 brajendr 22-FEB-2002 modifed the cursor (c_cc_number)which is used for creating the credit number as per the DLD
166
167 jbegum 20-FEB-2002 Enh bug # 2228910
168 Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
169
170 sykrishn 04-FEB-2002 Modifications due to SFCR020- Changes to call to credits API and changes in igs_fi_credits_pkg.update_row
171 to add the new columns
172 Code related to derivation of credit_source removed since it is going to be passed as null to credits API. - SFCR020 - 2191470
173
174 sarakshi 01-Feb-2002 In the call to the credit's API adding a new parameter p_invoice_id as a part of
175 SFCR003 , bug:2195715.
176
177 masehgal 17-Jan-2002 ENH # 2170429
178 Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
179 ***************************************************************/
180 CURSOR c_credittype_id(cp_v_credit_class igs_fi_cr_types.credit_class%TYPE) IS
181 SELECT credit_type_id , description
182 FROM igs_fi_cr_types
183 WHERE SYSDATE BETWEEN effective_start_date AND NVL(effective_end_date,SYSDATE)
184 AND credit_class = cp_v_credit_class;
185
186
187 CURSOR c_credit_card_code(cp_meaning VARCHAR2) IS
188 SELECT lookup_code
189 FROM igs_lookup_values
190 WHERE lookup_type = 'IGS_CREDIT_CARDS'
191 AND meaning = ''||cp_meaning||'';
192
193 CURSOR c_expiration_date(cp_month_year VARCHAR2) IS
194 SELECT LAST_DAY(TO_DATE('01/' || cp_month_year,'DD/MM/YYYY')) expiry_date
195 FROM dual;
196
197 l_cc_meaning igs_lookup_values.meaning%TYPE;
198 l_credit_type_id c_credittype_id%ROWTYPE;
199 l_credit_card_code c_credit_card_code%ROWTYPE;
200 l_attribute_rec igs_fi_credits_api_pub.attribute_rec_type;
201 l_expiration_date c_expiration_date%ROWTYPE;
202
203 l_v_currency igs_fi_control_all.currency_cd%TYPE;
204 l_v_curr_desc fnd_currencies_tl.name%TYPE;
205 l_v_message_name fnd_new_messages.message_name%TYPE;
206
207 l_b_return_status BOOLEAN;
208 l_credit_rec igs_fi_credit_pvt.credit_rec_type;
209 l_exception EXCEPTION;
210
211 l_v_transaction_date igs_fi_credits_all.transaction_date%TYPE := NULL;
212
213 BEGIN
214 x_credit_id := NULL;
215 x_credit_activity_id :=NULL;
216 x_return_status :=NULL;
217 x_msg_count :=NULL;
218 x_msg_data :=NULL;
219 x_credit_number := NULL;
220 -- Calling Credits API to insert data in the credits table in student finance.
221 l_attribute_rec.p_attribute_category := NULL;
222 l_attribute_rec.p_attribute1 := NULL;
223 l_attribute_rec.p_attribute2 := NULL;
224 l_attribute_rec.p_attribute3 := NULL;
225 l_attribute_rec.p_attribute4 := NULL;
226 l_attribute_rec.p_attribute5 := NULL;
227 l_attribute_rec.p_attribute6 := NULL;
228 l_attribute_rec.p_attribute7 := NULL;
229 l_attribute_rec.p_attribute8 := NULL;
230 l_attribute_rec.p_attribute9 := NULL;
231 l_attribute_rec.p_attribute10 := NULL;
232 l_attribute_rec.p_attribute11 := NULL;
233 l_attribute_rec.p_attribute12 := NULL;
234 l_attribute_rec.p_attribute13 := NULL;
235 l_attribute_rec.p_attribute14 := NULL;
236 l_attribute_rec.p_attribute15 := NULL;
237 l_attribute_rec.p_attribute16 := NULL;
238 l_attribute_rec.p_attribute17 := NULL;
239 l_attribute_rec.p_attribute18 := NULL;
240 l_attribute_rec.p_attribute19 := NULL;
241 l_attribute_rec.p_attribute20 := NULL;
242 -- Fetch the parameters from the respective cursors
243
244 -- Initialize the message stack.
245 fnd_msg_pub.initialize;
246
247 -- Get the Credit Type Id
248 -- Check whether credit id exists or not
249 OPEN c_credittype_id(p_credit_class);
250 FETCH c_credittype_id INTO l_credit_type_id;
251 IF c_credittype_id%NOTFOUND THEN
252 -- Get the lookup meaning
253 l_cc_meaning := IGS_FI_GEN_GL.get_lkp_meaning('IGS_FI_CREDIT_CLASS',p_credit_class);
254 CLOSE c_credittype_id;
255 fnd_message.set_name('IGS','IGS_FI_NO_ACTIVE_CT');
256 fnd_message.set_token('CREDIT_CLASS',l_cc_meaning);
257 fnd_msg_pub.add;
258 RAISE l_exception;
259 END IF;
260 CLOSE c_credittype_id;
261
262 -- Credit Instrument Code
263 -- Validate Credit Instrument Code by calling igs_fi_crdapi_util.validate_igs_lkp function
264 -- This call has been added as part of Enh# 2831554, Internal Credits API Build
265 IF NOT igs_fi_crdapi_util.validate_igs_lkp( p_v_lookup_type => 'IGS_FI_CREDIT_INSTRUMENT', p_v_lookup_code => 'CC' ) THEN
266 fnd_message.set_name ( 'IGS', 'IGS_FI_CAPI_CRD_INSTR_NULL' );
267 fnd_message.set_token ( 'CR_INSTR', 'CC' );
268 fnd_msg_pub.ADD;
269 RAISE l_exception;
270 END IF;
271
272 -- Validate amount. This validation has been added as part of Enh# 2831554, Internal Credits API Build
273 igs_fi_crdapi_util.validate_amount ( p_n_amount => p_amount,
274 p_b_return_status => l_b_return_status,
275 p_v_message_name => l_v_message_name );
276 IF NOT l_b_return_status THEN
277 fnd_message.set_name('IGS', l_v_message_name);
278 IF l_v_message_name = 'IGS_FI_CRD_AMT_NEGATIVE' THEN
279 fnd_message.set_token ( 'CR_AMT', p_amount );
280 END IF;
281 fnd_msg_pub.ADD;
282 RAISE l_exception;
283 END IF;
284
285 --Capture the default currency that is set up in System Options Form.
286 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => l_v_currency,
287 p_v_curr_desc => l_v_curr_desc,
288 p_v_message_name => l_v_message_name
289 );
290 IF l_v_message_name IS NOT NULL THEN
291 fnd_message.set_name('IGS',l_v_message_name);
292 fnd_msg_pub.ADD;
293 RAISE l_exception;
294 END IF;
295
296 -- Credit Card Code
297 OPEN c_credit_card_code(p_credit_card_code);
298 FETCH c_credit_card_code INTO l_credit_card_code;
299 CLOSE c_credit_card_code;
300 -- Credit Card Expiry Date
301 OPEN c_expiration_date(p_credit_card_expiration_date);
302 FETCH c_expiration_date INTO l_expiration_date;
303 CLOSE c_expiration_date;
304
305 -- Validate Expiry Date. This validation has been added as part of Enh# 2831554, Internal Credits API Build
306 IF TRUNC(l_expiration_date.expiry_date) < TRUNC(SYSDATE) THEN
307 fnd_message.set_name('IGS', 'IGS_FI_CRD_EXPDT_INVALID');
308 fnd_message.set_token('EXP_DATE', l_expiration_date.expiry_date);
309 fnd_msg_pub.ADD;
310 RAISE l_exception;
311 END IF;
312
313 -- Replaced the call to Public Credits API with call with Private API with validation level as G_VALID_LEVEL_NONE
314 -- This has been added as part of Enh# 2831554, Internal Credits API.
315 l_credit_rec.p_credit_status := 'CLEARED';
316 l_credit_rec.p_credit_source := NULL;
317 l_credit_rec.p_party_id := TO_NUMBER( p_party_id );
318 l_credit_rec.p_credit_type_id := l_credit_type_id.credit_type_id;
319 l_credit_rec.p_credit_instrument := 'CC';
320 l_credit_rec.p_description := l_credit_type_id.description;
321 l_credit_rec.p_amount := TO_NUMBER( p_amount );
322 l_credit_rec.p_currency_cd := l_v_currency;
323 l_credit_rec.p_exchange_rate := 1;
324 l_credit_rec.p_transaction_date := SYSDATE;
325 l_credit_rec.p_effective_date := SYSDATE;
326 l_credit_rec.p_source_transaction_id := NULL;
327 l_credit_rec.p_receipt_lockbox_number := NULL;
328 l_credit_rec.p_credit_card_code := l_credit_card_code.lookup_code;
329 l_credit_rec.p_credit_card_holder_name := p_credit_card_holder_name;
330 l_credit_rec.p_credit_card_number := p_credit_card_number;
331 l_credit_rec.p_credit_card_expiration_date := l_expiration_date.expiry_date;
332 l_credit_rec.p_credit_card_approval_code := NULL;
333 l_credit_rec.p_invoice_id := NULL;
334 l_credit_rec.p_awd_yr_cal_type := NULL;
335 l_credit_rec.p_awd_yr_ci_sequence_number := NULL;
336 l_credit_rec.p_fee_cal_type := NULL;
337 l_credit_rec.p_fee_ci_sequence_number := NULL;
338 l_credit_rec.p_check_number := NULL;
339 l_credit_rec.p_source_tran_type := NULL;
340 l_credit_rec.p_source_tran_ref_number := NULL;
341 l_credit_rec.p_gl_date := TRUNC(SYSDATE);
342 l_credit_rec.p_v_credit_card_payee_cd := fnd_profile.value('IGS_FI_PAYEE_NAME');
343 l_credit_rec.p_v_credit_card_status_code := 'PENDING';
344 l_credit_rec.p_v_credit_card_tangible_cd := p_credit_card_tangible_cd;
345 l_credit_rec.p_lockbox_interface_id := NULL;
346 l_credit_rec.p_batch_name := NULL;
347 l_credit_rec.p_deposit_date := NULL;
348
349 l_v_transaction_date := TRUNC(l_credit_rec.p_transaction_date);
350
351 igs_fi_credit_pvt.create_credit ( p_api_version => 2.1,
352 p_init_msg_list => fnd_api.g_false, /* Passing False because stack has already been initialized */
353 p_commit => fnd_api.g_false,
354 p_validation_level => fnd_api.g_valid_level_none,
355 x_return_status => x_return_status,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 p_credit_rec => l_credit_rec,
359 p_attribute_record => l_attribute_rec,
360 x_credit_id => x_credit_id,
361 x_credit_activity_id => x_credit_activity_id,
362 x_credit_number => x_credit_number);
363
364
365 -- Pass the following values to the out parameters
366 x_transaction_date := l_v_transaction_date;
367
368 -- If the credit creation was successful, but holds release failed, then
369 -- the return status will be S but message count will be > 0.
370 IF ( x_return_status = 'S' AND x_msg_count <> 0 ) THEN
371 x_msg_data := 'IGS_FI_NO_AUTO_HOLD_REL';
372 RETURN;
373 END IF;
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 x_return_status := 'E';
378 RETURN ;
379 WHEN l_exception THEN
380 x_return_status := 'E';
381 fnd_msg_pub.count_and_get( p_count => x_msg_count,
382 p_data => x_msg_data);
383 WHEN OTHERS THEN
384 x_return_status := 'E';
385 fnd_msg_pub.count_and_get( p_count => x_msg_count,
386 p_data => x_msg_data);
387 RETURN;
388 END create_cc_credit;
389
390
391 PROCEDURE update_cc_credit(
392 p_credit_card_approval_code IN NUMBER,
393 p_credit_id IN NUMBER,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2
397 ) AS
398 /*************************************************************
399 Created By :samaresh
400 Date Created By : 29-SEP-2001
401 Purpose : This Procedure calls the Create Credits API
402 Know limitations, enhancements or remarks
403 Change History
404 Who When What
405 pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
406 pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
407 Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
408 vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new parameters(lockbox_interface_id,batch_name,deposit_date) to the TBH
409 update_row call of credits table.
410 schodava 16-Jun-2003 Enh 2381587 - Credit Card Fund Transfer Build.
411 Modified the Credits TBH update row
412 vvutukur 16-Dec-2002 Enh#2584741.Modified the tbh call to igs_fi_credits_pkg.update_row to add 3 new
413 parameters check_number,source_transaction_type,source_transaction_ref.
414 vchappid 02-Dec-2002 Enh#2584986, NOCOPY is manually added for x_return_status procedure parameter
415 vvutukur 25-Nov-2002 Enh#2584986.Modified the call to igs_fi_credits_pkg.update_row to pass gl_date.
416 vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,from the call to
417 IGS_FI_CREDITS_pkg.Update_Row,
418 ***************************************************************/
419 CURSOR c_credit_dtls(cp_credit_id NUMBER) IS
420 SELECT *
421 FROM igs_fi_credits
422 WHERE credit_id = cp_credit_id;
423
424 l_credit_dtls_rec c_credit_dtls%ROWTYPE;
425 BEGIN
426
427 -- Fetch the values from the igs_fi_credits table for the credit_id
428 -- Credit Source
429 OPEN c_credit_dtls(p_credit_id);
430 FETCH c_credit_dtls INTO l_credit_dtls_rec;
431 CLOSE c_credit_dtls;
432
433 igs_fi_credits_pkg.update_row(x_rowid => l_credit_dtls_rec.row_id,
434 x_credit_id => l_credit_dtls_rec.credit_id,
435 x_credit_number => l_credit_dtls_rec.credit_number,
436 x_status => l_credit_dtls_rec.status,
437 x_credit_source => l_credit_dtls_rec.credit_source,
438 x_party_id => l_credit_dtls_rec.party_id,
439 x_credit_type_id => l_credit_dtls_rec.credit_type_id,
440 x_credit_instrument => l_credit_dtls_rec.credit_instrument,
441 x_description => l_credit_dtls_rec.description,
442 x_amount => l_credit_dtls_rec.amount,
443 x_currency_cd => l_credit_dtls_rec.currency_cd,
444 x_exchange_rate => l_credit_dtls_rec.exchange_rate,
445 x_transaction_date => l_credit_dtls_rec.transaction_date,
446 x_effective_date => l_credit_dtls_rec.effective_date,
447 x_reversal_date => l_credit_dtls_rec.reversal_date,
448 x_reversal_reason_code => l_credit_dtls_rec.reversal_reason_code,
449 x_reversal_comments => l_credit_dtls_rec.reversal_comments,
450 x_unapplied_amount => l_credit_dtls_rec.unapplied_amount,
451 x_source_transaction_id => l_credit_dtls_rec.source_transaction_id,
452 x_receipt_lockbox_number => l_credit_dtls_rec.receipt_lockbox_number,
453 x_merchant_id => l_credit_dtls_rec.merchant_id,
454 x_credit_card_code => l_credit_dtls_rec.credit_card_code,
455 x_credit_card_holder_name => l_credit_dtls_rec.credit_card_holder_name,
456 x_credit_card_number => l_credit_dtls_rec.credit_card_number,
457 x_credit_card_expiration_date => l_credit_dtls_rec.credit_card_expiration_date,
458 x_credit_card_approval_code => TO_CHAR(p_credit_card_approval_code),
459 x_awd_yr_cal_type => l_credit_dtls_rec.awd_yr_cal_type,
460 x_awd_yr_ci_sequence_number => l_credit_dtls_rec.awd_yr_ci_sequence_number,
461 x_fee_cal_type => l_credit_dtls_rec.fee_cal_type,
462 x_fee_ci_sequence_number => l_credit_dtls_rec.fee_ci_sequence_number,
463 x_attribute_category => l_credit_dtls_rec.attribute_category,
464 x_attribute1 => l_credit_dtls_rec.attribute1,
465 x_attribute2 => l_credit_dtls_rec.attribute2,
466 x_attribute3 => l_credit_dtls_rec.attribute3,
467 x_attribute4 => l_credit_dtls_rec.attribute4,
468 x_attribute5 => l_credit_dtls_rec.attribute5,
469 x_attribute6 => l_credit_dtls_rec.attribute6,
470 x_attribute7 => l_credit_dtls_rec.attribute7,
471 x_attribute8 => l_credit_dtls_rec.attribute8,
472 x_attribute9 => l_credit_dtls_rec.attribute9,
473 x_attribute10 => l_credit_dtls_rec.attribute10,
474 x_attribute11 => l_credit_dtls_rec.attribute11,
475 x_attribute12 => l_credit_dtls_rec.attribute12,
476 x_attribute13 => l_credit_dtls_rec.attribute13,
477 x_attribute14 => l_credit_dtls_rec.attribute14,
478 x_attribute15 => l_credit_dtls_rec.attribute15,
479 x_attribute16 => l_credit_dtls_rec.attribute16,
480 x_attribute17 => l_credit_dtls_rec.attribute17,
481 x_attribute18 => l_credit_dtls_rec.attribute18,
482 x_attribute19 => l_credit_dtls_rec.attribute19,
483 x_attribute20 => l_credit_dtls_rec.attribute20,
484 x_gl_date => l_credit_dtls_rec.gl_date,
485 x_check_number => l_credit_dtls_rec.check_number,
486 x_source_transaction_type => l_credit_dtls_rec.source_transaction_type,
487 x_source_transaction_ref => l_credit_dtls_rec.source_transaction_ref,
488 x_credit_card_status_code => l_credit_dtls_rec.credit_card_status_code,
489 x_credit_card_payee_cd => l_credit_dtls_rec.credit_card_payee_cd,
490 x_credit_card_tangible_cd => l_credit_dtls_rec.credit_card_tangible_cd,
491 x_lockbox_interface_id => l_credit_dtls_rec.lockbox_interface_id,
492 x_batch_name => l_credit_dtls_rec.batch_name,
493 x_deposit_date => l_credit_dtls_rec.deposit_date,
494 x_source_invoice_id => l_credit_dtls_rec.source_invoice_id,
495 x_tax_year_code => l_credit_dtls_rec.tax_year_code,
496 x_waiver_name => l_credit_dtls_rec.waiver_name
497 );
498
499
500 x_return_status := 'S';
501 EXCEPTION
502 WHEN NO_DATA_FOUND THEN
503 x_return_status := 'E';
504 RETURN ;
505 WHEN OTHERS THEN
506 x_return_status := 'E';
507 RETURN;
508 END update_cc_credit;
509
510 PROCEDURE finp_decline_Optional_fee(
511 p_invoice_id IN VARCHAR2,
512 p_return_status OUT NOCOPY VARCHAR2,
513 p_message_count OUT NOCOPY NUMBER,
514 p_message_data OUT NOCOPY VARCHAR2
515 )AS
516 /*************************************************************
517 Created By :knaraset
518 Date Created By : 02-OCT-2001
519 Purpose : This Procedure Create Reversal of the charge for the Declined Invoice record
520 And Update Invoice record with optional_fee_flag=>'D'(Declined)
521 Know limitations, enhancements or remarks
522 Change History
523 Who When What
524 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
525 Impact of Charges API version Number change
526 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
527 pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
528 smadathi 03-jun-2002 Enh. Bug 2831584. Modified opening of cursor cur_invheader to pass TO_NUMBER(p_invoice_id))
529 instead of passing l_inv_id. This was changed because the amount due and amount made same
530 by the update row call even after the declining of fees by charges API.
531 vvutukur 15-Nov-2002 Enh#2584986.Passed SYSDATE to the call to charges API for the parameter
532 l_chg_line_tbl(1).p_gl_date.
533 vvutukur 16-Sep-2002 Enh#2564643.Removed references to subaccount_id.ie.,the assignment of
534 L_CHG_REC.P_SUBACCOUNT_ID to L_CUR_INVHEADER.SUBACCOUNT_ID,and from
535 calls to igs_fi_inv_int_pkg.update_row.
536 masehgal 17-Jan-2002 ENH # 2170429
537 Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
538 ***************************************************************/
539 -- Cursor for fetching Invoice Header details
540 CURSOR cur_invheader(cp_invoice_id IGS_FI_INV_INT.Invoice_Id%TYPE) IS
541 SELECT *
542 FROM igs_fi_inv_int
543 WHERE invoice_id = cp_invoice_id;
544 -- Cursor for fetching Invoice Lines details
545 CURSOR cur_invln(p_invoice_id IGS_FI_INV_INT.Invoice_Id%TYPE) IS
546 SELECT *
547 FROM IGS_FI_INVLN_INT
548 WHERE invoice_id = p_invoice_id;
549 l_chg_rec Igs_Fi_Charges_Api_Pvt.Header_Rec_Type;
550 l_chg_line_tbl Igs_Fi_Charges_Api_Pvt.Line_Tbl_Type;
551 l_line_tbl Igs_Fi_Charges_Api_Pvt.Line_Id_Tbl_Type;
552 l_inv_id IGS_FI_INV_INT.Invoice_Id%TYPE;
553 l_msg_data VARCHAR2(2000);
554 l_var NUMBER(5) := 0;
555 l_cur_invheader cur_invheader%ROWTYPE;
556 l_msg VARCHAR2(2000);
557 l_n_waiver_amount NUMBER;
558
559 BEGIN
560 -- Fetch the Invoice Header Details
561 OPEN cur_invheader(TO_NUMBER(p_invoice_id));
562 FETCH cur_invheader INTO l_cur_invheader;
563 CLOSE cur_invheader;
564 -- Assign Invoice Header details to the Record Variable
565 l_chg_rec.p_person_id := l_cur_invheader.person_id;
566 l_chg_rec.p_fee_type := l_cur_invheader.fee_type;
567 l_chg_rec.p_fee_cat := l_cur_invheader.fee_cat;
568 l_chg_rec.p_fee_cal_type := l_cur_invheader.fee_cal_type;
569 l_chg_rec.p_fee_ci_sequence_number := l_cur_invheader.fee_ci_sequence_number;
570 l_chg_rec.p_course_cd := l_cur_invheader.course_cd;
571 l_chg_rec.p_attendance_type := l_cur_invheader.attendance_type;
572 l_chg_rec.p_attendance_mode := l_cur_invheader.attendance_mode;
573 l_chg_rec.p_invoice_amount := -(l_cur_invheader.invoice_amount);
574 l_chg_rec.p_invoice_creation_date := TRUNC(SYSDATE);
575 l_chg_rec.p_invoice_desc := l_cur_invheader.invoice_desc;
576 l_chg_rec.p_transaction_type := l_cur_invheader.transaction_type;
577 l_chg_rec.p_currency_cd := l_cur_invheader.currency_cd;
578 l_chg_rec.p_exchange_rate := l_cur_invheader.exchange_rate;
579 l_chg_rec.p_effective_date := l_cur_invheader.effective_date;
580 l_chg_rec.p_waiver_flag := NULL;
581 l_chg_rec.p_waiver_reason := NULL;
582 l_chg_rec.p_source_transaction_id := l_cur_invheader.invoice_id;
583 FOR invrec IN cur_invln(l_cur_invheader.invoice_id) LOOP
584 l_var := l_var + 1;
585 l_chg_line_tbl(l_var).p_s_chg_method_type := invrec.s_chg_method_type;
586 l_chg_line_tbl(l_var).p_description := invrec.description;
587 l_chg_line_tbl(l_var).p_chg_elements := invrec.chg_elements;
588 l_chg_line_tbl(l_var).p_amount := -(invrec.amount);
589 l_chg_line_tbl(l_var).p_uoo_id := invrec.uoo_id;
590 l_chg_line_tbl(l_var).p_unit_attempt_status := invrec.unit_attempt_status;
591 l_chg_line_tbl(l_var).p_eftsu := invrec.eftsu;
592 l_chg_line_tbl(l_var).p_credit_points := invrec.credit_points;
593 l_chg_line_tbl(l_var).p_org_unit_cd := invrec.org_unit_cd;
594 l_chg_line_tbl(l_var).p_attribute_category := invrec.attribute_category;
595 l_chg_line_tbl(l_var).p_attribute1 := invrec.attribute1;
596 l_chg_line_tbl(l_var).p_attribute2 := invrec.attribute2;
597 l_chg_line_tbl(l_var).p_attribute3 := invrec.attribute3;
598 l_chg_line_tbl(l_var).p_attribute4 := invrec.attribute4;
599 l_chg_line_tbl(l_var).p_attribute5 := invrec.attribute5;
600 l_chg_line_tbl(l_var).p_attribute6 := invrec.attribute6;
601 l_chg_line_tbl(l_var).p_attribute7 := invrec.attribute7;
602 l_chg_line_tbl(l_var).p_attribute8 := invrec.attribute8;
603 l_chg_line_tbl(l_var).p_attribute9 := invrec.attribute9;
604 l_chg_line_tbl(l_var).p_attribute10 := invrec.attribute10;
605 l_chg_line_tbl(l_var).p_attribute11 := invrec.attribute11;
606 l_chg_line_tbl(l_var).p_attribute12 := invrec.attribute12;
607 l_chg_line_tbl(l_var).p_attribute13 := invrec.attribute13;
608 l_chg_line_tbl(l_var).p_attribute14 := invrec.attribute14;
609 l_chg_line_tbl(l_var).p_attribute15 := invrec.attribute15;
610 l_chg_line_tbl(l_var).p_attribute16 := invrec.attribute16;
611 l_chg_line_tbl(l_var).p_attribute17 := invrec.attribute17;
612 l_chg_line_tbl(l_var).p_attribute18 := invrec.attribute18;
613 l_chg_line_tbl(l_var).p_attribute19 := invrec.attribute19;
614 l_chg_line_tbl(l_var).p_attribute20 := invrec.attribute20;
615 l_chg_line_tbl(l_var).p_override_dr_rec_ccid := invrec.rec_gl_ccid;
616 l_chg_line_tbl(l_var).p_override_cr_rev_ccid := invrec.rev_gl_ccid;
617 l_chg_line_tbl(l_var).p_override_dr_rec_account_cd := invrec.rec_account_cd;
618 l_chg_line_tbl(l_var).p_override_cr_rev_account_cd := invrec.rev_account_cd;
619 l_chg_line_tbl(l_var).p_d_gl_date := TRUNC(SYSDATE);
620
621 END LOOP;
622 Igs_Fi_Charges_Api_Pvt.Create_Charge(p_api_version => 2.0,
623 p_init_msg_list => 'T',
624 p_commit => 'F',
625 p_validation_level => 100,
626 p_header_rec => l_chg_rec,
627 p_line_tbl => l_chg_line_tbl,
628 x_invoice_id => l_inv_id,
629 x_line_id_tbl => l_line_tbl,
630 x_return_status => p_return_status,
631 x_msg_count => p_message_count,
632 x_msg_data => p_message_data,
633 x_waiver_amount => l_n_waiver_amount);
634 IF p_return_status <> 'S' THEN
635 /* IF p_message_count = 1 THEN
636 FND_MESSAGE.SET_ENCODED(l_msg_data);
637 p_message_data := FND_MESSAGE.Get;
638 ELSE
639 FOR l_cnt IN 1..p_message_count LOOP
640 l_msg := l_msg||FND_MSG_PUB.Get(p_msg_index => l_cnt,
641 p_encoded => 'T');
642 END LOOP;
643 p_message_data := l_msg;
644 END IF;*/
645 RETURN;
646 ELSE
647 -- Update Invoice record with optional_fee_flag=>'D'(Declined)
648
649 --Change History
650 --Who When What
651 --skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
652 --vvutukur 25-Nov-2002 Enh#2584986.Removed the call to igs_fi_inv_int_pkg.update_row.
653 --vvutukur 16-Sep-2002 Enh#2564643.As part of subaccount removal build,removed parameter
654 -- x_subaccount_id from the following call to igs_fi_inv_int_pkg.update_row.
655 --jbegum 20 feb 02 Enh bug # 2228910
656 -- Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
657 --masehgal 17-Jan-2002 ENH # 2170429
658 -- Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
659
660 -- Fetch the Invoice Header Details for the Reversal record to update optional_fee_flag to 'D'
661 -- So that reversal record won't be available for Decline again
662 OPEN cur_invheader(TO_NUMBER(p_invoice_id));
663 FETCH cur_invheader INTO l_cur_invheader;
664 CLOSE cur_invheader;
665
666 igs_fi_inv_int_pkg.update_row( x_rowid => l_cur_invheader.row_id ,
667 x_invoice_id => l_cur_invheader.invoice_id ,
668 x_person_id => l_cur_invheader.person_id ,
669 x_fee_type => l_cur_invheader.fee_type ,
670 x_fee_cat => l_cur_invheader.fee_cat ,
671 x_fee_cal_type => l_cur_invheader.fee_cal_type ,
672 x_fee_ci_sequence_number => l_cur_invheader.fee_ci_sequence_number ,
673 x_course_cd => l_cur_invheader.course_cd ,
674 x_attendance_mode => l_cur_invheader.attendance_mode ,
675 x_attendance_type => l_cur_invheader.attendance_type ,
676 x_invoice_amount_due => l_cur_invheader.invoice_amount_due ,
677 x_invoice_creation_date => l_cur_invheader.invoice_creation_date ,
678 x_invoice_desc => l_cur_invheader.invoice_desc ,
679 x_transaction_type => l_cur_invheader.transaction_type ,
680 x_currency_cd => l_cur_invheader.currency_cd ,
681 x_status => l_cur_invheader.status ,
682 x_attribute_category => l_cur_invheader.attribute_category ,
683 x_attribute1 => l_cur_invheader.attribute1 ,
684 x_attribute2 => l_cur_invheader.attribute2 ,
685 x_attribute3 => l_cur_invheader.attribute3 ,
686 x_attribute4 => l_cur_invheader.attribute4 ,
687 x_attribute5 => l_cur_invheader.attribute5 ,
688 x_attribute6 => l_cur_invheader.attribute6 ,
689 x_attribute7 => l_cur_invheader.attribute7 ,
690 x_attribute8 => l_cur_invheader.attribute8 ,
691 x_attribute9 => l_cur_invheader.attribute9 ,
692 x_attribute10 => l_cur_invheader.attribute10 ,
693 x_invoice_amount => l_cur_invheader.invoice_amount ,
694 x_bill_id => l_cur_invheader.bill_id ,
695 x_bill_number => l_cur_invheader.bill_number ,
696 x_bill_date => l_cur_invheader.bill_date ,
697 x_waiver_flag => l_cur_invheader.waiver_flag ,
698 x_waiver_reason => l_cur_invheader.waiver_reason ,
699 x_effective_date => l_cur_invheader.effective_date ,
700 x_invoice_number => l_cur_invheader.invoice_number ,
701 x_exchange_rate => l_cur_invheader.exchange_rate ,
702 x_bill_payment_due_date => l_cur_invheader.bill_payment_due_date ,
703 x_optional_fee_flag => 'D' ,
704 x_mode => 'R',
705 x_reversal_gl_date => TRUNC(SYSDATE),
706 x_tax_year_code => l_cur_invheader.tax_year_code,
707 x_waiver_name => l_cur_invheader.waiver_name
708 );
709 END IF;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 p_return_status := 'E';
714 RETURN;
715
716 END finp_decline_Optional_fee;
717
718 PROCEDURE finp_set_optional_fee_flag(
719 p_person_id IN VARCHAR2,
720 p_return_status OUT NOCOPY VARCHAR2,
721 p_message_count OUT NOCOPY NUMBER,
722 p_message_data OUT NOCOPY VARCHAR2
723 ) AS
724 /*************************************************************
725 Created By :knaraset
726 Date Created By : 02-OCT-2001
727 Purpose : This Procedure Updates All Invoice records which are not Declined
728 for the given Student and SubAccountID with optional_fee_flag => 'A'(Accepted)
729 Know limitations, enhancements or remarks
730 Change History
731 Who When What
732 pmarada 26-May-2005 Enh#3020586- added tax year code column as per 1098-t reporting build
733 vvutukur 04-Dec-2003 Bug#3249288.Modified cursor cur_opt_fees to select invoice records having
734 optional fee flag as 'O' without need to look at the value of optional_payment_ind
735 at fee type set up level.
736 pathipat 04-Jun-2003 Enh 2831584 - SS Enhancements Build
737 Modified cursor cur_opt_fees - included join with igs_fi_invln_int_all
738 vvutukur 25-Nov-2002 Enh#2584986.Passed NULL to the newly added parameter x_reversal_gl_date in the call to
739 igs_fi_inv_int_pkg.update_row.
740 vvutukur 16-Sep-2002 Enh#2564643.Removed the references to subaccount_id.ie., from
741 parameters list p_subaccount_id,from cursor cur_opt_fees and from the
742 call to igs_fi_inv_int_pkg.update_row.9/16/02
743 removal build.
744 masehgal 17-Jan-2002 ENH # 2170429
745 Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
746 ***************************************************************/
747
748 -- Cursor to fetch all the Optional fees which are not Declined
749 CURSOR cur_opt_fees IS
750 SELECT inv.invoice_id
751 FROM igs_fi_inv_int_all inv ,
752 igs_fi_invln_int_all invln
753 WHERE invln.invoice_id = inv.invoice_id
754 AND NVL(invln.error_account,'N') = 'N'
755 AND inv.person_id = p_person_id
756 AND inv.optional_fee_flag ='O';
757
758 -- cursor reads from the charges tables
759 CURSOR cur_inv_int(p_invoice_id NUMBER) IS
760 SELECT *
761 FROM igs_fi_inv_int
762 WHERE invoice_id = p_invoice_id;
763 l_cur_inv_int cur_inv_int%ROWTYPE;
764 BEGIN
765 FOR l_cur_opt_fees IN cur_opt_fees LOOP
766 OPEN cur_inv_int(l_cur_opt_fees.invoice_id);
767 FETCH cur_inv_int INTO l_cur_inv_int ;
768
769 --Change History
770 --Who When What
771 --skharida 26-Jun-2006 Bug# 5208136 - Removed the obsoleted columns from the table IGS_FI_INV_INT_ALL
772 --vvutukur 16-Sep-2002 Enh#2564643.As part of subaccount removal build, removed parameter
773 -- x_subaccount_id from the following call to igs_fi_inv_int_pkg.update_row.
774 --jbegum 20 feb 02 Enh bug # 2228910
775 -- Removed the source_transaction_id column from igs_fi_inv_int_pkg.update_row
776 --masehgal 17-Jan-2002 ENH # 2170429
777 -- Obsoletion of SPONSOR_CD from UPDATE_ROW Call to IGS_FI_INV_INT Tablehandler
778
779 Igs_Fi_Inv_Int_Pkg.update_row( x_rowid => l_cur_inv_int.row_id ,
780 x_invoice_id => l_cur_inv_int.invoice_id ,
781 x_person_id => l_cur_inv_int.person_id ,
782 x_fee_type => l_cur_inv_int.fee_type ,
783 x_fee_cat => l_cur_inv_int.fee_cat ,
784 x_fee_cal_type => l_cur_inv_int.fee_cal_type ,
785 x_fee_ci_sequence_number => l_cur_inv_int.fee_ci_sequence_number ,
786 x_course_cd => l_cur_inv_int.course_cd ,
787 x_attendance_mode => l_cur_inv_int.attendance_mode ,
788 x_attendance_type => l_cur_inv_int.attendance_type ,
789 x_invoice_amount_due => l_cur_inv_int.invoice_amount_due ,
790 x_invoice_creation_date => l_cur_inv_int.invoice_creation_date ,
791 x_invoice_desc => l_cur_inv_int.invoice_desc ,
792 x_transaction_type => l_cur_inv_int.transaction_type ,
793 x_currency_cd => l_cur_inv_int.currency_cd ,
794 x_status => l_cur_inv_int.status ,
795 x_attribute_category => l_cur_inv_int.attribute_category ,
796 x_attribute1 => l_cur_inv_int.attribute1 ,
797 x_attribute2 => l_cur_inv_int.attribute2 ,
798 x_attribute3 => l_cur_inv_int.attribute3 ,
799 x_attribute4 => l_cur_inv_int.attribute4 ,
800 x_attribute5 => l_cur_inv_int.attribute5 ,
801 x_attribute6 => l_cur_inv_int.attribute6 ,
802 x_attribute7 => l_cur_inv_int.attribute7 ,
803 x_attribute8 => l_cur_inv_int.attribute8 ,
804 x_attribute9 => l_cur_inv_int.attribute9 ,
805 x_attribute10 => l_cur_inv_int.attribute10 ,
806 x_invoice_amount => l_cur_inv_int.invoice_amount ,
807 x_bill_id => l_cur_inv_int.bill_id ,
808 x_bill_number => l_cur_inv_int.bill_number ,
809 x_bill_date => l_cur_inv_int.bill_date ,
810 x_waiver_flag => l_cur_inv_int.waiver_flag ,
811 x_waiver_reason => l_cur_inv_int.waiver_reason ,
812 x_effective_date => l_cur_inv_int.effective_date ,
813 x_invoice_number => l_cur_inv_int.invoice_number ,
814 x_exchange_rate => l_cur_inv_int.exchange_rate ,
815 x_bill_payment_due_date => l_cur_inv_int.bill_payment_due_date ,
816 x_optional_fee_flag => 'A' ,
817 x_mode => 'R',
818 x_reversal_gl_date => l_cur_inv_int.reversal_gl_date,
819 x_tax_year_code => l_cur_inv_int.tax_year_code,
820 x_waiver_name => l_cur_inv_int.waiver_name
821 );
822 CLOSE cur_inv_int;
823 END LOOP; -- l_cur_opt_fees
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 p_return_status := 'E';
828 RETURN;
829
830 END finp_set_optional_fee_flag;
831
832
833 PROCEDURE finp_calc_fees_todo
834 (
835 p_person_id IN igs_pe_std_todo_ref.person_id%TYPE,
836 p_init_msg_list IN VARCHAR2,
837 p_return_status OUT NOCOPY VARCHAR2,
838 p_message_count OUT NOCOPY NUMBER,
839 p_message_data OUT NOCOPY VARCHAR2
840 ) IS
841 ------------------------------------------------------------------
842 --Created by : Sanil Madathil, Oracle IDC
843 --Date created: 22 Mar 2002
844 --
845 --Purpose:
846 -- Invoked : From Student Acoount History and Payment Page.
847 -- Function : Procedure checks for any pending todo records of type fee_recalc.
848 -- If any entry is found, process calls the fee assessment routine for the FCI
849 -- Parameters : p_person_id : IN parameter. Required. Identifies the person for which ToDo Enteries
850 -- needs to be checked from IGS_PE_STD_TODO_REF table.
851 --
852 -- p_return_status : OUT parmeter.
853 --
854 -- p_message_count : OUT parameter
855 --
856 -- p_message_data : OUT parameter
857 --
858 --Known limitations/enhancements and/or remarks:
859 --
860 --Change History:
861 --Who When What
862 --gurprsin 6-Dec-2005 Bug 4735807, Modified the finp_calc_fees_todo method, Added a condition to skip the addition of message onto the stack as it is already added in
863 -- finp_ins_enr_fee_ass method for the case ' if no fee category is attached to the SPA.'. Otherwise a null message
864 -- will be added and shown on the SS page.
865 --schodava 21-Jan-2004 Bug # 3062706 - Removed the initialization of the variable l_d_creation_dt
866 -- within the loop across the records in the IGS_PE_STD_TODO_REF table.
867 --shtatiko 15-DEC_2003 Bug# 3230754, Replaced AND with OR when checking for uniqueness of FTCI.
868 --pathipat 06-Nov-2003 Enh 3117341 - Audit and Special Fees TD
869 -- Added logic for processing Special Fees after Fee Assessment
870 --smadathi 28-MAY-2003 Bug 2849142. Modified the exceptional handling. Used p_message_data to return message_name
871 -- instead.
872 --knaraset 12-May-2003 Modified cursor c_igs_pe_std_todo_ref to select uoo_id
873 -- also added uoo_id in TBH call to todo_ref, as part of MUS build bug 2829262
874 --vchappid 14-Mar-2003 Bug#2849142, Only one error message has to be shown to the user when any error occurs.
875 --smadathi 13-nov-2002 Added p_d_gl_date parameter to the call igs_fi_prc_fee_ass.finp_ins_enr_fee_ass
876 --vvutukur 17-Sep-2002 Enh#2564643.Removed DEFAULT clause from parameter list to avoid
877 -- gscc warning in order to comply with 9i standards.
878 ------------------------------------------------------------------
879 l_c_rpt_ld_cal_type igs_pe_std_todo_ref.cal_type%TYPE;
880 l_n_rpt_ld_ci_sequence_number igs_pe_std_todo_ref.ci_sequence_number%TYPE;
881 l_c_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE;
882 l_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE;
883 l_c_message_name fnd_new_messages.message_name%TYPE DEFAULT NULL;
884 l_c_message VARCHAR2(32767) DEFAULT NULL;
885 l_d_creation_dt DATE;
886 cst_fee_recalc CONSTANT VARCHAR2(10) := 'FEE_RECALC';
887
888 l_c_msg VARCHAR2(2000);
889 l_c_appln VARCHAR2(10);
890
891 -- Cursor to fetch all the open todo records from the igs_pe_std_todo_ref table for the person that are
892 -- of type 'FEE_RECALC'
893
894 CURSOR c_igs_pe_std_todo_ref( cp_n_person_id igs_pe_std_todo_ref.person_id%TYPE) IS
895 SELECT person_id , s_student_todo_type , sequence_number ,
896 reference_number , cal_type , ci_sequence_number ,
897 course_cd , unit_cd , other_reference ,
898 logical_delete_dt , Created_by , creation_date ,
899 last_updated_by , last_update_date , last_update_login ,
900 request_id , program_application_id , program_id ,
901 program_update_date , rowid,uoo_id
902 FROM igs_pe_std_todo_ref
903 WHERE person_id = cp_n_person_id
904 AND s_student_todo_type = cst_fee_recalc
905 AND logical_delete_dt IS NULL
906 ORDER BY cal_type , ci_sequence_number;
907 -- cursor % rowtype variable
908 rec_c_igs_pe_std_todo_ref c_igs_pe_std_todo_ref%ROWTYPE;
909 -- user defined exception
910 e_resource_busy EXCEPTION;
911 --associating the oracle error number and user defined exception using compiler directive
912 PRAGMA EXCEPTION_INIT(e_resource_busy,-00054);
913
914 l_b_fees_assessed BOOLEAN := TRUE;
915 l_b_recs_found BOOLEAN := FALSE;
916 l_v_ret_status VARCHAR2(1) := NULL;
917 l_n_waiver_amount NUMBER;
918
919 BEGIN
920
921 --Initialize message list if p_init_msg_list is set to TRUE.
922 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
923 FND_MSG_PUB.INITIALIZE;
924 END IF;
925 -- Initialize return status to success
926 p_return_status := FND_API.G_RET_STS_SUCCESS;
927
928 -- Initialize repeat counter for load calendar type and load sequence number
929 l_c_rpt_ld_cal_type := ' ';
930 l_n_rpt_ld_ci_sequence_number := 0;
931
932 --Validating if the mandatory parameter person_id is passed to the process
933 IF (p_person_id IS NULL) THEN
934 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PARAMETER_NULL');
935 FND_MSG_PUB.ADD;
936 RAISE FND_API.G_EXC_ERROR;
937 END IF;
938
939 -- validate the person id passed to the procedure
940 IF (igs_fi_gen_007.validate_person(p_person_id) = 'N' ) THEN
941 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
942 FND_MSG_PUB.ADD;
943 RAISE FND_API.G_EXC_ERROR;
944 END IF;
945
946 l_b_fees_assessed := TRUE;
947 FOR rec_c_igs_pe_std_todo_ref IN c_igs_pe_std_todo_ref (cp_n_person_id => p_person_id) LOOP
948
949 -- compare the fetched LCI with the last processed LCI
950 IF ((l_c_rpt_ld_cal_type <> rec_c_igs_pe_std_todo_ref.cal_type ) OR
951 (l_n_rpt_ld_ci_sequence_number <> rec_c_igs_pe_std_todo_ref.ci_sequence_number))
952 THEN
953 l_c_message_name := NULL;
954 -- call to function which converts the LCI to FCI
955 IF igs_fi_gen_001.finp_get_lfci_reln
956 (
957 p_cal_type => rec_c_igs_pe_std_todo_ref.cal_type ,
958 p_ci_sequence_number => rec_c_igs_pe_std_todo_ref.ci_sequence_number ,
959 p_cal_category => 'LOAD',
960 p_ret_cal_type => l_c_fee_cal_type,
961 p_ret_ci_sequence_number => l_n_fee_ci_sequence_number,
962 p_message_name => l_c_message_name
963 ) = FALSE THEN
964 IF l_c_message_name IS NOT NULL THEN
965 fnd_message.set_name('IGS', l_c_message_name);
966 fnd_msg_pub.ADD;
967 END IF;
968 -- Set Flag denoting that Fee Assessment failed
969 -- Exception is raised after Special Fee processing happens
970 l_b_fees_assessed := FALSE;
971 ELSE
972 -- Removed commit from this place as COMMIT has to happen once for all Load Calendars.
973 -- COMMIT after the for loop will do that.
974 l_c_message_name := NULL;
975 BEGIN
976 -- calls the fee calc routine
977 -- This routine will commit internally as we are calling with Test Run parameter as N.
978 IF igs_fi_prc_fee_ass.finp_ins_enr_fee_ass
979 (
980 p_effective_dt => SYSDATE,
981 p_person_id => rec_c_igs_pe_std_todo_ref.person_id,
982 p_course_cd => NULL,
983 p_fee_category => NULL,
984 p_fee_cal_type => l_c_fee_cal_type,
985 p_fee_ci_sequence_num => l_n_fee_ci_sequence_number,
986 p_fee_type => NULL,
987 p_trace_on => 'N',
988 p_test_run => 'N',
989 p_creation_dt => l_d_creation_dt,
990 p_message_name => l_c_message_name,
991 p_d_gl_date => TRUNC(SYSDATE),
992 p_v_wav_calc_flag => 'N',
993 p_n_waiver_amount => l_n_waiver_amount
994 ) = FALSE THEN
995 IF l_c_message_name IS NOT NULL THEN
996 --Bug 4735807, Added thiscondition to skip the addition of message onto the stack as it is already added in
997 --finp_ins_enr_fee_ass method for the case ' if no fee category is attached to the SPA.'. Otherwise a null message
998 --will be added and shown on the SS page.
999 IF l_c_message_name <> 'IGS_FI_NO_SPA_FEE_CAT' THEN
1000 fnd_message.set_name('IGS', l_c_message_name);
1001 IF l_c_message_name = 'IGS_FI_NO_CENSUS_DT_SETUP' THEN
1002 fnd_message.set_token('ALT_CD', igs_fi_prc_fee_ass.g_v_load_alt_code);
1003 END IF;
1004 fnd_msg_pub.ADD;
1005 END IF;
1006 END IF;
1007 -- Exception is raised after Special Fee processing happens
1008 l_b_fees_assessed := FALSE;
1009 END IF; -- fee calc routine check condition ends here
1010 EXCEPTION
1011 WHEN OTHERS THEN
1012 -- Even if Fee Assessment raises exception, Special Fees processing
1013 -- has to happen. So set flag to False and continue
1014 -- Exception is raised after Special Fee processing happens
1015 l_b_fees_assessed := FALSE;
1016 END ;
1017 END IF; -- condition check for LCI to FCI ends here
1018
1019 -- assign the fetched values to repeat values
1020 l_c_rpt_ld_cal_type := rec_c_igs_pe_std_todo_ref.cal_type;
1021 l_n_rpt_ld_ci_sequence_number := rec_c_igs_pe_std_todo_ref.ci_sequence_number;
1022 END IF; -- check condition for comparing the fetched LCI with the last processed LCI
1023
1024 -- update the logical delete date so that this record would no longer be an open todo
1025 -- Update the TODO record only if Fee Assessment was successful.
1026 IF l_b_fees_assessed THEN
1027 igs_pe_std_todo_ref_pkg.update_row ( x_rowid => rec_c_igs_pe_std_todo_ref.rowid,
1028 x_person_id => rec_c_igs_pe_std_todo_ref.person_id,
1029 x_s_student_todo_type => rec_c_igs_pe_std_todo_ref.s_student_todo_type,
1030 x_sequence_number => rec_c_igs_pe_std_todo_ref.sequence_number,
1031 x_reference_number => rec_c_igs_pe_std_todo_ref.reference_number,
1032 x_cal_type => rec_c_igs_pe_std_todo_ref.cal_type,
1033 x_ci_sequence_number => rec_c_igs_pe_std_todo_ref.ci_sequence_number,
1034 x_course_cd => rec_c_igs_pe_std_todo_ref.course_cd,
1035 x_unit_cd => rec_c_igs_pe_std_todo_ref.unit_cd,
1036 x_other_reference => rec_c_igs_pe_std_todo_ref.other_reference,
1037 x_logical_delete_dt => SYSDATE,
1038 x_mode => 'R',
1039 x_uoo_id => rec_c_igs_pe_std_todo_ref.uoo_id);
1040 ELSE
1041 -- i.e., l_b_fees_assessed == FALSE which means there is an error in deriving the Fee/Load period relation OR in fee assessment.
1042 -- So, Stop processing TODO REF records and exit out of loop
1043 EXIT;
1044 END IF;
1045
1046 END LOOP;
1047
1048 -- Commit if Fees is assessed.
1049 IF l_b_fees_assessed THEN
1050 COMMIT WORK;
1051 END IF;
1052
1053 -- Call Special Fees routine to assess Special Fees
1054 -- This has to be called irrespective of whether Fee Assessment is successful or not.
1055 igs_fi_prc_sp_fees.process_special_fees(p_n_person_id => p_person_id,
1056 p_v_fee_cal_type => NULL,
1057 p_n_fee_ci_seq_number => NULL,
1058 p_v_load_cal_type => NULL,
1059 p_n_load_ci_seq_number => NULL,
1060 p_d_gl_date => TRUNC(SYSDATE),
1061 p_v_test_run => 'N',
1062 p_b_log_messages => FALSE,
1063 p_b_recs_found => l_b_recs_found,
1064 p_v_return_status => l_v_ret_status);
1065 -- If Fee Assessment completed successfully, only then log any error messages of
1066 -- Special Fees Assessment, else SS pages would show only the Fee Assessment error
1067 IF l_b_fees_assessed THEN
1068 IF l_v_ret_status <> 'S' THEN
1069 -- Add Special Fee error message to the stack
1070 fnd_message.set_name('IGS','IGS_FI_SS_SP_NOT_ASSESSED');
1071 fnd_msg_pub.add;
1072 RAISE fnd_api.g_exc_error;
1073 END IF;
1074 ELSE
1075 -- Raise error since Fee Assessment errored out
1076 -- Error messages have already been added to the stack
1077 RAISE fnd_api.g_exc_error;
1078 END IF;
1079
1080 fnd_msg_pub.count_and_get(p_count => p_message_count,
1081 p_data => p_message_data
1082 );
1083
1084 EXCEPTION
1085 WHEN e_resource_busy THEN
1086 p_return_status := FND_API.G_RET_STS_ERROR;
1087 p_message_count := 1;
1088 p_message_data := fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F');
1089
1090 WHEN FND_API.G_EXC_ERROR THEN
1091 ROLLBACK ;
1092 p_return_status := FND_API.G_RET_STS_ERROR;
1093
1094 -- assign message data to this out parameter
1095 -- count should be initialized to 1 such that only one message is shown in the SS page
1096 -- after setting the message data OUT variable, initialize the message stack.
1097
1098 p_message_count := 1;
1099 p_message_data := fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F');
1100
1101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1102 ROLLBACK ;
1103 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1104 p_message_count := 1;
1105 p_message_data := fnd_msg_pub.get(p_msg_index => 1, p_encoded => 'F');
1106
1107 WHEN OTHERS THEN
1108 ROLLBACK ;
1109 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1110 p_message_count := 1;
1111 p_message_data := fnd_message.get_string('IGS', 'IGS_FI_SS_SP_NOT_ASSESSED');
1112
1113 END finp_calc_fees_todo;
1114
1115 FUNCTION get_msg_text(
1116 p_v_message_name IN fnd_new_messages.message_name%TYPE
1117 ) RETURN VARCHAR2 AS
1118 ------------------------------------------------------------------
1119 --Created by : Sanil Madathil, Oracle IDC
1120 --Date created: 30 May 2003
1121 --
1122 --Purpose:
1123 -- Invoked : From Student Homepage VO
1124 -- Function : Returns message text for the input message passed as parameter to it.
1125 -- Parameters :
1126 -- p_v_message_name : IN parmeter.
1127 --
1128 --Known limitations/enhancements and/or remarks:
1129 --
1130 --Change History:
1131 --Who When What
1132 --gurprsin 13-Sep-2005 Bug 3765876, Modified the Cursor c_get_msg_text select statement to add userenv. language condition with application id
1133 ------------------------------------------------------------------
1134
1135 CURSOR c_get_msg_text(cp_v_message_name fnd_new_messages.message_name%TYPE) IS
1136 SELECT message_text
1137 FROM fnd_new_messages
1138 WHERE message_name = cp_v_message_name
1139 AND application_id = 8405
1140 AND language_code = USERENV('LANG');
1141
1142 rec_c_get_msg_text c_get_msg_text%ROWTYPE;
1143
1144 BEGIN
1145
1146 IF p_v_message_name IS NULL THEN
1147 RETURN NULL;
1148 END IF;
1149
1150 OPEN c_get_msg_text(cp_v_message_name => p_v_message_name);
1151 FETCH c_get_msg_text INTO rec_c_get_msg_text;
1152 CLOSE c_get_msg_text;
1153
1154 RETURN rec_c_get_msg_text.message_text;
1155
1156 END get_msg_text;
1157
1158 END igs_fi_ss_acct_payment;
[Home] [Help]