[Home] [Help]
PACKAGE BODY: APPS.IEM_TAGPROCESS_PUB
Source
1 PACKAGE BODY IEM_TAGPROCESS_PUB AS
2 /* $Header: iemptagb.pls 120.2 2006/06/27 14:36:41 pkesani noship $ */
3
4 --
5 --
6 -- Purpose: Maintain Tag Process
7 --
8 -- MODIFICATION HISTORY
9 -- Person Date Comments
10 -- Liang Xia 3/24/2002 Created
11 -- Liang Xia 11/18/2002 Modified getEncryptId() to return null
12 -- for Acknowledgement account.
13 -- Liang Xia 12/6/2002 Fixed GSCC warning: NOCOPY, no G_MISS...
14 -- Liang Xia 01/15/2003 Fixed bug 2752169:changed jtf_rs_resource_members_vl to jtf_rs_resource_members
15 -- Liang Xia 09/18/2003 Fixed bug 3130813: added validation on User-responsibility, and end_date_active for
16 -- JTF_RS_GROUPS_B, jtf_rs_role_relations, jtf_group_members,
17 -- Liang Xia 09/24/2003 add extra validation on isValidAgent with GROUP usage ='CALL' (Call Center)
18 -- Liang Xia 08/13/2004 Modified getTagValue to reuse tag based on
19 -- profile IEM_REPROCESS_ALL_TAGS
20 -- Liang Xia 12/22/2004 Fixed bug 4079440. Init of IEM_REPROCESS_ALL_TAGS should act as 'N'
21 -- Liang Xia 04/06/2005 Fixed GSCC sql.46 ( bug 4256769 )
22 -- Liang Xia 05/31/2005 115.11 schema change compliance ( merged 115.12 with 115.10.11510.7 )
23 -- Liang Xia 06/02/2005 Fixed GSCC sql.46 according to bug 4289628
24 -- PKESANI 05/20/2006 For Bug 5195496, change the SQL to look for responsibility_key
25 -- instead of responsibility_id.
26 -- PKESANI 06/27/2006 For Bug 5143181, changed the SQL in isValidAgent Function,
27 -- To look into IEM_AGENTS instead of IEM_AGENT_ACCOUNTS.
28 -- --------- ------ -----------------------------------------
29
30 -- Enter procedure, function bodies as shown below
31
32 PROCEDURE getEncryptId(
33 P_Api_Version_Number IN NUMBER,
34 P_Init_Msg_List IN VARCHAR2 := null,
35 P_Commit IN VARCHAR2 := null,
36 p_email_account_id IN iem_mstemail_accounts.email_account_id%type,
37 p_agent_id IN NUMBER,
38 p_interaction_id IN NUMBER,
39 p_biz_keyVal_tab IN keyVals_tbl_type,
40 x_encrypted_id OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_return_status OUT NOCOPY VARCHAR2,
43 x_msg_data OUT NOCOPY VARCHAR2)
44 -- Standard Start of API savepoint
45 IS
46 l_api_name VARCHAR2(255):='getEncryptId';
47 l_api_version_number NUMBER:=1.0;
48
49 l_strings varchar2(2000):=null;
50 l_encripted_id VARCHAR2(20);
51 l_keyVal_tbl IEM_ENCRYPT_TAGS_PVT.email_tag_tbl;
52 l_token varchar2(30):=null;
53 l_indx binary_integer;
54 l_temp_key varchar2(30);
55 l_temp_value varchar2(256);
56
57 l_key_value keyVals_tbl_type;
58
59 l_select_csr INTEGER;
60 l_temp varchar2(256);
61 l_query_result varchar2(256);--iem_tag_keys.value%type;
62 l_dummy INTEGER;
63 l_account_flag varchar2(1);
64
65 v_ErrorCode NUMBER;
66 v_ErrorText varchar2(200);
67 errorMessage varchar2(2000);
68 logMessage varchar2(2000);
69
70 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
71 l_msg_count NUMBER := 0;
72 l_msg_data VARCHAR2(2000);
73
74 l_log_enabled BOOLEAN := false;
75 l_exception_log BOOLEAN :=false;
76
77 IEM_CREATE_ENCRYPTEDTAG_FAILED EXCEPTION;
78
79 cursor c_tags ( p_account_id iem_mstemail_accounts.email_account_id%type)
80 is
81 select a.tag_id, a.tag_type_code, a.value
82 from iem_tag_keys a, iem_account_tag_keys b
83 where a.tag_key_id = b.tag_key_id and b.email_account_id = p_account_id;
84
85 BEGIN
86 SAVEPOINT getEncryptId_PUB;
87
88 -- Standard call to check for call compatibility.
89 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
90 p_api_version_number,
91 l_api_name,
92 G_PKG_NAME)
93 THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96
97 -- Initialize message list if p_init_msg_list is set to TRUE.
98 IF FND_API.to_Boolean( p_init_msg_list ) THEN
99 FND_MSG_PUB.initialize;
100 END IF;
101
102 -- Initialize API return status to SUCCESS
103 x_return_status := FND_API.G_RET_STS_SUCCESS;
104
105 -- API begins
106
107 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
108
109 l_exception_log:= FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
110
111
112 if l_log_enabled then
113 logMessage := '[p_email_account_ID=' || to_char(p_email_account_ID) || '][p_Agent_Id='|| to_char(p_Agent_Id)||'][p_interaction_id='|| to_char(p_interaction_id)||']';
114 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId.START', logMessage);
115 end if;
116
117 -- discontinued since 115.11
118 -- select account_flag into l_account_flag from iem_email_accounts
119 -- where email_account_id = p_email_account_id;
120
121 -- IF email account is Acknowledgement, return null for x_encrypted_id
122 -- Shipped in MP-Q (115.9)
123 /* if l_account_flag = 'A' then
124
125 if l_log_enabled then
126 logMessage := '[Email account is Acknowledgement account for p_email_account_id='||to_char(p_email_account_id)||'.Return null for x_encrypted_id]';
127 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
128 end if;
129 x_encrypted_id := null;
130 else
131 */
132 --Start geting business tag and customerized tags here
133 for i in 1..p_biz_keyVal_tab.count loop
134 l_keyVal_tbl(i).email_tag_key := p_biz_keyVal_tab(i).key ;
135 l_keyVal_tbl(i).email_tag_value :=p_biz_keyVal_tab(i).value;
136 end loop;
137
138 l_indx := p_biz_keyVal_tab.count + 1;
139
140 l_key_value(1).key := 'IEMNEMAILACCOUNTID';
141 l_key_value(1).value := TO_CHAR( p_email_account_ID );
142 l_key_value(1).datatype := 'N';
143 l_key_value(2).key := 'IEMNAGENTID';
144 l_key_value(2).value := TO_CHAR(p_Agent_Id);
145 l_key_value(2).datatype := 'N';
146 l_key_value(3).key := 'IEMNINTERACTIONID';
147 l_key_value(3).value := TO_CHAR(p_interaction_id);
148 l_key_value(3).datatype := 'N';
149
150 --Get all the customer defined tags in the system
151 For v_tags in c_tags ( p_email_account_ID) Loop
152
153 l_temp_key := v_tags.tag_id ;
154 l_temp_value := v_tags.value;
155
156 if v_tags.tag_type_code = 'FIXED' then
157 l_keyVal_tbl(l_indx).email_tag_key := 'IEMS'||v_tags.tag_id;
158 l_keyVal_tbl(l_indx).email_tag_value := v_tags.value;
159 l_indx := l_indx + 1;
160
161 elsif v_tags.tag_type_code = 'QUERY' then
162 l_temp := null;
163
164 -- Begin QUERY processing
165 BEGIN
166 l_select_csr := DBMS_SQL.OPEN_CURSOR;
167 DBMS_SQL.PARSE(l_select_csr, l_temp_value, DBMS_SQL.native);
168 DBMS_SQL.DEFINE_COLUMN(l_select_csr, 1, l_query_result, 256);
169 l_dummy := DBMS_SQL.EXECUTE(l_select_csr);
170
171 -- fetch the first result if there is any
172 IF DBMS_SQL.FETCH_ROWS(l_select_csr) = 0 THEN
173 l_temp := null;
174 ELSE
175 DBMS_SQL.COLUMN_VALUE(l_select_csr, 1, l_query_result);
176 l_temp := l_query_result;
177
178 -- check if there are more than 1 rows selected.
179 IF DBMS_SQL.FETCH_ROWS(l_select_csr) <> 0 THEN
180 if l_log_enabled then
181 logMessage := '[ERROR (too many rows selected) when execute query for keyId: '||v_tags.tag_id ||']';
182 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
183 end if;
184 -- dbms_output.put_line('Too many rows are selected');
185 l_temp := null;
186 END IF;
187 end if;
188
189 -- Close the cursor
190 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
191
192 -- Insert data in key-value pair table
193 l_keyVal_tbl(l_indx).email_tag_key := 'IEMS'||v_tags.tag_id;
194 l_keyVal_tbl(l_indx).email_tag_value := l_temp;
195 l_indx := l_indx + 1;
196
197 EXCEPTION
198 WHEN OTHERS THEN
199 DBMS_SQL.CLOSE_CURSOR(l_select_csr);
200
201 if l_log_enabled then
202 logMessage := '[ERROR (Other exception) when execute query for keyId: '||v_tags.tag_id||'. Error:' ||sqlerrm||']';
203 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
204 end if;
205
206 --DBMS_OUTPUT.put_line('OTHER exception happened when execute the query');
207
208 -- Insert data in key-value pair table
209 l_keyVal_tbl(l_indx).email_tag_key := 'IEMS'||v_tags.tag_id;
210 l_keyVal_tbl(l_indx).email_tag_value := l_temp;
211 l_indx := l_indx + 1;
212 END; -- end of QUERY processing
213
214 elsif v_tags.tag_type_code = 'PROCEDURE' then
215 l_temp := null;
216
217 -- begin PROCEDURE processing
218 BEGIN
219 IEM_TAG_RUN_PROC_PVT.run_Procedure(
220 p_api_version_number =>P_Api_Version_Number,
221 p_init_msg_list => FND_API.G_FALSE,
222 p_commit => P_Commit,
223 p_procedure_name => l_temp_value,
224 p_key_value => l_key_value,
225 x_result => l_temp,
226 x_return_status =>l_return_status,
227 x_msg_count => l_msg_count,
228 x_msg_data => l_msg_data);
229
230 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
231 --dbms_output.put_line('Failed to get tag value from procedure '||l_temp_value);
232
233 if l_log_enabled then
234 logMessage := '[ERROR when execute procedure for keyId: '||v_tags.tag_id ||']';
235 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
236 end if;
237 end if;
238
239 -- Insert data in key-value pair table
240 l_keyVal_tbl(l_indx).email_tag_key := 'IEMS'||v_tags.tag_id;
241 l_keyVal_tbl(l_indx).email_tag_value := l_temp;
242 l_indx := l_indx + 1;
243 EXCEPTION
244 WHEN OTHERS THEN
245 -- dbms_output.put_line('OTHER exception happened when execute the procedure ' || SUBSTR (SQLERRM , 1 , 100));
246
247 if l_log_enabled then
248 logMessage := '[ERROR (Others) when execute procedure for keyId: '||v_tags.tag_id ||'. error:'||sqlerrm||']';
249 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
250 end if;
251
252 -- Insert data in key-value pair table
253 l_keyVal_tbl(l_indx).email_tag_key := 'IEMS'||v_tags.tag_id;
254 l_keyVal_tbl(l_indx).email_tag_value := l_temp;
255 l_indx := l_indx + 1;
256 END; -- end of PROCEDURE processing
257
258 end if;
259 end Loop;
260
261
262 IEM_ENCRYPT_TAGS_PVT.create_item(
263 p_api_version_number => P_Api_Version_Number,
264 p_init_msg_list => FND_API.G_FALSE,
265 p_commit => P_Commit,
266 p_agent_id => p_agent_id,
267 p_interaction_id => p_interaction_id,
268 p_email_tag_tbl => l_keyVal_tbl,
269 x_encripted_id => l_encripted_id,
270 x_token => l_token,
271 x_return_status => l_return_status,
272 x_msg_count => x_msg_count,
273 x_msg_data => x_msg_data
274 );
275 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
276 --dbms_output.put_line('Failed in Create_item: IEM_ENCRYPT_TAGS_PVT ');
277 raise IEM_CREATE_ENCRYPTEDTAG_FAILED;
278 end if;
279
280 /*
281 dbms_output.put_line('key' ||' = '|| 'IEMNAGENTID' );
282 dbms_output.put_line(' value' ||' = '||p_agent_id );
283 dbms_output.put_line('key' ||' = '|| 'IEMNINTERACTIONID' );
284 dbms_output.put_line(' value' ||' = '||p_interaction_id);
285
286 for j in 1..l_keyVal_tbl.count loop
287 dbms_output.put_line('key' ||' = '|| l_keyVal_tbl(j).email_tag_key );
288 dbms_output.put_line(' value' ||' = '||l_keyVal_tbl(j).email_tag_value );
289 end loop;
290 */
291
292 -- Return encrypted_id
293 x_encrypted_id := l_encripted_id||l_token;
294
295 -- end if;
296
297 if l_log_enabled then
298 logMessage := '[RETURN Encrypted Id= ' || x_encrypted_id||']';
299 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
300 end if;
301
302 -- Standard Check Of p_commit.
303 IF FND_API.To_Boolean(p_commit) THEN
304 COMMIT WORK;
305 END IF;
306
307 -- Standard callto get message count and if count is 1, get message info.
308 FND_MSG_PUB.Count_And_Get
309 ( p_count => x_msg_count,
310 p_data => x_msg_data
311 );
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 ROLLBACK TO getEncryptId_PUB;
316 x_return_status := FND_API.G_RET_STS_ERROR;
317 if l_log_enabled then
318 logMessage := '[No data found for p_email_account_id= '||p_email_account_id||'.]';
319 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
320 end if;
321
322 WHEN IEM_CREATE_ENCRYPTEDTAG_FAILED THEN
323 ROLLBACK TO getEncryptId_PUB;
324 x_return_status := FND_API.G_RET_STS_ERROR ;
325
326 if l_log_enabled then
327 logMessage := '[Failed to create data in IEM_ENCRYPTED_TAGS table. ]';
328 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
329 end if;
330
331 WHEN FND_API.G_EXC_ERROR THEN
332 ROLLBACK TO getEncryptId_PUB;
333 x_return_status := FND_API.G_RET_STS_ERROR ;
334
335 if l_exception_log then
336 logMessage := '[FND_API.G_EXC_ERROR in getEncryptId ]';
337 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
338 end if;
339 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
340 ROLLBACK TO getEncryptId_PUB;
341 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
342
343 if l_exception_log then
344 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in getEncryptId]';
345 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
346 end if;
347 WHEN OTHERS THEN
348
349 ROLLBACK TO getEncryptId_PUB;
350 x_return_status := FND_API.G_RET_STS_ERROR;
351
352 if l_exception_log then
353 logMessage := '[OTHER exception in getEncryptId]';
354 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getEncryptId', logMessage);
355 end if;
356
357 END getEncryptId;
358
359
360 PROCEDURE IEM_STAMP_ENCRYPTED_TAG(
361 P_Api_Version_Number IN NUMBER,
362 P_Init_Msg_List IN VARCHAR2 := null,
363 P_Commit IN VARCHAR2 := null,
364 p_encrypted_id IN NUMBER,
365 p_message_id IN NUMBER,
366 x_msg_count OUT NOCOPY NUMBER,
367 x_return_status OUT NOCOPY VARCHAR2,
368 x_msg_data OUT NOCOPY VARCHAR2)
369 -- Standard Start of API savepoint
370 IS
371 l_api_name VARCHAR2(255):='IEM_STAMP_ENCRYPTED_TAG';
372 l_api_version_number NUMBER:=1.0;
373
374 l_strings varchar2(2000):=null;
375
376 v_ErrorCode NUMBER;
377 v_ErrorText varchar2(200);
378 errorMessage varchar2(2000);
379 logMessage varchar2(2000);
380
381 l_len NUMBER := 0;
382 l_token_in VARCHAR2(20) := '';
383 l_token_out VARCHAR2(20);
384 l_encrypt_char VARCHAR2(150);
385 l_encrypt_num NUMBER(15);
386 l_temp number;
387
388 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
389 l_msg_count NUMBER := 0;
390 l_msg_data VARCHAR2(2000);
391 l_log_enabled BOOLEAN := false;
392 l_exception_log BOOLEAN :=false;
393
394 IEM_FAILED_TO_STAMP_TAG EXCEPTION;
395 IEM_TOKEN_NOT_MATCH EXCEPTION;
396 IEM_INVALID_ENCRYPTED_ID EXCEPTION;
397
398 BEGIN
399 SAVEPOINT IEM_STAMP_ENCRYPTED_TAG;
400
401 -- Standard call to check for call compatibility.
402 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
403 p_api_version_number,
404 l_api_name,
405 G_PKG_NAME)
406 THEN
407 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
408 END IF;
409
410 -- Initialize message list if p_init_msg_list is set to TRUE.
411 IF FND_API.to_Boolean( p_init_msg_list ) THEN
412 FND_MSG_PUB.initialize;
413 END IF;
414
415 -- Initialize API return status to SUCCESS
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417
418 -- API begins
419 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
420
421 l_exception_log:= FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
422
423
424 IEM_ENCRYPT_TAGS_PVT.update_item_on_mess_id (
425 p_api_version_number => P_Api_Version_Number,
426 p_init_msg_list => FND_API.G_FALSE,
427 p_commit => P_Commit,
428 p_encrypted_id => p_encrypted_id,
429 p_message_id => p_message_id,
430 x_return_status => l_return_status,
431 x_msg_count => x_msg_count,
432 x_msg_data => x_msg_data
433 );
434
435 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
436 raise IEM_FAILED_TO_STAMP_TAG;
437 end if;
438
439 if l_log_enabled then
440 logMessage := '[TAG IS STAMPED]';
441 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
442 end if;
443
444 -- Standard Check Of p_commit.
445 IF FND_API.To_Boolean(p_commit) THEN
446 COMMIT WORK;
447 END IF;
448
449
450 -- Standard callto get message count and if count is 1, get message info.
451 FND_MSG_PUB.Count_And_Get
452 ( p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455
456 EXCEPTION
457 WHEN IEM_INVALID_ENCRYPTED_ID THEN
458 --dbms_output.put_line('IEM_INVALID_ENCRYPTED_ID');
459 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
460 x_return_status := FND_API.G_RET_STS_ERROR ;
461
462 if l_log_enabled then
463 logMessage := '[The encrypted id is invalid because length is too short.]';
464 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
465 end if;
466 WHEN NO_DATA_FOUND THEN
467 --dbms_output.put_line('The encrypted Id is invalid because no data found or no security token stored.');
468 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
469 x_return_status := FND_API.G_RET_STS_ERROR ;
470
471 if l_log_enabled then
472 logMessage := '[The encrypted Id is invalid because no data found or no security token stored.]';
473 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
474 end if;
475
476 WHEN IEM_TOKEN_NOT_MATCH THEN
477 --dbms_output.put_line('IEM_TOKEN_NOT_MATCH');
478 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
479 x_return_status := FND_API.G_RET_STS_ERROR ;
480
481 if l_log_enabled then
482 logMessage := '[The token is not match with security token.]';
483 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
484 end if;
485
486 WHEN IEM_FAILED_TO_STAMP_TAG THEN
487 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
488 --dbms_output.put_line('IEM_STAMP_ENCRYPTED_TAG');
489 x_return_status := FND_API.G_RET_STS_ERROR ;
490
491 if l_log_enabled then
492 logMessage := '[FAILED to STAMP TAG]';
493 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
494 end if;
495
496 WHEN FND_API.G_EXC_ERROR THEN
497 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
498 x_return_status := FND_API.G_RET_STS_ERROR ;
499 if l_exception_log then
500 logMessage := '[FND_API.G_EXC_ERROR happened]';
501 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
502 end if;
503 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
504 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
506 if l_exception_log then
507 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR happened]';
508 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
509 end if;
510 WHEN OTHERS THEN
511 ROLLBACK TO IEM_STAMP_ENCRYPTED_TAG;
512 x_return_status := FND_API.G_RET_STS_ERROR;
513
514 if l_exception_log then
515 logMessage := '[Other exception happended in IEM_STAMP_ENCRYPTED_TAG: ' ||sqlerrm||']';
516 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG', logMessage);
517 end if;
518 END IEM_STAMP_ENCRYPTED_TAG;
519
520 PROCEDURE getTagValues(
521 P_Api_Version_Number IN NUMBER,
522 P_Init_Msg_List IN VARCHAR2 := null,
523 P_Commit IN VARCHAR2 := null,
524 p_encrypted_id IN VARCHAR2,
525 p_message_id IN NUMBER,
526 x_key_value OUT NOCOPY keyVals_tbl_type,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_data OUT NOCOPY VARCHAR2)
530 -- Standard Start of API savepoint
531 IS
532 l_api_name VARCHAR2(255):='getTagValues';
533 l_api_version_number NUMBER:=1.0;
534
535 l_encrypted_id VARCHAR2(256);
536 l_keyVal_tab keyVals_tbl_type;
537 l_agent_id number := null;
538 l_interaction_id number := null;
539 i number := 1;
540
541 v_ErrorCode NUMBER;
542 v_ErrorText varchar2(200);
543 errorMessage varchar2(2000);
544 logMessage varchar2(2000);
545
546 l_len NUMBER := 0;
547 l_token_in VARCHAR2(20) := '';
548 l_token_out VARCHAR2(20);
549 l_encrypt_char VARCHAR2(150);
550 l_encrypt_num NUMBER(15);
551 l_mess_id number;
552 l_reuse_tag VARCHAR2(20) := '';
553
554 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
555 l_msg_count NUMBER := 0;
556 l_msg_data VARCHAR2(2000);
557 l_log_enabled BOOLEAN := false;
558 l_exception_log BOOLEAN :=false;
559
560 IEM_TOKEN_NOT_MATCH EXCEPTION;
561 IEM_INVALID_ENCRYPTED_ID EXCEPTION;
562 IEM_FAILED_TO_STAMP_TAG EXCEPTION;
563 IEM_ENCRYPTED_ID_ALREADY_USED EXCEPTION;
564 IEM_FAIL_DUPLICAT_REC_REUSETAG EXCEPTION;
565
566 cursor c_key_val (p_encrypted_id iem_encrypted_tags.encrypted_id%type)
567 is
568 select
569 a.key,
570 a.value
571 from
572 iem_encrypted_tag_dtls a
573 where
574 a.encrypted_id = p_encrypted_id;
575 BEGIN
576 SAVEPOINT getTagValues_PUB;
577
578 -- Standard call to check for call compatibility.
579 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
580 p_api_version_number,
581 l_api_name,
582 G_PKG_NAME)
583 THEN
584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
585 END IF;
586
587 -- Initialize message list if p_init_msg_list is set to TRUE.
588 IF FND_API.to_Boolean( p_init_msg_list ) THEN
589 FND_MSG_PUB.initialize;
590 END IF;
591
592 -- Initialize API return status to SUCCESS
593 x_return_status := FND_API.G_RET_STS_SUCCESS;
594
595 -- API begins
596
597 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
598
599 l_exception_log:= FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
600
601
602 if l_log_enabled then
603 logMessage := '[Input Enrypted_ID=' || p_encrypted_id || ' p_message_id=' || p_message_id ||']';
604 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getTagValues.START', logMessage);
605 end if;
606
607 l_encrypted_id := LTRIM(RTRIM(p_encrypted_id));
608
609 l_len := length(l_encrypted_id);
610 if l_len < 10 then
611 -- dbms_output.put_line('Too short!');
612 raise IEM_INVALID_ENCRYPTED_ID;
613 else
614 l_token_in := SUBSTR(l_encrypted_id, l_len-4, 5);
615 l_encrypt_char := SUBSTR(l_encrypted_id, 1, l_len-5);
616 end if;
617
618 --Security check
619 begin
620 l_encrypt_num := TO_NUMBER( l_encrypt_char );
621 select token into l_token_out from iem_encrypted_tags where encrypted_id = l_encrypt_num;
622
623 exception
624 when others then
625 raise IEM_TOKEN_NOT_MATCH;
626 end;
627
628 if l_token_in <> l_token_out then
629 raise IEM_TOKEN_NOT_MATCH;
630 end if;
631
632 -- Check whether this encrypted_id already been used by other message or not
633 select message_id into l_mess_id from iem_encrypted_tags where encrypted_id = l_encrypt_num;
634
635 l_reuse_tag := FND_PROFILE.VALUE_SPECIFIC('IEM_REPROCESS_ALL_TAGS');
636
637 -- If reuse tag is not set and tag has been used
638 if ( l_reuse_tag is null or l_reuse_tag <> 'Y' ) and ( l_mess_id is not null ) then
639 raise IEM_ENCRYPTED_ID_ALREADY_USED;
640
641 -- If profile value of reuse tag is set and tag is used
642 -- Duplicate tag records and stamp with new msg_id, and return key_val
643 elsif ( l_reuse_tag = 'Y' ) and ( l_mess_id is not null )then
644
645 IEM_ENCRYPT_TAGS_PVT.duplicate_tags(
646 p_api_version_number => P_Api_Version_Number,
647 p_init_msg_list => FND_API.G_FALSE,
648 p_commit => P_Commit,
649 p_encrypted_id => l_encrypt_num,
650 p_message_id => p_message_id,
651 x_return_status => l_return_status,
652 x_msg_count => x_msg_count,
653 x_msg_data => x_msg_data
654 );
655
656 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
657 --dbms_output.put_line('ERROR when calling IEM_ENCRYPT_TAGS_PVT.duplicate_tags ');
658 raise IEM_FAIL_DUPLICAT_REC_REUSETAG;
659 end if;
660
661 -- Following cases to stamp msg_id and return key_val when
662 -- 1) l_reuse_tag <> 'Y' ) and ( l_mess_id is null )
663 -- or
664 -- 2) l_reuse_tag = 'Y' ) and ( l_mess_id is null )
665 else
666 --Stamping the message_id with the encrypted_id
667 IEM_TAGPROCESS_PUB.IEM_STAMP_ENCRYPTED_TAG
668 ( p_api_version_number => l_api_version_number,
669 p_init_msg_list => FND_API.G_FALSE,
670 p_commit =>FND_API.G_FALSE,
671 p_encrypted_id => l_encrypt_num,
672 p_message_Id=>p_message_id,
673 x_return_status =>l_return_status,
674 x_msg_count => l_msg_count,
675 x_msg_data => l_msg_data);
676
677 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
678 raise IEM_FAILED_TO_STAMP_TAG;
679 end if;
680
681 end if;
682
683 SELECT agent_id, interaction_id
684 INTO l_agent_id, l_interaction_id
685 FROM iem_encrypted_tags
686 WHERE encrypted_id = l_encrypt_num;
687
688 -- Get eMail Center system Tags
689 l_keyVal_tab(1).key := 'IEMNAGENTID';
690 l_keyVal_tab(1).value := l_agent_id;
691 l_keyVal_tab(1).datatype := 'N';
692
693 l_keyVal_tab(2).key := 'IEMNINTERACTIONID';
694 l_keyVal_tab(2).value := l_interaction_id;
695 l_keyVal_tab(2).datatype := 'N';
696
697 -- Get BIZ system Tags and Custom Tags
698 For v_key_val in c_key_val (l_encrypt_num ) Loop
699 l_keyVal_tab(i+2).key := v_key_val.key ;
700 l_keyVal_tab(i+2).value := v_key_val.value ;
701 l_keyVal_tab(i+2).datatype :=SUBSTR(v_key_val.key, 4, 1);
702 i := i+1;
703 end loop;
704
705 -- Log returned key-val.
706 if l_log_enabled then
707 logMessage := '[ Returned Key-val total = ' || l_keyVal_tab.COUNT|| ' ]';
708 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
709 end if;
710
711 FOR x in 1..l_keyVal_tab.COUNT LOOP
712 if l_log_enabled then
713 logMessage := '[ key= ' || l_keyVal_tab(x).key || ' ] [ value= '|| l_keyVal_tab(x).value ||' ]';
714 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
715 end if;
716 END LOOP;
717
718 -- Return key-val pairs
719 x_key_value := l_keyVal_tab;
720
721 -- Standard Check Of p_commit.
722 IF FND_API.To_Boolean(p_commit) THEN
723 COMMIT WORK;
724 END IF;
725
726
727 -- Standard callto get message count and if count is 1, get message info.
728 FND_MSG_PUB.Count_And_Get
729 ( p_count => x_msg_count,
730 p_data => x_msg_data
731 );
732
733 EXCEPTION
734 WHEN IEM_FAIL_DUPLICAT_REC_REUSETAG THEN
735 --dbms_output.put_line('Failed to stamp tag');
736 ROLLBACK TO getTagValues_PUB;
737 x_return_status := FND_API.G_RET_STS_SUCCESS ;
738
739 if l_log_enabled then
740 logMessage := '[ERROR when calling IEM_ENCRYPT_TAGS_PVT.duplicate_tags]';
741 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getTagValues', logMessage);
742 end if;
743
744 WHEN IEM_FAILED_TO_STAMP_TAG THEN
745 --dbms_output.put_line('Failed to stamp tag');
746 ROLLBACK TO getTagValues_PUB;
747 x_return_status := FND_API.G_RET_STS_SUCCESS ;
748
749 if l_log_enabled then
750 logMessage := '[FAILED to STAMP TAG]';
751 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
752 end if;
753
754 WHEN IEM_ENCRYPTED_ID_ALREADY_USED THEN
755 -- dbms_output.put_line('This encrypted id is already used by another message');
756 ROLLBACK TO getTagValues_PUB;
757 x_return_status := FND_API.G_RET_STS_SUCCESS ;
758
759 if l_log_enabled then
760 logMessage := '[The encrypted ID already has been stamped with other message.]';
761 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
762 end if;
763
764 WHEN IEM_INVALID_ENCRYPTED_ID THEN
765 ROLLBACK TO getTagValues_PUB;
766 x_return_status := FND_API.G_RET_STS_SUCCESS ;
767
768 if l_log_enabled then
769 logMessage := '[The encrypted id is invalid because length is too short.]';
770 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
771 end if;
772 WHEN NO_DATA_FOUND THEN
773 --dbms_output.put_line('The encrypted Id is invalid because no data found or no security token stored.');
774 ROLLBACK TO getTagValues_PUB;
775 x_return_status := FND_API.G_RET_STS_SUCCESS ;
776
777 if l_log_enabled then
778 logMessage := '[The encrypted Id is invalid because no data found or no security token stored.]';
779 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
780 end if;
781
782 WHEN IEM_TOKEN_NOT_MATCH THEN
783 ROLLBACK TO getTagValues_PUB;
784 --dbms_output.put_line('TOKEN_NOT_MATCH');
785 x_return_status := FND_API.G_RET_STS_SUCCESS ;
786
787 if l_log_enabled then
788 logMessage := '[The token is not match with security token.]';
789 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
790 end if;
791
792
793 WHEN FND_API.G_EXC_ERROR THEN
794
795 ROLLBACK TO getTagValues_PUB;
796 x_return_status := FND_API.G_RET_STS_ERROR ;
797 if l_exception_log then
798 logMessage := '[FND_API.G_EXC_ERROR in GETTAGVALUES]';
799 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
800 end if;
801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
802 ROLLBACK TO getTagValues_PUB;
803 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
804
805 if l_exception_log then
806 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in GETTAGVALUES]';
807 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
808 end if;
809 WHEN OTHERS THEN
810 ROLLBACK TO getTagValues_PUB;
811 x_return_status := FND_API.G_RET_STS_ERROR;
812
813 if l_exception_log then
814 logMessage := '[Other exception happened in GETTAGVALUES:' || sqlerrm || ']';
815 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES', logMessage);
816 end if;
817 END getTagValues;
818
819
820 PROCEDURE getTagValues_on_MsgId(
821 P_Api_Version_Number IN NUMBER,
822 P_Init_Msg_List IN VARCHAR2 := null,
823 P_Commit IN VARCHAR2 := null,
824 p_message_id IN NUMBER,
825 x_key_value OUT NOCOPY keyVals_tbl_type,
826 x_encrypted_id OUT NOCOPY VARCHAR2,
827 x_msg_count OUT NOCOPY NUMBER,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_data OUT NOCOPY VARCHAR2)
830 -- Standard Start of API savepoint
831 IS
832 l_api_name VARCHAR2(255):='getTagValues_on_MsgId';
833 l_api_version_number NUMBER:=1.0;
834
835
836 l_keyVal_tab keyVals_tbl_type ;
837 l_agent_id number := null;
838 l_interaction_id number := null;
839 i number :=1;
840
841 v_ErrorCode NUMBER;
842 v_ErrorText varchar2(200);
843 errorMessage varchar2(2000);
844 logMessage varchar2(2000);
845 l_encrypted_id varchar2(20);
846 l_token varchar(20);
847
848 l_return_status VARCHAR2(20) := FND_API.G_RET_STS_SUCCESS;
849 l_msg_count NUMBER := 0;
850 l_msg_data VARCHAR2(2000);
851 l_log_enabled BOOLEAN := false;
852 l_exception_log BOOLEAN :=false;
853
854 IEM_TOKEN_NOT_MATCH EXCEPTION;
855 IEM_INVALID_ENCRYPTED_ID EXCEPTION;
856
857 cursor c_key_val (p_msg_id iem_encrypted_tags.message_id%type)
858 is
859 select
860 a.key,
861 a.value
862 from
863 iem_encrypted_tag_dtls a, iem_encrypted_tags b
864 where
865 a.encrypted_id=b.encrypted_id and b.message_id = p_msg_id;
866 BEGIN
867 SAVEPOINT getTagValues_on_MsgId_PUB;
868
869 -- Standard call to check for call compatibility.
870 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
871 p_api_version_number,
872 l_api_name,
873 G_PKG_NAME)
874 THEN
875 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
876 END IF;
877
878 -- Initialize message list if p_init_msg_list is set to TRUE.
879 IF FND_API.to_Boolean( p_init_msg_list ) THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882
883 -- Initialize API return status to SUCCESS
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885
886 -- API begins
887 l_log_enabled := FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
888
889 l_exception_log:= FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL;
890
891 if l_log_enabled then
892 logMessage := '[Message_id passed in =' || p_message_id || ']';
893 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.getTagValues_on_MsgId.START', logMessage);
894 end if;
895
896
897 SELECT agent_id, interaction_id, encrypted_id, token
898 INTO l_agent_id, l_interaction_id, l_encrypted_id, l_token
899 FROM iem_encrypted_tags
900 WHERE message_Id = p_message_id;
901
902 --First get system tags: agent_id, interaction_id
903 l_keyVal_tab(1).key := 'IEMNAGENTID';
904 l_keyVal_tab(1).value := l_agent_id;
905 l_keyVal_tab(1).datatype := 'N';
906
907 l_keyVal_tab(2).key := 'IEMNINTERACTIONID';
908 l_keyVal_tab(2).value := l_interaction_id;
909 l_keyVal_tab(2).datatype := 'N';
910
911 --Then get Custom get and apps bix tags
912 For v_key_val in c_key_val (p_message_id ) Loop
913 l_keyVal_tab(i+2).key := v_key_val.key ;
914 l_keyVal_tab(i+2).value := v_key_val.value ;
915 l_keyVal_tab(i+2).datatype := SUBSTR(v_key_val.key, 4, 1);
916 i := i+1;
917 end loop;
918
919 -- Log returned key-val.
920 if l_log_enabled then
921 logMessage := '[ Returned Key-val total = ' || l_keyVal_tab.COUNT|| ' EncryptedID = ' ||l_encrypted_id || l_token||' ]';
922 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
923 end if;
924
925 if l_log_enabled then
926 logMessage := '[ Returned Key-val total = ' || l_keyVal_tab.COUNT|| ' ]';
927 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
928 end if;
929
930 FOR x in 1..l_keyVal_tab.COUNT LOOP
931 if l_log_enabled then
932 logMessage := '[ key= ' || l_keyVal_tab(x).key || ' ] [ value= '|| l_keyVal_tab(x).value ||' ]';
933 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
934 end if;
935 END LOOP;
936
937 x_key_value := l_keyVal_tab;
938 x_encrypted_id := l_encrypted_id || l_token;
939
940
941 -- Standard Check Of p_commit.
942 IF FND_API.To_Boolean(p_commit) THEN
943 COMMIT WORK;
944 END IF;
945
946 -- Standard callto get message count and if count is 1, get message info.
947 FND_MSG_PUB.Count_And_Get
948 ( p_count => x_msg_count,
949 p_data => x_msg_data
950 );
951
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 --dbms_output.put_line('The message id is not stamped.');
955 ROLLBACK TO getTagValues_on_MsgId_PUB;
956 x_return_status := FND_API.G_RET_STS_SUCCESS ;
957
958 if l_log_enabled then
959 logMessage := '[The message id is not stamped.There is not corresponding tags for this message.No Key_val returned.]';
960 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
961 end if;
962
963 WHEN FND_API.G_EXC_ERROR THEN
964 ROLLBACK TO getTagValues_on_MsgId_PUB;
965 x_return_status := FND_API.G_RET_STS_ERROR ;
966
967 if l_exception_log then
968 logMessage := '[FND_API.G_EXC_ERROR happened in GETTAGVALUES_ON_MSGID.No Key_val returned.]';
969 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
970 end if;
971 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
972 ROLLBACK TO getTagValues_on_MsgId_PUB;
973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
974
975 if l_exception_log then
976 logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR happened in GETTAGVALUES_ON_MSGID.No Key_val returned.]';
977 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
978 end if;
979 WHEN OTHERS THEN
980
981 ROLLBACK TO getTagValues_on_MsgId_PUB;
982 x_return_status := FND_API.G_RET_STS_ERROR;
983
984 if l_exception_log then
985 logMessage := '[Other exception happend in GETTAGVALUES_ON_MSGID.'||sqlerrm||']';
986 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION, 'IEM.PLSQL.IEM_TAGPROCESS_PUB.GETTAGVALUES_ON_MSGID', logMessage);
987 end if;
988 END getTagValues_on_MsgId;
989
990 function isValidAgent( p_agent_id number, p_email_acct_id number)
991 return boolean
992 is
993
994 l_asso_count number;
995 l_role_count number;
996 l_grp_count number;
997 l_user_resp number;
998
999 begin
1000 select count(*) into l_asso_count from iem_agents where resource_id=p_agent_id and email_account_id=p_email_acct_id;
1001 if l_asso_count < 1 then
1002 return false;
1003 end if;
1004
1005 select count(*) into l_role_count from jtf_rs_role_relations a, jtf_rs_roles_vl b
1006 where a.role_resource_id=p_agent_id and a.role_id=b.role_id
1007 and b.role_type_code='ICENTER'
1008 and a.delete_flag='N'
1009 and a.START_DATE_ACTIVE< sysdate and ( a.END_DATE_ACTIVE>sysdate or a.END_DATE_ACTIVE is null);
1010 if l_role_count < 1 then
1011 return false;
1012 end if;
1013
1014 select count(*) into l_grp_count from jtf_rs_group_members a, JTF_RS_GROUPS_B b, JTF_RS_GROUP_USAGES c
1015 where a.group_id = b.group_id
1016 and a.resource_id = p_agent_id
1017 and a.delete_flag = 'N'
1018 and b.START_DATE_ACTIVE< sysdate
1019 and ( b.END_DATE_ACTIVE>sysdate or b.END_DATE_ACTIVE is null)
1020 and b.group_id = c.group_id
1021 and c.usage = 'CALL';
1022
1023 if l_grp_count < 1 then
1024 return false;
1025 end if;
1026
1027 select count(a.user_id) into l_user_resp from jtf_rs_resource_extns a ,
1028 fnd_user_resp_groups b, fnd_user c, fnd_responsibility resp
1029 where a.resource_id = p_agent_id
1030 and a.START_DATE_ACTIVE< sysdate and ( a.END_DATE_ACTIVE>sysdate or a.END_DATE_ACTIVE is null)
1031 and a.user_id=b.user_id
1032 and b.START_DATE< sysdate and ( b.END_DATE>sysdate or b.END_DATE is null)
1033 -- and ( b.responsibility_id = 23720 or b.responsibility_id = 23107 )
1034 and ( b.responsibility_id = resp.responsibility_id and resp.application_id=680)
1035 and ( resp.responsibility_key = 'EMAIL_CENTER_SUPERVISOR' or resp.responsibility_key='IEM_SA_AGENT')
1036 and b.user_id = c.user_id
1037 and c.START_DATE< sysdate and ( c.END_DATE>sysdate or c.END_DATE is null);
1038
1039 if l_user_resp < 1 then
1040 return false;
1041 end if;
1042
1043 return true;
1044 end;
1045
1046 END IEM_TAGPROCESS_PUB;