1 PACKAGE BODY IEX_STRY_API_PUB as
2 /* $Header: iexpsapb.pls 120.6.12010000.1 2008/07/29 10:02:52 appldev ship $ */
3 -- Start of Comments
4 -- Package name : IEX_STRY_API_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(100):= 'IEX_STRY_API_PUB ';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpsapb.pls';
12 G_USER_ID NUMBER ;
13
14
15 /**Name AddInvalidArgMsg
16 **Appends to a message the api name, parameter name and parameter Value
17 */
18
19 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
20 PG_DEBUG NUMBER ;
21
22 PROCEDURE AddInvalidArgMsg
23 ( p_api_name IN VARCHAR2,
24 p_param_value IN VARCHAR2,
25 p_param_name IN VARCHAR2 ) IS
26 BEGIN
27 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
28 fnd_message.set_name('IEX', 'IEX_API_ALL_INVALID_ARGUMENT');
29 fnd_message.set_token('API_NAME', p_api_name);
30 fnd_message.set_token('VALUE', p_param_value);
31 fnd_message.set_token('PARAMETER', p_param_name);
32 fnd_msg_pub.add;
33 END IF;
34
35
36 END AddInvalidArgMsg;
37
38 /**Name AddMissingArgMsg
39 **Appends to a message the api name, parameter name and parameter Value
40 */
41
42 PROCEDURE AddMissingArgMsg
43 ( p_api_name IN VARCHAR2,
44 p_param_name IN VARCHAR2 )IS
45 BEGIN
46 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
47 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
48 fnd_message.set_token('API_NAME', p_api_name);
49 fnd_message.set_token('MISSING_PARAM', p_param_name);
50 fnd_msg_pub.add;
51 END IF;
52 END AddMissingArgMsg;
53
54 /**Name AddNullArgMsg
55 **Appends to a message the api name, parameter name and parameter Value
56 */
57
58 PROCEDURE AddNullArgMsg
59 ( p_api_name IN VARCHAR2,
60 p_param_name IN VARCHAR2 )IS
61 BEGIN
62 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
63 fnd_message.set_name('IEX', 'IEX_API_ALL_NULL_PARAMETER');
64 fnd_message.set_token('API_NAME', p_api_name);
65 fnd_message.set_token('NULL_PARAM', p_param_name);
66 fnd_msg_pub.add;
67 END IF;
68
69
70 END AddNullArgMsg;
71
72 /**Name AddFailMsg
73 **Appends to a message the name of the object anf the operation (insert, update ,delete)
74 */
75 PROCEDURE AddfailMsg
76 ( p_object IN VARCHAR2,
77 p_operation IN VARCHAR2 ) IS
78
79 BEGIN
80 fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
81 fnd_message.set_token('OBJECT', p_object);
82 fnd_message.set_token('OPERATION', p_operation);
83 fnd_msg_pub.add;
84
85 END AddfailMsg;
86
87
88
89 /** reassagin strategy
90 * send signal first
91 * then call create_Strategy_pub
92 * to create the new strategy
93 * the new strategy will launch the work flow*
94 **/
95 PROCEDURE REASSIGN_STRATEGY(
96 p_strategy_temp_id IN NUMBER,
97 p_strategy_id IN NUMBER,
98 p_status IN VARCHAR2,
99 p_commit IN VARCHAR2,
100 x_return_status OUT NOCOPY VARCHAR2,
101 x_msg_count OUT NOCOPY NUMBER,
102 x_msg_data OUT NOCOPY VARCHAR2) IS
103
104 l_object_type varchar2(30) ;
105 l_object_id number ;
106 l_delinquency_id number ;
107 l_return_status VARCHAR2(1);
108 l_msg_count NUMBER;
109 l_msg_data VARCHAR2(32767);
110 l_status varchar2(100) ;
111
112 cursor c_status(p_strategy_id IN NUMBER) is
113 select status_code
114 from iex_strategies
115 where strategy_id = p_strategy_id ;
116
117 cursor c_object(p_strategy_id IN NUMBER) is
118 select delinquency_id,object_id,object_type
119 from iex_strategies
120 where strategy_id = p_strategy_id ;
121
122 BEGIN
123
124 SAVEPOINT REASSIGN_STRATEGY_PUB;
125
126 x_return_status := FND_API.G_RET_STS_ERROR;
127
128 l_object_type := 'DELINQUENT' ;
129
130 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
131 strategy_id => p_strategy_id,
132 status => p_status ) ;
133
134 -- IF PG_DEBUG < 10 THEN
135 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
136 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'AFTER SEND SIGNAL');
137 END IF;
138
139 OPEN c_status(p_strategy_id);
140 FETCH c_status INTO l_status;
141 CLOSE c_status;
142
143 if ( l_status = 'CANCELLED' ) then
144 -- IF PG_DEBUG < 10 THEN
145 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
146 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'After Send Signal and it successfull ');
147 END IF;
148 OPEN c_object(p_strategy_id);
149 FETCH c_object INTO l_delinquency_id,l_object_id,l_object_type;
150 CLOSE c_object;
151
152 -- IF PG_DEBUG < 10 THEN
153 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
154 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
155 'before calling create strategy is '
156 ||l_delinquency_id||
157 ' object_id is '||l_object_id ||
158 ' object_type is' || l_object_type );
159 END IF;
160
161 -- IF PG_DEBUG < 10 THEN
162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
163 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
164 END IF;
165
166 IEX_STRATEGY_PUB.CREATE_STRATEGY
167 (p_api_version_number => 2.0,
168 p_init_msg_list => FND_API.G_TRUE,
169 p_commit => p_commit,
170 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
171 p_delinquencyId => l_delinquency_id ,
172 p_objecttype => l_object_type,
173 p_objectid => l_object_id ,
174 x_return_status => l_return_status,
175 x_msg_count => l_msg_count,
176 x_msg_data => l_msg_data,
177 p_strategy_temp_id => p_strategy_temp_id) ;
178
179 -- IF PG_DEBUG < 10 THEN
180 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
181 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
182 END IF;
183
184 x_msg_count :=l_msg_count;
185 x_msg_data :=l_msg_data;
186 x_return_status :=l_return_status;
187
188
189 ELSE
190 ROLLBACK TO REASSIGN_STRATEGY_PUB;
191 RETURN;
192 END if; --l_status =cancelled
193
194 -- Standard check of p_commit
195 IF FND_API.To_Boolean(p_commit) THEN
196 COMMIT WORK;
197 END IF;
198
199 EXCEPTION
200 WHEN OTHERS THEN
201 ROLLBACK TO REASSIGN_STRATEGY_PUB;
202 -- IF PG_DEBUG < 10 THEN
203 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
204 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
205 END IF;
206 END REASSIGN_STRATEGY;
207
208 --Start bug 6794510 gnramasa 5th Feb 08
209 /** assign strategy
210 * call create_Strategy_pub
211 * to create the new strategy
212 * the new strategy will launch the work flow*
213 **/
214
215 PROCEDURE ASSIGN_STRATEGY( p_strategy_temp_id IN NUMBER,
216 p_objectid IN NUMBER,
217 p_objecttype IN VARCHAR2,
218 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2) IS
222
223 l_delinquency_id number ;
224 l_return_status VARCHAR2(1);
225 l_msg_count NUMBER;
226 l_msg_data VARCHAR2(32767);
227 l_status varchar2(100) ;
228
229 BEGIN
230
231 SAVEPOINT ASSIGN_STRATEGY_PUB;
232
233 x_return_status := FND_API.G_RET_STS_ERROR;
234
235 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
236 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
237 'before calling create strategy is '
238 ||l_delinquency_id||
239 ' object_id is '||p_objectid ||
240 ' object_type is' || p_objecttype );
241 END IF;
242
243 -- IF PG_DEBUG < 10 THEN
244 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
245 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
246 END IF;
247
248 IEX_STRATEGY_PUB.CREATE_STRATEGY
249 (p_api_version_number => 2.0,
250 p_init_msg_list => FND_API.G_TRUE,
251 p_commit => p_commit,
252 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
253 p_delinquencyId => l_delinquency_id ,
254 p_objecttype => p_objecttype,
255 p_objectid => p_objectid ,
256 x_return_status => l_return_status,
257 x_msg_count => l_msg_count,
258 x_msg_data => l_msg_data,
259 p_strategy_temp_id => p_strategy_temp_id) ;
260
261 -- IF PG_DEBUG < 10 THEN
262 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
263 iex_debug_pub.logmessage('ASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
264 END IF;
265
266 x_msg_count :=l_msg_count;
267 x_msg_data :=l_msg_data;
268 x_return_status :=l_return_status;
269
270
271 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
272 ROLLBACK TO ASSIGN_STRATEGY_PUB;
273 RETURN;
274 END if; --l_status =cancelled
275
276 -- Standard check of p_commit
277 IF FND_API.To_Boolean(p_commit) THEN
278 COMMIT WORK;
279 END IF;
280
281 EXCEPTION
282 WHEN OTHERS THEN
283 ROLLBACK TO ASSIGN_STRATEGY_PUB;
284 -- IF PG_DEBUG < 10 THEN
285 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
286 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
287 END IF;
288 END ASSIGN_STRATEGY;
289 --End bug 6794510 gnramasa 5th Feb 08
290
291 /** update work item and call send signal
292 * if send signal fails, roolback the work item
293 **/
294
295 PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec IN
296 iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
297 p_commit IN VARCHAR2,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2)IS
301
302 l_return_status VARCHAR2(1);
303 l_msg_count NUMBER;
304 l_msg_data VARCHAR2(32767);
305 l_status varchar2(100) ;
306 l_object_version_number NUMBER;
307 v_result NUMBER;
308 l_status_code varchar2(100) ;
309 BEGIN
310
311 SAVEPOINT UPDATE_AND_SENDSIGNAL;
312
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314
315 begin
316 select status_code INTO l_status_code from iex_strategy_work_items
317 where strategy_id = p_strategy_work_item_rec.strategy_id
318 and work_item_id = p_strategy_work_item_rec.work_item_id;
319
320 if (l_status_code not in ('OPEN', 'ONHOLD','PRE-WAIT')) then --Added PRE-WAIT for bug#5474793 by schekuri on 21-Aug-2006
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
323 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not in status OPEN or ONHOLD');
324 end if;
325 return;
326 end if;
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 x_return_status := FND_API.G_RET_STS_ERROR;
331 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
332 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not found');
333 end if;
334 return;
335 end;
336
337 iex_strategy_work_items_pvt.update_strategy_work_items(
338 p_api_version_number => 2.0,
339 p_init_msg_list => FND_API.G_TRUE,
340 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
341 p_commit => p_commit,
342 x_return_status => l_return_status,
343 x_msg_count => l_msg_count,
344 x_msg_data => l_msg_data,
345 p_strategy_work_item_rec => p_strategy_work_item_rec,
346 xo_object_version_number => l_object_version_number);
347
348 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
349 x_msg_count :=l_msg_count;
350 x_msg_data :=l_msg_data;
351 x_return_status :=l_return_status;
352 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
353 return;
354 ELSE
355 --call send signal
356 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
357 strategy_id => p_strategy_work_item_rec.strategy_id,
358 status => p_strategy_work_item_rec.status_code,
359 work_item_id => p_strategy_work_item_rec.work_item_id);
360
361 -- IF PG_DEBUG < 10 THEN
362 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
363 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
364 END IF;
365 --check if it the strategy is open
366 -- and next work item is not the same as the updated work item
367 -- then the send signal has been successful and it has created
368 -- the next work item . other wise, the send signal failed.
369 -- id send signal is successful, commit , else rollback
370 /* comment out by kali and ctlee
371 select count(*) INTO v_result from iex_strategies
372 where strategy_id =p_strategy_work_item_rec.strategy_id
373 and next_work_item_id =p_strategy_work_item_rec.work_item_id
374 and status_code ='OPEN';
375
376 if v_result >0 THEN
377 -- IF PG_DEBUG < 10 THEN
378 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
379 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
380 END IF;
381 rollback to UPDATE_AND_SENDSIGNAL;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 return;
384 else
385 -- IF PG_DEBUG < 10 THEN
386 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
387 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
388 END IF;
389 end if;
390 */
391 END IF; --if status is successful
392
393 -- Standard check of p_commit
394 IF FND_API.To_Boolean(p_commit) THEN
395 COMMIT WORK;
396 END IF;
397
398 EXCEPTION
399 WHEN OTHERS THEN
400 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
401 -- IF PG_DEBUG < 10 THEN
402 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
403 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
404 END IF;
405
406 END UPDATE_AND_SENDSIGNAL;
407
408 /** update work item and call send signal
409 * if send signal fails, roolback the work item
410 * this is called from the JSP page , so passing
411 * columns instead of record type
412 * temporary fix till rosetta is fixed
413 * if the status is not changed to 'CLOSED'
414 * 'CANCELLED' THEN just update the work item
415 * do not call send signal
416 *06/21/02 --jsanju
417 **/
418
419 PROCEDURE UPDATE_AND_SENDSIGNAL(p_status IN VARCHAR2
420 ,p_work_item_id IN NUMBER
421 ,p_resource_id IN NUMBER
422 ,p_execute_start IN DATE
423 ,p_execute_end IN DATE
424 ,p_commit IN VARCHAR2
425 ,x_return_status OUT NOCOPY VARCHAR2
426 ,x_msg_count OUT NOCOPY NUMBER
427 ,x_msg_data OUT NOCOPY VARCHAR2) IS
428 l_return_status VARCHAR2(1);
429 l_msg_count NUMBER;
430 l_msg_data VARCHAR2(32767);
431 l_status varchar2(1) ;
432 l_version_number NUMBER;
433 v_result NUMBER;
434 l_strategy_work_item_rec
435 IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
436 l_strategy_id number ;
437
438 cursor c_work_item(p_work_item_id NUMBER) is
439 select object_version_number,strategy_id
440 from iex_strategy_work_items
441 where work_item_id = p_work_item_id ;
442
443 BEGIN
444 -- IF PG_DEBUG < 10 THEN
445 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
446 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'START UPDATE AND SEND SIGNAL');
447 END IF;
448 SAVEPOINT UPDATE_AND_SENDSIGNAL;
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450
451 OPEN c_work_item(p_work_item_id);
452 fetch c_work_item into l_version_number,l_strategy_id;
453 close c_work_item;
454
455 l_strategy_work_item_rec.work_item_id := p_work_item_id ;
456 l_strategy_work_item_rec.object_version_number := l_version_number ;
457 l_strategy_work_item_rec.execute_start := p_execute_start ;
458 l_strategy_work_item_rec.execute_end := p_execute_end ;
459 l_strategy_work_item_rec.status_code := p_status ;
460 l_strategy_work_item_rec.resource_id :=p_resource_id;
461 l_strategy_work_item_rec.strategy_id :=l_strategy_id;
462
463 -- IF PG_DEBUG < 10 THEN
464 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
465 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');
466 END IF;
467 iex_strategy_work_items_pvt.update_strategy_work_items(
468 p_api_version_number => 2.0,
469 p_init_msg_list => FND_API.G_TRUE,
470 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
471 p_commit => FND_API.G_FALSE,
472 x_return_status => l_return_status,
473 x_msg_count => l_msg_count,
474 x_msg_data => l_msg_data,
475 p_strategy_work_item_rec => l_strategy_work_item_rec,
476 xo_object_version_number => l_version_number);
477 -- IF PG_DEBUG < 10 THEN
478 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
479 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'Status of work item update ' ||l_return_status);
480 END IF;
481
482 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
483 x_msg_count :=l_msg_count;
484 x_msg_data :=l_msg_data;
485 x_return_status :=l_return_status;
486 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
487 return;
488 ELSE
489 --call send signal
490 --only if status = 'CLOSED or 'CANCELLED'
491 IF p_status IN ('CLOSED','CANCELLED','COMPLETE') THEN
492 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
493 strategy_id => l_strategy_work_item_rec.strategy_id,
494 status => l_strategy_work_item_rec.status_code,
495 work_item_id =>l_strategy_work_item_rec.work_item_id);
496
497 -- IF PG_DEBUG < 10 THEN
498 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
499 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
500 END IF;
501 --check if it the strategy is open
502 -- and next work item is not the same as the updated work item
503 -- then the send signal has been successful and it has created
504 -- the next work item . other wise, the send signal failed.
505 -- id send signal is successful, commit , else rollback
506 /* comment out by kali and ctlee
507 select count(*) INTO v_result from iex_strategies
508 where strategy_id =l_strategy_work_item_rec.strategy_id
509 and next_work_item_id =l_strategy_work_item_rec.work_item_id
510 and status_code ='OPEN';
511
512 if v_result >0 THEN
513 -- IF PG_DEBUG < 10 THEN
514 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
515 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
516 END IF;
517 rollback to UPDATE_AND_SENDSIGNAL;
518 x_return_status := FND_API.G_RET_STS_ERROR;
519 return;
520 else
521 -- IF PG_DEBUG < 10 THEN
522 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
523 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
524 END IF;
525 end if;
526 */
527 END IF ; -- p_status in 'closed' or cancelled'
528 END IF; --if status is successful
529
530 -- Standard check of p_commit
531 IF FND_API.To_Boolean(p_commit) THEN
532 COMMIT WORK;
533 END IF;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
538 -- IF PG_DEBUG < 10 THEN
539 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
540 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
541 END IF;
542
543
544 END UPDATE_AND_SENDSIGNAL;
545
546 --06/27
547 --this procedure check the status of the workflow
548 --will be called before "changing the strategy"
549 -- " update and skip to next work item"
550 -- if the work flow is in error, then
551 --display on the screen that the work flow is in error
552 --along with the error_message attribute
553 --if the workflow is not suspended and
554 --if the activity_name is null( that means
555 --it is not a escalation or optional task)
556 -- there has been a error. display message.
557 PROCEDURE CHECK_STRATEGY_WORKFLOW ( p_strategy IN NUMBER
558 ,x_return_status OUT NOCOPY VARCHAR2
559 ,x_return_message OUT NOCOPY VARCHAR2
560 ,x_wf_status OUT NOCOPY VARCHAR2) IS
561
562 l_result VARCHAR2(10);
563 l_return_status VARCHAR2(20);
564 l_activity_name VARCHAR2(100);
565 l_wf_error VARCHAR2(32627);
566 cursor c_get_Wf_error (p_strategy IN NUMBER)is
567 select
568 --ias.activity_result_code Result,
569 -- ias.error_name ERROR_NAME,
570 ias.error_message ERROR_MESSAGE
571 -- ,ias.error_stack ERROR_STACK
572 from wf_item_activity_statuses ias
573 where
574 ias.item_type ='IEXSTRY'
575 and ias.item_key =p_strategy
576 and ias.activity_status = 'ERROR';
577
578 BEGIN
579 -- IF PG_DEBUG < 10 THEN
580 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
581 IEX_DEBUG_PUB.logmessage('Begin IEX_STRY_API_PUB.CHECK_STRATEGY_WORKFLOW' );
582 END IF;
583 --check status of the workflow
584 wf_engine.ItemStatus( itemtype => 'IEXSTRY',
585 itemkey => p_strategy,
586 status => l_return_status,
587 result => l_result);
588 x_wf_Status :=l_return_status;
589
590 --07/31/02
591 --get workflow error from wf_item_activity_statuses table
592 OPEN c_get_Wf_error (p_strategy);
593 FETCH c_get_Wf_error INTO l_wf_error;
594 CLOSE c_get_wf_error;
595 -- IF PG_DEBUG < 10 THEN
596 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
597 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'wf error is ' ||l_wf_error);
598 END IF;
599
600 if l_return_status = wf_engine.eng_error THEN
601 -- work flow is in error
602 --get the error message from the error_message attribute
603 x_return_message :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
604 itemkey => p_strategy,
605 aname => 'ERROR_MESSAGE');
606 x_return_status := 'E';
607
608 x_return_message := x_return_message || l_wf_error;
609 -- IF PG_DEBUG < 10 THEN
610 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
611 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'Work flow is in error for strategy Id'
612 ||p_strategy || 'error is ' ||x_return_message);
613 END IF;
614
615 elsif l_return_status =wf_engine.eng_active THEN
616 -- work flow is active
617 -- and is in error if the activity name is not populated
618 -- the activity name gets populated for optional and escalation work items
619 -- if it is in error then get the error message from the error_message attribute
620 -- this can happen if the work flow has not reached the node where it gets suspended
621 --could be due to many reason. these profiles might not be set
622 --IEX_STRY_MEATAPHOR_CREATION -- for uwq creation
623 --IEX_STRY_DEFAULT_RESOURCE --
624
625 l_activity_name :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
626 itemkey => p_strategy,
627 aname => 'ACTIVITY_NAME');
628 If l_activity_name is null then
629 -- IF PG_DEBUG < 10 THEN
630 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
631 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'l_activity_name is null ' );
632 END IF;
633 x_return_message :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
634 itemkey => p_strategy,
635 aname => 'ERROR_MESSAGE');
636
637 x_return_message := x_return_message || l_wf_error;
638 x_return_status := 'E';
639 -- IF PG_DEBUG < 10 THEN
640 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
641 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'Work flow is in error for strategy Id'
642 ||p_strategy || 'error is ' ||x_return_message);
643 END IF;
644 -- set status to success
645 -- cancel or complete optional or escalate work item
646 else
647 x_return_status := 'S';
648 end if;
649 else
650 -- the work flow is SUSPENDED or COMPLETE
651 -- the form doesn't all ow any changes if the
652 -- workflow is 'COMPLETE'
653 -- set status to complete.
654 x_return_status := 'S';
655 x_return_message := NULL;
656 end if;
657
658 -- IF PG_DEBUG < 10 THEN
659 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
660 IEX_DEBUG_PUB.logmessage('End IEX_STRY_API_PUB.CHECK_STRATEGY_WORKFLOW' );
661 END IF;
662
663 EXCEPTION
664 WHEN OTHERS THEN
665 -- IF PG_DEBUG < 10 THEN
666 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
667 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
668 END IF;
669 x_return_status := 'E';
670 x_return_message := sqlerrm;
671 x_wf_Status :=NULL;
672 END CHECK_STRATEGY_WORKFLOW;
673
674 PROCEDURE UPDATE_WORKITEM (p_status IN VARCHAR2
675 ,p_work_item_id IN NUMBER
676 ,p_resource_id IN NUMBER
677 ,p_execute_start IN DATE
678 ,p_execute_end IN DATE
679 ,p_commit IN VARCHAR2
680 ,x_return_status OUT NOCOPY VARCHAR2
681 ,x_msg_count OUT NOCOPY NUMBER
682 ,x_msg_data OUT NOCOPY VARCHAR2) IS
683
684
685 l_return_status VARCHAR2(1);
686 l_msg_count NUMBER;
687 l_msg_data VARCHAR2(32767);
688 l_status varchar2(1) ;
689 l_version_number NUMBER;
690 v_result NUMBER;
691 l_strategy_work_item_rec
692 IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
693 l_strategy_id number ;
694
695 cursor c_work_item(p_work_item_id NUMBER) is
696 select object_version_number,strategy_id
697 from iex_strategy_work_items
698 where work_item_id = p_work_item_id ;
699
700 BEGIN
701 -- IF PG_DEBUG < 10 THEN
702 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
703 IEX_DEBUG_PUB.logmessage('START UPDATE_WORKITEM');
704 END IF;
705 SAVEPOINT UPDATE_WORKITEM;
706 x_return_status := FND_API.G_RET_STS_SUCCESS;
707
708 OPEN c_work_item(p_work_item_id);
709 fetch c_work_item into l_version_number,l_strategy_id;
710 close c_work_item;
711
712 l_strategy_work_item_rec.work_item_id := p_work_item_id ;
713 l_strategy_work_item_rec.object_version_number := l_version_number ;
714 l_strategy_work_item_rec.execute_start := p_execute_start ;
715 l_strategy_work_item_rec.execute_end := p_execute_end ;
716 l_strategy_work_item_rec.status_code := p_status ;
717 l_strategy_work_item_rec.resource_id :=p_resource_id;
718 l_strategy_work_item_rec.strategy_id :=l_strategy_id;
719
720 -- IF PG_DEBUG < 10 THEN
721 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
722 IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');
723 END IF;
724 iex_strategy_work_items_pvt.update_strategy_work_items(
725 p_api_version_number => 2.0,
726 p_init_msg_list => FND_API.G_TRUE,
727 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
728 p_commit => FND_API.G_FALSE,
729 x_return_status => l_return_status,
730 x_msg_count => l_msg_count,
731 x_msg_data => l_msg_data,
732 p_strategy_work_item_rec => l_strategy_work_item_rec,
733 xo_object_version_number => l_version_number);
734 -- IF PG_DEBUG < 10 THEN
735 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
736 IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'Status of work item update ' ||l_return_status);
737 END IF;
738
739 -- Standard check of p_commit
740 IF FND_API.To_Boolean(p_commit) THEN
741 COMMIT WORK;
742 END IF;
743
744 EXCEPTION
745 WHEN OTHERS THEN
746 ROLLBACK TO UPDATE_WORKITEM;
747 -- IF PG_DEBUG < 10 THEN
748 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
749 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_WORKITEM '||sqlerrm);
750 END IF;
751
752 END UPDATE_WORKITEM;
753
754 /*********************
755 Set UWQ status for Strategy
756 ***********************/
757 PROCEDURE SHOW_IN_UWQ(
758 P_API_VERSION IN NUMBER,
759 P_INIT_MSG_LIST IN VARCHAR2,
760 P_COMMIT IN VARCHAR2,
761 P_VALIDATION_LEVEL IN NUMBER,
762 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
763 X_MSG_COUNT OUT NOCOPY NUMBER,
764 X_MSG_DATA OUT NOCOPY VARCHAR2,
765 P_WORK_ITEM_ID_TBL IN DBMS_SQL.NUMBER_TABLE,
766 P_UWQ_STATUS IN VARCHAR2,
767 P_NO_DAYS IN NUMBER)
768 IS
769 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_IN_UWQ';
770 l_api_version CONSTANT NUMBER := 1.0;
771 l_return_status varchar2(10);
772 l_msg_count number;
773 l_msg_data varchar2(200);
774
775 l_validation_item varchar2(100);
776 l_days NUMBER;
777 l_set_status_date DATE;
778 l_status varchar2(20);
779 nCount number;
780
781 Type refCur is Ref Cursor;
782 l_cursor refCur;
783 l_SQL VARCHAR2(10000);
784 l_broken_promises DBMS_SQL.NUMBER_TABLE;
785 i number;
786 j number;
787 l_uwq_active_date date;
788 l_uwq_complete_date date;
789
790 begin
791 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
792 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
793 END IF;
794
795 -- Standard start of API savepoint
796 SAVEPOINT SHOW_IN_UWQ_PVT;
797
798 -- Standard call to check for call compatibility
799 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
800 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
801 END IF;
802
803 -- Initialize message list if p_init_msg_list is set to TRUE
804 IF FND_API.To_Boolean(p_init_msg_list) THEN
805 FND_MSG_PUB.initialize;
806 END IF;
807
808 -- Initialize API return status to success
809 l_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 -- START OF BODY OF API
812
813 -- validating uwq status
814 l_validation_item := 'P_UWQ_STATUS';
815 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
816 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
817 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
818 END IF;
819 if P_UWQ_STATUS is null then
820 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
821 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
822 END IF;
823 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
824 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
825 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
826 FND_MSG_PUB.Add;
827 RAISE FND_API.G_EXC_ERROR;
828 end if;
829
830 -- validating table of promises
831 l_validation_item := 'P_WORK_ITEM_ID_TBL';
832 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
833 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_WORK_ITEM_ID_TBL: ' || P_WORK_ITEM_ID_TBL.count);
834 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
835 END IF;
836 if P_WORK_ITEM_ID_TBL.count = 0 then
837 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
838 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
839 END IF;
840 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
841 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
842 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
843 FND_MSG_PUB.Add;
844 RAISE FND_API.G_EXC_ERROR;
845 end if;
846
847 -- validating p_days
848 l_validation_item := 'P_NO_DAYS';
849 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
850 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
851 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
852 END IF;
853 if P_NO_DAYS is not null and P_NO_DAYS < 0 then
854 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
855 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
856 END IF;
857 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
858 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
859 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
860 FND_MSG_PUB.Add;
861 RAISE FND_API.G_EXC_ERROR;
862 end if;
863
864 -- set number of days
865 if P_NO_DAYS is null then
866 l_days := to_number(nvl(fnd_profile.value('IEX_UWQ_DEFAULT_PENDING_DAYS'), '0'));
867 else
868 l_days := P_NO_DAYS;
869 end if;
870 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
871 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
872 END IF;
873 l_set_status_date := sysdate + l_days;
874 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
875 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
876 END IF;
877
878 -- check for status
879 if P_UWQ_STATUS = 'ACTIVE' then
880 l_uwq_active_date := NULL;
881 l_uwq_complete_date := NULL;
882 elsif P_UWQ_STATUS = 'PENDING' then
883 l_uwq_active_date := l_set_status_date;
884 l_uwq_complete_date := NULL;
885 elsif P_UWQ_STATUS = 'COMPLETE' then
886 l_uwq_active_date := NULL;
887 l_uwq_complete_date := sysdate;
888 end if;
889
890 nCount := p_work_item_id_tbl.count;
891 if nCount > 0 then
892 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
893 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
894 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
895 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
896 END IF;
897 FORALL i in 1..nCount
898 UPDATE iex_strategy_work_items
899 SET uwq_status = p_uwq_status,
900 uwq_active_date = l_uwq_active_date,
901 uwq_complete_date = l_uwq_complete_date,
902 last_update_date = sysdate,
903 last_updated_by = g_user_id
904 where
905 work_item_id = p_work_item_id_tbl(i);
906 else
907 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
908 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
909 END IF;
910 end if;
911
912 -- END OF BODY OF API
913
914 -- Standard check of p_commit.
915 IF FND_API.To_Boolean( p_commit ) THEN
916 COMMIT WORK;
917 END IF;
918
919 x_return_status := l_return_status;
920 -- Standard call to get message count and if count is 1, get message info
921 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
922 p_count => x_msg_count,
923 p_data => x_msg_data);
924
925 EXCEPTION
926 WHEN FND_API.G_EXC_ERROR THEN
927 ROLLBACK TO SHOW_IN_UWQ_PVT;
928 x_return_status := FND_API.G_RET_STS_ERROR;
929 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
930 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
931 ROLLBACK TO SHOW_IN_UWQ_PVT;
932 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
933 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
934 WHEN OTHERS THEN
935 ROLLBACK TO SHOW_IN_UWQ_PVT;
936 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
937 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
938 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
939 END IF;
940 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
941 end;
942
943 /**
944 copy strategy work item template
945 **/
946 PROCEDURE COPY_WORK_ITEM_TEMPLATE( p_work_item_temp_id IN NUMBER,
947 p_new_work_item_temp_id IN NUMBER)
948 is
949 l_api_name CONSTANT VARCHAR2(30) := 'COPY_WORK_ITEM_TEMPLATE';
950 -- l_work_item_seq number;
951 begin
952 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
953 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'begin.' );
954 END IF;
955
956 SAVEPOINT COPY_WORK_ITEM;
957
958 -- SELECT IEX_STRATEGY_TEMP_WORK_ITEMS_S.NEXTVAL into l_work_item_seq FROM DUAL;
959
960 insert into iex_stry_temp_work_items_b
961 (work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
962 option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
963 closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
964 last_update_date, last_updated_by, last_update_login, creation_date, created_by, object_version_number,
965 fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
966 )
967 select p_new_work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
968 option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
969 closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
970 sysdate, fnd_global.user_id, fnd_global.user_id, sysdate, fnd_global.user_id, 1.0,
971 fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
972 from iex_stry_temp_work_items_b
973 where work_item_temp_id = p_work_item_temp_id;
974
975 INSERT INTO IEX_STRY_TEMP_WORK_ITEMS_TL
976 (WORK_ITEM_TEMP_ID,NAME,DESCRIPTION,LANGUAGE,SOURCE_LANG,
977 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY
978 )
979 select p_new_work_item_temp_id, 'Copy of ' || b.name,b.description,l.language_code ,b.SOURCE_LANG,
980 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id
981 from iex_stry_temp_work_items_tl B, FND_LANGUAGES L
982 where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
983 and b.work_item_temp_id = p_work_item_temp_id;
984
985 INSERT INTO iex_strategy_work_skills
986 (work_skill_id, WORK_ITEM_TEMP_ID,competence_id,
987 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY, object_version_number
988 )
989 select iex_strategy_work_skills_s.NEXTVAL, p_new_work_item_temp_id, a.competence_id,
990 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id, a.object_version_number
991 from iex_strategy_work_skills a
992 where a.work_item_temp_id = p_work_item_temp_id;
993
994 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
995 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'end.' );
996 END IF;
997
998 EXCEPTION
999 WHEN FND_API.G_EXC_ERROR THEN
1000 ROLLBACK TO COPY_WORK_ITEM;
1001 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1002 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'g_exc_error.' || sqlerrm );
1003 END IF;
1004 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1005 ROLLBACK TO COPY_WORK_ITEM;
1006 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1007 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'g_exc_unexpected_error.' || sqlerrm );
1008 END IF;
1009 WHEN OTHERS THEN
1010 ROLLBACK TO COPY_WORK_ITEM;
1011 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1012 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'others error.' || sqlerrm);
1013 END IF;
1014 end;
1015
1016
1017 --Begin bug#5474793 schekuri 21-Aug-2006
1018 --Added this procedure to provide a way in workitem details form to skip the pre-wait or post wait of the work item
1019 PROCEDURE SKIP_WAIT(p_strategy_id in number,
1020 p_workitem_id in number,
1021 p_wkitem_status in varchar2,
1022 x_return_status out nocopy varchar2) IS
1023
1024 l_return_status varchar2(50);
1025 l_activity_label varchar2(500);
1026 l_work_item_id number;
1027 BEGIN
1028
1029 SAVEPOINT SKIP_WAIT;
1030 l_return_status := FND_API.G_RET_STS_SUCCESS;
1031 l_activity_label := wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
1032 itemkey => p_strategy_id,
1033 aname => 'ACTIVITY_NAME');
1034
1035 l_work_item_id := wf_engine.GetItemAttrNumber(itemtype => 'IEXSTRY',
1036 itemkey => p_strategy_id,
1037 aname => 'WORK_ITEMID');
1038
1039 IF (l_activity_label = 'STRATEGY_SUBPROCESS:PRE_WAIT_PROCESS' and p_wkitem_status = 'PRE-WAIT') OR
1040 (l_activity_label = 'STRATEGY_WORKFLOW:WAIT_AFTER_PROCESS' and p_wkitem_status = 'COMPLETE' and p_workitem_id = l_work_item_id) THEN
1041
1042 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1043 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || 'SKIP_WAIT' || ' Forcing to complete Wait Activity ' || l_activity_label);
1044 END IF;
1045
1046 wf_engine.CompleteActivity(itemtype => 'IEXSTRY',
1047 itemkey => p_strategy_id,
1048 activity =>l_activity_label,
1049 result =>'#TIMEOUT');
1050 COMMIT WORK;
1051 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1052 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || 'SKIP_WAIT' || ' Forcefully completed Wait Activity ' || l_activity_label);
1053 END IF;
1054 x_return_status := l_return_status;
1055 END IF;
1056 EXCEPTION WHEN OTHERS THEN
1057 ROLLBACK TO SKIP_WAIT;
1058 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1059 END;
1060 --End bug#5474793 schekuri 21-Aug-2006
1061
1062
1063
1064 begin
1065 G_USER_ID := FND_GLOBAL.User_Id;
1066 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1067 END IEX_STRY_API_PUB ;
1068
1069
1070
1071 --
1072 --show errors package body IEX_CASE_UTL_PUB
1073 --/
1074 --
1075 --SELECT line, text FROM user_errors
1076 --WHERE name = 'IEX_STRY_API_PUB'
1077 --AND type = 'PACKAGE BODY'
1078 --/
[Home] [Help]