[Home] [Help]
PACKAGE BODY: APPS.IEM_SPV_ACTIONS_PVT
Source
1 package body IEM_SPV_ACTIONS_PVT as
2 /* $Header: iemvspab.pls 120.0 2005/06/02 14:06:59 appldev noship $*/
3 G_PKG_NAME varchar2(100):='IEM_SPV_ACTIONS_PVT';
4
5
6 PROCEDURE delete_queue_msg (p_api_version_number IN NUMBER,
7 p_init_msg_list IN VARCHAR2 := NULL,
8 p_commit IN VARCHAR2 := NULL,
9 p_message_id in number,
10 p_reason_id in number,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2) IS
14
15 l_api_name varchar2(30):='delete_queue_msg';
16 l_api_version_number number:=1.0;
17 l_status NUMBER := 0;
18 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
19 l_msg_count NUMBER := 0;
20 l_msg_data VARCHAR2(2000);
21 l_post_mdts iem_rt_proc_emails%rowtype;
22 l_class_name iem_route_classifications.name%type;
23 MOVE_MSG_FAIL exception;
24 l_current_user NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
25 l_spv_resource_id number := 0;
26 l_interaction_rec JTF_IH_PUB.interaction_rec_type;
27 l_interaction_id NUMBER;
28 l_activity_id NUMBER;
29 l_activity_rec JTF_IH_PUB.activity_rec_type;
30 l_media_lc_rec APPS.JTF_IH_PUB.media_lc_rec_type;
31 l_milcs_id number;
32 l_party_id NUMBER := 1000;
33
34 BEGIN
35
36 --Standard Savepoint
37 SAVEPOINT delete_queue_msg;
38
39 -- Standard call to check for call compatibility.
40 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
41 p_api_version_number,
42 l_api_name,
43 G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48 --Initialize the message list if p_init_msg_list is set to TRUE
49 If FND_API.to_Boolean(p_init_msg_list) THEN
50 FND_MSG_PUB.initialize;
51 END IF;
52
53 --Initialize API status return
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 select * into l_post_mdts from iem_rt_proc_emails where message_id=p_message_id for update;
57
58 --l_current_user := 1003651;
59
60 select resource_id into l_spv_resource_id from jtf_rs_resource_extns where user_id=l_current_user;
61
62 if (l_post_mdts.customer_id = 0 or l_post_mdts.customer_id = -1) then
63 IEM_GETCUST_PVT.CustomerSearch(
64 P_Api_Version_Number => 1.0,
65 p_email => l_post_mdts.from_address,
66 x_party_id => l_party_id,
67 x_msg_count => l_msg_count,
68 x_return_status => l_return_status,
69 x_msg_data=> l_msg_data);
70
71 IF l_return_status<>'S' THEN
72 raise MOVE_MSG_FAIL;
73 END IF;
74 else
75 l_party_id := l_post_mdts.customer_id;
76 end if;
77
78 if (l_post_mdts.ih_interaction_id is null) then
79
80 -- Open an Interaction
81 l_interaction_rec.start_date_time := sysdate;
82 l_interaction_rec.resource_id:= l_spv_resource_id;
83 l_interaction_rec.party_id := l_party_id;
84 --l_interaction_rec.outcome_id := 14; -- EMAIL DELETED
85 --l_interaction_rec.result_id := 13;
86 l_interaction_rec.handler_id := 680; -- IEM APPL_ID
87 --l_interaction_rec.reason_id := 10;
88
89 select wu.outcome_id, wu.result_id, wu.reason_id into
90 l_interaction_rec.outcome_id, l_interaction_rec.result_id, l_interaction_rec.reason_id
91 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
92 where aa.action_id = 31 and aa.action_item_id = 45
93 and aa.default_wrap_id = wu.wrap_id;
94
95 JTF_IH_PUB.Open_Interaction( p_api_version => 1.0,
96 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
97 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
98 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
99 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
100 x_return_status => l_return_status,
101 x_msg_count => l_msg_count,
102 x_msg_data => l_msg_data,
103 x_interaction_id => l_interaction_id,
104 p_interaction_rec => l_interaction_rec
105 );
106
107 IF l_return_status<>'S' THEN
108 raise MOVE_MSG_FAIL;
109 END IF;
110 else
111 l_interaction_id := l_post_mdts.ih_interaction_id;
112
113 l_interaction_rec.interaction_id:= l_interaction_id;
114 l_interaction_rec.resource_id:= l_spv_resource_id;
115 l_interaction_rec.reason_id:= p_reason_id;
116
117 select wu.outcome_id, wu.result_id into
118 l_interaction_rec.outcome_id, l_interaction_rec.result_id
119 from jtf_ih_action_action_items aa, jtf_ih_wrap_ups wu
120 where aa.action_id = 31 and aa.action_item_id = 45
121 and aa.default_wrap_id = wu.wrap_id;
122
123 JTF_IH_PUB.Update_Interaction( p_api_version => 1.0,
124 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
125 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
126 p_user_id => nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
127 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
128 x_return_status => l_return_status,
129 x_msg_count => l_msg_count,
130 x_msg_data => l_msg_data,
131 p_interaction_rec => l_interaction_rec
132 );
133
134 IF l_return_status<>'S' THEN
135 raise MOVE_MSG_FAIL;
136 END IF;
137
138 end if; -- if (l_post_mdts.ih_interaction_id is null) then
139
140 -- Add an Activity
141 l_activity_rec.start_date_time := SYSDATE;
142 l_activity_rec.media_id := l_post_mdts.ih_media_item_id;
143 l_activity_rec.action_id := 31; -- Deleted an inbound email
144 l_activity_rec.interaction_id := l_interaction_id;
145 l_activity_rec.outcome_id := 14;
146 l_activity_rec.result_id := 13;
147 l_activity_rec.reason_id := p_reason_id;
148 l_activity_rec.action_item_id := 45;-- EMAIL
149
150
151 JTF_IH_PUB.Add_Activity(p_api_version => 1.0,
152 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
153 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
154 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
155 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
156 x_return_status => l_return_status,
157 x_msg_count => l_msg_count,
158 x_msg_data => l_msg_data,
159 p_activity_rec => l_activity_rec,
160 x_activity_id => l_activity_id
161 );
162
163 IF l_return_status<>'S' THEN
164 raise MOVE_MSG_FAIL;
165 END IF;
166
167
168 -- Create a Media Life Cycle
169 l_media_lc_rec.media_id :=l_post_mdts.ih_media_item_id ;
170 l_media_lc_rec.milcs_type_id := 6;
171 l_media_lc_rec.start_date_time := sysdate;
172 l_media_lc_rec.handler_id := 680;
173 l_media_lc_rec.resource_id := l_spv_resource_id;
174
175 JTF_IH_PUB.Add_MediaLifeCycle( 1.0,
176 'T',
177 'F',
178 TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
179 TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
180 nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
181 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
182 l_return_status,
183 l_msg_count,
184 l_msg_data,
185 l_media_lc_rec,
186 l_milcs_id);
187 IF l_return_status<>'S' THEN
188 raise MOVE_MSG_FAIL;
189 END IF;
190
191 -- Close Interaction
192 l_interaction_rec.interaction_id:=l_interaction_id;
193
194 JTF_IH_PUB.Close_Interaction( p_api_version => 1.0,
195 p_resp_appl_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_APPL_ID')),
196 p_resp_id => TO_NUMBER(FND_PROFILE.VALUE('RESP_ID')),
197 p_user_id =>nvl(TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),-1),
198 p_login_id =>TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
199 x_return_status => l_return_status,
200 x_msg_count => l_msg_count,
201 x_msg_data => l_msg_data,
202 p_interaction_rec => l_interaction_rec);
203
204 IF l_return_status<>'S' THEN
205 raise MOVE_MSG_FAIL;
206 END IF;
207
208 -- Move Messages between folders
209 /*select name into l_class_name from iem_route_classifications where route_classification_id=l_post_mdts.rt_classification_id;
210
211 iem_movemsg_pvt.moveOesMessage (p_api_version_number => 1.0,
212 p_init_msg_list => FND_API.G_FALSE,
213 p_commit=> FND_API.G_FALSE,
214 p_msgid => l_post_mdts.source_message_id,
215 p_email_account_id => l_post_mdts.email_account_id,
216 p_tofolder => 'Deleted',
217 p_fromfolder => l_class_name,
218 x_return_status => l_return_status,
219 x_msg_count => l_msg_count,
220 x_msg_data => l_msg_data);
221
222
223 --Check for error, raise exception
224 --if error raise MOVE_MSG_FAIL;
225 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
226 raise MOVE_MSG_FAIL;
227 END IF;
228 */
229 -- Delete from queue and close media items
230 IEM_MAILITEM_PUB.ResolvedMessage (p_api_version_number => 1.0,
231 p_init_msg_list => FND_API.G_FALSE,
232 p_commit => FND_API.G_FALSE,
233 p_message_id => l_post_mdts.message_id,
234 p_action_flag => 'D',
235 x_return_status => l_return_status,
236 x_msg_count => l_msg_count,
237 x_msg_data => l_msg_data);
238
239 --Check for error, raise exception
240 --if error raise MOVE_MSG_FAIL;
241 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
242 raise MOVE_MSG_FAIL;
243 END IF;
244
245 --Standard check of p_commit
246 IF FND_API.to_Boolean(p_commit) THEN
247 COMMIT WORK;
248 END IF;
249
250
251 EXCEPTION
252 WHEN MOVE_MSG_FAIL THEN
253 ROLLBACK TO delete_queue_msg;
254 x_return_status := FND_API.G_RET_STS_ERROR ;
255 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
256
257
258
259 WHEN FND_API.G_EXC_ERROR THEN
260 ROLLBACK TO delete_queue_msg;
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
262 FND_MSG_PUB.Count_And_Get
263 ( p_count => x_msg_count,p_data => x_msg_data);
264
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 ROLLBACK TO delete_queue_msg;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
268 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
269
270 WHEN OTHERS THEN
271 ROLLBACK TO delete_queue_msg;
272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
274 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
275 END IF;
276
277 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
278
279 END delete_queue_msg;
280
281
282 PROCEDURE delete_queue_msg_batch (p_api_version_number IN NUMBER,
283 p_init_msg_list IN VARCHAR2 := NULL,
284 p_commit IN VARCHAR2 := NULL,
285 p_message_ids_tbl IN jtf_varchar2_Table_100,
286 p_reason_id IN NUMBER,
287 x_moved_message_count OUT NOCOPY NUMBER,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2) IS
291
292 l_api_name varchar2(30):='delete_queue_msg_batch';
293 l_api_version_number number:=1.0;
294 l_status NUMBER := 0;
295 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
296 l_msg_count NUMBER := 0;
297 l_msg_data VARCHAR2(2000);
298 l_moved_message_count NUMBER := 0;
299
300 BEGIN
301
302 --Standard Savepoint
303 SAVEPOINT delete_queue_msg_batch;
304
305 -- Standard call to check for call compatibility.
306 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
307 p_api_version_number,
308 l_api_name,
309 G_PKG_NAME)
310 THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 END IF;
313
314 --Initialize the message list if p_init_msg_list is set to TRUE
315 If FND_API.to_Boolean(p_init_msg_list) THEN
316 FND_MSG_PUB.initialize;
317 END IF;
318
319 --Initialize API status return
320 x_return_status := FND_API.G_RET_STS_SUCCESS;
321
322 FOR i IN p_message_ids_tbl.FIRST..p_message_ids_tbl.LAST LOOP
323
324
325 iem_spv_actions_pvt.delete_queue_msg (p_api_version_number => 1.0,
326 p_init_msg_list => FND_API.G_FALSE,
327 p_commit => FND_API.G_FALSE,
328 p_message_id => p_message_ids_tbl(i),
329 p_reason_id => p_reason_id,
330 x_return_status => l_return_status,
331 x_msg_count => l_msg_count,
332 x_msg_data => l_msg_data) ;
333
334 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
335 l_moved_message_count := l_moved_message_count + 1;
336 END IF;
337
338 END LOOP;
339
340 x_moved_message_count := l_moved_message_count;
341
342
343 --Standard check of p_commit
344 IF FND_API.to_Boolean(p_commit) THEN
345 COMMIT WORK;
346 END IF;
347
348
349 EXCEPTION
350
351 WHEN FND_API.G_EXC_ERROR THEN
352 ROLLBACK TO delete_queue_msg;
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
354 FND_MSG_PUB.Count_And_Get
355 ( p_count => x_msg_count,p_data => x_msg_data);
356
357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
358 ROLLBACK TO delete_queue_msg;
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
360 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
361
362 WHEN OTHERS THEN
363 ROLLBACK TO delete_queue_msg;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
366 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
367 END IF;
368
369 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
370
371 END delete_queue_msg_batch;
372
373 end IEM_SPV_ACTIONS_PVT ;