[Home] [Help]
PACKAGE BODY: APPS.IEX_CASES_PVT
Source
1 PACKAGE BODY IEX_CASES_PVT as
2 /* $Header: iexvcasb.pls 120.2 2006/05/30 17:52:15 scherkas noship $ */
3 -- Start of Comments
4 -- Package name : IEX_CASES_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IEX_CASES_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'iexvaseb.pls';
13
14 --PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
15 PG_DEBUG NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16
17 PROCEDURE Create_cas(
18 P_Api_Version_Number IN NUMBER,
19 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
20 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
21 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 P_cas_Rec IN cas_Rec_Type := G_MISS_cas_REC,
23 X_CASE_ID OUT NOCOPY NUMBER,
24 X_Return_Status OUT NOCOPY VARCHAR2,
25 X_Msg_Count OUT NOCOPY NUMBER,
26 X_Msg_Data OUT NOCOPY VARCHAR2
27 )
28
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CAS';
31 l_api_version_number CONSTANT NUMBER := 2.0;
32 v_org_id iex_cases_all_b.org_id%TYPE;
33 v_cas_id iex_cases_all_b.cas_id%TYPE;
34 v_case_number iex_cases_all_b.case_number%TYPE;
35 v_active_flag iex_cases_all_b.active_flag%TYPE;
36 v_object_version_number iex_cases_all_b.object_version_number%TYPE;
37 v_status_code iex_cases_all_b.status_code%TYPE;
38 v_case_state iex_cases_all_b.case_state%TYPE;
39 v_CASE_ESTABLISHED_DATE DATE;
40 v_rowid VARCHAR2(24);
41 Cursor c2 is SELECT IEX_CASES_ALL_B_S.nextval from dual;
42
43 BEGIN
44 -- IF PG_DEBUG < 10 THEN
45 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
46 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PVT.Create_cas ******** ');
47 END IF;
48 -- Standard Start of API savepoint
49 SAVEPOINT CREATE_CAS_PVT;
50
51 -- Standard call to check for call compatibility.
52 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
53 p_api_version_number,
54 l_api_name,
55 G_PKG_NAME)
56 THEN
57 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
58 END IF;
59 -- IF PG_DEBUG < 10 THEN
60 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
61 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After Compatibility Check');
62 END IF;
63
64 -- Initialize message list if p_init_msg_list is set to TRUE.
65 IF FND_API.to_Boolean( p_init_msg_list )
66 THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70
71 -- Initialize API return status to SUCCESS
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73 --
74 -- API body
75 --
76 -- ******************************************************************
77 -- Validate Environment
78 -- ******************************************************************
79 IF FND_GLOBAL.User_Id IS NULL
80 THEN
81 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
82 THEN
83 FND_MESSAGE.Set_Name('IEX', 'IEX_CANNOT_GET_PROFILE_VALUE');
84 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
85 FND_MSG_PUB.ADD;
86 END IF;
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89 -- IF PG_DEBUG < 10 THEN
90 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
91 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After Global user Check');
92 END IF;
93
94 -- Get org_id if not present
95 IF (p_cas_rec.org_id IS NULL) OR (p_cas_rec.ORG_ID = FND_API.G_MISS_NUM) THEN
96 --Bug#4679639 schekuri 20-OCT-2005
97 --Used mo_global.get_current_org_id to get ORG_ID
98 --v_org_id := fnd_profile.value('ORG_ID');
99 v_org_id := mo_global.get_current_org_id;
100 else
101 v_org_id :=p_cas_rec.org_id;
102 END IF;
103 -- IF PG_DEBUG < 10 THEN
104 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
105 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After ORG ID Check and Org_id is => '||v_org_id);
106 END IF;
107
108 -- Set Status_code to 'CURRENT'
109 v_status_code :='CURRENT';
110 -- Set Status_code to Open
111 v_case_state :='OPEN';
112
113 --object version Number
114 v_object_version_number :=1;
115 -- get cas_id
116 OPEN C2;
117 FETCH C2 INTO v_CAS_ID;
118 CLOSE C2;
119 -- IF PG_DEBUG < 10 THEN
120 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
121 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After CAS ID Check and cas_id is => '||v_cas_id);
122 END IF;
123
124 --Case number
125 If (p_cas_rec.CASE_NUMBER IS NULL) OR (p_cas_rec.CASE_NUMBER = FND_API.G_MISS_CHAR) then
126 v_case_number:=v_cas_id;
127 else
128 v_case_number := p_cas_rec.CASE_NUMBER;
129 end if;
130 -- IF PG_DEBUG < 10 THEN
131 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
132 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After CAS Number Check and cas_number is => '||v_case_number);
133 END IF;
134
135 --Active_flag
136 v_active_flag:='Y';
137 -- IF PG_DEBUG < 10 THEN
138 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
139 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After active flag Check' );
140 END IF;
141
142 --Case established Date
143 If (p_cas_rec.CASE_ESTABLISHED_DATE IS NULL) OR (p_cas_rec.CASE_ESTABLISHED_DATE = FND_API.G_MISS_DATE) then
144 v_CASE_ESTABLISHED_DATE:=sysdate;
145 else
146 v_CASE_ESTABLISHED_DATE := p_cas_rec.CASE_ESTABLISHED_DATE;
147 end if;
148 -- IF PG_DEBUG < 10 THEN
149 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
150 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After Case ESTABLISHED Date Check and case_ESTABLISHED is => '||v_case_ESTABLISHED_DATE);
151 END IF;
152 -- Added on 11/21/01
153 --Party Id check
154 IF (p_cas_rec.party_id IS NULL) OR (p_cas_rec.party_id =FND_API.G_MISS_NUM) THEN
155 fnd_message.set_name('IEX', 'IEX_API_ALL_MISSING_PARAM');
156 fnd_message.set_token('API_NAME', l_api_name);
157 fnd_message.set_token('MISSING_PARAM', 'party_id');
158 fnd_msg_pub.add;
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161 -- IF PG_DEBUG < 10 THEN
162 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
163 IEX_DEBUG_PUB.LogMessage ('Create_cas: ' || 'After Party id check');
164 END IF;
165
166 -- IF PG_DEBUG < 10 THEN
167 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
168 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'Before Calling iex_cases_pkg.insert_row');
169 END IF;
170 -- Invoke table handler(IEX_CASES_ALL_B_PKG.Insert_Row)
171 IEX_CASES_PKG.Insert_Row(
172 x_rowid =>v_rowid,
173 x_CAS_ID => v_CAS_ID,
174 x_CASE_NUMBER => v_CASE_NUMBER,
175 x_active_flag => v_active_flag,
176 x_party_id => p_cas_rec.party_id,
177 x_ORIG_CAS_ID => p_cas_rec.orig_cas_id,
178 x_CASE_STATE => v_CASE_STATE,
179 x_STATUS_CODE => v_STATUS_CODE,
180 x_OBJECT_VERSION_NUMBER => v_OBJECT_VERSION_NUMBER,
181 x_CASE_ESTABLISHED_DATE => v_CASE_ESTABLISHED_DATE,
182 x_CASE_CLOSING_DATE => p_cas_rec.CASE_CLOSING_DATE,
183 X_OWNER_RESOURCE_ID => p_cas_rec.OWNER_RESOURCE_ID,
184 x_ACCESS_RESOURCE_ID => p_cas_rec.ACCESS_RESOURCE_ID,
185 X_COMMENTS =>P_CAS_REC.COMMENTS,
186 X_PREDICTED_RECOVERY_AMOUNT =>p_cas_rec.PREDICTED_RECOVERY_AMOUNT,
187 X_PREDICTED_CHANCE =>p_cas_rec.PREDICTED_CHANCE,
188 x_REQUEST_ID => p_cas_rec.REQUEST_ID,
189 x_PROGRAM_APPLICATION_ID => p_cas_rec.PROGRAM_APPLICATION_ID,
190 x_PROGRAM_ID => p_cas_rec.PROGRAM_ID,
191 x_PROGRAM_UPDATE_DATE => p_cas_rec.PROGRAM_UPDATE_DATE,
192 x_ATTRIBUTE_CATEGORY => p_cas_rec.ATTRIBUTE_CATEGORY,
193 x_ATTRIBUTE1 => p_cas_rec.ATTRIBUTE1,
194 x_ATTRIBUTE2 => p_cas_rec.ATTRIBUTE2,
195 x_ATTRIBUTE3 => p_cas_rec.ATTRIBUTE3,
196 x_ATTRIBUTE4 => p_cas_rec.ATTRIBUTE4,
197 x_ATTRIBUTE5 => p_cas_rec.ATTRIBUTE5,
198 x_ATTRIBUTE6 => p_cas_rec.ATTRIBUTE6,
199 x_ATTRIBUTE7 => p_cas_rec.ATTRIBUTE7,
200 x_ATTRIBUTE8 => p_cas_rec.ATTRIBUTE8,
201 x_ATTRIBUTE9 => p_cas_rec.ATTRIBUTE9,
202 x_ATTRIBUTE10 => p_cas_rec.ATTRIBUTE10,
203 x_ATTRIBUTE11 => p_cas_rec.ATTRIBUTE11,
204 x_ATTRIBUTE12 => p_cas_rec.ATTRIBUTE12,
205 x_ATTRIBUTE13 => p_cas_rec.ATTRIBUTE13,
206 x_ATTRIBUTE14 => p_cas_rec.ATTRIBUTE14,
207 x_ATTRIBUTE15 => p_cas_rec.ATTRIBUTE15,
208 x_CREATED_BY => FND_GLOBAL.USER_ID,
209 X_CREATION_DATE => SYSDATE,
210 x_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
211 X_LAST_UPDATE_DATE => SYSDATE,
212 x_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
213 x_CLOSE_REASON => p_cas_rec.CLOSE_REASON,
214 x_org_id =>v_org_id);
215
216 -- Hint: Primary key should be returned.
217 x_CASE_ID := v_CAS_ID;
218 -- IF PG_DEBUG < 10 THEN
219 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
220 IEX_DEBUG_PUB.LogMessage('Create_cas: ' || 'After Calling iex_cases_pkg.insert_row and case id => '||x_case_id);
221 END IF;
222
223
224
225 -- Check return status from the above procedure call
226 IF x_return_status = FND_API.G_RET_STS_ERROR then
227 raise FND_API.G_EXC_ERROR;
228 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
229 raise FND_API.G_EXC_UNEXPECTED_ERROR;
230 END IF;
231
232 --
233 -- End of API body.
234 --
235
236 -- Standard check for p_commit
237 IF FND_API.to_Boolean( p_commit )
238 THEN
239 COMMIT WORK;
240 END IF;
241
242
243 -- Standard call to get message count and if count is 1, get message info.
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count,
246 p_data => x_msg_data
247 );
248
249 -- IF PG_DEBUG < 10 THEN
250 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
251 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PVT.Create_cas ******** ');
252 END IF;
253 EXCEPTION
254 WHEN FND_API.G_EXC_ERROR THEN
255 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
256 P_API_NAME => L_API_NAME
257 ,P_PKG_NAME => G_PKG_NAME
258 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
259 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
260 ,X_MSG_COUNT => X_MSG_COUNT
261 ,X_MSG_DATA => X_MSG_DATA
262 ,X_RETURN_STATUS => X_RETURN_STATUS);
263
264 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
265 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
266 P_API_NAME => L_API_NAME
267 ,P_PKG_NAME => G_PKG_NAME
268 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
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
274 WHEN OTHERS THEN
275 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
276 P_API_NAME => L_API_NAME
277 ,P_PKG_NAME => G_PKG_NAME
278 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
279 ,P_SQLCODE => SQLCODE
280 ,P_SQLERRM => SQLERRM
281 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
282 ,X_MSG_COUNT => X_MSG_COUNT
283 ,X_MSG_DATA => X_MSG_DATA
284 ,X_RETURN_STATUS => X_RETURN_STATUS);
285 End Create_cas;
286
287
288 -- Hint: Add corresponding update detail table procedures if it's master-detail relationship.
289 PROCEDURE Update_cas(
290 P_Api_Version_Number IN NUMBER,
291 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
292 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
293 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
294 P_cas_Rec IN cas_Rec_Type,
295 X_Return_Status OUT NOCOPY VARCHAR2,
296 X_Msg_Count OUT NOCOPY NUMBER,
297 X_Msg_Data OUT NOCOPY VARCHAR2,
298 XO_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER
299 )
300
301 IS
302 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CAS';
303 l_api_version_number CONSTANT NUMBER := 2.0;
304 l_object_version_number iex_cases_all_b.object_version_number%TYPE:=p_cas_rec.object_version_number;
305
306 BEGIN
307 -- IF PG_DEBUG < 10 THEN
308 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
309 IEX_DEBUG_PUB.LogMessage ('********* Start of Procedure =>IEX_CASES_PVT.update_cas ******** ');
310 END IF;
311 -- Standard Start of API savepoint
312 SAVEPOINT UPDATE_CAS_PVT;
313
314 -- Standard call to check for call compatibility.
315 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
316 p_api_version_number,
317 l_api_name,
318 G_PKG_NAME)
319 THEN
320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
321 END IF;
322
323
324 -- Initialize message list if p_init_msg_list is set to TRUE.
325 IF FND_API.to_Boolean( p_init_msg_list )
326 THEN
327 FND_MSG_PUB.initialize;
328 END IF;
329
330
331 -- Initialize API return status to SUCCESS
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 --
335 -- API body
336 --
337
338 -- IF PG_DEBUG < 10 THEN
339 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
340 IEX_DEBUG_PUB.LogMessage('Update_cas: ' || 'Before Calling iex_cases_pkg.lock_row');
341 END IF;
342 -- Invoke table handler(IEX_CASES_ALL_B_PKG.Update_Row)
343 -- call locking table handler
344 IEX_CASES_PKG.lock_row (
345 p_cas_rec.cas_id,
346 l_object_version_number
347 );
348 -- IF PG_DEBUG < 10 THEN
349 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
350 IEX_DEBUG_PUB.LogMessage('Update_cas: ' || 'Before Calling iex_cases_pkg.update_row');
351 END IF;
352 IEX_CASES_PKG.Update_Row(
353 x_CAS_ID => p_cas_rec.CAS_ID,
354 x_CASE_NUMBER => p_cas_rec.CASE_NUMBER,
355 x_ACTIVE_FLAG => p_cas_rec.ACTIVE_FLAG,
356 x_PARTY_ID => p_cas_rec.PARTY_ID,
357 x_ORIG_CAS_ID => p_cas_rec.orig_cas_id,
358 x_CASE_STATE => p_cas_rec.CASE_STATE,
359 x_STATUS_CODE => p_cas_rec.STATUS_CODE,
360 x_OBJECT_VERSION_NUMBER => l_OBJECT_VERSION_NUMBER +1,
361 x_CASE_ESTABLISHED_DATE => p_cas_rec.CASE_ESTABLISHED_DATE,
362 x_CASE_CLOSING_DATE => p_cas_rec.CASE_CLOSING_DATE,
363 x_OWNER_RESOURCE_ID => p_cas_rec.OWNER_RESOURCE_ID,
364 x_ACCESS_RESOURCE_ID => p_cas_rec.ACCESS_RESOURCE_ID,
365 x_REQUEST_ID => p_cas_rec.REQUEST_ID,
366 X_COMMENTS =>P_CAS_REC.COMMENTS,
367 X_PREDICTED_RECOVERY_AMOUNT =>p_cas_rec.PREDICTED_RECOVERY_AMOUNT,
368 X_PREDICTED_CHANCE =>p_cas_rec.PREDICTED_CHANCE,
369 x_PROGRAM_APPLICATION_ID => p_cas_rec.PROGRAM_APPLICATION_ID,
370 x_PROGRAM_ID => p_cas_rec.PROGRAM_ID,
371 x_PROGRAM_UPDATE_DATE => p_cas_rec.PROGRAM_UPDATE_DATE,
372 x_ATTRIBUTE_CATEGORY => p_cas_rec.ATTRIBUTE_CATEGORY,
373 x_ATTRIBUTE1 => p_cas_rec.ATTRIBUTE1,
374 x_ATTRIBUTE2 => p_cas_rec.ATTRIBUTE2,
375 x_ATTRIBUTE3 => p_cas_rec.ATTRIBUTE3,
376 x_ATTRIBUTE4 => p_cas_rec.ATTRIBUTE4,
377 x_ATTRIBUTE5 => p_cas_rec.ATTRIBUTE5,
378 x_ATTRIBUTE6 => p_cas_rec.ATTRIBUTE6,
379 x_ATTRIBUTE7 => p_cas_rec.ATTRIBUTE7,
380 x_ATTRIBUTE8 => p_cas_rec.ATTRIBUTE8,
381 x_ATTRIBUTE9 => p_cas_rec.ATTRIBUTE9,
382 x_ATTRIBUTE10 => p_cas_rec.ATTRIBUTE10,
383 x_ATTRIBUTE11 => p_cas_rec.ATTRIBUTE11,
384 x_ATTRIBUTE12 => p_cas_rec.ATTRIBUTE12,
385 x_ATTRIBUTE13 => p_cas_rec.ATTRIBUTE13,
386 x_ATTRIBUTE14 => p_cas_rec.ATTRIBUTE14,
387 x_ATTRIBUTE15 => p_cas_rec.ATTRIBUTE15,
388 x_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
389 x_LAST_UPDATE_DATE => SYSDATE,
390 x_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
391 x_CLOSE_REASON => p_cas_rec.CLOSE_REASON,
392 x_org_id =>p_cas_rec.org_id);
393
394 --Return Version number
395 xo_object_version_number := l_object_version_number + 1;
396 -- Check return status from the above procedure call
397 IF x_return_status = FND_API.G_RET_STS_ERROR then
398 raise FND_API.G_EXC_ERROR;
399 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
400 raise FND_API.G_EXC_UNEXPECTED_ERROR;
401 END IF;
402
403 --
404 -- End of API body
405 --
406
407 -- Standard check for p_commit
408 IF FND_API.to_Boolean( p_commit )
409 THEN
410 COMMIT WORK;
411 END IF;
412
413
414 -- Standard call to get message count and if count is 1, get message info.
415 FND_MSG_PUB.Count_And_Get
416 ( p_count => x_msg_count,
417 p_data => x_msg_data
418 );
419 -- IF PG_DEBUG < 10 THEN
420 IF (FND_LOG.LEVEL_EVENT >= PG_DEBUG) THEN
421 IEX_DEBUG_PUB.LogMessage ('********* End of Procedure =>IEX_CASES_PVT.Update_cas ******** ');
422 END IF;
423 EXCEPTION
424 WHEN FND_API.G_EXC_ERROR THEN
425 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
426 P_API_NAME => L_API_NAME
427 ,P_PKG_NAME => G_PKG_NAME
428 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
429 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
430 ,X_MSG_COUNT => X_MSG_COUNT
431 ,X_MSG_DATA => X_MSG_DATA
432 ,X_RETURN_STATUS => X_RETURN_STATUS);
433
434 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
435 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
436 P_API_NAME => L_API_NAME
437 ,P_PKG_NAME => G_PKG_NAME
438 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
439 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
440 ,X_MSG_COUNT => X_MSG_COUNT
441 ,X_MSG_DATA => X_MSG_DATA
442 ,X_RETURN_STATUS => X_RETURN_STATUS);
443
444 WHEN OTHERS THEN
445 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
446 P_API_NAME => L_API_NAME
447 ,P_PKG_NAME => G_PKG_NAME
448 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
449 ,P_SQLCODE => SQLCODE
450 ,P_SQLERRM => SQLERRM
451 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
452 ,X_MSG_COUNT => X_MSG_COUNT
453 ,X_MSG_DATA => X_MSG_DATA
454 ,X_RETURN_STATUS => X_RETURN_STATUS);
455 End Update_cas;
456
457
458 -- Hint: Add corresponding delete detail table procedures if it's master-detail relationship.
459 -- The Master delete procedure may not be needed depends on different business requirements.
460 PROCEDURE Delete_cas(
461 P_Api_Version_Number IN NUMBER,
462 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
463 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
464 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
465 P_cas_Id IN NUMBER,
466 X_Return_Status OUT NOCOPY VARCHAR2,
467 X_Msg_Count OUT NOCOPY NUMBER,
468 X_Msg_Data OUT NOCOPY VARCHAR2
469 )
470
471 IS
472 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_CAS';
473 l_api_version_number CONSTANT NUMBER := 2.0;
474
475 BEGIN
476 -- Standard Start of API savepoint
477 SAVEPOINT DELETE_CAS_PVT;
478
479 -- Standard call to check for call compatibility.
480 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
481 p_api_version_number,
482 l_api_name,
483 G_PKG_NAME)
484 THEN
485 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
486 END IF;
487
488
489 -- Initialize message list if p_init_msg_list is set to TRUE.
490 IF FND_API.to_Boolean( p_init_msg_list )
491 THEN
492 FND_MSG_PUB.initialize;
493 END IF;
494
495
496 -- Initialize API return status to SUCCESS
497 x_return_status := FND_API.G_RET_STS_SUCCESS;
498 --
499 -- API body
500 --
501
502 -- call table handler to insert into jtf_tasks_temp_groups
503 iex_cases_pkg.delete_row (p_cas_id);
504
505 -- Check return status from the above procedure call
506 IF x_return_status = FND_API.G_RET_STS_ERROR then
507 raise FND_API.G_EXC_ERROR;
508 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
509 raise FND_API.G_EXC_UNEXPECTED_ERROR;
510 END IF;
511
512 --
513 -- End of API body
514 --
515
516 -- Standard check for p_commit
517 IF FND_API.to_Boolean( p_commit )
518 THEN
519 COMMIT WORK;
520 END IF;
521
522
523 -- Standard call to get message count and if count is 1, get message info.
524 FND_MSG_PUB.Count_And_Get
525 ( p_count => x_msg_count,
526 p_data => x_msg_data
527 );
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
531 P_API_NAME => L_API_NAME
532 ,P_PKG_NAME => G_PKG_NAME
533 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
534 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
535 ,X_MSG_COUNT => X_MSG_COUNT
536 ,X_MSG_DATA => X_MSG_DATA
537 ,X_RETURN_STATUS => X_RETURN_STATUS);
538
539 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
540 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
541 P_API_NAME => L_API_NAME
542 ,P_PKG_NAME => G_PKG_NAME
543 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
544 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
545 ,X_MSG_COUNT => X_MSG_COUNT
546 ,X_MSG_DATA => X_MSG_DATA
547 ,X_RETURN_STATUS => X_RETURN_STATUS);
548
549 WHEN OTHERS THEN
550 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
551 P_API_NAME => L_API_NAME
552 ,P_PKG_NAME => G_PKG_NAME
553 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
554 ,P_SQLCODE => SQLCODE
555 ,P_SQLERRM => SQLERRM
556 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
557 ,X_MSG_COUNT => X_MSG_COUNT
558 ,X_MSG_DATA => X_MSG_DATA
559 ,X_RETURN_STATUS => X_RETURN_STATUS);
560 End Delete_cas;
561
562
563
564
565 End IEX_CASES_PVT;