[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_ADJUSTMENT
Source
1 PACKAGE BODY ARP_PROCESS_ADJUSTMENT AS
2 /* $Header: ARTEADJB.pls 120.44.12020000.5 2012/11/15 13:07:34 ashlkuma ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 pg_msg_level_debug binary_integer;
6 pg_user_id binary_integer;
7 pg_text_dummy varchar2(10);
8 pg_base_curr_code gl_sets_of_books.currency_code%type;
9 pg_base_precision fnd_currencies.precision%type;
10 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
11 /* VAT changes */
12 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
13
14
15 /*===========================================================================+
16 | PROCEDURE |
17 | validate_insert_adjustment |
18 | |
19 | DESCRIPTION |
20 | Validates row that is going to be inserted into ar_adjustments |
21 | |
22 | SCOPE - PRIVATE |
23 | |
24 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
25 | arp_util.debug |
26 | |
27 | ARGUMENTS : IN: |
28 | OUT: |
29 | IN/ OUT: |
30 | |
31 | RETURNS : NONE |
32 | |
33 | NOTES |
34 | |
35 | MODIFICATION HISTORY |
36 | 25-AUG-95 Martin Johnson Created |
37 | 09-APR-96 Martin Johnson BugNo:354971. Added call to |
38 | arp_non_db_pkg.check_natural_ |
39 | application to check for overapp. |
40 | 18-AUG-97 Debbie Jancis Bug 715036: prevent saving of adj if |
41 | balances are not correct. |
42 | |
43 +===========================================================================*/
44
45 PROCEDURE validate_insert_adjustment( p_adj_amount IN number,
46 p_payment_schedule_id IN number,
47 p_type IN varchar2 ) IS
48
49 l_amount_due_original number;
50 l_amount_due_remaining number;
51 l_creation_sign varchar2(30);
52 l_allow_overapp_flag varchar2(1);
53
54 BEGIN
55
56 arp_util.debug('arp_process_adjustment.validate_insert_adjustment()+');
57
58 SELECT ps.amount_due_original,
59 ps.amount_due_remaining,
60 ctt.creation_sign,
61 ctt.allow_overapplication_flag
62 INTO l_amount_due_original,
63 l_amount_due_remaining,
64 l_creation_sign,
65 l_allow_overapp_flag
66 FROM
67 ra_cust_trx_types ctt
68 , ar_payment_schedules ps
69 WHERE ps.payment_schedule_id = p_payment_schedule_id
70 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
71
72 arp_util.debug( 'p_type = ' || p_type);
73 arp_util.debug('adj amount = ' || p_adj_amount);
74 arp_util.debug('amount due rem ' || l_amount_due_remaining);
75
76 IF ( p_type = 'INVOICE'
77 and p_adj_amount <> (0 - l_amount_due_remaining)) then
78
79 fnd_message.set_name('AR', 'GENERIC_MESSAGE');
80 fnd_message.set_token('GENERIC_TEXT', 'Internal Error: Your totals are o
81 ut of balance. Please requery the receipt applications.');
82 app_exception.raise_exception;
83
84 ELSE
85
86 arp_non_db_pkg.check_natural_application(
87 l_creation_sign,
88 l_allow_overapp_flag,
89 'N',
90 '+',
91 null,
92 p_adj_amount,
93 0,
94 l_amount_due_remaining,
95 l_amount_due_original );
96 END IF;
97
98 /* 12926153 - Prevent creation of adjustment if there exists
99 unearned revenue on the target invoice */
100 DECLARE
101 l_inv_amount NUMBER;
102 l_cm_amount NUMBER;
103 l_uns_amount NUMBER;
104 BEGIN
105 /* This invoice either has rules or created with contingency */
106 SELECT SUM(NVL(d.amount,0)) amount
107 INTO l_inv_amount
108 FROM ra_cust_trx_line_gl_dist d,
109 ar_payment_schedules p
110 WHERE p.payment_schedule_id = p_payment_schedule_id
111 AND d.customer_trx_id = p.customer_trx_id
112 AND d.account_class = 'UNEARN'
113 AND d.account_set_flag = 'N';
114
115 IF l_inv_amount IS NOT NULL
116 THEN
117 /* There can any CM applied on this invoice that offsets the Unearn
118 balance on the invoice */
119 SELECT SUM(NVL(d.amount,0)) amount
120 INTO l_cm_amount
121 FROM ra_cust_trx_line_gl_dist d,
122 ra_customer_trx t,
123 ar_payment_schedules p
124 WHERE p.payment_schedule_id = p_payment_schedule_id
125 AND p.customer_trx_id = t.previous_customer_trx_id
126 AND d.customer_trx_id = t.customer_trx_id
127 AND d.account_class = 'UNEARN'
128 AND d.account_set_flag = 'N';
129
130 l_uns_amount := l_inv_amount + NVL(l_cm_amount,0);
131
132 IF l_uns_amount <> 0
133 THEN
134 /* Raise message and halt process */
135 arp_util.debug('Unearned balance exists for this transaction');
136 fnd_message.set_name('AR', 'AR_UNEARN_PREV_ADJ');
137 app_exception.raise_exception;
138 END IF;
139 END IF;
140 EXCEPTION
141 WHEN OTHERS THEN
142 arp_util.debug('Exception during Unearned test: ' || SQLERRM);
143 RAISE;
144 END;
145
146 arp_util.debug('arp_process_adjustment.validate_insert_adjustment()-');
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 arp_util.debug(
151 'EXCEPTION: arp_process_adjustment.validate_insert_adjustment()');
152 RAISE;
153
154 END;
155
156 /*===========================================================================+
157 | PROCEDURE |
158 | validate_update_adjustment |
159 | |
160 | DESCRIPTION |
161 | Validates row that is going to be updateded in ar_adjustments |
162 | |
163 | SCOPE - PRIVATE |
164 | |
165 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
166 | arp_util.debug |
167 | |
168 | ARGUMENTS : IN: |
169 | OUT: |
170 | IN/ OUT: |
171 | |
172 | RETURNS : NONE |
173 | |
174 | NOTES |
175 | |
176 | MODIFICATION HISTORY |
177 | 06-SEP-95 Martin Johnson Created |
178 | 18-APR-96 Martin Johnson BugNo:357974. Check for |
179 | overapplication when the adjustment |
180 | is approved. |
181 | |
182 +===========================================================================*/
183
184 PROCEDURE validate_update_adjustment(p_payment_schedule_id IN number,
185 p_adj_amount IN number,
186 p_type IN varchar2,
187 p_status_changed_flag IN boolean,
188 p_status IN varchar2,
189 p_tax_adjusted IN number )
190 IS
191
192 l_type_adr number;
193 l_type_ado number;
194 l_amount_due_original number;
195 l_amount_due_remaining number;
196 l_creation_sign varchar2(30);
197 l_allow_overapp_flag varchar2(1);
198 /* VAT changes */
199 l_tax_remaining number;
200 l_tax_original number;
201
202 BEGIN
203
204 arp_util.debug('arp_process_adjustment.validate_update_adjustment()+');
205
206 IF ( p_status_changed_flag ) AND
207 ( p_status = 'A' )
208 THEN
209
210 /*------------------------------------------------------------+
211 | If status changed to Approved, check for overapplication |
212 +------------------------------------------------------------*/
213
214 SELECT NVL(DECODE(p_type,
215 'CHARGES', ps.amount_due_remaining,
216 'INVOICE', ps.amount_due_remaining,
217 'FREIGHT', ps.freight_remaining,
218 'LINE', ps.amount_line_items_remaining,
219 'TAX', ps.tax_remaining ),
220 0 ),
221 NVL(DECODE(p_type,
222 'CHARGES', ps.amount_due_original,
223 'INVOICE', ps.amount_due_original,
224 'FREIGHT', ps.freight_original,
225 'LINE', ps.amount_line_items_original,
226 'TAX', ps.tax_original ),
227 0 ),
228 ps.tax_remaining,
229 ps.tax_original,
230 ps.amount_due_remaining,
231 ps.amount_due_original,
232 ctt.creation_sign,
233 ctt.allow_overapplication_flag
234 INTO l_type_adr,
235 l_type_ado,
236 /* VAT changes */
237 l_tax_remaining,
238 l_tax_original,
239 l_amount_due_remaining,
240 l_amount_due_original,
241 l_creation_sign,
242 l_allow_overapp_flag
243 FROM ar_payment_schedules ps,
244 ra_cust_trx_types ctt
245 WHERE ps.payment_schedule_id = p_payment_schedule_id
246 AND ps.cust_trx_type_id = ctt.cust_trx_type_id;
247
248 IF ( p_type = 'INVOICE' )
249 THEN
250 /*----------------------------------------------------------+
251 | Invoice type adjustment must make the balance due zero |
252 +----------------------------------------------------------*/
253
254 IF ( l_amount_due_remaining + p_adj_amount <> 0 )
255 THEN fnd_message.set_name('AR', 'AR_TW_VAL_AMT_ADJ_INV');
256 app_exception.raise_exception;
257 END IF;
258
259 ELSE
260
261 /*----------------------------------------------------------+
262 | Check for overapplication based on the adjustment type |
263 +----------------------------------------------------------*/
264
265 arp_non_db_pkg.check_natural_application(
266 l_creation_sign,
267 l_allow_overapp_flag,
268 'N',
269 '+',
270 null,
271 /* VAT changes */
272 p_adj_amount - nvl(p_tax_adjusted, 0),
273 0,
274 l_type_adr,
275 l_type_ado );
276
277 /*------------------------------------+
278 | Check for overapplication of tax |
279 +------------------------------------*/
280
281 IF p_type in ('CHARGES', 'LINE') and
282 nvl(p_tax_adjusted,0) <> 0 THEN
283 arp_non_db_pkg.check_natural_application(
284 l_creation_sign,
285 l_allow_overapp_flag,
286 'N',
287 '+',
288 null,
289 p_tax_adjusted,
290 0,
291 l_tax_remaining,
292 l_tax_original );
293 END IF;
294
295
296 /*-----------------------------------------------------+
297 | Check for overapplication of amount_due_remaining |
298 +-----------------------------------------------------*/
299
300 arp_non_db_pkg.check_natural_application(
301 l_creation_sign,
302 l_allow_overapp_flag,
303 'N',
304 '+',
305 null,
306 p_adj_amount,
307 0,
308 l_amount_due_remaining,
309 l_amount_due_original );
310
311 END IF;
312
313 END IF;
314
315 arp_util.debug('arp_process_adjustment.validate_update_adjustment()-');
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
320 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_non_db_pkg.check_natural_application exception: '||SQLERRM );
321 arp_util.debug(
322 'EXCEPTION: arp_process_adjustment.validate_update_adjustment()');
323 RAISE;
324
325 END;
326
327 /*===========================================================================+
328 | PROCEDURE |
329 | set_flags |
330 | |
331 | DESCRIPTION |
332 | Sets various change and status flags for the current record. |
333 | |
334 | SCOPE - PRIVATE |
335 | |
336 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
337 | arp_util.debug |
338 | |
339 | ARGUMENTS : IN: |
340 | p_adjustment_id |
341 | p_new_adj_rec |
342 | OUT: |
343 | p_status_changed_flag |
344 | IN/ OUT: |
345 | None |
346 | |
347 | RETURNS : NONE |
348 | |
349 | NOTES |
350 | |
351 | MODIFICATION HISTORY |
352 | 06-SEP-95 Martin Johnson Created |
353 | |
354 +===========================================================================*/
355
356 PROCEDURE set_flags(
357 p_adjustment_id IN ar_adjustments.adjustment_id%type,
358 p_old_adj_rec IN ar_adjustments%rowtype,
359 p_new_adj_rec IN ar_adjustments%rowtype,
360 p_status_changed_flag OUT NOCOPY boolean)
361
362 IS
363
364 BEGIN
365
366 arp_util.debug('ar_process_adjustment.set_flags()+',
367 pg_msg_level_debug);
368
369 arp_util.debug('p_old_adj_rec.status: ' || p_old_adj_rec.status );
370 arp_util.debug('p_new_adj_rec.status: ' || p_new_adj_rec.status );
371 arp_util.debug('pg_text_dummy: ' || pg_text_dummy );
372
373 IF (
374 nvl(p_old_adj_rec.status, '!@#$%') <>
375 nvl(p_new_adj_rec.status, '!@#$%')
376 AND
377 nvl(p_new_adj_rec.status, '!@#$%') <> pg_text_dummy
378 )
379 THEN p_status_changed_flag := TRUE;
380 ELSE p_status_changed_flag := FALSE;
381 END IF;
382
383 arp_util.debug('ar_process_adjustment.set_flags()-',
384 pg_msg_level_debug);
385
386 EXCEPTION
387 WHEN OTHERS THEN
388
389 /*---------------------------------------------+
390 | Display parameters and raise the exception |
391 +---------------------------------------------*/
392
393 arp_util.debug('EXCEPTION: arp_process_adjustment.set_flags()');
394
395 arp_util.debug('');
396 arp_util.debug('---------- parameters for set_flags() ---------');
397
398 arp_util.debug('p_adjustment_id = ' || p_adjustment_id);
399 arp_util.debug('');
400
401 arp_util.debug('---------- new adjustment record ----------');
402 arp_adjustments_pkg.display_adj_rec( p_new_adj_rec );
403 arp_util.debug('');
404
405 RAISE;
406
407 END;
408
409 /*===========================================================================+
410 | PROCEDURE |
411 | validate_inv_line_amount |
412 | |
413 | DESCRIPTION |
414 | validates that the adjustment is not for more than available invoiced |
415 | line amount. |
416 | |
417 | SCOPE - PRIVATE |
418 | |
419 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
420 | arp_util.debug |
421 | |
422 | ARGUMENTS : IN: |
423 | p_adj_rec |
424 | p_ps_rec |
425 | OUT: |
426 | IN/ OUT: |
427 | |
428 | NOTES |
429 | |
430 | MODIFICATION HISTORY |
431 | 08-SEP-95 Charlie Tomberg Created
432 | 7/16/1996 Harri Kaukovuo Bug 382421. Line level adjustment
433 | check was looking for header level
434 | applications from
435 | ar_receivable_applications.
436 +===========================================================================*/
437
438 PROCEDURE validate_inv_line_amount(p_adj_rec IN
439 ar_adjustments%rowtype,
440 p_ps_rec IN ar_payment_schedules%rowtype)
441 IS
442
443 CURSOR cu_rem_amt(p_customer_trx_id IN NUMBER, p_customer_trx_line_id IN NUMBER) IS
444 SELECT b.sum_orig sum_orig
445 ,b.sum_acctd_orig sum_acctd_orig
446 ,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',amt
447 ,'APP_CHRG',DECODE(a.line_type,'LINE',amt,0) * -1,0))) CHRG_ON_REV_LINE
448 ,SUM((DECODE(a.activity_bucket,'ADJ_CHRG',acctd_amt
449 ,'APP_CHRG',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_CHRG_ON_REV_LINE
450 ,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
451 ,'APP_FRT',DECODE(a.line_type,'LINE',amt,0) * -1,0))) FRT_ON_REV_LINE
452 ,SUM((DECODE(a.activity_bucket,'ADJ_FRT',amt
453 ,'APP_FRT',DECODE(a.line_type,'LINE',acctd_amt,0) * -1,0))) ACCTD_FRT_ON_REV_LINE
454 ,SUM((DECODE(a.activity_bucket,'ED_FRT',amt,0))) ED_FRT_REV_LINE
455 ,SUM((DECODE(a.activity_bucket,'ED_FRT',acctd_amt,0))) ACCTD_ED_FRT_REV_LINE
456 ,SUM((DECODE(a.activity_bucket,'UNED_FRT',amt,0))) UNED_FRT_REV_LINE
457 ,SUM((DECODE(a.activity_bucket,'UNED_FRT',acctd_amt,0))) ACCTD_UNED_FRT_REV_LINE
458 ,SUM((DECODE(a.activity_bucket,'ADJ_LINE',amt
459 ,'APP_LINE',(amt * -1)
460 ,'ED_LINE' ,amt
461 ,'UNED_LINE',amt -- line
462 ,'ADJ_TAX' ,amt
463 ,'APP_TAX' ,(amt * -1)
464 ,'ED_TAX' ,amt
465 ,'UNED_TAX',amt --tax
466 ,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',amt,0) * -1)
467 ,'APP_CHRG',(DECODE(a.line_type,'CHARGES',amt,0) * -1)
468 ,0))) REM_TYPE_LINE
469 ,SUM((DECODE(a.activity_bucket,'ADJ_LINE',acctd_amt
470 ,'APP_LINE',(acctd_amt * -1)
471 ,'ED_LINE' ,acctd_amt
472 ,'UNED_LINE',acctd_amt -- line
473 ,'ADJ_TAX' ,acctd_amt
474 ,'APP_TAX' ,(acctd_amt * -1)
475 ,'ED_TAX' ,acctd_amt
476 ,'UNED_TAX',acctd_amt --tax
477 ,'APP_FRT' ,(DECODE(a.line_type,'FREIGHT',acctd_amt,0) * -1)
478 ,'APP_CHRG',(DECODE(a.line_type,'CHARGES',acctd_amt,0) * -1)
479 ,0))) ACCTD_REM_TYPE_LINE
480 ,b.customer_trx_line_id CUSTOMER_TRX_LINE_ID,
481 ctl1.line_type line_type
482 FROM
483 (SELECT SUM( NVL(ard.amount_cr,0) - NVL(ard.amount_dr,0) ) amt,
484 SUM( NVL(ard.acctd_amount_cr,0) - NVL(ard.acctd_amount_dr,0)) acctd_amt,
485 ard.ref_customer_trx_line_id,
486 ard.ref_account_class,
487 ard.activity_bucket,
488 ctl.line_type
489 FROM ar_distributions ard,
490 ra_customer_trx_lines ctl
491 WHERE ctl.customer_trx_id = p_customer_trx_id
492 AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
493 AND (ctl.customer_trx_line_id = p_customer_trx_line_id
494 OR
495 (ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
496 AND ctl.line_type = 'TAX'
497 )
498 )
499 GROUP BY
500 ard.ref_customer_trx_line_id,
501 ard.ref_account_class,
502 ard.activity_bucket,
503 ctl.line_type) a,
504 (SELECT SUM(ctlgd.AMOUNT) sum_orig,
505 SUM(ctlgd.ACCTD_AMOUNT) sum_acctd_orig,
506 ctlgd.customer_trx_line_id
507 FROM ra_cust_trx_line_gl_dist ctlgd,
508 ra_customer_trx_lines ctll
509 WHERE ctlgd.customer_trx_id = p_customer_trx_id
510 AND ctlgd.customer_trx_line_id = ctll.customer_trx_line_id
511 AND (ctll.customer_trx_line_id = p_customer_trx_line_id
512 OR
513 (ctll.link_to_cust_trx_line_id = p_customer_trx_line_id
514 AND ctll.line_type = 'TAX'
515 )
516 )
517 GROUP BY ctlgd.customer_trx_line_id) b,
518 ra_customer_trx_lines ctl1
519 WHERE a.ref_customer_trx_line_id (+) = b.customer_trx_line_id
520 and b.customer_trx_line_id=ctl1.customer_trx_line_id
521 GROUP BY b.customer_trx_line_id,
522 b.sum_orig,
523 b.sum_acctd_orig, ctl1.line_type;
524
525 /* CURSOR cu_line_tax (p_customer_trx_id IN NUMBER, p_customer_trx_line_id IN NUMBER) IS
526 SELECT customer_trx_line_id
527 FROM ra_customer_trx_lines
528 WHERE customer_trx_line_id = p_customer_trx_id
529 AND (customer_trx_line_id = p_customer_trx_line_id
530 OR
531 (link_to_cust_trx_line_id = p_customer_trx_line_id
532 AND line_type = 'TAX'
533 )
534 );
535 */
536
537 l_result VARCHAR2(1);
538 l_term_ratio NUMBER;
539 l_line_original NUMBER;
540 l_sum_line_adj NUMBER;
541 l_line_credited NUMBER;
542 l_line_applied NUMBER;
543 l_line_tax VARCHAR2(10);
544 l_remain_amt NUMBER;
545 l_tax_code_source ar_receivables_trx.tax_code_source%TYPE; -- Bug 13882660
546
547
548 BEGIN
549
550 arp_util.debug('arp_process_adjustment.validate_inv_line_amount()+',
551 pg_msg_level_debug);
552
553 /*----------------------------------------------------------------+
554 | IF the line number is filled in |
555 | THEN validate adjustment is not more than available invoiced |
556 | line amount |
557 +----------------------------------------------------------------*/
558
559 IF ( p_adj_rec.customer_trx_line_id IS NOT NULL )
560 THEN
561
562 l_remain_amt := 0;
563 l_line_original:= 0;
564
565 IF(p_adj_rec.receivables_trx_id IS NOT NULL) THEN -- Bug 13882660
566 SELECT tax_code_source
567 INTO l_tax_code_source
568 FROM ar_receivables_trx
569 WHERE receivables_trx_id = p_adj_rec.receivables_trx_id;
570 END IF;
571
572 FOR i IN cu_rem_amt(p_adj_rec.customer_trx_id, p_adj_rec.customer_trx_line_id) LOOP
573 IF PG_DEBUG in ('Y', 'C') THEN
574 arp_util.debug('Value of l_remain_amt: ' || l_remain_amt);
575 arp_util.debug('Value of sum_orig: ' || i.sum_orig);
576 arp_util.debug('Value of REM_TYPE_LINE: ' || i.REM_TYPE_LINE);
577 arp_util.debug('Value of l_line_original: ' || l_line_original);
578 END IF;
579 IF(Nvl(l_tax_code_source,'NONE') = 'NONE') THEN -- Bug 13882660
580 IF(i.line_type <> 'TAX') THEN
581 l_remain_amt := l_remain_amt + i.sum_orig + i.REM_TYPE_LINE;
582 l_line_original := l_line_original + i.sum_orig;
583 END IF;
584 ELSE
585 l_remain_amt := l_remain_amt + i.sum_orig + i.REM_TYPE_LINE;
586 l_line_original := l_line_original + i.sum_orig;
587 END IF;
588 END LOOP;
589
590 IF PG_DEBUG in ('Y', 'C') THEN
591 arp_util.debug('Value of l_remain_amt: ' || l_remain_amt);
592 arp_util.debug('Value of l_line_original: ' || l_line_original);
593 END IF;
594
595 /*------------------------------------------+
596 | Get the amounts used to calculate the |
597 | available invoiced line amount. |
598 +------------------------------------------*/
599 /* 1909312
600 Terms will not be present for Credit Memos.
601 Added the following IF Condition and the ELSE clause */
602 /*
603 IF (p_ps_rec.Term_id IS NOT NULL) THEN
604 SELECT NVL( tl.relative_amount, 1) /
605 NVL( t.base_amount, 1),
606 arpcurr.CurrRound(
607 (
608 NVL( tl.relative_amount, 1) /
609 NVL( t.base_amount, 1)
610 ) *
611 ctl.extended_amount,
612 p_ps_rec.invoice_currency_code
613 )
614 INTO l_term_ratio,
615 l_line_original
616 FROM ra_terms_lines tl,
617 ra_terms t,
618 ra_customer_trx_lines ctl
619 WHERE p_ps_rec.term_id = t.term_id
620 AND t.term_id = tl.term_id
621 AND p_ps_rec.terms_sequence_number = tl.sequence_num
622 AND ctl.customer_trx_line_id =
623 p_adj_rec.customer_trx_line_id;
624 ELSE */ /* 1909312 Code Added begins */
625 /* SELECT ctl.extended_amount
626 INTO l_line_original
627 FROM ra_customer_trx_lines ctl
628 WHERE ctl.customer_trx_line_id =
629 p_adj_rec.customer_trx_line_id;
630 l_term_ratio := 1;
631 END IF; */ /* 1909312 Code Added Ends */
632
633 --Add for 13538911
634 /* SELECT NVL(SUM(ctl.extended_amount),0)
635 INTO l_line_tax
636 FROM ra_customer_trx_lines ctl
637 WHERE ctl.link_to_cust_trx_line_id=
638 p_adj_rec.customer_trx_line_id;
639
640 SELECT NVL(SUM(amount),0)
641 INTO l_sum_line_adj
642 FROM ar_adjustments
643 WHERE customer_trx_line_id = p_adj_rec.customer_trx_line_id
644 AND NVL(postable, 'Y') = 'Y'
645 AND customer_trx_id = p_adj_rec.customer_trx_id;
646
647 SELECT arpcurr.CurrRound(
648 NVL(
649 SUM( ctl.extended_amount *
650 l_term_ratio ),
651 0
652 ),
653 p_ps_rec.invoice_currency_code
654 )
655 INTO l_line_credited
656 FROM ra_customer_trx_lines ctl
657 WHERE ctl.previous_customer_trx_line_id =
658 p_adj_rec.customer_trx_line_id;
659 */
660 /*
661 This does not work
662 SELECT NVL(
663 SUM(ra.amount_applied )
664 , 0
665 )
666 INTO l_line_applied
667 FROM ar_receivable_applications ra
668 WHERE applied_payment_schedule_id = p_adj_rec.payment_schedule_id
669 AND applied_customer_trx_id = p_adj_rec.customer_trx_id;
670 */
671 /* SELECT NVL(
672 SUM(ra.amount_applied )
673 , 0
674 )
675 INTO l_line_applied
676 FROM ar_receivable_applications ra
677 WHERE
678 ra.applied_customer_trx_id = p_adj_rec.customer_trx_id
679 AND ra.applied_customer_trx_line_id= p_adj_rec.customer_trx_line_id;
680
681 arp_util.debug('Adj Amt: ' || p_adj_rec.amount ||
682 ' Line Orig: ' || l_line_original ||
683 ' Adj : ' || l_sum_line_adj ||' Cred: ' ||
684 l_line_credited || ' Appl: ' || l_line_applied ||
685 ' Net: ' || TO_CHAR(p_adj_rec.amount +
686 l_line_original +
687 l_sum_line_adj +
688 l_line_credited -
689 l_line_applied ) );
690 */
691 /*Bug 2248207: The procedure was initially checking for positive invoice amount and
692 Was rejecting the Adjustment if the amount exceeds the remaining amount.
693 Now this is checking for negative amount as well.
694 */
695 /*
696 IF (l_line_original > 0) THEN
697 IF (
698 p_adj_rec.amount +
699 l_line_original +
700 l_sum_line_adj +
701 l_line_tax +--Add for 13538911
702 l_line_credited -
703 l_line_applied < 0
704 )
705 THEN
706 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
707 'validate_inv_line_amount ()',
708 pg_msg_level_debug);
709 arp_util.debug( 'Adjustments cannot be more than available ' ||
710 'invoiced line amount.',
711 pg_msg_level_debug);
712 FND_MESSAGE.set_name('AR', 'AR_VAL_ADJ_INV_LINE_AMT');
713 APP_EXCEPTION.raise_exception;
714 END IF;
715 ELSIF (l_line_original < 0) THEN
716 IF (
717 p_adj_rec.amount +
718 l_line_original+
719 l_sum_line_adj +
720 l_line_tax +--Add for 13538911
721 l_line_credited -
722 l_line_applied > 0
723 )
724 THEN
725 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
726 'validate_inv_line_amount ()',
727 pg_msg_level_debug);
728 arp_util.debug( 'Adjustments cannot be more than available ' ||
729 'invoiced line amount.',
730 pg_msg_level_debug);
731 FND_MESSAGE.set_name('AR', 'AR_VAL_ADJ_INV_LINE_AMT');
732 APP_EXCEPTION.raise_exception;
733 END IF;
734 END IF; */
735
736 IF (l_line_original > 0) THEN
737 IF (
738 p_adj_rec.amount +
739 l_remain_amt < 0
740 )
741 THEN
742 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
743 'validate_inv_line_amount ()',
744 pg_msg_level_debug);
745 arp_util.debug( 'Adjustments cannot be more than available ' ||
746 'invoiced line amount.',
747 pg_msg_level_debug);
748 FND_MESSAGE.set_name('AR', 'AR_VAL_ADJ_INV_LINE_AMT');
749 APP_EXCEPTION.raise_exception;
750 END IF;
751 ELSIF (l_line_original < 0) THEN
752 IF (
753 p_adj_rec.amount +
754 l_remain_amt > 0
755 )
756 THEN
757 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
758 'validate_inv_line_amount ()',
759 pg_msg_level_debug);
760 arp_util.debug( 'Adjustments cannot be more than available ' ||
761 'invoiced line amount.',
762 pg_msg_level_debug);
763 FND_MESSAGE.set_name('AR', 'AR_VAL_ADJ_INV_LINE_AMT');
764 APP_EXCEPTION.raise_exception;
765 END IF;
766 END IF;
767
768 END IF; -- end not approved or adjusted and line specified case
769
770
771 arp_util.debug('arp_process_adjustment.validate_inv_line_amount()-',
772 pg_msg_level_debug);
773
774 EXCEPTION
775 WHEN OTHERS THEN
776 arp_util.debug('EXCEPTION: arp_process_adjustment.' ||
777 'validate_inv_line_amount()',
778 pg_msg_level_debug);
779
780 arp_util.debug('', pg_msg_level_debug);
781 arp_util.debug('---------- parameters for validate_inv_line_amount()' ||
782 ' ---------',
783 pg_msg_level_debug);
784
785 arp_adjustments_pkg.display_adj_rec( p_adj_rec );
786
787 RAISE;
788
789 END;
790
791 /*===========================================================================+
792 | PROCEDURE |
793 | validate_inv_line_amount_cover |
794 | |
795 | DESCRIPTION |
796 | validates that the adjustment is not for more than available invoiced |
797 | line amount. |
798 | |
799 | SCOPE - PRIVATE |
800 | |
801 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
802 | arp_util.debug |
803 | |
804 | ARGUMENTS : IN: |
805 | p_customer_trx_line_id |
806 | p_customer_trx_id |
807 | p_payment_schedule_id |
808 | p_amount |
809 | p_invoice_currency_code |
810 | p_term_id |
811 | p_terms_sequence_number |
812 | OUT: |
813 | IN/ OUT: |
814 | |
815 | NOTES |
816 | |
817 | MODIFICATION HISTORY |
818 | 25-JUN-96 Charlie Tomberg Created |
819 | |
820 +===========================================================================*/
821
822 PROCEDURE validate_inv_line_amount_cover(
823 p_customer_trx_line_id IN number,
824 p_customer_trx_id IN number,
825 p_payment_schedule_id IN number,
826 p_amount IN number,
827 p_receivables_trx_id IN NUMBER DEFAULT NULL) IS
828
829 l_adj_rec ar_adjustments%rowtype;
830 l_ps_rec ar_payment_schedules%rowtype;
831
832 BEGIN
833
834 arp_util.debug('arp_process_adjustment.validate_inv_line_amount_cover()+',
835 pg_msg_level_debug);
836
837 l_adj_rec.customer_trx_line_id := p_customer_trx_line_id;
838 l_adj_rec.customer_trx_id := p_customer_trx_id;
839 l_adj_rec.payment_schedule_id := p_payment_schedule_id;
840 l_adj_rec.amount := p_amount;
841 l_adj_rec.receivables_trx_id := p_receivables_trx_id; -- Bug 13882660
842
843 SELECT term_id,
844 terms_sequence_number,
845 invoice_currency_code
846 INTO l_ps_rec.term_id,
847 l_ps_rec.terms_sequence_number,
848 l_ps_rec.invoice_currency_code
849 FROM ar_payment_schedules
850 WHERE payment_schedule_id = p_payment_schedule_id;
851
852 validate_inv_line_amount( l_adj_rec, l_ps_rec );
853
854 arp_util.debug('arp_process_adjustment.validate_inv_line_amount_cover()-',
855 pg_msg_level_debug);
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 arp_util.debug('EXCEPTION: arp_process_adjustment.' ||
860 'validate_inv_line_amount_cover()',
861 pg_msg_level_debug);
862
863 arp_util.debug('', pg_msg_level_debug);
864 arp_util.debug('---------- parameters for ' ||
865 'validate_inv_line_amount_cover()' ||
866 ' ---------',
867 pg_msg_level_debug);
868
869 arp_util.debug('p_customer_trx_line_id = ' ||
870 TO_CHAR(p_customer_trx_line_id), pg_msg_level_debug);
871 arp_util.debug('p_customer_trx_id = ' ||
872 TO_CHAR(p_customer_trx_id), pg_msg_level_debug);
873 arp_util.debug('p_payment_schedule_id = ' ||
874 TO_CHAR(p_payment_schedule_id), pg_msg_level_debug);
875 arp_util.debug('p_amount = ' ||
876 TO_CHAR(p_amount), pg_msg_level_debug);
877
878 RAISE;
879
880 END;
881
882
883 /*===========================================================================+
884 | PROCEDURE |
885 | validate_update_approve_adj |
886 | |
887 | DESCRIPTION |
888 | Validates row that is going to be approved. |
889 | |
890 | SCOPE - PRIVATE |
891 | |
892 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
893 | arp_util.debug |
894 | |
895 | ARGUMENTS : IN: |
896 | p_adj_rec |
897 | p_ps_rec |
898 | p_adjustment_code |
899 | p_chk_approval_limits |
900 | OUT: |
901 | IN/ OUT: |
902 | |
903 | NOTES |
904 | |
905 | MODIFICATION HISTORY |
906 | 08-SEP-95 Charlie Tomberg Created |
907 | |
908 | 03-FEB-00 Saloni Shah Made changes data model changes to |
909 | AR_APPROVAL_USER_LIMITS. |
910 | |
911 | 03-FEB-00 Saloni Shah Made changes for the BR/BOE project |
912 | A new IN parameter p_chk_approval_limits |
913 | was added. |
914 | |
915 +===========================================================================*/
916
917 PROCEDURE validate_update_approve_adj( p_adj_rec IN ar_adjustments%rowtype,
918 p_ps_rec IN ar_payment_schedules%rowtype,
919 p_adjustment_code IN ar_lookups.lookup_code%type,
920 p_chk_approval_limits IN varchar2
921 ) IS
922
923 l_varchar_dummy VARCHAR2(128);
924 l_date_dummy DATE;
925 l_number_dummy NUMBER;
926 l_closing_status gl_period_statuses.closing_status%type;
927 l_result VARCHAR2(1);
928 l_approval_amount_to ar_approval_user_limits.amount_to%type;
929 l_approval_amount_from ar_approval_user_limits.amount_from%type;
930
931 BEGIN
932
933 arp_util.debug('arp_process_adjustment.validate_update_approve_adj()+',
934 pg_msg_level_debug);
935
936
937 IF ( p_adjustment_code = 'A' )
938 THEN
939
940 /*-------------------------------------------------------------------+
941 | validate that GL Date is in an open or future enterable period |
942 +-------------------------------------------------------------------*/
943
944 arp_standard.gl_period_info( p_adj_rec.gl_date,
945 l_varchar_dummy,
946 l_date_dummy,
947 l_date_dummy,
948 l_closing_status,
949 l_varchar_dummy,
950 l_number_dummy,
951 l_number_dummy,
952 l_number_dummy );
953
954 IF ( l_closing_status not in ('O', 'F' ) )
955 THEN
956
957 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
958 'validate_update_approve_adj ()',
959 pg_msg_level_debug);
960 arp_util.debug( 'Invalid date. Enter a GL date in an open or' ||
961 ' future enterable period.',
962 pg_msg_level_debug);
963 FND_MESSAGE.set_name('AR', 'AR_VAL_GL_DATE_OPEN');
964 APP_EXCEPTION.raise_exception;
965 END IF;
966
967 /*------------------------------------------------------------------+
968 | validate that GL date is not be prior to the invoice's GL date |
969 +------------------------------------------------------------------*/
970
971 IF ( p_adj_rec.gl_date < p_ps_rec.gl_date )
972 THEN
973
974 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
975 'validate_update_approve_adj ()',
976 pg_msg_level_debug);
977 arp_util.debug( 'The GL date should not be prior to the ' ||
978 'invoice''s GL date.',
979 pg_msg_level_debug);
980 FND_MESSAGE.set_name('AR', 'AR_VAL_GL_INV_GL');
981 APP_EXCEPTION.raise_exception;
982
983 END IF;
984
985 /*------------------------------------------------------------+
986 | validate that user has approval limits for the currency |
987 | |
988 | Change made for BR/BOE project |
989 | The adjusted amount is validated against the user approval|
990 | limits only if the p_chk_approval_limits has value 'T' |
991 +------------------------------------------------------------*/
992
993 IF (p_chk_approval_limits = FND_API.G_TRUE ) THEN
994 BEGIN
995 SELECT aul.amount_to,
996 aul.amount_from
997 INTO l_approval_amount_to,
998 l_approval_amount_from
999 FROM ar_approval_user_limits aul
1000 WHERE aul.user_id = arp_adjustments_pkg.pg_user_id
1001 AND aul.currency_code = p_ps_rec.invoice_currency_code
1002 /* Bug 941429: Credit memo workflow added a new document_type column
1003 to AR_APPROVAL_USER_LIMITS. Now user_id and currency_code alone can't
1004 uniquely identify a row. Need to include document_type as well */
1005 AND aul.document_type = 'ADJ';
1006
1007 EXCEPTION
1008 WHEN NO_DATA_FOUND THEN
1009 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
1010 'validate_update_approve_adj ()',
1011 pg_msg_level_debug);
1012 arp_util.debug( 'You do not have approval limits for currency ' ||
1013 p_ps_rec.invoice_currency_code,
1014 pg_msg_level_debug);
1015 FND_MESSAGE.set_name('AR', 'AR_VAL_USER_LIMIT');
1016 FND_MESSAGE.set_token( 'CURRENCY',
1017 p_ps_rec.invoice_currency_code);
1018 APP_EXCEPTION.raise_exception;
1019
1020 WHEN OTHERS THEN RAISE;
1021 END;
1022
1023 IF (
1024 ( p_adj_rec.amount > l_approval_amount_to )
1025 OR
1026 ( p_adj_rec.amount < l_approval_amount_from )
1027 )
1028 THEN
1029
1030 arp_util.debug( 'EXCEPTION: arp_process_adjustment.' ||
1031 'validate_update_approve_adj ()',
1032 pg_msg_level_debug);
1033 arp_util.debug( 'User ID: ' || arp_adjustments_pkg.pg_user_id ||
1034 ' Amount: ' ||
1035 p_adj_rec.amount || ' From: ' ||
1036 l_approval_amount_from || ' To: ' ||
1037 l_approval_amount_to,
1038 pg_msg_level_debug);
1039 arp_util.debug( 'Amount exceeded approval limit.',
1040 pg_msg_level_debug);
1041 FND_MESSAGE.set_name('AR', 'AR_VAL_AMT_APPROVAL_LIMIT');
1042 APP_EXCEPTION.raise_exception;
1043
1044 END IF;
1045
1046 END IF;
1047
1048 validate_inv_line_amount( p_adj_rec,
1049 p_ps_rec );
1050
1051 END IF; -- end approved case
1052
1053
1054 arp_util.debug('arp_process_adjustment.validate_update_approve_adj()-',
1055 pg_msg_level_debug);
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 arp_util.debug('EXCEPTION: arp_process_adjustment.' ||
1060 'validate_update_approve_adj()',
1061 pg_msg_level_debug);
1062
1063 arp_util.debug('', pg_msg_level_debug);
1064 arp_util.debug('---------- parameters for validate_update_approve_adj()'
1065 || ' ---------',
1066 pg_msg_level_debug);
1067
1068 arp_util.debug('p_adjustment_code = ' || p_adjustment_code );
1069 arp_adjustments_pkg.display_adj_rec( p_adj_rec );
1070
1071 RAISE;
1072
1073 END;
1074
1075 /*===========================================================================+
1076 | PROCEDURE |
1077 | insert_adjustment |
1078 | |
1079 | DESCRIPTION |
1080 | Inserts a record into ar_adjustments |
1081 | |
1082 | SCOPE - PUBLIC |
1083 | |
1084 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1085 | arp_util.debug |
1086 | |
1087 | ARGUMENTS : IN: |
1088 | p_form_name |
1089 | p_form_version |
1090 | p_check_amount |
1091 | OUT: |
1092 | p_adjustment_number |
1093 | p_adjustment_id |
1094 | IN/ OUT: |
1095 | p_adj_rec |
1096 | |
1097 | RETURNS : NONE |
1098 | |
1099 | NOTES |
1100 | |
1101 | MODIFICATION HISTORY |
1102 | 24-AUG-95 Martin Johnson Created |
1103 | 4/17/1996 Harri Kaukovuo Added special handling for |
1104 | chargebacks. |
1105 | 9/17/1996 Harri Kaukovuo Bug fix 394553. |
1106 | |
1107 | 03-FEB-00 Saloni Shah Made changes for the BR/BOE project. |
1108 | When adjustment is reversed, then the|
1109 | validation on the amounts is not done|
1110 | 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR.
1111 | The new parameter is used to detect
1112 | whether the deferred tax is moved as
1113 | part of maturity_date event or as a
1114 | part of activity on the BR(Bug 1290698)
1115 | 13-Jun-00 Satheesh Nambiar Bug 1329091 - Passing one more |
1116 | parameter to accounting engine |
1117 | 25-Aug-00 SNAMBIAR Bug 1395396
1118 | Modified the code accept $0 adjustment
1119 | 25-Aug-00 SNAMBIAR Added a new parameter p_called_from
1120 | for BR to pass to Accounting engine.
1121 | Added a new parameter old_adjustment_id
1122 | for calling Accounting engine in REVERSE
1123 | mode.(Bug 1415964)
1124 | 31-Jan-01 SNAMBIAR Bug 1620930 - Modified for commitment
1125 | adjustment
1126 | 07-Mar-01 YREDDY Bug 1686556: Modified to have the
1127 | correct account in the distributions
1128 | 11-JUL-02 HYU Bug 2365805: Manual charge using "Finance Charge"
1129 | is incorrect.
1130 | 09-AUG-05 MRAYMOND 4544013 - Implemented etax calls for
1131 | adjustment API and forms
1132 +===========================================================================*/
1133
1134 PROCEDURE insert_adjustment(p_form_name IN varchar2,
1135 p_form_version IN number,
1136 p_adj_rec IN OUT
1137 ar_adjustments%rowtype,
1138 p_adjustment_number OUT NOCOPY
1139 ar_adjustments.adjustment_number%type,
1140 p_adjustment_id OUT NOCOPY
1141 ar_adjustments.adjustment_id%type,
1142 p_check_amount IN varchar2 := FND_API.G_TRUE,
1143 p_move_deferred_tax IN varchar2 := 'Y',
1144 p_called_from IN varchar2 DEFAULT NULL,
1145 p_old_adjust_id IN ar_adjustments.adjustment_id%type DEFAULT NULL,
1146 p_override_flag IN varchar2 DEFAULT NULL,
1147 p_app_level IN VARCHAR2 DEFAULT 'TRANSACTION')
1148
1149
1150 IS
1151
1152 l_adjustment_id ar_adjustments.adjustment_id%type;
1153 l_ps_rec ar_payment_schedules%rowtype;
1154 l_acctd_amount ar_adjustments.acctd_amount%type;
1155 l_amount_adjusted ar_payment_schedules.amount_adjusted%type;
1156 l_aah_rec ar_approval_action_history%rowtype;
1157 l_approval_action_history_id
1158 ar_approval_action_history.approval_action_history_id%type;
1159 ln_adr_tmp NUMBER;
1160 ln_acctd_adr_tmp NUMBER;
1161 /* VAT changes */
1162 l_ae_doc_rec ae_doc_rec_type;
1163
1164 l_adj_type ar_adjustments.type%type;
1165 l_accounting_affect_flag ar_receivables_trx.accounting_affect_flag%type;
1166
1167 l_app_ps_status VARCHAR2(10);
1168 --BUG#2750340
1169 l_xla_ev_rec arp_xla_events.xla_events_type;
1170
1171 /* 4544013 */
1172 l_gt_id NUMBER := 0;
1173 l_gt_id_temp NUMBER := 0;
1174 l_line_amt NUMBER;
1175 l_tax_amt NUMBER;
1176 l_from_llca_call VARCHAR2(1) := 'N';
1177 l_mode VARCHAR2(20);
1178 -- Line level Adjustment
1179 l_line_adjusted NUMBER;
1180 l_tax_adjusted NUMBER;
1181 l_line_id NUMBER;
1182 v_cont NUMBER;
1183 v_claim_amt varchar2(100);
1184
1185 l_customer_trx ra_customer_trx%ROWTYPE;
1186 l_gl_account_source ar_receivables_trx.gl_account_source%type;
1187 l_tax_code_source ar_receivables_trx.tax_code_source%type;
1188 BEGIN
1189
1190 arp_util.debug('ar_process_adjustment.insert_adjustment()+');
1191
1192 p_adjustment_number := NULL;
1193 p_adjustment_id := NULL;
1194
1195 -- check form version to determine if it is compatible with the
1196 -- entity handler.
1197 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
1198
1199 -- Lock rows in other tables that reference this customer_trx_id
1200 arp_trx_util.lock_transaction(p_adj_rec.customer_trx_id);
1201
1202 /*-----------------------------------+
1203 | Get the payment schedule record |
1204 +-----------------------------------*/
1205
1206 arp_ps_pkg.fetch_p(p_adj_rec.payment_schedule_id, l_ps_rec);
1207
1208 --apandit
1209 l_app_ps_status := l_ps_rec.status;
1210
1211 /*--------------------+
1212 | pre-insert logic |
1213 +--------------------*/
1214
1215 arp_util.debug( 'p_app_level = ' || p_app_level);
1216 arp_util.debug( 'p_type = ' || p_adj_rec.type);
1217 arp_util.debug('adj amount = ' || p_adj_rec.amount);
1218
1219 /*----------------------------------------------------+
1220 | BOE change |
1221 | For a reverse adjustment the validation on insert |
1222 | for the amounts is not done. |
1223 | The reversal of an adjustment is indicated by |
1224 | p_check_amount flag set to 'F' |
1225 +----------------------------------------------------*/
1226
1227 select nvl(gl_account_source,'NONE'),
1228 nvl(tax_code_source,'NONE')
1229 into l_gl_account_source,
1230 l_tax_code_source
1231 from ar_receivables_trx
1232 where receivables_trx_id = p_adj_rec.receivables_trx_id;
1233
1234 IF ((l_gl_account_source = 'REVENUE_ON_INVOICE')
1235 AND (((nvl(p_adj_rec.line_adjusted,0) +
1236 nvl(p_adj_rec.freight_adjusted,0) +
1237 nvl(p_adj_rec.receivables_charges_adjusted,0)) <> 0)
1238 OR ((l_tax_code_source = 'NONE')
1239 AND (nvl(p_adj_rec.tax_adjusted,0) <> 0))
1240 ))
1241 THEN
1242 IF (p_check_amount = FND_API.G_TRUE) THEN
1243 validate_insert_adjustment( p_adj_rec.amount,
1244 p_adj_rec.payment_schedule_id,
1245 p_adj_rec.type );
1246 END IF;
1247 END IF;
1248
1249 BEGIN
1250
1251 SELECT ps.amount_due_remaining-ofc.amount+p_adj_rec.amount,ofc.amount into v_cont,v_claim_amt ---Modified for the bug 14500887
1252 FROM ra_customer_trx rct,
1253 ar_payment_schedules ps,
1254 ar_receivable_applications ra,
1255 ozf_claims ofc
1256 WHERE rct.customer_trx_id=ps.customer_trx_id
1257 AND ra.applied_customer_trx_id=rct.customer_trx_id
1258 AND ofc.source_object_id = rct.customer_trx_id
1259 AND rct.customer_trx_id = p_adj_rec.customer_trx_id
1260 AND application_ref_type='CLAIM'
1261 AND application_ref_num is not null
1262 AND ps.active_claim_flag='Y';
1263
1264 EXCEPTION
1265 when others then
1266 v_cont := 0;
1267 END;
1268
1269 IF (p_adj_rec.type <> 'CB' and p_form_name = 'ARXTWADJ' and v_cont < 0) THEN ---of amount of adjustment amt of document
1270
1271 fnd_message.set_name('AR', 'AR_CLAIM_DEDUC_RED');
1272 fnd_message.set_token('CLAIM_AMOUNT',v_claim_amt);
1273 app_exception.raise_exception;
1274
1275 END IF;
1276
1277 IF p_adj_rec.status = 'A'
1278 THEN
1279 -- ------------------------------------------------------------------
1280 -- This is to make arp_ps_util.update_adj_related_columns work OK
1281 -- CB means that we are adjusting chargeback amount to applied
1282 -- transaction.
1283 -- This does not work the same way as normal invoice adjustment
1284 -- because normal invoice adjustment assumes that the whole
1285 -- full amount of amount due remaining is adjusted.
1286 -- Chargeback can be done to be less or equal to amount due remaining.
1287 -- ------------------------------------------------------------------
1288
1289 IF (p_adj_rec.type = 'CB')
1290 THEN
1291 /* VAT changes */
1292 arp_ps_util.update_adj_related_columns(
1293 null,
1294 p_adj_rec.type,
1295 p_adj_rec.amount,
1296 null,
1297 p_adj_rec.line_adjusted,
1298 p_adj_rec.tax_adjusted,
1299 p_adj_rec.freight_adjusted,
1300 p_adj_rec.receivables_charges_adjusted,
1301 p_adj_rec.apply_date,
1302 p_adj_rec.gl_date,
1303 l_acctd_amount,
1304 l_ps_rec);
1305
1306 -- ----------------------------------------------------------------
1307 -- Change this back to INVOICE for standard way of treating this
1308 -- adjustment.
1309 -- ----------------------------------------------------------------
1310 l_ae_doc_rec.other_flag := 'CHARGEBACK';
1311 l_ae_doc_rec.source_id_old := p_adj_rec.code_combination_id;
1312 p_adj_rec.type := 'INVOICE';
1313
1314 ELSE
1315 arp_util.debug( 'before update_adj_related_adjustment');
1316 arp_util.debug( 'line adjusted = ' || p_adj_rec.line_adjusted);
1317 arp_util.debug( 'tax adjusted = ' || p_adj_rec.tax_adjusted);
1318 arp_util.debug( 'freight adjusted = ' || p_adj_rec.freight_adjusted);
1319
1320 /*-------------------------------------------------------------+
1321 | If the flag p_check_amount has the value of 'F' ie it is |
1322 | an adjustment reversal, then adjustment_type is set to |
1323 | 'REVERSE' so that the values for line_adjusted, tax_adjusted|
1324 | freight_adjusted and amount_adjusted are not calculated in |
1325 | arp_ps_util.update_adj_related_columns procedure. |
1326 +-------------------------------------------------------------*/
1327 /*-------------------------------------------------------------+
1328 | Bug 1290698 - For partial adjustment, p_check_amount is 'F'.|
1329 | So set the type = 'REVERSE' only when it is actual reversal |
1330 +-------------------------------------------------------------*/
1331 --Modified to call Accounting Engine in reverse mode while
1332 --creating reverse adjustment with old_adjustment_id
1333
1334 IF (p_check_amount = FND_API.G_FALSE)
1335 and p_adj_rec.created_from = 'REVERSE_ADJUSTMENT' THEN
1336 l_adj_type := 'REVERSE';
1337 l_ae_doc_rec.source_id_old := p_old_adjust_id;
1338 l_ae_doc_rec.other_flag := 'REVERSE';
1339
1340 ELSE
1341 l_adj_type := p_adj_rec.type;
1342 END IF;
1343
1344 --Bug 1395396.Update PS record only if Amount is not 0
1345 IF p_adj_rec.amount <> 0 THEN
1346
1347 IF l_adj_type <> 'REVERSE' THEN
1348 arp_ps_util.update_adj_related_columns(
1349 null,
1350 l_adj_type,
1351 p_adj_rec.amount,
1352 null,
1353 p_adj_rec.line_adjusted,
1354 p_adj_rec.tax_adjusted,
1355 p_adj_rec.freight_adjusted,
1356 p_adj_rec.receivables_charges_adjusted,
1357 p_adj_rec.apply_date,
1358 p_adj_rec.gl_date,
1359 l_acctd_amount,
1360 l_ps_rec);
1361 ELSE
1362 --Bug 1415964
1363 --Do not recalculate the acctd amount while reversing
1364 --Take the amounts from old_adjustment and reverse it
1365
1366 l_amount_adjusted := NVL(p_adj_rec.line_adjusted, 0 ) +
1367 NVL(p_adj_rec.tax_adjusted, 0 ) +
1368 NVL(p_adj_rec.freight_adjusted, 0 ) +
1369 NVL(p_adj_rec.receivables_charges_adjusted, 0 );
1370
1371 --Assign the amounts from old adjustment record
1372
1373 l_ps_rec.amount_due_remaining :=
1374 l_ps_rec.amount_due_remaining +
1375 nvl(l_amount_adjusted,0);
1376 l_ps_rec.acctd_amount_due_remaining :=
1377 l_ps_rec.acctd_amount_due_remaining +
1378 p_adj_rec.acctd_amount;
1379 l_acctd_amount := p_adj_rec.acctd_amount;
1380
1381
1382 -- Add amount adjusted to current amount_adjusted and subtract
1383 -- adjusted amounts from amounts remaining
1384
1385 l_ps_rec.amount_adjusted :=
1386 nvl(l_ps_rec.amount_adjusted, 0) +
1387 l_amount_adjusted;
1388
1389 IF ( p_adj_rec.line_adjusted IS NOT NULL ) THEN
1390 l_ps_rec.amount_line_items_remaining :=
1391 NVL(l_ps_rec.amount_line_items_remaining, 0 ) +
1392 p_adj_rec.line_adjusted;
1393
1394 END IF;
1395
1396 IF (p_adj_rec.receivables_charges_adjusted IS NOT NULL) THEN
1397 l_ps_rec.receivables_charges_remaining :=
1398 NVL(l_ps_rec.receivables_charges_remaining, 0 ) +
1399 p_adj_rec.receivables_charges_adjusted;
1400
1401 END IF;
1402
1403 IF ( p_adj_rec.tax_adjusted IS NOT NULL ) THEN
1404 l_ps_rec.tax_remaining :=
1405 NVL( l_ps_rec.tax_remaining, 0 ) +
1406 p_adj_rec.tax_adjusted;
1407
1408 END IF;
1409
1410 IF ( p_adj_rec.freight_adjusted IS NOT NULL ) THEN
1411 l_ps_rec.freight_remaining :=
1412 NVL( l_ps_rec.freight_remaining, 0 ) +
1413 p_adj_rec.freight_adjusted;
1414
1415 END IF;
1416
1417 arp_ps_util.populate_closed_dates(p_adj_rec.gl_date,
1418 p_adj_rec.apply_date, 'ADJ', l_ps_rec );
1419 arp_ps_pkg.update_p(l_ps_rec );
1420
1421 END IF; -- Close for Reverse block
1422
1423 END IF;
1424 END IF;
1425
1426 ELSE
1427 --update ar_payment_schedules.amount_adjusted_pending
1428 --Bug 1395396.Update PS record only if Amount is not 0
1429
1430 IF p_adj_rec.amount <> 0 THEN
1431
1432 /*3869570 Replaced p_adj_rec.apply_date and
1433 p_adj_rec.gl_Date with l_ps_rec.actual_date_closed and
1434 l_ps_rec.gl_date_closed*/
1435 arp_ps_util.update_adj_related_columns(
1436 null,
1437 null,
1438 null,
1439 p_adj_rec.amount,
1440 p_adj_rec.line_adjusted,
1441 p_adj_rec.tax_adjusted,
1442 p_adj_rec.freight_adjusted,
1443 p_adj_rec.receivables_charges_adjusted,
1444 l_ps_rec.actual_date_closed,
1445 l_ps_rec.gl_date_closed,
1446 l_acctd_amount,
1447 l_ps_rec);
1448
1449 -- We store ADR (amount due remaining) values to temporary
1450 -- variables, because we do not update transaction payment
1451 -- schedule when adjustment is not approved.
1452
1453 arp_util.calc_acctd_amount(
1454 NULL
1455 , NULL
1456 , NULL
1457 , NVL(l_ps_rec.exchange_rate,1) -- Exchange rate
1458 , '+' -- amount_applied must be added to ADR
1459 , l_ps_rec.amount_due_remaining -- Current ADR
1460 , l_ps_rec.acctd_amount_due_remaining -- Current Acctd. ADR
1461 , p_adj_rec.amount -- Amount adjusted
1462 , ln_adr_tmp -- New ADR (OUT)
1463 , ln_acctd_adr_tmp -- New Acctd. ADR (OUT)
1464 , l_acctd_amount); -- Acct. amount adjusted
1465 -- (OUT)
1466 END IF;
1467 END IF;
1468
1469 p_adj_rec.acctd_amount := l_acctd_amount;
1470
1471 --Bug 1620930 Add the folowing conditions for commitment adjustment
1472 IF (p_adj_rec.receivables_trx_id = -1) THEN
1473 p_adj_rec.adjustment_type := 'C';
1474 END IF;
1475
1476 -- if a line level adjustment, then we want to tag the created_From
1477 -- for later use.
1478
1479 IF (p_app_level = 'LINE') THEN
1480 p_adj_rec.created_from := 'ARXRWLLC';
1481 END IF;
1482
1483 /*----------------------+
1484 | call table-handler |
1485 +----------------------*/
1486 arp_adjustments_pkg.insert_p(p_adj_rec,
1487 l_ps_rec.exchange_rate,
1488 p_adjustment_number,
1489 l_adjustment_id);
1490
1491 /* 4544013 - Call etax routine to prorate line and
1492 tax for recoverable tax transactions. Note that
1493 this routine will only change the line_adjusted
1494 and tax_adjusted columns. It will not affect
1495 the overall adj amount or trx balance.
1496
1497 Passing a 'Y' for p_upd_adj_and_ps causes
1498 the proration code to update the adjustment
1499 and target payment schedule with the new
1500 prorated amounts (overriding what was
1501 passed in or written in the original PS insert */
1502
1503 --================== For LLCA adjustment, inserting into Activity Details Table =================
1504 IF p_app_level = 'LINE'
1505 THEN
1506
1507 SELECT ar_activity_details_s.nextval
1508 INTO l_line_id
1509 FROM dual;
1510
1511 SELECT
1512 LINE_ADJUSTED,
1513 TAX_ADJUSTED
1514 INTO
1515 l_line_adjusted,
1516 l_tax_adjusted
1517 FROM ar_adjustments
1518 WHERE adjustment_id = l_adjustment_id;
1519
1520 INSERT INTO AR_ACTIVITY_DETAILS (
1521 LINE_ID,
1522 APPLY_TO,
1523 customer_trx_line_id,
1524 CASH_RECEIPT_ID,
1525 GROUP_ID,
1526 AMOUNT,
1527 TAX,
1528 CREATED_BY,
1529 CREATION_DATE,
1530 LAST_UPDATE_LOGIN,
1531 LAST_UPDATE_DATE,
1532 LAST_UPDATED_BY,
1533 OBJECT_VERSION_NUMBER,
1534 CREATED_BY_MODULE,
1535 SOURCE_ID,
1536 SOURCE_TABLE,
1537 CURRENT_ACTIVITY_FLAG
1538 )
1539
1540 VALUES (
1541 l_line_id, -- line_id
1542 1, -- APPLY_TO
1543 p_adj_rec.customer_trx_line_id, -- customer_Trx_line_id
1544 NULL, -- cash_Receipt_id
1545 NULL, -- Group_ID (ll grp adj not implem)
1546 l_line_adjusted, -- Amount
1547 l_tax_adjusted, -- TAX
1548 NVL(FND_GLOBAL.user_id,-1), -- Created_by
1549 SYSDATE, -- Creation_date
1550 decode(FND_GLOBAL.conc_login_id,
1551 null,FND_GLOBAL.login_id,
1552 -1, FND_GLOBAL.login_id,
1553 FND_GLOBAL.conc_login_id), -- Last_update_login
1554 SYSDATE, -- Last_update_date
1555 NVL(FND_GLOBAL.user_id,-1), -- last_updated_by
1556 0, -- object_version_number
1557 'ARXTWADJ', -- created_by_module
1558 l_adjustment_id, -- source_id
1559 'ADJ', -- source_table
1560 'Y' -- Application record status
1561 );
1562
1563 END IF;
1564
1565 --================== For LLCA adjustment, inserting into Activity Details Table =================
1566
1567 IF p_adj_rec.type in ('INVOICE','LINE','TAX','CHARGES') AND
1568 p_adj_rec.status = 'A'
1569 THEN
1570
1571 /* Set mode */
1572 IF p_adj_rec.type = 'INVOICE'
1573 THEN
1574 l_mode := 'INV';
1575 ELSIF p_adj_rec.type = 'CHARGES'
1576 THEN
1577 l_mode := 'LINE';
1578 ELSE
1579 l_mode := p_adj_rec.type;
1580 END IF;
1581
1582 arp_util.debug(' cust trx line id = ' || p_adj_rec.customer_trx_line_id);
1583
1584 IF (p_app_level = 'LINE') THEN
1585 arp_etax_util.prorate_recoverable(
1586 p_adj_id => l_adjustment_id,
1587 p_target_id => p_adj_rec.customer_trx_id,
1588 p_target_line_id => p_adj_rec.customer_trx_line_id,
1589 p_amount => p_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
1590 p_apply_date => p_adj_rec.apply_date,
1591 p_mode => l_mode,
1592 p_upd_adj_and_ps => 'Y',
1593 p_gt_id => l_gt_id,
1594 p_prorated_line => l_line_amt,
1595 p_prorated_tax => l_tax_amt);
1596
1597 ELSE
1598 arp_etax_util.prorate_recoverable(
1599 p_adj_id => l_adjustment_id,
1600 p_target_id => p_adj_rec.customer_trx_id,
1601 p_target_line_id => NULL,
1602 p_amount => p_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
1603 p_apply_date => p_adj_rec.apply_date,
1604 p_mode => l_mode,
1605 p_upd_adj_and_ps => 'Y',
1606 p_gt_id => l_gt_id,
1607 p_prorated_line => l_line_amt,
1608 p_prorated_tax => l_tax_amt);
1609 END IF;
1610
1611 /* If the rec_activity is not recoverable, this routine
1612 just returns as-is. Since we requested that the
1613 routine update the adj and ps rows, the returned
1614 prorated amounts can be ignored from this point
1615 on. */
1616
1617 /* display results in debug log */
1618 arp_util.debug('After return from arp_etax_util.prorate_recoverable');
1619 arp_util.debug(' l_gt_id = ' || l_gt_id);
1620 arp_util.debug(' l_line_amt = ' || l_line_amt);
1621 arp_util.debug(' l_tax_amt = ' || l_tax_amt);
1622
1623 IF NVL(l_gt_id,0) <> 0
1624 THEN
1625 l_from_llca_call := 'Y';
1626 ELSE
1627 l_from_llca_call := 'N';
1628 l_gt_id := NULL;
1629 END IF;
1630
1631 END IF;
1632
1633 /*Moved the query for getting the accounting flag here, as we need accounting flag to decide whether to call create events or not.
1634 Refer Bug7299812 for details. - vavenugo */
1635
1636 /*--------------------------------------------+
1637 | Get the value for accounting_affect_flag |
1638 +--------------------------------------------*/
1639
1640 SELECT NVL(accounting_affect_flag, 'Y')
1641 INTO l_accounting_affect_flag
1642 FROM ar_receivables_trx
1643 WHERE receivables_trx_id = p_adj_rec.receivables_trx_id;
1644
1645
1646 --BUG#2750340
1647 /*------------------------------------------+
1648 | Need to call XLA engine to create the |
1649 | ADJ_CREATE event because it can be not |
1650 | approved in which case no accounting are |
1651 | created. |
1652 +------------------------------------------*/
1653 l_xla_ev_rec.xla_from_doc_id := l_adjustment_id;
1654 l_xla_ev_rec.xla_to_doc_id := l_adjustment_id;
1655 l_xla_ev_rec.xla_doc_table := 'ADJ';
1656 l_xla_ev_rec.xla_mode := 'O';
1657 l_xla_ev_rec.xla_call := 'B';
1658
1659
1660 IF (l_accounting_affect_flag <> 'N') THEN
1661 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
1662 END IF;
1663
1664 /*End Bug7299812 */
1665
1666 p_adjustment_id := l_adjustment_id;
1667
1668 --apandit
1669 IF l_app_ps_status <> l_ps_rec.status THEN
1670 l_app_ps_status := l_ps_rec.status;
1671 ELSE
1672 l_app_ps_status := 'NO_CHANGE';
1673 END IF;
1674 --Bug 2641517 raise business event
1675 AR_BUS_EVENT_COVER.Raise_Adj_Create_Event(l_adjustment_id,
1676 l_app_ps_status,
1677 p_adj_rec.status );
1678
1679
1680 /*-----------------------------------------------------------------------+
1681 | VAT changes: create acct entry |
1682 | Bug 916659: Create accounting only if adjustment is approved |
1683 | Change made for BR/BOE project. Accounting is created only if the |
1684 | accounting_affect_flag for the receivable_trx_id is not 'N' |
1685 +-----------------------------------------------------------------------*/
1686
1687 IF (p_adj_rec.status = 'A' and l_accounting_affect_flag <> 'N')
1688 THEN
1689
1690 --{BUG 2365805:
1691 -- old code :l_ae_doc_rec.document_type := 'ADJUSTMENT';
1692 IF p_adj_rec.Type = 'CHARGES' THEN
1693 l_ae_doc_rec.document_type := 'FINANCE_CHARGES';
1694 ELSE
1695 l_ae_doc_rec.document_type := 'ADJUSTMENT';
1696 END IF;
1697 --}
1698 l_ae_doc_rec.document_id := l_adjustment_id;
1699 l_ae_doc_rec.accounting_entity_level := 'ONE';
1700 l_ae_doc_rec.source_table := 'ADJ';
1701 l_ae_doc_rec.source_id := l_adjustment_id;
1702 l_ae_doc_rec.deferred_tax := p_move_deferred_tax;
1703
1704 --Bug 1329091 - PS is updated before Accounting Engine Call
1705
1706 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
1707
1708 --Added a new parameter p_called_from for BR
1709
1710 l_ae_doc_rec.event := p_called_from;
1711
1712 /* Bug 1686556: The changed adjustment account is now reflected
1713 in the distributions also */
1714
1715
1716 IF Nvl(p_override_flag,'N') = 'Y' and
1717 p_adj_rec.code_combination_id is NOT NULL
1718 THEN
1719 l_ae_doc_rec.other_flag := 'OVERRIDE';
1720 l_ae_doc_rec.source_id_old := p_adj_rec.code_combination_id;
1721 END IF;
1722
1723
1724 --Bug 1620930 Add the folowing conditions for commitment adjustment
1725 IF (p_adj_rec.receivables_trx_id = -1) THEN
1726 l_ae_doc_rec.other_flag := 'COMMITMENT';
1727 l_ae_doc_rec.source_id_old := p_adj_rec.code_combination_id;
1728 END IF;
1729
1730 IF (l_from_llca_call = 'N' and p_app_level = 'LINE') THEN
1731 -- we have line level app with non-recoverable tax
1732 -- we need to populate the gt table before calling the
1733 -- the accting engine.
1734
1735 arp_llca_adjust_pkg.LLCA_Adjustments(
1736 p_customer_trx_line_id => p_adj_rec.customer_trx_line_id,
1737 p_customer_trx_id => p_adj_rec.customer_trx_id,
1738 p_line_adjusted => p_adj_rec.line_adjusted,
1739 p_tax_adjusted => p_adj_rec.tax_adjusted,
1740 p_adj_id => l_adjustment_id,
1741 p_inv_currency_code => l_ps_rec.invoice_currency_code,
1742 p_gt_id => l_gt_id_temp );
1743
1744 l_gt_id := l_gt_id_temp;
1745 l_from_llca_call := 'Y';
1746
1747 END IF;
1748
1749 arp_util.debug('Before Calling arp_acct_main.Create_Acct_Entry');
1750 arp_util.debug('l_gt_id = ' || l_gt_id);
1751 arp_util.debug('l_from_llca_call = '||l_from_llca_call);
1752
1753 arp_acct_main.Create_Acct_Entry(p_ae_doc_rec => l_ae_doc_rec,
1754 p_from_llca_call => l_from_llca_call,
1755 p_gt_id => l_gt_id);
1756 END IF;
1757
1758 /*---------------------+
1759 | post-insert logic |
1760 +---------------------*/
1761 begin
1762 select * into l_customer_trx from ra_customer_trx
1763 where customer_trx_id= p_adj_rec.customer_trx_id;
1764 exception
1765 when others then
1766 null;
1767 end;
1768 IF p_adj_rec.customer_trx_line_id is not null or l_customer_trx.upgrade_method = 'R12' then
1769 ARP_DET_DIST_PKG.re_calcul_rem_amt(l_customer_trx);
1770 END IF;
1771
1772 IF p_adj_rec.status <> 'A'
1773 THEN
1774 -- insert row into ar_approval_action_history
1775
1776 l_aah_rec.action_name := p_adj_rec.status;
1777 l_aah_rec.adjustment_id := l_adjustment_id;
1778 l_aah_rec.action_date := trunc(sysdate);
1779 l_aah_rec.comments := p_adj_rec.comments;
1780
1781 arp_aah_pkg.insert_p(l_aah_rec,
1782 l_approval_action_history_id);
1783
1784 END IF;
1785
1786 arp_util.debug('ar_process_adjustment.insert_adjustment()-');
1787
1788 EXCEPTION
1789 WHEN OTHERS THEN
1790 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
1791 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.insert_adjustment exception: '||SQLERRM );
1792 arp_util.debug(
1793 'EXCEPTION: ar_process_adjustment.insert_adjustment()');
1794 RAISE;
1795
1796 END;
1797
1798
1799 /*===========================================================================+
1800 | PROCEDURE |
1801 | update_adjustment |
1802 | |
1803 | DESCRIPTION |
1804 | Updates a record in ar_adjustments |
1805 | |
1806 | SCOPE - PUBLIC |
1807 | |
1808 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1809 | arp_util.debug |
1810 | |
1811 | ARGUMENTS : IN: |
1812 | OUT: |
1813 | IN/ OUT: |
1814 | |
1815 | RETURNS : NONE |
1816 | |
1817 | NOTES |
1818 | Adjustment amount cannot be updated in Rel 10. This procedure |
1819 | assumes that adjustment amount will never be updated. |
1820 | |
1821 | MODIFICATION HISTORY |
1822 | 06-SEP-95 Martin Johnson Created |
1823 | 26-MAR-96 Martin Johnson BugNo:352255. Fixed so that |
1824 | l_old_adj_rec is always fetched. |
1825 | 9/18/1996 Harri Kaukovuo Fixed the procedure to recalculate |
1826 | accounted adjust amount when adjustment|
1827 | is approved. Bug fix 403019. |
1828 | 03-FEB-00 Saloni Shah Changes made for the BR/BOE project. |
1829 | The accounting enteries will be created|
1830 | only if the status is 'A' and the |
1831 | accounting_affect_flag for the |
1832 | receivables_Trx_id is not set to 'N' |
1833 | 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR. |
1834 | The new parameter is used to detect |
1835 | whether the deferred tax is moved as |
1836 | part of maturity_date event or as a |
1837 | part of activity on the BR(Bug 1290698)|
1838 | 13-Jun-00 Satheesh Nambiar Bug 1329091- Passing one more parameter|
1839 | to accounting engine to acknowledge PS |
1840 | updated. |
1841 | 05-Jun-02 Rahna Kader Bug 2377672: While updating an |
1842 | adjustment reversal, the accounting |
1843 | entries should not be re-created |
1844 +===========================================================================*/
1845
1846 PROCEDURE update_adjustment(
1847 p_form_name IN varchar2,
1848 p_form_version IN varchar2,
1849 p_adj_rec IN ar_adjustments%rowtype,
1850 p_move_deferred_tax IN varchar2 := 'Y',
1851 p_adjustment_id IN ar_adjustments.adjustment_id%type)
1852
1853 IS
1854
1855 l_adj_rec ar_adjustments%rowtype;
1856 l_aah_rec ar_approval_action_history%rowtype;
1857 l_ps_rec ar_payment_schedules%rowtype;
1858 l_approval_action_history_id
1859 ar_approval_action_history.approval_action_history_id%type;
1860 l_status_changed_flag boolean;
1861 l_old_adj_rec ar_adjustments%rowtype;
1862 l_acctd_amount_adjusted ar_adjustments.acctd_amount%type;
1863 l_ae_doc_rec ae_doc_rec_type;
1864 l_accounting_affect_flag ar_receivables_trx.accounting_affect_flag%type;
1865 /* Bug fix 2377672
1866 variables to decide whether the accounting needs to be re-created */
1867 l_recreate_accounting boolean;
1868 l_accounts number;
1869 --apandit
1870 l_app_ps_status VARCHAR2(20);
1871 l_gt_id_temp NUMBER := 0;
1872
1873 l_amount_adjusted_pending NUMBER; /*3590046 */
1874
1875 --BUG#2750340
1876 l_xla_ev_rec arp_xla_events.xla_events_type;
1877
1878 /* 6888581 */
1879 l_event_source_info xla_events_pub_pkg.t_event_source_info;
1880 l_event_id NUMBER;
1881 l_security xla_events_pub_pkg.t_security;
1882 l_adj_post_to_gl ra_cust_trx_types.adj_post_to_gl%TYPE := 'Y' ;
1883 /* Added local variables to be used in Update_Adjustment procedure, manishri, Bug 9708599 */
1884 l_mode VARCHAR2(20);
1885 l_gt_id NUMBER := 0;
1886 l_line_amt NUMBER;
1887 l_tax_amt NUMBER;
1888 l_from_llca_call VARCHAR2(1) := 'N';
1889 /* End of change, manishri, Bug 9708599 */
1890 l_line_adjusted NUMBER;
1891 l_tax_adjusted NUMBER;
1892
1893 BEGIN
1894 arp_util.debug('ar_process_adjustment.update_adjustment()+',
1895 pg_msg_level_debug);
1896
1897 /*----------------------------------------------------------------+
1898 | check form version to determine if it is compatible with the |
1899 | entity handler. |
1900 +----------------------------------------------------------------*/
1901
1902 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
1903
1904
1905 /*-------------------------------------------------------------------+
1906 | If the adjustment record parameter does not have all the columns |
1907 | filled in, the procedure will not work. In this case, |
1908 | fetch the adjustment record from the database and construct a |
1909 | new record that consists of the unchanged columns from the old |
1910 | records and the changed columns from the record passed in as a |
1911 | parameter. |
1912 +-------------------------------------------------------------------*/
1913
1914 arp_adjustments_pkg.fetch_p( l_old_adj_rec,
1915 p_adjustment_id );
1916
1917 IF (p_adj_rec.type = arp_adjustments_pkg.get_text_dummy )
1918 THEN
1919
1920 arp_adjustments_pkg.merge_adj_recs( l_old_adj_rec,
1921 p_adj_rec,
1922 l_adj_rec );
1923 ELSE
1924 l_adj_rec := p_adj_rec;
1925 END IF;
1926
1927
1928 /*-----------------------------------------------------------------+
1929 | Lock rows in other tables that reference this customer_trx_id |
1930 +-----------------------------------------------------------------*/
1931
1932 arp_trx_util.lock_transaction(l_adj_rec.customer_trx_id);
1933
1934 /*-----------------------------------+
1935 | Get the payment schedule record |
1936 +-----------------------------------*/
1937
1938 arp_ps_pkg.fetch_p(l_adj_rec.payment_schedule_id, l_ps_rec);
1939 --apandit
1940 l_app_ps_status := l_ps_rec.status;
1941
1942 /*--------------------+
1943 | pre-update logic |
1944 +--------------------*/
1945
1946 set_flags(p_adjustment_id,
1947 l_old_adj_rec,
1948 l_adj_rec,
1949 l_status_changed_flag);
1950
1951 validate_update_adjustment(l_adj_rec.payment_schedule_id,
1952 l_adj_rec.amount,
1953 l_adj_rec.type,
1954 l_status_changed_flag,
1955 l_adj_rec.status,
1956 l_adj_rec.tax_adjusted );
1957
1958 /* Added cal to Validate_Update_Approve_Adj while approving Adjustment, manishri, Bug 9708599 */
1959 IF (l_adj_rec.status IN ('A', 'R') and l_old_adj_rec.status = 'W') THEN
1960 validate_update_approve_adj( p_adj_rec => l_adj_rec,
1961 p_ps_rec => l_ps_rec,
1962 p_adjustment_code => l_adj_rec.status,
1963 p_chk_approval_limits => FND_API.G_TRUE);
1964 END IF;
1965 /* End of change, manishri, Bug 9708599 */
1966
1967 arp_util.debug(
1968 'l_status_changed_flag: ' ||
1969 arp_trx_util.boolean_to_varchar2(l_status_changed_flag) );
1970
1971 IF l_status_changed_flag
1972 THEN
1973 IF l_adj_rec.status = 'A'
1974 THEN
1975 arp_ps_util.update_adj_related_columns(
1976 null, -- payment_schedule_id
1977 l_adj_rec.type, -- p_type
1978 l_adj_rec.amount, -- p_amount_adjusted
1979 l_adj_rec.amount * -1, -- p_amount_adjusted_pending
1980 l_adj_rec.line_adjusted, -- p_line_adjusted
1981 l_adj_rec.tax_adjusted, -- p_tax_adjusted
1982 l_adj_rec.freight_adjusted, -- p_freight_adjusted
1983 l_adj_rec.receivables_charges_adjusted,
1984 l_adj_rec.apply_date, -- p_apply_date
1985 l_adj_rec.gl_date, -- p_gl_date
1986 l_acctd_amount_adjusted, -- p_acctd_amount_adjusted
1987 l_ps_rec ); -- p_ps_rec
1988
1989 -- Bug fix 403019, to avoid rounding errors.
1990 l_adj_rec.acctd_amount := l_acctd_amount_adjusted;
1991
1992 END IF; /* IF l_adj_rec.status = 'A */
1993
1994 -- Bug 568533: need to update payment schedule to remove
1995 -- adjusted amount pending if Adjustment is rejected.
1996
1997 IF ( l_adj_rec.status = 'R' )
1998 THEN
1999
2000 /*3869570 Replaced p_adj_rec.apply_date and
2001 p_adj_rec.gl_Date with l_ps_rec.actual_date_closed and
2002 l_ps_rec.gl_date_closed*/
2003
2004 arp_ps_util.update_adj_related_columns(
2005 null, -- paymenty schedule id
2006 l_adj_rec.type, -- p_type
2007 null, -- p_amount_adjusted
2008 -1 * l_adj_rec.amount,-- p_amount_adjusted_pending
2009 l_adj_rec.line_adjusted,
2010 l_adj_rec.tax_adjusted,
2011 l_adj_rec.freight_adjusted,
2012 l_adj_rec.receivables_charges_adjusted,
2013 l_ps_rec.actual_date_closed,
2014 l_ps_rec.gl_date_closed,
2015 l_acctd_amount_adjusted,
2016 l_ps_rec );
2017
2018 END IF;
2019
2020 END IF; /* IF l_status_changed_flag */
2021
2022 /*----------------------+
2023 | call table-handler |
2024 +----------------------*/
2025
2026 arp_adjustments_pkg.update_p(l_adj_rec,
2027 p_adjustment_id,
2028 l_ps_rec.exchange_rate);
2029
2030 --Bug 12959780.
2031 IF l_adj_rec.created_from = 'ARXRWLLC' THEN
2032 SELECT
2033 LINE_ADJUSTED,
2034 TAX_ADJUSTED
2035 INTO
2036 l_line_adjusted,
2037 l_tax_adjusted
2038 FROM ar_adjustments
2039 WHERE adjustment_id = p_adjustment_id;
2040
2041 UPDATE AR_ACTIVITY_DETAILS
2042 SET AMOUNT = l_line_adjusted,
2043 TAX = l_tax_adjusted
2044 WHERE SOURCE_TABLE = 'ADJ'
2045 AND SOURCE_ID = p_adjustment_id
2046 AND customer_trx_line_id = l_adj_rec.customer_trx_line_id;
2047
2048 END IF;
2049
2050 --Bug 12959780.
2051 --Bug 1277494 Added NVL to selection which was missing
2052 BEGIN
2053 SELECT NVL(accounting_affect_flag,'Y')
2054 INTO l_accounting_affect_flag
2055 FROM ar_receivables_trx
2056 WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
2057
2058 EXCEPTION
2059 WHEN OTHERS THEN
2060 l_accounting_affect_flag := 'Y';
2061 END;
2062
2063 /* Added to execute tax calculation, manishri, Bug 9708599 */
2064 IF (l_adj_rec.status = 'A' and l_old_adj_rec.status = 'W') THEN
2065
2066 IF l_adj_rec.type in ('INVOICE','LINE','TAX','CHARGES') THEN
2067
2068 /* Set mode */
2069 IF l_adj_rec.type = 'INVOICE' THEN
2070 l_mode := 'INV';
2071 ELSIF l_adj_rec.type = 'CHARGES' THEN
2072 l_mode := 'LINE';
2073 ELSE
2074 l_mode := l_adj_rec.type;
2075 END IF;
2076
2077 -- Added for Line Level Adjustment
2078 IF l_adj_rec.created_from = 'ARXRWLLC' THEN
2079
2080 arp_etax_util.prorate_recoverable(
2081 p_adj_id => p_adjustment_id,
2082 p_target_id => l_adj_rec.customer_trx_id,
2083 p_target_line_id => l_adj_rec.customer_trx_line_id,
2084 p_amount => l_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
2085 p_apply_date => l_adj_rec.apply_date,
2086 p_mode => l_mode,
2087 p_upd_adj_and_ps => NULL, -- no maint reqd
2088 p_gt_id => l_gt_id,
2089 p_prorated_line => l_line_amt,
2090 p_prorated_tax => l_tax_amt);
2091 ELSE
2092
2093 arp_etax_util.prorate_recoverable(
2094 p_adj_id => p_adjustment_id,
2095 p_target_id => l_adj_rec.customer_trx_id,
2096 p_target_line_id => NULL,
2097 p_amount => l_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
2098 p_apply_date => l_adj_rec.apply_date,
2099 p_mode => l_mode,
2100 p_upd_adj_and_ps => NULL, -- no maint reqd
2101 p_gt_id => l_gt_id,
2102 p_prorated_line => l_line_amt,
2103 p_prorated_tax => l_tax_amt);
2104 END IF;
2105
2106 /* If the rec_activity is not recoverable, this routine
2107 just returns as-is. Since we requested that the
2108 routine update the adj and ps rows, the returned
2109 prorated amounts can be ignored from this point
2110 on. */
2111
2112 /* display results in debug log */
2113 arp_util.debug('After return from arp_etax_util.prorate_recoverable');
2114 arp_util.debug(' l_gt_id = ' || l_gt_id);
2115 arp_util.debug(' l_line_amt = ' || l_line_amt);
2116 arp_util.debug(' l_tax_amt = ' || l_tax_amt);
2117
2118 IF l_gt_id <> 0 THEN
2119 l_from_llca_call := 'Y';
2120
2121 /* Set adj line and tax amounts before call to
2122 update PS */
2123 l_adj_rec.line_adjusted := l_line_amt;
2124 l_adj_rec.tax_adjusted := l_tax_amt;
2125 ELSE
2126 l_from_llca_call := 'N';
2127 END IF;
2128
2129 --Bug 12959780.
2130 IF l_adj_rec.status = 'A' and l_accounting_affect_flag <> 'N'
2131 AND l_adj_rec.created_from = 'ARXRWLLC' AND l_from_llca_call = 'N' THEN
2132 arp_llca_adjust_pkg.LLCA_Adjustments(
2133 p_customer_trx_line_id => l_adj_rec.customer_trx_line_id,
2134 p_customer_trx_id => l_adj_rec.customer_trx_id,
2135 p_line_adjusted => l_adj_rec.line_adjusted,
2136 p_tax_adjusted => l_adj_rec.tax_adjusted,
2137 p_adj_id => p_adjustment_id,
2138 p_inv_currency_code => l_ps_rec.invoice_currency_code,
2139 p_gt_id => l_gt_id_temp );
2140
2141 l_gt_id := l_gt_id_temp;
2142 l_from_llca_call := 'Y';
2143
2144 End If;
2145
2146 END IF;
2147
2148 END IF;
2149 /* End of change, manishri, Bug 9708599 */
2150
2151
2152 /*3321021*/
2153 /*Gives provision to modify the amount for statuses other than
2154 Approved/Waiting */
2155 /*The user can completely change the amount .. hence the amount
2156 pending to be adjusted needs to be re-calculated*/
2157 BEGIN
2158 l_amount_adjusted_pending:=0;
2159 Select SUM(AMOUNT) into l_amount_adjusted_pending
2160 FROM ar_adjustments where payment_schedule_id=l_adj_rec.payment_schedule_id
2161 AND STATUS NOT IN ('A','R','U');
2162 UPDATE ar_payment_schedules set amount_adjusted_pending=
2163 DECODE(l_amount_adjusted_pending,0,NULL,l_amount_adjusted_pending)
2164 WHERE payment_schedule_id=l_adj_rec.payment_schedule_id;
2165 EXCEPTION
2166 WHEN OTHERS THEN
2167 IF PG_DEBUG in ('Y', 'C') THEN
2168 arp_util.debug('Problem in Amount adjusted pending calculation ' ||
2169 'EXCEPTION: arp_ps_util.update_adj_related_columns' );
2170 END IF;
2171 RAISE;
2172 END;
2173
2174 /* Bug 7621813: Get Adjustment Post to GL flag */
2175 BEGIN
2176
2177 Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
2178 into l_adj_post_to_gl
2179 from ra_customer_trx ct, ra_cust_trx_types ctt
2180 where ct.customer_trx_id = l_adj_rec.customer_trx_id
2181 and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
2182
2183 IF PG_DEBUG in ('Y', 'C') THEN
2184 arp_util.debug('l_adj_post_to_gl : '|| l_adj_post_to_gl);
2185 END IF;
2186
2187 EXCEPTION
2188 WHEN OTHERS THEN
2189
2190 IF PG_DEBUG in ('Y', 'C') THEN
2191 arp_util.debug('Unable to get post to gl flag for adjustment' );
2192 arp_util.debug('EXCEPTION: arp_ps_util.update_adjustment '|| SQLERRM);
2193 END IF;
2194 RAISE;
2195 END;
2196
2197 IF NVL(l_adj_post_to_gl, 'N') = 'Y' THEN
2198 /* 6888581 */
2199 IF ( l_adj_rec.status = 'R' ) THEN
2200
2201 BEGIN
2202
2203 select /*+ INDEX(xet xla_transaction_entities_N1) */
2204 xet.legal_entity_id legal_entity_id,
2205 adj.SET_OF_BOOKS_ID set_of_books_id,
2206 adj.org_id org_id,
2207 adj.event_id event_id,
2208 xet.entity_code entity_code,
2209 adj.adjustment_id adjustment_id,
2210 xet.application_id
2211 into
2212 l_event_source_info.legal_entity_id,
2213 l_event_source_info.ledger_id,
2214 l_security.security_id_int_1,
2215 l_event_id ,
2216 l_event_source_info.entity_type_code,
2217 l_event_source_info.source_id_int_1,
2218 l_event_source_info.application_id
2219 from
2220 ar_adjustments adj ,
2221 xla_transaction_entities_upg xet
2222 where adj.adjustment_id = p_adjustment_id
2223 and adj.adjustment_id = NVL(xet.source_id_int_1, -99)
2224 and xet.entity_code ='ADJUSTMENTS'
2225 AND xet.application_id = 222
2226 AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
2227
2228 xla_events_pub_pkg.update_event
2229 (p_event_source_info => l_event_source_info,
2230 p_event_id => l_event_id,
2231 p_event_status_code => 'N',
2232 p_valuation_method => null,
2233 p_security_context => l_security);
2234 EXCEPTION
2235 WHEN OTHERS THEN
2236 IF PG_DEBUG in ('Y', 'C') THEN
2237 arp_util.debug('Unable to get the XLA Entites Data ' ||
2238 'EXCEPTION: arp_ps_util.update_adjustment' );
2239 END IF;
2240 RAISE;
2241 END;
2242
2243 ELSE
2244
2245 --BUG#2750340
2246 /*----------------------------------------------+
2247 | Need to call AR XLA engine for ADJ modified |
2248 | not approved without distributions. |
2249 +----------------------------------------------*/
2250 l_xla_ev_rec.xla_from_doc_id := p_adjustment_id;
2251 l_xla_ev_rec.xla_to_doc_id := p_adjustment_id;
2252 l_xla_ev_rec.xla_doc_table := 'ADJ';
2253 l_xla_ev_rec.xla_mode := 'O';
2254 l_xla_ev_rec.xla_call := 'B';
2255 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
2256
2257 END IF;
2258 END IF;
2259 /*-------------------------------------------------------------------+
2260 | VAT changes: update the accounting by first deleting the old one |
2261 | and then creating a new one. |
2262 | Change for the BR/BOE project has been made. |
2263 | Accounting is created only if the status is 'A' and the |
2264 | accounting_affect_flag of the receivables_Trx is not set to 'N' |
2265 +-------------------------------------------------------------------*/
2266
2267 /*--------------------------------------------+
2268 | Change made for BR/BOE project. |
2269 | Get the value for accounting_affect_flag |
2270 +--------------------------------------------*/
2271 /* Moved this code on top. Bug 12959780.
2272 --Bug 1277494 Added NVL to selection which was missing
2273 BEGIN
2274 SELECT NVL(accounting_affect_flag,'Y')
2275 INTO l_accounting_affect_flag
2276 FROM ar_receivables_trx
2277 WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
2278
2279 EXCEPTION
2280 WHEN OTHERS THEN
2281 l_accounting_affect_flag := 'Y';
2282 END;
2283 */
2284 /* Fix for bug 2377672
2285 If the updated record is an adjustment reversal, the accounting
2286 entries should not be changed */
2287 select count(*)
2288 into l_accounts
2289 from ar_distributions
2290 where source_id = p_adjustment_id
2291 and source_table = 'ADJ';
2292 IF l_adj_rec.receivables_trx_id = -13 AND l_accounts > 0 THEN
2293 l_recreate_accounting := FALSE;
2294 ELSE
2295 l_recreate_accounting := TRUE;
2296 END IF;
2297
2298 IF (l_adj_rec.status = 'A' and l_accounting_affect_flag <> 'N'
2299 and l_recreate_accounting) THEN
2300 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2301 l_ae_doc_rec.document_id := p_adjustment_id;
2302 l_ae_doc_rec.accounting_entity_level := 'ONE';
2303 l_ae_doc_rec.source_table := 'ADJ';
2304 l_ae_doc_rec.source_id := p_adjustment_id;
2305 l_ae_doc_rec.deferred_tax := p_move_deferred_tax;
2306 /* Bug 916659: For a pending adjustment, there is no accounting,
2307 so no need to delete */
2308
2309 --Bug 1329091 - PS is updated before Accounting engine call
2310 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2311
2312 /*-------------------------------------------------------------------+
2313 | Call the accounting engine in delete mode for unposted adjustments|
2314 | This is necessary as the parent adjustment has changed so a fresh |
2315 | call is given to the accounting engine to re-create the accounting|
2316 +-------------------------------------------------------------------*/
2317 /*bug2636927*/
2318 IF ( l_old_adj_rec.status NOT IN ('M', 'W')
2319 and l_adj_rec.posting_control_id =-3
2320 and l_accounts <> 0 --Bug 3483238
2321 )
2322 THEN
2323 arp_acct_main.Delete_Acct_Entry(l_ae_doc_rec);
2324 END IF; --Bug 1787087
2325
2326 /*------------------------------------------------------------------+
2327 | Bug 1787087 : When an adjustment is approved, the newly created |
2328 | distributions should always reflect the account from adj record |
2329 | and not the defaulted account only if they are not the same. |
2330 +------------------------------------------------------------------*/
2331 arp_standard.debug('l_old_adj_rec.code_combination_id ' || l_old_adj_rec.code_combination_id);
2332 arp_standard.debug('l_adj_rec.code_combination_id ' || l_adj_rec.code_combination_id);
2333
2334 IF l_adj_rec.code_combination_id IS NOT NULL THEN
2335 l_ae_doc_rec.source_id_old := l_adj_rec.code_combination_id;
2336 l_ae_doc_rec.other_flag := 'OVERRIDE';
2337 END IF;
2338
2339 --Bug 12959780.
2340 arp_acct_main.Create_Acct_Entry(p_ae_doc_rec => l_ae_doc_rec,
2341 p_from_llca_call => l_from_llca_call,
2342 p_gt_id => l_gt_id);
2343
2344
2345 END IF;
2346
2347 /*---------------------+
2348 | post-update logic |
2349 +---------------------*/
2350
2351 IF l_status_changed_flag
2352 THEN
2353 -- insert row into ar_approval_action_history
2354
2355 l_aah_rec.action_name := l_adj_rec.status;
2356 l_aah_rec.adjustment_id := p_adjustment_id;
2357 l_aah_rec.action_date := trunc(sysdate);
2358 l_aah_rec.comments := l_adj_rec.comments;
2359
2360 arp_aah_pkg.insert_p(
2361 l_aah_rec,
2362 l_approval_action_history_id);
2363
2364
2365 -- Status changed
2366 IF ( l_adj_rec.status = 'A' AND
2367 l_adj_rec.type in ('TAX', 'LINE', 'CHARGES' ) AND -- Approved Tax Adjustment?
2368 /* VAT changes */
2369 nvl(l_adj_rec.tax_adjusted,0) <> 0)
2370 THEN
2371 /* 4544013 - removed call to sync_vendor_f_ct_adj_id. */
2372 NULL;
2373 END IF;
2374
2375 arp_standard.debug('before call to the business events');
2376
2377 IF l_app_ps_status <> l_ps_rec.status THEN
2378 l_app_ps_status := l_ps_rec.status;
2379 ELSE
2380 l_app_ps_status := 'NO_CHANGE';
2381 END IF;
2382 --apandit
2383 --Bug 2641517 raise business event for approval
2384 AR_BUS_EVENT_COVER.Raise_Adj_Approve_Event(p_adjustment_id,
2385 l_approval_action_history_id,
2386 l_app_ps_status);
2387 END IF;
2388
2389 arp_util.debug('ar_process_adjustment.update_adjustment()-',
2390 pg_msg_level_debug);
2391
2392
2393 EXCEPTION
2394 WHEN OTHERS THEN
2395 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
2396 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_adjustment exception: '||SQLERRM );
2397 arp_util.debug(
2398 'EXCEPTION: ar_process_adjustment.update_adjustment()',
2399 pg_msg_level_debug);
2400 RAISE;
2401
2402 END;
2403
2404
2405 /*===========================================================================+
2406 | PROCEDURE |
2407 | update_approve_adj |
2408 | |
2409 | DESCRIPTION |
2410 | Deletes a record from ar_adjustments |
2411 | |
2412 | SCOPE - PUBLIC |
2413 | |
2414 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2415 | arp_util.debug |
2416 | |
2417 | ARGUMENTS : IN: |
2418 | p_form_name |
2419 | p_form_version |
2420 | p_adj_rec |
2421 | p_adjustment_code |
2422 | p_adjustment_id |
2423 | OUT: |
2424 | None |
2425 | IN/ OUT: |
2426 | None |
2427 | |
2428 | |
2429 | NOTES |
2430 | |
2431 | MODIFICATION HISTORY |
2432 | 05-SEP-95 Charlie Tomberg Created |
2433 | 03-FEB-00 Saloni Shah Changes for the BR/BOE project is made|
2434 | A new p_chk_approval_limits parameter |
2435 | is added. |
2436 | 17-May-00 Satheesh Nambiar Added p_move_deferred_tax for BOE/BR. |
2437 | The new parameter is used to detect |
2438 | whether the deferred tax is moved as |
2439 | part of maturity_date event or as a |
2440 | part of activity on the BR(Bug 1290698)|
2441 | 13-Jun-00 Satheesh Nambiar Bug 1329091- Passing one more parameter|
2442 | to accounting engine to acknowledge PS |
2443 | updated. |
2444 | |
2445 +===========================================================================*/
2446
2447 PROCEDURE update_approve_adj(p_form_name IN varchar2,
2448 p_form_version IN number,
2449 p_adj_rec IN ar_adjustments%rowtype,
2450 p_adjustment_code ar_lookups.lookup_code%type,
2451 p_adjustment_id IN ar_adjustments.adjustment_id%type,
2452 p_chk_approval_limits IN varchar2,
2453 p_move_deferred_tax IN varchar2 := 'Y') IS
2454
2455 l_ps_rec ar_payment_schedules%rowtype;
2456 l_adj_rec ar_adjustments%rowtype;
2457 l_aah_rec ar_approval_action_history%rowtype;
2458 l_acctd_amount_adjusted ar_adjustments.acctd_amount%type;
2459
2460 l_approval_action_history_id
2461 ar_approval_action_history.approval_action_history_id%type;
2462 l_old_adj_rec ar_adjustments%rowtype;
2463 l_ae_doc_rec ae_doc_rec_type;
2464 l_accounting_affect_flag ar_receivables_trx.accounting_affect_flag%type;
2465 --BUG#2750340
2466 l_xla_ev_rec arp_xla_events.xla_events_type;
2467
2468 /* 4544013 */
2469 l_gt_id NUMBER := 0;
2470 l_line_amt NUMBER;
2471 l_tax_amt NUMBER;
2472 l_from_llca_call VARCHAR2(1) := 'N';
2473 l_mode VARCHAR2(20);
2474
2475 l_gt_id_temp NUMBER := 0;
2476 BEGIN
2477
2478 arp_util.debug('ar_process_adjustment.update_approve_adj()+',
2479 pg_msg_level_debug);
2480
2481
2482 /*-----------------------------------------------------------------+
2483 | check form version to determine if it is compatible with the |
2484 | entity handler. |
2485 +-----------------------------------------------------------------*/
2486
2487 arp_trx_validate.ar_entity_version_check(p_form_name, p_form_version);
2488
2489
2490 /*-------------------------------------------------------------------+
2491 | If the adjustment record parameter does not have all the columns |
2492 | filled in, the procedure will not work. In this case, |
2493 | fetch the adjustment record from the database and construct a |
2494 | new record that consists of the unchanged columns from the old |
2495 | records and the changed columns from the record passed in as a |
2496 | parameter. |
2497 +-------------------------------------------------------------------*/
2498
2499 IF (p_adj_rec.type = arp_adjustments_pkg.get_text_dummy )
2500 THEN
2501 arp_adjustments_pkg.fetch_p( l_old_adj_rec,
2502 p_adjustment_id );
2503
2504 arp_adjustments_pkg.merge_adj_recs( l_old_adj_rec,
2505 p_adj_rec,
2506 l_adj_rec );
2507 ELSE
2508 l_adj_rec := p_adj_rec;
2509 END IF;
2510
2511 /*-----------------------------------------------------------------+
2512 | Lock rows in other tables that reference this customer_trx_id |
2513 +-----------------------------------------------------------------*/
2514
2515 arp_trx_util.lock_transaction(l_adj_rec.customer_trx_id);
2516
2517 arp_ps_pkg.fetch_p( l_adj_rec.payment_schedule_id,
2518 l_ps_rec );
2519
2520 validate_update_approve_adj( l_adj_rec,
2521 l_ps_rec,
2522 p_adjustment_code,
2523 p_chk_approval_limits );
2524
2525
2526 /* 4544013 - Call etax routine to prorate line and
2527 tax for recoverable tax transactions. Note that
2528 this routine will only change the line_adjusted
2529 and tax_adjusted columns. It will not affect
2530 the overall adj amount or trx balance.
2531
2532 */
2533
2534 IF p_adj_rec.type in ('INVOICE','LINE','TAX','CHARGES') AND
2535 p_adjustment_code in ('A','R')
2536 THEN
2537
2538 /* Set mode */
2539 IF p_adj_rec.type = 'INVOICE'
2540 THEN
2541 l_mode := 'INV';
2542 ELSIF p_adj_rec.type = 'CHARGES'
2543 THEN
2544 l_mode := 'LINE';
2545 ELSE
2546 l_mode := p_adj_rec.type;
2547 END IF;
2548
2549 -- Added for Line Level Adjustment
2550 IF p_adj_rec.created_from = 'ARXRWLLC'
2551 THEN
2552
2553 arp_etax_util.prorate_recoverable(
2554 p_adj_id => p_adjustment_id,
2555 p_target_id => p_adj_rec.customer_trx_id,
2556 p_target_line_id => p_adj_rec.customer_trx_line_id,
2557 p_amount => p_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
2558 p_apply_date => p_adj_rec.apply_date,
2559 p_mode => l_mode,
2560 p_upd_adj_and_ps => NULL, -- no maint reqd
2561 p_gt_id => l_gt_id,
2562 p_prorated_line => l_line_amt,
2563 p_prorated_tax => l_tax_amt);
2564 ELSE
2565
2566 arp_etax_util.prorate_recoverable(
2567 p_adj_id => p_adjustment_id,
2568 p_target_id => p_adj_rec.customer_trx_id,
2569 p_target_line_id => NULL,
2570 p_amount => p_adj_rec.amount - nvl(p_adj_rec.freight_adjusted,0) - nvl(p_adj_rec.receivables_charges_adjusted,0),
2571 p_apply_date => p_adj_rec.apply_date,
2572 p_mode => l_mode,
2573 p_upd_adj_and_ps => NULL, -- no maint reqd
2574 p_gt_id => l_gt_id,
2575 p_prorated_line => l_line_amt,
2576 p_prorated_tax => l_tax_amt);
2577 END IF;
2578
2579 /* If the rec_activity is not recoverable, this routine
2580 just returns as-is. Since we requested that the
2581 routine update the adj and ps rows, the returned
2582 prorated amounts can be ignored from this point
2583 on. */
2584
2585 /* display results in debug log */
2586 arp_util.debug('After return from arp_etax_util.prorate_recoverable');
2587 arp_util.debug(' l_gt_id = ' || l_gt_id);
2588 arp_util.debug(' l_line_amt = ' || l_line_amt);
2589 arp_util.debug(' l_tax_amt = ' || l_tax_amt);
2590
2591 IF l_gt_id <> 0
2592 THEN
2593 l_from_llca_call := 'Y';
2594
2595 /* Set adj line and tax amounts before call to
2596 update PS */
2597 l_adj_rec.line_adjusted := l_line_amt;
2598 l_adj_rec.tax_adjusted := l_tax_amt;
2599 ELSE
2600 l_from_llca_call := 'N';
2601 END IF;
2602
2603 END IF;
2604
2605 /*---------------------------------+
2606 | update ar_payment_schedules |
2607 +---------------------------------*/
2608
2609 IF ( p_adjustment_code = 'A' )
2610 THEN
2611
2612 arp_ps_util.update_adj_related_columns(
2613 null,
2614 l_adj_rec.type,
2615 l_adj_rec.amount,
2616 l_adj_rec.amount * -1,
2617 l_adj_rec.line_adjusted,
2618 l_adj_rec.tax_adjusted,
2619 l_adj_rec.freight_adjusted,
2620 l_adj_rec.receivables_charges_adjusted,
2621 l_adj_rec.apply_date,
2622 l_adj_rec.gl_date,
2623 l_acctd_amount_adjusted,
2624 l_ps_rec );
2625
2626 END IF;
2627
2628 IF ( p_adjustment_code = 'R' )
2629 THEN
2630 /*3869570 Replaced p_adj_rec.apply_date and
2631 p_adj_rec.gl_Date with l_ps_rec.actual_date_closed and
2632 l_ps_rec.gl_date_closed*/
2633 arp_ps_util.update_adj_related_columns(
2634 null,
2635 l_adj_rec.type,
2636 null,
2637 -1 *
2638 l_ps_rec.amount_adjusted_pending,
2639 l_adj_rec.line_adjusted,
2640 l_adj_rec.tax_adjusted,
2641 l_adj_rec.freight_adjusted,
2642 l_adj_rec.receivables_charges_adjusted,
2643 l_ps_rec.actual_date_closed,
2644 l_ps_rec.gl_date_closed,
2645 l_acctd_amount_adjusted,
2646 l_ps_rec );
2647
2648 END IF;
2649
2650 l_adj_rec.status := NVL( p_adjustment_code, l_adj_rec.status );
2651
2652 /*--------------------------+
2653 | Update ar_adjustments |
2654 +--------------------------*/
2655
2656 arp_adjustments_pkg.update_p( l_adj_rec,
2657 p_adjustment_id,
2658 l_ps_rec.exchange_rate );
2659
2660 --BUG#2750340
2661 /*------------------------------------------------+
2662 | Need to call AR XLA event because a ADJ can be |
2663 | updated without touching its accounting |
2664 +------------------------------------------------*/
2665 l_xla_ev_rec.xla_from_doc_id := p_adjustment_id;
2666 l_xla_ev_rec.xla_to_doc_id := p_adjustment_id;
2667 l_xla_ev_rec.xla_doc_table := 'ADJ';
2668 l_xla_ev_rec.xla_mode := 'O';
2669 l_xla_ev_rec.xla_call := 'B';
2670 ARP_XLA_EVENTS.create_events(p_xla_ev_rec => l_xla_ev_rec);
2671
2672 /*-------------------------------------------------------------------+
2673 | Change for the BR/BOE project has been made. |
2674 | Accounting is created only if the status is 'A' and the |
2675 | accounting_affect_flag of the receivables_Trx is not set to 'N' |
2676 +-------------------------------------------------------------------*/
2677
2678 /*--------------------------------------------+
2679 | Change made for BR/BOE project. |
2680 | Get the value for accounting_affect_flag |
2681 +--------------------------------------------*/
2682
2683 /*-------------------------------------------------------------------+
2684 | Bug 1277494 Added NVL to selection which was missing |
2685 | and Call Accounting Engine if accounting affect flag is not 'N' |
2686 +------------------------------------------------------------------+*/
2687 BEGIN
2688 SELECT NVL(accounting_affect_flag,'Y')
2689 INTO l_accounting_affect_flag
2690 FROM ar_receivables_trx
2691 WHERE receivables_trx_id = l_adj_rec.receivables_trx_id;
2692
2693 EXCEPTION
2694 WHEN OTHERS THEN
2695 l_accounting_affect_flag := 'Y';
2696 END;
2697
2698 -- need to do some stuff for LLCA
2699 IF (l_adj_rec.created_from = 'ARXRWLLC' and l_gt_id = 0) THEN
2700 -- we have line level app with non-recoverable tax
2701 -- we need to populate the gt table before calling the
2702 -- the accting engine.
2703
2704 arp_llca_adjust_pkg.LLCA_Adjustments(
2705 p_customer_trx_line_id => l_adj_rec.customer_trx_line_id,
2706 p_customer_trx_id => l_adj_rec.customer_trx_id,
2707 p_line_adjusted => l_adj_rec.line_adjusted,
2708 p_tax_adjusted => l_adj_rec.tax_adjusted,
2709 p_adj_id => p_adjustment_id,
2710 p_inv_currency_code => l_ps_rec.invoice_currency_code,
2711 p_gt_id => l_gt_id_temp );
2712
2713 l_gt_id := l_gt_id_temp;
2714 l_from_llca_call := 'Y';
2715 END IF;
2716
2717 IF (l_adj_rec.status = 'A' and l_accounting_affect_flag <> 'N') THEN
2718 l_ae_doc_rec.document_type := 'ADJUSTMENT';
2719 l_ae_doc_rec.document_id := p_adjustment_id;
2720 l_ae_doc_rec.accounting_entity_level := 'ONE';
2721 l_ae_doc_rec.source_table := 'ADJ';
2722 l_ae_doc_rec.source_id := p_adjustment_id;
2723 l_ae_doc_rec.deferred_tax := p_move_deferred_tax;
2724
2725 --Bug 1329091 - PS is updated before Accounting Engine Call
2726 l_ae_doc_rec.pay_sched_upd_yn := 'Y';
2727
2728 /*---------------------------+
2729 | Call Accounting Engine |
2730 +---------------------------*/
2731
2732 arp_acct_main.Create_Acct_Entry(p_ae_doc_rec => l_ae_doc_rec,
2733 p_from_llca_call => l_from_llca_call,
2734 p_gt_id => l_gt_id);
2735
2736 END IF;
2737
2738 /*-------------------------------------------+
2739 | Insert into ar_approval_action_history |
2740 +-------------------------------------------*/
2741
2742 l_aah_rec.action_name := l_adj_rec.status;
2743 l_aah_rec.adjustment_id := p_adjustment_id;
2744 l_aah_rec.action_date := TRUNC( sysdate );
2745 l_aah_rec.comments := l_adj_rec.comments;
2746
2747 arp_aah_pkg.insert_p(
2748 l_aah_rec,
2749 l_approval_action_history_id
2750 );
2751
2752 arp_util.debug('ar_process_adjustment.update_approve_adj()-',
2753 pg_msg_level_debug);
2754
2755
2756 EXCEPTION
2757 WHEN OTHERS THEN
2758 arp_util.debug(
2759 'EXCEPTION: ar_process_adjustment.update_approve_adj()',
2760 pg_msg_level_debug);
2761 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
2762 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.update_approce_adjustment exception: '||SQLERRM );
2763
2764 arp_util.debug('', pg_msg_level_debug);
2765 arp_util.debug('---------- parameters for update_approve_adj()'
2766 || ' ---------',
2767 pg_msg_level_debug);
2768
2769 arp_util.debug('p_form_name = ' || p_form_name );
2770 arp_util.debug('p_form_version = ' || p_form_version );
2771 arp_util.debug('p_adjustment_code = ' || p_adjustment_code );
2772 arp_util.debug('p_adjustment_id = ' || p_adjustment_id );
2773
2774 arp_adjustments_pkg.display_adj_rec( p_adj_rec );
2775
2776 RAISE;
2777
2778 END;
2779
2780
2781 /*===========================================================================+
2782 | PROCEDURE |
2783 | test_adj |
2784 | |
2785 | DESCRIPTION |
2786 | Verifies that adjustment approvals updates the relevant tables |
2787 | correctly. This procedure should only be called during tests of |
2788 | the update_approve_adj() procedure. |
2789 | |
2790 | SCOPE - PUBLIC |
2791 | |
2792 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2793 | arp_util.debug |
2794 | |
2795 | ARGUMENTS : IN: |
2796 | None |
2797 | OUT: |
2798 | None |
2799 | IN / OUT: |
2800 | p_result |
2801 | p_old_ps_rec |
2802 | p_adj_rec |
2803 | |
2804 | RETURNS : NONE |
2805 | |
2806 | NOTES |
2807 | |
2808 | MODIFICATION HISTORY |
2809 | 12-SEP-95 Charlie Tomberg Created |
2810 | |
2811 +===========================================================================*/
2812
2813 PROCEDURE test_adj( p_adj_rec IN OUT NOCOPY ar_adjustments%rowtype,
2814 p_result IN OUT NOCOPY varchar2,
2815 p_old_ps_rec IN OUT NOCOPY ar_payment_schedules%rowtype) IS
2816
2817 l_new_ps_rec ar_payment_schedules%rowtype;
2818
2819 BEGIN
2820 arp_util.debug('test_adj()+');
2821
2822
2823 /*---------------------------------------------------+
2824 | Verify that the adjustment was updated properly |
2825 +---------------------------------------------------*/
2826
2827 p_adj_rec.acctd_amount :=
2828 arpcurr.functional_amount(
2829 p_adj_rec.amount,
2830 'USD',
2831 p_old_ps_rec.exchange_rate,
2832 2,
2833 null);
2834
2835 select decode(max(adjustment_id),
2836 NULL, 'A: Fail, ',
2837 'A: Pass, ')
2838 into p_result
2839 from ar_adjustments
2840 where adjustment_id = p_adj_rec.adjustment_id
2841 and adjustment_number = p_adj_rec.adjustment_number
2842 and payment_schedule_id = p_adj_rec.payment_schedule_id
2843 and customer_trx_id = p_adj_rec.customer_trx_id
2844 and amount = p_adj_rec.amount
2845 and (
2846 (
2847 nvl(line_adjusted,
2848 -99.9999) = decode(p_adj_rec.type,
2849 'LINE', p_adj_rec.amount,
2850 -99.9999)
2851 and nvl(tax_adjusted,
2852 -99.9999) = decode(p_adj_rec.type,
2853 'TAX', p_adj_rec.amount,
2854 -99.9999)
2855 and nvl(freight_adjusted,
2856 -99.9999) = decode(p_adj_rec.type,
2857 'FREIGHT', p_adj_rec.amount,
2858 -99.9999)
2859 and nvl(receivables_charges_adjusted ,
2860 -99.9999) = decode(p_adj_rec.type,
2861 'CHARGES', p_adj_rec.amount,
2862 -99.9999)
2863 ) OR
2864 (
2865 (
2866 p_adj_rec.type = 'INVOICE'
2867 and p_adj_rec.amount = nvl(line_adjusted, 0) +
2868 nvl(tax_adjusted, 0) +
2869 nvl(freight_adjusted, 0) +
2870 nvl(receivables_charges_adjusted, 0)
2871 )
2872 )
2873 )
2874 and apply_date = p_adj_rec.apply_date
2875 and gl_date = p_adj_rec.gl_date
2876 and code_combination_id = p_adj_rec.code_combination_id
2877 and type = p_adj_rec.type
2878 and adjustment_type = p_adj_rec.adjustment_type
2879 and status = p_adj_rec.status
2880 and nvl(customer_trx_line_id,
2881 -999.999) = NVL(p_adj_rec.customer_trx_line_id, -999.999)
2882 and receivables_trx_id = p_adj_rec.receivables_trx_id
2883 and created_from = p_adj_rec.created_from
2884 -- check the derived columns
2885 and postable = 'Y'
2886 and approved_by = arp_adjustments_pkg.pg_user_id
2887 and nvl(comments, '^%') = nvl(p_adj_rec.comments, '^%')
2888 and acctd_amount = p_adj_rec.acctd_amount;
2889
2890 IF ( p_result = 'A: Fail, ' )
2891 THEN
2892 arp_util.debug('----- database adjustment record -----');
2893 arp_adjustments_pkg.display_adj_p(p_adj_rec.adjustment_id);
2894 arp_util.debug('----- parameter adjustment record -----');
2895 arp_adjustments_pkg.display_adj_rec(p_adj_rec);
2896 END IF;
2897
2898
2899 /*------------------------------------------------------------------+
2900 | Verify that a row was inserted into ar_approval_action_history |
2901 +------------------------------------------------------------------*/
2902
2903 select p_result ||
2904 decode(max(approval_action_history_id),
2905 NULL, 'H: Fail, ',
2906 'H: Pass, ')
2907 into p_result
2908 from ar_approval_action_history
2909 where adjustment_id = p_adj_rec.adjustment_id
2910 and action_name = p_adj_rec.status
2911 and action_date = TRUNC(sysdate)
2912 and nvl(comments, '!@#$%') = nvl(p_adj_rec.comments, '!@#$%');
2913
2914 /*---------------------------------------------------------+
2915 | Verify that the payment schedule was updated properly |
2916 +---------------------------------------------------------*/
2917
2918 arp_ps_pkg.fetch_p(p_adj_rec.payment_schedule_id, l_new_ps_rec);
2919
2920
2921 select decode( max(dummy),
2922 null, p_result || 'P: Fail',
2923 p_result || 'P: Pass'
2924 )
2925 into p_result
2926 from dual
2927 where
2928 (
2929 (l_new_ps_rec.amount_due_remaining =
2930 p_old_ps_rec.amount_due_remaining + p_adj_rec.amount)
2931 AND
2932 (l_new_ps_rec.acctd_amount_due_remaining =
2933 round(
2934 (p_old_ps_rec.amount_due_remaining + p_adj_rec.amount)
2935 * p_old_ps_rec.exchange_rate,
2936 2
2937 ) )
2938 AND
2939 (
2940 (
2941 decode(p_adj_rec.type,
2942 'LINE', l_new_ps_rec.amount_line_items_remaining,
2943 'TAX', l_new_ps_rec.tax_remaining,
2944 'FREIGHT', l_new_ps_rec.freight_remaining,
2945 'CHARGES', l_new_ps_rec.receivables_charges_remaining) =
2946 decode(p_adj_rec.type,
2947 'LINE', p_old_ps_rec.amount_line_items_remaining,
2948 'TAX', p_old_ps_rec.tax_remaining,
2949 'FREIGHT', p_old_ps_rec.freight_remaining,
2950 'CHARGES', p_old_ps_rec.receivables_charges_remaining) +
2951 p_adj_rec.amount
2952 )
2953 OR
2954 ( p_adj_rec.type = 'INVOICE')
2955 )
2956 AND
2957 (l_new_ps_rec.amount_adjusted =
2958 (
2959 nvl(p_old_ps_rec.amount_adjusted, 0) +
2960 p_adj_rec.amount) )
2961 AND
2962 (l_new_ps_rec.amount_due_remaining =
2963 nvl(l_new_ps_rec.amount_line_items_remaining,0) +
2964 nvl(l_new_ps_rec.tax_remaining,0) +
2965 nvl(l_new_ps_rec.freight_remaining,0) +
2966 nvl(l_new_ps_rec.receivables_charges_remaining,0))
2967 AND
2968 (l_new_ps_rec.amount_due_remaining =
2969 l_new_ps_rec.amount_due_original
2970 + nvl(l_new_ps_rec.amount_adjusted,0)
2971 - nvl(l_new_ps_rec.amount_applied,0)
2972 + nvl(l_new_ps_rec.amount_credited,0)
2973 - nvl(l_new_ps_rec.discount_taken_earned,0)
2974 - nvl(l_new_ps_rec.discount_taken_unearned,0))
2975 AND
2976 ( (l_new_ps_rec.status = 'OP' AND
2977 l_new_ps_rec.amount_due_remaining <> 0)
2978 OR
2979 (l_new_ps_rec.status = 'CL' AND
2980 l_new_ps_rec.amount_due_remaining = 0))
2981 );
2982
2983
2984 arp_util.debug('test_adj()-');
2985
2986 EXCEPTION
2987 WHEN OTHERS THEN
2988 arp_util.debug( 'EXCEPTION: ar_process_adjustment.test_adj()',
2989 pg_msg_level_debug);
2990 RAISE;
2991 END;
2992
2993
2994 PROCEDURE validate_args_radj( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
2995 p_reversal_gl_date IN DATE,
2996 p_reversal_date IN DATE );
2997 --
2998 PROCEDURE modify_adj_rec( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
2999 p_reversal_gl_date IN DATE,
3000 p_reversal_date IN DATE );
3001 --
3002
3003 PROCEDURE val_insert_rev_actions(
3004 p_adj_id IN ar_adjustments.adjustment_id%TYPE );
3005 --
3006 /*===========================================================================+
3007 | PROCEDURE |
3008 | reverse_adjustment() |
3009 | |
3010 | DESCRIPTION |
3011 | This function reverses an adjustment by inserting an opposing entry |
3012 | in the AR_ADJUSTMENTS table |
3013 | |
3014 | SCOPE - PUBLIC |
3015 | |
3016 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3017 | arp_aa_history_pkg.insert_p - approval history table insert table |
3018 | handler |
3019 | |
3020 | ARGUMENTS : IN: |
3021 | p_adj_id - Id of row to be reversed |
3022 | p_reversal_gl_date - Reversal GL date |
3023 | p_reversal_date - Reversal Date |
3024 | p_module_name - Name of the module that called this proc. |
3025 | p_module_version - Version of module that called this proc|
3026 | OUT: |
3027 | None |
3028 | |
3029 | RETURNS : NONE |
3030 | |
3031 | NOTES |
3032 | |
3033 | MODIFICATION HISTORY
3034 | 04/25/95 Ganesh Vaidee Created
3035 | 4/18/1996 Harri Kaukovuo Added RAISE clause to locking block
3036 | Added NOWAIT to FOR UPDATE OF ... clause
3037 | Removed hard coded comment and replaced it
3038 | with message dictionary equivalent.
3039 +===========================================================================*/
3040 PROCEDURE reverse_adjustment(
3041 p_adj_id IN ar_adjustments.adjustment_id%TYPE,
3042 p_reversal_gl_date IN DATE,
3043 p_reversal_date IN DATE,
3044 p_module_name IN VARCHAR2,
3045 p_module_version IN VARCHAR2 ) IS
3046 l_aah_rec ar_approval_action_history%ROWTYPE;
3047 l_aah_id NUMBER;
3048 l_adj_rec ar_adjustments%ROWTYPE;
3049 l_ps_rec ar_payment_schedules%rowtype;
3050 --BUG#2750340
3051 l_xla_ev_rec arp_xla_events.xla_events_type;
3052
3053 /* 7699796 */
3054 l_event_source_info xla_events_pub_pkg.t_event_source_info;
3055 l_event_id NUMBER;
3056 l_security xla_events_pub_pkg.t_security;
3057 l_adj_post_to_gl ra_cust_trx_types.adj_post_to_gl%TYPE := 'Y' ;
3058
3059 BEGIN
3060 IF PG_DEBUG in ('Y', 'C') THEN
3061 arp_standard.debug( 'arp_process_adjustment.reverse_adjustment()+' );
3062 arp_standard.debug( 'p_adj_id = '||to_char( p_adj_id ) );
3063 END IF;
3064
3065 IF (p_module_name IS NOT NULL
3066 AND p_module_version IS NOT NULL )
3067 THEN
3068 validate_args_radj( p_adj_id, p_reversal_gl_date, p_reversal_date );
3069 END IF;
3070
3071 -- Select from ar_adjustments to update status. This is just a
3072 -- simple select statement, so not using a separate function. Also
3073 -- note that the WHERE clause is different than the fetch_p procedure
3074 -- in the table handler. Also locking the table
3075
3076 -- This block will update all other adjustments than Approved and
3077 -- Rejected to be Rejected.
3078
3079 IF PG_DEBUG in ('Y', 'C') THEN
3080 arp_util.debug ( 'before update all other adjustments');
3081 END IF;
3082 BEGIN
3083 SELECT *
3084 INTO l_adj_rec
3085 FROM ar_adjustments adj
3086 WHERE adj.adjustment_id = p_adj_id
3087 AND adj.status not in ('A', 'R')
3088 FOR UPDATE of adj.STATUS NOWAIT;
3089
3090 l_adj_rec.status := 'R';
3091
3092 arp_adj_pkg.update_p( l_adj_rec );
3093
3094 BEGIN
3095
3096 /* 7699796 */
3097
3098 Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
3099 into l_adj_post_to_gl
3100 from ra_customer_trx ct, ra_cust_trx_types ctt
3101 where ct.customer_trx_id = l_adj_rec.customer_trx_id
3102 and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
3103
3104 IF PG_DEBUG in ('Y', 'C') THEN
3105 arp_util.debug('l_adj_post_to_gl : '|| l_adj_post_to_gl);
3106 END IF;
3107
3108 EXCEPTION
3109 WHEN OTHERS THEN
3110
3111 IF PG_DEBUG in ('Y', 'C') THEN
3112 arp_util.debug('Unable to get post to gl flag for adjustment' );
3113 arp_util.debug('EXCEPTION: arp_process_adjustment.reverse_adjustment '|| SQLERRM);
3114 END IF;
3115 RAISE;
3116 END;
3117
3118
3119 IF NVL(l_adj_post_to_gl, 'N') = 'Y' THEN
3120
3121 IF ( l_adj_rec.status = 'R' ) THEN
3122 BEGIN
3123
3124 select xet.legal_entity_id legal_entity_id,
3125 adj.SET_OF_BOOKS_ID set_of_books_id,
3126 adj.org_id org_id,
3127 adj.event_id event_id,
3128 xet.entity_code entity_code,
3129 adj.adjustment_id adjustment_id,
3130 xet.application_id
3131 into
3132 l_event_source_info.legal_entity_id,
3133 l_event_source_info.ledger_id,
3134 l_security.security_id_int_1,
3135 l_event_id ,
3136 l_event_source_info.entity_type_code,
3137 l_event_source_info.source_id_int_1,
3138 l_event_source_info.application_id
3139 from
3140 ar_adjustments adj ,
3141 xla_transaction_entities_upg xet
3142 where adj.adjustment_id = p_adj_id
3143 and adj.adjustment_id = nvl(xet.source_id_int_1,-99)
3144 and xet.entity_code ='ADJUSTMENTS'
3145 AND xet.application_id = 222
3146 AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
3147
3148 xla_events_pub_pkg.update_event
3149 (p_event_source_info => l_event_source_info,
3150 p_event_id => l_event_id,
3151 p_event_status_code => 'N',
3152 p_valuation_method => null,
3153 p_security_context => l_security);
3154 EXCEPTION
3155 WHEN OTHERS THEN
3156 IF PG_DEBUG in ('Y', 'C') THEN
3157 arp_util.debug('Unable to get the XLA Entites Data ' ||
3158 'EXCEPTION: arp_process_adjustment.reverse_adjustment' );
3159 END IF;
3160 RAISE;
3161 END;
3162 END IF ;
3163 END IF;
3164
3165 EXCEPTION
3166 WHEN NO_DATA_FOUND THEN
3167 IF PG_DEBUG in ('Y', 'C') THEN
3168 arp_standard.debug(
3169 'NO_DATA_FOUND: arp_process_adjustment.reverse_adjustment' );
3170 END IF;
3171
3172 WHEN OTHERS THEN
3173 IF PG_DEBUG in ('Y', 'C') THEN
3174 arp_standard.debug(
3175 'EXCEPTION: arp_process_adjustment.reverse_adjustment:SELECT' );
3176 END IF;
3177 RAISE;
3178 END;
3179
3180 -- Create a record in AR_APPROVAL_ACTION_HISTORY for the above adj
3181 -- Get the message from message dict for inserting in comments
3182
3183 l_aah_rec.action_name := 'R';
3184 l_aah_rec.adjustment_id := p_adj_id;
3185 l_aah_rec.action_date := TRUNC( SYSDATE );
3186
3187 l_aah_rec.comments :=
3188 fnd_message.get_string ('AR','AR_ADJ_REVERSED');
3189
3190 IF PG_DEBUG in ('Y', 'C') THEN
3191 arp_util.debug( 'before insert_p for ar_approval_action_history');
3192 END IF;
3193 arp_aa_history_pkg.insert_p( l_aah_rec, l_aah_id );
3194
3195 -- If status of adj == R, then there is no need to create an opposing
3196 -- Approved adj. In fact, in this case you dont have to do anything
3197 -- Otherwise, create an opposing adj with status = A
3198 -- and amount = (-1)*amount
3199
3200 IF PG_DEBUG in ('Y', 'C') THEN
3201 arp_util.debug( 'before modify_adj_rec');
3202 END IF;
3203 modify_adj_rec(
3204 p_adj_id
3205 , p_reversal_gl_date
3206 , p_reversal_date );
3207
3208
3209 IF PG_DEBUG in ('Y', 'C') THEN
3210 arp_standard.debug( 'arp_process_adjustment.reverse_adjustment()-' );
3211 END IF;
3212
3213 EXCEPTION
3214 WHEN OTHERS THEN
3215 IF PG_DEBUG in ('Y', 'C') THEN
3216 arp_standard.debug(
3217 'EXCEPTION: arp_process_adjustment.reverse_adjustment' );
3218 END IF;
3219 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
3220 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.reverse_adjustment exception: '||SQLERRM );
3221
3222 IF PG_DEBUG in ('Y', 'C') THEN
3223 arp_standard.debug ( 'p_adj_id = '|| TO_CHAR(p_adj_id ) );
3224 arp_standard.debug ( 'p_reversal_gl_date = '|| TO_CHAR(p_reversal_gl_date));
3225 arp_standard.debug ( 'p_reversal_date = '|| TO_CHAR(p_reversal_date));
3226 arp_standard.debug ( 'p_module_name = '|| p_module_name);
3227 arp_standard.debug ( 'p_module_version = '|| p_module_version);
3228 END IF;
3229
3230 RAISE;
3231 END;
3232 --
3233 /*===========================================================================+
3234 | PROCEDURE |
3235 | validate_args_radj |
3236 | |
3237 | DESCRIPTION |
3238 | Validate inputs to reverse_adjustment procedure |
3239 | |
3240 | SCOPE - PRIVATE |
3241 | |
3242 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
3243 | |
3244 | ARGUMENTS : IN: |
3245 | p_adj_id - Adjustments Record Id |
3246 | p_reversal_gl_date - Reversal GL date |
3247 | p_reversal_date - Reversal Date |
3248 | OUT: |
3249 | None |
3250 | |
3251 | RETURNS : NONE |
3252 | |
3253 | NOTES |
3254 | |
3255 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
3256 | |
3257 +===========================================================================*/
3258 PROCEDURE validate_args_radj( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
3259 p_reversal_gl_date IN DATE,
3260 p_reversal_date IN DATE ) IS
3261 BEGIN
3262 IF PG_DEBUG in ('Y', 'C') THEN
3263 arp_standard.debug( 'arp_process_adjustment.validate_args_radj()+' );
3264 END IF;
3265 IF ( p_adj_id is NULL OR p_reversal_gl_date is NULL OR
3266 p_reversal_date is NULL ) THEN
3267 IF PG_DEBUG in ('Y', 'C') THEN
3268 arp_standard.debug( ' Null values found in input variable' );
3269 END IF;
3270 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
3271 APP_EXCEPTION.raise_exception;
3272 END IF;
3273 --
3274 IF PG_DEBUG in ('Y', 'C') THEN
3275 arp_standard.debug( 'arp_process_adjustment.validate_args_radj()-' );
3276 END IF;
3277 EXCEPTION
3278 WHEN OTHERS THEN
3279 IF PG_DEBUG in ('Y', 'C') THEN
3280 arp_standard.debug(
3281 'EXCEPTION: arp_process_adjustment.validate_args_radj' );
3282 END IF;
3283 RAISE;
3284 END;
3285 --
3286 /*===========================================================================+
3287 | PROCEDURE |
3288 | modify_adj_rec |
3289 | |
3290 | DESCRIPTION |
3291 | Modify Adjustment Record to prepare for reversal |
3292 | |
3293 | SCOPE - PRIVATE |
3294 | |
3295 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
3296 | |
3297 | ARGUMENTS : IN: |
3298 | p_adj_id - Adjustments Record Id |
3299 | p_reversal_gl_date - Reversal GL date |
3300 | p_reversal_date - Reversal Date |
3301 | OUT: |
3302 | None |
3303 | |
3304 | RETURNS : NONE |
3305 | |
3306 | NOTES |
3307 | |
3308 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
3309 | |
3310 +===========================================================================*/
3311 PROCEDURE modify_adj_rec( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
3312 p_reversal_gl_date IN DATE,
3313 p_reversal_date IN DATE ) IS
3314 l_adj_rec ar_adjustments%ROWTYPE;
3315 l_rev_gl_date DATE;
3316 l_error_message VARCHAR2(128);
3317 l_defaulting_rule_used VARCHAR2(100);
3318 l_default_gl_date DATE;
3319 BEGIN
3320 IF PG_DEBUG in ('Y', 'C') THEN
3321 arp_standard.debug( 'arp_process_adjustment.modify_adj_rec()+' );
3322 arp_standard.debug( 'p_adj_id = '||to_char( p_adj_id ) );
3323 END IF;
3324
3325 arp_adj_pkg.fetch_p( p_adj_id, l_adj_rec );
3326
3327 l_adj_rec.apply_date := p_reversal_date;
3328
3329 /* bug 3687113 */
3330 IF p_reversal_gl_date > l_adj_rec.gl_date THEN
3331 l_adj_rec.gl_date := p_reversal_gl_date;
3332 ELSE
3333 l_rev_gl_date := l_adj_rec.gl_date;
3334 IF (arp_standard.validate_and_default_gl_date(
3335 l_rev_gl_date,
3336 NULL,
3337 l_rev_gl_date,
3338 NULL,
3339 NULL,
3340 NULL,
3341 NULL,
3342 NULL,
3343 'N',
3344 NULL,
3345 arp_global.set_of_books_id,
3346 222,
3347 l_default_gl_date,
3348 l_defaulting_rule_used,
3349 l_error_message) = TRUE)
3350 THEN
3351 l_adj_rec.gl_date := l_default_gl_date;
3352 END IF;
3353 END IF;
3354 arp_standard.debug(' Adjustment Reversal GL Date '|| l_default_gl_date);
3355
3356 l_adj_rec.amount := -l_adj_rec.amount;
3357 l_adj_rec.acctd_amount := -l_adj_rec.acctd_amount;
3358 IF ( l_adj_rec.chargeback_customer_trx_id is NULL ) THEN
3359 l_adj_rec.receivables_trx_id := -13;
3360 ELSE
3361 l_adj_rec.receivables_trx_id := arp_global.G_CB_REV_RT_ID;
3362 END IF;
3363
3364 l_adj_rec.line_adjusted := -l_adj_rec.line_adjusted;
3365 l_adj_rec.freight_adjusted := -l_adj_rec.freight_adjusted;
3366 l_adj_rec.tax_adjusted := -l_adj_rec.tax_adjusted;
3367 l_adj_rec.receivables_charges_adjusted :=
3368 -l_adj_rec.receivables_charges_adjusted;
3369 l_adj_rec.adjustment_type := 'M';
3370 l_adj_rec.created_from := 'REVERSE_ADJUSTMENT';
3371
3372 /* VAT changes: pass old adjustment_id to insert_reverse_actions
3373 to be in turn passed to accounting library */
3374 l_adj_rec.adjustment_id := p_adj_id;
3375
3376 insert_reverse_actions( l_adj_rec, NULL, NULL );
3377
3378 IF PG_DEBUG in ('Y', 'C') THEN
3379 arp_standard.debug( 'arp_process_adjustment.modify_adj_rec()-' );
3380 END IF;
3381
3382 EXCEPTION
3383 WHEN OTHERS THEN
3384 IF PG_DEBUG in ('Y', 'C') THEN
3385 arp_standard.debug(
3386 'EXCEPTION: arp_process_adjustment.modify_adj_rec' );
3387 END IF;
3388 RAISE;
3389
3390 END;
3391 --
3392 /*===========================================================================+
3393 | PROCEDURE |
3394 | insert_reverse_actions |
3395 | |
3396 | DESCRIPTION |
3397 | This procedure performs all actions to modify the passed in |
3398 | adjustments record and calls adjustments insert table handler to |
3399 | insert the reversed adjuetments row |
3400 | |
3401 | SCOPE - PUBLIC |
3402 | |
3403 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
3404 | arp_adj_pkg.insert_p - Insert a row into AR_ADJUSTMENTS table|
3405 | |
3406 | ARGUMENTS : IN OUT: |
3407 | p_adj_rec - Adjustment Record structure |
3408 | p_module_name _ Name of module that called this procedure|
3409 | p_module_version - Version of module that called this |
3410 | procedure |
3411 | OUT: |
3412 | |
3413 | RETURNS : NONE |
3414 | |
3415 | NOTES |
3416 | |
3417 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
3418 | 7/30/1996 Harri Kaukovuo Fixed the code to fnd_seqnum, because AOL
3419 | has changed the data type of the date parameter.
3420 | Fixed possible bug cancidate when trying to
3421 | select name from ar_receivables_trx into
3422 | VARCHAR2(30) field. Name is VARCHAR2(50).
3423 | 7/30/1996 Harri Kaukovuo Bug fix 387035
3424 |10/16/1998 Sushama Borde Bug fix 741725: Used AOL API get_next_sequence
3425 | instead of get_seq_name.
3426 +===========================================================================*/
3427 PROCEDURE insert_reverse_actions (
3428 p_adj_rec IN OUT NOCOPY ar_adjustments%ROWTYPE,
3429 p_module_name IN VARCHAR2,
3430 p_module_version IN VARCHAR2 ) IS
3431 l_new_adj_id ar_adjustments.adjustment_id%TYPE;
3432 l_old_adj_id ar_adjustments.adjustment_id%TYPE;
3433
3434 l_rec_name VARCHAR2(50);
3435 l_number NUMBER;
3436
3437 -- This stuff is for sequence numbering
3438 l_sequence_name VARCHAR2(500);
3439 l_sequence_id NUMBER;
3440 l_sequence_value NUMBER;
3441 l_sequence_assignment_id NUMBER;
3442 /* VAT changes */
3443 l_ae_doc_rec ae_doc_rec_type;
3444 --BUG#2750340
3445 l_xla_ev_rec arp_xla_events.xla_events_type;
3446 /* 7699796 */
3447 l_event_source_info xla_events_pub_pkg.t_event_source_info;
3448 l_event_id NUMBER;
3449 l_security xla_events_pub_pkg.t_security;
3450 l_adj_post_to_gl ra_cust_trx_types.adj_post_to_gl%TYPE := 'Y' ;
3451 l_adj_status VARCHAR2(1);
3452
3453
3454 BEGIN
3455 IF PG_DEBUG in ('Y', 'C') THEN
3456 arp_standard.debug( 'arp_process_adjustment.insert_reverse_actions()+');
3457 END IF;
3458
3459 /* VAT changes: save p_adj_rec.adjustment_id in l_old_adj_id
3460 to be passed to accounting library. Clear p_adj_rec.adjustment_id
3461 afterwards */
3462 l_old_adj_id := p_adj_rec.adjustment_id;
3463 p_adj_rec.adjustment_id := NULL;
3464
3465 -- -------------------------------------------------------------------
3466 -- This function could be called from a FORMS or SRW and if so, this
3467 -- validate args function should be enabled, However at that time we
3468 -- should determine what argument to check for
3469 -- -------------------------------------------------------------------
3470 IF ( p_adj_rec.status is NULL ) THEN
3471 p_adj_rec.status := 'A';
3472 END IF;
3473
3474 -- Set up sequential numbering stuff
3475
3476 -- Fix for bug 540964: use p_adj_rec.receivables_trx_id instead of
3477 -- arp_global.G_CB_REV_RT_ID to make sure the
3478 -- correct sequence is used for Adjustments
3479
3480 SELECT rt.name
3481 INTO l_rec_name
3482 FROM ar_receivables_trx rt
3483 WHERE rt.receivables_trx_id = p_adj_rec.receivables_trx_id;
3484
3485 IF PG_DEBUG in ('Y', 'C') THEN
3486 arp_standard.debug( 'after select in insert_revers_actions in app_delete' );
3487 END IF;
3488
3489 -- -----------------------------------------------------------------
3490 -- Get document numbers only if customer is using document numbering
3491 -- -----------------------------------------------------------------
3492 -- Profile option values:
3493 -- 'A' = always used
3494 -- 'P' = Partially Used
3495 -- 'N' = not used
3496 IF (fnd_profile.value('UNIQUE:SEQ_NUMBERS') <> 'N')
3497 THEN
3498 BEGIN
3499
3500 /* Commented to fix bug #741725, as this does not handle gapless sequence
3501 -- numbering.
3502 FND_SEQNUM.GET_SEQ_NAME(
3503 arp_standard.application_id
3504 , l_rec_name -- category code
3505 , arp_global.set_of_books_id
3506 , 'A'
3507 , p_adj_rec.apply_date
3508 , l_sequence_name
3509 , l_sequence_id
3510 , l_sequence_assignment_id);
3511
3512 p_adj_rec.doc_sequence_value :=
3513 fnd_seqnum.get_next_auto_seq(l_sequence_name);
3514 p_adj_rec.doc_sequence_id := l_sequence_id;
3515 */
3516
3517 -- Bug fix #741725: Use AOL API get_next_sequence() instead of
3518 -- get_seq_name.
3519 p_adj_rec.doc_sequence_value :=
3520 FND_SEQNUM.GET_NEXT_SEQUENCE(
3521 appid => arp_standard.application_id,
3522 cat_code => l_rec_name,
3523 sobid => arp_global.set_of_books_id,
3524 met_code => 'A',
3525 trx_date => p_adj_rec.apply_date,
3526 dbseqnm => l_sequence_name,
3527 dbseqid => p_adj_rec.doc_sequence_id);
3528
3529 IF PG_DEBUG in ('Y', 'C') THEN
3530 arp_standard.debug( 'doc sequence name = '|| l_sequence_name);
3531 arp_standard.debug( 'doc sequence id = '|| p_adj_rec.doc_sequence_id);
3532 arp_standard.debug( 'doc sequence value = '||p_adj_rec.doc_sequence_value);
3533 END IF;
3534 -- End fix for bug #741725
3535
3536 /* Bug 631699: If no document sequence is defined, gives an error
3537 if profile is set to "always used". If it is "partially used",
3538 set document number to null as adjustment reversal must have
3539 automatic sequence and cannot be entered manually */
3540 EXCEPTION
3541 WHEN NO_DATA_FOUND THEN
3542 IF (fnd_profile.value('UNIQUE:SEQ_NUMBERS') = 'A') THEN
3543 FND_MESSAGE.set_name ('AR', 'AR_TW_NO_DOC_SEQ' );
3544 APP_EXCEPTION.raise_exception;
3545 ELSE
3546 p_adj_rec.doc_sequence_value := NULL;
3547 p_adj_rec.doc_sequence_id := NULL;
3548 END IF;
3549 END;
3550
3551 IF PG_DEBUG in ('Y', 'C') THEN
3552 arp_standard.debug( 'doc sequence name = ' || l_sequence_name);
3553 arp_standard.debug( 'doc sequence id = ' || l_sequence_id);
3554 arp_standard.debug( 'doc sequence value = ' || l_sequence_value);
3555 END IF;
3556 ELSE
3557 p_adj_rec.doc_sequence_value := NULL;
3558 p_adj_rec.doc_sequence_id := NULL;
3559 END IF;
3560
3561
3562 /*
3563 p_adj_rec.doc_sequence_value :=
3564 FND_SEQNUM.get_next_auto_sequence (
3565 arp_standard.application_id
3566 , l_rec_name
3567 , arp_standard.sysparm.set_of_books_id
3568 , 'A'
3569 , to_char(p_adj_rec.apply_date,'YYYY/MM/DD'));
3570 */
3571 p_adj_rec.set_of_books_id := arp_standard.sysparm.set_of_books_id;
3572 p_adj_rec.batch_id := NULL;
3573 p_adj_rec.distribution_set_id := NULL;
3574 p_adj_rec.gl_posted_date := NULL;
3575 p_adj_rec.comments := 'XXXXXXX';
3576 p_adj_rec.automatically_generated := 'Y';
3577 p_adj_rec.approved_by := FND_GLOBAL.user_id;
3578 p_adj_rec.ussgl_transaction_code := NULL;
3579 p_adj_rec.ussgl_transaction_code_context := NULL;
3580 p_adj_rec.posting_control_id := -3;
3581 p_adj_rec.cons_inv_id :=NULL; /*-------bug 13111718 */
3582
3583
3584 -- Insert opposing adjustment
3585
3586 IF PG_DEBUG in ('Y', 'C') THEN
3587 arp_standard.debug( 'before ar_adjustments_pkg.insert_p in app_delete' );
3588 END IF;
3589 arp_adj_pkg.insert_p( p_adj_rec, l_new_adj_id );
3590 IF PG_DEBUG in ('Y', 'C') THEN
3591 arp_standard.debug( 'after ar_adjustments_pkg.insert_p in app_delete' );
3592 END IF;
3593
3594
3595 /* VAT changes: create acct entry */
3596
3597 l_ae_doc_rec.document_type := 'ADJUSTMENT';
3598 l_ae_doc_rec.document_id := l_new_adj_id;
3599 l_ae_doc_rec.accounting_entity_level := 'ONE';
3600 l_ae_doc_rec.source_table := 'ADJ';
3601 l_ae_doc_rec.source_id := l_new_adj_id;
3602 IF (p_adj_rec.created_from = 'REVERSE_CHARGEBACK') THEN
3603 l_ae_doc_rec.source_id_old := p_adj_rec.code_combination_id;
3604 l_ae_doc_rec.other_flag := 'CBREVERSAL';
3605 ELSE
3606 l_ae_doc_rec.source_id_old := l_old_adj_id;
3607 l_ae_doc_rec.other_flag := 'REVERSE';
3608 END IF;
3609 arp_acct_main.Create_Acct_Entry(l_ae_doc_rec);
3610
3611 /*7699796*/
3612
3613 BEGIN
3614
3615 Select decode (nvl(ctt.post_to_gl,'N'),'Y', 'Y', nvl(ctt.adj_post_to_gl,'N'))
3616 into l_adj_post_to_gl
3617 from ra_customer_trx ct, ra_cust_trx_types ctt
3618 where ct.customer_trx_id = p_adj_rec.customer_trx_id
3619 and ct.cust_trx_type_id = ctt.cust_trx_type_id ;
3620
3621 IF PG_DEBUG in ('Y', 'C') THEN
3622 arp_util.debug('l_adj_post_to_gl : '|| l_adj_post_to_gl);
3623 END IF;
3624
3625 EXCEPTION
3626 WHEN OTHERS THEN
3627
3628 IF PG_DEBUG in ('Y', 'C') THEN
3629 arp_util.debug('Unable to get post to gl flag for adjustment' );
3630 arp_util.debug('EXCEPTION: apr_process_adjustment.insert_reverse_actions '|| SQLERRM);
3631 END IF;
3632 RAISE;
3633 END;
3634
3635
3636 IF NVL(l_adj_post_to_gl, 'N') = 'Y' THEN
3637
3638 select status into l_adj_status from ar_adjustments
3639 where adjustment_id = l_new_adj_id ;
3640
3641 IF ( l_adj_status = 'R' ) THEN
3642 BEGIN
3643
3644 select xet.legal_entity_id legal_entity_id,
3645 adj.SET_OF_BOOKS_ID set_of_books_id,
3646 adj.org_id org_id,
3647 adj.event_id event_id,
3648 xet.entity_code entity_code,
3649 adj.adjustment_id adjustment_id,
3650 xet.application_id
3651 into
3652 l_event_source_info.legal_entity_id,
3653 l_event_source_info.ledger_id,
3654 l_security.security_id_int_1,
3655 l_event_id ,
3656 l_event_source_info.entity_type_code,
3657 l_event_source_info.source_id_int_1,
3658 l_event_source_info.application_id
3659 from
3660 ar_adjustments adj ,
3661 xla_transaction_entities_upg xet
3662 where adj.adjustment_id = l_new_adj_id
3663 and adj.adjustment_id = nvl(xet.source_id_int_1,-99)
3664 and xet.entity_code ='ADJUSTMENTS'
3665 AND xet.application_id = 222
3666 AND adj.SET_OF_BOOKS_ID = xet.LEDGER_ID;
3667
3668 xla_events_pub_pkg.update_event
3669 (p_event_source_info => l_event_source_info,
3670 p_event_id => l_event_id,
3671 p_event_status_code => 'N',
3672 p_valuation_method => null,
3673 p_security_context => l_security);
3674 EXCEPTION
3675 WHEN OTHERS THEN
3676 IF PG_DEBUG in ('Y', 'C') THEN
3677 arp_util.debug('Unable to get the XLA Entites Data ' ||
3678 'EXCEPTION: arp_process_adjustment.insert_reverse_actions' );
3679 END IF;
3680 RAISE;
3681 END;
3682
3683 END IF;
3684 END IF;
3685
3686
3687
3688 /**********************************************************************
3689 * DO NOT THINK THIS IS NEEDED FOR ETAX SO COMMENTING OUT
3690 * IF p_adj_rec.type = 'TAX' AND
3691 * p_adj_rec.status = 'A' AND -- Approved Tax Adjustment?
3692 * nvl(p_adj_rec.tax_adjusted,0) <> 0
3693 * /o VAT changes o/
3694 *
3695 * THEN
3696 * IF PG_DEBUG in ('Y', 'C') THEN
3697 * arp_standard.debug( 'before arp_process_tax.sync_vendor_f_ct_adj_id' );
3698 * END IF;
3699 *
3700 * /o--------------------------------------------------------+
3701 * | Synchronize Tax Vendor. |
3702 * +--------------------------------------------------------o/
3703 * BEGIN
3704 * arp_process_tax.sync_vendor_f_ct_adj_id( NULL,
3705 * l_new_adj_id,
3706 * 'ADJ' );
3707 * EXCEPTION
3708 * WHEN arp_tax.AR_TAX_EXCEPTION then
3709 * -- Ignore Exception for now.
3710 * null;
3711 * END;
3712 *
3713 * IF PG_DEBUG in ('Y', 'C') THEN
3714 * arp_standard.debug( 'after arp_process_tax.sync_vendor_f_ct_adj_id' );
3715 * END IF;
3716 *
3717 * END IF;
3718 *********************************************************************/
3719
3720 IF PG_DEBUG in ('Y', 'C') THEN
3721 arp_standard.debug( 'arp_process_adjustment.insert_reverse_actions()-');
3722 END IF;
3723
3724 EXCEPTION
3725 WHEN OTHERS THEN
3726 IF PG_DEBUG in ('Y', 'C') THEN
3727 arp_standard.debug(
3728 'EXCEPTION: arp_process_adjustment.insert_reverse_actions');
3729 END IF;
3730 RAISE;
3731 END insert_reverse_actions;
3732 --
3733 /*===========================================================================+
3734 | PROCEDURE |
3735 | val_insert_rev_actions |
3736 | |
3737 | DESCRIPTION |
3738 | This procedure validated arguments passed to insert_reverse_actions |
3739 | |
3740 | SCOPE - PRIVATE |
3741 | |
3742 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
3743 | |
3744 | ARGUMENTS : IN: |
3745 | p_adj_id - Adjustment Record Id |
3746 | OUT: |
3747 | |
3748 | RETURNS : NONE |
3749 | |
3750 | NOTES |
3751 | |
3752 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
3753 | |
3754 +===========================================================================*/
3755 PROCEDURE val_insert_rev_actions(
3756 p_adj_id IN ar_adjustments.adjustment_id%TYPE ) IS
3757 BEGIN
3758 IF PG_DEBUG in ('Y', 'C') THEN
3759 arp_standard.debug( 'arp_process_adjustment.val_insert_rev_actions()+' );
3760 END IF;
3761 IF ( p_adj_id IS NULL ) THEN
3762 IF PG_DEBUG in ('Y', 'C') THEN
3763 arp_standard.debug( ' Null values found in input variable' );
3764 END IF;
3765 FND_MESSAGE.set_name ('AR', 'AR_ARGUEMENTS_FAIL' );
3766 APP_EXCEPTION.raise_exception;
3767 END IF;
3768 --
3769 IF PG_DEBUG in ('Y', 'C') THEN
3770 arp_standard.debug( 'arp_process_adjustment.val_insert_rev_actions()-' );
3771 END IF;
3772 EXCEPTION
3773 WHEN OTHERS THEN
3774 IF PG_DEBUG in ('Y', 'C') THEN
3775 arp_standard.debug(
3776 'EXCEPTION: arp_process_adjustment.val_insert_rev_actions' );
3777 END IF;
3778 RAISE;
3779 END val_insert_rev_actions;
3780
3781 /* VAT changes: new procedure */
3782 /*===========================================================================+
3783 | PROCEDURE |
3784 | cal_prorated_amounts |
3785 | |
3786 | DESCRIPTION |
3787 | Given the adjusted amount, this procedure will calculate the net amount |
3788 | and the tax amount. If tax code for the receivable activity is: |
3789 | NONE - prorated line amount = adjustment amount |
3790 | proated tax = 0 |
3791 | ACTIVITY - prorated amounts are calculated using tax rate of the asset |
3792 | tax code for the receivable activity |
3793 | prorated tax = adjustment amount * tax rate / (100 + tax rate) |
3794 | prorated line amount = adjustment amount - prorated tax |
3795 | INVOICE - prorated tax = adjustment amount * tax remaining / |
3796 | (tax remaining + line remaining) |
3797 | prorated line amount = adjustment amount - prorated tax |
3798 | In case there is any error occurred, prorated tax and line will return 0|
3799 | and p_error_num will be non-zero depending on error encountered |
3800 | p_error_num = 1 when tax rate for the receivable activity tax code |
3801 | cannot be found |
3802 | p_error_num = 2 when sum of lines remaining and tax remaining is zero |
3803 | so that the proratio rate cannot be determined when |
3804 | tax code source is invoice |
3805 | p_error_num = 3 when a finance charge activity has a tax code source of |
3806 | invoice |
3807 | |
3808 | SCOPE - PUBLIC |
3809 | |
3810 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - |
3811 | arpcurr.currround |
3812 | |
3813 | ARGUMENTS : IN: |
3814 | p_adj_amount |
3815 | p_payment_schedule_id |
3816 | p_type |
3817 | OUT: |
3818 | p_prorated_amt |
3819 | p_prorated_tax |
3820 | p_error_num |
3821 | |
3822 | RETURNS : NONE |
3823 | |
3824 | NOTES: |
3825 | |
3826 | MODIFICATION HISTORY |
3827 | 10-DEC-98 Tasman Tang Created |
3828 | 04-MAR-99 Tasman Tang Added parameters p_receivables_trx_id |
3829 | and p_apply_date. Used tax rate to |
3830 | calculate prorated amounts for activity|
3831 | tax code |
3832 | 17-AUG-05 Debbie Jancis Added customer_Trx_line_id for LLCA |
3833 | and selected the balances from |
3834 | ra_customer_Trx_lines for Line Level |
3835 +===========================================================================*/
3836
3837 PROCEDURE cal_prorated_amounts( p_adj_amount IN number,
3838 p_payment_schedule_id IN number,
3839 p_type IN varchar2,
3840 p_receivables_trx_id IN number,
3841 p_apply_date IN date,
3842 p_prorated_amt OUT NOCOPY number,
3843 p_prorated_tax OUT NOCOPY number,
3844 p_error_num OUT NOCOPY number,
3845 p_cust_trx_line_id IN NUMBER default NULL
3846 ) IS
3847 l_line_remaining number;
3848 l_tax_remaining number;
3849 l_prorated_tax number;
3850 l_invoice_currency_code ar_payment_schedules.invoice_currency_code%TYPE;
3851 l_activity_type ar_receivables_trx.type%TYPE;
3852 l_tax_code_source ar_receivables_trx.tax_code_source%TYPE;
3853 l_asset_tax_code ar_receivables_trx.asset_tax_code%TYPE;
3854 l_sob_id ar_receivables_trx.set_of_books_id%TYPE;
3855 l_tax_rate ar_vat_tax.tax_rate%TYPE;
3856
3857 -- Bug 2189230
3858 /* The tax of Adjustment was calculated according to nearest rule everytime */
3859
3860 l_precision number;
3861 l_extended_precision number;
3862 l_min_acct_unit number;
3863 l_rounding_rule varchar2(30);
3864
3865 l_le_id NUMBER;
3866 /* Bug 8652261 */
3867 l_msg_count NUMBER;
3868 l_msg_data VARCHAR2(1024);
3869 l_effective_date DATE;
3870 l_return_status VARCHAR2(10);
3871
3872 BEGIN
3873 IF PG_DEBUG in ('Y', 'C') THEN
3874 arp_util.debug('arp_process_adjustment.cal_prorated_amounts()+');
3875 arp_util.debug( 'p_adj_amount = ' || to_char(p_adj_amount));
3876 arp_util.debug( 'p_payment_schedule_id = ' || to_char(p_payment_schedule_id));
3877 arp_util.debug( 'p_type = ' || p_type);
3878 arp_util.debug( 'p_receivables_trx_id = ' || to_char(p_receivables_trx_id));
3879 arp_util.debug( 'p_apply_date = ' || to_char(p_apply_date));
3880 arp_util.debug(' cust trx line id = ' || to_char(p_cust_trx_line_id));
3881 END IF;
3882
3883 p_error_num := 0;
3884
3885 IF (arp_legal_entity_util.Is_LE_Subscriber) THEN
3886 SELECT trx.legal_entity_id
3887 INTO l_le_id
3888 FROM ra_customer_Trx trx,
3889 ar_payment_schedules ps
3890 where ps.payment_schedule_id = p_payment_schedule_id
3891 and ps.customer_trx_id = trx.customer_trx_id;
3892
3893 /* 5236782 - detail table not required for adj */
3894 SELECT trx.type,
3895 trx.tax_code_source,
3896 nvl(details.asset_tax_code, trx.asset_tax_code),
3897 trx.set_of_books_id
3898 INTO l_activity_type,
3899 l_tax_code_source,
3900 l_asset_tax_code,
3901 l_sob_id
3902 FROM ar_receivables_trx trx,
3903 ar_rec_trx_le_details details
3904 WHERE trx.receivables_trx_id = p_receivables_trx_id
3905 and trx.receivables_trx_id = details.receivables_trx_id (+)
3906 and details.legal_entity_id (+) = l_le_id;
3907 ELSE
3908 SELECT type, tax_code_source, asset_tax_code, set_of_books_id
3909 INTO l_activity_type, l_tax_code_source, l_asset_tax_code, l_sob_id
3910 FROM ar_receivables_trx
3911 WHERE receivables_trx_id = p_receivables_trx_id;
3912 END IF;
3913
3914 IF l_tax_code_source = 'NONE' THEN
3915 l_prorated_tax := 0;
3916
3917 ELSE
3918 -- if p_cust_Trx_line_id is null - then it is a header level adj
3919 IF (p_cust_trx_line_id IS NULL ) THEN
3920 SELECT amount_line_items_remaining,
3921 tax_remaining,
3922 invoice_currency_code
3923 INTO l_line_remaining,
3924 l_tax_remaining,
3925 l_invoice_currency_code
3926 FROM ar_payment_schedules
3927 WHERE payment_schedule_id = p_payment_schedule_id;
3928 ELSE
3929 -- then we are adjusting at the Line Level.
3930 SELECT sum(DECODE (lines.line_type,
3931 'TAX',0,
3932 'FREIGHT',0 , 1) *
3933 DECODE(ct.complete_flag, 'N',
3934 0, lines.amount_due_remaining)), -- line adr
3935 sum(DECODE (lines.line_type,
3936 'TAX',1,0) *
3937 DECODE(ct.complete_flag,
3938 'N', 0,
3939 lines.amount_due_remaining )), -- tax adr
3940 max(ct.invoice_currency_code) -- curr code
3941 INTO l_line_remaining,
3942 l_tax_remaining,
3943 l_invoice_currency_code
3944 FROM ra_customer_trx ct,
3945 ra_customer_trx_lines lines
3946 WHERE (lines.customer_Trx_line_id = p_cust_trx_line_id or
3947 lines.link_to_cust_trx_line_id = p_cust_trx_line_id)
3948 AND ct.customer_Trx_id = lines.customer_trx_id;
3949 END IF;
3950
3951 -- Bug 2189230
3952 -- Bug 5514473 : Handled no data found so that tax_rounding_rule will be defaulted if there is no data in zx_product_options for the org
3953 -- Bug 5514473 : When application tax options are not defined through tax manager for newly created orgs there will no data in zx_product_options
3954 BEGIN
3955 SELECT tax_rounding_rule INTO l_rounding_rule
3956 FROM zx_product_options
3957 WHERE application_id = 222
3958 AND org_id = arp_global.sysparam.org_id;
3959 EXCEPTION
3960 WHEN NO_DATA_FOUND THEN
3961 l_rounding_rule := NULL;
3962 arp_util.debug('tax_rounding_rule will be defaulted because there is no row in zx_product_options');
3963 arp_util.debug('Ideal Default Tax Rounding Rule will be : NEAREST');
3964 END;
3965 arp_util.debug('tax_rounding_rule = ' || l_rounding_rule);
3966
3967 fnd_currency.Get_info(l_invoice_currency_code,
3968 l_precision,
3969 l_extended_precision,
3970 l_min_acct_unit);
3971
3972 /* NOTE: needs to be addressed when ETAX does receivable activity */
3973 IF l_tax_code_source = 'ACTIVITY' THEN
3974
3975 SELECT trx.legal_entity_id
3976 INTO l_le_id
3977 FROM ra_customer_Trx trx,
3978 ar_payment_schedules ps
3979 WHERE ps.payment_schedule_id = p_payment_schedule_id
3980 AND ps.customer_trx_id = trx.customer_trx_id;
3981
3982 /* Bug 8652261: Setting the tax security profile as we query the zx tables */
3983
3984 zx_api_pub.set_tax_security_context(
3985 p_api_version => 1.0,
3986 p_init_msg_list => 'T',
3987 p_commit => 'F',
3988 p_validation_level => NULL,
3989 x_return_status => l_return_status,
3990 x_msg_count => l_msg_count,
3991 x_msg_data => l_msg_data,
3992 p_internal_org_id => arp_standard.sysparm.org_id,
3993 p_legal_entity_id => l_le_id,
3994 p_transaction_date => p_apply_date,
3995 p_related_doc_date => NULL,
3996 p_adjusted_doc_date=> NULL,
3997 x_effective_date => l_effective_date);
3998
3999 BEGIN
4000
4001 --Bug 10136019. Added condition for zx_accounts.internal_organization_id.
4002 SELECT zxr.percentage_rate
4003 INTO l_tax_rate
4004 FROM zx_sco_rates zxr,
4005 zx_accounts zxa
4006 WHERE zxa.tax_account_entity_code = 'RATES'
4007 AND zxa.tax_account_entity_id = zxr.tax_rate_id
4008 AND NVL(zxr.tax_class, 'OUTPUT') = 'OUTPUT'
4009 AND zxr.tax_jurisdiction_code is NULL
4010 AND p_apply_date
4011 BETWEEN nvl(zxr.effective_from, p_apply_date)
4012 AND nvl(zxr.effective_to, p_apply_date)
4013 AND zxr.tax_rate_code = l_asset_tax_code
4014 AND (zxa.internal_organization_id = arp_standard.sysparm.org_id
4015 OR zxa.internal_organization_id IS NULL)
4016 AND active_flag = 'Y';
4017
4018
4019 -- Bug 2189230
4020
4021 l_prorated_tax := arp_etax_util.tax_curr_round(
4022 (p_adj_amount*l_tax_rate/(100 + l_tax_rate)),
4023 l_invoice_currency_code,
4024 l_precision,
4025 l_min_acct_unit,
4026 l_rounding_rule);
4027
4028 IF PG_DEBUG in ('Y', 'C') THEN
4029 arp_util.debug( 'l_tax_rate = ' || to_char(l_tax_rate));
4030 END IF;
4031 EXCEPTION
4032 WHEN NO_DATA_FOUND THEN
4033 IF PG_DEBUG in ('Y', 'C') THEN
4034 arp_util.debug( 'EXCEPTION: Cannot find a tax rate for the receivable activity tax code');
4035 END IF;
4036 p_error_num := 1;
4037 FND_MESSAGE.SET_NAME('AR', 'AR_TW_PRORATE_ADJ_NO_TAX_RATE');
4038 END;
4039
4040 ELSIF l_tax_code_source = 'INVOICE' THEN
4041 IF l_activity_type = 'FINCHRG' and
4042 p_type = 'CHARGES' THEN
4043 p_error_num := 3;
4044 END IF;
4045 IF (l_tax_remaining+l_line_remaining = 0) THEN
4046 p_error_num := 2;
4047 FND_MESSAGE.SET_NAME('AR', 'AR_TW_PRORATE_ADJ_OVERAPPLY');
4048 ELSE
4049 -- Bug 2189230
4050 l_prorated_tax := arp_etax_util.tax_curr_round(
4051 (l_tax_remaining*p_adj_amount/
4052 (l_tax_remaining+l_line_remaining)),
4053 l_invoice_currency_code,
4054 l_precision,
4055 l_min_acct_unit,
4056 l_rounding_rule);
4057
4058 END IF;
4059 END IF;
4060 END IF;
4061
4062 IF p_error_num = 0 THEN
4063 p_prorated_amt := p_adj_amount - l_prorated_tax;
4064 p_prorated_tax := l_prorated_tax;
4065 ELSE
4066 p_prorated_amt := 0;
4067 p_prorated_tax := 0;
4068 END IF;
4069
4070 IF PG_DEBUG in ('Y', 'C') THEN
4071 arp_util.debug( 'p_prorated_amt = ' || to_char(p_prorated_amt));
4072 arp_util.debug( 'p_prorated_tax = ' || to_char(p_prorated_tax));
4073 arp_util.debug( 'p_error_num = ' || to_char(p_error_num));
4074 arp_util.debug('arp_process_adjustment.cal_prorated_amounts()-');
4075 END IF;
4076
4077 EXCEPTION
4078 WHEN OTHERS THEN
4079 IF PG_DEBUG in ('Y', 'C') THEN
4080 arp_util.debug(
4081 'EXCEPTION: arp_process_adjustment.cal_prorated_amounts()');
4082 END IF;
4083 FND_MESSAGE.set_name( 'AR', 'GENERIC_MESSAGE' );
4084 FND_MESSAGE.set_token( 'GENERIC_TEXT', 'arp_process_adjustment.cal_prorated_amounts exception: '||SQLERRM );
4085 RAISE;
4086
4087 END cal_prorated_amounts;
4088
4089
4090 /*---------------------------------------------+
4091 | Package initialization section. |
4092 +---------------------------------------------*/
4093
4094
4095 BEGIN
4096
4097 pg_msg_level_debug := arp_global.MSG_LEVEL_DEBUG;
4098 pg_user_id := fnd_global.user_id;
4099 pg_text_dummy := arp_adjustments_pkg.get_text_dummy;
4100 pg_base_curr_code := arp_global.functional_currency;
4101 pg_base_precision := arp_global.base_precision;
4102 pg_base_min_acc_unit := arp_global.base_min_acc_unit;
4103
4104 EXCEPTION
4105 WHEN OTHERS THEN
4106 arp_util.debug('EXCEPTION: arp_process_adjustment.initialization');
4107 RAISE;
4108
4109
4110 END ARP_PROCESS_ADJUSTMENT;