[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_select varchar2(10);
SELECT RESOURCE_id
FROM jtf_rs_resource_extns_vl
WHERE upper(resource_name) = upper(c_resource_name)
AND category = 'EMPLOYEE'
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
SELECT group_id
FROM jtf_rs_groups_vl
WHERE upper(group_name) = upper(c_group_name)
AND SYSDATE BETWEEN start_date_active AND NVL(end_date_active, SYSDATE+1);
SELECT rol.role_code
FROM jtf_rs_roles_vl rol
WHERE upper(rol.role_name) = upper(c_role_name)
AND ( rol.role_type_code = 'SALES'
OR rol.role_type_code = 'TELESALES'
OR rol.role_type_code = 'FIELDSALES'
)
AND active_flag ='Y';
SELECT 'VALID' INTO l_select
FROM jtf_tty_terr_grp_accts tga,
jtf_tty_named_acct_rsc nar
WHERE nar.terr_group_account_id = tga.terr_group_account_id
AND nar.rsc_role_code = X_ROLE_CODE
AND nar.resource_id = l_res_tbl(i)
AND nar.rsc_group_id = X_GROUP_ID
AND tga.named_account_id = P_NAMED_ACCOUNT_ID
AND tga.terr_group_id <>P_terr_group_id
AND rownum < 2;
SELECT 'VALID'
INTO l_select
FROM jtf_rs_group_members mem,
jtf_rs_roles_b rol,
jtf_rs_role_relations rlt
WHERE rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.delete_flag = 'N'
AND sysdate >= rlt.start_date_active
AND ( rlt.end_date_active is null
OR
sysdate <= rlt.end_date_active
)
AND rlt.role_id = rol.role_id
AND rol.role_code = x_role_code
AND rlt.role_resource_id = mem.group_member_id
AND mem.delete_flag = 'N'
AND mem.group_id = x_group_id
AND mem.resource_id = l_res_tbl(i);
SELECT 'Y'
INTO l_select
FROM jtf_tty_terr_grp_roles
WHERE terr_group_id=P_terr_group_id
AND role_code = X_ROLE_CODE;
SELECT 'VALID'
INTO l_select
FROM JTF_TTY_MY_DIRECTS_V
WHERE current_user_id = l_user_id
AND resource_id = l_res_tbl(i)
AND group_id = X_GROUP_ID
AND role_code = X_ROLE_CODE;
SELECT resource_id, group_id, role_code
INTO x_RESOURCE_id, x_group_id, x_role_code
FROM JTF_TTY_MY_DIRECTS_V
WHERE current_user_id = l_user_id
AND upper(resource_name) = upper(P_RESOURCE_NAME)
AND upper(group_name) = upper(P_GROUP_NAME)
AND upper(role_name) = upper(P_ROLE_NAME)
AND rownum<2;
l_select varchar2(100);
SELECT 'VALID'
INTO l_select
FROM jtf_tty_srch_my_resources_v /*jtf_tty_my_resources_v*/ grv,
jtf_tty_terr_grp_owners jto
WHERE EXISTS
( SELECT NULL
FROM JTF_RS_GROUPS_DENORM /*jtf_rs_grp_denorm_vl*/ grpd
WHERE /* part of Salesgroup hierarchy of Territory Group owner */
grpd.parent_group_id = JTO.rsc_group_id
/* groups I (logged-in user) am 'member' of */
AND grpd.group_id = GRV.group_id
)
AND jto.terr_group_id = P_TG_ID
AND grv.ROLE_CODE = P_ROLE_CODE
AND grv.GROUP_ID = P_GROUP_ID
AND grv.resource_id = P_RESOURCE_ID
AND grv.CURRENT_USER_ID = P_USER_ID
AND ROWNUM < 2;
l_select varchar2(100);
SELECT 'VALID'
INTO l_select
FROM (
/* Salesperson is a member of one of his mgr's group OR
** is a manager of a child group of one of his mgr's groups */
SELECT dir.resource_id, dir.resource_name, dir.user_id dir_user_id
, MY_GRPS.group_id
, MY_GRPS.parent_group_id
, MY_GRPS.CURRENT_USER_ID
, rol.role_code, rol.role_name
, MY_GRPS.current_user_role_code
, MY_GRPS.current_user_rsc_id
FROM jtf_rs_roles_vl rol
, jtf_rs_role_relations rlt
, jtf_rs_group_members grpmemo
, jtf_rs_resource_extns_vl dir
, ( /* MY_GRPS INLINE VIEW */
/* Groups logged-in user manages/administrates */
SELECT /*+ NO_MERGE */
dv.group_id
, dv.parent_group_id
, sgh.resource_id
, mrsc.user_id CURRENT_USER_ID
, mrsc.resource_id current_user_rsc_id
, usg.USAGE
, rol.role_code current_user_role_code
FROM jtf_rs_group_usages usg
, jtf_rs_groups_denorm dv
, jtf_rs_rep_managers sgh
, jtf_rs_resource_extns mrsc
, jtf_rs_roles_b rol
, jtf_rs_role_relations rlt
WHERE usg.usage = 'SALES'
AND usg.group_id = dv.group_id
AND rlt.role_id = rol.role_id
AND rlt.role_relate_id = sgh.par_role_relate_id
AND dv.parent_group_id = sgh.group_id
AND sgh.resource_id = sgh.parent_resource_id
AND ( sgh.hierarchy_type IN ('MGR_TO_MGR')
OR rol.role_code = FND_PROFILE.VALUE('JTF_TTY_NA_PROXY_USER_ROLE')
)
AND mrsc.resource_id = sgh.resource_id ) MY_GRPS
WHERE ( rol.member_flag = 'Y' OR rol.manager_flag = 'Y' )
AND rlt.role_id = rol.role_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = grpmemo.group_member_id
AND grpmemo.resource_id = dir.resource_id
AND grpmemo.group_id = MY_GRPS.group_id
UNION ALL
/* Base Salesperson logged in, i.e., user is not
** a manager of a salesgroup */
SELECT dir.resource_id
, dir.resource_name
, dir.user_id dir_user_id
, SALES_GRPS.group_id
, SALES_GRPS.parent_group_id
, dir.user_id CURRENT_USER_ID
, rol.role_code, rol.role_name
, rol.role_code current_user_role_code
, dir.resource_id current_user_rsc_id
FROM jtf_rs_roles_vl rol
, jtf_rs_role_relations rlt
, jtf_rs_group_members grpmemo
, jtf_rs_resource_extns_vl dir
, ( /* SALES GROUPS INLINE VIEW */
SELECT dv.group_id
, dv.group_id PARENT_GROUP_ID
, NULL PARENT_GROUP_NAME
FROM jtf_rs_group_usages usg
, jtf_rs_groups_b dv
WHERE usg.usage = 'SALES'
AND usg.group_id = dv.group_id
) SALES_GRPS
WHERE rol.member_flag = 'Y'
AND rlt.role_id = rol.role_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.role_resource_id = grpmemo.group_member_id
AND grpmemo.resource_id = dir.resource_id
AND grpmemo.group_id = SALES_GRPS.group_id
AND NOT EXISTS (
/* Rep is not a manager */
SELECT NULL
FROM jtf_rs_rep_managers mgr
WHERE mgr.parent_resource_id = dir.resource_id
AND mgr.parent_resource_id = mgr.resource_id
AND mgr.group_id = grpmemo.group_id
AND mgr.hierarchy_type = 'MGR_TO_MGR'
)
) MY_REPS
, jtf_tty_terr_grp_owners tgo
, jtf_tty_terr_grp_roles tgr
WHERE EXISTS (
SELECT NULL
FROM JTF_RS_GROUPS_DENORM /*jtf_rs_grp_denorm_vl*/ grpd
WHERE grpd.parent_group_id = TGO.rsc_group_id
AND grpd.group_id = MY_REPS.group_id )
AND tgr.role_code = MY_REPS.role_code
AND tgr.terr_group_id = tgo.terr_group_id
AND tgo.terr_group_id = P_TG_ID
AND MY_REPS.CURRENT_USER_ID = P_USER_ID
AND MY_REPS.role_code = P_ROLE_CODE
AND MY_REPS.group_id = P_GROUP_ID
AND MY_REPS.resource_id = P_RESOURCE_ID
AND ROWNUM < 2;
IS select RESOURCE_ID, RSC_GROUP_ID , RSC_ROLE_CODE
from jtf_tty_named_acct_rsc
where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id;
IS SELECT narsc.resource_id resource_id,
narsc.rsc_group_id rsc_group_id,
narsc.rsc_role_code rsc_role_code
FROM jtf_tty_named_acct_rsc narsc
WHERE narsc.terr_group_account_id = c_terr_grp_acct_id
AND (narsc.resource_id, narsc.rsc_group_id, narsc.rsc_role_code ) IN
( select /*+ NO_MERGE */ mydir.resource_id, mydir.group_id, mydir.role_code
from jtf_tty_srch_my_resources_v mydir
where mydir.current_user_id = c_user_id );
IS SELECT rsc.user_id
FROM jtf_rs_resource_extns rsc,
jtf_tty_alignments al
WHERE rsc.resource_id = al.owner_resource_id
AND al.owner_resource_type = 'RS_EMPLOYEE'
AND al.alignment_id = l_align_id;
IS select pt.RESOURCE_ID, pt.RSC_GROUP_ID , pt.RSC_ROLE_CODE
from jtf_tty_align_pterr pt,
jtf_tty_pterr_accts pa,
jtf_tty_align_accts aa
where pt.align_proposed_terr_id = pa.align_proposed_terr_id
and pa.align_acct_id = aa.align_acct_id
and aa.terr_group_account_id = l_terr_grp_acct_id
and aa.alignment_id = p_alignment_id;
IS SELECT 'Y'
FROM jtf_tty_my_directs_v
WHERE current_user_id = c_user_id
AND resource_id = c_resource_id
AND group_id = c_group_id
AND role_code = c_role_code;
IS SELECT mydir.resource_id resource_id,
mydir.group_id group_id,
mydir.role_code role_code
FROM jtf_tty_my_directs_v mydir
WHERE mydir.current_user_id = c_user_id
AND mydir.dir_user_id <> c_user_id
AND ( mydir.resource_id, mydir.group_id, mydir.role_code) IN
( SELECT /*+ NO_MERGE */
repmgr.parent_resource_id,
grpmem.group_id,
rol.role_code
FROM jtf_rs_rep_managers repmgr,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol,
jtf_rs_group_members grpmem
WHERE repmgr.resource_id = c_resource_id
AND repmgr.group_id = c_group_id
AND repmgr.par_role_relate_id = rlt.role_relate_id
AND SYSDATE BETWEEN repmgr.start_date_active
AND NVL(repmgr.end_date_active, SYSDATE+1)
AND rlt.role_id = rol.role_id
AND rlt.role_resource_type = 'RS_GROUP_MEMBER'
AND rlt.delete_flag = 'N'
AND SYSDATE BETWEEN rlt.start_date_active
AND NVL(rlt.end_date_active, SYSDATE+1)
AND rlt.role_resource_id = grpmem.group_member_id
AND grpmem.delete_flag = 'N'
);
l_delete_count NUMBER := 0;
SELECT 'VALID' INTO l_found
FROM JTF_TTY_ALIGNMENTS
WHERE alignment_id = P_ALIGNMENT_ID
AND l_user_id = created_by;
select terr_group_id
into l_terr_group_id
from jtf_tty_terr_groups
where trim(terr_group_name) =P_TERRITORY_GROUP; -- deal with the trailing blank
SELECT named_account_id
INTO l_named_account_id
FROM jtf_tty_terr_grp_accts
WHERE terr_group_account_id = l_terr_grp_acct_id;
select narsc.resource_id INTO l_resource_id
from jtf_tty_named_acct_rsc narsc,
jtf_tty_srch_my_resources_v repdn -- jtf_tty_my_resources_v
where narsc.resource_id = repdn.resource_id
and narsc.rsc_group_id = repdn.group_id
and repdn.current_user_id = l_user_id
and narsc.terr_group_account_id = l_terr_grp_acct_id
and rownum < 2;
SELECT narsc.resource_id
INTO l_resource_id
FROM jtf_tty_named_acct_rsc narsc
WHERE narsc.terr_group_account_id = l_terr_grp_acct_id
AND EXISTS (
SELECT 'Y'
FROM jtf_rs_group_members grpmemo ,
jtf_rs_resource_extns dir ,
( SELECT /*+ NO_MERGE */ dv.group_id ,
mrsc.user_id CURRENT_USER_ID
FROM jtf_rs_group_usages usg ,
jtf_rs_groups_denorm dv ,
jtf_rs_rep_managers sgh ,
jtf_rs_resource_extns mrsc ,
jtf_rs_roles_b rol ,
jtf_rs_role_relations rlt
WHERE usg.usage = 'SALES'
AND usg.group_id = dv.group_id
AND rlt.role_id = rol.role_id
AND rlt.role_relate_id = sgh.par_role_relate_id
AND dv.parent_group_id = sgh.group_id
AND sgh.resource_id = sgh.parent_resource_id
AND (sgh.hierarchy_type IN ('MGR_TO_MGR')
OR rol.role_code = FND_PROFILE.VALUE('JTF_TTY_NA_PROXY_USER_ROLE'))
AND mrsc.resource_id = sgh.resource_id
AND mrsc.user_id = l_user_id
) MY_GRPS
WHERE grpmemo.resource_id = dir.resource_id
AND grpmemo.group_id = MY_GRPS.group_id
AND grpmemo.resource_id = narsc.resource_id
AND grpmemo.group_id = narsc.rsc_group_id
UNION ALL
SELECT 'Y'
FROM jtf_rs_group_members grpmemo ,
jtf_rs_resource_extns dir ,
jtf_rs_group_usages usg
WHERE usg.usage = 'SALES'
AND grpmemo.resource_id = dir.resource_id
AND grpmemo.group_id = usg.group_id
AND dir.user_id = l_user_id
AND grpmemo.resource_id = narsc.resource_id
AND grpmemo.group_id = narsc.rsc_group_id
)
and rownum < 2;
select ASSIGNED_FLAG
into l_assign_flag
from jtf_tty_named_acct_rsc
where TERR_GROUP_ACCOUNT_ID = l_terr_grp_acct_id
and RESOURCE_ID = l_added_rscs_tbl(j).Resource_id
and RSC_GROUP_ID = l_added_rscs_tbl(j).group_id
and RSC_ROLE_CODE = l_added_rscs_tbl(j).role_code
and RSC_RESOURCE_TYPE = 'RS_EMPLOYEE';
l_delete_count :=l_delete_count +1;
l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
select 'x'
into l_whether_exist
from jtf_tty_align_pterr pt,
jtf_tty_pterr_accts pa,
jtf_tty_align_accts aa
where aa.terr_group_account_id = l_terr_grp_acct_id
and aa.alignment_id = p_alignment_id
and aa.align_acct_id = pa.align_acct_id
and pa.align_proposed_terr_id = pt.align_proposed_terr_id
and pt.resource_id = l_added_rscs_tbl(j).Resource_id
and pt.rsc_group_id = l_added_rscs_tbl(j).group_id
and pt.rsc_role_code = l_added_rscs_tbl(j).role_code
and pt.resource_type = 'RS_EMPLOYEE';
select imported_on
into l_imported_on
from jtf_tty_alignments
where alignment_id = p_alignment_id;
l_delete_count :=l_delete_count +1;
l_removed_rscs_tbl(l_delete_count).resource_id := c_res.Resource_id;
l_removed_rscs_tbl(l_delete_count).group_id := c_res.RSC_GROUP_ID;
l_removed_rscs_tbl(l_delete_count).role_code := c_res.RSC_ROLE_CODE;
l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
l_delete_count :=l_delete_count +1;
l_removed_rscs_tbl(l_delete_count).resource_id := l_directs_tbl(k).resource_id;
l_removed_rscs_tbl(l_delete_count).group_id := l_directs_tbl(k).group_id;
l_removed_rscs_tbl(l_delete_count).role_code := l_directs_tbl(k).role_code;
l_removed_rscs_tbl(l_delete_count).attribute1 := 'N';
JTF_TTY_NACCT_SALES_PUB.UPDATE_SALES_TEAM(
p_api_version_number => 1,
p_init_msg_list => 'N',
p_SQL_Trace => 'N',
p_Debug_Flag => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_user_resource_id => null,
p_terr_group_id => l_terr_group_id,
p_user_attribute1 => fnd_global.user_id,
--p_user_attribute1 => 1069,
p_user_attribute2 => null,
p_added_rscs_tbl => l_add_rscs_tbl,
p_removed_rscs_tbl => l_removed_rscs_tbl,
p_affected_parties_tbl => l_affected_parties_tbl,
ERRBUF => errbuf,
RETCODE => retcode
);
JTF_TTY_ALIGN_WEBADI_INT_PKG.UPDATE_ALIGNMENT_TEAM(
p_api_version_number => 1,
p_init_msg_list => 'N',
p_SQL_Trace => 'N',
p_Debug_Flag => 'N',
p_alignment_id => p_alignment_id,
p_user_id => l_user_id,
p_user_attribute1 => fnd_global.user_id,
p_added_rscs_tbl => l_add_rscs_tbl,
p_removed_rscs_tbl => l_removed_rscs_tbl,
p_affected_parties_tbl => l_affected_parties_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);