[Home] [Help]
PACKAGE BODY: APPS.IEX_STRATEGY_WORK_ITEMS_PVT
Source
1 PACKAGE BODY IEX_strategy_work_items_PVT as
2 /* $Header: iexvswib.pls 120.7 2010/11/10 09:58:34 pnaveenk noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(300):= 'IEX_strategy_work_items_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvswib.pls';
6
7
8 -- Hint: Primary key needs to be returned.
9 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
10 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
11
12 PROCEDURE Create_strategy_work_items(
13 P_Api_Version_Number IN NUMBER,
14 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
15 P_Commit IN VARCHAR2 := FND_API.G_TRUE,
16 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 P_strategy_work_item_Rec IN strategy_work_item_Rec_Type := G_MISS_strategy_work_item_REC,
18 X_WORK_ITEM_ID OUT NOCOPY NUMBER,
19 X_Return_Status OUT NOCOPY VARCHAR2,
20 X_Msg_Count OUT NOCOPY NUMBER,
21 X_Msg_Data OUT NOCOPY VARCHAR2
22 )
23
24 IS
25 l_api_name CONSTANT VARCHAR2(200) := 'Create_strategy_work_items';
26 l_api_version_number CONSTANT NUMBER := 2.0;
27 l_return_status_full VARCHAR2(1);
28 v_WORK_ITEM_ID iex_strategy_work_items.strategy_id%TYPE;
29 v_object_version_number iex_strategy_work_items.object_version_number%TYPE;
30 v_rowid VARCHAR2(24);
31 --added by vimpi
32 l_msg_Count number ;
33 l_msg_data varchar2(200) ;
34 l_return_status varchar2(200) ;
35
36
37 Cursor c2 is SELECT iex_strategy_work_items_S.nextval from dual;
38
39 BEGIN
40 -- IF PG_DEBUG < 10 THEN
41 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
42 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_strategy_work_items_PVT.Create_strategy_work_items ******** ');
43 END IF;
44 -- Standard Start of API savepoint
45 SAVEPOINT CREATE_strategy_work_items_PVT;
46
47 -- Standard call to check for call compatibility.
48 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
49 p_api_version_number,
50 l_api_name,
51 G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55 -- IF PG_DEBUG < 10 THEN
56 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
57 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'After Compatibility Check');
58 END IF;
59
60 -- Initialize message list if p_init_msg_list is set to TRUE.
61 IF FND_API.to_Boolean( p_init_msg_list )
62 THEN
63 FND_MSG_PUB.initialize;
64 END IF;
65
66
67 -- Initialize API return status to SUCCESS
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 --
70 -- API body
71 --
72 -- ******************************************************************
73 -- Validate Environment
74 -- ******************************************************************
75 IF FND_GLOBAL.User_Id IS NULL
76 THEN
77 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
78 THEN
79 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
80 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
81 FND_MSG_PUB.ADD;
82 END IF;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 -- IF PG_DEBUG < 10 THEN
86 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
87 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'After Global user Check');
88 END IF;
89
90 --object version Number
91 v_object_version_number :=1;
92 -- get work_item_id
93 OPEN C2;
94 FETCH C2 INTO v_work_item_id;
95 CLOSE C2;
96 -- IF PG_DEBUG < 10 THEN
97 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
98 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'After work_item_id Check and work_item_id is => '||v_work_item_id);
99 END IF;
100
101 --strategy Id check
102 IF (P_strategy_work_item_rec.strategy_id IS NULL) OR
103 (P_strategy_work_item_rec.strategy_id =FND_API.G_MISS_NUM) THEN
104 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
105 fnd_message.set_token('API_NAME', l_api_name);
106 fnd_message.set_token('MISSING_PARAM', 'strategy_id');
107 fnd_msg_pub.add;
108 RAISE FND_API.G_EXC_ERROR;
109 END IF;
110 -- IF PG_DEBUG < 10 THEN
111 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
112 IEX_DEBUG_PUB.LogMessage ('Create_strategy_work_items: ' || 'After strategy id check');
113 END IF;
114
115 --resource Id check
116 IF (P_strategy_work_item_rec.resource_id IS NULL) OR
117 (P_strategy_work_item_rec.resource_id =FND_API.G_MISS_NUM) THEN
118 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
119 fnd_message.set_token('API_NAME', l_api_name);
120 fnd_message.set_token('MISSING_PARAM', 'resource_id');
121 fnd_msg_pub.add;
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124 -- IF PG_DEBUG < 10 THEN
125 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
126 IEX_DEBUG_PUB.LogMessage ('Create_strategy_work_items: ' || 'After resource id check');
127 END IF;
128
129
130 -- IF PG_DEBUG < 10 THEN
131 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
132 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'Before Calling iex_strategies_pkg.insert_row');
133 END IF;
134
135 -- Invoke table handler(IEX_STRATEGY_WORK_ITEMS_PKG.Insert_Row)
136 IEX_STRATEGY_WORK_ITEMS_PKG.Insert_Row(
137 x_rowid =>v_rowid
138 ,x_WORK_ITEM_ID => v_WORK_ITEM_ID
139 ,x_STRATEGY_ID => p_strategy_work_item_rec.STRATEGY_ID
140 ,x_RESOURCE_ID => p_strategy_work_item_rec.RESOURCE_ID
141 ,x_STATUS_CODE => p_strategy_work_item_rec.STATUS_CODE
142 ,x_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
143 ,x_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
144 ,x_CREATION_DATE => SYSDATE
145 ,x_CREATED_BY => FND_GLOBAL.USER_ID
146 ,x_PROGRAM_ID => p_strategy_work_item_rec.PROGRAM_ID
147 ,x_OBJECT_VERSION_NUMBER => v_OBJECT_VERSION_NUMBER
148 ,x_REQUEST_ID => p_strategy_work_item_rec.REQUEST_ID
149 ,x_LAST_UPDATE_DATE => SYSDATE
150 ,x_WORK_ITEM_TEMPLATE_ID => p_strategy_work_item_rec.WORK_ITEM_TEMPLATE_ID
151 ,x_PROGRAM_APPLICATION_ID => p_strategy_work_item_rec.PROGRAM_APPLICATION_ID
152 ,x_PROGRAM_UPDATE_DATE => p_strategy_work_item_rec.PROGRAM_UPDATE_DATE
153 ,x_EXECUTE_START => p_strategy_work_item_rec.EXECUTE_START
154 ,x_EXECUTE_END => p_strategy_work_item_rec.EXECUTE_END
155 ,x_SCHEDULE_START => p_strategy_work_item_rec.SCHEDULE_START
156 ,x_SCHEDULE_END => p_strategy_work_item_rec.SCHEDULE_END
157 ,x_STRATEGY_TEMP_ID => p_strategy_work_item_rec.STRATEGY_TEMP_ID
158 ,x_WORK_ITEM_ORDER => p_strategy_work_item_rec.WORK_ITEM_ORDER
159 ,x_ESCALATED_YN => p_strategy_work_item_rec.ESCALATED_YN
160 ,x_PRE_EXECUTION_WAIT => p_strategy_work_item_rec.pre_execution_wait
161 ,x_PRE_EXECUTION_TIME_UOM => p_strategy_work_item_rec.pre_execution_time_uom
162 ,x_POST_EXECUTION_WAIT => p_strategy_work_item_rec.post_execution_wait
163 ,x_EXECUTION_TIME_UOM => p_strategy_work_item_rec.execution_time_uom
164 ,x_XDO_TEMP_ID => p_strategy_work_item_rec.xdo_temp_id
165 ,x_optional_yn => p_strategy_work_item_rec.optional_yn
166 ,x_optional_wait_time => p_strategy_work_item_rec.optional_wait_time
167 ,x_optional_wait_time_uom => p_strategy_work_item_rec.optional_wait_time_uom
168 ,x_notify_yn => p_strategy_work_item_rec.notify_yn
169 ,x_escalate_yn => p_strategy_work_item_rec.escalate_yn
170 ,x_escalate_wait_time => p_strategy_work_item_rec.escalate_wait_time
171 ,x_escalate_wait_time_uom => p_strategy_work_item_rec.escalate_wait_time_uom
172 ,x_custom_workflow_type => p_strategy_work_item_rec.custom_workflow_type
173 );
174
175 x_WORK_ITEM_ID := v_WORK_ITEM_ID;
176
177 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
178 RAISE FND_API.G_EXC_ERROR;
179 END IF;
180
181 --
182 -- End of API body
183 --
184 -- added by vimpi for metaphor integration
185
186 /* begin remove the check of profile, the procedure is doing nothing, ctlee 02/28/2005 */
187 /*
188 IF( x_return_status = FND_API.G_RET_STS_SUCCESS) then
189 IF (NVL(FND_PROFILE.VALUE('IEX_STRY_METAPHOR_CREATION'),'N') = 'Y') then
190 BEGIN
191 --dbms_output.put_line('calinggggg create_uwq_itemmmmm');
192 IEX_STRY_UWQ_PVT.Create_uwq_item(
193 p_api_version => 1.0,
194 p_init_msg_list => FND_API.G_TRUE,
195 p_commit => p_commit,
196 p_work_item_id => v_work_item_id,
197 P_strategy_work_item_Rec => p_strategy_work_item_rec,
198 x_return_status => l_return_status,
199 x_msg_count => l_msg_count,
200 x_msg_data => l_msg_data) ;
201
202 --dbms_output.put_line('ERRRRRRRROS is '||l_msg_data) ;
203 if ( l_return_status = 'E') then
204 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
205 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'No Data Found');
206 END IF;
207 RAISE FND_API.G_EXC_ERROR;
208 elsif ( l_return_status ='U') then
209 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
210 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'Create UwqmProcedure failed');
211 END IF;
212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
213 end if ;
214 END;
215 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
216 IEX_DEBUG_PUB.LogMessage('Create_strategy_work_items: ' || 'In if metaphor');
217 END IF;
218 end if ;
219 --end of profile enabled checking
220 end if ;
221 */
222 /* end remove the check of profile, the procedure is doing nothing, ctlee 02/28/2005 */
223
224
225 -- Standard check for p_commit
226 IF FND_API.to_Boolean( p_commit )
227 THEN
228 COMMIT WORK;
229 END IF;
230
231 -- Standard call to get message count and if count is 1, get message info.
232 FND_MSG_PUB.Count_And_Get
233 ( p_count => x_msg_count,
234 p_data => x_msg_data
235 );
236
237 -- IF PG_DEBUG < 10 THEN
238 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
239 IEX_DEBUG_PUB.LogMessage ('********* end of Procedure =>IEX_strategy_work_items_PVT.Create_strategy_work_items ******** ');
240 END IF;
241 EXCEPTION
242 WHEN FND_API.G_EXC_ERROR THEN
243 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
244 P_API_NAME => L_API_NAME
245 ,P_PKG_NAME => G_PKG_NAME
246 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
247 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
248 ,X_MSG_COUNT => X_MSG_COUNT
249 ,X_MSG_DATA => X_MSG_DATA
250 ,X_RETURN_STATUS => X_RETURN_STATUS);
251
252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
254 P_API_NAME => L_API_NAME
255 ,P_PKG_NAME => G_PKG_NAME
256 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
257 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
258 ,X_MSG_COUNT => X_MSG_COUNT
259 ,X_MSG_DATA => X_MSG_DATA
260 ,X_RETURN_STATUS => X_RETURN_STATUS);
261
262 WHEN OTHERS THEN
263 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
264 P_API_NAME => L_API_NAME
265 ,P_PKG_NAME => G_PKG_NAME
266 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
267 ,P_SQLCODE => SQLCODE
268 ,P_SQLERRM => SQLERRM
269 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
270 ,X_MSG_COUNT => X_MSG_COUNT
271 ,X_MSG_DATA => X_MSG_DATA
272 ,X_RETURN_STATUS => X_RETURN_STATUS);
273 End Create_strategy_work_items;
274
275
276 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
277 PROCEDURE Update_strategy_work_items(
278 P_Api_Version_Number IN NUMBER,
279 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
280 P_Commit IN VARCHAR2 := FND_API.G_TRUE,
281 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
282 P_strategy_work_item_Rec IN strategy_work_item_Rec_Type,
283 X_Return_Status OUT NOCOPY VARCHAR2,
284 X_Msg_Count OUT NOCOPY NUMBER,
285 X_Msg_Data OUT NOCOPY VARCHAR2,
286 XO_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
287 )
288
289 IS
290 /*
291 Cursor C_Get_strategy_work_items(WORK_ITEM_ID Number) IS
292 Select rowid,
293 WORK_ITEM_ID,
294 STRATEGY_ID,
295 COMPETENCE_ID,
296 CATEGORY_TYPE,
297 RESOURCE_ID,
298 REQUIRED_YN,
299 STATUS_CODE,
300 PRIORITY_ID,
301 PRE_EXECUTION_WAIT,
302 POST_EXECUTION_WAIT,
303 CLOSURE_DATE_LIMIT,
304 EXECUTE_DATE_LIMIT,
305 SEEDED_WORKFLOW_YN,
306 WORKFLOW_ITEM_TYPE,
307 WORKFLOW_PROCESS_NAME,
308 LAST_UPDATED_BY,
309 LAST_UPDATE_LOGIN,
310 CREATION_DATE,
311 CREATED_BY,
312 PROGRAM_ID,
313 OBJECT_VERSION_NUMBER,
314 REQUEST_ID,
315 WORK_TYPE,
316 LAST_UPDATE_DATE,
317 WORK_ITEM_TEMPLATE_ID
318 From IEX_STRATEGY_WORK_ITEMS
319 -- Hint: Developer need to provide Where clause
320 For Update NOWAIT;
321 */
322 l_api_name CONSTANT VARCHAR2(200) := 'Update_strategy_work_items';
323 l_api_version_number CONSTANT NUMBER := 2.0;
324
325 -- Local Variables
326 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
327 l_ref_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type;
328 l_tar_strategy_work_item_rec IEX_strategy_work_items_PVT.strategy_work_item_Rec_Type := P_strategy_work_item_Rec;
329
330 l_object_version_number IEX_strategy_work_items.object_version_number%TYPE:=P_strategy_work_item_Rec.object_version_number;
331 l_rowid ROWID;
332 --added by vimpi
333 l_msg_Count number ;
334 l_msg_data varchar2(200) ;
335 l_return_status varchar2(200) ;
336 l_work_item_temp_id number ;
337 l_work_item_id number ;
338 l_strategy_id number ;
339
340 BEGIN
341
342 -- IF PG_DEBUG < 10 THEN
343 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
344 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_strategy_work_items_PVT.update_strategy_work_items ******** ');
345 END IF;
346 -- Standard Start of API savepoint
347 SAVEPOINT UPDATE_strategy_work_items_PVT;
348
349 -- Standard call to check for call compatibility.
350 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
351 p_api_version_number,
352 l_api_name,
353 G_PKG_NAME)
354 THEN
355 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 END IF;
357
358
359 -- Initialize message list if p_init_msg_list is set to TRUE.
360 IF FND_API.to_Boolean( p_init_msg_list )
361 THEN
362 FND_MSG_PUB.initialize;
363 END IF;
364
365
366
367
368 -- Initialize API return status to SUCCESS
369 x_return_status := FND_API.G_RET_STS_SUCCESS;
370
371 --
372 -- Api body
373 --
374
375 -- call locking table handler
376 IEX_STRATEGY_WORK_ITEMS_PKG.lock_row (
377 p_strategy_work_item_rec.WORK_ITEM_ID,
378 l_object_version_number
379 );
380
381 -- Invoke table handler(IEX_STRATEGY_WORK_ITEMS_PKG.Update_Row)
382 IEX_STRATEGY_WORK_ITEMS_PKG.Update_Row(
383 x_WORK_ITEM_ID => p_strategy_work_item_rec.WORK_ITEM_ID
384 ,x_STRATEGY_ID => p_strategy_work_item_rec.STRATEGY_ID
385 ,x_RESOURCE_ID => p_strategy_work_item_rec.RESOURCE_ID
386 ,x_STATUS_CODE => p_strategy_work_item_rec.STATUS_CODE
387 ,x_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
388 ,x_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID
389 ,x_PROGRAM_ID => p_strategy_work_item_rec.PROGRAM_ID
390 ,x_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER +1
391 ,x_REQUEST_ID => p_strategy_work_item_rec.REQUEST_ID
392 ,x_LAST_UPDATE_DATE => SYSDATE
393 ,x_WORK_ITEM_TEMPLATE_ID => p_strategy_work_item_rec.WORK_ITEM_TEMPLATE_ID
394 ,x_PROGRAM_APPLICATION_ID => p_strategy_work_item_rec.PROGRAM_APPLICATION_ID
395 ,x_PROGRAM_UPDATE_DATE => p_strategy_work_item_rec.PROGRAM_UPDATE_DATE
396 ,x_EXECUTE_START => p_strategy_work_item_rec.EXECUTE_START
397 ,x_EXECUTE_END => p_strategy_work_item_rec.EXECUTE_END
398 ,x_SCHEDULE_START => p_strategy_work_item_rec.SCHEDULE_START
399 ,x_SCHEDULE_END => p_strategy_work_item_rec.SCHEDULE_END
400 ,x_STRATEGY_TEMP_ID => p_strategy_work_item_rec.STRATEGY_TEMP_ID
401 ,x_WORK_ITEM_ORDER => p_strategy_work_item_rec.WORK_ITEM_ORDER
402 ,x_ESCALATED_YN => p_strategy_work_item_rec.ESCALATED_YN
403 );
404 --
405 -- End of API body.
406 --
407 --Return Version number
408 xo_object_version_number := l_object_version_number + 1;
409
410 -- added by vimpi for metaphor integration
411
412 /* begin remove the check of profile, the procedure is doing nothing, ctlee 02/28/2005 */
413 /*
414 IF( x_return_status = FND_API.G_RET_STS_SUCCESS) then
415 IF (NVL(FND_PROFILE.VALUE('IEX_STRY_METAPHOR_CREATION'),'N') = 'Y') then
416 BEGIN
417 l_work_item_temp_id := p_strategy_work_item_rec.WORK_ITEM_TEMPLATE_ID ;
418 l_work_item_id := p_strategy_work_item_rec.WORK_ITEM_ID ;
419 l_strategy_id := p_strategy_work_item_rec.STRATEGY_ID ;
420 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
421 IEX_DEBUG_PUB.LogMessage('Update_strategy_work_items: ' || 'In VIMPIIIIIIII '||l_work_item_id);
422 END IF;
423 IEX_STRY_UWQ_PVT.Update_uwq_item(
424 p_api_version => 1.0,
425 p_init_msg_list => FND_API.G_TRUE,
426 p_commit => p_commit,
427 p_work_item_id => p_strategy_work_item_rec.WORK_ITEM_ID,
428 P_strategy_work_item_Rec => p_strategy_work_item_rec,
429 x_return_status => l_return_status,
430 x_msg_count => l_msg_count,
431 x_msg_data => l_msg_data) ;
432 if ( l_return_status = 'E') then
433 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
434 IEX_DEBUG_PUB.LogMessage('Update_strategy_work_items: ' || 'No Data Found');
435 END IF;
436 RAISE FND_API.G_EXC_ERROR;
437 elsif ( l_return_status ='U') then
438 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
439 IEX_DEBUG_PUB.LogMessage('Update_strategy_work_items: ' || 'Create UwqmProcedure failed');
440 END IF;
441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
442 end if ;
443 END;
444 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
445 IEX_DEBUG_PUB.LogMessage('Update_strategy_work_items: ' || 'In if metaphor');
446 END IF;
447 end if ;
448 --end of profile enabled checking
449 end if ;
450 */
451 /* end remove the check of profile, the procedure is doing nothing, ctlee 02/28/2005 */
452
453 --
454
455 -- Standard check for p_commit
456 IF FND_API.to_Boolean( p_commit )
457 THEN
458 COMMIT WORK;
459 END IF;
460
461 -- added by vimpi for metaphor integration
462
463 /* IF( x_return_status = FND_API.G_RET_STS_SUCCESS) then
464 IF (NVL(FND_PROFILE.VALUE('IEX_STRY_METAPHOR_CREATION'),'N') = 'Y') then
465 BEGIN
466 IEX_STRY_UWQ_PVT.Update_uwq_item(
467 p_api_version => 1.0,
468 p_init_msg_list => FND_API.G_TRUE,
469 p_commit => FND_API.G_TRUE,
470 p_work_item_id => p_strategy_work_item_rec.WORK_ITEM_ID,
471 P_strategy_work_item_Rec => p_strategy_work_item_rec,
472 x_return_status => l_return_status,
473 x_msg_count => l_msg_count,
474 x_msg_data => l_msg_data) ;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
479 IEX_DEBUG_PUB.LogMessage( debug_msg => 'IEU_UWQM_ITEMS' || to_char(p_strategy_work_item_rec.work_item_id), print_date => 'Y');
480 END IF;
481 AS_UTILITY_PVT.Set_Message(
482 p_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR,
483 p_msg_name => 'IEX_METAPHOR_CREATION_FAILED',
484 p_token1 => 'WORK_ITEM ',
485 p_token1_value => to_char(p_strategy_work_item_rec.work_item_id));
486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
487
488 END;
489 -- IF PG_DEBUG < 10 THEN
490 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
491 IEX_DEBUG_PUB.LogMessage('Update_strategy_work_items: ' || 'In if metaphor');
492 END IF;
493 end if ;
494 --end of profile enabled checking
495 end if ;
496 */
497
498
499
500
501
502 -- Standard call to get message count and if count is 1, get message info.
503 FND_MSG_PUB.Count_And_Get
504 ( p_count => x_msg_count,
505 p_data => x_msg_data
506 );
507 -- IF PG_DEBUG < 10 THEN
508 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
509 IEX_DEBUG_PUB.LogMessage ('********* end of Procedure =>IEX_strategy_work_items_PVT.update_strategy_work_items ******** ');
510 END IF;
511 EXCEPTION
512 WHEN FND_API.G_EXC_ERROR THEN
513 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
514 P_API_NAME => L_API_NAME
515 ,P_PKG_NAME => G_PKG_NAME
516 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
517 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
518 ,X_MSG_COUNT => X_MSG_COUNT
519 ,X_MSG_DATA => X_MSG_DATA
520 ,X_RETURN_STATUS => X_RETURN_STATUS);
521
522 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
523 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
524 P_API_NAME => L_API_NAME
525 ,P_PKG_NAME => G_PKG_NAME
526 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
527 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
528 ,X_MSG_COUNT => X_MSG_COUNT
529 ,X_MSG_DATA => X_MSG_DATA
530 ,X_RETURN_STATUS => X_RETURN_STATUS);
531
532 WHEN OTHERS THEN
533 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
534 P_API_NAME => L_API_NAME
535 ,P_PKG_NAME => G_PKG_NAME
536 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
537 ,P_SQLCODE => SQLCODE
538 ,P_SQLERRM => SQLERRM
539 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
540 ,X_MSG_COUNT => X_MSG_COUNT
541 ,X_MSG_DATA => X_MSG_DATA
542 ,X_RETURN_STATUS => X_RETURN_STATUS);
543 End Update_strategy_work_items;
544
545
546 PROCEDURE Delete_strategy_work_items(
547 P_Api_Version_Number IN NUMBER,
548 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
549 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
550 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
551 p_WORK_ITEM_ID IN NUMBER,
552 X_Return_Status OUT NOCOPY VARCHAR2,
553 X_Msg_Count OUT NOCOPY NUMBER,
554 X_Msg_Data OUT NOCOPY VARCHAR2 )
555
556 IS
557 l_api_name CONSTANT VARCHAR2(30) := 'Delete_strategy_work_items';
558 l_api_version_number CONSTANT NUMBER := 2.0;
559 l_identity_sales_member_rec AS_SALES_MEMBER_PUB.Sales_member_rec_Type;
560 BEGIN
561 -- Standard Start of API savepoint
562 SAVEPOINT DELETE_strategy_work_items_PVT;
563
564 -- Standard call to check for call compatibility.
565 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
566 p_api_version_number,
567 l_api_name,
568 G_PKG_NAME)
569 THEN
570 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
571 END IF;
572
573
574 -- Initialize message list if p_init_msg_list is set to TRUE.
575 IF FND_API.to_Boolean( p_init_msg_list )
576 THEN
577 FND_MSG_PUB.initialize;
578 END IF;
579
580
581
582 -- Initialize API return status to SUCCESS
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 --
586 -- Api body
587 --
588
589 -- Invoke table handler(IEX_STRATEGY_WORK_ITEMS_PKG.Delete_Row)
590 IEX_STRATEGY_WORK_ITEMS_PKG.Delete_Row(p_WORK_ITEM_ID);
591
592 --
593 -- End of API body
594 --
595
596 -- Standard check for p_commit
597 IF FND_API.to_Boolean( p_commit )
598 THEN
599 COMMIT WORK;
600 END IF;
601
602 -- Standard call to get message count and if count is 1, get message info.
603 FND_MSG_PUB.Count_And_Get
604 ( p_count => x_msg_count,
605 p_data => x_msg_data
606 );
607
608 EXCEPTION
609 WHEN FND_API.G_EXC_ERROR THEN
610 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
611 P_API_NAME => L_API_NAME
612 ,P_PKG_NAME => G_PKG_NAME
613 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
614 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
615 ,X_MSG_COUNT => X_MSG_COUNT
616 ,X_MSG_DATA => X_MSG_DATA
617 ,X_RETURN_STATUS => X_RETURN_STATUS);
618
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
621 P_API_NAME => L_API_NAME
622 ,P_PKG_NAME => G_PKG_NAME
623 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
624 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
625 ,X_MSG_COUNT => X_MSG_COUNT
626 ,X_MSG_DATA => X_MSG_DATA
627 ,X_RETURN_STATUS => X_RETURN_STATUS);
628
629 WHEN OTHERS THEN
630 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
631 P_API_NAME => L_API_NAME
632 ,P_PKG_NAME => G_PKG_NAME
633 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
634 ,P_SQLCODE => SQLCODE
635 ,P_SQLERRM => SQLERRM
636 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
637 ,X_MSG_COUNT => X_MSG_COUNT
638 ,X_MSG_DATA => X_MSG_DATA
639 ,X_RETURN_STATUS => X_RETURN_STATUS);
640 End Delete_strategy_work_items;
641
642
643 End IEX_strategy_work_items_PVT;