[Home] [Help]
178: from (
179: select ap.rsc_role_code role_code, count(ap.rsc_role_code) num
180: from JTF_TTY_ALIGN_ACCTS aa,
181: JTF_TTY_PTERR_ACCTS pa,
182: JTF_TTY_ALIGN_PTERR ap
183: -- jtf_rs_roles_vl rol
184: where
185: aa.alignment_id = p_align_id
186: and aa.align_acct_id = pa.align_acct_id
272: grp.group_name, --ap.rsc_group_id group_id,
273: rol.role_name, ap.rsc_role_code role_code
274: from JTF_TTY_ALIGN_ACCTS aa,
275: JTF_TTY_PTERR_ACCTS pa,
276: JTF_TTY_ALIGN_PTERR ap,
277: jtf_rs_resource_extns_vl rsc,
278: jtf_rs_groups_vl grp,
279: jtf_rs_roles_vl rol
280: where aa.terr_group_account_id = tgaid
699:
700: CURSOR c_all_pterrs
701: IS
702: SELECT AA.align_proposed_terr_id
703: FROM JTF_TTY_ALIGN_PTERR AA
704: WHERE AA.ALIGNMENT_ID = p_alignment_id;
705:
706: l_align_pterrs_tbl Number_table_type := Number_table_type();
707: l_sysdate DATE;
761: from JTF_TTY_ACCT_METRICS AM,
762: jtf_tty_align_accts ac,
763: JTF_TTY_PTERR_ACCTS pa,
764: jtf_tty_terr_grp_accts ga,
765: jtf_tty_align_pterr ap,
766: ( select sum(ams.metric_value) align_metric_val
767: from jtf_tty_acct_metrics ams
768: ,jtf_tty_terr_grp_accts tga
769: ,jtf_tty_align_accts ala
820: from JTF_TTY_ACCT_METRICS AM,
821: jtf_tty_align_accts ac,
822: JTF_TTY_PTERR_ACCTS pa,
823: jtf_tty_terr_grp_accts ga,
824: jtf_tty_align_pterr ap,
825: ( select sum(ams.metric_value) align_metric_val
826: from jtf_tty_acct_metrics ams
827: ,jtf_tty_terr_grp_accts tga
828: ,jtf_tty_align_accts ala
877: ,count(pa.align_acct_id) metric_value
878: ,round( (count(pa.align_acct_id)/ alm.tot_align_metric_val )* 100, 2 ) metric_pct
879: from
880: JTF_TTY_PTERR_ACCTS pa,
881: jtf_tty_align_pterr ap,
882: ( select count(ala.terr_group_account_id) tot_align_metric_val
883: from jtf_tty_align_accts ala
884: where ala.alignment_id = l_alignment_id
885: ) alm
925: from JTF_TTY_ACCT_METRICS AM,
926: jtf_tty_align_accts ac,
927: JTF_TTY_PTERR_ACCTS pa,
928: jtf_tty_terr_grp_accts ga,
929: jtf_tty_align_pterr ap,
930: ( select sum(ams.metric_value) align_metric_val
931: from jtf_tty_acct_metrics ams
932: ,jtf_tty_terr_grp_accts tga
933: ,jtf_tty_align_accts ala
987: pterrs_changed_tbl Number_table_type := Number_table_type();
988:
989: cursor c_all_pterrs (c_align_acct_id NUMBER, c_alignment_id NUMBER ) IS
990: select AA.align_proposed_terr_id
991: from JTF_TTY_ALIGN_PTERR AA,
992: JTF_TTY_PTERR_ACCTS PA
993: where AA.ALIGNMENT_ID = c_alignment_id
994: and AA.ALIGN_PROPOSED_TERR_ID = PA.ALIGN_PROPOSED_TERR_ID
995: and PA.ALIGN_ACCT_ID = c_align_acct_id ;
1007:
1008: cursor c_align_pterr(c_resource_id NUMBER, c_group_id NUMBER, c_role_code VARCHAR2,
1009: c_alignment_id NUMBER ) IS
1010: select align_proposed_terr_id
1011: from JTF_TTY_ALIGN_PTERR
1012: where alignment_id = c_alignment_id
1013: and resource_id = c_resource_id
1014: and rsc_group_id = c_group_id
1015: and rsc_role_code = c_role_code;
1296: --insert into tmp2 values('2. b4 create pterr. l_align_pterr_id =', l_align_pterr_id); commit;
1297: if c_align_pterr%notfound then
1298: --insert into tmp2 values('1.77775. b4 c_align_pterr. p_added_rscs_tbl(i).resource_id =', p_added_rscs_tbl(i).resource_id || ' ' || p_added_rscs_tbl(i).group_id || p_added_rscs_tbl(i).role_code); commit;
1299: -- create a proposed territory for this resource
1300: select JTF_TTY_ALIGN_PTERR_S.nextval
1301: into l_align_pterr_id
1302: from dual;
1303:
1304: insert into JTF_TTY_ALIGN_PTERR
1300: select JTF_TTY_ALIGN_PTERR_S.nextval
1301: into l_align_pterr_id
1302: from dual;
1303:
1304: insert into JTF_TTY_ALIGN_PTERR
1305: ( align_proposed_terr_id
1306: ,object_version_number
1307: ,alignment_id
1308: ,resource_id
1496:
1497: delete from jtf_tty_pterr_accts
1498: where align_proposed_terr_id IN
1499: ( select align_proposed_terr_id
1500: from jtf_tty_align_pterr
1501: where alignment_id = l_alignment_id )
1502: and align_acct_id NOT IN
1503: ( select align_acct_id
1504: from jtf_tty_align_accts