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';
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_agents_s1.nextval into l_agent_account_id
from dual;
INSERT INTO IEM_AGENTS (
AGENT_ID ,
EMAIL_ACCOUNT_ID ,
RESOURCE_ID ,
SIGNATURE ,
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_AGENT_ACCOUNT_ID ,
p_EMAIL_ACCOUNT_ID ,
p_resource_id ,
decode(p_SIGNATURE,FND_API.G_MISS_CHAR,NULL,p_signature),
decode(p_CREATED_BY,null,-1,p_CREATED_BY),
sysdate,
decode(p_LAST_UPDATED_BY,null,-1,p_LAST_UPDATED_BY),
sysdate,
decode(p_LAST_UPDATE_LOGIN,null,-1,p_LAST_UPDATE_LOGIN),
p_ATTRIBUTE1,
p_ATTRIBUTE2,
p_ATTRIBUTE3,
p_ATTRIBUTE4,
p_ATTRIBUTE5,
p_ATTRIBUTE6,
p_ATTRIBUTE7,
p_ATTRIBUTE8,
p_ATTRIBUTE9,
p_ATTRIBUTE10,
p_ATTRIBUTE11,
p_ATTRIBUTE12,
p_ATTRIBUTE13,
p_ATTRIBUTE14,
p_ATTRIBUTE15
);
PROCEDURE delete_item (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_resource_id in number,
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_item';
select agent_id into l_agent_id
from IEM_AGENTS
where resource_id=p_resource_id
and email_account_id=p_email_account_id;
delete from IEM_AGENTS
where resource_id=p_resource_id
and email_account_id=p_email_account_id;
p_param => 'DELETE',
p_value => l_agent_id,
x_return_status => l_stat,
x_msg_count => l_count,
x_msg_data => l_data);
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
ROLLBACK TO delete_item_PVT;
END delete_item;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_in_resource_tbl(i);
select count(*) into l_agent_account_count from iem_agents where resource_id=p_in_resource_tbl(i)
and email_account_id=p_email_account_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_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
x_return_status=>l_return_status);
PROCEDURE delete_agntacct_by_agent (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_out_resource_tbl IN jtf_varchar2_Table_100,
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_agntacct_by_agent';
SAVEPOINT delete_agntacct_by_agent_PVT;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_out_resource_tbl(i);
select count(*) into l_agent_account_count from iem_agents where resource_id=p_out_resource_tbl(i)
and email_account_id=p_email_account_id;
IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
p_init_msg_list=>'F' ,
p_commit=>'F' ,
p_resource_id => p_out_resource_tbl(i),
p_email_account_id => p_email_account_id,
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
x_return_status=>l_return_status);
/* select count(*) into l_resource_param_count from jtf_rs_resource_values
where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
where value_type=p_email_account_id and resource_id=p_out_resource_tbl(i) and value='IEM_DEFAULT_VALUE';
JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
P_Api_Version => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_resource_param_value_id => l_resource_param_value_id,
p_object_version_number => l_object_version_number,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT1_DELETED');
FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT9_DELETED');
FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT5_DELETED');
ROLLBACK TO delete_agntacct_by_agent_PVT;
ROLLBACK TO delete_agntacct_by_agent_PVT;
ROLLBACK TO delete_agntacct_by_agent_PVT;
END delete_agntacct_by_agent;
PROCEDURE update_agntacct_by_agent_wrap (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_in_resource_tbl IN jtf_varchar2_Table_100,
p_out_resource_tbl IN jtf_varchar2_Table_100,
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):='update_agntacct_by_agent_wrap';
iem_agent_act_pvt.delete_agntacct_by_agent (p_api_version_number =>1.0,
p_init_msg_list=>'F' ,
p_commit=>'F' ,
p_out_resource_tbl => p_out_resource_tbl,
p_email_account_id => p_email_account_id,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END update_agntacct_by_agent_wrap;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
SELECT count(*) into l_count
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id
AND ( end_date_active is null OR
trunc(end_date_active) >= trunc(sysdate) );
select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
WHERE EMAIL_ACCOUNT_ID = p_in_email_account_tbl(i);
SELECT USER_NAME
INTO l_email_user
FROM IEM_MSTEMAIL_ACCOUNTS
WHERE EMAIL_ACCOUNT_ID = p_in_email_account_tbl(i);
select count(*) into l_agent_account_count from iem_agents where email_account_id= p_in_email_account_tbl(i) and resource_id=p_resource_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_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
x_return_status=>l_return_status);
PROCEDURE delete_agntacct_by_account (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_out_email_account_tbl IN jtf_varchar2_Table_100,
p_resource_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_agntacct_by_account';
SAVEPOINT delete_agntacct_by_account_PVT;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
select count(*) into l_account_count FROM IEM_MSTEMAIL_ACCOUNTS
WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
SELECT USER_NAME
INTO l_email_user
FROM IEM_MSTEMAIL_ACCOUNTS
WHERE EMAIL_ACCOUNT_ID = p_out_email_account_tbl(i);
select count(*) into l_agent_account_count from iem_agents where email_account_id=p_out_email_account_tbl(i)
and resource_id=p_resource_id;
IEM_AGENT_ACT_PVT.delete_item(p_api_version_number=>1.0,
P_Init_Msg_List =>'F',
P_Commit => 'F',
p_resource_id => p_resource_id,
p_email_account_id => p_out_email_account_tbl(i),
x_msg_count=>l_msg_count,
x_msg_data=>l_msg_data,
x_return_status=>l_return_status);
/* select count(*) into l_resource_param_count from jtf_rs_resource_values
where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
select resource_param_value_id, object_version_number into l_resource_param_value_id, l_object_version_number from jtf_rs_resource_values
where value_type=p_out_email_account_tbl(i) and resource_id=p_resource_id and value='IEM_DEFAULT_VALUE';
JTF_RS_RESOURCE_VALUES_PUB.DELETE_RS_RESOURCE_VALUES(
P_Api_Version => 1.0,
P_Init_Msg_List => FND_API.G_FALSE,
P_Commit => FND_API.G_FALSE,
p_resource_param_value_id => l_resource_param_value_id,
p_object_version_number => l_object_version_number,
X_Return_Status => l_return_status,
X_Msg_Count => l_msg_count,
X_Msg_Data => l_msg_data);
FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT2_DELETED');
FND_MESSAGE.SET_NAME('IEM','IEM_SSS_AGNTACCT10_DELETED');
FND_MESSAGE.SET_NAME('IEM', 'IEM_SSS_AGNTACCT6_DELETED');
ROLLBACK TO delete_agntacct_by_account_PVT;
ROLLBACK TO delete_agntacct_by_account_PVT;
ROLLBACK TO delete_agntacct_by_account_PVT;
END delete_agntacct_by_account;
PROCEDURE update_agntacct_by_acct_wrap (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_in_email_account_tbl IN jtf_varchar2_Table_100,
p_out_email_account_tbl IN jtf_varchar2_Table_100,
p_resource_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_agntacct_by_acct_wrap';
iem_agent_act_pvt.delete_agntacct_by_account (p_api_version_number =>1.0,
P_Init_Msg_List =>'F',
P_Commit => 'F',
p_out_email_account_tbl => p_out_email_account_tbl,
p_resource_id => p_resource_id,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
END update_agntacct_by_acct_wrap;
PROCEDURE update_agent_cherrypick (
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_in_cherrypick_tbl IN jtf_varchar2_Table_100,
p_out_cherrypick_tbl IN jtf_varchar2_Table_100,
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):='update_agent_cherrypick';
SAVEPOINT update_agent_cherrypick_PVT;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_in_cherrypick_tbl(i);
select count(*) into l_agent_account_count
from iem_agents
where resource_id=p_in_cherrypick_tbl(i)
and email_account_id=p_email_account_id;
UPDATE iem_agents set cherry_pick_flag = 'Y'
WHERE resource_id=p_in_cherrypick_tbl(i)
and email_account_id=p_email_account_id;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_out_cherrypick_tbl(i);
select count(*) into l_agent_account_count
from iem_agents
where resource_id=p_out_cherrypick_tbl(i)
and email_account_id=p_email_account_id;
UPDATE iem_agents set cherry_pick_flag = null
WHERE resource_id=p_out_cherrypick_tbl(i)
and email_account_id=p_email_account_id;
ROLLBACK TO update_agent_cherrypick_PVT;
ROLLBACK TO update_agent_cherrypick_PVT;
ROLLBACK TO update_agent_cherrypick_PVT;
END update_agent_cherrypick;
PROCEDURE update_acct_cherrypick (p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
p_in_acct_chrypick_tbl IN jtf_varchar2_Table_100,
p_out_acct_chrypick_tbl IN jtf_varchar2_Table_100,
p_resource_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_acct_cherrypick';
SAVEPOINT update_acct_chrypick_PVT;
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
select count(*) into l_agent_account_count
from iem_agents
where resource_id=p_resource_id
and email_account_id=p_in_acct_chrypick_tbl(i);
UPDATE iem_agents set cherry_pick_flag = 'Y'
where resource_id=p_resource_id
and email_account_id=p_in_acct_chrypick_tbl(i);
SELECT USER_ID, USER_NAME, SOURCE_LAST_NAME || ', ' || SOURCE_FIRST_NAME as RESOURCE_NAME
INTO l_user_id, l_user_name, l_res_name
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = p_resource_id;
select count(*) into l_agent_account_count
from iem_agents
where resource_id=p_resource_id
and email_account_id=p_out_acct_chrypick_tbl(i);
UPDATE iem_agents set cherry_pick_flag = null
where resource_id=p_resource_id
and email_account_id=p_out_acct_chrypick_tbl(i);
FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETED');
FND_MESSAGE.SET_NAME('IEM','IEM_CHRRYPICK_ACCT_DELETE_NO_DATA_ERROR');
FND_MESSAGE.SET_NAME('IEM','IEM_CHRYPICK_ACCT_DELETE_OTHERS_ERROR');
ROLLBACK TO update_acct_chrypick_PVT;
ROLLBACK TO update_acct_chrypick_PVT;
ROLLBACK TO update_acct_chrypick_PVT;
END update_acct_cherrypick;