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