[Home] [Help]
PACKAGE BODY: APPS.AR_PREPAYMENTS
Source
1 PACKAGE BODY AR_PREPAYMENTS AS
2 /* $Header: ARPREPYB.pls 120.21 2006/02/23 11:52:37 bichatte noship $ */
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 --PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
22 PG_DEBUG varchar2(1) := 'Y';
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
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('process_prepayments: ' ||
633 'Prepay Application ID :'||
634 p_prepay_application_id);
635 arp_util.debug('process_prepayments: ' ||
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('process_prepayments: ' || '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
1611 BEGIN
1612 SAVEPOINT match_prepayment_PVT;
1613
1614 IF PG_DEBUG in ('Y', 'C') THEN
1615 arp_util.debug('ar_prepayments.match_prepayment (+)');
1616 END IF;
1617 x_return_status := FND_API.G_RET_STS_SUCCESS;
1618 l_payment_schedule_id := p_payment_schedule_id;
1619
1620 BEGIN
1621 SELECT 'Y'
1622 INTO l_prepayment_exist_flag
1623 FROM ar_payment_schedules ps,
1624 ra_customer_trx ct
1625 WHERE ps.customer_trx_id =ct.customer_trx_id
1626 AND NVL(ct.prepayment_flag,'N') = 'Y'
1627 AND ps.payment_schedule_id=l_payment_schedule_id;
1628
1629 EXCEPTION
1630 WHEN no_data_found THEN
1631 IF PG_DEBUG in ('Y', 'C') THEN
1632 arp_util.debug('ar_prepayments.match_prepayment No prepayment exists');
1633 END IF;
1634 x_return_status := FND_API.G_RET_STS_SUCCESS;
1635 RETURN;
1636 WHEN others THEN
1637 IF PG_DEBUG in ('Y', 'C') THEN
1638 arp_util.debug('EXCEPTION :ar_prepayments.match_prepayment Check ');
1639 END IF;
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641 RETURN;
1642 END;
1643
1644 --fetch invoice payment schedule
1645 arp_ps_pkg.fetch_p(l_payment_schedule_id,l_ps_rec);
1646
1647 FOR paymentset_rec in paymentset_cur(l_ps_rec.customer_trx_id) LOOP
1648
1649 FOR prepayapp_rec in prepayapp_cur(paymentset_rec.payment_set_id) LOOP
1650
1651 p_cash_receipt_id := prepayapp_rec.cash_receipt_id;
1652
1653 --Unapply the prepayment
1654 ar_receipt_api_pub.Unapply_other_account(
1655 --Standard API parameters.
1656 p_api_version => 1.0,
1657 x_return_status => l_return_status,
1658 x_msg_count => l_msg_count,
1659 x_msg_data => l_msg_data,
1660 p_receipt_number => NULL,
1661 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1662 p_receivable_application_id => prepayapp_rec.receivable_application_id,
1663 p_reversal_gl_date => p_apply_gl_date
1664 );
1665
1666 IF PG_DEBUG in ('Y', 'C') THEN
1667 arp_util.debug('match_prepayment: ' || 'unapply_other_account retun status : '||l_return_status);
1668 END IF;
1669 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1670 x_return_status := FND_API.G_RET_STS_ERROR;
1671 EXIT;
1672 END IF;
1673
1674 IF NVL(l_ps_rec.amount_due_remaining,0) < NVL(prepayapp_rec.amount_applied,0)
1675 THEN
1676 l_amount_applied := NVL(l_ps_rec.amount_due_remaining,0);
1677 l_prepay_amount_reapplied := prepayapp_rec.amount_applied - l_ps_rec.amount_due_remaining;
1678 ELSE
1679 l_amount_applied := NVL(prepayapp_rec.amount_applied,0);
1680 END IF;
1681
1682 --Apply to invoice
1683 ar_receipt_api_pub.Apply(p_api_version => l_api_version,
1684 x_return_status => l_return_status,
1685 x_msg_count => l_msg_count,
1686 x_msg_data => l_msg_data,
1687 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1688 p_trx_number => l_ps_rec.trx_number,
1689 p_customer_trx_id => l_ps_rec.customer_trx_id,
1690 p_installment => l_ps_rec.terms_sequence_number,
1691 p_applied_payment_schedule_id => l_ps_rec.payment_schedule_id,
1692 p_amount_applied => l_amount_applied,
1693 p_apply_date => p_apply_date,
1694 p_apply_gl_date => p_apply_gl_date,
1695 p_called_from => 'PREPAYMENT',
1696 p_payment_set_id => prepayapp_rec.payment_set_id
1697 );
1698
1699 l_ps_rec.amount_due_remaining := (l_ps_rec.amount_due_remaining - l_amount_applied);
1700
1701 IF PG_DEBUG in ('Y', 'C') THEN
1702 arp_util.debug('match_prepayment: ' || 'Apply retun status : '||l_return_status);
1703 END IF;
1704
1705 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1706 x_return_status := FND_API.G_RET_STS_ERROR;
1707 END IF;
1708
1709 --Apply rest of the amount back to prepayment
1710 IF (l_prepay_amount_reapplied > 0) AND (x_return_status <> FND_API.G_RET_STS_ERROR) THEN
1711 --Assign atributes
1712 l_attribute_rec.attribute_category := prepayapp_rec.attribute_category;
1713 l_attribute_rec.attribute1 := prepayapp_rec.attribute1;
1714 l_attribute_rec.attribute2 := prepayapp_rec.attribute2;
1715 l_attribute_rec.attribute3 := prepayapp_rec.attribute3;
1716 l_attribute_rec.attribute4 := prepayapp_rec.attribute4;
1717 l_attribute_rec.attribute5 := prepayapp_rec.attribute5;
1718 l_attribute_rec.attribute6 := prepayapp_rec.attribute6;
1719 l_attribute_rec.attribute7 := prepayapp_rec.attribute7;
1720 l_attribute_rec.attribute8 := prepayapp_rec.attribute8;
1721 l_attribute_rec.attribute9 := prepayapp_rec.attribute9;
1722 l_attribute_rec.attribute10 := prepayapp_rec.attribute10;
1723 l_attribute_rec.attribute11 := prepayapp_rec.attribute11;
1724 l_attribute_rec.attribute12 := prepayapp_rec.attribute12;
1725 l_attribute_rec.attribute13 := prepayapp_rec.attribute13;
1726 l_attribute_rec.attribute14 := prepayapp_rec.attribute14;
1727 l_attribute_rec.attribute15 := prepayapp_rec.attribute15;
1728 l_global_attribute_rec.global_attribute_category :=
1729 prepayapp_rec.global_attribute_category;
1730 l_global_attribute_rec.global_attribute1 := prepayapp_rec.global_attribute1;
1731 l_global_attribute_rec.global_attribute2 := prepayapp_rec.global_attribute2;
1732 l_global_attribute_rec.global_attribute3 := prepayapp_rec.global_attribute3;
1733 l_global_attribute_rec.global_attribute4 := prepayapp_rec.global_attribute4;
1734 l_global_attribute_rec.global_attribute5 := prepayapp_rec.global_attribute5;
1735 l_global_attribute_rec.global_attribute6 := prepayapp_rec.global_attribute6;
1736 l_global_attribute_rec.global_attribute7 := prepayapp_rec.global_attribute7;
1737 l_global_attribute_rec.global_attribute8 := prepayapp_rec.global_attribute8;
1738 l_global_attribute_rec.global_attribute9 := prepayapp_rec.global_attribute9;
1739 l_global_attribute_rec.global_attribute10 := prepayapp_rec.global_attribute10;
1740 l_global_attribute_rec.global_attribute11 := prepayapp_rec.global_attribute11;
1741 l_global_attribute_rec.global_attribute12 := prepayapp_rec.global_attribute12;
1742 l_global_attribute_rec.global_attribute13 := prepayapp_rec.global_attribute13;
1743 l_global_attribute_rec.global_attribute14 := prepayapp_rec.global_attribute14;
1744 l_global_attribute_rec.global_attribute15 := prepayapp_rec.global_attribute15;
1745 l_global_attribute_rec.global_attribute16 := prepayapp_rec.global_attribute16;
1746 l_global_attribute_rec.global_attribute17 := prepayapp_rec.global_attribute17;
1747 l_global_attribute_rec.global_attribute18 := prepayapp_rec.global_attribute18;
1748 l_global_attribute_rec.global_attribute19 := prepayapp_rec.global_attribute19;
1749 l_global_attribute_rec.global_attribute20 := prepayapp_rec.global_attribute20;
1750
1751 ar_receipt_api_pub.Apply_other_account(
1752 -- Standard API parameters.
1753 p_api_version => l_api_version,
1754 x_return_status => l_return_status,
1755 x_msg_count => l_msg_count,
1756 x_msg_data => l_msg_data,
1757 p_receivable_application_id => l_receivable_application_id,
1758 --Receipt application parameters.
1759 p_cash_receipt_id => prepayapp_rec.cash_receipt_id,
1760 p_amount_applied => l_prepay_amount_reapplied,
1761 p_receivables_trx_id => prepayapp_rec.receivables_trx_id,
1762 p_applied_payment_schedule_id => -7,
1763 p_apply_date => prepayapp_rec.apply_date,
1764 p_apply_gl_date => p_apply_gl_date,
1765 p_ussgl_transaction_code => prepayapp_rec.ussgl_transaction_code,
1766 p_application_ref_type => prepayapp_rec.application_ref_type,
1767 p_application_ref_id => prepayapp_rec.application_ref_id,
1768 p_application_ref_num => prepayapp_rec.application_ref_num,
1769 p_secondary_application_ref_id => prepayapp_rec.secondary_application_ref_id,
1770 p_payment_set_id => prepayapp_rec.payment_set_id,
1771 p_attribute_rec => l_attribute_rec,
1772 -- ******* Global Flexfield parameters *******
1773 p_global_attribute_rec => l_global_attribute_rec
1774 );
1775
1776 IF PG_DEBUG in ('Y', 'C') THEN
1777 arp_util.debug('match_prepayment: ' || 'apply_other_account retun status : '||l_return_status);
1778 END IF;
1779
1780 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1781 x_return_status := FND_API.G_RET_STS_ERROR;
1782 END IF;
1783
1784 END IF;
1785
1786 ps_amt_due_remain := l_ps_rec.amount_due_remaining;
1787
1788 IF l_ps_rec.amount_due_remaining <= 0 THEN
1789 l_complete_applied_flag := TRUE;
1790 EXIT;
1791 END IF;
1792
1793 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1794 EXIT;
1795 END IF;
1796
1797
1798 END LOOP;
1799
1800 IF (l_complete_applied_flag) THEN
1801 EXIT;
1802 END IF;
1803
1804 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1805 EXIT;
1806 END IF;
1807
1808 END LOOP;
1809
1810 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1811 ROLLBACK to match_prepayment_PVT;
1812 IF NVL(l_msg_count,0) > 0 Then
1813 IF l_msg_count = 1 Then
1814 IF PG_DEBUG in ('Y', 'C') THEN
1815 arp_util.debug('match_prepayment: ' || l_msg_data);
1816 END IF;
1817
1818 ELSIF l_msg_count > 1 Then
1819 FOR l_count IN 1..l_msg_count LOOP
1820
1821 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
1822 IF PG_DEBUG in ('Y', 'C') THEN
1823 arp_util.debug('match_prepayment: ' || to_char(l_count)||' : '||l_msg_data);
1824 END IF;
1825 END LOOP;
1826
1827 END IF; -- l_msg_count
1828 END IF; -- NVL(l_msg_count,0)
1829 ELSE
1830 x_return_status := FND_API.G_RET_STS_SUCCESS;
1831 END IF;
1832
1833 IF PG_DEBUG in ('Y', 'C') THEN
1834 arp_util.debug('ar_prepayments.match_prepayment (-)');
1835 END IF;
1836
1837 EXCEPTION
1838 WHEN others THEN
1839 IF PG_DEBUG in ('Y', 'C') THEN
1840 arp_util.debug('EXCEPTION : ar_prepayments.match_prepayment ||SQLERRM');
1841 END IF;
1842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1843 END match_prepayment;
1844
1845 /*=======================================================================
1846 | PUBLIC Procedure get_installment
1847 |
1848 | DESCRIPTION
1849 | Gets the installment number and the amount due for a payment term
1850 | -----------------------------------------------------------------
1851 |
1852 | PSEUDO CODE/LOGIC
1853 |
1854 | PARAMETERS
1855 |
1856 |
1857 | RETURNS
1858 | nothing
1859 |
1860 | KNOWN ISSUES
1861 |
1862 |
1863 |
1864 | NOTES
1865 |
1866 |
1867 |
1868 | MODIFICATION HISTORY
1869 | Date Author Description of Changes
1870 | 10-JUL-2003 Jyoti Pandey o Created
1871 | 12-DEC-2003 Jyoti Pandey o Forward Port Bug 3316165 for Base bug
1872 | 3248093. Changing p_installment_tbl
1873 | ar_prepayments_pub.installment_tbl
1874 |
1875 *=======================================================================*/
1876 PROCEDURE get_installment(
1877 p_term_id IN NUMBER,
1878 p_amount IN NUMBER,
1879 p_currency_code IN VARCHAR2,
1880 --bug 3248093 --
1881 p_installment_tbl OUT NOCOPY ar_prepayments_pub.installment_tbl,
1882 x_return_status OUT NOCOPY VARCHAR2,
1883 x_msg_count OUT NOCOPY NUMBER,
1884 x_msg_data OUT NOCOPY VARCHAR2) IS
1885
1886 l_dummy VARCHAR2(1);
1887 i BINARY_INTEGER;
1888
1889 --Gets the installment amount and the installment number based
1890 --on the functional currency
1891 CURSOR get_installment_amount (l_term_id IN NUMBER, l_amount IN NUMBER ,
1892 l_currency_code IN VARCHAR2) IS
1893 select sequence_num as installment_number,
1894 arp_util.CurrRound( (relative_amount/base_amount ) * l_amount ,
1895 l_currency_code) as installment_amount
1896 from ra_terms t , ra_terms_lines tl
1897 where t.term_id = tl.term_id
1898 and t.term_id = l_term_id;
1899
1900 BEGIN
1901
1902 IF PG_DEBUG in ('Y', 'C') THEN
1903 arp_util.debug('ar_prepayment.get_installment (+)');
1904 END IF;
1905
1906 ---- first reinitialize ARP_GLOBAL
1907 arp_global.init_global;
1908
1909 /*-------------------------------------+
1910 | Standard start of API savepoint |
1911 +------------------------------------*/
1912 SAVEPOINT get_installment_PVT;
1913
1914 /*----------------------------------------+
1915 | Initialize return status to SUCCESS |
1916 +-----------------------------------------*/
1917 x_return_status := FND_API.G_RET_STS_SUCCESS;
1918
1919 /*-------Validate the p_term_id---------------*/
1920 IF p_term_id IS NULL THEN
1921 FND_MESSAGE.SET_NAME('AR','AR_PPAY_PAY_TERM_INVALID');
1922 FND_MSG_PUB.Add;
1923 RAISE FND_API.G_EXC_ERROR;
1924 ELSE
1925 BEGIN
1926 SELECT 1 into l_dummy
1927 FROM RA_TERMS_B
1928 WHERE term_id = p_term_id;
1929 EXCEPTION
1930 WHEN NO_DATA_FOUND THEN
1931 FND_MESSAGE.SET_NAME('AR','AR_PPAY_PAY_TERM_INVALID');
1932 FND_MSG_PUB.Add;
1933 RAISE FND_API.G_EXC_ERROR;
1934 END;
1935 END IF;
1936
1937 -----Input amount should not be null or 0 -------
1938 IF ( (p_amount is null) or (p_amount <= 0) ) THEN
1939 FND_MESSAGE.SET_NAME('AR','AR_PPAY_BASE_AMOUNT_INVALID');
1940 FND_MSG_PUB.Add;
1941 RAISE FND_API.G_EXC_ERROR;
1942 END IF;
1943
1944 -----Input currency code should not be invalid -------
1945 IF p_currency_code IS NULL THEN
1946 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
1947 FND_MSG_PUB.Add;
1948 RAISE FND_API.G_EXC_ERROR;
1949 ELSE
1950 BEGIN
1951 SELECT 1 into l_dummy
1952 FROM fnd_currencies
1953 WHERE currency_code = p_currency_code;
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND THEN
1956 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CURR_CODE_INVALID');
1957 FND_MSG_PUB.Add;
1958 RAISE FND_API.G_EXC_ERROR;
1959 END;
1960 END IF;
1961
1962
1963 i := 1;
1964 OPEN get_installment_amount(p_term_id, p_amount , p_currency_code);
1965 loop
1966 fetch get_installment_amount into
1967 p_installment_tbl(i).installment_number,
1968 p_installment_tbl(i).installment_amount;
1969
1970 exit when get_installment_amount%NOTFOUND;
1971
1972 i := i + 1;
1973 end loop;
1974 close get_installment_amount;
1975
1976 IF PG_DEBUG in ('Y', 'C') THEN
1977 arp_util.debug('ar_prepayment.get_installment (-)');
1978 END IF;
1979
1980 EXCEPTION
1981 WHEN fnd_api.g_exc_error THEN
1982
1983
1984 x_return_status := FND_API.G_RET_STS_ERROR;
1985 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1986 p_count => x_msg_count,
1987 p_data => x_msg_data);
1988
1989
1990 WHEN others THEN
1991 x_return_status := FND_API.G_RET_STS_ERROR;
1992
1993 --Bug 3107679 removed to_char call
1994 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1995 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','GET_INSTALLMENT : '||SQLERRM);
1996 FND_MSG_PUB.Add;
1997 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
1998 p_count => x_msg_count,
1999 p_data => x_msg_data);
2000
2001 ROLLBACK to get_installment_PVT;
2002
2003 END get_installment;
2004
2005 /*-------------------------------------------------------------------------+
2006 | FUNCTION NAME |
2007 | rule_select_prepayments |
2008 | |
2009 | DESCRIPTION |
2010 | Subscription to the business event AutoInvoice
2011 | |
2012 | PARAMETERS |
2013 | |
2014 | MODIFIES |
2015 | |
2016 | RETURNS |
2017 |
2018 | |
2019 +-------------------------------------------------------------------------*/
2020 FUNCTION rule_select_prepayments(
2021 p_subscription_guid in raw,
2022 p_event in out NOCOPY wf_event_t)RETURN VARCHAR2 IS
2023
2024 l_request_id NUMBER := null;
2025 l_conc_request_id NUMBER := null;
2026
2027 l_user_id NUMBER;
2028 l_resp_id NUMBER;
2029 l_application_id NUMBER;
2030 l_org_id NUMBER;
2031
2032 BEGIN
2033
2034 l_user_id := p_event.GetValueForParameter('USER_ID');
2035 l_resp_id := p_event.GetValueForParameter('RESP_ID');
2036 l_application_id := p_event.GetValueForParameter('RESP_APPL_ID');
2037 l_org_id := p_event.GetValueForParameter('ORG_ID');
2038
2039 SAVEPOINT Select_Prepay_Event;
2040
2041 --
2042 --set the application context.
2043 --
2044 fnd_global.apps_initialize(l_user_id,l_resp_id,l_application_id);
2045
2046
2047 IF PG_DEBUG in ('Y', 'C') THEN
2048 arp_util.debug('The rule_select_prepayments Subscription to AutoInvoice ''');
2049 arp_util.debug('Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS'));
2050 END IF;
2051
2052
2053 ---get the parameter request_id
2054 l_request_id := p_event.GetValueForParameter('REQUEST_ID');
2055
2056 IF l_request_id <> 0 AND l_request_id is not null THEN
2057 ---Make a callout to Concurrent program
2058 fnd_request.set_org_id(l_org_id);
2059 l_conc_request_id := fnd_request.submit_request('AR',
2060 'ARPREMAT',
2061 'Prepayment Matching Program',
2062 to_char(sysdate,'DD-MON-YY HH:MI:SS'),
2063 FALSE,
2064 'AutoInvoice Batch',
2065 l_request_id );
2066
2067 IF l_conc_request_id = 0 THEN
2068 FND_MESSAGE.SET_NAME('AR', 'AR_CUST_CONC_ERROR');
2069 FND_MSG_PUB.ADD;
2070 RAISE FND_API.G_EXC_ERROR;
2071 END IF;
2072 END IF;
2073
2074 RETURN 'SUCCESS';
2075
2076
2077 EXCEPTION
2078 WHEN OTHERS THEN
2079 ROLLBACK TO Select_Prepay_Event;
2080
2081 FND_MESSAGE.SET_NAME( 'AR', 'GENERIC_MESSAGE' );
2082 FND_MESSAGE.SET_TOKEN( 'GENERIC_TEXT' ,SQLERRM );
2083 FND_MSG_PUB.ADD;
2084
2085 WF_CORE.CONTEXT('AR_PREPAYMENTS', 'RULE_SELECT_PREPAYMENTS', p_event.getEventName(), p_subscription_guid);
2086 WF_EVENT.setErrorInfo(p_event, 'ERROR');
2087
2088 RETURN 'ERROR';
2089
2090
2091 END rule_select_prepayments;
2092
2093 /*=======================================================================
2094 | PUBLIC Procedure Select_Prepayments
2095 |
2096 | DESCRIPTION
2097 | Called from Concurrent program 'Prepayments Matching Program' to
2098 | match the prepaid receipts to their invoices
2099 | -----------------------------------------------------------------
2100 |
2101 | PSEUDO CODE/LOGIC
2102 |
2103 | PARAMETERS
2104 | p_batch_source : 'All Invoices' for matching all the invoices
2105 | 'AutoInvoice Batch' for matching invoices in a
2106 | particular AutoInvoice Batch
2107 | p_request_id : Populated only if p_batch_source = 'AutoInvoice Batch'
2108 | Request ID for the AutoInvoice Batch
2109 |
2110 |
2111 | RETURNS
2112 | nothing
2113 |
2114 | KNOWN ISSUES
2115 |
2116 |
2117 |
2118 | NOTES
2119 |
2120 |
2121 |
2122 | MODIFICATION HISTORY
2123 | Date Author Description of Changes
2124 | 10-JUL-2003 Jyoti Pandey o Created
2125 |
2126 *=======================================================================*/
2127 PROCEDURE Select_Prepayments ( errbuf OUT NOCOPY VARCHAR2,
2128 retcode OUT NOCOPY VARCHAR2,
2129 p_batch_source IN VARCHAR2,
2130 p_request_id IN NUMBER )
2131
2132 IS
2133
2134 l_retcode NUMBER := 0;
2135 l_request_id NUMBER := null;
2136 l_payment_schedule_id NUMBER;
2137 l_amt_due_remaining NUMBER;
2138 l_cash_receipt_id NUMBER;
2139 l_ps_amt_due_remain NUMBER;
2140 l_return_status VARCHAR2(1);
2141 l_msg_data VARCHAR2(2000);
2142
2143 ---identify all invoices in the autoinvoice batch that are prepaid
2144 ---for an auto invoice batch
2145 ---Bug: 3717795 Remove check on receipt methods
2146 CURSOR get_prepay_invoices(p_req_id IN NUMBER ,p_batch_src IN VARCHAR2) IS
2147 SELECT
2148 ps.payment_schedule_id,
2149 ps.amount_due_remaining
2150 FROM
2151 ra_customer_trx ct,
2152 ar_payment_schedules ps
2153 WHERE ps.status = 'OP'
2154 AND ps.amount_due_remaining > 0
2155 AND ps.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD')
2156 --- Bug : 917451 in order to force the use of AR_PAYMENT_SCHEDULES_N9 --
2157 AND ps.selected_for_receipt_batch_id IS NULL
2158 ---AND ps.due_date +0 <= TO_DATE(SYSDATE) + TO_NUMBER(rm.lead_days)
2159 AND nvl(ct.prepayment_flag, 'N') = 'Y'
2160 AND ps.customer_trx_id = ct.customer_trx_id
2161 AND decode(p_batch_src,'All Invoices', '1', ct.request_id) =
2162 decode(p_batch_src,'All Invoices', '1' , p_req_id)
2163 ORDER BY ps.due_date;
2164
2165 BEGIN
2166
2167 -- Initialize message stack
2168 FND_MSG_PUB.initialize;
2169 FND_FILE.put_line(fnd_file.log,'Starting Concurrent Program ''Prepayment Matching ''');
2170 FND_FILE.put_line(fnd_file.log,'Start Time ' || TO_CHAR(SYSDATE, 'MM-DD-YY HH24:MI:SS' ));
2171
2172 ---get the parameter request_id
2173 l_request_id := p_request_id;
2174
2175 ---Validate that if p_batch_source is NOT 'ALL' then there is request_id
2176 if p_batch_source <> 'All Invoices' then
2177
2178 IF( l_request_id IS NULL OR l_request_id = 0 )THEN
2179 FND_MESSAGE.SET_NAME('AR','AR_PPAY_INVALID_REQ_ID');
2180 FND_MSG_PUB.Add;
2181 l_retcode := 10;
2182 RAISE FND_API.G_EXC_ERROR;
2183 END IF;
2184 end if;
2185
2186 SAVEPOINT prepay_start;
2187 OPEN get_prepay_invoices(l_request_id , p_batch_source);
2188 LOOP
2189 fetch get_prepay_invoices into
2190 l_payment_schedule_id ,
2191 l_amt_due_remaining;
2192
2193 EXIT when get_prepay_invoices%NOTFOUND;
2194
2195
2196 ---Ajay, since there is no report exposing what happened? should be have them
2197 ---as regular log messages as fnd_file.put_line or this?
2198
2199 IF PG_DEBUG in ('Y', 'C') THEN
2200 arp_util.debug('AR-ARZPREPAY-bef: Payment Schedule Id '||
2201 l_payment_schedule_id);
2202 arp_util.debug('AR-ARZPREPAY-bef: PS Amt Due Remaining' ||
2203 l_amt_due_remaining);
2204 END IF;
2205
2206 /*-------------------------------------------------------------------------+
2207 |Call the match prepay routine
2208 +-------------------------------------------------------------------------*/
2209 IF PG_DEBUG in ('Y', 'C') THEN
2210 arp_util.debug('Starting ar_prepayments.match_prepayment Routine(+) ');
2211 END IF;
2212
2213 begin
2214 SAVEPOINT match_start;
2215
2216 ---Recipt API apply or unapply
2217 ar_prepayments.match_prepayment (
2218 p_payment_schedule_id => l_payment_schedule_id,
2219 p_apply_date => null,
2220 p_apply_gl_date => null,
2221 p_cash_receipt_id => l_cash_receipt_id,
2222 ps_amt_due_remain => l_ps_amt_due_remain,
2223 x_return_status => l_return_status);
2224
2225 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2226 l_retcode := 10;
2227 END IF;
2228
2229 IF PG_DEBUG in ('Y', 'C') then
2230 arp_util.debug('ar_prepayments.match_prepayment-after: P Cash Receipt Id >' || l_cash_receipt_id);
2231 arp_util.debug('AR-ARZPREPAY-after: PS Amt Due Remaining>'
2232 || l_ps_amt_due_remain);
2233 END IF;
2234
2235
2236 EXCEPTION
2237
2238 WHEN others THEN
2239
2240 fnd_file.put_line(fnd_file.log,'EXCEPTION :AR_PREPAYMENTS_PUB.MATCH_PREPAYMENT Routine'||SQLERRM);
2241 l_retcode := 10;
2242 ROLLBACK to match_start;
2243
2244 end; ---end to call to match_prepayment
2245
2246
2247 if (l_ps_amt_due_remain = -1) then
2248 fnd_file.put_line(fnd_file.log,' Payment Schedule Id '|| l_payment_schedule_id);
2249 end if;
2250
2251
2252 END LOOP;
2253 close get_prepay_invoices;
2254 IF PG_DEBUG in ('Y', 'C') THEN
2255 arp_util.debug('End ar_prepayments.match_prepayment Routine (-)');
2256 END IF;
2257
2258 if (l_retcode <> 0) then
2259 RAISE FND_API.G_EXC_ERROR;
2260 end if;
2261
2262
2263 EXCEPTION
2264 WHEN fnd_api.g_exc_error THEN
2265 FND_MESSAGE.SET_NAME ('AR','AR_PREPAY_ERROR');
2266 FND_MSG_PUB.Add;
2267
2268 FOR I IN 1..FND_MSG_PUB.Count_Msg LOOP
2269 l_msg_data :=
2270 substr(l_msg_data || ' ' || FND_MSG_PUB.Get(p_encoded =>
2271 FND_API.G_FALSE ), 1,255);
2272 END LOOP;
2273 fnd_file.put_line(fnd_file.log, l_msg_data);
2274 FND_MSG_PUB.Delete_Msg;
2275 retcode := l_retcode;
2276
2277 END select_prepayments;
2278
2279 END AR_PREPAYMENTS;