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