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