[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_WF
Source
1 PACKAGE BODY OKL_AM_WF AS
2 /* $Header: OKLRAWFB.pls 120.17 2007/12/17 11:25:15 ansethur noship $ */
3
4 -- GLOBAL VARIABLES
5 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
6 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
7 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
8 G_MODULE_NAME CONSTANT VARCHAR2(500) := 'okl.am.plsql.okl_am_wf.';
9
10 SUBTYPE p_bind_var_tbl IS JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
11 SUBTYPE p_bind_val_tbl IS JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
12 SUBTYPE p_bind_type_tbl IS JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
13
14 -- Start of comments
15 --
16 -- Procedure Name : set_wf_launch_message
17 -- Description : Sets the message to display workflow and process called.
18 -- Business Rules :
19 -- Parameters : p_event_name, p_event_key
20 -- Version : 1.0
21 --
22 -- End of comments
23 PROCEDURE set_wf_launch_message (p_event_name IN VARCHAR2,
24 p_event_key IN VARCHAR2) AS
25
26 -- Selects the workfow and process details
27 CURSOR c_get_wf_details_csr (c_event_name VARCHAR2)
28 IS
29 SELECT IT.display_name
30 , RP.display_name
31 FROM WF_EVENTS WFEV,
32 WF_EVENT_SUBSCRIPTIONS WFES,
33 wf_runnable_processes_v RP,
34 wf_item_types_vl IT
35 WHERE WFEV.guid = WFES.event_filter_guid
36 AND WFES.WF_PROCESS_TYPE = RP.ITEM_TYPE
37 AND WFES.WF_PROCESS_NAME = RP.PROCESS_NAME
38 AND RP.ITEM_TYPE = IT.NAME
39 AND WFEV.NAME = c_event_name;
40
41 l_wf_desc VARCHAR2(100);
42 l_process_desc VARCHAR2(100);
43
44 -- for debug logging
45 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_wf_launch_message';
46 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
47 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
48 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
49
50 BEGIN
51
52 IF (is_debug_procedure_on) THEN
53 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
54 END IF;
55
56 OPEN c_get_wf_details_csr(p_event_name);
57 FETCH c_get_wf_details_csr INTO l_wf_desc, l_process_desc;
58 IF c_get_wf_details_csr%found THEN
59
60 OKL_API.set_message(p_app_name => 'OKL',
61 p_msg_name => 'OKL_AM_WF_LAUNCH_MSG',
62 p_token1 => 'ITEM_DESC',
63 p_token1_value => l_wf_desc,
64 p_token2 => 'PROCESS_DESC',
65 p_token2_value => l_process_desc,
66 p_token3 => 'EVENT_KEY',
67 p_token3_value => p_event_key);
68 END IF;
69 CLOSE c_get_wf_details_csr;
70
71 IF (is_debug_procedure_on) THEN
72 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
73 END IF;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 IF c_get_wf_details_csr%ISOPEN THEN
78 CLOSE c_get_wf_details_csr;
79 END IF;
80
81 END set_wf_launch_message;
82
83 -- Start of comments
84 --
85 -- Procedure Name : raise_business_event
86 -- Description : Generic procedure for raising business events
87 -- Business Rules :
88 -- Parameters : p_transaction_id, p_event_name
89 -- Version : 1.0
90 --
91 -- End of comments
92 PROCEDURE raise_business_event (p_transaction_id IN NUMBER,
93 p_event_name IN VARCHAR2) AS
94
95 l_parameter_list wf_parameter_list_t;
96 l_key VARCHAR2(240);
97 l_seq NUMBER;
98
99
100 -- Selects the nextval from sequence, used later for defining event key
101 CURSOR okl_key_csr IS
102 SELECT okl_wf_item_s.nextval
103 FROM dual;
104
105 -- for debug logging
106 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'raise_business_event';
107 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
108 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
109 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
110 BEGIN
111
112 IF (is_debug_procedure_on) THEN
113 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
114 END IF;
115
116 SAVEPOINT raise_event;
117
118 OPEN okl_key_csr;
119 FETCH okl_key_csr INTO l_seq;
120 CLOSE okl_key_csr;
121
122 l_key := p_event_name ||l_seq ;
123
124 wf_event.AddParameterToList('TRANSACTION_ID',p_transaction_id,l_parameter_list);
125 --added by akrangan as part of MOAC changes
126 if p_event_name in ('oracle.apps.okl.am.approveassetrepair',
127 'oracle.apps.okl.am.acceptrestquote',
128 'oracle.apps.okl.am.preproceeds',
129 'oracle.apps.okl.am.postproceeds',
130 'oracle.apps.okl.am.submitquoteforapproval',
131 'oracle.apps.okl.am.remkcustomflow',
132 'oracle.apps.okl.am.notifycollections',
133 'oracle.apps.okl.am.notifyremarketer',
134 'oracle.apps.okl.am.notifyrepoagent',
135 'oracle.apps.okl.am.notifytitleholder',
136 'oracle.apps.okl.am.approvecontportfolio',
137 'oracle.apps.okl.am.notifyportexe',
138 'oracle.apps.okl.am.notifyshipinstr',
139 'oracle.apps.okl.am.notifytransdept' ,
140 'oracle.apps.okl.am.sendquote'
141 )
142 then
143 --added by akrangan
144 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
145 end if ;
146 -- Raise Event
147 wf_event.raise(p_event_name => p_event_name
148 ,p_event_key => l_key
149 ,p_parameters => l_parameter_list);
150 l_parameter_list.DELETE;
151
152 -- Set Launch Message
153 set_wf_launch_message(p_event_name => p_event_name,
154 p_event_key => l_key);
155
156 IF (is_debug_procedure_on) THEN
157 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
158 END IF;
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
163 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
164 FND_MSG_PUB.ADD;
165
166 IF okl_key_csr%ISOPEN THEN
167 CLOSE okl_key_csr;
168 END IF;
169
170 ROLLBACK TO raise_event;
171
172 END raise_business_event;
173
174 -- Start of comments
175 --
176 -- Procedure Name : raise_fulfillment_event
177 -- Description : Generic procedure for raising fulfillment business events
178 -- Business Rules :
179 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
180 -- Version : 1.0
181 --
182 -- End of comments
183 PROCEDURE raise_fulfillment_event (
184 itemtype IN VARCHAR2
185 , itemkey IN VARCHAR2
186 , actid IN NUMBER
187 , funcmode IN VARCHAR2
188 , resultout OUT NOCOPY VARCHAR2) IS
189
190 l_parameter_list wf_parameter_list_t;
191 l_key VARCHAR2(240);
192 l_event_name VARCHAR2(240) := 'oracle.apps.okl.am.notifyexternalparty' ;
193 l_seq NUMBER;
194
195 l_transaction_id VARCHAR2(100);
196 l_process_code VARCHAR2(100);
197 l_recipient_type VARCHAR2(10);
198 l_recipient_id VARCHAR2(100);
199 l_recipient_desc VARCHAR2(1000);
200 l_created_by NUMBER;
201 l_expand_roles VARCHAR2(1);
202 l_email_address VARCHAR2(100);
203 --19-jul-2007 ansethur R12B XML Publisher starts
204 l_batch_id number;-- Varchar2(100);
205 l_from_address VARCHAR2(100);
206 --19-jul-2007 ansethur R12B XML Publisher ends
207
208 -- Selects the nextval from sequence, used later for defining event key
209 CURSOR okl_key_csr IS
210 SELECT okl_wf_item_s.nextval
211 FROM dual;
212
213 -- for debug logging
214 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'raise_fulfillment_event';
215 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
216 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
217 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
218
219 BEGIN
220
221 IF (is_debug_procedure_on) THEN
222 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
223 END IF;
224
225 IF (funcmode = 'RUN') THEN
226
227 SAVEPOINT raise_fulfillment_event;
228
229 l_transaction_id := wf_engine.GetItemAttrText( itemtype => itemtype,
230 itemkey => itemkey,
231 aname => 'TRANSACTION_ID');
232
233 l_process_code := wf_engine.GetItemAttrText( itemtype => itemtype,
234 itemkey => itemkey,
235 aname => 'PROCESS_CODE');
236
237 l_recipient_type := wf_engine.GetItemAttrText( itemtype => itemtype,
238 itemkey => itemkey,
239 aname => 'RECIPIENT_TYPE');
240
241 l_recipient_id := wf_engine.GetItemAttrText( itemtype => itemtype,
242 itemkey => itemkey,
243 aname => 'RECIPIENT_ID');
244
245 l_recipient_desc := wf_engine.GetItemAttrText( itemtype => itemtype,
246 itemkey => itemkey,
247 aname => 'RECIPIENT_DESCRIPTION');
248
249 l_created_by := wf_engine.GetItemAttrNumber( itemtype => itemtype,
250 itemkey => itemkey,
251 aname => 'CREATED_BY');
252
253 l_email_address := wf_engine.GetItemAttrText( itemtype => itemtype,
254 itemkey => itemkey,
255 aname => 'EMAIL_ADDRESS');
256
257 --19-jul-2007 ansethur R12B XML Publisher starts
258 l_from_address := wf_engine.GetItemAttrText( itemtype => itemtype,
259 itemkey => itemkey,
260 aname => 'FROM_ADDRESS');
261 l_batch_id := wf_engine.GetItemAttrNumber( itemtype => itemtype,
262 itemkey => itemkey,
263 aname => 'BATCH_ID');
264 --19-jul-2007 ansethur R12B XML Publisher end
265 OPEN okl_key_csr;
266 FETCH okl_key_csr INTO l_seq;
267 CLOSE okl_key_csr;
268
269 l_key := l_event_name ||l_seq ;
270
271 wf_event.AddParameterToList('TRANSACTION_ID',l_transaction_id,l_parameter_list);
272 wf_event.AddParameterToList('PROCESS_CODE',l_process_code,l_parameter_list);
273 wf_event.AddParameterToList('RECIPIENT_TYPE',l_recipient_type,l_parameter_list);
274 wf_event.AddParameterToList('RECIPIENT_ID',l_recipient_id,l_parameter_list);
275 wf_event.AddParameterToList('RECIPIENT_DESCRIPTION',l_recipient_desc,l_parameter_list);
276 wf_event.AddParameterToList('CREATED_BY',l_created_by,l_parameter_list);
277 -- wf_event.AddParameterToList('EXPAND_ROLES',l_expand_roles,l_parameter_list);
278 wf_event.AddParameterToList('EMAIL_ADDRESS',l_email_address,l_parameter_list);
279 --19-jul-2007 ansethur R12B XML Publisher Starts
280 wf_event.AddParameterToList('BATCH_ID',l_batch_id,l_parameter_list);
281 wf_event.AddParameterToList('FROM_ADDRESS',l_from_address,l_parameter_list);
282 --19-jul-2007 ansethur R12B XML Publisher Ends
283 --added by akrangan
284 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
285
286
287 -- Raise Event
288 wf_event.raise(p_event_name => l_event_name
289 ,p_event_key => l_key
290 ,p_parameters => l_parameter_list);
291 l_parameter_list.DELETE;
292
293 END IF;
294
295 IF (funcmode = 'CANCEL') THEN
296 --
297 resultout := 'COMPLETE:';
298 RETURN;
299 --
300 END IF;
301 --
302 -- TIMEOUT mode
303 --
304 IF (funcmode = 'TIMEOUT') THEN
305 --
306 resultout := 'COMPLETE:';
307 RETURN;
308 --
309 END IF;
310
311 IF (is_debug_procedure_on) THEN
312 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
313 END IF;
314
315
316 EXCEPTION
317 WHEN OTHERS THEN
318 FND_MESSAGE.SET_NAME('OKL', 'OKL_API_OTHERS_EXCEP');
319 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
320 FND_MSG_PUB.ADD;
321
322 IF okl_key_csr%ISOPEN THEN
323 CLOSE okl_key_csr;
324 END IF;
325
326 ROLLBACK TO raise_fulfillment_event;
327
328 END raise_fulfillment_event;
329
330 -- Start of comments
331 --
332 -- Procedure Name : call_am_fulfillment
333 -- Description : Called from any WF process to execute a fulfillment request
334 -- Business Rules :
335 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
336 -- Version : 1.0
337 --
338 -- End of comments
339 PROCEDURE CALL_AM_FULFILLMENT( itemtype IN VARCHAR2,
340 itemkey IN VARCHAR2,
341 actid IN NUMBER,
342 funcmode IN VARCHAR2,
343 resultout OUT NOCOPY VARCHAR2 )IS
344
345 l_transaction_id VARCHAR2(100);
346 l_trans_id NUMBER;
347 l_process_code VARCHAR2(100);
348 l_recipient_type VARCHAR2(10);
349 l_recipient_id VARCHAR2(100);
350 l_recipient_desc VARCHAR2(1000);
351 l_created_by NUMBER;
352 l_expand_roles VARCHAR2(1);
353
354 l_mesg_count NUMBER := 0;
355 l_mesg_text VARCHAR2(4000);
356 l_mesg_len NUMBER;
357
358 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
359 l_msg_count NUMBER;
360 l_msg_data VARCHAR2(30000);
361
362 l_status_message VARCHAR2(30000);
363
364 l_notification_agent VARCHAR2(100);
365 l_desc VARCHAR2(100);
366
367 l_mt_bind_names p_bind_var_tbl;
368 l_mt_bind_values p_bind_val_tbl;
369 l_mt_bind_types p_bind_type_tbl;
370
371 l_email_address VARCHAR(100);
372 lx_error_rec OKL_API.error_rec_type;
373 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
374
375 -- for debug logging
376 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'call_am_fulfillment';
377 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
378 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
379 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
380
381 BEGIN
382
383 IF (is_debug_procedure_on) THEN
384 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
385 END IF;
386
387 l_mt_bind_names(1) := '';
388 l_mt_bind_values(1) := '';
389 l_mt_bind_types(1) := '';
390
391 IF (funcmode = 'RUN') THEN
392
393 SAVEPOINT call_fulfillment;
394
395 l_transaction_id := wf_engine.GetItemAttrText( itemtype => itemtype,
396 itemkey => itemkey,
397 aname => 'TRANSACTION_ID');
398
399 l_process_code := wf_engine.GetItemAttrText( itemtype => itemtype,
400 itemkey => itemkey,
401 aname => 'PROCESS_CODE');
402
403 l_recipient_type := wf_engine.GetItemAttrText( itemtype => itemtype,
404 itemkey => itemkey,
405 aname => 'RECIPIENT_TYPE');
406
407 l_recipient_id := wf_engine.GetItemAttrText( itemtype => itemtype,
408 itemkey => itemkey,
409 aname => 'RECIPIENT_ID');
410
411 l_recipient_desc := wf_engine.GetItemAttrText( itemtype => itemtype,
412 itemkey => itemkey,
413 aname => 'RECIPIENT_DESCRIPTION');
414
415 l_created_by := wf_engine.GetItemAttrNumber( itemtype => itemtype,
416 itemkey => itemkey,
417 aname => 'CREATED_BY');
418
419 l_expand_roles := wf_engine.GetItemAttrText( itemtype => itemtype,
420 itemkey => itemkey,
421 aname => 'EXPAND_ROLES');
422
423 l_email_address := wf_engine.GetItemAttrText( itemtype => itemtype,
424 itemkey => itemkey,
425 aname => 'EMAIL_ADDRESS');
426
427 OKL_AM_UTIL_PVT.EXECUTE_FULFILLMENT_REQUEST (
428 p_api_version => 1
429 , p_init_msg_list => FND_API.G_FALSE
430 , x_return_status => l_return_status
431 , x_msg_count => l_msg_count
432 , x_msg_data => l_msg_data
433 , p_ptm_code => l_process_code
434 , p_agent_id => l_created_by
435 , p_transaction_id => l_transaction_id
436 , p_recipient_type => l_recipient_type
437 , p_recipient_id => l_recipient_id
438 , p_expand_roles => l_expand_roles
439 , p_pt_bind_names => l_mt_bind_names
440 , p_pt_bind_values => l_mt_bind_values
441 , p_pt_bind_types => l_mt_bind_types
442 , p_recipient_email => l_email_address
443 -- , p_commit => FND_API.G_FALSE
444 );
445 IF (is_debug_statement_on) THEN
446 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,L_MODULE_NAME,
447 'after call to okl_am_util_pvt.execute_fulfillment_request :'||l_return_status);
448 END IF;
449
450 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
451
452 l_status_message := ' ';
453 LOOP
454
455 fnd_msg_pub.get(
456 p_msg_index => l_msg_idx,
457 p_encoded => FND_API.G_FALSE,
458 p_data => lx_error_rec.msg_data,
459 p_msg_index_out => lx_error_rec.msg_count);
460
461 IF (lx_error_rec.msg_count IS NOT NULL) THEN
462
463 IF LENGTH(l_status_message) + LENGTH(lx_error_rec.msg_data) < 30000 THEN
464 l_status_message := l_status_message||' '||lx_error_rec.msg_data;
465 END IF;
466
467 END IF;
468
469 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
470 OR (lx_error_rec.msg_count IS NULL));
471
472 l_msg_idx := FND_MSG_PUB.G_NEXT;
473
474 END LOOP;
475
476 wf_engine.SetItemAttrText ( itemtype=> itemtype,
477 itemkey => itemkey,
478 aname => 'STATUS_MESSAGE',
479 avalue => l_status_message);
480
481 -- Locates the user name for the requestor, performing agent for notifications
482 okl_am_wf.get_notification_agent(
483 itemtype => itemtype
484 , itemkey => itemkey
485 , actid => actid
486 , funcmode => funcmode
487 , p_user_id => l_created_by
488 , x_name => l_notification_agent
489 , x_description => l_desc);
490
491 wf_engine.SetItemAttrText ( itemtype=> itemtype,
492 itemkey => itemkey,
493 aname => 'NOTIFY_AGENT',
494 avalue => l_notification_agent);
495
496 wf_engine.SetItemAttrText ( itemtype=> itemtype,
497 itemkey => itemkey,
498 aname => 'RECIPIENT_DESCRIPTION',
499 avalue => l_notification_agent);
500
501 END IF;
502
503
504 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
505 resultout := 'COMPLETE:ERROR';
506 ELSE
507 resultout := 'COMPLETE:SUCCESS';
508 END IF;
509
510 RETURN ;
511
512 END IF;
513 --
514 -- CANCEL mode
515 --
516 IF (funcmode = 'CANCEL') THEN
517 --
518 resultout := 'COMPLETE:';
519 RETURN;
520 --
521 END IF;
522 --
523 -- TIMEOUT mode
524 --
525 IF (funcmode = 'TIMEOUT') THEN
526 --
527 resultout := 'COMPLETE:';
528 RETURN;
529 --
530 END IF;
531
532 IF (is_debug_procedure_on) THEN
533 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
534 END IF;
535
536
537 EXCEPTION
538 WHEN OTHERS THEN
539
540 ROLLBACK TO call_fulfillment;
541
542 wf_core.context('OKL_AM_WF' , 'CALL_AM_FULFILLMENT', itemtype, itemkey, actid, funcmode);
543 RAISE;
544
545 END CALL_AM_FULFILLMENT;
546
547 -- Start of comments
548 --
549 -- Procedure Name : start_approval_process
550 -- Description : Called from any WF where AM Approvals WF needs to be launched
551 -- Business Rules :
552 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
553 -- Version : 1.0
554 --
555 -- End of comments
556 PROCEDURE START_APPROVAL_PROCESS( itemtype IN VARCHAR2,
557 itemkey IN VARCHAR2,
558 actid IN NUMBER,
559 funcmode IN VARCHAR2,
560 resultout OUT NOCOPY VARCHAR2 )IS
561
562 -- Selects the nextval from sequence, used later for defining event key
563 CURSOR okl_key_csr IS
564 SELECT okl_wf_item_s.nextval
565 FROM dual;
566
567 l_key VARCHAR2(240);
568 l_seq NUMBER;
569 l_itemtype VARCHAR2(30) := 'OKLAMAPP';
570 l_process VARCHAR2(30) := 'APPROVAL_PROC';
571 l_parent_trx_id VARCHAR2(240);
572 l_parent_trx_type VARCHAR2(240);
573 l_requester VARCHAR2(30);
574 l_mess_desc VARCHAR2(4000);
575
576 -- 19-NOV-03 MDOKAL -- Bug 3262184
577 CURSOR check_ia_exists_csr(c_item_type VARCHAR2) IS
578 SELECT name
579 FROM wf_item_attributes
580 WHERE item_type = c_item_type
581 AND name IN ('APP_REQUEST_SUB' ,'APP_REMINDER_SUB' ,'APP_APPROVED_SUB',
582 'APP_REJECTED_SUB','APP_REMINDER_HEAD','APP_APPROVED_HEAD',
583 'APP_REJECTED_HEAD') ;
584 -- smadhava - Bug#5235038 - Added - Start
585 l_msg_doc VARCHAR2(4000);
586 -- smadhava - Bug#5235038 - Added - End
587
588 --dkagrawa -Bug#5256290 start
589 invalid_attr EXCEPTION;
590 PRAGMA EXCEPTION_INIT(invalid_attr, -20002);
591 --dkagrawa -Bug#5256290 end
592 -- for debug logging
593 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'start_approval_process';
594 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
595 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
596 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
597 BEGIN
598
599 IF (is_debug_procedure_on) THEN
600 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
601 END IF;
602
603 OPEN okl_key_csr;
604 FETCH okl_key_csr INTO l_seq;
605 CLOSE okl_key_csr;
606
607 l_key := l_itemtype ||l_seq ;
608
609 IF (funcmode = 'RUN') THEN
610
611
612 wf_engine.CreateProcess(itemtype => l_itemtype,
613 itemkey => l_key,
614 process => l_process);
615
616
617 wf_engine.SetItemParent(itemtype => l_itemtype,
618 itemkey => l_key,
619 parent_itemtype => itemtype,
620 parent_itemkey => itemkey,
621 parent_context => 'MASTER');
622
623 l_parent_trx_id := wf_engine.GetItemAttrText (
624 itemtype => itemtype,
625 itemkey => itemkey,
626 aname => 'TRANSACTION_ID');
627
628 wf_engine.SetItemAttrText (
629 itemtype => l_itemtype,
630 itemkey => l_key,
631 aname => 'TRANSACTION_ID',
632 avalue => l_parent_trx_id);
633
634 l_parent_trx_type := wf_engine.GetItemAttrText (
635 itemtype => itemtype,
636 itemkey => itemkey,
637 aname => 'TRX_TYPE_ID');
638
639 l_mess_desc := wf_engine.GetItemAttrText (
640 itemtype => itemtype,
641 itemkey => itemkey,
642 aname => 'MESSAGE_DESCRIPTION');
643
644 --dkagrawa -Bug#5256290 added exception handling start
645 BEGIN
646 -- smadhava - Bug#5235038 - Added - Start
647 -- Get the MESSAGE_DOC attribute value set in the original Workflow
648 l_msg_doc := wf_engine.GetItemAttrText(
649 itemtype => itemtype
650 , itemkey => itemkey
651 , aname => 'MESSAGE_DOC');
652 -- smadhava - Bug#5235038 - Added - End
653 EXCEPTION
654 WHEN invalid_attr THEN
655 l_msg_doc := null;
656 END;
657 --dkagrawa -Bug#5256290 end
658
659 l_requester := wf_engine.GetItemAttrText (
660 itemtype => itemtype,
661 itemkey => itemkey,
662 aname => 'REQUESTER');
663
664 wf_engine.SetItemAttrText (
665 itemtype => l_itemtype,
666 itemkey => l_key,
667 aname => 'TRX_TYPE_ID',
668 avalue => l_parent_trx_type);
669
670 wf_engine.SetItemAttrText (
671 itemtype => l_itemtype,
672 itemkey => l_key,
673 aname => 'PARENT_ITEM_KEY',
674 avalue => itemkey);
675
676 wf_engine.SetItemAttrText (
677 itemtype => l_itemtype,
678 itemkey => l_key,
679 aname => 'PARENT_ITEM_TYPE',
680 avalue => itemtype);
681
682 wf_engine.SetItemAttrText (
683 itemtype => l_itemtype,
684 itemkey => l_key,
685 aname => 'MESSAGE_DESCRIPTION',
686 avalue => l_mess_desc);
687
688 wf_engine.SetItemAttrText (
689 itemtype => l_itemtype,
690 itemkey => l_key,
691 aname => 'REQUESTER',
692 avalue => l_requester);
693
694 wf_engine.SetItemAttrText (
695 itemtype => l_itemtype,
696 itemkey => l_key,
697 aname => 'WF_ADMINISTRATOR',
698 avalue => l_requester);
699
700 -- MDOKAL, 20-MAR-2003 Bug 2862254
701 -- Added the following logic to populate the default value of the
702 -- document type item attribute.
703 -- smadhava - Bug#5235038 - Modified - Start
704 -- Check if the MESSAGE_DOC has already been set. If so donot modify.
705 -- Else assign the call to pop_approval_doc to assign the MESSAGE_DESCRIPTION
706 IF l_msg_doc IS NULL THEN
707 l_msg_doc := 'plsql:okl_am_wf.pop_approval_doc/'||l_key;
708 END IF;
709 wf_engine.SetItemAttrText (
710 itemtype => l_itemtype,
711 itemkey => l_key,
712 aname => 'MESSAGE_DOC',
713 avalue => l_msg_doc);
714 -- smadhava - Bug#5235038 - Modified - End
715 -- 19-NOV-03 MDOKAL -- Bug 3262184
716 -- process optional parameters
717 FOR ia_rec IN check_ia_exists_csr(itemtype) LOOP
718
719 wf_engine.SetItemAttrText (
720 itemtype => l_itemtype,
721 itemkey => l_key,
722 aname => ia_rec.name,
723 avalue => wf_engine.GetItemAttrText(
724 itemtype => itemtype,
725 itemkey => itemkey,
726 aname => ia_rec.name)
727 );
728 END LOOP;
729
730 wf_engine.StartProcess(itemtype => l_itemtype,
731 itemkey => l_key);
732
733 resultout := 'COMPLETE:';
734 RETURN;
735
736 END IF;
737 --
738 -- CANCEL mode
739 --
740 IF (funcmode = 'CANCEL') THEN
741 --
742 resultout := 'COMPLETE:';
743 RETURN;
744 --
745 END IF;
746 --
747 -- TIMEOUT mode
748 --
749 IF (funcmode = 'TIMEOUT') THEN
750 --
751 resultout := 'COMPLETE:';
752 RETURN;
753 --
754 END IF;
755
756 IF (is_debug_procedure_on) THEN
757 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
758 END IF;
759
760
761 EXCEPTION
762 WHEN OTHERS THEN
763
764 IF okl_key_csr%ISOPEN THEN
765 CLOSE okl_key_csr;
766 END IF;
767
768 wf_core.context('OKL_AM_WF' , 'START_APPROVAL_PROCESS', itemtype, itemkey, actid, funcmode);
769 RAISE;
770
771 END START_APPROVAL_PROCESS;
772
773 -- Start of comments
774 --
775 -- Procedure Name : set_parent_attributes
776 -- Description : Called from the Generic Approvals WF for setting the approval outcome
777 -- for the parent WF.
778 -- Business Rules :
779 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
780 -- Version : 1.0
781 --
782 -- End of comments
783 PROCEDURE SET_PARENT_ATTRIBUTES( itemtype IN VARCHAR2,
784 itemkey IN VARCHAR2,
785 actid IN NUMBER,
786 funcmode IN VARCHAR2,
787 resultout OUT NOCOPY VARCHAR2 )IS
788
789 l_parent_key VARCHAR2(240);
790 l_parent_type VARCHAR2(240);
791 l_approved_yn VARCHAR2(240);
792 l_transaction_id VARCHAR2(100);
793
794 -- for debug logging
795 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_parent_attributes';
796 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
797 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
798 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
799
800 BEGIN
801
802 IF (is_debug_procedure_on) THEN
803 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
804 END IF;
805
806 -- SAVEPOINT set_atts;
807
808 IF (funcmode = 'RUN') THEN
809
810
811 -- Get parent information from Approvals WF
812
813 l_parent_key := wf_engine.GetItemAttrText (
814 itemtype => itemtype,
815 itemkey => itemkey,
816 aname => 'PARENT_ITEM_KEY');
817
818 l_parent_type := wf_engine.GetItemAttrText (
819 itemtype => itemtype,
820 itemkey => itemkey,
821 aname => 'PARENT_ITEM_TYPE');
822
823
824 -- Get Approved flag
825 l_approved_yn := wf_engine.GetItemAttrText (
826 itemtype => itemtype,
827 itemkey => itemkey,
828 aname => 'RESULT');
829
830 -- Set the parent attribute(s)
831
832 if l_approved_yn = 'APPROVED' then
833 l_approved_yn := 'Y';
834 else
835 l_approved_yn := 'N';
836 end if;
837
838
839 wf_engine.SetItemAttrText (
840 itemtype => l_parent_type,
841 itemkey => l_parent_key,
842 aname => 'APPROVED_YN',
843 avalue => l_approved_yn);
844
845 l_transaction_id := wf_engine.GetItemAttrText (
846 itemtype => l_parent_type,
847 itemkey => l_parent_key,
848 aname => 'TRANSACTION_ID');
849
850 -- ensure the the statuses have been cleared out for reuse.
851 update ame_temp_old_approver_lists
852 set approval_status = null
853 where transaction_id = l_transaction_id;
854
855
856 resultout := 'COMPLETE:';
857 RETURN;
858
859 END IF;
860 --
861 -- CANCEL mode
862 --
863 IF (funcmode = 'CANCEL') THEN
864 --
865 resultout := 'COMPLETE:';
866 RETURN;
867 --
868 END IF;
869 --
870 -- TIMEOUT mode
871 --
872 IF (funcmode = 'TIMEOUT') THEN
873 --
874 resultout := 'COMPLETE:';
875 RETURN;
876 --
877 END IF;
878
879 IF (is_debug_procedure_on) THEN
880 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
881 END IF;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 wf_core.context('OKL_AM_WF' , 'SET_PARENT_ATTRIBUTES', itemtype, itemkey, actid, funcmode);
886 RAISE;
887
888 END SET_PARENT_ATTRIBUTES;
889
890 -- Start of comments
891 --
892 -- Procedure Name : validate_approval_request
893 -- Description : Called from the Generic Approvals WF for validating approval request
894 -- Business Rules :
895 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
896 -- Version : 1.0
897 --
898 -- End of comments
899 PROCEDURE VALIDATE_APPROVAL_REQUEST( itemtype IN VARCHAR2,
900 itemkey IN VARCHAR2,
901 actid IN NUMBER,
902 funcmode IN VARCHAR2,
903 resultout OUT NOCOPY VARCHAR2 )IS
904
905 l_trx_type VARCHAR2(1000);
906 l_app_id NUMBER;
907 l_knt NUMBER;
908 l_parent_type VARCHAR2(300);
909 l_parent_key VARCHAR2(300);
910
911 -- Get the valid application id from FND
912 CURSOR c_get_app_id_csr
913 IS
914 SELECT APPLICATION_ID
915 FROM FND_APPLICATION
916 WHERE APPLICATION_SHORT_NAME = 'OKL';
917
918 -- Validate the Transaction Type Id from OAM
919 CURSOR c_validate_trx_type_csr(c_trx_type VARCHAR2)
920 IS
921 SELECT count(*)
922 FROM AME_CALLING_APPS
923 WHERE TRANSACTION_TYPE_ID = c_trx_type;
924 /*
925 CURSOR c_get_parent_key_csr(c_itemtype VARCHAR2,
926 c_itemkey VARCHAR2)
927 IS
928 SELECT PARENT_ITEM_TYPE, PARENT_ITEM_KEY
929 FROM WF_ITEMS
930 WHERE ITEM_TYPE = c_itemtype
931 AND ITEM_KEY = c_itemkey;
932 */
933
934 -- for debug logging
935 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'validate_approval_request';
936 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
937 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
938 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
939 BEGIN
940
941 IF (is_debug_procedure_on) THEN
942 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
943 END IF;
944
945 IF (funcmode = 'RUN') THEN
946
947 l_trx_type := wf_engine.GetItemAttrText (
948 itemtype => itemtype,
949 itemkey => itemkey,
950 aname => 'TRX_TYPE_ID');
951
952 OPEN c_validate_trx_type_csr(l_trx_type);
953 FETCH c_validate_trx_type_csr INTO l_knt;
954 CLOSE c_validate_trx_type_csr;
955
956 OPEN c_get_app_id_csr;
957 FETCH c_get_app_id_csr INTO l_app_id;
958 CLOSE c_get_app_id_csr;
959
960 IF l_knt <> 0 AND l_app_id IS NOT NULL THEN
961
962 wf_engine.SetItemAttrText (
963 itemtype => itemtype,
964 itemkey => itemkey,
965 aname => 'APPLICATION_ID',
966 avalue => l_app_id);
967
968 resultout := 'COMPLETE:VALID';
969 ELSE
970 resultout := 'COMPLETE:INVALID';
971 END IF;
972
973 RETURN;
974
975 END IF;
976 --
977 -- CANCEL mode
978 --
979 IF (funcmode = 'CANCEL') THEN
980 --
981 resultout := 'COMPLETE:';
982 RETURN;
983 --
984 END IF;
985 --
986 -- TIMEOUT mode
987 --
988 IF (funcmode = 'TIMEOUT') THEN
989 --
990 resultout := 'COMPLETE:';
991 RETURN;
992 --
993 END IF;
994
995 IF (is_debug_procedure_on) THEN
996 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
997 END IF;
998
999
1000 EXCEPTION
1001 WHEN OTHERS THEN
1002
1003 IF c_validate_trx_type_csr%ISOPEN THEN
1004 CLOSE c_validate_trx_type_csr;
1005 END IF;
1006
1007 IF c_get_app_id_csr%ISOPEN THEN
1008 CLOSE c_get_app_id_csr;
1009 END IF;
1010
1011 wf_core.context('OKL_AM_WF' , 'VALIDATE_APPROVAL_REQUEST', itemtype, itemkey, actid, funcmode);
1012 RAISE;
1013
1014 END VALIDATE_APPROVAL_REQUEST;
1015
1016 -- Start of comments
1017 --
1018 -- Procedure Name : get_approver
1019 -- Description : Called from the Generic Approvals WF and is recursively executed
1020 -- until all approvers have been located or until an approvwer
1021 -- rejects a request.
1022 -- Business Rules :
1023 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1024 -- Version : 1.0
1025 -- History : MSDOKAL - Created
1026 -- SGORANTL 28-DEC-05 4887809 : Modified to convert person id into user id
1027 -- before calling get_notification_agent
1028 --
1029 -- End of comments
1030 PROCEDURE GET_APPROVER( itemtype IN VARCHAR2,
1031 itemkey IN VARCHAR2,
1032 actid IN NUMBER,
1033 funcmode IN VARCHAR2,
1034 resultout OUT NOCOPY VARCHAR2 )IS
1035
1036
1037 l_trx_type VARCHAR2(240);
1038 l_app_id NUMBER;
1039 l_approver_rec ame_util.approverRecord;
1040 l_approver wf_users.name%type;
1041 l_name wf_users.description%type;
1042 l_transaction_id VARCHAR2(100);
1043
1044 l_result VARCHAR2(30);
1045
1046 l_user_id NUMBER; -- SGORANTL 28-DEC-05 4887809
1047 -- for debug logging
1048 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_approver';
1049 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1050 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1051 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1052 BEGIN
1053
1054 IF (is_debug_procedure_on) THEN
1055 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1056 END IF;
1057
1058 IF (funcmode = 'RUN') THEN
1059
1060 -- Get OAM parameter values from Approvals WF
1061 l_trx_type := wf_engine.GetItemAttrText (
1062 itemtype => itemtype,
1063 itemkey => itemkey,
1064 aname => 'TRX_TYPE_ID');
1065
1066 l_app_id := wf_engine.GetItemAttrText (
1067 itemtype => itemtype,
1068 itemkey => itemkey,
1069 aname => 'APPLICATION_ID');
1070
1071 l_transaction_id := wf_engine.GetItemAttrText (
1072 itemtype => itemtype,
1073 itemkey => itemkey,
1074 aname => 'TRANSACTION_ID');
1075
1076 -- Call OAM api to get approval details
1077 ame_api.getNextApprover(applicationIdIn => l_app_id,
1078 transactionIdIn => l_transaction_id,
1079 transactionTypeIn => l_trx_type,
1080 nextApproverOut => l_approver_rec);
1081
1082 IF l_approver_rec.person_id IS NOT NULL THEN -- populate attributes
1083
1084 -- SGORANTL 28-DEC-05 4887809 : convert person_id into user_id
1085 l_user_id := ame_util.personidtouserid(L_approver_rec.person_id);
1086
1087 okl_am_wf.get_notification_agent(
1088 itemtype => itemtype
1089 , itemkey => itemkey
1090 , actid => actid
1091 , funcmode => funcmode
1092 -- , p_user_id => l_approver_rec.person_id -- SGORANTL 28-DEC-05 4887809
1093 , p_user_id => l_user_id -- SGORANTL 28-DEC-05 4887809
1094 , x_name => l_approver
1095 , x_description => l_name);
1096
1097 wf_engine.SetItemAttrText (
1098 itemtype => itemtype,
1099 itemkey => itemkey,
1100 aname => 'PERFORMING_AGENT',
1101 avalue => l_approver);
1102
1103 wf_engine.SetItemAttrText (
1104 itemtype => itemtype,
1105 itemkey => itemkey,
1106 aname => 'FIRST_NAME',
1107 avalue => l_approver_rec.first_name);
1108
1109 wf_engine.SetItemAttrText (
1110 itemtype => itemtype,
1111 itemkey => itemkey,
1112 aname => 'LAST_NAME',
1113 avalue => l_approver_rec.last_name);
1114
1115 wf_engine.SetItemAttrNumber (
1116 itemtype => itemtype,
1117 itemkey => itemkey,
1118 aname => 'USER_ID',
1119 avalue => l_approver_rec.user_id);
1120
1121 wf_engine.SetItemAttrNumber (
1122 itemtype => itemtype,
1123 itemkey => itemkey,
1124 aname => 'PERSON_ID',
1125 avalue => l_approver_rec.person_id);
1126
1127 wf_engine.SetItemAttrText (
1128 itemtype => itemtype,
1129 itemkey => itemkey,
1130 aname => 'API_INSERTION',
1131 avalue => l_approver_rec.api_insertion);
1132
1133 wf_engine.SetItemAttrText (
1134 itemtype => itemtype,
1135 itemkey => itemkey,
1136 aname => 'AUTHORITY',
1137 avalue => l_approver_rec.authority);
1138
1139 resultout := 'COMPLETE:FOUND';
1140 ELSE
1141
1142 l_result := wf_engine.GetItemAttrText (
1143 itemtype => itemtype,
1144 itemkey => itemkey,
1145 aname => 'RESULT');
1146
1147 IF l_result IS NULL THEN
1148 -- There were no appovers, set RESULT to APPROVE
1149 wf_engine.SetItemAttrText (
1150 itemtype => itemtype,
1151 itemkey => itemkey,
1152 aname => 'RESULT',
1153 avalue => 'APPROVED');
1154 END IF;
1155
1156 resultout := 'COMPLETE:NOT_FOUND';
1157 END IF;
1158 RETURN;
1159
1160 END IF;
1161 --
1162 -- CANCEL mode
1163 --
1164 IF (funcmode = 'CANCEL') THEN
1165 --
1166 resultout := 'COMPLETE:';
1167 RETURN;
1168 --
1169 END IF;
1170 --
1171 -- TIMEOUT mode
1172 --
1173 IF (funcmode = 'TIMEOUT') THEN
1174 --
1175 resultout := 'COMPLETE:';
1176 RETURN;
1177 --
1178 END IF;
1179
1180 IF (is_debug_procedure_on) THEN
1181 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1182 END IF;
1183
1184
1185 EXCEPTION
1186 WHEN OTHERS THEN
1187
1188 wf_core.context('OKL_AM_WF' , 'GET_APPROVER', itemtype, itemkey, actid, funcmode);
1189 RAISE;
1190
1191 END GET_APPROVER;
1192
1193 -- Start of comments
1194 --
1195 -- Procedure Name : set_approval_status
1196 -- Description : Called from the Generic Approvals WF to set the approval status
1197 -- and is recursively executed
1198 -- until all approvers have been located or until an approvwer
1199 -- rejects a request.
1200 -- Business Rules :
1201 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1202 -- Version : 1.0
1203 --
1204 -- End of comments
1205 PROCEDURE SET_APPROVAL_STATUS( itemtype IN VARCHAR2,
1206 itemkey IN VARCHAR2,
1207 actid IN NUMBER,
1208 funcmode IN VARCHAR2,
1209 resultout OUT NOCOPY VARCHAR2 )IS
1210
1211 l_app_id NUMBER;
1212 l_trx_type VARCHAR2(100);
1213 l_approved_yn VARCHAR2(30);
1214 l_approver_rec ame_util.approverRecord;
1215 l_user_id NUMBER;
1216 l_transaction_id VARCHAR2(100);
1217 -- for debug logging
1218 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_approval_status';
1219 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1220 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1221 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1222 BEGIN
1223
1224 IF (is_debug_procedure_on) THEN
1225 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1226 END IF;
1227
1228 SAVEPOINT set_atts;
1229
1230 IF (funcmode = 'RUN') THEN
1231
1232 -- Get current approval status
1233 l_approved_yn := wf_engine.GetItemAttrText (
1234 itemtype => itemtype,
1235 itemkey => itemkey,
1236 aname => 'RESULT');
1237
1238 IF l_approved_yn = 'APPROVED' THEN
1239 l_approver_rec.approval_status := 'APPROVE';
1240 ELSE
1241 l_approver_rec.approval_status := 'REJECT';
1242 END IF;
1243
1244 -- All OAM attributes
1245
1246 l_transaction_id := wf_engine.GetItemAttrText (
1247 itemtype => itemtype,
1248 itemkey => itemkey,
1249 aname => 'TRANSACTION_ID');
1250
1251 l_trx_type := wf_engine.GetItemAttrText (
1252 itemtype => itemtype,
1253 itemkey => itemkey,
1254 aname => 'TRX_TYPE_ID');
1255
1256 l_app_id := wf_engine.GetItemAttrNumber (
1257 itemtype => itemtype,
1258 itemkey => itemkey,
1259 aname => 'APPLICATION_ID');
1260
1261 l_approver_rec.last_name := wf_engine.GetItemAttrText (
1262 itemtype => itemtype,
1263 itemkey => itemkey,
1264 aname => 'LAST_NAME');
1265
1266 l_approver_rec.first_name := wf_engine.GetItemAttrText (
1267 itemtype => itemtype,
1268 itemkey => itemkey,
1269 aname => 'FIRST_NAME');
1270
1271 l_user_id := wf_engine.GetItemAttrNumber (
1272 itemtype => itemtype,
1273 itemkey => itemkey,
1274 aname => 'USER_ID');
1275
1276 if l_user_id = -1 then
1277 l_approver_rec.user_id := null;
1278 else
1279 l_approver_rec.user_id := l_user_id;
1280 end if;
1281
1282 l_approver_rec.person_id := wf_engine.GetItemAttrNumber (
1283 itemtype => itemtype,
1284 itemkey => itemkey,
1285 aname => 'PERSON_ID');
1286
1287 l_approver_rec.api_insertion := wf_engine.GetItemAttrText (
1288 itemtype => itemtype,
1289 itemkey => itemkey,
1290 aname => 'API_INSERTION');
1291
1292 l_approver_rec.authority := wf_engine.GetItemAttrText (
1293 itemtype => itemtype,
1294 itemkey => itemkey,
1295 aname => 'AUTHORITY');
1296
1297 ame_api.updateApprovalStatus(applicationIdIn => l_app_id,
1298 transactionIdIn => l_transaction_id,
1299 approverIn => l_approver_rec,
1300 transactionTypeIn => l_trx_type);
1301
1302 resultout := 'COMPLETE:';
1303 RETURN;
1304
1305 END IF;
1306 --
1307 -- CANCEL mode
1308 --
1309 IF (funcmode = 'CANCEL') THEN
1310 --
1311 resultout := 'COMPLETE:';
1312 RETURN;
1313 --
1314 END IF;
1315 --
1316 -- TIMEOUT mode
1317 --
1318 IF (funcmode = 'TIMEOUT') THEN
1319 --
1320 resultout := 'COMPLETE:';
1321 RETURN;
1322 --
1323 END IF;
1324
1325 IF (is_debug_procedure_on) THEN
1326 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1327 END IF;
1328
1329
1330 EXCEPTION
1331 WHEN OTHERS THEN
1332 wf_core.context('OKL_AM_WF' , 'SET_APPROVAL_STATUS', itemtype, itemkey, actid, funcmode);
1333 RAISE;
1334
1335 END SET_APPROVAL_STATUS;
1336
1337
1338 -- Start of comments
1339 --
1340 -- Procedure Name : GET_ERROR_STACK
1341 -- Description : Called from AM workflows to retrieve errors from the error
1342 -- stack and stores values in item attributes.
1343 -- Business Rules :
1344 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1345 -- Version : 1.0
1346 --
1347 -- End of comments
1348 PROCEDURE GET_ERROR_STACK( itemtype IN VARCHAR2,
1349 itemkey IN VARCHAR2,
1350 actid IN NUMBER,
1351 funcmode IN VARCHAR2,
1352 resultout OUT NOCOPY VARCHAR2 )IS
1353
1354 l_return_status VARCHAR2(1);
1355 l_msg_data VARCHAR2(4000);
1356
1357 l_error_itemtype VARCHAR2(100);
1358 l_error_itemkey VARCHAR2(100);
1359
1360 -- for debug logging
1361 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_error_stack';
1362 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1363 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1364 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1365 BEGIN
1366
1367 IF (is_debug_procedure_on) THEN
1368 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1369 END IF;
1370
1371 IF funcmode = 'RUN' THEN
1372
1373 -- get the errored processes itemtype and itemkey
1374 l_error_itemtype := wf_engine.GetItemAttrText (
1375 itemtype => itemtype,
1376 itemkey => itemkey,
1377 aname => 'ERROR_ITEM_TYPE');
1378
1379 l_error_itemkey := wf_engine.GetItemAttrText (
1380 itemtype => itemtype,
1381 itemkey => itemkey,
1382 aname => 'ERROR_ITEM_KEY');
1383
1384 -- get the error details from the errored process
1385
1386 l_return_status := wf_engine.GetItemAttrText (
1387 itemtype => l_error_itemtype,
1388 itemkey => l_error_itemkey,
1389 aname => 'API_ERROR');
1390
1391 l_msg_data := wf_engine.GetItemAttrText (
1392 itemtype => l_error_itemtype,
1393 itemkey => l_error_itemkey,
1394 aname => 'API_ERROR_STACK');
1395
1396 -- set error details in the standard error item type
1397
1398 wf_engine.SetItemAttrText (
1399 itemtype => itemtype,
1400 itemkey => itemkey,
1401 aname => 'API_ERROR',
1402 avalue => l_return_status);
1403
1404 wf_engine.SetItemAttrText (
1405 itemtype => itemtype,
1406 itemkey => itemkey,
1407 aname => 'API_ERROR_STACK',
1408 avalue => l_msg_data);
1409
1410
1411 resultout := 'COMPLETE:';
1412 RETURN;
1413 END IF;
1414
1415 IF (is_debug_procedure_on) THEN
1416 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1417 END IF;
1418
1419
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422
1423 wf_core.context('OKL_AM_WF' , 'GET_ERROR_STACK', itemtype, itemkey, actid, funcmode);
1424 RAISE;
1425 END GET_ERROR_STACK;
1426
1427 -- Start of comments
1428 --
1429 -- Procedure Name : POPULATE_ERROR_ATTS
1430 -- Description : Called from the AM Error WF (OKLAMERR) to populate additonal
1431 -- notificaiton attributes.
1432 -- Business Rules :
1433 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1434 -- Version : 1.0
1435 -- History : SECHAWLA 03-DEC-04 4047159 : display all messages from the stack
1436 -- End of comments
1437 PROCEDURE POPULATE_ERROR_ATTS(
1438 itemtype IN VARCHAR2
1439 , itemkey IN VARCHAR2
1440 , actid IN NUMBER
1441 , funcmode IN VARCHAR2
1442 , resultout OUT NOCOPY VARCHAR2 )IS
1443
1444 l_mesg_count NUMBER := 0;
1445 l_mesg_text VARCHAR2(4000);
1446 l_mesg_len NUMBER;
1447
1448 l_status_message VARCHAR2(30000);
1449 l_wf_admin VARCHAR2(100);
1450
1451 API_ERROR EXCEPTION;
1452
1453 -- for debug logging
1454 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'populate_error_atts';
1455 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1456 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1457 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1458 BEGIN
1459
1460 IF (is_debug_procedure_on) THEN
1461 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1462 END IF;
1463
1464 IF funcmode = 'RUN' THEN
1465
1466 l_mesg_count := fnd_msg_pub.count_msg;
1467
1468 IF l_mesg_count > 0 THEN
1469
1470 l_mesg_text := substr(fnd_msg_pub.get (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),1, 512);
1471
1472 --FOR i IN 1..2 LOOP -- (l_mesg_count - 1) loop -- SECHAWLA 03-DEC-04 4047159
1473 FOR i IN 1..(l_mesg_count - 1) loop -- SECHAWLA 03-DEC-04 4047159
1474 l_mesg_text := l_mesg_text || substr(fnd_msg_pub.get
1475 (fnd_msg_pub.G_NEXT, fnd_api.G_FALSE), 1, 512);
1476 END LOOP;
1477
1478 fnd_msg_pub.delete_msg();
1479
1480 l_mesg_len := length(l_mesg_text);
1481
1482 FOR i IN 1..ceil(l_mesg_len/255) LOOP
1483 l_status_message := l_status_message||' '||substr(l_mesg_text, ((i*255)-254), 255);
1484 END LOOP;
1485
1486 ELSE
1487
1488 l_status_message := 'An error was encountered but no message was found in the error stack';
1489
1490 END IF;
1491
1492 wf_engine.SetItemAttrText ( itemtype => itemtype,
1493 itemkey => itemkey,
1494 aname => 'API_ERROR_STACK',
1495 avalue => l_status_message);
1496
1497 wf_engine.SetItemAttrText ( itemtype => itemtype,
1498 itemkey => itemkey,
1499 aname => 'API_ERROR',
1500 avalue => 'E');
1501
1502
1503 resultout := 'COMPLETE:';
1504 RETURN;
1505
1506 END IF;
1507
1508 IF (is_debug_procedure_on) THEN
1509 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1510 END IF;
1511
1512
1513 EXCEPTION
1514 WHEN OTHERS THEN
1515 wf_core.context('OKL_AM_WF' , 'POPULATE_ERROR_ATTS', itemtype, itemkey, actid, funcmode);
1516 RAISE;
1517
1518 END POPULATE_ERROR_ATTS;
1519
1520
1521 -- Start of comments
1522 --
1523 -- Procedure Name : GET_NOTIFICATION_AGENT
1524 -- Description : Used by WF procedures where internal notifications are sent.
1525 -- Determines the user_name and description of the notification
1526 -- agent.
1527 -- Business Rules :
1528 -- Parameters : itemtype, itemkey, actid, funcmode, resultout, p_user_id,
1529 -- x_name, x_description
1530 -- Version : 1.1
1531 -- MDOKAL : Bug 2902588, changed code to check for fnd_user info 1st
1532 -- and then WF_ROLES instead of WF_USERS.
1533 -- : 19-NOV-03 MDOKAL Bug 3262184 - changed order in which
1534 -- wf users are retrieved.
1535 -- End of comments
1536 PROCEDURE GET_NOTIFICATION_AGENT(
1537 itemtype IN VARCHAR2
1538 , itemkey IN VARCHAR2
1539 , actid IN NUMBER
1540 , funcmode IN VARCHAR2
1541 , p_user_id IN NUMBER
1542 , x_name OUT NOCOPY VARCHAR2
1543 , x_description OUT NOCOPY VARCHAR2 ) IS
1544
1545 CURSOR wf_roles_csr(c_emp_id NUMBER, c_system VARCHAR2)
1546 IS
1547 SELECT NAME, DISPLAY_NAME
1548 FROM WF_ROLES
1549 WHERE orig_system_id = c_emp_id
1550 AND orig_system = c_system;
1551
1552 CURSOR fnd_users_csr(c_user_id NUMBER)
1553 IS
1554 SELECT USER_NAME, DESCRIPTION, EMPLOYEE_ID
1555 FROM FND_USER
1556 WHERE user_id = c_user_id;
1557
1558 l_user VARCHAR2(50);
1559 l_desc VARCHAR2(100);
1560 l_emp NUMBER;
1561 -- for debug logging
1562 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'get_notification_agent';
1563 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1564 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1565 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1566 BEGIN
1567
1568 IF (is_debug_procedure_on) THEN
1569 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1570 END IF;
1571
1572 /* The logic for retrieving a wf user follows this hierarchy:
1573 [Step 1] First check if p_user_id is for an FND user.
1574 [Step 2] If FND user found, then find if attached to a HR person
1575 [Step 3] If Step 2 is TRUE then get user where orig system is 'PER'
1576 based on the HR person id (l_emp)
1577 [Step 4] If Step 2 TRUE but l_emp is NULL therefore not attached
1578 to a HR person, use the FND user as is.
1579 [Step 5] If FND user not found, then check if the p_user_id passed
1580 is indeed for a HR person that is attached to an FND user.
1581 [Step 6] If FND user not found and PER user not found, check if the
1582 p_user_id pertains to a HR person not attached to an FND user.
1583 [Step 7] Finally, this is an invalid user, user the sysadmin user.
1584
1585 */
1586 -- 1st Check fnd users
1587 OPEN fnd_users_csr(p_user_id);
1588 FETCH fnd_users_csr INTO l_user, l_desc, l_emp;
1589 IF fnd_users_csr%notfound THEN
1590 -- 2nd check if id passed belongs to an employee rather then fnd user
1591 OPEN wf_roles_csr(p_user_id, 'PER');
1592 FETCH wf_roles_csr INTO l_user, l_desc;
1593 IF wf_roles_csr%notfound THEN
1594 CLOSE wf_roles_csr;
1595 -- Maybe a HR user not attached to FND user
1596 OPEN wf_roles_csr(p_user_id, 'HZ_PARTY');
1597 FETCH wf_roles_csr INTO l_user, l_desc;
1598 CLOSE wf_roles_csr;
1599 ELSE
1600 CLOSE wf_roles_csr;
1601 END IF;
1602 END IF;
1603 CLOSE fnd_users_csr;
1604
1605 -- if l_emp is not null then the user is attached to an employee
1606 IF l_emp IS NOT NULL THEN
1607
1608 OPEN wf_roles_csr(l_emp, 'PER');
1609 FETCH wf_roles_csr INTO l_user, l_desc;
1610 CLOSE wf_roles_csr;
1611
1612 END IF;
1613
1614 -- if l_user is still null, no user info was found
1615 IF l_user IS NULL THEN
1616 l_user := 'SYSADMIN';
1617 l_desc := 'System Administrator';
1618 END IF;
1619
1620 x_name := l_user;
1621 x_description := l_desc;
1622
1623 IF (is_debug_procedure_on) THEN
1624 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1625 END IF;
1626
1627
1628 EXCEPTION
1629 WHEN OTHERS THEN
1630 wf_core.context('OKL_AM_WF' , 'GET_NOTIFICATION_AGENT', itemtype, itemkey, actid, funcmode);
1631 RAISE;
1632 END GET_NOTIFICATION_AGENT;
1633
1634
1635 -- Start of comments
1636 --
1637 -- Procedure Name : pop_approval_doc
1638 -- Description : MDOKAL, 20-MAR-2003 Bug 2862254
1639 -- This procedure is invoked dynamically by Workflow API's
1640 -- in order to populate the message body item attribute
1641 -- during notification submission.
1642 -- Business Rules :
1643 -- Parameters : document_id, display_type, document, document_type
1644 -- Version : 1.0
1645 --
1646 -- End of comments
1647 PROCEDURE pop_approval_doc (document_id in varchar2,
1648 display_type in varchar2,
1649 document in out nocopy varchar2,
1650 document_type in out nocopy varchar2) IS
1651
1652 l_message VARCHAR2(32000);
1653 -- for debug logging
1654 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'pop_approval_doc';
1655 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1656 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1657 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1658 BEGIN
1659
1660 IF (is_debug_procedure_on) THEN
1661 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1662 END IF;
1663
1664 l_message := wf_engine.GetItemAttrText (
1665 itemtype => 'OKLAMAPP',
1666 itemkey => document_id,
1667 aname => 'MESSAGE_DESCRIPTION');
1668
1669 document := l_message;
1670 document_type := display_type;
1671
1672 RETURN;
1673
1674 IF (is_debug_procedure_on) THEN
1675 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1676 END IF;
1677
1678
1679 EXCEPTION
1680 WHEN OTHERS THEN NULL;
1681
1682 END pop_approval_doc;
1683
1684
1685 -- Start of comments
1686 --
1687 -- Procedure Name : set_status_on_exit
1688 -- Description : Called from the Generic Approvals WF to set the Result
1689 -- attribute when requet timed out
1690 -- Business Rules :
1691 -- Parameters : itemtype, itemkey, actid, funcmode, resultout
1692 -- Version : 1.0
1693 --
1694 -- End of comments
1695 PROCEDURE SET_STATUS_ON_EXIT( itemtype IN VARCHAR2,
1696 itemkey IN VARCHAR2,
1697 actid IN NUMBER,
1698 funcmode IN VARCHAR2,
1699 resultout OUT NOCOPY VARCHAR2 )IS
1700 -- for debug logging
1701 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'set_status_on_exit';
1702 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1703 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1704 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1705 BEGIN
1706
1707 IF (is_debug_procedure_on) THEN
1708 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1709 END IF;
1710
1711 SAVEPOINT set_atts;
1712
1713 IF (funcmode = 'RUN') THEN
1714
1715 wf_engine.SetItemAttrText (
1716 itemtype => itemtype,
1717 itemkey => itemkey,
1718 aname => 'RESULT',
1719 avalue => 'REJECT');
1720
1721
1722 resultout := 'COMPLETE:';
1723 RETURN;
1724
1725 END IF;
1726 --
1727 -- CANCEL mode
1728 --
1729 IF (funcmode = 'CANCEL') THEN
1730 --
1731 resultout := 'COMPLETE:';
1732 RETURN;
1733 --
1734 END IF;
1735 --
1736 -- TIMEOUT mode
1737 --
1738 IF (funcmode = 'TIMEOUT') THEN
1739 --
1740 resultout := 'COMPLETE:';
1741 RETURN;
1742 --
1743 END IF;
1744
1745 IF (is_debug_procedure_on) THEN
1746 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1747 END IF;
1748
1749
1750 EXCEPTION
1751 WHEN OTHERS THEN
1752 wf_core.context('OKL_AM_WF' , 'SET_STATUS_ON_EXIT', itemtype, itemkey, actid, funcmode);
1753 RAISE;
1754
1755 END SET_STATUS_ON_EXIT;
1756 --added by akrangan as part of MOAC changes
1757 PROCEDURE CALLBACK(itemtype IN VARCHAR2,
1758 itemkey IN VARCHAR2,
1759 activity_id IN NUMBER,
1760 command IN VARCHAR2,
1761 resultout OUT NOCOPY VARCHAR2) IS
1762 l_user_name VARCHAR2(240);
1763 x_return_status VARCHAR2(1);
1764 l_api_name VARCHAR2(40) := 'callback';
1765 x_msg_count NUMBER;
1766 x_msg_data VARCHAR2(32767);
1767 l_application_id fnd_application.application_id%TYPE;
1768 l_api_version NUMBER := 1.0;
1769 p_api_version NUMBEr := 1.0;
1770 l_org_id NUMBER;
1771 current_org_id NUMBER;
1772 l_user_id NUMBER;
1773 current_user_id NUMBER;
1774 l_resp_id NUMBER;
1775 current_resp_id NUMBER;
1776 l_appl_id NUMBER;
1777 current_appl_id NUMBER;
1778
1779 -- for debug logging
1780 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'callback';
1781 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1782 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1783 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1784 BEGIN
1785
1786 IF (is_debug_procedure_on) THEN
1787 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1788 END IF;
1789
1790 l_org_id:= wf_engine.GetItemAttrNumber(itemtype,
1791 itemkey,
1792 'ORG_ID');
1793 current_org_id := nvl(mo_global.get_current_org_id(),-1);
1794
1795 IF(itemtype = 'OKLCSCRM') THEN
1796
1797 -- Get the user who intiated the workflow
1798 l_user_id:= wf_engine.GetItemAttrNumber(itemtype,
1799 itemkey,
1800 'USER_ID');
1801 -- Get the current user
1802 current_user_id:= FND_GLOBAL.USER_ID;
1803 -- Get the responsibility at which the above user intiated the workflow
1804 l_resp_id:= wf_engine.GetItemAttrNumber(itemtype,
1805 itemkey,
1806 'RESPONSIBILITY_ID');
1807 -- Get the current responsibility
1808 current_resp_id:= FND_GLOBAL.RESP_ID;
1809 -- Get the application where the above user intiated the workflow
1810 l_appl_id:= wf_engine.GetItemAttrNumber(itemtype,
1811 itemkey,
1812 'APPLICATION_ID');
1813 -- Get the current application
1814 current_appl_id:= FND_GLOBAL.RESP_APPL_ID;
1815 END IF;
1816 IF (command ='SET_CTX') THEN
1817 -- Set the application user context back to the original one
1818 IF(itemtype = 'OKLCSCRM') THEN
1819 FND_GLOBAL.APPS_initialize(l_user_id,l_resp_id,l_appl_id);
1820 END IF;
1821 mo_global.init('OKL');
1822 MO_GLOBAL.set_policy_context('S',l_org_id);
1823 resultout :='COMPLETE';
1824 END IF;
1825
1826 IF (command='TEST_CTX') THEN
1827 -- Check if user or resp or application or org has changed
1828 IF(itemtype = 'OKLCSCRM') THEN
1829 IF ( l_org_id <> current_org_id
1830 OR l_user_id <> current_user_id
1831 OR l_resp_id <> current_resp_id
1832 OR l_appl_id <> current_appl_id) THEN
1833 resultout := 'NOTSET';
1834 ELSE
1835 resultout := 'TRUE';
1836 END IF;
1837 END IF;
1838 IF (l_org_id <> current_org_id) THEN
1839 resultout := 'NOTSET';
1840 ELSE
1841 resultout := 'TRUE';
1842 END IF;
1843 return;
1844 END IF;
1845
1846
1847 IF (is_debug_procedure_on) THEN
1848 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
1849 END IF;
1850
1851
1852 EXCEPTION
1853 WHEN OTHERS THEN
1854 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name,
1855 'OKL_AM_WF',
1856 'OTHERS',
1857 x_msg_count,
1858 x_msg_data,
1859 '_PVT');
1860 RAISE;
1861 END callback;
1862
1863 /*Sosharma 24-Oct-2006
1864 Build:R12
1865 Procedure to populate attribute values for delivery mode from the profiles.
1866 Also to populate the value of template code based on recipient type and process code
1867 Start Changes */
1868 PROCEDURE populate_attributes( itemtype IN VARCHAR2,
1869 itemkey IN VARCHAR2,
1870 actid IN NUMBER,
1871 funcmode IN VARCHAR2,
1872 resultout OUT NOCOPY VARCHAR2 )IS
1873
1874 l_delivery_mode VARCHAR2(100);
1875 l_recipient_type VARCHAR2(100);
1876 l_template_code VARCHAR2(100);
1877 l_process_code VARCHAR2(100);
1878 l_datasource_code VARCHAR2(100);
1879 l_notification_agent VARCHAR2(200);
1880 l_desc VARCHAR2(500);
1881 l_created_by VARCHAR2(100);
1882 l_message_body VARCHAR2(500);
1883 l_message_sub VARCHAR2(200);
1884 --19-jul-2007 ansethur R12B XML Publisher Starts
1885 l_email_subject_line VARCHAR2(500);
1886 -- ansethur modified the cursor to return template_code, data_source_code and subject_line
1887 CURSOR c_get_temp_code(process_code varchar2,recipient_type varchar2)
1888 IS
1889 SELECT --PT.PTM_CODE REPORT_CODE,
1890 -- FND.MEANING REPORT_NAME,
1891 PT.XML_TMPLT_CODE TEMPLATE_CODE,
1892 --XDOTL.TEMPLATE_NAME,
1893 -- XDOB.APPLICATION_SHORT_NAME TMPLT_APPS_SHORT_NAME,
1894 -- XDOB.DS_APP_SHORT_NAME DATA_SRC_APPS_SHORT_NAME,
1895 XDOB.DATA_SOURCE_CODE,
1896 PTTL.EMAIL_SUBJECT_LINE
1897 FROM OKL_PROCESS_TMPLTS_B PT,
1898 OKL_PROCESS_TMPLTS_TL PTTL,
1899 FND_LOOKUPS FND,
1900 XDO_TEMPLATES_B XDOB,
1901 XDO_TEMPLATES_TL XDOTL
1902 WHERE PT.PTM_CODE = FND.LOOKUP_CODE
1903 AND XDOB.TEMPLATE_CODE = XDOTL.TEMPLATE_CODE
1904 AND XDOB.APPLICATION_SHORT_NAME = 'OKL'
1905 AND PT.START_DATE <= SYSDATE
1906 AND NVL(PT.END_DATE,SYSDATE) >= SYSDATE
1907 AND PT.XML_TMPLT_CODE = XDOB.TEMPLATE_CODE
1908 AND XDOTL.LANGUAGE = USERENV('LANG')
1909 AND PT.PTM_CODE = process_code
1910 AND PT.RECIPIENT_TYPE_CODE = recipient_type
1911 AND PTTL.ID=PT.ID;
1912 --19-jul-2007 ansethur R12B XML Publisher Ends
1913 -- for debug logging
1914 L_MODULE_NAME VARCHAR2(500) := G_MODULE_NAME||'populate_attributes';
1915 is_debug_exception_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_EXCEPTION);
1916 is_debug_procedure_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_PROCEDURE);
1917 is_debug_statement_on boolean := OKL_DEBUG_PUB.Check_Log_On (l_module_name, G_LEVEL_STATEMENT);
1918 BEGIN
1919
1920 IF (is_debug_procedure_on) THEN
1921 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'Begin(+)');
1922 END IF;
1923
1924
1925 IF (funcmode = 'RUN') THEN
1926
1927 SAVEPOINT call_populate;
1928
1929
1930 l_recipient_type := wf_engine.GetItemAttrText (
1931 itemtype => itemtype,
1932 itemkey => itemkey,
1933 aname => 'RECIPIENT_TYPE');
1934
1935 l_process_code := wf_engine.GetItemAttrText (
1936 itemtype => itemtype,
1937 itemkey => itemkey,
1938 aname => 'PROCESS_CODE');
1939
1940 l_created_by := wf_engine.GetItemAttrNumber( itemtype => itemtype,
1941 itemkey => itemkey,
1942 aname => 'CREATED_BY');
1943
1944 OPEN c_get_temp_code(l_process_code,l_recipient_type);
1945 FETCH c_get_temp_code INTO l_template_code,l_datasource_code,l_email_subject_line; -- ansethur added subject line
1946 CLOSE c_get_temp_code;
1947
1948
1949 l_delivery_mode := 'EMAIL';--fnd_profile.value('OKL_DELIVERY_MODE'); -- ansethur changed the delivery mode
1950
1951 fnd_message.set_name ('OKL','OKL_XML_WF_SUB');
1952 l_message_sub:=fnd_message.get;
1953 fnd_message.set_name ('OKL','OKL_XML_WF_BODY');
1954 l_message_body:=fnd_message.get;
1955
1956 okl_am_wf.get_notification_agent(
1957 itemtype => itemtype
1958 , itemkey => itemkey
1959 , actid => actid
1960 , funcmode => funcmode
1961 , p_user_id => l_created_by
1962 , x_name => l_notification_agent
1963 , x_description => l_desc);
1964
1965 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1966 itemkey => itemkey,
1967 aname => 'NOTIFY_AGENT',
1968 avalue => l_notification_agent);
1969
1970 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1971 itemkey => itemkey,
1972 aname => 'RECIPIENT_DESCRIPTION',
1973 avalue => l_notification_agent);
1974
1975 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1976 itemkey => itemkey,
1977 aname => 'TEMPLATE_CODE',
1978 avalue => l_template_code);
1979
1980
1981 wf_engine.SetItemAttrText ( itemtype=> itemtype,
1982 itemkey => itemkey,
1983 aname => 'DELIVERY_MODE',
1984 avalue => l_delivery_mode);
1985
1986
1987
1988 wf_engine.SetItemAttrText (itemtype => itemtype,
1989 itemkey => itemkey,
1990 aname => 'MESSAGE_BODY_TEXT',
1991 avalue => l_message_body
1992 );
1993 wf_engine.SetItemAttrText (itemtype => itemtype,
1994 itemkey => itemkey,
1995 aname => 'MESSAGE_SUB_TEXT',
1996 avalue => l_message_sub
1997 );
1998 --19-jul-2007 ansethur R12B XML Publisher Starts
1999 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2000 itemkey => itemkey,
2001 aname => 'DATA_SOURCE_CODE',
2002 avalue => l_datasource_code);
2003 wf_engine.SetItemAttrText ( itemtype=> itemtype,
2004 itemkey => itemkey,
2005 aname => 'SUBJECT',
2006 avalue => l_email_subject_line);
2007 --19-jul-2007 ansethur R12B XML Publisher Ends
2008
2009 resultout := 'COMPLETE:SET';
2010 RETURN;
2011
2012 END IF;
2013
2014
2015
2016 --
2017 -- CANCEL mode
2018 --
2019 IF (funcmode = 'CANCEL') THEN
2020 --
2021 resultout := 'COMPLETE:';
2022 RETURN;
2023 --
2024 END IF;
2025 --
2026 -- TIMEOUT mode
2027 --
2028 IF (funcmode = 'TIMEOUT') THEN
2029 --
2030 resultout := 'COMPLETE:';
2031 RETURN;
2032 --
2033 END IF;
2034
2035 IF (is_debug_procedure_on) THEN
2036 OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_PROCEDURE,L_MODULE_NAME,'End(-)');
2037 END IF;
2038
2039
2040 EXCEPTION
2041 WHEN OTHERS THEN
2042
2043 ROLLBACK TO call_populate;
2044
2045 wf_core.context('OKL_AM_WF' , 'populate_attributes', itemtype, itemkey, actid, funcmode);
2046 RAISE;
2047
2048 END populate_attributes;
2049 /* sosharma End Changes */
2050
2051
2052
2053 END OKL_AM_WF;