[Home] [Help]
PACKAGE BODY: APPS.AMS_CODE_DEFINITION_PVT
Source
1 PACKAGE BODY AMS_Code_Definition_PVT as
2 /* $Header: amsvcdnb.pls 120.1 2005/06/27 05:41:11 appldev ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Code_Definition_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Code_Definition_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvcdnb.pls';
19
20 -- Hint: Primary key needs to be returned.
21 PROCEDURE Create_Code_Definition(
22 p_api_version_number IN NUMBER,
23 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
24 p_commit IN VARCHAR2 := FND_API.G_FALSE,
25 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
26
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2,
30
31 p_code_def_rec IN code_def_rec_type := g_miss_code_def_rec,
32 x_code_definition_id OUT NOCOPY NUMBER
33 )
34
35 IS
36 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Code_Definition';
37 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
38 l_return_status_full VARCHAR2(1);
39 l_object_version_number NUMBER := 1;
40 l_org_id NUMBER := FND_API.G_MISS_NUM;
41 l_CODE_DEFINITION_ID NUMBER;
42 l_dummy NUMBER;
43
44
45 CURSOR c_id IS
46 SELECT BIM_R_CODE_DEFINITIONS_s.NEXTVAL
47 FROM dual;
48
49 CURSOR c_id_exists (l_id IN NUMBER) IS
50 SELECT 1
51 FROM BIM_R_CODE_DEFINITIONS
52 WHERE CODE_DEFINITION_ID = l_id
53 ;
54
55 CURSOR c_col_name(l_colName IN VARCHAR2, l_obj_type IN VARCHAR2)IS
56 SELECT count(*)
57 FROM BIM_R_CODE_DEFINITIONS
58 WHERE column_name = l_colName
59 AND column_name<>'Z'
60 AND object_type = l_obj_type
61 ;
62
63 BEGIN
64 -- Standard Start of API savepoint
65 SAVEPOINT CREATE_Code_Definition_PVT;
66
67 -- Standard call to check for call compatibility.
68 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
69 p_api_version_number,
70 l_api_name,
71 G_PKG_NAME)
72 THEN
73 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
74 END IF;
75
76 -- Initialize message list if p_init_msg_list is set to TRUE.
77 IF FND_API.to_Boolean( p_init_msg_list )
78 THEN
79 FND_MSG_PUB.initialize;
80 END IF;
81
82 -- Debug Message
83 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
84
85
86 -- Initialize API return status to SUCCESS
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 -- Local variable initialization
90
91 IF p_code_def_rec.CODE_DEFINITION_ID IS NULL OR p_code_def_rec.CODE_DEFINITION_ID = FND_API.g_miss_num THEN
92 LOOP
93 l_dummy := NULL;
94 OPEN c_id;
95 FETCH c_id INTO l_CODE_DEFINITION_ID;
96 CLOSE c_id;
97
98 OPEN c_id_exists(l_CODE_DEFINITION_ID);
99 FETCH c_id_exists INTO l_dummy;
100 CLOSE c_id_exists;
101 EXIT WHEN l_dummy IS NULL;
102 --ELSE
103 -- p_code_def_rec.code_definition_id := l_code_definition_id;
104 END LOOP;
105 END IF;
106
107 -- =========================================================================
108 -- Validate Environment
109 -- =========================================================================
110
111 IF FND_GLOBAL.User_Id IS NULL
112 THEN
113 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
114 RAISE FND_API.G_EXC_ERROR;
115 END IF;
116
117 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
118 THEN
119 -- Debug message
120 AMS_UTILITY_PVT.debug_message('Private API: Validate_Code_Definition');
121
122 -- Invoke validation procedures
123 Validate_code_definition(
124 p_api_version_number => 1.0,
125 p_init_msg_list => FND_API.G_FALSE,
126 p_validation_level => p_validation_level,
127 p_validation_mode => JTF_PLSQL_API.g_create,
128 p_code_def_rec => p_code_def_rec,
129 x_return_status => x_return_status,
130 x_msg_count => x_msg_count,
131 x_msg_data => x_msg_data);
132 END IF;
133
134 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
135 RAISE FND_API.G_EXC_ERROR;
136 END IF;
137
138 IF p_code_def_rec.object_def IS NULL OR p_code_def_rec.object_def = FND_API.g_miss_char THEN
139 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
140 FND_MESSAGE.set_name('AMS', 'AMS_OBJECT_TYPE_MISSING');
141 FND_MSG_PUB.add;
142 END IF;
143 RAISE FND_API.g_exc_error;
144 END IF;
145
146 OPEN c_col_name(p_code_def_rec.column_name,p_code_def_rec.object_type);
147 FETCH c_col_name INTO l_dummy;
148 CLOSE c_col_name;
149
150 IF (l_dummy >0) THEN
151 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
152 FND_MESSAGE.set_name('AMS', 'AMS_DUPLICATE_COLUMN_NAME');
153 FND_MSG_PUB.add;
154 END IF;
155 RAISE FND_API.g_exc_error;
156 END IF;
157 -- Debug Message
158 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
159
160 -- Invoke table handler(AMS_R_CODE_DEFINITIONS_PKG.Insert_Row)
161 AMS_R_CODE_DEFINITIONS_PKG.Insert_Row(
162 p_creation_date => SYSDATE,
163 p_last_update_date => SYSDATE,
164 p_created_by => FND_GLOBAL.USER_ID,
165 p_last_updated_by => FND_GLOBAL.USER_ID,
166 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
167 p_object_type => p_code_def_rec.object_type,
168 p_column_name => p_code_def_rec.column_name,
169 p_object_def => p_code_def_rec.object_def,
170 px_code_definition_id => l_code_definition_id,
171 px_object_version_number => l_object_version_number);
172 x_code_definition_id := l_code_definition_id;
173 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176 --
177 -- End of API body
178 --
179
180 -- Standard check for p_commit
181 IF FND_API.to_Boolean( p_commit )
182 THEN
183 COMMIT WORK;
184 END IF;
185
186
187 -- Debug Message
188 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
189
190 -- Standard call to get message count and if count is 1, get message info.
191 FND_MSG_PUB.Count_And_Get
192 (p_count => x_msg_count,
193 p_data => x_msg_data
194 );
195 EXCEPTION
196
197 WHEN AMS_Utility_PVT.resource_locked THEN
198 x_return_status := FND_API.g_ret_sts_error;
199 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
200
201 WHEN FND_API.G_EXC_ERROR THEN
202 ROLLBACK TO CREATE_Code_Definition_PVT;
203 x_return_status := FND_API.G_RET_STS_ERROR;
204 -- Standard call to get message count and if count=1, get the message
205 FND_MSG_PUB.Count_And_Get (
206 p_encoded => FND_API.G_FALSE,
207 p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210
211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
212 ROLLBACK TO CREATE_Code_Definition_PVT;
213 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
214 -- Standard call to get message count and if count=1, get the message
215 FND_MSG_PUB.Count_And_Get (
216 p_encoded => FND_API.G_FALSE,
217 p_count => x_msg_count,
218 p_data => x_msg_data
219 );
220
221 WHEN OTHERS THEN
222 ROLLBACK TO CREATE_Code_Definition_PVT;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
224 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
225 THEN
226 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
227 END IF;
228 -- Standard call to get message count and if count=1, get the message
229 FND_MSG_PUB.Count_And_Get (
230 p_encoded => FND_API.G_FALSE,
231 p_count => x_msg_count,
232 p_data => x_msg_data
233 );
234 End Create_Code_Definition;
235
236 PROCEDURE Complete_code_def_Rec (
237 p_code_def_rec IN code_def_rec_type,
238 x_complete_rec OUT NOCOPY code_def_rec_type)
239 IS
240 l_return_status VARCHAR2(1);
241
242 CURSOR c_complete IS
243 SELECT *
244 FROM bim_r_code_definitions
245 WHERE code_definition_id = p_code_def_rec.code_definition_id;
246 l_code_def_rec c_complete%ROWTYPE;
247 BEGIN
248 x_complete_rec := p_code_def_rec;
249
250
251 OPEN c_complete;
252 FETCH c_complete INTO l_code_def_rec;
253 CLOSE c_complete;
254
255 -- creation_date
256 IF p_code_def_rec.creation_date = FND_API.g_miss_date THEN
257 x_complete_rec.creation_date := l_code_def_rec.creation_date;
258 END IF;
259
260 -- last_update_date
261 IF p_code_def_rec.last_update_date = FND_API.g_miss_date THEN
262 x_complete_rec.last_update_date := l_code_def_rec.last_update_date;
263 END IF;
264
265 -- created_by
266 IF p_code_def_rec.created_by = FND_API.g_miss_num THEN
267 x_complete_rec.created_by := l_code_def_rec.created_by;
268 END IF;
269
270 -- last_updated_by
271 IF p_code_def_rec.last_updated_by = FND_API.g_miss_num THEN
272 x_complete_rec.last_updated_by := l_code_def_rec.last_updated_by;
273 END IF;
274
275 -- last_update_login
276 IF p_code_def_rec.last_update_login = FND_API.g_miss_num THEN
277 x_complete_rec.last_update_login := l_code_def_rec.last_update_login;
278 END IF;
279
280 -- object_type
281 IF p_code_def_rec.object_type = FND_API.g_miss_char THEN
282 x_complete_rec.object_type := l_code_def_rec.object_type;
283 END IF;
284
285 -- column_name
286 IF p_code_def_rec.column_name = FND_API.g_miss_char THEN
287 x_complete_rec.column_name := l_code_def_rec.column_name;
288 END IF;
289
290 -- object_def
291 IF p_code_def_rec.object_def = FND_API.g_miss_char THEN
292 x_complete_rec.object_def := l_code_def_rec.object_def;
293 END IF;
294
295 -- code_definition_id
296 IF p_code_def_rec.code_definition_id = FND_API.g_miss_num THEN
297 x_complete_rec.code_definition_id := l_code_def_rec.code_definition_id;
298 END IF;
299
300 -- object_version_number
301 IF p_code_def_rec.object_version_number = FND_API.g_miss_num THEN
302 x_complete_rec.object_version_number := l_code_def_rec.object_version_number;
303 END IF;
304 -- Note: Developers need to modify the procedure
305 -- to handle any business specific requirements.
306 END Complete_code_def_Rec;
307
308
309 PROCEDURE Update_Code_Definition(
310 p_api_version_number IN NUMBER,
311 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
312 p_commit IN VARCHAR2 := FND_API.G_FALSE,
313 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
314
315 x_return_status OUT NOCOPY VARCHAR2,
316 x_msg_count OUT NOCOPY NUMBER,
317 x_msg_data OUT NOCOPY VARCHAR2,
318
319 p_code_def_rec IN code_def_rec_type,
320 x_object_version_number OUT NOCOPY NUMBER
321 )
322
323 IS
324
325 CURSOR c_get_code_definition(creation_date DATE) IS
326 SELECT *
327 FROM BIM_R_CODE_DEFINITIONS
328 WHERE code_definition_id = p_code_def_rec.code_definition_id;
329 -- Hint: Developer need to provide Where clause
330
331 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Code_Definition';
332 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
333 -- Local Variables
334 l_object_version_number NUMBER;
335 l_CODE_DEFINITION_ID NUMBER;
336 l_ref_code_def_rec c_get_Code_Definition%ROWTYPE ;
337 l_tar_code_def_rec AMS_Code_Definition_PVT.code_def_rec_type := P_code_def_rec;
338 l_rowid ROWID;
339
340 BEGIN
341 -- Standard Start of API savepoint
342 SAVEPOINT UPDATE_Code_Definition_PVT;
343
344 -- Standard call to check for call compatibility.
345 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
346 p_api_version_number,
347 l_api_name,
348 G_PKG_NAME)
349 THEN
350 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
351 END IF;
352
353 -- Initialize message list if p_init_msg_list is set to TRUE.
354 IF FND_API.to_Boolean( p_init_msg_list )
355 THEN
356 FND_MSG_PUB.initialize;
357 END IF;
358
359 -- Debug Message
360 --AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
361
362
363 -- Initialize API return status to SUCCESS
364 x_return_status := FND_API.G_RET_STS_SUCCESS;
365
366 -- Debug Message
367 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
368
369
370 OPEN c_get_Code_Definition( l_tar_code_def_rec.creation_date);
371
372 FETCH c_get_Code_Definition INTO l_ref_code_def_rec ;
373
374 If ( c_get_Code_Definition%NOTFOUND) THEN
375 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
376 p_token_name => 'INFO',
377 p_token_value => 'Code_Definition') ;
378 RAISE FND_API.G_EXC_ERROR;
379 END IF;
380 -- Debug Message
381 --AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
382 CLOSE c_get_Code_Definition;
383
384
385
386 If (l_tar_code_def_rec.object_version_number is NULL or
387 l_tar_code_def_rec.object_version_number = FND_API.G_MISS_NUM ) Then
388 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
389 p_token_name => 'COLUMN',
390 p_token_value => 'Last_Update_Date') ;
391 raise FND_API.G_EXC_ERROR;
392 End if;
393 -- Check Whether record has been changed by someone else
394 If (l_tar_code_def_rec.object_version_number <> l_ref_code_def_rec.object_version_number) Then
395 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
399 End if;
396 p_token_name => 'INFO',
397 p_token_value => 'Code_Definition') ;
398 raise FND_API.G_EXC_ERROR;
400 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
401 THEN
402 -- Debug message
403 --AMS_UTILITY_PVT.debug_message('Private API: Validate_Code_Definition');
404
405 -- Invoke validation procedures
406 Validate_code_definition(
407 p_api_version_number => 1.0,
408 p_init_msg_list => FND_API.G_FALSE,
409 p_validation_level => p_validation_level,
410 p_validation_mode => JTF_PLSQL_API.g_update,
411 p_code_def_rec => p_code_def_rec,
412 x_return_status => x_return_status,
413 x_msg_count => x_msg_count,
414 x_msg_data => x_msg_data);
415 END IF;
416
417 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420 -- replace g_miss_char/num/date with current column values
421
422 -- Debug Message
423 AMS_UTILITY_PVT.debug_message( 'Private API: Calling update table handler');
424 AMS_UTILITY_PVT.debug_message( ' p_code_def_rec.code_definition_id'||p_code_def_rec.code_definition_id);
425 AMS_UTILITY_PVT.debug_message( ' p_code_def_rec.code_definition_id'||l_tar_code_def_rec.code_definition_id);
426 AMS_UTILITY_PVT.debug_message( ' p_code_def_rec.l_object_version_number'||p_code_def_rec.object_version_number);
427 AMS_UTILITY_PVT.debug_message( ' p_code_def_rec.object_type'||p_code_def_rec.object_type);
428 AMS_UTILITY_PVT.debug_message( ' p_code_def_rec.column_name'||p_code_def_rec.column_name);
429 -- Invoke table handler(AMS_R_CODE_DEFINITIONS_PKG.Update_Row)
430 AMS_R_CODE_DEFINITIONS_PKG.Update_Row(
431 p_last_update_date => SYSDATE,
432 p_last_updated_by => FND_GLOBAL.USER_ID,
433 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
434 p_object_type => p_code_def_rec.object_type,
435 p_column_name => p_code_def_rec.column_name,
436 p_object_def => p_code_def_rec.object_def,
437 p_code_definition_id => p_code_def_rec.code_definition_id,
438 p_object_version_number => p_code_def_rec.object_version_number);
439 --x_object_version_number := l_trade_profile_rec.object_version_number;
440
441 --
442 -- End of API body.
443 --
444
445 -- Standard check for p_commit
446 IF FND_API.to_Boolean( p_commit )
447 THEN
448 COMMIT WORK;
449 END IF;
450
451
452 -- Debug Message
453 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
454
455 -- Standard call to get message count and if count is 1, get message info.
456 FND_MSG_PUB.Count_And_Get
457 (p_count => x_msg_count,
458 p_data => x_msg_data
459 );
460 EXCEPTION
461
462 WHEN AMS_Utility_PVT.resource_locked THEN
463 x_return_status := FND_API.g_ret_sts_error;
464 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
465
466 WHEN FND_API.G_EXC_ERROR THEN
467 ROLLBACK TO UPDATE_Code_Definition_PVT;
468 x_return_status := FND_API.G_RET_STS_ERROR;
469 -- Standard call to get message count and if count=1, get the message
470 FND_MSG_PUB.Count_And_Get (
471 p_encoded => FND_API.G_FALSE,
472 p_count => x_msg_count,
473 p_data => x_msg_data
474 );
475
476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
477 ROLLBACK TO UPDATE_Code_Definition_PVT;
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 -- Standard call to get message count and if count=1, get the message
480 FND_MSG_PUB.Count_And_Get (
481 p_encoded => FND_API.G_FALSE,
482 p_count => x_msg_count,
483 p_data => x_msg_data
484 );
485
486 WHEN OTHERS THEN
487 ROLLBACK TO UPDATE_Code_Definition_PVT;
488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
489 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
490 THEN
491 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
492 END IF;
493 -- Standard call to get message count and if count=1, get the message
494 FND_MSG_PUB.Count_And_Get (
495 p_encoded => FND_API.G_FALSE,
496 p_count => x_msg_count,
497 p_data => x_msg_data
498 );
499 End Update_Code_Definition;
500
501
502 PROCEDURE Delete_Code_Definition(
503 p_api_version_number IN NUMBER,
504 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
505 p_commit IN VARCHAR2 := FND_API.G_FALSE,
506 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
507 x_return_status OUT NOCOPY VARCHAR2,
508 x_msg_count OUT NOCOPY NUMBER,
509 x_msg_data OUT NOCOPY VARCHAR2,
510 p_code_definition_id IN NUMBER,
511 p_object_version_number IN NUMBER
512 )
513
514 IS
515 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Code_Definition';
516 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
517 l_object_version_number NUMBER;
518
519 BEGIN
520 -- Standard Start of API savepoint
521 SAVEPOINT DELETE_Code_Definition_PVT;
522
523 -- Standard call to check for call compatibility.
524 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
525 p_api_version_number,
526 l_api_name,
530 END IF;
527 G_PKG_NAME)
528 THEN
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
531
532 -- Initialize message list if p_init_msg_list is set to TRUE.
533 IF FND_API.to_Boolean( p_init_msg_list )
534 THEN
535 FND_MSG_PUB.initialize;
536 END IF;
537
538 -- Debug Message
539 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
540
541
542 -- Initialize API return status to SUCCESS
543 x_return_status := FND_API.G_RET_STS_SUCCESS;
544
545 --
546 -- Api body
547 --
548 -- Debug Message
549 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
550
551 -- Invoke table handler(AMS_R_CODE_DEFINITIONS_PKG.Delete_Row)
552 AMS_R_CODE_DEFINITIONS_PKG.Delete_Row(
553 p_CODE_DEFINITION_ID => p_CODE_DEFINITION_ID);
554 --
555 -- End of API body
556 --
557
558 -- Standard check for p_commit
559 IF FND_API.to_Boolean( p_commit )
560 THEN
561 COMMIT WORK;
562 END IF;
563
564
565 -- Debug Message
566 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
567
568 -- Standard call to get message count and if count is 1, get message info.
569 FND_MSG_PUB.Count_And_Get
570 (p_count => x_msg_count,
571 p_data => x_msg_data
572 );
573 EXCEPTION
574
575 WHEN AMS_Utility_PVT.resource_locked THEN
576 x_return_status := FND_API.g_ret_sts_error;
577 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
578
579 WHEN FND_API.G_EXC_ERROR THEN
580 ROLLBACK TO DELETE_Code_Definition_PVT;
581 x_return_status := FND_API.G_RET_STS_ERROR;
582 -- Standard call to get message count and if count=1, get the message
583 FND_MSG_PUB.Count_And_Get (
584 p_encoded => FND_API.G_FALSE,
585 p_count => x_msg_count,
586 p_data => x_msg_data
587 );
588
589 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
590 ROLLBACK TO DELETE_Code_Definition_PVT;
591 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
592 -- Standard call to get message count and if count=1, get the message
593 FND_MSG_PUB.Count_And_Get (
594 p_encoded => FND_API.G_FALSE,
595 p_count => x_msg_count,
596 p_data => x_msg_data
597 );
598
599 WHEN OTHERS THEN
600 ROLLBACK TO DELETE_Code_Definition_PVT;
601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
603 THEN
604 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
605 END IF;
606 -- Standard call to get message count and if count=1, get the message
607 FND_MSG_PUB.Count_And_Get (
608 p_encoded => FND_API.G_FALSE,
609 p_count => x_msg_count,
610 p_data => x_msg_data
611 );
612 End Delete_Code_Definition;
613
614
615
616 -- Hint: Primary key needs to be returned.
617 PROCEDURE Lock_Code_Definition(
618 p_api_version_number IN NUMBER,
619 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
620
621 x_return_status OUT NOCOPY VARCHAR2,
622 x_msg_count OUT NOCOPY NUMBER,
623 x_msg_data OUT NOCOPY VARCHAR2,
624
625 p_code_definition_id IN NUMBER,
626 p_object_version IN NUMBER
627 )
628
629 IS
630 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Code_Definition';
631 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
632 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
633 l_CODE_DEFINITION_ID NUMBER;
634
635 CURSOR c_Code_Definition IS
636 SELECT CODE_DEFINITION_ID
637 FROM BIM_R_CODE_DEFINITIONS
638 WHERE CODE_DEFINITION_ID = p_CODE_DEFINITION_ID
639 AND object_version_number = p_object_version
640 FOR UPDATE NOWAIT;
641
642 BEGIN
643
644 -- Debug Message
645 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
646
647 -- Initialize message list if p_init_msg_list is set to TRUE.
648 IF FND_API.to_Boolean( p_init_msg_list )
649 THEN
650 FND_MSG_PUB.initialize;
651 END IF;
652
653 -- Standard call to check for call compatibility.
654 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
655 p_api_version_number,
656 l_api_name,
657 G_PKG_NAME)
658 THEN
659 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
660 END IF;
661
662
663 -- Initialize API return status to SUCCESS
664 x_return_status := FND_API.G_RET_STS_SUCCESS;
665
666
667 ------------------------ lock -------------------------
668
669 AMS_Utility_PVT.debug_message(l_full_name||': start');
670 OPEN c_Code_Definition;
671
672 FETCH c_Code_Definition INTO l_CODE_DEFINITION_ID;
673
674 IF (c_Code_Definition%NOTFOUND) THEN
675 CLOSE c_Code_Definition;
676 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
677 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
678 FND_MSG_PUB.add;
679 END IF;
680 RAISE FND_API.g_exc_error;
681 END IF;
682
686 FND_MSG_PUB.count_and_get(
683 CLOSE c_Code_Definition;
684
685 -------------------- finish --------------------------
687 p_encoded => FND_API.g_false,
688 p_count => x_msg_count,
689 p_data => x_msg_data);
690 AMS_Utility_PVT.debug_message(l_full_name ||': end');
691 EXCEPTION
692
693 WHEN AMS_Utility_PVT.resource_locked THEN
694 x_return_status := FND_API.g_ret_sts_error;
695 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
696
697 WHEN FND_API.G_EXC_ERROR THEN
698 ROLLBACK TO LOCK_Code_Definition_PVT;
699 x_return_status := FND_API.G_RET_STS_ERROR;
700 -- Standard call to get message count and if count=1, get the message
701 FND_MSG_PUB.Count_And_Get (
702 p_encoded => FND_API.G_FALSE,
703 p_count => x_msg_count,
704 p_data => x_msg_data
705 );
706
707 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
708 ROLLBACK TO LOCK_Code_Definition_PVT;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 -- Standard call to get message count and if count=1, get the message
711 FND_MSG_PUB.Count_And_Get (
712 p_encoded => FND_API.G_FALSE,
713 p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716
717 WHEN OTHERS THEN
718 ROLLBACK TO LOCK_Code_Definition_PVT;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
721 THEN
722 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
723 END IF;
724 -- Standard call to get message count and if count=1, get the message
725 FND_MSG_PUB.Count_And_Get (
726 p_encoded => FND_API.G_FALSE,
727 p_count => x_msg_count,
728 p_data => x_msg_data
729 );
730 End Lock_Code_Definition;
731
732
733 PROCEDURE check_code_def_uk_items(
734 p_code_def_rec IN code_def_rec_type,
735 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
736 x_return_status OUT NOCOPY VARCHAR2)
737 IS
738 l_valid_flag VARCHAR2(1);
739
740 BEGIN
741 x_return_status := FND_API.g_ret_sts_success;
742 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
743 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
744 'BIM_R_CODE_DEFINITIONS',
745 'CODE_DEFINITION_ID = ''' || p_code_def_rec.CODE_DEFINITION_ID ||''''
746 );
747 ELSE
748 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
749 'BIM_R_CODE_DEFINITIONS',
750 'CODE_DEFINITION_ID = ''' || p_code_def_rec.CODE_DEFINITION_ID ||
751 ''' AND CODE_DEFINITION_ID <> ' || p_code_def_rec.CODE_DEFINITION_ID
752 );
753 END IF;
754
755 IF l_valid_flag = FND_API.g_false THEN
756 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_CODE_DEFINITION_ID_DUPLICATE');
757 x_return_status := FND_API.g_ret_sts_error;
758 RETURN;
759 END IF;
760
761 END check_code_def_uk_items;
762
763 PROCEDURE check_code_def_req_items(
764 p_code_def_rec IN code_def_rec_type,
765 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
766 x_return_status OUT NOCOPY VARCHAR2
767 )
768 IS
769 BEGIN
770 x_return_status := FND_API.g_ret_sts_success;
771
772 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
773
774
775 IF p_code_def_rec.code_definition_id = FND_API.g_miss_num OR p_code_def_rec.code_definition_id IS NULL THEN
776 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
777 FND_MESSAGE.set_token('MISS_FIELD','CODE_DEFINITION_ID');
778 x_return_status := FND_API.g_ret_sts_error;
779 RETURN;
780 END IF;
781
782
783 IF p_code_def_rec.object_version_number = FND_API.g_miss_num OR p_code_def_rec.object_version_number IS NULL THEN
784 FND_MESSAGE.set_name('AMS', 'AMS_API_MISSING_FIELD');
785 FND_MESSAGE.set_token('MISS_FIELD','OBJECT_VERSION_NUMBER');
786 x_return_status := FND_API.g_ret_sts_error;
787 RETURN;
788 END IF;
789 ELSE
790
791
792 IF p_code_def_rec.code_definition_id IS NULL THEN
793 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_code_def_NO_code_definition_id');
794 x_return_status := FND_API.g_ret_sts_error;
795 RETURN;
796 END IF;
797
798
799 IF p_code_def_rec.object_version_number IS NULL THEN
800 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_code_def_NO_object_version_number');
801 x_return_status := FND_API.g_ret_sts_error;
802 RETURN;
803 END IF;
804 END IF;
805
806 END check_code_def_req_items;
807
808 PROCEDURE check_code_def_FK_items(
809 p_code_def_rec IN code_def_rec_type,
810 x_return_status OUT NOCOPY VARCHAR2
811 )
812 IS
813 BEGIN
814 x_return_status := FND_API.g_ret_sts_success;
815
816 -- Enter custom code here
817
818 END check_code_def_FK_items;
819
820 PROCEDURE check_code_def_Lookup_items(
821 p_code_def_rec IN code_def_rec_type,
822 x_return_status OUT NOCOPY VARCHAR2
823 )
824 IS
825 BEGIN
826 x_return_status := FND_API.g_ret_sts_success;
827
828 -- Enter custom code here
829
830 END check_code_def_Lookup_items;
831
832 PROCEDURE Check_code_def_Items (
833 P_code_def_rec IN code_def_rec_type,
834 p_validation_mode IN VARCHAR2,
835 x_return_status OUT NOCOPY VARCHAR2
836 )
837 IS
838 BEGIN
842 check_code_def_uk_items(
839
840 -- Check Items Uniqueness API calls
841
843 p_code_def_rec => p_code_def_rec,
844 p_validation_mode => p_validation_mode,
845 x_return_status => x_return_status);
846 IF x_return_status <> FND_API.g_ret_sts_success THEN
847 RETURN;
848 END IF;
849
850 -- Check Items Required/NOT NULL API calls
851
852 /* check_code_def_req_items(
853 p_code_def_rec => p_code_def_rec,
854 p_validation_mode => p_validation_mode,
855 x_return_status => x_return_status);
856 IF x_return_status <> FND_API.g_ret_sts_success THEN
857 RETURN;
858 END IF; */
859 -- Check Items Foreign Keys API calls
860
861 check_code_def_FK_items(
862 p_code_def_rec => p_code_def_rec,
863 x_return_status => x_return_status);
864 IF x_return_status <> FND_API.g_ret_sts_success THEN
865 RETURN;
866 END IF;
867 -- Check Items Lookups
868
869 check_code_def_Lookup_items(
870 p_code_def_rec => p_code_def_rec,
871 x_return_status => x_return_status);
872 IF x_return_status <> FND_API.g_ret_sts_success THEN
873 RETURN;
874 END IF;
875
876 END Check_code_def_Items;
877
878
879
880
881 PROCEDURE Validate_code_definition(
882 p_api_version_number IN NUMBER,
883 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
884 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
885 p_code_def_rec IN code_def_rec_type,
886 p_validation_mode IN VARCHAR2,
887 x_return_status OUT NOCOPY VARCHAR2,
888 x_msg_count OUT NOCOPY NUMBER,
889 x_msg_data OUT NOCOPY VARCHAR2
890 )
891 IS
892 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Code_Definition';
893 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
894 l_object_version_number NUMBER;
895 l_code_def_rec AMS_Code_Definition_PVT.code_def_rec_type;
896
897 BEGIN
898 -- Standard Start of API savepoint
899 SAVEPOINT VALIDATE_Code_Definition_;
900
901 -- Standard call to check for call compatibility.
902 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
903 p_api_version_number,
904 l_api_name,
905 G_PKG_NAME)
906 THEN
907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
908 END IF;
909
910 -- Initialize message list if p_init_msg_list is set to TRUE.
911 IF FND_API.to_Boolean( p_init_msg_list )
912 THEN
913 FND_MSG_PUB.initialize;
914 END IF;
915 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
916 Check_code_def_Items(
917 p_code_def_rec => p_code_def_rec,
918 p_validation_mode => p_validation_mode,
919 x_return_status => x_return_status
920 );
921
922 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
923 RAISE FND_API.G_EXC_ERROR;
924 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
926 END IF;
927 END IF;
928
929 Complete_code_def_Rec(
930 p_code_def_rec => p_code_def_rec,
931 x_complete_rec => l_code_def_rec
932 );
933
934 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
935 Validate_code_def_Rec(
936 p_api_version_number => 1.0,
937 p_init_msg_list => FND_API.G_FALSE,
938 x_return_status => x_return_status,
939 x_msg_count => x_msg_count,
940 x_msg_data => x_msg_data,
941 p_code_def_rec => l_code_def_rec);
942
943 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
944 RAISE FND_API.G_EXC_ERROR;
945 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
946 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
947 END IF;
948 END IF;
949
950
951 -- Debug Message
952 -- AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
953
954
955 -- Initialize API return status to SUCCESS
956 x_return_status := FND_API.G_RET_STS_SUCCESS;
957
958
959 -- Debug Message
960 -- AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
961
962 -- Standard call to get message count and if count is 1, get message info.
963 FND_MSG_PUB.Count_And_Get
964 (p_count => x_msg_count,
965 p_data => x_msg_data
966 );
967 EXCEPTION
968
969 WHEN AMS_Utility_PVT.resource_locked THEN
970 x_return_status := FND_API.g_ret_sts_error;
971 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
972
973 WHEN FND_API.G_EXC_ERROR THEN
974 ROLLBACK TO VALIDATE_Code_Definition_;
975 x_return_status := FND_API.G_RET_STS_ERROR;
976 -- Standard call to get message count and if count=1, get the message
977 FND_MSG_PUB.Count_And_Get (
978 p_encoded => FND_API.G_FALSE,
979 p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982
983 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
984 ROLLBACK TO VALIDATE_Code_Definition_;
985 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 p_count => x_msg_count,
986 -- Standard call to get message count and if count=1, get the message
987 FND_MSG_PUB.Count_And_Get (
988 p_encoded => FND_API.G_FALSE,
990 p_data => x_msg_data
991 );
992
993 WHEN OTHERS THEN
994 ROLLBACK TO VALIDATE_Code_Definition_;
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
997 THEN
998 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
999 END IF;
1000 -- Standard call to get message count and if count=1, get the message
1001 FND_MSG_PUB.Count_And_Get (
1002 p_encoded => FND_API.G_FALSE,
1003 p_count => x_msg_count,
1004 p_data => x_msg_data
1005 );
1006 End Validate_Code_Definition;
1007
1008
1009 PROCEDURE Validate_code_def_rec(
1010 p_api_version_number IN NUMBER,
1011 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1012 x_return_status OUT NOCOPY VARCHAR2,
1013 x_msg_count OUT NOCOPY NUMBER,
1014 x_msg_data OUT NOCOPY VARCHAR2,
1015 p_code_def_rec IN code_def_rec_type
1016 )
1017 IS
1018 BEGIN
1019 -- Initialize message list if p_init_msg_list is set to TRUE.
1020 IF FND_API.to_Boolean( p_init_msg_list )
1021 THEN
1022 FND_MSG_PUB.initialize;
1023 END IF;
1024
1025 -- Initialize API return status to SUCCESS
1026 x_return_status := FND_API.G_RET_STS_SUCCESS;
1027
1028 -- Hint: Validate data
1029 -- If data not valid
1030 -- THEN
1031 -- x_return_status := FND_API.G_RET_STS_ERROR;
1032
1033 -- Debug Message
1034 -- AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1035 -- Standard call to get message count and if count is 1, get message info.
1036 FND_MSG_PUB.Count_And_Get
1037 (p_count => x_msg_count,
1038 p_data => x_msg_data
1039 );
1040 END Validate_code_def_Rec;
1041
1042 END AMS_Code_Definition_PVT;