[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_WRITEOFF
Source
1 PACKAGE BODY ARP_PROCESS_WRITEOFF AS
2 /* $Header: ARPWRTFB.pls 120.8.12000000.4 2007/09/27 11:42:30 nemani ship $ */
3 /*========================================================================
4 | PRIVATE PROCEDURE submit_report
5 |
6 | DESCRIPTION
7 | This procedure submits the receipt write-off report.
8 |
9 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
10 | arp_process_writeoff.create_receipt_writeoff()
11 |
12 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
13 | NONE
14 | PARAMETERS
15 | p_currency_code IN Currency code
16 |
17 | RETURNS : NONE
18 |
19 | KNOWN ISSUES
20 |
21 | NOTES
22 | This concurrent request for receipt write-off report is submitted
23 | after the write_off records are processed.
24 | MODIFICATION HISTORY
25 | Date Author Description of Changes
26 | 22-AUG-00 S.Nambiar Created
27 | 21-MAY-01 S.Nambiar Bug 1784850 - Modified to pass apply
28 | date apply_gl_date to the report.
29 +===========================================================================*/
30 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
31
32 PROCEDURE submit_report(p_currency_code IN VARCHAR2,
33 p_apply_date IN DATE,
34 p_apply_gl_date IN DATE) IS
35
36 m_request_id NUMBER;
37 l_request_id NUMBER;
38 l_options_ok BOOLEAN;
39 l_org_id NUMBER;
40
41 BEGIN
42 IF PG_DEBUG in ('Y', 'C') THEN
43 arp_util.debug('arp_process_writeoff.submit_report()+');
44 END IF;
45
46 l_request_id := fnd_global.conc_request_id;
47
48 --Bug 5094139
49 select org_id
50 into l_org_id
51 from fnd_concurrent_requests
52 where request_id = l_request_id;
53
54 fnd_request.set_org_id(l_org_id);
55
56 l_options_ok := FND_REQUEST.SET_OPTIONS (
57 implicit => 'NO'
58 , protected => 'YES'
59 , language => ''
60 , territory => '');
61 IF (l_options_ok)
62 THEN
63 m_request_id := FND_REQUEST.SUBMIT_REQUEST(
64 application => 'AR'
65 , program => 'ARWRTRPT'
66 , description => ''
67 , start_time => ''
68 , sub_request => FALSE
69 , argument1 => 'P_APPLY_DATE='||fnd_date.date_to_canonical(p_apply_date)
70 , argument2 => 'P_CUSTOMER_NUMBER='||''
71 , argument3 => 'P_GL_DATE='||fnd_date.date_to_canonical(p_apply_gl_date)
72 , argument4 => 'P_RECEIPT_CURRENCY_CODE='||p_currency_code
73 , argument5 => 'P_RECEIPT_DATE_FROM='||''
74 , argument6 => 'P_RECEIPT_DATE_TO='||''
75 , argument7 => 'P_RECEIPT_GL_DATE_FROM='||''
76 , argument8 => 'P_RECEIPT_GL_DATE_TO='||''
77 , argument9 => 'P_RECEIPT_METHOD_ID='||''
78 , argument10 => 'P_RECEIPT_NUMBER='||''
79 , argument11 => 'P_REQUEST_ID='||fnd_number.number_to_canonical(l_request_id)
80 , argument12 => 'P_UNAPP_AMOUNT='||''
81 , argument13 => 'P_UNAPP_AMOUNT_PERCENT='||''
82 , argument14 => 'P_USER_ID='||''
83 , argument15 => chr(0)
84 , argument16 => ''
85 , argument17 => ''
86 , argument18 => ''
87 , argument19 => ''
88 , argument20 => ''
89 , argument21 => ''
90 , argument22 => ''
91 , argument23 => ''
92 , argument24 => ''
93 , argument25 => ''
94 , argument26 => ''
95 , argument27 => ''
96 , argument28 => ''
97 , argument29 => ''
98 , argument30 => ''
99 , argument31 => ''
100 , argument32 => ''
101 , argument33 => ''
102 , argument34 => ''
103 , argument35 => ''
104 , argument36 => ''
105 , argument37 => ''
106 , argument38 => ''
107 , argument39 => ''
108 , argument40 => ''
109 , argument41 => ''
110 , argument42 => ''
111 , argument43 => ''
112 , argument44 => ''
113 , argument45 => ''
114 , argument46 => ''
115 , argument47 => ''
116 , argument48 => ''
117 , argument49 => ''
118 , argument50 => ''
119 , argument51 => ''
120 , argument52 => ''
121 , argument53 => ''
122 , argument54 => ''
123 , argument55 => ''
124 , argument56 => ''
125 , argument57 => ''
126 , argument58 => ''
127 , argument59 => ''
128 , argument60 => ''
129 , argument61 => ''
130 , argument62 => ''
131 , argument63 => ''
132 , argument64 => ''
133 , argument65 => ''
134 , argument66 => ''
135 , argument67 => ''
136 , argument68 => ''
137 , argument69 => ''
138 , argument70 => ''
139 , argument71 => ''
140 , argument72 => ''
141 , argument73 => ''
142 , argument74 => ''
143 , argument75 => ''
144 , argument76 => ''
145 , argument77 => ''
146 , argument78 => ''
147 , argument79 => ''
148 , argument80 => ''
149 , argument81 => ''
150 , argument82 => ''
151 , argument83 => ''
152 , argument84 => ''
153 , argument85 => ''
154 , argument86 => ''
155 , argument87 => ''
156 , argument88 => ''
157 , argument89 => ''
158 , argument90 => ''
159 , argument91 => ''
160 , argument92 => ''
161 , argument93 => ''
162 , argument94 => ''
163 , argument95 => ''
164 , argument96 => ''
165 , argument97 => ''
166 , argument98 => ''
167 , argument99 => ''
168 , argument100 => '');
169 END IF;
170 IF PG_DEBUG in ('Y', 'C') THEN
171 arp_util.debug('arp_process_writeoff.submit_report()-');
172 END IF;
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF PG_DEBUG in ('Y', 'C') THEN
177 arp_util.debug('arp_process_writeoff.submit_report() '||SQLERRM);
178 END IF;
179
180 END submit_report;
181 /*========================================================================
182 | PUBLIC PROCEDURE create_receipt_writeoff
183 |
184 | DESCRIPTION
185 | This procedure proccess the write-off records according to the
186 | criteria passed
187 |
188 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
189 | NONE
190 |
191 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
192 | arp_process_writeoff.unapplied_amount()
193 |
194 | PARAMETERS
195 | p_receipt_currency_code IN Receipt currency code
196 | p_unapp_amount IN Unapplied amount
197 | p_unapp_amount_percent IN Unapplied amount percent
198 | p_receipt_date_from IN Receipt date from
199 | p_receipt_date_to IN Receipt date to
200 | p_receipt_gl_date_from IN Receipt GL date from
201 | p_receipt_gl_date_to IN Receipt GL date to
202 | p_receipt_method_id IN Receipt payment method id
203 | p_customer_number IN Customer Number
204 | p_receipt_number IN Receipt Number
205 | p_receivable_trx_id IN Receivable trx id of the activity
206 | p_apply_date IN Apply Date
207 | p_gl_date IN GL Date
208 | p_comments IN Comments
209 |
210 | KNOWN ISSUES
211 |
212 | NOTES
213 |
214 | MODIFICATION HISTORY
215 | Date Author Description of Changes
216 | 22-AUG-00 S.Nambiar Created
217 | 18-MAY-01 S.Nambiar Bug 1784818 - Modified the fetch
218 | validate receipt_date to be less
219 | than apply date and gl_date
220 | 19-JUL-01 S.Nambiar Bug 1893041 - When unapp_amount parameter
221 | is not null and unapp_amount_percentage is
222 | null, in unapp_amount_percent calculation
223 | the value should be 100 insted of 0.
224 | 26-SEP-01 R.Jose Bug 1981698
225 | Changed the variable name
226 | c_customer_number to c_customer_id
227 | and modified the definition of
228 | l_customer_number.
229 | 16-JUL-02 S.Nambiar Bug 2276353,Code restructuring done
230 | to addresses performance issues.
231 *=======================================================================*/
232 PROCEDURE create_receipt_writeoff (
233 errbuf IN OUT NOCOPY VARCHAR2,
234 retcode IN OUT NOCOPY VARCHAR2,
235 p_receipt_currency_code IN ar_cash_receipts.currency_code%type,
236 p_unapp_amount IN VARCHAR2,
237 p_unapp_amount_percent IN VARCHAR2,
238 p_receipt_date_from IN VARCHAR2,
239 p_receipt_date_to IN VARCHAR2,
240 p_receipt_gl_date_from IN VARCHAR2,
241 p_receipt_gl_date_to IN VARCHAR2,
242 p_receipt_method_id IN VARCHAR2,
243 p_customer_number IN VARCHAR2,
244 p_receipt_number IN ar_cash_receipts.receipt_number%type,
245 p_receivable_trx_id IN VARCHAR2,
246 p_apply_date IN VARCHAR2,
247 p_gl_date IN VARCHAR2,
248 p_comments IN ar_receivable_applications.comments%type
249 ) IS
250
251 --Declare Local Variables
252 l_msg_count NUMBER;
253 l_msg_data VARCHAR2(240);
254 l_return_status VARCHAR2(1);
255
256 l_unapp_amount ar_receivable_applications.amount_applied%type;
257 l_unapp_amount_percent NUMBER;
258 l_receipt_date_from ar_cash_receipts.receipt_date%type;
259 l_receipt_date_to ar_cash_receipts.receipt_date%type;
260 l_receipt_gl_date_from ar_receivable_applications.gl_date%type;
261 l_receipt_gl_date_to ar_receivable_applications.gl_date%type;
262 l_receipt_method_id ar_cash_receipts.receipt_method_id%type;
263
264 -- Bug 1981698 Changed the definition for l_customer_number.
265
266 l_customer_number hz_cust_accounts.account_number%type;
267 l_customer_id hz_cust_accounts.cust_account_id%type;
268 l_receivable_trx_id ar_receivable_applications.receivables_trx_id%type;
269 l_apply_date ar_cash_receipts.receipt_date%type;
270 l_gl_date ar_cash_receipts.receipt_date%type;
271 l_receipt_currency_code ar_cash_receipts.currency_code%type;
272 l_receipt_number ar_cash_receipts.receipt_number%type;
273 l_comments ar_receivable_applications.comments%type;
274 l_user_id NUMBER;
275 l_application_ref_type ar_receivable_applications.application_ref_type%TYPE;
276 l_application_ref_id ar_receivable_applications.application_ref_id%TYPE;
277 l_application_ref_num ar_receivable_applications.application_ref_num%TYPE;
278 l_secondary_application_ref_id ar_receivable_applications.secondary_application_ref_id%TYPE;
279 l_receivable_application_id NUMBER;
280
281 l_unapp_amount_balance NUMBER := 0;
282 l_written_off_amount ar_receivable_applications.amount_applied%TYPE;
283 l_tot_write_off_amount ar_receivable_applications.amount_applied%TYPE;
284 l_min_wrt_off_amount ar_receivable_applications.amount_applied%TYPE;
285 l_max_wrt_off_amount ar_receivable_applications.amount_applied%TYPE;
286 l_tot_writeoff_amt_func ar_receivable_applications.amount_applied%TYPE;
287 l_exchange_rate ar_cash_receipts.exchange_rate%TYPE;
288 l_actual_writeoff_amount NUMBER := 0;
289 l_rcpt_percent_amount NUMBER;
290
291 l_amount_to NUMBER;
292 l_amount_from NUMBER;
293 l_functional_currency ar_cash_receipts.currency_code%TYPE;
294 l_request_id NUMBER;
295 l_number_of_records_writtenoff NUMBER := 0;
296
297 /*5444407*/
298 l_batch_id ar_batches.batch_id%type;
299 i NUMBER;
300 l_cnt NUMBER;
301
302 --Declare Cursor
303 CURSOR writeoff_cur(c_receipt_currency_code ar_cash_receipts.currency_code%type,
304 c_receipt_date_from ar_cash_receipts.receipt_date%type,
305 c_receipt_date_to ar_cash_receipts.receipt_date%type,
306 c_receipt_gl_date_from ar_receivable_applications.gl_date%type,
307 c_receipt_gl_date_to ar_receivable_applications.gl_date%type,
308 c_receipt_method_id ar_cash_receipts.receipt_method_id%type,
309 --Bug 1981698 Changed c_customer_number to c_customer_id.
310 c_customer_id ar_cash_receipts.pay_from_customer%type,
311 c_receipt_number ar_cash_receipts.receipt_number%type,
312 c_apply_date ar_cash_receipts.receipt_date%type,
313 c_gl_date ar_cash_receipts.receipt_date%type,
314 c_user_id ar_receivable_applications.created_by%Type
315 ) IS
316
317 /*5444407*/
318 SELECT acr.cash_receipt_id cash_receipt_id,
319 acr.receipt_number receipt_number,
320 acr.amount,
321 NVL(acr.exchange_rate,1) exchange_rate,
322 SUM(DECODE(app.status,'UNAPP',NVL(app.amount_applied_from,app.amount_applied),0)) unapplied_amount,
323 SUM(DECODE(app.status,'ACTIVITY',DECODE(applied_payment_schedule_id,-3,
324 DECODE(app.created_by,c_user_id,
325 NVL(app.amount_applied_from,app.amount_applied),0),0),0)) written_off_amount,
326 crh.batch_id batch_id
327 FROM ar_payment_schedules ps,
328 ar_cash_receipts acr,
329 ar_cash_receipt_history crh,
330 ar_receivable_applications app
331 WHERE ps.invoice_currency_code = c_receipt_currency_code
332 AND acr.cash_receipt_id = ps.cash_receipt_id
333 AND acr.cash_receipt_id = crh.cash_receipt_id
334 AND crh.current_record_flag = 'Y'
335 AND acr.cash_receipt_id = app.cash_receipt_id
336 AND ps.status = 'OP'
337 AND ps.class = 'PMT'
338 AND ps.trx_date BETWEEN NVL(c_receipt_date_from,ps.trx_date)
339 AND NVL(c_receipt_date_to,ps.trx_date)
340 AND ps.gl_date BETWEEN NVL(c_receipt_gl_date_from,ps.gl_date)
341 AND NVL(c_receipt_gl_date_to,ps.gl_date)
342 AND ps.trx_date <= c_apply_date
343 AND ps.gl_date <= c_gl_date
344 AND acr.receipt_method_id = NVL(c_receipt_method_id,acr.receipt_method_id)
345 AND acr.pay_from_customer = NVL(c_customer_id,acr.pay_from_customer)
346 AND acr.receipt_number = NVL(c_receipt_number,acr.receipt_number)
347 group by acr.cash_receipt_id,acr.receipt_number,acr.amount,
348 acr.exchange_rate,crh.batch_id;
349
350 BEGIN
351 arp_util.debug('arp_process_writeoff.creare_receipt_writeoff()+');
352
353 arp_util.debug('p_receipt curreny '||p_receipt_currency_code);
354 arp_util.debug('p_unapp_amount '||p_unapp_amount);
355 arp_util.debug('p_unapp_amount_percent '||p_unapp_amount_percent);
356 arp_util.debug('p_receipt_date_from '||p_receipt_date_from);
357 arp_util.debug('p_receipt_date_to '||p_receipt_date_to);
358 arp_util.debug('p_receipt_gl_date_from '||p_receipt_gl_date_from);
359 arp_util.debug('p_receipt_gl_date_to '||p_receipt_gl_date_to);
360 arp_util.debug('p_receipt_method_id '||p_receipt_method_id);
361 arp_util.debug('p_customer_number '||p_customer_number);
362 arp_util.debug('p_receivable_trx_id '||p_receivable_trx_id);
363 arp_util.debug('p_apply_date '||p_apply_date);
364 arp_util.debug('p_gl_date '||p_gl_date);
365 arp_util.debug('p_receipt_number '||p_receipt_number);
366 arp_util.debug('p_comments '||p_comments);
367
368 --Convert the IN variables
369 l_unapp_amount := FND_NUMBER.CANONICAL_TO_NUMBER(p_unapp_amount);
370 l_unapp_amount_percent := FND_NUMBER.CANONICAL_TO_NUMBER(p_unapp_amount_percent);
371 l_receipt_date_from := FND_DATE.CANONICAL_TO_DATE(p_receipt_date_from);
372 l_receipt_date_to := FND_DATE.CANONICAL_TO_DATE(p_receipt_date_to);
373 l_receipt_gl_date_from := FND_DATE.CANONICAL_TO_DATE(p_receipt_gl_date_from);
374 l_receipt_gl_date_to := FND_DATE.CANONICAL_TO_DATE(p_receipt_gl_date_to);
375 l_receipt_method_id := FND_NUMBER.CANONICAL_TO_NUMBER(p_receipt_method_id);
376 l_customer_number := p_customer_number;
377 l_receivable_trx_id := FND_NUMBER.CANONICAL_TO_NUMBER(p_receivable_trx_id) ;
378 l_apply_date := FND_DATE.CANONICAL_TO_DATE(p_apply_date);
379 l_gl_date := FND_DATE.CANONICAL_TO_DATE(p_gl_date);
380
381 l_receipt_currency_code := p_receipt_currency_code;
382 l_receipt_number := p_receipt_number;
383 l_comments := p_comments;
384 l_user_id := arp_global.user_id;
385 l_request_id := fnd_global.conc_request_id;
386
387
388 --Intialize the out NOCOPY variable
389
390 l_return_status := FND_API.G_RET_STS_SUCCESS;
391
392 --Get the approval limits of the user
393 BEGIN
394 SELECT NVL(amount_from,0),
395 NVL(amount_to,0)
396 INTO l_amount_from,
397 l_amount_to
398 FROM ar_approval_user_limits
399 WHERE currency_code = l_receipt_currency_code
400 AND user_id = arp_global.user_id
401 AND document_type ='WRTOFF';
402 EXCEPTION
403 WHEN NO_DATA_FOUND THEN
404 l_amount_from := 0;
405 l_amount_to := 0;
406 END;
407
408 /* Bug fix 3385020
409 The validations should be done only if both the amount and percentage are
410 not zero or NULL */
411 IF l_unapp_amount_percent = 0
412 OR l_unapp_amount = 0
413 OR (l_unapp_amount_percent IS NULL AND l_unapp_amount IS NULL) THEN
414 null;
415 ELSE
416 --Get the customer id for the customer number
417 IF p_customer_number IS NOT NULL
418 THEN
419 l_customer_id := to_number(arp_util.Get_Id( 'CUSTOMER_NUMBER',
420 l_customer_number,
421 l_return_status));
422 IF l_customer_id IS NULL THEN
423 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_NUM_INVALID');
424 FND_MSG_PUB.Add;
425 l_return_status := FND_API.G_RET_STS_ERROR;
426 END IF;
427 END IF;
428
429 arp_util.debug('Customer ID '||to_char(l_customer_id));
430
431 arp_util.debug('User Approval Limits From : '||to_char(l_amount_from)||
432 'To : '||to_char(l_amount_to));
433
434
435 --Get the functional currency
436 l_functional_currency := arp_global.functional_currency;
437 l_min_wrt_off_amount := arp_global.sysparam.min_wrtoff_amount;
438 arp_util.debug('Minimum write-off limit :'||to_char(l_min_wrt_off_amount));
439 l_max_wrt_off_amount := arp_global.sysparam.max_wrtoff_amount;
440 arp_util.debug('Maximum write-off limit :'||to_char(l_max_wrt_off_amount));
441
442 IF l_max_wrt_off_amount IS NULL THEN
443 fnd_message.set_name ('AR','AR_SYSTEM_WR_NO_LIMIT_SET');
444 app_exception.raise_exception;
445 END IF;
446
447 arp_util.debug('Functional Currency :'||l_functional_currency);
448
449 FOR writeoff_rec IN writeoff_cur(l_receipt_currency_code,
450 l_receipt_date_from,
451 l_receipt_date_to,
452 l_receipt_gl_date_from,
453 l_receipt_gl_date_to,
454 l_receipt_method_id,
455 l_customer_id,
456 l_receipt_number,
457 l_apply_date,
458 l_gl_date,
459 l_user_id
460 )
461 LOOP
462
463 IF writeoff_rec.unapplied_amount > 0 THEN
464
465 --Get Unapplied amount on the receipt
466 l_unapp_amount_balance := writeoff_rec.unapplied_amount ;
467
468 --Get written off amount by the use logged in.
469 l_written_off_amount := writeoff_rec.written_off_amount;
470
471 l_tot_write_off_amount := l_written_off_amount + l_unapp_amount_balance;
472 -- Bug 3136127 - moved check on user limits to before system checks
473 -- to prevent system limit check being overridden
474
475 IF (l_tot_write_off_amount >= l_amount_from)
476 AND (l_tot_write_off_amount <= l_amount_to) THEN
477 l_actual_writeoff_amount := NVL(l_unapp_amount_balance,0);
478 ELSE
479 l_actual_writeoff_amount := 0;
480 END IF;
481
482 -- Bug 3136127 - included checks on minimum system limit
483 IF l_tot_write_off_amount < 0 THEN
484 IF l_min_wrt_off_amount IS NULL THEN
485 fnd_message.set_name ('AR','AR_SYSTEM_WR_NO_LIMIT_SET');
486 app_exception.raise_exception;
487 END IF;
488 END IF;
489
490 IF l_functional_currency <> l_receipt_currency_code THEN
491
492 l_exchange_rate := writeoff_rec.exchange_rate;
493
494 l_tot_writeoff_amt_func := arpcurr.functional_amount(
495 l_tot_write_off_amount,
496 l_functional_currency,
497 l_exchange_rate,
498 arp_global.base_precision,
499 arp_global.base_min_acc_unit);
500
501 IF (NVL(l_tot_writeoff_amt_func,0)) > l_max_wrt_off_amount THEN
502 l_actual_writeoff_amount := 0;
503 END IF;
504 IF (NVL(l_tot_writeoff_amt_func,0) < 0) THEN
505 IF (NVL(l_tot_writeoff_amt_func,0) < l_min_wrt_off_amount) THEN
506 l_actual_writeoff_amount := 0;
507 END IF;
508 END IF;
509 ELSE
510 IF (l_tot_write_off_amount > l_max_wrt_off_amount) THEN
511 l_actual_writeoff_amount := 0;
512 END IF;
513 IF (l_tot_write_off_amount < 0) THEN
514 IF (l_tot_write_off_amount < l_min_wrt_off_amount) THEN
515 l_actual_writeoff_amount := 0;
516 END IF;
517 END IF;
518 END IF;
519
520 --If the write-off amount total including the amount already written-off previously
521 --for this receipt, is not within the limit then this receipt can't be written off
522 --So return 0. Otherwise, return the balance unapplied amount.
523
524 --Check whether the write-off amount is less than the passed amount
525 /* Bug fix 3385020 : The comparison should be made only if the amount is passed */
526 IF l_unapp_amount IS NOT NULL and l_actual_writeoff_amount > l_unapp_amount THEN
527 l_actual_writeoff_amount := 0;
528 END IF;
529
530 --Check whether the write-off amount is less than the passed amount
531 /* Bug fix 3385020 : Changed the condition to check for NULL value alone */
532 IF l_unapp_amount_percent IS NOT NULL THEN
533 l_rcpt_percent_amount := ((NVL(writeoff_rec.amount,0) * l_unapp_amount_percent)/100);
534
535 IF l_actual_writeoff_amount > NVL(l_rcpt_percent_amount,0) THEN
536 l_actual_writeoff_amount := 0;
537 END IF;
538 END IF;
539
540 IF l_actual_writeoff_amount <> 0 THEN
541
542 /*5444407*/
543 IF NOT (arp_process_writeoff.gt_rec_wrt_off_type.EXISTS(writeoff_rec.batch_id)) AND
544 writeoff_rec.batch_id is not null THEN
545 arp_process_writeoff.gt_rec_wrt_off_type(writeoff_rec.batch_id).batch_id:=writeoff_rec.batch_id;
546 END IF;
547
548 --Calling Activity_application Procedure
549 ar_receipt_api_pub.activity_application(
550 p_api_version => 1.0 ,
551 x_return_status => l_return_status ,
552 x_msg_count => l_msg_count ,
553 x_msg_data => l_msg_data ,
554 p_cash_receipt_id => writeoff_rec.cash_receipt_id ,
555 p_receipt_number => '',
556 p_amount_applied => l_actual_writeoff_amount ,
557 p_applied_payment_schedule_id => -3 ,
558 p_receivables_trx_id => l_receivable_trx_id ,
559 p_apply_date => l_apply_date ,
560 p_apply_gl_date => l_gl_date ,
561 p_comments => l_comments ,
562 p_application_ref_type => l_application_ref_type ,
563 p_application_ref_id => l_application_ref_id ,
564 p_application_ref_num => l_application_ref_num ,
565 p_secondary_application_ref_id => l_secondary_application_ref_id,
566 p_receivable_application_id => l_receivable_application_id,
567 p_called_from => 'WRITEOFF'
568 );
569
570
571 l_number_of_records_writtenoff := l_number_of_records_writtenoff + 1;
572
573 END IF;
574
575 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS )
576 THEN
577
578 arp_util.debug('arp_process_writeoff - Failed for Receipt : '||
579 writeoff_rec.receipt_number );
580 arp_util.debug('p_return_status : ' || l_return_status);
581 arp_util.debug('p_msg_count : ' || l_msg_count);
582 arp_util.debug('p_msg_data : ' || l_msg_data);
583
584 --Bug 1788596 - begin changes
585
586 IF l_msg_count = 1 THEN
587
588 FND_MESSAGE.SET_NAME ('AR', 'GENERIC_MESSAGE');
589 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_msg_data);
590 app_exception.raise_exception;
591 ELSIF l_msg_count > 1 THEN
592 --retrive only first messages from the stack and display it .
593
594 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
595
596 FND_MESSAGE.SET_NAME ('AR', 'GENERIC_MESSAGE');
597 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_msg_data);
598 app_exception.raise_exception;
599
600 END IF;
601
602 END IF;
603
604 END IF; --if unapplied amount > 0
605
606 END LOOP;
607
608 /*5444407*/
609 IF arp_process_writeoff.gt_rec_wrt_off_type.COUNT <> 0 THEN
610 l_cnt:=arp_process_writeoff.gt_rec_wrt_off_type.COUNT;
611 arp_util.debug(' Count of records in PL/SQL table :' || to_char(l_cnt));
612 i := arp_process_writeoff.gt_rec_wrt_off_type.FIRST;
613 WHILE i IS NOT NULL LOOP
614 l_batch_id:=arp_process_writeoff.gt_rec_wrt_off_type(i).batch_id;
615 arp_rw_batches_check_pkg.update_batch_status(l_batch_id);
616 i:=arp_process_writeoff.gt_rec_wrt_off_type.NEXT(i);
617 END LOOP;
618 END IF;
619
620 IF l_number_of_records_writtenoff > 0 THEN
621 --Since report submission is another concurrent program,
622 --commit the records processed before submitting the report
623
624 COMMIT;
625
626 --for 1644863
627 arp_process_writeoff.submit_report(l_receipt_currency_code,l_apply_date,l_gl_date);
628 END IF;
629 END IF;
630 arp_util.debug('Total Number of Receipts written off :'||to_char(l_number_of_records_writtenoff));
631
632 arp_util.debug('arp_process_writeoff.creare_receipt_writeoff()-');
633
634 EXCEPTION
635 WHEN OTHERS THEN
636 arp_util.debug('EXCEPTION:arp_process_writeoff.cretre_receipt_writeoff()'||SQLERRM);
637 RAISE;
638
639 END;
640 /*========================================================================
641 | PUBLIC FUNCTIONS unapplied_amount
642 |
643 | DESCRIPTION
644 | This procedure calculates the unapplied amount for the given receipt
645 |
646 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
647 | arp_process_writeoff.create_receipt_writeoff()
648 |
649 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
650 | NONE
651 | PARAMETERS
652 | p_cash_receipt_id IN Cash Receipt ID of the receipt
653 | p_request_id IN Concurrent Request Id
654 |
655 | RETURNS : unapplied_amount
656 |
657 | KNOWN ISSUES
658 |
659 | NOTES
660 | This functiona is also called from the Report ARXRCWRT.
661 | When request id is passed, then this functions returns the
662 | unapplied amount for the particular request id.When request id is not
663 | passed then it return the unapplied amount for the whole receipt.
664 |
665 | MODIFICATION HISTORY
666 | Date Author Description of Changes
667 | 22-AUG-00 S.Nambiar Created
668 | 19-MAY-01 S.Nambiar Bug 1784778 - UNPPP sum should not
669 | include OTHER ACC paired UNAPP
670 | 01-JUN-00 S.Nambiar Bug 1809395 - Write-off should check
671 | the system level limit setup.
672 | 01-JUN-00 S.Nambiar Bug 1788614 - auto Write-off should check
673 | how much the user has already written off
674 | on the receipt.
675 +===========================================================================*/
676 FUNCTION unapplied_amount(p_cash_receipt_id IN NUMBER,
677 p_currency_code IN ar_cash_receipts.currency_code%TYPE,
678 p_user_id IN ar_approval_user_limits.user_id%TYPE,
679 p_request_id IN NUMBER DEFAULT NULL, /*5444407*/
680 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE
681 default NULL,
682 p_amount_from IN NUMBER default null,
683 p_amount_to IN NUMBER default null)
684 RETURN number IS
685
686 l_unapp_amount_balance NUMBER := 0;
687 l_written_off_amount ar_receivable_applications.amount_applied%TYPE;
688 l_tot_write_off_amount ar_receivable_applications.amount_applied%TYPE;
689 l_max_wrt_off_amount ar_receivable_applications.amount_applied%TYPE;
690 l_tot_writeoff_amt_func ar_receivable_applications.amount_applied%TYPE;
691 l_exchange_rate ar_cash_receipts.exchange_rate%TYPE;
692
693 l_amount_to NUMBER;
694 l_amount_from NUMBER;
695 l_functional_currency ar_cash_receipts.currency_code%TYPE;
696
697 BEGIN
698 arp_util.debug('arp_process_writeoff.unapplied_amount()+');
699 l_functional_currency := arp_global.functional_currency;
700
701 IF nvl(p_request_id,0) = 0
702 THEN
703 SELECT SUM(DECODE(ra.status,'UNAPP',NVL(ra.amount_applied, 0),0)) unapplied_amount,
704 SUM(decode(ra.status,'ACTIVITY',decode(ra.applied_payment_schedule_id,-3,
705 decode(ra.created_by,p_user_id,NVL(ra.amount_applied,0),0),0),0)) written_off_amount
706 INTO l_unapp_amount_balance,l_written_off_amount
707 FROM ar_receivable_applications ra
708 WHERE ra.cash_receipt_id = p_cash_receipt_id
709 AND ra.status in ('UNAPP','ACTIVITY');
710
711 --Check how much amount has been written-off by this user for this receipt
712 /* Bug 2479793 : The index on applied_payment_schedule_id is supressed as
713 CBO sometimes find this better than index on cash_receipt_id */
714 /*SELECT NVL(SUM(NVL(ra.amount_applied, 0)),0) written_off_amount
715 INTO l_written_off_amount
716 FROM ar_receivable_applications ra
717 WHERE ra.cash_receipt_id = p_cash_receipt_id
718 AND ra.status = 'ACTIVITY'
719 AND ra.applied_payment_schedule_id + 0 = -3
720 AND ra.created_by = p_user_id;*/
721
722 --Get the approval limits of the user
723 l_amount_from := p_amount_from;
724 l_amount_to := p_amount_to;
725 If l_amount_from is null and l_amount_to is null then
726 BEGIN
727 SELECT NVL(amount_from,0),
728 NVL(amount_to,0)
729 INTO l_amount_from,
730 l_amount_to
731 FROM ar_approval_user_limits
732 WHERE currency_code = p_currency_code
733 AND user_id = p_user_id
734 AND document_type ='WRTOFF';
735 EXCEPTION
736 WHEN NO_DATA_FOUND THEN
737 l_amount_from := 0;
738 l_amount_to := 0;
739 END;
740 end if;
741
742 --In case of cross currency,the write-off amount should not exceed the maximum
743 --write-off limit set at the system level. For that, we need to take the exchange
744 --rate from the receipt and calculate the write-off amount and validate with
745 --system limit setup.
746
747 /*5444407*/
748 l_max_wrt_off_amount:=arp_global.sysparam.max_wrtoff_amount;
749
750 l_tot_write_off_amount := l_written_off_amount + l_unapp_amount_balance;
751
752 IF l_functional_currency <> p_currency_code THEN
753
754 /*5444407*/
755 IF p_exchange_rate is null then
756 SELECT nvl(exchange_rate,1)
757 INTO l_exchange_rate
758 FROM ar_cash_receipts
759 WHERE cash_receipt_id = p_cash_receipt_id;
760 END IF;
761
762 l_tot_writeoff_amt_func := arpcurr.functional_amount(
763 l_tot_write_off_amount,
764 l_functional_currency,
765 l_exchange_rate,
766 arp_global.base_precision,
767 arp_global.base_min_acc_unit);
768
769 IF (NVL(l_tot_writeoff_amt_func,0)) > l_max_wrt_off_amount THEN
770 RETURN (0);
771 END IF;
772 ELSE
773 IF (l_tot_write_off_amount > l_max_wrt_off_amount) THEN
774 RETURN (0);
775 END IF;
776
777 END IF;
778
779 --If the write-off amount total including the amount already written-off previously
780 --for this receipt, is not within the limit then this receipt can't be written off
781 --So return 0. Otherwise, return the balance unapplied amount.
782
783 IF (l_tot_write_off_amount >= l_amount_from)
784 AND (l_tot_write_off_amount <= l_amount_to) THEN
785 RETURN (l_unapp_amount_balance);
786 ELSE
787 RETURN (0);
788 END IF;
789
790 ELSE
791 SELECT NVL(SUM(NVL(ra.amount_applied,0)),10) l_unapplied_amount
792 INTO l_unapp_amount_balance
793 FROM ar_receivable_applications ra
794 WHERE ra.cash_receipt_id = p_cash_receipt_id
795 AND status = 'ACTIVITY'
796 AND ra.request_id = p_request_id;
797
798 RETURN (l_unapp_amount_balance);
799 END IF;
800
801 arp_util.debug('arp_process_writeoff.unapplied_amount()+');
802
803 EXCEPTION
804 WHEN OTHERS THEN
805 arp_util.debug('EXCEPTION :arp_process_writeoff.unapplied_amount()'
806 ||SQLERRM);
807 RETURN (0);
808 END;
809 /*========================================================================
810 | PUBLIC FUNCTIONS applied_amount
811 |
812 | DESCRIPTION
813 | This procedure calculates the applied amount for the given receipt
814 |
815 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
816 | arp_process_writeoff.create_receipt_writeoff()
817 |
818 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
819 | NONE
820 | PARAMETERS
821 | p_cash_receipt_id IN Cash Receipt ID of the receipt
822 | p_request_id IN Concurrent Request Id
823 |
824 | RETURNS : applied_amount
825 |
826 | KNOWN ISSUES
827 |
828 | NOTES
829 | This functiona is also called from the Report ARXRCWRT.
830 | When request id is passed, then this functions returns the
831 | applied amount for the particular request id.When request id is not
832 | passed then it return the applied amount for the whole receipt.
833 |
834 | MODIFICATION HISTORY
835 | Date Author Description of Changes
836 | 22-AUG-00 S.Nambiar Created
837 +===========================================================================*/
838 FUNCTION applied_amount(p_cash_receipt_id IN NUMBER,
839 p_request_id IN NUMBER DEFAULT 0)
840 RETURN number IS
841
842 l_applied_amount NUMBER;
843
844 BEGIN
845 arp_util.debug('arp_process_writeoff.applied_amount()+');
846
847 IF NVL(p_request_id,0) <> 0
848 THEN
849 --Sum of the applied amount excluding the current one with the request id
850 SELECT
851 SUM(DECODE(ra.status,
852 'APP',DECODE(ra.confirmed_flag,
853 'N', 0,
854 NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
855 'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
856 -2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
857 -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
858 -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
859 0)
860 ,0)) applied_amount
861
862 INTO l_applied_amount
863 FROM
864 ar_receivable_applications ra
865 WHERE ra.cash_receipt_id = p_cash_receipt_id
866 AND ra.request_id <> p_request_id;
867 ELSE
868 --Sum of the all the applied amounts for a receipt
869 SELECT
870 SUM(DECODE(ra.status,
871 'APP',DECODE(ra.confirmed_flag,
872 'N', 0,
873 NVL(nvl(ra.amount_applied_from, ra.amount_applied),0)),
874 'ACTIVITY',DECODE(ra.applied_payment_schedule_id,
875 -2,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
876 -3,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
877 -5,NVL(nvl(ra.amount_applied_from, ra.amount_applied),0),
878 0)
879 ,0)) applied_amount
880
881 INTO l_applied_amount
882 FROM
883 ar_receivable_applications ra
884 WHERE ra.cash_receipt_id = p_cash_receipt_id;
885 END IF;
886
887 arp_util.debug('arp_process_writeoff.applied_amount()-');
888 RETURN (l_applied_amount);
889
890 EXCEPTION
891 WHEN OTHERS THEN
892 arp_util.debug('EXCEPTION :arp_process_writeoff.applied_amount()'||SQLERRM);
893 RETURN (0);
894
895 END;
896
897 /*========================================================================
898 | PUBLIC FUNCTIONS on_account_amount
899 |
900 | DESCRIPTION
901 | This procedure calculates the on_account_amount for the given
902 | receipt
903 |
904 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
905 | NONE
906 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
907 | NONE
908 | PARAMETERS
909 | p_cash_receipt_id IN Cash Receipt ID of the receipt
910 |
911 | RETURNS : applied_amount
912 |
913 | KNOWN ISSUES
914 |
915 | NOTES
916 | This functiona is called from the Report ARXRCWRT.
917 |
918 | MODIFICATION HISTORY
919 | Date Author Description of Changes
920 | 22-AUG-00 S.Nambiar Created
921 +=======================================================================*/
922 FUNCTION on_account_amount(p_cash_receipt_id IN NUMBER)
923 RETURN number IS
924
925 l_on_account_amount NUMBER;
926
927 BEGIN
928 arp_util.debug('arp_process_writeoff.on_account_amount()+');
929
930 SELECT
931 SUM(DECODE(ra.status,'ACC', NVL(ra.amount_applied, 0),
932 0)) on_account_amount
933 INTO l_on_account_amount
934 FROM
935 ar_receivable_applications ra
936 WHERE ra.cash_receipt_id = p_cash_receipt_id;
937
938 arp_util.debug('arp_process_writeoff.on_account_amount()-');
939
940 RETURN (l_on_account_amount);
941 EXCEPTION
942 WHEN OTHERS THEN
943 arp_util.debug('arp_process_writeoff.on_account_amount() '||SQLERRM);
944 RETURN (0);
945
946 END;
947
948 /*========================================================================
949 | PUBLIC FUNCTIONS balancing_segment
950 |
951 | DESCRIPTION
952 | This utility returns the balancing segment for a code combination id
953 | passed
954 |
955 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
956 | NONE
957 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
958 | NONE
959 | PARAMETERS
960 | p_code_combination_id IN Code combination ID
961 |
962 | RETURNS : Balancing_segment value
963 |
964 | KNOWN ISSUES
965 |
966 | NOTES
967 | This functiona is called from the Rxi:Other Applications Report.
968 |
969 | MODIFICATION HISTORY
970 | Date Author Description of Changes
971 | 22-AUG-00 S.Nambiar Created
972 +========================================================================*/
973 FUNCTION balancing_segment(p_code_combination_id IN NUMBER)
974 RETURN VARCHAR2 IS
975
976 l_balancing_segment varchar2(25);
977 l_str varchar2(500);
978 l_segment_value varchar2(25);
979
980 BEGIN
981
982 arp_util.debug('arp_process_writeoff.balancing_segment()+');
983
984 SELECT fa_rx_flex_pkg.flex_sql(101,'GL#',chart_of_accounts_id,NULL,
985 'SELECT','GL_BALANCING')
986 INTO l_balancing_segment
987 FROM gl_code_combinations
988 WHERE code_combination_id= p_code_combination_id;
989
990 l_str := 'SELECT '||l_balancing_segment||' INTO :bnd_segment_value
991 FROM gl_code_combinations where code_combination_id=:bind_ccid';
992
993 EXECUTE IMMEDIATE l_str INTO l_segment_value USING p_code_combination_id;
994
995 arp_util.debug('arp_process_writeoff.balancing_segment()-');
996
997 RETURN l_segment_value;
998
999 EXCEPTION
1000 WHEN OTHERS THEN
1001 arp_util.debug('arp_process_writeoff.balancing_segment() '||SQLERRM);
1002 RETURN (NULL);
1003 END;
1004 BEGIN
1005 arp_global.init_global;
1006 END ARP_PROCESS_WRITEOFF;