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