[Home] [Help]
PACKAGE BODY: APPS.IEM_MOVEMSG_PVT
Source
1 PACKAGE BODY IEM_MOVEMSG_PVT as
2 /* $Header: iemvmsgb.pls 115.3 2002/12/04 00:01:55 chtang noship $*/
3 -- *****************************************************
4 -- Start of Comments
5 -- Package name : IEM_MOVEMSG_PVT
6 -- Purpose : APIs that are used to move messages to agent folders.
7 -- : sboorela 08/13/2001 Created moveMessage() API
8 -- : chtang 07/08/2002 Created moveOesMessage() API
9 -- : chtang 12/03/2002 Fixed gscc error
10 -- NOTE :
11 -- End of Comments
12 -- *****************************************************
13 G_PKG_NAME CONSTANT varchar2(30) :='IEM_MOVEMSG_PVT ';
14
15 -- Start of Comments
16 -- API name : moveMessage
17 -- Type : Private
18 -- Function : This procedure move message to agent folder
19 -- Pre-reqs : None.
20 -- Parameters :
21 -- IN
22 -- p_api_version_number IN NUMBER Required
23 -- p_init_msg_list IN VARCHAR2
24 -- p_commit IN VARCHAR2
25 -- p_msgid IN NUMBER,
26 -- p_email_account_id IN NUMBER,
27 -- p_tofolder IN VARCHAR2,
28 -- p_reverse IN VARCHAR2,
29 -- OUT
30 -- x_status OUT NUMBER
31 -- x_return_status OUT VARCHAR2
32 -- x_msg_count OUT NUMBER
33 -- x_msg_data OUT VARCHAR2
34 --
35 -- Version : 1.0
36 -- Notes :
37 --
38 -- End of comments
39 -- **********************************************************
40
41 PROCEDURE moveMessage (p_api_version_number IN NUMBER,
42 p_init_msg_list IN VARCHAR2,
43 p_commit IN VARCHAR2,
44 p_msgid IN NUMBER,
45 p_email_account_id IN NUMBER,
46 p_tofolder IN VARCHAR2,
47 p_reverse IN VARCHAR2,
48 x_status OUT NOCOPY NUMBER,
49 x_return_status OUT NOCOPY VARCHAR2,
50 x_msg_count OUT NOCOPY NUMBER,
51 x_msg_data OUT NOCOPY VARCHAR2
52 ) is
53 l_api_name VARCHAR2(255):='moveMessage';
54 l_api_version_number NUMBER:=1.0;
55 l_pass VARCHAR2(30);
56 l_user VARCHAR2(30);
57 l_domain VARCHAR2(30);
58 l_db_server_id NUMBER;
59 l_str VARCHAR2(200);
60 l_ret NUMBER;
61 l_data varchar2(255);
62 l_stat varchar2(10);
63 l_count number;
64 l_im_link varchar2(200);
65 l_im_link1 varchar2(200);
66 l_folder varchar2(50);
67 l_frfolder varchar2(50);
68 l_tofolder varchar2(50);
69
70 BEGIN
71 -- Standard Start of API savepoint
72 SAVEPOINT moveMessage_PVT;
73 -- Standard call to check for call compatibility.
74 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
75 p_api_version_number,
76 l_api_name,
77 G_PKG_NAME)
78 THEN
79 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
80 END IF;
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean( p_init_msg_list )
83 THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86 -- Initialize API return status to SUCCESS
87 x_return_status := FND_API.G_RET_STS_SUCCESS;
88
89 SELECT DB_SERVER_ID,EMAIL_USER,DOMAIN,EMAIL_PASSWORD
90 INTO l_db_server_id,l_user,l_domain,l_pass
91 FROM IEM_EMAIL_ACCOUNTS
92 WHERE EMAIL_ACCOUNT_ID=p_email_account_id;
93
94 IEM_DB_CONNECTIONS_PVT.select_item(
95 p_api_version_number =>1.0,
96 p_db_server_id =>l_db_server_id,
97 p_is_admin =>'P',
98 x_db_link=>l_im_link1,
99 x_return_status =>l_stat,
100 x_msg_count => l_count,
101 x_msg_data => l_data);
102
103 If l_im_link1 is null then
104 l_im_link:=null;
105 else
106 l_im_link:='@'||l_im_link1;
107 end if;
108 l_str:='begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user,:a_domain,:a_password);end; ';
109 EXECUTE IMMEDIATE l_str using OUT l_ret,l_user,l_domain,l_pass;
110 IF l_ret=0 THEN
111 x_return_status := FND_API.G_RET_STS_SUCCESS;
112 ELSE
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 END IF;
115
116 -- Now we are ready to call im movetoFolder
117 IF (p_reverse='N') THEN
118 l_frfolder :='/Inbox';
119 l_tofolder :='/'||p_tofolder;
120 ELSIF (p_reverse='Y') THEN
121 l_tofolder :='/Inbox';
122 l_frfolder :='/'||p_tofolder;
123 END IF;
124 l_str:='begin :l_ret:=im_api.movetofolder'||l_im_link||'(:a_msgid,:a_frfolder,:a_tofolder);end; ';
125
126 EXECUTE IMMEDIATE l_str using OUT l_ret,p_msgid,l_frfolder,l_tofolder;
127 IF l_ret=0 THEN
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129 x_status := 0;
130 ELSIF l_ret=2 THEN
131 x_return_status := FND_API.G_RET_STS_SUCCESS;
132 x_status := 2;
133 ELSIF l_ret=3 THEN
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135 x_status := 3;
136 END IF;
137
138 -- Standard Check Of p_commit.
139 IF FND_API.To_Boolean(p_commit) THEN
140 COMMIT WORK;
141 END IF;
142 -- Standard callto get message count and if count is 1, get message info.
143 FND_MSG_PUB.Count_And_Get
144 ( p_count => x_msg_count,
145 p_data => x_msg_data
146 );
147 EXCEPTION
148 WHEN FND_API.G_EXC_ERROR THEN
149 ROLLBACK TO moveMessage_PVT;
150 x_return_status := FND_API.G_RET_STS_ERROR ;
151 FND_MSG_PUB.Count_And_Get
152 ( p_count => x_msg_count,
153 p_data => x_msg_data
154 );
155 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
156 ROLLBACK TO moveMessage_PVT;
157 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
158 FND_MSG_PUB.Count_And_Get
159 ( p_count => x_msg_count,
160 p_data => x_msg_data
161 );
162 WHEN OTHERS THEN
163 ROLLBACK TO moveMessage_PVT;
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 IF FND_MSG_PUB.Check_Msg_Level
166 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
167 THEN
168 FND_MSG_PUB.Add_Exc_Msg
169 ( G_PKG_NAME ,
170 l_api_name
171 );
172 END IF;
173 FND_MSG_PUB.Count_And_Get
174 ( p_count => x_msg_count ,
175 p_data => x_msg_data
176 );
177
178 END;
179
180 PROCEDURE moveOesMessage (p_api_version_number IN NUMBER,
181 p_init_msg_list IN VARCHAR2,
182 p_commit IN VARCHAR2,
183 p_msgid IN NUMBER,
184 p_email_account_id IN NUMBER,
185 p_tofolder IN VARCHAR2,
186 p_fromfolder IN VARCHAR2,
187 x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2
190 ) is
191 l_api_name VARCHAR2(255):='moveOesMessage';
192 l_api_version_number NUMBER:=1.0;
193 l_pass VARCHAR2(30);
194 l_user VARCHAR2(30);
195 l_domain VARCHAR2(30);
196 l_db_server_id NUMBER;
197 l_str VARCHAR2(200);
198 l_ret NUMBER;
199 l_data varchar2(255);
200 l_stat varchar2(10);
201 l_count number;
202 l_im_link varchar2(200);
203 l_im_link1 varchar2(200);
204 l_frfolder varchar2(50);
205 l_tofolder varchar2(50);
206
207 MOVE_MSG_FAIL EXCEPTION;
208
209 OES_DOWN EXCEPTION;
210 PRAGMA EXCEPTION_INIT(OES_DOWN , -04052);
211
212
213 BEGIN
214 -- Standard Start of API savepoint
215 SAVEPOINT moveMessage_PVT;
216 -- Standard call to check for call compatibility.
217 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
218 p_api_version_number,
219 l_api_name,
220 G_PKG_NAME)
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224 -- Initialize message list if p_init_msg_list is set to TRUE.
225 IF FND_API.to_Boolean( p_init_msg_list )
226 THEN
227 FND_MSG_PUB.initialize;
228 END IF;
229 -- Initialize API return status to SUCCESS
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231
232 SELECT DB_SERVER_ID,EMAIL_USER,DOMAIN,EMAIL_PASSWORD
233 INTO l_db_server_id,l_user,l_domain,l_pass
234 FROM IEM_EMAIL_ACCOUNTS
235 WHERE EMAIL_ACCOUNT_ID=p_email_account_id;
236
237 IEM_DB_CONNECTIONS_PVT.select_item(
238 p_api_version_number =>1.0,
239 p_db_server_id =>l_db_server_id,
240 p_is_admin =>'P',
241 x_db_link=>l_im_link1,
242 x_return_status =>l_stat,
243 x_msg_count => l_count,
244 x_msg_data => l_data);
245
246 If l_im_link1 is null then
247 l_im_link:=null;
248 else
249 l_im_link:='@'||l_im_link1;
250 end if;
251 l_str:='begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user,:a_domain,:a_password);end; ';
252 EXECUTE IMMEDIATE l_str using OUT l_ret,l_user,l_domain,l_pass;
253 IF l_ret=0 THEN
254 x_return_status := FND_API.G_RET_STS_SUCCESS;
255 ELSE
256 x_return_status := FND_API.G_RET_STS_ERROR;
257 END IF;
258
259 -- Now we are ready to call im movetoFolder
260
261 l_frfolder :='/'||p_fromfolder;
262 l_tofolder :='/'||p_tofolder;
263
264 l_str:='begin :l_ret:=im_api.movetofolder'||l_im_link||'(:a_msgid,:a_frfolder,:a_tofolder);end; ';
265
266 EXECUTE IMMEDIATE l_str using OUT l_ret,p_msgid,l_frfolder,l_tofolder;
267 IF l_ret<>0 THEN
268 raise MOVE_MSG_FAIL;
269 END IF;
270
271 -- Standard Check Of p_commit.
272 IF FND_API.To_Boolean(p_commit) THEN
273 COMMIT WORK;
274 END IF;
275 -- Standard callto get message count and if count is 1, get message info.
276 FND_MSG_PUB.Count_And_Get
277 ( p_count => x_msg_count,
278 p_data => x_msg_data
279 );
280 EXCEPTION
281 WHEN MOVE_MSG_FAIL THEN
282 ROLLBACK TO moveMessage_PVT;
283 x_return_status := FND_API.G_RET_STS_ERROR ;
284 FND_MESSAGE.SET_NAME('IEM', 'IEM_MOVE_DEL_MESSAGE_FAIL');
285 FND_MSG_PUB.ADD;
286 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
287
288 WHEN OES_DOWN THEN
289 ROLLBACK TO moveMessage_PVT;
290 x_return_status := FND_API.G_RET_STS_ERROR ;
291 FND_MESSAGE.SET_NAME('IEM', 'IEM_OES_DOWN');
292 FND_MSG_PUB.ADD;
293 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
294
295 WHEN FND_API.G_EXC_ERROR THEN
296 ROLLBACK TO moveMessage_PVT;
297 x_return_status := FND_API.G_RET_STS_ERROR ;
298 FND_MSG_PUB.Count_And_Get
299 ( p_count => x_msg_count,
300 p_data => x_msg_data
301 );
302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
303 ROLLBACK TO moveMessage_PVT;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
305 FND_MSG_PUB.Count_And_Get
306 ( p_count => x_msg_count,
307 p_data => x_msg_data
308 );
309 WHEN OTHERS THEN
310 ROLLBACK TO moveMessage_PVT;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 IF FND_MSG_PUB.Check_Msg_Level
313 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
314 THEN
315 FND_MSG_PUB.Add_Exc_Msg
316 ( G_PKG_NAME ,
317 l_api_name
318 );
319 END IF;
320 FND_MSG_PUB.Count_And_Get
321 ( p_count => x_msg_count ,
322 p_data => x_msg_data
323 );
324
325 END;
326
327
328
329 END IEM_MOVEMSG_PVT;