[Home] [Help]
PACKAGE BODY: APPS.WF_CORE
Source
4 --
1 PACKAGE BODY wf_core AS
2 /* $Header: wfcoreb.pls 120.21.12020000.6 2013/04/09 20:18:11 skandepu ship $ */
3
5 -- Token List
6 --
7 TYPE TokenNameTyp IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
8 TYPE TokenValueTyp IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
9 TYPE number_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10
11 token_name_arr TokenNameTyp;
12 token_value_arr TokenValueTyp;
13 token_counter pls_integer := 0;
14
15 -- State globals for random number generator
19 random_ab_rand number_array;
16 random_state number_array;
17 random_length number;
18 random_tap number;
20 random_ab_poly number_array;
21 random_index_next number;
22 random_modulus number;
23 random_seeded boolean;
24
25 gwf_nls_date_format varchar2(64) := null;
26 gwf_nls_date_language varchar2(64) := null;
27 gwf_nls_language varchar2(64) := null;
28 gwf_nls_territory varchar2(64) := null;
29 gwf_nls_calendar varchar2(64) := null;
30 gwf_nls_sort varchar2(64) := null;
31 gwf_nls_currency varchar2(64) := null;
32 gwf_nls_numeric_characters varchar2(64) := null;
33
34 l_database_default_edition DBA_EDITIONS.EDITION_NAME%TYPE := 'NOT_SET';
35
36 -- HashKey
37 -- Generate the Hash Key for a string
38 FUNCTION HashKey (p_HashString in varchar2) return number is
39
40 l_hashKey number;
41
42 BEGIN
43
44 return(dbms_utility.get_hash_value(p_HashString, HashBase,
45 HashSize));
46
47 END;
48
49 --
50 -- Clear
51 -- Clear the error buffers.
52 -- EXCEPTIONS
53 -- none
54 --
55 procedure Clear is
56 begin
57 wf_core.error_name := '';
58 wf_core.error_number := '';
59 wf_core.error_message := '';
60 wf_core.error_stack := '';
61 token_counter := 0;
62 end Clear;
63
64 --
65 -- Get_Error
66 -- Return current error info and clear error stack.
67 -- Returns null if no current error.
68 --
69 -- IN
70 -- maxErrStackLength - Maximum length of error_stack to return - number
71 --
72 -- OUT
73 -- error_name - error name - varchar2(30)
74 -- error_message - substituted error message - varchar2(2000)
75 -- error_stack - error call stack, truncated if needed - varchar2(2000)
76 -- EXCEPTIONS
77 -- none
78 --
79 procedure Get_Error(err_name out nocopy varchar2,
80 err_message out nocopy varchar2,
81 err_stack out nocopy varchar2,
82 maxErrStackLength in number )
83 is
84 begin
85 err_name := wf_core.error_name;
86 err_message := wf_core.error_message;
87 err_stack := substrb(wf_core.error_stack, 1, maxErrStackLength);
88 wf_core.clear;
89 end Get_Error;
90
91 --
92 -- Token
93 -- define error token
94 -- IN
95 -- token_name - name of token
96 -- token_value - token value
97 -- EXCEPTIONS
98 -- none
99 --
100 procedure Token(token_name in varchar2,
101 token_value in varchar2) is
102 begin
103 token_name_arr(token_counter) := token_name;
104 token_value_arr(token_counter) := token_value;
105 token_counter := token_counter + 1;
106 token_name_arr(token_counter) := '';
107 token_value_arr(token_counter) := '';
108 end Token;
109
110 --
111 -- Substitute
112 -- Return substituted message string, with exception if not found.
113 -- IN
114 -- mtype - message type (WFERR, WFTKN, etc)
115 -- mname - message internal name
116 -- EXCEPTIONS
117 -- Raises an exception if message is not found.
118 --
119 function Substitute(mtype in varchar2, mname in varchar2)
120 return varchar2
121 is
122 mesg_text varchar2(2000); -- the message text
123 tk varchar2(30); -- token name
124 i pls_integer; -- the counter for the token table
125
126 begin
127 -- Get error message and number
128 begin
129 SELECT TEXT INTO mesg_text
130 FROM WF_RESOURCES
131 WHERE TYPE = mtype
132 and NAME = mname
133 and LANGUAGE = userenv('LANG');
134 exception
135 when NO_DATA_FOUND then
136 wf_core.token('NAME', mname);
137 wf_core.token('TYPE', mtype);
138 wf_core.raise('WFCORE_NO_MESSAGE');
139 end;
140
141 -- Substitute tokens in message
142 i := 0;
143 while (i < token_counter) loop
144
145 if (instr(mesg_text, '&'||token_name_arr(i), 1, 1) <> 0) then
146 mesg_text := substrb(replace(mesg_text, '&'||token_name_arr(i),
147 token_value_arr(i)), 1, 2000);
148 end if;
149
150 i := i + 1;
151 end loop;
152
153 -- Clear the token table
154 token_counter := 0;
155
156 return mesg_text;
157 exception
158 when OTHERS then
159 raise;
160 end Substitute;
161
162 --
163 -- Get_Message (PRIVATE)
164 -- Get a susbstituted message string.
165 -- IN
166 -- msgtype - message type (WFERROR, WFTKN, etc)
167 -- msgname - message name
168 -- RETURNS
169 -- Substituted message string
170 -- EXCEPTIONS
171 -- Never raises an exception. Return unsusbstituted name if any
172 -- errors.
173 --
174 function Get_Message(
175 msgtype in varchar2,
176 msgname in varchar2)
177 return varchar2
178 is
179 buf varchar2(2000);
180 i pls_integer;
181 begin
182 /* mjc
183 ** WF_VERSION, WF_SYSTEM_GUID, WF_SYSTEM STATUS should
184 ** not vary by language, and should not have been stored
185 ** in wf_resources. If the NLS_LANG is not set to US,
186 ** then these values cannot be retrieved and the Event
187 ** System will error. To makes sure that the Event System
191 ** (I bet you have heard that one before, right?)
188 ** does not fail, we are including a check here so that
189 ** we always get these values from the US language.
190 ** One day we will move these values somewhere else....
192 */
193
194 -- Get error message and number
195 begin
196 /**
197 * bug 12409884: The message 'WF_STATUS_MONITOR_APPLET_SIZE'
198 * does not require translation since it holds the height and
199 * width information of the workflow status monitor
200 **/
201 if msgname in ('WF_VERSION','WF_SYSTEM_GUID',
202 'WF_SYSTEM_STATUS','WF_SCHEMA','SVC_ENABLED_FLAG',
203 'WFBES_MAX_CACHE_SIZE', 'WF_STATUS_MONITOR_APPLET_SIZE') then
204
205 select TEXT
206 into buf
207 from WF_RESOURCES
208 where TYPE = Get_Message.msgtype
209 and NAME = Get_Message.msgname
210 and LANGUAGE = 'US';
211
212 else
213
214 select TEXT
215 into buf
216 from WF_RESOURCES
217 where TYPE = Get_Message.msgtype
218 and NAME = Get_Message.msgname
219 and LANGUAGE = userenv('LANG');
220
221 end if;
222
223 exception
224 when NO_DATA_FOUND then
225 buf := '[' || msgname || ']';
226 end;
227
228 -- Substitute tokens in error message
229 i := 0;
230 while (i < token_counter) loop
231
232 if (instr(buf, '&'||token_name_arr(i), 1, 1) = 0) then
233 -- Token does not appear in message, tack it on to end
234 buf := substrb(buf||' '||token_name_arr(i)||'='||token_value_arr(i),
235 1, 2000);
236 else
237 buf := substrb(replace(buf, '&'||token_name_arr(i), token_value_arr(i)),
238 1, 2000);
239 end if;
240 i := i + 1;
241 end loop;
242
243 -- Clear the token table
244 token_counter := 0;
245
246 return(buf);
247 exception
248 when others then
249 return(msgname);
250 end Get_Message;
251
252 --
253 -- Translate
254 -- Translate a string value
255 -- IN
256 -- tkn_name - String token name
257 -- RETURNS
258 -- Translated value of string token
259 --
260 function Translate (tkn_name in varchar2)
261 return varchar2
262 is
263 l_translated_string VARCHAR2(4000);
264 begin
265
266 l_translated_string := wf_core.get_message('WFTKN', tkn_name);
267
268 return (l_translated_string);
269
270 exception
271 when others then
272 -- Return untranslated token name if any error.
273 return(tkn_name);
274 end Translate;
275
276 --
277 -- Raise
278 -- Raise an exception to the caller
279 -- IN
280 -- error_name - error name (internal name)
281 -- EXCEPTIONS
282 -- Raises an a user-defined (20002) exception with the error message.
283 --
284 procedure Raise(name in varchar2)
285 is
286 begin
287 -- Set error name
288 wf_core.error_name := name;
289
290 -- Get substituted message
291 wf_core.error_message := Wf_Core.Get_Message('WFERR', name);
292
293 -- Select error number
294 begin
295 SELECT ID
296 INTO wf_core.error_number
297 FROM WF_RESOURCES
298 WHERE TYPE = 'WFERR'
299 and NAME = Raise.name
300 and LANGUAGE = userenv('LANG');
301 exception
302 when NO_DATA_FOUND then
303 wf_core.error_number := '';
304 end;
305
306 -- Prepend error number to message if available
307 if (wf_core.error_number is not null) then
308 wf_core.error_message := substrb(to_char(wf_core.error_number)||
309 ': '||wf_core.error_message, 1, 2000);
310 end if;
311
312 -- Raise the error
313 raise_application_error(-20002, wf_core.error_message);
314 exception
315 when others then
316 raise;
317 end Raise;
318
319 --
320 -- Context
321 -- set procedure context (for stack trace)
322 -- IN
323 -- pkg_name - package name
327 -- EXCEPTIONS
324 -- proc_name - procedure/function name
325 -- arg1 - first IN argument
326 -- argn - n'th IN argument
328 -- none
329 --
330 procedure Context(pkg_name in varchar2,
331 proc_name in varchar2,
332 arg1 in varchar2 ,
333 arg2 in varchar2 ,
334 arg3 in varchar2 ,
335 arg4 in varchar2 ,
336 arg5 in varchar2 ,
337 arg6 in varchar2 ,
338 arg7 in varchar2 ,
339 arg8 in varchar2 ,
340 arg9 in varchar2 ,
341 arg10 in varchar2 ) is
342
343 buf varchar2(32000);
344 begin
345 -- Start with package and proc name.
346 buf := wf_core.newline||pkg_name||'.'||proc_name||'(';
347
348 -- Add all defined args.
349 if (arg1 <> '*none*') then
350 buf := substrb(buf||arg1, 1, 32000);
351 end if;
352 if (arg2 <> '*none*') then
353 buf := substrb(buf||', '||arg2, 1, 32000);
354 end if;
355 if (arg3 <> '*none*') then
356 buf := substrb(buf||', '||arg3, 1, 32000);
357 end if;
358 if (arg4 <> '*none*') then
359 buf := substrb(buf||', '||arg4, 1, 32000);
360 end if;
361 if (arg5 <> '*none*') then
362 buf := substrb(buf||', '||arg5, 1, 32000);
363 end if;
364 if (arg6 <> '*none*') then
365 buf := substrb(buf||',' ||arg6, 1, 32000);
366 end if;
367 if (arg7 <> '*none*') then
368 buf := substrb(buf||', '||arg7, 1, 32000);
369 end if;
370 if (arg8 <> '*none*') then
371 buf := substrb(buf||', '||arg8, 1, 32000);
372 end if;
373 if (arg9 <> '*none*') then
374 buf := substrb(buf||', '||arg9, 1, 32000);
375 end if;
376 if (arg10 <> '*none*') then
377 buf := substrb(buf||', '||arg10, 1, 32000);
378 end if;
379
380 buf := substrb(buf||')', 1, 32000);
381
382 -- Concatenate to the error_stack buffer
383 wf_core.error_stack := substrb(wf_core.error_stack||buf, 1, 32000);
384
385 end Context;
386
387 -- *** RANDOM ***
388 -- Implements a pseudo-random number generator using the additive linear
389 -- feedback algorithm. Numbers are generateed according to the rule:
390 -- X[i] = X[i - a] + X[i - b]
391 -- where a and b are constant "taps".
392
393 --
394 -- Random_init_arrays (PRIVATE)
395 -- Initialize random number generator
396 --
397 procedure random_init_arrays is
398 begin
399 random_ab_rand(1) := 3614090360;
400 random_ab_rand(2) := 3905402710;
401 random_ab_rand(3) := 606105819;
402 random_ab_rand(4) := 3250441966;
403 random_ab_rand(5) := 4118548399;
404 random_ab_rand(6) := 1200080426;
405 random_ab_rand(7) := 2821735955;
406 random_ab_rand(8) := 4249261313;
407 random_ab_rand(9) := 1770035416;
408 random_ab_rand(10) := 2336552879;
409 random_ab_rand(11) := 4294925233;
410 random_ab_rand(12) := 2304563134;
411 random_ab_rand(13) := 1804603682;
412 random_ab_rand(14) := 4254626195;
413 random_ab_rand(15) := 2792965006;
414 random_ab_rand(16) := 1236535329;
415 random_ab_rand(17) := 4129170786;
416 random_ab_rand(18) := 3225465664;
417 random_ab_rand(19) := 643717713;
418 random_ab_rand(20) := 3921069994;
419 random_ab_rand(21) := 3593408605;
420 random_ab_rand(22) := 38016083;
421 random_ab_rand(23) := 3634488961;
422 random_ab_rand(24) := 3889429448;
423 random_ab_rand(25) := 568446438;
424 random_ab_rand(26) := 3275163606;
425 random_ab_rand(27) := 4107603335;
426 random_ab_rand(28) := 1163531501;
427 random_ab_rand(29) := 2850285829;
428 random_ab_rand(30) := 4243563512;
429 random_ab_rand(31) := 1735328473;
430 random_ab_rand(32) := 2368359562;
431 random_ab_rand(33) := 4294588738;
432 random_ab_rand(34) := 2272392833;
433 random_ab_rand(35) := 1839030562;
434 random_ab_rand(36) := 4259657740;
435 random_ab_rand(37) := 2763975236;
436 random_ab_rand(38) := 1272893353;
437 random_ab_rand(39) := 4139469664;
438 random_ab_rand(40) := 3200236656;
439 random_ab_rand(41) := 681279174;
440 random_ab_rand(42) := 3936430074;
441 random_ab_rand(43) := 3572445317;
442 random_ab_rand(44) := 76029189;
443 random_ab_rand(45) := 3654602809;
444 random_ab_rand(46) := 3873151461;
445 random_ab_rand(47) := 530742520;
446 random_ab_rand(48) := 3299628645;
447 random_ab_rand(49) := 4096336452;
448 random_ab_rand(50) := 1126891415;
449 random_ab_rand(51) := 2878612391;
450 random_ab_rand(52) := 4237533241;
451 random_ab_rand(53) := 1700485571;
452 random_ab_rand(54) := 2399980690;
453 random_ab_rand(55) := 4293915773;
454 random_ab_rand(56) := 2240044497;
455 random_ab_rand(57) := 1873313359;
456 random_ab_rand(58) := 4264355552;
457 random_ab_rand(59) := 2734768916;
458 random_ab_rand(60) := 1309151649;
459 random_ab_rand(61) := 4149444226;
460 random_ab_rand(62) := 3174756917;
461 random_ab_rand(63) := 718787259;
462 random_ab_rand(64) := 3951481745;
463
464 random_ab_poly(1) := 0;
465 random_ab_poly(2) := 0;
466 random_ab_poly(3) := 1;
467 random_ab_poly(4) := 1;
471 random_ab_poly(8) := 1;
468 random_ab_poly(5) := 1;
469 random_ab_poly(6) := 2;
470 random_ab_poly(7) := 1;
472 random_ab_poly(9) := 0;
473 random_ab_poly(10) := 4;
474 random_ab_poly(11) := 3;
475 random_ab_poly(12) := 2;
476 random_ab_poly(13) := 0;
477 random_ab_poly(14) := 0;
478 random_ab_poly(15) := 0;
479 random_ab_poly(16) := 1;
480 random_ab_poly(17) := 0;
481 random_ab_poly(18) := 3;
482 random_ab_poly(19) := 7;
483 random_ab_poly(20) := 0;
484 random_ab_poly(21) := 3;
485 random_ab_poly(22) := 2;
486 random_ab_poly(23) := 1;
487 random_ab_poly(24) := 5;
488 random_ab_poly(25) := 0;
489 random_ab_poly(26) := 3;
490 random_ab_poly(27) := 0;
491 random_ab_poly(28) := 0;
492 random_ab_poly(29) := 3;
493 random_ab_poly(30) := 2;
494 random_ab_poly(31) := 0;
495 random_ab_poly(32) := 3;
496 random_ab_poly(33) := 0;
497 random_ab_poly(34) := 13;
498 random_ab_poly(35) := 0;
499 random_ab_poly(36) := 2;
500 random_ab_poly(37) := 11;
501 random_ab_poly(38) := 0;
502 random_ab_poly(39) := 0;
503 random_ab_poly(40) := 4;
504 random_ab_poly(41) := 0;
505 random_ab_poly(42) := 3;
506 random_ab_poly(43) := 0;
507 random_ab_poly(44) := 0;
508 random_ab_poly(45) := 0;
509 random_ab_poly(46) := 0;
510 random_ab_poly(47) := 0;
511 random_ab_poly(48) := 5;
512 random_ab_poly(49) := 0;
513 random_ab_poly(50) := 9;
514 random_ab_poly(51) := 0;
515 random_ab_poly(52) := 0;
516 random_ab_poly(53) := 3;
517 random_ab_poly(54) := 0;
518 random_ab_poly(55) := 0;
519 random_ab_poly(56) := 24;
520 random_ab_poly(57) := 0;
521 random_ab_poly(58) := 7;
522 random_ab_poly(59) := 19;
523 random_ab_poly(60) := 0;
524 random_ab_poly(61) := 1;
525 random_ab_poly(62) := 0;
526 random_ab_poly(63) := 0;
527 random_ab_poly(64) := 1;
528 end random_init_arrays;
529
530 --
531 -- Random_Init (PRIVATE)
532 -- Initialize, but don't seed, the generator. Length refers to the
533 -- amount of state in the generator; longer generators are (somewhat)
534 -- more difficult to predict.
535 --
536 procedure random_init(p_length in number)
537 is
538 begin
539 random_init_arrays;
540
541 random_length := p_length;
542 random_tap := random_ab_poly(p_length);
543 random_modulus := power(2, 32) - 1;
544
545 random_init_arrays;
546
547 random_index_next := 1;
548 end random_init;
549
550 --
551 -- Random_Seed (PRIVATE)
552 -- Seed the generator with value. Run it through the specified number of
553 -- cycles, to ensure the seed affects all values produced (10 cycles should
554 -- be sufficient). If generator has already been seeded, don't do it again.
555 --
556 procedure random_seed(value in number,
557 cycles in number)
558 is
559 dummy number;
560 modval number;
561 begin
562 for n in 1..random_length loop
563 random_state(n) := random_ab_rand(n);
564 end loop;
565
566 modval := mod(value, random_modulus);
567 random_state(1) := mod(random_state(1) + modval, random_modulus);
568
569 for n in 1..(random_length * cycles) loop
570 dummy := to_number(random);
571 end loop;
572 end random_seed;
573
574 --
575 -- RANDOM (PUBLIC)
576 -- Get the next pseudorandom string
577 -- RETURNS
578 -- Random number as a string (max length 80)
579 --
580 function random return varchar2 is
581 oldestval number;
582 tapval number;
583 nextval number;
584 l_random varchar2(80);
585 begin
586
587 if (random_seeded is null) then
588 l_random := wfa_sec.random;
589 if (l_random is not null) then
590 random_seeded := false;
591 return(l_random);
592 end if;
593 elsif (not random_seeded) then
594 return(wfa_sec.random);
595 end if;
596
597 -- no preferred implementation is picked
598 -- use the default one
599
600 if NVL(random_seeded, FALSE) <> TRUE then
601 random_init(7);
602 random_seeded := true;
603 random_seed(to_number(to_char(sysdate, 'JSSSSS')), 10);
604 end if;
605
606 oldestval := random_state(random_index_next);
607 tapval := random_state(
608 mod(random_index_next+random_length-random_tap-1,
609 random_length) + 1);
610 nextval := mod(oldestval + tapval, random_modulus);
611
612 random_state(random_index_next) := nextval;
613 random_index_next := random_index_next + 1;
614 if random_index_next > random_length then
615 random_index_next := 1;
616 end if;
617 return substr(to_char(nextval), 1, 80);
618
619 exception
620 when others then
621 return('');
622 end random;
623
624 --
625 -- ACTIVITY_RESULT
626 -- Return the meaning of an activities result_type
627 -- Including standard engine codes
628 -- IN
629 -- LOOKUP_TYPE
630 -- LOOKUP_CODE
631 --
632 -- RETURNS
633 -- MEANING
634 --
635 function activity_result( result_type in varchar2, result_code in varchar2) return varchar2
636 is
640 select meaning
637 l_meaning varchar2(80);
638 begin
639 begin
641 into l_meaning
642 from wf_lookups
643 where lookup_type = result_type
644 and lookup_code = result_code;
645 exception
646 when NO_DATA_FOUND then
647 --
648 -- If result_code is not in assigned type
649 -- check standard engine result codes
650 --
651 select meaning
652 into l_meaning
653 from wf_lookups
654 where lookup_type = 'WFENG_RESULT'
655 and lookup_code = result_code;
656 end;
657 --
658 return(l_meaning);
659 --
660 exception
661 --
662 -- return lookup_code if any error
663 --
664 when others then
665 return(result_code);
666 end;
667 --
668
669 --
670 -- GetResource
671 -- ** OBSOLETE **
672 -- Please use wf_monitor.GetResource instead.
673 -- Called by WFResourceManager.class. Used by the Monitor and Lov Applet.
674 -- fetch A resource from wf_resource table.
675 -- IN
676 -- x_restype
677 -- x_resname
678
679 procedure GetResource(x_restype varchar2,
680 x_resname varchar2) is
681 begin
682 null;
683 end GetResource;
684
685 --
686 -- GetResources
687 -- ** OBSOLETE **
688 -- Please use wf_monitor.GetResources instead.
689 -- Called by WFResourceManager.class. Used by the Monitor and Lov Applet.
690 -- fetch some resources from wf_resource table that match the respattern.
691 -- IN
692 -- x_restype
693 -- x_respattern
694
695 procedure GetResources(x_restype varchar2,
696 x_respattern varchar2) is
697 begin
698 null;
699 end GetResources;
700
701 -- *** Substitue HTML Characters ****
702 -- SubstituteSpecialChars
703 -- Substitutes the occurence of special characters like <, >, \, ', " etc
704 -- with their html codes in any arbitrary string.
705 -- IN
706 -- some_text - text to be substituted
707 -- RETURN
708 -- substituted text
709
710 function SubstituteSpecialChars(some_text in varchar2)
711 return varchar2 is
712 l_amp varchar2(1);
713 buf varchar2(32000);
714 l_amp_flag boolean;
715 l_lt_flag boolean;
716 l_gt_flag boolean;
717 l_bsl_flag boolean;
718 l_apos_flag boolean;
719 l_quot_flag boolean;
720 begin
721 l_amp := '&';
722
723 buf := some_text;
724
725 -- bug 6025162 - This function should substitute only those chars that
726 -- really require substitution. Any valid occurences should be retained.
727 -- No validation should be required for calling this function
728
729 if (instr(buf, l_amp) > 0) then
730 l_amp_flag := false;
731 l_lt_flag := false;
732 l_gt_flag := false;
733 l_bsl_flag := false;
734 l_apos_flag := false;
735 l_quot_flag := false;
736
737 -- mask all valid ampersand containing patterns in the content
738 -- issue is when ntf body already contains of these reserved words...
739 if (instr(buf, l_amp||'amp;') > 0) then
740 buf := replace(buf, l_amp||'amp;', '#AMP#');
741 l_amp_flag := true;
742 end if;
743 if (instr(buf, l_amp||'lt;') > 0) then
744 buf := replace(buf, l_amp||'lt;', '#LT#');
745 l_lt_flag := true;
746 end if;
747 if (instr(buf, l_amp||'gt;') > 0) then
748 buf := replace(buf, l_amp||'gt;', '#GT#');
749 l_gt_flag := true;
750 end if;
751 if (instr(buf, l_amp||'#92;') > 0) then
752 buf := replace(buf, l_amp||'#92;', '#BSL#');
753 l_bsl_flag := true;
754 end if;
755 if (instr(buf, l_amp||'#39;') > 0) then
756 buf := replace(buf, l_amp||'#39;', '#APO#');
757 l_apos_flag := true;
758 end if;
759 if (instr(buf, l_amp||'quot;') > 0) then
760 buf := replace(buf, l_amp||'quot;', '#QUOT#');
761 l_quot_flag := true;
762 end if;
763
764 buf := replace(buf, l_amp, l_amp||'amp;');
765
766 -- put the masked valid ampersand containing patterns back
767 if (l_amp_flag) then
768 buf := replace(buf, '#AMP#', l_amp||'amp;');
769 end if;
770 if (l_lt_flag) then
771 buf := replace(buf, '#LT#', l_amp||'lt;');
772 end if;
773 if (l_gt_flag) then
774 buf := replace(buf, '#GT#', l_amp||'gt;');
775 end if;
776 if (l_bsl_flag) then
777 buf := replace(buf, '#BSL#', l_amp||'#92;');
778 end if;
779 if (l_apos_flag) then
780 buf := replace(buf, '#APO#', l_amp||'#39;');
781 end if;
782 if (l_quot_flag) then
783 buf := replace(buf, '#QUOT#', l_amp||'quot;');
784 end if;
785 end if;
786
787 buf := replace(buf, '<', l_amp||'lt;');
788 buf := replace(buf, '>', l_amp||'gt;');
789 buf := replace(buf, '\', l_amp||'#92;');
790 buf := replace(buf, '''', l_amp||'#39;');
791 buf := replace(buf, '"', l_amp||'quot;');
792 return buf;
796
793 exception
794 when others then
795 raise;
797 end SubstituteSpecialChars;
798
799 -- *** Special Char functions ***
800
801 -- Local_Chr
802 -- Return specified character in current codeset
803 -- IN
804 -- ascii_chr - chr number in US7ASCII
805 function Local_Chr(
806 ascii_chr in number)
807 return varchar2
808 is
809 begin
810
811 return(wfa_sec.local_chr(ascii_chr));
812
813 end Local_Chr;
814
815 -- Newline
816 -- Return newline character in current codeset
817 function Newline
818 return varchar2
819 is
820 begin
821 return(Wf_Core.Local_Chr(10));
822 end Newline;
823
824 -- Tab
825 -- Return tab character in current codeset
826 function Tab
827 return varchar2
828 is
829 begin
830 return(Wf_Core.Local_Chr(9));
831 end Tab;
832
833 -- CR - CarriageReturn
834 -- Return CR character in current codeset.
835 function CR
836 return varchar2
837 is
838 begin
839 return(WF_CORE.Local_Chr(13));
840 end CR;
841
842 --
843 -- CheckIllegalChars (PRIVATE)
844 --
845 function CheckIllegalChars(p_text varchar2, p_raise_exception boolean,p_illegal_charset varchar2)
846 return boolean
847 is
848 l_charset varchar2(20);
849 l_illegal_char varchar2(4);
850 begin
851 if p_illegal_charset is null then
852 l_charset := ';<>()"';
853 else
854 l_charset := p_illegal_charset;
855 end if;
856
857 for i in 1..length(l_charset)
858 loop
859 l_illegal_char := substr(l_charset,i,1);
860 if (instr(p_text,l_illegal_char)>0) then
861 if (p_raise_exception) then
862 wf_core.token('TEXT', p_text);
863 wf_core.raise('WF_ILLEGAL_CHARS');
864 -- ### Illegal characters found in 'TEXT'
865 end if;
866 return true;
867 end if;
868 end loop;
869 return false;
870 end CheckIllegalChars;
871
872 procedure InitCache
873 is
874 begin
875
876 SELECT to_number(substr(version,1, instr(version,'.',1,1) -1))
877 INTO g_oracle_major_version
878 FROM v$instance;
879
880 if (g_oracle_major_version < 10) then
881 if (g_aq_tm_processes is null) then
882 SELECT value
883 INTO g_aq_tm_processes
884 FROM v$parameter
885 WHERE name = 'aq_tm_processes';
886 end if;
887 end if;
888
889 end InitCache;
890
891 --============================================
892
893 FUNCTION nls_date_format RETURN varchar2
894 is
895 begin
896
897 if(gwf_nls_date_format is null) then
898 gwf_nls_date_format := SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT');
899 end if;
900 RETURN gwf_nls_date_format ;
901
902 END nls_date_format;
903
904 --
905 --
906 --
907 FUNCTION nls_date_language RETURN varchar2
908 is
909 begin
910 if (gwf_nls_date_language is null) then
911 gwf_nls_date_language := SYS_CONTEXT('USERENV', 'NLS_DATE_LANGUAGE');
912 end if;
913
914 RETURN gwf_nls_date_language;
915 END nls_date_language;
916
917 --
918 --
919 --
920 --
921 FUNCTION nls_calendar RETURN varchar2
922 is
923 begin
924 if(gwf_nls_calendar is null) then
925 gwf_nls_calendar := SYS_CONTEXT('USERENV', 'NLS_CALENDAR');
926 end if;
927 RETURN gwf_nls_calendar;
928 END nls_calendar;
929
930 --
931 --
932 --
933 --
934 FUNCTION nls_sort RETURN varchar2
935 is
936 begin
937 if(gwf_nls_sort is null) then
938 gwf_nls_sort := SYS_CONTEXT('USERENV', 'NLS_SORT');
939 end if;
940 RETURN gwf_nls_sort;
941 END nls_sort;
942
943
944 --
945 --
946 --
947 FUNCTION nls_currency RETURN varchar2
948 is
949 begin
950
951 if( gwf_nls_currency is null) then
952 gwf_nls_currency := SYS_CONTEXT('USERENV', 'NLS_CURRENCY');
953 end if;
954 RETURN gwf_nls_currency;
955
956 END nls_currency;
957
958 --
959 --
960 --
961 --
962 FUNCTION nls_numeric_characters RETURN varchar2
963 is
964 begin
965 if (gwf_nls_numeric_characters is null) then
966 select value into gwf_nls_numeric_characters
967 from v$nls_parameters where parameter ='NLS_NUMERIC_CHARACTERS';
968 end if;
969
970 RETURN gwf_nls_numeric_characters;
971
972 END nls_numeric_characters;
973
974 FUNCTION nls_language RETURN varchar2
975 is
976 l_value varchar2(64);
977 l_pos1 number;
978 l_pos2 number;
979 begin
980 if (gwf_nls_language is null) then
981 l_value := SYS_CONTEXT('USERENV', 'LANGUAGE');
982 l_pos1 := instr(l_value, '_');
983 l_pos2 := instr(l_value, '.');
984
985 gwf_nls_language := substr(l_value, 1, l_pos1-1);
986 gwf_nls_territory := substr(l_value, l_pos1+1, l_pos2-l_pos1-1);
987 end if;
988
989 RETURN gwf_nls_language;
990
991 END nls_language;
992
993 FUNCTION nls_territory RETURN varchar2
994 is
995 l_value varchar2(64);
996 begin
1000
997 if (gwf_nls_territory is null) then
998 l_value := nls_language; -- in nls_language we initialize both language and territory
999 end if;
1001 RETURN gwf_nls_territory;
1002
1003 END nls_territory;
1004
1005 procedure initializeNLSDefaults is
1006 l_val varchar2(64);
1007 begin
1008 l_val := nls_date_format;
1009 l_val := nls_date_language;
1010 l_val := nls_calendar;
1011 l_val := nls_sort ;
1012 l_val := nls_currency ;
1013 l_val := nls_numeric_characters ;
1014 l_val := nls_language ;
1015 l_val := nls_territory ;
1016 end ;
1017
1018
1019 --
1020 -- Tag_DB_Session (PRIVATE)
1021 -- Used by the different WF Engine entry points to tag the current session
1022 -- as per the Connection tag initiative described in bug 9370420
1023 -- This procedure checks for the user and application id. If they are not
1024 -- set then it means the context is not set.
1025 --
1026 procedure TAG_DB_SESSION(p_module_type varchar, p_action varchar2)
1027 is
1028 l_module V$SESSION.MODULE%TYPE;
1029 l_action V$SESSION.ACTION%TYPE;
1030 l_module_type varchar2(20);
1031 l_module_name V$SESSION.MODULE%TYPE;
1032 l_application_name varchar2(10);
1033 -- Since the format of module is e:<app>:wf:<item_type> we need to determine
1034 -- the values for module type and name
1035 pos2 integer;
1036 pos3 integer;
1037 sql_stm varchar2(400);
1038 begin
1039 -- Determine current session tags to see if they are already set
1040 dbms_application_info.read_module(l_module, l_action);
1041 pos2 := instr(l_module, ':', 1, 2);
1042 pos3 := instr(l_module, ':', 1, 3);
1043 l_module_type := substr(l_module, pos2+1, pos3-pos2-1);
1044 l_module_name := substr(l_module, pos3+1, length(l_module));
1045
1046 if (FND_GLOBAL.user_name is null OR FND_GLOBAL.resp_id is null) then
1047 -- Context is not set
1048 l_application_name := 'fnd';
1049 sql_stm := 'BEGIN FND_GLOBAL.TAG_DB_SESSION(:1, :2, :3); END;';
1050 execute immediate sql_stm using p_module_type, p_action, l_application_name;
1051 elsif (l_module_type <> p_module_type OR l_module_name <> p_action) then
1052 -- Different application process or item type in session
1053 sql_stm := 'BEGIN FND_GLOBAL.TAG_DB_SESSION(:1, :2); END;';
1054 execute immediate sql_stm using p_module_type, p_action;
1055 end if;
1056 exception
1057 when others then
1058 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_ERROR, 'WF_CORE.Tag_DB_Session',
1059 'module_type '||p_module_type||', module_name '||p_action);
1060 end;
1061
1062 -- database_default_edition deescribed in wfcores.pls
1063 function database_default_edition return varchar2 is
1064 begin
1065 if l_database_default_edition = 'NOT_SET' then
1066 l_database_default_edition := ad_zd.get_edition('RUN');
1067 end if;
1068 return l_database_default_edition;
1069 end database_default_edition;
1070
1071 -- database_current_edition deescribed in wfcores.pls
1072 function database_current_edition return varchar2 is
1073 begin
1074 return ad_zd.get_edition();
1075 end database_current_edition;
1076
1077 --
1078 -- getDedicatedComponentsCorrIds
1079 -- ER 16593551: Gets the correlation Ids of all dedicated components belongs
1080 -- to specified agent and return as comma separated values
1081 -- IN
1082 -- p_agent_name -- agent name
1083 -- p_schemaName -- schema name
1084 -- RETURNS
1085 -- boolean
1086 --
1087 function getDedicatedComponentsCorrIds(p_agent_name in varchar2,
1088 p_schemaName in varchar2)
1089 return varchar2 RESULT_CACHE
1090 is
1091
1092 cursor c_dedicated_corrIds(p_componentType varchar2) is
1093 select correlation_id
1094 from fnd_svc_components
1095 where component_status in
1096 ('RUNNING', 'STOPPED_ERROR', 'STARTING', 'DEACTIVATED_SYSTEM')
1097 and component_type = p_componentType
1098 and correlation_id is not NULL;
1099
1100 l_corrId_list varchar2(4000) := null;
1101 l_componentType varchar2(30) := null;
1102
1103 begin
1104
1105 if(p_agent_name = 'WF_DEFERRED') then
1106 l_componentType := 'WF_AGENT_LISTENER';
1107 elsif(p_agent_name = 'WF_JAVA_DEFERRED') then
1108 l_componentType := 'WF_JAVA_AGENT_LISTENER';
1109 elsif(p_agent_name = 'WF_NOTIFICATION_OUT') then
1110 l_componentType := 'WF_MAILER';
1111 end if;
1112
1113 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, 'wf.plsql.WF_CORE.getDedicatedComponentsCorrIds',
1114 'Getting the dedicated components corrId list for agent '||
1115 p_agent_name || ' and component type '|| l_componentType);
1116
1117 -- Get the list of dedicated components correlation Ids separated with commas
1118 for corrId in c_dedicated_corrIds(l_componentType) loop
1119 if(l_corrId_list is null) then
1120 l_corrId_list := p_schemaName || ':' ||corrId.correlation_id;
1121 else
1122 l_corrId_list := l_corrId_list || ',' || p_schemaName || ':'|| corrId.correlation_id;
1123 end if;
1124
1125 end loop;
1126
1127 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, 'wf.plsql.WF_CORE.getDedicatedComponentsCorrIds',
1128 'The dedicated components correlation Ids list is:'||l_corrId_list);
1129
1130 return l_corrId_list;
1131
1132 end getDedicatedComponentsCorrIds;
1133
1134 --
1135 -- matchCorrId
1136 -- ER 16593551: Checks that the given message corrId matches with the any one of the
1137 -- dedicated components corrId list and returns 0 if it matches, otherwise returns 1
1138 -- IN
1139 -- p_msgCorrId -- message correlation Id
1140 -- p_corrId_list -- dedicated components correlation Ids list
1141 -- RETURNS
1142 -- number
1143 --
1144 function matchCorrId(p_msgCorrId in varchar2,
1145 p_corrId_list in varchar2)
1146 return number RESULT_CACHE
1147 is
1148
1149 TYPE corrId_type IS TABLE OF VARCHAR2(250) INDEX BY PLS_INTEGER;
1150 l_corrId corrId_type;
1151
1152 begin
1153
1154 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, 'wf.plsql.WF_CORE.matchCorrId',
1155 'The message corrId is:'|| p_msgCorrId ||
1156 ', dedicated components correlation Ids list is:'|| p_corrId_list || ':');
1157
1158 -- Get each dedicated component correlation and return 0 if it
1159 -- matches with message correlation Id and else return 1
1160 FOR i IN 1 .. LENGTH (p_corrId_list)
1161 LOOP
1162
1163 l_corrid(i) := REGEXP_SUBSTR (p_corrId_list, '[^,]+', 1, i);
1164 EXIT WHEN l_corrid (i) IS NULL;
1165
1166 if( p_msgCorrId like l_corrid(i)) then
1167 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_ERROR, 'wf.plsql.WF_CORE.matchCorrId',
1168 'The message corrId matched with dedicated components corrId '||
1169 l_corrid(i) || ', returning 0');
1170 return 0;
1171 end if;
1172
1173 END LOOP;
1174
1175 WF_LOG_PKG.String(WF_LOG_PKG.LEVEL_PROCEDURE, 'wf.plsql.WF_CORE.matchCorrId',
1176 'The message corrId does not match any of the dedicated components ' ||
1177 'corrId, returning 1');
1178 return 1;
1179
1180 end matchCorrId;
1181
1182 end WF_CORE;