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