[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_autoDelete OUT NOCOPY emailProc_tbl,
x_autoAck OUT NOCOPY emailProc_tbl,
x_autoProc OUT NOCOPY emailProc_tbl,
x_redirect OUT NOCOPY emailProc_tbl,
x_3Rs OUT NOCOPY emailProc_tbl,
x_document OUT NOCOPY emailProc_tbl,
x_route OUT NOCOPY emailProc_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name varchar2(30):='loadEmailProc';
l_autoDeletes emailProc_tbl;
IEM_TAG_NOT_DELETED EXCEPTION;
select rt.route_classification_id, rt.name, rt.description, fu.user_name,
to_char(rt.creation_date) creation_date, rt.boolean_type_code
from iem_route_classifications rt, fnd_user fu
where fu.user_id = rt.created_by and rt.route_classification_id<>0 and rt.deleted_flag='N'
order by UPPER(name) asc;
select rt.route_id, rt.name, rt.description, rt.boolean_type_code, fu.user_name, to_char(rt.creation_date) creation_date
from iem_routes rt, fnd_user fu where fu.user_id = rt.created_by
order by UPPER(rt.name) asc;
select ep.emailproc_id, ep.name, ep.description, ep.rule_type, fu.user_name, to_char(ep.creation_date) creation_date
from iem_emailprocs ep, fnd_user fu
where ep.created_by=fu.user_id and ep.rule_type= v_rule_type
order by UPPER(name);
FOR v_emailProcs IN c_emailProcs('AUTODELETE') LOOP
l_autoDeletes(x).emailProc_id := v_emailProcs.emailproc_id;
l_autoDeletes(x).name := v_emailProcs.name;
l_autoDeletes(x).description := v_emailProcs.description;
l_autoDeletes(x).rule_type := 'AUTODELETE';
l_autoDeletes(x).created_by := v_emailProcs.user_name;
l_autoDeletes(x).creation_date := v_emailProcs.creation_date;
select action into l_action from iem_actions
where emailproc_id = v_emailProcs.emailproc_id;
select action into l_action from iem_actions
where emailproc_id = v_emailProcs.emailproc_id;
select action into l_action from iem_actions
where emailproc_id = v_emailProcs.emailproc_id;
x_autoDelete := l_autoDeletes;
x_autoDelete OUT NOCOPY acctEmailProc_tbl,
x_autoAck OUT NOCOPY acctEmailProc_tbl,
x_autoProc OUT NOCOPY acctEmailProc_tbl,
x_redirect OUT NOCOPY acctEmailProc_tbl,
x_3Rs OUT NOCOPY acctEmailProc_tbl,
x_document OUT NOCOPY acctEmailProc_tbl,
x_route OUT NOCOPY acctEmailProc_tbl,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_api_name varchar2(30):='loadAcctEmailProc';
l_autoDeletes acctEmailProc_tbl;
IEM_TAG_NOT_DELETED EXCEPTION;
select a.account_route_class_id, a.priority, r.route_classification_id,
r.name,r.description, r.boolean_type_code, a.enabled_flag
from iem_route_classifications r, iem_account_route_class a
where r.route_classification_id=a.route_classification_id
and a.email_account_id = v_acct_Id and r.route_classification_id<>0 and r.deleted_flag='N'
order by a.priority asc;
select a.account_emailProc_id, a.priority, r.emailproc_id, r.name,r.description,
a.enabled_flag
from iem_emailprocs r, iem_account_emailprocs a
where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
and r.rule_type = v_rule_type order by a.priority asc;
select a.account_emailProc_id, a.priority, r.emailproc_id, r.name, r.description,
b.action, a.enabled_flag
from iem_emailprocs r, iem_account_emailprocs a, iem_actions b
where r.emailproc_id = a.emailproc_id and a.email_account_id = v_acct_Id
and r.rule_type = v_rule_type and a.emailproc_id = b.emailproc_id order by a.priority asc;
select a.account_route_id, a.priority, r.route_id, r.name,r.description,
r.boolean_type_code, a.enabled_flag
from iem_routes r, iem_account_routes a
where r.route_id=a.route_id and a.email_account_id = v_acct_Id
order by a.priority asc;
FOR v_emailProcs IN c_emailProcs(p_acct_id,'AUTODELETE') LOOP
l_autoDeletes(x).account_emailProc_id := v_emailProcs.account_emailProc_id;
l_autoDeletes(x).emailProc_id := v_emailProcs.emailproc_id;
l_autoDeletes(x).name := v_emailProcs.name;
l_autoDeletes(x).description := v_emailProcs.description;
l_autoDeletes(x).rule_type := 'AUTODELETE';
l_autoDeletes(x).priority := v_emailProcs.priority;
l_autoDeletes(x).enabled_flag := v_emailProcs.enabled_flag;
x_autoDelete := l_autoDeletes;
PROCEDURE deleteAcctEmailProc (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_acct_id IN NUMBER,
p_rule_type In VARCHAR2,
p_emailProc_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):='deleteAcctEmailProc';
l_delete_class_ids_tbl jtf_varchar2_Table_100:=jtf_varchar2_Table_100();
IEM_EMAILPROC_NOT_DELETED EXCEPTION;
select action_id from iem_actions where emailproc_id = v_emailProc_id;
l_delete_class_ids_tbl.extend;
l_delete_class_ids_tbl(1) := to_char(p_emailProc_id);
if ( l_delete_class_ids_tbl.count <> 0 ) then
iem_route_class_pvt.delete_acct_class_batch
(p_api_version_number => p_api_version_number,
P_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_class_ids_tbl => l_delete_class_ids_tbl,
p_account_id => p_acct_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
Update iem_account_routes set priority=priority-1
where email_account_id=p_acct_id
and priority >
( Select priority from iem_account_routes
where route_id=p_emailProc_id
and email_account_id=p_acct_id);
DELETE
FROM IEM_ACCOUNT_ROUTES
WHERE route_id = p_emailProc_id and email_account_id = p_acct_id;
Update iem_account_emailprocs set priority=priority-1
where email_account_id=p_acct_id
and priority >
( Select priority from iem_account_emailprocs
where emailproc_id=p_emailProc_id
and email_account_id=p_acct_id)
and emailproc_id in
( select emailproc_id from iem_emailprocs
where rule_type = p_rule_type );
DELETE
FROM iem_account_emailprocs
WHERE emailproc_id = p_emailProc_id and email_account_id = p_acct_id;
PROCEDURE update_emailproc_wrap (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_emailproc_id IN NUMBER,
p_name IN VARCHAR2:= null,
p_ruling_chain IN VARCHAR2:= null,
p_description IN VARCHAR2:= null,
p_all_email IN VARCHAR2:= null,
p_rule_type 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,
--below is the action and action parameter to be updated
p_action IN VARCHAR2 := null,
p_parameter1_tbl IN jtf_varchar2_Table_300,
p_parameter2_tbl IN jtf_varchar2_Table_300,
p_parameter3_tbl IN jtf_varchar2_Table_300,
p_parameter_tag_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_emailproc_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_emailprocs where emailproc_id = p_emailproc_id;
raise IEM_NO_ROUTE_UPDATE;
select count(*) into l_redirect_same_acct
from iem_account_emailprocs
where email_account_id=p_parameter1_tbl(1) and emailproc_id=p_emailproc_id;
iem_emailproc_hdl_pvt.update_item_emailproc(
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_emailproc_id => p_emailproc_id,
p_name => p_name,
p_description =>p_description,
p_ruling_chain =>p_ruling_chain,
p_all_email => p_all_email,
p_rule_type => p_rule_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_emailproc_hdl_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_emailproc_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_EMAILPROC_RULES
WHERE emailproc_rule_id = p_remove_rule_ids_tbl(i);
raise IEM_RULE_NOT_DELETED;
select all_email into l_all_emails
from iem_emailprocs where emailproc_id = p_emailproc_id;
select count(*) into l_rule_count from iem_emailproc_rules where emailproc_id = p_emailproc_id;
select action_id into l_action_id from iem_actions where emailproc_id = p_emailproc_id;
update iem_actions set action=p_action where action_id = l_action_id;
delete from iem_action_dtls where action_id = l_action_id;
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
ROLLBACK TO update_item_wrap;
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;
ROLLBACK TO update_item_wrap;
END update_emailproc_wrap;
IEM_ACCOUNT_ROUTE_NOT_UPDATED EXCEPTION;
select count(*) into l_count from iem_emailprocs
where emailproc_id = p_emailproc_id;
select count(*) into l_account from iem_mstemail_accounts
where email_account_id = p_email_account_id;
select count(*) into l_redirect_same_acct
from iem_emailprocs a, iem_actions b, iem_action_dtls c
where a.emailproc_id = p_emailproc_id and a.emailproc_id=b.emailproc_id
and a.rule_type='AUTOREDIRECT' and b.action='AUTOREDIRECT_INTERNAL'
and b.action_id=c.action_id and c.parameter1=to_char(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_wrap_account_emailprocs (
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_emailproc_ids_tbl IN jtf_varchar2_Table_100,
p_upd_enable_flag_tbl IN jtf_varchar2_Table_100,
p_delete_emailproc_ids_tbl IN jtf_varchar2_Table_100,
p_rule_type IN 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_wrap_account_emailprocs';
IEM_ACCT_EMAILPROC_NOT_DELETED EXCEPTION;
IEM_ACCT_EMAILPROC_NOT_UPDATED EXCEPTION;
SAVEPOINT update_wrap_acct_emailproc_PVT;
iem_emailproc_hdl_pvt.update_account_emailprocs
(p_api_version_number =>p_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_emailproc_id => p_emailproc_ids_tbl(i),
p_email_account_id => p_email_account_id,
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_ACCT_EMAILPROC_NOT_UPDATED;
if ( p_delete_emailproc_ids_tbl.count <> 0 ) then
iem_emailproc_hdl_pvt.delete_acct_emailproc_batch
(p_api_version_number => p_api_version_number,
P_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_TRUE,
p_emailproc_ids_tbl => p_delete_emailproc_ids_tbl,
p_account_id => p_email_account_id,
p_rule_type => p_rule_type,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data) ;
raise IEM_ACCT_EMAILPROC_NOT_DELETED;
WHEN IEM_ACCT_EMAILPROC_NOT_UPDATED THEN
ROLLBACK TO update_wrap_acct_emailproc_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_UPDATED');
WHEN IEM_ACCT_EMAILPROC_NOT_DELETED THEN
ROLLBACK TO update_wrap_acct_emailproc_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCT_EMAILPROC_NOT_DELETED');
ROLLBACK TO update_wrap_acct_emailproc_PVT;
ROLLBACK TO update_wrap_acct_emailproc_PVT;
ROLLBACK TO update_wrap_acct_emailproc_PVT;
END update_wrap_account_emailprocs;
PROCEDURE delete_item_emailproc
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_emailproc_id IN NUMBER,
p_rule_type IN VARCHAR2,
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_emailprocs where emailproc_id = l_emailproc_id;
select action_id from iem_actions where emailproc_id = l_emailproc_id;
IEM_ROUTE_NOT_DELETED EXCEPTION;
SAVEPOINT delete_item_batch;
DELETE
FROM IEM_EMAILPROCS
WHERE emailproc_id = p_emailproc_id;
raise IEM_ROUTE_NOT_DELETED;
Update iem_account_emailprocs set priority=priority-1
where email_account_id=acct_id.email_account_id
and emailproc_id in
( select emailproc_id
from iem_emailprocs
where rule_type=p_rule_type )
and priority > (Select priority from iem_account_emailprocs
where emailproc_id=p_emailproc_id and email_account_id = acct_id.email_account_id);
DELETE
FROM iem_account_emailprocs
WHERE emailproc_id = p_emailproc_id;
DELETE
FROM IEM_EMAILPROC_RULES
WHERE emailproc_id=p_emailproc_id;
delete from iem_action_dtls where action_id = v_action_id.action_id;
delete from iem_actions where emailproc_id = p_emailproc_id;
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_emailproc;
PROCEDURE delete_acct_emailproc_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_emailproc_by_acct';
SAVEPOINT delete_acct_emailproc_by_acct;
DELETE
FROM IEM_ACCOUNT_EMAILPROCS
WHERE email_account_id = p_email_account_id;
ROLLBACK TO delete_acct_emailproc_by_acct;
ROLLBACK TO delete_acct_emailproc_by_acct;
ROLLBACK TO delete_acct_emailproc_by_acct;
END delete_acct_emailproc_by_acct;