[Home] [Help]
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_item_batch
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_class_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';
l_undeleted_class_name_1 varchar2(30);
l_undeleted_class_name varchar2(3000);
select unique email_account_id from iem_account_route_class where route_classification_id = l_classification_id;
IEM_RT_CLASS_NOT_DELETED EXCEPTION;
SAVEPOINT delete_item_batch;
-- select count(*) into l_count_msg_postmdt from iem_post_mdts where rt_classification_id=p_class_ids_tbl(i);
select count(*) into l_count_msg_postmdt from iem_rt_proc_emails where rt_classification_id=p_class_ids_tbl(i);
select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_ROUTE_CLASS_PVT.delete_item_batch.START', logMessage);
Update iem_account_route_class ac set ac.priority=ac.priority-1
where ac.email_account_id=acct_id.email_account_id and ac.priority > (Select be.priority from iem_account_route_class be
where be.route_classification_id=p_class_ids_tbl(i) and be.email_account_id = acct_id.email_account_id);
UPDATE IEM_ROUTE_CLASSIFICATIONS
SET DELETED_FLAG='Y'
WHERE route_classification_id = p_class_ids_tbl(i);
DELETE
FROM IEM_ROUTE_CLASS_RULES
WHERE route_classification_id = p_class_ids_tbl(i);
DELETE
FROM IEM_ACCOUNT_ROUTE_CLASS
WHERE route_classification_id = p_class_ids_tbl(i);
if l_undeleted_class_name is not null then
l_undeleted_class_name := RTRIM(l_undeleted_class_name, ', ');
FND_MESSAGE.SET_TOKEN('CLASSIFICATION', l_undeleted_class_name);
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_route_classifications where UPPER(name) = UPPER(p_name) and deleted_flag='N';
SELECT IEM_ROUTE_CLASSIFICATIONS_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ROUTE_CLASSIFICATIONS
(
ROUTE_CLASSIFICATION_ID,
NAME,
DESCRIPTION,
BOOLEAN_TYPE_CODE,
procedure_name,
deleted_flag,
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
(
G_CLASS_ID,
p_name,
l_description,
p_boolean_type_code,
l_proc_name,
'N',
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_CLASS_RULES_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ROUTE_CLASS_RULES
(
ROUTE_CLASS_RULE_ID,
ROUTE_CLASSIFICATION_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_class_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)
);
PROCEDURE update_item_wrap (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_class_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,
--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_RT_CLASS_UPDATE EXCEPTION;
IEM_NO_RULE_UPDATE EXCEPTION;
IEM_RULE_NOT_DELETED EXCEPTION;
SAVEPOINT update_item_wrap;
select count(*) into l_class from iem_route_classifications where route_classification_id = p_class_id;
raise IEM_NO_RT_CLASS_UPDATE;
l_return_type := p_update_rule_keys_tbl(1);
iem_route_class_pvt.update_item_class(
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_class_id => p_class_id,
p_description =>p_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);
raise IEM_NO_RT_CLASS_UPDATE;
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_class_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_class_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_CLASS_RULES
WHERE route_class_rule_id = p_remove_rule_ids_tbl(i);
raise IEM_RULE_NOT_DELETED;
select count(*) into l_rule_count from iem_route_class_rules where route_classification_id = p_class_id;
WHEN IEM_NO_RT_CLASS_UPDATE THEN
ROLLBACK TO update_item_wrap;
FND_MESSAGE.SET_NAME('IEM','IEM_NO_RT_CLASS_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_class (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_class_id IN NUMBER ,
p_proc_name IN VARCHAR2:= null,
p_return_type IN VARCHAR2:= null,
p_description 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_class';
SAVEPOINT update_item_class;
/* select count(*) into l_name_count from iem_route_classifications where UPPER(name) = UPPER(p_name) and route_classification_id <> p_class_id;
update IEM_ROUTE_CLASSIFICATIONS
set
description=decode(l_description,FND_API.G_MISS_CHAR,description,l_description),
boolean_type_code=decode(l_ruling_chain,FND_API.G_MISS_CHAR,boolean_type_code,l_ruling_chain),
procedure_name=decode(l_proc_name,FND_API.G_MISS_CHAR,procedure_name,l_proc_name),
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_classification_id=p_class_id;
ROLLBACK TO update_item_class;
ROLLBACK TO update_item_class;
ROLLBACK TO update_item_class;
ROLLBACK TO update_item_class;
ROLLBACK TO update_item_class;
ROLLBACK TO update_item_class;
END update_item_class;
PROCEDURE update_item_rule (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_route_class_rule_id IN NUMBER ,
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_class_rules
where route_class_rule_id = p_route_class_rule_id;
raise IEM_NO_RULE_UPDATE;
update IEM_ROUTE_CLASS_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_class_rule_id=p_route_class_rule_id;
ROLLBACK TO update_item_rule;
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_RT_ClASS_ACCT_NOT_UPDATED EXCEPTION;
select count(*) into l_class from iem_route_classifications
where route_classification_id = p_class_id;
select count(*) into l_account from iem_mstemail_accounts
where email_account_id = p_email_account_id and deleted_flag='N';
raise IEM_RT_ClASS_ACCT_NOT_UPDATED;
select name into l_class_name from iem_route_classifications where route_classification_id = p_class_id;
WHEN IEM_RT_ClASS_ACCT_NOT_UPDATED THEN
ROLLBACK TO create_wrap_acct_rt_class_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_RT_ClASS_ACCT_NOT_UPDATED');
SELECT IEM_ACCOUNT_ROUTE_CLASS_s1.nextval
INTO l_seq_id
FROM dual;
INSERT INTO IEM_ACCOUNT_ROUTE_CLASS
(
ROUTE_CLASSIFICATION_ID,
EMAIL_ACCOUNT_ID,
ACCOUNT_ROUTE_CLASS_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_class_id,
p_email_account_id,
l_seq_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_wrap_account_class (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_class_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_class_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_class';
IEM_ACCOUNT_CLASS_NOT_DELETED EXCEPTION;
IEM_RT_CLASS_ACCT_NOT_UPDATE EXCEPTION;
SAVEPOINT update_wrap_acct_class_1_PVT;
iem_route_class_pvt.update_account_class (p_api_version_number =>p_api_version_number,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_TRUE,
p_class_id => p_class_ids_tbl(i),
p_email_account_id => p_email_account_id,
p_enabled_flag => p_upd_enable_flag_tbl(i),
--p_priority => p_upd_priority_tbl(i),
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
raise IEM_RT_CLASS_ACCT_NOT_UPDATE;
if ( p_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_TRUE,
p_class_ids_tbl => p_delete_class_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) ;
--ROLLBACK TO update_wrap_acct_class_2_PVT;
WHEN IEM_RT_CLASS_ACCT_NOT_UPDATE THEN
ROLLBACK TO update_wrap_acct_class_1_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_RT_CLASS_ACCT_NOT_UPDATE');
WHEN IEM_ACCOUNT_CLASS_NOT_DELETED THEN
ROLLBACK TO update_wrap_acct_class_2_PVT;
FND_MESSAGE.SET_NAME('IEM','IEM_ACCOUNT_CLASS_NOT_DELETED');
ROLLBACK TO update_wrap_acct_class_1_PVT;
ROLLBACK TO update_wrap_acct_class_1_PVT;
ROLLBACK TO update_wrap_acct_class_1_PVT;
END update_wrap_account_class;
PROCEDURE update_account_class(p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_class_id IN NUMBER ,
p_email_account_id IN NUMBER,
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_class';
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_class_PVT;
Select count(*) into l_class_cnt from iem_route_classifications
where route_classification_id=p_class_id;
Select count(*) into l_acct_cnt from iem_mstemail_accounts
where email_account_id=p_email_account_id and deleted_flag='N' ;
update IEM_ACCOUNT_ROUTE_CLASS
set
enabled_flag=decode(p_enabled_flag,FND_API.G_MISS_CHAR,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_classification_id = p_class_id and email_account_id = p_email_account_id;
ROLLBACK TO update_account_class_PVT;
ROLLBACK TO update_account_class_PVT;
ROLLBACK TO update_account_class_PVT;
ROLLBACK TO update_account_class_PVT;
ROLLBACK TO update_account_class_PVT;
END update_account_class;
PROCEDURE delete_acct_class_batch
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_class_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_class_batch';
l_undeleted_class_name varchar2(2000);
l_undeleted_class_name_1 varchar2(30);
IEM_ACCOUNT_CLASS_NOT_DELETED EXCEPTION;
SAVEPOINT delete_acct_class_batch;
select count(*) into l_count_msg_postmdt from iem_rt_proc_emails where rt_classification_id=p_class_ids_tbl(i) and email_account_id = p_account_id;
select name into l_undeleted_class_name_1 from iem_route_classifications where route_classification_id=p_class_ids_tbl(i);
l_undeleted_class_name := l_undeleted_class_name||l_undeleted_class_name_1||', ';
Update iem_account_route_class set priority=priority-1
where email_account_id = p_account_id and priority > (Select priority from iem_account_route_class
where route_classification_id=p_class_ids_tbl(i) and email_account_id=p_account_id);
DELETE
FROM IEM_ACCOUNT_ROUTE_CLASS
WHERE route_classification_id = p_class_ids_tbl(i) and email_account_id = p_account_id;
raise IEM_ACCOUNT_CLASS_NOT_DELETED;
if l_undeleted_class_name is not null then
l_undeleted_class_name := RTRIM(l_undeleted_class_name, ', ');
FND_MESSAGE.SET_TOKEN('CLASSIFICATION', l_undeleted_class_name);
WHEN IEM_ACCOUNT_CLASS_NOT_DELETED THEN
ROLLBACK TO delete_acct_class_batch;
FND_MESSAGE.SET_NAME('IEM', 'IEM_ACCOUNT_CLASS_NOT_DELETED');
ROLLBACK TO delete_acct_class_batch;
ROLLBACK TO delete_acct_class_batch;
ROLLBACK TO delete_acct_class_batch;
END delete_acct_class_batch;
PROCEDURE delete_folder_on_classId
(p_api_version_number IN NUMBER,
P_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_classification_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):='delete_folder_on_classId';
select unique email_account_id from iem_account_route_class where route_classification_id = l_classification_id;
IEM_UNEXPT_ERR_DELETE_FOLDER EXCEPTION;
SAVEPOINT delete_folder_on_classId;
ROLLBACK TO delete_folder_on_classId;
WHEN IEM_UNEXPT_ERR_DELETE_FOLDER THEN
ROLLBACK TO delete_folder_on_classId;
ROLLBACK TO delete_folder_on_classId;
ROLLBACK TO delete_folder_on_classId;
ROLLBACK TO delete_folder_on_classId;
END delete_folder_on_classId;
select DB_SERVER_ID
INTO l_db_server_id
FROM IEM_EMAIL_ACCOUNTS
WHERE email_account_id = p_email_account_id;
IEM_DB_CONNECTIONS_PVT.select_item(
p_api_version_number => 1.0,
p_db_server_id => l_db_server_id,
p_is_admin => 'P',
x_db_link => l_im_link1,
x_return_status => l_stat,
x_msg_count => l_count,
x_msg_data => l_data );
select email_user, domain, email_password into l_email_acct_Rec from iem_email_accounts
where email_account_id = p_email_account_id;
PROCEDURE delete_folder (
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_class_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) is
TYPE email_acct_Rec IS RECORD (
email_user iem_email_accounts.email_user%TYPE,
domain iem_email_accounts.domain%TYPE,
email_password iem_email_accounts.email_password%TYPE);
l_api_name VARCHAR2(255):='delete_folder';
SAVEPOINT delete_folder_PVT;
select DB_SERVER_ID
INTO l_db_server_id
FROM IEM_EMAIL_ACCOUNTS
WHERE email_account_id = p_email_account_id;
IEM_DB_CONNECTIONS_PVT.select_item(
p_api_version_number => 1.0,
p_db_server_id => l_db_server_id,
p_is_admin => 'P',
x_db_link => l_im_link1,
x_return_status => l_stat,
x_msg_count => l_count,
x_msg_data => l_data );
select email_user, domain, email_password into l_email_acct_Rec from iem_email_accounts
where email_account_id = p_email_account_id;
select name into l_folder from iem_route_classifications where route_classification_id = p_class_id;
l_str:='begin :l_ret:=im_api.deletefolder'||l_im_link||'(:a_folder);end;';
ROLLBACK TO delete_folder_PVT;
ROLLBACK TO delete_folder_PVT;
ROLLBACK TO delete_folder_PVT;
ROLLBACK TO delete_folder_PVT;
ROLLBACK TO delete_folder_PVT;
ROLLBACK TO delete_folder_PVT;
END delete_folder;
PROCEDURE delete_association_on_acctId
(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
l_api_name VARCHAR2(255):='delete_association_on_acctId';
select unique route_classification_id from iem_account_route_class where email_account_id = l_account_id;
IEM_UNEXPT_ERR_DELETE_FOLDER EXCEPTION;
SAVEPOINT delete_association_on_acctId;
delete from iem_account_route_class where email_account_id = p_email_account_id;
ROLLBACK TO delete_association_on_acctId;
WHEN IEM_UNEXPT_ERR_DELETE_FOLDER THEN
ROLLBACK TO delete_association_on_acctId;
FND_MESSAGE.SET_NAME('IEM', 'IEM_UNEXPT_ERR_DELETE_FOLDER');
ROLLBACK TO delete_association_on_acctId;
ROLLBACK TO delete_association_on_acctId;
ROLLBACK TO delete_association_on_acctId;
END delete_association_on_acctId;