[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT role_code, MAX(num) num
FROM (
SELECT mydir.role_code role_code,
COUNT(role_code) num
FROM
jtf_tty_my_directs_gt mydir,
(
select terr_grp_acct_id
from jtf_tty_webadi_interface
where user_id = userid
) tgaid_list
WHERE
mydir.current_user_id = userid
and mydir.resource_id in (
select -- NO_MERGE
repmgr.parent_resource_id
from jtf_rs_rep_managers repmgr,
jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts ga
where narsc.resource_id = repmgr.resource_id
and narsc.rsc_group_id = repmgr.group_id
AND narsc.terr_group_account_id = ga.terr_group_account_id
AND ga.terr_group_account_id = tgaid_list.terr_grp_acct_id
)
GROUP BY tgaid_list.terr_grp_acct_id, role_code
ORDER BY MAX(role_name)
)
GROUP BY role_code;
SELECT role_code, MAX(num) num
FROM (
SELECT mydir.role_code role_code, COUNT(mydir.role_code) num
FROM ( select distinct
tmp.terr_grp_acct_id,
repmgr.parent_resource_id resource_id,
grpmem.group_id group_id,
rol.role_code role_code
from jtf_tty_webadi_interface tmp,
jtf_tty_named_acct_rsc narsc,
jtf_rs_rep_managers repmgr,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol,
jtf_rs_group_members grpmem,
jtf_tty_my_directs_gt dir
where narsc.resource_id = repmgr.resource_id
and narsc.rsc_group_id = repmgr.group_id
AND narsc.terr_group_account_id = tmp.terr_grp_acct_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'
AND tmp.user_id = userid
AND dir.current_user_id = userid
AND dir.dir_user_id <> userid
AND dir.resource_id = repmgr.parent_resource_id
AND dir.group_id = grpmem.group_id
AND dir.role_code = rol.role_code
AND tmp.user_id = dir.current_user_id
UNION ALL
select
tmp.terr_grp_acct_id,
narsc.resource_id resource_id,
narsc.rsc_group_id group_id,
narsc.rsc_role_code role_code
from jtf_tty_webadi_interface tmp,
jtf_tty_named_acct_rsc narsc,
jtf_tty_my_directs_gt dir
where narsc.terr_group_account_id = tmp.terr_grp_acct_id
AND dir.current_user_id = userid
AND dir.dir_user_id = userid
AND dir.resource_id = narsc.resource_id
AND dir.group_id = narsc.rsc_group_id
AND dir.role_code = narsc.rsc_role_code
AND tmp.user_id = userid
AND tmp.user_id = dir.current_user_id
) mydir
GROUP BY mydir.terr_grp_acct_id, mydir.role_code
)
GROUP BY role_code;
select role_code, MAX(num) num
from (
select ap.rsc_role_code role_code, count(ap.rsc_role_code) num
from JTF_TTY_ALIGN_ACCTS aa,
JTF_TTY_PTERR_ACCTS pa,
JTF_TTY_ALIGN_PTERR ap
-- jtf_rs_roles_vl rol
where
aa.alignment_id = p_align_id
and aa.align_acct_id = pa.align_acct_id
and pa.align_proposed_terr_id= ap.align_proposed_terr_id
and ap.resource_type = 'RS_EMPLOYEE'
-- and rol.role_code = ap.rsc_role_code
group by aa.terr_group_account_id, ap.rsc_role_code
-- ORDER BY MAX(rol.role_name)
)
group by role_code ;
SELECT jtf_tty_webadi_int_id, terr_grp_acct_id
FROM jtf_tty_webadi_interface
where user_id=p_userid;
select /* search directs */
mydir.resource_name, --mydir.resource_id,
mydir.group_name, --mydir.group_id,
mydir.role_name, mydir.role_code
FROM jtf_tty_my_directs_gt /*jtf_tty_my_directs_v*/ mydir
WHERE mydir.current_user_id = userid
and mydir.dir_user_id <> userid
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_tty_named_acct_rsc narsc,
jtf_rs_rep_managers repmgr,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol,
jtf_rs_group_members grpmem
where narsc.resource_id = repmgr.resource_id
and narsc.rsc_group_id = repmgr.group_id
AND narsc.terr_group_account_id = tgaid
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'
)
UNION /* the user herself */
SELECT
mydir.resource_name ,--mydir.resource_id,
mydir.group_name, --mydir.group_id,
mydir.role_name, mydir.role_code
FROM jtf_tty_my_directs_gt /*jtf_tty_my_directs_v*/ mydir
WHERE mydir.current_user_id = userid
and mydir.dir_user_id = userid
and ( mydir.resource_id, mydir.group_id, mydir.role_code) in
( select /*+ NO_MERGE */
narsc.resource_id, narsc.rsc_group_id, narsc.rsc_role_code
from jtf_tty_named_acct_rsc narsc
where narsc.terr_group_account_id = tgaid
);
select DISTINCT
mydir.resource_name,
mydir.group_name,
mydir.role_name, mydir.role_code
FROM
jtf_tty_named_acct_rsc narsc,
jtf_tty_terr_grp_accts ga,
jtf_rs_rep_managers repmgr,
jtf_tty_my_directs_v mydir
WHERE narsc.resource_id = repmgr.resource_id
and narsc.rsc_group_id = repmgr.group_id
and repmgr.parent_resource_id = mydir.resource_id
and mydir.current_user_id = userid
AND narsc.terr_group_account_id = ga.terr_group_account_id
AND ga.terr_group_account_id = tgaid;
select rsc.resource_name, --ap.resource_id,
grp.group_name, --ap.rsc_group_id group_id,
rol.role_name, ap.rsc_role_code role_code
from JTF_TTY_ALIGN_ACCTS aa,
JTF_TTY_PTERR_ACCTS pa,
JTF_TTY_ALIGN_PTERR ap,
jtf_rs_resource_extns_vl rsc,
jtf_rs_groups_vl grp,
jtf_rs_roles_vl rol
where aa.terr_group_account_id = tgaid
and aa.alignment_id = align_id
and aa.align_acct_id = pa.align_acct_id
and pa.align_proposed_terr_id= ap.align_proposed_terr_id
and ap.resource_type = 'RS_EMPLOYEE'
and rsc.resource_id = ap.resource_id
and grp.group_id = ap.rsc_group_id
and rol.role_code = ap.rsc_role_code
order by ap.rsc_role_code, rsc.resource_name ;
delete from JTF_TTY_WEBADI_INTERFACE
where user_id = to_number(p_userid);
select jtf_tty_interface_s.nextval into SEQ from dual;
select resource_id into salesMgr from jtf_rs_resource_extns
where user_id = to_number(p_userid);
delete from jtf_tty_my_directs_gt;
INSERT INTO jtf_tty_my_directs_gt
(
resource_id,
resource_name,
group_id,
group_name,
role_code,
role_name,
dir_user_id,
current_user_id,
parent_group_id,
current_user_role_code,
current_user_rsc_id
)
select
resource_id,
resource_name,
group_id,
group_name,
role_code,
role_name,
dir_user_id,
current_user_id,
parent_group_id,
current_user_role_code,
current_user_rsc_id
from jtf_tty_my_directs_v
where CURRENT_USER_ID = to_number(p_userid);
' INSERT into JTF_TTY_WEBADI_INTERFACE ' ||
' ( USER_SEQUENCE,USER_ID,TERR_GRP_ACCT_ID,JTF_TTY_WEBADI_INT_ID,NAMED_ACCOUNT,SITE_TYPE,TRADE_NAME,DUNS, '||
' GU_DUNS,GU_NAME,CITY,STATE,POSTAL_CODE,TERRITORY_GROUP, ALIGNMENT_ID, ' ||
' CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE ' ||
' ) ' ||
' SELECT ' ||
seq || ' USER_SEQUENCE,'||
P_USERID || ' USER_ID,'||
' ga.terr_group_account_id gaid, '||
' na.named_account_id naid, '||
' hzp.party_name named_account, '||
' lkp.meaning site_type, '||
' hzp.known_as trade_name, '||
' hzp.duns_number_c site_duns, '||
' GU.GU_DUNS gu_duns, ' ||
' GU.GU_NAME gu_name, ' ||
' hzp.city city, '||
' hzp.state state, '||
' hzp.postal_code postal_code, '||
' ttygrp.terr_group_name grpname, '||
P_ALIGN_ID || ' ALIGNMENT_ID,' ||
P_USERID || ' CREATED_BY,' ||
'''' || sysdate|| '''' || ' CREATION_DATE,' ||
P_USERID || ' LAST_UPDATED_BY,'
|| '''' || sysdate || '''' || ' LAST_UPDATE_DATE '||
' from hz_parties hzp, '||
' jtf_tty_named_accts na, '||
' jtf_tty_terr_grp_accts ga, '||
' fnd_lookups lkp, '||
' jtf_tty_terr_groups ttygrp '||
' , ( /* Global Ultimate */ ' ||
' SELECT min(gup.party_name) GU_NAME ' ||
' , min(gup.duns_number_c) GU_DUNS ' ||
' , hzr.object_id GU_OBJECT_ID ' ||
' FROM hz_parties gup ' ||
' , hz_relationships hzr ' ||
' WHERE hzr.subject_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.object_table_name = ''HZ_PARTIES'' ' ||
' AND hzr.relationship_type = ''GLOBAL_ULTIMATE'' ' ||
' AND hzr.relationship_code = ''GLOBAL_ULTIMATE_OF'' ' ||
' AND hzr.status = ''A'' ' ||
' AND hzr.subject_id = gup.party_id ' ||
' AND gup.status = ''A'' ' ||
' group by hzr.object_id ) GU ' ||
' where hzp.party_id = na.party_id '||
' and na.site_type_code = lkp.lookup_code '||
' and lkp.lookup_type = ''JTF_TTY_SITE_TYPE_CODE'' '||
' and na.named_account_id = ga.named_account_id '||
' and ttygrp.terr_group_id = ga.terr_group_id '||
' and ttygrp.active_from_date <= sysdate '||
' and ( ttygrp.active_to_date is null '||
' or '||
' ttygrp.active_to_date >= sysdate '||
' ) '||
' and ga.terr_group_account_id IN '||
' ( select /*+ NO_MERGE */ narsc.terr_group_account_id '||
' from jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_srch_my_resources_v repdn '||
' where narsc.resource_id = repdn.resource_id '||
' and narsc.rsc_group_id = repdn.group_id '||
' and repdn.current_user_id = :p_userid '||
' ) '||
' AND GU.GU_OBJECT_ID (+) = hzp.party_id ';
/* ' select DISTINCT '||
' dir.resource_name, dir.resource_id, '||
' dir.group_name, dir.group_id, '||
' dir.role_name, dir.role_code '||
' FROM '||
' jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_terr_grp_accts ga, '||
' jtf_rs_rep_managers repmgr, '||
' jtf_tty_my_directs_v mydir '||
' WHERE narsc.resource_id = repmgr.resource_id '||
' and narsc.rsc_group_id = repmgr.group_id '||
' and repmgr.parent_resource_id = mydir.resource_id '||
' and mydir.current_user_id = :0 '||
' AND narsc.terr_group_account_id = ga.terr_group_account_id '||
' AND ga.terr_group_account_id = :1; ';
' select '||
' dir.resource_name, dir.resource_id, '||
' dir.group_name, dir.group_id, '||
' dir.role_name, dir.role_code '||
' from '||
' jtf_tty_my_directs_v dir, '||
' jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_terr_grp_accts ga '||
' where dir.current_user_id = :0 '||
' and dir.resource_id = narsc.resource_id '||
' and dir.role_code = narsc.rsc_role_code '||
' and dir.group_id = narsc.rsc_group_id '||
' and narsc.terr_group_account_id = ga.terr_group_account_id '||
' and narsc.rsc_resource_type = ''RS_EMPLOYEE'' '||
' and ga.terr_group_account_id = :1 '||
' order by dir.role_code, dir.resource_name ' ||
' UNION ' || -- union will remove duplicate.
--l_rsc_4_na_owned_by_indirect
' select'||
' dir.resource_name, dir.resource_id,'||
' dir.group_name, dir.group_id, '||
' dir.role_name, dir.role_code '||
' from '||
' jtf_tty_my_directs_v dir '||
' where dir.current_user_id = :0 '||
' and dir.dir_user_id <> :1 '||
' and dir.resource_id IN ( select res.parent_resource_id '||
' from jtf_rs_rep_managers res, '||
' jtf_tty_named_acct_rsc narsc, '||
' jtf_tty_terr_grp_accts ga '||
' where res.resource_id = narsc.resource_id '||
' and res.group_id = narsc.rsc_group_id '||
' and res.role_code = narsc.rsc_role_code '||
' and narsc.terr_group_account_id = ga.terr_group_account_id '||
' and narsc.rsc_resource_type = ''RS_EMPLOYEE'' '||
' and ga.terr_group_account_id = :2 ) '||
' order by dir.role_code, dir.resource_name ';
select metric_value into l_dnb_annual_rev
from JTF_TTY_ACCT_METRICS am
where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
and rownum<2;
select metric_value into l_dnb_num_of_emp
from JTF_TTY_ACCT_METRICS am
where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
and rownum<2;
select metric_value into l_prior_won
from JTF_TTY_ACCT_METRICS am
where m.JTF_TTY_WEBADI_INT_ID = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'PRIOR_SALES'
and rownum<2;
select alignment_id into l_align_id
from jtf_tty_align_accts
where terr_group_account_id = m.terr_grp_acct_id
and alignment_id = p_align_id;
update JTF_TTY_WEBADI_INTERFACE -- /*+ INDEX(JTF_TTY_WEBADI_INTF_N2) */
set RESOURCE1_NAME=RESOURCE_NAME(1),GROUP1_NAME=GROUP_NAME(1),ROLE1_NAME=ROLE_NAME(1),
RESOURCE2_NAME=RESOURCE_NAME(2),GROUP2_NAME=GROUP_NAME(2),ROLE2_NAME=ROLE_NAME(2),
RESOURCE3_NAME=RESOURCE_NAME(3),GROUP3_NAME=GROUP_NAME(3),ROLE3_NAME=ROLE_NAME(3),
RESOURCE4_NAME=RESOURCE_NAME(4),GROUP4_NAME=GROUP_NAME(4),ROLE4_NAME=ROLE_NAME(4),
RESOURCE5_NAME=RESOURCE_NAME(5),GROUP5_NAME=GROUP_NAME(5),ROLE5_NAME=ROLE_NAME(5),
RESOURCE6_NAME=RESOURCE_NAME(6),GROUP6_NAME=GROUP_NAME(6),ROLE6_NAME=ROLE_NAME(6),
RESOURCE7_NAME=RESOURCE_NAME(7),GROUP7_NAME=GROUP_NAME(7),ROLE7_NAME=ROLE_NAME(7),
RESOURCE8_NAME=RESOURCE_NAME(8),GROUP8_NAME=GROUP_NAME(8),ROLE8_NAME=ROLE_NAME(8),
RESOURCE9_NAME=RESOURCE_NAME(9),GROUP9_NAME=GROUP_NAME(9),ROLE9_NAME=ROLE_NAME(9),
RESOURCE10_NAME=RESOURCE_NAME(10),GROUP10_NAME=GROUP_NAME(10),ROLE10_NAME=ROLE_NAME(10),
RESOURCE11_NAME=RESOURCE_NAME(11),GROUP11_NAME=GROUP_NAME(11),ROLE11_NAME=ROLE_NAME(11),
RESOURCE12_NAME=RESOURCE_NAME(12),GROUP12_NAME=GROUP_NAME(12),ROLE12_NAME=ROLE_NAME(12),
RESOURCE13_NAME=RESOURCE_NAME(13),GROUP13_NAME=GROUP_NAME(13),ROLE13_NAME=ROLE_NAME(13),
RESOURCE14_NAME=RESOURCE_NAME(14),GROUP14_NAME=GROUP_NAME(14),ROLE14_NAME=ROLE_NAME(14),
RESOURCE15_NAME=RESOURCE_NAME(15),GROUP15_NAME=GROUP_NAME(15),ROLE15_NAME=ROLE_NAME(15),
RESOURCE16_NAME=RESOURCE_NAME(16),GROUP16_NAME=GROUP_NAME(16),ROLE16_NAME=ROLE_NAME(16),
RESOURCE17_NAME=RESOURCE_NAME(17),GROUP17_NAME=GROUP_NAME(17),ROLE17_NAME=ROLE_NAME(17),
RESOURCE18_NAME=RESOURCE_NAME(18),GROUP18_NAME=GROUP_NAME(18),ROLE18_NAME=ROLE_NAME(18),
RESOURCE19_NAME=RESOURCE_NAME(19),GROUP19_NAME=GROUP_NAME(19),ROLE19_NAME=ROLE_NAME(19),
RESOURCE20_NAME=RESOURCE_NAME(20),GROUP20_NAME=GROUP_NAME(20),ROLE20_NAME=ROLE_NAME(20),
RESOURCE21_NAME=RESOURCE_NAME(21),GROUP21_NAME=GROUP_NAME(21),ROLE21_NAME=ROLE_NAME(21),
RESOURCE22_NAME=RESOURCE_NAME(22),GROUP22_NAME=GROUP_NAME(22),ROLE22_NAME=ROLE_NAME(22),
RESOURCE23_NAME=RESOURCE_NAME(23),GROUP23_NAME=GROUP_NAME(23),ROLE23_NAME=ROLE_NAME(23),
RESOURCE24_NAME=RESOURCE_NAME(24),GROUP24_NAME=GROUP_NAME(24),ROLE24_NAME=ROLE_NAME(24),
RESOURCE25_NAME=RESOURCE_NAME(25),GROUP25_NAME=GROUP_NAME(25),ROLE25_NAME=ROLE_NAME(25),
RESOURCE26_NAME=RESOURCE_NAME(26),GROUP26_NAME=GROUP_NAME(26),ROLE26_NAME=ROLE_NAME(26),
RESOURCE27_NAME=RESOURCE_NAME(27),GROUP27_NAME=GROUP_NAME(27),ROLE27_NAME=ROLE_NAME(27),
RESOURCE28_NAME=RESOURCE_NAME(28),GROUP28_NAME=GROUP_NAME(28),ROLE28_NAME=ROLE_NAME(28),
RESOURCE29_NAME=RESOURCE_NAME(29),GROUP29_NAME=GROUP_NAME(29),ROLE29_NAME=ROLE_NAME(29),
RESOURCE30_NAME=RESOURCE_NAME(30),GROUP30_NAME=GROUP_NAME(30),ROLE30_NAME=ROLE_NAME(30),
dnb_annual_rev=l_dnb_annual_rev,dnb_num_of_em=l_dnb_num_of_emp,prior_won=l_prior_won
where user_id = p_userid
and TERR_GRP_ACCT_ID =m.TERR_GRP_ACCT_ID;
SELECT AA.align_proposed_terr_id
FROM JTF_TTY_ALIGN_PTERR AA
WHERE AA.ALIGNMENT_ID = p_alignment_id;
delete from jtf_tty_pterr_metrics
where align_proposed_terr_id = l_align_pterrs_tbl(y);
insert into jtf_tty_pterr_metrics
( align_pterr_metric_id
,object_version_number
,align_proposed_terr_id
,metric_lookup_type
,metric_lookup_code
,metric_value
,metric_value_percent
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
select
jtf_tty_pterr_metrics_s.nextval
, 1
, l_align_pterrs_tbl(j)
, 'JTF_TTY_ALIGN_METRICS'
, 'DNB_ANNUAL_REVENUE'
, pterr_list.metric_value
, pterr_list.metric_pct
, l_user_id
, l_sysdate
, l_user_id
, l_sysdate
, 1
from ( select pa.align_proposed_terr_id pterr_id
,sum(am.metric_value) metric_value
,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
from JTF_TTY_ACCT_METRICS AM,
jtf_tty_align_accts ac,
JTF_TTY_PTERR_ACCTS pa,
jtf_tty_terr_grp_accts ga,
jtf_tty_align_pterr ap,
( select sum(ams.metric_value) align_metric_val
from jtf_tty_acct_metrics ams
,jtf_tty_terr_grp_accts tga
,jtf_tty_align_accts ala
where ala.alignment_id = l_alignment_id
and ala.terr_group_account_id = tga.terr_group_account_id
and tga.named_account_id = ams.named_account_id
and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and ams.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
) alm
where
pa.align_proposed_terr_id = ap.align_proposed_terr_id
and ap.alignment_id = l_alignment_id
and pa.align_acct_id = ac.align_acct_id
and ac.terr_group_account_id = ga.terr_group_account_id
and ga.named_account_id = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'DNB_ANNUAL_REVENUE'
and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
and alm.align_metric_val > 0
group by alm.align_metric_val, pa.align_proposed_terr_id
) pterr_list;
insert into jtf_tty_pterr_metrics
( align_pterr_metric_id
,object_version_number
,align_proposed_terr_id
,metric_lookup_type
,metric_lookup_code
,metric_value
,metric_value_percent
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
select
jtf_tty_pterr_metrics_s.nextval
, 1
, l_align_pterrs_tbl(j)
, 'JTF_TTY_ALIGN_METRICS'
, 'DNB_NUM_EMPLOYEES'
, pterr_list.metric_value
, pterr_list.metric_pct
, l_user_id
, l_sysdate
, l_user_id
, l_sysdate
, 1
from ( select pa.align_proposed_terr_id pterr_id
,sum(am.metric_value) metric_value
,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
from JTF_TTY_ACCT_METRICS AM,
jtf_tty_align_accts ac,
JTF_TTY_PTERR_ACCTS pa,
jtf_tty_terr_grp_accts ga,
jtf_tty_align_pterr ap,
( select sum(ams.metric_value) align_metric_val
from jtf_tty_acct_metrics ams
,jtf_tty_terr_grp_accts tga
,jtf_tty_align_accts ala
where ala.alignment_id = l_alignment_id
and ala.terr_group_account_id = tga.terr_group_account_id
and tga.named_account_id = ams.named_account_id
and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and ams.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
) alm
where
pa.align_proposed_terr_id = ap.align_proposed_terr_id
and ap.alignment_id = l_alignment_id
and pa.align_acct_id = ac.align_acct_id
and ac.terr_group_account_id = ga.terr_group_account_id
and ga.named_account_id = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'DNB_NUM_EMPLOYEES'
and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
and alm.align_metric_val > 0
group by alm.align_metric_val, pa.align_proposed_terr_id
) pterr_list;
insert into jtf_tty_pterr_metrics
( align_pterr_metric_id
,object_version_number
,align_proposed_terr_id
,metric_lookup_type
,metric_lookup_code
,metric_value
,metric_value_percent
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
select
jtf_tty_pterr_metrics_s.nextval
, 1
, l_align_pterrs_tbl(j)
, 'JTF_TTY_ALIGN_METRICS'
, 'NUM_ACCOUNTS'
, pterr_list.metric_value
, pterr_list.metric_pct
, l_user_id
, l_sysdate
, l_user_id
, l_sysdate
, 1
from (select pa.align_proposed_terr_id pterr_id
,count(pa.align_acct_id) metric_value
,round( (count(pa.align_acct_id)/ alm.tot_align_metric_val )* 100, 2 ) metric_pct
from
JTF_TTY_PTERR_ACCTS pa,
jtf_tty_align_pterr ap,
( select count(ala.terr_group_account_id) tot_align_metric_val
from jtf_tty_align_accts ala
where ala.alignment_id = l_alignment_id
) alm
where
pa.align_proposed_terr_id = ap.align_proposed_terr_id
and ap.alignment_id = l_alignment_id
and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
and alm.tot_align_metric_val > 0
group by alm.tot_align_metric_val, pa.align_proposed_terr_id
) pterr_list;
insert into jtf_tty_pterr_metrics
( align_pterr_metric_id
,object_version_number
,align_proposed_terr_id
,metric_lookup_type
,metric_lookup_code
,metric_value
,metric_value_percent
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
)
select
jtf_tty_pterr_metrics_s.nextval
, 1
, l_align_pterrs_tbl(j)
, 'JTF_TTY_ALIGN_METRICS'
, 'PRIOR_SALES'
, pterr_list.metric_value
, pterr_list.metric_pct
, l_user_id
, l_sysdate
, l_user_id
, l_sysdate
, 1
from ( select pa.align_proposed_terr_id pterr_id
,sum(am.metric_value) metric_value
,round( (sum(am.metric_value)/ alm.align_metric_val )* 100, 2 ) metric_pct
from JTF_TTY_ACCT_METRICS AM,
jtf_tty_align_accts ac,
JTF_TTY_PTERR_ACCTS pa,
jtf_tty_terr_grp_accts ga,
jtf_tty_align_pterr ap,
( select sum(ams.metric_value) align_metric_val
from jtf_tty_acct_metrics ams
,jtf_tty_terr_grp_accts tga
,jtf_tty_align_accts ala
where ala.alignment_id = l_alignment_id
and ala.terr_group_account_id = tga.terr_group_account_id
and tga.named_account_id = ams.named_account_id
and ams.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and ams.metric_lookup_code = 'PRIOR_SALES'
) alm
where
pa.align_proposed_terr_id = ap.align_proposed_terr_id
and ap.alignment_id = l_alignment_id
and pa.align_acct_id = ac.align_acct_id
and ac.terr_group_account_id = ga.terr_group_account_id
and ga.named_account_id = am.named_account_id
and am.metric_lookup_type = 'JTF_TTY_ALIGN_METRICS'
and am.metric_lookup_code = 'PRIOR_SALES'
and pa.align_proposed_terr_id = l_align_pterrs_tbl(j)
and alm.align_metric_val > 0
group by alm.align_metric_val, pa.align_proposed_terr_id
) pterr_list;
PROCEDURE UPDATE_ALIGNMENT_TEAM(
p_api_version_number IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_SQL_Trace IN VARCHAR2,
p_Debug_Flag IN VARCHAR2,
p_alignment_id IN NUMBER,
p_user_id IN NUMBER,
p_user_attribute1 IN VARCHAR2,
p_added_rscs_tbl IN JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE,
p_removed_rscs_tbl IN JTF_TTY_NACCT_SALES_PUB.SALESREP_RSC_TBL_TYPE,
p_affected_parties_tbl IN JTF_TTY_NACCT_SALES_PUB.AFFECTED_PARTY_TBL_TYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_align_acct_id NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ALIGNMENT_TEAM';
select AA.align_proposed_terr_id
from JTF_TTY_ALIGN_PTERR AA,
JTF_TTY_PTERR_ACCTS PA
where AA.ALIGNMENT_ID = c_alignment_id
and AA.ALIGN_PROPOSED_TERR_ID = PA.ALIGN_PROPOSED_TERR_ID
and PA.ALIGN_ACCT_ID = c_align_acct_id ;
select align_acct_id
from JTF_TTY_ALIGN_ACCTS
where terr_group_account_id = c_terr_group_account_id
and alignment_id = c_alignment_id;
select align_acct_id, terr_group_account_id
from JTF_TTY_ALIGN_ACCTS
where alignment_id = c_alignment_id;
select align_proposed_terr_id
from JTF_TTY_ALIGN_PTERR
where alignment_id = c_alignment_id
and resource_id = c_resource_id
and rsc_group_id = c_group_id
and rsc_role_code = c_role_code;
select ga.terr_group_account_id gaid
from jtf_tty_terr_grp_accts ga,
jtf_tty_terr_groups ttygrp
where ttygrp.terr_group_id = ga.terr_group_id
and ttygrp.active_from_date <= sysdate
and ( ttygrp.active_to_date is null
or
ttygrp.active_to_date >= sysdate
)
and ga.terr_group_account_id IN
( select /*+ NO_MERGE */
narsc.terr_group_account_id
from jtf_tty_named_acct_rsc narsc,
jtf_tty_srch_my_resources_v repdn
where narsc.resource_id = repdn.resource_id
and narsc.rsc_group_id = repdn.group_id
and repdn.current_user_id = c_user_id
);
select narsc.resource_id resource_id,
narsc.rsc_group_id group_id,
narsc.rsc_role_code role_code
from jtf_tty_named_acct_rsc narsc
where narsc.terr_group_account_id = c_tg_acct_id
and narsc.rsc_resource_type = 'RS_EMPLOYEE'
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 );
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_tty_named_acct_rsc narsc,
jtf_rs_rep_managers repmgr,
jtf_rs_role_relations rlt,
jtf_rs_roles_b rol,
jtf_rs_group_members grpmem
where narsc.resource_id = repmgr.resource_id
AND narsc.rsc_group_id = repmgr.group_id
AND narsc.terr_group_account_id = c_tg_acct_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'
);
select imported_on
into l_imported_on
from jtf_tty_alignments
where alignment_id = l_alignment_id;
insert into JTF_TTY_ALIGN_ACCTS
( align_acct_id
,object_version_number
,alignment_id
,terr_group_account_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( JTF_TTY_ALIGN_ACCTS_S.nextval
,1
,l_alignment_id
,all_tg_accts_tbl(k)
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
insert into JTF_TTY_PTERR_ACCTS
( align_pterr_acct_id
,object_version_number
,align_proposed_terr_id
,align_acct_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( JTF_TTY_PTERR_ACCTS_S.nextval
,1
,align_rec.align_proposed_terr_id
,all_align_accts_tbl(j)
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
select count(*)
into l_pterr_accts_num
from jtf_tty_pterr_accts
where align_proposed_terr_id = align_pterr_rec.align_proposed_terr_id
and align_acct_id = all_align_accts_tbl(j);
insert into JTF_TTY_PTERR_ACCTS
( align_pterr_acct_id
,object_version_number
,align_proposed_terr_id
,align_acct_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( JTF_TTY_PTERR_ACCTS_S.nextval
,1
,align_pterr_rec.align_proposed_terr_id
,all_align_accts_tbl(j)
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
update jtf_tty_alignments
set imported_on = l_sysdate
where alignment_id = l_alignment_id;
select JTF_TTY_ALIGN_ACCTS_S.nextval
into l_align_acct_id
from dual;
insert into JTF_TTY_ALIGN_ACCTS
( align_acct_id
,object_version_number
,alignment_id
,terr_group_account_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( l_align_acct_id
,1
,l_alignment_id
,p_affected_parties_tbl(j).terr_group_account_id
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
select JTF_TTY_ALIGN_PTERR_S.nextval
into l_align_pterr_id
from dual;
insert into JTF_TTY_ALIGN_PTERR
( align_proposed_terr_id
,object_version_number
,alignment_id
,resource_id
,rsc_group_id
,rsc_role_code
,resource_type
,proposed_quota
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( l_align_pterr_id
,1
,l_alignment_id
,p_added_rscs_tbl(i).resource_id
,p_added_rscs_tbl(i).group_id
,p_added_rscs_tbl(i).role_code
,'RS_EMPLOYEE'
,0
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
select count(*)
into l_pterr_accts_num
from jtf_tty_pterr_accts
where align_proposed_terr_id = l_align_pterr_id
and align_acct_id = l_align_acct_id
and rownum < 2;
insert into JTF_TTY_PTERR_ACCTS
( align_pterr_acct_id
,object_version_number
,align_proposed_terr_id
,align_acct_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
) values
( JTF_TTY_PTERR_ACCTS_S.nextval
,1
,l_align_pterr_id
,l_align_acct_id
,G_USER
,l_sysdate
,G_USER
,l_sysdate
,G_LOGIN
);
delete from JTF_TTY_PTERR_ACCTS
where align_proposed_terr_id = l_align_pterr_id
and align_acct_id = l_align_acct_id;
delete from jtf_tty_align_accts
where alignment_id = l_alignment_id
and terr_group_account_id NOT IN
( select ga.terr_group_account_id
from jtf_tty_terr_grp_accts ga,
jtf_tty_terr_groups ttygrp
where ttygrp.terr_group_id = ga.terr_group_id
and ttygrp.active_from_date <= sysdate
and ( ttygrp.active_to_date is null
or
ttygrp.active_to_date >= sysdate
)
and ga.terr_group_account_id IN
( select /*+ NO_MERGE */ narsc.terr_group_account_id
from jtf_tty_named_acct_rsc narsc,
jtf_tty_srch_my_resources_v repdn
where narsc.resource_id = repdn.resource_id
and narsc.rsc_group_id = repdn.group_id
and repdn.current_user_id = l_user_id
)
);
delete from jtf_tty_pterr_accts
where align_proposed_terr_id IN
( select align_proposed_terr_id
from jtf_tty_align_pterr
where alignment_id = l_alignment_id )
and align_acct_id NOT IN
( select align_acct_id
from jtf_tty_align_accts
where alignment_id = l_alignment_id )
returning align_proposed_terr_id BULK COLLECT INTO pterrs_changed_tbl;