[Home] [Help]
PACKAGE BODY: APPS.ARP_BAL_UTIL
Source
1 PACKAGE BODY ARP_BAL_UTIL AS
2 /* $Header: ARTUBALB.pls 120.23.12020000.2 2012/07/24 03:34:12 rravikir ship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION |
7 | get_line_balance |
8 | |
9 | DESCRIPTION |
10 | Gets the uncredited amount of a line. |
11 | |
12 | SCOPE - PUBLIC |
13 | |
14 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
15 | |
16 | ARGUMENTS : IN: |
17 | p_customer_trx_line_id |
18 | p_extended_amount |
19 | p_cm_customer_trx_line_id |
20 | OUT: |
21 | None |
22 | |
23 | RETURNS : NONE |
24 | |
25 | NOTES |
26 | |
27 | MODIFICATION HISTORY |
28 | 19-SEP-95 Charlie Tomberg Created |
29 | 19-FEB-96 Martin Johnson Added parameter |
30 | p_cm_customer_trx_line_id |
31 | |
32 +===========================================================================*/
33
34 FUNCTION get_line_balance( p_customer_trx_line_id IN number,
35 p_extended_amount IN number
36 DEFAULT NULL,
37 p_cm_customer_trx_line_id IN
38 number
39 DEFAULT NULL )
40 RETURN NUMBER IS
41
42 l_net_amount number;
43 l_original_amount number;
44
45 BEGIN
46
47 IF ( p_customer_trx_line_id IS NULL )
48 THEN RETURN( NULL );
49 ELSE
50
51 /*--------------------------------------------------------+
52 | Get the original line amount if it was not passed in |
53 +--------------------------------------------------------*/
54 /*
55 IF ( p_extended_amount IS NULL )
56 THEN
57 SELECT extended_amount
58 INTO l_original_amount
59 FROM ra_customer_trx_lines
60 WHERE customer_trx_line_id = p_customer_trx_line_id;
61 ELSE l_original_amount := p_extended_amount;
62 END IF;
63 */
64
65 SELECT DECODE(line_type, 'LINE',
66 NVL(gross_extended_amount, extended_amount),
67 extended_amount)
68 INTO l_original_amount
69 FROM ra_customer_trx_lines
70 WHERE customer_trx_line_id = p_customer_trx_line_id;
71
72
73 /*-----------------------------------------------------+
76 | If p_cm_customer_trx_line_id is passed, include |
74 | Get the sum of all credit memos against this line |
75 | |
77 | it's extended_amount in the sum even if the CM |
78 | is not complete. |
79 +-----------------------------------------------------*/
80 --2858276, added gross extended amount below
81
82 SELECT l_original_amount +
83 NVL(
84 SUM(
85 DECODE(ct.complete_flag,
86 'N', DECODE(ctl.customer_trx_line_id,
87 p_cm_customer_trx_line_id,
88 nvl(ctl.gross_extended_amount,ctl.extended_amount),
89 0 ),
90 nvl(ctl.gross_extended_amount,ctl.extended_amount)
91 )
92 ), 0
93 )
94 INTO l_net_amount
95 FROM ra_customer_trx ct,
96 ra_customer_trx_lines ctl
97 WHERE ctl.previous_customer_trx_line_id = p_customer_trx_line_id
98 AND ctl.customer_trx_id = ct.customer_trx_id;
99
100 RETURN(l_net_amount);
101
102 END IF;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 RAISE;
107
108 END;
109
110
111 /*===========================================================================+
112 | FUNCTION |
113 | get_line_cm |
114 | |
115 | DESCRIPTION |
116 | Gets the total amount credited against a line |
117 | |
118 | SCOPE - PUBLIC |
119 | |
120 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
121 | |
122 | ARGUMENTS : IN: |
123 | p_prev_customer_trx_line_id |
124 | OUT: |
125 | None |
126 | |
127 | RETURNS : NONE |
128 | |
129 | NOTES |
130 | |
131 | MODIFICATION HISTORY |
132 | 19-SEP-95 Charlie Tomberg Created |
133 | |
134 +===========================================================================*/
135
136 FUNCTION get_line_cm( p_prev_customer_trx_line_id IN Number)
137 RETURN NUMBER IS
138
139 l_credit_amount number;
140
141 BEGIN
142
143 IF ( p_prev_customer_trx_line_id IS NULL )
144 THEN RETURN( null );
145 ELSE
146
147 SELECT NVL(
148 SUM( extended_amount ), 0
149 )
150 INTO l_credit_amount
151 FROM ra_customer_trx_lines
152 WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
153
154 RETURN(l_credit_amount);
155 END IF;
156
157 EXCEPTION
158 WHEN OTHERS THEN
159 RAISE;
160
161 END;
162
163 /*===========================================================================+
164 | FUNCTION |
165 | get_line_cm |
166 | |
167 | DESCRIPTION |
168 | Overridden function for get_line_cm(p_prev_customer_trx_line_id) |
169 | |
170 | SCOPE - PUBLIC |
171 | |
172 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
173 | |
174 | ARGUMENTS : IN: |
175 | p_prev_customer_trx_line_id |
176 | p_return_null_flag |
177 | OUT: |
178 | None |
179 | |
180 | RETURNS : NONE |
181 | |
182 | NOTES |
183 | Created in order to fix Bug-1369715 |
184 | MODIFICATION HISTORY |
185 | 26-MAR-12 Anand Mattikopp Created |
186 | |
187 +===========================================================================*/
188
189 FUNCTION get_line_cm( p_prev_customer_trx_line_id IN Number, p_return_null_flag IN varchar2)
190 RETURN NUMBER IS
191
192 l_credit_amount number;
193
194 BEGIN
195
196 IF ( p_prev_customer_trx_line_id IS NULL )
197 THEN RETURN( null );
198 ELSE
199 IF (UPPER(p_return_null_flag)='Y')
200 THEN
201 SELECT SUM(extended_amount)
202 INTO l_credit_amount
203 FROM ra_customer_trx_lines
204 WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
205 ELSE
206 SELECT NVL(
207 SUM( extended_amount ), 0
208 )
209 INTO l_credit_amount
210 FROM ra_customer_trx_lines
211 WHERE previous_customer_trx_line_id = p_prev_customer_trx_line_id;
212 END IF;
213 RETURN(l_credit_amount);
214 END IF;
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 RAISE;
219
220 END;
221
222 /*===========================================================================+
223 | PROCEDURE |
224 | transaction_balances |
225 | |
226 | DESCRIPTION |
227 | Determines the balances for a transaction |
228 | |
229 | SCOPE - PUBLIC |
230 | |
231 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
232 | |
233 | ARGUMENTS : IN: |
234 | p_customer_trx_id - identifies the transaction |
235 | p_open_receivables_flag |
236 | p_exchange_rate |
237 | p_mode - Can be 'ALL' or 'SUMMARY' |
238 | - All balances are returned in ALL |
239 | mode. Only the Txn. original and |
240 | remaining balances are returned |
241 | in SUMMARY mode. |
242 | |
243 | p_currency_mode - Can be 'E'(ntered) or 'A'(ll) |
244 | - The base currency amounts are only |
245 | calculated and returned in 'A' mode.|
246 | OUT: |
247 | < entered currency balances > |
248 | < base currency balances > |
249 | |
250 | NOTES |
251 | Rounding errors for the base amounts are corrected in this procedure |
252 | by putting the rounding error on the line balances. This may not be |
253 | the same as how the rounding errors are corrected on the actual |
254 | transaction. Therefore, the base line, tax and freight balances may |
255 | not be accurate. The totals are always accurate, however. |
256 | |
257 | MODIFICATION HISTORY |
258 | 31-AUG-95 Charlie Tomberg Created |
259 | 28-MAR-96 Vikas Mahajan l_base_total_credit not getting the |
260 | right value |
261 | 12-JAN-98 Debbie Jancis l_base_total_credit not being converted|
262 | to functional amount. Bug 508560 |
263 | |
264 | 06-OCT-98 Sai Rangarajan Bug Fix 729221 , credit amounts |
265 | get null values when entered currency |
266 | is the functional currency |
267 | 07-Jan-02 Debbie Jancis Fixed Bug 1373449: Separate adjustments|
268 | into adjustments and assignments |
269 | 19-APR-02 Amit Bhati Bug 2319665: This is an extension to |
270 | the fix for bug 2013601. The accounted |
271 | amount adjusted for different type of |
272 | adjustments is now calculated from |
273 | total accounted amount adjusted under |
274 | some condition. |
275 +===========================================================================*/
276
277
278 PROCEDURE transaction_balances(
279 p_customer_trx_id IN Number,
280 p_open_receivables_flag IN Varchar2,
281 p_exchange_rate IN Number,
282 p_mode IN VARCHAR2,
283 p_currency_mode IN VARCHAR2,
284 p_line_original OUT NOCOPY NUMBER,
285 p_line_remaining OUT NOCOPY NUMBER,
286 p_tax_original OUT NOCOPY NUMBER,
287 p_tax_remaining OUT NOCOPY NUMBER,
288 p_freight_original OUT NOCOPY NUMBER,
289 p_freight_remaining OUT NOCOPY NUMBER,
290 p_charges_original OUT NOCOPY NUMBER,
291 p_charges_remaining OUT NOCOPY NUMBER,
292 p_line_discount OUT NOCOPY NUMBER,
293 p_tax_discount OUT NOCOPY NUMBER,
294 p_freight_discount OUT NOCOPY NUMBER,
295 p_charges_discount OUT NOCOPY NUMBER,
296 p_total_discount OUT NOCOPY NUMBER,
297 p_total_original OUT NOCOPY NUMBER,
298 p_total_remaining OUT NOCOPY NUMBER,
299 p_line_receipts OUT NOCOPY NUMBER,
300 p_tax_receipts OUT NOCOPY NUMBER,
301 p_freight_receipts OUT NOCOPY NUMBER,
302 p_charges_receipts OUT NOCOPY NUMBER,
303 p_total_receipts OUT NOCOPY NUMBER,
304 p_line_credits OUT NOCOPY NUMBER,
305 p_tax_credits OUT NOCOPY NUMBER,
306 p_freight_credits OUT NOCOPY NUMBER,
307 p_total_credits OUT NOCOPY NUMBER,
308 p_line_adjustments OUT NOCOPY NUMBER,
309 p_tax_adjustments OUT NOCOPY NUMBER,
310 p_freight_adjustments OUT NOCOPY NUMBER,
311 p_charges_adjustments OUT NOCOPY NUMBER,
312 p_total_adjustments OUT NOCOPY NUMBER,
313 p_aline_adjustments OUT NOCOPY NUMBER,
314 p_atax_adjustments OUT NOCOPY NUMBER,
315 p_afreight_adjustments OUT NOCOPY NUMBER,
316 p_acharges_adjustments OUT NOCOPY NUMBER,
317 p_atotal_adjustments OUT NOCOPY NUMBER,
318 p_base_line_original OUT NOCOPY NUMBER,
319 p_base_line_remaining OUT NOCOPY NUMBER,
320 p_base_tax_original OUT NOCOPY NUMBER,
321 p_base_tax_remaining OUT NOCOPY NUMBER,
322 p_base_freight_original OUT NOCOPY NUMBER,
323 p_base_freight_remaining OUT NOCOPY NUMBER,
324 p_base_charges_original OUT NOCOPY NUMBER,
325 p_base_charges_remaining OUT NOCOPY NUMBER,
326 p_base_line_discount OUT NOCOPY NUMBER,
327 p_base_tax_discount OUT NOCOPY NUMBER,
328 p_base_freight_discount OUT NOCOPY NUMBER,
329 p_base_total_discount OUT NOCOPY NUMBER,
330 p_base_total_original OUT NOCOPY NUMBER,
331 p_base_total_remaining OUT NOCOPY NUMBER,
332 p_base_line_receipts OUT NOCOPY NUMBER,
333 p_base_tax_receipts OUT NOCOPY NUMBER,
334 p_base_freight_receipts OUT NOCOPY NUMBER,
335 p_base_charges_receipts OUT NOCOPY NUMBER,
336 p_base_total_receipts OUT NOCOPY NUMBER,
337 p_base_line_credits OUT NOCOPY NUMBER,
338 p_base_tax_credits OUT NOCOPY NUMBER,
339 p_base_freight_credits OUT NOCOPY NUMBER,
340 p_base_total_credits OUT NOCOPY NUMBER,
341 p_base_line_adjustments OUT NOCOPY NUMBER,
342 p_base_tax_adjustments OUT NOCOPY NUMBER,
343 p_base_freight_adjustments OUT NOCOPY NUMBER,
344 p_base_charges_adjustments OUT NOCOPY NUMBER,
345 p_base_total_adjustments OUT NOCOPY NUMBER,
346 p_base_aline_adjustments OUT NOCOPY NUMBER,
347 p_base_atax_adjustments OUT NOCOPY NUMBER,
348 p_base_afreight_adjustments OUT NOCOPY NUMBER,
349 p_base_acharges_adjustments OUT NOCOPY NUMBER,
350 p_base_atotal_adjustments OUT NOCOPY NUMBER
351 ) IS
352
353 l_open_receivables_flag ra_cust_trx_types.accounting_affect_flag%type;
354 l_exchange_rate ra_customer_trx.exchange_rate%type;
355 l_base_curr_code fnd_currencies.currency_code%type;
356 l_base_precision fnd_currencies.precision%type;
357 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
358
359 l_line_original NUMBER;
360 l_line_remaining NUMBER;
361 l_tax_original NUMBER;
362 l_tax_remaining NUMBER;
363 l_freight_original NUMBER;
364 l_freight_remaining NUMBER;
365 l_charges_original NUMBER;
366 l_charges_remaining NUMBER;
367 l_line_discount NUMBER;
368 l_tax_discount NUMBER;
369 l_freight_discount NUMBER;
370 l_charges_discount NUMBER;
371 l_total_discount NUMBER;
372 l_total_original NUMBER;
373 l_total_remaining NUMBER;
374 l_line_receipts NUMBER;
375 l_tax_receipts NUMBER;
376 l_freight_receipts NUMBER;
377 l_charges_receipts NUMBER;
378 l_total_receipts NUMBER;
379 l_line_edreceipts NUMBER;
380 l_line_uedreceipts NUMBER;
381 l_tax_edreceipts NUMBER;
382 l_tax_uedreceipts NUMBER;
383 l_freight_edreceipts NUMBER;
384 l_freight_uedreceipts NUMBER;
385 l_charges_edreceipts NUMBER;
386 l_charges_uedreceipts NUMBER;
387 l_line_credits NUMBER;
388 l_tax_credits NUMBER;
389 l_freight_credits NUMBER;
390 l_total_credits NUMBER;
391 l_line_adjustments NUMBER;
392 l_tax_adjustments NUMBER;
393 l_freight_adjustments NUMBER;
394 l_charges_adjustments NUMBER;
395 l_total_adjustments NUMBER;
396
397 /* added for Bug 1373449 */
398 l_aline_adjustments NUMBER; /* ASSIGNMENT ADJUSTMENTS */
399 l_atax_adjustments NUMBER;
400 l_afreight_adjustments NUMBER;
401 l_acharges_adjustments NUMBER;
402 l_atotal_adjustments NUMBER;
403
404 l_base_line_original NUMBER;
408 l_base_freight_original NUMBER;
405 l_base_line_remaining NUMBER;
406 l_base_tax_original NUMBER;
407 l_base_tax_remaining NUMBER;
409 l_base_freight_remaining NUMBER;
410 l_base_charges_original NUMBER;
411 l_base_charges_remaining NUMBER;
412 l_base_line_discount NUMBER;
413 l_base_tax_discount NUMBER;
414 l_base_freight_discount NUMBER;
415 l_base_charges_discount NUMBER;
416 l_base_total_discount NUMBER;
417 l_base_total_original NUMBER;
418 l_base_total_remaining NUMBER;
419 l_base_line_receipts NUMBER;
420 l_base_tax_receipts NUMBER;
421 l_base_freight_receipts NUMBER;
422 l_base_charges_receipts NUMBER;
423 l_base_total_receipts NUMBER;
424 l_base_line_credits NUMBER;
425 l_base_tax_credits NUMBER;
426 l_base_freight_credits NUMBER;
427 l_base_total_credits NUMBER;
428 l_base_line_adjustments NUMBER;
429 l_base_tax_adjustments NUMBER;
430 l_base_freight_adjustments NUMBER;
431 l_base_charges_adjustments NUMBER;
432 l_base_total_adjustments NUMBER;
433
434 /* added for Bug 1373449 */
435 l_base_aline_adjustments NUMBER; /* BASE ASSIGNMENTS (ADJ) */
436 l_base_atax_adjustments NUMBER;
437 l_base_afreight_adjustments NUMBER;
438 l_base_acharges_adjustments NUMBER;
439 l_base_atotal_adjustments NUMBER;
440
441 -- Bug 931292
442 l_base_total_credits1 NUMBER;
443 l_base_total_credits2 NUMBER;
444 l_line_credits1 NUMBER;
445 l_tax_credits1 NUMBER;
446 l_freight_credits1 NUMBER;
447 l_line_credits2 NUMBER;
448 l_tax_credits2 NUMBER;
449 l_freight_credits2 NUMBER;
450 l_line_act_credits NUMBER;
451 l_tax_act_credits NUMBER;
452 l_freight_act_credits NUMBER;
453
454 l_trx_type ra_cust_trx_types.type%TYPE; /* 07-AUG-2000 J Rautiainen BR Implementation */
455 /*Bug 2319665*/
456 l_run_adj_tot NUMBER;
457 l_base_run_adj_tot NUMBER;
458
459 /*3374248*/
460 l_new_line_acctd_amt NUMBER;
461 l_new_frt_acctd_amt NUMBER;
462 l_new_chrg_acctd_amt NUMBER;
463 l_new_tax_acctd_amt NUMBER;
464 l_cm_refunds NUMBER;
465 /*9453136*/
466 l_previous_customer_trx_id NUMBER;
467 BEGIN
468 arp_standard.debug('ARP_BAL_UTIL.Transaction_Balances (+)');
469 /*---------------------------------------------------+
470 | Get the Open Receivable Flag and Exchange Rate |
471 | if either was not provided. |
472 +---------------------------------------------------*/
473
474 IF ( p_open_receivables_flag IS NULL OR
475 (
476 p_exchange_rate IS NULL AND
477 p_currency_mode <> 'E'
478 )
479 )
480 THEN
481 /* 07-AUG-2000 J Rautiainen BR Implementation
482 * Need to know the transaction type since
483 * the accounting is stored in ar_distributions
484 * instead of ra_cust_trx_line_gl_dist for BR */
485 SELECT ctt.accounting_affect_flag,
486 ct.exchange_rate,
487 ctt.type
488 INTO l_open_receivables_flag,
489 l_exchange_rate,
490 l_trx_type
491 FROM ra_cust_trx_types ctt,
492 ra_customer_trx ct
493 WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
494 AND ct.customer_trx_id = p_customer_trx_id;
495 ELSE
496 /* 07-AUG-2000 J Rautiainen BR Implementation
497 * Need to know the transaction type since
498 * the accounting is stored in ar_distributions
499 * instead of ra_cust_trx_line_gl_dist for BR */
500 SELECT ctt.type
501 INTO l_trx_type
502 FROM ra_cust_trx_types ctt,
503 ra_customer_trx ct
504 WHERE ct.cust_trx_type_id = ctt.cust_trx_type_id
505 AND ct.customer_trx_id = p_customer_trx_id;
506
507 l_open_receivables_flag := p_open_receivables_flag;
508 l_exchange_rate := p_exchange_rate;
509 END IF;
510
511 /*-------------------------------------------------------+
512 | Get the base currency and exchange rate information |
513 +-------------------------------------------------------*/
514 --bug7025523
515 SELECT sob.currency_code,
516 precision,
517 minimum_accountable_unit
518 INTO l_base_curr_code,
519 l_base_precision,
520 l_base_min_acc_unit
521 FROM fnd_currencies fc,
522 gl_sets_of_books sob
523 WHERE sob.set_of_books_id = arp_global.set_of_books_id
524 AND sob.currency_code = fc.currency_code;
525
526 /*-----------------------------------------+
527 | Get the credit memo accounted amount |
528 +-----------------------------------------*/
529
530 IF ( p_currency_mode <> 'E' ) AND
531 (
532 p_mode = 'ALL' OR
533 p_open_receivables_flag = 'N' -- needed to calc balance
534 )
535 THEN
536 IF ( nvl(l_exchange_rate, 1) <> 1 )
537 /* 08-AUG-2000 J Rautiainen BR Implementation */
538 AND (NVL(l_trx_type,'INV') <> 'BR')
539 THEN
540 -- for regular CM applied on the invoice
541 /* bug2324069 added nvl */
542 SELECT nvl(SUM( acctd_amount ),0)
543 INTO l_base_total_credits1
544 FROM ra_cust_trx_line_gl_dist lgd,
545 ra_customer_trx ct
546 WHERE ct.customer_trx_id = lgd.customer_trx_id
547 AND lgd.account_class = 'REC'
548 AND lgd.latest_rec_flag = 'Y'
549 AND ct.previous_customer_trx_id = p_customer_trx_id;
550
551 -- get the on-account credit applied on the transaction
552 -- Fix for bug 931292
553 select nvl(sum(rec.acctd_amount_applied_from),0)*(-1)
554 into l_base_total_credits2
555 from ar_receivable_applications rec,
556 ra_customer_trx trx
557 where rec.applied_customer_trx_id = p_customer_trx_id
558 and rec.customer_trx_id = trx.customer_trx_id
559 and rec.status = 'APP'
560 and rec.application_type = 'CM'
561 and trx.previous_customer_trx_id is null;
562
563 l_base_total_credits := NVL(l_base_total_credits1,0) +
564 NVL(l_base_total_credits2,0);
565 END IF;
566
567 END IF;
568
569 /*------------------------------------------------------------------+
570 | IF the transaction is Open Receivable = Yes, |
571 | THEN get the transaction balances from the payment schedules |
572 | ELSE get the original and uncredited amounts from the lines |
573 +------------------------------------------------------------------*/
574
575 IF ( l_open_receivables_flag = 'Y' )
576 THEN
577 SELECT SUM( NVL( amount_line_items_original, 0 ) ),
578 SUM( NVL( amount_line_items_remaining, 0 ) ),
579 SUM( NVL( tax_original, 0 ) ),
580 SUM( NVL( tax_remaining, 0 ) ),
581 SUM( NVL( freight_original, 0 ) ),
582 SUM( NVL( freight_remaining, 0 ) ),
583 SUM( NVL( receivables_charges_charged, 0 ) ),
584 SUM( NVL( receivables_charges_remaining, 0 ) ),
585 SUM( NVL( amount_due_original, 0 ) ),
586 SUM( NVL( amount_due_remaining, 0 ) ),
587 DECODE(
588 p_currency_mode,
589 'E', null,
590 SUM( NVL( acctd_amount_due_remaining, 0 ) )
591 ),
592 DECODE(
593 p_mode,
594 'ALL', SUM( NVL( amount_applied, 0 ) ),
595 null
596 ),
597 DECODE(
598 p_mode,
599 'ALL', SUM( NVL( amount_credited, 0 ) ),
600 null
601 )
602 -- DECODE(
603 -- p_mode,
604 -- 'ALL', SUM( NVL( amount_adjusted, 0 ) ),
605 -- null
606 -- )
607 INTO
608 l_line_original,
609 l_line_remaining,
610 l_tax_original,
611 l_tax_remaining,
612 l_freight_original,
613 l_freight_remaining,
614 l_charges_original,
615 l_charges_remaining,
616 l_total_original,
617 l_total_remaining,
618 l_base_total_remaining,
619 l_total_receipts,
620 l_total_credits
621 -- l_total_adjustments
622 FROM ar_payment_schedules ps
623 WHERE ps.customer_trx_id = p_customer_trx_id;
624
625 /* 08-AUG-2000 J Rautiainen BR Implementation
626 * Bills Receivable transaction does not have accounting in ra_cust_trx_gl_dist table */
627
628 IF (NVL(l_trx_type,'INV') = 'BR') THEN
629
630 SELECT DECODE(
631 p_currency_mode,
632 'E', null,
633 MAX( dist.acctd_amount_dr )
634 )
635 INTO l_base_total_original
636 FROM ar_transaction_history trh,
637 ar_distributions dist
638 WHERE trh.customer_trx_id = p_customer_trx_id
639 AND trh.first_posted_record_flag = 'Y'
640 AND dist.source_id = trh.transaction_history_id
641 AND dist.source_table = 'TH'
642 AND dist.source_type = 'REC'
643 AND dist.source_type_secondary IS NULL
644 AND dist.source_id_secondary IS NULL
645 AND dist.source_table_secondary IS NULL;
646
647 ELSE
648
649 SELECT DECODE(
650 p_currency_mode,
651 'E', null,
652 MAX( lgd.acctd_amount )
653 )
654 INTO l_base_total_original
655 FROM ra_cust_trx_line_gl_dist lgd
656 WHERE lgd.customer_trx_id = p_customer_trx_id
657 AND lgd.account_class = 'REC'
658 AND lgd.latest_rec_flag = 'Y';
659
660 END IF;
661
662 /*---------------------------------------------------+
663 | If all amounts are required, |
664 | get the receipt, credit and adjustment amounts |
665 +---------------------------------------------------*/
666
667 IF ( p_mode = 'ALL' )
668 THEN
669
670 /*-------------------------------+
671 | Determine the credit amounts |
672 +-------------------------------*/
673
674 /* 08-AUG-2000 J Rautiainen BR Implementation
675 * No impact for BR since no credit memoes exist against BR */
676 SELECT SUM(
677 DECODE(
678 ct.complete_flag,
679 'N', 0,
680 DECODE(
681 ctl.line_type,
682 'TAX', 0,
683 'FREIGHT', 0,
684 ctl.extended_amount
685 )
686 )
687 ), -- line_credited
688 SUM(
689 DECODE(
690 ct.complete_flag,
691 'N', 0,
692 DECODE(
693 ctl.line_type,
694 'TAX', ctl.extended_amount,
695 0
696 )
697 )
698 ), -- tax_credited
699 SUM(
700 DECODE(
701 ct.complete_flag,
702 'N', 0,
703 DECODE(
704 ctl.line_type,
705 'FREIGHT', ctl.extended_amount,
706 0
707 )
708 )
709 ) -- freight_credited
710 INTO l_line_credits1,
711 l_tax_credits1,
712 l_freight_credits1
713 FROM ra_customer_trx_lines ctl,
714 ra_cust_trx_line_gl_dist rec,
715 ra_customer_trx ct
716 WHERE ct.customer_trx_id = ctl.customer_trx_id
717 AND ct.customer_trx_id = rec.customer_trx_id
718 AND rec.account_class = 'REC'
719 AND rec.latest_rec_flag = 'Y'
720 AND ct.previous_customer_trx_id = p_customer_trx_id;
721
722 -- get the on-account credit applied on the transaction
723 -- Fix for bug 931292
724 select nvl(sum(rec.line_applied),0)*(-1),
725 nvl(sum(rec.tax_applied),0)*(-1),
726 nvl(sum(rec.freight_applied),0)*(-1)
727 into l_line_credits2,
728 l_tax_credits2,
729 l_freight_credits2
730 from ar_receivable_applications rec,
731 ra_customer_trx trx
732 where rec.applied_customer_trx_id = p_customer_trx_id
733 and rec.customer_trx_id = trx.customer_trx_id
734 and rec.status = 'APP'
735 and rec.application_type = 'CM'
736 and trx.previous_customer_trx_id is null;
737
738 /* Bug 4112494 CM refund total */
739 SELECT NVL(SUM(DECODE(rec.line_applied,null,rec.amount_applied,0)),0),
740 NVL(SUM(NVL(rec.line_applied,rec.amount_applied)),0),
741 NVL(SUM(rec.tax_applied),0),
742 NVL(SUM(rec.freight_applied),0)
743 into l_cm_refunds,
744 l_line_act_credits,
745 l_tax_act_credits,
746 l_freight_act_credits
747 FROM ar_receivable_applications rec
748 WHERE rec.customer_trx_id = p_customer_trx_id
749 AND rec.status = 'ACTIVITY';
750
751
752 l_line_credits := NVL(l_line_credits1,0) +
753 NVL(l_line_credits2,0) +
754 NVL(l_line_act_credits,0);
755
756 l_tax_credits := NVL(l_tax_credits1,0) +
757 NVL(l_tax_credits2,0) +
758 NVL(l_tax_act_credits,0);
759
760 l_freight_credits := NVL(l_freight_credits1,0) +
761 NVL(l_freight_credits2,0) +
762 NVL(l_freight_act_credits,0);
763
764 /*-----------------------------------+
765 | Determine the adjustment amounts |
766 +-----------------------------------*/
767
768 /* Bug 1373449: don't include assignments in amount */
769 SELECT SUM( NVL( line_adjusted, 0) ),
770 SUM( NVL( tax_adjusted, 0) ),
771 SUM( NVL( freight_adjusted, 0) ),
772 SUM( NVL( receivables_charges_adjusted, 0) ),
773 DECODE(
774 p_currency_mode,
775 'E', null,
776 SUM( acctd_amount )
777 ),
778 SUM(NVL(amount,0))
779 /*3374248*/
780 ,SUM(DECODE(type,'LINE',NVL(acctd_amount,0),0))
781 ,SUM(DECODE(type,'FREIGHT',NVL(acctd_amount,0),0))
782 ,SUM(DECODE(type,'CHARGES',NVL(acctd_amount,0),0))
783 ,SUM(DECODE(type,'TAX',NVL(acctd_amount,0),0))
784 INTO l_line_adjustments,
785 l_tax_adjustments,
786 l_freight_adjustments,
787 l_charges_adjustments,
788 l_base_total_adjustments,
789 l_total_adjustments
790 /*3374248*/
791 ,l_new_line_acctd_amt
792 ,l_new_frt_acctd_amt
793 ,l_new_chrg_acctd_amt
794 ,l_new_tax_acctd_amt
795 FROM ar_adjustments
796 WHERE customer_trx_id = p_customer_trx_id
797 AND status = 'A'
798 AND receivables_trx_id <> -15;
799
800 /*-----------------------------------+
801 | Bug 1373449: |
802 | Determine the assignment amounts |
803 +-----------------------------------*/
804
805 SELECT SUM( NVL( line_adjusted, 0) ),
806 SUM( NVL( tax_adjusted, 0) ),
807 SUM( NVL( freight_adjusted, 0) ),
808 SUM( NVL( receivables_charges_adjusted, 0) ),
809 DECODE(
810 p_currency_mode,
811 'E', null,
812 SUM( acctd_amount )
813 ),
814 SUM(NVL(amount,0))
815 INTO l_aline_adjustments,
816 l_atax_adjustments,
817 l_afreight_adjustments,
818 l_acharges_adjustments,
819 l_base_atotal_adjustments,
820 l_atotal_adjustments
821 FROM ar_adjustments
822 WHERE customer_trx_id = p_customer_trx_id
823 AND status = 'A'
824 AND receivables_trx_id = -15;
825
826
827 /*--------------------------------+
828 | Determine the receipt amounts |
829 +--------------------------------*/
830
831 SELECT SUM( NVL( line_applied, 0 )),
832 SUM( NVL( tax_applied, 0 )),
833 SUM( NVL( freight_applied, 0 )),
834 SUM( NVL( receivables_charges_applied, 0 )),
835 SUM( NVL( amount_applied, 0 )),
836 SUM( NVL( line_ediscounted, 0)),
837 SUM( NVL( line_uediscounted, 0)),
838 SUM( NVL( tax_ediscounted, 0)),
839 SUM( NVL( tax_uediscounted, 0)),
840 SUM( NVL( freight_ediscounted, 0)),
841 SUM( NVL( freight_uediscounted, 0)),
842 SUM( NVL( charges_ediscounted, 0)),
843 SUM( NVL( charges_uediscounted, 0)),
844 DECODE(
845 p_currency_mode,
846 'E', null,
847 SUM( NVL( acctd_amount_applied_to, 0 ))
848 ),
849 SUM(
850 NVL( earned_discount_taken, 0) +
851 NVL( unearned_discount_taken, 0 )
852 ),
853 DECODE(
854 p_currency_mode,
855 'E', null,
856 SUM(
857 NVL( acctd_earned_discount_taken, 0) +
858 NVL( acctd_unearned_discount_taken, 0 )
859 )
860 )
861 INTO l_line_receipts,
862 l_tax_receipts,
863 l_freight_receipts,
864 l_charges_receipts,
865 l_total_receipts,
866 l_line_edreceipts,
867 l_line_uedreceipts,
868 l_tax_edreceipts,
869 l_tax_uedreceipts,
870 l_freight_edreceipts,
871 l_freight_uedreceipts,
872 l_charges_edreceipts,
873 l_charges_uedreceipts,
874 l_base_total_receipts,
875 l_total_discount,
876 l_base_total_discount
877 FROM ar_receivable_applications
878 WHERE applied_customer_trx_id = p_customer_trx_id
879 AND application_type = 'CASH'
880 AND NVL( confirmed_flag, 'Y' ) = 'Y';
881
882 END IF; -- End ALL mode
883
884
885 ELSE -- Open Receivables No case
886 /* 08-AUG-2000 J Rautiainen BR Implementation
887 * No impact for BR since the open receivable flag is always Y BR */
888 SELECT SUM(
889 DECODE(
890 ct.complete_flag,
891 'N', 0,
892 DECODE( -- only use the original lines
893 ctl.customer_trx_line_id,
894 orig_ctl.customer_trx_line_id,
895 orig_ctl.extended_amount,
896 0
897 )
898 )
899 ), -- total original
900 SUM(
901 DECODE(
902 ct.complete_flag,
903 'N', 0,
904 ctl.extended_amount
905 )
906 ), -- total remaining
907 SUM(
908 DECODE( -- only use LINE, CHARGES + CB lines
909 ctl.line_type,
910 'TAX', 0,
911 'FREIGHT', 0,
912 1
913 ) *
914 DECODE(
915 ct.complete_flag,
916 'N', 0,
917 DECODE(
918 ctl.customer_trx_line_id,
919 orig_ctl.customer_trx_line_id,
920 orig_ctl.extended_amount,
921 0
922 )
923 )
924 ), -- line original
925 SUM(
926 DECODE(
927 ctl.line_type,
928 'TAX', 0,
929 'FREIGHT', 0,
930 1
931 ) *
932 DECODE(
933 ct.complete_flag,
934 'N', 0,
935 ctl.extended_amount
936 )
937 ), -- line remaining
938 SUM(
939 DECODE( -- only use TAX lines
940 ctl.line_type,
941 'TAX', 1,
942 0
943 ) *
944 DECODE(
945 ct.complete_flag,
946 'N', 0,
947 DECODE(
948 ctl.customer_trx_line_id,
949 orig_ctl.customer_trx_line_id,
950 orig_ctl.extended_amount,
951 0
952 )
953 )
954 ), -- tax original
955 SUM(
956 DECODE(
957 ctl.line_type,
958 'TAX', 1,
959 0
960 ) *
961 DECODE(
962 ct.complete_flag,
963 'N', 0,
964 ctl.extended_amount
965 )
966 ), -- tax remaining
967 SUM(
968 DECODE( -- only use FREIGHT lines
969 ctl.line_type,
970 'FREIGHT', 1,
971 0
972 ) *
973 DECODE(
974 ct.complete_flag,
975 'N', 0,
976 DECODE(
977 ctl.customer_trx_line_id,
978 orig_ctl.customer_trx_line_id,
979 orig_ctl.extended_amount,
980 0
981 )
982 )
983 ), -- freight original
984 SUM(
985 DECODE(
986 ctl.line_type,
987 'FREIGHT', 1,
988 0
989 ) *
990 DECODE(
991 ct.complete_flag,
992 'N', 0,
993 ctl.extended_amount
994 )
995 ), -- freight remaining
996 SUM(
997 DECODE( -- Only get credits in ALL mode
998 p_mode, 'ALL',
999 1,
1000 null
1001 ) *
1002 DECODE( -- only use LINE, CHARGES + CB lines
1003 ctl.line_type,
1004 'TAX', 0,
1005 'FREIGHT', 0,
1006 1
1007 ) *
1008 DECODE(
1009 ct.complete_flag,
1010 'N', 0,
1011 DECODE(
1012 ctl.customer_trx_line_id,
1013 orig_ctl.customer_trx_line_id,
1014 0,
1015 ctl.extended_amount
1016 )
1017 )
1018 ), -- line credits
1019 SUM(
1020 DECODE( -- Only get credits in ALL mode
1021 p_mode, 'ALL',
1022 1,
1023 null
1024 ) *
1025 DECODE( -- only use TAX lines
1026 ctl.line_type,
1027 'TAX', 1,
1028 0
1029 ) *
1030 DECODE(
1031 ct.complete_flag,
1032 'N', 0,
1033 DECODE(
1034 ctl.customer_trx_line_id,
1035 orig_ctl.customer_trx_line_id,
1036 0,
1037 ctl.extended_amount
1038 )
1039 )
1040 ), -- tax credits
1041 SUM(
1042 DECODE( -- Only get credits in ALL mode
1043 p_mode, 'ALL',
1044 1,
1045 null
1046 ) *
1047 DECODE( -- only use FREIGHT lines
1048 ctl.line_type,
1049 'FREIGHT', 1,
1050 0
1051 ) *
1052 DECODE(
1053 ct.complete_flag,
1054 'N', 0,
1055 DECODE(
1056 ctl.customer_trx_line_id,
1057 orig_ctl.customer_trx_line_id,
1058 0,
1059 ctl.extended_amount
1060 )
1061 )
1062 ), -- freight credits
1063 SUM(
1064 DECODE( -- Only get credits in ALL mode
1065 p_mode, 'ALL',
1066 1,
1067 null
1068 ) *
1069 DECODE(
1070 ct.complete_flag,
1071 'N', 0,
1072 DECODE( -- only use the credit lines
1073 ctl.customer_trx_line_id,
1074 orig_ctl.customer_trx_line_id,
1075 0,
1076 ctl.extended_amount
1077 )
1078 )
1079 ), -- total credits
1080 DECODE(
1081 p_currency_mode,
1082 'E', null,
1083 max( lgd.acctd_amount )
1084 ) -- total base amount
1085 INTO l_total_original,
1086 l_total_remaining,
1087 l_line_original,
1088 l_line_remaining,
1089 l_tax_original,
1090 l_tax_remaining,
1091 l_freight_original,
1092 l_freight_remaining,
1093 l_line_credits,
1094 l_tax_credits,
1095 l_freight_credits,
1096 l_total_credits,
1097 l_base_total_original
1098 FROM ra_cust_trx_line_gl_dist lgd,
1099 ra_customer_trx_lines orig_ctl,
1100 ra_customer_trx_lines ctl,
1101 ra_customer_trx ct
1102 WHERE (
1103 ctl.customer_trx_line_id = orig_ctl.customer_trx_line_id
1104 OR
1105 ctl.previous_customer_trx_line_id
1106 = orig_ctl.customer_trx_line_id
1107 )
1108 AND ctl.customer_trx_id = ct.customer_trx_id
1109 AND orig_ctl.customer_trx_id = lgd.customer_trx_id
1110 AND lgd.account_class = 'REC'
1111 AND lgd.latest_rec_flag = 'Y'
1112 AND orig_ctl.customer_trx_id = p_customer_trx_id;
1113
1114 /* bug2324069 added nvl */
1115 l_base_total_remaining := nvl(l_base_total_original,0) +
1116 nvl(l_base_total_credits,0);
1117
1118 /* bug 9453136 , set the balance of regular CM to 0, since it will always be 0 */
1119 if NVL(l_trx_type,'INV')='CM' THEN
1120 select nvl(PREVIOUS_CUSTOMER_TRX_ID,0) into l_previous_customer_trx_id from ra_customer_trx where customer_trx_id=p_customer_trx_id;
1121 IF l_previous_customer_trx_id <> 0 THEN
1122 l_total_remaining:=0;
1123 l_line_remaining:=0;
1124 l_tax_remaining:=0;
1125 l_freight_remaining:=0;
1126 l_tax_remaining:=0;
1127 END IF;
1128 END IF;
1129 /*End bug 9453136 */
1130
1131 END IF;
1132
1133 -- l_base_total_credits should remain as entered currency only if
1134 -- we are not switching to functional currency. Bug 508560
1135 /* if ( p_currency_mode <> 'A') then
1136 l_base_total_credits := l_total_credits;
1137 end if; */
1138
1139 /* Bug Fix 729221 - Functional Credit amounts disappear when the transaction
1140 entered currency is the same as base currency ,
1141 commented out NOCOPY check for p_currency_mode above (for bug fix 508560)
1142 should be checking for exchange rate instead */
1143
1144 If ( nvl(l_exchange_rate, 1) = 1 ) then
1145 l_base_total_credits := l_total_credits;
1146 end if;
1147
1148 /*----------------------------------------------------+
1149 | Convert the entered amounts to the base currency |
1150 | if the base currency amounts are required |
1151 +----------------------------------------------------*/
1152
1153 IF ( p_currency_mode <> 'E' )
1154 THEN
1155
1156 IF ( l_line_original IS NOT NULL )
1157 THEN l_base_line_original := arpcurr.functional_amount(
1158 l_line_original,
1159 l_base_curr_code,
1160 l_exchange_rate,
1161 l_base_precision,
1162 l_base_min_acc_unit);
1163
1164 END IF;
1165
1166 /* Bug 4112494 add CM refunds to line balance */
1167 /* Bug 5877375 added NVL to l_cm_refunds */
1168 l_line_remaining := l_line_remaining + NVL(l_cm_refunds,0);
1169
1170 IF ( l_line_remaining IS NOT NULL )
1171 THEN l_base_line_remaining := arpcurr.functional_amount(
1172 l_line_remaining,
1173 l_base_curr_code,
1174 l_exchange_rate,
1175 l_base_precision,
1176 l_base_min_acc_unit);
1177 END IF;
1178
1179
1180 IF ( l_tax_original IS NOT NULL )
1181 THEN l_base_tax_original := arpcurr.functional_amount(
1182 l_tax_original,
1183 l_base_curr_code,
1184 l_exchange_rate,
1185 l_base_precision,
1186 l_base_min_acc_unit);
1187 END IF;
1188
1189 IF ( l_tax_remaining IS NOT NULL )
1190 THEN l_base_tax_remaining := arpcurr.functional_amount(
1191 l_tax_remaining,
1192 l_base_curr_code,
1193 l_exchange_rate,
1194 l_base_precision,
1195 l_base_min_acc_unit);
1196 END IF;
1197
1198 IF ( l_freight_original IS NOT NULL )
1199 THEN l_base_freight_original := arpcurr.functional_amount(
1200 l_freight_original,
1201 l_base_curr_code,
1202 l_exchange_rate,
1203 l_base_precision,
1204 l_base_min_acc_unit);
1205 END IF;
1206
1207 IF ( l_freight_remaining IS NOT NULL )
1208 THEN l_base_freight_remaining := arpcurr.functional_amount(
1209 l_freight_remaining,
1210 l_base_curr_code,
1211 l_exchange_rate,
1212 l_base_precision,
1213 l_base_min_acc_unit);
1214 END IF;
1215
1216 IF ( l_charges_original IS NOT NULL )
1217 THEN l_base_charges_original := arpcurr.functional_amount(
1218 l_charges_original,
1219 l_base_curr_code,
1220 l_exchange_rate,
1221 l_base_precision,
1222 l_base_min_acc_unit);
1223 END IF;
1224
1225 IF ( l_charges_remaining IS NOT NULL )
1226 THEN l_base_charges_remaining := arpcurr.functional_amount(
1227 l_charges_remaining,
1228 l_base_curr_code,
1229 l_exchange_rate,
1230 l_base_precision,
1231 l_base_min_acc_unit);
1232 END IF;
1233
1234
1235 IF ( l_line_receipts IS NOT NULL )
1236 THEN l_base_line_receipts := arpcurr.functional_amount(
1237 l_line_receipts,
1238 l_base_curr_code,
1239 l_exchange_rate,
1240 l_base_precision,
1241 l_base_min_acc_unit);
1242 END IF;
1243
1244 IF ( l_tax_receipts IS NOT NULL )
1245 THEN l_base_tax_receipts := arpcurr.functional_amount(
1246 l_tax_receipts,
1247 l_base_curr_code,
1248 l_exchange_rate,
1249 l_base_precision,
1250 l_base_min_acc_unit);
1251 END IF;
1252
1253 IF ( l_freight_receipts IS NOT NULL )
1254 THEN l_base_freight_receipts := arpcurr.functional_amount(
1255 l_freight_receipts,
1256 l_base_curr_code,
1257 l_exchange_rate,
1258 l_base_precision,
1259 l_base_min_acc_unit);
1260 END IF;
1261
1262 IF ( l_charges_receipts IS NOT NULL )
1263 THEN l_base_charges_receipts := arpcurr.functional_amount(
1264 l_charges_receipts,
1265 l_base_curr_code,
1266 l_exchange_rate,
1267 l_base_precision,
1268 l_base_min_acc_unit);
1269 END IF;
1270
1271
1272 IF ( l_line_credits IS NOT NULL )
1273 THEN l_base_line_credits := arpcurr.functional_amount(
1274 l_line_credits,
1275 l_base_curr_code,
1276 l_exchange_rate,
1277 l_base_precision,
1278 l_base_min_acc_unit);
1279 END IF;
1280
1281 IF ( l_tax_credits IS NOT NULL )
1282 THEN l_base_tax_credits := arpcurr.functional_amount(
1283 l_tax_credits,
1284 l_base_curr_code,
1285 l_exchange_rate,
1286 l_base_precision,
1287 l_base_min_acc_unit);
1288 END IF;
1289
1290 IF ( l_freight_credits IS NOT NULL )
1291 THEN l_base_freight_credits := arpcurr.functional_amount(
1292 l_freight_credits,
1293 l_base_curr_code,
1294 l_exchange_rate,
1295 l_base_precision,
1296 l_base_min_acc_unit);
1297 END IF;
1298
1299 IF ( l_line_adjustments IS NOT NULL )
1300 THEN l_base_line_adjustments := arpcurr.functional_amount(
1301 l_line_adjustments,
1302 l_base_curr_code,
1303 l_exchange_rate,
1304 l_base_precision,
1305 l_base_min_acc_unit);
1306 END IF;
1307
1308 /* Bug 1373449 */
1309 IF ( l_aline_adjustments IS NOT NULL )
1310 THEN l_base_aline_adjustments := arpcurr.functional_amount(
1311 l_aline_adjustments,
1312 l_base_curr_code,
1313 l_exchange_rate,
1314 l_base_precision,
1315 l_base_min_acc_unit);
1316 END IF;
1317
1318 IF ( l_tax_adjustments IS NOT NULL )
1319 THEN l_base_tax_adjustments := arpcurr.functional_amount(
1320 l_tax_adjustments,
1321 l_base_curr_code,
1322 l_exchange_rate,
1323 l_base_precision,
1324 l_base_min_acc_unit);
1325 END IF;
1326
1327 /* Bug 1373449 */
1328 IF ( l_atax_adjustments IS NOT NULL )
1329 THEN l_base_atax_adjustments := arpcurr.functional_amount(
1330 l_atax_adjustments,
1331 l_base_curr_code,
1332 l_exchange_rate,
1333 l_base_precision,
1334 l_base_min_acc_unit);
1335 END IF;
1336
1337 IF ( l_freight_adjustments IS NOT NULL )
1338 THEN l_base_freight_adjustments := arpcurr.functional_amount(
1339 l_freight_adjustments,
1340 l_base_curr_code,
1341 l_exchange_rate,
1342 l_base_precision,
1343 l_base_min_acc_unit);
1344 END IF;
1345
1346 /* Bug 1373449 */
1347 IF ( l_afreight_adjustments IS NOT NULL )
1348 THEN l_base_afreight_adjustments := arpcurr.functional_amount(
1349 l_afreight_adjustments,
1350 l_base_curr_code,
1351 l_exchange_rate,
1352 l_base_precision,
1353 l_base_min_acc_unit);
1354 END IF;
1355
1356 IF ( l_charges_adjustments IS NOT NULL )
1357 THEN l_base_charges_adjustments := arpcurr.functional_amount(
1358 l_charges_adjustments,
1359 l_base_curr_code,
1360 l_exchange_rate,
1361 l_base_precision,
1362 l_base_min_acc_unit);
1363 END IF;
1364
1365 /* Bug 1373449 */
1366 IF ( l_acharges_adjustments IS NOT NULL )
1367 THEN l_base_acharges_adjustments := arpcurr.functional_amount(
1368 l_acharges_adjustments,
1369 l_base_curr_code,
1370 l_exchange_rate,
1371 l_base_precision,
1372 l_base_min_acc_unit);
1373 END IF;
1374
1375 /*Bug 2319665: When different types of accounted amount adjusted are calculated
1376 by multiplication of amount adjusted with the rate and their sum
1377 is not equal to the total accounted amount adjusted then recalculate
1378 the values from the total accounted amount adjusted. This is an
1379 extension to the fix for bug 2013601.*/
1380
1381 IF (l_base_total_adjustments <>
1382 (l_base_line_adjustments +
1383 l_base_tax_adjustments +
1384 l_base_freight_adjustments +
1385 l_base_charges_adjustments))
1386 /*Bug3374248*/
1387 AND NVL(l_total_adjustments,0) <> 0
1388 THEN
1389 l_run_adj_tot := 0;
1390 l_base_run_adj_tot := 0;
1391
1392 l_run_adj_tot := l_run_adj_tot + l_line_adjustments;
1393 l_base_line_adjustments := arpcurr.Currround(
1394 (l_run_adj_tot/l_total_adjustments)*
1395 l_base_total_adjustments ,l_base_curr_code) -
1396 l_base_run_adj_tot;
1397 l_base_run_adj_tot := l_base_run_adj_tot + l_base_line_adjustments;
1398
1399 l_run_adj_tot := l_run_adj_tot + l_tax_adjustments;
1400 l_base_tax_adjustments := arpcurr.Currround(
1401 (l_run_adj_tot/l_total_adjustments)*
1402 l_base_total_adjustments ,l_base_curr_code) -
1403 l_base_run_adj_tot;
1404 l_base_run_adj_tot := l_base_run_adj_tot + l_base_tax_adjustments;
1405
1406 l_run_adj_tot := l_run_adj_tot + l_freight_adjustments;
1407 l_base_freight_adjustments := arpcurr.Currround(
1408 (l_run_adj_tot/l_total_adjustments)*
1409 l_base_total_adjustments ,l_base_curr_code) -
1410 l_base_run_adj_tot;
1411 l_base_run_adj_tot := l_base_run_adj_tot + l_base_freight_adjustments;
1412
1413 l_run_adj_tot := l_run_adj_tot + l_charges_adjustments;
1414 l_base_charges_adjustments := arpcurr.Currround(
1415 (l_run_adj_tot/l_total_adjustments)*
1416 l_base_total_adjustments ,l_base_curr_code) -
1417 l_base_run_adj_tot;
1418 l_base_run_adj_tot := l_base_run_adj_tot + l_base_charges_adjustments;
1419 /*3374248*/
1420 ELSIF (NVL(l_total_adjustments,0) = 0
1421 AND NVL(l_base_total_adjustments,0) <> 0
1422 AND (l_base_total_adjustments = l_new_line_acctd_amt +
1423 l_new_tax_acctd_amt +
1424 l_new_frt_acctd_amt +
1425 l_new_chrg_acctd_amt))
1426 THEN
1427 l_run_adj_tot := 0;
1428 l_base_run_adj_tot := 0;
1429 l_base_line_adjustments:=l_new_line_acctd_amt;
1430 l_base_tax_adjustments:=l_new_tax_acctd_amt;
1431 l_base_freight_adjustments:=l_new_frt_acctd_amt;
1432 l_base_charges_adjustments:=l_new_chrg_acctd_amt;
1433 l_base_run_adj_tot:=l_base_total_adjustments;
1434 END IF;
1435 /* Bug 2319665 fix ends */
1436
1437 /*-----------------------------------------------------------------+
1438 | Correct rounding errors by putting the difference on the line |
1439 +-----------------------------------------------------------------*/
1440
1441 l_base_line_receipts := l_base_line_receipts +
1442 (
1443 l_base_total_receipts -
1444 l_base_line_receipts -
1445 l_base_tax_receipts -
1446 l_base_freight_receipts -
1447 l_base_charges_receipts
1448 );
1449
1450 l_base_line_adjustments := l_base_line_adjustments +
1451 (
1452 l_base_total_adjustments -
1453 l_base_line_adjustments -
1454 l_base_tax_adjustments -
1455 l_base_freight_adjustments -
1456 l_base_charges_adjustments
1457 );
1458
1459 /* Bug 1373449 */
1460 l_base_aline_adjustments := l_base_aline_adjustments +
1461 (
1462 l_base_atotal_adjustments -
1463 l_base_aline_adjustments -
1464 l_base_atax_adjustments -
1465 l_base_afreight_adjustments -
1466 l_base_acharges_adjustments
1467 );
1468
1469
1470 l_base_line_credits := l_base_line_credits +
1471 (
1472 l_base_total_credits -
1473 l_base_line_credits -
1474 l_base_tax_credits -
1475 l_base_freight_credits
1476 );
1477
1478 /*
1479 122958 fbreslin: Remove the charges portion of the Original total calculation
1480 */
1481 l_base_line_original := l_base_line_original +
1482 (
1483 l_base_total_original -
1484 l_base_line_original -
1485 l_base_tax_original -
1486 l_base_freight_original
1487 );
1488
1489 l_base_line_remaining := l_base_line_remaining +
1490 (
1491 l_base_total_remaining -
1492 l_base_line_remaining -
1493 l_base_tax_remaining -
1494 l_base_freight_remaining -
1495 NVL( l_base_charges_remaining, 0 )
1496 );
1497
1498 END IF; -- not entered mode only case
1499
1500 /*------------------------------------------------------------------------+
1501 | If p_mode <> 'ALL' but the Open Receivable Flag was set to N, |
1502 | the base total credits values was selected in order to determine |
1503 | the base total balance. This value should not be returned, |
1504 | however, since the p_mode <> 'ALL'. Null the value out NOCOPY in this case. |
1505 +------------------------------------------------------------------------*/
1506
1507 IF ( p_mode <> 'ALL' )
1508 THEN l_base_total_credits := null;
1509 END IF;
1510
1511 /*----------------------------------------------------------------------+
1512 | Calculate discount on line, tax and freight |
1513 | The discount could have been partially used by receipts |
1514 | The discounted amounts are stored in ra_receivable_applications |
1515 +----------------------------------------------------------------------*/
1516
1517 l_line_discount := l_line_edreceipts + l_line_uedreceipts ;
1518
1519 l_tax_discount := l_tax_edreceipts + l_tax_uedreceipts ;
1520
1521 l_freight_discount := l_freight_edreceipts + l_freight_uedreceipts ;
1522
1523 l_charges_discount := l_charges_edreceipts + l_charges_uedreceipts ;
1524
1525 /*-----------------------------------------------------+
1526 | Convert the discount amounts to the base currency |
1527 +-----------------------------------------------------*/
1528
1529 IF ( p_currency_mode <> 'E' )
1530 THEN
1531
1532
1533 IF ( l_line_discount IS NOT NULL )
1534 THEN l_base_line_discount := arpcurr.functional_amount(
1535 l_line_discount,
1536 l_base_curr_code,
1537 l_exchange_rate,
1538 l_base_precision,
1539 l_base_min_acc_unit);
1540 END IF;
1541
1542
1543 IF ( l_tax_discount IS NOT NULL )
1544 THEN l_base_tax_discount := arpcurr.functional_amount(
1545 l_tax_discount,
1546 l_base_curr_code,
1547 l_exchange_rate,
1548 l_base_precision,
1549 l_base_min_acc_unit);
1550 END IF;
1551
1552
1553 IF ( l_freight_discount IS NOT NULL )
1554 THEN l_base_freight_discount := arpcurr.functional_amount(
1555 l_freight_discount,
1556 l_base_curr_code,
1557 l_exchange_rate,
1558 l_base_precision,
1559 l_base_min_acc_unit);
1560 END IF;
1561
1562 IF ( l_charges_discount IS NOT NULL )
1563 THEN l_base_charges_discount := arpcurr.functional_amount(
1564 l_charges_discount,
1565 l_base_curr_code,
1566 l_exchange_rate,
1567 l_base_precision,
1568 l_base_min_acc_unit);
1569 END IF;
1570
1571
1572 END IF;
1573
1574 /*-----------------------------------------------------------------------+
1575 | Copy the local variables to the OUT NOCOPY parameters. |
1576 | Local variables were used because the values need to be read |
1577 | after they are calculated. I did not use IN/OUT parameters because |
1578 | I want to insure that no old values are passed into this function. |
1579 +-----------------------------------------------------------------------*/
1580
1581 p_line_original := l_line_original;
1582 p_line_remaining := l_line_remaining;
1583 p_tax_original := l_tax_original;
1584 p_tax_remaining := l_tax_remaining;
1585 p_freight_original := l_freight_original;
1586 p_freight_remaining := l_freight_remaining;
1587 p_charges_original := l_charges_original;
1588 p_charges_remaining := l_charges_remaining;
1589 p_line_discount := l_line_discount;
1590 p_tax_discount := l_tax_discount;
1591 p_freight_discount := l_freight_discount;
1592 p_charges_discount := l_charges_discount;
1593 p_total_discount := l_total_discount;
1594 p_total_original := l_total_original;
1595 p_total_remaining := l_total_remaining;
1596 p_line_receipts := l_line_receipts;
1597 p_tax_receipts := l_tax_receipts;
1598 p_freight_receipts := l_freight_receipts;
1599 p_charges_receipts := l_charges_receipts;
1600 p_total_receipts := l_total_receipts;
1601 p_line_credits := l_line_credits;
1602 p_tax_credits := l_tax_credits;
1603 p_freight_credits := l_freight_credits;
1604 p_total_credits := l_total_credits;
1605 p_line_adjustments := l_line_adjustments;
1606 p_tax_adjustments := l_tax_adjustments;
1607 p_freight_adjustments := l_freight_adjustments;
1608 p_charges_adjustments := l_charges_adjustments;
1609 p_total_adjustments := l_total_adjustments;
1610
1611 /* Bug 1373449 */
1612 p_aline_adjustments := l_aline_adjustments;
1613 p_atax_adjustments := l_atax_adjustments;
1614 p_afreight_adjustments := l_afreight_adjustments;
1615 p_acharges_adjustments := l_acharges_adjustments;
1616 p_atotal_adjustments := l_atotal_adjustments;
1617
1618 p_base_line_original := l_base_line_original;
1619 p_base_line_remaining := l_base_line_remaining;
1620 p_base_tax_original := l_base_tax_original;
1621 p_base_tax_remaining := l_base_tax_remaining;
1622 p_base_freight_original := l_base_freight_original;
1623 p_base_freight_remaining := l_base_freight_remaining;
1624 p_base_charges_original := l_base_charges_original;
1625 p_base_charges_remaining := l_base_charges_remaining;
1626 p_base_line_discount := l_base_line_discount;
1627 p_base_tax_discount := l_base_tax_discount;
1628 p_base_freight_discount := l_base_freight_discount;
1629 p_base_total_discount := l_base_total_discount;
1630 p_base_total_original := l_base_total_original;
1631 p_base_total_remaining := l_base_total_remaining;
1632 p_base_line_receipts := l_base_line_receipts;
1633 p_base_tax_receipts := l_base_tax_receipts;
1634 p_base_freight_receipts := l_base_freight_receipts;
1635 p_base_charges_receipts := l_base_charges_receipts;
1636 p_base_total_receipts := l_base_total_receipts;
1637 p_base_line_credits := l_base_line_credits;
1638 p_base_tax_credits := l_base_tax_credits;
1639 p_base_freight_credits := l_base_freight_credits;
1640 p_base_total_credits := l_base_total_credits;
1641 p_base_line_adjustments := l_base_line_adjustments;
1642 p_base_tax_adjustments := l_base_tax_adjustments;
1643 p_base_freight_adjustments := l_base_freight_adjustments;
1644 p_base_charges_adjustments := l_base_charges_adjustments;
1645 p_base_total_adjustments := l_base_total_adjustments;
1646
1647 /* Bug 1373449 */
1648 p_base_aline_adjustments := l_base_aline_adjustments;
1649 p_base_atax_adjustments := l_base_atax_adjustments;
1650 p_base_afreight_adjustments := l_base_afreight_adjustments;
1651 p_base_acharges_adjustments := l_base_acharges_adjustments;
1652 p_base_atotal_adjustments := l_base_atotal_adjustments;
1653
1654 EXCEPTION
1655 WHEN OTHERS THEN
1656 RAISE;
1657 arp_standard.debug('ARP_BAL_UTIL.Transaction_Balances (-)');
1658 END;
1659
1660 /*===========================================================================+
1661 | PROCEDURE |
1662 | get_summary_trx_balances |
1663 | |
1664 | DESCRIPTION |
1665 | Returns the original and remaining balances for a transaction. |
1666 | This procedure does not provide the line type breakdown for credits, |
1667 | adjustments, receipts or discounts. It also does not provide base |
1668 | currency amounts. |
1669 | |
1670 | SCOPE - PUBLIC |
1671 | |
1672 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1673 | |
1674 | ARGUMENTS : IN: |
1675 | p_customer_trx_id |
1676 | p_open_receivables_flag |
1677 | p_exchange_rate |
1678 | OUT: |
1679 | p_line_original |
1680 | p_line_remaining |
1681 | p_tax_original |
1682 | p_tax_remaining |
1683 | p_freight_original |
1684 | p_freight_remaining |
1685 | p_charges_original |
1686 | p_charges_remaining |
1687 | p_total_original |
1688 | p_total_remaining |
1689 | |
1690 | |
1691 | NOTES |
1692 | |
1693 | MODIFICATION HISTORY |
1694 | 05-SEP-95 Charlie Tomberg Created |
1695 | |
1696 +===========================================================================*/
1697
1698
1699 PROCEDURE get_summary_trx_balances( p_customer_trx_id IN Number,
1700 p_open_receivables_flag IN Varchar2,
1701 p_line_original OUT NOCOPY NUMBER,
1702 p_line_remaining OUT NOCOPY NUMBER,
1703 p_tax_original OUT NOCOPY NUMBER,
1704 p_tax_remaining OUT NOCOPY NUMBER,
1705 p_freight_original OUT NOCOPY NUMBER,
1706 p_freight_remaining OUT NOCOPY NUMBER,
1707 p_charges_original OUT NOCOPY NUMBER,
1708 p_charges_remaining OUT NOCOPY NUMBER,
1709 p_total_original OUT NOCOPY NUMBER,
1710 p_total_remaining OUT NOCOPY NUMBER )
1711 IS
1712 l_dummy NUMBER;
1713
1714
1715 BEGIN
1716
1717 arp_bal_util.transaction_balances(p_customer_trx_id,
1718 p_open_receivables_flag,
1719 1,
1720 'SUMMARY',
1721 'E',
1722 p_line_original,
1723 p_line_remaining,
1724 p_tax_original,
1725 p_tax_remaining,
1726 p_freight_original,
1727 p_freight_remaining,
1728 p_charges_original,
1729 p_charges_remaining,
1730 l_dummy,
1731 l_dummy,
1732 l_dummy,
1733 l_dummy,
1734 l_dummy,
1735 p_total_original,
1736 p_total_remaining,
1737 l_dummy,
1738 l_dummy,
1739 l_dummy,
1740 l_dummy,
1741 l_dummy,
1742 l_dummy,
1743 l_dummy,
1744 l_dummy,
1745 l_dummy,
1746 l_dummy,
1747 l_dummy,
1748 l_dummy,
1749 l_dummy,
1750 l_dummy,
1751 l_dummy,
1752 l_dummy,
1753 l_dummy,
1754 l_dummy,
1755 l_dummy,
1756 l_dummy,
1757 l_dummy,
1758 l_dummy,
1759 l_dummy,
1760 l_dummy,
1761 l_dummy,
1762 l_dummy,
1763 l_dummy,
1764 l_dummy,
1765 l_dummy,
1766 l_dummy,
1767 l_dummy,
1768 l_dummy,
1769 l_dummy,
1770 l_dummy,
1771 l_dummy,
1772 l_dummy,
1773 l_dummy,
1774 l_dummy,
1775 l_dummy,
1776 l_dummy,
1780 l_dummy,
1777 l_dummy,
1778 l_dummy,
1779 l_dummy,
1781 l_dummy,
1782 l_dummy,
1783 l_dummy,
1784 l_dummy,
1785 l_dummy,
1786 l_dummy,
1787 l_dummy,
1788 l_dummy
1789 );
1790
1791
1792 EXCEPTION
1793 WHEN OTHERS THEN
1794 RAISE;
1795
1796 END;
1797
1798 /*===========================================================================+
1799 | FUNCTION |
1800 | Get_trx_balance |
1801 | |
1802 | DESCRIPTION |
1803 | Gets the balance due for a transaction. |
1804 | |
1805 | SCOPE - PUBLIC |
1806 | |
1807 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1808 | |
1809 | ARGUMENTS : IN: |
1810 | p_customer_trx_id |
1811 | p_open_receivables_flag |
1812 | OUT: |
1813 | None |
1814 | |
1815 | RETURNS : The transaction balance |
1816 | |
1817 | NOTES |
1818 | |
1819 | MODIFICATION HISTORY |
1820 | 11-DEC-95 Charlie Tomberg Created |
1821 | |
1822 +===========================================================================*/
1823
1824 FUNCTION get_trx_balance( p_customer_trx_id IN Number,
1825 p_open_receivables_flag IN Varchar2)
1826 RETURN NUMBER IS
1827 l_balance number;
1828 l_dummy number;
1829
1830 BEGIN
1831
1832 IF (p_customer_trx_id IS NULL)
1833 THEN RETURN(NULL);
1834 ELSE
1835 arp_bal_util.get_summary_trx_balances( p_customer_trx_id,
1836 p_open_receivables_flag,
1837 l_dummy,
1838 l_dummy,
1839 l_dummy,
1840 l_dummy,
1841 l_dummy,
1842 l_dummy,
1843 l_dummy,
1844 l_dummy,
1845 l_dummy,
1846 l_balance);
1847
1848 RETURN(l_balance);
1849
1850 END IF;
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN RAISE;
1854
1855 END;
1856
1857
1858
1859 /*===========================================================================+
1860 | FUNCTION |
1861 | Get_commitment_balance |
1862 | |
1863 | DESCRIPTION |
1864 | Gets the commitment balance for a deposit or Guarantee. |
1865 | This is a cover for calc_commitment_balance(). |
1866 | |
1867 | SCOPE - PUBLIC |
1868 | |
1869 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1870 | |
1871 | ARGUMENTS : IN: |
1872 | p_customer_trx_id |
1873 | p_class |
1874 | p_oe_installed_flag |
1875 | p_so_source_code - value of profile SO_SOURCE_CODE |
1876 | OUT: |
1877 | None |
1878 | |
1879 | RETURNS : The commitment balance |
1880 | |
1881 | NOTES |
1882 | |
1883 | MODIFICATION HISTORY |
1884 | 10-NOV-95 Charlie Tomberg Created |
1885 | 02-FEB-98 Debbie Jancis Changed the order of the |
1886 | p_oe_installed_flag and |
1887 | p_so_source_code in the calling seq |
1888 | because they were reversed. |
1889 +===========================================================================*/
1890
1891 FUNCTION get_commitment_balance( p_customer_trx_id IN Number,
1892 p_class IN Varchar2,
1893 p_so_source_code IN varchar2,
1894 p_oe_installed_flag IN varchar2)
1895 RETURN NUMBER IS
1896
1897 BEGIN
1898
1899 RETURN(
1900 arp_bal_util.calc_commitment_balance( p_customer_trx_id,
1901 p_class,
1902 'Y',
1903 p_oe_installed_flag,
1904 p_so_source_code )
1905 );
1906 END;
1907
1908
1909 /*===========================================================================+
1910 | FUNCTION |
1911 | calc_commitment_balance |
1912 | |
1913 | DESCRIPTION |
1914 | Gets the commitment balance for a deposit or Guarantee. |
1915 | |
1916 | SCOPE - PUBLIC |
1917 | |
1918 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1919 | |
1920 | ARGUMENTS : IN: |
1921 | p_customer_trx_id |
1922 | p_class |
1923 | p_include_oe_trx_flag |
1924 | p_oe_installed_flag |
1925 | p_so_source_code - value of profile SO_SOURCE_CODE |
1926 | OUT: |
1927 | None |
1928 | |
1929 | RETURNS : The commitment balance |
1930 | |
1931 | NOTES |
1932 | |
1933 | MODIFICATION HISTORY |
1934 | 10-NOV-95 Charlie Tomberg Created |
1935 | 12-JAN-01 Michael Raymond Fixed select over ra_interface_lines
1936 | table to properly test trx_type
1937 | for commitment invoices. OM
1938 | is using a little-known method where
1939 | the trx_type is defaulted from the
1940 | commitment trx_type.
1941 | See bug 1580737 for details.
1942 | 11-APR-01 Michael Raymond Implemented promised_commitment_amount
1943 | and allocate_tax_freight logic for
1944 | commitment-related lines in
1945 | ra_interface_lines table.
1946 | See bugs 1483656 and 1645425 for details.
1947 +===========================================================================*/
1948
1949 FUNCTION calc_commitment_balance( p_customer_trx_id IN Number,
1950 p_class IN Varchar2,
1951 p_include_oe_trx_flag IN varchar2,
1952 p_oe_installed_flag IN varchar2,
1953 p_so_source_code IN varchar2 )
1954 RETURN NUMBER IS
1955
1956 l_commitment_bal number;
1957 l_commitment_class ra_cust_trx_types.type%type;
1958 l_currency_code fnd_currencies.currency_code%type;
1959 /* 1580737 - holds subsequent_trx_type_id */
1960 l_sub_inv_trx_type_id ra_cust_trx_types.subsequent_trx_type_id%type;
1961 /* 1483656 - holds the allocation flag */
1962 l_allocate_t_f ra_cust_trx_types.allocate_tax_freight%type;
1963
1964 BEGIN
1965
1966 IF (
1967 p_customer_trx_id IS NULL
1968 OR NVL(p_class, 'DEP') NOT IN ('DEP', 'GUAR')
1969 )
1970 THEN RETURN( null );
1971 ELSE
1972
1973 /*-----------------------------------------------------------+
1974 | Get the Commitment Balance and the type of Transaction. |
1975 +-----------------------------------------------------------*/
1976
1977 BEGIN
1978 /* 1580737 - added subsequent_trx_type_id */
1979 /* modified for tca uptake */
1980 SELECT lines.extended_amount,
1981 type.type,
1982 trx.invoice_currency_code,
1983 type.subsequent_trx_type_id,
1984 type.allocate_tax_freight
1985 INTO l_commitment_bal,
1986 l_commitment_class,
1987 l_currency_code,
1988 l_sub_inv_trx_type_id,
1989 l_allocate_t_f
1990 FROM hz_cust_accounts cust_acct,
1991 ra_customer_trx_lines lines,
1992 ra_customer_trx trx,
1993 ra_cust_trx_types type
1994 WHERE trx.customer_trx_id = p_customer_trx_id
1995 AND trx.cust_trx_type_id = type.cust_trx_type_id
1996 AND trx.customer_trx_id = lines.customer_trx_id
1997 AND trx.bill_to_customer_id = cust_acct.cust_account_id
1998 AND type.type IN ('DEP','GUAR')
1999 ORDER BY trx.trx_number;
2000
2001 EXCEPTION
2002 WHEN NO_DATA_FOUND THEN
2003 RETURN( null );
2004 WHEN OTHERS THEN RAISE;
2005 END;
2006 /*-------------------------------------------------------------+
2007 | ** If OE is installed and the include_oe_trx_flag |
2008 | is set to true, get the uninvoiced commitment balance |
2009 +-------------------------------------------------------------*/
2010
2011 IF (
2012 p_include_oe_trx_flag = 'Y'
2013 AND p_oe_installed_flag = 'I'
2014 )
2015 THEN
2016
2017 /*---------------------------------------------------------------+
2018 | ** Get uninvoiced commitment balance and subtract from total |
2019 | ** commitment balance |
2020 +---------------------------------------------------------------*/
2021
2022
2023 -- replace OE_ACCOUNTING with OE_Payments_Util
2024 SELECT NVL( l_commitment_bal, 0 ) -
2025 NVL(OE_Payments_Util.Get_Uninvoiced_Commitment_Bal(p_customer_trx_id), 0)
2026 INTO l_commitment_bal
2027 FROM dual;
2028
2029 /*------------------------------------------------------------+
2030 | Include OE transactions that are in the AutoInvoice |
2031 | interface tables and have not yet been transferred to AR. |
2032 +------------------------------------------------------------*/
2033
2034 /* 1580737 - Restructured where clause for
2035 better performance and included logic
2036 for commitment invoices from OE (null trx_type) */
2037 /* 1483656 - Implemented logic for promised_commitment_amt
2038 and allocate_tax_freight */
2039
2040 SELECT NVL( l_commitment_bal, 0 ) -
2041 NVL( SUM(NVL(i.promised_commitment_amount,
2042 i.amount)), 0)
2043 INTO l_commitment_bal
2044 FROM ra_interface_lines i,
2045 ra_customer_trx_lines l
2046 WHERE NVL(interface_status,
2047 'A') <> 'P'
2048 AND (i.line_type = 'LINE'
2049 OR i.line_type = DECODE(l_allocate_t_f,'Y','FREIGHT','LINE'))
2050 AND i.reference_line_id = l.customer_trx_line_id
2051 AND l.customer_trx_id = p_customer_trx_id
2052 AND i.interface_line_context = p_so_source_code
2053 AND (EXISTS
2054 ( select 'valid_trx_type'
2055 from ra_cust_trx_types ty
2056 where (i.cust_trx_type_name = ty.name OR
2057 i.cust_trx_type_id = ty.cust_trx_type_id)
2058 AND ty.type = 'INV')
2059 OR (i.cust_trx_type_name is null AND
2060 i.cust_trx_type_id is null AND
2061 l_sub_inv_trx_type_id is not null));
2062
2063 END IF; -- end OE is installed case
2064
2065 /*-------------------------------------------+
2066 | If the commitment type is for a DEPOSIT, |
2067 | then add in commitment adjustments |
2068 +-------------------------------------------*/
2069
2070 IF ( l_commitment_class = 'DEP' )
2071 THEN
2072
2073 SELECT NVL( l_commitment_bal, 0)
2074 -
2075 (
2076 NVL(
2077 SUM( ADJ.AMOUNT),
2078 0
2079 ) * -1
2080 )
2081 INTO l_commitment_bal
2082 FROM ra_customer_trx trx,
2083 ra_cust_trx_types type,
2084 ar_adjustments adj
2085 WHERE trx.cust_trx_type_id = type.cust_trx_type_id
2086 AND trx.initial_customer_trx_id = p_customer_trx_id
2087 AND trx.complete_flag = 'Y'
2088 AND adj.adjustment_type = 'C'
2089 AND type.type IN ('INV', 'CM')
2090 AND adj.customer_trx_id =
2091 DECODE(type.type,
2092 'INV', trx.customer_trx_id,
2093 'CM', trx.previous_customer_trx_id)
2094 AND NVL( adj.subsequent_trx_id, -111) =
2095 DECODE(type.type,
2096 'INV', -111,
2097 'CM', trx.customer_trx_id);
2098
2099 /*-------------------------------------------------------+
2100 | Subtract out NOCOPY credit memos against the deposit itself |
2101 +-------------------------------------------------------*/
2102
2103 SELECT NVL( l_commitment_bal, 0)
2104 -
2105 NVL(
2106 SUM(
2107 -1 * line.extended_amount
2108 ),
2109 0
2110 )
2111 INTO l_commitment_bal
2112 FROM ra_customer_trx trx,
2113 ra_customer_trx_lines line
2114 WHERE trx.customer_trx_id = line.customer_trx_id
2115 AND trx.previous_customer_trx_id = p_customer_trx_id
2116 AND trx.complete_flag = 'Y';
2117
2118 ELSE -- Guarantee case
2119
2120 SELECT NVL( l_commitment_bal, 0) -
2121 (
2122 NVL(
2123 SUM(
2124 amount_line_items_original
2125 ),
2126 0
2127 ) -
2128 NVL(
2129 SUM(
2130 amount_due_remaining
2131 ),
2132 0
2133 )
2134 )
2135 INTO l_commitment_bal
2136 FROM ar_payment_schedules
2137 WHERE customer_trx_id = p_customer_trx_id;
2138
2139
2140 /*------------------------------------------------------------+
2141 | We do not want to adjust the commitment balance by the |
2142 | amount of any manual adjustments against the commitment. |
2143 | The following statement backs out NOCOPY these manual |
2144 | adjustments from the commitment balance. |
2145 +------------------------------------------------------------*/
2146
2147 SELECT NVL( l_commitment_bal, 0) -
2148 NVL(
2149 SUM( amount ),
2150 0
2151 )
2152 INTO l_commitment_bal
2153 FROM ar_adjustments
2154 WHERE customer_trx_id = p_customer_trx_id
2155 AND adjustment_type <> 'C';
2156
2157 END IF; -- end Guarantee case
2158
2159
2160 RETURN(
2161 arpcurr.CurrRound(
2162 GREATEST(
2163 l_commitment_bal,
2164 0
2165 ),
2166 l_currency_code
2167 )
2168 );
2169
2170 END IF; -- end processing required case
2171
2172 EXCEPTION
2173 WHEN OTHERS THEN
2174 RAISE;
2175
2176 END;
2177
2178 /*===========================================================================+
2179 | FUNCTION |
2180 | DESCRIPTION |
2181 | Gets the Balance for Child of Commitment . |
2182 | |
2183 | SCOPE - PUBLIC |
2184 | |
2185 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2186 | |
2187 | ARGUMENTS : IN: |
2188 | p_customer_trx_id |
2189 | p_mode('E'-Entered ,'F'-Functional) |
2190 | OUT: |
2191 | None |
2192 | |
2193 | RETURNS : NONE |
2194 | |
2195 | NOTES |
2196 | |
2197 | MODIFICATION HISTORY |
2198 | 27-JAN-96 Vikas Mahajan Created |
2199 | |
2200 | 12-MAR-02 Michael Raymond Bug 2237126 - Added logic to account |
2201 | for changes to commitment applied amount|
2202 | caused by credit memo applications. |
2203 | |
2204 | 18-DEC-03 Pravin Pawar Bug3252481 - Divided the the SQL into |
2205 | 3 SQLs , for better |
2206 | performance. |
2207 |
2208 | 30-JUN-04 Obaidur Rashid Bug 3702956 - The previous fix
2209 | introduced a bug wherein
2210 | if more than one of the
2211 | OR condition is TRUE then
2212 | the same row will be
2213 | returned more than once.
2214 | Which results into
2215 | displaying wrong amount to
2216 | the user. This is corrected
2217 | in this fix.
2218 |
2219 +===========================================================================*/
2220
2221 FUNCTION get_applied_com_balance( p_customer_trx_id IN Number,
2222 p_mode IN VARCHAR2)
2223 RETURN NUMBER IS
2224
2225
2226 l_actual_amount number := 0 ;
2227 l_acctd_amount number := 0 ;
2228 l_actual_amount1 number := 0 ;
2229 l_acctd_amount1 number := 0 ;
2230
2231 BEGIN
2232 IF ( p_customer_trx_id IS NULL ) THEN
2233 RETURN( NULL );
2234 ELSE
2235
2236 /* Bug3252481 : Divided main SQL into following 3 SQLs */
2237
2238 SELECT NVL(SUM(amount),0),
2239 NVL(SUM(acctd_amount),0)
2240 INTO l_actual_amount1,
2241 l_acctd_amount1
2242 FROM ra_customer_trx t,
2243 ra_cust_trx_types ty,
2244 ar_adjustments a
2245 WHERE t.cust_trx_type_id = ty.cust_trx_type_id
2246 and t.customer_trx_id = a.customer_trx_id
2247 and ty.type not in ('DEP', 'GUAR')
2248 and a.adjustment_type = 'C'
2249 and t.customer_trx_id = p_customer_trx_id;
2250
2251 l_actual_amount := l_actual_amount + l_actual_amount1;
2252 l_acctd_amount := l_acctd_amount + l_acctd_amount1;
2253
2254 SELECT NVL(SUM(amount),0),
2255 NVL(SUM(acctd_amount),0)
2256 INTO l_actual_amount1,
2257 l_acctd_amount1
2258 FROM ra_customer_trx t,
2259 ra_cust_trx_types ty,
2260 ar_adjustments a
2261 WHERE t.cust_trx_type_id = ty.cust_trx_type_id
2262 and t.customer_trx_id = a.subsequent_trx_id
2263 and ty.type not in ('DEP', 'GUAR')
2264 and a.adjustment_type = 'C'
2265 and t.customer_trx_id = p_customer_trx_id;
2266
2267 l_actual_amount := l_actual_amount + l_actual_amount1;
2268 l_acctd_amount := l_acctd_amount + l_acctd_amount1;
2269
2270 SELECT NVL(SUM(amount),0),
2271 NVL(SUM(acctd_amount),0)
2272 INTO l_actual_amount1,
2273 l_acctd_amount1
2274 FROM ra_customer_trx t,
2275 ra_cust_trx_types ty,
2276 ar_adjustments a
2277 WHERE t.cust_trx_type_id = ty.cust_trx_type_id
2278 and a.subsequent_trx_id IN
2279 (select cma.customer_trx_id
2280 from ar_receivable_applications cma
2281 where cma.applied_customer_trx_id =
2282 t.customer_trx_id
2283 and cma.application_type = 'CM')
2284 and ty.type not in ('DEP', 'GUAR')
2285 and a.adjustment_type = 'C'
2286 and t.customer_trx_id = p_customer_trx_id
2287 -- following was added for Bug # 3702956
2288 AND adjustment_id NOT IN
2289 (
2290 SELECT adjustment_id
2291 FROM ar_adjustments aa1
2292 WHERE aa1.customer_trx_id = t.customer_trx_id
2293 AND aa1.adjustment_type = 'C'
2294 )
2295 AND adjustment_id NOT IN
2296 (
2297 SELECT adjustment_id
2298 FROM ar_adjustments aa2
2299 WHERE aa2.subsequent_trx_id = t.customer_trx_id
2300 AND aa2.adjustment_type = 'C'
2301 );
2302
2303
2304 l_actual_amount := l_actual_amount + l_actual_amount1;
2305 l_acctd_amount := l_acctd_amount + l_acctd_amount1;
2306
2307 /* Bug3252481 End */
2308
2309 IF (p_mode='E')
2310 THEN
2311 return(l_actual_amount);
2312 ELSIF (p_mode='F')
2313 THEN
2314 return(l_acctd_amount);
2315 ELSE
2316 return(NULL);
2317 END IF;
2318 END IF;
2319
2320 EXCEPTION
2321 WHEN OTHERS THEN
2322 RAISE;
2323 END get_applied_com_balance;
2324
2325 /*===========================================================================+
2326 | FUNCTION LINE_LEVEL_ACTIVTY |
2327 | |
2328 | DESCRIPTION |
2329 | Returns TRUE if there are line Level applications for this |
2330 | customer_trx_id |
2331 | |
2332 | SCOPE - PUBLIC |
2333 | |
2334 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2335 | |
2336 | ARGUMENTS : IN: |
2337 | p_customer_trx_id |
2338 | |
2339 | RETURNS : BOOLEAN |
2340 | |
2341 | NOTES |
2342 | |
2343 | MODIFICATION HISTORY |
2344 | Date Name Modification |
2345 | 02-Aug-2005 Debbie Sue Jancis Original Coding |
2346 | |
2347 +===========================================================================*/
2348 FUNCTION Line_Level_Activity ( p_customer_trx_id IN Number)
2349 RETURN BOOLEAN IS
2350 l_count NUMBER;
2351
2352 BEGIN
2353 arp_util.debug('Line_Level_Activity()+' );
2354
2355 Select count(customer_Trx_line_id)
2356 INTO l_count
2357 from ar_activity_details
2358 WHERE customer_trx_line_id in
2359 (SELECT customer_trx_line_id
2360 FROM RA_CUSTOMER_TRX_LINES
2361 WHERE customer_trx_id = p_customer_trx_id)
2362 and nvl(CURRENT_ACTIVITY_FLAG, 'Y') = 'Y'; -- bug 7241111
2363
2364 IF ( l_count > 0) THEN
2365 arp_util.debug('Line_Level_Activity Exists()-' );
2366 RETURN TRUE;
2367 ELSE
2368 arp_util.debug('Line_Level_Activity Does Not Exist()-' );
2369 RETURN FALSE;
2370 END IF;
2371
2372 END Line_Level_Activity;
2373
2374 /*===========================================================================+
2375 | PROCEDURE |
2376 | trx_line_balances |
2377 | |
2378 | DESCRIPTION |
2379 | Determines the line level balances for a trx Line or Group |
2380 | |
2381 | SCOPE - PUBLIC |
2382 | |
2383 | |
2384 | ARGUMENTS : IN: |
2385 | |
2386 | NOTES |
2387 | |
2388 | MODIFICATION HISTORY |
2389 | |
2390 | 03-Aug-2005 Debbie Sue Jancis Original |
2391 | |
2392 +===========================================================================*/
2393 PROCEDURE trx_line_balances (
2394 p_customer_trx_id
2395 IN RA_CUSTOMER_TRX.CUSTOMER_TRX_ID%TYPE DEFAULT NULL,
2396 p_line_num IN NUMBER DEFAULT NULL,
2397 p_group_id IN NUMBER DEFAULT NULL,
2398 p_exchange_rate IN NUMBER,
2399 p_line_original OUT NOCOPY NUMBER,
2400 p_tax_original OUT NOCOPY NUMBER,
2401 p_base_line_original OUT NOCOPY NUMBER,
2402 p_base_tax_original OUT NOCOPY NUMBER,
2403 p_total_original OUT NOCOPY NUMBER,
2404 p_base_total_original OUT NOCOPY NUMBER,
2405 p_line_receipts OUT NOCOPY NUMBER,
2406 p_tax_receipts OUT NOCOPY NUMBER,
2407 p_line_discount OUT NOCOPY NUMBER,
2408 p_tax_discount OUT NOCOPY NUMBER,
2409 p_base_line_receipts OUT NOCOPY NUMBER,
2410 p_base_tax_receipts OUT NOCOPY NUMBER,
2411 p_base_line_discount OUT NOCOPY NUMBER,
2412 p_base_tax_discount OUT NOCOPY NUMBER,
2413 p_freight_original OUT NOCOPY NUMBER,
2414 p_base_freight_original OUT NOCOPY NUMBER,
2415 p_freight_receipts OUT NOCOPY NUMBER,
2416 p_charges_receipts OUT NOCOPY NUMBER,
2417 p_base_charges_receipts OUT NOCOPY NUMBER,
2418 p_base_freight_receipts OUT NOCOPY NUMBER,
2419 p_freight_discount OUT NOCOPY NUMBER,
2420 p_base_freight_discount OUT NOCOPY NUMBER,
2421 p_total_receipts OUT NOCOPY NUMBER,
2422 p_base_total_receipts OUT NOCOPY NUMBER,
2423 p_total_discount OUT NOCOPY NUMBER,
2424 p_base_total_discount OUT NOCOPY NUMBER,
2425 p_line_remaining OUT NOCOPY NUMBER,
2426 p_tax_remaining OUT NOCOPY NUMBER,
2427 p_freight_remaining OUT NOCOPY NUMBER,
2428 p_charges_remaining OUT NOCOPY NUMBER,
2429 p_total_remaining OUT NOCOPY NUMBER,
2430 p_base_line_remaining OUT NOCOPY NUMBER,
2431 p_base_tax_remaining OUT NOCOPY NUMBER,
2432 p_base_freight_remaining OUT NOCOPY NUMBER,
2433 p_base_charges_remaining OUT NOCOPY NUMBER,
2434 p_base_total_remaining OUT NOCOPY NUMBER,
2435 p_line_credits OUT NOCOPY NUMBER,
2436 p_tax_credits OUT NOCOPY NUMBER,
2437 p_freight_credits OUT NOCOPY NUMBER,
2438 p_total_credits OUT NOCOPY NUMBER,
2439 p_base_line_credits OUT NOCOPY NUMBER,
2440 p_base_tax_credits OUT NOCOPY NUMBER,
2441 p_base_freight_credits OUT NOCOPY NUMBER,
2442 p_base_total_credits OUT NOCOPY NUMBER,
2443 p_line_adjustments OUT NOCOPY NUMBER,
2444 p_tax_adjustments OUT NOCOPY NUMBER,
2445 p_freight_adjustments OUT NOCOPY NUMBER,
2446 p_charges_adjustments OUT NOCOPY NUMBER,
2447 p_total_adjustments OUT NOCOPY NUMBER,
2448 p_base_line_adjustments OUT NOCOPY NUMBER,
2449 p_base_tax_adjustments OUT NOCOPY NUMBER,
2450 p_base_freight_adjustments OUT NOCOPY NUMBER,
2451 p_base_charges_adjustments OUT NOCOPY NUMBER,
2452 p_base_total_adjustments OUT NOCOPY NUMBER
2453 ) IS
2454
2455 l_line_original NUMBER;
2456 l_tax_original NUMBER;
2457 l_freight_original NUMBER;
2458 l_total_original NUMBER;
2459
2460 l_line_receipts NUMBER;
2461 l_tax_receipts NUMBER;
2462 l_freight_receipts NUMBER;
2463 l_charges_receipts NUMBER;
2464 l_total_receipts NUMBER;
2465
2466 l_line_discount NUMBER;
2467 l_tax_discount NUMBER;
2468 l_freight_discount NUMBER;
2469 l_total_discount NUMBER;
2470
2471 l_base_line_original NUMBER;
2472 l_base_tax_original NUMBER;
2473 l_base_freight_original NUMBER;
2474 l_base_total_original NUMBER;
2475
2476 l_base_line_receipts NUMBER;
2477 l_base_tax_receipts NUMBER;
2478 l_base_freight_receipts NUMBER;
2479 l_base_charges_receipts NUMBER;
2480 l_base_total_receipts NUMBER;
2481
2482 l_base_line_discount NUMBER;
2483 l_base_tax_discount NUMBER;
2484 l_base_freight_discount NUMBER;
2485 l_base_total_discount NUMBER;
2486
2487 l_line_remaining NUMBER;
2488 l_tax_remaining NUMBER;
2489 l_freight_remaining NUMBER;
2490 l_charges_remaining NUMBER;
2491 l_total_remaining NUMBER;
2492
2493 l_base_line_remaining NUMBER;
2494 l_base_tax_remaining NUMBER;
2495 l_base_freight_remaining NUMBER;
2496 l_base_charges_remaining NUMBER;
2497 l_base_total_remaining NUMBER;
2498
2499 l_line_credits NUMBER;
2500 l_tax_credits NUMBER;
2501 l_freight_credits NUMBER;
2502 l_total_credits NUMBER;
2503 l_base_line_credits NUMBER;
2504 l_base_tax_credits NUMBER;
2505 l_base_freight_credits NUMBER;
2506 l_base_total_credits NUMBER;
2507
2508 l_line_adjustments NUMBER;
2509 l_tax_adjustments NUMBER;
2510 l_freight_adjustments NUMBER;
2511 l_total_adjustments NUMBER;
2512 l_base_line_adjustments NUMBER;
2513 l_base_tax_adjustments NUMBER;
2514 l_base_freight_adjustments NUMBER;
2515 l_base_total_adjustments NUMBER;
2516
2517 l_base_curr_code fnd_currencies.currency_code%type;
2518 l_base_precision fnd_currencies.precision%type;
2519 l_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
2520
2521 l_customer_Trx_line_id ra_customer_Trx_lines.customer_Trx_line_id%type;
2522 BEGIN
2523
2524 /* initialize the items */
2525
2526 l_line_receipts :=0;
2527 l_tax_receipts :=0;
2528 l_freight_receipts :=0;
2529 l_charges_receipts := 0;
2530 l_total_receipts := 0;
2531
2532 arp_util.debug('l_tax_receipts = ' || l_tax_receipts);
2533
2534 /*-------------------------------------------------------+
2535 | Get the base currency and exchange rate information |
2536 +-------------------------------------------------------*/
2537 --bug7025523
2538 SELECT sob.currency_code,
2539 precision,
2543 l_base_min_acc_unit
2540 minimum_accountable_unit
2541 INTO l_base_curr_code,
2542 l_base_precision,
2544 FROM fnd_currencies fc,
2545 gl_sets_of_books sob
2546 WHERE sob.set_of_books_id = arp_global.set_of_books_id
2547 AND sob.currency_code = fc.currency_code;
2548
2549 -- derive the balances for LINE number:
2550
2551 IF (p_line_num IS NOT NULL) THEN
2552
2553 select customer_Trx_line_id
2554 into l_customer_trx_line_id
2555 from ra_customer_Trx_lines
2556 where line_number = p_line_num
2557 and line_type = 'LINE'
2558 and customer_trx_id = p_customer_trx_id;
2559
2560 -- line original, tax original, freight original (entered currencies)
2561 select sum(DECODE (lines.line_type,
2562 'TAX',0,
2563 'FREIGHT',0 , 1) *
2564 DECODE(ct.complete_flag, 'N',
2565 0, lines.extended_amount)), -- line_original
2566 sum(DECODE (lines.line_type,
2567 'TAX',1,0) *
2568 DECODE(ct.complete_flag,
2569 'N', 0,
2570 lines.extended_amount )) tax_original, -- tax_original
2571 sum(DECODE (lines.line_type,
2572 'FREIGHT', 1,0) *
2573 DECODE(ct.complete_flag,
2574 'N', 0 ,
2575 lines.extended_amount)) -- freight_original
2576 INTO l_line_original,
2577 l_tax_original,
2578 l_freight_original
2579 from ra_customer_trx ct,
2580 ra_customer_trx_lines lines
2581 where (lines.customer_Trx_line_id = l_customer_trx_line_id or
2582 lines.link_to_cust_trx_line_id = l_customer_trx_line_id)
2583 and ct.customer_Trx_id = lines.customer_trx_id
2584 and ct.customer_trx_id = p_customer_trx_id;
2585
2586 -- Derive line_Receipt in entered and base currencies
2587 --Bug6906707
2588 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2589 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2590 INTO
2591 l_line_receipts,
2592 l_base_line_receipts
2593 FROM ar_distributions
2594 WHERE source_table = 'RA'
2595 AND source_id in (select receivable_application_id
2596 from ar_receivable_applications
2597 where status = 'APP' and
2598 applied_customer_Trx_id = p_customer_trx_id and
2599 cash_receipt_id is not null )
2600 AND ref_customer_trx_line_id = l_customer_trx_line_id
2601 AND activity_bucket = 'APP_LINE'
2602 AND ref_account_class = 'REV';
2603
2604 -- Derive tax_receipt in entered and base currencies
2605 --Bug6906707
2606 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2607 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2608 INTO
2609 l_tax_receipts,
2610 l_base_tax_receipts
2611 FROM ar_distributions
2612 WHERE source_table = 'RA'
2613 AND source_id in (select receivable_application_id
2614 from ar_receivable_applications
2615 where status = 'APP' and
2616 applied_customer_Trx_id = p_customer_trx_id and
2617 cash_receipt_id is not null )
2618 AND tax_link_id = l_customer_trx_line_id
2619 AND activity_bucket = 'APP_TAX'
2620 AND ref_account_class = 'TAX';
2621
2622
2623
2624 -- derive freight_receipt,
2625 -- line_discount, tax_discount, freight_discount amts
2626 -- in entered currency
2627 /*Bug6821893 */ /*Bug6906707*/
2628 SELECT
2629 nvl(sum(nvl(charges,0)),0),
2630 nvl(sum(nvl(freight_discount,0)),0)
2631 INTO
2632 l_charges_receipts,
2633 l_freight_discount
2634 FROM AR_ACTIVITY_DETAILS act,
2635 ra_customer_trx_lines line
2636 WHERE line.customer_Trx_id = p_customer_trx_id
2637 and line.line_number = p_line_num
2638 and line.line_type = 'LINE'
2639 and nvl(act.CURRENT_ACTIVITY_FLAG, 'Y') = 'Y' -- bug 7241111
2640 and line.customer_Trx_line_id = act.customer_Trx_line_id;
2641
2642 /*Bug6906707, Start */
2643 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2644 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2645 INTO
2646 l_freight_receipts,
2647 l_base_freight_receipts
2648 FROM ar_distributions ard,
2649 ra_customer_trx_lines ctl
2650 WHERE ard.source_table = 'RA'
2651 AND ard.source_id in (select receivable_application_id
2652 from ar_receivable_applications
2653 where status = 'APP' and
2654 applied_customer_Trx_id = p_customer_trx_id and
2655 cash_receipt_id is not null )
2656 AND ctl.link_to_cust_trx_line_id = l_customer_trx_line_id
2657 AND ard.ref_customer_trx_line_id = ctl.customer_trx_line_id
2658 AND ctl.line_type = 'FREIGHT'
2659 AND ard.activity_bucket = 'APP_FRT'
2660 AND ard.ref_account_class = 'FREIGHT';
2661
2662
2663 SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
2664 NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
2665 INTO
2666 l_tax_discount,
2667 l_base_tax_discount
2668 FROM ar_distributions
2669 WHERE source_table = 'RA'
2670 AND source_id in (select receivable_application_id
2671 from ar_receivable_applications
2672 where status = 'APP' and
2673 applied_customer_Trx_id = p_customer_trx_id and
2674 cash_receipt_id is not null )
2675 AND tax_link_id = l_customer_trx_line_id
2676 AND activity_bucket IN ('ED_TAX', 'UNED_TAX')
2677 AND ref_account_class = 'TAX';
2678
2679
2680 SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
2681 NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
2682 INTO
2683 l_line_discount,
2684 l_base_line_discount
2685 FROM ar_distributions
2686 WHERE source_table = 'RA'
2687 AND source_id in (select receivable_application_id
2688 from ar_receivable_applications
2689 where status = 'APP' and
2690 applied_customer_Trx_id = p_customer_trx_id and
2691 cash_receipt_id is not null )
2692 AND ref_customer_trx_line_id = l_customer_trx_line_id
2693 AND activity_bucket in ('ED_LINE', 'UNED_LINE')
2694 AND ref_account_class = 'REV';
2695
2696 /*Bug6906707, End */
2697 -- derive Line credit in entered and base currencies
2698 -- Bug 11715598
2699 SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
2700 NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
2701 INTO
2702 l_line_credits,
2703 l_base_line_credits
2704 from ar_receivable_applications rec,
2705 ar_distributions dist
2706 where rec.applied_customer_trx_id = p_customer_trx_id
2707 and dist.ref_customer_trx_line_id = l_customer_trx_line_id
2708 and rec.status = 'APP'
2709 and rec.application_type = 'CM'
2710 and dist.source_table = 'RA'
2711 and dist.source_id = rec.receivable_application_id
2712 and activity_bucket = 'APP_LINE'
2713 and ref_account_class = 'REV';
2714
2715 -- derive tax credit in entered and base currencies
2716 -- Bug 11715598
2717 SELECT NVL(sum(NVL(amount_dr,0) - NVL(amount_cr,0)),0),
2718 NVL(sum(NVL(acctd_amount_dr,0) - NVL(acctd_amount_cr,0)),0)
2719 INTO
2720 l_tax_credits,
2721 l_base_tax_credits
2722 from ar_receivable_applications rec,
2723 ar_distributions dist
2724 where rec.applied_customer_trx_id = p_customer_trx_id
2725 and dist.ref_customer_trx_line_id = l_customer_trx_line_id
2726 and rec.status = 'APP'
2727 and rec.application_type = 'CM'
2728 and dist.source_table = 'RA'
2729 and dist.source_id = rec.receivable_application_id
2730 and activity_bucket = 'APP_TAX'
2731 and ref_account_class = 'REV';
2732
2733
2734 -- derive line adjustment in entered and base currencies
2735 /*Bug6821893 */
2736
2737 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2738 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2739 INTO l_line_adjustments,
2740 l_base_line_adjustments
2741 from ar_distributions dist
2742 where dist.ref_customer_trx_line_id = l_customer_trx_line_id
2743 and dist.source_table = 'ADJ'
2744 and dist.activity_bucket = 'ADJ_LINE';
2745
2746
2747 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2748 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2749 INTO l_tax_adjustments,
2750 l_base_tax_adjustments
2751 from ar_distributions dist,
2752 ra_customer_trx_lines lines
2753 where lines.link_to_cust_trx_line_id = l_customer_trx_line_id
2754 and lines.line_type = 'TAX'
2755 and dist.ref_customer_trx_line_id = lines.customer_trx_line_id
2756 and dist.source_table = 'ADJ'
2757 and dist.activity_bucket = 'ADJ_TAX';
2758
2759
2760 SELECT NVL(sum(NVL(amount_cr,0) - NVL(amount_dr,0)),0),
2761 NVL(sum(NVL(acctd_amount_cr,0) - NVL(acctd_amount_dr,0)),0)
2762 INTO l_freight_adjustments,
2763 l_base_freight_adjustments
2764 from ar_distributions dist,
2765 ra_customer_trx_lines lines
2766 where lines.link_to_cust_trx_line_id = l_customer_trx_line_id
2767 and dist.ref_customer_trx_line_id = lines.link_to_cust_trx_line_id
2768 and lines.line_type = 'FREIGHT'
2769 and dist.source_table = 'ADJ'
2770 and dist.activity_bucket = 'ADJ_FRT';
2771
2772
2773 -- derive the balances for the GROUP ID
2774 ELSIF (p_group_id IS NOT NULL) THEN
2775
2776 -- line original, tax original, freight original (entered currencies)
2777 arp_util.debug('group amounts');
2778 END IF;
2779
2780
2781 -- get total amounts (entered currency)
2782 l_total_original := l_line_original + l_tax_original + l_freight_original;
2783
2784 l_total_receipts := l_line_receipts + l_tax_receipts + l_freight_receipts +
2785 l_charges_receipts;
2786 l_total_discount := l_line_discount + l_tax_discount + l_freight_discount;
2787
2788 l_total_credits := l_line_credits + l_tax_credits;
2789
2790 -- Bug 11715598
2791 l_base_total_credits := l_base_line_credits + l_base_tax_credits;
2792
2793 l_total_adjustments := l_line_adjustments + l_tax_adjustments
2794 + l_freight_adjustments; /*Bug6821893*/
2795
2796 l_base_total_adjustments := l_base_line_adjustments + l_base_tax_adjustments
2797 + l_base_freight_adjustments; /*Bug6821893*/
2798
2799 -- get functional currencies.
2800 IF ( l_line_original IS NOT NULL ) THEN
2801 l_base_line_original := arpcurr.functional_amount(
2802 l_line_original,
2803 l_base_curr_code,
2804 p_exchange_rate,
2805 l_base_precision,
2806 l_base_min_acc_unit);
2807 END IF;
2808
2809 IF ( l_tax_original IS NOT NULL ) THEN
2810 l_base_tax_original := arpcurr.functional_amount(
2811 l_tax_original,
2812 l_base_curr_code,
2813 p_exchange_rate,
2814 l_base_precision,
2815 l_base_min_acc_unit);
2816 END IF;
2817
2818 IF ( l_freight_original IS NOT NULL ) THEN
2819 l_base_freight_original := arpcurr.functional_amount(
2820 l_freight_original,
2821 l_base_curr_code,
2822 p_exchange_rate,
2823 l_base_precision,
2824 l_base_min_acc_unit);
2825 END IF;
2826
2827 IF (l_total_original IS NOT NULL) THEN
2828 l_base_total_original := l_base_line_original +
2829 l_base_tax_original +
2830 l_base_freight_original;
2831 END IF;
2832
2833 IF (l_tax_receipts IS NOT NULL ) THEN
2834 l_base_tax_receipts := arpcurr.functional_amount(
2835 l_tax_receipts,
2836 l_base_curr_code,
2837 p_exchange_rate,
2838 l_base_precision,
2839 l_base_min_acc_unit);
2840 END IF;
2841
2842 IF (l_charges_receipts IS NOT NULL ) THEN
2843 l_base_charges_receipts := arpcurr.functional_amount(
2844 l_charges_receipts,
2845 l_base_curr_code,
2846 p_exchange_rate,
2847 l_base_precision,
2848 l_base_min_acc_unit);
2849 END IF;
2850
2851 IF (l_total_receipts IS NOT NULL) THEN
2852 l_base_total_receipts := l_base_line_receipts +
2853 l_base_tax_receipts +
2854 l_base_freight_receipts +
2855 l_base_charges_receipts;
2856 END IF;
2857
2858 IF (l_freight_discount IS NOT NULL ) THEN
2859 l_base_freight_discount := arpcurr.functional_amount(
2860 l_freight_discount,
2861 l_base_curr_code,
2862 p_exchange_rate,
2863 l_base_precision,
2864 l_base_min_acc_unit);
2865 END IF;
2866
2867 IF (l_total_discount IS NOT NULL) THEN
2868 l_base_total_discount := l_base_line_discount +
2869 l_base_tax_discount +
2870 l_base_freight_discount;
2871 END IF;
2872
2873 /*-----------------------------------------+
2874 | Calculate remaining |
2875 +-----------------------------------------*/
2876 /*Bug6821893, included adjustment amounts to calculate line_remaining,
2877 tax_remaining, total_remaining, base_line_remaining, base_tax_remaining
2878 and base_total_remaining */
2879
2880 -- Bug 11715598
2881
2882 l_line_remaining := l_line_original - l_line_receipts -
2883 l_line_discount + l_line_adjustments +
2884 l_line_credits;
2885 l_tax_remaining := l_tax_original - l_tax_receipts -
2886 l_tax_discount + l_tax_adjustments +
2887 l_tax_credits;
2888 l_freight_remaining := l_freight_original - l_freight_receipts -
2889 l_freight_discount + l_freight_adjustments;
2890 l_charges_remaining := l_charges_receipts;
2891 l_total_remaining := l_total_original - l_total_receipts -
2892 l_total_discount + l_total_adjustments +
2893 l_total_credits;
2894
2895 l_base_line_remaining := l_base_line_original - l_base_line_receipts -
2896 l_base_line_discount + l_base_line_adjustments +
2897 l_base_line_credits;
2898 l_base_tax_remaining := l_base_tax_original - l_base_tax_receipts -
2899 l_base_tax_discount + l_base_tax_adjustments +
2900 l_base_tax_credits;
2901 l_base_freight_remaining := l_base_freight_original -
2902 l_base_freight_receipts -
2903 l_base_freight_discount +
2904 l_base_freight_adjustments;
2905 l_base_charges_remaining := l_base_charges_receipts;
2906 l_base_total_remaining := l_base_total_original -
2907 l_base_total_receipts -
2908 l_base_total_discount +
2909 l_base_total_adjustments +
2910 l_base_total_credits;
2911
2912 /*-----------------------------------------+
2913 | copy local variables to out variables |
2914 +-----------------------------------------*/
2915 p_line_original := l_line_original;
2916 p_tax_original := l_tax_original;
2917 p_freight_original := l_freight_original;
2918 p_total_original := l_total_original;
2919
2920 p_base_line_original := l_base_line_original;
2921 p_base_tax_original := l_base_tax_original;
2922 p_base_freight_original := l_base_freight_original;
2923 p_base_total_original := l_base_total_original;
2924
2925 p_line_receipts := l_line_receipts;
2926 p_tax_receipts := l_tax_receipts;
2927 p_freight_receipts := l_freight_receipts;
2928 p_charges_receipts := l_charges_receipts;
2929 p_total_receipts := l_total_receipts;
2930
2931 p_base_line_receipts := l_base_line_receipts;
2932 p_base_tax_receipts := l_base_tax_receipts;
2933 p_base_freight_receipts := l_base_freight_receipts;
2934 p_base_charges_receipts := l_base_charges_receipts;
2935 p_base_total_receipts := l_base_total_receipts;
2936
2937 p_line_discount := l_line_discount;
2938 p_tax_discount := l_tax_discount;
2939 p_freight_discount := l_freight_discount;
2940 p_total_discount := l_total_discount;
2941
2942 p_base_line_discount := l_base_line_discount;
2943 p_base_tax_discount := l_base_tax_discount;
2944 p_base_freight_discount := l_base_freight_discount;
2945 p_base_total_discount := l_base_total_discount;
2946
2947 p_line_remaining := l_line_remaining;
2948 p_tax_remaining := l_tax_remaining;
2949 p_freight_remaining := l_freight_remaining;
2950 p_charges_remaining := l_charges_remaining;
2951 p_total_remaining := l_total_remaining;
2952
2953 p_base_line_remaining := l_base_line_remaining;
2954 p_base_tax_remaining := l_base_tax_remaining;
2955 p_base_freight_remaining := l_base_freight_remaining;
2956 p_base_charges_remaining := l_base_charges_remaining;
2957 p_base_total_remaining := l_base_total_remaining;
2958
2959 p_line_credits := l_line_credits;
2960 p_base_line_credits := l_base_line_credits;
2961 p_tax_credits := l_tax_credits;
2962 p_base_tax_credits := l_base_tax_credits;
2963 p_total_credits := l_total_credits;
2964 p_base_total_credits := l_base_total_credits;
2965
2966 /*Bug6821893 */
2967 p_line_adjustments := l_line_adjustments;
2968 p_tax_adjustments := l_tax_adjustments;
2969 p_freight_adjustments := l_freight_adjustments;
2970 p_total_adjustments := l_total_adjustments;
2971 p_base_line_adjustments := l_base_line_adjustments;
2972 p_base_tax_adjustments := l_base_tax_adjustments;
2973 p_base_freight_adjustments := l_base_freight_adjustments;
2974 p_base_total_adjustments := l_base_total_adjustments;
2975
2976 --
2977 END trx_line_balances;
2978
2979 END ARP_BAL_UTIL;