[Home] [Help]
PACKAGE BODY: APPS.IEX_STRY_UTL_PUB
Source
1 PACKAGE BODY IEX_STRY_UTL_PUB as
2 /* $Header: iexpsutb.pls 120.9.12020000.3 2012/08/13 13:51:30 bibeura ship $ */
3 -- Start of Comments
4 -- Package name : IEX_STRY_UTL_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(100):= 'IEX_STRY_UTL_PUB ';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexpsutb.pls';
12
13
14 /**Name AddInvalidArgMsg
15 **Appends to a message the api name, parameter name and parameter Value
16 */
17
18 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
19 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
20
21 PROCEDURE AddInvalidArgMsg
22 ( p_api_name IN VARCHAR2,
23 p_param_value IN VARCHAR2,
24 p_param_name IN VARCHAR2 ) IS
25 BEGIN
26 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
27 fnd_message.set_name('IEX', 'IEX_API_ALL_INVALID_ARGUMENT');
28 fnd_message.set_token('API_NAME', p_api_name);
29 fnd_message.set_token('VALUE', p_param_value);
30 fnd_message.set_token('PARAMETER', p_param_name);
31 fnd_msg_pub.add;
32 END IF;
33
34
35 END AddInvalidArgMsg;
36
37 /**Name AddMissingArgMsg
38 **Appends to a message the api name, parameter name and parameter Value
39 */
40
41 PROCEDURE AddMissingArgMsg
42 ( p_api_name IN VARCHAR2,
43 p_param_name IN VARCHAR2 )IS
44 BEGIN
45 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
46 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
47 fnd_message.set_token('API_NAME', p_api_name);
48 fnd_message.set_token('MISSING_PARAM', p_param_name);
49 fnd_msg_pub.add;
50 END IF;
51 END AddMissingArgMsg;
52
53 /**Name AddNullArgMsg
54 **Appends to a message the api name, parameter name and parameter Value
55 */
56
57 PROCEDURE AddNullArgMsg
58 ( p_api_name IN VARCHAR2,
59 p_param_name IN VARCHAR2 )IS
60 BEGIN
61 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
62 fnd_message.set_name('IEX', 'IEX_API_ALL_NULL_PARAMETER');
63 fnd_message.set_token('API_NAME', p_api_name);
64 fnd_message.set_token('NULL_PARAM', p_param_name);
65 fnd_msg_pub.add;
66 END IF;
67
68
69 END AddNullArgMsg;
70
71 /**Name AddFailMsg
72 **Appends to a message the name of the object anf the operation (insert, update ,delete)
73 */
74 PROCEDURE AddfailMsg
75 ( p_object IN VARCHAR2,
76 p_operation IN VARCHAR2 ) IS
77
78 BEGIN
79 fnd_message.set_name('IEX', 'IEX_FAILED_OPERATION');
80 fnd_message.set_token('OBJECT', p_object);
81 fnd_message.set_token('OPERATION', p_operation);
82 fnd_msg_pub.add;
83
84 END AddfailMsg;
85
86
87 PROCEDURE GET_NEXT_WORK_ITEMS
88 (p_api_version IN NUMBER,
89 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
90 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
91 p_strategy_id IN NUMBER,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_msg_count OUT NOCOPY NUMBER,
94 x_msg_data OUT NOCOPY VARCHAR2,
95 x_work_item_tab OUT NOCOPY work_item_tab_type) IS
96
97 CURSOR c_get_strategy_template_id(c_strategy_id NUMBER) IS
98 SELECT strategy_template_id
99 FROM iex_strategies
100 WHERE strategy_id =c_strategy_id;
101
102
103 CURSOR c_get_work_items(c_strategy_id NUMBER, c_template_id NUMBER) IS
104
105 select sxref.strategy_temp_id TEMPLATE_ID,
106 sxref.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
107 sxref.work_item_order ORDER_BY
108 ,nvl(swit.status_code,'NOTCREATED') STATUS
109 ,swit.work_item_id WORK_ITEM_ID
110 ,swit.strategy_id STRATEGY_ID
111 from iex_strategy_work_temp_xref sxref
112 ,iex_strategy_work_items swit
113 where sxref.strategy_temp_id =c_template_id
114 and swit.work_item_template_id(+) =sxref.work_item_temp_id
115 and swit.strategy_id(+) =c_strategy_id
116 union all
117 select susit.strategy_template_id TEMPLATE_ID,
118 susit.work_item_temp_id WORK_ITEM_TEMPLATE_ID,
119 susit.work_item_order ORDER_BY
120 ,nvl(swit.status_code,'NOTCREATED') STATUS
121 ,swit.work_item_id WORK_ITEM_ID
122 ,susit.strategy_id STRATEGY_ID
123 from iex_strategy_user_items susit
124 ,iex_strategy_work_items swit
125 where susit.strategy_id =c_strategy_id
126 and swit.work_item_template_id(+) =susit.work_item_temp_id
127 and swit.strategy_id(+) =c_strategy_id
128 order by order_by;
129
130
131 l_api_version CONSTANT NUMBER := 1.0;
132 l_api_name VARCHAR2(100) := 'GET_NEXT_WORK_ITEMS';
133 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
134 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
135 l_return_status VARCHAR2(1);
136 l_msg_count NUMBER;
137 l_msg_data VARCHAR2(32767);
138 idx NUMBER := 0;
139 l_template_id NUMBER :=0;
140 BEGIN
141
142 SAVEPOINT GET_NEXT_WORK_ITEMS_PUB;
143
144 -- Standard call to check for call compatibility.
145 IF NOT FND_API.Compatible_API_Call (l_api_version,
146 p_api_version,
147 l_api_name,
148 G_PKG_NAME) THEN
149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
150 END IF;
151
152 -- Check p_init_msg_list
153 IF FND_API.to_Boolean( p_init_msg_list ) THEN
154 FND_MSG_PUB.initialize;
155 END IF;
156
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158
159 -- Check for required parameter p_strategy_id
160 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
161 -- IF PG_DEBUG < 10 THEN
162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
163 IEX_DEBUG_PUB.LogMessage('GET_NEXT_WORK_ITEMS: ' || 'Required Parameter p_strategy_id is invalid');
164 END IF;
165 AddMissingArgMsg(
166 p_api_name => l_api_name_full,
167 p_param_name => 'p_strategy_id' );
168 RAISE FND_API.G_EXC_ERROR;
169 END IF;
170 -- IF PG_DEBUG < 10 THEN
171 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
172 IEX_DEBUG_PUB.LogMessage('GET_NEXT_WORK_ITEMS: ' || 'after p_strategy_id check');
173 END IF;
174
175 OPEN c_get_strategy_template_id (p_strategy_id);
176 FETCH c_get_strategy_template_id INTO l_template_id;
177 CLOSE c_get_strategy_template_id;
178
179 -- Check for required parameter p_template_id
180 IF (l_template_id IS NULL) THEN
181 -- IF PG_DEBUG < 10 THEN
182 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
183 IEX_DEBUG_PUB.LogMessage('GET_NEXT_WORK_ITEMS: ' || 'template_id is invalid');
184 END IF;
185 AddMissingArgMsg(
186 p_api_name => l_api_name_full,
187 p_param_name => 'l_template_id' );
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190 -- IF PG_DEBUG < 10 THEN
191 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
192 IEX_DEBUG_PUB.LogMessage('GET_NEXT_WORK_ITEMS: ' || 'after l_template_id check'||l_template_id);
193 END IF;
194
195 FOR c_get_work_items_rec in c_get_work_items (p_strategy_id,l_template_id)
196 LOOP
197 idx := idx + 1;
198 x_work_item_tab(idx).TEMPLATE_ID := c_get_work_items_rec.TEMPLATE_ID;
199 x_work_item_tab(idx).WORK_ITEM_TEMPLATE_ID := c_get_work_items_rec.WORK_ITEM_TEMPLATE_ID;
200 x_work_item_tab(idx).ORDER_BY := c_get_work_items_rec.ORDER_BY;
201 x_work_item_tab(idx).STATUS := c_get_work_items_rec.STATUS;
202 x_work_item_tab(idx).WORK_ITEM_ID := c_get_work_items_rec.WORK_ITEM_ID;
203 x_work_item_tab(idx).STRATEGY_ID := c_get_work_items_rec.STRATEGY_ID;
204 END LOOP;
205
206
207
208 -- Standard check of p_commit
209 IF FND_API.To_Boolean(p_commit) THEN
210 COMMIT WORK;
211 END IF;
212
213 -- Standard call to get message count and if count is 1, get message info
214 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
215
216
217 EXCEPTION
218 WHEN FND_API.G_EXC_ERROR THEN
219 ROLLBACK TO GET_NEXT_WORK_ITEMS_PUB;
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
222
223 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
224 ROLLBACK TO GET_NEXT_WORK_ITEMS_PUB;
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
227
228 WHEN OTHERS THEN
229 ROLLBACK TO GET_NEXT_WORK_ITEMS_PUB;
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
232 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
233 END IF;
234 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
235
236 END GET_NEXT_WORK_ITEMS;
237
238 /**
239 update all the work_items status to depending on the status passed
240 update the stragey status to depending on the status passed
241 **/
242
243 PROCEDURE CLOSE_STRY_AND_WITEMS
244 (p_api_version IN NUMBER,
245 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE,
246 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
247 p_strategy_id IN NUMBER,
248 p_status IN VARCHAR2,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_count OUT NOCOPY NUMBER,
251 x_msg_data OUT NOCOPY VARCHAR2
252 )IS
253
254 l_api_version CONSTANT NUMBER := 1.0;
255 l_api_name VARCHAR2(100) := 'CLOSE_STRATEGY_AND WORK_ITEMS';
256 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
257 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
258 l_return_status VARCHAR2(1);
259 l_msg_count NUMBER;
260 l_msg_data VARCHAR2(32767);
261 l_api_version_number CONSTANT NUMBER := 2.0;
262 l_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type;
263 l_strategy_rec IEX_strategy_PVT.strategy_Rec_Type;
264 l_object_version_number NUMBER;
265
266 Cursor c_get_work_items (p_strategy_id NUMBER) is
267 SELECT work_item_id, object_version_number
268 FROM iex_strategy_work_items
269 WHERE strategy_id = p_strategy_id
270 and status_code IN ('OPEN','PRE-WAIT','INERROR_CHECK_NOTIFY'); -- Changed for bug#7703351 by PNAVEENK on 22-1-2009
271 -- NOT IN ('COMPLETE' ,'CANCELLED','CLOSED','TIMEOUT');
272
273
274 BEGIN
275
276 SAVEPOINT CLOSE_STRY_AND_WITEMS_PUB;
277
278 -- Standard call to check for call compatibility.
279 IF NOT FND_API.Compatible_API_Call (l_api_version,
280 p_api_version,
281 l_api_name,
282 G_PKG_NAME) THEN
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285
286 -- Check p_init_msg_list
287 IF FND_API.to_Boolean( p_init_msg_list ) THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290 -- IF PG_DEBUG < 10 THEN
291 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
292 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'after init');
293 END IF;
294
295 x_return_status := FND_API.G_RET_STS_SUCCESS;
296
297 -- Check for required parameter p_strategy_id
298 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
299 -- IF PG_DEBUG < 10 THEN
300 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
301 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'Required Parameter p_strategy_id is invalid');
302 END IF;
303 AddMissingArgMsg(
304 p_api_name => l_api_name_full,
305 p_param_name => 'p_strategy_id' );
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308 -- IF PG_DEBUG < 10 THEN
309 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
310 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'after p_strategy_id check');
311 END IF;
312
313 -- Check for required parameter p_status_id
314 IF (p_status IS NULL) OR (p_status = FND_API.G_MISS_CHAR) THEN
315 -- IF PG_DEBUG < 10 THEN
316 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
317 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'Required Parameter p_status is invalid');
318 END IF;
319 AddMissingArgMsg(
320 p_api_name => l_api_name_full,
321 p_param_name => 'p_status' );
322 RAISE FND_API.G_EXC_ERROR;
323 END IF;
324 -- IF PG_DEBUG < 10 THEN
325 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
326 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'after p_status check');
327 END IF;
328
329 FOR c_get_work_items_rec in c_get_work_items (p_strategy_id)
330 LOOP
331 l_strategy_work_item_Rec.work_item_id :=c_get_work_items_rec.work_item_id;
332 l_strategy_work_item_Rec.status_code := p_status;
333 l_strategy_work_item_Rec.object_version_number
334 :=c_get_work_items_rec.object_version_number;
335 l_strategy_work_item_Rec.execute_end := sysdate;
336 -- IF PG_DEBUG < 10 THEN
337 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
338 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'Before Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items');
339 END IF;
340 -- IF PG_DEBUG < 10 THEN
341 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
342 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || '---------------------------------');
343 END IF;
344
345 -- Start for bug 8740185 PNAVEENK
346 if p_status = 'CLOSED' then
347 l_strategy_work_item_Rec.status_code := 'CANCELLED';
348 end if;
349 -- End for bug 8740185
350
351 IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
352 P_Api_Version_Number =>l_api_version_number,
353 P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
354 P_Init_Msg_List => p_init_msg_list, --FND_API.G_TRUE, bug 9462104
355 p_commit => p_commit, --FND_API.G_TRUE,
356 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
357 x_msg_count => l_msg_count,
358 x_msg_data => l_msg_data,
359 x_return_status => l_return_status,
360 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
361 -- IF PG_DEBUG < 10 THEN
362 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
363 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
364 'and Status =>'||l_return_status);
365 END IF;
366 IF l_return_status = FND_API.G_RET_STS_ERROR then
367 AddFailMsg( p_object => 'STRATEGY_WORK_ITEMS',
368 p_operation => 'UPDATE' );
369 raise FND_API.G_EXC_ERROR;
370 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
371 raise FND_API.G_EXC_UNEXPECTED_ERROR;
372 END IF;
373
374 END LOOP;
375 -- IF PG_DEBUG < 10 THEN
376 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
377 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'End of work items update ');
378 END IF;
379
380 l_strategy_Rec.strategy_id :=p_strategy_id;
381 l_strategy_Rec.status_code := p_status;
382
383 BEGIN
384 select object_version_number INTO l_object_version_number
385 FROM iex_strategies
386 where strategy_id =p_strategy_id;
387 EXCEPTION
388 WHEN NO_DATA_FOUND THEN
389 -- IF PG_DEBUG < 10 THEN
390 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
391 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'Required Parameter p_stragey_id is invalid');
392 END IF;
393 AddInvalidArgMsg(
394 p_api_name => l_api_name_full,
395 p_param_value => p_strategy_id,
396 p_param_name => 'p_stragey_id' );
397 RAISE FND_API.G_EXC_ERROR;
398 WHEN OTHERS THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END;
401
402 l_strategy_Rec.object_version_number := l_object_version_number;
403
404 -- IF PG_DEBUG < 10 THEN
405 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
406 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
407 END IF;
408 -- IF PG_DEBUG < 10 THEN
409 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
410 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || '---------------------------------');
411 END IF;
412
413 IEX_STRATEGY_PVT.Update_strategy(
414 P_Api_Version_Number =>l_api_version_number,
415 P_strategy_Rec =>l_strategy_Rec,
416 P_Init_Msg_List =>p_init_msg_list, -- FND_API.G_TRUE, Bug 14053089 bibeura
417 p_commit =>p_commit, -- FND_API.G_TRUE, Bug 14053089 bibeura
418 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
419 x_msg_count => l_msg_count,
420 x_msg_data => l_msg_data,
421 x_return_status => l_return_status,
422 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
423 -- IF PG_DEBUG < 10 THEN
424 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
425 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
426 'and Status =>'||l_return_status);
427 END IF;
428
429 IF l_return_status = FND_API.G_RET_STS_ERROR then
430 AddFailMsg( p_object => 'STRATEGY',
431 p_operation => 'UPDATE' );
432 raise FND_API.G_EXC_ERROR;
433 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
434 raise FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437
438 -- Standard check of p_commit
439 IF FND_API.To_Boolean(p_commit) THEN
440 COMMIT WORK;
441 END IF;
442 --Begin bug#5874874 gnramasa 25-Apr-2007
443 --Update the UWQ summary table after closing the strategy.
444 if l_strategy_work_item_Rec.work_item_id is not null then
445 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_strategy_work_item_Rec.work_item_id,p_commit);
446 end if;
447 --End bug#5874874 gnramasa 25-Apr-2007
448
449 -- Standard call to get message count and if count is 1, get message info
450 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
451
452
453 EXCEPTION
454 WHEN FND_API.G_EXC_ERROR THEN
455 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
458
459 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
460 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
462 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
463
464 WHEN OTHERS THEN
465 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
468 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
469 END IF;
470 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
471
472 END CLOSE_STRY_AND_WITEMS;
473
474
475 /**
476 update the stragey status to depending on the status passed
477 **/
478
479 PROCEDURE CLOSE_STRATEGY
480 (p_api_version IN NUMBER,
481 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
482 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
483 p_strategy_id IN NUMBER,
484 p_status IN VARCHAR2,
485 x_return_status OUT NOCOPY VARCHAR2,
486 x_msg_count OUT NOCOPY NUMBER,
487 x_msg_data OUT NOCOPY VARCHAR2
488 )IS
489
490 l_api_version NUMBER := 1.0;
491 l_api_name VARCHAR2(100) := 'CLOSE_STRATEGY';
492 --l_api_name_full VARCHAR2(2000) := g_pkg_name || '.' || l_api_name;
493 l_api_name_full VARCHAR2(100) := l_api_name;
494 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
495 l_return_status VARCHAR2(100);
496 l_msg_count NUMBER;
497 l_msg_data VARCHAR2(32767);
498 l_strategy_rec IEX_strategy_PVT.strategy_Rec_Type;
499 l_object_version_number NUMBER;
500
501 Cursor c_get_work_items (p_strategy_id NUMBER) is
502 SELECT work_item_id, object_version_number
503 FROM iex_strategy_work_items
504 WHERE strategy_id = p_strategy_id;
505
506
507 BEGIN
508
509 SAVEPOINT CLOSE_STRATEGY_PUB;
510
511 -- Standard call to check for call compatibility.
512 IF NOT FND_API.Compatible_API_Call (l_api_version,
513 p_api_version,
514 l_api_name,
515 G_PKG_NAME) THEN
516 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
517 END IF;
518
519 -- Check p_init_msg_list
520 IF FND_API.to_Boolean( p_init_msg_list ) THEN
521 FND_MSG_PUB.initialize;
522 END IF;
523
524 x_return_status := FND_API.G_RET_STS_SUCCESS;
525
526 -- Check for required parameter p_strategy_id
527 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
528 -- IF PG_DEBUG < 10 THEN
529 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
530 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_strategy_id is invalid');
531 END IF;
532 AddMissingArgMsg(
533 p_api_name => l_api_name_full,
534 p_param_name => 'p_strategy_id' );
535 RAISE FND_API.G_EXC_ERROR;
536 END IF;
537 -- IF PG_DEBUG < 10 THEN
538 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
539 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'after p_strategy_id check');
540 END IF;
541
542 -- Check for required parameter p_status_id
543 IF (p_status IS NULL) OR (p_status = FND_API.G_MISS_CHAR) THEN
544 -- IF PG_DEBUG < 10 THEN
545 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
546 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_status is invalid');
547 END IF;
548 AddMissingArgMsg(
549 p_api_name => l_api_name_full,
550 p_param_name => 'p_status' );
551 RAISE FND_API.G_EXC_ERROR;
552 END IF;
553 -- IF PG_DEBUG < 10 THEN
554 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
555 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'after p_status check');
556 END IF;
557
558 l_strategy_Rec.strategy_id :=p_strategy_id;
559 l_strategy_Rec.status_code := p_status;
560
561 BEGIN
562 select object_version_number INTO l_object_version_number
563 FROM iex_strategies
564 where strategy_id =p_strategy_id;
565 EXCEPTION
566 WHEN NO_DATA_FOUND THEN
567 -- IF PG_DEBUG < 10 THEN
568 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
569 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_stragey_id is invalid');
570 END IF;
571 AddInvalidArgMsg(
572 p_api_name => l_api_name_full,
573 p_param_value => p_strategy_id,
574 p_param_name => 'p_stragey_id' );
575 RAISE FND_API.G_EXC_ERROR;
576 WHEN OTHERS THEN
577 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
578 END;
579
580 l_strategy_Rec.object_version_number := l_object_version_number;
581
582 -- IF PG_DEBUG < 10 THEN
583 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
584 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
585 END IF;
586 -- IF PG_DEBUG < 10 THEN
587 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
588 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || '---------------------------------');
589 END IF;
590
591 IEX_STRATEGY_PVT.Update_strategy(
592 P_Api_Version_Number =>2.0,
593 P_Init_Msg_List => p_init_msg_list, -- FND_API.G_TRUE, Bug 14053089 bibeura
594 p_commit => p_commit, -- FND_API.G_TRUE, Bug 14053089 bibeura
595 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
596 P_strategy_Rec =>l_strategy_Rec,
597 x_msg_count => l_msg_count,
598 x_msg_data => l_msg_data,
599 x_return_status => l_return_status,
600 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
601 -- IF PG_DEBUG < 10 THEN
602 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
603 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
604 'and Status =>'||l_return_status);
605 END IF;
606
607 IF l_return_status = FND_API.G_RET_STS_ERROR then
608 AddFailMsg( p_object => 'STRATEGY',
609 p_operation => 'UPDATE' );
610 raise FND_API.G_EXC_ERROR;
611 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
612 raise FND_API.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615
616 -- Standard check of p_commit
617 IF FND_API.To_Boolean(p_commit) THEN
618 COMMIT WORK;
619 END IF;
620
621 -- Standard call to get message count and if count is 1, get message info
622 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
623
624
625 EXCEPTION
626 WHEN FND_API.G_EXC_ERROR THEN
627 ROLLBACK TO CLOSE_STRATEGY_PUB;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
630
631 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
632 ROLLBACK TO CLOSE_STRATEGY_PUB;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
635
636 WHEN OTHERS THEN
637 ROLLBACK TO CLOSE_STRATEGY_PUB;
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
640 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
641 END IF;
642 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
643
644 END CLOSE_STRATEGY;
645
646
647 /**
648 update all the work_item status to depending on the status passed
649
650 **/
651
652 PROCEDURE UPDATE_WORK_ITEM
653 (p_api_version IN NUMBER,
654 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE,
655 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
656 p_work_item_id IN NUMBER,
657 p_status IN VARCHAR2,
658 x_return_status OUT NOCOPY VARCHAR2,
659 x_msg_count OUT NOCOPY NUMBER,
660 x_msg_data OUT NOCOPY VARCHAR2
661 )IS
662
663 l_api_version CONSTANT NUMBER := 1.0;
664 l_api_name VARCHAR2(100) := 'UPDATE_WORK_ITEM';
665 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
666 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
667 l_return_status VARCHAR2(1);
668 l_msg_count NUMBER;
669 l_msg_data VARCHAR2(32767);
670 l_api_version_number CONSTANT NUMBER := 2.0;
671 l_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type;
672 l_object_version_number NUMBER;
673
674 Cursor c_get_work_items (p_work_item_id NUMBER) is
675 SELECT object_version_number
676 FROM iex_strategy_work_items
677 WHERE work_item_id =p_work_item_id;
678
679
680
681 BEGIN
682
683 SAVEPOINT UPDATE_WORK_ITEM_PUB;
684
685 -- Standard call to check for call compatibility.
686 IF NOT FND_API.Compatible_API_Call (l_api_version,
687 p_api_version,
688 l_api_name,
689 G_PKG_NAME) THEN
690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
691 END IF;
692
693 -- Check p_init_msg_list
694 IF FND_API.to_Boolean( p_init_msg_list ) THEN
695 FND_MSG_PUB.initialize;
696 END IF;
697 -- IF PG_DEBUG < 10 THEN
698 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
699 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after init');
700 END IF;
701
702 x_return_status := FND_API.G_RET_STS_SUCCESS;
703
704 -- Check for required parameter p_strategy_id
705 IF (p_work_item_id IS NULL) OR (p_work_item_id = FND_API.G_MISS_NUM) THEN
706 -- IF PG_DEBUG < 10 THEN
707 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
708 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
709 END IF;
710 AddMissingArgMsg(
711 p_api_name => l_api_name_full,
712 p_param_name => 'p_work_item_id' );
713 RAISE FND_API.G_EXC_ERROR;
714 END IF;
715 -- IF PG_DEBUG < 10 THEN
716 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
717 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_work_item_id check');
718 END IF;
719
720 -- Check for required parameter p_status_id
721 IF (p_status IS NULL) OR (p_status = FND_API.G_MISS_CHAR) THEN
722 -- IF PG_DEBUG < 10 THEN
723 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
724 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_status is invalid');
725 END IF;
726 AddMissingArgMsg(
727 p_api_name => l_api_name_full,
728 p_param_name => 'p_status' );
729 RAISE FND_API.G_EXC_ERROR;
730 END IF;
731 -- IF PG_DEBUG < 10 THEN
732 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
733 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_status check');
734 END IF;
735
736 FOR c_get_work_items_rec in c_get_work_items (p_work_item_id)
737 LOOP
738 l_strategy_work_item_Rec.work_item_id :=p_work_item_id;
739 l_strategy_work_item_Rec.status_code := p_status;
740 l_strategy_work_item_Rec.object_version_number
741 :=c_get_work_items_rec.object_version_number;
742 l_strategy_work_item_Rec.execute_end := sysdate;
743 -- IF PG_DEBUG < 10 THEN
744 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
745 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items');
746 END IF;
747 -- IF PG_DEBUG < 10 THEN
748 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
749 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || '---------------------------------');
750 END IF;
751
752 IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
753 P_Api_Version_Number =>l_api_version_number,
754 P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
755 P_Init_Msg_List => p_init_msg_list, --FND_API.G_TRUE,
756 p_commit => p_commit , --FND_API.G_TRUE,
757 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
758 x_msg_count => l_msg_count,
759 x_msg_data => l_msg_data,
760 x_return_status => l_return_status,
761 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
762 -- IF PG_DEBUG < 10 THEN
763 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
764 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
765 'and Status =>'||l_return_status);
766 END IF;
767 IF l_return_status = FND_API.G_RET_STS_ERROR then
768 AddFailMsg( p_object => 'STRATEGY_WORK_ITEMS',
769 p_operation => 'UPDATE' );
770 raise FND_API.G_EXC_ERROR;
771 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
772 raise FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 END LOOP;
776 -- IF PG_DEBUG < 10 THEN
777 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
778 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'End of work items update ');
779 END IF;
780
781
782
783 -- Standard check of p_commit
784 IF FND_API.To_Boolean(p_commit) THEN
785 COMMIT WORK;
786 END IF;
787
788 -- Standard call to get message count and if count is 1, get message info
789 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
790
791
792 EXCEPTION
793 WHEN FND_API.G_EXC_ERROR THEN
794 --ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
795 ROLLBACK TO UPDATE_WORK_ITEM_PUB;
796 x_return_status := FND_API.G_RET_STS_ERROR;
797 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
798
799 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
800 --ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
801 ROLLBACK TO UPDATE_WORK_ITEM_PUB;
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
803 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
804
805 WHEN OTHERS THEN
806 --ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
807 ROLLBACK TO UPDATE_WORK_ITEM_PUB;
808 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
809 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
810 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
811 END IF;
812 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
813
814 END UPDATE_WORK_ITEM;
815 /**
816 * update next work item in the strategy table
817 * when a work item is created
818 **/
819 PROCEDURE UPDATE_NEXT_WORK_ITEM
820 (p_api_version IN NUMBER,
821 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
822 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
823 p_work_item_id IN NUMBER,
824 p_strategy_id IN NUMBER,
825 x_return_status OUT NOCOPY VARCHAR2,
826 x_msg_count OUT NOCOPY NUMBER,
827 x_msg_data OUT NOCOPY VARCHAR2
828 ) IS
829
830 l_api_version NUMBER := 1.0;
831 l_api_name VARCHAR2(100) := 'UPDATE_NEXT_WORK_ITEM';
832 --l_api_name_full VARCHAR2(2000) := g_pkg_name || '.' || l_api_name;
833 l_api_name_full VARCHAR2(100) := l_api_name;
834 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
835 l_return_status VARCHAR2(100);
836 l_msg_count NUMBER;
837 l_msg_data VARCHAR2(32767);
838 l_strategy_rec IEX_strategy_PVT.strategy_Rec_Type;
839 l_object_version_number NUMBER;
840
841 BEGIN
842
843
844 SAVEPOINT UPDATE_NEXT_WORK_ITEM_PUB;
845
846 -- Standard call to check for call compatibility.
847 IF NOT FND_API.Compatible_API_Call (l_api_version,
848 p_api_version,
849 l_api_name,
850 G_PKG_NAME) THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854 -- Check p_init_msg_list
855 IF FND_API.to_Boolean( p_init_msg_list ) THEN
856 FND_MSG_PUB.initialize;
857 END IF;
858
859 x_return_status := FND_API.G_RET_STS_SUCCESS;
860
861 -- Check for required parameter p_strategy_id
862 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
863 -- IF PG_DEBUG < 10 THEN
864 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
865 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_strategy_id is invalid');
866 END IF;
867 AddMissingArgMsg(
868 p_api_name => l_api_name_full,
869 p_param_name => 'p_strategy_id' );
870 RAISE FND_API.G_EXC_ERROR;
871 END IF;
872 -- IF PG_DEBUG < 10 THEN
873 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
874 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_strategy_id check');
875 END IF;
876
877 -- Check for required parameter p_work_item_id
878 IF (p_work_item_id IS NULL) OR (p_work_item_id = FND_API.G_MISS_NUM) THEN
879 -- IF PG_DEBUG < 10 THEN
880 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
881 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
882 END IF;
883 AddMissingArgMsg(
884 p_api_name => l_api_name_full,
885 p_param_name => 'p_work_item_id' );
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888 -- IF PG_DEBUG < 10 THEN
889 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
890 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_work_item_id check');
891 END IF;
892 l_strategy_Rec.strategy_id :=p_strategy_id;
893 l_strategy_Rec.next_work_item_id := p_work_item_id;
894
895 BEGIN
896 select object_version_number INTO l_object_version_number
897 FROM iex_strategies
898 where strategy_id =p_strategy_id;
899 EXCEPTION
900 WHEN NO_DATA_FOUND THEN
901 -- IF PG_DEBUG < 10 THEN
902 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
903 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_stratgey_id is invalid');
904 END IF;
905 AddInvalidArgMsg(
906 p_api_name => l_api_name_full,
907 p_param_value => p_strategy_id,
908 p_param_name => 'p_stragey_id' );
909 RAISE FND_API.G_EXC_ERROR;
910 WHEN OTHERS THEN
911 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
912 END;
913
914 l_strategy_Rec.object_version_number := l_object_version_number;
915
916 -- IF PG_DEBUG < 10 THEN
917 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
918 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
919 END IF;
920 -- IF PG_DEBUG < 10 THEN
921 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
922 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || '---------------------------------');
923 END IF;
924
925 IEX_STRATEGY_PVT.Update_strategy(
926 P_Api_Version_Number =>2.0,
927 P_Init_Msg_List =>p_init_msg_list, -- FND_API.G_TRUE, Bug 14053089 bibeura
928 p_commit =>p_commit, -- FND_API.G_TRUE, Bug 14053089 bibeura
929 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
930 P_strategy_Rec =>l_strategy_Rec,
931 x_msg_count => l_msg_count,
932 x_msg_data => l_msg_data,
933 x_return_status => l_return_status,
934 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
935 -- IF PG_DEBUG < 10 THEN
936 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
937 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
938 'and Status =>'||l_return_status);
939 END IF;
940
941 IF l_return_status = FND_API.G_RET_STS_ERROR then
942 AddFailMsg( p_object => 'STRATEGY',
943 p_operation => 'UPDATE' );
944 raise FND_API.G_EXC_ERROR;
945 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
946 raise FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948
949
950 -- Standard check of p_commit
951 IF FND_API.To_Boolean(p_commit) THEN
952 COMMIT WORK;
953 END IF;
954
955 -- Standard call to get message count and if count is 1, get message info
956 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
957
958
959 EXCEPTION
960 WHEN FND_API.G_EXC_ERROR THEN
961 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
962 x_return_status := FND_API.G_RET_STS_ERROR;
963 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
964
965 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
966 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
967 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
968 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
969
970 WHEN OTHERS THEN
971 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
973 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
974 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
975 END IF;
976 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
977
978 END UPDATE_NEXT_WORK_ITEM;
979
980
981 /**
982 **check all the work_items for the given strategy for status in
983 ** CANCELLED,COMPLETE.
984 ** set the return value to 0 if the all the work items are
985 ** exhausted
986 **/
987 FUNCTION CHECK_WORK_ITEM_STATUS(
988 p_strategy_id IN NUMBER
989 )RETURN NUMBER IS
990
991 x_work_item_done NUMBER :=0;
992 BEGIN
993 SELECT count(*) into x_work_item_done
994 FROM iex_strategy_work_items
995 WHERE strategy_id = p_strategy_id
996 and status_code not in ('CANCELLED','COMPLETE');
997
998 return x_work_item_done;
999
1000 END CHECK_WORK_ITEM_STATUS;
1001
1002 FUNCTION get_Date (p_date IN DATE,
1003 l_UOM varchar2,
1004 l_unit number) return date
1005 IS
1006 r_date Date;
1007 l_conversion number := 0;
1008 l_jtf_time_uom_class varchar2(255); --Added for Bug 7434190 22-Jan-2009 barathsr
1009 begin
1010
1011 select sysdate into r_date from dual; -- default to sysdate;
1012
1013 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1014 iex_debug_pub.logmessage ('get_date before get conversion rate ');
1015 END IF;
1016 --Start of bug 7434190 22-Jan-2009 barathsr
1017 l_jtf_time_uom_class := fnd_profile.value_specific(NAME => 'JTF_TIME_UOM_CLASS',APPLICATION_ID =>695);
1018 select conversion_rate into l_conversion from mtl_uom_conversions
1019 --Use the profile 'unit of measure class' value if it is set at application level, else use it from site level
1020 /* where UOM_code = l_UOM and uom_class = (select fnd_profile.value('JTF_TIME_UOM_CLASS') from dual) */
1021 where UOM_code = l_UOM and uom_class = l_jtf_time_uom_class
1022 --End of Bug 7434190 22-Jan-2009 barathsr
1023 and inventory_item_id = 0;
1024 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1025 iex_debug_pub.logmessage ('get_date l_conversion => '|| l_conversion);
1026 END IF;
1027
1028 select p_date + l_conversion * l_unit / 24 into r_date from dual;
1029 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1030 iex_debug_pub.logmessage ('get_date => '|| to_char(r_date, 'yyyy/mm/dd/hh24:mi:ss'));
1031 END IF;
1032
1033 return r_date;
1034 exception when others THEN
1035 r_date :=p_date;
1036 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1037 iex_debug_pub.logmessage ('get_date exception return sysdate');
1038 END IF;
1039 return r_date;
1040
1041 end get_date;
1042
1043 /** subscription function example
1044 *
1045 **/
1046 FUNCTION create_workitem_check
1047 ( p_subscription_guid in raw,
1048 p_event in out NOCOPY wf_event_t)
1049 return varchar2
1050 is
1051 l_key varchar2(240) := p_event.GetEventKey();
1052 x_return_status VARCHAR2(10) := 'S';
1053 x_msg_count NUMBER;
1054 x_msg_data VARCHAR2(2000);
1055 exc EXCEPTION;
1056
1057
1058 l_del_id NUMBER;
1059 l_strategy_id NUMBER;
1060 l_workitem_id NUMBER;
1061
1062 begin
1063 -- put custom code
1064 -- this is just an example
1065 -- writes into the log file
1066 l_del_id := p_event.GetValueForParameter('DELINQUENCY_ID');
1067 l_strategy_id := p_event.GetValueForParameter('STRATEGY_ID');
1068 l_workitem_id := p_event.GetValueForParameter('WORK_ITEMID');
1069
1070 --IF PG_DEBUG < 10 THEN
1071 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1072 iex_debug_pub.logmessage ('create_workitem_check: ' || 'EVENT NAME =>'||p_event.getEventName());
1073 END IF;
1074 --IF PG_DEBUG < 10 THEN
1075 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1076 iex_debug_pub.logmessage ('create_workitem_check: ' || 'DELID =>' || l_del_id);
1077 END IF;
1078 --IF PG_DEBUG < 10 THEN
1079 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1080 iex_debug_pub.logmessage ('create_workitem_check: ' || 'strategy ID =>' ||l_strategy_id );
1081 END IF;
1082 --IF PG_DEBUG < 10 THEN
1083 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1084 iex_debug_pub.logmessage ('create_workitem_check: ' || 'work item ID =>' || l_workitem_id);
1085 END IF;
1086
1087 IF x_return_status <> 'S' THEN
1088 RAISE EXC;
1089 END IF;
1090 RETURN 'SUCCESS';
1091
1092 EXCEPTION
1093 WHEN EXC THEN
1094 -- IF PG_DEBUG < 10 THEN
1095 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1096 iex_debug_pub.logmessage ('create_workitem_check: ' || 'raised exe error');
1097 END IF;
1098 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_check', p_event.getEventName(), p_subscription_guid);
1099 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1100 RETURN 'ERROR';
1101 WHEN OTHERS THEN
1102 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_check', p_event.getEventName(), p_subscription_guid);
1103 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1104 RETURN 'ERROR';
1105
1106
1107 END create_workitem_check;
1108
1109 /** subscription function example
1110 * for complete work item
1111 **/
1112 FUNCTION create_workitem_complete
1113 ( p_subscription_guid in raw,
1114 p_event in out NOCOPY wf_event_t)
1115 return varchar2
1116 is
1117 l_key varchar2(240) := p_event.GetEventKey();
1118 x_return_status VARCHAR2(10) := 'S';
1119 x_msg_count NUMBER;
1120 x_msg_data VARCHAR2(2000);
1121 exc EXCEPTION;
1122
1123
1124 l_del_id NUMBER;
1125 l_strategy_id NUMBER;
1126 l_workitem_id NUMBER;
1127
1128 begin
1129 -- put custom code
1130 -- this is just an example
1131 -- writes into the log file
1132
1133 l_del_id := p_event.GetValueForParameter('DELINQUENCY_ID');
1134 l_strategy_id := p_event.GetValueForParameter('STRATEGY_ID');
1135 l_workitem_id := p_event.GetValueForParameter('WORK_ITEMID');
1136
1137 --IF PG_DEBUG < 10 THEN
1138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1139 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'EVENT NAME =>'||p_event.getEventName());
1140 END IF;
1141 --IF PG_DEBUG < 10 THEN
1142 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1143 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'DELID =>' || l_del_id);
1144 END IF;
1145 --IF PG_DEBUG < 10 THEN
1146 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1147 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'strategy ID =>' ||l_strategy_id );
1148 END IF;
1149 --IF PG_DEBUG < 10 THEN
1150 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1151 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'work item ID =>' || l_workitem_id);
1152 END IF;
1153
1154 IF x_return_status <> 'S' THEN
1155 RAISE EXC;
1156 END IF;
1157 RETURN 'SUCCESS';
1158
1159 EXCEPTION
1160 WHEN EXC THEN
1161 -- IF PG_DEBUG < 10 THEN
1162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1163 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'raised exe error');
1164 END IF;
1165 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_complete', p_event.getEventName(), p_subscription_guid);
1166 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1167 RETURN 'ERROR';
1168 WHEN OTHERS THEN
1169 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_complete', p_event.getEventName(), p_subscription_guid);
1170 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1171 RETURN 'ERROR';
1172
1173
1174 END create_workitem_complete;
1175
1176 --Begin bug#5874874 gnramasa 25-Apr-2007
1177 --Clear the Strategy related data in UWQ summary table.
1178 procedure clear_uwq_str_summ(p_object_id in number,p_object_type in varchar2) is
1179 begin
1180 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1181 iex_debug_pub.logmessage ('**** BEGIN : IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM ************');
1182 iex_debug_pub.logmessage ('IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM object_type='||p_object_type);
1183 iex_debug_pub.logmessage ('IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM object_id='||p_object_id);
1184 END IF;
1185
1186 IF p_object_type = 'PARTY' THEN
1187
1188 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1189 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing party level strategy uwq summary ************');
1190 END IF;
1191 UPDATE IEX_DLN_UWQ_SUMMARY
1192 SET WORK_ITEM_ID = null,
1193 SCHEDULE_START = null,
1194 SCHEDULE_END = null,
1195 WORK_TYPE = null,
1196 CATEGORY_TYPE = null,
1197 PRIORITY_TYPE = null,
1198 WKITEM_RESOURCE_ID = null,
1199 STRATEGY_ID = null,
1200 STRATEGY_TEMPLATE_ID = null,
1201 WORK_ITEM_TEMPLATE_ID = null,
1202 STATUS_CODE = null,
1203 STR_STATUS = null, -- Added for bug#7416344 by PNAVEENK
1204 START_TIME = null,
1205 END_TIME = null,
1206 WORK_ITEM_ORDER = null,
1207 WKITEM_ESCALATED_YN = null
1208 WHERE PARTY_ID = p_object_id;
1209
1210 ELSIF p_object_type = 'IEX_ACCOUNT' THEN
1211 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1212 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing account level strategy uwq summary ************');
1213 END IF;
1214 UPDATE IEX_DLN_UWQ_SUMMARY
1215 SET WORK_ITEM_ID = null,
1216 SCHEDULE_START = null,
1217 SCHEDULE_END = null,
1218 WORK_TYPE = null,
1219 CATEGORY_TYPE = null,
1220 PRIORITY_TYPE = null,
1221 WKITEM_RESOURCE_ID = null,
1222 STRATEGY_ID = null,
1223 STRATEGY_TEMPLATE_ID = null,
1224 WORK_ITEM_TEMPLATE_ID = null,
1225 STATUS_CODE = null,
1226 STR_STATUS = null, -- Added for bug#7416344 by PNAVEENK
1227 START_TIME = null,
1228 END_TIME = null,
1229 WORK_ITEM_ORDER = null,
1230 WKITEM_ESCALATED_YN = null
1231 WHERE CUST_ACCOUNT_ID = p_object_id;
1232
1233 ELSIF p_object_type = 'IEX_BILLTO' THEN
1234 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1235 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing billto level strategy uwq summary ************');
1236 END IF;
1237 UPDATE IEX_DLN_UWQ_SUMMARY
1238 SET WORK_ITEM_ID = null,
1239 SCHEDULE_START = null,
1240 SCHEDULE_END = null,
1241 WORK_TYPE = null,
1242 CATEGORY_TYPE = null,
1243 PRIORITY_TYPE = null,
1244 WKITEM_RESOURCE_ID = null,
1245 STRATEGY_ID = null,
1246 STRATEGY_TEMPLATE_ID = null,
1247 WORK_ITEM_TEMPLATE_ID = null,
1248 STATUS_CODE = null,
1249 STR_STATUS = null, -- Added for bug#7416344 by PNAVEENK
1250 START_TIME = null,
1251 END_TIME = null,
1252 WORK_ITEM_ORDER = null,
1253 WKITEM_ESCALATED_YN = null
1254 WHERE SITE_USE_ID = p_object_id;
1255 END IF;
1256 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1257 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing party level strategy uwq summary ************');
1258 END IF;
1259 exception
1260 when others then
1261 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1262 iex_debug_pub.logmessage ('**** EXCEPTION: IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM ************');
1263 END IF;
1264 end;
1265
1266 --Update the Strategy related data in UWQ summary table.
1267 procedure refresh_uwq_str_summ(p_workitem_id in number,
1268 p_commit IN VARCHAR2 DEFAULT 'T') is
1269
1270 CURSOR c_strategy_summary(p_work_item_id number) IS
1271 select strat.jtf_object_id,
1272 strat.jtf_object_type,
1273 wkitem.WORK_ITEM_ID,
1274 wkitem.schedule_start schedule_start,
1275 wkitem.schedule_end schedule_end,
1276 stry_temp_wkitem.category_type category,
1277 stry_temp_wkitem.WORK_TYPE,
1278 stry_temp_wkitem.PRIORITY_TYPE,
1279 wkitem.resource_id,
1280 wkitem.strategy_id,
1281 strat.strategy_template_id,
1282 wkitem.work_item_template_id,
1283 wkitem.status_code workitem_status,
1284 strat.status_code startegy_status,
1285 wkitem.creation_date start_time,
1286 wkitem.execute_end end_time, -- snuthala 28/08/2008 bug #6745580
1287 wkitem.work_item_order wkitem_order,
1288 wkitem.escalated_yn escalated_yn
1289 from iex_strategies strat,
1290 iex_strategy_work_items wkitem,
1291 iex_stry_temp_work_items_b stry_temp_wkitem
1292 where wkitem.work_item_id=p_work_item_id
1293 AND wkitem.strategy_id = strat.strategy_id
1294 AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id;
1295
1296 l_jtf_object_id number;
1297 l_jtf_object_type varchar2(30);
1298 l_work_item_id number;
1299 l_schedule_start date;
1300 l_schedule_end date;
1301 l_work_type varchar2(30);
1302 l_category_type varchar2(30);
1303 l_priority_type varchar2(30);
1304 l_wkitem_resource_id number;
1305 l_strategy_id number;
1306 l_strategy_template_id number;
1307 l_work_item_template_id number;
1308 l_workitem_status varchar2(30);
1309 l_strategy_status varchar2(30);
1310 l_start_time date;
1311 l_end_time date;
1312 l_work_item_order number;
1313 l_escalated_yn varchar2(1);
1314 begin
1315
1316 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1317 iex_debug_pub.logmessage ('**** BEGIN refresh_uwq_str_summ ************');
1318 END IF;
1319
1320 open c_strategy_summary(p_workitem_id);
1321 fetch c_strategy_summary into
1322 l_jtf_object_id,
1323 l_jtf_object_type,
1324 l_work_item_id,
1325 l_schedule_start,
1326 l_schedule_end,
1327 l_category_type,
1328 l_work_type,
1329 l_priority_type,
1330 l_wkitem_resource_id,
1331 l_strategy_id,
1332 l_strategy_template_id,
1333 l_work_item_template_id,
1334 l_workitem_status,
1335 l_strategy_status,
1336 l_start_time,
1337 l_end_time,
1338 l_work_item_order,
1339 l_escalated_yn;
1340
1341 if l_strategy_status not in ('OPEN','ONHOLD') or l_workitem_status<>'OPEN' or l_work_type='AUTOMATIC' then
1342 close c_strategy_summary;
1343 clear_uwq_str_summ(l_jtf_object_id,l_jtf_object_type);
1344 -- Added IF condition for Bug #14053089 bibeura
1345 IF FND_API.To_Boolean(p_commit) THEN
1346 COMMIT WORK;
1347 END IF;
1348 return;
1349 end if;
1350 IF l_jtf_object_type = 'PARTY' THEN
1351 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1352 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating party level strategy uwq summary ************');
1353 END IF;
1354 update iex_dln_uwq_summary
1355 set work_item_id = l_work_item_id,
1356 schedule_start = l_schedule_start,
1357 schedule_end = l_schedule_end,
1358 work_type = l_work_type,
1359 category_type = l_category_type,
1360 priority_type = l_priority_type,
1361 wkitem_resource_id = l_wkitem_resource_id,
1362 strategy_id = l_strategy_id,
1363 strategy_template_id = l_strategy_template_id,
1364 work_item_template_id = l_work_item_template_id,
1365 status_code = l_workitem_status,
1366 str_status = l_strategy_status, -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1367 start_time = l_start_time,
1368 end_time = l_end_time,
1369 work_item_order = l_work_item_order,
1370 wkitem_escalated_yn = l_escalated_yn
1371 where party_id = l_jtf_object_id;
1372
1373 ELSIF l_jtf_object_type = 'IEX_ACCOUNT' THEN
1374 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1375 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating account level strategy uwq summary ************');
1376 END IF;
1377 update iex_dln_uwq_summary
1378 set work_item_id = l_work_item_id,
1379 schedule_start = l_schedule_start,
1380 schedule_end = l_schedule_end,
1381 work_type = l_work_type,
1382 category_type = l_category_type,
1383 priority_type = l_priority_type,
1384 wkitem_resource_id = l_wkitem_resource_id,
1385 strategy_id = l_strategy_id,
1386 strategy_template_id = l_strategy_template_id,
1387 work_item_template_id = l_work_item_template_id,
1388 status_code = l_workitem_status,
1389 str_status = l_strategy_status, -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1390 start_time = l_start_time,
1391 end_time = l_end_time,
1392 work_item_order = l_work_item_order,
1393 wkitem_escalated_yn = l_escalated_yn
1394 where cust_account_id = l_jtf_object_id;
1395
1396 ELSIF l_jtf_object_type = 'IEX_BILLTO' THEN
1397 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1398 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating billto level strategy uwq summary ************');
1399 END IF;
1400 update iex_dln_uwq_summary
1401 set work_item_id = l_work_item_id,
1402 schedule_start = l_schedule_start,
1403 schedule_end = l_schedule_end,
1404 work_type = l_work_type,
1405 category_type = l_category_type,
1406 priority_type = l_priority_type,
1407 wkitem_resource_id = l_wkitem_resource_id,
1408 strategy_id = l_strategy_id,
1409 strategy_template_id = l_strategy_template_id,
1410 work_item_template_id = l_work_item_template_id,
1411 status_code = l_workitem_status,
1412 str_status = l_strategy_status, -- Added for bug#7416344 by PNAVEENK on 16-3-2009
1413 start_time = l_start_time,
1414 end_time = l_end_time,
1415 work_item_order = l_work_item_order,
1416 wkitem_escalated_yn = l_escalated_yn
1417 where site_use_id = l_jtf_object_id;
1418
1419 END IF;
1420 close c_strategy_summary;
1421 -- Added IF condition for Bug #14053089 bibeura
1422 IF FND_API.To_Boolean(p_commit) THEN
1423 COMMIT WORK;
1424 END IF;
1425 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1426 iex_debug_pub.logmessage ('**** END refresh_uwq_str_summ ************');
1427 END IF;
1428 exception
1429 when others then
1430
1431 iex_debug_pub.logmessage ('**** EXCEPTION refresh_uwq_str_summ ************');
1432
1433 end;
1434 --End bug#5874874 gnramasa 25-Apr-2007
1435
1436 /** reassagin strategy
1437 * send signal first
1438 * then call create_Strategy_pub
1439 * to create the new strategy
1440 * the new strategy will launch the work flow*
1441 **/
1442 /*
1443 PROCEDURE REASSIGN_STRATEGY( p_strategy_id IN NUMBER,
1444 p_status IN VARCHAR2,
1445 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1446 x_return_status OUT NOCOPY VARCHAR2,
1447 x_msg_count OUT NOCOPY NUMBER,
1448 x_msg_data OUT NOCOPY VARCHAR2) IS
1449
1450 l_object_type varchar2(30) := 'DELINQUENT' ;
1451 l_object_id number ;
1452 l_delinquency_id number ;
1453 l_return_status VARCHAR2(1);
1454 l_msg_count NUMBER;
1455 l_msg_data VARCHAR2(32767);
1456 l_status varchar2(100) ;
1457
1458 cursor c_status(p_strategy_id IN NUMBER) is
1459 select status_code
1460 from iex_strategies
1461 where strategy_id = p_strategy_id ;
1462
1463 cursor c_object(p_strategy_id IN NUMBER) is
1464 select delinquency_id,object_id,object_type
1465 from iex_strategies
1466 where strategy_id = p_strategy_id ;
1467
1468 BEGIN
1469
1470 SAVEPOINT REASSIGN_STRATEGY_PUB;
1471
1472 x_return_status := FND_API.G_RET_STS_ERROR;
1473
1474
1475 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1476 strategy_id => p_strategy_id,
1477 status => p_status ) ;
1478
1479 -- IF PG_DEBUG < 10 THEN
1480 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1481 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'AFTER SEND SIGNAL');
1482 END IF;
1483
1484 OPEN c_status(p_strategy_id);
1485 FETCH c_status INTO l_status;
1486 CLOSE c_status;
1487
1488 if ( l_status = 'CANCELLED' ) then
1489 -- IF PG_DEBUG < 10 THEN
1490 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1491 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'After Send Signal and it successfull ');
1492 END IF;
1493 OPEN c_object(p_strategy_id);
1494 FETCH c_object INTO l_delinquency_id,l_object_id,l_object_type;
1495 CLOSE c_object;
1496
1497 -- IF PG_DEBUG < 10 THEN
1498 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1499 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
1500 'before calling create strategy is '
1501 ||l_delinquency_id||
1502 ' object_id is '||l_object_id ||
1503 ' object_type is' || l_object_type );
1504 END IF;
1505
1506 -- IF PG_DEBUG < 10 THEN
1507 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1508 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
1509 END IF;
1510
1511 IEX_STRATEGY_PUB.CREATE_STRATEGY
1512 (p_api_version_number => 2.0,
1513 p_init_msg_list => FND_API.G_TRUE,
1514 p_commit => p_commit,
1515 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1516 p_delinquencyId => l_delinquency_id ,
1517 p_objecttype => l_object_type,
1518 p_objectid => l_object_id ,
1519 x_return_status => l_return_status,
1520 x_msg_count => l_msg_count,
1521 x_msg_data => l_msg_data) ;
1522
1523 -- IF PG_DEBUG < 10 THEN
1524 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1525 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
1526 END IF;
1527
1528 x_msg_count :=l_msg_count;
1529 x_msg_data :=l_msg_data;
1530 x_return_status :=l_return_status;
1531
1532
1533 ELSE
1534 ROLLBACK TO REASSIGN_STRATEGY_PUB;
1535 RETURN;
1536 END if; --l_status =cancelled
1537
1538 -- Standard check of p_commit
1539 IF FND_API.To_Boolean(p_commit) THEN
1540 COMMIT WORK;
1541 END IF;
1542
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 ROLLBACK TO REASSIGN_STRATEGY_PUB;
1546 -- IF PG_DEBUG < 10 THEN
1547 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1548 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
1549 END IF;
1550 END REASSIGN_STRATEGY;
1551 */
1552 /** update work item and call send signal
1553 * if send signal fails, roolback the work item
1554 **/
1555
1556 /*
1557 PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec IN
1558 iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
1559 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1560 x_return_status OUT NOCOPY VARCHAR2,
1561 x_msg_count OUT NOCOPY NUMBER,
1562 x_msg_data OUT NOCOPY VARCHAR2)IS
1563
1564 l_return_status VARCHAR2(1);
1565 l_msg_count NUMBER;
1566 l_msg_data VARCHAR2(32767);
1567 l_status varchar2(100) ;
1568 l_object_version_number NUMBER;
1569 v_result NUMBER;
1570 BEGIN
1571
1572 SAVEPOINT UPDATE_AND_SENDSIGNAL;
1573
1574 x_return_status := FND_API.G_RET_STS_SUCCESS;
1575
1576 iex_strategy_work_items_pvt.update_strategy_work_items(
1577 p_api_version_number => 2.0,
1578 p_init_msg_list => FND_API.G_TRUE,
1579 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1580 p_commit => p_commit,
1581 x_return_status => l_return_status,
1582 x_msg_count => l_msg_count,
1583 x_msg_data => l_msg_data,
1584 p_strategy_work_item_rec => p_strategy_work_item_rec,
1585 xo_object_version_number => l_object_version_number);
1586
1587 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1588 x_msg_count :=l_msg_count;
1589 x_msg_data :=l_msg_data;
1590 x_return_status :=l_return_status;
1591 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
1592 return;
1593 ELSE
1594 --call send signal
1595 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1596 strategy_id => p_strategy_work_item_rec.strategy_id,
1597 status => p_strategy_work_item_rec.status_code,
1598 work_item_id => p_strategy_work_item_rec.work_item_id);
1599
1600 -- IF PG_DEBUG < 10 THEN
1601 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1602 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
1603 END IF;
1604 --check if it the strategy is open
1605 -- and next work item is not the same as the updated work item
1606 -- then the send signal has been successful and it has created
1607 -- the next work item . other wise, the send signal failed.
1608 -- id send signal is successful, commit , else rollback
1609 select count(*) INTO v_result from iex_strategies
1610 where strategy_id =p_strategy_work_item_rec.strategy_id
1611 and next_work_item_id =p_strategy_work_item_rec.work_item_id
1612 and status_code ='OPEN';
1613
1614 if v_result >0 THEN
1615 -- IF PG_DEBUG < 10 THEN
1616 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1617 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
1618 END IF;
1619 rollback to UPDATE_AND_SENDSIGNAL;
1620 x_return_status := FND_API.G_RET_STS_ERROR;
1621 return;
1622 else
1623 -- IF PG_DEBUG < 10 THEN
1624 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1625 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
1626 END IF;
1627 end if;
1628 END IF; --if status is successful
1629
1630 -- Standard check of p_commit
1631 IF FND_API.To_Boolean(p_commit) THEN
1632 COMMIT WORK;
1633 END IF;
1634
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
1638 -- IF PG_DEBUG < 10 THEN
1639 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1640 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
1641 END IF;
1642
1643 END UPDATE_AND_SENDSIGNAL;
1644 */
1645
1646 -- this procedure gets called to update workflow attribute values when resource_id changed in ownership form
1647
1648 PROCEDURE update_wf_attributes (p_resource_id NUMBER , p_resourceto_id NUMBER ) IS
1649 cursor c_str_wf(p_resource_id number) is
1650 select strategy_id
1651 from iex_strategy_work_items
1652 where resource_id = p_resource_id;
1653
1654 cursor c_fulfil_wf(p_resource_id number) is
1655 select work_item_id
1656 from iex_strategy_work_items a,
1657 iex_stry_temp_work_items_b b
1658 where a.work_item_template_id = b.work_item_temp_id
1659 and b.work_type = 'AUTOMATIC'
1660 and a.resource_id = p_resource_id;
1661
1662 cursor c_custom_wf(p_resource_id number) is
1663 select work_item_id, b.workflow_item_type
1664 from iex_strategy_work_items a,
1665 iex_stry_temp_work_items_b b
1666 where a.work_item_template_id = b.work_item_temp_id
1667 and b.work_type = 'WORKFLOW'
1668 and a.resource_id = p_resource_id;
1669
1670 CURSOR c_manager(p_resource_id NUMBER) IS
1671 SELECT b.user_id, b.user_name , b.resource_id
1672 FROM JTF_RS_RESOURCE_EXTNS a
1673 ,JTF_RS_RESOURCE_EXTNS b
1674 WHERE b.source_id = a.source_mgr_id
1675 AND a.resource_id = p_resource_id;
1676
1677 cursor c_getname(p_resource_id NUMBER) is
1678 Select user_name,source_name
1679 from jtf_rs_resource_extns
1680 where resource_id =p_resource_id;
1681
1682 l_source_name varchar2(50);
1683 l_username varchar2(240);
1684 l_mgrname VARCHAR2(100);
1685 l_mgr_resource_id NUMBER ;
1686 l_mgr_id number;
1687
1688 begin
1689
1690 iex_debug_pub.logmessage(' p_resource_id ' || p_resource_id);
1691 iex_debug_pub.logmessage(' p_resourceto_id '|| p_resourceto_id);
1692
1693 for i in c_str_wf(p_resource_id) loop
1694 iex_strategy_wf.set_notification_resources(p_resourceto_id,'IEXSTRY',to_char(i.strategy_id));
1695
1696 /* OPEN c_getname(p_resourceto_id);
1697 FETCH c_getname INTO l_username, l_source_name;
1698 CLOSE c_getname;
1699
1700 wf_engine.SetItemAttrText(itemtype => 'IEXSTRY',
1701 itemkey => to_char(i.strategy_id),
1702 aname => 'ASSIGNEE',
1703 avalue => l_source_name);
1704 wf_engine.SetItemAttrText(itemtype => 'IEXSTRY',
1705 itemkey => to_char(i.strategy_id),
1706 aname => 'NOTIFICATION_USERNAME',
1707 avalue => l_username);
1708
1709 open c_manager(p_resourceto_id);
1710 fetch c_manager into l_mgr_id, l_mgrname, l_mgr_resource_id;
1711 close c_manager;
1712
1713 if l_mgrname is NULL then
1714 l_mgrname := l_username;
1715 l_mgr_resource_id := p_resourceto_id;
1716 end if;
1717
1718 wf_engine.SetItemAttrText(itemtype => 'IEXSTRY',
1719 itemkey => to_char(i.strategy_id),
1720 aname => 'NOTIFICATION_MGRNAME',
1721 avalue => l_mgrname);*/
1722
1723 end loop;
1724
1725 for i in c_fulfil_wf(p_resource_id) loop
1726
1727 OPEN c_getname(p_resourceto_id);
1728 FETCH c_getname INTO l_username, l_source_name;
1729 CLOSE c_getname;
1730
1731 wf_engine.SetItemAttrText(itemtype => 'IEXSTFFM',
1732 itemkey => to_char(i.work_item_id),
1733 aname => 'NOTIFICATION_USERNAME',
1734 avalue => l_username);
1735
1736 end loop;
1737
1738 for i in c_custom_wf(p_resource_id) loop
1739
1740 OPEN c_getname(p_resourceto_id);
1741 FETCH c_getname INTO l_username, l_source_name;
1742 CLOSE c_getname;
1743
1744 wf_engine.SetItemAttrText(itemtype => to_char(i.workflow_item_type),
1745 itemkey => to_char(i.work_item_id),
1746 aname => 'NOTIFICATION_USERNAME',
1747 avalue => l_username);
1748 end loop;
1749
1750 commit;
1751 exception
1752 when others then
1753 iex_debug_pub.logmessage (' Exception update_wf_attributes '|| sqlerrm);
1754 END update_wf_attributes;
1755
1756
1757 END IEX_STRY_UTL_PUB ;
1758
1759