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 UPDATE_DP_CONFIG_DATA_WRAP(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_email_acct_id IN NUMBER,
p_action IN VARCHAR2,
P_ACTIVE_FLAG IN varchar2 := null,
P_USER_NAME IN varchar2 := null,
P_USER_PASSWORD IN varchar2 := null,
P_IN_HOST IN varchar2 := null,
P_IN_PORT 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):='IS_DLPS_RUNNING';
l_is_acct_updated VARCHAR2(1);
SAVEPOINT UPDATE_DP_CONFIG_DATA_WRAP_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
l_is_acct_updated := 'Y';
IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
p_api_version_number => P_Api_Version_Number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => P_Commit,
p_email_acct_id => p_email_acct_id,
p_active_flag => p_active_flag,
p_is_acct_update => l_is_acct_updated,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
elsif ( p_action = 'update' ) then
select count(*) into l_count from iem_mstemail_accounts where email_account_id=p_email_acct_id;
CHECK_IF_ACCOUNT_UPDATED (
p_api_version_number => l_api_version_number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => P_Commit,
p_email_account_id => p_email_acct_id,
P_ACTIVE_FLAG => P_ACTIVE_FLAG,
P_USER_NAME => l_USER_NAME,
P_USER_PASSWORD => P_USER_PASSWORD,
P_IN_HOST => l_IN_HOST,
P_IN_PORT => l_IN_PORT,
x_is_data_changed => l_is_data_changed,
x_is_acct_updated => l_is_acct_updated,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
p_api_version_number => P_Api_Version_Number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => P_Commit,
p_email_acct_id => p_email_acct_id,
p_active_flag => p_active_flag,
p_is_acct_update => l_is_acct_updated,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
elsif ( p_action = 'delete' ) then
l_active_flag := 'N';
l_is_acct_updated := 'N';
IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA(
p_api_version_number => P_Api_Version_Number,
p_init_msg_list => FND_API.G_FALSE,
p_commit => P_Commit,
p_email_acct_id => p_email_acct_id,
p_active_flag => l_active_flag,
p_is_acct_update => l_is_acct_updated,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
logMessage := '[Failed when calling IEM_UPDATE_DP_CONFIG_DATA!]';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
ROLLBACK TO UPDATE_DP_CONFIG_DATA_WRAP_PVT;
END UPDATE_DP_CONFIG_DATA_WRAP;
PROCEDURE UPDATE_DP_CONFIG_DATA(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit IN VARCHAR2 := null,
p_email_acct_id IN NUMBER,
p_active_flag IN VARCHAR2,
p_is_acct_update 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):='IS_DLPS_RUNNING';
l_update_flag VARCHAR2(1);
l_has_updated VARCHAR2(1);
SAVEPOINT UPDATE_DP_CONFIG_DATA_PVT;
l_has_updated := 'N';
l_update_flag := p_is_acct_update;
select emta_config_param_id, action_type, email_account_id, account_update_flag
from iem_emta_config_params where flag='N' and email_account_id=p_email_acct_id for update nowait
)
LOOP
if x.account_update_flag = 'Y' then
update iem_emta_config_params set action_type = l_action,last_update_date = sysdate
where emta_config_param_id = x.emta_config_param_id;
l_has_updated := 'Y';
update iem_emta_config_params set action_type = l_action, account_update_flag=l_update_flag, last_update_date = sysdate
where emta_config_param_id = x.emta_config_param_id;
l_has_updated := 'Y';
if l_has_updated = 'N' then
select IEM_EMTA_CONFIG_PARAMS_S1.nextval into l_seq_id from dual;
INSERT INTO IEM_EMTA_CONFIG_PARAMS
(
EMTA_CONFIG_PARAM_ID,
EMAIL_ACCOUNT_ID,
ACTION_TYPE,
ACCOUNT_UPDATE_FLAG,
FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES
(
l_seq_id,
p_email_acct_id,
l_action,
l_update_flag,
'N',
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 UPDATE_DP_CONFIG_DATA_PVT;
ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
ROLLBACK TO UPDATE_DP_CONFIG_DATA_PVT;
END UPDATE_DP_CONFIG_DATA;
select emta_config_param_id, action_type, email_account_id, account_update_flag
from iem_emta_config_params where flag='N' for update nowait
)
LOOP
update IEM_EMTA_CONFIG_PARAMS set FLAG='A', LAST_UPDATE_DATE=SYSDATE
where emta_config_param_id=x.emta_config_param_id;
select count(*) into l_count
from iem_mstemail_accounts where email_account_id=x.email_account_id;
delete IEM_EMTA_CONFIG_PARAMS where emta_config_param_id=x.emta_config_param_id;
select * into account_rec
from iem_mstemail_accounts where email_account_id=x.email_account_id;
l_acct_info(Y).update_flag := x.account_update_flag;
PROCEDURE DELETE_ITEMS (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 := null,
p_commit 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(30):='GET_ACCOUNT_INFO';
delete iem_emta_config_params where flag='A';
x_is_acct_updated OUT NOCOPY varchar2 )
return boolean
is
l_data_changed boolean;
l_is_acct_updated varchar2(1);
l_is_acct_updated := 'N';
--select active_flag, user_name, user_password, encrypt_key, in_host, in_port
select active_flag, user_name, in_host, in_port
into l_active_flag, l_user_name, l_in_host, l_in_port
from iem_mstemail_accounts where email_account_id = p_email_account_id ;
l_is_acct_updated := 'Y';
x_is_acct_updated := l_is_acct_updated;
x_is_acct_updated := l_is_acct_updated;
if fnd_log.test(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP') then
logMessage := '[input data is invalid, no data inserted to IEM_EMTA_CONFIG_PARAMS,return true!]';
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.UPDATE_DP_CONFIG_DATA_WRAP', logMessage);
PROCEDURE CHECK_IF_ACCOUNT_UPDATED(
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_ACTIVE_FLAG IN varchar2,
P_USER_NAME IN varchar2,
P_USER_PASSWORD IN varchar2,
P_IN_HOST IN varchar2,
P_IN_PORT IN varchar2,
x_is_data_changed OUT NOCOPY varchar2,
x_is_acct_updated OUT NOCOPY 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):='CHECK_IF_ACCOUNT_UPDATED';
l_is_acct_updated varchar2(1);
SAVEPOINT CHECK_IF_ACCOUNT_UPDATED_PVT;
x_is_acct_updated := 'N';
l_is_acct_updated := 'N';
--select active_flag, user_name, user_password, encrypt_key, in_host, in_port
select active_flag, user_name, in_host, in_port
into l_active_flag, l_user_name, l_in_host, l_in_port
from iem_mstemail_accounts where email_account_id = p_email_account_id ;
l_is_acct_updated := 'Y';
l_is_acct_updated := 'Y';
x_is_acct_updated := l_is_acct_updated;
ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'IEM.PLSQL.IEM_EMTA_ADMIN_PVT.CHECK_IF_ACCOUNT_UPDATED', logMessage);
ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
ROLLBACK TO CHECK_IF_ACCOUNT_UPDATED_PVT;
END CHECK_IF_ACCOUNT_UPDATED;