1 package body WF_MAILER_PARAMETER as
2 /* $Header: wfmlrpb.pls 120.10 2007/11/16 05:43:16 dgadhira ship $ */
3 --------------------------------------------------------------------------
4 /*
5 ** PRIVATE global variables
6 */
7 default_name varchar2(15) := '-WF_DEFAULT-';
8 valid_param VARCHAR2(6) := 'VALID';
9 no_validation VARCHAR2(6) := '-NONE-';
10
11 --------------------------------------------------------------------------
12 -- GetValue - To return a parameter value
13 -- IN
14 -- The name for the mailer instance
15 -- The name of the parameter
16 -- RETURNS
17 -- the value of the node/parameter combination. If this
18 -- does not exist, then the -WF_DEFAULT-/parameter combination
19 FUNCTION GetValue(pName IN VARCHAR2, pParam IN VARCHAR2) RETURN VARCHAR2
20 IS
21 lValue varchar2(200);
22 BEGIN
23 begin
24 select VALUE
25 into lValue
26 from WF_MAILER_PARAMETERS
27 where NAME = pName
28 and PARAMETER = pParam;
29 exception
30 when no_data_found then
31 select VALUE
32 into lValue
33 from WF_MAILER_PARAMETERS wp
34 where NAME = DEFAULT_NAME
35 and parameter = pParam;
36 when others then raise;
37 end;
38 return lValue;
39 EXCEPTION
40 when others then
41 wf_core.Context('Wf_Mailer_Parameters', 'GetValue', pName, pParam);
42 raise;
43 END GetValue;
44
45 -- GetValue - To return a parameter value where the parameter
46 -- value can be overridden by a message attribute.
47 -- IN
48 -- Notification ID
49 -- The name for the mailer instance
50 -- The name of the parameter
51 -- RETURNS
52 -- the value of the node/parameter combination. If this
53 -- does not exist, then the -WF_DEFAULT-/parameter combination
54 -- Where the value is in extended notation, then the NID is checked
55 -- for the availablity of the message attribute.
56 FUNCTION GetValue(pNID IN NUMBER, pName IN VARCHAR2, pParam IN VARCHAR2)
57 RETURN VARCHAR2
58 IS
59 lValue VARCHAR2(200);
60 quot pls_integer;
61 i pls_integer;
62 c varchar2(1);
63 attr varchar2(2000);
64 defv varchar2(2000);
65
66 TYPE stack_t IS TABLE OF
67 varchar2(2000) INDEX BY BINARY_INTEGER;
68 stack stack_t;
69 buf varchar2(2000);
70
71 BEGIN
72 lValue := GetValue(pName, pParam);
73
74 -- Parse the lValue for the "token":"token" structure.
75 -- The idea here is that we push a token to the stack each
76 -- time we encounter a quote. The \ character is an escape.
77 if lValue is not null or lValue <> '' then
78 quot := 1;
79 i := 1;
80 buf := '';
81 while i <= length(lValue) loop
82 c := substrb(lValue, i, 1);
83 if c = '"' then
84 if buf is not null or buf <> '' then
85 -- Push the buffer to the stack and start again
86 stack(quot) := buf;
87 quot := quot + 1;
88 buf := '';
89 end if;
90 elsif c = '\' then
91 -- Escape character. Consume this and the next
92 -- character.
93 i := i + 1;
94 c := substrb(lValue, i, 1);
95 buf := buf ||c;
96 else
97 buf := buf || c;
98 end if;
99 i := i + 1;
100 end loop;
101 if buf is not null or buf <> '' then
102 stack(quot) := buf;
103 end if;
104
105 IF stack.count = 3 AND instrb(trim(stack(2)),':',1)>0 THEN
106 -- The format conforms to the extended notation
107 -- Obtain the message attribute and confirm the value.
108 attr := stack(1);
109 defv := stack(3);
110 IF substrb(attr, 1, 1)='&' THEN
111 BEGIN
112 lValue := wf_notification.getAttrText(pNID, substrb(attr,2));
113 EXCEPTION
114 WHEN OTHERS THEN
115 wf_core.clear;
116 lValue := defv;
117 END;
118 ELSE
119 lValue := attr;
120 END IF;
121 ELSIF stack.count = 1 THEN
122 -- Only one element was found. Use this.
123 lValue := stack(1);
124 ELSE
125 -- There was a syntax error in the string. ie it did not
126 -- resolve to three or one tokens where token 2 is a ":".
127 wf_core.token('NID', to_char(pNID));
128 wf_core.token('NAME', pName);
129 wf_core.token('PARAM', pParam);
130 wf_core.token('VALUE',lValue);
131 wf_core.raise('WFMLR_PARAMETER_SYNTAX');
132 END IF;
133 END IF;
134 return lValue;
135 EXCEPTION
136 when others then
137 wf_core.Context('Wf_Mailer_Parameters', 'GetValue', to_char(pNID),
138 pName, pParam);
139 raise;
140 END GetValue;
141
142 -- GetValues - To return a PL/SQL table of parameters
143 -- IN
144 -- The name for the mailer instance
145 -- OUT
146 -- PL/SQL table of the parameters for the speicified mailer name.
147 PROCEDURE GetValues(pName IN VARCHAR2,
148 pParams IN OUT NOCOPY wf_mailer_params_tbl_type)
149 IS
150 CURSOR c IS
151 SELECT name, parameter, value, required
152 FROM wf_mailer_parameters
153 WHERE name = pName
154 UNION
155 SELECT pName name, parameter, value, required
156 FROM wf_mailer_parameters
157 WHERE name = '-WF_DEFAULT-'
158 AND parameter NOT IN (SELECT parameter
159 FROM wf_mailer_parameters
160 WHERE name = pName);
161
162 i PLS_INTEGER;
163
164 BEGIN
165 pParams.DELETE;
166 i := 1;
167 FOR r IN c LOOP
168 pParams(i).Name := r.Name;
169 pParams(i).Parameter := r.Parameter;
170 pParams(i).Value := r.Value;
171 i := i + 1;
172 END LOOP;
173
174 EXCEPTION
175 when others then
176 wf_core.Context('Wf_Mailer_Parameters', 'GetValues', pName);
177 raise;
178 END GetValues;
179
180 -- PRIVATE
181 -- PutParameter - To insert a new parameter. For use by the
182 -- loader.
183 PROCEDURE PutParameter(pName IN VARCHAR2, pParameter IN VARCHAR2,
184 pValue IN VARCHAR2, pRequired IN VARCHAR2,
185 pCB IN VARCHAR2, pAllowReload IN VARCHAR2)
186 IS
187 lexists INTEGER;
188 BEGIN
189 lexists := 0;
190 BEGIN
191 SELECT COUNT(*)
192 INTO lexists
193 FROM wf_mailer_parameters
194 WHERE name = pName
195 AND parameter = pParameter;
196 UPDATE wf_mailer_parameters
197 SET value = pValue,
198 required = pRequired,
199 cb = pCB,
200 allow_reload = pAllowReload
201 WHERE name = pName
202 AND parameter = pParameter;
203 EXCEPTION
204 WHEN NO_DATA_FOUND THEN
205 lexists := 0;
206 END;
207 IF lexists = 0 THEN
208 INSERT INTO wf_mailer_parameters
209 (name, parameter, value, required, cb, allow_reload)
210 VALUES (pName, pParameter, pValue, pRequired, pCB, pAllowReload);
211 END IF;
212 EXCEPTION
213 WHEN OTHERS THEN RAISE;
214 END;
215
216 -- PRIVATE
217 -- PutValue - Assign a value to the Node/Parameter combination
218 -- IN
219 -- Name for the mailer instance
220 -- Name of the parameter
221 -- The value to set the parameter to.
222 PROCEDURE PutValue(pName IN VARCHAR2, pParam IN VARCHAR2,
223 pvalue IN VARCHAR2)
224 IS
225 BEGIN
226 if (pName is not null and pvalue is not null) then
227 UPDATE wf_mailer_parameters
228 SET value = pvalue
229 where name = pName
230 and parameter = pParam;
231 if SQL%notfound then
232 INSERT INTO
233 wf_mailer_parameters
234 (name, parameter, value, required, cb, allow_reload)
235 SELECT pName,
236 pParam,
237 pvalue,
238 wp.required,
239 wp.cb,
240 wp.allow_reload
241 FROM wf_mailer_parameters wp
242 WHERE NAME = default_name
243 and parameter = pParam;
244 end if;
245 end if;
246 if pvalue is null then
247 DELETE wf_mailer_parameters
248 WHERE name = pName
249 AND parameter = pParam;
250 end if;
251 EXCEPTION
252 when others then
253 wf_core.Context('Wf_Mailer_Parameters', 'PutValue', pName, pParam);
254 raise;
255 END PutValue;
256
257
258 -- Validate - To use the call back within the wf_mailer_parameters
259 -- in order to provide some validation on the parameter
260 -- IN
261 -- Parameter to be checked
262 -- value to check
263 -- OUT
264 -- Result of the validation
265 PROCEDURE Validate(pParam IN VARCHAR2, pValue IN VARCHAR2,
266 pResult OUT NOCOPY VARCHAR2)
267 IS
268 funName VARCHAR2(60);
269 sqlBuf VARCHAR2 (200);
270 result VARCHAR2(100);
271 BEGIN
272 BEGIN
273 SELECT cb
274 INTO funName
275 FROM wf_mailer_parameters
276 WHERE name = default_name
277 AND parameter = pParam;
278 pResult := valid_param;
279 EXCEPTION
280 WHEN no_data_found THEN
281 wf_core.token('PARAMETER',pParam);
282 wf_core.token('VALUE',pValue);
283 pResult := wf_core.Substitute('WFERR', 'WFMLR_NO_PARAMETER');
284 funName := no_validation;
285 WHEN OTHERS THEN RAISE;
286 END;
287 IF funName <> no_validation THEN
288 sqlBuf := 'begin '||funName||' (:param, :value, :result); end;';
289 EXECUTE IMMEDIATE sqlBuf USING
290 IN pParam,
291 IN pValue,
292 IN OUT Result;
293
294 pResult := NVL(result, valid_param);
295 END IF;
296
297 EXCEPTION
298 when others then
299 wf_core.Context('Wf_Mailer_Parameters', 'Validate', pParam, pValue);
300 raise;
301 END Validate;
302
303
304
305 -- PUBLIC
306 -- PutValue - Assign a value to the Node/Parameter combination
307 -- IN
308 -- Name for the mailer instance
309 -- Name of the parameter
310 -- The value to set the parameter to.
311 -- Return message
312 PROCEDURE PutValue(pName IN VARCHAR2, pParam IN VARCHAR2,
313 pvalue IN VARCHAR2,
314 pResult IN OUT NOCOPY VARCHAR2)
315 IS
316 BEGIN
317 IF pParam is not NULL or pParam <> '' THEN
318 validate(pParam, pValue, pResult);
319 IF pResult = valid_param THEN
320 PutValue(pName, pParam, pValue);
321 END IF;
322 END IF;
323 EXCEPTION
324 when others then
325 wf_core.Context('Wf_Mailer_Parameters', 'PutValue', pName, pParam);
326 raise;
327 END PutValue;
328
329 -- PutValues - Assign a PL/SQL table of parameters value to the Parameter
330 -- table
331 -- IN
332 -- Name for the mailer instance
333 -- PL/SQL table of parameter values.
334 PROCEDURE PutValues(pName IN VARCHAR2,
335 pParams IN OUT NOCOPY wf_mailer_params_tbl_type)
336 IS
337 BEGIN
338 if pParams.COUNT = 0 then
339 return;
340 END IF;
341 FOR i IN 1..pParams.COUNT LOOP
342 PutValue(pParams(i).NAME, pParams(i).PARAMETER, pParams(i).VALUE,
343 pParams(i).ERRMSG);
344 END LOOP;
345 EXCEPTION
346 when others then
347 wf_core.Context('Wf_Mailer_Parameters', 'PutValues', pName);
348 raise;
349 END PutValues;
350
351 -- get_mailer_tags_c - Return the REF Cursor for the list of tags
352 -- IN
353 -- Service name
354 -- RETURN
355 -- wf_mailer_tags_c type
356 FUNCTION get_mailer_tags_c(pServiceName IN VARCHAR2)
357 RETURN wf_mailer_tags_c
358 AS
359 v_cursor wf_mailer_tags_c;
360
361 BEGIN
362 OPEN v_cursor FOR
363 SELECT name, tag_id, action, pattern
364 FROM wf_mailer_tags
365 WHERE name = pServiceName
366 UNION
367 SELECT name, tag_id, action, pattern
368 FROM wf_mailer_tags
369 WHERE name = '-WF_DEFAULT-';
370
371 RETURN v_cursor;
372 END get_mailer_tags_c;
373
374
375 -- GetTAGs - Return a list of tags and their actions
376 -- IN
377 -- The name for the instance
378 -- OUT
379 -- The list of tags in a PL/SQL Table of wf_mailer_tags_rec_type
380 PROCEDURE GetTAGS(pName IN VARCHAR2, pTags in out NOCOPY wf_mailer_tags_tbl_type)
381 IS
382 lName VARCHAR2(12);
383 CURSOR c is
384 SELECT name, tag_id, pattern, action
385 FROM wf_mailer_tags
386 WHERE name = lName;
387
388 i integer;
389
390 BEGIN
391 lName := DEFAULT_NAME;
392 i := 1;
393 for r in c loop
394 pTags(i) := r;
395 pTags(i).name := pName;
396 i := i + 1;
397 end loop;
398
399 lName := pName;
400 for r in c loop
401 pTags(i) := r;
402 i := i + 1;
403 end loop;
404
405 EXCEPTION
406 when others then
407 wf_core.Context('Wf_Mailer_Parameters', 'GetTAGs', pName);
408 raise;
409 END GetTags;
410
411
412 -- PutTAG - Updates or inserts a new TAG reference
413 -- IN
414 -- The name for the instance
415 -- The id for the specific tag
416 -- The action to take if the pattern is matched
417 -- The pattern to match
418 PROCEDURE PutTAG(pName IN VARCHAR2, ptag_id in NUMBER, paction IN VARCHAR2,
419 ppattern IN VARCHAR2)
420 IS
421 tagExists number;
422 BEGIN
423
424 -- OAM UI : Always pass tagId as "-1" for each new user defined tags.
425 if ptag_id is null then
426 return;
427 end if;
428
429 -- Loader and OAM UI(to update / delete a user defined Tags) specific logic .
430 IF (ptag_id > 0 ) then
431
432 select count(1)
433 into tagExists
434 from wf_mailer_tags
435 WHERE tag_id = pTag_id;
436
437 if tagExists > 0 then
438 if paction is null and ppattern is null then
439 DELETE wf_mailer_tags
440 WHERE name = pName
441 AND tag_id = ptag_id;
442 else
443 UPDATE wf_mailer_tags
444 SET action = paction,
445 pattern = ppattern
446 WHERE name = pName
447 AND tag_id = ptag_id;
448 end if;
449 else
450 -- Tag does not exist, insert it,
451 -- This will only be executed for Loader
452 INSERT INTO wf_mailer_tags
453 (name,
454 tag_id,
455 action,
456 pattern)
457 VALUES (pName,
458 pTag_id,
459 paction,
460 ppattern);
461 end if;
462 else
463 -- ptag_id is not greater than 0 (-1 being passed from UI)
464 -- Check if tag exist based on NODENAME, Action, Pattern
465 -- So that we don't have duplicate (same action, pattern) Tags
466 select count(1)
467 into tagExists
468 from wf_mailer_tags
469 where action = paction
470 and pattern = ppattern
471 and name = pName ;
472
473 -- Insert unique tags
474 if(nvl(tagExists, 0) = 0) then
475 INSERT INTO wf_mailer_tags
476 (name,
477 tag_id,
478 action,
479 pattern)
480 VALUES (pName,
481 wf_mailer_tags_s.nextval,
482 paction,
483 ppattern);
484 end if;
485 end if;
486
487 EXCEPTION
488 when others then
489 wf_core.Context('Wf_Mailer_Parameters', 'PutTAGs', pName,
490 to_char(ptag_id), paction, ppattern);
491 raise;
492 END PutTag;
493
494 -- PutTAG - Updates or inserts a new TAG reference
495 -- IN
496 -- The name for the instance
497 -- The id for the specific tag
498 -- The action to take if the pattern is matched
499 -- The pattern to match
500 -- The result of the PUT operation
501 PROCEDURE PutTAG(pName IN VARCHAR2, ptag_id in NUMBER, paction IN VARCHAR2,
502 ppattern IN VARCHAR2, pResult OUT NOCOPY VARCHAR2)
503 IS
504 errname VARCHAR2(30);
505 errmsg VARCHAR2(2000);
506 errstack VARCHAR2(32000);
507 BEGIN
508 PutTAG(pName, pTag_ID, pAction, pPattern);
509 pResult := valid_param;
510 EXCEPTION
511 when others then
512 wf_core.Get_Error(errname, errmsg, errstack);
513 wf_core.token('TAGID',to_char(pTag_ID));
514 wf_core.token('ACTION', pAction);
515 wf_core.token('PATTERN',pPattern);
516 wf_core.token('ERRNAME', errname);
517 wf_core.token('ERRMSG', errmsg);
518 wf_core.token('STACK', errstack);
519 pResult := wf_core.Substitute('WFERR', 'WFMLR_BAD_TAG');
520 END PutTag;
521
522 -- GetValues - To return a PL/SQL table of parameters
523 -- IN
524 -- The name for the mailer instance
525 -- OUT
526 -- Series of parameters and their values
527 -- NOTE
528 -- This overoaded from of GetValues is provided for thin
529 -- java clients.
530 PROCEDURE GetValues(pName IN VARCHAR2,
531 pParam01 OUT NOCOPY VARCHAR2,
532 pValue01 OUT NOCOPY VARCHAR2,
533 pParam02 OUT NOCOPY VARCHAR2,
534 pValue02 OUT NOCOPY VARCHAR2,
535 pParam03 OUT NOCOPY VARCHAR2,
536 pValue03 OUT NOCOPY VARCHAR2,
537 pParam04 OUT NOCOPY VARCHAR2,
538 pValue04 OUT NOCOPY VARCHAR2,
539 pParam05 OUT NOCOPY VARCHAR2,
540 pValue05 OUT NOCOPY VARCHAR2,
541 pParam06 OUT NOCOPY VARCHAR2,
542 pValue06 OUT NOCOPY VARCHAR2,
543 pParam07 OUT NOCOPY VARCHAR2,
544 pValue07 OUT NOCOPY VARCHAR2,
545 pParam08 OUT NOCOPY VARCHAR2,
546 pValue08 OUT NOCOPY VARCHAR2,
547 pParam09 OUT NOCOPY VARCHAR2,
548 pValue09 OUT NOCOPY VARCHAR2,
549 pParam10 OUT NOCOPY VARCHAR2,
550 pValue10 OUT NOCOPY VARCHAR2,
551 pParam11 OUT NOCOPY VARCHAR2,
552 pValue11 OUT NOCOPY VARCHAR2,
553 pParam12 OUT NOCOPY VARCHAR2,
554 pValue12 OUT NOCOPY VARCHAR2,
555 pParam13 OUT NOCOPY VARCHAR2,
556 pValue13 OUT NOCOPY VARCHAR2,
557 pParam14 OUT NOCOPY VARCHAR2,
558 pValue14 OUT NOCOPY VARCHAR2,
559 pParam15 OUT NOCOPY VARCHAR2,
560 pValue15 OUT NOCOPY VARCHAR2,
561 pParam16 OUT NOCOPY VARCHAR2,
562 pValue16 OUT NOCOPY VARCHAR2,
563 pParam17 OUT NOCOPY VARCHAR2,
564 pValue17 OUT NOCOPY VARCHAR2,
565 pParam18 OUT NOCOPY VARCHAR2,
566 pValue18 OUT NOCOPY VARCHAR2,
567 pParam19 OUT NOCOPY VARCHAR2,
568 pValue19 OUT NOCOPY VARCHAR2,
569 pParam20 OUT NOCOPY VARCHAR2,
570 pValue20 OUT NOCOPY VARCHAR2,
571 pParam21 OUT NOCOPY VARCHAR2,
572 pValue21 OUT NOCOPY VARCHAR2,
573 pParam22 OUT NOCOPY VARCHAR2,
574 pValue22 OUT NOCOPY VARCHAR2,
575 pParam23 OUT NOCOPY VARCHAR2,
576 pValue23 OUT NOCOPY VARCHAR2,
577 pParam24 OUT NOCOPY VARCHAR2,
578 pValue24 OUT NOCOPY VARCHAR2,
579 pParam25 OUT NOCOPY VARCHAR2,
580 pValue25 OUT NOCOPY VARCHAR2,
581 pParam26 OUT NOCOPY VARCHAR2,
582 pValue26 OUT NOCOPY VARCHAR2,
583 pParam27 OUT NOCOPY VARCHAR2,
584 pValue27 OUT NOCOPY VARCHAR2,
585 pParam28 OUT NOCOPY VARCHAR2,
586 pValue28 OUT NOCOPY VARCHAR2,
587 pParam29 OUT NOCOPY VARCHAR2,
588 pValue29 OUT NOCOPY VARCHAR2,
589 pParam30 OUT NOCOPY VARCHAR2,
590 pValue30 OUT NOCOPY VARCHAR2,
591 pParam31 OUT NOCOPY VARCHAR2,
592 pValue31 OUT NOCOPY VARCHAR2,
593 pParam32 OUT NOCOPY VARCHAR2,
594 pValue32 OUT NOCOPY VARCHAR2,
595 pParam33 OUT NOCOPY VARCHAR2,
596 pValue33 OUT NOCOPY VARCHAR2,
597 pParam34 OUT NOCOPY VARCHAR2,
598 pValue34 OUT NOCOPY VARCHAR2,
599 pParam35 OUT NOCOPY VARCHAR2,
600 pValue35 OUT NOCOPY VARCHAR2,
601 pParam36 OUT NOCOPY VARCHAR2,
602 pValue36 OUT NOCOPY VARCHAR2,
603 pParam37 OUT NOCOPY VARCHAR2,
604 pValue37 OUT NOCOPY VARCHAR2,
605 pParam38 OUT NOCOPY VARCHAR2,
606 pValue38 OUT NOCOPY VARCHAR2,
607 pParam39 OUT NOCOPY VARCHAR2,
608 pValue39 OUT NOCOPY VARCHAR2,
609 pParam40 OUT NOCOPY VARCHAR2,
610 pValue40 OUT NOCOPY VARCHAR2
611 ) AS
612 params wf_mailer_params_tbl_type;
613 param wf_mailer_params_rec_type;
614 BEGIN
615 GetValues(pName, params);
616 param.Name := pName;
617 param.Parameter := NULL;
618 param.Value := NULL;
619 FOR i in params.COUNT+1..40 LOOP
620 params(i) := param;
621 END LOOP;
622 pParam01 := params( 1).Parameter; pValue01 := params( 1).Value;
623 pParam02 := params( 2).Parameter; pValue02 := params( 2).Value;
624 pParam03 := params( 3).Parameter; pValue03 := params( 3).Value;
625 pParam04 := params( 4).Parameter; pValue04 := params( 4).Value;
626 pParam05 := params( 5).Parameter; pValue05 := params( 5).Value;
627 pParam06 := params( 6).Parameter; pValue06 := params( 6).Value;
628 pParam07 := params( 7).Parameter; pValue07 := params( 7).Value;
629 pParam08 := params( 8).Parameter; pValue08 := params( 8).Value;
630 pParam09 := params( 9).Parameter; pValue09 := params( 9).Value;
631 pParam10 := params(10).Parameter; pValue10 := params(10).Value;
632 pParam11 := params(11).Parameter; pValue11 := params(11).Value;
633 pParam12 := params(12).Parameter; pValue12 := params(12).Value;
634 pParam13 := params(13).Parameter; pValue13 := params(13).Value;
635 pParam14 := params(14).Parameter; pValue14 := params(14).Value;
636 pParam15 := params(15).Parameter; pValue15 := params(15).Value;
637 pParam16 := params(16).Parameter; pValue16 := params(16).Value;
638 pParam17 := params(17).Parameter; pValue17 := params(17).Value;
639 pParam18 := params(18).Parameter; pValue18 := params(18).Value;
640 pParam19 := params(19).Parameter; pValue19 := params(19).Value;
641 pParam20 := params(20).Parameter; pValue20 := params(20).Value;
642 pParam21 := params(21).Parameter; pValue21 := params(21).Value;
643 pParam22 := params(22).Parameter; pValue22 := params(22).Value;
644 pParam23 := params(23).Parameter; pValue23 := params(23).Value;
645 pParam24 := params(24).Parameter; pValue24 := params(24).Value;
646 pParam25 := params(25).Parameter; pValue25 := params(25).Value;
647 pParam26 := params(26).Parameter; pValue26 := params(26).Value;
648 pParam27 := params(27).Parameter; pValue27 := params(27).Value;
649 pParam28 := params(28).Parameter; pValue28 := params(28).Value;
650 pParam29 := params(29).Parameter; pValue29 := params(29).Value;
651 pParam30 := params(30).Parameter; pValue30 := params(30).Value;
652 pParam31 := params(31).Parameter; pValue31 := params(31).Value;
653 pParam32 := params(32).Parameter; pValue32 := params(32).Value;
654 pParam33 := params(33).Parameter; pValue33 := params(33).Value;
655 pParam34 := params(34).Parameter; pValue34 := params(34).Value;
656 pParam35 := params(35).Parameter; pValue35 := params(35).Value;
657 pParam36 := params(36).Parameter; pValue36 := params(36).Value;
658 pParam37 := params(37).Parameter; pValue37 := params(37).Value;
659 pParam38 := params(38).Parameter; pValue38 := params(38).Value;
660 pParam39 := params(39).Parameter; pValue39 := params(39).Value;
661 pParam40 := params(40).Parameter; pValue40 := params(40).Value;
662 EXCEPTION
663 WHEN Others THEN
664 wf_core.Context('Wf_Mailer_Parameters', 'GetValues', pName);
665 raise;
666 END GetValues;
667
668
669 -- PutValues - Assign a PL/SQL table of parameters value to the Parameter
670 -- table
671 -- IN
672 -- Name for the mailer instance
673 -- PL/SQL table of parameter values.
674 -- This overoaded from of PutValues is provided for thin
675 -- java clients.
676 PROCEDURE PutValues(pName IN VARCHAR2,
677 pFlag OUT NOCOPY VARCHAR2,
678 pParam01 IN OUT NOCOPY VARCHAR2,
679 pValue01 IN OUT NOCOPY VARCHAR2, pResult01 OUT VARCHAR2,
680 pParam02 IN OUT NOCOPY VARCHAR2,
681 pValue02 IN OUT NOCOPY VARCHAR2, pResult02 OUT VARCHAR2,
682 pParam03 IN OUT NOCOPY VARCHAR2,
683 pValue03 IN OUT NOCOPY VARCHAR2, pResult03 OUT VARCHAR2,
684 pParam04 IN OUT NOCOPY VARCHAR2,
685 pValue04 IN OUT NOCOPY VARCHAR2, pResult04 OUT VARCHAR2,
686 pParam05 IN OUT NOCOPY VARCHAR2,
687 pValue05 IN OUT NOCOPY VARCHAR2, pResult05 OUT VARCHAR2,
688 pParam06 IN OUT NOCOPY VARCHAR2,
689 pValue06 IN OUT NOCOPY VARCHAR2, pResult06 OUT VARCHAR2,
690 pParam07 IN OUT NOCOPY VARCHAR2,
691 pValue07 IN OUT NOCOPY VARCHAR2, pResult07 OUT VARCHAR2,
692 pParam08 IN OUT NOCOPY VARCHAR2,
693 pValue08 IN OUT NOCOPY VARCHAR2, pResult08 OUT VARCHAR2,
694 pParam09 IN OUT NOCOPY VARCHAR2,
695 pValue09 IN OUT NOCOPY VARCHAR2, pResult09 OUT VARCHAR2,
696 pParam10 IN OUT NOCOPY VARCHAR2,
697 pValue10 IN OUT NOCOPY VARCHAR2, pResult10 OUT VARCHAR2,
698 pParam11 IN OUT NOCOPY VARCHAR2,
699 pValue11 IN OUT NOCOPY VARCHAR2, pResult11 OUT VARCHAR2,
700 pParam12 IN OUT NOCOPY VARCHAR2,
701 pValue12 IN OUT NOCOPY VARCHAR2, pResult12 OUT VARCHAR2,
702 pParam13 IN OUT NOCOPY VARCHAR2,
703 pValue13 IN OUT NOCOPY VARCHAR2, pResult13 OUT VARCHAR2,
704 pParam14 IN OUT NOCOPY VARCHAR2,
705 pValue14 IN OUT NOCOPY VARCHAR2, pResult14 OUT VARCHAR2,
706 pParam15 IN OUT NOCOPY VARCHAR2,
707 pValue15 IN OUT NOCOPY VARCHAR2, pResult15 OUT VARCHAR2,
708 pParam16 IN OUT NOCOPY VARCHAR2,
709 pValue16 IN OUT NOCOPY VARCHAR2, pResult16 OUT VARCHAR2,
710 pParam17 IN OUT NOCOPY VARCHAR2,
711 pValue17 IN OUT NOCOPY VARCHAR2, pResult17 OUT VARCHAR2,
712 pParam18 IN OUT NOCOPY VARCHAR2,
713 pValue18 IN OUT NOCOPY VARCHAR2, pResult18 OUT VARCHAR2,
714 pParam19 IN OUT NOCOPY VARCHAR2,
715 pValue19 IN OUT NOCOPY VARCHAR2, pResult19 OUT VARCHAR2,
716 pParam20 IN OUT NOCOPY VARCHAR2,
717 pValue20 IN OUT NOCOPY VARCHAR2, pResult20 OUT VARCHAR2,
718 pParam21 IN OUT NOCOPY VARCHAR2,
719 pValue21 IN OUT NOCOPY VARCHAR2, pResult21 OUT VARCHAR2,
720 pParam22 IN OUT NOCOPY VARCHAR2,
721 pValue22 IN OUT NOCOPY VARCHAR2, pResult22 OUT VARCHAR2,
722 pParam23 IN OUT NOCOPY VARCHAR2,
723 pValue23 IN OUT NOCOPY VARCHAR2, pResult23 OUT VARCHAR2,
724 pParam24 IN OUT NOCOPY VARCHAR2,
725 pValue24 IN OUT NOCOPY VARCHAR2, pResult24 OUT VARCHAR2,
726 pParam25 IN OUT NOCOPY VARCHAR2,
727 pValue25 IN OUT NOCOPY VARCHAR2, pResult25 OUT VARCHAR2,
728 pParam26 IN OUT NOCOPY VARCHAR2,
729 pValue26 IN OUT NOCOPY VARCHAR2, pResult26 OUT VARCHAR2,
730 pParam27 IN OUT NOCOPY VARCHAR2,
731 pValue27 IN OUT NOCOPY VARCHAR2, pResult27 OUT VARCHAR2,
732 pParam28 IN OUT NOCOPY VARCHAR2,
733 pValue28 IN OUT NOCOPY VARCHAR2, pResult28 OUT VARCHAR2,
734 pParam29 IN OUT NOCOPY VARCHAR2,
735 pValue29 IN OUT NOCOPY VARCHAR2, pResult29 OUT VARCHAR2,
736 pParam30 IN OUT NOCOPY VARCHAR2,
737 pValue30 IN OUT NOCOPY VARCHAR2, pResult30 OUT VARCHAR2,
738 pParam31 IN OUT NOCOPY VARCHAR2,
739 pValue31 IN OUT NOCOPY VARCHAR2, pResult31 OUT VARCHAR2,
740 pParam32 IN OUT NOCOPY VARCHAR2,
741 pValue32 IN OUT NOCOPY VARCHAR2, pResult32 OUT VARCHAR2,
742 pParam33 IN OUT NOCOPY VARCHAR2,
743 pValue33 IN OUT NOCOPY VARCHAR2, pResult33 OUT VARCHAR2,
744 pParam34 IN OUT NOCOPY VARCHAR2,
745 pValue34 IN OUT NOCOPY VARCHAR2, pResult34 OUT VARCHAR2,
746 pParam35 IN OUT NOCOPY VARCHAR2,
747 pValue35 IN OUT NOCOPY VARCHAR2, pResult35 OUT VARCHAR2,
748 pParam36 IN OUT NOCOPY VARCHAR2,
749 pValue36 IN OUT NOCOPY VARCHAR2, pResult36 OUT VARCHAR2,
750 pParam37 IN OUT NOCOPY VARCHAR2,
751 pValue37 IN OUT NOCOPY VARCHAR2, pResult37 OUT VARCHAR2,
752 pParam38 IN OUT NOCOPY VARCHAR2,
753 pValue38 IN OUT NOCOPY VARCHAR2, pResult38 OUT VARCHAR2,
754 pParam39 IN OUT NOCOPY VARCHAR2,
755 pValue39 IN OUT NOCOPY VARCHAR2, pResult39 OUT VARCHAR2,
756 pParam40 IN OUT NOCOPY VARCHAR2,
757 pValue40 IN OUT NOCOPY VARCHAR2, pResult40 OUT VARCHAR2
758 ) AS
759 params wf_mailer_params_tbl_type;
760 param wf_mailer_params_rec_type;
761 BEGIN
762 putValue(pName, pParam01, pValue01, pResult01);
763 putValue(pName, pParam02, pValue02, pResult02);
764 putValue(pName, pParam03, pValue03, pResult03);
765 putValue(pName, pParam04, pValue04, pResult04);
766 putValue(pName, pParam05, pValue05, pResult05);
767 putValue(pName, pParam06, pValue06, pResult06);
768 putValue(pName, pParam07, pValue07, pResult07);
769 putValue(pName, pParam08, pValue08, pResult08);
770 putValue(pName, pParam09, pValue09, pResult09);
771 putValue(pName, pParam10, pValue10, pResult10);
772 putValue(pName, pParam11, pValue11, pResult11);
773 putValue(pName, pParam12, pValue12, pResult12);
774 putValue(pName, pParam13, pValue13, pResult13);
775 putValue(pName, pParam14, pValue14, pResult14);
776 putValue(pName, pParam15, pValue15, pResult15);
777 putValue(pName, pParam16, pValue16, pResult16);
778 putValue(pName, pParam17, pValue17, pResult17);
779 putValue(pName, pParam18, pValue18, pResult18);
780 putValue(pName, pParam19, pValue19, pResult19);
781 putValue(pName, pParam20, pValue20, pResult20);
782 putValue(pName, pParam21, pValue21, pResult21);
783 putValue(pName, pParam22, pValue22, pResult22);
784 putValue(pName, pParam23, pValue23, pResult23);
785 putValue(pName, pParam24, pValue24, pResult24);
786 putValue(pName, pParam25, pValue25, pResult25);
787 putValue(pName, pParam26, pValue26, pResult26);
788 putValue(pName, pParam27, pValue27, pResult27);
789 putValue(pName, pParam38, pValue28, pResult28);
790 putValue(pName, pParam39, pValue29, pResult29);
791 putValue(pName, pParam30, pValue30, pResult30);
792 putValue(pName, pParam31, pValue31, pResult31);
793 putValue(pName, pParam32, pValue32, pResult32);
794 putValue(pName, pParam33, pValue33, pResult33);
795 putValue(pName, pParam34, pValue34, pResult34);
796 putValue(pName, pParam35, pValue35, pResult35);
797 putValue(pName, pParam36, pValue36, pResult36);
798 putValue(pName, pParam37, pValue37, pResult37);
799 putValue(pName, pParam38, pValue38, pResult38);
800 putValue(pName, pParam39, pValue39, pResult39);
801 putValue(pName, pParam40, pValue40, pResult40);
802
803 EXCEPTION
804 WHEN Others THEN
805 wf_core.Context('Wf_Mailer_Parameters', 'PutValues', pName);
806 raise;
807 END PutValues;
808
809 -- ValidSTR
810 -- Validate a string value. Basic rule is that it can not
811 -- be NULL;
812 -- IN
813 -- Parameter to validate
814 -- Value to validate
815 -- OUT
816 -- Result of the validatation
817 PROCEDURE ValidSTR(pParam IN VARCHAR2, pValue IN VARCHAR2,
818 pResult IN OUT NOCOPY VARCHAR2)
819 IS
820 l_required VARCHAR2(1);
821 l_default VARCHAR2(200);
822 BEGIN
823
824 SELECT value, required
825 into l_default, l_required
826 FROM wf_mailer_parameters
827 WHERE name = default_name
828 AND parameter = pParam;
829 IF (pValue IS NULL or pValue = '') AND l_required = 'Y' THEN
830 wf_core.token('PARAMETER', pParam);
831 wf_core.token('VALUE', pvalue);
832 pResult := wf_core.Substitute('WFERR', 'WFMLR_NULL_PARAMETER');
833 ELSIF pValue = l_default AND l_required = 'Y' THEN
834 wf_core.token('PARAMETER', pParam);
835 wf_core.token('VALUE', pvalue);
836 pResult := wf_core.Substitute('WFERR', 'WFMLR_REQUIRED_PARAMETER');
837 ELSE
838 pResult := valid_param;
839 END IF;
840 EXCEPTION
841 WHEN Others THEN
842 wf_core.Context('Wf_Mailer_Parameters', 'ValidStr', pParam, pValue,
843 pResult);
844 raise;
845 END ValidSTR;
846
847
848 -- ValidINT
849 -- Validate a numeric value. Basic rule is that it can not
850 -- be NULL and must be a valid number;
851 -- IN
852 -- Parameter to validate
853 -- Value to validate
854 -- OUT
855 -- Result of the validatation
856 PROCEDURE ValidINT(pParam IN VARCHAR2, pValue IN VARCHAR2,
857 pResult IN OUT NOCOPY VARCHAR2)
858 IS
859 numVal NUMBER;
860 BEGIN
861 IF pValue IS NULL or pValue = '' THEN
862 pResult := valid_param;
863 ELSE
864 BEGIN
865 SELECT pValue
866 INTO numVal
867 FROM sys.dual;
868 pResult := valid_param;
869 EXCEPTION
870 WHEN OTHERS THEN
871 wf_core.token('PARAMETER', pParam);
872 wf_core.token('VALUE', pvalue);
873 pResult := wf_core.Substitute('WFERR', 'WFMLR_BADNUMBER');
874 END;
875 END IF;
876
877 EXCEPTION
878 WHEN Others THEN
879 wf_core.Context('Wf_Mailer_Parameters', 'ValidINT', pParam, pValue,
880 pResult);
881 raise;
882 END ValidINT;
883
884
885 -- ValidLOG
886 -- Validate a boolean value. Basic rule is that it can not
887 -- be YES/NO
888 -- IN
889 -- Parameter to validate
890 -- Value to validate
891 -- OUT
892 -- Result of the validatation
893 PROCEDURE ValidLOG(pParam IN VARCHAR2, pValue IN VARCHAR2,
894 pResult IN OUT NOCOPY VARCHAR2)
895 IS
896 BEGIN
897 IF upper(pValue) NOT IN ('Y','N','YES','NO') then
898 wf_core.token('PARAMETER', pParam);
899 wf_core.token('VALUE', pvalue);
900 pResult := wf_core.Substitute('WFERR', 'WFMLR_BADBOOL');
901 ELSE
902 pResult := valid_param;
903 END IF;
904 EXCEPTION
905 WHEN Others THEN
906 wf_core.Context('Wf_Mailer_Parameters', 'ValidLOG', pParam, pValue,
907 pResult);
908 raise;
909 END ValidLOG;
910
911
912 -- ValidPROTOCOL
913 -- Validate a protocol
914 -- IN
915 -- Parameter to validate
916 -- Value to validate
917 -- OUT
918 -- Result of the validatation
919 PROCEDURE ValidPROTOCOL(pParam IN VARCHAR2, pValue IN VARCHAR2,
920 pResult IN OUT NOCOPY VARCHAR2)
921 IS
922 BEGIN
923 IF upper(pValue) NOT IN ('POP3','IMAP') then
924 wf_core.token('PARAMETER', pParam);
925 wf_core.token('VALUE', pvalue);
926 pResult := wf_core.Substitute('WFERR', 'WFMLR_BADPROTOCOL');
927 ELSE
928 pResult := valid_param;
929 END IF;
930 EXCEPTION
931 WHEN Others THEN
932 wf_core.Context('Wf_Mailer_Parameters', 'ValidPROTOCOL',
933 pParam, pValue, pResult);
934 raise;
935 END ValidPROTOCOL;
936
937
938 -- ValidSENDARG
939 -- Validate the sendmail arguments
940 -- IN
941 -- Parameter to validate
942 -- Value to validate
943 -- OUT
944 -- Result of the validatation
945 PROCEDURE ValidSENDARG(pParam IN VARCHAR2, pValue IN VARCHAR2,
946 pResult IN OUT NOCOPY VARCHAR2)
947 IS
948 valid integer := 0;
949 BEGIN
950 BEGIN
951 SELECT 1 into valid
952 FROM sys.dual
953 WHERE pValue like '%%%s%-t%-F%"%%s"%<%%%s';
954 EXCEPTION
955 WHEN NO_DATA_FOUND THEN
956 valid := 0;
957 END;
958 IF pValue IS NOT NULL AND VALID = 0 THEN
959 wf_core.token('PARAMETER', pParam);
960 wf_core.token('VALUE', pvalue);
961 pResult := wf_core.Substitute('WFERR', 'WFMLR_BAD_SENDMAILARG');
962 ELSE
963 pResult := valid_param;
964 END IF;
965 EXCEPTION
966 WHEN Others THEN
967 wf_core.Context('Wf_Mailer_Parameters', 'ValidPROTOCOL',
968 pParam, pValue, pResult);
969 raise;
970 END ValidSENDARG;
971
972 --------------------------------------------------------------------------
973 -- GetValueForCorr - To return a parameter value
974 -- IN
975 -- The correlation id for the mailer instance
976 -- The name of the parameter
977 -- RETURNS
978 -- the value of the parameter.
979 FUNCTION GetValueForCorr(pCorrId IN VARCHAR2, pName IN VARCHAR2) RETURN VARCHAR2
980 IS
981
982 l_component_id fnd_svc_components.component_id%TYPE;
983 l_value fnd_svc_comp_param_vals.parameter_value%TYPE;
984 l_component_found boolean;
985 l_corrid varchar2(40);
986
987 CURSOR c_get_components_for_corr is
988 SELECT component_id
989 FROM FND_SVC_COMPONENTS
990 WHERE
991 l_corrid like correlation_id and
992 component_type = 'WF_MAILER'
993 order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
994
995 CURSOR c_get_components_for_null_corr is
996 SELECT component_id
997 FROM FND_SVC_COMPONENTS
998 WHERE
999 correlation_id is null and
1000 component_type = 'WF_MAILER'
1001 order by DECODE(component_status, 'RUNNING', 1, 'NOT_CONFIGURED', 3, 2) ASC ;
1002
1003
1004 BEGIN
1005
1006 -- If correlation id does not contain message name, add : to it
1007 -- in order to get reliable result from SVC component definition
1008 if (pCorrId is not null and instr(pCorrId, ':') = 0) then
1009 l_corrid := pCorrId||':';
1010 else
1011 l_corrid := pCorrId;
1012 end if;
1013
1014 l_component_found := FALSE;
1015
1016 if l_corrid is not null then
1017 for rec_component in c_get_components_for_corr loop
1018 l_component_id := rec_component.component_id;
1019 l_component_found := TRUE;
1020 exit;
1021 end loop;
1022 END IF;
1023
1024 if not l_component_found then
1025 for rec_component_null in c_get_components_for_null_corr loop
1026 l_component_id := rec_component_null.component_id;
1027 l_component_found := TRUE;
1028 exit;
1029 end loop;
1030 END IF;
1031
1032 if l_component_found then
1033 l_value := FND_SVC_COMPONENT.Retrieve_Parameter_Value
1034 (p_parameter_name => pName,
1035 p_component_id => l_component_id);
1036 END IF;
1037
1038 return l_value;
1039
1040 EXCEPTION
1041 when others then
1042 wf_core.Context('Wf_Mailer_Parameters', 'GetValueForCorr',
1043 'CorrId:'||l_corrid, 'Parameter:'||pName);
1044 raise;
1045 END GetValueForCorr;
1046
1047
1048 -- GetValueForCorr - To return a parameter value based on the
1049 -- content of the message attribute of with the
1050 -- name pattern of #WFM_<PARAM>
1051 -- IN
1052 -- The Notification ID
1053 -- The correlation id for the mailer instance
1054 -- The name of the parameter
1055 -- RETURNS
1056 -- the value of the parameter.
1057 FUNCTION GetValueForCorr(pNid IN VARCHAR2, pCorrId IN VARCHAR2,
1058 pName IN VARCHAR2,
1059 pInAttr OUT NOCOPY varchar2) RETURN VARCHAR2
1060 IS
1061
1062 l_value fnd_svc_comp_param_vals.parameter_value%TYPE;
1063
1064 BEGIN
1065 pInAttr := 'N';
1066 begin
1067 l_value := wf_notification.getAttrText(pNid, '#WFM_'||pName);
1068 pInAttr := 'Y';
1069 exception
1070 when others then
1071 if (wf_core.error_name = 'WFNTF_ATTR') then
1072 Wf_Core.Clear;
1073 pInAttr := 'N';
1074 else
1075 raise;
1076 end if;
1077 end;
1078 if (pInAttr = 'Y' and l_value is not null) then
1079 return l_value;
1080 end if;
1081
1082 return getValueForCorr(pCorrId, pName);
1083
1084
1085 EXCEPTION
1086 when others then
1087 wf_core.Context('Wf_Mailer_Parameters', 'GetValueForCorr',
1088 'pNid: '||pNid, 'CorrId:'||pCorrId,
1089 'Parameter:'||pName);
1090 raise;
1091 END GetValueForCorr;
1092
1093 end WF_MAILER_PARAMETER;
[Home] [Help]