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