1 PACKAGE BODY igs_fi_credit_pvt AS
2 /* $Header: IGSFI83B.pls 120.11 2006/05/16 23:57:15 pathipat ship $ */
3
4 /*----------------------------------------------------------------------------
5 || Created By : vvutukur
6 || Created On : 03-Apr-2003
7 || Purpose : Private API for creating credit and deposit transactions.
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || Who When What
11 || (reverse chronological order - newest change first)
12 || pathipat 17-May-2006 Bug 5104599 - Modified create_credit
13 || sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified create_credit.
14 uudayapr 8-Oct-2005 BUG 4660773 Commentted the Code LOgic introduced as part of Credit Card Enryption enhancement
15 || gurprsin 26-Sep-2005 Bug 4607540, In create_credit, Credit Card Enryption enhancement, Modified the call to igs_fi_credits_pkg.insert_row method.
16 || gurprsin 13-Sep-2005 Bug 3627209, After calling build accounts process, Added message on to the stack before adding it on to the fnd_message_pub.
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 4213629 - The automatic generation of the Receipt Number.
20 || Added x_credit_number OUT parameter
21 || bannamal 03-Jun-2005 Bug#3442712 Unit Level Fee Assessment Build. Modified the call
22 to igs_fi_prc_acct_pkg.build_accounts to add new paramters.
23 || pathipat 22-Apr-2004 Enh 3558549 - Comm Receivables Enhancements
24 || Modified TBH call to igs_fi_credits_pkg in create_credit()
25 || vvutukur 14-Sep-2003 Enh#3045007.Payment Plans Build. Modified create_credit.
26 || pathipat 13-Aug-2003 Enh 3076768 - Automatic Release of Holds
27 || Proc create_credit(): Added call to finp_auto_release_holds()
28 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Modified create_credit procedure.
29 || schodava 11-Jun-2003 Enh# 2831587. Modified the create_credit procedure
30 || shtatiko 30-APR-2003 Enh# 2831569, Modified create_credit
31 || vchappid 19-May-2003 Build Bug# 2831572, Financial Accounting Enhancements
32 || New Parameters - Attendance Type, Attendance Mode, Residency Status Code
33 || added in procedure create_credit
34 ----------------------------------------------------------------------------*/
35
36 PROCEDURE create_credit( p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2,
38 p_commit IN VARCHAR2,
39 p_validation_level IN NUMBER,
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2,
43 p_credit_rec IN credit_rec_type,
44 p_attribute_record IN igs_fi_credits_api_pub.attribute_rec_type,
45 x_credit_id OUT NOCOPY igs_fi_credits_all.credit_id%TYPE,
46 x_credit_activity_id OUT NOCOPY igs_fi_cr_activities.credit_activity_id%TYPE,
47 x_credit_number OUT NOCOPY igs_fi_credits_all.credit_number%TYPE
48 ) AS
49 /*----------------------------------------------------------------------------
50 || Created By : vvutukur
51 || Created On : 03-Apr-2003
52 || Purpose : Private API for creating credit and deposit transactions.
53 || Known limitations, enhancements or remarks :
54 || Change History :
55 || Who When What
56 || (reverse chronological order - newest change first)
57 || pathipat 17-May-2006 Bug 5104599 - After call to igs_fi_prc_balances.update_balances, added condition to not raise
58 || exception if the messages were regd exclusion of credit types or fee types
59 || svuppala 05-May-2006 Enh#3924836 Precision Issue. Done the formatting of amount in the beginning itslef
60 || sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted to igs_fi_credits and
61 || igs_fi_cr_activities_pkg are now rounded off to currency precision
62 uudayapr 8-Oct-2005 BUG 4660773 Commentted the Code LOgic introduced as part of Credit Card Enryption enhancement
63 || gurprsin 26-Sep-2005 Bug 4607540, Credit Card Enryption enhancement, Modified the call to igs_fi_credits_pkg.insert_row method.
64 || pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
65 || parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
66 || svuppala 9-JUN-2005 Enh 4213629 - The automatic generation of the Receipt Number.
67 || Added x_credit_number OUT parameter
68 || pathipat 22-Apr-2004 Enh 3558549 - Comm Receivables Enhancements
69 || Added param x_source_invoice_id in call to igs_fi_credits_pkg.insert_row()
70 || Modified IF condition in validation of p_invoice_id.
71 || vvutukur 14-Sep-2003 Enh#3045007.Payment Plans Build. Changes as specified in TD.
72 || pathipat 13-Aug-2003 Enh 3076768 - Automatic Release of Holds
73 || Added call to finp_auto_release_holds()
74 || vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build. Added 3 new paramters lockbox_interface_id,batch_name,deposit_date
75 || in the TBH call igs_fi_credits_pkg.insert_row.
76 || shtatiko 30-APR-2003 Enh# 2831569, Added check for Manage Accounts
77 || System Option. If its value is NULL then this will
78 || error out. If its value is OTHER then it tries
79 || to create a credit but doesn't update Standard
80 || or Holds Balance for the person in context.
81 || vchappid 19-May-2003 Build Bug# 2831572, Financial Accounting Enhancements
82 || New Parameters - Attendance Type, Attendance Mode, Residency Status Code
83 || added in procedure create_credit
84 ----------------------------------------------------------------------------*/
85
86 --Cursors used in this procedure
87
88 --Cursor to derive revenue account from the credit account of source charge transaction.
89 CURSOR cur_override_dr(cp_invoice_id igs_fi_invln_int_all.invoice_id%TYPE) IS
90 SELECT rev_account_cd, rev_gl_ccid
91 FROM igs_fi_invln_int_all
92 WHERE invoice_id = cp_invoice_id;
93
94 --Cursor to fetch the admission application number for an application id and person id.
95 CURSOR cur_adm_app_num ( cp_application_id igs_ad_appl.application_id%TYPE,
96 cp_person_id igs_ad_appl.person_id%TYPE
97 )IS
98 SELECT admission_appl_number
99 FROM igs_ad_appl_all
100 WHERE person_id = cp_person_id
101 AND application_id = cp_application_id;
102
103 -- Cursor to get the waiver name, fee cal type for the passed waiver name
104 CURSOR cur_waiver_pgms (cp_fee_cal_type igs_fi_waiver_pgms.fee_cal_type%TYPE,
105 cp_fee_ci_sequence_number igs_fi_waiver_pgms.fee_ci_sequence_number%TYPE,
106 cp_waiver_name igs_fi_waiver_pgms.waiver_name%TYPE ) IS
107 SELECT fwp.fee_cal_type, fwp.fee_ci_sequence_number, fwp.waiver_name
108 FROM igs_fi_waiver_pgms fwp
109 WHERE fwp.fee_cal_type = cp_fee_cal_type
110 AND fwp.fee_ci_sequence_number = cp_fee_ci_sequence_number
111 AND fwp.waiver_name = cp_waiver_name;
112
113 l_waiver_pgms cur_waiver_pgms%ROWTYPE;
114 --Local Variables declaration part.
115 l_pkg_name CONSTANT VARCHAR2(30) := 'IGS_FI_CREDIT_PVT';
116 l_api_version CONSTANT NUMBER := 2.1;
117 l_api_name CONSTANT VARCHAR2(30) := 'create_credit';
118 l_credit CONSTANT VARCHAR2(10) := 'CREDIT';
119 l_standard CONSTANT igs_fi_balance_rules.balance_name%TYPE := 'STANDARD';
120 l_hold CONSTANT igs_fi_balance_rules.balance_name%TYPE := 'HOLDS';
121 l_v_action_active CONSTANT VARCHAR2(10) := 'ACTIVE';
122
123 l_b_return_status BOOLEAN;
124 l_d_last_conversion_date DATE;
125 l_d_transaction_date DATE;
126 l_d_effective_date DATE;
127
128 l_n_cnv_prc igs_fi_control_all.conv_process_run_ind%TYPE := NULL;
129 l_n_version_number igs_fi_balance_rules.version_number%TYPE;
130 l_n_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
131 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
132 l_v_currency_cd igs_fi_control_all.currency_cd%TYPE := NULL;
133 l_n_amount igs_fi_credits_all.amount%TYPE;
134
135 l_n_dr_gl_ccid igs_fi_cr_activities.dr_gl_ccid%TYPE;
136 l_n_cr_gl_ccid igs_fi_cr_activities.cr_gl_ccid%TYPE;
137 l_v_dr_account_cd igs_fi_cr_activities.dr_account_cd%TYPE;
138 l_v_cr_account_cd igs_fi_cr_activities.cr_account_cd%TYPE;
139 l_n_err_type NUMBER(2) := NULL;
140 l_v_err_string VARCHAR2(2000) := NULL;
141
142 l_rec_cur_override_dr cur_override_dr%ROWTYPE;
143 l_v_credit_class igs_fi_cr_types_all.credit_class%TYPE;
144 l_v_acct_mthd igs_fi_control_all.accounting_method%TYPE;
145 l_var VARCHAR2(1);
146
147 l_n_credit_id igs_fi_credits.credit_id%TYPE := NULL;
148 l_n_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE := NULL;
149 l_v_rowid VARCHAR2(25) := NULL;
150
151 l_adm_app_number igs_ad_appl_all.admission_appl_number%TYPE;
152 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
153
154 l_v_credit_number igs_fi_credits_all.credit_number%TYPE;
155 l_v_cc_number igs_fi_credits_all.credit_card_number%TYPE;
156
157 BEGIN
158
159 --Create a savepoint.
160 SAVEPOINT create_credit_pvt;
161
162 --Check if the user is having a compatible version.
163 IF NOT fnd_api.compatible_api_call( p_current_version_number => l_api_version,
164 p_caller_version_number => p_api_version,
165 p_api_name => l_api_name,
166 p_pkg_name => l_pkg_name) THEN
167 --If not, then raise an error message.
168 RAISE fnd_api.g_exc_unexpected_error;
169 END IF;
170
171 --If the calling program has passed the parameter for initializing the message list
172 IF fnd_api.to_boolean(p_init_msg_list) THEN
173 --then call the Initialize program of the fnd_msg_pub package to initialize the message list.
174 fnd_msg_pub.initialize;
175 END IF;
176
177 --Set the return status to success
178 x_return_status := fnd_api.g_ret_sts_success;
179
180 -- Get the value of "Manage Accounts" System Option value.
181 -- If this value is NULL then this process should error out.
182 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
183 p_v_message_name => l_v_message_name );
184 IF l_v_manage_accounts IS NULL THEN
185 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
186 fnd_message.set_name ( 'IGS', l_v_message_name );
187 fnd_msg_pub.add;
188 RAISE fnd_api.g_exc_error;
189 END IF;
190 END IF;
191
192 l_v_message_name := NULL;
193 -- Check for Holds Balance Conversion Process and Existance of Balance Rule for Holds Balance
194 -- are not required if Manage Accounts Option has value OTHER.
195 IF l_v_manage_accounts <> 'OTHER' THEN
196 --Check whether Holds Balance Conversion Process is running..
197 igs_fi_gen_007.finp_get_conv_prc_run_ind( p_n_conv_process_run_ind => l_n_cnv_prc,
198 p_v_message_name => l_v_message_name
199 );
200
201 --If holds conversion process is running..error out of Private Credits API.
202 IF l_n_cnv_prc = 1 AND l_v_message_name IS NULL THEN
203 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
204 fnd_message.set_name('IGS','IGS_FI_REASS_BAL_PRC_RUN');
205 fnd_msg_pub.add;
206 RAISE fnd_api.g_exc_error;
207 END IF;
208 END IF;
209
210 IF l_v_message_name IS NOT NULL THEN
211 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
212 fnd_message.set_name('IGS',l_v_message_name);
213 fnd_msg_pub.add;
214 RAISE fnd_api.g_exc_error;
215 END IF;
216 END IF;
217
218 --Get the latest active balance rule for 'HOLDS' balance type.
219 igs_fi_gen_007.finp_get_balance_rule( p_v_balance_type => l_hold,
220 p_v_action => l_v_action_active,
221 p_n_balance_rule_id => l_n_balance_rule_id,
222 p_d_last_conversion_date => l_d_last_conversion_date,
223 p_n_version_number => l_n_version_number
224 );
225
226 --If no active balance rule exists for 'HOLDS', raise error.
227 IF l_n_version_number = 0 THEN
228 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
229 fnd_message.set_name('IGS','IGS_FI_CANNOT_CRT_TXN');
230 fnd_msg_pub.add;
231 RAISE fnd_api.g_exc_error;
232 END IF;
233 END IF;
234 END IF;
235
236 --Assign the parameter values of amount and currency code into local variables.
237 -- Added call to format amount by rounding off to currency precision
238 l_n_amount := igs_fi_gen_gl.get_formatted_amount(p_credit_rec.p_amount);
239 l_v_currency_cd := p_credit_rec.p_currency_cd;
240
241 --If this procedure is invoked with FULL validation level,
242 IF p_validation_level = fnd_api.g_valid_level_full THEN
243 --then, call the public procedure for all validations to happen.
244 igs_fi_crdapi_util.validate_parameters( p_n_validation_level => fnd_api.g_valid_level_full,
245 p_credit_rec => p_credit_rec,
246 p_attribute_rec => p_attribute_record,
247 p_b_return_status => l_b_return_status);
248
249 --if any error occurred during above validation, raise error.
250 IF l_b_return_status = FALSE THEN
251 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
252 RAISE fnd_api.g_exc_error;
253 END IF;
254 END IF;
255
256 l_v_message_name := NULL;
257
258 --also, call the public procedure for currency code validation and translation into local currency(if required).
259 igs_fi_crdapi_util.translate_local_currency( p_n_amount => l_n_amount,
260 p_v_currency_cd => l_v_currency_cd,
261 p_n_exchange_rate => p_credit_rec.p_exchange_rate,
262 p_b_return_status => l_b_return_status,
263 p_v_message_name => l_v_message_name);
264 --if any error occurred during above validation...
265 IF l_b_return_status = FALSE THEN
266 --raise the error and abort further processing.
267 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
268 fnd_message.set_name('IGS',l_v_message_name);
269 fnd_msg_pub.add;
270 RAISE fnd_api.g_exc_error;
271 END IF;
272 END IF;
273 END IF;
274
275
276 --Fetch the credit class.
277 igs_fi_crdapi_util.validate_credit_type( p_n_credit_type_id => p_credit_rec.p_credit_type_id,
278 p_v_credit_class => l_v_credit_class,
279 p_b_return_stat => l_b_return_status
280 );
281
282 -- Pmarada Added logic as per Tution Waivers enhancement
283 IF (p_credit_rec.p_waiver_name IS NOT NULL) AND (l_v_credit_class = 'WAIVER') THEN
284 OPEN cur_waiver_pgms (p_credit_rec.p_fee_cal_type, p_credit_rec.p_fee_ci_sequence_number, p_credit_rec.p_waiver_name );
285 FETCH cur_waiver_pgms INTO l_waiver_pgms;
286 IF cur_waiver_pgms%NOTFOUND THEN
287 CLOSE cur_waiver_pgms;
288 fnd_message.set_name('IGS', 'IGS_FI_WAV_PGM_NO_REC_FOUND');
289 fnd_msg_pub.add;
290 RAISE fnd_api.g_exc_error;
291 END IF;
292 CLOSE cur_waiver_pgms;
293 END IF;
294
295 --Assign the parameter values of trancation date and effective dates into local variables.
296 l_d_transaction_date := p_credit_rec.p_transaction_date;
297 l_d_effective_date := p_credit_rec.p_effective_date;
298
299 --If no value is provided for the transaction date parameter, set the System Date to this and use for further processing.
300 IF p_credit_rec.p_transaction_date IS NULL THEN
301 l_d_transaction_date := TRUNC(SYSDATE);
302 END IF;
303
304 --If no value is provided for the effective date parameter also, set effective date same as the transaction date.
305 IF p_credit_rec.p_effective_date IS NULL THEN
306 l_d_effective_date := l_d_transaction_date;
307 END IF;
308
309 --Invoke the Build Accounts Process for deriving the accounting information.
310 igs_fi_prc_acct_pkg.build_accounts(
311 p_fee_type => NULL,
312 p_fee_cal_type => NULL,
313 p_fee_ci_sequence_number => NULL,
314 p_course_cd => NULL,
315 p_course_version_number => NULL,
316 p_org_unit_cd => NULL,
317 p_org_start_dt => NULL,
318 p_unit_cd => NULL,
319 p_unit_version_number => NULL,
320 p_uoo_id => NULL,
321 p_location_cd => NULL,
322 p_transaction_type => l_credit,
323 p_credit_type_id => p_credit_rec.p_credit_type_id,
324 p_source_transaction_id => NULL,
325 x_dr_gl_ccid => l_n_dr_gl_ccid,
326 x_cr_gl_ccid => l_n_cr_gl_ccid,
327 x_dr_account_cd => l_v_dr_account_cd,
328 x_cr_account_cd => l_v_cr_account_cd,
329 x_err_type => l_n_err_type,
330 x_err_string => l_v_err_string,
331 x_ret_status => l_b_return_status,
332 p_v_attendance_type => NULL,
333 p_v_attendance_mode => NULL,
334 p_v_residency_status_cd => NULL,
335 p_n_unit_type_id => NULL,
336 p_v_unit_class => NULL,
337 p_v_unit_mode => NULL,
338 p_v_unit_level => NULL,
339 p_v_waiver_name => NULL
340 );
341
342 --If any error occurred, raise the error.
343 IF l_b_return_status = FALSE THEN
344 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
345 --Bug 3627209, Added message on to the stack before adding it on to the fnd_message_pub
346 fnd_message.set_name('IGS',l_v_err_string);
347 fnd_msg_pub.add;
348 RAISE fnd_api.g_exc_error;
349 END IF;
350 END IF;
351
352 --If the user has provided a value for p_invoice_id parameter...
353 IF (p_credit_rec.p_invoice_id IS NOT NULL) AND (l_v_credit_class = 'CHGADJ') THEN
354 --Check if the accounting method setup in the System Options Form is ACCRUAL.
355 --If accounting method is CASH, no changes are necessary for the accounting information derived earlier.
356 l_v_acct_mthd := igs_fi_gen_005.finp_get_acct_meth;
357 --If the accounting method is ACCRUAL...
358 IF l_v_acct_mthd = 'ACCRUAL' THEN
359 OPEN cur_override_dr(p_credit_rec.p_invoice_id);
360 FETCH cur_override_dr INTO l_rec_cur_override_dr;
361 IF cur_override_dr%FOUND THEN
362 --Override the debit account with the revenue account checking if the receivables is installed or not.
363 IF igs_fi_gen_005.finp_get_receivables_inst = 'Y' THEN
364 l_n_dr_gl_ccid := l_rec_cur_override_dr.rev_gl_ccid;
365 l_v_dr_account_cd := NULL;
366 ELSE
367 l_n_dr_gl_ccid := NULL;
368 l_v_dr_account_cd := l_rec_cur_override_dr.rev_account_cd;
369 END IF;
370 END IF; --cur override IF
371 CLOSE cur_override_dr;
372 END IF; --acct mthd IF
373 END IF; --for p_invoice_id IF
374
375 --Enh 4607540 , Calling iPayment API to get the encrypted value of Credit card number.
376 --This Code logic is commented as the part of the Bug 4660773 Dont remove the commented Code
377 /* IF p_credit_rec.p_credit_card_number IS NOT NULL THEN
378 l_v_cc_number := IBY_CC_SECURITY_PUB.SECURE_CARD_NUMBER(p_commit => FND_API.G_FALSE, p_card_number => p_credit_rec.p_credit_card_number);
379 ELSE
380 l_v_cc_number := p_credit_rec.p_credit_card_number;
381 END IF;
382
383 */
384 l_v_cc_number := p_credit_rec.p_credit_card_number; -- This piece of code has to be removed when the above commented code is uncommented.
385
386
387 --Call the table handler for the credits table for creating a record in the credits table.
388 --passed l_v_credit_number for x_credit_number (svuppala - Enh# 4213629)
389 igs_fi_credits_pkg.insert_row( x_rowid => l_v_rowid,
390 x_credit_id => l_n_credit_id,
391 x_credit_number => l_v_credit_number,
392 x_status => p_credit_rec.p_credit_status,
393 x_credit_source => p_credit_rec.p_credit_source,
394 x_party_id => p_credit_rec.p_party_id,
395 x_credit_type_id => p_credit_rec.p_credit_type_id,
396 x_credit_instrument => p_credit_rec.p_credit_instrument,
397 x_description => p_credit_rec.p_description,
398 x_amount => l_n_amount,
399 x_currency_cd => l_v_currency_cd,
400 x_exchange_rate => 1,
401 x_transaction_date => TRUNC(l_d_transaction_date),
402 x_effective_date => TRUNC(l_d_effective_date),
403 x_reversal_date => NULL,
404 x_reversal_reason_code => NULL,
405 x_reversal_comments => NULL,
406 x_unapplied_amount => l_n_amount,
407 x_source_transaction_id => p_credit_rec.p_source_transaction_id,
408 x_merchant_id => NULL,
409 x_receipt_lockbox_number => p_credit_rec.p_receipt_lockbox_number,
410 x_credit_card_code => p_credit_rec.p_credit_card_code,
411 x_credit_card_holder_name => p_credit_rec.p_credit_card_holder_name,
412 --Bug 4607540, Credit Card Enryption enhancement
413 x_credit_card_number => l_v_cc_number,
414 x_credit_card_expiration_date => TRUNC(p_credit_rec.p_credit_card_expiration_date),
415 x_credit_card_approval_code => p_credit_rec.p_credit_card_approval_code,
416 x_awd_yr_cal_type => p_credit_rec.p_awd_yr_cal_type,
417 x_awd_yr_ci_sequence_number => p_credit_rec.p_awd_yr_ci_sequence_number,
418 x_fee_cal_type => p_credit_rec.p_fee_cal_type,
419 x_fee_ci_sequence_number => p_credit_rec.p_fee_ci_sequence_number,
420 x_attribute_category => p_attribute_record.p_attribute_category,
421 x_attribute1 => p_attribute_record.p_attribute1,
422 x_attribute2 => p_attribute_record.p_attribute2,
423 x_attribute3 => p_attribute_record.p_attribute3,
424 x_attribute4 => p_attribute_record.p_attribute4,
425 x_attribute5 => p_attribute_record.p_attribute5,
426 x_attribute6 => p_attribute_record.p_attribute6,
427 x_attribute7 => p_attribute_record.p_attribute7,
428 x_attribute8 => p_attribute_record.p_attribute8,
429 x_attribute9 => p_attribute_record.p_attribute9,
430 x_attribute10 => p_attribute_record.p_attribute10,
431 x_attribute11 => p_attribute_record.p_attribute11,
432 x_attribute12 => p_attribute_record.p_attribute12,
433 x_attribute13 => p_attribute_record.p_attribute13,
434 x_attribute14 => p_attribute_record.p_attribute14,
435 x_attribute15 => p_attribute_record.p_attribute15,
436 x_attribute16 => p_attribute_record.p_attribute16,
437 x_attribute17 => p_attribute_record.p_attribute17,
438 x_attribute18 => p_attribute_record.p_attribute18,
439 x_attribute19 => p_attribute_record.p_attribute19,
440 x_attribute20 => p_attribute_record.p_attribute20,
441 x_gl_date => p_credit_rec.p_gl_date,
442 x_check_number => p_credit_rec.p_check_number,
443 x_source_transaction_type => p_credit_rec.p_source_tran_type,
444 x_source_transaction_ref => p_credit_rec.p_source_tran_ref_number,
445 x_credit_card_payee_cd => p_credit_rec.p_v_credit_card_payee_cd,
446 x_credit_card_status_code => p_credit_rec.p_v_credit_card_status_code,
447 x_credit_card_tangible_cd => p_credit_rec.p_v_credit_card_tangible_cd,
448 x_lockbox_interface_id => p_credit_rec.p_lockbox_interface_id,
449 x_batch_name => p_credit_rec.p_batch_name,
450 x_deposit_date => p_credit_rec.p_deposit_date,
451 x_source_invoice_id => p_credit_rec.p_invoice_id,
452 x_tax_year_code => NULL,
453 x_waiver_name => p_credit_rec.p_waiver_name
454 );
455
456 --Initilialize local variable of rowid to null values, as this value would be populated thru tbh call.
457 l_v_rowid := NULL;
458
459 --Call the table handler insert row procedure for creating the records in the credit activities table.
460 igs_fi_cr_activities_pkg.insert_row(x_rowid => l_v_rowid,
461 x_credit_activity_id => l_n_credit_activity_id,
462 x_credit_id => l_n_credit_id,
463 x_status => p_credit_rec.p_credit_status,
464 x_transaction_date => TRUNC(l_d_transaction_date),
465 x_amount => l_n_amount,
466 x_dr_account_cd => l_v_dr_account_cd,
467 x_cr_account_cd => l_v_cr_account_cd,
468 x_dr_gl_ccid => l_n_dr_gl_ccid,
469 x_cr_gl_ccid => l_n_cr_gl_ccid,
470 x_bill_id => NULL,
471 x_bill_number => NULL,
472 x_bill_date => NULL,
473 x_posting_id => NULL,
474 x_gl_date => p_credit_rec.p_gl_date,
475 x_gl_posted_date => NULL,
476 x_posting_control_id => NULL
477 );
478
479 --If the credit class is Installment Payments then, apply the transaction amount to the person's
480 --installment payments such that the new installment balance is reflected.
481 IF l_v_credit_class = 'INSTALLMENT_PAYMENTS' THEN
482 igs_fi_crdapi_util.apply_installments(p_n_person_id => p_credit_rec.p_party_id,
483 p_n_amount => l_n_amount,
484 p_n_credit_id => l_n_credit_id,
485 p_n_cr_activity_id => l_n_credit_activity_id);
486 END IF;
487
488 --In case of a deposit transaction, with 'Enrollment Deposit' Credit Class, the successfully recorded
489 --transaction information in Student Finance Subsystem has to be updated in the Admission Subsystem also.
490 IF l_v_credit_class = 'ENRDEPOSIT' THEN
491 --Fetch the appliation number for the source transaction reference number(application_id).
492 OPEN cur_adm_app_num(TO_NUMBER(p_credit_rec.p_source_tran_ref_number),p_credit_rec.p_party_id);
493 FETCH cur_adm_app_num INTO l_adm_app_number;
494 CLOSE cur_adm_app_num;
495
496 --passed l_v_credit_number for p_reference_number (svuppala - Enh# 4213629)
497 igs_ad_gen_015.create_enrollment_deposit( p_person_id => p_credit_rec.p_party_id,
498 p_admission_appl_number => l_adm_app_number,
499 p_enrollment_deposit_amount => l_n_amount,
500 p_payment_date => TRUNC(SYSDATE),
501 p_fee_payment_method => p_credit_rec.p_credit_instrument,
502 p_reference_number => l_v_credit_number
503 );
504 END IF;
505
506 -- After the records have been created in the credits and the credit activities table, then
507 -- the update balances process should be called for picking up the balances records in the credits
508 -- table for Holds and Standard Balance.
509
510 --Firstly, Standard Balance updation.
511
512 l_v_message_name :=NULL;
513
514 --Updation of balances table SHOULD NOT happen for Deposit Transactions.
515 -- And also if Manage Accounts is OTHER
516 IF (l_v_credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT'))
517 AND l_v_manage_accounts <> 'OTHER' THEN
518 igs_fi_prc_balances.update_balances( p_party_id => p_credit_rec.p_party_id,
519 p_balance_type => l_standard,
520 p_balance_date => TRUNC(l_d_transaction_date),
521 p_amount => ((-1)*l_n_amount),
522 p_source => l_credit,
523 p_source_id => l_n_credit_id,
524 p_message_name => l_v_message_name
525 );
526
527 IF l_v_message_name IS NOT NULL THEN
528 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
529 fnd_message.set_name('IGS', l_v_message_name);
530 fnd_msg_pub.add;
531 -- Do not raise error for information messages regd credit type or fee type being excluded.
532 IF l_v_message_name NOT IN ('IGS_FI_CTYP_EXCLDED','IGS_FI_FTYP_EXCLDED') THEN
533 RAISE fnd_api.g_exc_error;
534 END IF;
535 END IF;
536 END IF;
537 END IF;
538
539 --Secondly, Holds Balance updation.
540
541 l_v_message_name := NULL;
542
543 --Updation of balances table SHOULD NOT happen for Deposit Transactions.
544 -- And also if Manage Accounts is OTHER
545 IF (l_v_credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT'))
546 AND l_v_manage_accounts <> 'OTHER' THEN
547 igs_fi_prc_balances.update_balances( p_party_id => p_credit_rec.p_party_id,
548 p_balance_type => l_hold,
549 p_balance_date => TRUNC(l_d_transaction_date),
550 p_amount => ((-1)*l_n_amount),
551 p_source => l_credit,
552 p_source_id => l_n_credit_id,
553 p_message_name => l_v_message_name
554 );
555
556 IF l_v_message_name IS NOT NULL THEN
557 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
558 fnd_message.set_name('IGS', l_v_message_name);
559 fnd_msg_pub.add;
560 -- Do not raise error for information messages regd credit type or fee type being excluded.
561 IF l_v_message_name NOT IN ('IGS_FI_CTYP_EXCLDED','IGS_FI_FTYP_EXCLDED') THEN
562 RAISE fnd_api.g_exc_error;
563 END IF;
564 END IF;
565 END IF;
566 END IF;
567
568 -- Call procedure to Validate the holds and release holds if applicable
569 -- The holds table, IGS_FI_PERSON_HOLDS, will contain the credit id of the
570 -- transaction as the release_credit_id
571 l_v_message_name := NULL;
572 IF (l_v_credit_class NOT IN ('ENRDEPOSIT','OTHDEPOSIT')) AND l_v_manage_accounts <> 'OTHER' THEN
573 igs_fi_prc_holds.finp_auto_release_holds( p_person_id => p_credit_rec.p_party_id,
574 p_hold_plan_level => 'S',
575 p_release_credit_id => l_n_credit_id,
576 p_run_application => 'N',
577 p_message_name => l_v_message_name
578 );
579 -- If p_message_name has been returned with a value, add message to stack
580 IF l_v_message_name IS NOT NULL THEN
581 fnd_message.set_name('IGS',l_v_message_name);
582 fnd_msg_pub.add;
583 END IF;
584 END IF;
585
586 --If the calling program has passed the parameter for committing the data and there
587 --have been no errors in calling the balances process, then commit the work
588 IF (fnd_api.to_boolean(p_commit)) THEN
589 COMMIT WORK;
590 END IF;
591
592 --Assign the values to the out parameters
593 x_credit_id := l_n_credit_id;
594 x_credit_activity_id := l_n_credit_activity_id;
595 x_credit_number := l_v_credit_number;
596
597 fnd_msg_pub.count_and_get( p_count => x_msg_count,
598 p_data => x_msg_data);
599
600 EXCEPTION
601 WHEN fnd_api.g_exc_error THEN
602 ROLLBACK TO create_credit_pvt;
603 x_return_status := fnd_api.g_ret_sts_error;
604 fnd_msg_pub.count_and_get( p_count => x_msg_count,
605 p_data => x_msg_data);
606
607 WHEN fnd_api.g_exc_unexpected_error THEN
608 ROLLBACK TO create_credit_pvt;
609 x_return_status := fnd_api.g_ret_sts_unexp_error;
610 fnd_msg_pub.count_and_get( p_count => x_msg_count,
611 p_data => x_msg_data);
612
613 WHEN OTHERS THEN
614 ROLLBACK TO create_credit_pvt;
615 x_return_status := fnd_api.g_ret_sts_unexp_error;
616 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
617 fnd_msg_pub.add_exc_msg(l_pkg_name,
618 l_api_name);
619 END IF;
620 fnd_msg_pub.count_and_get( p_count => x_msg_count,
621 p_data => x_msg_data);
622
623 END create_credit;
624 END igs_fi_credit_pvt;
[Home] [Help]