[Home] [Help]
1: PACKAGE BODY IEM_TAGPROCESS_PUB AS
2: /* $Header: iemptagb.pls 120.2 2006/06/27 14:36:41 pkesani noship $ */
3:
4: --
5: --
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
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: */
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;
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:
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
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;
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
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 ;
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:
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
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');
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;
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;
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 ;
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;
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(
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:
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,
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
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
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');
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');
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;
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
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 ;
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;
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:
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
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|| ' ]';
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
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;
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 ;
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)
1042:
1043: return true;
1044: end;
1045:
1046: END IEM_TAGPROCESS_PUB;