1 package body ARP_STANDARD as
2 /* $Header: ARPLSTDB.pls 120.27.12010000.2 2008/11/05 06:29:39 ankuagar ship $ */
3
4 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
5
6
7 /*-------------------------------------------------------------------------+
8 | |
9 | PRIVATE CURSORS |
10 | |
11 | gl_periods_c Returns the period status for non adjusted periods |
12 | for each period between pstart and pend_date or, if |
13 | the start and end are within the same period, then it |
14 | returns just the enclosing period. |
15 | |
16 +-------------------------------------------------------------------------*/
17
18 cursor gl_periods_c( app_id in number,
19 sob_id in number,
20 pstart_date in varchar2,
21 pend_date in varchar2 ) IS
22 select period_name, closing_status,
23 period_type, period_year, period_num
24 from gl_period_statuses
25 where application_id = app_id
26 and set_of_books_id = sob_id
27 and
28 (
29 start_date between to_date(pstart_date, 'YYYYMMDD') and
30 to_date(pend_date, 'YYYYMMDD')
31 OR
32 end_date between to_date(pstart_date, 'YYYYMMDD') and
33 to_date(pend_date, 'YYYYMMDD')
34 OR
35 to_date(pstart_date, 'YYYYMMDD') between start_date and end_date
36 OR
37 to_date(pend_date, 'YYYYMMDD') between start_date and end_date
38 )
39 and adjustment_period_flag = 'N'
40 order by period_year, period_num;
41
42
43
44
45 /*-------------------------------------------------------------------------+
46 | |
47 | PRIVATE DATA TYPES |
48 | |
49 +-------------------------------------------------------------------------*/
50 TYPE gl_date_type IS
51 TABLE OF BINARY_INTEGER
52 INDEX BY BINARY_INTEGER;
53
54 /*-------------------------------------------------------------------------+
55 | |
56 | PRIVATE FLAGS |
57 | |
58 | Control flags are currently held in base 10. |
59 | PUBLIC FUNCTIONS are declared to export each of these private flags |
60 | to a SQL*ReportWriter application. |
61 | |
62 +-------------------------------------------------------------------------*/
63
64 INT_MD_MSG_NUMBER constant number := 1; -- Message Dictionary control
65 INT_MD_MSG_TEXT constant number := 10; -- Options
66 INT_MD_MSG_NAME constant number := 100; -- Show message name only
67 INT_MD_MSG_TOKENS constant number := 1000; -- List Message Tokens and Values
68 INT_MD_MSG_EXPLANATION constant number := 10000; -- Not supported yet
69 INT_MD_MSG_FIND_NUMBER constant number := 100000; -- Use Message Number not Name
70
71
72 /*-------------------------------------------------------------------------+
73 | |
74 | PRIVATE VARIABLES |
75 | |
76 +-------------------------------------------------------------------------*/
77
78 debug_flag boolean := false;
79 MD_OPTIONS NUMBER := INT_MD_MSG_NUMBER + INT_MD_MSG_TEXT;
80
81 pg_file_name VARCHAR2(100) := NULL;
82 pg_path_name VARCHAR2(100) := NULL;
83 pg_fp utl_file.file_type;
84 pg_period gl_date_type;
85 pg_period_open gl_date_type;
86
87 /*-------------------------------------------------------------------------+
88 | |
89 | PUBLIC FUNCTIONS |
90 | |
91 +-------------------------------------------------------------------------*/
92
93
94
95 /*----------------------------------------------------------------------------*
96 | PUBLIC FUNCTION |
97 | get_next_word( list in out, value out NOCOPY ) return boolean |
98 | |
99 | DESCRIPTION |
100 | Given an input list of 'words' this routine will extract the first |
101 | word from the list and set the output parameter "value" to this word. |
102 | Returns false when the list is empty. |
103 | |
104 | REQUIRES |
105 | list of words, separated by space characters, eg: |
106 | COUNTY STATE COUNTY CITY |
107 | |
108 | MODIFIES |
109 | list - Each time a word is found, it is taken off this list. |
110 | value - Next word on list or null if list was empty. |
111 | |
112 | RETURNS |
113 | TRUE - The output parameter value is not null |
114 | FALSE - The input list was empty, and no word could be found. |
115 | |
116 | NOTES |
117 | Was originally written as part of the package: arp_flex but has been |
118 | moved to arp_standard( public function ) so that it can be used in |
119 | all software. |
120 | |
121 | HISTORY |
122 | 2/11/93 Nigel Smith Created |
123 | |
124 *----------------------------------------------------------------------------*/
125
126 function get_next_word( list in out NOCOPY varchar2, value in out NOCOPY varchar2 ) return boolean is
127 end_pos number;
128 begin
129
130 arp_standard.debug( 'arp_standard.get_next_word(' || list || ',' || value || ')+' );
131
132 list := ltrim( list );
133 /*** MB skip, Get the character position with the first ' ' ***/
134 end_pos := instr( list, ' ', 1 );
135
136 if end_pos = 0
137 then
138 /*** MB skip ***/
139 end_pos := length( list );
140 end if;
141
142 /*** MB skip ***/
143 value := rtrim(ltrim( substr( list, 1, end_pos )));
144 list := substr( list, end_pos+1 );
145
146 arp_standard.debug( 'arp_standard.get_next_word(' || list || ',' || value || ')-' );
147
148 if ( value is null )
149 then
150 return( FALSE );
151 else
152 return( TRUE );
153 end if;
154
155 end;
156
157
158
159 /*----------------------------------------------------------------------------*
160 | PUBLIC FUNCTION |
161 | ar_lookup |
162 | |
163 | DESCRIPTION |
164 | Interfaces with AR lookups, returning meaning for message type and code |
165 | |
166 | REQUIRES |
167 | lookup_type |
168 | lookup_code |
169 | |
170 | RETURNS |
171 | Meaning Lookup meaning |
172 | |
173 | EXCEPTIONS RAISED |
174 | None |
175 | |
176 | ERRORS RAISED |
177 | AR_NO_LOOKUP The Lookup Type and Code could not be found |
178 | |
179 | KNOWN BUGS |
180 | |
181 | NOTES |
182 | |
183 | HISTORY |
184 | 2/11/93 Nigel Smith Created |
185 | |
186 *----------------------------------------------------------------------------*/
187
188 function ar_lookup ( lookup_type in varchar2, lookup_code in varchar2 )
189 return varchar2 is
190
191 cursor sel_lookup_meaning( t in varchar2, c in varchar2 ) is
192 select meaning from ar_lookups where lookup_type = t
193 and lookup_code = c;
194
195 sel sel_lookup_meaning%ROWTYPE;
196
197 begin
198
199 open sel_lookup_meaning( lookup_type, lookup_code );
200 fetch sel_lookup_meaning into sel.meaning;
201 if sel_lookup_meaning%NOTFOUND
202 then
203 close sel_lookup_meaning;
204 fnd_message( 'AR_NO_LOOKUP', 'TYPE', lookup_type, 'CODE', lookup_code );
205 else
206 close sel_lookup_meaning;
207 return( sel.meaning );
208 end if;
209 end;
210
211
212 /*----------------------------------------------------------------------------*
213 | PUBLIC FUNCTION |
214 | gl_date_range_open |
215 | |
216 | DESCRIPTION |
217 | This function returns true if every General Ledger period is marked |
218 | as open between the date range supplied. At least one period must be |
219 | marked as open or future enterable, the function will return FALSE |
220 | if no periods are open. |
221 | |
222 | REQUIRES |
223 | pstart_date - Start date, typically trunc( date ) |
224 | in format 'YYYYMMDD' |
225 | pend_date - End date, typically, rounded up to just before |
226 | midnight using the ceil date function. |
227 | in format 'YYYYMMDD' |
228 | RETURNS |
229 | TRUE - If at least one period, and all others were open |
230 | or future enterable |
231 | |
232 | EXCEPTIONS RAISED |
233 | |
234 | KNOWN BUGS |
235 | <none> |
236 | |
237 | NOTES |
238 | |
239 | HISTORY |
240 | 15/02/93 Nigel Smith Created |
241 | 08/07/96 Subash C Modified data type to varchar2 (OSF issue) |
242 | |
243 *----------------------------------------------------------------------------*/
244
245 FUNCTION gl_date_range_open( pstart_date in varchar2,
246 pend_date in varchar2 ) RETURN BOOLEAN IS
247 date_ok BOOLEAN;
248 periods_found BOOLEAN;
249
250 BEGIN
251 date_ok := TRUE;
252 periods_found := FALSE;
253
254 FOR status in gl_periods_c( application_id,
255 sysparm.set_of_books_id,
256 pstart_date, pend_date )
257 LOOP
258 periods_found := TRUE;
259 IF status.closing_status not in ( 'O', 'F' )
260 THEN -- 'Open' or 'Future Enterable'
261 date_ok := false;
262 EXIT;
263 END IF;
264 END LOOP;
265
266 IF periods_found and date_ok
267 THEN
268 return( TRUE );
269 ELSE
270 return( FALSE );
271 END IF;
272
273 END;
274
275
276 /*----------------------------------------------------------------------------*
277 | PUBLIC FUNCTION |
278 | functional_amount |
279 | |
280 | DESCRIPTION |
281 | This function returns the functional amount for a given foreign amount. |
282 | THe functional amount is rounded to the correct precision. |
283 | |
284 | REQUIRES |
285 | Amount - the foreign amount |
286 | Exchange Rate - to use when converting to functional amount |
287 | one of: |
288 | Currency Code - of the functional amount |
289 | Precision - of the functional amount |
290 | minimum accountable unit - of the functional amount |
291 | |
292 | RETURNS |
293 | amount * exchange_rate to correct rounding for currency |
294 | |
295 | EXCEPTIONS RAISED |
296 | Oracle Error If Currency Code, Precision and minimum accountable |
297 | are all NULL or if Amount or Exchange Rate ar NULL |
298 | |
299 | Oracle Error If can not find information for Currency Code |
300 | supplied |
301 | |
302 | KNOWN BUGS |
303 | <none> |
304 | |
305 | NOTES |
306 | |
307 | HISTORY |
308 | 2/10/93 Martin Morris Created |
309 | 7/21/95 Martin Johnson Replaced fnd_message with |
310 | user-defined exception so that|
311 | pragma restrict_references |
312 | does not fail |
313 | |
314 *----------------------------------------------------------------------------*/
315
316
317 FUNCTION functional_amount(amount IN NUMBER,
318 currency_code IN VARCHAR2,
319 exchange_rate IN NUMBER,
320 precision IN NUMBER,
321 min_acc_unit IN NUMBER) RETURN NUMBER IS
322
323 BEGIN
324
325 RETURN(
326 arpcurr.functional_amount(amount,
327 currency_code,
328 exchange_rate,
329 precision,
330 min_acc_unit)
331 );
332
333 EXCEPTION
334 WHEN OTHERS THEN
335 RAISE;
336
337 END functional_amount;
338
339
340
341 /*-------------------------------------------------------------------------+
342 | |
343 | PUBLIC FLAGS |
344 | Since Public Constants are not supported yet betwen server and client |
345 | applications in PL/SQL 1 and 2. The following public functions are |
346 | declared so that application programmer can reference internal private |
347 | constants. |
348 | |
349 +-------------------------------------------------------------------------*/
350
351
352 function MD_MSG_NUMBER return number is
353 begin
354 return( INT_MD_MSG_NUMBER ); -- Show Message Number
355 end;
356
357 function MD_MSG_TEXT return number is
358 begin
359 return( INT_MD_MSG_TEXT ); -- Show Message Text
360 end;
361
362 function MD_MSG_NAME return number is
363 begin
364 return( INT_MD_MSG_NAME ); -- Show message Name
365 end;
366
367 function MD_MSG_TOKENS return number is
368 begin
369 return( INT_MD_MSG_TOKENS ); -- Return Message Tokens and Numbers
370 end;
371
372 function MD_MSG_EXPLANATION return number is
373 begin
374 return( INT_MD_MSG_EXPLANATION ); -- Not supported yet
375 end;
376
377 function MD_MSG_FIND_NUMBER return number is
378 begin
379 return( INT_MD_MSG_FIND_NUMBER ); -- Use Message Number not Name
380 end;
381
382
383
384 /*----------------------------------------------------------------------------*
385 | PUBLIC PROCEDURE |
386 | debug - Display text message if in debug mode |
387 | enable_debug - Enable run time debugging |
388 | disable_debug - Disable run time debugging |
389 | |
390 | DESCRIPTION |
391 | Generate standard debug information sending it to dbms_output so that |
392 | the client tool can log it for the user. |
393 | NOTE: As of 115.16 of this package, we no longer use dbms_output to
394 | route messages to log or screen. As of version 115.36, we now use
395 | FND_LOG routines to record the messages in the FND_LOG_MESSAGES table.
396 |
397 | The current version of the debug procedure works like this:
398 |
399 | arp_standard.debug('arp_util_pkg.get_value()+', --> msg text
400 | 'plsql', --> prefix (source)
401 | 'arp_util_pkg.get_value.begin', --> package name
402 | FND_LOG_LEVEL.PROCEDURE); --> FND constant
403 |
404 | Now, in reality, only the first parameter is required and the others
405 | will be defaulted or determined based on content of the message.
406 |
407 | arp_standard.debug('arp_util_pkg.get_value()+');
408 |
409 | Both of the above examples will log the following message:
410 |
411 | module := ar.plsql.arp_util_pkg.get_value.begin
412 | level := 2 (this equates to FND_LOG.LEVEL_PROCEDURE)
413 | text := 'arp_util_pkg.get_value()+'
414 |
415 | Several rules you should keep in mind...
416 | (1) I never manipulate the message text (looks just like it did before)
417 | (2) I default the source as 'plsql' in most cases.
418 | Valid values include 'resource','reports','forms','src.<subdir>'
419 | (3) I use parentheses () and +/- signs to determine the module name
420 | and log level. Only use parens to designate calls and put a +
421 | or - adjacent to the closing (right) paren to default
422 | FND_LOG.LEVEL_PROCEDURE and the '.begin' or '.end'
423 | DO NOT HAVE EXTRA SPACES WITHIN PARENS OR PROCEDURE NAMES
424 | (4) The word 'EXCEPTION' is interpreted as an
425 | FND_LOG.LEVEL_EXCEPTION message.
426 |
427 | Here are sample uses of arp_standard.debug:
428 |
429 | Typical entry/exit messages in plsql packages:
430 | arp_standard.debug('arp_rounding.correct_other_receivables()+');
431 |
432 | Inline in .pld library:
433 | arp_standard.debug('arxtwmai_folder.validate(EVENT)-','resource');
434 |
435 | Inline in pro*C:
436 | arp_standard.debug('raaiad()+', 'src.autoinv');
437 |
438 | Detail messages in plsql:
439 | arp_standard.debug('request_id = ' || l_request_id,
440 | 'plsql',
441 | 'arp_util.get_gl_date');
442 |
443 | Specific exception/error logging:
444 | arp_standard.debug('ERROR: Processed has failed',
445 | 'plsql',
446 | 'arp_trx_validate.gl_date',
447 | 'FND_LOG.LEVEL_ERROR');
448 |
449 | For most logging messages, it is sufficient to just supply the message
450 | text and maybe the prefix (source type). As long as you follow the naming
451 | convention and use of parens, I can get the procedure name for you.
452 |
453 | The valid FND_LOG LEVEL values are:
454 |
455 | FND_LOG.LEVEL_STATEMENT - most detailed level - should be used
456 | for displaying values and other run-time specifics
457 | FND_LOG.LEVEL_PROCEDURE - Entry and exit points. I determine
458 | this level in the algorythm logic by looking for )+, +),
459 | )-, and -). When using this level, you should include
460 | '.begin' or '.end' to the module_name
461 | FND_LOG.LEVEL_EVENT - A higher level message for indicating
462 | things like 'record added' or 'calling arp_whatever_api'
463 | FND_LOG.LEVEL_EXCEPTION - recording a handled exception in an
464 | exception block. By default, I look for the word
465 | 'exception' in the message text and set this level.
466 | FND_LOG.LEVEL_ERROR - An error message displayed to the end-user
467 | FND_LOG.LEVEL_UNEXPECTED - A failure that could result in
468 | the product becoming unstable.
469 |
470 | In the database, the log level is reflected as a number from 1 to 6
471 | where LEVEL_STATEMENT = 1 and LEVEL_UNEXPECTED = 6
472 |
473 | REQUIRES |
474 | line_of_text The line of text that will be displayed. |
475 | |
476 | EXCEPTIONS RAISED |
477 | |
478 | KNOWN BUGS |
479 | |
480 | NOTES |
481 | |
482 | HISTORY |
483 | 26 Mar 93 Nigel Smith Created |
484 | 12-NOV-97 OSTEINME added file io debugging |
485 | 24-SEP-03 M Raymond Modified debug routine to parse message
486 | for module name and assess message level
487 | Also added parameters that map to the
488 | prefix, module, and level.
489 | |
490 *----------------------------------------------------------------------------*/
491
492 procedure file_debug(line in varchar2) IS
493 x number;
494 begin
495 if (pg_file_name is not null) THEN
496 utl_file.put_line(pg_fp, line);
497 utl_file.fflush(pg_fp);
498 end if;
499 end file_debug;
500
501 procedure enable_file_debug(path_name in varchar2,
502 file_name in varchar2) IS
503
504 x number;
505 begin
506
507 if (pg_file_name is null) THEN
508 pg_fp := utl_file.fopen(path_name, file_name, 'a');
509 pg_file_name := file_name;
510 pg_path_name := path_name;
511 end if;
512
513 -- fnd_message does not compile here, since it is redefined in this scope.
514
515 exception
516 when utl_file.invalid_path then
517 -- fnd_message.set_name('AR', 'GENERIC_MESSAGE');
518 -- fnd_message.set_token('GENERIC_TEXT', 'Invalid path: '||path_name);
519 -- x := 1/0;
520 app_exception.raise_exception;
521 when utl_file.invalid_mode then
522 -- fnd_message.set_name('AR', 'GENERIC_MESSAGE');
523 -- fnd_message.set_token('GENERIC_TEXT', 'Cannot open file '||file_name||
524 -- ' in write mode.');
525 app_exception.raise_exception;
526
527 end ;
528
529 procedure disable_file_debug is
530 begin
531 if (pg_file_name is not null) THEN
532 utl_file.fclose(pg_fp);
533 end if;
534 end;
535
536 procedure debug( line in varchar2,
537 msg_prefix in varchar2,
538 msg_module in varchar2,
539 msg_level in number
540 ) is
541 l_msg_prefix varchar2(64);
542 l_msg_level number;
543 l_msg_module varchar2(256);
544 l_beg_end_suffix varchar2(15);
545 l_org_cnt number;
546 l_line varchar2(32767);
547 begin
548 l_line := line;
549
550 IF (pg_file_name IS NOT NULL) THEN
551 file_debug(l_line);
552 ELSE
553 /* Bug 3161609 - Implement FND debugging routines for all plsql
554 and resourceareas of product. Note that the prior version
555 of debug logic would send
556 strings of 255 bytes of message text. The current version can take
557 single strings of 4000 bytes or less so I have discarded the looping
558 mechanism */
559
560 l_msg_prefix := 'a' || 'r' || '.' || msg_prefix || '.';
561
562 /* EXCEPTIONS:
563 - if length of message > 99
564 - if text contains (s)
565 */
566 IF lengthb(l_line) > 99 OR
567 INSTRB(l_line, '(s)') <> 0
568 THEN
569 l_msg_level := FND_LOG.LEVEL_STATEMENT;
570 l_msg_module := l_msg_prefix || NVL(g_msg_module, 'UNKNOWN');
571
572 -- This logs the message
573 /* Bug 4361955 */
574 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
575 THEN
576 FND_LOG.STRING(l_msg_level, l_msg_module, substr(l_line,1,4000));
577 END IF;
578
579 RETURN;
580 END IF;
581
582 -- set msg_level for this message
583 IF (msg_level IS NULL)
584 THEN
585 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0)
586 THEN
587 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
588 ELSIF (INSTRB(l_line, ')+') <> 0 OR
589 INSTRB(l_line, '+)') <> 0)
590 THEN
591 l_msg_level := FND_LOG.LEVEL_PROCEDURE;
592 l_beg_end_suffix := '.begin';
593 ELSIF (INSTRB(l_line, ')-') <> 0 OR
594 INSTRB(l_line, '-)') <> 0)
595 THEN
596 l_msg_level := FND_LOG.LEVEL_PROCEDURE;
597 l_beg_end_suffix := '.end';
598 ELSE
599 l_msg_level := FND_LOG.LEVEL_STATEMENT;
600 l_beg_end_suffix := NULL;
601 END IF;
602 ELSE
603 /* Verify that level is between 1 and 6 */
604 IF msg_level >= 1 AND msg_level <= 6
605 THEN
606 l_msg_level := msg_level;
607 ELSE
608 /* Invalid message level, default 1 */
609 l_msg_level := 1;
610 END IF;
611 END IF;
612
613 -- set module for this message
614 IF (msg_module IS NULL)
615 THEN
616
617 -- chop off extraneous stuff on right end of string
618 l_msg_module := SUBSTRB(RTRIM(l_line), 1,
619 INSTRB(l_line, '(') - 1);
620
621 -- chop off extraneous stuff on left
622 l_msg_module := SUBSTRB(l_msg_module,
623 INSTRB(l_msg_module, ' ', -3 ) + 1);
624
625 /* If we were unable to get a module name, use
626 the global (previously stored) one */
627 IF l_msg_module IS NULL
628 THEN
629 l_msg_module := NVL(g_msg_module, 'UNKNOWN');
630 ELSE
631 g_msg_module := l_msg_module;
632 END IF;
633
634 l_msg_module := l_msg_prefix || l_msg_module || l_beg_end_suffix;
635 ELSE
636 l_msg_module := l_msg_prefix || msg_module;
637 END IF;
638
639 -- This actually logs the message
640 /* Bug 4361955 */
641 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
642 THEN
643 FND_LOG.STRING(l_msg_level, l_msg_module, l_line);
644 END IF;
645
646 END IF;
647 exception
648 when others then
649 raise;
650 end;
651
652 procedure enable_debug is
653 begin
654 debug_flag := true;
655 -- dbms_output.enable;
656 end;
657
658 procedure enable_debug( buffer_size NUMBER ) is
659 begin
660 debug_flag := true;
661 -- dbms_output.enable( buffer_size );
662 end;
663
664 procedure disable_debug is
665 begin
666 debug_flag := false;
667 end;
668
669 /*----------------------------------------------------------------------------*
670 | PUBLIC PROCEDURE |
671 | set who information |
672 | |
673 | DESCRIPTION |
674 | Set foundation who information so that all future packages and |
675 | procedures can reference the correct value. |
676 | |
677 | REQUIRES |
678 | |
679 | user_id Foundation User ID |
680 | request_id Concurrent Request ID |
681 | program_application_id |
682 | program_id |
683 | last_update_login |
684 | |
685 | EXCEPTIONS RAISED |
686 | |
687 | KNOWN BUGS |
688 | |
689 | NOTES |
690 | |
691 | HISTORY |
692 | 2/11/93 Nigel Smith Created |
693 | |
694 *----------------------------------------------------------------------------*/
695
696 PROCEDURE set_who_information( user_id in number,
697 request_id in number,
698 program_application_id in number,
699 program_id in number,
700 last_update_login in number ) IS
701 BEGIN
702
703 debug( '>> SET_WHO_INFORMATION( ' ||
704 to_char(user_id) || ', ' ||
705 to_char( request_id ) || ', ' ||
706 to_char(program_application_id ) || ', ' ||
707 to_char( program_id ) || ', ' ||
708 to_char( last_update_login ) || ' )' );
709
710
711 /*
712 BUGFIX: 226504, row who information not correct
713 ===============================================
714
715 */
716
717 profile.user_id := nvl(fnd_global.user_id, -1);
718 profile.request_id := fnd_global.conc_request_id;
719 profile.program_application_id := fnd_global.prog_appl_id;
720 profile.program_id := fnd_global.conc_program_id;
721 profile.last_update_login := fnd_global.conc_login_id;
722
723 debug( '<< SET_WHO_INFORMATION' );
724
725 END;
726
727 /*----------------------------------------------------------------------------*
728 | PUBLIC PROCEDURE |
729 | set application information |
730 | |
731 | DESCRIPTION |
732 | Set foundation application id and language variables, so that all |
733 | future calls to packaged procedures can reference the correct value. |
734 | |
735 | REQUIRES |
736 | application_id |
737 | language_id |
738 | |
739 | EXCEPTIONS RAISED |
740 | |
741 | KNOWN BUGS |
742 | |
743 | NOTES |
744 | |
745 | HISTORY |
746 | 2/11/93 Nigel Smith Created |
747 | 05/20/93 Nigel Smith Checks that params are not null |
748 | |
749 *----------------------------------------------------------------------------*/
750
751 PROCEDURE set_application_information( appl_id in number,
752 language_id in number ) is
753 l_base_language varchar2(50);
754 BEGIN
755 if appl_id is null
756 then
757 fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'SET_APPLICATION_INFORMATION',
758 'PARAMETER', 'APPL_ID' );
759 end if;
760 if language_id is null
761 then
762 fnd_message( 'AR_PP_NULL_PARAMETER', 'OBJECT', 'SET_APPLICATION_INFORMATION',
763 'PARAMETER', 'LANGUAGE_ID' );
764 end if;
765
766 application_id := 222;
767 profile.language_id := 0;
768 /* base_language returns NLS Language */
769 profile.language_code := fnd_global.base_language;
770
771 END ;
772
773 /*----------------------------------------------------------------------------*
774 | PUBLIC FUNCTION |
775 | ceil (in date ) return date |
776 | |
777 | DESCRIPTION |
778 | Rounds any given date to the last second before midnight of that night |
779 | usefull in ensuring any given date is between trunc(start) and ceil(max)|
780 | |
781 | |
782 | REQUIRES |
783 | d - Any date and time value |
784 | |
785 | RETURNS |
786 | Date set to DD-MON-YYYY 23:59:59 |
787 | |
788 | EXCEPTIONS RAISED |
789 | |
790 | KNOWN BUGS |
791 | |
792 | NOTES |
793 | |
794 | HISTORY |
795 | 2/11/93 Nigel Smith Created |
796 | |
797 *----------------------------------------------------------------------------*/
798
799
800 function ceil( d in date ) return date IS
801 begin
802 return( trunc( d + 1 ) - 1/24/60/60 );
803 end;
804
805 /*----------------------------------------------------------------------------*
806 | PUBLIC FUNCTIONS |
807 | Bit Wise Operations |
808 | |
809 | DESCRIPTION |
810 | Provides support for bitwise operations within PL/SQL, useful for |
811 | control flags etc. |
812 | |
813 | |
814 | FUNCTIONS |
815 | even( n ) -- Returns true if n is even |
816 | odd( n ) -- Returns true if n is odd |
817 | set_flag -- Sets a given flag in the options variable |
818 | clear_flag -- Clears a given flag from the options variable |
819 | check_flag -- Returns true if a given flag is set |
820 | |
821 | KNOWN BUGS |
822 | All flags must be powers of 10. |
823 | |
824 | NOTES |
825 | |
826 | HISTORY |
827 | 2/11/93 Nigel Smith Created |
828 | |
829 *----------------------------------------------------------------------------*/
830
831 function even( n in number ) return boolean is
832 begin
833 if ( trunc(n) / 2 ) = trunc( trunc(n) / 2 )
834 then
835 return( TRUE );
836 else
837 return( FALSE );
838 end if;
839 end ;
840
841 function odd( n in number ) return boolean is
842 begin
843 return( not even( n ) );
844 end ;
845
846
847 function check_flag( options in number, flag in number ) return boolean is
848 begin
849 return( odd( options / flag ) );
850 end;
851
852
853 procedure clear_flag( options in out NOCOPY number, flag in number ) is
854 begin
855 if check_flag( options, flag )
856 then
857 options := options - flag;
858 end if;
859 end;
860
861 procedure set_flag( options in out NOCOPY number, flag in number ) is
862 begin
863 if not check_flag( options, flag )
864 then
865 options := options + flag;
866 end if;
867 end;
868
869
870 /*----------------------------------------------------------------------------*
871 | PUBLIC FUNCTION |
872 | fnd_message |
873 | |
874 | DESCRIPTION |
875 | Interfaces with AOL's message dictionary |
876 | |
877 | Called as a function, it returns the message text with any tokens |
878 | expanded to the passed token values. |
879 | |
880 | Called as a procedure, it raises an oracle error ( -20000 ) and the |
881 | message number, text with expanded tokens for the passed message name |
882 | |
883 | REQUIRES |
884 | Message Name - Message dictionary name |
885 | TOKEN, VALUE - Up to four pairs of token, values |
886 | |
887 | RETURNS |
888 | Expanded message text, or if called as a procedure an oracle error |
889 | |
890 | EXCEPTIONS RAISED |
891 | |
892 | KNOWN BUGS |
893 | application_id is currently held in package ar, should be in AOL |
894 | |
895 | NOTES |
896 | |
897 | HISTORY |
898 | 2/11/93 Nigel Smith Created |
899 | 03/23/93 Nigel Smith Select from fnd_messages now uses application_id|
900 | and language id from session profiles |
901 | 03/10/00 Tara Welby Add substrb |
902 | |
903 *----------------------------------------------------------------------------*/
904
905
906 function fnd_message( md_options in number, msg_name in varchar2 ) return varchar2 is
907
908
909 /*----------------------------------------------------------------------------*
910 | PRIVATE CURSOR |
911 | fnd_message_c |
912 | |
913 | DESCRIPTION |
914 | Gets the message_number and message_text from fnd_messages |
915 | |
916 *----------------------------------------------------------------------------*/
917
918 /* bug2995947 : Replaced profile.language_code with userenv('LANG')
919 in order to get proper language message text */
920 cursor fnd_message_c( app_id in number, msg_name in varchar2 ) IS
921 select message_number, message_text
922 from fnd_new_messages
923 where application_id = app_id
924 and language_code = userenv('LANG')
925 and message_name = msg_name;
926
927 /*---------------------------------------------------------------------------*
928 | PRIVATE DATATYPES |
929 | |
930 *---------------------------------------------------------------------------*/
931
932 rtn_msg varchar2(2000) := '';
933 message_number number;
934 message_text varchar2(2000);
935
936 begin
937
938
939 if md_options = INT_MD_MSG_NAME
940 then
941 --- Only needs message name, dont check the database.
942 return( msg_name );
943 else
944 --- Need information, held in the database.
945
946 OPEN fnd_message_c( application_id, msg_name );
947 FETCH fnd_message_c into message_number, message_text;
948
949 if fnd_message_c%FOUND
950 THEN
951
952 if check_flag( MD_OPTIONS, INT_MD_MSG_NUMBER )
953 then
954 rtn_msg := 'APP-' || replace(to_char( message_number, '00000' ), ' ', null) ;
955 end if;
956
957 if check_flag( MD_OPTIONS, INT_MD_MSG_NAME )
958 then
959 rtn_msg := msg_name;
960 end if;
961
962 if check_flag( MD_OPTIONS, INT_MD_MSG_TEXT )
963 then
964 if rtn_msg is null
965 then
966 rtn_msg := ltrim(message_text);
967 else
968 -- TAW 1222450 PL/SQL Numeric or Value Error add substrb
969 rtn_msg := substrb((rtn_msg || ': ' || message_text),1,2000) ;
970 end if;
971 end if;
972
973 ELSE
974 rtn_msg := 'APP-00001: Unable to find message: ' || msg_name ;
975 END IF;
976 CLOSE fnd_message_c;
977 return( rtn_msg );
978 end if;
979 end;
980
981 function fnd_message( msg_name in varchar2 ) return varchar2 is
982 begin
983 return( fnd_message( INT_MD_MSG_TEXT, msg_name ));
984 end;
985
986 function msg_tkn_expand( md_options in number, msg in varchar2, T1 in varchar2, V1 in varchar2 ) return varchar2 is
987 begin
988 if md_options = md_msg_name -- User only want message names, no tokens!
989 then
990 return(msg);
991 else
992 if T1 is not null and instr( msg, '&' || T1 ) = 0
993 then -- Token not found, append token to end of string with value.
994 return( msg || ' '||T1 ||'='||V1 );
995 else
996 return( REPLACE( msg, '&' || T1, V1 ));
997 end if;
998 end if;
999 end;
1000
1001 function fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in varchar2 ) return varchar2 is
1002 begin
1003 return( msg_tkn_expand( md_options, fnd_message( md_options, msg_name ), T1, V1 ) );
1004 end;
1005
1006 function fnd_message( msg_name in varchar2, T1 in varchar2, V1 in varchar2 ) return varchar2 is
1007 begin
1008 return( msg_tkn_expand( md_options, fnd_message( msg_name ), T1, V1 ) );
1009 end;
1010
1011 function fnd_message( md_options in number, msg_name in varchar2,
1012 T1 in varchar2, V1 in varchar2,
1013 T2 in varchar2, V2 in varchar2 ) return varchar2 is
1014 begin
1015 return( msg_tkn_expand( md_options, fnd_message( md_options, msg_name, T1, V1 ), T2, V2 ));
1016 end;
1017
1018
1019 function fnd_message( msg_name in varchar2,
1020 T1 in varchar2, V1 in varchar2,
1021 T2 in varchar2, V2 in varchar2 ) return varchar2 is
1022 begin
1023 return( msg_tkn_expand( md_options, fnd_message( msg_name, T1, V1 ), T2, V2 ));
1024 end;
1025
1026 function fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in varchar2, T2 in varchar2, V2 in varchar2,
1027 T3 in varchar2, V3 in varchar2 ) return varchar2 is
1028 begin
1029 return( msg_tkn_expand( md_options, fnd_message( md_options, msg_name, T1, V1, T2, V2 ), T3, V3 ));
1030 end;
1031
1032 function fnd_message( msg_name in varchar2, T1 in varchar2, V1 in varchar2, T2 in varchar2, V2 in varchar2,
1033 T3 in varchar2, V3 in varchar2 ) return varchar2 is
1034 begin
1035 return( msg_tkn_expand( md_options, fnd_message( msg_name, T1, V1, T2, V2 ), T3, V3 ));
1036 end;
1037
1038 function fnd_message( msg_name in varchar2, T1 in varchar2, V1 in varchar2, T2 in varchar2, V2 in varchar2,
1039 T3 in varchar2, V3 in varchar2, T4 in varchar2, V4 in varchar2 ) return varchar2 is
1040 begin
1041 return( msg_tkn_expand( md_options, fnd_message( msg_name, T1, V1, T2, V2, T3, V3 ), T4, V4 ));
1042 end;
1043
1044 function fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in varchar2, T2 in varchar2, V2 in varchar2,
1045 T3 in varchar2, V3 in varchar2, T4 in varchar2, V4 in varchar2 ) return varchar2 is
1046 begin
1047 return( msg_tkn_expand( md_options, fnd_message( md_options, msg_name, T1, V1, T2, V2, T3, V3 ), T4, V4 ));
1048 end;
1049
1050 procedure fnd_message is
1051 begin
1052 raise_application_error( AR_ERROR_NUMBER, fnd_message( md_options ));
1053 end;
1054
1055 procedure fnd_message(md_options in number ) is
1056 begin
1057 raise_application_error( AR_ERROR_NUMBER, fnd_message( md_options ));
1058 end;
1059
1060 procedure fnd_message( md_options in number, msg_name in varchar2 ) is
1061 begin
1062 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name ) );
1063 end;
1064
1065 procedure fnd_message( md_options in number,
1066 msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2 ) is
1067 begin
1068 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1 ) );
1069 end;
1070
1071 procedure fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1072 T2 in varchar2, V2 in VARCHAR2 ) is
1073 begin
1074 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2 ) );
1075 end;
1076
1077 procedure fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1078 T2 in varchar2, V2 in VARCHAR2,
1079 T3 in varchar2, V3 in VARCHAR2 ) is
1080 begin
1081 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2, T3, V3 ) );
1082 end;
1083
1084 procedure fnd_message( md_options in number, msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1085 T2 in varchar2, V2 in VARCHAR2,
1086 T3 in varchar2, V3 in VARCHAR2,
1087 T4 in varchar2, V4 in VARCHAR2 ) is
1088 begin
1089 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2, T3, V3, T4, V4 ) );
1090 end;
1091
1092
1093 /*---------------------------------------------------------------------------*
1094 | |
1095 | PUBLIC FUNCTION: previous_message |
1096 | |
1097 | These functions are required because PL/SQL does not return the user |
1098 | defined error message in sqlerrm. Optional message dictionary control |
1099 | flags are used to control token expansion, message lookup etc. |
1100 | |
1101 | EXAMPLES |
1102 | |
1103 | str := previous_message; |
1104 | str := previous_message( md_msg_name + md_msg_tokens ); |
1105 | |
1106 *---------------------------------------------------------------------------*/
1107
1108 function fnd_message return varchar2 is
1109 begin
1110 return( fnd_message( MD_MSG_NAME ) );
1111 end;
1112
1113 function fnd_message( md_options in number ) return varchar2 is
1114 begin
1115 return( previous_message( md_options ) );
1116 end;
1117
1118 function previous_message( md_options in number ) return varchar2 is
1119 str varchar2(800);
1120 begin
1121 str := fnd_message( md_options,
1122 previous_msg.name,
1123 previous_msg.t1,
1124 previous_msg.v1,
1125 previous_msg.t2,
1126 previous_msg.v2,
1127 previous_msg.t3,
1128 previous_msg.v3,
1129 previous_msg.t4,
1130 previous_msg.v4 );
1131
1132 if check_flag( md_options, md_msg_tokens )
1133 then
1134 str := str || ' ' ||
1135 previous_msg.t1 || ' ' ||
1136 previous_msg.v1 || ' ' ||
1137 previous_msg.t2 || ' ' ||
1138 previous_msg.v2 || ' ' ||
1139 previous_msg.t3 || ' ' ||
1140 previous_msg.t3 || ' ' ||
1141 previous_msg.t4 || ' ' ||
1142 previous_msg.t4;
1143 end if;
1144
1145 return(str);
1146
1147 end;
1148
1149
1150
1151 function previous_message return varchar2 is
1152 begin
1153 return( fnd_message( MD_MSG_NAME,
1154 previous_msg.name,
1155 previous_msg.t1,
1156 previous_msg.v1,
1157 previous_msg.t2,
1158 previous_msg.v2,
1159 previous_msg.t3,
1160 previous_msg.t3,
1161 previous_msg.t4,
1162 previous_msg.t4 ));
1163
1164 end;
1165
1166
1167 procedure fnd_message( msg_name in varchar2 ) is
1168 begin
1169 previous_msg.name := msg_name;
1170 previous_msg.t1 := null;
1171 previous_msg.v1 := null;
1172 previous_msg.t2 := null;
1173 previous_msg.v2 := null;
1174 previous_msg.t3 := null;
1175 previous_msg.v3 := null;
1176 previous_msg.t4 := null;
1177 previous_msg.v4 := null;
1178 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name ) );
1179 end;
1180
1181 procedure fnd_message( msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2 ) is
1182 begin
1183 previous_msg.name := msg_name;
1184 previous_msg.t1 := t1;
1185 previous_msg.v1 := v1;
1186 previous_msg.t2 := null;
1187 previous_msg.v2 := null;
1188 previous_msg.t3 := null;
1189 previous_msg.v3 := null;
1190 previous_msg.t4 := null;
1191 previous_msg.v4 := null;
1192 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1 ) );
1193 end;
1194
1195 procedure fnd_message( msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1196 T2 in varchar2, V2 in VARCHAR2 ) is
1197 begin
1198 previous_msg.name := msg_name;
1199 previous_msg.t1 := t1;
1200 previous_msg.v1 := v1;
1201 previous_msg.t2 := t2;
1202 previous_msg.v2 := v2;
1203 previous_msg.t3 := null;
1204 previous_msg.v3 := null;
1205 previous_msg.t4 := null;
1206 previous_msg.v4 := null;
1207 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2 ) );
1208 end;
1209
1210 procedure fnd_message( msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1211 T2 in varchar2, V2 in VARCHAR2,
1212 T3 in varchar2, V3 in VARCHAR2 ) is
1213 begin
1214 previous_msg.name := msg_name;
1215 previous_msg.t1 := t1;
1216 previous_msg.v1 := v1;
1217 previous_msg.t2 := t2;
1218 previous_msg.v2 := v2;
1219 previous_msg.t3 := t3;
1220 previous_msg.v3 := v3;
1221 previous_msg.t4 := null;
1222 previous_msg.v4 := null;
1223 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2, T3, V3 ) );
1224 end;
1225
1226 procedure fnd_message( msg_name in varchar2, T1 in varchar2, V1 in VARCHAR2,
1227 T2 in varchar2, V2 in VARCHAR2,
1228 T3 in varchar2, V3 in VARCHAR2,
1229 T4 in varchar2, V4 in VARCHAR2 ) is
1230 begin
1231 previous_msg.name := msg_name;
1232 previous_msg.t1 := t1;
1233 previous_msg.v1 := v1;
1234 previous_msg.t2 := t2;
1235 previous_msg.v2 := v2;
1236 previous_msg.t3 := t3;
1237 previous_msg.v3 := v3;
1238 previous_msg.t4 := t4;
1239 previous_msg.v4 := v4;
1240 raise_application_error(AR_ERROR_NUMBER, fnd_message( md_options, msg_name, T1, V1, T2, V2, T3, V3, T4, V4 ) );
1241 end;
1242
1243 /*----------------------------------------------------------------------------*
1244 | PUBLIC PROCEDURE |
1245 | gl_period_info() |
1246 | |
1247 | DESCRIPTION |
1248 | This function returns information about the GL period that contains |
1249 | a given GL date. |
1250 | It does not information about adjustment periods. |
1251 | In the case of overlapping periods, the function chooses the period with|
1252 | the lowest period num. |
1253 | |
1254 | REQUIRES |
1255 | gl_date - A date in the desired period |
1256 | start_date - Returned values |
1257 | end_date / |
1258 | closing_status / |
1259 | period_type / |
1260 | period_year / |
1261 | period_num / |
1262 | quarter_num / |
1263 | period_name / |
1264 | |
1265 | |
1266 | EXCEPTIONS RAISED |
1267 | None |
1268 | |
1269 | KNOWN BUGS |
1270 | |
1271 | NOTES |
1272 | Only the first record returned by the cursor is used. The others |
1273 | represent overlapping periods. |
1274 | |
1275 | HISTORY |
1276 | |
1277 *----------------------------------------------------------------------------*/
1278
1279
1280 procedure gl_period_info( gl_date in date,
1281 period_name out NOCOPY varchar2,
1282 start_date out NOCOPY date,
1283 end_date out NOCOPY date,
1284 closing_status out NOCOPY varchar2,
1285 period_type out NOCOPY varchar2,
1286 period_year out NOCOPY number,
1287 period_num out NOCOPY number,
1288 quarter_num out NOCOPY number
1289 ) is
1290
1291
1292 cursor gl_periods_c( gl_date in date ) IS
1293 select period_name,
1294 start_date,
1295 end_date,
1296 closing_status,
1297 period_type,
1298 period_year,
1299 period_num,
1300 quarter_num
1301 from gl_period_statuses ps
1302 --begin anuj
1303 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;09/11/2002*/
1304 where ps.set_of_books_id = sysparm.set_of_books_id
1305 /* Multi-Org Access Control Changes for SSA;End;anukumar;09/11/2002*/
1306 --end anuj
1307 and application_id = 222
1308 and adjustment_period_flag = 'N'
1309 and trunc(gl_date) between start_date and end_date
1310 order by period_num
1311 ;
1312
1313 /* cursor gl_periods_c( gl_date in date ) IS
1314 select period_name,
1315 start_date,
1316 end_date,
1317 closing_status,
1318 period_type,
1319 period_year,
1320 period_num,
1321 quarter_num
1322 from gl_period_statuses ps,
1323 ar_system_parameters p
1324 where ps.set_of_books_id = p.set_of_books_id
1325 and application_id = 222
1326 and adjustment_period_flag = 'N'
1327 and trunc(gl_date) between start_date and end_date
1328 order by period_num
1329 ;
1330 */
1331
1332
1333 /*---------------------------------------------------------------------------*
1334 | PRIVATE DATATYPES |
1335 | |
1336 *---------------------------------------------------------------------------*/
1337
1338
1339 begin
1340
1341 period_name := '';
1342 start_date := '';
1343 end_date := '';
1344 closing_status := '';
1345
1346
1347 OPEN gl_periods_c(gl_date);
1348
1349 FETCH gl_periods_c
1350 into period_name,
1351 start_date,
1352 end_date,
1353 closing_status,
1354 period_type,
1355 period_year,
1356 period_num,
1357 quarter_num;
1358
1359 CLOSE gl_periods_c;
1360
1361
1362 end;
1363
1364
1365 /*----------------------------------------------------------------------------*
1366 | PUBLIC FUNCTION |
1367 | gl_period_name() |
1368 | |
1369 | DESCRIPTION |
1370 | Returns the name of the GL period that contains a given GL date. |
1371 | The function does not return the names of adjustment periods. |
1372 | In the case of overlapping periods, the function chooses the period with|
1373 | the lowest period num. |
1374 | |
1375 | REQUIRES |
1376 | gl_date - A date in the desired period |
1377 | |
1378 | RETURNS |
1379 | The name of the peruod that contains gl_date. |
1380 | If the gl_date is not in a period, null is returned. |
1381 | |
1382 | EXCEPTIONS RAISED |
1383 | None |
1384 | |
1385 | KNOWN BUGS |
1386 | |
1387 | NOTES |
1388 | |
1389 | HISTORY |
1390 | |
1391 *----------------------------------------------------------------------------*/
1392
1393 function gl_period_name( gl_date in date ) return varchar2 is
1394
1395 period_name varchar2(26);
1396 start_date date;
1397 end_date date;
1398 closing_status varchar(1);
1399 period_type varchar2(15);
1400 period_year number(15);
1401 period_num number(15);
1402 quarter_num number(15);
1403
1404
1405 begin
1406
1407 arp_standard.gl_period_info( gl_date,
1408 period_name,
1409 start_date,
1410 end_date,
1411 closing_status,
1412 period_type,
1413 period_year,
1414 period_num,
1415 quarter_num);
1416
1417 return(period_name);
1418
1419
1420 end;
1421
1422 /*----------------------------------------------------------------------------*
1423 | PRIVATE FUNCTION |
1424 | is_gl_date_valid() |
1425 | |
1426 | DESCRIPTION |
1427 | This function determines if a given date is a valid GL date. |
1428 | |
1429 | A GL date is considered valid if all of the following conditions are |
1430 | true. |
1431 | |
1432 | 1) The date is in an Open or Future period, or it is in a Never Opened |
1433 | period and the Allow Not Open Flag is set to Yes. |
1434 | |
1435 | 2) The date is greater than or equal to the trx_date and the three |
1436 | validation dates if they are specified. |
1437 | |
1438 | 3) The period cannot be an Adjustment period. |
1439 | |
1440 | |
1441 | |
1442 | |
1443 | PARAMETERS |
1444 | p_gl_date IN Optional |
1445 | p_trx_date IN Optional |
1446 | p_validation_date1 IN Optional |
1447 | p_validation_date2 IN Optional |
1448 | p_validation_date3 IN Optional |
1449 | p_allow_not_open_flag IN Optional Default: N |
1450 | p_set_of_books_id IN Optional Default: AR's set of books |
1451 | p_application_id IN Optional Default: 222 |
1452 | p_check_period_status IN Optional Default: TRUE |
1453 | |
1454 | If p_check_period_status is TRUE, the period status of the date is |
1455 | checked. Otherwise, it is not chacked. |
1456 | |
1457 | RETURNS |
1458 | TRUE if the date is valid, FALSE otherwise. |
1459 | |
1460 | EXCEPTIONS RAISED |
1461 | None |
1462 | |
1463 | KNOWN BUGS |
1464 | |
1465 | NOTES |
1466 | |
1467 | **** IMPORTANT NOTE ****************************************************** |
1468 | A direct copy of this function exists in the package ARP_VIEW_CONSTANTS. |
1469 | Any modifications to this function MUST be made in this package also. |
1470 | ************************************************************************** |
1471 | |
1472 | HISTORY |
1473 | 16-JUN-94 Charlie Tomberg Created. |
1474 | 20-SEP-96 Karen Lawrance Added note about ARP_VIEW_CONSTANTS |
1475 | package. |
1476 | 05-AUG-99 Genneva Wang 955813 Truncate time stamp for input |
1477 | parameter |
1478 | 26-Nov-01 Ramakant Alat Cached the gl_dates in a PL/SQL table |
1479 | to improve performance. |
1480 | |
1481 *----------------------------------------------------------------------------*/
1482
1483 function is_gl_date_valid(
1484 p_gl_date in date,
1485 p_trx_date in date,
1486 p_validation_date1 in date,
1487 p_validation_date2 in date,
1488 p_validation_date3 in date,
1489 p_allow_not_open_flag in varchar2,
1490 p_set_of_books_id in number,
1491 p_application_id in number,
1492 p_check_period_status in boolean default TRUE)
1493 return boolean is
1494
1495 return_value boolean;
1496 num_return_value number;
1497 l_gl_date date;
1498 l_trx_date date;
1499 l_validation_date1 date;
1500 l_validation_date2 date;
1501 l_validation_date3 date;
1502 l_request_id number; /*3264603*/
1503
1504 begin
1505 /* Bug fix: 955813 */
1506 /*------------------------------+
1507 | Initialize input variables |
1508 +------------------------------*/
1509 l_gl_date := trunc(p_gl_date);
1510 l_trx_date := trunc(p_trx_date);
1511 l_validation_date1 := trunc(p_validation_date1);
1512 l_validation_date2 := trunc(p_validation_date2);
1513 l_validation_date3 := trunc(p_validation_date3);
1514 l_request_id := arp_global.request_id; /* bug fix 3264603*/
1515
1516 if (l_gl_date is null)
1517 then return(FALSE);
1518 end if;
1519
1520 if (l_gl_date < nvl(l_validation_date1, l_gl_date) )
1521 then return(FALSE);
1522 end if;
1523
1524 if (l_gl_date < nvl(l_validation_date2, l_gl_date) )
1525 then return(FALSE);
1526 end if;
1527
1528 if (l_gl_date < nvl(l_validation_date3, l_gl_date) )
1529 then return(FALSE);
1530 end if;
1531
1532 if (p_check_period_status = TRUE)
1533 then
1534
1535 -- Bug# 2123155 - Cached the gl_dates in a PL/SQL
1536 -- table to improve performance
1537 IF l_request_id IS NOT NULL THEN /* bug fix 3264603 */
1538
1539 if p_allow_not_open_flag = 'Y' then
1540 if pg_period.EXISTS(to_char(l_gl_date,'j')) then
1541 null;
1542 else
1543 select decode(max(period_name),
1544 '', 0,
1545 1)
1546 into pg_period(to_char(l_gl_date,'j'))
1547 from gl_period_statuses
1548 where application_id = p_application_id
1549 and set_of_books_id = p_set_of_books_id
1550 and adjustment_period_flag = 'N'
1551 and l_gl_date between start_date and end_date
1552 and closing_status in ('O', 'F', 'N') ;
1553
1554 end if;
1555
1556 num_return_value := pg_period(to_char(l_gl_date,'j'));
1557 else
1558 if pg_period_open.EXISTS(to_char(l_gl_date,'j')) then
1559 null;
1560 else
1561 select decode(max(period_name),
1562 '', 0,
1563 1)
1564 into pg_period_open(to_char(l_gl_date,'j'))
1565 from gl_period_statuses
1566 where application_id = p_application_id
1567 and set_of_books_id = p_set_of_books_id
1568 and adjustment_period_flag = 'N'
1569 and l_gl_date between start_date and end_date
1570 and closing_status in ('O', 'F');
1571 end if;
1572
1573 num_return_value := pg_period_open(to_char(l_gl_date,'j'));
1574
1575 end if;
1576 ELSE /* Bug fix 3264603*/
1577
1578 /* Bug 3839973/3828312 - Removed extra OR condition and
1579 set validation sql to execute based on value of
1580 p_allow_not_open_flag */
1581 IF (p_allow_not_open_flag = 'Y')
1582 THEN
1583 select decode(max(period_name),
1584 '', 0,
1585 1)
1586 into num_return_value
1587 from gl_period_statuses
1588 where application_id = p_application_id
1589 and set_of_books_id = p_set_of_books_id
1590 and adjustment_period_flag = 'N'
1591 and l_gl_date between start_date and end_date
1592 and closing_status in ('O', 'F', 'N');
1593 ELSE
1594 select decode(max(period_name),
1595 '', 0,
1596 1)
1597 into num_return_value
1598 from gl_period_statuses
1599 where application_id = p_application_id
1600 and set_of_books_id = p_set_of_books_id
1601 and adjustment_period_flag = 'N'
1602 and l_gl_date between start_date and end_date
1603 and closing_status in ('O', 'F');
1604 END IF;
1605 END IF;
1606
1607 if (num_return_value = 1)
1608 then return_value := TRUE;
1609 else return_value := FALSE;
1610 end if;
1611
1612 else return_value := TRUE;
1613 end if;
1614
1615 return(return_value);
1616
1617 end; /* function is_gl_date_valid() */
1618
1619
1620 /*----------------------------------------------------------------------------*
1621 | PUBLIC FUNCTION |
1622 | Validate_And_Default_GL_Date() |
1623 | |
1624 | DESCRIPTION |
1625 | This function returns a default GL date. If an invalid GL date is |
1626 | specifoed as a parameter, the function "bumps" it to a valid period. |
1627 | The defaulting and bumping algorithm is as follows: |
1628 | |
1629 | If any date violates any of the requirements listed above, the next |
1630 | defaulting method is used. If the date of the period to use is not |
1631 | specified, use the first day of the period if it is greater than |
1632 | nvl(trx_date, sysdate), otherwise use the last day of the period. |
1633 | |
1634 | 1) Use the passed in gl_date. |
1635 | |
1636 | 2) Use passed in default_date1. |
1637 | |
1638 | 3) Use passed in default_date2. |
1639 | |
1640 | 4) Use passed in default_date3. |
1641 | |
1642 | 5) If sysdate is in a Future period, use the last date of the last |
1643 | Open period that is less than sysdate. |
1644 | 5b) Bug 2824692 - if passed date is in closed period (for advanced rule
1645 | transactions, use first day of next period
1646 | |
1647 | 6) Use sysdate. |
1648 | |
1649 | |
1650 | 7) If the trx_date is known, use trx_date. |
1651 | |
1652 | 8) If the trx_date is known, use the first date of the first Open period |
1653 | that is greater than or equal to the trx_date. |
1654 | |
1655 | 9) If the trx_date is not known, use the first date of the first Open |
1656 | period that is greater than or equal to sysdate. |
1657 | |
1658 | 10) If the trx_date is not known, use the last open period. |
1659 | |
1660 | |
1661 | 11) If the trx_date is known, use the first date of the first Future |
1662 | period that is greater than or equal to the trx_date. |
1663 | |
1664 | 12) If the trx_date is not known, use the first date of the first Future |
1665 | period that is greater than or equal to sysdate. |
1666 | |
1667 | 13) If the trx_date is not known, use the last Future period. |
1668 | |
1669 | 14) No default. |
1670 | |
1671 | |
1672 | A GL date is considered valid if all of the following conditions are |
1673 | true. |
1674 | |
1675 | 1) The date is in an Open or Future period, or it is in a Never Opened |
1676 | period and the Allow Not Open Flag is set to Yes. |
1677 | |
1678 | 2) The date is greater than or equal to the trx_date and the three |
1679 | validation dates if they are specified. |
1680 | |
1681 | 3) The period cannot be an Adjustment period. |
1682 | |
1683 | PARAMETERS |
1684 | gl_date IN Optional |
1685 | trx_date IN Optional |
1686 | validation_date1 IN Optional |
1687 | validation_date2 IN Optional |
1688 | validation_date3 IN Optional |
1689 | default_date1 IN Optional |
1690 | default_date2 IN Optional |
1691 | default_date3 IN Optional |
1692 | p_allow_not_open_flag IN Optional Default: N |
1693 | p_invoicing_rule_id IN Optional |
1694 | p_set_of_books_id IN Optional Default: AR's set of books |
1695 | p_application_id IN Optional Default: 222 |
1696 | default_gl_date OUT NOCOPY Mandatory |
1697 | defaulting_rule_used OUT NOCOPY Optional |
1698 | error_message OUT NOCOPY Optional |
1699 | |
1700 | RETURNS |
1701 | FALSE if an Oracle error occurrs, TRUE otherwise. |
1702 | |
1703 | EXCEPTIONS RAISED |
1704 | None |
1705 | |
1706 | KNOWN BUGS |
1707 | |
1708 | NOTES |
1709 | |
1710 | **** IMPORTANT NOTE ****************************************************** |
1711 | A direct copy of this function exists in the package ARP_VIEW_CONSTANTS. |
1712 | Any modifications to this function MUST be made in this package also. |
1713 | ************************************************************************** |
1714 | |
1715 | HISTORY |
1716 | 16-JUN-94 Charlie Tomberg Created. |
1717 | 20-SEP-96 Karen Lawrance Added note about ARP_VIEW_CONSTANTS |
1718 | package. |
1719 | 27-MAR-03 Michael Raymond Bug 2824692 - added piece of code to |
1720 | default gl_date for ADVANCED invoices |
1721 | to the first day of the next open period |
1722 | when the gl_date is in closed period.i |
1723 | |
1724 | 17-Nov-04 Debbie Jancis Forward ported bug 3477990: CM |
1725 | distribution getting created in a closed |
1726 | period. |
1727 *----------------------------------------------------------------------------*/
1728
1729 function validate_and_default_gl_date(
1730 gl_date in date,
1731 trx_date in date,
1732 validation_date1 in date,
1733 validation_date2 in date,
1734 validation_date3 in date,
1735 default_date1 in date,
1736 default_date2 in date,
1737 default_date3 in date,
1738 p_allow_not_open_flag in varchar2,
1739 p_invoicing_rule_id in varchar2,
1740 p_set_of_books_id in number,
1741 p_application_id in number,
1742 default_gl_date out NOCOPY date,
1743 defaulting_rule_used out NOCOPY varchar2,
1744 error_message out NOCOPY varchar2
1745 ) return boolean is
1746
1747
1748 allow_not_open_flag varchar2(2);
1749 h_application_id number;
1750 h_set_of_books_id number;
1751 candidate_gl_date date;
1752 candidate_start_gl_date date;
1753 candidate_end_gl_date date;
1754
1755 l_gl_date date;
1756 l_trx_date date;
1757 l_validation_date1 date;
1758 l_validation_date2 date;
1759 l_validation_date3 date;
1760 l_default_date1 date;
1761 l_default_date2 date;
1762 l_default_date3 date;
1763
1764 begin
1765 /* Bug fix: 870945 */
1766 /*------------------------------+
1767 | Initialize input variables |
1768 +------------------------------*/
1769
1770 l_gl_date := trunc(gl_date);
1771 l_trx_date := trunc(trx_date);
1772 l_validation_date1 := trunc(validation_date1);
1773 l_validation_date2 := trunc(validation_date2);
1774 l_validation_date3 := trunc(validation_date3);
1775 l_default_date1 := trunc(default_date1);
1776 l_default_date2 := trunc(default_date2);
1777 l_default_date3 := trunc(default_date3);
1778
1779 /*------------------------------+
1780 | Initialize output variables |
1781 +------------------------------*/
1782
1783 defaulting_rule_used := '';
1784 error_message := '';
1785 default_gl_date := '';
1786 candidate_gl_date := '';
1787
1788 /*---------------------------+
1789 | Populate default values |
1790 +---------------------------*/
1791
1792
1793 if (p_allow_not_open_flag is null)
1794 then allow_not_open_flag := 'N';
1795 else allow_not_open_flag := p_allow_not_open_flag;
1796 end if;
1797
1798 if (p_invoicing_rule_id = '-3')
1799 then allow_not_open_flag := 'Y';
1800 end if;
1801
1802 if (p_application_id is null)
1803 then h_application_id := 222;
1804 else h_application_id := p_application_id;
1805 end if;
1806
1807 if (p_set_of_books_id is null)
1808 then h_set_of_books_id := sysparm.set_of_books_id;
1809 else h_set_of_books_id := p_set_of_books_id;
1810 end if;
1811
1812
1813 /*--------------------------+
1814 | Apply defaulting rules |
1815 +--------------------------*/
1816
1817
1818 /* Try the gl_date that was passed in */
1819
1820 if is_gl_date_valid(l_gl_date,
1821 l_trx_date,
1822 l_validation_date1,
1823 l_validation_date2,
1824 l_validation_date3,
1825 allow_not_open_flag,
1826 h_set_of_books_id,
1827 h_application_id,
1828 TRUE)
1829 then default_gl_date := l_gl_date;
1830 defaulting_rule_used := 'ORIGINAL GL_DATE';
1831 return(TRUE);
1832 end if;
1833
1834
1835 /* Try the default dates that were passed in */
1836
1837 if is_gl_date_valid(l_default_date1,
1838 l_trx_date,
1839 l_validation_date1,
1840 l_validation_date2,
1841 l_validation_date3,
1842 allow_not_open_flag,
1843 h_set_of_books_id,
1844 h_application_id,
1845 TRUE)
1846 then default_gl_date := l_default_date1;
1847 defaulting_rule_used := 'DEFAULT_DATE1';
1848 return(TRUE);
1849 end if;
1850
1851 if is_gl_date_valid(l_default_date2,
1852 l_trx_date,
1853 l_validation_date1,
1854 l_validation_date2,
1855 l_validation_date3,
1856 allow_not_open_flag,
1857 h_set_of_books_id,
1858 h_application_id,
1859 TRUE)
1860 then default_gl_date := l_default_date2;
1861 defaulting_rule_used := 'DEFAULT_DATE2';
1862 return(TRUE);
1863 end if;
1864
1865 if is_gl_date_valid(l_default_date3,
1866 l_trx_date,
1867 l_validation_date1,
1868 l_validation_date2,
1869 l_validation_date3,
1870 allow_not_open_flag,
1871 h_set_of_books_id,
1872 h_application_id,
1873 TRUE)
1874 then default_gl_date := l_default_date3;
1875 defaulting_rule_used := 'DEFAULT_DATE3';
1876 return(TRUE);
1877 end if;
1878
1879 /* Bug 3477990 - for invoices with rules, , if the passed date is in a closed
1880 period, go for the first day of the next open period. This specifically
1881 activates when the user passes a gl_date on an invoice with rule
1882 and that gl_date falls in a closed period. In theory, it should
1883 adjust to the next (first) open period after that date. */
1884 /* bug3672087 -- we were passing FALSE in check_period_status in is_gl_date_valid
1885 but we want to validate the period -- hence we should be be passing TRUE */
1886
1887 /*bug3744833 -- the loop checking for invoicing_rule is not reqd.
1888 since its needed for all invoices */
1889
1890
1891 SELECT min(start_date)
1892 INTO candidate_gl_date
1893 FROM gl_period_statuses
1894 WHERE application_id = h_application_id
1895 AND set_of_books_id = h_set_of_books_id
1896 AND adjustment_period_flag = 'N'
1897 AND closing_status IN ('O','F','N')
1898 AND start_date >= l_gl_date;
1899
1900 IF ( candidate_gl_date is not null )
1901 THEN
1902 IF is_gl_date_valid(candidate_gl_date,
1903 l_trx_date,
1904 l_validation_date1,
1905 l_validation_date2,
1906 l_validation_date3,
1907 allow_not_open_flag,
1908 h_set_of_books_id,
1909 h_application_id,
1910 TRUE)
1911 THEN
1912 default_gl_date := candidate_gl_date;
1913 defaulting_rule_used := 'FIRST OPEN PERIOD AFTER GL_DATE';
1914
1915 RETURN(TRUE);
1916 END IF;
1917 END IF;
1918 /* End bug 3477990 */
1919
1920 /*-----------------------------------------------------------------+
1921 | If sysdate is in a Future period, |
1922 | Then use the last day of the last Open period before sysdate. |
1923 +-----------------------------------------------------------------*/
1924
1925
1926 select max(d.end_date)
1927 into candidate_gl_date
1928 from gl_period_statuses d,
1929 gl_period_statuses s
1930 where d.application_id = s.application_id
1931 and d.set_of_books_id = s.set_of_books_id
1932 and d.adjustment_period_flag = 'N'
1933 and d.end_date < sysdate
1934 and d.closing_status = 'O'
1935 and s.application_id = h_application_id
1936 and s.set_of_books_id = h_set_of_books_id
1937 and s.adjustment_period_flag = 'N'
1938 and s.closing_status = 'F'
1939 and sysdate between s.start_date and s.end_date;
1940
1941 if ( candidate_gl_date is not null )
1942 then
1943 if is_gl_date_valid(candidate_gl_date,
1944 l_trx_date,
1945 l_validation_date1,
1946 l_validation_date2,
1947 l_validation_date3,
1948 allow_not_open_flag,
1949 h_set_of_books_id,
1950 h_application_id,
1951 FALSE)
1952 then default_gl_date := candidate_gl_date;
1953 defaulting_rule_used :=
1954 'LAST DAY OF OPEN PERIOD BEFORE FUTURE PERIOD';
1955 return(TRUE);
1956 end if;
1957 end if;
1958
1959 /* Try sysdate */
1960 if is_gl_date_valid(sysdate,
1961 l_trx_date,
1962 l_validation_date1,
1963 l_validation_date2,
1964 l_validation_date3,
1965 allow_not_open_flag,
1966 h_set_of_books_id,
1967 h_application_id,
1968 TRUE)
1969 then default_gl_date := trunc(sysdate);
1970 defaulting_rule_used := 'SYSDATE';
1971 return(TRUE);
1972 end if;
1973
1974 /* Try trx_date */
1975 if ( trx_date is not null )
1976 then
1977
1978 /* Try trx_date */
1979 if is_gl_date_valid(l_trx_date,
1980 l_trx_date,
1981 l_validation_date1,
1982 l_validation_date2,
1983 l_validation_date3,
1984 allow_not_open_flag,
1985 h_set_of_books_id,
1986 h_application_id,
1987 TRUE)
1988 then default_gl_date := l_trx_date;
1989 defaulting_rule_used := 'TRX_DATE';
1990 return(TRUE);
1991 end if;
1992
1993 /* Bug 1882597
1994 Try the open period prior to the trx_date*/
1995
1996 select max(end_date)
1997 into candidate_gl_date
1998 from gl_period_statuses
1999 where application_id = h_application_id
2000 and set_of_books_id = h_set_of_books_id
2001 and adjustment_period_flag = 'N'
2002 and closing_status = 'O'
2003 and start_date < l_trx_date;
2004
2005 arp_util.debug('Candidate GL DATE = '||to_char(candidate_gl_date,'dd/mm/yyyy'));
2006 if ( candidate_gl_date is not null )
2007 then
2008 if is_gl_date_valid(candidate_gl_date,
2009 l_trx_date,
2010 l_validation_date1,
2011 l_validation_date2,
2012 l_validation_date3,
2013 allow_not_open_flag,
2014 h_set_of_books_id,
2015 h_application_id,
2016 FALSE)
2017 then default_gl_date := candidate_gl_date;
2018 defaulting_rule_used :=
2019 'LAST DATE OF THE PREVIOUS OPEN PERIOD';
2020 return(TRUE);
2021 else
2022
2023 arp_util.debug('NOT VALID');
2024 end if;
2025 end if;
2026
2027 /* Try first Open period after trx_date */
2028
2029 select min(start_date)
2030 into candidate_gl_date
2031 from gl_period_statuses
2032 where application_id = h_application_id
2033 and set_of_books_id = h_set_of_books_id
2034 and adjustment_period_flag = 'N'
2035 and closing_status = 'O'
2036 and start_date >= l_trx_date;
2037
2038 if ( candidate_gl_date is not null )
2039 then
2040 if is_gl_date_valid(candidate_gl_date,
2041 l_trx_date,
2042 l_validation_date1,
2043 l_validation_date2,
2044 l_validation_date3,
2045 allow_not_open_flag,
2046 h_set_of_books_id,
2047 h_application_id,
2048 FALSE)
2049 then default_gl_date := candidate_gl_date;
2050 defaulting_rule_used :=
2051 'FIRST OPEN PERIOD AFTER TRX_DATE';
2052 return(TRUE);
2053 end if;
2054 end if; /* candidate_gl_date is not null case */
2055
2056
2057 /* Try first Future period after trx_date */
2058
2059 select min(start_date)
2060 into candidate_gl_date
2061 from gl_period_statuses
2062 where application_id = h_application_id
2063 and set_of_books_id = h_set_of_books_id
2064 and adjustment_period_flag = 'N'
2065 and closing_status = 'F'
2066 and start_date >= l_trx_date;
2067
2068 if ( candidate_gl_date is not null )
2069 then
2070 if is_gl_date_valid(candidate_gl_date,
2071 l_trx_date,
2072 l_validation_date1,
2073 l_validation_date2,
2074 l_validation_date3,
2075 allow_not_open_flag,
2076 h_set_of_books_id,
2077 h_application_id,
2078 FALSE)
2079 then default_gl_date := candidate_gl_date;
2080 defaulting_rule_used :=
2081 'FIRST FUTURE PERIOD AFTER TRX_DATE';
2082 return(TRUE);
2083 end if;
2084 end if; /* candidate_gl_date is not null case */
2085
2086 else /* trx_date is not known case */
2087
2088 /* Bug 1882597
2089 try the previous open period */
2090
2091 select max(end_date)
2092 into candidate_gl_date
2093 from gl_period_statuses
2094 where application_id = h_application_id
2095 and set_of_books_id = h_set_of_books_id
2096 and adjustment_period_flag = 'N'
2097 and closing_status = 'O'
2098 and start_date < sysdate;
2099 arp_util.debug('Candidate gl_date = '|| to_char(candidate_gl_date,'dd-mon-yyyy'));
2100 arp_util.debug('l_ gl_date = '|| to_char(l_gl_date,'dd-mon-yyyy'));
2101
2102 if ( candidate_gl_date is not null )
2103 then
2104 if is_gl_date_valid(candidate_gl_date,
2105 l_trx_date,
2106 l_validation_date1,
2107 l_validation_date2,
2108 l_validation_date3,
2109 allow_not_open_flag,
2110 h_set_of_books_id,
2111 h_application_id,
2112 FALSE)
2113 then default_gl_date := candidate_gl_date;
2114 defaulting_rule_used :=
2115 'LAST DATE OF THE PREVIOUS OPEN PERIOD';
2116 return(TRUE);
2117 end if;
2118 end if; /* candidate_gl_date is not null case */
2119
2120
2121 /* try the first open period after sysdate */
2122
2123 /* Bug 1882597
2124 Changed the function from max to min*/
2125
2126 select min(start_date)
2127 into candidate_gl_date
2128 from gl_period_statuses
2129 where application_id = h_application_id
2130 and set_of_books_id = h_set_of_books_id
2131 and adjustment_period_flag = 'N'
2132 and closing_status = 'O'
2133 and start_date >= sysdate;
2134
2135 if ( candidate_gl_date is not null )
2136 then
2137 if is_gl_date_valid(candidate_gl_date,
2138 l_trx_date,
2139 l_validation_date1,
2140 l_validation_date2,
2141 l_validation_date3,
2142 allow_not_open_flag,
2143 h_set_of_books_id,
2144 h_application_id,
2145 FALSE)
2146 then default_gl_date := candidate_gl_date;
2147 defaulting_rule_used :=
2148 'FIRST OPEN PERIOD AFTER SYSDATE';
2149 return(TRUE);
2150 end if;
2151 end if; /* candidate_gl_date is not null case */
2152
2153
2154 /* try the last open period */
2155
2156 select max(start_date), max(end_date)
2157 into candidate_start_gl_date,
2158 candidate_end_gl_date
2159 from gl_period_statuses
2160 where application_id = h_application_id
2161 and set_of_books_id = h_set_of_books_id
2162 and adjustment_period_flag = 'N'
2163 and closing_status = 'O';
2164
2165 if (sysdate > candidate_start_gl_date)
2166 then candidate_gl_date := candidate_end_gl_date;
2167 else candidate_gl_date := candidate_start_gl_date;
2168 end if;
2169
2170 if ( candidate_gl_date is not null )
2171 then
2172 if is_gl_date_valid(candidate_gl_date,
2173 l_trx_date,
2174 l_validation_date1,
2175 l_validation_date2,
2176 l_validation_date3,
2177 allow_not_open_flag,
2178 h_set_of_books_id,
2179 h_application_id,
2180 FALSE)
2181 then default_gl_date := candidate_gl_date;
2182 defaulting_rule_used :=
2183 'LAST OPEN PERIOD';
2184 return(TRUE);
2185 end if;
2186 end if; /* candidate_gl_date is not null case */
2187
2188
2189 /* try the first Future period >= sysdate */
2190
2191 select min(start_date)
2192 into candidate_gl_date
2193 from gl_period_statuses
2194 where application_id = h_application_id
2195 and set_of_books_id = h_set_of_books_id
2196 and adjustment_period_flag = 'N'
2197 and closing_status = 'F'
2198 and start_date >= sysdate;
2199
2200
2201 if ( candidate_gl_date is not null )
2202 then
2203 if is_gl_date_valid(candidate_gl_date,
2204 l_trx_date,
2205 l_validation_date1,
2206 l_validation_date2,
2207 l_validation_date3,
2208 allow_not_open_flag,
2209 h_set_of_books_id,
2210 h_application_id,
2211 FALSE)
2212 then default_gl_date := candidate_gl_date;
2213 defaulting_rule_used :=
2214 'FIRST FUTURE PERIOD AFTER SYSDATE';
2215 return(TRUE);
2216 end if;
2217 end if; /* candidate_gl_date is not null case */
2218
2219
2220 /* try the last Future period */
2221
2222 select max(start_date), max(end_date)
2223 into candidate_start_gl_date,
2224 candidate_end_gl_date
2225 from gl_period_statuses
2226 where application_id = h_application_id
2227 and set_of_books_id = h_set_of_books_id
2228 and adjustment_period_flag = 'N'
2229 and closing_status = 'F';
2230
2231 if (sysdate > candidate_start_gl_date)
2232 then candidate_gl_date := candidate_end_gl_date;
2233 else candidate_gl_date := candidate_start_gl_date;
2234 end if;
2235
2236 if ( candidate_gl_date is not null )
2237 then
2238 if is_gl_date_valid(candidate_gl_date,
2239 l_trx_date,
2240 l_validation_date1,
2241 l_validation_date2,
2242 l_validation_date3,
2243 allow_not_open_flag,
2244 h_set_of_books_id,
2245 h_application_id,
2246 FALSE)
2247 then default_gl_date := candidate_gl_date;
2248 defaulting_rule_used :=
2249 'LAST FUTURE PERIOD';
2250 return(TRUE);
2251 end if;
2252 end if; /* candidate_gl_date is not null case */
2253
2254
2255 end if; /* trx_date is null or not null */
2256
2257
2258 return(TRUE);
2259
2260 EXCEPTION
2261 WHEN OTHERS THEN
2262 error_message := 'arplbstd(): ' || sqlerrm;
2263 return(FALSE);
2264
2265 end; /* function validate_and_default_gl_date() */
2266
2267 /* Bug 1882597 */
2268 /*===========================================================================+
2269 | FUNCTION |
2270 | default_gl_date_conc |
2271 | |
2272 | DESCRIPTION |
2273 | This function returns the gl_date to be defaulted based on the |
2274 | sysdate. This function can be called for setting the default gl_date |
2275 | for concurrent requests |
2276 | Usage : arp_standard.default_gl_date_conc |
2277 | |
2278 | SCOPE - PUBLIC |
2279 | |
2280 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2281 | |
2282 | ARGUMENTS : IN : None |
2283 | RETURNS : Date |
2284 | |
2285 | |
2286 | NOTES |
2287 | |
2288 | MODIFICATION HISTORY |
2289 | 20-Feb-2002 Rahna Kader Created |
2290 | |
2291 +===========================================================================*/
2292
2293 FUNCTION default_gl_date_conc RETURN date IS
2294 l_default_gl_date DATE;
2295 l_defaulting_rule_used VARCHAR2(50);
2296 l_error_message VARCHAR2(100);
2297 BEGIN
2298 IF (arp_util.validate_and_default_gl_date(
2299 sysdate,
2300 NULL,
2301 NULL,
2302 NULL,
2303 NULL,
2304 NULL,
2305 NULL,
2306 NULL,
2307 'N',
2308 NULL,
2309 sysparm.set_of_books_id,
2310 222,
2311 l_default_gl_date,
2312 l_defaulting_rule_used,
2313 l_error_message) = TRUE) THEN
2314 return l_default_gl_date;
2315 ELSE
2316 return sysdate;
2317 END IF;
2318 return sysdate;
2319 END; /*default_gl_date_conc end*/
2320
2321
2322
2323 /*----------------------------------------------------------------------------*
2324 | PUBLIC PROCEDURE |
2325 | gl_activity ( P_PERIOD_FROM IN |
2326 | ,P_PERIOD_TO IN |
2327 | ,P_CODE_COMBINATION_ID IN |
2328 | ,P_SET_OF_BOOKS_ID IN |
2329 | ,PERIOD_NET_DR OUT NOCOPY |
2330 | ,PERIOD_NET_CR OUT) |
2331 | |
2332 | |
2333 | DESCRIPTION |
2334 | Given the parameter listed above and the procedure will return |
2335 | the Period Net Cr and the Period Net Dr |
2336 | or raise exception NO_DATA_FOUND |
2337 | |
2338 | If the GL Server server package: GL_BALANCES is not installed, this |
2339 | function returns NO_DATA_FOUND. |
2340 | |
2341 | PARAMETERS |
2342 | PERIOD_FROM VARCHAR2 |
2343 | PERIOD_TO VARCHAR2 |
2344 | CODE_COMBINATION_ID NUMBER |
2345 | SET_OF_BOOKS_ID NUMBER |
2346 | |
2347 | RETURNS |
2348 | PERIOD_NET_DR |
2349 | PERIOD_NET_CR |
2350 | exception NO_DATA_FOUND |
2351 | |
2352 | |
2353 | HISTORY |
2354 | 3/16/95 Schirin Farzaneh Created |
2355 | |
2356 *----------------------------------------------------------------------------*/
2357
2358 procedure gl_activity ( P_PERIOD_FROM IN VARCHAR2
2359 ,P_PERIOD_TO IN VARCHAR2
2360 ,P_CODE_COMBINATION_ID IN NUMBER
2361 ,P_SET_OF_BOOKS_ID IN NUMBER
2362 ,P_PERIOD_NET_DR OUT NOCOPY NUMBER
2363 ,P_PERIOD_NET_CR OUT NOCOPY NUMBER) IS
2364
2365 c number;
2366 statement varchar2(2000);
2367 rows number;
2368
2369 dr number;
2370 cr number;
2371 begin
2372
2373 statement := 'begin gl_balances_PKG.gl_get_period_range_activity( :P_PERIOD_FROM ' ||
2374 ',:P_PERIOD_TO ' ||
2375 ',:P_CODE_COMBINATION_ID ' ||
2376 ',:P_SET_OF_BOOKS_ID ' ||
2377 ',:P_PERIOD_NET_DR ' ||
2378 ',:P_PERIOD_NET_CR ); end; ';
2379 c := dbms_sql.open_cursor;
2380
2381 /* Parse SQL Statement, returning the exception: NO_DATA_FOUND if the GL */
2382 /* Server procedure: gl_balances.get_activity has not been installed */
2383 /* Any other error, generated by this call will also return NO_DATA_FOUND */
2384
2385 begin
2386 dbms_sql.parse(c, statement, dbms_sql.native);
2387
2388 dbms_sql.bind_variable( c, 'p_period_from', p_period_from );
2389 dbms_sql.bind_variable( c, 'p_period_to', p_period_to );
2390 dbms_sql.bind_variable( c, 'p_code_combination_id', p_code_combination_id );
2391 dbms_sql.bind_variable( c, 'p_set_of_books_id', p_set_of_books_id );
2392 dbms_sql.bind_variable( c, 'p_period_net_dr', dr );
2393 dbms_sql.bind_variable( c, 'p_period_net_cr', cr );
2394 rows := dbms_sql.execute(c);
2395 dbms_sql.variable_value( c, 'p_period_net_dr', dr );
2396 dbms_sql.variable_value( c, 'p_period_net_cr', cr );
2397 dbms_sql.close_cursor(c);
2398 exception
2399 when others then if dbms_sql.is_open(c) then dbms_sql.close_cursor(c); end if;
2400 raise no_data_found;
2401 end;
2402 p_period_net_dr := dr;
2403 p_period_net_cr := cr;
2404
2405 end;
2406
2407
2408 /*===========================================================================+
2409 | FUNCTION |
2410 | find_previous_trx_line_id |
2411 | |
2412 | DESCRIPTION |
2413 | For a given credit memo line and associated line number for tax, this |
2414 | rountine will attempt to find the same tax line for that invoice line |
2415 | so that the accounting engine can build the autoaccounting for the |
2416 | credit memo line. |
2417 | |
2418 | SCOPE - PRIVATE |
2419 | |
2420 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2421 | |
2422 | ARGUMENTS : IN: p_customer_trx_line_id |
2423 | IN: p_tax_line_number |
2424 | IN: p_vat_tax_id |
2425 | OUT: p_tax_customer_trx_id |
2426 | OUT: p_tax_customer_trx_line_id |
2427 | |
2428 | |
2429 | NOTES |
2430 | Before the invoice tax line can be found ALL of the following |
2431 | conditions must be meet. |
2432 | Same Tax Line Number |
2433 | Same Tax Code |
2434 | |
2435 | If either of these conditions fails, the rountines continue the |
2436 | search, and attempts to find the matching record based on: |
2437 | Same Tax Code |
2438 | Only one original tax of that code. |
2439 | |
2440 | MODIFICATION HISTORY |
2441 | 28-Nov-95 Nigel Smith Created |
2442 | |
2443 +===========================================================================*/
2444
2445 PROCEDURE find_previous_trx_line_id( p_customer_trx_line_id IN NUMBER,
2446 p_tax_line_number IN NUMBER,
2447 p_vat_tax_id IN NUMBER,
2448 p_tax_customer_trx_id OUT NOCOPY NUMBER,
2449 p_tax_customer_trx_line_id OUT NOCOPY NUMBER,
2450 p_chk_applied_cm IN BOOLEAN default FALSE ) IS
2451
2452
2453 CURSOR c_find_prev_trx_line_id( p_customer_trx_line_id IN NUMBER,
2454 p_tax_line_number IN NUMBER,
2455 p_vat_tax_id IN NUMBER ) IS
2456
2457 SELECT tax.customer_trx_line_id,
2458 tax.customer_trx_id
2459 FROM ra_customer_trx_lines tax,
2460 ra_customer_trx_lines line,
2461 ar_vat_tax v1,
2462 ar_vat_tax v2
2463 WHERE tax.link_to_cust_trx_line_id = line.previous_customer_trx_line_id
2464 AND line.customer_trx_line_id = p_customer_trx_line_id
2465 AND tax.line_number = p_tax_line_number
2466 AND tax.vat_tax_id = v1.vat_tax_id
2467 AND v1.tax_code = v2.tax_code
2468 AND v2.vat_tax_id = p_vat_tax_id;
2469
2470
2471 CURSOR c_find_prev_trx_line_id_nol( p_customer_trx_line_id IN NUMBER,
2472 p_vat_tax_id IN NUMBER ) IS
2473
2474 SELECT tax.customer_trx_line_id,
2475 tax.customer_trx_id
2476 FROM ra_customer_trx_lines tax,
2477 ra_customer_trx_lines line,
2478 ar_vat_tax v1,
2479 ar_vat_tax v2
2480 WHERE tax.link_to_cust_trx_line_id = line.previous_customer_trx_line_id
2481 AND line.customer_trx_line_id = p_customer_trx_line_id
2482 AND tax.vat_tax_id = v1.vat_tax_id
2483 AND v1.tax_code = v2.tax_code
2484 AND v2.vat_tax_id = p_vat_tax_id
2485 AND not exists ( select 'x' from ra_customer_trx_lines v, ar_vat_tax v3
2486 where v.link_to_cust_trx_line_id = line.previous_customer_trx_line_id
2487 and v.vat_tax_id = v3.vat_tax_id
2488 and v3.tax_code = v1.tax_code
2489 and v.customer_trx_line_id <> tax.customer_trx_line_id );
2490
2491 cursor c_chk_applied_cm( p_customer_trx_line_id in number ) is
2492
2493 /* Applied Credit Memos interface through autoinvoice must have */
2494 /* previous customer_trx_id and trx_line_id for tax records if */
2495 /* autoaccounting is to work. Using 10SC the tax views will */
2496 /* allways return a previous_customer_trx_line_id for applied trx */
2497
2498 SELECT 'x' from ra_customer_trx_lines line, ra_customer_trx hdr, ra_cust_trx_types type
2499 where line.customer_trx_id = hdr.customer_trx_id
2500 and line.customer_trx_line_id = p_customer_trx_line_id
2501 and hdr.previous_customer_trx_id is not null
2502 and hdr.cust_trx_type_id = type.cust_trx_type_id
2503 and type.type = 'CM';
2504
2505
2506 l_tax_customer_trx_line_id NUMBER;
2507 l_tax_customer_trx_id NUMBER;
2508 l_dummy VARCHAR2(16);
2509
2510 BEGIN
2511 IF PG_DEBUG in ('Y', 'C') THEN
2512 arp_util.debug( 'arp_process_tax.find_previous_trx_line_id()+' );
2513 END IF;
2514 OPEN c_find_prev_trx_line_id( p_customer_trx_line_id, p_tax_line_number, p_vat_tax_id );
2515 FETCH c_find_prev_trx_line_id into l_tax_customer_trx_line_id, l_tax_customer_trx_id;
2516
2517 IF c_find_prev_trx_line_id%NOTFOUND
2518 THEN
2519 OPEN c_find_prev_trx_line_id_nol( p_customer_trx_line_id, p_vat_tax_id );
2520 FETCH c_find_prev_trx_line_id_nol into l_tax_customer_trx_line_id, l_tax_customer_trx_id;
2521
2522 IF c_find_prev_trx_line_id_nol%NOTFOUND
2523 THEN
2524
2525 l_tax_customer_trx_line_id := null;
2526 l_tax_customer_trx_id := null;
2527
2528 IF p_chk_applied_cm
2529 THEN
2530 BEGIN
2531
2532 OPEN c_chk_applied_cm( p_customer_trx_line_id );
2533 FETCH c_chk_applied_cm into l_dummy;
2534 if c_chk_applied_cm%FOUND
2535 THEN
2536 BEGIN
2537 close c_chk_applied_cm;
2538 close c_find_prev_trx_line_id;
2539 close c_find_prev_trx_line_id_nol;
2540 raise NO_DATA_FOUND;
2541 END;
2542 END IF;
2543 close c_chk_applied_cm;
2544
2545 END;
2546 END IF;
2547 END IF;
2548 CLOSE c_find_prev_trx_line_id_nol;
2549
2550 END IF;
2551 CLOSE c_find_prev_trx_line_id;
2552
2553 IF PG_DEBUG in ('Y', 'C') THEN
2554 arp_util.debug( 'arp_standard.find_previous_trx_line_id( ' ||
2555 to_char(l_tax_customer_trx_id ) || ', ' || to_char( l_tax_customer_trx_line_id ) || ' )-' );
2556 END IF;
2557
2558 p_tax_customer_trx_line_id := l_tax_customer_trx_line_id ;
2559 p_tax_customer_trx_id := l_tax_customer_trx_id;
2560
2561
2562
2563 EXCEPTION
2564 WHEN OTHERS
2565 THEN
2566 IF PG_DEBUG in ('Y', 'C') THEN
2567 arp_util.debug( 'EXCEPTION: arp_standard.find_previous_trx_line_id( ' ||
2568 to_char( p_customer_trx_line_id ) || ' )' );
2569 END IF;
2570
2571 IF c_find_prev_trx_line_id%ISOPEN
2572 THEN
2573 CLOSE c_find_prev_trx_line_id;
2574 END IF;
2575
2576 IF c_find_prev_trx_line_id_nol%ISOPEN
2577 THEN
2578 CLOSE c_find_prev_trx_line_id_nol;
2579 END IF;
2580
2581 IF c_chk_applied_cm%ISOPEN
2582 THEN
2583 CLOSE c_chk_applied_cm;
2584 END IF;
2585
2586 RAISE;
2587 END find_previous_trx_line_id;
2588
2589 /*===========================================================================+
2590 | PROCEDURE |
2591 | enable_sql_trace |
2592 | |
2593 | DESCRIPTION |
2594 | Enable sql trace based on value (YES/NO) of profile AR_ENABLE_SQL_TRACE|
2595 | |
2596 | SCOPE - PRIVATE |
2597 | |
2598 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2599 | |
2600 | NOTES |
2601 | |
2602 | MODIFICATION HISTORY |
2603 | 10-Sep-99 Govind Jayanth Created |
2604 | |
2605 +===========================================================================*/
2606 PROCEDURE enable_sql_trace IS
2607 BEGIN
2608
2609 --ATG mandate remove sql trace related code from files
2610 --IF (fnd_profile.value('AR_ENABLE_SQL_TRACE') = 'Y') THEN
2611 -- dbms_session.set_sql_trace(true);
2612 --END IF;
2613
2614 NULL;
2615
2616
2617 EXCEPTION
2618 WHEN OTHERS THEN
2619 null;
2620 END enable_sql_trace;
2621
2622 /*===========================================================================+
2623 | PROCEDURE |
2624 | set_enable_debug |
2625 | |
2626 | DESCRIPTION |
2627 | Set program global debug output flag based on value of profile |
2628 | AFLOG_ENABLED |
2629 | |
2630 | SCOPE - PRIVATE |
2631 | |
2632 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2633 | |
2634 | NOTES |
2635 | |
2636 | MODIFICATION HISTORY |
2637 | 10-NOV-99 Govind Jayanth Created |
2638 | |
2639 +===========================================================================*/
2640 PROCEDURE set_enable_debug IS
2641 BEGIN
2642 IF (fnd_profile.value('AFLOG_ENABLED') = 'Y') THEN
2643 arp_standard.pg_prf_enable_debug := 'Y';
2644 END IF;
2645 EXCEPTION
2646 WHEN OTHERS THEN
2647 null;
2648 END set_enable_debug;
2649
2650 /*===========================================================================+
2651 | PROCEDURE |
2652 | init_standard |
2653 | |
2654 | DESCRIPTION |
2655 | Procedure to initialize ARP_STANDARD public variables. |
2656 | |
2657 | SCOPE - Public |
2658 | |
2659 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
2660 | |
2661 | NOTES |
2662 | |
2663 | MODIFICATION HISTORY |
2664 | 11-Sep-2002 Sahana Shetty Created for Bug2538244. |
2665 | |
2666 +===========================================================================*/
2667 --begin anuj
2668 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
2669 PROCEDURE INIT_STANDARD(p_org_id number default null) IS
2670
2671 l_gr ar_mo_cache_utils.GlobalsRecord;
2672 l_count PLS_INTEGER;
2673 l_default_org_id NUMBER;
2674 l_default_ou_name mo_glob_org_access_tmp.organization_name%type; --Bug Fix 6814490
2675
2676 BEGIN
2677
2678 application_id := 222;
2679 gl_application_id := 101;
2680 previous_msg.name := 'AR_PP_NO_MESSAGE';
2681
2682 /* --------------------------------------------------------------------------------
2683 If you pass p_org_id to INIT_STANDARD it will the set the current org to p_org_id
2684 else it will get the default org_id based on mo_utils.get_default_ou output
2685 --------------------------------------------------------------------------------- */
2686
2687 IF p_org_id is NOT NULL then
2688 ar_mo_global_cache.set_current_org_id(p_org_id);
2689 ELSE
2690 mo_utils.get_default_ou(l_default_org_id,l_default_ou_name,l_count);
2691 IF l_default_org_id is null then
2692 begin
2693 select min(org_id) into l_default_org_id from ar_system_parameters;
2694 end;
2695 end if;
2696 END IF;
2697
2698 /* --------------------------------------------------------------------------------
2699 Get the cached attribute info for the org you pass p_org_id to INIT_STANDARD into
2700 Local Variable l_gr
2701 --------------------------------------------------------------------------------- */
2702
2703 l_gr := ar_mo_global_cache.get_org_attributes(nvl(p_org_id,l_default_org_id));
2704
2705 /* --------------------------------------------------------------------------------
2706 Begin populate all attribute of global variable, sysparm of ar_system_parameters%rowtype
2707 from Local Variable, l_gr, retieved from cache for the passed org
2708 --------------------------------------------------------------------------------- */
2709 /* enable_debug;
2710 enable_file_debug('/sqlcom/out/modev116','artestlog'||userenv('SESSIONID')||'.log');
2711 */
2712 arp_standard.debug('SSA CACHE TEST BEGIN');
2713 ARP_STANDARD.sysparm.org_id := l_gr.org_id;
2714 ARP_STANDARD.sysparm.set_of_books_id := l_gr.set_of_books_id;
2715 ARP_STANDARD.sysparm.accounting_method := l_gr.accounting_method;
2716 ARP_STANDARD.sysparm.accrue_interest := l_gr.accrue_interest;
2717 ARP_STANDARD.sysparm.unearned_discount := l_gr.unearned_discount;
2718 ARP_STANDARD.sysparm.partial_discount_flag := l_gr.partial_discount_flag;
2719 ARP_STANDARD.sysparm.print_remit_to := l_gr.print_remit_to;
2720 ARP_STANDARD.sysparm.default_cb_due_date := l_gr.default_cb_due_date;
2721 ARP_STANDARD.sysparm.auto_site_numbering := l_gr.auto_site_numbering;
2722 ARP_STANDARD.sysparm.cash_basis_set_of_books_id := l_gr.cash_basis_set_of_books_id;
2723 ARP_STANDARD.sysparm.code_combination_id_gain := l_gr.code_combination_id_gain;
2724 ARP_STANDARD.sysparm.autocash_hierarchy_id := l_gr.autocash_hierarchy_id;
2725 ARP_STANDARD.sysparm.run_gl_journal_import_flag := l_gr.run_gl_journal_import_flag;
2726 ARP_STANDARD.sysparm.cer_split_amount := l_gr.cer_split_amount;
2727 ARP_STANDARD.sysparm.cer_dso_days := l_gr.cer_dso_days;
2728
2729 ARP_STANDARD.sysparm.posting_days_per_cycle := l_gr.posting_days_per_cycle;
2730 ARP_STANDARD.sysparm.address_validation := l_gr.address_validation;
2731 ARP_STANDARD.sysparm.calc_discount_on_lines_flag:= l_gr.calc_discount_on_lines_flag;
2732 ARP_STANDARD.sysparm.change_printed_invoice_flag:= l_gr.change_printed_invoice_flag;
2733 ARP_STANDARD.sysparm.code_combination_id_loss := l_gr.code_combination_id_loss;
2734 ARP_STANDARD.sysparm.create_reciprocal_flag := l_gr.create_reciprocal_flag;
2735 ARP_STANDARD.sysparm.default_country := l_gr.default_country;
2736 ARP_STANDARD.sysparm.default_territory := l_gr.default_territory;
2737
2738 ARP_STANDARD.sysparm.generate_customer_number := l_gr.generate_customer_number;
2739 ARP_STANDARD.sysparm.invoice_deletion_flag := l_gr.invoice_deletion_flag;
2740 ARP_STANDARD.sysparm.location_structure_id := l_gr.location_structure_id;
2741 ARP_STANDARD.sysparm.site_required_flag := l_gr.site_required_flag;
2742 ARP_STANDARD.sysparm.tax_allow_compound_flag := l_gr.tax_allow_compound_flag;
2743 ARP_STANDARD.sysparm.tax_header_level_flag := l_gr.tax_header_level_flag;
2744 ARP_STANDARD.sysparm.tax_rounding_allow_override:= l_gr.tax_rounding_allow_override;
2745 ARP_STANDARD.sysparm.tax_invoice_print := l_gr.tax_invoice_print;
2746 ARP_STANDARD.sysparm.tax_method := l_gr.tax_method;
2747
2748 ARP_STANDARD.sysparm.tax_use_customer_exempt_flag:= l_gr.tax_use_customer_exempt_flag;
2749 ARP_STANDARD.sysparm.tax_use_cust_exc_rate_flag := l_gr.tax_use_cust_exc_rate_flag;
2750 ARP_STANDARD.sysparm.tax_use_loc_exc_rate_flag := l_gr.tax_use_loc_exc_rate_flag;
2751 ARP_STANDARD.sysparm.tax_use_product_exempt_flag:= l_gr.tax_use_product_exempt_flag;
2752 ARP_STANDARD.sysparm.tax_use_prod_exc_rate_flag := l_gr.tax_use_prod_exc_rate_flag;
2753 ARP_STANDARD.sysparm.tax_use_site_exc_rate_flag := l_gr.tax_use_site_exc_rate_flag;
2754 ARP_STANDARD.sysparm.ai_log_file_message_level := l_gr.ai_log_file_message_level;
2755 ARP_STANDARD.sysparm.ai_max_memory_in_bytes := l_gr.ai_max_memory_in_bytes;
2756
2757 ARP_STANDARD.sysparm.ai_acct_flex_key_left_prompt:= l_gr.ai_acct_flex_key_left_prompt;
2758 ARP_STANDARD.sysparm.ai_mtl_items_key_left_prompt:= l_gr.ai_mtl_items_key_left_prompt;
2759 ARP_STANDARD.sysparm.ai_territory_key_left_prompt:= l_gr.ai_territory_key_left_prompt;
2760 ARP_STANDARD.sysparm.ai_purge_interface_tables_flag:= l_gr.ai_purge_int_tables_flag;
2761 ARP_STANDARD.sysparm.ai_activate_sql_trace_flag := l_gr.ai_activate_sql_trace_flag;
2762 ARP_STANDARD.sysparm.default_grouping_rule_id := l_gr.default_grouping_rule_id;
2763 ARP_STANDARD.sysparm.salesrep_required_flag := l_gr.salesrep_required_flag;
2764
2765 ARP_STANDARD.sysparm.auto_rec_invoices_per_commit:= l_gr.auto_rec_invoices_per_commit;
2766 ARP_STANDARD.sysparm.auto_rec_receipts_per_commit:= l_gr.auto_rec_receipts_per_commit;
2767 ARP_STANDARD.sysparm.pay_unrelated_invoices_flag:= l_gr.pay_unrelated_invoices_flag;
2768 ARP_STANDARD.sysparm.print_home_country_flag := l_gr.print_home_country_flag;
2769 ARP_STANDARD.sysparm.location_tax_account := l_gr.location_tax_account;
2770 ARP_STANDARD.sysparm.from_postal_code := l_gr.from_postal_code;
2771 ARP_STANDARD.sysparm.to_postal_code := l_gr.to_postal_code;
2772
2773 ARP_STANDARD.sysparm.tax_registration_number := l_gr.tax_registration_number;
2774 ARP_STANDARD.sysparm.populate_gl_segments_flag := l_gr.populate_gl_segments_flag;
2775 ARP_STANDARD.sysparm.unallocated_revenue_ccid := l_gr.unallocated_revenue_ccid;
2776
2777 ARP_STANDARD.sysparm.inclusive_tax_used := l_gr.inclusive_tax_used;
2778 ARP_STANDARD.sysparm.tax_enforce_account_flag := l_gr.tax_enforce_account_flag;
2779
2780 ARP_STANDARD.sysparm.ta_installed_flag := l_gr.ta_installed_flag;
2781 ARP_STANDARD.sysparm.bills_receivable_enabled_flag:= l_gr.br_enabled_flag;
2782
2783 ARP_STANDARD.sysparm.attribute_category := l_gr.attribute_category;
2784 ARP_STANDARD.sysparm.attribute1 := l_gr.attribute1;
2785 ARP_STANDARD.sysparm.attribute2 := l_gr.attribute2;
2786 ARP_STANDARD.sysparm.attribute3 := l_gr.attribute3;
2787 ARP_STANDARD.sysparm.attribute4 := l_gr.attribute4;
2788 ARP_STANDARD.sysparm.attribute5 := l_gr.attribute5;
2789 ARP_STANDARD.sysparm.attribute6 := l_gr.attribute6;
2790 ARP_STANDARD.sysparm.attribute7 := l_gr.attribute7;
2791 ARP_STANDARD.sysparm.attribute8 := l_gr.attribute8;
2792 ARP_STANDARD.sysparm.attribute9 := l_gr.attribute9;
2793 ARP_STANDARD.sysparm.attribute10 := l_gr.attribute10;
2794 ARP_STANDARD.sysparm.attribute11 := l_gr.attribute11;
2795 ARP_STANDARD.sysparm.attribute12 := l_gr.attribute12;
2796 ARP_STANDARD.sysparm.attribute13 := l_gr.attribute13;
2797 ARP_STANDARD.sysparm.attribute14 := l_gr.attribute14;
2798 ARP_STANDARD.sysparm.attribute15 := l_gr.attribute15;
2799
2800 ARP_STANDARD.sysparm.created_by := l_gr.created_by;
2801 ARP_STANDARD.sysparm.creation_date := l_gr.creation_date;
2802 ARP_STANDARD.sysparm.last_updated_by := l_gr.last_updated_by;
2803 ARP_STANDARD.sysparm.last_update_date := l_gr.last_update_date;
2804 ARP_STANDARD.sysparm.last_update_login := l_gr.last_update_login;
2805
2806 ARP_STANDARD.sysparm.tax_code := l_gr.tax_code;
2807 ARP_STANDARD.sysparm.tax_currency_code := l_gr.tax_currency_code;
2808 ARP_STANDARD.sysparm.tax_minimum_accountable_unit:= l_gr.tax_minimum_accountable_unit;
2809 ARP_STANDARD.sysparm.tax_precision := l_gr.tax_precision;
2810 ARP_STANDARD.sysparm.tax_rounding_rule := l_gr.tax_rounding_rule;
2811 ARP_STANDARD.sysparm.tax_use_account_exc_rate_flag:= l_gr.tax_use_acc_exc_rate_flag;
2812 ARP_STANDARD.sysparm.tax_use_system_exc_rate_flag:= l_gr.tax_use_system_exc_rate_flag;
2813 ARP_STANDARD.sysparm.tax_hier_site_exc_rate := l_gr.tax_hier_site_exc_rate;
2814 ARP_STANDARD.sysparm.tax_hier_cust_exc_rate := l_gr.tax_hier_cust_exc_rate;
2815 ARP_STANDARD.sysparm.tax_hier_prod_exc_rate := l_gr.tax_hier_prod_exc_rate;
2816 ARP_STANDARD.sysparm.tax_hier_account_exc_rate := l_gr.tax_hier_account_exc_rate;
2817 ARP_STANDARD.sysparm.tax_hier_system_exc_rate := l_gr.tax_hier_system_exc_rate;
2818 ARP_STANDARD.sysparm.tax_database_view_set := l_gr.tax_database_view_set;
2819
2820 ARP_STANDARD.sysparm.global_attribute1 := l_gr.global_attribute1;
2821 ARP_STANDARD.sysparm.global_attribute2 := l_gr.global_attribute2;
2822 ARP_STANDARD.sysparm.global_attribute3 := l_gr.global_attribute3;
2823 ARP_STANDARD.sysparm.global_attribute4 := l_gr.global_attribute4;
2824 ARP_STANDARD.sysparm.global_attribute5 := l_gr.global_attribute5;
2825 ARP_STANDARD.sysparm.global_attribute6 := l_gr.global_attribute6;
2826 ARP_STANDARD.sysparm.global_attribute7 := l_gr.global_attribute7;
2827 ARP_STANDARD.sysparm.global_attribute8 := l_gr.global_attribute8;
2828 ARP_STANDARD.sysparm.global_attribute9 := l_gr.global_attribute9;
2829 ARP_STANDARD.sysparm.global_attribute10 := l_gr.global_attribute10;
2830 ARP_STANDARD.sysparm.global_attribute11 := l_gr.global_attribute11;
2831 ARP_STANDARD.sysparm.global_attribute12 := l_gr.global_attribute12;
2832 ARP_STANDARD.sysparm.global_attribute13 := l_gr.global_attribute13;
2833 ARP_STANDARD.sysparm.global_attribute14 := l_gr.global_attribute14;
2834 ARP_STANDARD.sysparm.global_attribute15 := l_gr.global_attribute15;
2835 ARP_STANDARD.sysparm.global_attribute16 := l_gr.global_attribute16;
2836 ARP_STANDARD.sysparm.global_attribute17 := l_gr.global_attribute17;
2837 ARP_STANDARD.sysparm.global_attribute18 := l_gr.global_attribute18;
2838 ARP_STANDARD.sysparm.global_attribute19 := l_gr.global_attribute19;
2839 ARP_STANDARD.sysparm.global_attribute20 := l_gr.global_attribute20;
2840 ARP_STANDARD.sysparm.global_attribute_category := l_gr.global_attribute_category;
2841
2842 ARP_STANDARD.sysparm.rule_set_id := l_gr.rule_set_id;
2843 ARP_STANDARD.sysparm.code_combination_id_round := l_gr.code_combination_id_round;
2844 ARP_STANDARD.sysparm.trx_header_level_rounding := l_gr.trx_header_level_rounding;
2845 ARP_STANDARD.sysparm.trx_header_round_ccid := l_gr.trx_header_round_ccid;
2846 ARP_STANDARD.sysparm.finchrg_receivables_trx_id := l_gr.finchrg_receivables_trx_id;
2847 ARP_STANDARD.sysparm.sales_tax_geocode := l_gr.sales_tax_geocode;
2848 ARP_STANDARD.sysparm.rev_transfer_clear_ccid := l_gr.rev_transfer_clear_ccid;
2849 ARP_STANDARD.sysparm.sales_credit_pct_limit := l_gr.sales_credit_pct_limit;
2850 ARP_STANDARD.sysparm.max_wrtoff_amount := l_gr.max_wrtoff_amount;
2851 ARP_STANDARD.sysparm.irec_cc_receipt_method_id := l_gr.irec_cc_receipt_method_id;
2852 ARP_STANDARD.sysparm.show_billing_number_flag := l_gr.show_billing_number_flag;
2853 ARP_STANDARD.sysparm.cross_currency_rate_type := l_gr.cross_currency_rate_type;
2854 ARP_STANDARD.sysparm.document_seq_gen_level := l_gr.document_seq_gen_level;
2855 ARP_STANDARD.sysparm.calc_tax_on_credit_memo_flag:= l_gr.calc_tax_on_credit_memo_flag;
2856 ARP_STANDARD.sysparm.IREC_BA_RECEIPT_METHOD_ID := l_gr.IREC_BA_RECEIPT_METHOD_ID;
2857 ARP_STANDARD.sysparm.payment_threshold := l_gr.payment_threshold;
2858 ARP_STANDARD.sysparm.standard_refund := l_gr.standard_refund;
2859 ARP_STANDARD.sysparm.credit_classification1 := l_gr.credit_classification1;
2860 ARP_STANDARD.sysparm.credit_classification2 := l_gr.credit_classification2;
2861 ARP_STANDARD.sysparm.credit_classification3 := l_gr.credit_classification3;
2862 ARP_STANDARD.sysparm.unmtch_claim_creation_flag := l_gr.unmtch_claim_creation_flag;
2863 ARP_STANDARD.sysparm.matched_claim_creation_flag := l_gr.matched_claim_creation_flag;
2864 ARP_STANDARD.sysparm.matched_claim_excl_cm_flag := l_gr.matched_claim_excl_cm_flag;
2865 ARP_STANDARD.sysparm.min_wrtoff_amount := l_gr.min_wrtoff_amount;
2866 ARP_STANDARD.sysparm.min_refund_amount := l_gr.min_refund_amount;
2867
2868
2869 /* --------------------------------------------------------------------------------
2870 End populate all attribute of global variable, sysparm of ar_system_parameters%rowtype
2871 from Local Variable, l_gr, retieved from cache for the passed org
2872 --------------------------------------------------------------------------------- */
2873 if ARP_STANDARD.sysparm.from_postal_code is null
2874 then
2875 ARP_STANDARD.sysparm.from_postal_code := '00000';
2876 end if;
2877
2878 if ARP_STANDARD.sysparm.to_postal_code is null
2879 then
2880 ARP_STANDARD.sysparm.to_postal_code := '99999-9999';
2881 end if;
2882
2883 ARP_STANDARD.gl_chart_of_accounts_id:= l_gr.chart_of_accounts_id;
2884
2885
2886 SET_WHO_INFORMATION(0,0,0,0,0); -- Now uses AOL FND_GLOBAL package
2887 SET_APPLICATION_INFORMATION(0,0); -- Now uses AOL FND_GLOBAL package
2888
2889 /* Enable sql trace based on profile */
2890 --ATG mandate there should not be any sql trace logic
2891 --enable_sql_trace;
2892
2893 /* Set arp_standard package global variable, if profile is set */
2894 set_enable_debug;
2895
2896 EXCEPTION
2897 WHEN NO_DATA_FOUND
2898 THEN
2899 BEGIN
2900 --Bug Fix:1949618; added following two lines
2901 SET_WHO_INFORMATION(0,0,0,0,0); -- Now uses AOL FND_GLOBAL package
2902 SET_APPLICATION_INFORMATION(0,0); -- Now uses AOL FND_GLOBAL package
2903 arp_standard.fnd_message( 'AR_NO_ROW_IN_SYSTEM_PARAMETERS' );
2904 END;
2905 END INIT_STANDARD;
2906
2907 begin --- Initialisation section
2908
2909 /* --------------------------------------------------------------------------------------------
2910 Calling Procedure INIT_STANDARD in initialization section
2911 -------------------------------------------------------------------------------------------- */
2912 ar_mo_global_cache.populate;
2913 /*Bug 4624926 API failing in R12-Starts*/
2914 IF mo_global.get_current_org_id is null then
2915 ARP_STANDARD.INIT_STANDARD;
2916 ELSE
2917 ARP_STANDARD.INIT_STANDARD(mo_global.get_current_org_id);
2918 END IF;
2919 /* Bug 4624926 API failing in R12-Ends*/
2920
2921 /* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
2922 --end anuj
2923
2924 END ARP_STANDARD;
[Home] [Help]