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') ) ;
PROCEDURE delete_acct_route_by_acct
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_email_account_id IN NUMBER,
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_acct_route_by_acct';
IEM_ROUTE_NOT_DELETED EXCEPTION;
SAVEPOINT delete_acct_route_by_acct;
DELETE
FROM IEM_ACCOUNT_ROUTES
WHERE email_account_id = p_email_account_id;
ROLLBACK TO delete_acct_route_by_acct;
ROLLBACK TO delete_acct_route_by_acct;
ROLLBACK TO delete_acct_route_by_acct;
END delete_acct_route_by_acct;
PROCEDURE delete_item_batch
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_ids_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';
select email_account_id from iem_account_routes where route_id = l_route_id;
IEM_ROUTE_NOT_DELETED EXCEPTION;
SAVEPOINT delete_item_batch;
DELETE
FROM IEM_ROUTES
WHERE route_id = p_route_ids_tbl(i);
raise IEM_ROUTE_NOT_DELETED;
Update iem_account_routes set priority=priority-1
where email_account_id=acct_id.email_account_id and priority > (Select priority from iem_account_routes
where route_id=p_route_ids_tbl(i) and email_account_id = acct_id.email_account_id);
DELETE
FROM IEM_ACCOUNT_ROUTES
WHERE route_id = p_route_ids_tbl(i);
DELETE
FROM IEM_ROUTE_RULES
WHERE route_id=p_route_ids_tbl(i);
WHEN IEM_ROUTE_NOT_DELETED THEN
ROLLBACK TO delete_item_batch;
FND_MESSAGE.SET_NAME('IEM', 'IEM_ROUTE_NOT_DELETED');
ROLLBACK TO delete_item_batch;
ROLLBACK TO delete_item_batch;
ROLLBACK TO delete_item_batch;
END delete_item_batch;
select count(*) into l_name_count from iem_routes where UPPER(name) = UPPER(p_name);
SELECT IEM_ROUTES_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ROUTES
(
ROUTE_ID,
NAME,
DESCRIPTION,
BOOLEAN_TYPE_CODE,
PROCEDURE_NAME,
all_email,
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_name,
l_description,
p_boolean_type_code,
l_proc_name,
l_all_email,
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_ROUTE_RULES_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ROUTE_RULES
(
ROUTE_RULE_ID,
ROUTE_ID,
KEY_TYPE_CODE,
OPERATOR_TYPE_CODE,
VALUE,
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_route_id,
p_key_type_code,
p_operator_type_code,
p_value,
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_ACCOUNT_ROUTES_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ACCOUNT_ROUTES
(
ROUTE_ID,
EMAIL_ACCOUNT_ID,
ACCOUNT_ROUTE_ID,
DESTINATION_GROUP_ID,
DEFAULT_GROUP_ID,
ENABLED_FLAG,
PRIORITY,
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
(
p_route_id,
p_email_account_id,
l_seq_id,
p_destination_group_id,
p_default_grp_id,
p_enabled_flag,
p_priority,
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 update_item_wrap (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_id IN NUMBER,
p_name IN VARCHAR2:= null,
p_ruling_chain IN VARCHAR2:= null,
p_description IN VARCHAR2:= null,
p_procedure_name IN VARCHAR2:= null,
p_all_emails IN VARCHAR2:= null,
--below is the data for update
p_update_rule_ids_tbl IN jtf_varchar2_Table_100,
p_update_rule_keys_tbl IN jtf_varchar2_Table_100,
p_update_rule_operators_tbl IN jtf_varchar2_Table_100,
p_update_rule_values_tbl IN jtf_varchar2_Table_300,
--below is the data for insert
p_new_rule_keys_tbl IN jtf_varchar2_Table_100,
p_new_rule_operators_tbl IN jtf_varchar2_Table_100,
p_new_rule_values_tbl IN jtf_varchar2_Table_300,
--below is the data to be removed
p_remove_rule_ids_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
l_api_name VARCHAR2(255):='update_item_wrap';
IEM_NO_ROUTE_UPDATE EXCEPTION;
IEM_NO_RULE_UPDATE EXCEPTION;
IEM_RULE_NOT_DELETED EXCEPTION;
SAVEPOINT update_item_wrap;
select count(*) into l_route from iem_routes where route_id = p_route_id;
raise IEM_NO_ROUTE_UPDATE;
l_return_type := p_update_rule_keys_tbl(1);
iem_route_pvt.update_item_route(
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_route_id => p_route_id,
p_name => p_name,
p_all_emails => p_all_emails,
p_description =>l_description,
p_ruling_chain =>p_ruling_chain,
p_proc_name => l_proc_name,
p_return_type => l_return_type,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
if ( p_update_rule_ids_tbl.count <>0 ) then
FOR i IN p_update_rule_ids_tbl.FIRST..p_update_rule_ids_tbl.LAST loop
iem_route_pvt.update_item_rule(p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_route_rule_id => p_update_rule_ids_tbl(i),
p_key_type_code =>p_update_rule_keys_tbl(i),
p_operator_type_code =>p_update_rule_operators_tbl(i),
p_value => p_update_rule_values_tbl(i),
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
raise IEM_NO_RULE_UPDATE;
DELETE
FROM IEM_ROUTE_RULES
WHERE route_rule_id = p_remove_rule_ids_tbl(i);
raise IEM_RULE_NOT_DELETED;
select count(*) into l_rule_count from iem_route_rules where route_id = p_route_id;
ROLLBACK TO update_item_wrap;
WHEN IEM_NO_ROUTE_UPDATE THEN
ROLLBACK TO update_item_wrap;
FND_MESSAGE.SET_NAME('IEM','IEM_NO_ROUTE_UPDATE');
WHEN IEM_NO_RULE_UPDATE THEN
ROLLBACK TO update_item_wrap;
FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
WHEN IEM_RULE_NOT_DELETED THEN
ROLLBACK TO update_item_wrap;
FND_MESSAGE.SET_NAME('IEM','IEM_RULE_NOT_DELETED');
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
END update_item_wrap;
PROCEDURE update_item_route (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_id IN NUMBER,
p_name IN VARCHAR2:= null,
p_description IN VARCHAR2:= null,
p_all_emails IN VARCHAR2:= null,
p_proc_name IN VARCHAR2:= null,
p_return_type IN VARCHAR2:= null,
p_ruling_chain IN VARCHAR2:= null,
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_route';
SAVEPOINT update_item_route;
select count(*) into l_name_count from iem_routes where UPPER(name) = UPPER(p_name) and route_id <> p_route_id;
update IEM_ROUTES
set
name=decode(p_name,null,name,p_name),
description=decode(p_description,FND_API.G_MISS_CHAR,null,null,description,p_description),
boolean_type_code=decode(p_ruling_chain,null,boolean_type_code,p_ruling_chain),
procedure_name=decode(l_proc_name,FND_API.G_MISS_CHAR,null,null,procedure_name,l_proc_name),
all_email=decode(p_all_emails,FND_API.G_MISS_CHAR,null,null,all_email,p_all_emails),
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 route_id=p_route_id;
ROLLBACK TO update_item_route;
ROLLBACK TO update_item_route;
ROLLBACK TO update_item_route;
ROLLBACK TO update_item_route;
ROLLBACK TO update_item_route;
ROLLBACK TO update_item_route;
END update_item_route;
PROCEDURE update_item_rule
(p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_rule_id IN NUMBER := null,
p_key_type_code IN VARCHAR2:= null,
p_operator_type_code IN VARCHAR2:= null,
p_value IN VARCHAR2:= null,
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_rule';
IEM_NO_RULE_UPDATE EXCEPTION;
SAVEPOINT update_item_rule;
select count(*) into l_rule from iem_route_rules
where route_rule_id = p_route_rule_id;
raise IEM_NO_RULE_UPDATE;
update IEM_ROUTE_RULES
set
key_type_code=decode(p_key_type_code,null,key_type_code,p_key_type_code),
operator_type_code=decode(p_operator_type_code,null,operator_type_code,p_operator_type_code),
value=decode(p_value,null,value,p_value),
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 route_rule_id=p_route_rule_id;
WHEN IEM_NO_RULE_UPDATE THEN
ROLLBACK TO update_item_rule;
FND_MESSAGE.SET_NAME('IEM','IEM_NO_RULE_UPDATE');
ROLLBACK TO update_item_rule;
ROLLBACK TO update_item_rule;
ROLLBACK TO update_item_rule;
ROLLBACK TO update_item_rule;
END update_item_rule;
IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
select count(*) into l_route from iem_routes
where route_id = p_route_id;
select count(*) into l_account from iem_mstemail_accounts
where email_account_id = p_email_account_id;
raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
WHEN IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
ROLLBACK TO create_wrap_account_routes_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
PROCEDURE update_account_routes(p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_id IN NUMBER,
p_email_account_id IN NUMBER,
p_destination_grp_id IN VARCHAR2:= null,
p_default_grp_id IN VARCHAR2:= null,
p_enabled_flag IN VARCHAR2:= null,
p_priority IN VARCHAR2:= null,
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_account_routes';
l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
l_LAST_UPDATE_DATE DATE:=SYSDATE;
l_LAST_UPDATE_LOGIN NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('LOGIN_ ID')) ;
SAVEPOINT update_account_routes_PVT;
Select count(*) into l_route_cnt from iem_routes
where route_id=p_route_id;
Select count(*) into l_acct_cnt from iem_mstemail_accounts
where email_account_id=p_email_account_id;
update IEM_ACCOUNT_ROUTES
set
destination_group_id = decode(p_destination_grp_id,null,destination_group_id,p_destination_grp_id),
default_group_id =decode(p_default_grp_id,null,default_group_id,p_default_grp_id),
enabled_flag=decode(p_enabled_flag,null,enabled_flag,p_enabled_flag),
priority=decode(p_priority,null,priority,p_priority),
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 route_id = p_route_id and email_account_id = p_email_account_id;
ROLLBACK TO update_account_routes_PVT;
ROLLBACK TO update_account_routes_PVT;
ROLLBACK TO update_account_routes_PVT;
ROLLBACK TO update_account_routes_PVT;
END update_account_routes;
PROCEDURE delete_acct_route_batch
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_ids_tbl IN jtf_varchar2_Table_100,
p_account_id IN NUMBER,
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_acct_route_batch';
IEM_ACCOUNT_ROUTE_NOT_DELETED EXCEPTION;
SAVEPOINT delete_acct_route_batch;
Update iem_account_routes set priority=priority-1
where email_account_id=p_account_id and priority > (Select priority from iem_account_routes
where route_id=p_route_ids_tbl(i) and email_account_id=p_account_id);
DELETE
FROM IEM_ACCOUNT_ROUTES
WHERE route_id = p_route_ids_tbl(i) and email_account_id = p_account_id;
WHEN IEM_ACCOUNT_ROUTE_NOT_DELETED THEN
ROLLBACK TO delete_acct_route_batch;
FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCOUNT_ROUTE_NOT_DELETED');
ROLLBACK TO delete_acct_route_batch;
ROLLBACK TO delete_acct_route_batch;
ROLLBACK TO delete_acct_route_batch;
END delete_acct_route_batch;
PROCEDURE update_wrap_account_routes
(p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_email_account_id IN NUMBER,
p_route_ids_tbl IN jtf_varchar2_Table_100,
p_upd_dest_ids_tbl IN jtf_varchar2_Table_100,
p_upd_default_ids_tbl IN jtf_varchar2_Table_100,
p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
--p_upd_priority_tbl IN jtf_varchar2_Table_100,
p_delete_route_ids_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
l_api_name VARCHAR2(255):='update_wrap_account_routes';
IEM_ACCOUNT_ROUTE_NOT_DELETED EXCEPTION;
IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
SAVEPOINT update_wrap_acct_routes_1_PVT;
iem_route_pvt.update_account_routes (p_api_version_number =>p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_route_id => p_route_ids_tbl(i),
p_email_account_id => p_email_account_id,
p_destination_grp_id => p_upd_dest_ids_tbl(i),
p_default_grp_id =>p_upd_default_ids_tbl(i),
p_enabled_flag => p_upd_enable_flag_tbl(i),
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
raise IEM_ACCOUNT_ROUTE_NOT_UPDATED;
SAVEPOINT update_wrap_acct_routes_2_PVT;
iem_route_pvt.delete_acct_route_batch
(p_api_version_number => p_api_version_number,
P_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_route_ids_tbl => p_delete_route_ids_tbl,
p_account_id => p_email_account_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
raise IEM_ACCOUNT_ROUTE_NOT_DELETED;
WHEN IEM_ACCOUNT_ROUTE_NOT_UPDATED THEN
ROLLBACK TO update_wrap_acct_routes_1_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_UPDATED');
WHEN IEM_ACCOUNT_ROUTE_NOT_DELETED THEN
ROLLBACK TO update_wrap_acct_routes_2_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_ROUTE_NOT_DELETED');
ROLLBACK TO update_wrap_acct_routes_2_PVT;
ROLLBACK TO update_wrap_acct_routes_2_PVT;
ROLLBACK TO update_wrap_acct_routes_2_PVT;
END update_wrap_account_routes;