[Home] [Help]
PACKAGE BODY: APPS.IEM_EMTA_ADMIN_PVT
Source
1 PACKAGE BODY IEM_EMTA_ADMIN_PVT AS
2 /* $Header: iemvemtb.pls 120.3 2005/08/07 17:33:04 appldev noship $ */
3
4 --
5 --
6 -- Purpose: Mantain EMTA admin related issue.
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 12/05/2004 Created
11 -- Liang Xia 01/10/2004 Added UPDATE_DP_CONFIG_DATA_WRAP for Email Account GUI
12 -- Liang Xia 01/19/2005 Changed UPDATE_DP_CONFIG_DATA_WRAP, if P_PASSWORD is null, means no changes
13 -- Liang Xia 07/08/2005 Changed for FND_VAL: removed PASSWORD column in account tables
14 -- --------- ------ ------------------------------------------
15
16 -- Enter procedure, function bodies as shown below
17 G_PKG_NAME CONSTANT varchar2(30) :='IEM_EMTA_ADMIN_PVT ';
18 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
19 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
20
21 PROCEDURE IS_DLPS_RUNNING (
22 p_api_version_number IN NUMBER,
23 p_init_msg_list IN VARCHAR2 := null,
24 p_commit IN VARCHAR2 := null,
25 p_email_acct_id IN NUMBER,
26 x_running_status OUT NOCOPY VARCHAR2,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2
30
31 ) is
32 l_api_name VARCHAR2(255):='IS_DLPS_RUNNING';
33 l_api_version_number NUMBER:=1.0;
34 l_seq_id NUMBER;
35
36 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
37 l_msg_count NUMBER := 0;
38 l_msg_data VARCHAR2(2000);
39 i INTEGER;
40
41
42 BEGIN
43 -- Standard Start of API savepoint
44 SAVEPOINT IS_DLPS_RUNNING_PVT;
45
46 -- Standard call to check for call compatibility.
47
48 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
49 p_api_version_number,
50 l_api_name,
51 G_PKG_NAME)
52 THEN
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END IF;
55
56
57 -- Initialize message list if p_init_msg_list is set to TRUE.
58 IF FND_API.to_Boolean( p_init_msg_list )
59 THEN
60 FND_MSG_PUB.initialize;
61 END IF;
62
63 -- Initialize API return status to SUCCESS
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 --begins here
67
68 x_running_status := 'N';
69
70 -- Standard Check Of p_commit.
71 IF FND_API.To_Boolean(p_commit) THEN
72 COMMIT WORK;
73 END IF;
74
75
76 -- Standard callto get message count and if count is 1, get message info.
77 FND_MSG_PUB.Count_And_Get
78 ( p_count => x_msg_count,
79 p_data => x_msg_data
80 );
81
82 EXCEPTION
83
84 WHEN FND_API.G_EXC_ERROR THEN
85 ROLLBACK TO IS_DLPS_RUNNING_PVT;
86 x_return_status := FND_API.G_RET_STS_ERROR ;
87 FND_MSG_PUB.Count_And_Get
88
89 ( p_count => x_msg_count,
90 p_data => x_msg_data
91 );
92
93 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
94 ROLLBACK TO IS_DLPS_RUNNING_PVT;
95 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
96 FND_MSG_PUB.Count_And_Get
97 ( p_count => x_msg_count,
98 p_data => x_msg_data
99 );
100
101 WHEN OTHERS THEN
102 ROLLBACK TO IS_DLPS_RUNNING_PVT;
103 x_return_status := FND_API.G_RET_STS_ERROR;
104 IF FND_MSG_PUB.Check_Msg_Level
105 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
106 THEN
107 FND_MSG_PUB.Add_Exc_Msg
108 ( G_PKG_NAME ,
109 l_api_name
110 );
111 END IF;
112
113 FND_MSG_PUB.Count_And_Get
114 ( p_count => x_msg_count,
115 p_data => x_msg_data
116
117 );
118
119 END IS_DLPS_RUNNING;
120
121
122 PROCEDURE UPDATE_DP_CONFIG_DATA_WRAP(
123 p_api_version_number IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := null,
125 p_commit IN VARCHAR2 := null,
126 p_email_acct_id IN NUMBER,
127 p_action IN VARCHAR2,
128 P_ACTIVE_FLAG IN varchar2 := null,
129 P_USER_NAME IN varchar2 := null,
130 P_USER_PASSWORD IN varchar2 := null,
131 P_IN_HOST IN varchar2 := null,
132 P_IN_PORT IN varchar2 := null,
133 x_return_status OUT NOCOPY VARCHAR2,
134 x_msg_count OUT NOCOPY NUMBER,
135 x_msg_data OUT NOCOPY VARCHAR2 )
136 is
137 l_api_name VARCHAR2(255):='IS_DLPS_RUNNING';
138 l_api_version_number NUMBER:=1.0;
139
140 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
141 l_msg_count NUMBER := 0;
142 l_msg_data VARCHAR2(2000);
143
144 l_email_acct_id NUMBER;
145 l_is_acct_updated VARCHAR2(1);
146 l_is_data_changed VARCHAR2(1);
147 l_active_flag VARCHAR2(1);
148
149 --l_action VARCHAR2;
150
151
152 l_USER_NAME varchar2(100);
153 l_USER_PASSWORD varchar2(100);
154 l_IN_HOST varchar2(256);
155 l_IN_PORT varchar2(15);
156
157 l_count NUMBER;
158 errorMessage varchar2(2000);
159 logMessage varchar2(2000);
160
161 IEM_INVALID_DATA_DO_NOTHING EXCEPTION;
162 IEM_UPD_DP_CONFIG_DATA_FAILED EXCEPTION;
163 IEM_ACCOUT_ID_NOT_EXIST EXCEPTION;
164 BEGIN
165 -- Standard Start of API savepoint
166 SAVEPOINT UPDATE_DP_CONFIG_DATA_WRAP_PVT;
167
168 -- Standard call to check for call compatibility.
169
170 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
171 p_api_version_number,
172 l_api_name,
173 G_PKG_NAME)
174 THEN
175 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
176 END IF;
177
178
179 -- Initialize message list if p_init_msg_list is set to TRUE.
180 IF FND_API.to_Boolean( p_init_msg_list )
181 THEN
182 FND_MSG_PUB.initialize;
183 END IF;
184
185 -- Initialize API return status to SUCCESS
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188 --begins here
189
190 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
191 logMessage := 'Input data: [p_email_acct_id=' ||p_email_acct_id
192 ||'][p_action=' || p_action ||'][P_ACTIVE_FLAG=' || P_ACTIVE_FLAG
193 ||'][P_USER_NAME=' || P_USER_NAME --||'][P_USER_PASSWORD =' || P_USER_PASSWORD
194 ||'][P_IN_HOST=' || P_IN_HOST ||'][ P_IN_PORT =' || P_IN_PORT ||']' ;
195 --dbms_output.put_line(logMessage);
196 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
197 end if;
198
199 if ( p_email_acct_id is null ) then
200 raise IEM_INVALID_DATA_DO_NOTHING;
201 end if;
202
203 if ( p_action = 'create' ) then
204
205 if ( p_active_flag is null or ( p_active_flag <> 'Y' and p_active_flag <> 'N') ) then
206 raise IEM_INVALID_DATA_DO_NOTHING;
207 end if;
208
209 l_is_acct_updated := 'Y';
210
211 IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
212 p_api_version_number => P_Api_Version_Number,
213 p_init_msg_list => FND_API.G_FALSE,
214 p_commit => P_Commit,
215 p_email_acct_id => p_email_acct_id,
216 p_active_flag => p_active_flag,
217 p_is_acct_update => l_is_acct_updated,
218 x_return_status => l_return_status,
219 x_msg_count => x_msg_count,
220 x_msg_data => x_msg_data
221 );
222 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
223 raise IEM_UPD_DP_CONFIG_DATA_FAILED;
224 end if;
225
226
227 elsif ( p_action = 'update' ) then
228
229 select count(*) into l_count from iem_mstemail_accounts where email_account_id=p_email_acct_id;
230
231 if ( l_count = 0 ) then
232 raise IEM_ACCOUT_ID_NOT_EXIST;
233 end if;
234
235 if ( p_active_flag is null or ( p_active_flag <> 'Y' and p_active_flag <> 'N') ) then
236 raise IEM_INVALID_DATA_DO_NOTHING;
237 end if;
238
239 --if ( P_USER_NAME is null or P_USER_PASSWORD is null or P_IN_HOST is null or P_IN_PORT is null ) then
240 if ( P_USER_NAME is null or P_IN_HOST is null or P_IN_PORT is null ) then
241 raise IEM_INVALID_DATA_DO_NOTHING;
242 end if;
243
244 l_USER_NAME := RTRIM(LTRIM(P_USER_NAME)) ;
245 --l_USER_PASSWORD := RTRIM(LTRIM(P_USER_PASSWORD));
246 l_IN_HOST := RTRIM(LTRIM(P_IN_HOST));
247 l_IN_PORT := RTRIM(LTRIM(P_IN_PORT));
248
249
250
251 CHECK_IF_ACCOUNT_UPDATED (
252 p_api_version_number => l_api_version_number,
253 p_init_msg_list => FND_API.G_FALSE,
254 p_commit => P_Commit,
255 p_email_account_id => p_email_acct_id,
256 P_ACTIVE_FLAG => P_ACTIVE_FLAG,
257 P_USER_NAME => l_USER_NAME,
258 P_USER_PASSWORD => P_USER_PASSWORD,
259 P_IN_HOST => l_IN_HOST,
260 P_IN_PORT => l_IN_PORT,
261 x_is_data_changed => l_is_data_changed,
262 x_is_acct_updated => l_is_acct_updated,
263 x_return_status => l_return_status,
264 x_msg_count => x_msg_count,
265 x_msg_data => x_msg_data
266 );
267
268 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
269 raise IEM_UPD_DP_CONFIG_DATA_FAILED;
270 end if;
271
272 if ( l_is_data_changed = 'Y' ) then
273
274 IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
275 p_api_version_number => P_Api_Version_Number,
276 p_init_msg_list => FND_API.G_FALSE,
277 p_commit => P_Commit,
278 p_email_acct_id => p_email_acct_id,
279 p_active_flag => p_active_flag,
280 p_is_acct_update => l_is_acct_updated,
281 x_return_status => l_return_status,
282 x_msg_count => x_msg_count,
283 x_msg_data => x_msg_data
284 );
285
286 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
287 raise IEM_UPD_DP_CONFIG_DATA_FAILED;
288 end if;
289
290 end if;
291 elsif ( p_action = 'delete' ) then
292
293 l_active_flag := 'N';
294 l_is_acct_updated := 'N';
295
296 IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
297 p_api_version_number => P_Api_Version_Number,
298 p_init_msg_list => FND_API.G_FALSE,
299 p_commit => P_Commit,
300 p_email_acct_id => p_email_acct_id,
301 p_active_flag => l_active_flag,
302 p_is_acct_update => l_is_acct_updated,
303 x_return_status => l_return_status,
304 x_msg_count => x_msg_count,
305 x_msg_data => x_msg_data
306 );
307
308 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
309 --dbms_output.put_line('ERROR when calling IEM_ENCRYPT_TAGS_PVT.duplicate_tags ');
310 raise IEM_UPD_DP_CONFIG_DATA_FAILED;
311 end if;
312
313 else
314 raise IEM_INVALID_DATA_DO_NOTHING;
315 end if;
316
317
318
319 -- Standard Check Of p_commit.
320 IF FND_API.To_Boolean(p_commit) THEN
321 COMMIT WORK;
322 END IF;
323
324
325 -- Standard callto get message count and if count is 1, get message info.
326 FND_MSG_PUB.Count_And_Get
327 ( p_count => x_msg_count,
328 p_data => x_msg_data
329 );
330
331 EXCEPTION
332 WHEN IEM_INVALID_DATA_DO_NOTHING THEN
333 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
334 x_return_status := FND_API.G_RET_STS_ERROR; --FND_API.G_RET_STS_SUCCESS ;
335
336 FND_MESSAGE.SET_NAME('IEM', 'IEM_INVALID_DATA_DO_NOTHING');
337
338 FND_MSG_PUB.ADD;
339 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
340
341 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
342 logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
343 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
344 end if;
345
346 WHEN IEM_UPD_DP_CONFIG_DATA_FAILED THEN
347 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
348 x_return_status := FND_API.G_RET_STS_ERROR ;
349
350 FND_MESSAGE.SET_NAME('IEM', 'IEM_UPD_DP_CONFIG_DATA_FAILED');
351
352 FND_MSG_PUB.ADD;
353 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
354
355 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
356 logMessage := '[Failed when calling IEM_UPDATE_DP_CONFIG_DATA!]';
357 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
358 end if;
359 WHEN IEM_ACCOUT_ID_NOT_EXIST THEN
360
361 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
362 x_return_status := FND_API.G_RET_STS_ERROR ;
363
364 FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCOUT_ID_NOT_EXIST');
365
366 FND_MSG_PUB.ADD;
367 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
368
369 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
370 logMessage := '[Email Account Id does not exist in IEM_MSTEMAIL_ACCOUNTS!]';
371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
372 end if;
373 WHEN FND_API.G_EXC_ERROR THEN
374 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
375 x_return_status := FND_API.G_RET_STS_ERROR ;
376 FND_MSG_PUB.Count_And_Get
377
378 ( p_count => x_msg_count,
379 p_data => x_msg_data
380 );
381
382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
383 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
385 FND_MSG_PUB.Count_And_Get
386 ( p_count => x_msg_count,
387 p_data => x_msg_data
388 );
389
390 WHEN OTHERS THEN
391 ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
392 x_return_status := FND_API.G_RET_STS_ERROR;
393 IF FND_MSG_PUB.Check_Msg_Level
394 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
395 THEN
396 FND_MSG_PUB.Add_Exc_Msg
397 ( G_PKG_NAME ,
398 l_api_name
399 );
400 END IF;
401
402 FND_MSG_PUB.Count_And_Get
403 ( p_count => x_msg_count,
404 p_data => x_msg_data
405
406 );
407
408 END UPDATE_DP_CONFIG_DATA_WRAP;
409
410
411 PROCEDURE UPDATE_DP_CONFIG_DATA(
412 p_api_version_number IN NUMBER,
413 p_init_msg_list IN VARCHAR2 := null,
414 p_commit IN VARCHAR2 := null,
415 p_email_acct_id IN NUMBER,
416 p_active_flag IN VARCHAR2,
417 p_is_acct_update IN VARCHAR2,
418 x_return_status OUT NOCOPY VARCHAR2,
419 x_msg_count OUT NOCOPY NUMBER,
420 x_msg_data OUT NOCOPY VARCHAR2 )
421 is
422 l_api_name VARCHAR2(255):='IS_DLPS_RUNNING';
423 l_api_version_number NUMBER:=1.0;
424
425 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
426 l_msg_count NUMBER := 0;
427 l_msg_data VARCHAR2(2000);
428
429 l_seq_id NUMBER;
430 l_action VARCHAR2(10);
431 l_update_flag VARCHAR2(1);
432
433 l_has_updated VARCHAR2(1);
434
435 e_nowait EXCEPTION;
436 PRAGMA EXCEPTION_INIT(e_nowait, -54);
437
438 BEGIN
439 -- Standard Start of API savepoint
440 SAVEPOINT UPDATE_DP_CONFIG_DATA_PVT;
441
442 -- Standard call to check for call compatibility.
443
444 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
445 p_api_version_number,
446 l_api_name,
447 G_PKG_NAME)
448 THEN
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452
453 -- Initialize message list if p_init_msg_list is set to TRUE.
454 IF FND_API.to_Boolean( p_init_msg_list )
455 THEN
456 FND_MSG_PUB.initialize;
457 END IF;
458
459 -- Initialize API return status to SUCCESS
460 x_return_status := FND_API.G_RET_STS_SUCCESS;
461
462 --begins here
463 l_has_updated := 'N';
464
465 if p_active_flag = 'Y' then
466 l_action := 'active';
467 elsif p_active_flag = 'N' then
468 l_action := 'inactive';
469 end if;
470
471 l_update_flag := p_is_acct_update;
472
473 begin
474 FOR x IN (
475 select emta_config_param_id, action_type, email_account_id, account_update_flag
476 from iem_emta_config_params where flag='N' and email_account_id=p_email_acct_id for update nowait
477 )
478 LOOP
479
480 if x.account_update_flag = 'Y' then
481 update iem_emta_config_params set action_type = l_action,last_update_date = sysdate
482 where emta_config_param_id = x.emta_config_param_id;
483
484 l_has_updated := 'Y';
485 exit;
486 else
487 update iem_emta_config_params set action_type = l_action, account_update_flag=l_update_flag, last_update_date = sysdate
488 where emta_config_param_id = x.emta_config_param_id;
489
490 l_has_updated := 'Y';
491 exit;
492 end if;
493
494 END LOOP;
495
496 exception
497 when e_nowait then
498
499 null;
500 when others then
501
502 null;
503 end;
504
505 if l_has_updated = 'N' then
506 select IEM_EMTA_CONFIG_PARAMS_S1.nextval into l_seq_id from dual;
507
508
509 INSERT INTO IEM_EMTA_CONFIG_PARAMS
510 (
511 EMTA_CONFIG_PARAM_ID,
512 EMAIL_ACCOUNT_ID,
513 ACTION_TYPE,
514 ACCOUNT_UPDATE_FLAG,
515 FLAG,
516 CREATED_BY,
517 CREATION_DATE,
518 LAST_UPDATED_BY,
519 LAST_UPDATE_DATE,
520 LAST_UPDATE_LOGIN
521 )
522 VALUES
523 (
524 l_seq_id,
525 p_email_acct_id,
526 l_action,
527 l_update_flag,
528 'N',
529 decode(G_created_updated_by,null,-1,G_created_updated_by),
530 sysdate,
531 decode(G_created_updated_by,null,-1,G_created_updated_by),
532 sysdate,
533 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
534 );
535 end if;
536
537 -- Standard Check Of p_commit.
538 IF FND_API.To_Boolean(p_commit) THEN
539 COMMIT WORK;
540 END IF;
541
542
543 -- Standard callto get message count and if count is 1, get message info.
544 FND_MSG_PUB.Count_And_Get
545 ( p_count => x_msg_count,
546 p_data => x_msg_data
547 );
548
549 EXCEPTION
550
551 WHEN FND_API.G_EXC_ERROR THEN
552 ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
553 x_return_status := FND_API.G_RET_STS_ERROR ;
554 FND_MSG_PUB.Count_And_Get
555
556 ( p_count => x_msg_count,
557 p_data => x_msg_data
558 );
559
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 FND_MSG_PUB.Count_And_Get
564 ( p_count => x_msg_count,
565 p_data => x_msg_data
566 );
567
568 WHEN OTHERS THEN
569 ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 IF FND_MSG_PUB.Check_Msg_Level
572 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg
575 ( G_PKG_NAME ,
576 l_api_name
577 );
578 END IF;
579
580 FND_MSG_PUB.Count_And_Get
581 ( p_count => x_msg_count,
582 p_data => x_msg_data
583
584 );
585
586 END UPDATE_DP_CONFIG_DATA;
587
588
589 PROCEDURE GET_ACCOUNT_INFO (
590 p_api_version_number IN NUMBER,
591 p_init_msg_list IN VARCHAR2 := null,
592 p_commit IN VARCHAR2 := null,
593 x_acct_info OUT NOCOPY acct_info_tbl,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2
597 )
598 IS
599 l_api_name varchar2(30):='GET_ACCOUNT_INFO';
600 l_api_version_number number:=1.0;
601
602 l_acct_info acct_info_tbl;
603 l_count number;
604 Y number;
605 account_rec IEM_MSTEMAIL_ACCOUNTS%ROWTYPE;
606
607 e_nowait EXCEPTION;
608 PRAGMA EXCEPTION_INIT(e_nowait, -54);
609
610 BEGIN
611
612 --Standard Savepoint
613 SAVEPOINT GET_ACCOUNT_INFO_pvt;
614
615 -- Standard call to check for call compatibility.
616 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
617 p_api_version_number,
618 l_api_name,
619 G_PKG_NAME)
620 THEN
621 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
622 END IF;
623
624 --Initialize the message list if p_init_msg_list is set to TRUE
625 If FND_API.to_Boolean(p_init_msg_list) THEN
626 FND_MSG_PUB.initialize;
627 END IF;
628
629 --Initialize API status return
630 x_return_status := FND_API.G_RET_STS_SUCCESS;
631
632 --Actual API starts here
633
634 Y := 1;
635
636 for x in (
637 select emta_config_param_id, action_type, email_account_id, account_update_flag
638 from iem_emta_config_params where flag='N' for update nowait
639 )
640 LOOP
641 update IEM_EMTA_CONFIG_PARAMS set FLAG='A', LAST_UPDATE_DATE=SYSDATE
642 where emta_config_param_id=x.emta_config_param_id;
643
644 select count(*) into l_count
645 from iem_mstemail_accounts where email_account_id=x.email_account_id;
646
647 if ( l_count = 0 ) then
648 delete IEM_EMTA_CONFIG_PARAMS where emta_config_param_id=x.emta_config_param_id;
649 else
650
651 select * into account_rec
652 from iem_mstemail_accounts where email_account_id=x.email_account_id;
653
654 l_acct_info(Y).account_id := x.email_account_id;
655 l_acct_info(Y).action := x.action_type;
656 l_acct_info(Y).update_flag := x.account_update_flag;
657 l_acct_info(Y).user_name := account_rec.user_name;
658 --l_acct_info(Y).user_password := account_rec.user_password;
659 l_acct_info(Y).in_host := account_rec.in_host;
660 l_acct_info(Y).in_port := account_rec.in_port;
661
662 Y := Y+1;
663 end if;
664 end LOOP;
665
666 x_acct_info := l_acct_info;
667
668 --Standard check of p_commit
669 IF FND_API.to_Boolean(p_commit) THEN
670 COMMIT WORK;
671 END IF;
672
673
674 EXCEPTION
675 WHEN e_nowait THEN
676 NULL;
677
678 WHEN FND_API.G_EXC_ERROR THEN
679 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
680 x_return_status := FND_API.G_RET_STS_ERROR ;
681 FND_MSG_PUB.Count_And_Get
682 ( p_count => x_msg_count,p_data => x_msg_data);
683
684
685 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
686 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
688 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
689
690
691 WHEN OTHERS THEN
692 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
693 x_return_status := FND_API.G_RET_STS_ERROR;
694 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
695 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
696 END IF;
697
698 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
699
700 END ;
701
702
703 PROCEDURE DELETE_ITEMS (
704 p_api_version_number IN NUMBER,
705 p_init_msg_list IN VARCHAR2 := null,
706 p_commit IN VARCHAR2 := null,
707 x_return_status OUT NOCOPY VARCHAR2,
708 x_msg_count OUT NOCOPY NUMBER,
709 x_msg_data OUT NOCOPY VARCHAR2
710 )
711 IS
712 l_api_name varchar2(30):='GET_ACCOUNT_INFO';
713 l_api_version_number number:=1.0;
714
715 l_acct_info acct_info_tbl;
716 Y number;
717 account_rec IEM_MSTEMAIL_ACCOUNTS%ROWTYPE;
718
719 e_nowait EXCEPTION;
720 PRAGMA EXCEPTION_INIT(e_nowait, -54);
721
722 BEGIN
723
724 --Standard Savepoint
725 SAVEPOINT GET_ACCOUNT_INFO_pvt;
726
727 -- Standard call to check for call compatibility.
728 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
729 p_api_version_number,
730 l_api_name,
731 G_PKG_NAME)
732 THEN
733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
734 END IF;
735
736 --Initialize the message list if p_init_msg_list is set to TRUE
737 If FND_API.to_Boolean(p_init_msg_list) THEN
738 FND_MSG_PUB.initialize;
739 END IF;
740
741 --Initialize API status return
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744 --Actual API starts here
745 delete iem_emta_config_params where flag='A';
746
747 --Standard check of p_commit
748 IF FND_API.to_Boolean(p_commit) THEN
749 COMMIT WORK;
750 END IF;
751
752
753 EXCEPTION
754 WHEN e_nowait THEN
755 NULL;
756
757 WHEN FND_API.G_EXC_ERROR THEN
758 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
759 x_return_status := FND_API.G_RET_STS_ERROR ;
760 FND_MSG_PUB.Count_And_Get
761 ( p_count => x_msg_count,p_data => x_msg_data);
762
763
764 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
765 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
766 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
767 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
768
769
770 WHEN OTHERS THEN
771 ROLLBACK TO GET_ACCOUNT_INFO_pvt;
772 x_return_status := FND_API.G_RET_STS_ERROR;
773 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
774 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
775 END IF;
776
777 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
778
779 END ;
780
781
782 FUNCTION is_data_changed ( p_email_account_id IN number,
783 P_ACTIVE_FLAG IN varchar2,
784 P_USER_NAME IN varchar2,
785 P_USER_PASSWORD IN varchar2,
786 P_IN_HOST IN varchar2,
787 P_IN_PORT IN varchar2,
788 x_is_acct_updated OUT NOCOPY varchar2 )
789 return boolean
790 is
791 l_data_changed boolean;
792 l_active_flag varchar2(1);
793 l_user_name varchar2(100);
794 l_user_pwd varchar2(100);
795 l_encrypt_key varchar2(100);
796 l_in_host varchar2(256);
797 l_in_port varchar2(15);
798 l_decrypted_pwd varchar2(256);
799
800 l_is_acct_updated varchar2(1);
801 IEM_FAILED_DECRYPT_ACCT_PWD EXCEPTION;
802 BEGIN
803 l_data_changed := false;
804 l_is_acct_updated := 'N';
805
806 --select active_flag, user_name, user_password, encrypt_key, in_host, in_port
807 select active_flag, user_name, in_host, in_port
808 into l_active_flag, l_user_name, l_in_host, l_in_port
809 from iem_mstemail_accounts where email_account_id = p_email_account_id ;
810
811 /*
812 IEM_UTILS_PVT.IEM_DecryptPassword(
813 p_api_version_number =>1.0,
814 p_init_msg_list => 'T',
815 p_commit => 'T',
816 p_input_data => l_user_pwd,
817 p_decrypted_key => l_encrypt_key,
818 x_decrypted_data => l_decrypted_pwd ,
819 x_return_status =>l_return_status,
820 x_msg_count => l_msg_count,
821 x_msg_data => l_msg_data);
822
823 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
824 raise IEM_FAILED_DECRYPT_ACCT_PWD;
825 end if;
826 */
827 l_decrypted_pwd := fnd_vault.get('IEM', p_email_account_id );
828
829 if ( l_active_flag <> P_ACTIVE_FLAG ) then
830 l_data_changed := true;
831 end if;
832
833 --fixme
834 if ( l_user_name = P_USER_NAME and l_decrypted_pwd=P_USER_PASSWORD and l_in_host = P_IN_HOST and l_in_port = P_IN_PORT ) then
835 --if ( l_user_name = RTRIM(LTRIM(P_USER_NAME)) and l_user_pwd=RTRIM(LTRIM(P_USER_PASSWORD))
836 -- and l_in_host = RTRIM(LTRIM(P_IN_HOST)) and l_in_port = RTRIM(LTRIM(P_IN_PORT)) ) then
837 null;
838 else
839 l_is_acct_updated := 'Y';
840 l_data_changed := true;
841 end if;
842
843 x_is_acct_updated := l_is_acct_updated;
844
845 return l_data_changed;
846
847 EXCEPTION/*
848 WHEN IEM_FAILED_DECRYPT_ACCT_PWD THEN
849
850 x_is_acct_updated := l_is_acct_updated;
851
852
853 --FND_MESSAGE.SET_NAME('IEM', 'IEM_INVALID_DATA_DO_NOTHING');
854
855 FND_MSG_PUB.ADD;
856 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
857
858 if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP') then
859 logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
860 dbms_output.put_line(logMessage);
861 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
862 end if;
863 */ when others then
864 null;
865
866 return l_data_changed;
867 END is_data_changed;
868
869
870 PROCEDURE CHECK_IF_ACCOUNT_UPDATED(
871 p_api_version_number IN NUMBER,
872 p_init_msg_list IN VARCHAR2 := null,
873 p_commit IN VARCHAR2 := null,
874 p_email_account_id IN number,
875 P_ACTIVE_FLAG IN varchar2,
876 P_USER_NAME IN varchar2,
877 P_USER_PASSWORD IN varchar2,
878 P_IN_HOST IN varchar2,
879 P_IN_PORT IN varchar2,
880 x_is_data_changed OUT NOCOPY varchar2,
881 x_is_acct_updated OUT NOCOPY varchar2,
882 x_return_status OUT NOCOPY VARCHAR2,
883 x_msg_count OUT NOCOPY NUMBER,
884 x_msg_data OUT NOCOPY VARCHAR2 )
885 is
886 l_api_name VARCHAR2(255):='CHECK_IF_ACCOUNT_UPDATED';
887 l_api_version_number NUMBER:=1.0;
888
889 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
890 l_msg_count NUMBER := 0;
891 l_msg_data VARCHAR2(2000);
892
893 l_data_changed varchar2(1);
894 l_active_flag varchar2(1);
895 l_user_name varchar2(100);
896 l_user_pwd varchar2(100);
897 l_encrypt_key varchar2(100);
898 l_in_host varchar2(256);
899 l_in_port varchar2(15);
900 l_decrypted_pwd varchar2(256);
901
902 l_is_acct_updated varchar2(1);
903 IEM_FAILED_DECRYPT_ACCT_PWD EXCEPTION;
904 l_count NUMBER;
905 errorMessage varchar2(2000);
906 logMessage varchar2(2000);
907
908 BEGIN
909 -- Standard Start of API savepoint
910 SAVEPOINT CHECK_IF_ACCOUNT_UPDATED_PVT;
911
912 -- Standard call to check for call compatibility.
913
914 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
915 p_api_version_number,
916 l_api_name,
917 G_PKG_NAME)
918 THEN
919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920 END IF;
921
922
923 -- Initialize message list if p_init_msg_list is set to TRUE.
924 IF FND_API.to_Boolean( p_init_msg_list )
925 THEN
926 FND_MSG_PUB.initialize;
927 END IF;
928
929 -- Initialize API return status to SUCCESS
930 x_return_status := FND_API.G_RET_STS_SUCCESS;
931
932 --begins here
933 x_is_acct_updated := 'N';
934 x_is_data_changed := 'N';
935
936 l_data_changed := 'N';
937 l_is_acct_updated := 'N';
938
939 --select active_flag, user_name, user_password, encrypt_key, in_host, in_port
940 select active_flag, user_name, in_host, in_port
941 into l_active_flag, l_user_name, l_in_host, l_in_port
942 from iem_mstemail_accounts where email_account_id = p_email_account_id ;
943
944 if ( l_active_flag <> P_ACTIVE_FLAG ) then
945 l_data_changed := 'Y';
946 end if;
947
948 if ( P_USER_PASSWORD is null or P_USER_PASSWORD = '') then
949
950 if ( l_user_name = P_USER_NAME and l_in_host = P_IN_HOST and l_in_port = P_IN_PORT ) then
951 null;
952 else
953 l_is_acct_updated := 'Y';
954 l_data_changed := 'Y';
955 end if;
956
957 else
958 /*
959
960 IEM_UTILS_PVT.IEM_DecryptPassword(
961 p_api_version_number =>1.0,
962 p_init_msg_list => 'T',
963 p_commit => 'T',
964 p_input_data => l_user_pwd,
965 p_decrypted_key => l_encrypt_key,
966 x_decrypted_data => l_decrypted_pwd ,
967 x_return_status =>l_return_status,
968 x_msg_count => l_msg_count,
969 x_msg_data => l_msg_data);
970
971 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
972 raise IEM_FAILED_DECRYPT_ACCT_PWD;
973 end if;
974 */
975 l_decrypted_pwd := fnd_vault.get('IEM', p_email_account_id );
976
977 if ( l_user_name = P_USER_NAME and l_decrypted_pwd=P_USER_PASSWORD and l_in_host = P_IN_HOST and l_in_port = P_IN_PORT ) then
978 null;
979 else
980 l_is_acct_updated := 'Y';
981 l_data_changed := 'Y';
982 end if;
983
984 end if;
985
986
987
988 x_is_acct_updated := l_is_acct_updated;
989 x_is_data_changed := l_data_changed;
990
991 -- Standard Check Of p_commit.
992 IF FND_API.To_Boolean(p_commit) THEN
993 COMMIT WORK;
994 END IF;
995
996
997 -- Standard callto get message count and if count is 1, get message info.
998 FND_MSG_PUB.Count_And_Get
999 ( p_count => x_msg_count,
1000 p_data => x_msg_data
1001 );
1002
1003 EXCEPTION
1004 WHEN IEM_FAILED_DECRYPT_ACCT_PWD THEN
1005 ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
1006 x_return_status := FND_API.G_RET_STS_ERROR;
1007
1008 FND_MESSAGE.SET_NAME('IEM', 'IEM_FAILED_DECRYPT_ACCT_PWD');
1009
1010 FND_MSG_PUB.ADD;
1011 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1012
1013 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1014 logMessage := '[Unable to descript password for account_id=' || p_email_account_id ||'!]';
1015 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.CHECK_IF_ACCOUNT_UPDATED', logMessage);
1016 end if;
1017
1018
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
1021 x_return_status := FND_API.G_RET_STS_ERROR ;
1022 FND_MSG_PUB.Count_And_Get
1023
1024 ( p_count => x_msg_count,
1025 p_data => x_msg_data
1026 );
1027
1028 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1029 ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
1030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1031 FND_MSG_PUB.Count_And_Get
1032 ( p_count => x_msg_count,
1033 p_data => x_msg_data
1034 );
1035
1036 WHEN OTHERS THEN
1037 ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
1038 x_return_status := FND_API.G_RET_STS_ERROR;
1039 IF FND_MSG_PUB.Check_Msg_Level
1040 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1041 THEN
1042 FND_MSG_PUB.Add_Exc_Msg
1043 ( G_PKG_NAME ,
1044 l_api_name
1045 );
1046 END IF;
1047
1048 FND_MSG_PUB.Count_And_Get
1049 ( p_count => x_msg_count,
1050 p_data => x_msg_data
1051
1052 );
1053
1054 END CHECK_IF_ACCOUNT_UPDATED;
1055
1056 END;