[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select user_id
from jtf_rs_resource_extns
where resource_id = p_resource_id;
select category, source_id, person_party_id
from jtf_rs_resource_extns
where resource_id = p_resource_id;
select name, orig_system, orig_system_id
from wf_local_roles
where name = l_res_usr_role_name
and orig_system = l_res_usr_orig_system
and orig_system_id = p_resource_id;
select name, orig_system, orig_system_id
from wf_local_roles
where orig_system = l_res_hz_orig_system
and orig_system_id = c_party_id;
select per_party_id
from po_vendor_contacts
where vendor_contact_id = c_vendor_contact_id;
select ppf.party_id
from per_all_people_f ppf,
jtf_rs_resource_extns res
where res.category = 'EMPLOYEE'
and res.source_id = ppf.person_id
and res.resource_id = p_resource_id
order by ppf.effective_start_date desc;
select res.source_id
from jtf_rs_resource_extns res
where res.resource_id = p_resource_id;
l_list.DELETE;
PROCEDURE update_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN NUMBER,
P_RESOURCE_NAME IN VARCHAR2,
P_USER_ID IN NUMBER,
P_EMAIL_ADDRESS IN VARCHAR2,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE';
select resource_name, source_email, user_id, source_id, category,
trunc(start_date_active) start_date_active,
trunc(end_date_active) end_date_active
from jtf_rs_resource_extns_vl
where resource_id = p_resource_id;
select name
from wf_local_roles
where name = l_res_usr_role_name
and orig_system = l_res_usr_orig_system
and orig_system_id = p_resource_id;
select user_name
from fnd_user
where user_id = l_user_id;
select party_id
from per_all_people_f ppf
where ppf.person_id = ll_source_id
order by ppf.effective_start_date desc;
l_list.DELETE;
PROCEDURE update_wf_role (ll_role_name VARCHAR2,
ll_role_orig_system VARCHAR2,
ll_role_orig_system_id NUMBER,
ll_role_display_name VARCHAR2,
ll_email_address VARCHAR2,
ll_status VARCHAR2,
ll_start_date_active DATE,
ll_expiration_date DATE,
ll_source_id NUMBER,
ll_category VARCHAR2) IS
l_list WF_PARAMETER_LIST_T;
select party_id
from per_all_people_f ppf
where ppf.person_id = ll_source_id
order by ppf.effective_start_date desc;
l_list.DELETE;
END update_wf_role;
SELECT mem.group_id, grp.group_number,
trunc(grp.start_date_active) start_date_active,
trunc(grp.end_date_active) end_date_active
FROM jtf_rs_group_members mem, jtf_rs_groups_b grp
WHERE mem.group_id = grp.group_id
AND nvl(mem.delete_flag,'N') <> 'Y'
AND l_sysdate between trunc(grp.start_date_active) and nvl(trunc(grp.end_date_active),l_sysdate)
AND mem.resource_id = ll_resource_id;
select name
from wf_local_roles
where name = c_grp_role_name
and orig_system = g_grp_orig_system
and orig_system_id = c_group_id;
SELECT mem.team_id,
trunc(tm.start_date_active) start_date_active,
trunc(tm.end_date_active) end_date_active
FROM jtf_rs_team_members mem, jtf_rs_teams_b tm
WHERE mem.team_id = tm.team_id
AND nvl(mem.delete_flag,'N') <> 'Y'
AND l_sysdate between trunc(tm.start_date_active) and nvl(trunc(tm.end_date_active),l_sysdate)
AND mem.team_resource_id = ll_resource_id
AND mem.RESOURCE_TYPE = 'INDIVIDUAL';
select name
from wf_local_roles
where name = c_team_role_name
and orig_system = g_team_orig_system
and orig_system_id = c_team_id;
/* following procedure will update the roles with latest info */
update_wf_role(ll_role_name => l_res_usr_role_name,
ll_role_orig_system => l_res_usr_orig_system,
ll_role_orig_system_id => p_resource_id,
ll_role_display_name => p_resource_name,
ll_email_address => p_email_address,
ll_status => 'ACTIVE',
ll_start_date_active => l_start_date_active,
ll_expiration_date => l_end_date_active,
ll_source_id => res_rec.source_id,
ll_category => res_rec.category);
/* following procedure will update the wf_local_user with latest info */
update_wf_role(ll_role_name => l_res_usr_role_name,
ll_role_orig_system => l_res_usr_orig_system,
ll_role_orig_system_id => p_resource_id,
ll_role_display_name => p_resource_name,
ll_email_address => p_email_address,
ll_status => 'ACTIVE',
ll_start_date_active => l_start_date_active,
ll_expiration_date => l_end_date_active,
ll_source_id => res_rec.source_id,
ll_category => res_rec.category);
END update_resource;
PROCEDURE delete_resource
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
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_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE';
SELECT role_name, role_orig_system, role_orig_system_id
FROM wf_local_user_roles
WHERE user_name = l_res_usr_role_name
AND user_orig_system = l_res_usr_orig_system
AND user_orig_system_id = p_resource_id
AND role_name <> l_res_usr_role_name;
AddParameterToList('DELETE','TRUE',l_list);
l_list.DELETE;
END delete_resource;
l_list.DELETE;
PROCEDURE update_resource_group
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN NUMBER,
P_GROUP_NAME IN VARCHAR2,
P_EMAIL_ADDRESS IN VARCHAR2,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP';
SELECT 'Y'
FROM WF_LOCAL_ROLES
WHERE NAME = P_NAME AND
ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
ORIG_SYSTEM = P_ORG_SYS;
SELECT EMAIL_ADDRESS, GROUP_NAME, trunc(START_DATE_ACTIVE) START_DATE_ACTIVE, trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_GROUPS_VL WHERE GROUP_ID = P_GROUP_ID;
SELECT mem.resource_id,
greatest(l_start_date_active, res.start_date_active) grp_mem_start_date,
least (nvl(l_end_date_active, l_g_miss_date), nvl(res.end_date_active, l_g_miss_date)) grp_mem_end_date
FROM jtf_rs_group_members mem, jtf_rs_groups_b grp, jtf_rs_resource_extns res
WHERE mem.group_id = grp.group_id
AND mem.resource_id = res.resource_id
AND nvl(mem.delete_flag,'N') <> 'Y'
AND l_sysdate between trunc(res.start_date_active) and nvl(trunc(res.end_date_active),l_sysdate)
AND mem.group_id = p_group_id;
SELECT mem.team_id,
trunc(tm.start_date_active) start_date_active,
trunc(tm.end_date_active) end_date_active
FROM jtf_rs_team_members mem, jtf_rs_teams_b tm
WHERE mem.team_id = tm.team_id
AND nvl(mem.delete_flag,'N') <> 'Y'
-- AND l_sysdate between trunc(tm.start_date_active) and nvl(trunc(tm.end_date_active),l_sysdate)
AND mem.team_resource_id = p_group_id
AND mem.RESOURCE_TYPE = 'GROUP';
SAVEPOINT wf_int_update_resource_group;
/* Role record exists then update if group name, email, start date
or end date is changed */
OPEN C_GRP_OLD_VALS(p_group_id);
/* If any of the above is changed and the group old/new end_date is >= l_sysdate, then update the group */
if ((nvl(l_end_date_active,l_sysdate) >= l_sysdate)) then
AddParameterToList('USER_NAME',l_grp_role_name,l_list);
l_list.DELETE;
l_list.DELETE;
ROLLBACK TO wf_int_update_resource_group;
SELECT 'Y'
FROM WF_LOCAL_ROLES
WHERE NAME = P_NAME AND
ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
ORIG_SYSTEM = P_ORG_SYS;
SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_GROUPS_B
WHERE GROUP_ID = P_GROUP_ID AND
trunc(START_DATE_ACTIVE) <= l_sysdate AND
NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = P_RESOURCE_ID AND
trunc(START_DATE_ACTIVE) <= l_sysdate AND
NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
PROCEDURE delete_resource_group_members
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN NUMBER,
P_GROUP_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_MEMBERS';
l_list.DELETE;
PROCEDURE update_resource_team
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_TEAM_ID IN NUMBER,
P_TEAM_NAME IN VARCHAR2,
P_EMAIL_ADDRESS IN VARCHAR2,
P_START_DATE_ACTIVE IN DATE,
P_END_DATE_ACTIVE IN DATE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_TEAM';
SELECT 'Y'
FROM WF_LOCAL_ROLES
WHERE NAME = P_NAME AND
ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
ORIG_SYSTEM = P_ORG_SYS;
SELECT EMAIL_ADDRESS, TEAM_NAME, trunc(START_DATE_ACTIVE) START_DATE_ACTIVE, trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_TEAMS_VL WHERE TEAM_ID = P_TEAM_ID;
SELECT mem.team_resource_id,
mem.resource_type
FROM jtf_rs_team_members mem, jtf_rs_teams_b team
WHERE mem.team_id = team.team_id
AND nvl(mem.delete_flag,'N') <> 'Y'
-- AND l_sysdate between trunc(team.start_date_active) and nvl(trunc(team.end_date_active),l_sysdate)
AND team.team_id = p_team_id;
SELECT trunc(start_date_active) start_date_active,
trunc(end_date_active) end_date_active
FROM jtf_rs_resource_extns
WHERE resource_id = c_resource_id;
SELECT trunc(start_date_active) start_date_active,
trunc(end_date_active) end_date_active
FROM jtf_rs_groups_b
WHERE group_id = c_group_id;
SAVEPOINT wf_int_update_resource_team;
/* Role record exists then update if team name, email, start date
or end date is changed */
OPEN C_TEAM_OLD_VALS(p_team_id);
/* If any of the above is changed and the team old/new end_date is >= l_sysdate, then update the team */
if ((nvl(l_end_date_active,l_sysdate) >= l_sysdate)) then
AddParameterToList('USER_NAME',l_team_role_name,l_list);
l_list.DELETE;
l_list.DELETE;
ROLLBACK TO wf_int_update_resource_team;
SELECT 'Y'
FROM WF_LOCAL_ROLES
WHERE NAME = P_NAME AND
ORIG_SYSTEM_ID = P_ORG_SYS_ID AND
ORIG_SYSTEM = P_ORG_SYS;
SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_TEAMS_B
WHERE TEAM_ID = P_TEAM_ID AND
trunc(START_DATE_ACTIVE) <= l_sysdate AND
NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID = P_RESOURCE_ID AND
trunc(START_DATE_ACTIVE) <= l_sysdate AND
NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
SELECT trunc(START_DATE_ACTIVE) START_DATE_ACTIVE,
trunc(END_DATE_ACTIVE) END_DATE_ACTIVE
FROM JTF_RS_GROUPS_B
WHERE GROUP_ID = P_GROUP_ID AND
trunc(START_DATE_ACTIVE) <= l_sysdate AND
NVL(trunc(END_DATE_ACTIVE), l_sysdate) >= l_sysdate;
PROCEDURE delete_resource_team_members
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_RESOURCE_ID IN NUMBER,
P_GROUP_ID IN NUMBER,
P_TEAM_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_TEAM_MEMBERS';