[Home] [Help]
PACKAGE BODY: APPS.IEM_CONCURRENT_MDT_PVT
Source
1 PACKAGE BODY IEM_CONCURRENT_MDT_PVT as
2 /* $Header: iempconb.pls 120.1 2010/12/23 09:43:50 sanjrao noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_CONCURRENT_MDT_PVT ';
5
6 PROCEDURE StartProcess(ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY VARCHAR2,
8 p_delay_worker_start_time VARCHAR2,
9 p_schedule_worker_stop_date VARCHAR2,
10 p_period_to_wake_up NUMBER,
11 p_number_of_threads NUMBER,
12 p_number_of_msgs NUMBER)
13 IS
14 l_request_id NUMBER;
15 l_Error_Message VARCHAR2(2000);
16 l_call_status BOOLEAN;
17 l_time_to_sch VARCHAR2(25);
18
19 MAIN_WORKER_NOT_SUBMITTED EXCEPTION;
20 REPEAT_OPTIONS_NOT_SET EXCEPTION;
21 WORKER_NOT_SUBMITTED EXCEPTION;
22 RETRY_NOT_SUBMITTED EXCEPTION;
23 INVALID_HOUR EXCEPTION;
24 INVALID_MINUTE EXCEPTION;
25 --sanjana
26 l_status_count number;
27 l_status VARCHAR2(10);
28
29 i number;
30 req_data varchar2(10);
31 reqid number;
32
33 BEGIN
34
35 SELECT count(*) into l_status_count from IEM_COMP_RT_STATS
36 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
37 if l_status_count = 0 THEN
38 INSERT into IEM_COMP_RT_STATS
39 (
40 COMP_RT_STATS_ID,
41 TYPE,
42 PARAM,
43 VALUE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE)
46 VALUES
47 (-1,
48 'MAILPROC',
49 'RUNTIME STATUS',
50 'T',
51 99999,
52 SYSDATE);
53 END IF;
54 --
55 -- Read the value from REQUEST_DATA. If this is the
56 -- first run of the program, then this value will be null.
57 -- Otherwise, this will be the value that we passed to
58 -- SET_REQ_GLOBALS on the previous run.
59 --
60 req_data := fnd_conc_global.request_data;
61
62 --
63 -- If this is the first run, we'll set i = 1. and update iem_comp_rt_stats
64 -- and set it to T
65 -- Otherwise, we'll set i = request_data + 1, and we'll exit
66 -- if we're done.
67 --
68 if (req_data is not null) then
69 --this means the conc program has been running
70 SELECT value into l_status from iem_comp_rt_stats
71 WHERE type='MAILPROC' and param='RUNTIME STATUS' and rownum = 1;
72 i := to_number(req_data);
73 i := i + 1;
74 --if (i > MAXREQS ) then
75 if (l_status = 'F' ) then
76 errbuf := 'Done!';
77 retcode := 0 ;
78 --delete the record so that next time it we start we insert a value
79 DELETE FROM iem_comp_rt_Stats
80 WHERE TYPE='MAILPROC' and PARAM='RUNTIME STATUS';
81 commit;
82 return;
83 end if;
84 else
85 i := 1;
86 end if;
87
88 fnd_file.put_line(fnd_file.log, 'p_delay_worker_start_time = ' || p_delay_worker_start_time);
89 fnd_file.put_line(fnd_file.log, 'p_schedule_worker_stop_date = ' || p_schedule_worker_stop_date);
90 fnd_file.put_line(fnd_file.log, 'p_period_to_wake_up = ' || to_char(p_period_to_wake_up));
91 fnd_file.put_line(fnd_file.log, 'p_number_of_threads = ' || to_char(p_number_of_threads));
92 fnd_file.put_line(fnd_file.log, 'p_number_of_msgs = ' || to_char(p_number_of_msgs));
93
94 fnd_file.put_line(fnd_file.log, 'Starting Processing');
95
96
97 FOR j in 1..p_number_of_threads loop
98
99 -- Submit the child request. The sub_request parameter
100 -- must be set to 'Y'.
101 l_call_status := fnd_request.set_repeat_options('',p_period_to_wake_up,'MINUTES','END',p_schedule_worker_stop_date);
102 l_request_id := fnd_request.submit_request('IEM', 'IEMMDTWW', 'SUBREQ - Child request #'|| to_char(j),p_delay_worker_start_time,TRUE,1,'F','T','MAILPREPROC','IEM_MAIL','NO_WAIT',p_number_of_msgs);
103
104
105 end loop;
106 Commit work;
107 fnd_file.put_line(fnd_file.log, 'Controller Exited');
108
109 -- Removed Retry portion KBeagle 20-11-02
110
111 /*
112 if (p_schedule_retry = 'Y') then
113
114 l_time_to_sch := to_char(p_hour) || ':' || to_char(p_minutes);
115
116 fnd_file.put_line(fnd_file.log, 'Retry process time ' || l_time_to_sch);
117
118 l_call_status := fnd_request.set_repeat_options(repeat_time => l_time_to_sch);
119
120 if not l_call_status then
121 rollback;
122 raise REPEAT_OPTIONS_NOT_SET;
123 end if;
124
125 fnd_file.put_line(fnd_file.log, 'Retry repeat options set for retry process');
126
127 l_request_id := fnd_request.submit_request('IEM', 'IEMMDTWR', '','',FALSE,1,'F','T','MAILPREPROC','IEM_MAIL');
128
129 if l_request_id = 0 then
130 rollback;
131 raise RETRY_NOT_SUBMITTED;
132 else
133 commit;
134 end if;
135
136 fnd_file.put_line(fnd_file.log, 'Retry folders scheduled. Request id = ' || to_char(l_request_id));
137
138 end if;
139
140 */
141 if l_request_id = 0 then
142 -- If request submission failed, exit with error.
143 errbuf := fnd_message.get;
144 retcode := 2;
145 else
146 -- Here we set the globals to put the program into the
147 -- PAUSED status on exit, and to save the state in request_data.
148 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => to_char(i)) ;
149 errbuf := 'Sub-Request #' || to_char(i) || ' submitted!';
150 retcode := 0 ;
151 --sleep here
152 dbms_lock.sleep(p_period_to_wake_up*60);
153 end if;
154
155 return;
156
157 EXCEPTION
158 WHEN INVALID_HOUR THEN
159 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_HOUR');
160 l_Error_Message := FND_MESSAGE.GET;
161 fnd_file.put_line(fnd_file.log, l_Error_Message);
162 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
163
164 WHEN INVALID_MINUTE THEN
165 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_MINUTE');
166 l_Error_Message := FND_MESSAGE.GET;
167 fnd_file.put_line(fnd_file.log, l_Error_Message);
168 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
169
170 WHEN REPEAT_OPTIONS_NOT_SET THEN
171 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_REPEAT_OPTIONS_NOT_SET');
172 l_Error_Message := FND_MESSAGE.GET;
173 fnd_file.put_line(fnd_file.log, l_Error_Message);
174 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
175
176 WHEN MAIN_WORKER_NOT_SUBMITTED THEN
177 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_MAIN_WORKER_NOT_SUBMITTED');
178 l_Error_Message := FND_MESSAGE.GET;
179 fnd_file.put_line(fnd_file.log, l_Error_Message);
180 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
181
182 WHEN WORKER_NOT_SUBMITTED THEN
183 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_WORKER_NOT_SUBMITTED');
184 l_Error_Message := FND_MESSAGE.GET;
185 fnd_file.put_line(fnd_file.log, l_Error_Message);
186 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
187
188 WHEN RETRY_NOT_SUBMITTED THEN
189 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_RETRY_NOT_SUBMITTED');
190 l_Error_Message := FND_MESSAGE.GET;
191 fnd_file.put_line(fnd_file.log, l_Error_Message);
192 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
193
194 WHEN OTHERS THEN
195 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_UNXP_ERROR');
196 l_Error_Message := FND_MESSAGE.GET;
197 fnd_file.put_line(fnd_file.log, l_Error_Message);
198 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
199 END StartProcess;
200 /*
201
202 PROCEDURE SyncFolder(ERRBUF OUT NOCOPY VARCHAR2,
203 RETCODE OUT NOCOPY VARCHAR2
204 )
205 is
206 i number;
207 l_counter NUMBER:=1;
208 email_account_id_list email_account_id_tbl;
209 l_pass VARCHAR2(15);
210 l_email_user VARCHAR2(30);
211 l_domain VARCHAR2(30);
212 l_db_server_id NUMBER;
213 l_im_link varchar2(200);
214 l_im_link1 varchar2(200);
215 l_stat varchar2(10);
216 l_count number;
217 l_data varchar2(255);
218 l_str VARCHAR2(200);
219 l_ret NUMBER;
220 IM_AUTHENTICATION_FAILED EXCEPTION;
221 IM_CREATEFOLDER_FAILED EXCEPTION;
222 l_resource_id number;
223 l_Error_Message VARCHAR2(2000);
224 l_call_status BOOLEAN;
225 l_user_name varchar2(50);
226 l_folder varchar2(100);
227
228
229 begin
230
231 declare
232 CURSOR r_cur is
233 SELECT email_account_id from iem_email_accounts;
234 begin
235 open r_cur;
236 LOOP
237
238 FETCH r_cur into email_account_id_list(l_counter);
239 EXIT WHEN (r_cur%notfound);
240 l_counter:=l_counter+1;
241
242 END LOOP;
243
244 close r_cur;
245 end;
246 for i in 1..email_account_id_list.count loop
247 SELECT EMAIL_PASSWORD, EMAIL_USER, DOMAIN, DB_SERVER_ID
248 INTO l_pass, l_email_user, l_domain, l_db_server_id
249 FROM IEM_EMAIL_ACCOUNTS
250 WHERE EMAIL_ACCOUNT_ID = email_account_id_list(i);
251
252 IEM_DB_CONNECTIONS_PVT.select_item(
253 p_api_version_number =>1.0,
254 p_db_server_id =>l_db_server_id,
255 p_is_admin =>'P',
256 x_db_link=>l_IM_LINK1,
257 x_return_status =>l_stat,
258 x_msg_count => l_count,
259 x_msg_data => l_data);
260
261 If l_im_link1 is null then
262 l_im_link:=null;
263 else
264 l_im_link:='@'||l_im_link1;
265 end if;
266 l_str:='begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user,:a_domain,:a_password);end; ';
267 EXECUTE IMMEDIATE l_str using OUT NOCOPY l_ret,l_email_user,l_domain,l_pass;
268 IF l_ret <> 0 THEN
269 raise IM_AUTHENTICATION_FAILED;
270
271 END IF;
272
273 declare
274 cursor v_cur is
275 select resource_id
276 from jtf_rs_resource_values where value_type = to_char(email_account_id_list(i));
277 begin
278 open v_cur;
279 LOOP
280
281 FETCH v_cur into l_resource_id;
282 EXIT WHEN (v_cur%notfound);
283
284 select a.user_name into l_user_name
285 from fnd_user a, jtf_rs_resource_extns b
286 where a.user_id = b.user_id and b.resource_id = l_resource_id;
287
288 l_folder := '/'||l_user_name||'/Drafts';
289
290 -- Now we are ready to call im createfolder
291 l_str:='begin :l_ret:=im_api.createfolder'||l_im_link||'(:a_folder);end; ';
292 EXECUTE IMMEDIATE l_str using OUT NOCOPY l_ret,l_folder;
293 IF l_ret <> 0 THEN
294 raise IM_CREATEFOLDER_FAILED;
295
296 END IF;
297
298 END LOOP;
299
300 close v_cur;
301 end;
302
303 end loop;
304
305 EXCEPTION
306
307 WHEN IM_AUTHENTICATION_FAILED THEN
308 FND_MESSAGE.SET_NAME('IEM','IM_AUTHENTICATION_FAILED');
309 l_Error_Message := FND_MESSAGE.GET;
310 fnd_file.put_line(fnd_file.log, l_Error_Message);
311 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
312
313
314 WHEN IM_CREATEFOLDER_FAILED THEN
315 FND_MESSAGE.SET_NAME('IEM','IM_CREATEFOLDER_FAILED');
316 l_Error_Message := FND_MESSAGE.GET;
317 fnd_file.put_line(fnd_file.log, l_Error_Message);
318 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
319
320 end SyncFolder;
321 */
322
323 END IEM_CONCURRENT_MDT_PVT;