[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_updated_by number;
SELECT rsc.resource_id
, rsc.resource_number
, rsc.source_id
, rsc.object_version_number
, rsc.start_date_active
, rsc.end_date_active
FROM jtf_rs_resource_extns rsc
WHERE rsc.resource_id = l_resource_id;
SELECT rlt.role_relate_id
, rlt.start_date_active
, rlt.end_date_active
, rlt.object_version_number
FROM jtf_rs_role_relations rlt
WHERE rlt.role_resource_id = l_role_resource_id
AND rlt.role_resource_type = 'RS_INDIVIDUAL'
AND nvl(rlt.delete_flag, 'N') <> 'Y'
-- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
SELECT res.salesrep_id
, res.org_id
, res.start_date_active
, res.end_date_active
, res.object_version_number
, res.sales_credit_type_id
FROM jtf_rs_salesreps res
WHERE res.resource_id = l_resource_id;
SELECT terr.salesrep_id
,terr.territory_id
,terr.salesrep_territory_id
,terr.start_date_active
,terr.end_date_active
,terr.object_version_number
FROM ra_salesrep_territories terr
WHERE terr.salesrep_id = l_salesrep_id;
SELECT salesrep_territory_id
FROM ra_salesrep_territories
WHERE salesrep_id = c_salesrep_id
AND territory_id = c_territory_id
and salesrep_territory_id <> c_salesrep_territory_id
AND (c_start_date_active between start_date_active and (nvl(end_date_active, l_fnd_date))
OR (nvl(c_end_date_active, l_fnd_date) between start_date_active and nvl(end_date_active, l_fnd_date))
OR (c_start_date_active < start_date_active and nvl(c_end_date_active, l_fnd_date) > nvl(end_date_active, l_fnd_date))
);
SELECT rlt.role_relate_id
, rlt.start_date_active
, rlt.end_date_active
, rlt.object_version_number
FROM jtf_rs_role_relations rlt
, jtf_rs_team_members mem
WHERE mem.team_resource_id = l_resource_id
AND mem.resource_type = 'INDIVIDUAL'
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND rlt.role_resource_id = mem.team_member_id
AND rlt.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(rlt.delete_flag ,'N') <> 'Y'
-- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
SELECT rlt.role_relate_id
, rlt.start_date_active
, rlt.end_date_active
, rlt.object_version_number
FROM jtf_rs_role_relations rlt
, jtf_rs_group_members mem
WHERE mem.resource_id = l_resource_id
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(rlt.delete_flag ,'N') <> 'Y'
-- AND nvl(rlt.end_date_active,trunc(sysdate)) >= trunc(sysdate);
l_updated_by := jtf_resource_utl.updated_by;
jtf_rs_role_relate_pub.delete_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_team_rec.role_relate_id,
P_OBJECT_VERSION_NUM => res_team_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_team_rec.role_relate_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => res_team_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.delete_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_group_rec.role_relate_id,
P_OBJECT_VERSION_NUM => res_group_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_group_rec.role_relate_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => res_group_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.delete_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_role_rec.role_relate_id,
P_OBJECT_VERSION_NUM => res_role_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
jtf_rs_role_relate_pub.update_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => res_role_rec.role_relate_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => res_role_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
update ra_salesrep_territories
set start_date_active = trunc(p_end_date_active - 1),
end_date_active = trunc(p_end_date_active),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = l_updated_by
where salesrep_territory_id = res_srp_terr_rec.salesrep_territory_id;
update ra_salesrep_territories
set end_date_active = trunc(p_end_date_active),
object_version_number = object_version_number + 1,
last_update_date = sysdate,
last_updated_by = l_updated_by
where salesrep_territory_id = res_srp_terr_REC.SALEsrep_territory_id;
jtf_rs_salesreps_pub.update_salesrep
( P_API_VERSION => 1.0,
P_SALESREP_ID => res_srp_rec.salesrep_id,
P_ORG_ID => res_srp_rec.org_id,
P_SALES_CREDIT_TYPE_ID => res_srp_rec.sales_credit_type_id,
P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
P_END_DATE_ACTIVE => trunc(p_end_date_active ) ,
P_OBJECT_VERSION_NUMBER => res_srp_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
jtf_rs_salesreps_pub.update_salesrep
( P_API_VERSION => 1.0,
P_SALESREP_ID => res_srp_rec.salesrep_id,
P_ORG_ID => res_srp_rec.org_id,
P_SALES_CREDIT_TYPE_ID => res_srp_rec.sales_credit_type_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active ) ,
P_OBJECT_VERSION_NUMBER => res_srp_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data ) ;
jtf_rs_resource_pub.update_resource
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_true,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => term_res_rec.resource_id,
P_RESOURCE_NUMBER => term_res_rec.resource_number,
P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => l_object_version_num_res,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data) ;
jtf_rs_resource_pub.update_resource
( P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_true,
P_COMMIT => fnd_api.g_false,
P_RESOURCE_ID => term_res_rec.resource_id,
P_RESOURCE_NUMBER => term_res_rec.resource_number,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => l_object_version_num_res,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data) ;
/* Function to check if user has resource update access */
Function Validate_Update_Access( p_resource_id number,
p_resource_user_id number default null
) Return varchar2
IS
l_profile_value VARCHAR2(10);
SELECT nvl(user_id,-99)
INTO l_resource_user_id
FROM jtf_rs_resource_extns
WHERE resource_id = p_resource_id;
END Validate_Update_Access;
/* Function to check if logged in user has access to Update Group Membership/Hierarchy */
FUNCTION Group_Update_Access( p_group_id IN number default null) RETURN VARCHAR2
IS
l_profile_value VARCHAR2(10);
SELECT parent_group_id
FROM jtf_rs_groups_denorm
WHERE group_id = l_group_id
AND group_id <> parent_group_id
AND trunc(sysdate) between start_date_active and nvl(end_date_active, trunc(sysdate));
SELECT resource_id
INTO l_resource_id
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id
AND rownum < 2;
SELECT '1'
INTO l_mgr
FROM jtf_rs_roles_b c,
jtf_rs_role_relations b,
jtf_rs_group_members a
WHERE a.group_id = parent_grp_rec.parent_group_id
AND a.resource_id = l_resource_id
AND nvl(a.delete_flag, 'N') <> 'Y'
AND b.role_resource_id = a.group_member_id
AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
AND b.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(b.delete_flag, 'N') <> 'Y'
AND c.role_id = b.role_id
AND 'Y' in (c.manager_flag, c.admin_flag)
AND c.active_flag = 'Y'
AND rownum < 2 ;
SELECT '2'
INTO l_mgr
FROM jtf_rs_roles_b c,
jtf_rs_role_relations b,
jtf_rs_group_members a
WHERE a.group_id = p_group_id
AND a.resource_id = l_resource_id
AND nvl(a.delete_flag, 'N') <> 'Y'
AND b.role_resource_id = a.group_member_id
AND trunc(sysdate) between b.start_date_active and nvl(b.end_date_active, trunc(sysdate))
AND b.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(b.delete_flag, 'N') <> 'Y'
AND c.role_id = b.role_id
AND 'Y' in (c.manager_flag, c.admin_flag)
AND c.active_flag = 'Y'
AND rownum < 2 ;
END Group_Update_Access;
/* Function to check if logged in user has access to Update role */
FUNCTION Role_Update_Access RETURN VARCHAR2
IS
l_profile_value VARCHAR2(10);
END Role_Update_Access;
select user_id
from jtf_rs_resource_extns connect by
source_id = prior source_mgr_id
start with resource_id = p_res_id;
SELECT grp.group_id,
grp.group_number,
grp.object_version_number,
grp.start_date_active,
grp.end_date_active
FROM jtf_rs_groups_b grp
WHERE grp.group_id = c_group_id;
SELECT rlt.role_relate_id,
rlt.start_date_active,
rlt.end_date_active,
rlt.object_version_number
FROM jtf_rs_role_relations rlt,
jtf_rs_group_members mem
WHERE mem.group_id = c_group_id
AND nvl(mem.delete_flag, 'N') <> 'Y'
AND rlt.role_resource_id = mem.group_member_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(rlt.delete_flag ,'N') <> 'Y'
AND nvl(rlt.end_date_active, l_fnd_date) > c_group_end_date
UNION ALL
SELECT rlt2.role_relate_id,
rlt2.start_date_active,
rlt2.end_date_active,
rlt2.object_version_number
FROM jtf_rs_role_relations rlt2
WHERE rlt2.role_resource_id = c_group_id
AND rlt2.role_resource_type = 'RS_GROUP'
AND nvl(rlt2.delete_flag ,'N') <> 'Y'
AND NVL(rlt2.end_date_active, l_fnd_date) > c_group_end_date;
SELECT group_relate_id,
group_id,
related_group_id,
start_date_active,
end_date_active,
object_version_number
FROM jtf_rs_grp_relations
WHERE nvl(delete_flag, 'N') <> 'Y'
AND group_id = c_group_id
AND nvl(end_date_active, l_fnd_date) > c_group_end_date
UNION ALL
SELECT group_relate_id,
group_id,
related_group_id,
start_date_active,
end_date_active,
object_version_number
FROM jtf_rs_grp_relations
WHERE nvl(delete_flag, 'N') <> 'Y'
AND related_group_id = c_group_id
AND nvl(end_date_active, l_fnd_date) > c_group_end_date;
jtf_rs_role_relate_pub.delete_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_role_relate_pub.update_resource_role_relate
( P_API_VERSION => 1.0,
P_ROLE_RELATE_ID => group_mem_roles_rec.role_relate_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => group_mem_roles_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_group_relate_pvt.delete_resource_group_relate
( P_API_VERSION => 1.0,
P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_group_relate_pvt.update_resource_group_relate
( P_API_VERSION => 1.0,
P_GROUP_RELATE_ID => grp_relations_rec.group_relate_id,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => grp_relations_rec.object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_groups_pub.update_resource_group
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_true,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => term_grp_rec.group_id,
P_GROUP_NUMBER => term_grp_rec.group_number,
P_START_DATE_ACTIVE => trunc(p_end_date_active - 1) ,
P_END_DATE_ACTIVE => trunc(p_end_date_active) ,
P_OBJECT_VERSION_NUM => l_object_version_num_grp,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data) ;
jtf_rs_groups_pub.update_resource_group
(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => fnd_api.g_true,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => term_grp_rec.group_id,
P_GROUP_NUMBER => term_grp_rec.group_number,
P_END_DATE_ACTIVE => trunc(p_end_date_active),
P_OBJECT_VERSION_NUM => l_object_version_num_grp,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data) ;