The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_LAST_UPDATED_BY NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATE_LOGIN NUMBER,
p_ATTRIBUTE1 VARCHAR2,
p_ATTRIBUTE2 VARCHAR2,
p_ATTRIBUTE3 VARCHAR2,
p_ATTRIBUTE4 VARCHAR2,
p_ATTRIBUTE5 VARCHAR2,
p_ATTRIBUTE6 VARCHAR2,
p_ATTRIBUTE7 VARCHAR2,
p_ATTRIBUTE8 VARCHAR2,
p_ATTRIBUTE9 VARCHAR2,
p_ATTRIBUTE10 VARCHAR2,
p_ATTRIBUTE11 VARCHAR2,
p_ATTRIBUTE12 VARCHAR2,
p_ATTRIBUTE13 VARCHAR2,
p_ATTRIBUTE14 VARCHAR2,
p_ATTRIBUTE15 VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
l_api_name VARCHAR2(255):='create_item_sss';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SELECT iem_email_servers_s1.nextval
INTO l_seq_id
FROM dual;
Select count(*) into l_type_cnt from iem_email_server_types
where email_server_type_id=p_server_type_id
and rownum=1;
Select count(*) into l_grp_cnt from iem_server_groups
where server_group_id=p_server_group_id
and rownum=1;
INSERT INTO iem_email_servers
(
EMAIL_SERVER_ID,
SERVER_NAME,
DNS_NAME,
IP_ADDRESS,
PORT,
SERVER_TYPE_ID ,
RT_AVAILABILITY,
SERVER_GROUP_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15 )
VALUES
(
l_seq_id,
p_server_name,
p_dns_name,
p_ip_address,
p_port,
p_server_type_id,
p_rt_availability,
p_server_group_id,
decode(l_CREATED_BY,null,-1,l_CREATED_BY),
sysdate,
decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
sysdate,
decode(l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
decode(p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE1),
decode(p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE2),
decode(p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE3),
decode(p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE4),
decode(p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE5),
decode(p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE6),
decode(p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE7),
decode(p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE8),
decode(p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE9),
decode(p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE10),
decode(p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE11),
decode(p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE12),
decode(p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE13),
decode(p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE14),
decode(p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, p_ATTRIBUTE15)
);
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status =>l_stat,
x_msg_count => l_count,
x_msg_data => l_data);
-- Update Cache Audit Trail
select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;
select email_server_id into l_email_server_id from iem_email_servers
where UPPER(dns_name)=UPPER(p_dns_name)
and ip_address=p_ip_address and port=p_port;
SELECT iem_email_servers_s1.currval
INTO l_email_server_id
FROM dual;
PROCEDURE delete_item (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_email_server_id IN NUMBER,
p_dns_name IN VARCHAR2,
p_ip_address IN VARCHAR2,
p_port 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):='delete_item';
SAVEPOINT delete_item_PVT;
delete from iem_email_servers
where ip_address=p_ip_address and dns_name=p_dns_name and port=p_port;
delete from iem_email_servers
where email_server_id=p_email_server_id;
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
END delete_item;
PROCEDURE update_item_sss (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_email_server_id IN NUMBER,
p_server_name IN varchar2,
p_dns_name IN VARCHAR2,
p_ip_address IN VARCHAR2,
p_port IN NUMBER,
p_server_type_id IN number,
p_rt_availability in varchar2,
p_server_group_id in number,
p_LAST_UPDATED_BY NUMBER,
p_LAST_UPDATE_DATE DATE,
p_LAST_UPDATE_LOGIN NUMBER,
p_ATTRIBUTE1 VARCHAR2,
p_ATTRIBUTE2 VARCHAR2,
p_ATTRIBUTE3 VARCHAR2,
p_ATTRIBUTE4 VARCHAR2,
p_ATTRIBUTE5 VARCHAR2,
p_ATTRIBUTE6 VARCHAR2,
p_ATTRIBUTE7 VARCHAR2,
p_ATTRIBUTE8 VARCHAR2,
p_ATTRIBUTE9 VARCHAR2,
p_ATTRIBUTE10 VARCHAR2,
p_ATTRIBUTE11 VARCHAR2,
p_ATTRIBUTE12 VARCHAR2,
p_ATTRIBUTE13 VARCHAR2,
p_ATTRIBUTE14 VARCHAR2,
p_ATTRIBUTE15 VARCHAR2,
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_sss';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_item_PVT;
Select count(*) into l_type_cnt from iem_email_server_types
where email_server_type_id=p_server_type_id
and rownum=1;
select count(*) into l_grp_cnt from iem_server_groups
where server_group_id=p_server_group_id
and rownum=1;
UPDATE IEM_EMAIL_SERVERS
set server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL, server_name,p_server_name),
rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY, null,-1,l_LAST_UPDATED_BY),
LAST_UPDATE_LOGIN = decode( l_LAST_UPDATE_LOGIN,null,-1,l_LAST_UPDATE_LOGIN),
ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
where dns_name=p_dns_name and ip_address=p_ip_address and port=p_port;
UPDATE IEM_EMAIL_SERVERS
SET server_name=decode(p_server_name,FND_API.G_MISS_CHAR, NULL, NULL,server_name,p_server_name),
server_type_id=decode(p_server_type_id,FND_API.G_MISS_NUM, NULL, NULL,server_type_id,p_server_type_id),
rt_availability=decode(p_rt_availability,FND_API.G_MISS_CHAR, NULL, NULL,rt_availability,p_rt_availability),
server_group_id=decode(p_server_group_id,FND_API.G_MISS_NUM, NULL, NULL,server_group_id,p_server_group_id),
dns_name=decode(p_dns_name,FND_API.G_MISS_CHAR, NULL, NULL,dns_name,p_dns_name),
ip_address=decode(p_ip_address,FND_API.G_MISS_CHAR, NULL, NULL,ip_address,p_ip_address),
port=decode(p_port,FND_API.G_MISS_NUM, NULL, NULL,port,p_port),
LAST_UPDATE_DATE = sysdate,
LAST_UPDATED_BY = l_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = l_LAST_UPDATE_LOGIN,
ATTRIBUTE1 = decode( p_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE1, p_ATTRIBUTE1),
ATTRIBUTE2 = decode( p_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE2, p_ATTRIBUTE2),
ATTRIBUTE3 = decode( p_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE3, p_ATTRIBUTE3),
ATTRIBUTE4 = decode( p_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE4, p_ATTRIBUTE4),
ATTRIBUTE5 = decode( p_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE5, p_ATTRIBUTE5),
ATTRIBUTE6 = decode( p_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE6, p_ATTRIBUTE6),
ATTRIBUTE7 = decode( p_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE7, p_ATTRIBUTE7),
ATTRIBUTE8 = decode( p_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE8, p_ATTRIBUTE8),
ATTRIBUTE9 = decode( p_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE9, p_ATTRIBUTE9),
ATTRIBUTE10 = decode( p_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE10, p_ATTRIBUTE10),
ATTRIBUTE11 = decode( p_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE11, p_ATTRIBUTE11),
ATTRIBUTE12 = decode( p_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE12, p_ATTRIBUTE12),
ATTRIBUTE13 = decode( p_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE13, p_ATTRIBUTE13),
ATTRIBUTE14 = decode( p_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE14, p_ATTRIBUTE14),
ATTRIBUTE15 = decode( p_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, NULL, ATTRIBUTE15, p_ATTRIBUTE15)
where EMAIL_SERVER_ID=p_email_server_id;
ROLLBACK TO update_item_pvt;
ROLLBACK TO update_item_pvt;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
ROLLBACK TO update_item_PVT;
END update_item_sss;
PROCEDURE update_item_wrap_sss (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_email_server_id IN NUMBER,
p_server_name IN varchar2,
p_dns_name IN VARCHAR2,
p_ip_address IN VARCHAR2,
p_port IN NUMBER,
p_server_type_id IN number,
p_rt_availability in varchar2,
p_server_group_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_wrap_sss';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
IEM_EMAIL_SERVERS_PVT.update_item_sss(
p_api_version_number =>1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit=>FND_API.G_FALSE,
p_email_server_id => p_email_server_id,
p_server_name => p_server_name,
p_dns_name => p_dns_name,
p_ip_address => p_ip_address,
p_port => p_port,
p_server_type_id => p_server_type_id,
p_rt_availability => p_rt_availability,
p_server_group_id => p_server_group_id,
p_LAST_UPDATED_BY =>TO_NUMBER (FND_PROFILE.VALUE('USER_ID')),
p_LAST_UPDATE_DATE =>SYSDATE,
p_LAST_UPDATE_LOGIN=>TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ID')) ,
p_ATTRIBUTE1 =>null,
p_ATTRIBUTE2 =>null,
p_ATTRIBUTE3 =>null,
p_ATTRIBUTE4 =>null,
p_ATTRIBUTE5 =>null,
p_ATTRIBUTE6 =>null,
p_ATTRIBUTE7 =>null,
p_ATTRIBUTE8 =>null,
p_ATTRIBUTE9 =>null,
p_ATTRIBUTE10 =>null,
p_ATTRIBUTE11 =>null,
p_ATTRIBUTE12 =>null,
p_ATTRIBUTE13 =>null,
p_ATTRIBUTE14 =>null,
p_ATTRIBUTE15 =>null,
x_return_status =>l_stat,
x_msg_count => l_count,
x_msg_data => l_data);
-- Update Cache Audit Trail
select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=p_server_type_id;
select email_server_id into l_email_server_id from iem_email_servers
where UPPER(dns_name)=UPPER(p_dns_name)
and ip_address=p_ip_address and port=p_port;
p_param => 'UPDATE',
p_value => l_email_server_id,
x_return_status => l_stat,
x_msg_count => l_count,
x_msg_data => l_data
);
END update_item_wrap_sss;
PROCEDURE delete_item_batch
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_group_tbl IN jtf_varchar2_Table_100,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
i INTEGER;
l_api_name varchar2(30):='delete_item_batch';
SERVER_GROUP_NOT_DELETED EXCEPTION;
SAVEPOINT delete_item_batch;
-- Update Cache Audit Trail
select server_type_id into l_server_type_id from iem_email_servers WHERE EMAIL_SERVER_ID =l_email_server_id;
select email_server_type into l_email_server_type from iem_email_server_types where email_server_type_id=l_server_type_id;
p_param => 'DELETE',
p_value => l_email_server_id,
x_return_status => l_stat,
x_msg_count => l_count,
x_msg_data => l_data
);
DELETE FROM IEM_EMAIL_SERVERS
WHERE EMAIL_SERVER_ID =l_email_server_id;
WHEN SERVER_GROUP_NOT_DELETED THEN
ROLLBACK TO delete_item_batch;
FND_MESSAGE.SET_NAME('IEM', 'IEM_SERVER_GROUP_NOT_DELETED');
ROLLBACK TO delete_item_batch;
ROLLBACK TO delete_item_batch;
ROLLBACK TO delete_item_batch;
END delete_item_batch;