[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_DEPOSITS_PRCSS
Source
1 PACKAGE BODY igs_fi_deposits_prcss AS
2 /* $Header: IGSFI74B.pls 120.2 2005/07/08 04:37:51 appldev ship $ */
3
4 /*=======================================================================+
5 | Copyright (c) 1994, 1996 Oracle Corp. Redwood Shores, California, USA|
6 | All rights reserved. |
7 +=======================================================================+
8 | |
9 | DESCRIPTION |
10 | PL/SQL body for package: IGS_FI_DEPOSITS_PRCSS |
11 | |
12 | NOTES: |
13 | Contains procedure for reversing a transaction, reverse_transaction(),|
14 | forfeit_deposit for forfeiting, and transfer_Deposit for transfer |
15 | |
16 | HISTORY |
17 | WHO WHEN WHAT |
18 | svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic |
19 | generation of the Receipt Number. |
20 | Modified procedure transfer_deposit. |
21 | pmarada 26-May-2005 Enh#3020586- added tax year code column |
22 | to as per 1098-t reporting build |
23 | pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh |
24 | Added param x_source_invoice_id in calls|
25 | to igs_fi_credits_pkg.update_row() |
26 | schodava 06-Oct-2003 Bug # 3123405. Modified procedure |
27 | transfer_deposit.
28 | vvutukur 22-Sep-2003 Enh#3045007.Payment Plans Build.Modified|
29 | reverse_transaction. |
30 | vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified the |
31 | procedures forfeit_deposit, |
32 | reverse_transaction,transfer_deposit. |
33 | schodava 11-Jun-2003 Enh# 2831587. Credit Card Fund Transfer |
34 | Modified forfeit_deposit, |
35 | reverse_transaction and transfer_deposit|
36 | procedures |
37 | vvutukur 09-Apr-2003 Enh#2831554.Internal Credits API Build. |
38 | Modified procedure transfer_deposit. |
39 | pathipat 8-Dec-02 Enh # 2584741 Deposits build |
40 | Added forfeit_deposit and transfer_deposit
41 | schodava 4-Dec-02 Enh # 2584741 Deposits Build |
42 | Added logic for reversal of Deposit |
43 | related transactions |
44 *=======================================================================*/
45
46 g_cleared CONSTANT igs_lookup_values.lookup_code%TYPE := 'CLEARED';
47 g_forfeited CONSTANT igs_lookup_values.lookup_code%TYPE := 'FORFEITED';
48 g_transferred CONSTANT igs_lookup_values.lookup_code%TYPE := 'TRANSFERRED';
49 g_reversed CONSTANT igs_lookup_values.lookup_code%TYPE := 'REVERSED';
50 g_deposit CONSTANT igs_lookup_values.lookup_code%TYPE := 'DEPOSIT';
51
52
53 PROCEDURE forfeit_deposit( p_n_credit_id IN NUMBER,
54 p_d_gl_date IN DATE,
55 p_b_return_status OUT NOCOPY BOOLEAN,
56 p_c_message_name OUT NOCOPY VARCHAR2
57 ) AS
58 ------------------------------------------------------------------
59 --Created by : Priya Athipatla, Oracle IDC
60 --Date created: 08-DEC-2002
61 --
62 --Purpose: For forfeiting a deposit
63 --
64 --Known limitations/enhancements and/or remarks:
65 --
66 --Change History:
67 --Who When What
68 --pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
69 -- Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
70 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified update_row TBH call to credits table to add 3 new columns
71 -- lockbox_interface_id,batch_name,deposit_date.
72 --schodava 11-Jun-03 Enh # 2831587. Modified the Credits table update_row call
73 -------------------------------------------------------------------
74
75 CURSOR cur_fi_credits (cp_credit_id NUMBER ) IS
76 SELECT cre.rowid,cre.*
77 FROM igs_fi_credits cre
78 WHERE credit_id = cp_credit_id
79 AND status = g_cleared;
80
81 CURSOR cur_fi_cr_types (cp_credit_type_id NUMBER ) IS
82 SELECT *
83 FROM igs_fi_cr_types
84 WHERE credit_type_id = cp_credit_type_id;
85
86 l_cur_fi_credits cur_fi_credits%ROWTYPE;
87 l_cur_fi_cr_types cur_fi_cr_types%ROWTYPE;
88 l_n_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE := NULL;
89 l_rowid ROWID := NULL;
90 l_n_cr_gl_ccid igs_fi_cr_types.cr_gl_ccid%TYPE;
91 l_n_dr_gl_ccid igs_fi_cr_types.dr_gl_ccid%TYPE;
92 l_v_cr_account_cd igs_fi_cr_types.cr_account_cd%TYPE;
93 l_v_dr_account_cd igs_fi_cr_types.dr_account_cd%TYPE;
94
95 BEGIN
96
97 -- Check if mandatory parameters are specified
98 IF (p_n_credit_id IS NULL) OR (p_d_gl_date IS NULL) THEN
99 p_c_message_name := 'IGS_UC_NO_MANDATORY_PARAMS';
100 p_b_return_status := FALSE;
101 RETURN;
102 END IF;
103
104 -- Validate credit_id, if valid, then obtain the records
105 OPEN cur_fi_credits(p_n_credit_id);
106 FETCH cur_fi_credits INTO l_cur_fi_credits;
107 IF cur_fi_credits%NOTFOUND THEN
108 p_c_message_name := 'IGS_GE_INVALID_VALUE';
109 p_b_return_status := FALSE;
110 CLOSE cur_fi_credits;
111 RETURN;
112 END IF;
113 CLOSE cur_fi_credits;
114
115 -- Update the credits table - set status to Forfeited, the reversal data is null,
116 -- unapplied amount is same as the amount.
117 igs_fi_credits_pkg.update_row ( x_mode => 'R',
118 x_rowid => l_cur_fi_credits.rowid,
119 x_credit_id => l_cur_fi_credits.credit_id,
120 x_credit_number => l_cur_fi_credits.credit_number ,
121 x_status => g_forfeited ,
122 x_credit_source => l_cur_fi_credits.credit_source,
123 x_party_id => l_cur_fi_credits.party_id,
124 x_credit_type_id => l_cur_fi_credits.credit_type_id,
125 x_credit_instrument => l_cur_fi_credits.credit_instrument,
126 x_description => l_cur_fi_credits.description,
127 x_amount => l_cur_fi_credits.amount,
128 x_currency_cd => l_cur_fi_credits.currency_cd,
129 x_exchange_rate => l_cur_fi_credits.exchange_rate,
130 x_transaction_date => l_cur_fi_credits.transaction_date,
131 x_effective_date => l_cur_fi_credits.effective_date,
132 x_reversal_date => NULL,
133 x_reversal_reason_code => NULL,
134 x_reversal_comments => NULL,
135 x_unapplied_amount => l_cur_fi_credits.unapplied_amount,
136 x_source_transaction_id => l_cur_fi_credits.source_transaction_id,
137 x_receipt_lockbox_number => l_cur_fi_credits.receipt_lockbox_number,
138 x_merchant_id => l_cur_fi_credits.merchant_id,
139 x_credit_card_code => l_cur_fi_credits.credit_card_code,
140 x_credit_card_holder_name => l_cur_fi_credits.credit_card_holder_name,
141 x_credit_card_number => l_cur_fi_credits.credit_card_number,
142 x_credit_card_expiration_date => l_cur_fi_credits.credit_card_expiration_date,
143 x_credit_card_approval_code => l_cur_fi_credits.credit_card_approval_code,
144 x_awd_yr_cal_type => l_cur_fi_credits.awd_yr_cal_type,
145 x_awd_yr_ci_sequence_number => l_cur_fi_credits.awd_yr_ci_sequence_number,
146 x_fee_cal_type => l_cur_fi_credits.fee_cal_type,
147 x_fee_ci_sequence_number => l_cur_fi_credits.fee_ci_sequence_number,
148 x_attribute_category => l_cur_fi_credits.attribute_category,
149 x_attribute1 => l_cur_fi_credits.attribute1,
150 x_attribute2 => l_cur_fi_credits.attribute2,
151 x_attribute3 => l_cur_fi_credits.attribute3,
152 x_attribute4 => l_cur_fi_credits.attribute4,
153 x_attribute5 => l_cur_fi_credits.attribute5,
154 x_attribute6 => l_cur_fi_credits.attribute6,
155 x_attribute7 => l_cur_fi_credits.attribute7,
156 x_attribute8 => l_cur_fi_credits.attribute8,
157 x_attribute9 => l_cur_fi_credits.attribute9,
158 x_attribute10 => l_cur_fi_credits.attribute10,
159 x_attribute11 => l_cur_fi_credits.attribute11,
160 x_attribute12 => l_cur_fi_credits.attribute12,
161 x_attribute13 => l_cur_fi_credits.attribute13,
162 x_attribute14 => l_cur_fi_credits.attribute14,
163 x_attribute15 => l_cur_fi_credits.attribute15,
164 x_attribute16 => l_cur_fi_credits.attribute16,
165 x_attribute17 => l_cur_fi_credits.attribute17,
166 x_attribute18 => l_cur_fi_credits.attribute18,
167 x_attribute19 => l_cur_fi_credits.attribute19,
168 x_attribute20 => l_cur_fi_credits.attribute20,
169 x_gl_date => l_cur_fi_credits.gl_date,
170 x_check_number => l_cur_fi_credits.check_number,
171 x_source_transaction_type => l_cur_fi_credits.source_transaction_type,
172 x_source_transaction_ref => l_cur_fi_credits.source_transaction_ref,
173 x_credit_card_payee_cd => l_cur_fi_credits.credit_card_payee_cd,
174 x_credit_card_status_code => l_cur_fi_credits.credit_card_status_code,
175 x_credit_card_tangible_cd => l_cur_fi_credits.credit_card_tangible_cd,
176 x_lockbox_interface_id => l_cur_fi_credits.lockbox_interface_id,
177 x_batch_name => l_cur_fi_credits.batch_name,
178 x_deposit_date => l_cur_fi_credits.deposit_date,
179 x_source_invoice_id => l_cur_fi_credits.source_invoice_id,
180 x_tax_year_code => l_cur_fi_credits.tax_year_code
181 );
182
183
184 -- Create a new activity record, with the debit account to be the credit account and
185 -- the credit account to be the forfeiture account code/ccid. The amount is the same
186 -- as the amount for the base deposit record. Status is set to 'Forfeited'.
187
188 OPEN cur_fi_cr_types(l_cur_fi_credits.credit_type_id);
189 FETCH cur_fi_cr_types INTO l_cur_fi_cr_types;
190 CLOSE cur_fi_cr_types;
191
192 IF igs_fi_gen_005.finp_get_receivables_inst ='Y' THEN
193 l_n_cr_gl_ccid := l_cur_fi_cr_types.forfeiture_gl_ccid;
194 l_n_dr_gl_ccid := l_cur_fi_cr_types.cr_gl_ccid;
195 l_v_cr_account_cd := NULL;
196 l_v_dr_account_cd := NULL;
197 ELSE
198 l_n_cr_gl_ccid := NULL;
199 l_n_dr_gl_ccid := NULL;
200 l_v_cr_account_cd := l_cur_fi_cr_types.forfeiture_account_cd;
201 l_v_dr_account_cd := l_cur_fi_cr_types.cr_account_cd;
202 END IF;
203
204 igs_fi_cr_activities_pkg.insert_row ( x_mode => 'R',
205 x_rowid => l_rowid,
206 x_credit_activity_id => l_n_credit_activity_id,
207 x_credit_id => l_cur_fi_credits.credit_id,
208 x_status => g_forfeited ,
209 x_transaction_date => TRUNC(SYSDATE),
210 x_amount => l_cur_fi_credits.amount,
211 x_dr_account_cd => l_v_dr_account_cd,
212 x_cr_account_cd => l_v_cr_account_cd,
213 x_dr_gl_ccid => l_n_dr_gl_ccid,
214 x_cr_gl_ccid => l_n_cr_gl_ccid,
215 x_bill_id => NULL,
216 x_bill_number => NULL,
217 x_bill_date => NULL,
218 x_gl_date => TRUNC(p_d_gl_date),
219 x_gl_posted_date => NULL,
220 x_posting_id => NULL
221 );
222
223 -- On successful forfeiture, return status as true and message conveying successful transaction
224 p_c_message_name := 'IGS_FI_DP_FORFEITED';
225 p_b_return_status := TRUE;
226
227 END forfeit_deposit;
228
229
230 PROCEDURE reverse_transaction( p_n_credit_id IN NUMBER,
231 p_c_reversal_reason IN VARCHAR2,
232 p_c_reversal_comments IN VARCHAR2,
233 p_d_gl_date IN DATE,
234 p_b_return_status OUT NOCOPY BOOLEAN,
235 p_c_message_name OUT NOCOPY VARCHAR2
236 ) AS
237 ------------------------------------------------------------------
238 --Created by : Priya Athipatla, Oracle IDC
239 --Date created: 26-OCT-2002
240 --
241 --Purpose:
242 --
243 --Known limitations/enhancements and/or remarks:
244 --
245 --Change History:
246 --Who When What
247 --pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
248 -- Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
249 --vvutukur 22-Sep-2003 Enh#3045007.Payment Plans Build. Changes as specified in TD.
250 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified update_row TBH call to credits table to add 3 new columns
251 -- lockbox_interface_id,batch_name,deposit_date.
252 --schodava 11-Jun-03 Enh # 2831587. Modified the Credits table update_row call
253 --schodava 4-Dec-2002 Enh # 2584741 - Deposits Build
254 -------------------------------------------------------------------
255
256 CURSOR cur_lookup_code (cp_reversal_code IN igs_lookup_values.lookup_code%TYPE) IS
257 SELECT 'X'
258 FROM igs_lookup_values
259 WHERE lookup_type = 'IGS_FI_REVERSAL_REASON'
260 AND lookup_code = cp_reversal_code
261 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active,SYSDATE)) AND TRUNC(NVL(end_date_active, SYSDATE))
262 AND NVL(enabled_flag,'N') = 'Y';
263
264 CURSOR cur_fi_credits (cp_credit_id IN igs_fi_credits.credit_id%TYPE) IS
265 SELECT *
266 FROM igs_fi_credits
267 WHERE credit_id = cp_credit_id;
268
269 CURSOR c_deposit (cp_party_id IN igs_fi_credits.party_id%TYPE,
270 cp_receipt_number IN igs_fi_credits.credit_number%TYPE) IS
271 SELECT *
272 FROM igs_fi_credits
273 WHERE party_id = cp_party_id
274 AND credit_number = cp_receipt_number;
275
276 CURSOR cur_fi_cr_types (cp_credit_type_id IN igs_fi_cr_types.credit_type_id%TYPE) IS
277 SELECT *
278 FROM igs_fi_cr_types
279 WHERE credit_type_id = cp_credit_type_id;
280
281 --Cursor for identifying the Payment Plan installment application records.
282 CURSOR cur_insts(cp_n_credit_id igs_fi_credits.credit_id%TYPE,
283 cp_v_plan_status_cd igs_fi_pp_std_attrs.plan_status_code%TYPE
284 ) IS
285 SELECT app.rowid a_rowid,
286 app.installment_application_id a_inst_app_id,
287 app.application_type_code a_appl_typ_cd,
288 app.credit_id a_cr_id,
289 app.credit_activity_id a_cr_acty_id,
290 app.applied_amt a_app_amt,
291 app.transaction_date a_txn_dt,
292 app.link_application_id a_lnk_app_id,
293 inst.rowid i_rowid,
294 inst.installment_id i_inst_id,
295 inst.student_plan_id i_std_pln_id,
296 inst.installment_line_num i_inst_ln_num,
297 inst.due_day i_due_day,
298 inst.due_month_code i_due_mnth_cd,
299 inst.due_year i_due_year,
300 inst.due_date i_due_date,
301 inst.installment_amt i_inst_amt,
302 inst.due_amt i_due_amt,
303 inst.penalty_flag i_pnlty_flg
304 FROM igs_fi_pp_ins_appls app,
305 igs_fi_pp_instlmnts inst,
306 igs_fi_pp_std_attrs std
307 WHERE std.plan_status_code = cp_v_plan_status_cd
308 AND std.student_plan_id = inst.student_plan_id
309 AND inst.installment_id = app.installment_id
310 AND app.credit_id = cp_n_credit_id;
311
312
313 -- cursor rowtype variables
314 l_cur_fi_credits cur_fi_credits%ROWTYPE;
315 rec_deposit c_deposit%ROWTYPE;
316 l_cur_fi_cr_types cur_fi_cr_types%ROWTYPE;
317 rec_dep_type cur_fi_cr_types%ROWTYPE;
318
319 l_v_dummy VARCHAR2(1);
320 l_n_conv_prc_val igs_fi_control_all.conv_process_run_ind%TYPE;
321 l_v_message_name fnd_new_messages.message_name%TYPE;
322 l_v_message_name1 fnd_new_messages.message_name%TYPE := NULL;
323 l_v_credit_number igs_fi_credits.credit_number%TYPE;
324 l_n_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE := NULL;
325 l_rowid ROWID := NULL;
326
327 -- boolean variable for the update balances call
328 l_b_upd_bal BOOLEAN := FALSE;
329 -- boolean variable for updating the deposit txn
330 -- and inserting into the Credit Activities table.
331 l_b_upd_dp_ins_cr BOOLEAN := FALSE;
332
333 l_dr_account_cd igs_fi_cr_activities.dr_account_cd%TYPE;
334 l_cr_account_cd igs_fi_cr_activities.cr_account_cd%TYPE;
335 l_dr_gl_ccid igs_fi_cr_activities.dr_gl_ccid%TYPE;
336 l_cr_gl_ccid igs_fi_cr_activities.cr_gl_ccid%TYPE;
337 l_c_status igs_fi_credits.status%TYPE;
338 l_n_unapp_amt igs_fi_credits.unapplied_amount%TYPE;
339 l_c_reversal_reason igs_fi_credits.reversal_reason_code%TYPE;
340 l_c_reversal_comments igs_fi_credits.reversal_comments%TYPE;
341 l_d_date DATE;
342 l_n_due_amt igs_fi_pp_instlmnts.due_amt%TYPE;
343 l_n_inst_appl_id igs_fi_pp_ins_appls.installment_application_id%TYPE;
344
345 BEGIN
346
347 -- Check if mandatory parameters are specified
348 IF (p_n_credit_id IS NULL) OR (p_d_gl_date IS NULL) THEN
349 p_c_message_name := 'IGS_UC_NO_MANDATORY_PARAMS';
350 p_b_return_status := FALSE;
351 RETURN;
352 END IF;
353
354 -- If the Holds Balance Conversion Process is running then the generic function
355 -- igs_fi_gen_007.finp_get_conv_prc_run_ind returns 1. Credit Reversal cannot be done
356 -- when the Holds Balance Conversion Process is running. Hence error message is shown to the user
357
358 igs_fi_gen_007.finp_get_conv_prc_run_ind(p_n_conv_process_run_ind => l_n_conv_prc_val,
359 p_v_message_name => l_v_message_name);
360
361 --If there is no record in igs_fi_control table, error message should be displayed to the user.
362 --User need to navigate to System Options Form and insert a record before performing this operation.
363 IF l_v_message_name IS NOT NULL THEN
364 p_c_message_name := l_v_message_name;
365 p_b_return_status := FALSE;
366 RETURN;
367 END IF;
368
369 IF l_n_conv_prc_val = 0 THEN
370
371 -- Validate credit_id, if valid, then obtain the records
372 OPEN cur_fi_credits(p_n_credit_id);
373 FETCH cur_fi_credits INTO l_cur_fi_credits;
374 IF cur_fi_credits%NOTFOUND THEN
375 p_c_message_name := 'IGS_GE_INVALID_VALUE';
376 p_b_return_status := FALSE;
377 CLOSE cur_fi_credits;
378 RETURN;
379 END IF;
380 CLOSE cur_fi_credits;
381
382 -- Validate the reversal reason code to be a valid lookup
383 IF p_c_reversal_reason IS NOT NULL THEN
384 OPEN cur_lookup_code (p_c_reversal_reason);
385 FETCH cur_lookup_code INTO l_v_dummy;
386 IF cur_lookup_code%NOTFOUND THEN
387 p_c_message_name := 'IGS_GE_INVALID_VALUE';
388 p_b_return_status := FALSE;
389 CLOSE cur_lookup_code;
390 RETURN;
391 END IF;
392 CLOSE cur_lookup_code;
393 END IF;
394
395 -- Fetch the credit type record for the passed credit type id
396 OPEN cur_fi_cr_types(l_cur_fi_credits.credit_type_id);
397 FETCH cur_fi_cr_types INTO l_cur_fi_cr_types;
398 CLOSE cur_fi_cr_types;
399
400 -- Following Variable values are common for all except 1 (last) of the 4 conditions below
401 l_c_status := 'REVERSED';
402 l_n_unapp_amt := 0;
403 l_c_reversal_reason := p_c_reversal_reason;
404 l_c_reversal_comments := p_c_reversal_comments;
405 l_d_date := TRUNC(SYSDATE);
406
407 -- Depending on the Credit Class, Status and Instrument the
408 -- logic of the remaining part of this procedure is determined
409
410 -- CASE 1 : Reversal of a Payment, created from the Receipts form/Self Service
411 IF l_cur_fi_cr_types.credit_class IN ('PMT','ONLINE PAYMENT','INSTALLMENT_PAYMENTS')
412 AND l_cur_fi_credits.credit_instrument <> 'DEPOSIT'
413 AND l_cur_fi_credits.status = 'CLEARED' THEN
414 l_dr_account_cd := l_cur_fi_cr_types.dr_account_cd;
415 l_cr_account_cd := l_cur_fi_cr_types.cr_account_cd;
416 l_dr_gl_ccid := l_cur_fi_cr_types.dr_gl_ccid;
417 l_cr_gl_ccid := l_cur_fi_cr_types.cr_gl_ccid;
418 l_b_upd_bal := TRUE;
419 -- CASE 2 : Reversal of a Payment, created by a Transfer of a Deposit.
420 ELSIF l_cur_fi_cr_types.credit_class = 'PMT'
421 AND l_cur_fi_credits.credit_instrument = 'DEPOSIT'
422 AND l_cur_fi_credits.status = 'CLEARED' THEN
423 -- Fetch the corresponding deposit record for the passed credit id (payment) record
424 OPEN c_deposit (cp_party_id => l_cur_fi_credits.party_id,
425 cp_receipt_number => l_cur_fi_credits.source_transaction_ref);
426 FETCH c_deposit INTO rec_deposit;
427 CLOSE c_deposit;
428
429 OPEN cur_fi_cr_types(rec_deposit.credit_type_id);
430 FETCH cur_fi_cr_types INTO rec_dep_type;
431 CLOSE cur_fi_cr_types;
432
433 l_dr_account_cd := rec_dep_type.cr_account_cd;
434 l_cr_account_cd := l_cur_fi_cr_types.cr_account_cd;
435 l_dr_gl_ccid := rec_dep_type.cr_gl_ccid;
436 l_cr_gl_ccid := l_cur_fi_cr_types.cr_gl_ccid;
437 l_b_upd_bal := TRUE;
438 l_b_upd_dp_ins_cr := TRUE;
439 -- CASE 3 : Reversal of a deposit.
440 ELSIF l_cur_fi_cr_types.credit_class IN ('ENRDEPOSIT','OTHDEPOSIT') THEN
441 IF l_cur_fi_credits.status = 'CLEARED' THEN
442 l_dr_account_cd := l_cur_fi_cr_types.dr_account_cd;
443 l_cr_account_cd := l_cur_fi_cr_types.cr_account_cd;
444 l_dr_gl_ccid := l_cur_fi_cr_types.dr_gl_ccid;
445 l_cr_gl_ccid := l_cur_fi_cr_types.cr_gl_ccid;
446 -- Note : both flags l_b_upd_bal and l_b_upd_dp_ins_cr are let to remain FALSE
447 -- CASE 4 : Reversal of a Forfeited Deposit.
448 ELSIF l_cur_fi_credits.status = 'FORFEITED' THEN
449 l_dr_account_cd := l_cur_fi_cr_types.cr_account_cd;
450 l_cr_account_cd := l_cur_fi_cr_types.forfeiture_account_cd;
451 l_dr_gl_ccid := l_cur_fi_cr_types.cr_gl_ccid;
452 l_cr_gl_ccid := l_cur_fi_cr_types.forfeiture_gl_ccid;
453 -- Override the variables to 'CLEARED' and existing unapplied amount
454 l_c_status := 'CLEARED';
455 l_n_unapp_amt := l_cur_fi_credits.unapplied_amount;
456 l_c_reversal_reason := NULL;
457 l_c_reversal_comments := NULL;
458 l_d_date := NULL;
459 -- Note : both flags l_b_upd_bal and l_b_upd_dp_ins_cr are let to remain FALSE
460 END IF;
461 END IF;
462
463 -- If Oracle Financials is installed, then override the account code strings to NULL
464 -- Else, override the flexfields to NULL
465 IF igs_fi_gen_005.finp_get_receivables_inst = 'Y' THEN
466 l_dr_account_cd := NULL;
467 l_cr_account_cd := NULL;
468 ELSE
469 l_dr_gl_ccid := NULL;
470 l_cr_gl_ccid := NULL;
471 END IF;
472
473 -- If all validations are passed (CASE 1,2,3 and 4)
474 -- Update into igs_fi_credits, with appropriate status and unapplied_amount
475 -- gl_date of the record is not updated
476
477 igs_fi_credits_pkg.update_row( x_mode => 'R',
478 x_rowid => l_cur_fi_credits.row_id,
479 x_credit_id => l_cur_fi_credits.credit_id,
480 x_credit_number => l_cur_fi_credits.credit_number,
481 x_status => l_c_status, -- Set to 'REVERSED' for CASE 1,2,3. Set to 'CLEARED' for Case 4.
482 x_credit_source => l_cur_fi_credits.credit_source,
483 x_party_id => l_cur_fi_credits.party_id,
484 x_credit_type_id => l_cur_fi_credits.credit_type_id,
485 x_credit_instrument => l_cur_fi_credits.credit_instrument,
486 x_description => l_cur_fi_credits.description,
487 x_amount => l_cur_fi_credits.amount,
488 x_currency_cd => l_cur_fi_credits.currency_cd,
489 x_exchange_rate => l_cur_fi_credits.exchange_rate,
490 x_transaction_date => l_cur_fi_credits.transaction_date,
491 x_effective_date => l_cur_fi_credits.effective_date,
492 x_reversal_date => l_d_date,
493 x_reversal_reason_code => l_c_reversal_reason,
494 x_reversal_comments => l_c_reversal_comments,
495 x_unapplied_amount => l_n_unapp_amt, -- Set to '0' for CASE 1,2,3. Set to the existing value for Case 4.
496 x_source_transaction_id => l_cur_fi_credits.source_transaction_id,
497 x_receipt_lockbox_number => l_cur_fi_credits.receipt_lockbox_number,
498 x_merchant_id => l_cur_fi_credits.merchant_id,
499 x_credit_card_code => l_cur_fi_credits.credit_card_code,
500 x_credit_card_holder_name => l_cur_fi_credits.credit_card_holder_name,
501 x_credit_card_number => l_cur_fi_credits.credit_card_number,
502 x_credit_card_expiration_date => l_cur_fi_credits.credit_card_expiration_date,
503 x_credit_card_approval_code => l_cur_fi_credits.credit_card_approval_code,
504 x_awd_yr_cal_type => l_cur_fi_credits.awd_yr_cal_type,
505 x_awd_yr_ci_sequence_number => l_cur_fi_credits.awd_yr_ci_sequence_number,
506 x_fee_cal_type => l_cur_fi_credits.fee_cal_type,
507 x_fee_ci_sequence_number => l_cur_fi_credits.fee_ci_sequence_number,
508 x_attribute_category => l_cur_fi_credits.attribute_category,
509 x_attribute1 => l_cur_fi_credits.attribute1,
510 x_attribute2 => l_cur_fi_credits.attribute2,
511 x_attribute3 => l_cur_fi_credits.attribute3,
512 x_attribute4 => l_cur_fi_credits.attribute4,
513 x_attribute5 => l_cur_fi_credits.attribute5,
514 x_attribute6 => l_cur_fi_credits.attribute6,
515 x_attribute7 => l_cur_fi_credits.attribute7,
516 x_attribute8 => l_cur_fi_credits.attribute8,
517 x_attribute9 => l_cur_fi_credits.attribute9,
518 x_attribute10 => l_cur_fi_credits.attribute10,
519 x_attribute11 => l_cur_fi_credits.attribute11,
520 x_attribute12 => l_cur_fi_credits.attribute12,
521 x_attribute13 => l_cur_fi_credits.attribute13,
522 x_attribute14 => l_cur_fi_credits.attribute14,
523 x_attribute15 => l_cur_fi_credits.attribute15,
524 x_attribute16 => l_cur_fi_credits.attribute16,
525 x_attribute17 => l_cur_fi_credits.attribute17,
526 x_attribute18 => l_cur_fi_credits.attribute18,
527 x_attribute19 => l_cur_fi_credits.attribute19,
528 x_attribute20 => l_cur_fi_credits.attribute20,
529 x_gl_date => l_cur_fi_credits.gl_date,
530 x_check_number => l_cur_fi_credits.check_number,
531 x_source_transaction_type => l_cur_fi_credits.source_transaction_type,
532 x_source_transaction_ref => l_cur_fi_credits.source_transaction_ref,
533 x_credit_card_payee_cd => l_cur_fi_credits.credit_card_payee_cd,
534 x_credit_card_status_code => l_cur_fi_credits.credit_card_status_code,
535 x_credit_card_tangible_cd => l_cur_fi_credits.credit_card_tangible_cd,
536 x_lockbox_interface_id => l_cur_fi_credits.lockbox_interface_id,
537 x_batch_name => l_cur_fi_credits.batch_name,
538 x_deposit_date => l_cur_fi_credits.deposit_date,
539 x_source_invoice_id => l_cur_fi_credits.source_invoice_id,
540 x_tax_year_code => l_cur_fi_credits.tax_year_code
541 );
542
543 -- Insert into the activities table with gl_date as the gl_date used in the reversal
544 -- and with appropriate status. Transaction date would be the system date.
545 -- Amount field is negated.
546
547 igs_fi_cr_activities_pkg.insert_row ( x_mode => 'R',
548 x_rowid => l_rowid,
549 x_credit_activity_id => l_n_credit_activity_id,
550 x_credit_id => l_cur_fi_credits.credit_id,
551 x_status => l_c_status, -- Set to 'REVERSED' for CASE 1,2,3. Set to 'CLEARED' for Case 4.
552 x_transaction_date => TRUNC(SYSDATE),
553 x_amount => ((-1) * l_cur_fi_credits.amount),
554 x_dr_account_cd => l_dr_account_cd,
555 x_cr_account_cd => l_cr_account_cd,
556 x_dr_gl_ccid => l_dr_gl_ccid,
557 x_cr_gl_ccid => l_cr_gl_ccid,
558 x_bill_id => NULL,
559 x_bill_number => NULL,
560 x_bill_date => NULL,
561 x_gl_date => TRUNC(p_d_gl_date),
562 x_gl_posted_date => NULL,
563 x_posting_id => NULL
564 );
565 -- Call to update the holds and standard balance (CASE 1 and 2)
566 -- only if the l_b_upd_bal flag is TRUE
567 IF l_b_upd_bal THEN
568 -- Update the holds and standard balance accordingly with the reversed amount
569 -- Balance date is updated to sysdate
570
571 -- Update balances of balance type 'STANDARD'
572 igs_fi_prc_balances.update_balances ( p_party_id => l_cur_fi_credits.party_id,
573 p_balance_type => 'STANDARD',
574 p_balance_date => TRUNC(SYSDATE),
575 p_amount => ABS(NVL(l_cur_fi_credits.amount,0)), --Amount always passed as +ve
576 p_source => 'CREDIT',
577 p_source_id => p_n_credit_id,
578 p_message_name => l_v_message_name1
579 ) ;
580 IF l_v_message_name1 IS NOT NULL THEN
581 p_c_message_name := l_v_message_name1;
582 p_b_return_status := FALSE;
583 RETURN;
584 END IF;
585
586 -- Update balances of balance type 'HOLDS'
587 igs_fi_prc_balances.update_balances ( p_party_id => l_cur_fi_credits.party_id,
588 p_balance_type => 'HOLDS',
589 p_balance_date => TRUNC(SYSDATE),
590 p_amount => ABS(NVL(l_cur_fi_credits.amount,0)), --Amount always passed as +ve
591 p_source => 'CREDIT',
592 p_source_id => p_n_credit_id,
593 p_message_name => l_v_message_name1
594 ) ;
595
596 IF l_v_message_name1 IS NOT NULL THEN
597 p_c_message_name := l_v_message_name1;
598 p_b_return_status := FALSE;
599 RETURN;
600 END IF;
601 END IF; -- For l_b_upd_bal
602
603 -- For a reversal of a credit, which is created due to the transfer of a deposit, (CASE 2 only)
604 -- the corresponding deposit record of the credit in context should be updated.
605 IF l_b_upd_dp_ins_cr THEN
606
607 igs_fi_credits_pkg.update_row (
608 x_mode => 'R',
609 x_rowid => rec_deposit.row_id,
610 x_credit_id => rec_deposit.credit_id,
611 x_credit_number => rec_deposit.credit_number ,
612 x_status => 'CLEARED',
613 x_credit_source => rec_deposit.credit_source,
614 x_party_id => rec_deposit.party_id,
615 x_credit_type_id => rec_deposit.credit_type_id,
616 x_credit_instrument => rec_deposit.credit_instrument,
617 x_description => rec_deposit.description,
618 x_amount => rec_deposit.amount,
619 x_currency_cd => rec_deposit.currency_cd,
620 x_exchange_rate => rec_deposit.exchange_rate,
621 x_transaction_date => rec_deposit.transaction_date,
622 x_effective_date => rec_deposit.effective_date,
623 x_reversal_date => rec_deposit.reversal_date,
624 x_reversal_reason_code => rec_deposit.reversal_reason_code,
625 x_reversal_comments => rec_deposit.reversal_comments,
626 x_unapplied_amount => rec_deposit.unapplied_amount,
627 x_source_transaction_id => rec_deposit.source_transaction_id,
628 x_receipt_lockbox_number => rec_deposit.receipt_lockbox_number,
629 x_merchant_id => rec_deposit.merchant_id,
630 x_credit_card_code => rec_deposit.credit_card_code,
631 x_credit_card_holder_name => rec_deposit.credit_card_holder_name,
632 x_credit_card_number => rec_deposit.credit_card_number,
633 x_credit_card_expiration_date => rec_deposit.credit_card_expiration_date,
634 x_credit_card_approval_code => rec_deposit.credit_card_approval_code,
635 x_awd_yr_cal_type => rec_deposit.awd_yr_cal_type,
636 x_awd_yr_ci_sequence_number => rec_deposit.awd_yr_ci_sequence_number,
637 x_fee_cal_type => rec_deposit.fee_cal_type,
638 x_fee_ci_sequence_number => rec_deposit.fee_ci_sequence_number,
639 x_attribute_category => rec_deposit.attribute_category,
640 x_attribute1 => rec_deposit.attribute1,
641 x_attribute2 => rec_deposit.attribute2,
642 x_attribute3 => rec_deposit.attribute3,
643 x_attribute4 => rec_deposit.attribute4,
644 x_attribute5 => rec_deposit.attribute5,
645 x_attribute6 => rec_deposit.attribute6,
646 x_attribute7 => rec_deposit.attribute7,
647 x_attribute8 => rec_deposit.attribute8,
648 x_attribute9 => rec_deposit.attribute9,
649 x_attribute10 => rec_deposit.attribute10,
650 x_attribute11 => rec_deposit.attribute11,
651 x_attribute12 => rec_deposit.attribute12,
652 x_attribute13 => rec_deposit.attribute13,
653 x_attribute14 => rec_deposit.attribute14,
654 x_attribute15 => rec_deposit.attribute15,
655 x_attribute16 => rec_deposit.attribute16,
656 x_attribute17 => rec_deposit.attribute17,
657 x_attribute18 => rec_deposit.attribute18,
658 x_attribute19 => rec_deposit.attribute19,
659 x_attribute20 => rec_deposit.attribute20,
660 x_gl_date => rec_deposit.gl_date,
661 x_check_number => rec_deposit.check_number,
662 x_source_transaction_type => rec_deposit.source_transaction_type,
663 x_source_transaction_ref => rec_deposit.source_transaction_ref,
664 x_credit_card_payee_cd => rec_deposit.credit_card_payee_cd,
665 x_credit_card_status_code => rec_deposit.credit_card_status_code,
666 x_credit_card_tangible_cd => rec_deposit.credit_card_tangible_cd,
667 x_lockbox_interface_id => rec_deposit.lockbox_interface_id,
668 x_batch_name => rec_deposit.batch_name,
669 x_deposit_date => rec_deposit.deposit_date,
670 x_source_invoice_id => rec_deposit.source_invoice_id,
671 x_tax_year_code => rec_deposit.tax_year_code
672 );
673
674 l_rowid := NULL;
675 l_n_credit_activity_id := NULL;
676 igs_fi_cr_activities_pkg.insert_row ( x_mode => 'R',
677 x_rowid => l_rowid,
678 x_credit_activity_id => l_n_credit_activity_id,
679 x_credit_id => rec_deposit.credit_id,
680 x_status => 'CLEARED',
681 x_transaction_date => TRUNC(SYSDATE),
682 x_amount => rec_deposit.amount,
683 x_dr_account_cd => NULL,
684 x_cr_account_cd => NULL,
685 x_dr_gl_ccid => NULL,
686 x_cr_gl_ccid => NULL,
687 x_bill_id => NULL,
688 x_bill_number => NULL,
689 x_bill_date => NULL,
690 x_gl_date => NULL,
691 x_gl_posted_date => NULL,
692 x_posting_id => NULL
693 );
694 END IF;
695
696
697 --If the credit class is Installment Payments then...
698 IF l_cur_fi_cr_types.credit_class = 'INSTALLMENT_PAYMENTS' THEN
699 --Fetch the details of payment plan installments and corresponding application records and loop through them.
700 --if no records are found, nothing needs to be done.
701 FOR rec_cur_insts IN cur_insts(p_n_credit_id,'ACTIVE') LOOP
702 --Calculate the due amount of each installment to be updated.
703 --Due amount to be updated is the sum of the current due amount and already applied amount for that
704 --installment.
705 l_n_due_amt := rec_cur_insts.i_due_amt + rec_cur_insts.a_app_amt;
706
707 --Update the due amt of payment plan installment with the value calculated as above. Because
708 --of the unapplication of the installment, the installment balance for the person should be increased.
709 igs_fi_pp_instlmnts_pkg.update_row(
710 x_rowid => rec_cur_insts.i_rowid,
711 x_installment_id => rec_cur_insts.i_inst_id,
712 x_student_plan_id => rec_cur_insts.i_std_pln_id,
713 x_installment_line_num => rec_cur_insts.i_inst_ln_num,
714 x_due_day => rec_cur_insts.i_due_day,
715 x_due_month_code => rec_cur_insts.i_due_mnth_cd,
716 x_due_year => rec_cur_insts.i_due_year,
717 x_due_date => rec_cur_insts.i_due_date,
718 x_installment_amt => rec_cur_insts.i_inst_amt,
719 x_due_amt => l_n_due_amt,
720 x_penalty_flag => rec_cur_insts.i_pnlty_flg,
721 x_mode => 'R'
722 );
723
724 --Create an Payment Plan unapplication record with the amount being negative value of the
725 --amount previously applied and with application type code being 'UNAPP' and link application
726 --id being the application id of the respective application record.
727 l_rowid := NULL;
728 l_n_inst_appl_id := NULL;
729
730 igs_fi_pp_ins_appls_pkg.insert_row(
731 x_rowid => l_rowid,
732 x_installment_application_id => l_n_inst_appl_id,
733 x_application_type_code => 'UNAPP',
734 x_installment_id => rec_cur_insts.i_inst_id,
735 x_credit_id => p_n_credit_id,
736 x_credit_activity_id => l_n_credit_activity_id,
737 x_applied_amt => -1 * rec_cur_insts.a_app_amt,
738 x_transaction_date => TRUNC(SYSDATE),
739 x_link_application_id => rec_cur_insts.a_inst_app_id,
740 x_mode => 'R'
741 );
742 END LOOP;
743 END IF;
744 -- If the Holds Balance Conversion Process is running then the generic function
745 -- igs_fi_gen_007.finp_get_conv_prc_run_ind returns 1. Credit Reversal cannot be done
746 -- when the Holds Balance Conversion Process is running.Hence error message is shown
747 -- user.
748 ELSE -- l_con_prc_val = 1
749 p_c_message_name := 'IGS_FI_REASS_BAL_PRC_RUN';
750 p_b_return_status := FALSE;
751 RETURN;
752 END IF; -- end if for 'l_con_prc_val = 0'
753
754 p_b_return_status := TRUE;
755 p_c_message_name := NULL;
756
757 END reverse_transaction;
758
759
760 PROCEDURE transfer_deposit( p_n_credit_id IN NUMBER,
761 p_d_gl_date IN DATE,
762 p_b_return_status OUT NOCOPY BOOLEAN,
763 p_c_message_name OUT NOCOPY VARCHAR2,
764 p_c_receipt_number OUT NOCOPY VARCHAR2
765 ) AS
766 ------------------------------------------------------------------
767 --Created by : Priya Athipatla, Oracle IDC
768 --Date created: 08-DEC-2002
769 --
770 --Purpose: To transfer a deposit - Deposit transferred and a credit
771 -- created for the deposit amount
772 --
773 --Known limitations/enhancements and/or remarks:
774 --
775 --Change History:
776 --Who When What
777 --svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number
778 -- Modified logic for credit_number.
779 --pathipat 21-Apr-2004 Enh 3558549 - Comm Receivables Enh
780 -- Added param x_source_invoice_id in call to igs_fi_credits_pkg.update_row()
781 --schodava 06-Oct-2003 Bug # 3123405. The call to update balances is modified.
782 --vvutukur 16-Jun-2003 Enh#2831582.Lockbox Build.Modified update_row TBH call to credits table to add 3 new columns
783 -- lockbox_interface_id,batch_name,deposit_date.
784 --schodava 11-Jun-03 Enh # 2831587. Modified the Credits table insert_row, update_row calls
785 --vvutukur 09-Apr-2003 Enh#2831554.Internal Credits API Build. Removed cursor cur_pay_credit_type and its usage,instead called a generic
786 -- procedure igs_fi_crdapi_util.validate_dep_crtype,which serves the same purpose of validating the payment credit type.
787 -------------------------------------------------------------------
788 CURSOR cur_fi_credits (cp_credit_id NUMBER ) IS
789 SELECT cre.rowid,cre.*
790 FROM igs_fi_credits cre
791 WHERE credit_id = cp_credit_id
792 AND status = g_cleared;
793
794
795 -- Cursor to obtain the debit account info for the new credit created
796 CURSOR cur_account_info (cp_credit_type_id IN NUMBER) IS
797 SELECT cr_account_cd, cr_gl_ccid
798 FROM igs_fi_cr_types
799 WHERE credit_type_id = cp_credit_type_id;
800
801 l_cur_fi_credits cur_fi_credits%ROWTYPE;
802 l_cur_dr_account_info cur_account_info%ROWTYPE;
803 l_cur_cr_account_info cur_account_info%ROWTYPE;
804 l_rowid VARCHAR2(25) := NULL;
805 l_n_credit_activity_id igs_fi_cr_activities.credit_activity_id%TYPE := NULL;
806 l_n_credit_id igs_fi_credits_all.credit_id%TYPE;
807 l_v_credit_number igs_fi_credits_all.credit_number%TYPE;
808 l_n_dr_gl_ccid igs_fi_cr_types.dr_gl_ccid%TYPE;
809 l_n_cr_gl_ccid igs_fi_cr_types.cr_gl_ccid%TYPE;
810 l_v_dr_account_cd igs_fi_cr_types.dr_account_cd%TYPE;
811 l_v_cr_account_cd igs_fi_cr_types.cr_account_cd%TYPE;
812 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
813 l_n_conv_prc_run_ind igs_fi_control_all.conv_process_run_ind%TYPE;
814
815 l_n_pay_credit_type_id igs_fi_cr_types_all.credit_type_id%TYPE;
816 l_b_return_status BOOLEAN;
817
818 BEGIN
819
820 -- If the Holds Conversion process is running, Transfer cannot be performed as it updates
821 -- balances after successful transfer. So error out in Holds Conversion is running
822 igs_fi_gen_007.finp_get_conv_prc_run_ind( p_n_conv_process_run_ind => l_n_conv_prc_run_ind,
823 p_v_message_name => l_v_message_name
824 );
825 IF l_n_conv_prc_run_ind = 1 THEN
826 p_c_message_name := 'IGS_FI_REASS_BAL_PRC_RUN';
827 p_b_return_status := FALSE;
828 p_c_receipt_number := NULL;
829 RETURN;
830 ELSIF l_v_message_name IS NOT NULL THEN
831 p_c_message_name := l_v_message_name;
832 p_b_return_status := FALSE;
833 p_c_receipt_number := NULL;
834 RETURN;
835 END IF;
836
837 -- Check if mandatory parameters are specified
838 IF (p_n_credit_id IS NULL) OR (p_d_gl_date IS NULL) THEN
839 p_c_message_name := 'IGS_UC_NO_MANDATORY_PARAMS';
840 p_b_return_status := FALSE;
841 p_c_receipt_number := NULL;
842 RETURN;
843 END IF;
844
845 -- Validate credit_id, if valid, then obtain the records
846 OPEN cur_fi_credits(p_n_credit_id);
847 FETCH cur_fi_credits INTO l_cur_fi_credits;
848 IF cur_fi_credits%NOTFOUND THEN
849 p_c_message_name := 'IGS_GE_INVALID_VALUE';
850 p_b_return_status := FALSE;
851 p_c_receipt_number := NULL;
852 CLOSE cur_fi_credits;
853 RETURN;
854 END IF;
855 CLOSE cur_fi_credits;
856
857 --Validate the payment credit type.
858 igs_fi_crdapi_util.validate_dep_crtype( p_n_credit_type_id => l_cur_fi_credits.credit_type_id,
859 p_n_pay_credit_type_id => l_n_pay_credit_type_id,
860 p_b_return_stat => l_b_return_status
861 );
862
863 --If not valid, then error out.
864 IF l_b_return_status = FALSE THEN
865 p_c_message_name := 'IGS_FI_PCT_DCT_INVALID';
866 p_b_return_status := FALSE;
867 p_c_receipt_number := NULL;
868 RETURN;
869 END IF;
870
871 -- Update the record in the credits table with a status of 'TRANSFERRED'
872 igs_fi_credits_pkg.update_row ( x_mode => 'R',
873 x_rowid => l_cur_fi_credits.rowid,
874 x_credit_id => l_cur_fi_credits.credit_id,
875 x_credit_number => l_cur_fi_credits.credit_number ,
876 x_status => g_transferred,
877 x_credit_source => l_cur_fi_credits.credit_source,
878 x_party_id => l_cur_fi_credits.party_id,
879 x_credit_type_id => l_cur_fi_credits.credit_type_id,
880 x_credit_instrument => l_cur_fi_credits.credit_instrument,
881 x_description => l_cur_fi_credits.description,
882 x_amount => l_cur_fi_credits.amount,
883 x_currency_cd => l_cur_fi_credits.currency_cd,
884 x_exchange_rate => l_cur_fi_credits.exchange_rate,
885 x_transaction_date => l_cur_fi_credits.transaction_date,
886 x_effective_date => l_cur_fi_credits.effective_date,
887 x_reversal_date => l_cur_fi_credits.reversal_date,
888 x_reversal_reason_code => l_cur_fi_credits.reversal_reason_code,
889 x_reversal_comments => l_cur_fi_credits.reversal_comments,
890 x_unapplied_amount => l_cur_fi_credits.unapplied_amount,
891 x_source_transaction_id => l_cur_fi_credits.source_transaction_id,
892 x_receipt_lockbox_number => l_cur_fi_credits.receipt_lockbox_number,
893 x_merchant_id => l_cur_fi_credits.merchant_id,
894 x_credit_card_code => l_cur_fi_credits.credit_card_code,
895 x_credit_card_holder_name => l_cur_fi_credits.credit_card_holder_name,
896 x_credit_card_number => l_cur_fi_credits.credit_card_number,
897 x_credit_card_expiration_date => l_cur_fi_credits.credit_card_expiration_date,
898 x_credit_card_approval_code => l_cur_fi_credits.credit_card_approval_code,
899 x_awd_yr_cal_type => l_cur_fi_credits.awd_yr_cal_type,
900 x_awd_yr_ci_sequence_number => l_cur_fi_credits.awd_yr_ci_sequence_number,
901 x_fee_cal_type => l_cur_fi_credits.fee_cal_type,
902 x_fee_ci_sequence_number => l_cur_fi_credits.fee_ci_sequence_number,
903 x_attribute_category => l_cur_fi_credits.attribute_category,
904 x_attribute1 => l_cur_fi_credits.attribute1,
905 x_attribute2 => l_cur_fi_credits.attribute2,
906 x_attribute3 => l_cur_fi_credits.attribute3,
907 x_attribute4 => l_cur_fi_credits.attribute4,
908 x_attribute5 => l_cur_fi_credits.attribute5,
909 x_attribute6 => l_cur_fi_credits.attribute6,
910 x_attribute7 => l_cur_fi_credits.attribute7,
911 x_attribute8 => l_cur_fi_credits.attribute8,
912 x_attribute9 => l_cur_fi_credits.attribute9,
913 x_attribute10 => l_cur_fi_credits.attribute10,
914 x_attribute11 => l_cur_fi_credits.attribute11,
915 x_attribute12 => l_cur_fi_credits.attribute12,
916 x_attribute13 => l_cur_fi_credits.attribute13,
917 x_attribute14 => l_cur_fi_credits.attribute14,
918 x_attribute15 => l_cur_fi_credits.attribute15,
919 x_attribute16 => l_cur_fi_credits.attribute16,
920 x_attribute17 => l_cur_fi_credits.attribute17,
921 x_attribute18 => l_cur_fi_credits.attribute18,
922 x_attribute19 => l_cur_fi_credits.attribute19,
923 x_attribute20 => l_cur_fi_credits.attribute20,
924 x_gl_date => l_cur_fi_credits.gl_date,
925 x_check_number => l_cur_fi_credits.check_number,
926 x_source_transaction_type => l_cur_fi_credits.source_transaction_type,
927 x_source_transaction_ref => l_cur_fi_credits.source_transaction_ref,
928 x_credit_card_payee_cd => l_cur_fi_credits.credit_card_payee_cd,
929 x_credit_card_status_code => l_cur_fi_credits.credit_card_status_code,
930 x_credit_card_tangible_cd => l_cur_fi_credits.credit_card_tangible_cd,
931 x_lockbox_interface_id => l_cur_fi_credits.lockbox_interface_id,
932 x_batch_name => l_cur_fi_credits.batch_name,
933 x_deposit_date => l_cur_fi_credits.deposit_date,
934 x_source_invoice_id => l_cur_fi_credits.source_invoice_id,
935 x_tax_year_code => l_cur_fi_credits.tax_year_code
936 );
937
938 -- Create a new activities record with status 'Transferred' and with
939 -- NULL accounting information
940
941 igs_fi_cr_activities_pkg.insert_row ( x_mode => 'R',
942 x_rowid => l_rowid,
943 x_credit_activity_id => l_n_credit_activity_id,
944 x_credit_id => l_cur_fi_credits.credit_id,
945 x_status => g_transferred,
946 x_transaction_date => TRUNC(SYSDATE),
947 x_amount => l_cur_fi_credits.amount,
948 x_dr_account_cd => NULL,
949 x_cr_account_cd => NULL,
950 x_dr_gl_ccid => NULL,
951 x_cr_gl_ccid => NULL,
952 x_bill_id => NULL,
953 x_bill_number => NULL,
954 x_bill_date => NULL,
955 x_gl_date => NULL,
956 x_gl_posted_date => NULL,
957 x_posting_id => NULL
958 );
959
960
961 -- Create a payment record in igs_fi_Credits and an activity correspondingly in igs_fi_cr_activities
962 -- Account details obtained
963 l_rowid := NULL;
964 l_n_credit_id := NULL;
965 l_v_credit_number := NULL;
966
967 igs_fi_credits_pkg.insert_row ( x_mode => 'R',
968 x_rowid => l_rowid,
969 x_credit_id => l_n_credit_id,
970 x_credit_number => l_v_credit_number ,
971 x_status => g_cleared,
972 x_credit_source => NULL,
973 x_party_id => l_cur_fi_credits.party_id,
974 x_credit_type_id => l_n_pay_credit_type_id,
975 x_credit_instrument => g_deposit,
976 x_description => l_cur_fi_credits.description,
977 x_amount => l_cur_fi_credits.amount,
978 x_currency_cd => l_cur_fi_credits.currency_cd,
979 x_exchange_rate => l_cur_fi_credits.exchange_rate,
980 x_transaction_date => TRUNC(SYSDATE),
981 x_effective_date => TRUNC(SYSDATE),
982 x_reversal_date => NULL,
983 x_reversal_reason_code => NULL,
984 x_reversal_comments => NULL,
985 x_unapplied_amount => l_cur_fi_credits.unapplied_amount,
986 x_source_transaction_id => NULL,
987 x_receipt_lockbox_number => NULL,
988 x_merchant_id => NULL,
989 x_credit_card_code => NULL,
990 x_credit_card_holder_name => NULL,
991 x_credit_card_number => NULL,
992 x_credit_card_expiration_date => NULL,
993 x_credit_card_approval_code => NULL,
994 x_awd_yr_cal_type => NULL,
995 x_awd_yr_ci_sequence_number => NULL,
996 x_fee_cal_type => l_cur_fi_credits.fee_cal_type,
997 x_fee_ci_sequence_number => l_cur_fi_credits.fee_ci_sequence_number,
998 x_attribute_category => l_cur_fi_credits.attribute_category,
999 x_attribute1 => l_cur_fi_credits.attribute1,
1000 x_attribute2 => l_cur_fi_credits.attribute2,
1001 x_attribute3 => l_cur_fi_credits.attribute3,
1002 x_attribute4 => l_cur_fi_credits.attribute4,
1003 x_attribute5 => l_cur_fi_credits.attribute5,
1004 x_attribute6 => l_cur_fi_credits.attribute6,
1005 x_attribute7 => l_cur_fi_credits.attribute7,
1006 x_attribute8 => l_cur_fi_credits.attribute8,
1007 x_attribute9 => l_cur_fi_credits.attribute9,
1008 x_attribute10 => l_cur_fi_credits.attribute10,
1009 x_attribute11 => l_cur_fi_credits.attribute11,
1010 x_attribute12 => l_cur_fi_credits.attribute12,
1011 x_attribute13 => l_cur_fi_credits.attribute13,
1012 x_attribute14 => l_cur_fi_credits.attribute14,
1013 x_attribute15 => l_cur_fi_credits.attribute15,
1014 x_attribute16 => l_cur_fi_credits.attribute16,
1015 x_attribute17 => l_cur_fi_credits.attribute17,
1016 x_attribute18 => l_cur_fi_credits.attribute18,
1017 x_attribute19 => l_cur_fi_credits.attribute19,
1018 x_attribute20 => l_cur_fi_credits.attribute20,
1019 x_gl_date => p_d_gl_date,
1020 x_check_number => NULL,
1021 x_source_transaction_type => g_deposit,
1022 x_source_transaction_ref => l_cur_fi_credits.credit_number,
1023 x_credit_card_payee_cd => l_cur_fi_credits.credit_card_payee_cd,
1024 x_credit_card_status_code => l_cur_fi_credits.credit_card_status_code,
1025 x_credit_card_tangible_cd => l_cur_fi_credits.credit_card_tangible_cd,
1026 x_lockbox_interface_id => l_cur_fi_credits.lockbox_interface_id,
1027 x_batch_name => l_cur_fi_credits.batch_name,
1028 x_deposit_date => l_cur_fi_credits.deposit_date,
1029 x_source_invoice_id => l_cur_fi_credits.source_invoice_id,
1030 x_tax_year_code => NULL
1031 );
1032
1033 -- Derive the credit and debit accounting information
1034 -- The debit account will be the credit account of the base deposit record
1035 -- The credit account will be the credit account of the payment_credit_type_id
1036 -- attached to the base credit_type_id
1037
1038 -- Using the credit_type_id of the base deposit, get the debit information
1039 OPEN cur_account_info(l_cur_fi_credits.credit_type_id);
1040 FETCH cur_account_info INTO l_cur_dr_account_info;
1041 CLOSE cur_account_info;
1042
1043 -- For credit info, pass the payment_credit_type_id attached to the base credit_type_id
1044 OPEN cur_account_info(l_n_pay_credit_type_id);
1045 FETCH cur_account_info INTO l_cur_cr_account_info;
1046 CLOSE cur_account_info;
1047
1048 -- Copy the ccid/account_cd depending on whether Financials is installed or not
1049 IF igs_fi_gen_005.finp_get_receivables_inst ='Y' THEN
1050 l_n_dr_gl_ccid := l_cur_dr_account_info.cr_gl_ccid;
1051 l_n_cr_gl_ccid := l_cur_cr_account_info.cr_gl_ccid;
1052 l_v_dr_account_cd := NULL;
1053 l_v_cr_account_cd := NULL;
1054 ELSE
1055 l_n_dr_gl_ccid := NULL;
1056 l_n_cr_gl_ccid := NULL;
1057 l_v_dr_account_cd := l_cur_dr_account_info.cr_account_cd;
1058 l_v_cr_account_cd := l_cur_cr_account_info.cr_account_cd;
1059 END IF;
1060
1061 l_rowid := NULL;
1062 l_n_credit_activity_id := NULL;
1063
1064 igs_fi_cr_activities_pkg.insert_row ( x_mode => 'R',
1065 x_rowid => l_rowid,
1066 x_credit_activity_id => l_n_credit_activity_id,
1067 x_credit_id => l_n_credit_id,
1068 x_status => g_cleared,
1069 x_transaction_date => TRUNC(SYSDATE),
1070 x_amount => l_cur_fi_credits.amount,
1071 x_dr_account_cd => l_v_dr_account_cd,
1072 x_cr_account_cd => l_v_cr_account_cd,
1073 x_dr_gl_ccid => l_n_dr_gl_ccid,
1074 x_cr_gl_ccid => l_n_cr_gl_ccid,
1075 x_bill_id => NULL,
1076 x_bill_number => NULL,
1077 x_bill_date => NULL,
1078 x_gl_date => p_d_gl_date,
1079 x_gl_posted_date => NULL,
1080 x_posting_id => NULL
1081 );
1082
1083
1084 -- Call update balances to update holds and standard balances
1085 -- Update balances of balance type 'STANDARD'
1086 igs_fi_prc_balances.update_balances ( p_party_id => l_cur_fi_credits.party_id,
1087 p_balance_type => 'STANDARD',
1088 p_balance_date => TRUNC(SYSDATE),
1089 p_amount => NVL((-1)*l_cur_fi_credits.amount,0), --Amount always passed as -ve
1090 p_source => 'CREDIT',
1091 p_source_id => l_n_credit_id,
1092 p_message_name => l_v_message_name
1093 ) ;
1094 IF l_v_message_name IS NOT NULL THEN
1095 p_c_message_name := l_v_message_name;
1096 p_b_return_status := FALSE;
1097 p_c_receipt_number := NULL;
1098 RETURN;
1099 END IF;
1100
1101 -- Update balances of balance type 'HOLDS'
1102 igs_fi_prc_balances.update_balances ( p_party_id => l_cur_fi_credits.party_id,
1103 p_balance_type => 'HOLDS',
1104 p_balance_date => TRUNC(SYSDATE),
1105 p_amount => NVL((-1)*l_cur_fi_credits.amount,0), --Amount always passed as +ve
1106 p_source => 'CREDIT',
1107 p_source_id => l_n_credit_id,
1108 p_message_name => l_v_message_name
1109 ) ;
1110
1111 IF l_v_message_name IS NOT NULL THEN
1112 p_c_message_name := l_v_message_name;
1113 p_b_return_status := FALSE;
1114 p_c_receipt_number := NULL;
1115 RETURN;
1116 END IF;
1117
1118 -- If payment creation was successful, return with success message and the
1119 -- credit number of the new receipt created.
1120 p_c_message_name := 'IGS_FI_DP_TRANSFERRED';
1121 p_b_return_status := TRUE;
1122 p_c_receipt_number := l_v_credit_number;
1123
1124 END transfer_deposit;
1125
1126 END igs_fi_deposits_prcss;