[Home] [Help]
PACKAGE BODY: APPS.IEM_MDTSTARTPROCESS_PUB
Source
1 PACKAGE BODY IEM_MDTSTARTPROCESS_PUB as
2 /* $Header: iempcmsb.pls 120.3 2010/12/23 09:20:36 sanjrao ship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_MDTSTARTPROCESS_PUB ';
5 --G_RETRY_FOLDER varchar2(50):='/'||IEM_IM_SETUP_PVT.G_RetryFldrName;
6 --G_ADMIN_FOLDER varchar2(50):='/'||IEM_IM_SETUP_PVT.G_AdminFldrName;
7 G_RETRY_FOLDER varchar2(50):='/Retry';
8 G_ADMIN_FOLDER varchar2(50):='/Admin';
9 G_DEFAULT_FOLDER varchar2(50):='/Inbox';
10 G_IM_LINK varchar2(90);
11 STOP_ALL_WORKERS EXCEPTION;
12 PROCEDURE LaunchProcess(ERRBUF OUT NOCOPY VARCHAR2,
13 ERRRET OUT NOCOPY VARCHAR2,
14 p_api_version_number IN NUMBER,
15 p_init_msg_list IN VARCHAR2 ,
16 p_commit IN VARCHAR2 ,
17 p_workflowProcess in varchar2 ,
18 p_Item_Type in varchar2 ,
19 p_qopt in varchar2:='NO_WAIT',
20 p_counter in number
21 ) IS
22 l_api_name VARCHAR2(255):='LaunchProcess';
23 l_api_version_number NUMBER:=1.0;
24 Itemuserkey varchar2(30) := 'iemmail_preproc';
25 l_itemkey varchar2(30);
26 l_msg_count number;
27 l_seq number;
28 l_Error_Message VARCHAR2(2000);
29 l_call_status BOOLEAN;
30 l_return_status varchar2(6);
31 l_msg_data varchar2(1000);
32 l_exit varchar2(1):='T';
33 l_status varchar2(1);
34 l_counter number:=1;
35 l_errbuf varchar2(100);
36 l_errret varchar2(10);
37 l_rphase varchar2(30);
38 l_rstatus varchar2(30);
39 l_dphase varchar2(30);
40 l_dstatus varchar2(30);
41 l_message varchar2(240);
42 l_request_id number;
43 l_counter2 number:=0;
44 l_UProcess_ID number;
45 l_UTerminal varchar2(50);
46 l_UNode varchar2(50);
47 l_UName varchar2(50);
48 l_UProgram varchar2(50);
49 --commented out for bug 9929169
50 BEGIN
51 -- Standard Start of API savepoint
52 --SAVEPOINT LaunchProcess_PUB;
53 -- Standard call to check for call compatibility.
54 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
55 p_api_version_number,
56 l_api_name,
57 G_PKG_NAME)
58 THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF FND_API.to_Boolean( p_init_msg_list )
63 THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66 -- dbms_lock.sleep(2*60);
67 /*
68 * removed by sanjana to take care of the issue 9929169
69 BEGIN
70 SELECT VALUE into l_status from IEM_COMP_RT_STATS
71 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
72 EXCEPTION WHEN NO_DATA_FOUND THEN
73 INSERT into IEM_COMP_RT_STATS
74 (
75 COMP_RT_STATS_ID,
76 TYPE,
77 PARAM,
78 VALUE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_DATE)
81 VALUES
82 (-1,
83 'MAILPROC',
84 'RUNTIME STATUS',
85 'T',
86 99999,
87 SYSDATE);
88 WHEN TOO_MANY_ROWS THEN
89 delete from iem_comp_rt_Stats
90 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS'
91 and rownum<
92 (select count(*) from iem_comp_rt_stats
93 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS');
94 WHEN OTHERS THEN
95 NULL;
96 END;
97 */
98
99 /* shams
100 IF l_status='F'
101 THEN RAISE STOP_ALL_WORKERS;
102 END IF;
103 */
104 -- Fix for bug 3410951
105 --UPDATE IEM_COMP_RT_STATS
106 --set VALUE='T'
107 --WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
108
109 LOOP
110 /*
111 BEGIN
112 SELECT value into l_status from iem_comp_rt_stats
113 WHERE type='MAILPROC' and param='RUNTIME STATUS';
114 EXIT when l_status='F';
115 --IF l_status='F'
116 -- THEN RAISE STOP_ALL_WORKERS;
117 --END IF;
118 EXCEPTION WHEN OTHERS THEN
119 EXIT;
120 END;
121 */
122 -- Added new procedure with new parameters
123 IEM_EMAIL_PROC_PVT.PROC_EMAILS(ERRBUF=>l_errbuf,
124 ERRRET=>l_errret,
125 p_api_version_number=>1.0,
126 p_init_msg_list=>p_init_msg_list,
127 p_commit=>p_commit,
128 p_count=>1);
129 COMMIT;
130 l_counter:=l_counter+1;
131 IF p_counter is not null and p_qopt='NO_WAIT' then
132 EXIT when l_counter>p_counter;
133 END IF;
134 END LOOP;
135
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138
139 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_EXEC_ERROR');
140 l_Error_Message := FND_MESSAGE.GET;
141 fnd_file.put_line(fnd_file.log, l_Error_Message);
142 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
143 --ROLLBACK TO LaunchProcess_PUB;
144 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
145
146 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_UNXPTD_ERR');
147 l_Error_Message := FND_MESSAGE.GET;
148 fnd_file.put_line(fnd_file.log, l_Error_Message);
149 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
150 --ROLLBACK TO LaunchProcess_PUB;
151 WHEN STOP_ALL_WORKERS THEN
152 -- Get a current IEM Concurrent worker id.
153 l_call_status :=FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id, 'IEM', 'IEMMDTWW', l_rphase,l_rstatus,l_dphase,l_dstatus,l_message);
154 IF l_call_status = false THEN
155 fnd_file.put_line(fnd_file.log, l_message);
156 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
157
158 ELSE -- call staus is true
159 -- Cancel request id
160 l_call_status :=FND_CONCURRENT.CANCEL_REQUEST(l_request_id,l_Error_Message);
161
162 IF l_call_status = false THEN
163 -- Try again
164 l_counter2 :=0;
165 LOOP -- Sleep while other worker is terminating
166 l_counter2 := l_counter2 +1;
167 EXIT when l_counter2 = 100000000;
168 END LOOP;
169 l_call_status :=FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id, 'IEM', 'IEMMDTWW', l_rphase,l_rstatus,l_dphase,l_dstatus,l_message);
170 l_call_status :=FND_CONCURRENT.CANCEL_REQUEST(l_request_id,l_Error_Message);
171 IF l_call_status = false THEN
172 fnd_file.put_line(fnd_file.log, l_Error_Message);
173 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_Error_Message);
174 ELSE -- call ststus is true
175 -- l_call_status :=FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id, 60, 600, l_rphase, l_rstatus, l_dphase, l_dstatus, l_message);
176 IF l_call_status = false THEN
177 fnd_file.put_line(fnd_file.log, l_Error_Message);
178 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
179 END IF;
180 END IF;
181
182 ELSE -- Call status is True
183 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', ' '||l_UProcess_ID||' '||l_UName||' '||l_UProgram);
184 IF l_call_status = false OR l_dstatus='TERMINATING' THEN
185 l_call_status :=FND_CONCURRENT.CHECK_LOCK_CONTENTION('', l_request_id, l_UProcess_ID, l_UTerminal, l_UNode, l_UName, l_UProgram);
186 fnd_file.put_line(fnd_file.log, l_message);
187 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', ' '||l_UProcess_ID||' '||l_UName||' '||l_UProgram);
188 END IF;
189 commit;
190 END IF;
191 commit;
192 END IF;
193 return;
194 --l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',' '||l_request_id||' '||l_Error_Message||' '||l_counter2);
195 WHEN OTHERS THEN
196
197 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_OTHER_ERRORS');
198 l_Error_Message := SQLERRM;
199 fnd_file.put_line(fnd_file.log, l_Error_Message);
200 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
201 --ROLLBACK TO LaunchProcess_PUB;
202 END LaunchProcess;
203
204 PROCEDURE ProcessRetry(ERRBUF OUT NOCOPY VARCHAR2,
205 ERRRET OUT NOCOPY VARCHAR2,
206 p_api_version_number in number,
207 p_init_msg_list IN VARCHAR2 ,
208 p_commit IN VARCHAR2 ,
209 p_workflowProcess in varchar2 ,
210 p_Item_Type in varchar2
211 ) IS
212
213 l_api_version_number number:=1.0;
214 l_api_name varchar2(30):='ProcessRetry';
215 l_errbuf varchar2(500);
216 l_errret varchar2(100);
217 l_Error_Message VARCHAR2(2000);
218 l_call_status BOOLEAN;
219 BEGIN
220 -- Standard Start of API savepoint
221 SAVEPOINT ProcessRetry_PUB;
222 -- Standard call to check for call compatibility.
223 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
224 p_api_version_number,
225 l_api_name,
226 G_PKG_NAME)
227 THEN
228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229 END IF;
230 -- Initialize message list if p_init_msg_list is set to TRUE.
231 IF FND_API.to_Boolean( p_init_msg_list )
232 THEN
233 FND_MSG_PUB.initialize;
234 END IF;
235 commit;
236 EXCEPTION
237 WHEN FND_API.G_EXC_ERROR THEN
238 ROLLBACK TO LaunchProcess_PUB;
239 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_EXEC_ERROR');
240 l_Error_Message := FND_MESSAGE.GET;
241 fnd_file.put_line(fnd_file.log, l_Error_Message);
242 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
244 ROLLBACK TO LaunchProcess_PUB;
245 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_UNXPTD_ERR');
246 l_Error_Message := FND_MESSAGE.GET;
247 fnd_file.put_line(fnd_file.log, l_Error_Message);
248 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
249 WHEN OTHERS THEN
250 ROLLBACK TO LaunchProcess_PUB;
251 FND_MESSAGE.SET_NAME('IEM','IEM_RETRYPROCESS_OTHER_ERR');
252 l_Error_Message := SQLERRM;
253 fnd_file.put_line(fnd_file.log, l_Error_Message);
254 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
255
256 END ProcessRetry;
257 PROCEDURE StopProcessing(ERRBUF OUT NOCOPY VARCHAR2,
258 ERRRET OUT NOCOPY VARCHAR2,
259 p_api_version_number IN NUMBER,
260 p_init_msg_list IN VARCHAR2 ,
261 p_commit IN VARCHAR2
262 ) IS
263 l_api_name VARCHAR2(255):='StopProcessing';
264 l_api_version_number NUMBER:=1.0;
265 l_msg_count number;
266 l_call_status BOOLEAN;
267 l_return_status varchar2(10);
268 l_Error_Message VARCHAR2(2000);
269 l_msg_data varchar2(240);
270 BEGIN
271 -- Standard Start of API savepoint
272 SAVEPOINT StopWorkflow_PUB;
273 -- Standard call to check for call compatibility.
274 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
275 p_api_version_number,
276 l_api_name,
277 G_PKG_NAME)
278 THEN
279 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
280 END IF;
281 -- Initialize message list if p_init_msg_list is set to TRUE.
282 IF FND_API.to_Boolean( p_init_msg_list )
283 THEN
284 FND_MSG_PUB.initialize;
285 END IF;
286 -- Initialize API return status to SUCCESS
287 UPDATE IEM_COMP_RT_STATS
288 set VALUE='F'
289 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
290 COMMIT;
291 EXCEPTION
292 WHEN FND_API.G_EXC_ERROR THEN
293 ROLLBACK TO Stopworkflow_PUB;
294 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_EXEC_ERROR');
295 l_Error_Message := FND_MESSAGE.GET;
296 fnd_file.put_line(fnd_file.log, l_Error_Message);
297 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
299 ROLLBACK TO Stopworkflow_PUB;
300 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_UNXPTD_ERR');
301 l_Error_Message := FND_MESSAGE.GET;
302 fnd_file.put_line(fnd_file.log, l_Error_Message);
303 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
304 WHEN OTHERS THEN
305 ROLLBACK TO Stopworkflow_PUB;
306 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_OTHER_ERR');
307 l_Error_Message := SQLERRM;
308 fnd_file.put_line(fnd_file.log, l_Error_Message);
309 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
310
311 END StopProcessing;
312
313 PROCEDURE PurgeWorkflow(ERRBUF OUT NOCOPY varchar2,
314 ERRRET OUT NOCOPY varchar2,
315 p_api_version_number IN NUMBER,
316 p_init_msg_list IN VARCHAR2 ,
317 p_commit IN VARCHAR2 ,
318 p_item_type IN VARCHAR2:='IEM_MAIL',
319 p_end_date IN varchar2
320 ) IS
321 l_api_name VARCHAR2(255):='PurgeWorkflow';
322 l_api_version_number NUMBER:=1.0;
323 l_msg_count number;
324 l_call_status BOOLEAN;
325 l_return_status varchar2(10);
326 l_error_message varchar2(200);
327 l_msg_data varchar2(1000);
328 l_date date;
329 CURSOR wf_err_data_csr is
330 SELECT item_key
331 from wf_item_activity_statuses
332 where item_type=p_item_type
333 and activity_status = 'ERROR'
334 and begin_date<=to_date(p_end_date,'yyyy/mm/dd hh24:mi:ss');
335 BEGIN
336 -- Standard Start of API savepoint
337 SAVEPOINT PurgeWorkflow_PUB;
338 -- Standard call to check for call compatibility.
339 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
340 p_api_version_number,
341 l_api_name,
342 G_PKG_NAME)
343 THEN
344 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
345 END IF;
346 -- Initialize message list if p_init_msg_list is set to TRUE.
347 IF FND_API.to_Boolean( p_init_msg_list )
348 THEN
349 FND_MSG_PUB.initialize;
350 END IF;
351 -- Initialize API return status to SUCCESS
352 FOR wf_err_data_rec in wf_err_data_csr LOOP
353 BEGIN
354 wf_engine.abortprocess(p_item_type,wf_err_data_rec.item_key);
355 EXCEPTION WHEN OTHERS THEN
356 NULL;
357 END;
358 END LOOP;
359 l_date:=to_date(p_end_Date,'yyyy/mm/dd hh24:mi:ss');
360 wf_purge.total(p_item_type,null,l_Date);
361 EXCEPTION
362 WHEN FND_API.G_EXC_ERROR THEN
363 ROLLBACK TO Purgeworkflow_PUB;
364 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_EXEC_ERROR');
365 l_Error_Message := FND_MESSAGE.GET;
366 fnd_file.put_line(fnd_file.log, l_Error_Message);
367 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
368 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
369 ROLLBACK TO Purgeworkflow_PUB;
370 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_UNXPTD_ERR');
371 l_Error_Message := FND_MESSAGE.GET;
372 fnd_file.put_line(fnd_file.log, l_Error_Message);
373 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
374 WHEN OTHERS THEN
375 ROLLBACK TO Purgeworkflow_PUB;
376 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_OTHER_ERR');
377 l_Error_Message := SQLERRM;
378 fnd_file.put_line(fnd_file.log, l_Error_Message);
379 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
380 END PurgeWorkflow;
381
382 END IEM_MDTSTARTPROCESS_PUB ;