[Home] [Help]
PACKAGE BODY: APPS.IEM_TAG_KEY_PVT
Source
1 PACKAGE BODY IEM_TAG_KEY_PVT AS
2 /* $Header: iemvtagb.pls 120.0 2005/06/02 14:17:25 appldev noship $ */
3
4 --
5 --
6 -- Purpose: Mantain email tag related operations
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 3/20/2002 Created
11 -- Liang Xia 5/14/2002 added more validation on Key ID
12 -- Liang Xia 12/05/2002 Fixed plsql GSCC warning: NOCOPY, No G_MISS..
13 -- Liang Xia 01/21/2003 Adding additional check to email processing rule when deleting tag
14 -- Liang Xia 12/04/2004 changed to iem_mstemail_accounts for 115.11 schema compliance
15 -- --------- ------ ------------------------------------------
16
17 -- Enter procedure, function bodies as shown below
18 G_PKG_NAME CONSTANT varchar2(30) :='IEM_TAG_KEY_PVT ';
19 G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
20 G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID') ) ;
21
22 PROCEDURE delete_item_batch
23 (p_api_version_number IN NUMBER,
24 P_init_msg_list IN VARCHAR2 := null,
25 p_commit IN VARCHAR2 := null,
26 p_tagKey_ids_tbl IN jtf_varchar2_Table_100,
27 x_return_status OUT NOCOPY VARCHAR2,
28 x_msg_count OUT NOCOPY NUMBER,
29 x_msg_data OUT NOCOPY VARCHAR2)
30 IS
31 i INTEGER;
32 l_api_name varchar2(30):='delete_item_batch';
33 l_api_version_number number:=1.0;
34
35 l_tag_name varchar2(256);
36 l_used_tag_name varchar2(2000);
37 l_route_count number;
38 l_class_count number;
39 l_emailproc_count number;
40
41 IEM_TAG_NOT_DELETED EXCEPTION;
42 BEGIN
43
44 --Standard Savepoint
45 SAVEPOINT delete_item_batch;
46
47 -- Standard call to check for call compatibility.
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
58 --Initialize the message list if p_init_msg_list is set to TRUE
59 If FND_API.to_Boolean(p_init_msg_list) THEN
60 FND_MSG_PUB.initialize;
61 END IF;
62
63 --Initialize API status return
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 --Actual API starts here
67 if ( p_tagKey_ids_tbl.count <> 0 ) then
68
69 FOR i IN p_tagKey_ids_tbl.FIRST..p_tagKey_ids_tbl.LAST LOOP
70 l_route_count := 0;
71 l_class_count := 0;
72
73 select count(*) into l_route_count
74 from iem_tag_keys a, iem_route_rules b
75 where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);
76
77 select count(*) into l_class_count
78 from iem_tag_keys a, iem_route_class_rules b
79 where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);
80
81 select count(*) into l_emailproc_count
82 from iem_tag_keys a, iem_emailproc_rules b
83 where a.tag_key_id=p_tagKey_ids_tbl(i) and upper('IEMS'||a.tag_id) = UPPER(b.key_type_code);
84
85 if (l_route_count > 0 ) or (l_class_count > 0 ) or ( l_emailproc_count > 0 ) then
86 select tag_name into l_tag_name from iem_tag_keys where tag_key_id = p_tagKey_ids_tbl(i);
87 l_used_tag_name := l_used_tag_name||l_tag_name||', ' ;
88 else
89 DELETE
90 FROM IEM_TAG_KEYS
91 WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);
92
93 if SQL%NOTFOUND then
94 raise IEM_TAG_NOT_DELETED;
95 end if;
96
97 DELETE
98 FROM IEM_ACCOUNT_TAG_KEYS
99 WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);
100 end if;
101
102 END LOOP;
103
104 --Delete the accounts, tags associated with this tag
105 --if ( p_tagKey_ids_tbl.count <> 0 ) then
106 /* FOR i IN p_tagKey_ids_tbl.FIRST..p_tagKey_ids_tbl.LAST LOOP
107
108 DELETE
109 FROM IEM_ACCOUNT_TAG_KEYS
110 WHERE TAG_KEY_ID = p_tagKey_ids_tbl(i);
111
112 END LOOP;
113 */
114 end if;
115
116 --add names of un_deleted tags into message
117 if l_used_tag_name is not null then
118 l_used_tag_name := RTRIM(l_used_tag_name, ', ');
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 FND_MESSAGE.SET_NAME('IEM', 'IEM_ADMIN_TAG_NOT_DELETED');
121 FND_MESSAGE.SET_TOKEN('TAG', l_used_tag_name);
122 FND_MSG_PUB.ADD;
123 /* FND_MSG_PUB.Count_And_Get
124 ( p_count => x_msg_count,
125 p_data => x_msg_data
126 );
127 */
128 end if;
129
130 --Standard check of p_commit
131 IF FND_API.to_Boolean(p_commit) THEN
132 COMMIT WORK;
133 END IF;
134 FND_MSG_PUB.Count_And_Get
135 ( p_count => x_msg_count,
136 p_data => x_msg_data
137 );
138
139 EXCEPTION
140
141 WHEN IEM_TAG_NOT_DELETED THEN
142 ROLLBACK TO delete_item_batch;
143 x_return_status := FND_API.G_RET_STS_ERROR;
144 FND_MESSAGE.SET_NAME('IEM', 'IEM_TAG_NOT_DELETED');
145
146 FND_MSG_PUB.ADD;
147 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
148
149 WHEN FND_API.G_EXC_ERROR THEN
150 ROLLBACK TO delete_item_batch;
151 x_return_status := FND_API.G_RET_STS_ERROR ;
152 FND_MSG_PUB.Count_And_Get
153 ( p_count => x_msg_count,p_data => x_msg_data);
154
155
156 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
157 ROLLBACK TO delete_item_batch;
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
159 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
160
161
162 WHEN OTHERS THEN
163 ROLLBACK TO delete_item_batch;
164 x_return_status := FND_API.G_RET_STS_ERROR;
165 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
166 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
167 END IF;
168
169 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
170
171 END delete_item_batch;
172
173
174 PROCEDURE delete_acct_tag_on_acct_ID
175 (p_api_version_number IN NUMBER,
176 P_init_msg_list IN VARCHAR2 := null,
177 p_commit IN VARCHAR2 := null,
178 p_email_acct_id IN iem_mstemail_accounts.email_account_id%type,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2)
182 IS
183 i INTEGER;
184 l_api_name varchar2(30):='delete_acct_tag_on_acct_ID';
185 l_api_version_number number:=1.0;
186
187 l_acct_id number;
188
189 IEM_TAG_NOT_DELETED EXCEPTION;
190 BEGIN
191
192 --Standard Savepoint
193 SAVEPOINT delete_association_on_acct_ID;
194
195 -- Standard call to check for call compatibility.
196 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
197 p_api_version_number,
198 l_api_name,
199 G_PKG_NAME)
200 THEN
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 END IF;
203
204
205
206 --Initialize the message list if p_init_msg_list is set to TRUE
207 If FND_API.to_Boolean(p_init_msg_list) THEN
208 FND_MSG_PUB.initialize;
209 END IF;
210
211 --Initialize API status return
212 x_return_status := FND_API.G_RET_STS_SUCCESS;
213
214 --Actual API starts here
215 l_acct_id := LTRIM(RTRIM(p_email_acct_id));
216 delete from iem_account_tag_keys where email_account_id = l_acct_id;
217
218 --Standard check of p_commit
219 IF FND_API.to_Boolean(p_commit) THEN
220 COMMIT WORK;
221 END IF;
222
223
224 EXCEPTION
225
226 WHEN FND_API.G_EXC_ERROR THEN
227 ROLLBACK TO delete_acct_tag_on_acct_ID;
228 x_return_status := FND_API.G_RET_STS_ERROR ;
229 FND_MSG_PUB.Count_And_Get
230 ( p_count => x_msg_count,p_data => x_msg_data);
231
232
233 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
234 ROLLBACK TO delete_acct_tag_on_acct_ID;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
236 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,p_data => x_msg_data);
237
238
239 WHEN OTHERS THEN
240 ROLLBACK TO delete_acct_tag_on_acct_ID;
241 x_return_status := FND_API.G_RET_STS_ERROR;
242 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
243 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME , l_api_name);
244 END IF;
245
246 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count ,p_data => x_msg_data);
247
248 END delete_acct_tag_on_acct_ID;
249
250 PROCEDURE create_item_tag (
251 p_api_version_number IN NUMBER,
252 p_init_msg_list IN VARCHAR2 := null,
253 p_commit IN VARCHAR2 := null,
254 p_key_id IN VARCHAR2,
255 p_key_name IN VARCHAR2,
256 p_type_type_code IN VARCHAR2,
257 p_value IN VARCHAR2,
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(255):='create_item_tag';
263 l_api_version_number NUMBER:=1.0;
264 l_seq_id NUMBER;
265
266 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
267 l_msg_count NUMBER := 0;
268 l_msg_data VARCHAR2(2000);
269
270 l_name_count NUMBER;
271 l_id_count NUMBER;
272 l_id_count_rt NUMBER;
273 l_id_count_cls NUMBER;
274 l_cursorid NUMBER;
275 l_key_id VARCHAR2(30);
276 l_key_id_temp VARCHAR2(30);
277 l_key_name VARCHAR2(50);
278 l_value VARCHAR2(256);
279 l_error_text varchar2(2000);
280
281 IEM_TAG_DUP_KEY_NAME EXCEPTION;
282 IEM_TAG_DUP_KEY_ID EXCEPTION;
283 l_invalid_query EXCEPTION;
284 l_IEM_INVALID_PROCEDURE EXCEPTION;
285 IEM_ADM_G_MISS_FOR_NOTNULL EXCEPTION;
286 IEM_TAG_NAME_VALUE_KEY_NULL EXCEPTION;
287
288 BEGIN
289 -- Standard Start of API savepoint
290 SAVEPOINT create_item_tag_PVT;
291
292 -- Standard call to check for call compatibility.
293
294 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
295 p_api_version_number,
296 l_api_name,
297 G_PKG_NAME)
298 THEN
299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
300 END IF;
301
302
303 -- Initialize message list if p_init_msg_list is set to TRUE.
304 IF FND_API.to_Boolean( p_init_msg_list )
305 THEN
306 FND_MSG_PUB.initialize;
307 END IF;
308
309
310 -- Initialize API return status to SUCCESS
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312
313 --begins here
314 --Valid no Null passed in for NOT_NULL parameters
315 if ( p_key_name is null or p_value is null or p_key_id is null ) then
316 raise IEM_TAG_NAME_VALUE_KEY_NULL;
317 elsif ( p_key_name=FND_API.G_MISS_CHAR or p_value=FND_API.G_MISS_CHAR or p_key_id=FND_API.G_MISS_CHAR ) then
318 raise IEM_ADM_G_MISS_FOR_NOTNULL;
319 end if;
320
321 l_key_name := LTRIM(RTRIM(p_key_name));
322 l_value := LTRIM(RTRIM(p_value));
323 l_key_id_temp := LTRIM(RTRIM(p_key_id));
324 l_key_id := 'IEMS'||l_key_id_temp;
325
326 --check duplicate value for attribute Name, ID
327 select count(*) into l_name_count from iem_tag_keys where UPPER(tag_name) = UPPER(l_key_name);
328 if l_name_count > 0 then
329 raise IEM_TAG_DUP_KEY_NAME;
330 end if;
331
332 select count(*) into l_id_count from iem_tag_keys where UPPER(tag_id) = UPPER(l_key_id_temp);
333 if l_id_count > 0 then
334 raise IEM_TAG_DUP_KEY_ID;
335 end if;
336
337 SELECT count(*) into l_id_count_rt from FND_LOOKUPS WHERE upper(lookup_code)=upper(l_key_id) and enabled_flag = 'Y' AND NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active, SYSDATE) >= SYSDATE AND lookup_type = 'IEM_KEY_TYPE_CODE';
338 if l_id_count_rt > 0 then
339 raise IEM_TAG_DUP_KEY_ID;
340 end if;
341
342 SELECT count(*) into l_id_count_cls from FND_LOOKUPS
343 WHERE upper(lookup_code)=upper(l_key_id) and enabled_flag = 'Y'
344 AND NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active, SYSDATE) >= SYSDATE AND lookup_type = 'IEM_CLASS_KEY_TYPE_CODE';
345 if l_id_count_cls > 0 then
346 raise IEM_TAG_DUP_KEY_ID;
347 end if;
348
349 /*
350 if (l_key_id='IEMNAGENTID' or l_key_id='IEMNINTERACTIONID' or
351 l_key_id='IEMNBZTSRVSRID' or l_key_id='IEMNCUSTOMERID' or l_key_id='IEMNCONTACTID' or l_key_id='IEMNEMAILACCOUNTID')
352 then
353 raise IEM_TAG_DUP_KEY_ID;
354 end if;
355 */
356
357 -- Valid 'QUERY' type and 'PROCEDURE' type
358 if p_type_type_code = 'QUERY' then
359 IF p_value is NOT NULL THEN
360 BEGIN
361 l_cursorid := DBMS_SQL.OPEN_CURSOR;
362 DBMS_SQL.PARSE(l_cursorid, l_value, DBMS_SQL.V7);
363
364 EXCEPTION
365 WHEN OTHERS THEN
366 fnd_message.set_name ('IEM', 'IEM_TAG_INVALID_QUERY');
367 l_error_text := SUBSTR (SQLERRM , 1 , 240);
368 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
369 FND_MSG_PUB.add;
370 DBMS_SQL.CLOSE_CURSOR(l_cursorid);
371 RAISE l_invalid_query;
372 END;
373 END IF;
374 elsif p_type_type_code = 'PROCEDURE' then
375 IF p_value is NOT NULL THEN
376 IEM_TAG_RUN_PROC_PVT.validProcedure(
377 p_api_version_number => P_Api_Version_Number,
378 p_init_msg_list => FND_API.G_FALSE,
379 p_commit => P_Commit,
380 p_ProcName => l_value,
381 x_return_status => l_return_status,
382 x_msg_count => l_msg_count,
383 x_msg_data => l_msg_data
384 );
385 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
386 raise l_IEM_INVALID_PROCEDURE;
387 end if;
388
389 END IF;
390 end if;
391
392 --get next sequential number for route_id
393 SELECT IEM_TAG_KEYS_s1.nextval
394 INTO l_seq_id
395 FROM dual;
396
397 -- G_ROUTE_ID := l_seq_id;
398
399 INSERT INTO IEM_TAG_KEYS
400 (
401 TAG_KEY_ID,
402 TAG_ID,
403 TAG_NAME,
404 TAG_TYPE_CODE,
405 VALUE,
406 ATTRIBUTE1,
407 ATTRIBUTE2,
408 ATTRIBUTE3,
409 ATTRIBUTE4,
410 ATTRIBUTE5,
411 ATTRIBUTE6,
412 ATTRIBUTE7,
413 ATTRIBUTE8,
414 ATTRIBUTE9,
415 ATTRIBUTE10,
416 ATTRIBUTE11,
420 ATTRIBUTE15,
417 ATTRIBUTE12,
418 ATTRIBUTE13,
419 ATTRIBUTE14,
421 ATTRIBUTE_CATEGORY,
422 CREATED_BY,
423 CREATION_DATE,
424 LAST_UPDATED_BY,
425 LAST_UPDATE_DATE,
426 LAST_UPDATE_LOGIN
427 )
428 VALUES
429 (
430 l_seq_id,
431 l_key_id_temp,
432 l_key_name,
433 p_type_type_code,
434 l_value,
435 NULL,
436 NULL,
437 NULL,
438 NULL,
439 NULL,
440 NULL,
441 NULL,
442 NULL,
443 NULL,
444 NULL,
445 NULL,
446 NULL,
447 NULL,
448 NULL,
449 NULL,
450 NULL,
451 decode(G_created_updated_by,null,-1,G_created_updated_by),
452 sysdate,
453 decode(G_created_updated_by,null,-1,G_created_updated_by),
454 sysdate,
455 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
456 );
457
458 -- Standard Check Of p_commit.
459 IF FND_API.To_Boolean(p_commit) THEN
460 COMMIT WORK;
461 END IF;
462
463 -- Standard callto get message count and if count is 1, get message info.
464 FND_MSG_PUB.Count_And_Get
465 ( p_count => x_msg_count,
466 p_data => x_msg_data
467 );
468
469 EXCEPTION
470 WHEN l_invalid_query THEN
471 ROLLBACK TO create_item_tag_PVT;
472 x_return_status := FND_API.G_RET_STS_ERROR ;
473 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
474
475 WHEN l_IEM_INVALID_PROCEDURE THEN
476 ROLLBACK TO create_item_tag_PVT;
477 x_return_status := FND_API.G_RET_STS_ERROR ;
478 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
479
480 WHEN IEM_TAG_NAME_VALUE_KEY_NULL THEN
481 ROLLBACK TO create_item_tag_PVT;
482 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_NAME_VALUE_KEY_NULL');
483 FND_MSG_PUB.Add;
484 x_return_status := FND_API.G_RET_STS_ERROR ;
485 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
486
487 WHEN IEM_ADM_G_MISS_FOR_NOTNULL THEN
488 ROLLBACK TO create_item_tag_PVT;
489 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_G_MISS_FOR_NOTNULL');
490 FND_MSG_PUB.Add;
491 x_return_status := FND_API.G_RET_STS_ERROR ;
492 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
493
494 WHEN IEM_TAG_DUP_KEY_NAME THEN
495 ROLLBACK TO create_item_tag_PVT;
496 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_DUP_KEY_NAME');
497 FND_MSG_PUB.Add;
498 x_return_status := FND_API.G_RET_STS_ERROR ;
499 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
500
501 WHEN IEM_TAG_DUP_KEY_ID THEN
502 ROLLBACK TO create_item_tag_PVT;
503 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_DUP_KEY_ID');
504 FND_MSG_PUB.Add;
505 x_return_status := FND_API.G_RET_STS_ERROR ;
506 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
507
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO create_item_tag_PVT;
510 x_return_status := FND_API.G_RET_STS_ERROR ;
511 FND_MSG_PUB.Count_And_Get
512
513 ( p_count => x_msg_count,
514 p_data => x_msg_data
515 );
516
517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
518 ROLLBACK TO create_item_tag_PVT;
519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
520 FND_MSG_PUB.Count_And_Get
521 ( p_count => x_msg_count,
522 p_data => x_msg_data
523 );
524
525 WHEN OTHERS THEN
526 ROLLBACK TO create_item_tag_PVT;
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 IF FND_MSG_PUB.Check_Msg_Level
529 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
530 THEN
531 FND_MSG_PUB.Add_Exc_Msg
532 ( G_PKG_NAME ,
533 l_api_name
534 );
535 END IF;
536
537 FND_MSG_PUB.Count_And_Get
538 ( p_count => x_msg_count,
539 p_data => x_msg_data
540
541 );
542
543 END create_item_tag;
544
545 --transfer string containing elements seperated by ; to table
546 FUNCTION varChar_to_table ( inString IN VARCHAR2 )
547 return key_tbl_type
548 is
549 l_indx number:=0;
550 l_temp varchar2(200);
551 l_rem varchar2(2000);
552 l_table key_tbl_type;
553 i BINARY_INTEGER :=1;
554 BEGIN
555 l_rem := inString ;
556
557 loop
558 l_indx := INSTR(l_rem, ';');
559 if (l_indx <> 0)then
560 l_temp := SUBSTR( l_rem, 1, l_indx-1 );
561 l_rem := SUBSTR( l_rem, l_indx+1);
562 l_table(i) := l_temp;
563 i := i + 1;
564 else
565 exit;
566 end if;
567 end loop;
568
569 return l_table;
570
571 END varChar_to_table;
572
573
574
575
576 PROCEDURE create_item_account_tags (
577 p_api_version_number IN NUMBER,
578 p_init_msg_list IN VARCHAR2 := null,
582 x_return_status OUT NOCOPY VARCHAR2,
579 p_commit IN VARCHAR2 := null,
580 p_email_account_id IN NUMBER,
581 p_tag_key_id IN NUMBER,
583 x_msg_count OUT NOCOPY NUMBER,
584 x_msg_data OUT NOCOPY VARCHAR2
585 ) is
586 l_api_name VARCHAR2(255):='create_item_account_tags';
587 l_api_version_number NUMBER:=1.0;
588 l_seq_id number;
589
590 l_count number;
591 IEM_TAG_KEY_ID_NOT_EXIST EXCEPTION;
592 IEM_TAG_ACCT_ID_NOT_EXIST EXCEPTION;
593
594 BEGIN
595
596 -- Standard Start of API savepoint
597 SAVEPOINT create_item_account_tags_PVT;
598
599 -- Standard call to check for call compatibility.
600 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
601 p_api_version_number,
602 l_api_name,
603 G_PKG_NAME)
604 THEN
605 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
606 END IF;
607
608 -- Initialize message list if p_init_msg_list is set to TRUE.
609 IF FND_API.to_Boolean( p_init_msg_list ) THEN
610 FND_MSG_PUB.initialize;
611 END IF;
612
613 -- Initialize API return status to SUCCESS
614 x_return_status := FND_API.G_RET_STS_SUCCESS;
615
616 --valid tag_key_id
617 select count(*) into l_count from iem_tag_keys where tag_key_id = p_tag_key_id;
618 if l_count < 1 then
619 raise IEM_TAG_KEY_ID_NOT_EXIST;
620 end if;
621
622 --valid account_id
623 select count(*) into l_count from iem_mstemail_accounts where email_account_id = p_email_account_id;
624 if l_count < 1 then
625 raise IEM_TAG_ACCT_ID_NOT_EXIST;
626 end if;
627
628 --actual API begins here
629 SELECT IEM_ACCOUNT_TAG_KEYS_s1.nextval
630 INTO l_seq_id
631 FROM dual;
632
633 INSERT INTO IEM_ACCOUNT_TAG_KEYS
634 (
635 ACCOUNT_TAG_KEY_ID,
636 EMAIL_ACCOUNT_ID,
637 TAG_KEY_ID,
638 ATTRIBUTE1,
639 ATTRIBUTE2,
640 ATTRIBUTE3,
641 ATTRIBUTE4,
642 ATTRIBUTE5,
643 ATTRIBUTE6,
644 ATTRIBUTE7,
645 ATTRIBUTE8,
646 ATTRIBUTE9,
647 ATTRIBUTE10,
648 ATTRIBUTE11,
649 ATTRIBUTE12,
650 ATTRIBUTE13,
651 ATTRIBUTE14,
652 ATTRIBUTE15,
653 ATTRIBUTE_CATEGORY,
654 CREATED_BY,
655 CREATION_DATE,
656 LAST_UPDATED_BY,
657 LAST_UPDATE_DATE,
658 LAST_UPDATE_LOGIN
659 )
660 VALUES
661 (
662 l_seq_id,
663 p_email_account_id,
664 p_tag_key_id,
665 NULL,
666 NULL,
667 NULL,
668 NULL,
669 NULL,
670 NULL,
671 NULL,
672 NULL,
673 NULL,
674 NULL,
675 NULL,
676 NULL,
677 NULL,
678 NULL,
679 NULL,
680 NULL,
681 decode(G_created_updated_by,null,-1,G_created_updated_by),
682 sysdate,
683 decode(G_created_updated_by,null,-1,G_created_updated_by),
684 sysdate,
685 decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
686 );
687
688 -- Standard Check Of p_commit
689 IF FND_API.To_Boolean(p_commit) THEN
690 COMMIT WORK;
691 END IF;
692
693 -- Standard callto get message count and if count is 1, get message info.
694 FND_MSG_PUB.Count_And_Get
695 ( p_count => x_msg_count,
696 p_data => x_msg_data
697 );
698
699 EXCEPTION
700 WHEN IEM_TAG_KEY_ID_NOT_EXIST THEN
701 ROLLBACK TO create_item_account_tags_PVT;
702 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_KEY_ID_NOT_EXIST');
703 FND_MSG_PUB.Add;
704 x_return_status := FND_API.G_RET_STS_ERROR ;
705 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
706
707 WHEN IEM_TAG_ACCT_ID_NOT_EXIST THEN
708 ROLLBACK TO create_item_account_tags_PVT;
709 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_ACCT_ID_NOT_EXIST');
710 FND_MSG_PUB.Add;
711 x_return_status := FND_API.G_RET_STS_ERROR ;
712 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
713
714 WHEN FND_API.G_EXC_ERROR THEN
715 ROLLBACK TO create_item_account_tags_PVT;
716 x_return_status := FND_API.G_RET_STS_ERROR ;
717 FND_MSG_PUB.Count_And_Get
718 ( p_count => x_msg_count,
719 p_data => x_msg_data
720 );
721
722 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
723 ROLLBACK TO create_item_account_tags_PVT;
724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
725 FND_MSG_PUB.Count_And_Get
726 ( p_count => x_msg_count,
727 p_data => x_msg_data
728 );
729
730 WHEN OTHERS THEN
731 ROLLBACK TO create_item_account_tags_PVT;
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 IF FND_MSG_PUB.Check_Msg_Level
734 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
735 THEN
736 FND_MSG_PUB.Add_Exc_Msg
737 ( G_PKG_NAME,
738 l_api_name
739 );
740 END IF;
741 FND_MSG_PUB.Count_And_Get
745
742 ( p_count => x_msg_count,
743 p_data => x_msg_data
744 );
746 END create_item_account_tags;
747
748
749 PROCEDURE update_acct_tag_wrap (p_api_version_number IN NUMBER,
750 p_init_msg_list IN VARCHAR2 := null,
751 p_commit IN VARCHAR2 := null,
752 p_account_id IN NUMBER,
753 p_in_key_id IN VARCHAR2:= null,
754 p_out_key_id IN VARCHAR2 := null,
755 x_return_status OUT NOCOPY VARCHAR2,
756 x_msg_count OUT NOCOPY NUMBER,
757 x_msg_data OUT NOCOPY VARCHAR2 )is
758
759 l_api_name VARCHAR2(255):='update_acct_tag_wrap';
760 l_api_version_number NUMBER:=1.0;
761
762 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
763 l_msg_count NUMBER := 0;
764 l_msg_data VARCHAR2(2000);
765
766 IEM_ACCT_TAG_NOT_UPD EXCEPTION;
767 IEM_NO_RULE_UPDATE EXCEPTION;
768
769 l_in_tab key_tbl_type ;
770 l_out_tab key_tbl_type ;
771 l_count number;
772 l_tag_key_id iem_tag_keys.tag_key_id%type :=0;
773
774 l_temp varchar2(256);
775 BEGIN
776 -- Standard Start of API savepoint
777 SAVEPOINT update_acct_tag_wrap;
778
779 -- Standard call to check for call compatibility.
780 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
781 p_api_version_number,
782 l_api_name,
783 G_PKG_NAME)
784 THEN
785 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
786
787 END IF;
788
789 -- Initialize message list if p_init_msg_list is set to TRUE.
790 IF FND_API.to_Boolean( p_init_msg_list )
791 THEN
792 FND_MSG_PUB.initialize;
793 END IF;
794
795 -- Initialize API return status to SUCCESS
796 x_return_status := FND_API.G_RET_STS_SUCCESS;
797
798 --API Body
799 l_in_tab := IEM_TAG_KEY_PVT.varChar_to_table(p_in_key_id);
800 l_out_tab := IEM_TAG_KEY_PVT.varChar_to_table(p_out_key_id);
801
802 --delete association based on OUT list
803 for i in 1..l_out_tab.count() loop
804 delete from iem_account_tag_keys a where a.email_account_id=p_account_id and a.tag_key_id =
805 (select b.tag_key_id from iem_tag_keys b where UPPER(b.tag_id) = UPPER(l_out_tab(i)) );
806 end loop;
807
808 --add association based on IN list
809 for j in 1..l_in_tab.count() loop
810 select count(*) into l_count from iem_account_tag_keys a, iem_tag_keys b
811 where a.email_account_id=p_account_id and a.tag_key_id=b.tag_key_id and b.tag_id=l_in_tab(j);
812
813 if l_count=0 then
814 select tag_key_id into l_tag_key_id from iem_tag_keys where UPPER(tag_id) = UPPER(l_in_tab(j));
815
816 create_item_account_tags (
817 p_api_version_number => l_api_version_number,
818 p_init_msg_list => FND_API.G_FALSE,
819 p_commit => FND_API.G_FALSE,
820 p_email_account_id => p_account_id,
821 p_tag_key_id => l_tag_key_id,
822 x_return_status => l_return_status,
823 x_msg_count => l_msg_count,
824 x_msg_data => l_msg_data);
825
826 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
827 raise IEM_ACCT_TAG_NOT_UPD;
828 end if;
829
830 end if;
831
832 end loop;
833
834 -- Standard Check Of p_commit.
835 IF FND_API.To_Boolean(p_commit) THEN
836 COMMIT WORK;
837 END IF;
838
839
840 EXCEPTION
841 WHEN NO_DATA_FOUND THEN
842 ROLLBACK TO update_acct_tag_wrap;
843 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_TAG_NOT_EXIST');
844 FND_MSG_PUB.Add;
845 x_return_status := FND_API.G_RET_STS_ERROR ;
846 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
847
848 WHEN IEM_ACCT_TAG_NOT_UPD THEN
849 ROLLBACK TO update_acct_tag_wrap;
850 FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_TAG_NOT_UPD');
851 FND_MSG_PUB.Add;
852 x_return_status := FND_API.G_RET_STS_ERROR ;
853 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
854
855 WHEN FND_API.G_EXC_ERROR THEN
856 ROLLBACK TO update_acct_tag_wrap;
857 x_return_status := FND_API.G_RET_STS_ERROR ;
858 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count,p_data => x_msg_data);
859
860
861 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
862 ROLLBACK TO update_acct_tag_wrap;
863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
864 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
865
866
867 WHEN OTHERS THEN
868 ROLLBACK TO update_acct_tag_wrap;
869 x_return_status := FND_API.G_RET_STS_ERROR;
873
870 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
871 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME , l_api_name);
872 END IF;
874
875 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count, p_data => x_msg_data );
876
877 END update_acct_tag_wrap;
878
879
880 PROCEDURE update_item_tag_key (
881 p_api_version_number IN NUMBER,
882 p_init_msg_list IN VARCHAR2 := null,
883 p_commit IN VARCHAR2 := null,
884 p_tag_key_id IN NUMBER,
885 p_key_id IN VARCHAR2:= null,
886 p_key_name IN VARCHAR2:= null,
887 p_type_type_code IN VARCHAR2:= null,
888 p_value IN VARCHAR2:= null,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2
892 ) is
893 l_api_name VARCHAR2(255):='update_item_tag_key';
894 l_api_version_number NUMBER:=1.0;
895 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
896 l_msg_count NUMBER := 0;
897 l_msg_data VARCHAR2(2000);
898
899 l_name_count NUMBER;
900 l_id_count NUMBER;
901 l_cursorid NUMBER;
902 l_error_text varchar2(2000);
903
904 IEM_TAG_DUP_KEY_NAME EXCEPTION;
905 IEM_TAG_DUP_KEY_ID EXCEPTION;
906 l_invalid_query EXCEPTION;
907 l_IEM_INVALID_PROCEDURE EXCEPTION;
908 IEM_ADM_G_MISS_FOR_NOTNULL EXCEPTION;
909 BEGIN
910
911 -- Standard Start of API savepoint
912 SAVEPOINT update_item_tag_key;
913
914 -- Standard call to check for call compatibility.
915 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
916 p_api_version_number,
917 l_api_name,
918 G_PKG_NAME)
919 THEN
920 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
921 END IF;
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 -- Valid g_miss
933 if ( p_key_id=FND_API.G_MISS_CHAR or p_key_name=FND_API.G_MISS_CHAR
934 or p_type_type_code=FND_API.G_MISS_CHAR or p_value=FND_API.G_MISS_CHAR) then
935 raise IEM_ADM_G_MISS_FOR_NOTNULL;
936 end if;
937
938 --check duplicate key name
939 select count(*) into l_name_count from iem_tag_keys where UPPER(tag_name) = UPPER(p_key_name) and tag_key_id <> p_tag_key_id;
940
941 if l_name_count > 0 then
942 raise IEM_TAG_DUP_KEY_NAME;
943 end if;
944
945 --check duplicate key Id
946 select count(*) into l_id_count from iem_tag_keys where UPPER(tag_id) = UPPER(p_key_id) and tag_key_id <> p_tag_key_id;
947
948 if l_id_count > 0 then
949 raise IEM_TAG_DUP_KEY_ID;
950 end if;
951
952 -- Valid 'QUERY' type and 'PROCEDURE' type
953 if p_type_type_code = 'QUERY' then
954 IF p_value is NOT NULL THEN
955 BEGIN
956 l_cursorid := DBMS_SQL.OPEN_CURSOR;
957 DBMS_SQL.PARSE(l_cursorid, p_value, DBMS_SQL.V7);
958
959 EXCEPTION
960 WHEN OTHERS THEN
961 fnd_message.set_name ('IEM', 'IEM_TAG_INVALID_QUERY');
962 l_error_text := SUBSTR (SQLERRM , 1 , 240);
963 FND_MESSAGE.SET_TOKEN('ERROR_TEXT',l_error_text);
964 FND_MSG_PUB.add;
965 DBMS_SQL.CLOSE_CURSOR(l_cursorid);
966 RAISE l_invalid_query;
967 END;
968 END IF;
969 elsif p_type_type_code = 'PROCEDURE' then
970 IF p_value is NOT NULL THEN
971 IEM_TAG_RUN_PROC_PVT.validProcedure(
972 p_api_version_number => P_Api_Version_Number,
973 p_init_msg_list => FND_API.G_FALSE,
974 p_commit => P_Commit,
975 p_ProcName => p_value,
976 x_return_status => l_return_status,
977 x_msg_count => l_msg_count,
978 x_msg_data => l_msg_data
979 );
980 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
981 raise l_IEM_INVALID_PROCEDURE;
982 end if;
983
984 END IF;
985 end if;
986
987
988 update IEM_TAG_KEYS
989 set
990 --tag_id=decode(p_key_id,FND_API.G_MISS_CHAR,tag_id,p_key_id),
991 tag_name=decode(p_key_name,null,tag_name,p_key_name),
992 tag_type_code=decode(p_type_type_code,null,tag_type_code,p_type_type_code),
993 value=decode(p_value,null,tag_type_code,p_value),
994 LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
995 LAST_UPDATE_DATE = sysdate,
996 LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
997 where tag_key_id=p_tag_key_id;
998
1002 END IF;
999 -- Standard Check Of p_commit.
1000 IF FND_API.To_Boolean(p_commit) THEN
1001 COMMIT WORK;
1003
1004 -- Standard callto get message count and if count is 1, get message info.
1005 FND_MSG_PUB.Count_And_Get
1006 ( p_count => x_msg_count,
1007 p_data => x_msg_data
1008 );
1009 EXCEPTION
1010
1011 WHEN l_invalid_query THEN
1012 ROLLBACK TO update_item_tag_key;
1013 x_return_status := FND_API.G_RET_STS_ERROR ;
1014 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1015
1016 WHEN l_IEM_INVALID_PROCEDURE THEN
1017 ROLLBACK TO update_item_tag_key;
1018 x_return_status := FND_API.G_RET_STS_ERROR ;
1019 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1020
1021 WHEN IEM_ADM_G_MISS_FOR_NOTNULL THEN
1022 ROLLBACK TO update_item_tag_key;
1023 FND_MESSAGE.SET_NAME('IEM','IEM_ADM_G_MISS_FOR_NOTNULL');
1024 FND_MSG_PUB.Add;
1025 x_return_status := FND_API.G_RET_STS_ERROR ;
1026 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1027
1028 WHEN IEM_TAG_DUP_KEY_NAME THEN
1029 ROLLBACK TO update_item_tag_key;
1030 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_DUP_KEY_NAME');
1031 FND_MSG_PUB.Add;
1032 x_return_status := FND_API.G_RET_STS_ERROR ;
1033 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1034
1035 WHEN IEM_TAG_DUP_KEY_ID THEN
1036 ROLLBACK TO update_item_tag_key;
1037 FND_MESSAGE.SET_NAME('IEM','IEM_TAG_DUP_KEY_ID');
1038 FND_MSG_PUB.Add;
1039 x_return_status := FND_API.G_RET_STS_ERROR ;
1040 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1041
1042 WHEN FND_API.G_EXC_ERROR THEN
1043 ROLLBACK TO update_item_tag_key;
1044 x_return_status := FND_API.G_RET_STS_ERROR ;
1045 FND_MSG_PUB.Count_And_Get
1046 ( p_count => x_msg_count,
1047 p_data => x_msg_data
1048 );
1049
1050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1051 ROLLBACK TO update_item_tag_key;
1052 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1053 FND_MSG_PUB.Count_And_Get
1054 ( p_count => x_msg_count,
1055 p_data => x_msg_data
1056 );
1057
1058 WHEN OTHERS THEN
1059
1060 ROLLBACK TO update_item_tag_key;
1061 x_return_status := FND_API.G_RET_STS_ERROR;
1062 IF FND_MSG_PUB.Check_Msg_Level
1063 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1064 THEN
1065 FND_MSG_PUB.Add_Exc_Msg
1066 ( G_PKG_NAME,
1067 l_api_name
1068 );
1069 END IF;
1070 FND_MSG_PUB.Count_And_Get
1071
1072 ( p_count => x_msg_count ,
1073 p_data => x_msg_data
1074
1075 );
1076
1077 END update_item_tag_key;
1078
1079
1080 END IEM_TAG_KEY_PVT; -- Package Body IEM_TAG_KEY_PVT