[Home] [Help]
PACKAGE BODY: APPS.IEM_ENCRYPT_TAGS_PVT
Source
1 PACKAGE BODY IEM_ENCRYPT_TAGS_PVT AS
2 /* $Header: iemvencb.pls 120.1 2005/08/29 17:38:11 appldev noship $ */
3
4 --
5 --
6 -- Purpose: Mantain Encrypted Tags
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 02/25/2002 Created
11 -- Liang Xia 10/24/2002 Added reset_tag API
12 -- Liang Xia 12/05/2002 Fixed GSCC warning: NOCOPY, no G_MISS...
13 -- Liang Xia 07/22/2004 Added duplicate_tags for reuse tag
14 -- Liang Xia 06/02/2005 Fixed GSCC sql.46 according to bug 4289628
15 -- Liang Xia 08/29/2005 Change Ramdom number generation using fnd_crypto
16 -- --------- ------ ------------------------------------------
17
18 -- Enter procedure, function bodies as shown below
19 G_PKG_NAME CONSTANT varchar2(30) :='IEM_ENCRYPT_TAG_PVT ';
20 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
21 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
22 g_encrypted_id NUMBER := 0;
23
24 PROCEDURE create_item (
25 p_api_version_number IN NUMBER,
26 p_init_msg_list IN VARCHAR2 := null,
27 p_commit IN VARCHAR2 := null,
28 p_agent_id IN number,
29 p_interaction_id IN number,
30 p_email_tag_tbl IN email_tag_tbl,
31 x_encripted_id OUT NOCOPY number,
32 x_token OUT NOCOPY VARCHAR2,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_msg_count OUT NOCOPY NUMBER,
35 x_msg_data OUT NOCOPY VARCHAR2
36 ) is
37 l_api_name VARCHAR2(255):='create_item';
38 l_api_version_number NUMBER:=1.0;
39 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
40 l_msg_count NUMBER := 0;
41 l_msg_data VARCHAR2(2000);
42 l_seq_id NUMBER;
43
44 l_key VARCHAR(256);
45 l_val VARCHAR(256);
46 l_token VARCHAR2(15) := '';
47 l_ram VARCHAR2(256) :='';
48 l_ram_len NUMBER :=0;
49 l_temp NUMBER :=0;
50
51 logMessage varchar2(2000);
52 IEM_AGENT_INTERACTION_ID_NULL EXCEPTION;
53
54
55 BEGIN
56 -- Standard Start of API savepoint
57 SAVEPOINT create_item_PVT;
58
59 -- Standard call to check for call compatibility.
60
61 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
62 p_api_version_number,
63 l_api_name,
64 G_PKG_NAME)
65 THEN
66 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
67 END IF;
68
69 -- Initialize message list if p_init_msg_list is set to TRUE.
70 IF FND_API.to_Boolean( p_init_msg_list )
71 THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74
75
76 -- Initialize API return status to SUCCESS
77 x_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 --begins here
80 if (p_agent_id is NULL or p_interaction_id is NULL ) then
81 raise IEM_AGENT_INTERACTION_ID_NULL;
82 end if;
83
84 --Get random number and shorten it for 5 digits
85 DBMS_RANDOM.INITIALIZE ( 8726527 );
86 --l_ram := TO_CHAR( ABS(DBMS_RANDOM.Random) );
87 select to_char(fnd_crypto.randomnumber) into l_ram from dual;
88
89 l_ram_len := LENGTH( l_ram );
90 if l_ram_len < 5 then
91 l_token := SUBSTR( l_ram, 1, l_ram_len );
92 l_temp := l_ram_len;
93
94 for l_ram_len in l_temp..4 loop
95 l_token := l_token || '0';
96 end loop;
97 else
98 l_token := SUBSTR( l_ram, 1, 5 );
99 end if;
100
101 --DBMS_RANDOM.TERMINATE;
102
103 --get next sequential number
104 SELECT IEM_ENCRYPTED_TAGS_s1.nextval
105 INTO l_seq_id
106 FROM dual;
107
108 g_encrypted_id := l_seq_id;
109
110
111 INSERT INTO IEM_ENCRYPTED_TAGS
112 (
113 ENCRYPTED_ID,
114 MESSAGE_ID,
115 AGENT_ID,
116 INTERACTION_ID,
117 TOKEN,
118 ATTRIBUTE1,
119 ATTRIBUTE2,
120 ATTRIBUTE3,
121 ATTRIBUTE4,
122 ATTRIBUTE5,
123 ATTRIBUTE6,
124 ATTRIBUTE7,
125 ATTRIBUTE8,
126 ATTRIBUTE9,
127 ATTRIBUTE10,
128 ATTRIBUTE11,
129 ATTRIBUTE12,
130 ATTRIBUTE13,
131 ATTRIBUTE14,
132 ATTRIBUTE15,
133 ATTRIBUTE_CATEGORY,
134 CREATED_BY,
135 CREATION_DATE,
136 LAST_UPDATED_BY,
137 LAST_UPDATE_DATE,
138 LAST_UPDATE_LOGIN
139 )
140 VALUES
141 (
142 l_seq_id,
143 null,
144 p_agent_id,
145 p_interaction_id,
146 l_token,
147 NULL,
148 NULL,
149 NULL,
150 NULL,
151 NULL,
152 NULL,
153 NULL,
154 NULL,
155 NULL,
156 NULL,
157 NULL,
158 NULL,
159 NULL,
160 NULL,
161 NULL,
162 NULL,
163 decode(G_created_updated_by,null,-1,G_created_updated_by),
164 sysdate,
165 decode(G_created_updated_by,null,-1,G_created_updated_by),
166 sysdate,
167 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
168 );
169
170 if p_email_tag_tbl.count <> 0 then
171 FOR i in p_email_tag_tbl.FIRST..p_email_tag_tbl.LAST LOOP
172 l_key := p_email_tag_tbl(i).email_tag_key;
173 l_val := p_email_tag_tbl(i).email_tag_value;
174
175 if l_key is not null then
176 IEM_ENCRYPT_TAGS_PVT.create_encrypted_tag_dtls(
177 p_api_version_number =>P_Api_Version_Number,
178 p_init_msg_list => FND_API.G_FALSE,
179 p_commit => P_Commit,
180 p_key => l_key,
181 p_val => l_val,
182 x_return_status =>l_return_status,
183 x_msg_count => l_msg_count,
184 x_msg_data => l_msg_data);
185 else
186 if( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
187 logMessage := '[Miss creating key-val in Encypted tag details table since Key is null.]';
188 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.CREATE_ITEM', logMessage);
189 end if;
190 end if;
191
192 END LOOP;
193 end if;
194
195 x_encripted_id := l_seq_id;
196 x_token := l_token;
197
198 -- Standard Check Of p_commit.
199 IF FND_API.To_Boolean(p_commit) THEN
200 COMMIT WORK;
201 END IF;
202
203 -- Standard callto get message count and if count is 1, get message info.
204 FND_MSG_PUB.Count_And_Get
205 ( p_count => x_msg_count,
206 p_data => x_msg_data
207 );
208 EXCEPTION
209 WHEN IEM_AGENT_INTERACTION_ID_NULL THEN
210 ROLLBACK TO create_item_PVT;
211 x_return_status := FND_API.G_RET_STS_ERROR ;
212 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
213
214 WHEN FND_API.G_EXC_ERROR THEN
215 ROLLBACK TO create_item_PVT;
216 x_return_status := FND_API.G_RET_STS_ERROR ;
217
218 FND_MSG_PUB.Count_And_Get
219
220 ( p_count => x_msg_count,
221 p_data => x_msg_data
222 );
223
224 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
225 ROLLBACK TO create_item_PVT;
226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
227 FND_MSG_PUB.Count_And_Get
228 ( p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231
232 WHEN OTHERS THEN
233 ROLLBACK TO create_item_PVT;
234 x_return_status := FND_API.G_RET_STS_ERROR;
235 IF FND_MSG_PUB.Check_Msg_Level
236 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
237 THEN
238 FND_MSG_PUB.Add_Exc_Msg
239 ( G_PKG_NAME ,
240 l_api_name
241 );
242 END IF;
243
244 FND_MSG_PUB.Count_And_Get
245 ( p_count => x_msg_count,
246 p_data => x_msg_data
247
248 );
249 END create_item;
250
251
252
253 PROCEDURE delete_item_by_msg_id
254 (p_api_version_number IN NUMBER,
255 P_init_msg_list IN VARCHAR2 := null,
256 p_commit IN VARCHAR2 := null,
257 p_message_id IN NUMBER,
258 x_return_status OUT NOCOPY VARCHAR2,
259 x_msg_count OUT NOCOPY NUMBER,
260 x_msg_data OUT NOCOPY VARCHAR2)
261 IS
262 l_api_name varchar2(30):='delete_item_by_msg_id_PVT';
263 l_api_version_number number:=1.0;
264 logMessage varchar2(2000);
265 l_encpt_id number;
266 l_msg_id number;
267 l_debug boolean;
268 IEM_MSG_ID_NOT_FOUND EXCEPTION;
269 IEM_NO_ENCRYPTEID_FOR_MSGID EXCEPTION;
270 BEGIN
271
272 --Standard Savepoint
273 SAVEPOINT delete_item_by_msg_id_PVT;
274
275 -- Standard call to check for call compatibility.
276 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
277 p_api_version_number,
278 l_api_name,
279 G_PKG_NAME)
280 THEN
281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
282 END IF;
283
284 --Initialize the message list if p_init_msg_list is set to TRUE
285 If FND_API.to_Boolean(p_init_msg_list) THEN
286 FND_MSG_PUB.initialize;
287 END IF;
288
289 --Initialize API status return
290 x_return_status := FND_API.G_RET_STS_SUCCESS;
291
292 --Actual API starts here
293 FND_LOG_REPOSITORY.init(null,null);
294
295 l_debug := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
296
297
298 l_msg_id := LTRIM(RTRIM(p_message_id));
299
300 select ENCRYPTED_ID into l_encpt_id from iem_encrypted_tags where message_id = l_msg_id;
301
302 DELETE
303 FROM IEM_ENCRYPTED_TAGS
304 WHERE message_id = l_msg_id;
305
306 if SQL%NOTFOUND then
307 -- dbms_output.put_line('Delete encypted_tag no msg found!');
308 raise IEM_MSG_ID_NOT_FOUND;
309 end if;
310
311 DELETE
312 FROM IEM_ENCRYPTED_TAG_DTLS
313 WHERE ENCRYPTED_ID = l_encpt_id;
314
315 if SQL%NOTFOUND then
316 null;
317 end if;
318
319 if l_debug then
320 logMessage := '[Success deleting: MSG_ID = ' || p_message_id ||' from encrypted tag table! ]';
321 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
322 end if;
323
324 --Standard check of p_commit
325 IF FND_API.to_Boolean(p_commit) THEN
326 COMMIT WORK;
327 END IF;
328
329 FND_MSG_PUB.Count_And_Get
330 ( p_count => x_msg_count,p_data => x_msg_data);
331
332 EXCEPTION
333 WHEN NO_DATA_FOUND THEN
334 ROLLBACK TO delete_item_by_msg_id_PVT;
335 --dbms_output.put_line('IEM_NO_ENCRYPTEID_FOR_MSGID!');
336 x_return_status := FND_API.G_RET_STS_ERROR ;
337 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
338 if l_debug then
339 logMessage := '[Not delete (no encrypted tag found)- trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
340 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
341 end if;
342
343 WHEN IEM_MSG_ID_NOT_FOUND THEN
344 ROLLBACK TO delete_item_by_msg_id_PVT;
345 -- dbms_output.put_line('IEM_MSG_ID_NOT_FOUND!');
346 x_return_status := FND_API.G_RET_STS_ERROR ;
347 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
348
349 if l_debug then
350 logMessage := '[Not delete (MSG_ID not found) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
351 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
352 end if;
353
354 WHEN FND_API.G_EXC_ERROR THEN
355 ROLLBACK TO delete_item_by_msg_id_PVT;
356 x_return_status := FND_API.G_RET_STS_ERROR ;
357 FND_MSG_PUB.Count_And_Get
358 ( p_count => x_msg_count,p_data => x_msg_data);
359
360 if l_debug then
361 logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
362 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
363 end if;
364 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
365 ROLLBACK TO delete_item_by_msg_id_PVT;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
367 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
368
369 if l_debug then
370 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
371 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
372 end if;
373 WHEN OTHERS THEN
374 ROLLBACK TO delete_item_by_msg_id_PVT;
375 --dbms_output.put_line('Other error in delete_item_on_msg_id ' ||SUBSTR (SQLERRM , 1 , 100));
376 x_return_status := FND_API.G_RET_STS_ERROR;
377 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
378 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
379 END IF;
380 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
381
382 if l_debug then
383 logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
385 end if;
386 END delete_item_by_msg_id;
387
388
389 PROCEDURE update_item_on_mess_id (
390 p_api_version_number IN NUMBER,
391 p_init_msg_list IN VARCHAR2 := null,
395 x_return_status OUT NOCOPY VARCHAR2,
392 p_commit IN VARCHAR2 := null,
393 p_encrypted_id IN NUMBER,
394 p_message_id IN NUMBER,
396 x_msg_count OUT NOCOPY NUMBER,
397 x_msg_data OUT NOCOPY VARCHAR2
398 ) is
399 l_api_name VARCHAR2(255):='update_item_on_mess_id';
400 l_api_version_number NUMBER:=1.0;
401 IEM_MSG_ID_NULL EXCEPTION;
402 IEM_ENCRYPTED_ID_NOT_FOUND EXCEPTION;
403 IEM_INVALID_MSG_ID EXCEPTION;
404
405 BEGIN
406 -- Standard Start of API savepoint
407 SAVEPOINT update_item_on_mess_id;
408
409 -- Standard call to check for call compatibility.
410 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
411 p_api_version_number,
412 l_api_name,
413 G_PKG_NAME)
414 THEN
415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
416 END IF;
417
418 -- Initialize message list if p_init_msg_list is set to TRUE.
419 IF FND_API.to_Boolean( p_init_msg_list )
420 THEN
421 FND_MSG_PUB.initialize;
422 END IF;
423
424 -- Initialize API return status to SUCCESS
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426
427 if p_message_id is null then
428 raise IEM_MSG_ID_NULL;
429 end if;
430
431 -- valid msg_id
432 --select(*) into l_count from iem_post_mdts where msg_id = p_message_id;
433
434 --if l_count < 1 then
435 -- raise IEM_INVALID_MSG_ID;
436 --end if;
437
438
439 update IEM_ENCRYPTED_TAGS
440 set
441 message_id=p_message_id,
442 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
443 LAST_UPDATE_DATE = sysdate,
444 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
445 where encrypted_id = p_encrypted_id;
446
447 if SQL%NOTFOUND then
448 --dbms_output.put_line('failed Update encypted_tags table');
449 raise IEM_ENCRYPTED_ID_NOT_FOUND;
450 end if;
451
452 -- Standard Check Of p_commit.
453 IF FND_API.To_Boolean(p_commit) THEN
454 COMMIT WORK;
455 END IF;
456
457 -- Standard callto get message count and if count is 1, get message info.
458 FND_MSG_PUB.Count_And_Get
459 ( p_count => x_msg_count,
460 p_data => x_msg_data
461 );
462 EXCEPTION
463 WHEN IEM_MSG_ID_NULL THEN
464 ROLLBACK TO update_item_on_mess_id;
465 x_return_status := FND_API.G_RET_STS_ERROR ;
466 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
467
468 WHEN IEM_ENCRYPTED_ID_NOT_FOUND THEN
469 --dbms_output.put_line('IEM_ENCRYPTED_ID_NOT_FOUND');
470 ROLLBACK TO update_item_on_mess_id;
471 x_return_status := FND_API.G_RET_STS_ERROR ;
472 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
473
474 WHEN FND_API.G_EXC_ERROR THEN
475 --dbms_output.put_line('FND_API.G_EXC_ERROR');
476 ROLLBACK TO update_item_on_mess_id;
477 x_return_status := FND_API.G_RET_STS_ERROR ;
478 FND_MSG_PUB.Count_And_Get
479 ( p_count => x_msg_count,
480 p_data => x_msg_data
481 );
482 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
483 --dbms_output.put_line('G_EXC_UNEXPECTED_ERROR');
484 ROLLBACK TO update_item_on_mess_id;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
486 FND_MSG_PUB.Count_And_Get
487 ( p_count => x_msg_count,
488 p_data => x_msg_data
489 );
490 WHEN OTHERS THEN
491 --dbms_output.put_line('Exception in update encypted_tag tabel happened ' || SUBSTR (SQLERRM , 1 , 240));
492 ROLLBACK TO update_item_on_mess_id;
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 IF FND_MSG_PUB.Check_Msg_Level
495 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
496 THEN
497 FND_MSG_PUB.Add_Exc_Msg
498 ( G_PKG_NAME ,
499 l_api_name
500 );
501 END IF;
502 FND_MSG_PUB.Count_And_Get
503 ( p_count => x_msg_count ,
504 p_data => x_msg_data
505 );
506
507 END update_item_on_mess_id;
508
509
510
511 PROCEDURE create_encrypted_tag_dtls (
512 p_api_version_number IN NUMBER,
513 p_init_msg_list IN VARCHAR2 := null,
514 p_commit IN VARCHAR2 := null,
515 p_key IN VARCHAR2,
516 p_val IN VARCHAR2,
517 x_return_status OUT NOCOPY VARCHAR2,
518 x_msg_count OUT NOCOPY NUMBER,
519 x_msg_data OUT NOCOPY VARCHAR2
520 ) is
521 l_api_name VARCHAR2(255):='create_encrypted_tag_dtls';
522 l_api_version_number NUMBER:=1.0;
523 l_seq_id NUMBER;
524 l_key VARCHAR2(256) := '';
525 l_val VARCHAR2(256) :='';
526 l_temp NUMBER :=0;
527 l_debug Boolean ;
528 IEM_TAG_KEY_NULL EXCEPTION;
529
530 BEGIN
531 -- Standard Start of API savepoint
535 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
532 SAVEPOINT create_encrypted_tag_dtls_PVT;
533
534 -- Standard call to check for call compatibility.
536 p_api_version_number,
537 l_api_name,
538 G_PKG_NAME)
539 THEN
540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
541 END IF;
542
543 -- Initialize message list if p_init_msg_list is set to TRUE.
544 IF FND_API.to_Boolean( p_init_msg_list )
545 THEN
546 FND_MSG_PUB.initialize;
547 END IF;
548
549
550 -- Initialize API return status to SUCCESS
551 x_return_status := FND_API.G_RET_STS_SUCCESS;
552
553 --begins here
554 if (p_key is NULL) then
555 raise IEM_TAG_KEY_NULL;
556 end if;
557
558 l_key := LTRIM(RTRIM(p_key));
559 l_val := LTRIM(RTRIM(p_val));
560
561 --get next sequential number
562 SELECT IEM_ENCRYPTED_TAG_DTLS_S1.nextval
563 INTO l_seq_id
564 FROM dual;
565
566 INSERT INTO IEM_ENCRYPTED_TAG_DTLS
567 (
568 ENCRYPTED_TAG_DTL_ID,
569 KEY,
570 VALUE,
571 ENCRYPTED_ID,
572 ATTRIBUTE1,
573 ATTRIBUTE2,
574 ATTRIBUTE3,
575 ATTRIBUTE4,
576 ATTRIBUTE5,
577 ATTRIBUTE6,
578 ATTRIBUTE7,
579 ATTRIBUTE8,
580 ATTRIBUTE9,
581 ATTRIBUTE10,
582 ATTRIBUTE11,
583 ATTRIBUTE12,
584 ATTRIBUTE13,
585 ATTRIBUTE14,
586 ATTRIBUTE15,
587 ATTRIBUTE_CATEGORY,
588 CREATED_BY,
589 CREATION_DATE,
590 LAST_UPDATED_BY,
591 LAST_UPDATE_DATE,
592 LAST_UPDATE_LOGIN
593 )
594 VALUES
595 (
596 l_seq_id,
597 l_key,
598 l_val,
599 g_encrypted_id,
600 NULL,
601 NULL,
602 NULL,
603 NULL,
604 NULL,
605 NULL,
606 NULL,
607 NULL,
608 NULL,
609 NULL,
610 NULL,
611 NULL,
612 NULL,
613 NULL,
614 NULL,
615 NULL,
616 decode(G_created_updated_by,null,-1,G_created_updated_by),
617 sysdate,
618 decode(G_created_updated_by,null,-1,G_created_updated_by),
619 sysdate,
620 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
621 );
622
623 -- Standard Check Of p_commit.
624 IF FND_API.To_Boolean(p_commit) THEN
625 COMMIT WORK;
626 END IF;
627
628 -- Standard callto get message count and if count is 1, get message info.
629 FND_MSG_PUB.Count_And_Get
630 ( p_count => x_msg_count,
631 p_data => x_msg_data
632 );
633 EXCEPTION
634 WHEN IEM_TAG_KEY_NULL THEN
635 ROLLBACK TO create_encrypted_tag_dtls_PVT;
636 x_return_status := FND_API.G_RET_STS_ERROR ;
637 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
638
639 WHEN FND_API.G_EXC_ERROR THEN
640 ROLLBACK TO create_encrypted_tag_dtls_PVT;
641 x_return_status := FND_API.G_RET_STS_ERROR ;
642
643 FND_MSG_PUB.Count_And_Get
644
645 ( p_count => x_msg_count,
646 p_data => x_msg_data
647 );
648
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650 ROLLBACK TO create_encrypted_tag_dtls_PVT;
651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
652 FND_MSG_PUB.Count_And_Get
653 ( p_count => x_msg_count,
654 p_data => x_msg_data
655 );
656
657 WHEN OTHERS THEN
658 ROLLBACK TO create_encrypted_tag_dtls_PVT;
659 x_return_status := FND_API.G_RET_STS_ERROR;
660 IF FND_MSG_PUB.Check_Msg_Level
661 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
662 THEN
663 FND_MSG_PUB.Add_Exc_Msg
664 ( G_PKG_NAME ,
665 l_api_name
666 );
667 END IF;
668
669 FND_MSG_PUB.Count_And_Get
670 ( p_count => x_msg_count,
671 p_data => x_msg_data
672
673 );
674 END ;
675
676 PROCEDURE reset_tag
677 (p_api_version_number IN NUMBER,
678 P_init_msg_list IN VARCHAR2 := null,
679 p_commit IN VARCHAR2 := null,
680 p_message_id IN NUMBER,
681 x_return_status OUT NOCOPY VARCHAR2,
682 x_msg_count OUT NOCOPY NUMBER,
683 x_msg_data OUT NOCOPY VARCHAR2)
684 IS
685 l_api_name varchar2(30):='reset_tag_PVT';
686 l_api_version_number number:=1.0;
687 logMessage varchar2(2000);
688 l_encpt_id number;
689 l_msg_id number;
690 l_debug Boolean ;
691 IEM_MSG_ID_NOT_FOUND EXCEPTION;
692 IEM_NO_ENCRYPTEID_FOR_MSGID EXCEPTION;
693 BEGIN
694
695 --Standard Savepoint
696 SAVEPOINT reset_tag_PVT;
697
698 -- Standard call to check for call compatibility.
699 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
700 p_api_version_number,
701 l_api_name,
702 G_PKG_NAME)
703 THEN
704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 If FND_API.to_Boolean(p_init_msg_list) THEN
705 END IF;
706
707 --Initialize the message list if p_init_msg_list is set to TRUE
709 FND_MSG_PUB.initialize;
710 END IF;
711
712 --Initialize API status return
713 x_return_status := FND_API.G_RET_STS_SUCCESS;
714
715 --Actual API starts here
716 FND_LOG_REPOSITORY.init(null,null);
717
718 l_debug := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
719
720
721 l_msg_id := LTRIM(RTRIM(p_message_id));
722
723 update iem_encrypted_tags set message_id = null where message_id = l_msg_id;
724
725 if l_debug then
726 logMessage := '[Success reset: MSG_ID = ' || p_message_id ||' from encrypted tag table! ]';
727 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
728 end if;
729
730 --Standard check of p_commit
731 IF FND_API.to_Boolean(p_commit) THEN
732 COMMIT WORK;
733 END IF;
734
735 FND_MSG_PUB.Count_And_Get
736 ( p_count => x_msg_count,p_data => x_msg_data);
737
738 EXCEPTION
739 WHEN NO_DATA_FOUND THEN
740 ROLLBACK TO reset_tag_PVT;
741 --dbms_output.put_line('IEM_NO_ENCRYPTEID_FOR_MSGID!');
742 x_return_status := FND_API.G_RET_STS_ERROR ;
743 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
744 if l_debug then
745 logMessage := '[Not delete (no encrypted tag found)- trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
746 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
747 end if;
748
749 WHEN FND_API.G_EXC_ERROR THEN
750 ROLLBACK TO reset_tag_PVT;
751 x_return_status := FND_API.G_RET_STS_ERROR ;
752 FND_MSG_PUB.Count_And_Get
753 ( p_count => x_msg_count,p_data => x_msg_data);
754
755 if l_debug then
756 logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
757 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
758 end if;
759 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
760 ROLLBACK TO reset_tag_PVT;
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
762 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
763
764 if l_debug then
765 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
766 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
767 end if;
768 WHEN OTHERS THEN
769 ROLLBACK TO reset_tag_PVT;
770 --dbms_output.put_line('Other error in delete_item_on_msg_id ' ||SUBSTR (SQLERRM , 1 , 100));
771 x_return_status := FND_API.G_RET_STS_ERROR;
772 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
773 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
774 END IF;
775 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
776
777 if l_debug then
778 logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
779 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
780 end if;
781 END ;
782
783 -- Enter further code below as specified in the Package spec.
784 PROCEDURE duplicate_tags
785 (p_api_version_number IN NUMBER,
786 P_init_msg_list IN VARCHAR2 := null,
787 p_commit IN VARCHAR2 := null,
788 p_encrypted_id IN NUMBER,
789 p_message_id IN NUMBER,
790 x_return_status OUT NOCOPY VARCHAR2,
791 x_msg_count OUT NOCOPY NUMBER,
792 x_msg_data OUT NOCOPY VARCHAR2)
793 IS
794 l_api_name varchar2(30):='duplicate_tags_PVT';
795 l_api_version_number number:=1.0;
796 logMessage varchar2(2000);
797 l_encpt_id number;
798 l_msg_id number;
799 l_debug boolean;
800 l_seq_id number;
801 l_seq_dtl_id number;
802
803 l_encypted_rec IEM_ENCRYPTED_TAGS%ROWTYPE;
804 cursor c_tag_dtls (p_encypted_id iem_encrypted_tag_dtls.encrypted_id%type)
805 is
806 select key, value from iem_encrypted_tag_dtls where encrypted_id = p_encypted_id;
807
808
809 IEM_MSG_ID_NOT_FOUND EXCEPTION;
810 IEM_NO_ENCRYPTEID_FOR_MSGID EXCEPTION;
811 BEGIN
812
813 --Standard Savepoint
814 SAVEPOINT delete_item_by_msg_id_PVT;
815
816 -- Standard call to check for call compatibility.
817 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
818 p_api_version_number,
819 l_api_name,
820 G_PKG_NAME)
821 THEN
822 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
823 END IF;
824
825 --Initialize the message list if p_init_msg_list is set to TRUE
826 If FND_API.to_Boolean(p_init_msg_list) THEN
827 FND_MSG_PUB.initialize;
828 END IF;
829
830 --Initialize API status return
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 --Actual API starts here
834 FND_LOG_REPOSITORY.init(null,null);
835
836 l_debug := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ;
837
838 select * into l_encypted_rec from IEM_ENCRYPTED_TAGS where ENCRYPTED_ID=p_encrypted_id;
839
840 SELECT IEM_ENCRYPTED_TAGS_S1.nextval
841 INTO l_seq_id
842 FROM dual;
843
844 --l_encypted_rec.ENCRYPTED_ID := l_seq_id;
845 --l_encypted_rec.MESSAGE_ID := p_message_id;
846 --l_encypted_rec.CREATION_DATE := SYSDATE;
847 --l_encypted_rec.LAST_UPDATE_DATE := SYSDATE;
848
849 insert into IEM_ENCRYPTED_TAGS
850 (
851 ENCRYPTED_ID,
852 MESSAGE_ID,
853 AGENT_ID,
854 INTERACTION_ID,
855 TOKEN,
856 ATTRIBUTE1,
857 ATTRIBUTE2,
858 ATTRIBUTE3,
859 ATTRIBUTE4,
860 ATTRIBUTE5,
861 ATTRIBUTE6,
862 ATTRIBUTE7,
863 ATTRIBUTE8,
864 ATTRIBUTE9,
865 ATTRIBUTE10,
866 ATTRIBUTE11,
867 ATTRIBUTE12,
868 ATTRIBUTE13,
869 ATTRIBUTE14,
870 ATTRIBUTE15,
871 ATTRIBUTE_CATEGORY,
872 CREATED_BY,
873 CREATION_DATE,
874 LAST_UPDATED_BY,
875 LAST_UPDATE_DATE,
876 LAST_UPDATE_LOGIN
877 )
878 values
879 (
880 l_seq_id,
881 p_message_id,
882 l_encypted_rec.agent_id,
883 l_encypted_rec.interaction_id,
884 l_encypted_rec.token,
885 NULL,
886 NULL,
887 NULL,
888 NULL,
889 NULL,
890 NULL,
891 NULL,
892 NULL,
893 NULL,
894 NULL,
895 NULL,
896 NULL,
897 NULL,
898 NULL,
899 NULL,
900 NULL,
901 decode(G_created_updated_by,null,-1,G_created_updated_by),
902 sysdate,
903 decode(G_created_updated_by,null,-1,G_created_updated_by),
904 sysdate,
905 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
906 );
907
908 For v_rec in c_tag_dtls ( p_encrypted_id ) Loop
909 --get next sequential number
910 SELECT IEM_ENCRYPTED_TAG_DTLS_S1.nextval
911 INTO l_seq_dtl_id
912 FROM dual;
913
914 INSERT INTO IEM_ENCRYPTED_TAG_DTLS
915 (
916 ENCRYPTED_TAG_DTL_ID,
917 KEY,
918 VALUE,
919 ENCRYPTED_ID,
920 ATTRIBUTE1,
921 ATTRIBUTE2,
922 ATTRIBUTE3,
923 ATTRIBUTE4,
924 ATTRIBUTE5,
925 ATTRIBUTE6,
926 ATTRIBUTE7,
927 ATTRIBUTE8,
928 ATTRIBUTE9,
929 ATTRIBUTE10,
930 ATTRIBUTE11,
931 ATTRIBUTE12,
932 ATTRIBUTE13,
933 ATTRIBUTE14,
934 ATTRIBUTE15,
935 ATTRIBUTE_CATEGORY,
936 CREATED_BY,
937 CREATION_DATE,
938 LAST_UPDATED_BY,
939 LAST_UPDATE_DATE,
940 LAST_UPDATE_LOGIN
941 )
942 VALUES
943 (
944 l_seq_dtl_id,
945 v_rec.key,
946 v_rec.value,
947 l_seq_id,
948 NULL,
949 NULL,
950 NULL,
951 NULL,
952 NULL,
953 NULL,
954 NULL,
955 NULL,
956 NULL,
957 NULL,
958 NULL,
959 NULL,
960 NULL,
961 NULL,
962 NULL,
963 NULL,
964 decode(G_created_updated_by,null,-1,G_created_updated_by),
965 sysdate,
966 decode(G_created_updated_by,null,-1,G_created_updated_by),
967 sysdate,
968 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
969 );
970
971 end loop;
972
973 --Standard check of p_commit
974 IF FND_API.to_Boolean(p_commit) THEN
975 COMMIT WORK;
976 END IF;
977
978 FND_MSG_PUB.Count_And_Get
979 ( p_count => x_msg_count,p_data => x_msg_data);
980
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 ROLLBACK TO delete_item_by_msg_id_PVT;
984 --dbms_output.put_line('IEM_NO_ENCRYPTEID_FOR_MSGID!');
985 x_return_status := FND_API.G_RET_STS_ERROR ;
986 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
987 if l_debug then
988 logMessage := '[No Data found when duplicate tag records! ]';
989 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DUPLICATE_TAGS', logMessage);
990 end if;
991
992
993 WHEN FND_API.G_EXC_ERROR THEN
994 ROLLBACK TO delete_item_by_msg_id_PVT;
995 x_return_status := FND_API.G_RET_STS_ERROR ;
996 FND_MSG_PUB.Count_And_Get
997 ( p_count => x_msg_count,p_data => x_msg_data);
998
999 if l_debug then
1000 logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
1001 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DUPLICATE_TAGS', logMessage);
1002 end if;
1003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004 ROLLBACK TO delete_item_by_msg_id_PVT;
1005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1006 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
1007
1008 if l_debug then
1009 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
1010 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DUPLICATE_TAGS', logMessage);
1011 end if;
1012 WHEN OTHERS THEN
1013 ROLLBACK TO delete_item_by_msg_id_PVT;
1014 --dbms_output.put_line('Other error in delete_item_on_msg_id ' ||SUBSTR (SQLERRM , 1 , 100));
1015 x_return_status := FND_API.G_RET_STS_ERROR;
1016 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1017 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
1018 END IF;
1019 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
1020
1021 if l_debug then
1022 logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
1023 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DUPLICATE_TAGS', logMessage);
1024 end if;
1025 END duplicate_tags;
1026
1027 END IEM_ENCRYPT_TAGS_PVT; -- Package Body IEM_ENCRYPT_TAGS_PVT