[Home] [Help]
PACKAGE BODY: APPS.IEX_STRY_API_PUB
Source
1 PACKAGE BODY IEX_STRY_API_PUB as
2 /* $Header: iexpsapb.pls 120.11.12020000.3 2013/02/08 03:45:02 bibeura 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 l_prev_status varchar2(100) ;
112
113 cursor c_status(p_strategy_id IN NUMBER) is
114 select status_code
115 from iex_strategies
116 where strategy_id = p_strategy_id ;
117
118 cursor c_object(p_strategy_id IN NUMBER) is
119 select delinquency_id,object_id,object_type
120 from iex_strategies
121 where strategy_id = p_strategy_id ;
122
123 BEGIN
124
125 SAVEPOINT REASSIGN_STRATEGY_PUB;
126
127 x_return_status := FND_API.G_RET_STS_ERROR;
128
129 l_object_type := 'DELINQUENT' ;
130
131 OPEN c_status(p_strategy_id);
132 FETCH c_status INTO l_prev_status;
133 CLOSE c_status;
134
135 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
136 IEX_DEBUG_PUB.logmessage('l_prev_status: ' || l_prev_status);
137 END IF;
138
139 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
140 strategy_id => p_strategy_id,
141 status => p_status ) ;
142
143 -- IF PG_DEBUG < 10 THEN
144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
145 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'AFTER SEND SIGNAL');
146 END IF;
147
148 OPEN c_status(p_strategy_id);
149 FETCH c_status INTO l_status;
150 CLOSE c_status;
151
152 IF ( l_status = 'CANCELLED' ) THEN
153 -- IF PG_DEBUG < 10 THEN
154 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
155 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'After Send Signal and it successfull ');
156 END IF;
157 OPEN c_object(p_strategy_id);
158 FETCH c_object INTO l_delinquency_id,l_object_id,l_object_type;
159 CLOSE c_object;
160
161 -- IF PG_DEBUG < 10 THEN
162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
163 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
164 'before calling create strategy is '
165 ||l_delinquency_id||
166 ' object_id is '||l_object_id ||
167 ' object_type is' || l_object_type );
168 END IF;
169
170 -- IF PG_DEBUG < 10 THEN
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
173 END IF;
174
175 IEX_STRATEGY_PUB.CREATE_STRATEGY
176 (p_api_version_number => 2.0,
177 p_init_msg_list => FND_API.G_TRUE,
178 p_commit => p_commit,
179 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
180 p_delinquencyId => l_delinquency_id ,
181 p_objecttype => l_object_type,
182 p_objectid => l_object_id ,
183 x_return_status => l_return_status,
184 x_msg_count => l_msg_count,
185 x_msg_data => l_msg_data,
186 p_strategy_temp_id => p_strategy_temp_id) ;
187
188 -- IF PG_DEBUG < 10 THEN
189 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
190 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
191 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || ' after calling create strategy '
192 || ' object_id is '||l_object_id || ' object_type is' || l_object_type );
193 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'Calling set_strategy to update the status_code');
194 END IF;
195
196 IF l_prev_status = 'ONHOLD' THEN
197 iex_strategy_pub.set_strategy(
198 P_Api_Version_Number => 2.0,
199 P_Init_Msg_List => 'F',
200 P_Commit => 'T',
201 p_validation_level => 100,
202 X_Return_Status => l_return_status,
203 X_Msg_Count => l_msg_count,
204 X_Msg_Data => l_msg_data,
205 p_DelinquencyID => NULL,
206 p_ObjectType => l_object_type,
207 p_ObjectID => l_object_id,
208 p_Status => 'ONHOLD'
209 );
210 END IF;
211 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
212 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
213 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || ' after calling set_strategy ');
214 END IF;
215
216 x_msg_count :=l_msg_count;
217 x_msg_data :=l_msg_data;
218 x_return_status :=l_return_status;
219
220
221 ELSE
222 ROLLBACK TO REASSIGN_STRATEGY_PUB;
223 RETURN;
224 END if; --l_status =cancelled
225
226 -- Standard check of p_commit
227 IF FND_API.To_Boolean(p_commit) THEN
228 COMMIT WORK;
229 END IF;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 ROLLBACK TO REASSIGN_STRATEGY_PUB;
234 -- IF PG_DEBUG < 10 THEN
235 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
236 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
237 END IF;
238 END REASSIGN_STRATEGY;
239
240 --Start bug 6794510 gnramasa 5th Feb 08
241 /** assign strategy
242 * call create_Strategy_pub
243 * to create the new strategy
244 * the new strategy will launch the work flow*
245 **/
246
247 PROCEDURE ASSIGN_STRATEGY( p_strategy_temp_id IN NUMBER,
248 p_objectid IN NUMBER,
249 p_objecttype IN VARCHAR2,
250 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_count OUT NOCOPY NUMBER,
253 x_msg_data OUT NOCOPY VARCHAR2) IS
254
255 l_delinquency_id number ;
256 l_return_status VARCHAR2(1);
257 l_msg_count NUMBER;
258 l_msg_data VARCHAR2(32767);
259 l_status varchar2(100) ;
260 -- Start Bug# by bibeura 28-Dec-2011
261 TYPE c_onhold_strategiesCurTyp IS REF CURSOR;
262 c_onhold_strategies c_onhold_strategiesCurTyp;
263 vPLSQL VARCHAR2(5000);
264 l_str_count NUMBER;
265 l_where_clause varchar2(200);
266 -- End Bug# by bibeura 28-Dec-2011
267
268 BEGIN
269
270 SAVEPOINT ASSIGN_STRATEGY_PUB;
271
272 x_return_status := FND_API.G_RET_STS_ERROR;
273
274 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
275 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
276 'before calling create strategy is '
277 ||l_delinquency_id||
278 ' object_id is '||p_objectid ||
279 ' object_type is' || p_objecttype );
280 END IF;
281
282 -- IF PG_DEBUG < 10 THEN
283 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
284 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
285 END IF;
286
287 IEX_STRATEGY_PUB.CREATE_STRATEGY
288 (p_api_version_number => 2.0,
289 p_init_msg_list => FND_API.G_TRUE,
290 p_commit => p_commit,
291 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
292 p_delinquencyId => l_delinquency_id ,
293 p_objecttype => p_objecttype,
294 p_objectid => p_objectid ,
295 x_return_status => l_return_status,
296 x_msg_count => l_msg_count,
297 x_msg_data => l_msg_data,
298 p_strategy_temp_id => p_strategy_temp_id) ;
299
300 -- IF PG_DEBUG < 10 THEN
301 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
302 iex_debug_pub.logmessage('ASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
303 END IF;
304
305 x_msg_count :=l_msg_count;
306 x_msg_data :=l_msg_data;
307 x_return_status :=l_return_status;
308
309
310 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
311 ROLLBACK TO ASSIGN_STRATEGY_PUB;
312 RETURN;
313 END if; --l_status =cancelled
314
315 BEGIN
316 IF p_objecttype = 'PARTY' THEN
317 l_where_clause := ' del.party_cust_id = :p_objectid ' ;
318 ELSIF p_objecttype = 'ACCOUNT' THEN
319 l_where_clause := ' del.cust_account_id = :p_objectid ' ;
320 ELSIF p_objecttype = 'BILL_TO' THEN
321 l_where_clause := ' del.customer_site_use_id = :p_objectid ' ;
322 ELSE l_where_clause := ' del.delinquency_id = :p_objectid ' ;
323 END IF;
324
325 vPLSQL := 'SELECT COUNT(1) ' ||
326 ' FROM ar_payment_schedules_all ps, ' ||
327 ' iex_delinquencies_all del WHERE ' ||
328 l_where_clause ||
329 ' AND ps.payment_schedule_id = del.payment_schedule_id ' ||
330 ' AND ps.status = ''OP'' ' ||
331 ' AND del.status IN (''DELINQUENT'', ''PREDELINQUENT'') ' ||
332 ' AND NOT EXISTS(SELECT 1 ' ||
333 ' FROM iex_promise_details pd ' ||
334 ' WHERE pd.delinquency_id = del.delinquency_id ' ||
335 ' AND pd.status = ''COLLECTABLE'' ' ||
336 ' AND pd.state = ''PROMISE'' ' ||
337 ' GROUP BY pd.delinquency_id ' ||
338 ' HAVING sum(nvl(pd.promise_amount,0)) >= ps.amount_due_remaining)';
339
340
341 iex_debug_pub.logmessage('p_object_id: '|| p_objectid);
342 iex_debug_pub.logmessage('p_object_type: '|| p_objecttype);
343 iex_debug_pub.logmessage('l_where_clause: '|| l_where_clause);
344 iex_debug_pub.logmessage('vPLSQL: '|| vPLSQL);
345
346 OPEN c_onhold_strategies FOR vPLSQL USING p_objectid;
347 FETCH c_onhold_strategies INTO l_str_count ;
348 CLOSE c_onhold_strategies;
349
350 iex_debug_pub.logmessage('l_str_count: '|| l_str_count);
351 IF l_str_count = 0 then
352 iex_debug_pub.logmessage('Calling iex_strategy_pub.set_strategy to set the strategy status to ONHOLD');
353
354 iex_strategy_pub.set_strategy
355 (
356 P_Api_Version_Number => 2.0,
357 P_Init_Msg_List => 'F',
358 P_Commit => 'T',
359 p_validation_level => 100,
360 X_Return_Status => l_return_status,
361 X_Msg_Count => l_msg_count,
362 X_Msg_Data => l_msg_data,
363 p_DelinquencyID => l_delinquency_id,
364 p_ObjectType => p_objecttype,
365 p_ObjectID => p_objectid,
366 p_Status => 'ONHOLD'
367 );
368 iex_debug_pub.logmessage('l_return_status: '|| l_return_status);
369 END IF;
370 EXCEPTION
371 WHEN OTHERS THEN
372 iex_debug_pub.logmessage('In exception block');
373 iex_debug_pub.logmessage('SQLCODE: '|| SQLCODE);
374 iex_debug_pub.logmessage('SQLERRM: '|| SQLERRM);
375 END;
376
377 -- Standard check of p_commit
378 IF FND_API.To_Boolean(p_commit) THEN
379 COMMIT WORK;
380 END IF;
381
382 EXCEPTION
383 WHEN OTHERS THEN
384 ROLLBACK TO ASSIGN_STRATEGY_PUB;
385 -- IF PG_DEBUG < 10 THEN
386 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
387 IEX_DEBUG_PUB.logmessage('ASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
388 END IF;
389 END ASSIGN_STRATEGY;
390 --End bug 6794510 gnramasa 5th Feb 08
391
392 /** update work item and call send signal
393 * if send signal fails, roolback the work item
394 **/
395
396 PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec IN
397 iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
398 p_commit IN VARCHAR2,
399 x_return_status OUT NOCOPY VARCHAR2,
400 x_msg_count OUT NOCOPY NUMBER,
401 x_msg_data OUT NOCOPY VARCHAR2)IS
402
403 l_return_status VARCHAR2(1);
404 l_msg_count NUMBER;
405 l_msg_data VARCHAR2(32767);
406 l_status varchar2(100) ;
407 l_object_version_number NUMBER;
408 v_result NUMBER;
409 l_status_code varchar2(100) ;
410 BEGIN
411
412 SAVEPOINT UPDATE_AND_SENDSIGNAL;
413
414 x_return_status := FND_API.G_RET_STS_SUCCESS;
415
416 begin
417 select status_code INTO l_status_code from iex_strategy_work_items
418 where strategy_id = p_strategy_work_item_rec.strategy_id
419 and work_item_id = p_strategy_work_item_rec.work_item_id;
420
421 --Added INERROR_CHECK_NOTIFY for bug#9736794 by bibeura on 09-Dec-2011
422 if (l_status_code not in ('OPEN', 'ONHOLD','PRE-WAIT','INERROR_CHECK_NOTIFY')) then --Added PRE-WAIT for bug#5474793 by schekuri on 21-Aug-2006
423 x_return_status := FND_API.G_RET_STS_ERROR;
424 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
425 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not in status OPEN or ONHOLD');
426 end if;
427 return;
428 end if;
429
430 EXCEPTION
431 WHEN OTHERS THEN
432 x_return_status := FND_API.G_RET_STS_ERROR;
433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
434 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'work item not found');
435 end if;
436 return;
437 end;
438
439 iex_strategy_work_items_pvt.update_strategy_work_items(
440 p_api_version_number => 2.0,
441 p_init_msg_list => FND_API.G_TRUE,
442 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
443 p_commit => p_commit,
444 x_return_status => l_return_status,
445 x_msg_count => l_msg_count,
446 x_msg_data => l_msg_data,
447 p_strategy_work_item_rec => p_strategy_work_item_rec,
448 xo_object_version_number => l_object_version_number);
449
450 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
451 x_msg_count :=l_msg_count;
452 x_msg_data :=l_msg_data;
453 x_return_status :=l_return_status;
454 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
455 return;
456 ELSE
457 --call send signal
458 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
459 strategy_id => p_strategy_work_item_rec.strategy_id,
460 status => p_strategy_work_item_rec.status_code,
461 work_item_id => p_strategy_work_item_rec.work_item_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: ' || 'AFTER SEND SIGNAL');
466 END IF;
467 --check if it the strategy is open
468 -- and next work item is not the same as the updated work item
469 -- then the send signal has been successful and it has created
470 -- the next work item . other wise, the send signal failed.
471 -- id send signal is successful, commit , else rollback
472 /* comment out by kali and ctlee
473 select count(*) INTO v_result from iex_strategies
474 where strategy_id =p_strategy_work_item_rec.strategy_id
475 and next_work_item_id =p_strategy_work_item_rec.work_item_id
476 and status_code ='OPEN';
477
478 if v_result >0 THEN
479 -- IF PG_DEBUG < 10 THEN
480 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
481 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
482 END IF;
483 rollback to UPDATE_AND_SENDSIGNAL;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 return;
486 else
487 -- IF PG_DEBUG < 10 THEN
488 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
489 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
490 END IF;
491 end if;
492 */
493 END IF; --if status is successful
494
495 -- Standard check of p_commit
496 IF FND_API.To_Boolean(p_commit) THEN
497 COMMIT WORK;
498 END IF;
499
500 EXCEPTION
501 WHEN OTHERS THEN
502 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
503 -- IF PG_DEBUG < 10 THEN
504 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
505 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
506 END IF;
507
508 END UPDATE_AND_SENDSIGNAL;
509
510 /** update work item and call send signal
511 * if send signal fails, roolback the work item
512 * this is called from the JSP page , so passing
513 * columns instead of record type
514 * temporary fix till rosetta is fixed
515 * if the status is not changed to 'CLOSED'
516 * 'CANCELLED' THEN just update the work item
517 * do not call send signal
518 *06/21/02 --jsanju
519 **/
520
521 PROCEDURE UPDATE_AND_SENDSIGNAL(p_status IN VARCHAR2
522 ,p_work_item_id IN NUMBER
523 ,p_resource_id IN NUMBER
524 ,p_execute_start IN DATE
525 ,p_execute_end IN DATE
526 ,p_commit IN VARCHAR2
527 ,x_return_status OUT NOCOPY VARCHAR2
528 ,x_msg_count OUT NOCOPY NUMBER
529 ,x_msg_data OUT NOCOPY VARCHAR2) IS
530 l_return_status VARCHAR2(1);
531 l_msg_count NUMBER;
532 l_msg_data VARCHAR2(32767);
533 l_status varchar2(1) ;
534 l_version_number NUMBER;
535 v_result NUMBER;
536 l_strategy_work_item_rec
537 IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
538 l_strategy_id number ;
539
540 cursor c_work_item(p_work_item_id NUMBER) is
541 select object_version_number,strategy_id
542 from iex_strategy_work_items
543 where work_item_id = p_work_item_id ;
544
545 BEGIN
546 -- IF PG_DEBUG < 10 THEN
547 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
548 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'START UPDATE AND SEND SIGNAL');
549 END IF;
550 SAVEPOINT UPDATE_AND_SENDSIGNAL;
551 x_return_status := FND_API.G_RET_STS_SUCCESS;
552
553 OPEN c_work_item(p_work_item_id);
554 fetch c_work_item into l_version_number,l_strategy_id;
555 close c_work_item;
556
557 l_strategy_work_item_rec.work_item_id := p_work_item_id ;
558 l_strategy_work_item_rec.object_version_number := l_version_number ;
559 l_strategy_work_item_rec.execute_start := p_execute_start ;
560 l_strategy_work_item_rec.execute_end := p_execute_end ;
561 l_strategy_work_item_rec.status_code := p_status ;
562 l_strategy_work_item_rec.resource_id :=p_resource_id;
563 l_strategy_work_item_rec.strategy_id :=l_strategy_id;
564
565 -- IF PG_DEBUG < 10 THEN
566 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
567 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');
568 END IF;
569 iex_strategy_work_items_pvt.update_strategy_work_items(
570 p_api_version_number => 2.0,
571 p_init_msg_list => FND_API.G_TRUE,
572 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
573 p_commit => FND_API.G_FALSE,
574 x_return_status => l_return_status,
575 x_msg_count => l_msg_count,
576 x_msg_data => l_msg_data,
577 p_strategy_work_item_rec => l_strategy_work_item_rec,
578 xo_object_version_number => l_version_number);
579 -- IF PG_DEBUG < 10 THEN
580 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
581 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'Status of work item update ' ||l_return_status);
582 END IF;
583
584 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
585 x_msg_count :=l_msg_count;
586 x_msg_data :=l_msg_data;
587 x_return_status :=l_return_status;
588 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
589 return;
590 ELSE
591 --call send signal
592 --only if status = 'CLOSED or 'CANCELLED'
593 IF p_status IN ('CLOSED','CANCELLED','COMPLETE') THEN
594 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
595 strategy_id => l_strategy_work_item_rec.strategy_id,
596 status => l_strategy_work_item_rec.status_code,
597 work_item_id =>l_strategy_work_item_rec.work_item_id);
598
599 -- IF PG_DEBUG < 10 THEN
600 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
601 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
602 END IF;
603 --check if it the strategy is open
604 -- and next work item is not the same as the updated work item
605 -- then the send signal has been successful and it has created
606 -- the next work item . other wise, the send signal failed.
607 -- id send signal is successful, commit , else rollback
608 /* comment out by kali and ctlee
609 select count(*) INTO v_result from iex_strategies
610 where strategy_id =l_strategy_work_item_rec.strategy_id
611 and next_work_item_id =l_strategy_work_item_rec.work_item_id
612 and status_code ='OPEN';
613
614 if v_result >0 THEN
615 -- IF PG_DEBUG < 10 THEN
616 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
617 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
618 END IF;
619 rollback to UPDATE_AND_SENDSIGNAL;
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 return;
622 else
623 -- IF PG_DEBUG < 10 THEN
624 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
625 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
626 END IF;
627 end if;
628 */
629 END IF ; -- p_status in 'closed' or cancelled'
630 END IF; --if status is successful
631
632 -- Standard check of p_commit
633 IF FND_API.To_Boolean(p_commit) THEN
634 COMMIT WORK;
635 END IF;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
640 -- IF PG_DEBUG < 10 THEN
641 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
642 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
643 END IF;
644
645
646 END UPDATE_AND_SENDSIGNAL;
647
648 --06/27
649 --this procedure check the status of the workflow
650 --will be called before "changing the strategy"
651 -- " update and skip to next work item"
652 -- if the work flow is in error, then
653 --display on the screen that the work flow is in error
654 --along with the error_message attribute
655 --if the workflow is not suspended and
656 --if the activity_name is null( that means
657 --it is not a escalation or optional task)
658 -- there has been a error. display message.
659 PROCEDURE CHECK_STRATEGY_WORKFLOW ( p_strategy IN NUMBER
660 ,x_return_status OUT NOCOPY VARCHAR2
661 ,x_return_message OUT NOCOPY VARCHAR2
662 ,x_wf_status OUT NOCOPY VARCHAR2) IS
663
664 l_result VARCHAR2(10);
665 l_return_status VARCHAR2(20);
666 l_activity_name VARCHAR2(100);
667 l_wf_error VARCHAR2(32627);
668 l_strategy VARCHAR2(100);
669 --begin bug#13842980 schekuri 14-Mar-2012
670 l_status varchar2(1000);
671 l_actid varchar2(1000);
672 l_errname varchar2(1000);
673 l_errstack varchar2(1000);
674 /*cursor c_get_Wf_error (p_strategy IN VARCHAR2 )is
675 select
676 --ias.activity_result_code Result,
677 -- ias.error_name ERROR_NAME,
678 ias.error_message ERROR_MESSAGE
679 -- ,ias.error_stack ERROR_STACK
680 from wf_item_activity_statuses ias
681 where
682 ias.item_type ='IEXSTRY'
683 and ias.item_key =p_strategy -- changed for bug 13111098 pnaveenk
684 and ias.activity_status = 'ERROR';*/
685 --End bug#13842980 schekuri 14-Mar-2012
686 BEGIN
687 -- IF PG_DEBUG < 10 THEN
688 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
689 IEX_DEBUG_PUB.logmessage('Begin IEX_STRY_API_PUB.CHECK_STRATEGY_WORKFLOW' );
690 END IF;
691 --check status of the workflow
692 wf_engine.ItemStatus( itemtype => 'IEXSTRY',
693 itemkey => p_strategy,
694 status => l_return_status,
695 result => l_result);
696 x_wf_Status :=l_return_status;
697
698 --07/31/02
699 --get workflow error from wf_item_activity_statuses table
700 -- bug 13111098 pnaveenk
701 l_strategy := TO_CHAR(p_strategy);
702 --begin bug#13842980 schekuri 14-Mar-2012
703 /*OPEN c_get_Wf_error (l_strategy);
704 FETCH c_get_Wf_error INTO l_wf_error;
705 CLOSE c_get_wf_error;*/
706 wf_engine.ItemInfo(itemtype =>'IEXSTRY',
707 itemkey => l_strategy,
708 status =>l_status,
709 result =>l_result,
710 actid =>l_actid,
711 errname =>l_errname,
712 errmsg =>l_wf_error,
713 errstack =>l_errstack);
714 --end bug#13842980 schekuri 14-Mar-2012
715 -- IF PG_DEBUG < 10 THEN
716 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
717 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'wf error is ' ||l_wf_error);
718 END IF;
719
720 if l_return_status = wf_engine.eng_error THEN
721 -- work flow is in error
722 --get the error message from the error_message attribute
723 x_return_message :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
724 itemkey => p_strategy,
725 aname => 'ERROR_MESSAGE');
726 x_return_status := 'E';
727
728 x_return_message := x_return_message || l_wf_error;
729 -- IF PG_DEBUG < 10 THEN
730 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
731 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'Work flow is in error for strategy Id'
732 ||p_strategy || 'error is ' ||x_return_message);
733 END IF;
734
735 elsif l_return_status =wf_engine.eng_active THEN
736 -- work flow is active
737 -- and is in error if the activity name is not populated
738 -- the activity name gets populated for optional and escalation work items
739 -- if it is in error then get the error message from the error_message attribute
740 -- this can happen if the work flow has not reached the node where it gets suspended
741 --could be due to many reason. these profiles might not be set
742 --IEX_STRY_MEATAPHOR_CREATION -- for uwq creation
743 --IEX_STRY_DEFAULT_RESOURCE --
744
745 l_activity_name :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
746 itemkey => p_strategy,
747 aname => 'ACTIVITY_NAME');
748 If l_activity_name is null then
749 -- IF PG_DEBUG < 10 THEN
750 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
751 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'l_activity_name is null ' );
752 END IF;
753 x_return_message :=wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
754 itemkey => p_strategy,
755 aname => 'ERROR_MESSAGE');
756
757 x_return_message := x_return_message || l_wf_error;
758 x_return_status := 'E';
759 -- IF PG_DEBUG < 10 THEN
760 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
761 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'Work flow is in error for strategy Id'
762 ||p_strategy || 'error is ' ||x_return_message);
763 END IF;
764 -- set status to success
765 -- cancel or complete optional or escalate work item
766 else
767 x_return_status := 'S';
768 end if;
769 else
770 -- the work flow is SUSPENDED or COMPLETE
771 -- the form doesn't all ow any changes if the
772 -- workflow is 'COMPLETE'
773 -- set status to complete.
774 x_return_status := 'S';
775 x_return_message := NULL;
776 end if;
777
778 -- IF PG_DEBUG < 10 THEN
779 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
780 IEX_DEBUG_PUB.logmessage('End IEX_STRY_API_PUB.CHECK_STRATEGY_WORKFLOW' );
781 END IF;
782
783 EXCEPTION
784 WHEN OTHERS THEN
785 -- IF PG_DEBUG < 10 THEN
786 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
787 IEX_DEBUG_PUB.logmessage('CHECK_STRATEGY_WORKFLOW: ' || 'inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
788 END IF;
789 x_return_status := 'E';
790 x_return_message := sqlerrm;
791 x_wf_Status :=NULL;
792 END CHECK_STRATEGY_WORKFLOW;
793
794 PROCEDURE UPDATE_WORKITEM (p_status IN VARCHAR2
795 ,p_work_item_id IN NUMBER
796 ,p_resource_id IN NUMBER
797 ,p_execute_start IN DATE
798 ,p_execute_end IN DATE
799 ,p_commit IN VARCHAR2
800 ,x_return_status OUT NOCOPY VARCHAR2
801 ,x_msg_count OUT NOCOPY NUMBER
802 ,x_msg_data OUT NOCOPY VARCHAR2) IS
803
804
805 l_return_status VARCHAR2(1);
806 l_msg_count NUMBER;
807 l_msg_data VARCHAR2(32767);
808 l_status varchar2(1) ;
809 l_version_number NUMBER;
810 v_result NUMBER;
811 l_strategy_work_item_rec
812 IEX_STRATEGY_WORK_ITEMS_PVT.STRATEGY_WORK_ITEM_REC_TYPE;
813 l_strategy_id number ;
814
815 cursor c_work_item(p_work_item_id NUMBER) is
816 select object_version_number,strategy_id
817 from iex_strategy_work_items
818 where work_item_id = p_work_item_id ;
819
820 BEGIN
821 -- IF PG_DEBUG < 10 THEN
822 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
823 IEX_DEBUG_PUB.logmessage('START UPDATE_WORKITEM');
824 END IF;
825 SAVEPOINT UPDATE_WORKITEM;
826 x_return_status := FND_API.G_RET_STS_SUCCESS;
827
828 OPEN c_work_item(p_work_item_id);
829 fetch c_work_item into l_version_number,l_strategy_id;
830 close c_work_item;
831
832 l_strategy_work_item_rec.work_item_id := p_work_item_id ;
833 l_strategy_work_item_rec.object_version_number := l_version_number ;
834 l_strategy_work_item_rec.execute_start := p_execute_start ;
835 l_strategy_work_item_rec.execute_end := p_execute_end ;
836 l_strategy_work_item_rec.status_code := p_status ;
837 l_strategy_work_item_rec.resource_id :=p_resource_id;
838 l_strategy_work_item_rec.strategy_id :=l_strategy_id;
839
840 -- IF PG_DEBUG < 10 THEN
841 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
842 IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'BEFORE CALLING UPDATE WORK ITEM PVT');
843 END IF;
844 iex_strategy_work_items_pvt.update_strategy_work_items(
845 p_api_version_number => 2.0,
846 p_init_msg_list => FND_API.G_TRUE,
847 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
848 p_commit => FND_API.G_FALSE,
849 x_return_status => l_return_status,
850 x_msg_count => l_msg_count,
851 x_msg_data => l_msg_data,
852 p_strategy_work_item_rec => l_strategy_work_item_rec,
853 xo_object_version_number => l_version_number);
854 -- IF PG_DEBUG < 10 THEN
855 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
856 IEX_DEBUG_PUB.logmessage('UPDATE_WORKITEM: ' || 'Status of work item update ' ||l_return_status);
857 END IF;
858
859 -- Standard check of p_commit
860 IF FND_API.To_Boolean(p_commit) THEN
861 COMMIT WORK;
862 END IF;
863
864 EXCEPTION
865 WHEN OTHERS THEN
866 ROLLBACK TO UPDATE_WORKITEM;
867 -- IF PG_DEBUG < 10 THEN
868 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
869 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_WORKITEM '||sqlerrm);
870 END IF;
871
872 END UPDATE_WORKITEM;
873
874 /*********************
875 Set UWQ status for Strategy
876 ***********************/
877 PROCEDURE SHOW_IN_UWQ(
878 P_API_VERSION IN NUMBER,
879 P_INIT_MSG_LIST IN VARCHAR2,
880 P_COMMIT IN VARCHAR2,
881 P_VALIDATION_LEVEL IN NUMBER,
882 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
883 X_MSG_COUNT OUT NOCOPY NUMBER,
884 X_MSG_DATA OUT NOCOPY VARCHAR2,
885 P_WORK_ITEM_ID_TBL IN DBMS_SQL.NUMBER_TABLE,
886 P_UWQ_STATUS IN VARCHAR2,
887 P_NO_DAYS IN NUMBER)
888 IS
889 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_IN_UWQ';
890 l_api_version CONSTANT NUMBER := 1.0;
891 l_return_status varchar2(10);
892 l_msg_count number;
893 l_msg_data varchar2(200);
894
895 l_validation_item varchar2(100);
896 l_days NUMBER;
897 l_set_status_date DATE;
898 l_status varchar2(20);
899 nCount number;
900
901 Type refCur is Ref Cursor;
902 l_cursor refCur;
903 l_SQL VARCHAR2(10000);
904 l_broken_promises DBMS_SQL.NUMBER_TABLE;
905 i number;
906 j number;
907 l_uwq_active_date date;
908 l_uwq_complete_date date;
909
910 begin
911 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
912 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': start');
913 END IF;
914
915 -- Standard start of API savepoint
916 SAVEPOINT SHOW_IN_UWQ_PVT;
917
918 -- Standard call to check for call compatibility
919 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
922
923 -- Initialize message list if p_init_msg_list is set to TRUE
924 IF FND_API.To_Boolean(p_init_msg_list) THEN
925 FND_MSG_PUB.initialize;
926 END IF;
927
928 -- Initialize API return status to success
929 l_return_status := FND_API.G_RET_STS_SUCCESS;
930
931 -- START OF BODY OF API
932
933 -- validating uwq status
934 l_validation_item := 'P_UWQ_STATUS';
935 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
936 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': new uwq status: ' || P_UWQ_STATUS);
937 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
938 END IF;
939 if P_UWQ_STATUS is null then
940 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
941 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
942 END IF;
943 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
944 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
945 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
946 FND_MSG_PUB.Add;
947 RAISE FND_API.G_EXC_ERROR;
948 end if;
949
950 -- validating table of promises
951 l_validation_item := 'P_WORK_ITEM_ID_TBL';
952 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
953 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': count of P_WORK_ITEM_ID_TBL: ' || P_WORK_ITEM_ID_TBL.count);
954 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
955 END IF;
956 if P_WORK_ITEM_ID_TBL.count = 0 then
957 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
958 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
959 END IF;
960 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
961 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
962 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
963 FND_MSG_PUB.Add;
964 RAISE FND_API.G_EXC_ERROR;
965 end if;
966
967 -- validating p_days
968 l_validation_item := 'P_NO_DAYS';
969 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
970 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': P_NO_DAYS: ' || P_NO_DAYS);
971 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': Validating ' || l_validation_item);
972 END IF;
973 if P_NO_DAYS is not null and P_NO_DAYS < 0 then
974 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
975 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': ' || l_validation_item || ' failed validation');
976 END IF;
977 FND_MESSAGE.SET_NAME('IEX','IEX_BAD_API_INPUT');
978 FND_MESSAGE.SET_TOKEN('API_NAME', G_PKG_NAME || '.' || l_api_name);
979 FND_MESSAGE.SET_TOKEN('API_PARAMETER', l_validation_item);
980 FND_MSG_PUB.Add;
981 RAISE FND_API.G_EXC_ERROR;
982 end if;
983
984 -- set number of days
985 if P_NO_DAYS is null then
986 l_days := to_number(nvl(fnd_profile.value('IEX_UWQ_DEFAULT_PENDING_DAYS'), '0'));
987 else
988 l_days := P_NO_DAYS;
989 end if;
990 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
991 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': number of days: ' || l_days);
992 END IF;
993 l_set_status_date := sysdate + l_days;
994 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
995 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': set status date: ' || l_set_status_date);
996 END IF;
997
998 -- check for status
999 if P_UWQ_STATUS = 'ACTIVE' then
1000 l_uwq_active_date := NULL;
1001 l_uwq_complete_date := NULL;
1002 elsif P_UWQ_STATUS = 'PENDING' then
1003 l_uwq_active_date := l_set_status_date;
1004 l_uwq_complete_date := NULL;
1005 elsif P_UWQ_STATUS = 'COMPLETE' then
1006 l_uwq_active_date := NULL;
1007 l_uwq_complete_date := sysdate;
1008 end if;
1009
1010 nCount := p_work_item_id_tbl.count;
1011 if nCount > 0 then
1012 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1013 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_active_date: ' || l_uwq_active_date);
1014 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': l_uwq_complete_date: ' || l_uwq_complete_date);
1015 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': updating promise details...');
1016 END IF;
1017 FORALL i in 1..nCount
1018 UPDATE iex_strategy_work_items
1019 SET uwq_status = p_uwq_status,
1020 uwq_active_date = l_uwq_active_date,
1021 uwq_complete_date = l_uwq_complete_date,
1022 last_update_date = sysdate,
1023 last_updated_by = g_user_id
1024 where
1025 work_item_id = p_work_item_id_tbl(i);
1026 else
1027 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1028 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || ': nothing to update');
1029 END IF;
1030 end if;
1031
1032 -- END OF BODY OF API
1033
1034 -- Standard check of p_commit.
1035 IF FND_API.To_Boolean( p_commit ) THEN
1036 COMMIT WORK;
1037 END IF;
1038
1039 x_return_status := l_return_status;
1040 -- Standard call to get message count and if count is 1, get message info
1041 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1042 p_count => x_msg_count,
1043 p_data => x_msg_data);
1044
1045 EXCEPTION
1046 WHEN FND_API.G_EXC_ERROR THEN
1047 ROLLBACK TO SHOW_IN_UWQ_PVT;
1048 x_return_status := FND_API.G_RET_STS_ERROR;
1049 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1051 ROLLBACK TO SHOW_IN_UWQ_PVT;
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1054 WHEN OTHERS THEN
1055 ROLLBACK TO SHOW_IN_UWQ_PVT;
1056 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1057 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1058 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1059 END IF;
1060 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1061 end;
1062
1063 /**
1064 copy strategy work item template
1065 **/
1066 PROCEDURE COPY_WORK_ITEM_TEMPLATE( p_work_item_temp_id IN NUMBER,
1067 p_new_work_item_temp_id IN NUMBER)
1068 is
1069 l_api_name CONSTANT VARCHAR2(30) := 'COPY_WORK_ITEM_TEMPLATE';
1070 -- l_work_item_seq number;
1071 Newworkitemname varchar2(250);
1072 Newcnt number;
1073
1074 begin
1075
1076 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1077 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'begin.' );
1078 END IF;
1079
1080 SAVEPOINT COPY_WORK_ITEM;
1081
1082 -- begin Bug 8233425
1083 begin
1084 select b.name into Newworkitemname from iex_stry_temp_work_items_tl B
1085 where B.LANGUAGE = userenv('LANG')
1086 and b.work_item_temp_id = p_work_item_temp_id;
1087
1088 Newcnt := 1;
1089
1090 While Newcnt >= 1
1091 Loop
1092
1093 Newworkitemname := 'Copy of ' ||Newworkitemname;
1094 iex_debug_pub.LogMessage( 'Inside Loop ... Newworkitemname === '||Newworkitemname );
1095
1096 select count(name) into Newcnt
1097 from iex_stry_temp_work_items_tl B, FND_LANGUAGES L
1098 where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
1099 and b.name = Newworkitemname;
1100
1101 End Loop;
1102
1103 exception
1104 when others then
1105 iex_debug_pub.LogMessage( 'Exception from bug 8233425....' );
1106 null;
1107 end;
1108
1109 iex_debug_pub.LogMessage( 'Outside Loop ... Newworkitemname === '||Newworkitemname );
1110 -- end Bug 8233425
1111
1112
1113 -- SELECT IEX_STRATEGY_TEMP_WORK_ITEMS_S.NEXTVAL into l_work_item_seq FROM DUAL;
1114
1115 insert into iex_stry_temp_work_items_b
1116 (work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
1117 option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
1118 closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
1119 last_update_date, last_updated_by, last_update_login, creation_date, created_by, object_version_number,
1120 fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
1121 )
1122 select p_new_work_item_temp_id, competence_id, work_type, category_type, priority_type, optional_yn,
1123 option_wait_time, option_wait_time_uom, pre_execution_wait, post_execution_wait, execution_time_uom,
1124 closure_time_limit, closure_time_uom, workflow_item_type, same_resource_yn,
1125 sysdate, fnd_global.user_id, fnd_global.user_id, sysdate, fnd_global.user_id, 1.0,
1126 fulfil_temp_id, escalate_yn, notify_yn, schedule_wait, schedule_uom, enabled_flag, xdo_template_id
1127 from iex_stry_temp_work_items_b
1128 where work_item_temp_id = p_work_item_temp_id;
1129
1130 INSERT INTO IEX_STRY_TEMP_WORK_ITEMS_TL
1131 (WORK_ITEM_TEMP_ID,NAME,DESCRIPTION,LANGUAGE,SOURCE_LANG,
1132 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY
1133 )
1134 -- Bug 8233425 select p_new_work_item_temp_id, 'Copy of ' || b.name,b.description,l.language_code ,b.SOURCE_LANG,
1135 select p_new_work_item_temp_id, Newworkitemname,b.description,l.language_code ,b.SOURCE_LANG,
1136 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id
1137 from iex_stry_temp_work_items_tl B, FND_LANGUAGES L
1138 where L.INSTALLED_FLAG in ('I', 'B') and B.LANGUAGE = userenv('LANG')
1139 and b.work_item_temp_id = p_work_item_temp_id;
1140
1141 INSERT INTO iex_strategy_work_skills
1142 (work_skill_id, WORK_ITEM_TEMP_ID,competence_id,
1143 CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,LAST_UPDATED_BY, object_version_number
1144 )
1145 select iex_strategy_work_skills_s.NEXTVAL, p_new_work_item_temp_id, a.competence_id,
1146 sysdate,fnd_global.user_id,sysdate,fnd_global.user_id,fnd_global.user_id, a.object_version_number
1147 from iex_strategy_work_skills a
1148 where a.work_item_temp_id = p_work_item_temp_id;
1149
1150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1151 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'end.' );
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN FND_API.G_EXC_ERROR THEN
1156 ROLLBACK TO COPY_WORK_ITEM;
1157 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1158 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'g_exc_error.' || sqlerrm );
1159 END IF;
1160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1161 ROLLBACK TO COPY_WORK_ITEM;
1162 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1163 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'g_exc_unexpected_error.' || sqlerrm );
1164 END IF;
1165 WHEN OTHERS THEN
1166 ROLLBACK TO COPY_WORK_ITEM;
1167 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1168 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || l_api_name || 'others error.' || sqlerrm);
1169 END IF;
1170 end;
1171
1172
1173 --Begin bug#5474793 schekuri 21-Aug-2006
1174 --Added this procedure to provide a way in workitem details form to skip the pre-wait or post wait of the work item
1175 PROCEDURE SKIP_WAIT(p_strategy_id in number,
1176 p_workitem_id in number,
1177 p_wkitem_status in varchar2,
1178 x_return_status out nocopy varchar2) IS
1179
1180 l_return_status varchar2(50);
1181 l_activity_label varchar2(500);
1182 l_work_item_id number;
1183 BEGIN
1184
1185 SAVEPOINT SKIP_WAIT;
1186 l_return_status := FND_API.G_RET_STS_SUCCESS;
1187 l_activity_label := wf_engine.GetItemAttrText(itemtype => 'IEXSTRY',
1188 itemkey => p_strategy_id,
1189 aname => 'ACTIVITY_NAME');
1190
1191 l_work_item_id := wf_engine.GetItemAttrNumber(itemtype => 'IEXSTRY',
1192 itemkey => p_strategy_id,
1193 aname => 'WORK_ITEMID');
1194
1195 IF (l_activity_label = 'STRATEGY_SUBPROCESS:PRE_WAIT_PROCESS' and p_wkitem_status = 'PRE-WAIT') OR
1196 (l_activity_label = 'STRATEGY_WORKFLOW:WAIT_AFTER_PROCESS' and p_wkitem_status = 'COMPLETE' and p_workitem_id = l_work_item_id) THEN
1197
1198 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1199 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || 'SKIP_WAIT' || ' Forcing to complete Wait Activity ' || l_activity_label);
1200 END IF;
1201
1202 wf_engine.CompleteActivity(itemtype => 'IEXSTRY',
1203 itemkey => p_strategy_id,
1204 activity =>l_activity_label,
1205 result =>'#TIMEOUT');
1206 COMMIT WORK;
1207 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1208 iex_debug_pub.LogMessage(G_PKG_NAME || '.' || 'SKIP_WAIT' || ' Forcefully completed Wait Activity ' || l_activity_label);
1209 END IF;
1210 x_return_status := l_return_status;
1211 END IF;
1212 EXCEPTION WHEN OTHERS THEN
1213 ROLLBACK TO SKIP_WAIT;
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 END;
1216 --End bug#5474793 schekuri 21-Aug-2006
1217
1218
1219
1220 begin
1221 G_USER_ID := FND_GLOBAL.User_Id;
1222 PG_DEBUG := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
1223 END IEX_STRY_API_PUB ;
1224
1225
1226
1227 --
1228 --show errors package body IEX_CASE_UTL_PUB
1229 --/
1230 --
1231 --SELECT line, text FROM user_errors
1232 --WHERE name = 'IEX_STRY_API_PUB'
1233 --AND type = 'PACKAGE BODY'
1234 --/