[Home] [Help]
PACKAGE BODY: APPS.AR_IREC_APPLY_CREDITS
Source
1 PACKAGE BODY AR_IREC_APPLY_CREDITS AS
2 /* $Header: ARIAPCRB.pls 120.27 2007/11/28 14:22:51 rsinthre ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_IREC_APPLY_CREDITS';
8 PG_DEBUG VARCHAR2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
9 PG_DIAGNOSTICS VARCHAR2(1) := NVL(FND_PROFILE.value('FND_DIAGNOSTICS'), 'N');
10
11 /*========================================================================
12 | Prototype Declarations - Private Procedures
13 *=======================================================================*/
14 PROCEDURE select_credit_to_apply( p_currency_code IN VARCHAR2,
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_credit_ps_id OUT NOCOPY NUMBER,
17 x_debit_ps_id OUT NOCOPY NUMBER);
18
19 PROCEDURE apply_credits_on_payment(p_currency_code IN VARCHAR2,
20 x_open_invoices_status OUT NOCOPY VARCHAR2,
21 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
22 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
23 x_cash_receipt_id OUT NOCOPY NUMBER,
24 x_msg_count OUT NOCOPY NUMBER,
25 x_msg_data OUT NOCOPY VARCHAR2,
26 x_return_status OUT NOCOPY VARCHAR2
27 );
28
29 PROCEDURE apply_credits_on_credit_memo(p_currency_code IN VARCHAR2,
30 x_open_invoices_status OUT NOCOPY VARCHAR2,
31 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
32 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2,
35 x_return_status OUT NOCOPY VARCHAR2
36 );
37
38 /*============================================================
39 | PUBLIC procedure copy_transaction_list_records
40 |
41 | DESCRIPTION
42 | Copy the transactions for the active customer, site and currency from the
43 | Transaction List GT to the Apply Credits GT
44 |
45 | PSEUDO CODE/LOGIC
46 |
47 | PARAMETERS
48 | p_customer_id IN NUMBER
49 | p_customer_site_use_id IN NUMBER DEFAULT NULL
50 | p_currency_code IN VARCHAR2
51 |
52 | KNOWN ISSUES
53 |
54 |
55 |
56 | NOTES
57 |
58 |
59 |
60 | MODIFICATION HISTORY
61 | Date Author Description of Changes
62 | 12-OCT-2004 vnb Created
63 | 26-MAY-2005 rsinthre Bug 4392371 - OIR needs to support cross customer payment
64 +============================================================*/
65
66 PROCEDURE copy_transaction_list_records(p_customer_id IN NUMBER,
67 p_customer_site_use_id IN NUMBER DEFAULT NULL,
68 p_currency_code IN VARCHAR2
69 ) IS
70
71 CURSOR transaction_list (p_customer_id NUMBER,
72 p_customer_site_use_id NUMBER,
73 p_currency_code VARCHAR2) IS
74
75 SELECT
76 CUSTOMER_ID,
77 CUSTOMER_SITE_USE_ID,
78 ACCOUNT_NUMBER,
79 CUSTOMER_TRX_ID,
80 TRX_NUMBER,TRX_DATE,
81 TRX_CLASS,
82 DUE_DATE,
83 PAYMENT_SCHEDULE_ID,
84 STATUS,
85 PAYMENT_TERMS,
86 NUMBER_OF_INSTALLMENTS,
87 TERMS_SEQUENCE_NUMBER,
88 LINE_AMOUNT,
89 TAX_AMOUNT ,
90 FREIGHT_AMOUNT,
91 FINANCE_CHARGES,
92 CURRENCY_CODE ,
93 AMOUNT_DUE_ORIGINAL,
94 AMOUNT_DUE_REMAINING,
95 PAYMENT_AMT ,
96 SERVICE_CHARGE,
97 DISCOUNT_AMOUNT,
98 RECEIPT_DATE,
99 RECEIPT_NUMBER,
100 PO_NUMBER,
101 SO_NUMBER,
102 PRINTING_OPTION ,
103 ATTRIBUTE_CATEGORY ,
104 ATTRIBUTE1,
105 ATTRIBUTE2,
106 ATTRIBUTE3,
107 ATTRIBUTE4,
108 ATTRIBUTE5,
109 ATTRIBUTE6,
110 ATTRIBUTE7,
111 ATTRIBUTE8,
112 ATTRIBUTE9,
113 ATTRIBUTE10,
114 ATTRIBUTE11,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
118 ATTRIBUTE15,
119 INTERFACE_HEADER_CONTEXT,
120 INTERFACE_HEADER_ATTRIBUTE1,
121 INTERFACE_HEADER_ATTRIBUTE2,
122 INTERFACE_HEADER_ATTRIBUTE3,
123 INTERFACE_HEADER_ATTRIBUTE4,
124 INTERFACE_HEADER_ATTRIBUTE5,
125 INTERFACE_HEADER_ATTRIBUTE6,
126 INTERFACE_HEADER_ATTRIBUTE7,
127 INTERFACE_HEADER_ATTRIBUTE8,
128 INTERFACE_HEADER_ATTRIBUTE9,
129 INTERFACE_HEADER_ATTRIBUTE10,
130 INTERFACE_HEADER_ATTRIBUTE11,
131 INTERFACE_HEADER_ATTRIBUTE12,
132 INTERFACE_HEADER_ATTRIBUTE13,
133 INTERFACE_HEADER_ATTRIBUTE14,
134 INTERFACE_HEADER_ATTRIBUTE15,
135 LAST_UPDATE_DATE,
136 LAST_UPDATED_BY,
137 CREATION_DATE,
138 CREATED_BY,
139 LAST_UPDATE_LOGIN,
140 CASH_RECEIPT_ID,
141 PAY_FOR_CUSTOMER_ID,
142 PAY_FOR_CUSTOMER_SITE_ID
143 FROM ar_irec_payment_list_gt
144 WHERE CUSTOMER_ID = p_customer_id
145 AND CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID)
146 AND CURRENCY_CODE = p_currency_code;
147
148 l_debit_transactions_flag BOOLEAN;
149 l_credit_transactions_flag BOOLEAN;
150
151 l_procedure_name VARCHAR2(50);
152 l_debug_info VARCHAR2(200);
153
154 BEGIN
155 l_debit_transactions_flag := false;
156 l_credit_transactions_flag := false;
157
158 l_procedure_name := '.copy_transaction_list_records';
159
160
161 ----------------------------------------------------------------------------------------
162 l_debug_info := 'Fetch all transactions from Transaction List GT into Apply Credits GT';
163 -----------------------------------------------------------------------------------------
164 IF (PG_DEBUG = 'Y') THEN
165 arp_standard.debug(l_debug_info);
166 END IF;
167
168 FOR trx IN transaction_list(p_customer_id,
169 p_customer_site_use_id,
170 p_currency_code )
171 LOOP
172 INSERT INTO ar_irec_apply_credit_gt
173 (
174 CUSTOMER_ID,
175 CUSTOMER_SITE_USE_ID,
176 ACCOUNT_NUMBER,
177 CUSTOMER_TRX_ID,
178 TRX_NUMBER,TRX_DATE,
179 TRX_CLASS,
180 DUE_DATE,
181 PAYMENT_SCHEDULE_ID,
182 STATUS,
183 PAYMENT_TERMS,
184 NUMBER_OF_INSTALLMENTS,
185 TERMS_SEQUENCE_NUMBER,
186 LINE_AMOUNT,
187 TAX_AMOUNT ,
188 FREIGHT_AMOUNT,
189 FINANCE_CHARGES,
190 CURRENCY_CODE ,
191 AMOUNT_DUE_ORIGINAL,
192 AMOUNT_DUE_REMAINING,
193 PAYMENT_AMT ,
194 SERVICE_CHARGE,
195 DISCOUNT_AMOUNT,
196 RECEIPT_DATE,
197 RECEIPT_NUMBER,
198 PO_NUMBER,
199 SO_NUMBER,
200 PRINTING_OPTION ,
201 ATTRIBUTE_CATEGORY ,
202 ATTRIBUTE1,
203 ATTRIBUTE2,
204 ATTRIBUTE3,
205 ATTRIBUTE4,
206 ATTRIBUTE5,
207 ATTRIBUTE6,
208 ATTRIBUTE7,
209 ATTRIBUTE8,
210 ATTRIBUTE9,
211 ATTRIBUTE10,
212 ATTRIBUTE11,
213 ATTRIBUTE12,
214 ATTRIBUTE13,
215 ATTRIBUTE14,
216 ATTRIBUTE15,
217 INTERFACE_HEADER_CONTEXT,
218 INTERFACE_HEADER_ATTRIBUTE1,
219 INTERFACE_HEADER_ATTRIBUTE2,
220 INTERFACE_HEADER_ATTRIBUTE3,
221 INTERFACE_HEADER_ATTRIBUTE4,
222 INTERFACE_HEADER_ATTRIBUTE5,
223 INTERFACE_HEADER_ATTRIBUTE6,
224 INTERFACE_HEADER_ATTRIBUTE7,
225 INTERFACE_HEADER_ATTRIBUTE8,
226 INTERFACE_HEADER_ATTRIBUTE9,
227 INTERFACE_HEADER_ATTRIBUTE10,
228 INTERFACE_HEADER_ATTRIBUTE11,
229 INTERFACE_HEADER_ATTRIBUTE12,
230 INTERFACE_HEADER_ATTRIBUTE13,
231 INTERFACE_HEADER_ATTRIBUTE14,
232 INTERFACE_HEADER_ATTRIBUTE15,
233 LAST_UPDATE_DATE,
234 LAST_UPDATED_BY,
235 CREATION_DATE,
236 CREATED_BY,
237 LAST_UPDATE_LOGIN,
238 APPLICATION_AMOUNT,
239 CASH_RECEIPT_ID,
240 PAY_FOR_CUSTOMER_ID,
241 PAY_FOR_CUSTOMER_SITE_ID )
242 VALUES
243 (
244 -- In create transaction list record of arirpmtb, the actual customer id of the transaction is stored in pay_for_customer_id
245 -- and the customer id is the login customer id.
246 trx.PAY_FOR_CUSTOMER_ID,
247 trx.PAY_FOR_CUSTOMER_SITE_ID,
248 trx.ACCOUNT_NUMBER,
249 trx.CUSTOMER_TRX_ID,
250 trx.TRX_NUMBER,
251 trx.TRX_DATE,
252 trx.TRX_CLASS,
253 trx.DUE_DATE,
254 trx.PAYMENT_SCHEDULE_ID,
255 trx.STATUS,
256 trx.PAYMENT_TERMS,
257 trx.NUMBER_OF_INSTALLMENTS,
258 trx.TERMS_SEQUENCE_NUMBER,
259 trx.LINE_AMOUNT,
260 trx.TAX_AMOUNT ,
261 trx.FREIGHT_AMOUNT,
262 trx.FINANCE_CHARGES,
263 trx.CURRENCY_CODE ,
264 trx.AMOUNT_DUE_ORIGINAL,
265 trx.AMOUNT_DUE_REMAINING,
266 trx.PAYMENT_AMT ,
267 trx.SERVICE_CHARGE,
268 trx.DISCOUNT_AMOUNT,
269 trx.RECEIPT_DATE,
270 trx.RECEIPT_NUMBER,
271 trx.PO_NUMBER,
272 trx.SO_NUMBER,
273 trx.PRINTING_OPTION ,
274 trx.ATTRIBUTE_CATEGORY ,
275 trx.ATTRIBUTE1,
276 trx.ATTRIBUTE2,
277 trx.ATTRIBUTE3,
278 trx.ATTRIBUTE4,
279 trx.ATTRIBUTE5,
280 trx.ATTRIBUTE6,
281 trx.ATTRIBUTE7,
282 trx.ATTRIBUTE8,
283 trx.ATTRIBUTE9,
284 trx.ATTRIBUTE10,
285 trx.ATTRIBUTE11,
286 trx.ATTRIBUTE12,
287 trx.ATTRIBUTE13,
288 trx.ATTRIBUTE14,
289 trx.ATTRIBUTE15,
290 trx.INTERFACE_HEADER_CONTEXT,
291 trx.INTERFACE_HEADER_ATTRIBUTE1,
292 trx.INTERFACE_HEADER_ATTRIBUTE2,
293 trx.INTERFACE_HEADER_ATTRIBUTE3,
294 trx.INTERFACE_HEADER_ATTRIBUTE4,
295 trx.INTERFACE_HEADER_ATTRIBUTE5,
296 trx.INTERFACE_HEADER_ATTRIBUTE6,
297 trx.INTERFACE_HEADER_ATTRIBUTE7,
298 trx.INTERFACE_HEADER_ATTRIBUTE8,
299 trx.INTERFACE_HEADER_ATTRIBUTE9,
300 trx.INTERFACE_HEADER_ATTRIBUTE10,
301 trx.INTERFACE_HEADER_ATTRIBUTE11,
302 trx.INTERFACE_HEADER_ATTRIBUTE12,
303 trx.INTERFACE_HEADER_ATTRIBUTE13,
304 trx.INTERFACE_HEADER_ATTRIBUTE14,
305 trx.INTERFACE_HEADER_ATTRIBUTE15,
306 trx.LAST_UPDATE_DATE,
307 trx.LAST_UPDATED_BY,
308 trx.CREATION_DATE,
309 trx.CREATED_BY,
310 trx.LAST_UPDATE_LOGIN,
311 trx.PAYMENT_AMT,
312 trx.CASH_RECEIPT_ID,
313 trx.PAY_FOR_CUSTOMER_ID,
314 trx.PAY_FOR_CUSTOMER_SITE_ID
315 );
316
317 END LOOP;
318
319 COMMIT;
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 IF (PG_DEBUG = 'Y') THEN
324 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
325 arp_standard.debug('- Customer Id: '||p_customer_id);
326 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
327 arp_standard.debug('- Currency Code: '||p_currency_code);
328 arp_standard.debug('ERROR =>'|| SQLERRM);
329 END IF;
330
331 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
332 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
333 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
334 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
335 FND_MSG_PUB.ADD;
336
337 END copy_transaction_list_records;
338
339
340 /*============================================================
341 | PUBLIC procedure copy_open_debits
342 |
343 | DESCRIPTION
344 | Copy all open debit transactions for the active customer, site and currency from the
345 | AR_PAYMENT_SCHEDULES to the Apply Credits GT
346 |
347 | PSEUDO CODE/LOGIC
348 |
349 | PARAMETERS
350 | p_customer_id IN NUMBER
351 | p_customer_site_use_id IN NUMBER DEFAULT NULL
352 | p_currency_code IN VARCHAR2
353 |
354 | KNOWN ISSUES
355 |
356 |
357 |
358 | NOTES
359 |
360 |
361 |
362 | MODIFICATION HISTORY
363 | Date Author Description of Changes
364 | 12-OCT-2004 vnb Created
365 | 26-MAY-2005 rsinthre Bug 4392371 - OIR needs to support cross customer payment
366 +============================================================*/
367
368 PROCEDURE copy_open_debits(p_customer_id IN NUMBER,
369 p_customer_site_use_id IN NUMBER DEFAULT NULL,
370 p_currency_code IN VARCHAR2
371 ) IS
372 BEGIN
373 --This procedure is no longer used, so removed the code from this procedure.
374 NULL;
375 END copy_open_debits;
376
377 /*============================================================
378 | PUBLIC procedure copy_open_credits
379 |
380 | DESCRIPTION
381 | Copy all open credit transactions for the active customer, site and currency from the
382 | AR_PAYMENT_SCHEDULES to the Apply Credits GT
383 |
384 | PSEUDO CODE/LOGIC
385 |
386 | PARAMETERS
387 | p_customer_id IN NUMBER
388 | p_customer_site_use_id IN NUMBER DEFAULT NULL
389 | p_currency_code IN VARCHAR2
390 |
391 | KNOWN ISSUES
392 |
393 |
394 |
395 | NOTES
396 |
397 |
398 |
399 | MODIFICATION HISTORY
400 | Date Author Description of Changes
401 | 12-OCT-2004 vnb Created
402 | 26-MAY-2005 rsinthre Bug 4392371 - OIR needs to support cross customer payment
403 +============================================================*/
404
405 PROCEDURE copy_open_credits(p_customer_id IN NUMBER,
406 p_customer_site_use_id IN NUMBER DEFAULT NULL,
407 p_currency_code IN VARCHAR2
408 ) IS
409 BEGIN
410 --This procedure is no longer used, so removed the code from this procedure.
411 NULL;
412 END copy_open_credits;
413
414 /*============================================================
415 | PUBLIC procedure create_apply_credits_record
416 |
420 |
417 | DESCRIPTION
418 | Copy the transactions for the active customer, site and currency from the
419 | Transaction List GT to the Apply Credits GT
421 | PSEUDO CODE/LOGIC
422 |
423 | PARAMETERS
424 | p_payment_schedule_id IN NUMBER
425 | p_customer_id IN NUMBER
426 | p_customer_site_id IN NUMBER
427 |
428 | KNOWN ISSUES
429 |
430 |
431 |
432 | NOTES
433 |
434 |
435 |
436 | MODIFICATION HISTORY
437 | Date Author Description of Changes
438 | 12-OCT-2004 vnb Created
439 | 26-MAY-2005 rsinthre Bug 4392371 - OIR needs to support cross customer payment
440 +============================================================*/
441 PROCEDURE create_apply_credits_record( p_payment_schedule_id IN NUMBER,
442 p_customer_id IN NUMBER,
443 p_customer_site_id IN NUMBER
444 ) IS
445
446 l_trx_class VARCHAR2(20);
447 l_amount_due_remaining NUMBER;
448 l_customer_id NUMBER;
449 l_customer_site_use_id NUMBER;
450 l_currency_code VARCHAR2(15);
451 l_cash_receipt_id NUMBER;
452
453 l_discount_amount NUMBER;
454 l_rem_amt_rcpt NUMBER;
455 l_rem_amt_inv NUMBER;
456 l_grace_days_flag VARCHAR2(2);
457
458 l_procedure_name VARCHAR2(50);
459 l_debug_info VARCHAR2(200);
460 l_pay_for_cust_id NUMBER(15);
461 l_pay_for_cust_site_id NUMBER(15);
462
463 BEGIN
464
465 l_procedure_name := '.create_apply_credits_record';
466 l_discount_amount := 0;
467 l_rem_amt_rcpt := 0;
468 l_rem_amt_inv := 0;
469
470
471 select class, amount_due_remaining, cash_receipt_id, ct.PAYING_CUSTOMER_ID, ct.PAYING_SITE_USE_ID, ps.CUSTOMER_ID, ps.CUSTOMER_SITE_USE_ID
472 into l_trx_class, l_amount_due_remaining, l_cash_receipt_id, l_pay_for_cust_id, l_pay_for_cust_site_id, l_customer_id, l_customer_site_use_id
473 from ar_payment_schedules ps, ra_customer_trx ct
474 where ps.CUSTOMER_TRX_ID = ct.CUSTOMER_TRX_ID(+)
475 and ps.payment_schedule_id = p_payment_schedule_id;
476
477 --Bug 4000279 - Modified to check for 'UNAPP' status only
478 IF (l_trx_class = 'PMT') THEN
479 select -sum(app.amount_applied)
480 into l_amount_due_remaining
481 from ar_receivable_applications app
482 where nvl( app.confirmed_flag, 'Y' ) = 'Y'
483 AND app.status = 'UNAPP'
484 AND app.cash_receipt_id = l_cash_receipt_id;
485
486 ELSIF (l_trx_class = 'INV') THEN
487
488 l_grace_days_flag := AR_IREC_PAYMENTS.is_grace_days_enabled_wrapper();
489
490 arp_discounts_api.get_discount(p_ps_id => p_payment_schedule_id,
491 p_apply_date => sysdate,
492 p_in_applied_amount => l_amount_due_remaining,
493 p_grace_days_flag => l_grace_days_flag,
494 p_out_discount => l_discount_amount,
495 p_out_rem_amt_rcpt => l_rem_amt_rcpt,
496 p_out_rem_amt_inv => l_rem_amt_inv);
497 END IF;
498
499 ----------------------------------------------------------------------------------------
500 l_debug_info := 'Populate the Apply Credits GT with the transaction';
501 -----------------------------------------------------------------------------------------
502 IF (PG_DEBUG = 'Y') THEN
503 arp_standard.debug(l_debug_info);
504 END IF;
505
506 if(l_pay_for_cust_id is null) then
507 l_pay_for_cust_id := l_customer_id;
508 l_pay_for_cust_site_id := l_customer_site_use_id;
509 end if;
510
511 INSERT INTO ar_irec_apply_credit_gt
512 (
513 CUSTOMER_ID,
514 CUSTOMER_SITE_USE_ID,
515 ACCOUNT_NUMBER,
516 CUSTOMER_TRX_ID,
517 TRX_NUMBER,TRX_DATE,
518 TRX_CLASS,
519 DUE_DATE,
520 PAYMENT_SCHEDULE_ID,
521 STATUS,
522 PAYMENT_TERMS,
523 NUMBER_OF_INSTALLMENTS,
524 TERMS_SEQUENCE_NUMBER,
525 LINE_AMOUNT,
526 TAX_AMOUNT ,
527 FREIGHT_AMOUNT,
528 FINANCE_CHARGES,
529 CURRENCY_CODE ,
530 AMOUNT_DUE_ORIGINAL,
531 AMOUNT_DUE_REMAINING,
532 PAYMENT_AMT ,
533 SERVICE_CHARGE,
534 DISCOUNT_AMOUNT,
535 RECEIPT_DATE,
536 PO_NUMBER,
537 SO_NUMBER,
538 PRINTING_OPTION ,
539 ATTRIBUTE_CATEGORY ,
540 ATTRIBUTE1,
541 ATTRIBUTE2,
542 ATTRIBUTE3,
543 ATTRIBUTE4,
544 ATTRIBUTE5,
545 ATTRIBUTE6,
546 ATTRIBUTE7,
547 ATTRIBUTE8,
548 ATTRIBUTE9,
549 ATTRIBUTE10,
550 ATTRIBUTE11,
551 ATTRIBUTE12,
552 ATTRIBUTE13,
553 ATTRIBUTE14,
554 ATTRIBUTE15,
555 INTERFACE_HEADER_CONTEXT,
559 INTERFACE_HEADER_ATTRIBUTE4,
556 INTERFACE_HEADER_ATTRIBUTE1,
557 INTERFACE_HEADER_ATTRIBUTE2,
558 INTERFACE_HEADER_ATTRIBUTE3,
560 INTERFACE_HEADER_ATTRIBUTE5,
561 INTERFACE_HEADER_ATTRIBUTE6,
562 INTERFACE_HEADER_ATTRIBUTE7,
563 INTERFACE_HEADER_ATTRIBUTE8,
564 INTERFACE_HEADER_ATTRIBUTE9,
565 INTERFACE_HEADER_ATTRIBUTE10,
566 INTERFACE_HEADER_ATTRIBUTE11,
567 INTERFACE_HEADER_ATTRIBUTE12,
568 INTERFACE_HEADER_ATTRIBUTE13,
569 INTERFACE_HEADER_ATTRIBUTE14,
570 INTERFACE_HEADER_ATTRIBUTE15,
571 APPLICATION_AMOUNT,
572 CASH_RECEIPT_ID,
573 PAY_FOR_CUSTOMER_ID,
574 PAY_FOR_CUSTOMER_SITE_ID)
575 SELECT l_customer_id,
576 DECODE(l_customer_site_use_id,to_number(''),-1,l_customer_site_use_id),
577 hca.account_number,
578 ps.customer_trx_id,
579 ps.trx_number,
580 ps.trx_date,
581 ps.class,
582 ps.due_date,
583 ps.payment_schedule_id,
584 ps.status,
585 rt.name,
586 ARPT_SQL_FUNC_UTIL.Get_Number_Of_Due_Dates(ps.term_id) number_of_installments,
587 ps.terms_sequence_number,
588 ps.amount_line_items_original line_amount,
589 ps.tax_original tax_amount,
590 ps.freight_original freight_amount,
591 ps.receivables_charges_charged finance_charge,
592 ps.INVOICE_CURRENCY_CODE,
593 ps.AMOUNT_DUE_ORIGINAL,
594 l_amount_due_remaining,
595 NULL,
596 0,
597 l_discount_amount,
598 sysdate,
599 ct.PURCHASE_ORDER,
600 NULL,
601 ct.printing_option,
602 ps.ATTRIBUTE_CATEGORY ,
603 ps.ATTRIBUTE1,
604 ps.ATTRIBUTE2,
605 ps.ATTRIBUTE3,
606 ps.ATTRIBUTE4,
607 ps.ATTRIBUTE5,
608 ps.ATTRIBUTE6,
609 ps.ATTRIBUTE7,
610 ps.ATTRIBUTE8,
611 ps.ATTRIBUTE9,
612 ps.ATTRIBUTE10,
613 ps.ATTRIBUTE11,
614 ps.ATTRIBUTE12,
615 ps.ATTRIBUTE13,
616 ps.ATTRIBUTE14,
617 ps.ATTRIBUTE15,
618 ct.INTERFACE_HEADER_CONTEXT,
619 ct.INTERFACE_HEADER_ATTRIBUTE1,
620 ct.INTERFACE_HEADER_ATTRIBUTE2,
621 ct.INTERFACE_HEADER_ATTRIBUTE3,
622 ct.INTERFACE_HEADER_ATTRIBUTE4,
623 ct.INTERFACE_HEADER_ATTRIBUTE5,
624 ct.INTERFACE_HEADER_ATTRIBUTE6,
625 ct.INTERFACE_HEADER_ATTRIBUTE7,
626 ct.INTERFACE_HEADER_ATTRIBUTE8,
627 ct.INTERFACE_HEADER_ATTRIBUTE9,
628 ct.INTERFACE_HEADER_ATTRIBUTE10,
629 ct.INTERFACE_HEADER_ATTRIBUTE11,
630 ct.INTERFACE_HEADER_ATTRIBUTE12,
631 ct.INTERFACE_HEADER_ATTRIBUTE13,
632 ct.INTERFACE_HEADER_ATTRIBUTE14,
633 ct.INTERFACE_HEADER_ATTRIBUTE15,
634 ARI_UTILITIES.curr_round_amt(l_amount_due_remaining - l_discount_amount,ps.INVOICE_CURRENCY_CODE),
635 l_cash_receipt_id,
636 l_pay_for_cust_id,
637 --Bug 4062938 - Handling of transactions with no site id
638 decode(l_pay_for_cust_site_id, null, -1,l_pay_for_cust_site_id) as customer_site_use_id
639 FROM ar_payment_schedules ps, hz_cust_accounts hca, ra_terms rt, ra_customer_trx ct
640 WHERE ps.payment_schedule_id = p_payment_schedule_id
641 AND ps.customer_id = hca.cust_account_id
642 AND ps.term_id = rt.term_id(+)
643 AND ps.customer_trx_id = ct.customer_trx_id(+);
644
645 COMMIT;
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 IF (PG_DEBUG = 'Y') THEN
650 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
651 arp_standard.debug('- Payment Schedule Id: '||p_payment_schedule_id);
652 arp_standard.debug('ERROR =>'|| SQLERRM);
653 END IF;
654
655 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
656 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
657 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
658 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
659 FND_MSG_PUB.ADD;
660
661 END create_apply_credits_record;
662
663 /*============================================================
664 | PUBLIC procedure delete_all_debits
665 |
666 | DESCRIPTION
667 | Deletes all credit transactions for the active customer, site and currency from the
668 | Apply Credits GT
669 |
670 | PSEUDO CODE/LOGIC
671 |
672 | PARAMETERS
673 | p_customer_id IN NUMBER
674 | p_customer_site_use_id IN NUMBER DEFAULT NULL
675 | p_currency_code IN VARCHAR2
676 |
677 | KNOWN ISSUES
678 |
679 |
680 |
681 | NOTES
682 |
683 |
684 |
685 | MODIFICATION HISTORY
686 | Date Author Description of Changes
687 | 13-OCT-2004 vnb Created
688 +============================================================*/
689
690 PROCEDURE delete_all_debits(p_customer_id IN NUMBER,
691 p_customer_site_use_id IN NUMBER DEFAULT NULL,
692 p_currency_code IN VARCHAR2
693 ) IS
694
695 l_procedure_name VARCHAR2(50);
696 l_debug_info VARCHAR2(200);
697
698 BEGIN
699 l_procedure_name := '.delete_all_debits';
700
701
702 ---------------------------------------------------------------------------
703 l_debug_info := 'Delete all debit transactions from Apply Credits GT';
704 ---------------------------------------------------------------------------
705 IF (PG_DEBUG = 'Y') THEN
706 arp_standard.debug(l_debug_info);
707 END IF;
708
709 DELETE FROM ar_irec_apply_credit_gt
710 WHERE customer_id = p_customer_id
711 AND customer_site_use_id = nvl(p_customer_site_use_id, customer_site_use_id)
712 AND currency_code = p_currency_code
713 AND ( trx_class = 'INV' OR
714 trx_class = 'DM' OR
715 trx_class = 'CB' OR
716 trx_class = 'DEP'
717 );
718
719 COMMIT;
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 IF (PG_DEBUG = 'Y') THEN
724 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
725 arp_standard.debug('- Customer Id: '||p_customer_id);
726 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
727 arp_standard.debug('- Currency Code: '||p_currency_code);
728 arp_standard.debug('ERROR =>'|| SQLERRM);
729 END IF;
730
731 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
732 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
733 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
734 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
735 FND_MSG_PUB.ADD;
736
737 END delete_all_debits;
738
739 /*============================================================
740 | PUBLIC procedure delete_all_credits
741 |
742 | DESCRIPTION
743 | Deletes all credit transactions for the active customer, site and currency from the
744 | Apply Credits GT
745 |
746 | PSEUDO CODE/LOGIC
747 |
748 | PARAMETERS
749 | p_customer_id IN NUMBER
750 | p_customer_site_use_id IN NUMBER DEFAULT NULL
751 | p_currency_code IN VARCHAR2
752 |
753 | KNOWN ISSUES
754 |
755 |
756 |
757 | NOTES
758 |
759 |
760 |
761 | MODIFICATION HISTORY
762 | Date Author Description of Changes
763 | 13-OCT-2004 vnb Created
764 +============================================================*/
765
766 PROCEDURE delete_all_credits(p_customer_id IN NUMBER,
767 p_customer_site_use_id IN NUMBER DEFAULT NULL,
768 p_currency_code IN VARCHAR2
769 ) IS
770 l_procedure_name VARCHAR2(50);
771 l_debug_info VARCHAR2(200);
772
773 BEGIN
774
775 l_procedure_name := '.delete_all_credits';
776
777
778 ---------------------------------------------------------------------------
779 l_debug_info := 'Delete all credit transactions from Apply Credits GT';
780 ---------------------------------------------------------------------------
781 IF (PG_DEBUG = 'Y') THEN
782 arp_standard.debug(l_debug_info);
783 END IF;
784
785 DELETE FROM ar_irec_apply_credit_gt
786 WHERE customer_id = p_customer_id
787 AND customer_site_use_id = nvl(p_customer_site_use_id, customer_site_use_id)
788 AND currency_code = p_currency_code
789 AND ( trx_class = 'CM' OR
790 trx_class = 'PMT'
791 );
792
793 COMMIT;
794
795 EXCEPTION
796 WHEN OTHERS THEN
797 IF (PG_DEBUG = 'Y') THEN
798 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
799 arp_standard.debug('- Customer Id: '||p_customer_id);
800 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
801 arp_standard.debug('- Currency Code: '||p_currency_code);
802 arp_standard.debug('ERROR =>'|| SQLERRM);
803 END IF;
804
805 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
806 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
807 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
808 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
809 FND_MSG_PUB.ADD;
810
811 END delete_all_credits;
812
813 /*============================================================
814 | PUBLIC procedure delete_apply_credits_record
815 |
816 | DESCRIPTION
817 | Deletes a transaction, specified by a Payment Schedule Id, from the Apply Credits GT
818 |
819 | PSEUDO CODE/LOGIC
820 |
821 | PARAMETERS
822 | p_payment_schedule_id IN NUMBER
823 |
824 | KNOWN ISSUES
825 |
826 |
827 |
828 | NOTES
829 |
830 |
831 |
832 | MODIFICATION HISTORY
833 | Date Author Description of Changes
834 | 13-OCT-2004 vnb Created
835 +============================================================*/
836
837 PROCEDURE delete_apply_credits_record( p_payment_schedule_id IN NUMBER
838 ) IS
839
840 l_procedure_name VARCHAR2(50);
841 l_debug_info VARCHAR2(200);
842
843 BEGIN
844 l_procedure_name := '.delete_apply_credits_record';
845
846
847 ---------------------------------------------------------------------------
848 l_debug_info := 'Delete the transaction from Apply Credits GT';
849 ---------------------------------------------------------------------------
850 IF (PG_DEBUG = 'Y') THEN
851 arp_standard.debug(l_debug_info);
852 END IF;
853
854 DELETE FROM ar_irec_apply_credit_gt
855 WHERE payment_schedule_id = p_payment_schedule_id;
856
857 COMMIT;
858
859 EXCEPTION
860 WHEN OTHERS THEN
861 IF (PG_DEBUG = 'Y') THEN
862 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
863 arp_standard.debug('- Payment Schedule Id: '||p_payment_schedule_id);
864 arp_standard.debug('ERROR =>'|| SQLERRM);
865 END IF;
866
867 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
868 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
869 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
870 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
871 FND_MSG_PUB.ADD;
872
873 END delete_apply_credits_record;
874
875 /*============================================================
876 | PUBLIC procedure delete_all_records
877 |
878 | DESCRIPTION
879 | Deletes all transactions for the active customer, site and currency from the
880 | Apply Credits GT
881 |
882 | PSEUDO CODE/LOGIC
883 |
884 | PARAMETERS
885 | p_customer_id IN NUMBER
886 | p_customer_site_use_id IN NUMBER DEFAULT NULL
887 | p_currency_code IN VARCHAR2
888 |
889 | KNOWN ISSUES
890 |
891 |
892 |
893 | NOTES
894 |
895 |
896 |
897 | MODIFICATION HISTORY
898 | Date Author Description of Changes
899 | 13-OCT-2004 vnb Created
900 +============================================================*/
901
902 PROCEDURE delete_all_records(p_customer_id IN NUMBER,
903 p_customer_site_use_id IN NUMBER DEFAULT NULL,
904 p_currency_code IN VARCHAR2
905 ) IS
906 l_procedure_name VARCHAR2(50);
907 l_debug_info VARCHAR2(200);
908
909 BEGIN
910
911 l_procedure_name := '.delete_all_records';
912
913
914 ---------------------------------------------------------------------------
915 l_debug_info := 'Delete all transactions from Apply Credits GT';
916 ---------------------------------------------------------------------------
917 IF (PG_DEBUG = 'Y') THEN
918 arp_standard.debug(l_debug_info);
919 END IF;
920 -- Bug 5076215 - Apply Credits GT contains data related to the customer and its related customer,
921 -- the related customer transactions are not getting cleared. Since GT contains only session specific data removed the where condition.
922 DELETE FROM ar_irec_apply_credit_gt;
923 COMMIT;
924
925 EXCEPTION
926 WHEN OTHERS THEN
927 IF (PG_DEBUG = 'Y') THEN
928 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
929 arp_standard.debug('- Customer Id: '||p_customer_id);
930 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
931 arp_standard.debug('- Currency Code: '||p_currency_code);
932 arp_standard.debug('ERROR =>'|| SQLERRM);
933 END IF;
934
935 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
936 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
937 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
938 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
939 FND_MSG_PUB.ADD;
940
941 END delete_all_records;
942
943 /*============================================================
944 | PUBLIC procedure apply_credits
945 |
946 | DESCRIPTION
947 | Applies selected credits against selected debits
948 |
949 | PSEUDO CODE/LOGIC
950 |
951 | PARAMETERS
952 | p_customer_id IN NUMBER
953 | p_customer_site_use_id IN NUMBER DEFAULT NULL
954 | p_currency_code IN VARCHAR2
955 | p_credit_memos_only IN VARCHAR2
956 |
957 | KNOWN ISSUES
958 |
959 |
960 |
961 | NOTES
962 |
963 |
964 |
965 | MODIFICATION HISTORY
966 | Date Author Description of Changes
967 | 13-OCT-2004 vnb Created
968 | 06-DEC-2004 vnb Bug 4042557 - Change sign in credit application amount
969 | 10-JAN-2005 vnb Bug 4105891 - Check if invoice will remain open after application
970 | 12-Jan-2005 vnb Bug 4050280 - Added 'ORDER BY' clause in cursors for applying credits
971 | 25-Jan-2005 vnb Bug 4103527 - Display error icons for duplicate application
972 +============================================================*/
973
974 PROCEDURE apply_credits(p_customer_id IN NUMBER,
975 p_customer_site_use_id IN NUMBER DEFAULT NULL,
976 p_driving_customer_id IN NUMBER,
977 p_currency_code IN VARCHAR2,
978 p_credit_memos_only IN VARCHAR2,
979 x_open_invoices_status OUT NOCOPY VARCHAR2,
980 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
981 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
982 x_cash_receipt_id OUT NOCOPY NUMBER,
983 x_msg_count OUT NOCOPY NUMBER,
984 x_msg_data OUT NOCOPY VARCHAR2,
985 x_return_status OUT NOCOPY VARCHAR2
986 ) IS
987
988 l_procedure_name VARCHAR2(50);
989 l_debug_info VARCHAR2(200);
990 l_customer_id NUMBER;
991 l_customer_site_use_id NUMBER;
992
993 BEGIN
994
995 l_procedure_name := '.apply_credits';
996 x_cash_receipt_id := 0;
997
998 DELETE FROM ar_irec_apply_credit_gt WHERE PRINTING_OPTION <> 'Y';
999 COMMIT;
1000
1001 if(p_driving_customer_id is NOT NULL and p_driving_customer_id <> p_customer_id) then
1002 l_customer_id := p_driving_customer_id;
1003 l_customer_site_use_id := NULL;
1004 end if;
1005
1006 IF (p_credit_memos_only = 'Y') THEN
1007 ---------------------------------------------------------------------------
1008 l_debug_info := 'Apply credits on credit memo';
1012 END IF;
1009 ---------------------------------------------------------------------------
1010 IF (PG_DEBUG = 'Y') THEN
1011 arp_standard.debug(l_debug_info);
1013 apply_credits_on_credit_memo(p_currency_code,
1014 x_open_invoices_status,
1015 x_dup_appln_dbt_psid,
1016 x_dup_appln_crdt_psid,
1017 x_msg_count,
1018 x_msg_data,
1019 x_return_status
1020 );
1021 ELSE
1022 ---------------------------------------------------------------------------
1023 l_debug_info := 'Apply credits on payment';
1024 ---------------------------------------------------------------------------
1025 IF (PG_DEBUG = 'Y') THEN
1026 arp_standard.debug(l_debug_info);
1027 END IF;
1028 apply_credits_on_payment( p_currency_code,
1029 x_open_invoices_status,
1030 x_dup_appln_dbt_psid,
1031 x_dup_appln_crdt_psid,
1032 x_cash_receipt_id,
1033 x_msg_count,
1034 x_msg_data,
1035 x_return_status
1036 );
1037 END IF;
1038
1039 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1040 AR_IREC_PAYMENTS.write_error_messages(x_msg_data,x_msg_count);
1041 END IF;
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 IF (PG_DEBUG = 'Y') THEN
1046 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1047 arp_standard.debug('- Customer Id: '||p_customer_id);
1048 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
1049 arp_standard.debug('- Currency Code: '||p_currency_code);
1050 arp_standard.debug('ERROR =>'|| SQLERRM);
1051 END IF;
1052
1053 IF(PG_DIAGNOSTICS = 'Y') THEN
1054 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1055 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1056 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1057 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1058 FND_MSG_PUB.ADD;
1059 ELSE
1060 x_msg_data := x_msg_data||SQLERRM;
1061 END IF;
1062
1063 AR_IREC_PAYMENTS.write_error_messages(x_msg_data,x_msg_count);
1064
1065 END apply_credits;
1066
1067
1068 /*============================================================
1069 | PRIVATE procedure apply_credits_on_payment
1070 |
1071 | DESCRIPTION
1072 | Applies selected credits and selected debits against a selected payment
1073 |
1074 | PSEUDO CODE/LOGIC
1075 |
1076 | PARAMETERS
1077 | p_customer_id IN NUMBER
1078 | p_customer_site_use_id IN NUMBER DEFAULT NULL
1079 | p_currency_code IN VARCHAR2
1080 |
1081 | KNOWN ISSUES
1082 |
1083 |
1084 |
1085 | NOTES
1086 |
1087 |
1088 |
1089 | MODIFICATION HISTORY
1090 | Date Author Description of Changes
1091 | 02-FEB-2005 vnb Created
1092 +============================================================*/
1093
1094 PROCEDURE apply_credits_on_payment(p_currency_code IN VARCHAR2,
1095 x_open_invoices_status OUT NOCOPY VARCHAR2,
1096 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
1097 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
1098 x_cash_receipt_id OUT NOCOPY NUMBER,
1099 x_msg_count OUT NOCOPY NUMBER,
1100 x_msg_data OUT NOCOPY VARCHAR2,
1101 x_return_status OUT NOCOPY VARCHAR2
1102 ) IS
1103
1104 CURSOR debit_transactions(p_currency_code VARCHAR2) IS
1105 SELECT CUSTOMER_ID,
1106 CUSTOMER_SITE_USE_ID,
1107 CUSTOMER_TRX_ID,
1108 TRX_NUMBER,
1109 TRX_DATE,
1110 TRX_CLASS,
1111 DUE_DATE,
1112 PAYMENT_SCHEDULE_ID,
1113 STATUS,
1114 TERMS_SEQUENCE_NUMBER,
1115 LINE_AMOUNT,
1116 TAX_AMOUNT ,
1117 FREIGHT_AMOUNT,
1118 FINANCE_CHARGES,
1122 SERVICE_CHARGE,
1119 CURRENCY_CODE ,
1120 AMOUNT_DUE_ORIGINAL,
1121 AMOUNT_DUE_REMAINING,
1123 DISCOUNT_AMOUNT,
1124 APPLICATION_AMOUNT,
1125 CASH_RECEIPT_ID
1126 FROM ar_irec_apply_credit_gt
1127 WHERE CURRENCY_CODE = p_currency_code
1128 AND ( TRX_CLASS = 'INV' OR
1129 TRX_CLASS = 'DM' OR
1130 TRX_CLASS = 'CB' OR
1131 TRX_CLASS = 'DEP'
1132 )
1133 ORDER BY AMOUNT_DUE_REMAINING ASC;
1134
1135 CURSOR credit_transactions(p_currency_code VARCHAR2) IS
1136 SELECT CUSTOMER_ID,
1137 CUSTOMER_SITE_USE_ID,
1138 CUSTOMER_TRX_ID,
1139 TRX_NUMBER,
1140 TRX_DATE,
1141 TRX_CLASS,
1142 DUE_DATE,
1143 PAYMENT_SCHEDULE_ID,
1144 STATUS,
1145 TERMS_SEQUENCE_NUMBER,
1146 LINE_AMOUNT,
1147 TAX_AMOUNT ,
1148 FREIGHT_AMOUNT,
1149 FINANCE_CHARGES,
1150 CURRENCY_CODE ,
1151 AMOUNT_DUE_ORIGINAL,
1152 AMOUNT_DUE_REMAINING,
1153 SERVICE_CHARGE,
1154 DISCOUNT_AMOUNT,
1155 APPLICATION_AMOUNT,
1156 CASH_RECEIPT_ID
1157 FROM ar_irec_apply_credit_gt
1158 WHERE CURRENCY_CODE = p_currency_code
1159 AND ( TRX_CLASS = 'CM' OR
1160 TRX_CLASS = 'PMT'
1161 )
1162 ORDER BY PAYMENT_SCHEDULE_ID;
1163
1164 CURSOR find_cash_receipt(p_ps_id NUMBER) IS
1165 select cash_receipt_id
1166 from ar_payment_schedules
1167 where payment_schedule_id = p_ps_id;
1168
1169 debit_trx_record debit_transactions%ROWTYPE;
1170 credit_trx_record credit_transactions%ROWTYPE;
1171 find_cash_receipt_record find_cash_receipt%ROWTYPE;
1172
1173 l_return_status VARCHAR2(10);
1174 l_msg_count NUMBER;
1175 l_msg_data VARCHAR2(255);
1176
1177 l_sel_cash_receipt_id NUMBER;
1178
1179 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
1180 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1181 l_applied_rec_app_id ar_receivable_applications.receivable_application_id%TYPE;
1182 l_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
1183 l_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
1184
1185 l_procedure_name VARCHAR2(50);
1186 l_debug_info VARCHAR2(200);
1187
1188 BEGIN
1189 x_msg_count := 0;
1190 x_msg_data := '*';
1191 x_open_invoices_status := 'N';
1192 l_sel_cash_receipt_id := 0;
1193 x_return_status := FND_API.G_RET_STS_ERROR;
1194
1195 l_procedure_name := '.apply_credits_on_payment';
1196
1197 SAVEPOINT ARI_APPLY_CREDITS_PMT;
1198
1199
1200 --------------------------------------------------------------------------------
1201 l_debug_info := 'Get the credit transaction to apply other transactions against';
1202 --------------------------------------------------------------------------------
1203 IF (PG_DEBUG = 'Y') THEN
1204 arp_standard.debug(l_debug_info);
1205 END IF;
1206 select_credit_to_apply( p_currency_code => p_currency_code,
1207 x_return_status => x_return_status,
1208 x_credit_ps_id => x_dup_appln_crdt_psid,
1209 x_debit_ps_id => x_dup_appln_dbt_psid
1210 );
1211
1212 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1213 IF (x_dup_appln_crdt_psid = 0) THEN
1214 ---------------------------------------------------------------------------
1215 l_debug_info := 'An unexpected error has occurred while finding the credit';
1216 ---------------------------------------------------------------------------
1217 IF (PG_DEBUG = 'Y') THEN
1218 arp_standard.debug(l_debug_info);
1219 END IF;
1220 APP_EXCEPTION.raise_exception;
1221 ELSE
1222 ---------------------------------------------------------------------------
1223 l_debug_info := 'Duplicate application error';
1224 ---------------------------------------------------------------------------
1225 IF (PG_DEBUG = 'Y') THEN
1226 arp_standard.debug(l_debug_info);
1227 END IF;
1228
1229 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1230
1231 FND_MESSAGE.SET_NAME('AR', 'AR_RW_PAID_INVOICE_TWICE' );
1232 FND_MSG_PUB.ADD;
1233
1234 x_cash_receipt_id := l_sel_cash_receipt_id;
1235
1236 RETURN;
1237 END IF;
1238 END IF;
1239
1240 --Get the cash_receipt_id if selected credit is a payment
1241 ---------------------------------------------------------------------------
1242 l_debug_info := 'Find cash receipt id';
1243 ---------------------------------------------------------------------------
1244 IF (PG_DEBUG = 'Y') THEN
1245 arp_standard.debug(l_debug_info);
1246 END IF;
1247 OPEN find_cash_receipt(x_dup_appln_crdt_psid);
1248 FETCH find_cash_receipt INTO find_cash_receipt_record;
1252 --Should not come here
1249 IF (find_cash_receipt%FOUND) THEN
1250 l_sel_cash_receipt_id := find_cash_receipt_record.cash_receipt_id;
1251 ELSE
1253 APP_EXCEPTION.raise_exception;
1254 END IF;
1255 CLOSE find_cash_receipt;
1256
1257 x_cash_receipt_id := l_sel_cash_receipt_id;
1258
1259 ----------------------------------------------------------------------------------
1260 l_debug_info := 'Apply credits against the selected payment';
1261 ----------------------------------------------------------------------------------
1262 IF (PG_DEBUG = 'Y') THEN
1263 arp_standard.debug(l_debug_info);
1264 END IF;
1265
1266 FOR credit_trx_record in credit_transactions(p_currency_code)
1267 LOOP
1268 --If this is the credit transaction selected to be applied against, do not do anything.
1269 IF (credit_trx_record.payment_schedule_id <> x_dup_appln_crdt_psid) THEN
1270
1271 IF (PG_DEBUG = 'Y') THEN
1272 arp_standard.debug('-------Credit Transaction Information------');
1273 arp_standard.debug('Customer Id: ' || credit_trx_record.customer_id);
1274 arp_standard.debug('Customer Site Use Id: ' || credit_trx_record.customer_site_use_id);
1275 arp_standard.debug('Customer Trx Id: ' || credit_trx_record.customer_trx_id);
1276 arp_standard.debug('Trx Number: ' || credit_trx_record.trx_number);
1277 arp_standard.debug('Trx Date: ' || credit_trx_record.trx_date);
1278 arp_standard.debug('Trx Class: ' || credit_trx_record.trx_class);
1279 arp_standard.debug('Due Date: ' || credit_trx_record.due_date);
1280 arp_standard.debug('Payment Schedule Id: ' || credit_trx_record.payment_schedule_id);
1281 arp_standard.debug('Status: ' || credit_trx_record.status);
1282 arp_standard.debug('Terms Sequence Number: ' || credit_trx_record.terms_sequence_number);
1283 arp_standard.debug('Line Amount: ' || credit_trx_record.line_amount);
1284 arp_standard.debug('Tax Amount: ' || credit_trx_record.tax_amount);
1285 arp_standard.debug('Freight Amount: ' || credit_trx_record.freight_amount);
1286 arp_standard.debug('Finance Charges: ' || credit_trx_record.trx_class);
1287 arp_standard.debug('Currency Code: ' || credit_trx_record.currency_code);
1288 arp_standard.debug('Amount Due Original: ' || credit_trx_record.amount_due_original);
1289 arp_standard.debug('Amount Due Remaining: ' || credit_trx_record.amount_due_remaining);
1290 arp_standard.debug('Discount Amount: ' || credit_trx_record.discount_amount);
1291 arp_standard.debug('Application Amount: ' || credit_trx_record.application_amount);
1292 arp_standard.debug('Cash Receipt Id: ' || credit_trx_record.cash_receipt_id);
1293 END IF;
1294
1295 IF (credit_trx_record.trx_class = 'CM') THEN
1296 ----------------------------------------------------------------------------------
1297 l_debug_info := 'Apply credit memo against selected payment';
1298 ----------------------------------------------------------------------------------
1299 IF (PG_DEBUG = 'Y') THEN
1300 arp_standard.debug(l_debug_info);
1301 END IF;
1302
1303 AR_RECEIPT_API_PUB.apply(
1304 p_api_version => 1.0,
1305 p_init_msg_list => FND_API.G_TRUE,
1306 p_commit => FND_API.G_FALSE,
1307 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1308 x_return_status => l_return_status,
1309 x_msg_count => l_msg_count,
1310 x_msg_data => l_msg_data,
1311 p_cash_receipt_id => l_sel_cash_receipt_id,
1312 p_customer_trx_id => credit_trx_record.customer_trx_id,
1313 p_installment => credit_trx_record.terms_sequence_number,
1314 p_applied_payment_schedule_id => credit_trx_record.payment_schedule_id,
1315 p_amount_applied => credit_trx_record.application_amount,
1316 p_discount => credit_trx_record.discount_amount,
1317 p_called_from => 'IREC'
1318 );
1319
1320 ELSIF (credit_trx_record.trx_class = 'PMT') THEN
1321 ----------------------------------------------------------------------------------
1322 l_debug_info := 'Apply payment against selected payment';
1323 ----------------------------------------------------------------------------------
1324 IF (PG_DEBUG = 'Y') THEN
1325 arp_standard.debug(l_debug_info);
1326 END IF;
1327
1328 AR_RECEIPT_API_PUB.apply_open_receipt
1329 (p_api_version => 1.0,
1330 p_init_msg_list => FND_API.G_TRUE,
1331 p_commit => FND_API.G_FALSE,
1332 x_return_status => l_return_status,
1333 x_msg_count => l_msg_count,
1334 x_msg_data => l_msg_data,
1338 p_called_from => 'IREC',
1335 p_cash_receipt_id => l_sel_cash_receipt_id,
1336 p_open_cash_receipt_id => credit_trx_record.cash_receipt_id,
1337 p_amount_applied => credit_trx_record.application_amount,
1339 x_application_ref_num => l_application_ref_num,
1340 x_receivable_application_id => l_receivable_application_id,
1341 x_applied_rec_app_id => l_applied_rec_app_id,
1342 x_acctd_amount_applied_from => l_acctd_amount_applied_from,
1343 x_acctd_amount_applied_to => l_acctd_amount_applied_to
1344 );
1345
1346 END IF;
1347
1348 --Check for errors
1349 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 x_msg_data := x_msg_data || l_msg_data;
1352 x_msg_count := x_msg_count + l_msg_count;
1353
1354 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1355
1356 RETURN;
1357 END IF;
1358
1359 END IF;
1360
1361 END LOOP;
1362
1363 ----------------------------------------------------------------------------------
1364 l_debug_info := 'Apply invoices against the selected payment';
1365 ----------------------------------------------------------------------------------
1366 IF (PG_DEBUG = 'Y') THEN
1367 arp_standard.debug(l_debug_info);
1368 END IF;
1369
1370 FOR debit_trx_record in debit_transactions(p_currency_code)
1371 LOOP
1372 IF (PG_DEBUG = 'Y') THEN
1373 arp_standard.debug('-------Debit Transaction Information------');
1374 arp_standard.debug('Customer Id: ' || debit_trx_record.customer_id);
1375 arp_standard.debug('Customer Site Use Id: ' || debit_trx_record.customer_site_use_id);
1376 arp_standard.debug('Customer Trx Id: ' || debit_trx_record.customer_trx_id);
1377 arp_standard.debug('Trx Number: ' || debit_trx_record.trx_number);
1378 arp_standard.debug('Trx Date: ' || debit_trx_record.trx_date);
1379 arp_standard.debug('Trx Class: ' || debit_trx_record.trx_class);
1380 arp_standard.debug('Due Date: ' || debit_trx_record.due_date);
1381 arp_standard.debug('Payment Schedule Id: ' || debit_trx_record.payment_schedule_id);
1382 arp_standard.debug('Status: ' || debit_trx_record.status);
1383 arp_standard.debug('Terms Sequence Number: ' || debit_trx_record.terms_sequence_number);
1384 arp_standard.debug('Line Amount: ' || debit_trx_record.line_amount);
1385 arp_standard.debug('Tax Amount: ' || debit_trx_record.tax_amount);
1386 arp_standard.debug('Freight Amount: ' || debit_trx_record.freight_amount);
1387 arp_standard.debug('Finance Charges: ' || debit_trx_record.trx_class);
1388 arp_standard.debug('Currency Code: ' || debit_trx_record.currency_code);
1389 arp_standard.debug('Amount Due Original: ' || debit_trx_record.amount_due_original);
1390 arp_standard.debug('Amount Due Remaining: ' || debit_trx_record.amount_due_remaining);
1391 arp_standard.debug('Discount Amount: ' || debit_trx_record.discount_amount);
1392 arp_standard.debug('Application Amount: ' || debit_trx_record.application_amount);
1393 arp_standard.debug('Cash Receipt Id: ' || debit_trx_record.cash_receipt_id);
1394 END IF;
1395
1396 IF (x_open_invoices_status = 'N') THEN
1397 ----------------------------------------------------------------------------------
1398 l_debug_info := 'Check if invoice will remain open after the application';
1399 ----------------------------------------------------------------------------------
1400 IF (PG_DEBUG = 'Y') THEN
1401 arp_standard.debug(l_debug_info);
1402 END IF;
1403
1404 IF (debit_trx_record.amount_due_remaining
1405 - ( debit_trx_record.discount_amount + debit_trx_record.application_amount) > 0) THEN
1406 x_open_invoices_status := 'Y';
1407 END IF;
1408 END IF;
1409
1410 ----------------------------------------------------------------------------------
1411 l_debug_info := 'Apply invoice against selected payment';
1412 ----------------------------------------------------------------------------------
1413 IF (PG_DEBUG = 'Y') THEN
1414 arp_standard.debug(l_debug_info);
1415 END IF;
1416
1417 AR_RECEIPT_API_PUB.apply(
1418 p_api_version => 1.0,
1419 p_init_msg_list => FND_API.G_TRUE,
1420 p_commit => FND_API.G_FALSE,
1421 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1422 x_return_status => l_return_status,
1423 x_msg_count => l_msg_count,
1424 x_msg_data => l_msg_data,
1425 p_cash_receipt_id => l_sel_cash_receipt_id,
1426 p_customer_trx_id => debit_trx_record.customer_trx_id,
1427 p_installment => debit_trx_record.terms_sequence_number,
1431 p_called_from => 'IREC'
1428 p_applied_payment_schedule_id => debit_trx_record.payment_schedule_id,
1429 p_amount_applied => debit_trx_record.application_amount,
1430 p_discount => debit_trx_record.discount_amount,
1432 );
1433
1434 --Check for errors
1435 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 x_msg_data := x_msg_data || l_msg_data;
1438 x_msg_count := x_msg_count + l_msg_count;
1439
1440 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1441
1442 RETURN;
1443 END IF;
1444
1445 END LOOP;
1446 ---------------------------------------------------------------------------
1447 l_debug_info := 'Close the credit and debit cursors';
1448 ---------------------------------------------------------------------------
1449 IF (PG_DEBUG = 'Y') THEN
1450 arp_standard.debug(l_debug_info);
1451 END IF;
1452
1453 COMMIT;
1454
1455 x_return_status := FND_API.G_RET_STS_SUCCESS;
1456
1457 EXCEPTION
1458 WHEN OTHERS THEN
1459 IF (PG_DEBUG = 'Y') THEN
1460 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1461 arp_standard.debug('- Currency Code: '||p_currency_code);
1462 arp_standard.debug('ERROR =>'|| SQLERRM);
1463 END IF;
1464
1465 IF(PG_DIAGNOSTICS = 'Y') THEN
1466 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1467 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1468 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1469 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1470 FND_MSG_PUB.ADD;
1471 ELSE
1472 x_msg_data := x_msg_data||SQLERRM;
1473 END IF;
1474
1475 x_cash_receipt_id := l_sel_cash_receipt_id;
1476
1477 END apply_credits_on_payment;
1478
1479 /*============================================================
1480 | PRIVATE procedure apply_credits_on_credit_memo
1481 |
1482 | DESCRIPTION
1483 | Applies selected credits against selected debits
1484 |
1485 | PSEUDO CODE/LOGIC
1486 |
1487 | PARAMETERS
1488 | p_customer_id IN NUMBER
1489 | p_customer_site_use_id IN NUMBER DEFAULT NULL
1490 | p_currency_code IN VARCHAR2
1491 |
1492 | KNOWN ISSUES
1493 |
1494 |
1495 |
1496 | NOTES
1497 |
1498 |
1499 |
1500 | MODIFICATION HISTORY
1501 | Date Author Description of Changes
1502 | 02-FEB-2005 vnb Created
1503 +============================================================*/
1504
1505 PROCEDURE apply_credits_on_credit_memo(p_currency_code IN VARCHAR2,
1506 x_open_invoices_status OUT NOCOPY VARCHAR2,
1507 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
1508 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
1509 x_msg_count OUT NOCOPY NUMBER,
1510 x_msg_data OUT NOCOPY VARCHAR2,
1511 x_return_status OUT NOCOPY VARCHAR2
1512 ) IS
1513
1514 CURSOR debit_transactions(p_currency_code VARCHAR2) IS
1515 SELECT CUSTOMER_ID,
1516 CUSTOMER_SITE_USE_ID,
1517 CUSTOMER_TRX_ID,
1518 TRX_NUMBER,
1519 TRX_DATE,
1520 TRX_CLASS,
1521 DUE_DATE,
1522 PAYMENT_SCHEDULE_ID,
1523 STATUS,
1524 TERMS_SEQUENCE_NUMBER,
1525 LINE_AMOUNT,
1526 TAX_AMOUNT ,
1527 FREIGHT_AMOUNT,
1528 FINANCE_CHARGES,
1529 CURRENCY_CODE ,
1530 AMOUNT_DUE_ORIGINAL,
1531 AMOUNT_DUE_REMAINING,
1532 SERVICE_CHARGE,
1533 DISCOUNT_AMOUNT,
1534 APPLICATION_AMOUNT,
1535 CASH_RECEIPT_ID
1536 FROM ar_irec_apply_credit_gt
1537 WHERE CURRENCY_CODE = p_currency_code
1538 AND ( TRX_CLASS = 'INV' OR
1539 TRX_CLASS = 'DM' OR
1540 TRX_CLASS = 'CB' OR
1541 TRX_CLASS = 'DEP'
1542 )
1543 ORDER BY AMOUNT_DUE_REMAINING ASC;
1544
1545 CURSOR credit_transactions(p_currency_code VARCHAR2) IS
1546 SELECT CUSTOMER_ID,
1547 CUSTOMER_SITE_USE_ID,
1548 CUSTOMER_TRX_ID,
1549 TRX_NUMBER,
1550 TRX_DATE,
1551 TRX_CLASS,
1552 DUE_DATE,
1553 PAYMENT_SCHEDULE_ID,
1554 STATUS,
1555 TERMS_SEQUENCE_NUMBER,
1556 LINE_AMOUNT,
1557 TAX_AMOUNT ,
1558 FREIGHT_AMOUNT,
1559 FINANCE_CHARGES,
1560 CURRENCY_CODE ,
1561 AMOUNT_DUE_ORIGINAL,
1562 AMOUNT_DUE_REMAINING,
1563 SERVICE_CHARGE,
1564 DISCOUNT_AMOUNT,
1565 APPLICATION_AMOUNT,
1569 AND ( TRX_CLASS = 'CM' OR
1566 CASH_RECEIPT_ID
1567 FROM ar_irec_apply_credit_gt
1568 WHERE CURRENCY_CODE = p_currency_code
1570 TRX_CLASS = 'PMT'
1571 )
1572 ORDER BY PAYMENT_SCHEDULE_ID;
1573
1574 debit_trx_record debit_transactions%ROWTYPE;
1575 credit_trx_record credit_transactions%ROWTYPE;
1576
1577 l_next_transaction VARCHAR2(10);
1578 l_debit_application_amount NUMBER;
1579 l_credit_application_amount NUMBER;
1580 l_credit_trx_class VARCHAR2(20);
1581
1582 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1583 l_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
1584 l_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
1585
1586 l_return_status VARCHAR2(10);
1587 l_msg_count NUMBER;
1588 l_msg_data VARCHAR2(255);
1589
1590 l_defaulting_rule_used VARCHAR2(255);
1591 l_error_message VARCHAR2(255);
1592
1593 l_application_amount NUMBER;
1594
1595 l_open_gl_date DATE;
1596 l_gl_date DATE;
1597 l_inv_date DATE;
1598 l_receipt_date DATE;
1599
1600 l_found VARCHAR2(1);
1601
1602 l_procedure_name VARCHAR2(50);
1603 l_debug_info VARCHAR2(200);
1604
1605 BEGIN
1606 x_msg_count := 0;
1607 x_msg_data := '*';
1608 x_open_invoices_status := 'N';
1609 x_return_status := FND_API.G_RET_STS_ERROR;
1610
1611 l_procedure_name := '.apply_credits_on_credit_memo';
1612 l_debit_application_amount := 0;
1613 l_credit_application_amount:= 0;
1614 l_next_transaction := 'BOTH';
1615 l_gl_date := sysdate;
1616
1617 SAVEPOINT ARI_APPLY_CREDITS_CM;
1618
1619
1620 ---------------------------------------------------------------------------
1621 l_debug_info := 'Open the credit and debit cursors';
1622 ---------------------------------------------------------------------------
1623 IF (PG_DEBUG = 'Y') THEN
1624 arp_standard.debug(l_debug_info);
1625 END IF;
1626 OPEN debit_transactions(p_currency_code);
1627 OPEN credit_transactions(p_currency_code);
1628
1629 LOOP
1630 ---------------------------------------------------------------------------
1631 l_debug_info := 'Fetch debit and credit transactions';
1632 ---------------------------------------------------------------------------
1633 IF (PG_DEBUG = 'Y') THEN
1634 arp_standard.debug(l_debug_info);
1635 END IF;
1636
1637 IF (l_next_transaction = 'DEBIT') THEN
1638 FETCH debit_transactions INTO debit_trx_record;
1639 ELSIF (l_next_transaction = 'CREDIT') THEN
1640 FETCH credit_transactions INTO credit_trx_record;
1641 ELSIF (l_next_transaction = 'BOTH') THEN
1642 FETCH debit_transactions INTO debit_trx_record;
1643 FETCH credit_transactions INTO credit_trx_record;
1644 END IF;
1645
1646 EXIT WHEN ((debit_transactions%NOTFOUND) AND (credit_transactions%NOTFOUND));
1647
1648 --Bug 4105891 - Check if there will be a non-zero remaining amount on the invoice after application
1649 IF (l_next_transaction = 'BOTH' OR l_next_transaction = 'DEBIT') THEN
1650 IF (debit_trx_record.amount_due_remaining
1651 - ( debit_trx_record.discount_amount + debit_trx_record.application_amount) > 0) THEN
1652 x_open_invoices_status := 'Y';
1653 END IF;
1654 END IF;
1655
1656 l_debit_application_amount := debit_trx_record.application_amount;
1657 l_credit_application_amount := - credit_trx_record.application_amount;
1658 l_credit_trx_class := credit_trx_record.trx_class;
1659
1660 -- Compare the application amount for the credit and debit transactions,
1661 -- to decide what should be the next transaction to be considered for application.
1662 IF (l_debit_application_amount > l_credit_application_amount) THEN
1663
1664 l_next_transaction := 'CREDIT';
1665 l_application_amount := l_credit_application_amount;
1666 debit_trx_record.application_amount := l_debit_application_amount - l_credit_application_amount;
1667
1668 ELSIF (l_debit_application_amount < l_credit_application_amount) THEN
1669
1670 l_next_transaction := 'DEBIT';
1671 l_application_amount := l_debit_application_amount;
1672 --Bug 4042557 - Change sign in credit application amount
1673 credit_trx_record.application_amount := -(l_credit_application_amount - l_debit_application_amount);
1674
1675 ELSE
1676
1677 l_next_transaction := 'BOTH';
1678 l_application_amount := l_debit_application_amount;
1679
1680 END IF;
1681
1682 IF (PG_DEBUG = 'Y') THEN
1683 arp_standard.debug('-------Debit Transaction Information------');
1684 arp_standard.debug('Customer Id: ' || debit_trx_record.customer_id);
1685 arp_standard.debug('Customer Site Use Id: ' || debit_trx_record.customer_site_use_id);
1689 arp_standard.debug('Trx Class: ' || debit_trx_record.trx_class);
1686 arp_standard.debug('Customer Trx Id: ' || debit_trx_record.customer_trx_id);
1687 arp_standard.debug('Trx Number: ' || debit_trx_record.trx_number);
1688 arp_standard.debug('Trx Date: ' || debit_trx_record.trx_date);
1690 arp_standard.debug('Due Date: ' || debit_trx_record.due_date);
1691 arp_standard.debug('Payment Schedule Id: ' || debit_trx_record.payment_schedule_id);
1692 arp_standard.debug('Status: ' || debit_trx_record.status);
1693 arp_standard.debug('Terms Sequence Number: ' || debit_trx_record.terms_sequence_number);
1694 arp_standard.debug('Line Amount: ' || debit_trx_record.line_amount);
1695 arp_standard.debug('Tax Amount: ' || debit_trx_record.tax_amount);
1696 arp_standard.debug('Freight Amount: ' || debit_trx_record.freight_amount);
1697 arp_standard.debug('Finance Charges: ' || debit_trx_record.trx_class);
1698 arp_standard.debug('Currency Code: ' || debit_trx_record.currency_code);
1699 arp_standard.debug('Amount Due Original: ' || debit_trx_record.amount_due_original);
1700 arp_standard.debug('Amount Due Remaining: ' || debit_trx_record.amount_due_remaining);
1701 arp_standard.debug('Discount Amount: ' || debit_trx_record.discount_amount);
1702 arp_standard.debug('Application Amount: ' || debit_trx_record.application_amount);
1703 arp_standard.debug('Cash Receipt Id: ' || debit_trx_record.cash_receipt_id);
1704 arp_standard.debug('-------Credit Transaction Information------');
1705 arp_standard.debug('Customer Id: ' || credit_trx_record.customer_id);
1706 arp_standard.debug('Customer Site Use Id: ' || credit_trx_record.customer_site_use_id);
1707 arp_standard.debug('Customer Trx Id: ' || credit_trx_record.customer_trx_id);
1708 arp_standard.debug('Trx Number: ' || credit_trx_record.trx_number);
1709 arp_standard.debug('Trx Date: ' || credit_trx_record.trx_date);
1710 arp_standard.debug('Trx Class: ' || credit_trx_record.trx_class);
1711 arp_standard.debug('Due Date: ' || credit_trx_record.due_date);
1712 arp_standard.debug('Payment Schedule Id: ' || credit_trx_record.payment_schedule_id);
1713 arp_standard.debug('Status: ' || credit_trx_record.status);
1714 arp_standard.debug('Terms Sequence Number: ' || credit_trx_record.terms_sequence_number);
1715 arp_standard.debug('Line Amount: ' || credit_trx_record.line_amount);
1716 arp_standard.debug('Tax Amount: ' || credit_trx_record.tax_amount);
1717 arp_standard.debug('Freight Amount: ' || credit_trx_record.freight_amount);
1718 arp_standard.debug('Finance Charges: ' || credit_trx_record.trx_class);
1719 arp_standard.debug('Currency Code: ' || credit_trx_record.currency_code);
1720 arp_standard.debug('Amount Due Original: ' || credit_trx_record.amount_due_original);
1721 arp_standard.debug('Amount Due Remaining: ' || credit_trx_record.amount_due_remaining);
1722 arp_standard.debug('Discount Amount: ' || credit_trx_record.discount_amount);
1723 arp_standard.debug('Application Amount: ' || credit_trx_record.application_amount);
1724 arp_standard.debug('Cash Receipt Id: ' || credit_trx_record.cash_receipt_id);
1725 END IF;
1726
1727 -- Bug 4103527 - Display error icons for duplicate application
1728 BEGIN
1729
1730 select 'Y'
1731 into l_found
1732 from ar_receivable_applications rap
1733 where rap.payment_schedule_id = credit_trx_record.payment_schedule_id
1734 and rap.applied_payment_schedule_id = debit_trx_record.payment_schedule_id
1735 and rap.display = 'Y'
1736 and rap.status = 'APP';
1737
1738 IF l_found = 'Y' THEN
1739 x_dup_appln_dbt_psid := debit_trx_record.payment_schedule_id;
1740 x_dup_appln_crdt_psid := credit_trx_record.payment_schedule_id;
1741
1742 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1743
1744 FND_MESSAGE.SET_NAME('AR', 'AR_RW_PAID_INVOICE_TWICE' );
1745 FND_MSG_PUB.ADD;
1746
1747 AR_IREC_PAYMENTS.write_error_messages(x_msg_data,x_msg_count);
1748 RETURN;
1749 END IF;
1750 EXCEPTION
1751 WHEN NO_DATA_FOUND THEN
1752 null;
1753 END;
1754
1755 IF ( l_credit_trx_class = 'CM') THEN
1756 ---------------------------------------------------------------------------
1757 l_debug_info := 'Apply credit memo against the debit transaction';
1758 ---------------------------------------------------------------------------
1759 IF (PG_DEBUG = 'Y') THEN
1760 arp_standard.debug(l_debug_info);
1761 END IF;
1762
1763 --Bug 4042557: Fetch the GL date for the credit memo
1764 select trunc(gl_date), trunc(trx_date)
1765 into l_gl_date, l_receipt_date
1766 from ar_payment_schedules
1767 where payment_schedule_id = credit_trx_record.payment_schedule_id;
1768
1769 select trunc(gl_date)
1770 into l_inv_date
1771 from ar_payment_schedules
1772 where payment_schedule_id = debit_trx_record.payment_schedule_id;
1773
1774 --Bug 5911297 - Using apply credit applied GL date profile and pass appropriate gl date
1778 l_gl_date := Greatest(l_inv_date, l_receipt_date, l_gl_date);
1775 IF(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT') = 'INV_REC_SYS_DT') THEN
1776 l_gl_date := Greatest(l_inv_date, l_receipt_date, trunc(sysdate));
1777 ELSIF(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT') = 'INV_REC_DT') THEN
1779 END IF;
1780 --Bug 6062210: Fetch the open GL date for the credit memo
1781 if(arp_util.validate_and_default_gl_date( gl_date => l_gl_date,
1782 trx_date => l_receipt_date,
1783 validation_date1 => null,
1784 validation_date2 => null,
1785 validation_date3 => null,
1786 default_date1 => null,
1787 default_date2 => null,
1788 default_date3 => null,
1789 p_allow_not_open_flag => null,
1790 p_invoicing_rule_id => null,
1791 p_set_of_books_id => null,
1792 p_application_id => 222,
1793 default_gl_date => l_open_gl_date,
1794 defaulting_rule_used => l_defaulting_rule_used,
1795 error_message => l_error_message)) then
1796
1797 l_gl_date := l_open_gl_date;
1798
1799 end if;
1800
1801
1802 arp_process_application.cm_application(
1803 p_cm_ps_id => credit_trx_record.payment_schedule_id,
1804 p_invoice_ps_id => debit_trx_record.payment_schedule_id,
1805 p_amount_applied => l_application_amount,
1806 p_apply_date => trunc(sysdate),
1807 p_gl_date => l_gl_date,
1808 p_ussgl_transaction_code => null,
1809 p_attribute_category => null,
1810 p_attribute1 => null,
1811 p_attribute2 => null,
1812 p_attribute3 => null,
1813 p_attribute4 => null,
1814 p_attribute5 => null,
1815 p_attribute6 => null,
1816 p_attribute7 => null,
1817 p_attribute8 => null,
1818 p_attribute9 => null,
1819 p_attribute10 => null,
1820 p_attribute11 => null,
1821 p_attribute12 => null,
1822 p_attribute13 => null,
1823 p_attribute14 => null,
1824 p_attribute15 => null,
1825 p_global_attribute_category => null,
1826 p_global_attribute1 => null,
1827 p_global_attribute2 => null,
1828 p_global_attribute3 => null,
1829 p_global_attribute4 => null,
1830 p_global_attribute5 => null,
1831 p_global_attribute6 => null,
1832 p_global_attribute7 => null,
1833 p_global_attribute8 => null,
1834 p_global_attribute9 => null,
1835 p_global_attribute10 => null,
1836 p_global_attribute11 => null,
1837 p_global_attribute12 => null,
1838 p_global_attribute13 => null,
1839 p_global_attribute14 => null,
1840 p_global_attribute15 => null,
1841 p_global_attribute16 => null,
1842 p_global_attribute17 => null,
1843 p_global_attribute18 => null,
1844 p_global_attribute19 => null,
1845 p_global_attribute20 => null,
1846 p_customer_trx_line_id => null,
1847 p_comments => null,
1848 p_module_name => null,
1849 p_module_version => null,
1850 p_out_rec_application_id => l_receivable_application_id,
1851 p_acctd_amount_applied_from =>l_acctd_amount_applied_from,
1852 p_acctd_amount_applied_to=>l_acctd_amount_applied_to);
1853
1854 IF (l_receivable_application_id IS NULL) THEN
1855 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1856 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1857 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1858 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1859 FND_MSG_PUB.ADD;
1860
1861 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1862 RETURN;
1863 END IF;
1864
1865 ELSIF ( l_credit_trx_class = 'PMT') THEN
1866 ---------------------------------------------------------------------------
1867 l_debug_info := 'Apply receipt against the debit transaction';
1868 ---------------------------------------------------------------------------
1869 IF (PG_DEBUG = 'Y') THEN
1870 arp_standard.debug(l_debug_info);
1871 END IF;
1872 AR_RECEIPT_API_PUB.apply(
1873 p_api_version => 1.0,
1874 p_init_msg_list => FND_API.G_TRUE,
1875 p_commit => FND_API.G_FALSE,
1876 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1880 p_cash_receipt_id => credit_trx_record.cash_receipt_id,
1877 x_return_status => l_return_status,
1878 x_msg_count => l_msg_count,
1879 x_msg_data => l_msg_data,
1881 p_customer_trx_id => debit_trx_record.customer_trx_id,
1882 p_applied_payment_schedule_id => debit_trx_record.payment_schedule_id,
1883 p_amount_applied => l_application_amount,
1884 p_discount => 0,
1885 p_called_from => 'IREC',
1886 p_apply_date => trunc(sysdate)
1887 );
1888
1889 -- Check for error
1890 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1891 x_msg_count := l_msg_count;
1892 x_msg_data := l_msg_data;
1893 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1894 RETURN;
1895 END IF;
1896
1897 END IF;
1898
1899 END LOOP;
1900
1901 ---------------------------------------------------------------------------
1902 l_debug_info := 'Close the credit and debit cursors';
1903 ---------------------------------------------------------------------------
1904 IF (PG_DEBUG = 'Y') THEN
1905 arp_standard.debug(l_debug_info);
1906 END IF;
1907 CLOSE credit_transactions;
1908 CLOSE debit_transactions;
1909
1910 COMMIT;
1911
1912 x_return_status := FND_API.G_RET_STS_SUCCESS;
1913
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 IF (PG_DEBUG = 'Y') THEN
1917 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1918 arp_standard.debug('- Currency Code: '||p_currency_code);
1919 arp_standard.debug('ERROR =>'|| SQLERRM);
1920 END IF;
1921
1922 IF(PG_DIAGNOSTICS = 'Y') THEN
1923 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1924 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1925 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1926 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1927 FND_MSG_PUB.ADD;
1928 ELSE
1929 x_msg_data := x_msg_data||SQLERRM;
1930 END IF;
1931
1932 END apply_credits_on_credit_memo;
1933
1934 /*============================================================
1935 | PUBLIC procedure copy_apply_credits_records
1936 |
1937 | DESCRIPTION
1938 | Copy the open debits for the active customer, site and currency from the
1939 | Apply Credits GT to the Transaction List GT
1940 |
1941 | PSEUDO CODE/LOGIC
1942 |
1943 | PARAMETERS
1944 | p_customer_id IN NUMBER
1945 | p_customer_site_use_id IN NUMBER DEFAULT NULL
1946 | p_currency_code IN VARCHAR2
1947 |
1948 | KNOWN ISSUES
1949 |
1950 |
1951 |
1952 | NOTES
1953 |
1954 |
1955 |
1956 | MODIFICATION HISTORY
1957 | Date Author Description of Changes
1958 | 12-OCT-2004 vnb Created
1959 +============================================================*/
1960
1961 PROCEDURE copy_apply_credits_records( p_customer_id IN NUMBER,
1962 p_customer_site_use_id IN NUMBER DEFAULT NULL,
1963 p_currency_code IN VARCHAR2
1964 ) IS
1965
1966 CURSOR open_debits_list (p_customer_id NUMBER,
1967 p_customer_site_use_id NUMBER,
1968 p_currency_code VARCHAR2) IS
1969
1970 SELECT
1971 CUSTOMER_ID,
1972 CUSTOMER_SITE_USE_ID,
1973 TRX_CLASS,
1974 PAYMENT_SCHEDULE_ID,
1975 CURRENCY_CODE
1976 FROM ar_irec_apply_credit_gt
1977 WHERE PAY_FOR_CUSTOMER_ID = p_customer_id
1978 AND ( (PAY_FOR_CUSTOMER_SITE_ID IS NULL AND p_customer_site_use_id IS NULL) OR CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID))
1979 AND CURRENCY_CODE = p_currency_code
1980 AND ( TRX_CLASS = 'INV' OR
1981 TRX_CLASS = 'DM' OR
1982 TRX_CLASS = 'CB' OR
1983 TRX_CLASS = 'DEP'
1984 )
1985 AND (AMOUNT_DUE_REMAINING - (nvl(DISCOUNT_AMOUNT,0) + APPLICATION_AMOUNT) > 0);
1986
1987 l_procedure_name VARCHAR2(50);
1988 l_debug_info VARCHAR2(200);
1989
1990 BEGIN
1991
1992 l_procedure_name := '.copy_apply_credits_records';
1993
1994
1995 ----------------------------------------------------------------------------------------
1996 l_debug_info := 'Clear the Transaction List GT for the active customer, site, currency code';
1997 -----------------------------------------------------------------------------------------
1998 IF (PG_DEBUG = 'Y') THEN
1999 arp_standard.debug(l_debug_info);
2000 END IF;
2001
2002 DELETE FROM AR_IREC_PAYMENT_LIST_GT
2003 WHERE CUSTOMER_ID = p_customer_id
2004 AND CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID)
2005 AND CURRENCY_CODE = p_currency_code;
2006
2007 ----------------------------------------------------------------------------------------
2011 arp_standard.debug(l_debug_info);
2008 l_debug_info := 'Fetch open debits from Apply Credits GT into Transaction List GT';
2009 -----------------------------------------------------------------------------------------
2010 IF (PG_DEBUG = 'Y') THEN
2012 END IF;
2013
2014 FOR trx IN open_debits_list(p_customer_id,
2015 p_customer_site_use_id,
2016 p_currency_code )
2017 LOOP
2018 AR_IREC_PAYMENTS.create_transaction_list_record(
2019 p_payment_schedule_id => trx.payment_schedule_id,
2020 p_customer_id => p_customer_id,
2021 p_customer_site_id => p_customer_site_use_id
2022 );
2023
2024 END LOOP;
2025
2026 COMMIT;
2027
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 IF (PG_DEBUG = 'Y') THEN
2031 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2032 arp_standard.debug('- Customer Id: '||p_customer_id);
2033 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
2034 arp_standard.debug('- Currency Code: '||p_currency_code);
2035 arp_standard.debug('ERROR =>'|| SQLERRM);
2036 END IF;
2037
2038 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2039 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2040 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2041 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2042 FND_MSG_PUB.ADD;
2043
2044 END copy_apply_credits_records;
2045
2046 /*============================================================
2047 | PUBLIC procedure select_credit_to_apply
2048 |
2049 | DESCRIPTION
2050 | Select credit to apply other transactions against
2051 |
2052 | PSEUDO CODE/LOGIC
2053 |
2054 | PARAMETERS
2055 | p_customer_id IN NUMBER
2056 | p_customer_site_use_id IN NUMBER DEFAULT NULL
2057 | p_currency_code IN VARCHAR2
2058 | x_return_status OUT VARCHAR2 Returns 'S' if successful; 'E' if duplicate application
2059 | x_credit_ps_id OUT NUMBER
2060 | x_debit_ps_id OUT NUMBER Returns debit payment schedule id if duplicate application; else null
2061 |
2062 | KNOWN ISSUES
2063 |
2064 | NOTES
2065 |
2066 |
2067 | MODIFICATION HISTORY
2068 | Date Author Description of Changes
2069 | 02-FEB-2005 vnb Created
2070 | 07-SEP-2005 7 rrsaneve Updated the cursors credit_to_apply ,duplicate_application as bug#6311033 fix.
2071 +============================================================*/
2072 PROCEDURE select_credit_to_apply( p_currency_code IN VARCHAR2,
2073 x_return_status OUT NOCOPY VARCHAR2,
2074 x_credit_ps_id OUT NOCOPY NUMBER,
2075 x_debit_ps_id OUT NOCOPY NUMBER)
2076 IS
2077 CURSOR credit_to_apply (p_currency_code VARCHAR2) IS
2078 select acgt.payment_schedule_id
2079 from ar_irec_apply_credit_gt acgt
2080 where acgt.currency_code = p_currency_code
2081 and acgt.trx_class = 'PMT'
2082 and (not exists
2083 (
2084 select arp.payment_schedule_id
2085 from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
2086 where acgt1.currency_code = p_currency_code
2087 and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
2088 and arp.payment_schedule_id = acgt.payment_schedule_id
2089 )
2090 or ( (
2091 select sum(amount_applied)
2092 from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
2093 where acgt1.currency_code = p_currency_code
2094 and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
2095 and arp.payment_schedule_id = acgt.payment_schedule_id) = 0
2096 )
2097 )
2098 order by acgt.trx_date asc;
2099
2100 /*
2101
2102 CURSOR duplicate_application(p_currency_code VARCHAR2) IS
2103 select acgt.payment_schedule_id, arp.applied_payment_schedule_id
2104 from ar_irec_apply_credit_gt acgt,ar_receivable_applications_all arp,ar_irec_apply_credit_gt acgt1
2105 where acgt.currency_code = p_currency_code
2106 and acgt.trx_class = 'PMT'
2107 and acgt.payment_schedule_id = arp.payment_schedule_id
2108 and acgt1.currency_code = p_currency_code
2109 and acgt1.payment_schedule_id = arp.applied_payment_schedule_id;
2110
2111 */
2112 /* bug#6311033-APPLY CREDIT FAILS WITH YOU HAVE PAID SAME INVOICE TWICE ERROR */
2113
2114 CURSOR duplicate_application(p_currency_code VARCHAR2) IS
2115 select acgt.payment_schedule_id, arp.applied_payment_schedule_id
2116 from ar_irec_apply_credit_gt acgt,ar_receivable_applications_all arp,ar_irec_apply_credit_gt acgt1
2117 where acgt.currency_code = p_currency_code
2118 and acgt.trx_class = 'PMT'
2119 and acgt.payment_schedule_id = arp.payment_schedule_id
2120 and acgt1.currency_code = p_currency_code
2121 and acgt1.payment_schedule_id = arp.applied_payment_schedule_id
2122 and (
2123 select sum(amount_applied)
2127 and arp.payment_schedule_id = acgt.payment_schedule_id) > 0;
2124 from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
2125 where acgt1.currency_code = p_currency_code
2126 and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
2128
2129
2130 l_procedure_name VARCHAR2(50);
2131 l_debug_info VARCHAR2(200);
2132
2133 credit_to_apply_record credit_to_apply%ROWTYPE;
2134 duplicate_application_record duplicate_application%ROWTYPE;
2135
2136 BEGIN
2137 x_return_status := FND_API.G_RET_STS_ERROR;
2138 x_credit_ps_id := 0;
2139 x_debit_ps_id := 0;
2140
2141 l_procedure_name := '.select_credit_to_apply';
2142
2143 ---------------------------------------------------------------------------
2144 l_debug_info := 'Open the cursor to select credit to apply';
2145 ---------------------------------------------------------------------------
2146 IF (PG_DEBUG = 'Y') THEN
2147 arp_standard.debug(l_debug_info);
2148 END IF;
2149
2150 OPEN credit_to_apply(p_currency_code);
2151 FETCH credit_to_apply INTO x_credit_ps_id;
2152
2153 IF (credit_to_apply%FOUND) THEN
2154 x_return_status := FND_API.G_RET_STS_SUCCESS;
2155 ELSE
2156 ---------------------------------------------------------------------------
2157 l_debug_info := 'Open the cursor to return duplicate application transactions';
2158 ---------------------------------------------------------------------------
2159 IF (PG_DEBUG = 'Y') THEN
2160 arp_standard.debug(l_debug_info);
2161 END IF;
2162 OPEN duplicate_application(p_currency_code);
2163 FETCH duplicate_application INTO duplicate_application_record;
2164
2165 IF (duplicate_application%FOUND) THEN
2166 ---------------------------------------------------------------------------
2167 l_debug_info := 'If a record is found, return the credit and debit transaction';
2168 ---------------------------------------------------------------------------
2169 IF (PG_DEBUG = 'Y') THEN
2170 arp_standard.debug(l_debug_info);
2171 END IF;
2172
2173 x_credit_ps_id := duplicate_application_record.payment_schedule_id;
2174 x_debit_ps_id := duplicate_application_record.applied_payment_schedule_id;
2175
2176 END IF;
2177
2178 CLOSE duplicate_application;
2179
2180 END IF;
2181
2182 CLOSE credit_to_apply;
2183
2184 EXCEPTION
2185 WHEN OTHERS THEN
2186 IF (PG_DEBUG = 'Y') THEN
2187 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2188 arp_standard.debug('- Currency Code: '||p_currency_code);
2189 arp_standard.debug('ERROR =>'|| SQLERRM);
2190 END IF;
2191
2192 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2193 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2194 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2195 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2196 FND_MSG_PUB.ADD;
2197 END select_credit_to_apply;
2198
2199 END AR_IREC_APPLY_CREDITS ;
2200