[Home] [Help]
PACKAGE BODY: APPS.IEM_CONCURRENT_PVT
Source
1 PACKAGE BODY IEM_CONCURRENT_PVT as
2 /* $Header: iemvconb.pls 115.7 2002/12/22 01:20:05 sboorela shipped $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_CONCURRENT_PVT ';
5
6 PROCEDURE StartProcess(ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY VARCHAR2,
8 p_period_to_wake_up NUMBER,
9 p_number_of_threads NUMBER,
10 p_number_of_msgs NUMBER,
11 p_schedule_retry VARCHAR2,
12 p_hour NUMBER,
13 p_minutes NUMBER)
14 IS
15 l_request_id NUMBER;
16 l_Error_Message VARCHAR2(2000);
17 l_call_status BOOLEAN;
18 l_time_to_sch VARCHAR2(25);
19
20 MAIN_WORKER_NOT_SUBMITTED EXCEPTION;
21 REPEAT_OPTIONS_NOT_SET EXCEPTION;
22 WORKER_NOT_SUBMITTED EXCEPTION;
23 RETRY_NOT_SUBMITTED EXCEPTION;
24 INVALID_HOUR EXCEPTION;
25 INVALID_MINUTE EXCEPTION;
26 BEGIN
27
28 fnd_file.put_line(fnd_file.log, 'p_period_to_wake_up = ' || to_char(p_period_to_wake_up));
29 fnd_file.put_line(fnd_file.log, 'p_number_of_threads = ' || to_char(p_number_of_threads));
30 fnd_file.put_line(fnd_file.log, 'p_number_of_msgs = ' || to_char(p_number_of_msgs));
31 fnd_file.put_line(fnd_file.log, 'p_schedule_retry = ' || p_schedule_retry);
32 fnd_file.put_line(fnd_file.log, 'p_hour = ' || to_char(p_hour));
33 fnd_file.put_line(fnd_file.log, 'p_minutes = ' || to_char(p_minutes));
34
35 fnd_file.put_line(fnd_file.log, 'Starting Processing');
36
37 if p_schedule_retry = 'Y' then
38 if p_hour not between 0 and 23 then
39 raise INVALID_HOUR;
40 end if;
41
42 if p_minutes not between 0 and 59 then
43 raise INVALID_MINUTE;
44 end if;
45 end if;
46
47 --Submitting the process that remains forever
48 --samir.hans commented out on 8/18/2000
49 /*l_request_id := fnd_request.submit_request('IEM', 'IEMADMWW', '','',FALSE,1,'F','T','MAILPREPROC','IEM_MAIL','FOREVER',p_number_of_msgs);
50
51 fnd_file.put_line(fnd_file.log, 'Main Worker Request Id ' || to_char(l_request_id));
52
53 if l_request_id = 0 then
54 rollback;
55 raise MAIN_WORKER_NOT_SUBMITTED;
56 else
57 commit;
58 end if;*/
59
60 FOR i in 1..p_number_of_threads loop
61
62 l_call_status := fnd_request.set_repeat_options('',p_period_to_wake_up,'MINUTES','END');
63
64 if not l_call_status then
65 rollback;
66 raise REPEAT_OPTIONS_NOT_SET;
67 end if;
68
69 l_request_id := fnd_request.submit_request('IEM', 'IEMADMWW', '','',FALSE,1,'F','T','MAILPREPROC','IEM_MAIL','NO_WAIT',p_number_of_msgs);
70
71 fnd_file.put_line(fnd_file.log, 'Worker number ' || to_char(i) || ' Request Id ' || to_char(l_request_id));
72
73 if l_request_id = 0 then
74 rollback;
75 raise WORKER_NOT_SUBMITTED;
76 else
77 commit;
78 end if;
79
80 end loop;
81
82 if (p_schedule_retry = 'Y') then
83
84 l_time_to_sch := to_char(p_hour) || ':' || to_char(p_minutes);
85
86 fnd_file.put_line(fnd_file.log, 'Retry process time ' || l_time_to_sch);
87
88 l_call_status := fnd_request.set_repeat_options(repeat_time => l_time_to_sch);
89
90 if not l_call_status then
91 rollback;
92 raise REPEAT_OPTIONS_NOT_SET;
93 end if;
94
95 fnd_file.put_line(fnd_file.log, 'Retry repeat options set for retry process');
96
97 l_request_id := fnd_request.submit_request('IEM', 'IEMADMWR', '','',FALSE,1,'F','T','MAILPREPROC','IEM_MAIL');
98
99 if l_request_id = 0 then
100 rollback;
101 raise RETRY_NOT_SUBMITTED;
102 else
103 commit;
104 end if;
105
106 fnd_file.put_line(fnd_file.log, 'Retry folders scheduled. Request id = ' || to_char(l_request_id));
107
108 end if;
109
110
111 Commit work;
112 fnd_file.put_line(fnd_file.log, 'Controller Exited');
113
114 EXCEPTION
115 WHEN INVALID_HOUR THEN
116 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_HOUR');
117 l_Error_Message := FND_MESSAGE.GET;
118 fnd_file.put_line(fnd_file.log, l_Error_Message);
119 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
120
121 WHEN INVALID_MINUTE THEN
122 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_INVALID_MINUTE');
123 l_Error_Message := FND_MESSAGE.GET;
124 fnd_file.put_line(fnd_file.log, l_Error_Message);
125 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
126
127 WHEN REPEAT_OPTIONS_NOT_SET THEN
128 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_REPEAT_OPTIONS_NOT_SET');
129 l_Error_Message := FND_MESSAGE.GET;
130 fnd_file.put_line(fnd_file.log, l_Error_Message);
131 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
132
133 WHEN MAIN_WORKER_NOT_SUBMITTED THEN
134 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_MAIN_WORKER_NOT_SUBMITTED');
135 l_Error_Message := FND_MESSAGE.GET;
136 fnd_file.put_line(fnd_file.log, l_Error_Message);
137 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
138
139 WHEN WORKER_NOT_SUBMITTED THEN
140 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_WORKER_NOT_SUBMITTED');
141 l_Error_Message := FND_MESSAGE.GET;
142 fnd_file.put_line(fnd_file.log, l_Error_Message);
143 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
144
145 WHEN RETRY_NOT_SUBMITTED THEN
146 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_RETRY_NOT_SUBMITTED');
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
151 WHEN OTHERS THEN
152 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_UNXP_ERROR');
153 l_Error_Message := FND_MESSAGE.GET;
154 fnd_file.put_line(fnd_file.log, l_Error_Message);
155 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
156 END StartProcess;
157
158 PROCEDURE SyncFolder(ERRBUF OUT NOCOPY VARCHAR2,
159 RETCODE OUT NOCOPY VARCHAR2
160 )
161 is
162 i number;
163 l_counter NUMBER:=1;
164 email_account_id_list email_account_id_tbl;
165 l_pass VARCHAR2(15);
166 l_email_user VARCHAR2(30);
167 l_domain VARCHAR2(30);
168 l_db_server_id NUMBER;
169 l_im_link varchar2(200);
170 l_im_link1 varchar2(200);
171 l_stat varchar2(10);
172 l_count number;
173 l_data varchar2(255);
174 l_str VARCHAR2(200);
175 l_ret NUMBER;
176 IM_AUTHENTICATION_FAILED EXCEPTION;
177 IM_CREATEFOLDER_FAILED EXCEPTION;
178 l_resource_id number;
179 l_Error_Message VARCHAR2(2000);
180 l_call_status BOOLEAN;
181 l_user_name varchar2(50);
182 l_folder varchar2(100);
183
184
185 begin
186 declare
187 CURSOR r_cur is
188 SELECT email_account_id from iem_email_accounts;
189 begin
190 open r_cur;
191 LOOP
192
193 FETCH r_cur into email_account_id_list(l_counter);
194 EXIT WHEN (r_cur%notfound);
195 l_counter:=l_counter+1;
196
197 END LOOP;
198
199 close r_cur;
200 end;
201 for i in 1..email_account_id_list.count loop
202 SELECT EMAIL_PASSWORD, EMAIL_USER, DOMAIN, DB_SERVER_ID
203 INTO l_pass, l_email_user, l_domain, l_db_server_id
204 FROM IEM_EMAIL_ACCOUNTS
205 WHERE EMAIL_ACCOUNT_ID = email_account_id_list(i);
206
207 IEM_DB_CONNECTIONS_PVT.select_item(
208 p_api_version_number =>1.0,
209 p_db_server_id =>l_db_server_id,
210 p_is_admin =>'P',
211 x_db_link=>l_IM_LINK1,
212 x_return_status =>l_stat,
213 x_msg_count => l_count,
214 x_msg_data => l_data);
215
216 If l_im_link1 is null then
217 l_im_link:=null;
218 else
219 l_im_link:='@'||l_im_link1;
220 end if;
221 l_str:='begin :l_ret:=im_api.authenticate'||l_im_link||'(:a_user,:a_domain,:a_password);end; ';
222 EXECUTE IMMEDIATE l_str using OUT l_ret,l_email_user,l_domain,l_pass;
223 IF l_ret <> 0 THEN
224 raise IM_AUTHENTICATION_FAILED;
225
226 END IF;
227
228 declare
229 cursor v_cur is
230 select resource_id
231 from jtf_rs_resource_values where value_type = to_char(email_account_id_list(i));
232 begin
233 open v_cur;
234 LOOP
235
236 FETCH v_cur into l_resource_id;
237 EXIT WHEN (v_cur%notfound);
238
239 select a.user_name into l_user_name
240 from fnd_user a, jtf_rs_resource_extns b
241 where a.user_id = b.user_id and b.resource_id = l_resource_id;
242
243 l_folder := '/'||l_user_name||'/Drafts';
244
245 -- Now we are ready to call im createfolder
246 l_str:='begin :l_ret:=im_api.createfolder'||l_im_link||'(:a_folder);end; ';
247 EXECUTE IMMEDIATE l_str using OUT l_ret,l_folder;
248 IF l_ret <> 0 THEN
249 raise IM_CREATEFOLDER_FAILED;
250
251 END IF;
252
253 END LOOP;
254
255 close v_cur;
256 end;
257
258 end loop;
259
260 EXCEPTION
261
262 WHEN IM_AUTHENTICATION_FAILED THEN
263 FND_MESSAGE.SET_NAME('IEM','IM_AUTHENTICATION_FAILED');
264 l_Error_Message := FND_MESSAGE.GET;
265 fnd_file.put_line(fnd_file.log, l_Error_Message);
266 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
267
268
269 WHEN IM_CREATEFOLDER_FAILED THEN
270 FND_MESSAGE.SET_NAME('IEM','IM_CREATEFOLDER_FAILED');
271 l_Error_Message := FND_MESSAGE.GET;
272 fnd_file.put_line(fnd_file.log, l_Error_Message);
273 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_Error_Message);
274
275 end SyncFolder;
276
277
278 END IEM_CONCURRENT_PVT;