[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_RETURNS
Source
1 PACKAGE BODY ARP_PROCESS_RETURNS AS
2 /* $Header: ARPRRTNB.pls 120.11.12010000.2 2008/08/25 18:57:35 mpsingh ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARP_PROCESS_RETURNS';
8
9 /*=======================================================================+
10 | Package Global Constants
11 +=======================================================================*/
12 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13 g_ccr_receivables_trx_id NUMBER(15);
14 g_batch_source_id ra_batch_sources.batch_source_id%type;
15 g_receipt_handling_option ra_batch_sources.receipt_handling_option%type;
16 g_nccr_receivables_trx_id NUMBER(15);
17
18 /*========================================================================
19 | Prototype Declarations Procedures
20 *=======================================================================*/
21 --
22 PROCEDURE check_rec_in_doubt(p_cash_receipt_id IN NUMBER,
23 x_rec_in_doubt OUT NOCOPY VARCHAR2,
24 x_rid_reason OUT NOCOPY VARCHAR2,
25 x_rec_proc_option IN VARCHAR2);
26 --
27 --
28 PROCEDURE get_receipt_amounts (p_cash_receipt_id IN NUMBER,
29 x_receipt_amount OUT NOCOPY NUMBER,
30 x_refund_amount OUT NOCOPY NUMBER,
31 x_rec_proc_option IN VARCHAR2);
32 --
33 PROCEDURE add_ra_to_list(p_ra_info IN app_info_type,
34 p_ra_rec IN ar_receivable_applications%rowtype);
35 --
36 PROCEDURE populate_dff_and_gdf(p_ra_rec IN ar_receivable_applications%rowtype,
37 x_dff_rec OUT NOCOPY
38 ar_receipt_api_pub.attribute_rec_type,
39 x_gdf_rec OUT NOCOPY
40 ar_receipt_api_pub.global_attribute_rec_type);
41 --
42 PROCEDURE initialize_globals IS
43 BEGIN
44 BEGIN
45 SELECT receivables_trx_id
46 INTO g_ccr_receivables_trx_id
47 FROM ar_receivables_trx
48 WHERE type = 'CCREFUND'
49 AND status = 'A';
50 EXCEPTION
51 WHEN NO_DATA_FOUND THEN
52 NULL;
53 WHEN OTHERS THEN
54 RAISE;
55 END;
56 BEGIN
57 SELECT receivables_trx_id
58 INTO g_nccr_receivables_trx_id
59 FROM ar_receivables_trx
60 WHERE type = 'CM_REFUND'
61 AND status = 'A';
62 EXCEPTION
63 WHEN NO_DATA_FOUND THEN
64 NULL;
65 WHEN OTHERS THEN
66 RAISE;
67 END;
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 RAISE;
72 END initialize_globals;
73
74 /*========================================================================
75 | Procedure process_invoice_list()
76 |
77 | DESCRIPTION
78 | Process Invoices from the list prepared by the AutoInvoice
79 |
80 | PSEUDO CODE/LOGIC
81 |
82 | PARAMETERS
83 |
84 |
85 | RETURNS
86 | nothing
87 |
88 | KNOWN ISSUES
89 |
90 |
91 |
92 | NOTES
93 |
94 |
95 |
96 | MODIFICATION HISTORY
97 | Date Author Description of Changes
98 | 02-Jul-2003 Ramakant Alat Created
99 |
100 *=======================================================================*/
101 PROCEDURE process_invoice_list AS
102
103 -- Get info for given Invoice
104 CURSOR c01 (p_customer_trx_id NUMBER) IS
105 SELECT
106 inv.customer_trx_id inv_customer_trx_id,
107 inv.invoice_currency_code,
108 inv.exchange_rate,
109 cmbs.receipt_handling_option,
110 COUNT(DISTINCT invps.payment_schedule_id) ps_count,
111 get_total_cm_amount(inv.customer_trx_id, cm.request_id) cm_amount,
112 get_total_payment_types(inv.customer_trx_id) total_pmt_types,
113 SUM(invps.amount_due_remaining)/
114 COUNT(DISTINCT NVL(adj.adjustment_id, -9.9)) inv_balance,
115 (SUM(NVL(invps.amount_applied, 0))+
116 SUM(NVL(invps.discount_taken_earned, 0)))/
117 COUNT(DISTINCT NVL(adj.adjustment_id, -9.9)) inv_app_amount,
118 NVL(SUM(DECODE(adj.adjustment_type, 'C', adj.amount, 0)), 0) /
119 COUNT(DISTINCT invps.payment_schedule_id) cmt_adj_amount,
120 NVL(SUM(DECODE(adj.adjustment_type, 'C', 0, adj.amount)), 0) /
121 COUNT(DISTINCT invps.payment_schedule_id) adj_amount
122 FROM
123 ra_customer_trx inv,
124 ar_payment_schedules invps,
125 ra_cust_trx_types itt,
126 ra_batch_sources cmbs,
127 ra_customer_trx cm,
128 ar_adjustments adj
129 WHERE
130 inv.customer_trx_id = cm.previous_customer_trx_id
131 AND inv.customer_trx_id = p_customer_trx_id
132 AND inv.customer_trx_id = invps.customer_trx_id
133 AND cm.batch_source_id = cmbs.batch_source_id
134 AND cm.request_id = arp_global.request_id
135 AND inv.cust_trx_type_id = itt.cust_trx_type_id
136 AND cmbs.receipt_handling_option IS NOT NULL
137 AND itt.allow_overapplication_flag = 'N'
138 AND inv.customer_trx_id = adj.customer_trx_id (+)
139 GROUP BY
140 cmbs.receipt_handling_option,
141 cm.request_id,
142 inv.invoice_currency_code,
143 inv.exchange_rate,
144 inv.customer_trx_id;
145 /***
146 HAVING
147 (SUM(invps.amount_due_original)/
148 COUNT(DISTINCT NVL(adj.adjustment_id, -9.9))) > 0 ;
149 ***/
150
151 adj_exception EXCEPTION;
152 overapp_exception EXCEPTION;
153 l_total_unapp_amount NUMBER;
154 l_total_unapp_acctd_amount NUMBER;
155 l_rec_in_doubt VARCHAR2(1):='N';
156 l_rid_reason VARCHAR2(2000):= null;
157 l_mult_pmt_types_msg VARCHAR2(2000):=
158 arp_standard.fnd_message('AR_RID_MULTIPLE_PMT_TYPES');
159 l_min_ref_amt_msg VARCHAR2(2000):=
160 arp_standard.fnd_message('AR_RID_OAPP_LT_MIN_REF_AMT');
161 l_split_term_with_bal_msg VARCHAR2(2000):=
162 arp_standard.fnd_message('AR_RID_SPLIT_TERM_WITH_BAL');
163 l_amt_lt_min_ref_amt_msg VARCHAR2(2000):=
164 arp_standard.fnd_message('AR_RID_OAPP_LT_MIN_REF_AMT');
165 i NUMBER(15):= 0;
166
167 BEGIN
168 --
169 IF PG_DEBUG in ('Y', 'C') THEN
170 arp_standard.debug('arp_process_returns.process_invoice_list()+ ');
171 END IF;
172 --
173 -- Check if there are any Invoices to process in the list
174 --
175 IF inv_info.COUNT = 0 THEN
176 IF PG_DEBUG in ('Y', 'C') THEN
177 arp_standard.debug('No Invoice in the list to process..');
178 END IF;
179 GOTO after_loop;
180 END IF;
181 --
182 -- Process all Invoices added to the PL/SQL table by AutoInvoice
183 --
184 i := inv_info.FIRST; -- get subscript of first element
185 --
186 WHILE i IS NOT NULL
187 LOOP
188 --
189 IF PG_DEBUG in ('Y', 'C') THEN
190 arp_standard.debug('INV Customer Trx ID [' || i || ']');
191 END IF;
192 --
193 FOR c01_rec IN c01 (i) LOOP
194 --
195 IF PG_DEBUG in ('Y', 'C') THEN
196 arp_standard.debug('CM count [' || inv_info(i).num_of_cms || ']');
197 arp_standard.debug('Inv Balance [' || c01_rec.inv_balance || ']');
198 arp_standard.debug('PS count [' || c01_rec.ps_count || ']');
199 arp_standard.debug('Inv App Amount [' ||
200 c01_rec.inv_app_amount || ']');
201 arp_standard.debug('Commitment Adj amt [' ||
202 c01_rec.cmt_adj_amount || ']');
203 arp_standard.debug('Adj amt [' || c01_rec.adj_amount || ']');
204 arp_standard.debug('CM amt [' || c01_rec.cm_amount || ']');
205 END IF;
206 --
207 -- If adjustment exists then raise exception ***/
208 --
209 IF c01_rec.adj_amount <> 0 THEN
210 arp_standard.debug('arp_process_returns.process_invoice_list : ' ||
211 'adj_EXCEPTION customer_trx_id <' || c01_rec.inv_customer_trx_id ||
212 '>');
213 RAISE adj_exception;
214 END IF;
215 --
216 -- Calculate Total amount which needs to be unapplied from receipts
217 --
218 l_total_unapp_amount := -1 * (c01_rec.inv_balance
219 - c01_rec.cmt_adj_amount
220 + c01_rec.cm_amount);
221 --
222 -- No overapplication, so no unapplication required
223 --
224 IF c01_rec.cm_amount = 0 THEN
225 GOTO end_loop;
226 END IF;
227 --
228 -- If Total Unapp amount > Applied amount then raise exception
229 --
230 IF l_total_unapp_amount > (c01_rec.inv_app_amount) THEN
231 arp_standard.debug('arp_process_returns.process_invoice_list : ' ||
232 'overapp_EXCEPTION customer_trx_id <'
233 || c01_rec.inv_customer_trx_id || '>');
234 arp_standard.debug('Inv Balance : <' || c01_rec.inv_balance);
235 arp_standard.debug('Inv App Amount : <' || c01_rec.inv_app_amount);
236 arp_standard.debug('Cmt Adj Amount : <' || c01_rec.cmt_adj_amount);
237 arp_standard.debug('CM Amount : <' || c01_rec.cm_amount);
238 RAISE overapp_exception;
239 END IF;
240
241 --
242 -- Check if invoice has CC payment then check for receipt
243 -- in doubt scenarios
244 --
245 IF c01_rec.total_pmt_types = 0 THEN
246 --
247 inv_info(i).cc_apps := FALSE; -- No CC Applications
248 inv_info(i).all_recs_in_doubt := FALSE; -- No receipts in doubt
249 inv_info(i).rid_reason := null;
250 --
251 ELSIF c01_rec.total_pmt_types = 1 THEN
252 --
253 inv_info(i).cc_apps := TRUE; -- CC Applications
254 inv_info(i).all_recs_in_doubt := FALSE; -- No receipts in doubt
255 inv_info(i).rid_reason := null;
256 --
257 /* ELSE --- Greater than 1
258 --
259 inv_info(i).cc_apps := TRUE; -- No CC Applications
260 --
261 IF c01_rec.receipt_handling_option = 'REFUND' THEN
262 inv_info(i).all_recs_in_doubt := TRUE; -- receipts in doubt
263 inv_info(i).rid_reason := l_mult_pmt_types_msg;
264
265 ELSE
266 inv_info(i).all_recs_in_doubt := FALSE; -- No receipts in doubt
267 inv_info(i).rid_reason := null;
268 END IF; -- receipt handling option
269 */ -- GGADHAMS Commented as Refund can be now done for CC and Non CC Receipt
270
271 ELSIF c01_rec.receipt_handling_option = 'REFUND' THEN
272 inv_info(i).cc_apps := TRUE;
273 inv_info(i).all_recs_in_doubt := FALSE; -- No receipts in doubt
274 inv_info(i).rid_reason := null;
275 ELSE
276 inv_info(i).cc_apps := TRUE;
277 --
278 --
279 END IF; -- total_pmt_types
280 --
281 -- Check for RID due to min refund amount check
282 --
283 IF c01_rec.receipt_handling_option = 'REFUND' AND
284 inv_info(i).cc_apps AND
285 NOT inv_info(i).all_recs_in_doubt THEN
286 --
287 -- Get functional unapply amount
288 --
289 IF arp_global.functional_currency <> c01_rec.invoice_currency_code
290 THEN
291 l_total_unapp_acctd_amount:= ARPCURR.functional_amount(
292 amount=>l_total_unapp_amount,
293 currency_code=>
294 c01_rec.invoice_currency_code,
295 exchange_rate=>c01_rec.exchange_rate,
296 precision=>null,
297 min_acc_unit=>null);
298 ELSE
299 l_total_unapp_acctd_amount:= l_total_unapp_amount;
300 END IF; -- functional_currency
301 --
302 -- Check for open split term invoices
303 --
304 IF c01_rec.ps_count > 1 AND c01_rec.inv_balance > 0 THEN
305 --
306 inv_info(i).all_recs_in_doubt := TRUE; -- receipts in doubt
307 inv_info(i).rid_reason := l_split_term_with_bal_msg;
308 --
309 ELSIF NVL(arp_global.sysparam.min_refund_amount, 0) >
310 l_total_unapp_acctd_amount THEN
311 --
312 inv_info(i).all_recs_in_doubt := TRUE; -- receipts in doubt
313 inv_info(i).rid_reason := l_amt_lt_min_ref_amt_msg;
314 --
315 END IF;
316
317 END IF; -- receipt_handling option
318 --
319 IF PG_DEBUG in ('Y', 'C') THEN
320 arp_standard.debug('Calling unapply_receipts...');
321 arp_standard.debug('Inv Customer Trx ID [' ||
322 c01_rec.inv_customer_trx_id || ']');
323 arp_standard.debug('RecHandOption [' ||
324 c01_rec.receipt_handling_option || ']');
325 arp_standard.debug('Unapp amount [' || l_total_unapp_amount || ']');
326 arp_standard.debug('RID Reason [' || inv_info(i).rid_reason || ']');
327 IF inv_info(i).all_recs_in_doubt THEN
328 arp_standard.debug('Rec In doubt ');
329 ELSE
330 arp_standard.debug('Rec NOT In doubt ');
331 END IF;
332 END IF;
333
334 --
335 -- Call unapply_receipts
336 --
337 unapply_receipts (p_inv_customer_trx_id=>c01_rec.inv_customer_trx_id,
338 p_receipt_handling_option=>
339 c01_rec.receipt_handling_option);
340
341 <<end_loop>>
342 NULL;
343 END LOOP;
344 --
345 i := inv_info.NEXT(i);
346 --
347 END LOOP;
348 --
349 <<after_loop>>
350 --
351 IF PG_DEBUG in ('Y', 'C') THEN
352 arp_standard.debug('arp_process_RETURNS.process_invoice_list()- ');
353 END IF;
354 EXCEPTION
355 WHEN OTHERS THEN
356 arp_standard.debug('EXCEPTION : arp_process_returns.process_invoice_list : ' || SQLERRM(SQLCODE));
357 RAISE;
358 END process_invoice_list;
359
360 /*========================================================================
361 | Procedure process_application_list()
362 |
363 | DESCRIPTION
364 | Process Applications from the list prepared by the unapply_receipts
365 |
366 | PSEUDO CODE/LOGIC
367 |
368 | PARAMETERS
369 |
370 |
371 | RETURNS
372 | nothing
373 |
374 | KNOWN ISSUES
375 |
376 |
377 |
378 | NOTES
379 |
380 |
381 |
382 | MODIFICATION HISTORY
383 | Date Author Description of Changes
384 | 18-Jul-2003 Ramakant Alat Created
385 |
386 *=======================================================================*/
390 -- Get open balance for the given Invoice
387 PROCEDURE process_application_list AS
388
389
391 CURSOR c01 (p_payment_schedule_id NUMBER) IS
392 SELECT
393 inv.customer_trx_id inv_customer_trx_id,
394 inv.invoice_currency_code,
395 inv.exchange_rate,
396 invps.amount_due_remaining inv_balance
397 FROM
398 ra_customer_trx inv,
399 ar_payment_schedules invps
400 WHERE
401 invps.payment_schedule_id = p_payment_schedule_id
402 AND inv.customer_trx_id = invps.customer_trx_id;
403
404 adj_exception EXCEPTION;
405 overapp_exception EXCEPTION;
406 l_apply_failed EXCEPTION;
407 l_activity_app_failed EXCEPTION;
408 l_on_account_app_failed EXCEPTION;
409 l_total_unapp_amount NUMBER;
410 l_refund_amount ar_cash_receipts.amount%type;
411 l_pay_refund_amount ar_cash_receipts.amount%type;
412 l_on_account_amount ar_cash_receipts.amount%type;
413 l_old_refund_amount ar_cash_receipts.amount%type;
414 l_receipt_amount ar_cash_receipts.amount%type;
415 l_reapply_amount ar_cash_receipts.amount%type;
416 l_new_apply_amount ar_cash_receipts.amount%type;
417 l_new_apply_amount_fr ar_cash_receipts.amount%type;
418 l_ch_apply_amount_fr ar_cash_receipts.amount%type;
419 l_total_unapp_amount NUMBER;
420 l_rec_in_doubt VARCHAR2(1):='N';
421 l_rid_reason VARCHAR2(2000):= null;
422 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
423 l_msg_count NUMBER;
424 l_msg_data VARCHAR2(2000);
425 l_app_comments ar_receivable_applications.comments%type :=
426 arp_standard.fnd_message('AR_RID_PROCESSED_AS_PER_REQ');
427 l_application_ref_type ar_receivable_applications.application_ref_type%type;
428 l_application_ref_id ar_receivable_applications.application_ref_id%type;
429 l_application_ref_num ar_receivable_applications.application_ref_num%type;
430 l_receivable_application_id ar_receivable_applications.receivable_application_id%type;
431 l_new_ra_rec ar_receivable_applications%rowtype;
432 l_refunding BOOLEAN:=FALSE;
433 l_gdf_rec ar_receipt_api_pub.global_attribute_rec_type;
434 l_dff_rec ar_receipt_api_pub.attribute_rec_type;
435 l_party_id hz_parties.party_id%type;
436 BEGIN
437 IF PG_DEBUG in ('Y', 'C') THEN
438 arp_standard.debug('arp_process_returns.process_application_list()+ ');
439 END IF;
440 --
441 -- Check if there are any applications to process in the list
442 --
443 IF app_info.COUNT = 0 THEN
444 IF PG_DEBUG in ('Y', 'C') THEN
445 arp_standard.debug('No Application in the list to process..');
446 END IF;
447 GOTO after_loop;
448 END IF;
449 --
450 -- Process all applications added to the PL/SQL table by unapply_receipts
451 --
452 FOR i IN 1..app_info.COUNT
453 LOOP
454 --
455 --
456 --
457 IF PG_DEBUG in ('Y', 'C') THEN
458 arp_standard.debug('INV Customer Trx ID [' ||
459 app_tab(i).applied_customer_trx_id || ']');
460 arp_standard.debug('rec_proc_option [' ||
461 app_info(i).rec_proc_option || ']');
462 arp_standard.debug('rec_in_doubt [' ||
463 app_info(i).rec_in_doubt || ']');
464 arp_standard.debug('rec_currency_code [' ||
465 app_info(i).rec_currency_code || ']');
466 arp_standard.debug('inv_currency_code [' ||
467 app_info(i).inv_currency_code || ']');
468 arp_standard.debug('rid_reason [' ||
469 app_info(i).rid_reason || ']');
470 arp_standard.debug('trx_number [' ||
471 app_info(i).trx_number || ']');
472 END IF;
473 --
474 FOR c01_rec IN c01 (app_tab(i).applied_payment_schedule_id)
475 LOOP
476 --
477 IF PG_DEBUG in ('Y', 'C') THEN
478 arp_standard.debug('Inv Balance [' || c01_rec.inv_balance || ']');
479 arp_standard.debug('Inv Customer Trx Id [' || c01_rec.inv_customer_trx_id || ']');
480 END IF;
481 --
482 -- Compute reapply amount ** 1 **
483 --
484 l_reapply_amount := LEAST(app_tab(i).amount_applied,
485 c01_rec.inv_balance );
486
487
488
489 IF PG_DEBUG in ('Y', 'C') THEN
490 arp_standard.debug(' l_reapply_amount [' || l_reapply_amount || ']');
491 END IF;
492
493 IF l_reapply_amount > 0 THEN
494 --
495 -- Get Amount Applied to be passed to Receipt API
496 --
497 IF app_tab(i).amount_applied +
498 NVL(app_tab(i).earned_discount_taken, 0) >= c01_rec.inv_balance
499 THEN
500 --
501 l_new_apply_amount := null;
502 --
503 ELSE
504 --
505 l_new_apply_amount := app_tab(i).amount_applied;
506 --
507 END IF;
508
509 IF PG_DEBUG in ('Y', 'C') THEN
510 arp_standard.debug(' l_new_apply_amount [' || l_new_apply_amount || ']');
511 END IF;
512
513
514 --
515 -- Populate DFF and GDF for re-app from the Old app
516 --
517 populate_dff_and_gdf(p_ra_rec=>app_tab(i),
518 x_dff_rec=>l_dff_rec,
519 x_gdf_rec=>l_gdf_rec
520 );
521 --
522 -- Apply to original payment schedule
523 --
524 -- Re-apply to the application to the same invoice
525 --
526 IF PG_DEBUG in ('Y', 'C') THEN
527 arp_standard.debug('Re-apply back to invoice ps[' ||
528 app_tab(i).applied_payment_schedule_id ||'] : <' ||
529 l_reapply_amount|| '>');
530 END IF;
531 --
532 --
533 ar_receipt_api_pub.Apply(p_api_version => 1.0,
534 x_return_status => l_return_status,
535 x_msg_count => l_msg_count,
536 x_msg_data => l_msg_data,
537 p_cash_receipt_id => app_tab(i).cash_receipt_id,
538 p_applied_payment_schedule_id =>
539 app_tab(i).applied_payment_schedule_id,
540 p_amount_applied => l_new_apply_amount,
541 p_trans_to_receipt_rate =>
542 app_tab(i).trans_to_receipt_rate,
543 p_apply_date => app_tab(i).apply_date,
544 p_comments => app_tab(i).comments,
545 p_ussgl_transaction_code =>
546 app_tab(i).ussgl_transaction_code,
547 p_customer_trx_line_id =>
548 app_tab(i).applied_customer_trx_line_id,
549 p_attribute_rec => l_dff_rec,
550 p_global_attribute_rec => l_gdf_rec,
551 p_customer_reference =>
552 app_tab(i).customer_reference,
553 p_customer_reason => app_tab(i).customer_reason
554 );
555
556 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
557
558 IF (l_msg_count = 1) THEN
559 arp_standard.debug('Apply: ' || l_MSG_DATA);
560 ELSIF(l_MSG_COUNT>1)THEN
561 LOOP
562 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
563 IF (l_MSG_DATA IS NULL)THEN
564 EXIT;
565 END IF;
566 arp_standard.debug('Apply : ' || l_MSG_DATA);
567 END LOOP;
568 END IF;
569
570 arp_standard.debug('Apply failed');
571
572 RAISE l_apply_failed;
573
574 END IF;
575 --
576 -- Fetch Rec App record for the application
577 --
578 arp_app_pkg.fetch_p(p_ra_id=>
579 ar_receipt_api_pub.apply_out_rec.receivable_application_id,
580 p_ra_rec=>l_new_ra_rec);
581 --
582 -- Get Amount Applied from for the new application
583 --
584 l_new_apply_amount_fr := NVL(l_new_ra_rec.amount_applied_from, 0);
585 l_new_apply_amount := NVL(l_new_ra_rec.amount_applied, 0);
586 --
587 ELSE
588 l_new_apply_amount := 0;
589 l_new_apply_amount_fr := 0;
590 END IF;
591 --
592 -- Compute change in Application amount applied "from"
593 --
594 IF app_info(i).cross_currency THEN
595 --
596 IF PG_DEBUG in ('Y', 'C') THEN
597 arp_standard.debug('Cross Currency');
598 arp_standard.debug('Re-apply Amount :' || l_reapply_amount);
599 arp_standard.debug('New-apply Amount :' || l_new_apply_amount);
600 arp_standard.debug('T->R Rate :' ||
601 app_tab(i).trans_to_receipt_rate);
602 arp_standard.debug('Currency REC :' ||
603 app_info(i).rec_currency_code);
604 END IF;
605 --
606 /***
607 l_new_apply_amount_fr :=
608 arp_util.CurrRound(
609 l_reapply_amount *
610 app_tab(i).trans_to_receipt_rate,
611 app_info(i).rec_currency_code
612 );
613 ***/
614 --
615 l_ch_apply_amount_fr := app_tab(i).amount_applied_from -
616 l_new_apply_amount_fr;
617 --
618 ELSE
619 IF PG_DEBUG in ('Y', 'C') THEN
620 arp_standard.debug('Not Cross Currency');
621 arp_standard.debug('Old Amount Applied :' ||
622 app_tab(i).amount_applied );
623 arp_standard.debug('New Amount Applied :' ||
624 l_new_apply_amount);
625 END IF;
626 l_ch_apply_amount_fr := app_tab(i).amount_applied -
627 l_new_apply_amount;
628 END IF;
629 --
630 IF PG_DEBUG in ('Y', 'C') THEN
634 END IF;
631 arp_standard.debug('Change in App amount [' ||
632 app_tab(i).applied_payment_schedule_id ||'] : <' ||
633 l_ch_apply_amount_fr || '>');
635 --
636 --
637 -- Initialize amounts
638 --
639 l_refund_amount := 0;
640 l_old_refund_amount := 0;
641 l_on_account_amount := 0;
642 l_receipt_amount := 0;
643 l_refunding := FALSE;
644 l_pay_refund_amount :=0;
645
646 IF app_info(i).rec_in_doubt = 'N' AND
647 app_info(i).rec_proc_option = 'REFUND' THEN
648 --
649 l_refunding := TRUE;
650 --
651 IF PG_DEBUG in ('Y', 'C') THEN
652 arp_standard.debug('Refunding...');
653 END IF;
654 --
655 -- Get receipt amount and old refund amounts from the receipt
656 --
657 get_receipt_amounts(
658 p_cash_receipt_id=>app_tab(i).cash_receipt_id,
659 x_receipt_amount=>l_receipt_amount,
660 x_refund_amount=>l_old_refund_amount,
661 x_rec_proc_option=> app_info(i).rec_proc_option);
662 --
663 IF PG_DEBUG in ('Y', 'C') THEN
664 arp_standard.debug('Cash Receipt Id ' ||
665 app_tab(i).cash_receipt_id ||'] : RecAmt<' ||
666 l_receipt_amount|| '>' );
667 arp_standard.debug('Old Refund Amount :[' ||
668 l_old_refund_amount ||']');
669 END IF;
670 --
671 --
672 -- Compute refund amount = LEAST(receipt amount - old refunds,
673 -- change in application amount)
674 --
675 l_refund_amount := LEAST(l_receipt_amount - l_old_refund_amount,
676 l_ch_apply_amount_fr);
677 --
678 ELSE
679 l_refund_amount := 0;
680 END IF;
681 --
682 IF PG_DEBUG in ('Y', 'C') THEN
683 arp_standard.debug('New Refund Amount [' || l_refund_amount || ']');
684 END IF;
685
686 --GGADHAMS Added for Payment Refund
687 IF app_info(i).rec_in_doubt = 'N' AND
688 app_info(i).rec_proc_option = 'PAY_REFUND' THEN
689 --
690 l_refunding := TRUE;
691 --
692 IF PG_DEBUG in ('Y', 'C') THEN
693 arp_standard.debug('Payment Refunding...');
694 END IF;
695 --
696 -- Get receipt amount and old refund amounts from the receipt
697 --
698 get_receipt_amounts(
699 p_cash_receipt_id=>app_tab(i).cash_receipt_id,
700 x_receipt_amount=>l_receipt_amount,
701 x_refund_amount=>l_old_refund_amount,
702 x_rec_proc_option=> app_info(i).rec_proc_option);
703 --
704 IF PG_DEBUG in ('Y', 'C') THEN
705 arp_standard.debug('Cash Receipt Id ' ||
706 app_tab(i).cash_receipt_id ||'] : RecAmt<' ||
707 l_receipt_amount|| '>' );
708 arp_standard.debug('Old Refund Amount :[' ||
709 l_old_refund_amount ||']');
710 END IF;
711 --
712 --
713 -- Compute refund amount = LEAST(receipt amount - old refunds,
714 -- change in application amount)
715 --
716 l_pay_refund_amount := LEAST(l_receipt_amount - l_old_refund_amount,
717 l_ch_apply_amount_fr);
718 --
719 ELSE
720 l_pay_refund_amount := 0;
721 END IF;
722 --
723 IF PG_DEBUG in ('Y', 'C') THEN
724 arp_standard.debug('New Payment Refund Amount [' || l_pay_refund_amount || ']');
725 END IF;
726 --Added till here for Payment Refund
727 --
728
729 --
730 -- Get On-account application amount = (change in application amount
731 -- - refund amount)
732 --
733 l_on_account_amount := l_ch_apply_amount_fr - l_refund_amount - l_pay_refund_amount;
734 --
735 IF PG_DEBUG in ('Y', 'C') THEN
736 arp_standard.debug('On Account Amount [' ||
737 l_on_account_amount|| ']');
738 END IF;
739 --
740 --
741 -- Create Credit Card application
742 --
743 IF l_refund_amount > 0 THEN
744 -- Apply to CCR
745 IF PG_DEBUG in ('Y', 'C') THEN
746 arp_standard.debug('Creating CCR application..');
747 arp_standard.debug('l_app_comments :[' || l_app_comments ||']');
748 END IF;
749 --
750 -- Initialize IN-OUT variables
751 --
752 l_application_ref_type := null;
753 l_application_ref_id := null;
754 l_application_ref_num := null;
755 --
756
757 select party_id
758 into l_party_id
759 from
763 and trx.trx_number = app_info(i).trx_number;
760 hz_cust_accounts acc,
761 ra_customer_trx trx
762 where trx.bill_to_customer_id = acc.cust_account_id
764
765 ar_receipt_api_pub.activity_application(
766 p_api_version => 1.0,
767 x_return_status => l_return_status,
768 x_msg_count => l_msg_count,
769 x_msg_data => l_msg_data,
770 p_cash_receipt_id =>
771 app_tab(i).cash_receipt_id,
772 p_amount_applied => l_refund_amount,
773 p_applied_payment_schedule_id => -6,
774 p_receivables_trx_id => g_ccr_receivables_trx_id,
775 p_comments => l_app_comments,
776 p_application_ref_type => l_application_ref_type,
777 p_application_ref_id => l_application_ref_id,
778 p_application_ref_num => l_application_ref_num,
779 p_secondary_application_ref_id =>
780 app_tab(i).applied_customer_trx_id,
781 p_secondary_app_ref_type => 'TRANSACTION',
782 p_secondary_app_ref_num => app_info(i).trx_number,
783 p_receivable_application_id => l_receivable_application_id,
784 p_party_id => l_party_id
785 );
786
787 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
788
789 IF (l_msg_count = 1) THEN
790 arp_standard.debug('ActivityApp: ' || l_MSG_DATA);
791 ELSIF(l_MSG_COUNT>1)THEN
792 LOOP
793 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
794 IF (l_MSG_DATA IS NULL)THEN
795 EXIT;
796 END IF;
797 arp_standard.debug('ActivityApp: ' || l_MSG_DATA);
798 END LOOP;
799 END IF;
800
801 arp_standard.debug('ActivityApp failed');
802
803 RAISE l_activity_app_failed;
804 END IF; -- Handle API errors
805 --
806 END IF; -- Process CCR
807 --
808 -- Create On-account application
809 --
810 IF l_on_account_amount > 0 THEN
811 --
812 -- Apply to ON-ACCOUNT
813 IF PG_DEBUG in ('Y', 'C') THEN
814 arp_standard.debug('Creating ON-ACCOUNT application..');
815 arp_standard.debug('l_app_comments :[' || l_app_comments ||']');
816 arp_standard.debug('l_app_comments NVL:[' ||
817 NVL(app_info(i).rid_reason, l_app_comments) ||']');
818 END IF;
819 --
820 --
821 IF l_refunding THEN
822 l_app_comments := arp_standard.fnd_message('AR_RID_TOTAL_REFUND_LIMIT');
823 END IF;
824 ar_receipt_api_pub.Apply_on_account(
825 p_api_version => 1.0,
826 x_return_status => l_return_status,
827 x_msg_count => l_msg_count,
828 x_msg_data => l_msg_data,
829 p_cash_receipt_id =>
830 app_tab(i).cash_receipt_id,
831 p_amount_applied => l_on_account_amount,
832 p_comments => NVL(app_info(i).rid_reason,
833 l_app_comments),
834 p_secondary_application_ref_id =>
835 app_tab(i).applied_customer_trx_id,
836 p_secondary_app_ref_type => 'TRANSACTION',
837 p_secondary_app_ref_num => app_info(i).trx_number
838 );
839 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
840
841 IF (l_msg_count = 1) THEN
842 arp_standard.debug('OnaccountApp: ' || l_MSG_DATA);
843 ELSIF(l_MSG_COUNT>1)THEN
844 LOOP
845 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
846 IF (l_MSG_DATA IS NULL)THEN
847 EXIT;
848 END IF;
849 arp_standard.debug('OnaccountApp : ' || l_MSG_DATA);
850 END LOOP;
851 END IF;
852
853 arp_standard.debug('OnaccountApp failed');
854
855 RAISE l_on_account_app_failed;
856
857 END IF;
858 --
859 END IF; -- Process On-Account
860
861
862 -- GGADHAMS
863 -- Create Payment Refund application
864 --
865 IF l_pay_refund_amount > 0 THEN
866 --
867 -- Apply to PAYMENT REFUND
868 IF PG_DEBUG in ('Y', 'C') THEN
869 arp_standard.debug('Creating Payment Refund application..');
870 arp_standard.debug('l_app_comments :[' || l_app_comments ||']');
871 arp_standard.debug('l_app_comments NVL:[' ||
872 NVL(app_info(i).rid_reason, l_app_comments) ||']');
873 END IF;
874 --
878 p_commit =>FND_API.G_FALSE,
875 ar_receipt_api_pub.activity_application(
876 p_api_version => 1.0,
877 p_init_msg_list =>FND_API.G_FALSE,
879 p_validation_level =>FND_API.G_VALID_LEVEL_FULL,
880 x_return_status => l_return_status,
881 x_msg_count => l_msg_count,
882 x_msg_data => l_msg_data,
883 p_cash_receipt_id => app_tab(i).cash_receipt_id,
884 p_amount_applied => l_pay_refund_amount,
885 p_applied_payment_schedule_id => -8,
886 p_receivables_trx_id => g_nccr_receivables_trx_id,
887 p_comments => l_app_comments,
888 p_application_ref_type => l_application_ref_type,
889 p_application_ref_id => l_application_ref_id,
890 p_application_ref_num => l_application_ref_num,
891 p_secondary_application_ref_id => app_tab(i).applied_customer_trx_id,
892 p_secondary_app_ref_type => 'TRANSACTION',
893 p_secondary_app_ref_num => app_info(i).trx_number,
894 p_receivable_application_id => l_receivable_application_id
895 -- p_party_id => 1004
896 );
897 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
898
899 IF (l_msg_count = 1) THEN
900 arp_standard.debug('ActivityApp: ' || l_MSG_DATA);
901 ELSIF(l_MSG_COUNT>1)THEN
902 LOOP
903 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
904 IF (l_MSG_DATA IS NULL)THEN
905 EXIT;
906 END IF;
907 arp_standard.debug('ActivityApp: ' || l_MSG_DATA);
908 END LOOP;
909 END IF;
910
911 arp_standard.debug('ActivityApp failed');
912
913 RAISE l_activity_app_failed;
914 END IF; -- Handle API errors
915 --
916 END IF; -- Process PAyment Refund
917
918
919
920
921 <<end_loop>>
922 NULL;
923 END LOOP;
924 END LOOP;
925 --
926 <<after_loop>>
927 --
928 IF PG_DEBUG in ('Y', 'C') THEN
929 arp_standard.debug('arp_process_RETURNS.process_application_list()- ');
930 END IF;
931 EXCEPTION
932 WHEN OTHERS THEN
933 arp_standard.debug('EXCEPTION : arp_process_returns.process_application_list : ' || SQLERRM(SQLCODE));
934 RAISE;
935 END process_application_list;
936
937 /*========================================================================
938 | Procedure unapply_receipts()
939 |
940 | DESCRIPTION
941 | Unapply all receipt applications for the given invoice
942 | and create the application list. This list will be used to create
943 | special applications and apply remaining amount back to original
944 | invoice
945 |
946 | PSEUDO CODE/LOGIC
947 |
948 | PARAMETERS
949 |
950 | p_inv_customer_trx_id - Invoice customer Trx ID
951 | p_receipt_handling_option IN VARCHAR2
952 |
953 | RETURNS
954 | nothing
955 |
956 | KNOWN ISSUES
957 |
958 |
959 |
960 | NOTES
961 |
962 |
963 |
964 | MODIFICATION HISTORY
965 | Date Author Description of Changes
966 | 17-Jul-2003 Ramakant Alat Created
967 |
968 *=======================================================================*/
969
970 PROCEDURE unapply_receipts (p_inv_customer_trx_id IN NUMBER,
971 p_receipt_handling_option IN VARCHAR2
972 ) AS
973
974 --
975 -- Cursor to get information about all receipt applications for the
976 -- given invoice.
977 --
978 /*GGADHAMS Modified the cursor for automated Receipt Handling using
979 Payment Refund*/
980 CURSOR c02 (p_customer_trx_id NUMBER,
981 p_receipt_handling_option IN VARCHAR2) IS
982 SELECT
983 ra.receivable_application_id,
984 ra.cash_receipt_id,
985 cr.amount,
986 cr.currency_code rec_currency_code,
987 inv.invoice_currency_code,
988 ra.applied_customer_trx_id,
989 ra.applied_payment_schedule_id,
990 inv.trx_number,
991 rm.payment_channel_code payment_type,
992 -- DECODE(p_receipt_handling_option, 'REFUND',
993 -- DECODE(rm.payment_channel_code,
994 -- 'CREDIT_CARD', 'REFUND',
995 -- 'ON-ACCOUNT'),
996 -- 'ON-ACCOUNT') rec_proc_option,
997 DECODE(p_receipt_handling_option, 'REFUND',
998 DECODE(rm.payment_channel_code,
999 'CREDIT_CARD', 'REFUND',
1000 ' BANK_ACCT_XFER','PAY_REFUND',
1001 null,'PAY_REFUND',
1002 'ON-ACCOUNT'),
1003 'ON-ACCOUNT') rec_proc_option,
1004 ra.amount_applied,
1008 ,ar_cash_receipts cr
1005 ra.amount_applied_from
1006 FROM
1007 ar_receivable_applications ra
1009 ,ar_receipt_methods rm
1010 ,ra_customer_trx inv
1011 WHERE
1012 ra.applied_customer_trx_id = p_customer_trx_id
1013 AND ra.cash_receipt_id = cr.cash_receipt_id
1014 AND rm.receipt_method_id = cr.receipt_method_id
1015 AND ra.display = 'Y'
1016 AND ra.applied_customer_trx_id = inv.customer_trx_id
1017 ORDER BY
1018 ra.APPLY_DATE, --- This is for aging
1019 TO_NUMBER(DECODE(p_receipt_handling_option, 'REFUND',
1020 DECODE(rm.payment_channel_code, 'CREDIT_CARD',
1021 2, 1) ,
1022 ra.amount_applied)) desc,
1023 ra.amount_applied desc;
1024
1025 -- Local Variables
1026 l_application_ref_type ar_receivable_applications.application_ref_type%type;
1027 l_application_ref_id ar_receivable_applications.application_ref_id%type;
1028 l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%type;
1029 l_application_ref_num ar_receivable_applications.application_ref_num%type;
1030 l_receivable_application_id ar_receivable_applications.receivable_application_id%type;
1031 l_receivables_trx_id ar_receivable_applications.receivables_trx_id%type;
1032 l_app_comments ar_receivable_applications.comments%type;
1033
1034 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1035 l_msg_count NUMBER;
1036 l_msg_data VARCHAR2(2000);
1037 l_rid_reason VARCHAR2(2000);
1038 l_unapp_amt_remaining ar_receivable_applications.amount_applied%type;
1039 l_unapp_amount ar_receivable_applications.amount_applied%type;
1040 l_ra_rec ar_receivable_applications%rowtype;
1041 l_unapply_failed EXCEPTION;
1042 l_apply_failed EXCEPTION;
1043 l_activity_app_failed EXCEPTION;
1044 l_on_account_app_failed EXCEPTION;
1045 l_ra_info app_info_type;
1046 l_rec_in_doubt VARCHAR2(1):='N';
1047 BEGIN
1048 IF PG_DEBUG in ('Y', 'C') THEN
1049 arp_standard.debug('arp_process_RETURNS.unapply_receipts()+ ');
1050 arp_standard.debug('p_inv_customer_trx_id :<' || p_inv_customer_trx_id ||'>');
1051 arp_standard.debug('rec_hand_option :<' || p_receipt_handling_option ||'>');
1052 END IF;
1053 --
1054 --
1055 --
1056 FOR c02_rec IN c02(p_inv_customer_trx_id,
1057 p_receipt_handling_option) LOOP
1058 --
1059 --
1060 l_rec_in_doubt := 'N';
1061 l_rid_reason := null;
1062 --
1063 -- If receipt is not already in doubt then check for doubt
1064 --
1065
1066 -- Need to add check receipt in doubt for PAY_REFUND
1067 IF c02_rec.rec_proc_option = 'REFUND' THEN
1068 --
1069 IF PG_DEBUG in ('Y', 'C') THEN
1070 arp_standard.debug('CC receipt with refund request ');
1071 END IF;
1072 --
1073 IF inv_info(p_inv_customer_trx_id).all_recs_in_doubt THEN
1074 --
1075 l_rec_in_doubt := 'Y';
1076 l_rid_reason := inv_info(p_inv_customer_trx_id).rid_reason;
1077 --
1078 IF PG_DEBUG in ('Y', 'C') THEN
1079 arp_standard.debug('All recs in doubt :<' || l_rid_reason ||'>');
1080 END IF;
1081 --
1082 ELSE
1083 --
1084 check_rec_in_doubt(p_cash_receipt_id=>c02_rec.cash_receipt_id,
1085 x_rec_in_doubt =>l_rec_in_doubt,
1086 x_rid_reason=>l_rid_reason,
1087 x_rec_proc_option => c02_rec.rec_proc_option);
1088 --
1089 --
1090 IF PG_DEBUG in ('Y', 'C') THEN
1091 arp_standard.debug('After RID chk :<' || l_rid_reason ||'>');
1092 END IF;
1093 --
1094 END IF;
1095 --
1096
1097 END IF;
1098
1099
1100 IF c02_rec.rec_proc_option = 'PAY_REFUND' THEN
1101 --
1102 IF PG_DEBUG in ('Y', 'C') THEN
1103 arp_standard.debug('Non CC receipt with refund request ');
1104 END IF;
1105 --
1106 IF inv_info(p_inv_customer_trx_id).all_recs_in_doubt THEN
1107 --
1108 l_rec_in_doubt := 'Y';
1109 l_rid_reason := inv_info(p_inv_customer_trx_id).rid_reason;
1110 --
1111 IF PG_DEBUG in ('Y', 'C') THEN
1112 arp_standard.debug('All recs in doubt :<' || l_rid_reason ||'>');
1113 END IF;
1114 --
1115 ELSE
1116 --
1117 check_rec_in_doubt(p_cash_receipt_id=>c02_rec.cash_receipt_id,
1118 x_rec_in_doubt =>l_rec_in_doubt,
1119 x_rid_reason=>l_rid_reason,
1120 x_rec_proc_option=> c02_rec.rec_proc_option);
1121 --
1122 --
1123 IF PG_DEBUG in ('Y', 'C') THEN
1124 arp_standard.debug('After Non CC RID chk :<' || l_rid_reason ||'>');
1125 END IF;
1126 --
1127 END IF;
1128 --
1129
1130 END IF;
1134 -- Before we unapply receipt, get current application info.
1131
1132
1133 --
1135 -- This application info will be used to create special apps and
1136 -- remaining amount re-app to old transaction.
1137 --
1138 -- Fetch Rec App record for the application
1139 --
1140 arp_app_pkg.fetch_p(p_ra_id=>c02_rec.receivable_application_id,
1141 p_ra_rec=>l_ra_rec);
1142 --
1143 -- Add Receivable Application record to the list
1144 --
1145 -- This list will be used to create special apps e.g. REFUND, ON-ACCOUNT
1146 -- and re-app to old transaction
1147 --
1148 l_ra_info.rec_proc_option := c02_rec.rec_proc_option;
1149 l_ra_info.rec_in_doubt := l_rec_in_doubt;
1150 l_ra_info.rid_reason := l_rid_reason;
1151 l_ra_info.trx_number := c02_rec.trx_number;
1152 l_ra_info.rec_currency_code := c02_rec.rec_currency_code;
1153 l_ra_info.inv_currency_code := c02_rec.invoice_currency_code;
1154 --
1155 IF c02_rec.rec_currency_code <> c02_rec.invoice_currency_code THEN
1156 l_ra_info.cross_currency := TRUE;
1157 ELSE
1158 l_ra_info.cross_currency := FALSE;
1159 END IF;
1160 --
1161 l_ra_info.inv_currency_code := c02_rec.invoice_currency_code;
1162 --
1163 add_ra_to_list(p_ra_info=>l_ra_info, p_ra_rec=>l_ra_rec);
1164 --
1165 IF PG_DEBUG in ('Y', 'C') THEN
1166 arp_standard.debug('rec_app_id :<' ||
1167 c02_rec.receivable_application_id ||'>');
1168 arp_standard.debug('rec_in_doubt :<' || l_rec_in_doubt ||'>');
1169 arp_standard.debug('rec_in_doubt_reason :<' ||
1170 l_rid_reason ||'>');
1171 arp_standard.debug('rec_proc_option :<' ||
1172 c02_rec.rec_proc_option ||'>');
1173 END IF;
1174 --
1175 -- Unapply the application
1176 --
1177 ar_receipt_api_pub.Unapply(
1178 p_api_version => 1.0,
1179 x_return_status => l_return_status,
1180 x_msg_count => l_msg_count,
1181 x_msg_data => l_msg_data,
1182 p_receivable_application_id => c02_rec.receivable_application_id
1183 );
1184
1185 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1186
1187 IF (l_msg_count = 1) THEN
1188 arp_standard.debug('Unapply: ' || l_MSG_DATA);
1189 ELSIF(l_MSG_COUNT>1)THEN
1190 LOOP
1191 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
1192 IF (l_MSG_DATA IS NULL)THEN
1193 EXIT;
1194 END IF;
1195 arp_standard.debug('UNapply: ' || l_MSG_DATA);
1196 END LOOP;
1197 END IF;
1198
1199 arp_standard.debug('Unapplication failed');
1200 RAISE l_unapply_failed;
1201 END IF;
1202
1203 END LOOP;
1204
1205 IF PG_DEBUG in ('Y', 'C') THEN
1206 arp_standard.debug('arp_process_RETURNS.unapply_receipts()- ');
1207 END IF;
1208 EXCEPTION
1209 WHEN OTHERS THEN
1210 arp_standard.debug('arp_process_returns.unapply_receipts : '
1211 || SQLERRM(SQLCODE));
1212 RAISE;
1213
1214 END unapply_receipts;
1215
1216 --
1217 -- Add invoice to the list, which will be used for further processing
1218 --
1219
1220 PROCEDURE add_invoice (p_customer_trx_id IN NUMBER) IS
1221 BEGIN
1222 IF PG_DEBUG in ('Y', 'C') THEN
1223 arp_standard.debug('arp_process_RETURNS.add_invoice()+ ');
1224 arp_standard.debug('p_customer_trx_id :<' || p_customer_trx_id ||'>');
1225 END IF;
1226
1227 IF inv_info.EXISTS(p_customer_trx_id) THEN
1228 inv_info(p_customer_trx_id).num_of_cms :=
1229 inv_info(p_customer_trx_id).num_of_cms + 1;
1230 ELSE
1231 inv_info(p_customer_trx_id).num_of_cms := 1;
1232 END IF;
1233
1234 IF PG_DEBUG in ('Y', 'C') THEN
1235 arp_standard.debug('arp_process_RETURNS.add_invoice()- ');
1236 END IF;
1237 EXCEPTION
1238 WHEN OTHERS THEN
1239 arp_standard.debug('arp_process_returns.add_invoice : ' ||
1240 SQLERRM(SQLCODE));
1241 RAISE;
1242 END;
1243
1244 --
1245 -- Add Receipt Application to the list,
1246 -- which will be used for further processing
1247 --
1248
1249 PROCEDURE add_ra_to_list(p_ra_info IN app_info_type,
1250 p_ra_rec IN ar_receivable_applications%rowtype) AS
1251 l_cnt NUMBER := app_info.COUNT;
1252 BEGIN
1253 --
1254 IF PG_DEBUG in ('Y', 'C') THEN
1255 arp_standard.debug('arp_process_RETURNS.add_ra_to_list()+ ');
1256 arp_standard.debug('count :<' || l_cnt ||'>');
1257 END IF;
1258 --
1259 l_cnt := l_cnt + 1;
1260 app_info(l_cnt) := p_ra_info;
1261 app_tab(l_cnt) := p_ra_rec;
1262 --
1263 IF PG_DEBUG in ('Y', 'C') THEN
1264 arp_standard.debug('arp_process_RETURNS.add_ra_to_list()- ');
1265 END IF;
1266 --
1267 EXCEPTION
1268 WHEN OTHERS THEN
1269 arp_standard.debug('arp_process_returns.add_ra_to_list : ' ||
1270 SQLERRM(SQLCODE));
1271 RAISE;
1272 END;
1273 --
1274 -- Get Total CM amount for a given invoice
1275 --
1276 FUNCTION get_total_cm_amount (p_inv_customer_trx_id IN NUMBER,
1277 p_request_id IN NUMBER) RETURN NUMBER AS
1278
1279 l_total_cm_amount RA_CUSTOMER_TRX_LINES.EXTENDED_AMOUNT%TYPE;
1280
1281 BEGIN
1282 --
1283 IF PG_DEBUG in ('Y', 'C') THEN
1284 arp_standard.debug('arp_process_RETURNS.get_total_cm_amount()+ ');
1285 arp_standard.debug('p_inv_customer_trx_id :<'
1286 || p_inv_customer_trx_id ||'>');
1287 END IF;
1288 --
1289 SELECT NVL(SUM(extended_amount) , 0)
1290 INTO l_total_cm_amount
1291 FROM RA_CUSTOMER_TRX_LINES
1292 WHERE previous_customer_trx_id = p_inv_customer_trx_id
1293 AND request_id = p_request_id;
1294 --
1295 IF PG_DEBUG in ('Y', 'C') THEN
1296 arp_standard.debug('l_total_cm_amount :<'
1297 || l_total_cm_amount ||'>');
1298 arp_standard.debug('arp_process_RETURNS.get_total_cm_amount()- ');
1299 END IF;
1300 --
1301
1302 RETURN l_total_cm_amount;
1303 EXCEPTION
1304 WHEN OTHERS THEN
1305 arp_standard.debug('EXCEPTION:arp_process_returns.get_total_cm_amount : '
1306 || SQLERRM(SQLCODE));
1307 RAISE;
1308
1309 END get_total_cm_amount;
1310
1311 --
1312 -- Get total payment types for all receipts applied to this invoice
1313 --
1314 --Modified the select using Payment Channel code to identify the payment type
1315 --Need confirmation on count and NVL
1316 FUNCTION get_total_payment_types (p_inv_customer_trx_id IN NUMBER)
1317 RETURN NUMBER AS
1318
1319 l_total_payment_types NUMBER:=0;
1320 l_total_cc_pmts NUMBER:=0;
1321
1322 BEGIN
1323 --
1324 IF PG_DEBUG in ('Y', 'C') THEN
1325 arp_standard.debug('arp_process_RETURNS.get_total_payment_types()+ ');
1326 arp_standard.debug('p_inv_customer_trx_id :<'
1327 || p_inv_customer_trx_id ||'>');
1328 END IF;
1329 --
1330 SELECT
1331 -- count(distinct NVL(rm.payment_channel_code, 'CHECK')) ,
1332 count(distinct NVL(rm.payment_channel_code, 'CHECK')) ,
1333 -- sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
1334 sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
1335 INTO
1336 l_total_payment_types,
1337 l_total_cc_pmts
1338 FROM AR_RECEIVABLE_APPLICATIONS ra,
1339 ar_cash_receipts cr,
1340 ar_receipt_methods rm
1341 WHERE ra.applied_customer_trx_id = p_inv_customer_trx_id
1342 AND ra.cash_receipt_id = cr.cash_receipt_id
1343 AND cr.receipt_method_id = rm.receipt_method_id;
1344
1345 IF l_total_cc_pmts = 0 THEN
1346 l_total_payment_types := 0;
1347 END IF;
1348 --
1349 IF PG_DEBUG in ('Y', 'C') THEN
1350 arp_standard.debug('l_total_payment_types :<'
1351 || l_total_payment_types ||'>');
1352 arp_standard.debug('arp_process_RETURNS.get_total_payment_types()- ');
1353 END IF;
1354 --
1355 RETURN l_total_payment_types;
1356 --
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 arp_standard.debug('EXCEPTION:arp_process_returns.get_total_payment_types : '
1360 || SQLERRM(SQLCODE));
1361 RAISE;
1362
1363 END get_total_payment_types;
1364
1365
1366 --
1367 -- Get receipt and refund amounts
1368 --
1369 PROCEDURE get_receipt_amounts (p_cash_receipt_id IN NUMBER,
1370 x_receipt_amount OUT NOCOPY NUMBER,
1371 x_refund_amount OUT NOCOPY NUMBER,
1372 x_rec_proc_option IN VARCHAR2) AS
1373
1374 BEGIN
1375 --
1376 IF PG_DEBUG in ('Y', 'C') THEN
1377 arp_standard.debug('arp_process_RETURNS.get_receipt_amounts()+ ');
1378 arp_standard.debug('p_cash_receipt_id :<'
1379 || p_cash_receipt_id ||'>');
1380 END IF;
1381 --
1382 x_receipt_amount := 0;
1383 x_refund_amount := 0;
1384
1385 IF x_rec_proc_option = 'REFUND' THEN
1386
1387 SELECT NVL(amount, 0), NVL(SUM(amount_applied) , 0)
1388 INTO x_receipt_amount, x_refund_amount
1389 FROM ar_cash_receipts cr, ar_receivable_applications ra
1390 WHERE cr.cash_receipt_id = p_cash_receipt_id
1391 AND cr.cash_receipt_id = ra.cash_receipt_id(+)
1392 AND ra.applied_payment_schedule_id(+) = -6
1393 AND ra.display(+) = 'Y'
1394 GROUP BY amount;
1395
1396 ELSIF x_rec_proc_option = 'PAY_REFUND' THEN
1397 SELECT NVL(amount, 0), NVL(SUM(amount_applied) , 0)
1398 INTO x_receipt_amount, x_refund_amount
1399 FROM ar_cash_receipts cr, ar_receivable_applications ra
1400 WHERE cr.cash_receipt_id = p_cash_receipt_id
1401 AND cr.cash_receipt_id = ra.cash_receipt_id(+)
1402 AND ra.applied_payment_schedule_id(+) = -8
1403 AND ra.display(+) = 'Y'
1404 GROUP BY amount;
1405
1406 END IF;
1407
1408 --
1409 IF PG_DEBUG in ('Y', 'C') THEN
1410 arp_standard.debug('x_receipt_amount :<'
1411 || x_receipt_amount ||'>');
1412 arp_standard.debug('x_refund_amount :<'
1413 || x_refund_amount ||'>');
1414 arp_standard.debug('arp_process_RETURNS.get_receipt_amounts()- ');
1415 END IF;
1416 --
1417
1418 EXCEPTION
1419 WHEN NO_DATA_FOUND THEN
1420 NULL;
1421 WHEN OTHERS THEN
1422 arp_standard.debug('EXCEPTION:arp_process_returns.get_receipt_amounts : '
1423 || SQLERRM(SQLCODE));
1424 RAISE;
1425 END;
1426
1427 /*===========================================================================+
1428 | PORCEDURE |
1429 | check_rec_in_doubt |
1430 | |
1431 | DESCRIPTION |
1432 | This function checks if given receipt is doubt |
1433 | Given receipt can be in doubt for any of the following reasons |
1434 | . If receipt is a CC receipt and is not remitted |
1435 | . If receipt has Special application of Claims Investigation |
1436 | . If the receipt is Debit Memo reversed |
1437 | . If the Receipt is a Non CC receipt and is not cleared
1438 | |
1439 | SCOPE - PUBLIC |
1440 | |
1441 | ARGUMENTS : IN : p_cash_receipt_id |
1442 | |
1443 | : OUT : x_rec_in_doubt (Y/N) |
1444 | OUT : x_rid_reason |
1445 | |
1446 | NOTES : |
1447 | |
1448 | |
1449 | MODIFICATION HISTORY |
1450 | 19-Jun-03 Ramakant Alat Created |
1451 | 27-Dec-05 Gyanajyothi G Added the check for Non CC receipt |
1452 +===========================================================================*/
1453 PROCEDURE check_rec_in_doubt(p_cash_receipt_id IN NUMBER,
1454 x_rec_in_doubt OUT NOCOPY VARCHAR2,
1455 x_rid_reason OUT NOCOPY VARCHAR2,
1456 x_rec_proc_option IN VARCHAR2) IS
1457 BEGIN
1458 ---
1459 IF PG_DEBUG in ('Y', 'C') THEN
1460 arp_standard.debug('arp_process_RETURNS.check_rec_in_doubt()+ ');
1461 END IF;
1462 ---
1463 x_rec_in_doubt := 'N';
1464 x_rid_reason := null;
1465 ---
1466 --- For CC receipts, receipt should be remitted
1467 ---
1468 IF x_rec_proc_option = 'REFUND' THEN
1469 BEGIN
1470 SELECT 'Y', arp_standard.fnd_message('AR_RID_NOT_REMITTED_OR_CLEARED')
1471 INTO x_rec_in_doubt, x_rid_reason
1472 FROM dual
1473 WHERE
1474 (
1475 NOT EXISTS
1476 (
1477 SELECT 1
1478 FROM AR_CASH_RECEIPT_HISTORY crh
1479 WHERE crh.cash_receipt_id = p_cash_receipt_id
1480 AND crh.status IN ('REMITTED', 'CLEARED')
1481 )
1482 );
1483 EXCEPTION
1484 WHEN NO_DATA_FOUND THEN
1485 NULL;
1486 WHEN OTHERS THEN
1487 arp_standard.debug('Unexpected error '||sqlerrm||
1488 ' occurred in arp_process_returns.check_rec_in_doubt');
1489 RAISE;
1490 END;
1491
1492 ---
1493 IF PG_DEBUG in ('Y', 'C') THEN
1494 arp_standard.debug('After REFUND x_rec_in_doubt[x_rid_reason]: ' || x_rec_in_doubt ||
1495 '[' || x_rid_reason || ']');
1496 END IF;
1497
1498 ELSIF x_rec_proc_option = 'PAY_REFUND' THEN
1499 ---
1500 --- For Non CC Receipts , receipt should be cleared
1501 ---
1502 BEGIN
1503 SELECT 'Y', arp_standard.fnd_message('AR_RID_NOT_CLEARED')
1504 INTO x_rec_in_doubt, x_rid_reason
1505 FROM dual
1506 WHERE
1507 (
1508 NOT EXISTS
1509 (
1510 SELECT 1
1511 FROM AR_CASH_RECEIPT_HISTORY crh
1512 WHERE crh.cash_receipt_id = p_cash_receipt_id
1516 EXCEPTION
1513 AND crh.status IN ('CLEARED')
1514 )
1515 );
1517 WHEN NO_DATA_FOUND THEN
1518 NULL;
1519 WHEN OTHERS THEN
1520 arp_standard.debug('Unexpected error '||sqlerrm||
1521 ' occurred in arp_process_returns.check_rec_in_doubt');
1522 RAISE;
1523 END;
1524
1525 ---
1526 IF PG_DEBUG in ('Y', 'C') THEN
1527 arp_standard.debug('After Non CC REFUND x_rec_in_doubt[x_rid_reason]: ' || x_rec_in_doubt ||
1528 '[' || x_rid_reason || ']');
1529 END IF;
1530 END IF;
1531
1532
1533 ---
1534 ---
1535 --- There should not be any Claims Investigation or CB special application
1536 ---
1537 BEGIN
1538 SELECT 'Y', arp_standard.fnd_message('AR_RID_CLAIM_OR_CB_APP_EXISTS')
1539 INTO x_rec_in_doubt, x_rid_reason
1540 FROM dual
1541 WHERE
1542 EXISTS
1543 (
1544 SELECT 1
1545 FROM ar_receivable_applications ra
1546 WHERE ra.cash_receipt_id = p_cash_receipt_id
1547 AND applied_payment_schedule_id IN (-4, -5)
1548 AND display = 'Y'
1549 );
1550 EXCEPTION
1551 WHEN NO_DATA_FOUND THEN
1552 NULL;
1553 WHEN OTHERS THEN
1554 arp_standard.debug('Unexpected error '||sqlerrm||
1555 ' occurred in arp_process_returns.check_rec_in_doubt');
1556 RAISE;
1557 END;
1558
1559 ---
1560 IF PG_DEBUG in ('Y', 'C') THEN
1561 arp_standard.debug('After CLAIMS x_rec_in_doubt[x_rid_reason]: ' ||
1562 x_rec_in_doubt || '[' || x_rid_reason || ']');
1563 END IF;
1564 ---
1565 ---
1566 --- Receipt should not be reversed
1567 ---
1568 BEGIN
1569 SELECT 'Y', arp_standard.fnd_message('AR_RID_RECEIPT_REVERSED')
1570 INTO x_rec_in_doubt, x_rid_reason
1571 FROM dual
1572 WHERE
1573 EXISTS
1574 (
1575 SELECT 1
1576 FROM ar_cash_receipts cr1
1577 WHERE cr1.cash_receipt_id = p_cash_receipt_id
1578 AND cr1.reversal_date is not null
1579 );
1580 EXCEPTION
1581 WHEN NO_DATA_FOUND THEN
1582 NULL;
1583 WHEN OTHERS THEN
1584 arp_standard.debug('Unexpected error '||sqlerrm||
1585 ' occurred in arp_process_returns.check_rec_in_doubt');
1586 RAISE;
1587 END;
1588
1589 ---
1590 IF PG_DEBUG in ('Y', 'C') THEN
1591 arp_standard.debug('After DM reverse x_rec_in_doubt[x_rid_reason]: ' ||
1592 x_rec_in_doubt || '[' || x_rid_reason || ']');
1593 END IF;
1594 ---
1595 <<end_of_proc>>
1596 ---
1597 IF PG_DEBUG in ('Y', 'C') THEN
1598 arp_standard.debug('arp_process_RETURNS.check_rec_in_doubt()- ');
1599 END IF;
1600 ---
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 arp_standard.debug('Unexpected error '||sqlerrm||
1604 ' occurred in arp_process_returns.check_rec_in_doubt');
1605 RAISE;
1606 END check_rec_in_doubt;
1607
1608 /*===========================================================================+
1609 | FUNCTION |
1610 | get_on_acct_cm_apps |
1611 | |
1612 | DESCRIPTION |
1613 | This function returns the total number of on-acct cm applications |
1614 | to the given transaction |
1615 | |
1616 | SCOPE - PUBLIC |
1617 | |
1618 | ARGUMENTS : IN: p_customer_trx_id |
1619 | |
1620 | RETURNS : Total number of on-account credit memo applications |
1621 | |
1622 | NOTES : |
1623 | |
1624 | |
1625 | MODIFICATION HISTORY |
1626 | 19-Jun-03 Ramakant Alat Created |
1627 +===========================================================================*/
1628
1629 FUNCTION get_on_acct_cm_apps(p_customer_trx_id IN NUMBER)
1630 RETURN NUMBER IS
1631 l_count NUMBER;
1632 BEGIN
1633 ---
1634 IF PG_DEBUG in ('Y', 'C') THEN
1635 arp_standard.debug('arp_process_RETURNS.get_on_acct_cm_apps()+ ');
1636 arp_standard.debug('p_customer_trx_id :<'
1637 || p_customer_trx_id ||'>');
1638 END IF;
1639 ---
1640 select count(*)
1641 into l_count
1642 from ar_receivable_applications app,
1643 ra_customer_trx oncm
1644 where app.applied_customer_trx_id = p_customer_trx_id
1645 and app.status = 'APP'
1646 and app.application_type = 'CM'
1650 ---
1647 and app.display = 'Y'
1648 and app.customer_trx_id = oncm.customer_trx_id
1649 and oncm.previous_customer_trx_id IS NULL;
1651 IF PG_DEBUG in ('Y', 'C') THEN
1652 arp_standard.debug('arp_process_RETURNS.get_on_acct_cm_apps()- ');
1653 END IF;
1654 ---
1655 RETURN l_count;
1656
1657 EXCEPTION
1658 WHEN OTHERS THEN
1659 arp_standard.debug('Unexpected error '||sqlerrm||
1660 ' occurred in arp_process_returns.get_on_acct_cm_apps');
1661 RAISE;
1662 END get_on_acct_cm_apps;
1663
1664 /*===========================================================================+
1665 | FUNCTION |
1666 | get_neg_inv_apps |
1667 | |
1668 | DESCRIPTION |
1669 | This function returns the total number of negative inv applications |
1670 | across different receipts |
1671 | |
1672 | SCOPE - PUBLIC |
1673 | |
1674 | ARGUMENTS : IN: p_customer_trx_id |
1675 | |
1676 | RETURNS : Total number of negative inv applications |
1677 | |
1678 | NOTES : |
1679 | |
1680 | |
1681 | MODIFICATION HISTORY |
1682 | 29-Oct-03 Ramakant Alat Created |
1683 +===========================================================================*/
1684
1685 FUNCTION get_neg_inv_apps(p_customer_trx_id IN NUMBER)
1686 RETURN NUMBER IS
1687 l_count NUMBER;
1688 BEGIN
1689 ---
1690 IF PG_DEBUG in ('Y', 'C') THEN
1691 arp_standard.debug('arp_process_RETURNS.get_neg_inv_apps()+ ');
1692 arp_standard.debug('p_customer_trx_id :<'
1693 || p_customer_trx_id ||'>');
1694 END IF;
1695 ---
1696 select count(*)
1697 into l_count
1698 from ar_receivable_applications app
1699 where app.applied_customer_trx_id = p_customer_trx_id
1700 and app.status = 'APP'
1701 and app.application_type = 'CASH'
1702 and app.display = 'Y'
1703 and app.amount_applied < 0;
1704 ---
1705 IF PG_DEBUG in ('Y', 'C') THEN
1706 arp_standard.debug('arp_process_RETURNS.get_neg_inv_apps()- ');
1707 END IF;
1708 ---
1709 RETURN l_count;
1710
1711 EXCEPTION
1712 WHEN OTHERS THEN
1713 arp_standard.debug('Unexpected error '||sqlerrm||
1714 ' occurred in arp_process_returns.get_neg_inv_apps');
1715 RAISE;
1716 END get_neg_inv_apps;
1717
1718
1719 /*===========================================================================+
1720 | FUNCTION |
1721 | get_llca_apps |
1722 | |
1723 | DESCRIPTION |
1724 | This function checks if there exists a Line Level Cash Applications |
1725 | to the given transaction |
1726 | |
1727 | SCOPE - PUBLIC |
1728 | |
1729 | ARGUMENTS : IN: p_customer_trx_id |
1730 | |
1731 | RETURNS : Total of LLCA |
1732 | |
1733 | NOTES : |
1734 | |
1735 | |
1736 | MODIFICATION HISTORY |
1737 | 29-Dec-05 Gyanajyothi G Created |
1738 +===========================================================================*/
1739 FUNCTION get_llca_apps(p_customer_trx_id IN NUMBER)
1740 RETURN NUMBER IS
1741 l_count NUMBER;
1742 BEGIN
1743 ---
1744 IF PG_DEBUG in ('Y', 'C') THEN
1745 arp_standard.debug('arp_process_RETURNS.get_llca_apps()+ ');
1746 arp_standard.debug('p_customer_trx_id :<'
1747 || p_customer_trx_id ||'>');
1748 END IF;
1749 ---
1750 select count(*)
1751 into l_count
1752 from ar_activity_details aad,
1753 ra_customer_trx_lines lines
1754 where
1755 lines.customer_trx_id = p_customer_trx_id
1756 and nvl(aad.CURRENT_ACTIVITY_FLAG,'Y') = 'Y'
1760 IF PG_DEBUG in ('Y', 'C') THEN
1757 and aad.customer_trx_line_id = lines.customer_trx_line_id;
1758
1759 ---
1761 arp_standard.debug('arp_process_RETURNS.get_llca_apps()- ');
1762 END IF;
1763 ---
1764 RETURN l_count;
1765
1766 EXCEPTION
1767 WHEN OTHERS THEN
1768 arp_standard.debug('Unexpected error '||sqlerrm||
1769 ' occurred in arp_process_returns.get_llca_apps');
1770 RAISE;
1771 END get_llca_apps;
1772
1773
1774
1775 /*===========================================================================+
1776 | PROCEDURE |
1777 | populate_dff_and_gdf |
1778 | |
1779 | DESCRIPTION |
1780 | This procedure populates the Global DFF and DFF from the old |
1781 | record |
1782 | |
1783 | SCOPE - PUBLIC |
1784 | |
1785 | ARGUMENTS : IN : p_ra_rec |
1786 | OUT : x_dff_rec |
1787 | x_gdf_rec |
1788 | |
1789 | |
1790 | NOTES : |
1791 | |
1792 | |
1793 | MODIFICATION HISTORY |
1794 | 27-Jul-03 Ramakant Alat Created |
1795 +===========================================================================*/
1796
1797 PROCEDURE populate_dff_and_gdf(p_ra_rec IN ar_receivable_applications%rowtype,
1798 x_dff_rec OUT NOCOPY
1799 ar_receipt_api_pub.attribute_rec_type,
1800 x_gdf_rec OUT NOCOPY
1801 ar_receipt_api_pub.global_attribute_rec_type)
1802 AS
1803 BEGIN
1804 ---
1805 IF PG_DEBUG in ('Y', 'C') THEN
1806 arp_standard.debug('arp_process_RETURNS.populate_dff_and_gdf()+ ');
1807 END IF;
1808 ---
1809 x_dff_rec.attribute_category:=p_ra_rec.attribute_category;
1810 x_dff_rec.attribute1 :=p_ra_rec.attribute1;
1811 x_dff_rec.attribute2 :=p_ra_rec.attribute2;
1812 x_dff_rec.attribute3 :=p_ra_rec.attribute3;
1813 x_dff_rec.attribute4 :=p_ra_rec.attribute4;
1814 x_dff_rec.attribute5 :=p_ra_rec.attribute5;
1815 x_dff_rec.attribute6 :=p_ra_rec.attribute6;
1816 x_dff_rec.attribute7 :=p_ra_rec.attribute7;
1817 x_dff_rec.attribute8 :=p_ra_rec.attribute8;
1818 x_dff_rec.attribute9 :=p_ra_rec.attribute9;
1819 x_dff_rec.attribute10 :=p_ra_rec.attribute10;
1820 x_dff_rec.attribute11 :=p_ra_rec.attribute11;
1821 x_dff_rec.attribute12 :=p_ra_rec.attribute12;
1822 x_dff_rec.attribute13 :=p_ra_rec.attribute13;
1823 x_dff_rec.attribute14 :=p_ra_rec.attribute14;
1824 x_dff_rec.attribute15 :=p_ra_rec.attribute15;
1825 ---
1826 ---
1827 x_gdf_rec.global_attribute_category :=p_ra_rec.global_attribute_category ;
1828 x_gdf_rec.global_attribute1 :=p_ra_rec.global_attribute1;
1829 x_gdf_rec.global_attribute2 :=p_ra_rec.global_attribute2;
1830 x_gdf_rec.global_attribute3 :=p_ra_rec.global_attribute3;
1831 x_gdf_rec.global_attribute4 :=p_ra_rec.global_attribute4;
1832 x_gdf_rec.global_attribute5 :=p_ra_rec.global_attribute5;
1833 x_gdf_rec.global_attribute6 :=p_ra_rec.global_attribute6;
1834 x_gdf_rec.global_attribute7 :=p_ra_rec.global_attribute7;
1835 x_gdf_rec.global_attribute8 :=p_ra_rec.global_attribute8;
1836 x_gdf_rec.global_attribute9 :=p_ra_rec.global_attribute9;
1837 x_gdf_rec.global_attribute10 :=p_ra_rec.global_attribute10;
1838 x_gdf_rec.global_attribute11 :=p_ra_rec.global_attribute11;
1839 x_gdf_rec.global_attribute12 :=p_ra_rec.global_attribute12;
1840 x_gdf_rec.global_attribute13 :=p_ra_rec.global_attribute13;
1841 x_gdf_rec.global_attribute14 :=p_ra_rec.global_attribute14;
1842 x_gdf_rec.global_attribute15 :=p_ra_rec.global_attribute15;
1843 x_gdf_rec.global_attribute16 :=p_ra_rec.global_attribute16;
1844 x_gdf_rec.global_attribute17 :=p_ra_rec.global_attribute17;
1845 x_gdf_rec.global_attribute18 :=p_ra_rec.global_attribute18;
1846 x_gdf_rec.global_attribute19 :=p_ra_rec.global_attribute19;
1847 x_gdf_rec.global_attribute20 :=p_ra_rec.global_attribute20;
1848 ---
1849 ---
1850 IF PG_DEBUG in ('Y', 'C') THEN
1851 arp_standard.debug('arp_process_RETURNS.populate_dff_and_gdf()- ');
1852 END IF;
1853 ---
1854 EXCEPTION
1855 WHEN OTHERS THEN
1856 arp_standard.debug('Unexpected error '||sqlerrm||
1857 ' occurred in arp_process_returns.populate_dff_and_gdf');
1858 RAISE;
1862 /*===========================================================================+
1859 END populate_dff_and_gdf;
1860
1861
1863 | FUNCTION |
1864 | get_amount_applied |
1865 | |
1866 | DESCRIPTION |
1867 | This function returns the amount applied by receipts for a given |
1868 | invoice for requested bucket |
1869 | |
1870 | SCOPE - PUBLIC |
1871 | |
1872 | ARGUMENTS : IN: p_customer_trx_id |
1873 | IN: p_line_type |
1874 | |
1875 | RETURNS : amount applied for the given bucket by receipts |
1876 | |
1877 | NOTES : |
1878 | |
1879 | |
1880 | MODIFICATION HISTORY |
1881 | 26-Jul-03 Ramakant Alat Created |
1882 +===========================================================================*/
1883
1884 FUNCTION get_amount_applied(p_customer_trx_id IN NUMBER,
1885 p_line_type IN VARCHAR2)
1886 RETURN NUMBER IS
1887
1888 l_total_amount ar_receivable_applications.amount_applied%type:=0;
1889 l_amt_app_rec amt_app_type;
1890
1891 l_line_amount ar_receivable_applications.amount_applied%type:=0;
1892 l_tax_amount ar_receivable_applications.amount_applied%type:=0;
1893 l_frt_amount ar_receivable_applications.amount_applied%type:=0;
1894 l_charges_amount ar_receivable_applications.amount_applied%type:=0;
1895 l_applied_amount ar_receivable_applications.amount_applied%type:=0;
1896
1897 BEGIN
1898 ---
1899 IF PG_DEBUG in ('Y', 'C') THEN
1900 arp_standard.debug('arp_process_RETURNS.get_amount_applied()+ ');
1901 arp_standard.debug('Customer Trx Id : ' || p_customer_trx_id);
1902 arp_standard.debug('p_line_type : ' || p_line_type);
1903 END IF;
1904 --
1905 -- Adjust amount applied iff invoice is in the list created during validation
1906 --
1907 IF inv_info.EXISTS(p_customer_trx_id) THEN
1908 --
1909 IF amt_app_tab.EXISTS(p_customer_trx_id) THEN
1910 --
1911 IF PG_DEBUG in ('Y', 'C') THEN
1912 arp_standard.debug('Cache Hit...');
1913 END IF;
1914 --
1915 null;
1916 --
1917 ELSE
1918 --
1919 IF PG_DEBUG in ('Y', 'C') THEN
1920 arp_standard.debug('Database Hit...');
1921 END IF;
1922 --
1923 --
1924 --
1925 SELECT
1926 SUM(NVL(line_applied, 0) + NVL(line_ediscounted, 0)),
1927 SUM(NVL(tax_applied, 0) + NVL(tax_ediscounted, 0)),
1928 SUM(NVL(freight_applied, 0) + NVL(freight_ediscounted, 0)),
1929 SUM(NVL(receivables_charges_applied, 0)
1930 + NVL(charges_ediscounted, 0)),
1931 SUM(NVL(amount_applied, 0) + NVL(earned_discount_taken, 0))
1932 INTO
1933 l_line_amount,
1934 l_tax_amount,
1935 l_frt_amount,
1936 l_charges_amount,
1937 l_applied_amount
1938 FROM
1939 ar_receivable_applications
1940 WHERE
1941 applied_customer_trx_id = p_customer_trx_id
1942 AND application_type = 'CASH' -- Consider only receipt applications
1943 AND display = 'Y';
1944 --
1945 --
1946 amt_app_tab(p_customer_trx_id).line_applied := NVL(l_line_amount, 0);
1947 amt_app_tab(p_customer_trx_id).tax_applied := NVL(l_tax_amount, 0);
1948 amt_app_tab(p_customer_trx_id).freight_applied := NVL(l_frt_amount, 0);
1949 amt_app_tab(p_customer_trx_id).charges_applied := NVL(l_charges_amount, 0);
1950 amt_app_tab(p_customer_trx_id).amount_applied := NVL(l_applied_amount, 0);
1951 --
1952 --
1953 END IF;
1954 --
1955 IF p_line_type = 'LINE' THEN
1956 l_total_amount := amt_app_tab(p_customer_trx_id).line_applied;
1957 ELSIF p_line_type = 'TAX' THEN
1958 l_total_amount := amt_app_tab(p_customer_trx_id).tax_applied;
1959 ELSIF p_line_type = 'FREIGHT' THEN
1960 l_total_amount := amt_app_tab(p_customer_trx_id).freight_applied;
1961 ELSIF p_line_type = 'CHARGES' THEN
1962 l_total_amount := amt_app_tab(p_customer_trx_id).charges_applied;
1963 ELSE
1964 l_total_amount := amt_app_tab(p_customer_trx_id).amount_applied;
1965 END IF;
1966 --
1967 ELSE
1968 l_total_amount := 0;
1969 END IF;
1970 ---
1971 IF PG_DEBUG in ('Y', 'C') THEN
1972 arp_standard.debug('Total Amount : ' || l_total_amount);
1973 arp_standard.debug('arp_process_RETURNS.get_amount_applied()- ');
1974 END IF;
1975 --
1976
1977 RETURN l_total_amount;
1978
1979 EXCEPTION
1980 WHEN OTHERS THEN
1981 arp_standard.debug('Unexpected error '||sqlerrm||
1982 ' occurred in arp_process_returns.get_amount_applied');
1983 RAISE;
1984 END get_amount_applied;
1985
1986 BEGIN
1987 initialize_globals;
1988
1989 END arp_process_RETURNS;