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