[Home] [Help]
PACKAGE BODY: APPS.AR_PREPAYMENTS
Source
1 PACKAGE BODY AR_PREPAYMENTS AS
2 /* $Header: ARPREPYB.pls 120.27 2010/12/17 11:55:15 manishri ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_PREPAYMENTS';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14
15
16
17 /*========================================================================
18 | Prototype Declarations Procedures
19 *=======================================================================*/
20
21
22 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
23
24
25 /*========================================================================
26 | Prototype Declarations Functions
27 *=======================================================================*/
28 /*===========================================================================+
29 | PORCEDURE |
30 | check_rec_in_doubt |
31 | |
32 | DESCRIPTION |
33 | This function checks if given receipt is doubt |
34 | Given receipt can be in doubt for any of the following reasons |
35 | . If receipt is a CC receipt and is not remitted |
36 | . If receipt has Special application of Claims Investigation |
37 | . If the receipt is Debit Memo reversed |
38 | |
39 | SCOPE - PUBLIC |
40 | |
41 | ARGUMENTS : IN : p_cash_receipt_id |
42 | |
43 | : OUT : x_rec_in_doubt (Y/N) |
44 | OUT : x_rid_reason |
45 | |
46 | NOTES : |
47 | This is same as arp_process_returns.check_rec_in_doubt |
48 | |
49 | MODIFICATION HISTORY |
50 | 22-MAR-04 Jyoti Pandey created |
51 | |
52 +===========================================================================*/
53 PROCEDURE check_rec_in_doubt(p_cash_receipt_id IN NUMBER,
54 x_rec_in_doubt OUT NOCOPY VARCHAR2,
55 x_rid_reason OUT NOCOPY VARCHAR2) IS
56 BEGIN
57 ---
58 IF PG_DEBUG in ('Y', 'C') THEN
59 arp_standard.debug('ar_prepayments.check_rec_in_doubt()+ ');
60 END IF;
61 ---
62 x_rec_in_doubt := 'N';
63 x_rid_reason := null;
64 ---
65 --- For CC receipts, receipt should be remitted
66 ---
67 BEGIN
68 SELECT 'Y', arp_standard.fnd_message('AR_RID_NOT_REMITTED_OR_CLEARED')
69 INTO x_rec_in_doubt, x_rid_reason
70 FROM dual
71 WHERE
72 (
73 NOT EXISTS
74 (
75 SELECT 1
76 FROM AR_CASH_RECEIPT_HISTORY crh
77 WHERE crh.cash_receipt_id = p_cash_receipt_id
78 AND crh.status IN ('REMITTED', 'CLEARED')
79 )
80 );
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 NULL;
84 WHEN OTHERS THEN
85 arp_standard.debug('Unexpected error '||sqlerrm||
86 ' occurred in ar_prepayments.check_rec_in_doubt');
87 RAISE;
88 END;
89
90 ---
91 IF PG_DEBUG in ('Y', 'C') THEN
92 arp_standard.debug('After REFUND x_rec_in_doubt[x_rid_reason]: ' || x_rec_in_doubt ||
93 '[' || x_rid_reason || ']');
94 END IF;
95 ---
96 ---
97 --- There should not be any Claims Investigation or CB special application
98 ---
99 BEGIN
100 SELECT 'Y', arp_standard.fnd_message('AR_RID_CLAIM_OR_CB_APP_EXISTS')
101 INTO x_rec_in_doubt, x_rid_reason
102 FROM dual
103 WHERE
104 EXISTS
105 (
106 SELECT 1
107 FROM ar_receivable_applications ra
108 WHERE ra.cash_receipt_id = p_cash_receipt_id
109 AND applied_payment_schedule_id IN (-4, -5)
110 AND display = 'Y'
111 );
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 NULL;
115 WHEN OTHERS THEN
116 arp_standard.debug('Unexpected error '||sqlerrm||
117 ' occurred in ar_prepayments.check_rec_in_doubt');
118 RAISE;
119 END;
120
121 ---
122 IF PG_DEBUG in ('Y', 'C') THEN
123 arp_standard.debug('After CLAIMS x_rec_in_doubt[x_rid_reason]: ' ||
124 x_rec_in_doubt || '[' || x_rid_reason || ']');
125 END IF;
126 ---
127 ---
128 --- Receipt should not be reversed
129 ---
130 BEGIN
131 SELECT 'Y', arp_standard.fnd_message('AR_RID_RECEIPT_REVERSED')
132 INTO x_rec_in_doubt, x_rid_reason
133 FROM dual
134 WHERE
135 EXISTS
136 (
137 SELECT 1
138 FROM ar_cash_receipts cr1
139 WHERE cr1.cash_receipt_id = p_cash_receipt_id
140 AND cr1.reversal_date is not null
141 );
142 EXCEPTION
143 WHEN NO_DATA_FOUND THEN
144 NULL;
145 WHEN OTHERS THEN
146 arp_standard.debug('Unexpected error '||sqlerrm||
147 ' occurred in ar_prepayments.check_rec_in_doubt');
148 RAISE;
149 END;
150
151 ---
152 IF PG_DEBUG in ('Y', 'C') THEN
153 arp_standard.debug('After DM reverse x_rec_in_doubt[x_rid_reason]: ' ||
154 x_rec_in_doubt || '[' || x_rid_reason || ']');
155 END IF;
156 ---
157 <<end_of_proc>>
158 ---
159 IF PG_DEBUG in ('Y', 'C') THEN
160 arp_standard.debug('ar_prepayments.check_rec_in_doubt()- ');
161 END IF;
162 ---
163 EXCEPTION
164 WHEN OTHERS THEN
165 arp_standard.debug('Unexpected error '||sqlerrm||
166 ' occurred in arp_process_returns.check_rec_in_doubt');
167 RAISE;
168 END check_rec_in_doubt;
169
170
171 PROCEDURE Process_Prepayments(
172 -- Standard API parameters.
173 p_api_version IN NUMBER,
174 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
175 p_commit IN VARCHAR2 := FND_API.G_FALSE,
176 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
177 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE DEFAULT NULL,
178 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
179 p_receivable_application_id IN ar_receivable_applications.
180 receivable_application_id%TYPE DEFAULT NULL,
181 p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE,
182 p_refund_amount IN ar_receivable_applications.amount_applied%TYPE
183 DEFAULT NULL,
184 p_refund_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
185 p_refund_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
186 p_ussgl_transaction_code IN ar_receivable_applications.
187 ussgl_transaction_code%TYPE DEFAULT NULL,
188 p_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
189 DEFAULT ar_receipt_api_pub.attribute_rec_const,
190 -- ******* Global Flexfield parameters *******
191 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
192 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
193 p_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
194
195 --Multiple Prapayments project, refund of type Credit card or on account
196 p_refund_type IN VARCHAR2 DEFAULT NULL,
197
198 x_return_status OUT NOCOPY VARCHAR2,
199 x_msg_count OUT NOCOPY NUMBER,
200 x_msg_data OUT NOCOPY VARCHAR2,
201 p_prepay_application_id OUT NOCOPY ar_receivable_applications.
202 receivable_application_id%TYPE
203 ) IS
204
205 l_ra_rec ar_receivable_applications%ROWTYPE;
206 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
207 l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type;
208
209 l_cash_receipt_id NUMBER;
210 l_applied_ps_id NUMBER;
211 l_receivable_application_id NUMBER;
212 l_receivables_trx_id NUMBER;
213 l_apply_gl_date DATE;
214 l_def_return_status VARCHAR2(1);
215 l_def_activity_return_status VARCHAR2(1);
216 l_payment_type_return_status VARCHAR2(1);
217 l_val_return_status VARCHAR2(1);
218 l_reapply_amount ar_receivable_applications.amount_applied%TYPE;
219 l_payment_set_id ar_receivable_applications.payment_set_id%TYPE;
220 l_refund_amount NUMBER;
221 l_application_ref_type ar_receivable_applications.application_ref_type%TYPE;
222 l_application_ref_id ar_receivable_applications.application_ref_id%TYPE;
223 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
224 l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE;
225
226 --Multiple Prepayments project
227 l_rec_in_doubt VARCHAR2(1) := 'N';
228 l_rid_reason VARCHAR2(2000) := ' ';
229 l_comments ar_receivable_applications.comments%TYPE;
230 l_refund_type VARCHAR2(30) := null;
231
232 l_cr_unapp_amount NUMBER; /* Bug fix 3569640 */
233
234 --Bug 3628401
235 l_ra_refund_rec ar_receivable_applications%ROWTYPE;
236 l_on_acc_rec_app_id ar_receivable_applications.receivable_application_id%type;
237 l_actual_refund_amount ar_receivable_applications.amount_applied%TYPE;
238
239 BEGIN
240 IF PG_DEBUG in ('Y', 'C') THEN
241 arp_util.debug(
242 'ar_prepayments.process prepayment (+)');
243 END IF;
244
245 x_return_status := FND_API.G_RET_STS_SUCCESS;
246 l_cash_receipt_id := p_cash_receipt_id;
247 l_receivable_application_id := p_receivable_application_id;
248 l_refund_amount := p_refund_amount;
249 l_receivables_trx_id := p_receivables_trx_id;
250 l_refund_type := p_refund_type;
251
252
253 /*----------------------------------------------------
254 Check if there is enough prepayment amount
255 to refund on the receipt.
256 ----------------------------------------------------*/
257 ar_receipt_val_pvt.validate_prepay_amount(
258 p_receipt_number ,
259 l_cash_receipt_id ,
260 -7,--Prepayment
261 l_receivable_application_id ,
262 l_refund_amount ,
263 l_val_return_status
264 );
265
266 IF PG_DEBUG in ('Y', 'C') THEN
267 arp_util.debug(
268 'Validate Prepaymet amount return status :'||
269 l_val_return_status);
270 END IF;
271
272 ar_receipt_lib_pvt.derive_otheraccount_ids(
273 p_receipt_number ,
274 l_cash_receipt_id ,
275 -7,--Prepayment
276 l_receivable_application_id ,
277 l_apply_gl_date ,
278 l_cr_unapp_amount , /* Bug fix 3569640 */
279 l_def_return_status);
280
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_util.debug(
283 'Derive other accounts ids return status : '||
284 l_def_return_status);
285 END IF;
286
287 /*----------------------------------------------------
288 Credit card refunds
289 -----------------------------------------------------*/
290 IF l_refund_type = 'CREDIT_CARD' THEN
291
292 /*----------------------------------------------------
293 Check the Receipt in doubt scenario
294 -----------------------------------------------------*/
295 check_rec_in_doubt(p_cash_receipt_id => l_cash_receipt_id,
296 x_rec_in_doubt => l_rec_in_doubt,
297 x_rid_reason => l_rid_reason);
298
299
300 IF l_rec_in_doubt = 'Y' then
301 l_refund_type:= 'ON_ACCOUNT';
302
303 IF PG_DEBUG in ('Y', 'C') THEN
304 arp_util.debug('process_prepayments: '||
305 'Receipt is in doubt with following reason: '|| l_rid_reason);
306
307 END IF;
308
309
310 ELSE
311
312 --Default the receivable_trx_id for credit card refund activity
313 ar_receipt_lib_pvt.Default_prepay_cc_activity(
314 'CCREFUND',
315 l_receivables_trx_id,
316 l_def_activity_return_status);
317 IF PG_DEBUG in ('Y', 'C') THEN
318 arp_util.debug(
319 'Default Refund Activity Return status :'||
320 l_def_activity_return_status);
321 END IF;
322
323 END IF; --l_rec_in_doubt
324
325 END IF; ----l_refund_type = CREDIT_CARD
326
327 IF PG_DEBUG in ('Y', 'C') THEN
328 arp_util.debug('process_prepayments: Refund type ' ||l_refund_type);
329 END IF;
330
331
332
333 IF l_val_return_status <> FND_API.G_RET_STS_SUCCESS OR
334 l_def_return_status <> FND_API.G_RET_STS_SUCCESS OR
335 l_def_activity_return_status <> FND_API.G_RET_STS_SUCCESS THEN
336
337 IF PG_DEBUG in ('Y', 'C') THEN
338 arp_util.debug(
339 'Validation or Defaulting Failed' );
340 END IF;
341 x_return_status := FND_API.G_RET_STS_ERROR ;
342
343 END IF;
344
345
346 -- Fetch the details on the unapplied prepayment record.
347 arp_app_pkg.fetch_p(l_receivable_application_id, l_ra_rec );
348
349 /*----------------------------------------------------
350 If the validations passed then
351 Unapply prepayment
352 -----------------------------------------------------*/
353 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
354
355 IF PG_DEBUG in ('Y', 'C') THEN
356 arp_util.debug(
357 ' Calling ar_receipt_api_pub.Unapply_other_account' );
358 END IF;
359
360
361 ar_receipt_api_pub.Unapply_other_account(
362 --Standard API parameters.
363 p_api_version => p_api_version,
364 p_init_msg_list => p_init_msg_list,
365 p_commit => p_commit ,
366 p_validation_level => p_validation_level,
367 x_return_status => x_return_status,
368 x_msg_count => x_msg_count,
369 x_msg_data => x_msg_data,
370 p_receipt_number => p_receipt_number,
371 p_cash_receipt_id => l_cash_receipt_id,
372 p_receivable_application_id => l_receivable_application_id,
373 p_reversal_gl_date => p_refund_gl_date
374 );
375
376 IF PG_DEBUG in ('Y', 'C') THEN
377 arp_util.debug(
378 'Unapply Other Account Return status :'||
379 x_return_status);
380 END IF;
381
382 END IF; ---x_return_status = FND_API.G_RET_STS_SUCCESS
383
384
385 /*----------------------------------------------------
386 Make sure there are no errors in unapplication.
387 if there is any error,do not perform refund or
388 reapplication
389 -----------------------------------------------------*/
390 IF x_return_status = FND_API.G_RET_STS_SUCCESS AND
391 l_refund_type = 'CREDIT_CARD' THEN
392
393 IF PG_DEBUG in ('Y', 'C') THEN
394 arp_util.debug(
395 ' Calling ar_receipt_api_pub.Activity_Application' );
396 END IF;
397
398 -- Issue a credit card Refund
399
400 ar_receipt_api_pub.Activity_application(
401 -- Standard API parameters.
402 p_api_version => p_api_version,
403 p_init_msg_list => p_init_msg_list,
404 p_commit => p_commit ,
405 p_validation_level => p_validation_level,
406 x_return_status => x_return_status,
407 x_msg_count => x_msg_count,
408 x_msg_data => x_msg_data,
409 p_cash_receipt_id => p_cash_receipt_id,
410 p_receipt_number => p_receipt_number,
411 p_amount_applied => p_refund_amount,
412 p_applied_payment_schedule_id => -6, --this is for CC Refund
413 p_link_to_customer_trx_id => NULL,
414 p_receivables_trx_id => l_receivables_trx_id,
415 p_apply_date => NVL(p_refund_date,sysdate),
416 p_apply_gl_date => NVL(p_refund_gl_date,sysdate),
417 p_ussgl_transaction_code => p_ussgl_transaction_code,
418 p_attribute_rec => p_attribute_rec,
419 -- ******* Global Flexfield parameters *******
420 p_global_attribute_rec => p_global_attribute_rec,
421 p_comments => p_comments,
422 p_application_ref_type => l_application_ref_type,
423 p_application_ref_id => l_application_ref_id,
424 p_application_ref_num => l_application_ref_num,
425 p_secondary_application_ref_id => l_secondary_application_ref_id,
426 p_receivable_application_id => l_receivable_application_id,
427 p_payment_set_id => l_ra_rec.payment_set_id
428 );
429
430 IF PG_DEBUG in ('Y', 'C') THEN
431 arp_util.debug(
432 'Acticvity application return status :'||
433 x_return_status);
434
435 arp_util.debug('process_prepayments: '||
436 ' Receivable App. ID : '||
437 l_receivable_application_id );
438 END IF;
439
440
441 /*----------------------------------------------------
442 Bug 3628401
443 Fetch the amount_applied for credit card refund
444 application
445 -----------------------------------------------------*/
446 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
447 arp_app_pkg.fetch_p(l_receivable_application_id,
448 l_ra_refund_rec);
449 l_actual_refund_amount := NVL(l_ra_refund_rec.amount_applied,0);
450 END IF;
451
452 /*----------------------------------------------------
453 On Account refund Application
454 -----------------------------------------------------*/
455 ELSIF x_return_status = FND_API.G_RET_STS_SUCCESS AND
456 l_refund_type = 'ON_ACCOUNT' THEN
457
458 /*----------------------------------------------------
459 populate the message if the receipt was supposed
460 be credit card but was placed on Acccount because of
461 receipt in doubt condition
462 -----------------------------------------------------*/
463 if l_rec_in_doubt = 'Y' then
464
465 l_comments := substrb( l_rid_reason, 1, 240);
466
467 else
468 l_comments := substrb( p_comments, 1, 240);
469 end if;
470
471
472 IF PG_DEBUG in ('Y', 'C') THEN
473 arp_util.debug(
474 ' Calling ar_receipt_api_pub.Apply_On_Account' );
475 END IF;
476
477 --put the amount on account
478 ar_receipt_api_pub.Apply_on_account(
479 p_api_version => 1.0,
480 x_return_status => x_return_status,
481 x_msg_count => x_msg_count,
482 x_msg_data => x_msg_data,
483 p_cash_receipt_id => p_cash_receipt_id,
484 p_amount_applied => p_refund_amount,
485 p_comments => l_comments,
486 p_secondary_application_ref_id => l_application_ref_id,
487 p_secondary_app_ref_type => l_application_ref_type,
488 p_secondary_app_ref_num => l_application_ref_num
489 );
490
491 IF PG_DEBUG in ('Y', 'C') THEN
492 arp_util.debug(
493 ' Apply On Account return status: ' || x_return_status );
494 END IF;
495
496 /*----------------------------------------------------
497 Bug 3628401
498 Fetch the amount_applied on On Account refund record
499 -----------------------------------------------------*/
500 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
501
502 l_on_acc_rec_app_id :=
503 ar_receipt_api_pub.g_apply_on_account_out_rec.receivable_application_id;
504 arp_app_pkg.fetch_p(l_on_acc_rec_app_id, l_ra_refund_rec );
505 l_actual_refund_amount := NVL(l_ra_refund_rec.amount_applied,0);
506 END IF;
507
508 END IF ; --l_refund_type
509
510
511 /*----------------------------------------------------
512 Bug 3628401
513 If the refund application was successful then evaluate
514 whether any amount needs to be applied back on Prepayment
515 -----------------------------------------------------*/
516 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
517
518 l_reapply_amount := NVL(l_ra_rec.amount_applied,0)
519 - NVL(l_actual_refund_amount,0);
520
521 IF l_reapply_amount > 0 THEN
522
523 IF PG_DEBUG in ('Y', 'C') THEN
524 arp_util.debug(
525 ' Amount to be re-applied back on Prepayment: '||
526 l_reapply_amount );
527 END IF;
528
529
530 --If the refund routine is called from sweeper program or any
531 --other routine then we need to take the payment set id from
532 --the old prepayment record and store in the new prepayment
533 --re-application record.
534
535 l_payment_set_id := l_ra_rec.payment_set_id;
536
537 --Assign atributes
538 l_attribute_rec.attribute_category := l_ra_rec.attribute_category;
539 l_attribute_rec.attribute1 := l_ra_rec.attribute1;
540 l_attribute_rec.attribute2 := l_ra_rec.attribute2;
541 l_attribute_rec.attribute3 := l_ra_rec.attribute3;
542 l_attribute_rec.attribute4 := l_ra_rec.attribute4;
543 l_attribute_rec.attribute5 := l_ra_rec.attribute5;
544 l_attribute_rec.attribute6 := l_ra_rec.attribute6;
545 l_attribute_rec.attribute7 := l_ra_rec.attribute7;
546 l_attribute_rec.attribute8 := l_ra_rec.attribute8;
547 l_attribute_rec.attribute9 := l_ra_rec.attribute9;
548 l_attribute_rec.attribute10 := l_ra_rec.attribute10;
549 l_attribute_rec.attribute11 := l_ra_rec.attribute11;
550 l_attribute_rec.attribute12 := l_ra_rec.attribute12;
551 l_attribute_rec.attribute13 := l_ra_rec.attribute13;
552 l_attribute_rec.attribute14 := l_ra_rec.attribute14;
553 l_attribute_rec.attribute15 := l_ra_rec.attribute15;
554
555 l_global_attribute_rec.global_attribute_category :=
556 l_ra_rec.global_attribute_category;
557 l_global_attribute_rec.global_attribute1 :=
558 l_ra_rec.global_attribute1;
559 l_global_attribute_rec.global_attribute2 :=
560 l_ra_rec.global_attribute2;
561 l_global_attribute_rec.global_attribute3 :=
562 l_ra_rec.global_attribute3;
563 l_global_attribute_rec.global_attribute4 :=
564 l_ra_rec.global_attribute4;
565 l_global_attribute_rec.global_attribute5 :=
566 l_ra_rec.global_attribute5;
567 l_global_attribute_rec.global_attribute6 :=
568 l_ra_rec.global_attribute6;
569 l_global_attribute_rec.global_attribute7 :=
570 l_ra_rec.global_attribute7;
571 l_global_attribute_rec.global_attribute8 :=
572 l_ra_rec.global_attribute8;
573 l_global_attribute_rec.global_attribute9 :=
574 l_ra_rec.global_attribute9;
575 l_global_attribute_rec.global_attribute10 :=
576 l_ra_rec.global_attribute10;
577 l_global_attribute_rec.global_attribute11 :=
578 l_ra_rec.global_attribute11;
579 l_global_attribute_rec.global_attribute12 :=
580 l_ra_rec.global_attribute12;
581 l_global_attribute_rec.global_attribute13 :=
582 l_ra_rec.global_attribute13;
583 l_global_attribute_rec.global_attribute14 :=
584 l_ra_rec.global_attribute14;
585 l_global_attribute_rec.global_attribute15 :=
586 l_ra_rec.global_attribute15;
587 l_global_attribute_rec.global_attribute16 :=
588 l_ra_rec.global_attribute16;
589 l_global_attribute_rec.global_attribute17 :=
590 l_ra_rec.global_attribute17;
591 l_global_attribute_rec.global_attribute18 :=
592 l_ra_rec.global_attribute18;
593 l_global_attribute_rec.global_attribute19 :=
594 l_ra_rec.global_attribute19;
595 l_global_attribute_rec.global_attribute20 :=
596 l_ra_rec.global_attribute20;
597
598 /*----------------------------------------------------
599 Reapply to prepayment
600 -----------------------------------------------------*/
601 ar_receipt_api_pub.Apply_other_account(
602 --Standard API parameters.
603 p_api_version => p_api_version,
604 p_init_msg_list => p_init_msg_list,
605 p_commit => p_commit ,
606 p_validation_level => p_validation_level,
607 x_return_status => x_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data,
610 p_receivable_application_id => p_prepay_application_id, --OUT
611 --Receipt application parameters.
612 p_cash_receipt_id => p_cash_receipt_id,
613 p_receipt_number => p_receipt_number,
614 p_amount_applied => l_reapply_amount,
615 p_receivables_trx_id => l_ra_rec.receivables_trx_id,
616 p_applied_payment_schedule_id => -7,
617 p_apply_date => p_refund_date,
618 p_apply_gl_date => p_refund_gl_date,
619 p_ussgl_transaction_code => l_ra_rec.ussgl_transaction_code,
620 p_application_ref_type => l_ra_rec.application_ref_type,
621 p_application_ref_id => l_ra_rec.application_ref_id,
622 p_application_ref_num => l_ra_rec.application_ref_num,
623 p_secondary_application_ref_id => l_ra_rec.secondary_application_ref_id,
624 p_payment_set_id => l_payment_set_id,
625 p_attribute_rec => l_attribute_rec,
626 -- ******* Global Flexfield parameters *******
627 p_global_attribute_rec => l_global_attribute_rec,
628 p_comments => l_ra_rec.comments
629 );
630
631 IF PG_DEBUG in ('Y', 'C') THEN
632 arp_util.debug(
633 'Prepay Application ID :'||
634 p_prepay_application_id);
635 arp_util.debug(
636 'Other account application return status :'||
637 x_return_status);
638 END IF;
639
640
641 END IF; --l_reapply_amount
642
643
644 END IF; --x_return_status
645
646 /*----------------------------------------------------
647 Error Handling Finally
648 -----------------------------------------------------*/
649 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
650
651 ---Bug 3628401 removed unexpected_error;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
654 p_count => x_msg_count,
655 p_data => x_msg_data
656 );
657 RETURN;
658 END IF;
659
660 IF PG_DEBUG in ('Y', 'C') THEN
661 arp_util.debug( 'ar_prepayments.process prepayment (-)');
662 END IF;
663
664 EXCEPTION
665
666 WHEN OTHERS THEN
667 x_return_status := FND_API.G_RET_STS_ERROR;
668 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
669 p_count => x_msg_count,
670 p_data => x_msg_data);
671
672 IF PG_DEBUG in ('Y', 'C') THEN
673 arp_util.debug('EXCEPTION :ar_prepayments.process_prepayments :'||SQLERRM);
674 END IF;
675
676 END Process_Prepayments;
677
678 /*=======================================================================
679 | PUBLIC Procedure Create_Prepayment
680 |
681 | DESCRIPTION
682 | Create prepayment receipt and put it on prepayment
683 | ----------------------------------------
684 |
685 | PSEUDO CODE/LOGIC
686 |
687 | PARAMETERS
688 |
689 |
690 | RETURNS
691 | nothing
692 |
693 | KNOWN ISSUES
694 |
695 |
696 |
697 | NOTES
698 |
699 |
700 |
701 | MODIFICATION HISTORY
702 | Date Author Description of Changes
703 | 10-SEP-2001 S Nambiar Created
704 | 10-MAR-2002 S Nambiar Bug 2315864 - Validate customer
705 | bank account id,raise error if
706 | NULL is passed
707 | 08-AUG-2003 J Pandey All the code has ben moved to the new
708 | prepayment API AR_PREPAYMNTS_PUB
709 *=======================================================================*/
710 PROCEDURE Create_Prepayment(
711 -- Standard API parameters.
712 p_api_version IN NUMBER,
713 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
714 p_commit IN VARCHAR2 := FND_API.G_FALSE,
715 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_msg_count OUT NOCOPY NUMBER,
718 x_msg_data OUT NOCOPY VARCHAR2,
719 -- Receipt info. parameters
720 p_usr_currency_code IN VARCHAR2 DEFAULT NULL, --the translated currency code
721 p_currency_code IN ar_cash_receipts.currency_code%TYPE DEFAULT NULL,
722 p_usr_exchange_rate_type IN VARCHAR2 DEFAULT NULL,
723 p_exchange_rate_type IN ar_cash_receipts.exchange_rate_type%TYPE DEFAULT NULL,
724 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE DEFAULT NULL,
725 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE DEFAULT NULL,
726 p_amount IN ar_cash_receipts.amount%TYPE,
727 p_factor_discount_amount IN ar_cash_receipts.factor_discount_amount%TYPE DEFAULT NULL,
728
729 --Bug 3106245
730 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE DEFAULT NULL,
731
732 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
733 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
734 p_maturity_date IN DATE DEFAULT NULL,
735 p_postmark_date IN DATE DEFAULT NULL,
736 p_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
737 p_customer_name IN hz_parties.party_name%TYPE DEFAULT NULL,
738 p_customer_number IN hz_cust_accounts.account_number%TYPE DEFAULT NULL,
739 p_customer_bank_account_id IN ar_cash_receipts.customer_bank_account_id%TYPE DEFAULT NULL,
740 p_customer_bank_account_num IN ap_bank_accounts.bank_account_num%TYPE DEFAULT NULL,
741 p_customer_bank_account_name IN ap_bank_accounts.bank_account_name%TYPE DEFAULT NULL,
742 p_location IN hz_cust_site_uses.location%TYPE DEFAULT NULL,
743 p_customer_site_use_id IN hz_cust_site_uses.site_use_id%TYPE DEFAULT NULL,
744 p_customer_receipt_reference IN ar_cash_receipts.customer_receipt_reference%TYPE DEFAULT NULL,
745 p_override_remit_account_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
746 p_remittance_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%TYPE DEFAULT NULL,
747 p_remittance_bank_account_num IN ce_bank_accounts.bank_account_num%TYPE DEFAULT NULL,
748 p_remittance_bank_account_name IN ce_bank_accounts.bank_account_name%TYPE DEFAULT NULL,
749 p_deposit_date IN ar_cash_receipts.deposit_date%TYPE DEFAULT NULL,
750 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE DEFAULT NULL,
751 p_receipt_method_name IN ar_receipt_methods.name%TYPE DEFAULT NULL,
752 p_doc_sequence_value IN NUMBER DEFAULT NULL,
753 p_ussgl_transaction_code IN ar_cash_receipts.ussgl_transaction_code%TYPE DEFAULT NULL,
754 p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE DEFAULT NULL,
755 p_called_from IN VARCHAR2 DEFAULT NULL,
756 p_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
757 DEFAULT ar_receipt_api_pub.attribute_rec_const,
758 -- ******* Global Flexfield parameters *******
759 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
760 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
761 p_receipt_comments IN VARCHAR2 DEFAULT NULL,
762 -- *** Notes Receivable Additional Information ***
763 p_issuer_name IN ar_cash_receipts.issuer_name%TYPE DEFAULT NULL,
764 p_issue_date IN ar_cash_receipts.issue_date%TYPE DEFAULT NULL,
765 p_issuer_bank_branch_id IN ar_cash_receipts.issuer_bank_branch_id%TYPE DEFAULT NULL,
766 -- ** OUT NOCOPY variables for Creating receipt
767 p_cr_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
768 -- Receipt application parameters
769 p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
770 p_amount_applied IN ar_receivable_applications.amount_applied%TYPE DEFAULT NULL,
771 p_application_ref_type IN VARCHAR2 DEFAULT NULL,
772 p_application_ref_id IN OUT NOCOPY NUMBER ,
773 p_application_ref_num IN OUT NOCOPY VARCHAR2 ,
774 p_secondary_application_ref_id IN OUT NOCOPY NUMBER ,
775 p_receivable_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE DEFAULT NULL,
776 p_amount_applied_from IN ar_receivable_applications.amount_applied_from%TYPE DEFAULT NULL,
777 p_apply_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
778 p_apply_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
779 app_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
780 p_show_closed_invoices IN VARCHAR2 DEFAULT 'FALSE',
781 p_move_deferred_tax IN VARCHAR2 DEFAULT 'Y',
782 app_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
783 DEFAULT ar_receipt_api_pub.attribute_rec_const,
784 -- ******* Global Flexfield parameters *******
785 app_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
786 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
787 app_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
788 -- processor such as iPayments
789 p_payment_server_order_num IN OUT NOCOPY ar_cash_receipts.payment_server_order_num%TYPE,
790 p_approval_code IN OUT NOCOPY ar_cash_receipts.approval_code%TYPE,
791 --- Bug: 3220078 Change the p_call_payment_processor to TRUE ---
792 p_call_payment_processor IN VARCHAR2 DEFAULT FND_API.G_TRUE,
793
794 p_payment_response_error_code OUT NOCOPY VARCHAR2,
795 -- OUT NOCOPY parameter for the Application
796 p_receivable_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
797 p_payment_set_id IN OUT NOCOPY NUMBER,
798 p_org_id IN NUMBER DEFAULT NULL,
799 p_payment_trxn_extension_id IN ar_cash_receipts.payment_trxn_extension_id%TYPE
800 ) IS
801
802 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
803 l_org_id NUMBER;
804 l_org_return_status VARCHAR2(1);
805
806
807 BEGIN
808
809 IF PG_DEBUG in ('Y', 'C') THEN
810 arp_standard.debug('ar_prepayments.Create_Prepayment ()+');
811 END IF;
812
813 /*--------------------------------------------------------------+
814 | Initialize message list if p_init_msg_list is set to TRUE |
815 +--------------------------------------------------------------*/
816 IF FND_API.to_Boolean( p_init_msg_list )
817 THEN
818 FND_MSG_PUB.initialize;
819 END IF;
820 /* SSA change */
821 l_org_id := p_org_id;
822 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
823 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
824 p_return_status =>l_org_return_status);
825 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
826 x_return_status := FND_API.G_RET_STS_ERROR;
827 ELSE
828
829
830 --Initialize the return status
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 --get the value of receipt_number into local variable
834 l_receipt_number := p_receipt_number;
835
836 IF PG_DEBUG in ('Y', 'C') THEN
837 arp_standard.debug('ar_prepayments.Create_Prepayment ()+'|| p_org_id);
838 arp_util.debug('Create_Prepayment: payment_trxn_extension_id' || to_char(p_payment_trxn_extension_id) );
839 END IF;
840
841
842 ----Call the AR_PREPAYMENTS_PUB.Create_Prepayment API----
843 AR_PREPAYMENTS_PUB.Create_Prepayment(
844 -- Standard API parameters.
845 p_api_version ,
846 p_init_msg_list ,
847 p_commit ,
848 p_validation_level ,
849
850 x_return_status,
851 x_msg_count ,
852 x_msg_data ,
853
854 -- Receipt info. parameters
855 p_usr_currency_code ,
856 p_currency_code ,
857 p_usr_exchange_rate_type ,
858 p_exchange_rate_type ,
859 p_exchange_rate ,
860 p_exchange_rate_date ,
861 p_amount ,
862 p_factor_discount_amount ,
863
864 ---Bug 3106245 pass the local variable value to IN OUT
865 l_receipt_number ,
866
867 p_receipt_date ,
868 p_gl_date ,
869 p_maturity_date ,
870 p_postmark_date ,
871 p_customer_id ,
872 p_customer_name ,
873 p_customer_number ,
874 p_customer_bank_account_id ,
875 p_customer_bank_account_num ,
876 p_customer_bank_account_name ,
877 p_location ,
878 p_customer_site_use_id ,
879 p_customer_receipt_reference ,
880 p_override_remit_account_flag ,
881 p_remittance_bank_account_id ,
882 p_remittance_bank_account_num ,
883 p_remittance_bank_account_name ,
884 p_deposit_date ,
885 p_receipt_method_id ,
886 p_receipt_method_name ,
887 p_doc_sequence_value ,
888 p_ussgl_transaction_code ,
889 p_anticipated_clearing_date ,
890 p_called_from ,
891 p_attribute_rec ,
892 -- ******* Global Flexfield parameters *******
893 p_global_attribute_rec ,
894 p_receipt_comments ,
895 -- *** Notes Receivable Additional Information ***
896 p_issuer_name ,
897 p_issue_date ,
898 p_issuer_bank_branch_id ,
899 -- ** OUT variables for Creating receipt
900 p_cr_id ,
901 -- Receipt application parameters
902 p_applied_payment_schedule_id ,
903 p_amount_applied ,
904 p_application_ref_type ,
905 p_application_ref_id ,
906 p_application_ref_num ,
907 p_secondary_application_ref_id ,
908 p_receivable_trx_id ,
909 p_amount_applied_from ,
910 p_apply_date ,
911 p_apply_gl_date ,
912 app_ussgl_transaction_code ,
913 p_show_closed_invoices ,
914 p_move_deferred_tax ,
915 app_attribute_rec ,
916 -- ******* Global Flexfield parameters *******
917 app_global_attribute_rec ,
918 app_comments ,
919 -- processor such as iPayments
920 p_payment_server_order_num ,
921 p_approval_code ,
922 p_call_payment_processor ,
923 p_payment_response_error_code ,
924 -- OUT NOCOPY parameter for the Application
925 p_receivable_application_id ,
926 p_payment_set_id,
927 p_org_id,
928 p_payment_trxn_extension_id);
929
930
931 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
932 p_count => x_msg_count,
933 p_data => x_msg_data);
934
935 IF PG_DEBUG in ('Y', 'C') THEN
936 arp_util.debug('AR_PREPAYMENTS.Create_Prepayment ()-');
937 END IF;
938
939
940 END IF; /* SSA changes */
941
942 EXCEPTION
943 WHEN others THEN
944 IF PG_DEBUG in ('Y', 'C') THEN
945 arp_util.debug('EXCEPTION : ar_prepayment.create_prepayment ||SQLERRM');
946 END IF;
947 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
948
949 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
950 p_count => x_msg_count,
951 p_data => x_msg_data);
952
953 END Create_Prepayment;
954
955 PROCEDURE Refund_Prepayments(
956 -- Standard API parameters.
957 p_api_version IN NUMBER,
958 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
959 p_commit IN VARCHAR2 := FND_API.G_FALSE,
960 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
961 x_return_status OUT NOCOPY VARCHAR2,
962 x_msg_count OUT NOCOPY NUMBER,
963 x_msg_data OUT NOCOPY VARCHAR2,
964 p_prepay_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
965 p_number_of_refund_receipts OUT NOCOPY NUMBER,
966 p_receipt_number IN ar_cash_receipts.receipt_number%TYPE DEFAULT NULL,
967 p_cash_receipt_id IN ar_cash_receipts.cash_receipt_id%TYPE DEFAULT NULL,
968 p_receivable_application_id IN ar_receivable_applications.receivable_application_id%TYPE DEFAULT NULL,
969 p_receivables_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE DEFAULT NULL,
970 p_refund_amount IN ar_receivable_applications.amount_applied%TYPE DEFAULT NULL,
971 p_refund_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
972 p_refund_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
973 p_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
974 p_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
975 DEFAULT ar_receipt_api_pub.attribute_rec_const,
976 -- ******* Global Flexfield parameters *******
977 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
978 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
979 p_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
980 p_payment_set_id IN NUMBER DEFAULT NULL
981 ) IS
982
983 --Multiple Prepayments project, get all the receipts
984 CURSOR prepay_rcpt_cur(c_payment_set_id NUMBER,
985 c_receipt_method_id IN NUMBER,
986 c_bank_account_id IN NUMBER) IS
987 SELECT ra.cash_receipt_id,
988 SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
989 -7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) prepayment_amount
990 FROM ar_receivable_applications ra , ar_cash_receipts cr
991 WHERE ra.payment_set_id= c_payment_set_id
992 AND cr.cash_receipt_id = ra.cash_receipt_id
993 AND ra.display = 'Y'
994 AND decode(c_receipt_method_id, null,'1',cr.receipt_method_id ) =
995 nvl(c_receipt_method_id,'1')
996 AND decode(c_bank_account_id,null,1,cr.customer_bank_account_id ) =
997 nvl(c_bank_account_id,1)
998 GROUP by ra.cash_receipt_id
999 order by prepayment_amount desc;
1000
1001 l_api_name CONSTANT VARCHAR2(20) := 'Refund_Prepayment';
1002 l_api_version CONSTANT NUMBER := 1.0;
1003
1004 l_ra_rec ar_receivable_applications%ROWTYPE;
1005 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
1006 l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type;
1007
1008 l_cash_receipt_id NUMBER;
1009 l_applied_ps_id NUMBER;
1010 l_receivable_application_id NUMBER;
1011 l_receivables_trx_id NUMBER;
1012 l_apply_gl_date DATE;
1013 l_def_return_status VARCHAR2(1);
1014 l_def_activity_return_status VARCHAR2(1);
1015 l_val_return_status VARCHAR2(1);
1016 l_reapply_amount ar_receivable_applications.amount_applied%TYPE;
1017 l_payment_set_id NUMBER;
1018 l_refund_amount NUMBER;
1019 l_rcpt_refund_amount NUMBER;
1020 l_number_of_refund_receipts NUMBER :=0;
1021
1022 ------Multiple Prepayments
1023 l_total_pmt_types NUMBER := 0;
1024 l_credit_pmt_type_count NUMBER := 0;
1025 l_refund_type VARCHAR2(30) := null;
1026 l_payment_type ar_receipt_methods.PAYMENT_TYPE_CODE%TYPE;
1027 l_receipt_id_def_status VARCHAR2(1);
1028 t_bank_account_id NUMBER := null;
1029 t_receipt_method_id NUMBER := null;
1030 l_max_refund_amt NUMBER := 0;
1031 l_dummy number := null;
1032 BEGIN
1033 IF PG_DEBUG in ('Y', 'C') THEN
1034 arp_util.debug('ar_prepayment.refund_prepayments (+)');
1035 END IF;
1036
1037 /*------------------------------------+
1038 | Standard start of API savepoint |
1039 +------------------------------------*/
1040
1041 SAVEPOINT refund_prepay_PVT;
1042
1043 /*-----------------------------------------+
1044 | Initialize return status to SUCCESS |
1045 +-----------------------------------------*/
1046
1047 x_return_status := FND_API.G_RET_STS_SUCCESS;
1048
1049 /*--------------------------------------------------+
1050 | Standard call to check for call compatibility |
1051 +--------------------------------------------------*/
1052
1053 IF NOT FND_API.Compatible_API_Call(
1054 l_api_version,
1055 p_api_version,
1056 l_api_name,
1057 G_PKG_NAME
1058 )
1059 THEN
1060 x_return_status := FND_API.G_RET_STS_ERROR;
1061 RAISE FND_API.G_EXC_ERROR;
1062 END IF;
1063
1064 /*--------------------------------------------------------------+
1065 | Initialize message list if p_init_msg_list is set to TRUE |
1066 +--------------------------------------------------------------*/
1067
1068 IF FND_API.to_Boolean( p_init_msg_list )
1069 THEN
1070 FND_MSG_PUB.initialize;
1071 END IF;
1072
1073 IF PG_DEBUG in ('Y', 'C') THEN
1074 arp_util.debug('Refund_Prepayments: ' || 'Activity_application()+ ');
1075 END IF;
1076
1077
1078 /*-------------------------------------------------+
1079 | Initialize SOB/org dependent variables |
1080 +-------------------------------------------------*/
1081 arp_global.init_global;
1082 arp_standard.init_standard;
1083
1084
1085 --If receivable_application_id is NOT passed, get the id and details
1086 --and fetch the record and keep it in local variable before unapplying it
1087
1088 l_cash_receipt_id := p_cash_receipt_id;
1089 l_receivable_application_id := p_receivable_application_id;
1090 l_refund_amount := p_refund_amount;
1091 l_payment_set_id := p_payment_set_id;
1092 l_receivables_trx_id := p_receivables_trx_id;
1093 l_receipt_id_def_status := FND_API.G_RET_STS_SUCCESS;
1094
1095 IF (l_payment_set_id IS NULl AND p_receipt_number IS NULL
1096 AND l_cash_receipt_id IS NULL AND l_receivable_application_id IS NULL)
1097 THEN
1098 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_NULL');
1099 FND_MSG_PUB.Add;
1100 x_return_status := FND_API.G_RET_STS_ERROR;
1101 RETURN;
1102 END IF;
1103
1104
1105
1106 --if receipt number is passed and cash_receipt_id is not then
1107 --derive the cash_receipt_id
1108 IF p_receipt_number IS NOT NULL AND l_cash_receipt_id is NULL THEN
1109 ar_receipt_lib_pvt.Default_cash_receipt_id(l_cash_receipt_id ,
1110 p_receipt_number ,
1111 l_receipt_id_def_status);
1112
1113
1114 IF l_receipt_id_def_status <> FND_API.G_RET_STS_SUCCESS OR
1115 l_cash_receipt_id is NULL THEN
1116
1117 IF PG_DEBUG in ('Y', 'C') THEN
1118 arp_util.debug('refund_prepayments: ' || 'Validation or Defaulting Failed' ) ;
1119 END IF;
1120
1121 x_return_status := FND_API.G_RET_STS_ERROR;
1122 RAISE FND_API.G_EXC_ERROR;
1123 END IF;
1124 END IF;
1125
1126
1127 -------------------------------------------------------------
1128 ----------Refund using Receipt Info ------------------------
1129 -------------------------------------------------------------
1130 IF l_cash_receipt_id is not null then
1131
1132 IF PG_DEBUG in ('Y', 'C') THEN
1133 arp_util.debug('refund_prepayments: refund using cash_receipt_id: '
1134 || l_cash_receipt_id ) ;
1135 END IF;
1136
1137 BEGIN
1138 --Check the payment_method
1139 select nvl(rm.payment_channel_code, 'CHECK')
1140 into l_payment_type
1141 from ar_receipt_methods rm , ar_cash_receipts cr
1142 WHERE cr.cash_receipt_id = l_cash_receipt_id
1143 AND cr.receipt_method_id = rm.receipt_method_id;
1144
1145 -------------Validate the refund amount for payment_set_id-------
1146 SELECT
1147 SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
1148 -7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) max_refund_amt
1149 into l_max_refund_amt
1150 FROM ar_receivable_applications ra
1151 WHERE ra.cash_receipt_id = l_cash_receipt_id
1152 AND ra.display = 'Y';
1153
1154 ---Bug: 3504678
1155 if nvl(l_refund_amount,0) > nvl(l_max_refund_amt,0) then
1156 --raise error X validation failed
1157 FND_MESSAGE.SET_NAME('AR','AR_RW_CCR_REFUND_AMOUNT');
1158 FND_MSG_PUB.Add;
1159 x_return_status := FND_API.G_RET_STS_ERROR ;
1160 RETURN;
1161 end if;
1162
1163
1164 ---Deriive the refund type from payment type
1165 if l_payment_type <> 'CREDIT_CARD' THEN
1166 l_refund_type := 'ON_ACCOUNT';
1167 else
1168 l_refund_type := 'CREDIT_CARD';
1169 end if;
1170
1171 IF PG_DEBUG in ('Y', 'C') THEN
1172 arp_util.debug('refund_prepayments: Refund type : ' ||
1173 l_refund_type);
1174 END IF;
1175
1176 EXCEPTION
1177 when no_data_found then
1178 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_INVALID');
1179 FND_MSG_PUB.Add;
1180 x_return_status := FND_API.G_RET_STS_ERROR;
1181 RETURN ;
1182
1183 when others then
1184 x_return_status := FND_API.G_RET_STS_ERROR;
1185 RAISE;
1186 END;
1187
1188 IF PG_DEBUG in ('Y', 'C') THEN
1189 arp_util.debug('refund_prepayments: Calling process_prepayments');
1190 END IF;
1191
1192 --call process prepayment
1193 process_prepayments(
1194 p_api_version => p_api_version,
1195 p_init_msg_list => p_init_msg_list,
1196 p_commit => p_commit,
1197 p_validation_level => p_validation_level,
1198 p_receipt_number => p_receipt_number,
1199 p_cash_receipt_id => l_cash_receipt_id,
1200 p_receivable_application_id => l_receivable_application_id,
1201 p_receivables_trx_id => l_receivables_trx_id,
1202 p_refund_amount => l_refund_amount,
1203 p_refund_date => p_refund_date,
1204 p_refund_gl_date => p_refund_gl_date,
1205 p_ussgl_transaction_code => p_ussgl_transaction_code,
1206 p_attribute_rec => p_attribute_rec,
1207 -- ******* Global Flexfield parameters *******
1208 p_global_attribute_rec => p_global_attribute_rec,
1209 p_comments => p_comments,
1210 p_refund_type => l_refund_type,
1211 x_return_status => x_return_status,
1212 x_msg_count => x_msg_count,
1213 x_msg_data => x_msg_data,
1214 p_prepay_application_id => p_prepay_application_id
1215 );
1216
1217
1218 IF PG_DEBUG in ('Y', 'C') THEN
1219 arp_util.debug('refund_prepayments: process_prepayments ' ||
1220 ' return status: '|| x_return_status );
1221 END IF;
1222
1223 ELSE ---if refund is via payment_set_id
1224
1225 --Check if the global variables are populated, that means
1226 --the credit card refund need to be done for a particular
1227 --receipt_method to a particular credit card bank
1228 IF l_payment_set_id is not null THEN
1229
1230 IF PG_DEBUG in ('Y', 'C') THEN
1231 arp_util.debug('refund_prepayments: refund using payment_set_id: ' ||
1232 l_payment_set_id ) ;
1233 END IF;
1234
1235 /*--------------------------------------
1236 Check if payment set id is valid
1237 ---------------------------------------*/
1238 select count(*) into l_dummy
1239 from ar_cash_receipts_all cr, ar_receivable_applications ra
1240 where ra.payment_set_id= l_payment_set_id
1241 AND cr.cash_receipt_id = ra.cash_receipt_id;
1242
1243 if l_dummy = 0 then
1244 FND_MESSAGE.SET_NAME('AR','AR_CUST_INVALID_PARAMETER');
1245 FND_MESSAGE.SET_TOKEN('PARAMETER','P_PAYMENT_SET_ID');
1246 FND_MESSAGE.SET_TOKEN('VALUE',l_payment_set_id);
1247 FND_MSG_PUB.Add;
1248 x_return_status := FND_API.G_RET_STS_ERROR;
1249 RETURN;
1250 end if;
1251
1252
1253 if G_REFUND_BANK_ACCOUNT_ID is not NULL then
1254 t_bank_account_id := G_REFUND_BANK_ACCOUNT_ID;
1255
1256 if PG_DEBUG in ('Y', 'C') THEN
1257 arp_util.debug('refund_prepayments: ' ||
1258 'refund to the bank account id: ' ||
1259 t_bank_account_id ) ;
1260 end if;
1261
1262 /*--------------------------------------
1263 Check if t_bank_account_id is valid
1264 ---------------------------------------*/
1265 select count(*) into l_dummy
1266 from ar_cash_receipts_all cr, ar_receivable_applications ra
1267 where ra.payment_set_id= l_payment_set_id
1268 AND cr.cash_receipt_id = ra.cash_receipt_id
1269 and cr.customer_bank_account_id = t_bank_account_id;
1270
1271 if l_dummy = 0 then
1272 FND_MESSAGE.SET_NAME('AR','AR_CUST_INVALID_PARAMETER');
1273 FND_MESSAGE.SET_TOKEN('PARAMETER','P_BANK_ACCOUNT_ID');
1274 FND_MESSAGE.SET_TOKEN('VALUE',t_bank_account_id);
1275 FND_MSG_PUB.Add;
1276 x_return_status := FND_API.G_RET_STS_ERROR;
1277 RETURN;
1278 end if;
1279
1280 end if;
1281
1282 if G_REFUND_RECEIPT_METHOD_ID is not null then
1283 t_receipt_method_id := G_REFUND_RECEIPT_METHOD_ID;
1284
1285 if PG_DEBUG in ('Y', 'C') THEN
1286 arp_util.debug('refund_prepayments: '||
1287 ' refund for the receipt method : ' ||
1288 t_receipt_method_id ) ;
1289 end if;
1290
1291 /*--------------------------------------
1292 Check if t_receipt_method_id is valid
1293 ---------------------------------------*/
1294 select count(*) into l_dummy
1295 from ar_cash_receipts_all cr, ar_receivable_applications ra
1296 where ra.payment_set_id= l_payment_set_id
1297 AND cr.cash_receipt_id = ra.cash_receipt_id
1298 and cr.receipt_method_id = t_receipt_method_id;
1299
1300 if l_dummy = 0 then
1301 FND_MESSAGE.SET_NAME('AR','AR_CUST_INVALID_PARAMETER');
1302 FND_MESSAGE.SET_TOKEN('PARAMETER','P_RECEIPT_METHOD_ID');
1303 FND_MESSAGE.SET_TOKEN('VALUE',t_receipt_method_id);
1304 FND_MSG_PUB.Add;
1305 x_return_status := FND_API.G_RET_STS_ERROR;
1306 RETURN;
1307 end if;
1308
1309
1310 end if;
1311
1312 -------------Validate the refund amount for payment_set_id-------
1313 SELECT
1314 SUM(DECODE(ra.status,'OTHER ACC',DECODE(applied_payment_schedule_id,
1315 -7,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),0),0)) max_refund_amt
1316 into l_max_refund_amt
1317 FROM ar_receivable_applications ra , ar_cash_receipts cr
1318 WHERE ra.payment_set_id= l_payment_set_id
1319 AND cr.cash_receipt_id = ra.cash_receipt_id
1320 AND ra.display = 'Y'
1321 AND decode(t_receipt_method_id, null,'1',cr.receipt_method_id ) =
1322 nvl(t_receipt_method_id,'1')
1323 AND decode(t_bank_account_id,null,1,cr.customer_bank_account_id ) =
1324 nvl(t_bank_account_id,1);
1325
1326 ---Bug 3504678
1327 if nvl(l_refund_amount,0) > nvl(l_max_refund_amt,0) then
1328 --raise error X validation failed
1329 FND_MESSAGE.SET_NAME('AR','AR_RW_CCR_REFUND_AMOUNT');
1330 FND_MSG_PUB.Add;
1331 x_return_status := FND_API.G_RET_STS_ERROR ;
1332 RETURN;
1333 end if;
1334
1335 if PG_DEBUG in ('Y', 'C') THEN
1336 arp_util.debug('refund_prepayments: refund amount : ' ||
1337 l_refund_amount);
1338 end if;
1339
1340
1341 --Refunding across Prepaid receipts using Payment_Set_Id
1342 ----check whether refund should be ON ACCOUNT or to CREDIT CARD----
1343 SELECT count(distinct NVL(rm.payment_channel_code, 'CHECK'))
1344 as pmt_type_count,
1345 sum(DECODE(rm.payment_channel_code, 'CREDIT_CARD', 1, 0))
1346 as credit_pmt_type_count
1347 INTO l_total_pmt_types, l_credit_pmt_type_count
1348 FROM ar_receivable_applications ra,
1349 ar_cash_receipts cr,
1350 ar_receipt_methods rm
1351 WHERE ra.payment_set_id = l_payment_set_id
1352 AND ra.cash_receipt_id = cr.cash_receipt_id
1353 AND cr.receipt_method_id = rm.receipt_method_id
1354 AND decode(t_bank_account_id,null,1,cr.customer_bank_account_id ) =
1355 nvl(t_bank_account_id,1)
1356 AND decode(t_receipt_method_id,null,1,cr.receipt_method_id ) =
1357 nvl(t_receipt_method_id,1);
1358
1359 ----- If there are no payment types-------
1360 IF l_total_pmt_types = 0 THEN
1361
1362 IF PG_DEBUG in ('Y', 'C') THEN
1363 arp_util.debug('Refund_Prepayments: ' || 'Could not find payment type()+ ');
1364 END IF;
1365 x_return_status := FND_API.G_RET_STS_ERROR;
1366 RETURN;
1367
1368
1369 -----if there is one payment type --------
1370 ELSIF l_total_pmt_types = 1 and l_credit_pmt_type_count > 0 then
1371
1372 l_refund_type := 'CREDIT_CARD';
1373
1374 if PG_DEBUG in ('Y', 'C') THEN
1375 arp_util.debug('Refund_Prepayments: '||'One Payment type '||
1376 ' Refund: '|| l_refund_type);
1377 end if;
1378
1379 ELSE --- Greater than 1 payment types
1380 l_refund_type := 'ON_ACCOUNT'; -- No CC Applications
1381
1382 if PG_DEBUG in ('Y', 'C') THEN
1383 arp_util.debug('Refund_Prepayments: ' || 'Many Payment types '||
1384 ' Refund: '|| l_refund_type);
1385 end if;
1386
1387 END IF; -- total_pmt_types
1388
1389 FOR prepay_rcpt_rec IN prepay_rcpt_cur(l_payment_set_id,
1390 t_receipt_method_id, t_bank_account_id )
1391 LOOP
1392
1393 l_cash_receipt_id := prepay_rcpt_rec.cash_receipt_id;
1394
1395 IF l_refund_amount > 0 THEN
1396
1397 IF NVL(l_refund_amount,0) <= prepay_rcpt_rec.prepayment_amount THEN
1398 l_rcpt_refund_amount := l_refund_amount;
1399 ELSE
1400 l_rcpt_refund_amount := prepay_rcpt_rec.prepayment_amount;
1401 END IF;
1402
1403 if PG_DEBUG in ('Y', 'C') THEN
1404 arp_util.debug('Refund_Prepayments: ' ||
1405 ' Calling process_prepayments' ||
1406 ' For Receipt ID: '|| l_cash_receipt_id ||
1407 ' To issue Refund of type '|| l_refund_type ||
1408 'For Amount: '|| l_rcpt_refund_amount);
1409 end if;
1410
1411 --call process prepayment
1412
1413 process_prepayments(
1414 p_api_version => p_api_version,
1415 p_init_msg_list => p_init_msg_list,
1416 p_commit => p_commit,
1417 p_validation_level => p_validation_level,
1418 p_receipt_number => p_receipt_number,
1419 p_cash_receipt_id => l_cash_receipt_id,
1420 p_receivable_application_id => l_receivable_application_id,
1421 p_receivables_trx_id => l_receivables_trx_id,
1422 p_refund_amount => l_rcpt_refund_amount,
1423 p_refund_date => p_refund_date,
1424 p_refund_gl_date => p_refund_gl_date,
1425 p_ussgl_transaction_code => p_ussgl_transaction_code,
1426 p_attribute_rec => p_attribute_rec,
1427 -- ******* Global Flexfield parameters *******
1428 p_global_attribute_rec => p_global_attribute_rec,
1429 p_comments => p_comments,
1430
1431 --Multiple Prepayments project
1432 p_refund_type => l_refund_type,
1433
1434 x_return_status => x_return_status,
1435 x_msg_count => x_msg_count,
1436 x_msg_data => x_msg_data,
1437 p_prepay_application_id => p_prepay_application_id
1438 );
1439
1440 IF PG_DEBUG in ('Y', 'C') THEN
1441 arp_util.debug('Refund_Prepayments: ' || 'Receivable App ID : '||p_prepay_application_id);
1442 arp_util.debug('Refund_Prepayments: ' || 'Process Payments status : '||x_return_status);
1443 END IF;
1444
1445 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1446 x_return_status := FND_API.G_RET_STS_ERROR;
1447 EXIT;
1448 END IF;
1449
1450 l_refund_amount := l_refund_amount - l_rcpt_refund_amount;
1451
1452 --This is to indicate how many receipt this amount has been prorated.
1453 l_number_of_refund_receipts := l_number_of_refund_receipts + 1;
1454 ELSE
1455 EXIT; -- Exit out NOCOPY the loop
1456 END IF; --refund_amount > 0
1457 END LOOP;
1458
1459 END IF; ---payment_set_id not null
1460
1461 IF PG_DEBUG in ('Y', 'C') THEN
1462 arp_util.debug('Refund_Prepayments: ' || 'Process Payments status : '||x_return_status);
1463 END IF;
1464
1465 l_number_of_refund_receipts := 1;
1466
1467 END IF; --cash receipt_id or payment_set_id
1468
1469 /*-------------------------------------------------
1470 Finally error Handling
1471 Moved it down fron before the end if
1472 --------------------------------------------------*/
1473 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1474
1475 ---Bug 3628401 raising expected error
1476 x_return_status := FND_API.G_RET_STS_ERROR;
1477 RAISE FND_API.G_EXC_ERROR;
1478
1479 END IF;
1480
1481
1482 p_number_of_refund_receipts := l_number_of_refund_receipts;
1483
1484 /*--------------------------------+
1485 | Standard check of p_commit |
1486 +--------------------------------*/
1487
1488 IF FND_API.To_Boolean( p_commit )
1489 THEN
1490 IF PG_DEBUG in ('Y', 'C') THEN
1491 arp_util.debug('Refund_Prepayments: ' || 'committing');
1492 END IF;
1493 Commit;
1494 END IF;
1495
1496 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1497 p_count => x_msg_count,
1498 p_data => x_msg_data);
1499
1500 IF PG_DEBUG in ('Y', 'C') THEN
1501 arp_util.debug('ar_prepayments.refund_prepayments(-)');
1502 END IF;
1503
1504
1505 EXCEPTION
1506
1507 WHEN FND_API.G_EXC_ERROR THEN
1508
1509 IF PG_DEBUG in ('Y', 'C') THEN
1510 arp_util.debug( SQLCODE, G_MSG_ERROR);
1511 arp_util.debug( SQLERRM, G_MSG_ERROR);
1512 END IF;
1513
1514 ROLLBACK TO refund_prepay_PVT;
1515 x_return_status := FND_API.G_RET_STS_ERROR ;
1516
1517 -- Display_Parameters;
1518 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1519 p_count => x_msg_count,
1520 p_data => x_msg_data
1521 );
1522
1523 WHEN OTHERS THEN
1524
1525 /*-------------------------------------------------------+
1526 | Handle application errors that result from trapable |
1527 | error conditions. The error messages have already |
1528 | been put on the error stack. |
1529 +-------------------------------------------------------*/
1530
1531 IF (SQLCODE = -20001) THEN
1532 ROLLBACK TO refund_prepay_PVT;
1533
1534 -- Display_Parameters;
1535 x_return_status := FND_API.G_RET_STS_ERROR ;
1536 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1537 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','AR_PREPAYMENTS_PUB.refund : '||SQLERRM);
1538 FND_MSG_PUB.Add;
1539 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1540 p_count => x_msg_count,
1541 p_data => x_msg_data);
1542 RETURN;
1543 ELSE
1544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1545 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1546 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','REFUND_PREPAYMENT : '||SQLERRM);
1547 FND_MSG_PUB.Add;
1548 END IF;
1549
1550 IF PG_DEBUG in ('Y', 'C') THEN
1551 arp_util.debug('Refund_Prepayment: ' || SQLCODE, G_MSG_ERROR);
1552 arp_util.debug('Refund_Prepayment: ' || SQLERRM, G_MSG_ERROR);
1553 END IF;
1554
1555 ROLLBACK TO refund_prepay_PVT;
1556
1557 IF FND_MSG_PUB.Check_Msg_Level THEN
1558 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1559 l_api_name
1560 );
1561 END IF;
1562
1563 -- Display_Parameters;
1564 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1565 p_count => x_msg_count,
1566 p_data => x_msg_data);
1567
1568
1569 END refund_prepayments;
1570
1571 ------Multiple Prepayments project-------------------------------------------
1572 ---Removing the procedure Process_Credit_Card to charge the Credit card------
1573
1574 PROCEDURE match_prepayment (p_payment_schedule_id IN NUMBER,
1575 p_apply_date IN DATE,
1576 p_apply_gl_date IN DATE,
1577 p_cash_receipt_id OUT NOCOPY NUMBER,
1578 ps_amt_due_remain OUT NOCOPY NUMBER,
1579 x_return_status OUT NOCOPY VARCHAR2
1580 ) IS
1581
1582 CURSOR paymentset_cur(c_customer_trx_id NUMBER) IS
1583 SELECT distinct ctl.payment_set_id
1584 FROM ra_customer_trx_lines ctl
1585 WHERE ctl.payment_set_id is not null
1586 AND ctl.customer_trx_id= c_customer_trx_id;
1587
1588 CURSOR prepayapp_cur(c_payment_set_id NUMBER) IS
1589 SELECT *
1590 FROM ar_receivable_applications
1591 WHERE display ='Y'
1592 AND applied_payment_schedule_id = -7
1593 AND payment_set_id = c_payment_set_id
1594 order by amount_applied;
1595
1596 l_payment_schedule_id ar_payment_schedules.payment_schedule_id%TYPE;
1597 l_ps_rec ar_payment_schedules%ROWTYPE;
1598 l_api_version CONSTANT NUMBER := 1.0;
1599 l_api_name CONSTANT VARCHAR2(20) := 'MATCH_PREPAYMENT';
1600 l_return_status VARCHAR2(1);
1601 l_msg_count NUMBER;
1602 l_msg_data VARCHAR2(2000);
1603 l_amount_applied ar_receivable_applications.amount_applied%TYPE := 0;
1604 l_prepay_amount_reapplied ar_receivable_applications.amount_applied%TYPE := 0;
1605 l_receivable_application_id ar_receivable_applications.receivable_application_id%TYPE;
1606 l_attribute_rec ar_receipt_api_pub.attribute_rec_type;
1607 l_global_attribute_rec ar_receipt_api_pub.global_attribute_rec_type;
1608 l_prepayment_exist_flag VARCHAR2(1);
1609 l_complete_applied_flag BOOLEAN := FALSE;
1610 l_amount_to_be_applied NUMBER; /* Bug 10250730, variable to store value returned from function get_invoice_bal_after_disc */
1611
1612 BEGIN
1613 SAVEPOINT match_prepayment_PVT;
1614
1615 IF PG_DEBUG in ('Y', 'C') THEN
1616 arp_util.debug('ar_prepayments.match_prepayment (+)');
1617 END IF;
1618 x_return_status := FND_API.G_RET_STS_SUCCESS;
1619 l_payment_schedule_id := p_payment_schedule_id;
1620
1621 BEGIN
1622 SELECT 'Y'
1623 INTO l_prepayment_exist_flag
1624 FROM ar_payment_schedules ps,
1625 ra_customer_trx ct
1626 WHERE ps.customer_trx_id =ct.customer_trx_id
1627 AND NVL(ct.prepayment_flag,'N') = 'Y'
1628 AND ps.payment_schedule_id=l_payment_schedule_id;
1629
1630 EXCEPTION
1631 WHEN no_data_found THEN
1632 IF PG_DEBUG in ('Y', 'C') THEN
1633 arp_util.debug('ar_prepayments.match_prepayment No prepayment exists');
1634 END IF;
1635 x_return_status := FND_API.G_RET_STS_SUCCESS;
1636 RETURN;
1637 WHEN others THEN
1638 IF PG_DEBUG in ('Y', 'C') THEN
1639 arp_util.debug('EXCEPTION :ar_prepayments.match_prepayment Check ');
1640 END IF;
1641 x_return_status := FND_API.G_RET_STS_ERROR;
1642 RETURN;
1643 END;
1644
1645 --fetch invoice payment schedule
1646 arp_ps_pkg.fetch_p(l_payment_schedule_id,l_ps_rec);
1647
1648 FOR paymentset_rec in paymentset_cur(l_ps_rec.customer_trx_id) LOOP
1649
1650 FOR prepayapp_rec in prepayapp_cur(paymentset_rec.payment_set_id) LOOP
1651
1652 p_cash_receipt_id := prepayapp_rec.cash_receipt_id;
1653
1654 --Unapply the prepayment
1655 ar_receipt_api_pub.Unapply_other_account(
1656 --Standard API parameters.
1657 p_api_version => 1.0,
1658 x_return_status => l_return_status,
1659 x_msg_count => l_msg_count,
1660 x_msg_data => l_msg_data,
1661 p_receipt_number => NULL,
1662 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1663 p_receivable_application_id => prepayapp_rec.receivable_application_id,
1664 p_reversal_gl_date => p_apply_gl_date,
1665 p_called_from => 'PREPAYMENT' --Bug7194951
1666 );
1667
1668 IF PG_DEBUG in ('Y', 'C') THEN
1669 arp_util.debug('match_prepayment: ' || 'unapply_other_account retun status : '||l_return_status);
1670 END IF;
1671 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1672 FND_FILE.put_line(fnd_file.log,'Prepayment is not getting unapplied Cash Receipt ID : ' || prepayapp_rec.cash_receipt_id);
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 EXIT;
1675 END IF;
1676
1677 /*
1678 Bug 10250730. Included call to AR_AUTOREC_API.Get_Invoice_Bal_After_Disc() function to get the Invoice Due amount after deducting Discount component.
1679 This amount is to be used while applying Prepayment Receipt to Invoice so that the Earned Discount gets populated with Discount amount in the Receipts form.
1680 */
1681
1682 l_amount_to_be_applied := AR_AUTOREC_API.Get_Invoice_Bal_After_Disc(p_applied_payment_schedule_id => l_ps_rec.payment_schedule_id,
1683 p_apply_date => prepayapp_rec.apply_date);
1684
1685
1686 IF NVL(l_amount_to_be_applied,0) < NVL(prepayapp_rec.amount_applied,0)
1687 THEN
1688 l_amount_applied := NVL(l_amount_to_be_applied,0);
1689 l_prepay_amount_reapplied := prepayapp_rec.amount_applied - l_amount_to_be_applied;
1690 ELSE
1691 l_amount_applied := NVL(prepayapp_rec.amount_applied,0);
1692 END IF;
1693
1694 /* End of change, Bug 10250730 */
1695
1696 --Apply to invoice
1697 ar_receipt_api_pub.Apply(p_api_version => l_api_version,
1698 x_return_status => l_return_status,
1699 x_msg_count => l_msg_count,
1700 x_msg_data => l_msg_data,
1701 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1702 p_trx_number => l_ps_rec.trx_number,
1703 p_customer_trx_id => l_ps_rec.customer_trx_id,
1704 p_installment => l_ps_rec.terms_sequence_number,
1705 p_applied_payment_schedule_id => l_ps_rec.payment_schedule_id,
1706 p_amount_applied => l_amount_applied,
1707 p_apply_date => p_apply_date,
1708 p_apply_gl_date => p_apply_gl_date,
1709 p_called_from => 'PREPAYMENT',
1710 p_payment_set_id => prepayapp_rec.payment_set_id
1711 );
1712
1713 l_ps_rec.amount_due_remaining := (l_ps_rec.amount_due_remaining - l_amount_applied);
1714
1715 IF PG_DEBUG in ('Y', 'C') THEN
1716 arp_util.debug('match_prepayment: ' || 'Apply retun status : '||l_return_status);
1717 END IF;
1718
1719 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1720 x_return_status := FND_API.G_RET_STS_ERROR;
1721 FND_FILE.put_line(fnd_file.log,'Prepayment is not getting applied to Invoice ');
1722 FND_FILE.put_line(fnd_file.log,'Cash receipt ID : ' || prepayapp_rec.cash_receipt_id);
1723 FND_FILE.put_line(fnd_file.log,'Customer Trx ID : ' || l_ps_rec.customer_trx_id);
1724 FND_FILE.put_line(fnd_file.log,'Payment Schedule ID: ' || l_ps_rec.payment_schedule_id);
1725 FND_FILE.put_line(fnd_file.log,'Transaction Number: ' || l_ps_rec.trx_number);
1726 FND_FILE.put_line(fnd_file.log,'Payment Set ID: ' || prepayapp_rec.payment_set_id);
1727 END IF;
1728
1729 --Apply rest of the amount back to prepayment
1730 IF (l_prepay_amount_reapplied > 0) AND (x_return_status <> FND_API.G_RET_STS_ERROR) THEN
1731 --Assign atributes
1732 l_attribute_rec.attribute_category := prepayapp_rec.attribute_category;
1733 l_attribute_rec.attribute1 := prepayapp_rec.attribute1;
1734 l_attribute_rec.attribute2 := prepayapp_rec.attribute2;
1735 l_attribute_rec.attribute3 := prepayapp_rec.attribute3;
1736 l_attribute_rec.attribute4 := prepayapp_rec.attribute4;
1737 l_attribute_rec.attribute5 := prepayapp_rec.attribute5;
1738 l_attribute_rec.attribute6 := prepayapp_rec.attribute6;
1739 l_attribute_rec.attribute7 := prepayapp_rec.attribute7;
1740 l_attribute_rec.attribute8 := prepayapp_rec.attribute8;
1741 l_attribute_rec.attribute9 := prepayapp_rec.attribute9;
1742 l_attribute_rec.attribute10 := prepayapp_rec.attribute10;
1743 l_attribute_rec.attribute11 := prepayapp_rec.attribute11;
1744 l_attribute_rec.attribute12 := prepayapp_rec.attribute12;
1745 l_attribute_rec.attribute13 := prepayapp_rec.attribute13;
1746 l_attribute_rec.attribute14 := prepayapp_rec.attribute14;
1747 l_attribute_rec.attribute15 := prepayapp_rec.attribute15;
1748 l_global_attribute_rec.global_attribute_category :=
1749 prepayapp_rec.global_attribute_category;
1750 l_global_attribute_rec.global_attribute1 := prepayapp_rec.global_attribute1;
1751 l_global_attribute_rec.global_attribute2 := prepayapp_rec.global_attribute2;
1752 l_global_attribute_rec.global_attribute3 := prepayapp_rec.global_attribute3;
1753 l_global_attribute_rec.global_attribute4 := prepayapp_rec.global_attribute4;
1754 l_global_attribute_rec.global_attribute5 := prepayapp_rec.global_attribute5;
1755 l_global_attribute_rec.global_attribute6 := prepayapp_rec.global_attribute6;
1756 l_global_attribute_rec.global_attribute7 := prepayapp_rec.global_attribute7;
1757 l_global_attribute_rec.global_attribute8 := prepayapp_rec.global_attribute8;
1758 l_global_attribute_rec.global_attribute9 := prepayapp_rec.global_attribute9;
1759 l_global_attribute_rec.global_attribute10 := prepayapp_rec.global_attribute10;
1760 l_global_attribute_rec.global_attribute11 := prepayapp_rec.global_attribute11;
1761 l_global_attribute_rec.global_attribute12 := prepayapp_rec.global_attribute12;
1762 l_global_attribute_rec.global_attribute13 := prepayapp_rec.global_attribute13;
1763 l_global_attribute_rec.global_attribute14 := prepayapp_rec.global_attribute14;
1764 l_global_attribute_rec.global_attribute15 := prepayapp_rec.global_attribute15;
1765 l_global_attribute_rec.global_attribute16 := prepayapp_rec.global_attribute16;
1766 l_global_attribute_rec.global_attribute17 := prepayapp_rec.global_attribute17;
1767 l_global_attribute_rec.global_attribute18 := prepayapp_rec.global_attribute18;
1768 l_global_attribute_rec.global_attribute19 := prepayapp_rec.global_attribute19;
1769 l_global_attribute_rec.global_attribute20 := prepayapp_rec.global_attribute20;
1770
1771 ar_receipt_api_pub.Apply_other_account(
1772 -- Standard API parameters.
1773 p_api_version => l_api_version,
1774 x_return_status => l_return_status,
1775 x_msg_count => l_msg_count,
1776 x_msg_data => l_msg_data,
1777 p_receivable_application_id => l_receivable_application_id,
1778 --Receipt application parameters.
1779 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1780 p_amount_applied => l_prepay_amount_reapplied,
1781 p_receivables_trx_id => prepayapp_rec.receivables_trx_id,
1782 p_applied_payment_schedule_id => -7,
1783 p_apply_date => prepayapp_rec.apply_date,
1784 p_apply_gl_date => p_apply_gl_date,
1785 p_ussgl_transaction_code => prepayapp_rec.ussgl_transaction_code,
1786 p_application_ref_type => prepayapp_rec.application_ref_type,
1787 p_application_ref_id => prepayapp_rec.application_ref_id,
1788 p_application_ref_num => prepayapp_rec.application_ref_num,
1789 p_secondary_application_ref_id => prepayapp_rec.secondary_application_ref_id,
1790 p_payment_set_id => prepayapp_rec.payment_set_id,
1791 p_attribute_rec => l_attribute_rec,
1792 -- ******* Global Flexfield parameters *******
1793 p_global_attribute_rec => l_global_attribute_rec
1794 );
1795
1796 IF PG_DEBUG in ('Y', 'C') THEN
1797 arp_util.debug('match_prepayment: ' || 'apply_other_account retun status : '||l_return_status);
1798 END IF;
1799
1800 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1801 x_return_status := FND_API.G_RET_STS_ERROR;
1802 FND_FILE.put_line(fnd_file.log,'Leftover amount is not getting applied back to Prepayment ');
1803 FND_FILE.put_line(fnd_file.log,'Cash receipt ID : ' || prepayapp_rec.cash_receipt_id);
1804 FND_FILE.put_line(fnd_file.log,'Payment Set ID: ' || prepayapp_rec.payment_set_id);
1805 END IF;
1806
1807 END IF;
1808
1809 ps_amt_due_remain := l_ps_rec.amount_due_remaining;
1810
1811 IF l_ps_rec.amount_due_remaining <= 0 THEN
1812 l_complete_applied_flag := TRUE;
1813 EXIT;
1814 END IF;
1815
1816 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1817 EXIT;
1818 END IF;
1819
1820
1821 END LOOP;
1822
1823 IF (l_complete_applied_flag) THEN
1824 EXIT;
1825 END IF;
1826
1827 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1828 EXIT;
1829 END IF;
1830
1831 END LOOP;
1832
1833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1834 ROLLBACK to match_prepayment_PVT;
1835 IF NVL(l_msg_count,0) > 0 Then
1836 IF l_msg_count = 1 Then
1837 IF PG_DEBUG in ('Y', 'C') THEN
1838 arp_util.debug('match_prepayment: ' || l_msg_data);
1839 END IF;
1840
1841 ELSIF l_msg_count > 1 Then
1842 FOR l_count IN 1..l_msg_count LOOP
1843
1844 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
1845 IF PG_DEBUG in ('Y', 'C') THEN
1846 arp_util.debug('match_prepayment: ' || to_char(l_count)||' : '||l_msg_data);
1847 END IF;
1848 END LOOP;
1849
1850 END IF; -- l_msg_count
1851 END IF; -- NVL(l_msg_count,0)
1852 ELSE
1853 x_return_status := FND_API.G_RET_STS_SUCCESS;
1854 END IF;
1855
1856 IF PG_DEBUG in ('Y', 'C') THEN
1857 arp_util.debug('ar_prepayments.match_prepayment (-)');
1858 END IF;
1859
1860 EXCEPTION
1861 WHEN others THEN
1862 IF PG_DEBUG in ('Y', 'C') THEN
1863 arp_util.debug('EXCEPTION : ar_prepayments.match_prepayment ||SQLERRM');
1864 END IF;
1865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1866 END match_prepayment;
1867
1868 /*=======================================================================
1869 | PUBLIC Procedure get_installment
1870 |
1871 | DESCRIPTION
1872 | Gets the installment number and the amount due for a payment term
1873 | -----------------------------------------------------------------
1874 |
1875 | PSEUDO CODE/LOGIC
1876 |
1877 | PARAMETERS
1878 |
1879 |
1880 | RETURNS
1881 | nothing
1882 |
1883 | KNOWN ISSUES
1884 |
1885 |
1886 |
1887 | NOTES
1888 |
1889 |
1890 |
1891 | MODIFICATION HISTORY
1892 | Date Author Description of Changes
1893 | 10-JUL-2003 Jyoti Pandey o Created
1894 | 12-DEC-2003 Jyoti Pandey o Forward Port Bug 3316165 for Base bug
1895 | 3248093. Changing p_installment_tbl
1896 | ar_prepayments_pub.installment_tbl
1897 |
1898 *=======================================================================*/
1899 PROCEDURE get_installment(
1900 p_term_id IN NUMBER,
1901 p_amount IN NUMBER,
1902 p_currency_code IN VARCHAR2,
1903 --bug 3248093 --
1904 p_installment_tbl OUT NOCOPY ar_prepayments_pub.installment_tbl,
1905 x_return_status OUT NOCOPY VARCHAR2,
1906 x_msg_count OUT NOCOPY NUMBER,
1907 x_msg_data OUT NOCOPY VARCHAR2) IS
1908
1909 l_dummy VARCHAR2(1);
1910 i BINARY_INTEGER;
1911
1912 --Gets the installment amount and the installment number based
1913 --on the functional currency
1914 CURSOR get_installment_amount (l_term_id IN NUMBER, l_amount IN NUMBER ,
1915 l_currency_code IN VARCHAR2) IS
1916 select sequence_num as installment_number,
1917 arp_util.CurrRound( (relative_amount/base_amount ) * l_amount ,
1918 l_currency_code) as installment_amount
1919 from ra_terms t , ra_terms_lines tl
1920 where t.term_id = tl.term_id
1921 and t.term_id = l_term_id;
1922
1923 BEGIN
1924
1925 IF PG_DEBUG in ('Y', 'C') THEN
1926 arp_util.debug('ar_prepayment.get_installment (+)');
1927 END IF;
1928
1929 ---- first reinitialize ARP_GLOBAL
1930 arp_global.init_global;
1931
1932 /*-------------------------------------+
1933 | Standard start of API savepoint |
1934 +------------------------------------*/
1935 SAVEPOINT get_installment_PVT;
1936
1937 /*----------------------------------------+
1938 | Initialize return status to SUCCESS |
1939 +-----------------------------------------*/
1940 x_return_status := FND_API.G_RET_STS_SUCCESS;
1941
1942 /*-------Validate the p_term_id---------------*/
1943 IF p_term_id IS NULL THEN
1944 FND_MESSAGE.SET_NAME('AR','AR_PPAY_PAY_TERM_INVALID');
1945 FND_MSG_PUB.Add;
1946 RAISE FND_API.G_EXC_ERROR;
1947 ELSE
1948 BEGIN
1949 SELECT 1 into l_dummy
1950 FROM RA_TERMS_B
1951 WHERE term_id = p_term_id;
1952 EXCEPTION
1953 WHEN NO_DATA_FOUND THEN
1954 FND_MESSAGE.SET_NAME('AR','AR_PPAY_PAY_TERM_INVALID');
1955 FND_MSG_PUB.Add;
1956 RAISE FND_API.G_EXC_ERROR;
1957 END;
1958 END IF;
1959
1960 -----Input amount should not be null or 0 -------
1961 IF ( (p_amount is null) or (p_amount <= 0) ) THEN
1962 FND_MESSAGE.SET_NAME('AR','AR_PPAY_BASE_AMOUNT_INVALID');
1963 FND_MSG_PUB.Add;
1964 RAISE FND_API.G_EXC_ERROR;
1965 END IF;
1966
1967 -----Input currency code should not be invalid -------
1968 IF p_currency_code IS NULL THEN
1969 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
1970 FND_MSG_PUB.Add;
1971 RAISE FND_API.G_EXC_ERROR;
1972 ELSE
1973 BEGIN
1974 SELECT 1 into l_dummy
1975 FROM fnd_currencies
1976 WHERE currency_code = p_currency_code;
1977 EXCEPTION
1978 WHEN NO_DATA_FOUND THEN
1979 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
1980 FND_MSG_PUB.Add;
1981 RAISE FND_API.G_EXC_ERROR;
1982 END;
1983 END IF;
1984
1985
1986 i := 1;
1987 OPEN get_installment_amount(p_term_id, p_amount , p_currency_code);
1988 loop
1989 fetch get_installment_amount into
1990 p_installment_tbl(i).installment_number,
1991 p_installment_tbl(i).installment_amount;
1992
1993 exit when get_installment_amount%NOTFOUND;
1994
1995 i := i + 1;
1996 end loop;
1997 close get_installment_amount;
1998
1999 IF PG_DEBUG in ('Y', 'C') THEN
2000 arp_util.debug('ar_prepayment.get_installment (-)');
2001 END IF;
2002
2003 EXCEPTION
2004 WHEN fnd_api.g_exc_error THEN
2005
2006
2007 x_return_status := FND_API.G_RET_STS_ERROR;
2008 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
2009 p_count => x_msg_count,
2010 p_data => x_msg_data);
2011
2012
2013 WHEN others THEN
2014 x_return_status := FND_API.G_RET_STS_ERROR;
2015
2016 --Bug 3107679 removed to_char call
2017 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
2018 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','GET_INSTALLMENT : '||SQLERRM);
2019 FND_MSG_PUB.Add;
2020 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
2021 p_count => x_msg_count,
2022 p_data => x_msg_data);
2023
2024 ROLLBACK to get_installment_PVT;
2025
2026 END get_installment;
2027
2028 /*-------------------------------------------------------------------------+
2029 | FUNCTION NAME |
2030 | rule_select_prepayments |
2031 | |
2032 | DESCRIPTION |
2033 | Subscription to the business event AutoInvoice
2034 | |
2035 | PARAMETERS |
2036 | |
2037 | MODIFIES |
2038 | |
2039 | RETURNS |
2040 |
2041 | |
2042 +-------------------------------------------------------------------------*/
2043 FUNCTION rule_select_prepayments(
2044 p_subscription_guid in raw,
2045 p_event in out NOCOPY wf_event_t)RETURN VARCHAR2 IS
2046
2047 l_request_id NUMBER := null;
2048 l_conc_request_id NUMBER := null;
2049
2050 l_user_id NUMBER;
2051 l_resp_id NUMBER;
2052 l_application_id NUMBER;
2053 l_org_id NUMBER;
2054
2055 BEGIN
2056
2057 l_user_id := p_event.GetValueForParameter('USER_ID');
2058 l_resp_id := p_event.GetValueForParameter('RESP_ID');
2059 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
2060
2061 SAVEPOINT Select_Prepay_Event;
2062
2063 --
2064 --set the application context.
2065 --
2066 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
2067
2068
2069 IF PG_DEBUG in ('Y', 'C') THEN
2070 arp_util.debug('The rule_select_prepayments Subscription to AutoInvoice ''');
2071 arp_util.debug('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2072 END IF;
2073
2074
2075 ---get the parameter request_id
2076 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
2077 -- bug 9027940
2078
2079 IF l_request_id <> 0 AND l_request_id is not null THEN
2080
2081 BEGIN
2082 select org_id into l_org_id
2083 from fnd_concurrent_requests
2084 where request_id = l_request_id;
2085
2086 EXCEPTION
2087 WHEN OTHERS THEN
2088 ROLLBACK TO Select_Prepay_Event;
2089
2090 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
2091 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
2092 FND_MSG_PUB.ADD;
2093
2094 RETURN 'ERROR';
2095 END;
2096
2097 END IF;
2098
2099 IF PG_DEBUG in ('Y', 'C') THEN
2100 FND_FILE.put_line(fnd_file.log, 'Request id is ' || l_request_id);
2101 arp_util.debug('Request id is' || l_request_id);
2102
2103 FND_FILE.put_line(fnd_file.log, 'ORGANISATION ID is ' || l_org_id);
2104 arp_util.debug('ORG id is' || l_org_id);
2105 END IF;
2106
2107 IF l_request_id <> 0 AND l_request_id is not null THEN
2108 ---Make a callout to Concurrent program
2109 fnd_request.set_org_id(l_org_id);
2110 -- Bug 10066143.
2111 l_conc_request_id := fnd_request.submit_request('AR',
2112 'ARPREMAT',
2113 'Prepayment Matching Program',
2114 to_char(sysdate,'DD-MON-YY HH24:MI:SS'),
2115 FALSE,
2116 'AutoInvoice Batch',
2117 l_request_id );
2118
2119 IF l_conc_request_id = 0 THEN
2120 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
2121 FND_MSG_PUB.ADD;
2122 RAISE FND_API.G_EXC_ERROR;
2123 END IF;
2124 END IF;
2125
2126 RETURN 'SUCCESS';
2127
2128
2129 EXCEPTION
2130 WHEN OTHERS THEN
2131 ROLLBACK TO Select_Prepay_Event;
2132
2133 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
2134 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
2135 FND_MSG_PUB.ADD;
2136
2137 WF_CORE.CONTEXT('AR_PREPAYMENTS', 'RULE_SELECT_PREPAYMENTS', p_event.getEventName(), p_subscription_guid);
2138 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2139
2140 RETURN 'ERROR';
2141
2142
2143 END rule_select_prepayments;
2144
2145 /*=======================================================================
2146 | PUBLIC Procedure Select_Prepayments
2147 |
2148 | DESCRIPTION
2149 | Called from Concurrent program 'Prepayments Matching Program' to
2150 | match the prepaid receipts to their invoices
2151 | -----------------------------------------------------------------
2152 |
2153 | PSEUDO CODE/LOGIC
2154 |
2155 | PARAMETERS
2156 | p_batch_source : 'All Invoices' for matching all the invoices
2157 | 'AutoInvoice Batch' for matching invoices in a
2158 | particular AutoInvoice Batch
2159 | p_request_id : Populated only if p_batch_source = 'AutoInvoice Batch'
2160 | Request ID for the AutoInvoice Batch
2161 |
2162 |
2163 | RETURNS
2164 | nothing
2165 |
2166 | KNOWN ISSUES
2167 |
2168 |
2169 |
2170 | NOTES
2171 |
2172 |
2173 |
2174 | MODIFICATION HISTORY
2175 | Date Author Description of Changes
2176 | 10-JUL-2003 Jyoti Pandey o Created
2177 |
2178 *=======================================================================*/
2179 PROCEDURE Select_Prepayments ( errbuf OUT NOCOPY VARCHAR2,
2180 retcode OUT NOCOPY VARCHAR2,
2181 p_batch_source IN VARCHAR2,
2182 p_request_id IN NUMBER )
2183
2184 IS
2185
2186 l_retcode NUMBER := 0;
2187 l_request_id NUMBER := null;
2188 l_payment_schedule_id NUMBER;
2189 l_amt_due_remaining NUMBER;
2190 l_cash_receipt_id NUMBER;
2191 l_ps_amt_due_remain NUMBER;
2192 l_ps_due_date DATE; --Bug7194951
2193 l_return_status VARCHAR2(1);
2194 l_msg_data VARCHAR2(2000);
2195 lb_request_status BOOLEAN;
2196 ---identify all invoices in the autoinvoice batch that are prepaid
2197 ---for an auto invoice batch
2198 ---Bug: 3717795 Remove check on receipt methods
2199 ---Bug7194951(FP of 7146916) use union to use index for improving performance
2200 TYPE prepay_invoices_type IS REF CURSOR;
2201 get_prepay_invoices prepay_invoices_type;
2202 /*
2203 CURSOR get_prepay_invoices(p_req_id IN NUMBER ,p_batch_src IN VARCHAR2) IS
2204 SELECT
2205 ps.payment_schedule_id,
2206 ps.amount_due_remaining
2207 FROM
2208 ra_customer_trx ct,
2209 ar_payment_schedules ps
2210 WHERE ps.status = 'OP'
2211 AND ps.amount_due_remaining > 0
2212 AND ps.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD')
2213 --- Bug : 917451 in order to force the use of AR_PAYMENT_SCHEDULES_N9 --
2214 AND ps.selected_for_receipt_batch_id IS NULL
2215 ---AND ps.due_date +0 <= TO_DATE(SYSDATE) + TO_NUMBER(rm.lead_days)
2216 AND nvl(ct.prepayment_flag, 'N') = 'Y'
2217 AND ps.customer_trx_id = ct.customer_trx_id
2218 AND decode(p_batch_src,'All Invoices', '1', ct.request_id) =
2219 decode(p_batch_src,'All Invoices', '1' , p_req_id)
2220 ORDER BY ps.due_date;
2221 */
2222 BEGIN
2223
2224 -- Initialize message stack
2225 FND_MSG_PUB.initialize;
2226 FND_FILE.put_line(fnd_file.log,'Starting Concurrent Program ''Prepayment Matching ''');
2227 FND_FILE.put_line(fnd_file.log,'Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS' ));
2228
2229 ---get the parameter request_id
2230 l_request_id := p_request_id;
2231
2232 ---Validate that if p_batch_source is NOT 'ALL' then there is request_id
2233 if p_batch_source <> 'All Invoices' then
2234
2235 IF( l_request_id IS NULL OR l_request_id = 0 )THEN
2236 FND_MESSAGE.SET_NAME('AR','AR_PPAY_INVALID_REQ_ID');
2237 FND_MSG_PUB.Add;
2238 l_retcode := 10;
2239 RAISE FND_API.G_EXC_ERROR;
2240 END IF;
2241 end if;
2242
2243 SAVEPOINT prepay_start;
2244 --Bug7194951 (FP of Bug7146916) Start Here
2245 IF p_batch_source = 'All Invoices' then
2246 OPEN get_prepay_invoices for
2247 SELECT
2248 ps.payment_schedule_id,
2249 ps.amount_due_remaining,
2250 ps.due_date
2251 FROM
2252 ra_customer_trx ct,
2253 ar_payment_schedules ps
2254 WHERE ps.status = 'OP'
2255 AND ps.amount_due_remaining > 0
2256 AND ps.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD')
2257 AND ps.selected_for_receipt_batch_id IS NULL
2258 AND nvl(ct.prepayment_flag, 'N') = 'Y'
2259 AND ps.customer_trx_id = ct.customer_trx_id
2260 order by ps.due_date;
2261 ELSE
2262 OPEN get_prepay_invoices for
2263 SELECT
2264 ps.payment_schedule_id,
2265 ps.amount_due_remaining,
2266 ps.due_date
2267 FROM
2268 ra_customer_trx ct,
2269 ar_payment_schedules ps
2270 WHERE ps.status = 'OP'
2271 AND ps.amount_due_remaining > 0
2272 AND ps.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD')
2273 AND ps.selected_for_receipt_batch_id IS NULL
2274 AND nvl(ct.prepayment_flag, 'N') = 'Y'
2275 AND ps.customer_trx_id = ct.customer_trx_id
2276 AND p_batch_source = 'AutoInvoice Batch'
2277 AND ct.request_id = p_request_id
2278 order by ps.due_date;
2279 END IF;
2280 --Bug7194951 (FP of Bug7146916) End Here
2281
2282 LOOP
2283 fetch get_prepay_invoices into
2284 l_payment_schedule_id ,
2285 l_amt_due_remaining,l_ps_due_date; --Bug7194951
2286 EXIT when get_prepay_invoices%NOTFOUND;
2287
2288 ---Ajay, since there is no report exposing what happened? should be have them
2289 ---as regular log messages as fnd_file.put_line or this?
2290
2291 IF PG_DEBUG in ('Y', 'C') THEN
2292 arp_util.debug('AR-ARZPREPAY-bef: Payment Schedule Id '||
2293 l_payment_schedule_id);
2294 arp_util.debug('AR-ARZPREPAY-bef: PS Amt Due Remaining' ||
2295 l_amt_due_remaining);
2296 END IF;
2297
2298 /*-------------------------------------------------------------------------+
2299 |Call the match prepay routine
2300 +-------------------------------------------------------------------------*/
2301 IF PG_DEBUG in ('Y', 'C') THEN
2302 arp_util.debug('Starting ar_prepayments.match_prepayment Routine(+) ');
2303 END IF;
2304
2305 begin
2306 SAVEPOINT match_start;
2307
2308 ---Recipt API apply or unapply
2309 ar_prepayments.match_prepayment (
2310 p_payment_schedule_id => l_payment_schedule_id,
2311 p_apply_date => null,
2312 p_apply_gl_date => null,
2313 p_cash_receipt_id => l_cash_receipt_id,
2314 ps_amt_due_remain => l_ps_amt_due_remain,
2315 x_return_status => l_return_status);
2316
2317 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2318 l_retcode := 10;
2319 ELSE
2320 /* 9021126 - Commit the receipt application for this transaction to
2321 release locks on the various application rows */
2322 COMMIT;
2323 END IF;
2324
2325 IF PG_DEBUG in ('Y', 'C') then
2326 arp_util.debug('ar_prepayments.match_prepayment-after: P Cash Receipt Id >' || l_cash_receipt_id);
2327 arp_util.debug('AR-ARZPREPAY-after: PS Amt Due Remaining>'
2328 || l_ps_amt_due_remain);
2329 END IF;
2330
2331 EXCEPTION
2332
2333 WHEN others THEN
2334 fnd_file.put_line(fnd_file.log,'EXCEPTION :AR_PREPAYMENTS_PUB.MATCH_PREPAYMENT Routine'||SQLERRM);
2335 l_retcode := 10;
2336 ROLLBACK to match_start;
2337
2338 end; ---end to call to match_prepayment
2339
2340
2341 if (l_ps_amt_due_remain = -1) then
2342 fnd_file.put_line(fnd_file.log,' Payment Schedule Id '|| l_payment_schedule_id);
2343 end if;
2344
2345
2346 END LOOP;
2347 close get_prepay_invoices;
2348 /*bug 8372888*/
2349 COMMIT;
2350 IF PG_DEBUG in ('Y', 'C') THEN
2351 arp_util.debug('End ar_prepayments.match_prepayment Routine (-)');
2352 END IF;
2353
2354 if (l_retcode <> 0) then
2355 FND_FILE.put_line(fnd_file.log,'There are few prepayments which could not match. Pl check log file for details');
2356 lb_request_status := FND_CONCURRENT.set_completion_status('WARNING', '');
2357 -- RAISE FND_API.G_EXC_ERROR;
2358 end if;
2359
2360
2361 EXCEPTION
2362 WHEN fnd_api.g_exc_error THEN
2363 FND_MESSAGE.SET_NAME ('AR','AR_PREPAY_ERROR');
2364 FND_MSG_PUB.Add;
2365
2366 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
2367 l_msg_data :=
2368 substr(l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded =>
2369 FND_API.G_FALSE ), 1,255);
2370 END LOOP;
2371
2372 fnd_file.put_line(fnd_file.log, l_msg_data);
2373 FND_MSG_PUB.Delete_Msg;
2374 retcode := l_retcode;
2375
2376 END select_prepayments;
2377
2378 END AR_PREPAYMENTS;