[Home] [Help]
PACKAGE BODY: APPS.OKC_ASYNC_PVT
Source
1 package body OKC_ASYNC_PVT as
2 /* $Header: OKCRASNB.pls 120.3 2005/12/14 22:11:36 npalepu noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 G_MUTE_PROFILE CONSTANT VARCHAR2(30) := 'OKC_SUPPRESS_EMAILS';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKC_API.G_APP_NAME;
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKC_ASYNC_PVT';
9 G_LEVEL CONSTANT VARCHAR2(4) := '_PVT';
10 l_api_version CONSTANT NUMBER := 1;
11
12
13 --
14 -- private procedure to be called by wf start api
15 -- to save session context in wf attributes
16 --
17 procedure save_env(p_wf_name varchar2, p_key varchar2) is
18 p_recipient varchar2(100);
19 l_nls_language varchar2(100);
20 l_nls_territory varchar2(100);
21 l_ntf_pref varchar2(30);
22
23 --
24 -- changed to go of users directly JEG 11/30/2000
25 --
26 cursor nls_csr is
27 select
28 NVL(wf_pref.get_pref(USR.USER_NAME, 'LANGUAGE'), FNDL.NLS_LANGUAGE) language,
29 NVL(wf_pref.get_pref(USR.USER_NAME, 'TERRITORY'), FNDL.NLS_TERRITORY) territory,
30 NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),'MAILHTML')
31 notification_preference
32 from fnd_languages fndl,
33 fnd_user usr
34 where usr.user_name = p_recipient
35 and fndl.installed_flag = 'B';
36
37 --
38 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'save_env';
39 --
40
41 begin
42
43 IF (l_debug = 'Y') THEN
44 okc_debug.Set_Indentation(l_proc);
45 okc_debug.Log('10: Entering ',2);
46 okc_debug.Log('10: p_wf_name : '||p_wf_name,2);
47 okc_debug.Log('10: p_key : '||p_key,2);
48 END IF;
49 --
50 -- save apps context
51 --
52 begin
53 wf_engine.SetItemAttrNumber (itemtype => p_wf_name,
54 itemkey => p_key,
55 aname => 'USER_ID',
56 avalue => fnd_global.user_id);
57 IF (l_debug = 'Y') THEN
58 okc_debug.Log('001 in set_env user_id:'||fnd_global.user_id,2);
59 END IF;
60 exception
61 when others then
62 wf_engine.AddItemAttr (itemtype => p_wf_name,
63 itemkey => p_key,
64 aname => 'USER_ID');
65 wf_engine.SetItemAttrNumber(itemtype => p_wf_name,
66 itemkey => p_key,
67 aname => 'USER_ID',
68 avalue => fnd_global.user_id);
69 IF (l_debug = 'Y') THEN
70 okc_debug.Log('002 in set_env user_id:'||fnd_global.user_id,2);
71 END IF;
72 end;
73 begin
74 wf_engine.SetItemAttrNumber (itemtype => p_wf_name,
75 itemkey => p_key,
76 aname => 'RESP_ID',
77 avalue => fnd_global.resp_id);
78 IF (l_debug = 'Y') THEN
79 okc_debug.Log('003 in set_env resp_id:'||fnd_global.resp_id,2);
80 END IF;
81 exception
82 when others then
83 wf_engine.AddItemAttr (itemtype => p_wf_name,
84 itemkey => p_key,
85 aname => 'RESP_ID');
86 wf_engine.SetItemAttrNumber(itemtype => p_wf_name,
87 itemkey => p_key,
88 aname => 'RESP_ID',
89 avalue => fnd_global.resp_id);
90 IF (l_debug = 'Y') THEN
91 okc_debug.Log('004 in set_env resp_id:'||fnd_global.resp_id,2);
92 END IF;
93 end;
94 begin
95 wf_engine.SetItemAttrNumber (itemtype => p_wf_name,
96 itemkey => p_key,
97 aname => 'RESP_APPL_ID',
98 avalue => fnd_global.RESP_APPL_id);
99 IF (l_debug = 'Y') THEN
100 okc_debug.Log('005 in set_env resp_id:'||fnd_global.resp_appl_id,2);
101 END IF;
102 exception
103 when others then
104 wf_engine.AddItemAttr (itemtype => p_wf_name,
105 itemkey => p_key,
106 aname => 'RESP_APPL_ID');
107 wf_engine.SetItemAttrNumber(itemtype => p_wf_name,
108 itemkey => p_key,
109 aname => 'RESP_APPL_ID',
110 avalue => fnd_global.RESP_APPL_id);
111 IF (l_debug = 'Y') THEN
112 okc_debug.Log('006 in set_env resp_id:'||fnd_global.resp_appl_id,2);
113 END IF;
114 end;
115 --
116 -- save NLS/NTF preferences from P_RESOLVER/P_S_RECIPIENT/P_E_RECIPIENT/apps
117 --
118 p_recipient := NVL( wf_engine.GetItemAttrText(p_wf_name,p_key,'P_RESOLVER'),
119 NVL(wf_engine.GetItemAttrText(p_wf_name,p_key,'P_S_RECIPIENT'),
120 wf_engine.GetItemAttrText(p_wf_name,p_key,'P_E_RECIPIENT')));
121 select value into L_NLS_LANGUAGE
122 from NLS_SESSION_PARAMETERS
123 where PARAMETER='NLS_LANGUAGE';
124 select value into L_NLS_TERRITORY
125 from NLS_SESSION_PARAMETERS
126 where PARAMETER='NLS_TERRITORY';
127 open nls_csr;
128 fetch nls_csr into l_nls_language, l_nls_territory, l_ntf_pref;
129 close nls_csr;
130 --
131 if ( l_ntf_pref='MAILTEXT' ) then
132 wf_engine.SetItemAttrText (itemtype => p_wf_name,
133 itemkey => p_key,
134 aname => 'PREFORMAT',
135 avalue => '');
136 wf_engine.SetItemAttrText (itemtype => p_wf_name,
137 itemkey => p_key,
138 aname => 'PREFORMAT_',
139 avalue => '');
140 IF (l_debug = 'Y') THEN
141 okc_debug.Log('007 in set_env l_ntf_pref:'||l_ntf_pref,2);
142 END IF;
143 end if;
144 --
145 begin
146 wf_engine.SetItemAttrText (itemtype => p_wf_name,
147 itemkey => p_key,
148 aname => 'NLS_LANGUAGE',
149 avalue => ''''||l_nls_language||'''');
150 exception
151 when others then
152 wf_engine.AddItemAttr (itemtype => p_wf_name,
153 itemkey => p_key,
154 aname => 'NLS_LANGUAGE');
155 wf_engine.SetItemAttrText(itemtype => p_wf_name,
156 itemkey => p_key,
157 aname => 'NLS_LANGUAGE',
158 avalue => ''''||l_nls_language||'''');
159 end;
160 begin
161 wf_engine.SetItemAttrText (itemtype => p_wf_name,
162 itemkey => p_key,
163 aname => 'NLS_TERRITORY',
164 avalue => ''''||l_nls_territory||'''');
165 exception
166 when others then
167 wf_engine.AddItemAttr (itemtype => p_wf_name,
168 itemkey => p_key,
169 aname => 'NLS_TERRITORY');
170 wf_engine.SetItemAttrText(itemtype => p_wf_name,
171 itemkey => p_key,
172 aname => 'NLS_TERRITORY',
173 avalue => ''''||l_nls_territory||'''');
174 end;
175 begin
176 wf_engine.SetItemAttrText (itemtype => p_wf_name,
177 itemkey => p_key,
178 aname => 'NTF_PREF',
179 avalue => l_ntf_pref);
180 exception
181 when others then
182 wf_engine.AddItemAttr (itemtype => p_wf_name,
183 itemkey => p_key,
184 aname => 'NTF_PREF');
185 wf_engine.SetItemAttrText (itemtype => p_wf_name,
186 itemkey => p_key,
187 aname => 'NTF_PREF',
188 avalue => l_ntf_pref);
189 IF (l_debug = 'Y') THEN
190 okc_debug.Log('008 in set_env l_ntf_pref:'||l_ntf_pref,2);
191 END IF;
192 end;
193
194
195 IF (l_debug = 'Y') THEN
196 okc_debug.Log('1000: Leaving ',2);
197 okc_debug.Log('008 leaving set_env',2);
198 okc_debug.Reset_Indentation;
199 END IF;
200
201 end save_env;
202
203 --
204 -- wf start API (Branch 2)
205 --
206 procedure wf_call(
207 --
208 -- common API parameters
209 --
210 p_api_version IN NUMBER,
211 p_init_msg_list IN VARCHAR2 ,
212 x_return_status OUT NOCOPY VARCHAR2,
213 x_msg_count OUT NOCOPY NUMBER,
214 x_msg_data OUT NOCOPY VARCHAR2,
215 --
216 -- params for dynamic proc call
217 --
218 p_proc IN VARCHAR2 ,
219 p_subj_first_msg IN VARCHAR2 ,
220 --
221 -- notification params
222 --
223 p_ntf_type IN VARCHAR2 ,
224 p_e_recipient IN VARCHAR2 ,
225 p_s_recipient IN VARCHAR2 ,
226 --
227 -- extra wf params (wf attr. / other than 3 previous - i.e. CONTRACT_ID)
228 --
229 p_wf_par_tbl IN par_tbl_typ
230 ) is
231
232 l_api_name CONSTANT VARCHAR2(30) := 'WF_CALL';
233 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
234 l_key varchar2(100);
235 c NUMBER;
236 i NUMBER;
237 j NUMBER;
238 P_VERSION NUMBER := 2;
239 --
240 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'wf_call';
241 --
242
243 BEGIN
244
245 IF (l_debug = 'Y') THEN
246 okc_debug.Set_Indentation(l_proc);
247 okc_debug.Log('10: Entering ',2);
248 okc_debug.Log('20: G_WF_NAME : '||G_WF_NAME,2);
249 okc_debug.Log('20: G_PROCESS_NAME : '||G_PROCESS_NAME,2);
250 okc_debug.Log('20: p_api_version : '||p_api_version,2);
251 okc_debug.Log('20: p_init_msg_list : '||p_init_msg_list,2);
252 okc_debug.Log('20: p_proc : '||p_proc,2);
253 okc_debug.Log('20: p_subj_first_msg : '||p_subj_first_msg,2);
254 okc_debug.Log('20: p_ntf_type : '||p_ntf_type,2);
255 okc_debug.Log('20: p_e_recipient : '||p_e_recipient,2);
256 okc_debug.Log('20: p_s_recipient : '||p_s_recipient,2);
257 END IF;
258
259 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
260 G_PKG_NAME,
261 p_init_msg_list,
262 l_api_version,
263 p_api_version,
264 G_LEVEL,
265 x_return_status);
266 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
267 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
268 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
269 RAISE OKC_API.G_EXCEPTION_ERROR;
270 END IF;
271
272 --
273 -- create process
274 --
275 select to_char(okc_wf_notify_s1.nextval) into l_key from dual;
276
277 IF (l_debug = 'Y') THEN
278 okc_debug.Log('30: l_key : '||l_key,2);
279 END IF;
280
281 wf_engine.CreateProcess( ItemType => G_WF_NAME,
282 ItemKey => L_KEY,
283 process => G_PROCESS_NAME);
284 wf_engine.SetItemUserKey ( ItemType => G_WF_NAME,
285 ItemKey => L_KEY,
286 UserKey => L_KEY);
287 --
288 -- design time attr !
289 --
290 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
291 itemkey => L_KEY,
292 aname => 'P_PROC',
293 avalue => P_PROC);
294 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
295 itemkey => L_KEY,
296 aname => 'P_SUBJ_FIRST_MSG',
297 avalue => P_SUBJ_FIRST_MSG);
298 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
299 itemkey => L_KEY,
300 aname => 'P_NTF_TYPE',
301 avalue => P_NTF_TYPE);
302 --
303 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
304 itemkey => L_KEY,
305 aname => 'P_E_RECIPIENT',
306 avalue => P_E_RECIPIENT);
307 --
308 if (p_proc is NULL and p_s_recipient is NULL) then
309 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
310 itemkey => L_KEY,
311 aname => 'P_S_RECIPIENT',
312 avalue => fnd_global.user_name);
313 IF (l_debug = 'Y') THEN
314 okc_debug.Log('009 in wf_call p_s_recipient :'||fnd_global.user_name,2);
315 END IF;
316 else
317 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
318 itemkey => L_KEY,
319 aname => 'P_S_RECIPIENT',
320 avalue => P_S_RECIPIENT);
321 IF (l_debug = 'Y') THEN
322 okc_debug.Log('010 in wf_call p_s_recipient :'||P_S_RECIPIENT,2);
323 END IF;
324 end if;
325 --
326 -- design time attr ! other wf parameters
327 --
328 c := p_wf_par_tbl.COUNT;
329
330 IF (l_debug = 'Y') THEN
331 okc_debug.Log('40: p_wf_par_tbl.COUNT : '||c,2);
332 END IF;
333
334 if (c>0) then
335 i := p_wf_par_tbl.FIRST;
336 LOOP
337 IF (l_debug = 'Y') THEN
338 okc_debug.Log('50: Inside Loop ',2);
339 END IF;
340 if ( (p_wf_par_tbl(i).par_type is NULL) or (p_wf_par_tbl(i).par_type = 'C') ) then
341 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
342 itemkey => L_KEY,
343 aname => p_wf_par_tbl(i).par_name,
344 avalue => p_wf_par_tbl(i).par_value);
345 IF (l_debug = 'Y') THEN
346 okc_debug.Log('60: p_wf_par_tbl(i).par_name : '||p_wf_par_tbl(i).par_name,2);
347 okc_debug.Log('60: p_wf_par_tbl(i).par_value : '||p_wf_par_tbl(i).par_value,2);
348 END IF;
349 if (p_wf_par_tbl(i).par_name = 'P_DOC_PROC') then
350 P_VERSION := 4;
351 IF (l_debug = 'Y') THEN
352 okc_debug.Log('60: P_VERSION := 4 ',2);
353 END IF;
354 end if;
355 elsif (p_wf_par_tbl(i).par_type = 'N') then
356 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
357 itemkey => L_KEY,
358 aname => p_wf_par_tbl(i).par_name,
359 avalue => to_number(p_wf_par_tbl(i).par_value));
360 IF (l_debug = 'Y') THEN
361 okc_debug.Log('70: p_wf_par_tbl(i).par_name : '||p_wf_par_tbl(i).par_name,2);
362 okc_debug.Log('70: p_wf_par_tbl(i).par_value : '||p_wf_par_tbl(i).par_value,2);
363 END IF;
364 elsif (p_wf_par_tbl(i).par_type = 'D') then
365 wf_engine.SetItemAttrDate (itemtype => G_WF_NAME,
366 itemkey => L_KEY,
367 aname => p_wf_par_tbl(i).par_name,
368 avalue => to_date(p_wf_par_tbl(i).par_value,'YYYY/MM/DD'));
369 IF (l_debug = 'Y') THEN
370 okc_debug.Log('80: p_wf_par_tbl(i).par_name : '||p_wf_par_tbl(i).par_name,2);
371 okc_debug.Log('80: p_wf_par_tbl(i).par_value : '||p_wf_par_tbl(i).par_value,2);
372 END IF;
373 end if;
374 c := c-1;
375 EXIT WHEN (c=0);
376 i := p_wf_par_tbl.NEXT(i);
377 END LOOP;
378 IF (l_debug = 'Y') THEN
379 okc_debug.Log('90: out nocopy of Loop ',2);
380 END IF;
381 end if;
382 --
383 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
384 itemkey => L_KEY,
385 aname => 'P_KEY',
386 avalue => L_KEY);
387 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
388 itemkey => L_KEY,
389 aname => 'P_VERSION',
390 avalue => P_VERSION);
391
392 --
393 -- end wf parameters
394 --
395
396 --
397 -- save env / start
398 --
399 save_env(G_WF_NAME, l_key);
400 wf_engine.SetItemOwner ( itemtype => G_WF_NAME,
401 itemkey => L_KEY,
402 owner => fnd_global.user_name);
403
404 IF (l_debug = 'Y') THEN
405 okc_debug.Log('100: WF Owner : '||fnd_global.user_name,2);
406 END IF;
407
408 wf_engine.StartProcess( itemtype => G_WF_NAME,
409 itemkey => L_KEY);
410
411 IF (l_debug = 'Y') THEN
412 okc_debug.Log('110: Started WF ',2);
413 END IF;
414
415 -- commit;
416 x_return_status := OKC_API.G_RET_STS_SUCCESS;
417 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
418
419 IF (l_debug = 'Y') THEN
420 okc_debug.Log('120: x_msg_count : '||x_msg_count,2);
421 okc_debug.Log('130: x_msg_data : '||x_msg_data,2);
422 END IF;
423
424 IF (l_debug = 'Y') THEN
425 okc_debug.Log('1000: Leaving ',2);
426 okc_debug.Reset_Indentation;
427 END IF;
428
429 EXCEPTION
430 WHEN OKC_API.G_EXCEPTION_ERROR THEN
431 x_return_status := OKC_API.HANDLE_EXCEPTIONS
432 (substr(l_api_name,1,26),
433 G_PKG_NAME,
434 'OKC_API.G_RET_STS_ERROR',
435 x_msg_count,
436 x_msg_data,
437 G_LEVEL);
438 IF (l_debug = 'Y') THEN
439 okc_debug.Log('3000: Leaving ',2);
440 okc_debug.Reset_Indentation;
441 END IF;
442 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
443 x_return_status := OKC_API.HANDLE_EXCEPTIONS
444 (substr(l_api_name,1,26),
445 G_PKG_NAME,
446 'OKC_API.G_RET_STS_UNEXP_ERROR',
447 x_msg_count,
448 x_msg_data,
449 G_LEVEL);
450 IF (l_debug = 'Y') THEN
451 okc_debug.Log('4000: Leaving ',2);
452 okc_debug.Reset_Indentation;
453 END IF;
454 WHEN OTHERS THEN
455 x_return_status := OKC_API.HANDLE_EXCEPTIONS
456 (substr(l_api_name,1,26),
457 G_PKG_NAME,
458 'OTHERS',
459 x_msg_count,
460 x_msg_data,
461 G_LEVEL);
462 IF (l_debug = 'Y') THEN
463 okc_debug.Log('5000: Leaving ',2);
464 okc_debug.Reset_Indentation;
465 END IF;
466 end wf_call;
467
468 --
469 -- set_env - private procedure to be called by Selector
470 --
471 procedure set_env( p_wf_name varchar2,
472 p_key varchar2) is
473 l_nls_language varchar2(100);
474 l_nls_territory varchar2(100);
475 l1 varchar2(100);
476 cursor c1(p varchar2) is
477 select ''''||value||''''
478 from NLS_SESSION_PARAMETERS
479 where PARAMETER=p;
480 l_version number;
481 --
482 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'set_env';
483 --
484 begin
485
486 IF (l_debug = 'Y') THEN
487 okc_debug.Set_Indentation(l_proc);
488 okc_debug.Log('10: Entering ',2);
489 END IF;
490
491 IF (l_debug = 'Y') THEN
492 okc_debug.Log('20: p_wf_name : '||p_wf_name,2);
493 okc_debug.Log('20: p_key : '||p_key,2);
494 END IF;
495 --
496 -- do nothing for previous version
497 --
498 begin
499 l_version := wf_engine.GetItemAttrNumber(p_wf_name,p_key,'P_VERSION');
500 exception
501 when others then
502 l_version := 1;
503 end;
504 if (l_version < 2) then
505
506 IF (l_debug = 'Y') THEN
507 okc_debug.Log('100: Leaving ',2);
508 okc_debug.Reset_Indentation;
509 END IF;
510
511 return;
512 end if;
513 --
514 -- set apps context
515 --
516 fnd_global.apps_initialize
517 (
518 user_id =>
519 wf_engine.GetItemAttrNumber(p_wf_name,p_key,'USER_ID'),
520 resp_id =>
521 wf_engine.GetItemAttrNumber(p_wf_name,p_key,'RESP_ID'),
522 resp_appl_id =>
523 wf_engine.GetItemAttrNumber(p_wf_name,p_key,'RESP_APPL_ID')
524 );
525 IF (l_debug = 'Y') THEN
526 okc_debug.Log('100-11: Printing Apps Context in okc_asynch_pvt.set_env after call to fnd_global.apps_initialize...',2);
527 okc_debug.Log('100-12: USER_ID = '|| to_char(fnd_global.user_id),2);
528 okc_debug.Log('100-13: RESP_ID = '|| to_char(fnd_global.resp_id),2);
529 okc_debug.Log('100-14: RESP_APPL_ID = '|| to_char(fnd_global.resp_appl_id),2);
530 END IF;
531
532
533 --
534 -- set nls context if different
535 --
536 l_nls_language := wf_engine.GetItemAttrText(p_wf_name,p_key,'NLS_LANGUAGE');
537 l_nls_territory := wf_engine.GetItemAttrText(p_wf_name,p_key,'NLS_TERRITORY');
538 open c1('NLS_LANGUAGE');
539 fetch c1 into L1;
540 close c1;
541 if (L1<>l_nls_language) then
542 sys.dbms_session.set_nls('NLS_LANGUAGE',l_nls_language);
543 end if;
544 open c1('NLS_TERRITORY');
545 fetch c1 into L1;
546 close c1;
547 if (L1<>l_nls_territory) then
548 sys.dbms_session.set_nls('NLS_TERRITORY',l_nls_territory);
549 end if;
550
551 IF (l_debug = 'Y') THEN
552 okc_debug.Log('1000: Leaving ',2);
553 okc_debug.Reset_Indentation;
554 END IF;
555
556 end set_env;
557
558 --
559 -- Selector sets environment for version > 1
560 --
561 procedure Selector ( item_type in varchar2,
562 item_key in varchar2,
563 activity_id in number,
564 command in varchar2,
565 resultout out nocopy varchar2 ) is
566 -- local declarations
567 --
568 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'Selector';
569 --
570 begin
571
572 IF (l_debug = 'Y') THEN
573 okc_debug.Set_Indentation(l_proc);
574 okc_debug.Log('10: Entering ',2);
575 END IF;
576
577 IF (l_debug = 'Y') THEN
578 okc_debug.Log('20: item_type : '||item_type,2);
579 okc_debug.Log('20: item_key : '||item_key,2);
580 okc_debug.Log('20: activity_id : '||activity_id,2);
581 okc_debug.Log('20: command : '||command,2);
582 END IF;
583
584 resultout := ''; -- return value for other possible modes
585 --
586 -- RUN mode - normal process execution
587 --
588 if (command = 'RUN') then
589 --
590 -- Return process to run
591 --
592 resultout := G_PROCESS_NAME;
593
594 IF (l_debug = 'Y') THEN
595 okc_debug.Log('30: resultout : '||resultout,2);
596 okc_debug.Log('100: Leaving ',2);
597 okc_debug.Reset_Indentation;
598 END IF;
599
600 return;
601 end if;
602 IF (l_debug = 'Y') THEN
603 okc_debug.Log('100-15: Inside Selector selector/callback mode : '||command,2);
604 okc_debug.Log('100-16: Inside Selector resultout is : '||resultout,2);
605 END IF;
606
607 --
608 -- SET_CTX mode - set context for new DB session
609 --
610 if (command = 'SET_CTX') then
611 set_env(p_wf_name => item_type,p_key => item_key);
612
613 IF (l_debug = 'Y') THEN
614 okc_debug.Log('100-17: Inside Selector if mode is SET_CTX resultout is : '||resultout,2);
615 okc_debug.Log('200: Leaving ',2);
616 okc_debug.Reset_Indentation;
617 END IF;
618
619
620 return;
621 end if;
622
623 --
624 -- TEST_CTX mode - test context
625 --
626 if (command = 'TEST_CTX') then
627 -- test code
628 -- Bug#2909586 Changed resultout to FALSE so that wworkflow will always set the context
629 resultout := 'FALSE';
630
631 IF (l_debug = 'Y') THEN
632 okc_debug.Log('100-18: Inside Selector if mode is TEST_CTX resultout is : '||resultout,2);
633 okc_debug.Log('40: resultout : '||resultout,2);
634 okc_debug.Log('300: Leaving ',2);
635 okc_debug.Reset_Indentation;
636 END IF;
637
638
639 return;
640 end if;
641
642 IF (l_debug = 'Y') THEN
643 okc_debug.Log('1000: Leaving ',2);
644 okc_debug.Reset_Indentation;
645 END IF;
646
647 exception
648 when others then
649 wf_core.context(G_PKG_NAME,
650 'SELECTOR',
651 item_type,
652 item_key,
653 to_char(activity_id),
654 command);
655 IF (l_debug = 'Y') THEN
656 okc_debug.Log('100-19: Inside Selector exception resultout is : '||resultout,2);
657 okc_debug.Log('2000: Leaving ',2);
658 okc_debug.Reset_Indentation;
659 END IF;
660 raise;
661 end Selector;
662
663 --
664 -- get_version returns '1' for previous wf branch, '2' for new
665 --
666 procedure get_version( itemtype in varchar2,
667 itemkey in varchar2,
668 actid in number,
669 funcmode in varchar2,
670 resultout out nocopy varchar2 ) is
671 P_VERSION varchar2(6) := '1';
672 --
673 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'get_version';
674 --
675
676 begin
677
678 IF (l_debug = 'Y') THEN
679 okc_debug.Set_Indentation(l_proc);
680 okc_debug.Log('10: Entering ',2);
681 END IF;
682
683 IF (l_debug = 'Y') THEN
684 okc_debug.Log('20: itemtype : '||itemtype,2);
685 okc_debug.Log('20: itemkey : '||itemkey,2);
686 okc_debug.Log('20: actid : '||actid,2);
687 okc_debug.Log('20: funcmode : '||funcmode,2);
688 END IF;
689 --
690 -- RUN mode - normal process execution
691 --
692 if (funcmode = 'RUN') then
693 begin
694 P_VERSION := to_char(wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_VERSION'));
695 IF (l_debug = 'Y') THEN
696 okc_debug.Log('30: P_VERSION : '||P_VERSION,2);
697 END IF;
698 exception
699 when others then
700 P_VERSION := '1';
701 end;
702 resultout := 'COMPLETE:'||P_VERSION;
703 IF (l_debug = 'Y') THEN
704 okc_debug.Log('40: resultout : '||resultout,2);
705 END IF;
706 end if;
707 --
708 -- CANCEL mode
709 --
710 if (funcmode = 'CANCEL') then
711 --
712 resultout := 'COMPLETE:';
713
714 IF (l_debug = 'Y') THEN
715 okc_debug.Log('50: resultout : '||resultout,2);
716 okc_debug.Log('100: Leaving ',2);
717 okc_debug.Reset_Indentation;
718 END IF;
719
720
721 return;
722 --
723 end if;
724 --
725 -- TIMEOUT mode
726 --
727 if (funcmode = 'TIMEOUT') then
728 --
729 resultout := 'COMPLETE:';
730
731 IF (l_debug = 'Y') THEN
732 okc_debug.Log('60: resultout : '||resultout,2);
733 okc_debug.Log('200: Leaving ',2);
734 okc_debug.Reset_Indentation;
735 END IF;
736
737
738 return;
739 --
740 end if;
741
742 IF (l_debug = 'Y') THEN
743 okc_debug.Log('1000: Leaving ',2);
744 okc_debug.Reset_Indentation;
745 END IF;
746
747 exception
748 when others then
749 wf_core.context(G_PKG_NAME,
750 'GET_VERSION',
751 itemtype,
752 itemkey,
753 to_char(actid),
754 funcmode);
755 IF (l_debug = 'Y') THEN
756 okc_debug.Log('2000: Leaving ',2);
757 okc_debug.Reset_Indentation;
758 END IF;
759 raise;
760 end get_version;
761
762 --
763 -- proc_call private procedure that
764 -- executes dynamic sql comming from attribute p_proc/p_accept_proc/...
765 --
766 procedure proc_call(
767 p_key varchar2,
768 p_attr_name varchar2,
769 x_return_status IN OUT NOCOPY VARCHAR2
770 ) is
771 j number;
772 P_PROC varchar2(8000);
773 P_SUBJ_FIRST_MSG varchar2(1);
774 P_VERSION number;
775 --
776 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'proc_call';
777 --
778 begin
779
780 IF (l_debug = 'Y') THEN
781 okc_debug.Set_Indentation(l_proc);
782 okc_debug.Log('10: Entering ',2);
783 END IF;
784
785 IF (l_debug = 'Y') THEN
786 okc_debug.Log('20: p_key : '||p_key,2);
787 okc_debug.Log('20: p_attr_name : '||p_attr_name,2);
788 END IF;
789
790 x_return_status := 'S';
791 P_PROC := wf_engine.GetItemAttrText (itemtype => G_WF_NAME,
792 itemkey => P_KEY,
793 aname => p_attr_name);
794 -- marat start (bug#2477385)
795 okc_wf.init_wf_string( wf_engine.GetItemAttrText ( itemtype => G_WF_NAME,
796 itemkey => P_KEY,
797 aname => 'EXTRA_ATTR_TEXT'));
798 -- marat end
799 P_VERSION := wf_engine.GetItemAttrNumber (itemtype => G_WF_NAME,
800 itemkey => P_KEY,
801 aname => 'P_VERSION');
802 savepoint exec_call_sp;
803 if (P_VERSION in (2,5)) then
804 begin
805 EXECUTE IMMEDIATE P_PROC USING IN OUT x_return_status;
806 exception
807 when others then
808 IF (l_debug = 'Y') THEN
809 okc_debug.Log('011 in proc_call exception block:'||substr(sqlerrm,1,200),2);
810 END IF;
811 begin
812 rollback to exec_call_sp;
813 EXECUTE IMMEDIATE P_PROC;
814 exception when others then
815 x_return_status := 'U';
816 end;
817 end;
818 IF (x_return_status in ('E','U')) then
819 rollback to exec_call_sp;
820 end if;
821 elsif (P_VERSION = 3) then
822 begin
823 EXECUTE IMMEDIATE P_PROC USING
824 wf_engine.GetItemAttrText (
825 itemtype => G_WF_NAME,
826 itemkey => P_KEY,
827 aname => 'NOTE');
828 EXCEPTION WHEN OTHERS THEN
829 begin
830 rollback to exec_call_sp;
831 EXECUTE IMMEDIATE P_PROC;
832 exception when others then
833 rollback to exec_call_sp;
834 end;
835 end;
836 end if;
837
838 IF (l_debug = 'Y') THEN
839 okc_debug.Log('1000: Leaving ',2);
840 okc_debug.Reset_Indentation;
841 END IF;
842
843 end proc_call;
844
845 -- Procedure Name : load_mess
846 -- Description : Private procedure to load messages into attributes
847 -- to be called by nun_generic
848
849 procedure load_mess(itemtype in varchar2,
850 itemkey in varchar2,
851 --NPALEPU
852 --14-DEC-2005
853 --Bug # 4699009
854 p_proc_success_flag IN VARCHAR2
855 --END NPALEPU
856 ) is
857 i integer; -- fnd_message counter
858 j integer; -- fnd_message number
859 k integer; -- MESSAGE counter
860 P_SUBJ_FIRST_MSG varchar2(1);
861 msg_buf varchar2(4000);
862 fnd_buf varchar2(2000);
863 nl varchar2(4);
864 text_limit number := 32000;
865 --
866 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'load_mess';
867 --
868 --NPALEPU
869 --14-DEC-2005
870 --for bug # 4699009
871 l_contract_id NUMBER;
872 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
873 l_proc_name VARCHAR2(4000);
874
875 CURSOR l_contract_number_csr(contract_id NUMBER) IS
876 SELECT CONTRACT_NUMBER
877 FROM OKC_K_HEADERS_ALL_B
878 WHERE ID = contract_id;
879
880 --END NPALEPU
881 begin
882
883 IF (l_debug = 'Y') THEN
884 okc_debug.Set_Indentation(l_proc);
885 okc_debug.Log('10: Entering ',2);
886 END IF;
887
888 nl:=fnd_global.NewLine;
889 P_SUBJ_FIRST_MSG := wf_engine.GetItemAttrText(itemtype,itemkey,'P_SUBJ_FIRST_MSG');
890 j := NVL(FND_MSG_PUB.Count_Msg,0);
891
892 if (j=0) then
893
894 --NPALEPU
895 --14-DEC-2005
896 --bug # 4699009
897
898 IF (l_debug = 'Y') THEN
899 okc_debug.Log('50: Message Count is Zero ',2);
900 okc_debug.Reset_Indentation;
901 END IF;
902
903 l_contract_id := (wf_engine.GetItemAttrText (itemtype => itemtype,
904 itemkey => itemkey,
905 aname => 'CONTRACT_ID'));
906 l_proc_name := (wf_engine.GetItemAttrText (itemtype => itemtype,
907 itemkey => itemkey,
908 aname => 'P_PROC_NAME'));
909 IF l_contract_id IS NOT NULL THEN
910 BEGIN
911 OPEN l_contract_number_csr(l_contract_id);
912 FETCH l_contract_number_csr INTO l_contract_number;
913 CLOSE l_contract_number_csr;
914 EXCEPTION
915 WHEN OTHERS THEN
916 l_contract_number := NULL;
917 END;
918 END IF;
919
920 IF p_proc_success_flag = 'S' THEN
921 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
922 p_msg_name => 'OKC_OUTCOME_SUCCESS',
923 p_token1 => 'CONTRACT_NUMBER',
924 p_token1_value => l_contract_number,
925 p_token2 => 'PROCESS_NAME',
926 p_token2_value => l_proc_name);
927
928 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
929 p_msg_name => 'OKC_OUTCOME_SUCCESS',
930 p_token1 => 'CONTRACT_NUMBER',
931 p_token1_value => l_contract_number,
932 p_token2 => 'PROCESS_NAME',
933 p_token2_value => l_proc_name);
934 ELSE
935 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
936 p_msg_name => 'OKC_OUTCOME_FAILURE',
937 p_token1 => 'CONTRACT_NUMBER',
938 p_token1_value => l_contract_number,
939 p_token2 => 'PROCESS_NAME',
940 p_token2_value => l_proc_name);
941
942 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
943 p_msg_name => 'OKC_OUTCOME_FAILURE',
944 p_token1 => 'CONTRACT_NUMBER',
945 p_token1_value => l_contract_number,
946 p_token2 => 'PROCESS_NAME',
947 p_token2_value => l_proc_name);
948 END IF;
949
950 wf_engine.SetItemAttrText (itemtype => itemtype,
951 itemkey => itemkey,
952 aname => 'MESSAGE0',
953 avalue => FND_MSG_PUB.Get(2,p_encoded =>FND_API.G_FALSE ));
954
955 wf_engine.SetItemAttrText (itemtype => itemtype,
956 itemkey => itemkey,
957 aname => 'MESSAGE1',
958 avalue => FND_MSG_PUB.Get(1,p_encoded =>FND_API.G_FALSE ));
959 --END NPALEPU
960
961 IF (l_debug = 'Y') THEN
962 okc_debug.Log('100: Leaving ',2);
963 okc_debug.Reset_Indentation;
964 END IF;
965 return;
966
967 end if;
968
969 if (P_SUBJ_FIRST_MSG = 'T') then
970 wf_engine.SetItemAttrText (itemtype => itemtype,
971 itemkey => itemkey,
972 aname => 'MESSAGE0',
973 avalue => FND_MSG_PUB.Get(1,p_encoded =>FND_API.G_FALSE ));
974 i:=2;
975 else
976 wf_engine.SetItemAttrText (itemtype => itemtype,
977 itemkey => itemkey,
978 aname => 'MESSAGE0',
979 avalue => FND_MSG_PUB.Get(j,p_encoded =>FND_API.G_FALSE ));
980 j:=j-1;
981 i:=1;
982 end if;
983 text_limit:=text_limit-length(wf_engine.GetItemAttrText (itemtype => itemtype,
984 itemkey => itemkey,
985 aname => 'MESSAGE0'));
986 k:=1; -- msg index
987 LOOP
988 if (i>j) then exit; end if;
989 msg_buf:='';
990 LOOP
991 fnd_buf := FND_MSG_PUB.Get(i,p_encoded =>FND_API.G_FALSE );
992 if (length(msg_buf)+4+length(fnd_buf))>4000 then
993 exit;
994 end if;
995 if msg_buf is NULL then
996 msg_buf := fnd_buf;
997 else
998 msg_buf := msg_buf||nl||fnd_buf;
999 end if;
1000 i:=i+1;
1001 if (i>j) then exit; end if;
1002 END LOOP;
1003 wf_engine.SetItemAttrText (itemtype => itemtype,
1004 itemkey => itemkey,
1005 aname => 'MESSAGE'||to_char(k),
1006 avalue => msg_buf);
1007 text_limit:=text_limit-length(msg_buf);
1008 if (text_limit<0) then
1009 wf_engine.SetItemAttrText (itemtype => itemtype,
1010 itemkey => itemkey,
1011 aname => 'MESSAGE'||to_char(k),
1012 avalue => '');
1013 exit;
1014 end if;
1015 k := k+1;
1016 if (k>9) then exit; end if;
1017 END LOOP;
1018 end;
1019
1020
1021 --
1022 -- procedure fun_generic
1023 -- returns 'S' if notify about success
1024 -- returns 'E' if notify about error
1025 -- returns 'X' if noone to notify
1026 --
1027 --
1028 procedure fun_generic( itemtype in varchar2,
1029 itemkey in varchar2,
1030 actid in number,
1031 funcmode in varchar2,
1032 resultout out nocopy varchar2 ) is
1033 x_return_status varchar2(1);
1034 l_subj_first varchar2(1);
1035 --
1036 p_recipient varchar2(100);
1037 l_pref varchar2(100);
1038 l_timeout_minutes NUMBER := 0;
1039
1040 --
1041 -- changed to go of users directly JEG 11/30/2000
1042 --
1043 cursor pref_csr is
1044 select
1045 NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),'MAILHTML')
1046 notification_preference
1047 from fnd_user usr
1048 where usr.user_name = p_recipient;
1049
1050 --
1051 --
1052 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'fun_generic';
1053 --
1054 begin
1055
1056 IF (l_debug = 'Y') THEN
1057 okc_debug.Set_Indentation(l_proc);
1058 okc_debug.Log('10: Entering ',2);
1059 END IF;
1060 --
1061 -- RUN mode - normal process execution
1062 --
1063 /* MSENGUPT: Introduced the following code to re-read and reset the profile option in case of timeouts due to errors */
1064 if (funcmode <> 'RUN') then
1065 l_timeout_minutes := fnd_profile.value('OKC_ALERT_TIMEOUT');
1066 if l_timeout_minutes IS NOT NULL then
1067 wf_engine.SetItemAttrNumber (itemtype, itemkey, 'P_TIMEOUT_MINUTES', L_TIMEOUT_MINUTES);
1068 end if;
1069 end if;
1070 /* End of code insert */
1071
1072 if (funcmode = 'RUN') then
1073 IF (l_debug = 'Y') THEN
1074 okc_debug.Log('20: funcmode = RUN ',2);
1075 END IF;
1076 FND_MSG_PUB.initialize;
1077 IF (l_debug = 'Y') THEN
1078 okc_debug.Log('30: ',2);
1079 END IF;
1080 if (wf_engine.GetItemAttrText(itemtype,itemkey,'P_PROC') is NULL) then
1081 IF (l_debug = 'Y') THEN
1082 okc_debug.Log('40 ',2);
1083 END IF;
1084 resultout := 'COMPLETE:S';
1085 else
1086 if (wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_VERSION') = 5) then
1087 wf_engine.SetItemAttrText (
1088 itemtype => itemtype,
1089 itemkey => itemkey,
1090 aname => 'MESSAGE0',
1091 avalue => '');
1092 wf_engine.SetItemAttrText (
1093 itemtype => itemtype,
1094 itemkey => itemkey,
1095 aname => 'MESSAGE1',
1096 avalue => '');
1097 wf_engine.SetItemAttrText (
1098 itemtype => itemtype,
1099 itemkey => itemkey,
1100 aname => 'MESSAGE2',
1101 avalue => '');
1102 wf_engine.SetItemAttrText (
1103 itemtype => itemtype,
1104 itemkey => itemkey,
1105 aname => 'MESSAGE3',
1106 avalue => '');
1107 wf_engine.SetItemAttrText (
1108 itemtype => itemtype,
1109 itemkey => itemkey,
1110 aname => 'MESSAGE4',
1111 avalue => '');
1112 wf_engine.SetItemAttrText (
1113 itemtype => itemtype,
1114 itemkey => itemkey,
1115 aname => 'MESSAGE5',
1116 avalue => '');
1117 wf_engine.SetItemAttrText (
1118 itemtype => itemtype,
1119 itemkey => itemkey,
1120 aname => 'MESSAGE6',
1121 avalue => '');
1122 wf_engine.SetItemAttrText (
1123 itemtype => itemtype,
1124 itemkey => itemkey,
1125 aname => 'MESSAGE7',
1126 avalue => '');
1127 wf_engine.SetItemAttrText (
1128 itemtype => itemtype,
1129 itemkey => itemkey,
1130 aname => 'MESSAGE8',
1131 avalue => '');
1132 wf_engine.SetItemAttrText (
1133 itemtype => itemtype,
1134 itemkey => itemkey,
1135 aname => 'MESSAGE9',
1136 avalue => '');
1137 end if;
1138 proc_call(p_key => itemkey,p_attr_name => 'P_PROC',
1139 x_return_status => x_return_status);
1140 IF (l_debug = 'Y') THEN
1141 okc_debug.Log('50 ',2);
1142 END IF;
1143 if ((x_return_status = 'S') and
1144 (wf_engine.GetItemAttrText(itemtype,itemkey,'P_S_RECIPIENT') is not NULL)) then
1145 --NPALEPU
1146 --14-DEC-2005
1147 --For bug # 4699009
1148 /* load_mess(itemtype,itemkey); */
1149 load_mess(itemtype,itemkey,x_return_status);
1150 --END NPALEPU
1151 --
1152 p_recipient := wf_engine.GetItemAttrText(itemtype,itemkey,'P_S_RECIPIENT');
1153 IF (l_debug = 'Y') THEN
1154 okc_debug.Log('60 ',2);
1155 END IF;
1156 open pref_csr;
1157 fetch pref_csr into l_pref;
1158 close pref_csr;
1159
1160 IF (l_debug = 'Y') THEN
1161 okc_debug.Log('70 l_pref : '||l_pref);
1162 END IF;
1163
1164 if (l_pref='MAILTEXT') then
1165 wf_engine.SetItemAttrText (itemtype => itemtype,
1166 itemkey => itemkey,
1167 aname => 'PREFORMAT',
1168 avalue => '');
1169 wf_engine.SetItemAttrText (itemtype => itemtype,
1170 itemkey => itemkey,
1171 aname => 'PREFORMAT_',
1172 avalue => '');
1173 end if;
1174 --
1175 resultout := 'COMPLETE:S';
1176 elsif ((x_return_status in ('E','U')) and
1177 (wf_engine.GetItemAttrText(itemtype,itemkey,'P_E_RECIPIENT') is not NULL)) then
1178 --NPALEPU
1179 --14-DEC-2005
1180 --For bug # 4699009
1181 /* load_mess(itemtype,itemkey); */
1182 load_mess(itemtype,itemkey,x_return_status);
1183 --END NPALEPU
1184 --
1185 p_recipient := wf_engine.GetItemAttrText(itemtype,itemkey,'P_E_RECIPIENT');
1186 open pref_csr;
1187 fetch pref_csr into l_pref;
1188 close pref_csr;
1189
1190 IF (l_debug = 'Y') THEN
1191 okc_debug.Log('80 l_pref : '||l_pref);
1192 END IF;
1193
1194 if (l_pref='MAILTEXT') then
1195 wf_engine.SetItemAttrText (itemtype => itemtype,
1196 itemkey => itemkey,
1197 aname => 'PREFORMAT',
1198 avalue => '');
1199 wf_engine.SetItemAttrText (itemtype => itemtype,
1200 itemkey => itemkey,
1201 aname => 'PREFORMAT_',
1202 avalue => '');
1203 end if;
1204 --
1205 resultout := 'COMPLETE:E';
1206 else
1207 resultout := 'COMPLETE:X';
1208 end if;
1209 IF (l_debug = 'Y') THEN
1210 okc_debug.Log('81: ',2);
1211 END IF;
1212
1213 end if;
1214 IF (l_debug = 'Y') THEN
1215 okc_debug.Log('82: ',2);
1216 END IF;
1217
1218 end if;
1219 IF (l_debug = 'Y') THEN
1220 okc_debug.Log('83: ',2);
1221 END IF;
1222 --
1223 -- CANCEL mode
1224 --
1225
1226 if (funcmode = 'CANCEL') then
1227 --
1228 IF (l_debug = 'Y') THEN
1229 okc_debug.Log('90 funcmode = CANCEL',2);
1230 END IF;
1231
1232 resultout := 'COMPLETE:';
1233
1234 IF (l_debug = 'Y') THEN
1235 okc_debug.Log('100: Leaving ',2);
1236 okc_debug.Reset_Indentation;
1237 END IF;
1238
1239 return;
1240 --
1241 end if;
1242 --
1243 -- TIMEOUT mode
1244 --
1245 if (funcmode = 'TIMEOUT') then
1246 --
1247 IF (l_debug = 'Y') THEN
1248 okc_debug.Log('110 funcmode = TIMEOUT',2);
1249 END IF;
1250
1251 resultout := 'COMPLETE:';
1252
1253 IF (l_debug = 'Y') THEN
1254 okc_debug.Log('200: Leaving ',2);
1255 okc_debug.Reset_Indentation;
1256 END IF;
1257
1258 return;
1259 --
1260 end if;
1261
1262 IF (l_debug = 'Y') THEN
1263 okc_debug.Log('1000: Leaving ',2);
1264 okc_debug.Reset_Indentation;
1265 END IF;
1266
1267 exception
1268 when others then
1269 wf_core.context(G_PKG_NAME,
1270 'FUN_GENERIC',
1271 itemtype,
1272 itemkey,
1273 to_char(actid),
1274 funcmode);
1275 IF (l_debug = 'Y') THEN
1276 okc_debug.Log('2000: Leaving ',2);
1277 okc_debug.Reset_Indentation;
1278 END IF;
1279 raise;
1280 end fun_generic;
1281
1282 --
1283 -- accept
1284 --
1285 procedure accept( itemtype in varchar2,
1286 itemkey in varchar2,
1287 actid in number,
1288 funcmode in varchar2,
1289 resultout out nocopy varchar2) is
1290 x_return_status varchar2(1);
1291 --
1292 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'accept';
1293 --
1294 begin
1295
1296 IF (l_debug = 'Y') THEN
1297 okc_debug.Set_Indentation(l_proc);
1298 okc_debug.Log('10: Entering ',2);
1299 END IF;
1300 --
1301 -- RUN mode - normal process execution
1302 --
1303 if (funcmode = 'RUN') then
1304 --
1305 proc_call(p_key => itemkey,p_attr_name => 'P_ACCEPT_PROC',
1306 x_return_status => x_return_status);
1307 resultout := 'COMPLETE:';
1308
1309 IF (l_debug = 'Y') THEN
1310 okc_debug.Log('100: Leaving ',2);
1311 okc_debug.Reset_Indentation;
1312 END IF;
1313
1314 return;
1315 --
1316 end if;
1317 --
1318 -- CANCEL mode
1319 --
1320 if (funcmode = 'CANCEL') then
1321 --
1322 resultout := 'COMPLETE:';
1323
1324 IF (l_debug = 'Y') THEN
1325 okc_debug.Log('200: Leaving ',2);
1326 okc_debug.Reset_Indentation;
1327 END IF;
1328
1329 return;
1330 --
1331 end if;
1332 --
1333 -- TIMEOUT mode
1334 --
1335 if (funcmode = 'TIMEOUT') then
1336 --
1337 resultout := 'COMPLETE:';
1338
1339 IF (l_debug = 'Y') THEN
1340 okc_debug.Log('300: Leaving ',2);
1341 okc_debug.Reset_Indentation;
1342 END IF;
1343
1344 return;
1345 --
1346 end if;
1347
1348 IF (l_debug = 'Y') THEN
1349 okc_debug.Log('1000: Leaving ',2);
1350 okc_debug.Reset_Indentation;
1351 END IF;
1352
1353 exception
1354 when others then
1355 wf_core.context(G_PKG_NAME,
1356 'ACCEPT',
1357 itemtype,
1358 itemkey,
1359 to_char(actid),
1360 funcmode);
1361 IF (l_debug = 'Y') THEN
1362 okc_debug.Log('2000: Leaving ',2);
1363 okc_debug.Reset_Indentation;
1364 END IF;
1365 raise;
1366 end accept;
1367
1368
1369 --
1370 -- reject
1371 --
1372 procedure reject( itemtype in varchar2,
1373 itemkey in varchar2,
1374 actid in number,
1375 funcmode in varchar2,
1376 resultout out nocopy varchar2) is
1377 x_return_status varchar2(1);
1378 --
1379 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'reject';
1380 --
1381
1382 begin
1383
1384 IF (l_debug = 'Y') THEN
1385 okc_debug.Set_Indentation(l_proc);
1386 okc_debug.Log('10: Entering ',2);
1387 END IF;
1388 --
1389 -- RUN mode - normal process execution
1390 --
1391 if (funcmode = 'RUN') then
1392 --
1393 proc_call(p_key => itemkey,p_attr_name => 'P_REJECT_PROC',
1394 x_return_status => x_return_status);
1395 resultout := 'COMPLETE:';
1396
1397 IF (l_debug = 'Y') THEN
1398 okc_debug.Log('100: Leaving ',2);
1399 okc_debug.Reset_Indentation;
1400 END IF;
1401
1402 return;
1403 --
1404 end if;
1405 --
1406 -- CANCEL mode
1407 --
1408 if (funcmode = 'CANCEL') then
1409 --
1410 resultout := 'COMPLETE:';
1411
1412 IF (l_debug = 'Y') THEN
1413 okc_debug.Log('200: Leaving ',2);
1414 okc_debug.Reset_Indentation;
1415 END IF;
1416
1417 return;
1418 --
1419 end if;
1420 --
1421 -- TIMEOUT mode
1422 --
1423 if (funcmode = 'TIMEOUT') then
1424 --
1425 resultout := 'COMPLETE:';
1426
1427 IF (l_debug = 'Y') THEN
1428 okc_debug.Log('300: Leaving ',2);
1429 okc_debug.Reset_Indentation;
1430 END IF;
1431
1432 return;
1433 --
1434 end if;
1435
1436 IF (l_debug = 'Y') THEN
1437 okc_debug.Log('1000: Leaving ',2);
1438 okc_debug.Reset_Indentation;
1439 END IF;
1440
1441
1442 exception
1443 when others then
1444 wf_core.context(G_PKG_NAME,
1445 'REJECT',
1446 itemtype,
1447 itemkey,
1448 to_char(actid),
1449 funcmode);
1450 IF (l_debug = 'Y') THEN
1451 okc_debug.Log('2000: Leaving ',2);
1452 okc_debug.Reset_Indentation;
1453 END IF;
1454 raise;
1455 end reject;
1456
1457 --
1458 -- timeout
1459 --
1460 procedure timeout( itemtype in varchar2,
1461 itemkey in varchar2,
1462 actid in number,
1463 funcmode in varchar2,
1464 resultout out nocopy varchar2) is
1465 x_return_status varchar2(1);
1466 --
1467 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'timeout';
1468 --
1469 begin
1470
1471 IF (l_debug = 'Y') THEN
1472 okc_debug.Set_Indentation(l_proc);
1473 okc_debug.Log('10: Entering ',2);
1474 END IF;
1475 --
1476 -- RUN mode - normal process execution
1477 --
1478 if (funcmode = 'RUN') then
1479 --
1480 proc_call(p_key => itemkey,p_attr_name => 'P_TIMEOUT_PROC',
1481 x_return_status => x_return_status);
1482 resultout := 'COMPLETE:';
1483
1484 IF (l_debug = 'Y') THEN
1485 okc_debug.Log('100: Leaving ',2);
1486 okc_debug.Reset_Indentation;
1487 END IF;
1488
1489 return;
1490 --
1491 end if;
1492 --
1493 -- CANCEL mode
1494 --
1495 if (funcmode = 'CANCEL') then
1496 --
1497 resultout := 'COMPLETE:';
1498
1499 IF (l_debug = 'Y') THEN
1500 okc_debug.Log('200: Leaving ',2);
1501 okc_debug.Reset_Indentation;
1502 END IF;
1503
1504
1505 return;
1506 --
1507 end if;
1508 --
1509 -- TIMEOUT mode
1510 --
1511 if (funcmode = 'TIMEOUT') then
1512 --
1513 resultout := 'COMPLETE:';
1514
1515 IF (l_debug = 'Y') THEN
1516 okc_debug.Log('300: Leaving ',2);
1517 okc_debug.Reset_Indentation;
1518 END IF;
1519
1520 return;
1521 --
1522 end if;
1523
1524 IF (l_debug = 'Y') THEN
1525 okc_debug.Log('1000: Leaving ',2);
1526 okc_debug.Reset_Indentation;
1527 END IF;
1528
1529
1530 exception
1531 when others then
1532 wf_core.context(G_PKG_NAME,
1533 'TIMEOUT',
1534 itemtype,
1535 itemkey,
1536 to_char(actid),
1537 funcmode);
1538 IF (l_debug = 'Y') THEN
1539 okc_debug.Log('2000: Leaving ',2);
1540 okc_debug.Reset_Indentation;
1541 END IF;
1542 raise;
1543 end timeout;
1544
1545 --
1546 -- wf start API (Branch 3)
1547 --
1548 procedure resolver_call(
1549 --
1550 -- common API parameters
1551 --
1552 p_api_version IN NUMBER,
1553 p_init_msg_list IN VARCHAR2 ,
1554 x_return_status OUT NOCOPY VARCHAR2,
1555 x_msg_count OUT NOCOPY NUMBER,
1556 x_msg_data OUT NOCOPY VARCHAR2,
1557 --
1558 -- wf attributes
1559 --
1560 p_resolver IN VARCHAR2,
1561 p_msg_subj_resolver IN VARCHAR2,
1562 p_msg_body_resolver IN VARCHAR2,
1563 p_note IN VARCHAR2 ,
1564 p_accept_proc IN VARCHAR2,
1565 p_reject_proc IN VARCHAR2,
1566 p_timeout_proc IN VARCHAR2 ,
1567 p_timeout_minutes IN NUMBER ,
1568 --
1569 -- hidden notification attributes
1570 --
1571 p_ntf_type IN VARCHAR2 ,
1572 p_contract_id IN NUMBER ,
1573 p_task_id IN NUMBER ,
1574 p_extra_attr_num IN NUMBER ,
1575 p_extra_attr_text IN VARCHAR2 ,
1576 p_extra_attr_date IN DATE
1577 ) is
1578
1579 l_api_name CONSTANT VARCHAR2(30) := 'RESOLVER_CALL';
1580 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1581 l_key varchar2(100);
1582 --
1583 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'resolver_call';
1584 --
1585
1586 BEGIN
1587
1588 IF (l_debug = 'Y') THEN
1589 okc_debug.Set_Indentation(l_proc);
1590 okc_debug.Log('10: Entering ',2);
1591 END IF;
1592
1593 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
1594 G_PKG_NAME,
1595 p_init_msg_list,
1596 l_api_version,
1597 p_api_version,
1598 G_LEVEL,
1599 x_return_status);
1600 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1601 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1602 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1603 RAISE OKC_API.G_EXCEPTION_ERROR;
1604 END IF;
1605
1606 --
1607 -- create process
1608 --
1609 select to_char(okc_wf_notify_s1.nextval) into l_key from dual;
1610 wf_engine.CreateProcess( ItemType => G_WF_NAME,
1611 ItemKey => L_KEY,
1612 process => G_PROCESS_NAME);
1613 wf_engine.SetItemUserKey ( ItemType => G_WF_NAME,
1614 ItemKey => L_KEY,
1615 UserKey => L_KEY);
1616 --
1617 -- design time attr !
1618 --
1619
1620 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1621 itemkey => L_KEY,
1622 aname => 'P_RESOLVER',
1623 avalue => P_RESOLVER);
1624 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1625 itemkey => L_KEY,
1626 aname => 'MSG_SUBJ_RESOLVER',
1627 avalue => P_MSG_SUBJ_RESOLVER);
1628 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1629 itemkey => L_KEY,
1630 aname => 'MSG_BODY_RESOLVER',
1631 avalue => P_MSG_BODY_RESOLVER);
1632 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1633 itemkey => L_KEY,
1634 aname => 'NOTE',
1635 avalue => P_NOTE);
1636 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1637 itemkey => L_KEY,
1638 aname => 'P_ACCEPT_PROC',
1639 avalue => P_ACCEPT_PROC);
1640 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1641 itemkey => L_KEY,
1642 aname => 'P_REJECT_PROC',
1643 avalue => P_REJECT_PROC);
1644 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1645 itemkey => L_KEY,
1646 aname => 'P_TIMEOUT_PROC',
1647 avalue => NVL(P_TIMEOUT_PROC,P_REJECT_PROC));
1648 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
1649 itemkey => L_KEY,
1650 aname => 'P_TIMEOUT_MINUTES',
1651 avalue => P_TIMEOUT_MINUTES);
1652 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1653 itemkey => L_KEY,
1654 aname => 'P_NTF_TYPE',
1655 avalue => P_NTF_TYPE);
1656 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
1657 itemkey => L_KEY,
1658 aname => 'CONTRACT_ID',
1659 avalue => P_CONTRACT_ID);
1660 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
1661 itemkey => L_KEY,
1662 aname => 'TASK_ID',
1663 avalue => P_TASK_ID);
1664 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
1665 itemkey => L_KEY,
1666 aname => 'EXTRA_ATTR_NUM',
1667 avalue => P_EXTRA_ATTR_NUM);
1668 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
1669 itemkey => L_KEY,
1670 aname => 'EXTRA_ATTR_TEXT',
1671 avalue => P_EXTRA_ATTR_TEXT);
1672 wf_engine.SetItemAttrDate (itemtype => G_WF_NAME,
1673 itemkey => L_KEY,
1674 aname => 'EXTRA_ATTR_DATE',
1675 avalue => P_EXTRA_ATTR_DATE);
1676 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
1677 itemkey => L_KEY,
1678 aname => 'P_VERSION',
1679 avalue => 3);
1680 --
1681 -- save env / start
1682 --
1683 save_env(G_WF_NAME, l_key);
1684 wf_engine.SetItemOwner ( itemtype => G_WF_NAME,
1685 itemkey => L_KEY,
1686 owner => fnd_global.user_name);
1687 wf_engine.StartProcess( itemtype => G_WF_NAME,
1688 itemkey => L_KEY);
1689 -- commit;
1690 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1691 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1692
1693 IF (l_debug = 'Y') THEN
1694 okc_debug.Log('1000: Leaving ',2);
1695 okc_debug.Reset_Indentation;
1696 END IF;
1697
1698
1699 EXCEPTION
1700 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1701 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1702 (substr(l_api_name,1,26),
1703 G_PKG_NAME,
1704 'OKC_API.G_RET_STS_ERROR',
1705 x_msg_count,
1706 x_msg_data,
1707 G_LEVEL);
1708 IF (l_debug = 'Y') THEN
1709 okc_debug.Log('3000: Leaving ',2);
1710 okc_debug.Reset_Indentation;
1711 END IF;
1712 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1713 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1714 (substr(l_api_name,1,26),
1715 G_PKG_NAME,
1716 'OKC_API.G_RET_STS_UNEXP_ERROR',
1717 x_msg_count,
1718 x_msg_data,
1719 G_LEVEL);
1720 IF (l_debug = 'Y') THEN
1721 okc_debug.Log('4000: Leaving ',2);
1722 okc_debug.Reset_Indentation;
1723 END IF;
1724 WHEN OTHERS THEN
1725 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1726 (substr(l_api_name,1,26),
1727 G_PKG_NAME,
1728 'OTHERS',
1729 x_msg_count,
1730 x_msg_data,
1731 G_LEVEL);
1732 IF (l_debug = 'Y') THEN
1733 okc_debug.Log('5000: Leaving ',2);
1734 okc_debug.Reset_Indentation;
1735 END IF;
1736 end resolver_call;
1737
1738 --
1739 -- periodic returns 'T'/'F'
1740 --
1741 procedure periodic( itemtype in varchar2,
1742 itemkey in varchar2,
1743 actid in number,
1744 funcmode in varchar2,
1745 resultout out nocopy varchar2 ) is
1746 P_PERIOD_DAYS number;
1747 P_STOP_DATE date;
1748 --
1749 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'periodic';
1750 --
1751
1752 begin
1753
1754 IF (l_debug = 'Y') THEN
1755 okc_debug.Set_Indentation(l_proc);
1756 okc_debug.Log('10: Entering ',2);
1757 END IF;
1758 --
1759 -- RUN mode - normal process execution
1760 --
1761 if (funcmode = 'RUN') then
1762 begin
1763 P_PERIOD_DAYS := wf_engine.GetItemAttrNumber(itemtype,itemkey,'P_PERIOD_DAYS');
1764 P_STOP_DATE := wf_engine.GetItemAttrDate(itemtype,itemkey,'P_STOP_DATE');
1765 exception
1766 when others then
1767 NULL;
1768 end;
1769 if ((P_PERIOD_DAYS is NULL) or (P_PERIOD_DAYS = 0) or
1770 (P_STOP_DATE is NULL) or (sysdate+P_PERIOD_DAYS >= P_STOP_DATE))
1771 then
1772 resultout := 'COMPLETE:F';
1773 else
1774 resultout := 'COMPLETE:T';
1775 end if;
1776 end if;
1777 --
1778 -- CANCEL mode
1779 --
1780 if (funcmode = 'CANCEL') then
1781 --
1782 resultout := 'COMPLETE:';
1783
1784 IF (l_debug = 'Y') THEN
1785 okc_debug.Log('100: Leaving ',2);
1786 okc_debug.Reset_Indentation;
1787 END IF;
1788
1789 return;
1790 --
1791 end if;
1792 --
1793 -- TIMEOUT mode
1794 --
1795 if (funcmode = 'TIMEOUT') then
1796 --
1797 resultout := 'COMPLETE:';
1798
1799 IF (l_debug = 'Y') THEN
1800 okc_debug.Log('200: Leaving ',2);
1801 okc_debug.Reset_Indentation;
1802 END IF;
1803
1804 return;
1805 --
1806 end if;
1807
1808 IF (l_debug = 'Y') THEN
1809 okc_debug.Log('1000: Leaving ',2);
1810 okc_debug.Reset_Indentation;
1811 END IF;
1812
1813
1814 exception
1815 when others then
1816 wf_core.context(G_PKG_NAME,
1817 'PERIODIC',
1818 itemtype,
1819 itemkey,
1820 to_char(actid),
1821 funcmode);
1822 IF (l_debug = 'Y') THEN
1823 okc_debug.Log('2000: Leaving ',2);
1824 okc_debug.Reset_Indentation;
1825 END IF;
1826 raise;
1827 end periodic;
1828
1829 --
1830 -- periodic returns 'T'/'F'
1831 --
1832 procedure time_over( itemtype in varchar2,
1833 itemkey in varchar2,
1834 actid in number,
1835 funcmode in varchar2,
1836 resultout out nocopy varchar2 ) is
1837 --
1838 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'time_over';
1839 --
1840 begin
1841
1842 IF (l_debug = 'Y') THEN
1843 okc_debug.Set_Indentation(l_proc);
1844 okc_debug.Log('10: Entering ',2);
1845 END IF;
1846 --
1847 -- RUN mode - normal process execution
1848 --
1849 if (funcmode = 'RUN') then
1850 if (sysdate >= wf_engine.GetItemAttrDate(itemtype,itemkey,'P_STOP_DATE')) then
1851 resultout := 'COMPLETE:T';
1852 else
1853 resultout := 'COMPLETE:F';
1854 end if;
1855 end if;
1856 --
1857 -- CANCEL mode
1858 --
1859 if (funcmode = 'CANCEL') then
1860 --
1861 resultout := 'COMPLETE:';
1862
1863 IF (l_debug = 'Y') THEN
1864 okc_debug.Log('100: Leaving ',2);
1865 okc_debug.Reset_Indentation;
1866 END IF;
1867
1868
1869 return;
1870 --
1871 end if;
1872 --
1873 -- TIMEOUT mode
1874 --
1875 if (funcmode = 'TIMEOUT') then
1876 --
1877 resultout := 'COMPLETE:';
1878
1879 IF (l_debug = 'Y') THEN
1880 okc_debug.Log('200: Leaving ',2);
1881 okc_debug.Reset_Indentation;
1882 END IF;
1883
1884
1885 return;
1886 --
1887 end if;
1888
1889 IF (l_debug = 'Y') THEN
1890 okc_debug.Log('1000: Leaving ',2);
1891 okc_debug.Reset_Indentation;
1892 END IF;
1893
1894
1895 exception
1896 when others then
1897 wf_core.context(G_PKG_NAME,
1898 'TIME_OVER',
1899 itemtype,
1900 itemkey,
1901 to_char(actid),
1902 funcmode);
1903 IF (l_debug = 'Y') THEN
1904 okc_debug.Log('2000: Leaving ',2);
1905 okc_debug.Reset_Indentation;
1906 END IF;
1907 raise;
1908 end time_over;
1909
1910 --
1911 -- calls p_doc_proc
1912 --
1913 procedure get_doc(document_id in varchar2,
1914 display_type in varchar2,
1915 document in out nocopy CLOB,
1916 document_type in out nocopy varchar2) is
1917 --
1918 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'get_doc';
1919 --
1920 begin
1921
1922 IF (l_debug = 'Y') THEN
1923 okc_debug.Set_Indentation(l_proc);
1924 okc_debug.Log('10: Entering ',2);
1925 END IF;
1926
1927 IF (l_debug = 'Y') THEN
1928 okc_debug.Log('20: document_id : '||document_id,2);
1929 okc_debug.Log('20: display_type : '||display_type,2);
1930 okc_debug.Log('20: document_type : '||document_type,2);
1931 END IF;
1932
1933 savepoint get_doc;
1934 begin
1935 EXECUTE IMMEDIATE wf_engine.GetItemAttrText(G_WF_NAME,document_id,'P_DOC_PROC') USING IN OUT document;
1936 exception
1937 when others then
1938 rollback to get_doc;
1939 end;
1940
1941 IF (l_debug = 'Y') THEN
1942 okc_debug.Log('100: document_type out nocopy : '||document_type,2);
1943 okc_debug.Log('1000: Leaving ',2);
1944 okc_debug.Reset_Indentation;
1945 END IF;
1946
1947
1948 end;
1949
1950 --
1951 -- wf start API (Branch 5)
1952 --
1953 procedure loop_call(
1954 --
1955 -- common API parameters
1956 --
1957 p_api_version IN NUMBER,
1958 p_init_msg_list IN VARCHAR2 ,
1959 x_return_status OUT NOCOPY VARCHAR2,
1960 x_msg_count OUT NOCOPY NUMBER,
1961 x_msg_data OUT NOCOPY VARCHAR2,
1962 --
1963 -- specific parameters
1964 --
1965 p_proc IN VARCHAR2,
1966 --NPALEPU
1967 --14-DEC-2005
1968 --Added new parameter P_PROC_NAME for bug # 4699009.
1969 p_proc_name IN VARCHAR2 DEFAULT NULL,
1970 --END NPALEPU
1971 p_s_recipient IN VARCHAR2 ,
1972 p_e_recipient IN VARCHAR2 ,
1973 p_timeout_minutes IN NUMBER ,
1974 p_loops IN NUMBER ,
1975 p_subj_first_msg IN VARCHAR2 ,
1976 --
1977 -- hidden notification attributes
1978 --
1979 p_ntf_type IN VARCHAR2 ,
1980 p_contract_id IN NUMBER ,
1981 p_task_id IN NUMBER ,
1982 p_extra_attr_num IN NUMBER ,
1983 p_extra_attr_text IN VARCHAR2 ,
1984 p_extra_attr_date IN DATE
1985 ) is
1986 l_api_name CONSTANT VARCHAR2(30) := 'LOOP_CALL';
1987 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1988 l_key varchar2(100);
1989
1990 --
1991 CURSOR c_wf_timeout IS
1992 SELECT NUMBER_DEFAULT
1993 from WF_ITEM_ATTRIBUTES
1994 where ITEM_TYPE = G_WF_NAME
1995 and NAME = 'P_TIMEOUT_MINUTES';
1996 --
1997 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'loop_call';
1998 --
1999 l_timeout_minutes NUMBER := p_timeout_minutes;
2000 l_wf_timeout_minutes NUMBER;
2001 --
2002 BEGIN
2003
2004 IF (l_debug = 'Y') THEN
2005 okc_debug.Set_Indentation(l_proc);
2006 okc_debug.Log('10: Entering ',2);
2007 END IF;
2008
2009 IF (l_debug = 'Y') THEN
2010 okc_debug.Log('20: p_api_version : '||p_api_version,2);
2011 okc_debug.Log('20: p_init_msg_list : '||p_init_msg_list,2);
2012 okc_debug.Log('20: p_proc : '||p_proc,2);
2013 --NPALEPU
2014 --14-DEC-2005
2015 --BUG # 4699009
2016 okc_debug.Log('20: p_proc_name : '||p_proc_name,2);
2017 --END NPALEPU
2018 okc_debug.Log('20: p_s_recipient : '||p_s_recipient,2);
2019 okc_debug.Log('20: p_e_recipient : '||p_e_recipient,2);
2020 okc_debug.Log('20: p_timeout_minutes : '||p_timeout_minutes,2);
2021 okc_debug.Log('20: p_loops : '||p_loops,2);
2022 okc_debug.Log('20: p_subj_first_msg : '||p_subj_first_msg,2);
2023 okc_debug.Log('20: p_ntf_type : '||p_ntf_type,2);
2024 okc_debug.Log('20: p_contract_id : '||p_contract_id,2);
2025 okc_debug.Log('20: p_task_id : '||p_task_id,2);
2026 okc_debug.Log('20: p_extra_attr_num : '||p_extra_attr_num,2);
2027 okc_debug.Log('20: p_extra_attr_text : '||p_extra_attr_text,2);
2028 okc_debug.Log('20: p_extra_attr_date : '||p_extra_attr_date,2);
2029 END IF;
2030
2031 l_return_status := OKC_API.START_ACTIVITY(substr(l_api_name,1,26),
2032 G_PKG_NAME,
2033 p_init_msg_list,
2034 l_api_version,
2035 p_api_version,
2036 G_LEVEL,
2037 x_return_status);
2038
2039 IF (l_debug = 'Y') THEN
2040 okc_debug.Log('30: l_return_status : '||l_return_status,2);
2041 END IF;
2042
2043 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2044 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2045 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2046 RAISE OKC_API.G_EXCEPTION_ERROR;
2047 END IF;
2048
2049 /* MSENGUPT: The following code is changed from the previous version to make it more effucient. ie. read c_wf_timeout only
2050 if l_timeout_minutes from profile options is NULL...... very less likely */
2051 -- determine the value of p_timeout_minutes
2052 If l_timeout_minutes IS NULL THEN
2053 l_timeout_minutes := fnd_profile.value('OKC_ALERT_TIMEOUT');
2054 IF l_timeout_minutes IS NULL THEN
2055 -- get the defualt value for p_timeout_minutes from the OKCALERT workflow
2056 OPEN c_wf_timeout;
2057 FETCH c_wf_timeout INTO l_wf_timeout_minutes;
2058 IF c_wf_timeout%NOTFOUND THEN
2059 l_wf_timeout_minutes := 2880;
2060 END IF;
2061 CLOSE c_wf_timeout;
2062 l_timeout_minutes := l_wf_timeout_minutes;
2063 END IF;
2064 END IF;
2065 /* End of code change */
2066 --
2067 -- create process
2068 --
2069 select to_char(okc_wf_notify_s1.nextval) into l_key from dual;
2070
2071 IF (l_debug = 'Y') THEN
2072 okc_debug.Log('40: l_key : '||l_key,2);
2073 okc_debug.Log('40: G_WF_NAME : '||G_WF_NAME,2);
2074 okc_debug.Log('40: G_PROCESS_NAME : '||G_PROCESS_NAME,2);
2075 END IF;
2076
2077 wf_engine.CreateProcess( ItemType => G_WF_NAME,
2078 ItemKey => L_KEY,
2079 process => G_PROCESS_NAME);
2080 wf_engine.SetItemUserKey ( ItemType => G_WF_NAME,
2081 ItemKey => L_KEY,
2082 UserKey => L_KEY);
2083 --
2084 -- design time attr !
2085 --
2086
2087 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2088 itemkey => L_KEY,
2089 aname => 'P_VERSION',
2090 avalue => 5);
2091 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2092 itemkey => L_KEY,
2093 aname => 'P_PROC',
2094 avalue => P_PROC);
2095 --NPALEPU,14-DEC-2005
2096 --bug # 4699009
2097 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2098 itemkey => L_KEY,
2099 aname => 'P_PROC_NAME',
2100 avalue => P_PROC_NAME);
2101 --END NPALEPU
2102 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2103 itemkey => L_KEY,
2104 aname => 'P_S_RECIPIENT',
2105 avalue => P_S_RECIPIENT);
2106 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2107 itemkey => L_KEY,
2108 aname => 'P_E_RECIPIENT',
2109 avalue => P_E_RECIPIENT);
2110 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2111 itemkey => L_KEY,
2112 aname => 'P_TIMEOUT_MINUTES',
2113 avalue => L_TIMEOUT_MINUTES);
2114 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2115 itemkey => L_KEY,
2116 aname => 'P_LOOPS',
2117 avalue => P_LOOPS);
2118 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2119 itemkey => L_KEY,
2120 aname => 'P_SUBJ_FIRST_MSG',
2121 avalue => P_SUBJ_FIRST_MSG);
2122 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2123 itemkey => L_KEY,
2124 aname => 'P_NTF_TYPE',
2125 avalue => P_NTF_TYPE);
2126 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2127 itemkey => L_KEY,
2128 aname => 'CONTRACT_ID',
2129 avalue => P_CONTRACT_ID);
2130 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2131 itemkey => L_KEY,
2132 aname => 'TASK_ID',
2133 avalue => P_TASK_ID);
2134 wf_engine.SetItemAttrNumber (itemtype => G_WF_NAME,
2135 itemkey => L_KEY,
2136 aname => 'EXTRA_ATTR_NUM',
2137 avalue => P_EXTRA_ATTR_NUM);
2138 -- marat start (bug#2477385)
2139 wf_engine.SetItemAttrText ( itemtype => G_WF_NAME,
2140 itemkey => L_KEY,
2141 aname => 'EXTRA_ATTR_TEXT',
2142 avalue => okc_wf.get_wf_string);
2143 -- marat end
2144 /* - we'll use EXTRA_ATTR_TEXT item attr for sending outcome params to wf process
2145 -- (bug#2477385)
2146 -- below commented out by marat
2147 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2148 itemkey => L_KEY,
2149 aname => 'EXTRA_ATTR_TEXT',
2150 avalue => P_EXTRA_ATTR_TEXT);
2151 -- above commented out by marat
2152 */
2153 wf_engine.SetItemAttrDate (itemtype => G_WF_NAME,
2154 itemkey => L_KEY,
2155 aname => 'EXTRA_ATTR_DATE',
2156 avalue => P_EXTRA_ATTR_DATE);
2157 --
2158 -- save env / start
2159 --
2160 save_env(G_WF_NAME, l_key);
2161 wf_engine.SetItemOwner ( itemtype => G_WF_NAME,
2162 itemkey => L_KEY,
2163 owner => fnd_global.user_name);
2164
2165 IF (l_debug = 'Y') THEN
2166 okc_debug.Log('50: WF Owner : '||fnd_global.user_name,2);
2167 END IF;
2168
2169 wf_engine.StartProcess( itemtype => G_WF_NAME,
2170 itemkey => L_KEY);
2171 -- commit;
2172 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2173 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2174
2175 IF (l_debug = 'Y') THEN
2176 okc_debug.Log('60: x_return_status : '||x_return_status,2);
2177 END IF;
2178
2179
2180 IF (l_debug = 'Y') THEN
2181 okc_debug.Log('1000: Leaving ',2);
2182 okc_debug.Reset_Indentation;
2183 END IF;
2184
2185
2186 EXCEPTION
2187 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2188 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2189 (substr(l_api_name,1,26),
2190 G_PKG_NAME,
2191 'OKC_API.G_RET_STS_ERROR',
2192 x_msg_count,
2193 x_msg_data,
2194 G_LEVEL);
2195 IF (l_debug = 'Y') THEN
2196 okc_debug.Log('3000: Leaving ',2);
2197 okc_debug.Reset_Indentation;
2198 END IF;
2199 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2200 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2201 (substr(l_api_name,1,26),
2202 G_PKG_NAME,
2203 'OKC_API.G_RET_STS_UNEXP_ERROR',
2204 x_msg_count,
2205 x_msg_data,
2206 G_LEVEL);
2207 IF (l_debug = 'Y') THEN
2208 okc_debug.Log('4000: Leaving ',2);
2209 okc_debug.Reset_Indentation;
2210 END IF;
2211 WHEN OTHERS THEN
2212 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2213 (substr(l_api_name,1,26),
2214 G_PKG_NAME,
2215 'OTHERS',
2216 x_msg_count,
2217 x_msg_data,
2218 G_LEVEL);
2219 IF (l_debug = 'Y') THEN
2220 okc_debug.Log('5000: Leaving ',2);
2221 okc_debug.Reset_Indentation;
2222 END IF;
2223 end loop_call;
2224
2225 -- Start of comments
2226 --
2227 -- Procedure Name : No_Email
2228 -- Description :
2229 -- Business Rules :
2230 -- Parameters :
2231 -- Post_Approval : 1.0
2232 -- End of comments
2233
2234 procedure No_Email( itemtype in varchar2,
2235 itemkey in varchar2,
2236 actid in number,
2237 funcmode in varchar2,
2238 resultout out nocopy varchar2 ) is
2239 --
2240 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'No_Email';
2241 --
2242 begin
2243
2244 IF (l_debug = 'Y') THEN
2245 okc_debug.Set_Indentation(l_proc);
2246 okc_debug.Log('10: Entering ',2);
2247 END IF;
2248 --
2249 -- RESPOND mode
2250 --
2251 if (funcmode = 'RESPOND') then
2252 update wf_notifications
2253 set mail_status=NULL
2254 where NOTIFICATION_ID=wf_engine.context_nid;
2255
2256 IF (l_debug = 'Y') THEN
2257 okc_debug.Log('100: Leaving ',2);
2258 okc_debug.Reset_Indentation;
2259 END IF;
2260
2261
2262 return;
2263 end if;
2264 --
2265 -- if other mode mode
2266 --
2267
2268 IF (l_debug = 'Y') THEN
2269 okc_debug.Log('1000: Leaving ',2);
2270 okc_debug.Reset_Indentation;
2271 END IF;
2272
2273 --
2274 return;
2275 --
2276
2277 exception
2278 when others then
2279 wf_core.context('OKC_ASYNC_PVT',
2280 'NO_EMAIL',
2281 itemtype,
2282 itemkey,
2283 to_char(actid),
2284 funcmode);
2285 IF (l_debug = 'Y') THEN
2286 okc_debug.Log('2000: Leaving ',2);
2287 okc_debug.Reset_Indentation;
2288 END IF;
2289 raise;
2290 end No_Email;
2291
2292 procedure success_mute( itemtype in varchar2,
2293 itemkey in varchar2,
2294 actid in number,
2295 funcmode in varchar2,
2296 resultout out nocopy varchar2 ) is
2297 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2298 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_S_RECIPIENT');
2299 cursor c1(p_name varchar2) is
2300 select user_id from fnd_user
2301 where user_name=p_name;
2302 l_user_id number;
2303 l_p_value varchar2(3);
2304 --
2305 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'success_mute';
2306 --
2307 begin
2308
2309 IF (l_debug = 'Y') THEN
2310 okc_debug.Set_Indentation(l_proc);
2311 okc_debug.Log('10: Entering ',2);
2312 END IF;
2313
2314 if (funcmode = 'RUN') then
2315 open c1(l_user_name);
2316 fetch c1 into l_user_id;
2317 close c1;
2318 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2319 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2320 USER_ID => l_user_id,
2321 RESPONSIBILITY_ID => NULL,
2322 APPLICATION_ID => NULL);
2323 if (l_p_value is NULL) then
2324 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2325 (NAME => G_MUTE_PROFILE,
2326 USER_ID => l_user_id,
2327 RESPONSIBILITY_ID=> NULL,
2328 APPLICATION_ID => NULL);
2329 end if;
2330 if (l_p_value is NULL or l_p_value='N') then
2331 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2332 itemkey => itemkey,
2333 aname => '.MAIL_QUERY',
2334 avalue => ' ');
2335 else
2336 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2337 itemkey => itemkey,
2338 aname => '.MAIL_QUERY',
2339 avalue => l_user_name);
2340 end if;
2341 end if;
2342 if (funcmode = 'CANCEL') then
2343 --
2344 resultout := 'COMPLETE:';
2345
2346 IF (l_debug = 'Y') THEN
2347 okc_debug.Log('100: Leaving ',2);
2348 okc_debug.Reset_Indentation;
2349 END IF;
2350
2351
2352 return;
2353 --
2354 end if;
2355 --
2356 -- TIMEOUT mode
2357 --
2358 if (funcmode = 'TIMEOUT') then
2359 --
2360 resultout := 'COMPLETE:';
2361
2362 IF (l_debug = 'Y') THEN
2363 okc_debug.Log('200: Leaving ',2);
2364 okc_debug.Reset_Indentation;
2365 END IF;
2366
2367
2368 return;
2369 --
2370 end if;
2371
2372 IF (l_debug = 'Y') THEN
2373 okc_debug.Log('1000: Leaving ',2);
2374 okc_debug.Reset_Indentation;
2375 END IF;
2376
2377
2378 exception
2379 when others then
2380 wf_core.context(G_PKG_NAME,
2381 'SUCCESS_MUTE',
2382 itemtype,
2383 itemkey,
2384 to_char(actid),
2385 funcmode);
2386 IF (l_debug = 'Y') THEN
2387 okc_debug.Log('2000: Leaving ',2);
2388 okc_debug.Reset_Indentation;
2389 END IF;
2390 raise;
2391 end success_mute;
2392
2393 procedure error_mute( itemtype in varchar2,
2394 itemkey in varchar2,
2395 actid in number,
2396 funcmode in varchar2,
2397 resultout out nocopy varchar2 ) is
2398 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2399 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_E_RECIPIENT');
2400 cursor c1(p_name varchar2) is
2401 select user_id from fnd_user
2402 where user_name=p_name;
2403 l_user_id number;
2404 l_p_value varchar2(3);
2405 --
2406 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'error_mute';
2407 --
2408 begin
2409
2410 IF (l_debug = 'Y') THEN
2411 okc_debug.Set_Indentation(l_proc);
2412 okc_debug.Log('10: Entering ',2);
2413 END IF;
2414
2415 if (funcmode = 'RUN') then
2416 open c1(l_user_name);
2417 fetch c1 into l_user_id;
2418 close c1;
2419 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2420 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2421 USER_ID => l_user_id,
2422 RESPONSIBILITY_ID=> NULL,
2423 APPLICATION_ID => NULL);
2424 if (l_p_value is NULL) then
2425 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2426 (NAME => G_MUTE_PROFILE,
2427 USER_ID => l_user_id,
2428 RESPONSIBILITY_ID=> NULL,
2429 APPLICATION_ID => NULL);
2430 end if;
2431 if (l_p_value is NULL or l_p_value='N') then
2432 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2433 itemkey => itemkey,
2434 aname => '.MAIL_QUERY',
2435 avalue => ' ');
2436 else
2437 wf_engine.SetItemAttrText (itemtype => G_WF_NAME,
2438 itemkey => itemkey,
2439 aname => '.MAIL_QUERY',
2440 avalue => l_user_name);
2441 end if;
2442 end if;
2443 if (funcmode = 'CANCEL') then
2444 --
2445 resultout := 'COMPLETE:';
2446
2447 IF (l_debug = 'Y') THEN
2448 okc_debug.Log('100: Leaving ',2);
2449 okc_debug.Reset_Indentation;
2450 END IF;
2451
2452
2453 return;
2454 --
2455 end if;
2456 --
2457 -- TIMEOUT mode
2458 --
2459 if (funcmode = 'TIMEOUT') then
2460 --
2461 resultout := 'COMPLETE:';
2462
2463 IF (l_debug = 'Y') THEN
2464 okc_debug.Log('200: Leaving ',2);
2465 okc_debug.Reset_Indentation;
2466 END IF;
2467
2468
2469 return;
2470 --
2471 end if;
2472
2473 IF (l_debug = 'Y') THEN
2474 okc_debug.Log('1000: Leaving ',2);
2475 okc_debug.Reset_Indentation;
2476 END IF;
2477
2478
2479 exception
2480 when others then
2481 wf_core.context(G_PKG_NAME,
2482 'ERROR_MUTE',
2483 itemtype,
2484 itemkey,
2485 to_char(actid),
2486 funcmode);
2487 IF (l_debug = 'Y') THEN
2488 okc_debug.Log('2000: Leaving ',2);
2489 okc_debug.Reset_Indentation;
2490 END IF;
2491 raise;
2492 end error_mute;
2493
2494 -- Disables sending of email notifications
2495 --
2496 procedure fyi_mute( itemtype in varchar2,
2497 itemkey in varchar2,
2498 actid in number,
2499 funcmode in varchar2,
2500 resultout out nocopy varchar2 ) is
2501
2502 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2503 -- value NEXT_INFORMED_RECIPIENT used in Contract Approval workflow attribute and messages
2504 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'NEXT_INFORMED_USERNAME');
2505
2506 cursor c1(p_name varchar2) is
2507 select user_id from fnd_user
2508 where user_name=p_name;
2509
2510 l_user_id number;
2511 l_p_value varchar2(3);
2512 --
2513 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'fyi_mute';
2514 --
2515
2516 begin
2517
2518 IF (l_debug = 'Y') THEN
2519 okc_debug.Set_Indentation(l_proc);
2520 okc_debug.Log('10: Entering ',2);
2521 END IF;
2522
2523 if (funcmode = 'RUN') then
2524 open c1(l_user_name);
2525 fetch c1 into l_user_id;
2526 close c1;
2527 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2528 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2529 USER_ID => l_user_id,
2530 RESPONSIBILITY_ID=> NULL,
2531 APPLICATION_ID => NULL);
2532 if (l_p_value is NULL) then
2533 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2534 (NAME => G_MUTE_PROFILE,
2535 USER_ID => l_user_id,
2536 RESPONSIBILITY_ID=> NULL,
2537 APPLICATION_ID => NULL);
2538 end if;
2539 if (l_p_value is NULL or l_p_value='N') then
2540 -- UnMute Recipient
2541 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2542 itemkey => itemkey,
2543 aname => '.MAIL_QUERY',
2544 avalue => ' ');
2545 else
2546 -- Suppress Recipient (Mute)
2547 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2548 itemkey => itemkey,
2549 aname => '.MAIL_QUERY',
2550 avalue => l_user_name);
2551 end if;
2552 end if;
2553 if (funcmode = 'CANCEL') then
2554 --
2555 resultout := 'COMPLETE:';
2556
2557 IF (l_debug = 'Y') THEN
2558 okc_debug.Log('100: Leaving ',2);
2559 okc_debug.Reset_Indentation;
2560 END IF;
2561
2562
2563 return;
2564 --
2565 end if;
2566 --
2567 -- TIMEOUT mode
2568 --
2569 if (funcmode = 'TIMEOUT') then
2570 --
2571 resultout := 'COMPLETE:';
2572
2573 IF (l_debug = 'Y') THEN
2574 okc_debug.Log('200: Leaving ',2);
2575 okc_debug.Reset_Indentation;
2576 END IF;
2577
2578
2579 return;
2580 --
2581 end if;
2582
2583 IF (l_debug = 'Y') THEN
2584 okc_debug.Log('1000: Leaving ',2);
2585 okc_debug.Reset_Indentation;
2586 END IF;
2587
2588
2589 exception
2590 when others then
2591 wf_core.context(G_PKG_NAME,
2592 'FYI_MUTE',
2593 itemtype,
2594 itemkey,
2595 to_char(actid),
2596 funcmode);
2597 IF (l_debug = 'Y') THEN
2598 okc_debug.Log('2000: Leaving ',2);
2599 okc_debug.Reset_Indentation;
2600 END IF;
2601 raise;
2602 end fyi_mute;
2603
2604 -- Disables sending of Next Performer email notifications
2605 --
2606 procedure mute_nxt_pfmr( itemtype in varchar2,
2607 itemkey in varchar2,
2608 actid in number,
2609 funcmode in varchar2,
2610 resultout out nocopy varchar2 ) is
2611
2612 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2613 -- value NEXT_PERFORMER_USERNAME used in Contract Approval workflow attribute and messages
2614 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'NEXT_PERFORMER_USERNAME');
2615
2616 cursor c1(p_name varchar2) is
2617 select user_id from fnd_user
2618 where user_name=p_name;
2619
2620 l_user_id number;
2621 l_p_value varchar2(3);
2622 --
2623 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'mute_nxt_pfmr';
2624 --
2625
2626 begin
2627
2628 IF (l_debug = 'Y') THEN
2629 okc_debug.Set_Indentation(l_proc);
2630 okc_debug.Log('10: Entering ',2);
2631 END IF;
2632
2633 if (funcmode = 'RUN') then
2634 open c1(l_user_name);
2635 fetch c1 into l_user_id;
2636 close c1;
2637 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2638 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2639 USER_ID => l_user_id,
2640 RESPONSIBILITY_ID=> NULL,
2641 APPLICATION_ID => NULL);
2642 if (l_p_value is NULL) then
2643 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2644 (NAME => G_MUTE_PROFILE,
2645 USER_ID => l_user_id,
2646 RESPONSIBILITY_ID=> NULL,
2647 APPLICATION_ID => NULL);
2648 end if;
2649 if (l_p_value is NULL or l_p_value='N') then
2650 -- UnMute Recipient
2651 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2652 itemkey => itemkey,
2653 aname => '.MAIL_QUERY',
2654 avalue => ' ');
2655 else
2656 -- Suppress Recipient (Mute)
2657 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2658 itemkey => itemkey,
2659 aname => '.MAIL_QUERY',
2660 avalue => l_user_name);
2661 end if;
2662 end if;
2663 if (funcmode = 'CANCEL') then
2664 --
2665 resultout := 'COMPLETE:';
2666
2667 IF (l_debug = 'Y') THEN
2668 okc_debug.Log('100: Leaving ',2);
2669 okc_debug.Reset_Indentation;
2670 END IF;
2671
2672
2673 return;
2674 --
2675 end if;
2676 --
2677 -- TIMEOUT mode
2678 --
2679 if (funcmode = 'TIMEOUT') then
2680 --
2681 resultout := 'COMPLETE:';
2682
2683 IF (l_debug = 'Y') THEN
2684 okc_debug.Log('200: Leaving ',2);
2685 okc_debug.Reset_Indentation;
2686 END IF;
2687
2688
2689 return;
2690 --
2691 end if;
2692
2693 IF (l_debug = 'Y') THEN
2694 okc_debug.Log('1000: Leaving ',2);
2695 okc_debug.Reset_Indentation;
2696 END IF;
2697
2698
2699 exception
2700 when others then
2701 wf_core.context(G_PKG_NAME,
2702 'MUTE_NXT_PFMR',
2703 itemtype,
2704 itemkey,
2705 to_char(actid),
2706 funcmode);
2707 IF (l_debug = 'Y') THEN
2708 okc_debug.Log('2000: Leaving ',2);
2709 okc_debug.Reset_Indentation;
2710 END IF;
2711 raise;
2712 end mute_nxt_pfmr;
2713
2714 -- Disables sending of Contract Admin email notifications
2715 --
2716 procedure mute_k_admin( itemtype in varchar2,
2717 itemkey in varchar2,
2718 actid in number,
2719 funcmode in varchar2,
2720 resultout out nocopy varchar2 ) is
2721
2722 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2723 -- value CONTRACT_ADMIN_USERNAME used in Contract Approval workflow attribute and messages
2724 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'CONTRACT_ADMIN_USERNAME');
2725
2726 cursor c1(p_name varchar2) is
2727 select user_id from fnd_user
2728 where user_name=p_name;
2729
2730 l_user_id number;
2731 l_p_value varchar2(3);
2732 --
2733 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'mute_k_admin';
2734 --
2735
2736 begin
2737
2738 IF (l_debug = 'Y') THEN
2739 okc_debug.Set_Indentation(l_proc);
2740 okc_debug.Log('10: Entering ',2);
2741 END IF;
2742
2743 if (funcmode = 'RUN') then
2744 open c1(l_user_name);
2745 fetch c1 into l_user_id;
2746 close c1;
2747 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2748 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2749 USER_ID => l_user_id,
2750 RESPONSIBILITY_ID=> NULL,
2751 APPLICATION_ID => NULL);
2752 if (l_p_value is NULL) then
2753 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2754 (NAME => G_MUTE_PROFILE,
2755 USER_ID => l_user_id,
2756 RESPONSIBILITY_ID=> NULL,
2757 APPLICATION_ID => NULL);
2758 end if;
2759 if (l_p_value is NULL or l_p_value='N') then
2760 -- UnMute Recipient
2761 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2762 itemkey => itemkey,
2763 aname => '.MAIL_QUERY',
2764 avalue => ' ');
2765 else
2766 -- Suppress Recipient (Mute)
2767 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2768 itemkey => itemkey,
2769 aname => '.MAIL_QUERY',
2770 avalue => l_user_name);
2771 end if;
2772 end if;
2773 if (funcmode = 'CANCEL') then
2774 --
2775 resultout := 'COMPLETE:';
2776
2777 IF (l_debug = 'Y') THEN
2778 okc_debug.Log('100: Leaving ',2);
2779 okc_debug.Reset_Indentation;
2780 END IF;
2781
2782
2783 return;
2784 --
2785 end if;
2786 --
2787 -- TIMEOUT mode
2788 --
2789 if (funcmode = 'TIMEOUT') then
2790 --
2791 resultout := 'COMPLETE:';
2792
2793 IF (l_debug = 'Y') THEN
2794 okc_debug.Log('200: Leaving ',2);
2795 okc_debug.Reset_Indentation;
2796 END IF;
2797
2798
2799 return;
2800 --
2801 end if;
2802
2803 IF (l_debug = 'Y') THEN
2804 okc_debug.Log('1000: Leaving ',2);
2805 okc_debug.Reset_Indentation;
2806 END IF;
2807
2808
2809 exception
2810 when others then
2811 wf_core.context(G_PKG_NAME,
2812 'MUTE_K_ADMIN',
2813 itemtype,
2814 itemkey,
2815 to_char(actid),
2816 funcmode);
2817 IF (l_debug = 'Y') THEN
2818 okc_debug.Log('2000: Leaving ',2);
2819 okc_debug.Reset_Indentation;
2820 END IF;
2821 raise;
2822 end mute_k_admin;
2823
2824 -- Disables sending of Contract Signatory email notifications
2825 --
2826 procedure mute_signer( itemtype in varchar2,
2827 itemkey in varchar2,
2828 actid in number,
2829 funcmode in varchar2,
2830 resultout out nocopy varchar2 ) is
2831
2832 l_ntf_type varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'P_NTF_TYPE');
2833 -- value SIGNATORY_USERNAME used in Contract Approval workflow attribute and messages
2834 l_user_name varchar2(100) := wf_engine.GetItemAttrText(itemtype,itemkey,'SIGNATORY_USERNAME');
2835
2836 cursor c1(p_name varchar2) is
2837 select user_id from fnd_user
2838 where user_name=p_name;
2839
2840 l_user_id number;
2841 l_p_value varchar2(3);
2842 --
2843 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'mute_signer';
2844 --
2845
2846 begin
2847
2848 IF (l_debug = 'Y') THEN
2849 okc_debug.Set_Indentation(l_proc);
2850 okc_debug.Log('10: Entering ',2);
2851 END IF;
2852
2853 if (funcmode = 'RUN') then
2854 open c1(l_user_name);
2855 fetch c1 into l_user_id;
2856 close c1;
2857 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2858 (NAME => G_MUTE_PROFILE||'_'||l_ntf_type,
2859 USER_ID => l_user_id,
2860 RESPONSIBILITY_ID=> NULL,
2861 APPLICATION_ID => NULL);
2862 if (l_p_value is NULL) then
2863 l_p_value := FND_PROFILE.VALUE_SPECIFIC
2864 (NAME => G_MUTE_PROFILE,
2865 USER_ID => l_user_id,
2866 RESPONSIBILITY_ID=> NULL,
2867 APPLICATION_ID => NULL);
2868 end if;
2869 if (l_p_value is NULL or l_p_value='N') then
2870 -- UnMute Recipient
2871 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2872 itemkey => itemkey,
2873 aname => '.MAIL_QUERY',
2874 avalue => ' ');
2875 else
2876 -- Suppress Recipient (Mute)
2877 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2878 itemkey => itemkey,
2879 aname => '.MAIL_QUERY',
2880 avalue => l_user_name);
2881 end if;
2882 end if;
2883 if (funcmode = 'CANCEL') then
2884 --
2885 resultout := 'COMPLETE:';
2886
2887 IF (l_debug = 'Y') THEN
2888 okc_debug.Log('100: Leaving ',2);
2889 okc_debug.Reset_Indentation;
2890 END IF;
2891
2892
2893 return;
2894 --
2895 end if;
2896 --
2897 -- TIMEOUT mode
2898 --
2899 if (funcmode = 'TIMEOUT') then
2900 --
2901 resultout := 'COMPLETE:';
2902
2903 IF (l_debug = 'Y') THEN
2904 okc_debug.Log('200: Leaving ',2);
2905 okc_debug.Reset_Indentation;
2906 END IF;
2907
2908
2909 return;
2910 --
2911 end if;
2912
2913 IF (l_debug = 'Y') THEN
2914 okc_debug.Log('1000: Leaving ',2);
2915 okc_debug.Reset_Indentation;
2916 END IF;
2917
2918
2919 exception
2920 when others then
2921 wf_core.context(G_PKG_NAME,
2922 'MUTE_SIGNER',
2923 itemtype,
2924 itemkey,
2925 to_char(actid),
2926 funcmode);
2927 IF (l_debug = 'Y') THEN
2928 okc_debug.Log('2000: Leaving ',2);
2929 okc_debug.Reset_Indentation;
2930 END IF;
2931 raise;
2932 end mute_signer;
2933
2934 -- Enables sending of email notifications
2935 --
2936 procedure unmute( itemtype in varchar2,
2937 itemkey in varchar2,
2938 actid in number,
2939 funcmode in varchar2,
2940 resultout out nocopy varchar2 ) is
2941
2942 --
2943 l_proc varchar2(72) := ' OKC_ASYNC_PVT.'||'unmute';
2944 --
2945
2946 begin
2947
2948 IF (l_debug = 'Y') THEN
2949 okc_debug.Set_Indentation(l_proc);
2950 okc_debug.Log('10: Entering ',2);
2951 END IF;
2952
2953 if (funcmode = 'RUN') then
2954
2955 -- UnMute Recipient
2956 wf_engine.SetItemAttrText (itemtype => itemtype, -- may need to hard code value OKCAUKAP
2957 itemkey => itemkey,
2958 aname => '.MAIL_QUERY',
2959 avalue => ' ');
2960 end if;
2961 if (funcmode = 'CANCEL') then
2962 --
2963 resultout := 'COMPLETE:';
2964
2965 IF (l_debug = 'Y') THEN
2966 okc_debug.Log('100: Leaving ',2);
2967 okc_debug.Reset_Indentation;
2968 END IF;
2969
2970
2971 return;
2972 --
2973 end if;
2974 --
2975 -- TIMEOUT mode
2976 --
2977 if (funcmode = 'TIMEOUT') then
2978 --
2979 resultout := 'COMPLETE:';
2980
2981 IF (l_debug = 'Y') THEN
2982 okc_debug.Log('200: Leaving ',2);
2983 okc_debug.Reset_Indentation;
2984 END IF;
2985
2986
2987 return;
2988 --
2989 end if;
2990
2991 IF (l_debug = 'Y') THEN
2992 okc_debug.Log('1000: Leaving ',2);
2993 okc_debug.Reset_Indentation;
2994 END IF;
2995
2996
2997 exception
2998 when others then
2999 wf_core.context(G_PKG_NAME,
3000 'UNMUTE',
3001 itemtype,
3002 itemkey,
3003 to_char(actid),
3004 funcmode);
3005 IF (l_debug = 'Y') THEN
3006 okc_debug.Log('2000: Leaving ',2);
3007 okc_debug.Reset_Indentation;
3008 END IF;
3009 raise;
3010 end unmute;
3011
3012
3013 begin
3014 G_WF_NAME := 'OKCALERT';
3015 G_PROCESS_NAME := 'ALERT_PROCESS';
3016 end OKC_ASYNC_PVT;