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