1 PACKAGE BODY arp_util AS
2 /*$Header: ARCUTILB.pls 120.26.12020000.2 2012/07/28 00:49:14 dgaurab ship $*/
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5 -----------------------------------------------------------------------------
6 -- Debugging functions
7 -----------------------------------------------------------------------------
8 PROCEDURE enable_debug is
9 BEGIN
10 arp_standard.enable_debug;
11 END;
12 --
13 --
14 PROCEDURE enable_debug( buffer_size NUMBER ) is
15 BEGIN
16 arp_standard.enable_debug( buffer_size );
17 END;
18 --
19 --
20 --
21 PROCEDURE disable_debug is
22 BEGIN
23 arp_standard.disable_debug;
24 END;
25 --
26 --
27 --
28 PROCEDURE debug( line in varchar2 ) is
29 BEGIN
30 arp_debug.debug(line);
31 END;
32 --
33 --
34 --
35 PROCEDURE debug( str VARCHAR2, print_level NUMBER ) IS
36 BEGIN
37
38 debug( str );
39
40 END debug;
41 --
42 --
43 --
44 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
45 BEGIN
46
47 debug( p_label || ' ' || to_char(sysdate, 'DD-MON-YY HH:MI:SS'));
48
49 END print_fcn_label;
50 --
51 --
52 --
53 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
54 BEGIN
55
56 debug( p_label || ' ' || to_char(sysdate, 'DD-MON-YY HH:MI:SS'));
57
58 END print_fcn_label2;
59 --
60 --
61 --
62
63
64 -----------------------------------------------------------------------------
65 -- Amount functions
66 -----------------------------------------------------------------------------
67 FUNCTION functional_amount(amount IN NUMBER,
68 currency_code IN VARCHAR2,
69 exchange_rate IN NUMBER,
70 precision IN NUMBER,
71 min_acc_unit IN NUMBER) RETURN NUMBER IS
72
73 BEGIN
74 RETURN( arp_standard.functional_amount(amount,currency_code,
75 exchange_rate,
76 precision,
77 min_acc_unit ));
78 END functional_amount;
79
80
81 -- Bug5041260
82 FUNCTION func_amount(amount IN NUMBER,
83 currency_code IN VARCHAR2,
84 exchange_rate IN NUMBER,
85 precision IN NUMBER,
86 min_acc_unit IN NUMBER) RETURN NUMBER IS
87
88 /*----------------------------------------------------------------------------*
89 | PRIVATE CURSOR |
90 | curr_info |
91 | |
92 | DESCRIPTION |
93 | Gets the precision and the minimum accountable unit for the currency |
94 | Supplied |
95 | |
96 *----------------------------------------------------------------------------*/
97
98 CURSOR curr_info (cc FND_CURRENCIES.CURRENCY_CODE%TYPE) IS
99 SELECT PRECISION,
100 MINIMUM_ACCOUNTABLE_UNIT,
101 CURRENCY_CODE
102 FROM FND_CURRENCIES
103 WHERE CURRENCY_CODE = cc;
104
105 /*---------------------------------------------------------------------------*
106 | PRIVATE DATATYPES |
107 | |
108 *---------------------------------------------------------------------------*/
109
110
111 curr curr_info%ROWTYPE;
112 loc_amount NUMBER;
113 invalid_params EXCEPTION;
114
115 BEGIN
116
117
118 /*--------------------------------------------------------------------*
119 | Validate Parameters |
120 *--------------------------------------------------------------------*/
121
122 IF (((currency_code IS NULL) AND
123 (precision IS NULL) AND
124 (min_acc_unit IS NULL)) OR
125 (amount IS NULL) ) THEN
126 BEGIN
127
128 /* fnd_message('STD-FUNCT-AMT-INV-PAR'); */
129
130 RAISE invalid_params;
131
132 END;
133 END IF;
134
135 /*--------------------------------------------------------------------*
136 | Only get currency info from database if not supplied as parameters |
137 *--------------------------------------------------------------------*/
138
139
140 IF ((precision IS NULL) AND (min_acc_unit IS NULL)) THEN
141 BEGIN
142 OPEN curr_info(currency_code);
143 FETCH curr_info INTO curr;
144 CLOSE curr_info;
145
146 IF (curr.currency_code IS NULL) THEN
147
148 /* fnd_message('STD-FUNCT-AMT-CURR-NF',
149 'CURR',
150 currency_code); */
151
152 RAISE invalid_params;
153
154 END IF;
155
156 END;
157 ELSE
158 BEGIN
159 curr.precision := precision;
160 curr.minimum_accountable_unit := min_acc_unit;
161 END;
162 END IF;
163
164 loc_amount := amount * NVL(exchange_rate, 1);
165
166 /*-----------------*
167 | Round correctly |
168 *-----------------*/
169
170 IF (curr.minimum_accountable_unit IS NULL) THEN
171 RETURN( ROUND(loc_amount, curr.precision));
172 ELSE
173 RETURN( ROUND((loc_amount / curr.minimum_accountable_unit)) *
174 curr.minimum_accountable_unit);
175 END IF;
176
177 EXCEPTION
178 WHEN OTHERS THEN
179 -- Bug 2191876
180 IF curr_info%ISOPEN THEN
181 CLOSE curr_info;
182 END IF;
183 RAISE;
184
185 END func_amount;
186
187
188 --
189 -- This function returns the amount to the correct presicion
190 -- for the currency code you passed in.
191 -- If P_exchange_rate is not passed, it would be default to 1
192 -- If P_currency_code is not passed, it would be default to its
193 -- functional currency.
194 --
195
196 FUNCTION calc_dynamic_amount(
197 P_amount IN NUMBER,
198 P_exchange_rate IN NUMBER,
199 P_currency_code IN fnd_currencies.currency_code%TYPE )
200 RETURN NUMBER IS
201
202 l_precision fnd_currencies.precision%TYPE;
203 l_ext_precision fnd_currencies.extended_precision%TYPE;
204 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
205 l_format_mask VARCHAR2(45);
206
207 BEGIN
208 --
209 IF PG_DEBUG in ('Y', 'C') THEN
210 debug('arp_util.calc_dynamic_amount()+' );
211 END IF;
212
213 /*
214 FND_CURRENCY.get_info( nvl(P_currency_code,ARP_GLOBAL.functional_currency),
215 l_precision, l_ext_precision, l_min_acct_unit );
216
217 FND_CURRENCY.build_format_mask( l_format_mask,45,l_precision,
218 l_min_acct_unit,FALSE,'-XXX','XXX');
219 */
220
221 IF PG_DEBUG in ('Y', 'C') THEN
222 debug('arp_util.calc_dynamic_amount()-' );
223 END IF;
224
225 return( TO_NUMBER( TO_CHAR( P_amount*nvl(P_exchange_rate,1),
226 l_format_mask ) ) );
227
228 EXCEPTION
229 WHEN OTHERS THEN
230 IF PG_DEBUG in ('Y', 'C') THEN
231 debug( 'Exception: arp_util.calc_dynamic_amount' );
232 END IF;
233 RAISE;
234
235 END calc_dynamic_amount;
236 --
237 --
238 --
239 FUNCTION CurrRound( p_amount IN NUMBER,
240 p_currency_code IN VARCHAR2)
241 RETURN NUMBER IS
242 BEGIN
243
244 RETURN( arpcurr.CurrRound( p_amount, p_currency_code ) );
245
246 END;
247
248
249 /*===========================================================================+
250 | PROCEDURE |
251 | calc_acctd_amount |
252 | |
253 | DESCRIPTION |
254 | Converts a 'detail' amount to a foreign currency. |
255 | The basis of the calculation is that the accounted equivalent of a |
256 | 'detail' amount is the change in the accounted amount that the detail |
257 | produced in some 'master' record. |
258 | |
259 | eg an adjustment is a 'detail' amount that results in a change to |
260 | the amount_due_remaining in the 'master' record |
261 | (ar_payment_schedules) |
262 | |
263 | the routine can take a currency as a parameter, in which case the |
264 | precision and minimum_accountable_unit for that currency will be used |
265 | |
266 | if no currency is sent, the routine can use the precision and |
267 | minimum_accountable_unit if sent |
268 | |
269 | if the precision and minimum_accountable_unit are not sent, then |
270 | the routine will use the precision and minimum_accountable_unit |
271 | of the set of books currency |
272 | |
273 | SCOPE - PRIVATE |
274 | |
275 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
276 | |
277 | ARGUMENTS |
278 | IN: |
279 | p_currency - the currency to be converted to (optional) |
280 | p_precision - the precision (optional) |
281 | p_mau - the minimum accountable unit (optional) |
282 | p_rate - the exchange rate (mandatory) |
283 | p_type - takes value '+' or '-' defaults to '+'. '+' |
284 | means that the detail amount will be used to |
285 | increase the master amount. |
286 | '-' means that the detail amount will be used to |
287 | decrease the master amount |
288 | p_master_from - the original amount on the master record (in |
289 | foreign currency |
290 | p_detail - the foreign amount on the detail |
291 | record (mandatory) |
292 | IN OUT: |
293 | p_acctd_master_from |
294 | - the accounted equivalent of the master record |
295 | (optional) |
296 | OUT: |
297 | p_master_to - returns the new foreign value of the master |
298 | record. ie master_from +- detail |
299 | p_acctd_master_to - returns the master_to converted to accounted |
300 | currency |
301 | p_acctd_detail - returns the accounted equivalent of detail |
302 | |
303 | RETURNS |
304 | |
305 | NOTES |
306 | Unlike the Pro*C equivalent of this function, aracc(), this PL/SQL |
307 | function has no optional output parameters, i.e., in function calls |
308 | NULL cannot be specified for the IN OUT NOCOPY or OUT NOCOPY variables. |
309 | |
310 | MODIFICATION HISTORY |
311 | |
312 | 21-AUG-95 OSTEINME created |
313 | 2/22/1996 H.Kaukovuo Added debug messages to print out NOCOPY parameters
314 | Modified procedure to use standard functional
315 | amount procedure.
316 | Changed procedure to consider p_rate = 1 as
317 | functional currency (was 0 before).
318 | 2/23/1996 H.Kaukovuo Fixed own bug, was returing ORA6512.
319 | 3/1/1996 H.Kaukovuo Fixed bug where p_master_from was null and
320 | caused ORA6512
321 | 8/28/1996 H.Kaukovuo Added parameters p_precision and p_mau to
322 | call to arp_util.functional_amount().
323 | 7/26/2000 skoukunt Fix 1353061, comment code which assume
324 | If a rate <> 1 is given, the currency to be
325 | foreign
326 +===========================================================================*/
327
328 PROCEDURE calc_acctd_amount(
329 p_currency IN VARCHAR2,
330 p_precision IN NUMBER,
331 p_mau IN NUMBER,
332 p_rate IN NUMBER,
333 p_type IN VARCHAR2,
334 p_master_from IN NUMBER,
335 p_acctd_master_from IN OUT NOCOPY NUMBER,
336 p_detail IN NUMBER,
337 p_master_to IN OUT NOCOPY NUMBER,
338 p_acctd_master_to IN OUT NOCOPY NUMBER,
339 p_acctd_detail IN OUT NOCOPY NUMBER
340 ) IS
341 --
342 -- Local Variables:
343 --
344 l_functional BOOLEAN; -- flag: TRUE if functional currency
345 l_mau NUMBER; -- minimum accounting unit
346 l_precision NUMBER; -- precision
347 lc_currency_code VARCHAR2(20);
348 ln_detail_amount NUMBER;
349 ln_exchange_rate NUMBER;
350 l_acctd_detail NUMBER;
351 l_overapp_allow_flag VARCHAR2(1);
352
353 BEGIN
354
355 IF PG_DEBUG in ('Y', 'C') THEN
356 debug( 'arp_util.calc_acctd_amount()+');
357 debug( '-- p_currency = '||p_currency);
358 debug( '-- p_precision = '||to_number(p_precision));
359 debug( '-- p_mau = '||TO_NUMBER(p_mau));
360 debug( '-- p_rate = '||TO_NUMBER(p_rate));
361 debug( '-- p_type = '||p_type);
362 debug( '-- p_master_from = '||TO_NUMBER(p_master_from));
363 debug( '-- p_acctd_master_from = '||TO_NUMBER(p_acctd_master_from));
364 debug( '-- p_detail= '||TO_NUMBER(p_detail));
365 debug( '-- p_master_to = '||TO_NUMBER(p_master_to));
366 debug( '-- p_acctd_master_to = '||TO_NUMBER(p_acctd_master_to));
367 debug( '-- p_acctd_detail = '||TO_NUMBER(p_acctd_detail));
368 END IF;
369
370 -- If detail not passed default to zero
371 ln_detail_amount := NVL(p_detail,0);
372 ln_exchange_rate := NVL(p_rate,1);
373 l_overapp_allow_flag := NVL(arp_util.PG_OVERAPP_ALLOW_FLAG, 'N');
374
375 -- Determine if currency is functional currency.
376 -- If a rate <> 1 is given, assume the currency to be
377 -- foreign.
378 /*
379 -- comment to fix bug 1353061
380 IF (ln_exchange_rate = 1) THEN
381 l_functional := TRUE;
382 ELSE
383 l_functional := FALSE;
384 END IF;
385 */
386 /*
387 IF (NOT l_functional) THEN
388 IF (NOT(p_currency IS NULL)) THEN
389 SELECT minimum_accountable_unit,
390 precision
391 INTO l_mau,
392 l_precision
393 FROM fnd_currencies
394 WHERE currency_code = p_currency;
395 ELSE
396 IF (NOT( (p_precision IS NULL ) AND (p_mau IS NULL))) THEN
397 l_precision := p_precision;
398 l_mau := p_mau;
399 ELSE
400 SELECT c.minimum_accountable_unit,
401 c.precision
402 INTO l_mau,
403 l_precision
404 FROM fnd_currencies c,
405 gl_sets_of_books sob,
406 ar_system_parameters sp
407 WHERE sob.set_of_books_id = sp.set_of_books_id
408 AND c.currency_code = sob.currency_code;
409 END IF;
410 END IF;
411 END IF;
412 */
413
414 lc_currency_code := NVL(p_currency, arpcurr.FunctionalCurrency);
415
416 -- all variables are now initialized.
417 -- calculate the new p_master_to amount:
418
419 /*
420 IF (p_type = '+') THEN
421 p_master_to := p_master_from + p_detail;
422 ELSE
423 p_master_to := p_master_from - p_detail;
424 END IF;
425 */
426
427 IF (p_type = '+') THEN
428 p_master_to := p_master_from + ln_detail_amount;
429 ELSE
430 p_master_to := p_master_from - ln_detail_amount;
431 END IF;
432
433
434 -- now calculate the accounted version of master_to:
435
436 /*
437 IF (l_functional) THEN
438 p_acctd_master_to := p_master_to;
439 ELSE
440 p_acctd_master_to := p_master_to * p_rate;
441 END IF;
442 */
443
444 IF PG_DEBUG in ('Y', 'C') THEN
445 debug( 'First functional_amount call');
446 debug( '-- lc_currency_code = '|| lc_currency_code);
447 debug( '-- p_master_to = '|| to_char(p_master_to));
448 debug( '-- p_rate = '|| to_char(p_rate));
449 END IF;
450
451 IF (p_master_to IS NOT NULL)
452 THEN
453 p_acctd_master_to := arp_util.functional_amount(
454 amount => p_master_to
455 , currency_code => lc_currency_code
456 , exchange_rate => ln_exchange_rate
457 , precision => p_precision
458 , min_acc_unit => p_mau);
459 END IF;
460
461 -- calculate the accounted version of master_from:
462
463 IF PG_DEBUG in ('Y', 'C') THEN
464 debug( 'After First functional_amount call');
465 END IF;
466
467 IF (p_master_from IS NULL)
468 THEN
469 p_acctd_master_from := NULL;
470
471 ELSIF (p_acctd_master_from IS NULL AND p_master_from IS NOT NULL)
472 THEN
473 IF PG_DEBUG in ('Y', 'C') THEN
474 debug( 'Second functional_amount call');
475 END IF;
476 p_acctd_master_from := arp_util.functional_amount(
477 amount => p_master_from
478 , currency_code => lc_currency_code
479 , exchange_rate => ln_exchange_rate
480 , precision => p_precision
481 , min_acc_unit => p_mau);
482 END IF;
483
484 IF PG_DEBUG in ('Y', 'C') THEN
485 debug( 'Overriding the p_acctd_master_to in specific corner case due to rounding.');
486 debug( 'Header Level Rounding: ' || arp_global.sysparam.trx_header_level_rounding);
487 debug( 'Allow Overapplication Flag: ' || l_overapp_allow_flag);
488 debug( 'p_acctd_master_to = '|| to_char(p_acctd_master_to));
489 debug( 'p_acctd_master_from = '|| to_char(p_acctd_master_from));
490 END IF;
491
492 --Bug 13543605.
493 IF (arp_global.sysparam.trx_header_level_rounding <> 'Y' AND
494 p_acctd_master_from IS NOT NULL)
495 THEN
496 IF (p_type = '+') THEN
497 l_acctd_detail := p_acctd_master_to - p_acctd_master_from;
498 ELSE
499 l_acctd_detail := p_acctd_master_from - p_acctd_master_to;
500 END IF;
501
502 IF PG_DEBUG in ('Y', 'C') THEN
503 debug('Value for l_acctd_detail ' || l_acctd_detail);
504 debug('Sign of l_acctd_detail ' || sign(l_acctd_detail));
505 debug('Sign of ln_detail_amount ' || sign(ln_detail_amount));
506 END IF;
507
508 IF ((sign(l_acctd_detail) <> sign(ln_detail_amount)) AND
509 (l_acctd_detail <> 0) AND (ln_detail_amount <> 0) AND
510 l_overapp_allow_flag = 'Y' AND sign(p_master_from) <> sign(ln_detail_amount) AND
511 p_master_from <> 0 AND ln_detail_amount <> 0)
512 OR
513 (l_acctd_detail = 0) AND (ln_detail_amount <> 0)
514 OR
515 (l_acctd_detail <> 0) AND (ln_detail_amount = 0)
516 THEN
517 IF (p_type = '+') THEN
518 p_acctd_master_to := p_acctd_master_from + arp_util.functional_amount(
519 amount => ln_detail_amount
520 , currency_code => lc_currency_code
521 , exchange_rate => ln_exchange_rate
522 , PRECISION => p_precision
523 , min_acc_unit => p_mau);
524 ELSE
525 p_acctd_master_to := p_acctd_master_from - arp_util.functional_amount(
526 amount => ln_detail_amount
527 , currency_code => lc_currency_code
528 , exchange_rate => ln_exchange_rate
529 , PRECISION => p_precision
530 , min_acc_unit => p_mau);
531 END IF;
532
533 IF PG_DEBUG in ('Y', 'C') THEN
534 debug('Value for p_acctd_master_to ' || p_acctd_master_to);
535 END IF;
536 END IF;
537 END IF;
538
539 /*
540 IF (p_acctd_master_from IS NULL) THEN
541 IF (l_functional = TRUE) THEN
542 p_acctd_master_from := p_master_from;
543 ELSE
544 p_acctd_master_from := p_rate * p_master_from;
545 END IF;
546 END IF;
547 */
548
549 -- round the amounts
550
551 /*
552 IF (l_functional = FALSE) THEN
553 IF (l_mau IS NULL) THEN
554 p_acctd_master_to := ROUND(p_acctd_master_to, l_precision);
555 p_acctd_master_from := ROUND(p_acctd_master_from, l_precision);
556 ELSE
557 p_acctd_master_to := round(p_acctd_master_to/l_mau,0)*l_mau;
558 p_acctd_master_from := round(p_acctd_master_from/l_mau,0)*l_mau;
559 END IF;
560 END IF;
561 */
562
563 IF (l_functional = TRUE) THEN
564 p_acctd_detail := p_detail;
565 ELSE
566 /*4084266*/
567 IF p_detail = 0 AND p_master_to=p_master_from THEN
568 p_acctd_master_to:=p_acctd_master_from;
569 END IF;
570 IF (p_type = '+') THEN
571 p_acctd_detail := p_acctd_master_to - p_acctd_master_from;
572 ELSE
573 p_acctd_detail := p_acctd_master_from - p_acctd_master_to;
574 END IF;
575 END IF;
576
577 IF PG_DEBUG in ('Y', 'C') THEN
578 debug( '-- ** Procedure returns values:');
579 debug( '-- p_master_to : '||to_char(p_master_to));
580 debug( '-- p_acctd_master_to : '||to_char(p_acctd_master_to));
581 debug( '-- p_master_from : '||to_char(p_master_from));
582 debug( '-- p_acctd_master_from : '||to_char(p_acctd_master_from));
583 debug( '-- p_acctd_detail : '||to_char(p_acctd_detail));
584 debug( 'arp_util.calc_acctd_amount()-');
585 END IF;
586
587 EXCEPTION
588 WHEN OTHERS THEN
589 IF PG_DEBUG in ('Y', 'C') THEN
590 debug( 'Exception: arp_util.calc_acctd_amount()');
591 END IF;
592 RAISE;
593
594 END; -- calc_acctd_amount()
595
596
597
598 -- Bug5041260
599 PROCEDURE calc_accounted_amount(
600 p_currency IN VARCHAR2,
601 p_precision IN NUMBER,
602 p_mau IN NUMBER,
603 p_rate IN NUMBER,
604 p_type IN VARCHAR2,
605 p_master_from IN NUMBER,
606 p_acctd_master_from IN OUT NOCOPY NUMBER,
607 p_detail IN NUMBER,
608 p_master_to IN OUT NOCOPY NUMBER,
609 p_acctd_master_to IN OUT NOCOPY NUMBER,
610 p_acctd_detail IN OUT NOCOPY NUMBER
611 ) IS
612 --
613 -- Local Variables:
614 --
615 l_functional BOOLEAN; -- flag: TRUE if functional currency
616 l_mau NUMBER; -- minimum accounting unit
617 l_precision NUMBER; -- precision
618 lc_currency_code VARCHAR2(20);
619 ln_detail_amount NUMBER;
620 ln_exchange_rate NUMBER;
621
622 BEGIN
623 -- If detail not passed default to zero
624 ln_detail_amount := NVL(p_detail,0);
625 ln_exchange_rate := NVL(p_rate,1);
626
627 --Need to pass the currency code as the functional currency
628 lc_currency_code := p_currency;
629
630
631 -- all variables are now initialized.
632 -- calculate the new p_master_to amount:
633
634
635 IF (p_type = '+') THEN
636 p_master_to := p_master_from + ln_detail_amount;
637 ELSE
638 p_master_to := p_master_from - ln_detail_amount;
639 END IF;
640
641
642 IF (p_master_to IS NOT NULL)
643 THEN
644 p_acctd_master_to := arp_util.func_amount(
645 amount => p_master_to
646 , currency_code => lc_currency_code
647 , exchange_rate => ln_exchange_rate
648 , precision => p_precision
649 , min_acc_unit => p_mau);
650 END IF;
651
652 -- calculate the accounted version of master_from:
653 /*
654 IF PG_DEBUG in ('Y', 'C') THEN
655 debug( 'After First functional_amount call');
656 END IF;
657 */
658 IF (p_master_from IS NULL)
659 THEN
660 p_acctd_master_from := NULL;
661
662 ELSIF (p_acctd_master_from IS NULL and p_master_from IS NOT NULL)
663 THEN
664 IF PG_DEBUG in ('Y', 'C') THEN
665 debug( 'Second functional_amount call');
666 END IF;
667
668 p_acctd_master_from := arp_util.func_amount(
669 amount => p_master_from
670 , currency_code => lc_currency_code
671 , exchange_rate => ln_exchange_rate
672 , precision => p_precision
673 , min_acc_unit => p_mau);
674
675
676 END IF;
677
678
679
680 IF (l_functional = TRUE) THEN
681 p_acctd_detail := p_detail;
682 ELSE
683 /*4084266*/
684 IF p_detail = 0 AND p_master_to=p_master_from THEN
685 p_acctd_master_to:=p_acctd_master_from;
686 END IF;
687 IF (p_type = '+') THEN
688 p_acctd_detail := p_acctd_master_to - p_acctd_master_from;
689 ELSE
690 p_acctd_detail := p_acctd_master_from - p_acctd_master_to;
691 END IF;
692 END IF;
693
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 IF PG_DEBUG in ('Y', 'C') THEN
698 debug( 'Exception: arp_util.calc_accounted_amount()');
699 END IF;
700 RAISE;
701
702 END; -- calc_accounted_amount()
703
704
705 -- ########################## TEST FUNCTION ###############################
706 -- This function contains tests for calc_acctd_amount. It is NOT used for
707 -- the actual product.
708
709 PROCEDURE calc_acctd_amount_test IS
710 --
711 acctd_master_from NUMBER;
712 master_to NUMBER;
713 acctd_master_to NUMBER;
714 acctd_detail NUMBER;
715 --
716 BEGIN
717
718 IF PG_DEBUG in ('Y', 'C') THEN
719 debug( 'arp_util.calc_amount_test()+');
720 debug( 'Test 1:');
721 END IF;
722
723 acctd_master_from := NULL;
724 master_to := NULL;
725 acctd_master_to := NULL;
726 acctd_detail := NULL;
727
728 -- calc_acctd_amount(NULL, 2, NULL, NULL, '-', 50, acctd_master_from,
729 -- 50, master_to, acctd_master_to, acctd_detail);
730
731
732 -- calc_acctd_amount(NULL, 2, NULL, .33333333, '-', 50, acctd_master_from,
733 -- 50, master_to, acctd_master_to, acctd_detail);
734
735
736 -- calc_acctd_amount(NULL, 2, NULL, .3333333333, '+', 50, acctd_master_from,
737 -- 50, master_to, acctd_master_to, acctd_detail);
738
739
740 -- calc_acctd_amount(NULL, NULL, NULL, .3333333333, '+', 50, acctd_master_from,
741 -- 50, master_to, acctd_master_to, acctd_detail);
742
743 -- calc_acctd_amount('ITL', NULL, NULL, .3333333333, '+', 50, acctd_master_from,
744 -- 50, master_to, acctd_master_to, acctd_detail);
745
746
747 -- calc_acctd_amount(NULL, NULL, 0.25, .3333333333, '+', 50, acctd_master_from,
748 -- 50, master_to, acctd_master_to, acctd_detail);
749
750
751 -- calc_acctd_amount(NULL, 1, 0.25, .3333333333, '+', 50, acctd_master_from,
752 -- 50, master_to, acctd_master_to, acctd_detail);
753
754 acctd_master_from := 18;
755
756 calc_acctd_amount(NULL, NULL, NULL, .3333333333, '+', 50, acctd_master_from, 50, master_to, acctd_master_to, acctd_detail);
757
758
759
760 END; -- test()
761
762
763
764
765 -----------------------------------------------------------------------------
766 -- Date functions
767 -----------------------------------------------------------------------------
768 --
769 --
770 -- FUNCTION NAME validate_and_default_gl_date
771 --
772 -- DESCRIPTION
773 -- This is a just a stub to call the validate_and_default_gl_date in
774 -- ARP_STANDARD package
775 --
776 -- PUBLIC PROCEDURES/FUNCTIONS
777 --
778 -- PRIVATE PROCEDURES/FUNCTIONS
779 --
780 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
781 --
782 -- PARAMETERS - Look at ARP_STANDARD.validate_and_default_gl_date
783 --
784 -- HISTORY
785 -- 10-OCT-95 G vaidees Created
786 --
787 --
788 FUNCTION validate_and_default_gl_date(
789 gl_date in date,
790 trx_date in date,
791 validation_date1 in date,
792 validation_date2 in date,
793 validation_date3 in date,
794 default_date1 in date,
795 default_date2 in date,
796 default_date3 in date,
797 p_allow_not_open_flag in varchar2,
798 p_invoicing_rule_id in varchar2,
799 p_set_of_books_id in number,
800 p_application_id in number,
801 default_gl_date out NOCOPY date,
802 defaulting_rule_used out NOCOPY varchar2,
803 error_message out NOCOPY varchar2
804 ) RETURN BOOLEAN IS
805 l_result BOOLEAN;
806 BEGIN
807 IF PG_DEBUG in ('Y', 'C') THEN
808 debug( 'arp_util.validate_and_default_gl_date()+' );
809 END IF;
810 --
811 l_result := arp_standard.validate_and_default_gl_date(
812 gl_date, trx_date,
813 validation_date1, validation_date2, validation_date3,
814 default_date1, default_date2, default_date3,
815 p_allow_not_open_flag, p_invoicing_rule_id,
816 p_set_of_books_id, p_application_id,
817 default_gl_date, defaulting_rule_used, error_message );
818 --
819 IF PG_DEBUG in ('Y', 'C') THEN
820 debug( 'arp_util.validate_and_default_gl_date()-' );
821 END IF;
822 --
823 RETURN l_result;
824 --
825 EXCEPTION
826 WHEN OTHERS THEN
827 IF PG_DEBUG in ('Y', 'C') THEN
828 debug( 'EXCEPTION: arp_util.validate_and_default_gl_date' );
829 END IF;
830 RAISE;
831 END validate_and_default_gl_date;
832
833
834 --
835 -- FUNCTION NAME validate_and_default_gl_date
836 --
837 -- DESCRIPTION
838 -- Procedure to validate and default gl date for a given date and also
839 -- return the period name corresponding to the validated gl date.
840 --
841 -- PUBLIC PROCEDURES/FUNCTIONS
842 --
843 -- PRIVATE PROCEDURES/FUNCTIONS
844 --
845 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
846 -- arp_standard.validate_and_default_gl_date
847 -- arp_standard.gl_period_name
848 --
849 -- PARAMETERS - Look at ARP_STANDARD.validate_and_default_gl_date
850 -- p_period_name
851 -- HISTORY
852 -- 21-NOV-95 Subash C Created
853 --
854 --
855
856 FUNCTION validate_and_default_gl_date(
857 gl_date in date,
858 trx_date in date,
859 validation_date1 in date,
860 validation_date2 in date,
861 validation_date3 in date,
862 default_date1 in date,
863 default_date2 in date,
864 default_date3 in date,
865 p_allow_not_open_flag in varchar2,
866 p_invoicing_rule_id in varchar2,
867 p_set_of_books_id in number,
868 p_application_id in number,
869 default_gl_date out NOCOPY date,
870 defaulting_rule_used out NOCOPY varchar2,
871 error_message out NOCOPY varchar2,
872 p_period_name out NOCOPY varchar2
873 ) RETURN BOOLEAN IS
874 l_result BOOLEAN;
875 l_default_gl_date date;
876 BEGIN
877 IF PG_DEBUG in ('Y', 'C') THEN
878 debug( 'arp_util.validate_and_default_gl_date()+' );
879 END IF;
880 --
881 l_result := arp_standard.validate_and_default_gl_date(
882 gl_date, trx_date,
883 validation_date1, validation_date2, validation_date3,
884 default_date1, default_date2, default_date3,
885 p_allow_not_open_flag, p_invoicing_rule_id,
886 p_set_of_books_id, p_application_id,
887 l_default_gl_date, defaulting_rule_used, error_message );
888
889 default_gl_date := l_default_gl_date;
890
891 --
892 -- get period name for the gl date
893 --
894 p_period_name := arp_standard.gl_period_name(l_default_gl_date);
895
896 IF PG_DEBUG in ('Y', 'C') THEN
897 debug( 'arp_util.validate_and_default_gl_date()-' );
898 END IF;
899 --
900 RETURN l_result;
901 --
902
903 EXCEPTION
904 WHEN OTHERS THEN
905 IF PG_DEBUG in ('Y', 'C') THEN
906 debug( 'EXCEPTION: arp_util.validate_and_default_gl_date' );
907 END IF;
908 RAISE;
909
910 END validate_and_default_gl_date;
911
912 --
913 --
914 --
915 FUNCTION is_gl_date_valid( p_gl_date IN DATE )
916 RETURN BOOLEAN IS
917
918 l_num_return_value NUMBER;
919 l_bool BOOLEAN;
920
921 BEGIN
922 IF PG_DEBUG in ('Y', 'C') THEN
923 debug( 'arp_util.is_gl_date_valid()+' );
924 END IF;
925
926 IF ( p_gl_date is NULL ) THEN
927
928 IF PG_DEBUG in ('Y', 'C') THEN
929 debug( 'arp_util.is_gl_date_valid()-' );
930 END IF;
931 RETURN FALSE;
932
933 END IF;
934
935 l_bool := arp_standard.is_gl_date_valid( p_gl_date,
936 NULL, -- trx_date
937 NULL, -- validation_date1
938 NULL, -- validation_date2
939 NULL, -- validation_date3
940 'N', -- allow_not_open_flag
941 arp_global.set_of_books_id,
942 arp_global.g_ar_app_id,
943 TRUE -- check_period_status
944 );
945
946 IF PG_DEBUG in ('Y', 'C') THEN
947 debug( 'arp_util.is_gl_date_valid()-' );
948 END IF;
949 RETURN l_bool;
950
951 EXCEPTION
952 WHEN OTHERS THEN
953 IF PG_DEBUG in ('Y', 'C') THEN
954 debug( 'EXCEPTION: arp_util.is_gl_date_valid()' );
955 END IF;
956 RAISE;
957 END is_gl_date_valid;
958 --
959 --
960 FUNCTION is_gl_date_valid( p_gl_date IN DATE,
961 p_allow_not_open_flag IN VARCHAR )
962 RETURN BOOLEAN IS
963
964 l_num_return_value NUMBER;
965 l_bool BOOLEAN;
966
967 BEGIN
968 IF PG_DEBUG in ('Y', 'C') THEN
969 debug( 'arp_util.is_gl_date_valid()+' );
970 END IF;
971
972 IF( p_gl_date is NULL ) THEN
973
974 IF PG_DEBUG in ('Y', 'C') THEN
975 debug( 'arp_util.is_gl_date_valid()-' );
976 END IF;
977 RETURN FALSE;
978
979 END IF;
980
981 l_bool := arp_standard.is_gl_date_valid( p_gl_date,
982 NULL, -- trx_date
983 NULL, -- validation_date1
984 NULL, -- validation_date2
985 NULL, -- validation_date3
986 p_allow_not_open_flag,
987 arp_global.set_of_books_id,
988 arp_global.g_ar_app_id,
989 TRUE -- check_period_status
990 );
991
992 IF PG_DEBUG in ('Y', 'C') THEN
993 debug( 'arp_util.is_gl_date_valid()-' );
994 END IF;
995 RETURN l_bool;
996
997 EXCEPTION
998 WHEN OTHERS THEN
999 IF PG_DEBUG in ('Y', 'C') THEN
1000 debug( 'EXCEPTION: arp_util.is_gl_date_valid()' );
1001 END IF;
1002 RAISE;
1003 END is_gl_date_valid;
1004 --
1005 --
1006 --
1007 -- PROCEDURE NAME validate_gl_date
1008 --
1009 -- DESCRIPTION
1010 -- Validates GL date. This procedure just calls the is_gl_date_valid
1011 -- function and raises an exception depending on the return value
1012 --
1013 -- PUBLIC PROCEDURES/FUNCTIONS
1014 --
1015 -- PRIVATE PROCEDURES/FUNCTIONS
1016 --
1017 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1018 --
1019 -- PARAMETERS
1020 -- p_gl_date - Input GL date
1021 --
1022 -- HISTORY
1023 -- 14-JUL-95 G vaidees Created
1024 --
1025 PROCEDURE validate_gl_date( p_gl_date IN DATE,
1026 p_module_name IN VARCHAR2,
1027 p_module_version IN VARCHAR2 ) IS
1028 l_ret_code BOOLEAN;
1029
1030 BEGIN
1031 IF PG_DEBUG in ('Y', 'C') THEN
1032 debug( 'arp_util.validate_gl_date()+' );
1033 END IF;
1034 --
1035 l_ret_code := is_gl_date_valid( p_gl_date );
1036 IF ( l_ret_code = FALSE ) THEN
1037 FND_MESSAGE.set_name( 'AR', 'AR_INVALID_APP_GL_DATE' );
1038 FND_MESSAGE.set_token( 'GL_DATE', TO_CHAR( p_gl_date ) );
1039 APP_EXCEPTION.raise_exception;
1040 END IF;
1041 --
1042 IF PG_DEBUG in ('Y', 'C') THEN
1043 debug( 'arp_util.validate_gl_date()-' );
1044 END IF;
1045 --
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 IF PG_DEBUG in ('Y', 'C') THEN
1049 debug( 'EXCEPTION: arp_util.validate_gl_date' );
1050 END IF;
1051 RAISE;
1052 END validate_gl_date;
1053
1054 --
1055 --
1056 -- PROCEDURE NAME validate_gl_date
1057 --
1058 -- DESCRIPTION
1059 -- Overloaded procedure to validate GL date and to get period name
1060 -- corresponding to the GL date.
1061 --
1062 -- PUBLIC PROCEDURES/FUNCTIONS
1063 --
1064 -- PRIVATE PROCEDURES/FUNCTIONS
1065 --
1066 -- EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1067 -- arp_standard.gl_period_name
1068 --
1069 -- PARAMETERS
1070 -- p_gl_date - Input GL date
1071 -- p_module_name - Input module name
1072 -- p_module_version - Input module version
1073 -- p_period_name - Output period name
1074 --
1075 -- HISTORY
1076 -- 21-NOV-95 Subash C Created
1077 --
1078
1079 PROCEDURE validate_gl_date( p_gl_date IN DATE,
1080 p_module_name IN VARCHAR2,
1081 p_module_version IN VARCHAR2,
1082 p_period_name OUT NOCOPY varchar2 ) IS
1083 l_ret_code BOOLEAN;
1084
1085 BEGIN
1086 IF PG_DEBUG in ('Y', 'C') THEN
1087 debug( 'arp_util.validate_gl_date()+' );
1088 END IF;
1089 --
1090 validate_gl_date(p_gl_date,
1091 p_module_name,
1092 p_module_version);
1093
1094 --
1095 -- get period name
1096 --
1097 p_period_name := arp_standard.gl_period_name(p_gl_date);
1098
1099 IF PG_DEBUG in ('Y', 'C') THEN
1100 debug( 'arp_util.validate_gl_date()-' );
1101 END IF;
1102
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 IF PG_DEBUG in ('Y', 'C') THEN
1106 debug( 'EXCEPTION: arp_util.validate_gl_date' );
1107 END IF;
1108 RAISE;
1109 END validate_gl_date;
1110
1111 --
1112 --
1113 -----------------------------------------------------------------------------
1114 -- Misc functions
1115 -----------------------------------------------------------------------------
1116 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY NUMBER ) IS
1117 BEGIN
1118
1119 IF( dbms_sql.is_open( p_cursor_handle ) ) THEN
1120 dbms_sql.close_cursor( p_cursor_handle );
1121 END IF;
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 debug('EXCEPTION: arp_util.close_cursor()', arp_global.MSG_LEVEL_BASIC);
1126 RAISE;
1127 END close_cursor;
1128
1129 /* ==================================================================================
1130 | PROCEDURE Set_Buckets
1131 |
1132 | DESCRIPTION
1133 | Sets accounted amount base for tax, charges, freight, line
1134 | from amount buckets of the Receivable application or adjustment.
1135 | We do not store accounted amounts for individual buckets in the
1136 | payment schedule or on application or adjustment. Hence the accounted
1137 | amounts are derived by this routine in order, Tax, Charges, Line and
1138 | Freight by using the foreign currency amounts and multiplying with the
1139 | exchange rate to get the base or functional currency accounted amounts
1140 | with the rounding correction going to the last non zero amount
1141 | bucket in that order. This is the standard that has been established and
1142 | the same algorithm must be used to retain consistency. The usage came
1143 | into being during the Tax accounting for Discounts and Adjustments,
1144 | however in future projects this will be required. This could not be
1145 | derived as an effect on payment schedule becuause the payment schedules
1146 | are update before or after activity by various modules. In addition
1147 | depending on the bucket which is first choosen to be calculated the
1148 | rounding correction is different and goes to the last bucket. The
1149 | approach by this routine is the most desirable way to do things.
1150 |
1151 | SCOPE - PUBLIC
1152 |
1153 | PARAMETERS
1154 | p_header_acctd_amt IN Header accounted amount to reconcile
1155 | p_base_currency IN Base or functional currency
1156 | p_exchange_rate IN Exchange rate
1157 | p_base_precision IN Base precision
1158 | p_base_min_acc_unit IN Minimum accountable unit
1159 | p_tax_amt IN Tax amount in currency of Transaction
1160 | p_charges_amt IN Charges amount in currency of Transaction
1161 | p_freight_amt IN Freight amount in currency of Transaction
1162 | p_line_amt IN Line amount in currency of Transaction
1163 | p_tax_acctd_amt IN OUT NOCOPY Tax accounted amount in functional currency
1164 | p_charges_acctd_amt IN OUT NOCOPY Charges accounted amount in functional currency
1165 | p_freight_acctd_amt IN OUT NOCOPY Freight accounted amount in functional currency
1166 | p_line_acctd_amt IN OUT NOCOPY Line accounted amount in functional currency
1167 |
1168 | Notes
1169 | Introduced for 11.5 Tax accounting - used by ARALLOCB.pls and ARTWRAPB.pls
1170 *===================================================================================*/
1171 PROCEDURE Set_Buckets(
1172 p_header_acctd_amt IN NUMBER ,
1173 p_base_currency IN fnd_currencies.currency_code%TYPE,
1174 p_exchange_rate IN NUMBER ,
1175 p_base_precision IN NUMBER ,
1176 p_base_min_acc_unit IN NUMBER ,
1177 p_tax_amt IN NUMBER ,
1178 p_charges_amt IN NUMBER ,
1179 p_line_amt IN NUMBER ,
1180 p_freight_amt IN NUMBER ,
1181 p_tax_acctd_amt IN OUT NOCOPY NUMBER ,
1182 p_charges_acctd_amt IN OUT NOCOPY NUMBER ,
1183 p_line_acctd_amt IN OUT NOCOPY NUMBER ,
1184 p_freight_acctd_amt IN OUT NOCOPY NUMBER ) IS
1185
1186 l_run_amt_tot NUMBER;
1187 l_run_acctd_amt_tot NUMBER;
1188 l_last_bucket VARCHAR2(1);
1189
1190 /* Bug 2013601
1191 Variables to hold running total of amount, accounted amount and the
1192 total adjusted amount */
1193 l_run_oth_amt_tot NUMBER;
1194 l_run_oth_acctd_amt_tot NUMBER;
1195 l_amt_tot NUMBER;
1196
1197 BEGIN
1198
1199 IF PG_DEBUG in ('Y', 'C') THEN
1200 debug( 'ARP_UTIL.Set_Buckets()+');
1201 END IF;
1202
1203 l_run_amt_tot := 0;
1204 l_run_acctd_amt_tot := 0;
1205
1206 /* Bug 2013601
1207 Initialise the variables */
1208 l_run_oth_amt_tot := 0;
1209 l_run_oth_acctd_amt_tot := 0;
1210 l_amt_tot := p_tax_amt + p_charges_amt + p_line_amt + p_freight_amt ;
1211
1212 l_run_amt_tot := l_run_amt_tot + p_tax_amt;
1213 p_tax_acctd_amt := arpcurr.functional_amount(
1214 l_run_amt_tot,
1215 p_base_currency,
1216 p_exchange_rate,
1217 p_base_precision,
1218 p_base_min_acc_unit) - l_run_acctd_amt_tot;
1219
1220 l_run_acctd_amt_tot := l_run_acctd_amt_tot + p_tax_acctd_amt;
1221
1222 IF p_tax_acctd_amt <> 0 THEN
1223 l_last_bucket := 'T';
1224 END IF;
1225
1226 l_run_amt_tot := l_run_amt_tot + p_charges_amt;
1227 p_charges_acctd_amt := arpcurr.functional_amount(
1228 l_run_amt_tot,
1229 p_base_currency,
1230 p_exchange_rate,
1231 p_base_precision,
1232 p_base_min_acc_unit) - l_run_acctd_amt_tot;
1233
1234 l_run_acctd_amt_tot := l_run_acctd_amt_tot + p_charges_acctd_amt;
1235
1236 IF p_charges_acctd_amt <> 0 THEN
1237 l_last_bucket := 'C';
1238 END IF;
1239
1240 l_run_amt_tot := l_run_amt_tot + p_line_amt;
1241 p_line_acctd_amt := arpcurr.functional_amount(
1242 l_run_amt_tot,
1243 p_base_currency,
1244 p_exchange_rate,
1245 p_base_precision,
1246 p_base_min_acc_unit) - l_run_acctd_amt_tot;
1247
1248 l_run_acctd_amt_tot := l_run_acctd_amt_tot + p_line_acctd_amt;
1249
1250 IF p_line_acctd_amt <> 0 THEN
1251 l_last_bucket := 'L';
1252 END IF;
1253
1254 l_run_amt_tot := l_run_amt_tot + p_freight_amt;
1255 p_freight_acctd_amt := arpcurr.functional_amount(
1256 l_run_amt_tot,
1257 p_base_currency,
1258 p_exchange_rate,
1259 p_base_precision,
1260 p_base_min_acc_unit) - l_run_acctd_amt_tot;
1261
1262 l_run_acctd_amt_tot := l_run_acctd_amt_tot + p_freight_acctd_amt;
1263
1264 IF p_freight_acctd_amt <> 0 THEN
1265 l_last_bucket := 'F';
1266 END IF;
1267
1268 /* 2013601
1269 When none of the buckets have value for the acctd_amt
1270 by direct multiplication of amount adjusted with the rate,
1271 recalculate the values from the total accounted amount adjusted */
1272
1273 IF l_last_bucket IS NULL THEN
1274 IF PG_DEBUG in ('Y', 'C') THEN
1275 debug( 'l_last_bucket is null');
1276 END IF;
1277
1278 l_run_oth_amt_tot := l_run_oth_amt_tot + p_tax_amt;
1279 p_tax_acctd_amt := arpcurr.Currround((l_run_oth_amt_tot/l_amt_tot)*
1280 p_header_acctd_amt,p_base_currency) -
1281 l_run_oth_acctd_amt_tot;
1282 l_run_oth_acctd_amt_tot := l_run_oth_acctd_amt_tot + p_tax_acctd_amt;
1283
1284 l_run_oth_amt_tot := l_run_oth_amt_tot + p_charges_amt;
1285 p_charges_acctd_amt := arpcurr.Currround((l_run_oth_amt_tot/l_amt_tot)*
1286 p_header_acctd_amt,p_base_currency) -
1287 l_run_oth_acctd_amt_tot;
1288 l_run_oth_acctd_amt_tot := l_run_oth_acctd_amt_tot +p_charges_acctd_amt;
1289
1290 l_run_oth_amt_tot := l_run_oth_amt_tot + p_line_amt;
1291 p_line_acctd_amt := arpcurr.Currround((l_run_oth_amt_tot/l_amt_tot)*
1292 p_header_acctd_amt,p_base_currency) -
1293 l_run_oth_acctd_amt_tot;
1294 l_run_oth_acctd_amt_tot := l_run_oth_acctd_amt_tot + p_line_acctd_amt;
1295
1296 l_run_oth_amt_tot := l_run_oth_amt_tot + p_freight_amt;
1297 p_freight_acctd_amt := arpcurr.Currround((l_run_oth_amt_tot/l_amt_tot)*
1298 p_header_acctd_amt,p_base_currency) -
1299 l_run_oth_acctd_amt_tot;
1300 l_run_oth_acctd_amt_tot := l_run_oth_acctd_amt_tot + p_freight_acctd_amt;
1301
1302 IF PG_DEBUG in ('Y', 'C') THEN
1303 debug( 'p_tax_acctd_amt = '||p_tax_acctd_amt);
1304 debug( 'p_charges_acctd_amt = '||p_charges_acctd_amt);
1305 debug( 'p_line_acctd_amt = '||p_line_acctd_amt);
1306 debug( 'p_freight_acctd_amt = '||p_freight_acctd_amt);
1307 END IF;
1308
1309 ELSIF l_last_bucket = 'T' THEN
1310 p_tax_acctd_amt := p_tax_acctd_amt - (l_run_acctd_amt_tot - p_header_acctd_amt);
1311 ELSIF l_last_bucket = 'C' THEN
1312 p_charges_acctd_amt := p_charges_acctd_amt - (l_run_acctd_amt_tot - p_header_acctd_amt);
1313 ELSIF l_last_bucket = 'L' THEN
1314 p_line_acctd_amt := p_line_acctd_amt - (l_run_acctd_amt_tot - p_header_acctd_amt);
1315 ELSIF l_last_bucket = 'F' THEN
1316 p_freight_acctd_amt := p_freight_acctd_amt - (l_run_acctd_amt_tot - p_header_acctd_amt);
1317 END IF;
1318
1319 IF PG_DEBUG in ('Y', 'C') THEN
1320 debug( 'ARP_UTIL.Set_Buckets()-');
1321 END IF;
1322
1323 EXCEPTION
1324 WHEN OTHERS THEN
1325 IF PG_DEBUG in ('Y', 'C') THEN
1326 debug( 'EXCEPTION: ARP_UTIL.Set_Buckets');
1327 END IF;
1328 RAISE;
1329
1330 END Set_Buckets;
1331
1332 --
1333 --
1334 -----------------------------------------------------------------------------
1335 -- Function to support server-side patch-level identification
1336 -----------------------------------------------------------------------------
1337
1338 -- Bug# 1759719
1339
1340 FUNCTION ar_server_patch_level RETURN VARCHAR2 IS
1341 l_server_patchset_level VARCHAR2(30) ;
1342 BEGIN
1343
1344 IF PG_AR_SERVER_PATCH_LEVEL IS NULL
1345 THEN
1346 BEGIN
1347 SELECT patch_level
1348 INTO l_server_patchset_level
1349 FROM FND_PRODUCT_INSTALLATIONS
1350 WHERE application_id = 222 ;
1351
1352 PG_AR_SERVER_PATCH_LEVEL := l_server_patchset_level ;
1353
1354 EXCEPTION
1355 WHEN NO_DATA_FOUND THEN
1356 PG_AR_SERVER_PATCH_LEVEL := NULL ;
1357
1358 END ;
1359
1360 END IF ;
1361
1362 RETURN PG_AR_SERVER_PATCH_LEVEL ;
1363
1364 END;
1365
1366 PROCEDURE Validate_Desc_Flexfield(
1367 p_desc_flex_rec IN OUT NOCOPY arp_util.attribute_rec_type,
1368 p_desc_flex_name IN VARCHAR2,
1369 p_return_status IN OUT NOCOPY varchar2
1370 ) IS
1371
1372 l_flex_name fnd_descriptive_flexs.descriptive_flexfield_name%type;
1373 l_count NUMBER;
1374 l_col_name VARCHAR2(50);
1375 l_flex_exists VARCHAR2(1);
1376 CURSOR desc_flex_exists IS
1377 SELECT 'Y'
1378 FROM fnd_descriptive_flexs
1379 WHERE application_id = 222
1380 and descriptive_flexfield_name = p_desc_flex_name;
1381 BEGIN
1382 IF PG_DEBUG in ('Y', 'C') THEN
1383 debug( 'arp_util.Validate_Desc_Flexfield()+');
1384 END IF;
1385 p_return_status := FND_API.G_RET_STS_SUCCESS;
1386
1387 OPEN desc_flex_exists;
1388 FETCH desc_flex_exists INTO l_flex_exists;
1389 IF desc_flex_exists%NOTFOUND THEN
1390 CLOSE desc_flex_exists;
1391 p_return_status := FND_API.G_RET_STS_ERROR;
1392 return;
1393 END IF;
1394 CLOSE desc_flex_exists;
1395 fnd_flex_descval.set_context_value(p_desc_flex_rec.attribute_category);
1396
1397 fnd_flex_descval.set_column_value('ATTRIBUTE1', p_desc_flex_rec.attribute1);
1398 fnd_flex_descval.set_column_value('ATTRIBUTE2', p_desc_flex_rec.attribute2);
1399 fnd_flex_descval.set_column_value('ATTRIBUTE3', p_desc_flex_rec.attribute3);
1400 fnd_flex_descval.set_column_value('ATTRIBUTE4', p_desc_flex_rec.attribute4);
1401 fnd_flex_descval.set_column_value('ATTRIBUTE5', p_desc_flex_rec.attribute5);
1402 fnd_flex_descval.set_column_value('ATTRIBUTE6', p_desc_flex_rec.attribute6);
1403 fnd_flex_descval.set_column_value('ATTRIBUTE7', p_desc_flex_rec.attribute7);
1404 fnd_flex_descval.set_column_value('ATTRIBUTE8', p_desc_flex_rec.attribute8);
1405 fnd_flex_descval.set_column_value('ATTRIBUTE9', p_desc_flex_rec.attribute9);
1406 fnd_flex_descval.set_column_value('ATTRIBUTE10', p_desc_flex_rec.attribute10);
1407 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_desc_flex_rec.attribute11);
1408 fnd_flex_descval.set_column_value('ATTRIBUTE12', p_desc_flex_rec.attribute12);
1409 fnd_flex_descval.set_column_value('ATTRIBUTE13', p_desc_flex_rec.attribute13);
1410 fnd_flex_descval.set_column_value('ATTRIBUTE14', p_desc_flex_rec.attribute14);
1411 fnd_flex_descval.set_column_value('ATTRIBUTE15', p_desc_flex_rec.attribute15);
1412
1413 /*Changed the 'V' with 'I' in below call for bug3291407 */
1414 IF ( NOT fnd_flex_descval.validate_desccols('AR',p_desc_flex_name,'I') )
1415 THEN
1416
1417 FND_MESSAGE.SET_NAME('AR', 'AR_RAPI_DESC_FLEX_INVALID');
1418 FND_MESSAGE.SET_TOKEN('DFF_NAME',p_desc_flex_name);
1419 FND_MSG_PUB.ADD ;
1420 p_return_status := FND_API.G_RET_STS_ERROR;
1421 END IF;
1422
1423 l_count := fnd_flex_descval.segment_count;
1424
1425 /*Changed the segment_value with segment_id for bug 3291407 */
1426 FOR i in 1..l_count LOOP
1427 l_col_name := fnd_flex_descval.segment_column_name(i);
1428
1429 IF l_col_name = 'ATTRIBUTE1' THEN
1430 p_desc_flex_rec.attribute1 := fnd_flex_descval.segment_id(i);
1431 ELSIF l_col_name = 'ATTRIBUTE_CATEGORY' THEN
1432 p_desc_flex_rec.attribute_category := fnd_flex_descval.segment_id(i);
1433 ELSIF l_col_name = 'ATTRIBUTE2' THEN
1434 p_desc_flex_rec.attribute2 := fnd_flex_descval.segment_id(i);
1435 ELSIF l_col_name = 'ATTRIBUTE3' THEN
1436 p_desc_flex_rec.attribute3 := fnd_flex_descval.segment_id(i);
1437 ELSIF l_col_name = 'ATTRIBUTE4' THEN
1438 p_desc_flex_rec.attribute4 := fnd_flex_descval.segment_id(i);
1439 ELSIF l_col_name = 'ATTRIBUTE5' THEN
1440 p_desc_flex_rec.attribute5 := fnd_flex_descval.segment_id(i);
1441 ELSIF l_col_name = 'ATTRIBUTE6' THEN
1442 p_desc_flex_rec.attribute6 := fnd_flex_descval.segment_id(i);
1443 ELSIF l_col_name = 'ATTRIBUTE7' THEN
1444 p_desc_flex_rec.attribute7 := fnd_flex_descval.segment_id(i);
1445 ELSIF l_col_name = 'ATTRIBUTE8' THEN
1446 p_desc_flex_rec.attribute8 := fnd_flex_descval.segment_id(i);
1447 ELSIF l_col_name = 'ATTRIBUTE9' THEN
1448 p_desc_flex_rec.attribute9 := fnd_flex_descval.segment_id(i);
1449 ELSIF l_col_name = 'ATTRIBUTE10' THEN
1450 p_desc_flex_rec.attribute10 := fnd_flex_descval.segment_id(i);
1451 ELSIF l_col_name = 'ATTRIBUTE11' THEN
1452 p_desc_flex_rec.attribute11 := fnd_flex_descval.segment_id(i);
1453 ELSIF l_col_name = 'ATTRIBUTE12' THEN
1454 p_desc_flex_rec.attribute12 := fnd_flex_descval.segment_id(i);
1455 ELSIF l_col_name = 'ATTRIBUTE13' THEN
1456 p_desc_flex_rec.attribute13 := fnd_flex_descval.segment_id(i);
1457 ELSIF l_col_name = 'ATTRIBUTE14' THEN
1458 p_desc_flex_rec.attribute14 := fnd_flex_descval.segment_id(i);
1459 ELSIF l_col_name = 'ATTRIBUTE15' THEN
1460 p_desc_flex_rec.attribute15 := fnd_flex_descval.segment_id(i);
1461 END IF;
1462
1463 IF i > l_count THEN
1464 EXIT;
1465 END IF;
1466 END LOOP;
1467
1468 IF PG_DEBUG in ('Y', 'C') THEN
1469 debug( 'attribute_category : '||p_desc_flex_rec.attribute_category);
1470 debug( 'attribute1 : '||p_desc_flex_rec.attribute1);
1471 debug( 'attribute2 : '||p_desc_flex_rec.attribute2);
1472 debug( 'attribute3 : '||p_desc_flex_rec.attribute3);
1473 debug( 'attribute4 : '||p_desc_flex_rec.attribute4);
1474 debug( 'attribute5 : '||p_desc_flex_rec.attribute5);
1475 debug( 'attribute6 : '||p_desc_flex_rec.attribute6);
1476 debug( 'attribute7 : '||p_desc_flex_rec.attribute7);
1477 debug( 'attribute8 : '||p_desc_flex_rec.attribute8);
1478 debug( 'attribute9 : '||p_desc_flex_rec.attribute9);
1479 debug( 'attribute10 : '||p_desc_flex_rec.attribute10);
1480 debug( 'attribute11 : '||p_desc_flex_rec.attribute11);
1481 debug( 'attribute12 : '||p_desc_flex_rec.attribute12);
1482 debug( 'attribute13 : '||p_desc_flex_rec.attribute13);
1483 debug( 'attribute14 : '||p_desc_flex_rec.attribute14);
1484 debug( 'attribute15 : '||p_desc_flex_rec.attribute15);
1485 debug( 'arp_util.Validate_Desc_Flexfield()-');
1486 END IF;
1487 END Validate_Desc_Flexfield;
1488
1489 --
1490 --
1491 --This function will get the ID when you pass the corresponding number/or name
1492 --for an entity.The following entitiy can be passed to get the corresponding ID
1493 --CUSTOMER_NUMBER,CUSTOMER_NAME,RECEIPT_METHOD_NAME,CUST_BANK_ACCOUNT_NUMBER
1494 --CUST_BANK_ACCOUNT_NAME,REMIT_BANK_ACCOUNT_NUMBER,REMIT_BANK_ACCOUNT_NAME,
1495 --CURRENCY_NAME,
1496
1497 FUNCTION Get_Id(
1498 p_entity IN VARCHAR2,
1499 p_value IN VARCHAR2,
1500 p_return_status OUT NOCOPY VARCHAR2
1501 ) RETURN VARCHAR2 IS
1502
1503 l_cached_id VARCHAR2(100);
1504 l_selected_id VARCHAR2(100);
1505 l_index BINARY_INTEGER;
1506
1507 BEGIN
1508 IF PG_DEBUG in ('Y', 'C') THEN
1509 debug('Get_Id()+ ');
1510 END IF;
1511 IF ( p_value IS NULL )
1512 THEN
1513 RETURN(NULL);
1514 ELSE
1515 IF ( p_entity = 'CUSTOMER_NUMBER' )
1516 THEN
1517
1518 /* modified for tca uptake */
1519 /* fixed bug 1544201: removed customer_prospect_code
1520 decode statement as everyone is now considered a
1521 customer */
1522
1523 SELECT c.cust_account_id
1524 INTO l_selected_id
1525 FROM hz_cust_accounts c,
1526 hz_customer_profiles cp,
1527 hz_parties party
1528 WHERE c.cust_account_id = cp.cust_account_id (+) and
1529 cp.site_use_id is null and
1530 c.account_number = p_value
1531 AND c.party_id = party.party_id;
1532 ELSIF ( p_entity = 'CUSTOMER_NAME' )
1533 THEN
1534
1535 /* modified for tca uptake */
1536 /* fixed bug 1544201: removed customer_prospect_code
1537 decode statement as everyone is now considered a
1538 customer */
1539 SELECT cust_acct.cust_account_id
1540 INTO l_selected_id
1541 FROM hz_cust_accounts cust_acct,
1542 hz_customer_profiles cp,
1543 hz_parties party
1544 WHERE cust_acct.cust_account_id = cp.cust_account_id (+)
1545 and cust_acct.party_id = party.party_id
1546 and cp.site_use_id is null
1547 and party.party_name = p_value;
1548 ELSIF (p_entity = 'RECEIPT_METHOD_NAME' )
1549
1550 THEN
1551
1552 SELECT receipt_method_id
1553 INTO l_selected_id
1554 FROM ar_receipt_methods
1555 WHERE name = p_value;
1556
1557 ELSIF (p_entity = 'CUST_BANK_ACCOUNT_NUMBER')
1558 THEN
1559
1560 SELECT ext_bank_account_id
1561 INTO l_selected_id
1562 FROM iby_ext_bank_accounts
1563 WHERE ((bank_account_num = p_value) OR
1564 (bank_account_num_hash1=
1565 iby_security_pkg.Get_Hash(p_value,'F') and
1566 bank_account_num_hash2=
1567 iby_security_pkg.Get_Hash(p_value,'T')
1568 )
1569 );
1570
1571 ELSIF (p_entity = 'CUST_BANK_ACCOUNT_NAME')
1572 THEN
1573
1574 SELECT ext_bank_account_id
1575 INTO l_selected_id
1576 FROM iby_ext_bank_accounts
1577 WHERE bank_account_name = p_value;
1578
1579 ELSIF (p_entity = 'REMIT_BANK_ACCOUNT_NUMBER')
1580 THEN
1581 SELECT bank_account_id
1582 INTO l_selected_id
1583 FROM ce_bank_accounts
1584 WHERE bank_account_num = p_value;
1585 ELSIF (p_entity = 'REMIT_BANK_ACCOUNT_NAME')
1586 THEN
1587 SELECT bank_account_id
1588 INTO l_selected_id
1589 FROM ce_bank_accounts
1590 WHERE bank_account_name = p_value;
1591
1592 ELSIF (p_entity = 'CURRENCY_NAME')
1593 THEN
1594 SELECT currency_code
1595 INTO l_selected_id
1596 FROM fnd_currencies_vl
1597 WHERE name = p_value;
1598 ELSIF (p_entity = 'EXCHANGE_RATE_TYPE_NAME')
1599 THEN
1600 SELECT conversion_type
1601 INTO l_selected_id
1602 FROM gl_daily_conversion_types
1603 WHERE user_conversion_type = p_value ;
1604
1605 END IF;
1606
1607 IF PG_DEBUG in ('Y', 'C') THEN
1608 debug( 'Value selected. Entity: '||
1609 p_entity || ',' ||
1610 ' Value: ' || p_value || ',' ||
1611 'ID: ' || l_selected_id);
1612 debug('Get_Id()- ');
1613 END IF;
1614
1615 RETURN( l_selected_id );
1616
1617
1618
1619 END IF; -- end p_value is not null case
1620
1621
1622 IF PG_DEBUG in ('Y', 'C') THEN
1623 debug('Get_Id()- ');
1624 END IF;
1625
1626
1627 EXCEPTION
1628
1629 WHEN NO_DATA_FOUND THEN
1630 IF PG_DEBUG in ('Y', 'C') THEN
1631 debug( 'Value not found. Entity: ' ||
1632 p_entity ||' Value: ' || p_value);
1633 END IF;
1634 return(null);
1635 IF PG_DEBUG in ('Y', 'C') THEN
1636 debug('Get_Id()- ');
1637 END IF;
1638 WHEN OTHERS THEN
1639 IF PG_DEBUG in ('Y', 'C') THEN
1640 debug( 'Value not found. Entity: ' ||
1641 p_entity ||' Value: ' || p_value);
1642 END IF;
1643 RAISE;
1644
1645 END Get_Id;
1646
1647 -- Bug# 1842884
1648 -- This function returns the sum of the promised amounts for commitment
1649 -- invoices. Since it uses the interface_lines table, it is really
1650 -- only useful within the scope of autoinvoice.
1651 -- p_customer_trx_id = trx_id assigned on interface table
1652 -- p_alloc_tax_freight = trx_type.allocate_tax_freight (Y or null)
1653
1654 FUNCTION Get_Promised_Amount(
1655 p_customer_trx_id IN NUMBER,
1656 p_alloc_tax_freight IN VARCHAR2)
1657 RETURN NUMBER IS
1658
1659 CURSOR C1 (l_customer_trx_id NUMBER, l_alloc_tax_freight VARCHAR2 ) IS
1660 SELECT l.customer_trx_line_id,
1661 l.link_to_cust_trx_line_id link_to_line_id,
1662 l.line_type,
1663 l.extended_amount,
1664 il.promised_commitment_amount
1665 FROM ra_interface_lines_gt il,
1666 ra_customer_trx_lines l
1667 WHERE l.customer_trx_id = l_customer_trx_id
1668 AND l.customer_trx_line_id = il.interface_line_id (+)
1669 ORDER BY l.line_type ;
1670
1671 TYPE trx_line_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER ;
1672 TYPE line_type IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER ;
1673
1674 l_cust_trx_line_id trx_line_id ;
1675 l_link_to_line_id trx_line_id ;
1676 l_extended_amount trx_line_id ;
1677 l_promised_comm_amt trx_line_id ;
1678
1679 l_line_type line_type ;
1680
1681 l_promised_amount NUMBER := 0 ;
1682 l_last_index NUMBER := 0 ;
1683 l_null_prom_comm_amt NUMBER := 0 ;
1684 l_line_count NUMBER := 0 ;
1685
1686 BEGIN
1687
1688 --
1689 -- Bug# 1842884
1690 -- The promised_commitment_amount in the line includes the commitment amt
1691 -- for tax and freight. If the promised_commitment_amount is NULL for
1692 -- line_type LINE, then arrive at the promised_commitment_amount by
1693 -- summing up the amounts of LINE, TAX and FREIGHT. If for one of the
1694 -- line, the promised_commitment_amount is NULL and for the rest, it is
1695 -- NOT NULL, then this is not supported. In that case, treat the
1696 -- promised_commitment_amount as NULL
1697 -- If p_alloc_tax_freight is NULL and the promised_commitment_amount
1698 -- IS NULL, then it should be arrived with only the LINE amounts.
1699 --
1700
1701 FOR get_prom_amt in C1 ( p_customer_trx_id, p_alloc_tax_freight )
1702 LOOP
1703 l_last_index := l_last_index + 1 ;
1704
1705 l_cust_trx_line_id( l_last_index ) :=
1706 get_prom_amt.customer_trx_line_id ;
1707 l_link_to_line_id( l_last_index ) :=
1708 get_prom_amt.link_to_line_id ;
1709 l_extended_amount( l_last_index ) :=
1710 get_prom_amt.extended_amount ;
1711 l_promised_comm_amt( l_last_index ) :=
1712 get_prom_amt.promised_commitment_amount ;
1713 l_line_type( l_last_index ) :=
1714 get_prom_amt.line_type ;
1715
1716 IF l_line_type( l_last_index ) = 'LINE'
1717 THEN
1718 l_line_count := l_line_count + 1 ;
1719 IF l_promised_comm_amt( l_last_index ) IS NULL
1720 THEN
1721 l_null_prom_comm_amt := l_null_prom_comm_amt + 1 ;
1722 END IF ;
1723 END IF ;
1724
1725 END LOOP ;
1726
1727 IF l_null_prom_comm_amt > 0 AND
1728 l_line_count <> l_null_prom_comm_amt
1729 THEN
1730 -- there is atleast one line with NULL promised amount
1731 -- where for the rest the promised amount IS NOT NULL
1732 RETURN NULL ;
1733 END IF ;
1734
1735 FOR i in 1..l_last_index
1736 LOOP
1737
1738 -- If all the lines have null promised amount, then
1739 -- arrive the promised amount by adding the line amounts
1740 -- of LINE, TAX and FREIGHT for every line
1741
1742 IF l_line_count = l_null_prom_comm_amt
1743 THEN
1744 IF upper(p_alloc_tax_freight) = 'Y' OR
1745 l_line_type(i) = 'LINE'
1746 THEN
1747 l_promised_amount := l_promised_amount + l_extended_amount(i) ;
1748 END IF ;
1749 ELSE
1750 IF l_line_type(i) = 'LINE'
1751 THEN
1752 l_promised_amount := l_promised_amount +
1753 l_promised_comm_amt(i) ;
1754 END IF ;
1755 END IF ;
1756
1757 END LOOP ;
1758
1759 RETURN l_promised_amount ;
1760
1761 EXCEPTION
1762 WHEN OTHERS
1763 then RETURN null;
1764
1765 END Get_Promised_Amount;
1766
1767 /* ==========================================================================
1768 | PROCEDURE Substitute_Ccid
1769 |
1770 | DESCRIPTION
1771 | Builds the gain, loss, round account based on input parameters
1772 |
1773 | SCOPE - PRIVATE
1774 |
1775 | PARAMETERS
1776 | p_coa_id IN Chart of Accounts id
1777 | p_original_ccid IN Original ccid
1778 | p_subs_ccid IN Substitute ccid
1779 | p_actual_ccid OUT NOCOPY Actual or return ccid
1780 *==========================================================================*/
1781 PROCEDURE Substitute_Ccid(p_coa_id IN gl_sets_of_books.chart_of_accounts_id%TYPE ,
1782 p_original_ccid IN ar_system_parameters.code_combination_id_gain%TYPE,
1783 p_subs_ccid IN ar_system_parameters.code_combination_id_gain%TYPE,
1784 p_actual_ccid OUT NOCOPY ar_system_parameters.code_combination_id_gain%TYPE) IS
1785
1786 l_concat_segs varchar2(240) ;
1787 l_concat_ids varchar2(2000) ;
1788 l_concat_descs varchar2(2000) ;
1789 l_arerror varchar2(2000) ;
1790 l_actual_gain_loss_ccid ar_system_parameters_all.code_combination_id_gain%TYPE ;
1791 flex_subs_ccid_error EXCEPTION;
1792
1793 BEGIN
1794
1795 IF PG_DEBUG in ('Y', 'C') THEN
1796 debug( 'arp_util.Substitute_Ccid()+');
1797 END IF;
1798
1799 /*----------------------------------------------------------------------------+
1800 | Set other in out NOCOPY variables used by flex routine |
1801 +----------------------------------------------------------------------------*/
1802 p_actual_ccid := NULL;
1803 l_actual_gain_loss_ccid := NULL; --must always be derived
1804 l_concat_segs := NULL;
1805 l_concat_ids := NULL;
1806 l_concat_descs := NULL;
1807
1808 /*----------------------------------------------------------------------------+
1809 | Derive gain loss account using flex routine |
1810 +----------------------------------------------------------------------------*/
1811 IF l_actual_gain_loss_ccid is NULL THEN
1812
1813 IF NOT ar_flexbuilder_wf_pkg.substitute_balancing_segment (
1814 x_arflexnum => p_coa_id ,
1815 x_arorigccid => p_original_ccid ,
1816 x_arsubsticcid => p_subs_ccid ,
1817 x_return_ccid => l_actual_gain_loss_ccid ,
1818 x_concat_segs => l_concat_segs ,
1819 x_concat_ids => l_concat_ids ,
1820 x_concat_descrs => l_concat_descs ,
1821 x_arerror => l_arerror ) THEN
1822
1823 /*----------------------------------------------------------------------------+
1824 | Invalid account raise user exception |
1825 +----------------------------------------------------------------------------*/
1826 RAISE flex_subs_ccid_error;
1827
1828 END IF;
1829
1830 /*----------------------------------------------------------------------------+
1831 | Cache the gain loss account as it has been successfully derived |
1832 +----------------------------------------------------------------------------*/
1833
1834 IF PG_DEBUG in ('Y', 'C') THEN
1835 debug( 'Flexbuilder : Chart of Accounts ' || p_coa_id);
1836 debug( 'Flexbuilder : Original CCID ' || p_original_ccid);
1837 debug( 'Flexbuilder : Substitute CCID ' || p_subs_ccid);
1838 debug( 'Flexbuilder : Actual CCID ' || l_actual_gain_loss_ccid);
1839 END IF;
1840
1841 END IF;
1842
1843 p_actual_ccid := l_actual_gain_loss_ccid;
1844
1845 IF PG_DEBUG in ('Y', 'C') THEN
1846 debug( 'arp_util.Substitute_Ccid()-');
1847 END IF;
1848
1849 EXCEPTION
1850 WHEN flex_subs_ccid_error THEN
1851 IF PG_DEBUG in ('Y', 'C') THEN
1852 debug('Flexbuilder error: ARP_ALLOCATION_PKG.Substitute_Ccid');
1853 debug( 'Flexbuilder error: Chart of Accounts ' || p_coa_id);
1854 debug( 'Flexbuilder error: Original CCID ' || p_original_ccid);
1855 debug( 'Flexbuilder error: Substitute CCID ' || p_subs_ccid);
1856 debug( 'Flexbuilder error: Actual CCID ' || l_actual_gain_loss_ccid);
1857 END IF;
1858 fnd_message.set_name('AR','AR_FLEX_CCID_ERROR');
1859 fnd_message.set_token('COA',TO_CHAR(p_coa_id));
1860 fnd_message.set_token('ORG_CCID',TO_CHAR(p_original_ccid));
1861 fnd_message.set_token('SUB_CCID',TO_CHAR(p_subs_ccid));
1862 RAISE;
1863
1864 WHEN OTHERS THEN
1865 IF PG_DEBUG in ('Y', 'C') THEN
1866 debug('EXCEPTION: ARP_ALLOCATION_PKG.Substitute_Ccid');
1867 debug( 'Flexbuilder error: Chart of Accounts ' || p_coa_id);
1868 debug( 'Flexbuilder error: Original CCID ' || p_original_ccid);
1869 debug( 'Flexbuilder error: Substitute CCID ' || p_subs_ccid);
1870 debug( 'Flexbuilder error: Actual CCID ' || l_actual_gain_loss_ccid);
1871 END IF;
1872 RAISE;
1873
1874 END Substitute_Ccid;
1875
1876 /* ==========================================================================
1877 | PROCEDURE Dynamic_Select
1878 |
1879 | DESCRIPTION
1880 | Executes a dynamic select statement
1881 | Intended for client side calls where dynamic sql is not supported
1882 |
1883 | SCOPE - PUBLIC
1884 |
1885 | PARAMETERS
1886 | p_query IN Dynamically assembled query to be executed
1887 | p_result OUT NOCOPY Container for result column
1888 |
1889 | NOTES
1890 | Only one column can be returned
1891 *==========================================================================*/
1892 PROCEDURE Dynamic_Select(p_query IN VARCHAR2,
1893 p_result OUT NOCOPY VARCHAR2)
1894 IS
1895 BEGIN
1896 IF PG_DEBUG in ('Y', 'C') THEN
1897 debug( 'arp_util.Dynamic_Select()+');
1898 END IF;
1899 EXECUTE IMMEDIATE p_query INTO p_result;
1900 IF PG_DEBUG in ('Y', 'C') THEN
1901 debug( 'arp_util.Dynamic_Select()-');
1902 END IF;
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 IF PG_DEBUG in ('Y', 'C') THEN
1906 debug('EXCEPTION: ARP_UTIL.Dynamic_Select');
1907 debug('Dynamic_Select: ' || 'SELECT stmt : '||p_query);
1908 END IF;
1909 END Dynamic_Select;
1910
1911 -- kmahajan - 08/11/2003
1912 -- This function will get the Transaction dates for the Txn ID
1913 -- and all its related Transactions identified by the
1914 -- PREVIOUS_CUSTOMER_TRX_ID (Identifier for invoice credited)
1915 -- INITIAL_CUSTOMER_TRX_ID (Identifier of a related commitment)
1916 -- and transactions related, in turn, to these transactions.
1917 -- The earliest of these Transaction dates will be returned as the start_date
1918 -- and the latest / SYSDATE will be returned as the end_date
1919
1920 PROCEDURE Get_Txn_Start_End_Dates (
1921 p_customer_trx_id IN NUMBER,
1922 p_start_date OUT NOCOPY DATE,
1923 p_end_date OUT NOCOPY DATE
1924 ) IS
1925
1926 l_start_date DATE;
1927 l_end_date DATE;
1928 l_initial_customer_trx_id NUMBER;
1929 l_previous_customer_trx_id NUMBER;
1930
1931 BEGIN
1932 IF PG_DEBUG in ('Y', 'C') THEN
1933 debug( 'arp_util.get_txn_start_end_dates()+' );
1934 debug( 'p_customer_trx_id=' || to_char(p_customer_trx_id));
1935 END IF;
1936
1937 p_start_date := null;
1938 p_end_date := null;
1939
1940 /*--- Bug 5039192 This query raises No Data Found if no Customer is populated */
1941
1942 IF p_customer_trx_id IS NOT NULL THEN
1943
1944 select INITIAL_CUSTOMER_TRX_ID, PREVIOUS_CUSTOMER_TRX_ID, TRX_DATE
1945 into l_initial_customer_trx_id, l_previous_customer_trx_id, p_start_date
1946 from RA_CUSTOMER_TRX
1947 where CUSTOMER_TRX_ID = p_customer_trx_id;
1948
1949 END IF;
1950
1951
1952 if p_start_date is null then
1953 p_start_date := SYSDATE;
1954 end if;
1955
1956 if p_start_date > SYSDATE
1957 then
1958 p_end_date := p_start_date;
1959 else
1960 p_end_date := SYSDATE;
1961 end if;
1962
1963 if l_initial_customer_trx_id is not null then
1964 Get_Txn_Start_End_Dates(l_initial_customer_trx_id, l_start_date, l_end_date);
1965 if nvl(l_start_date, p_start_date) < p_start_date then
1966 p_start_date := l_start_date;
1967 end if;
1968 if nvl(l_end_date, p_end_date) > p_end_date then
1969 p_end_date := l_end_date;
1970 end if;
1971 end if;
1972
1973 if l_previous_customer_trx_id is not null then
1974 Get_Txn_Start_End_Dates(l_previous_customer_trx_id, l_start_date, l_end_date);
1975 if nvl(l_start_date, p_start_date) < p_start_date then
1976 p_start_date := l_start_date;
1977 end if;
1978 if nvl(l_end_date, p_end_date) > p_end_date then
1979 p_end_date := l_end_date;
1980 end if;
1981 end if;
1982
1983 IF PG_DEBUG in ('Y', 'C') THEN
1984 debug( 'arp_util.get_txn_start_end_dates()-' );
1985 END IF;
1986 EXCEPTION
1987 WHEN OTHERS THEN
1988 IF PG_DEBUG in ('Y', 'C') THEN
1989 debug( 'Exception: arp_util.get_txn_start_end_dates');
1990 END IF;
1991 RAISE;
1992 END Get_Txn_Start_End_Dates;
1993
1994 -- kmahajan - 25th Aug 2003 - New utility functions that serve as wrappers
1995 -- for the JTF function to return a Default Sales Group given a Sales Rep
1996 -- and effective date
1997 --
1998 FUNCTION Get_Default_SalesGroup (
1999 p_salesrep_id IN NUMBER,
2000 p_org_id IN NUMBER,
2001 p_date IN DATE
2002 ) RETURN NUMBER IS
2003
2004 l_group_id NUMBER;
2005 BEGIN
2006 IF PG_DEBUG in ('Y', 'C') THEN
2007 debug( 'arp_util.get_default_salesgroup()+' );
2008 END IF;
2009
2010 -- here, we need to make the call to the JTF function
2011 -- jtf_rs_integration_pub.get_default_sales_group
2012 -- Initially, this is just a stub returning NULL but
2013 -- going forward (DBI 6.1 onwards), it will pick up
2014 -- the default Sales Group
2015 BEGIN
2016 l_group_id := jtf_rs_integration_pub.get_default_sales_group(
2017 p_salesrep_id, p_org_id, p_date);
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020 l_group_id := null;
2021 END;
2022
2023 return l_group_id;
2024
2025 IF PG_DEBUG in ('Y', 'C') THEN
2026 debug( 'arp_util.get_default_salesgroup()-' );
2027 END IF;
2028 EXCEPTION
2029 WHEN OTHERS THEN
2030 IF PG_DEBUG in ('Y', 'C') THEN
2031 debug( 'Exception: arp_util.get_default_salesgroup');
2032 END IF;
2033 RAISE;
2034 END Get_Default_SalesGroup;
2035
2036 FUNCTION Get_Default_SalesGroup (
2037 p_salesrep_id IN NUMBER,
2038 p_customer_trx_id IN NUMBER
2039 ) RETURN NUMBER IS
2040 l_date DATE;
2041 l_org_id NUMBER;
2042 BEGIN
2043 IF PG_DEBUG in ('Y', 'C') THEN
2044 debug( 'arp_util.get_default_salesgroup()+' );
2045 END IF;
2046
2047 l_date := null;
2048
2049 select TRX_DATE, ORG_ID
2050 into l_date, l_org_id
2051 from RA_CUSTOMER_TRX
2052 where CUSTOMER_TRX_ID = p_customer_trx_id;
2053
2054 return Get_Default_SalesGroup(p_salesrep_id, l_org_id, l_date);
2055
2056 IF PG_DEBUG in ('Y', 'C') THEN
2057 debug( 'arp_util.get_default_salesgroup()-' );
2058 END IF;
2059 EXCEPTION
2060 WHEN NO_DATA_FOUND THEN
2061 IF PG_DEBUG in ('Y', 'C') THEN
2062 debug( 'Exception: arp_util.get_default_salesgroup');
2063 debug( 'NO_DATA_FOUND: p_customer_trx_id=' || to_char(p_customer_trx_id));
2064 END IF;
2065 RAISE;
2066 WHEN OTHERS THEN
2067 IF PG_DEBUG in ('Y', 'C') THEN
2068 debug( 'Exception: arp_util.get_default_salesgroup');
2069 debug( 'p_customer_trx_id=' || to_char(p_customer_trx_id));
2070 END IF;
2071 RAISE;
2072 END Get_Default_SalesGroup;
2073
2074 /* Bug fix 4942083:
2075 The accounting reports will be run for a GL date range. If within this date range, there
2076 is a period which is not Closed or Close Pending, this function will return TRUE. Else
2077 this function will return FALSE */
2078
2079 FUNCTION Open_Period_Exists(
2080 p_reporting_level IN VARCHAR2,
2081 p_reporting_entity_id IN NUMBER,
2082 p_gl_date_from IN DATE,
2083 p_gl_date_to IN DATE
2084 ) RETURN BOOLEAN IS
2085
2086 l_value NUMBER := 0;
2087 l_sysparam_org_where VARCHAR2(2000);
2088 l_select_stmt VARCHAR2(10000);
2089 BEGIN
2090 IF PG_DEBUG in ('Y', 'C') THEN
2091 debug('arp_util.Open_Period_Exists()+');
2092 END IF;
2093
2094 IF p_gl_date_from IS NULL and p_gl_date_to IS NULL THEN
2095 l_value := 1;
2096
2097 ELSIF p_gl_date_from IS NULL and p_gl_date_to IS NOT NULL THEN
2098 IF arp_util.Open_Period_Exists(p_reporting_level,
2099 p_reporting_entity_id,
2100 p_gl_date_to) THEN
2101 l_value := 1;
2102 END IF;
2103
2104 ELSIF p_gl_date_from IS NOT NULL and p_gl_date_to IS NULL THEN
2105 IF arp_util.Open_Period_Exists(p_reporting_level,
2106 p_reporting_entity_id,
2107 p_gl_date_from) THEN
2108 l_value := 1;
2109 END IF;
2110 ELSE
2111 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
2112
2113 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sp',null);
2114
2115 l_select_stmt := 'SELECT 1
2116 FROM DUAL
2117 WHERE EXISTS( SELECT closing_status
2118 FROM gl_period_statuses g,
2119 gl_sets_of_books b,
2120 ar_system_parameters_all sp
2121 WHERE b.set_of_books_id = g.set_of_books_id
2122 AND g.set_of_books_id = sp.set_of_books_id
2123 AND g.period_type = b.accounted_period_type
2124 AND g.application_id = 222
2125 AND g.adjustment_period_flag = ''N''
2126 AND g.closing_status not in (''P'', ''C'',''W'')
2127 AND ((g.end_date BETWEEN :p_gl_date_from AND :p_gl_date_to)
2128 OR (:p_gl_date_to BETWEEN g.start_date AND g.end_date))
2129 ' ||l_sysparam_org_where || ')';
2130
2131 IF p_reporting_level = '3000' THEN
2132 EXECUTE IMMEDIATE l_select_stmt
2133 INTO l_value
2134 USING p_gl_date_from,p_gl_date_to,p_gl_date_to,p_reporting_entity_id,p_reporting_entity_id;
2135 ELSE
2136 EXECUTE IMMEDIATE l_select_stmt
2137 INTO l_value
2138 USING p_gl_date_from, p_gl_date_to,p_gl_date_to;
2139 END IF;
2140 END IF;
2141
2142 IF l_value = 1 THEN
2143 return TRUE;
2144 ELSE
2145 return FALSE;
2146 END IF;
2147
2148 IF PG_DEBUG in ('Y', 'C') THEN
2149 debug('arp_util.Open_Period_Exists()-');
2150 END IF;
2151
2152 EXCEPTION
2153 WHEN OTHERS THEN
2154 IF PG_DEBUG in ('Y', 'C') THEN
2155 debug('arp_util.Open_Period_Exists: Exception');
2156 END IF;
2157 return FALSE;
2158 END Open_Period_Exists;
2159
2160 FUNCTION Open_Period_Exists(
2161 p_reporting_level IN VARCHAR2,
2162 p_reporting_entity_id IN NUMBER,
2163 p_in_as_of_date_low IN DATE
2164 ) RETURN BOOLEAN IS
2165
2166 l_value NUMBER := 0;
2167 l_sysparam_org_where VARCHAR2(2000);
2168 l_select_stmt VARCHAR2(10000);
2169 BEGIN
2170 IF PG_DEBUG in ('Y', 'C') THEN
2171 debug('arp_util.Open_Period_Exists()+');
2172 END IF;
2173
2174 XLA_MO_REPORTING_API.Initialize(p_reporting_level, p_reporting_entity_id, 'AUTO');
2175
2176 l_sysparam_org_where := XLA_MO_REPORTING_API.Get_Predicate('sp',null);
2177
2178 l_select_stmt := 'SELECT 1
2179 FROM DUAL
2180 WHERE EXISTS( SELECT closing_status
2181 FROM gl_period_statuses g,
2182 gl_sets_of_books b,
2183 ar_system_parameters_all sp
2184 WHERE b.set_of_books_id = g.set_of_books_id
2185 AND g.set_of_books_id = sp.set_of_books_id
2186 AND g.period_type = b.accounted_period_type
2187 AND g.application_id = 222
2188 AND g.adjustment_period_flag = ''N''
2189 AND g.closing_status not in (''P'', ''C'',''W'')
2190 AND start_date <= :p_in_as_of_date_low
2191 AND end_date >= :p_in_as_of_date_low
2192 ' ||l_sysparam_org_where || ')';
2193
2194 IF p_reporting_level = '3000' THEN
2195 EXECUTE IMMEDIATE l_select_stmt
2196 INTO l_value
2197 USING p_in_as_of_date_low,p_in_as_of_date_low,p_reporting_entity_id,p_reporting_entity_id;
2198 ELSE
2199 EXECUTE IMMEDIATE l_select_stmt
2200 INTO l_value
2201 USING p_in_as_of_date_low,p_in_as_of_date_low;
2202 END IF;
2203 IF l_value = 1 THEN
2204 RETURN TRUE;
2205 ELSE
2206 RETURN FALSE;
2207 END IF;
2208
2209 IF PG_DEBUG in ('Y', 'C') THEN
2210 debug('arp_util.Open_Period_Exists()-');
2211 END IF;
2212
2213 EXCEPTION
2214 WHEN OTHERS THEN
2215 IF PG_DEBUG in ('Y', 'C') THEN
2216 debug('arp_util.Open_Period_Exists: Exception');
2217 END IF;
2218 RETURN FALSE;
2219 END Open_Period_Exists;
2220
2221 /* ER Automatch Cash Application START */
2222 -- Function to restrict the new feature from user.
2223 FUNCTION AUTOMATCH_ENABLED RETURN VARCHAR2 is
2224 l_automatch_enabled_flag VARCHAR2(1) := 'F';
2225 BEGIN
2226 IF PG_DEBUG in ('Y', 'C') THEN
2227 debug('arp_util.automatch_enabled()+');
2228 END IF;
2229 begin
2230 select NVL(automatch_enabled_flag,'F')
2231 into l_automatch_enabled_flag
2232 from ar_system_parameters;
2233 exception
2234 when others then
2235 l_automatch_enabled_flag := 'F';
2236 end;
2237
2238 debug('l_automatch_enabled_flag : ' || l_automatch_enabled_flag);
2239
2240 IF l_automatch_enabled_flag = 'T' THEN
2241 RETURN 'TRUE';
2242 ELSE
2243 RETURN 'FALSE';
2244 END IF;
2245
2246 IF PG_DEBUG in ('Y', 'C') THEN
2247 debug('arp_util.automatch_enabled()-');
2248 END IF;
2249 EXCEPTION
2250 WHEN OTHERS THEN
2251 IF PG_DEBUG in ('Y', 'C') THEN
2252 debug('arp_util.automatch_enabled: Exception');
2253 END IF;
2254 RETURN 'FALSE';
2255 END automatch_enabled;
2256 /* ER Automatch Cash Application END */
2257
2258 END arp_util;
[Home] [Help]