1 package body IEX_STRATEGY_WF as
2 --$Header: iexstrwb.pls 120.49 2011/12/19 10:17:44 pnaveenk ship $
3
4
5 --------------private procedures and funtion --------------------------------
6
7 /** check if the process is active or not
8 * for fulfillment and custom work flows
9 * abort the processes that are not completed.
10 **/
11
12 wf_yes varchar2(1) ;
13 wf_no varchar2(1) ;
14 -- PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
15 PG_DEBUG NUMBER ;
16 procedure custom_abort_process(p_itemtype IN varchar2, p_itemkey IN varchar2) is
17 begin
18 wf_engine.abortprocess(itemtype => p_itemtype, itemkey => p_itemkey);
19 exception
20 when others then
21 iex_debug_pub.logmessage('Exception of custom abort_processes: ');
22 end;
23
24 PROCEDURE abort_processes(p_strategy_id IN NUMBER) IS
25
26 cursor c_get_itemtype is
27 select a.workflow_item_type itemtype,
28 b.work_item_id
29 from iex_stry_temp_work_items_vl a,
30 iex_strategy_work_items b
31 where a.work_item_temp_id =b.WORK_ITEM_TEMPLATE_ID
32 and b.strategy_id =p_strategy_id
33 and a.workflow_item_type IS NOT NULL;
34
35 cursor c_workitems is
36 select a.work_item_id
37 from iex_strategy_work_items a,
38 iex_stry_temp_work_items_vl b
39 where a.strategy_id = p_strategy_id
40 and a.work_item_template_id =b.work_item_temp_id
41 and (b.fulfil_temp_id IS NOT NULL or b.xdo_template_id IS NOT NULL)
42 -- Added for bug 8996459 PNAVEENK 27-OCT-2009
43 and exists ( select 1 from wf_items
44 where item_type='IEXSTFFM'
45 and item_key = a.work_item_id );
46 -- and b.fulfil_temp_id IS NOT NULL ;
47
48 l_itemtype VARCHAR2(100);
49 l_result VARCHAR2(100);
50 l_Status VARCHAR2(8);
51
52 -- begin bug 7703319
53 l_itemkey varchar2(240);
54 l_jtf_object_type varchar2(30);
55 l_jtf_object_id number;
56
57 cursor get_itemkey(c_id IN varchar2) is
58 select distinct item_key from wf_item_attr_values_ondemand
59 where name = 'WRITEOFF_ID' and text_value = c_id and item_type = 'IEXWRREQ';
60
61 cursor get_strategy_info(c_strategy_id IN number) is
62 select jtf_object_type,jtf_object_id from iex_strategies
63 where strategy_id = c_strategy_id;
64 -- end bug 7703319
65
66 BEGIN
67 --check for custom work flow first
68 --abort all the custom workflow for the given strategy_id
69 -- IF PG_DEBUG < 10 THEN
70 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
71 iex_debug_pub.logmessage ('**** BEGIN New abort_processes ************');
72 END IF;
73
74 -- Begin bug 7703319
75 begin
76 Open get_strategy_info(p_strategy_id);
77 Fetch get_strategy_info into l_jtf_object_type,l_jtf_object_id;
78
79 if l_jtf_object_type = 'IEX_WRITEOFF' then
80
81 open get_itemkey(l_jtf_object_id);
82 Loop
83 fetch get_itemkey into l_itemkey;
84 exit when get_itemkey%NOTFOUND;
85
86 iex_debug_pub.logmessage('IEXWRREQ Workflow and itemkey is...'||l_itemkey);
87
88 wf_engine.itemstatus(itemtype => 'IEXWRREQ', itemkey => l_itemkey, status => l_status, result => l_result);
89 iex_debug_pub.logmessage('IEXWRREQ Workflow Status = :: =>' || l_status||'and itemkey is...'||l_itemkey);
90
91 if l_status <> wf_engine.eng_completed then
92 wf_engine.abortprocess(itemtype => 'IEXWRREQ', itemkey => l_itemkey);
93 wf_engine.itemstatus(itemtype => 'IEXWRREQ', itemkey => l_itemkey, status => l_status, result => l_result);
94 iex_debug_pub.logmessage('cancel Writeoff workflow: Abort process has completed and status =>' || l_status);
95 end if;
96 End Loop;
97 close get_itemkey;
98
99 end if;
100 exception
101 when others then
102 iex_debug_pub.logmessage('Exception for WriteOFF Aborting.. Strategy ID =>'||p_strategy_id||'id =>'||l_jtf_object_id);
103 end;
104 iex_debug_pub.logmessage('Ending....cancel Writeoff workflow: ');
105 -- End bug 7703319
106
107 FOR c_rec in c_get_itemtype
108 LOOP
109 begin -- Added for bug#8493656 by PNAVEENK Moved all the code in the loop into the scope of exception handler
110 if c_rec.itemtype IS NOT NULL THEN
111 wf_engine.itemstatus(itemtype => c_rec.itemtype,
112 itemkey => c_rec.work_item_id,
113 status => l_status,
114 result => l_result);
115 -- IF PG_DEBUG < 10 THEN
116 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
117 iex_debug_pub.logmessage('abort_processes: ' || 'after workflow status check ' ||l_status || ' item key'
118 || c_rec.work_item_id);
119 END IF;
120
121 IF l_status <> wf_engine.eng_completed THEN
122 -- IF PG_DEBUG < 10 THEN
123 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
124 iex_debug_pub.logmessage('abort_processes: ' || ' process has not completed and status =>'|| l_status);
125 END IF;
126 -- BEGIN commented for bug#8493656
127 wf_engine.abortprocess(itemtype => c_rec.itemtype,
128 itemkey => c_rec.work_item_id);
129 END IF; -- Added for bug#8493656
130 end if; -- Added for bug#8493656
131 EXCEPTION
132 WHEN OTHERS THEN
133 -- IF PG_DEBUG < 10 THEN
134 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
135 iex_debug_pub.logmessage('abort_processes: ' || 'abort process ' || c_rec.itemtype ||
136 'itemkey ' || c_rec.work_item_id ||'has failed');
137 END IF;
138 END;
139 -- end if; commented for bug#8453656
140
141 -- end if; commented for bug#8453656
142
143 END LOOP;
144
145 --abort all fulfillment workflow for
146 -- the given strategy.
147 BEGIN
148 l_itemtype :='IEXSTFFM';
149 FOR c_rec in c_workitems
150 LOOP
151 -- Start for the bug 8996459 PNAVEENK
152 Begin
153 wf_engine.itemstatus(itemtype => l_itemtype,
154 itemkey => c_rec.work_item_id,
155 status =>l_status,
156 result =>l_result);
157 -- IF PG_DEBUG < 10 THEN
158 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
159 iex_debug_pub.logmessage('abort_processes: ' || 'after workflow status check ' ||l_status || ' item key'
160 ||c_rec.work_item_id);
161 END IF;
162
163 Exception
164 when others then
165 iex_debug_pub.logmessage(' Exception in finding workflow of work_item_id ' || c_rec.work_item_id);
166 end;
167
168 -- end for bug 8996459
169 IF l_status <> wf_engine.eng_completed THEN
170 -- IF PG_DEBUG < 10 THEN
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 iex_debug_pub.logmessage ('abort_processes: ' || ' in fulfilment and process has not completed '||
173 'and status =>'||l_status);
174 END IF;
175
176
177 BEGIN
178 wf_engine.abortprocess(itemtype => l_itemtype,
179 itemkey => c_rec.work_item_id);
180
181
182
183 --Begin bug#8490070 snuthala 25-May-2009
184 --cancel the delivery request when corresponding workitem is cancelled
185 iex_debug_pub.logmessage('abort_processes: ' || 'Cancelling the xml delivery requests corresponding to workitem: '||c_rec.work_item_id);
186 update iex_xml_request_histories
187 set status='CANCELLED'
188 where object_type='IEX_STRATEGY'
189 and status = 'XMLDATA'
190 and xml_request_id in (select xml_request_id
191 from iex_dunnings
192 where object_type='IEX_STRATEGY'
193 and object_id=c_rec.work_item_id);
194 iex_debug_pub.logmessage('abort_processes: ' || 'Completed cancelling the xml delivery requests corresponding to workitem: '||c_rec.work_item_id);
195 --Begin bug#8490070 snuthala 25-May-2009
196 EXCEPTION
197 WHEN OTHERS THEN
198 -- IF PG_DEBUG < 10 THEN
199 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
200 iex_debug_pub.logmessage('abort_processes: ' || 'abort process ' || l_itemtype ||
201 'itemkey ' || c_rec.work_item_id ||'has failed');
202 END IF;
203 END;
204
205 end if;
206 END LOOP;
207 EXCEPTION WHEN OTHERS THEN
208 null; -- if itemkey does not exists ..do nothing.
209 END ;
210
211
212 -- IF PG_DEBUG < 10 THEN
213 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
214 iex_debug_pub.logmessage ('**** END abort_processes ************');
215 END IF;
216 EXCEPTION
217 WHEN OTHERS THEN
218 -- IF PG_DEBUG < 10 THEN
219 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
220 iex_debug_pub.logmessage('abort_processes: ' || 'abort process has failed' );
221 END IF;
222 END abort_processes;
223
224 /**
225 * to see if the current work item is still open
226 * this is because custom and fulfillment work flows updates
227 * the work item and if the strategy is changed back from ONHOLD to OPEN,
228 * we need to resume the process.
229 **/
230
231 FUNCTION CHECK_WORK_ITEM_OPEN(p_strategy_id IN NUMBER)
232 RETURN NUMBER IS
233 v_result NUMBER;
234 BEGIN
235 -- IF PG_DEBUG < 10 THEN
236 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
237 iex_debug_pub.logmessage ('**** BEGIN CHECK_WORK_ITEM_OPEN ************');
238 END IF;
239 select count(*)
240 INTO v_result
241 from iex_strategies a,
242 ieX_strategy_work_items b
243 where a.strategy_id =p_strategy_id
244 and a.strategy_id =b.strategy_id
245 and a.next_work_item_id =b.work_item_id
246 and b.status_code ='OPEN'
247 and a.status_code ='OPEN';
248
249 return v_result;
250 -- IF PG_DEBUG < 10 THEN
251 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
252 iex_debug_pub.logmessage ('**** END CHECK_WORK_ITEM_OPEN ************');
253 END IF;
254 END CHECK_WORK_ITEM_OPEN;
255
256
257 /**
258 * work item is a match
259 **/
260
261 FUNCTION CHECK_CURRENT_WORK_ITEM(p_strategy_id IN NUMBER,
262 p_work_item_id IN NUMBER)
263 RETURN NUMBER IS
264 v_result NUMBER;
265 BEGIN
266 -- IF PG_DEBUG < 10 THEN
267 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
268 iex_debug_pub.logmessage ('**** BEGIN CHECK_CURRENT_WORK_ITEM ************');
269 END IF;
270 select count(*) INTO v_result from iex_strategies
271 where strategy_id =p_strategy_id
272 and next_work_item_id =p_work_item_id
273 and status_code ='OPEN';
274 return v_result;
275 -- IF PG_DEBUG < 10 THEN
276 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
277 iex_debug_pub.logmessage ('**** END CHECK_CURRENT_WORK_ITEM ************');
278 END IF;
279 END CHECK_CURRENT_WORK_ITEM;
280
281 /**
282 * update work item status
283 */
284 PROCEDURE update_workitem_Status(p_work_item_id IN NUMBER,
285 p_status IN VARCHAR2 )IS
286
287 l_api_version NUMBER := 1.0;
288 l_return_status VARCHAR2(1);
289 l_msg_count NUMBER;
290 l_msg_data VARCHAR2(32767);
291
292 BEGIN
293 -- IF PG_DEBUG < 10 THEN
294 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
295 iex_debug_pub.logmessage ('**** BEGIN update_workitem_Status ************');
296 END IF;
297 IEX_STRY_UTL_PUB.UPDATE_WORK_ITEM (p_api_version => l_api_version,
298 p_init_msg_list => FND_API.G_TRUE,
299 p_commit => FND_API.G_FALSE,
300 x_msg_count => l_msg_count,
301 x_msg_data => l_msg_data,
302 x_return_status => l_return_status,
303 p_work_item_id => p_work_item_id,
304 p_status => p_status);
305
306 -- IF PG_DEBUG < 10 THEN
307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
308 iex_debug_pub.logmessage ('**** END update_workitem_Status ************');
309 END IF;
310 END update_workitem_Status;
311
312 /** get user name
313 * this will used to send the notification
314 **/
315
316 procedure get_username
317 ( p_resource_id IN NUMBER,
318 --x_username OUT NOCOPY VARCHAR2 ) IS bug 6717880/7170165 by Ehuh
319 x_username OUT NOCOPY VARCHAR2,
320 x_source_name OUT NOCOPY VARCHAR2 ) IS -- bug 6717880/7170165 by Ehuh
321
322 cursor c_getname(p_resource_id NUMBER) is
323 --Select user_name -- bug 6717880/7170165 by Ehuh
324 Select user_name,source_name -- bug 6717880/7170165 by Ehuh
325 from jtf_rs_resource_extns
326 where resource_id =p_resource_id;
327
328 BEGIN
329 -- IF PG_DEBUG < 10 THEN
330 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
331 iex_debug_pub.logmessage ('**** BEGIN get_username ************');
332 END IF;
333 OPEN c_getname(p_resource_id);
334 --FETCH c_getname INTO x_username; -- bug 6717880/7170165 by Ehuh
335 FETCH c_getname INTO x_username, x_source_name; -- bug 6717880/7170165 by Ehuh
336 CLOSE c_getname;
337 -- IF PG_DEBUG < 10 THEN
338 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
339 iex_debug_pub.logmessage ('**** END get_username ************');
340 END IF;
341 END get_username;
342
343 -----populate set_notification_resources---------------------------------
344 procedure set_notification_resources(
345 p_resource_id in number,
346 itemtype in varchar2,
347 itemkey in varchar2
348 ) IS
349 l_username VARCHAR2(100);
350 l_mgrname VARCHAR2(100);
351 l_mgr_resource_id NUMBER ;
352 l_mgr_id number;
353
354 CURSOR c_manager(p_resource_id NUMBER) IS
355 SELECT b.user_id, b.user_name , b.resource_id
356 FROM JTF_RS_RESOURCE_EXTNS a
357 , JTF_RS_RESOURCE_EXTNS b
358 WHERE b.source_id = a.source_mgr_id
359 AND a.resource_id = p_resource_id;
360
361 --Start bug 6717880/7170165 by Ehuh
362 Cursor c_get_assignee(p_resource_id number) is
363 select source_name from JTF_RS_RESOURCE_EXTNS
364 where resource_id = p_resource_id;
365 l_source_name varchar2(50);
366 --End bug 6717880/7170165 by Ehuh
367
368
369 BEGIN
370 -- IF PG_DEBUG < 10 THEN
371 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
372 iex_debug_pub.logmessage ('**** BEGIN set_notification_resources ************');
373 END IF;
374 -- get user name from jtf_rs_resource_extns
375 --Start bug 6717880/7170165 by Ehuh
376 --get_username
377 -- ( p_resource_id =>p_resource_id,
378 -- x_username =>l_username);
379 get_username ( p_resource_id =>p_resource_id,
380 x_username =>l_username,
381 x_source_name =>l_source_name);
382
383 wf_engine.SetItemAttrText(itemtype => itemtype,
384 itemkey => itemkey,
385 aname => 'ASSIGNEE',
386 avalue => l_source_name);
387 --End bug 6717880/7170165 by Ehuh
388
389 wf_engine.SetItemAttrText(itemtype => itemtype,
390 itemkey => itemkey,
391 aname => 'NOTIFICATION_USERNAME',
392 avalue => l_username);
393
394 -- get mgr name from jtf_rs_resource_extns
395 -- haveto get the mgr resource id
396 -- for the time being assign mgr =resource_id
397 BEGIN
398 open c_manager(p_resource_id);
399 fetch c_manager into l_mgr_id, l_mgrname, l_mgr_resource_id;
400 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
401 iex_debug_pub.logmessage ('Manager Info ' || l_mgrName || ' ID ' ||
402 l_mgr_id || 'Resource ID ' || l_mgr_resource_id);
403 END IF;
404 close c_manager;
405
406 EXCEPTION
407 WHEN OTHERS then
408 null;
409
410 END;
411
412 if l_mgrname is NULL then
413 l_mgrname := l_username;
414 l_mgr_resource_id := p_resource_id;
415 end if;
416
417
418 wf_engine.SetItemAttrText(itemtype => itemtype,
419 itemkey => itemkey,
420 aname => 'NOTIFICATION_MGRNAME',
421 avalue => l_mgrname);
422 -- IF PG_DEBUG < 10 THEN
423 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
424 iex_debug_pub.logmessage ('**** END set_notification_resources ************');
425 END IF;
426 exception
427 when others then
428 null;
429
430 END set_notification_resources;
431
432
433
434
435 --------------populate set_escalate_wait_time---------------------------------
436 procedure set_escalate_wait_time
437 ( p_escalate_date IN DATE,
438 itemtype in varchar2,
439 itemkey in varchar2
440 ) IS
441
442 l_grace_period NUMBER ;
443
444 BEGIN
445 -- initialize variable
446 l_grace_period := nvl(fnd_profile.value('IEX_STRY_GRACE_PERIOD'),0);
447
448 -- IF PG_DEBUG < 10 THEN
449 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
450 iex_debug_pub.logmessage ('**** BEGIN set_escalate_wait_time ************');
451 END IF;
452 wf_engine.SetItemAttrDate(itemtype => itemtype,
453 itemkey => itemkey,
454 aname => 'ESCALATE_WAIT_TIME',
455 avalue => p_escalate_date);
456 -- IF PG_DEBUG < 10 THEN
457 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
458 iex_debug_pub.logmessage ('set_escalate_wait_time: ' || 'ESCALATETIME' ||TO_CHAR(p_escalate_date,'DD-MON-YYYY:HH:MI:SS'));
459 END IF;
460 -- IF PG_DEBUG < 10 THEN
461 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
462 iex_debug_pub.logmessage ('**** END set_escalate_wait_time ************');
463 END IF;
464 exception
465 when others then
466 null;
467 END set_escalate_wait_time;
468
469 -----populate set_optional_wait_time---------------------------------
470 procedure set_optional_wait_time(
471 p_optional_date IN DATE,
472 itemtype in varchar2,
473 itemkey in varchar2
474 ) IS
475
476 BEGIN
477
478 -- IF PG_DEBUG < 10 THEN
479 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
480 iex_debug_pub.logmessage ('**** BEGIN set_optional_wait_time ************');
481 END IF;
482 wf_engine.SetItemAttrDate(itemtype => itemtype,
483 itemkey => itemkey,
484 aname => 'OPTIONAL_WAIT_TIME',
485 avalue => p_optional_date);
486 -- IF PG_DEBUG < 10 THEN
487 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
488 iex_debug_pub.logmessage ('set_optional_wait_time: ' || 'OPTIONALTIME' ||TO_CHAR(p_optional_date,'DD-MON-YYYY:HH:MI:SS'));
489 END IF;
490 -- IF PG_DEBUG < 10 THEN
491 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
492 iex_debug_pub.logmessage ('**** END set_optional_wait_time ************');
493 END IF;
494 exception
495 when others then
496 null;
497
498 END set_optional_wait_time;
499
500 -----populate schedule_times---------------------------------
501 --set coptional wait period
502 --set escalte wait period
503 --set work_item_template_id
504 --populate schedule_end and schedule_start
505
506 procedure populate_schedule_times
507 (
508 p_work_item_temp_id IN NUMBER,
509 itemtype IN varchar2,
510 itemkey IN varchar2,
511 p_strategy_work_item_rec IN IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE,
512 x_schedule_start OUT NOCOPY DATE,
513 x_schedule_end OUT NOCOPY DATE
514 ) IS
515
516
517 cursor c_get_witem_temp(p_work_item_temp_id NUMBER) is
518 select closure_time_limit, closure_time_uom,
519 schedule_wait, schedule_uom,
520 optional_yn,escalate_yn,
521 option_wait_time, option_wait_time_uom
522 from iex_stry_temp_work_items_vl
523 where work_item_temp_id =p_work_item_temp_id;
524
525 l_optional_date DATE;
526 l_work_item_id number;
527
528
529 BEGIN
530 -- IF PG_DEBUG < 10 THEN
531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
532 iex_debug_pub.logmessage ('**** BEGIN populate_schedule_times ************');
533 END IF;
534 l_work_item_id := wf_engine.GetItemAttrNumber(
535 itemtype => itemtype,
536 itemkey => itemkey,
537 aname => 'WORK_ITEMID');
538
539 FOR c_rec in c_get_witem_temp(p_work_item_temp_id)
540 LOOP
541 -- bug 10133319 pre execution wait override if exists
542 IF p_strategy_work_item_rec.pre_execution_wait IS NOT NULL THEN
543 c_rec.schedule_wait := p_strategy_work_item_rec.pre_execution_wait;
544 c_rec.schedule_uom := p_strategy_work_item_rec.pre_execution_time_uom;
545 END IF;
546 x_schedule_start:=IEX_STRY_UTL_PUB.get_Date
547 (p_date =>SYSDATE,
548 l_UOM =>c_rec.schedule_uom,
549 l_UNIT =>c_rec.schedule_wait);
550 -- IF PG_DEBUG < 10 THEN
551 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
552 iex_debug_pub.logmessage('populate_schedule_times: ' || 'schedulestart'||x_schedule_start);
553 END IF;
554 -- bug 10133319 escalate wait override if exists
555 IF p_strategy_work_item_rec.escalate_wait_time IS NOT NULL THEN
556 c_rec.closure_time_limit := p_strategy_work_item_rec.escalate_wait_time;
557 c_rec.closure_time_uom := p_strategy_work_item_rec.escalate_wait_time_uom;
558 END IF;
559 x_schedule_end:=IEX_STRY_UTL_PUB.get_Date
560 (p_date =>x_schedule_start,
561 l_UOM =>c_rec.closure_time_uom,
562 l_UNIT =>c_rec.closure_time_limit);
563 -- IF PG_DEBUG < 10 THEN
564 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
565 iex_debug_pub.logmessage('populate_schedule_times: ' || 'schedulestart'||x_schedule_end);
566 END IF;
567 -- populate the escalate wait period
568 IF p_strategy_work_item_rec.escalate_yn IS NOT NULL THEN
569 c_rec.escalate_yn := p_strategy_work_item_rec.escalate_yn;
570 END IF;
571 if c_rec.escalate_yn =wf_yes THEN
572
573 set_escalate_wait_time
574 ( p_escalate_date =>x_schedule_end,
575 itemtype =>itemtype,
576 itemkey =>itemkey);
577 end if;
578 -- bug 10133319 optional wait override if exists
579 IF p_strategy_work_item_rec.optional_yn IS NOT NULL THEN
580 c_rec.optional_yn := p_strategy_work_item_rec.optional_yn;
581 c_rec.option_wait_time_uom := p_strategy_work_item_rec.optional_wait_time_uom;
582 c_rec.option_wait_time := p_strategy_work_item_rec.optional_wait_time;
583 END IF;
584 --populate optional wait period
585 if c_rec.optional_yn =wf_yes THEN
586 l_optional_date:= IEX_STRY_UTL_PUB.get_Date
587 (p_date =>x_schedule_start,
588 l_UOM =>c_rec.option_wait_time_uom,
589 l_UNIT =>c_rec.option_wait_time);
590 -- IF PG_DEBUG < 10 THEN
591 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
592 iex_debug_pub.logmessage('populate_schedule_times: ' || 'sysdate '
593 ||TO_CHAR(sysdate,'DD-MON-YYYY:HH:MI:SS'));
594 END IF;
595
596 -- IF PG_DEBUG < 10 THEN
597 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
598 iex_debug_pub.logmessage('populate_schedule_times: ' || 'l_optional_date '
599 ||TO_CHAR(l_optional_date,'DD-MON-YYYY:HH:MI:SS'));
600 END IF;
601
602 set_optional_wait_time
603 ( p_optional_date => l_optional_date,
604 itemtype =>itemtype,
605 itemkey =>itemkey);
606 end if;
607 --set workitem_template_id attribute
608 wf_engine.SetItemAttrNumber(itemtype => itemtype,
609 itemkey => itemkey,
610 aname => 'WORKITEM_TEMPLATE_ID',
611 avalue => p_work_item_temp_id);
612 --reset the activity_label
613 wf_engine.SetItemAttrText(itemtype => itemtype,
614 itemkey => itemkey,
615 aname => 'ACTIVITY_NAME',
616 avalue => null);
617 --reset the status
618 wf_engine.SetItemAttrText(itemtype => itemtype,
619 itemkey => itemkey,
620 aname => 'STRATEGY_STATUS',
621 avalue => null);
622
623
624 END LOOP;
625 -- IF PG_DEBUG < 10 THEN
626 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
627 iex_debug_pub.logmessage ('**** END populate_schedule_times ************');
628 END IF;
629 EXCEPTION WHEN OTHERS THEN
630 null;
631 END populate_schedule_times;
632
633
634 ------------------- procedure get_resource ------------------------------
635 /** get resource id for the given competence
636 *
637 **/
638 procedure get_resource ( p_party_id IN NUMBER,
639 p_competence_tab IN tab_of_comp_id,
640 x_resource_id OUT NOCOPY NUMBER) IS
641
642 l_api_version NUMBER ;
643 l_init_msg_list VARCHAR2(1) ;
644 l_resource_tab iex_utilities.resource_tab_type;
645
646 l_commit VARCHAR2(1) ;
647 l_return_status VARCHAR2(1);
648 l_msg_count NUMBER;
649 l_msg_data VARCHAR2(32767);
650 l_validation_level NUMBER ;
651
652 l_resource_id NUMBER ;
653 l_count NUMBER ;
654 l_found BOOLEAN ;
655 cursor c_get_person_id (l_person_id NUMBER,
656 l_competence_id NUMBER)is
657 select count(person_id)
658 from per_competence_elements
659 where competence_id = l_competence_id
660 and person_id = l_person_id
661 and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
662 and trunc(effective_date_from) <= trunc(sysdate) ;
663
664
665 BEGIN
666 -- initialize variable
667 l_api_version := 1.0;
668 l_init_msg_list := FND_API.G_TRUE;
669 l_commit := FND_API.G_FALSE;
670 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
671 l_resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
672 l_count :=0;
673 l_found := TRUE;
674
675 -- IF PG_DEBUG < 10 THEN
676 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
677 iex_debug_pub.logmessage ('populate_schedule_times: ' || '**** BEGIN get_resource ************');
678 iex_debug_pub.logmessage ('default resource id from profile iex_stry_default_resource) ' || l_resource_id);
679 iex_debug_pub.logmessage ('calling get_access_resources ' || p_party_id);
680 END IF;
681 -- get resource id table of reords for the given party id
682 -- the record has resource id and person id along with the user name
683 --Begin bug#5373412 schekuri 10-Jul-2006
684 --Call new consolidated procedure get_assigned_collector
685 /*iex_utilities.get_assign_resources(p_api_version => l_api_version,
686 p_init_msg_list => FND_API.G_TRUE,
687 p_commit => FND_API.G_FALSE,
688 p_validation_level => l_validation_level,
689 x_msg_count => l_msg_count,
690 x_msg_data => l_msg_data,
691 x_return_status => l_return_status,
692 p_party_id => p_party_id,
693 x_resource_tab => l_resource_tab);*/
694
695 iex_utilities.get_assigned_collector(p_api_version => l_api_version,
696 p_init_msg_list => FND_API.G_TRUE,
697 p_commit => FND_API.G_FALSE,
698 p_validation_level => l_validation_level,
699 p_level => 'PARTY',
700 p_level_id => p_party_id,
701 x_msg_count => l_msg_count,
702 x_msg_data => l_msg_data,
703 x_return_status => l_return_status,
704 x_resource_tab => l_resource_tab);
705
706 --End bug#5373412 schekuri 10-Jul-2006
707
708 --IF PG_DEBUG < 10 THEN
709 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
710 iex_debug_pub.logmessage('populate_schedule_times: ' || 'in get resource and l_return_status = '||l_return_status);
711 iex_debug_pub.logmessage('in get resource and l_return_status from iex_utilities.get_access_resources = '||l_return_status);
712 iex_debug_pub.logmessage('resource count from iex_utilities.get_access_resources = '||l_resource_tab.count);
713 END IF;
714
715 -- if COMPETENCE id exists for the given work template Id,
716 -- see if the person id from the
717 -- the above l_resource_tab matches with the competence Id
718 -- pick if there is match or pick any resource if there is no match
719 -- or competence id of the work template id is null
720
721
722 --if p_competence_id IS NULL THEN
723 if p_competence_tab.count = 0 THEN
724 -- IF PG_DEBUG < 10 THEN
725 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
726 iex_debug_pub.logmessage('populate_schedule_times: ' || 'Competence table is empty');
727 END IF;
728 --get the first resource id if competence id is null from
729 -- the work item template
730 FOR i in 1..l_resource_tab.count LOOP
731 l_resource_id := l_resource_tab(i).resource_id;
732 -- IF PG_DEBUG < 10 THEN
733 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
734 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
735 END IF;
736 EXIT;
737 END LOOP;
738 else
739 -- IF PG_DEBUG < 10 THEN
740 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
741 iex_debug_pub.logmessage('Loop for matching competence. count = '||p_competence_tab.count );
742 END IF;
743 FOR i in 1..l_resource_tab.count LOOP
744 -- IF PG_DEBUG < 10 THEN
745 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
746 iex_debug_pub.logmessage('populate_schedule_times: ' || 'PERSON ID is '||l_resource_tab(i).person_id);
747 iex_debug_pub.logmessage('populate_schedule_times: ' || 'RESOURCE ID is '||l_resource_tab(i).resource_id);
748 END IF;
749
750 FOR j in 1..p_competence_tab.count LOOP
751
752 OPEN c_get_person_id (l_resource_tab(i).person_id,
753 p_competence_tab(j));
754 FETCH c_get_person_id INTO l_count;
755 CLOSE c_get_person_id;
756 -- IF PG_DEBUG < 10 THEN
757 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
758 iex_debug_pub.logmessage('populate_schedule_times: ' || 'COMPETENCE ID is '||
759 p_competence_tab(j));
760 END IF;
761 -- IF PG_DEBUG < 10 THEN
762 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
763 iex_debug_pub.logmessage('populate_schedule_times: ' || 'no of matches '|| l_count);
764 END IF;
765 If l_count =0 THEN
766 -- match not found, use the first resource and exit out NOCOPY
767 -- from the competence loop.
768 --Begin bug#5373412 schekuri 10-Jul-2006
769 --Commented the below the code to return default resource id instead of first resource id
770 --when there is no resource found matching the competency of the workitem.
771 /*l_resource_id := l_resource_tab(1).resource_id;
772 -- IF PG_DEBUG < 10 THEN
773 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
774 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
775 END IF;*/
776 --End bug#5373412 schekuri 10-Jul-2006
777 -- have to look for the next resource if l_found is false
778 l_found :=FALSE;
779 EXIT;
780 ELSE
781 l_resource_id := l_resource_tab(i).resource_id;
782 -- IF PG_DEBUG < 10 THEN
783 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
784 iex_debug_pub.logmessage('1st record found with competence matched l_resource_tab l_resource_id = '|| l_resource_id);
785 END IF;
786 l_found :=TRUE;
787 End if;
788 END LOOP;
789 if l_found THEN
790 -- a matching resource with all the competencies
791 --have been found ,stop looking for next resource
792 -- IF PG_DEBUG < 10 THEN
793 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
794 iex_debug_pub.logmessage('populate_schedule_times: ' || 'match found and RESOURCE ID is =>'
795 ||l_resource_tab(i).resource_id);
796 END IF;
797 exit;
798 end if;
799 END LOOP;
800 end if;
801 --assign out NOCOPY variable
802 x_resource_id :=l_resource_id;
803 -- IF PG_DEBUG < 10 THEN
804 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
805 iex_debug_pub.logmessage('populate_schedule_times: ' || 'value of x_resource_id' ||x_resource_id);
806 END IF;
807 -- IF PG_DEBUG < 10 THEN
808 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
809 iex_debug_pub.logmessage ('populate_schedule_times: ' || '**** END get_resource ************');
810 END IF;
811 END get_resource;
812
813
814 ----------- procedure create_work_item ------------------------------
815 /** to create work item
816 **/
817
818 procedure create_work_item
819 ( itemtype in varchar2
820 ,itemkey in varchar2
821 ,p_strategy_id IN NUMBER
822 ,p_template_id IN NUMBER
823 ,p_party_id IN NUMBER
824 ,x_return_status OUT NOCOPY VARCHAR2
825 ,x_error_msg OUT NOCOPY VARCHAR2) IS
826
827
828 l_resource_id NUMBER;
829 l_competence_id NUMBER;
830
831 -- Begin- Andre 07/28/2004 - Add bill to assignmnet
832 l_siteuse_id NUMBER;
833 l_object_code VARCHAR2(30);
834 l_cust_account_id NUMBER;
835 l_object_id number;
836 -- End- Andre 07/28/2004 - Add bill to assignmnet
837
838 cursor c_get_next_witem(p_strategy_id NUMBER,
839 p_template_id NUMBER)
840 is
841
842 /*select sxref.strategy_temp_id TEMPLATE_ID,
843 sxref.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
844 sxref.work_item_order ORDER_BY
845 ,nvl(swit.status_code,'NOTCREATED') STATUS
846 ,swit.work_item_id WORK_ITEM_ID
847 ,swit.strategy_id STRATEGY_ID
848 from iex_strategy_work_temp_xref sxref
849 ,iex_strategy_work_items swit
850 where sxref.strategy_temp_id =p_template_id
851 and swit.work_item_template_id(+) =sxref.work_item_temp_id
852 and swit.strategy_id(+) =p_strategy_id
853 union all
854 select susit.strategy_template_id TEMPLATE_ID,
855 susit.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
856 susit.work_item_order ORDER_BY
857 ,nvl(swit.status_code,'NOTCREATED') STATUS
858 ,swit.work_item_id WORK_ITEM_ID
859 ,susit.strategy_id STRATEGY_ID
860 from iex_strategy_user_items susit
861 ,iex_strategy_work_items swit
862 where susit.strategy_id =p_strategy_id
863 and swit.work_item_template_id(+) =susit.work_item_temp_id
864 and swit.strategy_id(+) =p_strategy_id
865 order by order_by;
866 */
867 -- bug 10133319 get pre wait,post wait,notify,resource name, escalate,optional from iex_strategy_user_items if exists else from template
868 --created work items
869 SELECT wkitem.strategy_id STRATEGY_ID,
870 wkitem.strategy_temp_id TEMPLATE_ID,
871 wkitem.work_item_order ORDER_BY,
872 wkitem.work_item_id WORK_ITEM_ID,
873 wkitem.work_item_template_id WORK_ITEM_TEMPLATE_ID,
874 wkitem.status_code STATUS,
875 wkitem.pre_execution_wait pre_execution_wait,
876 wkitem.pre_execution_time_uom pre_execution_time_uom,
877 wkitem.post_execution_wait post_execution_wait,
878 wkitem.execution_time_uom post_execution_time_uom,
879 wkitem.xdo_temp_id xdo_template_id,
880 wkitem.OPTIONAL_YN OPTIONAL_YN,
881 wkitem.OPTIONAL_WAIT_TIME OPTIONAL_WAIT_TIME,
882 wkitem.OPTIONAL_WAIT_TIME_UOM OPTIONAL_WAIT_TIME_UOM,
883 wkitem.ESCALATED_YN ESCALATED_YN,
884 wkitem.escalate_yn escalate_yn,
885 wkitem.escalate_wait_time escalate_wait_time,
886 wkitem.escalate_wait_time_uom escalate_wait_time_uom,
887 wkitem.NOTIFY_YN NOTIFY_YN,
888 wkitem.resource_id resource_id,
889 wkitem.custom_workflow_type custom_workflow_type
890 from iex_strategy_work_items wkitem,
891 iex_stry_temp_work_items_vl stry_temp_wkitem
892 WHERE
893 wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
894 and wkitem.strategy_id =p_strategy_id
895 --to be created work items
896 union all
897 SELECT stry.STRATEGY_ID STRATEGY_ID
898 , xref.STRATEGY_TEMP_ID TEMPLATE_ID
899 , xref.WORK_ITEM_ORDER ORDER_BY
900 , TO_NUMBER(NULL) WORK_ITEM_ID
901 , xref.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
902 , 'NOTCREATED' STATUS
903 , stry_temp_wkitem.pre_execution_wait pre_execution_wait
904 ,stry_temp_wkitem.schedule_uom pre_execution_time_uom
905 , stry_temp_wkitem.post_execution_wait post_execution_wait
906 , stry_temp_wkitem.execution_time_uom post_execution_time_uom
907 ,stry_temp_wkitem.xdo_template_id xdo_template_id
908 ,stry_temp_wkitem.optional_yn OPTIONAL_YN
909 ,stry_temp_wkitem.option_wait_time OPTIONAL_WAIT_TIME
910 ,stry_temp_wkitem.option_wait_time_uom OPTIONAL_WAIT_TIME_UOM
911 ,NULL escalated_yn
912 ,stry_temp_wkitem.escalate_yn ESCALATE_YN
913 ,stry_temp_wkitem.closure_time_limit escalate_wait_time
914 ,stry_temp_wkitem.closure_time_uom escalate_wait_time_uom
915 ,stry_temp_wkitem.notify_yn NOTIFY_YN
916 ,NULL resource_id
917 ,workflow_item_type custom_workflow_type
918 FROM IEX_STRATEGIES stry
919 , IEX_STRATEGY_WORK_TEMP_XREF xref
920 , IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
921 WHERE stry.STRATEGY_TEMPLATE_ID = xref.STRATEGY_TEMP_ID
922 and xref.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
923 AND stry_temp_wkitem.enabled_flag ='Y' -- added for bug 7299555 pnaveenk
924 and stry.strategy_id =p_strategy_id
925 --not in workitems table
926 AND not exists ( select 'x' from iex_strategy_work_items wkitem where
927 wkitem.strategy_id = stry.strategy_id
928 and wkitem.work_item_template_id = xref.work_item_temp_id
929 and wkitem.work_item_order = xref.work_item_order
930 and wkitem.strategy_id =p_strategy_id
931 )
932 ----skip workitems which is status-ed SKIP
933 and not exists ( select 'x' from iex_strategy_user_items uitems where
934 uitems.strategy_id = stry.strategy_id and
935 uitems.work_item_temp_id = xref.work_item_temp_id and
936 uitems.work_item_order = xref.work_item_order and
937 uitems.operation in ('SKIP','NOTCREATED')
938 and uitems.strategy_id =p_strategy_id
939 )
940 and (xref.work_item_order > (select max(wkitem_order) from iex_work_item_bali_v
941 where strategy_id = p_strategy_id and start_time is not null)
942 or (select count(*) from iex_work_item_bali_v where strategy_id = p_strategy_id ) = 0
943 ) -- later on assignment of prior work items, and case of initial creation of wkitem
944 -- get all user items
945 union all
946 SELECT stry.STRATEGY_ID STRATEGY_ID
947 , uitem.STRATEGY_TEMPLATE_ID TEMPLATE_ID
948 , uitem.WORK_ITEM_ORDER ORDER_BY
949 , TO_NUMBER(NULL) WORK_ITEM_ID
950 , uitem.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
951 , uitem.operation STATUS
952 , uitem.pre_execution_wait pre_execution_wait
953 , uitem.pre_execution_time_uom pre_execution_time_uom
954 , uitem.post_execution_wait post_execution_wait
955 , uitem.execution_time_uom post_execution_time_uom
956 , uitem.xdo_temp_id xdo_template_id
957 , uitem.OPTIONAL_YN OPTIONAL_YN
958 , uitem.OPTIONAL_WAIT_TIME OPTIONAL_WAIT_TIME
959 , uitem.OPTIONAL_WAIT_TIME_UOM OPTIONAL_WAIT_TIME_UOM
960 , NULL escalated_yn
961 , uitem.escalate_yn escalate_yn
962 , uitem.escalate_wait_time escalate_wait_time
963 , uitem.escalate_wait_time_uom escalate_wait_time_uom
964 , uitem.NOTIFY_YN NOTIFY_YN
965 , uitem.resourcE_id resource_id
966 , uitem.custom_workflow_type custom_workflow_type
967 FROM IEX_STRATEGIES stry
968 , IEX_STRATEGY_user_items uitem
969 , IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
970 WHERE stry.STRATEGY_ID = uitem.STRATEGY_ID
971 and uitem.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
972 AND stry_temp_wkitem.enabled_flag = 'Y' -- added for bug 7299555
973 and stry.strategy_id =p_strategy_id
974 AND not exists
975 -- exclude useritem whoch is already a workitem
976 ( select 'x' from iex_strategy_work_items wkitem
977 where wkitem.strategy_id = stry.strategy_id
978 and wkitem.work_item_template_id = uitem.work_item_temp_id
979 and uitem.work_item_order = wkitem.work_item_order
980 and wkitem.strategy_id =p_strategy_id)
981 order by ORDER_BY;
982
983 /*cursor c_get_competence_id (p_work_item_temp_id NUMBER) IS
984 SELECT competence_id from iex_stry_temp_work_items_vl
985 where work_item_temp_id =p_work_item_temp_id;
986 */
987
988 -- Start Bug 6717880/7170165 by Ehuh
989 Cursor c_get_accloc(p_str_id number) Is
990 select account_number,location from iex_strategies_bali_v
991 where strategy_id = p_str_id;
992
993 l_location varchar2(50);
994 l_acct_number varchar2(50);
995 l_assignee varchar2(50);
996 -- End Bug 6717880/7170165 by Ehuh
997
998 cursor c_get_competence_id (p_work_item_temp_id NUMBER) IS
999 SELECT competence_id from iex_strategy_work_skills
1000 where work_item_temp_id =p_work_item_temp_id;
1001
1002 cursor c_get_callback_dt (p_work_item_temp_id NUMBER) IS
1003 SELECT callback_wait, callback_uom,optional_yn,option_wait_time,
1004 option_wait_time_uom,escalate_yn, notify_yn,workflow_item_type,work_type,
1005 category_type,fulfil_temp_id
1006 from iex_stry_temp_work_items_vl
1007 where work_item_temp_id =p_work_item_temp_id;
1008
1009 l_strategy_work_item_rec IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
1010 l_return_status VARCHAR2(1) ;
1011 l_msg_count NUMBER;
1012 l_msg_data VARCHAR2(32767);
1013 x_work_item_id NUMBER;
1014 x_schedule_start DATE;
1015 x_schedule_end DATE;
1016 l_api_version NUMBER := 1.0;
1017 l_competence_tab tab_of_comp_id;
1018 l_index NUMBER :=1;
1019 l_workitem_name varchar2(2000);
1020
1021 l_Assignment_level varchar2(100);
1022 l_Default_Resource_ID number;
1023 bReturn Boolean;
1024 l_wkitem_status varchar2(30); --bug#5874874 gnramasa
1025 l_str_level_count number; -- Added for bug 8708271 multi level strategy
1026
1027 CURSOR c_work_item_temp_resource(p_work_item_temp_id number) IS
1028 SELECT temp_resource_id FROM iex_stry_temp_work_items_b
1029 WHERE work_item_temp_id = p_work_item_temp_id;
1030
1031 BEGIN
1032 -- initialize variable
1033 l_Default_Resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
1034 l_Resource_ID := l_Default_Resource_ID;
1035
1036 l_Assignment_Level := NVL(FND_PROFILE.VALUE('IEX_ACCESS_LEVEL'),'PARTY');
1037 l_return_status :=FND_API.G_RET_STS_SUCCESS;
1038
1039 -- IF PG_DEBUG < 10 THEN
1040 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1041 iex_debug_pub.logmessage ('populate_schedule_times: ' || '**** BEGIN create_work_item ************');
1042 END IF;
1043
1044 -- Start for bug 8708271 pnaveenk multi level strategy
1045
1046 Begin
1047 SELECT count(*) into l_str_level_count
1048 FROM IEX_LOOKUPS_V
1049 WHERE LOOKUP_TYPE='IEX_RUNNING_LEVEL'
1050 AND iex_utilities.validate_running_level(LOOKUP_CODE)='Y';
1051 Exception
1052 when others then
1053 iex_debug_pub.logmessage (' Exception in str_level_count ');
1054 End;
1055
1056 FOR c_get_witem_rec in c_get_next_witem
1057 (p_strategy_id,
1058 p_template_id )
1059 LOOP
1060 -- check to see if the work item has not been created
1061 if c_get_witem_rec.status ='NOTCREATED'
1062 THEN
1063 -- then the status is UNASSIGNED .. create the work item
1064 --get the competence id from the work item template table
1065
1066 /* OPEN c_get_competence_id(c_get_witem_rec.work_item_template_id);
1067 FETCH c_get_competence_id INTO l_competence_id;
1068 CLOSE c_get_competence_id;
1069 */
1070 -- start for bug 13389462 pnaveenk
1071 OPEN c_work_item_temp_resource(c_get_witem_rec.work_item_template_id);
1072 FETCH c_work_item_temp_resource INTO l_resource_id;
1073 CLOSE c_work_item_temp_resource;
1074
1075 IF l_resource_id IS NULL THEN
1076 FOR c_rec IN c_get_competence_id(c_get_witem_rec.work_item_template_id)
1077 LOOP
1078 l_competence_tab(l_index):=c_rec.competence_id;
1079 l_index :=l_index +1;
1080 END LOOP;
1081
1082 -- IF PG_DEBUG < 10 THEN
1083 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1084 iex_debug_pub.logmessage ('populate_schedule_times: ' || 'No. of competence is =>'||l_competence_tab.count);
1085 END IF;
1086 -- IF PG_DEBUG < 10 THEN
1087 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1088 iex_debug_pub.logmessage ('populate_schedule_times: ' || 'in create work item private and going to call get_resource');
1089 END IF;
1090
1091 -- get resource for the given competence_id
1092 -- if resource is not returned, return out NOCOPY of the procedure
1093 --get_resource(p_party_id,l_competence_id,l_resource_id);
1094 -- Begin- Andre 07/28/2004 - Add bill to assignmnet
1095 -- Begin- Kasreeni 01/17/2005 - Modified for multiple level resource check
1096 -- get_resource(p_party_id,l_competence_tab,l_resource_id);
1097 begin
1098 select jtf_object_type, jtf_object_id, cust_account_id,customer_site_use_id
1099 into l_object_code, l_object_id, l_cust_account_id,l_siteuse_id
1100 from iex_strategies
1101 where strategy_id = p_strategy_id;
1102 exception
1103 when others then
1104 iex_debug_pub.logmessage ('populate_schedule_times: More than one row for this Strategy. Exception on selecting strategy!!!!!');
1105 end;
1106
1107 /* For Party, we try party only */
1108 if l_object_code = 'PARTY' then
1109 if l_str_level_count >1 then
1110 l_Assignment_Level := 'PARTY';
1111 end if;
1112
1113 if l_Assignment_Level = 'PARTY' then
1114
1115 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1116 iex_debug_pub.logmessage ('Calling Get_Resource! Party Level :Party ID ' || p_Party_ID);
1117 end if;
1118 get_resource(p_party_id,l_competence_tab,l_resource_id);
1119
1120 else
1121 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1122 iex_debug_pub.logmessage ('No Territory Access Call :Party ID ' || p_Party_ID);
1123 end if;
1124 end if;
1125
1126 /* For Account Level, we try Account, if missed try Party */
1127 elsif l_object_code = 'IEX_ACCOUNT' then
1128 if l_str_level_count >1 then
1129 l_Assignment_Level := 'ACCOUNT';
1130 end if;
1131 if l_Assignment_Level = 'PARTY' then
1132
1133 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1134 iex_debug_pub.logmessage ('Calling Get_Resource! Party Level :Party ID ' || p_Party_ID);
1135 end if;
1136 get_resource(p_party_id,l_competence_tab,l_resource_id);
1137
1138 elsif l_Assignment_level = 'ACCOUNT' then
1139
1140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1141 iex_debug_pub.logmessage ('Calling Get_Resource! Account Level :Account ID ' || l_Object_ID);
1142 end if;
1143
1144 bReturn:=get_account_resource(l_object_id, l_competence_tab, l_resource_id);
1145
1146 else
1147
1148 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1149 iex_debug_pub.logmessage ('No Territory Access Call :Party ID ' || p_Party_ID);
1150 end if;
1151
1152 end if;
1153
1154 else
1155 if l_str_level_count >1 then
1156 l_Assignment_Level := 'BILL_TO';
1157 end if;
1158 if l_Assignment_Level = 'PARTY' then
1159
1160 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1161 iex_debug_pub.logmessage ('Calling Get_Resource! Party Level :Party ID ' || p_Party_ID);
1162 end if;
1163 get_resource(p_party_id,l_competence_tab,l_resource_id);
1164
1165 elsif l_Assignment_level = 'ACCOUNT' then
1166
1167 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1168 iex_debug_pub.logmessage ('Calling Get_Resource! Account Level :Account ID ' || l_cust_account_ID);
1169 end if;
1170
1171 bReturn:=get_account_resource(l_cust_account_id, l_competence_tab, l_resource_id);
1172
1173 else
1174
1175 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1176 iex_debug_pub.logmessage ('Calling Get_Resource! Bill To Level :Site Use ID ' || l_Object_ID);
1177 end if;
1178 --Bug5373412. Fix By LKKUMAR on 11-July-2006.Start.
1179 --bReturn:=get_billto_resource(l_object_id,l_competence_tab,l_resource_id);
1180 bReturn:=get_billto_resource(l_siteuse_id,l_competence_tab,l_resource_id);
1181 --Bug5373412. Fix By LKKUMAR on 11-July-2006.End.
1182 end if;
1183
1184 end if;
1185 END if; -- l_resource_id is null
1186
1187 -- end for bug 8708271 pnaveenk multi level strategy
1188 -- End - Kasreeni 01/17/2005 - Modified for multiple level resource check
1189 -- End- Andre 07/28/2004 - Add bill to assignmnet
1190
1191 -- IF PG_DEBUG < 10 THEN
1192 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1193 iex_debug_pub.logmessage ('populate_schedule_times: ' || 'after get_resource and resource_id ='||
1194 l_resource_id);
1195 END IF;
1196
1197 if l_resource_id is null then
1198 l_Resource_id := l_Default_Resource_ID;
1199 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1200 iex_debug_pub.logmessage ('populate_schedule_times: ' || 'Assigning Default resource_id ='|| l_resource_id);
1201 end if;
1202 end if;
1203
1204
1205 if l_resource_id is null then
1206
1207 x_return_Status :=FND_API.G_RET_STS_ERROR;
1208 x_error_msg := 'resource_id is Null';
1209 -- IF PG_DEBUG < 10 THEN
1210 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1211 iex_debug_pub.logmessage ('populate_schedule_times: ' || 'resource id is null ');
1212 END IF;
1213 return;
1214 end if;
1215
1216 -- resource id is found, ready for creating work item in
1217 -- the database.(finally)
1218 -- IF PG_DEBUG < 10 THEN
1219 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1220 iex_debug_pub.logMessage ('populate_schedule_times: ' || 'ready to insert work items');
1221 END IF;
1222 -- IF PG_DEBUG < 10 THEN
1223 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1224 iex_debug_pub.logMessage ('populate_schedule_times: ' || 'work_item_template_id'||c_get_witem_rec.work_item_template_id);
1225 END IF;
1226 -- IF PG_DEBUG < 10 THEN
1227 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1228 iex_debug_pub.logMessage ('populate_schedule_times: ' || 'status_code'||c_get_witem_rec.status);
1229 END IF;
1230
1231 -- bug 10133319
1232 l_strategy_work_item_rec.pre_execution_wait := c_get_witem_rec.pre_execution_wait;
1233 l_strategy_work_item_rec.pre_execution_time_uom := c_get_witem_rec.pre_execution_time_uom;
1234 l_strategy_work_item_rec.post_execution_wait := c_get_witem_rec.post_execution_wait;
1235 l_strategy_work_item_rec.execution_time_uom := c_get_witem_rec.post_execution_time_uom;
1236 l_strategy_work_item_rec.xdo_temp_id := c_get_witem_rec.xdo_template_id;
1237 l_strategy_work_item_rec.optional_yn := c_get_witem_rec.optional_yn;
1238 l_strategy_work_item_rec.optional_wait_time := c_get_witem_rec.optional_wait_time;
1239 l_strategy_work_item_rec.optional_wait_time_uom := c_get_witem_rec.optional_wait_time_uom;
1240 l_strategy_work_item_rec.escalated_yn := c_get_witem_rec.escalated_yn;
1241 l_strategy_work_item_rec.escalate_yn := c_get_witem_rec.escalate_yn;
1242 l_strategy_work_item_rec.escalate_wait_time := c_get_witem_rec.escalate_wait_time;
1243 l_strategy_work_item_rec.escalate_wait_time_uom := c_get_witem_rec.escalate_wait_time_uom;
1244 l_strategy_work_item_rec.notify_yn := c_get_witem_rec.notify_yn;
1245 l_strategy_work_item_rec.custom_workflow_type := c_get_witem_rec.custom_workflow_type;
1246 IF c_get_witem_rec.resource_id IS NOT NULL THEN
1247 l_resource_id := c_get_witem_rec.resource_id;
1248 END IF;
1249
1250 l_strategy_work_item_rec.resource_id :=l_resource_id;
1251 l_strategy_work_item_rec.work_item_template_id
1252 :=c_get_witem_rec.work_item_template_id;
1253 l_strategy_work_item_rec.strategy_id := p_strategy_id;
1254 --begin bug#4506922 schekuri 06-Dec-2005
1255 -- All work items are created as PRE-WAIT
1256 /*l_strategy_work_item_rec.status_code
1257 :='OPEN';*/
1258 l_strategy_work_item_rec.status_code
1259 :='PRE-WAIT';
1260 --end bug#4506922 schekuri 06-Dec-2005
1261 l_strategy_work_item_rec.strategy_temp_id :=p_template_id;
1262 l_strategy_work_item_rec.work_item_order :=c_get_witem_rec.order_by;
1263
1264 --populate schedule_start and schedule_end
1265 -- l_strategy_work_item_rec contains pre wait,post wait,notify,resource name,optional,escalate values
1266 populate_schedule_times(
1267 p_work_item_temp_id =>c_get_witem_rec.work_item_template_id,
1268 itemtype =>itemtype,
1269 itemkey =>itemkey,
1270 p_strategy_work_item_rec => l_strategy_work_item_rec,
1271 x_schedule_start =>x_schedule_start,
1272 x_schedule_end =>x_schedule_end);
1273
1274 --populate notification_resource
1275 set_notification_resources(
1276 p_resource_id =>l_resource_id,
1277 itemtype =>itemtype,
1278 itemkey =>itemkey);
1279
1280 -- Start Bug 6717880/7170165 by Ehuh
1281 begin
1282
1283 Open c_get_accloc(p_strategy_id);
1284 Fetch c_get_accloc into l_acct_number,l_location;
1285 if c_get_accloc%NOTFOUND then null;
1286 else
1287 wf_engine.SetItemAttrText(
1288 itemtype =>itemtype,
1289 itemkey =>itemkey,
1290 aname => 'ACCOUNT_NUMBER',
1291 avalue => l_acct_number);
1292
1293 wf_engine.SetItemAttrText(
1294 itemtype =>itemtype,
1295 itemkey =>itemkey,
1296 aname => 'BILL_TO',
1297 avalue => l_location);
1298 end if;
1299 close c_get_accloc;
1300
1301 exception
1302 when others then null;
1303 end;
1304 -- End Bug 6717880/7170165 by Ehuh
1305
1306 l_strategy_work_item_rec.schedule_start :=x_schedule_start;
1307 l_strategy_work_item_rec.schedule_end :=x_schedule_end;
1308 l_strategy_work_item_rec.execute_start :=SYSDATE;
1309
1310 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1311 iex_debug_pub.logmessage ('before calling create_work_pvt.create');
1312 END IF;
1313
1314 iex_strategy_work_items_pvt.create_strategy_work_items
1315 (P_Api_Version_Number =>2.0,
1316 P_Init_Msg_List =>FND_API.G_TRUE,
1317 P_Commit =>FND_API.G_FALSE,
1318 p_validation_level =>FND_API.G_VALID_LEVEL_FULL,
1319 p_strategy_work_item_rec =>l_strategy_work_item_rec,
1320 x_work_item_id =>x_work_item_id,
1321 x_return_status =>l_return_status,
1322 x_msg_count =>l_msg_count,
1323 x_msg_data =>l_msg_data);
1324 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1325 iex_debug_pub.logmessage ('after calling create_work_pvt.create');
1326 iex_debug_pub.logmessage ('and l_return_status from the pvt ='||l_return_status);
1327 END IF;
1328 x_return_status :=l_return_Status;
1329 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
1330 --update nextwork item i nthe strategy table
1331 IEX_STRY_UTL_PUB.UPDATE_NEXT_WORK_ITEM
1332 (p_api_version => l_api_version,
1333 p_init_msg_list => FND_API.G_TRUE,
1334 p_commit => FND_API.G_FALSE,
1335 x_msg_count => l_msg_count,
1336 x_msg_data => l_msg_data,
1337 x_return_status => l_return_status,
1338 p_strategy_id => p_strategy_id,
1339 p_work_item_id => x_work_item_id);
1340
1341 wf_engine.SetItemAttrNumber(
1342 itemtype =>itemtype,
1343 itemkey =>itemkey,
1344 aname => 'WORK_ITEMID',
1345 avalue => x_work_item_id);
1346
1347 if x_work_item_id is not null then
1348 begin
1349 select a.name,b.status_code into l_workitem_name , l_wkitem_status ----bug#5874874 gnramasa
1350 from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
1351 where b.work_item_template_id = a.work_item_temp_id
1352 and b.work_item_id = x_work_item_id;
1353
1354 wf_engine.SetItemAttrText(itemtype =>itemtype,
1355 itemkey => itemkey,
1356 aname => 'WORK_ITEM_NAME',
1357 avalue => l_workitem_name);
1358 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1359 iex_debug_pub.logmessage ('work_item_name' ||l_workitem_name);
1360 END IF;
1361
1362 --Begin bug#5874874 gnramasa 25-Apr-2007
1363 --Update the UWQ summary table after creating workitem in OPEN status.
1364 if l_wkitem_status='OPEN' then
1365 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(x_work_item_id);
1366 end if;
1367 --End bug#5874874 gnramasa 25-Apr-2007
1368
1369 exception
1370 when others then
1371 null;
1372 end;
1373 end if;
1374
1375 -- if next work_item updation fails then also get the error message
1376 -- get error message and passit pass it
1377 --add new message
1378 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1379 fnd_message.set_name('IEX', 'IEX_UPD_NEXT_WORK_ITEM_FAILED');
1380 fnd_msg_pub.add;
1381 FND_MSG_PUB.Count_And_Get
1382 ( p_count => l_msg_count,
1383 p_data => l_msg_data
1384 );
1385 Get_Messages(l_msg_count,x_error_msg);
1386 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1387 iex_debug_pub.logmessage('error message is ' ||x_error_msg);
1388 END IF;
1389 END IF;
1390
1391 -- reset activity_name attribute each time a work item is created
1392 --06/26
1393 wf_engine.SetItemAttrText(itemtype => itemtype,
1394 itemkey => itemkey,
1395 aname => 'ACTIVITY_NAME',
1396 avalue => NULL);
1397
1398 ELSE
1399 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1400 iex_debug_pub.logmessage('X_MSG_COUNT' ||l_msg_count);
1401 iex_debug_pub.logmessage('X_MSG_DATA' ||l_msg_data);
1402 END IF;
1403 -- get error message and passit pass it
1404 --
1405
1406 --add new message
1407 fnd_message.set_name('IEX', 'IEX_METAPHOR_CREATION_FAILED');
1408 fnd_msg_pub.add;
1409 FND_MSG_PUB.Count_And_Get
1410 ( p_count => l_msg_count,
1411 p_data => l_msg_data
1412 );
1413
1414 Get_Messages(l_msg_count,x_error_msg);
1415 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1416 iex_debug_pub.logmessage('error message is ' || x_error_msg);
1417 END IF;
1418 end if;
1419 EXIT;
1420 END IF;
1421
1422 x_return_status :=l_return_Status;
1423 END LOOP;
1424 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1425 iex_debug_pub.logmessage ('after calling create_work_pvt.create ' ||
1426 'and x_return status=>'||x_return_status );
1427 END IF;
1428 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1429 iex_debug_pub.logmessage ('**** END create_work_item ************');
1430 END IF;
1431 End create_work_item;
1432
1433 -----------get the status of the process---------------------------------------
1434 -- to see if the process is SUSPEND(wf_engine.eng_suspended )
1435
1436 PROCEDURE process_status ( p_process in varchar2,
1437 p_itemtype in varchar2,
1438 p_itemkey in varchar2,
1439 x_status out NOCOPY varchar2) IS
1440 rootid NUMBER;
1441
1442
1443 BEGIN
1444 rootid :=Wf_Process_Activity.RootInstanceId(p_itemtype,
1445 p_itemkey,
1446 p_process);
1447
1448 -- IF PG_DEBUG < 10 THEN
1449 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1450 iex_debug_pub.logmessage ('process_status: ' || 'root is '||rootid);
1451 END IF;
1452 Wf_Item_Activity_Status.Status(p_itemtype
1453 ,p_itemkey
1454 ,rootid
1455 ,x_status);
1456
1457 -- IF PG_DEBUG < 10 THEN
1458 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1459 iex_debug_pub.logmessage ('process_status: ' || 'status is '||x_status);
1460 END IF;
1461 EXCEPTION WHEN OTHERS THEN
1462 -- send the status has active
1463 x_status :=wf_engine.eng_active;
1464
1465 END process_status;
1466
1467 --- populate strategy mailer record type
1468 PROCEDURE populate_strategy_mailer
1469 (itemtype IN VARCHAR2,
1470 itemkey IN VARCHAR2,
1471 l_strategy_mailer_rec OUT NOCOPY iex_strategy_work_pub.STRATEGY_Mailer_Rec_Type) IS
1472 BEGIN
1473 -- IF PG_DEBUG < 10 THEN
1474 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1475 iex_debug_pub.logmessage ('**** BEGIN populate_strategy_mailer ************');
1476 END IF;
1477 l_strategy_mailer_rec.strategy_id := wf_engine.GetItemAttrNumber(
1478 itemtype => itemtype,
1479 itemkey => itemkey,
1480 aname => 'STRATEGY_ID');
1481
1482 l_strategy_mailer_rec.delinquency_id := wf_engine.GetItemAttrNumber(
1483 itemtype => itemtype,
1484 itemkey => itemkey,
1485 aname => 'DELINQUENCY_ID');
1486
1487
1488 l_strategy_mailer_rec.workitem_id := wf_engine.GetItemAttrNumber(
1489 itemtype => itemtype,
1490 itemkey => itemkey,
1491 aname => 'WORK_ITEMID');
1492
1493 l_strategy_mailer_rec.user_id := wf_engine.GetItemAttrNumber(
1494 itemtype => itemtype,
1495 itemkey => itemkey,
1496 aname => 'USER_ID');
1497
1498 l_strategy_mailer_rec.resp_id := wf_engine.GetItemAttrNumber(
1499 itemtype => itemtype,
1500 itemkey => itemkey,
1501 aname => 'RESP_ID');
1502
1503 l_strategy_mailer_rec.resp_appl_id := wf_engine.GetItemAttrNumber(
1504 itemtype => itemtype,
1505 itemkey => itemkey,
1506 aname => 'RESP_APPL_ID');
1507 -- IF PG_DEBUG < 10 THEN
1508 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1509 iex_debug_pub.logmessage ('populate_strategy_mailer: ' || 'resp_id ' || l_strategy_mailer_rec.resp_id ||
1510 ' USER_ID ' || l_strategy_mailer_rec.user_id||' APPL ID '
1511 ||l_strategy_mailer_rec.resp_appl_id);
1512 END IF;
1513
1514 -- IF PG_DEBUG < 10 THEN
1515 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1516 iex_debug_pub.logmessage ('**** END populate_strategy_mailer ************');
1517 END IF;
1518 END populate_strategy_mailer;
1519
1520 --custom workflow record population
1521 PROCEDURE populate_custom_workflow
1522 (itemtype IN VARCHAR2,
1523 itemkey IN VARCHAR2,
1524 p_custom_itemtype IN VARCHAR2,
1525 l_custom_wf_rec OUT NOCOPY IEX_STRY_CUWF_PUB.CUSTOM_WF_Rec_Type) IS
1526 BEGIN
1527 -- IF PG_DEBUG < 10 THEN
1528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1529 iex_debug_pub.logmessage ('**** BEGIN populate_custom_workflow ************');
1530 END IF;
1531 l_custom_wf_rec.strategy_id := wf_engine.GetItemAttrNumber(
1532 itemtype => itemtype,
1533 itemkey => itemkey,
1534 aname => 'STRATEGY_ID');
1535
1536
1537
1538 l_custom_wf_rec.workitem_id := wf_engine.GetItemAttrNumber(
1539 itemtype => itemtype,
1540 itemkey => itemkey,
1541 aname => 'WORK_ITEMID');
1542
1543 l_custom_wf_rec.custom_itemtype := p_custom_itemtype;
1544
1545 l_custom_wf_rec.user_id := wf_engine.GetItemAttrNumber(
1546 itemtype => itemtype,
1547 itemkey => itemkey,
1548 aname => 'USER_ID');
1549
1550 l_custom_wf_rec.resp_id := wf_engine.GetItemAttrNumber(
1551 itemtype => itemtype,
1552 itemkey => itemkey,
1553 aname => 'RESP_ID');
1554
1555 l_custom_wf_rec.resp_appl_id := wf_engine.GetItemAttrNumber(
1556 itemtype => itemtype,
1557 itemkey => itemkey,
1558 aname => 'RESP_APPL_ID');
1559
1560
1561 -- IF PG_DEBUG < 10 THEN
1562 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1563 iex_debug_pub.logmessage ('populate_custom_workflow: ' || 'strategy_id ' || l_custom_wf_rec.strategy_id);
1564 END IF;
1565 -- IF PG_DEBUG < 10 THEN
1566 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1567 iex_debug_pub.logmessage ('populate_custom_workflow: ' || 'workitem_id ' || l_custom_wf_rec.workitem_id);
1568 END IF;
1569 -- IF PG_DEBUG < 10 THEN
1570 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1571 iex_debug_pub.logmessage ('populate_custom_workflow: ' || 'custom_itemtype ' || l_custom_wf_rec.custom_itemtype);
1572 END IF;
1573 -- IF PG_DEBUG < 10 THEN
1574 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1575 iex_debug_pub.logmessage ('populate_custom_workflow: ' || 'resp_id ' || l_custom_wf_rec.resp_id ||
1576 ' USER_ID ' || l_custom_wf_rec.user_id||' APPL ID '
1577 ||l_custom_wf_rec.resp_appl_id);
1578 END IF;
1579
1580 -- IF PG_DEBUG < 10 THEN
1581 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1582 iex_debug_pub.logmessage ('**** END populate_custom_workflow ************');
1583 END IF;
1584 END populate_custom_workflow;
1585
1586 -------------end of private procedures and funtion -------------------------
1587 ------------- PUBLIC PROCEDURES---------------------------------------------
1588
1589 -----get messages from the server side-----------------
1590 PROCEDURE Get_Messages (
1591 p_message_count IN NUMBER,
1592 x_msgs OUT NOCOPY VARCHAR2)
1593 IS
1594 l_msg_list VARCHAR2(5000) ;
1595 l_temp_msg VARCHAR2(2000);
1596 l_appl_short_name VARCHAR2(50) ;
1597 l_message_name VARCHAR2(30) ;
1598 l_id NUMBER;
1599 l_message_num NUMBER;
1600 l_msg_count NUMBER;
1601 l_msg_data VARCHAR2(2000);
1602
1603 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
1604 SELECT application_id
1605 FROM fnd_application_vl
1606 WHERE application_short_name = x_short_name;
1607
1608 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
1609 SELECT msg.message_number
1610 FROM fnd_new_messages msg, fnd_languages_vl lng
1611 WHERE msg.message_name = x_msg
1612 and msg.application_id = x_id
1613 and lng.LANGUAGE_CODE = msg.language_code
1614 and lng.language_id = x_lang_id;
1615 BEGIN
1616 -- initialize variable
1617 l_msg_list := '';
1618
1619 -- IF PG_DEBUG < 10 THEN
1620 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1621 iex_debug_pub.logmessage ('**** BEGIN Get_Messages ************');
1622 END IF;
1623 FOR l_count in 1..p_message_count LOOP
1624
1625 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
1626 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
1627 OPEN Get_Appl_Id (l_appl_short_name);
1628 FETCH Get_Appl_Id into l_id;
1629 CLOSE Get_Appl_Id;
1630 l_message_num := NULL;
1631
1632 IF l_id is not NULL
1633 THEN
1634 OPEN Get_Message_Num (l_message_name, l_id,
1635 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
1636 FETCH Get_Message_Num into l_message_num;
1637 CLOSE Get_Message_Num;
1638 END IF;
1639
1640 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
1641
1642 IF NVL(l_message_num, 0) <> 0
1643 THEN
1644 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
1645 ELSE
1646 l_temp_msg := NULL;
1647 END IF;
1648
1649 IF l_count = 1
1650 THEN
1651 l_msg_list := l_msg_list || l_temp_msg ||
1652 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
1653 ELSE
1654 l_msg_list := l_msg_list || l_temp_msg ||
1655 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
1656 END IF;
1657
1658 l_msg_list := l_msg_list || '';
1659
1660 END LOOP;
1661
1662 x_msgs := l_msg_list;
1663 -- IF PG_DEBUG < 10 THEN
1664 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1665 iex_debug_pub.logmessage ('Get_Messages: ' || 'L_MSG_LIST'||l_msg_list);
1666 END IF;
1667 -- IF PG_DEBUG < 10 THEN
1668 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1669 iex_debug_pub.logmessage ('**** END Get_Messages ************');
1670 END IF;
1671 END Get_Messages;
1672
1673
1674
1675 ----------- procedure check_work_items_completed ------------------------------
1676 /**
1677 * check to see if there are any pending
1678 * work items to be processed
1679 **/
1680 procedure check_work_items_completed(
1681 itemtype in varchar2,
1682 itemkey in varchar2,
1683 actid in number,
1684 funcmode in varchar2,
1685 result out NOCOPY varchar2) IS
1686
1687
1688
1689 cursor c_get_strat_template_id (p_strategy_id NUMBER) IS
1690 select strategy_template_id from iex_strategies
1691 where strategy_id =p_strategy_id;
1692
1693
1694 cursor c_get_next_witem(p_strategy_id NUMBER,
1695 p_template_id NUMBER)
1696 is
1697 /*select sxref.strategy_temp_id TEMPLATE_ID,
1698 sxref.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
1699 sxref.work_item_order ORDER_BY
1700 ,nvl(swit.status_code,'NOTCREATED') STATUS
1701 ,swit.work_item_id WORK_ITEM_ID
1702 ,swit.strategy_id STRATEGY_ID
1703 from iex_strategy_work_temp_xref sxref
1704 ,iex_strategy_work_items swit
1705 where sxref.strategy_temp_id =p_template_id
1706 and swit.work_item_template_id(+) =sxref.work_item_temp_id
1707 and swit.strategy_id(+) =p_strategy_id
1708 union all
1709 select susit.strategy_template_id TEMPLATE_ID,
1710 susit.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
1711 susit.work_item_order ORDER_BY
1712 ,nvl(swit.status_code,'NOTCREATED') STATUS
1713 ,swit.work_item_id WORK_ITEM_ID
1714 ,susit.strategy_id STRATEGY_ID
1715 from iex_strategy_user_items susit
1716 ,iex_strategy_work_items swit
1717 where susit.strategy_id =p_strategy_id
1718 and swit.work_item_template_id(+) =susit.work_item_temp_id
1719 and swit.strategy_id(+) =p_strategy_id
1720 order by order_by;
1721 */
1722
1723 --created work items
1724 SELECT wkitem.strategy_id STRATEGY_ID,
1725 wkitem.strategy_temp_id TEMPLATE_ID,
1726 wkitem.work_item_order ORDER_BY,
1727 wkitem.work_item_id WORK_ITEM_ID,
1728 wkitem.work_item_template_id WORK_ITEM_TEMPLATE_ID,
1729 wkitem.status_code STATUS
1730 from iex_strategy_work_items wkitem,
1731 iex_stry_temp_work_items_vl stry_temp_wkitem
1732 WHERE
1733 wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id
1734 and wkitem.strategy_id =p_strategy_id
1735 --to be created work items
1736 union all
1737 SELECT stry.STRATEGY_ID STRATEGY_ID
1738 , xref.STRATEGY_TEMP_ID TEMPLATE_ID
1739 , xref.WORK_ITEM_ORDER ORDER_BY
1740 , TO_NUMBER(NULL) WORK_ITEM_ID
1741 , xref.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
1742 , 'NOTCREATED' STATUS
1743 FROM IEX_STRATEGIES stry
1744 , IEX_STRATEGY_WORK_TEMP_XREF xref
1745 , IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
1746 WHERE stry.STRATEGY_TEMPLATE_ID = xref.STRATEGY_TEMP_ID
1747 and xref.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
1748 and stry.strategy_id =p_strategy_id
1749 --not in workitems table
1750 AND not exists ( select 'x' from iex_strategy_work_items wkitem where
1751 wkitem.strategy_id = stry.strategy_id
1752 and wkitem.work_item_template_id = xref.work_item_temp_id
1753 and wkitem.work_item_order = xref.work_item_order
1754 and wkitem.strategy_id =p_strategy_id
1755 )
1756 ----skip workitems which is status-ed SKIP
1757 and not exists ( select 'x' from iex_strategy_user_items uitems where
1758 uitems.strategy_id = stry.strategy_id and
1759 uitems.work_item_temp_id = xref.work_item_temp_id and
1760 uitems.work_item_order = xref.work_item_order and
1761 uitems.operation = 'SKIP'
1762 and uitems.strategy_id =p_strategy_id
1763 )
1764 and (xref.work_item_order > (select max(wkitem_order) from iex_work_item_bali_v
1765 where strategy_id = p_strategy_id and start_time is not null)
1766 or (select count(*) from iex_work_item_bali_v where strategy_id = p_strategy_id ) = 0
1767 ) -- later on assignment of prior work items, and case of initial creation of wkitem
1768 -- get all user items
1769 union all
1770 SELECT stry.STRATEGY_ID STRATEGY_ID
1771 , uitem.STRATEGY_TEMPLATE_ID TEMPLATE_ID
1772 , uitem.WORK_ITEM_ORDER ORDER_BY
1773 , TO_NUMBER(NULL) WORK_ITEM_ID
1774 , uitem.WORK_ITEM_TEMP_ID WORK_ITEM_TEMPLATE_ID
1775 , uitem.operation STATUS
1776 FROM IEX_STRATEGIES stry
1777 , IEX_STRATEGY_user_items uitem
1778 , IEX_STRY_TEMP_WORK_ITEMS_VL stry_temp_wkitem
1779 WHERE stry.STRATEGY_ID = uitem.STRATEGY_ID
1780 and uitem.WORK_ITEM_TEMP_ID = stry_temp_wkitem.WORK_ITEM_TEMP_ID
1781 and stry.strategy_id =p_strategy_id
1782 AND not exists
1783 -- exclude useritem whoch is already a workitem
1784 ( select 'x' from iex_strategy_work_items wkitem
1785 where wkitem.strategy_id = stry.strategy_id
1786 and wkitem.work_item_template_id = uitem.work_item_temp_id
1787 and uitem.work_item_order = wkitem.work_item_order
1788 and wkitem.strategy_id =p_strategy_id)
1789 order by ORDER_BY;
1790
1791 l_strategy_id NUMBER ;
1792 l_count NUMBER :=0;
1793 l_strategy_status VARCHAR2(100);
1794 l_strategy_template_id NUMBER;
1795 l_work_item_id NUMBER ;
1796 l_value VARCHAR2(300);
1797
1798 Begin
1799
1800 if funcmode <> 'RUN' then
1801 result := wf_engine.eng_null;
1802 return;
1803 end if;
1804
1805 --IF PG_DEBUG < 10 THEN
1806 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1807 iex_debug_pub.logmessage ('**** BEGIN check_work_items_completed ************');
1808 END IF;
1809 --IF PG_DEBUG < 10 THEN
1810 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1811 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'ITEMTYPE =>'||itemtype);
1812 END IF;
1813 --IF PG_DEBUG < 10 THEN
1814 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1815 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'ITEMKEY =>'||itemkey);
1816 END IF;
1817
1818 /* l_value :=wf_engine.GetActivityLabel(actid);
1819 wf_engine.SetItemAttrText(itemtype => itemtype,
1820 itemkey => itemkey,
1821 aname => 'ACTIVITY_NAME',
1822 avalue => l_value);
1823 --IF PG_DEBUG < 10 THEN
1824 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1825 iex_debug_pub.logMessage('check_work_items_completed: ' || 'ACTIVITYNAME' ||l_value);
1826 END IF;
1827 */
1828
1829 -- if the l_work_item_id is NULL
1830 -- don't check for pending work items
1831 -- set result to 'Y' indicating there are no pending
1832 -- items.The next node in the workflow 'close_strategy'
1833 -- will close the pending work items to the right status
1834 --- depending on the strategy_status attribute which is set in
1835 --send_signal process
1836
1837 /**
1838 * 04/17/02
1839 * if the strategy status is changed to 'OPEN' from ONHOLD
1840 * work item id is not passed, but we still have to
1841 * check for pending work item if status is 'OPEN'
1842 **/
1843
1844 l_strategy_status := wf_engine.GetItemAttrText(
1845 itemtype => itemtype,
1846 itemkey => itemkey,
1847 aname => 'STRATEGY_STATUS');
1848
1849
1850 l_work_item_id := wf_engine.GetItemAttrNumber(
1851 itemtype => itemtype,
1852 itemkey => itemkey,
1853 aname => 'WORK_ITEMID');
1854
1855
1856 --IF PG_DEBUG < 10 THEN
1857 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1858 iex_debug_pub.logmessage ('in check_work_items_completed and '||
1859 'l_work_item_id =>'||l_work_item_id);
1860 END IF;
1861
1862 IF l_work_item_id IS NULL and l_strategy_status <> 'OPEN' THEN
1863 result := wf_engine.eng_completed||':'||wf_yes;
1864 return;
1865 END IF;
1866
1867
1868
1869 ---get strategy_id from the work flow
1870
1871 l_strategy_id := wf_engine.GetItemAttrNumber(
1872 itemtype => itemtype,
1873 itemkey => itemkey,
1874 aname => 'STRATEGY_ID');
1875
1876 --IF PG_DEBUG < 10 THEN
1877 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1878 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'strategy id =>'||l_strategy_id);
1879 END IF;
1880
1881 --get strategy template id
1882 OPEN c_get_strat_template_id (l_strategy_id);
1883 FETCH c_get_strat_template_id INTO l_strategy_template_id;
1884 CLOSE c_get_strat_template_id;
1885 --IF PG_DEBUG < 10 THEN
1886 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1887 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'template id =>'||l_strategy_template_id);
1888 END IF;
1889
1890 --get the work items along with the status
1891 FOR c_get_witem_rec in c_get_next_witem
1892 (l_strategy_id,
1893 l_strategy_template_id )
1894 LOOP
1895 IF c_get_witem_rec.status IN ('NOTCREATED') THEN
1896 -- NOT IN ('COMPLETE','CANCELLED','TIMEOUT','SKIP') THEN
1897 -- there are pending work items to be processed
1898 result := wf_engine.eng_completed ||':'||wf_no;
1899 -- IF PG_DEBUG < 10 THEN
1900 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1901 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'there are pending witems to be created and result is'||
1902 '=>'||result);
1903 END IF;
1904 return;
1905 END IF;
1906
1907 END LOOP;
1908 result := wf_engine.eng_completed ||':'||wf_yes;
1909 -- IF PG_DEBUG < 10 THEN
1910 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1911 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'result =>'||result);
1912 END IF;
1913 -- IF PG_DEBUG < 10 THEN
1914 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1915 iex_debug_pub.logmessage ('**** END check_work_items_completed ************');
1916 END IF;
1917 exception
1918 when others then
1919 wf_core.context('IEX_STRATEGY_WF','check_work_items_completed',itemtype,
1920 itemkey,to_char(actid),funcmode);
1921 -- IF PG_DEBUG < 10 THEN
1922 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1923 iex_debug_pub.logmessage ('check_work_items_completed: ' || 'in error');
1924 END IF;
1925 raise;
1926
1927 end check_work_items_completed;
1928
1929
1930 ----------- procedure close_strategy ------------------------------
1931 /**
1932 * Close all the pending work items
1933 * and close the strategy
1934 * if the update fails , go and wait
1935 * for the signal again
1936 **/
1937
1938 procedure close_strategy(
1939 itemtype in varchar2,
1940 itemkey in varchar2,
1941 actid in number,
1942 funcmode in varchar2,
1943 result out NOCOPY varchar2) IS
1944
1945
1946 l_strategy_id NUMBER ;
1947 l_api_version NUMBER := 1.0;
1948 l_return_status VARCHAR2(1);
1949 l_msg_count NUMBER;
1950 l_msg_data VARCHAR2(32767);
1951 l_strategy_status VARCHAR2(100);
1952 l_work_item_id NUMBER ;
1953 l_value VARCHAR2(300);
1954 l_error VARCHAR2(32767);
1955
1956 Begin
1957 -- IF PG_DEBUG < 10 THEN
1958 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1959 iex_debug_pub.logmessage ('**** BEGIN close_strategy ************');
1960 END IF;
1961 if funcmode <> 'RUN' then
1962 result := wf_engine.eng_null;
1963 return;
1964 end if;
1965
1966 /*l_value :=wf_engine.GetActivityLabel(actid);
1967 wf_engine.SetItemAttrText(itemtype => itemtype,
1968 itemkey => itemkey,
1969 aname => 'ACTIVITY_NAME',
1970 avalue => l_value);
1971 -- IF PG_DEBUG < 10 THEN
1972 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1973 iex_debug_pub.logMessage('close_strategy: ' || 'ACTIVITYNAME' ||l_value);
1974 END IF;
1975 */
1976
1977 ---get strategy_id from the work flow
1978
1979 l_strategy_id := wf_engine.GetItemAttrNumber(
1980 itemtype => itemtype,
1981 itemkey => itemkey,
1982 aname => 'STRATEGY_ID');
1983
1984 l_strategy_status := wf_engine.GetItemAttrText(
1985 itemtype => itemtype,
1986 itemkey => itemkey,
1987 aname => 'STRATEGY_STATUS');
1988
1989 l_work_item_id := wf_engine.GetItemAttrNumber(
1990 itemtype => itemtype,
1991 itemkey => itemkey,
1992 aname => 'WORK_ITEMID');
1993
1994
1995 --if l_work_item id NULL then ,close or cancel strategy and the
1996 --pending work items. The action 'CANCEL ' or 'CLOSE is based on the
1997 -- the value of l_strategy_status.
1998
1999 -- if the l_work_item is not null then, it means all the pending work items are processed
2000 -- update the stategy_status to 'CLOSED'
2001
2002 -- IF PG_DEBUG < 10 THEN
2003 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2004 iex_debug_pub.logmessage('close_strategy: ' || ' close strategy procedure and status is =>'||l_strategy_status);
2005 END IF;
2006 -- commented for bug 11703982 PNAVEENK close the strategy even work item id is null
2007 IF l_work_item_id IS NOT NULL THEN
2008 -- normal processing, all work items are done
2009 --set the l_strategy_status to 'CLOSED'
2010 l_strategy_status := 'CLOSED';
2011 ELSIF l_strategy_status IS NULL OR (l_strategy_status NOT IN('CLOSED','CANCELLED')) THEN
2012 l_strategy_status := 'CLOSED';
2013 END IF;
2014
2015 IEX_STRY_UTL_PUB.CLOSE_STRY_AND_WITEMS(p_api_version => l_api_version,
2016 p_init_msg_list => FND_API.G_TRUE,
2017 p_commit => FND_API.G_FALSE,
2018 x_msg_count => l_msg_count,
2019 x_msg_data => l_msg_data,
2020 x_return_status => l_return_status,
2021 p_strategy_id => l_strategy_id,
2022 p_status => l_strategy_status);
2023
2024
2025 -- IF PG_DEBUG < 10 THEN
2026 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2027 iex_debug_pub.logmessage('close_strategy: ' || ' close strategy procedure and x_status is =>'||l_return_status);
2028 END IF;
2029
2030 -- if the result is 'N' then go back to
2031 -- sleep or wait mode
2032 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
2033 result := wf_engine.eng_completed ||':'||wf_yes;
2034 else
2035 result := wf_engine.eng_completed ||':'||wf_no;
2036 --set the strategy_status back to 'OPEN'
2037 --and go back to sleep mode
2038 wf_engine.SetItemAttrText(itemtype => itemtype,
2039 itemkey => itemkey,
2040 aname => 'STRATEGY_STATUS',
2041 avalue => 'OPEN');
2042
2043 --pass the error message
2044 -- get error message and passit
2045 --add new message
2046 fnd_message.set_name('IEX', 'IEX_CLOSE_STRATEGY_FAILED');
2047 fnd_msg_pub.add;
2048 FND_MSG_PUB.Count_And_Get
2049 ( p_count => l_msg_count,
2050 p_data => l_msg_data
2051 );
2052 Get_Messages(l_msg_count,l_error);
2053 -- IF PG_DEBUG < 10 THEN
2054 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2055 iex_debug_pub.logmessage('close_strategy: ' || 'error message is ' || l_error);
2056 END IF;
2057
2058 wf_engine.SetItemAttrText(itemtype => itemtype,
2059 itemkey => itemkey,
2060 aname => 'ERROR_MESSAGE',
2061 avalue => l_error);
2062
2063 end if;
2064
2065 -- IF PG_DEBUG < 10 THEN
2066 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2067 iex_debug_pub.logmessage ('**** END close_strategy ************');
2068 END IF;
2069 exception
2070 when others then
2071 wf_core.context('IEX_STRATEGY_WF',' close_strategy ',itemtype,
2072 itemkey,to_char(actid),funcmode);
2073 raise;
2074
2075 end close_strategy;
2076
2077
2078 ----------- procedure create_next_work_item------------------------------
2079 /**
2080 * Get the next work item for the strategy
2081 * from work_item_template,user_work_item for a given strategy
2082 * get resource id for the given strategy
2083 * for the matching competence id vcrom work item template
2084 * creates the work item in strategy_work_items table
2085 * update the attribute work_item_id in the workflow with the
2086 * create workitem_id.
2087 **/
2088 procedure create_next_work_item(
2089 itemtype in varchar2,
2090 itemkey in varchar2,
2091 actid in number,
2092 funcmode in varchar2,
2093 result out NOCOPY varchar2) IS
2094
2095 l_api_version NUMBER := 1.0;
2096 l_init_msg_list VARCHAR2(1) ;
2097 l_commit VARCHAR2(1) ;
2098 l_return_status VARCHAR2(1);
2099 l_msg_count NUMBER;
2100 l_msg_data VARCHAR2(32767);
2101
2102 l_strategy_id NUMBER;
2103 l_party_id NUMBER;
2104 l_strategy_template_id NUMBER;
2105 l_error VARCHAR2(32767);
2106
2107 cursor c_get_strat_template_id (p_strategy_id NUMBER) IS
2108 select strategy_template_id from iex_strategies
2109 where strategy_id =p_strategy_id;
2110 l_value VARCHAR2(300);
2111 Begin
2112 -- initialize variable
2113 l_init_msg_list := FND_API.G_TRUE;
2114 l_commit := FND_API.G_TRUE;
2115
2116 -- IF PG_DEBUG < 10 THEN
2117 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2118 iex_debug_pub.logmessage ('**** BEGIN create_next_work_item ************');
2119 END IF;
2120 if funcmode <> 'RUN' then
2121 result := wf_engine.eng_null;
2122 return;
2123 end if;
2124
2125 /*l_value :=wf_engine.GetActivityLabel(actid);
2126 wf_engine.SetItemAttrText(itemtype => itemtype,
2127 itemkey => itemkey,
2128 aname => 'ACTIVITY_NAME',
2129 avalue => l_value);
2130 --IF PG_DEBUG < 10 THEN
2131 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2132 iex_debug_pub.logMessage('create_next_work_item: ' || 'ACTIVITYNAME' ||l_value);
2133 END IF;
2134 */
2135
2136 ---get strategy_id from the work flow
2137 l_strategy_id := wf_engine.GetItemAttrNumber(
2138 itemtype => itemtype,
2139 itemkey => itemkey,
2140 aname => 'STRATEGY_ID');
2141
2142 ---get party_id from the work flow
2143 l_party_id := wf_engine.GetItemAttrNumber(
2144 itemtype => itemtype,
2145 itemkey => itemkey,
2146 aname => 'PARTY_ID');
2147
2148
2149 --get next strategy_template_id
2150 OPEN c_get_strat_template_id (l_strategy_id);
2151 FETCH c_get_strat_template_id INTO l_strategy_template_id;
2152 CLOSE c_get_strat_template_id;
2153 --IF PG_DEBUG < 10 THEN
2154 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2155 iex_debug_pub.logmessage('create_next_work_item: ' || 'in create next work item');
2156 END IF;
2157 --IF PG_DEBUG < 10 THEN
2158 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2159 iex_debug_pub.logmessage('create_next_work_item: ' || 'before calling create work item private proc');
2160 END IF;
2161
2162 --create work item in the database
2163 create_work_item (itemtype,
2164 itemkey,
2165 l_strategy_id,
2166 l_strategy_template_id,
2167 l_party_id,
2168 l_return_status,
2169 l_error);
2170
2171 --IF PG_DEBUG < 10 THEN
2172 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2173 iex_debug_pub.logmessage ('create_next_work_item: ' || ' creation of work items and status => '||l_return_status);
2174 END IF;
2175
2176 if l_return_status <>FND_API.G_RET_STS_SUCCESS THEN
2177 result := wf_engine.eng_completed ||':'||wf_no;
2178 --pass the error message
2179 wf_engine.SetItemAttrText(itemtype => itemtype,
2180 itemkey => itemkey,
2181 aname => 'ERROR_MESSAGE',
2182 avalue => l_error);
2183
2184 else
2185 result := wf_engine.eng_completed ||':'||wf_yes;
2186
2187 end if;
2188 --IF PG_DEBUG < 10 THEN
2189 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2190 iex_debug_pub.logmessage ('create_next_work_item: ' || ' RESULT IS => '||result);
2191 END IF;
2192 --IF PG_DEBUG < 10 THEN
2193 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2194 iex_debug_pub.logmessage ('**** END create_next_work_item ************');
2195 END IF;
2196 exception
2197 when others then
2198 wf_core.context('IEX_STRATEGY_WF',' create_next_work_item',itemtype,
2199 itemkey,to_char(actid),funcmode);
2200 raise;
2201
2202 end create_next_work_item;
2203
2204 ----------- procedure wait_signal ------------------------------
2205 procedure wait_signal(
2206 itemtype in varchar2,
2207 itemkey in varchar2,
2208 actid in number,
2209 funcmode in varchar2,
2210 result out NOCOPY varchar2) IS
2211
2212 l_value VARCHAR2(300);
2213 Begin
2214 -- IF PG_DEBUG < 10 THEN
2215 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2216 iex_debug_pub.logmessage ('**** BEGIN WAIT_SIGNAL ************');
2217 END IF;
2218 if funcmode <> 'RUN' then
2219 result := wf_engine.eng_null;
2220 return;
2221 end if;
2222
2223 /*l_value :=wf_engine.GetActivityLabel(actid);
2224 wf_engine.SetItemAttrText(itemtype => itemtype,
2225 itemkey => itemkey,
2226 aname => 'ACTIVITY_NAME',
2227 avalue => l_value);
2228 --IF PG_DEBUG < 10 THEN
2229 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2230 iex_debug_pub.logMessage('wait_signal: ' || 'ACTIVITYNAME' ||l_value);
2231 END IF;
2232 */
2233
2234 --- suspend process
2235 wf_engine.SuspendProcess(
2236 itemtype => itemtype,
2237 itemkey => itemkey);
2238 -- 05/10/02 the form doesnot know the workflow is
2239 -- suspended, so trying to issue a commit to see
2240 --if it works.
2241 --COMMIT;
2242 result := wf_engine.eng_completed ||':'||wf_yes;
2243 -- IF PG_DEBUG < 10 THEN
2244 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2245 iex_debug_pub.logmessage ('**** END WAIT_SIGNAL ************');
2246 END IF;
2247 exception
2248 when others then
2249 wf_core.context('IEX_STRATEGY_WF','wait_signal',itemtype,
2250 itemkey,to_char(actid),funcmode);
2251 raise;
2252
2253 end wait_signal;
2254
2255
2256 /**
2257 * check whether the work item has post execution wait
2258 **/
2259 procedure wait_complete_signal(
2260 itemtype in varchar2,
2261 itemkey in varchar2,
2262 actid in number,
2263 funcmode in varchar2,
2264 result out nocopy varchar2) IS
2265
2266 l_work_item_temp_id NUMBER;
2267 l_result VARCHAR2(1);
2268 l_value VARCHAR2(300);
2269
2270 BEGIN
2271 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2272 iex_debug_pub.logmessage ('**** START wait_complete_signal ************');
2273 END IF;
2274 if funcmode <> wf_engine.eng_run then
2275 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2276 iex_debug_pub.logmessage('SECOND TIME FUNCMODE' ||funcmode);
2277 END IF;
2278 result := wf_engine.eng_null;
2279 return;
2280 end if;
2281
2282
2283
2284 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2285 iex_debug_pub.logmessage('FUNCMODE' ||funcmode);
2286 END IF;
2287 l_value :=wf_engine.GetActivityLabel(actid);
2288 wf_engine.SetItemAttrText(itemtype => itemtype,
2289 itemkey => itemkey,
2290 aname => 'ACTIVITY_NAME',
2291 avalue => l_value);
2292 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2293 iex_debug_pub.logMessage('ACTIVITYNAME' ||l_value);
2294 END IF;
2295
2296
2297 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
2298 ':'||wf_engine.eng_null;
2299 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2300 iex_debug_pub.logmessage ('**** END wait_complete_signal ************');
2301 END IF;
2302 exception
2303 when others then
2304 result := wf_engine.eng_completed ||':'||wf_no;
2305 wf_core.context('IEX_STRATEGY_WF','wait_complete_signal',itemtype,
2306 itemkey,to_char(actid),funcmode);
2307 raise;
2308
2309 END wait_complete_signal;
2310
2311
2312
2313
2314 /**
2315 * This will be called from the form or the concurrent program
2316 * once the work item
2317 * status is changed or if the strategy is closed or cancelled
2318 * if the work item is null ,then the strategy and the pending
2319 * work items are to be closed/cancelled
2320 * else just complete or cancel the work item only
2321 *03/19/02
2322 * --sub process addition --
2323 * if it is waiting for optional check or escalate check
2324 * then we have not reached the wait for response activity
2325 * or the process is not suspended so these are the things we should do
2326 * 1.DO NOT resume process
2327 * 2.Complete activity (depending on the activity label - this will be
2328 * either escalate_check or optional_check)
2329 * then the subprocess will be completed
2330 *04/02/2002
2331 * add a new parameter signal_source for custom work flow and fulfilment work flow
2332 * check if the agent has already changed the work item status before the completion
2333 *of custom or fulfillment wf's.then do nothing ,else update workitem and resume process
2334 * If the strategy is ON-HOLD' or 'OPEN' THEN do not resume process, the agent will update the
2335 *work item and update the strategy to 'OPEN' .
2336 *04/08/02
2337 * abort fulfilment and custom workflow when agent updates fulfilment or custom workitem
2338 *04/16/02
2339 *if the strategy status is 'OPEN' THEN resume process and it is ON-HOLD then do not resume,
2340 * remain suspended
2341 04/18/02
2342 -- if the strategy is changed from on hold to OPEN and if the current work item has
2343 -- aleady completed ( changed the status from 'OPEN to CCANCELLED, CLOSED, TIMEOUT),
2344 -- then resume the process, othere wise remain suspended.
2345 **/
2346 --04/26/02 check if activity label is null before resuming process
2347 -- agent is not going to do update, so send signal will do it.
2348 --07/31/02 --abort the strategy workflow if the workflow is active or
2349 --in error
2350 procedure send_signal(
2351 process in varchar2 ,
2352 strategy_id in varchar2,
2353 status in VARCHAR2,
2354 work_item_id in number ,
2355 signal_source in varchar2 ) IS
2356 l_result VARCHAR2(100);
2357 l_Status VARCHAR2(8);
2358 l_activity_label VARCHAR2(100);
2359
2360 Begin
2361 -- IF PG_DEBUG < 10 THEN
2362 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2363 iex_debug_pub.logmessage ('**** START WAIT_SIGNAL ************');
2364 END IF;
2365 -- IF PG_DEBUG < 10 THEN
2366 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2367 iex_debug_pub.logmessage ('wait_signal: ' || 'status' ||status ||'workitemid ' ||work_item_id || '**'||
2368 'signal source '||signal_source||'strategy_id' ||strategy_id);
2369 END IF;
2370
2371 wf_engine.itemstatus(itemtype => process,
2372 itemkey => strategy_id,
2373 status =>l_status,
2374 result =>l_result);
2375
2376 -- IF PG_DEBUG < 10 THEN
2377 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2378 iex_debug_pub.logmessage('wait_signal: ' || 'status ofthe process '|| l_status);
2379 END IF;
2380
2381 --- resume process only if it is suspend
2382 -- check for other than fulfilment and custom work flow
2383
2384 IF l_status =wf_engine.eng_suspended THEN
2385
2386 --check if the signal_source is fulfillment or optional
2387 if signal_source in ('FULFILLMENT','CUSTOM') THEN
2388 -- IF PG_DEBUG < 10 THEN
2389 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2390 iex_debug_pub.logmessage('wait_signal: ' || 'signal source '|| signal_source);
2391 END IF;
2392 -- the agent has not updated the current work item
2393 l_result := CHECK_CURRENT_WORK_ITEM(strategy_id,work_item_id);
2394 IF l_result >0 THEN
2395 -- IF PG_DEBUG < 10 THEN
2396 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2397 iex_debug_pub.logmessage('wait_signal: ' || 'agent has not updated the work item');
2398 END IF;
2399 -- do not call update since custom or fulfilment is going to update it
2400 --update_workitem_Status(work_item_id,status );
2401 else
2402 --agent has updated and we are on the next work item or the strategy is
2403 --closed or complete, or ON HOLD
2404 -- IF PG_DEBUG < 10 THEN
2405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2406 iex_debug_pub.logmessage('wait_signal: ' || 'agent has updated the work item');
2407 END IF;
2408 return;
2409 END IF;
2410
2411 else -- signal source is null
2412 -- IF PG_DEBUG < 10 THEN
2413 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2414 iex_debug_pub.logmessage('wait_signal: ' || 'signal source is null');
2415 END IF;
2416 if work_item_id is NULL THEN
2417 -- the status could be ON-HOLD or 'OPEN' (from promises API)
2418 If status IN ('CLOSED','CANCELLED') THEN
2419 -- strategy is being closed or cancelled
2420 -- complete fulfilment and custom has to be aborted
2421 -- IF PG_DEBUG < 10 THEN
2422 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2423 iex_debug_pub.logmessage('wait_signal: ' || 'work item is null');
2424 END IF;
2425 --abort custom or fulfillment wfs' if they are not completed
2426 abort_processes(strategy_id);
2427 elsif status ='ONHOLD' THEN
2428 --continue being suspended
2429 -- IF PG_DEBUG < 10 THEN
2430 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2431 iex_debug_pub.logmessage('wait_signal: ' || 'work item is null and status is '||status);
2432 END IF;
2433 return;
2434 else --status is OPEN
2435 --if it is open resume process only if the existing work item has been
2436 -- completed, otherwise remain suspended.
2437 l_result := CHECK_WORK_ITEM_OPEN(strategy_id);
2438 IF l_result >0 THEN
2439 --remain suspended, do nothing
2440 -- IF PG_DEBUG < 10 THEN
2441 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2442 iex_debug_pub.logmessage('wait_signal: ' || 'work item is still open ');
2443 END IF;
2444 return;
2445 else
2446 --work item has completed, resume process
2447 -- IF PG_DEBUG < 10 THEN
2448 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2449 iex_debug_pub.logmessage('wait_signal: ' || 'work item has been completed');
2450 END IF;
2451 END IF;
2452 end if; -- status check
2453 else
2454 --04/08/02 abort the process if it is custom or fulfilment
2455 -- maybe the agent can indicate if it is custom or fulfilment
2456 --work item . this portion is not there .so call abort on the
2457 -- strategy_id.
2458 -- 04/26/02 -- update work item
2459 --update_workitem_Status(work_item_id,status );
2460 abort_processes(strategy_id);
2461 end if;--work item id is null
2462 end if; -- signal source check
2463
2464 wf_engine.SetItemAttrText(itemtype => process,
2465 itemkey => strategy_id,
2466 aname => 'STRATEGY_STATUS',
2467 avalue => status);
2468
2469 wf_engine.SetItemAttrNumber(itemtype => process,
2470 itemkey => strategy_id,
2471 aname => 'WORK_ITEMID',
2472 avalue => work_item_id);
2473
2474 wf_engine.ResumeProcess(itemtype => process,
2475 itemkey => strategy_id);
2476
2477 --COMMIT; -- the work flow was not going to end from the close strategy
2478
2479 ELSIF l_status =wf_engine.eng_active THEN
2480
2481 --complete the current activity
2482 --this will be either complete the ESCALATE_CHECK
2483 --OR OPTIONAL_CHECK activity
2484 --get the label first. ( the name of the activity is set in
2485 --the attribute)
2486 -- the fulfilment or custom work flow might send the signal later
2487 -- do not do anything if the process is active.
2488
2489 if signal_source in ('FULFILLMENT','CUSTOM') THEN
2490 return;
2491 else -- signal source is null
2492 -- IF PG_DEBUG < 10 THEN
2493 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2494 iex_debug_pub.logmessage('wait_signal: ' || 'signal source is null');
2495 END IF;
2496 if work_item_id is NULL THEN
2497 -- the status could be ON-HOLD or 'OPEN' (from promises API)
2498 If status IN ('CLOSED','CANCELLED') THEN
2499 -- strategy is being closed or cancelled
2500 -- complete fulfilment and custom has to be aborted
2501 -- IF PG_DEBUG < 10 THEN
2502 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2503 iex_debug_pub.logmessage('wait_signal: ' || 'work item is null');
2504 END IF;
2505 else
2506 -- IF PG_DEBUG < 10 THEN
2507 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2508 iex_debug_pub.logmessage('wait_signal: ' || 'work item is null and status is '||status);
2509 END IF;
2510 return;
2511 end if; -- status check
2512 else
2513 --04/08/02 abort the process if it is custom or fulfilment
2514 -- maybe the agent can indicate if it is custom or fulfilment
2515 --work item . this portion is not there .so call abort on the
2516 -- strategy_id.
2517 -- IF PG_DEBUG < 10 THEN
2518 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2519 iex_debug_pub.logmessage('wait_signal: ' || 'work item is not null ');
2520 END IF;
2521 --05/10 if sendsiganl fails we don't want to abort process
2522 --so abort shoud be part of complete activity and send signal want complete
2523 --activity without the label being set.
2524
2525 end if;--work item id is null
2526
2527 l_activity_label := wf_engine.GetItemAttrText(
2528 itemtype => process,
2529 itemkey => strategy_id,
2530 aname => 'ACTIVITY_NAME');
2531 -- IF PG_DEBUG < 10 THEN
2532 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2533 iex_debug_pub.logmessage('wait_signal: ' || 'BEFORE CALLING COMPLETE ACTIVITY');
2534 END IF;
2535 -- IF PG_DEBUG < 10 THEN
2536 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2537 iex_debug_pub.logmessage('wait_signal: ' || process ||strategy_id||l_activity_label);
2538 END IF;
2539
2540 wf_engine.SetItemAttrText(itemtype => process,
2541 itemkey => strategy_id,
2542 aname => 'STRATEGY_STATUS',
2543 avalue => status);
2544
2545 wf_engine.SetItemAttrNumber(itemtype => process,
2546 itemkey => strategy_id,
2547 aname => 'WORK_ITEMID',
2548 avalue => work_item_id);
2549
2550 -- 04/26/02
2551 -- change strategy from the UI, then send signal was failing
2552 --check value of activity label before resuming process
2553 If l_activity_label IS NOT NULL THEN
2554 abort_processes(strategy_id); -- added on 05/10/02
2555 wf_engine.CompleteActivity(
2556 itemtype => process,
2557 itemkey => strategy_id,
2558 activity =>l_activity_label,
2559 result =>'Yes');
2560 ELSE
2561 --07/31/02
2562 --abort the strategy workflow
2563 --update the work items,
2564 --close or cancel strategy
2565 --abort all the custom workflows
2566 --this will happen if the workflow failed before
2567 --being suspended, for example if notification
2568 -- does not have a performer.
2569 -- Bug 7703319 by Ehuh
2570 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2571 iex_debug_pub.logmessage('l_activity is NULL ..... ');
2572 END IF;
2573 CLOSE_AND_ABORT_STRATEGY_WF(strategy_id,status);
2574 --return;
2575 end if;
2576
2577 end if; --signal_source
2578
2579 ELSIF l_status =wf_engine.eng_error THEN
2580 CLOSE_AND_ABORT_STRATEGY_WF(strategy_id,status);
2581 NULL;
2582 --COMMIT; -- the work flow was not going to end from the close strategy
2583 END IF; -- item status
2584 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2585 iex_debug_pub.logmessage ('**** END WAIT_SIGNAL ************');
2586 END IF;
2587
2588 exception
2589 when others then
2590 wf_core.context('IEX_STRATEGY_WF','send_signal',process,
2591 strategy_id,to_char(111),'RUN');
2592 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2593 iex_debug_pub.logmessage ('Error in SEND_SIGNAL -->' ||sqlerrm);
2594 END IF;
2595 raise;
2596
2597 end send_signal;
2598
2599 /**
2600 * check whether there is a custom worlflow attached
2601 **/
2602 procedure CUSTOM_CHECK(
2603 itemtype in varchar2,
2604 itemkey in varchar2,
2605 actid in number,
2606 funcmode in varchar2,
2607 result out NOCOPY varchar2) IS
2608 l_work_item_temp_id NUMBER;
2609 l_result VARCHAR2(1);
2610 l_value VARCHAR2(300);
2611 l_custom_wf_rec IEX_STRY_CUWF_PUB.custom_wf_Rec_Type;
2612 l_return_status VARCHAR2(1) ;
2613 l_msg_count NUMBER;
2614 l_msg_data VARCHAR2(32767);
2615 l_custom_itemtype VARCHAR2 (100);
2616 --Added for bug#4506922
2617 L_STATUS_CODE VARCHAR2(25);
2618 l_work_item_id NUMBER(25);
2619
2620 CURSOR c_user_item_workflow(p_work_item_id NUMBER) IS
2621 SELECT a.custom_workflow_type
2622 FROM iex_strategy_user_items a,
2623 iex_strategy_work_items b
2624 WHERE a.strategy_id = b.strategy_id
2625 AND a.work_item_order = b.work_item_order
2626 AND b.work_item_id = p_work_item_id;
2627
2628 l_user_item_workflow VARCHAR2(240);
2629
2630 BEGIN
2631 -- initialize variable
2632 l_return_status :=FND_API.G_RET_STS_SUCCESS;
2633
2634 -- IF PG_DEBUG < 10 THEN
2635 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2636 iex_debug_pub.logmessage ('**** START CUSTOM_CHECK ************');
2637 END IF;
2638 if funcmode <> 'RUN' then
2639 result := wf_engine.eng_null;
2640 return;
2641 end if;
2642
2643 /* Begin 06-dec-2005 schekuri bug 4506922 - All work items are created as PRE-WAIT */
2644 l_work_item_id := wf_engine.GetItemAttrNumber(
2645 itemtype => itemtype,
2646 itemkey => itemkey,
2647 aname => 'WORK_ITEMID');
2648
2649 iex_debug_pub.logmessage('l_work_item_id ' || l_work_item_id);
2650
2651 SELECT STATUS_CODE INTO l_status_code FROM IEX_STRATEGY_WORK_ITEMS WHERE WORK_ITEM_ID = l_work_item_id;
2652
2653 if (l_status_code = 'PRE-WAIT') THEN
2654 BEGIN
2655
2656 UPDATE IEX_STRATEGY_WORK_ITEMS SET STATUS_CODE = 'OPEN' WHERE WORK_ITEM_ID = l_work_item_id;
2657 --Begin bug#5874874 gnramasa 25-Apr-2007
2658 --Update the UWQ summary table after workitem's status changes to OPEN from PRE-WAIT.
2659 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_work_item_id);
2660 --End bug#5874874 gnramasa 25-Apr-2007
2661 commit work;
2662 END;
2663 END IF;
2664
2665 /* End 06-dec-2005 schekuri bug 4506922 - All work items are created as PRE-WAIT */
2666
2667 /* l_value :=wf_engine.GetActivityLabel(actid);
2668 wf_engine.SetItemAttrText(itemtype => itemtype,
2669 itemkey => itemkey,
2670 aname => 'ACTIVITY_NAME',
2671 avalue => l_value);
2672
2673 -- IF PG_DEBUG < 10 THEN
2674 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2675 iex_debug_pub.logMessage('CUSTOM_CHECK: ' || 'ACTIVITYNAME' ||l_value);
2676 END IF;
2677 */
2678 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
2679 itemtype => itemtype,
2680 itemkey => itemkey,
2681 aname => 'WORKITEM_TEMPLATE_ID');
2682 iex_debug_pub.logmessage('l_work_item_temp_id '|| l_work_item_temp_id);
2683
2684 select decode(workflow_item_type,null,'N','Y'),
2685 workflow_item_type INTO l_result,l_custom_itemtype
2686 from iex_stry_temp_work_items_vl
2687 where work_item_temp_id =l_work_item_temp_id;
2688
2689 iex_debug_pub.logmessage('l_result '||l_result);
2690 iex_debug_pub.logmessage('l_custom_itemtype '|| l_custom_itemtype);
2691
2692 OPEN c_user_item_workflow(l_work_item_id);
2693 FETCH c_user_item_workflow INTO l_user_item_workflow;
2694 CLOSE c_user_item_workflow;
2695
2696 IF l_user_item_workflow IS NOT NULL THEN
2697 l_custom_itemtype := l_user_item_workflow;
2698 END IF;
2699
2700 iex_debug_pub.logmessage(' custom workflow type '|| l_custom_itemtype);
2701
2702 --start custom work flow process
2703 If l_result = wf_yes THEN
2704 populate_custom_workflow (itemtype,
2705 itemkey,
2706 l_custom_itemtype,
2707 l_custom_wf_rec);
2708
2709 -- IF PG_DEBUG < 10 THEN
2710 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2711 iex_debug_pub.logmessage('CUSTOM_CHECK: ' || 'before calling custom workflow');
2712 END IF;
2713
2714 IEX_STRY_cuwf_pub.Start_CustomWF
2715 (
2716 p_api_version =>1.0,
2717 p_init_msg_list => FND_API.G_TRUE,
2718 p_commit => FND_API.G_FALSE,
2719 p_custom_wf_rec =>l_custom_wf_rec,
2720 x_msg_count => l_msg_count,
2721 x_msg_data => l_msg_data,
2722 x_return_status => l_return_status);
2723 End if;
2724
2725 -- IF PG_DEBUG < 10 THEN
2726 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2727 iex_debug_pub.logmessage ('CUSTOM_CHECK: ' || 'End of custom work flow ');
2728 END IF;
2729 result := wf_engine.eng_completed ||':'||l_result;
2730 return;
2731 -- result := wf_engine.eng_completed ||':'||l_result;
2732
2733 -- don't check for result, what ever is the outcome
2734 -- go and wait, the UI will relaunch the custom workflow
2735
2736 /* if l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2737 result := wf_engine.eng_completed ||':'||wf_yes;
2738 else
2739 result := wf_engine.eng_completed ||':'||wf_no;
2740 end if;
2741
2742 */
2743
2744
2745 -- IF PG_DEBUG < 10 THEN
2746 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2747 iex_debug_pub.logmessage ('**** END CUSTOM_CHECK ************');
2748 END IF;
2749 exception
2750 when others then
2751 result := wf_engine.eng_completed ||':'||wf_no;
2752 wf_core.context('IEX_STRATEGY_WF','CUSTOM_CHECK',itemtype,
2753 itemkey,to_char(actid),funcmode);
2754 raise;
2755
2756 END CUSTOM_CHECK;
2757
2758 /**
2759 * check whether the fulfil_temp_id is populated for this work item
2760 *
2761 **/
2762 procedure FULFIL_CHECK(
2763 itemtype in varchar2,
2764 itemkey in varchar2,
2765 actid in number,
2766 funcmode in varchar2,
2767 result out NOCOPY varchar2)IS
2768
2769 l_work_item_temp_id NUMBER;
2770 l_result VARCHAR2(1);
2771 l_fulfil_temp_id NUMBER;
2772 l_value VARCHAr2(300);
2773 l_strategy_mailer_rec iex_strategy_work_pub.STRATEGY_Mailer_Rec_Type;
2774 l_return_status VARCHAR2(1) ;
2775 l_msg_count NUMBER;
2776 l_msg_data VARCHAR2(32767);
2777 l_curr_dmethod varchar2(10);
2778 l_work_item_id number;
2779 l_temp_id number;
2780 l_work_type VARCHAR2(100);
2781
2782 CURSOR c_witem_details(p_work_item_id number) IS
2783 SELECT xdo_temp_id
2784 FROM iex_strategy_work_items
2785 WHERE work_item_id = p_work_item_id;
2786
2787 BEGIN
2788 -- initialize variable
2789 l_return_status :=FND_API.G_RET_STS_SUCCESS;
2790
2791 -- IF PG_DEBUG < 10 THEN
2792 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2793 iex_debug_pub.logmessage ('**** START FULFIL_CHECK ************');
2794 END IF;
2795 if funcmode <> 'RUN' then
2796 result := wf_engine.eng_null;
2797 return;
2798 end if;
2799
2800 /* l_value :=wf_engine.GetActivityLabel(actid);
2801 wf_engine.SetItemAttrText(itemtype => itemtype,
2802 itemkey => itemkey,
2803 aname => 'ACTIVITY_NAME',
2804 avalue => l_value);
2805 -- IF PG_DEBUG < 10 THEN
2806 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2807 iex_debug_pub.logMessage('FULFIL_CHECK: ' || 'ACTIVITYNAME' ||l_value);
2808 END IF;
2809 */
2810
2811 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
2812 itemtype => itemtype,
2813 itemkey => itemkey,
2814 aname => 'WORKITEM_TEMPLATE_ID');
2815
2816 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
2817
2818 l_work_item_id := wf_engine.GetItemAttrNumber(
2819 itemtype => itemtype,
2820 itemkey => itemkey,
2821 aname => 'WORK_ITEMID');
2822
2823 if (l_curr_dmethod = 'FFM') then
2824 select decode(fulfil_temp_id,null,'N',wf_yes),fulfil_temp_id,work_type
2825 INTO l_result,l_fulfil_temp_id,l_work_type
2826 from iex_stry_temp_work_items_vl
2827 where work_item_temp_id =l_work_item_temp_id;
2828 else
2829 select decode(xdo_template_id,null,'N',wf_yes),xdo_template_id,work_type
2830 INTO l_result,l_fulfil_temp_id,l_work_type
2831 from iex_stry_temp_work_items_vl
2832 where work_item_temp_id =l_work_item_temp_id;
2833 end if;
2834
2835
2836 -- bug 10133319 override xdo_temp_id if exists
2837
2838 OPEN c_witem_details (l_work_item_id);
2839 FETCH c_witem_details INTO l_temp_id;
2840 CLOSE c_witem_details;
2841
2842 IF l_temp_id IS NOT NULL then
2843 l_fulfil_temp_id := l_temp_id;
2844 END IF;
2845 -- end for bug 10133319
2846
2847 --start fulfilment process
2848 If l_result = wf_yes AND l_work_type='AUTOMATIC' THEN
2849 populate_strategy_mailer (itemtype,itemkey,l_strategy_mailer_rec);
2850 --fulfilment ID -- xdo_template_id
2851
2852 if (l_curr_dmethod = 'FFM') then
2853 l_strategy_mailer_rec.template_id := l_fulfil_temp_id;
2854 else
2855 l_strategy_mailer_rec.xdo_template_id := l_fulfil_temp_id;
2856 end if;
2857 -- IF PG_DEBUG < 10 THEN
2858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2859 iex_debug_pub.logmessage('FULFIL_CHECK: ' || 'fulful template id' ||l_fulfil_temp_id);
2860 END IF;
2861 iex_strategy_work_pub.strategy_mailer(
2862 p_api_version =>1.0,
2863 p_init_msg_list => FND_API.G_FALSE,
2864 p_commit => FND_API.G_TRUE,
2865 p_strategy_mailer_rec => l_strategy_mailer_rec,
2866 x_msg_count => l_msg_count,
2867 x_msg_data => l_msg_data,
2868 x_return_status => l_return_status);
2869
2870 End if;
2871
2872 result := wf_engine.eng_completed ||':'||l_result;
2873
2874 -- don't check for result, what ever is the outcome
2875 -- go and wait, the UI will relaunch the fulilment workflow
2876
2877 /* if l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2878 result := wf_engine.eng_completed ||':'||wf_yes;
2879 else
2880 result := wf_engine.eng_completed ||':'||wf_no;
2881 end if;
2882
2883 */
2884
2885
2886 -- IF PG_DEBUG < 10 THEN
2887 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2888 iex_debug_pub.logmessage ('**** END FULFIL_CHECK ************');
2889 END IF;
2890 exception
2891 when others then
2892 result := wf_engine.eng_completed ||':'||wf_no;
2893 wf_core.context('IEX_STRATEGY_WF','FULFIL_CHECK',itemtype,
2894 itemkey,to_char(actid),funcmode);
2895 raise;
2896
2897 END FULFIL_CHECK;
2898
2899
2900 /* calculate the post execution wait
2901 *
2902 **/
2903 procedure cal_post_wait(
2904 itemtype in varchar2,
2905 itemkey in varchar2,
2906 actid in number,
2907 funcmode in varchar2,
2908 result out nocopy varchar2)IS
2909
2910 l_work_item_temp_id NUMBER;
2911 l_work_item_id NUMBER ;
2912
2913 l_result VARCHAR2(1);
2914 l_fulfil_temp_id NUMBER;
2915 l_value VARCHAr2(300);
2916 l_strategy_mailer_rec iex_strategy_work_pub.STRATEGY_Mailer_Rec_Type;
2917 l_return_status VARCHAR2(1) ;
2918 l_msg_count NUMBER;
2919 l_msg_data VARCHAR2(32767);
2920 l_execution_time date;
2921 l_post_execution_wait IEX_STRY_TEMP_WORK_ITEMS_VL.post_execution_wait%type := 0.0;
2922 l_execution_time_uom IEX_STRY_TEMP_WORK_ITEMS_VL.execution_time_uom%type;
2923 l_conversion mtl_uom_conversions.conversion_rate%type := 0.0;
2924 l_return VARCHAR2(1);
2925 l_strategy_status varchar2(100);
2926 l_curr_dmethod varchar2(10);
2927
2928 CURSOR c_witem_details(p_work_item_id number) IS
2929 SELECT post_execution_wait,execution_time_uom
2930 FROM iex_strategy_work_items
2931 WHERE work_item_id = p_work_item_id;
2932
2933 BEGIN
2934 -- initialize variable
2935 l_return_status :=FND_API.G_RET_STS_SUCCESS;
2936
2937 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2938 iex_debug_pub.logmessage ('**** START cal_post_wait ************');
2939 END IF;
2940 if funcmode <> 'RUN' then
2941 result := wf_engine.eng_null;
2942 return;
2943 end if;
2944
2945 l_strategy_status := wf_engine.GetItemAttrText(itemtype => itemtype,
2946 itemkey => itemkey,
2947 aname => 'STRATEGY_STATUS');
2948
2949 l_work_item_id := wf_engine.GetItemAttrNumber(
2950 itemtype => itemtype,
2951 itemkey => itemkey,
2952 aname => 'WORK_ITEMID');
2953
2954 if l_strategy_status = 'CANCELLED' then
2955 --Begin bug#5874874 gnramasa 25-Apr-2007
2956 --Update the UWQ summary table after CANCELLING the strategy.
2957 if l_work_item_id is not null then
2958 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_work_item_id);
2959 end if;
2960 --End bug#5874874 gnramasa 25-Apr-2007
2961 l_return := wf_no;
2962 result := wf_engine.eng_completed ||':'||l_return;
2963 return;
2964 end if;
2965
2966 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
2967 itemtype => itemtype,
2968 itemkey => itemkey,
2969 aname => 'WORKITEM_TEMPLATE_ID');
2970
2971 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2972 iex_debug_pub.logMessage('cal_post_wait workitemtempid = ' ||l_work_item_temp_id);
2973 END IF;
2974
2975 --Begin bug#5502077 schekuri 30-Apr-2007
2976 --If the Fulfillment workitem has post execution wait time, it should wait after completion
2977 --of workitem in the main workflow only. It shouldn't wait in the fulfillment workflow.
2978 -- xdo check
2979 /*begin
2980 l_curr_dmethod := iex_send_xml_pvt.getCurrDeliveryMethod();
2981 if (l_curr_dmethod = 'FFM') then
2982 select decode(fulfil_temp_id,null,'N',wf_yes),fulfil_temp_id
2983 INTO l_result,l_fulfil_temp_id
2984 from iex_stry_temp_work_items_vl
2985 where work_item_temp_id =l_work_item_temp_id;
2986 else
2987 select decode(xdo_template_id,null,'N',wf_yes),xdo_template_id
2988 INTO l_result,l_fulfil_temp_id
2989 from iex_stry_temp_work_items_vl
2990 where work_item_temp_id =l_work_item_temp_id;
2991 end if;
2992 exception
2993 when others then
2994 l_result := wf_no;
2995 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
2996 iex_debug_pub.logMessage('cal_post_wait GET FULFILLMENT ERROR ');
2997 END IF;
2998 end;
2999
3000 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3001 iex_debug_pub.logMessage('cal_post_wait l_result = ' ||l_result);
3002 END IF;
3003
3004 --start fulfilment process
3005 If l_result = wf_yes THEN
3006 select sysdate into l_execution_time from dual;
3007 l_return := wf_no;
3008 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3009 iex_debug_pub.logMessage('cal_post_wait l_execution_time = ' ||l_execution_time);
3010 END IF;
3011 else*/
3012 --End bug#5502077 schekuri 30-Apr-2007
3013 begin
3014 -- bug 1013319 post wait override
3015 OPEN c_witem_details(l_work_item_id);
3016 FETCH c_witem_details INTO l_post_execution_wait,l_execution_time_uom;
3017 CLOSE c_witem_details;
3018
3019 IF l_post_execution_wait IS NULL THEN
3020 select a.post_execution_wait, a.execution_time_uom
3021 into l_post_execution_wait, l_execution_time_uom
3022 from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
3023 where b.work_item_template_id = a.work_item_temp_id
3024 and b.work_item_id = l_work_item_id;
3025 END IF;
3026 -- end for bug 10133319
3027 exception
3028 when others then
3029 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3030 iex_debug_pub.logMessage('cal_post_wait get execution time error ');
3031 END IF;
3032 end;
3033
3034 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3035 iex_debug_pub.logMessage('fulfil_check post_execution_wait = ' ||l_post_execution_wait);
3036 iex_debug_pub.logMessage('fulfil_check execution_time_uom = ' ||l_execution_time_uom);
3037 END IF;
3038 if (l_post_execution_wait = 0) then
3039 l_return := wf_no;
3040 else
3041 begin
3042 l_execution_time:= IEX_STRY_UTL_PUB.get_Date
3043 (p_date =>sysdate,
3044 l_UOM =>l_execution_time_uom,
3045 l_UNIT =>l_post_execution_wait);
3046 exception
3047 when others then
3048 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3049 iex_debug_pub.logMessage('cal_post_wait convert date error ');
3050 END IF;
3051 end;
3052 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3053 iex_debug_pub.logMessage('fulfil_check l_execution_time = ' ||l_execution_time);
3054 END IF;
3055
3056 l_return := wf_yes;
3057 end if;
3058
3059 --End if; --Removed if for bug#5502077 schekuri 30-Apr-2007
3060
3061 wf_engine.SetItemAttrDate(itemtype => itemtype,
3062 itemkey => itemkey,
3063 aname => 'EXECUTION_TIME',
3064 avalue => l_execution_time);
3065
3066 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3067 iex_debug_pub.logMessage('cal_post_wait result = ' ||l_return);
3068 END IF;
3069
3070 --Begin bug#5874874 gnramasa 25-Apr-2007
3071 --Update the UWQ summary table after completing the workitem.
3072 if l_work_item_id is not null then
3073 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_work_item_id);
3074 end if;
3075 --End bug#5874874 gnramasa 25-Apr-2007
3076
3077 result := wf_engine.eng_completed ||':'||l_return;
3078
3079 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3080 iex_debug_pub.logmessage ('**** END cal_post_wait ************');
3081 END IF;
3082 exception
3083 when others then
3084 result := wf_engine.eng_completed ||':'||wf_no;
3085 wf_core.context('IEX_STRATEGY_WF','cal_post_wait',itemtype,
3086 itemkey,to_char(actid),funcmode);
3087 raise;
3088
3089
3090
3091 END cal_post_wait;
3092
3093
3094 /* calculate the pre execution wait
3095 *
3096 **/
3097 procedure cal_pre_wait(
3098 itemtype in varchar2,
3099 itemkey in varchar2,
3100 actid in number,
3101 funcmode in varchar2,
3102 result out nocopy varchar2)IS
3103
3104 l_work_item_temp_id NUMBER;
3105 l_work_item_id NUMBER ;
3106
3107 l_schedule date;
3108 l_pre_execution_wait IEX_STRY_TEMP_WORK_ITEMS_VL.pre_execution_wait%type := 0.0;
3109 l_schedule_uom IEX_STRY_TEMP_WORK_ITEMS_VL.schedule_uom%type;
3110 l_return VARCHAR2(1);
3111 l_strategy_status varchar2(100);
3112
3113 CURSOR c_witem_details(p_work_item_id number) IS
3114 SELECT pre_execution_wait,pre_execution_time_uom
3115 FROM iex_strategy_work_items
3116 WHERE work_item_id = p_work_item_id;
3117
3118 BEGIN
3119 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3120 iex_debug_pub.logmessage ('**** START cal_pre_wait ************');
3121 END IF;
3122 if funcmode <> 'RUN' then
3123 result := wf_engine.eng_null;
3124 return;
3125 end if;
3126
3127 l_strategy_status := wf_engine.GetItemAttrText(itemtype => itemtype,
3128 itemkey => itemkey,
3129 aname => 'STRATEGY_STATUS');
3130 if l_strategy_status = 'CANCELLED' then
3131 l_return := wf_no;
3132 result := wf_engine.eng_completed ||':'||l_return;
3133 return;
3134 end if;
3135
3136 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
3137 itemtype => itemtype,
3138 itemkey => itemkey,
3139 aname => 'WORKITEM_TEMPLATE_ID');
3140
3141 l_work_item_id := wf_engine.GetItemAttrNumber(
3142 itemtype => itemtype,
3143 itemkey => itemkey,
3144 aname => 'WORK_ITEMID');
3145
3146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3147 iex_debug_pub.logMessage('cal_pre_wait workitemtempid = ' ||l_work_item_temp_id);
3148 END IF;
3149
3150 BEGIN
3151 -- start bug 10133319 pre wait override
3152 OPEN c_witem_details(l_work_item_id);
3153 FETCH c_witem_details INTO l_pre_execution_wait , l_schedule_uom;
3154 CLOSE c_witem_details;
3155
3156 IF l_pre_execution_wait IS NULL then
3157 select a.pre_execution_wait, a.schedule_uom
3158 into l_pre_execution_wait, l_schedule_uom
3159 from IEX_STRY_TEMP_WORK_ITEMS_VL a, IEX_STRATEGY_WORK_ITEMS b
3160 where b.work_item_template_id = a.work_item_temp_id
3161 and b.work_item_id = l_work_item_id;
3162 END IF;
3163 -- end bug 10133319
3164 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3165 iex_debug_pub.logMessage('Collections cal_pre_wait pre_execution_wait = ' ||l_pre_execution_wait);
3166 iex_debug_pub.logMessage('Collections cal_pre_wait schedule_uom = ' ||l_schedule_uom);
3167 END IF;
3168 exception
3169 when others then
3170 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3171 iex_debug_pub.logMessage('cal_pre_wait get execution time error ');
3172 END IF;
3173 end;
3174
3175 if (l_pre_execution_wait = 0) then
3176 l_return := wf_no;
3177 else
3178 begin
3179 l_schedule:= IEX_STRY_UTL_PUB.get_Date
3180 (p_date =>sysdate,
3181 l_UOM =>l_schedule_uom,
3182 l_UNIT =>l_pre_execution_wait);
3183 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3184 iex_debug_pub.logMessage('Collections cal_pre_wait l_schedule = ' || to_char(l_schedule, 'hh24:mi:ss mm/dd/yyyy'));
3185 END IF;
3186 exception
3187 when others then
3188 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3189 iex_debug_pub.logMessage('cal_pre_wait convert date error ');
3190 END IF;
3191 end;
3192
3193 l_return := wf_yes;
3194 end if;
3195
3196
3197 wf_engine.SetItemAttrDate(itemtype => itemtype,
3198 itemkey => itemkey,
3199 aname => 'PRE_WAIT_TIME',
3200 avalue => l_schedule);
3201
3202 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3203 iex_debug_pub.logMessage('cal_pre_wait result = ' ||l_return);
3204 END IF;
3205
3206 result := wf_engine.eng_completed ||':'||l_return;
3207
3208 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3209 iex_debug_pub.logmessage ('**** END cal_pre_wait ************');
3210 END IF;
3211 exception
3212 when others then
3213 result := wf_engine.eng_completed ||':'||wf_no;
3214 wf_core.context('IEX_STRATEGY_WF','cal_pre_wait',itemtype,
3215 itemkey,to_char(actid),funcmode);
3216 raise;
3217
3218
3219
3220 END cal_pre_wait;
3221
3222
3223
3224
3225 /* begin bug 4141678 by ctlee 3/14/2005 - loop one when create workitem failed */
3226 /* calculate re-create work item time wait
3227 *
3228 **/
3229 procedure wi_failed_first_time(
3230 itemtype in varchar2,
3231 itemkey in varchar2,
3232 actid in number,
3233 funcmode in varchar2,
3234 result out nocopy varchar2)IS
3235
3236 l_create_wi_error_count NUMBER;
3237 l_restart_create_wi_time date;
3238 l_return VARCHAR2(1);
3239 l_strategy_status varchar2(100);
3240
3241 BEGIN
3242 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3243 iex_debug_pub.logmessage ('**** START wi_failed_first_time ************');
3244 END IF;
3245 if funcmode <> 'RUN' then
3246 result := wf_engine.eng_null;
3247 return;
3248 end if;
3249
3250 l_strategy_status := wf_engine.GetItemAttrText(itemtype => itemtype,
3251 itemkey => itemkey,
3252 aname => 'STRATEGY_STATUS');
3253 if l_strategy_status = 'CANCELLED' then
3254 l_return := wf_no;
3255 result := wf_engine.eng_completed ||':'||l_return;
3256 return;
3257 end if;
3258
3259 l_create_wi_error_count := wf_engine.GetItemAttrNumber(
3260 itemtype => itemtype,
3261 itemkey => itemkey,
3262 aname => 'CREATE_WI_ERROR_COUNT');
3263
3264
3265 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3266 iex_debug_pub.logMessage('wi_failed_first_time l_create_wi_error_count = ' ||l_create_wi_error_count);
3267 END IF;
3268
3269 if (l_create_wi_error_count = 1) then
3270 l_create_wi_error_count := 0;
3271 l_return := wf_no;
3272 else
3273 -- wait for one day, the workflow background process will pick it up
3274 begin
3275 select sysdate + 1
3276 into l_restart_create_wi_time
3277 from dual;
3278 exception
3279 when others then
3280 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3281 iex_debug_pub.logMessage('wi_failed_first_time get sysdate time error ');
3282 END IF;
3283 end;
3284
3285 iex_debug_pub.logMessage('wi_failed_first_time l_restart_create_wi_time = ' ||l_restart_create_wi_time);
3286
3287 l_create_wi_error_count := 1;
3288 l_return := wf_yes;
3289
3290 wf_engine.SetItemAttrDate(itemtype => itemtype,
3291 itemkey => itemkey,
3292 aname => 'RESTART_CREATE_WI_TIME',
3293 avalue => l_restart_create_wi_time);
3294 end if;
3295
3296 wf_engine.SetItemAttrNumber(itemtype =>itemtype,
3297 itemkey => itemkey,
3298 aname => 'CREATE_WI_ERROR_COUNT',
3299 avalue => l_create_wi_error_count);
3300
3301 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3302 iex_debug_pub.logMessage('wi_failed_first_time result = ' ||l_return);
3303 END IF;
3304
3305 result := wf_engine.eng_completed ||':'||l_return;
3306
3307 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3308 iex_debug_pub.logmessage ('**** END wi_failed_first_time ************');
3309 END IF;
3310 exception
3311 when others then
3312 result := wf_engine.eng_completed ||':'||wf_no;
3313 wf_core.context('IEX_STRATEGY_WF','wi_failed_first_time',itemtype,
3314 itemkey,to_char(actid),funcmode);
3315 raise;
3316
3317 END wi_failed_first_time;
3318 /* end bug 4141678 by ctlee 3/14/2005 - loop one when create workitem failed */
3319
3320
3321 /**
3322 * check whether the work item is optional or not
3323 **/
3324 procedure OPTIONAL_CHECK(
3325 itemtype in varchar2,
3326 itemkey in varchar2,
3327 actid in number,
3328 funcmode in varchar2,
3329 result out NOCOPY varchar2) IS
3330
3331 l_work_item_temp_id NUMBER;
3332 l_result VARCHAR2(1);
3333 l_value VARCHAr2(300);
3334 l_work_item_id number;
3335 l_optional VARCHAR2(1);
3336
3337 CURSOR c_optional(p_work_item_id number) IS
3338 SELECT optional_yn
3339 FROM iex_strategy_work_items
3340 WHERE work_item_id = p_work_item_id;
3341
3342 BEGIN
3343 -- IF PG_DEBUG < 10 THEN
3344 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3345 iex_debug_pub.logmessage ('**** START OPTIONAL_CHECK ************');
3346 END IF;
3347 if funcmode <> 'RUN' then
3348 result := wf_engine.eng_null;
3349 return;
3350 end if;
3351
3352 -- IF PG_DEBUG < 10 THEN
3353 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3354 iex_debug_pub.logmessage('OPTIONAL_CHECK: ' || '*************FUNCMODE' ||funcmode||'**********************');
3355 END IF;
3356
3357 l_value :=wf_engine.GetActivityLabel(actid);
3358 wf_engine.SetItemAttrText(itemtype => itemtype,
3359 itemkey => itemkey,
3360 aname => 'ACTIVITY_NAME',
3361 avalue => l_value);
3362 -- IF PG_DEBUG < 10 THEN
3363 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3364 iex_debug_pub.logMessage('OPTIONAL_CHECK: ' || 'ACTIVITYNAME' ||l_value);
3365 END IF;
3366
3367 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
3368 itemtype => itemtype,
3369 itemkey => itemkey,
3370 aname => 'WORKITEM_TEMPLATE_ID');
3371
3372 select nvl(optional_yn,'N') INTO l_result
3373 from iex_stry_temp_work_items_vl
3374 where work_item_temp_id =l_work_item_temp_id;
3375
3376 l_work_item_id := wf_engine.GetItemAttrNumber(
3377 itemtype => itemtype,
3378 itemkey => itemkey,
3379 aname => 'WORK_ITEMID');
3380
3381 -- start bug 10133319 optional override
3382 OPEN c_optional(l_work_item_id);
3383 FETCH c_optional INTO l_optional;
3384 CLOSE c_optional;
3385
3386 IF l_optional IS NOT NULL THEN
3387 l_result := l_optional;
3388 END IF;
3389 -- end bug 10133319
3390 result := wf_engine.eng_completed ||':'||l_result;
3391 -- IF PG_DEBUG < 10 THEN
3392 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3393 iex_debug_pub.logmessage ('**** END OPTIONAL_CHECK ************');
3394 END IF;
3395 exception
3396 when others then
3397 result := wf_engine.eng_completed ||':'||wf_no;
3398 wf_core.context('IEX_STRATEGY_WF','OPTIONAL_CHECK',itemtype,
3399 itemkey,to_char(actid),funcmode);
3400 raise;
3401
3402 END OPTIONAL_CHECK;
3403
3404 /**
3405 * check whether the work item should be escalated or not
3406 **/
3407 procedure ESCALATE_CHECK(
3408 itemtype in varchar2,
3409 itemkey in varchar2,
3410 actid in number,
3411 funcmode in varchar2,
3412 result out NOCOPY varchar2)IS
3413
3414 l_work_item_temp_id NUMBER;
3415 l_result VARCHAR2(1);
3416 l_value VARCHAR2(300);
3417 l_work_item_id number;
3418 l_escalate VARCHAR2(1);
3419
3420 CURSOR c_escalate(p_work_item_id NUMBER) IS
3421 SELECT escalate_yn
3422 FROM iex_strategy_work_items
3423 WHERE work_item_id = p_work_item_id;
3424
3425 BEGIN
3426 -- IF PG_DEBUG < 10 THEN
3427 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3428 iex_debug_pub.logmessage ('**** START ESCALATE_CHECK ************');
3429 END IF;
3430 if funcmode <> 'RUN' then
3431 result := wf_engine.eng_null;
3432 return;
3433 end if;
3434
3435 l_value :=wf_engine.GetActivityLabel(actid);
3436 wf_engine.SetItemAttrText(itemtype => itemtype,
3437 itemkey => itemkey,
3438 aname => 'ACTIVITY_NAME',
3439 avalue => l_value);
3440
3441 -- IF PG_DEBUG < 10 THEN
3442 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3443 iex_debug_pub.logMessage('ESCALATE_CHECK: ' || 'ACTIVITYNAME' ||l_value);
3444 END IF;
3445
3446 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
3447 itemtype => itemtype,
3448 itemkey => itemkey,
3449 aname => 'WORKITEM_TEMPLATE_ID');
3450
3451 select nvl(escalate_yn,'N') INTO l_result
3452 from iex_stry_temp_work_items_vl
3453 where work_item_temp_id =l_work_item_temp_id;
3454
3455 l_work_item_id := wf_engine.GetItemAttrNumber(
3456 itemtype => itemtype,
3457 itemkey => itemkey,
3458 aname => 'WORK_ITEMID');
3459
3460 -- start bug 10133319 escalate override
3461 OPEN c_escalate(l_work_item_id);
3462 FETCH c_escalate INTO l_escalate;
3463 CLOSE c_escalate;
3464
3465 IF l_escalate IS NOT NULL THEN
3466 l_result := l_escalate;
3467 END IF;
3468 -- end bug 10133319
3469 result := wf_engine.eng_completed ||':'||l_result;
3470 -- IF PG_DEBUG < 10 THEN
3471 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3472 iex_debug_pub.logmessage ('**** END ESCALATE_CHECK ************');
3473 END IF;
3474 exception
3475 when others then
3476 result := wf_engine.eng_completed ||':'||wf_no;
3477 wf_core.context('IEX_STRATEGY_WF','ESCALATE_CHECK',itemtype,
3478 itemkey,to_char(actid),funcmode);
3479 raise;
3480
3481 END ESCALATE_CHECK;
3482
3483 /**
3484 * check whether the to send a notification
3485 **/
3486 procedure NOTIFY_CHECK(
3487 itemtype in varchar2,
3488 itemkey in varchar2,
3489 actid in number,
3490 funcmode in varchar2,
3491 result out NOCOPY varchar2)IS
3492
3493 l_work_item_temp_id NUMBER;
3494 l_result VARCHAR2(1);
3495 l_value VARCHAr2(300);
3496 l_work_item_id number;
3497 l_notify VARCHAR2(1);
3498
3499 CURSOR c_notify(p_work_item_id NUMBER) IS
3500 SELECT notify_yn
3501 FROM iex_strategy_work_items
3502 WHERE work_item_id = p_work_item_id;
3503
3504 BEGIN
3505 -- IF PG_DEBUG < 10 THEN
3506 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3507 iex_debug_pub.logmessage ('**** START NOTIFY_CHECK ************');
3508 END IF;
3509 if funcmode <> 'RUN' then
3510 result := wf_engine.eng_null;
3511 return;
3512 end if;
3513
3514 /* l_value :=wf_engine.GetActivityLabel(actid);
3515 wf_engine.SetItemAttrText(itemtype => itemtype,
3516 itemkey => itemkey,
3517 aname => 'ACTIVITY_NAME',
3518 avalue => l_value);
3519 -- IF PG_DEBUG < 10 THEN
3520 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3521 iex_debug_pub.logMessage('NOTIFY_CHECK: ' || 'ACTIVITYNAME' ||l_value);
3522 END IF;
3523 */
3524 l_work_item_temp_id := wf_engine.GetItemAttrNumber(
3525 itemtype => itemtype,
3526 itemkey => itemkey,
3527 aname => 'WORKITEM_TEMPLATE_ID');
3528
3529 select nvl(notify_yn,'N') INTO l_result
3530 from iex_stry_temp_work_items_vl
3531 where work_item_temp_id =l_work_item_temp_id;
3532
3533 l_work_item_id := wf_engine.GetItemAttrNumber(
3534 itemtype => itemtype,
3535 itemkey => itemkey,
3536 aname => 'WORK_ITEMID');
3537
3538 -- bug 10133319 notify override
3539 OPEN c_notify(l_work_item_id);
3540 FETCH c_notify INTO l_notify;
3541 CLOSE c_notify;
3542
3543 IF l_notify IS NOT NULL THEN
3544 l_result := l_notify;
3545 END IF;
3546 -- end bug 10133319
3547 result := wf_engine.eng_completed ||':'||l_result;
3548 -- IF PG_DEBUG < 10 THEN
3549 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3550 iex_debug_pub.logmessage ('**** END NOTIFY_CHECK ************');
3551 END IF;
3552 exception
3553 when others then
3554 result := wf_engine.eng_completed ||':'||wf_no;
3555 wf_core.context('IEX_STRATEGY_WF','NOTIFY_CHECK',itemtype,
3556 itemkey,to_char(actid),funcmode);
3557 raise;
3558
3559 END NOTIFY_CHECK;
3560
3561
3562 --Begin - schekuri - 03-Dec-2005 - Bug#4506922
3563 --set the ON_HOLD_WAIT_TIME attribute
3564 procedure set_on_hold_wait(
3565 itemtype in varchar2,
3566 itemkey in varchar2) IS
3567
3568 l_execution_time date;
3569
3570 BEGIN
3571
3572 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3573 iex_debug_pub.logmessage ('**** BEGIN set_on_hold_wait ************');
3574 END IF;
3575 select sysdate+(23/24)
3576 into l_execution_time
3577 from dual;
3578 wf_engine.SetItemAttrDate(itemtype => itemtype,
3579 itemkey => itemkey,
3580 aname => 'ON_HOLD_WAIT_TIME',
3581 avalue => l_execution_time);
3582
3583 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3584 iex_debug_pub.logmessage ('set_on_hold_wait: ' || 'ON_HOLD_WAIT_TIME' ||TO_CHAR(l_execution_time,'DD-MON-YYYY:HH:MI:SS'));
3585 END IF;
3586 exception
3587 when others then
3588 null;
3589
3590 END set_on_hold_wait;
3591 --end - schekuri - 03-Dec-2005 - Bug#4506922
3592
3593
3594 /**
3595 * check whether the work item status is on hold
3596 **/
3597 procedure ONHOLD_CHECK(
3598 itemtype in varchar2,
3599 itemkey in varchar2,
3600 actid in number,
3601 funcmode in varchar2,
3602 result out NOCOPY varchar2)IS
3603
3604 l_strategy_id NUMBER;
3605 l_result VARCHAR2(1);
3606 l_count NUMBER ;
3607 l_value VARCHAr2(300);
3608 --Added by schekuri for bug#4506922 on 03-Dec-2005
3609 l_work_item_id NUMBER;
3610 l_timeout_wi NUMBER;
3611 l_strategy_status VARCHAR2(100);
3612 BEGIN
3613 -- IF PG_DEBUG < 10 THEN
3614 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3615 iex_debug_pub.logmessage ('**** START ONHOLD_CHECK ************');
3616 END IF;
3617 if funcmode <> 'RUN' then
3618 result := wf_engine.eng_null;
3619 return;
3620 end if;
3621
3622 /*l_value :=wf_engine.GetActivityLabel(actid);
3623 wf_engine.SetItemAttrText(itemtype => itemtype,
3624 itemkey => itemkey,
3625 aname => 'ACTIVITY_NAME',
3626 avalue => l_value);
3627 -- IF PG_DEBUG < 10 THEN
3628 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3629 iex_debug_pub.logMessage('ONHOLD_CHECK: ' || 'ACTIVITYNAME' ||l_value);
3630 END IF;
3631 */
3632
3633 --begin bug#4506922 schekuri 03-Dec-2005
3634 --When the delinquency corresponding to the strategy becomes CURRENT or CLOSED,
3635 --strategy management concurrent program calls the procedure IEX_STRATEGY_WF.SEND_SIGNAL.
3636 --This procedure just updates the "STRATEGY_STATUS" attribute to "CLOSED" and completes the current activity.
3637 --But status of the strategy in table IEX_STRATEGIES is still 'ONHOLD'.
3638 --If workflow is waiting at WAIT_ON_HOLD_SIGNAL node, it will be completed and it goes to ONHOLD_CHECK node.
3639 --Since the status of the strategy in table IEX_STRATEGIES is still 'ONHOLD', it again goes to WAIT_ON_HOLD_SIGNAL node
3640 --and waits for 23 hrs. To avoid this added the following code.
3641 l_value :=wf_engine.GetActivityLabel(actid);
3642 if instr(l_value,'STRATEGY_WORKFLOW')>0 then
3643 l_strategy_status := wf_engine.GetItemAttrText(
3644 itemtype => itemtype,
3645 itemkey => itemkey,
3646 aname => 'STRATEGY_STATUS');
3647
3648 if l_strategy_status in ('CLOSED','CANCELLED') then
3649 result := wf_engine.eng_completed ||':'||'N';
3650 return;
3651 end if;
3652 end if;
3653 --end bug#4506922 schekuri 03-Dec-2005
3654
3655 l_strategy_id := wf_engine.GetItemAttrNumber(
3656 itemtype => itemtype,
3657 itemkey => itemkey,
3658 aname => 'STRATEGY_ID');
3659
3660 select decode(count(*),0,'N','Y') into l_result
3661 from iex_Strategies
3662 where strategy_id =l_strategy_id
3663 and status_code ='ONHOLD';
3664
3665 --begin bug#4506922 schekuri 03-Dec-2005
3666 --added the following code to avoid the workflow to get suspended at calc post wait node
3667 --after the timeout of the optional work item when the status of strategy is onhold.
3668 --Onhold check after calc post wait node stops the workflow when the strategy is onhold.
3669 --This is needed for workflows which are already started by the time this patch applied.
3670 --l_value :=wf_engine.GetActivityLabel(actid);
3671 if instr(l_value,'STRATEGY_SUBPROCESS')>0 and l_result = 'Y' then
3672 l_work_item_id := wf_engine.GetItemAttrText(
3673 itemtype => itemtype,
3674 itemkey => itemkey,
3675 aname => 'WORK_ITEMID');
3676 if l_work_item_id is not null then
3677 begin
3678 select count(*)
3679 into l_timeout_wi
3680 from iex_strategy_work_items
3681 where work_item_id=l_work_item_id
3682 and status_code='TIMEOUT';
3683 exception
3684 when others then
3685 l_timeout_wi:=0;
3686 end ;
3687 if l_timeout_wi>0 then
3688 l_result := 'N';
3689 end if;
3690 end if;
3691 end if;
3692
3693 --end bug#4506922 schekuri 03-Dec-2005
3694
3695 result := wf_engine.eng_completed ||':'||l_result;
3696
3697 --Begin - schekuri - 03-Dec-2005 - bug#4506922
3698 --if strategy is ONHOLD update the attribute 'ON_HOLD_WAIT_TIME' to sysdate + 23 hrs.
3699 if l_result='Y' then
3700 --setting on_hold_wait_time_attribute;
3701 set_on_hold_wait(itemtype,itemkey);
3702 end if;
3703 --End - schekuri - 03-Dec-2005 - bug#4506922
3704
3705 -- IF PG_DEBUG < 10 THEN
3706 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3707 iex_debug_pub.logmessage ('**** END ONHOLD_CHECK ************');
3708 END IF;
3709
3710 exception
3711 when others then
3712 result := wf_engine.eng_completed ||':'||wf_no;
3713 wf_core.context('IEX_STRATEGY_WF','ONHOLD_CHECK',itemtype,
3714 itemkey,to_char(actid),funcmode);
3715 raise;
3716
3717 END ONHOLD_CHECK;
3718
3719
3720
3721
3722 procedure UPDATE_WORK_ITEM(
3723 itemtype in varchar2,
3724 itemkey in varchar2,
3725 actid in number,
3726 funcmode in varchar2,
3727 result out NOCOPY varchar2) IS
3728 l_api_version NUMBER := 1.0;
3729 l_return_status VARCHAR2(1);
3730 l_msg_count NUMBER;
3731 l_msg_data VARCHAR2(32767);
3732 l_work_item_id NUMBER;
3733 exc EXCEPTION;
3734 l_error VARCHAR2(32767);
3735 BEGIN
3736 -- IF PG_DEBUG < 10 THEN
3737 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3738 iex_debug_pub.logmessage ('**** START UPDATE_WORK_ITEM ************');
3739 END IF;
3740 if funcmode <> 'RUN' then
3741 result := wf_engine.eng_null;
3742 return;
3743 end if;
3744
3745 l_work_item_id := wf_engine.GetItemAttrText(
3746 itemtype => itemtype,
3747 itemkey => itemkey,
3748 aname => 'WORK_ITEMID');
3749 -- IF PG_DEBUG < 10 THEN
3750 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3751 iex_debug_pub.logmessage('UPDATE_WORK_ITEM: ' || 'value of workitem id '||l_work_item_id);
3752 END IF;
3753 IEX_STRY_UTL_PUB.UPDATE_WORK_ITEM (p_api_version => l_api_version,
3754 p_init_msg_list => FND_API.G_TRUE,
3755 p_commit => FND_API.G_FALSE,
3756 x_msg_count => l_msg_count,
3757 x_msg_data => l_msg_data,
3758 x_return_status => l_return_status,
3759 p_work_item_id => l_work_item_id,
3760 p_status => 'TIMEOUT');
3761 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
3762 result := wf_engine.eng_completed ||':'||wf_yes;
3763 else
3764 RAISE EXC;
3765 END IF;
3766 -- IF PG_DEBUG < 10 THEN
3767 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3768 iex_debug_pub.logmessage ('**** END UPDATE_WORK_ITEM ************');
3769 END IF;
3770
3771
3772 exception
3773 WHEN EXC THEN
3774 --pass the error message
3775 -- get error message and passit pass it
3776 Get_Messages(l_msg_count,l_error);
3777 -- IF PG_DEBUG < 10 THEN
3778 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3779 iex_debug_pub.logmessage('UPDATE_WORK_ITEM: ' || 'error message is ' || l_error);
3780 END IF;
3781 wf_core.context('IEX_STRATEGY_WF','CUSTOM_CHECK',itemtype,
3782 itemkey,to_char(actid),funcmode,l_error);
3783 raise;
3784 when others then
3785 wf_core.context('IEX_STRATEGY_WF','CUSTOM_CHECK',itemtype,
3786 itemkey,to_char(actid),funcmode);
3787 raise;
3788 END UPDATE_WORK_ITEM;
3789
3790
3791 /**
3792 * check whether the work item is optional or not
3793 **/
3794 procedure WAIT_OPTIONAL(
3795 itemtype in varchar2,
3796 itemkey in varchar2,
3797 actid in number,
3798 funcmode in varchar2,
3799 result out NOCOPY varchar2) IS
3800
3801 l_work_item_temp_id NUMBER;
3802 l_result VARCHAR2(1);
3803 l_value VARCHAR2(300);
3804
3805 BEGIN
3806 -- IF PG_DEBUG < 10 THEN
3807 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3808 iex_debug_pub.logmessage ('**** START WAIT_OPTIONAL ************');
3809 END IF;
3810 if funcmode <> wf_engine.eng_run then
3811 -- IF PG_DEBUG < 10 THEN
3812 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3813 iex_debug_pub.logmessage('WAIT_OPTIONAL: ' || 'SECOND TIME FUNCMODE' ||funcmode);
3814 END IF;
3815 result := wf_engine.eng_null;
3816 return;
3817 end if;
3818
3819
3820
3821 -- IF PG_DEBUG < 10 THEN
3822 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3823 iex_debug_pub.logmessage('WAIT_OPTIONAL: ' || 'FUNCMODE' ||funcmode);
3824 END IF;
3825 l_value :=wf_engine.GetActivityLabel(actid);
3826 wf_engine.SetItemAttrText(itemtype => itemtype,
3827 itemkey => itemkey,
3828 aname => 'ACTIVITY_NAME',
3829 avalue => l_value);
3830 -- IF PG_DEBUG < 10 THEN
3831 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3832 iex_debug_pub.logMessage('WAIT_OPTIONAL: ' || 'ACTIVITYNAME' ||l_value);
3833 END IF;
3834
3835
3836 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
3837 ':'||wf_engine.eng_null;
3838 -- IF PG_DEBUG < 10 THEN
3839 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3840 iex_debug_pub.logmessage ('**** END WAIT_OPTIONAL ************');
3841 END IF;
3842 exception
3843 when others then
3844 result := wf_engine.eng_completed ||':'||wf_no;
3845 wf_core.context('IEX_STRATEGY_WF','WAIT_OPTIONAL',itemtype,
3846 itemkey,to_char(actid),funcmode);
3847 raise;
3848
3849 END WAIT_OPTIONAL;
3850
3851 /**
3852 * check whether the work item is optional or not
3853 **/
3854 procedure WAIT_ESCALATION(
3855 itemtype in varchar2,
3856 itemkey in varchar2,
3857 actid in number,
3858 funcmode in varchar2,
3859 result out NOCOPY varchar2) IS
3860
3861 l_work_item_temp_id NUMBER;
3862 l_result VARCHAR2(1);
3863 l_value VARCHAR2(300);
3864
3865 BEGIN
3866 -- IF PG_DEBUG < 10 THEN
3867 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3868 iex_debug_pub.logmessage ('**** START WAIT_ESCALATION ************');
3869 END IF;
3870 if funcmode <> wf_engine.eng_run then
3871 result := wf_engine.eng_null;
3872 return;
3873 end if;
3874
3875
3876
3877 -- IF PG_DEBUG < 10 THEN
3878 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3879 iex_debug_pub.logmessage('WAIT_ESCALATION: ' || 'FUNCMODE' ||funcmode);
3880 END IF;
3881 l_value :=wf_engine.GetActivityLabel(actid);
3882 wf_engine.SetItemAttrText(itemtype => itemtype,
3883 itemkey => itemkey,
3884 aname => 'ACTIVITY_NAME',
3885 avalue => l_value);
3886
3887 -- IF PG_DEBUG < 10 THEN
3888 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3889 iex_debug_pub.logMessage('WAIT_ESCALATION: ' || 'ACTIVITYNAME' ||l_value);
3890 END IF;
3891
3892
3893 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
3894 ':'||wf_engine.eng_null;
3895 -- IF PG_DEBUG < 10 THEN
3896 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3897 iex_debug_pub.logmessage ('**** END WAIT_ESCALATION ************');
3898 END IF;
3899 exception
3900 when others then
3901 result := wf_engine.eng_completed ||':'||wf_no;
3902 wf_core.context('IEX_STRATEGY_WF','WAIT_ESCALATION',itemtype,
3903 itemkey,to_char(actid),funcmode);
3904 raise;
3905
3906 END WAIT_ESCALATION;
3907
3908 /**
3909 * 04/29/02
3910 * sets the strategy to wait
3911 * before the work item gets created
3912 * this is because if the background process
3913 * is not active and if the user clicks change
3914 * strategy , the workflow should close the
3915 * strategy .if the back ground process is
3916 * running , clear the activity label and
3917 * step over to 'work_items_complete node'
3918 **/
3919 procedure WAIT_STRATEGY(
3920 itemtype in varchar2,
3921 itemkey in varchar2,
3922 actid in number,
3923 funcmode in varchar2,
3924 result out NOCOPY varchar2) IS
3925
3926 l_work_item_temp_id NUMBER;
3927 l_result VARCHAR2(1);
3928 l_value VARCHAR2(300);
3929
3930 BEGIN
3931
3932 -- IF PG_DEBUG < 10 THEN
3933 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3934 iex_debug_pub.logmessage ('**** START WAIT_STRATEGY ************');
3935 END IF;
3936 if funcmode <> wf_engine.eng_run then
3937 result := wf_engine.eng_null;
3938 return;
3939 end if;
3940
3941
3942
3943 -- IF PG_DEBUG < 10 THEN
3944 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3945 iex_debug_pub.logmessage('WAIT_STRATEGY: ' || 'FUNCMODE' ||funcmode);
3946 END IF;
3947 l_value :=wf_engine.GetActivityLabel(actid);
3948 wf_engine.SetItemAttrText(itemtype => itemtype,
3949 itemkey => itemkey,
3950 aname => 'ACTIVITY_NAME',
3951 avalue => l_value);
3952
3953 -- IF PG_DEBUG < 10 THEN
3954 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3955 iex_debug_pub.logMessage('WAIT_STRATEGY: ' || 'ACTIVITYNAME' ||l_value);
3956 END IF;
3957
3958
3959 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
3960 ':'||wf_engine.eng_null;
3961 -- IF PG_DEBUG < 10 THEN
3962 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
3963 iex_debug_pub.logmessage ('**** END WAIT_STRATEGY ************');
3964 END IF;
3965 exception
3966 when others then
3967 result := wf_engine.eng_completed ||':'||wf_engine.eng_null;
3968 wf_core.context('IEX_STRATEGY_WF','WAIT_STRATEGY',itemtype,
3969 itemkey,to_char(actid),funcmode);
3970 raise;
3971
3972 END WAIT_STRATEGY;
3973
3974 ----------------------------------------------
3975 --if work item creation fails ,
3976 --and if adminstartor does not want to continue
3977 --replies via email 'NO' then close strategy and
3978 --complete the workflow.
3979 --set the status attribute to 'CANCELLED'
3980 -- when closing strategy(CLOSE_STRATEGY procedure)
3981 -- this attribute is checked.
3982 --05/02/02
3983
3984 procedure SET_STRATEGY_STATUS(
3985 itemtype in varchar2,
3986 itemkey in varchar2,
3987 actid in number,
3988 funcmode in varchar2,
3989 result out NOCOPY varchar2) IS
3990 l_strategy_id NUMBER;
3991
3992 BEGIN
3993
3994 wf_engine.SetItemAttrText(itemtype => itemtype,
3995 itemkey => itemkey,
3996 aname => 'STRATEGY_STATUS',
3997 avalue => 'CANCELLED');
3998
3999 -- inside the close_strategy_procedure , the
4000 --existing strategy is CANCELLED.
4001 wf_engine.SetItemAttrNumber(itemtype =>itemtype,
4002 itemkey => itemkey,
4003 aname => 'WORK_ITEMID',
4004 avalue => NULL);
4005
4006 exception
4007 when others then
4008 result := wf_engine.eng_completed ||':'||wf_engine.eng_null;
4009 wf_core.context('IEX_STRATEGY_WF','SET_STRATEGY_STATUS',itemtype,
4010 itemkey,to_char(actid),funcmode);
4011 raise;
4012 END SET_STRATEGY_STATUS;
4013
4014 /**
4015 --07/31/02
4016 --abort the strategy workflow
4017 --update the work items,
4018 --close or cancel strategy
4019 --abort all the custom workflows
4020 --this will happen if the workflow failed before
4021 --being suspended, for example if notification
4022 -- does not have a performer.
4023
4024 **/
4025 procedure CLOSE_AND_ABORT_STRATEGY_WF(
4026 l_strategy_id in NUMBER,
4027 l_status in VARCHAR2 ) IS
4028
4029 l_api_version NUMBER := 1.0;
4030 l_return_status VARCHAR2(1);
4031 l_msg_count NUMBER;
4032 l_msg_data VARCHAR2(32767);
4033
4034
4035 BEGIN
4036 -- IF PG_DEBUG < 10 THEN
4037 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4038 iex_debug_pub.logmessage ('**** start CLOSE_AND_ABORT_STRATEGY_WF ************');
4039 END IF;
4040
4041 abort_processes(l_strategy_id);
4042
4043 IEX_STRY_UTL_PUB.CLOSE_STRY_AND_WITEMS(p_api_version => l_api_version,
4044 p_init_msg_list => FND_API.G_TRUE,
4045 p_commit => FND_API.G_FALSE,
4046 x_msg_count => l_msg_count,
4047 x_msg_data => l_msg_data,
4048 x_return_status => l_return_status,
4049 p_strategy_id => l_strategy_id,
4050 p_status => l_status);
4051
4052 -- IF PG_DEBUG < 10 THEN
4053 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4054 iex_debug_pub.logmessage ('CLOSE_AND_ABORT_STRATEGY_WF: ' || '**** status after closing/cancelling strategy is '||
4055 l_return_status);
4056 END IF;
4057
4058 BEGIN
4059 wf_engine.abortprocess(itemtype =>'IEXSTRY',
4060 itemkey => l_strategy_id);
4061 EXCEPTION
4062 WHEN OTHERS THEN
4063 -- IF PG_DEBUG < 10 THEN
4064 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4065 iex_debug_pub.logmessage('CLOSE_AND_ABORT_STRATEGY_WF: ' || 'abort process ' || l_strategy_id ||
4066 'has failed');
4067 END IF;
4068 END;
4069
4070 -- IF PG_DEBUG < 10 THEN
4071 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4072 iex_debug_pub.logmessage ('**** end CLOSE_AND_ABORT_STRATEGY_WF ************');
4073 END IF;
4074 END CLOSE_AND_ABORT_STRATEGY_WF;
4075
4076
4077 --set user_id,responsibility_id
4078 --and application responsibility id
4079 --which will then used by the the an other activity
4080 -- which comes after a deferred activitiy
4081 --08/02/02
4082
4083 procedure SET_SESSION_CTX(
4084 itemtype in varchar2,
4085 itemkey in varchar2,
4086 actid in number,
4087 funcmode in varchar2,
4088 result out NOCOPY varchar2) IS
4089
4090 l_party_id NUMBER;
4091 l_party_name varchar2(2000);
4092
4093
4094 BEGIN
4095 -- IF PG_DEBUG < 10 THEN
4096 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4097 iex_debug_pub.logmessage ('**** start SET_SESSION_CTX ************');
4098 END IF;
4099 --set item Attributes
4100 --set org_id
4101 wf_engine.SetItemAttrNumber(itemtype =>itemtype,
4102 itemkey => itemkey,
4103 aname => 'USER_ID',
4104 avalue => FND_GLOBAL.USER_ID);
4105 --set resp_id
4106 wf_engine.SetItemAttrNumber(itemtype =>itemtype,
4107 itemkey => itemkey,
4108 aname => 'RESP_ID',
4109 avalue => FND_GLOBAL.RESP_ID);
4110 --set app_resp_id
4111 wf_engine.SetItemAttrNumber(itemtype =>itemtype,
4112 itemkey => itemkey,
4113 aname => 'RESP_APPL_ID',
4114 avalue => FND_GLOBAL.RESP_APPL_ID );
4115
4116 -- IF PG_DEBUG < 10 THEN
4117 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4118 iex_debug_pub.logmessage ('SET_SESSION_CTX: ' || 'USER_ID' || FND_GLOBAL.USER_ID);
4119 END IF;
4120 -- IF PG_DEBUG < 10 THEN
4121 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4122 iex_debug_pub.logmessage ('SET_SESSION_CTX: ' || 'RESP_ID' || FND_GLOBAL.RESP_ID);
4123 END IF;
4124 -- IF PG_DEBUG < 10 THEN
4125 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4126 iex_debug_pub.logmessage ('SET_SESSION_CTX: ' || 'RESP_APPL_ID' ||FND_GLOBAL.RESP_APPL_ID);
4127 END IF;
4128
4129
4130 ---get party_id from the work flow
4131 l_party_id := wf_engine.GetItemAttrNumber(
4132 itemtype => itemtype,
4133 itemkey => itemkey,
4134 aname => 'PARTY_ID');
4135
4136 if l_party_id is not null then
4137 begin
4138 select party_name into l_party_name from hz_parties where party_id = l_party_id;
4139 wf_engine.SetItemAttrText(itemtype =>itemtype,
4140 itemkey => itemkey,
4141 aname => 'CUSTOMER_NAME',
4142 avalue => l_party_name);
4143 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4144 iex_debug_pub.logmessage ('party_name' ||l_party_name);
4145 END IF;
4146 exception
4147 when others then
4148 null;
4149 end;
4150 end if;
4151
4152 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4153 iex_debug_pub.logmessage ('**** End SET_SESSION_CTX ************');
4154 END IF;
4155 EXCEPTION
4156 when others then
4157 result := wf_engine.eng_completed ||':'||wf_engine.eng_null;
4158 wf_core.context('IEX_STRATEGY_WF','SET_SESSION_CTX',itemtype,
4159 itemkey,to_char(actid),funcmode);
4160 raise;
4161
4162 END;
4163
4164 -- Begin- Andre 07/28/2004 - Add bill to assignmnet
4165
4166 ------------------- procedure get_billto_resource ------------------------------
4167 /** get resource id for the given competence and bill to address
4168 *
4169 **/
4170 function get_billto_resource ( p_siteuse_id IN NUMBER,
4171 p_competence_tab IN tab_of_comp_id,
4172 x_resource_id OUT NOCOPY NUMBER)
4173 RETURN BOOLEAN IS
4174
4175 l_bReturn boolean := FALSE;
4176
4177 l_vTest varchar2(20) ;
4178
4179 l_api_version NUMBER := 1.0;
4180 l_init_msg_list VARCHAR2(1) ;
4181 l_resource_tab iex_utilities.resource_tab_type;
4182
4183 l_commit VARCHAR2(1) ;
4184 l_return_status VARCHAR2(1);
4185 l_msg_count NUMBER;
4186 l_msg_data VARCHAR2(32767);
4187 l_validation_level NUMBER ;
4188
4189 l_resource_id NUMBER ;
4190 l_count NUMBER :=0;
4191 l_found BOOLEAN := TRUE;
4192 -- Changed for the bug#7656223 by PNAVEENK on 2-1-2009
4193 cursor c_get_person_id (l_person_id NUMBER,
4194 l_competence_id NUMBER)is
4195 select count(person_id)
4196 from per_competence_elements
4197 where competence_id =l_competence_id
4198 and person_id =l_person_id
4199 and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
4200 and trunc(effective_date_from) <= trunc(sysdate) ;
4201 -- End for the bug#7656223
4202 BEGIN
4203 -- initialize variable
4204 l_vTest := 'Instance time';
4205 l_init_msg_list := FND_API.G_TRUE;
4206 l_commit := FND_API.G_FALSE;
4207 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
4208 l_resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
4209
4210 iex_debug_pub.logmessage ('get_billto_resource: Test variable is: ' || l_vTest);
4211
4212 l_vTest := 'Runtime Changed';
4213
4214 -- IF PG_DEBUG < 10 THEN
4215 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4216 iex_debug_pub.logmessage ('get_billto_resource: ' || '**** BEGIN get_billto_resource ************');
4217 iex_debug_pub.logmessage ('default resource id from profile iex_stry_default_resource) ' || l_resource_id);
4218 iex_debug_pub.logmessage ('calling get_billto_resources ' || p_siteuse_id);
4219 END IF;
4220 -- get resource id table of reords for the given site use id
4221 -- the record has resource id and person id along with the user name
4222 --Begin bug#5373412 schekuri 10-Jul-2006
4223 --Call new consolidated procedure get_assigned_collector
4224 /*iex_utilities.get_billto_resources(p_api_version => l_api_version,
4225 p_init_msg_list => FND_API.G_TRUE,
4226 p_commit => FND_API.G_FALSE,
4227 p_validation_level => l_validation_level,
4228 x_msg_count => l_msg_count,
4229 x_msg_data => l_msg_data,
4230 x_return_status => l_return_status,
4231 p_site_use_id => p_siteuse_id,
4232 x_resource_tab => l_resource_tab);*/
4233
4234 iex_utilities.get_assigned_collector(p_api_version => l_api_version,
4235 p_init_msg_list => FND_API.G_TRUE,
4236 p_commit => FND_API.G_FALSE,
4237 p_validation_level => l_validation_level,
4238 p_level => 'BILLTO',
4239 p_level_id => p_siteuse_id,
4240 x_msg_count => l_msg_count,
4241 x_msg_data => l_msg_data,
4242 x_return_status => l_return_status,
4243 x_resource_tab => l_resource_tab);
4244
4245 --End bug#5373412 schekuri 10-Jul-2006
4246 --IF PG_DEBUG < 10 THEN
4247 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4248 iex_debug_pub.logmessage('get_billto_resource: ' || 'in get resource and l_return_status = '||l_return_status);
4249 iex_debug_pub.logmessage('in get resource and l_return_status from iex_utilities.get_billto_resources = '||l_return_status);
4250 iex_debug_pub.logmessage('resource count from iex_utilities.get_billto_resources = '||l_resource_tab.count);
4251 END IF;
4252
4253 if l_resource_tab.count > 0 then
4254 l_bReturn := true;
4255 end if;
4256 -- if COMPETENCE id exists for the given work template Id,
4257 -- see if the person id from the
4258 -- the above l_resource_tab matches with the competence Id
4259 -- pick if there is match or pick any resource if there is no match
4260 -- or competence id of the work template id is null
4261
4262
4263 --if p_competence_id IS NULL THEN
4264 if p_competence_tab.count = 0 THEN
4265 -- IF PG_DEBUG < 10 THEN
4266 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4267 iex_debug_pub.logmessage('get_billto_resource: ' || 'Competence table is empty');
4268 END IF;
4269 --get the first resource id if competence id is null from
4270 -- the work item template
4271 FOR i in 1..l_resource_tab.count LOOP
4272 l_resource_id := l_resource_tab(i).resource_id;
4273 -- IF PG_DEBUG < 10 THEN
4274 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4275 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
4276 END IF;
4277 EXIT;
4278 END LOOP;
4279 else
4280 -- IF PG_DEBUG < 10 THEN
4281 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4282 iex_debug_pub.logmessage('Loop for matching competence. count = '||p_competence_tab.count );
4283 END IF;
4284 FOR i in 1..l_resource_tab.count LOOP
4285 -- IF PG_DEBUG < 10 THEN
4286 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4287 iex_debug_pub.logmessage('get_billto_resource: ' || 'PERSON ID is '||l_resource_tab(i).person_id);
4288 iex_debug_pub.logmessage('get_billto_resource: ' || 'RESOURCE ID is '||l_resource_tab(i).resource_id);
4289 END IF;
4290
4291 FOR j in 1..p_competence_tab.count LOOP
4292
4293 OPEN c_get_person_id (l_resource_tab(i).person_id,
4294 p_competence_tab(j));
4295 FETCH c_get_person_id INTO l_count;
4296 CLOSE c_get_person_id;
4297 -- IF PG_DEBUG < 10 THEN
4298 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4299 iex_debug_pub.logmessage('get_billto_resource: ' || 'COMPETENCE ID is '||
4300 p_competence_tab(j));
4301 END IF;
4302 -- IF PG_DEBUG < 10 THEN
4303 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4304 iex_debug_pub.logmessage('get_billto_resource: ' || 'no of matches '|| l_count);
4305 END IF;
4306 If l_count =0 THEN
4307 -- match not found, use the first resource and exit out NOCOPY
4308 -- from the competence loop.
4309 --Begin bug#5373412 schekuri 10-Jul-2006
4310 --Commented the below the code to return default resource id instead of first resource id
4311 --when there is no resource found matching the competency of the workitem.
4312 /*l_resource_id := l_resource_tab(1).resource_id;
4313 -- IF PG_DEBUG < 10 THEN
4314 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4315 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
4316 END IF;*/
4317 --End bug#5373412 schekuri 10-Jul-2006
4318 -- have to look for the next resource if l_found is false
4319 l_found :=FALSE;
4320 EXIT;
4321 ELSE
4322 l_resource_id := l_resource_tab(i).resource_id;
4323 -- IF PG_DEBUG < 10 THEN
4324 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4325 iex_debug_pub.logmessage('1st record found with competence matched l_resource_tab l_resource_id = '|| l_resource_id);
4326 END IF;
4327 l_found :=TRUE;
4328 End if;
4329 END LOOP;
4330 if l_found THEN
4331 -- a matching resource with all the competencies
4332 --have been found ,stop looking for next resource
4333 -- IF PG_DEBUG < 10 THEN
4334 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4335 iex_debug_pub.logmessage('get_billto_resource: ' || 'match found and RESOURCE ID is =>'
4336 ||l_resource_tab(i).resource_id);
4337 END IF;
4338 exit;
4339 end if;
4340 END LOOP;
4341 end if;
4342 --assign out NOCOPY variable
4343 x_resource_id :=l_resource_id;
4344 -- IF PG_DEBUG < 10 THEN
4345 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4346 iex_debug_pub.logmessage('get_billto_resource: ' || 'value of x_resource_id' ||x_resource_id);
4347 END IF;
4348 -- IF PG_DEBUG < 10 THEN
4349 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4350 iex_debug_pub.logmessage ('get_billto_resource: ' || '**** END get_billto_resource ************');
4351 END IF;
4352
4353 return l_bReturn;
4354 END get_billto_resource;
4355 -- End- Andre 07/28/2004 - Add bill to assignmnet
4356
4357
4358
4359 -- Begin- krishna 07/01/2005 - Add account assignment
4360
4361 ------------------- procedure get_billto_resource ------------------------------
4362 /** get resource id for the given competence and account id
4363 *
4364 **/
4365 function get_account_resource ( p_account_id IN NUMBER,
4366 p_competence_tab IN tab_of_comp_id,
4367 x_resource_id OUT NOCOPY NUMBER)
4368 RETURN BOOLEAN IS
4369
4370 l_bReturn boolean := FALSE;
4371
4372 l_vTest varchar2(20) ;
4373
4374 l_api_version NUMBER := 1.0;
4375 l_init_msg_list VARCHAR2(1) ;
4376 l_resource_tab iex_utilities.resource_tab_type;
4377
4378 l_commit VARCHAR2(1) ;
4379 l_return_status VARCHAR2(1);
4380 l_msg_count NUMBER;
4381 l_msg_data VARCHAR2(32767);
4382 l_validation_level NUMBER ;
4383
4384 l_resource_id NUMBER ;
4385 l_count NUMBER :=0;
4386 l_found BOOLEAN := TRUE;
4387 -- Changed for the bug#7656223 by PNAVEENK on 2-1-2009
4388 cursor c_get_person_id (l_person_id NUMBER,
4389 l_competence_id NUMBER)is
4390 select count(person_id)
4391 from per_competence_elements
4392 where competence_id =l_competence_id
4393 and person_id =l_person_id
4394 and trunc(NVL(effective_date_to,SYSDATE)) >= trunc(sysdate)
4395 and trunc(effective_date_from) <= trunc(sysdate) ;
4396 -- End for bug#7656223
4397
4398 BEGIN
4399 -- initialize variable
4400 l_vTest := 'Instance time';
4401 l_init_msg_list := FND_API.G_TRUE;
4402 l_commit := FND_API.G_FALSE;
4403 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
4404 l_resource_id := nvl(fnd_profile.value('IEX_STRY_DEFAULT_RESOURCE'),0);
4405
4406 iex_debug_pub.logmessage ('get_account_resource: Test variable is: ' || l_vTest);
4407
4408 l_vTest := 'Runtime Changed';
4409
4410 -- IF PG_DEBUG < 10 THEN
4411 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4412 iex_debug_pub.logmessage ('get_account_resource: ' || '**** BEGIN get_billto_resource ************');
4413 iex_debug_pub.logmessage ('default resource id from profile iex_stry_default_resource) ' || l_resource_id);
4414 iex_debug_pub.logmessage ('calling get_account_resources ' || p_account_id);
4415 END IF;
4416 -- get resource id table of reords for the given site use id
4417 -- the record has resource id and person id along with the user name
4418 --Begin bug#5373412 schekuri 10-Jul-2006
4419 --Call new consolidated procedure get_assigned_collector
4420 iex_utilities.get_assigned_collector(p_api_version => l_api_version,
4421 p_init_msg_list => FND_API.G_TRUE,
4422 p_commit => FND_API.G_FALSE,
4423 p_validation_level => l_validation_level,
4424 p_level => 'ACCOUNT',
4425 p_level_id => p_account_id,
4426 x_msg_count => l_msg_count,
4427 x_msg_data => l_msg_data,
4428 x_return_status => l_return_status,
4429 x_resource_tab => l_resource_tab);
4430 /*iex_utilities.get_assign_account_resources(p_api_version => l_api_version,
4431 p_init_msg_list => FND_API.G_TRUE,
4432 p_commit => FND_API.G_FALSE,
4433 p_validation_level => l_validation_level,
4434 x_msg_count => l_msg_count,
4435 x_msg_data => l_msg_data,
4436 x_return_status => l_return_status,
4437 p_account_id => p_account_id,
4438 x_resource_tab => l_resource_tab);*/
4439 --End bug#5373412 schekuri 10-Jul-2006
4440 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4441 iex_debug_pub.logmessage('get_billto_resource: ' || 'in get resource and l_return_status = '||l_return_status);
4442 iex_debug_pub.logmessage('in get resource and l_return_status from iex_utilities.get_assign_account_resources = '||l_return_status);
4443 iex_debug_pub.logmessage('resource count from iex_utilities.get_assign_account_resources = '||l_resource_tab.count);
4444 END IF;
4445
4446 if l_resource_tab.count > 0 then
4447 l_bReturn := true;
4448 end if;
4449 -- if COMPETENCE id exists for the given work template Id,
4450 -- see if the person id from the
4451 -- the above l_resource_tab matches with the competence Id
4452 -- pick if there is match or pick any resource if there is no match
4453 -- or competence id of the work template id is null
4454
4455
4456 --if p_competence_id IS NULL THEN
4457 if p_competence_tab.count = 0 THEN
4458 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4459 iex_debug_pub.logmessage('get_account_resource: ' || 'Competence table is empty');
4460 END IF;
4461 --get the first resource id if competence id is null from
4462 -- the work item template
4463 FOR i in 1..l_resource_tab.count LOOP
4464 l_resource_id := l_resource_tab(i).resource_id;
4465 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4466 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
4467 END IF;
4468 EXIT;
4469 END LOOP;
4470 else
4471 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4472 iex_debug_pub.logmessage('Loop for matching competence. count = '||p_competence_tab.count );
4473 END IF;
4474 FOR i in 1..l_resource_tab.count LOOP
4475 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4476 iex_debug_pub.logmessage('get_account_resource: ' || 'PERSON ID is '||l_resource_tab(i).person_id);
4477 iex_debug_pub.logmessage('get_account_resource: ' || 'RESOURCE ID is '||l_resource_tab(i).resource_id);
4478 END IF;
4479
4480 FOR j in 1..p_competence_tab.count LOOP
4481
4482 OPEN c_get_person_id (l_resource_tab(i).person_id,
4483 p_competence_tab(j));
4484 FETCH c_get_person_id INTO l_count;
4485 CLOSE c_get_person_id;
4486 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4487 iex_debug_pub.logmessage('get_account_resource: ' || 'COMPETENCE ID is '||
4488 p_competence_tab(j));
4489 END IF;
4490 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4491 iex_debug_pub.logmessage('get_account_resource: ' || 'no of matches '|| l_count);
4492 END IF;
4493 If l_count =0 THEN
4494 -- match not found, use the first resource and exit out NOCOPY
4495 -- from the competence loop.
4496 --Begin bug#5373412 schekuri 10-Jul-2006
4497 --Commented the below the code to return default resource id instead of first resource id
4498 --when there is no resource found matching the competency of the workitem.
4499 /*l_resource_id := l_resource_tab(1).resource_id;
4500 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4501 iex_debug_pub.logmessage('1st record from l_resource_tab l_resource_id = '|| l_resource_id);
4502 END IF;*/
4503 --End bug#5373412 schekuri 10-Jul-2006
4504 -- have to look for the next resource if l_found is false
4505 l_found :=FALSE;
4506 EXIT;
4507 ELSE
4508 l_resource_id := l_resource_tab(i).resource_id;
4509 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4510 iex_debug_pub.logmessage('1st record found with competence matched l_resource_tab l_resource_id = '|| l_resource_id);
4511 END IF;
4512 l_found :=TRUE;
4513 End if;
4514 END LOOP;
4515 if l_found THEN
4516 -- a matching resource with all the competencies
4517 --have been found ,stop looking for next resource
4518 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4519 iex_debug_pub.logmessage('get_account_resource: ' || 'match found and RESOURCE ID is =>'
4520 ||l_resource_tab(i).resource_id);
4521 END IF;
4522 exit;
4523 end if;
4524 END LOOP;
4525 end if;
4526 --assign out NOCOPY variable
4527 x_resource_id :=l_resource_id;
4528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4529 iex_debug_pub.logmessage('get_account_resource: ' || 'value of x_resource_id' ||x_resource_id);
4530 END IF;
4531 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4532 iex_debug_pub.logmessage ('get_account_resource: ' || '**** END get_account_resource ************');
4533 END IF;
4534
4535 return l_bReturn;
4536 END get_account_resource;
4537
4538 --Begin - schekuri - 03-Dec-2005 - bug#4506922
4539 --to make the wf wait, if the status of strategy is ONHOLD
4540 --after 23 hrs. it rechecks the status
4541 procedure wait_on_hold_signal(
4542 itemtype in varchar2,
4543 itemkey in varchar2,
4544 actid in number,
4545 funcmode in varchar2,
4546 result out nocopy varchar2) IS
4547
4548 l_work_item_temp_id NUMBER;
4549 l_result VARCHAR2(1);
4550 l_value VARCHAR2(300);
4551
4552 BEGIN
4553 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4554 iex_debug_pub.logmessage ('**** START wait_on_hold_signal ************');
4555 END IF;
4556 if funcmode <> wf_engine.eng_run then
4557 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4558 iex_debug_pub.logmessage('SECOND TIME FUNCMODE' ||funcmode);
4559 END IF;
4560 result := wf_engine.eng_null;
4561 return;
4562 end if;
4563
4564
4565
4566 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4567 iex_debug_pub.logmessage('FUNCMODE' ||funcmode);
4568 END IF;
4569 l_value :=wf_engine.GetActivityLabel(actid);
4570 wf_engine.SetItemAttrText(itemtype => itemtype,
4571 itemkey => itemkey,
4572 aname => 'ACTIVITY_NAME',
4573 avalue => l_value);
4574 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4575 iex_debug_pub.logMessage('ACTIVITYNAME' ||l_value);
4576 END IF;
4577
4578
4579 result := wf_engine.eng_notified||':'||wf_engine.eng_null||
4580 ':'||wf_engine.eng_null;
4581 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4582 iex_debug_pub.logmessage ('**** END wait_on_hold_signal ************');
4583 END IF;
4584 exception
4585 when others then
4586 result := wf_engine.eng_completed ||':'||wf_no;
4587 wf_core.context('IEX_STRATEGY_WF','wait_on_hold_signal',itemtype,
4588 itemkey,to_char(actid),funcmode);
4589 raise;
4590
4591 END wait_on_hold_signal;
4592 --End - schekuri - 03-Dec-2005 - bug#4506922
4593
4594 --Begin - kasreeni- 11-03-2005 - Bug# 4667500
4595 -- procedure to update the workitem to open
4596 -- if the strategy is cancelled, we don't have to do anything
4597 /* Begin 05-dec-2005 schekuri bug#4506922 - All work items are created as PRE-WAIT */
4598 procedure update_work_item_to_open(
4599 itemtype in varchar2,
4600 itemkey in varchar2,
4601 actid in number,
4602 funcmode in varchar2,
4603 result out NOCOPY varchar2) IS
4604 L_STATUS_CODE varchar2(30);
4605 l_work_item_id NUMBER(30);
4606 l_result VARCHAR2(5);
4607 l_value VARCHAR2(300);
4608 l_strategy_status VARCHAR2(50);
4609
4610 BEGIN
4611
4612 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4613 iex_debug_pub.logmessage ('**** START update_work_item_to_open ************');
4614 END IF;
4615 if funcmode <> wf_engine.eng_run then
4616 result := wf_engine.eng_null;
4617 return;
4618 end if;
4619
4620 l_work_item_id := wf_engine.GetItemAttrNumber(
4621 itemtype => itemtype,
4622 itemkey => itemkey,
4623 aname => 'WORK_ITEMID');
4624
4625 l_strategy_status := wf_engine.GetItemAttrText(
4626 itemtype => itemtype,
4627 itemkey => itemkey,
4628 aname => 'STRATEGY_STATUS');
4629
4630 iex_debug_pub.logMessage('Got the work item ' || itemtype || ' item key ' || itemkey
4631 || ' l_work_item_ID ' || l_work_item_id || ' Strategy_status ' || l_strategy_status);
4632
4633 if l_strategy_status in ('CLOSED','CANCELLED') then
4634 result := wf_engine.eng_completed ||':'||wf_yes;
4635 return;
4636 end if;
4637
4638 if (l_work_item_id is not null ) then
4639
4640 SELECT STATUS_CODE INTO l_status_code FROM IEX_STRATEGY_WORK_ITEMS WHERE WORK_ITEM_ID = l_work_item_id;
4641
4642 iex_debug_pub.logMessage('Got the Status ' || l_status_code );
4643
4644 if (l_status_code = 'PRE-WAIT') THEN
4645
4646 UPDATE IEX_STRATEGY_WORK_ITEMS SET STATUS_CODE = 'OPEN' WHERE WORK_ITEM_ID = l_work_item_id;
4647 END IF;
4648 end if;
4649
4650 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4651 iex_debug_pub.logmessage ('**** END update_work_item_to_open ************');
4652 END IF;
4653
4654 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4655 iex_debug_pub.logmessage('FUNCMODE ' ||funcmode);
4656 END IF;
4657
4658 l_value :=wf_engine.GetActivityLabel(actid);
4659
4660 wf_engine.SetItemAttrText(itemtype => itemtype,
4661 itemkey => itemkey,
4662 aname => 'ACTIVITY_NAME',
4663 avalue => l_value);
4664
4665 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4666 iex_debug_pub.logMessage('ACTIVITYNAME ' ||l_value);
4667 END IF;
4668
4669
4670 result := wf_engine.eng_completed ||':'||wf_yes;
4671
4672 exception
4673 when others then
4674 iex_debug_pub.logmessage ('**** EXCEPTION update_work_item_to_open ************');
4675 result := wf_engine.eng_completed ||':'||wf_no;
4676 wf_core.context('IEX_STRATEGY_WF','wait_on_hold_signal',itemtype,
4677 itemkey,to_char(actid),funcmode);
4678 raise;
4679 END;
4680
4681 --End - 05-dec-2005 schekuri bug#4506922
4682
4683 procedure UPDATE_ESC_FLAG(
4684 itemtype in varchar2,
4685 itemkey in varchar2,
4686 actid in number,
4687 funcmode in varchar2,
4688 result out NOCOPY varchar2) IS
4689 l_api_version_number NUMBER := 2.0;
4690 l_return_status VARCHAR2(1);
4691 l_msg_count NUMBER;
4692 l_msg_data VARCHAR2(32767);
4693 l_work_item_id NUMBER;
4694 l_party_id NUMBER;
4695 exc EXCEPTION;
4696 l_error VARCHAR2(32767);
4697 l_object_version_number number;
4698 l_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type;
4699 l_status_code iex_strategy_work_items.status_code%type;
4700 Cursor c_get_work_items (p_work_item_id NUMBER) is
4701 SELECT status_code, object_version_number
4702 FROM iex_strategy_work_items
4703 WHERE work_item_id = p_work_item_id;
4704 l_resource_id number; -- added for bug 8919933 PNAVEENK
4705 BEGIN
4706 -- IF PG_DEBUG < 10 THEN
4707 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4708 iex_debug_pub.logmessage ('**** START UPDATE_ESCALATION_FLAG ************');
4709 END IF;
4710 if funcmode <> 'RUN' then
4711 result := wf_engine.eng_null;
4712 return;
4713 end if;
4714
4715 l_work_item_id := wf_engine.GetItemAttrText(
4716 itemtype => itemtype,
4717 itemkey => itemkey,
4718 aname => 'WORK_ITEMID');
4719 l_party_id := wf_engine.GetItemAttrText(
4720 itemtype => itemtype,
4721 itemkey => itemkey,
4722 aname => 'PARTY_ID');
4723 -- IF PG_DEBUG < 10 THEN
4724 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4725 iex_debug_pub.logmessage('UPDATE_ESC_FLAG: ' || 'value of workitem id '||l_work_item_id);
4726 END IF;
4727 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4728 iex_debug_pub.logmessage ('UPDATE_ESC_FLAG: ' || 'Updating the work item..');
4729 END IF;
4730 open c_get_work_items(l_work_item_id);
4731 fetch c_get_work_items into l_status_code,l_object_version_number;
4732 close c_get_work_items;
4733 l_strategy_work_item_Rec.work_item_id := l_work_item_id;
4734 l_strategy_work_item_Rec.object_version_number :=l_object_version_number;
4735 l_strategy_work_item_Rec.escalated_yn := 'Y';
4736 IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
4737 P_Api_Version_Number =>l_api_version_number,
4738 P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
4739 P_Init_Msg_List =>FND_API.G_TRUE,
4740 p_commit =>FND_API.G_TRUE,
4741 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4742 x_msg_count => l_msg_count,
4743 x_msg_data => l_msg_data,
4744 x_return_status => l_return_status,
4745 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
4746 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4747 iex_debug_pub.logmessage ('UPDATE_ESC_FLAG: Return status = ' || l_return_status);
4748 END IF;
4749 if l_return_status =FND_API.G_RET_STS_SUCCESS THEN
4750 begin
4751 iex_debug_pub.logmessage('UPDATE_ESC_FLAG: Refreshing UWQ Summary..');
4752 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_work_item_id);
4753 exception
4754 when others then
4755 iex_debug_pub.logmessage('UPDATE_ESC_FLAG:Exception when refreshing UWQ Summary: '||SQLERRM);
4756 end;
4757
4758 result := wf_engine.eng_completed ||':'||wf_yes;
4759 else
4760 RAISE EXC;
4761 END IF;
4762 -- IF PG_DEBUG < 10 THEN
4763 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4764 iex_debug_pub.logmessage ('**** END UPDATE_ESC_FLAG ************');
4765 END IF;
4766
4767 -- start for bug 8919933 PNAVEENK
4768
4769 Begin
4770 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4771 iex_debug_pub.logmessage ('**** START UPDATE_NOTIFICATION_RESOURCE ************');
4772 END IF;
4773
4774
4775 if (l_work_item_id is not null ) then
4776
4777 SELECT resource_id INTO l_resource_id FROM IEX_STRATEGY_WORK_ITEMS WHERE WORK_ITEM_ID = l_work_item_id;
4778
4779 end if;
4780 iex_debug_pub.logMessage('Current resource id ' || l_resource_id );
4781
4782 set_notification_resources(l_resource_id, itemtype, itemkey);
4783
4784 result := wf_engine.eng_completed ||':'||wf_yes;
4785
4786 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4787 iex_debug_pub.logmessage ('**** END UPDATE_NOTIFICATION_RESOURCE ************');
4788 END IF;
4789
4790 exception
4791 when others then
4792 iex_debug_pub.logmessage('UPDATE_ESC_FLAG:Exception UPDATE NOTIFICATION RESOURCE '||SQLERRM);
4793 end;
4794
4795 -- end for bug 8919933
4796
4797
4798 exception
4799 WHEN EXC THEN
4800 --pass the error message
4801 -- get error message and passit pass it
4802 Get_Messages(l_msg_count,l_error);
4803 -- IF PG_DEBUG < 10 THEN
4804 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
4805 iex_debug_pub.logmessage('UPDATE_ESC_FLAG: ' || 'error message is ' || l_error);
4806 END IF;
4807 wf_core.context('IEX_STRATEGY_WF','UPDATE_ESC_FLAG',itemtype,
4808 itemkey,to_char(actid),funcmode,l_error);
4809 raise;
4810 when others then
4811 wf_core.context('IEX_STRATEGY_WF','UPDATE_ESC_FLAG',itemtype,
4812 itemkey,to_char(actid),funcmode);
4813 raise;
4814 END UPDATE_ESC_FLAG;
4815
4816
4817 begin
4818 -- initialize variable
4819 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4820 wf_yes := 'Y';
4821 wf_no := 'N';
4822 end IEX_STRATEGY_WF;
[Home] [Help]