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