469: FROM
470: jtf_tty_named_acct_rsc nar
471: , jtf_tty_terr_grp_accts tga
472: , jtf_tty_terr_grp_roles tgr
473: , jtf_tty_role_access ra
474: WHERE tga.terr_group_account_id = nar.terr_group_account_id
475: AND nar.terr_group_account_id = LP_terr_group_account_id
476: AND nar.rsc_role_code = tgr.role_code
477: AND ra.terr_group_role_id = tgr.terr_group_role_id
481: FROM
482: jtf_tty_named_acct_rsc nar
483: , jtf_tty_terr_grp_accts tga
484: , jtf_tty_terr_grp_roles tgr
485: , jtf_tty_role_access ra
486: WHERE tga.terr_group_account_id = nar.terr_group_account_id
487: AND nar.terr_group_account_id = LP_terr_group_account_id
488: AND nar.rsc_role_code = tgr.role_code
489: AND ra.terr_group_role_id = tgr.terr_group_role_id
495:
496: /* Access Types for a Territory Group */
497: CURSOR na_access(l_terr_group_id number) IS
498: SELECT distinct a.access_type
499: from jtf_tty_role_access a
500: , jtf_tty_terr_grp_roles b
501: where a.terr_group_role_id = b.terr_group_role_id
502: and b.terr_group_id = l_terr_group_id;
503:
504: /* Access Types for a particular Role within a Territory Group */
505: CURSOR NON_OVLY_role_access( lp_terr_group_id number
506: , lp_role varchar2) IS
507: SELECT distinct a.access_type
508: from jtf_tty_role_access a
509: , jtf_tty_terr_grp_roles b
510: where a.terr_group_role_id = b.terr_group_role_id
511: and b.terr_group_id = lp_terr_group_id
512: and b.role_code = lp_role
520:
521: /* Access Types for a particular Role within a Territory Group */
522: CURSOR role_access(l_terr_group_id number,l_role varchar2) IS
523: SELECT distinct a.access_type
524: from jtf_tty_role_access a
525: , jtf_tty_terr_grp_roles b
526: where a.terr_group_role_id = b.terr_group_role_id
527: and b.terr_group_id = l_terr_group_id
528: and b.role_code = l_role
775: /* get those roles for a territory Group that
776: ** do not have Product Interest defined */
777: CURSOR role_no_pi(l_terr_group_id number) IS
778: SELECT distinct b.role_code
779: from jtf_tty_role_access a
780: , jtf_tty_terr_grp_roles b
781: , jtf_tty_role_prod_int c
782: where a.terr_group_role_id = b.terr_group_role_id
783: and b.terr_group_id = l_terr_group_id
872: , sysdate
873: , G_USER_ID
874: , sysdate
875: , G_LOGIN_ID);
876: INSERT into JTF_TTY_ROLE_ACCESS(
877: TERR_GROUP_ROLE_ACCESS_ID
878: , OBJECT_VERSION_NUMBER
879: , TERR_GROUP_ROLE_ID
880: , ACCESS_TYPE
883: , LAST_UPDATED_BY
884: , LAST_UPDATE_DATE
885: , LAST_UPDATE_LOGIN)
886: VALUES(
887: JTF_TTY_ROLE_ACCESS_S.nextval
888: , 1
889: , l_overnon_role_tbl(i).grp_role_id
890: , 'ACCOUNT'
891: , G_USER_ID
2239: loop
2240: delete from jtf_tty_terr_grp_roles
2241: where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
2242: --dbms_output.put_line('deleted');
2243: delete from jtf_tty_role_access
2244: where TERR_GROUP_ROLE_ID=l_overnon_role_tbl(i).grp_role_id;
2245: end loop;
2246: end if;
2247:
3801: SELECT ra.access_type
3802: FROM
3803: JTF_TTY_GEO_TERR_RSC grsc
3804: , jtf_tty_terr_grp_roles tgr
3805: , jtf_tty_role_access ra
3806: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
3807: AND grsc.rsc_role_code = tgr.role_code
3808: AND ra.terr_group_role_id = tgr.terr_group_role_id
3809: AND ra.access_type IN ('ACCOUNT')
3811: SELECT ra.access_type
3812: FROM
3813: JTF_TTY_GEO_TERR_RSC grsc
3814: , jtf_tty_terr_grp_roles tgr
3815: , jtf_tty_role_access ra
3816: WHERE grsc.GEO_TERRITORY_ID = l_geo_territory_id
3817: AND grsc.rsc_role_code = tgr.role_code
3818: AND ra.terr_group_role_id = tgr.terr_group_role_id
3819: AND NOT EXISTS (
3939: /* Access Types for a particular Role within a Territory Group */
3940: CURSOR NON_OVLY_role_access( lp_terr_group_id number
3941: , lp_role varchar2) IS
3942: SELECT distinct a.access_type
3943: from jtf_tty_role_access a
3944: , jtf_tty_terr_grp_roles b
3945: where a.terr_group_role_id = b.terr_group_role_id
3946: and b.terr_group_id = lp_terr_group_id
3947: and b.role_code = lp_role
4056:
4057: /* Access Types for a particular Role within a Territory Group */
4058: CURSOR role_access(l_terr_group_id number,l_role varchar2) IS
4059: SELECT distinct a.access_type
4060: from jtf_tty_role_access a
4061: , jtf_tty_terr_grp_roles b
4062: where a.terr_group_role_id = b.terr_group_role_id
4063: and b.terr_group_id = l_terr_group_id
4064: and b.role_code = l_role