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