The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_created_updated_by NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
G_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID') ) ;
select to_char(fnd_crypto.randomnumber) into l_ram from dual;
SELECT IEM_ENCRYPTED_TAGS_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ENCRYPTED_TAGS
(
ENCRYPTED_ID,
MESSAGE_ID,
AGENT_ID,
INTERACTION_ID,
TOKEN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_seq_id,
null,
p_agent_id,
p_interaction_id,
l_token,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
);
PROCEDURE delete_item_by_msg_id
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_message_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_api_name varchar2(30):='delete_item_by_msg_id_PVT';
SAVEPOINT delete_item_by_msg_id_PVT;
select ENCRYPTED_ID into l_encpt_id from iem_encrypted_tags where message_id = l_msg_id;
DELETE
FROM IEM_ENCRYPTED_TAGS
WHERE message_id = l_msg_id;
DELETE
FROM IEM_ENCRYPTED_TAG_DTLS
WHERE ENCRYPTED_ID = l_encpt_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[Not delete (no encrypted tag found)- trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[Not delete (MSG_ID not found) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
END delete_item_by_msg_id;
PROCEDURE update_item_on_mess_id (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_encrypted_id IN NUMBER,
p_message_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_api_name VARCHAR2(255):='update_item_on_mess_id';
SAVEPOINT update_item_on_mess_id;
update IEM_ENCRYPTED_TAGS
set
message_id=p_message_id,
LAST_UPDATED_BY = decode(G_created_updated_by,null,-1,G_created_updated_by),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATE_LOGIN = decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
where encrypted_id = p_encrypted_id;
ROLLBACK TO update_item_on_mess_id;
ROLLBACK TO update_item_on_mess_id;
ROLLBACK TO update_item_on_mess_id;
ROLLBACK TO update_item_on_mess_id;
ROLLBACK TO update_item_on_mess_id;
END update_item_on_mess_id;
SELECT IEM_ENCRYPTED_TAG_DTLS_S1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ENCRYPTED_TAG_DTLS
(
ENCRYPTED_TAG_DTL_ID,
KEY,
VALUE,
ENCRYPTED_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_seq_id,
l_key,
l_val,
g_encrypted_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
);
update iem_encrypted_tags set message_id = null where message_id = l_msg_id;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
logMessage := '[Not delete (no encrypted tag found)- trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ENCRYPTED_TAGS_PVT.DELETE_ITEM_BY_MSG_ID', logMessage);
select key, value from iem_encrypted_tag_dtls where encrypted_id = p_encypted_id;
SAVEPOINT delete_item_by_msg_id_PVT;
select * into l_encypted_rec from IEM_ENCRYPTED_TAGS where ENCRYPTED_ID=p_encrypted_id;
SELECT IEM_ENCRYPTED_TAGS_S1.nextval
INTO l_seq_id
FROM dual;
insert into IEM_ENCRYPTED_TAGS
(
ENCRYPTED_ID,
MESSAGE_ID,
AGENT_ID,
INTERACTION_ID,
TOKEN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
values
(
l_seq_id,
p_message_id,
l_encypted_rec.agent_id,
l_encypted_rec.interaction_id,
l_encypted_rec.token,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
);
SELECT IEM_ENCRYPTED_TAG_DTLS_S1.nextval
INTO l_seq_dtl_id
FROM dual;
INSERT INTO IEM_ENCRYPTED_TAG_DTLS
(
ENCRYPTED_TAG_DTL_ID,
KEY,
VALUE,
ENCRYPTED_ID,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE_CATEGORY,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_seq_dtl_id,
v_rec.key,
v_rec.value,
l_seq_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_created_updated_by,null,-1,G_created_updated_by),
sysdate,
decode(G_LAST_UPDATE_LOGIN,null,-1,G_LAST_UPDATE_LOGIN)
);
ROLLBACK TO delete_item_by_msg_id_PVT;
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[FND_API.G_EXC_ERROR - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[FND_API.G_EXC_UNEXPECTED_ERROR in - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';
ROLLBACK TO delete_item_by_msg_id_PVT;
logMessage := '[Failed (Other exception) - trying to delete Encrypted tag with MSG_ID = ' || p_message_id ||']';