[Home] [Help]
PACKAGE BODY: APPS.IEM_CONFIGURATION_PUB
Source
1 PACKAGE BODY IEM_CONFIGURATION_PUB as
2 /* $Header: iempcfgb.pls 115.3 2002/12/04 01:22:44 chtang noship $*/
3
4 G_PKG_NAME CONSTANT varchar2(30) :='IEM_CONFIGURATION_PUB ';
5
6 PROCEDURE GetConfiguration(ERRBUF VARCHAR2,
7 ERRRET VARCHAR2,
8 p_api_version_number IN NUMBER := 1,
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_commit IN VARCHAR2 := FND_API.G_TRUE
11 ) IS
12 l_api_name VARCHAR2(255):='GetConfiguration';
13 l_api_version_number NUMBER:=1.0;
14 l_call_status BOOLEAN;
15 l_count NUMBER:=0;
16 l_counter NUMBER:=0;
17 l_counter1 NUMBER:=0;
18 l_return_status varchar2(20);
19 l_msg_count number;
20 l_msg_data varchar2(300);
21 l_Error_Message VARCHAR2(2000);
22 l_profile_value varchar2(200);
23 l_account_name varchar(500);
24 l_server_group_rec IEM_SERVER_GROUPS%ROWTYPE;
25 l_db_server_rec IEM_DB_SERVERS%ROWTYPE;
26 l_db_connection_rec IEM_DB_CONNECTIONS%ROWTYPE;
27 l_email_server_rec IEM_EMAIL_SERVERS%ROWTYPE;
28 l_email_account_rec IEM_EMAIL_ACCOUNTS%ROWTYPE;
29 l_classification_rec IEM_CLASSIFICATIONS%ROWTYPE;
30 l_theme_rec IEM_THEMES%ROWTYPE;
31 l_agent_account_rec IEM_AGENT_ACCOUNTS%ROWTYPE;
32 Cursor csr_server_group is select * from iem_server_groups;
33 Cursor csr_db_server is select * from iem_db_servers;
34 Cursor csr_db_connection is select * from iem_db_connections;
35 Cursor csr_email_server is select * from iem_email_servers;
36 Cursor csr_email_account is select * from iem_email_accounts;
37 Cursor csr_classification is select * from iem_classifications;
38 Cursor csr_agent_account is select * from iem_agent_accounts;
39 CURSOR csr_theme( l_classification_id IN NUMBER ) IS
40 select * from iem_themes where classification_id = l_classification_id ;
41
42
43 BEGIN
44 -- Standard Start of API savepoint
45 SAVEPOINT GetConfiguration_PUB;
46 -- Standard call to check for call compatibility.
47 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
48 p_api_version_number,
49 l_api_name,
50 G_PKG_NAME)
51 THEN
52 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
53 END IF;
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55 IF FND_API.to_Boolean( p_init_msg_list )
56 THEN
57 FND_MSG_PUB.initialize;
58 END IF;
59 -- Initialize API return status to SUCCESS
60 l_Error_Message := '--------------eMail Center Configuration Details --------------';
61 fnd_file.put_line(fnd_file.log, l_Error_Message);
62
63 -- Server Group
64 select count(*) into l_count from iem_server_groups;
65
66 l_Error_Message := '*** SERVER GROUP (IEM_SERVER_GROUPS) Total # of rows: ' || l_count || ' *** ';
67 fnd_file.put_line(fnd_file.log, l_Error_Message);
68
69 fnd_file.put_line(fnd_file.log, 'row_id, server_group_id, group_name');
70
71
72 OPEN csr_server_group;
73
74 LOOP
75 FETCH csr_server_group INTO l_server_group_rec;
76 EXIT WHEN csr_server_group%NOTFOUND;
77
78 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_server_group_rec.server_group_id || ', ' || l_server_group_rec.group_name);
79 l_counter := l_counter + 1;
80
81 END LOOP;
82 close csr_server_group;
83
84 -- Database Server
85 select count(*) into l_count from iem_db_servers;
86 l_counter := 0;
87
88 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
89 l_Error_Message := '*** Database Server (IEM_DB_SERVERS) Total # of rows: ' || l_count || ' *** ';
90 fnd_file.put_line(fnd_file.log, l_Error_Message);
91
92 fnd_file.put_line(fnd_file.log, 'row_id, db_server_id, db_name, hostname, port, protocol, sid, service_name, server_group_id, rt_availability, email_node, admin_user');
93
94 OPEN csr_db_server;
95
96 LOOP
97 FETCH csr_db_server INTO l_db_server_rec;
98 EXIT WHEN csr_db_server%NOTFOUND;
99
100 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_db_server_rec.db_server_id || ', ' || l_db_server_rec.db_name || ', ' ||
101 l_db_server_rec.hostname || ', ' || l_db_server_rec.port || ', ' || l_db_server_rec.protocol || ', ' || l_db_server_rec.sid ||
102 ', ' || l_db_server_rec.service_name || ', ' || l_db_server_rec.server_group_id || ', ' || l_db_server_rec.rt_availability ||
103 ', ' || l_db_server_rec.email_node || ', ' || l_db_server_rec.admin_user);
104
105 l_counter := l_counter + 1;
106
107 END LOOP;
108 close csr_db_server;
109
110 -- Database Connection
111 select count(*) into l_count from iem_db_connections;
112 l_counter := 0;
113
114 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
115 l_Error_Message := '*** Database Connection (IEM_DB_CONNECTIONS) Total # of rows: ' || l_count || ' *** ';
116 fnd_file.put_line(fnd_file.log, l_Error_Message);
117
118
119 fnd_file.put_line(fnd_file.log, 'row_id, db_connection_id, db_link, db_username, db_server_id, is_admin');
120
121 OPEN csr_db_connection;
122
123 LOOP
124 FETCH csr_db_connection INTO l_db_connection_rec;
125 EXIT WHEN csr_db_connection%NOTFOUND;
126
127 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_db_connection_rec.db_connection_id || ', ' ||
128 l_db_connection_rec.db_link || ', ' || l_db_connection_rec.db_username || ', ' || l_db_connection_rec.db_server_id || ', ' ||
129 l_db_connection_rec.is_admin);
130
131 l_counter := l_counter + 1;
132
133 END LOOP;
134 close csr_db_connection;
135
136 -- Email Server
137 select count(*) into l_count from iem_email_servers;
138 l_counter := 0;
139
140 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
141 l_Error_Message := '*** EMAIL SERVER (IEM_EMAIL_SERVERS) Total # of rows: ' || l_count || ' *** ';
142 fnd_file.put_line(fnd_file.log, l_Error_Message);
143
144 fnd_file.put_line(fnd_file.log, 'row_id, email_server_id, server_name, dns_name, ip_address, port, server_type_id, rt_availability, server_group_id');
145
146 OPEN csr_email_server;
147
148 LOOP
149 FETCH csr_email_server INTO l_email_server_rec;
150 EXIT WHEN csr_email_server%NOTFOUND;
151
152 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_email_server_rec.email_server_id || ', ' ||
153 l_email_server_rec.server_name || ', ' || l_email_server_rec.dns_name || ', ' || l_email_server_rec.ip_address || ', ' ||
154 l_email_server_rec.port || ', ' || l_email_server_rec.server_type_id || ', ' || l_email_server_rec.rt_availability || ', ' ||
155 l_email_server_rec.server_group_id );
156
157 l_counter := l_counter + 1;
158
159 END LOOP;
160 close csr_email_server;
161
162 -- Email Account
163 select count(*) into l_count from iem_email_accounts;
164 l_counter := 0;
165
166 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
167 l_Error_Message := '*** EMAIL ACCOUNT (IEM_EMAIL_ACCOUNTS) Total # of rows: ' || l_count || ' *** ';
168 fnd_file.put_line(fnd_file.log, l_Error_Message);
169
170 fnd_file.put_line(fnd_file.log, 'row_id, email_account_id, account_name, email_user, domain, db_server_id, server_group_id, acct_language, reply_to_address, from_name, intent_enabled, custom_enabled');
171
172 OPEN csr_email_account;
173
174 LOOP
175 FETCH csr_email_account INTO l_email_account_rec;
176 EXIT WHEN csr_email_account%NOTFOUND;
177
178 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_email_account_rec.email_account_id || ', ' ||
179 l_email_account_rec.account_name || ', ' || l_email_account_rec.email_user || ', ' || l_email_account_rec.domain || ', ' ||
180 l_email_account_rec.db_server_id || ', ' || l_email_account_rec.server_group_id || ', ' || l_email_account_rec.acct_language ||
181 ', ' || l_email_account_rec.reply_to_address || ', ' || l_email_account_rec.from_name || ', ' ||
182 l_email_account_rec.intent_enabled || ', ' || l_email_account_rec.custom_enabled);
183
184 l_counter := l_counter + 1;
185
186 END LOOP;
187 close csr_email_account;
188
189 -- Intent
190 select count(*) into l_count from iem_classifications;
191 l_counter := 0;
192
193 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
194 l_Error_Message := '*** INTENT (IEM_CLASSIFICATIONS) Total # of rows: ' || l_count || ' *** ';
195 fnd_file.put_line(fnd_file.log, l_Error_Message);
196
197 fnd_file.put_line(fnd_file.log, 'row_id, classification_id, account_name, classification, created_by, creation_date, last_updated_by, last_update_date');
198
199 OPEN csr_classification;
200
201 LOOP
202 FETCH csr_classification INTO l_classification_rec;
203 EXIT WHEN csr_classification%NOTFOUND;
204
205 select email_user||'@'||domain as account_name into l_account_name from iem_email_accounts where email_account_id= l_classification_rec.email_account_id;
206
207 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
208 fnd_file.put_line(fnd_file.log, 'INTENT : ' || l_counter || ', ' || l_classification_rec.classification_id || ', ' ||
209 l_account_name || ', ' || l_classification_rec.classification || ', ' ||
210 l_classification_rec.created_by || ', ' || l_classification_rec.creation_date || ', ' ||
211 l_classification_rec.last_updated_by || ', ' || l_classification_rec.last_update_date);
212
213 l_counter := l_counter + 1;
214 l_counter1 := 0;
215
216 -- Keyword
217 select count(*) into l_count from iem_themes where classification_id=l_classification_rec.classification_id;
218
219 l_Error_Message := ' *** KEYWORD (IEM_THEMES) Total # of rows: ' || l_count || ' *** ';
220 fnd_file.put_line(fnd_file.log, l_Error_Message);
221
222 fnd_file.put_line(fnd_file.log, ' row_id, theme_id, theme, score, query_response, created_by, creation_date, last_updated_by, last_update_date');
223
224
225 FOR l_theme_rec IN csr_theme(l_classification_rec.classification_id) LOOP
226
227 fnd_file.put_line(fnd_file.log, ' ' || l_counter1 || ', ' || l_theme_rec.theme_id || ', ' || l_theme_rec.theme ||
228 ', ' || l_theme_rec.score || ', ' || l_theme_rec.query_response || ', ' ||
229 l_theme_rec.created_by || ', ' || l_theme_rec.creation_date || ', ' ||
230 l_theme_rec.last_updated_by || ', ' || l_theme_rec.last_update_date);
231
232 l_counter1 := l_counter1 + 1;
233
234 END LOOP;
235
236 END LOOP;
237 close csr_classification;
238
239 -- Agent Account
240 select count(*) into l_count from iem_agent_accounts;
241 l_counter := 0;
242
243 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
244 l_Error_Message := '*** AGENT ACCOUNT (IEM_AGENT_ACCOUNTS) Total # of rows: ' || l_count || ' *** ';
245 fnd_file.put_line(fnd_file.log, l_Error_Message);
246
247 fnd_file.put_line(fnd_file.log, 'row_id, email_account_id, account_name, email_user, domain, db_server_id, server_group_id, acct_language, reply_to_address, from_name, intent_enabled, custom_enabled');
248
249 OPEN csr_agent_account;
250
251 LOOP
252 FETCH csr_agent_account INTO l_agent_account_rec;
253 EXIT WHEN csr_agent_account%NOTFOUND;
254
255 fnd_file.put_line(fnd_file.log, l_counter || ', ' || l_agent_account_rec.agent_account_id || ', ' ||
256 l_agent_account_rec.email_account_id || ', ' || l_agent_account_rec.account_name || ', ' || l_agent_account_rec.email_user ||
257 ', ' || l_agent_account_rec.domain || ', ' || l_agent_account_rec.reply_to_address || ', ' || l_agent_account_rec.from_address ||
258 ', ' || l_agent_account_rec.resource_id || ', ' || l_agent_account_rec.signature || ', ' || l_agent_account_rec.user_name ||
259 ', ' || l_agent_account_rec.from_name);
260
261 l_counter := l_counter + 1;
262
263 END LOOP;
264 close csr_agent_account;
265
266 -- eMC Profile Values
267 fnd_file.put_line(fnd_file.log, ' '); -- blank lines
268 l_Error_Message := '*** eMail Center Profile Values *** ';
269 fnd_file.put_line(fnd_file.log, l_Error_Message);
270
271 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
272 p_init_msg_list => FND_API.G_FALSE,
273 p_commit => FND_API.G_FALSE,
274 p_profile_name => 'IEM_ACCOUNT_SENDER_NAME',
275 x_profile_value => l_profile_value,
276 x_return_status => l_return_status,
277 x_msg_count => l_msg_count,
278 x_msg_data=> l_msg_data);
279
280 l_Error_Message := 'IEM_ACCOUNT_SENDER_NAME : ' || l_profile_value;
281 fnd_file.put_line(fnd_file.log, l_Error_Message);
282
283 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
284 p_init_msg_list => FND_API.G_FALSE,
285 p_commit => FND_API.G_FALSE,
286 p_profile_name => 'IEM_CACHE_UPDATE_FREQ',
287 x_profile_value => l_profile_value,
288 x_return_status => l_return_status,
289 x_msg_count => l_msg_count,
290 x_msg_data=> l_msg_data);
291
292 l_Error_Message := 'IEM_CACHE_UPDATE_FREQ : ' || l_profile_value;
293 fnd_file.put_line(fnd_file.log, l_Error_Message);
294
295 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
296 p_init_msg_list => FND_API.G_FALSE,
297 p_commit => FND_API.G_FALSE,
298 p_profile_name => 'IEM_DEFAULT_CUSTOMER_ID',
299 x_profile_value => l_profile_value,
300 x_return_status => l_return_status,
301 x_msg_count => l_msg_count,
302 x_msg_data=> l_msg_data);
303
304 l_Error_Message := 'IEM_DEFAULT_CUSTOMER_ID : ' || l_profile_value;
305 fnd_file.put_line(fnd_file.log, l_Error_Message);
306
307 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
308 p_init_msg_list => FND_API.G_FALSE,
309 p_commit => FND_API.G_FALSE,
310 p_profile_name => 'IEM_DEFAULT_CUSTOMER_NUMBER',
311 x_profile_value => l_profile_value,
312 x_return_status => l_return_status,
313 x_msg_count => l_msg_count,
314 x_msg_data=> l_msg_data);
315
316 l_Error_Message := 'IEM_DEFAULT_CUSTOMER_NUMBER : ' || l_profile_value;
317 fnd_file.put_line(fnd_file.log, l_Error_Message);
318
319 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
320 p_init_msg_list => FND_API.G_FALSE,
321 p_commit => FND_API.G_FALSE,
322 p_profile_name => 'IEM_DEFAULT_RESOURCE_NUMBER',
323 x_profile_value => l_profile_value,
324 x_return_status => l_return_status,
325 x_msg_count => l_msg_count,
326 x_msg_data=> l_msg_data);
327
328 l_Error_Message := 'IEM_DEFAULT_RESOURCE_NUMBER : ' || l_profile_value;
329 fnd_file.put_line(fnd_file.log, l_Error_Message);
330
331 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
332 p_init_msg_list => FND_API.G_FALSE,
333 p_commit => FND_API.G_FALSE,
334 p_profile_name => 'IEM_INTENT_RESPONSE_NUM',
335 x_profile_value => l_profile_value,
336 x_return_status => l_return_status,
337 x_msg_count => l_msg_count,
338 x_msg_data=> l_msg_data);
339
340 l_Error_Message := 'IEM_INTENT_RESPONSE_NUM : ' || l_profile_value;
341 fnd_file.put_line(fnd_file.log, l_Error_Message);
342
343 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
344 p_init_msg_list => FND_API.G_FALSE,
345 p_commit => FND_API.G_FALSE,
346 p_profile_name => 'IEM_KNOWLEDGE_BASE',
347 x_profile_value => l_profile_value,
348 x_return_status => l_return_status,
349 x_msg_count => l_msg_count,
350 x_msg_data=> l_msg_data);
351
352 l_Error_Message := 'IEM_KNOWLEDGE_BASE : ' || l_profile_value;
353 fnd_file.put_line(fnd_file.log, l_Error_Message);
354
355 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
356 p_init_msg_list => FND_API.G_FALSE,
357 p_commit => FND_API.G_FALSE,
358 p_profile_name => 'IEM_SRVRPROC_APENABLE',
359 x_profile_value => l_profile_value,
363
360 x_return_status => l_return_status,
361 x_msg_count => l_msg_count,
362 x_msg_data=> l_msg_data);
364 l_Error_Message := 'IEM_SRVRPROC_APENABLE : ' || l_profile_value;
365 fnd_file.put_line(fnd_file.log, l_Error_Message);
366
367 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
368 p_init_msg_list => FND_API.G_FALSE,
369 p_commit => FND_API.G_FALSE,
370 p_profile_name => 'IEM_SRVR_ARES',
371 x_profile_value => l_profile_value,
372 x_return_status => l_return_status,
373 x_msg_count => l_msg_count,
374 x_msg_data=> l_msg_data);
375
376 l_Error_Message := 'IEM_SRVR_ARES : ' || l_profile_value;
377 fnd_file.put_line(fnd_file.log, l_Error_Message);
378
379 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
380 p_init_msg_list => FND_API.G_FALSE,
381 p_commit => FND_API.G_FALSE,
382 p_profile_name => 'IEM_SRVR_AROUTE',
383 x_profile_value => l_profile_value,
384 x_return_status => l_return_status,
385 x_msg_count => l_msg_count,
386 x_msg_data=> l_msg_data);
387
388 l_Error_Message := 'IEM_SRVR_AROUTE : ' || l_profile_value;
389 fnd_file.put_line(fnd_file.log, l_Error_Message);
390
391
392 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
393 p_init_msg_list => FND_API.G_FALSE,
394 p_commit => FND_API.G_FALSE,
395 p_profile_name => 'IEM_SRVR_ASRUPD',
396 x_profile_value => l_profile_value,
397 x_return_status => l_return_status,
398 x_msg_count => l_msg_count,
399 x_msg_data=> l_msg_data);
400
401 l_Error_Message := 'IEM_SRVR_ASRUPD : ' || l_profile_value;
402 fnd_file.put_line(fnd_file.log, l_Error_Message);
403
404 iem_parameters_pvt.select_profile (p_api_version_number =>1.0,
405 p_init_msg_list => FND_API.G_FALSE,
406 p_commit => FND_API.G_FALSE,
407 p_profile_name => 'IEM_SRVR_SRST',
408 x_profile_value => l_profile_value,
409 x_return_status => l_return_status,
410 x_msg_count => l_msg_count,
411 x_msg_data=> l_msg_data);
412
413 l_Error_Message := 'IEM_SRVR_SRST : ' || l_profile_value;
414 fnd_file.put_line(fnd_file.log, l_Error_Message);
415
416
417 EXCEPTION
418 WHEN FND_API.G_EXC_ERROR THEN
419 ROLLBACK TO GetConfiguration_PUB;
420 FND_MESSAGE.SET_NAME('IEM','IEM_COLLECTCONFIG_EXEC_ERROR');
421 l_Error_Message := FND_MESSAGE.GET;
422 fnd_file.put_line(fnd_file.log, l_Error_Message);
423 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
425 ROLLBACK TO GetConfiguration_PUB;
426 FND_MESSAGE.SET_NAME('IEM','IEM_COLLECTCONFIG_UNXPTD_ERR');
427 l_Error_Message := FND_MESSAGE.GET;
428 fnd_file.put_line(fnd_file.log, l_Error_Message);
429 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
430 WHEN OTHERS THEN
431 ROLLBACK TO GetConfiguration_PUB;
432 FND_MESSAGE.SET_NAME('IEM','IEM_COLLECTCONFIG_OTHER_ERR');
433 l_Error_Message := SQLERRM;
434 fnd_file.put_line(fnd_file.log, l_Error_Message);
435 l_call_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_Error_Message);
436
437 END GetConfiguration;
438
439
440 END IEM_CONFIGURATION_PUB ;