[Home] [Help]
PACKAGE BODY: APPS.IEM_DB_CONNECTIONS_PVT
Source
1 PACKAGE BODY IEM_DB_CONNECTIONS_PVT as
2 /* $Header: iemvdbcb.pls 115.4 2002/12/03 20:11:32 chtang ship $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_DB_CONNECTIONS_PVT ';
5
6 PROCEDURE create_item (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 p_db_link IN VARCHAR2,
10 p_db_username IN VARCHAR2,
11 p_db_password IN VARCHAR2,
12 p_db_server_id IN NUMBER,
13 p_is_admin IN VARCHAR2,
14 p_conn_desc IN VARCHAR2:=null,
15 p_CREATED_BY NUMBER:=null,
16 p_CREATION_DATE DATE:=null,
17 p_LAST_UPDATED_BY NUMBER:=null ,
18 p_LAST_UPDATE_DATE DATE:=null,
19 p_LAST_UPDATE_LOGIN NUMBER:=null,
20 p_ATTRIBUTE1 VARCHAR2:=null,
21 p_ATTRIBUTE2 VARCHAR2:=null,
22 p_ATTRIBUTE3 VARCHAR2:=null,
23 p_ATTRIBUTE4 VARCHAR2:=null,
24 p_ATTRIBUTE5 VARCHAR2:=null,
25 p_ATTRIBUTE6 VARCHAR2:=null,
26 p_ATTRIBUTE7 VARCHAR2:=null,
27 p_ATTRIBUTE8 VARCHAR2:=null,
28 p_ATTRIBUTE9 VARCHAR2:=null,
29 p_ATTRIBUTE10 VARCHAR2:=null,
30 p_ATTRIBUTE11 VARCHAR2:=null,
31 p_ATTRIBUTE12 VARCHAR2:=null,
32 p_ATTRIBUTE13 VARCHAR2:=null,
33 p_ATTRIBUTE14 VARCHAR2:=null,
34 p_ATTRIBUTE15 VARCHAR2:=null,
35 x_return_status OUT NOCOPY VARCHAR2,
36 x_msg_count OUT NOCOPY NUMBER,
37 x_msg_data OUT NOCOPY VARCHAR2
38 ) is
39 l_api_name VARCHAR2(255):='create_item';
40 l_api_version_number NUMBER:=1.0;
41 l_seq_id number;
42 l_grp_cnt number;
43 l_CREATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID'));
44 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
45 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
46
47 BEGIN
48 -- Standard Start of API savepoint
49 SAVEPOINT create_item_PVT;
50 -- Standard call to check for call compatibility.
51 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
52 p_api_version_number,
53 l_api_name,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58 -- Initialize message list if p_init_msg_list is set to TRUE.
59 IF FND_API.to_Boolean( p_init_msg_list )
60 THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63 -- Initialize API return status to SUCCESS
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 SELECT IEM_DB_CONNECTIONS_s1.nextval
67 INTO l_seq_id
68 FROM dual;
69
70 /*Check For Existing Server Group Id */
71 if p_db_server_id <> FND_API.G_MISS_NUM THEN
72 Select count(*) into l_grp_cnt from iem_db_servers
73 where db_server_id=p_db_server_id
74 and rownum=1;
75 IF l_grp_cnt = 0 then
76 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_DB_SRV_GRP');
77 FND_MSG_PUB.ADD;
78 x_return_status := FND_API.G_RET_STS_ERROR ;
79 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
80 p_data =>x_msg_data);
81 APP_EXCEPTION.RAISE_EXCEPTION;
82 END IF;
83 end if;
84
85 INSERT INTO IEM_DB_CONNECTIONS
86 (
87 DB_CONNECTION_ID,
88 DB_LINK,
89 DB_USERNAME,
90 DB_PASSWORD,
91 DB_SERVER_ID,
92 IS_ADMIN,
93 CONNECTION_DESC,
94 CREATED_BY,
95 CREATION_DATE,
96 LAST_UPDATED_BY,
97 LAST_UPDATE_DATE,
98 LAST_UPDATE_LOGIN,
99 ATTRIBUTE1,
100 ATTRIBUTE2,
101 ATTRIBUTE3,
102 ATTRIBUTE4,
103 ATTRIBUTE5,
104 ATTRIBUTE6,
105 ATTRIBUTE7,
106 ATTRIBUTE8,
107 ATTRIBUTE9,
108 ATTRIBUTE10,
109 ATTRIBUTE11,
110 ATTRIBUTE12,
111 ATTRIBUTE13,
112 ATTRIBUTE14,
113 ATTRIBUTE15
114 )
115 VALUES
116 (
117 l_seq_id,
118 p_db_link,
119 p_db_username,
120 p_db_password,
121 p_db_server_id,
122 p_is_admin,
123 decode(p_conn_desc,FND_API.G_MISS_CHAR,NULL,p_conn_desc),
124 decode(l_CREATED_BY,null,-1,l_CREATED_BY),
125 sysdate,
126 decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
127 sysdate,
128 decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
129 decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
130 decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
131 decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
132 decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
133 decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
134 decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
135 decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
136 decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
137 decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
138 decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
139 decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
140 decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
141 decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
142 decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
143 decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
144 );
145
146 -- Standard Check Of p_commit.
147 IF FND_API.To_Boolean(p_commit) THEN
148 COMMIT WORK;
149 END IF;
150 -- Standard callto get message count and if count is 1, get message info.
151 FND_MSG_PUB.Count_And_Get
152 ( p_count => x_msg_count,
153 p_data => x_msg_data
154 );
155 EXCEPTION
156 WHEN FND_API.G_EXC_ERROR THEN
157 ROLLBACK TO create_item_PVT;
158 x_return_status := FND_API.G_RET_STS_ERROR ;
159 FND_MSG_PUB.Count_And_Get
160 ( p_count => x_msg_count,
161 p_data => x_msg_data
162 );
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 ROLLBACK TO create_item_PVT;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
166 FND_MSG_PUB.Count_And_Get
167 ( p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170 WHEN OTHERS THEN
171 ROLLBACK TO create_item_PVT;
172 x_return_status := FND_API.G_RET_STS_ERROR;
173 IF FND_MSG_PUB.Check_Msg_Level
174 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_Exc_Msg
177 ( G_PKG_NAME ,
178 l_api_name
179 );
180 END IF;
181 FND_MSG_PUB.Count_And_Get
182 ( p_count => x_msg_count ,
183 p_data => x_msg_data
184 );
185
186 END create_item;
187
188 PROCEDURE delete_item (p_api_version_number IN NUMBER,
189 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
190 p_commit IN VARCHAR2 := FND_API.G_FALSE,
191 p_db_conn_id in number:=null,
192 p_db_username IN VARCHAR2 :=null,
193 p_db_server_id IN NUMBER:=null,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2
197 ) is
198 l_api_name VARCHAR2(255):='delete_item';
199 l_api_version_number NUMBER:=1.0;
200
201 BEGIN
202 -- Standard Start of API savepoint
203 SAVEPOINT delete_item_PVT;
204 -- Standard call to check for call compatibility.
205 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
206 p_api_version_number,
207 l_api_name,
208 G_PKG_NAME)
209 THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212 -- Initialize message list if p_init_msg_list is set to TRUE.
213 IF FND_API.to_Boolean( p_init_msg_list )
214 THEN
215 FND_MSG_PUB.initialize;
216 END IF;
217 -- Initialize API return status to SUCCESS
218 x_return_status := FND_API.G_RET_STS_SUCCESS;
219 if p_db_conn_id = FND_API.G_MISS_NUM then
220 delete from IEM_DB_CONNECTIONS
221 where db_username=p_db_username and db_server_id=p_db_server_id ;
222 else
223 delete from IEM_DB_CONNECTIONS
224 where db_connection_id=p_db_conn_id;
225 end if;
226
227 -- Standard Check Of p_commit.
228 IF FND_API.To_Boolean(p_commit) THEN
229 COMMIT WORK;
230 END IF;
231 -- Standard callto 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 EXCEPTION
237 WHEN FND_API.G_EXC_ERROR THEN
238 ROLLBACK TO delete_item_PVT;
239 x_return_status := FND_API.G_RET_STS_ERROR ;
240 FND_MSG_PUB.Count_And_Get
241 ( p_count => x_msg_count,
242 p_data => x_msg_data
243 );
244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
245 ROLLBACK TO delete_item_PVT;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
247 FND_MSG_PUB.Count_And_Get
248 ( p_count => x_msg_count,
249 p_data => x_msg_data
250 );
251 WHEN OTHERS THEN
252 ROLLBACK TO delete_item_PVT;
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 IF FND_MSG_PUB.Check_Msg_Level
255 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
256 THEN
257 FND_MSG_PUB.Add_Exc_Msg
258 ( G_PKG_NAME ,
259 l_api_name
260 );
261 END IF;
262 FND_MSG_PUB.Count_And_Get
263 ( p_count => x_msg_count ,
264 p_data => x_msg_data
265 );
266
267 END delete_item;
268
269 PROCEDURE update_item (p_api_version_number IN NUMBER,
270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
271 p_commit IN VARCHAR2 := FND_API.G_FALSE,
272 p_db_conn_id IN NUMBER:=null,
273 p_db_link IN VARCHAR2:=null,
274 p_db_username IN VARCHAR2:=null,
275 p_db_password IN VARCHAR2:=null,
276 p_db_server_id IN NUMBER:=null,
277 p_is_admin IN VARCHAR2:=null,
278 p_conn_desc IN VARCHAR2:=null,
279 p_CREATED_BY NUMBER:=null,
280 p_CREATION_DATE DATE:=null,
281 p_LAST_UPDATED_BY NUMBER:=null ,
282 p_LAST_UPDATE_DATE DATE:=null,
283 p_LAST_UPDATE_LOGIN NUMBER:=null,
284 p_ATTRIBUTE1 VARCHAR2:=null,
285 p_ATTRIBUTE2 VARCHAR2:=null,
286 p_ATTRIBUTE3 VARCHAR2:=null,
287 p_ATTRIBUTE4 VARCHAR2:=null,
288 p_ATTRIBUTE5 VARCHAR2:=null,
289 p_ATTRIBUTE6 VARCHAR2:=null,
290 p_ATTRIBUTE7 VARCHAR2:=null,
291 p_ATTRIBUTE8 VARCHAR2:=null,
292 p_ATTRIBUTE9 VARCHAR2:=null,
293 p_ATTRIBUTE10 VARCHAR2:=null,
294 p_ATTRIBUTE11 VARCHAR2:=null,
295 p_ATTRIBUTE12 VARCHAR2:=null,
296 p_ATTRIBUTE13 VARCHAR2:=null,
297 p_ATTRIBUTE14 VARCHAR2:=null,
298 p_ATTRIBUTE15 VARCHAR2:=null,
299 x_return_status OUT NOCOPY VARCHAR2,
300 x_msg_count OUT NOCOPY NUMBER,
301 x_msg_data OUT NOCOPY VARCHAR2
302 ) is
303 l_api_name VARCHAR2(255):='update_item';
304 l_api_version_number NUMBER:=1.0;
305 l_grp_cnt NUMBER;
306 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
307 l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
308
309 BEGIN
310 -- Standard Start of API savepoint
311 SAVEPOINT update_item_PVT;
312 -- Standard call to check for call compatibility.
313 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
314 p_api_version_number,
315 l_api_name,
316 G_PKG_NAME)
317 THEN
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319 END IF;
320 -- Initialize message list if p_init_msg_list is set to TRUE.
321 IF FND_API.to_Boolean( p_init_msg_list )
322 THEN
323 FND_MSG_PUB.initialize;
324 END IF;
325 -- Initialize API return status to SUCCESS
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 if p_db_server_id is not null then
329
330 /*Check For Existing DB Server Id */
331
332 Select count(*) into l_grp_cnt from iem_db_servers
333 where db_server_id=p_db_Server_id
334 and rownum=1;
335 IF l_grp_cnt = 0 then
336 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_DB_SRV_GRP');
337 FND_MSG_PUB.ADD;
338 x_return_status := FND_API.G_RET_STS_ERROR ;
339 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
340 p_data =>x_msg_data);
341 APP_EXCEPTION.RAISE_EXCEPTION;
342 END IF;
343 end if;
344 if p_db_conn_id is null then
345 update IEM_DB_CONNECTIONS
346 set db_link=decode(p_db_link,FND_API.G_MISS_CHAR, NULL, NULL, db_link,p_db_link),
347 is_admin=decode(p_is_admin,FND_API.G_MISS_CHAR, NULL, NULL,is_admin,is_admin),
348 connection_desc=decode(p_conn_desc,FND_API.G_MISS_CHAR, NULL, NULL,connection_desc,p_conn_desc),
349 LAST_UPDATE_DATE = sysdate,
350 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
351 LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,l_LAST_UPDATE_LOGIN),
352 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
353 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
354 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
355 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
356 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
357 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
358 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
359 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
360 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
361 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
362 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
363 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
364 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
365 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
366 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
367 where db_username=p_db_username and db_server_id=p_db_server_id;
368
369 else
370 update IEM_DB_CONNECTIONS
371 set db_username=decode(p_db_username,FND_API.G_MISS_CHAR, NULL, NULL,db_username,p_db_username),
372 db_password=decode(p_db_password,FND_API.G_MISS_CHAR, NULL, NULL,db_password,p_db_password),
373 db_link=decode(p_db_link,FND_API.G_MISS_CHAR, NULL, NULL,db_link,p_db_link),
374 is_admin=decode(p_is_admin,FND_API.G_MISS_CHAR, NULL, NULL,is_admin,p_is_admin),
375 connection_desc=decode(p_conn_desc,FND_API.G_MISS_CHAR, NULL, NULL,connection_desc,p_conn_desc),
376 db_server_id=decode(p_db_server_id,FND_API.G_MISS_CHAR, NULL, NULL,db_server_id,p_db_server_id),
377 LAST_UPDATE_DATE = sysdate,
378 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
379 LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,l_LAST_UPDATE_LOGIN),
380 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
381 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
382 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
383 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
384 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
385 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
386 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
387 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
388 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
389 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
390 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
391 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
392 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
393 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
394 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
395 where db_connection_id=p_db_conn_id;
396 end if;
397
398 -- Standard Check Of p_commit.
399 IF FND_API.To_Boolean(p_commit) THEN
400 COMMIT WORK;
401 END IF;
402 -- Standard callto get message count and if count is 1, get message info.
403 FND_MSG_PUB.Count_And_Get
404 ( p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407 EXCEPTION
408 WHEN FND_API.G_EXC_ERROR THEN
409 ROLLBACK TO update_item_PVT;
410 x_return_status := FND_API.G_RET_STS_ERROR ;
411 FND_MSG_PUB.Count_And_Get
412 ( p_count => x_msg_count,
413 p_data => x_msg_data
414 );
415 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
416 ROLLBACK TO update_item_PVT;
417 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
418 FND_MSG_PUB.Count_And_Get
419 ( p_count => x_msg_count,
420 p_data => x_msg_data
421 );
422 WHEN OTHERS THEN
423 ROLLBACK TO update_item_PVT;
424 x_return_status := FND_API.G_RET_STS_ERROR;
425 IF FND_MSG_PUB.Check_Msg_Level
426 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
427 THEN
428 FND_MSG_PUB.Add_Exc_Msg
429 ( G_PKG_NAME ,
430 l_api_name
431 );
432 END IF;
433 FND_MSG_PUB.Count_And_Get
434 ( p_count => x_msg_count ,
435 p_data => x_msg_data
436 );
437
438 END update_item;
439
440 PROCEDURE select_item (p_api_version_number IN NUMBER,
441 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
442 p_commit IN VARCHAR2 := FND_API.G_FALSE,
443 p_db_server_id in number:=null,
444 p_is_admin IN VARCHAR2 :=null,
445 x_db_link OUT NOCOPY VARCHAR2,
446 x_return_status OUT NOCOPY VARCHAR2,
447 x_msg_count OUT NOCOPY NUMBER,
448 x_msg_data OUT NOCOPY VARCHAR2
449 ) is
450 l_api_name VARCHAR2(255):='select_item';
451 l_api_version_number NUMBER:=1.0;
452
453 BEGIN
454 -- Standard Start of API savepoint
455 SAVEPOINT select_item_PVT;
456 -- Standard call to check for call compatibility.
457 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
458 p_api_version_number,
459 l_api_name,
460 G_PKG_NAME)
461 THEN
462 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
463 END IF;
464 -- Initialize message list if p_init_msg_list is set to TRUE.
465 IF FND_API.to_Boolean( p_init_msg_list )
466 THEN
467 FND_MSG_PUB.initialize;
468 END IF;
469 -- Initialize API return status to SUCCESS
470 x_return_status := FND_API.G_RET_STS_SUCCESS;
471 BEGIN
472 SELECT DB_LINK into x_db_link
473 FROM IEM_DB_CONNECTIONS
474 where db_server_id=p_db_server_id
475 and is_admin=p_is_admin;
476 EXCEPTION WHEN NO_DATA_FOUND THEN
477 FND_MESSAGE.SET_NAME('IEM','IEM_NON_EXISTENT_DBLINK_NAME');
478 FND_MSG_PUB.Add;
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
481 p_data =>x_msg_data);
482 WHEN TOO_MANY_ROWS THEN
483 FND_MESSAGE.SET_NAME('IEM','IEM_MORE_THAN_ONE_DBLINK');
484 FND_MSG_PUB.Add;
485 x_return_status := FND_API.G_RET_STS_ERROR ;
486 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
487 p_data =>x_msg_data);
488 END;
489 -- Standard Check Of p_commit.
490 IF FND_API.To_Boolean(p_commit) THEN
491 COMMIT WORK;
492 END IF;
493 -- Standard callto get message count and if count is 1, get message info.
494 FND_MSG_PUB.Count_And_Get
495 ( p_count => x_msg_count,
496 p_data => x_msg_data
497 );
498 EXCEPTION
499 WHEN FND_API.G_EXC_ERROR THEN
500 ROLLBACK TO select_item_PVT;
501 x_return_status := FND_API.G_RET_STS_ERROR ;
502 FND_MSG_PUB.Count_And_Get
503 ( p_count => x_msg_count,
504 p_data => x_msg_data
505 );
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 ROLLBACK TO select_item_PVT;
508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
509 FND_MSG_PUB.Count_And_Get
510 ( p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513 WHEN OTHERS THEN
514 ROLLBACK TO select_item_PVT;
515 x_return_status := FND_API.G_RET_STS_ERROR;
516 IF FND_MSG_PUB.Check_Msg_Level
517 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
518 THEN
519 FND_MSG_PUB.Add_Exc_Msg
520 ( G_PKG_NAME ,
521 l_api_name
522 );
523 END IF;
524 FND_MSG_PUB.Count_And_Get
525 ( p_count => x_msg_count ,
526 p_data => x_msg_data
527 );
528 END select_item;
529
530 END IEM_DB_CONNECTIONS_PVT ;