4 g_module_name VARCHAR2(100) := 'fv.plsql.FV_LOCKBOX_FC_PKG.';
1 PACKAGE BODY fv_lockbox_FC_pkg AS
2 --$Header: FVDCLKBB.pls 120.7 2005/10/21 11:19:28 kbhatt noship $
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
5
6
7 -- -----------------------------------------------------------------------
8 -- Variable Declarations
9 -- -----------------------------------------------------------------------
10 v_test number;
11 v_transmission_id ar_transmissions_all.transmission_id%TYPE;
12 v_batch_id ar_batches.batch_id%TYPE;
13 v_org_id ar_batches.org_id%TYPE;
14 v_invoice_id ra_customer_trx.customer_trx_id%TYPE;
15 v_temp_id fv_lockbox_ipa_temp.temp_id%TYPE;
16 v_debit_memo_id ra_customer_trx.customer_trx_id%TYPE;
17 v_amount ar_payment_schedules.amount_due_remaining%TYPE;
18 v_priority fv_finance_charge_controls.priority%TYPE;
19 v_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE;
20 v_appl_inv_id ra_customer_trx.customer_trx_id%TYPE;
21 v_appl_inv_ps_id ar_payment_schedules.payment_schedule_id%TYPE;
22 v_sold_to_customer ar_interim_cash_rcpt_lines_all.sold_to_customer%TYPE;
23 v_transaction_code ar_interim_cash_receipts.ussgl_transaction_code%TYPE;
24 v_appl_inv_amt number;
25 v_appl_amt_remain number;
26 v_appl_dm_id fv_lockbox_ipa_temp.debit_memo_id%TYPE;
27 v_dm_amt fv_lockbox_ipa_temp.amount%TYPE;
28 v_prioirty fv_lockbox_ipa_temp.priority%TYPE;
29 v_dm_pay_sch_id fv_lockbox_ipa_temp.payment_schedule_id%TYPE;
30 v_origin varchar2(1);
31 v_cash_receipt_id number;
32 v_cash_receipt_line_id_ctr number;
33 v_cash_receipt_line_id number;
34 v_retcode varchar2(1);
35 v_errbuf varchar2(1000);
36
37 -- record creation variables
38 v_created_by number(15) := fnd_global.user_id;
39 v_creation_date date := sysdate;
40 v_last_updated_by number(15) := fnd_global.user_id;
41 v_last_update_date date := sysdate;
42
43
44 -- -----------------------------------------------------------------------
45 -- Cursor Declarations
46 -- -----------------------------------------------------------------------
47
48 cursor c_batches(cv_transmission_id ar_batches.transmission_id%TYPE) is
49 select *
50 from ar_batches
51 where transmission_id = cv_transmission_id;
52
53 cursor c_invoices(cv_batch_id ar_batches.batch_id%TYPE,
54 cv_org_id ar_batches.org_id%TYPE) is
55 /*
56 select distinct(decode(a.customer_trx_id,null,b.customer_trx_id,
57 a.customer_trx_id)) customer_trx_id
58 from ar_interim_cash_receipts a,
59 ar_interim_cash_rcpt_lines_all b
60 where a.batch_id = cv_batch_id
61 and a.cash_receipt_id = b.cash_receipt_id (+)
62 and(a.customer_trx_id is not null or b.customer_trx_id is not null);
63 */
64 select distinct(customer_trx_id) customer_trx_id
65 from ar_interim_cash_receipts
66 where batch_id = cv_batch_id
67 and customer_trx_id is not null
68 union
69 select distinct(customer_trx_id) customer_trx_id
70 from ar_interim_cash_rcpt_lines_all
71 where batch_id = cv_batch_id
72 and org_id = cv_org_id
73 and customer_trx_id is not null;
74
75 cursor c_debit_memos(cv_invoice_id ra_customer_trx.customer_trx_id%TYPE) IS
76 Select aps.customer_trx_id,
77 aps.amount_due_remaining,
78 fcc.priority,
82 fv_finance_charge_controls fcc
79 aps.payment_schedule_id
80 from ra_customer_trx rct,
81 ar_payment_schedules aps,
83 where rct.related_customer_trx_id = cv_invoice_id
84 and aps.customer_trx_id = rct.customer_trx_id
85 and rct.interface_header_attribute3 = fcc.charge_type
86 and rct.set_of_books_id = fcc.set_of_books_id
87 and aps.amount_due_remaining > 0
88 order by fcc.priority ;
89
90 cursor c_receipt_dms(cv_batch_id ar_batches.batch_id%TYPE,
91 cv_org_id ar_interim_cash_receipts.org_id%TYPE) is
92 select sum(amount) amount,
93 aicr.customer_trx_id,
94 aicr.payment_schedule_id
95 from ar_interim_cash_receipts aicr,
96 ra_customer_trx rct,
97 fv_finance_charge_controls fcc
98 where aicr.batch_id = cv_batch_id
99 and aicr.customer_trx_id is not null
100 and aicr.customer_trx_id = rct.customer_trx_id
101 and rct.interface_header_attribute3 = fcc.charge_type
102 and rct.set_of_books_id = fcc.set_of_books_id
103 group by aicr.customer_trx_id, aicr.payment_schedule_id
104 union
105 select sum(payment_amount) amount,
106 aicrl.customer_trx_id,
107 aicrl.payment_schedule_id
108 from ar_interim_cash_rcpt_lines_all aicrl,
109 ra_customer_trx rct,
110 fv_finance_charge_controls fcc
111 where aicrl.batch_id = cv_batch_id
112 and aicrl.customer_trx_id is not null
113 and aicrl.customer_trx_id = rct.customer_trx_id
114 and rct.interface_header_attribute3 = fcc.charge_type
115 and rct.set_of_books_id = fcc.set_of_books_id
116 and aicrl.org_id = cv_org_id
117 group by aicrl.customer_trx_id, aicrl.payment_schedule_id;
118
119
120 cursor c_receipt_details(cv_batch_id ar_batches.batch_id%TYPE,
121 cv_org_id ar_interim_cash_receipts.org_id%TYPE) is
122 select customer_trx_id, aicr.amount amount, 'R' origin,
123 cash_receipt_id, 0 cash_receipt_line_id, payment_schedule_id,
124 0 sold_to_customer, ussgl_transaction_code
125 from ar_interim_cash_receipts aicr
126 where batch_id = cv_batch_id
127 and customer_trx_id is not null
128 and exists (select 'x' from fv_lockbox_ipa_temp f
129 where f.invoice_id = aicr.customer_trx_id
130 and f.batch_id = aicr.batch_id)
131 union
132 select customer_trx_id, aicrl.payment_amount amount, 'L' origin,
133 cash_receipt_id, cash_receipt_line_id, payment_schedule_id,
134 sold_to_customer, ussgl_transaction_code
135 from ar_interim_cash_rcpt_lines_all aicrl
136 where batch_id = cv_batch_id
137 and org_id = cv_org_id
138 and customer_trx_id is not null
139 and exists (select 'x' from fv_lockbox_ipa_temp f
140 where f.invoice_id = aicrl.customer_trx_id
141 and f.batch_id = aicrl.batch_id);
142
143 cursor c_finchrg_total(cv_trans_id fv_lockbox_ipa_temp.transmission_id%TYPE,
144 cv_batch_id fv_lockbox_ipa_temp.batch_id%TYPE,
145 cv_invoice_id fv_lockbox_ipa_temp.invoice_id%TYPE) is
146 select debit_memo_id, amount, priority, payment_schedule_id
147 from fv_lockbox_ipa_temp
148 where transmission_id = cv_trans_id
149 and batch_id = cv_batch_id
150 and invoice_id = cv_invoice_id
151 order by priority;
152 -- --------------------------------------------------------------------------
153 -- PROCEDURE Main
154 -- --------------------------------------------------------------------------
155
156 PROCEDURE main(x_errbuf OUT NOCOPY varchar2,
157 x_retcode OUT NOCOPY varchar2,
158 x_transmission_id IN NUMBER) AS
159 l_module_name VARCHAR2(200) := g_module_name || 'main';
160 BEGIN
161
162 v_transmission_id := x_transmission_id;
163 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
164 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'STARTING THE MAIN PROCESS. PROCESSING TRANSMISSION '||
165 'ID '||to_char(v_transmission_id));
166 END IF;
167
168 -- clear records out of fv_lockbox_ipa_temp for current transmission
169 delete from fv_lockbox_ipa_temp
170 where transmission_id = v_transmission_id;
171
172 -- finding all batches within the transmission.
173 FOR c_batches_rec IN c_batches(v_transmission_id) LOOP
174
175 v_batch_id := c_batches_rec.batch_id;
176 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING BATCH_ID = '||TO_CHAR(V_BATCH_ID));
178 END IF;
179
180 v_org_id := c_batches_rec.org_id;
181
182 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
183 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FINDING ALL RECEIPTS THAT ARE APPLIED TO INVOICES');
184 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'POPULATING FV_LOCKBOX_IPA_TEMP W/TOTALS OF DEBIT MEMOS');
185 END IF;
186 -- finding all the receipts applied to invoices within the batch
187 FOR c_invoices_rec IN c_invoices(v_batch_id,v_org_id) LOOP
188
189 v_invoice_id := c_invoices_rec.customer_trx_id;
190
191 --finding all debit memos and total for each invoice
195 v_amount := c_debit_memo_rec.amount_due_remaining;
192 FOR c_debit_memo_rec IN c_debit_memos(v_invoice_id) LOOP
193
194 v_debit_memo_id := c_debit_memo_rec.customer_trx_id;
196 v_priority := c_debit_memo_rec.priority;
197 v_payment_schedule_id := c_debit_memo_rec.payment_schedule_id;
198
199 BEGIN
200 SELECT fv_lockbox_ipa_temp_s.nextval
201 INTO v_temp_id
202 FROM dual;
203
204 EXCEPTION
205 WHEN others THEN
206 v_retcode := '2';
207 v_errbuf := 'fv_lockbox_ipa_temp_s '||sqlerrm;
208 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.error1',v_errbuf);
209 ROLLBACK;
210 RAISE;
211 END;
212
213 -- setting up debit memo temp data
214 insert into fv_lockbox_ipa_temp(temp_id,
215 invoice_id,
216 debit_memo_id,
217 amount,
218 priority,
219 batch_id,
220 payment_schedule_id,
221 transmission_id)
222 values(v_temp_id,
223 v_invoice_id,
224 v_debit_memo_id,
225 nvl(v_amount,0),
226 v_priority,
227 v_batch_id,
228 v_payment_schedule_id,
229 v_transmission_id);
230
231 END LOOP; -- c_debit_memos
232
233 END LOOP; -- c_invoices
234
235 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
236 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FINDING RECEIPT WITH APPLICATION TO DEBIT MEMOS');
237 END IF;
238
239 -- find all finance charge debit memos within batch
240 FOR c_receipt_dms_rec IN c_receipt_dms(v_batch_id, v_org_id) LOOP
241
242 -- update the total amount available to apply to receipts for
243 -- any debit memos in the batch. This is being done by finding
244 -- all finance charge debit memo's in the batch and reducing the
245 -- the total amount of the fc dm available to apply.
246
247 update fv_lockbox_ipa_temp
248 set amount = amount - nvl(c_receipt_dms_rec.amount,0)
249 where debit_memo_id = c_receipt_dms_rec.customer_trx_id
250 and batch_id = v_batch_id
251 and transmission_id = v_transmission_id
252 and payment_schedule_id = c_receipt_dms_rec.payment_schedule_id;
253
254 END LOOP; -- c_receipts_dm
255
256 -- process each receipt application to find related finance charge debit
257 -- memos to pay off.
258
259 process_receipt_applications;
260
261 END LOOP; -- c_batches
262
263 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'***COMPLETED PROCESS SUCCESFULLY***');
265 END IF;
266
267 x_retcode := '0';
268 EXCEPTION
269 when others then
270 IF v_errbuf is null THEN
271 x_errbuf := 'Main '||sqlerrm;
272 x_retcode := '2';
273 ELSE
274 x_retcode := v_retcode;
275 x_errbuf := v_errbuf;
276 END If;
277
278 IF c_batches%ISOPEN THEN
279 close c_batches;
280 END IF;
281
282 IF c_invoices%ISOPEN THEN
283 close c_invoices;
284 END IF;
285
286 IF c_debit_memos%ISOPEN THEN
287 close c_debit_memos;
288 END IF;
289
290 IF c_receipt_dms%ISOPEN THEN
291 close c_debit_memos;
292 END IF;
293
294 IF c_receipt_details%ISOPEN THEN
295 close c_receipt_details;
296 END IF;
297
298 IF c_finchrg_total%ISOPEN THEN
302 ROLLBACK;
299 close c_finchrg_total;
300 END IF;
301
303 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',x_errbuf);
304
305
306 END main;
307
308 -- ------------------------------------------------------------------------
309 -- PROCEDURE process_receipt_applications
310 -- ------------------------------------------------------------------------
311 -- This process determines for each receipt applied to an invoice, if
312 -- there are any finance charge debit memos to paid off first. If there
313 -- are they will be received against prior to the prinicipal invoice if
314 -- there is monies left after paying off all outstanding finance charge
315 -- debit memos.
316 -- ------------------------------------------------------------------------
317 PROCEDURE process_receipt_applications is
318 l_module_name VARCHAR2(200) := g_module_name || 'process_receipt_applications';
319
320 BEGIN
321
322 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
323 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'STARTING PROCESS_RECEIPT_APPLICATIONS');
324 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FINDING ALL INVOICE APPLICATIONS WITHIN THE BATCH');
325 END IF;
326
327 -- getting all invoices to pay off within the batch
328 FOR c_receipt_details_rec IN c_receipt_details(v_batch_id, v_org_id) LOOP
329
330 v_origin := c_receipt_details_rec.origin;
331 v_cash_receipt_id := c_receipt_details_rec.cash_receipt_id;
332 v_cash_receipt_line_id := c_receipt_details_rec.cash_receipt_line_id;
333 v_appl_inv_id := c_receipt_details_rec.customer_trx_id;
334 v_appl_inv_ps_id := c_receipt_details_rec.payment_schedule_id;
335 v_appl_inv_amt := c_receipt_details_rec.amount;
336 v_sold_to_customer := c_receipt_details_rec.sold_to_customer;
337 v_transaction_code := c_receipt_details_rec.ussgl_transaction_code;
338
339 v_appl_amt_remain := v_appl_inv_amt;
340
341 -- reset cash_receipt_line_id used during insert_cash_receipt
342 v_cash_receipt_line_id_ctr := 0;
343
344 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
345 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_CASH_RECEIPT_ID = '||TO_CHAR(V_CASH_RECEIPT_ID));
346 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_CASH_RECEIPT_LINE_ID = '
347 ||to_char(v_cash_receipt_line_id));
348 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'V_APPL_INV_AMT = '||TO_CHAR(V_APPL_INV_AMT));
349 END IF;
350
351 -- get finance charge debit memo totals for an invoice
352 OPEN c_finchrg_total(v_transmission_id, v_batch_id,v_appl_inv_id);
353
354 WHILE (v_appl_amt_remain > 0) LOOP
355 FETCH c_finchrg_total INTO v_appl_dm_id,
356 v_dm_amt,
357 v_prioirty,
358 v_dm_pay_sch_id;
359
360 EXIT when c_finchrg_total%NOTFOUND;
361
362 -- amount remaining is >= to the total amount due on the debit memo
363 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
364 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DEBIT MEMO ID = '||TO_CHAR(V_APPL_DM_ID));
365 END IF;
366
367 IF v_appl_amt_remain >= v_dm_amt THEN
368 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
369 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT REMAIN > DM AMT');
370 END IF;
371
372 v_appl_amt_remain := v_appl_amt_remain - v_dm_amt;
373
374 -- insert new cash receipt applied to a finance charge debit memo
375 -- into ar_interim_cash_rcpt_lines_all
376 IF v_dm_amt >0 THEN -- don't want to insert $0 receipts
377 insert_cash_receipt(v_appl_dm_id, v_dm_pay_sch_id, v_dm_amt, null);
378
379 --update temp table subtracting v_dm_amt
380 update_lockbox_temp(v_dm_amt);
381 END IF;
382
383 ELSE -- amount remaining is less than the amount due of the debit memo.
384 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
385 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'AMT REMAIN < DM AMT');
386 END IF;
387
388 IF v_origin = 'R' THEN
389 -- when a receipt comes from ar_interim_cash_receipts table
390 -- and we want to update it and apply it to a debit memo, because
391 -- of form validation we must instead do the following:
392 -- move the invoice appl record to the interim lines table and
393 -- update the original record so that it is 'MULTIPLE' and not
394 -- applied to invoice
395
396 insert_cash_receipt(v_appl_dm_id, v_dm_pay_sch_id,
397 v_appl_amt_remain, null);
398
399 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
400 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE AR_INTERIM_CASH_RECEIPTS TO MULTIPLE');
401 END IF;
402 update ar_interim_cash_receipts
403 set customer_trx_id = null,
404 payment_schedule_id = null,
405 special_type = 'MULTIPLE',
409 where cash_receipt_id = v_cash_receipt_id;
406 amount_applied = null,
407 last_updated_by = v_last_updated_by,
408 last_update_date = v_last_update_date
410
411 ELSE -- v_origin = L
412
413 -- update interim table w/amount remaining and finance charge
414 -- debit memo id
415
416 update_interim_table(v_origin, v_appl_amt_remain,
417 v_appl_dm_id,v_dm_pay_sch_id);
418
419 --since the original receipt appl record is being updated to
420 --to apply to a finance charge debit memo, the ussgl_transaction
421 --_code should be set to null. There should not be a tc
422 --applied to a debit memo.
423
424 update ar_interim_cash_rcpt_lines_all
425 set ussgl_transaction_code = null
426 where cash_receipt_id = v_cash_receipt_id
427 and cash_receipt_line_id = v_cash_receipt_line_id;
428
429 END IF;
430
431 -- update temp table subtract v_appl_amt_remain
432 update_lockbox_temp(v_appl_amt_remain);
433
437 END LOOP; -- c_finchrg_total while
434 v_appl_amt_remain := 0; -- no more to apply
435 END IF;
436
438
439 IF c_finchrg_total%NOTFOUND and (v_appl_amt_remain > 0) and
440 (v_appl_amt_remain <> v_appl_inv_amt) THEN
441
442 -- this is for the case when have paid off all the finance charge debit
443 -- memo and monies still remain to apply. Adjust the original
444 -- invoice application receipt record.
445
446 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
447 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'3RD BRANCH');
448 END IF;
449
450 IF v_origin = 'R' THEN
451 -- when a receipt comes from ar_interim_cash_receipts table
452 -- and we want to update it and apply it to a debit memo, because
453 -- of form validation we must instead do the following:
454 -- move the invoice appl record to the interim lines table and
455 -- update the original record so that it is 'MULTIPLE' and not
456 -- applied to invoice
457
458 insert_cash_receipt(v_appl_inv_id, v_appl_inv_ps_id,
459 v_appl_amt_remain, v_transaction_code);
460
461 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
462 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE AR_INTERIM_CASH_RECEIPTS TO MULTIPLE');
463 END IF;
464 update ar_interim_cash_receipts
465 set customer_trx_id = null,
466 payment_schedule_id = null,
467 special_type = 'MULTIPLE',
468 amount_applied = null,
469 last_updated_by = v_last_updated_by,
470 last_update_date = v_last_update_date
471 where cash_receipt_id = v_cash_receipt_id;
472
473 ELSE -- v_origin = L
474
475 -- update the invoice appl receipt in the interim table with
476 -- the amount remaining
477 update_interim_table(v_origin, v_appl_amt_remain,
478 v_appl_inv_id, v_appl_inv_ps_id);
479 END IF;
480
481 -- update temp table subtract v_appl_amt_remain
482 update_lockbox_temp(v_appl_amt_remain);
483
484 v_appl_amt_remain := 0; -- no more to apply
485
486 END IF;
487 CLOSE c_finchrg_total;
488 END LOOP; -- c_receipt_details
489
490 EXCEPTION
491 WHEN others THEN
492 IF v_retcode is null THEN
493 v_retcode := '2';
494 v_errbuf := 'process_receipt_application '||sqlerrm;
495 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN PROCESS_RECEIPT_APPLICATION - '||SQLERRM);
496 END IF;
497
498 ROLLBACK;
499 RAISE;
500
501 END process_receipt_applications;
502
503 -- ------------------------------------------------------------------------
504 -- PROCEDURE insert_cash_receipt
505 -- ------------------------------------------------------------------------
506 -- This procedures inserts a record into ar_interim_cash_rcpt_lines_all
507 -- for a new receipt.
508 -- ------------------------------------------------------------------------
509 PROCEDURE insert_cash_receipt(v_cust_trx_id IN number,
510 v_pay_sch_id IN number,
511 v_amount IN number,
512 v_ussgl_tran_code IN varchar2) AS
513 l_module_name VARCHAR2(200) := g_module_name || 'insert_cash_receipt';
514
515 BEGIN
516
517 IF v_cash_receipt_line_id_ctr = 0 THEN
518
519 --find current max cash_receipt_line_id for current cash_receipt_id
520 select max(cash_receipt_line_id)
521 into v_cash_receipt_line_id_ctr
525
522 from ar_interim_cash_rcpt_lines_all
523 where batch_id = v_batch_id
524 and cash_receipt_id = v_cash_receipt_id;
526 END IF;
527
528 v_cash_receipt_line_id_ctr := nvl(v_cash_receipt_line_id_ctr,0) + 1;
529 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
530 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CTR = '||TO_CHAR(V_CASH_RECEIPT_LINE_ID_CTR));
531 END IF;
532
533 insert into ar_interim_cash_rcpt_lines_all
534 (cash_receipt_id,
535 cash_receipt_line_id,
536 last_updated_by,
537 last_update_date,
538 created_by,
539 creation_date,
540 sold_to_customer,
541 payment_amount,
542 payment_schedule_id,
543 customer_trx_id,
544 batch_id,
545 ussgl_transaction_code)
546 values
547 (v_cash_receipt_id,
548 v_cash_receipt_line_id_ctr,
549 v_last_updated_by,
550 v_last_update_date,
551 v_created_by,
552 v_creation_date,
553 v_sold_to_customer,
554 v_amount,
555 v_pay_sch_id, -- fin chrg debit memo or invoice_id payment_schedule_id
556 v_cust_trx_id, -- fin chrg debit memo or invoice_id customer_trx_id
557 v_batch_id,
558 v_ussgl_tran_code);
559
560
561 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
562 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'INSERTED NEW CASH RECEIPT FOR CUSTOMER_TRX_ID '||
563 to_char(v_cust_trx_id));
564 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FOR THE AMOUNT OF '||TO_CHAR(V_AMOUNT));
565 END IF;
566
567 EXCEPTION
568 WHEN others THEN
569 v_retcode := '2';
570 v_errbuf := 'insert_cash_receipt '||sqlerrm;
571 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN INSERT_CASH_RECEIPT - '||SQLERRM);
572 ROLLBACK;
573 RAISE;
574
575 END insert_cash_receipt;
576
577 -- ------------------------------------------------------------------------
578 -- PROCEDURE updated_lockbox_temp
579 -- ------------------------------------------------------------------------
580 -- This process updates fv_lockbox_ipa_temp. It decreases the total amount
581 -- available for application on a debit memo. This is done so that the process
582 -- knows the current amount available for application in a receipt.
583 -- ------------------------------------------------------------------------
584
585 PROCEDURE update_lockbox_temp(v_decrease_dm_amount IN NUMBER) IS
586 l_module_name VARCHAR2(200) := g_module_name || 'update_lockbox_temp';
587
588 BEGIN
589
590 update fv_lockbox_ipa_temp
591 set amount = nvl(amount,0) - nvl(v_decrease_dm_amount,0)
592 where debit_memo_id = v_appl_dm_id -- current debit memo being processed
593 and invoice_id = v_appl_inv_id -- current invoice being processed
594 and batch_id = v_batch_id; -- curent batch being processed
595
596 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
597 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE TEMP TABLE DECREASING BY '
598 ||to_char(v_decrease_dm_amount));
599 END IF;
600 EXCEPTION
601 WHEN others THEN
602 v_retcode := '2';
603 v_errbuf := 'update_lockbox_temp '||sqlerrm;
604 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN UPDATE_LOCKBOX_TEMP - '||SQLERRM);
605 ROLLBACK;
606 RAISE;
607
608 END update_lockbox_temp;
609
610 -- ------------------------------------------------------------------------
611 -- PROCEDURE update_interim_table
612 -- ------------------------------------------------------------------------
613 -- Since all the debit memos have been applied against and monies
614 -- remain and the amount remaining is not equal to the original application
615 -- amount (meaning we have paid off some portion of debit memo(s), then
616 -- the orginal application receipt record must be updated with the amount
617 -- remaining on the receipt and the debit memo being paid off.
618 -- V_table will contain a 'R' if the receipt
619 -- record is in ar_interim_cash_receipts or a 'L' if the receipt record
620 -- is in ar_interim_cash_rcpt_lines_all.
621 -- ------------------------------------------------------------------------
622
623 PROCEDURE update_interim_table(v_table IN VARCHAR2,
624 v_decrease_appl_amt IN NUMBER,
625 v_upd_customer_trx_id IN NUMBER,
626 v_upd_pay_sch_id IN NUMBER) IS
627 l_module_name VARCHAR2(200) := g_module_name || 'update_interim_table';
628
629 BEGIN
630
631
632 update ar_interim_cash_rcpt_lines_all
633 set payment_amount = nvl(v_decrease_appl_amt,0),
634 customer_trx_id = v_upd_customer_trx_id,
635 payment_schedule_id = v_upd_pay_sch_id,
636 last_updated_by = v_last_updated_by,
637 last_update_date = v_last_update_date
638 where cash_receipt_id = v_cash_receipt_id
639 and cash_receipt_line_id = v_cash_receipt_line_id;
640
641 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
642 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'UPDATE INTERIM LINES TABLE SETTING AMOUNT = '
643 ||to_char(v_decrease_appl_amt)||' on cash_receipt_id '
644 ||to_char(v_cash_receipt_id));
645 END IF;
646
647
648 EXCEPTION
649 WHEN others THEN
650 v_retcode := '2';
654 RAISE;
651 v_errbuf := 'update_interim_table '||sqlerrm;
652 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,'ERROR OCCURRED IN UPDATE_INTERIM_TABLE - '||SQLERRM);
653 ROLLBACK;
655
656 END update_interim_table;
657
658
659 END FV_LOCKBOX_FC_PKG;
[Home] [Help]