[Home] [Help]
PACKAGE BODY: APPS.AR_IREC_APPLY_CREDITS
Source
4 /*=======================================================================+
1 PACKAGE BODY AR_IREC_APPLY_CREDITS AS
2 /* $Header: ARIAPCRB.pls 120.27.12020000.3 2012/10/25 17:49:10 melapaku ship $ */
3
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,
118 ATTRIBUTE15,
115 ATTRIBUTE12,
116 ATTRIBUTE13,
117 ATTRIBUTE14,
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);
169 p_customer_site_use_id,
166 END IF;
167
168 FOR trx IN transaction_list(p_customer_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 (
247 trx.PAY_FOR_CUSTOMER_SITE_ID,
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,
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
347 | PSEUDO CODE/LOGIC
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 |
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
368 PROCEDURE copy_open_debits(p_customer_id IN NUMBER,
365 | 26-MAY-2005 rsinthre Bug 4392371 - OIR needs to support cross customer payment
366 +============================================================*/
367
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 |
417 | DESCRIPTION
418 | Copy the transactions for the active customer, site and currency from the
419 | Transaction List GT to the Apply Credits GT
420 |
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';
504 END IF;
501 -----------------------------------------------------------------------------------------
502 IF (PG_DEBUG = 'Y') THEN
503 arp_standard.debug(l_debug_info);
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,
519 DUE_DATE,
516 CUSTOMER_TRX_ID,
517 TRX_NUMBER,TRX_DATE,
518 TRX_CLASS,
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,
556 INTERFACE_HEADER_ATTRIBUTE1,
557 INTERFACE_HEADER_ATTRIBUTE2,
558 INTERFACE_HEADER_ATTRIBUTE3,
559 INTERFACE_HEADER_ATTRIBUTE4,
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,
629 ct.INTERFACE_HEADER_ATTRIBUTE11,
626 ct.INTERFACE_HEADER_ATTRIBUTE8,
627 ct.INTERFACE_HEADER_ATTRIBUTE9,
628 ct.INTERFACE_HEADER_ATTRIBUTE10,
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
690 PROCEDURE delete_all_debits(p_customer_id IN NUMBER,
687 | 13-OCT-2004 vnb Created
688 +============================================================*/
689
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 /*============================================================
743 | Deletes all credit transactions for the active customer, site and currency from the
740 | PUBLIC procedure delete_all_credits
741 |
742 | DESCRIPTION
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);
869 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
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);
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;
1007 ---------------------------------------------------------------------------
1004 end if;
1005
1006 IF (p_credit_memos_only = 'Y') THEN
1008 l_debug_info := 'Apply credits on credit memo';
1009 ---------------------------------------------------------------------------
1010 IF (PG_DEBUG = 'Y') THEN
1011 arp_standard.debug(l_debug_info);
1012 END IF;
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,
1121 AMOUNT_DUE_REMAINING,
1118 FINANCE_CHARGES,
1119 CURRENCY_CODE ,
1120 AMOUNT_DUE_ORIGINAL,
1122 SERVICE_CHARGE,
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 --Setting return status to success initially for Bug# 13520421
1194 x_return_status := FND_API.G_RET_STS_SUCCESS;
1195 l_procedure_name := '.apply_credits_on_payment';
1196
1197
1198 SAVEPOINT ARI_APPLY_CREDITS_PMT;
1199
1200
1201 --------------------------------------------------------------------------------
1202 l_debug_info := 'Get the credit transaction to apply other transactions against';
1203 --------------------------------------------------------------------------------
1204 IF (PG_DEBUG = 'Y') THEN
1205 arp_standard.debug(l_debug_info);
1206 END IF;
1207 select_credit_to_apply( p_currency_code => p_currency_code,
1208 x_return_status => x_return_status,
1209 x_credit_ps_id => x_dup_appln_crdt_psid,
1210 x_debit_ps_id => x_dup_appln_dbt_psid
1211 );
1212
1213 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1214
1215 IF (x_dup_appln_crdt_psid = 0) THEN
1216 ---------------------------------------------------------------------------
1217 l_debug_info := 'An unexpected error has occurred while finding the credit';
1218 ---------------------------------------------------------------------------
1219 IF (PG_DEBUG = 'Y') THEN
1220 arp_standard.debug(l_debug_info);
1221 END IF;
1222 APP_EXCEPTION.raise_exception;
1223 ELSE
1224 ---------------------------------------------------------------------------
1225 l_debug_info := 'Duplicate application error';
1226 ---------------------------------------------------------------------------
1227 IF (PG_DEBUG = 'Y') THEN
1228 arp_standard.debug(l_debug_info);
1229 END IF;
1230
1231 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1232
1233 FND_MESSAGE.SET_NAME('AR', 'AR_RW_PAID_INVOICE_TWICE' );
1234 FND_MSG_PUB.ADD;
1235
1236 x_cash_receipt_id := l_sel_cash_receipt_id;
1237
1238 RETURN;
1239 END IF;
1240 END IF;
1241
1242 --Get the cash_receipt_id if selected credit is a payment
1243 ---------------------------------------------------------------------------
1244 l_debug_info := 'Find cash receipt id';
1245 ---------------------------------------------------------------------------
1246
1247
1248 IF (PG_DEBUG = 'Y') THEN
1249 arp_standard.debug(l_debug_info);
1250 END IF;
1251 OPEN find_cash_receipt(x_dup_appln_crdt_psid);
1252 FETCH find_cash_receipt INTO find_cash_receipt_record;
1253 IF (find_cash_receipt%FOUND) THEN
1254 l_sel_cash_receipt_id := find_cash_receipt_record.cash_receipt_id;
1255 ELSE
1259 CLOSE find_cash_receipt;
1256 --Should not come here
1257 APP_EXCEPTION.raise_exception;
1258 END IF;
1260
1261 x_cash_receipt_id := l_sel_cash_receipt_id;
1262
1263 ----------------------------------------------------------------------------------
1264 l_debug_info := 'Apply credits against the selected payment';
1265 ----------------------------------------------------------------------------------
1266 IF (PG_DEBUG = 'Y') THEN
1267 arp_standard.debug(l_debug_info);
1268 END IF;
1269
1270 FOR credit_trx_record in credit_transactions(p_currency_code)
1271 LOOP
1272 --If this is the credit transaction selected to be applied against, do not do anything.
1273 IF (credit_trx_record.payment_schedule_id <> x_dup_appln_crdt_psid) THEN
1274
1275 IF (PG_DEBUG = 'Y') THEN
1276 arp_standard.debug('-------Credit Transaction Information------');
1277 arp_standard.debug('Customer Id: ' || credit_trx_record.customer_id);
1278 arp_standard.debug('Customer Site Use Id: ' || credit_trx_record.customer_site_use_id);
1279 arp_standard.debug('Customer Trx Id: ' || credit_trx_record.customer_trx_id);
1280 arp_standard.debug('Trx Number: ' || credit_trx_record.trx_number);
1281 arp_standard.debug('Trx Date: ' || credit_trx_record.trx_date);
1282 arp_standard.debug('Trx Class: ' || credit_trx_record.trx_class);
1283 arp_standard.debug('Due Date: ' || credit_trx_record.due_date);
1284 arp_standard.debug('Payment Schedule Id: ' || credit_trx_record.payment_schedule_id);
1285 arp_standard.debug('Status: ' || credit_trx_record.status);
1286 arp_standard.debug('Terms Sequence Number: ' || credit_trx_record.terms_sequence_number);
1287 arp_standard.debug('Line Amount: ' || credit_trx_record.line_amount);
1288 arp_standard.debug('Tax Amount: ' || credit_trx_record.tax_amount);
1289 arp_standard.debug('Freight Amount: ' || credit_trx_record.freight_amount);
1290 arp_standard.debug('Finance Charges: ' || credit_trx_record.trx_class);
1291 arp_standard.debug('Currency Code: ' || credit_trx_record.currency_code);
1292 arp_standard.debug('Amount Due Original: ' || credit_trx_record.amount_due_original);
1293 arp_standard.debug('Amount Due Remaining: ' || credit_trx_record.amount_due_remaining);
1294 arp_standard.debug('Discount Amount: ' || credit_trx_record.discount_amount);
1295 arp_standard.debug('Application Amount: ' || credit_trx_record.application_amount);
1296 arp_standard.debug('Cash Receipt Id: ' || credit_trx_record.cash_receipt_id);
1297 END IF;
1298
1299 IF (credit_trx_record.trx_class = 'CM') THEN
1300 ----------------------------------------------------------------------------------
1301 l_debug_info := 'Apply credit memo against selected payment';
1302 ----------------------------------------------------------------------------------
1303 IF (PG_DEBUG = 'Y') THEN
1304 arp_standard.debug(l_debug_info);
1305 END IF;
1306
1307 AR_RECEIPT_API_PUB.apply(
1308 p_api_version => 1.0,
1309 p_init_msg_list => FND_API.G_TRUE,
1310 p_commit => FND_API.G_FALSE,
1311 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1312 x_return_status => l_return_status,
1313 x_msg_count => l_msg_count,
1314 x_msg_data => l_msg_data,
1315 p_cash_receipt_id => l_sel_cash_receipt_id,
1316 p_customer_trx_id => credit_trx_record.customer_trx_id,
1317 p_installment => credit_trx_record.terms_sequence_number,
1318 p_applied_payment_schedule_id => credit_trx_record.payment_schedule_id,
1319 p_amount_applied => credit_trx_record.application_amount,
1320 p_discount => credit_trx_record.discount_amount,
1321 p_called_from => 'IREC'
1322 );
1323
1324 ELSIF (credit_trx_record.trx_class = 'PMT') THEN
1325 ----------------------------------------------------------------------------------
1326 l_debug_info := 'Apply payment against selected payment';
1327 ----------------------------------------------------------------------------------
1328 IF (PG_DEBUG = 'Y') THEN
1329 arp_standard.debug(l_debug_info);
1330 END IF;
1331
1332 AR_RECEIPT_API_PUB.apply_open_receipt
1333 (p_api_version => 1.0,
1334 p_init_msg_list => FND_API.G_TRUE,
1335 p_commit => FND_API.G_FALSE,
1336 x_return_status => l_return_status,
1337 x_msg_count => l_msg_count,
1338 x_msg_data => l_msg_data,
1339 p_cash_receipt_id => l_sel_cash_receipt_id,
1340 p_open_cash_receipt_id => credit_trx_record.cash_receipt_id,
1341 p_amount_applied => credit_trx_record.application_amount,
1342 p_called_from => 'IREC',
1343 x_application_ref_num => l_application_ref_num,
1344 x_receivable_application_id => l_receivable_application_id,
1348 );
1345 x_applied_rec_app_id => l_applied_rec_app_id,
1346 x_acctd_amount_applied_from => l_acctd_amount_applied_from,
1347 x_acctd_amount_applied_to => l_acctd_amount_applied_to
1349
1350 END IF;
1351
1352 --Check for errors
1353 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1354 x_return_status := FND_API.G_RET_STS_ERROR;
1355 x_msg_data := x_msg_data || l_msg_data;
1356 x_msg_count := x_msg_count + l_msg_count;
1357
1358 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1359
1360 RETURN;
1361 END IF;
1362
1363 END IF;
1364
1365 END LOOP;
1366
1367 ----------------------------------------------------------------------------------
1368 l_debug_info := 'Apply invoices against the selected payment';
1369 ----------------------------------------------------------------------------------
1370 IF (PG_DEBUG = 'Y') THEN
1371 arp_standard.debug(l_debug_info);
1372 END IF;
1373
1374 FOR debit_trx_record in debit_transactions(p_currency_code)
1375 LOOP
1376 IF (PG_DEBUG = 'Y') THEN
1377 arp_standard.debug('-------Debit Transaction Information------');
1378 arp_standard.debug('Customer Id: ' || debit_trx_record.customer_id);
1379 arp_standard.debug('Customer Site Use Id: ' || debit_trx_record.customer_site_use_id);
1380 arp_standard.debug('Customer Trx Id: ' || debit_trx_record.customer_trx_id);
1381 arp_standard.debug('Trx Number: ' || debit_trx_record.trx_number);
1382 arp_standard.debug('Trx Date: ' || debit_trx_record.trx_date);
1383 arp_standard.debug('Trx Class: ' || debit_trx_record.trx_class);
1384 arp_standard.debug('Due Date: ' || debit_trx_record.due_date);
1385 arp_standard.debug('Payment Schedule Id: ' || debit_trx_record.payment_schedule_id);
1386 arp_standard.debug('Status: ' || debit_trx_record.status);
1387 arp_standard.debug('Terms Sequence Number: ' || debit_trx_record.terms_sequence_number);
1388 arp_standard.debug('Line Amount: ' || debit_trx_record.line_amount);
1389 arp_standard.debug('Tax Amount: ' || debit_trx_record.tax_amount);
1390 arp_standard.debug('Freight Amount: ' || debit_trx_record.freight_amount);
1391 arp_standard.debug('Finance Charges: ' || debit_trx_record.trx_class);
1392 arp_standard.debug('Currency Code: ' || debit_trx_record.currency_code);
1393 arp_standard.debug('Amount Due Original: ' || debit_trx_record.amount_due_original);
1394 arp_standard.debug('Amount Due Remaining: ' || debit_trx_record.amount_due_remaining);
1395 arp_standard.debug('Discount Amount: ' || debit_trx_record.discount_amount);
1396 arp_standard.debug('Application Amount: ' || debit_trx_record.application_amount);
1397 arp_standard.debug('Cash Receipt Id: ' || debit_trx_record.cash_receipt_id);
1398 END IF;
1399
1400 IF (x_open_invoices_status = 'N') THEN
1401 ----------------------------------------------------------------------------------
1402 l_debug_info := 'Check if invoice will remain open after the application';
1403 ----------------------------------------------------------------------------------
1404 IF (PG_DEBUG = 'Y') THEN
1405 arp_standard.debug(l_debug_info);
1406 END IF;
1407
1408 IF (debit_trx_record.amount_due_remaining
1409 - ( debit_trx_record.discount_amount + debit_trx_record.application_amount) > 0) THEN
1410 x_open_invoices_status := 'Y';
1411 END IF;
1412 END IF;
1413
1414 ----------------------------------------------------------------------------------
1415 l_debug_info := 'Apply invoice against selected payment';
1416 ----------------------------------------------------------------------------------
1417 IF (PG_DEBUG = 'Y') THEN
1418 arp_standard.debug(l_debug_info);
1419 END IF;
1420
1421 AR_RECEIPT_API_PUB.apply(
1422 p_api_version => 1.0,
1423 p_init_msg_list => FND_API.G_TRUE,
1424 p_commit => FND_API.G_FALSE,
1425 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1426 x_return_status => l_return_status,
1427 x_msg_count => l_msg_count,
1428 x_msg_data => l_msg_data,
1429 p_cash_receipt_id => l_sel_cash_receipt_id,
1430 p_customer_trx_id => debit_trx_record.customer_trx_id,
1431 p_installment => debit_trx_record.terms_sequence_number,
1432 p_applied_payment_schedule_id => debit_trx_record.payment_schedule_id,
1433 p_amount_applied => debit_trx_record.application_amount,
1434 p_discount => debit_trx_record.discount_amount,
1435 p_called_from => 'IREC'
1436 );
1437
1438 --Check for errors
1439 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1440 x_return_status := FND_API.G_RET_STS_ERROR;
1441 x_msg_data := x_msg_data || l_msg_data;
1442 x_msg_count := x_msg_count + l_msg_count;
1443
1444 ROLLBACK TO ARI_APPLY_CREDITS_PMT;
1445
1446 RETURN;
1447 END IF;
1448
1449 END LOOP;
1453 IF (PG_DEBUG = 'Y') THEN
1450 ---------------------------------------------------------------------------
1451 l_debug_info := 'Close the credit and debit cursors';
1452 ---------------------------------------------------------------------------
1454 arp_standard.debug(l_debug_info);
1455 END IF;
1456
1457 COMMIT;
1458
1459 x_return_status := FND_API.G_RET_STS_SUCCESS;
1460
1461 EXCEPTION
1462 WHEN OTHERS THEN
1463 IF (PG_DEBUG = 'Y') THEN
1464 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1465 arp_standard.debug('- Currency Code: '||p_currency_code);
1466 arp_standard.debug('ERROR =>'|| SQLERRM);
1467 END IF;
1468
1469 IF(PG_DIAGNOSTICS = 'Y') THEN
1470 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1471 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1472 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1473 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1474 FND_MSG_PUB.ADD;
1475 ELSE
1476 x_msg_data := x_msg_data||SQLERRM;
1477 END IF;
1478
1479 x_cash_receipt_id := l_sel_cash_receipt_id;
1480
1481 END apply_credits_on_payment;
1482
1483 /*============================================================
1484 | PRIVATE procedure apply_credits_on_credit_memo
1485 |
1486 | DESCRIPTION
1487 | Applies selected credits against selected debits
1488 |
1489 | PSEUDO CODE/LOGIC
1490 |
1491 | PARAMETERS
1492 | p_customer_id IN NUMBER
1493 | p_customer_site_use_id IN NUMBER DEFAULT NULL
1494 | p_currency_code IN VARCHAR2
1495 |
1496 | KNOWN ISSUES
1497 |
1498 |
1499 |
1500 | NOTES
1501 |
1502 |
1503 |
1504 | MODIFICATION HISTORY
1505 | Date Author Description of Changes
1506 | 02-FEB-2005 vnb Created
1507 +============================================================*/
1508
1509 PROCEDURE apply_credits_on_credit_memo(p_currency_code IN VARCHAR2,
1510 x_open_invoices_status OUT NOCOPY VARCHAR2,
1511 x_dup_appln_dbt_psid OUT NOCOPY NUMBER,
1512 x_dup_appln_crdt_psid OUT NOCOPY NUMBER,
1513 x_msg_count OUT NOCOPY NUMBER,
1514 x_msg_data OUT NOCOPY VARCHAR2,
1515 x_return_status OUT NOCOPY VARCHAR2
1516 ) IS
1517
1518 CURSOR debit_transactions(p_currency_code VARCHAR2) IS
1519 SELECT CUSTOMER_ID,
1520 CUSTOMER_SITE_USE_ID,
1521 CUSTOMER_TRX_ID,
1522 TRX_NUMBER,
1523 TRX_DATE,
1524 TRX_CLASS,
1525 DUE_DATE,
1526 PAYMENT_SCHEDULE_ID,
1527 STATUS,
1528 TERMS_SEQUENCE_NUMBER,
1529 LINE_AMOUNT,
1530 TAX_AMOUNT ,
1531 FREIGHT_AMOUNT,
1532 FINANCE_CHARGES,
1533 CURRENCY_CODE ,
1534 AMOUNT_DUE_ORIGINAL,
1535 AMOUNT_DUE_REMAINING,
1536 SERVICE_CHARGE,
1537 DISCOUNT_AMOUNT,
1538 APPLICATION_AMOUNT,
1539 CASH_RECEIPT_ID
1540 FROM ar_irec_apply_credit_gt
1541 WHERE CURRENCY_CODE = p_currency_code
1542 AND ( TRX_CLASS = 'INV' OR
1543 TRX_CLASS = 'DM' OR
1544 TRX_CLASS = 'CB' OR
1545 TRX_CLASS = 'DEP'
1546 )
1547 ORDER BY AMOUNT_DUE_REMAINING ASC;
1548
1549 CURSOR credit_transactions(p_currency_code VARCHAR2) IS
1550 SELECT CUSTOMER_ID,
1551 CUSTOMER_SITE_USE_ID,
1552 CUSTOMER_TRX_ID,
1553 TRX_NUMBER,
1554 TRX_DATE,
1555 TRX_CLASS,
1556 DUE_DATE,
1557 PAYMENT_SCHEDULE_ID,
1558 STATUS,
1559 TERMS_SEQUENCE_NUMBER,
1560 LINE_AMOUNT,
1561 TAX_AMOUNT ,
1562 FREIGHT_AMOUNT,
1563 FINANCE_CHARGES,
1564 CURRENCY_CODE ,
1565 AMOUNT_DUE_ORIGINAL,
1566 AMOUNT_DUE_REMAINING,
1567 SERVICE_CHARGE,
1568 DISCOUNT_AMOUNT,
1569 APPLICATION_AMOUNT,
1570 CASH_RECEIPT_ID
1571 FROM ar_irec_apply_credit_gt
1572 WHERE CURRENCY_CODE = p_currency_code
1573 AND ( TRX_CLASS = 'CM' OR
1574 TRX_CLASS = 'PMT'
1575 )
1576 ORDER BY PAYMENT_SCHEDULE_ID;
1577
1578 debit_trx_record debit_transactions%ROWTYPE;
1579 credit_trx_record credit_transactions%ROWTYPE;
1580
1581 l_next_transaction VARCHAR2(10);
1582 l_debit_application_amount NUMBER;
1583 l_credit_application_amount NUMBER;
1584 l_credit_trx_class VARCHAR2(20);
1585
1586 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1587 l_acctd_amount_applied_from ar_receivable_applications.acctd_amount_applied_from%TYPE;
1588 l_acctd_amount_applied_to ar_receivable_applications.acctd_amount_applied_to%TYPE;
1589
1590 l_return_status VARCHAR2(10);
1591 l_msg_count NUMBER;
1592 l_msg_data VARCHAR2(255);
1593
1594 l_defaulting_rule_used VARCHAR2(255);
1595 l_error_message VARCHAR2(255);
1596
1597 l_application_amount NUMBER;
1598
1599 l_open_gl_date DATE;
1600 l_gl_date DATE;
1601 l_inv_date DATE;
1602 l_receipt_date DATE;
1603
1604 l_found VARCHAR2(1);
1605
1606 l_procedure_name VARCHAR2(50);
1607 l_debug_info VARCHAR2(200);
1608
1609 BEGIN
1610 x_msg_count := 0;
1611 x_msg_data := '*';
1612 x_open_invoices_status := 'N';
1613 x_return_status := FND_API.G_RET_STS_ERROR;
1614
1615 l_procedure_name := '.apply_credits_on_credit_memo';
1616 l_debit_application_amount := 0;
1617 l_credit_application_amount:= 0;
1618 l_next_transaction := 'BOTH';
1619 l_gl_date := sysdate;
1620
1621 SAVEPOINT ARI_APPLY_CREDITS_CM;
1622
1623
1624 ---------------------------------------------------------------------------
1625 l_debug_info := 'Open the credit and debit cursors';
1626 ---------------------------------------------------------------------------
1627 IF (PG_DEBUG = 'Y') THEN
1628 arp_standard.debug(l_debug_info);
1629 END IF;
1630 OPEN debit_transactions(p_currency_code);
1631 OPEN credit_transactions(p_currency_code);
1632
1633 LOOP
1634 ---------------------------------------------------------------------------
1635 l_debug_info := 'Fetch debit and credit transactions';
1636 ---------------------------------------------------------------------------
1637 IF (PG_DEBUG = 'Y') THEN
1638 arp_standard.debug(l_debug_info);
1639 END IF;
1640
1641 IF (l_next_transaction = 'DEBIT') THEN
1642 FETCH debit_transactions INTO debit_trx_record;
1643 ELSIF (l_next_transaction = 'CREDIT') THEN
1644 FETCH credit_transactions INTO credit_trx_record;
1645 ELSIF (l_next_transaction = 'BOTH') THEN
1646 FETCH debit_transactions INTO debit_trx_record;
1647 FETCH credit_transactions INTO credit_trx_record;
1648 END IF;
1649
1650 EXIT WHEN ((debit_transactions%NOTFOUND) AND (credit_transactions%NOTFOUND));
1651
1652 --Bug 4105891 - Check if there will be a non-zero remaining amount on the invoice after application
1653 IF (l_next_transaction = 'BOTH' OR l_next_transaction = 'DEBIT') THEN
1654 IF (debit_trx_record.amount_due_remaining
1655 - ( debit_trx_record.discount_amount + debit_trx_record.application_amount) > 0) THEN
1656 x_open_invoices_status := 'Y';
1657 END IF;
1658 END IF;
1659
1660 l_debit_application_amount := debit_trx_record.application_amount;
1661 l_credit_application_amount := - credit_trx_record.application_amount;
1662 l_credit_trx_class := credit_trx_record.trx_class;
1663
1664 -- Compare the application amount for the credit and debit transactions,
1665 -- to decide what should be the next transaction to be considered for application.
1666 IF (l_debit_application_amount > l_credit_application_amount) THEN
1667
1668 l_next_transaction := 'CREDIT';
1669 l_application_amount := l_credit_application_amount;
1670 debit_trx_record.application_amount := l_debit_application_amount - l_credit_application_amount;
1671
1672 ELSIF (l_debit_application_amount < l_credit_application_amount) THEN
1673
1674 l_next_transaction := 'DEBIT';
1675 l_application_amount := l_debit_application_amount;
1676 --Bug 4042557 - Change sign in credit application amount
1677 credit_trx_record.application_amount := -(l_credit_application_amount - l_debit_application_amount);
1678
1679 ELSE
1680
1681 l_next_transaction := 'BOTH';
1682 l_application_amount := l_debit_application_amount;
1683
1684 END IF;
1685
1686 IF (PG_DEBUG = 'Y') THEN
1687 arp_standard.debug('-------Debit Transaction Information------');
1688 arp_standard.debug('Customer Id: ' || debit_trx_record.customer_id);
1689 arp_standard.debug('Customer Site Use Id: ' || debit_trx_record.customer_site_use_id);
1690 arp_standard.debug('Customer Trx Id: ' || debit_trx_record.customer_trx_id);
1691 arp_standard.debug('Trx Number: ' || debit_trx_record.trx_number);
1692 arp_standard.debug('Trx Date: ' || debit_trx_record.trx_date);
1693 arp_standard.debug('Trx Class: ' || debit_trx_record.trx_class);
1694 arp_standard.debug('Due Date: ' || debit_trx_record.due_date);
1695 arp_standard.debug('Payment Schedule Id: ' || debit_trx_record.payment_schedule_id);
1696 arp_standard.debug('Status: ' || debit_trx_record.status);
1697 arp_standard.debug('Terms Sequence Number: ' || debit_trx_record.terms_sequence_number);
1698 arp_standard.debug('Line Amount: ' || debit_trx_record.line_amount);
1699 arp_standard.debug('Tax Amount: ' || debit_trx_record.tax_amount);
1700 arp_standard.debug('Freight Amount: ' || debit_trx_record.freight_amount);
1701 arp_standard.debug('Finance Charges: ' || debit_trx_record.trx_class);
1702 arp_standard.debug('Currency Code: ' || debit_trx_record.currency_code);
1703 arp_standard.debug('Amount Due Original: ' || debit_trx_record.amount_due_original);
1704 arp_standard.debug('Amount Due Remaining: ' || debit_trx_record.amount_due_remaining);
1705 arp_standard.debug('Discount Amount: ' || debit_trx_record.discount_amount);
1706 arp_standard.debug('Application Amount: ' || debit_trx_record.application_amount);
1707 arp_standard.debug('Cash Receipt Id: ' || debit_trx_record.cash_receipt_id);
1708 arp_standard.debug('-------Credit Transaction Information------');
1709 arp_standard.debug('Customer Id: ' || credit_trx_record.customer_id);
1710 arp_standard.debug('Customer Site Use Id: ' || credit_trx_record.customer_site_use_id);
1711 arp_standard.debug('Customer Trx Id: ' || credit_trx_record.customer_trx_id);
1712 arp_standard.debug('Trx Number: ' || credit_trx_record.trx_number);
1713 arp_standard.debug('Trx Date: ' || credit_trx_record.trx_date);
1714 arp_standard.debug('Trx Class: ' || credit_trx_record.trx_class);
1715 arp_standard.debug('Due Date: ' || credit_trx_record.due_date);
1716 arp_standard.debug('Payment Schedule Id: ' || credit_trx_record.payment_schedule_id);
1717 arp_standard.debug('Status: ' || credit_trx_record.status);
1718 arp_standard.debug('Terms Sequence Number: ' || credit_trx_record.terms_sequence_number);
1719 arp_standard.debug('Line Amount: ' || credit_trx_record.line_amount);
1720 arp_standard.debug('Tax Amount: ' || credit_trx_record.tax_amount);
1721 arp_standard.debug('Freight Amount: ' || credit_trx_record.freight_amount);
1722 arp_standard.debug('Finance Charges: ' || credit_trx_record.trx_class);
1723 arp_standard.debug('Currency Code: ' || credit_trx_record.currency_code);
1724 arp_standard.debug('Amount Due Original: ' || credit_trx_record.amount_due_original);
1725 arp_standard.debug('Amount Due Remaining: ' || credit_trx_record.amount_due_remaining);
1726 arp_standard.debug('Discount Amount: ' || credit_trx_record.discount_amount);
1727 arp_standard.debug('Application Amount: ' || credit_trx_record.application_amount);
1728 arp_standard.debug('Cash Receipt Id: ' || credit_trx_record.cash_receipt_id);
1729 END IF;
1730
1731 -- Bug 4103527 - Display error icons for duplicate application
1732 BEGIN
1733
1734 select 'Y'
1735 into l_found
1736 from ar_receivable_applications rap
1737 where rap.payment_schedule_id = credit_trx_record.payment_schedule_id
1738 and rap.applied_payment_schedule_id = debit_trx_record.payment_schedule_id
1739 and rap.display = 'Y'
1740 and rap.status = 'APP';
1741
1742 IF l_found = 'Y' THEN
1743 x_dup_appln_dbt_psid := debit_trx_record.payment_schedule_id;
1744 x_dup_appln_crdt_psid := credit_trx_record.payment_schedule_id;
1745
1746 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1747
1748 FND_MESSAGE.SET_NAME('AR', 'AR_RW_PAID_INVOICE_TWICE' );
1749 FND_MSG_PUB.ADD;
1750
1751 AR_IREC_PAYMENTS.write_error_messages(x_msg_data,x_msg_count);
1752 RETURN;
1753 END IF;
1754 EXCEPTION
1755 WHEN NO_DATA_FOUND THEN
1756 null;
1757 END;
1758
1759 IF ( l_credit_trx_class = 'CM') THEN
1760 ---------------------------------------------------------------------------
1761 l_debug_info := 'Apply credit memo against the debit transaction';
1762 ---------------------------------------------------------------------------
1763 IF (PG_DEBUG = 'Y') THEN
1764 arp_standard.debug(l_debug_info);
1765 END IF;
1766
1767 --Bug 4042557: Fetch the GL date for the credit memo
1768 select trunc(gl_date), trunc(trx_date)
1769 into l_gl_date, l_receipt_date
1770 from ar_payment_schedules
1771 where payment_schedule_id = credit_trx_record.payment_schedule_id;
1772
1776 where payment_schedule_id = debit_trx_record.payment_schedule_id;
1773 select trunc(gl_date)
1774 into l_inv_date
1775 from ar_payment_schedules
1777
1778 --Bug 5911297 - Using apply credit applied GL date profile and pass appropriate gl date
1779 IF(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT') = 'INV_REC_SYS_DT') THEN
1780 l_gl_date := Greatest(l_inv_date, l_receipt_date, trunc(sysdate));
1781 ELSIF(FND_PROFILE.VALUE('AR_APPLICATION_GL_DATE_DEFAULT') = 'INV_REC_DT') THEN
1782 l_gl_date := Greatest(l_inv_date, l_receipt_date, l_gl_date);
1783 END IF;
1784 --Bug 6062210: Fetch the open GL date for the credit memo
1785 if(arp_util.validate_and_default_gl_date( gl_date => l_gl_date,
1786 trx_date => l_receipt_date,
1787 validation_date1 => null,
1788 validation_date2 => null,
1789 validation_date3 => null,
1790 default_date1 => null,
1791 default_date2 => null,
1792 default_date3 => null,
1793 p_allow_not_open_flag => null,
1794 p_invoicing_rule_id => null,
1795 p_set_of_books_id => null,
1796 p_application_id => 222,
1797 default_gl_date => l_open_gl_date,
1798 defaulting_rule_used => l_defaulting_rule_used,
1799 error_message => l_error_message)) then
1800
1801 l_gl_date := l_open_gl_date;
1802
1803 end if;
1804
1805
1806 arp_process_application.cm_application(
1807 p_cm_ps_id => credit_trx_record.payment_schedule_id,
1808 p_invoice_ps_id => debit_trx_record.payment_schedule_id,
1809 p_amount_applied => l_application_amount,
1810 p_apply_date => trunc(sysdate),
1811 p_gl_date => l_gl_date,
1812 p_ussgl_transaction_code => null,
1813 p_attribute_category => null,
1814 p_attribute1 => null,
1815 p_attribute2 => null,
1816 p_attribute3 => null,
1817 p_attribute4 => null,
1818 p_attribute5 => null,
1819 p_attribute6 => null,
1820 p_attribute7 => null,
1821 p_attribute8 => null,
1822 p_attribute9 => null,
1823 p_attribute10 => null,
1824 p_attribute11 => null,
1825 p_attribute12 => null,
1826 p_attribute13 => null,
1827 p_attribute14 => null,
1828 p_attribute15 => null,
1829 p_global_attribute_category => null,
1830 p_global_attribute1 => null,
1831 p_global_attribute2 => null,
1832 p_global_attribute3 => null,
1833 p_global_attribute4 => null,
1834 p_global_attribute5 => null,
1835 p_global_attribute6 => null,
1836 p_global_attribute7 => null,
1837 p_global_attribute8 => null,
1838 p_global_attribute9 => null,
1839 p_global_attribute10 => null,
1840 p_global_attribute11 => null,
1841 p_global_attribute12 => null,
1842 p_global_attribute13 => null,
1843 p_global_attribute14 => null,
1844 p_global_attribute15 => null,
1845 p_global_attribute16 => null,
1846 p_global_attribute17 => null,
1847 p_global_attribute18 => null,
1848 p_global_attribute19 => null,
1849 p_global_attribute20 => null,
1850 p_customer_trx_line_id => null,
1851 p_comments => null,
1852 p_module_name => null,
1853 p_module_version => null,
1854 p_out_rec_application_id => l_receivable_application_id,
1855 p_acctd_amount_applied_from =>l_acctd_amount_applied_from,
1856 p_acctd_amount_applied_to=>l_acctd_amount_applied_to);
1857
1858 IF (l_receivable_application_id IS NULL) THEN
1859 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1860 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1861 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1862 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1863 FND_MSG_PUB.ADD;
1864
1865 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1866 RETURN;
1867 END IF;
1868
1869 ELSIF ( l_credit_trx_class = 'PMT') THEN
1870 ---------------------------------------------------------------------------
1871 l_debug_info := 'Apply receipt against the debit transaction';
1872 ---------------------------------------------------------------------------
1873 IF (PG_DEBUG = 'Y') THEN
1874 arp_standard.debug(l_debug_info);
1875 END IF;
1876 AR_RECEIPT_API_PUB.apply(
1877 p_api_version => 1.0,
1878 p_init_msg_list => FND_API.G_TRUE,
1879 p_commit => FND_API.G_FALSE,
1883 x_msg_data => l_msg_data,
1880 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1881 x_return_status => l_return_status,
1882 x_msg_count => l_msg_count,
1884 p_cash_receipt_id => credit_trx_record.cash_receipt_id,
1885 p_customer_trx_id => debit_trx_record.customer_trx_id,
1886 p_applied_payment_schedule_id => debit_trx_record.payment_schedule_id,
1887 p_amount_applied => l_application_amount,
1888 p_discount => 0,
1889 p_called_from => 'IREC',
1890 p_apply_date => trunc(sysdate)
1891 );
1892
1893 -- Check for error
1894 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1895 x_msg_count := l_msg_count;
1896 x_msg_data := l_msg_data;
1897 ROLLBACK TO ARI_APPLY_CREDITS_CM;
1898 RETURN;
1899 END IF;
1900
1901 END IF;
1902
1903 END LOOP;
1904
1905 ---------------------------------------------------------------------------
1906 l_debug_info := 'Close the credit and debit cursors';
1907 ---------------------------------------------------------------------------
1908 IF (PG_DEBUG = 'Y') THEN
1909 arp_standard.debug(l_debug_info);
1910 END IF;
1911 CLOSE credit_transactions;
1912 CLOSE debit_transactions;
1913
1914 COMMIT;
1915
1916 x_return_status := FND_API.G_RET_STS_SUCCESS;
1917
1918 EXCEPTION
1919 WHEN OTHERS THEN
1920 IF (PG_DEBUG = 'Y') THEN
1921 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
1922 arp_standard.debug('- Currency Code: '||p_currency_code);
1923 arp_standard.debug('ERROR =>'|| SQLERRM);
1924 END IF;
1925
1926 IF(PG_DIAGNOSTICS = 'Y') THEN
1927 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
1928 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
1929 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1930 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
1931 FND_MSG_PUB.ADD;
1932 ELSE
1933 x_msg_data := x_msg_data||SQLERRM;
1934 END IF;
1935
1936 END apply_credits_on_credit_memo;
1937
1938 /*============================================================
1939 | PUBLIC procedure copy_apply_credits_records
1940 |
1941 | DESCRIPTION
1942 | Copy the open debits for the active customer, site and currency from the
1943 | Apply Credits GT to the Transaction List GT
1944 |
1945 | PSEUDO CODE/LOGIC
1946 |
1947 | PARAMETERS
1948 | p_customer_id IN NUMBER
1949 | p_customer_site_use_id IN NUMBER DEFAULT NULL
1950 | p_currency_code IN VARCHAR2
1951 |
1952 | KNOWN ISSUES
1953 |
1954 |
1955 |
1956 | NOTES
1957 |
1958 |
1959 |
1960 | MODIFICATION HISTORY
1961 | Date Author Description of Changes
1962 | 12-OCT-2004 vnb Created
1963 +============================================================*/
1964
1965 PROCEDURE copy_apply_credits_records( p_customer_id IN NUMBER,
1966 p_customer_site_use_id IN NUMBER DEFAULT NULL,
1967 p_currency_code IN VARCHAR2
1968 ) IS
1969
1970 CURSOR open_debits_list (p_customer_id NUMBER,
1971 p_customer_site_use_id NUMBER,
1972 p_currency_code VARCHAR2) IS
1973
1974 SELECT
1975 CUSTOMER_ID,
1976 CUSTOMER_SITE_USE_ID,
1977 TRX_CLASS,
1978 PAYMENT_SCHEDULE_ID,
1979 CURRENCY_CODE
1980 FROM ar_irec_apply_credit_gt
1981 WHERE PAY_FOR_CUSTOMER_ID = p_customer_id
1982 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))
1983 AND CURRENCY_CODE = p_currency_code
1984 AND ( TRX_CLASS = 'INV' OR
1985 TRX_CLASS = 'DM' OR
1986 TRX_CLASS = 'CB' OR
1987 TRX_CLASS = 'DEP'
1988 )
1989 AND (AMOUNT_DUE_REMAINING - (nvl(DISCOUNT_AMOUNT,0) + APPLICATION_AMOUNT) > 0);
1990
1991 l_procedure_name VARCHAR2(50);
1992 l_debug_info VARCHAR2(200);
1993
1994 BEGIN
1995
1996 l_procedure_name := '.copy_apply_credits_records';
1997
1998
1999 ----------------------------------------------------------------------------------------
2000 l_debug_info := 'Clear the Transaction List GT for the active customer, site, currency code';
2001 -----------------------------------------------------------------------------------------
2002 IF (PG_DEBUG = 'Y') THEN
2003 arp_standard.debug(l_debug_info);
2004 END IF;
2005
2006 DELETE FROM AR_IREC_PAYMENT_LIST_GT
2007 WHERE CUSTOMER_ID = p_customer_id
2008 AND CUSTOMER_SITE_USE_ID = nvl(p_customer_site_use_id,CUSTOMER_SITE_USE_ID)
2009 AND CURRENCY_CODE = p_currency_code;
2010
2011 ----------------------------------------------------------------------------------------
2012 l_debug_info := 'Fetch open debits from Apply Credits GT into Transaction List GT';
2013 -----------------------------------------------------------------------------------------
2014 IF (PG_DEBUG = 'Y') THEN
2018 FOR trx IN open_debits_list(p_customer_id,
2015 arp_standard.debug(l_debug_info);
2016 END IF;
2017
2019 p_customer_site_use_id,
2020 p_currency_code )
2021 LOOP
2022 AR_IREC_PAYMENTS.create_transaction_list_record(
2023 p_payment_schedule_id => trx.payment_schedule_id,
2024 p_customer_id => p_customer_id,
2025 p_customer_site_id => p_customer_site_use_id
2026 );
2027
2028 END LOOP;
2029
2030 COMMIT;
2031
2032 EXCEPTION
2033 WHEN OTHERS THEN
2034 IF (PG_DEBUG = 'Y') THEN
2035 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2036 arp_standard.debug('- Customer Id: '||p_customer_id);
2037 arp_standard.debug('- Customer Site Use Id: '||p_customer_site_use_id);
2038 arp_standard.debug('- Currency Code: '||p_currency_code);
2039 arp_standard.debug('ERROR =>'|| SQLERRM);
2040 END IF;
2041
2042 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2043 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2044 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2045 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2046 FND_MSG_PUB.ADD;
2047
2048 END copy_apply_credits_records;
2049
2050 /*============================================================
2051 | PUBLIC procedure select_credit_to_apply
2052 |
2053 | DESCRIPTION
2054 | Select credit to apply other transactions against
2055 |
2056 | PSEUDO CODE/LOGIC
2057 |
2058 | PARAMETERS
2059 | p_customer_id IN NUMBER
2060 | p_customer_site_use_id IN NUMBER DEFAULT NULL
2061 | p_currency_code IN VARCHAR2
2062 | x_return_status OUT VARCHAR2 Returns 'S' if successful; 'E' if duplicate application
2063 | x_credit_ps_id OUT NUMBER
2064 | x_debit_ps_id OUT NUMBER Returns debit payment schedule id if duplicate application; else null
2065 |
2066 | KNOWN ISSUES
2067 |
2068 | NOTES
2069 |
2070 |
2071 | MODIFICATION HISTORY
2072 | Date Author Description of Changes
2073 | 02-FEB-2005 vnb Created
2074 | 07-SEP-2005 7 rrsaneve Updated the cursors credit_to_apply ,duplicate_application as bug#6311033 fix.
2075 +============================================================*/
2076 PROCEDURE select_credit_to_apply( p_currency_code IN VARCHAR2,
2077 x_return_status OUT NOCOPY VARCHAR2,
2078 x_credit_ps_id OUT NOCOPY NUMBER,
2079 x_debit_ps_id OUT NOCOPY NUMBER)
2080 IS
2081 CURSOR credit_to_apply (p_currency_code VARCHAR2) IS
2082 select acgt.payment_schedule_id
2083 from ar_irec_apply_credit_gt acgt
2084 where acgt.currency_code = p_currency_code
2085 and acgt.trx_class = 'PMT'
2086
2087 or ( (
2088 select sum(amount_applied)
2089 from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
2090 where acgt1.currency_code = p_currency_code
2091 and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
2092 and arp.payment_schedule_id = acgt.payment_schedule_id) = 0
2093 )
2094
2095 order by acgt.trx_date asc;
2096
2097
2098 /* bug#6311033-APPLY CREDIT FAILS WITH YOU HAVE PAID SAME INVOICE TWICE ERROR */
2099
2100 CURSOR duplicate_application(p_currency_code VARCHAR2) IS
2101 select acgt.payment_schedule_id, arp.applied_payment_schedule_id
2102 from ar_irec_apply_credit_gt acgt,ar_receivable_applications_all arp,ar_irec_apply_credit_gt acgt1
2103 where acgt.currency_code = p_currency_code
2104 and acgt.trx_class = 'PMT'
2105 and acgt.payment_schedule_id = arp.payment_schedule_id
2106 and acgt1.currency_code = p_currency_code
2107 and acgt1.payment_schedule_id = arp.applied_payment_schedule_id
2108 and (
2109 select sum(amount_applied)
2110 from ar_receivable_applications arp, ar_irec_apply_credit_gt acgt1
2111 where acgt1.currency_code = p_currency_code
2112 and arp.applied_payment_schedule_id = acgt1.payment_schedule_id
2113 and arp.payment_schedule_id = acgt.payment_schedule_id) > 0;
2114
2115
2116 l_procedure_name VARCHAR2(50);
2117 l_debug_info VARCHAR2(200);
2118
2119 credit_to_apply_record credit_to_apply%ROWTYPE;
2120 duplicate_application_record duplicate_application%ROWTYPE;
2121
2122 BEGIN
2123 -- Setting the return status to Success for Bug# 13520421
2124 x_return_status := FND_API.G_RET_STS_SUCCESS;
2125 x_credit_ps_id := 0;
2126 x_debit_ps_id := 0;
2127
2128 l_procedure_name := '.select_credit_to_apply';
2129
2130 ---------------------------------------------------------------------------
2131 l_debug_info := 'Open the cursor to select credit to apply';
2132 ---------------------------------------------------------------------------
2133 IF (PG_DEBUG = 'Y') THEN
2134 arp_standard.debug(l_debug_info);
2135 END IF;
2136
2137 OPEN credit_to_apply(p_currency_code);
2138 FETCH credit_to_apply INTO x_credit_ps_id;
2139
2140
2141
2142 ---------------------------------------------------------------------------
2143 l_debug_info := 'Open the cursor to return duplicate application transactions';
2144 ---------------------------------------------------------------------------
2145 IF (PG_DEBUG = 'Y') THEN
2146 arp_standard.debug(l_debug_info);
2147 END IF;
2148 OPEN duplicate_application(p_currency_code);
2149 FETCH duplicate_application INTO duplicate_application_record;
2150
2151 IF (duplicate_application%FOUND) THEN
2152
2153 ---------------------------------------------------------------------------
2154 l_debug_info := 'If a record is found, return the credit and debit transaction';
2155 ---------------------------------------------------------------------------
2156 IF (PG_DEBUG = 'Y') THEN
2157 arp_standard.debug(l_debug_info);
2158 END IF;
2159
2160 x_credit_ps_id := duplicate_application_record.payment_schedule_id;
2161 x_debit_ps_id := duplicate_application_record.applied_payment_schedule_id;
2162 x_return_status := FND_API.G_RET_STS_ERROR;
2163
2164 END IF;
2165 CLOSE duplicate_application;
2166
2167
2168 CLOSE credit_to_apply;
2169
2170 EXCEPTION
2171 WHEN OTHERS THEN
2172
2173 IF (PG_DEBUG = 'Y') THEN
2174 arp_standard.debug('Unexpected Exception in ' || G_PKG_NAME || l_procedure_name);
2175 arp_standard.debug('- Currency Code: '||p_currency_code);
2176 arp_standard.debug('ERROR =>'|| SQLERRM);
2177 END IF;
2178
2179 FND_MESSAGE.SET_NAME ('AR','ARI_REG_DISPLAY_UNEXP_ERROR');
2180 FND_MESSAGE.SET_TOKEN('PROCEDURE', G_PKG_NAME || l_procedure_name);
2181 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2182 FND_MESSAGE.SET_TOKEN('DEBUG_INFO', l_debug_info);
2183 FND_MSG_PUB.ADD;
2184 END select_credit_to_apply;
2185
2186 END AR_IREC_APPLY_CREDITS ;
2187