[Home] [Help]
PACKAGE BODY: APPS.IEX_STRY_UTL_PUB
Source
1 PACKAGE BODY IEX_STRY_UTL_PUB as
2 /* $Header: iexpsutb.pls 120.1.12010000.5 2009/01/22 10:54:12 barathsr 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'); -- 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 IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
346 P_Api_Version_Number =>l_api_version_number,
347 P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
348 P_Init_Msg_List =>FND_API.G_TRUE,
349 p_commit =>FND_API.G_TRUE,
350 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
351 x_msg_count => l_msg_count,
352 x_msg_data => l_msg_data,
353 x_return_status => l_return_status,
354 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
355 -- IF PG_DEBUG < 10 THEN
356 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
357 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
358 'and Status =>'||l_return_status);
359 END IF;
360 IF l_return_status = FND_API.G_RET_STS_ERROR then
361 AddFailMsg( p_object => 'STRATEGY_WORK_ITEMS',
362 p_operation => 'UPDATE' );
363 raise FND_API.G_EXC_ERROR;
364 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
365 raise FND_API.G_EXC_UNEXPECTED_ERROR;
366 END IF;
367
368 END LOOP;
369 -- IF PG_DEBUG < 10 THEN
370 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
371 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'End of work items update ');
372 END IF;
373
374 l_strategy_Rec.strategy_id :=p_strategy_id;
375 l_strategy_Rec.status_code := p_status;
376
377 BEGIN
378 select object_version_number INTO l_object_version_number
379 FROM iex_strategies
380 where strategy_id =p_strategy_id;
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 -- IF PG_DEBUG < 10 THEN
384 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
385 IEX_DEBUG_PUB.LogMessage('CLOSE_STRY_AND_WITEMS: ' || 'Required Parameter p_stragey_id is invalid');
386 END IF;
387 AddInvalidArgMsg(
388 p_api_name => l_api_name_full,
389 p_param_value => p_strategy_id,
390 p_param_name => 'p_stragey_id' );
391 RAISE FND_API.G_EXC_ERROR;
392 WHEN OTHERS THEN
393 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
394 END;
395
396 l_strategy_Rec.object_version_number := l_object_version_number;
397
398 -- IF PG_DEBUG < 10 THEN
399 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
400 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
401 END IF;
402 -- IF PG_DEBUG < 10 THEN
403 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
404 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || '---------------------------------');
405 END IF;
406
407 IEX_STRATEGY_PVT.Update_strategy(
408 P_Api_Version_Number =>l_api_version_number,
409 P_strategy_Rec =>l_strategy_Rec,
410 P_Init_Msg_List =>FND_API.G_TRUE,
411 p_commit =>FND_API.G_TRUE,
412 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
413 x_msg_count => l_msg_count,
414 x_msg_data => l_msg_data,
415 x_return_status => l_return_status,
416 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
417 -- IF PG_DEBUG < 10 THEN
418 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
419 iex_debug_pub.logMessage('CLOSE_STRY_AND_WITEMS: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
420 'and Status =>'||l_return_status);
421 END IF;
422
423 IF l_return_status = FND_API.G_RET_STS_ERROR then
424 AddFailMsg( p_object => 'STRATEGY',
425 p_operation => 'UPDATE' );
426 raise FND_API.G_EXC_ERROR;
427 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
428 raise FND_API.G_EXC_UNEXPECTED_ERROR;
429 END IF;
430
431
432 -- Standard check of p_commit
433 IF FND_API.To_Boolean(p_commit) THEN
434 COMMIT WORK;
435 END IF;
436 --Begin bug#5874874 gnramasa 25-Apr-2007
437 --Update the UWQ summary table after closing the strategy.
438 if l_strategy_work_item_Rec.work_item_id is not null then
439 IEX_STRY_UTL_PUB.refresh_uwq_str_summ(l_strategy_work_item_Rec.work_item_id);
440 end if;
441 --End bug#5874874 gnramasa 25-Apr-2007
442
443 -- Standard call to get message count and if count is 1, get message info
444 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
445
446
447 EXCEPTION
448 WHEN FND_API.G_EXC_ERROR THEN
449 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
452
453 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
454 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
457
458 WHEN OTHERS THEN
459 ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
462 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
463 END IF;
464 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
465
466 END CLOSE_STRY_AND_WITEMS;
467
468
469 /**
470 update the stragey status to depending on the status passed
471 **/
472
473 PROCEDURE CLOSE_STRATEGY
474 (p_api_version IN NUMBER,
475 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
476 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
477 p_strategy_id IN NUMBER,
478 p_status IN VARCHAR2,
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2
482 )IS
483
484 l_api_version NUMBER := 1.0;
485 l_api_name VARCHAR2(100) := 'CLOSE_STRATEGY';
486 --l_api_name_full VARCHAR2(2000) := g_pkg_name || '.' || l_api_name;
487 l_api_name_full VARCHAR2(100) := l_api_name;
488 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
489 l_return_status VARCHAR2(100);
490 l_msg_count NUMBER;
491 l_msg_data VARCHAR2(32767);
492 l_strategy_rec IEX_strategy_PVT.strategy_Rec_Type;
493 l_object_version_number NUMBER;
494
495 Cursor c_get_work_items (p_strategy_id NUMBER) is
496 SELECT work_item_id, object_version_number
497 FROM iex_strategy_work_items
498 WHERE strategy_id = p_strategy_id;
499
500
501 BEGIN
502
503 SAVEPOINT CLOSE_STRATEGY_PUB;
504
505 -- Standard call to check for call compatibility.
506 IF NOT FND_API.Compatible_API_Call (l_api_version,
507 p_api_version,
508 l_api_name,
509 G_PKG_NAME) THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512
513 -- Check p_init_msg_list
514 IF FND_API.to_Boolean( p_init_msg_list ) THEN
515 FND_MSG_PUB.initialize;
516 END IF;
517
518 x_return_status := FND_API.G_RET_STS_SUCCESS;
519
520 -- Check for required parameter p_strategy_id
521 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
522 -- IF PG_DEBUG < 10 THEN
523 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
524 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_strategy_id is invalid');
525 END IF;
526 AddMissingArgMsg(
527 p_api_name => l_api_name_full,
528 p_param_name => 'p_strategy_id' );
529 RAISE FND_API.G_EXC_ERROR;
530 END IF;
531 -- IF PG_DEBUG < 10 THEN
532 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
533 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'after p_strategy_id check');
534 END IF;
535
536 -- Check for required parameter p_status_id
537 IF (p_status IS NULL) OR (p_status = FND_API.G_MISS_CHAR) THEN
538 -- IF PG_DEBUG < 10 THEN
539 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
540 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_status is invalid');
541 END IF;
542 AddMissingArgMsg(
543 p_api_name => l_api_name_full,
544 p_param_name => 'p_status' );
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547 -- IF PG_DEBUG < 10 THEN
548 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
549 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'after p_status check');
550 END IF;
551
552 l_strategy_Rec.strategy_id :=p_strategy_id;
553 l_strategy_Rec.status_code := p_status;
554
555 BEGIN
556 select object_version_number INTO l_object_version_number
557 FROM iex_strategies
558 where strategy_id =p_strategy_id;
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 -- IF PG_DEBUG < 10 THEN
562 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
563 IEX_DEBUG_PUB.LogMessage('CLOSE_STRATEGY: ' || 'Required Parameter p_stragey_id is invalid');
564 END IF;
565 AddInvalidArgMsg(
566 p_api_name => l_api_name_full,
567 p_param_value => p_strategy_id,
568 p_param_name => 'p_stragey_id' );
569 RAISE FND_API.G_EXC_ERROR;
570 WHEN OTHERS THEN
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572 END;
573
574 l_strategy_Rec.object_version_number := l_object_version_number;
575
576 -- IF PG_DEBUG < 10 THEN
577 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
578 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
579 END IF;
580 -- IF PG_DEBUG < 10 THEN
581 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
582 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || '---------------------------------');
583 END IF;
584
585 IEX_STRATEGY_PVT.Update_strategy(
586 P_Api_Version_Number =>2.0,
587 P_Init_Msg_List =>FND_API.G_TRUE,
588 p_commit =>FND_API.G_TRUE,
589 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
590 P_strategy_Rec =>l_strategy_Rec,
591 x_msg_count => l_msg_count,
592 x_msg_data => l_msg_data,
593 x_return_status => l_return_status,
594 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
595 -- IF PG_DEBUG < 10 THEN
596 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
597 iex_debug_pub.logMessage('CLOSE_STRATEGY: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
598 'and Status =>'||l_return_status);
599 END IF;
600
601 IF l_return_status = FND_API.G_RET_STS_ERROR then
602 AddFailMsg( p_object => 'STRATEGY',
603 p_operation => 'UPDATE' );
604 raise FND_API.G_EXC_ERROR;
605 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
606 raise FND_API.G_EXC_UNEXPECTED_ERROR;
607 END IF;
608
609
610 -- Standard check of p_commit
611 IF FND_API.To_Boolean(p_commit) THEN
612 COMMIT WORK;
613 END IF;
614
615 -- Standard call to get message count and if count is 1, get message info
616 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
617
618
619 EXCEPTION
620 WHEN FND_API.G_EXC_ERROR THEN
621 ROLLBACK TO CLOSE_STRATEGY_PUB;
622 x_return_status := FND_API.G_RET_STS_ERROR;
623 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
624
625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
626 ROLLBACK TO CLOSE_STRATEGY_PUB;
627 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
628 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
629
630 WHEN OTHERS THEN
631 ROLLBACK TO CLOSE_STRATEGY_PUB;
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
634 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
635 END IF;
636 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
637
638 END CLOSE_STRATEGY;
639
640
641 /**
642 update all the work_item status to depending on the status passed
643
644 **/
645
646 PROCEDURE UPDATE_WORK_ITEM
647 (p_api_version IN NUMBER,
648 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE,
649 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
650 p_work_item_id IN NUMBER,
651 p_status IN VARCHAR2,
652 x_return_status OUT NOCOPY VARCHAR2,
653 x_msg_count OUT NOCOPY NUMBER,
654 x_msg_data OUT NOCOPY VARCHAR2
655 )IS
656
657 l_api_version CONSTANT NUMBER := 1.0;
658 l_api_name VARCHAR2(100) := 'UPDATE_WORK_ITEM';
659 l_api_name_full CONSTANT VARCHAR2(61) := g_pkg_name || '.' || l_api_name;
660 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
661 l_return_status VARCHAR2(1);
662 l_msg_count NUMBER;
663 l_msg_data VARCHAR2(32767);
664 l_api_version_number CONSTANT NUMBER := 2.0;
665 l_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type;
666 l_object_version_number NUMBER;
667
668 Cursor c_get_work_items (p_work_item_id NUMBER) is
669 SELECT object_version_number
670 FROM iex_strategy_work_items
671 WHERE work_item_id =p_work_item_id;
672
673
674
675 BEGIN
676
677 SAVEPOINT UPDATE_WORK_ITEM_PUB;
678
679 -- Standard call to check for call compatibility.
680 IF NOT FND_API.Compatible_API_Call (l_api_version,
681 p_api_version,
682 l_api_name,
683 G_PKG_NAME) THEN
684 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
685 END IF;
686
687 -- Check p_init_msg_list
688 IF FND_API.to_Boolean( p_init_msg_list ) THEN
689 FND_MSG_PUB.initialize;
690 END IF;
691 -- IF PG_DEBUG < 10 THEN
692 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
693 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after init');
694 END IF;
695
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 -- Check for required parameter p_strategy_id
699 IF (p_work_item_id IS NULL) OR (p_work_item_id = FND_API.G_MISS_NUM) THEN
700 -- IF PG_DEBUG < 10 THEN
701 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
702 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
703 END IF;
704 AddMissingArgMsg(
705 p_api_name => l_api_name_full,
706 p_param_name => 'p_work_item_id' );
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709 -- IF PG_DEBUG < 10 THEN
710 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
711 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_work_item_id check');
712 END IF;
713
714 -- Check for required parameter p_status_id
715 IF (p_status IS NULL) OR (p_status = FND_API.G_MISS_CHAR) THEN
716 -- IF PG_DEBUG < 10 THEN
717 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
718 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'Required Parameter p_status is invalid');
719 END IF;
720 AddMissingArgMsg(
721 p_api_name => l_api_name_full,
722 p_param_name => 'p_status' );
723 RAISE FND_API.G_EXC_ERROR;
724 END IF;
725 -- IF PG_DEBUG < 10 THEN
726 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
727 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'after p_status check');
728 END IF;
729
730 FOR c_get_work_items_rec in c_get_work_items (p_work_item_id)
731 LOOP
732 l_strategy_work_item_Rec.work_item_id :=p_work_item_id;
733 l_strategy_work_item_Rec.status_code := p_status;
734 l_strategy_work_item_Rec.object_version_number
735 :=c_get_work_items_rec.object_version_number;
736 l_strategy_work_item_Rec.execute_end := sysdate;
737 -- IF PG_DEBUG < 10 THEN
738 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
739 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items');
740 END IF;
741 -- IF PG_DEBUG < 10 THEN
742 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
743 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || '---------------------------------');
744 END IF;
745
746 IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items(
747 P_Api_Version_Number =>l_api_version_number,
748 P_strategy_work_item_Rec =>l_strategy_work_item_Rec,
749 P_Init_Msg_List =>FND_API.G_TRUE,
750 p_commit =>FND_API.G_TRUE,
751 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
752 x_msg_count => l_msg_count,
753 x_msg_data => l_msg_data,
754 x_return_status => l_return_status,
755 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
756 -- IF PG_DEBUG < 10 THEN
757 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
758 iex_debug_pub.logMessage('UPDATE_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_WORK_ITEMS_PVT.Update_strategy_work_items '||
759 'and Status =>'||l_return_status);
760 END IF;
761 IF l_return_status = FND_API.G_RET_STS_ERROR then
762 AddFailMsg( p_object => 'STRATEGY_WORK_ITEMS',
763 p_operation => 'UPDATE' );
764 raise FND_API.G_EXC_ERROR;
765 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
766 raise FND_API.G_EXC_UNEXPECTED_ERROR;
767 END IF;
768
769 END LOOP;
770 -- IF PG_DEBUG < 10 THEN
771 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
772 IEX_DEBUG_PUB.LogMessage('UPDATE_WORK_ITEM: ' || 'End of work items update ');
773 END IF;
774
775
776
777 -- Standard check of p_commit
778 IF FND_API.To_Boolean(p_commit) THEN
779 COMMIT WORK;
780 END IF;
781
782 -- Standard call to get message count and if count is 1, get message info
783 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
784
785
786 EXCEPTION
787 WHEN FND_API.G_EXC_ERROR THEN
788 --ROLLBACK TO CLOSE_STRY_AND_WITEMS_PUB;
789 ROLLBACK TO UPDATE_WORK_ITEM_PUB;
790 x_return_status := FND_API.G_RET_STS_ERROR;
791 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
792
793 WHEN FND_API.G_EXC_UNEXPECTED_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_UNEXP_ERROR;
797 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
798
799 WHEN OTHERS 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 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
804 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
805 END IF;
806 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
807
808 END UPDATE_WORK_ITEM;
809 /**
810 * update next work item in the strategy table
811 * when a work item is created
812 **/
813 PROCEDURE UPDATE_NEXT_WORK_ITEM
814 (p_api_version IN NUMBER,
815 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
816 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
817 p_work_item_id IN NUMBER,
818 p_strategy_id IN NUMBER,
819 x_return_status OUT NOCOPY VARCHAR2,
820 x_msg_count OUT NOCOPY NUMBER,
821 x_msg_data OUT NOCOPY VARCHAR2
822 ) IS
823
824 l_api_version NUMBER := 1.0;
825 l_api_name VARCHAR2(100) := 'UPDATE_NEXT_WORK_ITEM';
826 --l_api_name_full VARCHAR2(2000) := g_pkg_name || '.' || l_api_name;
827 l_api_name_full VARCHAR2(100) := l_api_name;
828 l_init_msg_list VARCHAR2(100) := p_init_msg_list;
829 l_return_status VARCHAR2(100);
830 l_msg_count NUMBER;
831 l_msg_data VARCHAR2(32767);
832 l_strategy_rec IEX_strategy_PVT.strategy_Rec_Type;
833 l_object_version_number NUMBER;
834
835 BEGIN
836
837
838 SAVEPOINT UPDATE_NEXT_WORK_ITEM_PUB;
839
840 -- Standard call to check for call compatibility.
841 IF NOT FND_API.Compatible_API_Call (l_api_version,
842 p_api_version,
843 l_api_name,
844 G_PKG_NAME) THEN
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847
848 -- Check p_init_msg_list
849 IF FND_API.to_Boolean( p_init_msg_list ) THEN
850 FND_MSG_PUB.initialize;
851 END IF;
852
853 x_return_status := FND_API.G_RET_STS_SUCCESS;
854
855 -- Check for required parameter p_strategy_id
856 IF (p_strategy_id IS NULL) OR (p_strategy_id = FND_API.G_MISS_NUM) THEN
857 -- IF PG_DEBUG < 10 THEN
858 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
859 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_strategy_id is invalid');
860 END IF;
861 AddMissingArgMsg(
862 p_api_name => l_api_name_full,
863 p_param_name => 'p_strategy_id' );
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866 -- IF PG_DEBUG < 10 THEN
867 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
868 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_strategy_id check');
869 END IF;
870
871 -- Check for required parameter p_work_item_id
872 IF (p_work_item_id IS NULL) OR (p_work_item_id = FND_API.G_MISS_NUM) THEN
873 -- IF PG_DEBUG < 10 THEN
874 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
875 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_work_item_id is invalid');
876 END IF;
877 AddMissingArgMsg(
878 p_api_name => l_api_name_full,
879 p_param_name => 'p_work_item_id' );
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882 -- IF PG_DEBUG < 10 THEN
883 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
884 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'after p_work_item_id check');
885 END IF;
886 l_strategy_Rec.strategy_id :=p_strategy_id;
887 l_strategy_Rec.next_work_item_id := p_work_item_id;
888
889 BEGIN
890 select object_version_number INTO l_object_version_number
891 FROM iex_strategies
892 where strategy_id =p_strategy_id;
893 EXCEPTION
894 WHEN NO_DATA_FOUND THEN
895 -- IF PG_DEBUG < 10 THEN
896 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
897 IEX_DEBUG_PUB.LogMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Required Parameter p_stratgey_id is invalid');
898 END IF;
899 AddInvalidArgMsg(
900 p_api_name => l_api_name_full,
901 p_param_value => p_strategy_id,
902 p_param_name => 'p_stragey_id' );
903 RAISE FND_API.G_EXC_ERROR;
904 WHEN OTHERS THEN
905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
906 END;
907
908 l_strategy_Rec.object_version_number := l_object_version_number;
909
910 -- IF PG_DEBUG < 10 THEN
911 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
912 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || 'Before Calling IEX_STRATEGY_PVT.Update_strategy');
913 END IF;
914 -- IF PG_DEBUG < 10 THEN
915 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
916 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || '---------------------------------');
917 END IF;
918
919 IEX_STRATEGY_PVT.Update_strategy(
920 P_Api_Version_Number =>2.0,
921 P_Init_Msg_List =>FND_API.G_TRUE,
922 p_commit =>FND_API.G_TRUE,
923 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
924 P_strategy_Rec =>l_strategy_Rec,
925 x_msg_count => l_msg_count,
926 x_msg_data => l_msg_data,
927 x_return_status => l_return_status,
928 XO_OBJECT_VERSION_NUMBER =>l_object_version_number );
929 -- IF PG_DEBUG < 10 THEN
930 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
931 iex_debug_pub.logMessage('UPDATE_NEXT_WORK_ITEM: ' || 'After Calling IEX_STRATEGY_PVT.Update_strategy '||
932 'and Status =>'||l_return_status);
933 END IF;
934
935 IF l_return_status = FND_API.G_RET_STS_ERROR then
936 AddFailMsg( p_object => 'STRATEGY',
937 p_operation => 'UPDATE' );
938 raise FND_API.G_EXC_ERROR;
939 elsif l_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
940 raise FND_API.G_EXC_UNEXPECTED_ERROR;
941 END IF;
942
943
944 -- Standard check of p_commit
945 IF FND_API.To_Boolean(p_commit) THEN
946 COMMIT WORK;
947 END IF;
948
949 -- Standard call to get message count and if count is 1, get message info
950 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
951
952
953 EXCEPTION
954 WHEN FND_API.G_EXC_ERROR THEN
955 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
956 x_return_status := FND_API.G_RET_STS_ERROR;
957 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
958
959 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
960 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
961 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
962 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
963
964 WHEN OTHERS THEN
965 ROLLBACK TO UPDATE_NEXT_WORK_ITEM_PUB;
966 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
967 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
968 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
969 END IF;
970 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
971
972 END UPDATE_NEXT_WORK_ITEM;
973
974
975 /**
976 **check all the work_items for the given strategy for status in
977 ** CANCELLED,COMPLETE.
978 ** set the return value to 0 if the all the work items are
979 ** exhausted
980 **/
981 FUNCTION CHECK_WORK_ITEM_STATUS(
982 p_strategy_id IN NUMBER
983 )RETURN NUMBER IS
984
985 x_work_item_done NUMBER :=0;
986 BEGIN
987 SELECT count(*) into x_work_item_done
988 FROM iex_strategy_work_items
989 WHERE strategy_id = p_strategy_id
990 and status_code not in ('CANCELLED','COMPLETE');
991
992 return x_work_item_done;
993
994 END CHECK_WORK_ITEM_STATUS;
995
996 FUNCTION get_Date (p_date IN DATE,
997 l_UOM varchar2,
998 l_unit number) return date
999 IS
1000 r_date Date;
1001 l_conversion number := 0;
1002 l_jtf_time_uom_class varchar2(255); --Added for Bug 7434190 22-Jan-2009 barathsr
1003 begin
1004
1005 select sysdate into r_date from dual; -- default to sysdate;
1006
1007 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1008 iex_debug_pub.logmessage ('get_date before get conversion rate ');
1009 END IF;
1010 --Start of bug 7434190 22-Jan-2009 barathsr
1011 l_jtf_time_uom_class := fnd_profile.value_specific(NAME => 'JTF_TIME_UOM_CLASS',APPLICATION_ID =>695);
1012 select conversion_rate into l_conversion from mtl_uom_conversions
1013 --Use the profile 'unit of measure class' value if it is set at application level, else use it from site level
1014 /* where UOM_code = l_UOM and uom_class = (select fnd_profile.value('JTF_TIME_UOM_CLASS') from dual) */
1015 where UOM_code = l_UOM and uom_class = l_jtf_time_uom_class
1016 --End of Bug 7434190 22-Jan-2009 barathsr
1017 and inventory_item_id = 0;
1018 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1019 iex_debug_pub.logmessage ('get_date l_conversion => '|| l_conversion);
1020 END IF;
1021
1022 select p_date + l_conversion * l_unit / 24 into r_date from dual;
1023 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1024 iex_debug_pub.logmessage ('get_date => '|| to_char(r_date, 'yyyy/mm/dd/hh24:mi:ss'));
1025 END IF;
1026
1027 return r_date;
1028 exception when others THEN
1029 r_date :=p_date;
1030 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1031 iex_debug_pub.logmessage ('get_date exception return sysdate');
1032 END IF;
1033 return r_date;
1034
1035 end get_date;
1036
1037 /** subscription function example
1038 *
1039 **/
1040 FUNCTION create_workitem_check
1041 ( p_subscription_guid in raw,
1042 p_event in out NOCOPY wf_event_t)
1043 return varchar2
1044 is
1045 l_key varchar2(240) := p_event.GetEventKey();
1046 x_return_status VARCHAR2(10) := 'S';
1047 x_msg_count NUMBER;
1048 x_msg_data VARCHAR2(2000);
1049 exc EXCEPTION;
1050
1051
1052 l_del_id NUMBER;
1053 l_strategy_id NUMBER;
1054 l_workitem_id NUMBER;
1055
1056 begin
1057 -- put custom code
1058 -- this is just an example
1059 -- writes into the log file
1060 l_del_id := p_event.GetValueForParameter('DELINQUENCY_ID');
1061 l_strategy_id := p_event.GetValueForParameter('STRATEGY_ID');
1062 l_workitem_id := p_event.GetValueForParameter('WORK_ITEMID');
1063
1064 --IF PG_DEBUG < 10 THEN
1065 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1066 iex_debug_pub.logmessage ('create_workitem_check: ' || 'EVENT NAME =>'||p_event.getEventName());
1067 END IF;
1068 --IF PG_DEBUG < 10 THEN
1069 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1070 iex_debug_pub.logmessage ('create_workitem_check: ' || 'DELID =>' || l_del_id);
1071 END IF;
1072 --IF PG_DEBUG < 10 THEN
1073 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1074 iex_debug_pub.logmessage ('create_workitem_check: ' || 'strategy ID =>' ||l_strategy_id );
1075 END IF;
1076 --IF PG_DEBUG < 10 THEN
1077 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1078 iex_debug_pub.logmessage ('create_workitem_check: ' || 'work item ID =>' || l_workitem_id);
1079 END IF;
1080
1081 IF x_return_status <> 'S' THEN
1082 RAISE EXC;
1083 END IF;
1084 RETURN 'SUCCESS';
1085
1086 EXCEPTION
1087 WHEN EXC THEN
1088 -- IF PG_DEBUG < 10 THEN
1089 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1090 iex_debug_pub.logmessage ('create_workitem_check: ' || 'raised exe error');
1091 END IF;
1092 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_check', p_event.getEventName(), p_subscription_guid);
1093 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1094 RETURN 'ERROR';
1095 WHEN OTHERS THEN
1096 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_check', p_event.getEventName(), p_subscription_guid);
1097 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1098 RETURN 'ERROR';
1099
1100
1101 END create_workitem_check;
1102
1103 /** subscription function example
1104 * for complete work item
1105 **/
1106 FUNCTION create_workitem_complete
1107 ( p_subscription_guid in raw,
1108 p_event in out NOCOPY wf_event_t)
1109 return varchar2
1110 is
1111 l_key varchar2(240) := p_event.GetEventKey();
1112 x_return_status VARCHAR2(10) := 'S';
1113 x_msg_count NUMBER;
1114 x_msg_data VARCHAR2(2000);
1115 exc EXCEPTION;
1116
1117
1118 l_del_id NUMBER;
1119 l_strategy_id NUMBER;
1120 l_workitem_id NUMBER;
1121
1122 begin
1123 -- put custom code
1124 -- this is just an example
1125 -- writes into the log file
1126
1127 l_del_id := p_event.GetValueForParameter('DELINQUENCY_ID');
1128 l_strategy_id := p_event.GetValueForParameter('STRATEGY_ID');
1129 l_workitem_id := p_event.GetValueForParameter('WORK_ITEMID');
1130
1131 --IF PG_DEBUG < 10 THEN
1132 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1133 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'EVENT NAME =>'||p_event.getEventName());
1134 END IF;
1135 --IF PG_DEBUG < 10 THEN
1136 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1137 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'DELID =>' || l_del_id);
1138 END IF;
1139 --IF PG_DEBUG < 10 THEN
1140 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1141 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'strategy ID =>' ||l_strategy_id );
1142 END IF;
1143 --IF PG_DEBUG < 10 THEN
1144 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1145 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'work item ID =>' || l_workitem_id);
1146 END IF;
1147
1148 IF x_return_status <> 'S' THEN
1149 RAISE EXC;
1150 END IF;
1151 RETURN 'SUCCESS';
1152
1153 EXCEPTION
1154 WHEN EXC THEN
1155 -- IF PG_DEBUG < 10 THEN
1156 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1157 iex_debug_pub.logmessage ('create_workitem_complete: ' || 'raised exe error');
1158 END IF;
1159 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_complete', p_event.getEventName(), p_subscription_guid);
1160 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1161 RETURN 'ERROR';
1162 WHEN OTHERS THEN
1163 WF_CORE.CONTEXT('IEX_STRY_UTL_PUB', 'create_workitem_complete', p_event.getEventName(), p_subscription_guid);
1164 WF_EVENT.setErrorInfo(p_event, 'ERROR');
1165 RETURN 'ERROR';
1166
1167
1168 END create_workitem_complete;
1169
1170 --Begin bug#5874874 gnramasa 25-Apr-2007
1171 --Clear the Strategy related data in UWQ summary table.
1172 procedure clear_uwq_str_summ(p_object_id in number,p_object_type in varchar2) is
1173 begin
1174 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1175 iex_debug_pub.logmessage ('**** BEGIN : IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM ************');
1176 iex_debug_pub.logmessage ('IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM object_type='||p_object_type);
1177 iex_debug_pub.logmessage ('IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM object_id='||p_object_id);
1178 END IF;
1179
1180 IF p_object_type = 'PARTY' THEN
1181
1182 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1183 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing party level strategy uwq summary ************');
1184 END IF;
1185 UPDATE IEX_DLN_UWQ_SUMMARY
1186 SET WORK_ITEM_ID = null,
1187 SCHEDULE_START = null,
1188 SCHEDULE_END = null,
1189 WORK_TYPE = null,
1190 CATEGORY_TYPE = null,
1191 PRIORITY_TYPE = null,
1192 WKITEM_RESOURCE_ID = null,
1193 STRATEGY_ID = null,
1194 STRATEGY_TEMPLATE_ID = null,
1195 WORK_ITEM_TEMPLATE_ID = null,
1196 STATUS_CODE = null,
1197 START_TIME = null,
1198 END_TIME = null,
1199 WORK_ITEM_ORDER = null,
1200 WKITEM_ESCALATED_YN = null
1201 WHERE PARTY_ID = p_object_id;
1202
1203 ELSIF p_object_type = 'IEX_ACCOUNT' THEN
1204 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1205 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing account level strategy uwq summary ************');
1206 END IF;
1207 UPDATE IEX_DLN_UWQ_SUMMARY
1208 SET WORK_ITEM_ID = null,
1209 SCHEDULE_START = null,
1210 SCHEDULE_END = null,
1211 WORK_TYPE = null,
1212 CATEGORY_TYPE = null,
1213 PRIORITY_TYPE = null,
1214 WKITEM_RESOURCE_ID = null,
1215 STRATEGY_ID = null,
1216 STRATEGY_TEMPLATE_ID = null,
1217 WORK_ITEM_TEMPLATE_ID = null,
1218 STATUS_CODE = null,
1219 START_TIME = null,
1220 END_TIME = null,
1221 WORK_ITEM_ORDER = null,
1222 WKITEM_ESCALATED_YN = null
1223 WHERE CUST_ACCOUNT_ID = p_object_id;
1224
1225 ELSIF p_object_type = 'IEX_BILLTO' THEN
1226 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1227 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing billto level strategy uwq summary ************');
1228 END IF;
1229 UPDATE IEX_DLN_UWQ_SUMMARY
1230 SET WORK_ITEM_ID = null,
1231 SCHEDULE_START = null,
1232 SCHEDULE_END = null,
1233 WORK_TYPE = null,
1234 CATEGORY_TYPE = null,
1235 PRIORITY_TYPE = null,
1236 WKITEM_RESOURCE_ID = null,
1237 STRATEGY_ID = null,
1238 STRATEGY_TEMPLATE_ID = null,
1239 WORK_ITEM_TEMPLATE_ID = null,
1240 STATUS_CODE = null,
1241 START_TIME = null,
1242 END_TIME = null,
1243 WORK_ITEM_ORDER = null,
1244 WKITEM_ESCALATED_YN = null
1245 WHERE SITE_USE_ID = p_object_id;
1246 END IF;
1247 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1248 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM: Clearing party level strategy uwq summary ************');
1249 END IF;
1250 exception
1251 when others then
1252 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1253 iex_debug_pub.logmessage ('**** EXCEPTION: IEX_STRY_UTL_PUB.CLEAR_UWQ_STR_SUMM ************');
1254 END IF;
1255 end;
1256
1257 --Update the Strategy related data in UWQ summary table.
1258 procedure refresh_uwq_str_summ(p_workitem_id in number) is
1259
1260 CURSOR c_strategy_summary(p_work_item_id number) IS
1261 select strat.jtf_object_id,
1262 strat.jtf_object_type,
1263 wkitem.WORK_ITEM_ID,
1264 wkitem.schedule_start schedule_start,
1265 wkitem.schedule_end schedule_end,
1266 stry_temp_wkitem.category_type category,
1267 stry_temp_wkitem.WORK_TYPE,
1268 stry_temp_wkitem.PRIORITY_TYPE,
1269 wkitem.resource_id,
1270 wkitem.strategy_id,
1271 strat.strategy_template_id,
1272 wkitem.work_item_template_id,
1273 wkitem.status_code workitem_status,
1274 strat.status_code startegy_status,
1275 wkitem.creation_date start_time,
1276 wkitem.execute_end end_time, -- snuthala 28/08/2008 bug #6745580
1277 wkitem.work_item_order wkitem_order,
1278 wkitem.escalated_yn escalated_yn
1279 from iex_strategies strat,
1280 iex_strategy_work_items wkitem,
1281 iex_stry_temp_work_items_b stry_temp_wkitem
1282 where wkitem.work_item_id=p_work_item_id
1283 AND wkitem.strategy_id = strat.strategy_id
1284 AND wkitem.work_item_template_id = stry_temp_wkitem.work_item_temp_id;
1285
1286 l_jtf_object_id number;
1287 l_jtf_object_type varchar2(30);
1288 l_work_item_id number;
1289 l_schedule_start date;
1290 l_schedule_end date;
1291 l_work_type varchar2(30);
1292 l_category_type varchar2(30);
1293 l_priority_type varchar2(30);
1294 l_wkitem_resource_id number;
1295 l_strategy_id number;
1296 l_strategy_template_id number;
1297 l_work_item_template_id number;
1298 l_workitem_status varchar2(30);
1299 l_strategy_status varchar2(30);
1300 l_start_time date;
1301 l_end_time date;
1302 l_work_item_order number;
1303 l_escalated_yn varchar2(1);
1304 begin
1305
1306 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1307 iex_debug_pub.logmessage ('**** BEGIN refresh_uwq_str_summ ************');
1308 END IF;
1309
1310 open c_strategy_summary(p_workitem_id);
1311 fetch c_strategy_summary into
1312 l_jtf_object_id,
1313 l_jtf_object_type,
1314 l_work_item_id,
1315 l_schedule_start,
1316 l_schedule_end,
1317 l_category_type,
1318 l_work_type,
1319 l_priority_type,
1320 l_wkitem_resource_id,
1321 l_strategy_id,
1322 l_strategy_template_id,
1323 l_work_item_template_id,
1324 l_workitem_status,
1325 l_strategy_status,
1326 l_start_time,
1327 l_end_time,
1328 l_work_item_order,
1329 l_escalated_yn;
1330
1331 if l_strategy_status not in ('OPEN','ONHOLD') or l_workitem_status<>'OPEN' or l_work_type='AUTOMATIC' then
1332 close c_strategy_summary;
1333 clear_uwq_str_summ(l_jtf_object_id,l_jtf_object_type);
1334 commit work;
1335 return;
1336 end if;
1337 IF l_jtf_object_type = 'PARTY' THEN
1338 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1339 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating party level strategy uwq summary ************');
1340 END IF;
1341 update iex_dln_uwq_summary
1342 set work_item_id = l_work_item_id,
1343 schedule_start = l_schedule_start,
1344 schedule_end = l_schedule_end,
1345 work_type = l_work_type,
1346 category_type = l_category_type,
1347 priority_type = l_priority_type,
1348 wkitem_resource_id = l_wkitem_resource_id,
1349 strategy_id = l_strategy_id,
1350 strategy_template_id = l_strategy_template_id,
1351 work_item_template_id = l_work_item_template_id,
1352 status_code = l_workitem_status,
1353 start_time = l_start_time,
1354 end_time = l_end_time,
1355 work_item_order = l_work_item_order,
1356 wkitem_escalated_yn = l_escalated_yn
1357 where party_id = l_jtf_object_id;
1358
1359 ELSIF l_jtf_object_type = 'IEX_ACCOUNT' THEN
1360 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1361 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating account level strategy uwq summary ************');
1362 END IF;
1363 update iex_dln_uwq_summary
1364 set work_item_id = l_work_item_id,
1365 schedule_start = l_schedule_start,
1366 schedule_end = l_schedule_end,
1367 work_type = l_work_type,
1368 category_type = l_category_type,
1369 priority_type = l_priority_type,
1370 wkitem_resource_id = l_wkitem_resource_id,
1371 strategy_id = l_strategy_id,
1372 strategy_template_id = l_strategy_template_id,
1373 work_item_template_id = l_work_item_template_id,
1374 status_code = l_workitem_status,
1375 start_time = l_start_time,
1376 end_time = l_end_time,
1377 work_item_order = l_work_item_order,
1378 wkitem_escalated_yn = l_escalated_yn
1379 where cust_account_id = l_jtf_object_id;
1380
1381 ELSIF l_jtf_object_type = 'IEX_BILLTO' THEN
1382 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1383 iex_debug_pub.logmessage ('**** IEX_STRY_UTL_PUB.REFRESH_UWQ_STR_SUMM: Updating billto level strategy uwq summary ************');
1384 END IF;
1385 update iex_dln_uwq_summary
1386 set work_item_id = l_work_item_id,
1387 schedule_start = l_schedule_start,
1388 schedule_end = l_schedule_end,
1389 work_type = l_work_type,
1390 category_type = l_category_type,
1391 priority_type = l_priority_type,
1392 wkitem_resource_id = l_wkitem_resource_id,
1393 strategy_id = l_strategy_id,
1394 strategy_template_id = l_strategy_template_id,
1395 work_item_template_id = l_work_item_template_id,
1396 status_code = l_workitem_status,
1397 start_time = l_start_time,
1398 end_time = l_end_time,
1399 work_item_order = l_work_item_order,
1400 wkitem_escalated_yn = l_escalated_yn
1401 where site_use_id = l_jtf_object_id;
1402
1403 END IF;
1404 close c_strategy_summary;
1405 commit work;
1406 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1407 iex_debug_pub.logmessage ('**** END refresh_uwq_str_summ ************');
1408 END IF;
1409 exception
1410 when others then
1411
1412 iex_debug_pub.logmessage ('**** EXCEPTION refresh_uwq_str_summ ************');
1413
1414 end;
1415 --End bug#5874874 gnramasa 25-Apr-2007
1416
1417 /** reassagin strategy
1418 * send signal first
1419 * then call create_Strategy_pub
1420 * to create the new strategy
1421 * the new strategy will launch the work flow*
1422 **/
1423 /*
1424 PROCEDURE REASSIGN_STRATEGY( p_strategy_id IN NUMBER,
1425 p_status IN VARCHAR2,
1426 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1427 x_return_status OUT NOCOPY VARCHAR2,
1428 x_msg_count OUT NOCOPY NUMBER,
1429 x_msg_data OUT NOCOPY VARCHAR2) IS
1430
1431 l_object_type varchar2(30) := 'DELINQUENT' ;
1432 l_object_id number ;
1433 l_delinquency_id number ;
1434 l_return_status VARCHAR2(1);
1435 l_msg_count NUMBER;
1436 l_msg_data VARCHAR2(32767);
1437 l_status varchar2(100) ;
1438
1439 cursor c_status(p_strategy_id IN NUMBER) is
1440 select status_code
1441 from iex_strategies
1442 where strategy_id = p_strategy_id ;
1443
1444 cursor c_object(p_strategy_id IN NUMBER) is
1445 select delinquency_id,object_id,object_type
1446 from iex_strategies
1447 where strategy_id = p_strategy_id ;
1448
1449 BEGIN
1450
1451 SAVEPOINT REASSIGN_STRATEGY_PUB;
1452
1453 x_return_status := FND_API.G_RET_STS_ERROR;
1454
1455
1456 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1457 strategy_id => p_strategy_id,
1458 status => p_status ) ;
1459
1460 -- IF PG_DEBUG < 10 THEN
1461 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1462 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'AFTER SEND SIGNAL');
1463 END IF;
1464
1465 OPEN c_status(p_strategy_id);
1466 FETCH c_status INTO l_status;
1467 CLOSE c_status;
1468
1469 if ( l_status = 'CANCELLED' ) then
1470 -- IF PG_DEBUG < 10 THEN
1471 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1472 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'After Send Signal and it successfull ');
1473 END IF;
1474 OPEN c_object(p_strategy_id);
1475 FETCH c_object INTO l_delinquency_id,l_object_id,l_object_type;
1476 CLOSE c_object;
1477
1478 -- IF PG_DEBUG < 10 THEN
1479 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1480 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'IEXSTTAB-Delinquency id'||
1481 'before calling create strategy is '
1482 ||l_delinquency_id||
1483 ' object_id is '||l_object_id ||
1484 ' object_type is' || l_object_type );
1485 END IF;
1486
1487 -- IF PG_DEBUG < 10 THEN
1488 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1489 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'BEFORE CALLING CREATE_STRATEGY ');
1490 END IF;
1491
1492 IEX_STRATEGY_PUB.CREATE_STRATEGY
1493 (p_api_version_number => 2.0,
1494 p_init_msg_list => FND_API.G_TRUE,
1495 p_commit => p_commit,
1496 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1497 p_delinquencyId => l_delinquency_id ,
1498 p_objecttype => l_object_type,
1499 p_objectid => l_object_id ,
1500 x_return_status => l_return_status,
1501 x_msg_count => l_msg_count,
1502 x_msg_data => l_msg_data) ;
1503
1504 -- IF PG_DEBUG < 10 THEN
1505 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1506 iex_debug_pub.logmessage('REASSIGN_STRATEGY: ' || 'status of create strategy ' ||l_return_status);
1507 END IF;
1508
1509 x_msg_count :=l_msg_count;
1510 x_msg_data :=l_msg_data;
1511 x_return_status :=l_return_status;
1512
1513
1514 ELSE
1515 ROLLBACK TO REASSIGN_STRATEGY_PUB;
1516 RETURN;
1517 END if; --l_status =cancelled
1518
1519 -- Standard check of p_commit
1520 IF FND_API.To_Boolean(p_commit) THEN
1521 COMMIT WORK;
1522 END IF;
1523
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 ROLLBACK TO REASSIGN_STRATEGY_PUB;
1527 -- IF PG_DEBUG < 10 THEN
1528 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1529 IEX_DEBUG_PUB.logmessage('REASSIGN_STRATEGY: ' || 'inexception of reassign strategy '||sqlerrm);
1530 END IF;
1531 END REASSIGN_STRATEGY;
1532 */
1533 /** update work item and call send signal
1534 * if send signal fails, roolback the work item
1535 **/
1536
1537 /*
1538 PROCEDURE UPDATE_AND_SENDSIGNAL( P_strategy_work_item_Rec IN
1539 iex_strategy_work_items_pvt.strategy_work_item_Rec_Type,
1540 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1541 x_return_status OUT NOCOPY VARCHAR2,
1542 x_msg_count OUT NOCOPY NUMBER,
1543 x_msg_data OUT NOCOPY VARCHAR2)IS
1544
1545 l_return_status VARCHAR2(1);
1546 l_msg_count NUMBER;
1547 l_msg_data VARCHAR2(32767);
1548 l_status varchar2(100) ;
1549 l_object_version_number NUMBER;
1550 v_result NUMBER;
1551 BEGIN
1552
1553 SAVEPOINT UPDATE_AND_SENDSIGNAL;
1554
1555 x_return_status := FND_API.G_RET_STS_SUCCESS;
1556
1557 iex_strategy_work_items_pvt.update_strategy_work_items(
1558 p_api_version_number => 2.0,
1559 p_init_msg_list => FND_API.G_TRUE,
1560 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1561 p_commit => p_commit,
1562 x_return_status => l_return_status,
1563 x_msg_count => l_msg_count,
1564 x_msg_data => l_msg_data,
1565 p_strategy_work_item_rec => p_strategy_work_item_rec,
1566 xo_object_version_number => l_object_version_number);
1567
1568 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1569 x_msg_count :=l_msg_count;
1570 x_msg_data :=l_msg_data;
1571 x_return_status :=l_return_status;
1572 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
1573 return;
1574 ELSE
1575 --call send signal
1576 IEX_STRATEGY_WF.SEND_SIGNAL(process => 'IEXSTRY',
1577 strategy_id => p_strategy_work_item_rec.strategy_id,
1578 status => p_strategy_work_item_rec.status_code,
1579 work_item_id => p_strategy_work_item_rec.work_item_id);
1580
1581 -- IF PG_DEBUG < 10 THEN
1582 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1583 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'AFTER SEND SIGNAL');
1584 END IF;
1585 --check if it the strategy is open
1586 -- and next work item is not the same as the updated work item
1587 -- then the send signal has been successful and it has created
1588 -- the next work item . other wise, the send signal failed.
1589 -- id send signal is successful, commit , else rollback
1590 select count(*) INTO v_result from iex_strategies
1591 where strategy_id =p_strategy_work_item_rec.strategy_id
1592 and next_work_item_id =p_strategy_work_item_rec.work_item_id
1593 and status_code ='OPEN';
1594
1595 if v_result >0 THEN
1596 -- IF PG_DEBUG < 10 THEN
1597 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1598 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal has failed ');
1599 END IF;
1600 rollback to UPDATE_AND_SENDSIGNAL;
1601 x_return_status := FND_API.G_RET_STS_ERROR;
1602 return;
1603 else
1604 -- IF PG_DEBUG < 10 THEN
1605 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1606 IEX_DEBUG_PUB.logmessage('UPDATE_AND_SENDSIGNAL: ' || 'send signal is successful ');
1607 END IF;
1608 end if;
1609 END IF; --if status is successful
1610
1611 -- Standard check of p_commit
1612 IF FND_API.To_Boolean(p_commit) THEN
1613 COMMIT WORK;
1614 END IF;
1615
1616 EXCEPTION
1617 WHEN OTHERS THEN
1618 ROLLBACK TO UPDATE_AND_SENDSIGNAL;
1619 -- IF PG_DEBUG < 10 THEN
1620 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
1621 IEX_DEBUG_PUB.logmessage('inexception of UPDATE_AND_SENDSIGNAL '||sqlerrm);
1622 END IF;
1623
1624 END UPDATE_AND_SENDSIGNAL;
1625 */
1626
1627 END IEX_STRY_UTL_PUB ;
1628
1629