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