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