[Home] [Help]
PACKAGE BODY: APPS.ARP_DEDUCTION_COVER
Source
1 PACKAGE BODY ARP_DEDUCTION_COVER AS
2 /* $Header: ARXDECVB.pls 120.26.12010000.3 2008/10/20 13:25:38 spdixit ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARP_DEDUCTION_COVER';
8
9
10 /*========================================================================
11 | Prototype Declarations Procedures
12 *=======================================================================*/
13
14
15 /*========================================================================
16 | Prototype Declarations Functions
17 *=======================================================================*/
18
19 /*========================================================================
20 | PUBLIC PROCEDURE update_amount_in_dispute
21 |
22 | DESCRIPTION
23 | ----------------------------------------
24 | This procedure calls entity handlers to update the amount_in_dispute
25 | on the given transaction's payment schedule and inserts a note
26 | on the transaction
27 |
28 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
29 |
30 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
31 |
32 | PARAMETERS
33 | p_customer_trx_id IN Transaction whose dispute amount is changed
34 | p_claim_number IN Number of claim
35 | p_amount IN Amount of adjustment to dispute amount
36 | p_init_msg_list IN API message stack initialize flag
37 | x_return_status OUT NOCOPY
38 | x_msg_count OUT NOCOPY
39 | x_msg_data OUT NOCOPY
40 |
41 | KNOWN ISSUES
42 |
43 | NOTES
44 |
45 | MODIFICATION HISTORY
46 | Date Author Description of Changes
47 | 12-OCT-2001 jbeckett Created
48 |
49 *=======================================================================*/
50 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
51
52 PROCEDURE update_amount_in_dispute(
53 p_customer_trx_id IN NUMBER,
54 p_claim_number IN VARCHAR2,
55 p_amount IN NUMBER,
56 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
57 x_return_status OUT NOCOPY VARCHAR2,
58 x_msg_count OUT NOCOPY NUMBER,
59 x_msg_data OUT NOCOPY VARCHAR2)
60 IS
61
62 l_ps_rec ar_payment_schedules%ROWTYPE;
63 l_payment_schedule_id NUMBER;
64 l_amount_in_dispute NUMBER;
65 l_amount_in_dispute_new NUMBER;
66 l_note_text VARCHAR2(2000);
67 l_note_id NUMBER;
68 l_error_count NUMBER := 0;
69 l_api_name CONSTANT VARCHAR2(30)
70 := 'update_amount_in_dispute';
71 l_claim_id NUMBER;
72 l_active_claim_flag ar_payment_schedules.active_claim_flag%TYPE;
73
74 BEGIN
75 IF PG_DEBUG in ('Y', 'C') THEN
76 arp_standard.debug('ARP_DEDUCTION_COVER.update_amount_in_dispute()+');
77 END IF;
78
79 -- Standard Start of API savepoint
80 SAVEPOINT Update_amount_In_Dispute;
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean( p_init_msg_list )
83 THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86 -- Initialize API return status to success
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 IF PG_DEBUG in ('Y', 'C') THEN
90 arp_standard.debug('update_amount_in_dispute: ' || ' p_customer_trx_id :' || p_customer_trx_id);
91 arp_standard.debug('update_amount_in_dispute: ' || ' p_claim_number :' || p_claim_number);
92 arp_standard.debug('update_amount_in_dispute: ' || ' p_amount :' || p_amount);
93 END IF;
94 /*---------------------------------------------------------------------+
95 | 1) Retrieve the invoice payment schedule id from the application |
96 +---------------------------------------------------------------------*/
97
98 BEGIN
99 SELECT applied_payment_schedule_id
100 , secondary_application_ref_id
101 INTO l_payment_schedule_id
102 , l_claim_id
103 FROM ar_receivable_applications
104 WHERE applied_customer_trx_id = p_customer_trx_id
105 AND application_ref_num = p_claim_number
106 AND application_ref_type = 'CLAIM'
107 AND display = 'Y'
108 AND ROWNUM = 1;
109 EXCEPTION
110 WHEN OTHERS THEN
111 FND_MESSAGE.SET_NAME('AR','ARTA_PAYMENT_SCHEDULE_NO_FOUND');
112 FND_MSG_PUB.Add;
113 l_error_count := l_error_count + 1;
114 END;
115
116 /*---------------------------------------------------------------------+
117 | 2) Update payment schedule record |
118 +---------------------------------------------------------------------*/
119
120 IF (l_payment_schedule_id > 0) THEN
121 IF (ARPT_SQL_FUNC_UTIL.get_claim_amount(l_claim_id) = 0) THEN
122 l_active_claim_flag := 'N';
123 ELSE
124 l_active_claim_flag := 'Y';
125 END IF;
126 END IF;
127
128 IF l_error_count > 0
129 THEN
130 RAISE FND_API.G_EXC_ERROR;
131 ELSE
132 arp_ps_pkg.set_to_dummy(p_ps_rec => l_ps_rec);
133 SELECT amount_in_dispute
134 INTO l_amount_in_dispute
135 FROM ar_payment_schedules
136 WHERE payment_schedule_id = l_payment_schedule_id;
137
138 l_ps_rec.payment_schedule_id := l_payment_schedule_id;
139 l_amount_in_dispute_new := l_amount_in_dispute + p_amount;
140 l_ps_rec.amount_in_dispute := l_amount_in_dispute_new;
141 l_ps_rec.dispute_date := SYSDATE;
142 l_ps_rec.active_claim_flag := l_active_claim_flag;
143
144 IF PG_DEBUG in ('Y', 'C') THEN
145 arp_standard.debug('update_amount_in_dispute: ' || 'New amount in dispute = '||l_ps_rec.amount_in_dispute);
146 arp_standard.debug('update_amount_in_dispute: ' || 'New dispute date = '||l_ps_rec.dispute_date);
147 END IF;
148
149 arp_ps_pkg.update_p
150 (p_ps_rec => l_ps_rec,
151 p_ps_id => l_payment_schedule_id);
152
153 /*---------------------------------------------------------------------+
154 | 2) Enter a note on the transaction |
155 +---------------------------------------------------------------------*/
156 FND_MESSAGE.set_name('AR','AR_RW_TRX_CLAIM_SETTLE_NOTE');
157 FND_MESSAGE.set_token('CLAIM_NUM',p_claim_number);
158 FND_MESSAGE.set_token('AMOUNT',p_amount);
159 l_note_text := FND_MESSAGE.get;
160
161 arp_cmreq_wf.InsertTrxNotes
162 (x_customer_call_id => NULL,
163 x_customer_call_topic_id => NULL,
164 x_action_id => NULL,
165 x_customer_trx_id => p_customer_trx_id,
166 x_note_type => 'MAINTAIN',
167 x_text => l_note_text,
168 x_note_id => l_note_id);
169 END IF;
170
171 IF PG_DEBUG in ('Y', 'C') THEN
172 arp_standard.debug('ARP_DEDUCTION_COVER.update_amount_in_dispute()-');
173 END IF;
174
175 EXCEPTION
176 WHEN FND_API.G_EXC_ERROR THEN
177 ROLLBACK TO Update_Amount_In_Dispute;
178 x_return_status := FND_API.G_RET_STS_ERROR ;
179 FND_MSG_PUB.Count_And_Get
180 (p_encoded => FND_API.G_FALSE,
181 p_count => x_msg_count,
182 p_data => x_msg_data);
183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
184 IF PG_DEBUG in ('Y', 'C') THEN
185 arp_standard.debug('update_amount_in_dispute: ' || 'Unexpected error '||sqlerrm||
186 ' at arp_deduction_cover.update_amount_in_dispute()+');
187 END IF;
188 ROLLBACK TO Update_Amount_In_Dispute;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
190 FND_MSG_PUB.Count_And_Get
191 (p_encoded => FND_API.G_FALSE,
192 p_count => x_msg_count,
193 p_data => x_msg_data);
194 WHEN OTHERS THEN
195 IF (SQLCODE = -20001)
196 THEN
197 IF PG_DEBUG in ('Y', 'C') THEN
198 arp_util.debug('update_amount_in_dispute: ' || '20001 error '||
199 ' at arp_deduction_cover.update_amount_in_dispute()+');
200 END IF;
201 x_return_status := FND_API.G_RET_STS_ERROR ;
202 ELSE
203 IF PG_DEBUG in ('Y', 'C') THEN
204 arp_util.debug('update_amount_in_dispute: ' || 'Unexpected error '||sqlerrm||
205 ' at arp_deduction_cover.update_amount_in_dispute()+');
206 END IF;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 IF FND_MSG_PUB.Check_Msg_Level
209 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
210 THEN
211 FND_MSG_PUB.Add_Exc_Msg
212 ( G_PKG_NAME ,
213 l_api_name
214 );
215 END IF;
216 END IF;
217 ROLLBACK TO Update_Amount_In_Dispute;
218 FND_MSG_PUB.Count_And_Get
219 (p_encoded => FND_API.G_FALSE,
220 p_count => x_msg_count,
221 p_data => x_msg_data);
222
223 END update_amount_in_dispute;
224
225 /*========================================================================
226 | PUBLIC PROCEDURE create_receipt_writeoff
227 |
228 | DESCRIPTION
229 | ----------------------------------------
230 | This procedure calls entity handlers to unapply the claim investigation
231 | application associated with the given claim, then to apply the same to
232 | receipt write off activity
233 |
234 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
235 |
236 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
237 |
238 | PARAMETERS
239 | p_claim_id IN ID of claim being written off
240 | p_amount IN Amount to be written off
241 | p_new_claim_id IN ID of claim to apply balance to
242 | p_init_msg_list IN API message stack initialize flag
243 | p_cash_receipt_id IN ID of receipt for which claim originally
244 | created
245 | p_receivables_trx_id IN ID of write off activity
246 | p_ussgl_transaction_code IN Default value for USSGL trx code flexfield
247 | x_return_status OUT NOCOPY
248 | x_msg_count OUT NOCOPY
249 | x_msg_data OUT NOCOPY
250 |
251 | KNOWN ISSUES
252 |
253 | NOTES
254 |
255 | MODIFICATION HISTORY
256 | Date Author Description of Changes
257 | 12-OCT-2001 jbeckett Created
258 | 09-MAY-2002 jbeckett Bug 2353144 - Replaced calls to entity
259 | handlers with calls to receipt API
260 | 08-OCT-2002 jbeckett Bug 2615618 - GL date defaulted according to
261 | profile option AR_APPLICATION_GL_DATE_DEFAULT
262 | and apply_date allowed to default instead of
263 | using receipt date and original apply date.
264 | 04-MAR-2003 jbeckett Added secondary_application_reference_id,
265 | application_ref_num and customer reference
266 | Bug 2751910.
267 *=======================================================================*/
268 PROCEDURE create_receipt_writeoff
269 (p_claim_id IN NUMBER,
270 p_amount IN NUMBER,
271 p_new_claim_id IN NUMBER,
272 p_init_msg_list IN VARCHAR2,
273 p_cash_receipt_id IN NUMBER,
274 p_receivables_trx_id IN NUMBER,
275 p_ussgl_transaction_code IN NUMBER,
276 p_application_ref_num IN
277 ar_receivable_applications.application_ref_num%TYPE,
278 p_secondary_application_ref_id IN
279 ar_receivable_applications.secondary_application_ref_id%TYPE,
280 p_customer_reference IN
281 ar_receivable_applications.customer_reference%TYPE,
282 x_return_status OUT NOCOPY VARCHAR2,
283 x_msg_count OUT NOCOPY NUMBER,
284 x_msg_data OUT NOCOPY VARCHAR2)
285 IS
286
287 l_set_of_books_id NUMBER;
288 l_application_id NUMBER;
289 l_apply_date DATE;
290 l_app_gl_date DATE;
291 l_amount NUMBER;
292 l_bal_due_remaining NUMBER;
293 l_receivable_application_id NUMBER;
294 l_application_ref_id NUMBER := NULL;
295 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE := NULL;
296 l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE := NULL;
297 l_application_ref_reason ar_receivable_applications.application_ref_reason%TYPE;
298 l_dum_app_ref_type ar_receivable_applications.application_ref_type%TYPE;
299 l_dum_app_ref_id NUMBER;
300 l_dum_app_ref_num ar_receivable_applications.application_ref_num%TYPE;
301 l_dum_sec_app_ref_id NUMBER;
302 l_payment_set_id NUMBER;
303 l_claim_receivables_trx_id NUMBER;
304 l_comments ar_receivable_applications.comments%TYPE;
305 l_customer_reference ar_receivable_applications.customer_reference%TYPE;
306 l_attribute_rec AR_Receipt_API_PUB.attribute_rec_type;
307 l_global_attribute_rec AR_Receipt_API_PUB.global_attribute_rec_type;
308 l_balance NUMBER := 0;
309 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
310 l_receipt_date DATE;
311 l_currency_code ar_cash_receipts.currency_code%TYPE;
312 l_trx_currency_code ar_payment_schedules.invoice_currency_code%TYPE;
313 l_cr_gl_date DATE;
314 l_cr_payment_schedule_id NUMBER;
315 l_customer_id NUMBER;
316 l_default_gl_date DATE;
317 l_defaulting_rule_used VARCHAR2(50);
318 l_new_application_id NUMBER;
319 l_receivables_trx_id NUMBER;
320 l_trans_to_receipt_rate NUMBER;
321 l_discount_earned NUMBER;
322 l_discount_unearned NUMBER;
323 l_new_claim_num ar_receivable_applications.application_ref_num%TYPE;
324 l_return_status VARCHAR2(1);
325 l_api_name CONSTANT VARCHAR2(30)
326 := 'create_receipt_writeoff';
327 l_error_message VARCHAR2(2000);
328 l_error_count NUMBER := 0;
329 l_msg_count NUMBER;
330 l_msg_data VARCHAR2(2000);
331 l_new_claim_id NUMBER;
332 l_app_gl_date_prof VARCHAR2(240); -- bug 2615618
333 l_gl_date DATE; -- bug 2615618
334 l_claim_applied VARCHAR2(1);
335
336 BEGIN
337
338 IF PG_DEBUG in ('Y', 'C') THEN
339 arp_standard.debug('ARP_DEDUCTION_COVER.create_receipt_writeoff()+');
340 END IF;
341
342 -- Standard Start of API savepoint
343 SAVEPOINT Create_Receipt_Writeoff;
344 -- Initialize message list if p_init_msg_list is set to TRUE.
345 IF FND_API.to_Boolean( p_init_msg_list )
346 THEN
347 FND_MSG_PUB.initialize;
348 END IF;
349 -- Initialize API return status to success
350 x_return_status := FND_API.G_RET_STS_SUCCESS;
351
352 /* Bug 3022077 - initialize global variables */
353 arp_global.init_global;
354 arp_standard.init_standard;
355
356 /*---------------------------------------------------------------------+
357 | 1) Check that a valid writeoff activity has been passed |
358 +---------------------------------------------------------------------*/
359 IF (p_receivables_trx_id IS NULL)
360 THEN
361 FND_MESSAGE.SET_NAME('AR','AR_WR_INVALID_ACTIVITY_ID');
362 FND_MSG_PUB.Add;
363 l_error_count := l_error_count + 1;
364 ELSE
365 BEGIN
366 SELECT receivables_trx_id
367 INTO l_receivables_trx_id
368 FROM ar_receivables_trx
369 WHERE receivables_trx_id = p_receivables_trx_id
370 AND NVL(status,'A') = 'A'
371 AND TRUNC(SYSDATE) BETWEEN NVL(start_date_active,TRUNC(SYSDATE))
372 AND NVL(end_date_active,trunc(SYSDATE))
373 AND type = 'WRITEOFF';
374 EXCEPTION
375 WHEN NO_DATA_FOUND
376 THEN
377 FND_MESSAGE.SET_NAME('AR','AR_WR_INVALID_ACTIVITY_ID');
378 FND_MSG_PUB.Add;
379 l_error_count := l_error_count + 1;
380 END;
381 END IF;
382
383 /*---------------------------------------------------------------------+
384 | 2) Check that a valid receipt has been passed |
385 +---------------------------------------------------------------------*/
386 IF NOT receipt_valid
387 (p_cash_receipt_id => p_cash_receipt_id,
388 x_receipt_number => l_receipt_number,
389 x_receipt_date => l_receipt_date,
390 x_cr_gl_date => l_cr_gl_date,
391 x_customer_id => l_customer_id,
392 x_currency_code => l_currency_code,
393 x_cr_payment_schedule_id => l_cr_payment_schedule_id)
394 THEN
395 l_error_count := l_error_count + 1;
396 END IF;
397
398 /*---------------------------------------------------------------------+
399 | 3) Check that a valid claim has been passed |
400 +---------------------------------------------------------------------*/
401 IF NOT claim_on_receipt (
402 p_claim_id => p_claim_id
403 , p_cash_receipt_id => p_cash_receipt_id
404 , p_applied_ps_id => -4
405 , x_application_id => l_application_id
406 , x_apply_date => l_apply_date
407 , x_app_gl_date => l_app_gl_date
408 , x_amount_applied => l_amount
409 , x_trans_to_receipt_rate => l_trans_to_receipt_rate
410 , x_discount_earned => l_discount_earned
411 , x_discount_unearned => l_discount_unearned
412 , x_application_ref_num => l_dum_app_ref_num
413 , x_application_ref_reason => l_application_ref_reason
414 , x_receivables_trx_id => l_claim_receivables_trx_id
415 , x_comments => l_comments
416 , x_customer_reference => l_customer_reference
417 , x_attribute_rec => l_attribute_rec
418 , x_global_attribute_rec => l_global_attribute_rec
419 , x_claim_applied => l_claim_applied)
420 THEN
421 l_error_count := l_error_count + 1;
422 END IF;
423
424 /*---------------------------------------------------------------------+
425 | 4) Check that a valid new claim has been passed if partial writeoff |
426 +---------------------------------------------------------------------*/
427 IF (l_amount <> p_amount)
428 THEN
429 l_balance := (l_amount - p_amount);
430 IF p_new_claim_id IS NULL
431 THEN
432 FND_MESSAGE.set_name('AR','AR_RWAPP_NEW_CLAIM_ID_NULL');
433 FND_MSG_PUB.Add;
434 l_error_count := l_error_count + 1;
435 ELSE
436 IF NOT claim_valid(
437 p_claim_id => p_new_claim_id,
438 p_receipt_id => p_cash_receipt_id,
439 p_curr_code => l_currency_code,
440 p_amount => l_balance,
441 x_claim_num => l_new_claim_num)
442 THEN
443 l_error_count := l_error_count + 1;
444 END IF;
445 END IF;
446 END IF;
447
448 /*---------------------------------------------------------------------+
449 | 5) Validate and default the GL date (bug 2615618)
450 +---------------------------------------------------------------------*/
451 l_gl_date := NULL;
452 l_app_gl_date_prof :=
453 NVL(fnd_profile.value('AR_APPLICATION_GL_DATE_DEFAULT'),'INV_REC_DT');
454 IF (l_app_gl_date_prof = 'INV_REC_DT') THEN
455 IF l_cr_gl_date > l_app_gl_date THEN
456 l_gl_date := l_cr_gl_date;
457 END IF;
458 ELSIF (l_app_gl_date_prof = 'INV_REC_SYS_DT') THEN
459 IF l_cr_gl_date > SYSDATE THEN
460 l_gl_date := l_cr_gl_date;
461 ELSE
462 l_gl_date := SYSDATE;
463 END IF;
464 END IF;
465 IF (arp_util.validate_and_default_gl_date(
466 l_gl_date,
467 NULL,
468 NULL,
469 NULL,
470 NULL,
471 NULL,
472 NULL,
473 NULL,
474 'N',
475 NULL,
476 l_set_of_books_id,
477 222,
478 l_default_gl_date,
479 l_defaulting_rule_used,
480 l_error_message) = TRUE)
481 THEN
482 NULL;
483 ELSE
484 FND_MESSAGE.SET_NAME('AR', 'GENERIC_MESSAGE');
485 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT', l_error_message);
486 FND_MSG_PUB.Add;
487 l_error_count := l_error_count + 1;
488 END IF;
489
490 /*---------------------------------------------------------------------+
491 | 6) Raise error if validation errors found |
492 +---------------------------------------------------------------------*/
493 IF (l_error_count > 0)
494 THEN
495 RAISE FND_API.G_EXC_ERROR;
496 END IF;
497
498 /*---------------------------------------------------------------------+
499 | 7) Unapply the claim investigation application |
500 +---------------------------------------------------------------------*/
501 /* Bug 2821139 - under no circumstances should AR update claims when
502 they are settled from TM */
503
504 IF l_claim_applied = 'Y' THEN
505 AR_Receipt_API_PUB.unapply_other_account (
506 p_api_version => 1.0,
507 p_init_msg_list => FND_API.G_FALSE,
508 x_return_status => l_return_status,
509 x_msg_count => l_msg_count,
510 x_msg_data => l_msg_data,
511 p_receivable_application_id => l_application_id,
512 p_cash_receipt_id => p_cash_receipt_id,
513 p_cancel_claim_flag => 'N',
514 p_called_from => 'TRADE_MANAGEMENT');
515 END IF;
516
517 /*---------------------------------------------------------------------+
518 | 8) Apply any remaining balance to new claim |
519 +---------------------------------------------------------------------*/
520 IF l_balance <> 0
521 THEN
522 IF PG_DEBUG in ('Y', 'C') THEN
523 arp_standard.debug('create_receipt_writeoff: ' || 'Before Inserting Claim Application (+)');
524 END IF;
525 l_new_claim_id := p_new_claim_id;
526 /* Bug 2821139 - under no circumstances should AR update claims when
527 they are settled from TM */
528
529 ar_receipt_api_pub.apply_other_account (
530 p_api_version => 1.0,
531 p_init_msg_list => FND_API.G_FALSE,
532 x_return_status => l_return_status,
533 x_msg_count => l_msg_count,
534 x_msg_data => l_msg_data,
535 p_receivable_application_id => l_receivable_application_id,
536 p_cash_receipt_id => p_cash_receipt_id,
537 p_amount_applied => l_balance,
538 p_receivables_trx_id => l_claim_receivables_trx_id,
539 p_apply_date => l_apply_date,
540 p_apply_gl_date => l_default_gl_date, -- bug 2615618
541 p_applied_payment_schedule_id => -4,
542 p_ussgl_transaction_code => p_ussgl_transaction_code,
543 p_application_ref_type => 'CLAIM',
544 p_application_ref_id => l_application_ref_id,
545 p_application_ref_num => l_new_claim_num,
546 p_secondary_application_ref_id => l_new_claim_id,
547 p_attribute_rec => l_attribute_rec,
548 p_global_attribute_rec => l_global_attribute_rec,
549 p_comments => l_comments,
550 p_application_ref_reason => l_application_ref_reason,
551 p_customer_reference => l_customer_reference,
552 p_called_from => 'TRADE_MANAGEMENT');
553
554 IF l_return_status = 'E'
555 THEN
556 RAISE FND_API.G_EXC_ERROR;
557 ELSIF l_return_status = 'U'
558 THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561 IF PG_DEBUG in ('Y', 'C') THEN
562 arp_standard.debug('create_receipt_writeoff: ' || 'After Inserting Claim Application (-)');
563 END IF;
564 END IF;
565
566 /*---------------------------------------------------------------------+
567 | 9) Apply to writeoff application using Receipt API |
568 +---------------------------------------------------------------------*/
569
570 l_application_ref_num := p_application_ref_num;
571 l_secondary_application_ref_id := p_secondary_application_ref_id;
572
573 AR_Receipt_API_PUB.Activity_Application (
574 p_api_version => 1.0,
575 x_return_status => l_return_status,
576 x_msg_count => l_msg_count,
577 x_msg_data => l_msg_data,
578 p_cash_receipt_id => p_cash_receipt_id,
579 p_amount_applied => p_amount,
580 p_applied_payment_schedule_id => -3,
581 p_receivables_trx_id => l_receivables_trx_id,
582 p_apply_date => NULL, -- bug 2615618
583 p_apply_gl_date => l_default_gl_date, -- bug 2615618
584 p_ussgl_transaction_code => p_ussgl_transaction_code,
585 p_attribute_rec => l_attribute_rec,
586 p_global_attribute_rec => l_global_attribute_rec,
587 p_application_ref_type => l_dum_app_ref_type,
588 p_application_ref_id => l_dum_app_ref_id,
589 p_application_ref_num => l_application_ref_num,
590 p_secondary_application_ref_id => l_secondary_application_ref_id,
591 p_receivable_application_id => l_new_application_id,
592 p_customer_reference => p_customer_reference,
593 p_val_writeoff_limits_flag => 'N'
594 );
595
596 IF l_return_status = 'E'
597 THEN
598 RAISE FND_API.G_EXC_ERROR;
599 ELSIF l_return_status = 'U'
600 THEN
601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
602 END IF;
603
604 IF PG_DEBUG in ('Y', 'C') THEN
605 arp_standard.debug('ARP_DEDUCTION_COVER.create_receipt_writeoff()-');
606 END IF;
607
608 EXCEPTION
609 WHEN FND_API.G_EXC_ERROR THEN
610 ROLLBACK TO Create_Receipt_Writeoff;
611 x_return_status := FND_API.G_RET_STS_ERROR ;
612 FND_MSG_PUB.Count_And_Get
613 (p_encoded => FND_API.G_FALSE,
614 p_count => x_msg_count,
615 p_data => x_msg_data);
616 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
617 IF PG_DEBUG in ('Y', 'C') THEN
618 arp_standard.debug('create_receipt_writeoff: ' || 'Unexpected error '||sqlerrm||
619 ' at arp_deduction_cover.create_receipt_writeoff()+');
620 END IF;
621 ROLLBACK TO Create_Receipt_Writeoff;
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
623 FND_MSG_PUB.Count_And_Get
624 (p_encoded => FND_API.G_FALSE,
625 p_count => x_msg_count,
626 p_data => x_msg_data);
627 WHEN OTHERS THEN
628 IF (SQLCODE = -20001)
629 THEN
630 IF PG_DEBUG in ('Y', 'C') THEN
631 arp_util.debug('create_receipt_writeoff: ' || '20001 error '||
632 ' at arp_deduction_cover.create_receipt_writeoff()+');
633 END IF;
634 x_return_status := FND_API.G_RET_STS_ERROR ;
635 ELSE
636 IF PG_DEBUG in ('Y', 'C') THEN
637 arp_util.debug('create_receipt_writeoff: ' || 'Unexpected error '||sqlerrm||
638 ' at arp_deduction_cover.create_receipt_writeoff()+');
639 END IF;
640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
641 IF FND_MSG_PUB.Check_Msg_Level
642 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
643 THEN
644 FND_MSG_PUB.Add_Exc_Msg
645 ( G_PKG_NAME ,
646 l_api_name
647 );
648 END IF;
649 END IF;
650 ROLLBACK TO Create_Receipt_Writeoff;
651 FND_MSG_PUB.Count_And_Get
652 (p_encoded => FND_API.G_FALSE,
653 p_count => x_msg_count,
654 p_data => x_msg_data);
655 END create_receipt_writeoff;
656
657 /*========================================================================
658 | PUBLIC PROCEDURE split_claim_reapplication
659 |
660 | DESCRIPTION
661 | ----------------------------------------
662 | This procedure calls entity handlers to unapply the current application
663 | for a given claim ID and to create a claim investigation
664 | application while bypassing the usual validation on existing claims.
665 | Amount and status are not checked, as in the case of a partial
666 | settlement the requirement is to reapply the balance to the original
667 | claim
668 |
669 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
670 |
671 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
672 |
673 | PARAMETERS
674 | p_claim_id IN ID of claim
675 | p_customer_trx_id IN Invoice ID (if invoice related)
676 | p_amount IN Amount to be applied
677 | p_init_msg_list IN API message stack initialize flag
678 | p_cash_receipt_id IN ID of receipt for which claim originally
679 | created
680 | p_ussgl_transaction_code IN Default value for USSGL trx code flexfield
681 | x_return_status OUT NOCOPY
682 | x_msg_count OUT NOCOPY
683 | x_msg_data OUT NOCOPY
684 |
685 | KNOWN ISSUES
686 |
687 | NOTES
688 |
689 | MODIFICATION HISTORY
690 | Date Author Description of Changes
691 | 20-MAR-2002 jbeckett Created
692 | 08-APR-2002 jbeckett Bug 2279399 - split_claim_reapplication
693 | should not reapply an amount of zero.
694 | 09-MAY-2002 jbeckett Bug 2353144 - Replaced calls to entity
695 | handlers with calls to receipt API
696 | 20-MAY-2002 jbeckett Bug 2381009 - amended to process installments
697 | correctly.
698 | 30-AUG-2002 jbeckett Bug 2535663 - pass sum of earned and unearned
699 | discounts to ar_receipt_api_pub.apply.
700 | 12-SEP-2002 S.Nambiar Bug 2560486 - split_claim_reapplication should
701 | not pass apply date or apply gl date. Let it default
702 | based on the profile option.
703 *=======================================================================*/
704 PROCEDURE split_claim_reapplication
705 (p_claim_id IN NUMBER,
706 p_customer_trx_id IN NUMBER,
707 p_amount IN NUMBER,
708 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
709 p_cash_receipt_id IN NUMBER,
710 p_ussgl_transaction_code IN NUMBER DEFAULT NULL,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_count OUT NOCOPY NUMBER,
713 x_msg_data OUT NOCOPY VARCHAR2)
714 IS
715 l_set_of_books_id NUMBER;
716 l_application_id NUMBER;
717 l_applied_ps_id NUMBER;
718 l_apply_date DATE;
719 l_app_gl_date DATE;
720 l_amount NUMBER;
721 l_bal_due_remaining NUMBER;
722 l_application_ref_id NUMBER := NULL;
723 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE := NULL;
724 l_application_ref_reason ar_receivable_applications.application_ref_reason%TYPE;
725 l_dum_app_ref_type ar_receivable_applications.application_ref_type%TYPE;
726 l_dum_app_ref_id NUMBER;
727 l_dum_app_ref_num ar_receivable_applications.application_ref_num%TYPE;
728 l_dum_sec_app_ref_id NUMBER;
729 l_payment_set_id NUMBER;
730 l_claim_receivables_trx_id NUMBER;
731 l_comments ar_receivable_applications.comments%TYPE;
732 l_customer_reference ar_receivable_applications.customer_reference%TYPE;
733 l_attribute_rec AR_Receipt_API_PUB.attribute_rec_type;
734 l_global_attribute_rec AR_Receipt_API_PUB.global_attribute_rec_type;
735 l_balance NUMBER := 0;
736 l_receipt_number ar_cash_receipts.receipt_number%TYPE;
737 l_receipt_date DATE;
738 l_currency_code ar_cash_receipts.currency_code%TYPE;
739 l_trx_currency_code ar_payment_schedules.invoice_currency_code%TYPE;
740 l_installment ar_payment_schedules.terms_sequence_number%TYPE;
741 l_cr_gl_date DATE;
742 l_cr_payment_schedule_id NUMBER;
743 l_customer_id NUMBER;
744 l_default_gl_date DATE;
745 l_defaulting_rule_used VARCHAR2(50);
746 l_new_application_id NUMBER;
747 l_receivable_application_id NUMBER;
748 l_receivables_trx_id NUMBER;
749 l_trans_to_receipt_rate NUMBER;
750 l_discount NUMBER; -- Bug 2535663
751 l_discount_earned NUMBER;
752 l_discount_unearned NUMBER;
753 l_applied_amount_from NUMBER;
754 l_applied_amt_from_old NUMBER; /* Bug fix 5291088*/
755 l_new_claim_num ar_receivable_applications.application_ref_num%TYPE;
756 l_return_status VARCHAR2(1);
757 l_amount_status VARCHAR2(1); -- Bug 3809272
758 l_api_name CONSTANT VARCHAR2(30)
759 := 'split_claim_reapplication';
760 l_error_message VARCHAR2(2000);
761 l_error_count NUMBER := 0;
762 l_msg_count NUMBER;
763 l_msg_data VARCHAR2(2000);
764 l_claim_id NUMBER;
765 l_claim_applied VARCHAR2(1);
766
767 /* Bug 7479983: Cursor is used to identify the applications associated to the claim on the receipt */
768
769 CURSOR get_app_ids(p_cash_rcpt_id in NUMBER, p_ctx_id in NUMBER) IS
770 select receivable_application_id from ar_receivable_applications_all
771 where cash_receipt_id = p_cash_rcpt_id
772 and applied_customer_trx_id = p_ctx_id
773 and status = 'APP'
774 and reversal_gl_date IS NULL
775 order by amount_applied desc;
776
777 CURSOR applied_amount( cv_cash_receipt_id IN NUMBER
778 , cv_customer_trx_id IN NUMBER
779 , cv_claim_id IN NUMBER ) IS
780 SELECT sum(rec.amount_applied) amt_applied,
781 sum(rec.earned_discount_taken) earned_discount_taken,
782 sum(rec.unearned_discount_taken) unearned_discount_taken
783 FROM ar_receivable_applications rec
784 , ar_payment_schedules pay
785 WHERE rec.applied_payment_schedule_id = pay.payment_schedule_id
786 AND rec.cash_receipt_id = cv_cash_receipt_id
787 AND pay.customer_trx_id = cv_customer_trx_id
788 AND rec.application_ref_type = 'CLAIM'
789 AND rec.display = 'Y'
790 AND rec.secondary_application_ref_id = cv_claim_id;
791
792 BEGIN
793
794 IF PG_DEBUG in ('Y', 'C') THEN
795 arp_standard.debug('ARP_DEDUCTION_COVER.split_claim_reapplication()+');
796 END IF;
797
798 -- Standard Start of API savepoint
799 SAVEPOINT split_claim_reapplication;
800 -- Initialize message list if p_init_msg_list is set to TRUE.
801 IF FND_API.to_Boolean( p_init_msg_list )
802 THEN
803 FND_MSG_PUB.initialize;
804 END IF;
805 -- Initialize API return status to success
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807
808 /* Bug 3022077 - initialize global variables */
809 arp_global.init_global;
810 arp_standard.init_standard;
811
812 /*---------------------------------------------------------------------+
813 | 1) Check that a valid receipt has been passed |
814 +---------------------------------------------------------------------*/
815 IF NOT receipt_valid
816 (p_cash_receipt_id => p_cash_receipt_id,
817 x_receipt_number => l_receipt_number,
818 x_receipt_date => l_receipt_date,
819 x_cr_gl_date => l_cr_gl_date,
820 x_customer_id => l_customer_id,
821 x_currency_code => l_currency_code,
822 x_cr_payment_schedule_id => l_cr_payment_schedule_id)
823 THEN
824 l_error_count := l_error_count + 1;
825 END IF;
826
827 /*---------------------------------------------------------------------+
828 | 2) Check that a valid claim has been passed |
829 +---------------------------------------------------------------------*/
830 BEGIN
831 IF p_customer_trx_id IS NOT NULL
832 THEN
833 /* Bug 2381009 - allow for more than 1 payment schedule on invoice */
834 SELECT ps.payment_schedule_id
835 , ps.invoice_currency_code
836 , ps.terms_sequence_number
837 INTO l_applied_ps_id
838 , l_trx_currency_code
839 , l_installment
840 FROM ar_payment_schedules ps,
841 ar_receivable_applications ra
842 WHERE ps.payment_schedule_id = ra.applied_payment_schedule_id
843 AND ra.application_ref_type = 'CLAIM'
844 AND ra.secondary_application_ref_id = p_claim_id
845 AND ra.cash_receipt_id = p_cash_receipt_id
846 AND ra.status = 'APP'
847 AND ps.customer_trx_id = p_customer_trx_id
848 AND ROWNUM = 1;
849 ELSE
850 l_applied_ps_id := -4;
851 END IF;
852 EXCEPTION
853 WHEN OTHERS THEN
854 FND_MESSAGE.SET_NAME('AR','ARTA_PAYMENT_SCHEDULE_NO_FOUND');
855 FND_MSG_PUB.Add;
856 RAISE FND_API.G_EXC_ERROR;
857 END;
858 IF NOT claim_on_receipt (
859 p_claim_id => p_claim_id
860 , p_cash_receipt_id => p_cash_receipt_id
861 , p_applied_ps_id => l_applied_ps_id
862 , x_application_id => l_application_id
863 , x_apply_date => l_apply_date
864 , x_app_gl_date => l_app_gl_date
865 , x_amount_applied => l_amount
866 , x_trans_to_receipt_rate => l_trans_to_receipt_rate
867 , x_discount_earned => l_discount_earned
868 , x_discount_unearned => l_discount_unearned
869 , x_application_ref_num => l_application_ref_num
870 , x_application_ref_reason => l_application_ref_reason
871 , x_receivables_trx_id => l_claim_receivables_trx_id
872 , x_comments => l_comments
873 , x_customer_reference => l_customer_reference
874 , x_attribute_rec => l_attribute_rec
875 , x_global_attribute_rec => l_global_attribute_rec
876 , x_claim_applied => l_claim_applied)
877 THEN
878 l_error_count := l_error_count + 1;
879 END IF;
880
881 IF PG_DEBUG in ('Y', 'C') THEN
882 arp_standard.debug('p_cash_receipt_id: ' || p_cash_receipt_id);
883 arp_standard.debug('p_customer_trx_id: ' || p_customer_trx_id);
884 arp_standard.debug('p_claim_id: ' || p_claim_id);
885 END IF;
886
887 OPEN applied_amount( p_cash_receipt_id, p_customer_trx_id, p_claim_id );
888 FETCH applied_amount INTO l_amount, l_discount_earned, l_discount_unearned;
889 CLOSE applied_amount;
890
891 l_applied_amount_from := p_amount * l_trans_to_receipt_rate;
892 l_applied_amt_from_old:= arpcurr.currround(l_amount * nvl(l_trans_to_receipt_rate,1),l_currency_code) ; /* Bug fix 5291088 */
893 l_discount := nvl(l_discount_earned,0) + nvl(l_discount_unearned,0); -- Bug 2535663
894
895 IF PG_DEBUG in ('Y', 'C') THEN
896 arp_standard.debug('l_applied_amount_from: ' || l_applied_amount_from);
897 arp_standard.debug('l_applied_amt_from_old: ' || l_applied_amt_from_old);
898 arp_standard.debug('l_discount_earned: ' || l_discount_earned);
899 arp_standard.debug('l_discount_unearned: ' || l_discount_unearned);
900 END IF;
901 -- Bug 3809272
902 /*---------------------------------------------------------------------+
903 | 2b) Check that the receipt will not go negative
904 +---------------------------------------------------------------------*/
905 /* Bug fix 5291088 : The amounts that should be compared to be passed in receipt currency */
906 validate_amount_applied( p_amount_applied => l_applied_amt_from_old,
907 p_new_amount_applied => nvl(arpcurr.currround(l_applied_amount_from,l_currency_code),p_amount),
908 p_cash_receipt_id => p_cash_receipt_id,
909 x_return_status => l_amount_status);
910 IF l_amount_status <> FND_API.G_RET_STS_SUCCESS THEN
911 l_error_count := l_error_count + 1;
912 END IF;
913
914 /*---------------------------------------------------------------------+
915 | 3) Raise error if validation errors found |
916 +---------------------------------------------------------------------*/
917 IF (l_error_count > 0)
918 THEN
919 RAISE FND_API.G_EXC_ERROR;
920 END IF;
921
922 /*---------------------------------------------------------------------+
923 | 4) Unapply the application |
924 +---------------------------------------------------------------------*/
925 /* Bug 2384340 - to prevent adjustments and chargebacks being reversed the
926 associated_cash_receipt_id on these records temporarily has its sign
927 switched */
928 IF (p_amount <> 0 AND l_claim_applied = 'Y')
929 THEN
930 UPDATE ar_adjustments
931 SET associated_cash_receipt_id = associated_cash_receipt_id * -1
932 WHERE associated_cash_receipt_id = p_cash_receipt_id
933 AND payment_schedule_id = l_applied_ps_id;
934 END IF;
935
936 IF (arp_util.validate_and_default_gl_date(
937 l_app_gl_date,
938 NULL,
939 NULL,
940 NULL,
941 NULL,
942 NULL,
943 NULL,
944 NULL,
945 'N',
946 NULL,
947 l_set_of_books_id,
948 222,
949 l_default_gl_date,
950 l_defaulting_rule_used,
951 l_error_message) = TRUE)
952 THEN
953 NULL;
954 END IF;
955
956 /* Bug 2821139 - under no circumstances should AR update claims when
957 they are settled from TM */
958
959 IF l_claim_applied = 'Y' THEN
960 IF p_customer_trx_id IS NOT NULL
961 THEN
962 FOR rec_app_id IN get_app_ids(p_cash_receipt_id, p_customer_trx_id) LOOP
963 AR_Receipt_API_PUB.Unapply(
964 p_api_version => 1.0,
965 p_init_msg_list => FND_API.G_FALSE,
966 x_return_status => l_return_status,
967 x_msg_count => l_msg_count,
968 x_msg_data => l_msg_data,
969 p_called_from => 'TRADE_MANAGEMENT',
970 p_cancel_claim_flag => 'N',
971 p_receivable_application_id => rec_app_id.receivable_application_id);
972 END LOOP;
973 ELSE
974 AR_Receipt_API_PUB.unapply_other_account (
975 p_api_version => 1.0,
976 p_init_msg_list => FND_API.G_FALSE,
977 x_return_status => l_return_status,
978 x_msg_count => l_msg_count,
979 x_msg_data => l_msg_data,
980 p_receivable_application_id => l_application_id,
981 p_cash_receipt_id => p_cash_receipt_id,
982 p_cancel_claim_flag => 'N',
983 p_called_from => 'TRADE_MANAGEMENT');
984 END IF;
985 END IF;
986
987 IF l_return_status = 'E'
988 THEN
989 RAISE FND_API.G_EXC_ERROR;
990 ELSIF l_return_status = 'U'
991 THEN
992 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
993 END IF;
994
995 /* Bug 2384340 - reinstating adjustments and chargebacks associated with
996 this receipt/payment schedule */
997 IF p_amount <> 0
998 THEN
999 UPDATE ar_adjustments
1000 SET associated_cash_receipt_id = associated_cash_receipt_id * -1
1001 WHERE associated_cash_receipt_id = p_cash_receipt_id * -1
1002 AND payment_schedule_id = l_applied_ps_id;
1003 END IF;
1004 /*---------------------------------------------------------------------+
1005 | 5) Apply new amount using original claim |
1006 +---------------------------------------------------------------------*/
1007 IF PG_DEBUG in ('Y', 'C') THEN
1008 arp_standard.debug('split_claim_reapplication: ' || 'Before Inserting Claim Application (+)');
1009 END IF;
1010 /* Bug 2279399 - do not apply if amount is zero */
1011 IF p_amount <> 0
1012 THEN
1013 /* Bug 2821139 - under no circumstances should AR update claims when
1014 they are settled from TM */
1015 l_claim_id := p_claim_id;
1016 IF p_customer_trx_id IS NOT NULL
1017 THEN
1018 ar_receipt_api_pub.apply (
1019 p_api_version => 1.0,
1020 p_init_msg_list => FND_API.G_FALSE,
1021 x_return_status => l_return_status,
1022 x_msg_count => l_msg_count,
1023 x_msg_data => l_msg_data,
1024 p_cash_receipt_id => p_cash_receipt_id,
1025 p_customer_trx_id => p_customer_trx_id,
1026 p_installment => l_installment,
1027 p_amount_applied => p_amount,
1028 p_amount_applied_from => l_applied_amount_from,
1029 p_trans_to_receipt_rate => l_trans_to_receipt_rate ,
1030 p_discount => l_discount, -- Bug 2535663
1031 p_apply_date => l_apply_date, -- Bug 2783541
1032 p_apply_gl_date => NULL,--Bug 2560486
1033 p_called_from => 'TRADE_MANAGEMENT',
1034 p_attribute_rec => l_attribute_rec,
1035 p_global_attribute_rec => l_global_attribute_rec,
1036 p_comments => l_comments,
1037 p_application_ref_type => 'CLAIM',
1038 p_application_ref_num => l_application_ref_num,
1039 p_secondary_application_ref_id => l_claim_id,
1040 p_application_ref_reason => l_application_ref_reason,
1041 p_customer_reference => l_customer_reference);
1042 ELSE
1043 ar_receipt_api_pub.apply_other_account (
1044 p_api_version => 1.0,
1045 p_init_msg_list => FND_API.G_FALSE,
1046 x_return_status => l_return_status,
1047 x_msg_count => l_msg_count,
1048 x_msg_data => l_msg_data,
1049 p_receivable_application_id => l_receivable_application_id,
1050 p_cash_receipt_id => p_cash_receipt_id,
1051 p_amount_applied => p_amount,
1052 p_receivables_trx_id => l_claim_receivables_trx_id,
1053 p_apply_date => l_apply_date, --Bug 2783541
1054 p_apply_gl_date => NULL,--Bug 2560486
1055 p_applied_payment_schedule_id => -4,
1056 p_application_ref_type => 'CLAIM',
1057 p_application_ref_id => l_application_ref_id,
1058 p_application_ref_num => l_application_ref_num,
1059 p_secondary_application_ref_id => l_claim_id,
1060 p_attribute_rec => l_attribute_rec,
1061 p_global_attribute_rec => l_global_attribute_rec,
1062 p_comments => l_comments,
1063 p_application_ref_reason => l_application_ref_reason,
1064 p_customer_reference => l_customer_reference,
1065 p_called_from => 'TRADE_MANAGEMENT');
1066 END IF;
1067
1068 IF (p_customer_trx_id IS NOT NULL AND
1069 ARPT_SQL_FUNC_UTIL.get_claim_amount(l_claim_id) = 0)
1070 THEN
1071 BEGIN
1072 UPDATE ar_payment_schedules
1073 SET active_claim_flag = 'N'
1074 WHERE payment_schedule_id = l_applied_ps_id;
1075 EXCEPTION
1076 WHEN others then
1077 IF PG_DEBUG in ('Y', 'C') THEN
1078 arp_standard.debug('split_claim_reapplication: ' || 'ERROR occured updating payment schedule: '||sqlerrm);
1079 END IF;
1080
1081 l_return_status := 'U';
1082 END;
1083 END IF;
1084
1085 IF l_return_status = 'E'
1086 THEN
1087 RAISE FND_API.G_EXC_ERROR;
1088 ELSIF l_return_status = 'U'
1089 THEN
1090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1091 END IF;
1092 END IF;
1093 IF PG_DEBUG in ('Y', 'C') THEN
1094 arp_standard.debug('split_claim_reapplication: ' || 'After Inserting Claim Application (-)');
1095 arp_standard.debug('ARP_DEDUCTION_COVER.split_claim_reapplication()-');
1096 END IF;
1097
1098 EXCEPTION
1099 WHEN FND_API.G_EXC_ERROR THEN
1100 ROLLBACK TO split_claim_reapplication;
1101 x_return_status := FND_API.G_RET_STS_ERROR ;
1102 FND_MSG_PUB.Count_And_Get
1103 (p_encoded => FND_API.G_FALSE,
1104 p_count => x_msg_count,
1105 p_data => x_msg_data);
1106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1107 IF PG_DEBUG in ('Y', 'C') THEN
1108 arp_standard.debug('split_claim_reapplication: ' || 'Unexpected error '||sqlerrm||
1109 ' at arp_deduction_cover.split_claim_reapplication()+');
1110 END IF;
1111 ROLLBACK TO split_claim_reapplication;
1112 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1113 FND_MSG_PUB.Count_And_Get
1114 (p_encoded => FND_API.G_FALSE,
1115 p_count => x_msg_count,
1116 p_data => x_msg_data);
1117 WHEN OTHERS THEN
1118 IF (SQLCODE = -20001)
1119 THEN
1120 IF PG_DEBUG in ('Y', 'C') THEN
1121 arp_util.debug('split_claim_reapplication: ' || '20001 error '||
1122 ' at arp_deduction_cover.split_claim_reapplication()+');
1123 END IF;
1124 x_return_status := FND_API.G_RET_STS_ERROR ;
1125 ELSE
1126 IF PG_DEBUG in ('Y', 'C') THEN
1127 arp_util.debug('split_claim_reapplication: ' || 'Unexpected error '||sqlerrm||
1128 ' at arp_deduction_cover.split_claim_reapplication()+');
1129 END IF;
1130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1131 IF FND_MSG_PUB.Check_Msg_Level
1132 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1133 THEN
1134 FND_MSG_PUB.Add_Exc_Msg
1135 ( G_PKG_NAME ,
1136 l_api_name
1137 );
1138 END IF;
1139 END IF;
1140 ROLLBACK TO split_claim_reapplication;
1141 FND_MSG_PUB.Count_And_Get
1142 (p_encoded => FND_API.G_FALSE,
1143 p_count => x_msg_count,
1144 p_data => x_msg_data);
1145 END split_claim_reapplication;
1146
1147 /*========================================================================
1148 | PUBLIC FUNCTION receipt_valid
1149 |
1150 | DESCRIPTION
1151 | ----------------------------------------
1152 | This function checks if the passed cash receipt ID is valid
1153 | It returns boolean TRUE or FALSE accordingly.
1154 | Addition receipt information is passed back if valid.
1155 |
1156 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1157 |
1158 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1159 |
1160 | PARAMETERS
1161 | p_cash_receipt_id IN ID of cash receipt
1162 | x_receipt_number OUT NOCOPY
1163 | x_receipt_date OUT NOCOPY
1164 | x_cr_gl_date OUT NOCOPY
1165 | x_customer_id OUT NOCOPY
1166 | x_currency_code OUT NOCOPY
1167 | x_cr_payment_schedule_id OUT NOCOPY
1168 |
1169 | KNOWN ISSUES
1170 |
1171 | NOTES
1172 |
1173 | MODIFICATION HISTORY
1174 | Date Author Description of Changes
1175 | 20-MAR-2002 jbeckett Created
1176 |
1177 *=======================================================================*/
1178 FUNCTION receipt_valid
1179 (p_cash_receipt_id IN NUMBER,
1180 x_receipt_number OUT NOCOPY VARCHAR2,
1181 x_receipt_date OUT NOCOPY DATE,
1182 x_cr_gl_date OUT NOCOPY DATE,
1183 x_customer_id OUT NOCOPY NUMBER,
1184 x_currency_code OUT NOCOPY VARCHAR2,
1185 x_cr_payment_schedule_id OUT NOCOPY NUMBER)
1186 RETURN BOOLEAN
1187 IS
1188 BEGIN
1189 IF PG_DEBUG in ('Y', 'C') THEN
1190 arp_standard.debug('ARP_DEDUCTION_COVER.receipt_valid()+');
1191 END IF;
1192
1193 x_receipt_number := NULL;
1194 x_receipt_date := NULL;
1195 x_cr_gl_date := NULL;
1196 x_customer_id := NULL;
1197 x_currency_code := NULL;
1198 x_cr_payment_schedule_id := NULL;
1199 IF (p_cash_receipt_id IS NULL)
1200 THEN
1201 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CASH_RCPT_ID_NULL');
1202 FND_MSG_PUB.Add;
1203 RETURN FALSE;
1204 ELSE
1205 SELECT cr.receipt_number
1206 , cr.receipt_date
1207 , crh.gl_date
1208 , cr.pay_from_customer
1209 , cr.currency_code
1210 , ps.payment_schedule_id
1211 INTO x_receipt_number
1212 , x_receipt_date
1213 , x_cr_gl_date
1214 , x_customer_id
1215 , x_currency_code
1216 , x_cr_payment_schedule_id
1217 FROM ar_cash_receipts cr
1218 , ar_payment_schedules ps
1219 , ar_cash_receipt_history crh
1220 WHERE cr.cash_receipt_id = crh.cash_receipt_id(+)
1221 AND crh.first_posted_record_flag(+) = 'Y'
1222 AND cr.cash_receipt_id = ps.cash_receipt_id(+)
1223 AND cr.cash_receipt_id = p_cash_receipt_id;
1224 END IF;
1225 RETURN TRUE;
1226 IF PG_DEBUG in ('Y', 'C') THEN
1227 arp_standard.debug('ARP_DEDUCTION_COVER.receipt_valid()-');
1228 END IF;
1229
1230 EXCEPTION
1231 WHEN OTHERS
1232 THEN
1233 FND_MESSAGE.SET_NAME('AR','ARTA_ERR_FINDING_CASH_RCPT');
1234 FND_MESSAGE.SET_TOKEN('CR_ID',p_cash_receipt_id);
1235 FND_MSG_PUB.Add;
1236 RETURN FALSE;
1237 END receipt_valid;
1238
1239 /*========================================================================
1240 | PUBLIC FUNCTION claim_on_receipt
1241 |
1242 | DESCRIPTION
1243 | ----------------------------------------
1244 | This function checks if a current claim investigation application
1245 | exists with the passed claim ID
1246 | It returns boolean TRUE or FALSE accordingly.
1247 | Additional application information is passed back if valid.
1248 |
1249 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1250 |
1251 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1252 |
1253 | PARAMETERS
1254 | p_claim_id IN
1255 | p_cash_receipt_id IN
1256 | p_applied_ps_id IN
1257 | x_application_id OUT NOCOPY
1258 | x_apply_date OUT NOCOPY
1259 | x_app_gl_date OUT NOCOPY
1260 | x_amount_applied OUT NOCOPY
1261 | x_trans_to_receipt_rate OUT NOCOPY
1262 | x_discount_earned OUT NOCOPY
1263 | x_discount_unearned OUT NOCOPY
1264 | x_application_ref_num OUT NOCOPY
1265 | x_application_ref_reason OUT NOCOPY
1266 | x_receivables_trx_id OUT NOCOPY
1267 | x_comments OUT NOCOPY
1268 | x_customer_reference OUT NOCOPY
1269 | x_attribute_rec OUT NOCOPY
1270 | x_global_attribute_rec OUT NOCOPY
1271 |
1272 | KNOWN ISSUES
1273 |
1274 | NOTES
1275 |
1276 | MODIFICATION HISTORY
1277 | Date Author Description of Changes
1278 | 20-MAR-2002 jbeckett Created
1279 | 15-APR-2002 jbeckett Check for invoice related claims also
1280 | 06-AUG-2004 JBECKETT Bug 3643551:Disabled use of index on
1281 | applied_ps_id in favour of cash_receipt_id
1282 |
1283 *=======================================================================*/
1284 FUNCTION claim_on_receipt (
1285 p_claim_id IN NUMBER
1286 , p_cash_receipt_id IN NUMBER
1287 , p_applied_ps_id IN NUMBER
1288 , x_application_id OUT NOCOPY NUMBER
1289 , x_apply_date OUT NOCOPY DATE
1290 , x_app_gl_date OUT NOCOPY DATE
1291 , x_amount_applied OUT NOCOPY NUMBER
1292 , x_trans_to_receipt_rate OUT NOCOPY NUMBER
1293 , x_discount_earned OUT NOCOPY NUMBER
1294 , x_discount_unearned OUT NOCOPY NUMBER
1295 , x_application_ref_num OUT NOCOPY VARCHAR2
1296 , x_application_ref_reason OUT NOCOPY VARCHAR2
1297 , x_receivables_trx_id OUT NOCOPY NUMBER
1298 , x_comments OUT NOCOPY VARCHAR2
1299 , x_customer_reference OUT NOCOPY VARCHAR2
1300 , x_attribute_rec OUT NOCOPY AR_Receipt_API_PUB.attribute_rec_type
1301 , x_global_attribute_rec OUT NOCOPY AR_Receipt_API_PUB.global_attribute_rec_type
1302 , x_claim_applied OUT NOCOPY VARCHAR2)
1303 RETURN BOOLEAN
1304 IS
1305
1306 BEGIN
1307
1308 IF PG_DEBUG in ('Y', 'C') THEN
1309 arp_standard.debug('ARP_DEDUCTION_COVER.claim_on_receipt()+');
1310 END IF;
1311
1312 IF (p_claim_id IS NULL)
1313 THEN
1314 FND_MESSAGE.SET_NAME('AR','AR_RWAPP_NULL_CLAIM_ID');
1315 FND_MSG_PUB.Add;
1316 RETURN FALSE;
1317 ELSIF (p_cash_receipt_id IS NOT NULL)
1318 THEN
1319 BEGIN
1320 -- Bug 3643661 - prevent index on applied_payment_schedule_id being used
1321 SELECT
1322 app.receivable_application_id
1323 , app.apply_date
1324 , app.gl_date
1325 , app.amount_applied
1326 , app.trans_to_receipt_rate
1327 , app.earned_discount_taken
1328 , app.unearned_discount_taken
1329 , app.application_ref_num
1330 , app.application_ref_reason
1331 , app.receivables_trx_id
1332 , app.comments
1333 , app.customer_reference
1334 , app.attribute_category
1335 , app.attribute1
1336 , app.attribute2
1337 , app.attribute3
1338 , app.attribute4
1339 , app.attribute5
1340 , app.attribute6
1341 , app.attribute7
1342 , app.attribute8
1343 , app.attribute9
1344 , app.attribute10
1345 , app.attribute11
1346 , app.attribute12
1347 , app.attribute13
1348 , app.attribute14
1349 , app.attribute15
1350 , app.global_attribute_category
1351 , app.global_attribute1
1352 , app.global_attribute2
1353 , app.global_attribute3
1354 , app.global_attribute4
1355 , app.global_attribute5
1356 , app.global_attribute6
1357 , app.global_attribute7
1358 , app.global_attribute8
1359 , app.global_attribute9
1360 , app.global_attribute10
1361 , app.global_attribute11
1362 , app.global_attribute12
1363 , app.global_attribute13
1364 , app.global_attribute14
1365 , app.global_attribute15
1366 , app.global_attribute16
1367 , app.global_attribute17
1368 , app.global_attribute18
1369 , app.global_attribute19
1370 , app.global_attribute20
1371 INTO
1372 x_application_id
1373 , x_apply_date
1374 , x_app_gl_date
1375 , x_amount_applied
1376 , x_trans_to_receipt_rate
1377 , x_discount_earned
1378 , x_discount_unearned
1379 , x_application_ref_num
1380 , x_application_ref_reason
1381 , x_receivables_trx_id
1382 , x_comments
1383 , x_customer_reference
1384 , x_attribute_rec.attribute_category
1385 , x_attribute_rec.attribute1
1386 , x_attribute_rec.attribute2
1387 , x_attribute_rec.attribute3
1388 , x_attribute_rec.attribute4
1389 , x_attribute_rec.attribute5
1390 , x_attribute_rec.attribute6
1391 , x_attribute_rec.attribute7
1392 , x_attribute_rec.attribute8
1393 , x_attribute_rec.attribute9
1394 , x_attribute_rec.attribute10
1395 , x_attribute_rec.attribute11
1396 , x_attribute_rec.attribute12
1397 , x_attribute_rec.attribute13
1398 , x_attribute_rec.attribute14
1399 , x_attribute_rec.attribute15
1400 , x_global_attribute_rec.global_attribute_category
1401 , x_global_attribute_rec.global_attribute1
1402 , x_global_attribute_rec.global_attribute2
1403 , x_global_attribute_rec.global_attribute3
1404 , x_global_attribute_rec.global_attribute4
1405 , x_global_attribute_rec.global_attribute5
1406 , x_global_attribute_rec.global_attribute6
1407 , x_global_attribute_rec.global_attribute7
1408 , x_global_attribute_rec.global_attribute8
1409 , x_global_attribute_rec.global_attribute9
1410 , x_global_attribute_rec.global_attribute10
1411 , x_global_attribute_rec.global_attribute11
1412 , x_global_attribute_rec.global_attribute12
1413 , x_global_attribute_rec.global_attribute13
1414 , x_global_attribute_rec.global_attribute14
1415 , x_global_attribute_rec.global_attribute15
1416 , x_global_attribute_rec.global_attribute16
1417 , x_global_attribute_rec.global_attribute17
1418 , x_global_attribute_rec.global_attribute18
1419 , x_global_attribute_rec.global_attribute19
1420 , x_global_attribute_rec.global_attribute20
1421 FROM ar_receivable_applications app
1422 WHERE app.secondary_application_ref_id = p_claim_id
1423 AND app.applied_payment_schedule_id + 0 = p_applied_ps_id
1424 AND app.application_ref_type = 'CLAIM'
1425 AND app.display = 'Y'
1426 AND app.status = DECODE(p_applied_ps_id,-4,'OTHER ACC','APP')
1427 AND app.cash_receipt_id = p_cash_receipt_id
1428 AND ROWNUM = 1;
1429 x_claim_applied := 'Y';
1430 EXCEPTION WHEN NO_DATA_FOUND THEN
1431 x_claim_applied := 'N';
1432 SELECT
1433 app.receivable_application_id
1434 , app.apply_date
1435 , app.gl_date
1436 , app.amount_applied
1437 , app.trans_to_receipt_rate
1438 , app.earned_discount_taken
1439 , app.unearned_discount_taken
1440 , app.application_ref_num
1441 , app.application_ref_reason
1442 , app.receivables_trx_id
1443 , app.comments
1444 , app.customer_reference
1445 , app.attribute_category
1446 , app.attribute1
1447 , app.attribute2
1448 , app.attribute3
1449 , app.attribute4
1450 , app.attribute5
1451 , app.attribute6
1452 , app.attribute7
1453 , app.attribute8
1454 , app.attribute9
1455 , app.attribute10
1456 , app.attribute11
1457 , app.attribute12
1458 , app.attribute13
1459 , app.attribute14
1460 , app.attribute15
1461 , app.global_attribute_category
1462 , app.global_attribute1
1463 , app.global_attribute2
1464 , app.global_attribute3
1465 , app.global_attribute4
1466 , app.global_attribute5
1467 , app.global_attribute6
1468 , app.global_attribute7
1469 , app.global_attribute8
1470 , app.global_attribute9
1471 , app.global_attribute10
1472 , app.global_attribute11
1473 , app.global_attribute12
1474 , app.global_attribute13
1475 , app.global_attribute14
1476 , app.global_attribute15
1477 , app.global_attribute16
1478 , app.global_attribute17
1479 , app.global_attribute18
1480 , app.global_attribute19
1481 , app.global_attribute20
1482 INTO
1483 x_application_id
1484 , x_apply_date
1485 , x_app_gl_date
1486 , x_amount_applied
1487 , x_trans_to_receipt_rate
1488 , x_discount_earned
1489 , x_discount_unearned
1490 , x_application_ref_num
1491 , x_application_ref_reason
1492 , x_receivables_trx_id
1493 , x_comments
1494 , x_customer_reference
1495 , x_attribute_rec.attribute_category
1496 , x_attribute_rec.attribute1
1497 , x_attribute_rec.attribute2
1498 , x_attribute_rec.attribute3
1499 , x_attribute_rec.attribute4
1500 , x_attribute_rec.attribute5
1501 , x_attribute_rec.attribute6
1502 , x_attribute_rec.attribute7
1503 , x_attribute_rec.attribute8
1504 , x_attribute_rec.attribute9
1505 , x_attribute_rec.attribute10
1506 , x_attribute_rec.attribute11
1507 , x_attribute_rec.attribute12
1508 , x_attribute_rec.attribute13
1509 , x_attribute_rec.attribute14
1510 , x_attribute_rec.attribute15
1511 , x_global_attribute_rec.global_attribute_category
1512 , x_global_attribute_rec.global_attribute1
1513 , x_global_attribute_rec.global_attribute2
1514 , x_global_attribute_rec.global_attribute3
1515 , x_global_attribute_rec.global_attribute4
1516 , x_global_attribute_rec.global_attribute5
1517 , x_global_attribute_rec.global_attribute6
1518 , x_global_attribute_rec.global_attribute7
1519 , x_global_attribute_rec.global_attribute8
1520 , x_global_attribute_rec.global_attribute9
1521 , x_global_attribute_rec.global_attribute10
1522 , x_global_attribute_rec.global_attribute11
1523 , x_global_attribute_rec.global_attribute12
1524 , x_global_attribute_rec.global_attribute13
1525 , x_global_attribute_rec.global_attribute14
1526 , x_global_attribute_rec.global_attribute15
1527 , x_global_attribute_rec.global_attribute16
1528 , x_global_attribute_rec.global_attribute17
1529 , x_global_attribute_rec.global_attribute18
1530 , x_global_attribute_rec.global_attribute19
1531 , x_global_attribute_rec.global_attribute20
1532 FROM ar_receivable_applications app
1533 WHERE app.secondary_application_ref_id = p_claim_id
1534 AND app.applied_payment_schedule_id + 0 = p_applied_ps_id
1535 AND app.application_ref_type = 'CLAIM'
1536 AND app.status = DECODE(p_applied_ps_id,-4,'OTHER ACC','APP')
1537 AND app.cash_receipt_id = p_cash_receipt_id
1538 AND ROWNUM = 1;
1539 END;
1540
1541 RETURN TRUE;
1542 ELSE
1543 FND_MESSAGE.SET_NAME('AR','AR_RW_INVALID_CLAIM_ID');
1544 FND_MSG_PUB.Add;
1545 RETURN FALSE;
1546 END IF;
1547 IF PG_DEBUG in ('Y', 'C') THEN
1548 arp_standard.debug('ARP_DEDUCTION_COVER.claim_on_receipt()-');
1549 END IF;
1550
1551 EXCEPTION
1552 WHEN OTHERS
1553 THEN
1554 FND_MESSAGE.SET_NAME('AR','AR_RWAPP_CLAIM_ID_NOTFOUND');
1555 FND_MESSAGE.SET_TOKEN('CLAIM_ID',p_claim_id);
1556 FND_MSG_PUB.Add;
1557 RETURN FALSE;
1558 END claim_on_receipt;
1559
1560 /*========================================================================
1561 | PUBLIC FUNCTION claim_valid
1562 |
1563 | DESCRIPTION
1564 | ----------------------------------------
1565 | This function checks if the passed claim ID is valid for this receipt
1566 | It returns boolean TRUE or FALSE accordingly.
1567 | Claim number is passed back if valid.
1568 |
1569 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1570 |
1571 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1572 |
1573 | PARAMETERS
1574 | p_claim_id IN
1575 | p_receipt_id IN
1576 | p_curr_code IN
1577 | p_amount IN
1578 | x_claim_num OUT NOCOPY
1579 |
1580 | KNOWN ISSUES
1581 |
1582 | NOTES
1583 |
1584 | MODIFICATION HISTORY
1585 | Date Author Description of Changes
1586 | 20-MAR-2002 jbeckett Removed checks for amount and status
1587 |
1588 *=======================================================================*/
1589 FUNCTION claim_valid (
1590 p_claim_id IN NUMBER,
1591 p_receipt_id IN NUMBER,
1592 p_curr_code IN VARCHAR2,
1593 p_amount IN NUMBER,
1594 x_claim_num OUT NOCOPY VARCHAR2)
1595 RETURN BOOLEAN
1596 IS
1597 l_query VARCHAR2(2000);
1598 l_api_name CONSTANT VARCHAR2(30) := 'claim_valid';
1599 BEGIN
1600 IF PG_DEBUG in ('Y', 'C') THEN
1601 arp_standard.debug('ARP_DEDUCTION_COVER.claim_valid()+');
1602 END IF;
1603 --
1604 -- The sql to check for the existence of the claim in iClaim is dynamic
1605 -- to avoid package compilation problems if iClaim is not installed
1606 --
1607 x_claim_num := NULL;
1608 /* Bug 2270842 - amount and status should not be validated as the original
1609 claim will be reallocated in the event of a split */
1610 IF arp_global.tm_installed_flag = 'Y'
1611 THEN
1612 l_query := ' select claim_number '||
1613 ' from ozf_ar_deductions_v '||
1614 ' where claim_id = :claim_id '||
1615 ' and receipt_id = :receipt_id '||
1616 ' and currency_code = :currency_code ';
1617 BEGIN
1618 EXECUTE IMMEDIATE l_query
1619 INTO x_claim_num
1620 USING p_claim_id,
1621 p_receipt_id,
1622 p_curr_code;
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND THEN
1625 FND_MESSAGE.set_name('AR','AR_RW_INVALID_CLAIM_ID');
1626 FND_MESSAGE.set_token('CLAIM_ID',p_claim_id);
1627 FND_MSG_PUB.Add;
1628 RETURN FALSE;
1629 END;
1630 RETURN TRUE;
1631 ELSE
1632 FND_MESSAGE.set_name('AR','AR_RW_ICLAIM_NOT_INSTALLED');
1633 FND_MSG_PUB.Add;
1634 RETURN FALSE;
1635 END IF;
1636 IF PG_DEBUG in ('Y', 'C') THEN
1637 arp_standard.debug('ARP_DEDUCTION_COVER.claim_valid()-');
1638 END IF;
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 IF (SQLCODE = -20001)
1642 THEN
1643 IF PG_DEBUG in ('Y', 'C') THEN
1644 arp_standard.debug('claim_valid: ' || '20001 error '||
1645 ' at arp_deduction_cover.claim_valid()+');
1646 END IF;
1647 ELSE
1648 IF PG_DEBUG in ('Y', 'C') THEN
1649 arp_standard.debug('claim_valid: ' || 'Unexpected error '||sqlerrm||
1650 ' at arp_deduction_cover.claim_valid()+');
1651 END IF;
1652 IF FND_MSG_PUB.Check_Msg_Level
1653 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1654 THEN
1655 FND_MSG_PUB.Add_Exc_Msg
1656 ( G_PKG_NAME ,
1657 l_api_name
1658 );
1659 END IF;
1660 END IF;
1661 END claim_valid;
1662
1663 /*========================================================================
1664 | PUBLIC FUNCTION negative_rct_writeoffs_allowed
1665 |
1666 | DESCRIPTION
1667 | ----------------------------------------
1668 | This function returns TRUE or FALSE depending on whether
1669 | negative receipt writeoffs are allowed. It returns TRUE
1670 | post 11.5.10 and FALSE for pre 11.5.10 versions.
1671 |
1672 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1673 |
1674 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1675 |
1676 | PARAMETERS
1677 |
1678 | KNOWN ISSUES
1679 |
1680 | NOTES
1681 |
1682 | MODIFICATION HISTORY
1683 | Date Author Description of Changes
1684 | 12-JUN-2003 jbeckett Created
1685 |
1686 *=======================================================================*/
1687
1688 FUNCTION negative_rct_writeoffs_allowed
1689 RETURN BOOLEAN
1690 IS
1691 BEGIN
1692 IF PG_DEBUG in ('Y', 'C') THEN
1693 arp_standard.debug('ARP_DEDUCTION_COVER.negative_rct_writeoffs_allowed()+');
1694 END IF;
1695 RETURN TRUE;
1696 END ;
1697
1698 /*========================================================================
1699 | PUBLIC PROCEDURE validate_amount_applied
1700 |
1701 | DESCRIPTION
1702 | ----------------------------------------
1703 | This procedure checks if the amended amount applied for the invoice
1704 | or claim investigation will still leave the receipt positive.
1705 |
1706 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1707 |
1708 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1709 |
1710 | PARAMETERS
1711 | p_amount_applied IN NUMBER
1712 | p_new_amount_applied IN NUMBER
1713 | p_cash_receipt_id IN NUMBER
1714 | x_return_status OUT VARCHAR2
1715 |
1716 | KNOWN ISSUES
1717 |
1718 | NOTES
1719 |
1720 | MODIFICATION HISTORY
1721 | Date Author Description of Changes
1722 | 04-AUG-2004 jbeckett bug 3809272 - Created.
1723 |
1724 *=======================================================================*/
1725 PROCEDURE validate_amount_applied (
1726 p_amount_applied IN NUMBER,
1727 p_new_amount_applied IN NUMBER,
1728 p_cash_receipt_id IN NUMBER,
1729 x_return_status OUT NOCOPY VARCHAR2)
1730 IS
1731 l_unapplied_total NUMBER;
1732 l_new_unapplied_total NUMBER;
1733
1734 BEGIN
1735
1736 IF PG_DEBUG in ('Y', 'C') THEN
1737 arp_standard.debug('ARP_DEDUCTION_COVER.validate_amount_applied()+');
1738 END IF;
1739
1740 x_return_status := FND_API.G_RET_STS_SUCCESS;
1741
1742 SELECT SUM(NVL(ra.amount_applied,0))
1743 INTO l_unapplied_total
1744 FROM ar_receivable_applications ra
1745 WHERE ra.cash_receipt_id = p_cash_receipt_id
1746 AND ra.status = 'UNAPP'
1747 AND NVL(ra.confirmed_flag,'Y') = 'Y';
1748
1749 l_new_unapplied_total := (l_unapplied_total + p_amount_applied - p_new_amount_applied);
1750
1751 /* Bug fix 5291088 : Added additional debug messages */
1752 IF PG_DEBUG in ('Y', 'C') THEN
1753 arp_standard.debug('p_cash_receipt_id = '||p_cash_receipt_id);
1754 arp_standard.debug('p_amount_applied = '||p_amount_applied);
1755 arp_standard.debug('p_new_amount_applied = '||p_new_amount_applied);
1756 arp_standard.debug('l_unapplied_total = '||l_unapplied_total);
1757 arp_standard.debug('l_new_unapplied_total = '||l_new_unapplied_total);
1758 END IF;
1759
1760 IF l_new_unapplied_total < 0 THEN
1761 FND_MESSAGE.set_name('AR','AR_RW_CLAIM_SETTLMT_NEG_RCT');
1762 FND_MSG_PUB.Add;
1763 x_return_status := FND_API.G_RET_STS_ERROR;
1764 END IF;
1765
1766 IF PG_DEBUG in ('Y', 'C') THEN
1767 arp_standard.debug('ARP_DEDUCTION_COVER.validate_amount_applied()-');
1768 END IF;
1769
1770 EXCEPTION WHEN OTHERS THEN
1771 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
1772 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','ARXDECVB.pls:Validate_amount_applied' ||SQLERRM);
1773 FND_MSG_PUB.Add;
1774 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1775 END validate_amount_applied;
1776
1777 END ARP_DEDUCTION_COVER;