[Home] [Help]
PACKAGE BODY: APPS.IEM_EMAIL_SERVER_TYPES_PVT
Source
1 PACKAGE BODY IEM_EMAIL_SERVER_TYPES_PVT as
2 /* $Header: iemvsvtb.pls 115.5 2002/12/04 01:02:50 sboorela shipped $ */
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMAIL_SERVER_TYPES_PVT ';
5
6 PROCEDURE create_item (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 ,
8 p_commit IN VARCHAR2 ,
9 p_server_type IN VARCHAR2,
10 p_is_secure IN VARCHAR2,
11 p_type_desc IN VARCHAR2,
12 p_CREATED_BY NUMBER,
13 p_CREATION_DATE DATE,
14 p_LAST_UPDATED_BY NUMBER,
15 p_LAST_UPDATE_DATE DATE,
16 p_LAST_UPDATE_LOGIN NUMBER,
17 p_ATTRIBUTE1 VARCHAR2,
18 p_ATTRIBUTE2 VARCHAR2,
19 p_ATTRIBUTE3 VARCHAR2,
20 p_ATTRIBUTE4 VARCHAR2,
21 p_ATTRIBUTE5 VARCHAR2,
22 p_ATTRIBUTE6 VARCHAR2,
23 p_ATTRIBUTE7 VARCHAR2,
24 p_ATTRIBUTE8 VARCHAR2,
25 p_ATTRIBUTE9 VARCHAR2,
26 p_ATTRIBUTE10 VARCHAR2,
27 p_ATTRIBUTE11 VARCHAR2,
28 p_ATTRIBUTE12 VARCHAR2,
29 p_ATTRIBUTE13 VARCHAR2,
30 p_ATTRIBUTE14 VARCHAR2,
31 p_ATTRIBUTE15 VARCHAR2,
32 x_return_status OUT NOCOPY VARCHAR2,
33 x_msg_count OUT NOCOPY NUMBER,
34 x_msg_data OUT NOCOPY VARCHAR2
35 ) is
36 l_api_name VARCHAR2(255):='create_item';
37 l_api_version_number NUMBER:=1.0;
38 l_seq_id number;
39
40 BEGIN
41 -- Standard Start of API savepoint
42 SAVEPOINT create_item_PVT;
43 -- Standard call to check for call compatibility.
44 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
45 p_api_version_number,
46 l_api_name,
47 G_PKG_NAME)
48 THEN
49 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
50 END IF;
51 -- Initialize message list if p_init_msg_list is set to TRUE.
52 IF FND_API.to_Boolean( p_init_msg_list )
53 THEN
54 FND_MSG_PUB.initialize;
55 END IF;
56 -- Initialize API return status to SUCCESS
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58 SELECT iem_email_server_types_s1.nextval
59 INTO l_seq_id
60 FROM dual;
61 INSERT INTO iem_email_server_types
62 (
63 EMAIL_SERVER_TYPE_ID,
64 EMAIL_SERVER_TYPE ,
65 IS_SECURE ,
66 TYPE_DESCRIPTION ,
67 CREATED_BY,
68 CREATION_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_DATE,
71 LAST_UPDATE_LOGIN,
72 ATTRIBUTE1,
73 ATTRIBUTE2,
74 ATTRIBUTE3,
75 ATTRIBUTE4,
76 ATTRIBUTE5,
77 ATTRIBUTE6,
78 ATTRIBUTE7,
79 ATTRIBUTE8,
80 ATTRIBUTE9,
81 ATTRIBUTE10,
82 ATTRIBUTE11,
83 ATTRIBUTE12,
84 ATTRIBUTE13,
85 ATTRIBUTE14,
86 ATTRIBUTE15 )
87 VALUES
88 (
89 l_seq_id,
90 p_server_type,
91 p_is_secure,
92 decode(p_type_desc,FND_API.G_MISS_CHAR,NULL,p_type_desc),
93 decode(p_CREATED_BY,null,-1,p_CREATED_BY),
94 sysdate,
95 decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
96 sysdate,
97 decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
98 p_ATTRIBUTE1,
99 p_ATTRIBUTE2,
100 p_ATTRIBUTE3,
101 p_ATTRIBUTE4,
102 p_ATTRIBUTE5,
103 p_ATTRIBUTE6,
104 p_ATTRIBUTE7,
105 p_ATTRIBUTE8,
106 p_ATTRIBUTE9,
107 p_ATTRIBUTE10,
108 p_ATTRIBUTE11,
109 p_ATTRIBUTE12,
110 p_ATTRIBUTE13,
111 p_ATTRIBUTE14,
112 p_ATTRIBUTE15);
113 -- Standard Check Of p_commit.
114 IF FND_API.To_Boolean(p_commit) THEN
115 COMMIT WORK;
116 END IF;
117 -- Standard callto get message count and if count is 1, get message info.
118 FND_MSG_PUB.Count_And_Get
119 ( p_count => x_msg_count,
120 p_data => x_msg_data
121 );
122 EXCEPTION
123 WHEN FND_API.G_EXC_ERROR THEN
124 ROLLBACK TO create_item_PVT;
125 x_return_status := FND_API.G_RET_STS_ERROR ;
126 FND_MSG_PUB.Count_And_Get
127 ( p_count => x_msg_count,
128 p_data => x_msg_data
129 );
130 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
131 ROLLBACK TO create_item_PVT;
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
133 FND_MSG_PUB.Count_And_Get
134 ( p_count => x_msg_count,
135 p_data => x_msg_data
136 );
137 WHEN OTHERS THEN
138 ROLLBACK TO create_item_PVT;
139 x_return_status := FND_API.G_RET_STS_ERROR;
140 IF FND_MSG_PUB.Check_Msg_Level
141 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
142 THEN
143 FND_MSG_PUB.Add_Exc_Msg
144 ( G_PKG_NAME ,
145 l_api_name
146 );
147 END IF;
148 FND_MSG_PUB.Count_And_Get
149 ( p_count => x_msg_count ,
150 p_data => x_msg_data
151 );
152
153 END create_item;
154
155 PROCEDURE delete_item (p_api_version_number IN NUMBER,
156 p_init_msg_list IN VARCHAR2 ,
157 p_commit IN VARCHAR2 ,
158 p_server_type_id IN NUMBER,
159 p_server_type IN VARCHAR2,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2
163 ) is
164 l_api_name VARCHAR2(255):='delete_item';
165 l_api_version_number NUMBER:=1.0;
166 l_seq_id number;
167
168 BEGIN
169 -- Standard Start of API savepoint
170 SAVEPOINT delete_item_PVT;
171 -- Standard call to check for call compatibility.
172 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
173 p_api_version_number,
174 l_api_name,
175 G_PKG_NAME)
176 THEN
177 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
178 END IF;
179 -- Initialize message list if p_init_msg_list is set to TRUE.
180 IF FND_API.to_Boolean( p_init_msg_list )
181 THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184 -- Initialize API return status to SUCCESS
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186 if p_server_type_id is null then
187 DELETE FROM IEM_EMAIL_SERVERS
188 WHERE SERVER_TYPE_ID IN
189 (SELECT EMAIL_SERVER_TYPE_ID FROM
190 IEM_EMAIL_SERVER_TYPES
191 WHERE EMAIL_SERVER_TYPE=p_server_type);
192 DELETE FROM IEM_EMAIL_SERVER_TYPES
193 WHERE EMAIL_SERVER_TYPE=p_server_type;
194 else
195 DELETE FROM IEM_EMAIL_SERVERS
196 WHERE SERVER_TYPE_ID=p_server_type_id;
197 DELETE FROM IEM_EMAIL_SERVER_TYPES
198 WHERE EMAIL_SERVER_TYPE_ID=p_server_type_id;
199 end if;
200
201 -- Standard Check Of p_commit.
202 IF FND_API.To_Boolean(p_commit) THEN
203 COMMIT WORK;
204 END IF;
205 -- Standard callto get message count and if count is 1, get message info.
206 FND_MSG_PUB.Count_And_Get
207 ( p_count => x_msg_count,
208 p_data => x_msg_data
209 );
210 EXCEPTION
211 WHEN FND_API.G_EXC_ERROR THEN
212 ROLLBACK TO delete_item_PVT;
213 x_return_status := FND_API.G_RET_STS_ERROR ;
214 FND_MSG_PUB.Count_And_Get
215 ( p_count => x_msg_count,
216 p_data => x_msg_data
217 );
218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
219 ROLLBACK TO delete_item_PVT;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
221 FND_MSG_PUB.Count_And_Get
222 ( p_count => x_msg_count,
223 p_data => x_msg_data
224 );
225 WHEN OTHERS THEN
226 ROLLBACK TO delete_item_PVT;
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 IF FND_MSG_PUB.Check_Msg_Level
229 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
230 THEN
231 FND_MSG_PUB.Add_Exc_Msg
232 ( G_PKG_NAME ,
233 l_api_name
234 );
235 END IF;
236 FND_MSG_PUB.Count_And_Get
237 ( p_count => x_msg_count ,
238 p_data => x_msg_data
239 );
240
241 END delete_item;
242
243 PROCEDURE update_item (p_api_version_number IN NUMBER,
244 p_init_msg_list IN VARCHAR2 ,
245 p_commit IN VARCHAR2 ,
246 p_server_type_id IN NUMBER,
247 p_server_type IN VARCHAR2,
248 p_is_secure IN VARCHAR2,
249 p_type_desc IN VARCHAR2,
250 p_LAST_UPDATED_BY NUMBER,
251 p_LAST_UPDATE_DATE DATE,
252 p_LAST_UPDATE_LOGIN NUMBER,
253 p_ATTRIBUTE1 VARCHAR2,
254 p_ATTRIBUTE2 VARCHAR2,
255 p_ATTRIBUTE3 VARCHAR2,
256 p_ATTRIBUTE4 VARCHAR2,
257 p_ATTRIBUTE5 VARCHAR2,
258 p_ATTRIBUTE6 VARCHAR2,
259 p_ATTRIBUTE7 VARCHAR2,
260 p_ATTRIBUTE8 VARCHAR2,
261 p_ATTRIBUTE9 VARCHAR2,
262 p_ATTRIBUTE10 VARCHAR2,
263 p_ATTRIBUTE11 VARCHAR2,
264 p_ATTRIBUTE12 VARCHAR2,
265 p_ATTRIBUTE13 VARCHAR2,
266 p_ATTRIBUTE14 VARCHAR2,
267 p_ATTRIBUTE15 VARCHAR2,
268 x_return_status OUT NOCOPY VARCHAR2,
269 x_msg_count OUT NOCOPY NUMBER,
270 x_msg_data OUT NOCOPY VARCHAR2
271 ) is
272 l_api_name VARCHAR2(255):='update_item';
273 l_api_version_number NUMBER:=1.0;
274 l_seq_id NUMBER;
275
276 BEGIN
277 -- Standard Start of API savepoint
278 SAVEPOINT update_item_PVT;
279 -- Standard call to check for call compatibility.
280 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
281 p_api_version_number,
282 l_api_name,
283 G_PKG_NAME)
284 THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287 -- Initialize message list if p_init_msg_list is set to TRUE.
288 IF FND_API.to_Boolean( p_init_msg_list )
289 THEN
290 FND_MSG_PUB.initialize;
291 END IF;
292 -- Initialize API return status to SUCCESS
293 x_return_status := FND_API.G_RET_STS_SUCCESS;
294 if p_server_type_id is null then
295 update iem_email_server_types
296 set type_description= decode(p_type_desc,FND_API.G_MISS_CHAR,null,null,type_Description,p_type_desc),
297 is_secure=decode(p_is_secure,FND_API.G_MISS_CHAR,null,null,is_secure,p_is_secure),
298 LAST_UPDATE_DATE = sysdate,
299 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, null,last_updated_by,p_LAST_UPDATED_BY),
300 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN,null,last_update_login,p_LAST_UPDATE_LOGIN),
301 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE1, p_ATTRIBUTE1),
302 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE2, p_ATTRIBUTE2),
303 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE3, p_ATTRIBUTE3),
304 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE4, p_ATTRIBUTE4),
305 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, null,null,ATTRIBUTE5, p_ATTRIBUTE5),
306 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE6, p_ATTRIBUTE6),
307 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE7, p_ATTRIBUTE7),
308 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE8, p_ATTRIBUTE8),
309 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE9, p_ATTRIBUTE9),
310 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE10, p_ATTRIBUTE10),
311 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE11, p_ATTRIBUTE11),
312 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE12, p_ATTRIBUTE12),
313 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE13, p_ATTRIBUTE13),
314 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE14, p_ATTRIBUTE14),
315 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE15, p_ATTRIBUTE15)
316 WHERE EMAIL_SERVER_TYPE=p_server_type;
317 ELSE
318 update iem_email_server_types
319 set
320 email_server_type= decode(p_server_type,FND_API.G_MISS_CHAR,null,null,email_server_type,p_server_type),
321 type_description= decode(p_type_desc,FND_API.G_MISS_CHAR,null,null,type_description,p_type_desc),
322 is_secure=decode(p_is_secure,FND_API.G_MISS_CHAR,null,null,is_secure,p_is_secure),
323 LAST_UPDATE_DATE = sysdate,
324 LAST_UPDATED_BY = decode(p_LAST_UPDATED_BY, null,last_updated_by,p_LAST_UPDATED_BY),
325 LAST_UPDATE_LOGIN = decode( p_LAST_UPDATE_LOGIN,null,last_update_login,p_LAST_UPDATE_LOGIN),
326 ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE1, p_ATTRIBUTE1),
327 ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE2, p_ATTRIBUTE2),
328 ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE3, p_ATTRIBUTE3),
329 ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE4, p_ATTRIBUTE4),
330 ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, null,null,ATTRIBUTE5, p_ATTRIBUTE5),
331 ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE6, p_ATTRIBUTE6),
332 ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE7, p_ATTRIBUTE7),
333 ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE8, p_ATTRIBUTE8),
334 ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE9, p_ATTRIBUTE9),
335 ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE10, p_ATTRIBUTE10),
336 ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE11, p_ATTRIBUTE11),
337 ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE12, p_ATTRIBUTE12),
338 ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE13, p_ATTRIBUTE13),
339 ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE14, p_ATTRIBUTE14),
340 ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR,null,null, ATTRIBUTE15, p_ATTRIBUTE15)
341 WHERE EMAIL_SERVER_TYPE_ID=p_server_type_id;
342
343 end if;
344
345 -- Standard Check Of p_commit.
346 IF FND_API.To_Boolean(p_commit) THEN
347 COMMIT WORK;
348 END IF;
349 -- Standard callto get message count and if count is 1, get message info.
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354 EXCEPTION
355 WHEN FND_API.G_EXC_ERROR THEN
356 ROLLBACK TO update_item_PVT;
357 x_return_status := FND_API.G_RET_STS_ERROR ;
358 FND_MSG_PUB.Count_And_Get
359 ( p_count => x_msg_count,
360 p_data => x_msg_data
361 );
362 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
363 ROLLBACK TO update_item_PVT;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
365 FND_MSG_PUB.Count_And_Get
366 ( p_count => x_msg_count,
367 p_data => x_msg_data
368 );
369 WHEN OTHERS THEN
370 ROLLBACK TO update_item_PVT;
371 x_return_status := FND_API.G_RET_STS_ERROR;
372 IF FND_MSG_PUB.Check_Msg_Level
373 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
374 THEN
375 FND_MSG_PUB.Add_Exc_Msg
376 ( G_PKG_NAME ,
377 l_api_name
378 );
382 p_data => x_msg_data
379 END IF;
380 FND_MSG_PUB.Count_And_Get
381 ( p_count => x_msg_count ,
383 );
384
385 END update_item;
386 END IEM_EMAIL_SERVER_TYPES_PVT ;