[Home] [Help]
PACKAGE BODY: APPS.IEM_MGINBOX_PUB
Source
1 PACKAGE BODY IEM_MGINBOX_PUB as
2 /* $Header: iemmginboxb.pls 120.3 2006/09/01 22:28:06 rtripath noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_MGINBOX_PUB';
5 PROCEDURE runInbox (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_agent_Account_id IN NUMBER,
10 p_inb_migration_id IN NUMBER,
11 p_outb_migration_id IN NUMBER,
12 p_type IN VARCHAR2, --'I' or 'O'
13 p_rerun IN VARCHAR2, --'Y'/'N'
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2
17 )
18 IS
19 l_api_name VARCHAR2(255);
20 l_api_version_number NUMBER;
21 l_rt_media_item_id NUMBER;
22 l_rt_interaction_id NUMBER;
23 l_agent_account_id NUMBER;
24 l_resource_id NUMBER;
25 l_folder_name VARCHAR2(255);
26 l_msg_uid NUMBER;
27 l_outb_rt_media_item_id NUMBER;
28 l_outb_agent_account_id NUMBER;
29 l_outb_folder_name VARCHAR2(255);
30 l_outb_msg_uid NUMBER;
31 l_dblink VARCHAR2(128);
32 l_reply_to_str VARCHAR2(240);
33 l_cc_str VARCHAR2(240);
34 l_bcc_str VARCHAR2(240);
35 l_sequence NUMBER;
36 l_userid NUMBER;
37 l_loginid NUMBER;
38 l_mig_status_inb VARCHAR2(1);
39 l_mig_status_outb VARCHAR2(1);
40 l_mig_status VARCHAR2(1);
41 l_err_msg VARCHAR2(1000);
42 mdts IEM_POST_MDTS%ROWTYPE;
43
44 -- select a.message_id, a.email_account_id, a.rfc822_message_id, a.sender_name,
45 -- a.to_address, a.sent_date, a.subject, a.ih_media_item_id, a.message_size,
46 -- a.source_message_id
47
48 -- Cursor for p_rerun == 'N'
49 Cursor acq_cur IS
50 select a.*
51 from iem_post_mdts a, iem_agent_accounts b
52 where a.email_account_id = p_email_account_id and a.agent_id =
53 b.resource_id and b.agent_account_id = p_agent_account_id and
54 a.email_account_id = b.email_account_id;
55
56 -- Cursor for p_rerun == 'Y' and p_type == 'I'
57 Cursor acq_inb_re_cur IS
58 select a.*
59 from iem_post_mdts a, iem_agent_accounts b
60 where a.email_account_id = p_email_account_id and a.agent_id =
61 b.resource_id and b.agent_account_id = p_agent_account_id and
62 a.email_account_id = b.email_account_id
63 and a.message_id not in (select message_id from iem_migration_store_temp
64 where migration_id = p_inb_migration_id and message_type = 'I'
65 and mig_status = 'R');
66
67 -- Cursor for p_rerun == 'Y' and p_type == 'O'
68 Cursor acq_outb_re_cur IS
69 select a.*
70 from iem_post_mdts a, iem_agent_accounts b
71 where a.email_account_id = p_email_account_id and a.agent_id =
72 b.resource_id and b.agent_account_id = p_agent_account_id and
73 a.email_account_id = b.email_account_id
74 and a.message_id not in (select message_id from iem_migration_store_temp
75 where migration_id = p_inb_migration_id and message_type = 'O'
76 and mig_status = 'R');
77 BEGIN
78 l_userid := NVL(to_number(FND_PROFILE.VALUE('USER_ID')), -1);
79 l_loginid := NVL(to_number(FND_PROFILE.VALUE('LOGIN_ID')), -1);
80
81 -- Standard Start of API savepoint
82 SAVEPOINT runInbox_pvt;
83
84 -- Initialize variables
85 l_api_name :='runInbox';
86 l_api_version_number :=1.0;
87
88
89 -- Standard call to check for call compatibility.
90 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
91 1.0,
92 l_api_name,
93 G_PKG_NAME)
94 THEN
95 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
96 END IF;
97
98 -- Initialize message list if p_init_msg_list is set to TRUE.
99 IF FND_API.to_Boolean( p_init_msg_list )
100 THEN
101 FND_MSG_PUB.initialize;
102 END IF;
103
104 -- Initialize API return status to SUCCESS
105 x_return_status := FND_API.G_RET_STS_SUCCESS;
106
107 -----------------------Code------------------------
108 if (p_rerun = 'N') then
109 OPEN acq_cur;
110 LOOP
111 FETCH acq_cur INTO mdts;
112 EXIT WHEN acq_cur%NOTFOUND;
113 l_err_msg := null;
114 begin
115 begin
116 l_rt_interaction_id := -1;
117 select rtm.rt_media_item_id, rtm.rt_interaction_id,
118 rtm.agent_account_id, rtm.resource_id, rtm.folder_name,
119 rtm.folder_uid
120 into l_rt_media_item_id, l_rt_interaction_id, l_agent_account_id,
121 l_resource_id, l_folder_name, l_msg_uid
122 from iem_rt_media_items rtm, iem_rt_interactions rti
123 where rtm.message_id = mdts.message_id and rtm.email_type ='I'
124 and rtm.rt_interaction_id = rti.rt_interaction_id
125 and rti.expire = 'N';
126 exception
127 when others then
128 l_err_msg := substr('Error in query iem_rt_media_items and iem_rt_interactions'||SQLERRM, 1000);
129 end;
130 if (p_type = 'I') then
131 begin -- inbound message
132 begin
133 select a.db_link into l_dblink
134 from iem_db_connections a, iem_email_accounts b
135 where a.db_server_id=b.db_Server_id
136 and b.email_account_id = mdts.email_account_id and a.is_admin='A';
137 exception
138 when others then
139 l_err_msg := substr(l_err_msg ||'Error in query iem_db_connection and iem_email_accounts'||SQLERRM, 1000);
140 end;
141
142 begin
143 execute immediate 'select reply_to_str, cc_str, bcc_str from '
144 ||'OM_HEADER@'||l_dblink||' where msg_id = mdts.source_message_id'
145 into l_reply_to_str, l_cc_str, l_bcc_str;
146 exception
147 when others then
148 l_err_msg := substr(l_err_msg || 'Error in query iem_db_connection and iem_email_accounts'||SQLERRM, 1000);
149 end;
150
151 if (l_err_msg is null) then
152 l_mig_status := 'R';
153 else
154 l_mig_status := 'E';
155 end if;
156 select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;
157 insert into IEM_MIGRATION_STORE_TEMP (
158 mail_id, message_id, migration_id, message_type, email_account_id,
159 agent_account_id, resource_id, folder_name, rt_media_item_id,
160 msg_uid, rfc822_message_id, from_str, to_str, reply_to_str,
161 cc_str, bcc_str, sent_date, subject, ih_media_item_id,
162 message_size, created_by, creation_date, last_update_date,
163 last_updated_by, last_update_login, mig_status, error_text)
164 values (
165 l_sequence, mdts.message_id, p_inb_migration_id, 'I', mdts.email_account_id,
166 l_agent_account_id, l_resource_id, l_folder_name,
167 l_rt_media_item_id, l_msg_uid, mdts.rfc822_message_id,
168 mdts.sender_name, mdts.to_address, l_reply_to_str, l_cc_str,
169 l_bcc_str, to_char(mdts.sent_date,'DD-MON-YYYY HH24:MI:SS'), mdts.subject,
170 mdts.ih_media_item_id, mdts.message_size,
171 l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
172 );
173 end; -- inbound
174 else -- type 'O'
175 begin -- check for draft
176 begin
177 select rtm.rt_media_item_id, rtm.agent_account_id, rtm.folder_name,
178 rtm.folder_uid into l_outb_rt_media_item_id,
179 l_outb_agent_account_id, l_outb_folder_name, l_outb_msg_uid
180 from iem_rt_media_items rtm, iem_msg_parts part
181 where rtm.rt_interaction_id = l_rt_interaction_id
182 and rtm.email_type = 'O' and rtm.folder_uid > 0
183 and rtm.folder_name = 'Drafts'
184 and part.ref_key = rtm.rt_media_item_id
185 and part.part_type = 'HEADERS'
186 and part.delete_flag <> 'Y';
187 exception
188 when NO_DATA_FOUND then
189 l_outb_rt_media_item_id := -1;
190 when others then
191 l_err_msg := substr(l_err_msg ||'Error in query draft' || SQLERRM, 1000);
192 end;
193
194 if ( l_outb_rt_media_item_id > 0 ) then
195 if (l_err_msg is null) then
196 l_mig_status := 'R';
197 else
198 l_mig_status := 'E';
199 end if;
200 begin
201 select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;
202 insert into IEM_MIGRATION_STORE_TEMP (
203 mail_id, message_id, migration_id, message_type, email_account_id,
204 agent_account_id, resource_id, folder_name,
205 rt_media_item_id, msg_uid, created_by, creation_date,
206 last_update_date, last_updated_by, last_update_login, mig_status, error_text)
207 values (
208 l_sequence, mdts.message_id, p_outb_migration_id, 'D', mdts.email_account_id,
209 l_outb_agent_account_id, l_resource_id, l_outb_folder_name,
210 l_outb_rt_media_item_id, l_outb_msg_uid,
211 l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
212 );
213 end;
214 end if; -- draft exists
215 end; -- check for draft
216 end if; -- type
217 end;
218 END LOOP;
219 CLOSE acq_cur;
220 else
221 if (p_type = 'I') then
222 OPEN acq_inb_re_cur;
223 LOOP
224 FETCH acq_inb_re_cur INTO mdts;
225 EXIT WHEN acq_inb_re_cur%NOTFOUND;
226 l_err_msg := null;
227 begin -- inbound message
228 begin
229 select rtm.rt_media_item_id, rtm.rt_interaction_id,
230 rtm.agent_account_id, rtm.resource_id, rtm.folder_name,
231 rtm.folder_uid
232 into l_rt_media_item_id, l_rt_interaction_id, l_agent_account_id,
233 l_resource_id, l_folder_name, l_msg_uid
234 from iem_rt_media_items rtm, iem_rt_interactions rti
235 where rtm.message_id = mdts.message_id and rtm.email_type ='I'
236 and rtm.rt_interaction_id = rti.rt_interaction_id
237 and rti.expire = 'N';
238 exception
239 when others then
240 l_err_msg := substr('Error in query iem_rt_media_items and iem_rt_interactions'||SQLERRM, 1000);
241 end;
242
243 begin
244 select a.db_link into l_dblink
245 from iem_db_connections a, iem_email_accounts b
246 where a.db_server_id=b.db_Server_id
247 and b.email_account_id = mdts.email_account_id and a.is_admin='A';
248 exception
249 when others then
250 l_err_msg := substr(l_err_msg ||'Error in query iem_db_connection and iem_email_accounts'||SQLERRM, 1000);
251 end;
252
253 begin
254 execute immediate 'select reply_to_str, cc_str, bcc_str from '
255 ||'OM_HEADER@'||l_dblink||' where msg_id = mdts.source_message_id'
256 into l_reply_to_str, l_cc_str, l_bcc_str;
257 exception
258 when others then
259 l_err_msg := substr(l_err_msg || 'Error in query iem_db_connection and iem_email_accounts'||SQLERRM, 1000);
260 end;
261
262 if (l_err_msg is null) then
263 l_mig_status := 'R';
264 else
265 l_mig_status := 'E';
266 end if;
267 select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;
268 insert into IEM_MIGRATION_STORE_TEMP (
269 mail_id, message_id, migration_id, message_type, email_account_id,
270 agent_account_id, resource_id, folder_name, rt_media_item_id,
271 msg_uid, rfc822_message_id, from_str, to_str, reply_to_str,
272 cc_str, bcc_str, sent_date, subject, ih_media_item_id,
273 message_size, created_by, creation_date, last_update_date,
274 last_updated_by, last_update_login, mig_status, error_text)
275 values (
276 l_sequence, mdts.message_id, p_inb_migration_id, 'I', mdts.email_account_id,
277 l_agent_account_id, l_resource_id, l_folder_name,
278 l_rt_media_item_id, l_msg_uid, mdts.rfc822_message_id,
279 mdts.sender_name, mdts.to_address, l_reply_to_str, l_cc_str,
280 l_bcc_str,to_char(mdts.sent_date,'DD-MON-YYYY HH24:MI:SS'), mdts.subject,
281 mdts.ih_media_item_id, mdts.message_size,
282 l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
283 );
284 delete from IEM_MIGRATION_STORE_TEMP where migration_id = p_inb_migration_id
285 and message_id = mdts.message_id and message_type = 'I'
286 and mig_status <> 'R' and mail_id <> l_sequence;
287 end; -- inbound
288 END LOOP;
289 CLOSE acq_inb_re_cur;
290 else
291 OPEN acq_outb_re_cur;
292 LOOP
293 FETCH acq_outb_re_cur INTO mdts;
294 EXIT WHEN acq_outb_re_cur%NOTFOUND;
295 begin -- check for draft
296 begin
297 l_err_msg := null;
298 select rtm.rt_media_item_id, rtm.agent_account_id, rtm.folder_name,
299 rtm.folder_uid into l_outb_rt_media_item_id,
300 l_outb_agent_account_id, l_outb_folder_name, l_outb_msg_uid
301 from iem_rt_media_items rtm, iem_msg_parts part
302 where rtm.rt_interaction_id = l_rt_interaction_id
303 and rtm.email_type = 'O' and rtm.folder_uid > 0
304 and rtm.folder_name = 'Drafts'
305 and part.ref_key = rtm.rt_media_item_id
306 and part.part_type = 'HEADERS'
307 and part.delete_flag <> 'Y';
308 exception
309 when NO_DATA_FOUND then
310 l_outb_rt_media_item_id := -1;
311 when others then
312 l_err_msg := substr('Error in query draft' || SQLERRM, 1000);
313 end;
314
315 if ( l_outb_rt_media_item_id > 0 ) then
316 if (l_err_msg is null) then
317 l_mig_status := 'R';
318 else
319 l_mig_status := 'E';
320 end if;
321 begin
322 select IEM_MIGRATION_STORE_TEMP_S1.nextval into l_sequence from DUAL;
323 insert into IEM_MIGRATION_STORE_TEMP (
324 mail_id, message_id, migration_id, message_type, email_account_id,
325 agent_account_id, resource_id, folder_name,
326 rt_media_item_id, msg_uid, created_by, creation_date,
327 last_update_date, last_updated_by, last_update_login, mig_status, error_text)
328 values (
329 l_sequence, mdts.message_id, p_outb_migration_id, 'D', mdts.email_account_id,
330 l_outb_agent_account_id, l_resource_id, l_outb_folder_name,
331 l_outb_rt_media_item_id, l_outb_msg_uid,
332 l_userid, sysdate, sysdate, l_userid, l_loginid, l_mig_status, l_err_msg
333 );
334 delete from IEM_MIGRATION_STORE_TEMP where migration_id = p_outb_migration_id
335 and message_id = mdts.message_id and message_type = 'O'
336 and mig_status <> 'R' and mail_id <> l_sequence;
337 end;
338 end if; -- draft exists
339 end; -- check for draft
340 END LOOP;
341 CLOSE acq_outb_re_cur;
342 end if; -- rerun draft
343 end if; -- check rerun
344
345 -------------------End Code------------------------
346 -- Standard Check Of p_commit.
347 IF FND_API.To_Boolean(p_commit) THEN
348 COMMIT WORK;
349 END IF;
350 -- Standard callto get message count and if count is 1, get message info.
351 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_TRUE,
352 p_count => x_msg_count,
353 p_data => x_msg_data
354 );
355 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR THEN
357 ROLLBACK TO runInbox_pvt;
358 x_return_status := FND_API.G_RET_STS_ERROR ;
359 FND_MSG_PUB.Count_And_Get(
360 p_encoded => FND_API.G_TRUE,
361 p_count => x_msg_count,
362 p_data => x_msg_data);
363 WHEN OTHERS THEN
364 ROLLBACK TO runInbox_pvt;
365 x_return_status := FND_API.G_RET_STS_ERROR;
366 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
367 THEN
368 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
369 END IF;
370 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_TRUE,
371 p_count => x_msg_count,
372 p_data => x_msg_data);
373
374 End runInbox;
375 End IEM_MGINBOX_PUB;