[Home] [Help]
PACKAGE BODY: APPS.IEM_NEXT_GEN_PROCESS_EMAIL_PVT
Source
1 PACKAGE BODY IEM_NEXT_GEN_PROCESS_EMAIL_PVT as
2 /* $Header: iemngcwb.pls 120.0 2005/06/02 13:49:35 appldev noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_NEXT_GEN_PROCESS_EMAIL_PVT';
5 G_ADMIN_FOLDER CONSTANT varchar2(50) :='/Admin';
6 G_DEFAULT_FOLDER CONSTANT varchar2(50) :='/Inbox';
7 G_IM_LINK varchar2(90);
8 STOP_ALL_WORKERS EXCEPTION;
9
10 PROCEDURE LaunchProcess(
11 ERRBUF OUT NOCOPY VARCHAR2,
12 ERRRET OUT NOCOPY VARCHAR2,
13 p_api_version_number IN NUMBER,
14 p_init_msg_list IN VARCHAR2 ,
15 p_commit IN VARCHAR2 ,
16 p_workflowProcess IN VARCHAR2 ,
17 p_Item_Type IN VARCHAR2 ,
18 p_qopt IN VARCHAR2:='NO_WAIT',
19 p_counter IN NUMBER
20 ) IS
21
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_PVT;
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 -- New Table
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
98 LOOP
99 BEGIN
100 SELECT value into l_status from IEM_COMP_RT_STATS
101 WHERE type='MAILPROC' and param='RUNTIME STATUS';
102 EXIT when l_status='F';
103 EXCEPTION WHEN OTHERS THEN
104 EXIT;
105 END;
106
107 IEM_EMAIL_PROC_PVT.PROC_EMAILS(ERRBUF=>l_errbuf, -- New procedure
108 ERRRET=>l_errret,
109 p_api_version_number=>1.0,
110 p_init_msg_list=>p_init_msg_list, -- New parameter
111 p_commit=>p_commit,
112 p_count=>1); -- New parameter
113 COMMIT;
114 l_counter:=l_counter+1;
115 IF p_counter is not null and p_qopt='NO_WAIT' then
116 EXIT when l_counter>p_counter;
117 END IF;
118 END LOOP;
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 ROLLBACK TO LaunchProcess_PVT;
123 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_EXEC_ERROR');
124 l_Error_Message := FND_MESSAGE.GET;
125 fnd_file.put_line(fnd_file.log, l_Error_Message);
126 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
127
128 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
129 ROLLBACK TO LaunchProcess_PVT;
130 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_UNXPTD_ERR');
131 l_Error_Message := FND_MESSAGE.GET;
132 fnd_file.put_line(fnd_file.log, l_Error_Message);
133 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
134
135 WHEN STOP_ALL_WORKERS THEN
136 -- Get a current IEM Concurrent worker id.
137 l_call_status :=FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id, 'IEM', 'IEMNGNWW', l_rphase,l_rstatus,l_dphase,l_dstatus,l_message);
138 IF l_call_status = false THEN
139 fnd_file.put_line(fnd_file.log, l_message);
140 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
141
142 ELSE -- call staus is true
143 -- Cancel request id
144 l_call_status :=FND_CONCURRENT.CANCEL_REQUEST(l_request_id,l_Error_Message);
145
146 IF l_call_status = false THEN
147 -- Try again
148 l_counter2 :=0;
149 LOOP -- Sleep while other worker is terminating
150 l_counter2 := l_counter2 +1;
151 EXIT when l_counter2 = 100000000;
152 END LOOP;
153 l_call_status :=FND_CONCURRENT.GET_REQUEST_STATUS(l_request_id, 'IEM', 'IEMNGNWW', l_rphase,l_rstatus,l_dphase,l_dstatus,l_message);
154 l_call_status :=FND_CONCURRENT.CANCEL_REQUEST(l_request_id,l_Error_Message);
155 IF l_call_status = false THEN
156 fnd_file.put_line(fnd_file.log, l_Error_Message);
157 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_Error_Message);
158 ELSE -- call ststus is true
159 l_call_status :=FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id, 60, 600, l_rphase, l_rstatus, l_dphase, l_dstatus, l_message);
160 IF l_call_status = false THEN
161 fnd_file.put_line(fnd_file.log, l_Error_Message);
162 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_message);
163 END IF;
164 END IF;
165
166 ELSE -- Call status is True
167 l_call_status :=FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id, 60, 600, l_rphase, l_rstatus, l_dphase, l_dstatus, l_message);
168 IF l_call_status = false OR l_dstatus='TERMINATING' THEN
169 l_call_status :=FND_CONCURRENT.CHECK_LOCK_CONTENTION('', l_request_id, l_UProcess_ID, l_UTerminal, l_UNode, l_UName, l_UProgram);
170 fnd_file.put_line(fnd_file.log, l_message);
171 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', ' '||l_UProcess_ID||' '||l_UName||' '||l_UProgram);
172 END IF;
173 END IF;
174 END IF;
175 --l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',' '||l_request_id||' '||l_Error_Message||' '||l_counter2);
176
177 WHEN OTHERS THEN
178 ROLLBACK TO LaunchProcess_PVT;
179 FND_MESSAGE.SET_NAME('IEM','IEM_LAUNCHPROCESS_OTHER_ERRORS');
180 l_Error_Message := SQLERRM;
181 fnd_file.put_line(fnd_file.log, l_Error_Message);
182 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
183 END LaunchProcess;
184
185 PROCEDURE StopProcessing(ERRBUF OUT NOCOPY VARCHAR2,
186 ERRRET OUT NOCOPY VARCHAR2,
187 p_api_version_number IN NUMBER,
188 p_init_msg_list IN VARCHAR2 ,
189 p_commit IN VARCHAR2
190 ) IS
191 l_api_name VARCHAR2(255):='StopProcessing';
192 l_api_version_number NUMBER:=1.0;
193 l_msg_count number;
194 l_call_status BOOLEAN;
195 l_return_status varchar2(10);
196 l_Error_Message VARCHAR2(2000);
197 l_msg_data varchar2(240);
198 BEGIN
199 -- Standard Start of API savepoint
200 SAVEPOINT StopWorkflow_PVT;
201 -- Standard call to check for call compatibility.
202 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
203 p_api_version_number,
204 l_api_name,
205 G_PKG_NAME)
206 THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209
210 -- Initialize message list if p_init_msg_list is set to TRUE.
211 IF FND_API.to_Boolean( p_init_msg_list )
212 THEN
213 FND_MSG_PUB.initialize;
214 END IF;
215
216 -- Initialize API return status to SUCCESS
217 UPDATE IEM_COMP_RT_STATS
218 set VALUE='F'
219 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
220 COMMIT;
221
222 EXCEPTION
223 WHEN FND_API.G_EXC_ERROR THEN
224 ROLLBACK TO Stopworkflow_PVT;
225 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_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 Stopworkflow_PVT;
231 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_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
236 WHEN OTHERS THEN
237 ROLLBACK TO Stopworkflow_PVT;
238 FND_MESSAGE.SET_NAME('IEM','IEM_STOPWORKFLOW_OTHER_ERR');
239 l_Error_Message := SQLERRM;
240 fnd_file.put_line(fnd_file.log, l_Error_Message);
241 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
242
243 END StopProcessing;
244
245 PROCEDURE PurgeWorkflow(ERRBUF OUT NOCOPY varchar2,
246 ERRRET OUT NOCOPY varchar2,
247 p_api_version_number IN NUMBER,
248 p_init_msg_list IN VARCHAR2 ,
249 p_commit IN VARCHAR2 ,
250 p_item_type IN VARCHAR2:='IEM_MAIL',
251 p_end_date IN DATE:=sysdate-3
252 ) IS
253 l_api_name VARCHAR2(255):='PurgeWorkflow';
254 l_api_version_number NUMBER:=1.0;
255 l_msg_count number;
256 l_call_status BOOLEAN;
257 l_return_status varchar2(10);
258 l_error_message varchar2(200);
259 l_msg_data varchar2(1000);
260 CURSOR wf_err_data_csr is
261 SELECT item_key
262 from wf_item_activity_statuses
263 where item_type=p_item_type
264 and activity_status = 'ERROR'
265 and begin_date<=p_end_date;
266 BEGIN
267 -- Standard Start of API savepoint
268 SAVEPOINT PurgeWorkflow_PVT;
269 -- Standard call to check for call compatibility.
270 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
271 p_api_version_number,
272 l_api_name,
273 G_PKG_NAME)
274 THEN
275 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
276 END IF;
277 -- Initialize message list if p_init_msg_list is set to TRUE.
278 IF FND_API.to_Boolean( p_init_msg_list )
279 THEN
280 FND_MSG_PUB.initialize;
281 END IF;
282 -- Initialize API return status to SUCCESS
283 FOR wf_err_data_rec in wf_err_data_csr LOOP
284 BEGIN
285 wf_engine.abortprocess(p_item_type,wf_err_data_rec.item_key);
286 EXCEPTION WHEN OTHERS THEN
287 NULL;
288 END;
289 END LOOP;
290 wf_purge.total(p_item_type,null,p_end_date);
291 EXCEPTION
292 WHEN FND_API.G_EXC_ERROR THEN
293 ROLLBACK TO Purgeworkflow_PVT;
294 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_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 Purgeworkflow_PVT;
300 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_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 Purgeworkflow_PVT;
306 FND_MESSAGE.SET_NAME('IEM','IEM_PURGEWORKFLOW_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 END PurgeWorkflow;
311
312 END IEM_NEXT_GEN_PROCESS_EMAIL_PVT;